Microsoft Excelで動的配列関数を使用する方法

Excel の動的配列関数を初めて発見したとき、スプレッドシート設計に対する私のアプローチ全体が変わりました。彼らは、私の不格好で多段階の回避策を、データの増大に応じて適応するスマートな自己拡張式に置き換え、複雑なワークフローを単一セル システムに変えました。

まだ手動のコピー & ペースト ルーチンを使用してデータセットを管理したり、壊れやすい数式を何千行も下にドラッグしたりしている場合は、これらのコア関数をマスターすると、Excel の操作方法が完全に変わります。

動的配列が Excel のデータ フローをどのように変えるか

CSEの終わりと流出範囲の上昇

従来、Excel の数式では 1 つのセルに 1 つの結果が生成され、列全体に計算をコピーする必要がありました。動的配列は、単一の数式でソース サイズに基づいて自動的に拡大または縮小するデータ ブロックを出力できるようにすることで、このワークフローを根本的に変更します。

この動作はスピルと呼ばれます。数式を入力して Enter キーを押すと、スピル範囲と呼ばれる細い青色の境界線でマークされた領域にデータが入力されます。重要なのは、この機能は単なる新しい関数セットではなく、アップグレードされた計算エンジンによってもたらされるということです。最新の Excel では、数式は複数の結果を返すことができ、必要に応じて新しい関数と既存の関数の両方を隣接するセルに書き込むことができます。これにより、構築とトラブルシューティングが難しいことで知られる古い Ctrl+Shift+Enter (CSE) 配列数式が不要になります。

ボタンをクリックせずに特定の行を分離する

以前は、Excel データセットをフィルター処理するには、リボンの[フィルター]ボタンを使用し、ボックスにチェックを入れ、結果を別の場所にコピーする必要がありました。しかし、ソース データが変更された瞬間に、それらの結果は古くなってしまいます。

FILTER 関数はそのワークフローを完全に置き換えます。行を非表示にする代わりに、一致するデータを別のスピル範囲に返します。

=FILTER(array, include, (if_empty))

という名前の Excel テーブルがあるとします。 従業員マスター。セル内の領域を選択すると G2、一致する結果をセルから流出させたい場合 I2。

に次の式を入力した後、 I2、データが変更されるか、別の領域が選択されると、出力は自動的に更新されます。

=FILTER(EmployeeMaster, EmployeeMaster(Region)=G2, "Region not found")

SORTBY: リストを実用的に整理する

固定順序から参照駆動のソートへ

基本的なリボンの並べ替え (「A ～ Z」) は静的リストの並べ替えには機能しますが、データセットに新しい行が継続的に追加される動的ダッシュボードではすぐに機能しなくなります。 Excel の SORT 関数は、順序付けを数式ベースの操作に変えることで、この点をすでに改善しています。ただし、列の位置に依存するため、時間の経過とともにデータセットの構造が変化すると脆弱になります。

より柔軟なアプローチは、依存関係を完全に削除する SORTBY 関数です。列インデックスによる並べ替えの代わりに、明示的な参照配列を使用して並べ替えます。つまり、ロジックは物理的なレイアウトではなくデータ自体に関連付けられます。これにより、並べ替えが位置ロジックからデータ駆動型ロジックに移行し、実際のダッシュボードでははるかに復元力が高くなります。

=SORTBY(array, by_array, (sort_order))

たとえば、 従業員マスター データセット、次の基準で直接並べ替えることができます 月次売上 列の位置に依存したり、基になるデータセットを変更したりすることなく、次のようになります。

=SORTBY(EmployeeMaster, EmployeeMaster(MonthlySales), -1)

並べ替えレイヤーは構造化参照を使用してフィールドを直接参照するため、データセットの構造が変更されても残ります。この時点では、計算を管理するのではなく、ワークブック内でデータがどのように流れるかを定義することになります。

ユニーク: 重複を削除して乱雑なデータをクリーンアップする

繰り返しのエントリを取り除き、きれいなディメンションを構築します

