☰

F&B 業界 - Excel を使用して 1 つのメニューをコンポーネントに変換する方法は?

tag-icon • tag-icon worksheet-function microsoft-excel-2010 pivot-table
F&B 業界 - Excel を使用して 1 つのメニューをコンポーネントに変換する方法は?

私は食品・飲料業界で働いていますが、会社のソフトウェアでは対応できない問題があり、Excel で管理しようとしています。

たくさんのコンポーネントを含むメニューがあります。そこで、メニューをコンポーネントに変換しようとします（添付画像）

ここに画像の説明を入力してください

データがあるのはチキンスープ3個だけです

私のデータベース:

              Column A     | Column B       | Column C
              Chicken Soup | Water          | 100 ml
              Chicken Soup | Chicken Broth  |  10 ml
              Chicken Soup | Chicken Meat   |  50 gr

そして、次のような結果を望んでいます:

              Water         300 ml

              Chicken Broth  30 ml

              Chicken Meat   150 gr

私が試したこと:

置いた私のデータベースすべて1つのシートにSUMIFS * (コンポーネントの数量)を使用します。SUMIFSは列Aに一致するすべてのデータを要約するためだけに使用され、そのコンポーネントと掛け合わせます。

私の仕事の問題点:

  1. レシピが非常に多く（約 14,000 行）、試した数式の計算に時間がかかりすぎます（入力したデータごとに約 10 秒）。

  2. データベースをワークシートとして使用すると、結果がゼロになる行が非常に多くなります (基本的に毎日のデータには平均で 5 ～ 10 項目しかないため)。つまり、実際のデータを取得するには、結果を再度並べ替える必要があります。

ピボット テーブルも試してみましたが、ピボット テーブルにはゼロ金額を含むすべてのデータが表示されます (ただし、必要なのは値のみを持つデータのみです)。

答え1

まず、レシピ リストから材料を取得する列を設定します。これにより、メニュー全体を調べて、そのアイテムに関連付けられているすべての材料が検索されます。

=IFERROR(INDEX($B$1:$B$9,SMALL(IF($A$1:$A$9=F$1,ROW($A$1:$A$9),9E+99),ROW($B1)-ROW($B$1)+1)),"")

ctr + Shift + Enter（配列数式では必ず押してください）

次に、インデックス マッチを使用して数量を検索します。

=INDEX($C$1:$C$6,MATCH(F2,$B$1:$B$6,0))*G$1&INDEX($B$1:$D$6,MATCH(F2,$B$1:$B$6,0),3)

レシピや量を調整すると、リストが自動的に更新されます。

ここに画像の説明を入力してください

関連情報