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
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:
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
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
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:
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:
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:
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:
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'
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
Operasi susunan terakhir menggabungkan ujian A dan ujian B dalam satu ungkapan:
=(B5:B9='ca')*(C5:C9>100) // state = 'ca' and amount > 100
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:
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:
Sekiranya anda membuka formula yang sama dalam Tradisional Excel, anda akan melihat pendakap keriting:
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:
Walau bagaimanapun, jika anda memasukkan semula formula tanpa perubahan, pendakap keriting dikeluarkan, dan formula mengembalikan hasil yang sama:
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.