Geçenlerde bir okuyucudan aynı çalışma kitabındaki birden çok çalışma sayfasını tek bir çalışma sayfasında birleştirme hakkında bir soru aldım.
Farklı sayfaları birleştirmek için Power Query'yi kullanmasını istedim, ancak daha sonra Power Query'de yeni olan biri için bunu yapmanın zor olabileceğini fark ettim.
Bu nedenle, bu öğreticiyi yazmaya ve Power Query kullanarak birden çok sayfayı tek bir tabloda birleştirmenin tam adımlarını göstermeye karar verdim.
Power Query kullanarak birden çok sayfadan/tablodan verilerin nasıl birleştirileceğini gösterdiğim bir videonun altında:
Aşağıda, birden fazla sayfanın nasıl birleştirileceğine ilişkin yazılı talimatlar bulunmaktadır (video yerine yazılı metni tercih etmeniz durumunda).
Not: Power Query, Excel 2010 ve 2013'te bir eklenti olarak kullanılabilir ve Excel 2016'dan itibaren yerleşik bir özelliktir. Sürümünüze bağlı olarak bazı resimler farklı görünebilir (bu eğitimde kullanılan resim yakalamaları Excel 2016'dan alınmıştır).
Power Query Kullanarak Birden Çok Çalışma Sayfasındaki Verileri Birleştirin
Power Query kullanarak farklı sayfalardaki verileri birleştirirken, verilerin bir Excel Tablosunda (veya en azından adlandırılmış aralıklarda) olması gerekir. Veriler bir Excel Tablosunda değilse, burada gösterilen yöntem işe yaramaz.
Dört farklı sayfanız olduğunu varsayalım - Doğu, Batı, Kuzey ve Güney.
Bu çalışma sayfalarının her biri bir Excel Tablosundaki verilere sahiptir ve tablonun yapısı tutarlıdır (yani başlıklar aynıdır).
Verileri indirmek ve takip etmek için buraya tıklayın.
Bu tür verilerin Power Query (Excel Tablosundaki verilerle gerçekten iyi çalışır) kullanılarak birleştirilmesi son derece kolaydır.
Bu tekniğin en iyi şekilde çalışması için, Excel Tablolarınız için adlara sahip olmak daha iyidir (onsuz da çalışır, ancak tablolar adlandırıldığında kullanımı daha kolaydır).
Tablolara aşağıdaki isimleri verdim: East_Data, West_Data, North_Data ve South_Data.
Power Query kullanarak birden çok çalışma sayfasını Excel Tablolarıyla birleştirme adımları şunlardır:
- Veri sekmesine gidin.
- Verileri Al ve Dönüştür grubunda, 'Veri Al' seçeneğini tıklayın.
- 'Diğer Kaynaklardan' seçeneğine gidin.
- 'Boş Sorgu' seçeneğini tıklayın. Bu, Power Query düzenleyicisini açacaktır.
- Sorgu düzenleyicide, formül çubuğuna aşağıdaki formülü yazın: =Excel.MevcutÇalışma Kitabı(). Power Query formüllerinin büyük/küçük harf duyarlı olduğunu unutmayın, bu nedenle tam formülü belirtildiği gibi kullanmanız gerekir (aksi takdirde bir hata alırsınız).
- Enter tuşuna basın. Bu size tüm çalışma kitabındaki tüm tablo adlarını gösterecektir (çalışma kitabında olması durumunda size adlandırılmış aralıkları ve/veya bağlantıları da gösterecektir).
- [İsteğe Bağlı Adım] Bu örnekte, tüm tabloları birleştirmek istiyorum. Yalnızca belirli Excel Tablolarını birleştirmek istiyorsanız, ad başlığındaki açılır simgeye tıklayıp birleştirmek istediklerinizi seçebilirsiniz. Benzer şekilde, aralıkları veya bağlantıları adlandırdıysanız ve yalnızca tabloları birleştirmek istiyorsanız, adlandırılmış aralıkları da kaldırabilirsiniz.
- İçerik başlığı hücresinde, çift uçlu oku tıklayın.
- Birleştirmek istediğiniz sütunları seçin. Tüm sütunları birleştirmek istiyorsanız, (Tüm Sütunları Seç) işaretli olduğundan emin olun.
- 'Önek olarak orijinal sütun adını kullan' seçeneğinin işaretini kaldırın.
- Tamam'ı tıklayın.
Yukarıdaki adımlar, tüm çalışma sayfalarındaki verileri tek bir tabloda birleştirir.
Yakından bakarsanız, son sütunun (en sağdaki) Excel tablolarının (East_Data, West_Data, North_Data ve South_Data) adını taşıdığını göreceksiniz. Bu, hangi kaydın hangi Excel Tablosundan geldiğini söyleyen bir tanımlayıcıdır. Excel tabloları için açıklayıcı adlara sahip olmanın daha iyi olduğunu söylememin nedeni de budur.
Power Query'nin kendisinde birleştirilmiş verilerde yapabileceğiniz birkaç değişiklik:
- Ad sütununu sürükleyip başa yerleştirin.
- Ad sütunundan “_Data”yı kaldırın (böylece ad sütununda Doğu, Batı, Kuzey ve Güney kalır). Bunu yapmak için, Ad başlığına sağ tıklayın ve Değerleri Değiştir'e tıklayın. Değerleri Değiştir iletişim kutusunda, _Data'yı bir boşlukla değiştirin.
- Veri sütununu yalnızca tarihleri gösterecek şekilde değiştirin (saati değil). Bunu yapmak için Tarih sütun başlığını tıklayın, "Dönüştür" sekmesine gidin ve Veri türünü Tarih olarak değiştirin.
- Sorguyu ConsolidatedData olarak yeniden adlandırın.
Artık Power Query'deki tüm çalışma sayfalarından birleştirilmiş verilere sahip olduğunuza göre, bunları yeni bir çalışma sayfasında yeni bir tablo olarak Excel'e yükleyebilirsiniz.
Bunu yapmak için. aşağıdaki adımları izleyin:
- 'Dosya' sekmesini tıklayın.
- Kapat ve Yükle'ye tıklayın.
- Verileri İçe Aktar iletişim kutusunda Tablo ve Yeni çalışma sayfası seçeneklerini seçin.
- Tamam'ı tıklayın.
Yukarıdaki adımlar, tüm çalışma sayfalarındaki verileri birleştirir ve bu birleştirilmiş verileri size yeni bir çalışma sayfasında verir.
Bu Yöntemi Kullanırken Çözmeniz Gereken Bir Sorun
Çalışma kitabındaki tüm tabloları birleştirmek için yukarıdaki yöntemi kullandıysanız, bir sorunla karşılaşmanız olasıdır.
Birleştirilmiş verilerin satır sayısına bakın - 1304 (doğru olan).
Şimdi sorguyu yenilersem satır sayısı 2607 olarak değişir. Tekrar yenileyin ve 3910 olarak değişecektir.
İşte sorun.
Sorguyu her yenilediğinizde, orijinal verilerdeki tüm kayıtları birleştirilmiş verilere ekler.
Not: Bu sorunla yalnızca birleştirmek için Power Query kullandıysanız karşılaşacaksınız. TÜM EXEL TABLOLARI çalışma kitabında. Birleştirilecek belirli tabloları seçtiyseniz, bu sorunla karşılaşmazsınız.Bu sorunun nedenini ve nasıl düzeltileceğini anlayalım.
Bir sorguyu yenilediğinizde geri döner ve verileri birleştirmek için attığımız tüm adımları izler.
Formülü kullandığımız adımda =Excel.CurrentWorkbook(), bize tüm tabloların bir listesini verdi. Sadece dört tablo olduğu için bu ilk kez iyi çalıştı.
Ancak, yenilediğinizde, çalışma kitabında beş tablo bulunur - buna Power Query'nin birleştirilmiş verilere sahip olduğumuz yere eklediği yeni tablo da dahildir.
Yani sorguyu her yenilediğinizde, birleştirmek istediğimiz dört Excel Tablosu dışında, mevcut sorgu tablosunu da sonuçtaki verilere ekler.
Buna özyineleme denir.
İşte bu sorunun nasıl çözüleceği.
Power Query formül çubuğuna =Excel.CurrentWorkbook() öğesini ekleyip enter tuşuna bastığınızda, Excel Tablolarının bir listesini alırsınız. Tabloları yalnızca çalışma sayfasından birleştirebildiğinizden emin olmak için, yalnızca birleştirmek istediğiniz tabloları bir şekilde filtrelemeniz ve diğer her şeyi kaldırmanız gerekir.
Yalnızca gerekli tablolara sahip olduğunuzdan emin olmak için gereken adımlar şunlardır:
- Açılır menüyü tıklayın ve imleci Metin Filtreleri üzerine getirin.
- İçerir seçeneğine tıklayın.
- Satırları Filtrele iletişim kutusunda, "içerir" seçeneğinin yanındaki alana _Data yazın.
- Tamam'ı tıklayın.
Verilerde herhangi bir değişiklik göremeyebilirsiniz, ancak bunu yapmak, sorgu yenilendiğinde ortaya çıkan tablonun tekrar eklenmesini önleyecektir.
Yukarıdaki adımlarda kullandığımızı unutmayın “_Veri” tabloları bu şekilde adlandırdığımız gibi filtrelemek için. Peki ya tablolarınız tutarlı bir şekilde adlandırılmamışsa. Ya tüm tablo adları rastgeleyse ve ortak hiçbir yanı yoksa.
Bunu çözmenin yolu şudur - 'eşit değildir' filtresini kullanın ve Sorgunun adını girin (örneğimizde ConsolidatedData olacaktır). Bu, her şeyin aynı kalmasını ve oluşturulan sorgu tablosunun filtrelenmesini sağlayacaktır.
Power Query'nin farklı sayfalardaki (hatta aynı sayfadaki) verileri birleştirme sürecinin tamamını oldukça kolay hale getirmesinin yanı sıra, onu kullanmanın bir başka avantajı da onu dinamik hale getirmesidir. Tablolardan herhangi birine daha fazla kayıt ekler ve Power Query'yi yenilerseniz, size otomatik olarak birleştirilmiş verileri verir.Önemli Not: Bu öğreticide kullanılan örnekte başlıklar aynıydı. Başlıkların farklı olması durumunda, Power Query yeni tablodaki tüm sütunları birleştirir ve oluşturur. Bu sütun için veriler mevcutsa, gösterilecek, aksi takdirde boş gösterecektir.
Aşağıdaki Power Query Eğitimlerini de Beğenebilirsiniz:
- Excel'de Birden Çok Çalışma Kitabındaki Verileri Birleştirin (Power Query kullanarak).
- Power Query (Al ve Dönüştür olarak da bilinir) kullanarak Excel'de Verilerin Özeti Nasıl Açılır?
- Klasörlerden ve Alt Klasörlerden Dosya Adlarının Listesini Alın (Power Query kullanarak)
- Power Query Kullanarak Excel'de Tabloları Birleştirme.
- İki Excel Sayfası/Dosyası Nasıl Karşılaştırılır
