こちらの記事では新しいExcelで追加になる計算方法、「スピル」について解説しました。
新しいExcelの一番の変更点は「スピル」？
とてもインパクトのある機能でして、多くの皆さんにご覧いただき知っていたけて、とてもうれしかったです。
ただ、インパクトのある機能ではあるものの、どのようなケースで使えるか、具体的にどんな業務に使えるかということまではなかなか繋がりにくいと思います。
夢でまでスピルが出てくるほど考えていまして、そんな中で、これはスピルでしかできないという有効活用方法を見つけましたので、お知らせいたします。
具体的には計算式作成の時短・効率化に役立ちます。

時間と単価と基本料金の表

今回紹介するケースはこのような、貸し部屋の料金表のケースです。

４つの表があります。
それぞれの表の左上は「基本料金」です。短時間使ってもこの料金はかかります。
それぞれの横軸は「単価」、縦軸は「利用時間」となっています。
料金は基本料金＋単価×利用時間で求まります。

従来での効率的な計算プロセス

従来のExcelで最もこの表の黄色のセル範囲に計算する方法は、セルB2に次の計算式を作成します。
=$A$1+B$1*$A2
そして、その計算式を縦横にコピーし、1つの表を作成します。
次にA6からの表の範囲に計算式を反映させようと思っても、
$A$1は必ずセルA1を見るし、B$1は必ず1行目を見ます。$A2はそのまま使えるのですが、F列からの表にコピーする場合には使えなくなります。
ということで、最初から計算式を作るか、上記の計算式を一度コピーしてその表にあわせて範囲を指定し直すか、そしてその改造した計算式を範囲にコピーします。
これが４つの表すべてで行う必要があります。

スピル使ったら？

スピルの場合は、セルB1に絶対参照を意識しない次の計算式を入力します。
=A1+B1:D1*A2:A4
それだけで、B2からD4の範囲に計算結果が出ます。

後は、セルB2の計算式をセルをB7、G2、G7の各セルにコピーするだけです。

スピルでの位置関係が完全にコピーされます。

スピルでの問題点

それでもまだスピルでは問題があります。
今回はすべて同じ範囲の大きさの表でしたが、それぞれバラバラのケースもあるでしょう。
その場合では、残念ながらすべての範囲でスピル式を作成する必要があります。

ここ、OFFSET関数などでうまくできる方法があれば知りたいところです。
あらかじめ表の上にでも縦横の大きさが計算されるような関数を仕込んでおいて、そこをOFFSET関数で処理して範囲を決めてもいいのかもしれないです。
次のような計算式もアリかもしれないです。
=(B1<>"")*(A2<>"")(A1+B1:D1A2:A4)
=IF(AND(B1<>"",A2<>""),A1+B1:D1*A2:A4,"")
この計算式は実働しません。もう少し検討が必要です。
もしその方法も使えない場合でも、スピルを使わない方法よりは使った方が効率化できると思います。

もっと応用

下のような表を作り、他の営業部のデータを作る時は、従来では、セルC3に計算式を作成し、セルC3からE4までの範囲にコピーして使い、そのシートをコピーし、中のパラメータを変更し、コピーしたシートの表範囲を切り取り、コピー前の表の6行目以降に貼り付ける、といった手順が最も早いかと思います。

スピルであれば、セルC3にスピル計算式を作成し、表全体を6行目以降に貼り付けてパラメータを変更するという流れだけで作業ができます。