Excel の動的配列

内容

動的配列とは

2018 年 7 月、Microsoft は、まったく新しいツールを Microsoft Excel に追加する更新プログラムをリリースしました: 動的配列とそれらを操作するための XNUMX つの新しい関数です。これらのことは、誇張ではなく、数式や関数を操作する通常のテクニックを根本的に変え、文字通り、すべてのユーザーに関係します。

本質を説明する簡単な例を考えてみましょう。

都市の月に関するデータを含む単純なテーブルがあるとします。シートの右側にある空のセルを選択し、XNUMX つのセルではなく、すぐに範囲にリンクする数式を入力するとどうなりますか?

以前のすべてのバージョンの Excel で、入力します 2 つの最初のセル BXNUMX の内容のみを取得します。他には？

または、この範囲を =SUM(B2:C4) のような集計関数でラップして、総計を取得することもできます。

一意の値やトップ 3 の抽出など、基本的な合計よりも複雑な操作が必要な場合は、キーボードショートカットを使用して数式を配列数式として入力する必要があります。 Ctrlキー+シフト+入力します.

今ではすべてが異なります。

このような数式を入力した後、単にクリックすることができます入力します –結果として、参照したすべての値uXNUMXbuXNUMXbtoをすぐに取得します。

これは魔法ではなく、Microsoft Excel が持つ新しい動的配列です。新しい世界へようこそ🙂

動的配列の操作の機能

技術的には、動的配列全体が最初のセル G4 に格納され、必要な数のセルを右と下にデータで埋めます。配列内の他のセルを選択すると、数式バーのリンクが非アクティブになり、「子」セルの XNUMX つにいることが示されます。

XNUMX つまたは複数の「子」セルを削除しようとしても、何も起こりません。Excel はすぐに再計算して入力します。

同時に、これらの「子」セルを他の数式で安全に参照できます。

配列の最初のセルをコピーすると (たとえば、G4 から F8 に)、配列全体 (その参照) が通常の数式と同じ方向に移動します。

配列を移動する必要がある場合は、移動するだけで十分です (マウスまたは Ctrlキー+X, Ctrlキー+V)、再び、最初のメインセル G4 のみ – その後、新しい場所に転送され、アレイ全体が再び拡張されます。

作成された動的配列をシートの別の場所で参照する必要がある場合は、先頭のセルのアドレスの後に特殊文字 # (「ポンド」) を使用できます。

たとえば、作成された動的配列を参照するセルにドロップダウンリストを簡単に作成できるようになりました。

動的配列エラー

