スポンサーリンク
はじめに
今回の記事では、ExcelのOFFSET関数の有効な活用場面について、取り上げてみる。
といってもOFFSET関数は難解な部類の関数なので、極力使わずに済ませたいものなのだけど、実務上でも使えないとちょっとキツイなと思う具体例を扱う。
例題
まず下のような表で、データが縦に何千列続くかは不明って場合に、B列・C列の数値の合計行を入れたいとする。
このとき、こんな感じで合計行を一番下に置くのは、初心者がやりがちだがあまり良くない例だ。
データが何行あるのかが不明なのだから、こうやって合計行を一番下に置くやり方だと、行数が増えると合計行の位置をズラす作業が必要になってしまう。
また、その何処にあるのかよく分からない合計行を、スクロールして探すのもイチイチ大変になる。
いやまあ、データの行数がほぼ一定とかなら、合計行を一番下に置く方法も一概に悪いとは言い切れないけど。
合計行は一番上に
ということで、こういう場合は、合計行は一番下ではなく一番上に置いて、固定するのが良い。
今回の例なら、タイトル行の直下の3行目に置くか、タイトル行の上の1行目に置くというところ。
ただ、3行目に置くのは、並べ替えやフィルタリングのときに合計行が巻き込まれてしまうのが嫌なところ。
そこで、1行目に固定すればそういう事故の心配がないので、合計行は1行目に置いてみよう。
そしてせっかく一番上に合計行を置くのだから、3行目から一番下＝1048576行目までを合計するSUM式を入れたいところだ。すると普通は
=SUM(B$3:B$1048576)
といったSUM式を入れるだろう。
なお、上の式で$マークを付けているけどこれは絶対参照・相対参照というもので、基礎中の基礎なので必ず分かるようにしないといけない。B列の横にあるC列にも計算式をコピーすることを考慮して、こういう式にしているということだ。
ただのSUM式では不十分
しかしこの式だと、例えば7行目(商品E)を切り取って3行目に挿入するとしただけで、計算結果がおかしくなってしまう。
先に書いた
=SUM(B$3:B$1048576)
の式は、起点となる3行目に別の行が挿入されてしまったら、式が変化してしまう。
この場合は
=SUM(B$4:B$1048576)
と、起点が4行目にズレてしまうわけだ。
こういう、行の挿入とかがあっても計算式がズレたりするのを確実に防ぐ時に、OFFSET関数は役に立つ。
OFFSET関数の構成
それではOFFSET関数の概要を語るが、OFFSET関数の構成は
①参照というのが、言わば起点となるセルをどこか指定するわけだ。
②③は、①の起点セルから縦と横に何個かずつズラしたセルを、新たに起点指定するのに使うというところ。
そんな起点の再指定なんて面倒なこと、普通はやらないから②③は省略することが多い。・・・けど、今回の例ではこれを有効活用する。
④⑤は、最終的に縦横が幾つずつのセル範囲を答えとして返すのか、指定する。
OFFSET式を組み立てる
では今回の例題で、OFFSET式を用いた合計式がどうなるかというと、
まずOFFSET関数の構成を再掲。
①の起点セルをB3とか3行目のセルに設定したところで、先程のように行挿入などされれば起点を動かされてしまうだけだ。
だから①の起点セルは、絶対に動かないタイトル行である2行目を指定しよう。
でも合計するセル範囲はあくまで3行目からだ。だから②の行数に1を指定して、①の2行目から1行ずらした3行目を、実際の計算の起点として再設定するわけだ。
③の列数は、何もズラす必要がないから、0でも入れておけば良い(省略する人もいるが、私は気持ち悪いから省略しない)。
④の高さについては、もうストレートに、Excelの最大行数である1048576から2(タイトル行の行数)を引いて1048574でいいだろう。
そんなに大きな数にする必要がなければ、500とかでも良いだろうけど。
⑤幅は、1で。
これででき上がったOFFSET式が、
OFFSET式は今回のように、行や列の挿入があったとしても変わらず対応できるようにしたい場合に非常に有効だ。
セルの位置や行数・列数といったものを、数字をメインに使って指定できるので、変化に強い表を作るのに役立つ。
また、リスト形式の入力規則の設定においてもよく使えるので、ぜひとも押さえておこう。
スポンサーリンク
