Kayan nokta aritmetiği Excel'de yanlış sonuçlar verebilir
Özet
Bu makalede, Microsoft Excel'in kayan nokta sayılarını nasıl depolayıp ve hesapladığı ele alınır. Bu durum, yuvarlama veya veri kesilmesi nedeniyle bazı sayıların veya formüllerin sonuçlarını etkileyebilir.
Genel bakış
Microsoft Excel, kayan noktalı sayıların nasıl depolanıp hesaplanacağını belirlemek için IEEE 754 belirtimine göre tasarlanmıştır. IEEE, başka şeylerin dışında bilgisayar yazılım ve donanım standartlarını da belirleyen, uluslararası bir gövde olan Elektrik ve Elektronik Mühendisleri Enstitüsü'dür. 754 belirtimi, kayan noktalı sayıların ikili bir bilgisayarda nasıl depolanması gerektiğini açıklayan yaygın olarak benimsenen bir belirtimdir. Kayan noktalı sayıların makul miktarda alanda depolanmasına ve hesaplamaların görece hızlı bir şekilde gerçekleşmesine izin verdiği için popülerdir. 754 standardı, Intel, Motorola, Sun ve MIPS işlemcileri de dahil olmak üzere kayan nokta aritmetiği uygulayan neredeyse tüm günümüz PC tabanlı mikroişlemcilerinin kayan nokta birimleri ve sayısal veri işlemcilerinde kullanılır.
Sayılar depolandığında her sayıyı ya da kesirli sayıyı, ona karşılık gelen bir ikili sayı temsil edebilir. Örneğin 1/10 kesiri, ondalık sayı sisteminde 0,1 olarak temsil edilebilir. Ancak aynı sayı, ikilik biçiminde aşağıdaki yinelenen ikili ondalık sayı olur:
0001100110011100110011 (ve böyle devam eder)
Bu sonsuz bir şekilde tekrar edilebilir. Bu sayı sınırlı (sınırlı) bir alanda temsil edilemez. Bu nedenle, bu sayı depolandığında yaklaşık -2,8E-17 aşağı yuvarlanır.
Bununla birlikte, üç genel kategoride yer alan IEEE 754 belirtiminin bazı sınırlandırmaları vardır:
- Maksimum/minimum sınırlamaları
- Hassasiyet
- Yinelenen ikili sayılar
Daha Fazla Bilgi
Maksimum/Minimum Sınırlamaları
Her bilgisayarın, işlenebilir bir maksimum ve minimum sayısı vardır. Sayının depolandığı belleğin bit sayısı sonlu olduğundan, depolanabilecek en büyük veya en küçük sayı da sonlu olur. Excel için depolanabilecek en büyük sayı 1,79769313486232E+308 ve depolanabilecek en düşük pozitif sayı 2,2250738585072E-308'dir.
IEEE 754'e bağlı kalınan durumlar
- Aşağı taşma: Aşağı taşma, temsil edilemeyecek kadar küçük bir sayı oluşturulduğunda meydana gelir. IEEE ve Excel'de sonuç 0'dır (IEEE'nin -0 kavramına sahip olması ve Excel'in olmaması dışında).
- Yukarı Taşma: Bir sayı, temsil edilemeyecek kadar büyük olduğunda yukarı taşma meydana gelir. Excel, bu durum için kendi özel temsilini kullanır (#NUM!).
IEEE 754'e bağlı kalmadığımız durumlar
Denormalize sayılar: Denormalize edilmiş bir sayı, 0 üssü ile gösterilir. Bu durumda, tüm sayı mantiste depolanır ve mantisin başında saklı bir 1 olmaz. Sonuç olarak, hassasiyeti kaybedersiniz ve sayı ne kadar küçük olursa hassasiyet o kadar fazla kaybolur. Bu aralığın küçük ucundaki sayıların tek basamaklı hassasiyeti vardır.
Örnek: Normalleştirilmiş bir sayanın başta saklı bir 1'i olur. Örneğin, mantis 0011001 temsil ederse normalize sayı, uygulanan saklı 1'den ötürü 10011001 olur. Normal dışı bir sayının başında örtük bir sayı yoktur, bu nedenle 0011001 örneğimizde normal olmayan sayı aynı kalır. Bu durumda, normalize sayının sekiz anlamlı basamağı (10011001) varken denormalize sayının, baştaki sıfırlar anlamsız olduğundan beş anlamlı basamağı (11001) vardır.
Denormalize sayılar temel olarak, normal alt sınırdan daha küçük sayıların depolanabilmesine olanak sağlayan geçici bir çözümdür. Doğası gereği normalleştirilmiş sayıların değişken sayıda anlamlı basamak içermesi nedeniyle Microsoft belirtiminin bu isteğe bağlı bölümünü uygulamaz. Bu, hesaplamalara girerken önemli hatalara yol açar.
Pozitif/Negatif Sonsuzlar: Sonsuzlar, 0'a böldüğünüzde ortaya çıkar. Excel sonsuzları desteklemez, bunun yerine bir #DIV/0 verir! hatasını verir.
Bir Sayı Değil (NaN): Geçersiz işlemleri (sonsuzluk/sonsuzluk, sonsuzluk-sonsuzluk veya -1'in kare kökü gibi) temsil etmek için NaN kullanılır. NaN'lar, programın geçersiz bir işlemi geçerek devam etmesine izin verir. Bunun yerine Excel hemen veya
#DIV/0!gibi#NUM!bir hata oluşturur.
Hassasiyet
Kayan noktalı bir sayı, 65 bit aralığında ikili olarak üç parça halinde depolanır: işaret, üs ve mantis.
| İşaret | Üs | Mantis |
|---|---|---|
| 1 işaret biti | 11 bit üs | 1 zımni bit + 52 bit kesir |
İşaret, sayın işaretini (pozitif veya negatif) depolar, üs, sayının yükseltildiği veya düşürüldüğü 2'nin katını depolar (2'nin en yüksek/en düşük katı +1.023 ve -1.022'dir) ve mantis, gerçek sayıyı depolar. Mantis için sonlu depolama alanı, iki komşu kayan noktalı sayının birbirine ne kadar yakın olabileceğini sınırlar (yani hassasiyet).
Mantis ve üs, ayrı bileşenler olarak saklanır. Sonuç olarak olası hassasiyet, işlenen sayının boyutuna (mantis) bağlı olarak değişebilir. Excel söz konusu olduğunda, Excel 1,79769313486232E308 ile 2,2250738585072E-308 arasında bir sayı depolayabilse de bunu yalnızca 15 basamak hassasiyet ile yapabilir. Bu sınırlama, IEEE 754 belirtimini kesinlikle takip etme işleminin doğrudan bir sonucudur ve Excel'in bir sınırlaması değildir. Bu duyarlılık düzeyi, diğer elektronik tablo programlarında da bulunur.
Kayan nokta sayıları aşağıdaki biçimde temsil edilir ve burada üs, ikili üsdür:
X = Kesir * 2^(üs - sapma)
Kesir, sayının normalleştirilmiş kesirli kısmıdır. Normalizedir çünkü üs öyle ayarlanır ki ilk bit her zaman 1 olur. Bu şekilde depolanması gerekmez ve bir miktar daha duyarlık elde edersiniz. İşte bu yüzden zımni bir şey var. Bu, ondalık virgülünün solunda bir basamak daha olacak şekilde üssün değiştiği bilimsel gösterime benzer. Fakat ikilik sistemde yalnızca 1'ler ve 0'lar olduğundan, her zaman ilk bit 1 olacak şekilde üssü değiştirebilirsiniz.
Sapma, negatif üsleri depolamak zorunda kalmamak için kullanılan sapma değeridir. Tek hassasiyetli sayılar için sapma 127 ve çift hassasiyetli sayılar için 1.023'tür (ondalık). Excel, çift hassasiyet kullanarak sayıları depolar.
Çok büyük sayılar kullanımına örnek
Yeni bir çalışma kitabına aşağıdakini girin:
A1: 1.2E+200
B1: 1E+100
C1: =A1+B1
C1 hücresindeki sonuç değeri 1,2E+200 olur; bu değer A1 hücresi ile aynı değerdir. Aslında EĞER işlevini kullanarak A1 ve C1 hücrelerini karşılaştırırsanız (örneğin, EĞER(A1=C1)), sonuç DOĞRU olacaktır. Bunun nedeni, IEEE belirtimlerinin yalnızca 15 anlamlı basamak hassasiyetiyle depolamasıdır. Yukarıdaki hesaplamayı depolayabilmek için Excel'in en az 100 basamak hassasiyete ihtiyacı vardır.
Çok küçük sayılar kullanımına örnek
Yeni bir çalışma kitabına aşağıdakini girin:
A1: 0.000123456789012345
B1: 1
C1: =A1+B1
C1 hücresindeki sonuç değeri 1,000123456789012345 yerine 1,00012345678901 olur. Bunun nedeni, IEEE belirtimlerinin yalnızca 15 anlamlı basamak hassasiyetiyle depolamasıdır. Yukarıdaki hesaplamayı depolayabilmek için Excel'in en az 19 basamak hassasiyete ihtiyacı vardır.
Hassasiyet hatalarını düzeltme
Excel, yuvarlama hatalarını telafi etmek için iki temel yöntem sunar: YUVARLA işlevi ve Göründüğü gibi hassasiyet veya Hassasiyeti görüntülendiği gibi ayarla çalışma kitabı seçeneği.
Yöntem 1: YUVARLA işlevi
Önceki verileri kullanarak aşağıdaki örnekte bir sayı YUVARLA işlevi kullanılarak beş basamağa zorlanıyor. Bu, sonucu başka bir değerle başarıyla karşılaştırmanızı sağlar.
A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5)
Sonuç 1,2E+200 olur.
D1: =EĞER(C1=1.2E+200, DOĞRU, YANLIŞ)
Bu, DOĞRU değerine neden olur.
Yöntem 2: Göründüğü gibi hassasiyet
Bazı durumlarda, Göründüğü gibi hassasiyet seçeneğini kullanarak yuvarlama hatalarının çalışmanızı etkilemesini önleyebilirsiniz. Bu seçenek, çalışma sayfasındaki her sayının değerinin görüntülenen değer olmaya zorlar. Bu seçeneği açmak için şu adımları izleyin.
- Dosya menüsünde, Seçenekler'e ve sonra Gelişmiş kategorisine tıklayın.
- Bu çalışma kitabını hesaplarken bölümünde, istediğiniz çalışma kitabını seçin ve sonra da Göründüğü gibi hassasiyet onay kutusunu seçin.
Örneğin, iki ondalık basamak gösteren bir sayı biçimi seçtiyseniz ve ardından Göründüğü gibi hassasiyet seçeneğini açarsanız çalışma kitabınızı kaydettiğinizde iki ondalık basamağı aşan tüm doğruluk kaybolur. Bu seçenek, tüm çalışma sayfaları dahil olmak üzere etkin çalışma kitabını etkiler. Bu seçeneği geri alıp kayıp verileri kurtaramazsınız. Bu seçeneği etkinleştirmeden önce çalışma kitabınızı kaydetmenizi öneririz.
Sıfıra yakın sonuçları olan yinelenen ikili sayılar ve hesaplamalar
Kayan noktalı sayıların ikili biçimde depolanmasını etkileyen bir diğer kafa karıştırıcı sorun da, ondalık taban 10'da sınırlı, tekrarlanmayan sayılar olan bazı sayıların sonsuz ve yinelenen sayılar olmasıdır. Bunun en yaygın örneği 0,1 değeri ve bu çeşitlemeleridir. Bu sayılar 10 tabanında mükemmel bir şekilde temsil edilebilse de, mantiste depolandığında ikili biçimde aynı sayı aşağıdaki yinelenen ikili sayı olur:
000110011001100110011 (ve böyle devam eder)
IEEE 754 belirtimi, herhangi bir sayı için özel izinler belirlemez. Mantiste neleri depolayabiliyorsa depolar ve kalan kısmı kırpar. Bu, depolandığında -2.8E-17 veya 0.0000000000000000028 hatasıyla sonuçlanır.
Ondalık 0,0001 gibi yaygın ondalık kesirler bile tam olarak ikili olarak temsil edilemez. (0,0001, 104 bitlik dönemi olan, yinelenen bir ikili kesirdir). Bu, 1/3 kesirinin tam olarak ondalık olarak temsil edilememesine benzer (yinelenen 0,333333333333333).
Örneğin, Microsoft Visual Basic for Applications'da aşağıdaki örneği göz önünde bulundurun:
Sub Main()
MySum = 0
For I% = 1 To 10000
MySum = MySum + 0.0001
Next I%
Debug.Print MySum
End Sub
Çıktı olarak 0,999999999999996 YAZACAKTIR. İkili değerde 0,0001'i temsilindeki küçük hata, toplama aktarılır.
Örnek: Negatif bir sayı ekleme
Yeni bir çalışma kitabına aşağıdakini girin:
A1: =(43.1-43.2)+1
A1 hücresine sağ tıklayın ve Hücreleri Biçimlendir'e tıklayın. Sayı sekmesinde, Kategori'nin altında Bilimsel'e tıklayın. Ondalık basamaklar'ı 15 olarak ayarlayın.
0,9 yerine Excel 0,899999999999999 gösterecektir. Önce (43,1-43,2) hesaplandığı için, -0,1 geçici olarak depolanır ve -0,1 depolama hatası hesaplamaya dahil edilir.
Bir değerin sıfıra ulaşmasına örnek
Excel 95 veya daha önceki bir sürümde, yeni çalışma kitabına aşağıdakini girin:
A1: =1,333+1,225-1,333-1,225
A1 hücresine sağ tıklayın ve Hücreleri Biçimlendir'e tıklayın. Sayı sekmesinde, Kategori'nin altında Bilimsel'e tıklayın. Ondalık basamaklar'ı 15 olarak ayarlayın.
0 yerine Excel 95, -2.22044604925031E-16 görüntüler.
Excel 97 ise bu sorunu düzeltmeyi denemeye yönelik bir iyileştirme sundu. Toplama veya çıkarma işlemi sıfır veya sıfıra çok yakın bir değerle sonuçlanırsa Excel 97 ve sonrası, işlenenin ikiliye ve ikiliden dönüştürülmesi sonucunda ortaya çıkan tüm hataları telafi edecektir. Yukarıdaki örnek, Excel 97 ve üstünde uygulandığında doğru şekilde 0 ya da bilimsel notasyonda 0,000000000000000E+00 olarak görüntüler.
Kayan noktalı sayılar ve IEEE 754 belirtimi hakkında daha fazla bilgi için aşağıdaki World Wide Web sitelerine bakın: