複数シートのデータを抽出するには？

Excel表にある複数シートの、複数行のデータを抽出したいのですが、マクロが使えず、関数でできないものかと模索中です。

具体的には、参照したいシート毎の、品名に応じた量を出したいのですが、
検索したいシートも、品名も、150前後あります。
シートが一つまたは品名（検索したい列か行）が1つから、vlookupやindirect で抽出できそうなのですが、両方のボリュームがあるので、上手くいかず。
（説明が下手ですみません。添付写真で伝わると良いのですが、、）
関数などを利用して、あまり知識のないものでも出来そうな方法はありますか？

質問者からの補足コメント

• 急に電波が悪くなり、2枚目以降の写真が遅くなりました。

  
    補足日時：2021/07/03 14:57

• これで、なんとなく理解していただけるとよいのですが、、

  
    補足日時：2021/07/03 14:58

• 説明の追加です。
  ・複数のシートをまとめて集計するのではなく、各列の参照先は2行目で選択したシートで、
  Ａ列で選択した品名に対応する値（量）に、回数をかけたものをB列に表示させたいのです。
  
  ちなみに、
  B8に=INDIRECT(B$2&”!C3”)*B$6
  とやれば上手く出たのですが、そのままコピーすると
  =INDIRECT(B$2&”!C3”)*B$6
  となり、全てがB2セルで指定したシートのC3セルの値掛ける回数(B6)となってしまうこと。
  
  また、できればA行で指定した値をB列に返すようにしたくて、その単体での式は
  ＝VLOOKUP(A8,A!B3:C7,2,FALSE)*B6
  かなと思ったのですが、
  それぞれの式をどう組み合わせて良いのかわからず困っています。
  （やっぱり説明が下手ですみません）
  宜しくお願いします。

    補足日時：2021/07/03 19:33

• ありがとうございます！！No3で教えていただいた方法（INDIRECT）で上手くいきました！
  実は、今までエクセル自体は使っていたものの、SUM関数くらいしか使う必要性がなかったのですがもう少し利用できる範囲を広げ、今の仕事をもう少し効率よくこなしたいなと、一から勉強中です。
  コピーの際の数式の変化する原理もよくわからず、どう学べばよいのやら、、
  
  上記で出来たけれど、再計算の時間がかかるのは本意ではないので、教えていただいた別な方法を試したいのですが、
  初っ端な
  （例えばB列の）参照式を「=」なしでどこかのセルに記入します。その中のシート名の部分を記号（例えば##とか）に変えておきます。
  の部分がよくわからず、どうしてよいのか、わからなくて、、
  一から（マイナスから？）聞いてしまいすみません。

  No.3の回答に寄せられた補足コメントです。 補足日時：2021/07/04 12:29

No.3 ベストアンサー 回答者： fujillin 回答日時： 2021/07/03 22:05

No2です。

＞B8に=INDIRECT(B$2&”!C3”)*B$6
＞とやれば上手く出たのですが、そのままコピーすると
＞=INDIRECT(B$2&”!C3”)*B$6となり～
なんで、そういう式になるのでしょうか？？

単に、列をそのまま順にコピーしたいのなら、
　=OFFSET(INDIRECT(B$2 & "!C1"),ROW(A2),0)
とでもしておけば良いでしょう。
セルのコピーの際の数式の変化する原理を理解するようにしてください。


＞＝VLOOKUP(A8,A!B3:C7,2,FALSE)*B6
＞かなと思ったのですが、
なぜ、そちらの式を使おうと思わないのでしょうね？？
No2での例示も「A!B:C」とセル範囲で示してしてあるのに・・・

仮にそのまま、
　=VLOOKUP(A8,INDIRECT(B$2 & "!B:C"),2,FALSE) *B6
とでもしておけば良いのでは？？
（セル範囲は適切な範囲に調整したほうが良いですが、範囲が不明なので…）

とは言っても、No2に記しておいたように、INDIRECT関数の多用はお勧めするものではありません。
（どこかのシートでセル値を変更する毎に再計算が発生します）
それなので、ちょっとだけ手間ですが二番目の方法の方がお勧めです。
（手間と言っても、実質的にはセル範囲のコピペが一回と、置換が一回増えるだけの差）


＞（やっぱり説明が下手ですみません）
回答者には質問文が全てです。
やりたいことを正しく伝えられなかったり、情報が不足していれば、求める回答は得られません。
増してや、セルの位置関係や範囲を正確に伝えられなければ、コピペできるような回答は到底望めません。
結果的に抽象的な回答になるので、質問者様が意味を理解して、実際の内容に応用するしかないことになります。

この回答へのお礼

やっと意味を理解できました！！
substituteいろんなところで使えそうですね。
拙い説明の質問に、丁寧な回答をくださり、ありがとうございました！

お礼日時：2021/07/04 13:30

No.5 回答者： mike_g 回答日時： 2021/07/04 01:13

》 説明の追加です

で始まる補足説明に期待したけど、添付図と辻褄が合わないので、ガッカリしました。

》 やっぱり説明が下手ですみません
"やっぱり"自覚はされてるようで、なぜかホッとしてはいます。(^_^)
質問が強制的に閉じられるまではまだ日数があるので、もうひと頑張りして完璧な補足説明のために頑張ってください。

