Excel Power Pivot: Adım Adım Kılavuz

Büyük Excel dosyalarını analiz etmek genellikle yavaş performansa yol açar. 

Power Pivot farklı bir yaklaşım sunar. Tabloları birbirine bağlar ve hesaplamaları performanstan ödün vermeden yürütür. VLOOKUP() zincirleri ve yardımcı sütunlarla boğuşmak yerine, doğrudan Excel'e yerleşik yapılandırılmış bir sistemle çalışırsınız.

Bu kılavuzda, veri modellerini nasıl kuracağınızı, tablo ilişkileri oluşturmayı, DAX formülleri yazmayı ve Power Pivot kullanarak etkileşimli raporlar oluşturmayı öğreneceksiniz.

Power Pivot Nedir ve Neden Faydalıdır?

Power Pivot, Excel’in yerleşik veri modelleme motorudur. Daha büyük veri kümelerini içeri almanıza, birden çok tabloyu bağlamanıza ve geleneksel çalışma sayfalarında yaşayacağınız hantallık olmadan karmaşık hesaplamalar çalıştırmanıza olanak tanır.

Power Pivot nasıl farklıdır

Verileri doğrudan bir sayfada saklamak yerine, Power Pivot her şeyi Excel’in dahili veri modeline yükler. 

Standart bir çalışma sayfası yaklaşık bir milyon satıra ulaşabilir ve genellikle çok daha erken yavaşlar. Power Pivot, verileri sıkıştırıp ayrı yöneterek bu sınırı aşar; böylece çalışma kitabı performansını korurken on milyonlarca satırla çalışabilirsiniz.

VLOOKUP zincirleri yerine ilişkisel yapı

Verileriniz modele girdikten sonra, tıpkı hafif bir veritabanında olduğu gibi anahtarlar kullanarak tabloları ilişkilendirebilirsiniz. Her şeyi tek bir dev sayfaya düzleştirip tabloları birleştirmek için iç içe VLOOKUP() işlevleri kullanmaya gerek kalmaz. Power Pivot, ilişkili tabloları yan yana, temiz ve güvenilir şekilde analiz etmenizi sağlar.

DAX ile daha güçlü hesaplamalar

Power Pivot, analitik çalışmalar için özel olarak geliştirilmiş bir formül dili olan DAX (Data Analysis Expressions)'i kullanır. Bunu, basit toplamlardan zaman bazlı metriklere, oranlara, hareketli pencerelere ve diğer gelişmiş hesaplamalara kadar standart bir PivotTable’ın kaldıramayacağı ölçüler oluşturmak için kullanabilirsiniz.

Örnek senaryolar

İşletmelerin Power Pivot’u iş operasyonlarında nasıl kullandıklarına dair iki örnek: 

• Satış performans takibi: Sipariş geçmişini, ürün tablolarını ve müşteri niteliklerini birleştirin; ardından el ile birleştirme yapmadan yıllık gelir veya müşteri yaşam boyu değeri için DAX ölçüleri oluşturun.
• Operasyon raporlaması: Envanter, sevkiyat ve tedarikçi verilerini bağlayın; ardından aynı modelden doluluk oranları, tedarik süreleri veya tahmin sapmalarını hesaplayın.

Kısacası, Power Pivot size Excel içinde veritabanı benzeri bir deneyim sunar. Büyük veya çok tabloluk veri kümeleriyle çalışıyorsanız, dağınık raporlama iş akışlarınızı üzerine inşa edebileceğiniz hızlı ve ölçeklenebilir modellere dönüştürebilir.

Excel'de Power Pivot'u Kurma

Şimdi Excel’de Power Pivot’u nasıl kullanmaya başlayabileceğinize bakalım. 

Power Pivot'u etkinleştirin

Power Pivot’u indirmenize gerek yok. Zaten Excel’de mevcut. Etkinleştirmek için:

1. Excel sayfasını açın 
2. Şeritten Dosyalar'a tıklayın
3. Şunu seçin: Seçenekler > Eklentiler 
4. Ardından açılır menüden COM Eklentileri'ni seçin ve Git'e tıklayın
5. Bir açılır pencere görünecek. Buradan Microsoft Power Pivot for Excel'i seçin, ardından Tamam'a tıklayın

