???? ??????? ??????? ?? ???? ????? ??? ????? ?? Excel?

?????? ????????? ?????? ?????????
???? ???????: 78113 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

??????

?????? ??? ??????? ??????? ???? ???? ??? ?????? Microsoft Excel ?????? ????? ??????? ??????? ???????. ?? ???? ??? ??? ????? ??? ??????? ?? ????? ????? ??????? ??????? ???????? ?? ?? ?????.

???? ????

?? ????? ?????? Microsoft Excel ???????? IEEE 754 ???? ????? ?????? ????? ??????? ??????? ???????. ???? IEEE ??? "???? ?????? ???????? ???????? ???????????"? ??? ???? ????? ???? ?????? ?????? ?????? ????? ????????? ????????? ???????? ??? ????? ????. ??? ??? ??????? 754 ???? ????????? ???????? ??? ???? ???? ???? ???? ?????? ????? ????? ????? ??????? ??????? ??? ???? ??????? ?????. ??? ????? ????? ????? ???????? ????? ??? ??? ???? ???? ?????? ????? ??????? ??????? ?? ????? ?????? ?????? ???????? ???????? ????? ??? ?? ??. ??? ??????? ??????? 754 ?? ????? ??????? ??????? ???????? ???????? ??????? ?????? ????? ????????? ??????? ?????? ????????? ????????? ?? ????? ?????? ???????? ????? ?????? ???? ??????? ??????? ??? ??? ??? ??????? Intel ?Motorola ?Sun ?MIPS.

??? ????? ???????? ???? ?? ???? ??? ????? ?? ??? ?? ??? ????. ???? ???? ??????? ???? ??? ????? 10/1 ????? ????? ?????? ??? 0.1. ??? ???? ???? ??? ????? ??????? ??????? ?? ????? ?????? ??????? ???????
0001100110011100110011 (?????)
????? ?????? ??? ?? ?? ?????. ?? ???? ??? ??? ????? ???? ????? ?????? (?????). ????????? ??? ????? ??? ????? ??? ?????? ?????? ?-2.8E-17 ??? ??????.

??? ???? ???? ??? ?????? ???? ?????? ??????? IEEE 754 ????? ????? ??? ???? ???? ????:
  • ???? ??????/???? ?????? ??????
  • ?????
  • ??????? ???????? ????????

??????? ????

???? ??????/???? ?????? ??????

??? ???? ????? ????????? ??? ???? ???? ???? ???? ???????. ??? ??? ?? ??????? ???? ??? ????? ????? ??? ?????? ???????? ??? ????? ?????? ?? ????? ?????? ???? ???? ?????? ???? ??????? ?????. ??????? ??????? Excel? ????? ?????? ???? ???? ?????? ?? 1.79769313486232E+308 ?????? ?????? ?????? ???? ???? ?????? ?? 2.2250738585072E-308.

