☰

Excel: 同じ範囲を維持しながら列が追加されたときに合計式を自動的に更新する方法

tag-icon • tag-icon microsoft-excel worksheet-function
Excel: 同じ範囲を維持しながら列が追加されたときに合計式を自動的に更新する方法

現在、Excel で問題が発生しています。年間コストの合計を求めていますが、この Excel シートは月が進むにつれて更新されます。問題は、sum 関数を使用して年間コストを簡単に合計できるのですが、新しい列 (月) とその列のコストが更新されたときに、年間コストを計算したいということです。

たとえば、年間コストは現在、2022 年 6 月から 2021 年 7 月までの合計になります。来月は、2022 年 6 月から 2022 年 8 月になります。sum(indirect) 関数を確認しましたが、新しい列が追加されたときに数式から最初の列を削除する必要があるという事実は含まれていないようです。

ご協力いただければ幸いです。ありがとうございます。

答え1

通常、後で挿入する列を合計に含める場合は、合計する個々の列と合計列の間に空の列を配置し、この空の列を SUM 範囲に含めます。次に、空の列の左側に列を挿入すると、SUM 関数は範囲内にあるため、この新しい列のセルを含めます。

ただし、この空の列も不要になる、より汎用性の高いアプローチは、次のような数式を使用することです。

=SUM(A1:OFFSET(K1,0,-1))

この例では、セル K1 に入力して、A1 から J1 までの範囲を合計します。関数は、OFFSETセル内の値ではなく、セル参照に対して機能します。は、 下のOFFSET(cell, y, x)セルy行とx右の列を意味しますcell。

合計範囲の開始をロールフォワードしたい場合は、同様にOFFSET関数を使用できます。たとえば、

=SUM(OFFSET(K1,0,-6):OFFSET(K1,0,-1))

最新の 6 列が表示されます。何をしようとしているのかよくわかりません。

答え2

次のような式です:

=SUM(INDIRECT(CELL("アドレス",OFFSET(A2,0,COUNTA(2A:ZZ2)-3))&":"&CELL("アドレス",OFFSET(A2,0,COUNTA(A2:ZZ2)-1))))

まずA2:ZZ2の値を持つセルの数を数えます
（COUNTA(A2:ZZ2)これにより、最初のOFFSET()パラメータもA2に強制される）

CELL("address",OFFSET(A2,0,COUNTA(A2:ZZ2)-3))セルのアドレスを含む文字列を作成します二範囲内の最後のセル（値があるセル）の左側の列。(最後の値を見つけるために、2 番目の OFFSET() に-3あります)-1

CELL(...) & ":" & CELL(...)...これにより、その行の最後の 3 つのセルに対応するセル範囲を作成してテキストを形成します 。

INDIRECT()次に、そのセル範囲テキストを「解釈」し、 に渡してSUM(...)計算を実行させます。

注: 完全な行範囲を取得するには、COUNTA(2:2)2 行目に eg を使用します。

LibreOffice で検証済み。どのバージョンの SpreadSheet でも動作するはずです。INDIRECT
() 関数の呼び出しが多いと、スプレッドシートの動作が遅くなる可能性があります。

関連情報