İ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)   

Ağırlıklı ortalama hesabında, matematiksel olarak her bir değer ağırlığıyla çarpılır ve çarpımlar toplanır. Bulunan toplam, ağırlıklar toplamına bölünür.  Örnek olarak A2, ..., A6 hücrelerinde değerler, B2, ..., B6 hücrelerinde ağırlıklar olsun.  Ağırlıklı Ortalaması =Topla.Çarpım(A2:A6;B2:B6)/Topla(B2:B6) 

İ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ş")) 

Çalıştığım dershanede öğrencilerimin hakkımda yazdıkları ve dershanenin sitesinde yayınlanan görüşleri aşağıdadır. 

Aynı tür girdileri kullanan, aynı amaçlı sonucu veren ve çok sık yaptığımız işlemleri bir fonksiyon oluşturup, Excel’in kendinde tanımlı fonksiyonları gibi kullanmak işlerimizi çok kolaylaştırabilir. Bir fonksiyon, bir veya birden fazla girdi bilgisi (parametre) alır, amaca yönelik işlem yapar, en az ve en fazla tek bir sonuç verir. Fonksiyonun bu özelliği, fonksiyonlar ile ilgili gönderimimde belirttiğim gibi matematikte de aynıdır. (Fonksiyonlar ile ilgili diğer temel bilgiler için bu gönderimim okunabilir.) Yapılacak İşlemler (Ana Hatlarıyla) Bu amaçla önce bir fonksiyon oluşturma ortamımız olacak. Bu fonksiyona bir veya birden fazla parametre almasını sağlayacağız. Bu parametreleri kullanıp amaca göre işlemler yapacağız. Bu işlemlerden sonra en az ve en fazla tek bir sonuç elde edeceğiz. Sistem, fonksiyonu çağıran yere bu sonucu çıktı olarak gönderecek.   Yukarıda genel olarak sözünü ettiğimiz işlemleri, şimdi adım adım görelim: Fonksiyonu oluşturma ortamı...

İstediğiniz hücrelerin başkaları tarafından değiştirilememesi, gizlediğiniz kolonların ve satırların başkaları tarafından görünür yapılamaması için:  Önce satır ve sütun başlıklarının birleşim yerini tıklayarak tüm hücreleri seçin. Farenin sağ düğme menüsünden Hücreleri Biçimlendir seçeneğini tıklayın. Açılan ekranda Koruma sekmesine gidin. Kilitli seçeneğinin işaretini kaldırın (Bu işlem tüm hücrelerin kilitlenmesini önler). ‘Tamam’ düğmesine basın.  Excel sayfanızdaki değişiklik yapılmasını istemediğiniz kolon, satır veya hücreleri seçip, biraz önce işaretini kaldırdığınız Kilitli seçeneğini bu kolonlar, hücreler için yeniden işaretleyiniz (Dolayısıyla daha önce tüm hücreler kilitliyken şimdi bu seçtiğiniz hücreler, vs. kilitli olmuş olacaklar). Gizlemek istediğiniz kolon ve satırları gizleyin. Excel programının üst şerit menüsündeki Gözden Geçir alt menüsüne gidin. Gözden Geçir alt menüsündeki Sayfayı Koru düğmesini tıklayın. Bir parola belirleyin ve açılan ekranda şimdil...

Elimizdeki kelimenin bir hücrede var olup olmadığını kontrol etmek istiyoruz. Bu işlemi bir bileşik fonksiyonla yapabiliriz. Bu fonksiyonu ve örneğin resmini aşağıda sunuyorum.   =EĞER(ESAYIYSA(BUL(B2;A2));"Var";"Yok")  ESayıysa fonksiyonunun İngilizcesi IsNumber ,  Bul fonksiyonunun İngilizcesi ise Find  fonksiyonudur. 

