列中に共通にあるデータを抽出したい

Question

エクセルでA列、B列、C列と別々の文字情報が入っているデータがあります（製品のコードNo）,列は条件で分類しています（例，列A: 水をかけて変質したもの、列B: 燃えたもの, など．数値の数は列ごとに異なります）. 列Aと列Bの両方で出てきたもの列Aと列Cで出てきたけれど列Bにはないもの、というような抽出を行なうにはどうしたらよいのでしょうか．うまく検索式を組む事が出来ません．よろしくお願い致します．

noname#70958 · Accepted Answer

●Ａ案　数式一発で。
　同一列内に重複がない、という前提です。
　１行目は見出し行として、A2:C999にデータが入っているとすれば、

　■A,B列にあり、C列にないもの
　　D2セルを
　　　=OFFSET($A$1,1/LARGE(INDEX((((ISNA(MATCH($A$2:$A$999,$B$2:$B$999,0))=FALSE)+(ISNA(MATCH($A$2:$A$999,$C$2:$C$999,0))=TRUE))=2)/ROW($A$2:$A$999),),ROW()-1)-1,)
　　として、#DIV/0が返るまで下方にフィル

　以下同様に、

　■A,B,C列にあるもの
　　　=OFFSET($A$1,1/LARGE(INDEX((((ISNA(MATCH($A$2:$A$999,$B$2:$B$999,0))=FALSE)+(ISNA(MATCH($A$2:$A$999,$C$2:$C$999,0))=FALSE))=2)/ROW($A$2:$A$999),),ROW()-1)-1,)
　■A,C列にあり、B列にないもの
　　　=OFFSET($A$1,1/LARGE(INDEX((((ISNA(MATCH($A$2:$A$999,$B$2:$B$999,0))=TRUE)+(ISNA(MATCH($A$2:$A$999,$C$2:$C$999,0))=FALSE))=2)/ROW($A$2:$A$999),),ROW()-1)-1,)
　■B,C列にあり、A列にないもの
　　　=OFFSET($B$1,1/LARGE(INDEX((((ISNA(MATCH($B$2:$B$999,$A$2:$A$999,0))=TRUE)+(ISNA(MATCH($B$2:$B$999,$C$2:$C$999,0))=FALSE))=2)/ROW($B$2:$B$999),),ROW()-1)-1,)
　■A列にあり、B,C列にないもの
　　　=OFFSET($A$1,1/LARGE(INDEX((((ISNA(MATCH($A$2:$A$999,$B$2:$B$999,0))=TRUE)+(ISNA(MATCH($A$2:$A$999,$C$2:$C$999,0))=TRUE))=2)/ROW($A$2:$A$999),),ROW()-1)-1,)
　■B列にあり、A,C列にないもの
　　　=OFFSET($B$1,1/LARGE(INDEX((((ISNA(MATCH($B$2:$B$999,$A$2:$A$999,0))=TRUE)+(ISNA(MATCH($B$2:$B$999,$C$2:$C$999,0))=TRUE))=2)/ROW($B$2:$B$999),),ROW()-1)-1,)
　■C列にあり、A,B列にないもの
　　　=OFFSET($C$1,1/LARGE(INDEX((((ISNA(MATCH($C$2:$C$999,$A$2:$A$999,0))=TRUE)+(ISNA(MATCH($C$2:$C$999,$B$2:$B$999,0))=TRUE))=2)/ROW($C$2:$C$999),),ROW()-1)-1,)

ちなみに、
　■A,B列にあれば、C列での有無は問わない場合
　　　=OFFSET($A$1,1/LARGE(INDEX((ISNA(MATCH($A$2:$A$999,$B$2:$B$999,0))=FALSE)/ROW($A$2:$A$999),),ROW()-1)-1,)

Excel2003で動作確認済。
ただし、データ数が多いと相当に重たい処理になりますから…
------------------------------------------------------
●Ｂ案　データ数が多い場合

１．A,B,C列のデータをD列に縦に並べる。

２．D列のデータを、フィルタオプションで重複を除いてE列2行目以下に書き出す

３．F2セルを
　　=IF(ISNA(MATCH(E2,$A$2:$A$9999,0)),"","A")&IF(ISNA(MATCH(E2,$B$2:$B$9999,0)),"","B")&IF(ISNA(MATCH(E2,$C$2:$C$9999,0)),"","C")
　として下方にフィル

