İleri Excel Raporlama - Sinan Dilaver

Birden çok sayfadaki ve gruplandırılmış değerlerin her bir sayfadaki grupları bazında toplamını hesaplayan bileşik fonksiyonu aşağıda ve örneğini görüntüde bulabilirsiniz.  =ÇOKETOPLA(DOLAYLI(A2&"!C:C");DOLAYLI(A2&"!B:B");B2)  Açıklaması:  Sayfa adını A2 hücresinden almakta, sayfa adını Dolaylı fonksiyonundaki “&” işaretinden sonraki kısım ile birleştirmektedir. Bu metin birleştirmesiyle elde edilen bilgiden dolaylı olarak ÇokETopla fonksiyonumuzun ilgili parametresini oluşturmaktadır. Örneğimizdeki Bölge Adı bilgisine eşit Bölgeler sayfası varsa ilgili sayfa bulunmuş ve bu sayfadaki ilgili kolondan bilgiler alınmış olmaktadır.  Örneğimizdeki A2&"!C:C" metin birleştirmesiyle Bölge1 sayfasındaki C kolonunda bulunan sayılar toplanacağı bildirilmektedir.  Örneğimizdeki A2&"!B:B" metin birleştirmesiyle Bölge1 sayfasındaki Ürün Grubu kolonunda, formülümüzün bulunduğu satırda B kolonundaki “Kalem” değeri aranacağı belirtilmekt...

Excel programının Veri sekmesindeki ‘Birleştir’ düğmesini kullanılarak birden fazla sayfadaki değerlerin toplamını hesaplatmışsınızdır.  Bunun  benzerini bileşik  fonksiyonlarla yapmaya ne dersiniz?  Burada  b irden fazla sayfanın ilgili kolonunun alt toplamı bir toplamlar sayfasında hesaplanmaktadır. Ayrıca da bu toplamların toplamı bulunmaktadır.  İlgili sayfadaki ilgili kolonunun toplamı  hesabı:   =TOPLA(DOLAYLI(A2&"!B:B"))  Böylece b ölgenin tüm ürünlerinin tutarları toplamı hesaplanmış olmaktadır.  A2 hücresinde, toplamı alınacak sayfanın adı yer almaktadır. Örneğimizdeki sayfalarda Bölgelerin satış değerleri listelenmektedir. Dolayısıyla da, sayfa adlarında ve A kolonunda Bölge adları kullanılmıştır.  Bölgeler toplamı hesabı ise aşağıda verilmiştir:  =TOPLA(B:B)   

İki dönem, örneğin iki yıl değerlerinin karşılaştırılması, farkın, değişim yüzdesinin hesaplanması, değişim yönünün görüntülenmesi ve değişimin basit yorumunun yapılması ile ilgili örneği aşağıda verilmiştir.  Değişim Oranı: =YUVARLA([@Fark]/[@[Yıl-1]]*100;0)  Artı & Eksi: =İŞARET([@[Değiş. %]])  Yorum: =EĞER([@[Artı&Eksi]]=1;"Artmış";EĞER([@[Artı&Eksi]]=-1;"Azalmış";"Değişmemiş")) 

Excel programını kullanırken, bir hareketler ( transaction ) tablosundan Pivot kullanmadan raporlama yapmak için:  Bu tablodaki anahtar kolonun bilgilerini yeni boş bir sayfaya kopyalayın.  Yeni sayfadaki bu kolonda ‘Yinelenenleri Kaldır’ düğmesini kullanarak tekrarlı satırları sildirin.  Sonra bu kolonda A’dan Z’ye sıralama yaptırın.  Bu kolonun sağındaki kolonlarda, bu kolondaki tekrarsız ve sıralı bilgileri hareketler tablosundan tek tek buldurup, ÇokETopla (SumIfs) gibi fonksiyonları kullanıp gerekli toplama, vs. küme işlemlerini yaptırarak raporunuzu hazırlayın. 

Excel programından çıktı alırken çıktının her sayfasının başında tablomuzun ilk satırının ve ilk sütununun görünebilmesi için: Şerit menüdeki ‘Sayfa Düzeni’ başlığı altındaki ‘Başlıkları Yazdır’ seçeneğini tıklayın. Açılan ekranda ‘Üstte yinelenecek satırlar’ alanında tablonuzun ilk satırını işaretleyin, ‘Solda yinelenecek sütunlar’ alanında ise tablonuzun ilk sütununu işaretleyin. 

 

