Temel Excel Dersleri 5

Temel Excel Dersleri 5
+ - 0

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;

#YOK!, #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ı

  1. a) =BUGÜN() o günün tarihini verir.
  2. 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)

  1. c) =GÜN(30.06.2002) Girilen bir tarihteki gün değerini geri döndürür. Örnekte 30 değerini

alır.

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

  1. e) =SGMDG() Girilen ani tarih ve saat olarak verir.
  2. 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:

  1. a) kayıt makroları
  2. b) excel makroları
  3. 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…

 

Yazar Hakkında

1984 İzmit'te doğdu Konya Selçuk Üniversitesi ve Eskişehir Anadolu Üniversitesi mezunu. Network Dizayn kurucusu ve editörü. Server altyapısını kuran ve düzenleyen kişi. Gerçek bir teknoloji bağımlısı olan Fatih, ayrıca oyun dünyası ve film sektörüne de ilgili.

Yorum Yap