地味に毎回調べるのでメモ。
環境
- Microsoft Office Home and Business 2019
- Microsoft® Excel® 2019 MSO (16.0.14228.20216) 64 ビット
ゴール
1月から12月のデータを入力する項目があります。現在入力している最新の月(一番下、一番右)のデータだけを参照します。
前提として、歯抜けデータは存在しない。
行対応
まず、データ数を取得します。セルに値が入力されている場合、入力されたセル数を返却するCOUNTA()
を使用します。画像の状態だと8
を返却します。
次に一番下のセル番地を取得します。画像で入力されている中で最新のセルはB9
です。データが入力されている列のB
、文字列結合を意味する&
とヘッダ分の+1
を組み合わせて"B" & COUNTA(B2:B13) + 1
とすると、B9
というセル番地を取得します。
最後に、目的のセル番地の値を取得します。INDIRECT()
にセル番地を設定することで、値を取得できます。
最終的な数式は次になります。
=INDIRECT("B"&(COUNTA(B2:B13)+1))
9月のデータを入力すると、9月のデータを参照します。
列対応
流れは行の算出と同じです。
まず、データ数を取得します。セルに値が入力されている場合、入力されたセル数を返却するCOUNTA()
を使用します。画像の状態だと8
を返却します。
次に一番下のセル番地を取得します。画像で入力されている中で最新のセルはN2
です。ただし、列は単純な足し算ではアルファベットに変換できません。そのため、R1C1形式でデータを取得する必要があります。今回の場合はN2
ではなく、R2C14
です。
列数調整の+6
を組み合わせて"R2C" &COUNTA(G2:R2)+6
とすると、R2C14
というセル番地を取得します。
最後に、目的のセル番地の値を取得します。INDIRECT()
はデフォルトだとA1形式ですので、R1C1形式に合うように第2パラメータにfalseすることでR1C1形式のセル番地の値を取得できます。
最終的な数式は次になります。
=INDIRECT("R2C"&COUNTA(G2:R2)+6,FALSE)
終わりに
知っていれば簡単ですが、INDIRECT
を使用しないと取得できないのが難しくしている点ですね。最新のデータが常に最大値を更新するのであれば、MAX
でいいので簡単ですが…。