表計算で直近の値を探したい!(下から上へ探す方法)

検索系の関数は上から下へ探し出す

時系列のある表を作った場合、だいたい上から下へ過去から未来へ進むもの。そんな中「同じ要素の直近の値が欲しい!」なんてこともある。上から順に探す方法では、最も古い値が取得できるだけ。それでも、逆に下から上へ探したいときは・・・どうするの!?

EW-SYS.INFO

下から上へ探すということ

 試しに「くだもの」の購入日と、その「くだもの」を購入した直近の日付を求める表を作ってみました。

 通常、表の中で、ある値を探そうとすると、上から下に向かって探す仕組みになっています。
 たとえば、「みかん」を探した場合、2行目の「みかん」が、真っ先に見つかります。
 ですが、今回は前回の購入日が知りたいので、4行目の「みかん」の購入日が知りたいのです。
 こういう時に、下から上へ探す方法が欲しくなるのです。

ABC
1くだもの購入日前回購入日
2みかん1月12日
3なし2月3日
4みかん2月10日1月12日
5りんご2月25日
6なし3月12日2月3日
7みかん4月2日2月10日

 表の中で同じ値を探す関数といえば、だいたいの方が、vlookup関数を挙げるのではないでしょうか?他にもmatch関数などもあります。そんな中、今回利用するのは、lookup関数です。

lookup関数

 関数の定義は、このようになっていて、配列内の検索ができます。

=lookup(検索値,検査範囲,[対応範囲])
=lookup(検索値,配列)

 セルの内容だけではなく、配列内の検索ができることが、たぶん重要なポイントになるでしょう。

 先の表のC列に入れるべき関数は、次のようになります。※座標C7の計算式です。

=LOOKUP(1,0/($A$1:A6=A7),$B$1:B6)
ABCC列の計算式
1くだもの購入日前回購入日
2みかん1月12日#DIV/0!=LOOKUP(1,0/($A$1:A1=A2),$B$1:B1)
3なし2月3日#N/A=LOOKUP(1,0/($A$1:A2=A3),$B$1:B2)
4みかん2月10日1月12日=LOOKUP(1,0/($A$1:A3=A4),$B$1:B3)
5りんご2月25日#N/A=LOOKUP(1,0/($A$1:A4=A5),$B$1:B4)
6なし3月12日2月3日=LOOKUP(1,0/($A$1:A5=A6),$B$1:B5)
7みかん4月2日2月10日=LOOKUP(1,0/($A$1:A6=A7),$B$1:B6)

 とりあえず、エラー表示(#DIV/0! や #N/A)は無視してください。はじめて買った「くだもの」には、前回の購入日が無いので、エラーになっているだけです。エラーを除けば、前回の購入日が求められています。

 パッと見、不思議な計算式です。まず私が疑問に思ったのは・・・

  • 検査範囲で、「0」を割っているけど?
  • 検索値の「1」はどこから出てきた!?セル座標でもないし、何を表している?

 この二つの疑問が湧いたものです。

0を割る理由

 数値の0を割る理由を知る前に、「真」と「偽」について知る必要があります。なので、真と偽の関係を次の表にまとめてみました。

はい正しいTRUE1
いいえ間違っているFALSE0

 条件を満たす場合を「真」といい、条件を満たさない場合を「偽」と呼びます。
 たとえば「今日は1月1日ですか?」と質問した場合、1月1日当日であれば、その通りなので「真」になり、1月1日以外の日だった場合は、違うので「偽」ということになります。

 それを踏まえたうえで、検査範囲の 0/($A$1:A6=A7) を紐解いていきます。

 まずは、割る側の ($A$1:A6=A7) の理解からはじめてみましょう。
 これは、セル座標A1からA6の範囲の値を、セル座標A7の値と、同じ(=)であるか?という質問をしています。これを表に当てはめると、次の表の2列目の答えになります。セル座標A7の「みかん」を探しているので、A1からA6の中にある「みかん」の箇所(2行目と4行目)で、条件を満たす「真」(TRUE)になっています。

行数($A$1:A6=A7)数値化0を割った答え
1FALSE0#DIV/0!
2TRUE10
3FALSE0#DIV/0!
4TRUE10
5FALSE0#DIV/0!
6FALSE0#DIV/0!

 そして、真は「1」、偽は「0」と数値化できるので、その数字で、「0」を割ると、2行目と4行目以外は、0で割り算をすることになるので、#DIV/0!(0除算エラー)になります。

 で!この計算結果が、配列になって、検査範囲に収まっているのです。

 0で割る理由は、次の検索値「1」の正体を理解してから!

検索値「1」の正体

 検索値と聞くと、この場合、単純には「みかん」になるのですが、「みかん」がどこにあるのかは、前述の ($A$1:A6=A7) によって、既に探しだされています。なので、今回の検索値は「1」になるのですが、実は「1」じゃなくても、「2」でも「5000」でも良いのです。検査範囲の中に無い値なら、なんでも良かったのです。
 そもそも検索値を探して見つけるのではなく、探しても無い状態にしておきたいのです。

 その理由は、lookup関数の仕様によります。
 lookup関数は、純粋に検査範囲内に検索値が在るか探す関数というより、存在しなくても検査範囲内で最後に探した配列の位置(何番目)の値を返してくるのです。
 そうなると、最後の6行目の答えを返しそうなものですが、lookup関数内で、エラーを無視するので、#DIV/0!の行が無視され最後は4行目の位置になるのです。

 というわけで、0で割る理由は「みかん」以外の行をエラーとして、探す対象から省きたいから!だったのです。

実は下から上に探していなかった!

 結局、lookup関数も配列内を最初から最後まで順番に探していたということになります。これは、下から探すというより、探してみた最後の位置にある値を返している。ということのようです。
 う~ん。正直、関数のバグを利用した裏ワザ的な感じが拭えません。
 どうしても納得できない場合は、VBAマクロでオリジナルの関数を作って、前回の購入日を求めてください。

余談ですが

 vlookup関数の検索範囲のA2:C7という書きかたを、C7:A2と逆に書いてみたら、逆順で探してくれるかも!!と試してみましたが、計算式の入力後に、A2:C7に自動的に整えられてしまい、思惑が外れました。
 うん、なんかアタマ良いのか・・・直観的じゃないのか・・・是非に及ばずですね。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください