Pivot tablo öğeleri. Alınan materyalle ne yapacağız. Ayrıntı Verilerini Göster ve Gizle

Pivot tabloların ana unsurları. Bu çalışmaya geçmeden önce, pivot tabloların hangi öğelerden yapıldığını söylemenin gereksiz olmayacağını düşünüyorum ve bunun için Şekil 2'deki örneği ele alalım. Bir sayfa alanı, orijinal liste veya tablodaki, listeye yerleştirilmiş bir alandır. sayfa yönlendirme alanı pivot tablo.

Bu örnekte Kapsam, bölgeye göre özetlemek için kullanılabilen bir sayfa kenar boşluğudur. Başka bir sayfa alanı öğesi belirttiğinizde, pivot tablo o öğeyle ilişkili toplamları görüntülemek için yeniden hesaplanır. Sayfa alanı değeri - Sayfa alanı öğeleri, tablonun orijinal listesindeki bir alanın veya sütunun kayıtlarını veya değerlerini birleştirir. Başka bir deyişle, bu, aslında, yeni bir bölge için yeni bir veri kaynağına her geçiş olduğunda bu alanı değiştirmektir.

Satır alanları, PivotTable'ın satır alanına yerleştirilmiş orijinal liste veya tablodaki alanlardır. Bu örnekte, Ürünler ve Satıcı dize alanlarıdır. Örneğin, Satış görevlisi gibi dahili satır alanları, örneğin Ürün grubu dahili olanlar gibi veri alanı harici satır alanlarıyla tam olarak eşleşir. Böylece, burada belirli bir kural izlenir, daha yüksek seviyeli bir alanın her bir öğesi, daha düşük bir seviyenin tüm alanlarını mükemmelleştirir, ürün parametresi altındaki tüm verileri sınırlar - ürün satıcıları, bu satıcıların ürün satışları hakkındaki veriler. Bir sütun alanı, sütun alanına yerleştirilmiş bir kaynak listesindeki veya tablodaki bir alandır.

Bu örnekte Quarters, KB2 ve KB3 olmak üzere iki alan öğesi içeren bir sütun alanıdır. Sütunların iç alanları elemanlar içerir, karşılık gelen veri alanları iç alanların üzerinde bulunan sütunların dış alanlarıdır Örnek sadece bir sütun alanını gösterir.

Veri alanı, PivotTable'ın toplamları içeren bölümüdür. Veri alanı hücreleri, satır veya sütun alanı öğeleri için toplamları görüntüler. Veri alanının her hücresindeki değerler, orijinal verilere karşılık gelir. Bu örnekte, C6 hücresi aynı ürün adını, dağıtıcısını ve belirli bir Et, Myastorg LLP ve KB2 çeyreğini içeren tüm ham veri kayıtlarını özetler. Alan öğeleri, PivotTable alanının alt kategorileridir. Bu örnekte, Et ve Deniz Ürünleri değerleri Ürünler alanındaki alan öğeleridir.

Alan öğeleri, kaynak verilerdeki bir alan veya sütundaki kayıtları temsil eder. Alan öğeleri, satır veya sütun başlıkları olarak ve sayfa alanları için açılır listede görünür. Veri alanı, veri içeren bir kaynak listesindeki veya tablodaki bir alandır. Bu örnekte, Sipariş Miktarı alanı, Sipariş Miktarı alanındaki veya sütunundaki orijinal verileri özetleyen bir veri alanıdır. Bir veri alanı genellikle istatistikler veya satış miktarları gibi bir sayı grubunu özetler, ancak mevcut veriler metinsel de olabilir.

Varsayılan olarak, PivotTable, Toplam Değerler özet işlevini kullanarak metin verilerini ve Özet özet işlevini kullanarak sayısal verileri özetler. Bir pivot tablonun yapısını değiştirme Görünüm Alan düğmelerinin veya alan öğelerinin adlarını sürükleyerek pivot tabloyu doğrudan sayfada düzenleyebilirsiniz. Öğe adını seçmeniz gereken alandaki öğelerin sırasını da değiştirebilir ve ardından işaretçiyi hücre kenarlığına ayarlayabilirsiniz.

İşaretçi bir oka dönüştüğünde, alan hücresini yeni bir konuma sürükleyin. Bir alanı kaldırmak için alan düğmesini ayrıntılar alanının dışına sürükleyin. Bir alanı silmek, pivot tablodaki tüm bağımlı değerleri gizler ancak orijinal verileri etkilemez. Pivot tabloyu yapılandırmak için sağlanan tüm araçları kullanmanız gerekiyorsa veya geçerli tablo daha önce kaynak verilerin tüm alanlarını içermediyse, pivot tablo sihirbazını kullanmalısınız.

Pivot tablo büyük bir sayfa alanları grubu içeriyorsa, bunlar satırlara veya sütunlara yerleştirilebilir. Ek olarak, PivotTable'ın yapısını değiştirmenin orijinal verileri etkilemediğine dikkat edilmelidir.

İş bitimi -

