スプレッドシートを変換する:5 つの動的配列関数 (FILTER、UNIQUE、SORT)

 

動的配列関数は、最も便利で強力な機能の 1 つです。何百行もコピーする複雑な数式を作成する代わりに、必要な数のセルに結果を自動的に「注ぎ込む」1 つの数式を作成します。データが変更されると、これらの関数はリアルタイムで更新されます。これにより、レポートと概要がより効率的になり、エラーが発生しにくくなります。

このチュートリアルでは、動的配列関数 (FILTER、UNIQUE、SORT) が作業方法を変える 5 つの方法を示します。

Excel での動的配列とスピル

動的配列: セルに数式を挿入すると、Excel によって結果が隣接するセルに自動的に入力または追加されます。結果のサイズが変化すると (行が増えたり、行が減ったり)、こぼれた範囲は自動的に拡大または縮小します。こぼれた範囲は次の理由で認識できます。

• 数式は左上のセルにのみ存在します。
• 他のセルには明るい境界線が表示され、それらをクリックすると数式がグレー表示されます。
• こぼれた範囲全体を A2# (ハッシュ記号) として参照できます。

動的配列は、Microsoft 365 (Microsoft 365 用 Excel)、Excel 2021 以降で使用できます。

1.重複のない一意の要約リストを生成

動的配列が登場する前は、重複を削除するには「重複の削除」または複雑な数式が必要でした。 UNIQUE は、簡単に要約できる、流出可能な一意のリストを作成します。ドロップダウン、検証リスト、ピボットフリーのダッシュボードに最適です。
一意の地域をリストする:

• セルを選択し、次の数式を挿入します。

この式により、固有の製品のリストが得られます。

ユニークな組み合わせ:
地域と営業担当者の固有の組み合わせを取得します。

これにより、すべての一意の組み合わせを示す 2 列のスピルが返されます。

ユニークな注文の数:

• UNIQUE と COUNTA を組み合わせて要約します。

この式は、一意の注文の合計数を計算します。データセットに新しい行を追加すると、リストが自動的に更新されます。

一度だけ出現する値:
1 回だけ出現する値を検索します。

3 番目の引数 (TRUE) ) は繰り返されない値のみを返します。

データ検証に UNIQUE を使用する (ドロップダウン):

<オル>

ドロップダウンを表示するセルを選択します。

データ に移動します。 タブ>> データ検証を選択します .

許可を設定します リストへ .

ソース内 、次のように入力します。

これで、ドロップダウンには、データに基づいて現在の一意のリージョンが常に表示されるようになりました。
UNIQUE を使用したグループ化された概要:
UNIQUE と SUMIF を組み合わせて、動的なグループ化されたサマリーを作成できます。
さまざまな地域の総収益を計算します。

=SUMIF(B2:B61, I15#, G2:G61)

この式は、流出領域 (I15#) のリスト全体を参照します。 ）。 SUMIF は、各エリアの総収益を返します。 Sales の行を追加または変更すると、地域と合計が自動的に調整されます。

ここで地域を変更すると、選択に基づいて総収益が自動的に更新されます。

2.動的レポート用にデータを自動的にフィルタリングする

従来のフィルタリングには、手動フィルタまたは複雑な数式が必要です。 FILTER() 関数は、動的配列用の最も強力なツールの 1 つです。条件を満たす行のみを抽出し、結果を表のような範囲にまとめます。この機能を使用すると、即座に更新されるレポートを作成できます。
地域ごとに売上をフィルタリング:

• FILTER 関数の動的な動作を表示するには、領域のドロップダウンを使用します。

=FILTER(A2:G61, B2:B61="East")

• より動的にするには、ドロップダウンから基準セルを参照します。

=FILTER(A2:G61, B2:B61=I4)

これにより、地域が「東」であるすべての行が除外されます。データを追加または削除すると拡大または縮小するミニレポートです。 I4 を変更します 「北」に設定すると、レポートが自動的に更新されます。VBA や手動更新は必要ありません。

これにより、データの静的コピーが排除されます。レポートには常にソースが反映されます。
複数の基準:
東部地域および金額が 1,000 ドルを超えるフィルター:

=FILTER(A2:G61, (B2:B61="East")*(G2:G61>1000), "No matches")

アスタリスク (*) ) は AND として機能します。プラス (+) を使用できます。 ) OR 条件の場合。
ユーザーがドロップダウンから条件を選択する対話型ダッシュボードを構築できます。

3. SORT() と SORTBY() を使用して自動的に並べ替えられたリストを作成する

