Microsoft Excel'de Özet Tablolarla Çalışma

Özet Tablolar, Microsoft Excel'in en güçlü özelliklerinden biridir. Yalnızca birkaç fare tıklamasıyla büyük miktarlarda verilerin analiz edilmesini ve özetlenmesini sağlar. Bu makalede PivotTable'ları inceliyoruz, ne olduklarını anlıyoruz ve onları nasıl oluşturacağınızı ve kişiselleştireceğinizi öğreniyoruz.

Not: Bu makale Excel 2010 (Beta) kullanılarak yazılmıştır. Özet Tablo kavramı yıllar içinde çok az değişti, ancak bir tane oluşturma yöntemi Excel'in hemen hemen her yinelemesinde değişti. 2010 olmayan bir Excel sürümü kullanıyorsanız, bu makalede gördüğünüzden farklı ekranlar bekleyin..

Biraz tarih

Elektronik tablo programlarının ilk günlerinde, Lotus 1-2-3, hüküm sürmeye hükmetti. Hakimiyeti o kadar tamamlandı ki, insanlar Microsoft'un Lotus ile rekabet edebilmek için kendi elektronik tablo yazılımlarını (Excel) geliştirmeye zahmet etmenin zaman kaybı olduğunu düşünüyorlardı. 2010'a dönüşte ileriye doğru ilerleyin ve Excel'in elektronik tablo pazarındaki hakimiyeti Lotus'un sahip olduğundan daha yüksektir, oysa hala Lotus 1-2-3 çalıştıran kullanıcı sayısı sıfıra yaklaşıyor. Bu nasıl oldu? Böylesine dramatik bir servet dönüşüne neden olan şey?

Endüstri analistleri bunu iki faktöre indirdi: Birincisi, Lotus, “Windows” adlı bu yeni ve yeni GUI platformunun, asla çıkamayacak bir geçiş olayı olduğuna karar verdi. Lotus 1-2-3'ün (birkaç yıl boyunca) bir Windows sürümü oluşturmayı reddetti ve bu da yazılımın DOS sürümlerinin ihtiyaç duyabilecekleri herkes olduğunu öngördü. Microsoft, tabii ki, yalnızca Windows için Excel'i geliştirdi. İkincisi, Microsoft Excel için Lotus'un 1-2-3'te sağlamadığı bir özellik geliştirdi, yani PivotTable. Excel'e özgü PivotTables özelliği, insanların sahip olmadığı bir programa (1-2-3) bağlı kalmak yerine yepyeni bir yazılım paketini (Excel) öğrenmeye istekli olacakları için şaşırtıcı derecede faydalı görünüyordu. Bu özellik, Windows'un başarısının yanlış değerlendirilmesinin yanı sıra, Lotus 1-2-3 için ölüm zili ve Microsoft Excel'in başarısının başlangıcı oldu.

Özet Tabloları Anlamak

Peki, Özet Tablo nedir, tam olarak?

Basitçe söylemek gerekirse, Özet Tablo, söz konusu verilerin kolay analizini sağlamak için oluşturulan bazı verilerin özetidir. Ancak, el ile oluşturulan bir özetin aksine, Excel Özet Tabloları etkileşimlidir. Bir tane oluşturduktan sonra, umduğunuz verilerinizle ilgili tam bir öngörü sunmuyorsa kolayca değiştirebilirsiniz. Birkaç tıklamayla özet “döndürülebilir” - sütun başlıkları satır başlıkları olacak şekilde döndürülebilir ve bunun tersi de geçerlidir. Yapılabilecek daha çok şey var. PivotTable'ların tüm özelliklerini tanımlamaya çalışmak yerine, sadece bunları göstereceğiz…

Özet Tablo kullanarak analiz ettiğiniz veriler yalnızca herhangi veri - olması gerekir çiğ önceden işlenmemiş (özetlenmemiş) veriler - tipik olarak bir tür liste. Buna bir örnek, son altı aydır bir şirketteki satış işlemlerinin listesi olabilir..

Aşağıda gösterilen verileri inceleyin:

Bunun olduğuna dikkat edin değil işlenmemiş veri. Aslında, zaten bir çeşit özeti. B3 hücresinde 30.000 $ 'ı görebiliriz (görünüşe göre James Cook’un Ocak ayı satışlarının toplamı). Peki ham veriler nerede? 30.000 Dolar rakamına nasıl ulaştık? Bu rakamın oluşturulduğu satış işlemlerinin orijinal listesi nereden? Bir yerde, birisinin son altı aydır tüm satış işlemlerini yukarıda gördüğümüz özete harcaması zorluğuna girmiş olduğu açık. Sence bu ne kadar sürdü? Bir saat? On?