Excel programında, iki kolonun aynı satırdaki hücrelerini tek tek çarptırıp, çarpımlar toplamını hesaplatmadan bu sonucu Topla.Çarpım fonksiyonuyla elde edebiliriz.  Örneğin Tutar kolonu oluşturmadan, yani Fiyatlar ile Adetleri tek tek çarptırmadan Tutarlar Toplamını tek bir hücre içinde hesaplatmak için Topla.Çarpım fonksiyonunu kullanabiliriz.   =TOPLA.ÇARPIM(D2:D10;E2:E10) 

Tarihe göre sıralı bir listeden belirli bir ürünün en son tarihli fiyatını ve tarihini getiren cümleleri aşağıda bulabilirsiniz.  Son Fiyat Cümlesi: =ARA(2;1/($A$2:$A$9=E12);$C$2:$C$9) Fiyat Tarihi Cümlesi: =ARA(2;1/($A$2:$A$9=E12);$B$2:$B$9)  

Bir Excel sayfasında bir hücredeki belli karakterin kaç tane olduğunu bulan bir cümle paylaşıyorum. Aşağıdaki örnekte adedi bulunmaya çalışılan karakter ',' karakteridir.  Bu karakter örneğin virgülse, bu cümlede ufak bir değişiklik yaparak kaç kelime vs olduğu buldurulabilir. 

Excel programında bir kolonda birden fazla kriterle örneğin toplama yaptırmak için birkaç cümle sunuyorum. Birincisi örneğin Şişli, Beşiktaş gibi kriterlerin cümlenin içinde yazıldığı yazılış şekli, ikincisi bunun açılımı, üçüncüsü bu kriterlerin ardışık hücrelerden okutulduğu yazılış şeklidir. Bu cümle grubunun yukarısındaki AltToplam fonksiyonunu içeren cümle ise, buradaki kriterlere göre filtrelenmiş tablonun Borç Toplamı kolonu toplamını vermektedir.   

  Sunumlarda kolay okunması amacıyla hazırladığım, sayı örneğin binden büyük ise "... K", milyondan büyük ise "... M" olarak gösteren bir Excel  cümlesini aşağıda bulabilirsiniz.  =EĞER(ESAYIYSA(A3)=YANLIŞ;EĞER(EBOŞSA(A3)=DOĞRU;"";A3);EĞER(MUTLAK(A3)<1000;METNEÇEVİR(A3;"0.0");EĞER(MUTLAK(A3)<1000000;METNEÇEVİR(A3/1000;"0.0")&" K";EĞER(MUTLAK(A3)<1000000000;METNEÇEVİR(A3/1000000;"0.0")&" M";METNEÇEVİR(A3/1000000000;"0.0")&" B")))) 

Akıllı Tablo ve Filtre Toplamlarının  Tablo Altında  Buldurulması  

Çift Yönlü (Çapraz = Cross) Rapor için bileşik fonksiyon:  =ÇOKETOPLA(Satışlar[Satış Toplamı];Satışlar[Ürün Cinsi];$F17;Satışlar[Ay Adı];G$16)  

Akıllı tablo hazırladıktan sonra otomatik filtreleme düğmeleri (dilimleyici) ekleyebilirsiniz.  Bu amaçla:  Akıllı tablo seçiliyken açılan Tablo seçeneğinin menüsündeki Dilimleyici Ekle seçeneğini tıklayın. Listelenen tablonuzun kolon başlıklarından istediğinizi seçin. Seçtiğiniz kolon başlığına ilişkin isimler, bir düğme grubu şeklinde birer kategori ismi olarak listelenecektir. Bu düğme grubunun görünüşünü istediğiniz gibi ayarlayın. Gerekiyorsa program menüsünde oluşan Seçenekler menüsündeki seçenekleri kullanarak daha detaylı ayar yapabilirsiniz. Bu düğme grubundan istediğiniz kategori ismine tıkladığınızda tablonuz bu isme göre filtrelenecektir.