Ö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;
İ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
aşağıdaki ekran gelecektir. Bu alanı incelediğimizde ise;
İş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.
Birleştir Ekranın detayı karşımıza gelecektir.
Başvuru alanına tıklayarak her tabloyu Sol ve Üst başlıklarına seçtiğimizden emin olarak seçip
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.
Tamam dediğimizde Toplam giderler alanında veriler aşağıdaki şekilde dolacaktır.
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.
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.
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 Ç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.
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.
Aynı işlemi farklı sayfalarda olmasına rağmen topladığını görmüş oluyoruz.
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.
Sonraki Konu
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.
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.
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.
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.
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
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.
Zahmetleriniz için çok teşekkür ederim. Makalenin işime oldukça yaradı.
Rica ederim. İşinize yaramasına sevindim. Selamlar
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
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.