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

質問

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

回答No.4

noname#70958 · ベストアンサー

●Ａ案　数式一発で。
　同一列内に重複がない、という前提です。
　１行目は見出し行として、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())を使った方が処理速度の点で優位です。

以上ご参考まで。長乱文陳謝。

お礼

詳しい説明をありがとうございます．早速やってみます！

回答No.3

mitarashi

ご質問を種に、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が生成するものを切り貼りできます。
ご参考にはならないかもしれませんが．．．

お礼

もっと色々と勉強せねばならないと反省する次第です．助言ありがとうございます．

回答No.2

imogasi

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

補足

質問内容が分かりにくくてすみません。
列で出てくるものは文字や数値が混じったものとなります。
例）A115, FLAG3, 112Jなど。
共通する文字列は、文字列そのものです。
例）A115とA114は区別する。
重複出現はありません（元データには重複する列データもあるのですが、どうしても抽出式が思いつかなかったので、まずは各々の列中でフィルタをかけて、重複がないように処理をしてから比較しようと考えております）。
列ごとのデータ数はバラバラです。
列A 　　列B 　　 出力結果
A115 　　2G 　　 A115
A114 　　 3P 　　 3E
FLAG3 　A115
3E 　　　542T
567Q 　　3E

このように出力されるようなものを考えておりますが、なかなかうまくゆきません。

回答No.1

hallo-2007

>列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,"") 下フィルします
ではいかがでしょうか。

お礼

やってみました．なんとか出来そうです．
データが多いと若干重くなりそうですが，見通しがあかるくなってきました．ありがとうございました．