複数のエクセルのファイルにおいて、共通する項目のみを抽出する方法を教えてください。
エクセル初心者です。
たとえば下記のようなデータ1とデータ2のファイルがあった場合、
データ1と共通する項目のみを、データ2から抽出するにはどうすればよいのでしょうか。
●データ1(今月の購入分)
品名コード 品名 価格 購入店名
1 りんご 100円 C店
5 みかん 300円 B店
1 りんご 120円 D店
4 すいか 900円 E店
●データ2(今年の購入分)
品名コード 品名 購入店名
1 りんご C店
3 かぼちゃ B店
5 みかん B店
6 にんじん A店
1 りんご D店
4 すいか E店
上記の表のように、すべての項目がデータ1と2で共通しているわけではありません。
それぞれ別の人が入力したデータのため、入力形式や順序などが揃っていないのです。
確実に双方に入力されているデータは品名と購入店名のみなので、
その二つの情報を元に、共通する項目を抜き出すしか方法がなさそうなのです。
このデータ1とデータ2の表を利用して、
データ2(今年度の購入分)からデータ1(今月の購入分)のみを抽出することは可能でしょうか?
最終的にはデータ2を下記のような形にしたいのです。
品名コード 品名 購入店名
1 りんご C店
5 みかん B店
1 りんご D店
4 すいか E店
実際はひとつひとつ抽出するには途方もない量のデータなので、
知恵を貸していただけるととても助かります。
No.4ベストアンサー
- 回答日時:
NO.1です!
補足を読ませてもらいました。
hakkairoさんが仰っている、三つの問題点の最初の二つについてはまったく問題ないデータ量だと思います。
行数が万単位でもちゃんと表示されるはずです。
一つ気になるのが三点目です。
「品名コードと店名がまったく同じデータが複数存在する」ということなので前回のSheet2の作業列D2セルの式では
最初のデータのみしか抽出しませんので、結果に同じものが複数表示されても良いのであれば
D2セルの数式を
=IF(COUNTBLANK(A2:C2),"",IF(COUNTIF(Sheet1!E:E,A2&B2&C2),ROW(),""))
に訂正してみてください。
尚、最後にある
>ちなみに、作業列2には数字が「0~4」までしか表示されていませんでした。
に関しては「0」という数値が出ること自体がおかしいですねぇ~!
数式の ROW() となっていますのでその行番号が表示されるはずです。
この件に関してはちょっと判りかねます。
この程度で解決できないかも知れませんが・・・
ごめんなさいね。m(__)m
遅くなりましたがありがとうございます!
作業列にはやはり0~4までの数字しか表示されませんでしたが、
抽出するものには数字が入るという判断材料とすることができたので、
フィルタをかけて数字が入っている項目のみを抽出しました。
本当に助かりました!
No.3
- 回答日時:
分かりやすいようにデータ1をシート1に、データ2をシート2に貼り付けることにします。
その上でお求めの表をシート3に作るとしてシート3のA1セルには次の式を入力してC1セルまでオートフィルドラッグします。=IF(COLUMN(A1)=1,Sheet1!$A1,IF(COLUMN(A1)=2,Sheet1!$B1,IF(COLUMN(A1)=3,Sheet1!$D1,"")))
A2セルには次の式をC2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(Sheet1!$B1="","",IF(COLUMN(A2)=1,INDEX(Sheet2!$A:$A,SUMPRODUCT((Sheet2!$B$1:$B$5000=$B2)*(Sheet2!$C$1:$C$5000=$C2)*ROW($A$1:$A$5000))),IF(COLUMN(A2)=2,Sheet1!$B2,IF(COLUMN(A2)=3,Sheet1!$D2,""))))
遅くなりましたが回答ありがとうございます。
質問文に書いた表は仮のもので、実際の表とはだいぶ異なる部分があり、
回答者様に書いていただいた式の内容を完全に理解する能力が私になかったため、
式をそのまま入力するという形では対応することができませんでした。
今後、勉強して知識がつきましたら参考にさせていただきます。
No.1
- 回答日時:
こんばんは!
一例です。
質問は別ファイル(別ブック)になっていますが、↓の画像は別Sheetでやっています。
別ファイルでも方法はまったく同様です。
質問を読ませてもらうと商品はコードによって決まっているみたいなので、コードと購入店だけで判断できると思います。
両方のSheetに作業用の列を設けています。
Sheet1(データ1)のE2セルを
=A2&D2
として、フィルハンドルの(+)マークでダブルクリック、またはオートフィルで下へずぃ~~~!っとコピーします。
Sheet2(データ2)のD2セルに
=IF(COUNTIF(Sheet1!E:E,A2&C2)=1,ROW(),"")
という数式をいれ、同様にオートフィルで下へコピー!
最後にSheet2のF2セルに
=IF(COUNT($D:$D)<ROW(A1),"",INDEX(A:A,SMALL($D:$D,ROW(A1))))
という数式をいれ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。
こんなんで良かったのですかね?m(__)m
この回答への補足
詳しい説明ありがとうございます。
教えていただいた通りにやってみたところ、こちらの表では確かにできました。
が、実際のデータでは上手くできませんでした…何故でしょうか。
抽出できないデータがどうしても残ってしまうのです。
原因として考えられるのは下記の三点かと思うのですが…
・データ1のデータが多すぎる(100行以上)
・データ2のデータが多すぎる(1000行以上)
・品名コードと店名が全く同じデータが複数存在する
ちなみに、作業列2には数字が「0~4」までしか表示されていませんでした。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- Excel(エクセル) マクロか関数で処理したいのですが、教えて頂けませんか。 8 2022/10/31 15:18
- Visual Basic(VBA) VBAで重複データを確認したい 5 2022/10/07 16:24
- ドライブ・ストレージ 古い外付けHDDから新品外付けHDDへのデータ移行方法 (Mac) 2 2022/12/11 02:01
- Visual Basic(VBA) 指定月分の顧客データファイルを統合して並べ替え、所定の場所に貼り付ける 3 2022/09/10 07:55
- Excel(エクセル) エクセルで沢山のレコードの最後に追記するには? 7 2023/04/10 13:27
- 会計ソフト・業務用ソフト Excelで売上げデータの中の任意の商品の合計を出したい 3 2023/01/18 18:19
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 3 2022/06/12 11:17
- その他(Microsoft Office) Excelで総数量を変動させたい 2 2022/11/04 23:49
- Excel(エクセル) VBAで、シート間の転記するコードを教えてください。 4 2023/03/26 10:43
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
ウィンドウィズ メモ帳で日付だ...
-
Excelに貼ったリンクについて E...
-
Outlook で宛先が複数の場合の人数
-
【スプレッドシート】指定の日...
-
Office2021を別のPCにインスト...
-
英数字のみ全角から半角に変換
-
【Excel VBA】PDFを作成して,...
-
【Excel】セル内の文字が正しい...
-
エクセルのシフト表を簡単にGoo...
-
Microsoft365で写真をアルバム...
-
パソコン買い換え Office移行 ...
-
エクセルで例えば、A1に㈱ベ...
-
Googleのスプレッドシートでシ...
-
エクセル関数について
-
Excel関数について質問ですm(__)m
-
会社のTeamsのことで相談です。...
-
自分の専門分野の仕事。初見で...
-
エクセルにリンクされるのをし...
-
2つのシートの一致する行のセ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報