Gantikan Jadual Pangsi dengan 3 Rumus Array Dinamik - Petua Excel

Isi kandungan

Tonton Video

Sudah lapan hari sejak formula larik dinamik diumumkan pada persidangan Ignite 2018 di Orlando. Inilah yang saya pelajari:

Modern Arrays diumumkan di Ignite pada 24 September 2018 dan secara rasmi dinamakan Dynamic Arrays.
Saya telah menulis e-buku 60 halaman dengan 30 contoh cara menggunakannya, dan saya menawarkannya percuma sehingga akhir 2018.
Pelancaran akan menjadi jauh lebih lambat daripada yang diinginkan oleh semua orang, yang mengecewakan. Kenapa lambat sekali? Pasukan Excel telah membuat perubahan pada kod Calc Engine yang telah stabil selama 30 tahun. Yang menjadi perhatian khusus: dengan tambahan yang memasukkan formula ke dalam Excel yang secara tidak sengaja menggunakan persimpangan tersirat. Tambahan tersebut akan hancur sekiranya Excel kini mengembalikan julat Tumpahan.
Terdapat cara baru untuk merujuk kepada rentang yang dikembalikan oleh tatasusunan: =E3#tetapi belum ada nama. The # dipanggil Formula tertumpah Operator . Apa pendapat anda tentang nama seperti Spill Ref (dicadangkan oleh Excel MVP Jon Acampora) atau The Spiller (dicadangkan oleh MVP Ingeborg Hawighorst)?

Sebagai pengarang bersama Pivot Table Data Crunching, saya suka jadual pangsi yang baik. Tetapi bagaimana jika anda memerlukan jadual pangsi anda untuk dikemas kini dan anda tidak boleh mempercayai pengurus pengurus anda untuk mengklik Muat semula? Teknik yang dijelaskan hari ini menawarkan rangkaian tiga formula untuk menggantikan jadual pangsi.

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

Satu formula larik dinamik untuk membuat pelanggan di sisi laporan

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

Untuk kawasan lajur, gunakan TRANSPOSE

Inilah masalahnya: anda tidak tahu berapa tinggi senarai pelanggan. Anda tidak tahu seberapa luas senarai produknya. Sekiranya anda merujuk pada I2 #, Spiller secara automatik akan merujuk pada ukuran semasa array yang dikembalikan.

Formula untuk kembali kawasan Nilai jadual pangsi ialah formula tatasusunan tunggal dalam J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

Dalam bahasa Inggeris, ini mengatakan bahawa anda ingin menambahkan pendapatan dari G2: G564 di mana Pelanggan di E sepadan dengan pelanggan baris semasa dari formula susunan I2 dan produk di B sepadan dengan lajur formula formula sekarang di J1.

Ini adalah formula manis

Bagaimana jika data asas berubah? Saya menambahkan pelanggan baru dan produk baru dengan menukar kedua sel ini di sumbernya.

Tukar beberapa sel dalam data asal

Laporan dikemas kini dengan baris baru dan lajur baru. Rujukan Array-Range I2 # dan J1 # mengendalikan baris dan lajur tambahan.

Laporan tab silang anda berkembang secara automatik dengan data baru

Mengapa SUMIFS berfungsi? Ini adalah konsep dalam Excel yang disebut Penyiaran. Sekiranya anda mempunyai formula yang merujuk kepada dua tatasusunan:

Array satu ialah (27 baris) x (1 lajur)
Array dua ialah (1 baris) x (3 lajur)
Excel akan mengembalikan susunan hasil yang sama tinggi dan lebarnya dengan bahagian tertinggi dan terluas dari tatasusunan yang dirujuk:
Hasilnya akan menjadi (27 baris) x (3 lajur).
Ini dipanggil susunan Penyiaran.