エクセル関数 VLOOKUP で近似値 +1 の式

Mac でもエクセルが使いたい!でも高いし体験版もないようで・・・。
勢いで買ってしまいそうになるのを抑えつつ iWork の体験版をインストールしてみました。
早速 iWork の表計算ソフト Numbers でいろいろ作っているのですが、エクセルとほぼ同じ関数が使えるとのことであまり悩まず使えています。
間数と言っても普段は IFVLOOKUPROUND くらいしか使わないですけどね。

VLOOKUP の検索の型は 0=検索値と完全に一致する値、1=検索値と一致する値がない場合は近似値、となっています。
近似値というのはその値を超えない範囲で一番近い値、例えば検索値が 200 だったとして 110 と 201 という数値があったら近似値は 110 になります。
ここでくまったー。近似値ではなくその値を超えて一番近い値を算出したいんだけどどうしよう。
晩ごはんを作りながら考えて、食べながら考えて、血糖値が上がったところでできました!

[画像]サンプルの表1

これが出力用の表1 で、

[画像]サンプルの表2

これが入力用の表2 です。画像がでっかくてすいません。

まずは表の解説から。
表1 の日付と同じもしくはそれを過ぎた直近の日付の金額を表示したい。

表1 B列

手入力した正解の金額

表1 C列

普通に VLOOKUP したもの。

=VLOOKUP(A2,表2 :: $A$2:$F$41,5,1)
表1 D列

近似値 +1 になるように工夫したもの。

=IF(A2=VLOOKUP(A2,表2 :: $A$2:$F$41,1,1),VLOOKUP(A2,表2 :: $A$2:$F$41,5,0),INDIRECT(VLOOKUP(A2,表2 :: $A$2:$F$41,6,1)))
表1 E列? G列

D列の式で出力されるそれぞれの値を検証した。

  • E列
    =VLOOKUP(A2,表2 :: $A$2:$F$41,1,1)
  • F列
    =VLOOKUP(A2,表2 :: $A$2:$F$41,5,0)
  • G列
    =INDIRECT(VLOOKUP(A2,表2 :: $A$2:$F$41,6,1))
表2 F列

一行下の E列の値を出力させる。
表1 から読み込むので E2 ではなく 表2 :: E2 などとした。

D列の式を日本語で言うと、日付が同じだったらその日の金額を、同じじゃなかったら次の行の金額を表示する!です。
よく見ると普通に VLOOKUP だと同じ日付がなかっときに一つ手前の行の金額を表示しているのでズレが生じています。(C5・C17 あたり)
同じ日付の金額が優先されるので、同じ日付があった場合は次の行の金額を表示しません(23行目参照)
たったこれだけのことなのにすげー悩んだ。しかももっとかっこいい書き方があるんじゃなかろうかと思います。

今回のひらめきの素はこちら。
エクセルについて教えてください。 – Yahoo!知恵袋
この回答に出てくる INDIRECT を使ってなんとかなるかも!という妄想の賜物です。

いっぱい悩んだおかげで Numbers とも仲良しになれました。MS Office 2008 買わなくて済むかな(`・ω・´)

「エクセル関数 VLOOKUP で近似値 +1 の式」への1件のフィードバック

  1. Hello blogger, i must say you have very interesting content here.
    Your blog should go viral. You need initial traffic only. How to get it?
    Search for; Mertiso’s tips go viral

コメントを残す

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