No.4 回答者： googoo900 回答日時： 2021/07/03 22:23

>マクロが使えず、関数でできないものかと模索中です。

マクロ以外のやり方で行いたい、と解釈しました。
関数ではないですが自分ならこうするというところで、フィルター機能を使ったやり方を紹介します。

抽出以外のところはよくわかりませんでした。
やりたいことと違っていたらスルーしてください。

■手順
１．シートAやシートBの2行目にフィルタを追加します。2行目を行選択して、メニュー ⇒ データ ⇒ フィルタ　です。
２．▼マークをクリックして、表示したいものにチェックをつけ、非表示にしたいもののチェックをはずす、です。
３．(必要に応じて)範囲コピーして別シートに貼り付けるなどすれば、フィルタリングした範囲のみコピペすることができます。
４．フィルターを解除するときは、メニューのフィルタの右横のクリアをクリックします。

※自分なら複数シートにあるデータをひとつのシートに貼り付けて、もう一列使ってそこにシート名を記載します。そうすればデータ全体から抽出することができますし、特定のシートからの抽出も可能です。（添付図参照）

ちなみに、フィルタで抽出したデータのみの平均や合計を算出する際には、『SUBTOTAL』を使用します。使い方はググればすぐにでてきます。

この回答へのお礼

わかりやすい画像まで、ありがとうございます！！
最初にこの方法を考えたのですが、シートが150以上ありそうなことと、各シート内の数値が、変更になる可能性があるので諦めた次第です。
説明不足でお手数をお掛けしてすみませんでした。

お礼日時：2021/07/04 12:51

No.2 回答者： fujillin 回答日時： 2021/07/03 16:48

こんにちは

ご質問の内容がよくわかりませんけれど、勝手に解釈すると…

・複数のシートをまとめて集計するのではなくって、各列の参照先は一つのシート限定で、シート名は2行目（？）のセルに記入されている。
・シート数が多いので、各列の式を修正するのが面倒
ということと解釈しました。

それぞれ単独の列であれば関数式は作成できるものとして、それを複数シートに展開する方法の例を以下に。
また、各列の式は下方にフィルコピー可能で、参照するシート名のみ異なっているものとします。

◇方法1
各式中のシート参照部分をINDIRECT関数に変更する方法。
例えば、式中に「A!B:C」等の参照があるとして、それぞれの部分を、「INDIRCT(B$2 & "!B:C")」のように置き換えることで、横方向（各列に）フィルコピー可能な式に変更できると思われます。
このように変更した式で、右、下方向にフィルコピーすれば、全体に式を入れられると思われます。
（ただし、全体の数にもよりますが、INDIRECT関数は揮発性関数なので計算負荷がかかりやすいです）

◇方法2
文字列で式を作成してから、関数式に変換する方法。
INDIRECT関数を用いないようにするなら、多少手間はかかりますが…
（例えばB列の）参照式を「=」なしでどこかのセルに記入します。その中のシート名の部分を記号（例えば##とか）に変えておきます。
一時的な作業行に
　="=" & SUBSTITUTE(上記のセル,"##", シート名のあるセル)
とすると、文字列で最終目的の関数式が表示されます。
これを右方向にフィルコピーします。（各列に応じた式が表示されます）
行全体をコピー、「値をペースト」で実際のセルにペーストします。
（この時点でも、式は文字列として認識されています）
ペーストした範囲を選択したまま、「置換」で「=」を「=」に置換。「すべて置換」を行うと、まとめて数式として認識されるようになり、目的の結果が表示されます。
そのまま、行全体を下方にフィルコピーすれば、全体に有効な式を入力できます。

※ 全然お門違いだったら、スルーしてください。

この回答へのお礼

ありがとうございます。
・複数のシートをまとめて集計するのではなくって、各列の参照先は一つのシート限定で、シート名は2行目（？）のセルに記入されている。
・シート数が多いので、各列の式を修正するのが面倒
その通りなのと、
2行目で選択したシートの、Ａ列で選択した品名に対する量に、回数をかけたものをB列に表示させたいのです。

ちなみに、
B8に=INDIRECT(B$2&”!C3”)*B$6
とやれば上手く出たのですが、そのままコピーすると
=INDIRECT(B$2&”!C3”)*B$6
となり、全てがB2セルで指定したシートのC3セルの値掛ける回数(B6)となり、困っていたのと、
全品名は150前後あるものの、まだシートも一から作るところ、かつ、指定した数十の品名のみ集計したい場合もある為、できればA行で指定した値をB列に返すようにしたくて、その単体での式は
＝VLOOKUP(A8,A!B3:C7,2,FALSE)*B6
かなと思ったのですが、
それぞれの式をどう組み合わせて良いのかわからず困っています。

お礼日時：2021/07/03 19:29

No.1 回答者： banzaiA 回答日時： 2021/07/03 14:36

>付写真で伝わると良いのですが、

伝わりません。

この回答へのお礼

コメントありがとうございます。
写真を3枚upしたかったのですが、2枚目以降が電波が悪く？なかなか添付できませんでした。3枚の写真で、なんとな～くわかっていただけるとよいのですが、、

お礼日時：2021/07/03 15:00