データ範囲が増減しても参照先が自動で変動する「名前の管理」の設定（Excel）

【本日のミッション】

Excel にて行と列（縦と横）のデータ範囲が増減しても、 参照先が自動で変動する「名前の管理」を設定せよ！

ミッションの概要

表の右に「項目A」「項目B」「項目C」「項目D」・・・と増えても、表の下に「A7」「A8」「A9」・・・と増えても、「名前の管理」の参照先を変更しなくてもよい設定にせよ、というのが今回のミッションです。

「名前の管理」の設定

今回「名前の管理」を設定するのは、下記の４つです。

• 「項目A」「項目B」「項目C」・・・に設定する「項目」
• B列のデータ部・・・に設定する「項目A」
• C列のデータ部・・・に設定する「項目B」
• D列のデータ部・・・に設定する「項目C」

[数式]－[定義された名前]－[名前の定義」をクリックします。

左上の[新規作成]ボタンをクリックし、下記の通り4種類入力します。

この「項目」の数式の示すデータ範囲ですが

セル「①名前の管理!$B$3」から

「②0」行、「③0」列、移動したセルを基準に（今回の場合は移動しないのでセルB3から）
「④1」行×「⑤COUNTA(名前の管理!$3:$3)」列の範囲（今回であればセルB3～D3）ということになります。

「⑤COUNTA(名前の管理!$3:$3)」は、3行目のデータの件数なので、「3」列になります。

注意しなくてはいけないのは、数式のCOUNTで参照している行や列には関係の無いデータを入れてはいけないということです。関係のないデータの数もカウントしてしまい、結果がおかしくなります。また、リストに空白セルが含まれるのも結果がおかしくなる原因になるので、データ範囲は空白を含めず詰めておきましょう。

もう一ついってみましょう！「項目A」の数式です。

セル「①名前の管理!$B$4」から

「②0」行、「③0」列、移動したセルを基準に（今回の場合は移動しないのでセルB4から）

「④COUNTA(名前の管理!$B:$B)-1」行×「⑤1」列の範囲(今回であればB4～B10)ということになります。

「④COUNTA(名前の管理!$B:$B)-1」で「-1」としているのは、B列のデータをカウントすると8件になりますが、セルB3のデータ分をカウントしないためです。

これらの設定をあらかじめしておくだけで、参照範囲が増減しても「名前の管理」の設定を修正しなくて済みます。

参照

「数字が文字列として保存されています。」を一括で数字に変換
データ範囲が増減しても参照先が自動で変動する「名前の管理」の設定（Excel）
連動するドロップダウンリストを名前の管理・入力規制・OFFSETを使って「リストが表示されない」を解決（Excel）
Excel 複数セル内の改行（Alt+Enter）を一括で解除する方法
Excel関数を使って 縦の表に連動した（リンクした）横の表を一括で作成せよ
ワイルドカードとは。使い方いろいろ。（Excel）
Excelの改ページの点線を消す方法
Excelの背景にある薄グレーの枠線（罫線）を消す（非表示）方法
非表示になってしまった列を表示させる方法
ファイルを開くと空の画面も同時に開くのを解決（Excel）
図 画像 グラフ ボタン などのオブジェクトが消えてしまった時の表示方法（Excel）
大量の画像やグラフ等のオブジェクトで画面表示が遅いのを解決（Excel）
保護されたシートに対して、このコマンドは使用できません。テーマが変えられない現象を解決（Excel）
離れた複数列をまとめて列番号（数字）で選択する方法（Excel VBA）
Excel VBA で選択した複数のセルにデータ・数式を（Ctrl+Enter のように）一気に入力する方法