حسابی با نقطه شناور ممکن است نتایج نادرست در اکسل داشته باشد

در این مقاله به بحث در مورد چگونگی خرید مایکروسافت و محاسبه شماره های شناور می پردازد. این ممکن است به دلیل گردآوری یا کوتاه شدن داده بر نتایج برخی اعداد یا فرمول ها تأثیر بگذارد.

بررسی اجمالی

Microsoft Excel در اطراف مشخصات IEEE 754 طراحی شده است تا نحوه ذخیره و محاسبه شماره های نقطه شناور را تعیین کند. IEEE انستیتوی مهندسان برق و الکترونیک است ، یک بدنه بین المللی که از جمله موارد دیگر ، استانداردهای نرم افزار و سخت افزار رایانه را تعیین می کند. مشخصات 754 مشخصات بسیار گسترده ای است که توضیح می دهد که چگونه باید شماره های نقطه شناور در یک رایانه باینری ذخیره شود. این محبوبیت است زیرا اجازه می دهد تا شماره های نقطه شناور در فضای معقول و محاسبات ذخیره شوند و محاسبات نسبتاً سریع اتفاق بیفتد. استاندارد 754 در واحدهای شناور و پردازنده داده های عددی تقریباً در تمام ریز پردازنده های مبتنی بر رایانه شخصی امروز که ریاضیات نقطه شناور را اجرا می کنند ، از جمله پردازنده های اینتل ، موتورولا ، خورشید و MIPS استفاده می شود.

هنگامی که اعداد ذخیره می شوند ، یک عدد باینری مربوطه می تواند هر عدد یا تعداد کسری را نشان دهد. به عنوان مثال ، کسری 1/10 را می توان در یک سیستم عدد اعشاری به عنوان 0. 1 نشان داد. با این حال ، همان تعداد در قالب باینری اعشاری باینری تکرار زیر می شود:

0001100110011100110011 (و غیره)

این می تواند بی نهایت تکرار شود. این تعداد را نمی توان در یک فضای محدود (محدود) نشان داد. بنابراین ، این تعداد در هنگام ذخیره تقریباً -2. 8 E-17 دور می شود.

با این حال ، محدودیت های مشخصات IEEE 754 وجود دارد که در سه دسته کلی قرار می گیرند:

• حداکثر/حداقل محدودیت ها
• دقت، درستی
• تکرار اعداد باینری

اطلاعات بیشتر

حداکثر/حداقل محدودیت ها

تمام رایانه ها حداکثر و حداقل تعداد دارند که می توان آنها را کنترل کرد. از آنجا که تعداد بیت های حافظه که در آن تعداد در آن ذخیره شده است محدود است ، نتیجه می گیرد که حداکثر یا حداقل تعداد قابل ذخیره سازی نیز محدود است. برای اکسل ، حداکثر تعداد قابل ذخیره سازی 1. 79769313486232E+308 و حداقل تعداد مثبت که می توان ذخیره کرد 2. 2250738585072E-308 است.

مواردی که در آن به IEEE 754 پایبند هستیم

