
エクセルのMATCH関数についての質問ですが、どうしてもエラーが表示され悩んでいます。どうか教えていただければと思います。
たとえば下記のような場合なぜかエラーが発生してしまいますがどうしてでしょうか?
A列 B列 C列 D列 E列 F列
1行 A B A+B 戻り値
2行 2.0 1.0 1.2 2.2 2
3行 2.2 0.9 1.1 2.0 1
4行 2.4 1.1 1.3 2.4 #N/A
5行 2.6 1.0 1.4 2.4 3
使用している関数は、
E列2行 =C2+D2 F列2行 =MATCH(E2,$A2:$A5,0)
E列3行 =C3+D3 F列3行 =MATCH(E3,$A2:$A5,0)
E列4行 =C4+D4 F列4行 =MATCH(E4,$A2:$A5,0)
E列5行 =C5+D5 F列5行 =MATCH(E5,$A2:$A5,0)
です。ここでどうしても、F列4行にエラー値(#N/A)が発生してしまいます。どうしてでしょうか?
E列4行とE列5行は同じ値にもかかわらず戻り値が違うのはどうしてでしょうか?
また、不思議なことにE列4行に手入力で2.4の値を入力した場合は戻り値は3となり、正しい値を表示します。どうしてでしょうか?
以上、よろしくお願いします。
No.2ベストアンサー
- 回答日時:
こんばんは。
既に、解決されているようですが、参考までに書かせていただきます。
>どうしてでしょうか?
そのエラーが出て合わないのは誤差が出ているので、『浮動小数点、丸め誤差』といいます。
インターネット検索してみるとよいかと思います。余計に混乱させられますが。
小数点を含む計算で演算(+,-,*,/)をすると誤差が出ることがあります。原因は、内部で2進数で計算しているからです。
こちらで、調べてみましたが、MATCH 関数の演算の中で誤差が出ているようです。
対策としては、
=VALUE(FIXED(C2+D2,1))
または、
=FIXED(C2+D2,1)*1
で、フィルダウン・コピーをします。
この書き方を、『小数点固定法』と呼びます。一旦、文字列にしてしまってから、再び、数値に戻すという方法です。
E列は、そのままで、 MATCH で、
=MATCH(INT(E2*10)/10,$A$2:$A$5,0)
このように整数にして計算する方法を、『整数変換法』と呼びます。どちらかというと、こちらのほうが、一般的に使われるような気がします。
関数による処理は、無限小数が出ない限りは、ROUND関数でも可能です。時間計算などは、浮動小数点丸め以外にも、別な丸めが存在しているようです。
数式で処理するのが面倒でしたら、
ツール-オプション-計算方法
表示桁で計算する(P) にチェックを入れると良いです。
この話は、ここまでにして、全体をみると、数式の目的によっては数式自体が変わるような気がします。
被検索データ(A列)が、0.2 ずつで、E列が合計値で、検索値がない場合は、エラーを出す目的なら以下は成立しませんが、0.2 ずつ増加していく被検索データなら、以下のような式なら、エラーは出ませんね。
=MATCH(E2,$A$2:$A$5,1)
また、配列数式にして比較した場合には、丸め誤差は出ないはずです。
例:
=SUMPRODUCT(($A$2:$A$5=E2)*ROW($A$2:$A$5))-1
その理由は、比較する内部のデータ型が違うからです。
とても詳しいご回答をいただき、どうもありがとうございました。
全体的にちょっとむずかしい内容でしたが、いろいろなテクニック(表示桁数で計算する(P)方法などは、そんな機能があることさえ知りませんでした)や今まで使用したことのない関数(FIXEDやSUMPRODUCTなど)が出てきていろいろと勉強になりました。
本当に、ご丁寧にどうもありがとうございました。
No.1
- 回答日時:
E列4行の式を
=ROUND(C4+D4,1)
として確認してください。
小数点を含む数式の結果の場合、実数値と表示値に誤差が出る事があります。
早速のご回答ありがとうございます。
なるほど、ご指摘のようにE列4行の式を =ROUND(C4+D4,1)
とすると、正常な値を表示しました。
今回のように、平方根とか分数を伴わない簡単な足し算程度のものでも
実数値と表示値に誤差が出るものですか?
いずれにせよ、悶々としていたところ、とりあえず問題解決できてほんとうにうれしく思います。本当に、どうもありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの関数について
-
【マクロ】【相談】Excelブック...
-
【画像あり】オートフィルター...
-
【マクロ】実行時エラー '424':...
-
エクセルのリストについて
-
【マクロ】左のブックと右のブ...
-
【マクロ】元データと同じお客...
-
【マクロ】数式を入力したい。...
-
他のシートの検索
-
Office2021のエクセルで米国株...
-
エクセルのVBAで集計をしたい
-
vba テキストボックスとリフト...
-
【マクロ】【配列】3つのシー...
-
【マクロ】変数に入れるコード...
-
【関数】=EXACT(a1,b1) a1とb1...
-
【マクロ】【画像あり】❶ブック...
-
エクセルシートの見出しの文字...
-
【マクロ画像あり】❶1つの条件...
-
【マクロ】【画像あり】ファイ...
-
エクセルの複雑なシフト表から...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【マクロ】元データと同じお客...
-
エクセルの関数について
-
【画像あり】オートフィルター...
-
エクセルのVBAで集計をしたい
-
エクセルのリストについて
-
【マクロ】数式を入力したい。...
-
【マクロ】【相談】Excelブック...
-
Office2021のエクセルで米国株...
-
【マクロ】実行時エラー '424':...
-
他のシートの検索
-
エクセルの複雑なシフト表から...
-
【マクロ】【配列】3つのシー...
-
vba テキストボックスとリフト...
-
【マクロ】左のブックと右のブ...
-
【マクロ】変数に入れるコード...
-
エクセルシートの見出しの文字...
-
【マクロ】別ファイルへマクロ...
-
【関数】同じ関数なのに、エラ...
-
Amazonでマイクロソフトオフィ...
-
ページが変なふうに切れる
おすすめ情報