エクセル表で重複するデータ（文字列）の抽出について

質問

このサイトで検索してもわからなかったので教えてください。

料理とその料理に使用する材料がわかる表を作成しました。

各々の料理に入っている材料は別シートにデータをいれてあり、セルから料理を選べば材料が一覧で出てくるようになっています。

この場合、料理を２種選択した時に重複する材料を別のセルに抽出したいのです。
そしてその材料の金額をまた別のシートのデータから持ってきたいと思っています。

　　重複する材料　　　その材料の金額
１ ジャガイモ　　　　　　　　２００　　　　　　　　　　　←この重複する材料の真下のセル（今はジャガイモ
２　小麦粉　　　　　　　　　　１５０　　　　　　　　　　　　というデータが入っているセル）にいれる関数
３　エビ　　　　　　　　　　　　３００　　　　　　　　　　　　がわかりません。
４
５
６
７
８
９
ここからまた別の表で

１０　料理名Ａ　　　　料理名Ｂ　　　　料理名Ｃ　　　　　　料理名Ｄ
１１　カレー　　　　　オムライス　　　　ピザ　　　　　　　　お好み焼き　
１２　ジャガイモ　　　卵　　　　　　　　　小麦粉　　　　　　小麦粉　
１３　人参　　　　　　　ひき肉　　　　　　チーズ　　　　　　豚肉
１４　　　　　　　　　　　白米　　　　　　　　ジャガイモ　　　エビ

料理名の真下のセルからプルダウンで料理名を選べば１１行目～に材料が出るようにはしてあります。（そこまでの関数はわかりました。）

上記の例の場合、４種の料理で重複する材料は

ジャガイモ
小麦粉
エビ

なのですが、

それを2行目～に表示できるようにしたいのです。

その表示が出来れば、別のシートに作成した、材料⇔金額一覧からその重複材料の金額を表示できるようにしたいです。（ＶＬＯＯＫＵＰという関数で出来ますよね？）

材料は１料理当たり最大１８種類です。
料理の種類は１３０あります。

わかりにくい質問になってしまい申し訳ありません。

どなたかご教授宜しくお願いいたします。

１５　　　　　　　　　　　　　　　　　　　　　　エビ

回答No.5

tom04 · ベストアンサー

No.2です。
補足に
＞私のシート２は例えば
Ａ１ カレー ジャガイモ ニンジン エビ
A2 お好み焼き ブタニク エビ 小麦 粉
A3 オムライス タマゴ ハクマイ タマ ネギ
・
シート３は
ジャガイモ １００
エビ ３００
コムギコ ２００

となっているというコトは↓の画像のようになっていると解釈です。
（万一Sheet2のA列の1セルだけに複数の食材が入力されている場合はかなり厄介になります）
ただ、これだと最初の質問にアップされている画像の配置とは違ってしまいますね！
まぁ～！それはさておいて・・・

Sheet3のC1セルに
=IF(COUNTIF(Sheet2!B:Z,A1)>1,ROW(),"")
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そしてSheet1のA2セルに
=IF(COUNT(Sheet3!C:C)<ROW(A1),"",INDEX(Sheet3!A:A,SMALL(Sheet3!C:C,ROW(A1))))
B2セルに
=IF(A2="","",VLOOKUP(A2,Sheet3!A:B,2,0))
という数式を入れそれぞれオートフィルで下へコピーすると
画像のような感じになります。

※　細かい配置は実状に合わせてみてください。
尚、一つ気になったのですがこの場合使用食材が複数ある場合だけの金額になりますが、
単品の場合の金額は不要なのでしょうかね？m(_ _)m

お礼

ありがとうございます☆

図付きで初心者の私でもとてもわかりやすかったのでベストアンサーに選ばせていただきました。

教えていただいた通りにすると出来ました！
今回のことでエクセルの面白さがわかった気がします。

本当にありがとうございました☆

ちなみに単品の場合の金額は不要なんです（笑）

回答No.4

KURUMITO

回答3の場合で重複する材料がA1セルから下方に表示されますのでB列に金額を表示させるのでしたら、シート3のA列に材料名、B列に金額があるとしたらシート1のB1セルには次の式を入力して下方にドラッグコピーします。

=IF(A1="","",VLOOKUP(A1,Sheet3!A:B,2,FALSE))

回答No.3

KURUMITO

