Excel özet raporu

  VB projesi ile çalışmak (11)
   Koşullu biçimlendirme (5)
  Listeler ve Aralıklar (5)
  Makrolar (VBA prosedürleri) (62)
  Diğer (37)

Özet Tablolara Genel Bakış

   Video eğitiminde kullanılan kaynak dosyayı indirin:

  (30.9 KiB, 651 indirilen)

Pivot tablo oluşturmada ilk fırsatın ortaya çıkmasına rağmen Excel 5.0(1993’te)Şimdi bile, birkaç Excel kullanıcısı problemleri çözmek için pivot tabloları kullanıyor. Birisi pivot tabloların çok karmaşık bir şey olduğunu düşünüyor, birileri onları duymamıştı bile. Bu bölümde (Özet Tablolar)  Okuyucuları olmadığına ikna etmeye çalışacağım. Aslında özet tablolarda zor bir şey yoktur. Ancak, bilgi analizinin faydaları sadece paha biçilmezdir.

Neden konsolide olanlara ihtiyacımız var? Excel'de, iş iki kategoriye ayrılabilir: analiz (hesaplama) ve veri formatlama. Hesaplamalar ve analizlerle, mevcut verilere dayanarak bazı göstergeler almak istiyorum. Biçimlendirme, renk içeren hücrelerin doldurulması değil, veri tablolarının sunumunun görünümüdür. Bütün bunlar formüller ile yapılabilir. Altı ay boyunca tüm şubelerin satışıyla ilgili ilk günlük veriler olduğunu varsayalım. Bu verilerden, her bir şube bağlamında ve şubelere göre bir raporun oluşturulması gerekmektedir. Her yarım yıl için her şube için artı özet raporları. Ve şimdi formüller gibi nasıl görüneceğini hayal edelim:
  - ilk ayların listesini almanız gerekiyor;
  - sonra iştirakler;
  - ayrıca, kaynak tablodan yardım ve benzerleri ile veri toplamak için gerekli olan, balık masalarıyla levhalar oluşturun.
  Uygun deneyimle, 15-20 dakikalarla buluşabilirsiniz. Bunu konsolide kullanarak iki dakika içinde yapılabilir.

Pivot Tablo, verilerin ve sonraki analizlerin benzersiz bir sunumunu oluşturmak için kullanılan bir Excel aracıdır. Pivot tablo, iyi oluşturulmuş bir kaynak veri tablosu temelinde oluşturulabilir:

• veri tablosu birleştirilmiş hücreler içermemelidir
• tamamen boş satır ve boş sütun içermemelidir
• her sütun aynı türden (metin, tarih veya sayı) veri içermelidir.
• her sütun benzersiz, özlü ve bilgilendirici bir başlık içermelidir
• sütunlar bir satırda olmalı ve boş ve birleştirilmiş hücreler içermemelidir

Pivot tablo verileri, çeşitli analitik raporlar elde etmek için gruplanabilir, sıralanabilir, filtrelenebilir ve değiştirilebilir. Önemli olan, pivot tabloların, başlangıç ​​tablosunu temel olarak, özet tablosunun oluşturulduğu herhangi bir şekilde değiştirmemesidir. Verileri özette yer alan yerlerde değiştirmek ve rapordan herhangi bir şekilde hariç tutmak mümkündür - orijinal veriler oldukları gibi kalacaktır.
  Ancak kaynak verileri değiştirirseniz, veri değişimi pivot tablolarının içine otomatik olarak yansıtılmayacaktır - bunun için pivot tablo raporunu zorla güncellemeniz gerekecektir:

Pivot tablonun herhangi bir hücresini seçin → Sağ fare tuşu → Yenile  veya sekme Veri (Data) → Tümünü Yenile (Yenile) → Yenile (Yenile).

KONSOLİDE TABLO OLUŞTURMA

1. Kaynak tablodaki herhangi bir hücreyi seçin
2. ilave Insert (Insert) → Grup Tablosu (Tablo) → PivotTable
3. İletişim kutusunda Özet Tablo Oluştur  veri aralığı seçiminin doğruluğunu kontrol edin (veya yeni bir veri kaynağı kurun)Pivot Tablosunun yerini belirleyin:
   • Yeni bir sayfada (Yeni Çalışma Sayfası)
   • Mevcut bir sayfada (Mevcut Çalışma Sayfası)
4. itmek tamam

