トピック

Excel 2021では、「並び替えミスで表を破壊」が防げる新関数が！フィルター/ソート/抽出の新関数はこう使おう！

Excelの基本的だが非常に重要な処理をFILTER/SORT/SORTBY/UNIQUE関数で

  • 今井 孝

2021年11月8日 06:55

Office 2021で利用できる「FILTER関数」「SORT/SORTBY関数」「UNIQUE関数」が便利

　2021年10月5日に発売された「Office 2021」。特に多くの機能追加が行われたのがExcelです。今回は、Excelに追加された新関数「FILTER関数」「SORT/SORTBY関数」「UNIQUE関数」の3つをまとめて紹介します。

　これらの新関数は、いままではメニューやショートカットキー、検索ボックス（Microsoft Search ボックス）から操作していたデータの絞り込み、並べ替えといった基本的な処理を、関数でも使えるようにしたものです。

　最大のメリットは「元データを改変しないこと」。これらの関数は、データを参照して結果を表示する仕組みで、何をしても元データには反映しません。従来のExcelで同様の処理を行なった場合、ちょっとしたミスで元データが壊れる事故を起こす可能性がありました。これらの新関数を使えばそんな心配は無用です。

　例えば、「追加した列にフィルターを設定しないまま並べ替えて、データがずれてしまった」というミスをしてしまった方も多いと思いますが、これらの新関数では、そうしたことはおきません。

　また、ひとつのセルに数式を入力するだけで隣接セルに値を配置できる「スピル」に対応しているので、数万件以上のデータの抽出・並べ替えも一発です。

　表がメチャクチャになってしまう事態を避けられますし、構文も簡単、使わない手はありません。

　それぞれの動作は、FILTER関数が「フィルターをかけてデータを絞り込む」、SORT/SORTBY関数が「指定列でデータをソートする（並べ替える）」、UNIQUE関数が「データからユニーク（一意）なデータを取得する」というもの。

　だいたいの動作は想像がつくと思いますが、その使い方と威力を、順にみていきましょう。

購入特典『できる Office 2021 新機能ガイド』。本稿でも紹介する新機能のポイントを、合計31ページで見やすく紹介している。

　なお、今「Office 2021」を購入すると、特典として小冊子『できる Office 2021 新機能ガイド』がもらえます（ただし、配布冊子がなくなり次第終了。詳細な配布方法は販売店によって異なります）。LET関数をはじめとする便利になった新機能を使いこなし、より効率よく作業する具体的な手順を詳しく紹介しているので、要チェックです。

目次

▼複数条件での絞り込みもできるFILTER関数
　・構文は「=FILTER(［範囲］,［条件］,［一致しない場合の値］)」
　・［条件］の指定方法
　・複数の条件を指定して絞り込むことも可能
▼データを並べ替えられるSORT/SORTBY関数
　・SORT関数とSORTBY関数の構文
　・SORT関数とSORTBY関数でデータを並べ替える
　・複数条件で並べ替えるならSORTBY関数
　・横方向の表を列単位で並べ替えるならSORT関数
　・FILTER関数と組み合わせることで真価を発揮
▼［重複の削除］ボタンと同じ動作を関数で行うUNIQUE関数
　・構文は「=UNIQUE(［範囲］,［検索方向,［回数］)」
　・複数列における一意の値を抽出したり、SORT関数と組み合わせることも
　・横方向の表から一意の値を抽出するときは［検索方向］を「TRUE」に
▼Excelの基本的だが非常に重要な処理を関数で行えるように


※本稿の内容はプレビュー版である「Office LTSC Professional Plus 2021 Preview」で動作確認しています。


複数条件での絞り込みもできるFILTER関数

　まずはFILTER関数から紹介していきましょう。

　FILTER関数は「フィルターボタン」のような絞り込み処理が行えます。スピルに対応しており、入力する数式はひとつのみです。構文を見てみましょう。


FILTER関数の構文は「=FILTER(［範囲］,［条件］,［一致しない場合の値］)」

対象のセル範囲の［範囲］と絞り込みの［条件］を指定する。［一致しない場合の値］は［条件］に合致するデータが存在しない場合に表示する値を指定する

