Power Query ile farklı Excel dosyalarından tabloları birleştirme

Sorunun formülasyonu

Çoğu Excel kullanıcısının er ya da geç karşılaştığı çok standart durumlardan biri için güzel bir çözüme bakalım: çok sayıda dosyadan verileri hızlı ve otomatik olarak tek bir nihai tabloda toplamanız gerekir. 

Şube şehirlerden veriler içeren birkaç dosya içeren aşağıdaki klasörümüz olduğunu varsayalım:

Dosya sayısı önemli değildir ve gelecekte değişebilir. Her dosyanın adında bir sayfa vardır. Satışveri tablosunun bulunduğu yer:

Tablolardaki satır (sıra) sayısı elbette farklıdır, ancak sütun kümesi her yerde standarttır.

Görev: Şehir dosyalarını veya tablolardaki satırları eklerken veya silerken sonraki otomatik güncelleme ile tüm dosyalardan verileri tek bir kitapta toplamak. Nihai konsolide tabloya göre, herhangi bir rapor, pivot tablo, filtre-sıralama verisi vb. Oluşturmak mümkün olacaktır. Asıl mesele toplayabilmektir.

Silahları seçiyoruz

Çözüm için, Excel 2016'nın en son sürümüne (gerekli işlevler varsayılan olarak zaten yerleşiktir) veya ücretsiz eklenti yüklü olan Excel 2010-2013'ün önceki sürümlerine ihtiyacımız var. Güç Sorgu Microsoft'tan (buradan indirin). Power Query, verileri dış dünyadan Excel'e yüklemek, ardından ayıklamak ve işlemek için süper esnek ve süper güçlü bir araçtır. Power Query, metin dosyalarından SQL'e ve hatta Facebook'a kadar neredeyse tüm mevcut veri kaynaklarını destekler 🙂

Excel 2013 veya 2016'nız yoksa, daha fazla okuyamazsınız (şaka yapıyorum). Excel'in eski sürümlerinde, böyle bir görev yalnızca Visual Basic'te (yeni başlayanlar için çok zor olan) bir makro programlanarak veya monoton manuel kopyalamayla (uzun zaman alır ve hatalara neden olur) gerçekleştirilebilir.

Adım 1. Bir dosyayı örnek olarak içe aktarın

İlk olarak, Excel'in "fikri alması" için örnek olarak bir çalışma kitabından veri aktaralım. Bunu yapmak için yeni bir boş çalışma kitabı oluşturun ve…

• Excel 2016'nız varsa, sekmeyi açın Veri ve sonra Sorgu Oluştur – Dosyadan – Kitaptan (Veri — Yeni Sorgu- Dosyadan — Excel'den)
• Power Query eklentisinin yüklü olduğu Excel 2010-2013'e sahipseniz sekmeyi açın Güç Sorgu ve üzerinde seçin Dosyadan – Kitaptan (Dosyadan — Excel'den)

Ardından, açılan pencerede, raporların bulunduğu klasörümüze gidin ve şehir dosyalarından herhangi birini seçin (hangisi olduğu önemli değil, çünkü hepsi tipiktir). Birkaç saniye sonra, sol tarafta ihtiyacımız olan sayfayı (Satış) seçmeniz gereken Gezgin penceresi görünmelidir ve içeriği sağ tarafta görüntülenecektir:

Bu pencerenin sağ alt köşesindeki düğmeye tıklarsanız İndir (Yük), ardından tablo hemen orijinal biçiminde sayfaya aktarılacaktır. Tek bir dosya için bu iyi, ancak bu tür birçok dosya yüklememiz gerekiyor, bu yüzden biraz farklı gideceğiz ve düğmeyi tıklayacağız. Düzeltme (Edit). Bundan sonra, Power Query sorgu düzenleyicisi, kitaptaki verilerimizle ayrı bir pencerede görüntülenmelidir:

Bu, tabloyu ihtiyacımız olan görünüme “bitirmenize” izin veren çok güçlü bir araçtır. Tüm işlevlerinin yüzeysel bir açıklaması bile yaklaşık yüz sayfa sürer, ancak çok kısa bir süre için bu pencereyi kullanarak şunları yapabilirsiniz:

• gereksiz verileri, boş satırları, hatalı satırları filtreleyin
• verileri bir veya daha fazla sütuna göre sıralama
• tekrardan kurtulmak
• yapışkan metni sütunlara bölün (sınırlayıcılara, karakter sayısına vb. göre)
• metni sıraya koyun (fazladan boşlukları kaldırın, büyük/küçük harf düzeltme vb.)
• veri türlerini mümkün olan her şekilde dönüştürün (metin gibi sayıları normal sayılara çevirin veya tam tersi)
• tabloları dönüştürün (döndürün) ve iki boyutlu çapraz tabloları düz olanlara genişletin
• tabloya ek sütunlar ekleyin ve Power Query'de yerleşik M dilini kullanarak bu sütunlardaki formülleri ve işlevleri kullanın.
• ...

Örneğin, daha sonra pivot tablo raporları oluşturmak daha kolay olacak şekilde tablomuza ayın metin adını içeren bir sütun ekleyelim. Bunu yapmak için sütun başlığına sağ tıklayın tarihve komutu seçin Yinelenen sütun (Yinelenen Sütun)ve ardından görünen yinelenen sütunun başlığına sağ tıklayın ve Komutlar'ı seçin. Dönüştür – Ay – Ay Adı:

Her satır için ayın metin isimleri ile yeni bir sütun oluşturulmalıdır. Bir sütun başlığına çift tıklayarak, onu yeniden adlandırabilirsiniz. Tarihi Kopyala daha rahata Ay, Örneğin.

Bazı sütunlarda program veri türünü tam olarak tanımıyorsa, her sütunun sol tarafındaki biçim simgesine tıklayarak ona yardımcı olabilirsiniz:

Basit bir filtre kullanarak hatalı veya boş satırların yanı sıra gereksiz yöneticiler veya müşteriler içeren satırları hariç tutabilirsiniz:

Ayrıca, gerçekleştirilen tüm dönüşümler, her zaman geri alınabilecekleri (çapraz) veya parametrelerini (dişli) değiştirebilecekleri sağ panelde sabitlenir:

Hafif ve zarif değil mi?

Adım 2. İsteğimizi bir fonksiyona dönüştürelim

İçe aktarılan her kitap için yapılan tüm veri dönüşümlerini daha sonra tekrarlamak için, oluşturulan isteğimizi sırayla tüm dosyalarımıza uygulanacak bir işleve dönüştürmemiz gerekir. Bunu yapmak aslında çok basittir.

Sorgu Düzenleyicide, Görünüm sekmesine gidin ve düğmesine tıklayın. Gelişmiş Düzenleyici (Görünüm — Gelişmiş Düzenleyici). Önceki tüm eylemlerimizin M dilinde kod şeklinde yazılacağı bir pencere açılmalıdır. Örnek için içe aktardığımız dosyanın yolunun kodda sabit kodlanmış olduğunu lütfen unutmayın:

Şimdi birkaç ayar yapalım:

Anlamları basit: ilk satır (dosya yolu)=> prosedürümüzü argümanlı bir fonksiyona dönüştürür dosya yolu, ve aşağıda bu değişkenin değerine giden sabit yolu değiştiriyoruz. 

Herşey. Tıklamak Bitiş ve şunu görmeli:

Verilerin kaybolmasından korkmayın – aslında, her şey yolunda, her şey böyle görünmeli 🙂 Özel bir işlevi başarıyla oluşturduk, burada veri alma ve işleme algoritmasının tamamının belirli bir dosyaya bağlı olmadan hatırlanması . Daha anlaşılır bir isim vermek için kalır (örneğin veri almak) alanında sağdaki panelde İsim ve biçebilirsin Ana Sayfa — Kapatın ve indirin (Ana Sayfa — Kapat ve Yükle). Lütfen örnek için içe aktardığımız dosyanın yolunun kodda sabit kodlanmış olduğunu unutmayın. Ana Microsoft Excel penceresine döneceksiniz, ancak sağda fonksiyonumuzla oluşturulan bağlantıya sahip bir panel görünmelidir:

Adım 3. Tüm dosyaları toplama

