Excelで大量のデータを扱う際、合計範囲を手動で変更するのは手間がかかりますよね。特に、データの追加や削除が頻繁に行われる場合、毎回合計範囲を修正するのは非効率的です。
そこで今回は、SUM関数とOFFSET関数を組み合わせることで、合計範囲を自動的に調整する方法をご紹介します。このテクニックを使えば、データの変更に合わせて合計値が自動的に更新されるので、作業効率が大幅にアップします。
SUM関数のみの場合
SUM関数のみで合計した場合、設定した合計範囲の一番上や一番下に行を追加して記入しても、自動的に合計範囲には入りません。
SUM関数の中身を修正しなくてはいけませんが、これに気付かずSUM関数で合計しているから正しい合計だ！と間違って認識してしまうと大変なことになってしまいます。
SUM関数とOFFSET関数を組み合わせた場合
そこで、SUM関数とOFFSET関数を組み合わせると、行を追加したときでも自動的に合計に反映されますので、行を追加した後でも間違った合計になってしまうことを防げます。
まず、SUM関数を使って普通に合計します。
下記画面のように、B1の下に新しく行が挿入されて売上が追加されたり、B7の上に新しく行が挿入されて売上が追加されたりしたときに、合計範囲が広がってほしいので、SUM関数にちょっと工夫を加えます。
OFFSET関数について少し説明します。
OFFSET関数とは、あるセルを基準にして、そこから指定した行数と列数だけ移動したセル、または範囲を参照することができる関数です。
式の中身は、
OFFSET( 基準となるセル , 行方向への移動量 , 列方向への移動量 , 高さ , 幅 )となります。
今回は、式内の「高さ」と「幅」は使用しないので、「基準となるセル」と「行方向への移動量」と「列方向への移動量」のみ考えていきます。
B1の1行下からB7の1行上までの範囲を合計したいので、これを関数で置換えると、
OFFSET(B1,1,0)からOFFSET(B7,-1,0)を合計するということになります。
これをSUM関数と合わせると、SUM(OFFSET(B1,1,0):OFFSET(B7,-1,0))となります。
ここで、するどい人は疑問が浮かんでくるかもしれません。
OFFSET(B7,-1,0)とすると、行がどんどん追加されてB15まで数値が入ったら、正しく合計されないのではないか？となると思います。
でも、大丈夫です！OFFSET(B7,-1,0)のB7の部分は行が追加されたら自動でB8、B9…というように増えてくれますので、安心です。
↑このように、行を追加すると、OFFSET(B7,-1,0)の部分がOFFSET(B10,-1,0)に自動で変わりました！
まとめ
SUM関数とOFFSET関数の組み合わせは、Excelのデータ分析において非常に強力なツールです。この方法をマスターすることで、より柔軟で効率的なデータ処理が可能になります。
この記事が、皆さんのExcelスキルアップの一助となれば幸いです。
＼ 業務効率化・補助金情報をお届け／
みどりデジタルサポートでは、Microsoft 365の導入企業向けに、Power BI、Power Automate、Power Appsを中心とした研修やサポートサービスを提供しております。「ExcelやWord以外のMicrosoft 365ツールも活用したい」という企業様に最適な研修内容です。詳しいサービス内容や研修プランについては、資料をご一読ください！
＼ 活用事例も掲載しています ／
また、私たちはWEBサイトからのデータスクレイピングサービスも提供しており、収集したデータをグラフ化し、ビジネスの意思決定に役立つレポートを作成しています。効率的なデータ活用で迅速な意思決定をサポートしたい企業様には、ぜひこちらのサービスもご検討ください。
ご不明な点がございましたら、お気軽にお問い合わせください。