回答No2さんの方法は優れた方法でしょう。しかし価格表にデータが無い時点では重複する材料に表示されませんね。
操作は多少複雑になりますが次のようにする方法もあります。
A10セルからD10セルまでに料理Aから料理Dまでの記載が有るとしてそれらの材料についてはお示しの表では11行目から下方になっていますが2行間に挿入をして13行目から下方に入力されているとします。
11行目と12行目は作業列とします。
A11セルには次の式を入力して例えばF11セルまで横にドラッグコピーします。

=IF(A$10="","",COUNTIF(A$13:A$50,"*?"))

A12セルには次の式を入力してF12セルまで横にドラッグコピーします。

=IF(COLUMN(A1)=1,A11,IF(A11="","",OFFSET(A12,,-1)+A11))

これらのデータをもとにG13セルから下方に材料の一覧を表示させることにします。
G13セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(A$12="",ROW(A1)>MAX(A$12:F$12)),"",IF(ROW(A1)<=A$12,INDEX(A$13:A$50,ROW(A1)),INDEX(A$13:F$50,ROW(A1)-INDEX(A$12:F$12,MATCH(ROW(A1)-0.5,A$12:F$12,1)),MATCH(ROW(A1)-0.5,A$12:F$12,1)+1)))

H13セルには次の式を入力して下方にドラッグコピーします。

=IF(G13="","",IF(AND(COUNTIF(G$13:G$200,G13)>1,COUNTIF(G$13:G13,G13)=1),MAX(H$12:H12)+1,""))

その後に例えばA1セルに次の式を入力して下方にドラッグコピーすれば重複材料のみがリストアップされます。

=IF(ROW(A1)>MAX(H$13:H$200),"",INDEX(G$13:G$200,MATCH(ROW(A1),H$13:H$200,0)))

お礼

回答ありがとうございます。

回答いただいた通りに入力したらできました！！！
私には式が長くて意味が理解できませんでしたが（笑）

すごいです☆

本当にありがとうございました。

回答No.2

tom04

こんにちは！
材料の使用量に関しては無視してよい訳ですよね？

＞セルから料理を選べば材料が一覧で出てくるようになっています。
とありますので、その表に↓の画像（右側がSheet2）のように価格も一緒の表にします。

＞料理名の真下のセルからプルダウンで料理名を選べば１１行目～に材料が出るようにはしてあります。（そこまでの関数はわかりました。）

というコトで左側のSheetの10行目以降は表示できているとします。

やり方だけ！

Sheet2に作業用の列を設けます。
作業列C2セルに
=IF(COUNTIF(Sheet1!$A$12:$D$100,A2)>1,ROW(),"")
という数式を入れオートフィルで下へコピー！

Sheet1のA2セルに
=IF(COUNT(Sheet2!C:C)<ROW(A1),"",INDEX(Sheet2!A:A,SMALL(Sheet2!C:C,ROW(A1))))

B2セルに
=IF(A2="","",VLOOKUP(A2,Sheet2!A:B,2,0))

という数式を入れそれぞれをオートフィルで下へコピーすると
画像のような感じになります。m(_ _)m

補足

2013/4/6(土) 17:23

回答ありがとうございます。

少しわからなかったので教えていただきたいの ですが

＞セルから料理を選べば材料が一覧で出てくる ようになっています。 とありますので、その表に↓の画像（右側が Sheet2）のように価格も一緒の表にします。

の部分ですが、

私のシート２は例えば

Ａ１ カレー ジャガイモ ニンジン エビ
A2 お好み焼き ブタニク エビ 小麦 粉
A3 オムライス タマゴ ハクマイ タマ ネギ
・
・
・
・

シート３は

ジャガイモ １００
エビ ３００
コムギコ ２００
・
・
・

という風になっています

このばあい、シート３の金額の右側に教えてい ただいた作業列を作成すればいいのでしょう か？

お忙しい中何度も申し訳ありませんが、よろし くお願いいたします

回答No.1

aokii

材料⇔金額一覧では、その材料の量によって、その材料の金額が変わってくるように思うのですが、エクセルの挿入から、ピボットテーブルをクリックして、ピボットテーブルを作ってみてください。

お礼

とても早い回答ありがとうございました。

ピボットテーブルとうもの初めて知りました。
今後使用してみようと思います。

本当にありがとうございました。

補足

早速のお返事ありがとうございます。

私の説明不足で申し訳ございません。
材料は単価がわかればいいのです。

引き続きよろしくお願いいたします。