エクセル関数を使って検索したいです。

質問

やりたいことは以下の通りです。

Sheet1～Sheet15が存在します。Sheet2～Sheet15には3列で行がそれぞれ異なる表があります。例えばSheet１は5行3列、Sheet2は10行3列、Sheet4は30行3列のように。ちなみにこれらの表は行が追加されていきます。

Sheet1にも同様に3列の表があるのですが、Sheet1のB列(2行目)に、式を作りたいです。
どんな式かと言うと、Sheet1のA列(1行目)に値を入れると、それと同じ値を、Sheet2～Sheet15のA列（1行目）と同じものを探し、そのSheetのB列（2行目）の値を返すというようにしたいです。

VLOOKUPを使うといいかと思うのですが、Sheetを14個もまたいで検索したい時にＩＦで作るとエクセル2003なので入れ子が7個以上になってしまいうまくいきません。

ちなみに、Sheet1に入力する値はAAA1やBBB2の様にアルファベットと数字の組み合わせです。
よって、例えばSheet2のＡ列にはAAA1、AAA2、AAA3、AAA4の様に、Sheet2のA列にはBBB1、BBB2、BBB3、BBB4の要に入力された状態になっています。

どなたかうまく切り抜ける方法を教えてください。

回答No.1

KURUMITO · ベストアンサー

作業用のシートを一つ用意して対応するのがよいでしょう。
シート2からシート15まででそれぞれのシートでのデータの数はそれほど多くないのですからそれらのすべてのデータを例えばシート16に表示させるようにして、シート16を利用してシート1に表示させるようにします。
なおそれぞれのシートは1行目は項目名で2行目から下方にデータが入力されているとします。
初めにシート16での作業を説明します。
A1セルから右横のセルに順番にSheet2,Sheet3,Sheet4・・・・・・Sheet15といったシート名を入力します。
次にA2セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(A1="","",COUNTA(INDIRECT(A1&"!A2:A1000")))

これでそれぞれのシートに入力されているデータの行の数が表示されます。
次にA3セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(A2="","",SUM($A$2:A2)/3)

5行目には各シート共通の項目名を入力します。3列までの項目名を入力します。
A6セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(OR(ROW(A1)>MAX($3:$3),COLUMN(A1)>3),"",IF(ROW(A1)<=MIN($3:$3),INDEX(INDIRECT($A$1&"!A2:D1000"),ROW(A1),COLUMN(A1)),IF(COUNTIF($3:$3,ROW(A1)),INDEX(INDIRECT(INDEX($1:$1,MATCH(ROW(A1),$3:$3,0))&"!A2:D1000"),ROW(A1)-INDEX($3:$3,MATCH(ROW(A1),$3:$3,0)-1),COLUMN(A1)),INDEX(INDIRECT(INDEX($1:$1,MATCH(ROW(A1),$3:$3,1)+1)&"!A2:D1000"),ROW(A1)-INDEX($3:$3,MATCH(ROW(A1),$3:$3,1)),COLUMN(A1)))))

これで5行目以降には各シートのデータがまとめて表示されます。勿論各シートに新たなデータが追加されることが有っても即座にデータが追加されます。

最後はシート16を元にシート1を完成させればよいわけです。
シート1のB1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(ROW(A1)>MAX(Sheet16!$3:$3),COUNTIF(Sheet16!$A:$A,A2)=0,COLUMN(A1)>3),"",VLOOKUP(A2,Sheet16!$A:$C,2,FALSE))

お礼

ありがとうございました。思うようになりました。

もし良ければ少しおしえてください。

=IF(A1="","",COUNTA(INDIRECT(A1&"!A2:A1000")))

と

