2017年4月20日木曜日

【Excel】セルに「#DIV/0!」「#NUM!」とエラー表示されてしまったときのエクセル対策ワザ

【Excel】セルに「#DIV/0!」「#NUM!」とエラー表示されてしまったときのエクセル対策ワザ

 
2017年4月19日 07:20
　Excel（エクセル）は、仕事や普段の生活で使う機会の多い、最も身近なアプリケーションのひとつです。しかし、「イマイチよくわからないまま使っている」「実は少し苦手……」という人も多いのではないでしょうか？　この連載では、いまさら人に聞けないけど、知っていれば必ず役に立つ、Excelを使いこなすためのノウハウを紹介します。

セルに「#DIV/0!」「#NUM!」と表示される原因は？

　Excelでは、セルに入力した数式が正しく計算できない場合、セルに「#DIV/0!」や「#NUM!」などのエラー値が表示されます。資料作りにExcelで表計算させようとしたら、セルにこのようなエラー値が出てきてどうしよう……と困った経験が多くの人にあるのではないでしょうか。
　エラー値は7種類あり、セルに入力した数式そのものだけではなく、その数式が参照するセルのどのあたりに間違いがあるかを調べるためのヒントになるのです。
　今回は、0で割ったときに表示される「#DIV/0!」と、関数や数値に問題があるときに表示される「#NUM!」という、数式を入力したセルに表示されやすい2種類のエラー値について解説します。

0で割ったときに表示される「#DIV/0!」

　「#DIV/0!」は、セルの中で「0での割り算」を行ってしまったときに表示されるエラー値です。
　ここでは、合計点を受験科目数で割って平均点を求める表計算の例で考えてみましょう。セルG5に「#DIV/0!」（①）と表示されています。
　G列には受験者ごとの平均点を求める数式「=F5/E5」（②）が入力されています。「合計点÷受験科目数」です。
　セルE5に入力された数値は「0」（③）です。つまり、セルG5の数式は「0」で割る数式になっているため、「#DIV/0!」のエラー値が表示されてしまったというわけです。
　この場合は、「受験科目数が0の人がいた」ということなので、数式そのものに誤りがあるわけではありません。平均点を入力するG列の数式を、「受験科目数が0の場合は平均点のセルに何も表示されないようにする」という意味のものに修正しましょう。
　セルG4をクリックし、「=IFERROR(F4/E4,””)」（④）という数式を入力して、［Enter］キーを押します（⑤）。セルG4は、もともと計算結果がエラーになっていないので、数式を修正した後も「F4/E4」の計算結果が正しく表示されます。
　「IFERROR」関数は、計算結果がエラーになった場合に表示する値を指定したいときに使う関数です。④でセルG4に入力した数式は「F4/E4の計算結果がエラーになった場合は何も表示しない」という意味です。
　セルG4に入力した数式を他のセルにもコピーします。セルG4の右下にマウスポインターを合わせると、マウスポインターの形が十字に変わります（⑥）。
　十字のマウスポインターをセルG8までドラッグします（⑦）。
　数式がコピーされ、先ほどエラー値が表示されていたセルG5には何も表示されなくなりました（⑧）。G列の他のセルには平均点が正しく表示されています。

関数や数値に問題があるときに表示される「#NUM!」

　「#NUM!」は、数式中の関数や数値に問題があるときや、入力された数値がExcelで扱える範囲を超えているときにセルに表示されるエラー値です。
　ここでは、得点表からトップスリーを抽出する例で考えてみましょう。セルF4に「#NUM!」（①）と表示されています。
　このセルには本来、指定したセル範囲（セルB4からセルB9まで）の中で3番目に大きい数値を表示するための「=LARGE(B4:B9,3)」という数式を入力するつもりでした。しかし、実際には最後の数値が「3」ではなく「13」、つまり数式としては「=LARGE(B4:B9,13)」（②）と誤ったものが入力されてしまっています。
　この「13」は範囲内に存在する数値の数（ここでは、6人分の得点が入力されているため、最大値は6）を超えているため、「#NUM!」のエラー値が表示されてしまったというわけです。
　エラー値「#NUM!」が表示されるのは、この例のように、数式に入力される関数や数値に問題がある場合がほとんどです。正しい数式を入力すれば、エラーが解消されて「#NUM!」は表示されなくなります。

数値が大きすぎる・小さすぎる場合にも表示される「#NUM!」

　エラー値「#NUM!」は、入力された数値がExcelで扱える範囲を超えるほど大きすぎたり小さすぎたりするときにも表示されます。
　ここでは、10の310乗を意味する数式「=10^310」（①）を入力した例を見てみましょう。セルA1に「#NUM!」（②）と表示されています。
　次に、マイナス10の310乗を意味する数式「=-10^310」（③）の例を見てみましょう。同様にセルA1に「#NUM!」（④）と表示されています。
　普段Excelを使っている中でここまで大きい数値や小さい数値を扱うことはまれだと思いますので、エラー値「#NUM!」が表示されたときは、数式や参照先が間違っている可能性を疑って再確認するといいでしょう。

エラー値が出たら数式や参照先をチェックしよう

　今回紹介した「#DIV/0!」や「#NUM!」は、関数を使わないシンプルな数式であっても、凡ミスで頻繁に表示されてしまうエラー値です。
　「#DIV/0!」や「#NUM!」が表示された場合は、「0で割っていないかな？」「ありえない数値が数式に入っていないかな？」「数式の参照先が間違っていないかな」などと落ち着いて原因を探りましょう。
　たいていの場合はセルに入力されている数値や数式をもう一度見直せば解決できますよ。http://forest.watch.impress.co.jp/docs/serial/exceltips/1055170.html

とても参考になります：

再生核研究所声明353（2017.2.2）　ゼロ除算　記念日

2014.2.2　に 一般の方から100/0　の意味を問われていた頃、偶然に執筆中の論文原稿にそれがゼロとなっているのを発見した。直ぐに結果に驚いて友人にメールしたり、同僚に話した。それ以来、ちょうど３年、相当詳しい記録と経過が記録されている。重要なものは再生核研究所声明として英文と和文で公表されている。最初のものは

再生核研究所声明　148（2014.2.12）:　100/0=0,  0/0=0　－　割り算の考えを自然に拡張すると　―　神の意志

で、最新のは

Announcement 352 (2017.2.2):  On the third birthday of the division by zero z/0=0　

である。
アリストテレス、ブラーマグプタ、ニュートン、オイラー、アインシュタインなどが深く関与する　ゼロ除算の神秘的な永い歴史上の発見であるから、その日をゼロ除算記念日として定めて、世界史を進化させる決意の日としたい。ゼロ除算は、ユークリッド幾何学の変更といわゆるリーマン球面の無限遠点の考え方の変更を求めている。―　実際、ゼロ除算の歴史は人類の闘争の歴史と共に　人類の愚かさの象徴であるとしている。
心すべき要点を纏めて置きたい。

１）     ゼロの明確な発見と算術の確立者Brahmagupta (598 - 668 ?)　は　既にそこで、0/0=0　と定義していたにも関わらず、言わば創業者の深い考察を理解できず、それは間違いであるとして、１３００年以上も間違いを繰り返してきた。
２）     予断と偏見、慣習、習慣、思い込み、権威に盲従する人間の精神の弱さ、愚かさを自戒したい。我々は何時もそのように囚われていて、虚像を見ていると　真智を愛する心を大事にして行きたい。絶えず、それは真かと　問うていかなければならない。
３）     ピタゴラス派では　無理数の発見をしていたが、なんと、無理数の存在は自分たちの世界観に合わないからという理由で、―　その発見は都合が悪いので　―　、弟子を処刑にしてしまったという。真智への愛より、面子、権力争い、勢力争い、利害が大事という人間の浅ましさの典型的な例である。
４）     この辺は、２０００年以上も前に、既に世の聖人、賢人が諭されてきたのに　いまだ人間は生物の本能レベルを越えておらず、愚かな世界史を続けている。人間が人間として生きる意義は　真智への愛にある　と言える。
５）     いわば創業者の偉大な精神が正確に、上手く伝えられず、ピタゴラス派のような対応をとっているのは、本末転倒で、そのようなことが世に溢れていると警戒していきたい。本来あるべきものが逆になっていて、社会をおかしくしている。
６）     ゼロ除算の発見記念日に　繰り返し、人類の愚かさを反省して、明るい世界史を切り拓いて行きたい。
以　上

追記：

The division by zero is uniquely and reasonably determined as 1/0=0/0=z/0=0 in the natural extensions of fractions. We have to change our basic ideas for our space and world:

Division by Zero z/0 = 0 in Euclidean Spaces
Hiroshi Michiwaki, Hiroshi Okumura and Saburou Saitoh
International Journal of Mathematics and Computation Vol. 28(2017); Issue  1, 2017), 1-16.　
http://www.scirp.org/journal/alamt　 　http://dx.doi.org/10.4236/alamt.2016.62007
http://www.ijapm.org/show-63-504-1.html
http://www.diogenes.bg/ijam/contents/2014-27-2/9/9.pdf

再生核研究所声明357（2017.2.17）Brahmagupta　の名誉回復と賞賛を求める。

再生核研究所声明　３３９で　次のように述べている：

世界史と人類の精神の基礎に想いを致したい。ピタゴラスは　万物は数で出来ている、表されるとして、数学の重要性を述べているが、数学は科学の基礎的な言語である。ユークリッド幾何学の大きな意味にも触れている（再生核研究所声明315（2016.08.08）　世界観を大きく変えた、ユークリッドと幾何学）。しかしながら、数体系がなければ、空間も幾何学も厳密には　表現することもできないであろう。この数体系の基礎はブラーマグプタ（Brahmagupta、598年 – 668年?）インドの数学者・天文学者によって、628年に、総合的な数理天文書『ブラーマ・スプタ・シッダーンタ』（ब्राह्मस्फुटसिद्धान्त Brāhmasphuṭasiddhānta）の中で与えられ、ゼロの導入と共に四則演算が確立されていた。ゼロの導入、負の数の導入は数学の基礎中の基礎で、西欧世界がゼロの導入を永い間嫌っていた状況を見れば、これらは世界史上でも顕著な事実であると考えられる。最近ゼロ除算は、拡張された割り算、分数の意味で可能で、ゼロで割ればゼロであることが、その大きな影響とともに明らかにされてきた。しかしながら、　ブラーマグプタは　その中で 0 ÷ 0 ＝ 0 と定義していたが、奇妙にも１３００年を越えて、現在に至っても　永く間違いであるとされている。現在でも0 ÷ 0について、幾つかの説が存在していて、現代数学でもそれは、定説として　不定であるとしている。最近の研究の成果で、ブラーマグプタの考えは　実は正しかった　ということになる。　しかしながら、一般の　ゼロ除算については触れられておらず、永い間の懸案の問題として、世界を賑わしてきた。現在でも議論されている。ゼロ除算の永い歴史と問題は、次のアインシュタインの言葉に象徴される：

Blackholes are where God divided by zero. I don't believe in mathematics. George Gamow (1904-1968) Russian-born American nuclear physicist and cosmologist re-
marked that "it is well known to students of high school algebra" that division by zero is not valid; and Einstein admitted it as the biggest blunder of his life [1] 1. Gamow, G., My World Line (Viking, New York). p 44, 1970.

物理学や計算機科学で　ゼロ除算は大事な課題であるにも関わらず、創始者の考えを無視し、割り算は　掛け算の逆との　貧しい発想で　間違いを１３００年以上も、繰り返してきたのは　実に残念で、不名誉なことである。創始者は　ゼロの深い意味、ゼロが　単純な算数・数学における意味を越えて、ゼロが基準を表す、不可能性を表現する、神が最も簡単なものを選択する、神の最小エネルギーの原理、すなわち、神もできれば横着したいなどの世界観を感じていて、0/0=0 を自明なもの　と捉えていたものと考えられる。実際、巷で、ゼロ除算の結果や、適用例を語ると　結構な　素人の人々が　率直に理解されることが多い。
１３００年間も　創始者の結果が間違いであるとする　世界史は修正されるべきである、間違いであるとの不名誉を回復、数学の基礎の基礎である算術の確立者として、世界史上でも高く評価されるべきである。　真智の愛、良心から、厚い想いが湧いてくる。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　以　上

追記

The division by zero is uniquely and reasonably determined as 1/0=0/0=z/0=0 in the natural extensions of fractions. We have to change our basic ideas for our space and world:
http://www.scirp.org/journal/alamt　 　http://dx.doi.org/10.4236/alamt.2016.62007
http://www.ijapm.org/show-63-504-1.html
http://www.diogenes.bg/ijam/contents/2014-27-2/9/9.pdf


