29 Kasım 2013 Cuma

Excel'de Verilerle Hesaplama Yapmak

İncelenecek Konular:
7.1. Giriş
7.2. Veri Gruplarını Adlandırmak
7.3. Değer Hesaplamak İçin Formül Oluşturmak
7.4. Belirli Koşulları Sağlayan Verileri Özetlemek
7.5. Hesaplama Hatalarını Bulup Düzeltmek

7.1. GİRİŞ

Excel 2007 kullanarak gerçekleştirebileceğimiz önemli görevlerden biri, ilişkili hücreler dizisindeki değerlerin toplamını hesaplamaktır. Bunun yanında Excel 2007’yi kullanarak seçtiğimiz veriyle ilişkili bir grup hücre içinde, en büyük veya en küçük değeri bulabiliriz. Örneğin; bir şirkette ürün yöneticisi olarak çalışıyorsak, elimizde var olan veri grubu içersindeki en büyük ya da en küçük değeri bularak, en iyi satıcımızı, daha çok ilgilenmemiz gereken ürün kategorisini ya da sürekli olarak en iyi fiyatı veren tedarikçileri belirleyebiliriz. Excel 2007, istediğimiz bilgiyi bulmada kolaylık sağlar. Ayrıca bir hata yaptığımızda, hızlıca bu hatanın nedenini bulup hatayı düzeltebiliriz. 

Birden çok hücreye başvurmadan istediğimiz bilgiye ulaşamayız ve genellikle aynı hücre grubu içersindeki verileri birden çok hesaplama için kullanırız. Excel 2007, aynı anda birden çok hücreye başvurmayı kolaylaştırır ve hesaplamaları hızlı bir şekilde gerçekleştirmemize olanak sağlar. 

Bu hafta, çalışma sayfasındaki veri gruplarına başvuru verimliliğini artırmayı ve veriler üzerinde formüller oluşturarak hesaplama yapmayı öğreneceğiz.  

7.2. VERİ GRUPLARINI ADLANDIRMAK

Çok sayıda veriyle çalışırken, birbiriyle ilişkili veriler içeren hücre gruplarını bulmak daha kolaydır. Örneğin; bir üniversitede yer alan fakültelerin 09:00 ile 17:00 saatleri arasında üniversite dışına göndermiş oldukları e-mail’lerin sayılarını tutan bir çalışma sayfası oluşturabiliriz.



Veriyi kullanmak istediğimizde, bu veriyi içeren hücreleri tek tek belirtmek yerine bu hücreleri “aralık” ya da başka bir ifade ile “adlandırılmış aralık” olarak tanımlayabiliriz. Örneğin, yukarıdaki ekran görüntüsünde D12 ile L12 hücreleri arasındaki öğeleri MühendislikFakültesiGönderilen adlı bir aralıkta gruplandırabiliriz. Bu aralıktaki hücre içeriklerini hesaplamalarda kullanmak istediğimizde tek tek her hücreyi belirtmek yerine sadece aralık adını kullanabiliriz.

Adlandırılmış aralık oluşturmak için, aralığa dâhil etmek istediğimiz hücreleri seçip, şeritten Formüller sekmesini tıklarız, ardından Tanımlı Adlar grubundaki Ad Tanımla'yı tıklarız. 


Yeni Ad iletişim kutusu görüntülenir. Yeni Ad iletişim kutusunda Ad alanına bir ad yazarız, Başvuru yeri alanında seçtiğimiz hücrelerin doğru olarak göründüğünden emin olmalıyız. Ayrıca, açıklama alanına, alanla ilgili bir açıklama da ekleyebilir ve bu adın tüm çalışma kitabında ya da sadece tek bir çalışma sayfasındaki formüllerde kullanılabilir olmasını sağlayabiliriz. Hücrelerin doğru olarak göründüğünden eminsek Tamam düğmesine tıklarız.



Adlandırılmış aralık için tanımlamak istediğimiz hücreler aralık adı olarak kullanmak istediğimiz bir etiket içeriyorsa, Formüller sekmesini görüntüleriz ve ardından Tanımlı Adlar grubunda yer alan “Seçimden Oluştur” düğmesini tıklarız. 