　［範囲］は絞り込む対象のセル範囲を指定します。通常は表全体でしょう。列見出しは含めずに指定します。連続したセル範囲であれば表の一部を指定しても構いません。［条件］には、文字列の比較や数値の大小など、データを絞り込む条件を指定します。

　3つめの引数［一致しない場合の値］には、［条件］に合致するデータが存在しない場合に表示する値、例えば「該当データなし」などと指定可能です。省略時に該当するデータが存在しない場合は、#CALC!エラーが表示されます。


［条件］の指定方法

　［条件］に指定する条件式は、絞り込み対象のセル範囲と比較する形式で「D2:D11="第一営業部"」のように指定します。まずは単純にひとつの条件で絞り込んでみます。セルA15に「=FILTER(A2:E11,D2:D11="第一営業部")」と入力します。

セルA2:E11に入力されたデータを絞り込む。セルA15に「=FILTER(A2:E11,D2:D11="第一営業部")」と入力する
「担当部署」が「第一営業部」のデータが絞り込まれた。スピルに対応しているため、ほかのセルに数式を入力する必要はない

　ここで指定した「=」（等しい）以外に「>=」（以上）、「<=」（以下）、「>」（より大きい）、「<」（より小さい）、「<>」（等しくない）の比較演算子も利用できます。数値の比較なら「E2:E11>=10000000」といった具合です。


複数の条件を指定して絞り込むことも可能

　AND/OR条件で複数の条件を指定する絞り込みも可能です。複数の条件式を「*」でつなげるとAND条件、「+」でつなげるとOR条件になります。もうフィルターボタンの形状で絞り込みの有無を判断する必要はありません。AND条件・OR条件を使う際には各条件を半角括弧「()」で括る必要があることに注意してください。

「売上金額が9,000,000円以上、かつ担当部署が第一営業部」のAND条件を指定する。セルA15に「=FILTER(A2:E11,(E2:E11>=9000000)*(D2:D11="第一営業部"))」と入力した。「*」で条件をつなげるとAND条件となる
「担当部署が第一営業部、もしくは第二営業部」のOR条件を指定する。セルA15に「=FILTER(A2:E11,(D2:D11="第一営業部")+(D2:D11="第二営業部"))」と入力した。「+」で条件をつなげるとOR条件となる


データを並べ替えられるSORT/SORTBY関数

　SORT関数はひとつの列、または行を基準に、SORTBY関数は複数の列を基準に並べ替えを行ないます。それぞれ構文を確認しておきましょう。


SORT関数とSORTBY関数の構文

SORT関数の構文。対象のセル範囲を［範囲］に指定する。［基準］には、基準にする列、または行のインデックス番号（先頭は1）を指定する。［順序］は並べ替え順を、1：昇順、-1：降順で指定。［データの並び］は元データの方向により、TRUE：行方向（右方向）、FALSE：列方向（下方向）で指定する。省略した場合はFALSEとなる
SORTBY関数の構文。対象のセル範囲を［範囲］に、基準にするセル範囲を［基準］に指定する。［範囲］と［基準］の組み合わせは126組まで指定可能。［順序］は並べ替え順を、1：昇順、-1：降順で指定する

　先頭行に見出しがあり、列方向（下方向）にデータが並ぶ一般的な表では、ひとつの条件でも並べ替え可能なSORTBY関数のほうが使い勝手がよいと思います。ただ、左端の列に見出しのある横方向の表を列単位で並べ替える場合は、SORT関数の出番です。それぞれ利用シーンを見てみましょう。


SORT関数とSORTBY関数でデータを並べ替える

SORT関数の利用例。セルA2:E11の表を年度で降順に並べ替える。セルA15に「=SORT(A2:E11,2,-1,FALSE)」と入力した
SORTBY関数でSORT関数と同じ結果を取得する例。セルA2:E11の表を年度（降順）に並べ替える。セルA15に「=SORTBY(A2:E11,B2:B11,-1)」と入力した

　上の例はSORT関数とSORTBY関数を利用して同じ結果を表示させました。「=SORT(A2:E11,2,-1,FALSE)」は最後の「,FALSE」を省略して「「=SORT(A2:E11,2,-1)」」と記述しても構いません。インデックス番号に慣れていれば、SORT関数のほうが短く簡単と感じるかもしれません。


