INDIRECT、MATCH関数等の組み合わせ方

質問

入力フォームとして作成したシート（あああ）のデータを、シート（一覧表）に、計算式で自動的に並べる方法（参照する方法）を教えてください。（画像を添付しました。）
入力フォームは、実際は150枚ぐらいになります。一覧表も実際は数枚あります。

画像の「あああ」が入力フォーム、「一覧表」が一覧表です。
あああ、AAA、111、アアアなどは、すべて項目名です。
DATA1からDATA32、DATA100-103が、実際にデータが入る部分です。

例えばですが、入力フォーム（あああ）のセルC6に入力したDATA1が、一覧表のセルC3に入るように、C3に計算式を入れたいです。

１．シート名を計算式にいれるには、INDIRECT関数が使えるかと思います。
２．複数の条件（A列、B列、1行、2行）でセルを特定する関数がわかりません。
３．１．と２．を組み合わせると、同様な関数になるのか、わかりません。

DATAについては、ゼロはゼロ、無記入は無記入のまま一覧表に表示したいです。

よろしくお願いします。

回答No.6

kagakusuki

>「AAA」「111」などはいずれも文字列
>「ＡＡＡ」などの中項目ごとの小項目名５５５、６６６、７７７、８８８は、すべて同じです。

という条件が入力フォーム用のどのシートのどの中項目においても成り立っていて、「ＡＡＡ」をはじめとする各中項目は何れも必ず4行(５５５、６６６、７７７、８８８が各1行ずつ)使用していて、尚且つ、「111」や「222」等のC列～D列の項目が必ず5行目に入力されているという場合には、以下の様な方法を使う事が出来ます。

　まず、一覧表シートのA2セルに例えば「[シート名]」等の「そのExcelBook内にあるシートのシート名としては使用されていない(使用される可能性がない)何らかの値」を入力しておく様にして下さい。
　次に、一覧表シートにおいて使用していない適当な列(ここでは仮にL列とします)の1行目のセル(L1セル)に次の関数を入力して下さい。

=$A$2

　次に、一覧表シートのL2以下に「あああ」、「いいい」、「ううう」、「えええ」、「おおお」等の各入力フォーム用のシートのシート名の一覧を途中に空欄を挟む事がない様に入力して下さい。
　次に、一覧表シートのA3セルに次の関数を入力して下さい。

=IF(A2="","",IF(ISERROR(1/(COUNTIF(A$2:A2,A2)<(COUNTIF(INDIRECT("'"&A2&"'!A:A"),"*?")-COUNTIF(INDIRECT("'"&A2&"'!A1:A5"),"*?"))/COUNTIF($1:$1,$C$1))),INDEX($L:$L,MATCH(A2,$L:$L,0)+1)&"",A2))

　次に、一覧表シートのA3セルをコピーして、一覧表シートのA4以下に貼り付けて下さい。
　次に、一覧表シートのC3セルに次の関数を入力して下さい。

=IF($B3="","",IF(ISERROR(1/(INDIRECT("'"&$A3&"'!R"&MATCH(C$2,OFFSET(INDIRECT("'"&$A3&"'!B"&MATCH($B3,INDIRECT("'"&$A3&"'!A:A"),0)),,,COUNTIF(INDIRECT("'"&$A3&"'!A:A"),$B3)),0)+MATCH($B3,INDIRECT("'"&$A3&"'!A:A"),0)-1&"C"&MATCH(C$1,INDIRECT("'"&$A3&"'!5:5"),0),FALSE)<>"")),"",INDIRECT("'"&$A3&"'!R"&MATCH(C$2,OFFSET(INDIRECT("'"&$A3&"'!B"&MATCH($B3,INDIRECT("'"&$A3&"'!A:A"),0)),,,COUNTIF(INDIRECT("'"&$A3&"'!A:A"),$B3)),0)+MATCH($B3,INDIRECT("'"&$A3&"'!A:A"),0)-1&"C"&MATCH(C$1,INDIRECT("'"&$A3&"'!5:5"),0),FALSE)))

　次に、一覧表シートのC3セルをコピーして、一覧表シートのC3～J3のセル範囲に貼り付けて下さい。
　次に、一覧表シートのC3～J3のセル範囲をコピーして、一覧表シートのC列～J列の4行目以下に貼り付けて下さい。

　以上です。

回答No.5

Nouble

おっと！　済みません
誤記を見つけました

