Excel'de rapor oluşturma. Raporlama ve veri işlemeyle ilgilenenler için Excel yaşam tüyoları

Excel, karmaşık hesaplamalar yapmanıza, bilgileri düzenlemenize ve analitik hesaplamalar oluşturmanıza olanak tanıyan güçlü bir ofis uygulamasıdır. Ne yazık ki, Excel'in inceliklerini anlamayan birçok kullanıcı, onu yalnızca hesap makinesi gibi basit bir sayma aracı olarak kullanıyor.

Rekor sürede analiz edin Kısa bir zaman Sayısal göstergeler eşliğinde sunulan bilgiler, bunlara dayalı olarak etkili yönetim kararları vermek için kullanılabilir, Excel'deki bir pivot tablo yardımcı olur ve bunun nasıl yapılacağı ileri düzey kullanıcıların önerilerini okuyarak kolayca anlaşılabilir.

Bir pivot tablo, Excel veya başka bir veritabanı ürünü olabilen mevcut bir tablo kullanılarak oluşturulur.

Kullanıcı gitmeye karar verirse başlangıç kursu Excel'de bir pivot tablonun nasıl oluşturulacağı, o zaman bunun tarafından oluşturulan tablo versiyonu üzerinde pratik yapması onun için daha iyidir. ofis uygulaması. Excel'de pivot tabloların nasıl oluşturulacağına ilişkin temel bilgileri mükemmel bir şekilde anladıktan sonra, tamamen farklı uygulamalar tarafından oluşturulan belgelerden indirilerek göstergeler elde edildiğinde daha karmaşık seçeneklere geçebilirsiniz.

Birincil gereksinimler

Karşılanması gereken bir takım gereksinimler vardır, aksi takdirde pivot tablo kullanıcının beklediği görevleri yerine getirmeyecektir.

Temel gereklilik, tüm verilerin bir tablo biçiminde girilmesi gerektiğidir; bunun üstünde elbette bir başlık bulunur (sütunların adlarını gösterir).

Ayrıca boş satır ve sütunların tamamen hariç tutulması gerekir. Kullanılan alana bitişik satır veya sütunlarda tablo dışında herhangi bir not veya gereksiz giriş olmamalıdır.

Tüm tablo hücrelerinin aynı zamanda veri içermesi tavsiye edilir; mümkünse boş hücrelerden kaçınılmalıdır çünkü bu, bilgilerin sistemleştirilmesini ve sonraki analizini olumsuz yönde etkileyebilir.

Genellikle Excel'de kullanıcılar birden çok hücreyi birleştirir. Özet raporlar oluşturmanız gerekiyorsa bu tür toplamalardan kaçınmanız gerekecektir. Bu durumda kullanıcının önemli olmadığına inandığı satır veya sütunların gizlenmesi kabul edilemez.

Oluşturma talimatları

Kullanıcı yukarıdaki gereksinimlerin tümünü karşıladıktan sonra devam edebilirsiniz. daha fazla eylemler Excel'de pivot tablonun nasıl oluşturulacağını anlamanıza olanak tanır.

Öncelikle tablo başlığının hemen altında bulunan ilk hücreyi bulup aktif hale getirmeniz gerekiyor. Bunu yapmak için fare imlecini bu hücrenin üzerine getirip sol tıklamanız gerekir.

Daha sonra ana menüde “Ekle” sekmesine gitmelisiniz, ardından alt menüsü görüntülenecek ve bunların arasında “Özet Tablo” öğesinin adını taşıyan bir simge bulunacaktır. Üzerine tıkladığınızda, tablo şeklinde bir analitik rapor oluşturmanın çok daha kolay olacağı özet raporlar oluşturma sihirbazı etkinleştirilecektir.

Görüntülenen iletişim kutusunda elektronik tablo düzenleyicisi sizden analiz edilecek aralığı belirtmenizi isteyecektir. Çoğu zaman sihirbaz bunu bağımsız olarak doğru şekilde belirler, bu nedenle değişiklik yapmaya gerek yoktur.

Ayrıca kullanıcının özet raporu nerede oluşturmak istediğini de belirtmek gerekir:

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

Yeni bir sayfaya geçtikten sonra kullanıcı, burada tablo için bir alanın yanı sıra geçerli alanların bir listesini bulacaktır. Hangi spesifik göstergelerin analiz edilmesi gerektiğine karar verdikten sonra, kullanıcının seçilen alanları fare ile “Filtre”, “Sütun” alanlarında sürüklemesi gerekir. Bu sayede özet rapor sütunları oluşturabilirsiniz.

Daha sonra fare ile gerekli alanlar sürüklenerek çizgiler tamamen aynı şekilde çizilir. Birkaç çizgi varsa hangisinin üstte, hangisinin altta olması gerektiğini belirlemek önemlidir. Öncelikle satırları aynı sırayla düzenleyerek ne kadar kullanışlı olacağını ve özet bilgilerin ne kadar doğru görüntüleneceğini gözlemleyebilirsiniz. Verileri bu düzenlemeyle analiz etmek zorsa, her şey kolayca düzeltilebilir - fareyle belirli bir alanı basılı tutup yukarı veya aşağı hareket ettirmeniz yeterlidir.

Son olarak geriye özet raporun son alanını “Değerler” yapmak kalıyor. Analiz için anahtar olan bir alan, örneğin toplam kütle, toplam miktar ona aktarılır.

Her şeyi kesinlikle talimatlara göre yaparsanız, bu aşamada bir analitik rapor görüntülenecektir. Hatalar tespit edilirse alanları değiştirin, verileri güncelleyin; tablo otomatik olarak yeniden oluşturulacaktır.

Özet raporları kullanma

Analitik faaliyetlere sıklıkla başvurulması gerekiyorsa kullanıcı bir pivot tablo oluşturmaya karar verir. Bu bakımdan tablonun, verileri belirtilen kriterlere göre sistemleştirmenize olanak tanıyan bir filtreyle donatıldığını bilmek de faydalıdır.

Filtre uygulama

Acemi kullanıcıların Excel'de bir pivot tabloyu nasıl güncelleyeceğini ve verilerin gruplandırılmasına göre nasıl sorgulanacağını bilmesi faydalıdır.

Kullanıcı ana tabloda herhangi bir değişiklik yaptığında pivot tablodaki verilerin güncellenmesi önemlidir. Verileri güncellemek için tablo alanı içinde fareye tıklayıp çağırmalısınız. ekstra menü. İçinde "Güncelleme" öğesini bulmak kolaydır ve onu seçtiğinizde güncelleme otomatik olarak gerçekleşir.

Özet raporlarda filtre uygulamak da oldukça önemli bir işlemdir. Çoğu zaman çalışanların günlük verileri girilir ve analitik raporlardan sorumlu çalışanın verileri çeyrek veya altı ay boyunca analiz etmesi gerekir.

Bir filtre bu sorunun çözülmesine yardımcı olur. Tekrar karmaşık bir şey yapmanıza gerek kalmayacak. Fareyle (sağ tuş) herhangi bir tarihe tıklamanız yeterlidir. içerik menüsü“Grup”u seçin, bu sizin için kabul edilebilir gruplama yöntemlerini belirleyebileceğiniz bir iletişim kutusu açacaktır. Bundan sonra tablo otomatik olarak yeniden oluşturulacak ve boyutu önemli ölçüde küçültülecek, böylece verilerin analizi çok daha kolay hale gelecektir.

Bu nedenle, ilk önce basit bir tablo üzerinde pratik yaparsanız, tüm gereksinimleri takip ederseniz ve doğru verileri girerseniz pivot tablolar oluşturmak zor değildir. Kullanıcı bu becerilerde uzmanlaştıktan sonra, veri analizi kısa bir sürede mutlak doğrulukla gerçekleştirileceğinden, faydaları hızlı bir şekilde deneyimleyecektir.

Excel pivot tablolarıyla farklı alanlarda çalışmanız gerekiyor. Büyük miktarda bilgiyi hızlı bir şekilde işleyebilir, verileri karşılaştırabilir ve gruplandırabilirsiniz. Bu, yöneticilerin, satış elemanlarının, yöneticilerin, pazarlamacıların, sosyologların vb. işini büyük ölçüde kolaylaştırır.

Pivot tablolar, aynı veriler üzerinde hızla farklı raporlar oluşturmanıza olanak tanır. Ayrıca bu raporlar esnek bir şekilde özelleştirilebilir, değiştirilebilir, güncellenebilir ve ayrıntılı olarak detaylandırılabilir.

PivotTable Sihirbazı'nı kullanarak rapor oluşturma

Sahibiz eğitim masası verilerle:

Her satır bize bir işlem hakkında kapsamlı bilgi verir:

satışların hangi mağazada gerçekleştiği;
hangi mal ve ne kadar;
satıcılardan hangisi denedi;
ne zaman (gün, ay).

Eğer bu büyük bir mağaza zinciriyse ve satışlar iyi gidiyorsa, dörtte bir içinde masanın boyutu korkunç hale gelecektir. Yüzlerce satırdaki verileri analiz etmek çok zor olacak. Ve bir raporun derlenmesi bir günden fazla sürecektir. Böyle bir durumda pivot tabloya ihtiyaç vardır.

Pivot Tablo Sihirbazı'nı kullanarak bir rapor oluşturalım. Yeni olarak Excel sürümleri Bazı nedenlerden dolayı ayarların derinliklerinde gizlidir:

"Dosya" - "Seçenekler" - "Panel"i seçin hızlı erişim».
Sol sütundaki açılır listeden: "Şundan bir takım seçin", "Tüm takımlar"ı seçin.
Sol sütunda şuna göre bul: alfabetik sıra ve şunu vurgulayın: “Özet Tablo ve Grafik Sihirbazı.” Sütunlar arasındaki düğmeye tıklayın: Aracın sağ sütuna taşınması için "Ekle" ve Tamam'a tıklayın.

Artık araç hızlı erişim panelindedir, bu da her zaman elinizin altında olduğu anlamına gelir.

İmleci veri tablosunda herhangi bir yere yerleştirin. Artık hızlı erişim panelinde bulunan ilgili araca tıklayarak Pivot Tablo Sihirbazını çağırıyoruz.
İlk adımda pivot tablo oluşturmak için bir veri kaynağı seçiyoruz. Sonrakine tıkla". Birkaç sayfadan özet rapor halinde bilgi toplamak için şunu seçin: “bir listede veya veritabanında Microsoft verileri Excel'de."
İkinci adımda raporun temel alacağı veri aralığını belirliyoruz. Tabloda imleç bulunduğundan aralık otomatik olarak belirlenecektir.
Üçüncü adımda Excel sizden pivot tablonun nereye yerleştirileceğini seçmenizi ister. “Son”a tıkladığınızda düzen açılacaktır.
Raporda görüntülenecek alanları belirlemeniz gerekmektedir. Diyelim ki her satıcının satış tutarlarını bilmek istiyoruz. Kutuları işaretliyoruz ve şunu elde ediyoruz:

Tamamlanan rapor biçimlendirilebilir ve değiştirilebilir.

Excel pivot tablosundaki veriler nasıl güncellenir?

Bu manuel ve otomatik olarak yapılabilir.

İmleci pivot tablonun herhangi bir yerine yerleştirin. Sonuç olarak, “Pivot Tablolarla Çalışmak” sekmesi görünür hale gelir.
“Veri” menüsünde “Güncelle” butonuna (veya ALT+F5 tuş kombinasyonuna) tıklayın.
Tüm raporları güncellemeniz gerekiyorsa Excel çalışma kitabı, “Tümünü güncelle” düğmesini (veya CTRL+ALT+F5 tuş kombinasyonunu) seçin.

Veriler değiştiğinde otomatik güncellemeleri ayarlama:

“Pivot Tablolarla Çalışmak” sekmesinde (rapora tıklamanız gerekir), “Seçenekler” menüsünü seçin.
Açık " Ekstra seçenekler Pivot tablo." Sihirbaz açılır.
"Veri" bölümünde "Dosyayı açarken güncelle" seçeneğinin yanındaki kutuyu işaretleyin.

Artık, verileri değiştirilmiş bir dosyayı her açışınızda, otomatik güncelleme Pivot tablo.

Bazı biçimlendirme sırları

Büyük miktarda veriyi bir raporda özetlediğimizde, sonuç çıkarmak ve bazı kararlar almak için gruplama gerekli olabilir. Diyelim ki bir ay veya üç aylık sonuçları görmemiz gerekiyor.

Excel pivot tablosunda tarihe göre gruplandırma:

Bilginin kaynağı bir veri raporudur.
Tarihe göre gruplandırmaya ihtiyacımız olduğundan karşılık gelen değere sahip herhangi bir hücreyi seçin. Sağ tık.
Açılır menüden "Gruplandırma" seçeneğini seçin. Bunun gibi bir araç açılacaktır:
Excel, "Başlangıç" ve "Bitiş" alanlarına veri aralığının başlangıç ve bitiş tarihlerini otomatik olarak doldurdu. Gruplandırma adımına karar veriyoruz. Örneğimiz için, aylar veya üç aylık dönemler. Aylara odaklanalım.

Aylara göre satış tutarlarını açıkça gösteren bir rapor alıyoruz. Deneyelim ve adımı “Bloklar” olarak ayarlayalım. Sonuç şöyle bir özet tablodur:

Mağaza zincirinin faaliyetlerini analiz etmek için satıcıların adı önemli değilse, üç aylık kârlı bir rapor oluşturabiliriz.

Gruplandırma sonuçlarını kaldırmak için veri hücresine sağ tıklayıp Grubu Çöz'e tıklamanız gerekir. Veya “Yapı” menüsünde bu seçeneği seçin.

Sonuçlarla çalışma

Şöyle bir özet raporumuz var:

Sonuçları aya göre (Gruplandırmaya göre yapılır) ve ürün adlarına göre görebilirsiniz. Raporu çalışmaya daha uygun hale getirelim.

Pivot tablonun en üstüne toplamlar nasıl eklenir:

“Pivot tablolarla çalışma” - “Tasarımcı”.
“Düzen” sekmesinde “Alt Toplamlar”a tıklayın. “Tüm alt toplamları grup başlığında göster”i seçin.
Sonuç aşağıdaki rapor türüdür:

Artık bilgiyi algılamayı zorlaştıran tıkanıklık yok.

Alt toplamlar nasıl silinir? Sadece düzen sekmesinde “Alt toplamları gösterme” seçeneğini seçin:

Ek tutarlar olmadan bir rapor alacağız:

Bilginin ayrıntıları

“Yabancı” tablolar baz alınarak derlenen devasa özet tabloların periyodik olarak detaylandırılması gerekiyor. Belirli bir tutarın nereden geldiğini bilmiyoruz. Excel hücresi. Ancak pivot tabloyu birkaç sayfaya bölüp bölmediğinizi öğrenebilirsiniz.

Mart ayında 23.780 PB tutarında çift kişilik yatak satışı gerçekleşti. Bu rakam nereden geldi? Bu miktarın bulunduğu hücreyi seçin ve sağ tıklayıp seçeneği seçin:
Yeni bir sayfada ürün satış verilerini içeren bir tablo açılacaktır.

Eylemler sekmesindeki Taşı düğmesini seçerek PivotTable'ın tamamını yeni bir sayfaya taşıyabiliriz.

Varsayılan olarak rapora eklediğimiz sütundaki bilgilerin tamamı pivot tabloya yerleştirilir.

Örneğimizde – TÜM ürünler, TÜM tarihler, TÜM tutarlar ve mağazalar. Kullanıcının bazı öğelere ihtiyacı olmayabilir. Sadece raporu karmaşık hale getirirler ve asıl şeye odaklanmanızı engellerler. Gereksiz unsurları kaldıralım.

Bilgi miktarını ayarlayacağımız sütun adının yanındaki oka tıklayın.
Açılır menüden alan adını seçin. Örneğimizde bu, ürün adı veya tarihidir. Adına sadık kalacağız.
Filtreyi değere göre ayarlayın. Tek kişilik yataklara ilişkin bilgileri rapordan hariç tutalım; ürün adının yanındaki onay kutusunu kaldırın.

Tamam'ı tıklayın; pivot tablo değişir.

Otomasyon araçları kullanılmadan büyük hacimli bilgilerin işlenmesi ve karmaşık çok düzeyli raporların oluşturulması oldukça zordur. Excel 2010, tam olarak pivot (çapraz) veri tabloları (Pivot tablo) oluşturarak bu görevleri basitleştirmenize olanak tanıyan bir araçtır.

Excel 2010'daki pivot tablo aşağıdakiler için kullanılır:

büyük bir veri kümesindeki ilişkilerin belirlenmesi;
verileri şuna göre gruplandırma: çeşitli işaretler ve gruplardaki eğilimlerin izlenmesi;
tekrar eden unsurları bulma, detaylandırma vb.;
okunması kolay raporlar oluşturmak en önemli şeydir.

Pivot tablo oluşturmanın iki yolu vardır. Her birine bakalım.

Yöntem 1: Standardı Kullanarak PivotTable'lar Oluşturma Excel aracı 2010 “Özet tablo”

PivotTable raporu oluşturmadan önce veri kaynağı olarak nelerin kullanılacağına karar verelim. Aynı belgede bulunan kaynak seçeneğini ele alalım.

1. Başlamak için raporunuzda kullanmanız gereken öğeleri listeleyen basit bir tablo oluşturun. Üst çizgi sütun başlıkları içermelidir.

2. "Ekle" sekmesini açın ve "Tablolar" bölümünden "Özet Tablo" aracını seçin.

Pivot tabloyla birlikte bir pivot grafik oluşturmanız gerekiyorsa, “Pivot Table” simgesinin sağ alt köşesindeki oka tıklayın ve “Pivot Chart”ı seçin.

3. Açılan Yeni Özet Tablo iletişim kutusunda yeni oluşturulan veri tablosunu veya aralığını seçin. Bunu yapmak için istediğiniz alanı seçin.

Analiz için veri olarak harici bir kaynak belirleyebilirsiniz: uygun alanda radyo düğmesini seçin ve gerekli bağlantı mevcut olanlar listesinden.

5. “Tamam” butonu ile işlemi onayladıktan sonra rapor düzeni oluşturulacak ve açılacaktır. Bunu düşünelim.

Pencerenin sağ yarısında ana yönetim araçlarından oluşan bir panel oluşturulur - "Özet Tablo Alanlarının Listesi". Tüm alanlar (kaynak veri tablosundaki sütun başlıkları) "Raporunuza eklenecek alanları seçin" alanında listelenecektir. Gerekli kutuları işaretleyin; seçilen alanları içeren bir pivot tablo raporu oluşturulacaktır.

Alanların konumu kontrol edilebilir; bunları ilgili pencerelere sürükleyerek satır veya sütun adları haline getirin ve ayrıca bunları sıralayın. uygun sipariş. Uygun alanı Filtre penceresine sürükleyerek öğeleri tek tek filtreleyebilirsiniz. “Değer” penceresi hesaplamaların yapıldığı ve sonuçların özetlendiği alanı içerir.

Raporları düzenlemeye yönelik diğer seçeneklere, Seçenekler ve Tasarım sekmelerindeki PivotTable Araçları menüsünden ulaşılabilir. Bu sekmelerdeki araçların neredeyse her birinde çok sayıda ayar ve ek işlev bulunur.

Yöntem 2: Pivot Tablo ve Grafik Sihirbazı aracını kullanarak Pivot Tablo oluşturma

Bu yöntemi kullanmak için yapmanız gerekenler erişilebilir araç varsayılan olarak şeritte görüntülenmez. “Dosya” sekmesini - “Seçenekler” - “Hızlı Erişim Araç Çubuğu”nu açın. Komutları seçin listesinde Şeritteki Komutlar'ı seçin. Ve aşağıda, komut listesinden “Özet Tablo ve Grafik Sihirbazı” nı seçin. "Ekle" düğmesini tıklayın. Hızlı erişim panelinin üst kısmında sihirbaz simgesi görünecektir.

Excel 2010'daki PivotTable Sihirbazı, Excel 2007'deki benzer araçtan pek farklı değildir. Bunu kullanarak pivot tablolar oluşturmak için aşağıdakileri yapın.

