該当する同じ数値が複数ある場合でも、1つの値だけを取得するものとします。
- FILTER関数とMAX/MIN関数を使って、多数の数値の中から指定した値に最も近いものを求められます。
手順
指定値以上の数値のうち最も小さいものを求める場合
B列に面積という数値が並んでいます。
また、D3セルに指定値が入力されています。
そこでE3セルに次のような式を入力し、面積のうち指定値以上であって指定値に最も近いもの、つまり指定値以上の面積のうち最も小さいものを求めています。
E3セル
=MIN(FILTER(B3:B10,B3:B10>=D3))
FILTER関数を使い数値を指定値以上のものだけに絞り込んだうえで、MIN関数により最小値を求めています。該当する数値である「70」は2箇所ありますが、取得している数値は1つだけであることに注意してください。
アプローチは他にもいくつか考えられますが、これは求めている内容がわかりやすく、次以降の例のように変更もしやすいのがメリットです。
参考までに、次の画像は数値が複数列にわたって並んでいる例です。
基本的にFILTER関数は1列に並んだ数値からしか抽出できませんが、FLATTEN関数を併用することで複数列に並んだ数値にも対応できます。
F3セル
=MIN(FILTER(FLATTEN(B3:C6),FLATTEN(B3:C6)>=E3))
指定値より大きい数値のうち最も小さいものを求める場合
基本的には最初の例と同じです。ただし指定値の数値が異なっています。
E3セルに次の式を入力し、面積のうち指定値より大きいものであって指定値に最も近いもの、つまり指定値より大きい面積のうち最も小さいものを求めています。
E3セル
=MIN(FILTER(B3:B10,B3:B10>D3))
考え方は最初の例と全く同じで、異なるのは「>=」が「>」になったことだけです。
面積の数値中に指定値と同じ「70」がありますがこれは取得されず、その次に小さい（=ちょっと大きい）「73」を取得しているのがわかります。
指定値以下の数値のうち最も大きいものを求める場合
E3セルに次の式を入力し、面積のうち指定値以下であって指定値に最も近いもの、つまり指定値以下の面積のうち最も大きいものを求めています。
E3セル
=MAX(FILTER(B3:B10,B3:B10<=D3))
最初の例と異なるのは外側の関数がMAXになっていることと、「>=」が「<=」になっていることです。
指定値より小さい数値のうち最も大きいものを求める場合
E3セルに次の式を入力し、面積のうち指定値より小さいものであって指定値に最も近いもの、つまり指定値より小さい面積のうち最も大きいものを求めています。
E3セル
=MAX(FILTER(B3:B10,B3:B10<D3))
最初の例と異なるのは外側の関数がMAXになっていることと、「>=」が「<」になっていることです。
面積の数値中に指定値と同じ「62」がありますがこれは取得されず、その次に大きい「57」を取得しているのがわかります。
