エクセルを使って同じ数か並び順の違う数を抽出したい

質問

エクセルを使って4ケタの数字を含んだデータを検索したい。

それぞれに名前が付いています。（持ち主です）

その4ケタの数字を打ち込むと

名前も含めたデータが表示されるようにしたい。

しかし、数字は4ケタ並び順が違っても

選ばれるようにしたいのです。

例えばデータが

名前1 1234
名前2 4321
名前3 7318
名前4 7319
名前5 7368
名前6 7369
名前7 7389
名前8 7168

で、検索を４３２１とします。

すると、表示されるデータが
名前1 1234
名前2 4321

と表示されるようにしたい。

いろいろとやってみたのですが、わからなくて困っています。。。

うまく関数で出来ないでしょうか？

エクセルは当方XPまたは２００７です。

ぶしつけで申し訳ないのですが困っております・・・

回答No.6

tom04 · ベストアンサー

No.2です！
補足に
＞#NUM　のエラーが出たというコトですが・・・

配列数式になっていないかもしれません。
（他の原因ならごめんなさい）

前回の方法ですでに作業列を設けていますので、この際もう1列作業列を追加すると
配列数式にする必要がありません。
その一例です。
前回と似たような配置ですが、Sheet1に作業列を2列設けています。

F2セルは前回同様
=IF(A2="","",MIN(B2:E2)&SMALL(B2:E2,2)&SMALL(B2:E2,3)&MAX(B2:E2))
G2セルに
=IF(F2=Sheet2!$B$2,ROW(),"")
という数式を入れ、F2・G2セルを範囲指定し、G2セルのフィルハンドルでずぃ～～～！っと下へコピーしておきます。
※　もうこれ以上データはない！というくらいしっかり下へコピーしておいても構いません。

そしてSheet2の作業セルB2セルを
=IF(COUNTBLANK(B1:E1),"",MIN(B1:E1)&SMALL(B1:E1,2)&SMALL(B1:E1,3)&MAX(B1:E1))
としておきます。

A5セルに
=IF(COUNT(Sheet1!$G:$G)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$G:$G,ROW(A1))))
という数式を入れ列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

この方法の方がPCに負担を掛けなくて良いと思います。m（＿ ＿）m

お礼

追加で回答、ありがとうございます！やってみます。。。

補足

クールでいいですね！動作も軽い！関数を入れた先のデータ未記入でも、エラー出ないので、誰が入力しても、直感的に使えそうです＾＾

回答No.5

kagakusuki

因みに、今回の私の回答とANo.1様の御回答に関しては、SUMPRODUCT関数が使われてはいますが、SUMPRODUCT関数の中で行われる、繰り返し計算の回数は、
私の回答の関数では、H11セル～K11セルの各セルに対して繰り返される、4回の繰り返し計算が行われるSUMPRODUCT関数が１箇所、
ANo.1様の御回答でも、H11セル～K11セルの各セルに対して繰り返される、4回の繰り返し計算が行われるSUMPRODUCT関数が2箇所で、総計8回、
しかありませんから、SUMPRODUCT関数が対象としているデータ数は少く、今回の私の回答とANo.1様の御回答に関しては、計算が重くなる心配は全くありません。
　尚、SUMPRODUCT関数を使わずに同じ働きをする関数を組むと、

=IF(AND(COUNTIF($H$11:$K$11,"*?")+COUNT($H$11:$K$11)>0,INDEX($A:$A,ROW())<>"",COUNTIF(INDEX($B:$B,ROW()):INDEX($E:$E,ROW()),$H$11)=COUNTIF($H$11:$K$11,$H$11),COUNTIF(INDEX($B:$B,ROW()):INDEX($E:$E,ROW()),$I$11)=COUNTIF($H$11:$K$11,$I$11),COUNTIF(INDEX($B:$B,ROW()):INDEX($E:$E,ROW()),$J$11)=COUNTIF($H$11:$K$11,$J$11),COUNTIF(INDEX($B:$B,ROW()):INDEX($E:$E,ROW()),$K$11)=COUNTIF($H$11:$K$11,$K$11)),ROW(),"")

という具合に、似た様な記述を４回繰り返さねばならなくなり、関数が長くなってしまいます。

お礼

テキストも使えるのは、今後いろいろデータ管理に使えそうです！本当にありがとうございます！！こんな使い方ができるなんて・・・すごい！

回答No.4

KURUMITO

一般にSUMPRODUCT関数を使った場合にはデータの数が多くなると計算が重くなります。
次のように作業列を使って対応すればよいでしょう。
F2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(B2:E2)<>4,"",SMALL(B2:E2,1)&SMALL(B2:E2,2)&SMALL(B2:E2,3)&SMALL(B2:E2,4)&COUNTIF(F$1:F1,SMALL(B2:E2,1)&SMALL(B2:E2,2)&SMALL(B2:E2,3)&SMALL(B2:E2,4)&"*"))

