Excel の動的配列関数がワークフローを簡素化した方法 - 生産性の飛躍的な進歩

2026 年 4 月 30 日、午前 10 時 30 分 EDT に公開

Yasir は機械エンジニアであり、MUO で Windows、生産性、セキュリティ、インターネットなどのテクノロジーについて執筆しています。彼は自律システムに興味があるため、ハードウェアとソフトウェアの両方を常にいじくり回しています。

彼のテクノロジーライティングの旅はエンジニアリングの 3 年生の時に始まり、MUO に入社する前に Android Police につながりました。彼は、Windows の問題のトラブルシューティング、生産性向上ツールの探索、わかりやすい英語でのセキュリティ リスクの説明など、テクノロジーを利用しやすくすることに重点を置いています。 Yasir 氏にとって、これを行うための最善の方法は、実際にツールを使用して、読者が直面しているのと同じ問題に遭遇することです。

執筆やエンジニアリングを行っていないときは、Yasir が Impractical Jokers を見て、これまでに何十回も見たいたずらを心から笑っているのがわかります。

私が知っているほとんどの Excel ユーザーは、私と同じ方法で数式を学習しました。つまり、一度に 1 つの関数を、すでに知っているものの上に積み重ねていきました。動的配列関数はこれらのスキルに代わるものではありません。多くの回避策が不要になるだけです。私はしばらくの間、TAKE と DROP を使用して自己更新トップ 5 リストを実行してきましたが、以下の 4 つの関数でも同じ変化が起こりました。それぞれの手法により、私が反射的に実行していた複数ステップのルーチンが 1 つの公式に減りました。

関連

FILTER は、ヘルパー列と配列数式の儀式全体を置き換えました

複数の関数を分割していた機能を 1 つの数式で実行できるようになりました

古い Excel で一致する行を取得するには、ネストされた INDEX、MATCH、SMALL、IFERROR の数式を作成し、Ctrl + Shift + Enter で入力する必要がありました。それはうまくいきましたが、後でそれを維持するのが問題でした。もう 1 つのオプションは、オートフィルターを適用し、表示されている行をコピーし、静的な値として別の場所に貼り付けることでした。ソース データが変更されるまでは、これでも問題ありませんでした。

FILTER 関数は、同じ作業を 1 行で実行します。私の売上スプレッドシートには、地域、製品カテゴリ、営業担当者にわたる 32 行が含まれており、西部地域からすべてのエレクトロニクス製品の売上を取得すると、次のようになります。

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

最初の引数は、返したい範囲です。 2 番目は条件で、2 つのチェック間の乗算は AND として機能します。両方とも true である必要があります。アスタリスクをプラス記号に切り替えると OR になり、結果が自動的に出力されます。ソースに新しい行を追加すると、Enter キーを押した瞬間にこぼれた出力が更新されます。

IFERROR で FILTER を 3 番目の引数でラップして、空の結果を処理できます。 =FILTER(range, condition, "No matches") シートに #CALC が表示されないようにします。該当する行がない場合はエラーになります。

UNIQUE は、私の 3 ステップの重複排除ルーチンを 1 つのセルに変えました

重複の削除は有効ですが、自分自身で更新されたことがありません

[データ] タブの [重複の削除] オプションは、1 回限りのクリーンアップには適しています。問題は、静的なリストが生成されることです。列を新しい場所にコピーしたり、ダイアログを実行したり、結果を並べ替えたりすると、次回誰かが行を追加したときにすべてをやり直す必要があります。私は数えきれないほどそうしました。

UNIQUE はそのすべてのステップをスキップします。営業スプレッドシートの営業担当者の列をポイントすると、次のようになります。

=UNIQUE(D2:D33)

その結果、ジョン・スミス、サラ・ジョンソン、マイク・ウィルソン、リサ・ブラウン、デビッド・チェン、エマ・デイビス、トム・ロドリゲス、エイミー・フォスターが流出した。これは、ダイアログ ボックスのない 8 つの名前です。 =SORT(UNIQUE(D2:D33)) としてラップします。 同じリストをアルファベット順に返します。出力はソースに接続されたままであるため、データに新しい名前を追加すると、こぼれたリストが自動的に拡張されます。

この設定により、データ検証ドロップダウン リストのクリーンなソースも作成されます。 D35# のようなハッシュを使用してこぼれた範囲を参照する場合 、ソース データに新しい名前が表示されると、ドロップダウンは自動的に大きくなります。

SORTBY は私のコピーアンドペーストとソートのシャッフルを完全に終了させました

ソース データを変更せずにビューを並べ替える

Excel での並べ替えには常に小さなリスクが伴います。ソースを再配置すると、固定行を参照する数式が壊れる可能性があるため、代替策として、チャンクを安全な場所にコピーし、そのコピーを並べ替えることにしました。 SORT は役に立ちましたが、それはソートしたすべての列を満足して表示できる場合に限られていました。

SORTBY はその制約を取り除きます。ある範囲を別の範囲の値を使用して並べ替えるので、2 番目の範囲が出力に表示される必要はありません。次の式を使用して、データセット内の収益に基づいて営業担当者をランク付けしました。

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

最初の引数は返される内容 (営業担当者) で、2 番目の引数は並べ替えの基準 (売上収益) で、-1 は順序を降順に設定します。名前は、売上高の最高額から最低額までのランク付けで返され、要求しない限り、売上高の列が出力に表示されることはありません。

FILTERとの組み合わせもきれいです。 FILTER(D2:G33, B2:B33="North") のラッピング inside SORTBY は、北部地域のレコードのみを高位から低位にソートして一度に返します。ソース データは変更されません。

SORTBY は複数の並べ替えレベルを受け入れます。範囲と順序のペアをさらに追加して、最初に地域ごとに並べ替え、次に各地域内の売上収益ごとに並べ替えることができます。

SEQUENCE はフィル ハンドルと私が誇りに思っていた ROW トリックを置き換えました

単一セルをドラッグせずにシリーズを生成する

番号付きシリーズを生成するには、忍耐力がなくなるまでフィル ハンドルをドラッグするか、=ROW(A1) を記述する必要がありました。 そしてそれをコピーします。どちらも機能しますが、基になるデータが変更されるとどちらもサイズ変更されません。

SEQUENCE は単一のセルで同じタスクを処理し、引数に応じて行、列、または完全なグリッドを生成します。構文は次のとおりです。

=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 です。リリースごとに別の習慣が廃止されるようですが、それは私にとっては問題ありません。