エクセルで特定の項目だけを抽出して別シートに表示する

シェアする

「日々記録している表の中から、特定の項目だけを抽出した表を別シートに出力したい。入力すれば即反映されるのが望ましい」といった要望を受けました。

そうですね、その都度、集計するならフィルターオプションを利用するのが手軽ですが、自動で反映させるなら別の方法が良いですね。方法としては、作業列を用意したり、配列数式を利用したりと、マクロを利用したりと色んなやり方がありますが、ここでは比較的すっきりした形で使える方法を紹介しておきます。

例として、下のSheet1にあるような表から、鉛筆に該当する行だけをSheet2に抽出する形で説明します。

20160516-Excel-08

スポンサーリンク

条件に合う行を順番に取り出す

まずSheet1が次のように表になっているとします。

20160516-Excel-01

最初に、Sheet2に項目名を用意します。

20160516-Excel-02

次にA2セルに次の数式を入力します。

=INDEX(Sheet1!A$1:A$20,SMALL(INDEX((Sheet1!$B$2:$B$20<>”鉛筆”)*1000+ROW(Sheet1!$B$2:$B$20),),ROW(A1)))

20160516-Excel-03

INDEX 指定範囲の指定された行列にある値を求める
SMALL 指定範囲で小さい値から指定順位の値を取り出す
ROW 行番号を求める

1行目が「鉛筆」なら「1」、「鉛筆」じゃなければ「1001」とかにして順番を後ろにし、少ない番号から表示(つまり「鉛筆」のある行だけを上から順番に表示)する仕組みです。

これを右方向と下方向へオートフィルします。

20160516-Excel-04

これで、鉛筆がある行だけが抽出されました。ただ、鉛筆が記載されている行以上の行は「#REF!」になってしまうので、これを修正します。

20160516-Excel-05

該当する行数以上は空白にする

A2セルの数式を下記に修正します。赤字部分を追加しています。

=IF(ROW(A1)>COUNTIF(Sheet1!$B$2:$B$20,”鉛筆”),””,INDEX(Sheet1!A$1:A$20,SMALL(INDEX((Sheet1!$B$2:$B$20<>”鉛筆”)*1000+ROW(Sheet1!$B$2:$B$20),),ROW(A1))))

20160516-Excel-06

IF 条件により処理を分ける
COUNTIF 条件に合う個数を数える

鉛筆のある行数以上の行は空白にするように指定しています。これをオートフィルすれば次のようになります。

20160516-Excel-07

これで、見た目もすっきりしました。Sheet1の表を修正したり追加したりすれば、自動的にSheet2の表にも反映されるはずです。

ちなみに、別のやり方として「配列数式」を使う方法は、下記を参考にしてみて下さい。

エクセルで特定の項目だけを抽出して別シートに表示する(配列数式)
昨日「エクセルで特定の項目だけを抽出して別シートに表示する方法」を紹介しましたが、そこでも触れたように、これには色んなやり方があります。 ...

シェアする

フォローする

おすすめ記事
おすすめ記事
スポンサーリンク

コメント

  1. 藤中 和彦 より:

    始めまして。私はとある会社員で、総務を担当しています、藤中と申します。エクセルはそこそこ理解できるつもりでいます。マクロは分かりません。
    会社がまだ、設立から間がなくて、会計管理システムが導入されておりません。
    なので、エクセルで色んなものの管理をしておりますが・・・
    先日、ある問題にぶつかりました。いろいろ試してみているうちに、あなたのページに、まさに私が今やろうとしていることがありましたので、早速試してみましたが、うまく行きまません。それで、「特定の項目だけを抽出して別シートに表示する」のサンプルをそのまま関数なども入力してみましたが、これまたうまく行きませんでした。私がどこかで間違っているのだと思います。
    それで、私がやるべきことを簡単な表にしています。(実物は、取引会社名や取引額などが表示されているので、そのままでは色々問題がありますので)
    この表から、別ページに、「抽出結果表」のように表示する方法をご教授願えませんでしょうか。どうぞよろしくお願いします。

    「セルD1と同じものを表の中から抽出して、抽出結果表のように表示したいのです。
    ただし、ピボットテーブルや、フィルターは使用せずに、関数で表示させたいのです。
    なんとかなりませんでしょうか。どうぞよろしくお願いします。

      A B C   D
    1   No 社名  金額   るるる
    2  1  PPP   50
    3   1 せせせ 19000
    4  1  るるる 12000
    5   2 PPP 80
    7  2 せせせ 20000
    8  1  JJJ 7890
    9   2 るるる 26600
    10  3 PPP 900
    11 1 KKK 15900
    12 3 るるる 55555

    抽出結果表

    1  No 社名   金額
    2  1  るるる  12000
    3  2  るるる  26600
    4  3  るるる  55555
    5
    6
    7


    • ひろぐら より:

      お問い合わせをありがとうございます。早速シート1に該当の表を入れ、シート2に結果を入れてみましたが、問題なく出来ましたね。
      具体的には、シート2のA2セルに下記を入れました。

      =INDEX(Sheet1!A$1:A$20,SMALL(INDEX((Sheet1!$B$2:$B$20<>Sheet1!$D$1)*1000+ROW(Sheet1!$B$2:$B$20),),ROW(A1)))

      記事内容とほぼ同じで、シート1のD1に求めたい言葉(るるる)があるので、シート1のD1を参照するようにしているくらいですね。後は右方向と下方向にオートフィルしているだけです。こちらの式はExcel2002以降であれば使えると思うので、恐らくバージョンも問題なさそうなんですけれど……。どうでしょうか。

  2. 藤中 和彦 より:

    早速のご返答ありがとうございます。こんなに早く返答が来るとは予想しておりませんでした。びっくりです。
    やってみているのですが、どうもうまく行きません。もう少し粘ってみます。
    余談ですが、私も若いころ、福岡から職場の転勤で、広島の戸坂に5年ほど住んでおりました。福岡に戻って30年以上経過しましたから、もうほとんど記憶がかすんでいます。(笑)

    • ひろぐら より:

      戸坂は市内中心部に近くていいところですよね。ちょっと渋滞しますが。
      それで、サンプルファイルをメールでお送りしましたので、試してみて下さい。
      やりたい内容に合っているかは分かりませんけれど。

  3. 藤中 和彦 より:

    至れり尽くせりのご対応ありがとうございます。サンプルを見させて頂きました。
    美しいです。なんだか、積年の便秘が解消されたような気分です。
    また、困ったことに出くわしたら、相談させて下さい。本当にありがとうございました。
    藤中