??????? ???? ??? ???????? ?????? ?? IEEE 754

  • ????? ???? ??????: ???? ????? ???? ?????? ????? ??? ????? ??? ???? ?????? ?????. ?? IEEE ??????? Excel? ???? ?????? 0 (???????? ?? IEEE ???? ????? ?-0? ?? ??? ?? ?????? Excel ??? ???? ??? ???????).
  • ????? ???? ??????: ???? ????? ???? ?????? ????? ???? ????? ?????? ???? ?????. ??? ??? ??????? ?????? ?????? Excel ????? ????? ?????? ?? (?#NUM!?).

??????? ???? ?? ??? ???????? ?????? ?? IEEE 754

  • ????? ???? ?????: ??? ??????? ??? ??? ???? ????? ???????? ???? 0. ??? ??? ??????? ??? ????? ????? ?????? ?? ????? ?????? ???? ?? ????? ??? ?????? ?????? 1. ?????? ????? ?? ????? ?????? ????? ?? ?????? ??? ?????. ????? ??????? ???????? ?? ??????? ??????? ???? ?????? ??? ??? ???? ??? ?? ?????.
    ????: ????? ????? ???? ??? ?????? ??? ?????? ?????? 1. ??? ???? ??????? ??? ??? ????? ?????? ???? 0011001? ???? ????? ???? ??? ?????? 10011001 ???? ???? ?????? ?????? 1. ?? ????? ????? ???? ?? ??? ?????? ??? ?????? ?????? ????? ???? ?? ???? 0011001? ???? ??? ????? ???? ?? ??? ??????. ?? ??? ??????? ????? ????? ???? ??? ?????? ?? ????? ????? ????? ????? (10011001) ????? ????? ????? ???? ?? ??? ?????? ?? ??? ????? ????? ????? (11001) ?????? ????? ??? ?????.

    ????? ??????? ???? ?? ??? ??????? ???? ????? ???? ?????? ?????? ?????? ??????? ????? ?? ???? ?????? ???????. ???? ?? ???? Microsoft ?????? ??? ????? ????????? ?? ?????????? ??? ??????? ???? ?? ??? ??????? ???????? ????? ?? ??? ????? ?? ??????? ??????? ???????. ??? ?? ???? ????? ??? ???? ?? ???????? ????????.
  • ?????? ?? ?? ????? ?????/?????: ???? ?????? ?? ?? ????? ??? ?????? ??? 0. ?? ???? ?????? Excel ?????? ?? ?? ?????? ????? ?? ???? ???? ????? ?#DIV/0!? ?? ??? ???????.
  • ??? ???? (NaN): ??? ??????? NaN ???? ???????? ??? ??????? (??? ?? ?? ?????/?? ?? ????? ?? ?? ?? ?????-?? ?? ????? ?? ????? ???????? ????? -1). ???? NaNs ???? ??????? ????????? ??? ???? ????? ??? ?????. ???? Excel ????? ?? ??? ?????? ??? ??? ?#NUM!? ?? ?#DIV/0!?.

?????

??? ????? ??? ??????? ??????? ?????? ?? ????? ????? ???? ???? ?? ??: ??????? ????? ?????? ??????.
?? ??? ??????????? ??? ??????
?? ??????? 1?? ???? 11?? ???? 1????? ?????? ??? 52
???? ??????? ?????? ????? ????? (????? ?? ?????) ????? ???? ?????? ?? ????? 2 ???? ??? ??? ????? ???? ?? ???? (???? ???? ??????/?????? ????? 2 ?? ?+1,023 ??-1,022) ????? ???? ????? ?????? ?????? ????? ???????. ??? ????? ??????? ???????? ????? ?????? ?????? ??? ???? ????? ??? ???? ????? ????? ???????? (???? ?? ??????? ??????).

??? ????? ?? ?? ????? ?????? ????? ??????? ??????. ??? ??? ?? ????? ????? ????? ???? ???? ?????? ???? ????? ??? ??? ????? (????? ??????) ???? ??? ???????. ?? ??? ?????? ?????? ??????? Excel? ?????? ?? ?? ?????? Excel ????? ?? ????? ????? ?????? ??? 1.79769313486232E308 ?2.2250738585072E-308? ??? ??? ????? ?????? ???? ??? ???? ?? ??? ?? ?????. ???? ??? ??????? ????? ?????? ??????? ?????? ???????? IEEE 754 ???? ????? ??????? ????? ??????? Excel. ??? ???? ??? ??????? ?? ????? ?? ????? ????? ???????? ?????? ?????.

??? ??? ????? ??????? ??????? ???????? ??????? ??? ???? ???? ?? ???? ???????:
X = ??? ???? * 2^(???? - ????????)
????? ?????? ?? ????? ?????? ???? ??? ?????? ?????? ??? ??? ?????? ????? ????? ???? ???? ???? ?? ?????? ?????? 1. ???? ??????? ?? ???? ???????? ??? ????? ?????? ??? ???? ?? ?? ???? ?????. ????? ????? ???? ?? ????. ???? ?? ???? ??????? ??????? ??? ???? ??????? ???? ?????? ??? ??? ???? ???? ??????? ???????? ???????? ????? ???????? ????? ?????? ?????? ???? ???? ???? ???? ????? ?? ????? 1? ????? ???? ?? ???? ??? ?????? ????????.

???????? ?? ???? ???????? ????????? ????? ???????? ??? ????? ????? ???????. ???? ???????? ????? ?????? ????? ??????? ?? 127 ?1023 (????? ??????) ??????? ?????? ????? ????????. ???? ?????? Excel ?????? ??????? ???????? ????? ????????.

???? ??? ??????? ????? ????? ????

?? ?????? ?????? ?? ???? ????:
   A1: 1.2E+200
   B1: 1E+100
   C1: =A1+B1
???? ?????? ??????? ???? ???? ?? ?????? C1 ?? 1.2E+200 ?? ??? ?????? ???????? ?? ?????? A1. ??? ??????? ??? ??? ????????? ??? ???????? A1 ?C1 ???????? ?????? IF? ??? ???? ??????? ?IF (A1=C1)?? ????? ?????? TRUE. ????? ??? ??? ??????? IEEE ?????? ?????? ?? ??? ???? ??? ?? ?????. ??? ????? ?? ????? ??????? ???????? ?????? ?? ????? ?????? Excel ??? ????? 100 ??? ?? ?????.

???? ??? ??????? ????? ????? ????

?? ?????? ?????? ?? ???? ????:
   A1: 0.000123456789012345
   B1: 1
   C1: =A1+B1
???? ?????? ??????? ???? ???? ?? ?????? C1 ?? 1.00012345678901 ???? 1.000123456789012345. ????? ??? ??? ??????? IEEE ?????? ?????? ?? ??? ???? ??? ?? ?????. ??? ????? ?? ????? ??????? ???????? ?????? ?? ????? ?????? Excel ??? ????? 19 ??? ?? ?????.

????? ????? ?????

???? ?????? Excel ??????? ???????? ?????? ????? ???????. ?????? ROUND ????? ?????? ????? ??? ?? ????? ?? ????? ????? ??? ?? ?????.

??????? ??????: ?????? ROUND

???? ?????? ?????? ???????? ???????? ??????? ?????? ???????? ?????? ROUND ?????? ?? ??? ??? ???? ?????. ??? ???? ?? ????????? ??? ?????? ???? ???? ???? ?????.
   A1: 1.2E+200
   B1: 1E+100
   C1: =ROUND(A1+B1,5)?
???? ?????? 1.2E+200.
   D1: =IF(C1=1.2E+200, TRUE, FALSE)?
???? ?????? ?? ?????? TRUE.

??????? ???????: ????? ??? ?? ?????

?? ??? ???????? ?? ????? ?? ??? ????? ??????? ?? ??????? ??? ????? ???? ???? ??? ???? ???????? ?????? ????? ??? ?? ?????. ???? ??? ?????? ?????? ???? ?? ??? ????? ?? ???? ????? ????? ?????? ????????. ?????? ??? ??????? ???? ???????? ?????????:
  1. ?? ?????? Excel 2003 ?????????? ??????? ?? Excel? ???? ??? ?????? ?? ??????? ?????.
  2. ?? ????? ??????? ????? ???? ?????? ???? ???????? ????? ??? ?? ?????.
  1. ?? ?????? Excel 2007? ???? ??? ?? Microsoft Office? ?? ???? ??? ?????? Excel? ?? ???? ??? ????? ?????? ??????.
  2. ?? ????? ??? ???? ??? ??????? ??? ?????? ???? ?????? ?? ??? ???? ???????? ????? ????? ??? ?? ?????.
??? ???? ??????? ??? ?????? ????? ???? ???? ??????? ??????? ?? ????? ?????? ????? ??? ?? ?????? ????? ???? ????? ???? ???? ??? ????? ?????? ??? ??? ??????. ???? ??? ?????? ??? ?????? ????? ??? ?? ??? ???? ????? ?????. ?? ???? ??????? ?? ??? ?????? ???????? ???????? ????????. ????? ???? ???? ?????? ??? ????? ??? ??????.

????? ??????? ???????? ????????? ???????? ???????? ??????? ?? ?????

???? ????? ???? ????? ????? ?????? ????? ??????? ??????? ???? ?????? ??? ??? ?? ???? ??? ??????? ???????? ???????? ??? ??????? ??? ???????? ?? ??????? ??????? 10 ??????????? ????? ???? ?????. ???? ?????? ?????? ?????? ??? ??? ?? ?????? 0.1 ??????????. ??? ????? ?? ??????? ??? ??? ??????? ????? ????? ?? ??????? 10? ???? ??? ????? ???? ??? ???? ???????? ??????? ?? ????? ??????? ??????? ?????? ????? ??? ?????? ?? ????? ??????:
000110011001100110011 (?????)
?? ???? ??????? IEEE 754 ?????? ??? ????? ???? ??? ???? ??? ???? ?????? ?? ???? ?????? ?? ????? ??????? ?? ?????? ??????. ???? ???? ??? ???? ??? ??? ????? 0.000000000000000028 ?? ????? ????? -2.8E-17.

?? ???? ??? ??? ?????? ??????? ???????? ??? ????? ?????? 0.0001? ???? ????? ????. (??? ????? 0.0001 ????? ??????? ??????? ???? ??? ??). ???? ?? ??? ????? ???? ???? ???? ??? ??????? ??? ????? ?????? 3/1 ?????? ?? ???? ??? ???? 0.33333333333333333333 ?????).

