Sayfaları Konsolide Etmek: Birleştir, 3B Başvuru ve Power Query

Birleştirme (Konsolide) ne zaman gerekir ve hangi yöntemler vardır?

Bölge, şube, ay ya da departman bazında ayrı sayfalarda tutulan verileri tek bir özet tabloda toplamak, Excel'de en sık karşılaşılan raporlama işlerinden biridir. Örneğin her şubenin aylık satışı kendi sayfasında duruyor ve yönetim şirket toplamını tek tabloda görmek istiyor. Bu işe konsolidasyon denir.

Birkaç farklı yöntem vardır ve doğru seçim verinin yapısına bağlıdır:

• Veri sekmesindeki Birleştir (Consolidate) komutu: Aynı yapıdaki ya da etiketle eşleşebilen aralıkları toplama, ortalama, sayma gibi işlemlerle tek tabloda birleştirir.
• 3B başvuru formülü: Aynı hücre adresinin birden çok sayfadaki değerini tek formülle toplar. Sayfalar aynı yerleşimde olmalıdır.
• Formül tabanlı özel hesap: Her sayfaya tek tek başvuran ya da dolaylı başvuru kullanan bir yapı. Esnektir ama bakımı zordur.
• Power Query ile ekleme (append): Sayfaları, dosyaları veya klasördeki tüm dosyaları satır satır alt alta ekler, sonra pivot ile özetler. Büyük ve değişken yapıda veride en sağlam yoldur.
• Pivot tablo ile çoklu aralık: Eski tarz bir yöntemdir; modern çözümde yerini Power Query almıştır.

Bu sayfa sayfalar arası özet toplama (konsolidasyon) işini ele alır. Birden fazla dosyanın satırlarını tek listede alt alta birleştirmek ayrı bir iştir ve ayrı bir sayfada anlatılır.

Birleştir komutuyla adım adım konsolidasyon

Önce kaynak sayfalarda verinin düzenli olduğundan emin olun: ilk satırda başlıklar, ilk sütunda etiketler (ürün, bölge) bulunmalı, birleşik hücre ve boş satır olmamalıdır.

• Sonucun yazılacağı yeni sayfada boş bir hücre seçin. Bu, sonuç tablosunun sol üst köşesi olur.
• Veri sekmesinde Veri Araçları grubundan Birleştir'i açın.
• İşlev kutusunda yapmak istediğiniz hesabı seçin; en yaygını Toplam'dır, ayrıca ortalama, sayı, en büyük, en küçük gibi seçenekler vardır.
• Başvuru kutusuna ilk kaynak aralığı girin (başlıkları dahil ederek). Ekle'ye basın. Aynı şekilde diğer sayfaların aralıklarını tek tek ekleyin. Başka bir çalışma kitabındaki aralık da eklenebilir; o dosyanın açık olması rahatlık sağlar.
• Etiketlerin nerede olduğunu belirtin: "Üst satır" ve "Sol sütun" kutularını işaretleyin. Böylece Excel aralıkları konuma göre değil etikete göre eşleştirir ve sayfalarda ürünlerin sırası farklı olsa bile doğru toplar.
• "Kaynak veriye bağlantılar oluştur" kutusunu işaretlerseniz sonuç statik değerler yerine kaynaklara bağlı olur; kaynaklar değişince özet güncellenir. İşaretlemezseniz değerler tek seferlik yazılır.

Sonuç tablosu, bağlantı seçtiyseniz gruplanmış (Outline) bir yapıda gelir ve soldaki artı işaretleriyle ayrıntı gösterilebilir. Etiketle eşleştirme sayesinde bir sayfada bulunmayan ürün de tabloya girer, o sayfa için değer sıfır sayılır.

Aynı yerleşimdeki sayfalar için 3B başvuru formülü

Her şube sayfası birebir aynı düzene sahipse (aynı hücrede aynı başlık, aynı hücrede toplam) en basit yol 3B başvurudur. Toplam fonksiyonunun içine, ilk sayfanın adından son sayfanın adına iki nokta ile bağlanan bir sayfa aralığı ve ardından ünlem işaretiyle hücre adresi yazılır. Bu formül, ilk ve son sayfa arasında kalan tüm sayfalardaki o hücreyi toplar. Araya sonradan yeni bir sayfa eklenirse (ilk ve son sayfa arasına), formül onu da kapsar.

