İleri Excel Raporlama - Sinan Dilaver

Macro kullanarak Excel'de süsleme yapmak için aşağıda örnek kodlama verilmiştir. Bu örneği bilgisayarınızda Macro yazma sayfasına kopyalayıp deneyebilir, değerleri ihtiyacınıza göre değiştirebilirsiniz. Açıklamaları tekli tırnak kullanılarak (tekli tırnaktan sonra) yazılmıştır.  Programı Excel'de oluşturduktan sonra Excel sayfasındayken bir dikdörtgen oluşturun, istediğiniz gibi renklendirin. Dikdörtgenin üzerine Süslemeler  gibi bir etiket yazın. Bu dikdörtgen seçiliyken farenin sağ düğme menüsünden Makro Ata seçeneğini kullanarak bu Macro programının ismini seçin.  Dikdörtgen bir düğme haline gelmiştir. Bu düğmeye basarak programda belirtilen süslemeleri yapın. Daha sonra, "=" işaretlerinin sağındaki değerleri istediğiniz gibi değiştirerek denemeler yapabilirsiniz.  ------------------------------------------------------------------------------------------------------------------  Public Sub Suslemeler()  Range("A1:H1").Select  'A1:H1 aralığını, yani ...

Excel Macro'da temel Tutar hesaplamaları aşağıdaki örneklerde açıklamalarla verilmiştir. Aşağıdaki tabloda henüz bir hesaplama yapılmamıştır.  Aşağıda görüntüde hesaplamalar ile ilgili program satırları verilmiştir. Bu Macro sayfasına Excel programında Alt-F11'a basılarak ulaşılır.  Bu görüntüden sonraki satırlarda görüntüdeki program satırları açıklamalar ile birlikte ve sizlerin kopya alacağı şekilde verilmiştir.  --------------------------------------------------------------------------------------------------------------------- Public Sub TutarHesaplamalari() 'Tutar Hesabı 'Her bir hücre Range("...") olarak kullanılmıştır.  'C2, D2 için açıklama: C2 ve D2 hücrelerindeki değerler çarpılmış ve E2 hücresine atanmıştır.     Range("E2") = Range("C2") * Range("D2")    Range("E3") = Range("C3") * Range("D3")    Range("E4") = Range("C4") * Range("D4")    Range("E5...

Public Sub CarpimTablosu()  For r = 1 To 9   For c = 1 To 9    Cells(r, c) = r * c   Next c  Next r End Sub Bir döngü örneği olarak, çarpım tablosu hazırlayan bir prosedür yazacağız. Çarpım tablosunda satır numarası ile sütun numarasını çarptıracağız. Yukarıda bu amaçla yazdığımız prosedür bloğunu görmekteyiz.  En temel döngü For-Next döngüsüdür. For-Next döngüsü, For başlangıç satırı ile Next bitiş satırı arasına yazılan işlem satırlarından oluşmaktadır. For-Next döngüsü For satırında belirtilen değişkeni, belirtilen başlangıç değerinden itibaren belirtilen bitiş değerine kadar otomatik olarak artırır. Bu artırma, bir artım değeri belirtilmemişse birer birer olur. Artırmadaki başlangıç değeri eşit işareti ile ' to ' terimi arasındaki sayıdır. Bitiş değeri ise ' to ' teriminden sonraki sayı olmaktadır.  Örneğimizdeki r değişkeni, satır numarasına ve c değişkeni ise sütun numarasına karşılık gelmektedir. Bu değişkenlerin değerleri iç içe For-N...

Bir hücredeki değere göre bir aralıkta koşullu biçimlendirme için, önce aralığı seçin.  Giriş  sekmesindeki  Koşullu Biçimlendirme düğmesiyle Yeni Biçimlendirme Kuralı ekranındaki sonuncu seçenek olan Biçimlendirilecek hücreleri belirlemek için formül kullan  seçeneğini seçin.  Aşağıdaki formül alanına  =$A1=$D$1 formülünü girin.  Formülü yazarken " = " işaretiyle başlıyoruz. Ardından "Eğer" veya "If" yazmadan koşul tümcesini yazıyoruz. Koşul tümcesinde, aralığın başlangıç hücresini kontrol edeceğimiz hücre ile karşılaştırıyoruz. Aralığın başlangıcı olan hücrenin adresini yazarken kolon adını sabitliyoruz ve satır numarasını sabitlemiyoruz. Karşılaştıracağımız hücrenin adresini yazarken hücreyi sabitliyoruz.  Ardından gerekli Biçimlendirme seçimi - aşağıdaki görüntüdeki gibi - yapılınca istediğimiz koşullu biçimlendirme oluşmuş olur.  Bundan sonra, D1 hücresindeki değeri değiştirdikçe, aralıktaki bu değere uygun hücreler, tanımlandığı gibi bi...

