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

シェアする

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

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

別の方法の中からもう1つ、「配列数式」を利用した方法を紹介しておきます。配列数式とは、たとえば作業列を使って行うような複数の処理を、1つの式で行うための式です。自分しか扱わない表であれば、この方法も比較的分かりやすいかなと思います。ただ、他人も編集したりする場合は、修正する時などに特殊なキー操作が必要なので、あんまりオススメは出来ませんが。

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

20160516-Excel-08

スポンサーリンク

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

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

20160516-Excel-01

最初に、Sheet2に項目名を用意します。ここまでは昨日の例と同じです。

20160516-Excel-02

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

=INDEX(Sheet1!A$2:A$20,SMALL(IF(Sheet1!$B$2:$B$20=”鉛筆”,ROW(Sheet1!$A$1:$A$19)),ROW(A1)))

20160517-Excel-01

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

そして、ここが大きく違うのですが、数式を編集状態のまま「Ctrl」キーと「Shift」キーを押しながら「Enter」キーを押します。すると、数式が「{}」で囲まれ、配列数式として扱われます。

20160517-Excel-02

これを右方向と下方向へオートフィルします。これで、鉛筆がある行だけが抽出されました。ただ、昨日と同様に、このままでは鉛筆がない行はエラーになってしまうので、これを修正します。

20160517-Excel-03

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

ここは昨日と同じように、A2セルの数式に、赤字部分を追加します。

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

20160517-Excel-04

COUNTIF 条件に合う個数を数える

数式を修正した場合も同様に、「Ctrl」キーと「Shift」キーを押しながら「Enter」キーを押します

20160517-Excel-05

これをオートフィルすれば次のようになります。これで、Sheet1の内容に応じて、自動的にSheet2の表にも反映されます。

20160517-Excel-06

このように、配列数式を使えば、条件を与えた結果の処理を一括で行えるようになるのですが、ここで説明したように数式の修正等を行うと、その都度「Shft」+「Ctrl」+「Enter」で確定する必要があります。最初に述べたように、他人も扱うのであれば、この操作に気を付けてもらうように、ちょっと注意が必要です。

シェアする

フォローする

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