Artık Power Pivot şeridinizde görünecektir.

Excel'de Power Pivot eklentisini etkinleştirin. Görsel: Yazar.

Not: Power Pivot yalnızca Excel Professional Plus veya Microsoft 365’te çalışır. Etkinleştirdikten sonra sekmeyi görmüyorsanız, bilgisayarınızdaki Excel sürümünde bulunmuyor olabilir.

Birden çok kaynaktan veri içe aktarın

Artık bir Excel dosyası, CSV dosyası veya hatta bir SQL Server veritabanı gibi farklı kaynaklardan veri içe aktarabilirsiniz. 

Bu örnek için, bir .xlsb dosyasında iki veri kümesi var:

1. sales.xlsb 

2. customer.xlsb 

Bunları Power Pivot’a aktarmak için:

1. Power Pivot sekmesine tıklayın ve Yönet'i seçin. Yeni bir pencere açılacaktır
2. Giriş'e gidin, ardından Harici Ver Al'a tıklayın ve Diğer Kaynaklardan'ı seçin
3. Aşağı kaydırın ve Excel Dosyası'na tıklayın

Verileri diğer kaynaklardan alın. Görsel: Yazar.

4. Şimdi açılır pencerede Gözat 'a tıklayın ve customer.xlsb dosyasını seçin 

5. İlk satırı sütun başlığı olarak kullan kutucuğunu işaretleyin ve İleri'ye tıklayın

Excel dosyasını Power Pivot'a içe aktarın. Görsel: Yazar.

Sonraki pencerede, içe aktarmadan önce verilerinizin nasıl görüneceğini görmek için Önizleme ve Filtrele'ye tıklayın. Tatmin olduğunuzda Tamam'a tıklayın; tüm satırların başarıyla aktarıldığını gösterecektir. Ardından Kapat'a tıklayın.

Seçilen verileri önizleyin. Görsel: Yazar. 

sales.xlsb dosyası için aynı işlemi tekrarlayın. Ardından, ekranın altında her iki dosya da içe aktarılmış olarak gösterilecektir. Üzerine çift tıklayın ve yeniden adlandırın. 

Her iki dosya da içe aktarıldı. Görsel: Yazar.

İlişkiler ve Veri Modelleri Oluşturma

Verileriniz Power Pivot’a yüklendiğine göre, tabloların nasıl bağlandığını Excel’in anlaması için onları birbirine bağlama zamanı. Bu adım, tüm raporlarınızın temelini oluşturur.

Tablolar arasında ilişkiler oluşturun

1. Giriş sekmesinde Diyagram Görünümü'ne tıklayın. Orada içe aktarılan her iki tabloyu da göreceksiniz
2. Sales tablosunda CustomerID'ye tıklayın
3. Bunu, iki tablo arasında ilişki oluşturmak için Customer tablosundaki CustomerID'ye sürükleyin

Not: İlişkiyi düzenlemek isterseniz, çizgiye sağ tıklayın ve İlişkiyi Düzenle..'ye tıklayın. Pencerede, ilişki kurmak istediğiniz sütunları seçin.

Tablolar arasında ilişki kurun. Görsel: Yazar.

Bu ilişkide bir müşteri Sales tablosunda birçok kez görünebilir ancak her müşteri Customers tablosunda yalnızca bir kez görünür. Bu, PivotTable’larda her iki tablodan alanları kullanmamıza ve arama formülleri olmadan hesaplama yapmamıza olanak tanıyan basit bir bire-çok ilişkidir.

Yıldız şemasıyla tasarlayın

Yıldız şeması, bir Power Pivot modelini yapılandırmanın en basit yollarından biridir. Tablolarınızı düzenli tutar ve hesaplamaları öngörülebilir kılar.

Önce, olgu (fact) tablosunu seçmeniz gerekir. Bu durumda, Sales olgu tablosu görevi görür; çünkü işlem kayıtlarını tutar: tarih, müşteri, ürün, miktar ve tutar.

Ardından, boyut tablolarını belirleyin; bunlar Sales'i açıklar. Yaygın örnekler:

