エクセル：条件データの選出

質問

エクセル
（前提）
項目１から項目５の100行×５列の表
１行目：タイトル　(1,1)が項目１、――、(1,5)が項目５
　　　（2,１）から（100,5）がデータ

（求めたいこと）
項目１データと項目２データの組合せを重複なく数え
Ｇ列以降に存在するだけ

Ｇ列　　　　　　　　　　　　：　H列 　　　　　　：Ｉ列　　　　-----------------
項目１データの１　　　　　　：　項目１データの2：項目１データの3------------
対応する項目２１データの１　：　項目２データの2：項目２データの3------------：
という形で出力したいする。（但し項目１は昇順にしたいが今回それは保留）
（例）
Ａ列　　　　Ｂ列
項目１　　：項目２
Ｃ　　　　　に
Ａ　　　　　い
Ｂ　　　　　ろ
Ａ　　　　　い
Ｂ　　　　　は
Ｃ　　　　　に
Ｄ　　　　　い

（例結果）
Ｇ列
Ａ：Ｂ：Ｂ：Ｃ：Ｄ（ここは必ずしもソートの必要なし）
い：ろ：は：に：い
どう解決すればということで、
1行目に項目1、2行目に項目2ということで
G1=INDEX($A$2:$B$100,SMALL(IF(MATCH($A$2:$A$100&$B$2:$B$100,$A$2:$A$100&$B$2:$B$100,0)=ROW($A$1:$A$99),ROW($A$1:$A$99)),COLUMN(A1)),ROW(A1))
配列数式です。Ctrl + Shift + Enter
2行目にフィル、エラーが出るまで横へフィル
昇順ではないので、結果をコピー、そのまま[形式を選択して貼り付け]-[値]、
あとは、列単位で並べ替え。
このやり方で一つの立派な解なのですが。
（質問）
項目１と項目２がＡ列，Ｄ列と離れている場合はどうするかということです。
もちろんワーク用にシートをコピー後B列~C列を削除して適用すれば求まるのはわかりますが、そうしないでもできる方法があれば教えてください。

回答No.3

noname#262398

#2のNNAQです。
まず補足質問の件ですが、数式を作るのにマクロの自動記録のようなツールは多分ありません。
1)フィルタオプションなどを使って手作業で求める方法
2)関数で求める方法
#2の回答は、2つの方法をご提示してみました、という意味で「関数のみで求めたのが…」と書きました。
数式も日本語も分かりにくくてすいません。説明させてください。

Ａ列
項目１
Ｃ
Ａ
Ｂ
Ａ
Ｂ
Ｃ
Ｄ

仮にA列のみ重複なくさせる場合、普通はCOUNTIFを使いますがCOUNTIF関数は配列に組み込みにくいので、MATCHを使って
B2=MATCH(A2,$A$2:$A$8,0)
C2=ROW(A1)
とすると、下の表になります。
Ａ列　　　　Ｂ列　　　　Ｃ列
項目１
Ｃ　　　　　1　　　　　1
Ａ　　　　　2　　　　　2
Ｂ　　　　　3　　　　　3
Ａ　　　　　2　　　　　4
Ｂ　　　　　3　　　　　5
Ｃ　　　　　1　　　　　6
Ｄ　　　　　7　　　　　7

これで、B列とC列の値が同じものを抽出すればいいことになります。
上の2つの式をまとめて、A列の何番目を抽出すべきかを求めると
B2=IF(MATCH(A2,$A$2:$A$8,0)=ROW(A1),ROW(A1))
C2=SMALL($B$2:$B$8,ROW(A1))
D2=INDEX($A$2:$A$8,C2,1)
下の表になります。
Ａ列　　　　Ｂ列　　　　Ｃ列　　　　Ｄ列
項目１
Ｃ　　　　　1　　　　　1　　　　　Ｃ
Ａ　　　　　2　　　　　2　　　　　Ａ
Ｂ　　　　　3　　　　　3　　　　　Ｂ
Ａ　　　　FALSE　　　　7　　　　　Ｄ
Ｂ　　　　FALSE
Ｃ　　　　FALSE
Ｄ　　　　　7

これを作業列を使わずにひとつのセルに入れると、
=INDEX($A$2:$A$8,C2,1)
この式の C2 の部分に SMALL($B$2:$B$8,ROW(A1))が入って、
その $B$2:$B$8 の部分に IF(MATCH(…))が入るので、
どうしても、長くて分かりにくくなってしまいます。
まあ普通はこんな面倒なことはしません。
フィルタオプションで簡単に出来ますから。

つまり、#2で回答した式は
G1=INDEX($A$2:$E$100,SMALL(…),1)
G2=INDEX($A$2:$E$100,SMALL(…),4)
G1はA列（1列目）に入力された値を出すのでINDEX関数の引数の列番号は「1」、
同じくG2はD列（4列目）なので「4」です。
以前のご質問の際にフィルだけで済むように変数化しましたが、
今回はA列とD列の場合ですし、フィルが1行分なので変数にするまでも無いと思い、定数としました。
「1」「4」の前の COLUMN(A1))は、SMALL関数の引数です。