しかし、配列を拡張するのに十分なスペースがない場合、またはそのパスに他のデータによって既に占有されているセルがある場合はどうなるでしょうか? Excel の根本的に新しいタイプのエラーに対応 – ＃移行！ (#流出!):

いつものように、黄色のひし形と感嘆符が付いたアイコンをクリックすると、問題の原因の詳細な説明が表示され、干渉しているセルをすばやく見つけることができます。

配列がシートから外れたり、結合されたセルに当たったりすると、同様のエラーが発生します。障害物を取り除くと、すべてがすぐに修正されます。

動的配列とスマートテーブル

動的配列が、キーボードショートカットによって作成された「スマート」テーブルを指している場合 Ctrlキー+T または ホーム – 表としてフォーマットする (ホーム — テーブルとしてフォーマット)、その主な品質である自動サイズも継承します。

新しいデータを下部または右側に追加すると、スマートテーブルとダイナミックレンジも自動的に拡張されます。

ただし、XNUMX つの制限があります。スマートテーブル内のフォーラムでダイナミックレンジ参照を使用することはできません。

動的配列とその他の Excel 機能

わかりました、あなたは言います。これはすべて面白くて面白いです。以前のように、元の範囲の最初のセルへの参照を使用して数式を手動で拡大する必要はありません。それだけですか？

かなりされていません。

動的配列は、Excel の単なるツールではありません。今では、計算エンジンである Microsoft Excel の心臓部 (または頭脳) に組み込まれています。これは、私たちがよく知っている他の Excel の数式や関数も、動的配列の操作をサポートするようになったことを意味します。いくつかの例を見て、起こった変化の深さを理解してみましょう。

Transpose

範囲を転置する (行と列を入れ替える) Microsoft Excel には常に組み込み関数がありました。 輸送（転置）. ただし、これを使用するには、最初に結果の範囲を正しく選択する必要があります (たとえば、入力が 5×3 の範囲である場合は、3×5 を選択している必要があります)。次に、関数を入力して、組み合わせ Ctrlキー+シフト+入力します、配列数式モードでのみ機能するためです。

これで、XNUMX つのセルを選択し、同じ数式を入力して、法線をクリックするだけで済みます。入力します – 動的配列はそれ自体ですべてを行います:

九九

これは、Excel で配列数式の利点を視覚化するように求められたときに使用した例です。ここで、ピタゴラスの表全体を計算するには、最初のセル B2 に立ち、1 つの配列 (垂直および水平の数値セット 10..XNUMX) を乗算する式をそこに入力し、単にクリックするだけで十分です。入力します:

接着とケース変換

配列は乗算するだけでなく、標準の演算子 & (アンパサンド) を使用して結合することもできます。 XNUMX つの列から姓名を抽出し、元のデータのジャンプケースを修正する必要があるとします。配列全体を形成する XNUMX つの短い数式でこれを行い、それに関数を適用します。 プロプナック（ちゃんとした）レジスターを整理するには:

結論トップ3

上位 XNUMX つの結果を導き出し、それらを降順に並べたい数値がたくさんあるとします。これは XNUMX つの式で行われます。 Ctrlキー+シフト+入力します以前のように:

結果を列ではなく行に配置する場合は、この式のコロン (行区切り) をセミコロン (XNUMX 行内の要素区切り) に置き換えるだけで十分です。英語版の Excel では、これらの区切り記号はそれぞれセミコロンとコンマです。

一度に複数の列を抽出するVLOOKUP

機能 VPR （VLOOKUP） XNUMX つからではなく、一度に複数の列から値を取得できるようになりました。関数の XNUMX 番目の引数で配列としてそれらの番号を (任意の順序で) 指定するだけです。

動的配列を返す OFFSET 関数

データ分析のための最も興味深く有用な (VLOOKUP の後) 関数の XNUMX つは、次の関数です。 廃棄（オフセット）、私は一度に私の本の全章とここの記事を捧げました. この関数を理解して習得する際の難しさは、常に結果としてデータの配列 (範囲) が返されることでしたが、Excel はそのままで配列を操作する方法をまだ認識していなかったため、それを確認することはできませんでした。

今、この問題は過去のものです。単一の数式と OFFSET によって返される動的配列を使用して、並べ替えられたテーブルから特定の製品のすべての行を抽出する方法を確認してください。

彼女の主張を見てみましょう：

A1 – 開始セル (基準点)
ПОИСКПОЗ(F2;A2:A30;0) – 開始セルから最初に見つかったキャベツまでのシフトの計算。
0 – 「ウィンドウ」を開始セルに対して右にシフト
СЧЁТЕСЛИ(A2:A30;F2) – 返された「ウィンドウ」の高さの計算 – キャベツがある行の数。
4 — 水平方向の「ウィンドウ」のサイズ、つまり出力 4 列

動的配列の新しい関数

古い関数での動的配列メカニズムのサポートに加えて、いくつかの完全に新しい関数が Microsoft Excel に追加され、特に動的配列を操作するために強化されました。特に、これらは次のとおりです。

GRADE （選別） – 入力範囲をソートし、出力に動的配列を生成します
ソートポ（並び替え） – ある範囲を別の範囲の値で並べ替えることができます
フィルタ（フィルター） – 指定された条件を満たすソース範囲から行を取得します
ユニク（個性的） – 範囲から一意の値を抽出するか、重複を削除します
SLMASSIVE (ランドアレイ) – 指定されたサイズの乱数の配列を生成します
産後（順序） — 指定されたステップで一連の数値から配列を形成します

それらについての詳細 – 少し後で。それらは、思慮深い研究のために別の記事（XNUMXつではなく）の価値があります🙂

結論

上記のすべてをお読みになった方は、すでに起こった変化の規模にお気づきかと思います。 Excel の非常に多くのことを、より簡単に、より簡単に、より論理的に実行できるようになりました。ここ、このサイト、そして私の本でどれだけ多くの記事を修正しなければならないかを知って少しショックを受けていることを認めなければなりませんが、私は軽い心でこれを行う準備ができています.

結果をまとめると、 плюсы 動的配列では、次のように記述できます。

組み合わせを忘れることができます Ctrlキー+シフト+入力します. Excel では、「通常の数式」と「配列数式」に違いがなく、同じように扱われるようになりました。
機能について SUMPRODUCT (和積)、以前は配列数式を入力するために使用されていました Ctrlキー+シフト+入力します忘れることもできます–今では十分に簡単です和 и 入力します.
スマートテーブルと使い慣れた関数 (SUM、IF、VLOOKUP、SUMIFS など) も、動的配列を完全または部分的にサポートするようになりました。
下位互換性があります。動的配列を含むワークブックを古いバージョンの Excel で開くと、それらは配列数式 (中括弧内) に変わり、「古いスタイル」で引き続き機能します。

いくつかの番号を見つけました マイナス:

動的配列から個々の行、列、またはセルを削除することはできません。つまり、単一のエンティティとして存続します。
通常の方法で動的配列をソートすることはできません データ – 並べ替え (データ — 並べ替え). これには特別な機能があります。 GRADE （選別）.
ダイナミックレンジをスマートテーブルに変換することはできません (ただし、スマートテーブルに基づいてダイナミックレンジを作成することはできます)。

もちろん、これで終わりではありません。Microsoft は今後もこのメカニズムを改善し続けると確信しています。

どこでダウンロードできますか？

そして最後に、主な質問🙂

Microsoft は、2018 年 XNUMX 月のカンファレンスで、Excel の動的配列のプレビューを最初に発表し、示しました。 発火する. 次の数か月で、新機能の徹底的なテストと慣らし運転が行われました。猫 Microsoft 自体の従業員、そして Office Insider サークルのボランティアテスター。今年、動的配列を追加する更新プログラムが、通常の Office 365 サブスクライバーに徐々に展開され始めました。たとえば、Office 365 Pro Plus (Monthly Targeted) サブスクリプションで XNUMX 月に受け取っただけです。

Excel にまだ動的配列がないが、実際に動的配列を使用したい場合は、次のオプションがあります。

Office 365 サブスクリプションをお持ちの場合は、この更新プログラムが届くまでお待ちください。これがどのくらいの速さで行われるかは、更新プログラムが Office に配信される頻度 (年に XNUMX 回、半年に XNUMX 回、月に XNUMX 回) によって異なります。会社の PC を使用している場合は、更新プログラムをより頻繁にダウンロードするように管理者に依頼できます。
これらの Office Insider テストボランティアの仲間入りをすることができます。そうすれば、すべての新機能を最初に受け取ることができます (もちろん、Excel のバグが増える可能性はあります)。
サブスクリプションをお持ちではなく、箱入りのスタンドアロンバージョンの Excel をお持ちの場合は、少なくとも 2022 年に Office と Excel の次のバージョンがリリースされるまで待つ必要があります。このようなバージョンのユーザーは、セキュリティ更新プログラムとバグ修正のみを受け取り、すべての新しい「特典」は Office 365 サブスクライバーのみに提供されるようになりました。悲しいけど本当です🙂

いずれにせよ、動的配列が Excel に表示されたら、この記事を読めば準備完了です 🙂

配列数式とは何ですか、および Excel でそれらを使用する方法
OFFSET 関数を使用したウィンドウ (範囲) の合計
Excel で表を転置する 3 つの方法