ChiquilinSite
いまやこのページを見る人もほぼいないと思います。現在は下記で紹介している「比較して抽出」なら PowerQueryでクエリのマージを実行する方が簡単だからです。一応残していますが過去の遺物であることをご承知おきください。
★その１：AにあってBにないデータの抽出
作業列を使っても重くなりやすい要望ですが うまくやれば負担を少なく処理することができます。まずは
COUNTIF関数を使う方法です。処理は重めですが 理解はし易いと思います。
■G1セル：データ1の個数：=COUNTA(A:A)
■G2セル：データ2の個数：=COUNTA(B:B)
■C2セル：「１○２×」データ1にあってデータ2にないデータ
=IF(A2="","",IF(COUNTIF(B$2:INDEX(B:B,G$1),A2),"",ROW()))
■D2セル：「１×２○」データ2にあってデータ1にないデータ
=IF(B2="","",IF(COUNTIF(A$2:INDEX(A:A,G$2),B2),"",ROW()))
C2:F2セルを 9行目までオートフィルコピー
■G3セル：「１○２×」の個数：=COUNT(C:C)
■G4セル：「１×２○」の個数：=COUNT(D:D)
最終行を取得しておくことで無駄な範囲の計算を省くことができます。また IF関数を2個挟んでいますがこれも処理の負担を可能な限り減らすためです。もしデータ1とデータ2にデータの重複がある場合は
データを1・2でそれぞれ昇順に並べ換えした上で C2・D2の式を下記のように変更します。
Ａ・Ｂ列を昇順にソートしていることが前提で
■C2セル：「１○２×」データ1にあってデータ2にないデータ
=IF(A2="","",IF(A1=A2,"",
IF(COUNTIF(B$2:INDEX(B:B,G$1),A2),"",ROW())))
■D2セル：「１×２○」データ2にあってデータ1にないデータ
=IF(B2="","",IF(B1=B2,"",
IF(COUNTIF(A$2:INDEX(A:A,G$2),B2),"",ROW())))
C2:F2セルを 9行目までオートフィルコピー
これによって無駄な計算を省ける他 抽出する時にデータが重複しないようになります。抽出の数式は下記の通り
A・B列を昇順にソートしていることが前提で
■A2セル：「１○２×」
=IF(data!G$3<ROW(A1),"",INDEX(data!A:A,SMALL(
data!C$2:INDEX(data!C:C,data!G$1),ROW(A1))))
■B2セル：「１×２○」
=IF(data!G$4<ROW(A1),"",INDEX(data!B:B,SMALL(
data!D$2:INDEX(data!D:D,data!G$2),ROW(A1))))
以上です。
→参考ファイル(029.xlsx)
続いて
LOOKUP関数を使う方法です。こちらは少々面倒なのが難点ですが 処理は速いです。こちらについては重複の有無に関わらずデータ1・2を
必ず昇順にソートしておいて下さい。
■G１セル：データ1の個数：=COUNTA(A:A)
■G2セル：データ2の個数：=COUNTA(B:B)
C1・D2セルに「0」と入力
■C2セル：「１○２×」データ1にあってデータ2にないデータ
=IF(A2="","",IF(A1=A2,C1,
IF(AND(A2>=B$2,LOOKUP(A2,B$2:INDEX(B:B,G$2))<>A2),C1+1,C1)))
■D2セル：「１×２○」データ2にあってデータ1にないデータ
=IF(B2="","",IF(B1=B2,C1,
IF(AND(B2>=A$2,LOOKUP(B2,A$2:INDEX(A:A,G$2))<>B2),D1+1,D1)))
■G3セル：「１○２×」の個数：=MAX(C:C)
■G4セル：「１×２○」の個数：=MAX(D:D)
これで準備終了。あとは抽出です。
■A2セル：「１○２×」
=IF(data!G$3<ROW(A1),"",INDEX(data!A:A,MATCH(ROW(A1)-1,
data!C$1:INDEX(data!C:C,data!G$1))+1))
■B2セル：「１×２○」
=IF(data!G$4<ROW(A1),"",INDEX(data!B:B,MATCH(ROW(B1)-1,
data!D$1:INDEX(data!D:D,data!G$2))+1))
こっちは TRUE検索だけをつかっているので もっとも処理が速いです。数式で処理する場合では一番お勧めの方法です。
→参考ファイル(030.xlsx)
★その２：AにもBにもあるデータの抽出
さて上の表を使うと「AにもBにもあるデータ」も抽出できます。こちらも２例紹介します。
まず COUNTIF関数を使った一つ目の表を使う場合の方法です。
■dataシートの D2セル：「１○２○」データ1にもデータ2にもあるデータ
=IF(B2="","",IF(AND(D2="",B1<>B2),ROW(),""))
9行目までオートフィルコピー
■dataシートの G5セル：「１○２○」の個数：=COUNT(E:E)
これで下準備終了。あとは抽出です。
■listシートのＣ１セル：「１○２○」
=IF(data!G$5<ROW(A1),"",INDEX(data!B:B,SMALL(
data!E$2:INDEX(data!E:E,data!G$2),ROW(A1))))
こんな感じです。
続いて LOOKUP関数を使った二つ目の表を使う場合の方法です。
E1セルに「0」と入力
■dataシートの D2セル：「１○２○」データ1にもデータ2にもあるデータ
=IF(B2="","",IF(D1=D2,E1+1,E1))
9行目までオートフィルコピー
■dataシートの G5セル：「１○２○」の個数：=MAX(E:E)
これで下準備終了。あとは抽出です。
E1セルに「0」と入力
■listシートの C1セル：「１○２○」
=IF(data!G$4<ROW(A1),"",INDEX(data!B:B,MATCH(ROW(C1)-1,
data!E$1:INDEX(data!E:E,data!G$2))+1))
以上が作業列を使った方法です。面倒に思われるでしょうが 一般機能ではこのくらいの手間がかかってしまいます。
★その３：フィルタオプションを使った方法
「ＡにもＢにもあるデータ」に限って云えば フィルタオプションの設定を使う方法もあります。この方法のいいところは 重複があっても問題ないのと 元データのソートが不要な点です。抽出の手順だけ記録マクロで登録しておけば
手間なく簡単に処理できます。注意して欲しいのはどちらの項目も
同じ項目名にしておく必要があります。
Ａ１セルとＢ１セルの項目名が同じことを確認した上で
A1セル上で[データ]→[フィルタ]→[フィルタオプションの設定]
その際
こういう表示が出ても無視して[ＯＫ]を押して下さい。
[抽出先]を[指定した範囲]に設定
リスト範囲：A:A
検索条件範囲：B:B
抽出範囲：D1
[重複するレコードは無視する]にチェックを入れて[OK]
とっても簡単です。2002以外では「前方一致」になってしまうのが珠にキズですが……
★その４：配列数式を使った方法
この方法にメリットがあるとしたら重複を省いたりソートしなくていい ということだけです。処理が非常に重いのでお勧めできません。簡易的に計算したい時用だと思ってください。
★「１○２×」の抽出
D2セル上で[挿入]→[名前]→[定義]
名 前：抽出１
参照範囲：
=SMALL(IF(ISNA(MATCH($A$2:$A$9,$B$2:$B$9,0))*
(MATCH($A$2:$A$9&"",$A$2:$A$9&"",0)=ROW($2:$9)-1)*
($A$2:$A$9<>""),ROW($2:$9)),ROW($A1))
→[OK]
■D2セル
=IF(ISERR(抽出１),"",INDEX(A:A,抽出１))
下方向にオートフィルコピー
他も同様です。
★「１×２○」の抽出
E2セル上で[挿入]→[名前]→[定義]
名 前：抽出２
=SMALL(IF(ISNA(MATCH($B$2:$B$9,$A$2:$A$9,0))*
(MATCH($B$2:$B$9&"",$B$2:$B$9&"",0)=ROW($2:$9)-1)*
($B$2:$B$9<>""),ROW($2:$9)),ROW($A1))
→[OK]
■Ｅ２セル
=IF(ISERR(抽出２),"",INDEX(B:B,抽出２))
下方向にオートフィルコピー
★「１○２○」の抽出
F2セル上で[挿入]→[名前]→[定義]
名 前：抽出３
=SMALL(IF(ISNUMBER(MATCH($B$2:$B$9,$A$2:$A$9,0))*
(MATCH($B$2:$B$9&"",$B$2:$B$9&"",0)=ROW($2:$9)-1)*
($B$2:$B$9<>""),ROW($2:$9)),ROW($A1))
→[OK]
■E2セル
=IF(ISERR(抽出３),"",INDEX(A:A,抽出３))
下方向にオートフィルコピー
データ量が多い時は控えた方がいいと思います。
→参考ファイル(031.xlsx)
