質問: 複数シートのデータを抽出するには? excel表にある複数シートの、複数行のデータを抽出したいのですが、マクロが使えず、関数でできないものかと模索中です。 具体的には、参照したいシート毎の、品名に応じた量を出したいのですが、 検索したいシートも、品名も、150前後あります。 シートが一つまたは品名(検索したい列か行)が1つから、vlookupやindirect で抽出できそうなのですが、両方のボリュームがあるので、上手くいかず。 (説明が下手ですみません。添付写真で伝わると良いのですが、、) 関数などを利用して、あまり知識のないものでも出来そうな方法はありますか?
回答: こんにちは ご質問の内容がよくわかりませんけれど、勝手に解釈すると... ・複数のシートをまとめて集計するのではなくって、各列の参照先は一つのシート限定で、シート名は2行目(?)のセルに記入されている。 ・シート数が多いので、各列の式を修正するのが面倒 ということと解釈しました。 それぞれ単独の列であれば関数式は作成できるものとして、それを複数シートに展開する方法の例を以下に。 また、各列の式は下方にフィルコピー可能で、参照するシート名のみ異なっているものとします。 ◇方法1 各式中のシート参照部分をindirect関数に変更する方法。 例えば、式中に「a!b:c」等の参照があるとして、それぞれの部分を、「indirct(b$2 & "!b:c")」のように置き換えることで、横方向(各列に)フィルコピー可能な式に変更できると思われます。 このように変更した式で、右、下方向にフィルコピーすれば、全体に式を入れられると思われます。 (ただし、全体の数にもよりますが、indirect関数は揮発性関数なので計算負荷がかかりやすいです) ◇方法2 文字列で式を作成してから、関数式に変換する方法。 indirect関数を用いないようにするなら、多少手間はかかりますが... (例えばb列の)参照式を「=」なしでどこかのセルに記入します。その中のシート名の部分を記号(例えば##とか)に変えておきます。 一時的な作業行に ="=" & substitute(上記のセル,"##", シート名のあるセル) とすると、文字列で最終目的の関数式が表示されます。 これを右方向にフィルコピーします。(各列に応じた式が表示されます) 行全体をコピー、「値をペースト」で実際のセルにペーストします。 (この時点でも、式は文字列として認識されています) ペーストした範囲を選択したまま、「置換」で「=」を「=」に置換。「すべて置換」を行うと、まとめて数式として認識されるようになり、目的の結果が表示されます。 そのまま、行全体を下方にフィルコピーすれば、全体に有効な式を入力できます。 ※ 全然お門違いだったら、スルーしてください。