DÖRDÜNCÜ ALANLARIN KONSOLİDE TABLOSU
   Veri alanı birincil özet alanıdır.   Hesaplamaların yapıldığı tablo. Sayısal alanlar için temel toplamlar içerir. Veri alanında aynı alanı koyabilirsiniz ancak farklı hesaplamalar yapabilirsiniz. (örneğin, alandaki bir Tutar, alandaki başka bir Miktar).
  Veri alanının temel hesaplama işlevleri:

 Sum (Toplam)
   Sayım (Sayı)
   Ortalama
   Maksimum (Maks)
   Minimum (Min)
   Ürün

Satır alanı - pivot tablonun sol kısmındaki başlıkları içerir ve eklenen alanın benzersiz değerlerini gösterir.
   Sütun alanı - pivot tablonun sütun başlıklarından oluşur.
   Sayfa ve filtre alanı isteğe bağlı bir alandır. Pivot tablonun üst kısmında bulunur ve içine yerleştirilen alanlara göre filtreleme fonksiyonları gerçekleştirir.

Bir pivot tablo kullanırken, kaynak verilerindeki değerleri değiştirirken değerlerini otomatik olarak güncellemediğini dikkate almalısınız. Güncellemeyi yapmak için, pivot tablonun herhangi bir hücresini seçmelisiniz → Sağ fare tuşu → Yenile  veya sekme Veri (Veri) → Yenile (Yenile). Bu özet orijinal verilere doğrudan başvuru içermiyor olmasından kaynaklanmaktadır ve yeterince hızlı veri işleme pivotu izin önbellekte depolar.

Makale yardımcı oldu mu? Bağlantıyı arkadaşlarınızla paylaşın!    Video eğiticileri

Excel, en karmaşık hesaplama hesaplarını gerçekleştirmenizi, bilgiyi sistemleştirmenizi ve analitik hesaplamalar yapmanızı sağlayan güçlü bir ofis uygulamasıdır. Ne yazık ki, birçok kullanıcı, Excel'in inceliklerini anlamadan, sadece hesap makinesi gibi basit bir hesaplama aracı olarak kullanıyor.

Kısa bir sürede, sayısal göstergeler eşliğinde sunulan bilgiler analiz edilebilir, bunlara dayanan etkili yönetim kararları verilebilir, Excel'deki pivot tablosuna yardımcı olur ve ileri düzeydeki kullanıcıların önerilerini okuyarak kolayca anlaşılabilir hale getirilebilir.

Bir Excel tablosu veya başka bir veritabanı olabilen varolan bir tablo temel alınarak bir pivot tablo oluşturulur.

Kullanıcı ilk dersi tamamlamaya karar verdiyse, Excel'de bir pivot tablo nasıl oluşturulur, o zaman bu ofis uygulaması tarafından oluşturulan tablo sürümü üzerinde pratik yapmak daha iyidir. Excel'de pivot tabloların nasıl yapılacağının temellerini öğrendikten sonra, göstergeler tamamen farklı uygulamalarla oluşturulan belgelerden yüklenerek elde edildiğinde daha gelişmiş seçeneklere geçebilirsiniz.

Temel gereksinimleri

Zorunlu olan bir dizi gereklilik vardır, aksi halde özet tablo kullanıcının dayanaklarını yerine getirmeyecektir.

Ana gereklilik, bütün verilerin bir tablo şeklinde girilmesi gerektiğidir, bunun üst kısmında elbette bir şapka oluşur (sütunların isimlerini gösterir).

Ayrıca, boş satırlar ve sütunlar tamamen hariç tutulmalıdır. Tablonun dışında, kullanılan alana bitişik olan satırlarda veya sütunlarda listelenen herhangi bir nota ve yabancı kayıt olmamalıdır.

Tüm tablo hücrelerinin de girilen veriye sahip olması istenir, mümkünse, boş hücrelerden kaçınılmalıdır, çünkü bu durum sistemleşmeyi ve sonraki bilgi analizini olumsuz yönde etkileyebilir.

Genellikle Excel'de, kullanıcılar birden çok hücreyi birleştirir. Özet raporlar oluşturmanız gerekiyorsa, böyle bir birleşmeyi önlemek zorunda kalacaksınız. Kullanıcının görüşüne göre, bu durumda önemli olmayan satır veya sütunların gizlenmesi kabul edilemez.

Oluşturma talimatları

Kullanıcı yukarıdaki tüm şartları yerine getirdikten sonra başka eylemExcel'de bir özet tablosunun nasıl yapıldığını anlamaya izin verir.

