Excel

Rumus array dinamik dalam Excel

Dynamic Array Formulas Excel

Dynamic Arrays adalah perubahan terbesar pada formula Excel selama bertahun-tahun. Mungkin perubahan terbesar yang pernah ada. Ini kerana Dynamic Arrays membolehkan anda bekerja dengan mudah dengan pelbagai nilai sekaligus dalam formula. Bagi banyak pengguna, ini adalah kali pertama mereka memahami dan menggunakan formula larik.





Ini adalah peningkatan besar dan perubahan selamat datang. Dynamic Arrays akan menyelesaikan beberapa masalah yang sangat sukar di Excel, dan secara asasnya mengubah cara lembaran kerja dirancang dan dibina.

Ketersediaan

Susunan dinamik dan fungsi baru di bawah hanya tersedia Excel 365 . Excel 2016 dan Excel 2019 tidak menawarkan sokongan formula larik dinamik. Untuk kemudahan, saya akan menggunakan 'Dynamic Excel' (Excel 365) dan 'Tradisional Excel' (2019 atau lebih awal) untuk membezakan versi di bawah.





Baru: Latihan video Formula Array Dinamik

Fungsi baru

Sebagai sebahagian daripada kemas kini array dinamik, Excel kini menyertakan 8 fungsi baru yang secara langsung memanfaatkan tatasusunan dinamik untuk menyelesaikan masalah yang secara tradisional sukar diselesaikan dengan formula konvensional. Klik pautan di bawah untuk perincian dan contoh untuk setiap fungsi:

Fungsi Tujuan
PENAPIS Tapis data dan kembalikan rekod yang sepadan
RANDARRAY Hasilkan pelbagai nombor rawak
RAHSIA Hasilkan pelbagai nombor turutan
JUAL Isih julat mengikut lajur
DISUSUN MENGIKUT Isih julat mengikut julat atau susunan yang lain
UNIK Ekstrak nilai unik dari senarai atau julat
XLOOKUP Penggantian moden untuk VLOOKUP
XMATCH Penggantian moden untuk fungsi MATCH

Video: Fungsi array dinamik baru dalam Excel (lebih kurang 3 minit).



cara membuat format nombor tersuai di excel

Catatan: XLOOKUP dan XMATCH tidak berada dalam kumpulan asal fungsi array dinamik baru, tetapi berfungsi dengan baik pada enjin array dinamik baru. XLOOKUP menggantikan VLOOKUP dan menawarkan pendekatan moden dan fleksibel yang memanfaatkan tatasusunan. XMATCH adalah peningkatan fungsi MATCH, memberikan kemampuan baru untuk INDEX dan PERTANDINGAN formula.

Contohnya

Sebelum kita mengetahui dengan terperinci, mari kita lihat contoh ringkas. Di bawah ini kita menggunakan yang baru Fungsi UNIK untuk mengekstrak nilai unik dari julat B5: B15, dengan a bujang formula yang dimasukkan dalam E5:

 
= UNIQUE (B5:B15) // return unique values in B5:B15

Contoh fungsi UNIK

Hasilnya adalah senarai lima nama kota yang unik, yang muncul di E5: E9.

Seperti semua formula, UNIK akan dikemas kini secara automatik apabila data berubah. Di bawah ini, Vancouver telah menggantikan Portland pada baris 11. Hasil dari UNIK kini merangkumi Vancouver:

Contoh fungsi UNIK selepas perubahan

Tumpahan - satu formula, banyak nilai

Dalam Excel Dinamik, formula yang mengembalikan banyak nilai akan ' permainan 'nilai-nilai ini terus ke lembaran kerja. Ini akan menjadi lebih logik bagi pengguna formula. Ini juga merupakan tingkah laku dinamik sepenuhnya - apabila data sumber berubah, hasil yang tumpah akan segera dikemas kini.

Segi empat tepat yang merangkumi nilai disebut ' julat permainan '. Anda akan melihat bahawa rentang tumpahan mempunyai penekanan khas. Dalam contoh UNIK di atas, julat tumpahan adalah E5: E10.

