Pivot tablolar herkes için iyidir - hızlı hesaplarlar, esnek bir şekilde yapılandırılırlar ve gerekirse tasarım onlara zarif bir şekilde sarılabilir. Ancak merhemde, özellikle değer alanının sayı içermemesi gereken metin içermesi gereken bir özet oluşturamama gibi birkaç sinek var.
Bu sınırlamayı aşmaya çalışalım ve benzer bir durumda bir "birkaç koltuk değneği" bulalım.
Diyelim ki firmamız ürünlerini konteynerlerde Ülkemizin ve Kazakistan'ın çeşitli şehirlerine taşıyor. Konteynerler ayda bir defadan fazla gönderilmez. Her kapsayıcının bir alfasayısal numarası vardır. İlk veri olarak, her bir şehre ve her ay gönderilen konteynerlerin sayısını net bir şekilde görmek için bir tür özet yapmanız gereken teslimatları listeleyen standart bir tablo vardır:
Kolaylık sağlamak için, komutu kullanarak ilk verileri “akıllı” olan tabloyu önceden yapalım. Ana Sayfa – Tablo olarak biçimlendir (Ana Sayfa — Tablo Olarak Biçimlendir) ve ona bir isim ver Teslimatlar çıkıntı Inşaatçı (Tasarım). Gelecekte, bu hayatı kolaylaştıracak çünkü. tablonun adını ve sütunlarını doğrudan formüllerde kullanmak mümkün olacaktır.
Yöntem 1. En kolayı – Power Query'yi kullanın
Power Query, Excel'de veri yüklemek ve dönüştürmek için süper güçlü bir araçtır. Bu eklenti, 2016'dan beri varsayılan olarak Excel'de yerleşiktir. Excel 2010 veya 2013'e sahipseniz, onu ayrı olarak indirebilir ve kurabilirsiniz (tamamen ücretsiz).
Tüm süreç, netlik için aşağıdaki videoda adım adım analiz ettim:
Power Query'yi kullanmak mümkün değilse, bir pivot tablo veya formüller aracılığıyla başka yollara gidebilirsiniz.
Yöntem 2. Yardımcı özet
Orijinal tablomuza bir sütun daha ekleyelim, burada basit bir formül kullanarak tablodaki her satırın sayısını hesaplıyoruz:
Açıkçası, -1 gereklidir, çünkü tablomuzda tek satırlık bir başlık var. Tablonuz sayfanın başında değilse, mevcut satır ve tablo başlığındaki sayıların farkını hesaplayan biraz daha karmaşık, ancak evrensel bir formül kullanabilirsiniz:
Şimdi standart bir şekilde verilerimize göre istenilen türde bir pivot tablo oluşturacağız ancak value alanında alanı bırakacağız. Satır numarası istediğimizin yerine konteyner:
Aynı ay içinde aynı ilde birden fazla konteynerimiz olmadığı için özetimiz aslında ihtiyacımız olan konteynerlerin miktarını değil satır numaralarını verecektir.
Ek olarak, sekmedeki genel ve ara toplamları kapatabilirsiniz. Yapıcı – Genel toplamlar и Alt toplamlar (Tasarım — Genel Toplamlar, Ara Toplamlar) ve aynı yerde düğmesiyle özeti daha uygun bir tablo düzenine geçirin Rapor maketi (Rapor Düzeni).
Böylece, zaten sonucun yarısına geldik: Şehir ve ayın kesiştiği yerde, kaynak tablosunda ihtiyacımız olan konteyner kodunun bulunduğu bir satır numarasının olduğu bir tablomuz var.
Şimdi özeti kopyalayalım (aynı sayfaya veya başka bir sayfaya) ve değerler olarak yapıştıralım ve ardından formülümüzü değer alanına girelim, bu da özette bulunan satır numarasına göre kap kodunu çıkaracak:
işlev IF (EĞER), bu durumda özetteki sonraki hücrenin boş olmadığını kontrol eder. Boşsa, boş bir metin dizisi “” çıktısı alın, yani hücreyi boş bırakın. Boş değilse, sütundan çıkarın Konteyner kaynak tablo Teslimatlar işlevi kullanarak satır numarasına göre hücre içeriği INDEX (İNDEKS).
Belki de burada çok açık olmayan tek nokta çift kelimedir. Konteyner formülde. Ne garip bir yazı biçimi:
Tedarik[[Konteyner]:[Konteyner]]
… sadece sütuna referans vermek için gereklidir Konteyner mutlaktı (sıradan “akıllı olmayan” tablolar için $ işaretli bir referans gibi) ve formülümüzü sağa kopyalarken komşu sütunlara kaymadı.
Gelecekte, kaynak tablodaki verileri değiştirirken Teslimatlar, yardımcı özetimizi sağ tıklayıp komutu seçerek satır numaralarıyla güncellemeyi hatırlamalıyız. Güncelle ve Kaydet (Yenile).
Yöntem 3. Formüller
Bu yöntem, bir ara pivot tablonun oluşturulmasını ve manuel güncelleme yapılmasını gerektirmez, ancak Excel'in “ağır silahını” kullanır - işlev YAZSLIMN (TOPLA). Bir özette satır numaralarını aramak yerine, bunları şu formülü kullanarak hesaplayabilirsiniz:
Aslında, bir miktar dış hacim ile, bu, seçici toplama işlevi için standart bir kullanım durumudur. YAZSLIMNVerilen şehir ve ay için satır numaralarını toplayan A. Yine aynı şehirde aynı ay içinde birden fazla konteynerimiz olmadığı için fonksiyonumuz aslında miktarı değil satır numarasını verecektir. Ve sonra önceki yöntemden zaten tanıdık olan işlev INDEX Ayrıca kapsayıcı kodlarını da çıkarabilirsiniz:
Tabii ki, bu durumda, artık özeti güncellemeyi düşünmenize gerek yok, ancak büyük tablolarda işlev SÜMMESLİ fark edilir derecede yavaş olabilir. Ardından, formüllerin otomatik güncellenmesini kapatmanız veya ilk yöntemi - bir pivot tabloyu kullanmanız gerekecektir.
Özetin görünümü raporunuz için çok uygun değilse, o zaman bizim yaptığımız gibi doğrudan değil, işlevi kullanarak satır numaralarını nihai tabloya çıkarabilirsiniz. ÖZET.TABLO.VERİ AL (GET.PIVOT.VERİ). Bunun nasıl yapılacağı burada bulunabilir.
- Pivot tablo kullanarak rapor nasıl oluşturulur?
- Pivot tablolarda hesaplamalar nasıl kurulur
- SUMIFS, COUNTIFS, vb. ile seçici sayma.