=IF(OR(ROW(A1)>MAX($3:$3),COLUMN(A1)>3),"",IF(ROW(A1)<=MIN($3:$3),INDEX(INDIRECT($A$1&"!A2:D1000"),ROW(A1),COLUMN(A1)),IF(COUNTIF($3:$3,ROW(A1)),INDEX(INDIRECT(INDEX($1:$1,MATCH(ROW(A1),$3:$3,0))&"!A2:D1000"),ROW(A1)-INDEX($3:$3,MATCH(ROW(A1),$3:$3,0)-1),COLUMN(A1)),INDEX(INDIRECT(INDEX($1:$1,MATCH(ROW(A1),$3:$3,1)+1)&"!A2:D1000"),ROW(A1)-INDEX($3:$3,MATCH(ROW(A1),$3:$3,1)),COLUMN(A1)))))

がどうしても理解できません。
少し解説をしていただけると嬉しいです。

回答No.6

tom04

こんばんは！
すでの回答は出ていますので、参考程度で・・・
VBAでの一例です。
画面左下のSheet1のSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてSheet1のA列にデータを入力してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim i, k As Long
If Intersect(Target, Columns(1)) Is Nothing Or Selection.Count <> 1 Then Exit Sub
For i = 2 To Worksheets.Count
If WorksheetFunction.CountIf(Worksheets(i).Columns(1), Target) Then
k = WorksheetFunction.Match(Target, Worksheets(i).Columns(1), False)
Exit For
End If
Next i
If k > 0 Then
Target.Offset(, 1) = Worksheets(i).Cells(k, 2)
Else
Target.Select
MsgBox "該当データがありません。" & vbCrLf & "再入力してください。"
Exit Sub
End If
End Sub 'この行まで

※　関数でないので、ご希望の方法でなかったらごめんなさいね。m(_ _)m

お礼

ありがとうございました。
VBAでもいいと思うのですが、関数でという指示があったもので。

ただこのすごくVBAも参考になりました。

回答No.5

keithin

シート２にはすべて「ＡＡＡ」が入り，シート３には全て「ＢＢＢのなんたら」が来ると言うことですか？

もしそういうお話なら，シート１に
Ｊ列　　　Ｋ列
ＡＡＡ　Sheet2
ＢＢＢ　Sheet3
ＣＣＣ　Sheet4

のようにインデックスと該当するシートの名称の対応表を用意しておけば，少しは作業が楽になりますね。

ケース１：ご質問の例示が事実で「ＡＡＡ」のように必ずアルファベット３文字だというなら
Ｂ２：
=VLOOKUP(A2,INDIRECT(VLOOKUP(LEFT(A2,3),J:K,2,FALSE)&"!A:C"),2,FALSE)

ケース２：ホントのデータは「ＡＡ１」だったり「ＢＢＢＢ３３」だったり不定なら
Ｂ２：
=VLOOKUP(A2,INDIRECT(INDEX($K$1:$K$15,SUMPRODUCT(LEFT(A2,LEN($J$1:$J$15))=$J$1:$J$15)*ROW($K$1:$K$15))&"!A:C"),2,FALSE)

お礼

ありがとうございました。アルファベット３文字プラス数字の組み合わせに必ずなります。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

>Sheet2～Sheet15のA列（1行目）と同じものを探し、そのSheetのB列（2行目）の値を返すというようにしたいです
1行ずらすのでしょうか？

ずらさないと仮定し
A2セルに 検索値

C2セルに
=IF(A2="","",MAX((CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13,14},
Sheet2!$A$1:$A$100,Sheet3!$A$1:$A$100,Sheet4!$A$1:$A$100,Sheet5!$A$1:$A$100,Sheet6!$A$1:$A$100,Sheet7!$A$1:$A$100,
Sheet8!$A$1:$A$100,Sheet9!$A$1:$A$100,Sheet10!$A$1:$A$100,Sheet11!$A$1:$A$100,Sheet12!$A$1:$A$100,Sheet13!$A$1:$A$100,
Sheet14!$A$1:$A$100,Sheet15!$A$1:$A$100)=Sheet1!A2)*ROW($A$1:$A$100)*100+{1,2,3,4,5,6,7,8,9,10,11,12,13,14}))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる
3ケタ以上の値が出る