Excel tabloları arasında ilişki kurup düşey ara yapmadan Pivot oluşturma için gerekli aşamalar, aşağıda bir örnek üzerinden anlatılmıştır.  İki tane tablo oluşturalım. Bunlardan birinde Ürünler, diğerinde bu ürünlerin Satışları bulunsun. Bu tabloları akıllı tablo haline getirelim. Akıllı tablo yapmak için her bir tabloyu seçip, Ekle sekmesindeki Tablo düğmesine basıyoruz.  Bu tablolara, tablo seçiliyken görünen  Tasarım sekmesinin sol tarafındaki Tablo Adı alanında anlamlı isimler verelim.  Veri sekmesinden İlişkiler  düğmesini tıklayalım. Açılan ekranda Yeni düğmesine basalım.  Ardından açılan ekranda üstteki ‘Tablo’ alanında Satışlar tablosunu seçelim. Sağındaki ‘Sütun (Yabancı)’ alanında bu tablodaki düşey arada kullanılacak kolonu seçelim. Alttaki ‘İlgili Tablo’ alanında Ürünler tablosunu seçelim. Sağındaki ‘İlgili Sütun (Birincil)’ alanında düşey arada kullanılan alan ile birebir olan alanı seçelim.  Tablo ilişkisini tanımlayınca, Excel...

Her sayfa ve her yön bizim için birer boyuttur. Dolayısıyla sayfa bir boyut ( Dimension ), düşey ve yatay yönler de birer boyut olduğundan bizim bu raporumuz da 3 boyutludur.  Satış yapan kişilerin satış bilgileri de ayrı ayrı Excel dosyalarda bulunursa ve bu dosyaları da yani kişileri bir boyut olarak alacak olursak bizim raporumuz 4 boyutlu olurdu.  ------------------------------------------------------------------------------------------------------------------------ Aşağıdaki Fonksiyonların Genel Açıklaması Dolaylı fonksiyonundaki "!" işaretinden önceki bilgi ilgili hücreden alınıyor ve Sayfa Adı  elde ediliyor. Sayfa Adı ve "!" işaretinden sonraki Hücre Aralığı  bilgileri kullanılarak Dolaylı fonksiyonu sayesinde ilgili sayfadaki ilgili aralık belirlenmiş oluyor. Bu aralık,  ÇokETopla (SumIfs)  fonksiyonunun ölçüt / toplam aralığı olarak kullanılıyor.  ---------------------------------------------------------------------------------------------...

Kümülatif Toplam için iki ayrı formül veriyorum.  Formül-1. Bulunduğumuz satırdaki toplanacak değere üst satırdaki toplam değerinin eklenmesiyle =B2 =C2+B3 =C3+B4 =C4+B5 =C5+B6 =C6+B7 =C7+B8 =C8+B9 =C9+B10 =C10+B11 =C11+B12 =C12+B13 Formül-2. Aralık bazında toplama işlemi yapan TOPLA(aralık) fonksiyonundaki aralığın başlangıç hücresini sabit yapıp, bitiş hücresini serbest bırakılmasıyla =TOPLA($B$2:B2) =TOPLA($B$2:B3) =TOPLA($B$2:B4) =TOPLA($B$2:B5) =TOPLA($B$2:B6) =TOPLA($B$2:B7) =TOPLA($B$2:B8) =TOPLA($B$2:B9) =TOPLA($B$2:B10) =TOPLA($B$2:B11) =TOPLA($B$2:B12) =TOPLA($B$2:B13)

