- ベストアンサー
Excelで重複する商品IDデータから正しい値を抽出する方法
- Excelで同じ商品IDが複数登録されているデータから、特定の含有物質の含有量を抽出する方法について説明します。
- 商品IDと商品名を含むシート1と、商品IDと含有成分を含むシート2を使用し、COUNTIF関数で重複を回避しつつINDEX＋MATCH関数を利用して正しい値を抽出します。
- VLOOKUP関数や通常のINDEX関数では重複データに対処できないため、この方法を利用すると、必要な含有量の情報を正確に取得できます。
- nadeko666
- お礼率9% (9/97)
- Excel（エクセル）
- 回答数7
- ありがとう数1
- みんなの回答 （7）
- 専門家の回答
質問者が選んだベストアンサー
回答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)))
その他の回答 (6)
- SI299792
- ベストアンサー率47% (780/1630)
そのように入力されているのは解りましたが、結果をどのように表示して欲しいのか解りません。何度質問しても堂々巡りになると思うので、前に上げた様に横並びで結果を表示します。 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を纏めて右へオートフィル。 纏めて下へコピペ。
- SI299792
- ベストアンサー率47% (780/1630)
確認したいのですが。 ①１商品に１列しかないのですが、どのようにすればいいですか。 画像の様に、横に並べればいいですか❓ ②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)）を振ります。
- Chiquilin
- ベストアンサー率30% (94/306)
まず VLOOKUP関数は「検索」の関数なので「検索範囲に一意の値がある」 ことが大前提となります。「IDが重複するデータから」の時点で目的と 手段が合っていません。 「抽出」の関数は FILTER関数だけですが バージョンが古いと使えません。 古いバージョンで数式を使って抽出をしようと思ったら 作業列を用意して 一意の値を作って検索するか 配列数式を使うかしかありません。 > どのような関数を使えばいいでしょうか。 配置や番地やデータ量が不明なので何ともいえませんが バージョンがスピ ルに対応していないなら数式で抽出しようとすること自体お勧めしません。 Excel2013以降ならクエリで抽出したほうがいいと思います。
- SI299792
- ベストアンサー率47% (780/1630)
文章だけで、ワークブックが付いていないので、どのような表か想像できません。 また、商品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欄に物質の含有量を表示させたいので、ピボットテーブルを使わずに関数で抽出する方法は無いか（色々試してみたが見つけられなかった。）と思い。質問させて頂きました。 長文になりましたが、ご教授頂けると幸いです。
- kkkkkm
- ベストアンサー率66% (1734/2604)
> 商品IDが登録されているデータから商品IDが１でかつ、含有物質Aが登録されていたら、その含有量の値を抽出したい場合 この組み合わせが一組しかないのでしたら SUMIFS関数かSUMPRODUCT関数で複数条件指定で抽出できます。
補足
回答頂き、有難うございます。 COUNTIFS関数で複数条件に一致したらカウントし、カウント１だったら"◯"の値を返すことは可能でしたが、複数条件に該当する場合含有量の値を変える方法がわかりませんでした。 SUMIFS関数かSUMPRODUCT関数で複数条件指定で抽出できます。 ＞＞ 具体的な式の例とかわかりますでしょうか。 私も、SUMIFS関数・SUMPRODUCT関数の使い方を一度確認したいと思います。
お礼