メインコンテンツへスキップ
見出し画像

直近3ヶ月の平均が毎月勝手にずれていく表の作り方

    先月ぶんの売上を、表のいちばん下に打ち込んだ。

    80万円。悪くない数字だ。

    なのに、上の合計セルが、1円も動かない。

    一瞬、目を疑った。

    見にいくと、合計は =SUM(C4:C9) で止まっていた。

    新しい行はC10に入ったのに、範囲がC9で終わっている。

    足したはずの80万円が、どこにも入っていなかった。

    こわいのは、エラーが出ないことだ。

    #REF! も #VALUE! も出ない。ただ、合計がひとつ前の月のまま、静かに座っている。

    もし気づかず提出していたら、私は80万円小さい売上を、そのまま報告していた。

    背筋が冷えた。

    思えば、この手の「範囲を手で書く」作業は、ずっと私の弱点だった。

    月が変わるたびに、合計の範囲を1行ずつ伸ばす。

    直近3ヶ月の移動平均も、毎月ひとつ下にずらして、また書き直す。

    =(C4+C5+C6)/3 を、翌月は =(C5+C6+C7)/3 に。

    機械でもできそうな書き替えを、私は毎月、手でやっていた。

    そして、たまに忘れる。伸ばし忘れる。ずらし忘れる。

    そのたびに、数字が静かに狂う。

    範囲は、固定で書くもの。私はそう思い込んでいた。

    セルをつかんで、始まりと終わりをドラッグして、範囲を決める。それがExcelだ、と。

    だから「範囲そのものが動く」なんて、考えたこともなかった。

    転機は、ある関数を教わったことだった。

    OFFSET。読み方は「オフセット」。

    やっていることは、たった一つ。「基準のセルから、指定した数だけ動いた場所」を指す。

    それだけの関数だ。

    でも、この「動いた場所」を数式で決められる、というのが効いた。

    基準を自分の行にすれば、コピーで窓が滑る。移動平均が、書き直しゼロになる。

    行数を数えれば、いつでも一番下——最新の行に届く。

    範囲の高さを数式にすれば、データが増えるたび、合計がひとりでに伸びる。

    あの夜、私を青ざめさせた「範囲の書き忘れ」は、OFFSETを覚えてから、一度も起きていない。

    範囲は、固定して書くものではなかった。

    範囲そのものを、数式で動かせばよかったのだ。

    この記事では、そのOFFSETの使い方を、順を追って見ていく。

    移動平均、最新の値、自動で伸びる合計、見たい期間の切り出し。

    どれも「範囲を手で書く」という、あの地味でこわい作業から、あなたを解放してくれるはずだ。


    範囲を固定で書くから、静かに抜ける

    まず、あの夜の私の手元を、絵にしておく。

    架空の会社の、月次売上だ。4月から順に、下へ増えていく。

    手作業の移動平均と、範囲を固定した合計を、1枚にならべてみた。

    画像

    上の「手書きの3ヶ月平均」は、=(C4+C5+C6)/3 と直に打ったものだ。

    翌月になれば、また =(C5+C6+C7)/3 と、ひとつずらして書き直す。

    下の「合計(範囲を固定)」が、あの夜の犯人だ。

    =SUM(C4:C9) のまま、10月の80万円が入ったC10に、範囲が届いていない。

    だから合計は3,800,000円で止まる。本当は4,600,000円のはずなのに。

    くり返すが、エラーは出ない。

    範囲を固定で書くと、こういう「静かな抜け」が起きる。

    数式が壊れるなら、まだいい。赤いエラーが目に入る。

    だが、範囲の書き忘れは、それらしい数字を出したまま、黙っている。

    いちばん危ないのは、間違いに見えない間違いだ。

    では、どうするか。

    範囲そのものが、データに合わせて動いてくれればいい。

    書き直しも、伸ばし忘れも、はじめから起きようがなくなる。

    それをやるのが、OFFSETだ。

    OFFSETは「基準から動いた場所」を指す

    OFFSETの形は、拍子抜けするほど単純だ。

    =OFFSET(基準, 下に動く数, 右に動く数)
    

    基準セルを起点に、下へ何マス、右へ何マス動いた先を指す。

    それだけの、道しるべのような関数だ。

    OFFSETが指す場所を、実際に出してみたのがこれだ。

    画像

    =OFFSET(B3,2,1) と書くと、B3を起点に、2つ下・1つ右へ動く。

    たどり着くのはC5。売上620,000円が返る。

    下・右にマイナスを書けば、上や左にも動ける。起点から、どちらへでも進める。

    そして、ここからが本題だ。

    引数を2つ足すと、OFFSETは1マスではなく「範囲」を返せる。

    =OFFSET(基準, 下, 右, 高さ, 幅)
    

    うしろの「高さ」「幅」で、範囲の大きさを決める。

    たとえば =SUM(OFFSET(C4,0,0,3,1)) は、「C4から動かず、高さ3・幅1」。

    これはC4:C6という範囲そのものだ。だからSUMでくるめば、その3マスを合計できる。答えは1,700,000円。

    ここまでだと、ただ回りくどいSUMに見えるかもしれない。

    肝は、この「下・右・高さ・幅」を、数式で計算できることにある。

    基準を自分の行にすれば、コピーで窓が動く。

    行数を数えれば、一番下に届く。

    固定だったはずの範囲が、数式ひとつで生き物のように動きはじめる。

    次の章から、その動く範囲を、実際に作っていく。

    移動平均は、窓を1段ずつ滑らせる

    OFFSETのいちばんおいしい使い方が、移動平均だ。

    直近3ヶ月をならして、売上の流れを見る。あの、毎月書き直していたやつだ。

    直近3ヶ月の移動平均を、OFFSETで一気に出した表がこれだ。

    画像

    6月の欄に入れる式は、これ一本。

    =AVERAGE(OFFSET(C6,-2,0,3,1))
    

    基準はC6。そこから2つ上へ戻り、高さ3の範囲——C4:C6を平均する。

    4月・5月・6月の平均で、566,667円だ。

    大事なのは、次の一手だ。

    この式を、下にコピーする。

    すると、基準のC6が、C7、C8へと自動でずれていく。

    だから窓も、C5:C7、C6:C8と、1段ずつ下に滑る。

    7月は633,333円、8月は646,667円、9月は700,000円。

    式は一本のまま。書き直しは、ただの一度もない。

    基準を自分の行にすると、コピーするだけで窓が滑る。 これが移動平均の種明かしだ。

    5ヶ月移動平均にしたければ、-2 と 3 を、-4 と 5 に変える。

    「基準から戻る数」と「高さ」を、セットで直す。それだけだ。

    窓の幅を、数式で自由に決められる。

    毎月ずらして書き直していた時間が、まるごと消える。

    データが増えても、いつも最新を見にいく

    次は、あの夜の逆側の問題だ。

    データが下にどんどん増える表で、「いつも最新の1件」を見たい。

    最新の行は毎月動く。ふつうに書くと、セルを指し直すことになる。

    ここで効くのが、COUNTAとの合わせ技だ。

    COUNTAは、その列にいまいくつデータがあるかを数える。6件あれば6を返す。

    データが増えても最新を見にいく式を、並べてみた。

    画像

    最新の売上を出す式は、これだ。

    =OFFSET($C$3,COUNTA($C$4:$C$100),0)
    

    基準は見出しの下、C3。そこからCOUNTAの数——いまなら6——だけ下に飛ぶ。

    たどり着くのはC9。最新の740,000円だ。

    来月、10月の行を足せば、COUNTAは7を返す。式はそのまま、飛ぶ先がC10に変わる。

    最新3ヶ月の合計なら、一番下から2つ戻って、高さ3の範囲を取る。

    =SUM(OFFSET($C$3,COUNTA($C$4:$C$100)-2,0,3,1))
    

    これで2,100,000円。7月・8月・9月のぶんだ。

    数える範囲は、少し広めにC4:C100で取ってある。

    だから100行までは、データを足しても、式にはいっさい触らなくていい。

    一つだけ、約束がある。

    基準セルは $C$3 と、$で固定する。 ここを留め忘れると、下にコピーしたとき基準までずれて壊れる。

    「動かす場所」と「動かさない基準」を、はっきり分ける。

    これが、OFFSETを安全に使う、いちばんのコツだ。

    行を足すと、合計がひとりでに伸びる

    そして、あの夜の元凶——「合計が伸びない」問題に、正面から答える。

    範囲を固定した合計と、自動で伸びる合計を、同じ表で見くらべた。

    画像

    左は、いつもの =SUM(C4:C9)。10月の80万を足したのに、範囲が届かず3,800,000円で止まっている。

    右が、その答えだ。

    =SUM(OFFSET($C$4,0,0,COUNTA($C$4:$C$100),1))
    

    C4を先頭に、「高さ=いまの件数」ぶんの範囲を作って、SUMしている。

    行が7件になれば高さ7、10件になれば高さ10。

    データが増えた瞬間、範囲もいっしょに伸びる。だから、ちゃんと4,600,000円になる。

    書き直しも、伸ばし忘れも、もう起きない。

    ただ、ここで正直に言っておきたいことがある。

    いまのExcelなら、この「行を足したら合計が伸びる」だけの目的は、テーブルで済む。

    表のどこかを選んで Ctrl+T。表を「テーブル」にすれば、行を足すだけで合計が自動で伸びる。

    見た目もきれいだし、こちらのほうがずっと簡単だ。

    では、OFFSETの出番はどこか。

    テーブルにできない他人のシート、名前の定義に動く範囲を仕込みたいとき、そして次の「窓を動かす」仕事だ。

    道具は、適材適所で選ぶ。単純に伸ばしたいだけなら、テーブルでいい。

    見たい期間を、1セルで切り替える

    OFFSETが本領を出すのは、MATCHと組んだときだ。

    「6月から3ヶ月ぶん」のように、起点と長さを、自分で自由に決めたい。

    見たい月と長さを1セルで変える仕掛けを、作ってみた。

    画像

    起点の月を「6月」、長さを「3」と、別のセルに置く。

    MATCHは、6月が月の列の何番目かを探して、3を返す。

    その3を、OFFSETの「下に動く数」に渡す。

    =SUM(OFFSET($C$3,MATCH($G$3,$B$4:$B$9,0),0,$G$4,1))
    

    C3から3つ下——C6が、窓の先頭になる。高さは、長さのセルから取った3だ。

    だから合計は、6月・7月・8月のC6:C8で、1,940,000円。

    ここからが気持ちいい。

    起点を「7月」に書き替えれば、窓はC7:C9へ動く。

    長さを「2」にすれば、2ヶ月ぶんに縮む。

    式には、指一本ふれない。セルの中身を変えるだけで、集計する範囲がまるごと動く。

    四半期の合計。任意期間の平均。直近◯週間の集計。

    「どこから、どれだけ」を人が選びたい場面で、この仕掛けはよく効く。

    「どこから」を探すMATCHと、「そこから動く」OFFSET。 この二人三脚が、可変な集計の型だ。

    現場では、こんな場面で効く

    ここまでの4つを、実際の仕事に当てはめてみる。

    どれも「範囲が動く」ことに価値がある場面だ。

    まず、移動平均。

    売上や来店数は、月によって上下する。ボーナス月は伸び、閑散期は落ちる。

    その月の数字だけを見ると、上がった下がったと一喜一憂してしまう。

    移動平均で3ヶ月をならすと、でこぼこが消えて、本当の流れが見えてくる。

    じわじわ落ちているのか、底を打って戻したのか。季節のノイズを抜いた、下の線が教えてくれる。

    為替やアクセス数、体重の記録でも同じだ。日々の上下ではなく、流れで見たいものに効く。

    次に、最新の値。

    在庫表を思い浮かべてほしい。入荷と出荷を、下へ下へと足していく。

    いま手元にいくつあるかは、いつも「一番下の残高」だ。

    その最新残高を、別のセルにいつも映しておきたい。COUNTAで飛ぶOFFSETが、まさにこれをやる。

    口座残高の記録でも、体重ログでも、プロジェクトの最新進捗でも、考え方は同じだ。

    「増え続ける表の、いちばん新しい1行」を、動かずに見張っていられる。

    三つ目、自動で伸びる合計。

    家計簿や日報のように、毎日1行ずつ足していく表に効く。

    月末に「今月いくら使ったか」を出すのに、範囲を伸ばし忘れる心配がいらない。

    打ち込んだ瞬間、合計に反映される。あの夜の私が、いちばんほしかったものだ。

    最後に、可変window。

    四半期ごとの集計や、繁忙期だけを切り出した平均。

    「4月から3ヶ月」「10月から3ヶ月」と、起点をずらして見くらべたいとき。

    起点のセルを打ち替えるだけで、同じ式が別の期間を映す。レポートの作り替えが、ぐっと減る。

    共通しているのは、どれも「範囲を人が書き直していた」作業だということだ。

    その書き直しを、OFFSETが引き受ける。人は、出てきた数字を読むことに集中できる。

    便利さの裏にある、3つのクセ

    ここまで持ち上げてきたが、OFFSETには、はっきりしたクセもある。

    使う前に、3つだけ知っておいてほしい。

    OFFSETの3つのクセを、1枚にまとめておいた。

    画像

    一つ目は、揮発性だ。

    OFFSETは「開くたび・どこかを触るたびに」計算し直す関数だ。

    数式を数百、数千とばらまくと、Enterを押すたびに、シートがもたつく。

    だから、移動平均や最新値のように、本当に「動く範囲」が要る場所だけに絞って使う。

    二つ目は、基準の固定。

    コピーして使う式なら、基準は $C$3 のように$で留める。

    留め忘れると、下にコピーしたとたん、基準までずれて、別の範囲を集計してしまう。行の挿入・削除でも同じだ。

    三つ目は、見えない依存。

    OFFSETは、参照先がセルの上に「線」で見えない。

    どこを見にいっているのか、あとから追いにくい。だから、式のそばにひとことメモを添えておくといい。

    そして、使い分けの結論はこうだ。

    行を足したら合計が伸びてほしいだけなら、テーブルが一番ラク。条件つきの集計なら、SUMIFS。

    OFFSETは、移動平均・最新N件・可変期間のような、「窓を動かす」仕事でこそ光る。

    つまずいたら、ここを見る

    OFFSETで詰まる場所は、だいたい決まっている。3つだけ挙げておく。

    一つ、#REF! が出る。

    これは、OFFSETが「シートの外」を指してしまったときのサインだ。

    たとえば OFFSET(C4,-2,0) は、C4から2つ上——C2を指す。だが表がC2で終わっていれば、はみ出して #REF! になる。

    移動平均の先頭2ヶ月が空になるのは、このためだ。データが足りない行は、無理に埋めなくていい。

    どうしても空欄が気になるなら、=IFERROR(AVERAGE(OFFSET(...)),"") でくるむと、静かに空にできる。

    二つ、答えが1つずつずれる。

    OFFSETの「下に動く数」は、基準セルからの距離だ。基準そのものは0、その1つ下が1になる。

    「3行目のデータがほしい」なら、動く数は3ではなく、基準の置き方しだいで変わる。

    見出しのすぐ下を1行目と数えたいなら、基準を見出しセルにして、動く数をそのまま順番の数にすると合いやすい。

    三つ、コピーしたら全部おかしくなった。

    ほぼ間違いなく、基準セルの$固定を忘れている。

    OFFSET(C3,...) のままコピーすると、基準がC4、C5とずれる。OFFSET($C$3,...) に直す。

    この3つを押さえておけば、OFFSETでハマる時間は、ぐっと短くなる。

    そのままコピペして使う式

    この記事で出てきた式を、まとめて置いておく。

    範囲や基準の場所だけ、自分の表に合わせて差し替えて使ってほしい。

    OFFSETの基本   =OFFSET(基準, 下に動く数, 右に動く数)
    範囲を返す     =OFFSET(基準, 下, 右, 高さ, 幅)
    
    3ヶ月移動平均  =AVERAGE(OFFSET(自分の行,-2,0,3,1))   ← 下にコピー
    最新の値       =OFFSET($C$3,COUNTA($C$4:$C$100),0)
    最新3ヶ月合計  =SUM(OFFSET($C$3,COUNTA($C$4:$C$100)-2,0,3,1))
    自動で伸びる   =SUM(OFFSET($C$4,0,0,COUNTA($C$4:$C$100),1))
    可変window合計 =SUM(OFFSET($C$3,MATCH(起点,月の列,0),0,長さ,1))
    

    C4 や C3 は、自分の表の「データの先頭」と「見出し」の位置に合わせる。

    $C$4:$C$100 の 100 は、データが増える見込みに合わせて、広めに取っておけばいい。

    OFFSETもCOUNTAも、MATCHも、20年以上前からある枯れた関数だ。

    会社のExcelがどれだけ古くても、そのまま動く。新しいバージョンは要らない。

    範囲は、決めるものではなく、動かすもの

    長いあいだ、私は範囲を「決めるもの」だと思っていた。

    始まりと終わりをドラッグして、固定して、それで完成。

    だから、月が変わるたびに範囲を伸ばし、移動平均をずらして書き直していた。

    そして、たまに伸ばし忘れて、80万円を静かに落とした。

    OFFSETを覚えて、その考え方がひっくり返った。

    範囲は、決めて固定するものではない。データに合わせて、動かすものだった。

    基準を自分の行に置けば、窓は勝手に滑る。

    行数を数えれば、いつでも最新に届く。

    高さを数式にすれば、合計はひとりでに伸びる。

    もう、あの夜のように、合計セルの前で青ざめることはない。

    範囲を書き直す時間は、その数字を「どう読むか」を考える時間に変わった。

    Excelに任せられる作業は、Excelに返す。

    浮いた時間を、人にしかできないことへ回す。私がずっとやりたかったのは、それだ。


    配布のお知らせ。この記事の式を全部組み込んだ「OFFSET動く範囲スターターキット」を、無料で渡している。

    月ごとの売上を貼れば、3ヶ月移動平均・最新の値・最新3ヶ月の合計・自動で伸びる年間合計が、いっぺんに出る。

    見たい月と長さを1セル変えるだけの「可変window集計」も、そのまま入れてある。

    もちろん、実際のExcelで全部の数式を計算し直して、値が合うこと・行を足しても自動で追従することは確かめてある。

    下のLINEに登録して、「範囲」 とひとこと送ってほしい。ダウンロードリンクを自動でお返しする。

    画像

    Excel時短・業務自動化・AI活用のネタを、ふだんもLINEとnoteで配っている。よかったら覗いてみてほしい。

     
     
    \初心者OK!自動化・効率化できるExcel教室/ ◇AI×Excelの仕事時短術で自分の時間爆増! ◇自分だけの自動化ツールがつくれる! ◆元PC平凡▶半年で職場のExcel先生 ◆AIを使った自動化ツール作成数100超

    あなたへのおすすめ