.png?w=728&ssl=1)


.png?w=728&ssl=1)


「名前付き範囲」のメリットは、面倒な大きなデータも名前の入力だけで済み、時間の節約になるところです。一方、追加データがあると、範囲を再指定する必要があり、不便でした。
以下では、追加データに応じて、自動拡大する名前付き範囲を作っていきましょう!
目次
Step # 1:名前付き範囲のつくりかた
1-1 手順

- C3:C9を選択
- 右クリック→「名前付き範囲を定義」
- 名前を「売上」に設定


Step # 2:どうやって拡大させる?


2-2 範囲が自動拡大する関数とは
以下が自動拡大する関数です。E3セルに入力しましょう。

.png?w=728&ssl=1)

Step # 3: 範囲が自動拡大する関数のつくりかた


3-1 セルに値段があるならTRUEを表示させる
「1/」の部分を無くして、よりシンプルな状態で試してみましょう。

値段があるセルにはTURE(はい)が表示されました!
つまりは、一気処理のサインのこと
従来個別に処理していた関数を、範囲処理として1セルへの入力で済ましたい時、ArrayFormulaを先頭につけることによって「今回は一度に処理するよ」という合図を関数全体に送る必要があります。
英語と一緒で、後ろから読んでいくイメージです。
3-2 TRUEを数字表示に変える
TRUEは計算式中で、「1」としてみなされます。これを利用して、「1/TRUE(1)=1」としましょう。次のMatch関数でこの作業が生きてきます。

一方、値段の無いセルには「#DIV/0!」というエラーが表示されています。これはFALSEが「0」を意味するためで、「1/0」は計算できません、という意味です。


次は、Match関数の中で、上の関数がどう働くかみていきましょう!
3-3 Match関数で、一番最後の「1」の行数を知る
Match関数は、「検索する範囲」から「検索値」を「検索方法」に従って検索し、あればその行数を上から数えて何番目かで返します。

「検索方法」に関しては、以下のルールを参考にしてください。
0 | 検索値と同じ値を検索。 |
1 | 検索値以下の近似値を検索。(※検索範囲は昇順で整列させておくこと。検索範囲の値が全て同じ場合は昇順の最後の値の行を返す。) |
-1 | 検索値以上の近似値を検索。(※検索範囲は降順で整列させておくこと。検索範囲の値が全て同じ場合は降順の最初の行数を返す。) |
今回の検索方法は1ですね。つまり、検索値「1」を検索範囲(値段があるセルまで全て1。それ以後はエラーのため検索不可。)から近似値検索するも、検索範囲は全て「1」なので、昇順最後の「1」の行数を返します。
.png?w=728&ssl=1)

以下のMatch関数をE3セルに入力すると..

追加の値段に応じて、最終行が自動で拡大していますね!
Step # 4: 完成
後は、先頭に参照用の「”‘シート1’!C3:C”&」を付け加えるだけです!
E3にこの関数を挿入後、名前付き範囲「売上」に指定。

これで、E3を経由して、自動拡大範囲を参照させることができますね。
一方、セル上の情報を参照させるには、あの関数を使う必要がありました。(そのまま「売上」を使うと、単純に「E3」自身を示すので、「E3」の合計や参照になってしまいます…。)
そう、Indirect関数です!
合計は、=Sum(Indirect(売上))
参照には、=Indirect(売上)のように、Indirect関数を指定すれば
売上→E3→その中身(‘シート1’!C3:C10)の合計・参照を実現できます。

これで、データが追加されると自動で拡大する名前付き範囲、プラスその活用方法(合計・データ参照)は全て完了です!
Indirect関数に関しては、以下を参考にしてくださいね!
お疲れ様でした!