Excelの動的配列関数がワークフローを劇的に変えた——知っておきたい4つの関数

公開日:2026年4月30日 午前10:30 EDT

Yasirは機械工学エンジニアであり、MUOでWindows、生産性、セキュリティ、インターネットに関する技術記事を執筆しています。自律システムへの強い関心から、ハードウェアとソフトウェアの両方に常に手を入れて試行錯誤する日々を送っています。

技術ライティングのキャリアは工学部3年生の頃に始まり、Android Policeを経てMUOに参画しました。Windowsのトラブルシューティング、生産性ツールの探求、セキュリティリスクの平易な解説など、技術をより身近なものにすることを信条としています。読者と同じ問題に実際にぶつかるために、ツールを自ら使い込むことを良しとしています。

執筆やエンジニアリングの合間には、「Impractical Jokers」を見て、何度も見たことのあるドッキリに本気で笑っているそうです。

はじめに:動的配列関数が変えた「数式の組み立て方」

私が知っているほとんどのExcelユーザーは、私と同じように数式を学んできました。関数を一つずつ覚え、既存の知識の上に積み重ねていく方式です。動的配列関数はそれらのスキルを置き換えるものではありませんが、多くの回避策をまるごと不要にしてくれます。私はTAKEとDROPを使った自動更新型のトップ5リストをしばらく運用していますが、今回紹介する4つの関数でも同じ転換が起きました。それぞれ、反射的に行っていた複数ステップのルーチンを、たった1つの数式へと圧縮してくれたのです。

関連記事

FILTERはヘルパー列と配列数式の「儀式」を丸ごと置き換えた

かつて複数の関数に分担させていた仕事を、1つの数式が完結

旧来のExcelで条件に一致する行を抽出するには、INDEX、MATCH、SMALL、IFERRORをネストした数式を組み上げ、Ctrl + Shift + Enterで入力する必要がありました。動作こそしましたが、後のメンテナンスが大きな負担でした。もう一つの選択肢はオートフィルターを適用し、表示中の行をコピーして、静的な値として別の場所へ貼り付ける方法です。これもソースデータが変更されるまでは有効ですが、一度データが変わればやり直しが必須でした。

FILTER関数なら、同じ仕事を1行で処理できます。地域、製品カテゴリ、営業担当者を含む32行の売上スプレッドシートから、「西地域のElectronics販売」をすべて抽出する数式は次のとおりです。

=FILTER(A2:G33, (B2:B33="West")*(C2:C33="Electronics"))

第1引数は返したい範囲、第2引数は条件です。2つの条件をつなぐ掛け算(*)はANDとして機能し、両方が真である行のみが返されます。アスタリスクをプラス記号(+)に変えればOR条件になり、結果は自動的にスピル(隣接セルへの展開)します。ソースに新しい行を追加すれば、Enterを押した瞬間にスピル結果も更新されます。

また、FILTERをIFERRORで囲み第3引数を指定すれば、空の結果にも优雅に対応できます。=FILTER(範囲, 条件, "該当なし")のように書けば、該当行がゼロの場合に#CALC!エラーが表示されるのを防げます。

UNIQUEは3段階の重複削除ルーチンを1つのセルに凝縮した

「重複の削除」も便利だが、一度も自動更新されたことはない

データタブの「重複の削除」は、一回限りのクリーンアップには十分な機能です。ただし、生成されるのは静的なリストという点が難点です。列を別の場所にコピーし、ダイアログを実行し、結果を並べ替える——その一連の作業は、誰かが行を追加するたびに全部やり直しになります。私自身、数えるのも嫌なくらい繰り返してきました。

UNIQUEはその全工程をスキップします。売上スプレッドシートの営業担当者列を指定するだけで完了です。

=UNIQUE(D2:D33)

結果として、John Smith、Sarah Johnson、Mike Wilson、Lisa Brown、David Chen、Emma Davis、Tom Rodriguez、Amy Fosterの8名がスピル表示されます。ダイアログボックス操作ゼロで8名分のユニークなリストが得られるわけです。=SORT(UNIQUE(D2:D33))と囲めば、同じリストがアルファベット順で返されます。出力はソースデータに接続されたままなので、新しい名前を追加するとスピルリストも自動的に拡張します。

