サイズを変更できる Excel のデータを含むテーブルがありますか。つまり、作業の過程で行 (列) の数が増減する可能性がありますか? テーブルのサイズが「フローティング」の場合は、この瞬間を常に監視して修正する必要があります。
- テーブルを参照するレポート式のリンク
- テーブルに従って作成されたピボット テーブルの初期範囲
- 表に従って作成されたチャートの初期範囲
- テーブルをデータ ソースとして使用するドロップダウンの範囲
これらすべてを合計すると、退屈することはありません 😉
データの実際の行数と列数に合わせてサイズが自動的に調整される動的な「ラバー」範囲を作成する方が、はるかに便利で正確です。 これを実装するには、いくつかの方法があります。
方法 1. スマート テーブル
セルの範囲を強調表示し、タブから選択します ホーム – 表としてフォーマット (ホーム – 表としてフォーマット):
副作用としてテーブルに追加された縞模様のデザインが必要ない場合は、表示されるタブでオフにすることができます コンストラクター (デザイン). この方法で作成された各テーブルには、タブの同じ場所でより便利な名前に置き換えることができる名前が付けられます コンストラクター (デザイン) 現場で テーブル名 (テーブル名).
これで、「スマート テーブル」への動的リンクを使用できるようになりました。
- テーブル1 – ヘッダー行 (A2:D5) を除くテーブル全体へのリンク
- 表1[#すべて] – テーブル全体へのリンク (A1:D5)
- 表1[ピーター] – 最初のセル ヘッダーのない範囲列への参照 (C2:C5)
- Table1[#ヘッダー] – 列の名前を含む「ヘッダー」へのリンク (A1:D1)
このような参照は、次のような数式でうまく機能します。
= SUM（表1【モスクワ】) – 列「モスクワ」の合計の計算
or
=VPR(F5;テーブル1;3;0) – テーブルでセル F5 から月を検索し、サンクトペテルブルクの合計を発行します (VLOOKUP とは何ですか?)
このようなリンクは、タブで選択してピボット テーブルを作成するときに使用できます。 挿入 – ピボット テーブル (挿入 – ピボット テーブル) スマート テーブルの名前をデータ ソースとして入力します。
このようなテーブルのフラグメント (たとえば、最初の XNUMX 列) を選択して任意のタイプの図を作成すると、新しい行を追加すると、自動的に図に追加されます。
ドロップダウン リストを作成する場合、スマート テーブル要素への直接リンクは使用できませんが、戦術的なトリックを使用してこの制限を簡単に回避できます。 間接 （間接）、テキストをリンクに変換します。
それらの。 テキスト文字列 (引用符で囲まれています!) の形式のスマート テーブルへのリンクは、本格的なリンクに変わり、ドロップダウン リストは通常それを認識します。
方法 2: 動的な名前付き範囲
なんらかの理由でデータをスマート テーブルに変換することが望ましくない場合は、もう少し複雑ですが、はるかに巧妙で用途の広い方法を使用できます。テーブルを参照する動的な名前付き範囲を Excel で作成します。 次に、スマート テーブルの場合と同様に、作成した範囲の名前を数式、レポート、グラフなどで自由に使用できます。簡単な例から始めましょう。
仕事: 都市のリストを参照する動的な名前付き範囲を作成し、新しい都市を追加または削除するときに自動的にサイズを拡大および縮小します。
どのバージョンでも利用可能なXNUMXつの組み込みExcel関数が必要になります- ポイポス （マッチ） 範囲の最後のセルを決定し、 INDEX （索引） 動的リンクを作成します。
MATCH を使用して最後のセルを見つける
MATCH(ルックアップ値、範囲、一致タイプ) – 範囲 (行または列) 内の特定の値を検索し、見つかったセルの序数を返す関数。 たとえば、数式 MATCH("March";A1:A5;0) は、結果として数値 4 を返します。これは、"March" という単語が A1:A5 列の 0 番目のセルにあるためです。 関数の最後の引数 Match_Type = XNUMX は、完全一致を探していることを意味します。 この引数が指定されていない場合、関数は最も近い最小値の検索モードに切り替わります。これはまさに、配列内の最後に占有されたセルを見つけるために使用できるものです。
トリックの本質は単純です。 MATCH は上から下までの範囲のセルを検索し、理論的には、指定された値に最も近い最小値が見つかったときに停止する必要があります。 テーブルで使用可能な値よりも明らかに大きい値を目的の値として指定すると、MATCH はテーブルの最後に到達し、何も検出せず、最後に入力されたセルのシーケンス番号を返します。 そして、私たちはそれを必要としています！
配列に数値しかない場合は、目的の値として数値を指定できます。これは、表のどの値よりも明らかに大きいです。
保証のために、数値 9E + 307 (9 の 10 の 307 乗、つまり 9 の 307 個のゼロ) を使用できます。これは、原則として Excel で処理できる最大数です。
列にテキスト値がある場合、可能な最大数に相当するものとして、構造 REPEAT(“i”, 255) – 255 文字からなるテキスト文字列 “i” – の最後の文字を挿入できます。アルファベット。 Excel は検索時に実際に文字コードを比較するため、テーブル内のテキストは技術的には、このような長い「yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy」行よりも「小さく」なります。
INDEX を使用してリンクを生成する
テーブル内の最後の空でない要素の位置がわかったので、範囲全体へのリンクを形成する必要があります。 このために、次の関数を使用します。
INDEX(範囲; 行番号; 列番号)
行番号と列番号で範囲からセルのコンテンツを取得します。つまり、たとえば、前のメソッドの都市と月を含む表の関数 =INDEX(A1:D5;3;4) は 1240 を取得します – コンテンツ3 番目の行と 4 番目の列、つまりセル D3 から。 列が 2 つしかない場合は、その番号を省略できます。つまり、式 INDEX(A6:A3;XNUMX) により、最後のスクリーンショットで「Samara」が得られます。
そして、完全に明白ではないニュアンスが 2 つあります。通常のように、INDEX が = 記号の後のセルに入力されているだけでなく、コロンの後の範囲への参照の最後の部分として使用されている場合、それはもはや与えられません。セルの内容ですが、そのアドレスです! したがって、$A$2:INDEX($A$100:$A$3;2) のような数式は、出力で範囲 A4:AXNUMX への参照を提供します。
そして、これが MATCH 関数の出番です。これを INDEX 内に挿入して、リストの末尾を動的に決定します。
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“私”;255);A2:A100))
名前付き範囲を作成する
それをすべてXNUMXつの全体に詰め込むことは残っています。 タブを開く 式 (数式) そして、 名前管理者 (ネームマネージャー). 開いたウィンドウで、 ボタンをクリックします。 創造する （新しい）、フィールドに範囲名と数式を入力します レンジ （参照）:
クリックする必要があります OK また、準備完了範囲は、任意の数式、ドロップダウン リスト、またはチャートで使用できます。
- VLOOKUP 関数を使用してテーブルとルックアップ値をリンクする
- 自動入力ドロップダウン リストを作成する方法
- 大量のデータを分析するためのピボット テーブルの作成方法