??? ???? ????? ???? ???? ???? ?? Microsoft Visual Basic for Applications
   Sub Main()?
      MySum = 0
      For I% = 1 To 10000
         MySum = MySum + 0.0001
      Next I%?
      Debug.Print MySum
   End Sub
?????? 0.999999999999996 ?????. ?????? ?????? ???? ???? ?? ??? ????? 0.0001 ???? ????? ????? ????? ??? ???????.

???? ??? ????? ??? ????.

  1. ?? ?????? ?????? ??? ???? ????:
       A1: =(43.1-43.2)+1
  2. ???? ??? ?????? ?????? ??? ?????? A1? ?? ???? ??? ????? ???????. ?? ????? ??????? ???? ???? ??? ???? ??? ?????. ?? ?????? ??????? ??????? ??? 15.
???? ?????? Excel ???? 0.899999999999999 ????? ?? 0.9. ???? ???? ??? ???? (43.1-43.2) ?????? ?? ????? -0.1 ???? ???? ????? ???? ????? ?????? ?? ????? -0.1 ?? ??????? ????????.

???? ??? ???? ???? ????? ??? ?????

  1. ?? ?????? Excel 95 ?? ????????? ???????? ?? ?????? ?????? ??? ???? ????:
       A1: =1.333+1.225-1.333-1.225
  2. ???? ??? ?????? ?????? ??? ?????? A1? ?? ???? ??? ????? ???????. ?? ????? ??????? ???? ???? ??? ???? ??? ?????. ?? ?????? ??????? ??????? ??? 15.
