Excelの便利な作業用コマンドに「フィルター機能」と「並べ替え機能」があります。
たとえば、条件に合致する行を抜き出し、別の場所にペーストして、特定の列の値で並べ替えるといった作業です。こうした手作業が、Microsoft365とExcel2021に新搭載されたスピル機能で自動化できるようになりました。スピル機能とは、数式に対する結果が複数だった場合、数式を入力したセルだけでなく、隣接するセルにも結果が表示される機能のことです。
「FILTER」「SORT」「UNIQUE」という3つの関数を使って、「フィルター機能」と「並べ替え機能」で行っていた手作業を自動化し、ユニークリストを作ってみましょう。
目次
特定条件のデータのみを抜き出す
まずはこちらのデータをご覧ください。
今回は、一人ひとりのデータが1行ずつ入力された表から、特定条件の人のデータだけ抜き出してみましょう。関数は「FILTER」を使います。
書式は、
=FILTER(抽出元範囲,抽出条件,空の場合)
です。
119人分の表から範囲を指定し、B列の「女性」を抽出条件に指定すると、数式は、
=FILTER(A2:C120,B2:B120=”女性”,””)
となります。
順番に見ていきましょう。
まず、A列～C列の表（119人分）を抽出元として範囲を入力します。
次に、B列の中の”女性”を抽出条件に指定します。回答者が女性のデータをピックアップして出力することになります。
空のセルについては””、つまり空を返すように指定します。
この数式をE2セルに入力すると、該当するデータが自動的にE2以下に出力されます。これが「スピル機能」です。これまでオートフィルターで抽出した範囲をコピーして別の場所にペーストしていた作業が、自動化されるわけです。
▲冒頭と同じ図。E2セルにFILTER関数を入力する
なお、スピル機能で自動的に入力されたセルの数式は、セルにカーソルを当てるとグレー文字で表示されます（スピル機能を使わず、直接入力した場合は黒で表示される）。このままでは編集できないので、必要に応じて範囲をコピーし、値でペーストし、上書きしましょう。数値などはそのままでも参照対象として使うことができます。
結果を並び変える
特定条件を満たしたデータの抽出後によく行われるのが「並べ替え」です。
「SORT」関数を使って並べ替えてみましょう。
書式は次のようになります。
=SORT(配列範囲,並べ替え基準列,並べ替え順)
さきほどの例の続きで数式を作ると、
=SORT(E2#,3,1)
となります。
（）の中の要素を説明していきましょう。
E2#は配列範囲です。並べ替えを行う範囲全体を指定します。この場合、先にFILTERで出力した範囲を使用しているので、「E2#」と表記しています。
3は並べ替え基準列です。範囲の中で左から何番目の列を基準にするかを指定します。この場合、C列（G列）の年代を指定したいので「3」と表記しています。
1は並べ替え順です。1が昇順 -1が降順をあらわします。
つまり、FILTER関数で抜き出したデータに対し、年代別で並べ替えを行うように指定しているわけです。
さらに、FILTER関数とSORT関数を組み合わせて抽出と並べ替えを同時に行うことも可能です。
その場合の数式は次のようになります。
=SORT(FILTER(A2:C120,B2:B120=”女性”,””),3,1)
SORT関数の配列選択部分にFILTER関数を利用していることがわかります。
▲M2セルに=SORT(FILTER(A2:C120,B2:B120=”女性”,””),3,1) を入力し、抽出と並べ替えを同時に行う
キーワードを抽出してユニークリストを作る
では、より分析的な抽出を行ってみましょう。
「女性」だけのデータを抽出しました。さらにここから「キーワード」の抜き出しを行い、ユニークリストを作成してみます。
使用する関数は「UNIQUE」です。UNIQUE関数は指定範囲から重複のない値を返します。
書式は以下のとおり。
=UNIQUE(配列範囲,列の比較,回数)
まず範囲指定だけを行ってみましょう。キーワードを抜き出したI列を対象にどのようなワードが抽出されているかを調べます。
=UNIQUE(I2:I71)
I列でデータの入っている最初から最後の行までを指定します。
すると、キーワードがすべて抽出されます。
▲M2セルに「=UNIQUE(I2:I71)」を入力し、ユニークリストを作成する
ただ、これだけでは一度しか出てきていない例外的なキーワードも拾ってしまいます。そこで、一度しか出現しないキーワードを抽出してみます。指定範囲のあとにTRUEを付け加えます。
=UNIQUE(I2:I71,,TRUE)
I2:I71は配列範囲を指定しています。
,,は列の比較を指定する部分で、ユニークな「列」を返したい場合には「TRUE」を、ユニークな行を返したい場合には「FALSE」を指定します。省略すると、「FALSE」が指定されます。
最後のTRUEは、回数指定です。1回だけ出現する値を返す場合は「TRUE」、重複しないすべての値を返す場合は「FALSE」を指定します。この数式を入力した結果、一度だけあらわれたあまり重要でないキーワードが抽出されました。
▲M21セルに「=UNIQUE(I2:I71,,TRUE) 」を入力し、1回だけ出現するキーワードを抽出する
なお、範囲指定に列の指定をすれば、UNIQUE関数で列方向のデータを抽出することもできます。
=UNIQUE(E2:M2,TRUE,)
といった指定です。
今回は比較的新しい機能であるスピル機能を使い、これまではコマンド機能を使って調べていたことがらを関数を利用して自動的に行えるようにしました。「フィルター機能」には「FILTER」関数、「並べ替え機能」には「SORT」関数を使います。最後に、「UNIQUE」関数を使い、データから重複を取り除き、ユニークリストを作成する方法もご紹介しました。Excelをより効率的に利用できるようになれば幸いです。
インターネットを最大限楽しむには「ビッグローブ光」の光回線がオススメ！
「インターネット回線が遅い」
「インターネット料金が高い」
などのお悩みはありませんか？
安定した速度とリーズナブルな光回線なら、
IPv6接続(IPoE方式)*1で最大10Gbps*2の「ビッグローブ光」がオススメです。
「ビッグローブ光」なら他社の光回線（フレッツ光・コラボ光）をお使いでもお乗り換えはかんたんです。工事費実質無料、おトクな特典もご用意しています。
*1 IPv6に対応していないサービス/サイトはIPv4接続となります。
*2 最大通信速度は光回線タイプによって異なります。最大通信速度はお客さまのご利用機器、宅内配線、回線の混雑状況などにより低下します。
光回線についてもっと詳しく知りたいという方は、
以下の記事も併せてご覧ください。