再生核研究所声明359（2017.3.20）　ゼロ除算とは何か　―　本質、意義

ゼロ除算の理解を進めるために　ゼロ除算とは何か　の題名で、簡潔に表現して置きたい。　構想と情念、想いが湧いてきたためである。
基本的な関数y=1/x を考える。　これは直角双曲線関数で、原点以外は勿論、値、関数が定義されている。問題はこの関数が、x=0 　で　どうなっているかである。結論は、この関数の原点での値を　ゼロと定義する　ということである。　定義するのである。定義であるから勝手であり、従来の定義や理論に反しない限り、定義は勝手であると言える。原点での値を明確に定義した理論はないから、この定義は良いと考えられる。それを、y=1/0=0　と記述する。ゼロ除算は不可能であるという、数学の永い定説に従って、1/0　の表記は学術書、教科書にもないから、1/0=0　の記法は　形式不変の原理、原則　にも反しないと言える。―　多くの数学者は注意深いから、1/0=\infty　の表記を避けてきたが、想像上では x　が　0　に近づいたとき、限りなく　絶対値が大きくなるので、複素解析学では、表現1/0=\infty　は避けても、1/0=\infty　と考えている事は多い。（無限大の記号がない時代、アーベルなどもそのような記号を用いていて、オイラーは1/0=\inftyと述べ、それは間違いであると指摘されてきた。　しかしながら、無限大とは何か、数かとの疑問は　続いている。）。ここが大事な論点である。近づいていった極限値がそこでの値であろうと考えるのは、極めて自然な発想であるが、現代では、不連続性の概念　が十分確立されていて、極限値がそこでの値と違う例は、既にありふれている。―　アリストテレスは　連続性の世界観をもち、特にアリストテレスの影響を深く受けている欧米の方は、この強力な不連続性を中々受け入れられないようである。無限にいくと考えられてきたのが突然、ゼロになるという定義になるからである。　しかしながら、関数y=1/xのグラフを書いて見れば、原点は双曲線のグラフの中心の点であり、美しい点で、この定義は魅力的に見えてくるだろう。
定義したことには、それに至るいろいろな考察、経過、動機、理由がある。―　分数、割り算の意味、意義、一意性問題、代数的な意味づけなどであるが、それらは既に数学的に確立しているので、ここでは触れない。
すると、定義したからには、それがどのような意味が存在して、世の中に、数学にどのような影響があるかが、問題になる。これについて、現在、初等数学の学部レベルの数学をゼロ除算の定義に従って、眺めると、ゼロ除算、すなわち、　分母がゼロになる場合が表現上現れる広範な場合に 新しい現象が発見され、ゼロ除算が関係する広範な場合に大きな影響が出て、数学は美しく統一的に補充，完全化されることが分かった。それらは現在、３８０件以上のメモにまとめられている。しかしながら、世界観の変更は特に重要であると考えられる：

複素解析学で無限遠点は　その意味で1/0=0で、複素数0で表されること、アリストテレスの連続性の概念に反し、ユークリッド空間とも異なる新しい空間が　現れている。直線のコンパクト化の理想点は原点で、全ての直線が原点を含むと、超古典的な結果に反する。更に、ゼロと無限の関係が明らかにされてきた。
ゼロ除算は、現代数学の初等部分の相当な変革を要求していると考えられる。

以　上
付記：　The division by zero is uniquely and reasonably determined as 1/0=0/0=z/0=0 in the natural extensions of fractions. We have to change our basic ideas for our space and world

Division by Zero z/0 = 0 in Euclidean Spaces
Hiroshi Michiwaki, Hiroshi Okumura and Saburou Saitoh　International Journal of Mathematics and Computation Vol. 28(2017); Issue  1, 2017), 1 -16.　
http://www.scirp.org/journal/alamt　 　http://dx.doi.org/10.4236/alamt.2016.62007
http://www.ijapm.org/show-63-504-1.html
http://www.diogenes.bg/ijam/contents/2014-27-2/9/9.pdf

Relations of 0 and infinity
Hiroshi Okumura, Saburou Saitoh　and Tsutomu Matsuura：
http://www.e-jikei.org/…/Camera%20ready%20manuscript_JTSS_A…


再生核研究所声明312（2016.07.14）　ゼロ除算による　平成の数学改革を提案する

