Microsoft Excel で構造化参照を使用する 4 つの方法

Excel での作業は通常、さまざまなデータ ポイント間の接続を見つけることを中心に展開します。ただし、複雑な数式を挿入する場合、相対および絶対の明示的なセル参照 (「B7」またはそのバリエーションなど) を繰り返し使用しても、数式バーが混乱して読めなくなるまでには限界があります。

Excel の構造化参照を使用すると、テーブルとそのヘッダーに名前を割り当てることで作業を効率化できます。これらの名前は暗黙的なセル参照として使用できるため、Excel は構造化データを自動的に取得して計算できます。

ここでは、Excel で構造化参照を使用する最も一般的な方法をいくつか紹介します。

1.テーブル内の計算

構造化参照はテーブル上でのみ機能するため、構造化参照を利用する最良の方法は、同じテーブル内で使用することです。

たとえば、店舗の売上データを含む B2 から F8 までの単純なテーブルを作成します。テーブルに「Sales」という名前を付けたことに注意してください (左上の「テーブル名」を参照)。

各売上の合計を計算してみましょう:

ステップ 1: F2 をクリックします (ただし、ドロップダウン アイコンはクリックしません)。 「ホーム」に移動し、「挿入」に移動し、「表の列を右側に挿入」を選択します。これにより、テーブルに新しい列が自動的に追加されます。

ステップ 2: 列 G ヘッダーに「Total」という名前を付けます。

ステップ 3: G3 で、=[@PricePerUnit]*[@Quantity] を挿入して Enter キーを押します。必要に応じてセル出力をフォーマットします。

「[@PricePerUnit]」と「[@Quantity]」は、これらの列の対応するフィールドへの参照です。列名の前の「@」引数は、各結果セルが同じテーブル行からの参照を使用することを意味します。

翻訳すると、G3 の式 =[@PricePerUnit]*[@Quantity] は、=$C3*$D3 を記述することと本質的に同じです。

2.テーブルの外の範囲を取得する

テーブルの外のセルで構造化参照を使用する場合は、参照の前に TableName を付ける必要があります。前の例では、「Sales[Total]」を使用すると、テーブル「Sales」のヘッダー「Total」の下の範囲全体が取得されます。これは、操作できる配列内の複数の値を取得できることを意味します。

範囲がこぼれるのに十分な余地を残した場合、Excel 内のセル I3 は次のようになります。

3.列の合計と部分的な合計

列全体をすばやく合計するには、[テーブル デザイン] オプション ([テーブル スタイル オプション] の下) で [合計行] チェックマークを使用できます。以下は、「数量」列と「合計」列の合計を取得する例です。

「合計」行自体は移動できず、テーブルの最後に配置されます (挿入が可能) が、その結果を別の場所に複製することができます。

• 「合計」列のすべての行の合計を取得するには、=SUM(Sales[Total]) を使用します。
• テーブルをフィルタリングした後など、表示されている列の合計のみを取得したい場合は、=SUBTOTAL(109,Sales[Total]) を使用します。この数式は、表形式の「合計行」オプションがその行で実際に行うことです。

テーブル内で見つかった特定の変数に基づいて、書式設定せずに部分合計を取得することもできます。例:

• Mike によるすべての売上の合計を取得するには、=SUMIF(Sales[Seller],”Mike”,Sales[Total]) を使用できます。式中の「Mike」は手動で入力した文字列です。
• ID「41230」のすべての商品の合計を取得するには、次の=SUMIF(Sales[ProductID],41230,Sales[Total])を使用します。なお、ProductID列は「一般」形式のため、数値を直接入力できます。

4. INDIRECT

によるテーブルからのデータ検証

以前に使用した Sales テーブルがあるとします。カスタムのデータ検証オプションを作成して、テーブル内の検索を容易にすることができます。 ProductID、日付、または販売者のいずれかを選択できる小さなテーブルを作成し、それらのサブセットから任意の 1 つのアイテムを選択して小計を表示しましょう。

ステップ 1: セル B13 で、データ検証を作成します ([データ] タブ > [データ ツール] > [データ検証])。

ステップ 2: ポップアップで、「許可」オプションから「リスト」を選択し、「ソース」ボックスに列の値をカンマで区切って手動で挿入します。この場合、「ProductID、販売者、日付」を挿入しました。

ステップ 3: セル C13 で、別のデータ検証を作成します。もう一度「リスト」を選択します。 「ソース」に次の式を挿入します: =INDIRECT("Sales["&B13&"]").

ステップ 4: セル D13 で、次の数式を使用します: =SUMIF(INDIRECT("Sales["&B13&"]"),B14,Sales[Total]).

2 つのデータ検証リストからオプションを選択できるようになり、小計が D13 に表示されます。

以上がMicrosoft Excel で構造化参照を使用する 4 つの方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。