googleスプレッドシートで、現在のセルよりも上にあって、値が入っているセルの一番下の値を取得する方法
はじめに
今日が2023年4月24日で、貯金額が10,000円で、
2日後の26日に8,000円の支出予定があるとします。
C4セルで10,000 – 8,000を行い貯金額の予測を出すには、
以下のように直接セルを指定して（C4セル）計算式を入力する方法もありますが、
計算式（上の例では、C4セル）よりも上にあって、空白ではないセルのうち一番下のセル（上の例では、C2セル）から、その日の支出予定額（上の例では、B4セル）を引くことができれば一つ上のセルをコピペするだけで済みます。
その方法が分かったので記事を書いていきます。
結論の数式
こちらの数式で、上にある空白ではないセルのうち一番下にあるセルの値が取得できます。
index(C:C,MAX(IF($C$1:C5="","",ROW(C:C))))先ほどの例でC6セルにこちらの数式を使用すると、
C4セルの¥2,000という値が取得できるので、B6セルの値である¥1,500を引くことで、
¥500となります。
さらに4月30日の支出予定が250円とすると、
先ほどの数式をコピペするだけで貯金額が計算されます。
個別の関数について
この数式では以下の関数が使われています。
- index関数
- max関数
- if関数
- row関数
それぞれについて解説していきます。
index関数
index関数は行と列が交差するセルの値を返します。
参考にしたサイト: GoogleスプレッドシートのINDEXとMATCHを分かりやすく解説
INDEX(対象範囲, 行番号, 列番号)なので、上の例では、A1からC8の範囲内から、A1を始点として、行2と列C（列番号が3）が交差するC2の値が返されます。
max関数
max関数は、範囲内の最大値を返します。
例えば以下のように、貯金額が入力されているC2からC8に対してmax関数を使用すると、
最大値の¥10,000が返ってきます。
参考にしたサイト: Googleスプレッドシートで最大値を色付け・抽出するMAX関数の使い方
if関数
if関数は、第一引数に設定した条件が真なら第二引数を、偽なら第三引数を返します。
例えば以下の場合、C2に入力している値が10000なので、○が表示されています。
以下の場合は、C4に入力している値が10000ではないので、×が表示されています。
row関数
row関数は、行の番号を返します。
以下の例ではD2~D9にrow関数が使用されているので、
その行の番号がそれぞれのセルに表示されています。
結論の数式の解説
個別の関数の解説を踏まえた上で、以下の数式を解説していきます。
index(C:C,MAX(IF($C$1:C5="","",ROW(C:C))))index関数の第一引数
index関数の第一引数で、C列を指定しています。
C列と第二引数との交点の値を出すためです。
index関数の第二引数
第二引数には、こちらを指定しています。
MAX(IF($C$1:C5="","",ROW(C:C)))if関数の第一引数で、上のセルが空の場合という条件を設定しています。
上のセルが空の場合、第二引数が実行されます。
第二引数には空という意味の””を指定しているため、
上のセルが空の場合は、何も返ってきません。
上のセルが空ではない場合、第三引数が実行されます。
第三引数では、row関数でC列を指定しています。
row関数は行番号を返すので、上のセルが空ではなかったら行番号が返ってきます。
全体がmax関数で囲われているので、上のセルが空ではない場合、最大の行番号が返ってきます。
第一引数と第二引数の解説を踏まえて
第一引数ではC列全体を指定しています。
第二引数では上のセルが空ではなかった場合、最大の行番号が返ってきます。
そしてindex関数によって、第一引数と第二引数の交点の値が返ってきます。
そのため今のセルよりも上にあって空白ではないセルのうち、
一番下の値が返ってきます。
以下の例では、index関数を使用して（index関数の中では、max、if、row関数が含まれる）C4よりも上にあって空白ではないセルの値である10,000を取得しています。
そして、B4セルの8,000を引いて、現在の貯金額を計算することができました。
コメントを残す