エクセルの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で質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルでエラーを無視して一番左側のセルの値を返したい 2 2023/07/27 13:06
- Excel(エクセル) エクセル 関数について質問です。 2 2022/10/03 11:14
- Excel(エクセル) Excel 値を返す数式についてです 3 2022/11/21 20:08
- Excel(エクセル) 関数を教えてください。 2 2023/08/01 10:59
- Excel(エクセル) エクセルのオートフィルについて質問があります。 2 2022/04/08 22:31
- Excel(エクセル) 列の複数ある空白セルを飛ばして、セルに並べて表示したい 3 2023/02/12 16:49
- その他(Microsoft Office) Excel 2列の値を返す数式についてです 1 2022/11/23 22:59
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
- Excel(エクセル) エラーの原因が分からない 1 2023/03/21 11:13
- Excel(エクセル) エクセル関数のスペシャリストの方、教えてください。 写真のように A列にはデータ C列にはデータの中 7 2022/04/09 00:15
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelについて教えてください
-
【マクロ】名前を保存する際に...
-
エクセル マクロ チェックボックス
-
Excel 複数列 A列B列C列一致 D...
-
エクセル vbaについて教えてく...
-
エクセルであるセルに数字を入...
-
【Excel】数字を3倍にし、なお...
-
Excel分析ツールでのポアソン回...
-
エクセルVBA、別ブックへ転記す...
-
【マクロ】顧客番号にて一致さ...
-
Excelでセルの値が同じか...
-
(マクロ)データをAブックからB...
-
エクセルを使っていて2024/5/15...
-
エクセル共有したが、アクセス...
-
エクセル②
-
エクセルのデーターが2か月前の...
-
UNIQUE関数が使えないバージョ...
-
INDIRECTを使わず excelで複数...
-
Excelで縦軸の書式を0:00形式の...
-
【マクロ】読取専用のファイル...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報