コロナ禍で働き方やライフスタイルの変化を強いられて久しい今、テレワークが働き方として一般化していく認識も定着しつつあります。
Excelを使って業務する中で、手短に条件に一致したデータを抽出しリストにしたいけど、VLOOKUP関数ましてやVBAを組むほどでもない・・
そんな時ちょっと使える技を紹介したいと思います。
スライサーやフィルタオプション、Ofiice365だけ使えるフィルター関数と抽出する機能が様々ある便利なExcelですが、
ちょっとマニアックな配列数式を使ってみましょう。
データと抽出先を用意する
では実践してみましょう。
用意するのはデータの表、入力規則で指定するリストの参照元、条件指定欄と抽出先として適宜セルを決めます。
下図でわかるようにセルA1からC21は大元となるデータ範囲です。
セルE2からE4は「List_Company」と名前の定義をしました。
セルG1は条件指定欄です。前述の「List_Company」を参照できるよう準備します。
「データ」タブのリボンメニューにある「データツール」グループの下にある「データの入力規則」ダイアログを開いて、入力値の種類をリストにし、元の値の入力欄に = List_Company を入力しOKを押してください。
セルG1に▼ボタンが配置され、社名がリスト状になり簡単に切り替えができます。
数式を組む
条件指定欄のセルG1の値に一致するよう果物名と個数を出力するための数式を組みます。
果物名出力先としてセルE2には下記の数式を入力します。
=IFERROR(INDEX($A$1:$C$21,SMALL(IF($A$1:$A$21=$G$1,ROW($A$1:$A$21)),ROW()-1),2),””)
CTRL+SHIFT+ENTERキーで配列数式に確定すると
{=IFERROR(INDEX($A$1:$C$21,SMALL(IF($A$1:$A$21=$G$1,ROW($A$1:$A$21)),ROW()-1),2),””)}
となります。くれぐれも手入力で { } は入れないでください。
セルE2を選択したらセルE21までオートフィルか、表の書式崩れが気になるなら数式のコピーペーストをします。
個数出力先としてセルI2には下記の数式を入力します。
=IFERROR(INDEX($A$1:$C$21,SMALL(IF($A$1:$A$21=$G$1,ROW($A$1:$A$21)),ROW()-1),3),””)
同様にCTRL+SHIFT+ENTERキーで配列数式に確定し、セルI21までオートフィルか数式のコピペをします。
数式の解説
数式内で指定されているセルは下図に示されている3か所です。
数式内で使用している関数は下図を参照してください。
④ IFERROR関数で、エラー値を非表示にしています。
⑤ INDEX関数の配列に①データ範囲、行番号に⑥ SMALL関数、列番号は⑩の部分で値は２ですが、これは①データ範囲の果物名の列番号になります。
⑦ IF関数で①データ範囲の社名列と③条件指定欄と一致するセルの行番号を⑧ ROW関数で、不一致のセルはFALSEで返すようにしています。この部分が下記⑪ 配列で処理されています。
⑪ 社名が「Ａ社」の場合、｛2,3,4,5,6,FALSE,FALSE,FALSE,FALSE・・FALSE,19,FALSE} となります。
⑥ SMALL関数は、⑪ 配列から⑨ ROW関数に準じた順位で小さな数値を返しています。⑨ ROW関数は下図を参照してください。
冒頭でも述べた通り抽出する機能は他に様々あるので、今回の配列数式による抽出はマニアックかなと思います。
しかしこの配列数式は、データとデータの比較と抽出で効果を発揮するのです。
データからリストを抽出する機能や方法は他にもいろいろあるので、追って紹介したいと思います。
クォークでは、皆様からの「こんなことできない？」を形にしていきます。
全国からのお問合せをお待ちしています。お問い合わせ