Excelで構造化参照を作成する方法は？
構造化参照はExcelテーブルから始まります。Excelで作成されたテーブルがすぐに、構造化された参照を自動的に作成します。
下の画像を見てください。
- ステップ1：リンクをB2として表示する代わりに、セルB3へのリンクをTable1 [@Sales]として表示しました。ここで、Table1はテーブルの名前であり、@ Salesは参照している列です。この列のすべてのセルは、テーブル名とそれに続く列見出し名で参照されます。
- ステップ2：テーブル名をData_Tableに変更し、列見出しをAmountに変更します。
- ステップ3：テーブル名を変更するには、テーブル内にカーソルを置き、[デザイン]> [テーブル名]に移動します。
- 手順4：テーブル名をData_Tableとして指定します。
- ステップ5：変更してB3セルへの参照を指定します。
したがって、構造化参照にはテーブル名と列名の2つの部分があることがわかりました。
例
この構造化参照Excelテンプレートはここからダウンロードできます–構造化参照Excelテンプレート例1
構造化参照を使用すると、数式を動的にすることができます。通常のセル参照とは異なり、データ範囲での追加と削除の場合に数式を有効にすることができます。
正常範囲とExcelテーブルの両方にSUM式を適用します。
正常範囲のSUM式。
ExcelテーブルのSUM式。
通常のテーブルとExcelテーブルの両方のデータに数行追加します。データに2つの広告申込情報を追加しましたが、違いがわかります。
Excelテーブルの構造化参照には更新された値が表示されますが、数式に手動で変更を加えない限り、正規データ範囲には更新された値は表示されません。
例2
ここで、もう1つの例を見てください。製品名、数量、価格の情報があります。この情報を使用して、売上高に到達する必要があります。
販売額を取得するための式は、数量*価格です。この式を表に適用してみましょう。
フォーミュラは[@QTY] * [@ PRICE]と言います。これは、B2 * C2の通常の参照よりも理解しやすいです。数式をテーブル内に配置している場合、テーブル名は取得されません。
Excelの構造化参照に関する問題
構造化参照を使用しているときに、以下に示すいくつかの問題に直面します。
問題＃1
構造化参照にも独自の問題があります。私たちは皆、Excelの数式を適用し、それを他の残りのセルにコピーまたはドラッグすることに精通しています。これは構造化参照の同じプロセスではなく、動作が少し異なります。
次に、以下の例を見てください。私は通常の範囲でExcelでSUM式を適用しました。
価格と販売額を合計したい場合は、現在の数式をコピーして貼り付けるか、他の2つのセルにドラッグするだけで、価格と販売額の合計値が表示されます。
次に、[数量]列のExcelテーブルに同じ式を適用します。
これで、Qty列の合計が得られました。通常の範囲と同様に、数式は現在の数式をコピーして[価格]列に貼り付け、価格の合計を取得します。
何てことだ！！！価格列の合計は表示されていませんが、数量列の合計のみが表示されています。したがって、この数式をコピーして隣接するセルや他のセルに貼り付けて、相対的な列または行を参照することはできません。
数式をドラッグして参照を変更します
これでその制限がわかりました。構造化参照を使用してコピーアンドペーストジョブを実行することはできなくなりました。では、どうすればこの制限を克服できますか？
解決策は非常に簡単で、コピーする代わりに数式をドラッグするだけです。数式セルを選択し、塗りつぶしハンドルを使用して残りの2つのセルにドラッグし、列の参照を価格と販売価格に変更します。
これで、それぞれの合計を取得するために数式が更新されました。
問題＃2
構造体参照に1つの問題があり、解決策も見つかりましたが、ここにもう1つ問題があります。数式を他のセルにドラッグしている場合、絶対参照として呼び出すことはできません。
以下の例を見てみましょう。複数のエントリを持つ販売テーブルがあり、ExcelのSUMIF関数を使用してデータを統合したいと思います。
次に、SUMIF関数を適用して、各製品の連結売上高を取得します。
1月の数式を適用しました。これは構造化された参照であるため、数式をコピーして残りの2つの列に貼り付けることはできず、参照を2月と3月に変更しないため、数式をドラッグします。
ああ！！2月と3月の列に値がありませんでした。何が問題になるのでしょうか？式をよく見てください。
1月から数式をドラッグしました。SUMIF関数 では、式をドラッグしたため、最初の引数はCriteria Range Sales_Table [Product]であり、Sales _Table [Jan]に変更されています。
では、どのように対処するのでしょうか？最初の引数、つまりProduct列を絶対列、その他の列を相対参照にする必要があります。通常の参照とは異なり、F4キーを使用して参照タイプを変更する余裕はありません。
解決策は、次の画像に示すように、参照列を複製する必要があることです。
これで、数式を他のリーマ2列にドラッグできます。基準範囲は一定であり、他の列参照はそれに応じて変更されます。
上級者向けのヒント： ROWを絶対参照として作成するには、二重のROWエントリを作成する必要がありますが、ROW名の前に@記号を付ける必要があります。
= Sales_Table [@ [Product]：[Product]]
Excelで構造化参照をオフにする方法は？
構造化参照のファンでない場合は、以下の手順に従ってオフにすることができます。
- ステップ1： [ファイル]> [オプション]に移動します。
- 手順2：[数式]> [数式でテーブル名を使用する]をオフにします。
覚えておくべきこと
- 構造化参照で絶対参照を作成するには、列名を2倍にする必要があります。
- 構造化参照の数式をコピーすることはできません。代わりに、数式をドラッグする必要があります。
- 構造化参照で参照しているセルを正確に確認することはできません。
- 構造化参照に興味がない場合は、それらをオフにすることができます。