Excel Pivot Tablolar: Gerçek Profesyonel İpuçlarıyla Şimdiye Kadarki En Kapsamlı Rehber
Bu makalede, Excel Pivot Tabloları ile ilgili derin uzmanlığımızı paylaşmaya çalışacağız. Burada, sıradan pivot tablo eğitimlerinde veya yazılarında göremeyeceğiniz çok nadir bazı ipuçları bulacaksınız.
Bu makale, Someka stajyerleri için hazırlanan dahili eğitimlerden oluşturulmuştur. Yani burada nokta atışı uzman tüyoları bulacaksınız!
Hadi başlayalım!
İçerik Tablosu
1. Excel Pivot Tablolarının arkasındaki mantık nedir?
2. Değiştirilmesi Gereken İlk 3 Şey
3. Pivot Tablo Hızlı Düzen Tasarım Seçenekleri
4. Pivot Tabloları Adlandırma
5. Pivot Tablo Gelişmiş Tasarım
6. Pivot Tablolarda Tarih ve Sayı Biçimlendirme
7. Bonus İpucu: Pivot Tablolar ile Veri Tutarsızlıklarını Bulma
8. Son Sözler
1. Excel Pivot Tablolarının arkasındaki mantık nedir?
İlk olarak, pivot tabloların arkasındaki temel mantık hakkında konuşacağız.
Excel pivot tablosu gerçekte ne işe yarar? Bu, Excel geliştirici adaylarımız için mülakat sorularımızdan biridir:
Pivot tablolarla aynı işlemleri formüller kullanarak gerçekleştirecek olsaydınız hangi formülleri kullanırdınız?
Cevap çok kolay. Diğer özelliklerine ek olarak, pivot tablolar temel olarak seçilen kriterlere göre veri grupları oluşturur ve daha sonra bu grupları sayar, toplar, ortalamalarını alır.
Esasen Pivot Tabloların yaptığı şey şudur: Veriler üzerinde sayma veya diğer işlemleri hızlı bir şekilde gerçekleştirme gibi belirli işlemleri belirli bir şekilde gerçekleştirirler.
Bir örnek verelim. Aşağıda ham veri kaynağımız yer almaktadır:
Şimdi her bir alan için toplam satış miktarını hesaplamak istiyoruz. İki seçeneğimiz var. Pivot tabloları kullanabiliriz veya SUMIF fonksiyonunu kullanabiliriz.
Bir Pivot Tablonun en büyük güzelliği manipülasyon kolaylığı ve hızıdır. Çalışma mekanizması formüllerden farklıdır, verileri Excel dosyası içinde benzersiz bir önbellekte depolar.
Bu ayrıntılar çok önemli olmayabilir, ancak bir Pivot Tablonun temel işlevinin esasen frekans dağılımı olduğunu unutmayın. Elbette başka birçok özelliği de vardır, ancak bu temel özelliği anlamak çok önemlidir, bu yüzden ilk olarak bunu ele aldık.
Excel Pivot Tabloları Nasıl Oluşturulur?
Pivot Tablo oluşturmayla ilgili çok temel bazı şeyleri ele alacağız. Boş bir Excel dosyası ve kaynak verilerle başlayalım.
Bir pivot tablo eklemek çok kolaydır: Kaynak verilerinizi seçin ve Ekle > Pivot Tablo > Tablodan/aralıktan seçeneğine gidin.
Ancak pivot tablonuzu nereye ekleyeceksiniz?
Size otomatik olarak ‘Yeni Çalışma Sayfası’ önerir. Ancak bu şekilde olmak zorunda değil. Kaynak verilerle aynı sayfayı da seçebiliriz. Örneğin, verileri seçebilir ve yeni bir sayfa oluşturmadan aynı sekmede hemen sıralayabiliriz.
Not: Ayrıca bir ‘Bu verileri Veri Modeline ekle’ seçeneği de vardır. Bunu daha sonra ele alacağız.
İkiden fazla tabloya bağlantı veriyorsanız, karmaşık veri modellerinden pivot tablolar ve grafikler oluşturmak için kolay bir yol sağlayan excel için power pivot‘ta uzmanlaşmalısınız.
2. Değiştirilecek İlk 3 Şey
Bir Pivot Tablo oluşturduktan hemen sonra yapmanızı önerdiğimiz ilk şey seçeneklere gitmek ve:
- Güncellemede otomatik sığdırma sütun genişliklerini kaldırma
- Hata Değerleri için onay kutusu boş göster
- Veri sekmesine gidin ve Alan Başına Tutulacak Öğe Sayısını Yok olarak ayarlayın
Bu değişikliklerin ne anlama geldiğini açıklayayım:
Otomatik uyum
Bu otomatik sığdırma seçeneğini işaretli bıraktığınızda, sütunların genişliğini otomatik olarak ayarlar. Otomatik sığdırma duruma bağlı olarak faydalı olabilir, ancak Pivot Tablolarımızı genellikle manuel olarak yönetiriz, bu nedenle buna ihtiyacımız yoktur. Bu daha çok düzeni yanlışlıkla bozabilecek uzman olmayan kullanıcılar içindir.
Bu yüzden Excel’de varsayılan ayar otomatik sığdırmadır, çünkü çirkin bir düzene sahip olmak görünmez bir düzene sahip olmaktan daha iyidir. Ancak güçlü bir Excel pivot tablo kullanıcısı olduğunuzda buna ihtiyacınız olmayacak.
Hata Değerleri için Boşlukları Göster
Hata durumları içindir. Diyelim ki bir şey hesaplıyoruz ve bir sıfıra bölme işlemi var. Normalde bir hata görünür, ancak bunu Pivot Tablomuzda istemeyiz.
Bir hata göstermek yerine bir boşluk göstermesini istiyoruz. Deneyimlerimize göre, bu genellikle daha iyidir, ancak hataları görmek istediğiniz durumlar olabileceğini lütfen unutmayın.
Silinen öğeler saklansın mı?
Yukarıda da belirtildiği gibi Excel Pivot Tabloları bir önbellekleme mantığı ile çalışır. Bu yüzden hızlıdırlar.
Ancak önbelleğe alırken Pivot Tablolar size “Veri kaynağından silinen öğeleri korumak istiyor musunuz?” diye sorar.
Bazen bunları tutmak için mantıklı nedenler olabilir. Ancak genellikle, karışıklığa neden olduğu için bunları saklamak istemeyiz. Sahte bir veri veya yanlış bir veri eklediğinizi ve ardından sildiğinizi düşünün. Ancak bu fetaure’u açık bırakırsanız, kaynak verilerinizde artık mevcut olmasalar bile silinen tüm öğeleri tutacaktır.
Özet olarak, bir Pivot Tablo oluştururken yapılması gereken ilk üç şey şunlardır: Seçeneklere gidin ve 1. Otomatik sığdırmayı kaldırın, 2. Hata için boş gösterin ve 3. Saklama değerini Veri Yok olarak ayarlayın. Bu adımlar otomatik hale gelmelidir.
3. Pivot Tablo Hızlı Düzen Tasarım Seçenekleri
Pivot Tablo hızlı tasarımı hakkında size bazı ipuçları vermeyeceğiz. İlk bakışta temiz bir görünüm için bu dört değişikliği yapıyoruz:
- Alt toplamları gösterme
- Satırlar ve Sütunlar için Genel Toplamlar kapalı
- Tablo Formunda Rapor Düzeni Gösterimi
- Rapor Düzeni Tüm Öğe Etiketlerini Tekrarla
İşte bu dört değişikliğin özeti:
Tabular Form tasarımı bir tercihtir, ancak net veri analizine yardımcı olur. Çünkü güzel görünür ve netliği korur.
Ayrıca, genişletme ve daraltma içinartı/eksi düğmelerini dekaldırabilirsiniz. Daha temiz bir görünüm için genellikle bunları kaldırırız.
Diğer düzen formları daha şık görünebilir, ancak veri analizi nedenleriyle, bu seçenekler pivot verilerinizi kopyalamayı ve ayrı bir analiz yapmayı veya pivot tablonuzun pivot tablosunu almayı kolaylaştırır.
Ancak belki de tüm analizleriniz tamamlandıktan sonra, nihai raporunuz veya gösterge tablonuz için başka harika düzen tasarımlarını tercih edebilirsiniz.
4. Pivot Tabloları Adlandırma
Ayrıca, Excel Pivot Tablolarınıza her zaman açıklayıcı isimler verin. Bu, özellikle karmaşık senaryolarda onları tanımlamaya yardımcı olur. Her zaman kullanmasanız bile bu iyi bir uygulamadır.
Özellikle dilimleyici veya grafik eklerken bu isim size çok yardımcı olacaktır.
Bir pivot tablo nasıl adlandırılır?
Pivot Tablo Araçları > PivotTable Analizi altında, varsayılan adı daha açıklayıcı bir adla değiştirebilirsiniz.
5. Excel Pivot Tablo Gelişmiş Tasarım
Şimdi, birçok kişinin sıklıkla keşfetmediği bir özellik olan Pivot Tablo tasarımından bahsedelim. Tasarım sekmesinden çeşitli değişiklikler yapabilirsiniz. Örneğin, kalın satır başlıklarını beğenmediyseniz, tablonun daha temiz görünmesi için bu biçimlendirmeyi kaldırmayı seçebilirsiniz.
İlk olarak, pivot tablonuzu ayrı bir sayfaya ekliyorsanız, daha güzel görünen bir düzen için kılavuz çizgilerini kaldırmaktan çekinmeyin.
İkinci olarak, sütun genişliklerini verilerinize göre değiştirebilirsiniz. Ayrıca, verilerinizin hizalanmasına da karar verebilirsiniz. Verilerin uzunluğu çok değişiyorsa, bunları sola hizalamanızı öneririz. Girilen veriler çoğunlukla benzer uzunluktaysa, Orta seçeneğini kullanabilirsiniz.
Pivot tablolarınızı tasarlarken, pivot sütunları seçmek için her zaman küçük okları kullanın. Aksi takdirde, yeni veri eklediğinizde ve pivot tablonuzu yenilediğinizde, tasarımınızı korumayacaktır!
Ardından, ilgili onay kutuları ile Stil Seçeneklerinizi seçebilirsiniz. Örneğin, ihtiyacınız yoksa Ham Başlıkları devre dışı bırakabilirsiniz.
Değişiklikleri uygularken aralıkları seçmenin farkına dikkat etmek önemlidir. Pivot Tablonun tamamını seçer ve bir biçim uygularsanız, biçim tüm tabloya uygulanır. Ancak yalnızca bir sütun veya belirli bir alan seçerseniz, biçimlendirme yalnızca oraya uygulanır. Bu ayrım, yaptığınız değişikliklerin tabloyu nasıl etkileyeceğini anlamak için çok önemlidir.
Özel Pivot Tablo Tasarımı
Birçok kişi özel Pivot Tablo tasarımınızı oluşturabileceğinizi bilmiyor.
Bunu yapmak için, önce mevcut bir tasarımı çoğaltın ve ardından tercihinize göre değiştirin. Bir kopya oluşturmak için PivotTable Stilleri altındaki herhangi bir varsayılan tasarıma sağ tıklayın ve Çoğalt’a tıklayın.
Ardından tasarımınıza özel bir isim verin.
Örneğin, başlık satırı metnini kalın olmayacak şekilde değiştirebilir veya ilk satır şeridinin biçimlendirmesini ayarlayabilirsiniz. İhtiyaçlarınıza uygun benzersiz bir tasarımı bu şekilde oluşturabilirsiniz.
Özel tasarımınızı değiştirmek için, değiştirmek istediğiniz Tasarım öğesine tıklayın ve ardından Biçimlendir’e tıklayın.
Excel Pivot Tablolarını daha derinlemesine inceleyelim. Tüm tablonun etrafına bir kenarlık eklemek istediğinizi varsayalım. Bir renk seçip uygulayabilirsiniz; bu nadir ama kullanışlı bir özelleştirmedir. Bir başka ipucu da daha temiz bir görünüm için kılavuz çizgilerini kaldırmaktır. Satır şeritlerinin rengini de değiştirmek isteyebilirsiniz. Değişiklik yaparken, sizin için en uygun olanı bulmak için farklı seçenekleri keşfedebilirsiniz.
Pivot Tablolardaki varsayılan tasarımların, kalın başlıklar gibi önceden ayarlanmış belirli stillerle birlikte geldiğini unutmayın. Tasarımınızı oluştururken bu öğeleri değiştirmekte özgürsünüz. Bu özelleştirme, özellikle kurumsal bir şablonda olduğu gibi Pivot Tablonuz için belirli bir görünüme ihtiyacınız varsa çok yararlı olabilir.
Pivot tablomuza kırmızı dış kenarlıklar nasıl eklenir?
İlk olarak tüm tabloyu tablo öğemiz olarak seçeceğiz:
Ardından Biçim’e tıklayacağız ve Biçim iletişim kutusunda istediğimiz değişikliği yapacağız:
Artık tablomuzun kırmızı bir kenarlığı var.
Aşağıda özel pivot tablolardan iyi bir örnek verilmiştir:
– Bu görsel Someka’nın özel pivot tablo tasarımları ile profesyonel bir görünüme sahip Ticaret Dergisi Şablonundan alınmıştır –
Bilmeniz gereken benzersiz bir püf noktası, kenarlığın nasıl uygulanacağıdır. Pivot Tabloya doğrudan bir kenarlık uygular ve ardından yenilerseniz, biçimlendirme bozulabilir.
Bunun yerine, kenarlığı Pivot Tablonun etrafındaki hücrelere uygulayın. Bu şekilde, kenarlık yenilendikten sonra bile bozulmadan kalır.
Ancak bu yöntemin bir dezavantajı olduğunu unutmayın. Pivot Tabloya yeni veriler eklenirse, kenarlık bunları içerecek şekilde otomatik olarak ayarlanmayacaktır. Daha dinamik ve uyarlanabilir bir tasarım için özel tasarım ınızı Pivot Tablo’nun tasarım ayarlarında oluşturmak daha iyidir. Bu şekilde, tasarım verilerdeki değişikliklere göre otomatik olarak ayarlanacaktır.
Tasarım seçimlerinizin verilerin kolay okunmasını ve yorumlanmasını kolaylaştırması gerektiğini unutmayın. Örneğin, ortalamaları hesaplarken, biçimlendirmenin gelecekteki veri girişlerine de uygulanacağından emin olun. Bu, tablonun dinamik ve yeni verilere duyarlı olmasını sağlar.
Sonuç olarak önemli olan, verilerin nasıl kullanılacağını ve yorumlanacağını göz önünde bulundurarak işlevsellik ile estetiği dengelemektir. Bu şekilde, yalnızca bilgilendirici değil aynı zamanda kullanıcı dostu bir Pivot Tablo oluşturursunuz.
6. Excel Pivot Tablolarında Tarih ve Sayı Biçimlendirme
Pivot Tablolarla ilgili yaygın bir sorun, özellikle sayı biçimini değiştirmeye çalışırken satırlar bölümündeki verilerin biçimlendirilmesidir.
Hücrenin kendisinde değişmiş gibi görünse de, sayı biçimini normal şekilde değiştirmenin Pivot Tabloya her zaman yansımadığını görebilirsiniz. Bunun nedeni Pivot Tabloların verileri farklı şekilde ele alması ve tasarımlarının belirli sınırlamalar getirmesidir.
Bu nedenle, tarih ve sayı biçimlendirmenizi her zaman Pivot Tablonuzun Değer Alanı Ayarları altında değiştirmelisiniz.
Peki ya bu ayarları göremiyorsanız?
İşte size profesyonel bir ipucu:
Biçimi değiştirmek için, kaynak verilerdeki tüm sütunun boşluk veya metin olmadan tarih olarak biçimlendirildiğinden emin olmanız gerekir. Sütunda boşluklar veya tarih olmayan öğeler varken biçimi değiştirmeye çalışırsanız, çalışmayacaktır. Bu, kaynak verilerinizdeki boşlukları veya tarih olmayan öğeleri kaldırarak verilerinizi temizlemenizi gerektirebilir.
Bir diğer önemli nokta ise, Pivot Tablonuza bağlı dilimleyicileriniz varsa, veri kaynağını güncellemenizi engelleyebilirler. Kaynak verilerde değişiklik yapmadan önce bu bağlantıları kaldırmanız gerekir.
Dolayısıyla, tarih biçimlendirmesi etkin değilse, bunun iki nedeni olabilir:
- Kaynak verilerinizde boş satırlar olabilir
- Bu pivot tablo ile ilgili dilimleyici bağlantılarınız olabilir
Bu nedenle, tarih veya sayı biçimlendirmesini değiştirmek için önce dilimleyici bağlantılarını kaldırın ve kaynak verilerinizde boş satır olmadığından emin olun.
Pivot Tabloda Dinamik Kaynaklar
İşte size faydalı bir ipucu:
Kaynak verileriniz için dinamik bir aralık oluşturun. Bu şekilde, verileriniz büyüdükçe Pivot Tablonuz istenmeyen boşluklar içermeden otomatik olarak ayarlanacaktır. Ad Yöneticisi’nde, aralığınızın yüksekliğini ve genişliğini gerçek veri boyutunuza göre ayarlayan bir OFFSET formülü kullanarak dinamik bir aralık oluşturabilirsiniz.
Öncelikle dinamik aralığınızın yüksekliğini MAX formülüyle hesaplayın, ardından Ad Yöneticisi’ne gidin ve OFFSET formülünü kullanın ve Offset veri aralığınızın yüksekliği olarak MAX formül hücresine referans verin.
Sadece profesyoneller için:
Boş kaynak verilerde biçimlendirmeyi neden değiştiremiyorsunuz?
İşte cevabı. Excel dosyanızı aç arsanız (evet, Excel dosyalarınızı açabilirsiniz!), tüm öğelerin aslında o zip klasöründeki XLM belgeleri olduğunu göreceksiniz.
Pivot tablolar da XLM dosyaları olarak kaydedilir ve ham verilerinizde boşluklar varsa, kaynak verilerinizi Metin biçimi olarak görür. Ancak, pivot sütununuzu tarih biçimlendirmesinde tutmak için tüm verilerinizin tarih biçiminde olması gerekir. Bu nedenle, verilerinizin içinde boş satırlar varsa biçimlendirmeyi değiştiremezsiniz.
Bu bölümün temel çıkarımları:
- Pivot Tablolarda dilimleyicilerin neden olduğu sınırlamaları ve kısıtlamaları anlama
- Pivot Tablolarda etkili biçimlendirme için kaynak verilerinizde boşluk bırakmamanın önemi
- Pivot Tablolarınız için dinamik bir kaynak veri aralığı oluşturma
- Pivot Tabloların verileri nasıl işlediğini ve sakladığını anlamak, bunları nasıl biçimlendirebileceğinizi etkilemek
7. Bonus İpucu: Excel Pivot Tabloları ile Veri Tutarsızlıklarını Bulma
Şimdi Pivot Tablolar ile oldukça kullanışlı ancak basit bir işlemi göstereceğiz.
Elinizde bir dizi veri olduğunu ve belirli kodların herhangi bir tutarsızlık olmadan tam olarak eşleştiğinden emin olmak istediğinizi varsayalım.
Örneğin, bir yerde S102’ye karşılık gelen 162 kodunuz varsa, bunun yanlışlıkla başka bir yerde S110’a karşılık gelmediğinden emin olmak istersiniz. Bu esasen bire bir ilişki sağlamaktır.
Örnek vermek gerekirse, ürün kodlarınız ve bunlara karşılık gelen değerleriniz olduğunu varsayalım. Her ürün kodunun bir ve yalnızca bir değerle eşleştiğinden emin olmak istiyorsunuz.
Örneğin, 162 kodu yalnızca S102 ile eşleşmeli ve veri kümesindeki başka hiçbir değerle eşleşmemelidir. Bu doğrulama, özellikle ürün kodlarını başka bir şeyle eşleştirdiğiniz ve her kodun benzersiz bir şekilde kullanıldığından emin olmanız gereken durumlarda çok önemlidir.
Bunu bir Pivot Tabloda kontrol etmek için önce kodları yan yana düzenlersiniz. Pivot Tabloyu tablo biçiminde ayarlayarak ve genel toplamları ve alt toplamları kapatarak, bire bir eşleşme olup olmadığını kolayca görebilirsiniz.
Eğer bir kod birden fazla değere karşılık geliyorsa (ya da tam tersi), bu açıkça görülebilir.
Bir Vaka Çalışması:
Örneğin, 162 kodu S102 ile eşleşecek şekilde ayarlanmışsa, ancak başka bir yerde de S106 ile eşleştiğini görürseniz, bire bir ilişki olmadığı açıktır.
Bunu Pivot Tablonun düzenini gözlemleyerek kontrol edebilirsiniz – aynı kod için birden fazla giriş varsa, bu birden fazla eşleşme olduğunu gösterir.
Ancak, bire bir ilişkiden tamamen emin olmak için bunu her iki yönden de kontrol etmeniz gerekir. Bu, verilerinizi bir sırayla (örneğin kod 2 sonra kod 3) ve ardından ters sırayla (kod 3 sonra kod 2) düzenlediğiniz anlamına gelir. Her iki düzenlemede de her kod ve her değer yalnızca bir kez görünüyorsa, bire bir ilişki olduğundan emin olabilirsiniz.
Bu yöntem, ilk başta anlaşılması biraz karmaşık olsa da, veri kümenizdeki veri ilişkilerinin doğruluğunu sağlamanın etkili bir yoludur. Özellikle öğeler arasında bire bir eşleşme sağlamanın çok önemli olduğu senaryolarda kullanışlıdır.
8. Son Sözler
Bu yazımızda sizlere Excel pivot tabloları hakkında hızlı ve profesyonel ipuçları vermeye çalıştık. Hiç şüphesiz pivot tablolar Microsoft Excel’in en eşsiz özelliklerinden biridir. Veri analiz araçları olarak pivot tablo, güçlü bir Excel kullanıcısı olma yolunda ilk adresiniz olmalı.
Önerilen Okumalar:
Excel dosyalarınızın ÇOK daha iyi görünmesini nasıl sağlarsınız?
İlgili Yazılar
