EXCEL DERSLERİ -2- (DEVAM)

EXCEL DERSLERİ  - 2 - (DEVAM)

ÖRNEK16:

Borç tutarı 50 milyondan az olanlar ve vadesi 1 Ocak 1995 den önce olanların ayrı bir listesini yapınız. (“ve” yi bilerek koyu yaptım, yerine veya olsa daha başka olurdu)

 1.adım: Kriter nedir? Tutar 50 milyondan büyük ve vadesi 1 Ocak95 den önce..öyleyse hemen tutar ve öd.tar başlıklarını kopyalayıp C15 ve D15 hücrelerine kopyalayalım. Hemen altlarına koşullarını yazalım; tutar >50000000 ve öd.tar<01/01/95 bu kadar...

 2.adım: Ana tablomuzu seçip Veri/süz/Gelişmiş Süzgeç komutu ile Şekil 37 deki pencereyi çağırtalım, Başka bir yere kopyala sekmesini seçelim, liste aralığı tablomuz (otomatik seçilmiş olmalı), ölçüt aralığı ise C15,C16,D15 ve D16 dan oluşan C15:D16 aralığındaki dört hücre..

Aşağıdaki şekilde bu durum verilmiştir.

  

Şekil 39

 

Uygun seçimlerden sonra tamam düğmesine basacak olursanız sonuçta istenen kriterlere uygun bir liste oluşur; Şekil 40.

 

Şekil 40

Gerçekten de istenen kritere uygun sadece bir veri bulunmaktadır...

 ÖRNEK17:

Borç tutarı 30000000 dan fazla veya vadesi 01/01/95 den önce olan verileri listeleyiniz.

 1.adım: Hemen uygun kriterleri yerleştirelim.

 2. Koşulu Hemen başlığın altına değil de bir boşluk bıraktıktan sonra yazdığıma dikkat edin. Bunun sebebi sorgu kriterindeki veya kelimesidir. Ve olsaydı alt alta yazacaktım.

 DİKKAT “ve” koşulu aynı satıra, “veya” koşulu bir alt satıra yazılır.

 2.adım: ana tabloyu seçip Veri/süz/gelişmiş süzgeç komutu ile gelen pencereyi uygun şekilde doldurursanız aşağıdaki şekildeki tabloyu elde edersiniz.

  

Şekil 42

ÖRNEK18:

Borcunu 30 günden fazla geciktiren ve borcu 10 000 000 dan az veya borcu 30 günden fazla geciktiren ve vadesi 1999 dan sonra olanları listeleyiniz

                                   

Şekil 43

ÖRNEK19:   Borcu 50 milyondan fazla ve gecikmesi 60 günden az veya borcu 30 milyondan fazla ve gecikmesi 30 günden az olanları listeleyin  ..(bu işin sonu yok ki kardeşim..)

                                           

Şekil 44

 

14-Excel De Parametrik Hesaplamalar (Hedef Ara Özelliği)

 Hedef ara özelliği excelin pek bilinmeyen ancak kullanışlı bir özelliğidir. Bir değişkene bağlı bir sonuç hücresinin alması istenen değer için bağlı olduğu değişkenin alabileceği değerin hesaplanması esasına dayanır. Hemen bir örnekle açıklayalım;

 ÖRNEK20: Yurtdışındaki fiyatı 15 000$ olan bir otomobilin, %20 gümrük vergisi ile yurtiçine getirildiğinde TL olarak maliyetini hesaplayalım. 1 dolar=1500 000 TL olsun. Uygun verilerin excele girilmesi ile elde edilecek tablo aşağıdaki şekildeki gibidir

                                                

Şekil 45

 

Şimdi farklı sonuç senaryoları için hedef ara özelliğinin nasıl kullanıldığını görelim.

a)    Otomobilin fiyatının 27 milyar değil de 20 milyar olması için doların kuru ne olmalı idi?

Araçlar menüsünden Hedef Ara komutunu verin Karşınıza aşağıdaki pencere gelecektir.

                    

Şekil 46

Burada ayarlanacak hücre B7 dir, Sonuç hücre, istenen değer yani 20 milyardır, bu sonuç için değişmesi istenen hücre ise dolar kurunun yazıldığı hücre B6 dır. Tama a basınca bu amaç için dolar kurunun 1 111 111 TL olması gerektiği bulunur.

 b)    25 milyar olması için gümrük vergisi ne olmalı idi?