En zor kısım geride kaldı, keyifli ve kolay kısım kaldı. sekmeye git Veri – Sorgu Oluştur – Dosyadan – Klasörden (Veri — Yeni Sorgu — Dosyadan — Klasörden) veya Excel 2010-2013'e sahipseniz, sekmeye benzer şekilde Güç Sorgu. Açılan pencerede tüm kaynak şehir dosyalarımızın bulunduğu klasörü belirtin ve tıklayın. OK. Bir sonraki adım, bu klasörde (ve alt klasörlerinde) bulunan tüm Excel dosyalarının ve her birinin ayrıntılarının listeleneceği bir pencere açmalıdır:

Tıkla değişim (Edit) ve yine tanıdık sorgu düzenleyici penceresine giriyoruz.

Şimdi, her dosyadan verileri “çekecek”, oluşturulan fonksiyonumuzla tablomuza başka bir sütun eklememiz gerekiyor. Bunu yapmak için sekmeye gidin Sütun Ekle – Özel Sütun (Sütun Ekle — Özel Sütun Ekle) ve görünen pencerede fonksiyonumuzu girin veri almak, bunun için her dosyanın tam yolunu bağımsız değişken olarak belirterek:

Tıkladıktan sonra OK oluşturulan sütun sağdaki tablomuza eklenmelidir.

Şimdi tüm gereksiz sütunları silelim (Excel'de olduğu gibi, farenin sağ düğmesini kullanarak - Kaldır), yalnızca eklenen sütunu ve dosya adını içeren sütunu bırakarak, çünkü bu adın (daha doğrusu şehir) her satır için toplam veride bulunması yararlı olacaktır.

Ve şimdi “vay anı” - fonksiyonumuzla eklenen sütunun sağ üst köşesinde kendi oklarıyla simgeye tıklayın:

… işareti kaldır Önek olarak orijinal sütun adını kullan (Önek olarak orijinal sütun adını kullanın)ve tıklayın OK. Ve fonksiyonumuz, kaydedilen algoritmayı takip ederek ve her şeyi ortak bir tabloda toplayarak her dosyadan verileri yükleyecek ve işleyecektir:

Tam bir güzellik için, dosya adlarıyla ilk sütundan .xlsx uzantılarını da kaldırabilirsiniz - standart olarak "hiçbir şey" ile değiştirme (sütun başlığına sağ tıklayın - Vekil) ve bu sütunu Şehir. Ayrıca sütundaki veri biçimini tarihle düzeltin.

Herşey! Tıklamak Ana Sayfa – Kapat ve Yükle (Ana Sayfa — Kapat ve Yükle). Tüm şehirler için sorgu tarafından toplanan tüm veriler, mevcut Excel sayfasına “akıllı tablo” formatında yüklenecektir:

Oluşturulan bağlantının ve montaj fonksiyonumuzun herhangi bir şekilde ayrı ayrı kaydedilmesi gerekmez - bunlar, geçerli dosya ile birlikte olağan şekilde kaydedilir.

Gelecekte, klasörde (şehir ekleme veya çıkarma) veya dosyalarda (satır sayısının değiştirilmesi) herhangi bir değişiklik olduğunda, doğrudan tabloya veya sağ paneldeki sorguya sağ tıklayıp seçim yapmak yeterli olacaktır. emretmek Güncelle ve Kaydet (Yenile) – Power Query, tüm verileri birkaç saniye içinde yeniden "yeniden oluşturur".

PS

Değişiklik. Ocak 2017 güncellemelerinden sonra Power Query, Excel çalışma kitaplarının kendi kendine nasıl toplanacağını öğrendi, yani artık ayrı bir işlev oluşturmaya gerek yok – bu otomatik olarak gerçekleşir. Böylece, bu makalenin ikinci adımına artık ihtiyaç duyulmaz ve tüm süreç gözle görülür şekilde daha basit hale gelir:

1. Klinik İstek Oluştur – Dosyadan – Klasörden – Klasörü Seç – Tamam
2. Dosya listesi göründükten sonra, düğmesine basın. değişim
3. Sorgu Düzenleyici penceresinde, İkili sütunu çift okla genişletin ve her dosyadan alınacak sayfa adını seçin

Ve hepsi bu! Şarkı!

• Çapraz tablonun, pivot tablolar oluşturmaya uygun düz bir şekilde yeniden tasarlanması
• Power View'da animasyonlu bir balon grafiği oluşturma
• Farklı Excel dosyalarından sayfaları bir araya getirmek için makro