並べ替えとは、かつてはデータのコピーやテーブルの使用を意味していました。 SORT は、動的で流出可能な並べ替えられたビューを作成します。データセットを追加、削除、または更新するたびに、データは自動的に並べ替えられます。
自動並べ替えられた売上ランキング:

この数式は、データ範囲全体を列 7 (金額) で降順 (-1) に並べ替えます。 ）。元のデータはそのまま残ります。新しいトップセールを追加すると、自動的に正しい位置に表示されます。

複数の並べ替えレベル:
営業担当者順に並べ替え、次に金額順に並べ替えます:

=SORT(A2:G61, {3,7}, {1,-1})

中括弧は配列を作成します。列 3 を昇順に並べ替え、次に列 7 を降順に並べ替えます。

さまざまな基準で並べ替え:
SORTBY 関数を使用すると、ある範囲を別の範囲の値に基づいて並べ替えることができます。
すべての列を保持したまま、範囲全体を営業担当者名で並べ替えます。

=SORTBY(A2:G61, C2:C61, 1)

4. FILTER と UNIQUE を組み合わせて、対象を絞った一意の概要を作成します

高度な要約の場合は、関数を組み合わせて最初にフィルターし、次に一意化して、きれいな自動更新リストを作成できます。関数を組み合わせて、それ自体を維持するレポートを作成します。

• セルを選択し、次の数式を挿入します。

=UNIQUE(FILTER(D2:D61, B2:B61="North"))

これにより、北部地域の製品がフィルタリングされ、独自の製品が流出します。

• 次に、SORT 関数を追加して概要を並べ替えます。

=SORT(UNIQUE(FILTER(D2:D61, B2:B61="North")))

この式により、ソートされた固有の製品リストが出力されます。

これにより、一意のフィルター処理されたリストの {=INDEX(…)} などの面倒な配列式が置き換えられます。データや基準を変更すると、地域の製品在庫などのレポートの更新がシームレスに反映されます。

5.動的な条件主導の概要ページ (フィルター、ユニーク、ソートの組み合わせ)

次に、これらの関数を組み合わせて、いくつかの基準セルから更新されるミニ概要/レポート ページを作成します。
地域レベルのダッシュボードを構築します。地域ドロップダウン (UNIQUE によって駆動)、フィルターされた地域リスト (FILTER)、および地域別上位製品リスト (FILTER + SORT) です。

ステップ 1:UNIQUE を使用した地域ドロップダウン

一意のリージョンのリストが作成され、ドロップダウンの構築に使用されています。

ステップ 2:地域レベルの販売の詳細

=FILTER(SalesData!A2:G61, SalesData!B2:B61=B4, "No sales in this region")

この数式は、地域に基づいて売上データをフィルタリングします。ドロップダウンから地域を変更すると、売上表が自動的に更新されます。

ステップ 3:選択した地域の人気商品

選択した地域で最も売れている商品を特定します。

• ヘッダーに「Product」と「Total Revenue」を含む小さなテーブルを作成します。
• L4 で、選択した地域で販売されている固有の商品を取得します。

=UNIQUE(FILTER(SalesData!D2:D61, SalesData!B2:B61=B4))

これにより、その地域の製品のリストが流出します。

• M4 で、その地域の製品ごとの総収益を計算します。

=SUMIFS(SalesData!G2:G61, SalesData!B2:B61, B4, SalesData!D2:D61, L4#)

これにより、L4# の各製品に一致する総収益の流出リストが返されます。 。

• 収益順 (降順) で並べ替えて表示するには、こぼれた 2 つの列を一緒に並べ替えます。

=SORT(CHOOSE({1,2}, L4#, M4#), 2, -1)

ここでは、CHOOSE({1,2}, L4#, M4#) 2 列の配列 (製品と総収益) を構築します。 2 「2 列目 (総収入) で並べ替える」を意味します。 -1 降順を意味します。

[選択した地域] レポートの動的トップ商品:

• B4 の地域ドロップダウンを変更します。すべての概要が更新されることに注意してください。
• 新しいデータを Sales に追加すると、レポートに追加されます。
• 数式のコピー、手動並べ替え、ピボットテーブルの更新は不要です。

結論

このチュートリアルでは、動的配列関数 (FILTER、UNIQUE、SORT) が作業方法を変える 5 つの方法を示します。動的配列関数により、スプレッドシートを保守するという忙しい作業が不要になります。数式をコピーしたり、壊れた参照を修正したりするのではなく、分析に集中します。レポートは自動的に更新されます。ダッシュボードは自動的に更新されます。これらの関数を使い始めると、サマリーやダッシュボードに最適であることがわかります。手動で更新したり、複雑な配列数式を使用したり、ヘルパー列を使用したりすることなく、即座に更新されるレポートを作成できます。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!