İçerik
Pivot tablolar Excel'deki en güçlü araçlardan biridir. Sadece birkaç fare tıklamasıyla büyük miktarda verinin çeşitli özetlerini analiz etmenize ve özetlemenize izin verirler. Bu yazıda, pivot tablolarla tanışacağız, ne olduklarını anlayacağız, nasıl oluşturulacağını ve özelleştirileceğini öğreneceğiz.
Bu makale Excel 2010 kullanılarak yazılmıştır. PivotTable kavramı yıllar içinde pek değişmemiştir, ancak bunları oluşturma şekliniz Excel'in her yeni sürümünde biraz farklıdır. Excel'in 2010 değil bir sürümünüz varsa, bu makaledeki ekran görüntülerinin ekranınızda gördüğünüzden farklı olacağına hazırlıklı olun.
Biraz tarih
Elektronik tablo yazılımının ilk günlerinde, Lotus 1-2-3 kural topu. Egemenliği o kadar eksiksizdi ki, Microsoft'un Lotus'a alternatif olarak kendi yazılımını (Excel) geliştirme çabaları zaman kaybı gibi görünüyordu. Şimdi hızlı ileri 2010'a! Excel, elektronik tablolara, Lotus kodunun tarihinde hiç olmadığı kadar hakimdir ve Lotus'u hâlâ kullanan kişi sayısı sıfıra yakındır. Bu nasıl olabilir? Olayların bu kadar dramatik bir şekilde değişmesinin nedeni neydi?
Analistler iki ana faktörü tanımlar:
- İlk olarak Lotus, Windows adlı bu yeni moda GUI platformunun uzun sürmeyecek geçici bir heves olduğuna karar verdi. Lotus 1-2-3'ün bir Windows sürümünü oluşturmayı reddettiler (ancak yalnızca birkaç yıllığına), yazılımlarının DOS sürümünün tüm tüketicilerin ihtiyaç duyacağı bir şey olacağını tahmin ettiler. Microsoft, Excel'i doğal olarak özellikle Windows için geliştirdi.
- İkincisi, Microsoft, Lotus 1-2-3'te bulunmayan PivotTables adlı Excel'de bir araç tanıttı. Excel'e özel PivotTable'lar, o kadar ezici bir şekilde yararlı olduklarını kanıtladılar ki, insanlar, bunlara sahip olmayan Lotus 1-2-3 ile devam etmek yerine, yeni Excel yazılım paketine bağlı kalma eğilimindeydiler.
PivotTable'lar, genel olarak Windows'un başarısını hafife almanın yanı sıra, Lotus 1-2-3 için ölüm marşını oynadı ve Microsoft Excel'in başarısını başlattı.
Pivot tablolar nelerdir?
Peki, PivotTable'ların ne olduğunu karakterize etmenin en iyi yolu nedir?
Basit bir ifadeyle, pivot tablolar, bu verilerin analizini kolaylaştırmak için oluşturulan bazı verilerin özetleridir. El ile oluşturulan toplamların aksine, Excel PivotTable'ları etkileşimlidir. Oluşturulduktan sonra, umduğunuz resmi vermezlerse bunları kolayca değiştirebilirsiniz. Yalnızca birkaç fare tıklamasıyla toplamlar çevrilebilir, böylece sütun başlıkları satır başlıklarına dönüşür ve bunun tersi de geçerlidir. Pivot tablolarla birçok şey yapabilirsiniz. Pivot tabloların tüm özelliklerini kelimelerle anlatmaya çalışmak yerine pratikte göstermek daha kolay…
PivotTable'larla analiz ettiğiniz veriler rastgele olamaz. Bir tür liste gibi ham ham veriler olmalıdır. Örneğin, şirketin son altı ayda yaptığı satışların bir listesi olabilir.
Aşağıdaki şekilde gösterilen verilere bakın:
Bunun daha önce özetlendiği için ham ham veri olmadığını unutmayın. B3 hücresinde 30000 $ görüyoruz, bu muhtemelen James Cook'un Ocak ayında yaptığı toplam sonuçtur. O zaman orijinal veriler nerede? 30000 dolar rakamı nereden geldi? Bu aylık toplamın elde edildiği orijinal satış listesi nerede? Birinin son altı aydaki tüm satış verilerini organize etme ve sıralama ve onu gördüğümüz toplamlar tablosuna dönüştürme konusunda harika bir iş çıkardığı açıktır. Sizce ne kadar sürdü? Saat? Saat on?
Gerçek şu ki, yukarıdaki tablo bir pivot tablo değildir. Başka bir yerde depolanan ham verilerden el yapımıydı ve işlenmesi en az birkaç saat sürdü. Sadece birkaç saniye içinde pivot tablolar kullanılarak böyle bir özet tablo oluşturulabilir. Nasıl olduğunu anlayalım…
Orijinal satış listesine geri dönersek, şöyle görünür:
Bu işlem listesinden, pivot tabloların yardımıyla ve sadece birkaç saniye içinde, yukarıda analiz ettiğimiz Excel'de aylık bir satış raporu oluşturabilmemize şaşırabilirsiniz. Evet, bunu ve daha fazlasını yapabiliriz!
Pivot tablo nasıl oluşturulur?
İlk olarak, bir Excel sayfasında bazı kaynak verileriniz olduğundan emin olun. Finansal işlemlerin listesi, meydana gelen en tipik olanıdır. Aslında, herhangi bir şeyin listesi olabilir: çalışan iletişim bilgileri, bir CD koleksiyonu veya şirketinizin yakıt tüketimi verileri.
Böylece Excel'i başlatıyoruz… ve böyle bir liste yüklüyoruz…
Bu listeyi Excel'de açtıktan sonra bir pivot tablo oluşturmaya başlayabiliriz.
Bu listeden herhangi bir hücre seçin:
Daha sonra sekmede sokma (Ekle) komut seç Pivot tablo (Pivot tablo):
Bir iletişim kutusu belirecektir PivotTable Oluştur (Bir pivot tablo oluşturma) sizin için iki soruyla:
- Yeni bir pivot tablo oluşturmak için hangi veriler kullanılmalı?
- Pivot tabloyu nereye koymalı?
Önceki adımda liste hücrelerinden birini zaten seçtiğimiz için, bir pivot tablo oluşturmak için tüm liste otomatik olarak seçilecektir. Farklı bir aralık, farklı bir tablo ve hatta Access veya MS-SQL veritabanı tablosu gibi bazı harici veri kaynakları seçebileceğimizi unutmayın. Ek olarak, yeni pivot tablonun nereye yerleştirileceğini seçmemiz gerekiyor: yeni bir sayfaya veya mevcut olanlardan birine. Bu örnekte, seçeneği seçeceğiz - Yeni Çalışma Sayfası (yeni bir sayfaya):
Excel yeni bir sayfa oluşturacak ve üzerine boş bir pivot tablo yerleştirecektir:
Pivot tablodaki herhangi bir hücreye tıkladığımızda başka bir iletişim kutusu belirecektir: Özet Tablo Alan Listesi (Pivot tablo alanları).
İletişim kutusunun üst kısmındaki alanların listesi, orijinal listedeki tüm başlıkların bir listesidir. Ekranın altındaki dört boş alan, PivotTable'a verileri nasıl özetlemek istediğinizi söylemenizi sağlar. Bu alanlar boş olduğu sürece tabloda da bir şey yok. Tek yapmamız gereken başlıkları üst alandan alttaki boş alanlara sürüklemek. Aynı zamanda, talimatlarımıza göre otomatik olarak bir pivot tablo oluşturulur. Bir hata yaparsak, alt alandan başlıkları kaldırabilir veya diğerlerini değiştirmek için sürükleyebiliriz.
Semt Değerler (Anlamlar) muhtemelen dördünün en önemlisidir. Bu alana hangi başlığın yerleştirileceği, hangi verilerin özetleneceğini belirler (toplam, ortalama, maksimum, minimum vb.) Bunlar neredeyse her zaman sayısal değerlerdir. Bu alandaki bir yer için mükemmel bir aday, başlığı altındaki verilerdir. Ücret (Maliyet) orijinal masamızın. Bu başlığı alana sürükleyin Değerler (Değerler):
Lütfen dikkat edin, başlık Ücret şimdi bir onay işaretiyle işaretlenmiştir ve alanda Değerler (Değerler) bir giriş belirdi Tutar Toplamı (Tutar alanı Tutar), sütunun Ücret özetlenmiş.
Pivot tablonun kendisine bakarsak, sütundaki tüm değerlerin toplamını göreceğiz. Ücret orijinal masa.
Böylece ilk pivot tablomuz oluşturuldu! Kullanışlı, ancak özellikle etkileyici değil. Muhtemelen verilerimiz hakkında şu anda sahip olduğumuzdan daha fazla bilgi almak istiyoruz.
Orijinal verilere dönelim ve bu toplamı bölmek için kullanılabilecek bir veya daha fazla sütun belirlemeye çalışalım. Örneğin, her satıcı için toplam satış tutarı ayrı ayrı hesaplanacak şekilde pivot tablomuzu oluşturabiliriz. Şunlar. Şirketteki her bir satış elemanının adı ve toplam satış tutarı ile pivot tablomuza satırlar eklenecektir. Bu sonucu elde etmek için başlığı sürüklemeniz yeterli satis elemani (satış temsilcisi) bölgeye Satır Etiketleri (Teller):
Daha ilginç hale geliyor! PivotTable'ımız şekillenmeye başlıyor…
Faydalarını gördünüz mü? Birkaç tıklamayla, manuel olarak oluşturulması çok uzun sürecek bir tablo oluşturduk.
Başka ne yapabiliriz? Bir anlamda pivot tablomuz hazır. Orijinal verilerin faydalı bir özetini oluşturduk. Önemli bilgiler zaten alındı! Bu makalenin geri kalanında, daha karmaşık PivotTable'lar oluşturmanın bazı yollarına bakacağız ve bunları nasıl özelleştireceğinizi öğreneceğiz.
PivotTable Kurulumu
İlk olarak, iki boyutlu bir pivot tablo oluşturabiliriz. Bunu sütun başlığını kullanarak yapalım Ödeme şekli (Ödeme şekli). Sadece başlığı sürükleyin Ödeme şekli bölgeye Sütun Etiketleri (Sütunlar):
Şu sonucu alıyoruz:
Çok havalı görünüyor!
Şimdi üç boyutlu bir tablo yapalım. Böyle bir masa nasıl olurdu? Bakalım…
Başlığı Sürükle paket (Karmaşık) alana rapor filtreleri (Filtreler):
Nerede olduğuna dikkat edin…
Bu bize raporu “Hangi tatil kompleksi için ödeme yapıldı” temelinde filtreleme fırsatı verir. Örneğin, tüm kompleksler için satıcılara ve ödeme yöntemlerine göre bir döküm görebiliriz veya birkaç fare tıklamasıyla pivot tablonun görünümünü değiştirebilir ve aynı dökümü sadece kompleksi sipariş edenler için gösterebiliriz. güneş arayanlar.
Yani, bunu doğru anladıysanız, pivot tablomuz üç boyutlu olarak adlandırılabilir. Kuruluma devam edelim…
Birdenbire pivot tabloda yalnızca çek ve kredi kartıyla ödemenin (yani nakitsiz ödeme) gösterilmesi gerektiği ortaya çıkarsa, başlığın görüntülenmesini kapatabiliriz. Nakit (Nakit). Bunun için yanında Sütun Etiketleri aşağı oku tıklayın ve açılır menüdeki kutunun işaretini kaldırın Nakit:
Şimdi pivot tablomuzun neye benzediğini görelim. Gördüğünüz gibi, sütun Nakit ondan kayboldu.
Excel'de PivotTable'ları Biçimlendirme
PivotTable'lar açıkçası çok güçlü bir araçtır, ancak şu ana kadar sonuçlar biraz sade ve sıkıcı görünüyor. Örneğin, topladığımız sayılar dolar tutarları gibi görünmüyor - bunlar sadece sayılar. Bunu düzeltelim.
Böyle bir durumda alışık olduğunuz şeyi yapmak ve tüm tabloyu (veya tüm sayfayı) seçmek ve istenen formatı ayarlamak için araç çubuğundaki standart sayı formatlama düğmelerini kullanmak cazip gelebilir. Bu yaklaşımla ilgili sorun, gelecekte pivot tablonun yapısını değiştirirseniz (%99 şansla gerçekleşir), biçimlendirmenin kaybedilmesidir. İhtiyacımız olan şey, onu (neredeyse) kalıcı hale getirmenin bir yolu.
İlk önce, girişi bulalım Tutar Toplamı in Değerler (Değerler) ve üzerine tıklayın. Görünen menüde öğeyi seçin Değer Alanı Ayarları (Değer alanı seçenekleri):
Bir iletişim kutusu belirecektir Değer Alanı Ayarları (Değer alanı seçenekleri).
Düğmeye bas sayı Biçimi (Sayı Biçimi), bir iletişim kutusu açılacaktır. biçim Hücreler (hücre biçimi):
Listeden Kategoriler (Sayı biçimleri) seçin muhasebe (Finansal) ve ondalık basamak sayısını sıfıra ayarlayın. Şimdi birkaç kez basın OKpivot tablomuza geri dönmek için.
Gördüğünüz gibi, sayılar dolar tutarları olarak biçimlendirilmiştir.
Biçimlendirmeyle uğraşırken, tüm PivotTable için biçimi ayarlayalım. Bunu yapmanın birkaç yolu vardır. Daha basit olanı kullanıyoruz…
Tıkla PivotTable Araçları: Tasarım (PivotTable'larla Çalışma: Yapıcı):
Ardından, bölümün sağ alt köşesindeki oka tıklayarak menüyü genişletin Özet Tablo Stilleri (PivotTable Stilleri) satır içi stillerin kapsamlı koleksiyonunu görmek için:
Uygun bir stil seçin ve pivot tablonuzdaki sonuca bakın:
Excel'deki Diğer PivotTable Ayarları
Bazen verileri tarihlere göre filtrelemeniz gerekir. Örneğin, esnaf listemizde çok, çok tarih var. Excel, verileri güne, aya, yıla vb. göre gruplandırmak için bir araç sağlar. Nasıl yapıldığını görelim.
İlk önce girişi kaldırın. Ödeme şekli bölgeden Sütun Etiketleri (Sütunlar). Bunu yapmak için, onu başlıklar listesine geri sürükleyin ve onun yerine başlığı taşıyın. Rezervasyon Tarihi (rezervasyon tarihi):
Gördüğünüz gibi, bu geçici olarak pivot tablomuzu işe yaramaz hale getirdi. Excel, ticaretin yapıldığı her tarih için ayrı bir sütun oluşturdu. Sonuç olarak, çok geniş bir masamız var!
Bunu düzeltmek için herhangi bir tarihe sağ tıklayın ve içerik menüsünden seçin GRUP (Grup):
Gruplandırma iletişim kutusu görünecektir. Biz seciyoruz Aylar (Ay) ve tıklayın OK:
İşte! Bu tablo çok daha kullanışlıdır:
Bu arada, bu tablo, satış toplamlarının manuel olarak derlendiği makalenin başında gösterilen tabloyla neredeyse aynıdır.
Bilmeniz gereken çok önemli bir nokta daha var! Bir değil, birkaç düzey satır (veya sütun) başlığı oluşturabilirsiniz:
… ve bu şekilde görünecek…
Aynısı sütun başlıkları (hatta filtreler) ile de yapılabilir.
Tablonun orijinal formuna dönelim ve toplamlar yerine ortalamaların nasıl görüntüleneceğini görelim.
Başlamak için tıklayın Tutar Toplamı ve görünen menüden seçin Değer Alanı Ayarları (Değer alanı seçenekleri):
Liste Değer alanını şuna göre özetle: (İşlem) iletişim kutusunda Değer Alanı Ayarları (Değer alanı seçenekleri) seçin Ortalama (Ortalama):
Aynı zamanda, buradayken değişelim ÖzelAdı (Özel ad) ile Tutarın Ortalaması (Miktar alanı Tutar) daha kısa bir şeye dönüştürün. Bu alana şöyle bir şey girin Ort:
Basın OK ve ne olduğunu görün. Tüm değerlerin toplamlardan ortalamalara değiştiğini ve tablo başlığının (sol üst hücrede) olarak değiştiğini unutmayın. Ort:
Dilerseniz tek bir pivot tabloda yer alan tutarı, ortalamayı ve adeti (satışları) anında alabilirsiniz.
Boş bir pivot tablodan başlayarak, bunun nasıl yapılacağına ilişkin adım adım bir kılavuz:
- Başlığı Sürükle satis elemani (satış temsilcisi) bölgeye Sütun Etiketleri (Sütunlar).
- Başlığı üç kez sürükleyin Ücret (Maliyet) alana Değerler (Değerler).
- İlk alan için Ücret başlığı şu şekilde değiştir Toplam (Tutar) ve bu alandaki sayı biçimi muhasebe (Parasal). Ondalık basamak sayısı sıfırdır.
- İkinci alan Ücret isim ortalamae, bunun için işlemi ayarla Ortalama (Ortalama) ve bu alandaki sayı biçimi de şu şekilde değişir: muhasebe (Finansal) sıfır ondalık basamaklı.
- Üçüncü alan için Ücret bir başlık ayarla saymak ve onun için bir operasyon - saymak (Miktar)
- içinde Sütun Etiketleri (Sütunlar) alanı otomatik olarak oluşturuldu Σ Değerler (Σ Değerler) – alana sürükleyin Satır Etiketleri (Çizgiler)
İşte bitireceğimiz şey:
Toplam tutar, ortalama değer ve satış sayısı - hepsi tek bir pivot tabloda!
Sonuç
Microsoft Excel'deki pivot tablolar, birçok özellik ve ayar içerir. Bu kadar küçük bir makalede, hepsini kapsayacak kadar yakın bile değiller. Pivot tabloların tüm olanaklarını tam olarak açıklamak için küçük bir kitap veya büyük bir web sitesi gerekir. Cesur ve meraklı okuyucular, pivot tabloları keşfetmeye devam edebilir. Bunu yapmak için, pivot tablonun hemen hemen herhangi bir öğesine sağ tıklayın ve hangi işlevlerin ve ayarların açıldığını görün. Şeritte iki sekme bulacaksınız: PivotTable Araçları: Seçenekler (analiz) ve Dizayn (Yapıcı). Hata yapmaktan korkmayın, PivotTable'ı her zaman silip baştan başlayabilirsiniz. Uzun süredir DOS ve Lotus 1-2-3 kullanıcılarının hiç sahip olmadığı bir fırsatınız var.