Büyük olasılıkla, evet. Görüyorsunuz, yukarıdaki elektronik tablo aslında değil Özet Tablo. Başka bir yerde depolanan ham verilerden elle yaratıldı ve derlenmesi için birkaç saat sürdü. Ancak, bu tam olarak bir özeti could PivotTables kullanılarak yaratılmış, bu durumda sadece birkaç saniye sürecektir. Bakalım nasıl…

Orijinal satış işlemleri listesini takip edersek, şöyle görünebilir:

Excel'in Özet Tablo özelliğini kullanarak, yalnızca birkaç fare tıklamasıyla birkaç saniyede, yukarıdakiyle benzer aylık bir satış özeti oluşturabileceğimizi öğrenince şaşırabilirsiniz. Bunu yapabiliriz - ve çok daha fazlası!

Özet Tablo Oluşturma

Öncelikle, Excel'deki bir çalışma sayfasında bazı ham verilerinizin olduğundan emin olun. Finansal işlemlerin listesi tipiktir, ancak hemen hemen her şeyin bir listesi olabilir: Çalışan iletişim bilgileri, CD koleksiyonunuz veya şirketinizin araç filosunun yakıt tüketim rakamları.

Böylece Excel'i başlatıyoruz… ve böyle bir liste yüklüyoruz…

Listeyi Excel'de açtıktan sonra, PivotTable'ı oluşturmaya hazırız.

Listedeki tek bir hücreye tıklayın:

Sonra, Ekle sekmesini tıklayın Pivot tablo simgesi:

Özet Tablo Oluştur size iki soru soran bir kutu belirir: Yeni Özet Tablo'nuz hangi verilere dayanmalı ve nerede oluşturulmalı? Listedeki bir hücreyi zaten tıklattığımız için (yukarıdaki adımda), bu hücreyi çevreleyen tüm liste bizim için zaten seçildi ($ A $ 1: $ G $ 88 üzerinde Ödemeler bu örnekte Başka bir çalışma sayfasının herhangi bir bölgesinde veya hatta Access veritabanı tablosu veya hatta bir MS-SQL Server veritabanı tablosu gibi bazı harici veri kaynaklarında bir liste seçebileceğimizi unutmayın. Yeni Özet Tablo'muzun bir bilgisayarda oluşturulmasını isteyip istemediğimizi de seçmemiz gerekir. yeni çalışma sayfasında veya mevcut bir. Bu örnekte biz seçeceğiz yeni bir:

Yeni çalışma sayfası bizim için ve bu çalışma sayfasında boş bir PivotTable oluşturuldu:

Başka bir kutu da görünür: Özet Tablo Alan Listesi. Bu alan listesi, PivotTable (yukarıdaki) içindeki herhangi bir hücreye tıkladığımızda gösterilecektir:

Kutunun üst kısmındaki alanların listesi aslında orijinal ham veri çalışma sayfasından sütun başlıklarının toplanmasıdır. Ekranın alt kısmındaki dört boş kutu, Özet Verilerimizin ham verileri özetlemesini istediğimiz yolu seçmemize izin veriyor. Şimdiye kadar, bu kutularda hiçbir şey yok, bu yüzden PivotTable boş. Tek yapmamız gereken alanları yukarıdaki listeden aşağı sürükleyip aşağıya bırakmak. Bir PivotTable daha sonra otomatik olarak talimatlarımızla eşleşecek şekilde oluşturulur. Yanlış yaparsak, alanları yalnızca geldikleri yere geri sürükleyip / veya sürüklemeliyiz. yeni onları değiştirmek için alanları aşağı.

Değerler kutu tartışmasız dört kişiden en önemlisidir. Bu kutuya sürüklenen alan, bir şekilde özetlenmesi gereken verileri gösterir (toplayarak, ortalamalarını alarak, maksimum, minimum, vb. Bulma). Neredeyse her zaman sayısal veri. Örnek verilerimizde bu kutu için mükemmel bir aday “Tutar” alanı / sütundur. Bu alanı sürükleyelim. Değerler Kutu:

(A) Alanlar listesindeki “Tutar” alanının şimdi işaretlendiğine ve “Tutarların Toplamı” kısmının eklendiğine dikkat edin. Değerler miktar sütununun toplandığını belirten.

Özet Tablo'nun kendisini incelersek, ham veri çalışma sayfasındaki tüm “Tutar” değerlerinin toplamını gerçekten buluruz:

İlk PivotTable'ımızı yarattık! Kullanışlı, ancak özellikle etkileyici değil. Muhtemelen verilerimizle ilgili verilerden biraz daha fazla bilgiye ihtiyacımız var..