???? ?????? Excel 95 ????? -2.22044604925031E-16 ????? ?? 0.

???? ?????? Excel 97? ?? ???? ?????? ??? ????????? ???? ????? ????? ??? ???????. ??? ??? ?? ??????? ?? ???? ???? ???? ?????? ??????? ?? ????? ???? ??? ??? ????? ?? ????? ????? ????? ?????? Excel 97 ?????????? ?????? ???????? ?? ??? ????? ???? ????? ????? ??? ????????? ??? ????? ?? ?????. ???? ?????? ????? ??? ?????? ?????? ????? ?? ?????? Excel 97 ?? ????????? ?????? ??? ????? 0 ?? 0.000000000000000E+00 ???????? ??????. ????? ?? ?????????? ???? ??? ???? ????????? ????????? ??????? ?? "????? ????? Microsoft" (?? ????? ????? ????????? ??? ???????? ??? ????? ?????? ?????????? (????? ?? ??? ?????? ???)):
172911 ?????? ??? ???? ??? ???? ??? ??? ?? ??? ?? ???? ????/???? ????
214373 ?XL2000: ?????? ??? ???? ??? ???? ??? ??? ?? ??? ?? ???? ????/???? ????
????? ?? ????????? ??? ????? ??????? ??????? ???????? IEEE 754? ?????? ????? ????? "???? ???????? ????????" ????????:
http://www.ieee.org

http://stevehollasch.com/cgindex/coding/ieeefloat.html

?????

????? ?? ????????? ??? ????? ?????? ??? ??? ???????? ???? ??? ??? ??????? ?????? ?????? ?? "????? ????? Microsoft" (?? ????? ??? ??????? ??? ???????? ??? ????? ?????? ?????????? (????? ?? ??? ?????? ???)):
214118 ????? ????? ????? ??????? ?? ???? ??????? ???????

???????

???? ???????: 78113 - ????? ??? ??????: 29/????? ??????/1431 - ??????: 7.0
????? ???
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X ?? Mac
  • Microsoft Excel 2001 ?? Macintosh
  • Microsoft Excel 98 ?? Macintosh
????? ??????: 
kbinfo KB78113

????? ???????

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com