【スプレッドシート】名前付き範囲を、内容の追加に応じて自動拡大させる方法

名前付き範囲ってなんだっけ？

ある範囲に、名前をつけることね。その範囲の参照や合計が、名前でできるようになるわ。

じゃあ、この範囲「売上」を自動で拡大させるってこと？

そう！このままだと、例えばくだものにキウイを追加しても、参照や合計値は固定されて変わらないわ。自動で拡大したら便利よね。

「名前付き範囲」のメリットは、面倒な大きなデータも名前の入力だけで済み、時間の節約になるところです。一方、追加データがあると、範囲を再指定する必要があり、不便でした。

以下では、追加データに応じて、自動拡大する名前付き範囲を作っていきましょう！

Step # 1：名前付き範囲のつくりかた

　C3:C9を選択

　右クリック→「名前付き範囲を定義」

　名前を「売上」に設定

これが、通常の名前付き範囲の指定方法よ。今回は、ここをデータ追加に応じて拡大させないと。

とりあえずE3を範囲に指定しましょう。E3に範囲が自動拡大する関数を組み込めば、これが範囲の代わりになるわ。

※ 関数挿入用なので、空きセルであればどこでも構いません。

以下が自動拡大する関数です。E3セルに入力しましょう。

E3 = “‘シート1’!C3:C”&Match(1,ArrayFormula(1/(C3:C<>“”)),1)

すごい！値段が追加されるのと同時に、範囲も自動で拡大してる！

次はこの関数のつくりかたをみていきましょう。

１つずつ分解して、理解していくのがいいわ。まずは、ここからよ。

「1/」の部分を無くして、よりシンプルな状態で試してみましょう。

E3 = ArrayFormula(C3:C<>“”)

値段があるセルにはTURE（はい）が表示されました！

　ArrayFormula関数とは

つまりは、一気処理のサインのこと

従来個別に処理していた関数を、範囲処理として1セルへの入力で済ましたい時、ArrayFormulaを先頭につけることによって「今回は一度に処理するよ」という合図を関数全体に送る必要があります。

　C3:C<>“”のつかいかた

C3:C<>“”　＝　空欄 (“”) ではない( <>) C3以下のC列のセル

英語と一緒で、後ろから読んでいくイメージです。

TRUEは計算式中で、「1」としてみなされます。これを利用して、「1/TRUE(1)=1」としましょう。次のMatch関数でこの作業が生きてきます。

E3 =ArrayFormula(1/(C3:C<>“”))

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

これで、ここが値段のあるセルまで「1」を返すことがわかったわね。

次は、Match関数の中で、上の関数がどう働くかみていきましょう！

Match関数は、「検索する範囲」から「検索値」を「検索方法」に従って検索し、あればその行数を上から数えて何番目かで返します。

「検索方法」に関しては、以下のルールを参考にしてください。

0	検索値と同じ値を検索。
1	検索値以下の近似値を検索。（※検索範囲は昇順で整列させておくこと。検索範囲の値が全て同じ場合は昇順の最後の値の行を返す。）
-1	検索値以上の近似値を検索。（※検索範囲は降順で整列させておくこと。検索範囲の値が全て同じ場合は降順の最初の行数を返す。）

今回の検索方法は1ですね。つまり、検索値「１」を検索範囲（値段があるセルまで全て1。それ以後はエラーのため検索不可。）から近似値検索するも、検索範囲は全て「1」なので、昇順最後の「1」の行数を返します。