✅ Pivot Tablo için Kaynak Verileri Hazırlama

İçindekiler

Pivot Tablo için Kaynak veriler için iyi bir tasarım nedir?
Kaynak Verileri Hazırlarken Kaçınılması Gereken Yaygın Tuzaklar
Kötü Kaynak Veri Tasarımlarına Örnekler
[VAKA ÇALIŞMASI] Kötü Biçimlendirilmiş Verileri Özet Tabloya Hazır Kaynak Verilerine Dönüştürme

Verilerin doğru formatta olması, sağlam ve hatasız bir Pivot Tablo oluşturmak için çok önemli bir adımdır. Doğru şekilde yapılmazsa, pivot tablonuzla ilgili birçok sorun yaşayabilirsiniz.

Pivot Tablo için Kaynak veriler için iyi bir tasarım nedir?

Pivot Tablo için iyi bir kaynak veri örneğine bakalım.

İşte onu iyi bir kaynak veri tasarımı yapan şey:

İlk satır, sütunlardaki verileri açıklayan başlıkları içerir.
Her sütun benzersiz bir veri kategorisini temsil eder. Örneğin, Sütun C'de yalnızca ürün verileri ve yalnızca sütun D ve ay verileri bulunur.
Her satır, işlem veya satışın bir örneğini temsil edecek bir kayıttır.
Veri başlıkları benzersizdir ve veri kümesinin hiçbir yerinde tekrarlanmaz. Örneğin, bir yılın dört çeyreği için Satış numaranız varsa, bunların tümünü Satış olarak adlandırmamalısınız. Bunun yerine, bu sütun başlıklarına Sales Q1, Sales Q2 vb. gibi benzersiz adlar verin…
- Benzersiz başlıklarınız yoksa, yine de devam edip bir Pivot Tablo oluşturabilirsiniz ve Excel bunları bir son ek (Sales, Sales2, Sales3 gibi) ekleyerek otomatik olarak benzersiz yapar. Ancak bu, bir Pivot Tablo hazırlamanın ve kullanmanın korkunç bir yolu olurdu.

Kaynak Verileri Hazırlarken Kaçınılması Gereken Yaygın Tuzaklar