Excel programında hücrelerde yapılacak işlemler bütünü hücrede eşit işaretiyle başlayarak yazılmaktadır. Bir hücrede eşitlik yazılması, o eşitliğin sonucunun o hücreye atanacağı anlamına gelir. Eşit işaretinin sağına yazılan ya bir formül, ya basit veya bileşik fonksiyon ya da bunların karışımıdır. Bunu matematikteki şu eşitlikler ile gösterebiliriz: y = x + 30 y = g(x) y = f(g(x)) Buradaki y eşitliğin yazıldığı hücreye, x ise hesapta kullanılan bilginin bulunduğu hücreye karşılık gelmektedir. Dolayısıyla bu bağlamda Excel’in her bir hücresi, matematikteki değişkenin karşılığıdır. Hesapta kullanacağımız bilgi A1 hücresinde, hesabı yaptıracağımız eşitlik de B1 hücresinde olsun. Yukarıdaki x değişkenine A1 hücresi, y değişkenine ise B1 hücresi denk gelmektedir. Fonksiyonlarımızın adları da g için ‘radyan’, f için ‘sin’ olsun. Bu durumda eşitliklerimiz sırasıyla şöyle olacaktır. B1 = A1 + 30 B1 = radyan(A1) B1 = sin(radyan(A1)) Excel programındaki bileşik fonksiyon, mate...

Değişik süre tiplerine göre vade tarihi hesaplama örneğini ve ilgili bileşik fonksiyonu aşağıda bulabilirsiniz.  =EĞER(D2="Gün";B2+C2;EĞER(D2="Ay";TARİH(YIL(B2);AY(B2)+C2;GÜN(B2));EĞER(D2="Hafta";B2+C2*7;EĞER(D2="Yıl";TARİH(YIL(B2)+C2;AY(B2);GÜN(B2))))))

En Yüksek Notu Alan Kim ve Listede Kaçıncı (Notu Maksimum olanın aranıp, bu kişinin  ismini ve  listedeki yerinin getirilmesi) 

=TOPLA(DOLAYLI(D9):KAYDIR(DOLAYLI(D9);D10-1;0))  Bu  bileşik fonksiyon, s ayılardan oluşan bir listenin belli satırından itibaren belirtilen satır adedi kadar ardışık satırlardaki sayıları toplamaktadır.  'Topla' fonksiyonunun parantezi içerisindeki işlemler, toplamda kullanılacak satır başlangıcını ve aralığı dinamik olarak belirlemektedir. 

Excel programındaki Eğer (If), bir SQL dilindeki iif, bir programlama dilindeki If ile mantıktaki ve günlük dildeki Eğer arasında kavramsal olarak özdeşlik vardır. Hepsinde de sırasıyla Koşul, koşula Uyulursa Ne Yapılacağı, koşula Uyulmazsa Ne Yapılacağı belirtilir. Bunlar arasında ancak biçimsel farklılık söz konusudur. Biçimsel farklar parantezlerin, If bloğu kapamasının mevcudiyeti, türü vs. gibi farklılıklardır. Excel’de Eğer(Koşul, Doğruysa-Değer, Yanlışsa-Değer) veya If(Condition, Value-If-True, Value-If-False) şeklinde olan kullanımın, programlama dilinde karşılığı örneğin aşağıdaki gibidir.   If <Koşul>     Then <Doğruysa Değer veya İşlem>     Else <Yanlışsa Değer veya İşlem>   End-If Excel ve SQL’de bu türden yapılacakların belirtilmesi bir fonksiyonla olduğu için, Eğer ifadesi de bir fonksiyonla ve fonksiyonun içinde kullanılan parametrelerle sisteme belirtilmektedir. Excel’dekine benzer olarak, SQL’de kullanılan fo...

Bir programlama dilindeki, bir SQL dilindeki veya Excel programındaki fonksiyonların ortak yönlerini bilirsek, örneğin Excel programındaki fonksiyonları bildikten sonra diğerlerindeki fonksiyonları kolayca algılayabiliriz. Tüm fonksiyonların ortak yönü, dışarıdan aldığı parametreleri diğer bir deyişle girdileri kullanıp tek bir sonuç sunmalarıdır. Bu özellik, matematikteki fonksiyonda da aynıdır. Bu türden bir dildeki fonksiyon, genelde diğer dillerde de bulunur. Bulunmayanlar ya diğer diller ile ana konusu itibariyle farklılık gösteriyordur ya da diğer dillere de sonraki versiyonlarda ekleneceklerdir. Ayrıca, bir dildeki fonksiyonların isimleri ve parametreleri, diğer dillerdekine az çok benzer. Benzer fonksiyon kategorilerine tarih, matematik ve istatistik ile ilgili fonksiyonları örnek verebiliriz. Dolayısıyla bir dilden bildiğimiz bir fonksiyonun benzerini, diğer yeni öğrendiğimiz bir dilde bulmaya çalışabiliriz. Diğer dildeki isim veya parametre dizilişi olarak farklı fakat ay...