Apabila data berubah, rentang tumpahan akan mengembang atau berkontrak sesuai keperluan. Anda mungkin melihat nilai baru ditambahkan, atau nilai yang ada hilang. Dengan cara ini, rentang tumpahan adalah jenis julat dinamik baru.

Catatan: apabila tumpahan disekat oleh data lain, anda akan melihat ralat #SPILL. Sebaik sahaja anda memberi ruang untuk jarak tumpahan, formula akan tumpah secara automatik.

Video: Tumpahan dan jarak tumpahan

Rujukan julat tumpahan

Untuk merujuk kepada rentang tumpahan, gunakan simbol hash (#) setelah sel pertama dalam julat. Sebagai contoh, untuk merujuk hasil dari fungsi UNIK di atas gunakan:

 
=E5# // reference UNIQUE results

Ini sama dengan merujuk keseluruhan rentang tumpahan, dan anda akan melihat sintaks ini semasa anda menulis formula yang merujuk kepada julat tumpahan yang lengkap.

Anda boleh memasukkan rujukan tumpahan ke formula lain secara langsung. Sebagai contoh, untuk mengira jumlah bandar yang dikembalikan oleh UNIK, anda boleh menggunakan:

 
= COUNTA (E5#) // count unique cities

Contoh rujukan julat tumpahan array dinamik

Apabila julat tumpahan berubah, formula akan mencerminkan data terkini.

Penyederhanaan besar-besaran

Penambahan formula susunan dinamik baru bermaksud formula tertentu dapat dipermudahkan secara drastik. Berikut adalah beberapa contoh:

  • Ekstrak dan senaraikan nilai unik ( sebelum ini | selepas )
  • Kira nilai unik ( sebelum ini | selepas )
  • Tapis dan ekstrak rekod ( sebelum ini | selepas )
  • Ekstrak sepadan perlawanan ( sebelum ini | selepas )

Kekuatan satu

Salah satu faedah paling kuat dari pendekatan 'satu formula, banyak nilai' kurang bergantung mutlak atau bercampur rujukan. Sebagai formula array dinamik menumpahkan hasil ke lembaran kerja, rujukan tetap tidak berubah, tetapi formula menghasilkan hasil yang betul.

Sebagai contoh, di bawah ini kita menggunakan fungsi FILTER untuk mengekstrak rekod dalam kumpulan 'A'. Dalam sel F5, satu formula dimasukkan:

 
= FILTER (B5:D11,B5:B11='a') // references are relative

Contoh dinamik satu formula sahaja

Perhatikan kedua-dua julat adalah rujukan relatif yang tidak terkunci, tetapi formula berfungsi dengan sempurna.

Ini adalah faedah besar bagi banyak pengguna, kerana proses pembuatan formula menjadi lebih mudah. Untuk contoh lain yang baik, lihat jadual pendaraban di bawah.

Fungsi rantai

Perkara menjadi sangat menarik apabila anda menyatukan lebih daripada satu fungsi array dinamik. Mungkin anda ingin menyusun keputusan yang dikembalikan oleh UNIK? Mudah. Balut sahaja Fungsi SORT sekitar fungsi UNIK seperti ini:

Contoh UNIK dan SORT bersama

Seperti sebelumnya, apabila data sumber berubah, hasil unik baru muncul secara automatik, disusun dengan baik.

Kelakuan asli

Penting untuk memahami bahawa tingkah laku larik dinamik adalah asli dan bersepadu . Bila ada formula mengembalikan banyak hasil, hasil ini akan tumpah menjadi beberapa sel pada lembaran kerja. Ini termasuk fungsi lama yang pada asalnya tidak dirancang untuk berfungsi dengan tatasusunan dinamik.

Sebagai contoh, dalam Excel Tradisional, jika kita memberikan Fungsi LEN ke julat nilai teks, kita akan melihat a bujang hasil. Dalam Dynamic Excel, jika kita memberi fungsi LEN pelbagai nilai, kita akan lihat pelbagai hasil. Skrin di bawah ini menunjukkan tingkah laku lama di sebelah kiri dan tingkah laku baru di sebelah kanan:

Fungsi LEN dengan tatasusunan - lama dan baru

Ini adalah perubahan besar yang boleh mempengaruhi semua jenis formula. Sebagai contoh, Fungsi VLOOKUP dirancang untuk mengambil satu nilai dari jadual, menggunakan indeks lajur. Walau bagaimanapun, dalam Dynamic Excel, jika kita memberikan VLOOKUP lebih daripada satu indeks lajur menggunakan pemalar larik seperti ini:

 
= VLOOKUP ('jose',F7:H10,{1,2,3},0)

VLOOKUP akan mengembalikan banyak lajur:

Pelbagai hasil dengan susunan VLOOKUP dan dinamik

Dengan kata lain, walaupun VLOOKUP tidak pernah dirancang untuk mengembalikan banyak nilai, kini dapat melakukannya, berkat mesin formula baru di Dynamic Excel.

Semua formula

Akhirnya, perhatikan bahawa tatasusunan dinamik berfungsi semua formula bukan hanya fungsi . Dalam contoh di bawah sel C5 mengandungi formula tunggal:

di mana kunci tatal pada papan kekunci
 
=B5:B14*C4:L4

Hasilnya meluas ke julat 10 hingga 10 yang merangkumi 100 sel:

Jadual pendaraban array dinamik

Catatan: Dalam Excel Tradisional, anda dapat melihat banyak hasil yang dikembalikan oleh formula array jika anda gunakan F9 untuk memeriksa formula . Tetapi melainkan jika anda memasukkan formula sebagai formula pelbagai sel , hanya satu nilai yang akan dipaparkan di lembaran kerja.

Susunan menjadi arus perdana

Dengan pelancaran susunan dinamik, perkataan ' susunan 'akan muncul lebih kerap. Sebenarnya, anda mungkin melihat 'array' dan 'range' digunakan hampir secara bergantian. Anda akan melihat tatasusunan dalam Excel tertutup kurung keriting seperti ini:

 
{1,2,3} // horizontal array {123} // vertical array

Array adalah istilah pengaturcaraan yang merujuk pada senarai item yang muncul dalam urutan tertentu. Sebab susunan sering muncul dalam formula Excel ialah tatasusunan boleh menyatakan nilai dengan sempurna dalam pelbagai sel .

Video: Apakah array itu?

Operasi array menjadi penting

Kerana formula Dynamic Excel dapat berfungsi dengan mudah dengan pelbagai nilai, operasi array akan menjadi lebih penting. Istilah 'operasi array' merujuk kepada ungkapan yang menjalankan ujian logik atau operasi matematik pada array. Sebagai contoh, ungkapan di bawah menguji jika nilai dalam B5: B9 sama dengan 'ca'

 
=B5:B9='ca' // state = 'ca'

Ujian contoh operasi array a

kerana terdapat 5 sel dalam B5: B9, hasilnya adalah 5 nilai BENAR / SALAH dalam suatu array:

 
{FALSETRUEFALSETRUETRUE}

Operasi tatasusunan di bawah memeriksa jumlah yang melebihi 100:

 
=C5:C9>100 // amounts > 100

Ujian contoh operasi array b

Operasi susunan terakhir menggabungkan ujian A dan ujian B dalam satu ungkapan:

 
=(B5:B9='ca')*(C5:C9>100) // state = 'ca' and amount > 100

Ujian contoh operasi array a dan b

Catatan: Excel secara automatik memaksakan nilai BENAR dan SALAH kepada 1 dan 0 semasa operasi matematik.

Untuk mengembalikan ini ke formula array dinamik di Excel, contoh di bawah menunjukkan bagaimana kita dapat menggunakan operasi array yang sama persis di dalam fungsi FILTER seperti merangkumi hujah:

Array operasi dengan fungsi FILTER

FILTER mengembalikan dua rekod di mana keadaan = 'ca' dan jumlah> 100.

Untuk demonstrasi, lihat: Cara menapis dengan dua kriteria (video).

Rumus susunan baru dan lama

Dalam Dynamic Excel, tidak perlu memasukkan formula array dengan control + shift + enter. Semasa formula dibuat, Excel memeriksa apakah formula itu mungkin mengembalikan beberapa nilai. Sekiranya demikian, ia akan disimpan secara automatik sebagai formula larik dinamik, tetapi anda tidak akan melihat pendakap kerinting. Contoh di bawah menunjukkan formula array khas yang dimasukkan dalam Dynamic Excel:

Formula array asas dalam Excel tradisional

Sekiranya anda membuka formula yang sama dalam Tradisional Excel, anda akan melihat pendakap keriting:

Formula array asas dalam Excel dinamik

Ke arah lain, apabila formula larik 'tradisional' dibuka di Dynamic Excel, anda akan melihat pendakap keriting di bar formula. Contohnya, skrin di bawah menunjukkan formula susunan mudah dalam Excel Tradisional:

Rumus susunan ringkas dengan pendakap keriting kelihatan

Walau bagaimanapun, jika anda memasukkan semula formula tanpa perubahan, pendakap keriting dikeluarkan, dan formula mengembalikan hasil yang sama:

Rumus susunan ringkas dengan pendakap kerinting tidak kelihatan

Intinya ialah formula array yang dimasukkan dengan control + shift + enter (CSE) masih berfungsi untuk mengekalkan keserasian, tetapi anda tidak perlu memasukkan formula array dengan CSE di Dynamic Excel.

Watak @

Dengan pengenalan susunan dinamik, anda akan melihat watak @ muncul lebih kerap dalam formula. Watak @ membolehkan tingkah laku yang dikenali sebagai ' persimpangan tersirat '. Persimpangan tersirat adalah proses logik di mana banyak nilai dikurangkan menjadi satu nilai.

Dalam Excel Tradisional, persimpangan tersirat adalah tingkah laku diam yang digunakan (apabila perlu) untuk mengurangkan beberapa nilai menjadi satu hasil dalam satu sel. Dalam Excel Dinamik, biasanya tidak diperlukan, kerana banyak hasil dapat tumpah ke lembaran kerja. Apabila diperlukan, persimpangan tersirat dipanggil secara manual dengan watak @.

Semasa membuka spreadsheet membuat versi Excel yang lebih lama, anda mungkin melihat watak @ ditambahkan secara automatik ke formula yang ada yang memiliki potensi untuk mengembalikan banyak nilai. Dalam Excel Tradisional, formula yang mengembalikan banyak nilai tidak akan tumpah pada lembaran kerja. Watak @ memaksa tingkah laku yang sama dalam Dynamic Excel sehingga formula berkelakuan sama dan mengembalikan hasil yang sama seperti yang berlaku dalam versi Excel yang asal.

Dengan kata lain, @ ditambahkan untuk mengelakkan formula lama daripada menumpahkan banyak hasil ke lembaran kerja. Bergantung pada formula, anda mungkin dapat membuang watak @ dan tingkah laku formula tidak akan berubah.

Ringkasan

  • Array Dinamik akan menjadikan formula tertentu lebih mudah ditulis.
  • Anda kini boleh menapis data padanan, mengisih, dan mengekstrak nilai unik dengan mudah dengan formula.
  • Rumus Dynamic Array boleh dirantai (bersarang) untuk melakukan perkara seperti tapisan dan urutan.
  • Formula yang mengembalikan lebih daripada satu nilai akan tumpah secara automatik.
  • Tidak perlu menggunakan Ctrl + Shift + Enter untuk memasukkan formula larik.
  • Rumus array dinamik hanya tersedia di Excel 365.
Pengarang Dave Bruns


^