エクセルダイナミックアレイ、関数、および式

Excel 365の革新的なコンピューティングエンジンアップデートにより、アレイ式は理解しやすくなり、高度なユーザー向けの専用スキルではなくなりました。このチュートリアルでは、Excelの動的配列の概念について説明し、ワークシートの効率を改善し、設定を簡素化するためにそれらを使用する方法を示しています。

Excelアレイ式は、常に専門家およびフォーミュラの専門家の特許と見なされてきました。 「アレイ式で解決できる」という文は、多くのユーザーにすぐに応答することがよくあります。「他に何か方法はありますか？」

ダイナミックアレイの導入は、待望の非常に歓迎すべき変更です。トリックやトリックなしで複数の値を単純な方法で処理できるため、すべてのExcelユーザーは、動的配列の式を理解して簡単に作成できます。

- エクセルダイナミックアレイ

• 動的配列の可用性
• 動的配列関数
• 動的配列式の例
• オーバーフロー領域 - 1つの式、複数のセル
• オーバーフローエリアリファレンス（＃シンボル）
• 暗黙の交差点と @文字
• 動的配列の利点
• 動的配列の制限
• 動的配列と従来のCSEアレイ式
• 後方互換性
• Excel動的配列式は無効です

エクセルダイナミックアレイ

動的配列は、単一のセルに入力された式に基づいて、すべてを複数のセルに自動的に計算して返す変更アレイです。

Microsoft Excelは過去30年にわたって多くの変更を受けましたが、一貫しています。フォーミュラ、セルです。従来の配列式でも、結果を表示する各セルに式を入力する必要があります。動的配列を使用すると、このルールは適用されなくなります。これで、Array値を返す式は、Ctrlシフトを押すか、他のことを実行せずに隣接するセルに自動的にオーバーフローします。言い換えれば、動的配列を操作することは、単一のセルを操作するのと同じくらい簡単です。

非常に基本的な例を使用して、この概念を説明しましょう。たとえば、2つのセットの数字を掛ける必要があるとします。たとえば、異なる割合を計算します。

Excelの非ダイナミックバージョンでは、複数のセルに入力し、Ctrlシフトを押してArray式として明示的に設定しない限り、次の式は最初のセルにのみ適用されます。

=A3:A5*B2:D2

ここで、Excel 365で同じ式を使用するときに何が起こるかを確認します。セルに入力するだけで（この場合はB3）、Enterを押します...領域全体が直ちに結果を記入します。

複数のセルを単一の式で充填することはオーバーフローと呼ばれ、細胞の充填領域はオーバーフローと呼ばれます。

最近の更新は、Excelアレイに対処するための単なる新しい方法以上のものであることに注意する必要があります。実際、これはコンピューティングエンジン全体のブレークスルーの変更です。動的配列により、Excel関数ライブラリに一連の新しい機能が追加され、既存の機能もより速く、より効率的に実行され始めました。最終的に、新しい動的配列は、CTRLシフトEnterショートカットを使用して入力された古いスタイルの配列式を完全に置き換える必要があります。

エクセルダイナミックアレイの可用性

ダイナミックアレイは2018年のMicrosoft Ignite Conferenceで発売され、2020年1月にOffice 365の加入者にリリースされました。現在、 Microsoft 365サブスクリプションとExcel 2021で利用できます。

次のバージョンは動的配列をサポートしています。

• Windows用のExcel 365
• Mac用のExcel 365
• Excel 2021
• Mac用のExcel 2021
• iPadに優れています
• iPhoneに優れています
• AndroidタブレットバージョンExcel
• ExcelのAndroidモバイルバージョン
• ウェブ上で優れています

Excel動的配列関数

新しい機能の一部として、6つの新しい機能がExcel 365に導入され、アレイをネイティブに処理し、データをセル範囲に出力できます。出力は常に動的です。ソースデータに変更が発生すると、結果は自動的に更新されます。したがって、グループの名前は -動的配列関数です。

これらの新しい機能は、伝統的に解決が難しいと考えられていた多くのタスクを簡単に処理できます。たとえば、重複を削除し、一意の値を抽出およびカウントし、ヌル値をフィルターし、ランダムな整数と小数を生成し、昇順または下降順にソートすることができます。

以下は、各関数の関数の簡単な紹介と、詳細なチュートリアルへのリンクです。