Kaynak verilerde boş sütunlar olmamalıdır. Bunu fark etmek kolaydır. Kaynak verilerde boş bir sütununuz varsa, Pivot Tablo oluşturamazsınız. Aşağıda gösterildiği gibi bir hata gösterecektir.
Kaynak verilerde boş hücreler/satırlar olmamalıdır. Boş hücrelere veya satırlara rağmen başarılı bir şekilde Özet Tablo oluşturabilirsiniz, ancak günün ilerleyen saatlerinde sizi ısırabilecek birçok yan etki vardır.
- Örneğin, satış sütununda boş bir hücreniz olduğunu varsayalım. Bu verileri kullanarak bir Pivot Tablo oluşturur ve satış alanını sütunlar alanına koyarsanız, size TOPLAM'ı değil COUNT'ı gösterir. Bunun nedeni, Excel'in tüm sütunu metin verilerine sahip olarak yorumlamasıdır (yalnızca tek bir boş hücre nedeniyle).
Kaynak verilerdeki hücrelere ilgili formatı uygulayın. Örneğin, tarihleriniz varsa (bunlar Excel'de arka uçta seri numaraları olarak saklanır), kabul edilebilir tarih biçimlerinden birini uygulayın. Bu, Özet Tabloyu oluşturmanıza ve verileri özetlemek, gruplandırmak ve sıralamak için ölçütlerden biri olarak Tarih'i kullanmanıza yardımcı olur.
- Birkaç saniyeniz varsa, bunu deneyin. Özet Tablonuzdaki tarihleri sayı olarak biçimlendirin ve ardından bu verileri kullanarak bir Özet tablo oluşturun. Şimdi Özet Tabloda tarih alanını seçin ve ne olduğunu görün. Otomatik olarak değerler alanına koyacaktır. Bunun nedeni, Pivot Tablonuzun bunların tarihler olduğunu bilmemesidir. Bunları sayı olarak yorumlar.
Kaynak verilerin bir parçası olarak Sütun Toplamları, Satır Toplamları, Ortalamalar vb. eklemeyin. Pivot Table'a sahip olduğunuzda, bunları daha sonra kolayca alabilirsiniz.
Her zaman bir Excel Tablosu oluşturun ve ardından bunu bir Pivot Tablo kaynağı olarak kullanın. Bu daha çok iyi bir uygulamadır ve bir tuzak değildir. Özet Tablonuz, Excel Tablosu olmayan bir kaynak verilerle de gayet iyi çalışır. Excel Tablosunun avantajı, genişleyen verileri ayarlayabilmesidir. Veri kümesine daha fazla satır eklerseniz, kaynak verileri tekrar tekrar ayarlamanız gerekmez. Özet Tabloyu yenileyebilirsiniz ve kaynak verilere eklenen yeni satırları otomatik olarak hesaba katar.

Kötü Kaynak Veri Tasarımlarına Örnekler

Kaynak veri tasarımlarının bazı kötü örneklerine bir göz atalım.

Kötü Kaynak Veri Tasarımı - Örnek 1

Bu, takip etmesi ve anlaması kolay olduğu için verileri korumanın yaygın bir yoludur. Bu veri düzenlemesiyle ilgili iki sorun vardır:

Tam resmi alamıyorsunuz. Örneğin, 1. Çeyrekte Mid West için satışların 2924300 olduğunu görebilirsiniz. Ama bu tek bir satış mı yoksa bir dizi satış mı? Her kaydı ayrı bir satırda bulundurursanız daha iyi bir analiz yapabilirsiniz.
Bunu kullanarak (yapabileceğiniz) bir Pivot Tablo oluşturursanız, farklı çeyrekler için farklı alanlar elde edersiniz. Aşağıda gösterildiği gibi bir şey:

Kötü Kaynak Veri Tasarımı - Örnek 2

Bu veri gösterimi, yönetim ve PowerPoint sunumlarının izleyicileri tarafından iyi karşılanabilir, ancak bir Özet Tablo oluşturmak için uygun değildir.

Yine, bu, bir Pivot Tablo kullanarak kolayca oluşturabileceğiniz türden bir özettir. Bu nedenle, sonunda verileriniz için böyle bir arama yapmak isteseniz bile, kaynak verileri Pivot'a hazır bir biçimde koruyun ve Pivot Tablosunu kullanarak bu görünümü oluşturun.

Kötü Kaynak Veri Tasarımı - Örnek 3

Bu yine bir Pivot Tablo kullanılarak kolayca elde edilebilecek bir çıktıdır. Ancak bir Pivot Tablo oluşturmak için kullanılamaz.

Veri setinde boş hücreler var ve çeyrekler sütun başlıkları olarak yayılıyor.

Ayrıca bölge en üstte belirtilirken, her kaydın bir parçası olması gerekir.

[VAKA ÇALIŞMASI] Kötü Biçimlendirilmiş Verileri Özet Tabloya Hazır Kaynak Verilerine Dönüştürme

Bazen, Özet Tablo için kaynak veri olarak kullanılmaya uygun olmayan bir veri kümesi alabilirsiniz. Böyle bir durumda, verileri Pivot dostu veri formatına dönüştürmekten başka seçeneğiniz olmayabilir.

İşte kötü veri tasarımına bir örnek:

Artık bu verileri Özet Tablo için kaynak veri olarak kullanılabilecek bir biçime dönüştürmek için Excel İşlevlerini veya Özet Sorgu'yu kullanabilirsiniz.

Bu yöntemlerin her ikisinin de nasıl çalıştığını görelim.

Yöntem 1: Excel Formüllerini Kullanma

Bu verileri Pivot Table hazır biçimine dönüştürmek için Excel İşlevlerini nasıl kullanacağımızı görelim.

Orijinal veri kümesindeki tüm kategoriler için benzersiz bir sütun başlığı oluşturun. Bu örnekte, Bölge, Çeyrek ve Satış olacaktır.
Bölge başlığının altındaki hücrede şu formülü kullanın: =INDEX($A$2:$A$5,ROUNDUP(ROWS($A$2:A2)/COUNTA($B$1:$E$1),0))
- Formülü aşağı sürükleyin ve tüm bölgeleri tekrarlayacaktır.
Çeyrek başlığının altındaki hücrede şu formülü kullanın: =INDEX($B$1:$E$1,ROUNDUP(MOD(ROWS($A$2:A2),COUNTA($B$1:$E$1)+0.1) ,0))
- Formülü aşağı sürükleyin ve tüm çeyrekleri tekrarlayacaktır.
Satışların altındaki başlıkta şu formülü kullanın: =INDEX($B$2:$E$5,MATCH(G2,$A$2:$A$5.0),MATCH(H2,$B$1:$E$1,0 ))
- Tüm değerleri almak için aşağı sürükleyin. Bu formül, arama değerleri olarak Bölge ve Çeyrek verilerini kullanır ve orijinal veri kümesinden satış değerini döndürür.

Artık bu sonuç verilerini Pivot Tablosu için kaynak veri olarak kullanabilirsiniz.

Örnek Dosyayı İndirmek İçin Tıklayınız.

Yöntem 2: Power Query'yi Kullanma

Power Query, bu tür verileri kolayca Pivot hazır veri formatına dönüştürebilen bir özelliğe sahiptir.

Excel 2016 kullanıyorsanız, Power Query özellikleri Al ve Dönüştür grubundaki Veri sekmesinde kullanılabilir. Excel 2013 veya önceki sürümleri kullanıyorsanız, eklenti olarak kullanabilirsiniz.

İşte Excel Kampüsü'nden Jon tarafından Power Query Kurulumu hakkında mükemmel bir kılavuz.

Yine, aşağıda gösterildiği gibi biçimlendirilmiş verilere sahip olduğunuzu göz önünde bulundurarak:

Kaynak verileri Pivot Table hazır biçimine dönüştürme adımları şunlardır:

Verileri bir Excel Tablosuna dönüştürün. Veri kümesini seçin ve Ekle -> Tablolar -> Tablo'ya gidin.
Tablo Ekle iletişim kutusunda, doğru aralığın seçildiğinden emin olun ve Tamam'ı tıklayın. Bu, tablo verilerini bir Excel Tablosuna dönüştürecektir.
Excel 2016'da Veri -> Al ve Dönüştür -> Tablodan seçeneğine gidin.
- Power Query Eklentisini önceki bir sürümde kullanıyorsanız, Power Query -> Dış Veriler -> Tablodan'a gidin.
Sorgu düzenleyicide, özetini çıkarmak istediğiniz sütunları seçin. Bu durumda, bunlar dört çeyrek için olanlar. Tüm sütunları seçmek için Shift tuşunu basılı tutun ve ardından ilk sütunu ve ardından son sütunu seçin.
Sorgu Düzenleyicisi'nde Dönüştür -> Herhangi Bir Sütun -> Özet Sütunları Aç'a gidin. Bu, sütunun verilerini Özet Tablo dostu biçime dönüştürecektir.
Power Query, sütunlara genel adlar verir. Bu isimleri istediğiniz isimlerle değiştirin. Bu durumda, Niteliği Çeyrek ve Değeri Satış olarak değiştirin.
Sorgu Düzenleyici'de Dosya -> Kapat ve Yükle'ye gidin. Bu, Power Query Düzenleyicisi iletişim kutusunu kapatır ve verileri özetlenmemiş sütunlara sahip olacak ayrı bir çalışma sayfası oluşturur.