Öncelikle, tabular başlığının hemen altında bulunan ve onu aktif hale getiren ilk hücreyi bulmanız gerekir. Bunu yapmak için, fare imlecini bu hücreye koymanız ve sol tuşa tıklamanız gerekir.

Ardından, ana menüde "Ekle" sekmesine gidin, ardından alt menüsü görünecek ve aralarında "Pivot Tablo" öğesinin adı bulunan bir simge olacaktır. Üzerine tıklandığında, özet raporların oluşturulma ustası aktif hale getirildi, bu da bir tablo analitik raporunun yapılması çok daha kolay olacak.

Görünen iletişim kutusunda, e-tablo düzenleyicisi analiz edilecek aralığı sizden isteyecektir. Çoğu zaman, sihirbaz bağımsız olarak doğru bir şekilde belirler, dolayısıyla hiçbir değişiklik gerekmeyecektir.

Ayrıca kullanıcının bir özet raporu oluşturmak istediği yeri belirtmeniz gerekir:

• ana göstergelerin bulunduğu aynı sayfada;
• yeni bir sayfada.

Yeni bir sayfaya dönersek, kullanıcı tablo için bir alanın yanı sıra geçerli alanların bir listesini bulacaktır. Hangi spesifik göstergelerin analiz edileceğine karar verildikten sonra, kullanıcı seçilen alanları “Filtre”, “Sütun” alanlarında fareyle sürüklemelidir. Bu şekilde özet raporun sütunlarını oluşturmak mümkün olacaktır.

Ayrıca, gerekli alanları fareyle sürükleyerek çizgiler aynı şekilde çizilir. Eğer birkaç çizgi varsa, hangisinin üstünde ve altta olması gerektiğini belirlemek önemlidir. Hatları önce aynı sırayla düzenleyebilir ve ne kadar uygun olduğunu, özet bilgilerinin ne kadar iyi görüntüleneceğini gözlemleyebilirsiniz. Bu konumda verileri analiz etmek zorsa, o zaman her şey kolayca düzeltilir - sadece belli bir alanı fareyle basılı tutun ve yukarı veya aşağı doğru hareket ettirin.

Sonuç olarak, “Değerler” özet raporunun son alanını oluşturmaya devam etmektedir. Analiz için anahtar olan alan, örneğin toplam kütle, toplam miktar, ona aktarılır.

Her şey talimatlara göre kesinlikle yapılırsa, bu aşamada bir analitik rapor görüntülenecektir. Hatalar tespit edildiğinde, alanları değiştirmek, verileri güncellemek yeterlidir ve tablo otomatik olarak yeniden oluşturulur.

Özet raporları kullanma

Kullanıcı sıklıkla analitik faaliyetlere başvurmak gerektiğinde bir pivot tablo yapmaya karar verir. Bu bağlamda, tablonun verileri belirtilen kriterler ile organize etmenize izin veren bir filtre ile donatıldığını bilmek faydalı olacaktır.

Filtrenin uygulanması

Acemi kullanıcıların Excel'deki bir pivot tablonun nasıl güncelleneceğini ve gruplama verilerine dayanarak nasıl bir sorgulama yapılacağını bilmeleri yararlı olur.

Kullanıcı ana tabloda herhangi bir değişiklik yaptığında, pivot tablodaki verileri yenileme önemlidir. Verileri güncellemek için, ek bir menüyü çağırmak üzere, tablo alanındaki fare ile tıklayın. "Güncelle" öğesini bulmak kolaydır ve otomatik olarak güncellenmesi seçer.

Özet raporlarda bir filtre kullanmak da oldukça önemlidir. Çoğunlukla, çalışanların günlük verileri girilir ve analitik raporlardan sorumlu olan çalışan, çeyrek, yarım yıl için verileri analiz etmelidir.

Filtre bu sorunu çözmeye yardımcı olur. Bir daha herşeyi yapmak gerekli olmayacaktır. Fare ile herhangi bir tarihte (sağ tıklama) tıklamak yeterlidir. içerik menüsü  “Grup” öğesini seçin ve ardından kabul edilebilir gruplama yöntemleri tanımlayabileceğiniz bir iletişim kutusu seçin. Bundan sonra, tablo otomatik olarak yeniden oluşturulacak, büyük ölçüde azaltılacaktır, bununla bağlantılı olarak verilerin analiz edilmesi daha kolay olacaktır.