1. Hızlı Erişim panelindeki sihirbaz simgesine tıklayın. İletişim kutusunda, veri kaynakları listesinden istediğiniz öğeyi seçin:

"bir listede veya veritabanında Microsoft Excel" - kaynak, varsa çalışma sayfası veritabanı olacaktır;
“harici bir veri kaynağında” - mevcut olanlardan seçilmesi gereken harici bir veritabanına bağlantı varsa;
“birkaç konsolidasyon aralığında” - farklı kaynaklardan gelen verilerin birleştirilmesi gerekiyorsa;
“başka bir pivot tablodaki veya pivot grafikteki veriler” - kaynak olarak mevcut bir pivot tablo veya grafik alınır.

2. Bundan sonra oluşturulacak rapor türünü seçin - “pivot tablo” veya “pivot grafik (tablolu)”.

Halihazırda mevcut olan kaynak olarak geçerli belge seçilirse basit masa Gelecekteki raporun öğeleriyle kapsama aralığını ayarlayın - imleçle istediğiniz alanı vurgulayın. Ardından, tablonun nereye yerleştirileceğini seçin - yeni bir sayfada mı yoksa geçerli sayfada mı ve "Son"a tıklayın. PivotTable oluşturulacak.
Birkaç kaynaktan gelen verileri birleştirmeniz gerekiyorsa uygun alandaki radyo düğmesini seçin ve rapor türünü seçin. Daha sonra gelecekteki pivot tablonun sayfa alanlarının nasıl oluşturulacağını belirtmeniz gerekecektir: bir alan veya birkaç alan.

“Sayfa Alanları Oluştur”u seçtiğinizde, öncelikle veri kaynağı aralıklarını belirtmeniz gerekecektir: ilk aralığı seçin, “Ekle”ye, ardından bir sonrakine vb. tıklayın.

Kolaylık sağlamak için aralıklara adlar verilebilir. Bunu yapmak için listeden birini seçin ve bunun için oluşturulacak sayfa alanı sayısını belirtin, ardından her alana bir ad (etiket) verin. Bundan sonra bir sonraki aralığı seçin ve bu şekilde devam edin.

Seçerken dış kaynak kullanılan veriler Microsoft uygulaması Sorgu, Excel 2010'a dahildir veya Office verilerine bağlanmak istiyorsanız Veri sekmesi seçeneklerini kullanın.
Belgede zaten bir PivotTable raporu veya PivotChart bulunuyorsa bunları kaynak olarak kullanabilirsiniz. Bunu yapmak için konumlarını belirtmeniz ve istediğiniz veri aralığını seçmeniz yeterlidir; ardından yeni bir pivot tablo oluşturulacaktır.

Malzemeyi beğendin mi?
Paylaşmak:

Lütfen oyla:

5 derecelendirme üzerinden:

Makalenin başına dön Excel 2010'da pivot tablo nasıl oluşturulur?

Şimdi size PowerPivot'ta ilk veri işleme deneyiminizi yaşatacak olan atölyeye başlayalım. Bu örnek, depo verilerini içeren CSV dosyasının 1,8 milyon satırına dayalı bir rapor oluşturacaktır. Aslında kayıt sayısı sınırsız olabilir (1.048.000'e kadar kayda izin veren Excel'in aksine).

Orijinal tablo, demo.csv adlı bir CSV dosyasında bulunan 1,8 milyon kayıt içerir. Not Defteri program penceresinde görüntülenen bu dosyanın içeriğinin bir kısmı Şekil 1'de gösterilmektedir. 10.2. Sütun başlıklarının CSV dosyasının ilk satırında olduğunu unutmayın. PowerPivot eklentisi bunları işlerken sorun yaşamamak için demo dosyasının üst kısmındaki standart olmayan satırları kaldırmanız gerekebileceğini lütfen unutmayın.

Pirinç. 10.2. Bu 1,8 milyon satırlık dosya Excel için çok büyük

Bir metin dosyasını içe aktar

1,8 milyon satır içeren bir dosyayı PowerPivot'a aktarmak için şu adımları izleyin:

Excel 2010 penceresinde PowerPivot sekmesini seçin.
PowerPivot Penceresi simgesine tıklayın. Ekranda PowerPivot uygulama penceresi görünecektir. iki sekme içerir: Ev (Ev) ve Proje (Tasarım). Ana Sayfa sekmesi Şekil 2'de gösterilmektedir. 10.3.
Öncelikle ana tabloyu içe aktarmanız gerekir. Bu tablo, Şekilde gösterilen büyük CSV dosyasından oluşturulmuştur. 10.2. Dış Veri Al komut grubunda Metinden düğmesini tıklayın. Tablo Alma Sihirbazı iletişim kutusu ekranda görünecektir.
Kolay Bağlantı Adı alanına bağlantınız için Satış Geçmişi gibi bir ad girin. Gözat düğmesini tıklayın ve metin dosyasını bulun. PowerPivot varsayılan olarak ilk satırın içeriğini sütun başlıkları olarak kullanmaz; bu nedenle önizleme işlemi sırasında sütun adları şu kafa karıştırıcı biçimde görünür: Fl, F2, F3, vb. (Şekil 10.4).
Sınırlayıcı olarak noktalı virgülün seçildiğinden emin olun. Sütun Ayırıcı açılır listesi, virgül, noktalı virgül, dikey çizgi vb. gibi bir dizi standart ayırıcıyı görüntüler.
İlk Satırı Sütun Başlıkları Olarak Kullan onay kutusunu işaretleyin. Önizleme penceresi gerçek sütun başlıklarını görüntüler.
Bir sütunu içe aktarmayı reddetmek istiyorsanız ilgili kutuların işaretini kaldırın. Metin dosyası RAM'e yüklenmeye hazır. Kullanılan miktarı önemli ölçüde azaltabileceğinizi lütfen unutmayın. rasgele erişim belleği fazladan sütunları hariç tutarsanız, özellikle uzun olanları içeriyorsa metin değerleri. İncirde. Şekil 10.5, onay kutusunun işareti kaldırıldığında verilerin nasıl göründüğünü gösterir.
Bitir düğmesini tıklattıktan sonra PowerPivot dosyayı belleğe yüklemeye başlar. Bu, yüklenen satır sayısını görüntüler şu anda zaman (Şekil 10.6).
Dosyanın içe aktarımı tamamlandıktan sonra, yüklenen satırların sayısı görüntülenir - 1,8 milyon kayıt (Şekil 10.7). PowerPivot penceresine dönmek için Kapat düğmesini tıklayın.
PowerPivot penceresi 1,8 milyon kayıt görüntüler. Bunları görüntülemek için kullanabilirsiniz dikey şerit taslak. Ayrıca sıralayabilir, sayı biçimini değiştirebilir veya filtre uygulayabilirsiniz (Şekil 10.8).

