2009/06/18

Excel MOD関数で#NUMエラー

「ExcelのMod関数(割り算の余りを求める関数)を使うとエラーがでるんだよ」という相談を受け、問題のBookをもらって眺めてみたところ。

割られる数の桁が妙に多い。

「なんかそれって怪しいなー」と思って、試しに割られる数の桁を少なくすると、ちゃんとMod関数は機能する。どうやらMod関数では、割る数が1桁の場合、おおよそ9桁前後で#NUMエラーが出てくる模様。ウーム、と思ってMod関数の割られる数の上限を求めてググってみると、MSのサイトにちゃんと原因・解決法が書いてありました。

原因は、これ

#NUM! エラーが返されるのは、以下の条件に該当する場合です。 (除数 ' * 134217728) が、数値' 以下です。

ここでいう数値'というのは、割られる数のコトですね。

たとえば1234567890 ÷ 7のケースを考えてみると、

7 × 134217728 = 939524096

割られる数1,234,567,890よりも939,524,096の方が小さいからアウト、というわけですな。

それじゃぁ桁数が大きいときに、MOD関数を使わずに余りを求めたいときはどうすりゃいいの?というのは簡単(上記のリンク先のヘルプにも書いてありますけど)。

1234567890 ÷ 7のケースで考えるならば、

1234567890 -(INT(1234567890/7)*7)

でOK。INT(1234567890/7)で商を求めて(INT関数で小数点以下を無視している)、除数で掛け算して、割る数から引けば、そりゃ余り。

5 件のコメント:

mas10 さんのコメント...

大変参考になりました。有難うございます。
ただ、INI関数でなく、INT関数ではないかと・・・。
同じようにmod関数で悩んでおりましたが、おかげさまで無事解決できました。

じょに さんのコメント...

mas10さん初めまして。
関数についてはご指摘の通り、INT関数が正しいです。
ご指摘のほど、ありがとうございました。m(_ _)m

Citrus さんのコメント...

最近、このエラーで悩んでいたところ、このページにたどり着き、問題を解決することが出来ました。ありがとうございます。

一点、記載の中で指摘させて頂きます。
7 × 134,217,728 = 939,524,096
割られる数(数値') 1,234,567,890 よりも
939,524,096の方が"小さい"からアウト
が正しいのではないでしょうか?

じょに さんのコメント...

はじめましてCitrusさん。
マッタクご指摘の通りでございます。
そもそも8641975230という謎の数字をどこからだしたんでしょうかね、私(恥)

匿名 さんのコメント...

NW-7形式の
7DRというチェックデジットを
求める方法をずっと探しておりまして
こちらにたどり着きました。

これでようやくエクセルで
簡単にチェックデジットの値を
計算することができました。

ありがとうございました。