• 一意 - セルの範囲から一意のアイテムを抽出します。
• フィルター - 定義する基準に基づいたフィルターデータ。
• 並べ替え - 指定された列でセル範囲を並べ替えます。
• sortby-別の領域または配列でセルの範囲を並べ替えます。
• RandArray-乱数の配列を生成します。
• シーケンス - シリアル番号のリストを生成します。
• textsplit-指定された区切り文字によって文字列を列または列に分割します。
• tocol-アレイまたは領域を単一の列に変換します。
• TOROW -REGIONまたはARRAYを1列に変換します。
• wrapcols-各行に指定された値の数に基づいて、行または列を2次元配列に変換します。
• ラプロウ - 各列に指定された値の数に基づいて、行または列を2次元配列に変換します。
• テイク - アレイの最初または端から指定された数の連続した行または列を抽出します。
• ドロップ - 配列から特定の数の行または列を削除します。
• 拡張 - 指定された数の行と列に配列を拡張します。
• ChooseCols-アレイから指定された列を返します。
• Chooserows-アレイから指定された行を抽出します。
• GroupBy- 1つ以上の列の値に基づいて、グループと集約データを行ごとに集約します。
• Pivotby -行と列、および関連する値の集合体でデータをグループ化します。

さらに、正式にはグループに属さないが、動的配列のあらゆる利点を活用できる人気のあるExcel関数には、2つの最新の代替品があります。

• Xlookup- vlookup、Hlookup、Lookupのより強力な後継者であり、列と行で複数の値を見つけて返すことができます。
• XMATCH-一致関数関数のより一般的な後継者であり、垂直および水平方向のルックアップを実行し、指定されたアイテムの相対位置を返します。

エクセルダイナミックアレイ式

Excelの最新バージョンでは、動的配列の動作は深く統合されており、これらの関数が最初に配列で動作するように設計されていなくても、すべての機能に固有のものになります。要するに、複数の値を返す任意の式で、Excelは結果が出力されるサイズ変更領域を自動的に作成します。この能力により、既存の機能は魔法の役割を果たすことができます！

次の例は、動的アレイ式の実用的なアプリケーションと、既存の関数に対する動的配列の影響を示しています。

例1。新しい動的配列関数

この例は、Excelの動的配列関数を使用して、ソリューションをどれだけ速くて簡単に実現できるかを示しています。

列から一意の値のリストを抽出するには、通常、以下に示すように複雑なCSE式を使用します。ダイナミックエクセルでは、一意の式の基本的な形式のみが必要です。

=UNIQUE(B2:B10)

空のセルに式を入力し、Enterを押します。 Excelはすぐにリスト内のすべての異なる値を抽出し、それらを式に入ったセルから始まるセルの範囲にそれらを出力します（この場合はD2）。ソースデータが変更されると、結果は自動的に再計算され、更新されます。

例2。1つの式で複数の動的配列関数を組み合わせる

1つの関数を使用してタスクを完了する方法がない場合は、いくつかの関数を一緒にリンクできます！たとえば、条件に基づいてデータをフィルタリングし、結果をアルファベット順に並べ替えるには、次のように並べ替え関数をフィルターの周りにラップできます。

=SORT(FILTER(A2:C13, B2:B13=F1, "No results"))

ここで、A2：C13はソースデータ、B2：B13がチェックする値であり、F1は条件です。

例3。既存の関数を使用して新しい動的配列関数を使用する

Excel 365に実装されている新しいコンピューティングエンジンは、従来の式を簡単に配列に変換できるため、新しい機能と古い機能をグループ化するのを妨げるものはありません。

たとえば、地域の一意の値の数を計算するには、古いcounta関数に動的な配列ユニークな関数をネストできます。

=COUNTA(UNIQUE(B2:B10))

例4.既存の関数は動的配列をサポートします

Excelの古いバージョン（Excel 2016やExcel 2019など）でトリム関数にセル範囲を提供すると、最初のセルの結果が1つの結果を返します。

=TRIM(A2:A6)

ダイナミックエクセルでは、同じ式がすべてのセルを処理し、次のように複数の結果を返します。

例5。複数の値を返すvlookup式

私たち全員が知っているように、vlookup関数は、指定した列インデックスに基づいて単一の値を返すように設計されています。ただし、Excel 365では、いくつかの列から一致を返すために一連の列番号を提供できます。

=VLOOKUP(F1, A2:C6, {1,2,3}, FALSE)

例6。簡素化された転移式

Excelの以前のバージョンでは、Transpose関数の構文はエラーなしではありません。ワークシートのデータを回転させるには、元の列と行を計算し、同じ数の空のセルを選択しますが、方向を変更します（大きなワークシートでは困惑している操作です！）、選択した領域のTransposeフォーミュラを入力し、Ctrlシフトを押してEnterを正しく完全に完了します。電話！

Dynamic Excelでは、出力領域の左端セルに式を入力し、Enterを押します。

=TRANSPOSE(A1:B6)

それをやってください！

オーバーフロー領域 - 1つの式、複数のセル

オーバーフロー領域は、動的配列式によって返される値を含むセルの領域です。

オーバーフロー領域のセルを選択すると、青い境界線がその内部のすべてが左上のセルの式によって計算されていることを示しているように見えます。最初のセルで式を削除すると、すべての結果が消えます。

オーバーフロー領域は、Excelユーザーが簡単にする優れた機能です。以前は、CSEアレイ式を使用した場合、それらをコピーしたいセルの数を推測する必要がありました。これで、最初のセルに式を入力し、Excelに残りを処理させます。

知らせ。他のデータがオーバーフロー領域をブロックすると、#spillエラーが発生します。データが削除されると、エラーが消えます。

詳細については、Excel Overflowエリアを参照してください。

オーバーフローエリアリファレンス（＃シンボル）

ダイナミックアレイ式で返されるオーバーフロー領域全体を参照するには、エリアの左上隅にあるセルのアドレスの後にポンドサインまたはポンドサイン（＃）を追加します。

たとえば、A2のRandArray式によって生成される乱数の数を見つけるには、Counta関数へのオーバーフロー領域の参照を提供します。

=COUNTA(A2#)

オーバーフロー領域に値を追加するには、次を使用します。

=SUM(A2#)

ヒント：

• オーバーフロー領域をすばやく参照するには、マウスを使用して青いボックス内のすべてのセルを選択するだけで、Excelはオーバーフローリファレンスを作成します。
• 通常のエリアの参照とは異なり、オーバーフローエリアの参照は動的であり、エリアのサイズ変更に自動的に応答します。詳細については、Overflow Regionオペレーターを参照してください。

暗黙の交差点と @文字

ダイナミックアレイExcelでは、フォーミュラ言語には別の重要な変更があります。 @文字が導入され、暗黙の交差演算子と呼ばれます。

Microsoft Excelでは、暗黙的な交差点は、複数の値を単一の値に減らす式の動作です。古いExcelでは、セルは単一の値のみを含むことができるため、これはデフォルトの動作であり、特別な演算子は必要ありません。

Excelの新しいバージョンでは、すべての式はデフォルトで配列式として扱われます。特定の式で配列の動作を使用したくない場合は、暗黙の交差点演算子を使用できます。言い換えれば、式を1つの値のみを返す必要がある場合は、関数名を @でプレフィックスすると、従来のExcelの非アレイ式のように実行されます。

実際にどのように機能するかを確認するには、以下のスクリーンショットをご覧ください。

C2には、結果を多くのセルにオーバーフフ化する動的な配列式があります。

=UNIQUE(A2:A9)

E2では、関数には @文字が付いており、暗黙の交差点を呼び出します。その結果、最初の一意の値のみが返されます。

=@UNIQUE(A2:A9)

詳細については、Excelの暗黙の交差点を参照してください。

Excelの動的配列の利点

ダイナミックアレイが長年にわたってExcelの最高の機能強化の1つであることは間違いありません。他の新機能と同様に、彼らは長所と短所を持っています。幸いなことに、新しいExcelダイナミックアレイ式の利点は圧倒的です！

シンプルでパワフル

動的配列により、より強力な式を簡単に作成できます。ここにいくつかの例があります：

• 一意の値を抽出します：従来の式|動的配列関数
• 一意の値と異なる値をカウント：従来の式|動的配列関数
• 列をアルファベット順に並べ替えます：従来の式|動的配列関数

すべての式のネイティブサポート

Dynamic Excelでは、どの関数がアレイをサポートし、どの関数がそうでないかを心配する必要はありません。式が複数の値を返すことができる場合、デフォルトでそうします。これは、この例に示すように、算術操作と従来の機能にも適用されます。

ネストされた動的配列関数

より複雑なタスクソリューションを解決するために、新しいExcelダイナミックアレイ関数を自由に組み合わせるか、こことここに示すように古い関数でそれらを使用できます。

相対的および絶対的な引用はあまり重要ではありません

「1つの式、複数の値」アプローチのため、技術的には1つのセルのみにあるため、$シンボルで領域をロックする必要はありません。したがって、ほとんどの場合、絶対、相対、または混合細胞の参照（常に初心者の混乱の原因です）を使用することは本当に重要ではありません。

動的配列の制限

新しいダイナミックアレイは素晴らしいですが、他の新しい機能と同様に、いくつかのメモがあります。

結果を通常の方法でソートすることはできません

Dynamic Array式によって返されるオーバーフロー領域は、Excelのソート機能を使用してソートすることはできません。そのような試みは、「配列の一部を変更できません」エラーが発生します。結果を小さいものから大きいものから大きく、または大部分から小さく並べるには、ソート関数に現在の式をラップします。たとえば、これはあなたが同時にフィルタリングとソートする方法です。

オーバーフロー領域の値を削除できません

同じ理由で、オーバーフロー領域の値を削除することは不可能です。配列の一部を変更することはできません。この動作は予想され、論理的です。同じことが従来のCSEアレイ式にも当てはまります。

Excelフォームにはサポートされていません

この機能（またはエラー？）は非常に予想外です。動的配列式は、通常の領域でのみExcelテーブルでは機能しません。オーバーフロー領域をテーブルに変換しようとする場合、Excelはこれを行います。ただし、#spillのみが表示されます！エラー、結果ではありません。

Excel Powerクエリには利用できません

動的配列式の結果を電源クエリにロードすることはできません。たとえば、電源クエリを使用して2つ以上のオーバーフロー領域を一緒にマージしようとする場合、この操作は機能しません。

動的配列と従来のCSEアレイ式

動的配列の導入により、2種類のExcelについて説明できます。

1. Dynamic Excelは、動的配列、関数、および式を完全にサポートします。現在、Excel 365とExcel 2021のみです。
2. 非ダイナミックExcelとも呼ばれる従来のExcelは、Ctrl Shift Enter Array式のみをサポートしています。 Excel 2019、Excel 2016、Excel 2013以前です。

言うまでもなく、動的配列はあらゆる方法でCSEアレイ式を上回ります。従来のアレイ式は互換性の理由で保存されていますが、これからは新しい配列式を使用することをお勧めします。

ここに最も重要な違いがあります：

• 動的配列式はセルに入力され、通常のENTERキーを使用して実行されます。古いスタイルの配列式を完了するには、Ctrl Shift Enterを押す必要があります。
• 新しい配列式は、複数のセルに自動的にオーバーフローします。複数の結果を返すには、CSE式をセル範囲にコピーする必要があります。
• 動的配列式の出力は、ソース領域のデータが変化すると自動的にサイズ変更されます。リターンエリアが小さすぎる場合、CSEフォーミュラは出力を切り捨てます。リターンエリアが大きすぎる場合、冗長セルでエラーが返されます。
• 動的配列式は、単一のセルで簡単に編集できます。 CSE式を変更するには、領域全体を選択して編集する必要があります。
• CSEフォーミュラ領域に行を削除して挿入することはできません。最初に既存のすべての式を削除する必要があります。動的配列の使用、行の挿入または削除は問題ではありません。

後方互換性：従来のExcelの動的配列

古いExcelで動的配列式を含むワークブックを開くと、ブレース{}に囲まれた通常の配列式に自動的に変換されます。 Excelの新しいバージョンでワークシートを再度開くと、ブレースが削除されます。

従来のExcelでは、この機能がサポートされていないことを示すために、新しい動的配列関数とオーバーフローエリア参照の前に_xlfnがあります。オーバーフローエリア参照記号（＃）は、アンカーアレイ関数に置き換えられます。

たとえば、以下は、 Excel 2013で一意の式がどのように表示されるかです。

ほとんどの動的な配列式（すべてではありませんが！）は、変更を加えるまで、従来のExcelで結果を表示し続けます。式を編集するとすぐに壊れて、1つ以上の#Namesを表示しますか？エラー値。

Excel動的配列式は無効です

関数に応じて、誤った構文または無効なパラメーターが使用されている場合、異なるエラーが発生する可能性があります。動的配列式を使用する際に遭遇できる最も一般的な間違いの3つを次に示します。

#spill！エラー

#spillは、動的配列が複数の結果を返したときに発生しますが、何かがオーバーフロー領域をブロックします！間違い。

このエラーを修正するには、完全に空ではないオーバーフロー領域のセルをクリアまたは削除するだけです。邪魔なすべてのセルをすばやく見つけるには、エラーインジケータをクリックし、 [ブロッキングセルを選択]をクリックします。

空ではないオーバーフロー領域に加えて、このエラーは他のいくつかの理由によって引き起こされる可能性があります。詳細については、次を参照してください。

• Excel #Spillエラー - 原因とソリューション
• vlookup、インデックスマッチ、Sumifで#spillを修正する方法！間違い

#ref！エラー

ワークブック間の外部参照に対するサポートが限られているため、動的配列には2つのファイルを同時に開く必要があります。ソースワークブックが閉じられている場合、#REFが表示されます！間違い。

＃名前？エラー

Excelの古いバージョンで動的配列関数を使用しようとすると、#Nameが発生しますか？間違い。新しい機能はExcel 365およびExcel 2021でのみ利用可能であることを忘れないでください。

このエラーがExcelのサポートされているバージョンで発生した場合、問題のセルの関数名を再確認します。多分それは間違って入力された:)

これは、Excelで動的配列を使用する方法です。この素晴らしい新機能をお楽しみください！とにかく、読んでくれてありがとう、来週私たちのブログでお会いしましょう！

以上がエクセルダイナミックアレイ、関数、および式の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。