DOKUMANISTAN

Excelde Verileri Birleştirme Data Consolide

Örneğin şirketinizdeki 3 ayrı ilin aylara göre Kira, Personel ve Akaryakıt bilgilerini değerlerini istediğinizi varsayalım. Her il çalışanı bilgileri aşağıdaki tablodaki gibi bilgileri iletti. Şimdi siz mesela Temmuz Ayının Kirası, Mart Ayının Personeli gibi bilgileri toplamını görmeniz ve ona göre işlem yapmanız gerekiyor. En acemi yolu her kesişen alanı elle tek tek toplamak.  Tabi bu işlemi DÜŞEYARA formülü ile veya Özet Tablo kullanarak ta sonuç alabilirsiniz. Ancak excelde Verileri Birleştirme Data Consolide işlemi bu iş için tasarlanmış en hızlı yöntemdir.

Örneğimizden yola çıkacak olursak;

Veri Birleştir Aylık Gider Tablosu

Veri Birleştir Toplam Giderler

İstanbul, Ankara ve İzmir ilindeki verileri TOPLAM GİDERLER tablosuna kesişen alanların toplamını yazdırmak istiyoruz.

Veri Birleştir (Data Consolide) Formülü Özellikleri

Veri Sekmesine Geliyoruz ve Birleştir seçtiğimizde

Excel Veri Birleştir Daha Consolide Veri Birleştir

aşağıdaki ekran gelecektir. Bu alanı incelediğimizde ise;

Excel Veri Birleştir Daha Consolide Özellikleri

İşlev :
Hücreler birleştirildiğinde nasıl bir hesaplama yapacağını belirleyen alandır. Toplam, Say, Ortalama vb. işlemleri bu alandan belirlenir.

Başvuru :
Bu alanı seçerek ekranın arkasında olan verilerde seçim yapmanızı sağlar.

Ekle :
Başvuru Kısmından seçtikten sonra Ekle butonuna basarız. Seçilen Veri Tüm Başvurular kısmına gelir.

Sil :
Tüm Başvurulardan önceden seçtiğimiz alanları da seçip sil yaparsak birleştirecek veriyi silmiş oluruz.

Tüm Başvurular :
Birleştirilecek alanların hücre adresi bu alanda listelenecektir.

Etiket Yeri :
Bu alan işin püf noktasıdır. Burada belirleyeceğimiz sol ve üst satırlara göre Birleştir işlemi çalışacaktır.
Diğer bir ifade ile birleştireceğiniz verilerin üst ve sol satırları olmalı ki birleştirme işlemi başarılı sonuçlansın.

Kaynak veriye Bağlantı Oluştur :
Eğer bu alan seçilmezse verileri olduğu gibi işlemini yapıp verileri ekler. Bu alan seçili olursa AltToplamdaki gibi her satırda + işareti çıkar ve verilerin nereden geldiği kaynağını gösterir.

Verileri Birleştirme (Data Consolide) Formülü Nasıl Kullanılır

Verileri incelediğimizde Kira, Personel ve Akaryakıt kolonları, Satırlarda ise Aylar var. Şimdi Veri Birleştir Tüm illerdeki örneğin Haziran Ayının Kirasını birleştirebilecek mi?

Bunun için boş tablo olan TOPLAM GİDERLER en sol üstteki hücresinde konumlanalım. Veri Sekmesinden – Birleştir Seçiyoruz.

Excel Veri Birleştir Daha Consolide Veri Birleştir

Birleştir Ekranın detayı karşımıza gelecektir.

Excel Veri Birleştir Daha Consolide Özellikleri

Başvuru alanına tıklayarak her tabloyu Sol ve Üst başlıklarına seçtiğimizden emin olarak seçip

Excel Veri Birleştir Daha Consolide Seçim Yapma

Ekle butonuna basıyoruz. Bu işlemi 3 il içinde aynı işlemi yapıyoruz. Seçim bittiğinde Birleştir ekranı aşağıdaki şekilde olmalıdır.

Excel Veri Birleştir Daha Consolide Özellikleri Ekle

Tamam dediğimizde Toplam giderler alanında veriler aşağıdaki şekilde dolacaktır.

Excel Veri Birleştir Daha Consolide Aylık Toplam Giderler

Excel Veri Birleştir Daha Consolide Aylık Giderler Kontrol Tablosu

