21-Düşeyara(aranacak değer;tablo dizisi;aramanın başladığı sütun) Özelliği
Bu özellik ile verilerden oluşan bir tabloda kullanıcının girdiği bir tek verinin tablo içinde aldığı
değeri görüntülemek mümkün olmaktadır. Hemen örnek..:)
ÖRNEK28:
Bir sınıf listesi ve girdikleri 3 sınavın sonuçları elimizde olsun. Bizden istenen bir hücreye isim
girince bu ismin aldığı notu tablodan okuyup karşısına yazsın.
Burada girilen formülün sonundaki “yanlış” ifadesi, eğer listede olmayan bir isim girilirse uyarsın
diye yazılmıştır. Formüldeki H3+1 değerinin anlamı, formüle aramanın başladığı şutunu
söylemek gerekiyor. (formülün açılımını şekil üzerinde inceleyin)
Başlangıç olarak isimlerin başladığı B şutunu 1 olarak alınır. 1 sınav için bu değere 1 eklemek, 2
sınav için 2 eklemek ilgili sütuna ulaşmak gerekir. Bu nedenle hangi sınav isteniyorsa o sınavın
rakamı H3 e yazılınca bu değere 1 ekleyerek başlangıç şutunu verilmiş olur.
Aranacak değer, tablo dizisi, aramanın başladığı sütun parametrelerinden sonra gelen YANLIŞ
parametresi aynı adla başlayan verilerde hata yapılmasını önlemek içindir ve düşeyara komutu
ile mutlaka kullanılmalıdır.
EHATALIYSA()
“Eğer hatalıysa” deyiminin kısa şekli olan ehatalıysa() eğer ilgili hücre değeri hata değerlerinden
birisi ise “DOĞRU” hata mesajı değilse “YANLIŞ” değerini alır. Hata mesajları ise malum;
#YÖK!, #DEĞER!, #BAŞV!, #SAYI/0!, #NUM!, #AD!, #BOŞ!…Hemen bir örnek yapalım. Bir
şutunda X değerleri, diğer sütunda Y değerleri olan bir tablo olsun. Tablonun son sütununa ise
=X / Y formülünü girip bölme işlemi yaptırdım. Şimdi bildiğiniz gibi sıfıra bölme anlamsızdır ve
hata mesajı verir. Bir yeni sütun yaparak =ehatalıysa() fonksiyonu ile bu hatayı kontrol
edeceğim. Tablonun yanına bir sütun daha açıp bu kontrolü yapacak olan =ehatalıysa()
formülünü girdim. Gste sonuç;
Bölmenin hatalı olduğu yerde hemen YANLIŞ değerinin döndüğünü görebilirsiniz. Bu gibi
hataların excelin normal uyarısı şeklinde görünmesini istemiyorsak hatayı kontrol edip çıkacak
uyarıyı biz yazabiliriz. Örneğin bu örnekte d sütunundaki formülü;
=EĞER(EHATALIYSA(C4);”SIFIRA BÖLME YAPILAMAZ”) şeklinde değiştirirseniz “DOĞRU” YAZAN
YERDE “SIFIRA BÖLME YAPILAMAZ” uyarısı çıkacaktır ki bu daha hoş bir görüntü olur.
Hata yakalamada daha önemli bir durum ise bir önceki konuda anlatılan düşeyara özelliği ile
ilgilidir. Bir önceki konuda isimler, sınav1 sınav2 sınav3 gibi sütunlar ve bu sütunlardaki isimlerin
hangi sınavdan hangi notu aldığını bulan güzel bir işlev olan düşeyara() fonksiyonunu
görmüştük. Sorun şu; peki kullanıcı listede olmayan bir isim girerse ne olacak? Tabi ki
#YÖK hata mesajı gelecek. Bu hata mesajını kontrol edip daha uygun bir uyarı çıkaran
fonksiyonu birlikte oluşturalım. Bunun için bir sonraki konuya dikkat edin!.
22-DÜŞEYARA() VE EHATALIYSA()
Konu 21 ve örnek 28 de anlatılıp uygulandığı gibi bir veri tablomuz ve bu tabloda veri bulup
getiren bir düşeyara() fonksiyonu ile çalışan arama hücremiz olsun. Tablomuz adı, melek, ve
maaş sütunlarından oluşuyor. Adı girildiğinde, adı girine kişinin meslek ve maaş durumunu
getiriyor. Eğer adı tabloda olmayan bir kişi adı girilirse hata mesajı veriyor. Biz bu hata mesajını
düzenleyelim. Bunun için düşeyara() fonkisyonu yerine daha karmaşık ama daha kullanışlı olan
bir karma fonksiyon kullanacağım. Bunun için düşeyara ve ehatalıysa fonksiyonlarını birlikte
kullanacağım.
Girilen karma fonksiyon karışık gibi görünebilir ama açıklamasını bir okuyun sonra karar verin;
Düşeyarama yapıyor, eğer hata mesajı varsa (yani arama başarısız ise) “kayıt yok” yazacak,
hata mesajı yoksa düşey arama yapıp sonucu yazacak. Baştaki eğerin anlamı da bu birinden biri
doğru. Düşey ara, hata mı var? Evet o zaman eğerin birinci şartını yap uyarıyı yaz, hata yok mu,
ikinci şartı yap, düşey ara ve sonucu yaz. Bu kadar..Girilen Özgür değeri listede olmadığı için
kayıt yok mesajı verilmiş..Tamam biraz zor ve karışık, ama hayat da öyle değil mi?
23-Zaman Fonksiyonlarının Kullanılması
- a) =BUGÜN() o günün tarihini verir.
- b) =DAKGKA(12:15:23) içine uygun formatta girilen saat verişindeki dakika değerini alır.
(Bu örnekte 15 değerini döndürür)
- c) =GÜN(30.06.2002) Girilen bir tarihteki gün değerini geri döndürür. Örnekte 30 değerini
alır.
- d) =HAFTANINGÜNÜ(tarih;başlangıç değerg-Pazar için 1-)
=HAFTANINGÜNÜ(30.06.2002) değeri 1 değerini alır. Çünkü verilen tarih Pazar gününe
gelir.
- e) =SGMDG() Girilen ani tarih ve saat olarak verir.
- f) =GÜN360(başlangıç tarihi, bitiş tarihi) iki tarih arasındaki gün sayısını verir, 1 yıl
360 gün kabul eder.. Böylece =GÜN360(doğum tarihiniz, ölüm tarihiniz) girip kaç gündür
yaşadığınızı bulabilirsiniz.
24-Excelde Koşullu Toplama Fonksiyonun Kullanılması
Koşullu toplamam özelliği özellikle aynı adla tekrarlanan verilerin toplanmasında çok kullanılır.
Örneğin bir oto galeride günlük satışların bir listesi çıkarılıyor olsun. Her markadan kaç adet
satış yapıldığı yanına yazılarak liste uzayıp gidiyor..Ay sonunda hangi markadan ne kadar
satılmış hesaplanmak isteniyor…Nasıl yaparız?(oturup tek tek hesaplarız diyenler, excel ile
tanısın..:)
Hemen sözü edilen örneği verelim;
ÖRNEK29:
Bir şutunda markalar, diğerinde kaç adet satıldıkları hemen yan tarafta bir hücreye gidip var
olan üç markanın adını yazıyoruz. Ardından markanın yanındaki hücreye Şekildeki formülü
giriyoruz. Dikkat: Burada bir konu çok önemli, bu bir dize formülü olduğu için yazdıktan
sonra enter değil Ctr+Shift+Enter basıyoruz.
=TOPLA(EĞER( B17:B24=E18;C17:C24)) formülünün açıklaması basit;
eğer E18 deki değer, B17 den B24 e kadar olan hücredeki değerlerden birine eşit ise o değerin
karşısındaki değeri ( C17 den C24 e..) topla..
25-Excelin Metini Sütuna Dönüştürme Özelliği
Bir şutunda ad soyadlarından oluşan bir listeniz olsun. Sorun şu; adlar ve soyadları aynı sütunda
ve siz bunları ayırmak istiyorsunuz. Ne yaparsınız?
Çok basit, hemen isimlerin olduğu şutunu seçin, Veri/Metni sütunlara dönüştür komutunu
verin, karşınıza gelen pencerede Sekmelerden “sınırlandırılmış” sekmesini seçip ileri tuşuna
basın. Sonra Ayırıcılardan “boşluk” sekmesini seçin. (isimler ile soyadları arasında boşluk olduğu
için bunu yaptık.)
Şimdi “ileri” ve “son” tuşlarına basın yeter…Hepsi bu kadar..
26-Etopla(toplanacak hücreler aralığı;koşul)
Aslında TOPLA(EĞER()) konusunda anlatılana çok benziyor, bu topla() ve Eğer() fonksiyonlarını
birleştiren bir fonksiyon. Koşul sağlanırsa belirtilen aralıkta toplama yapıyor, koşul sağlanmazsa
yapmıyor. Bir sayı listemiz olsun, sayılardan sadece 10 dan küçük olanları toplatacağım. Sonuç
tabi ki 13 neden, verilen aralıkta 10 dan küçük sadece 8 vr 5 var da ondan..
27-Eğersay (toplanacak hücre aralığı;koşul)
Koşula göre toplama yapar da koşula göre sayma yapamaz mı yapar tabi. Bir dizi sayı var acaba
içinde kaç sayı 5 den küçük? Aşağıdaki şekli inceleyin.
28-Excel ile Basit Veri Tabanı Yaratma
Aşağıdaki gibi bir tablo oluşturun. Tabloda başlıklar ve altında bir iki veri olsun.
Tablonuz hazır ise onu veri tabanına çevirmeye başlayalım. Başlık satırını seçili hale getirin ve
ardından Veri/Form komutunu verin. Aşağıdaki şekildeki komut penceresi karşınıza çıkacaktır.
Yeni kayıt, önceki kayıt, sonraki kayıt gibi işlemleri bu pencere üzerinden yapabilirsiniz.
29-Bir Listedeki Büyük Değerlerin Bulunması ve Farklı Gösterimi
Bir not listesi ya da alacak listesi ya da başka bir şey..En büyük ilk üç değeri bulup farklı renkte
olmasını sağlayabiliriz. Aslında bu fonksiyon ile büyüklük sıralamasında kaçıncı değeri istiyorsak
excel bize o değeri getirecektir. =Büyük(veri listesi;kaçıncı büyük isteniyorsa o)
ÖRNEK30:
Elimizde bir not ortalaması listesi olsun, ilk üçe girenleri otomatik olarak bulmak ve birinci
yüksek notu da kırmızı ile göstermek istiyoruz. 1., 2. ve 3. için girdiğim formül yukarıda
verdiğimin aynısı, şekilde görülebilir. Kırmızıya boyamak için önce veri tablosu seçilir.
Biçim/koşullu biçimlendirme komutundan koşul olarak hücre değeri “eşit” en yüksek notun
olduğu hücre verilir, biçim olarak da kırmızı yapılır, (daha önceki konularda nasıl yapılacağı
anlatılmıştı)
30-Yazdırma alanı Belirleme ve Tekrarlanan Başlık
Yazdırmak istediğimiz alanı kendiniz seçebilir, bunu yazdırmadan önce onizleme penceresinde
görebilirsiniz. Ayrıca özellikle firmalar, her sayfada tekrarlanan bir logo ya da antetlerinin
olmasını da isterler, bu gibi tekrarlana alanlar, sayfaların yazdırma sırası gibi önemli özellik ve
ayarları Dosya/Sayfa Yapısı komutu ile gelen pencerenin “çalışma sayfası” sekmesindeki ayarlar
ile yaparız.
Aynı şekilde yazdırma alanından taşanlar, sığmayanlar..gibi sorunlar için Görünüm/Sayfa Sonu
ön izleme komutunu vererek fare ile mavi çerçeveyi ayarlar iseniz, yazdırma alanınız
biçimlenmiş olur..Oradan geri dönemiyor musunuz, korkmayın canım, Görünüm/Normal
komutunu verin yeter…
Elimizde bir seri veri olsun, (x ler) ve bunlara karşılık gelen değerler (y ler).. Bunları excelde
yazıp çizdirebiliriz kolayca..Peki acaba bu x lerin y ler ile ilişki nedir? Yani fonksiyon ifadesi
nedir?
Kolay, çizdiğiniz grafik üzerine (tam çizgi üzerine ama..) fare sağ tus komutu ile “Eğilim Çizgisi
Ekle” komutunu verin. Gelen pencereden sizin verilere uygun bir seçenek seçip tamam tuşuna
basmadan önce, aynı pencerenin Seçenekler sekmesinde “Grafik üzerinde denklemi
görüntüle” komutunu aktif hale getirin.şimdi tamam diyebilirsiniz..hepsi bu…(Şekil 73)
31-Grafik Sınırları Dışındaki Değerlerin Bulunması (Extrapoasyon-interpolasyon)
Bir grafikte ya da veri kümesinde, x ler ve bu değerlere karşılık gelen y değerleri vardır. Yatay
ekşeni oluşturan x lerin bir başlangıç ve bir bitiş değeri vardır. Bu sınır içindeki değeri
bilinmeyen bir x verişine karşılık gelen y değerini hesaplamaya interpolasyon; bu sınırın
ötesinde, -fonksiyonun nasıl devam edeceğini tahmin ederek- bilinmeyen bir x değerine karşılık
gelen y değerini hesaplamaya extrapolasyon denir.
Özellikle hava tahmini, siyaset bilimi, ekonomi gibi geleceği önceden kestirmenin çok önemli
olduğu konularda extrapolasyon oldukça önemlidir. Örneğin mal satışınızın yıllara göre değiştiği
bir grafiğiniz olsa ve 2005 deki değeri tahmin etmeye çalışsanız bu extrapolasyon olur..
Şimdi gelelim excele bunu nasıl yaptıracağız; Kolay, dert etmeyin,
Bir önceki konuda eğilim çizgisi eklemiştik ya hani, işte o pencere yine işimizi görecek.
Eğilim çizgisi Biçimlendir komut penceresinde Seçenekler sekmesine gelin, karşınıza Tahmin
adında bir kutu gelecek.. Oradan ileri ve geri tahminler yaparak aslında olmayan x ler ve onlara
karşılık gelen y leri belirleyebilirsiniz.
32-Makro Nedir? Ne işe yarar? Nasıl yazılır?
Makro, excel ortamında makro dili ya da Visual basıc komutları ile oluşturulan bir komutlar dizisi
ya da program demektir. Üç tip makro vardır:
- a) kayıt makroları
- b) excel makroları
- c) Visual basıc komutları ile yazılan makrolar
Kayıt Makroları
Tıpkı bir ses kayıt cihazının sesleri kaydetmesi gibi, excel ortamında makro kaydedicisini
çalıştırırsınız ve çok sık tekrarlanan bir komutu kayıt altına alırsınız. Daha sonra bu kaydedilen
bu makroyu bir düğmeye bağlarsınız, böylece o kmut ya da eylem düğmeye her basıldığında
yeniden gerçekleştirilir.
Hemen bir örnek verelim;
Boş bir excel sayfasında iken;Araçlar/makro/Yeni makro kaydet komutunu verin, gelen
Pencerede makro adı yerine bir ad verin. Tamam düğmesine basınca excel penceresine geri
döneceksiniz, şimdi yaptıklarınıza çok dikkat edin, C1 hücresine gidip “Aşk Herşeyden Üstündür”
yazın. Dikkat edin hatasız ve geri dönüşsuz yazın, her hareket kaydediliyor. Gsiniz bitince
araçlar/makro/kaydı durdur komutunu verin.
Gste bu kadar, ee ne oldu şimdi?
Makromuz kaydedildi,
Önce C1e yazdığınız yazıyı silin..Sonra şu işlemleri takip edin;
Araçlar/makro/makrolar komutunu verin, gelen pencerede bir makro listesi olmalı, sizin az önce
verdiğiniz adla kaydolan makroyu bulun, çalıştır komutunu verin..Bir hata yapmadıysanız, kayıt
anında ne yaptıysanız aynen tekrarlanmış olması lazım..Yani Ç1 e Aşk Herşeyden Üstündür”
yazmalı..
Bu yolu öğrendiğinize göre örneğin C1 hücresinin içindekini silen bir makro da siz
kaydedebilirsiniz.
Makroları düğmelere de bağlayabiliriz. Ekranın üst kısmında komut satırlarının olduğu yerde fare
sağ tus ile açılan pencerede Formlar sekmesini aktif hale getirip formlar komut grubunun
penceresini açın, orda gri küçük bir düğme görüntüsü olacak, onun üzerini tıklayıp excel
sayfanızda uygun yere bir düğme yerleştirin, yerleştirdiğiniz anda makro penceresi açılır, bu
düğmeyi yazı yazdıran makroya bağlayın. Bir tane de silen makro için yapın..Böylece iki düğme
ile bir siler bir yazarsınız..
Kayıt tipi makrolar ile çok sık tekararlanan işlemler bir düğmeye bağlanarak kolaylıklar
sağlanabilir..
Makro Yazma
Kayıt tipi makroların dışında programlama bilgisi gerektiren makrolar da vardır. Bunları
oluşturmak için programlama bilgisi ve kod bilgisi gerekir. Burada bir iki küçük örnek ile bu tip
makroların nasıl yapıldığı ve kullanıldığı anlatılacaktır. Excelde iken Alt+F11 komutunu verin,
karşınıza Visual basıc makro düzenleyicisi çıkacaktır (Şekil 74).
Bu pencerede Şekil 74 de verilen kodları aynen yazın. Bu pencereyi kapatın. Bir önceki örnekte
nasıl düğme eklendiğini görmüştük, kayıt tipi iki makro kaydetmiştik, şimdi aynı şekilde bir
düğme yaratıp bu adı toplar() olan makroyu bu düğmeye bağlayın.
Bir sorun olmadıysa düğmeye basınca sizden bir sayı isteyip, ardından o sayıya kadar olan
sayıların toplamını bir mesaj olarak ekrana getiren basit bir makro yaratmış olmalısınız.
Makro yazarak excelin komut ve özelliklerinin ötesine geçebilir ve özel işlemleriniz, çalışmalarınız
için exceli değiştirebilirsiniz…
Örneğin bir makro yazdınız ve bunu bir düğmeye bağlayıp her excel dosyası açıldığında standart
düğmelerin arasında görünmesini istiyorsunuz.
Önce makronuzu yukarıda anlatılan yollardan biriyle oluşturup kaydedin. Ardından
araçlar/Özelleştir komutunu verip Komutlar sekmesine gelin. Solda “makro” yazan sekmeye
gelince sağda sarı bir gülen adam göreceksiniz. O damı fare ile tutup menü çubuklarının olduğu
uygun bir yere (yerini siz seçin) sürükleyip bırakın. Unutmayın bu işlem sırasında Araçlar komut
penceresi açık olacak.
Şekil 77 bu işlem yapılırken çekildi.
BU işlemden sonra düğme üzerine sağ tıklayarak makronuzu düğmeye bağlayın. (Makro ata)
komutu ile. Eğer düğme adını ve gülen adamı beğenmediyseniz, excel ad ve simge değiştirme
imkanı da veriyor.
Tüm bunlardan çıkan özet: excel ile hayal edipte yapamayacak bir şey yok gibi…ama çok
uğraşıp bulursanız, excel size o özelliği de yaratma, düğmeleme ve simgeleme imkanı veriyor.
Excel hakkında orta seviye bir çok konu hakkında yol aldık daha farklı çalışmalar ve hazır şablonlar ile bir sonraki makalede devam etmek istiyorum umarım bilgiler işinize yarar tekrar görüşmek dileklerimle…