• Customers (birincil anahtar: CustomerID)
• Products (birincil anahtar: ProductID)
• Regions (birincil anahtar: RegionID)

Her boyut tablosunun bir birincil anahtarı vardır. Bu anahtarı olgu tablosundaki eşleşen yabancı anahtara bağlarsınız:

• Customers.CustomerID → Sales.CustomerID
• Products.ProductID → Sales.ProductID
• Regions.RegionID → Customers.RegionID

Bağlantılar kurulduğunda, Sales tablosu ortada yer alır ve etrafında boyut tabloları ışınsal olarak konumlanır. İşte yıldızınız bu. Bu yapı modeli net tutar, hesaplamaları hızlandırır ve raporlama tutarlılığını artırır.

Bir yıldız şeması oluşturun. Görsel: Yazar.

Hesaplanan sütunlar ekleyin

İlişkileriniz hazırken, veri modelinde doğrudan yeni alanlar oluşturabilirsiniz.

1. Veri Görünümü'ne geçin

2. Tablonun sonundaki boş Sütun Ekle alanını seçin.

3. = [TotalAmount] / [Qty] yazın ve Excel tüm sütunu doldursun diye Enter'a basın

4. Başlığı PricePerUnit olarak yeniden adlandırın

Bu şekilde, hesaplanan sütunlar tablonun bir parçası haline gelir. Modelde saklanırlar, verilerinizle birlikte yenilenirler ve daha sonra oluşturacağınız herhangi bir PivotTable veya DAX ölçüsü için kullanılabilir durumda kalırlar.

Ek bir hesaplanan sütun ekleyin. Görsel: Yazar.

Analiz için DAX Formülleri Yazma

Model hazır olduğuna göre, verileri analiz etmek için DAX formülleri oluşturmaya başlayabiliriz. Bu formüller, raporlarımız içinde toplamlar, karşılaştırmalar ve zaman bazlı hesaplamalar oluşturmamıza yardımcı olur.

Ölçüler oluşturun

Hesaplamaların PivotTable içinde otomatik olarak yenilenmesini istediğinizde ölçüleri kullanmalısınız.

Bir ölçü oluşturmak için:

1. Power Pivot penceresini açın

2. Giriş > Hesaplamalar > Yeni Ölçü'ye gidin

3. = SUM(Sales[TotalAmount]) gibi bir formül girin

4. Adını Total Sales olarak belirleyin ve Tamam'ı seçin

Ölçüler oluşturun. Görsel: Yazar. 

Toplamın yüzdesi ölçüsü ekleyin

Aşağıdaki formülle toplamın yüzdesi ölçüsü de ekleyebilirsiniz:

= DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Regions)))

Bu, her bölgenin toplam gelire katkısını gösterir.

Toplamın yüzdesi ölçüsü ekleyin. Görsel: Yazar.

Zaman zekâsını kullanın

Zaman zekâsı işlevleri, verinin günler, aylar, çeyrekler ve yıllar boyunca nasıl değiştiğini anlayan DAX formülleridir. Yılbaşından bugüne toplamları hesaplamanızı, sonuçları önceki dönemlerle karşılaştırmanızı ve eğilimleri, filtreleri manuel ayarlamadan değerlendirmenizi sağlar.

Bu işlevlerin modelinizde nasıl çalıştığını görmek için önce düzgün bir Tarih tablosuna ihtiyacınız var.

Tarih tablosunu kurun

Tabloyu kurmak için: 

1. Power Pivot > Veri Modeline Ekle'ye gidin
2. Power Pivot’ta tabloyu seçin ve Tasarım > Tarih Tablosu Olarak İşaretle'yi seçin

Bir veri tablosu oluşturun. Görsel: Yazar.

3. Şimdi Giriş > Diyagram Görünümü'nden Date[Date] → Sales[OrderDate] bağlantısını kurun.

Date Table[Date]'i Sales[OrderDate]'e bağlayın. Görsel: Yazar.

Zaman zekâsı ölçüleri oluşturun

Date tablosu hazır olduğunda, farklı dönemlerde performansı değerlendiren ölçüler oluşturabilirsiniz.

Yılbaşından bugüne (YTD):