複数条件で並べ替えるならSORTBY関数

　複数条件を指定したい場合は、SORTBY関数を使います。「担当部署（昇順）、売上金額（降順）」で並べ替えてみます。

セルA2:E11の表を担当部署（昇順）、売上金額（降順）に並べ替える。セルA15に「=SORTBY(A2:E11,D2:D11,1,E2:E11,-1)」と入力した


横方向の表を列単位で並べ替えるならSORT関数

　前述のように、横方向の表を列単位で並べ替える場合は、SORT関数を使います。以下は、単価（降順）で列を並べ替えています。

横方向の表で、セルB1～K4の表を単価（降順）に並べ替える。セルB6に「=SORT(B1:K4,4,-1,TRUE)」と入力した


FILTER関数と組み合わせることで真価を発揮

　先ほど紹介したFILTER関数との組み合わせも可能です。特定の条件で絞り込んだデータを、指定列で並べ替えるといった日常業務でよくある処理も簡単です。

「担当部署」を「第一営業部」で絞り込み、売上金額（降順）で並べ替える。セルA15に「=SORT(FILTER(A2:E11,D2:D11="第一営業部"),5,-1)」と入力した

　この例ではデータが10行しかないので、そこまで恩恵を感じないかもしれませんが、100行、1,000行のデータを扱う際には心強い味方となるでしょう。


［重複の削除］ボタンと同じ動作を関数で行うUNIQUE関数

　セル範囲からユニーク（一意）な値を取り出す場合は、UNIQUE関数で一瞬です。これまでのように、値をコピー＆ペーストしてから［重複の削除］ボタンをクリックする必要はありません。構文を見てみましょう。


UNIQUE関数の構文は「=UNIQUE(［範囲］,［検索方向,［回数］)」

ユニーク（一意）な値抽出するセル範囲を［範囲］に指定する。［検索方向］はチェックする方向。TRUE：行方向（右方向）、FALSE：列方向（下方向）で指定する。省略した場合はFALSEとなる。［回数］は、重複データの扱い方を指定する。TRUE：重複していない値のみ（1回のみ出現する値）を抽出、FALSE：重複値を除く。省略した場合はFALSEとなる

　UNIQUE関数を使う際は、列方向（下方向）へ向かって一意な値を抽出することがほとんどでしょう。その場合、［検索方向］と［回数］は省略可能なので、UNIQUE関数の引数はセル範囲のみでよいことになります。


複数列における一意の値を抽出したり、SORT関数と組み合わせることも

　複数列における一意の値も抽出可能で、指定するセル範囲を拡張するだけです。さらに抽出した一意の値の並べ替えたい場合は、SORT関数と組み合わせます。

セルD2:D11から一意の値を抽出する。セルG2には「=UNIQUE(D2:D11)」と入力した
セルC2:D11から一意の値を抽出すると、各部署が担当している案件がわかる。セルG2には「=UNIQUE(C2:D11)」と入力した
抽出した一意の値を案件名で並べ替える。セルG2には「=SORT(UNIQUE(C2:D11),1,-1)」と入力した


横方向の表から一意の値を抽出するときは［検索方向］を「TRUE」に

　なお、横方向の表から一意の値を抽出したい場合は、［検索方向］に「TRUE」を指定します。［回数］を「TRUE」とすると、重複データは除かれて、セル範囲で1回しか出現しない値が抽出されます。

横方向の表で、セルB2:K2から一意の値を抽出する。セルB6には「=UNIQUE(B3:K3,TRUE)」と入力した


Excelの基本的だが非常に重要な処理を関数で行えるように

　ここで紹介した「FILTER関数」「SORT/SORTBY関数」「UNIQUE関数」は、XLOOKUP関数ほど注目されないかもしれません。しかし、データの絞り込み、並べ替えなどの処理はデータ分析に欠かせないものです。

　しかも、元データに触れずにデータ操作を行えるうえ、スピルによりすばやい処理が可能となっています。無駄な心配と事故を防げるようになったExcelを「Office 2021」でぜひ試してみてください。