Bu konu şu bölüme aittir:

Microsoft Excel kullanarak pivot tabloları kullanarak tablo bilgilerini işleme

Satırları ve sütunları değiştirerek, orijinal verilerin yeni toplamlarını oluşturabilirsiniz. farklı sayfalar verileri filtreleyebilirsiniz ve ... Başka bir deyişle, bu tablolar farklı kaynaklardan verileri birleştirmenize olanak tanır, ... Bir pivot tablo oluşturma Bir pivot tablo oluşturmadan önce, önce bu tablo için verileri ayarlamalısınız, .. .

Bu konuyla ilgili ek malzemeye ihtiyacınız varsa veya aradığınızı bulamadıysanız, çalışma tabanımızdaki aramayı kullanmanızı öneririz:

Alınan materyalle ne yapacağız:

Bu materyalin sizin için yararlı olduğu ortaya çıktıysa, sosyal ağlardaki sayfanıza kaydedebilirsiniz:

Bu bölümdeki tüm konular:

Bir pivot tablo oluşturma
Bir pivot tablo oluşturun. Bir pivot tablo oluşturmadan önce, öncelikle kitabın bir bölümünü, sayfaları ve bu bir veritabanı veya liste ise, sonra n'yi seçerek bu tablo için verileri ayarlamanız gerekir.

PivotTable Ayrıntıları ve Eylemleri
Pivot tablonun ayrıntıları ve üzerinde gerçekleştirilen eylemler. Ayrıntı verileri, bir pivot tablonun alt kategorisidir. Bu pivot tablo öğeleri, benzersiz tablo öğeleridir veya

Ayrıntılı PivotTable verilerini göster veya gizle
Ayrıntılı pivot tablo verilerini gösterin veya gizleyin. Daha önce gizlenmiş ayrıntılı verileri görüntülemek veya mevcut verileri gizlemek için alan öğesini, ayrıntılı verileri seçmeniz gerekir.

PivotTable alan öğelerini göster veya gizle
PivotTable alan öğelerini gösterin veya gizleyin. Bir alan öğesini görüntülemeniz veya gizlemeniz gerekiyorsa, bunun için önce görüntülemek istediğiniz alanı seçmeniz veya

Bir pivot tablo alanında maksimum veya minimum öğeleri görüntüleme
PivotTable alanındaki maksimum veya minimum öğeleri görüntüler. Maksimum veya minimum öğeleri görüntülemek için, öğelerin gelecekte gösterileceği alanı seçmek gerekir.

Pivot tablo ayrıntılarına erişimi devre dışı bırakın
Pivot tablo ayrıntılarına erişimi devre dışı bırakın. İmleci PivotTable'ın veri alanındaki bir hücrenin üzerine getirip çift tıklarsanız, kaynak verilerin bir özet listesi görüntülenir.

Bir pivot tablodaki verileri gruplama ve grubunu çözme
Bir pivot tablodaki verileri gruplama ve grubunu çözme. Bir pivot tabloda tarihleri, saatleri, sayıları ve seçili öğeleri gruplayabilirsiniz. Örneğin, kareyi özetlemek için ayları birleştirebilirsiniz.

PivotTable alanındaki öğeleri gruplama ve grubu çözme
PivotTable alanındaki öğeleri gruplayın ve grubunu çözün. Öğeleri gruplamak ve grubunu çözmek için, gruplanmış öğeleri seçmeniz gerekir. Ardından paneldeki Grup düğmesine tıklayın

Pivot tablodaki numaraları gruplama ve grubu çözme
Pivot tablodaki sayıları gruplama ve grubu çözme. Numaraları gruplandırmak ve grubu çözmek için, öğeleri gruplamak istediğiniz kutudaki numarayı seçin. Ardından düğmeye basın

Bir pivot tablodaki tarihleri \u200b\u200bveya saatleri gruplama ve grubu çözme
Bir pivot tablodaki tarihleri \u200b\u200bveya saatleri gruplayın ve gruplandırmayı kaldırın. Bir tarih veya saati gruplandırmak veya grubunu çözmek için, öğenin gruplanacağı alanda tarih veya saati seçin.

Pivot Tablo Sayfası Alanı Öğelerini Gruplama ve Grubu Çözme
PivotTable sayfa alanının öğelerini gruplayın ve grubunu çözün. Pivot tablo harici bir veri kaynağından oluşturulmuşsa, sayfa alanının harici verileri getirip getirmediğini kontrol edin

Pivot tablonun veri kaynağını yenileyin veya değiştirin. Çalışma sürecinde, bazen oluşturulan belgeye eklenmesi gereken yeni veriler göründüğünde bir durum ortaya çıkar ve

Bir pivot tablodaki verileri yenileme
Pivot tablodaki verileri yenileyin. Pivot tablodaki verileri güncellemek için aşağıdaki işlemleri yapmanız gerekir.Pivot tablodaki içeriği gerekli olan hücreyi seçmek gerekir.

Çalışma kitabı dosyasını açarken pivot tablo yenilemeyi devre dışı bırakın
Bir çalışma kitabı dosyasını açarken pivot tabloyu yenilemeyi devre dışı bırakın. Bir dosyayı açarken pivot tabloyu yenilemeyi devre dışı bırakmak için, pivot tabloda yenileme gerçekleşmeyecek bir hücre seçin

Pivot tablo verilerini sıralama
Pivot tablo verilerini sıralama. PivotTable alan öğeleri, adlarına göre otomatik olarak artan sırada sıralanır. Öğeleri ada göre sıralamak, orijinali geri yüklemenizi sağlar.

Pivot tablo verilerini özetleme ve işleme
Pivot tablonun verilerini özetleme ve işleme. Pivot tablo veri alanları, özet işlevinde varsayılan dışındaki hesaplamalar için yapılandırılabilir. Karmaşık gerçekleştirmek için

Bir pivot tabloda genel toplamları ve alt toplamları kullanma
Bir pivot tabloda genel toplamları ve alt toplamları kullanma. PivotTable, bir özet işlevi kullanarak hesaplamalar gerçekleştirmek üzere yapılandırılabilir. Özet işlevi, aşağıdakiler tarafından gerçekleştirilen hesaplama türüdür:

Bir pivot tabloya alt toplamları ekleme veya kaldırma
Bir pivot tabloya alt toplamları ekleyin veya kaldırın. Alt toplamları eklemek veya silmek için, imleci, içinde olmasını istediğiniz alanın düğmesine ayarlamanız gerekir.

Pivot tablo için bir grafik oluşturun
Pivot tablo için bir grafik oluşturun. Pivot tablo için bir grafik oluşturmak üzere PivotTables araç çubuğunda PivotTable menüsünden Seç komutunu seçin. Şunu kontrol et

Bu gönderi, Excel'deki basit ve şık PivotTable araçları koleksiyonunu tanıtır. İngilizce'de ipuçları ve püf noktaları denen şey. Buradaki ipuçlarını okumak için zaman ayırın. Kim bilir, belki de sonunda size uzun zamandır işkence eden sorunun cevabını bulacaksınız?

İpucu 1. Pivot tabloların otomatik olarak yenilenmesi

Bazen pivot tabloların otomatik olarak güncellenmesini istersiniz. Bir yönetici için bir pivot tablo oluşturduğunuzu varsayalım. Yönetici dizüstü bilgisayarına gitmenize izin vermediği sürece, düzenli olarak güncelleyebilmeniz olası değildir. Etkinleştirebilirsiniz otomatik güncelleme çalışma kitabı her açıldığında çalıştırılacak bir pivot tablo:

1. PivotTable'a sağ tıklayın ve Pivot tablo seçenekleri.
2. Görünen iletişim kutusunda Pivot tablo seçenekleri sekme seç Veri.
3. Kutuyu kontrol et Dosya açıldığında yenile.

Şekil: 1. Seçeneği etkinleştirin Dosya açıldığında yenile

Onay kutusu Dosya açıldığında yenile her bir pivot tablo için ayrı ayrı kurulmalıdır.

Notu formatta veya formatta örnekler (dosya VBA kodunu içerir) indirin.

İpucu 2. Kitabın tüm pivot tablolarının aynı anda güncellenmesi

Bir çalışma kitabı birden çok pivot tablo içeriyorsa, bunların aynı anda güncellenmesi sorunlu olabilir. Bu zorlukların üstesinden gelmenin birkaç yolu vardır:

Yöntem 1. Çalışma kitabının parçası olan her bir pivot tablo için, çalışma kitabı açıldığında otomatik olarak yenilenen bir ayar seçebilirsiniz (daha fazla ayrıntı için bkz. İpucu 1).

Yöntem 3: Çalışma kitabındaki tüm pivot tabloları isteğe bağlı olarak güncellemek için VBA kodunu kullanın. Bu yaklaşım, Workbook nesnesinin RefreshAll yöntemini kullanır. Bu tekniği kullanmak için yeni bir modül oluşturun ve aşağıdaki kodu girin:

Sub Refresh_All ()

ThisWorkbook.RefreshAll

İpucu 3. Öğeleri rastgele sırayla sıralayın

İncirde. Şekil 2, PivotTable'daki bölgelerin varsayılan görüntü sırasını gösterir. Bölgeler alfabetik sıraya göre sıralanır: Batı, Kuzey, Ortabatı ve Güney. Kurumsal kurallarınız önce Batı bölgesini ve ardından Ortabatı, Kuzey ve Güney bölgelerini görüntülemenizi gerektiriyorsa, manuel bir sıralama yapın. Midwest'i C4 hücresine girin ve tuşuna basın Giriş... Bölgelerin sıralama düzeni değişecektir.

İpucu 4: PivotTable'ı Sabit Kodlu Değerlere Dönüştürme

Bir pivot tablo oluşturmanın amacı, verileri özetlemek ve uygun format... PivotTable için orijinal veriler ayrı olarak depolanır ve bu da biraz "ek yük" oluşturur. Pivot tabloyu değerlere dönüştürmek, içinde elde edilen sonuçları orijinal verilere veya pivot tablo önbelleğine erişmeden kullanmanıza olanak tanır. Pivot tablonun nasıl dönüştürüldüğü, tablonun tamamının mı yoksa yalnızca bir kısmının mı etkileneceğine bağlıdır.

PivotTable'ın bir bölümünü dönüştürmek için şu adımları izleyin:

1. Kopyalanan PivotTable verilerini seçin, sağ tıklayın ve seçin Kopyala (veya klavyenizde Ctrl + C yazın).
2. Çalışma sayfasında herhangi bir yere sağ tıklayın ve komutu seçin Yapıştırmak (veya Ctrl + V yazın).

Tüm PivotTable'ı dönüştürmeniz gerekiyorsa şu adımları izleyin:

1. Tüm PivotTable'ı seçin, sağ tıklayın ve seçin Kopyala... PivotTable, FİLTRELER alanını içermiyorsa, PivotTable alanını seçmek için Ctrl + Shift + * klavye kısayolunu kullanabilirsiniz.
2. Sayfada herhangi bir yere sağ tıklayın ve içerik menüsünden bir seçenek seçin Özel yapıştır.
3. Bir seçenek seçin Değerler ve tıkla TAMAM MI.

Çevrimdışı veri kümesinde gerekli olmadıklarından PivotTable'ı dönüştürmeden önce alt toplamları silmek iyi bir fikirdir. Tüm ara toplamları silmek için Tasarım menüsü -\u003e Alt toplamlar -\u003e Alt toplamları gösterme seçeneğine gidin. Belirli alt toplamları silmek için toplamların hesaplandığı hücreye sağ tıklayın. Bağlam menüsünde öğeyi seçin Alan parametreleri ve iletişim kutusunda Alan parametreleri kısımda Sonuç radyo düğmesini seç Değil... Düğmeye tıkladıktan sonra TAMAM MI alt toplamlar silinecek.

İpucu 5. SATIR alanlarındaki boş hücreleri doldurma

PivotTable'ı dönüştürdükten sonra, sayfa yalnızca değerleri değil, PivotTable'ın tüm veri yapısını da görüntüler. Örneğin, Şekil 1'de gösterilen veriler. 3, tablo düzenine sahip bir pivot tablodan türetilmiştir.

Şekil: 3. Bu dönüştürülmüş pivot tabloyu sol taraftaki boş hücreleri doldurmadan kullanmak sorunludur

Alanların Bölge ve Satış pazarı Bu verilerin bir pivot tablonun SATIRLAR alanında bulunduğu zamanki satır yapısını korur. Excel 2013, hızlı yol HAT alanındaki hücreleri değerlerle doldurmak. Pivot tablo alanını tıklayın ve ardından menüye gidin Yapıcı -> Rapor düzeni -> (şek. 4). Ardından, pivot tabloyu değerlere dönüştürebilirsiniz, bu da size boşluk içermeyen bir veri tablosu verecektir.

Şekil: 4. Komutu uyguladıktan sonra Tüm öğe etiketlerini tekrarlayın tüm boş hücreler doldurulur

İpucu 6: PivotTable'daki sayısal alanları sıralama

Çok sayıda veri öğesi içeren alanları sıralarken ve derecelendirirken, analiz edilen veri öğesinin sayısal sırasını belirlemek her zaman kolay değildir. Ayrıca, pivot tablo değerlere dönüştürülürse, her bir veri öğesine bir tamsayı alanında görüntülenen bir sayısal sıra atamak, oluşturulan veri kümesinin analizini büyük ölçüde kolaylaştıracaktır. Şekil 2'de gösterilene benzer bir pivot tablo açın. 5. Lütfen aynı göstergenin - Alana göre miktar Satış hacmi - iki kez görüntülenir. Hesaplamanın ikinci örneğini sağ tıklayın ve seçin Ek hesaplamalar -> Maksimumdan minimuma sıralama (şekil 6.)

Bir derece oluşturduktan sonra, alan etiketlerini ve biçimlendirmeyi özelleştirebilirsiniz (Şekil 14.9). Sonuç güzel dereceli bir rapor olacak.

7. İpucu: PivotTable raporunun boyutunu küçültün

PivotTable raporu oluşturduğunuzda, Excel verilerin anlık görüntüsünü alır ve PivotTable önbelleğinde depolar. Pivot tablo önbelleği, erişimi hızlandırmak için veri kaynağının bir kopyasını depolayan özel bir bellek alanıdır. Başka bir deyişle, Excel verilerin bir kopyasını oluşturur ve ardından çalışma kitabıyla ilişkili bir önbellekte depolar. Pivot tablo önbelleği, iş akışı optimizasyonu sağlar. Alanların düzenini değiştirmek, yeni alanlar eklemek veya öğeleri gizlemek gibi PivotTable'da yapılan tüm değişiklikler daha hızlıdır ve sistem kaynağı gereksinimleri çok daha mütevazıdır. PivotTable Önbelleğinin ana dezavantajı, bir PivotTable'ın sıfırdan oluşturulduğu her seferinde çalışma kitabı dosyasının boyutunu neredeyse iki katına çıkarmasıdır.

Orijinal verileri silin.Çalışma kitabı orijinal bir veri kümesi ve bir pivot tablo içeriyorsa, dosya boyutu iki katına çıkar. Bu nedenle, orijinal verileri güvenle silebilirsiniz ve bu, pivot tablonuzun işlevselliğini hiç etkilemeyecektir. Orijinal verileri sildikten sonra, çalışma kitabı dosyasının sıkıştırılmış sürümünü kaydetmeyi unutmayın. Orijinal verileri sildikten sonra, pivot tabloyu her zamanki gibi kullanabilirsiniz. Tek sorun, orijinal verilerin eksikliğinden dolayı pivot tablonun güncellenememesidir. Arka plan verilerine ihtiyacınız varsa, genel toplamlar alanında bir satır ve bir sütunun kesişimine çift tıklayın (Şekil 7'de bu, B18 hücresidir). Bunu yaparken Excel, pivot tablo önbelleğinin içeriğini yeni bir çalışma sayfasına döker.

İpucu 8: Otomatik Olarak Genişletilebilir Bir Veri Aralığı Oluşturun

Elbette, PivotTable raporlarını günlük olarak güncellemeniz gereken durumlarla tekrar tekrar karşılaşmışsınızdır. Bu, çoğunlukla veri kaynağına sürekli olarak yeni kayıtlar eklendiğinde gereklidir. Bu gibi durumlarda, yeni kayıtlar yeni PivotTable'a eklenmeden önce önceden kullanılan aralığı yeniden tanımlamanız gerekecektir. Pivot tablo için orijinal veri aralığını yeniden tanımlamak kolaydır, ancak bunu sık sık yaptığınızda oldukça sıkıcı hale gelir.

Sorunun çözümü, orijinal veri aralığını pivot tablo oluşturulmadan önce bir tabloya dönüştürmektir. Sayesinde excel elektronik tabloları İçindeki veri miktarına bağlı olarak otomatik olarak genişleyebilen veya daralabilen adlandırılmış bir aralık oluşturabilirsiniz. Veri kümesindeki değişiklikleri izlemek için herhangi bir bileşeni, grafiği, PivotTable'ı veya formülü bir aralığa da bağlayabilirsiniz.

Açıklanan tekniği uygulamak için kaynak verileri seçin ve ardından sekmede bulunan tablo simgesine tıklayın. Ekle (şek. 8) veya Ctrl + T (İngilizce T) tuşlarına basın. Tıklayın TAMAM MI açılan pencerede. Pivot tablodaki kaynak veri aralığını yeniden tanımlamanız gerekmese de, pivot tablodaki aralığa kaynak veri eklerken yine de düğmeyi tıklamanız gerekeceğini unutmayın. Yenile.

9. İpucu: Normal tabloları bir pivot tabloyla karşılaştırın

İki farklı tabloyu karşılaştırıyorsanız, size çok zaman kazandıracak bir pivot tablo kullanmak uygundur. 2011 ve 2012 için müşteri ayrıntılarını gösteren iki tablonuz olduğunu varsayalım (Şekil 9). Bu tabloların küçük boyutları burada yalnızca örnek olarak verilmiştir. Pratikte çok daha büyük tablolar kullanılır.

Karşılaştırma, pivot tablonun oluşturulduğu tek bir tablo oluşturur. Bu tablolarla ilişkili verileri etiketlemek için bir yolunuz olduğundan emin olun. Bu örnekte, bu sütunu kullanır Mali yıl (şek. 10). İki tabloyu birleştirdikten sonra, yeni bir PivotTable oluşturmak için ortaya çıkan birleşik veri kümesini kullanın. PivotTable'ı, PivotTable Sütunu alanı tablo etiketi (tablonun kaynağını belirten tanımlayıcı) olarak kullanılacak şekilde biçimlendirin. Şek. 11, yıllar sütun alanındadır ve müşteri detayları satır alanındadır. Veri alanı, her müşteri için satış hacimlerini içerir.

İpucu 10: Bir pivot tablonun otomatik filtrelenmesi

Bildiğiniz gibi, Otomatik Filtreleri PivotTable'larda kullanamazsınız. Ancak, PivotTable'da Otomatik Filtreleri etkinleştirmenin bir yolu vardır. Bu tekniğin arkasındaki ilke, fare işaretçisini PivotTable'ın son başlığının (Şekil 12'deki D3 hücresi) sağına yerleştirmek, ardından şeride gidip komutu seçmektir. Veri -> Filtrele... Şu andan itibaren, pivot tablonuzda bir otomatik filtre belirecek! Örneğin, ortalamanın üzerinde işlem oranına sahip tüm müşterileri seçebilirsiniz. Otomatik Filtreler, PivotTable'a fazladan bir analiz katmanı ekler.

İpucu 11: Pivot tablolarda görüntülenen veri kümelerini dönüştürün

