複数列の一致する行を検索する数式はありますか？

質問

2007でブロック別の10のブックに分けたデータがあり、その中で特定の複数列が一致するデータを、現在は数列をフィルタ抽出し、それと一致するものが他の列にあるかどうか、対象列を変えて調べる、というムダかな？とも思えることをやっています。
実際の表とは別に仮定として質問させていただきたいのですが、A~C,D~F,G~Iの各3列ごとに異質ではあるが表示を同一としているデータがあるとして、調べた結果、ABCが5,10,15であるものが100,500,1000行目に、CDFが5,10,15であるものが1500,2000行目に、G H I が5,10,15であるものが3000行目にあることが判りました。
このケースで3列の組合せ一致があるかないかは調べてみないと判らないのと、フィルタをかける場合、最初に基準にする3列をどれにするかで異なるのと、全てを調べるには何年かかるのかな？という感じです。
全表の中で、同じ組合せがあるなら、それはどんな組合せで、どこにあるか、を調べる数式はあるのだろうか？という疑問をもっています。
VLOOKUPについて本を調べてみたのですが、検索する指定値が決まっているわけではないので違うかな？と思っています。
関数はSUMとCOUNTを少し活用できるようになったかな？のレベルです。
よろしくお願いします。

回答No.4

KURUMITO · ベストアンサー

重複するセル番地と重複の組み合わせなどを表示させるためには多少複雑な操作になりますが次のようにしてはどうでしょう。
シート1のＡ列からＩ列にかけてデータが入力されているとして、作業列としてＪ1セルには次の式を入力します。
=A1&"/"&B1&"/"&C1
Ｋ1セルには次の式を入力します。
=D1&"/"&E1&"/"&F1
Ｌ1セルには次の式を入力します。
=G1&"/"&H1&"/"&I1
Ｊ1セルからＬ1エルを選択し下方にオートフィルドラッグします。
答えの表はシート2に作るとしてシート2では次のようにします。
Ａ1セルにはセル番地、Ｂ1エルには組合せと文字を入力します。
Ａ2セルには次の式を入力します。
=IF(ROW(A1)<=MATCH(10^10,Sheet1!$A:$A),"A"&ROW(A1),IF((ROW(A1)-MATCH(10^10,Sheet1!$A:$A))<=MATCH(10^10,Sheet1!$D:$D),"D"&(ROW(A1)-MATCH(10^10,Sheet1!$A:$A)),IF((ROW(A1)-MATCH(10^10,Sheet1!$A:$A)-MATCH(10^10,Sheet1!$D:$D))<=MATCH(10^10,Sheet1!$G:$G),"G"&(ROW(A1)-MATCH(10^10,Sheet1!$A:$A)-MATCH(10^10,Sheet1!$D:$D)),"")))
Ｂ2セルには次の式を入力します。
=IF(A2="","",IF(LEFT(A2,1)="A",INDIRECT("Sheet1!J"&MID(A2,2,10000)*1),IF(LEFT(A2,1)="D",INDIRECT("Sheet1!K"&MID(A2,2,10000)*1),IF(LEFT(A2,1)="G",INDIRECT("Sheet1!L"&MID(A2,2,10000)*1),""))))
Ａ2セルとＢ2セルを選択して下方にオートフィルドラッグします。
これでシート1でのＡ列からＩ列までのデータがＡにセル番地で、Ｂに組合せの形で縦にまとめて表示されることになります。
そこでＡ列およびＢ列を選択して「コピー」し、Ｃ1セルを選択してから「形式を選択して貼り付け」で「値」にチェックを付けて貼り付けます。
Ｅ1セルには重複と文字を入力します。
Ｅ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(OR(D2="//",D2=""),"",IF(COUNTIF(D:D,D2)>1,"重複",""))
最後にＣ列、Ｄ列、Ｅ列を選択してから「ホーム」タブの「並べ替えとフィルタ」をクリックし、「ユーザー設定の並べ替え」を選択します。表示の画面で最優先されるキーに「組合せ」を選択し、「先頭行をデータの見出しとして使用する」にチェックを付けてＯＫします。「並べ替えの前に」の画面では「数値に見えるものはすべて・・・」にチェックをしてＯＫします。重複のデータ同士が並べられセル番地や組合せの形などを見ることができます。

お礼

現在やっていることに比べたらかなり効率化できそうなので、これに取り組んでみようと思います。
ありがとうございました。

回答No.6

tag1701

すみません。回答者Ｎｏ２です。
データの件数とはABC列を１データを指しています。
これを記述した関数で１つの数値（例えば　23.45.67とあれば234567
になおす。）にして、その数値を昇順で並び替える事により
同じ数字のものが並ぶ。これをフィルター／個数にする事により
１個以外は重複（A.B.C)があった事が分かるという考えです。

約８０列との事で、３列ずつということでしたので８１列と想像し
５０，０００行という事ですので、私が言うところのデータ量は
１３５万行になるので、駄目みたいでした。。。
お役に立てませんでした　m(_ _)m
（アクセスソフトでもあればこの考えでいけるのですが）