Total Sales YTD :=
TOTALYTD([Total Sales], 'Date Table'[Date])

Geçen yılla karşılaştırma:

Sales Last Year :=
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date Table'[Date]))

Zamanı hesaplayın. Görsel: Yazar. 

Ölçüler hazır olduğunda Excel’e geri dönün ve Veri Modelini kullanarak bir PivotTable oluşturun. Ardından, Satırlar alanına Date tablosundan alanlar yerleştirin ve Değerler'e Total Sales, Total Sales YTD ve Sales Last Year'ı ekleyin. 

Bu, zaman zekâsı ölçülerinin model içindeki Date tablosuyla nasıl çalıştığını gösterir.

Toplam Satış, YTD ve Geçen Yıl bilgisini gösteren PivotTable. Görsel: Yazar. 

Yaygın DAX kalıpları

Bazı DAX formülleri sıkça karşımıza çıkar; çünkü verileri hızla bölümlendirmenize ve yaygın soruları yanıtlamanıza yardımcı olur. Birçok modelde iyi çalışan iki kalıp şunlardır:

Kategori başına ortalama:

Average Sales Per Category :=
AVERAGE(Sales[TotalAmount])

Tarihler boyunca kümülatif toplam:

Running Total Sales :=
CALCULATE(
    [Total Sales],
    FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))
)

Ölçüler oluştururken şu basit alışkanlıkları edinin: 

• Açık isimler verin
• Formülleri okunur tutun
• Ölçü uzadığında değişkenleri (VAR) kullanın. 

Böylece, daha sonra modele geri döndüğünüzde anlaması daha kolay olur.

Modelinizi Görselleştirme ve Etkileşim

Model ve ölçüler tamamlandığına göre, verileri gerçek zamanlı olarak keşfedip ayarlayabileceğiniz görsellere dönüştürelim.

PivotTable ve PivotChart oluşturun

Bağlı tablolarınızla doğrudan çalışmak için veri modelinden bir PivotTable ekleme adımları şöyle:

1. Bir Excel sayfası açın
2. Ekle > PivotTable > Veri Modelinden'e gidin
3. Yeni Çalışma Sayfası'nı seçin

PivotTable Alanları bölmesinde artık herhangi bir tablodan alanları çekebilirsiniz. Örneğin:

• Regions tablosundan RegionName'i Satırlar alanına sürükleyin
• Total Sales'i Değerler alanına sürükleyin

İlişkileri önceden kurduğumuz için, Excel her şeyi otomatik olarak bir araya getirir.

Power Pivot verilerini kullanarak bir PivotTable oluşturun. Görsel: Yazar.

Görsel isterseniz, PivotTable içinde herhangi bir yere tıklayın, Ekle > PivotChart'a gidin, bir grafik türü seçin (örneğin Kümelenmiş Sütun) ve onaylayın. Grafik PivotTable'a bağlı kalır, bu nedenle her şey birlikte güncellenir.

PivotChart ekleyin. Görsel: Yazar.

Dilimleyiciler ve filtreler ekleyin

Dilimleyiciler, raporu etkileşimli hale getiren hızlı, düğme tarzı filtreler sunar. Eklemek için:

1. PivotTable'ınıza tıklayın
2. Ekle > Dilimleyici'ye gidin
3. RegionName veya ProductName gibi alanları seçin

Dilimleyici sayfada bir kutu olarak görünür. Farklı öğelere tıkladığınızda PivotTable ve grafik anında güncellenir. Birden fazla PivotTable varsa, tek bir dilimleyiciyi hepsine bağlayarak sayfa genelinde tutarlı filtreleme sağlayabilirsiniz.

Dilimleyiciler ekleyin. Görsel: Yazar.

KPI'lar oluşturun

KPI'lar, sayfaya ekstra hesaplama eklemeden hedefe karşı performansı görmenize yardımcı olur. Kendi KPI'nızı oluşturmak için:

1. Power Pivot penceresinde KPI'lar > Yeni KPI'ya gidin
2. Temel ölçü olarak Total Sales'i ayarlayın
3. Mutlak değer'i kullanın, hedefinizi girin (örneğin 4000), eşikleri ayarlayın ve bir simge stili seçin
4. KPI'yı oluşturmak için Tamam'a tıklayın

