Excel'de Dağınık Verileri Toplamanın Etkili Yolları

Excel'de farklı hücrelerden veya tablolardan verileri hızlıca toplamak ve birleştirmek için metinler için TEXTJOIN işlevini (2016 ve üzeri sürümler) veya eski sürümler için & operatörünü kullanın. Diğer sayfalardaki değerleri aramak için XLOOKUP veya INDEX+MATCH kombinasyonunu uygulayın. Büyük veri kümelerini otomatik olarak birleştirmeniz gerekiyorsa, en iyi araç Power Query eklentisidir. Bu yöntemler, bilgileri aktarırken manuel kopyalamayı ve hataları önlemenizi sağlar.

Metin Birleştirme: Basit "&"den Akıllı TEXTJOIN'e

En sık karşılaşılan görev, birkaç sütundaki Ad Soyad, adres veya yorumu tek bir hücrede toplamaktır.

Herhangi bir Excel sürümü için ampersand (&) operatörü uygundur. Yapıştırıcı gibi çalışır: =A1 & " " & B1 A1 hücresinde "İvan", B1 hücresinde "Petrov" yazıyorsa, sonuç "İvan Petrov" olacaktır. Kelimelerin bitişmemesi için tırnak içindeki boşluk gereklidir.

Modern sürümlerde (Excel 2016, 2019, 365) güçlü TEXTJOIN işlevi bulunmaktadır. Bunun en büyük avantajı, boş hücreleri yok sayabilmesi ve ayırıcıyı otomatik olarak yerleştirebilmesidir.

Formül şu şekilde görünür: =TEXTJOIN("; "; DOĞRU; A1:A10)

• İlk bağımsız değişken ("; ") — ayırıcı (noktalı virgül ve boşluk).
• İkinci bağımsız değişken (DOĞRU) — boş hücreleri atla.
• Üçüncü bağımsız değişken — veri aralığı.

Diğer Sayfalardan Veri Arama ve Çekme

Veriler farklı tablolar veya sayfalar arasında dağıldığında, bunları yapıştırmak yerine bir anahtara göre bulmanız gerekir (örneğin, ürün koduna veya müşteri kimliğine göre).

Klasik VLOOKUP (DÜŞEYARA) birçok kişi tarafından bilinir, ancak sınırlamaları vardır: yalnızca soldan sağa arama yapar ve kaynak tabloya yeni bir sütun eklerseniz bozulur.

Çok daha güvenilir olan INDEX + MATCH (İNDEKS + KAÇINCI) çiftini kullanmaktır: =INDEX(Sayfa2!B:B; KAÇINCI(A2; Sayfa2!A:A; 0)) Bu kombinasyon, A2'deki değeri ikinci sayfanın A sütununda arar ve B sütunundaki karşılık gelen değeri döndürür. Her yönde çalışır ve tablo yapısındaki değişikliklere karşı dayanıklıdır.

Microsoft 365 aboneliği olanlara veya Excel 2021+ kullanıcılarına eski işlevleri unutup XLOOKUP kullanmaları önerilir: =XLOOKUP(A2; Sayfa2!A:A; Sayfa2!B:B; "Bulunamadı") Yazımı daha basittir, daha hızlı çalışır ve #YOK hatası yerine değer bulunamadığında bir metin belirlemenize olanak tanır.

Power Query ile Büyük Hacimli Verilerin Otomasyonu

Düzenli olarak onlarca tabloyu (örneğin, her ay için raporları) birleştirmeniz veya iki büyük listeyi ortak bir alana göre eşleştirmeniz gerekiyorsa, formüller dosyayı yavaşlatabilir. Burada Power Query ("Veri" menüsü → "Veri Al") yardımınıza koşar.

İki tabloyu birleştirme algoritması:

