やりたいことは以下の通りです。
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の要に入力された状態になっています。
どなたかうまく切り抜ける方法を教えてください。
No.6
- 回答日時:
こんばんは!
すでの回答は出ていますので、参考程度で・・・
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
- 回答日時:
シート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)
No.4
- 回答日時:
>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
- 回答日時:
ごたごた文章が多く、普通のエクセルの約束と違う表現で、質問の意味が掴みにくい。
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でも多少コード変更が必要。
No.2
- 回答日時:
回答1です。
シート16での入力の式が一部間違っていました。
次にA3セルには次の式を入力して右横方向にオートフィルドラッグします。
=IF(A2="","",SUM($A$2:A2))
に変更してください。
No.1ベストアンサー
- 回答日時:
作業用のシートを一つ用意して対応するのがよいでしょう。
シート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)))))
がどうしても理解できません。
少し解説をしていただけると嬉しいです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBAでvlookup関数から、別シート参照するやり方・・・ 2 2022/11/14 18:49
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Visual Basic(VBA) VBA 別sheetからの転記なのですが 2 2023/05/22 15:55
- Visual Basic(VBA) Sheet「状況」から、分類の年齢別カウント数をSheet「D表」へ転記する下記マクロを作っています 7 2022/12/14 17:57
- その他(プログラミング・Web制作) python文字化けエラーが発生しているようです 3 2022/04/13 19:41
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Visual Basic(VBA) 【変更】ファイルを閉じてダイアログで保存した時、更新したシートだけの処理の実行をする 5 2022/03/26 18:31
- その他(Microsoft Office) オフィス365のエクセル 3 2023/06/29 13:10
- Excel(エクセル) Excelにて、行の最後のセルの値をコピーして別sheetに張りつけるVBAコードをご教授願います 3 2022/11/20 14:35
- Excel(エクセル) エクセルでのコピーペースト 6 2022/09/03 07:14
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでの作業計算方法について
-
はがきについて。
-
エクセル 文字を増やしたい。
-
セルの内容表示が邪魔になる
-
Microsoft365に変えたのですが...
-
エクセルの計算
-
Microsoft1Officeの互換ソフト...
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
エクセル初心者です 関数の入れ...
-
Excel ピボットテーブルで日付...
-
【関数】適切な文字数の数字を...
-
LOOKUP関数を使えばいいのでし...
-
Aというブックの1というシート...
-
エクセル関数を教えてください
-
Excelのチェックボックスの使い...
-
エクセル 白黒印刷で白線を印刷...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの条件付き書式につい...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報