Bir ölçünün KPI'sını ayarlayın. Görsel: Yazar.

5. PivotTable alanları bölmesinde Sales tablosunu genişletin, ardından Total Sales'i genişletin
6. Buradan Total Sales ve Status alanlarını Değer alanına sürükleyin

Artık eşiğe karşı hedef performansını görebilirsiniz. 

KPI durumunu bir Excel PivotTable içinde görüntüleyin. Görsel: Yazar.

Power Pivot Performansını Optimize Etme

Model kurulduktan sonra, hızlı ve kullanımı kolay kalmasını isteriz. Power Pivot büyük veri kümelerini kaldırabilir; ancak birkaç küçük ayar, özellikle zamanla daha fazla veri ekledikçe dosyanın duyarlı kalmasına yardımcı olur.

Model boyutunu küçültün

Daha hafif bir model daha hızlı çalışır; bu nedenle ihtiyacınız olmayan her şeyi kaldırın.

Veri Görünümü'nde kullanılmayan sütunları silebilirsiniz. Bir sütun PivotTable’da hiç görünmese bile bellek tüketir; bu yüzden kırpma işlemi modeli temiz tutar.

Yeni veri alırken, modele girmeden önce satır ve sütunları filtrelemek için Power Query kullanın. Böylece yalnızca ilgilendiğiniz alanlar yüklenir ve her şey daha temiz kalır.

Hesaplanan sütunlardan zorunlu olmadıkça kaçınmaya çalışın; çünkü her satır için bir değer saklarlar ve bu da dosya boyutunu hızla büyütür. Buna karşılık, ölçüler daha verimlidir; çünkü yalnızca bir PivotTable onlara ihtiyaç duyduğunda hesaplanırlar. 

Verimli veri türleri seçin

Power Pivot, veri türüne bağlı olarak verileri farklı şekilde sıkıştırır. Doğru türü kullanmak fark edilir bir etki yaratabilir.

Veri Görünümü'nde bir sütunu seçin ve şeritteki Veri Türü altında en doğru türü seçin. Örneğin:

• Tam sayılar > Tam Sayı
• Ondalık değerler > Ondalık Sayı
• Matematikte kullanılmayan ID’ler veya kodlar > Metin

Doğru türü seçtiğinizde Power Pivot sütunu daha iyi sıkıştırır; bu da boyutu azaltır ve hesaplamaları hızlandırır.

Doğru veri türünü kontrol edin ve kullanın. Görsel: Yazar.

Yenileme ve hesaplama sorunlarını yönetin

PivotTable’larınız en güncel verileri yansıtmıyorsa, Power Pivot sekmesine gidip Tümünü Yenile'ye tıklayın. Bu, her şeyi kaynak dosyalarınızdan yeniden yükler.

Sayılar tuhaf görünüyorsa, Diyagram Görünümü'nü açın ve ilişkilerinizi kontrol edin; çünkü eksik veya bozuk bir ilişki, toplamların sıçramasına veya yanlış filtrelenmesine neden olabilir.

DAX hatasıyla karşılaşırsanız, özellikle daha karmaşık ölçülerde, genellikle formülün dolaylı olarak kendisini referans aldığı anlamına gelir. Bu durumda ölçüyü daha basit mantıkla yeniden yazın veya dairesel başvuruyu çözmek için VAR bloklarını kullanın.

Power Query ve Power BI ile Entegrasyon

Power Pivot’un avantajlarından biri, Microsoft veri yığınıyla ne kadar kolay çalıştığıdır. Modelinize girmeden önce verileri temizlemek ve şekillendirmek için Power Query'yi kullanabilir veya etkileşimli panellere ihtiyaç duyduğunuzda tüm modeli Power BI'a taşıyabilirsiniz.

Power Query'de verileri temizleyin ve dönüştürün

Power Query, verileri Power Pivot’a yüklemeden önce hazırlamak için en iyi yerdir. Her şeyi en baştan temizlemenize, filtrelemenize ve şekillendirmenize olanak tanır; böylece model düzenli kalır.

