İki Excel Sayfasındaki Verileri Birleştirme

Bir Pazartesi günü gelen kutunuza iki dosya düşer. Birinde geçen ayın siparişleri, diğerinde ise bu ayın siparişleri yer alır ve üst kattan biri öğle yemeğine kadar tek bir rapor hazırlanmasını ister. Siz de her ikisini de açar, kopyalayıp yapıştırmaya başlarsınız; ancak işin yarısına geldiğinizde sütunların birbiriyle aynı şekilde hizalanmadığını fark edersiniz. Tanıdık geldi mi?

İşte neredeyse hiç kimsenin baştan bahsetmediği kısım.“Excel’de iki sayfadaki verileri birleştirmek” ifadesinin ardında yatan iki tamamen farklı görev vardır ve yanlış olanı seçmek bir öğleden sonranızı boşa harcamaya neden olur. Bir görev, satırları üst üste istifler. Diğeri ise ortak bir kimlik numarası kullanarak sütunları yan yana eşleştirir. Doğru teşhisi koyarsanız, asıl iş birkaç dakika sürer.

Önemli olan şu ki, bu kılavuz önce amaca göre sınıflandırıyor, ardından her birine uygun aracı sunuyor. Tahminde bulunmaya gerek yok.

Excel’de İki Çalışma Sayfasındaki Verileri Birleştirme: Herkesin Yaptığı İlk Hata

Çoğu kılavuz, bir yöntem seçip o yöntemle devam eder. Altı paragraf boyunca VLOOKUP işlevini adım adım anlatırlar ya da Power Query’yi gösterirler ve arama işlemlerinden hiç bahsetmezler. Okuyucuların yarısı yanlış sayfaya ulaşır ve geldiklerinden daha da kafası karışık bir şekilde sayfadan ayrılır.

Asıl sorun araçta değil. Sorun, insanların verilerin ne durumda olduğunu hiç sorgulamadan manuel olarak kopyala-yapıştır yapmaları. Manuel yapıştırma işlemi ise gözden kaçması kolay hatalara yol açar: burada bir yerde fazladan boş satır, orada bir sütun bir yer kaymış ve birdenbire toplamda birkaç binlik bir sapma ortaya çıkar, kimse de nedenini bilemez.

O halde, her şeyden önce şu hızlı kontrolü yapın.

• İki sayfa, aynı sütunlar, farklı kayıtlar (Ocak siparişleri + Şubat siparişleri)? Satırları birleştirmeniz gerekiyor. Bu, Power Query ile yapılabilecek bir iş.
• İki sayfa, ortak bir kimlik sütunu, farklı ayrıntılar (birinde sipariş kimlikleri, diğerinde müşteri e-postaları) mı? Sütunları eşleştirmeniz gerekir. Bu, XLOOKUP veya INDEX/MATCH ile yapılabilecek bir iştir.

Bu tek soru, bundan sonra olacak her şeyi belirler. Yanlış cevap verirseniz, formül becerileriniz ne kadar iyi olursa olsun sizi kurtaramaz.

İlk Adım: Power Query ile Satır Ekleme

İki Excel sayfasını birbirinin altına istifleyerek birleştirmek istediğinizde, Power Query en temiz yoldur. Excel’e entegre edilmiştir (Excel 2016’dan itibaren Veri sekmesi) ve verileri yeniler; böylece gelecek ay yeni verileri eklediğinizde, her şeyi yeniden oluşturmak yerine tek bir düğmeye tıklamanız yeterlidir.

Microsoft’un kendi belgeleri, birden fazla sayfadaki verileri birleştirmenin temellerini ele alır, ancak ekleme akışı özellikle şu şekildedir.

Öncelikle her bir sayfayı uygun bir Tabloya dönüştürün. Verilerinizin herhangi bir yerine tıklayın, Ctrl+T tuşlarına basın ve başlıklar onay kutusunu işaretleyin. Bunu her iki sayfa için de yapın. Tablo Tasarımı altında, bunları ” tblJan ” ve ” tblFeb” gibi tanıyabileceğiniz bir şekilde adlandırın.