Tablomuz doğru çalıştı mı? Kontrol edelim. Örneğin Mayıs Ayının Kira Bedelleri  İstanbul : 250.370 + Ankara : 390.883 + İzmir : 613.161 değerlerini görüyoruz. Bu değerleri topladığımızda 1.254.364 değeri elde ediyoruz. Toplam Giderler alanında da görüldüğü gibi Mayıs kira bedeli Toplamı aynı olduğu görülmektedir.

Excel Veri Birleştir Daha Consolide Aylık Toplam Giderler

Karmaşık Sıralamada Veri Birleştirme (Data Consolide) İşlemi

Diğer örneğimizde Sol Başlık ve Üst Başlıklar aynı sıralamada idi. Ancak tabloyu dolduran kişi farklı sıralamış olabilir. Karşınıza aynı örneğin karmaşık hali aşağıdaki gibi çıktığında ne yapacağız.

Verileri şöyle bir göz atarsak Mayıs Ayı Kira nerede olduğunu işaretledim. Her veri farklı bir satır ve sütunda olduğunu görüyoruz. Aynı işlemi tekrar yaparsak çalışır mı? Deneyelim!.

Yukarıda anlattığımız gibi Veri Sekmesinden –  Birleştir’i seçiyoruz. Seçimleri yapıyoruz. Burada tablonun Sol ve Üstteki Aylar ve Kira Akaryakıt ve Personel başlıklarını seçmeyi unutmuyoruz. Burası Önemliydi.

Excel Veri Birleştir Daha Consolide Etiket Yeri

Bunun yanında da Birleştir penceresinde Etiket Yeri olan Üst Satır ve Sol sütun seçili olduğundan emin oluyoruz ve seçimleri yaptıktan sonra Tamam diyoruz.  Aşağıdaki şekilde yine toplamı doğru şekilde yaptığını göreceksiniz.

Toplam Giderler satırının en sol üst satırında gidelim ve yukarıda anlattığım işlemleri aynen tekrar uygulayalım.

Rakamları topladığımıza 1.254.364 rakamının toplamı verdiğini görmüş oluyoruz.

Excel Veri Birleştir Daha Consolide Aylık Toplam Giderler

Excel Çalışma Sayfalarını Birleştirme ( Data Consolide ) İşlemi

İlk iki örnekte ay bazlı olduğunda 12 satırlık ve 3 sütunluk verileri birleştirmiştik. Ancak böyle bir dünya yok. Bazı iller örneğin Temmuz ayında iletecek bilgisi yoksa yazmayabilir. Bu durumda Verileri Birleştirme (Data Consolide) gene de çalışacak mı? Evet!  Birden çok çalışma sayfasındaki verileri tek bir çalışma sayfasında birleştirme Veri Birleştirme ile yapabiliriz.

Elimizde 3 ayrı çalışma sayfası (veya ayrı sayfalarda olabilir) Bölgelere ait aşağıdaki verilerin olduğunu düşünelim.

Excel Veri Birleştir Daha Consolide Çalışma Sayfası 1

Excel Veri Birleştir Daha Consolide Çalışma Sayfası 2

Excel Veri Birleştir Daha Consolide Çalışma Sayfası 3

Dikkat ederseniz her bölgenin tablosunda İçecek isimlerinde bir düzen yok. Bazı tabloda 4 tane bazı tabloda 5 ve aynı sıralama da değil. İşte bu örneğimiz biraz daha gerçekçi oldu.

Bu verilerdeki birleştirmeleri TOPLAM sayfamıza gelip boş hücreye gelip aynı işlemi bu sefer Başvuru alanında iken EGE, AKDENIZ ve MARMARA çalışma sayfalarından şekildeki gibi Sol ve üst başlıklarıyla seçelim. Her sayfa seçim yapıp diğer sayfaya geçtiğimizde dikkat ederseniz önceki seçim kadar alanı otomatik seçiyor. Eğer satır fazla veya eksikse bu seçimi göz ardı edip, yeniden seçim yapmalısınız. Etiket Yerindeki Üst satır ve Sol Satır kısmını seçmeyi unutmadan Tamam diyoruz.

Excel Veri Birleştir Daha Consolide Çalışma Sayfası Veri Birleştir Seçim

Aynı işlemi farklı sayfalarda olmasına rağmen topladığını görmüş oluyoruz.

Excel Veri Birleştir Daha Consolide Çalışma Sayfası Toplam Sonucu

Test etmek için Her sayfada olan Çamlıca içeceğinin Ocak ayındaki değerleri alalım.

Ege Bölgesi : 7.540 + Akdeniz Bölgesi : 2.520 + Marmara Bölgesi : 7.540 = Toplam: 17.600 ediyor.

