Excelの動的配列関数(FILTER・UNIQUE・SORT)が仕事を変える!実践的な5つの活用術

動的配列関数は、Excelの中でも最も便利で強力な機能のひとつです。従来のように数百行にわたって複雑な数式をコピー&ペーストする必要はなく、1つの数式を入力するだけで、必要な分だけ結果が自動的に隣接セルへ「スピル(あふれ出し)」されます。元データが変われば結果もリアルタイムで更新されるため、レポートや集計作業の効率が大幅に向上し、手作業によるミスも防げます。

この記事では、FILTER・UNIQUE・SORTなどの動的配列関数があなたのExcel作業をどのように変えるのか、具体的な5つの活用方法を解説します。

動的配列とスピルの基本

動的配列: セルに数式を入力すると、Excelが自動的に結果を隣接セルへスピルします。結果の行数が増減すれば、スピル範囲も自動的に拡大・縮小されます。スピル範囲は以下の特徴で見分けられます。

• 数式本体は左上のセルにのみ存在する。
• その他のセルには薄い枠線が表示され、クリックすると数式がグレー表示で確認できる。
• スピル範囲全体を「A2#」のようにハッシュ記号(#)を使って参照できる。

なお、動的配列はMicrosoft 365版ExcelおよびExcel 2021以降で利用できます。

1. UNIQUE関数で重複のないリストと集計サマリーを作る

動的配列が登場する以前は、重複の削除には「重複の削除」機能や複雑な数式が必要でした。UNIQUE関数なら、スピル対応の一意リストを瞬時に作成でき、ドロップダウンリスト・入力規則・ピボットテーブル不要のダッシュボードに最適です。

ユニークな商品リストを作成:

• 任意のセルを選択し、次の数式を入力します。

=UNIQUE(D2:D61)

この数式は、商品のユニークなリストをスピル表示します。

組み合わせの一意化:
地域と営業担当者のユニークな組み合わせを取得します。

=UNIQUE(B2:C61)

2列にわたるスピルとして、すべての一意の組み合わせが返されます。

ユニークな注文数をカウント:

• UNIQUE関数とCOUNTA関数を組み合わせてサマリーを作ります。

=COUNTA(UNIQUE(A2:A61))

この数式は一意な注文の総数を計算します。データセットに行を追加しても、リストは自動的に更新されます。

1回しか出現しない値を抽出:
一度だけ登場する値を見つけます。

=UNIQUE(A2:A61,FALSE,TRUE)

第3引数(TRUE)を指定すると、繰り返されていない値のみが返されます。

UNIQUEを入力規則(ドロップダウン)に使う:

1. ドロップダウンを設置したいセルを選択します。
2. データタブ >> データの入力規則を選択します。
3. 入力値の種類をリストに設定します。
4. 元の値に、スピルされた一意リストへの参照(例:=I15#)を入力します。

これで、ドロップダウンには常に最新のデータに基づいたユニークな地域が表示されるようになります。
UNIQUEを使ったグループ別サマリー:
UNIQUEとSUMIFを組み合わせれば、動的なグループ集計が作れます。
各地域の売上合計を計算します。

=SUMIF(B2:B61, I15#, G2:G61)

この数式は、スピルされた地域リスト全体(I15#)を参照しています。SUMIFが地域ごとの売上合計を返します。売上データに行を追加・変更しても、地域リストと合計値は自動的に調整されます。

地域を選び直せば、合計売上も選択内容に応じて自動的に更新されます。

2. FILTER関数でデータを自動抽出し、動的レポートを作る

従来のフィルタリングは、手動操作か複雑な数式が必要でした。FILTER関数は動的配列の中でも最も強力なツールのひとつで、条件を満たす行だけを抽出し、テーブルのような範囲へスピルします。この関数を使えば、即座に更新されるレポートを作成できます。
地域で売上を絞り込む:

• FILTER関数の動作を確認するため、地域選択用のドロップダウンを用意します。

=FILTER(A2:G61, B2:B61="East")

• さらに動的にするため、ドロップダウンの条件セルを参照させます。

=FILTER(A2:G61, B2:B61=I4)

この数式は、地域が「East」の行をすべてスピル表示します。データの追加・削除に応じてレポートが自動的に伸縮するミニレポートです。I4を「North」に変えれば、レポートは自動更新されます。VBAも手動更新も不要です。

静的なデータの複製が不要になり、レポートは常にソースデータを反映した状態を保てます。
複数条件での抽出:
東(East)地域かつ金額が$1,000超の行を抽出します。

=FILTER(A2:G61, (B2:B61="East")*(G2:G61>1000), "該当なし")

アスタリスク(*)はAND条件として機能し、プラス(+)を使えばOR条件になります。
ドロップダウンから条件を選べるインタラクティブなダッシュボードも簡単に構築できます。

3. SORT()とSORTBY()で自動ソートされるリストを作る

以前のソートは、データのコピーかテーブル機能に頼るものでした。SORT関数なら、動的かつスピル対応のソート済みビューを作れます。データセットの追加・削除・更新があるたび、自動的に並べ替えが行われます。
自動ソートされる売上ランキング:

=SORT(A2:G61, 7, -1)

この数式は、データ範囲全体を7列目(金額)の降順(-1)で並べ替えます。元データはそのまま維持されます。新しいトップセールスの行を追加すれば、自動的に正しい順位へ反映されます。

複数のソートキーを設定:
まず営業担当者、次に金額で並べ替えます。

=SORT(A2:G61, {3,7}, {1,-1})

波括弧で配列を指定しています。3列目を昇順、続いて7列目を降順にソートします。

別の基準で並べ替える:
SORTBY関数を使うと、別の範囲の値を基準にある範囲をソートできます。
全列を保持したまま、営業担当者名で範囲全体を並べ替えます。

=SORTBY(A2:G61, C2:C61, 1)

4. FILTERとUNIQUEを組み合わせた絞り込み型ユニークサマリー

より高度なサマリーでは、関数を組み合わせて「先にフィルタリングし、その後に一意化」することで、整った自動更新リストをスピルできます。自己メンテナンス型のレポートを、関数の組み合わせだけで実現しましょう。

• 任意のセルを選択し、次の数式を入力します。

=UNIQUE(FILTER(D2:D61, B2:B61="North"))

北(North)地域の商品を抽出し、その中から一意のものだけをスピルします。

• さらにSORT関数を追加して、サマリーを並べ替えます。

=SORT(UNIQUE(FILTER(D2:D61, B2:B61="North")))

これで、ソート済みの一意な商品リストがスピル表示されます。

{=INDEX(...)} のような扱いにくい配列数式を置き換えられるのがポイントです。データや条件を変更すれば、地域別の商品在庫リストなどのレポートもシームレスに更新されます。

5. 条件駆動型の動的サマリーページ(FILTER+UNIQUE+SORTの統合)

最後に、これらの関数を組み合わせて、少数の条件セルから自動更新されるミニサマリー/レポートページを作りましょう。
地域単位のダッシュボードを構築します。地域ドロップダウン(UNIQUEで生成)、フィルター済みの地域別明細(FILTER)、地域別の人気商品ランキング(FILTER+SORT)の3要素です。

ステップ1:UNIQUEで地域ドロップダウンを作成

ユニークな地域リストを作成し、それをもとにドロップダウンを構築します。

ステップ2:地域別の売上明細を表示

=FILTER(SalesData!A2:G61, SalesData!B2:B61=B4, "この地域の売上はありません")

この数式は、地域に基づいて売上データをフィルタリングします。ドロップダウンで地域を切り替えると、売上テーブルが自動的に更新されます。

ステップ3:選択した地域の人気商品を表示

選択した地域で最も売れている商品を特定します。

• 「商品」「合計売上」という見出しを持つ小さなテーブルを作成します。
• L4セルに、選択地域で販売されているユニークな商品を取得する数式を入力します。

=UNIQUE(FILTER(SalesData!D2:D61, SalesData!B2:B61=B4))

この数式は、その地域の商品リストをスピル表示します。

• M4セルに、その地域における商品ごとの合計売上を計算する数式を入力します。

=SUMIFS(SalesData!G2:G61, SalesData!B2:B61, B4, SalesData!D2:D61, L4#)

L4#内の各商品に対応する合計売上のスピルリストが返されます。

• 売上の降順で表示するには、2つのスピル列をまとめてソートします。

=SORT(CHOOSE({1,2}, L4#, M4#), 2, -1)

ここで、CHOOSE({1,2}, L4#, M4#)は2列の配列(商品と合計売上)を構築します。2は「2列目(合計売上)でソート」、-1は降順を意味します。

[選択地域]の人気商品レポートが完成:

• B4の地域ドロップダウンを変更すると、すべてのサマリーが更新されます。
• 売上データに新しい行を追加すれば、レポートにも自動的に反映されます。
• 数式のコピーや手動ソート、ピボットテーブルの更新は一切不要です。

まとめ

本チュートリアルでは、FILTER・UNIQUE・SORTという動的配列関数が仕事のやり方を変える5つの方法を紹介しました。動的配列関数は、スプレッドシートの保守にかかる面倒な作業を排除してくれます。数式のコピー壊れた参照の修復といった作業から解放され、分析そのものに集中できるのです。レポートは自分で更新されダッシュボードも自動的に最新化されます。これらの関数を使い始めれば、集計やダッシュボードに理想的であることがわかるはずです。手動更新も複雑な配列数式も作業用の補助列も不要で、即座に更新されるレポートを構築できます。

解答付きの無料の高度なExcel演習問題もぜひチェックしてみてください!