1. Formülle Özet Tabloyu Ters Çevirme

Bazen Excel’e dışarıdan aktardığımız veriler özet tablo formatında gelebilir. Özet tablolar veri görselliği açısından güzel olsa da veri analizi söz konusu olduğunda bazı sorunlara yol açar. Bu yazımda formül kullanarak bir özet tabloyu nasıl dikey tablo haline getirebileceğimizi anlatacağım.

Özet tablo formatı nedir diye merak ediyorsanız aşağıdaki resimde bu tarz tabloların nasıl gözüktüğüne bakalabilirsiniz. Bu formatı Excel’in kendi Özet Tablo işlevi ile karıştırmayın lütfen. Burada verilerin dizilişinden bahsediyorum.

Biz bu tabloyu formüller kullanarak aşağıdaki gibi dikey bir tablo biçimine dönüştüreceğiz:

Bu uygulama esnasında kullanacağım formüller ve tabii ki yöntem farklı Excel sürümlerinde birbirinden farklı olacak. Özellikle Excel 2022/365 eski sürümlere oranla daha esnek özellikler ve daha güncel işlevler içerdiğinden eski sürümlerde bu işlveler ve özellikler çalışmıyor. Yazı esnasında yeri geldikçe bu farklardan bahsedeceğim.

Yazıda kullandığım dosyayı buradan indirebilirsiniz.

2. Uygulama

Ben uygulamayı yeni bir sayfada yapacağım. O yüzden dosyada yeni bir sayfa oluşturup adına da DePivot adını veriyorum.

a. Açıklama

Özet tabloyu dikey tabloya çevirmek için kullanacağımız işlev İNDİS işlevi. Burada istediğimiz veriyi çekmek için özel satır ve sütun değerlerine ihtiyacım var.

Burada yapmak istediğim satırlarda yer alan her bir başlığı, sütunlarda yer alan başlık adedi kadar kendini tekrar etmesini sağlamak. Yani örnek olarak “Yeni Kullanıcı Oluşturma” başlığı toplam sütun adedi olan 27 adet satırda kendini tekrar etmesi lazım. 27 satırdan sonra ise bir sonraki başlığa geçmesi gerek. Yani İNDİS formülünde kullanacağım satır değeri 1’den başlayacak, 27 satır boyunca 1 değerine sahip olacak 28. satırda 2 değerini alacak ve bu şekilde toplam 16 değerine ve toplam satır sayısına ulaşana kadar devam edecek.

Sütundaki değerleri kaydırmak için de her satırda bir yan hücreye kayması gerek. Ancak bu kayma toplam sütun başlık sayısına kadar olmalı. Yani bana her satırda 1’den başlayıp 27’ye ulaşacak bir seri lazım. Ve 27 rakamı elde ettiğimde satırda bir alt başlığa geçtiğim için rakamın tekrar 1’e den başlaması gerek.

b. Satırlarda ve Sütunlardaki Başlık Adedi ve Toplam Satır Sayısını Bulma

İlk yapmamız gereken husus satırlarda yer alan toplam kaç tane başlık olduğunu bulmak. Bunun için kullanacağım işlev BAĞ_DEĞ_DOLU_SAY. Veriler 2022 sayfasında C:C sütununda yer aldığından DePivot Sayfamda B2 hücresine aşağıdaki formülü yazıyorum:

=BAĞ_DEĞ_DOLU_SAY(‘2022’!C:C)

Formül bize 16 değerini döndürecek

Şimdi de sütunlardaki toplam başlık sayısını yine aynı yöntemle bulacağım ve B3 hücresine bu değeri yazdıracağım. Sütun değerleri 3. Satırdan alacağım:

=BAĞ_DEĞ_DOLU_SAY(‘2022’!3:3)

Burada da formül sonucunda 22 değerini elde edeceğiz.

Şimdi bize lazım olan toplamda kaç tane satıra ihtiyacımız olacağı. Bunu da iki değerin çarpımı ile elde edeceğiz. Ben B4 hücresine iki değeri çarpıyorum ve 352 değerini elde edeceğim. Elimde aşağıdaki resimde gözüken değerlerden oluşan bir mini liste oluşacak.

c. Satır Numaralarını Bulma

Açıklama değerler satır sayısına bağlı olduğundan bana öncelikle 1’den toplam satır sayısına kadar giden bir ana rakam serisi lazım. Bunu yapabileceğimiz formül Excel 365/2022 için SIRALA işlevi, toplam satır sayım B4 hücresinde olduğundan kullanacağım formül:

=SIRALA(B4)

Bu formülü C2 hücresine yazdığınızda C433’ü hücreye kadar tüm hücrelere 1’den başlayarak artan bir rakam serisinin otomatik olarak yazıldığını göreceksiniz. Yeni Excel sürümlerinin en güzel özelliği bu.