“Seçimden Oluştur” düğmesini tıkladığımızda karşımıza Seçimden Ad Oluştur iletişim kutusu gelir. Bu iletişim kutusunda veri hücrelerindeki etiketin konumunu gösteren onay kutusunu işaretleriz ve ardından Tamam düğmesini tıklarız. 
Adlandırılmış aralık oluşturmak için son yöntem ise, aralıkta olmasını istediğimiz hücreleri seçmek, formül çubuğunun yanındaki Ad kutusunu tıklamak, sonra da aralığa verdiğimiz adı yazmaktır. Ad kutusunun aşağı okunu tıklayarak çalışma kitabında bulunan aralıkları görüntüleyebiliriz. 



Çalışma kitabındaki adlandırılmış aralıkları yönetmek için Şerit'teki Formüller sekmesini tıklarız. Ad Yöneticisi iletişim kutusunu görüntülemek için Tanımlı Adlar grubundaki Ad Yöneticisi'ni tıklarız.



Tanımlı Adlar grubundaki Ad Yöneticisi'ni tıkladığımızda, Ad Yöneticisi iletişim kutusu karşımıza gelir.



Adlandırılmış aralığı tıkladığımızda Excel 2007, Başvuru Yeri alanında aralığın içerdiği hücreleri görüntüler, Düzenle düğmesini tıklamak, adlandırılmış aralık tanımında değişiklik yapmamızı sağlayan Ad Düzenle iletişim kutusunu görüntüler; Yeni Ad iletişim kutusuna benzer. Ayrıca bir adı tıkladıktan sonra Sil düğmesini tıklayıp görüntülenen onay kutusunda Tamam düğmesine tıklayarak adı silebiliriz.



7.3. DEĞER HESAPLAMAK İÇİN FORMÜLLER OLUŞTURMAK

Bir çalışma sayfasına verilerimizi girdikten sonra bir formül oluşturabiliriz. Örneğin Mühendislik Fakültesinin gün boyu dışarıya göndermiş olduğu toplam mail sayısını ya da gün içinde en çok veya en az maili hangi zaman aralığında gönderdiğini bulabiliriz.  

Excel 2007’de formül yazmak için hücreye önce eşittir (=) işareti konur. Excel 2007, eşittir işaretini gördüğünde, ardından gelen ifadenin metin değil, hesaplama olduğunu bilir. Eşittir işaretinden sonra formülü yazarız. Örneğin, D5 ve E5 hücrelerindeki sayıların toplamını =D5+E5 formülüyle bulabiliriz. Bir hücreye girdiğimiz formülü gözden geçirmek için hücreyi tıklayabilir, sonra da formülü formül çubuğunda düzenleyebiliriz. 



Örneğin, önceki formülü, D5 ve E5 hücre içeriğinin farkını hesaplayan =E5-D5 olarak değiştirebiliriz.

Bir hesaplamada 15 ya da 20 hücre için hücre başvurusu yazmak zor ve yorucu bir iş olacağından Excel 2007, karmaşık hesaplamaları kolaylaştırır. Yeni bir hesaplama için, Şerit'teki Formüller sekmesini tıkladıktan sonra İşlev Kitaplığı grubundaki İşlev Ekle düğmesine tıklarız. 



İçinden seçim yapabileceğimiz bir işlev listesi ve önceden tanımlanmış formüller içeren İşlev Ekle iletişim kutusu görüntülenir.


Aşağıdaki tabloda bu listedeki en kullanışlı işlevler verilmiştir.

İşlev Açıklama
TOPLA Belirtilen hücrelerdeki sayıların toplamını bulur.
ORTALAMA Belirtilen hücrelerdeki sayıların ortalamasını bulur.
SAY Belirtilen hücrelerdeki girdi sayısını bulur.
MAK Belirtilen hücrelerdeki en büyük değeri bulur.
MIN Belirtilen hücrelerdeki en küçük değeri bulur.