お礼

わざわざありがとうございます。
そのような限界があることがわかっただけでも勉強になりました。

回答No.5

rivoisu

あるデータパターンを探すというのではなく重複しているもの全てを検出したい。
ということなのでしょうか。
どうやるにしてもまずABC,DEF、GHIを連結した値を作ることから始めることになりそうです。

どうしてもマクロは無理だとするとちょっとつらい手作業になりますが。
ABC、DEF、GHIはそれぞれ結合した値をJ,K,L列に作ります。
J列をコピーして別のシート(WKSとします）のA列に
WKSのB列に”A”、C列に連番
同様にK,L列もその下に貼付けB列にそれぞれ”B","C"、C列にそれぞれの連番を振ります。
でその3列をA列でソート
重複データが見つかります。（ここは関数をD列に入れると簡単）
そのデータの元がどの列の何行目にあるかはB列C列を見ればわかる。
さてその後どうします？

お礼

この3カ所の部分が一致したデータだけを集めて、他のデータになにがしかの法則性があるものかどうかを検証してみたいので、結果的に徒労に終わる可能性もあるのですが・・。
とりあえず見つけ出して集めることができれば一歩前進できます。その先にてまたお世話になることもあろうかと思いますので、その節はよろしくお願いします。
ありがとうございました。

回答No.3

ASIMOV

データーの量によっては、実用的じゃないかも知れませんが．．

３列のデーターを結合したセルを用意してそれを検索するという方法です

下図で
A1～G7が元のデーター
A11～G17が結合したデーターです
A11には「=A1&B1&C1」と入れ、右、下にコピーします
HとI列はデーターがG列以降も次の行に連続している場合のダミーです
H1は =A2 とし、コピーします

A9を書式設定で文字列にしておき
検索したいデーターを入れます
A11～G17に条件付き書式で
セルの値が、次の値に等しい、=$A$9
としておくと、一致したセルが一目でわかります

お礼

やはり説明がまずかったようで、お示しいただいた表でいうと、一行目にあるA=60,B=43,C=69と同じ並びとなっているものが、DEFやGHIにあるかどうかを調べるために現在は、D列で60を、E列で43を、F列で69をフィルタで抽出し、次にG列で60を、H列で43を、I列で69を、とやって一致があるかないか、あるときはその行番号をメモり、一致する行を集めてJ列以降のデータがどうであるのか、を検証しています。
一致するケースがない場合の方が多いのですが、ABCを基準に一行目から順次やったとして、次にDEFを基準に、次にGHIを基準にとやって行くことになるのですが、ABCを基準に調べた段階ですでに調べ済みで、DEFやGHIの中で調べなくても良いものがそれなりに含まれているハズなのですが、これはABCを基準に調べたときにこうであったの記憶がある道理もなく、他に方法がないのかなぁと思いながらやっているものですから。
ABC,DEF,GHIの各データは元々３ツのセルにあった3種類のものを、並べ替えやフィルタの都合から1種類につき3分割したものです。(例えば、A列に08-23-10となっていたものをAに8,Bに23,Cに10と分割しました)
仮に、作業列的なものにもう一度、連結させたものを入れ、その列がW,X,Yであったとして、3列が一致あるいはどれか2列だけが一致の場合を見出す方法はありますか？(一致するものを単純にカウントすることはできているのですが、どの行なのか、どんな内容なのか、を特定するのが大変なものですから)

回答No.2

tag1701

データの件数は１００万件を超えてますか？
エクセル２００７であれば約１０４万行あるので
別シートに全てABC(2から)列にコピーアンドペーストでまとめて
D2列に=CONCATENATE(A2,B2,C2)として最終行までコピー。
１行目に項目名作成（何でもいい）
D列で昇順並べ替えを行い、データ-小計-D列項目をグループの基準
集計の方法を個数としてOKにすると同一の場合２個以上で表示されます
左側の－を+にすると個数だけ表示になるので、フィルタを設定し
０以外にすれば理論上はマクロなしでも出来そうです。
（但し、件数が膨大なようで、実際メモリオーバー等起きないかは
　不明です。　試せないのですみません）

お礼

データの件数というのは使用しているセルの個数になりますか？
全てのセルが埋まっているわけではありませんが、約80列、5万行ですから、全てが埋まるとすると400万ということになるでしょうか。
メモリオーバーというのは知りませんでしたが、時々、処理に時間を要することがあります。
一応、ためしてみたいと思います。
ありがとうございました。

回答No.1

rivoisu

要するに横並びの連続3列が5,10,15の行を探り出すということでしょうか？
＞CDFが5,10,15であるものが1500,2000行目
DEFの間違い？それともABC,BCD、CDEと調べる？
どっちにしてもマクロかなぁ　と思います。

お礼

DEFのマチガイでした。
マクロですか。かなりムリそうです。
もっとレベルが向上したらまたお願いします。
ありがとうございました。