PivotTable'a dönüştürülen orijinal veriler için en iyi düzen, tablo düzenidir. Bu düzen türü şu özelliklere sahiptir: boş satır veya sütun yoktur, her sütunun bir başlığı vardır, her alanın her satırda bir değeri vardır ve sütunlar, yinelenen veri grupları içermez. Uygulamada, genellikle Şekil 1'de gösterilene benzeyen veri kümeleri vardır. 13. Gördüğünüz gibi, ay adları tablonun üst kısmında bir satırda görüntülenir ve sütun etiketlerinin ve gerçek verilerin ikili işlevini yerine getirir. Böyle bir tablodan oluşturulan bir pivot tabloda, bu, her biri ayrı bir ayı temsil eden 12 alanın yönetilmesi ile sonuçlanır.

Bu sorunu ortadan kaldırmak için ara adım olarak birkaç konsolide aralık içeren bir pivot tablo kullanabilirsiniz (ayrıntılara bakın). Matris stiline sahip bir veri kümesini, pivot tablolar oluşturmak için daha uygun bir veri kümesine dönüştürmek için aşağıdaki adımları izleyin.

Adım 1. Sütunlu olmayan tüm alanları tek bir sütunda birleştirin.Birden çok birleştirilmiş aralık içeren pivot tablolar oluşturmak için tek bir boyut sütunu oluşturun. Bu örnekte, ay alanıyla ilgili olmayan her şey bir boyut olarak kabul edilir. Bu nedenle alanlar Satış pazarı ve Servis açıklaması tek sütun halinde birleştirilmelidir. Alanları tek bir sütunda birleştirmek için, iki alanı ayırıcı olarak noktalı virgül kullanarak birleştiren bir formül girmeniz yeterlidir. Yeni sütuna bir ad verin. Girilen formül, formül çubuğunda görüntülenir (Şek. 14).

Şekil: 14. Sütun birleştirme sonucu Satış pazarı ve Servis açıklaması

Birleştirilmiş sütunu oluşturduktan sonra formülleri değerlere dönüştürün. Bunu yapmak için, yeni oluşturulan sütunu seçin, Ctrl + C tuşlarına basın ve ardından komutu çalıştırın. Yapıştırmak -> Özel yapıştır -> Değerler... Sütunlar artık kaldırılabilir Satış pazarı ve Servis açıklaması (şek. 15).

Şekil: 15. Sütunlar kaldırıldı Satış pazarı ve Servis açıklaması

Adım 2. Birden çok konsolidasyon aralığı olan bir pivot tablo oluşturun.Şimdi, Excel'in önceki sürümlerinden birçok kullanıcının aşina olduğu PivotTable ve Grafik Sihirbazı'nı aramanız gerekiyor. Bu sihirbazı çalıştırmak için Alt + D + P tuşlarına basın. Ne yazık ki, bu klavye kısayolu Excel 2013'ün İngilizce sürümü içindir. Rusça sürümde, Alt + D + N klavye kısayoluna karşılık gelir. Ama benim bilmediğim nedenlerle işe yaramıyor. Ancak, eski güzel PivotTable sihirbazını Hızlı Erişim Araç Çubuğu'na getirebilirsiniz, bkz. Sihirbazı başlattıktan sonra radyo düğmesini seçin Birden çok konsolidasyon aralığında... Tıklayın Daha ileri... Anahtarı ayarlayın Sayfa alanları oluşturun ve tıkla Daha ileri... Çalışma aralığını belirleyin ve tıklayın Bitti (ayrıntılara bakınız). Bir pivot tablo oluşturacaksınız (Şekil 16).

Adım 3. Genel toplam satırında bir satır ve bir sütunun kesişimini çift tıklayın.Bu noktada, neredeyse yararsız olan birkaç konsolidasyon aralığı içeren bir pivot tablonuz (Şekil 16) olacaktır. Satırın ve genel toplam sütununun kesişme noktasındaki hücreyi seçin ve üzerine çift tıklayın (bizim örneğimizde bu N88 hücresidir). Şekil 2'de gösterilene benzer bir yapıya sahip yeni bir sayfa alacaksınız. 17. Aslında, bu sayfa orijinal verilerin aktarılmış bir sürümüdür.

Adım 4. Satır sütununu ayrı alanlara bölme.Sütunu bölmeye devam ediyor Hat ayrı alanlara (orijinal yapıya geri dönün). Sütundan hemen sonra boş bir sütun ekleyin Hat... A sütununu vurgulayın ve ardından şerit sekmesine gidin Veri ve düğmeye tıklayın Sütun Metni... Ekranda bir iletişim kutusu belirir Metin Dağıtım Sihirbazı... İlk adımda radyo düğmesini seçin Sınırlandırılmışve İleri düğmesine tıklayın. Sonraki adımda radyo düğmesini seçin noktalı virgül ve tıkla Bitti... Metni biçimlendirin, bir başlık ekleyin ve orijinal verileri Ctrl + T tuşlarına basarak bir tabloya dönüştürün (Şekil 18).