Bu yöntemin avantajı basit ve şeffaf olmasıdır. Dezavantajı, sayfaların yerleşimi birebir aynı olmak zorunda olmasıdır; bir satır kaydığında yanlış hücre toplanır ve hata fark edilmeyebilir. Ayrıca sayfa sırası değişirse ya da ilk ve son sayfa taşınırsa kapsam değişir. Bu yüzden sayfa adlarını sabit tutan ve aralarında hiçbir şey olmayan "başlangıç" ve "bitiş" ayraç sayfaları kullanmak sık başvurulan bir önlemdir.

Aralık yerine tek tek sayfa adı yazan klasik formül de mümkündür: her sayfaya ayrı başvuru yapılır ve toplanır. Sayfa sayısı arttıkça formül uzar ve bakım zorlaşır.

Büyük ve değişken yapı için Power Query ile birleştirme

Sayfalar sık eklenip çıkıyorsa, yapıları tam aynı değilse ya da kaynaklar ayrı dosyalardaysa Power Query daha sağlamdır. Veri sekmesinde Veri Al ile ilgili çalışma kitabına bağlanın, her sayfayı sorgu olarak yükleyin ve Sorguları Birleştir altındaki Ekle komutuyla alt alta ekleyin. Her satırın hangi sayfadan geldiğini gösteren bir sütun eklemek özetlerde işinizi kolaylaştırır.

Bir klasördeki tüm dosyaları tek seferde almak da mümkündür. Veri Al, Dosyadan, Klasörden yolunu izleyip klasörü seçersiniz ve Power Query dosyaların içeriğini birleştirir. Yeni dosya klasöre eklenince yalnız Yenile demek yeterlidir. Böylece aylık gelen şube dosyaları her ay elle kopyalanmaz.

Sorguda sütun adlarını ve veri türlerini bir kez standartlaştırırsınız; sonrasında özet için pivot tablo kullanırsınız. Bu yaklaşım Excel'in Birleştir komutundan daha çok hazırlık gerektirir ama değişikliğe çok daha dayanıklıdır.

Sık yapılan hatalar

• Kaynak sayfalarda başlık adlarını farklı yazmak. "Ürün" ile "Ürün Adı" farklı etiket olur ve ayrı satıra düşer. Kaynakları önce standartlaştırın.
• Boşluk ya da büyük küçük harf farklarına dikkat etmemek. Sonunda boşluk olan etiket başka etiket sayılabilir.
• Birleştir komutunda aralığı yanlış seçip başlık satırını dışarıda bırakmak ya da toplam satırını içeri almak. Toplam satırı dahil edilirse sonuçlar iki kez sayılır.
• Statik sonuç üretip güncel sanmak. Bağlantı kutusu işaretlenmediyse özet kaynak veriyle senkron değildir.
• Bağlantılı dosyanın yerini değiştirmek. Başka bir çalışma kitabına bağlıysa, dosya taşınırsa bağlantı kopar.
• Farklı birimleri toplamak. Bir şube binlik birimle, diğeri tam birimle veri girmişse toplam anlamsızdır. Birimi sayfa başına belirtin.
• Metin olarak saklanan sayıları görmemek. Metin hücreler toplama girmez.
• Sonucu kaynakla doğrulamamak. Birkaç şubenin toplamını elle hesaplayıp özet tablosuyla karşılaştırın.

Konsolide raporlama Mindset programlarında nerede geçer?

Mindset'in "Excel ile Konsolide Raporlama" programında konsolidasyon kavramı ve mimarisi, veri alınması ve hafif ETL çözümleri, formül tabanlı konsolidasyon, çalışma kitapları arası veri bağlantısı, dinamik yönetim raporları ve denetim hazırlığı ve sunum modülleri yer alır. "Microsoft Excel ile Veri Analizi Eğitimi - İleri Excel (Uygulamalı)" programında ise veri bağlantıları ve dış veri aktarma ile ileri tablo yönetimi başlıkları bulunur.

Bu içerikler şube, bölge ya da iştirak verisini tek raporda toplayan finans, kontrol ve yönetim raporlama ekipleri için uygundur. Kurumsal ekiplerde konsolidasyon şablonunun ve etiket standardının ortak belirlenmesi, her dönem raporun aynı yapıda hazırlanmasını sağlar.