４．F列についてオートフィルタをかけ、目的のデータを抽出
------------------------------------------------------
なお、大量のデータについて「有無」を調べるだけの場合には、
COUNTIF()よりもISNA(MATCH())を使った方が処理速度の点で優位です。

以上ご参考まで。長乱文陳謝。

hallo-2007 · Answer

>列Aと列Bの両方で出てきたもの
B1セルの文字がA列にある場合は　B1　なければ空白
=IF(COUNTIF(A:A,B1),B1,"")  下フィルします
>列Aと列Cで出てきたけれど
同じですね（仮にE列を作業列として）
=IF(COUNTIF(A:A,C1),C1,"")  下フィルします
>列Bにはないもの
=IF(COUNTIF(B:B,E1),E1,"")  下フィルします
ではいかがでしょうか。

imogasi · Answer

もう少しＡ，Ｂ、Ｃ列のデータをモデル化して、順を追って、質問文に書かないと、回答者読者にはわかり難い。
＞列Aと列Bの両方で出てきたもの
共通する文字列は、文字列の一部か、文字列そのもの（全文字）か、も良くわからない。
Ａ列には（製品のコードNo？について）重複出現があるのかも質問に書いてない。
抽出は
フィルタ
フィルタオプション
ＶＢＡなど
非関数系利用が望ましい。
ーーー
＞列Aと列Bの両方で出てきたもの
作業列を使うなら、Ａ１がＢ１：Ｂ１００（Ｂ列データ行を１００と仮定）でカウント（ＣＯＵＮＴＩＦ）して
０ーーーＢ列に無い
１以上ーーＡ列とＢ列に存在する
と判定できるから、１以上の行に上から連番を振って、他シートなどに抜き出しはできる。（ＷＥＢ・Ｇｏｏｇｌｅでimogasi方式で照会すれば沢山例が出る）
＞Aと列Cで出てきたけれど列Bにはないもの
「Aと列Cで出てきた」は上記で判定し、＞列Bにはないものも上記で判定できるので、２つをＡＮＤ条件で判定すればよい。

mitarashi · Answer

ご質問を種に、MS Queryで遊んでおりました。
下記の様なデータブックを用います。
各列に名前、tableA,tableB,tableCをそれぞれつける
................A...............B...............C
..1.....code_a.....code_b.....code_c
..2..........0001..........0018..........0010
..3.........0002..........0019...........0011
..4.........0003.........0020..........0012
データブックとは別のブックに抽出します。（同じブック内でも可能ですが、ディスク上のファイルから抽出する様な気がするので、その場合は未保存データに要注意）
XL2000の例では、
データ／外部データの取り込み／新しいデータベースクエリ
Excel Files* を選択
カレントフォルダ内のエクセルファイルが表示されるので、
目的のデータファイルを選択
適当なテーブルと列を選び（結局SQLの実行環境としてしか使用しないので）
次へを何度か押して、最後の画面で
Microsoft Queryでデータの表示またはクエリの編集を行う　を選択して、完了
Microsoft Queryの画面が表示されるので、SQLのアイコンをクリックして、
表示されるテキストボックスに、SQL文を入力してOKをクリック
クエリを再表示できません、継続しますかと聞いてくるので、気にせずOKをクリック
これで目的のデータが抽出される
ファイル／Microsoft Excelにデータを返す　で、出力先セルを指定して、
抽出されたデータを貼り付ける
という手順で抽出出来ました。
>列Aと列Cで出てきたけれど列Bにはないもの
は、次のSQLで良いと思いますが、今晩初めてサブクエリの使い方が分かった様な気がする自分ですので、確信は持てません。
SELECT tableA.code_a
FROM tableA INNER JOIN tableC ON tableA.code_a = tableC.code_c
WHERE (((tableA.code_a) Not In (SELECT tableA.code_a
FROM tableA INNER JOIN tableB ON tableA.code_a = tableB.code_b;)));
サブクエリに用いるSQLは、MS Queryが生成するものを切り貼りできます。
ご参考にはならないかもしれませんが．．．

列中に共通にあるデータを抽出したい

>列Aと列Bの両方で出てきたもの

もう少しＡ，Ｂ、Ｃ列のデータをモデル化して、順を追って、質問文に書かないと、回答者読者にはわかり難い。

この回答への補足

ご質問を種に、MS Queryで遊んでおりました。

●Ａ案 数式一発で。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

●Ａ案　数式一発で。