それから、A2&B2　というデータを作る場合、
Ａ列　　　　Ｂ列
項目１　　：項目２
123　　　　 45
12　　　　　345
このような値だと A2&B2 と A3&B3 が同じになってしまうので、
=A2&"_"&B2
とか、使ってない記号を挟まなければなりません。状況次第ですが。

長くなりましたが、抽出しなければならないシートが大量にあれば、
VBAで処理したほうが良いでしょうし、少量でも数千行ものデータがあるなら配列数式を使うと処理が重くなります。
また、場合によっては手作業でピボットやフィルタオプションを使った方が良いこともあります。
そんなわけで、わたしの回答は、あくまでも一案です…

お礼

この回答にて全てのもやもやが完全になくなりました。ごていねいなご回答感謝に耐えません。ありがとうございました。
これでもう関数に対する恐怖も一層しました。

補足

ＮＮＡＱさんのエクセル力いつも感嘆しています。
一つおききしたいのですが、
G1=INDEX($A$2:$B$100,SMALL(IF(MATCH($A$2:$A$100&$B$2:$B$100,$A$2:$A$100&$B$2:$B$100,0)=ROW($A$1:$A$99),ROW($A$1:$A$99)),COLUMN(A1)),ROW(A1))
で
(1)ROW($A$1:$A$99)が99の理由
(2)式で$A$99を$A$100にするとエラになる理由
(3)Ｇ１式をフィルドラッグして、G3までにもってくるとＧ３がエラーが出る理由などもしできましたら説明ください。

ト100

回答No.2

noname#262398

F列を作業列として、F1に適当に見出しをつけます。
F2=A2&D2
100行目までコピーしたら[形式を選択して貼り付け]-[値]。
[データ]メニューの[フィルタ]-[フィルタオプションの設定]、
抽出先を[選択範囲内]、リスト範囲を[F1:F100]、[重複するレコードは無視する]にチェックして、[OK]。
抽出された状態でA列をコピー、G1に[形式を選択して貼り付け]-[行列を入れ替える]にチェック。
同じようにD列をコピー、G2に[形式を選択して貼り付け]-[行列を入れ替える]にチェック。
フィルタを解除してF列をクリアして完成。

これを関数のみで求めたのが先の配列数式です。
G1=INDEX($A$2:$E$100,SMALL(IF(MATCH($A$2:$A$100&$D$2:$D$100,$A$2:$A$100&$D$2:$D$100,0)=ROW($A$1:$A$99),ROW($A$1:$A$99)),COLUMN(A1)),1)

G2=INDEX($A$2:$E$100,SMALL(IF(MATCH($A$2:$A$100&$D$2:$D$100,$A$2:$A$100&$D$2:$D$100,0)=ROW($A$1:$A$99),ROW($A$1:$A$99)),COLUMN(A1)),4)

複数列のデータを重複無く抜き出すのは、
A2&D2　と、ひとつのデータにしてしまうのが分かりやすいです。
A列C列E列なら =A2&C2&E2　とすれば、離れていても3列以上でも簡単になります。

お礼

いつもお世話になります。ていねいな説明ありがとうございました。
イメージとして式のやってることが掴めるのですが、まだ後ろの
ｒｏｗ,colomunの役割がよくつかめていません。
何か変数的に動かすのにつかっているはずですが。

なおＧ２の終わりのところはA2ではないでしょうか。元の場合A2ですがROW($A$1:$A$99)),COLUMN(A1)),4)

補足

＞これを関数のみで求めたのが先の配列数式です。
この式は，ＮＮＡＱさんの頭の中で作り出した式ですか、それとも何か操作した結果、どこからヒントとなる式情報を得て出した結果ですか。

回答No.1

telescope

ピボットテーブルで似たようなことが出来ると思います。
「データ」－「ピボットテーブル～」でピボットテーブルウィザードを開きます。
ウィザード３／３で既存のシートを選び、G1セルをクリックすればよいのですが、列数が心配なので、とりあえず新規のシートにしておきます。

項目２と項目１を列のフィールドにドラッグします。
項目１が左側に来たほうが良いので、項目２を先にドラッグします。
（あとからでもドラッグすれば順序は変更できます）
データには、項目１、項目２のどちらかをドラッグします。
［A計］［B計］などが表示されていますので、右クリックして［表示しない］を選びます。
必要な部分だけコピーして、G1に値を貼り付ければ出来ると思います。

［A計］［B計］が表示される分列数が1.5倍ほどになるので列数が心配ですが、
列のフィールドにドラッグするとき、まず項目１をドラッグして、右クリックで［フィールドの設定］を選び、［集計］を［なし］にして［表示しない］ボタンを押します。
改めて、項目２、項目１の順に列のフィールドにドラッグして、データフィールドに項目１、項目２のどちらかをドラッグすれば、［A計］［B計］は表示されません。
列数が足りないようでしたら、試してください。