Microsoft ExcelのEXPAND関数の使い方

Microsoft Excel の動的配列は自動更新の変革をもたらしますが、その予測できないサイズにより、ダッシュボードの書式設定が台無しになる可能性があります。 EXPAND 関数を使用すると、レイアウトを所定の位置にロックし、スプレッドシートを船の形に保つことができます。その方法は次のとおりです。

EXPAND 関数の仕組み

EXPAND をデータのコンテナーとして考えてください。値を変更することはありません。値が特定の数の行または列を強制的に占めるだけです。たとえば、2×2 の小さな正方形のデータがあり、それをダッシュ​​ボードの 5×5 レイアウトに適合させる必要がある場合、EXPAND はギャップを埋めるフレームとして機能します。

EXPAND 構文

この関数は、4 つの引数を使用してデータ コンテナーの境界を定義します。

=EXPAND(array,(rows),(columns),(pad_with))

• 配列 展開するソース データまたは式の結果 (FILTER または UNIQUE リストなど) です。
• 行 最終結果に含める行の合計数です。省略した場合、デフォルトでソース配列の高さが設定されます。
• 列 最終結果に含める列の合計数です。省略した場合、デフォルトでソース配列の幅が使用されます。
• パッド付き は、「追加」セルに表示したい値です。これを空白のままにすると、Excel は新しいセルに #N/A を入力します。わかりやすくするために、空白のセルには “” を、ダッシュには “-” を使用できますが、この引数は任意のデータ型 (テキスト、数値、または別の数式) を受け入れます。

少なくとも 行 または 列 引数を使用してデータのサイズを変更します。それ以外の場合、関数は元の配列を変更せずに返します。また、すべての動的配列関数と同様に、EXPAND は Excel テーブル内で使用できません。EXPAND は Excel テーブルの外に存在する必要があります。これは、これらの最新の関数では結果がセル範囲に書き込まれるのに対し、Excel テーブルでは個々のセルに単一の自己完結型の数式または値が必要となるためです。

関連している

Microsoft Excelの使い方を変えた6つの機能

動的配列関数はゲームチェンジャーでした。

使用例 1: ダッシュボードの対称性の維持

Excel で並べて比較するテーブルを作成する場合、EXPAND を使用すると、カテゴリ間でデータ量が異なる場合でも、レイアウトのバランスが完全に保たれます。

シナリオ： T_Sales という名前の Excel テーブルがあり、2 つの地域の販売実績を比較するための「対」ダッシュボードを構築しています。データ検証ドロップダウン メニューからセル F1 および J1 の領域を選択すると、セル E2 および I2 の FILTER 関数がテーブルから関連するデータを取得します。

EXPAND 関数を使用しないと、F1 の地域には 5 件の売上があるのに、J1 の地域には 4 件しかない場合、偏ったダッシュボードが作成されてしまいます。これは、動的配列が返される結果の数に応じて自動的に縮小および拡大するためです。

セル E2:

=FILTER(T_Sales,T_Sales(Region)=F1)

セル I2:

=FILTER(T_Sales,T_Sales(Region)=J1)

フィルターを EXPAND でラップすると、両方のテーブルのフットプリントが固定の高さにロックされ、ダッシュボード要素が完全に整列した状態に保たれます。

セル E2:

=EXPAND(FILTER(T_Sales,T_Sales(Region)=F1),10,,"-")

セル I2:

=EXPAND(FILTER(T_Sales,T_Sales(Region)=J1),10,,"-")

• フィルター 領域がセル F1 とセル J1 の値にそれぞれ一致するすべての行を取得します。
• 10 Excel に、流出した結果の高さを 10 行にするように指示します。
• 「-」 実際のデータ数と 10 行の制限の間の行をダッシュ​​で埋めます。

あなたが飛び越えてから 列 引数を指定すると、Excel は FILTER 結果の元の幅 (3 列) を維持することを前提とします。また、EXPAND 関数がデフォルトで配列の (先頭ではなく) 末尾に新しい行を追加する方法にも注目してください。

関連している

XLOOKUP のことは忘れてください: Excel データの抽出に FILTER が優れている理由

FILTER 関数は一致するすべてのレコードを抽出しますが、XLOOKUP は最初の結果のみを返します。

使用例 2: VSTACK 用に一致しないテーブルを準備する

EXPAND は構造的なプリプロセッサとして機能し、エラーを引き起こすことなく列数が異なるデータセットをマージできるようにします。

