エクセル・オートフィルタを使わず関数で抽出をするには

質問

excelのデータで、該当する項目のある行のみの抽出をオートフィルタもマクロも使用せずに、関数で行う方法があれば教えてください。
　　　　A B
1　　桃太郎　123456
2　　猿　　　122222
3　　キジ　　123555
4　　桃太郎　122245
　　　…
1000　桃太郎　002145
↑このような表で「桃太郎」だけを抽出したいのです。
イメージとしては、ボックスに「桃太郎」と入力するだけで結果が一覧として出るようなものを作りたいのです。
さらにＢ列が昇順に並び替えされていると、なおよいのです。
随時更新するデータなので、その都度の最新の表が欲しいのです。

オートフィルタ→並べ替えをすれば簡単なのですが、エクセルを全く使えないような人がいる職場環境でして、教えるのが面倒というのとデータがぐちゃぐちゃになったら困るので、誰でも簡単にできる方法はないものかと考えています。
また環境的にマクロはあまり使いたくないのです。

よろしくお願いします。

回答No.3

moon_piyo · ベストアンサー

B列の値は数字のみでしょうか？
こんな方法を考えてみました...

C1を条件値入力セルとしました

(1)下記のセルにそれぞれの式を入れる
D1: =IF(A1<>$C$1,"",B1+ROW()/10000)
E1: =RANK(D1,D:D,1)
F1: =MATCH(ROW(),E:E,0)
G1: =IF(ISERROR(F1),"",OFFSET(A$1,F1-1,0,1,1))
H1: =IF(ISERROR(F1),"",OFFSET(A$1,F1-1,1,1,1))

(2)D1:H1の内容を十分な件数だけ下方向にコピー

(3)D,E,Fの列は非表示にしましょう

(4)C1に桃太郎を与えると G,H列に抽出してソートした結果がでます

お礼

どうもありがとうございます。たくさんの方からご回答をいただいているのですが、数式の意味を理解するのに時間がかかっていまして、まずmoon_piyo様からご回答いただいた分に対しお礼申し上げます。

この通り実行しましたらうまく行きました。
並び替えが複数あったので、上記のＤ１のところで１０分の１、１００分の１にして加えてみました。また、１カ所はアルファベットだったのでＩＦ関数で数字に換えてみました。
ＯＦＦＳＥＴ関数で好みの列のみ表示できるのが便利ですね。

思い通りの方法を考えてくださってとても感謝しています。

回答No.4

stones

＃２です。
すみません。
忘れてましたが、D1に桃太郎とかキジとかデータを抽出したい項目を
入力してください。
それと、Largeになっていますが、昇順なので、Smallにしなければ
ならないですね。

回答No.2

stones

A列に名前、B列に数値とします。

E1＝{=IF(ROW(A1)<=COUNTIF($A$1:$A$7,$D$1),INDEX(A$1:A$7,SMALL(IF($A$1:$A$7=$D$1,ROW($A$1:$A$7),""),ROW(A1)),1),"")}
（Shift+Ctrl+Enterで確定）
上記E1を必要分（E1000ぐらい？）までと、F1からF1000ぐらいまでコピー。
（コピーはE1セル入力後、セルの右下の十字をつかんで下までと横にコピー）
これで、抽出だけはできます。
次に、昇順に並べ替えるために、
G1=IF(E1<>"",E1,"")
H1=IF(ISERROR(LARGE(F:F,ROW(A1)))=TRUE,"",LARGE(F:F,ROW(A1)))
と入力し、下までコピー。

以上で如何でしょうか。

お礼

ご回答をありがとうございます。考えてはみたのですが、十分理解できなかったのでもう一度教えていただけると助かります。

Ｅ１の式のＳＭＡＬＬ関数が２行目以降＃ＮＵＭエラーになってしまいます。ちなみにその内側にあるＩＦ関数の結果はすべて１（Ｄ１と一致）か空白（不一致）になります。

お手数をお掛けいたしますがよろしくお願いいたします。

回答No.1

imogasi

他に何度も回答してますが、
(例データ）Sheet1のA1:C10と作業域D列
B1は抜き出すキャラクターを入力するセル。
A列　　　B列　　　　C列　　　D列
桃太郎
1 桃太郎 123456 1
2 猿 122222 1
3 キジ 123555 1
4 桃太郎 122245 2
5 キジ 123333 2
6 桃太郎 2145 3
7 犬 1233 3
8 鷹 445566 3
9 桃太郎 992312 4
（関数式）
Sheet1のD2に
=COUNTIF($B$2:B2,$B$1)　＄が大切
D2以下に複写。
（関数式）Sheet２のA2に（敢えて他シートに出しました）
=IF(ISERROR(MATCH(ROW()-1,Sheet1!$D$2:$D$100,0)),"",INDEX(Sheet1!$A$2:$C$100,MATCH(ROW()-1,Sheet1!$D$2:$D$100,0),1))
A2の式をA3以下に複写。
長く複雑なのは空白行のエラー表示防止のためです。
A2の式をB2に複写し、最後の引数を２に変える。
=IF(ISERROR(MATCH(ROW()-1,Sheet1!$D$2:$D$100,0)),"",INDEX(Sheet1!$A$2:$C$100,MATCH(ROW()-1,Sheet1!$D$2:$D$100,0),2))
B3以下に複写。
B2をC2に複写。最後の引数を３に変える。
C3以下に複写。
$C$100と$D$100の１００は適当な下行までと置換えてください。
（結果）Sheet2のA2:C5
1 桃太郎 123456
4 桃太郎 122245
6 桃太郎 2145
9 桃太郎 992312