Dizi işlemleri, mevcut fonksiyonlar yeterli olmadığında veya bazı fonksiyonların dizi kullanımını zorunlu tutması sebebiyle kullanılmaktadır.  Biz burada, fonksiyonların yeterli olmamalarına ilişkin olanları ele alıyoruz.  Bir kolonda değer buldurup bu değerler üzerinden toplama vs. işlemi olmak üzere iki kere işlem yapmak yerine, dizi şeklinde kullanımla değerleri aynı anda buldurup bir seferde işlem yapmak mümkün olmaktadır.  Dizi işlemleri, programlamada bir döngü içerisinde yapılan işlem benzeridir .   

 

Elimizde bir tarih değil, sadece ay numarası varsa, DüşeyAra kullanmadan bu ay numarasına karşılık Ay Adını getirmek için iki cümle sunuyorum.   MetneÇevir fonksiyonunun İngilizcesi Text fonksiyonu,  İndis fonksiyonunun İngilizcesi ise Index  fonksiyonudur.   

Bir denklemin hedeflenen değerine ilişkin olarak, bu denklemdeki bir değişkenin olması gereken değerini buldurmak üzere #Excel programındaki ‘Hedef Ara’ ( Goal Seek ) aracını kullanabilirsiniz.  ‘Hedef Ara’, bir bilinmeyenli bir denklemin çözümünü size sunar. HedefAra fonksiyonunun İngilizce karşılığı GoalSeek fonksiyonudur.  Denklemin Excel’deki karşılığı, formülün girilmiş olduğu hücredir. Bu amaçla,    Denklemin bulunduğu hücreyi ‘Hedef Ara’ ekranındaki ‘Ayarlanan hücre’ alanında işaretleyin.    Hedeflenen değeri ‘Sonuç hücre’ alanına girin.    Değişkenin bulunduğu hücreyi de ‘Değişecek hücre’ alanında işaretleyin ve Tamam düğmesine basın.  Program size değişkenin olması gereken değerini hesaplayacaktır. ‘Hedef Ara’ aracının bir bilinmeyenli denklem çözümünden görünüşte başka kullanım alanları da vardır. Fakat aslında bunlar da birer bir bilinmeyenli denklem gibi gösterilebilir.

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) 

Fatura Satır No oluşturma işlemi, bu C kolonundaki her bir "Satıcı Adı ve Fatura No" bilgisi için 1'den başlayarak satır numarası üretmektedir.  C kolonunda, A ve B kolonlarının, yani Satıcı Adı ile Fatura No bilgilerinin birleşimi mevcuttur.  Gerekli bileşik fonksiyon aşağıdadır:  =EĞERSAY($C$2:İNDİS(C:C;SATIR());C4) 

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 DüşeyAra fonksiyonundaki sütun_indis_sayısı değerini her zaman sayarak bulmak ve kopyalanmış hücrelerde bu değeri değiştirmek, çok kolonlu tablolarda zor olmaktadır.  Bu amaçla, sütun_indis_sayısı değerini buldurmak için Kaçıncı fonksiyonunu kullanıyoruz.  

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ı  

Bir tablodaki her bir ürün grubuna göre düşey ara tablosundan ürün grubu aralığını, bu aralıktan da fiyat aralığını ve dolayısıyla Ucuz, Normal, Pahalı kategori bilgisini bulduran bileşik fonksiyonları aşağıdaki görüntüde bulabilirsiniz.  Amacımız: Bilgi getirilen kaynak tabloda birden fazla ürün grubuna ilişkin fiyat aralığı varsa, kaynak tablodaki ilgili ürün grubu satırlarından DüşeyAra yapılması Çalışma şekli: Önce işlem yaptığımız tablodaki ürünün ürün grubunun satır aralığı, kaynak tablodan buldurulmaktadır. Sonra bu satırlardaki fiyat aralığından, ilgili ürünün fiyatının karşılığı kategori DüşeyAra kullanılarak elde edilmektedir. Not: Burada kullandığımız DüşeyAra fonksiyonunun Aralık Bak parametresinde Yaklaşık Eşleşme yapmaktayız ve parametrede DOĞRU değerini seçiyoruz. Çünkü arama yaptığımız tabloda elimizdeki fiyatın tam karşılığı bulunmamakta ve iki sayısal değerin arasında bulunmaktadır.    

Ç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)  

Aynı kodlu fakat farklı kategorilere ait ve farklı sayfalarda olan  örneğin ürün adlarını, sayfa adı bir kolonda belirtilen sayfalardan bulup getirmektedir. 

 

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.