シナリオ： VSTACK 関数を使用して、2 つのテーブル T_Primary と T_Secondary を 1 つのマスター リストに結合する必要があります。ただし、T_Primary には 3 つの列が含まれていますが、T_Secondary には 2 つの列しか含まれていません。

VSTACK のみを使用してマージを完了すると、数式はテーブルが一致しない #N/A の配列を返します。

=VSTACK(T_Primary,T_Secondary)

ただし、EXPAND を使用すると、空白またはダッシュからなる 3 番目の仮想列を幅の狭い T_Secondary テーブルに追加でき、結果がより整然となります。

=VSTACK(T_Primary,EXPAND(T_Secondary,,3,"-"))

• 拡大する T_Secondary のみに焦点を当て、水平方向に 3 列に拡張します。
• 「-」 空白またはエラーにならないように、空の Notes 列をダッシュ​​で埋めます。
• Vスタック 元の T_Primary テーブルと新しく埋め込まれた T_Secondary テーブルを 1 つの連続リストにマージします。

の値を指定しませんでした 行 引数を使用するのは、テーブルの高さは変更したくないためです。幅だけを変更したいからです。また、EXPAND 関数がデフォルトで配列の (左側ではなく) 右側に列を追加する方法にも注目してください。

関連している

Excel での二重引用符の役割を知る必要がある

二重引用符は音声だけで使用できるわけではありません。

使用例 3: 安定した UI コンテナーの作成

Excel スプレッドシートの外観は動作と同じくらい重要な場合があります。EXPAND 関数はその両方に役立ちます。ダッシュをパディング文字として使用すると、検索結果が短い場合でも視覚的に固定された結果カードを作成できます。

シナリオ： 列 A:C に T_Invoice という名前のソース テーブルがあります。セル I2 に値を入力すると、セル E2 の FILTER 式によって結果が右下にこぼれます。ただし、T_Invoice テーブルが拡大または縮小した場合でも、その高さと常に一致する列 E:G に結果カードを作成する必要があります。

この設定により、スプレッドシートのレイアウトのバランスが取れたプロフェッショナルな状態が維持されます。ただし、より重要なのは、 パッド付き ダッシュを挿入する引数は、重要な機能上の目的を果たします。スプレッドシートにアクセスする人に対して、その領域がアクティブであるため、これらのセルに何も入力しないように警告します。これにより、恐ろしい #SPILL を防ぐことができます。フィルター結果が範囲を設定しようとしたときにエラーが発生するのを防ぎます。

セル E2 に入力する数式は次のとおりです。

=EXPAND(FILTER(T_Invoice,T_Invoice(Amount)>I2,""),ROWS(T_Invoice),,"-")

• フィルター セル I2 の条件に一致するデータを取得します。
• ROWS(T_請求書) として機能します 行 EXPAND 構文の引数を使用してソース テーブルの高さを動的に計算することで、結果カードが常に一致するようにします。
• 「-」 最後に、すべての空の行をダッシュ​​で埋めて、特別な書式設定を必要とせずに表示される「予約された」スペースを提供します。
• 「」 中央にあると、FILTER 自体が #CALC! をスローするのを防ぎます。 EXPAND が実行される前にエラーが発生します。

を入力する必要はありません 列 これは、数式がソース テーブルの 3 列の幅を自動的に採用するためです。

関連している

ROWS 関数を使用して Excel スプレッドシートをよりスマートにする 4 つの方法

ROWS 関数の構造力を活用して、堅牢で下位互換性のある Excel ワークブックを作成します。

ソース テーブルが大きくなるにつれて、結果カードが完全に整列して保護されるようにダッシュの数が自動的に調整されます。

EXPAND 機能のトラブルシューティング

最後に、Excel の EXPAND 関数を使用するときに発生する最も一般的なエラーの早見表を以下に示します。

EXPAND 関数を使用してレイアウトをロックすると、動的レポートでよく発生するアコーディオン効果を防ぐことができます。この構造の安定性と明確なパディング文字を組み合わせることで、結果が整理された状態に保たれ、流出エラーから保護されます。結局のところ、これは Microsoft Excel ダッシュボードを洗練され、プロフェッショナルで、予測可能なものにするための完璧な方法です。

OS

Windows、macOS、iPhone、iPad、Android

無料トライアル

1ヶ月

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