1. Aralıklarınızı "Akıllı Tablo"ya dönüştürün (verileri seçin ve Ctrl+T tuşlarına basın).
2. "Veri" sekmesinde her tablo için "Tablo/Aralıktan" seçeneğini belirleyin. Sorgu düzenleyicisi açılacaktır.
3. "Sorguları Birleştir" (Merge Queries) düğmesine tıklayın.
4. Eşleştirilecek her iki tablodaki anahtar sütunu seçin (örneğin, "Ürün Kodu").
5. Görünen sütunu genişleterek eklemek istediğiniz alanları seçin.
6. "Kapat ve Yükle" düğmesine tıklayın.

Sonuç yeni bir sayfada görünecektir. En büyük avantaj: kaynak veriler değiştiğinde, "Yenile" düğmesine basmanız yeterlidir; pivot tablo kendiliğinden yeniden oluşturulur.

Veri Toplama: Pivot Tablolar ve 3D Başvurular

Bazen "birleştirmek", farklı sayfalardaki aynı göstergeleri sadece toplamak anlamına gelir.

3D başvurular, aynı yapıya sahip birden fazla sayfanız varsa çalışır (örneğin, "Ocak", "Şubat", "Mart"): =TOPLA(Ocak:Mart!B2) Bu formül, Ocak ve Mart dahil olmak üzere aralarındaki tüm sayfalardaki B2 hücresinin değerini toplayacaktır.

Daha karmaşık analizler için Pivot Tablolar kullanmak daha iyidir. Bunlar ("Veri Modeli" aracılığıyla) birden fazla aralıktaki verileri birleştirebilir; satırların müşteriler, değerlerin ise farklı kaynaklardan gelen satış toplamları olduğu raporlar oluşturmanıza olanak tanır.

Verilerle Çalışırken Sık Yapılan Hatalar

• Metin formüllerinde #DEĞER! hatası. Tarih veya sayıyı önceden biçimlendirmeden metinle birleştirmeye çalıştığınızda ortaya çıkar. Tarihi okunabilir bir forma getirmek için METNEÇEVİR() işlevini kullanın: =A1 & " tarihli " & METNEÇEVİR(B1; "GG.AA.YYYY").
• Fazladan boşluklar. Farklı kaynaklardan gelen veriler genellikle gizli boşluklar içerir ("İvan " ve "İvan"). KIRP (TRIM) işlevi, birleştirmeden önce metni temizleyecektir.
• Dosya yavaşlaması. Tüm bir sütun için binlerce DÜŞEYARA formülü (örneğin, A:A) işlemciyi ciddi şekilde yükler. Aralığı her zaman belirli satırlarla sınırlayın (A2:A1000) veya Akıllı Tablolar kullanın.

Sıkça Sorulan Sorular

Tablolardaki anahtarlar hatalı yazılmışsa veriler nasıl birleştirilir? Mükemmel bir otomatik yöntem yoktur. Önce verileri temizleyin: her şeyi KÜÇÜKHARF() işleviyle aynı harf durumuna getirin, KIRP() ile fazladan boşlukları silin. Excel 365'te benzer kelimeler için karmaşık arama yapan metin işlevleri vardır, ancak birleştirmeden önce yazım hatalarını manuel olarak düzeltmek çoğu zaman daha kolaydır.

Farklı Excel dosyalarından veriler birleştirilebilir mi? Evet. Formüllerde bu, köşeli parantez içinde dosya yolunu belirterek yapılır: =[Ocak_Raporu.xlsx]Sayfa1!$A$1. Ancak Power Query kullanmak daha güvenilirdir: burada "Dosyadan" → "Çalışma Kitabından" seçeneğini belirleyebilirsiniz ve sistem, dosya ortak bir klasörde olsa bile verileri kendiliğinden çekecektir.

Birleştirmeden sonra biçimlendirme kaybolursa ne yapılmalı? Formüller yalnızca değerleri döndürür. Renk veya yazı tipini korumanız gerekiyorsa, bunu manuel olarak veya makrolar (VBA) aracılığıyla yapmanız gerekir. Power Query de hücre biçimlendirmesi olmadan yalnızca "ham" verileri aktarır.