エクセルの「消えない数式」でメンテナンスしやすい表にする ～LET関数の活用も

表を再利用しやすくするためのひと工夫

　既存の表のデータだけを消去して再利用することがありますよね。例えば、月次の売上表をコピーして翌月分を作るような運用です。その際、「ジャンプ」の機能を利用して、数式だけを残して使い回すかもしれません。

表を使い回す場合、数式の入力されているC列を避けてデータを消去することが多い

　しかし、ファイルを共有した場合などでは、Excelに不慣れな人が意図せずに数式を消してしまう可能性や、既存の数式をコピーし間違えることも考えられます。間違いに気付かずに運用するのは事故の原因になります。

　数式を保つために「スピル」を利用する方法が考えられますが、“元の数式”が消されてしまえば意味がありません。

スピルを使って結果を表示した例。元となるセルC2の数式が消されてしまうと、セルC3以降の結果も消えてしまう

　そこで今回は、数式の結果を「スピル」で表示させる際に、表のメンテナンスを楽にする考え方を紹介します。

見出しに数式を埋め込む

　単純にスピルを使うと、元のセルを消した時点で以降の結果もすべて消えてしまいます。見出し行に数式を埋め込んで集約することで、表の再利用時の事故を減らすことができます。

　今回は、100行目までデータが入力されることを想定してセル参照します。ROW関数を使って、1行目が見出し行かどうかを判別して、2行目以降の売上を評価するIFS関数の数式を続けます。読みやすいように折り返していますが、続けて1行で記述して構いません。

　外側のIF関数の条件式「ROW(B1:B100)=ROW(B1)」で、セルB1～B100の行番号が「1」（セルB1）かどうかを、つまり見出し行かどうかを判定して、1行目なら「評価」と表示します。内側のIFS関数は、売上が入力されていなければ空白（B1:B100="",""）を入力、その後に条件と評価を記述しています。最後の「TRUE」は「それ以外」を意味します。

見出しの文字列を消去しておく

上記の数式を入力する

1行目の見出しには「評価」と表示され、2行目以降には評価が表示された

データをすべて削除しても、C列の数式は残っている

　見出しに数式を入力したので、データの入力範囲をまとめて消去しても大丈夫になりました。運用も簡単になりますよね。ただし、数式の結果はスピルで表示されるため、スピルの結果が表示されるセル範囲（ここでは、セルC2～C100）にデータが入力されていると「#スピル!」エラーとなることに注意してください。

LET関数でセル参照の列記を避ける

　上記の数式で運用する場合、101行目以降に対応できません。「B:B」のように列全体を参照しても動作しますが、更新を前提とする表では、末尾に備考などを入力することもふまえて、セル範囲を特定しておくことが多いでしょう。

　その際、「B1:B200」のようにセル参照を書き換えることになりますが、複数の「B1:B100」を修正するのは面倒です。LET関数で数式を整理して、さらにメンテナンスしやすくしてみましょう。

　LET関数では、引数［名前］に変数となる値を指定し、［計算］に処理を記述します。［名前］に「A1」や「B1」といったセル番地は付けられません。また、先頭に数字は使えません。

LET関数の構文。引数は［名前］と［名前値］の組み合わせと［計算］で指定する。［名前］と［名前値］の組み合わせは126組まで指定可能

　ここでは、「uriage」という［名前］にセル範囲（B1:B100）、［計算］にIF関数とIFS関数の数式を指定します。数式内で［名前］に指定した「uriage」を利用します。

　LET関数を使うことで、将来的にセル範囲を修正する際には、2行目の「B1:B100」の部分だけ修正すれば済みます。

見出し行に上記の数式を入力する

1行目の見出しには「評価」と表示され、2行目以降には評価が表示された

　Excelの表を使い回す場面では、数式を短く書くことよりも「壊れにくい設計」にしておくといいでしょう。スピルで数式をまとめて、さらにLET関数で参照を整理しておけば、後々のメンテナンスが楽になりますよ。