この仕組みは、データの入力規則によるドロップダウンリストのクリーンなソースとしても優秀です。D35#のようにハッシュ(#)付きでスピル範囲を参照すれば、ソースデータに新しい名前が追加されるたびに、ドロップダウンの選択肢も自動的に育っていきます。

SORTBYが「コピペして並べ替える」作業に終止符を打った

ソースデータに一切触れずに、見た目だけを並べ替える

Excelでの並べ替えには、常に小さなリスクが伴っていました。ソースを直接並べ替えると、固定行を参照している数式が壊れる恐れがあるため、私の定番ワークアラウンドは「安全な場所にデータの塊をコピーして、そのコピーを並べ替える」ことでした。SORT関数も役立ちましたが、並べ替え基準の列をすべて表示してよい場合に限られます。

SORTBYはその制約を取り払います。ある範囲を、別の範囲の値を基準に並べ替えられるうえ、基準となる範囲は出力に現れる必要がありません。データセットで営業担当者を売上高順にランク付けするために使った数式がこちらです。

=SORTBY(D2:D33, G2:G33, -1)

第1引数は返したい対象(営業担当者)、第2引数は並べ替えの基準(売上高)、-1は降順の指定です。名前は売上額の高い順から低い順に返され、売上額の列は明示的に求めない限り出力に現れません。

FILTERとの相性も抜群です。FILTER(D2:G33, B2:B33="North")をSORTBYで囲めば、北地域のレコードだけを売上の高い順に、一発で抽出・並べ替えできます。ソースデータは無傷のままです。

さらにSORTBYは複数の並べ替えレベルにも対応しています。範囲と順序のペアを追加すれば、「まず地域ごとに、さらに各地域内で売上高順」という多段階の並べ替えも1つの数式で実現できます。

SEQUENCEはフィルハンドルとROW関数の小技を引退させた

セルをひとつもドラッグせずに連続データを生成

番号付きの連続データを作るには、フィルハンドルを根気の限界までドラッグするか、=ROW(A1)と入力して下方向へコピーするのが定番でした。どちらも機能しますが、元のデータが増減してもサイズは自動調整されません。

SEQUENCEなら同じ作業を1つのセルで完結でき、引数に応じて行・列・さらには格子状のグリッドまで生成します。構文は次のとおりです。

=SEQUENCE(rows, [columns], [start], [step])

データ内の8名のユニークな営業担当者に、1001から連番を振るには次の数式を使いました。

=SEQUENCE(8, 1, 1001, 1)

これで1001〜1008が1列で返されます。columnsを1、stepを1に設定すれば、連続する整数の縦リストになります。ただし、SEQUENCEの真価が発揮されるのは、他の関数の中に組み込んだときです。たとえば2026年1月の31日分の日付範囲は、たった1つの数式で作れます。

=DATE(2026, 1, SEQUENCE(31))

スピルした配列をTEXT、INDEXなど、範囲を期待するあらゆる関数に渡せば、以前はヘルパー列が必要だった処理が一気に片付きます。SEQUENCEで日付列を埋めるテクニックについては以前詳しく取り上げましたが、今でもカレンダーやスケジュール表を作るたびに時間を節約してくれています。

次に掘り下げたい関数たち

従来の方法が使えなくなったわけではありません。単に時間がかかり、メンテナンス性の低いファイルになりがちなだけで、それは自分にとっても、後からブックを開く他の人にとっても同じです。私にとっての大きな転換は、「デフォルトで手が伸びる先」が変わったことです。新しいスプレッドシートは動的配列の発想で設計を始め、古い道具箱は他人のファイルを編集するときだけ登場するようになりました。

次の学習リストにあるのは、文字列を自在に分割するTEXTSPLIT、範囲を縦横に結合するVSTACKとHSTACK、そしてピボットテーブルを丸ごと省略できるPIVOTBYとGROUPBYです。アップデートのたびに古い習慣が一つ引退していくようですが、それは大歓迎です。