【Ｅｘｃｅｌ】レシピデータから、買い物リストを作りたい

Ｓｈｅｅｔ１のレシピデータ、Ｓｈｅｅｔ２の献立をもとに、Ｓｈｅｅｔ３に買い物リストを作りたいのですが、
どうすればいいでしょうか？

重複する材料を足して表示したいのですが…方法がわかりません。
（例えば、もし、1日目がシチューで３日目がカレーだったら、具材はほぼ一緒になると思います。それを足したものを、買い物リストに表示させたいのです。説明が下手ですみません）

以下、シートの情報。
/は、セルの区切りです。


Ｓｈｅｅｔ１『レシピデータ』
管理番号/料理名/材料名-1/一人当たりの量-1/材料名-2/一人当たりの量-2/～（略）～/一人当たりの量25
・
・
・

Ｓｈｅｅｔ２『献立』
『１日目』/管理番号/料理名
『２日目』/管理番号/料理名
『３日目』/管理番号/料理名

Ｓｈｅｅｔ３『買い物リスト』
Ｎ人分
材料名1　個数
材料名2　個数
材料名3　個数
・
・
・

No.1 ベストアンサー 回答者： fujillin 回答日時： 2019/02/13 12:56

こんにちは

＞どうすればいいでしょうか？
自動的に集計できるようにしたいということかと想像しました。
方法はいろいろ考えられると思います。

◇1　マクロによる方法
もしも、質問者様がマクロを利用できるのであれば、なさりたいことをマクロ化して処理するのが簡便可と思います。

◇2　関数による方法
一部の手順を分割する（=作業列を設けるなど）といったことをすれば、関数でも算出が可能と思います。
分割を少なくしようとすればするほど、関数式は複雑になりますので、簡明にしておくためには、適当に分割しておいた方が良いです。

◇3　関数と手作業の複合
全自動化にはなりませんが、わかる範囲を関数で処理して、一部（わからない範囲）だけ手動で処理するといった、上記2の方法との複合型です。


以下に、2の場合の一例について、考え方の概要を示してみます。
※ 管理番号がIDとして利用できるものと仮定します。（=個別の番号が振られている）
1）Sheet2に管理番号を入力すると、該当するレシピ（材料を含む）がその行に表示されるようにしておく。
　（Sheet1を参照するLOOKUP系の関数で簡単に実現できるはずです）
2）Sheet2を参照して、表示されている材料を全てリストアップ（重複を許す）
　 （参照する表が2次元なので、直接重複を省くのは面倒なので、重複を許して（作業列に）1列で出現する材料をリストアップする手順を取るのが簡単だと思います）
　　とはいえ、ここが関数で行うにはここが一番面倒と思います。（後述）
3）2）のリストから重複を省いた材料名をSheet3のA列にリストアップする。
4）3のリストを利用して、Sheet2の表から集計する。
　（対象が2次元の表ではありますが、SUMIF関数を領すれば簡単に集計可能です）


上記で面倒なのは2）、3）だと思います。
3）に関しては、検索すれば、いろいろと方法が見つかると思います。
大抵は、一列を元のデータとして重複を除く方法だと思いますので、2次元の表を1列に変換する、2）についてもう少し触れておきます。

内容がご質問文の程度（日数が3行程度）であれば、複雑なことをせずに、作業列に1列おきに3行ずつ参照する関数式を設定しておけば、（空白も含めて）全部をリストアップできます。
例えば、Sheet2のD1セルから材料名が始まるとするなら、作業列に
　=OFFSET($D$1,MOD((ROW(A1)+n-1),n),INT((ROW(A1)-1)/n)*2)
として、下方にオートフィルすると、ひとまず1列に網羅されます。
（式中、nは対象とする行数。対象の表が3行なら3となる）

あるいは、行数が可変などで空白を詰めて表示するような場合は、対象とするsheet2の範囲の名前を「材料表」とするなら
=IFERROR(INDEX(材料表,INT(SMALL(IF(NOT(ISNUMBER(材料表)+ISBLANK(材料表)),ROW(材料表)*1000+COLUMN(材料表)-3,FALSE),ROW(A1))/1000),MOD(SMALL(IF(NOT(ISNUMBER(材料表)+ISBLANK(材料表)),ROW(材料表)*1000+COLUMN(材料表)-3,FALSE),ROW(A1)),1000)),"")
のような式を入力して（配列数式）、フィルコピーすることで１列に抽出が可能です。
（上式は少々複雑になっていますが、作業列等を用いてさらに手順を分解すれば、簡単にすることが可能です）

一旦、1列にリスト化した材料名（重複を含む）、から重複を省いたリストを3）で作成し、それをインデックスとして材料表の数値を加算すれば良いことになります。
4）の部分を式化してみるなら、A列に個別の材料名があるものとして
　=SUMIF(材料表,D10,OFFSET(材料表,,1))
で材料の合計値（＝一人分の合計値）が求められますので、これに人数分を乗じたものにすれば良いものと思われます。