答えとなるG１６セルには次の式を入力してK１６セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTIF($F:$F,SMALL($H$11:$K$11,1)&SMALL($H$11:$K$11,2)&SMALL($H$11:$K$11,3)&SMALL($H$11:$K$11,4)&(ROW(A1)-1))=0,"",INDEX($A:$E,MATCH(SMALL($H$11:$K$11,1)&SMALL($H$11:$K$11,2)&SMALL($H$11:$K$11,3)&SMALL($H$11:$K$11,4)&(ROW(A1)-1),$F:$F,0),COLUMN(A1)))

お礼

ありがとうございます＾＾動作良い感じですね＾＾私のデータ量が多いせいか、若干重いかもですが、うれしいです。。。

補足

データに未記入があっても動作するところは良いですね＾＾使えそうです！うれしい！！

回答No.3

kagakusuki

H11～K11に入力される値が数値でも文字列でも関係なく有効な方法です。
　今仮に、M列を作業列として使用するものとします。
　まず、M2セルに次の数式を入力して下さい。

=IF(AND(COUNTIF($H$11:$K$11,"*?")+COUNT($H$11:$K$11)>0,INDEX($A:$A,ROW())<>"",SUMPRODUCT((COUNTIF(INDEX($B:$B,ROW()):INDEX($E:$E,ROW()),$H$11:$K$11)=COUNTIF($H$11:$K$11,$H$11:$K$11))*1)=COLUMNS($H$11:$K$11)),ROW(),"")

　次に、M2セルをコピーして、M3以下に貼り付けて下さい。
　次に、G16セルに次の数式を入力して下さい。

=IF(ROWS($16:16)>COUNT($M:$M),"",INDEX(A:A,SMALL($M:$M,ROWS($16:16))))

　次に、G16セルをコピーして、H16～K16の範囲に貼り付けて下さい。
　次に、G16～K16の範囲をコピーして、同じ列の17行目以下に貼り付けて下さい。

お礼

おお！なんか軽そうな気がしてきました。勉強させていただいて動作確認してみます。。

補足

少し、重いかな？テキストでも行けるところがすごい！

回答No.2

tom04

こんばんは！
質問内容に沿うような感じで・・・

4桁数値セルは必ず一桁の数値が入るものとします。
Sheet1のデータをSheet2に表示するようにしてみました。

↓の画像のような配置になっているものとします。
Sheet1・Sheet2に作業用のセルを設けています。

Sheet1の作業列F2セルに
=IF(A2="","",MIN(B2:E2)&SMALL(B2:E2,2)&SMALL(B2:E2,3)&MAX(B2:E2))
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

Sheet2の作業用セル、B2セルに
=IF(COUNTBLANK(B1:E1),"",MIN(B1:E1)&SMALL(B1:E1,2)&SMALL(B1:E1,3)&MAX(B1:E1))
という数式を入れ、Sheet2のB1～E1セルに検索数値を入力するとします。

Sheet2のA5セルに
=IF(COUNTIF(Sheet1!F:F,$B$2)<ROW(A1),"",INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$F$1:$F$1000=$B$2,ROW($A$1:$A$1000)),ROW(A1))))
これは配列数式になってしまいますので、この画面からSheet2のA5セルにコピー＆ペーストする場合は
A5セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

そして、B5セル(配列数式ではありません）に
=IF($A5="","",INDEX(Sheet1!B:B,MATCH($A5,Sheet1!$A:$A,0)))
として、E5セルまでオートフィルでコピー！

最後にA5～E5セルを範囲指定し、E5セルのフィルハンドルで下へコピーすると
画像のような感じになります。

以上、無理矢理って感じの方法です。参考になれば良いのですが・・・m（＿ ＿）m

お礼

ありがとうございます。動作確認してみます。ビジュアルがわかりやすくて良い感じですね。。。好みです

補足

あれ・・・#NUM！に後半なってしまう・・・もう一度やってみよう・・・良い感じなのにぃ

回答No.1

keithin

「関数でリストアップする」というのは，こちらのような相談室でも大変よく寄せられるご相談の一種ですが，初心者さんが一度は試みてことごとく挫折する「出来ないこと」の一つです。

それでも，無理にでも関数でやりたいのなら，つぎのようなメンドクサイ手間を掛けると出来ます。

＃簡単のため４つのデータはどれも「数値」である前提で

Ｆ２に
=IF(SUMPRODUCT(SMALL(B2:E2,COLUMN(B2:E2)-1)*10^COLUMN(B2:E2))=SUMPRODUCT(SMALL($H$11:$K$11,COLUMN(B2:E2)-1)*10^COLUMN(B2:E2)),ROW(),"")
と記入してリスト下端までコピー

Ｇ１６に
=IF(ROW(G1)>COUNT($F:$F),"",INDEX(A:A,MATCH(SMALL($F:$F,ROW(G1)),$F:$F,0)))
と記入し，右に下にコピー

＃若しくは，同姓同名が無い事が保証できるなら，VLOOKUPでＧ列の名前から続きの(右に付いてる)数字を検索してもかまいません。

お礼

早速の回答ありがとうございます。勉強してみます。動作出来たらうれしいです。

補足

動作確認中です。。。『それでも，無理にでも関数でやりたいのなら，つぎのようなメンドクサイ手間を掛けると出来ます。』とありますが、めんどくさくないやり方があるのでしょうか？もし時間があったらご照会いただけると嬉しいです。