Kullanmak isteyebileceğimiz diğer iki işlev ŞİMDİ( ) ve DEVRESEL_ÖDEME( )'dir. 

ŞİMDİ( ) işlevi; çalışma kitabında güncel tarih ve saati görüntüler, dolayısıyla çalışma kitabı her açıldığında değeri değişir. Bu işlevin doğru biçimi = ŞİMDİ( )'dir. Değeri geçerli tarih ve saate göre günceleştirmek için çalışmamızı kaydedip, çalışma kitabını kapatıp yeniden açarız. 



DEVRESEL_ÖDEME( ) işlevi, biraz daha karmaşıktır. Bu işlev, sabit faizli ve sabit ödemeli bir borç için yapılacak ödemeyi hesaplar. DEVRESEL_ÖDEME( ) işlevinin hesaplamayı yapması için bir faiz oranı, ödemenin yapılacağı dönem sayısı ve başlangıç bakiyesi gerekir. İşleve eklenen öğelere bağımsız değişkenler denir ve belirli bir sırayla girilmeleri gerekir: DEVRESEL_ÖDEME(oran; dönem_sayısı; bd; g; tür). 

Aşağıdaki tabloda DEVRESEL_ÖDEME( ) işlevindeki bağımsız değişkenler özetlenmiştir.