Bu nedenle, basit bir tablo üzerinde önceden çalışıyorsanız, tüm gereksinimleri karşılayıp doğru verileri girerseniz, bir özet tablosu oluşturmak kolaydır. Böyle becerilere hakim olan kullanıcı, çok hızlı bir şekilde faydaları hissedecektir, çünkü veri analizi, mutlak doğrulukla birlikte kısa bir sürede gerçekleştirilecektir.

Verileri hızlı bir şekilde analiz etme yeteneği genellikle daha verimli iş kararları almanıza yardımcı olur. Ancak bazen nereden başlanması gerektiği belli değil, özellikle de büyük miktarda bilgi olduğunda. Pivot tablolar, verilerin derlenmesini, analizini, çalışmasını ve sunulmasını basitleştirir. Sadece birkaç eylemle oluşturulabilir ve sonuçları nasıl görüntülemek istediğinize bağlı olarak hızlıca özelleştirilebilirler. Ayrıca, pivot tabloları temel alan pivot grafikler oluşturabilir, bunlar değiştiğinde otomatik olarak güncellenecektir.

Örneğin, burada aile masraflarının basit bir listesi ve bundan oluşturulan bir özet tablosu var:

Ama özet diyagramı:

  Not:  Bu makaledeki ekran görüntüleri Excel 2016'da alındı. Farklı bir sürüm kullanıyorsanız, arayüz biraz farklı olabilir, ancak işlevler aynı olacaktır.

Başlamadan önce

Bir pivot tablo oluşturma

Pivot tablolarla çalışma konusunda yeterli tecrübeniz yoksa veya nereden başlayacağınızı bilmiyorsanız, kullanmak daha iyidir. önerilen pivot tablo. Bu durumda, Excel uygun düzeni belirler, verileri özet tablosundaki en uygun alanlarla karşılaştırır. Bu, daha fazla deney için bir başlangıç ​​noktası elde etmenizi sağlar. Önerilen özet tablosunu oluşturduktan sonra, farklı yönlendirme seçeneklerini keşfedebilir ve istenen sonuçları elde etmek için alanların sırasını değiştirebilirsiniz. Önerilen pivot tabloları ilk olarak Excel 2013'te görüntülendi, bu nedenle daha önceki bir sürümünüz varsa, aşağıda açıklandığı gibi bir pivot tablo oluşturun.

Pivot tablo alanlarının bir listesiyle çalışın

Bölgede Alan adı  Üst kısımda, pivot tabloya eklemek istediğiniz alanların onay kutularını seçin. Varsayılan olarak, alana sayısal olmayan alanlar eklenir. hatlar, tarih ve saat değeri alanları - alana sütunlarve sayısal alanlar - bölgedeki değerler. Öğeleri pivot tablonun herhangi bir alanına elle de sürükleyebilirsiniz. Öğe artık gerekli değilse, alan listesinin dışına sürükleyin veya kutusunun işaretini kaldırın. Elemanları yeniden düzenleme olanağı, pivot tablonun özelliklerinden biridir ve bu sayede görünümünü hızlı ve kolay bir şekilde değiştirebilirsiniz.

Pivot tablosundaki değerler

Pivot tablolarının güncellenmesi

Kaynağa yeni veri eklerken, temel olarak oluşturulan tüm özet tablolarını güncellemek gerekir. Bir pivot tabloyu güncellemek için şunları yapabilirsiniz: tıklamak sağ tıklama  fare  menzilinin herhangi bir yerinde ve bir takım seç güncelleştirme. Birden fazla pivot tablo varsa, önce herhangi bir pivot tabloda herhangi bir hücreyi seçin ve sonra bant  seçmek Pivot tablolarla çalışma > analizi, grupta veritık  düğmenin altındaki ok güncelleştirme  ve komutu seç Tümünü yenile.

Pivot tabloyu sil

Artık bir PivotTable'a ihtiyacınız yoksa, bunu vurgulayın ve SİL. Bu diğer verileri ve pivot tabloları veya grafikleri etkilemez. Pivot tablo, gerekli verinin artık mevcut olmadığı ayrı bir sayfada bulunuyorsa, bu sayfayı sil  . Yani pivot tablodan kurtulmanın en kolay yolu.

Ek bilgi

Her zaman Excel Tech Community uzmanına bir soru sorabilir, Yanıt topluluğunda yardım isteyebilir veya web sitesinde yeni bir özellik veya iyileştirme önerebilirsiniz.