IF/EGER yerine MAX/MIN Fonksiyonları – Negatif değer yerine 0 sıfır


IF/EGER fonksiyonları yerine MAX/MAK ve MIN/MİN fonksiyonları kullanarak daha kısa ve güzel bir formülle çözebileceğimiz birkaç örnek yapalım.

1.Aşağıda 2015 ve 2016 yılı adet satışları olan bir tablom var, bir yan sütunda adet artışlarını hesaplamak istiyorum ancak adet artışı 0 sıfırdan küçük ise (yani aslında adet azalmışsa) negatif değer yerine artışa 0 sıfır yazsın.

ifyerinemax

Eğer adet farkı sıfırdan küçük ise 0 sıfır getir, değil ise farkı getir.

ifyerinemax2

Adet farkı ve 0 sıfır değerinden büyük olanı getir.

2.Tutarın %20 si kadar vergi alalım, %20’si 1000$’ı geçiyor ise 1000$ vergi alalım.

ifyerinemin

Vergi tutarı 1000$ i geçiyorsa 1000$ getir geçmiyorsa tutarın %20’sini getir.

ifyerinemin2

Tutarın %20’si ve 1000$ arasında küçük olan değeri getir.

Excel dosyasını indirmek için tıklayınız:

ifyerinemaxmin

İç içe Eğer/Nested IF


IF/EGER Fonksiyonu ve genel kullanımı IF/EGER fonksiyonuna giris konusunda görmüştük. Ancak birden fazla koşul aradığımız durumda tek IF/EGER fonksiyonu yeterli gelmeyecektir bu tip durumlarda nestedif/iç içe eğer yazarak çözüme ulaşmaya çalışacağız. nestedif/iç içe eğer  adı üzerinde birden fazla eğer fonksiyonunun ayni formül içinde beraber kullanılmasıdır. Basitten zora doğru giden örnekler ve önemli noktalarla konuyu anlamaya çalışalım.

Eğer(aranan koşul;koşul sağlanırsa değer; Eğer(diğer aranan koşul;koşul sağlanırsa değer; Eğer(diğer aranan koşul;koşul sağlanırsa değer; Eğer…; hiç bir koşul sağlanmazsa değer)))

1.Aşağıdaki örnekte öğrencilerin sınav puanları olan bir tablo var. Eğer puan kısmi bos ise öğrenci sınava girmedi demektir ve sonuc kismina “girmedi”, sınav puanı 70’in üzerindeyse “geçti”, altındaysa ise “kaldı” yazdıracağız.

nestedif_iciceeger2

Formul:

=IF(B2=””,”Girmedi”,IF(B2>=70,”Geçti”,”Kaldı”))

Not: Formüller her zaman soldan sağa doğru okunur. Yani bu formül için öncelikle bos hücre var mı diye bakar ve bos ise “girmedi” sonucunu verir, bos değil ise bir yan koşula geçer ve 70’den büyük mu diye bakar büyükse “geçti” sonucunu verir, 70’den büyük değil ise diğer tüm durumlarda “kaldı” sonucunu yazdırır.

nestedif_iciceeger

Daha iyi anlaşılması için formülün nasıl ilerlediğini bir akis şemasında gösterelim.

nestedifflow

Okumaya devam et

Şirket Çalışan Kadın/Erkek Dağılımı Dinamik Dashboard


dashboard

Daha net görüntülemek için resme tıklayınız

Çalışanların cinsiyet dağılımlarını bölge, şehir, departman ve pozisyonlara göre gösteren dinamik bir Dashboard’umuz var.

Dosya içerisinde “dashboard”, “data” ve özet tabloların tutulduğu “pivot” isimli üç sayfamız mevcut.

Bu Dashboard’u hazırlarken kullandığım araçlar ve formüller listesine aşağıdan ulaşabilirsiniz. Konu anlatımlarına link olarak da yönlendirme yaptım, dosyayı incelerken takıldığınız yerler için detaylı konu anlatıma ulaşabilirsiniz ya da yorum kısmına yazarak sorularınızı paylaşabilirsiniz.

1.Öncelikle mevcut datadan özet tablolar oluşturuldu. (ilgili konu anlatımı için tıklayınız)

