אריתמטיקה של נקודה צפה עלולה להחזיר תוצאות לא מדויקות ב- Excel

תרגומי מאמרים תרגומי מאמרים
Article ID: 78113 - View products that this article applies to.
הרחב הכל | כווץ הכל

On This Page

תקציר

מאמר זה מתאר כיצד Microsoft Excel מאחסן ומחשב מספרי נקודה צפה. פעולות אלה עלולות לשפיע על התוצאות של חלק מהמספרים או הנוסחאות עקב פעולת עיגול ו/או חיתוך נתונים.

מבט כולל

Microsoft Excel תוכנן בהתבסס על מפרט IEEE 754 ביחס לאחסון ולחישוב מספרי נקודה צפה. IEEE הוא קיצור של Institute of Electrical and Electronics Engineers, גוף בינלאומי שבין שאר הדברים, קובע סטנדרטים לתוכנות מחשב ולחומרת מחשב. מפרט 754 הוא מפרט שאומץ בקנה מידה רחב, המתאר כיצד יש לאחסן מספרי נקודה צפה במחשב בינארי. הפופולריות של מפרט זה נובעת מכך שהוא מאפשר לאחסן מספרי נקודה צפה בכמות שטח סבירה, והחישובים המתבצעים במהירות יחסית. תקן 754 נמצא בשימוש ביחידות נקודה צפה ובמעבדי נתונים נומריים כמעט בכל המיקרו-מעבדים מבוססי המחשב כיום, המיישמים מתמטיקה של נקודה צפה, כולל מעבדים של Intel,? Motorola,? Sun ו-MIPS.

בעת אחסון מספרים, ניתן לייצג כל מספר או מספר שבר על-ידי מספר בינארי תואם. לדוגמה, ניתן לייצג את השבר 1/10 במערכת מספרים עשרונית כ- 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). מספרי NaN מאפשרים לתוכנית להמשיך אחרי פעולה לא חוקית. Excel לעומת זאת יוצר באופן מיידי שגיאה כגון ?#NUM! או ?#DIV/0!.

דיוק

מספר נקודה צפה מאוחסן בצורה בינארית בשלושה חלקים בתוך טווח של 65 סיביות: הסימן, המעריך והמנטיסה.
כווץ את הטבלההרחב את הטבלה
?1 Sign Bit?11 Bit Exponent?1 Implied Bit?52 Bit Mantissa
הסימן מאחסן את סימן המספר (חיובי או שלילי), המעריך מאחסן את החזקה של 2 שבה המספר מועלה או מורד (החזקה המקסימלית/מינימלת של 2 היא +1,023 ו- -1,022) והמנטיסה מאחסנת את המספר בפועל. אזור אחסון סופי זה עבור המנטיסה קובע עד כמה שני מספרי נקודה צפה סמוכי יכולים להיות קרובים (כלומר, הדיוק).

המנטיסה והמעריך מאוחסנים כרכיבים נפרדים. כתוצאה מכך, מידת הדיוק האפשרית עשויה להשתנות בהתאם לגודל המספר (המנטיסה) שעליו מבצעים את הפעולה. במקרה של Excel, על אף ש- Excel יכול לאחסן מספרים מ- 1.79769313486232E308 עד 2.2250738585072E-308, באפשרותו לעשות זאת ברמת דיוק של 15 ספרות בלבד. מגבלה זו היא תוצאה ישירה של התאמה מחמירה למפרט IEEE 754 ואינה מהווה מגבלה של Excel. רמת דיוק זו נמצאת גם בתוכניות אחרות של גיליון אלקטרוני.

מספרי נקודה צפה מיוצגים בתבנית הבאה, שבה exponent הוא המעריך הבינארי:
X =
Fraction * 2^(exponent - bias)Fraction X = Fraction * 2^(exponent - bias) Fraction הוא חלק השבר המנורמל של המספר. מנורמל מאחר שהמעריך מותאם כך שהסיבית המובילה תהיה תמיד 1. באופן זה, אין צורך באחסון הסיבית וניתן לקבל סיבית נוספת של דיוק. זוהי הסיבה שבגללה ישנה סיבית משתמעת. מצב זה דומה לסימון מדעי, שבו ניתן להגדיר את המעריך כך שיכלול ספרה אחת משמאל לנקודה העשרונית; למעט בסיס בינארי, תמיד ניתן להגדיר את המעריך כך שהסיבית הראשונה תהיה 1, מאחר שרק הספרות 1 ו- 0 קיימות.