Herkese selam! Bugünün materyali, uygulama programlarıyla çalışma konusunda ustalaşmaya devam edenler ve Excel'de nasıl pivot tablo oluşturulacağını bilmeyenler içindir.

Herhangi birinde genel bir tablo oluşturduktan sonra metin belgeleri Excel'de pivot tablolar oluşturarak analiz edebilirsiniz.

Excel pivot tablosu oluşturmak belirli koşulların karşılanmasını gerektirir:

Veriler, sütunları olan bir tabloya ve adları olan listelere sığar.
Doldurulmamış form yok.
Gizli nesne yok.

Excel'de pivot tablo nasıl yapılır: adım adım talimatlar

Bir pivot tablo oluşturmak için ihtiyacınız olan:

Oluşturuldu boş sayfa, alanların ve alanların listesini görebileceğiniz yer. Başlıklar yeni tablomuzda alanlar haline geldi. Pivot tablo alanlar sürüklenerek oluşturulacaktır.

Bunlar bir onay işaretiyle işaretlenecek ve analiz kolaylığı için bunları tablo alanlarında değiştireceksiniz.

Veri analizini satıcıya göre filtreleyerek yapmaya karar verdim, böylece kim tarafından, her ay ne kadara satıldığı, ne tür bir ürün olduğu netleşsin.

Belirli bir satıcı seçiyoruz. Fareyi basılı tutun ve "Satıcı" alanını "Rapor Filtresi"ne taşıyın. Yeni alan işaretlenir ve tablo görünümü biraz değişir.

Bir açılır listeyi görüntülemek için adı hangi sırayla belirttiğimiz önemlidir. Başlangıçta satırlarda bir ürün lehine bir seçim yapıp ardından fiyatını belirtirsek, o zaman ürünler açılır listeler olacaktır ve bunun tersi de geçerlidir.

Ana tablodaki “Birimler” sütunu, belirli bir satıcının belirli bir fiyata sattığı malların miktarını gösteriyordu.

Örneğin her ay için satışları görüntülemek için "Tarih" alanını "Sütun adları" ile değiştirmeniz gerekir. Tarihe tıklayarak "Grupla" komutunu seçin.

Tarih dönemlerini ve adımı belirtin. Seçiminizi onaylayın.

Böyle bir tablo görüyoruz.

"Tutar" alanını "Değerler" alanına taşıyalım.

Sayıların gösterimi görünür hale geldi ancak sayı formatına ihtiyacımız var

Bunu düzeltmek için fareyle pencereyi çağırıp “Sayı Formatı”nı seçerek hücreleri seçin.

Bir sonraki pencere için sayı formatını seçip “Rakam grubu ayırıcı” seçeneğini işaretliyoruz. “Tamam” butonu ile onaylayın.

Pivot Tablo Tasarlamak

Aynı anda birden fazla nesnenin seçimini onaylayan kutuyu işaretlersek, birden fazla satıcıya ait verileri aynı anda işleyebiliriz.

Filtre sütunlara ve satırlara uygulanabilir. Ürün türlerinden birinin üzerindeki kutucuğu işaretleyerek bir veya daha fazla satıcı tarafından ne kadarının satıldığını öğrenebilirsiniz.

Saha parametreleri de ayrıca yapılandırılır. Örnekte belli bir Roman satıcının belli bir ayda belli bir fiyata gömlek sattığını görüyoruz. Fareye tıklayarak "Alana göre topla..." satırında menüyü çağırıyoruz ve "Değer Alanı Parametreleri"ni seçiyoruz.

Masaya bak. Satıcının bir ayda 2 gömlek sattığını açıkça gösteriyor.

Şimdi tabloyu değiştirip filtrenin aylara göre çalışmasını sağlıyoruz. “Tarih” alanını “Rapor Filtresi”ne aktarıyoruz, “Sütun Adları”nın olduğu yerde “Satıcı” olacak. Tablo, tüm satış dönemini veya belirli bir ayı görüntüler.

Pivot tablodaki hücrelerin seçilmesi, "Pivot Tablolarla Çalışmak" adlı bir sekmenin görünmesine neden olur ve "Seçenekler" ve "Tasarımcı" olmak üzere iki sekme daha olacaktır.

Aslında pivot tabloların ayarlarından çok uzun süre bahsedebilirsiniz. Zevkinize göre değişiklikler yapın, size uygun bir kullanım elde edin. Zorlamaktan ve denemekten korkmayın. Ctrl+Z tuş bileşimine basarak istediğiniz eylemi istediğiniz zaman değiştirebilirsiniz.

Umarım tüm materyali öğrenmişsinizdir ve artık Excel'de nasıl pivot tablo oluşturulacağını biliyorsunuzdur.

Özet Excel tabloları– bu, bir belge hakkında anında rapor oluşturmaya yönelik bir fonksiyonun varlığını ima eden özel bir yapı türüdür.

Onların yardımıyla aynı türdeki verilerden bazılarını kolayca özetleyebilirsiniz.

İÇİNDE Excel programı 2007 (MS Excel 2010|2013) pivot tablosu öncelikle verilerin matematiksel veya ekonomik analizini derlemek için kullanılır.

Excel'de pivot tablo nasıl yapılır

Belge verilerinin analizi, atanan görevlerin daha hızlı ve daha doğru çözümüne katkıda bulunur.

Excel elektronik tablo işlemcisi en hacimli ve karmaşık belgeleri bile işleyebilir. Bir raporun derlenmesi ve verilerin özetlenmesi program için zor değildir.

Basit bir özet tablosu oluşturmak için aşağıdaki talimatları izleyin:

Program araç çubuğunun ana menüsünde bulunan "ekle" sekmesini kullanarak önerilen pivot tabloları oluşturmak için düğmeyi seçin;

Daha fazla oluşturma için bir ekleme penceresi seçme

Açılan program iletişim kutusunda beğendiğiniz işaret çerçevesine tıklayın. Bu, düzeni kullanıcı önizlemesi için kullanılabilir hale getirecektir.
Tüm bilgileri tam olarak görüntüleyebilecek en uygun seçeneği seçin;

Tavsiye! Ek pivot tablo düzenleri resmi Microsoft web sitesinden indirilebilir.

Önizlemeleri oluşturmak ve görüntülemek için iletişim kutusu

Tamam'a bastığınızda program seçilen tabloyu (veya boş düzeni) hemen belgenin açık sayfasına ekleyecektir. Program ayrıca verilen bilgilere göre satırların sırasını da otomatik olarak belirleyecektir;
Tablo öğelerini seçmek ve bunları manuel olarak düzenlemek için içerikleri sıralayın. Veriler ayrıca filtrelenebilir. Esasen, bir kontrol paneli küçük bir veritabanının prototipidir.
İhtiyaç duyulduğunda filtreleme şarttır hızlı Görünüm yalnızca belirli sütunlar ve çizgiler. Aşağıda içeriğin filtrelenmesinden sonraki bir satış pivot tablosu örneği verilmiştir.
Bu şekilde bireysel bölgelerdeki (bizim durumumuzda Batı ve Güney) satış hacimlerini hızlı bir şekilde görüntüleyebilirsiniz;

