excelのデータで、該当する項目のある行のみの抽出をオートフィルタもマクロも使用せずに、関数で行う方法があれば教えてください。
A B
1 桃太郎 123456
2 猿 122222
3 キジ 123555
4 桃太郎 122245
…
1000 桃太郎 002145
↑このような表で「桃太郎」だけを抽出したいのです。
イメージとしては、ボックスに「桃太郎」と入力するだけで結果が一覧として出るようなものを作りたいのです。
さらにB列が昇順に並び替えされていると、なおよいのです。
随時更新するデータなので、その都度の最新の表が欲しいのです。
オートフィルタ→並べ替えをすれば簡単なのですが、エクセルを全く使えないような人がいる職場環境でして、教えるのが面倒というのとデータがぐちゃぐちゃになったら困るので、誰でも簡単にできる方法はないものかと考えています。
また環境的にマクロはあまり使いたくないのです。
よろしくお願いします。
No.3ベストアンサー
- 回答日時:
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様からご回答いただいた分に対しお礼申し上げます。
この通り実行しましたらうまく行きました。
並び替えが複数あったので、上記のD1のところで10分の1、100分の1にして加えてみました。また、1カ所はアルファベットだったのでIF関数で数字に換えてみました。
OFFSET関数で好みの列のみ表示できるのが便利ですね。
思い通りの方法を考えてくださってとても感謝しています。
No.4
- 回答日時:
#2です。
すみません。
忘れてましたが、D1に桃太郎とかキジとかデータを抽出したい項目を
入力してください。
それと、Largeになっていますが、昇順なので、Smallにしなければ
ならないですね。
No.2
- 回答日時:
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)))
と入力し、下までコピー。
以上で如何でしょうか。
ご回答をありがとうございます。考えてはみたのですが、十分理解できなかったのでもう一度教えていただけると助かります。
E1の式のSMALL関数が2行目以降#NUMエラーになってしまいます。ちなみにその内側にあるIF関数の結果はすべて1(D1と一致)か空白(不一致)になります。
お手数をお掛けいたしますがよろしくお願いいたします。
No.1
- 回答日時:
他に何度も回答してますが、
(例データ)Sheet1のA1:C10と作業域D列
B1は抜き出すキャラクターを入力するセル。
A列 B列 C列 D列
桃太郎
1桃太郎1234561
2猿1222221
3キジ1235551
4桃太郎1222452
5キジ1233332
6桃太郎21453
7犬12333
8鷹4455663
9桃太郎9923124
(関数式)
Sheet1のD2に
=COUNTIF($B$2:B2,$B$1) $が大切
D2以下に複写。
(関数式)Sheet2の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に複写し、最後の引数を2に変える。
=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に複写。最後の引数を3に変える。
C3以下に複写。
$C$100と$D$100の100は適当な下行までと置換えてください。
(結果)Sheet2のA2:C5
1桃太郎123456
4桃太郎122245
6桃太郎2145
9桃太郎992312
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(データベース) 20万行あるデータを動かしたい 2 2023/06/13 15:21
- Visual Basic(VBA) エクセルについて教えてください。 3 2023/06/28 09:11
- Excel(エクセル) Excel(エクセル)でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法 【内容】 添 4 2023/01/30 17:17
- ドラマ 桃太郎侍は何故一方手裏剣で最初に敵を倒さないの? 1 2022/11/07 19:29
- 日本語 日本語のアクセントについて 2 2022/04/28 22:57
- アニメ 幽遊白書・暗黒武術会「3位決定戦」が実施された場合? 1 2022/10/30 00:00
- 心理学 ゼレンスキー夫妻の戦い 1 2022/03/26 09:43
- Excel(エクセル) エクセルで重複データを行ごとに抽出したい 4 2022/12/05 08:18
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- Excel(エクセル) 関数EXACT(文字列,文字列)とexcelVBA 3 2022/04/14 15:07
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】スペースがいくつ入っ...
-
西暦や和暦の表示をyyyymmdd表...
-
【Microsoft Office Excel Comp...
-
Excelはなんで先頭の0を消すん...
-
Excelのセルを飛ばして入力する
-
別シートからの文字を変更
-
エクセルの行の抽出について質...
-
Excelのオートフィル
-
Excel 2019 のピボットテーブル...
-
スプレッドシート クエリ関数 1...
-
excelの不要な行の削除ができな...
-
Excel初心者です。 詳しい方、...
-
【Excel】セル内の時間帯が特定...
-
Excel初心者です。 詳しい方、...
-
EXACT関数とIF関数の組み合わせ...
-
Excelのグラフ軸について
-
スマートな関数を教えて下さい。
-
Excelで全角を半角にしたいので...
-
【マクロ】エクセルにかいてあ...
-
Excel:一部のフォントでセルの...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報