Bias הוא ערך הסטייה המשמש כדי למנוע את הצורך באחסון מעריכים שליליים. הסטייה עבור מספרים בעלי דיוק יחיד היא 127 ועבור מספרי בעלי דיוק כפול 1,023 (עשרוני). Excel מאחסן מספרים באמצעות דיוק כפול.

דוגמה לשימוש במספרים גדולים מאד

הזן את הנתונים הבאים לחוברת עבודה:
   A1: 1.2E+200
   B1: 1E+100
   C1: =A1+B1
הערך המתקבל בתא C1 יהיה 1.2E+200, אותו ערך כמו בתא A1. למעשה, אם תשווה את תאים A1 ו- C1 באמצעות הפונקציה IF, לדוגמה IF(A1=C1), התוצאה תהיה TRUE. מצב זה נגרם על-ידי מפרט IEEE המאחסן מספרים ברמת דיוק של 15 ספרות משמעותיות בלבד. כדי שתוכל לאחסן את החישוב לעיל, דרושה ל- Excel רמת דיוק של 100 ספרות לפחות.

דוגמה לשימוש במספרים קטנים מאד

הזן את הנתונים הבאים לחוברת עבודה:
   A1: 0.000123456789012345
   B1: 1
   C1: =A1+B1
הערך המתקבל בתא C1 יהיה 1.00012345678901 במקום 1.000123456789012345. מצב זה נגרם על-ידי מפרט IEEE המאחסן מספרים ברמת דיוק של 15 ספרות משמעותיות בלבד. כדי שתוכל לאחסן את החישוב לעיל, דרושה ל- Excel רמת דיוק של 19 ספרות לפחות.

תיקון שגיאות דיוק

Excel מציע שתי שיטות בסיסיות לפיצוי על שגיאות עיגול: הפונקציה ROUND ואפשרות חוברת העבודה רמת הדיוק המוצגת או הגדר רמת דיוק כפי שמוצג. שיטה 1: הפונקציה 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.

שיטה 2: רמת הדיוק המוצגת

במקרים מסוימים, ייתכן שתוכל למנוע מצב שבו שגיאות עיגול ישפיעו על עבודתך באמצעות האפשרות רמת הדיוק המוצגת. אפשרות זו כופה שהערך של כל מספר בגליון העבודה יהיה הערך המוצג. כדי להפעיל אפשרות זו, בצע את הפעולות הבאות:
  1. ב- Excel 2003 ובגירסאות קודמות, לחץ על אפשרויות בתפריט כלים.
  2. בכרטיסיה חישוב, לחץ כדי לסמן את תיבת הסימון ?רמת הדיוק המוצגת.
  1. ב- Excel 2007, לחץ על לחצן Microsoft Office, לחץ על אפשרויות Excel ולאחר מכן לחץ על הקטגוריה מתקדם.
  2. בחלק בעת חישוב חוברת עבודה זו, בחר את חוברת העבודה הרצויה ולאחר מכן בחר בתיבת הסימון הגדר רמת דיוק כפי שמוצג.
לדוגמה, אם תבחר תבנית מספר שמציגה שני מקומות עשרוניים ולאחר מכן תפעיל את האפשרות רמת הדיוק המוצגת, רמת הדיוק מעבר לשתי ספרות תאבד כאשר תשמור את חוברת העבודה. אפשרות זו משפיעה על חוברת העבודה הפעילה, כולל כל גליונות העבודה הכלולים בה. לא ניתן לבטל אפשרות זו ולשחזר את הנתונים שאבדו. מומלץ לשמור את חוברת העבודה לפני שתפעיל אפשרות זו.

מספרים בינאריים חוזרים וחישובים המובילים לתוצאות הקרובות לאפס

בעיה מבלבלת אחרת עם אחסון מספרי נקודה צפה בבסיס בינארי היא בעיה שבה מספרים מסוימים, שהם מספרים סופיים ואינם חוזרים על עצמם בבסיס עשרוני 10, הם מספרים אינסופיים שחוזרים על עצמם בבסיס בינארי. הדוגמה הנפוצה ביותר לכך היא הערך 0.1 על כל הווריאנטים שלו. על אף שניתן לייצג מספרים אלה באופן מושלם בבסיס 10, אותו מספר בתבנית בינארית הופך למספר הבינארי הבא שחוזר על עצמו כאשר הוא מאוחסן במנטיסה:
000110011001100110011 (וכן הלאה)
מפרט IEEE 754 אינו כולל הוראה מיוחדת עבור שום מספר, הוא מאחסן את מה שניתן לאחסן במנטיסה וחותך את השאר. תוצאה זו היא שגיאה של כ- -2.8E-17, או 0.000000000000000028 כאשר היא מאוחסנת.

גם שברים עשרוניים נפוצים, כגון 0.0001 בבסיס עשרוני, אינם ניתנים לייצוג מדויק בתבנית בינארית. (0.0001 הוא שבר בינארי שחוזר על עצמו בפרקים של 104 סיביות). הדבר דומה לסיבה שבגללה לא ניתן לייצג את השבר 1/3 באופן מדויק בתבנית עשרונית (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.
במקום להציג 0.9, Excel מציג 0.899999999999999. מאחר ש- (43.1-43.2) מחושב תחילה, -0.1 מאוחסן באופן זמני והשגיאה של אחסון -0.1 מוצגת בחישוב.

דוגמה שבה ערך מגיע לאפס

  1. ב- Excel 95 או בגירסאות מוקדמות יותר, הזן את הנתונים הבאים לחוברת עבודה חדשה: A1: =1.333+1.225-1.333-1.225
  2. לחץ באמצעות לחצן העכבר הימני על התא A1 ולאחר מכן לחץ על עיצוב תאים. בכרטיסיה מספר, לחץ על מדעי תחת קטגוריה. הגדר את מקומות עשרוניים ל- 15.
במקום להציג 0, Excel 95 מציג -2.22044604925031E-16.

עם זאת, Excel 97 מציג אופטימיזציה שמנסה לתקן בעיה זו. אם פעולת חיבור או חיסור תוביל לערך של אפס או קרוב מאוד לאפס, Excel 97 וגירסאות מתקדמות יותר יפצו על כל שגיאה שנוצרה כתוצאה מהמרת אופרנד אל מספר בינארי וממנו. כאשר הדוגמה לעיל מתבצעת ב- Excel 97 ובגירסאות מתקדמות יותר, מוצגת התוצאה הנכונה של 0 או 0.000000000000000E+00 בסימון מדעי. לקבלת מידע נוסף, לחץ על מספרי המאמרים שלהלן כדי להציגם מתוך מאגר הידע Microsoft Knowledge Base:
172911 תוצאה שגויה בעת העלאת 10 בחזקה גדולה מאוד/קטנה מאוד (ייתכן שקישור זה מפנה לתוכן שחלק ממנו או כולו מופיע באנגלית)
214373 תוצאה שגויה בעת העלאת 10 בחזקה גדולה מאוד/קטנה מאוד (ייתכן שקישור זה מפנה לתוכן שחלק ממנו או כולו מופיע באנגלית)
לקבלת מידע נוסף אודות מספרי נקודה צפה ומפרט IEEE 754, בקר באתריה אינטרנט הבאים:
http://www.ieee.org

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

מידע נוסף

לקבלת מידע נוסף על הדרך לעקיפת שגיאות אלה, לחץ על מספר המאמר להלן כדי להציגו מתוך מאגר הידע ?:Microsoft Knowledge Base
214118 כיצד לתקן שגיאות עיגול באריתמטיקה של נקודה צפה (ייתכן שקישור זה מפנה לתוכן שחלק ממנו או כולו מופיע באנגלית)

מאפיינים

Article ID: 78113 - Last Review: יום ראשון 18 ספטמבר 2011 - Revision: 2.0
המידע במאמר זה חל על:
  • Microsoft Excel 2010
  • Microsoft Office Excel 2008 for Mac
  • Microsoft Office Excel 2007
  • 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 for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh
  • Microsoft Office Excel 2003
מילות מפתח 
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