Power Query’yi şu yollarla açabilirsiniz: Veri > Metinden/CSV’den > Dönüştür. Bu işlem verileri düzenleyiciye getirir; burada şunları yapabilirsiniz:

• Yinelenen satırları kaldırma
• Sütunları yeniden adlandırma veya yeniden sıralama
• İhtiyacınız olmayan değerleri filtreleme
• Veri modele ulaşmadan önce veri türlerini değiştirme

Power Query, penceresinin sağ tarafında her adımı kaydeder. Bu da dosyayı her yenilediğinizde temizlemenin otomatik olarak çalıştığı anlamına gelir.

Her şey doğru göründüğünde Kapat ve Yükle Hedef'i seçin, ardından Veri Modeli'ni seçin. Temizlenen veriler doğrudan Power Pivot’a yüklenir.

Modelleri Power BI'a aktarın

Daha zengin görseller veya paylaşımlı paneller gerektiğinde Power Pivot modelinizi Power BI'a da taşıyabilirsiniz. Şöyle:

1. Excel çalışma kitabınızı kaydedin
2. Power BI Desktop'ı açın
3. Veri Al > Excel Çalışma Kitabı'na gidin
4. Dosyanızı seçin

Power BI, tabloları ve ilişkileri Power Pivot’taki halleriyle aynen içe aktarır. Buradan panolar oluşturabilir, ekibinizle işbirliği yapabilir ve raporlarınızın manuel adımlar olmadan güncel kalması için zamanlanmış yenilemeler ayarlayabilirsiniz.

Sürdürülebilir Modeller için En İyi Uygulamalar

Modeliniz büyüdükçe düzeni korumak; daha sonra güncellemenizi, hata ayıklamanızı ve üzerine inşa etmenizi kolaylaştırır. Bu nedenle, modelin zaman içinde temiz ve güvenilir kalmasına yardımcı olan birkaç alışkanlık şunlardır:

Adlandırma kuralları ve düzenleme

Açık isimler, bir dosyaya haftalar veya aylar sonra geri döndüğünüzde büyük fark yaratır. Bu yüzden, her ölçünün neyi temsil ettiğini her zaman bilmeniz için Total_Sales, Total_Quantity veya Profit_Margin gibi okunabilir ölçü adları kullanmalısınız.

Power Pivot penceresinde ilgili ölçüleri Görüntü Klasörleri içinde de gruplayabilirsiniz. Model büyüdüğünde bu klasörler, gereken hesaplamaları bulmayı kolaylaştırır.

Veri doğrulama

Sayılara güvenmeden önce birkaç hızlı kontrol yapın:

• Kaynak verideki toplamları PivotTable'lardaki toplamlarla karşılaştırın
• Şu basit DAX kontrollerini kullanın:

• COUNTROWS() ile bir tablodaki satır sayısını teyit edin

• DISTINCTCOUNT() ile müşteriler veya ürünler gibi benzersiz değerleri doğrulayın

Bu küçük testler, daha büyük sorunlara yol açmadan önce eksik ilişkileri, hatalı filtreleri veya veri problemlerini fark etmenize yardımcı olur.

Modellerinizi sürdürün ve güncelleyin

Yeni veriler geldiğinde Power Pivot sekmesine gidip Yenile veya Tümünü Yenile'yi seçin. Böylece Power Pivot, bağlı kaynaklardan her şeyi yeniden yükler.

Yeni ilişkiler eklemek veya kritik ölçüleri yeniden yazmak gibi büyük yapısal değişiklikler yapmadan önce dosyanın bir yedeğini kaydedin. Böylece planlandığı gibi gitmeyen bir durumda güvenli bir geri dönüşünüz olur.

Son Düşünceler

Power Pivot, verilerinizi tek bir yerde bir araya getirir ve net, güvenilir raporlar oluşturmanıza yardımcı olur. Model kurulduktan sonra, sayılarını keşfedin, görseller oluşturun ve her şeyi tek bir yenilemeyle güncelleyin.

Excel’in tüm araç setini öğrenmek istiyorsanız, Excel Power Tools ile Veri Analizi eğitim yolumuzu ve (elbette) Excel'de Power Pivot kursumuzu inceleyin.