İçerik
Sorunun formülasyonu
Girdi verileri olarak, sayfalardan birinin aşağıdaki biçimdeki satış verilerini içeren birkaç tablo içerdiği bir Excel dosyamız var:
Dikkat:
- Sıralama olmadan satır ve sütunlarda farklı boyutlarda ve farklı ürün ve bölge gruplarını içeren tablolar.
- Tablolar arasına boş satırlar eklenebilir.
- Tablo sayısı herhangi biri olabilir.
İki önemli varsayım. Şu varsayılmaktadır:
- Her tablonun üstünde, ilk sütunda, tablonun satışını gösterdiği yöneticinin adı (Ivanov, Petrov, Sidorov, vb.)
- Tüm tablolardaki mal ve bölge adları aynı şekilde - büyük/küçük harf doğruluğu ile yazılır.
Nihai hedef, tüm tablolardan verileri sonraki analizler ve bir özet oluşturmak için uygun olan tek bir düz normalleştirilmiş tabloda toplamaktır, yani şunda:
Adım 1. Dosyaya bağlanın
Yeni bir boş Excel dosyası oluşturalım ve sekmede seçelim Veri Komuta Veri Al – Dosyadan – Kitaptan (Veri — Dosyadan — Çalışma kitabından). Satış verileriyle kaynak dosyanın konumunu belirtin ve ardından gezgin penceresinde ihtiyacımız olan sayfayı seçin ve düğmesine tıklayın. Verileri Dönüştür (Verileri Dönüştür):
Sonuç olarak, ondan gelen tüm veriler Power Query düzenleyicisine yüklenmelidir:
2. Adım. Çöpü temizleyin
Otomatik olarak oluşturulan adımları sil değiştirilmiş tip (Değiştirilen Tür) и Yükseltilmiş başlıklar (Tanıtılan Başlıklar) ve bir filtre kullanarak boş satırlardan ve toplamları olan satırlardan kurtulun boş и TOPLAM ilk sütun tarafından. Sonuç olarak, aşağıdaki resmi elde ederiz:
Adım 3. Yönetici ekleme
Daha sonra kimin satışlarının nerede olduğunu anlamak için tablomuza her satırda karşılık gelen bir soyadı olacak bir sütun eklemek gerekir. Bunun için:
1. Komutu kullanarak satır numaralarına sahip bir yardımcı sütun ekleyelim Sütun Ekle – Dizin Sütunu – 0'dan (Sütun ekle — Dizin sütunu — 0'dan itibaren).
2. Komutla formül içeren bir sütun ekleyin Sütun Ekleme – Özel Sütun (Sütun ekle — Özel sütun) ve orada aşağıdaki yapıyı tanıtın:
Bu formülün mantığı basittir – ilk sütundaki bir sonraki hücrenin değeri “Ürün” ise, bu, yeni bir tablonun başlangıcına rastladığımız anlamına gelir, bu nedenle önceki hücrenin değerini yöneticinin adı. Aksi takdirde, hiçbir şey göstermeyiz, yani boş.
Soyadlı ana hücreyi almak için önce önceki adımdaki tabloya başvururuz. #”Dizin eklendi”ve sonra ihtiyacımız olan sütunun adını belirtin [Sütun1] köşeli parantez içinde ve o sütundaki hücre numarası kıvrık parantez içinde. Hücre numarası, sütundan aldığımız mevcut numaradan bir eksik olacaktır. indeks, Sırasıyla.
3. Boş hücreleri doldurmak için kalır boş komutuyla daha yüksek hücrelerden isimler Dönüştür – Doldur – Aşağı (Dönüştür — Doldur — Aşağı) ve ilk sütunda soyadları olan dizinleri ve satırları olan artık gerekli olmayan sütunu silin. Sonuç olarak şunları elde ederiz:
Adım 4. Yöneticiler tarafından ayrı tablolarda gruplandırma
Sonraki adım, her yönetici için satırları ayrı tablolarda gruplamaktır. Bunu yapmak için, Dönüştürme sekmesinde Gruplama ölçütü (Dönüştür – Gruplama Ölçütü) komutunu kullanın ve açılan pencerede Yönetici sütununu ve Tüm satırlar (Tüm satırlar) işlemini seçerek herhangi bir toplama işlevi uygulamadan veri toplamak için (toplam, ortalama, vb.). P.):
Sonuç olarak, her yönetici için ayrı tablolar elde ederiz:
Adım 5: İç İçe Tabloları Dönüştür
Şimdi ortaya çıkan sütunun her hücresinde bulunan tabloları veriyoruz. Bütün bilgiler terbiyeli formda.
İlk olarak, her tabloda artık gerekli olmayan bir sütunu silin müdür. tekrar kullanırız Özel sütun çıkıntı Dönüşüm (Dönüştür — Özel sütun) ve aşağıdaki formül:
Ardından, başka bir hesaplanmış sütunla, her tablodaki ilk satırı başlıklara yükseltiriz:
Ve son olarak, ana dönüşümü gerçekleştiriyoruz – her tabloyu M fonksiyonunu kullanarak açıyoruz. Table.UnpivotOtherColumns:
Başlıktaki bölgelerin adları yeni bir sütuna girecek ve daha dar ama aynı zamanda daha uzun normalleştirilmiş bir tablo elde edeceğiz. ile boş hücreler boş yok sayılır.
Gereksiz ara sütunlardan kurtulduğumuzda:
Adım 6 İç İçe Tabloları Genişletin
Sütun başlığındaki çift oklu düğmeyi kullanarak tüm normalleştirilmiş iç içe tabloları tek bir listeye genişletmek için kalır:
… ve sonunda istediğimizi elde ederiz:
Komutu kullanarak ortaya çıkan tabloyu Excel'e geri aktarabilirsiniz. Ana Sayfa — Kapat ve Yükle — Kapat ve Yükle… (Ana Sayfa — Kapat&Yükle — Kapat&Yükle…).
- Birden çok kitaptan farklı başlıklara sahip tablolar oluşturun
- Belirli bir klasördeki tüm dosyalardan veri toplama
- Kitabın tüm sayfalarından verileri tek bir tabloda toplama