ROUNDUP関数の出力をMATCH関数に食わせるとところどころ#N/Aになる問題の検証 & 実務上の打開策【Excel関数】

発端

以前、こんなものを作ったのですが、

感覚的ブラックボックス問題（参照後にFORCAST.LINEARで線形補間）からDB側を1%刻みにギチギチに作り込んで完全一致MATCHにしようということに...

ところが実務上で謎の挙動が...

21%という値を食わせたら#N/Aになる

MATCH関数に食わせる数値の謎

21%という値を食わせたら#N/A
21.0%と打つと正しく動く
でも表示は21%
表示桁数を2以上にしても21%

なんやこれ
結局実データとは別にTEXT関数で整形したもの同士でやらないといけいないやつ？#助けてExcel

— 青と緑のVBAer (@mechkawa) January 28, 2022

Double型の仮数部...？

多分、数値の比較(IFとか)は、イプシロン以下はイコールとみなすけど、Matchは文字列比較がベースなんで、バイト単位で一致しないとだめなのかな🤔　と推測(未検証

— 高柳直紀⚡exce.live(エクセリブ)チョットデキル (@excelive) January 28, 2022

PCの仕組みレベルの話？

ご指摘の現象、こちらでも全く同じ状況で再現できました。

PCで小数を扱う以上、こういうものだと思ったほうがいいですね。

Matchの場合は、TEXTで比較するのがよさそうな気がします

— 高柳直紀⚡exce.live(エクセリブ)チョットデキル (@excelive) January 29, 2022

検証

ROUNDUP：ところどころ#N/Aになる

#N/Aになるリスト

この数列に何の法則が？

#N/A軍団
0.94
0.82
0.69
0.57
0.47
0.41
0.35
0.29
0.24
0.21
0.18
0.15
0.1
0.07
0.06

ROUND：無問題

ROUNDDOWN：無問題

#N/Aが出た部分を深堀り

値貼り付けして比較：ROUNDUPの出力は依然として#N/A

引き算して比較：差を検出できず

IF比較：差を検出できず

【実務上の打開策】 ROUNDUP後に再度ROUND系で整形すると#N/Aが消える

ROUND

ROUNDDOWN

ROUNDUP 何故かNGの関数で挟んでも#N/Aが消えた

【余談】オートフィルで値が変になる問題

上の比較ではこの変な現象は修正したものを使っていますが、100%から順に表をつくる際、オートフィルを使うと途中で値が変になる現象もあるのでこちらも紹介

43%から変になる

途中から始めると変になる箇所が移動する

STARTを１行目とすると58行目からおかしくなる

まとめ

食わせる関数ごとのMATCH関数の挙動

関数	結果
ROUND	OK
ROUNDUP	ところどころ#N/Aになる
ROUNDDOWN	OK

ROUNDUP後にもう１回整形してからMATCHに食わせる【実務上の打開策】

本質的なところはDouble型の仮数部だのPCの仕組みだの環境要因なのでお手上げとしても、実務上の打開策としてはMATCH関数が食べられる形に咀嚼するためだけにもう１回ROUND系関数を挟めば解決できそうです。

関数	結果
ROUND	OK
ROUNDUP	OK
ROUNDDOWN	OK

ちなみにTEXT関数だと参照先・参照元の桁数をすべて完璧に揃える必要があり、フレキシブルに動けなくなるのであまり取りたくない手です。