Örnek verilerimize bakarsak, bu toplamı bölmek için makul bir şekilde kullanabileceğimiz bir veya daha fazla sütun başlığı tanımlamamız gerekir. Örneğin, elimizdeki verilerin bir özetini görmek istediğimize karar verebiliriz. satır başlığı Şirketimizdeki farklı satıcıların her biri için ve her biri için toplam. Bunu başarmak için tek yapmamız gereken “Satış Görevlisi” alanını Satır Etiketleri Kutu:

şimdi, Sonunda, işler ilginçleşmeye başlar! Özet Tablo'mız şekillenmeye başladı… .

Birkaç tıklamayla, manuel olarak yapılması uzun zaman alacak bir masa oluşturduk.

Peki başka ne yapabiliriz? Bir anlamda Özet Tablo'muz tamamlandı. Kaynak verilerimizin yararlı bir özetini oluşturduk. Önemli şeyler zaten öğrenildi! Makalenin geri kalanı için, daha karmaşık PivotTable'ların oluşturulabileceği bazı yolları ve bu PivotTable'ların özelleştirilebileceği yolları inceleyeceğiz..

İlk önce bir iki-boyutsal tablo Bunu bir sütun başlığı olarak “Payment Method” kullanarak yapalım. Sadece “Ödeme Yöntemi” başlığına sürükleyin. Sütun Etiketleri Kutu:

Bu gibi görünüyor:

Almaya başlamak çok güzel!

Bir yapalım üç-boyutsal tablo Böyle bir masa muhtemelen neye benzeyebilir? İyi, görelim bakalım…

“Paket” sütununu / başlığını Rapor Filtresi Kutu:

Nerede bittiğine dikkat et… .

Bu, hangi “tatil paketi” nin satın alındığına bağlı olarak raporumuzu filtrelememizi sağlar. Örneğin, satış görevlisinin dağılımını vs için ödeme yöntemini görebiliriz. herşey Paketler veya birkaç tıklama ile “Sunseekers” paketi için aynı dökümü göstermek üzere değiştirin:

Ve böylece, doğru şekilde düşünürseniz, Özet Tablo'muz şimdi üç boyutludur. Özelleştirmeye devam edelim…

Eğer çıkıyorsa, sadece görmek istediğimizi söyleyin. çek ve kredi kartı İşlemler (yani nakit işlem yok), o zaman "Nakit" öğesinin sütun başlıklarından seçimini kaldırabiliriz. Yanındaki açılır oku tıklayın Sütun Etiketleri, ve "Nakit" işaretini kaldırın:

Neye benzediğini görelim… Gördüğünüz gibi, “Nakit” gitti.

Biçimlendirme

Bu açıkçası çok güçlü bir sistem, ancak şu ana kadar sonuçlar çok sade ve sıkıcı görünüyor. Bir başlangıç için, topladığımız rakamlar dolar gibi görünmüyor - sadece eski sayılar. Bunu düzeltelim.

Bu gibi durumlarda yapmaya alışkın olduğumuz şeyi yapmak ve tüm tabloyu (veya tüm çalışma sayfasını) seçmek ve biçimlendirmeyi tamamlamak için araç çubuğundaki standart sayı biçimlendirme düğmelerini kullanmak bir cazip olabilir. Bu yaklaşımla ilgili sorun, gelecekte PivotTable'ın yapısını değiştirirseniz (% 99'dur), bu sayı biçimlerinin kaybolmasıdır. Onları (yarı) kalıcı hale getirecek bir yola ihtiyacımız var.

İlk önce, “Toplam Tutar” girişini buluyoruz. Değerler kutu ve tıklayın. Bir menü belirir. Biz seçiyoruz Değer Alanı Ayarları… menüden:

Değer Alanı Ayarları kutu belirir.

Tıkla Sayı biçimi düğme ve standart Hücreleri Biçimlendir kutusu görüntülenir:

İtibaren Kategori listeyi seçin Muhasebe, ve ondalık basamak sayısını 0 olarak bırakın. tamam Özet Tablo'ya geri dönmek için birkaç kez…

Gördüğünüz gibi, sayılar doğru bir şekilde dolar tutarı olarak biçimlendirilmiş..

Biçimlendirme konusuyken, tüm PivotTable'ı biçimlendirelim. Bunu yapmanın birkaç yolu var. Basit bir tane kullanalım…

Tıkla Özet Tablo Araçları / Tasarım sekmesi:

Ardından, sağ alt köşesindeki oku aşağıya doğru bırakın. Özet Tablo Stilleri Yerleşik stillerin geniş bir koleksiyonunu görmek için:

