Birleştir

Birleştir (Consolidate), ayrı aralıklarda, sayfalarda ya da çalışma kitaplarında duran verileri tek bir yerde toplam, ortalama, en büyük değer gibi bir işlevle özetleyen Excel komutudur. Aynı yapıdaki aylık, şube bazlı ya da bölüm bazlı listeleri formül yazmadan tek tabloda toplamak için kullanılır. Veriler satır ve sütun etiketlerine ya da hücrelerin konumuna göre eşleştirilir.

Nerede bulunur​

Komut Veri > Veri Araçları > Birleştir (Data > Data Tools > Consolidate) yolunda yer alır. Klavye kısayolu Alt+A+N tuş dizisidir. Komutu çalıştırmadan önce sonucun yazılacağı hedef aralığın sol üst hücresini seçin; Birleştir penceresi bu hücreden başlayarak sonucu yazar.

VBA tarafında aynı işi Range.Consolidate yöntemi yapar. Bu yöntem Sources, Function, TopRow, LeftColumn ve CreateLinks bağımsız değişkenlerini alır.

Nasıl kullanılır​

Adımlar şöyledir:

• Hedefi seçin: Sonucun başlayacağı boş hücreyi seçin.
• Komutu açın: Veri > Veri Araçları > Birleştir yolunu izleyin.
• İşlevi seçin: İşlev (Function) listesinden Toplam (Sum), Say (Count), Ortalama (Average), Maks (Max), Min (Min), Çarpım (Product), Sayıları Say (Count Numbers) gibi bir işlev seçin. Standart sapma ve varyans işlevleri de listede bulunur.
• Başvuruları ekleyin: Başvuru (Reference) kutusuna imleci koyup ilk aralığı fare ile seçin, ardından Ekle (Add) düğmesine basın. Her kaynak aralık için bu işlemi tekrarlayın. Başka sayfadaki aralığı sayfa sekmesine geçerek, kapalı bir kitaptaki aralığı Gözat (Browse) ile seçebilirsiniz.
• Etiket seçeneklerini işaretleyin: Aralıkların ilk satırında başlık, ilk sütununda ad varsa Üst satır (Top row) ve Sol sütun (Left column) kutularını işaretleyin.
• Bağlantı isteğe bağlıdır: Kaynak verilere bağlantılar oluştur (Create links to source data) işaretlenirse sonuç, kaynak değişince güncellenir.
• Onaylayın: Tamam düğmesine basın.

Yanlış eklenen bir aralığı Tüm başvurular (All references) listesinden seçip Sil (Delete) ile kaldırabilirsiniz.

Konuma göre ve kategoriye göre birleştirme​

Birleştir iki biçimde çalışır:

• Konuma göre: Üst satır ve Sol sütun kutularının ikisi de boştur. Excel, her aralıktaki aynı göreli konumdaki hücreleri (ilk hücre ile ilk hücre, ikinci ile ikinci gibi) birleştirir. Aralıkların düzeni birebir aynı olmalıdır.
• Kategoriye göre: Etiket kutularından en az biri işaretlidir. Excel, satır ve sütun adlarını eşleştirir. Aralıklardaki sıra farklı olabilir ve bir ad yalnız bazı aralıklarda yer alabilir; bu durumda ad sonuca yine eklenir.

Çok sayıda aynı düzende sayfanız varsa 3-B başvuru da bir alternatiftir. Örneğin Ocak, Şubat ve Mart sayfalarındaki aynı hücreyi toplamak için:
Bu formül, aralarındaki tüm sayfaları kapsar; ancak etiketlere göre eşleştirme yapmaz.

Bağlantılı birleştirme ve sonucun yapısı​

Kaynak verilere bağlantılar oluştur işaretlenmezse sonuç, o andaki değerlerin sabit bir özetidir; kaynak değişirse sonuç kendiliğinden yenilenmez, komutu yeniden çalıştırmanız gerekir. Bağlantı oluşturulursa Excel sonuç alanına kaynak hücrelere başvuran formüller yazar ve satırları anahat (gruplama) düzeninde gösterir; düğmelerle ayrıntı satırları açılıp kapatılabilir. Kaynak aralıklar hedefle aynı sayfada olmamalıdır, bağlantılı birleştirmeyi farklı bir sayfaya kurmak en sağlıklı yoldur.

Etiketler kullanıldığında sonuçta sol üst hücre boş kalır, üst satır başlıkları ilk satıra, satır etiketleri ilk sütuna yazılır.

1. Kategoriye göre toplam​

Sayfanın solunda Ocak ayı (A:B), yanında Şubat ayı (C:D) ürün adetleri duruyor. Ürünler iki listede farklı sırada, bazıları yalnız birinde yer alıyor. Veri > Veri Araçları > Birleştir penceresinde işlev olarak Toplam, başvuru olarak A1:B6 ve C1:D7 aralıklarını ekleyip Üst satır ile Sol sütun kutularını işaretleyin, hedef olarak E1 hücresini seçin. Örnekteki makro bu adımları sizin yerinize uygular.
Sonuç E1 hücresinden başlayarak yazılır. E1 boş kalır, üst satır başlığı F1 hücresine (Adet) gelir. Ürün adları E sütununa, toplam adetler F sütununa yerleşir; ilk ürün Kalem, toplamı 220 olur. Yalnız tek listede bulunan ürünler de sonuçta yer alır. Sonuç sabit değerlerdir; kaynak veriyi değiştirirseniz komutu yeniden çalıştırın.

2. Konuma göre ortalama​

Üç şubenin (İstanbul, Ankara, İzmir) aylık giderleri B:D sütunlarında aynı sırada duruyor. Etiket kutularını işaretlemeden, Ortalama işlevi ve her şube için ayrı bir başvuru (B2:B6, C2:C6, D2:D6) ile F2 hücresine birleştirme yapıyoruz. Bu, konuma göre birleştirmedir: her aralığın ilk hücresi ilk hücreyle, ikincisi ikinciyle eşleşir. Karşılaştırma için E sütunundaki formül aynı ortalamayı hesaplar.
F2 hücresinden başlayan alana her gider kaleminin üç şubedeki ortalaması yazılır; kira için 21.000,00, elektrik için 3.633,33 değerini görürsünüz. E sütunundaki formül sonucu (21.000,00) ile Birleştir sonucu aynıdır. Fark şudur: Birleştir sonucu sabit değerdir, formül ise veri değişince kendiliğinden güncellenir. Bu örnekte hiç etiket kullanılmadığı için eşleştirme yalnızca hücre konumuna göre yapılmıştır.

3. Sol sütun etiketiyle en yüksek değer​

A:B sütunlarında Mart, C:D sütunlarında Nisan ayı personel satışları var; kişiler iki listede farklı sırada ve Elif yalnız Nisan listesinde. Başvuru aralıkları başlık satırını içermez (A2:B5 ve C2:D6). Sol sütun kutusu işaretli, Üst satır boş bırakılır ve işlev olarak Maks seçilir. Hedef E2 hücresidir; E1 ve F1 başlıklarını önceden elle yazdık.
Sonuç E2 hücresinden başlar: personel adları E sütununa, her kişinin iki ay içindeki en yüksek satışı F sütununa yazılır. İlk satırda Ayşe adlı kişinin değeri 45.000 olur. Adlar etiket olarak eşleştirildiği için listelerdeki sıra farkı sonucu bozmaz ve yalnız Nisan listesinde olan kişi de sonuca eklenir. Başlıklar E1 ve F1 hücrelerine elle yazıldığı için bu örnekte üst satır etiketi kullanılmamıştır.

Kullanırken dikkat edin​

• Etiketler birebir aynı olmalı: Kategoriye göre birleştirmede ad eşleştirmesi yazıma duyarlıdır; fazladan boşluk ya da yazım farkı olan adlar (örneğin "Defter" ve "Defter ") ayrı satır olarak sonuca yazılır. Birleştirmeden önce adları temizleyin.
• Ekle düğmesini unutmayın: Aralığı seçmek yeterli değildir; Ekle düğmesine basılmadan aralık Tüm başvurular listesine girmez ve sonuca katılmaz. Listede olmasını istemediğiniz aralığı Sil ile kaldırın.
• Sonuç varsayılan olarak sabittir: Bağlantı kutusu işaretlenmezse kaynak veriler değiştiğinde sonuç güncellenmez; komutu yeniden çalıştırmanız gerekir. Güncel kalmasını istiyorsanız Kaynak verilere bağlantılar oluştur seçeneğini kullanın. Bağlantılı sonuçta Excel anahat (gruplama) satırları ekler.
• Hedef alanın üzerine yazılır: Sonuç, hedef hücreden başlayarak yazılır ve altındaki dolu hücrelerin üzerine yazabilir. Hedef olarak boş bir alan ya da boş bir sayfa seçin.
• Sayısal olmayan değerler: Toplam, Ortalama gibi işlevlerde metin içeren hücreler hesaba katılmaz. Başlık satırı ya da toplam satırı aralığa girerse sonuç yanlış çıkabilir; aralığı yalnız veri (gerekirse başlıkla birlikte) olacak biçimde seçin.
• Alternatifler: Düzenli ve sürekli güncellenen raporlarda Özet Tablo (PivotTable), Power Query ile Sorguları Birleştir ya da formül tabanlı çözümler daha esnektir. Birleştir ise hızlı, tek seferlik özetler için pratiktir.
• İlgili konular: Veri > Veri Araçları grubundaki diğer komutlar, Özet Tablo, Alt Toplam, Gruplandır (Group), 3-B başvuru, TOPLA, ORTALAMA, MAK, MİN, EĞERSAY, ETOPLA, DÜŞEYARA ve VBA'da Range.Consolidate yöntemi.

Örneklerdeki tabloları indirilebilir Excel dosyasında kendiniz de deneyebilirsiniz.