Excel動的配列FILTER・SORT・UNIQUEの使い方と実務活用

動的配列とは何か

動的配列は一つの数式が複数の結果を返す仕組みである。 結果は隣接するセルに自動で展開される。 この自動展開はスピルと呼ばれる。 従来は複数セルに数式を複写する必要があった。 動的配列では起点の一セルに書くだけでよい。 参照先の行数が増減しても出力範囲が追従する。 集計表や抽出表の保守負担が大幅に減る。 Microsoft公式解説（https://support.microsoft.com/ja-jp/office/動的配列数式とスピルされた配列動作-205c6b06-03ba-4151-89a1-87a7eb36e531）でも中核機能として説明がある。

対象はMicrosoft 365とExcel 2021以降である。 ウェブ版Excelでも同一の動作になる。 旧版Excelではスピルが再計算されない。 互換性を保つ運用設計が欠かせない。

従来の配列数式との違い

旧来の配列数式はCtrlとShiftとEnterの同時押しが必要であった。 出力範囲を事前に選択する手順も求められた。 範囲変更のたびに数式の入れ直しが発生した。 動的配列ではその手順が不要である。 通常のEnter確定でスピルが実行される。 数式は起点セルだけに保持される。 他セルには数式が複写されない。 この差が監査性と保守性を高める。

旧式のCSE配列は現在も動作する。 新規作成では動的配列への置換が推奨される。 特に抽出と並べ替えは置換効果が大きい。

FILTER関数の使い方

構文は=FILTER（配列, 含む, [空の場合]）である。 第1引数に戻したい表全体を指定する。 第2引数に抽出条件を指定する。 第3引数は該当なし時の表示を指定する。 省略時は#CALC!が出る。

例として社員表から営業部だけを抜く。 =FILTER（A2:D100, C2:C100=“営業部”, “該当なし”）と書く。 条件範囲と戻り範囲の行数は一致させる。 列全体参照は計算が重くなるため避ける。

複数条件のANDは掛け算で表す。 =FILTER（A2:D100, (C2:C100=“営業部”)*(E2:E100>=50)）となる。 OR条件は足し算で表す。 =FILTER（A2:D100, (C2:C100=“営業部”)+(C2:C100=“企画部”)）となる。 部分一致はSEARCH関数と組み合わせる。 =FILTER（A2:A100, ISNUMBER（SEARCH（“東京”, B2:B100）)）が典型である。

現場では日付抽出の需要が多い。 =FILTER（A2:D500, MONTH（B2:B500)=4）で4月分が取れる。 数値範囲の抽出も同様である。 在庫表から在庫薄だけを抜く用途に向く。

SORT関数の使い方

構文は=SORT（配列, [並べ替え指標], [並べ替え順序], [列単位]）である。 第2引数は何列目で並べるかを示す。 第3引数は1が昇順、-1が降順である。 第4引数はFALSEが行方向、TRUEが列方向である。 Microsoft公式解説（https://support.microsoft.com/ja-jp/office/sort-関数-22f63bd0-ccc8-492f-953d-c20e8e44b86c）に詳細な仕様がある。

売上表を金額降順に並べる例を示す。 =SORT（A2:D100, 4, -1）で4列目基準になる。 複数キー整列はSORTBY関数が適する。 =SORTBY（A2:D100, C2:C100, 1, D2:D100, -1）と書く。 デプロイ昇順かつ金額降順の整列が可能である。

日付並べ替えにも安定して使える。 文字列と数値の混在列では誤整列が出る。 事前にデータ型統一が必要である。 空白行を含むと0扱いで先頭に集まる。 FILTERで空白除去後の整列が安全である。

UNIQUE関数の使い方

構文は=UNIQUE（配列, [列単位], [回数指定]）である。 第2引数はFALSEが行比較、TRUEが列比較である。 第3引数はFALSEが重複除去、TRUEが一回だけの値抽出である。 Microsoft公式解説（https://support.microsoft.com/ja-jp/office/unique-関数-c5ab87fd-30a3-4ce9-9d1a-40204fb85e98）に動作条件の記載がある。

