Pivot Tabloyu 3 Dinamik Dizi Formülü ile Değiştirme - Excel İpuçları

İçindekiler

Video izle

Dinamik dizi formüllerinin Orlando'daki Ignite 2018 konferansında açıklanmasının üzerinden sekiz gün geçti. İşte öğrendiklerim:

Modern Diziler 24 Eylül 2018'de Ignite'ta duyuruldu ve resmi olarak Dynamic Arrays olarak adlandırıldı.
Nasıl kullanılacağına dair 30 örnek içeren 60 sayfalık bir e-kitap yazdım ve 2018'in sonuna kadar ücretsiz olarak sunuyorum.
Açılış, herkesin istediğinden çok daha yavaş olacak ve bu sinir bozucu. Neden bu kadar yavaş? Excel ekibi, 30 yıldır stabil olan Calc Engine kodunda değişiklikler yaptı. Özellikle endişe verici: Excel'e istemeden örtük kesişim kullanan formülleri enjekte eden eklentilerle. Excel şimdi bir Dökülme aralığı döndürürse bu eklentiler bozulacaktır.
Bir dizi tarafından döndürülen aralığa başvurmanın yeni bir yolu var: =E3#ancak henüz bir adı yok. # Denir Dökülen Formül Operatör . Spill Ref (Excel MVP Jon Acampora tarafından önerilen) veya The Spiller (MVP Ingeborg Hawighorst tarafından önerilen ) gibi bir ad hakkında ne düşünüyorsunuz ?

Pivot Table Data Crunching'in ortak yazarı olarak, iyi bir pivot tabloyu seviyorum. Peki ya pivot tablolarınızın güncellenmesi gerekiyorsa ve yöneticinizin yöneticisinin Yenile'yi tıklatacağına güvenemiyorsanız? Bugün açıklanan teknik, bir pivot tablonun yerini alacak bir dizi üç formül sunar.

Benzersiz müşterilerin sıralı bir listesini almak için =SORT(UNIQUE(E2:E564))I2'de kullanın .

Raporun diğer tarafında müşteriler oluşturmak için tek bir dinamik dizi formülü

Ürünü en üste koymak için =TRANSPOSE(SORT(UNIQUE(B2:B564)))J1'de kullanın .

Sütunlar alanı için TRANSPOSE kullanın

İşte bir sorun: Müşteri listesinin ne kadar uzun olacağını bilmiyorsunuz. Ürün listesinin ne kadar geniş olacağını bilmiyorsunuz. I2 # 'a başvurursanız, Spiller otomatik olarak döndürülen dizinin geçerli boyutuna başvurur.

Pivot tabloda değerleri alan geri formül J2 tek bir dizi formülü aşağıdaki gibidir: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

İngilizce'de bu, E'deki Müşteriler'in I2 dizi formülünden geçerli satırın müşterisiyle eşleştiği ve B'deki ürünlerin J1'deki dizi formülünün geçerli sütunuyla eşleştiği G2: G564'ten gelen gelirleri eklemek istediğinizi belirtir.

Bu tatlı bir formül

Ya temeldeki veriler değişirse? Kaynaktaki bu iki hücreyi değiştirerek yeni bir müşteri ve yeni bir ürün ekledim.

Orijinal verilerdeki bazı hücreleri değiştirin

Rapor, yeni satırlar ve yeni sütunlarla güncellenir. I2 # ve J1 # Dizi Aralığı Referansı, fazladan satır ve sütunu işler.

Çapraz sekmeler raporunuz yeni verilerle otomatik olarak genişler

SUMIFS neden çalışıyor? Bu, Excel'de Yayın adı verilen bir kavramdır. İki diziye başvuran bir formülünüz varsa:

Birinci dizi (27 satır) x (1 sütun)
Dizi iki (1 satır) x (3 sütun)
Excel, başvurulan dizilerin en uzun ve en geniş kısmı kadar uzun ve geniş bir sonuç dizisi döndürür:
Sonuç (27 satır) x (3 sütun) olacaktır.
Buna Yayın dizileri denir.