Ganti Tabel Pivot dengan 3 Rumus Array Dinamis - Tips Excel

Daftar Isi

Menonton video

Sudah delapan hari sejak formula array dinamis diumumkan pada konferensi Ignite 2018 di Orlando. Inilah yang saya pelajari:

Modern Array diumumkan di Ignite pada 24 September 2018 dan secara resmi disebut Dynamic Array.
Saya telah menulis e-book 60 halaman dengan 30 contoh bagaimana menggunakannya, dan saya menawarkannya gratis hingga akhir 2018.
Peluncurannya akan menjadi jauh lebih lambat dari yang diinginkan siapa pun, yang membuat frustrasi. Mengapa sangat lambat? Tim Excel telah membuat perubahan pada kode Calc Engine yang telah stabil selama 30 tahun. Perhatian khusus: dengan add-in yang memasukkan rumus ke dalam Excel yang secara tidak sengaja menggunakan persimpangan implisit. Add-in tersebut akan rusak jika Excel sekarang mengembalikan rentang Tumpahan.
Ada cara baru untuk merujuk ke rentang yang dikembalikan oleh larik: =E3#tetapi belum memiliki nama. The # disebut tumpah Formula Operator . Apa pendapat Anda tentang nama seperti Spill Ref (disarankan oleh Excel MVP Jon Acampora) atau The Spiller (disarankan oleh MVP Ingeborg Hawighorst)?

Sebagai salah satu penulis Pivot Table Data Crunching, saya menyukai tabel pivot yang bagus. Tetapi bagaimana jika Anda membutuhkan tabel pivot Anda untuk diperbarui dan Anda tidak dapat mempercayai manajer manajer Anda untuk mengklik Refresh? Teknik yang dijelaskan hari ini menawarkan serangkaian tiga rumus untuk menggantikan tabel pivot.

Untuk mendapatkan daftar pelanggan unik yang diurutkan, gunakan =SORT(UNIQUE(E2:E564))di I2.

Satu rumus array dinamis untuk membuat pelanggan di sisi laporan

Untuk meletakkan produk di atas, gunakan =TRANSPOSE(SORT(UNIQUE(B2:B564)))di J1.

Untuk area kolom, gunakan TRANSPOSE

Inilah masalahnya: Anda tidak tahu seberapa tinggi daftar pelanggan nantinya. Anda tidak tahu seberapa luas daftar produknya. Jika Anda merujuk ke I2 #, Spiller akan secara otomatis merujuk ke ukuran saat ini dari array yang dikembalikan.

Rumus untuk mengembalikan wilayah nilai-nilai tabel pivot adalah formula array tunggal di J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

Dalam bahasa Inggris, ini mengatakan bahwa Anda ingin menambahkan pendapatan dari G2: G564 di mana Pelanggan di E cocok dengan pelanggan baris saat ini dari rumus larik I2 dan produk di B cocok dengan kolom saat ini dari rumus larik di J1.

Ini adalah formula yang manis

Bagaimana jika data yang mendasarinya berubah? Saya menambahkan pelanggan baru dan produk baru dengan mengubah dua sel ini di sumbernya.

Ubah beberapa sel dalam data asli

Laporan diperbarui dengan baris baru dan kolom baru. Referensi Array-Range dari I2 # dan J1 # menangani baris dan kolom ekstra.

Laporan lintas tab Anda secara otomatis diperluas dengan data baru

Mengapa SUMIFS berfungsi? Ini adalah konsep di Excel yang disebut Broadcasting. Jika Anda memiliki rumus yang mengacu pada dua larik:

Larik satu adalah (27 baris) x (1 kolom)
Larik dua adalah (1 baris) x (3 kolom)
Excel akan mengembalikan larik resultan yang setinggi dan selebar bagian tertinggi dan terluas dari larik yang direferensikan:
Hasilnya adalah (27 baris) x (3 kolom).
Ini disebut array Penyiaran.