Daha eski Excel sürümleri için daha fazla el ile müdahale gerekiyor. Burada kullanacağım formül bir EĞER formülü. Sıralı sayı elde etmek için de SATIRSAY işlevini kullanacağım. Burada yapmak istediğim toplam satır numarasına erişene kadar formülün yazıldığı ilk hücre olan C2 sabit kalarak formülün yer aldığı hücreler arasındaki toplam hücre sayısını yazmak:

=EĞER(SATIRSAY($C$2:$C2)<=$B$4; SATIRSAY($C$2:$C2);””)

Bu formülü yazarak el ile C600 hücresine kadar uzatın.

Excel 365’te tüm rakamlar otomatik olarak gelirken eski Excel sürümlerinde bunu elle yapıyor olduğumuza dikkat edin. Bunun bir sonucu da özet tablodaki toplam satır sayısı değiştiğinde formülü uyguladığınız aralığı da buna uygun olarak elle arttırmanız gerek.

Tablom aşağıdaki resimdeki gibi genişlemiş olacak:

d. Çekeceğimiz Verilerin Konumlarını Bulma

İlk olarak her bir satırda, çekmek istediğim verinin yer aldığı satırı bulacağım. Bu da alt alta 27 satırda 1 rakamı, sonraki 22 satırda 2 rakamı olacak şekilde her 22 satırda bir artan bir rakam serisi. Bunun için ilk aşamada bulduğum numarayı 22’ye böler ve bulunan rakamı en yakın üst tam sayıya yuvarlayabilirim. Ben D2 hücresine aşağıdaki formülü yazıyorum:

=YUKARIYUVARLA(C2#/$B$3;0)

Bu formülde C2 adresinin yanındaki # işeratene dikkat. Bu C2’de yazdığım dinamik dizi formülünün geri döndürdüğü tüm aralığa bu işlemi uygula anlamına geliyor. Bu sayede elle bir müdahaleye gerek kalmadan tüm değerler otomatik olarak karşımıza geliyor.

Eski Excel sürümleri için aynı formülü # işareti olmadan yazmalı ve en az 360. satıra kadar el ile doldurmanız gerek. Hata değerleri görmek istemiyorsanız formülü EĞERHATA işlevini kullanarak aşağıdaki gibi güncellemenizde fayda var:

=EĞERHATA(YUKARIYUVARLA(C2/$B$3;0);””)

Bundan sonra sütun kaydırma serisini elde etmem lazım. Burada MOD işlevi işimi görecek gibi duruyor. Hemen E3 satırına aşağıdaki formülü yazalım:

=MOD(C2#;$B$3)

Bunu yazdığımızda 1-22 arası değerleri elde edeceğiz ancak bir sonraki seri 0’dan başlayacak. Bunu aşmak için B3 değerine ufak bir rakam ekleyelim:

=MOD(C2#;$B$3+0,0000000001)

Bu formül ile elde edeceğimiz sıra 1-22, 0,9999999999-26,9999999999 gibi küsüratlı bir seri elde edeceğim. Bunu da YUKARIYUVARLA işlevi ile birleştirelim:

=YUKARIYUVARLA(MOD(C2#;$B$3+0,0000000001);0)

Bu formülü yazdığımızda istemiş olduğumu 1-27 arası rakamlardan oluşan serimizi elde ediyor olacağız. Excel 2019 ve daha önceki sürümü kullananlar yine C2 ifadesinin yanındaki # işaretini silerek 600. satıra kadar çoğaltabilir ve/veya EĞERHATA işlevi ile birleştirebilir. Bu aşamada elimdeki tablo aşağıdaki gibi oluşacak

e. Değerleri Çekerek Dikey Tabloyu Oluşturma

İlk çekeceğim değer satır başlıkları. Satır başlıkları C4:C19 arası yer alıyor. Burada INDİS formülünü kullanacağım:

=İNDİS(‘2022’!$C$4:$C$19;D2#;)

Bu formülü F2 hücresine yazdığımda F2 hücresinden itibaren tüm satır başlıkları toplamda her birinden 27 adet olacak şekilde alt alta yazılacaktır.

İkinci olarak tarihleri çekelim. Tarih değerleri C2:AD2 hücreleri arasında yer alıyor:

=İNDİS(‘2022’!$D$2:$AD$2;;E2#)

Son olarak ta değerleri çekelim. Değerler C4:AD19 hücrelerine dağılmış durumda ve fakat biz her bir satırda çekmek istediğimiz değerin satır ve sütun numaraları zaten bulmuştuk:

=İNDİS(‘2022′!$D$4:$AD$19;’DePivot Formül ile’!D2#;’DePivot Formül ile’!E2#)

Artık verileri analiz edebileceğimiz, grafiğe dökebileceğimiz dikey tablomuz hazır. Eski Excel sürümlerini kullanan arkadaşlar yukarıdaki formüllerdeki # işaretini kaldırarak dilerlerse EĞERHATA işlevi ile birleştirebilirler. Formülleri 600. satıra kadar uzatmayı da sakın unutmayın.

İlginizi Çekebilecek Diğer Yazılar
Etiketler: , , , , , , ,

Leave a Reply