Filtrelenmiş tablo örneği

Önceden oluşturulmuş düzenlerin kullanılması tavsiye edilir, ancak kullanıcı boş bir tablo seçip onu bağımsız olarak doldurabilir.

İÇİNDE boş şablon alanlar, hesaplama formülleri, filtreler eklemeniz gerekir.

Gerekli veri öğeleri ayrı alanlara sürükleyip bırakılarak boş bir form doldurulur.

Kişisel kullanıcı özeti tablosu oluşturma formu

Aynı anda bir belgenin birden fazla sayfasında bağlantılı özet tabloları da oluşturabilirsiniz.

Bu sayede belgenin tamamının veya birden fazla belgenin/sayfanın verilerini aynı anda analiz edebilirsiniz.

Pivot tabloları kullanarak harici verileri de analiz edebilirsiniz.

Excel'deki Makrolar - Kullanım talimatları
Excel'deki açılır liste - Oluşturma talimatları
Excel'de bir satır nasıl dondurulur - Ayrıntılı talimatlar.

Bu Renat'ın Lifehacker'da konuk yazar olduğu ilk sefer değil. Daha önce kendisinden bir eğitim planının nasıl oluşturulacağı ve çevrimiçi kaynakların yanı sıra bir eğitim planının nasıl oluşturulacağı hakkında mükemmel materyaller yayınlamıştık.

Bu makale, aşağıdakilere izin veren basit teknikler içermektedir: Özellikle yönetim raporlamasıyla ilgilenenler, 1C'den ve diğer raporlardan indirilenlere dayalı çeşitli analitik raporlar hazırlayanlar ve bunlardan yönetim için sunumlar ve diyagramlar oluşturanlar için yararlı olacaklar. Mutlak bir yenilik iddiasında değilim - şu ya da bu şekilde, bu teknikler muhtemelen forumlarda tartışılmıştır.

Aradığınız değerler tablonun ilk sütununda değilse DÜŞEYARA ve YATAYARA'nın basit alternatifleri: LOOKUP, INDEX+MATCH

Sonuç:

İÇİNDE bu durumda CHAR işlevine (tırnak işaretlerini görüntülemek için) ek olarak, olumlu bir satış eğilimi olup olmadığına bağlı olarak metni değiştirmenize olanak tanıyan IF işlevi ve herhangi bir şekilde bir sayı görüntülemenize olanak tanıyan TEXT işlevi kullanılır. biçim. Sözdizimi aşağıda açıklanmıştır:

METİN ( Anlam; biçim)

Biçim, Hücreleri Biçimlendir penceresine özel bir biçim giriyormuşsunuz gibi tırnak işaretleri içinde belirtilir.

Daha karmaşık metinler de otomatikleştirilebilir. Uygulamamda, uzun ama rutin yorumların otomasyonu vardı. yönetim raporlaması"GÖSTERGE plana göre XX oranında düştü/arttı, esas olarak FAKTÖR1'in XX kadar artması/azalması, FAKTÖR2'nin YY kadar artması/azalması..." formatında değişen faktörler listesiyle. Bu tür yorumları sık sık yazıyorsanız ve bunları yazma süreci algoritmik hale getirilebiliyorsa, bir kez olsun sizi işin en azından bir kısmından kurtaracak bir formül veya makro oluşturmayı denemelisiniz.

Birleştirmeden sonra her hücredeki veriler nasıl kaydedilir?

Hücreleri birleştirirken yalnızca bir değer korunur. Hücreleri birleştirmeye çalıştığınızda Excel sizi bu konuda uyarır:

Buna göre, her hücreye bağlı bir formülünüz olsaydı, bunlar birleştirildikten sonra çalışmayı durdururdu (#örneğin 3-4. satırlarında N/A hatası):

Hücreleri birleştirmek ve yine de her birindeki verileri korumak için (belki de bu soyut örnekte olduğu gibi bir formülünüz vardır; belki de hücreleri birleştirmek ancak tüm verileri gelecek için kaydetmek veya kasıtlı olarak gizlemek istiyorsunuz), hücredeki herhangi bir hücreyi birleştirin. çalışma sayfası , bunları seçin ve ardından biçimlendirmeyi birleştirmeniz gereken hücrelere aktarmak için Biçim Boyacısı komutunu kullanın:

Birden çok veri kaynağından özet nasıl oluşturulur?

Aynı anda birden fazla veri kaynağından bir pivot tablo oluşturmanız gerekiyorsa, bu seçeneğe sahip olan şeride veya hızlı erişim paneline "Özet Tablo ve Grafik Sihirbazı"nı eklemeniz gerekecektir.

Bunu şu şekilde yapabilirsiniz: “Dosya” → “Seçenekler” → “Hızlı Erişim Araç Çubuğu” → “Tüm Komutlar” → “Özet Tablo ve Grafik Sihirbazı” → “Ekle”:

Bundan sonra, şeritte aynı sihirbazı çağıran ilgili bir simge görünecektir:

Üzerine tıkladığınızda bir iletişim kutusu görünür:

İçinde “Birkaç konsolidasyon aralığında” öğesini seçmeniz ve “İleri” ye tıklamanız gerekir. Bir sonraki adımda “Bir sayfa alanı oluştur” veya “Sayfa alanları oluştur” seçeneğini seçebilirsiniz. Her veri kaynağı için kendiniz bir ad bulmak istiyorsanız ikinci seçeneği seçin:

Bir sonraki pencerede, özetin oluşturulacağı tüm aralıkları ekleyin ve onlara ad verin:

Bundan sonra, son iletişim kutusunda PivotTable raporunun mevcut veya yeni bir sayfada nereye yerleştirileceğini belirtin:

PivotTable raporu hazır. "Sayfa 1" filtresinde gerekirse veri kaynaklarından yalnızca birini seçebilirsiniz:

A metninin B metninde geçme sayısı nasıl hesaplanır (“MTS tarifesi SuperMTS” - MTS kısaltmasının iki örneği)

İÇİNDE bu örnekte A sütununda birkaç metin satırı vardır ve görevimiz, E1 hücresinde bulunan aranan metnin her birinde kaç kez oluştuğunu bulmaktır:

Bu sorunu çözmek için aşağıdaki işlevlerden oluşan karmaşık bir formül kullanabilirsiniz:

LEN - metnin uzunluğunu hesaplar, tek argüman metindir. Örnek: DLTR (“araba”) = 6.
SUBSTITUTE - bir metin dizesindeki belirli bir metni başka bir metinle değiştirir. Söz dizimi: SUBSTITUTE ( metin; eski_metin; yeni_metin). Örnek: SUBSTITUTE (“araba”;“otomatik”;“”)= “mobil”.
UPPER - dizedeki tüm karakterleri büyük harflerle değiştirir. Tek argüman metindir. Örnek: SERMAYE (“makine”) = “MAKİNE”. Büyük/küçük harfe duyarlı olmayan aramalar yapmak için bu fonksiyona ihtiyacımız olacak. Sonuçta, ÜST("makine") = ÜST("Makine")

Belirli bir metin dizesinin başka bir metin dizesinde geçtiği yeri bulmak için, orijinal metin dizesindeki tüm oluşumlarını kaldırmanız ve ortaya çıkan dizenin uzunluğunu orijinal dizeyle karşılaştırmanız gerekir:

DLstr(“MTS Tarifesi Süper MTS”) – DLST (“Süper Tarife”) = 6

Ve sonra bu farkı aradığımız ipin uzunluğuna bölün:

6 / LISTR (“MTS”) = 2

Orijinalinde “MTS” satırının tam iki katı yer alıyor.

Geriye bu algoritmayı formüller dilinde yazmak kalıyor (geçişleri aradığımız metni "metin" ile belirtiriz ve oluşum sayısı bizi ilgilendiren metni "arama" ile belirtiriz):

=(UZUNLUK( metin)-UZUNLUK(YEDEK(ÜST( metin);BÜYÜK HARF( çok rağbette);"")))/UZUNLUK( çok rağbette)

Örneğimizde formül şöyle görünür:

=(UZUNLUK(A2)-UZUNLUK(YEDEK(BÜYÜK HARF(A2),BÜYÜK HARF($E$1),“”)))/UZUNLUK($E$1)

Pivot tablo etkili araç Karşılaştırmaları, kalıpları ve eğilimleri bulmayı kolaylaştıran verileri hesaplamak, özetlemek ve analiz etmek.

Bir pivot tablo ayarlama

Hazırlık

Pivot tablolarla ilgili yeterli deneyiminiz yoksa veya nereden başlayacağınızı bilmiyorsanız, pivot tabloları kullanmak en iyisidir. önerilen pivot tablo. Bunu kullanırken Excel işlevleri Verileri PivotTable'daki en uygun alanlarla eşleştirerek uygun düzeni belirler. Bu daha sonraki deneyler için bir başlangıç noktası sağlar. Önerilen PivotTable'ı oluşturduktan sonra farklı yönelimleri keşfedebilir ve istediğiniz sonuçları elde etmek için alanları yeniden düzenleyebilirsiniz.

Bölgede Alan adı çizgiler sütunlar değerler

PivotTable'daki Değerler

Pivot Tabloları Güncelleme

Bir kaynağa yeni veri eklediğinizde, bu veriden oluşturulan tüm pivot tabloları güncellemeniz gerekir. Bir pivot tabloyu güncellemek için şunları yapabilirsiniz: sağ tık aralığının herhangi bir yerinde ve komutu seçin Güncelleme. Birden fazla PivotTable'ınız varsa, önce herhangi bir PivotTable'daki herhangi bir hücreyi seçin ve ardından kaset sekmeyi aç Pivot tablo analizi, düğmenin altındaki oku tıklayın Güncelleme ve bir takım seçin Tümünü güncelle.

PivotTable'ı silme

Artık pivot tabloya ihtiyacınız yoksa onu seçin ve tuşuna basın. SİLMEK. Bu, diğer verileri, pivot tabloları veya grafikleri etkilemeyecektir. Pivot tablo şuradaysa ayrı sayfaİhtiyacınız olan verilere artık ihtiyacınız olmadığında o sayfayı silebilirsiniz. Bu, pivot tablodan kurtulmanın en kolay yoludur.

PivotTable artık Excel Online'da bir elektronik tabloya eklenebilir.

PivotTable alanlarının listesiyle çalışma

Bölgede Pivot tablo alanlarıÜst kısımda pivot tabloya eklemek istediğiniz alanların kutularını işaretleyin. Varsayılan olarak sayısal olmayan alanlar kapsama eklenir çizgiler, tarih ve saat değeri alanları - alana sütunlar ve sayısal alanlar - alana değerler. Ayrıca öğeleri herhangi bir PivotTable alanına manuel olarak sürükleyip bırakabilirsiniz. Artık bir öğeye ihtiyacınız yoksa, onu alan listesinden dışarı sürüklemeniz veya onay kutusunun işaretini kaldırmanız yeterlidir. Öğeleri yeniden düzenleme yeteneği, pivot tablonun özelliklerinden biridir ve bu sayede görünümünü hızlı ve kolay bir şekilde değiştirebilirsiniz.

PivotTable Değerleriyle Çalışmak

Pivot Tabloları Güncelleme

Bir kaynağa yeni veri eklediğinizde, buna dayalı olarak tüm pivot tabloları güncellemeniz gerekir. PivotTable'ı yenilemek için aralığındaki herhangi bir yeri sağ tıklayıp seçebilirsiniz. Güncelleme

PivotTable'ı silme

PivotTable'a artık ihtiyacınız yoksa onu seçip DELETE tuşuna basmanız yeterlidir. Bu, diğer verileri, pivot tabloları veya grafikleri etkilemeyecektir. Pivot tablo artık ihtiyacınız olan verileri içermeyen ayrı bir sayfadaysa, o sayfayı silebilirsiniz. Bu, pivot tablodan kurtulmanın en kolay yoludur.

Ek Bilgiler

Her zaman bir Excel Teknik Topluluğu uzmanına soru sorabilir, Yanıtlar topluluğundan yardım isteyebilir ve ayrıca önerilerde bulunabilirsiniz. yeni özellik veya web sitesinde iyileştirme

Büyük ve karmaşık tabloları analiz etmek için genellikle . Formüller mevcut verileri gruplamak ve analiz etmek için de kullanılabilir. Formülleri kullanarak basit raporlar oluşturalım.

Kullanacağız ( Ekle/Tablolar/Tablo), ürün partilerinin satışına ilişkin bilgileri içerir. Tablonun satırları, bir ürün grubunun teslimatı ve satışları hakkında veri sağlar. Makalede benzer bir tablo kullanıldı .

Tabloda aşağıdaki sütunlar bulunur:

Ürün– mal grubunun adı, örneğin “ Portakal»;
Grup– ürün grubu, örneğin, “ Portakal» grubun bir parçasıdır « Meyveler»;
teslimat tarihi– Malların Tedarikçi tarafından teslim tarihi;
Satış bölgesi– Mal grubunun satıldığı bölge;
Satış– Mal grubunu satmanın mümkün olduğu maliyet;
Satış– Malların Bölgedeki fiili satış süresi (gün olarak);
Kâr– Satılan Mal partisinden kar elde edilip edilmediğini gösteren bir not.

Başından sonuna kadar İsim Yöneticisi Tabloları " olarak ayarlayalım Kaynak_tablosu"(örnek dosyaya bakın).

Formülleri kullanarak ayrı sayfalara yerleştireceğimiz 5 basit rapor oluşturacağız.

Rapor No. 1 Ürünlerin toplam satışı

Her Ürünün toplam satışını bulalım.
Sorun, SUMIF() işlevi kullanılarak oldukça basit bir şekilde çözülebilir, ancak raporun kendisini oluşturmak, bazı EXCEL araçlarıyla çalışma konusunda belirli beceriler gerektirir.

Öyleyse başlayalım. Öncelikle Ürün adlarının bir listesini oluşturmamız gerekiyor. Çünkü Kaynak tablonun Ürün sütununda isimler tekrarlanır, o zaman ondan yalnızca değerleri seçmemiz gerekir. Bu birkaç yolla yapılabilir: formüller (makaleye bakın), menü aracılığıyla Veri/Verilerle çalışma/Yinelenenleri kaldır veya kullanıyor. İlk yöntemi kullanırsanız kaynak tabloya yeni Ürünler eklerken listeye otomatik olarak yeni adlar eklenecektir. Ancak burada kolaylık olması açısından ikinci yöntemi kullanacağız. Bunun için:

Orijinal tablonun bulunduğu sayfaya gidin;
Arama ( Veri/ Sırala ve Filtrele/ Gelişmiş);
Aşağıdaki şekilde gösterildiği gibi alanları doldurun: anahtarı konumuna getirin Sonucu başka bir konuma kopyalayın; Kaynak Aralığı alanına $A$4:$A$530 girin; Kutuyu kontrol et Yalnızca benzersiz girişler.

Ortaya çıkan listeyi raporun yerleştirileceği sayfaya kopyalayın;
Ürün listesini sıralayın ( Veri/ Sıralama ve Filtreleme/ A'dan Z'ye Sıralama).

Aşağıdaki listeyi almalısınız.

Bir hücrede B6 Aşağıdaki formülü girin ve listenin sonuna kopyalayın:

ETOPLA(Kaynak_Tablo[Ürün],A6,Kaynak_Tablo[Satış])

EĞERSAY(Orijinal_Tablo[Ürün],A6)

2 Numaralı Rapor Bölgelere Göre Ürün Satışları

Her Ürünün Bölgelerdeki toplam satışını bulalım.
1 No'lu Rapor için oluşturulan Ürünlerin listesini kullanalım. Benzer şekilde Bölge adlarının bir listesini elde edeceğiz (Başlangıç aralığı alanına $D$4:$D$530 girin).
Ortaya çıkan dikey aralığı şuraya kopyalayın: Pano ve yatay. Bölge adlarını içeren sonuç aralığını rapor başlığına yerleştirin.

Bir hücrede B 8 Aşağıdaki formülü girelim:

ÇOKETOPLA(Orijinal_Tablo[Satışlar];
Source_Table[Ürün];$A8;
Source_Table[Satış Bölgesi];B$7)

Formül, hücrede adı geçen Ürünün toplam satışını döndürecektir. A8 , Hücreden Bölge'de 7'DE . Kullanıma dikkat edin (referanslar $A8 ve B$7), tablonun geri kalan boş hücreleri için formülü kopyalarken buna ihtiyacınız olacak.

Yukarıdaki formülü kullanarak sağdaki hücrelere kopyalamak mümkün olmayacaktır (bu, 1 No'lu Rapor için yapılmıştır), çünkü bu durumda hücrede C8 formül şöyle görünecek:

SUMIFS(Orijinal_Tablo[Satışlar, günler];
Source_Table[Grup];$A8;
Source_Table[Satış];C$7)

Rapor No. 3 Ürünleri Kârlılığa Göre Filtreleme

Orijinal tabloya dönelim. Her Mal partisi ya kâr etti ya da etmedi (orijinal tablodaki Kâr sütununa bakın). Satışları karlılığa bağlı olarak Ürün Grubu bazında hesaplayalım. Bunu yapmak için kaynak tablonun kayıtlarını formüller kullanarak Kâr alanına göre filtreleyeceğiz.

SIKLIK(Kaynak_Tablo[Satışlar, günler];A7:A12)

Formül girmek için aralığı seçin C6:C12 , daha sonra Formül çubuğu yukarıdaki formülü girin ve tıklayın CTRL+ VARDİYA+ GİRMEK.

Aynı sonuç normal SUMproduct() işlevi kullanılarak da elde edilebilir:
=TOPLAÇARP((Orijinal_Tablo[Satışlar, günler]>A6)*
(Source_Table[Satışlar, günler]<=A7))

Rapor No. 5 Mal tedarik istatistikleri

Şimdi bu ayki Mal tedarikine ilişkin bir rapor hazırlayacağız.
Öncelikle aylara göre yıllara göre bir liste oluşturalım. Orijinal tabloda en erken teslim tarihi 07/11/2009'dur. Aşağıdaki formülü kullanarak hesaplayabilirsiniz:
=MIN(Kaynak_Tablo[Teslim Tarihi])

En erken teslimat tarihinden başlayarak bir tarih listesi oluşturalım. Bunu yapmak için şu formülü kullanıyoruz:
=AY($C$5,-1)+1

Sonuç olarak, tarihlerin bir listesini alıyoruz - ayların ilk günleri:

Uygun hücre formatını uygulayarak tarihlerin görünümünü değiştirelim:

Aylık teslim edilen Mal partilerinin sayısını hesaplamak için formül:

TOPLAMÇARP((Orijinal_Tablo[Teslim Tarihi]>=B9)*
(Kaynak_Tablo[Teslimat Tarihi]

Şimdi her yıl için toplam parti sayısını hesaplamak için satırlar ekleyelim. Bunu yapmak için tabloyu biraz değiştirelim ve YEAR() fonksiyonunu kullanarak teslimatın yapıldığı yılı ayrı bir sütunda vurgulayalım.

Şimdi yıla göre görüntülemek için menü öğesi aracılığıyla bir yapı oluşturalım:

Değiştirilen tablonun herhangi bir hücresini seçin;
Bir menü öğesi aracılığıyla bir pencereyi çağırma Veri/Yapı/Alt Toplamlar;
Alanları şekilde gösterildiği gibi doldurun:

Tamam'a tıkladıktan sonra tablo aşağıdaki gibi değiştirilecektir:

Yıllara göre ara toplamlar oluşturulacaktır.
Sayfanın sol üst köşesindeki küçük düğmelere tıklayarak verilerin tablodaki görünümünü kontrol edebilirsiniz.

Özet :

Oluşturulanlara benzer raporlar doğal olarak veya kullanılarak yapılabilir. Filtre kaynak tabloya veya BDSUMM(), BIZULT(), BCOUNT(), vb. diğer işlevleri kullanarak. Yaklaşım seçimi özel duruma bağlıdır.