エクセルSUM関数とOFFSET関数を組み合わせると、変動する合計範囲に自動で対応できる、数式を作成することができます。合計範囲を可変にして自動更新できます。SUM関数だけで計算式を設定すると、合計範囲が変動した時、エラーが出てしまうことがあります。これを、SUM関数とOFFSET関数を組み合わせて計算式を設定すると、自動で合計範囲が変わるのでエラーが出ません。合計範囲を自由に変える方法です。
それだけではなく、SUM関数とOFFSET関数を組み合わせた式で、セルに数値を入力するだけで、求める範囲の合計が出せる、といった便利な機能も作成できます！ 本文の最後で紹介しているので、ぜひお読みください！
目次
OFFSET関数
基準のセルから指定された、行数と列数だけシフトした位置にある、セルの参照を返します。
OFFSET関数の書式と引数
OFFSET(基準, 行数, 列数, [高さ], [幅])
| 基準 | 基準となるセルまたは隣接するセル範囲を指定します。 |
| 行数 | 基準の左上隅のセルを、上方向または下方向へシフトする行数を指定します。行数が正の場合、下方向へ負の場合上方向へシフトします。 |
| 列数 | 基準の左上隅のセルを、左方向または右方向へシフトする列数を指定します。行数が正の場合、右方向へ負の場合左方向へシフトします。 |
| 高さ | オフセット参照の行数を指定します。 |
| 幅 | オフセット参照の列数を指定します。 |
※「高さ」と「幅」は省略できます。
OFFSET関数はどんな関数
OFFSET関数はあるセルを基準として、指定した行数、列数シフトしたセルの値が返されます。
A1のセルを基準にして、１行、２列シフトしたセルの値をA5に返してみましょう。
A5 =OFFSET(A1,1,2)
セル「A1」を基準として「A1」から行数「１」、列数「２」シフトしたセルは「C2」です。
「C2」のセルの値「８」が返されます。
SUM関数の弱点
SUM関数で行、列の合計をだしたとき、範囲外に列、または行を挿入すると合計範囲が変わってしまうので、数式を修正しなければいけません。
SUM関数の合計範囲外に行を挿入した場合のSUM関数の弱点を、下の表を基に、分かりやすく説明していきます。
「B2:C2」は、SUM関数で合計が入力されています。
１．合計の行の前に、２つ行を挿入してみましょう。
２．B9に数値「1」を入力するとSUM関数の数式は「=SUM(B2:B7)」のままなので、B9は計算されません。
SUM関数の合計範囲を修正しなくてはいけません！
このSUM関数の弱点を解消してくれるのが、OFFSET関数です。エクセルSUM関数とOFFSET関数を組み合わせて、合計範囲を可変にする方法を説明していきます。
SUM関数の弱点を説明しましたが、SUM関数には自動で合計範囲が変更してくれる機能があります。
B8に、数値「1」を入力するとSUM関数の数式は「=SUM(B2:B8)」と自動で変わりました。B10の合計も「69,801」と変わりました。
最初、指定した合計範囲の、隣接したセルに数値を入力すると、そのセルの行、または列まで合計範囲が追加されて、合計範囲が変わります。
ただし、入力するセルの順番を間違えないように注意しなくてはいけません。こうなると、入力ミスを起こしやすくなります。
そこで活躍するのが、OFFSET関数です。
合計範囲を可変にする(行の挿入に対応)
合計範囲外に行を挿入した場合に対応できる数式を、エクセルSUM関数とOFFSET関数で作成してみましょう。
B8に、式を入力しましょう。
B8 =SUM(OFFSET(B1,1,0):OFFSET(B8,-1,0))
B8の式を、下にドラッグしましょう。
表の完成です。合計範囲が可変しても、対応できる表ができました！
合計範囲を可変にする(列の挿入に対応)
合計範囲外に列を挿入した場合に対応できる数式を、SUM関数とOFFSET関数で作成してみましょう。
E2に、式を入力します。
E2 =SUM(OFFSET(A2,0,1):OFFSET(E2,0,-1))
E2の式を、下にドラッグしましょう。
表の完成です。合計範囲外に列を挿入しても、対応できる表ができました！
セルに数値を入力して、求める範囲の合計を自動で出す
合計する範囲を自由に変えます。
セルに数値を入力すると、合計範囲が変わる数式を、エクセルSUM関数とOFFSET関数で作成します。セルに数値を入力するだけで、求める範囲の合計が出せる、便利な機能になっています。
商品の売上金額ランク表を基に、説明していきます。
E2に数値を入力すると、その数値(ランク)までの合計が求められる式を、F2に入力しましょう。
F2 =SUM(OFFSET(C2,,,E2,1))
次の式でもいいです。
F2 =SUM(OFFSET(C2,0,0,E2,1))
E2に「５」を入力しましょう。
F2には「156,000」が返されました！
順位が５位までの売上金額が合計されています。
E2に「６」を入力すると順位が６位までの売上金額が、「７」を入力すると順位が７位までの売上金額が合計されます。
E2に数値を入力すると、その順位までの売上金額の合計が表示される、便利な機能がSUM関数とOFFSET関数でできました！
OFFSET関数とMATCH関数と組み合わせて柔軟な検索
OFFSET関数関数は、単独で使うとあまり意味のない関数ですが、OFFSET関数とMATCH関数と組み合わせると、VOOKUP関数にはできない柔軟な検索ができます。
VOOKUP関数では、検索する列を一番左にして検索する範囲を指定しなければいけませんが、OFFSET関数とMATCH関数と組み合わせた検索ではその必要がありません。VOOKUP関数ではエラーがでる場面でもエラーが出ません。
この方法を覚えると、仕事アップできること間違いありません！
こちらの記事もご覧ください。⇒エクセルOFFSET関数の使い方 MATCH関数と組み合わせて可変するセル範囲に対応
