Excel IDが重複するデータから値を抽出したい

質問

Excelで同じ商品IDが登録されたデータの中から一致する値を返したい。

商品IDと商品名を登録しているシート１と商品IDを入力し商品名をINDEX＋MATCH関数で商品名を抽出し、商品の詳細の情報（ここでは含有物質と含有量）を登録するシート２がそれぞれあります。

シート２には商品IDと含有成分の物質名を登録する為、商品IDが同じなら１から連番を振る(COUNTIF関数)物質登録用のID２があります。

ここで抽出したい値は
シート２を参照範囲として、商品IDを登録しているシート１で
例
IDが１で含有物質Aが登録されていたら、物質Aの含有量の値を抽出したい

しかしINDEX関数、VLOOKUP関数で抽出しようとしても、シート２には同じ商品IDが複数登録されているので、一番最初に合致した値しか返すことができません。
重複した商品IDが登録されているデータから商品IDが１でかつ、含有物質Aが登録されていたら、その含有量の値を抽出したい場合、どのような関数を使えばいいでしょうか。

VLOOKUPは参照範囲の列を挿入してしまうと抽出範囲がずれてしまう為、INDEX＋MATCH関数を使用しております。

すこしややこしいですが、ご教授頂けると助かります。
宜しくお願い致します。

回答No.5

kkkkkm · ベストアンサー

回答No.4の蛇足的追加です。

> COUNTIFS関数で複数条件に一致したらカウントし、カウント１だったら"◯"の値を返すことは可能でしたが

ということですので、両方合わせて

=IF(COUNTIFS(A2:A7,A10,B2:B7,B10)=0,"",SUMIFS(C2:C7,A2:A7,A10,B2:B7,B10))
もしくは
=IF(COUNTIFS(A2:A7,A10,B2:B7,B10)=0,"",SUMPRODUCT((A2:A7=A10)*(B2:B7=B10)*(C2:C7)))

お礼

無事悩んでいた事柄が解決しました。 SUMIIFS関数を教えていただき助かりました。お礼申し上げます。

Powered by GRATICA

回答No.7

SI299792

そのように入力されているのは解りましたが、結果をどのように表示して欲しいのか解りません。何度質問しても堂々巡りになると思うので、前に上げた様に横並びで結果を表示します。

B2: =VLOOKUP(A2,Sheet2!A:C,3,0)
C2: =IFERROR(IF(INDEX(Sheet2!$A:$A,MATCH($A2,Sheet2!$A:$A,0)+COLUMN()/2-1)=VLOOKUP($A2,Sheet2!$A:$A,1,0),INDEX(Sheet2!$D:$D,MATCH($A2,Sheet2!$A:$A,0)+COLUMN()/2-1),""),"")
D2: =IFERROR(IF(INDEX(Sheet2!$A:$A,MATCH($A2,Sheet2!$A:$A,0)+COLUMN()/2-2)=VLOOKUP($A2,Sheet2!$A:$A,1,0),INDEX(Sheet2!$E:$E,MATCH($A2,Sheet2!$A:$A,0)+COLUMN()/2-2),""),"")
C2～D2を纏めて右へオートフィル。
纏めて下へコピペ。

回答No.6

SI299792

確認したいのですが。

①１商品に１列しかないのですが、どのようにすればいいですか。
画像の様に、横に並べればいいですか❓

②Excel のバージョンは❓
（filter関数が使えると楽なのですが）

③Sheet2はID順に並んでいますか❓

補足

回答頂き有難うございます。

①１商品に１列しかないのですが、どのようにすればいいですか。
画像の様に、横に並べればいいですか
＞＞
画像が添付できず申し訳ありません。
シートは以下の内容です。

[Sheet1]　（製品にIDを振るシート）
ID　商品名
１　製品A
２　製品B
３　製品C
[Sheet2]　（ID入力して製品名を抽出し、含有物質と含有量を登録するシート）
ID　ID2　製品名　物質名　含有量(%)
１　１　製品A　　物質A　　5.0
１　２　製品A　　物質B　　0.38
１　３　製品A　　物質C　　20.0
２　１　製品B　　物質A　　8.0
２　２　製品B　　物質D　　20.0
３　１　製品C　　物質B　　0.75

Sheet2のIDは入力したらSheet１から製品名を抽出し、
ID２は一つの製品に複数物質が含まれているため、IDで
重複した場合、連番（=COUNTIF($A$2:A2,A2)）を振ります。

質問内容はSheet１タイトル行の製品名の横のセルに特定の物質名（ex 物質A、物質B、物質D...）
を追加し、Sheet2から重複するIDの中から指定した物質を探し、なかった場合は空欄("")該当した場合は含有量(%)の値を返したいです。