Hemen aynı yolla komut penceremizi çağırıp gerekli verileri girersek sonuçta verginin %1 olması gerektiği sonucu elde edilir.

 

Şekil 47

c)     15 milyar olması için yurtdışı fiyatı ne olmalı idi?

Bunun da siz yapın artık...

15-Tablolardan Özet Ve Rapor Çıkarmak

 Bazı durumlarda çok fazla veri içeren tablolar sıkıcı olabilir. Tablo içeriğinde bu verileri çıkarmak mümkün değildir, sonuç değerleri bu hücrelere bağlıdır, ancak sonuçta bu ayrıntı hücrelerinin de göz önünde olması istenmez.

 Bu gibi durumlarda excelin şimdi anlatılacak özelliğinden faydalanabiliriz. Aşağıdaki gibi aylara göre giderlerimizi içeren bir tablomuz olsun

 

Şekil 48

 Bizim için önemli olan değerlerin üç aylık toplamlar olduğunu düşünelim. Üç aylık veriler ayları içeren hücrelere bağlanmıştır dolayısıyla ayları silemeyiz ama saklayabiliriz. Bunun için önce tabloyu seçin ardından Veri/Gruplandır seviyelendir/Otomatik seviyelendir komutunu verin. Ardından Veri/gruplandır seviyelendir/ Ayrıntıları Gizle komutunu verin. Özet tablonuz hazır.

 

Şekil 49

 

 16-Mantıksal Fonksiyonlar

 Excelin bir çok hazır fonksiyonu (İşlev) i mevcuttur. Bunlara ulaşmak için Ekle/İşlev komutunu vermek gerekir. Burada sadece mantıksal fonksiyonlardan bahsedilecektir. Mantıksal fonksiyonlar şekilde de görülebileceği gibi DEĞİL, DOĞRU, EĞER, VE, YA DA ve YANLIŞ fonksiyonlarıdır. Bunlardan işlevsel olanlara aşağıda değinilecektir.

                          

Şekil 50

 

VE-AND(koşul1; koşul2;...koşuln) FONKSİYONU

Bu fonksiyon, verilen koşulları kontorl ederek tümü de doğru ise “doğru”(True); sadece biri bile yanlışsa “yanlış” (FALSE) değerini geri döndürür. (geri döndürür yani ;girildiği hücreye bu değeri yazar)

 ÖRNEK21:

Aşağıdaki şekli inceleyin. D2 ye yazılan =VE(A1>8;A4>4;B3>2;B5>8) koşulda her şart doğru, sondaki hariç..Bu nedenle de “Yanlış” değeri geri döndü.

 

Şekil 51

 

B5 deki değeri 9 yaptığınızda koşul doğrulanacak ve geri dönen değer “doğru” ya dönecektir.

IF-EĞER(koşul1 ya da fonksiyon, değer1, değer2) Fonksiyonu

Uygulamada çok faydalı ve çok kullanılan bir excel fonksiyonudur. Koşul1 doğru ise değer1; doğru değilse değer2 atanır. Koşul1 bir sabit sayı, bir ifade ya da başka bir eğer fonksiyonu olabilir.

ÖRNEK22:

Aşağıdaki şekli iyi inceleyin. B1 e 15 C1 e 20 değerleri girilmiş, E1 e “büyük”, E2 ye ise “küçük”

Yazılmıştır. Daha sonra C3 hücresine =EĞER(B1+C1>50;E1;E2) mantıksal fonksiyonu girilmiştir.

Bu ilişkide B1+C1 toplam 50 den büyük ise E1 içeriği, değilse E2 içeriği C3 hücresine aktarılacaktır.

Şekil 52

 

Toplam 35 yani 50 den küçük olduğu için E2 deki değer dönmüştür.

ÖRNEK23:

Ara sınavın %30 unu, finalin %70 ini alıp; bunların toplamı 50 ve üzeri ise sonuç hücresine “GEÇTİ”, değilse “KALDI” yazacak bir excel örneği oluşturalım.

 

Şekil 53

E2 hücresine =C2*0,3+D2*0,7 ifadesi yazıldı. Bu geçme notunu hesaplayan ilişkidir. F2 hücresine ise =EĞER(E2>=50;"GEÇTİ";"KALDI") ifadesi yazılarak sonuca göre “GEÇTİ” ya da “KALDI” yazılır..

 ÖRNEK24:

“İyi güzel de yukarıdaki ilişki finalden 50 altında alanları da “geçti” diye veriyor” diyebilirsiniz (ben olsam derdim). Haklısınız bu durumda o sorunu da çözen ilişki =EĞER(D2<50;"KALDI";EĞER(E2>=50;"GEÇTİ";"KALDI")) ilişkisidir. F2 ye bu ilişkiyi girerseniz vizesi 89 olsa da finalden 49 alanı bırakacaktır Şekil 54

 

Şekil 54

 

ÖRNEK25:

İşi biraz daha zorlaştıralım, beynimize kan gitsin. Aşağıdaki kısıtları sağlayıp, girilen notun harf sistemindeki karşılığını verecek eğer fonksiyonunu geliştiriniz.

 

not

değer

4-3,5

A

3,4-3,0

B

2,9-2,0

C

1,9-0

F

Cevabı veriyorum ama kendiniz deneyin ve yapmaya çalışın. İçi içe eğer komutları oldukça önemli ve kullanışlıdır.

Şekil 55

 

En soldan başlarsak; C2 deki sayı 3.5 e eşit ya da büyükse A yazar, değilse devam eder; 3 e eşit ya da büyükse B yazar, değilse içeri doğru devam eder....

 Bir örnek de size; bir sütunda 5 isim var, yan sütunda hizmet yılları var, son sütuna kaç gün yıllık izin kullanabileceklerini listelemek istiyorum, koşul ise aşağıdaki gibi

Hadi bakalım bunu da siz yapın;

 

 

OR-YA DA(Koşul1, koşul2, ...koşuln) Fonksiyonu

Aldığı koşulları kontrol eder, koşulların biri bile doğru olsa “doğru” değerini, hepsi yanlışsa “yanlış” değerini geri döndürür.

 

Şekil 56

 

NOT-DEĞİL(mantıksal sabit ya da koşul) Fonksiyonu

Verilen mantıksal sabitin tersini elde eder. Koşul olarak verilmiş ise sonuç doğru ise yanlış, yanlış ise doğru yapar.

Aşağıdaki örnekte bazı değil örnekleri verdim.Hücrelere girdiğim ilişkileri yanlarına yazdım.

 

 

Şekil 57

 

 

17-Excelde Otomatik Şüzgeç Kullanan Veri Tabanları Yaratmak

 Bunun için oluşturulan tablo seçildikten sonra Veri/süz/otomatik süz komutu verilir. Otomatik süzgeç işaretleri tabloya yerleşir.

  

Şekil 61

 

18-Excelde Biçimlendirme İşlemleri

 a)    Bir hücreyi seçmek için üzerine tıklanır

b)    Bir sütunu seçmek için sütun üstündeki harfe tıklanır

c)     Bir satırı seçmek için satır başındaki rakama tıklanır

d)    Tüm sütunları ve satırları seçmek için sütun ve satır başlıklarının buluştuğu sol üstteki boş gri hücreye tıklanır.

e)    Kılavuz çizgilerini kaldırmak için Araçlar/Seçenekler/görünüm/kılavuz çizgileri sekmesi kaldırılır.

f)      Bir hücre ya da seçilen bir grup hücrenin zeminini boyamak için Vurgu komutu, yazıyı renkli yapmak için yazı tipi rengi komutu verilir.

 

g)    Bir satırın yüksekliği ve sütunun genişliği Biçim komutu ile verilir.

h)    Bir ya da birkaç satırın aşağıya kaymaması, başlık satırı gibi kalması için Pencere/Bölmeleri dondur komutu verilir.

i)       Bir excel dosyasının korunması için, şifrelenmesi için Araçlar/Koruma komutundan yararlanılır.

j)      Bir excel dosyası içinde bir grup hücreden oluşan bir bölgeyi özel a ile tanımlayabilir ve bu bölgeyi bir komutla çağırabilir, o bölgeye bağlantılı başlıklar yaratabilirsiniz.

F2 den H10 a kadar olan alanı seçili hale getirdikten sonra Ekle/Ad /Tanımla komutunu verip seçtiğiniz alana bir ad verin. Örneğin “kasa hesabı”. Artık hücre adlarını görüntüleyen hücrede bu adı görebilirsiniz. Bu hücreden seçim yaparak o alana bir tıklama ile gidebilirsiniz.

 