Excel で部門、名前、ID のクリーンなリストを作成するには、新しいデータに応答しない「重複の削除」などの破壊的なツールが必要でした。

現在、UNIQUE 関数は個別の値のライブ リストを返します。

=UNIQUE(array, (by_col), (exactly_once))

たとえば、次のように入力します。

=UNIQUE(EmployeeMaster(Department))

細胞に P1 テーブルをスキャンし、ソース データセットに新しい部門が追加されると拡張される一意の部門のクリーンなリストを生成します。

OS

Windows、macOS、iPhone、iPad、Android

無料トライアル

1ヶ月

Microsoft 365 には、最大 5 台のデバイスで Word、Excel、PowerPoint などの Office アプリ、1 TB の OneDrive ストレージなどへのアクセスが含まれています。

XLOOKUP: レイアウトを壊さずにデータ配列を取得する

単一のルックアップ値から複数の隣接する列を同時に取得します。

XLOOKUP 関数は、単に VLOOKUP の代替として扱われることがよくありますが、最新の Excel におけるその本当の強みは、流出モデルにいかに自然に適合するかにあります。単一の値ではなくレコード全体を返すことができ、複数列の戻り範囲が指定された場合、列の位置に依存せずに結果を水平方向に広げることができます。

構文は次のとおりです。

=XLOOKUP(lookup_value, lookup_array, return_array, (if_not_found), (match_mode), (search_mode))

私たちの中で 従業員マステr テーブルでは、セルからルックアップターゲットを渡すことで、完全な従業員の詳細を取得できます。 G2、それと照合します ID 列を作成し、複数のヘッダーにまたがるように戻り配列を設定します。

=XLOOKUP(G2, EmployeeMaster(EmployeeID), EmployeeMaster((EmployeeName):(MonthlySales)))

結果は数式セルから隣接する列に水平に広がります。また、固定列インデックスに依存しないため、テーブル構造が変わっても結果は安定します。

VSTACK と HSTACK: 分散したデータ ソースを結合する

重いツールを使用せずに高速でフォーミュラネイティブのレイアウトをスタッキング

個別の Excel データセットを統合するには、従来、手動でコピーするか、Power Query を使用する必要がありました。軽量の数式優先ワークフローの場合、VSTACK (垂直スタック) と HSTACK (水平スタック) を使用すると、外部のデータ準備ツールを起動せずに、セル計算内で配列を直接結合できます。これにより、高速な数式優先のデータ アセンブリに最適になります。

垂直方向に積み重ねる構文は次のとおりです。

=VSTACK(array1, (array2), ...)

水平方向にスタックするには、次を使用します。

=HSTACK(array1, (array2), ...)

このワークフローは、単純な循環ログを表す個別のテーブルがある場合に威力を発揮します。たとえば、四半期ごとの従業員指標を含む 3 つの Excel テーブルがあるとします (Q1_マスター、 Q2_マスター、 そして Q3_マスター）。次のように入力すると、それらをエンドツーエンドで積み重ねることができます。

=VSTACK(Q1_Master, Q2_Master, Q3_Master)

これにより、グリッド内に単一の統合されたデータセットが作成され、行がシームレスに追加され、手動で統合することなくソース テーブルの変更が反映されます。

コアの動的配列関数を超えたもの

Excel のスピルベース ツールの機能マップ

コアの動的配列関数を理解すると、Excel は同じスピルベースのモデルをより広範な特殊なオプションのセットに拡張します。

これらを組み合わせると、同じ原則が Excel 全体に拡張されます。つまり、データは手動で処理されなくなり、接続された数式レイヤーを通じて継続的に再形成されます。

スプレッドシートの未来をマスターする

動的配列は、Microsoft がここ数年で Excel に加えた最も革新的な変更の 1 つであり、不格好な従来のワークフローを、ワークブックの進化に応じてデータが更新、再形成、再計算されるモデルに置き換えます。この最新のワークフローを最大限に活用し始めると、Excel #SPILL を修正する方法がわかります。エラーは、すべてをスムーズに実行するのに役立ちます。