②Excel のバージョンは
（filter関数が使えると楽なのですが）
＞＞
Excelは2016です。
FILTER関数は使用できません。

③Sheet2はID順に並んでいますか
＞＞
はい、基本的にSheet１で登録したIDの番号順に登録をしていきます。
ID２は一つの製品に複数物質が含まれているため、IDで
重複した場合、連番（=COUNTIF($A$2:A2,A2)）を振ります。

回答No.4

kkkkkm

> 具体的な式の例とかわかりますでしょうか。

画像は条件が二つの場合の例です。

A10にIDをB10に物質名を入れたとして
C10に以下のどちらか
=SUMIFS(C2:C7,A2:A7,A10,B2:B7,B10)

=SUMPRODUCT((A2:A7=A10)*(B2:B7=B10)*(C2:C7))

絶対参照とかシートが別とかは実際の環境に合わせてください。
入力時にセル選択したら間違いないと思います。

回答No.3

Chiquilin

まず VLOOKUP関数は「検索」の関数なので「検索範囲に一意の値がある」
ことが大前提となります。「IDが重複するデータから」の時点で目的と
手段が合っていません。

「抽出」の関数は FILTER関数だけですが バージョンが古いと使えません。
古いバージョンで数式を使って抽出をしようと思ったら 作業列を用意して
一意の値を作って検索するか 配列数式を使うかしかありません。

> どのような関数を使えばいいでしょうか。
配置や番地やデータ量が不明なので何ともいえませんが バージョンがスピ
ルに対応していないなら数式で抽出しようとすること自体お勧めしません。

Excel2013以降ならクエリで抽出したほうがいいと思います。

回答No.2

SI299792

文章だけで、ワークブックが付いていないので、どのような表か想像できません。
また、商品ID:1、物質A が１つなのか、複数あるのか、複数ある場合どうするのかもわかりません。

勝手に画像の様な表で、□部分を入力とします。
B2: =VLOOKUP(A2,Sheet2!A:B,2,0)
D2: =SUMIFS(Sheet2!D:D,Sheet2!A:A,A2,Sheet2!C:C,C2)

ここは質問者が画像の追加をできません。ワークブックがどのようになっているかを付けて再質問することをお勧めします。

補足

回答頂きありがとうございます。
画像を添付できず失礼いたしました。

[Sheet1]　（製品にIDを振るシート）
ID　商品名
１　製品A
２　製品B
３　製品C
[Sheet2]　（ID入力して製品名を抽出し、含有物質と含有量を登録するシート）
ID　ID2　製品名　物質名　含有量(%)
１　１　製品A　　物質A　　5.0
１　２　製品A　　物質B　　0.38
１　３　製品A　　物質C　　20.0
２　１　製品B　　物質A　　8.0
２　２　製品B　　物質D　　20.0
３　１　製品C　　物質B　　0.75

Sheet2のIDは入力したらSheet１から製品名を抽出し、
ID２は一つの製品に複数物質が含まれているため、IDで
重複した場合、連番（=COUNTIF($A$2:A2,A2)）を振ります。

質問内容はSheet１タイトル行の製品名の横のセルに特定の物質名（ex 物質A、物質B、物質D...）
を追加し、Sheet2から重複するIDの中から指定した物質を探し、なかった場合は空欄("")該当した場合は含有量(%)の値を返したいです。

イメージするとピボットテーブルで集計したときに、行ラベルに製品名、列ラベルに物質名、値を含有量で集計する感じです。
Sheet１の製品のID欄に物質の含有量を表示させたいので、ピボットテーブルを使わずに関数で抽出する方法は無いか（色々試してみたが見つけられなかった。）と思い。質問させて頂きました。

長文になりましたが、ご教授頂けると幸いです。

回答No.1

kkkkkm

> 商品IDが登録されているデータから商品IDが１でかつ、含有物質Aが登録されていたら、その含有量の値を抽出したい場合

この組み合わせが一組しかないのでしたら

SUMIFS関数かSUMPRODUCT関数で複数条件指定で抽出できます。

補足

回答頂き、有難うございます。

COUNTIFS関数で複数条件に一致したらカウントし、カウント１だったら"◯"の値を返すことは可能でしたが、複数条件に該当する場合含有量の値を変える方法がわかりませんでした。

SUMIFS関数かSUMPRODUCT関数で複数条件指定で抽出できます。
＞＞
具体的な式の例とかわかりますでしょうか。
私も、SUMIFS関数・SUMPRODUCT関数の使い方を一度確認したいと思います。