「プロ」ダッシュボード エンジンを構築する方法

in tech

Excel でデータを手動で並べ替え、重複排除、フィルター処理するために何時間も無駄にする必要はありません。代わりに、FILTER、UNIQUE、SORTBY を組み合わせて、単一セルからすべての作業を実行し、更新する必要のない自動クリーニング データ エンジンを作成します。

OS

Windows、macOS、iPhone、iPad、Android

無料トライアル

1ヶ月

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


FILTER-UNIQUE-SORTBY トリオの仕組み

Excel でフィルタリング、重複排除、アルファベット順のリストを一度に作成するには、次の 3 つの関数をスタックする必要があります。

=SORTBY(
UNIQUE(FILTER(array,include,(if_empty))),
UNIQUE(FILTER(array,include,(if_empty))),
(sort_order)
)

ネストされた数式はすぐにテキストの壁になってしまう可能性があります。上に示した改行を作成するには、数式バーで Alt+Enter を押します。これによって数式の動作は変わりませんが、入力と監査がはるかに簡単になります。

一見すると、式の一部が繰り返されていることに気づくかもしれません。これは間違いではありません。このエンジンをエラーなしで動作させるには、ロジックをミラーリングする必要があります。なぜこれを行う必要があるのか​​については、以下のステップ 4 で詳しく説明します。

ネストされた数式のリストの上に関数記号があり、その横に Excel ロゴが表示されている図。

Excel の入れ子関数の初心者ガイド

複数の機能を同時に使用します。

ステップ 1. コア: フィルター

すべては、抽出する生データを識別するエンジン ルームである FILTER から始まります。

FILTER(array,include,(if_empty))
  • 配列 フィルタリングするセルまたはテーブルの範囲です。
  • 含む フィルターに何を保持するかを Excel に指示する基準です。
  • (空の場合) (オプション) では、一致するものが見つからなかった場合に Excel が表示する内容を指定します。

FILTER は、大規模で乱雑なテーブルを調べる代わりに、条件を満たす行のみを識別します。ノイズは無視され、関連するデータのみが次のステップに渡されます。さらに、標準のフィルター ボタンとは異なり、メイン テーブル内の行は非表示になりません。行は新しい場所に抽出されます。

ステップ 2. 中間層: ユニーク

次に、UNIQUE は「バウンサー」として機能し、受け取ったフィルター処理された配列からすべての重複を削除します。

UNIQUE(FILTER(...))

このトリオでは、FILTER 関数の結果全体が、UNIQUE が必要とする唯一の引数として機能します。このレイヤーが完了するまでに、すべての項目が 1 回だけ表示されるクリーンなリストが完成します。これは、元のテーブルに影響を与えず、ソースが変更された場合に手動で更新する必要がないため、重複の削除ツールを使用するよりも優れています。

ステップ 3. 外側のシェル: SORTBY

最後のステップは梱包です。 UNIQUE は重複を削除するのに優れていますが、データは元の順序で保持されます。そこで SORTBY が役に立ちます。

=SORTBY(array,sort_array,(sort_order))
  • 配列 前の 2 つのステップの結果です。
  • 並べ替え配列 Excel がリストを並べ替えるために使用するロジックです (以下の手順 4 を参照)。
  • (並べ替え順序) (オプション) Excel に並べ替えの方向を指示します。昇順 (AZ) の場合は 1 を、降順 (ZA) の場合は -1 を使用します。一方、デフォルト (昇順) をトリガーするには、空白のままにします。

SORT は基本的なリストには適していますが、2 つの理由から SORTBY が強力な選択肢となります。まず、UNIQUE や FILTER などの関数をスタックする場合、SORTBY は、データの構造を失うことなく、流出した結果をより適切に処理します。次に、SORT では最終結果の列でのみ並べ替えることができますが、SORTBY では結果には含まれていないまったく別の列に基づいてリストを並べ替えることができます。

Excel ロゴの横に '=SORT' 関数と '=SORTBY' 関数のラベルが付いて並べ替えられている 2 つの列を示す Microsoft Excel スプレッドシートの図。

