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

## 発端

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

https://qiita.com/taleau/items/4ba11074427af163e09e

https://qiita.com/taleau/items/52e452a91f187a0856bc

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/b52d02c5-7ea0-a030-c775-86ee8e9102ee.png)

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

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

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

<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">MATCH関数に食わせる数値の謎<br><br>21%という値を食わせたら#N/A<br>21.0%と打つと正しく動く<br>でも表示は21%<br>表示桁数を2以上にしても21%<br><br>なんやこれ<br>結局実データとは別にTEXT関数で整形したもの同士でやらないといけいないやつ？<a href="https://twitter.com/hashtag/%E5%8A%A9%E3%81%91%E3%81%A6Excel?src=hash&amp;ref_src=twsrc%5Etfw">#助けてExcel</a></p>&mdash; 青と緑のVBAer (@mechkawa) <a href="https://twitter.com/mechkawa/status/1487025404720205824?ref_src=twsrc%5Etfw">January 28, 2022</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

### Double型の仮数部...？

<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">多分、数値の比較(IFとか)は、イプシロン以下はイコールとみなすけど、Matchは文字列比較がベースなんで、バイト単位で一致しないとだめなのかな🤔　と推測(未検証</p>&mdash; 高柳直紀⚡exce.live(エクセリブ)チョットデキル (@excelive) <a href="https://twitter.com/excelive/status/1487110936557068291?ref_src=twsrc%5Etfw">January 28, 2022</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

### PCの仕組みレベルの話？
<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">ご指摘の現象、こちらでも全く同じ状況で再現できました。<br><br>PCで小数を扱う以上、こういうものだと思ったほうがいいですね。<br><br>Matchの場合は、TEXTで比較するのがよさそうな気がします</p>&mdash; 高柳直紀⚡exce.live(エクセリブ)チョットデキル (@excelive) <a href="https://twitter.com/excelive/status/1487233580241588224?ref_src=twsrc%5Etfw">January 29, 2022</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>



## 検証

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/49684589-bfdf-1d94-8b11-bdf6eb830e36.png)

#### #N/Aになるリスト

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/81cb1401-18a8-f5ed-e75d-1ae2ab8a9dae.png)

この数列に何の法則が？

|#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：無問題
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/4a19dcb3-0cf3-e146-b8e9-338e2535c56b.png)

### ROUNDDOWN：無問題
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/62cd37c6-1ffd-eb69-5bb5-192981e1e85a.png)

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

### 値貼り付けして比較：ROUNDUPの出力は依然として#N/A
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/68015fd1-4e3d-6315-f5c1-ed123662e3f7.png)

### 引き算して比較：差を検出できず
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/396257ff-ed53-dcf8-5a5c-83c2aa669700.png)

### IF比較：差を検出できず
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/aa2c859d-a1fa-9eb4-5411-beda3108dc9c.png)


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

### ROUND
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/dbdddc86-a2e4-00db-9f72-84075a8c1b1b.png)

### ROUNDDOWN
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/b44cba16-3407-76c7-46e0-235d026ff46b.png)

### ROUNDUP 何故かNGの関数で挟んでも#N/Aが消えた
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/5d8bb2ed-811a-a500-0f15-866a954d6737.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/af662af1-6d8f-d5a7-04e8-e8e3951f816e.png)

### 43%から変になる
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/9d76f8b4-6faf-8b8d-9827-845c5a743bb2.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/2869c8d7-a06a-5886-a61f-f1799fc75386.png)


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/6f109924-d0af-40dc-8834-f74ffa92acb9.png)

STARTを１行目とすると58行目からおかしくなる
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/8ea36bed-c9d1-44f2-d456-7ec3345dcef4.png)



## まとめ

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

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

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

|関数|結果|
|---|---|
|ROUND|OK|
|ROUNDUP|OK|
|ROUNDDOWN|OK|

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/f0c4b622-bfd0-e240-a88f-852eac3863d9.png)

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