ちょっと仕事で使う事になったので調べたついでに備忘録として記録しておきます。
きっかけは社内ツールのせいだった
社内では、とあるツールが平日だけ動いていて、そのツールはとある数字を自動でエクセルにため込んでいきます。
エクセルは今日(=TODAY())からさかのぼる事30日分のデータを一覧表示します。
土日はツールが動かないので土日のセルは空欄。
この時、前日までの範囲内で最新のデータが欲しくてこの壁にぶち当たりました。
そう、最新のデータが欲しいのであって、前日のデータが欲しいわけではないのです。
つまり、各行の中で値が入っているセルのうち、一番右にあるセルの値が欲しいのです。
VBAで作る手もあったのですが、なぜか数式にこだわったぼくは、とりあえず調べてみることに。
調べてみたけど意外と見つからない
なんとか欲しい情報を見つけたものの、欲しかった情報が検索結果にあまり出てこなかったことに驚きました。
同じ様な使い方をする人は少ないのかな？
それとも簡単な問題過ぎて話にならないのかな？
結論、「INDEX+MATCH+MAX」だった
セル範囲「A1:Z1」に入力されている値のうち、最も右の値を取得する数式
= INDEX ( A1:Z1 , MATCH( MAX( A1:Z1 ) + 1, A1:Z1, 1 ) )INDEX関数
範囲内の縦いくつ、横いくつの位置にあるセルの値を調べる関数。
= INDEX( 範囲, 縦位置, 横位置 )MATCH関数
範囲内から指定した値を探して、範囲内の上から数えた位置を求める関数。
= MATCH( 検索地, 範囲, 一致か近似値か )一致か近似値かの値は「1」か「0」か「-1」を指定します。
「0」は検索値と完全一致するものを探す場合に使用します。
「1」又は「-1」の場合、近似値を探します。
「1」は範囲の中で近似値以下の最大値を探します。一覧を昇順で並べ替える必要があります。
「-1」は範囲の中で近似値以上の最小値を探します。一覧を降順で並べ替える必要があります。
なぜ、上記「1」の説明を太字にしたか。
実はここが納得いかない仕様だったのです。(後ほど解説します)
MAX関数
範囲内から最大値を求める関数。
= MAX( 範囲 )「INDEX+MATCH+MAX」 で動作する理由
数式についての前提知識はあるものとして話を進めます。
MAX関数が一番内側にいるので、まずはMAX関数で最大値を…。
なぜ、最大値を探すのか
MAX関数で最大値を取得し、その数字に1加算しています。つまり最大値を探すって事？どういう事？
と、ぼくの中でパニック状態に。
全てはMATCH関数の仕様に隠されていた
ここで先程のMATCH関数の太字に戻ります。
「1」は範囲の中で近似値以下の最大値を探します。一覧を昇順で並べ替える必要があります。
これ、MATCH関数のヘルプを見ると「セル範囲内のデータは昇順にソートされていることを前提」と書かれています。
ここが全てのからくりでした。
同様に「最も下の値」を取得する数式(サンプル)
ぶっちゃけ、範囲を変えるだけでした。
セル範囲「A1:A10」に入力されている値のうち、最も下の値を取得する数式
= INDEX ( A1:A10 , MATCH( MAX( A1:A10 ) + 1, A1:A10, 1 ) )納得はいかないですが、やりたいことは出来たので良しとします。