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

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

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

例として、下の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の表にも反映されるはずです。

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

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

スポンサーリンク

シェアする

フォローする