B2セルに
=IF(A2="","",INDEX(CHOOSE(MOD(C2,100),Sheet2!$B$1:$B$100,Sheet3!$B$1:$B$100,Sheet4!$B$1:$B$100,Sheet5!$B$1:$B$100,Sheet6!$B$1:$B$100,Sheet7!$B$1:$B$100,
Sheet8!$B$1:$B$100,Sheet9!$B$1:$B$100,Sheet10!$B$1:$B$100,Sheet11!$B$1:$B$100,Sheet12!$B$1:$B$100,Sheet13!$B$1:$B$100,
Sheet14!$B$1:$B$100,Sheet15!$B$1:$B$100),INT(C2/100)))

お礼

すみません。書き方が悪かったと思います。
１行ずれたりはしません。

画像つきでわかりやすかったです。

ありがとうございました。

回答No.3

imogasi

ごたごた文章が多く、普通のエクセルの約束と違う表現で、質問の意味が掴みにくい。
３シートぐらいの例にして実例でもあげたらどうです。
＞A列(1行目
と書かず、Ｓｈｅｅｔ１のＡ１と書くのが慣わしです。こんなことも慣れてないのかな。
検索語　Ｓｈｅｅｔ１！Ａ１
式を入れるセル　Ｓｈｅｅｔ１！Ｂ２
検索する範囲　Ｓｈｅｅｔ２！Ａ：ＡからＳｈｅｅｔ１５！Ａ：Ａ　　＜－－Ａ列だけの中の検索で良いのか？
ーー
検索した結果見つかったとして、どう結果を返すのか？有り無しだけ？
普通は見つかったセルの同行右列などのデータがほしい場合が多いよ。
ーー
検索結果は書くシートのＡ列で複数行に見つかりそうなのかどうか？
大切な点。複数ありそうな場合は関数では特に難しい。
ーーー
その後データ例を挙げること
Ｓｈｅｅｔ１！Ａ１　AAA1やBBB2、これは敢えて書く必要は無いと思う。
＞Sheet2”Ａ：Ａ　にはAAA1、AAA2、AAA3、AAA4の
Ｓｈｅｅｔ3！Ａ：Ａ　にはSheet2のA列にはBBB1、BBB2、BBB3、BBB4
質問では＞Sheet2のA列にはBBB1、BBB2、BBB3、BBB4のようになっているがＳｈｅｅｔ３の誤りだろう。
それとＡＡＡＡ１が検索語の場合、「含む」場合の、ＡＡＡＡ１２などは探すのかどうか大切な点がかかれていない。
ーー
以上の質問表現の書き方を参考にして、質問の場合の文章は良く考えて。それにはもっと色んな勉強（要所とかパターンとかの学習）が必要。
ーーーー
本件は関数では複雑で難しいものになると思う。
ＶＢＡでも勉強し無いと難しいが、それも簡単ではない。
Sub test01()
x = Worksheets("Sheet1").Range("A1")
For Each sh In Worksheets
If sh.Name <> "Sheet1" Then
Set y = sh.Range("A:A").Find(x)
If y Is Nothing Then
MsgBox "not found"
Else
MsgBox "シート名" & sh.Name & の"" & y.Row & "行"End If
End If
Next
End Sub
のようなのを標準モジュールに書いて、シートにコマンドボタンでも設けて、クリックしたとき検索
のようなのをさせることになるのかな。
Ｓｈｅｅｔ１のイベントモジュールで
Private Sub CommandButton1_Click()
test01
End Sub
ーーーー
上記はＡ列には１つしか無いと仮定できる場合の話。
複数行にヒットする仕組みのデータ場合は関数では難しいし、ＶＢＡでも多少コード変更が必要。

お礼

すみませんでした。
素人なもので。
表現の仕方等もっと勉強します。

回答No.2

KURUMITO

回答1です。
シート16での入力の式が一部間違っていました。
次にA3セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(A2="","",SUM($A$2:A2))

に変更してください。