Ardından her birini Power Query’ye yükleyin. İmlecinizi ilk tablonun üzerine getirin ve Veri’ye gidin > Tablo/Aralık’tan. Düzenleyici açılır. Burada herhangi bir değişiklik yapmanıza gerek yoktur — sadece kapatın ve yalnızca bağlantı olarak yükleyin (Dosya > Kapat ve Şuraya Yükle > (Yalnızca Bağlantı Oluştur). İkinci tablo için de aynı işlemi tekrarlayın.

Şimdi asıl ekleme işlemi. Veri > Veri Al > Sorguları Birleştir > Ekle. İki sorgunuzu seçin ve Tamam’a tıklayın. Power Query, sütun başlıklarına göre eşleştirerek ikinci tablonun satırlarını ilk tablonun hemen altına yerleştirir.

Sonucu yeni bir sayfaya yükleyin. İşlem tamamdır. Power Query ile Excel’e veri ekleme yönteminin en güzel yanı ise yenileme özelliğidir. Bir dahaki sefere güncellenmiş dosyaları aldığınızda, yeni satırları kaynak tablolarınıza yapıştırın ve Veri > Tümünü Yenile seçeneğine tıklayın. Birleştirilmiş tablo kendiliğinden güncellenir.

“Ekleme işlemi formüllerle hiç ilgisi yok. Excel’e bu iki yığının aynı türden olduğunu belirtmek ve ardından her ay istediğiniz zaman bunları sizin için bir araya getirmesini sağlamakla ilgili.”

Birleştireceğiniz iki dosyadan fazla varsa, Trump Excel’in çalışma sayfalarını birleştirme kılavuzunda çok sayfalı versiyonun ayrıntılı bir açıklaması bulunmaktadır. Mantık aynıdır, sadece ekleme listesindeki sorgu sayısı daha fazladır.

İkinci Görev: XLOOKUP veya INDEX/MATCH ile Sütunları Eşleştirme

Bu görev, niteliği bakımından farklıdır. İki sayfanız da aynı kayıtları tanımlamaktadır, ancak bu kayıtlarla ilgili farklı sütunlar içermektedir. Birinci sayfada sipariş kimlikleri ve tutarlar yer almaktadır. İkinci sayfada ise aynı sipariş kimliklerinin yanı sıra müşterinin e-posta adresi ve bölgesi bulunmaktadır. Tüm bu bilgilerin yan yana yer aldığı tek bir tablo oluşturmak istiyorsunuz.

Bu, sayfalar arası VLOOKUP işlemi gerektiren bir durumdur; ancak VLOOKUP eski ve güvenilir olmayan bir yöntemdir. Microsoft 365 veya Excel 2021 kullananlar için XLOOKUP bu işlevin yerini almıştır ve bunun yerine XLOOKUP’u kullanmak daha mantıklıdır.

Diyelim ki ana tablonuz Sheet1’de yer alıyor ve sipariş kimlik numaraları A sütununda bulunuyor. Aradığınız e-posta adresi ise Sheet2’de yer alıyor; kimlik numaraları A sütununda, e-posta adresleri ise B sütununda bulunuyor. Sheet1’de, D2 hücresinde:

=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "Not found")

Bunun anlamı şudur: A2 hücresindeki kimliği al, Sheet2 sayfasındaki Kimlik sütununda ara ve eşleşen e-posta adresini döndür. Son argüman olan "Not found", eşleşme bulunmadığında görüntülenen değerdir; bu da VLOOKUP işlevinin sana bakan çirkin #N/A mesajından çok daha iyidir.

Bunu sütun boyunca aşağı sürükleyin; her satır, kendisiyle eşleşen öğeyi de beraberinde getirir. Çok temiz.

XLOOKUP işlevinin bulunmadığı eski bir Excel sürümünü mü kullanıyorsunuz? INDEX/MATCH işlevleri de aynı işi görür:

=INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0))

MATCH işlevindeki 0 parametresi tam eşleşmeyi zorunlu kılar; bu da kimlik numaraları söz konusu olduğunda neredeyse her zaman istediğiniz şeydir. Bu parametreyi atlarsanız Excel yaklaşık eşleştirme yapar ve sıralanmamış verilerde fark edilmeden yanlış değerler döndürür. Klasik bir sessiz hata.

Bilmeniz gereken bir nokta: Power Query de bu eşleştirme işini, “Append” yerine “Merge” işlevini kullanarak gerçekleştirebilir. Tek seferlik bir rapor için formül yazmak daha hızlıdır. Ancak her ay binlerce satır içeren bir raporu yeniden oluşturacaksanız, Power Query’deki “Merge” işlevi daha iyi ölçeklenir ve on bin adet XLOOKUP formülünün yaptığı gibi dosyanızı aşırı derecede yavaşlatmaz.

‘i Gerçekten Birleştirmek İstediğinizde

İnsanların genellikle unuttuğu, daha az bilinen üçüncü bir seçenek vardır. Amacınız verileri Excel tarzında birleştirmekse — yani her bir ham satırı olduğu gibi korumak yerine, toplamların veya ortalamaların sayfalar arasında toplanmasını istiyorsanız — Excel’de bunun için özel bir araç bulunmaktadır. Veri > Birleştir.

Her iki aralığı da seçin, istediğiniz işlevi (Toplam, Ortalama, Sayım) seçin; program, satır ve sütun etiketlerini eşleştirerek toplama işlemini gerçekleştirir. Spreadsheeto’nun “konsolide etme” adım adım kılavuzu, bu iletişim kutusunu ayrıntılı olarak ele almaktadır. Altta yatan ayrıntılara ihtiyacınız olmadığında, sadece toplamlara ihtiyacınız olduğunda doğru seçimdir; örneğin, bölgesel satış rakamlarını tek bir özet halinde birleştirmek gibi durumlar.