2.Dinamik secim yapabilmek için Dilimleyici/Slicers ekledik (ilgili konu anlatımı için tıklayınız) ve tüm özet tabloları birbirine bağladık. Yani dilimleyici üzerinde bir secim yapıldığında diğer özet tablolar da değişecek. (ilgili konu anlatımı için tıklayınız)

Not: tüm departmanları grafik üzerinde ayrı ayrı görebildiğimiz için departmanlar dilimleyicisi ile departmanlar grafiğini oluşturduğumuz “PivotTable2” bağlantısı kaldırıldı.

3.Özet tabloları yan taraftaki oluşturduğum benzer tablolara alırken INDEX/INDIS ve MATCH/KACINCI Fonksiyonları kullanıldı. Grafikler bu tablolardan oluşturuldu. (ilgili konu anlatımı için tıklayınız)

4.Secimle değişen metinler oluştururken TEXT/METNEÇEVİR fonksiyonunu kullandık. (ilgili konu anlatımı için tıklayınız)

5.IFERROR/EGERHATA ile formüllerin hata döndürmesi yerine hücrede 0 ya da bos değer yazdırmasını sağladık. (ilgili konu anlatımı için tıklayınız)

6.Bing Map harita uygulamasi konu anlatımı için tıklayınız.

Excel dosyasını indirmek icin tıklayınız:

sirket-calisanlari-cinsiyet-dagilimi

Özet Tablo Hesaplanmış Alan/Öğe


Bu konuda oluşturduğumuz Özet Tablo’ ya Hesaplanmış Alan (Calculated Field) ya da Hesaplanmış Öge (Calculated Item) ekleyerek çeşitli işlemler yapmayı öğreneceğiz.

Hesaplanmış Alan/Calculated Field Ekleme

Özet Tablo alanları mevcut datamızın baslıklarıdır. Hesaplanmış alan eklemek bize bu baslıklar arasında işlem yapma olanağı sağlar.

hesaplanmisalan2

hesaplanmisalan1

1.Özet Tablo üzerinde iken menüde aktif olan Özet Tablo araçlarına/PivotTable Tools gidelim ve seçenekler tabından Hesaplanmış Alan/Calculated Field komutuna tıklayalım.

hesaplanmisalan3

Basit bir vergi hesaplaması yapalım

(Satış tutarı-Maliyet)’in %18 i vergi tutarına eşit olsun.

2.Acılan pencerede Name kısmına hesaplama sonrası özet tablo üzerinde görülecek yeni alan adını yazalım.

3.Formül kısmına ise istediğimiz hesaplamayı girelim.

hesaplanmisalan

Vergi baslığı altında bir hesaplanmış alan eklenmiş oldu.

Okumaya devam et

Form Denetimleri/Form Controls


Form Denetimleri/Form Controls VBA kodu kullanmadan üzerinde çeşitli seçimler yapma, dinamik grafik ya da tablo oluşturma gibi çeşitli durumlarda kullanabileceğimiz nesnelerdir.

Ayrıca yazdığımız bir makroyu çalıştırmak için de button/düğme sık kullanılan bir form denetimidir.

Combo box (Açılan Kutu), List box (Liste Kutusu) gibi form kontrolleri üzerlerindeki listeden seçim yapmamizi sağlar. Ve seçili ogenin index değerini (listedeki sırası) bağlantı verilen hücrede gösterir. Option Button (Seçenek Düğmesi) ya da Check box (Onay Kutusu) ise üzerlerindeki ogeleri seçili hale getirmenize yardımcı olur. Seçili ogeyi index numarası ya da true/false değeri olarak bağlantı hücresinde gösterir. Spin Button (Değer Değiştirme Düğmesi) ve Scroll Bar (Kaydırma Çubuğu) ise kaydırma okları yardımıyla belirlediğimiz bağlantı hücresindeki değerin artmasını veya azalmasını sağlar. Button (Düğme) ise var olan ya da daha sonra yazacağınız bir makro kodunu kendisine atayarak çalıştırmamızı sağlar.

