☰

変数範囲を参照する数式を取得する

tag-icon • tag-icon microsoft-excel
変数範囲を参照する数式を取得する

そこで、セルの数が可変のときに方程式を機能させようとしています。現在のコストと予測コストが記載されたシートがあります。予測数値は現在のコストのみに基づいて機能するようにしたいと思います。これは、月初めの ACT で示されます。sumifs/countifs の最初の数式は、循環参照エラーが発生したために拒否されました。そのため、別のセルに応じて方程式を動的に更新する方法があるかどうか疑問に思っています。

更新が必要な行が約 400 行あり、範囲は 1 つずつ追加する必要があると理解しているため、動的範囲の使用は避けたいと思います。

数式で期待する動作を表す例: =Sum(A1:A(1+B1))、つまり、B1 が増加すると範囲も増加します。

サンプルデータ:

    Act   Act   Act   Proj   Proj  
A:   1     2     3      2      2
B:   1     3     5      3      3
C:   4     5     6      5      5
  • 投影A: 2
  • 投影B: 3
  • 投影C: 5

次に、4 か月目の実際の数値を追加し、Proj を Act に変更すると、投影 A、投影 B などが計算される範囲が、私がさらに入力しなくても 3 から 4 に変わります。

答え1

OFFSET()X 行 Y 列の範囲の列への参照を作成するには、次のようにします。

=OFFSET(A1,0,0,10,10)を参照する 10 x 10 の参照を作成しますA1:J10。

上記の数式でハードコードされた 10 を、COUNTIF()範囲が参照する行と列の数を推測する に置き換えることができます。

AltMMD循環エラーを回避するには、この数式で名前付き範囲 ( ) を定義し、数式を使用する場所で名前付き範囲を使用するようにしてください。

あるいは、OFFSET()幅と高さのパラメータ (つまり、上記の数式の 10 など) を使用せずに、代わりに範囲内の最初と最後のセルを検索することで、X 行 Y 列の参照を含むテキスト文字列を作成することもできます。次に、それらのセルを、そのアドレスを返す呼び出しでラップしますCELL("address",...)。(注意: 開始点が $A$1 の場合は、終了セルを見つけるだけです)。

:これらを の間にで連結し、INDIRECT()必要な場所ですべてを で囲みます。つまり、 がハードコードされていると仮定すると、次のようになります$A$1。

=INDIRECT("Sheet1!$A$1:"&CELL("address",OFFSET(Sheet1!$A$1,COUNTIF(..x-rows..),COUNTIF(..y-cols..))

COUNTIF()読者の練習として、残っている行と列の数を見つけるためのロジック

関連情報