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 nokta sayılarının ikili bir bilgisayarda nasıl depolandığı açıklayan, yaygın olarak benimsenmiş bir belirtimdir. Bu özellik popülerdir çünkü kayan nokta sayıların makul miktarda bir alanda depolanmasına ve hesaplamaların görece hızlı bir şekilde gerçekleşmesine olanak sağlar. 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ı, sonlu (sınırlı) bir alanda temsil edilemez. Bu nedenle, bu sayı depolandığı zaman yaklaşık -2,8E-17 kadar 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 olur (-0 kavramının IEEE'de olduğu, Excel'de olmadığı istisnası ile).
• 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. Denormalize sayının başında örtülü bir yoktur dolayısıyla 0011001 örneğimizde, denormalize 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. Özelliklerinden dolayı denormalize sayıların anlamlı basamak sayısı değişken olduğundan Microsoft, belirtimin 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 ve onların yerine #DIV/0! 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 #NUM! gibi bir hata döndürür veya #DIV/0!.

Hassasiyet

Kayan noktalı bir sayı, 65 bit aralığında ikili olarak üç parça halinde depolanır: işaret, üs ve mantis.

İş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 sıkıca izlemenin sonucudur, 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, depolanmak zorunda değildir ve bir bit daha hassaslık elde edersiniz. Bu nedenle saklı bir bit vardır. 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.

1. Dosya menüsünde, Seçenekler'e ve sonra Gelişmiş kategorisine tıklayın.
2. 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 alamaz ve 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

İkili biçimdeki kayan nokta sayılarının depolanmasını etkileyen bir diğer kafa karıştırıcı sorun, ondalık tabanda sonlu, yinelenmeyen bazı sayıların ikili tabanda sonsuz, tekrarlanan sayılar olmasıdır. Bunun en yaygın örneği 0,1 değeri ve bu çeşitlemeleridir. Bu sayılar, onluk tabanında tam olarak temsil edilse de ikili biçimde aynı sayı mantiste depolandığında 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ığı zaman -2,8E-17 veya 0,000000000000000028 hatasıyla sonuç verir.

Ondalık 0,0001 gibi yaygın ondalık kesirler bile tam olarak ikili şekilde temsil edilemez. (0,0001, 104 bitlik dönemi olan, yinelenen bir ikili kesirdir). Bu, 1/3 kesrinin tam olarak ondalık şekilde gösterilememesine benzer (yinelenen 0,33333333333333333333).

Örneğin, aşağıdaki basit Microsoft Visual Basic for Applications örneğini düşünün:

   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

1. Yeni bir çalışma kitabına aşağıdakini girin:

   A1: =(43.1-43.2)+1

2. 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

1. 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

2. 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 nokta sayıları ve IEEE 754 belirtimi hakkında daha fazla bilgi için lütfen aşağıdaki World Wide Web sitelerine bakın:

• https://www.ieee.org
• https://steve.hollasch.net/cgindex/coding/ieeefloat.html