• زیر جریان: زیر جریان هنگامی اتفاق می افتد که یک عدد تولید می شود که برای نمایش بسیار کوچک است. در IEEE و اکسل ، نتیجه 0 است (به استثنای IEEE مفهو م-0 ، و اکسل چنین نیست).
• سرریز: سرریز هنگامی رخ می دهد که یک عدد خیلی بزرگ باشد تا نشان داده شود. اکسل از نمایندگی ویژه خود برای این مورد استفاده می کند (#Num!).

مواردی که در آن ما به IEEE 754 پایبند نیستیم

اعداد غیر طبیعی: یک عدد غیر طبیعی شده توسط یک نماینده 0 نشان داده شده است. در این حالت ، کل تعداد در مانتیسا ذخیره می شود و مانتیسا هیچ پیشرو ضعیفی ندارد. 1. در نتیجه ، شما دقت را از دست می دهید و تعداد کوچکتر ، تعداد آن ، تعداد کوچکتر ،دقت بیشتری از بین می رود. اعداد در انتهای کوچک این محدوده فقط یک رقم از دقت دارند.

مثال: یک عدد عادی دارای یک پیشرو ضایعات 1. به عنوان مثال ، اگر مانتیسا 0011001 را نشان دهد ، تعداد عادی شده به دلیل ضمنی پیشرو ، 10011001 می شود. یک عدد غیر طبیعی دارای یک پیشرو ضمنی نیست ، بنابراین در مثال ما از 0011001 ،تعداد denormalized یکسان است. در این حالت ، تعداد عادی دارای هشت رقم قابل توجه (10011001) است در حالی که تعداد denormalized دارای پنج رقم مهم (11001) است که صفرهای پیشرو ناچیز هستند.

اعداد غیر طبیعی در واقع یک راه حل هستند که اجازه می دهد تا تعداد کمتری از حد پایین تر از حد طبیعی ذخیره شود. مایکروسافت این بخش اختیاری از مشخصات را پیاده سازی نمی کند زیرا اعداد denormalized با توجه به ماهیت بسیار دارای تعداد متغیر از ارقام قابل توجه هستند. این می تواند خطای قابل توجهی را وارد محاسبات کند.

بی نهایت مثبت/منفی: بی نهایت هنگامی اتفاق می افتد که با 0 تقسیم می شوید. اکسل از بی نهایت پشتیبانی نمی کند ، بلکه به #div/0 می دهد!خطا در این موارد

Not-A-Number (NAN): NAN برای نشان دادن عملیات نامعتبر (مانند بی نهایت/بی نهایت ، بی نهایت-بی نهایت یا ریشه مرب ع-1) استفاده می شود. NANS به یک برنامه اجازه می دهد تا یک عملیات نامعتبر را ادامه دهد. در عوض اکسل بلافاصله خطایی مانند #Num ایجاد می کند!یا #div/0!.

دقت، درستی

یک شماره نقطه شناور در سه قسمت در یک محدوده 65 بیتی به صورت باینری ذخیره می شود: علامت ، نمایشگر و مانتیسا.

علامت علامت شماره (مثبت یا منفی) را ذخیره می کند ، نماینده قدرت 2 را که تعداد آن افزایش یافته یا پایین می آید ذخیره می کند (حداکثر/حداقل قدرت 2 +1،023 و-1،022 است) ، و Mantissa ذخیره می کندشماره واقعیفضای ذخیره سازی محدود برای مانتیسا چقدر نزدیک دو شماره نقطه شناور مجاور می تواند باشد (یعنی دقت).

مانتیسا و نماینده هر دو به عنوان مؤلفه های جداگانه ذخیره می شوند. در نتیجه ، میزان دقت ممکن ممکن است بسته به اندازه تعداد (مانتیسا) دستکاری شود. در مورد اکسل ، اگرچه اکسل می تواند شماره ها را از 1. 79769313486232E308 تا 2. 2250738585072E-308 ذخیره کند ، اما فقط می تواند در 15 رقم از دقت انجام دهد. این محدودیت نتیجه مستقیم پیروی از مشخصات IEEE 754 است و محدودیت اکسل نیست. این سطح از دقت در سایر برنامه های صفحه گسترده نیز یافت می شود.

اعداد نقطه شناور به شکل زیر نشان داده می شوند ، که در آن نمایانگر باینری است:

x = کسر * 2^(نمایندگی - تعصب)

کسری قسمت کسری عادی از عدد است ، عادی شده است زیرا نماینده تنظیم می شود به گونه ای که بیت پیشرو همیشه یک است. به این ترتیب ، لازم نیست که ذخیره شود و شما یک دقت دیگر نیز بدست می آورید. به همین دلیل یک بیت ضمنی وجود دارد. این شبیه به نماد علمی است ، جایی که شما نماینده را دستکاری می کنید تا یک رقم در سمت چپ نقطه اعشاری داشته باشید. به جز در باینری ، شما همیشه می توانید نماینده را دستکاری کنید تا بیت اول 1 باشد ، زیرا فقط 1 و 0 وجود دارد.

تعصب مقدار تعصب است که برای جلوگیری از ذخیره نمایندگان منفی استفاده می شود. تعصب برای اعداد تک با دقت 127 و 1،023 (اعشاری) برای شماره های دو برابر است. اکسل با استفاده از دو دقت ، شماره ها را ذخیره می کند.

به عنوان مثال با استفاده از اعداد بسیار زیاد

موارد زیر را به یک کتاب کار جدید وارد کنید:

مقدار حاصل در سلول C1 1. 2E+200 ، همان مقدار سلول A1 خواهد بود. در واقع اگر سلولهای A1 و C1 را با استفاده از عملکرد IF مقایسه کنید ، به عنوان مثال اگر (A1 = C1) ، نتیجه درست خواهد بود. این ناشی از مشخصات IEEE در ذخیره تنها 15 رقم قابل توجه از دقت است. برای اینکه بتوانید محاسبه فوق را ذخیره کنید ، اکسل حداقل به 100 رقم از دقت نیاز دارد.

به عنوان مثال با استفاده از اعداد بسیار اندک

موارد زیر را به یک کتاب کار جدید وارد کنید:

مقدار حاصل در سلول C1 به جای 1. 000123456789012345 1. 00012345678901 خواهد بود. این ناشی از مشخصات IEEE در ذخیره تنها 15 رقم قابل توجه از دقت است. برای اینکه بتوانید محاسبه فوق را ذخیره کنید ، اکسل حداقل به 19 رقم از دقت نیاز دارد.

اصلاح خطاهای دقیق

اکسل دو روش اساسی را برای جبران خطاهای گرد ارائه می دهد: عملکرد دور و دقت همانطور که نمایش داده می شود یا دقت را به عنوان گزینه کتاب کار نمایش داده می کند.

روش 1: عملکرد دور

با استفاده از داده های قبلی ، مثال زیر از عملکرد دور برای مجبور کردن یک عدد به پنج رقم استفاده می کند. این به شما امکان می دهد تا نتیجه را با ارزش دیگری مقایسه کنید.

این منجر به 1. 2E+200 می شود.

d1: = if (c1 = 1. 2e+200 ، درست ، نادرست)

این باعث می شود مقدار درست باشد.

روش 2: دقت همانطور که نمایش داده شد

در بعضی موارد ، شما ممکن است با استفاده از دقت به عنوان گزینه نمایش داده شده ، از تأثیرگذاری خطاهای گرد در کار خود جلوگیری کنید. این گزینه مقدار هر شماره را در صفحه کار مجبور می کند تا مقدار نمایش داده شده باشد. برای روشن کردن این گزینه ، این مراحل را دنبال کنید.

1. در منوی File ، گزینه ها را کلیک کرده و سپس روی دسته پیشرفته کلیک کنید.
2. در هنگام محاسبه این بخش کار ، کتاب کار مورد نظر خود را انتخاب کنید و سپس مجموعه Set را به عنوان کادر نمایش داده شده انتخاب کنید.

به عنوان مثال ، اگر یک فرمت شماره را انتخاب کنید که دو مکان اعشاری را نشان می دهد ، و سپس دقت را به عنوان گزینه نمایش داده شده روشن می کنید ، هنگام ذخیره کتاب کار خود ، تمام دقت فراتر از دو مکان اعشاری از بین می رود. این گزینه بر کتاب کار فعال از جمله همه برگه ها تأثیر می گذارد. شما نمی توانید این گزینه را خنثیسازی کنید و داده های گمشده را بازیابی کنید. توصیه می کنیم قبل از فعال کردن این گزینه ، کتاب کار خود را ذخیره کنید.

تکرار اعداد باینری و محاسبات که نتایج تقریباً صفر دارند

یکی دیگر از مشکلات گیج کننده که بر ذخیره شماره های نقطه شناور در قالب باینری تأثیر می گذارد این است که برخی از اعداد محدود ، اعداد غیر تکرار شونده در پایه 10 اعشاری ، بی نهایت هستند و تعداد آنها را به صورت باینری تکرار می کنند. رایج ترین نمونه این مقدار 0. 1 و تغییرات آن است. اگرچه این اعداد را می توان به طور کامل در پایه 10 نشان داد ، اما همان تعداد در قالب باینری هنگام ذخیره در مانتیسا ، تعداد باینری تکرار زیر می شود:

000110011001100110011 (و غیره)

مشخصات IEEE 754 برای هر شماره کمک هزینه خاصی نمی دهد. این آنچه را که می تواند در مانتیسا ذخیره کرده و بقیه را کوتاه می کند. این منجر به خطایی در حدو د-2. 8 E-17 یا 0. 0000000000000028 در هنگام ذخیره می شود.

حتی کسری اعشاری متداول ، مانند اعشار 0. 0001 ، دقیقاً به صورت باینری قابل ارائه نیست.(0. 0001 یک بخش باینری تکرار شونده است که دارای مدت 104 بیت است). این شبیه به این دلیل است که کسری 1/3 دقیقاً در اعشاری نشان داده نمی شود (تکرار 0. 3333333333333333333).

به عنوان مثال ، مثال ساده زیر را در Microsoft Visual Basic برای برنامه ها در نظر بگیرید:

با این کار 0. 999999999999996 به عنوان خروجی چاپ می شود. خطای کوچک در نشان دادن 0. 0001 در باینری به جمع می رسد.

مثال: اضافه کردن یک عدد منفی

موارد زیر را به یک کتاب کار جدید وارد کنید:

A1: = (43. 1-43. 2) +1

بر روی سلول A1 راست کلیک کرده و سپس روی سلول های قالب کلیک کنید. در برگه شماره ، روی رده علمی کلیک کنید. مکان های اعشاری را روی 15 تنظیم کنید.

به جای نمایش 0. 9 ، اکسل 0. 899999999999999 را نشان می دهد. از آنجا که (43. 1-43. 2) ابتدا محاسبه می شود ، -0. 1 به طور موقت ذخیره می شود و خطای ذخیر ه-0. 1 در محاسبه وارد می شود.

مثال وقتی یک مقدار به صفر برسد

در اکسل 95 یا قبل از آن ، موارد زیر را وارد یک کتاب کار جدید کنید:

A1: = 1. 333+1. 225-1. 333-1. 225

بر روی سلول A1 راست کلیک کرده و سپس روی سلول های قالب کلیک کنید. در برگه شماره ، روی رده علمی کلیک کنید. مکان های اعشاری را روی 15 تنظیم کنید.

به جای نمایش 0 ، نمایشگرهای اکسل 9 5-2. 22044604925031 E-16.

اکسل 97 ، با این حال ، بهینه سازی را معرفی کرد که سعی در اصلاح این مشکل دارد. اگر یک عملیات اضافی یا تفریق منجر به یک مقدار در صفر یا بسیار نزدیک به صفر شود ، Excel 97 و بعد از آن هر خطایی را که در نتیجه تبدیل یک عملگر به و از باینری وارد شده است ، جبران می کند. مثال بالا هنگامی که در اکسل 97 انجام شد و بعداً به درستی 0 یا 0. 0000000000000E+00 را در نماد علمی نشان می دهد.

برای کسب اطلاعات بیشتر در مورد شماره های شناور و مشخصات IEEE 754 ، لطفاً به وب سایت های زیر جهانی زیر مراجعه کنید: <Pan> A1: = 1. 333+1. 225-1. 333-1. 225