Konuyu bir örnekle anlatacağım.  Ev ile ilgili bir kampanya vardır. Bu kampanyaya katılmak için koşullar şunlardır:  Katılabilecek kişi evli olmalı, evi olmamalı, otomobili olmamalı ve yaşı 40'dan büyük olmalıdır.  F kolonuna ilgili satırlara bu koşulları kontrol eden bir Eğer cümlesi yazıyoruz.  Bu koşulların hepsinin aynı anda yerine getirilmesi için bu koşulların arasında Ve mantıksal bağlacı kullanılır. Biz de bu amaçla Ve bağlacına karşılık gelecek olan Ve fonksiyonunu kullanacağız.  Eğer fonksiyonu bildiğimiz gibi 3 parametreli bir fonksiyondur. Birinci parametresi 'Mantıksal Sınama', ikinci parametresi 'Doğruysa Değer', üçüncü parametresi 'Yanlışsa Değer'. 'Mantıksal Sınama' parametresinde koşul vardır ve kontrol işlemi yapılır.  Mantıksal Sınama parametresinde 1 koşul kullanılabilir. Fakat bizim 4 koşulumuz mevcuttur. Bu koşulları Ve fonksiyonunun parametreleri olarak kullanacağız. Bu koşulların hepsi aynı anda doğruysa fonksiyonun sonucu Doğ...

Takvim Oluşturma ile ilgili Macro programı aşağıdadır. 

Bir dizideki aralıklı değerlerin bir veri aralığında kaçar tane olduğunu bulmak için SIKLIK (Frequency) fonksiyonunu kullanırız. Bu fonksiyonun çeşitli kullanımlarını aşağıdaki görüntüde bulabilirsiniz.  Fonksiyon iki parametrelidir. Birinci parametresi Veri Aralığı , ikinci parametresi Aranan Değerler Aralığı dır. Veri aralığı, örneğimizde B kolonundaki notlardır. Aranan değerler aralığı ise, aranan aralıklı değerlerin sıralı olarak bulunduğu aralıktır.  E kolonundaki notlar, bulunan sonuçların kontrol edilebilmesi için B kolonundaki notların artan sıralanmış halidir.  Bu amaçla fonksiyonu formül çubuğuna yazarız. Aranan değerler aralığını seçeriz. Ardından Ctrl ve Shift tuşlarına basılıyken Enter tuşuna basarız.  Böylece Excel programı, aranan değerler aralığındaki her bir değerden küçük veya değere eşit olduğu  ve aynı zamanda bir önceki değerden büyük rakamların sayısını ilgili hücreye yazar.  Örneğin 70, 80, 90, 100 değerlerinin bulunduğu aranan değe...

Excel'de normalde aralıklara verdiğimiz adlara karşılık gelen aralıklar sabittir.  Bu adlara ilişkin Başvuru Yeri alanına doğrudan aralık yazmak yerine aşağıdaki bileşik fonksiyonu yazarak değişken yapabiliriz.  =KAYDIR(SAYFA1!$A$2;0;0;BAĞ_DEĞ_DOLU_SAY($A:$A)-1;1)  Böylece aralığın sonuna yeni satırlar ekledikçe aralık otomatik olarak genişleyecek ve dinamik hale gelecektir.  Yukarıda kullanılan KAYDIR fonksiyonu kullanılacak aralığın belirlenmesini sağlamaktadır.  Bu fonksiyonun parametreleri sırasıyla aşağıda verilmiştir:  1. Başlangıç (Referans) Noktası (örneğimizde SAYFA1!$A$2),  2. Aralığın bu noktadan kaç satır aşağıdan başlayacağı (örneğimizde 0),  3. Aralığın bu noktadan kaç sütun sağdan başlayacağı (örneğimizde 0),  4. Aralığın kaç satır yüksekliğinde olacağı (örneğimizde BAĞ_DEĞ_DOLU_SAY($A:$A)-1),  5. Aralığın kaç sütun genişliğinde olacağı (örneğimizde 1).  Fonksiyonların İngilizce karşılıkları:  KAYDIR -> OFFSET...