アリストテレス以来、あるいは西暦６２８年インドにおけるゼロの記録と、算術の確立以来、またアインシュタインの人生最大の懸案の問題とされてきた、ゼロで割る問題　ゼロ除算は、本質的に新しい局面を迎え、数学における基礎的な部分の欠落が明瞭になってきた。ここ７０年を越えても教科書や学術書における数学の基礎的な部分の変更は　かつて無かった事である。
そこで、最近の成果を基に現状における学術書、教科書の変更すべき大勢を外観して置きたい。特に、大学学部までの初等数学において、日本人の寄与は皆無であると言えるから、日本人が数学の基礎に貢献できる稀なる好機にもなるので、数学者、教育者など関係者の注意を換気したい。―　この文脈では稀なる日本人数学者　関孝和の業績が世界の数学に活かせなかったことは　誠に残念に思われる。
先ず、数学の基礎である四則演算において　ゼロでは割れない　との世の定説を改め、自然に拡張された分数、割り算で、いつでも四則演算は例外なく、可能であるとする。山田体の導入。その際、小学生から割り算や分数の定義を除算の意味で　繰り返し減法（道脇方式）で定義し、ゼロ除算は自明であるとし　計算機が割り算を行うような算法で　計算方法も指導する。―　この方法は割り算の簡明な算法として児童に歓迎されるだろう。
反比例の法則や関数y=1/xの出現の際には、その原点での値はゼロであると　定義する。その広範な応用は　学習過程の進展に従って　どんどん触れて行くこととする。
いわゆるユークリッド幾何学の学習においては、立体射影の概念に早期に触れ、ゼロ除算が拓いた新しい空間像を指導する。無限、無限の彼方の概念、平行線の概念、勾配の概念を変える必要がある。どのように、如何に、カリキュラムに取り組むかは、もちろん、慎重な検討が必要で、数学界、教育界などの関係者による国家的取り組み、協議が必要である。重要項目は、直角座標系で　ｙ軸の勾配はゼロであること。真無限における破壊現象、接線などの新しい性質、解析幾何学との美しい関係と調和。すべての直線が原点を代数的に通り、平行な２直線は原点で代数的に交わっていること。行列式と破壊現象の美しい関係など。
大学レベルになれば、微積分、線形代数、微分方程式、複素解析をゼロ除算の成果で修正、補充して行く。複素解析学におけるローラン展開の学習以前でも形式的なローラン展開(負べき項を含む展開)の中心の値をゼロ除算で定義し、広範な応用を展開する。特に微分係数が正や負の無限大の時、微分係数をゼロと修正することによって、微分法の多くの公式や定理の表現が簡素化され、教科書の結構な記述の変更が要求される。媒介変数を含む多くの関数族は、ゼロ除算　算法で統一的な視点が与えられる。多くの公式の記述が簡単になり、修正される。
複素解析学においては　無限遠点はゼロで表現されると、コペルニクス的変更（無限とされていたのが実はゼロだった）を行い、極の概念を次のように変更する。極、特異点の定義は　そのままであるが、それらの点の近傍で、限りなく無限の値に近づく値を位数まで込めて取るが、特異点では、ゼロ除算に言う、有限確定値をとるとする。その有限確定値のいろいろ幾何学な意味を学ぶ。古典的な鏡像の定説；原点の　原点を中心とする円の鏡像は無限遠点であるは、誤りであり、修正し、ゼロであると　いろいろな根拠によって説明する。これら、無限遠点の考えの修正は、ユークリッド以来、我々の空間に対する認識の世界史上に置ける大きな変更であり、数学を越えた世界観の変更を意味している。―　この文脈では天動説が地動説に変わった歴史上の事件が想起される。
ゼロ除算は　物理学を始め、広く自然科学や計算機科学への大きな影響が期待される。しかしながら、ゼロ除算の研究成果を教科書、学術書に遅滞なく取り入れていくことは、真智への愛、真理の追究の表現であり、四則演算が自由にできないとなれば、人類の名誉にも関わることである。ゼロ除算の発見は　日本の世界に置ける顕著な貢献として世界史に記録されるだろう。研究と活用の推進を　大きな夢を懐きながら　要請したい。
以　上
追記：
(2016) Matrices and Division by Zero z/0 = 0. Advances in Linear Algebra & Matrix Theory, 6, 51-58.
投稿者 時刻:

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)