Ancak, aslında her satırın olduğu gibi kalması gerektiğinde “Consolidate” işlevini kullanmayın. Bu işlev verileri toplu hale getirir ve ayrıntı düzeyini kaybedersiniz. Bu, yanlış bir şekilde kullanılan bir ekleme işidir.

Biçerdöverinde Sorun Giderme

İşler tahmin edilebilir şekillerde ters gidebilir. İşte genellikle arızalanan parçalar ve çözümleri.

Bir ekleme işleminden sonra başlıkların tekrarlanması, neredeyse her zaman iki kaynak tablonuz arasındaki sütun adlarının uyuşmaması anlamına gelir. Geri dönün, sütun adlarını tam olarak eşleşecek şekilde yeniden adlandırın — boşluklar ve büyük harf kullanımı da dahil olmak üzere — ardından sayfayı yenileyin.

Var olduğunu bildiğiniz satırlarda arama sonuçları “ #N/A ” hatası mı veriyor? On vakadan dokuzunda bunun nedeni veri türü uyuşmazlığıdır. Bir sayfada kimlik numarası metin olarak, diğerinde ise sayı olarak saklanıyor. Ekranda aynı görünüyorlar ancak Excel, “1024” ile 1024’ü farklı değerler olarak değerlendirir. Her ikisinin de uyuşması için bir tarafı dönüştürün.

Birleştirilmiş tablonuza gizlice giren fazladan boş satırlar, genellikle orijinal veri aralığındaki ve birleştirme sırasında da dahil edilen boş satırlardan kaynaklanır. Birleştirme işleminden önce bunları kaynağında filtreleyerek ayıklayın.

Ayrıca, büyük bir arama işlemi sonrasında dosyanız yavaşlamaya başlarsa, bu Power Query’ye geçmeniz gerektiğinin bir işaretidir. Her düzenlemede yeniden hesaplanan binlerce değişken formül, oldukça iyi bir bilgisayarı bile çökertir. Birden fazla büyük çalışma kitabıyla uğraşıyorsanız, Ablebits’te daha karmaşık birleştirme senaryolarına ilişkin iyi bir genel bakış bulabilirsiniz.

Sık Sorulan Sorular

İki sayfayı birleştirmek için Power Query mi yoksa formül mü kullanmalıyım?
Bu, işin tek seferlik mi yoksa tekrarlanan bir işlem mi olduğuna bağlıdır. Bir daha asla dokunmayacağınız tek bir rapor için formül yazmak daha hızlıdır. Aylık olarak yeniden oluşturduğunuz her şey için ise Power Query daha avantajlıdır; çünkü her seferinde formülleri yeniden oluşturmak yerine tek bir tıklamayla güncellenir.

“Appending” ile “merging” arasındaki fark nedir?
“Appending“, her iki çalışma sayfasında da aynı tür kayıtlar bulunduğunda satırları birbirinin altına yığar. “Merging” (veya “looking up”), çalışma sayfalarında aynı kayıtlarla ilgili farklı ayrıntılar bulunduğunda, ortak bir kimlik kullanılarak sütunları yan yana eşleştirir. Bunlar tamamen farklı işlemlerdir.

Sayfaları birleştirmek için XLOOKUP, VLOOKUP’tan daha mı iyidir?
Evet, eğer elinizde varsa. XLOOKUP, yerleşik bir “bulunamadı” mesajını işler, sola veya sağa bakar ve birisi bir sütun eklediğinde hata vermez. VLOOKUP, eski dosyalar için uygundur ancak Excel 2021 veya 365’te yeni bir çalışma başlatırken bunu kullanmanın bir anlamı yoktur.

Veriler açıkça eşleşirken arama işlemim neden hata veriyor?
Neredeyse her zaman bir veri türü uyuşmazlığı söz konusudur. Bir sayfada anahtar metin olarak, diğerinde ise sayı olarak saklanmaktadır. Görünüşleri aynı olsa da Excel bunları eşit olarak değerlendirmemektedir. Her iki tarafta da biçimi standart hale getirdiğinizde eşleşme sorunları çözülür.

İki farklı çalışma kitabındaki sayfaları birleştirebilir miyim?
Evet. Power Query, harici dosyalara “Veri” > “Veri Al” > “Dosyadan” > “Çalışma Kitabından” seçenekleri aracılığıyla bağlanır. Her iki sorgu da yüklendikten sonra, “ekleme” ve “birleştirme” adımları tamamen aynı şekilde çalışır.

‘i Sonlandırma

: İki sayfayı birleştirmek, bunu tek bir sorun olarak görmeyi bıraktığınızda zor değildir. Önce şu soruyu sorun: aynı kayıtlar üst üste mi, yoksa aynı kimlikler eşleşiyor mu? İlk durum için Power Query’yi, ikinci durum için XLOOKUP veya INDEX/MATCH işlevlerini kullanın; yalnızca toplamlara ihtiyacınız varsa Consolidate işlevini kullanın. Excel’de iki sayfadaki verileri birleştirirken, önce durumu değerlendirin, sonra birleştirin. Bu sıra, size öğleden sonranızı kazandırır.