Active X denetimlerinden farkı form denetimleri kullanmak için makro bilgisine ihtiyaç yoktur. Active X denetimleri eklendiğinde VBA kod bolumu açılır, kontrolün çalışmasını tetikleyen bir olay (event) ve bu olay sonucunda gerçekleşmesini istediğiniz VBA kodunu yazmanız beklenir.

Form Denetimi Ekleme

Menü de Geliştirici Sekmesi/Developer Tab altında, Ekle/Insert komutuna tıklayalım ve acılan listede Form Denetimleri/ Form Controls başlığı altından istediğimiz kontrolü ekleyebiliriz.

formdenetimleri

Menüde Geliştirici Sekmesi/Developer Tab görünmüyor ise tıklayınız.

Form Denetimleri

  1. Button/Düğme
  2. Combo box /Açılan Kutu
  3. Check box /Onay Kutusu
  4. Spin button/Değer Değiştirme Düğmesi
  5. List box /Liste Kutusu
  6. Option button/Seçenek Düğmesi
  7. Group Box
  8. Label/Etiket
  9. Scroll Bar/ Kaydırma Çubuğu

Okumaya devam et

Özet Tabloda Boş ve Hata İçeren Hücre İçeriği Değiştirme


Oluşturduğumuz özet tabloda herhangi bir değer içermemesinden ötürü bazı hücreler aşağıdaki şekilde boş görünür ya da datada hata içeren hücreler var ise bu Özet Tablomuza #YOK/#NA şeklinde yansır. Bu konuda boş ya da hata içeren hücreler yerine istediğimiz farklı bir değeri nasıl gösterebileceğimize bakalım.

Özet tabloda boş ve hata içeren hücreler yerine 0(sıfır) değerini girelim.

Özet tablo üzerinde herhangi bir yerdeyken sağ tık, Özet Tablo seçenekleri/PivotTable Options açılan pencerede ilk karşımıza çıkan sekmede Boş hücreler için göster/For empty cells show kısmına (default olarak seçili gelir) görünmesini istediğimiz değeri girelim.

Bir üstte ise Hata değerleri için göster/For error values show kısmında (default seçili gelmez) önce kutucuğu işaretleyelim ve sonrasında görmek istediğimiz değeri girelim ve Tamam/Ok e basalım.

pivotemptycells

excel dosyasını indirmek için tıklayınız:

ozet_tablo_bos_ve_hatali_hucreler

Özet Tablo/Pivot Table Hızlı Düzenlemeler


Bu konuda menüdeki hazır komutları kullanarak özet tablonun çeşitli alanlarında nasıl hızlı düzenlemeler yapabileceğimiz üzerinde duracağız.

Bu düzenlemeler:

  • Alt toplam/Sub Totals görüntüleme veya gizleme
  • Genel Toplam/Grand Totals görüntüleme veya gizleme
  • Rapor Düzeni/Report Layout
  • Bos hücreler/Blank Rows

Tüm düzenlemeleri Özet Tablo üzerindeyken menüde aktif hale gelen Özet Tablo Araçları/PivotTable Tools altında Tasarım/Design sekmesinde Düzen/Layout grubu üzerindeki komutlarla gerçekleştireceğiz.

excelpivot1
excelpivot2

excelpivot3

Uzerinde calisacagimiz ozet tablo

Alt toplam/Sub Totals görüntüleme veya gizleme:

Secenekler turkce olarak sirayla:

  1. Alt Toplamları Gösterme
  2. Grubun Altında Tüm Alt Toplamları Göster
  3. Grubun Ustunde Tüm Alt Toplamları Göster

pivotsubtotal Okumaya devam et

Özet Tablo Yenileme, Veri Kaynağını Değiştirme


Bu konuda Özet Tablo/Pivot Table datası içerisinde yapılan değişikler sonrası Özet Tabloyu Yenileme ve gereken durumlarda Özet Tablo veri kaynağını değiştirme konuları üzerinde duracağız.

Bununla beraber sıkça karşılaşılan aşağıdaki sorulara da konu içerisinde cevaplandırıyor olacağız.

  1. Datayı değiştirdiğimde bu dataya bağlı tüm özet tablolar tek komutla yenilensin.
  2. Dataya yeni eklediğim veriler Özet Tabloma yansımıyor.
  3. Dataya yeni veri eklediğimde her seferinde veri kaynağını değiştirmeden Özet Tablomu nasıl güncelleyebilirim?
  4. Calisma kitabini her açtığımda Özet Tablolar otomatik yenilensin.

Aşağıdaki gibi bir data ve bu datadan oluşturduğumuz bir Özet Tablo olsun.

updatepivottable

Veri Kaynağı

ozet-tablo

Özet Tablo

Özet Tablo Yenileme/Refresh Pivot Table Okumaya devam et

ETARİHLİ – DATEDIF fonksiyonu


iki tarih arasındaki yıl, ay ya da gün sayısını hesaplama

DATEDIF fonksiyonu verilen iki tarih arasındaki yıl, ay ya da gün sayısını hesaplamamızı ve istediğimiz formatta göstermemizi sağlar.

ETARİHLİ(Başlangıç_tarihi, Bitiş_tarihi, Zaman_Birimi)

DATEDIF(Start_Date,End_date,Unit)

Bu fonksiyonun diğer fonksiyonlardan farkı Excel tarafından dökümente edilmemiştir, bunun anlamı formülü hücreye girdiğimizde fonksiyon listesinde ismi yoktur ve girdiler için size yön göstermez.

Aşağıdaki resimde gördüğümüz gibi formülü yazmaya başlamamıza rağmen “Date” ile başlayan diğer formüller listelendi ancak DATEDIF Fonksiyonu listede gelmedi.

datedif2

Formülü yazmayı bitirdiğimizde fonksiyon geldi ancak içeriye girmemiz gereken girdilerle ilgili herhangi bir bilgi de mevcut değil.

datedif3

Başlangıç tarihi/Start_date

Bitiş tarihi/End_date:

Zaman_Birimi /Unit: Sonucu görmek istediğimiz zaman birimi

Zaman_Birimi /Unit değerleri:

y: İki tarih arasındaki yıl sayısı

m: İki tarih arasındaki ay sayısı

d: İki tarih arasındaki gün sayısı

ym: Yılları hesaba katmadan iki tarih arasındaki ay sayısını

yd: Yılları hesaba katmadan iki tarih arasındaki gün sayısını

md: Yılları ve ayları hesaba katmadan iki tarih arasındaki gün sayısını hesaplar.

Not : Zaman_Birimi /Unit değerleri formül içerisine çift tırnak içinde girilmelidir.

Şimdi bir örnekle konumuza devam edelim:

datedif1

datedif4

İki tarih arasındaki yıl sayısı:

=DATEDIF($C$3,$C$4,”y”) birimi sonucun yaninda belirtmek icin =DATEDIF($C$3,$C$4,”y”)&” yıl”

İki tarih arasındaki yıl, ay ve gün sayısı:

=DATEDIF($C$3,$C$4,”y”)&” Yıl “&DATEDIF($C$3,$C$4,”ym”)&” Ay “&DATEDIF($C$3,$C$4,”md”)&” Gün”

Excel dosyasını indirmek için tıklayınız:

datedif

Excel Dünya Şampiyonluğu Yarışması


Merhaba arkadaşlar, Microsoft Kasım-Aralık aylarında gerçekleşecek olan bir Excel Dünya Şampiyonluğu yarışması düzenledi. 4 farklı aşamadan oluşan bu yarışma Excel bilgi ve becerilerinizi detaylı olarak ölçmeyi hedeflemiş. İlgilenenler için 1., 2. ve 3. aşamaların Excel çalışma kitaplarına aşağıdan ulaşabilirsiniz. Final aşaması yayınlanır yayınlanmaz 4. aşamanın sorularını da bu konu altından takip edebilirsiniz.

Katılmak isteyenler için önümüzdeki yıl yarışma başlamadan tarihleri sizlerle paylaşıyor olacağım, belki bir dünya şampiyonu bizim içimizden çıkar!

Bol Şans

2016-10-27-21_35_04-excel-2016

1.aşama indir

2016-10-27-21_35_04-excel-2016

2.aşama indir

2016-10-27-21_35_04-excel-2016

3.aşama indir

 

 

 

 

Yarışma hakkında detaylı bilgi için :

https://techcommunity.microsoft.com/t5/Excel-World-Champ/ExcelChampINTL/m-p/12696#M33