[EXCEL]~以上~未満の数値を取得するlookup関数。

電気代の計算するのに、下記の様なテーブルから単価を引っ張りたくなった。

しかしながら、vlookupだとうまくいかずEXCEL2016だと専用関数は無いみたいだった。(Office365だとXLOOKUPで逆順検索も出来るらしい。)

仕方がないので、下記のようにしてみた。

{=INDEX(B$2:B$4,SUM(($A7>$A$2:$A$4)*1))}

やってる内容は、対象より小さいテーブル値のtrue or false配列を作成し、trueの数をindex関数に渡すという処理。

注意点は*1で数値にしないとsumが使えないって点。(trueのままsum出来ない。因みに元々は、countifでやろうとしたんだけど、=COUNTIF($A7>$A$2:$A$4 , TRUE)という感じで、計算時の配列をcountifに渡そうとするとERRORが出た。)

入力時はCtrl+Shift+Enterで配列式として実行すること。

これで、階段グラフの形でテーブル参照できるようになった。

よく考えると、kWhが0の時は、sumが0になってしまうのだが、indexが0の時は1が取れてるみたいだった。

調べてみると、INDEX関数は、通常1から開始で0はエラーとなるのだが、下記のように、Ctrl+Shift+Enterを押して、配列表現にすると、1番目の要素が参照されていた。(バグか?)

{=INDEX(B$2:B$4,0)}

普通にEnterを押した、下記だと、#VALUE!となりNG。

=INDEX(B$2:B$4,0)

バグかも知れんが、やりたかった内挿関数となっているので良しとします。

コメントを残す

メールアドレスが公開されることはありません。