Bir değerin bir dizide artan veya azalan sıralamalı olarak ilk bulunduğu veya ilk-son ortalaması olarak yerini (sıra numarasını) bulmak için aşağıdaki fonksiyonları kullanabilirsiniz.  Dizimiz, $A$2:$A$9 aralığında ve aranan değerimiz de $D$10 hücresinde olsun.  Arananın Ortalama Yeri (Artan)    =RANK.ORT($D$10;$A$2:$A$9;1) Arananın İlk Yeri (Artan)                 = RANK.EŞİT($D$10;$A$2:$A$9;1) Arananın Ortalama Yeri (Azalan)  =RANK.ORT($D$10;$A$2:$A$9;0) Arananın İlk Yeri (Azalan)              =RANK.EŞİT($D$10;$A$2:$A$9;0) Rank.Ort ve Rank.Eşit fonksiyonları, aynı tür parametreler ile çalışmaktadır.  Parametreleri sırayla şunlardır:  1. Aranan değer veya değerin bulunduğu hücre 2. Arama yapılacak dizi aralığı  3. Arama artan yapılacaksa 1 , azalan yapılacaksa 0   Fonksiyon ile ilgili örnek aşağıdaki görüntüdedir. 

 Çok koşullu ve sanal tablolu DüşeyAra yapmak için aşağıdaki formülü kullanın.  =DÜŞEYARA($E11&$F11;ELEMAN({1;2};$B$3:$B$8&$C$3:$C$8;$A$3:$A$8);2;YANLIŞ) Burada E10 ve F10 hücreleri birleştirilerek aranan değer oluşturulmaktadır.  Bu formülde bulunan  ELEMAN({1;2};$B$3:$B$8&$C$3:$C$8;$A$3:$A$8) fonksiyonu,  $B$3:$B$8&$C$3:$C$8 aralığı ve $A$3:$A$8 aralığını birleştirip yeni bir tablo oluşturmaktadır. Bu oluşturulan tablo, DüşeyAra formülünde arama yapılan tablo olmaktadır.  Sonuç kolonundaki formülün çalışması için formülü yazdıktan sonra, hücrenin içindeyken ve sol elle Ctrl+Shift tuşlarına basılıyken sağ elle Enter tuşuna basmak gereklidir. 

Power BI kullanarak, şehir vs. ile ilgili verilerimizi haritada ilgili şehirler üzerinde görüntüleyebilmekteyiz. Bu amaçla şehir adlarımızın doğru yazılması gerekmektedir.  Ardından Görsel Ögeler kısmında 4ncü satır 1nci sütundaki Harita  isimli görsel öge düğmesine basıyoruz.  Görsel Ögeler altında çıkan parametreler arasındaki   Konum alanına Şehir alanını,   Açıklama  alanına örneğimizdeki Yıl alanını,   Boyut  alanına ise örneğimizdeki Toplam alanını tıklayarak ilişkilendirme yapıyoruz.  Yukarıdaki haritada kullanılan değerler, Power BI üzerindeki düşey menüden ulaşılan Veriler sekmesinde aşağıdaki gibidir.  Yukarıdaki Veriler sekmesinde görünen Satışlar tablosu, aşağıdaki Excel dosyadan yüklenmiştir.  Excel dosyadan yükleme yapmak için,   Power BI programının açılışındaki veya Giriş  sekmesindeki Excel  düğmesine basmamız,   ilgili Excel dosyamızı seçmemiz,   ardından gör...

Daha önce Excel'de fonksiyon oluşturma konularını işlemiştim. Bir veya daha fazla parametre değişkenine dışarıdan değer alıp, fonksiyon bünyesinde işlemler yapılıp, amaçlanan değişkene değer üretilip bu değer fonksiyonu çağırana bir çıktı olarak döndürülmekteydi.  Power BI üzerinde fonksiyon oluşturmak için önce Giriş sekmesindeki Veri Dönüştürme düğmesine bağlı seçeneklerden Veri Dönüştürme  seçeneğine tıklayarak Power Query  ekranına geçiyoruz.  Açılan ekranda sol düşey menüde bir tablo ismi üzerinde farenin sağ düğmesine basıyoruz ve İşlev Oluştur düğmesine basıyoruz.  Fonksiyon Oluşturma 1 Açılan ekranda fonksiyon oluşturma satırında gelen tüm kodlamayı silin ve örnek olarak bizim daha önceki örneklerimizdeki fonksiyona birebir olan şu kodu yazın:  = (KarneNotu as number) => let Kaynak = if KarneNotu >= 50 then "Geçti" else "Kaldı" in Kaynak Üstteki menüde bulunan Gelişmiş Düzenleyici  düğmesine basarsanız yazdığınız kodu aşağıdaki gibi bi...

Power BI'da yeni sütun eklemek için ilgili tabloyu seçin, üst menüdeki Yeni Sütun düğmesini tıklayın. Açılan formül giriş alanında eşittir işaretinin sağ tarafına formülü yazın, eşitin sol tarafına ise yeni sütunun adını yazın.  Aşağıdaki örnekte bir başka tabloya Marka tablosuna bağlantı yapılıyor. Bu bağlantı tablolar arasındaki ilişki tarafından sağlanmaktadır.