Veri birleştir ile ilgili anlatabileceklerim şimdilik bu kadar.

Excel Dosya Download Makalede kullandığımız çalışma kitabını buradan indirebilirsiniz.

Sonraki Konu

“Excelde Verileri Birleştirme Data Consolide” üzerine 11 yorum

  1. Merhaba
    B1 hücresi =8 olsun ; AB8 ifadesini AB & B1 şeklinde VBA da yapabiliyorken benzer bir ifade yazımını hücre içine yazdığımız formülde nasıl ifade edebiliriz. Yardımcı olursanız sevinirim.

    Yanıtla
    • Merhaba;
      =HÜCRE(“adres”;B1) formülü ile 8 değeri yazan hücrenin konumunu yani B1 değerini alırsın.
      Her ikisini birlikte görmek istersen de =HÜCRE(“adres”;B1)&B1 eklersin.
      Tam yapılmak isteneni anlamadığım için sorunuza göre cevap vermeye çalıştım.
      Selamlar.

      Yanıtla
  2. merhabalar,
    farklı günlerde satılmış olan elma , armut, kiraz ve bu üçünün fiyatları var diyelim,
    ikinci bir sayfada veri birleştirmeyi adım adım uyguluyorum.sonuçta;
    çift yazılanların tek olarak birleştirilmiş yazımı .tamam
    aynı olanların farklı tutarları veya adetleri birleşimi toplamı da. tamam
    mevcut lar üzerinde rakamsal oynamalar yapınca veri toplamı değişiyor. tamam
    sorum şu;
    alt satıra yeni bir ürün ve fiyatı eklediğimde toplam veri birleştirme sonucuna ürünü ve fiyatı eklemiyor.(kaynak veriye bağlantı oluştur seçeneğini işaretliyorum.)ne yapmalıyım.
    kolay gelsin.

    Yanıtla
    • Merhaba;
      Rahatsızlığımdan dolayı bakamadım. Kusura Bakmayın.
      Kaynak Veriye bağlantı oluştur seçerseniz verilerde güncellenecektir. Ancak Üst Satır veya Üst Sütunda bir genişleme varsa veri birleştir’deki alanı manuel genişletmeniz gerekir.

      Eğer bu işlem işinizi uzatacaksa size Pivot Table ile Tablo Nesnesi kullanmanızı öneririm. Böylelikle örneğin tablonun altına veri girdikçe alanı yeniden belirlemeden otomatik genişleyecek. Pivot Table ile de gruplanmış toplamları rahatlıkla rapor haline getirebilirsiniz.
      Selamlar.

      Yanıtla
  3. Merhaba benim balık buyutme ile ilgili bazı tablolarım var
    Farklı menşeiler oluca balık buyumeleri fark edior
    x menşei olunca 1 . tablodan çeksin veriyi
    y menşei olunca 2. tablodan çeksin veriyi gibisinden

    Yanıtla
    • Veriyi görmem lazım. Ama x meşei ve y menşei durumları varsa tavsiyem iki tabloyu birleştirin ve bir kolon daha açıp x ve menşei’lerini tekrarlı şekilde ekleyin. Daha sonra ister formülle ister PivotTable ile bazı bilgiler alabilirsiniz.

      Yanıtla
  4. Merhaba,
    Benim ayni kolondaki ayni kodlarin yanlarinda yer alan yorumlari, tek bir hucrede nasil toplayabilirim?
    Ornek olarak A1’de xyz, A2’de xyz, A3’de xyz olsun. B1’de abc, B2’de cba, B2’de bca olsun. Vlookup ile xyz koduna denk gelen veriyi almak istiyorum ancak rast gele birinci satirdakini aliyor. Sonucta tam olarak sunu gormeye ihtiyacim var: C2’de xyz, D2’de abc,cba,bca (bir arada olacak sekilde). Bu formulde abc verileri sayi olunca yapiliyor ama sayidan farkli olunca birlestirme islemini nasil yapalirim?
    Yardimci olursaniz cok sevinirim.
    Simdiden tesekkurler

    Yanıtla
    • Sorunuzu inceledim ama tam anlayamadım. DÜŞEYARA ile bir önceki değeri ve altındaki değeri istediğinizi anladım. Ama mantık olarak DÜŞEYARA ile yapılamaz bir işlem.
      Ne amaç için kullanmak istediğinizi anlatabilirseniz bekli oradan bi şeyler yakalayabilirim.

      Yanıtla

Yorum yapın