誤記
char(code(a)-1+match(…

正記
char(code(" a" )-1+match(…

済みません。　はぃ

回答No.4

Nouble

indirect関数を使う時は
"あああ!a3:a"&match(…
と、しても
使えますが

address関数と組み合わせる手も
ありますよ

此だと
行特定、列特定、各々に
マッチ関数を用いれば良く
括弧のネストが深くなる欠点はありますが
利便性は上がりますよね

indirect(address(match( …),match(…),…))
とね

でも、抑も
indirect(address(
と、するなら
offset(
と、した方が楽ですよ

と、言う事で
私は滅多に
indirect構文は、使いませんよ

使うとすれば
其の、実作業空間に
置かれている内の
幾台かのPCの、エクセルが
何らかに、侵されて
office構文の
正常動作が、望み薄い
と、感じた時
位、ですね

後は、
列の位置の、指定法
ですが
文字で、指定する時
どの文字に、当たるか　の
割り出しが、厄介　ですよね

此はですね
char(code(a)-1+match(…
と、すれば求まりますよ

でも、此
address構文やoffset構文には
無用、無駄、
の、努力
ですよ

indirect構文に、何故　拘ります？

お礼

十分に理解できるに至っていませんが、知らなかった関数をご教授いただきましたので、自分でもう少し調べてみようと思います。ありがとうございました。

回答No.3

Nouble

説明と映像の関連性が全く解らず、
困惑したのですが

映像を「正」として
縦、横の、表題に一致したものを
探し出し、表す
と、言う事で良い
ので、しょうか？

お礼

ありがとうございます。質問もこなれていなく、このままにしておくのもみなさんに大変申し訳無いので、間もなく質問を締めたいと思います。「あああ」のシートの構造を変えるなどして、参照が簡単にできるように組み直す方向で検討しようと思います。

補足

はい、全く分かりにくい質問文で申し訳ありません。

「あああ」のシートの「DATA1」などが、「一覧表」の「DATA1」などの位置に表示されればOKです。

　よろしくお願いいたします。

回答No.2

imogasi

以下、ややこしいと思ったら無視してください。
#1です。其の後も考えて疑問点が出た。回答ではなく、一種のメモにすぎませんが。疑問点もわいてきたので、後の回答者の参考に、その気があれば補足しておいてください。
ーー
やっと、やりたいことの輪郭がわかったような気がする。
しかしエクセル関数では、とても小生の手に負えない。
回答が出ていないのは、関数での回答がややこしく、複雑になるのは確実で、やってられないからではないかと推定する。
ーーー
（疑問点）
・Ａ．出現データの実情に従うのか、Ｂ．出てくる組み合わせが（画像例のように）決まっている、としてよいのか。はっきりさせてほしい。
・ＤＡＴＡ１，・・ＤＡＴＡｘは、具体的には計数か文字列・コードか、数字データの合計数か
ーーー
（１）シート数だけの繰り返し　あああー＞いいいー＞ううう・・　　Ａタイプ
（２）1つのシートのＡ列の各行の繰り返し　ＡＡＡー＞ＢＢＢー＞ＣＣＣー＞ＤＤＤ・・・　Ａタイプ
（３）各シートのＢ列の繰り返し　　５５５－＞６６６ー＞７７７・・・　　　　　　　　ＡタイプかＢタイプか不明
（４）第５行目Ｃ列から右列への繰り返し探索で、現れるコードを　　１１１ー＞２２２ー＞３３３・・・
　　　他の―とでは現れないコードもあるのか（出現データに従うのか。）　　　　　ＡタイプかＢタイプか不明
ーー
・そして範囲（列）を限って、重複しないデータを作る。（Ｂ列と第５行について。）
・例えばＡ列での重複しない値のＡＡＡ，ＢＢＢ、ＣＣＣを見つけて一覧表のＢ列に持ってくる。
・その数だけＡ列にシート名を持ってくる。関数では、現シート名を取り出すのは長い式になる。
・例示画像の「あああ」シートのＢ列の「555,666,777,888」の組はＢＢＢ、ＣＣＣ・・においても同じ
コードか文字のくみあわせか。出現データに従うのか。
（画像例では同じコードの組み合わせのようになっているが）違うコードも混じる、出てこない
コードもあるのか。

ーー
（１）はＶＢＡでは簡単
Sub test01()
For Each sh In Worksheets
sn = sh.Name
MsgBox sn
’ここに１シート対象の具体的な処理のコードが入る
Next
End Sub
（２）は、ややむつかしい。
出現データに基づいて、「重複なしのデータを抽出するいろいろな方法」
参考　http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_jyufuku.html　
・フィルタオプションの設定を利用する　　　
Sub myAd()
Dim rngData As Range, rngC As Range
With Worksheets("Sheet1")
Set rngData = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
Set rngC = Worksheets("aaa").Range("E1")
rngData.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=rngC, _
Unique:=True
End With
End Sub
重複のないデータの組がセル範囲に出るので、「一覧表」のそれぞれの所定のセルに移す。
・ＤＡＴＡｘのセル以外は何とかＶＢＡで作れそうだ。

補足

ありがとうございます。m(__)m

＞・Ａ．出現データの実情に従うのか、Ｂ．出てくる組み合わせが（画像例のように）決まっている、としてよいのか。はっきりさせてほしい。

「あああ」「AAA」「111」などはいずれも文字列（項目名）です。
「あああ」のシートには、小項目として、「AAA」から「DDD」まで4項目あり、「いいい」以下のそれぞれの小項目数は、1から5項目程度あり、小項目名の種類はトータルで50ぐらいあります。

＞・ＤＡＴＡ１，・・ＤＡＴＡｘは、具体的には計数か文字列・コードか、数字データの合計数か

大多数が数字なのですが、一部文字列の場合があります。文字列は少ないので、その部分だけは一覧表には手入力で計算式を上書きしてしまう、などでの対応が可能です。

ーーー
（１）シート数だけの繰り返し　あああー＞いいいー＞ううう・・　　Ａタイプ
→　はい、そのとおりだと思います。
（２）1つのシートのＡ列の各行の繰り返し　ＡＡＡー＞ＢＢＢー＞ＣＣＣー＞ＤＤＤ・・・　Ａタイプ
→　はい、そのとおりだと思います。「いいい」以下は、一覧表の例の通り、５０程度の中項目からいくつかが当てはまります。
（３）各シートのＢ列の繰り返し　　５５５－＞６６６ー＞７７７・・・　　　　　　　　ＡタイプかＢタイプか不明
→　Ｂタイプということだと思います。「ＡＡＡ」などの中項目ごとの小項目名５５５、６６６、７７７、８８８は、すべて同じです。
（４）第５行目Ｃ列から右列への繰り返し探索で、現れるコードを　　１１１ー＞２２２ー＞３３３・・・
　　　他の―とでは現れないコードもあるのか（出現データに従うのか。）　　　　　ＡタイプかＢタイプか不明
→　Ｂタイプだと思います。「１１１」「２２２」列以外はありません。すべてのシートに、「３３３」が追加されるかもしれない程度です。

「あああ」などは組織名で、「ＡＡＡ」などはその組織にある属性、例えば、新宿店、川崎店、六本木ヒルズ店など、一般名詞的なものとび固有名詞的なものの混在です。）、
「５５５」「６６６」などは、例えば、「売上高」「人件費」「物件費」「経常利益」などに相当します。
「１１１」は、例えば昨年度、「２２２」は、例えば今年度、などに相当します。
「アアア」「イイイ」などは、住所、電話番号、などに相当します。

「相当します」としているのは、実際には別の項目名なのですが、同様に当てはめられる項目名を考えてみたものです。

（エクセルシートを、「あああ」を「セブンイレブン」など、「ＡＡＡ」を「新宿店」などに差し替えてよりイメージしやすくしたものを作りましたが（表形式は全く同じ）、画像の再アップ方法がわかりませんでした。m(__)m　）

回答No.1

imogasi

表の組替えの問題のようだ。
質問者はエクセルは関数しかできなくて、関数で処理したいのだろうが、該当数が不定で
あるような場合は、式が異常に複雑になるように思う。
該当数が不定だと（組換え後の表の）行き先（セル番地）が不定（他の機会のデータシートの場合は位置が変わる）でややこしくなる。
エクセルの（メニュー化された）操作でも表の組換えはあまり用意がないように思う。
関数は持ってくるセルに関数を入れなければならないから。
ーー
一方ＶＢＡでは、持ってゆくべきセルに、データを代入できるし、数に応じてずらすこともできる。
突き詰めると「変数」が使えるというメリットが使える点だと思う。
ＶＢＡ向きの問題だと思う。ＶＢＡを勉強すべき課題と思う。
ーー
質問内容の説明も十分でないように思う。理解に時間がかかる。
質問が出て時間がたつが、回答がゼロなどの原因ではないか。