• ベストアンサー
  • 暇なときにでも

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

excelのデータで、該当する項目のある行のみの抽出をオートフィルタもマクロも使用せずに、関数で行う方法があれば教えてください。     A B 1  桃太郎 123456 2  猿   122222 3  キジ  123555 4  桃太郎 122245    … 1000 桃太郎 002145 ↑このような表で「桃太郎」だけを抽出したいのです。 イメージとしては、ボックスに「桃太郎」と入力するだけで結果が一覧として出るようなものを作りたいのです。 さらにB列が昇順に並び替えされていると、なおよいのです。 随時更新するデータなので、その都度の最新の表が欲しいのです。 オートフィルタ→並べ替えをすれば簡単なのですが、エクセルを全く使えないような人がいる職場環境でして、教えるのが面倒というのとデータがぐちゃぐちゃになったら困るので、誰でも簡単にできる方法はないものかと考えています。 また環境的にマクロはあまり使いたくないのです。 よろしくお願いします。

共感・応援の気持ちを伝えよう!

  • 回答数4
  • 閲覧数5700
  • ありがとう数2

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答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関数で好みの列のみ表示できるのが便利ですね。 思い通りの方法を考えてくださってとても感謝しています。

その他の回答 (3)

  • 回答No.4
  • stones
  • ベストアンサー率40% (88/218)

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

共感・感謝の気持ちを伝えよう!

  • 回答No.2
  • stones
  • ベストアンサー率40% (88/218)

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
  • imogasi
  • ベストアンサー率27% (4634/16581)

他に何度も回答してますが、 (例データ)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以下に複写。 (関数式)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

  • エクセルのオートフィルタについて

    エクセルのオートフィルタについて教えてください。 エクセル2003です。 データ-並べ替え で複数の列に対してレベルごとに昇順で並べ替えをしますが、これをオートフィルタではできないのでしょうか? 例えば、A列にクラスデータがあり、それをオートフィルタで昇順にしておいて、それをレベル1として、B列に点数があり、それをオートフィルタで降順にして、それをレベル2とするような使い方です。

  • excelオートフィルタで

    お世話になります。 excel97でオートフィルタを使っています。 数字の列で使っているのですが、””と等しいや””を含むについては抽出してくるのですが、””より大きいや””以上については抽出してきません。 何か考えられることがありますでしょうか? ちなみに 1.並べ替えでは昇順、降順正常に動作します。 2.表示形式は標準です。 以上

  • エクセルのオートフィルタについて

    質問させてください エクセルのオートフィルタ機能でデータを抽出したのですが、 他の表からも同様にオートフィルタ機能でデータを抽出しようとすると、 ツールバーのオートフィルタの所に既にチェックが入っていて、 そこをクリックしてしまうと、最初の表で抽出したデーターが元に戻ってしまいます。 オートフィルタは2つの表には適用できないのでしょうか? それとも、最初の表のオートフィルタを確定し、次の表に進むことは出来るのでしょうか? どなたか、ご指導よろしくお願い致します。

  • オートフィルタで抽出結果がない場合

    excel2003を使用しています。 以下のような表があります。 コード  社名  終了日  A   あ社   6/1  B   い社         C   う社      終了日に日付が入ったら別シートにその結果をコピーしたいのですが、もし、終了日に日付がまったくない時は、オートフィルタを設定しないか、コピーをしないかにしたいです。 この表一つでしたらマクロを実行しない方法もあるのですが、実際は複数の表を同時にオートフィルタしてコピーしてしまっているので、終了日に日付のまったくない表もあり、その場合、抽出結果をコピーした表がおかしくなります。 説明が不十分かも知れませんが、よろしくお願いいたします。

  • エクセルのオートフィルタ

    エクセルで住所録を作り、そのうちの東京都のデータだけオートフィルタで抽出し、それを別のシートにコピーして表を作りたいのですが、抽出はできても表にすることができません。オートフィルタで抽出したデータだけを一覧表にすることはできないのでしょうか?

  • エクセル・マクロ オートフィルタからコピー

    Excel2000使用中です。 縦にたくさん項目が並んでいる表にオートフィルタを行い特定の項目のみを抽出し、抽出した項目のみを他の表にコピペをするという作業をマクロで行ないたいです。 ただし抽出した項目数は50~75項目で日によって変わりますが、抽出先の表は75項目で、75に足りない部分は空白です(空白は必須です)。 始めはオートフィルタをかけた後で上から75行コピーして貼り付けをしてマクロ設定してみたのですが、うまくいきません。 このように日々変化する項目があってもマクロを設定する方法はありますか?

  • エクセルのオートフィルタで抽出できない

    エクセルのオートフィルタでデータを抽出しようとしていますが、あるレコード以降のデータからは条件にあっていても抽出されません。このあるレコードははっきり特定できません。 およそ4000以降くらいかと思われますが・・ この表の中に空白行はありません。 コンボボックスがありますが、何か関係ありますか? よろしくお願いします。

  • オートフィルタで抽出したデータの平均の求め方

    発注から出庫までのデータを書き出した表で、オートフィルタ抽出をかけて、その結果のみの平均値をだしたいのですがどうしてもうまくいきません。こういう場合はどうするのでしょうか。

  • オートフィルタの抽出速度を上げるには?

    エクセルで、データを抽出する際よく「データ」→オートフィルタ」を使うのですが、データ量が増えるにつれデータ抽出するのに5秒ぐらいかかるときがあります。 それが、別のエクセルファイルで、私がよく使うファイルよりもはるかに重いものがあるのですが、そのファイルで「オートフィルタ」を使ってもデータ抽出するのに5秒もかかりません。 もしかして、セル入力に「リスト」を使うのと「直接入力」とでオートフィルタの抽出速度の差が出てるのかなと思ったりもしたのですが、どうなんでしょうか? いろいろ調べたのですが、わからないので、アドバイスを下さい。

  • オートフィルタの抽出結果をマクロで表示するには?

    A 1000  D 200 A 500 A 1000 B 2000 上表をオートフィルタでA及び1000で絞ると左下に『4レコードで2個です。』と抽出個数が表示されると思いますが、その個数(2個)をマクロを使って表示させるにはどうすればいいでしょうか? マクロの記録でオートフィルタをかけるところまではできるのですが、個数表示で壁にぶち当たっています。 ちなみに1000を検索させて個数を出すという方法もありますが、2行目のデータが他の行にもあるため、データを検索させて個数を導くマクロは作られないので、オートフィルタからのマクロを作成しています。 マクロ初心者のため、分かる方いらっしゃいましたらご教授をお願いします。