[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『入力規則のリストを入力履歴を元に自動作成したい。』（にゅるん）
テーブルを使って、定期的に入力していくデータベースのようなものを作成しています。
その中に大区分、中区分、小区分という列があり、現在はAlt+↑、↓を使って入力しています。
これを、データの入力規則のリストを使用し、中区分は大区分によって表示内容が絞り込まれ、小区分は中区分によって絞り込まれる仕様にしたいと思います。
ここまでは普通に検索すると沢山ヒットするのですが、リスト用の表を別途手動で入力しなければならないものばかりが見つかります。
リスト用の表を作らないで（過去に入力されたデータが選択項目として表示される）
ようなものを実現する方法は無いでしょうか。
不特定多数の人が入力するため、出来ればVBAを使わないで関数のみで実現する方法が知りたいです。
以上、よろしくお願いいたします。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
作業列を使うのはアリです。
小区分が複数の中区分に、中区分が複数の大区分に存在することはありません。
小区分の重複を削除→小｜中｜大の対応表を作成＝小区分のリスト
中区分の重複を削除→中｜大の対応表を作成＝中区分のリスト
大区分の重複を削除→大区分のリスト
という流れで出来そうな気がします。
残念ながら自力でこれらの関数式が書けそうにありません。
（にゅるん） 2018/07/25(水) 17:50
＞リスト用の表を別途手動で入力しなければならないものばかりが見つかります。
多分、それが王道だからなんでしょうね。
＞・・という流れで出来そうな気がします。
途中の流れはさて置き、最終的にどんなリストになるんですか？ 簡略なリストの形で示して頂けませんか(大2種、中各2種計4、小各2種計8)
現在、ざっとで結構ですが、大・中・小 それぞれ何種類ぐらいあるんですか？
（半平太） 2018/07/25(水) 21:41
＞大1-中1-小1のものもあれば ＞大1-中3-小10のようなものもあります。
それは了解しています。 具体的なリスト例を書き易くするために、例示したものです。
＞小区分の重複を削除→小｜中｜大の対応表を作成＝小区分のリスト ＞中区分の重複を削除→中｜大の対応表を作成＝中区分のリスト ＞大区分の重複を削除→大区分のリスト ↑ こう云う手順で、どんなリストが出来るのか？ (またそれが最終形じゃないなら）どんな最終形のリストに仕上げるべきなのか？
それについて、にゅるんさん自身が具体的な形で理解できているか知りたいのです。
（半平太） 2018/07/26(木) 09:02
↓小区分のリスト 名前定義でデータベースの現在カーソルのある行の中区分の値で範囲を変化させる？
小区分 中区分
１１１ AAAAAA
２２２ AAAAAA
３３３ BBBBBB
４４４ BBBBBB
５５５ CCCCCC
※小区分はデータベースから重複を削除をする関数で抽出する？
※中区分は小区分をキーにデータベースからIndex、Matchで取り出す
↓中区分のリスト 同上
中区分 大区分
AAAAAA あああ
BBBBBB あああ
CCCCCC いいい
↓大区分のリスト
大区分
あああ
いいい
こんな感じで実現出来るのかなぁというイメージです。
（にゅるん） 2018/07/26(木) 09:29
そうするとトップダウンでリストを作成するしかない・・・？方法が思いつかないです。
（にゅるん） 2018/07/26(木) 10:28
＞私の方法だと上位の区分が整列しない
そこまで理解いただけたなら、もう十分です。
ところで、入力履歴はどこに、どんな状態で存在しているんですか？
こっちで勝手に決めて、あとはそちらで自力アジャストしますか？ それはしたくないなら、実際のレイアウト(タイトル・行列番号、2,3行のサンプルデータ）を提示してください。
（半平太） 2018/07/26(木) 12:11
A B C D 1 日付 大区分 中区分 小区分 2 1/1 あああ AAAAAA １１１ 3 1/2 あああ AAAAAA ２２２ 4 1/5 あああ BBBBBB ３３３ 5 1/8 あああ BBBBBB ４４４ 6 1/10 いいい CCCCCC ５５５ 項目はこれだけしか無いものとしてご教示頂けると幸いです。 というか、自分で直せないと今後の保守が出来ないので・・・
※上記試行錯誤の結果から、テーブルとして定義しようかと考えております。
右端の作業列、関数のみでバックグラウンドで動作するシートは幾らでも追加していただいて構いません。
以上、よろしくお願いいたします。
（にゅるん） 2018/07/26(木) 13:28
＞※上記試行錯誤の結果から、テーブルとして定義しようかと考えております。
それは、そちらサイドで工夫してください。
私の方は、定義なしにします。
数式の埋め込みだけでも結構大変です (-_-;)
※ 取りあえず書きますが、多分、面倒だと思いますので、
後記の「省エネマクロ」を使えば「タイトル・サンプルデータ・数式・入力規則」の自動埋め込みが出来ます。
「省エネマクロ」を使わない場合、F1,S1,T1セルは、サンプルの通りじゃなくてもいいですが、何か文字で埋めてください。
逆に、N1セルは空白にして置いてください。 リストに無くても「新規データ入力」でチェックに引っ掛からない様にするのに必要になります。 ※ 今回の入力規則は、リストからの選択を強制するものでなく、入力の補助をするだけであるため。
１．数式の入力
(1) F2セル =IF(F1="","",IFERROR(AGGREGATE(15,7,ROW($A$1:$A999)/(MATCH(D$2:D$1000,D$2:D$1000,0)=ROW($A$1:$A999)),ROW($A1)),""))
(2) G2セル =IF($F2="","",INDEX(B:B,$F2+1))
(3) I2セル =IF($F2="","",INDEX(D:D,$F2+1))
(4) J2セル =IF($F2="","",COUNTIF(G$2:G$250,"<"&G2))
(5) K2セル =IF($F2="","",COUNTIF(H$2:H$250,"<"&H2))
(6) L2セル =IF($F2="","",COUNTIF(I$2:I$250,"<"&I2))
(7) M2セル =IF(F2="","",J2*1000000+K2*1000+L2)
(8) N2セル =IF($F2="","",RANK(M2,$M$2:$M$250,1))
(9) O2セル =IF($F2="","",INDEX(G:G,MATCH(ROW($A1),$N:$N,0)))
(10) P2セル =IF($F2="","",INDEX(H:H,MATCH(ROW($A1),$N:$N,0)))
(11) Q2セル =IF($F2="","",INDEX(I:I,MATCH(ROW($A1),$N:$N,0)))
(12) R2セル =IF($F2="","",O2&"#!#"&P2)
(13) S2セル =IF(S1="","",IFERROR(INDEX(R$1:R$249,AGGREGATE(15,6,ROW($R$2:$R$250)/(MATCH($R$2:$R$250,$R$2:$R$250,0)=ROW($R$1:$R$249)),ROW($A1))),""))
(14) T2セル =IF(T1="","",IFERROR(INDEX(O$2:O$1000,AGGREGATE(15,7,ROW($A$1:$A999)/(MATCH(O$2:O$250,O$2:O$250,0)=ROW($A$1:$A999)),ROW($A1))),""))
(15) U2セル =IF(S2="","",REPLACE(S2,1,FIND("#!#",S2)+2,""))
F列からR列までは、250行目まで、コピー S列とU列は、 50行目までコピー T列は、 20行目までコピー
２．入力規則の設定 B2セル以下 「リスト」「空白を無視する」にチェックを入れる 元の値 =OFFSET($T$1,1,0,COUNTIF($T$2:$T$20,"*?")+LEN($D2)*0)
C2セル以下 「リスト」「空白を無視する」にチェックを入れる 元の値 =OFFSET($U$1,MATCH(B2&"#!#*",$S$2:$S$11,0),0,COUNTIF($S$2:$S$11,B2&"#!#*")+LEN($N$1)*0) D2セル以下 「リスト」「空白を無視する」にチェックを入れる 元の値 =OFFSET($P$1,MATCH(C2,$P$2:$P$250,0),1,COUNTIF($P$2:$P$250,C2)+LEN($N$1)*0)
<サンプル図> 行 __A__ ___B___ ___C___ ___D___ _E_ _______F_______ ___G___ ___H___ __I__ _J_ _K_ _L_ ____M____ _N_ ___O___ ___P___ __Q__ _______R_______ _______S_______ ___T___ ___U___ 1 日付 大区分 中区分 小区分 重複無小の位置 大 中 小 並び順 大昇 中昇 小昇 合成Key 重複無Key 大区分 中区分 2 7/1 いいい cccccc 555 1 いいい cccccc 555 241 240 4 241240004 5 あああ AAAAAA 222 あああ#!#AAAAAA あああ#!#AAAAAA あああ AAAAAA 3 7/1 あああ BBBBBB 111 2 あああ BBBBBB 111 237 239 0 237239000 3 あああ AAAAAA 444 あああ#!#AAAAAA あああ#!#BBBBBB いいい BBBBBB 4 7/1 あああ dd 333 3 あああ dd 333 237 241 2 237241002 4 あああ BBBBBB 111 あああ#!#BBBBBB あああ#!#dd 大1 dd 5 7/1 大2 中22 小211 4 大2 中22 小211 245 246 240 245246240 10 あああ dd 333 あああ#!#dd いいい#!#cccccc 大2 cccccc 6 7/1 あああ AAAAAA 222 5 あああ AAAAAA 222 237 237 1 237237001 1 いいい cccccc 555 いいい#!#cccccc 大1#!#中11 中11 7 7/1 大1 中12 小122 6 大1 中12 小122 242 244 239 242244239 8 大1 中11 小112 大1#!#中11 大1#!#中12 中12 8 7/1 大1 中11 小112 7 大1 中11 小112 242 242 237 242242237 6 大1 中11 小121 大1#!#中11 大2#!#中21 中21 9 7/1 あああ AAAAAA 444 8 あああ AAAAAA 444 237 237 3 237237003 2 大1 中12 小122 大1#!#中12 大2#!#中22 中22 10 7/1 大1 中11 小121 9 大1 中11 小121 242 242 238 242242238 7 大2 中21 小212 大2#!#中21 11 7/1 大2 中22 小222 10 大2 中22 小222 245 246 243 245246243 12 大2 中22 小211 大2#!#中22 12 7/1 大2 中21 小212 11 大2 中21 小212 245 245 241 245245241 9 大2 中22 小221 大2#!#中22 13 7/1 大1 中11 小112 13 大2 中22 小221 245 246 242 245246242 11 大2 中22 小222 大2#!#中22 14 7/1 大2 中22 小221 15 7/1 大2 中22 小222
<省エネ手順> 新規シートの「シート見出し」を右クリックして、「コードの表示(V)」を選ぶと 画面中央に白いエリアが表れます。(VBE画面です）
その白いエリアに後記マクロをコピぺし、F5キーを押下してください (すると、マクロ「onlyOnce」が実行され、自動的にサンプルデータ、数式および入力規則が設定されます）
※実行は１回だけですので、上のF5を押したら直ぐ「Ctrl+Z」でコードを消去し、Alt+F11でエクセルに戻って下さい
Private Sub onlyOnce()
With Me
Rem 標準外書式セルをまとめて処理
.Range("A2:A29").NumberFormatLocal = "m/d;@"
Rem 生データのセルをまとめて処理
.Range("A1").Value = "日付"
.Range("B1,T1").Value = "大区分"
.Range("C1,U1").Value = "中区分"
.Range("D1").Value = "小区分"
.Range("F1").Value = "重複無小の位置"
.Range("G1").Value = "大"
.Range("H1").Value = "中"
.Range("I1").Value = "小"
.Range("M1").Value = "並び順"
.Range("O1").Value = "大昇"
.Range("P1").Value = "中昇"
.Range("Q1").Value = "小昇"
.Range("R1").Value = "合成Key"
.Range("S1").Value = "重複無Key"
.Range("A2:A15").Value = 43282
.Range("B2").Value = "いいい"
.Range("C2,H2").Value = "cccccc"
.Range("D2").Value = 555
.Range("B3:B4,B6,B9").Value = "あああ"
.Range("C3").Value = "BBBBBB"
.Range("D3").Value = 111
.Range("C4").Value = "dd"
.Range("D4").Value = 333
.Range("B5,B11:B12,B14:B15").Value = "大2"
.Range("C5,C11,C14:C15").Value = "中22"
.Range("D5").Value = "小211"
.Range("C6,C9").Value = "AAAAAA"
.Range("D6").Value = 222
.Range("B7:B8,B10,B13").Value = "大1"
.Range("C7").Value = "中12"
.Range("D7").Value = "小122"
.Range("C8,C10,C13").Value = "中11"
.Range("D8,D13").Value = "小112"
.Range("D9").Value = 444
.Range("D10").Value = "小121"
.Range("D11,D15").Value = "小222"
.Range("C12").Value = "中21"
.Range("D12").Value = "小212"
.Range("D14").Value = "小221"
Rem 数式セルをまとめて処理
.Range("F2:F250").FormulaR1C1Local = "=IF(R[-1]C="""","""",IFERROR(AGGREGATE(15,7,ROW(R1C1:R[997]C1)/(MATCH(R2C[-2]:R1000C[-2],R2C[-2]:R1000C[-2],0)=ROW(R1C1:R[997]C1)),ROW(R[-1]C1)),""""))"
.Range("G2,I2,G3:I250").FormulaR1C1Local = "=IF(RC6="""","""",INDEX(C[-5],RC6+1))"
.Range("J2:L250").FormulaR1C1Local = "=IF(RC6="""","""",COUNTIF(R2C[-3]:R250C[-3],""<""&RC[-3]))"
.Range("M2:M250").FormulaR1C1Local = "=IF(RC[-7]="""","""",RC[-3]*1000000+RC[-2]*1000+RC[-1])"
.Range("N2:N250").FormulaR1C1Local = "=IF(RC6="""","""",RANK(RC[-1],R2C13:R250C13,1))"
.Range("O2:Q250").FormulaR1C1Local = "=IF(RC6="""","""",INDEX(C[-8],MATCH(ROW(R[-1]C1),C14,0)))"
.Range("R2:R250").FormulaR1C1Local = "=IF(RC6="""","""",RC[-3]&""#!#""&RC[-2])"
.Range("S2:S50").FormulaR1C1Local = "=IF(R[-1]C="""","""",IFERROR(INDEX(R1C[-1]:R249C[-1],AGGREGATE(15,6,ROW(R2C18:R250C18)/(MATCH(R2C18:R250C18,R2C18:R250C18,0)=ROW(R1C18:R249C18)),ROW(R[-1]C1))),""""))"
.Range("T2:T20").FormulaR1C1Local = "=IF(R[-1]C="""","""",IFERROR(INDEX(R2C[-5]:R1000C[-5],AGGREGATE(15,7,ROW(R1C1:R[997]C1)/(MATCH(R2C[-5]:R250C[-5],R2C[-5]:R250C[-5],0)=ROW(R1C1:R[997]C1)),ROW(R[-1]C1))),""""))"
.Range("U2:U50").FormulaR1C1Local = "=IF(RC[-2]="""","""",REPLACE(RC[-2],1,FIND(""#!#"",RC[-2])+2,""""))"
Rem 入力規則設定
With .Range("B2:B1000").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:= _
"=OFFSET($T$1,1,0,COUNTIF($T$2:$T$20,""*?"")+LEN($D2)*0)"
.IgnoreBlank = True
.InCellDropdown = True
End With
With .Range("C2:C1000").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:= _
"=OFFSET($U$1,MATCH(B2&""#!#*"",$S$2:$S$11,0),0,COUNTIF($S$2:$S$11,B2&""#!#*"")+LEN($N$1)*0)"
.IgnoreBlank = True
.InCellDropdown = True
End With
With .Range("D2:D1000").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:= _
"=OFFSET($P$1,MATCH(C2,$P$2:$P$250,0),1,COUNTIF($P$2:$P$250,C2)+LEN($N$1)*0)"
.IgnoreBlank = True
.InCellDropdown = True
End With
.Range("A1").Select
End With
End Sub
（半平太） 2018/07/26(木) 20:54
こんな大変なものを作って頂いて本当にありがとうございます。
当初軽い気持ちでVBA無しでも出来るのではないか、と思っていましたが考えれば考えるほど複雑化していき、完成品はものすごい量になって驚いております。
一通りは見知った関数なので、頑張れば解読できそうですが今の所半分くらいしか理解出来ておりません。
実データに埋め込みながら確認していきたいと思います。
（にゅるん） 2018/07/26(木) 21:46
1.上記VBAではH2のセルに数式を書き込んでいませんがミスでしょうか
＞ .Range("G2,I2,G3:I250").FormulaR1C1Local = "=IF(RC6="""","""",INDEX(C[-5],RC6+1))"
下記のccccccが残ります。
＞ .Range("C2,H2").Value = "cccccc"
2.今まで気が付かなかったのですが、小区分や中区分が空欄となるものがありました。
そこでE列に文字列を合成した区分を作成し
=B2&"#"&C2&"#"&D2
F列の重複無小の位置をE列に変更しました。
=IF(F1="","",IFERROR(AGGREGATE(15,7,ROW($A$1:$A999)/(MATCH(E$2:E$1000,E$2:E$1000,0)=ROW($A$1:$A999)),ROW($A1)),""))
代わりに選択肢に"0"が現れるのですが、選択肢としては表示されないようにする方法はありますか？
申し訳ございませんが、よろしくお願いいたします。
（にゅるん） 2018/07/27(金) 09:03
＞1.上記VBAではH2のセルに数式を書き込んでいませんがミスでしょうか
＞＞ .Range("G2,I2,G3:I250").FormulaR1C1Local = "=IF(RC6="""","""",INDEX(C[-5],RC6+1))"
＞下記のccccccが残ります。
済みません。数式が値になっていました。
H2セル =IF($F2="","",INDEX(C:C,$F2+1))
＞代わりに選択肢に"0"が現れるのですが、
エクセルの仕様で、空白セルを参照すると「0」が表示される現象です。
G2セル =IF($F2="","",INDEX(B:B,$F2+1)&"") H2セル =IF($F2="","",INDEX(C:C,$F2+1)&"") I2セル =IF($F2="","",INDEX(D:D,$F2+1)&"") ~~↑~~ 空白文字化する
＞選択肢としては表示されないようにする方法はありますか？
有るような、無いような感じですね。
・・と言うのは、空白は履歴(事実上受け入れられたデータ）になっていますので、 空白も選択肢として受け入れるのが正しいのではないかと思います。
あえて空白を排除するなら、空白は各区分の最上段に来ているハズなので、 最上段が空白なら、オフセット値を1行増やし、行高を一つ減らせばいいと思われます。
C2セルの入力規則 ↓ =IF(OFFSET($U$1,MATCH(B2&"#!#*",$S$2:$S$11,0),0)="",OFFSET($U$1,MATCH(B2&"#!#*",$S$2:$S$11,0)+1,0,COUNTIF($S$2:$S$11,B2&"#!#*")-1+LEN($N$1)*0),OFFSET($U$1,MATCH(B2&"#!#*",$S$2:$S$11,0),0,COUNTIF($S$2:$S$11,B2&"#!#*")+LEN($N$1)*0))
泥縄な数式ですが、スマート化を考える以前の問題として、 この根本的な前提から外れ始めた訳ですよね？ ↓ ＞小区分が複数の中区分に、中区分が複数の大区分に存在することはありません。
そうだとすると、私の案は使い物にならなくなった可能性があります。
私としては、再検討する気力も起きないので、これ以上深入りできません。
（半平太） 2018/07/27(金) 10:10
0が出るのは”仕様です”と言い切る方向でなんとかしたいと思います。
ありがとうございました。
（にゅるん） 2018/07/27(金) 10:20
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.