Şekil: 18. Bu veri kümesi, bir pivot tablo oluşturmak için idealdir (Şekil 13 ile karşılaştırın)

İpucu 12: Bir pivot tabloya iki sayı biçimi ekleyin

Şimdi, normalleştirilmiş bir veri kümesinin, analizi kolay bir pivot tablo oluşturmayı zorlaştırdığı bir durumu düşünün. Şekil 2'de bir örnek gösterilmiştir. 19, her satış alanı için iki farklı gösterge içeren bir tablodur. Metriği tanımlayan sütun D'ye dikkat edin.

Bu tablo oldukça iyi bir biçimlendirme örneği olsa da, o kadar da iyi değil. Lütfen bazı ölçümlerin sayısal biçimde gösterilmesi gerektiğini, bazılarının ise yüzde olarak görüntülenmesi gerektiğini unutmayın. Ancak kaynak veritabanında alan Değer Double tipindedir. Bir veri kümesinden bir pivot tablo oluştururken, aynı alana iki farklı sayı biçimi atayamazsınız Değer... Burada basit bir pratik kural vardır: bir alan, bir sayı biçimine karşılık gelir. Yüzde biçimi atanmış bir alana bir sayı biçimi atamaya çalışmak, yüzde değerlerinin yüzde işaretiyle biten normal sayılara dönüşmesine neden olur (Şekil 20).

Bu sorunu çözmek için, 1,5'ten büyük herhangi bir değeri sayı olarak biçimlendiren özel bir sayı biçimi kullanılır. Değer 1,5'ten küçükse yüzde olarak biçimlendirilir. İletişim kutusunda Hücre biçimi sekme seç (tüm biçimler) ve sahada Bir tür aşağıdaki biçimlendirme dizesini girin (şek. 21): [\u003e \u003d 1,5] $ # ## 0; [<1,5]0,0%

Şekil: 21. 1,5'ten küçük sayıların yüzde olarak biçimlendirildiği özel bir sayı biçimi uygulayın

Sonuç, Şekil 1'de gösterilmiştir. 22. Gördüğünüz gibi, artık her gösterge doğru şekilde biçimlendirilmiştir. Tabii ki, bu ipucundaki tarif evrensel değil. Aksine, deney yapılacak yönü gösterir.

İpucu 13: Pivot tablo için bir sıklık dağılımı oluşturun

Excel işlevini kullanarak daha önce frekans dağılımları oluşturduysanız SıklıkMuhtemelen bunun çok zor bir görev olduğunu biliyorsunuzdur. Dahası, veri aralıklarını değiştirdikten sonra her şeyin yeniden başlaması gerekir. Bu bölümde, basit bir pivot tablo kullanarak basit frekans dağılımlarının nasıl oluşturulacağını öğreneceksiniz. İlk olarak, satır alanındaki verilerle bir pivot tablo oluşturun. Şek. 2'ye dikkat edin. 23, alan satır alanında Ses satış.

Satırlar alanındaki herhangi bir değere sağ tıklayın ve içerik menüsünden seçeneği seçin Grup... İletişim kutusunda Gruplama (Şekil 24) frekans dağılımının başlangıcını, sonunu ve adımını belirleyen parametrelerin değerlerini belirler. Tamam'ı tıklayın.

Şekil: 24. İletişim kutusunda Gruplama frekans dağılım parametrelerini ayarlayın

Pivot tabloya bir alan eklerseniz Müşteri (Şekil 25), müşteri işlemlerinin sipariş büyüklüğüne göre (dolar cinsinden) frekans dağılımını alıyoruz.

Şekil: 25. Artık müşteri işlemlerinin sipariş büyüklüğüne göre (dolar cinsinden) dağıtımı emrinizde.

Bu tekniğin avantajı, PivotTable rapor filtresinin, verileri diğer sütunlara göre etkileşimli olarak filtrelemek için kullanılabilmesidir. Bölge ve Satış pazarı... Kullanıcı ayrıca, satır alanındaki herhangi bir sayıya sağ tıklayıp ardından bir seçenek belirleyerek frekans dağıtım aralıklarını hızlı bir şekilde ayarlama yeteneğine de sahiptir. Grup... Sunumun netliği için bir pivot grafik eklenebilir (Şekil 26).

İpucu 14. Bir veri kümesini çalışma kitabının sayfalarına dağıtmak için bir pivot tablo kullanma

Analistler genellikle her bölge, satış alanı, yönetici vb. İçin farklı PivotTable raporları oluşturmalıdır. Bu görev genellikle, pivot tablonun yeni bir sayfaya kopyalanması ve ardından uygun bölge ve yöneticiyi yansıtacak şekilde filtre alanının değiştirilmesi için uzun bir süreci içerir. Bu işlem manuel olarak yapılır ve her analiz türü için tekrarlanır. Ancak genel olarak, tek tek pivot tabloların oluşturulmasını Excel'e aktarabilirsiniz. Parametreyi uygulamanın bir sonucu olarak filtre alanları alanındaki her bir öğe için otomatik olarak ayrı bir pivot tablo oluşturulur. Bu özelliği kullanmak için, bir filtre alanı içeren bir pivot tablo oluşturmanız yeterlidir (Şekil 27). İmleci pivot tabloda ve sekmede herhangi bir yere yerleştirin Analiz takım grubunda Pivot tablo açılır menüye tıklayın Seçenekler (şek. 28). Ardından düğmeye tıklayın Rapor Filtresi Sayfalarını Göster.