顧客名簿の重複除去が代表例である。 =UNIQUE（B2:B1000）で一意な顧客一覧が出る。 複数列の組合せで一意化もできる。 =UNIQUE（A2:C1000）で3列一致での判定になる。 出現1回だけの異常検出にも使える。 =UNIQUE（B2:B1000, FALSE, TRUE）で単発値を抜ける。

並べ替え付き一覧はSORTと合成する。 =SORT（UNIQUE（B2:B1000)）で昇順名簿になる。 件数把握はROWS関数と組む。 =ROWS（UNIQUE（B2:B1000)）で種類数が分かる。 入力規則の選択肢原稿としても有用である。

組み合わせによる実践手順

単体利用より合成利用の価値が高い。 抽出と整列と重複除去を一本化できる。 保守対象の数式が一つに集約される。 中間作業列を作らずに済む。

手順1はFILTERで対象行を絞る。 手順2はSORTで表示順を整える。 手順3はUNIQUEで重複を除く。 例：=SORT（UNIQUE（FILTER（B2:B500, D2:D500=“完了”))）である。 完了案件の担当者を重複なく昇順表示できる。

LET関数併用で可読性が上がる。 =LET（対象, FILTER（A2:D500, C2:C500=“営業部”), SORT（対象, 4, -1)）と書く。 同じ範囲参照の反復を避けられる。 計算速度の改善にもつながる。 XLOOKUP併用で単価引当てなども可能である。

実務での用途は幅広い。 ・月次売上からの部門別明細抽出 ・不良履歴からの一意な型番一覧作成 ・勤怠表からの遅刻者抽出と日付順整列 ・問い合わせ記録からの重複件名整理 いずれも元表更新だけで出力が追従する。

不具合表示と対処法

代表は#SPILL!である。 出力先に値や罫線結合があると発生する。 出力範囲の確保で解消する。 書式付き表との重なりも原因になる。 構造化参照との併用は事前検証が要る。

#CALC!はFILTERの該当なしで出る。 第3引数の指定で回避する。 空文字返却より“該当なし”表示が分かりやすい。 #VALUE!は型不一致で出る。 日付列への文字列混入が典型原因である。 #REF!は参照削除で出る。 元表の列削除時は数式修正が必要である。

列全体参照は避けるべきである。 A:A指定は百万行超の評価を招く。 ファイル肥大と再計算遅延の原因になる。 使用範囲限定が基本である。 出力結果の固定化には値貼り付けを使う。 旧版Excelへの配布時は必須作業である。

対応環境と運用上の注意

動的配列はMicrosoft 365で完全動作する。 永続版ではExcel 2021以降が対応する。 Excel 2019以前では新規計算ができない。 開くだけなら値は表示される場合がある。 再計算で値が崩れる危険がある。

共有運用では版の統一が望ましい。 取引先配布用はPDF化か値貼り付けにする。 マクロ記録との相性にも注意が要る。 スピル範囲はResizeで変化する。 VBAで行数固定の書式設定は破綻しやすい。 出力先に条件付き書式を広めに掛ける運用が安定する。

編集部の見解

選択では再計算速度と保守性を重視すべきだと評価する。FILTER単体よりSORTやUNIQUEとの合成可否が実務効率を左右すると見る。複雑な条件分岐が必要な場合はXLOOKUPやLETとの併用可否も判断材料になると言えそうだ。

現場での落とし穴は参照範囲の指定方法にあると見る。列全体参照は計算負荷を増大させ保存時不具合を招くと言えそうだ。共有環境では旧版Excelとの互換性崩壊が頻発すると評価する。値貼り付けや範囲限定で回避可能だと見る。

今後はPython連携や生成AI支援による数式生成が主流になると見る。動的配列は自動化処理の基盤として位置付けを強めると評価する。手作業集計は減少し設計と検証能力が問われると言えそうだ。

参考

• 動的配列数式とスピルされた配列動作
• FILTER 関数
• SORT 関数
• UNIQUE 関数