Microsoft Excel の SORT と SORTBY: どちらを使用するべきですか?

Excel でデータを抽出して再配置するための最適な方法を選択してください。

ステップ 4. ミラーロジック

Excel には厳格なルールがあります。並べ替えるデータと並べ替えの基準は、正確に同じ高さでなければなりません。フィルター処理された一意のリストの高さが 5 行の場合、並べ替え命令も高さ 5 行である必要があります。したがって、元のソース テーブルを使用してこれら 5 つの名前を並べ替えようとすると、高さが一致せず、式が壊れてしまいます。

そのため、2 番目の引数として UNIQUE(FILTER(…)) チェーンを繰り返してロジックをミラーリングする必要があります。これにより、ディメンションが毎回完全に一致することが保証されます。

=SORTBY(
UNIQUE(FILTER(array,include,(if_empty))), <-- the data to sort 
UNIQUE(FILTER(array,include,(if_empty))), <-- the instructions to sort by 
(sort_order)
)

マジックトリオの活動: 顧客リスト

ブループリントを示したので、エンジンが実際に動作する様子を見てみましょう。 T_Sales という名前の Excel テーブルがあり、セル G2 で選択した地域の一意の顧客のアルファベット順のライブ リストが必要だと想像してください。

A 列に日付、B 列に企業、C 列に地域、D 列に金額を含む Excel テーブルと、データのフィルター、並べ替え、および重複除去が行われる右側の領域。

この数式をセル G4 に入力すると、数式の結果が以下のセルに反映されます。

=SORTBY(
UNIQUE(FILTER(T_Sales(Company),T_Sales(Region)=G2,"No Matches")),
UNIQUE(FILTER(T_Sales(Company),T_Sales(Region)=G2,"No Matches")),
1
)
Excel で SORTBY、UNIQUE、および FILTER を使用して、南部地域で取引のあるすべての企業を抽出します。

フィルターを数式にハードコーディングするのではなくセルを参照するということは、別の領域に簡単に切り替えることができることを意味します。また、他の人がエンジンを理解しやすくなります。SORTBY の仕組みを知る必要はありません。セル G2 のドロップダウン リストから領域を入力するか選択する方法を知っていれば十分です。

元のデータセットは Excel テーブル内にあるため、データ エンジンは将来も保証されます。テーブルの最後に販売データの新しい行を追加すると、数式は拡張された範囲を自動的に検出します。これは、数式を入力したセルから動的配列を書き出す関数を使用する利点の 1 つです。さらに、ハッシュ (#) (スピル範囲演算子とも呼ばれる) を参照に追加するだけで、結果全体を別の場所で参照できます。

G4#
Microsoft Excel アプリがインストールされたラップトップ。

Excel の構造化参照について知っておくべきことすべて

セル参照の代わりにテーブル名と列名を使用します。

避けるべき最後の落とし穴

新しいデータ エンジンをスムーズに実行し続けるには、次の 3 つのルールに留意してください。

  • パスをクリアします。 動的配列数式には、結果を下に書き出すための空のスペースが必要です。はみ出したテキストや別の表が邪魔になると、Excel は #SPILL! をスローします。エラー。
  • テーブル内に数式はありません: ソース データとしてテーブルを使用する必要がありますが、数式は通常のセルに配置する必要があります。これは、Excel テーブルが動的スピルをサポートしていないためです。
  • 1 列のバッファーを残します。 Excel テーブルの外でデータを分析する場合は、テーブルと入力している場所の間に常に 1 列の隙間を空けてください。そうしないと、Excel はテーブルにさらにデータを追加しているとみなして、分析を「取得」してしまいます。

手動の Excel ツールから離れ、動的配列を採用することで、データとともに成長するシステムを構築できます。このトリオは、日常業務をさらに自動化できる他の Excel 関数の組み合わせ (INDEX と XMATCH、IF と AND および OR、EOMONTH と SEQUENCE など) を検討するための完璧な出発点です。

関連記事

前の投稿
このJBLパーティースピーカーは現在600ドルでセール中です
次の投稿
すべての電気技師が信頼するミルウォーキーのツール

関連記事