Arahan, fungsi dan formula dinamik Excel

Kemas kini enjin pengkomputeran revolusioner Excel 365 menjadikan formula array mudah difahami, tidak lagi hanya kemahiran khusus untuk pengguna lanjutan. Tutorial ini menerangkan konsep tatasusunan dinamik Excel dan menunjukkan cara menggunakannya untuk meningkatkan kecekapan lembaran kerja dan memudahkan tetapan.

Formula Array Excel selalu dianggap sebagai paten pakar dan pakar formula. Kalimat "boleh diselesaikan dengan formula array" sering menyebabkan ramai pengguna segera menjawab: "Adakah terdapat cara lain?"

Pengenalan susunan dinamik adalah perubahan yang lama dan sangat dialu-alukan. Kerana mereka dapat mengendalikan pelbagai nilai dengan cara yang mudah, tanpa sebarang helah dan helah, setiap pengguna Excel dapat memahami dan mudah membuat formula array dinamik.

- Excel array dinamik

• Ketersediaan array dinamik
• Fungsi array dinamik
• Contoh formula array dinamik
• Kawasan Limpahan - Satu formula, pelbagai sel
• Rujukan Kawasan Limpahan (# simbol)
• Persimpangan tersirat dan @ aksara
• Kelebihan Array Dinamik
• Batasan susunan dinamik
• Arahan dinamik dan formula array CSE tradisional
• Keserasian ke belakang
• Formula Array Dynamic Excel tidak sah

Excel array dinamik

Arahan dinamik adalah susunan saiz semula yang mengira dan mengembalikan nilai secara automatik ke pelbagai sel, semuanya berdasarkan formula yang dimasukkan dalam sel tunggal.

Microsoft Excel telah mengalami banyak perubahan sejak tiga puluh tahun yang lalu, tetapi satu perkara yang konsisten: formula, sel. Walaupun dengan formula array tradisional, anda perlu memasukkan formula di setiap sel di mana anda ingin memaparkan hasilnya. Dengan susunan dinamik, peraturan ini tidak lagi berlaku. Sekarang mana -mana formula yang mengembalikan nilai array secara automatik akan melimpah ke sel -sel bersebelahan tanpa menekan shift Ctrl masukkan atau melakukan apa -apa lagi. Dalam erti kata lain, memanipulasi pelbagai dinamik adalah semudah memanipulasi sel tunggal.

Mari kita gunakan contoh yang sangat asas untuk menggambarkan konsep ini. Katakan anda perlu melipatgandakan dua set nombor, sebagai contoh, mengira peratusan yang berbeza.

Dalam versi Excel yang tidak dinamik, formula berikut hanya terpakai kepada sel pertama kecuali anda memasukkannya dalam pelbagai sel dan tekan CTRL Shift Enter untuk secara eksplisit menetapkannya sebagai formula array:

=A3:A5*B2:D2

Sekarang, lihat apa yang berlaku apabila menggunakan formula yang sama dalam Excel 365. Anda hanya memasukkannya ke dalam sel (B3 dalam kes ini), tekan Enter ... seluruh kawasan akan segera mengisi hasilnya:

Mengisi pelbagai sel dengan formula tunggal dipanggil limpahan , dan kawasan sel yang penuh dipanggil limpahan.

Harus diingat bahawa kemas kini baru -baru ini adalah lebih daripada sekadar cara baru untuk menangani tatasusunan Excel. Malah, ini adalah perubahan terobosan kepada keseluruhan enjin pengkomputeran. Dengan tatasusunan dinamik, satu siri fungsi baru telah ditambah ke perpustakaan fungsi Excel, dan fungsi yang sedia ada juga telah mula berjalan lebih cepat dan lebih cekap. Pada akhirnya, array dinamik baru harus menggantikan formula pelbagai gaya lama yang dimasukkan menggunakan shift Ctrl Enter Shortcut.

Ketersediaan Array Dinamik Excel

Arahan dinamik dilancarkan pada Persidangan Microsoft Ignite pada tahun 2018 dan dikeluarkan kepada pelanggan Office 365 pada bulan Januari 2020. Pada masa ini, mereka boleh didapati dalam langganan Microsoft 365 dan Excel 2021.

Versi berikut menyokong array dinamik:

• Excel 365 untuk Windows
• Excel 365 untuk Mac
• Excel 2021
• Excel 2021 untuk Mac
• Excel untuk iPad
• Excel untuk iPhone
• Versi tablet Android Excel
• Versi mudah alih Android dari Excel
• Excel di Web

Fungsi array dinamik excel

Sebagai sebahagian daripada fungsi baru, 6 fungsi baru diperkenalkan dalam Excel 365 yang boleh memproses array dan data output secara asli ke julat sel. Output sentiasa dinamik - hasilnya dikemas kini secara automatik apabila sebarang perubahan berlaku dalam data sumber. Oleh itu, nama kumpulan itu - fungsi array dinamik .

Fungsi -fungsi baru ini dapat dengan mudah menangani banyak tugas yang secara tradisinya dianggap sukar untuk diselesaikan. Sebagai contoh, mereka boleh memadam pendua, mengekstrak dan mengira nilai unik, menapis nilai null, menghasilkan bilangan bulat rawak dan perpuluhan, menyusun susunan menaik atau menurun, dan banyak lagi.

Berikut adalah pengenalan ringkas kepada fungsi setiap fungsi dan pautan ke tutorial yang mendalam:

• Unik - Ekstrak item unik dari julat sel.
• Penapis - Data penapis berdasarkan kriteria yang anda tentukan.
• Sort - menyusun julat sel oleh lajur yang ditentukan.
• Sortby - menyusun julat sel oleh kawasan lain atau array.
• Randarray - menjana pelbagai nombor rawak.
• Urutan - Menjana senarai nombor siri.
• TextSplit - Pecahkan rentetan ke dalam lajur atau/dan baris oleh pembatas yang ditentukan.
• TOCOL - Tukar array atau rantau ke lajur tunggal.
• Torow - Menukar rantau atau array ke satu baris.
• WrapCols - Menukar baris atau lajur ke array dua dimensi berdasarkan bilangan nilai yang ditentukan untuk setiap baris.
• Wraprows - Buat semula baris atau lajur ke dalam array dua dimensi berdasarkan bilangan nilai yang ditentukan untuk setiap lajur.
• Ambil - Ekstrak bilangan baris atau lajur berturut -turut yang ditentukan dari awal atau akhir array.
• Drop - Menghapuskan sejumlah baris atau lajur tertentu dari array.
• Kembangkan - Memperluas array ke bilangan baris dan lajur yang ditentukan.
• ChoOsecols - Mengembalikan lajur yang ditentukan dari array.
• Chooserows - Ekstrak baris yang ditentukan dari array.
• Groupby - Kumpulan dan agregat data mengikut baris berdasarkan nilai dalam satu atau lebih lajur.
• Pivotby - Data kumpulan mengikut baris dan lajur dan agregat nilai yang berkaitan.

Di samping itu, terdapat dua alternatif moden untuk fungsi Excel popular yang tidak secara rasmi tergolong dalam kumpulan, tetapi boleh memanfaatkan semua manfaat array dinamik:

• XLOOKUP - adalah pengganti yang lebih kuat untuk VLOOKUP, HOLLOW dan LINEUP, yang dapat mencari dan mengembalikan pelbagai nilai dalam lajur dan baris.
• Xmatch - adalah pengganti yang lebih umum untuk fungsi fungsi perlawanan, yang melakukan carian menegak dan mendatar dan mengembalikan kedudukan relatif item yang ditentukan.

Formula Array Dinamik Excel

Dalam versi moden Excel, tingkah laku array dinamik telah terintegrasi dan menjadi berasal dari semua fungsi , walaupun fungsi -fungsi ini tidak pada asalnya direka untuk bekerja dengan tatasusunan. Singkatnya, untuk mana -mana formula yang mengembalikan pelbagai nilai, Excel secara automatik mewujudkan kawasan saiz yang dihasilkan hasilnya. Oleh kerana keupayaan ini, fungsi sedia ada kini boleh memainkan peranan ajaib!

Contoh berikut menunjukkan aplikasi praktikal formula array dinamik dan kesan tatasusunan dinamik pada fungsi sedia ada.

Contoh 1. Fungsi Arus Dinamik Baru

Contoh ini menunjukkan betapa cepat dan mudah penyelesaian dapat dicapai dengan menggunakan fungsi array dinamik Excel.

Untuk mengekstrak senarai nilai unik dari lajur, anda biasanya menggunakan formula CSE yang kompleks seperti yang ditunjukkan di bawah. Dalam Excel Dinamik, anda hanya memerlukan bentuk asas formula unik:

=UNIQUE(B2:B10)

Anda memasukkan formula di mana -mana sel kosong dan tekan Enter. Excel segera mengekstrak semua nilai yang berbeza dalam senarai dan mengeluarkannya ke julat sel bermula dari sel di mana anda memasuki formula (dalam kes ini D2). Apabila data sumber berubah, hasilnya secara automatik dikira semula dan dikemas kini.

Contoh 2. Menggabungkan pelbagai fungsi array dinamik dalam satu formula

Sekiranya tidak ada cara untuk menyelesaikan tugas dengan satu fungsi, anda boleh menghubungkan beberapa fungsi bersama -sama! Sebagai contoh, untuk menapis data berdasarkan keadaan dan menyusun keputusan mengikut abjad, anda boleh membungkus fungsi jenis di sekitar penapis seperti berikut:

=SORT(FILTER(A2:C13, B2:B13=F1, "No results"))

Di mana A2: C13 adalah data sumber, B2: B13 adalah nilai yang akan diperiksa, dan F1 adalah keadaan.

Contoh 3. Menggunakan fungsi array dinamik baru dengan fungsi yang ada

Oleh kerana enjin pengkomputeran baru yang dilaksanakan di Excel 365 dengan mudah dapat menukar formula tradisional ke dalam tatasusunan, tidak ada yang menghalang anda daripada mengumpulkan fungsi baru dan lama bersama -sama.

Sebagai contoh, untuk mengira berapa banyak nilai unik di rantau ini, anda boleh sarang fungsi unik dinamik dalam fungsi Counta lama:

=COUNTA(UNIQUE(B2:B10))

Contoh 4. Fungsi sedia ada menyokong tatasusunan dinamik

Jika anda menyediakan julat sel kepada fungsi trim dalam versi Excel yang lebih lama (seperti Excel 2016 atau Excel 2019), ia akan mengembalikan hasil tunggal untuk sel pertama:

=TRIM(A2:A6)

Dalam Excel dinamik, formula yang sama memproses semua sel dan mengembalikan pelbagai hasil seperti berikut:

Contoh 5. Formula Vlookup yang mengembalikan pelbagai nilai

Seperti yang kita ketahui, fungsi VLOOKUP direka untuk mengembalikan nilai tunggal berdasarkan indeks lajur yang anda tentukan. Walau bagaimanapun, dalam Excel 365, anda boleh menyediakan satu set nombor lajur untuk mengembalikan perlawanan dari beberapa lajur:

=VLOOKUP(F1, A2:C6, {1,2,3}, FALSE)

Contoh 6. Formula transpose yang dipermudahkan

Dalam versi Excel terdahulu, sintaks fungsi transpose tidak boleh bebas ralat. Untuk memutar data dalam lembaran kerja, anda perlu mengira lajur dan baris asal, pilih bilangan sel kosong yang sama tetapi tukar orientasi (ia adalah operasi yang membingungkan dalam lembaran kerja besar!), Taipkan formula transpose di kawasan yang dipilih, dan tekan CTRL Shift masukkan untuk diselesaikan dengan betul. Hubungi!

Dalam Excel Dinamik, anda hanya memasukkan formula di sel paling kiri kawasan output dan tekan Enter:

=TRANSPOSE(A1:B6)

Selesaikan!

Kawasan Limpahan - Satu formula, pelbagai sel

Kawasan limpahan adalah kawasan sel yang mengandungi nilai -nilai yang dikembalikan oleh formula array dinamik.

Apabila memilih mana -mana sel di kawasan limpahan, sempadan biru nampaknya menunjukkan bahawa segala -galanya di dalamnya dikira oleh formula di sel kiri atas. Jika anda memadam formula di sel pertama, semua keputusan akan hilang.

Kawasan limpahan adalah ciri hebat yang memudahkan pengguna Excel. Sebelum ini, apabila menggunakan formula Array CSE, kita perlu meneka berapa banyak sel yang kita mahu menyalinnya. Sekarang anda hanya memasukkan formula di sel pertama dan biarkan Excel mengendalikan selebihnya.

Notis. Jika data lain menghalang kawasan limpahan, ralat #Spill berlaku. Setelah menyekat data dikeluarkan, ralat akan hilang.

Untuk maklumat lanjut, lihat Kawasan Limpahan Excel.

Rujukan Kawasan Limpahan (# simbol)

Untuk merujuk seluruh kawasan limpahan yang dikembalikan oleh formula array yang dinamik, tambahkan tanda paun atau satu paun (#) selepas alamat sel di sudut kiri atas kawasan tersebut.

Sebagai contoh, untuk mengetahui berapa banyak nombor rawak yang dihasilkan oleh formula RandArray dalam A2, memberikan rujukan kawasan limpahan kepada fungsi Counta:

=COUNTA(A2#)

Untuk menambah nilai di kawasan limpahan, gunakan:

=SUM(A2#)

Petunjuk:

• Untuk dengan cepat merujuk kawasan limpahan, pilih semua sel di dalam kotak biru dengan tetikus anda dan Excel akan membuat rujukan limpahan untuk anda.
• Tidak seperti rujukan kawasan biasa, rujukan kawasan limpahan dinamik dan secara automatik akan bertindak balas terhadap saiz semula kawasan. Untuk maklumat lanjut, lihat Operator Wilayah Overflow.

Persimpangan tersirat dan @ aksara

Dalam Excel Array Dynamic, bahasa formula mempunyai satu lagi perubahan penting - aksara @ diperkenalkan, yang dipanggil pengendali persimpangan tersirat .

Dalam Microsoft Excel, persimpangan tersirat adalah tingkah laku formula yang mengurangkan pelbagai nilai kepada satu nilai. Dalam Excel yang lebih tua, sel -sel hanya boleh mengandungi nilai tunggal, jadi ini adalah tingkah laku lalai dan tiada pengendali khas diperlukan.

Dalam versi baru Excel, semua formula dianggap sebagai formula array secara lalai. Jika anda tidak mahu tingkah laku array digunakan dalam formula tertentu, anda boleh menggunakan pengendali persimpangan tersirat. Dalam erti kata lain, jika anda mahu formula untuk mengembalikan hanya satu nilai, awalan nama fungsi dengan @ dan ia akan berjalan seperti formula bukan panah dalam Excel tradisional.

Untuk melihat bagaimana ia berfungsi dalam amalan, lihat tangkapan skrin di bawah.

Dalam C2, terdapat formula array dinamik yang melimpah hasilnya ke banyak sel:

=UNIQUE(A2:A9)

Dalam E2, fungsi ini diawali dengan @ aksara, yang memanggil persimpangan tersirat. Akibatnya, hanya nilai unik yang pertama dikembalikan:

=@UNIQUE(A2:A9)

Untuk maklumat lanjut, lihat persimpangan tersirat dalam Excel.

Kelebihan array dinamik Excel

Tidak ada keraguan bahawa tatasusunan dinamik adalah salah satu peningkatan terbaik dalam Excel selama bertahun -tahun. Seperti mana -mana ciri baru, mereka mempunyai kebaikan dan keburukan. Nasib baik, kelebihan formula Array Dynamic Excel yang baru sangat menggembirakan!

Mudah dan berkuasa

Arus dinamik menjadikannya lebih mudah untuk mencipta formula yang lebih kuat. Berikut adalah beberapa contoh:

• Ekstrak Nilai Unik: Formula Tradisional | Fungsi array dinamik
• Kira nilai unik dan nilai yang berbeza: Formula tradisional | Fungsi array dinamik
• Susun Lajur Abjad: Formula Tradisional | Fungsi array dinamik

Sokongan asli untuk semua formula

Dalam dinamik Excel, anda tidak perlu bimbang tentang fungsi sokongan fungsi dan fungsi mana yang tidak. Jika formula boleh mengembalikan pelbagai nilai, ia akan melakukannya secara lalai. Ini juga terpakai kepada operasi aritmetik dan fungsi tradisional, seperti yang ditunjukkan dalam contoh ini.

Fungsi array dinamik bersarang

Untuk menyelesaikan penyelesaian tugas yang lebih kompleks, anda boleh menggabungkan fungsi array dinamik Excel secara bebas, atau menggunakannya dengan fungsi lama, seperti yang ditunjukkan di sini dan di sini.

Petikan relatif dan mutlak tidak begitu penting

Kerana pendekatan "satu formula, pelbagai nilai", tidak perlu mengunci kawasan dengan simbol $ kerana secara teknikal formula hanya dalam satu sel. Oleh itu, dalam kebanyakan kes, ia tidak penting untuk menggunakan rujukan sel mutlak, relatif, atau campuran (yang sentiasa menjadi sumber kekeliruan untuk pemula) - Formula array dinamik akan menghasilkan hasil yang betul!

Batasan susunan dinamik

Arahan dinamik baru adalah hebat, tetapi seperti mana -mana ciri baru, terdapat beberapa nota.

Hasilnya tidak dapat disusun dengan cara biasa

Kawasan limpahan yang dikembalikan oleh formulas array dinamik tidak dapat disusun menggunakan fungsi penyortiran Excel. Sebarang percubaan sedemikian akan mengakibatkan " tidak dapat mengubah sebahagian daripada array " ralat. Untuk mengatur hasil dari kecil ke besar atau dari besar ke kecil, bungkus formula semasa dalam fungsi jenis. Sebagai contoh, ini adalah bagaimana anda boleh menapis dan menyusun pada masa yang sama.

Tidak dapat memadam sebarang nilai di kawasan limpahan

Atas sebab yang sama, adalah mustahil untuk memadamkan sebarang nilai di kawasan limpahan: sebahagian daripada array tidak dapat diubah. Tingkah laku ini dijangka dan logik. Begitu juga dengan formula array CSE tradisional.

Tidak disokong oleh borang Excel

Ciri ini (atau kesilapan?) Agak tidak dijangka. Formula array dinamik tidak berfungsi dalam jadual Excel, hanya untuk kawasan biasa. Jika anda cuba menukar kawasan limpahan ke meja, Excel melakukan ini. Walau bagaimanapun, anda hanya akan melihat #spill! Ralat, tidak hasilnya.

Tidak tersedia untuk pertanyaan kuasa excel

Hasil formulas array dinamik tidak dapat dimuatkan ke dalam pertanyaan kuasa. Sebagai contoh, jika anda cuba menggabungkan dua atau lebih kawasan limpahan bersama -sama menggunakan pertanyaan kuasa, operasi ini tidak berfungsi.

Arahan dinamik dan formula array CSE tradisional

Dengan pengenalan tatasusunan dinamik, kita boleh membincangkan dua jenis Excel:

1. Dynamic Excel sepenuhnya menyokong tatasusunan dinamik, fungsi dan formula. Pada masa ini hanya Excel 365 dan Excel 2021.
2. Excel tradisional , juga dikenali sebagai Excel bukan dinamik, hanya menyokong CTRL Shift Enter Formula Array. Ia adalah Excel 2019, Excel 2016, Excel 2013 dan lebih awal.

Tidak perlu dikatakan, array dinamik mengatasi formula pelbagai CSE dalam setiap cara. Walaupun formula pelbagai tradisional dipelihara untuk sebab -sebab keserasian, mulai sekarang, disyorkan untuk menggunakan formula array baru.

Berikut adalah perbezaan yang paling penting:

• Formula array dinamik dimasukkan ke dalam sel dan dilakukan menggunakan kekunci Enter biasa. Untuk melengkapkan formula array gaya lama, anda perlu menekan CTRL Shift Enter.
• Formula array baru secara automatik akan melimpah ke pelbagai sel. Formula CSE mesti disalin ke julat sel untuk mengembalikan pelbagai hasil.
• Output formula array dinamik secara automatik diubahsuai sebagai data di kawasan sumber berubah. Sekiranya kawasan pulangan terlalu kecil, formula CSE memotong output; Sekiranya kawasan pulangan terlalu besar, kesilapan akan dikembalikan di sel -sel yang berlebihan.
• Formula array dinamik boleh diedit dengan mudah dalam satu sel. Untuk mengubahsuai formula CSE, anda perlu memilih dan mengedit keseluruhan kawasan.
• Ia tidak mungkin untuk memadam dan memasukkan baris di kawasan formula CSE - anda perlu memadam semua formula sedia ada terlebih dahulu. Menggunakan tatasusunan dinamik, memasukkan atau memadam baris tidak menjadi masalah.

Keserasian ke belakang: Array Dinamik dalam Excel Tradisional

Apabila anda membuka buku kerja yang mengandungi formula array dinamik di Excel yang lebih tua, ia secara automatik ditukar kepada formula array biasa yang tertutup dalam pendakap {}. Apabila anda membuka lembaran kerja lagi dalam versi baru Excel, pendakap akan dipadamkan.

Dalam Excel tradisional, fungsi array dinamik baru dan rujukan kawasan limpahan didahului oleh _xlfn untuk menunjukkan bahawa ciri ini tidak disokong. Simbol Rujukan Kawasan Limpahan (#) akan digantikan oleh fungsi AnchorArray.

Sebagai contoh, berikut adalah bagaimana formula unik dipaparkan dalam Excel 2013 :

Rumusan array yang paling dinamik (tetapi tidak semua!) Akan terus memaparkan hasil mereka dalam Excel tradisional sehingga anda membuat sebarang perubahan kepada mereka. Adakah mengedit formula segera memecahkannya dan memaparkan satu atau lebih #names? Nilai ralat.

Formula Array Dynamic Excel tidak sah

Bergantung pada fungsi, kesilapan yang berbeza mungkin berlaku jika sintaks yang salah atau parameter tidak sah digunakan. Berikut adalah tiga kesilapan yang paling biasa yang boleh anda hadapi apabila menggunakan sebarang formula array dinamik.

#Spill! Ralat

#Spill berlaku apabila array dinamik mengembalikan pelbagai hasil, tetapi sesuatu menghalang kawasan limpahan! kesilapan.

Untuk menetapkan kesilapan ini, anda hanya membersihkan atau memadam mana -mana sel di kawasan limpahan yang tidak sepenuhnya kosong. Untuk cepat mencari semua sel yang berada di jalan, klik penunjuk ralat dan klik Pilih Menyekat Sel .

Sebagai tambahan kepada kawasan limpahan yang tidak kosong, kesilapan ini boleh disebabkan oleh beberapa sebab lain. Untuk maklumat lanjut, lihat:

• Excel #Spill ERROR - Punca dan penyelesaian
• Bagaimana untuk memperbaiki #spill dalam vlookup, perlawanan indeks, sumif! kesilapan

#Ref! Ralat

Oleh kerana sokongan terhad untuk rujukan luaran antara buku kerja, tatasusunan dinamik memerlukan dua fail untuk dibuka pada masa yang sama. Jika buku kerja sumber ditutup, #REF akan dipaparkan! kesilapan.

#Name? Ralat

Jika anda cuba menggunakan fungsi array dinamik dalam versi lama Excel, #name berlaku? kesilapan. Ingat bahawa fungsi baru hanya tersedia dalam Excel 365 dan Excel 2021.

Jika ralat ini berlaku dalam versi Excel yang disokong, semak semula nama fungsi dalam sel yang dipersoalkan. Mungkin ia dimasukkan dengan tidak betul :)

Inilah cara menggunakan array dinamik dalam Excel. Semoga anda menikmati ciri baru yang hebat ini! Bagaimanapun, terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Atas ialah kandungan terperinci Arahan, fungsi dan formula dinamik Excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!