Ya da bir hücreye “kasa hesabı” yazdıktan sonra bu yazılı hücreyi seçin. Ekle/köprü/bu belgede yerleştir komutunu verip gelen pencerede önceden tanımladığınız “kasahesabı” bölgesinin adını bulup bağlantıyı yapın. Artık bu ada tıkladığınızda doğrudan o alana gidilecektir.

 

 

 

19- Excelde Koşullu Biçimlendirme

Koşullu biçimlendirmeden kasıt, bir hücrenin içeriğine göre biçiminin değişmesidir. Örneğin 24 numaralı örnekte geçme notu hesaplatmıştık. Öyle bir düzenleme yapalım ki geçme notunu değerlendirdiğimiz hücre, eğer geçme notu 50 ise yeşil renk alsın, yok kaldı ise 50 nin altında sonuç üretiliyor ise kırmızı renk alsın...gibi..biçimlendirmelere koşullu biçimlendirme adı verilir. Söylediğimiz örneği yapalım.

 ÖRNEK26:

Artık bir klasik olan geçme notu durumunu hesaplayan tabloyu oluşturdum. Sonuç hücresini seçip Biçim/Koşullu biçimlendirme komutunu verdim. Gelen pencerede Ekle tuşuna basarak iki ayrı koşul durumu açtım. Birinci koşula 50 ye eşit ya da büyükse yazı tipi kalın ve yeşil renkli ayarlaması yaptım. İkinci koşula 50 den küçük ise yazı tipi kırmızı ve kalın ayarlaması yaptım. Tamam tuşuna bastım. Bununla da yetinmedim tüm bunların resmini çekip Şekil 64 e yerleştirdim. Hepsi bu kadar. Notumuz 50 nin altında ise kırmızı, üstünde ise yeşil renk belirecektir.

 

Şekil 62

 

 

20-Birden Fazla Veriye Bir Komutla İşlem Yapmak (Özel Yapıştır Komutu)

 Bir ya da birkaç sütun ve onlarca satırdan oluşan bir veri grubunuz olduğunu düşünelim. Bu verilerin tümünü bir seferde bir sayı ile çarpmanız gerekse, tümüne birden bir sayı eklemeniz, çıkarmanız, bölmeniz gerekse nasıl bir işlem yapardınız?

 Önce veri grubuna uygulanacak sayı bir hücreye girilir. Ardından bu hücre seçilip Ctr+C ile panoya kopyalanır. Daha sonra veri kümesini oluşturan hücreler grubu seçilir, Düzen/Özel yapıştır komutu verilir. Gelen pencerede uygulanacak işlemler sorulmaktadır.

 Çarpma yapılacak ise mesela, alt bölümde çarp sekmesi seçilir ve tamam düğmesine basılır. Tüm küme ayrı yere yazılıp panoya kopyalanan değer ile bir seferde çarpılmış olur. Bir örnekle açıklayalım

ÖRNEK27:

 

Şekil 63

Tamam düğmesi tıklandığında tüm veriler 10 ile çarpılır. Aynı komut penceresini incelerseniz bir hücrenin sadece formülünü, sadece değerini, sadece biçimini...başka bir yere kopyalamak için de bu komutun kullanılabileceğini görürsünüz.

 

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.

 

Şekil 64

 

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ığı sütunu söylemek gerekiyor. (formülün açılımını şekil üzerinde inceleyin)

 Başlangıç olarak isimlerin başladığı B sütunu 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ıç sütunu 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.

 

  25-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 sütunda 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. İşte sonuç;

 

Şekil 65

 

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 #YOK 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!.

 26-DÜŞEYARA() VE EHATALIYSA() BİRLİKTELİĞİ

 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.

 

Şekil 66

 

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?

 

27-Zaman Fonksiyonlarının Kullanılması

a)    =BUGÜN() o günün tarihini verir.

b)    =DAKİKA(12:15:23)   içine uygun formatta girilen saat verisindeki 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ğerğ-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)   =ŞİMDİ() Girilen anı 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.

 

28-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ışın..:)

 Hemen sözü edilen örneği verelim;

 ÖRNEK29:

Bir sütunda 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.

 



Şekil 67

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

 

Yorum Yaz
Arkadaşların Burada !
Arkadaşların Burada !