出産前後の痔にはご注意!

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

Sheet1~Sheet15が存在します。Sheet2~Sheet15には3列で行がそれぞれ異なる表があります。例えばSheet1は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個もまたいで検索したい時にIFで作るとエクセル2003なので入れ子が7個以上になってしまいうまくいきません。

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

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

このQ&Aに関連する最新のQ&A

A 回答 (6件)

作業用のシートを一つ用意して対応するのがよいでしょう。


シート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))
    • good
    • 0
この回答へのお礼

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

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

=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)))))


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

お礼日時:2011/12/19 20:39

こんばんは!


すでの回答は出ていますので、参考程度で・・・
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
    • good
    • 0
この回答へのお礼

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

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

お礼日時:2011/12/19 20:42

シート2にはすべて「AAA」が入り,シート3には全て「BBBのなんたら」が来ると言うことですか?



もしそういうお話なら,シート1に
J列   K列
AAA Sheet2
BBB Sheet3
CCC Sheet4

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



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


ケース2:ホントのデータは「AA1」だったり「BBBB33」だったり不定なら
B2:
=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)
    • good
    • 0
この回答へのお礼

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

お礼日時:2011/12/19 20:43

>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)))
「エクセル関数を使って検索したいです。」の回答画像4
    • good
    • 0
この回答へのお礼

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

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

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

お礼日時:2011/12/19 20:44

ごたごた文章が多く、普通のエクセルの約束と違う表現で、質問の意味が掴みにくい。


3シートぐらいの例にして実例でもあげたらどうです。
>A列(1行目
と書かず、Sheet1のA1と書くのが慣わしです。こんなことも慣れてないのかな。
検索語 Sheet1!A1
式を入れるセル Sheet1!B2
検索する範囲 Sheet2!A:AからSheet15!A:A  <--A列だけの中の検索で良いのか?
ーー
検索した結果見つかったとして、どう結果を返すのか?有り無しだけ?
普通は見つかったセルの同行右列などのデータがほしい場合が多いよ。
ーー
検索結果は書くシートのA列で複数行に見つかりそうなのかどうか?
大切な点。複数ありそうな場合は関数では特に難しい。
ーーー
その後データ例を挙げること
Sheet1!A1 AAA1やBBB2、これは敢えて書く必要は無いと思う。
>Sheet2”A:A にはAAA1、AAA2、AAA3、AAA4の
Sheet3!A:A にはSheet2のA列にはBBB1、BBB2、BBB3、BBB4
質問では>Sheet2のA列にはBBB1、BBB2、BBB3、BBB4のようになっているがSheet3の誤りだろう。
それとAAAA1が検索語の場合、「含む」場合の、AAAA12などは探すのかどうか大切な点がかかれていない。
ーー
以上の質問表現の書き方を参考にして、質問の場合の文章は良く考えて。それにはもっと色んな勉強(要所とかパターンとかの学習)が必要。
ーーーー
本件は関数では複雑で難しいものになると思う。
VBAでも勉強し無いと難しいが、それも簡単ではない。
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
のようなのを標準モジュールに書いて、シートにコマンドボタンでも設けて、クリックしたとき検索
のようなのをさせることになるのかな。
Sheet1のイベントモジュールで
Private Sub CommandButton1_Click()
test01
End Sub
ーーーー
上記はA列には1つしか無いと仮定できる場合の話。
複数行にヒットする仕組みのデータ場合は関数では難しいし、VBAでも多少コード変更が必要。
    • good
    • 0
この回答へのお礼

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

お礼日時:2011/12/19 20:45

回答1です。


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

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

に変更してください。
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!


人気Q&Aランキング