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


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



じゃあ、この範囲「売上」を自動で拡大させるってこと?
そう!このままだと、例えばくだものにキウイを追加しても、参照や合計値は固定されて変わらないわ。自動で拡大したら便利よね。



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



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



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


1-1 手順

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

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



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


    とりあえずE3を範囲に指定しましょう。E3範囲が自動拡大する関数を組み込めば、これが範囲の代わりになるわ。
    ※ 関数挿入用なので、空きセルであればどこでも構いません。



    2-2 範囲が自動拡大する関数とは


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

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


    すごい!値段が追加されるのと同時に、範囲も自動で拡大してる!
    次はこの関数のつくりかたをみていきましょう。


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


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


    3-1 セルに値段があるならTRUEを表示させる


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

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

    値段があるセルにはTURE(はい)が表示されました!



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

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


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

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



    3-2 TRUEを数字表示に変える


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

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

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


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

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



    3-3 Match関数で、一番最後の「1」の行数を知る


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


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

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

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


    じゃあ、Match関数の本来の使い方をしていないってこと?
    そうね笑 検索範囲は実質全部「1」だし、その中から検索値「1」を探すなんて、正直、Match関数は「?」状態でしょうね..。ともあれ、実際に使ってみましょう。



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

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

    追加の値段に応じて、最終行が自動で拡大していますね!


    Step # 4: 完成


    後は、先頭に参照用の「”‘シート1’!C3:C”&」を付け加えるだけです!

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

    E3にこの関数を挿入後、名前付き範囲「売上」に指定。

    これで、E3を経由して、自動拡大範囲を参照させることができますね。

    一方、セル上の情報を参照させるには、あの関数を使う必要がありました。(そのまま「売上」を使うと、単純に「E3」自身を示すので、「E3」の合計や参照になってしまいます…。)

    そう、Indirect関数です!

    合計は、=Sum(Indirect(売上))

    参照には、=Indirect(売上)のように、Indirect関数を指定すれば

    売上→E3→その中身(‘シート1’!C3:C10)の合計・参照を実現できます。

    これで、データが追加されると自動で拡大する名前付き範囲、プラスその活用方法(合計・データ参照)は全て完了です!

    Indirect関数に関しては、以下を参考にしてくださいね!

    【スプレッドシート】INDIRECT|セル上の情報(セル番・シート名)を介して、別のセル・シートを参照(#動的参照)

    2020.01.23

    お疲れ様でした!