Şekil: 28. düğmesini tıklayın Rapor Filtresi Sayfalarını Göster

Görünen iletişim kutusunda (Şekil 29), kendisi için ayrı pivot tabloların oluşturulacağı bir filtre alanı seçebilirsiniz. Uygun filtre alanını seçin ve tıklayın TAMAM MI.

Şekil: 29. İletişim kutusu Rapor Filtresi Sayfalarını Görüntüleme

Filtre alanının her bir öğesi için, ayrı bir sayfaya yerleştirilmiş bir pivot tablo oluşturulacaktır (Şekil 30). Sayfa sekmelerinin, filtre alanındaki öğelerle aynı şekilde adlandırıldığını unutmayın. Unutmayın ki parametre Filtre sayfalarını göster alanları tek tek filtrelemek için uygulanabilir.

İpucu 15: Bir veri kümesini ayrı çalışma kitaplarına dağıtmak için bir pivot tablo kullanma

14. ipucunda, ayırmak için özel bir seçenek kullandık özet tablolar satış pazarlarına göre çalışma kitabının farklı sayfalarında. Eğer ayrılman gerekiyorsa ilk veri farklı pazarlar için ayrı kitaplarda küçük bir VBA kodu kullanabilirsiniz. İlk olarak, filtrelemek istediğiniz alanı filtre alanları alanına yerleştirin. Alanı yerleştirin Satış hacmi değer aralığına (Şekil 31). Önerilen VBA kodu sırayla her FİLTRE öğesini seçer ve işlevi çağırır Detayları gösteryeni bir veri sayfası oluşturmak. Bu sayfa daha sonra yeni bir çalışma kitabına kaydedilir

Kod VBA.

Alt Patlatma Tablosu ()

Dim PvtItem As PivotItem

PivotTable Olarak Dim PvtTable

Dim strfield PivotField olarak

Değişkenleri betiğe göre değiştirin

ConststrFieldName \u003d "Pazar Yeri" '<—Изменение имени поля

Const strTriggerRange \u003d "A4" '<—Изменение диапазона триггера

Pivot tablonun adını değiştirin (gerekirse)

SetPvtTable \u003d ActiveSheet.PivotTables ("PivotTable1") '<—Изменение названия сводной

"Seçili alanın her bir öğesi arasında geçiş yap

PvtTable.PivotFields (strFieldName) .PivotItems içindeki Her PvtItem için

PvtTable.PivotFields (strFieldName) .CurrentPage \u003d PvtItem.Name

Aralık (strTriggerRange) .ShowDetail \u003d True

Geçici bir sayfaya bir isim atamak

ActiveSheet.Name \u003d "TempSheet"

Verileri yeni bir çalışma kitabına kopyalama ve geçici bir sayfayı silme

ActiveSheet.Cells.Copy

ActiveSheet.Paste

Cells.EntireColumn.AutoFit

Application.DisplayAlerts \u003d Yanlış

ActiveWorkbook.SaveAs _

Dosya adı: \u003d ThisWorkbook.Path & "\\" & PvtItem.Name & ".xlsx"

ActiveWorkbook.Close

E-Tablolar ("Tempsheet"). Sil

Application.DisplayAlerts \u003d Doğru

Bu kodu yeni bir VBA modülüne girin. Aşağıdaki sabitlerin ve değişkenlerin değerlerini kontrol edin ve gerekirse değiştirin:

• Const strFieldName. Verileri ayırmak için kullanılan alanın adı. Başka bir deyişle, PivotTable'ın filtre / sayfalar alanına uyan bir alandır.
• Const strTriggerRange. PivotTable'ın veri alanından tek bir sayıyı depolayan tetikleyici hücre. Bizim durumumuzda tetikleyici hücre A4'tür (bkz. Şekil 31).

VBA kodunun yürütülmesinin bir sonucu olarak, her satış alanı için veriler ayrı bir çalışma kitabına kaydedilecektir.

Jelen'in kitabına dayanan not, Alexander. ... Bölüm 14.

Okumanızı öneririz

Ofis programları

Ağartılmış duvarlar neden rüya görür?

Tabletler

Neden bir rüyada küfür etmeyi hayal ettin?

Akıllı telefonlar

Akrep burç taşı

Windows talimatları

İmparator: Bir durumda ve bir sorudaki anlam

Ofis programları

Koldaki bileziğin hayal ettiği şeyin rüya yorumu

Android talimatları

Şifacı Akulina'nın rüya yorumu Straw bir rüyada ne anlama geliyor?