Çekici olanlardan birini seçin ve PivotTable'ınızdaki sonuca bakın:

Diğer seçenekler

Tarihlerle de çalışabiliriz. Şimdi, genellikle, başladığımız gibi bir işlem listesinde çok, çok tarih vardır. Ancak Excel veri öğelerini gün, hafta, ay, yıl vb. İle birlikte gruplama seçeneği sunar. Bunun nasıl yapıldığını görelim..

Öncelikle, “Ödeme Yöntemi” sütununu Sütun Etiketleri kutu (yalnızca alan listesine geri sürükleyin) ve “Tarih Kitap” sütunuyla değiştirin:

Gördüğünüz gibi, bu PivotTable'ımızı anında yararsız hale getiriyor, işlemin gerçekleştiği her tarih için bize bir sütun veriyor - çok geniş bir tablo!

Bunu düzeltmek için herhangi bir tarihe sağ tıklayın ve Grup ... bağlam menüsünden:

Gruplandırma kutusu belirir. Biz seçiyoruz aylar ve Tamam'ı tıklayın:

İşte bu kadar! bir çok daha kullanışlı masa:

(Bu arada, bu tablo, bu makalenin başında gösterilenle neredeyse aynıdır - elle oluşturulan orijinal satış özeti.)

Dikkat edilmesi gereken bir diğer önemli nokta, birden fazla satır başlığına (veya sütun başlıklarına) sahip olmanız olabilir:

… Ki buna benziyor… .

Benzer bir şeyi sütun başlıklarıyla (veya rapor filtrelerini bile) yapabilirsiniz..

İşleri tekrar basit tutarak, nasıl çizileceğini görelim ortalama Toplanan değerler yerine değerler.

İlk önce, “Tutar Toplamı” üzerine tıklayın ve Değer Alanı Ayarları… Görünen içerik menüsünden:

İçinde Değer alanını özetle listedeki Değer Alanı Ayarları kutu seç Ortalama:

Biz buradayken, hadi değişelim Özel ad, “Ortalama Miktar” dan biraz daha kısa olan bir şeye. “Avg” gibi bir şey yazın:

Click tamam, ve neye benzediğini görün. Tüm değerlerin toplanan toplamlardan ortalamalara değiştiğine ve tablo başlığının (sol üst hücre) “Ortalama” olarak değiştiğine dikkat edin:

İstersek, aynı PivotTable'da toplamları, ortalamaları ve sayıları (sayıları = kaç satış olduğunu) bile alabiliriz!

İşte böyle bir şeyi yerine getirme adımları (boş bir PivotTable'dan başlayarak):

“Satış Görevlisini” içine sürükleyin Sütun Etiketleri
“Miktar” alanını aşağı sürükleyin Değerler kutu üç kere
İlk “Tutar” alanı için özel adını “Toplam” olarak ve sayı biçimini olarak değiştirin. Muhasebe (0 ondalık basamak)
İkinci “Tutar” alanı için özel adını “Ortalama” olarak değiştirir, işlevi Ortalama ve sayı biçimi Muhasebe (0 ondalık basamak)
Üçüncü "Miktar" alanı için adını "Sayı" olarak ve işlev olarak değiştirin. saymak
Otomatik olarak oluşturulan sürükle alandan Sütun Etiketleri için Satır Etiketleri

İşte neyle sonuçlandığımız:

Toplam, ortalama ve aynı Özet Tablodaki sayım!

Sonuç

Microsoft Excel tarafından oluşturulan Özet Tablolar için çok, çok daha fazla özellik ve seçenek var - böyle bir makalede listelenemeyecek kadar çok. Özet Tablo potansiyelini tam olarak karşılamak için küçük bir kitap (veya büyük bir web sitesi) gerekir. Cesur ve / veya meraklı okuyucular PivotTable'ları kolayca kolayca keşfedebilir: Hemen hemen her şeyi sağ tıklayın ve hangi seçeneklerin size uygun olduğunu görün. Ayrıca iki şerit sekmesi var: Özet Tablo Araçları / Seçenekler ve dizayn. Hata yaparsanız önemli değil - Özet Tablo'yu silmek ve yeniden başlatmak kolaydır - Lotus 1-2-3 eski DOS kullanıcıları.

Office 2007'de çalışıyorsanız, Excel 2007'de PivotTable'ın nasıl oluşturulacağı hakkındaki makalemize göz atın..

Özet Tablo becerilerinizi geliştirmek için indirebileceğiniz Excel çalışma kitabını ekledik. 97'den itibaren Excel'in tüm sürümleriyle çalışmalıdır..

Uygulamamızı indirin Excel Çalışma Kitabı