oran Aylık ödemeli bir borç için 12'ye bölünmesi gereken faiz oranı.
dönem_sayısı Borç için yapılacak toplam ödeme sayısı.
bd Borç miktarı (bd, borç durumunun kısaltmasıdır).
gd Ödeme döngüsünün sonunda kalacak miktar (genellikle boş bırakılır ve bunun anlamı 0'dır).
tür 0 ya da 1’dir ve ödemelerin dönemin/ayın başında ya da sonunda yapılacağını gösterir (genellikle boş bırakılır ve bu 0 ya da dönem sonunda anlamına gelir).

Örneğin; bir bankadan yüzde 2 faiz oranıyla 20.000 TL kredi almak ve geri ödemesini 36 ayda yapmak istersek, aylık ödemeyi bulmak için DEVRESEL_ÖDEME( ) işlevini kullanabiliriz. Bu örnekte, işlev =DEVRESEL_ÖDEME(2%/12; 36; 20000) şeklinde yazılır ve aylık ödeme 572,85 YTL olarak bulunur. 

Tanımladığımız herhangi bir aralık adını bir formülde değer olarak kullanabiliriz. Örneğin, adlandırılmış aralık “MühendislikFakültesiGönderilen”, D12:L12 hücre aralığını gösteriyorsa, =ORTALAMA(MühendislikFakültesiGönderilen) formülüyle D12 ve L12 arasındaki hücrelerin ortalamasını hesaplayabiliriz. 



Excel'in önceki sürümünde, adı formüle elle yazmamız gerekiyordu. Excel 2007 yeni Formülü Otomatik Tamamla özelliğiyle formüllerimize işlevler, adlandırılmış aralıklar ve tablo başvuruları eklememizi kolaylaştırır. Excel 2007 yazdığımız değerin önceki girdiyle eşleştiğini fark ettiğinde Otomatik Tamamla'nın hücrelerin metin'e değerini tamamlamak için öneride bulunması gibi, Formülü Otomatik Tamamla da biz bir formül oluştururken işlevi, adlandırılmış aralığı ya da tablo başvurusunu tamamlamak için öneride bulunur.


  
 
Formüle TOPLA işlevini eklemek için TOPLA'yı tıklarız, Tab tuşuna basarız. 

Bir formüle bir dizi bitişik hücreyi dâhil etmek istiyorsak ama hücreleri adlandırılmış aralık olarak tanımlamadıysak, aralıktaki ilk hücreyi tıklayıp son hücreye kadar sürükleyebiliriz. Eğer hücreler bitişik değilse, Ctrl tuşunu basılı tutarak aralığa dâhil edilecek hücrelere tıklarız. Her iki durumda da farenin düğmesine basmayı bıraktığımızda, seçtiğimiz hücre başvuruları formülde görüntülenir.  



Bir formülü oluşturduktan sonra kopyalayıp başka bir hücreye yapıştırabiliriz. Bunu yaptığınızda Excel 2007, formülü yeni hücrelerde çalışacak biçimde değiştirmeye çalışır. Örneğin, bir çalışma sayfasındaki D8 hücresinin =TOPLA(C2:C6) formülünü içerdiğini düşünelim. D8 hücresini tıklayıp, hücrenin içeriğini kopyalamak, sonra da sonucu D16 hücresine yapıştırmak, =TOPLA(C10:C14) formülünü D16 hücresine yazar. Excel 2007, yeni hücrelere uyması için formülü yeniden yorumlamıştır Excel 2007, formülde kullanılan hücreleri yeniden yorumlayabileceğini bilir çünkü formül göreli başvuru (formül başka bir hücreye kopyalandığında değişebilen başvuru) kullanmaktadır. Göreli başvurular yalnızca hücre satır ve sütunuyla (örneğin C14) yazılır. 

Formülün kullandığı bir hücre başka bir hücreye kopyalanırken hücre başvurusunun sabit kalmasını istiyorsak, mutlak başvuru kullanabiliriz. Bir hücre başvurusunu mutlak başvuru olarak yazmak için satır adının ve sütun numarasının önünde $ yazarız. D16 hücresindeki formülün C10 ile C14 arasında bulunan hücrelerin değer toplamını, yapıştırıldığı hücreye göre değiştirmeden göstermesini istersek, formülü =TOPLA($C$10:$C$14) olarak yazarız.

Göreli hücre başvurusunu mutlak başvuruya dönüştürmenin kolay yolu, formül çubuğunda hücre başvurusunu seçmek, sonra da F4 tuşuna basmaktır. F4 tuşuna arka arkaya her basışımızda, aşağıdaki başvuru türü dönüşümlerini yapar. 

  Göreli sütun ve satır (örneğin, C4) 
  Mutlak sütun ve satır (örneğin, $C$4) 
  Göreli sütunlar ve mutlak satır (örneğin, C$4) 
  Mutlak sütun ve göreli satır (örneğin, $C4)

7.4. BELİRLİ KOŞULLARI SAĞLAYAN VERİLERİ ÖZETLEMEK

Formüllerin başka bir kullanımı da belirli koşullar sağlandığında iletileri görüntülemektir. Örneğin; günlük olarak ortalama 20’den fazla e-mail gönderen fakültelerin bulunması istenebilir. Bu tür bir formül, koşullu formül olarak adlandırılır ve EĞER işlevi kullanılır. Koşullu formül oluşturmak için, formülü yazacağımız hücreyi seçeriz ve İşlev Ekle iletişim kutusunu açarız. Bu iletişim kutusundaki kullanılabilir işlevler listesinden EĞER'i seçip, sonra da Tamam'ı tıklarız. 



Tamam düğmesini tıkladıktan sonra karşımıza “Fonksiyon Bağımsız Değişkenleri” iletişim kutusu açılır.



EĞER işleviyle çalışırken, Fonksiyon Bağımsız Değişkenleri iletişim kutusu üç kutu içerir: 

Mantıksal_sınama, 
Eğer_doğruysa_değer ve 
Eğer_yanlışsa_değer. 

Mantıksal_sınama kutusuna denetlemek istediğimiz koşulu yazarız. Örneğin; günlük olarak ortalama 20’den fazla e-mail gönderen fakültelerin bulunması için K5>20 şeklinde yazarız. 

Excel 2007'de EĞER işlevinin sonucunda bir ileti görüntülemesi için; Eğer_doğruysa_değer veya Eğer_yanlışsa_değer kutularının içinde görüntülenmesini istediğimiz iletiyi tırnak içinde yazabiliriz. Bu durumda, Eğer_doğruysa_değer kutusuna "20'den Çok Mail Gönderiyor" ve Eğer_yanlışsa_ değer kutusuna "20'den Az Mail Gönderiyor" yazarız. 





Excel 2007, verileri özetlemede kullanabileceğiniz beş yeni koşullu işlev içerir: 

  EĞERHATA, formül sonucu hatalıysa bir değer, yanlışsa başka bir değer görüntüler.
  EĞERORTALAMA, bir hücre aralığındaki belirli bir koşulu sağlayan değerlerin ortalamasını bulur.
  ÇOKEĞERORTALAMA, bir hücre aralığındaki belirli bir koşul kümesini sağlayan değerlerin ortalamasını bulur. 
  ÇOKETOPLA, bir aralıktaki belirli bir koşul kümesini sağlayan değerlerin toplamını bulur. 
  ÇOKEĞERSAY, bir aralıktaki belirli bir koşul kümesini sağlayan değerleri sayar. 
7.5. HESAPLAMA HATALARINI BULMAK VE DÜZELTMEK

Çalışma sayfasına eklemiş olduğumuz veriler üzerinde hesaplama işlemini gerçekleştirmeye yönelik formül yazarken hata yapma olasılığımız vardır. Excel 2007, belirli bir hesaplamada kullanılan hücreleri tanımlayarak ve varsa oluşan hataları açıklayarak formüllerimizdeki hataları bulmamızı kolaylaştırır. Bir çalışma sayfasını formül hataları açısından incelemeye hata denetimi denir. Excel 2007, hataları çeşitli yollarla gösterir. Hatanın oluştuğu formülün olduğu hücreye bir hata kodu yazar. Aşağıdaki çalışma sayfasında B15 hücresi “#AD?” hata koduna sahiptir.



Hatalı formül içeren hücre etkinleştirildiğinde, yanında bir Hata düğmesi görünür. Hatayla ilgili bilgilendirici seçenekler içeren ve hatayı gidermemiz için yardım öneren menüyü görüntülemek için düğmenin aşağı okunu tıklayabiliriz. 



Aşağıdaki tabloda en yaygın karşılaşılan hata kodları açıklanmıştır. 

Hata Kodu Açıklama
##### Sütun, değeri görüntüleyecek kadar geniş değil.
#DEGER! Formülün bağımsız değişken türü yanlış (DOĞRU ya da YANLIŞ değerinin gerektiği yerde metin bulunması gibi).
#AD? Formül, Excel 2007'nin tanımadığı metin içeriyor. 
#BAŞV! Formül, var olmayan bir hücreye başvuruyor. Bu hata kodu hücrelerin silinmesi durumunda oluşur.
#SAYI/0! Formül, sıfıra bölmeye çalışıyor.

Formül hatalarının kaynağını bulmanın başka bir yolu, formül için uygun hücrelerin değer sağladığından emin olmaktır. Örneğin, fakültelere göre günlük toplam mail gönderim sayısını hesaplamak isteyebilirsiniz ancak eksik hücre alarak formül oluşturabilirsiniz. Bu tür gözden kaçan hatalar için formül oluştururken dikkat etmek gerekir.

BÖLÜM ÖZETİ

Formüle bir hücre grubu eklemek için formülü yazdıktan sonra adlandırmak istediğimiz hücreleri, fareyle seçip adlandırabiliriz. 

Adlandırılmış aralıklar oluşturmak, tek bir terimle bir hücre bloğunun tamamına başvurmamıza olanak tanımakla kalmaz, zamanımızın ve emeğimizin boşa harcanmasını önler. Aynı yöntemi tablo verileri için de kullanarak, tüm tabloya ya da bir veya daha fazla tablo sütununa başvurabiliriz. 

Formülü bir hücreden diğerine kopyalarken, değişmeden kalmasını istiyorsak, formülü yazarken mutlak başvuru ($A$l) kullanırız. Formülün değişip çalışma sayfasındaki yeni konumunu yansıtmasını istiyorsak göreli başvuru (Al) kullanırız. 

Sıfırdan bir formül yazmak yerine yardım almak için İşlev Ekle iletişim kutusunu kullanabiliriz. 

Formül Değerlendir iletişim kutusunda formüldeki hesaplamaları adım adım izleyebilir ya da Hata Denetimi aracını kullanarak daha ayrıntılı bir hata denetleme işlemi gerçekleştirebiliriz.