Cara menggunakan formula tatasusunan dinamik dalam Microsoft Excel

Jika anda menggunakan hamparan setiap hari, anda akan perasan bahawa sebaik sahaja ia mula berkembang dalam baris dan lajur, Formula Excel klasik agak kurang memuaskan. untuk melakukan pengiraan yang kompleks dengan selesa. Di sinilah formula matriks dinamik memainkan peranan, salah satu perkara baharu yang, setelah anda menguasainya, akan anda gunakan dalam hampir setiap buku.

Dalam versi Excel moden (terutamanya dalam Microsoft 365Kami mempunyai enjin pengiraan baharu yang membolehkan satu formula menjana berbilang hasil sekaligus, merangkumi beberapa sel bersebelahan tanpa memerlukan penyalinan manual. Hasil daripada tingkah laku ini, "limpahan" hasil secara keseluruhanKini lebih mudah untuk menyusun, menapis, menjana senarai atau melakukan pengiraan lanjutan tanpa perlu menggunakan Trik jarang atau kombinasi kekunci seperti Ctrl+Shift+Enter.

Apakah limpahan dalam formula matriks dinamik?

Dalam model pengiraan baharu Excel, formula boleh mengembalikan bukan sahaja satu nilai, tetapi satu set hasil yang teratur; set ini biasanya dipanggil tatasusunan atau julat output yang melimpahApabila ini berlaku, Excel secara automatik meletakkan nilai-nilai tersebut dalam sel berdekatan, mengembangkan formula ke bawah, ke kanan, atau ke kedua-dua arah, bergantung pada saiz hasilnya.

Contohnya, semasa menulis formula =SORT(D2:D11,1,-1) Dalam satu sel (contohnya, F2), Excel menjana senarai tersusun menurun berdasarkan julat D2:D11. Hasilnya menempati 10 baris, tetapi anda hanya memasukkan formula dalam satu sel; kedudukan selebihnya diisi oleh Excel sendiri menggunakan mekanisme limpahan ini.

Formula yang boleh mengubah saiz hasil carian berdasarkan data sumber dikenali sebagai formula matriks dinamikApabila formula ini mengembalikan julat hasil yang melangkaui sel tempat formula ditulis, dikatakan bahawa formula telah "melimpah" dan kawasan yang didudukinya dipanggil julat limpahan.

Dalam praktiknya, ini bermakna banyak fungsi, seperti SORT, FILTER, RANDOMAR, SEQUENCE atau UNIQUE, kini direka bentuk untuk mengembalikan matriks data sedia untuk digunakan yang lengkap, dengan cara yang lebih langsung dan mudah dibaca berbanding dengan formula matriks lama.

Bagaimana julat limpahan bertindak dalam Excel

Apabila anda mengesahkan formula tatasusunan dinamik dengan hanya menekan kekunci Enter, Excel menganalisis hasilnya dan melaraskan saiz julat output secara automatik untuk menampung semua nilai yang dikembalikan oleh formula. Kemudian, letakkan setiap elemen tatasusunan ke dalam sel yang sepadan dalam julat limpahan tersebut.

Jika anda menulis salah satu formula ini pada senarai atau jadual data, ia boleh menjadi agak praktikal untuk menukar data sumber kepada Jadual Excel dengan rujukan berstrukturJadual akan menyesuaikan diri secara automatik apabila anda menambah atau mengalih keluar baris, jadi formula tatasusunan dinamik yang menggunakannya akan dikemas kini tanpa anda perlu menyentuh apa-apa.

Adalah penting untuk mengetahui bahawa formula limpahan tidak berfungsi dalam jadual itu sendiri: Limpahan tidak dibenarkan dalam jadual Excel.Sebaliknya, anda harus meletakkan formula tersebut dalam grid biasa (di luar jadual) dan menggunakan jadual tersebut hanya sebagai sumber data. Jadual bertujuan untuk menyimpan rekod, bukan untuk mengembangkan keseluruhan julat output di dalamnya.

Setiap kali anda mengklik mana-mana sel dalam julat limpahan, Excel akan melukis kotak yang diserlahkan di sekeliling keseluruhan set sel yang terjejas. Ini menjadikannya sangat mudah untuk dilihat sepintas lalu. sejauh mana formula itu meluas dan sel mana yang bergantung padanyaSebaik sahaja anda memilih sel di luar julat itu, sempadan akan hilang.

Satu lagi butiran penting ialah, dalam julat limpahan, hanya sel pertama (yang berada di sudut kiri atas) yang sebenarnya mengandungi formula. Sel-sel lain memaparkan hasil, tetapi jika anda memilih mana-mana daripadanya, anda akan melihat formula dimalapkan dalam bar formula. Anda tidak akan dapat mengubahnya secara langsung.Anda mesti sentiasa mengedit sel sumber; selepas menukarnya dan menekan Enter, Excel akan mengira semula keseluruhan julat limpahan sekaligus.

Ralat pertindihan dan mesej #LIMPAHAN!

Agar formula tatasusunan dinamik dapat dikembangkan dengan lancar, Excel memerlukan kawasan output yang jelas. Jika data, formula atau elemen lain menduduki mana-mana sel di mana keputusan sepatutnya dipaparkan, ralat akan berlaku. Pertindihan julat limpahan dan Excel tidak dapat menyelesaikan operasi tersebut.

Dalam kes itu, anda akan melihat mesej ralat dan bukannya hasil yang dijangkakan. #LIMPAHAN! dalam sel tempat anda memasukkan formula. Ini adalah cara Excel memaklumkan anda bahawa terdapat kunci yang menghalang tatasusunan daripada diletakkan dalam semua sel yang diperlukan untuk menyimpan hasilnya.

Jika anda memilih formula yang dimaksudkan, Excel akan menunjukkan kepada anda, dengan sempadan bertitik, julat tempat ia ingin melimpah, termasuk sebarang sel yang menyekat proses tersebut. Paparan ini membolehkan anda mudah mencari sel-sel yang bermasalah supaya ia boleh dikosongkan atau kandungannya dipindahkan ke lokasi lain.

Sebaik sahaja anda mengalih keluar data yang menghalang pengembangan (contohnya, nilai sesat atau formula sebelumnya), apabila anda mengira semula helaian, Excel akan menyebabkan formula melimpah seperti yang dimaksudkan. Dalam banyak situasi, hanya memadam beberapa sel sudah cukup untuk menjadikan ralat #OVERFLOW! hilang serta-merta.

Ada juga kemungkinan formula itu sendiri direka bentuk dengan salah dan mengembalikan tatasusunan yang terlalu besar untuk ruang yang tersedia. Dalam kes ini, adalah dinasihatkan untuk menyemak semula formula tersebut. kedua-dua logik formula dan ruang kosong di sekelilingnya bagi memastikan julat output dapat berkembang mengikut keperluan.

Perbezaan antara formula matriks dinamik dan formula CSE legasi

Sebelum kemunculan matriks dinamik, formula matriks telah diperkenalkan dengan gabungan yang terkenal Ctrl+Shift+Enter (CSE)Formula legasi ini masih disokong dalam Excel atas sebab keserasian ke belakang, tetapi pendekatan yang disyorkan hari ini adalah untuk menggunakan model dinamik baharu, yang lebih mudah dan kurang terdedah kepada ralat.

Salah satu kelebihan besar formula matriks dinamik ialah Anda hanya perlu memasukkan formula sekali sahaja di sel kiri atas.Keputusan yang selebihnya dipaparkan secara automatik hasil daripada limpahan. Dalam formula CSE yang lebih lama, anda perlu memilih keseluruhan julat tempat anda mahu keputusan dipaparkan dan kemudian sahkan formula dengan Ctrl+Shift+Enter.

Formula dinamik juga boleh melaraskan saiznya apabila data sumber berubah. Jika anda menambah lebih banyak baris pada sumber data, tatasusunan boleh berkembang; jika anda memadam data, ia boleh mengecut—semuanya tanpa anda perlu mengedit julat secara manual. Dengan tatasusunan CSE legasi, jika kawasan pemulangan terlalu kecilHasilnya telah dipendekkan; dan jika ia berlebihan, anda mungkin menghadapi ralat seperti #N/A.

Satu lagi perbezaan yang menarik ialah banyak fungsi klasik, seperti RAND, ROW atau COLUMN, kini dinilai dalam konteks sel tunggal (1x1). Jika anda ingin menjana berbilang hasil rawak atau jujukan nombor berdasarkan baris dan lajur, adalah disyorkan untuk menggunakan fungsi seperti MATRIKS RAWAK atau JURUTANyang direka bentuk untuk mengembalikan matriks lengkap dan berfungsi dengan sempurna dengan enjin dinamik baharu.

Selain itu, pada masa lalu terdapat fenomena yang dikenali sebagai "CSE break", di mana formula matriks legasi tertentu yang bergantung antara satu sama lain boleh dikira secara bebas dan mengembalikan keputusan yang tidak konsisten atau sukar untuk dinyahpepijatDengan tatasusunan dinamik, tingkah laku ini hilang: jika terdapat rujukan bulat, Excel akan menandakannya sedemikian dan bukannya melanggar logik formula.

Mengubah suai formula tatasusunan dinamik juga lebih mudah. ​​Anda hanya perlu mengedit sel sumber, dan selebihnya dikemas kini secara automatik; dengan formula CSE, ini adalah perlu. edit keseluruhan julat yang terjejas sekaligusIni menjadi agak membebankan dengan julat yang besar. Tambahan pula, apabila helaian mengandungi julat aktif dengan formula CSE, adalah mustahil untuk memasukkan atau memadam baris atau lajur yang mengganggu julat tersebut sehingga formula tatasusunan yang diwarisi dialih keluar atau diubah suai terlebih dahulu.

Menggunakan fungsi utama dengan tatasusunan dinamik

Antara fungsi paling berkuasa yang memanfaatkan tatasusunan dinamik ialah PENAPIS, ISIH, ISIH MENGIKUT, UNIK, TATASUSUNAN RAWAK dan URUTANKesemuanya mengembalikan julat penuh, jadi ia sesuai dengan tingkah laku limpahan, dan terdapat alat yang berguna seperti Bot Formula Excel yang memudahkan penggunaannya.

Dengan fungsi FILTER, sebagai contoh, anda hanya boleh mengekstrak baris yang memenuhi kriteria tertentu daripada jadual data, mengembalikan set hasil yang dikemas kini secara automatik apabila data sumber berubah. Fungsi ini digabungkan dengan UNIQUE, yang membolehkan dapatkan senarai nilai tanpa pendua daripada lajur dengan banyak data berulang.

Fungsi MATRIZALEAT menjana julat nombor rawak dalam blok, sesuai untuk simulasi atau ujian; SECUENCIA, sebaliknya, mengembalikan matriks dengan siri nombor berturut-turut, membolehkan anda menyesuaikan penambahan dan saiz matriks mengikut keperluan anda. Kedua-duanya adalah berdasarkan model matriks baharu dan direka bentuk untuk isi kawasan besar helaian sekaligus.

Akhir sekali, SORT dan SORTBY memudahkan lagi untuk mencipta senarai yang disusun daripada lajur atau jadual sedia ada. Daripada menggunakan pengisihan manual atau kombinasi fungsi yang kompleks, kini anda boleh menulis satu formula yang mengembalikan data yang telah disusun dan ia secara automatik menyesuaikan diri dengan perubahan dalam nilai asal.

Semua fungsi ini bergabung antara satu sama lain dan, hasil daripada limpahan, membolehkan anda membina penyelesaian yang sangat canggih tanpa memerlukan makro atau formula tatasusunan legasi yang sukar diselenggara, dan, untuk mengautomasikan buku kerja anda, Skrip Office dalam Excel Web Ia boleh membantu.

Perbezaan pengiraan antara matriks dinamik dan matriks yang diwarisi

Jika anda masih menggunakan buku lama yang menggunakan formula tatasusunan CSE, adalah penting untuk diingat bahawa semasa menukarnya kepada dinamik setara mungkin sedikit mengubah tingkah laku Dalam beberapa kes. Walaupun dalam kebanyakan senario penukaran adalah secara langsung, adalah dinasihatkan untuk menyemak hasilnya.

Cara biasa untuk menukar daripada tatasusunan legasi kepada tatasusunan dinamik adalah dengan mencari sel pertama bagi julat tatasusunan, salin teks formula, padam keseluruhan julat lama, dan kemudian tulis semula formula hanya di sel kiri atas Dengan menggunakan pendekatan baharu, Excel akan mengendalikan limpahan hasil secara automatik.

Semasa peralihan, beri perhatian khusus kepada fungsi yang sebelum ini bergantung pada tingkah laku persilangan tersirat atau penilaian khas dalam formula CSE. Excel kini mempunyai operator persilangan tersirat (@)yang berfungsi untuk meniru tingkah laku lama dalam kes tertentu, tetapi cadangan umum kekal untuk menulis semula formula secara eksplisit dengan memanfaatkan fungsi baharu.

Excel juga menawarkan sokongan terhad apabila tatasusunan dinamik merujuk data yang terletak dalam buku kerja lain. Operasi yang betul hanya dijamin apabila Kedua-dua fail dibuka pada masa yang samaJika anda menutup buku kerja sumber, formula tatasusunan dinamik yang dipautkan mungkin mengembalikan ralat #REF! semasa cuba mengemas kini, jadi penting untuk mengingati perkara ini untuk model kompleks dengan berbilang rujukan luaran; dalam kes sedemikian, lihat caranya simpan dan kongsi buku kerja untuk mengelakkan masalah.

Walaupun formula CSE akan terus wujud untuk keserasian ke belakang, Microsoft sendiri mengesyorkan agar tidak lagi menciptanya dalam projek baharu dan memilih tatasusunan dinamik. Kebolehbacaan, kemudahan penyelenggaraan dan ketahanan Formula baharu ini menjadikannya pilihan utama untuk masa hadapan.

Julat limpahan dan penyuntingan praktikal pada helaian

Apabila formula melimpah, kawasan yang didudukinya dikenali sebagai julat limpahan. Dalam julat ini, seperti yang telah kami sebutkan, hanya sel pertama yang mengandungi formula sebenar. Sel yang tinggal memaparkan hasil, tetapi ia "dikawal" daripada sel sumber, yang bermaksud bahawa keseluruhan matriks bertindak sebagai satu unit.

Contohnya, jika anda menulis fungsi =ATAS(E7:E19) Dalam satu sel, anda akan melihat bagaimana Excel mengembalikan, merentasi beberapa baris, teks daripada julat tersebut ditukar kepada huruf besar. Jika anda memilih mana-mana sel dalam julat output, anda akan melihat hasilnya, tetapi apabila anda melihat bar formula, anda akan perasan bahawa kandungan kelihatan malap, menunjukkan bahawa Ia tidak boleh diedit secara langsungSebarang perubahan mesti dibuat dalam sel tempat anda mula-mula menulis formula.

Tingkah laku ini mempunyai kelebihan yang jelas: ia menghalang anda daripada mengubah suai hanya sebahagian daripada julat secara tidak sengaja dan membiarkan selebihnya tidak berubah, yang sering menyebabkan ralat yang sukar ditemui. Jika anda perlu menukar formula, anda hanya mengeditnya sekali dalam sel sumber, tekan Enter dan Excel akan mengemas kininya. mengira semula keseluruhan blok secara konsisten.

Dalam penggunaan seharian, ini juga mempengaruhi cara anda memadam atau memindahkan data. Jika anda cuba memadam satu sel dalam julat limpahan, Excel akan memberi amaran kepada anda bahawa anda menjejaskan tatasusunan dinamik. Untuk mengelakkan masalah, biasanya lebih baik memadam atau memotong keseluruhan sel. secara langsung sel sumber, yang menyeret seluruh julat., atau laraskan formula untuk mengembalikan matriks dengan saiz yang berbeza.

Apabila menggabungkan julat limpahan dengan formula lain, perlu diingat bahawa sel-sel ini dikira semula bersama. Sebarang rujukan kepada tatasusunan dinamik hendaklah dibuat dengan teliti, memanfaatkan enjin baharu tanpa mewujudkan rujukan bulat yang tidak perlu atau bercanggah dengan bahagian lain helaian.

Contoh formula matriks lanjutan dengan data sebenar

Banyak formula tatasusunan klasik masih masuk akal, terutamanya apabila bekerja dengan julat yang besar dan anda memerlukannya operasi bersyarat yang kompleksMari kita lihat beberapa contoh tipikal yang mungkin anda dapati sangat berguna dalam laporan dan model.

Bayangkan anda mempunyai julat bernama yang dipanggil Data yang merangkumi beberapa nilai ralat seperti #N/A. Jika anda menggunakan fungsi SUM terus ke julat tersebut, anda akan mendapat ralat. Untuk mengelakkan ini, anda boleh menggunakan tatasusunan yang mengabaikan ralat dengan formula seperti ini: =JUMLAH(JIKA(ISERROR(Data);"";Data)), yang secara dalaman mencipta tatasusunan di mana ralat digantikan dengan rentetan kosong sebelum penjumlahan.

Mengikuti idea yang sama, anda juga boleh mengira berapa banyak ralat yang terdapat dalam julat. Formula seperti =JUMLAH(JIKA(ISERROR(Data);1;0)) Ia menjana tatasusunan dengan 1 apabila terdapat ralat dan 0 apabila tiada ralat. Jumlah keseluruhan memberitahu anda bilangan sel yang salah. Anda juga boleh memudahkannya untuk =JUMLAH(JIKA(ISERROR(Data);1)) dan melangkah lebih jauh ke =JUMLAH(JIKA(ISERROR(Data)*1)), mengambil kesempatan daripada fakta bahawa BENAR*1 ialah 1 dan SALAH*1 ialah 0.

Satu lagi senario biasa ialah menjumlahkan nilai berdasarkan syarat. Contohnya, anda mungkin mempunyai julat yang dipanggil Jualan dan hanya mahu menjumlahkan nilai positif menggunakan formula seperti =SUM(JIKA(Jualan>0,Jualan))Di sini, fungsi IF menjana array dengan nilai yang memenuhi syarat dan dengan nilai palsu untuk selebihnya, yang diabaikan oleh SUM dalam amalan.

Jika anda perlu menggunakan lebih daripada satu syarat pada satu masa, anda boleh mendarabkan ungkapan logik untuk meniru operasi "DAN" dan kemudian menambahkannya bersama-sama. Contoh biasa ialah =SUM((Jualan>0)*(Jualan<=5)*(Jualan))Ini mengira jumlah jualan yang lebih besar daripada 0 dan kurang daripada atau sama dengan 5. Walau bagaimanapun, pendekatan ini memerlukan julat tersebut tidak mengandungi sel teks, jika tidak, anda mungkin menghadapi ralat.

Untuk meniru operasi “OR”, anda boleh menggunakan jumlah ungkapan logik: contohnya, =SUM(JIKA((Jualan<5)+(Jualan>15);Jualan))di mana nilai kurang daripada 5 atau lebih besar daripada 15 ditambah bersama. Dengan cara ini, anda boleh melakukan operasi lanjutan tanpa menggunakan fungsi AND dan OR secara langsung, yang mengembalikan satu nilai logik dan bukan susunan hasil yang lengkap.

Pengiraan statistik dan perbandingan dengan formula matriks

Formula matriks juga sangat berguna untuk mengira purata atau membuat perbandingan antara julat lengkapSatu contoh klasik melibatkan pengiraan min bagi set data tidak termasuk sifar. Jika julat anda dipanggil Jualan, formula =PURATA(JIKA(Jualan<>0;Jualan)) Ia mencipta tatasusunan dengan hanya nilai bukan sifar dan menghantarnya kepada AVERAGE, sekali gus menghilangkan rekod yang tidak memberikan maklumat.

Satu lagi kes yang menarik ialah membandingkan dua julat yang sama saiz, contohnya, MyData dan YourData. Jika anda ingin tahu berapa banyak sel yang berbeza antara keduanya, anda boleh menggunakan =JUMLAH(JIKA(DataSaya=DataAnda;0;1))Formula ini menjana tatasusunan 0 apabila nilainya sepadan dan 1 apabila nilainya berbeza, dan kemudian menjumlahkan hasilnya. Jika kedua-dua julat adalah sama, formula akan mengembalikan 0.

Perbandingan ini boleh dipermudahkan lagi dengan =SUM(1*(Data Saya<>Data Anda)), membuat perbandingan logik (MyData<>YourData) menjana BENAR atau SALAH, yang kemudiannya diubah menjadi 1 dan 0 dengan mendarab dengan 1. Sekali lagi, kuncinya adalah untuk memanfaatkan kelakuan ungkapan boolean dalam tatasusunan.

Anda juga boleh menggunakan formula tatasusunan untuk mencari nilai maksimum dalam julat dan mendapatkan kedudukannya. Jika anda mempunyai julat bernama Data, satu pilihan ialah =MIN(JIKA(Data=MAX(Data);ROW(Data);»»))Formula ini mencipta tatasusunan di mana hanya sel yang nilainya sama dengan maksimum mengandungi nombor barisnya; selebihnya ditukar kepada rentetan kosong. MIN kemudian mengembalikan nombor baris terkecil antara calon tersebut, iaitu, kejadian pertama nilai maksimum.

Jika anda mahukan rujukan sel penuh dan bukannya baris, anda boleh menggabungkan ADDRESS dengan logik di atas menggunakan sesuatu seperti =ALAMAT(MIN(JIKA(Data=MAX(Data);BARIS(Data);»»));LAJUR(Data))supaya anda mendapat rujukan seperti “$B$7” yang mengenal pasti dengan tepat di mana nilai maksimum berada dalam julat tersebut.

Contoh praktikal: jualan mengikut produk menggunakan formula matriks

Untuk lebih memahami potensi formula matriks, bayangkan jadual jualan kenderaan kecil dengan lajur untuk penjual, jenis kenderaan, unit yang dijual, harga seunit dan jumlah jualanAndaikan lajur C dan D masing-masing mengandungi bilangan yang dijual dan harga seunit.

Jika anda menyalin jadual ini ke dalam Excel, anda boleh memilih julat E2:E11 dan memasukkan formula seperti =C2:C11*D2:D11Dalam versi lama, anda perlu mengesahkan formula dengan Ctrl+Shift+Enter untuk menukarnya kepada tatasusunan klasik yang akan mengembalikan pendaraban baris demi baris secara pukal. Excel kemudiannya akan mengira jumlah jualan untuk setiap baris dengan mendarabkan unit dengan harga unit.

Perkara utamanya ialah anda harus sentiasa memilih semua sel yang akan mengandungi keputusan sebelum menulis formula tatasusunan. Jika tidak, hanya sebahagian daripada nilai yang mungkin dikira, atau anda mungkin perlu mengulangi proses tersebut beberapa kali, yang mana tidak cekap.

Dalam konteks ini, adalah perkara biasa untuk menggunakan formula tatasusunan sel tunggal untuk mendapatkan jumlah keseluruhan semua jualan. Contohnya, anda boleh pergi ke sel B13 dan masukkan =SUM(C2:C11*D2:D11)Apabila anda mengesahkan formula (dalam model klasik dengan Ctrl+Shift+Enter), Excel akan mendarabkan setiap pasangan nilai dalam C dan D dan menjumlahkan semua hasil darab. mengembalikan nilai agregat tunggal.

Walaupun contoh-contoh ini bergantung pada tingkah laku yang diwarisi bagi tatasusunan CSE, idea asasnya adalah sama seperti yang digunakan dengan tatasusunan dinamik semasa: melaksanakan operasi pada keseluruhan julat sekaligus dan mengembalikan berbilang atau hasil agregat tanpa memerlukan formula bantu perantaraan dalam setiap baris.

Hari ini, banyak tugasan ini boleh diselesaikan dengan menggabungkan fungsi dinamik dan limpahan, menjadikan hamparan lebih bersih, lebih mudah dibaca dan lebih mudah diselenggaraterutamanya apabila jumlah data meningkat atau apabila beberapa pengguna sedang mengendalikan buku yang sama.

Menguasai penggunaan formula tatasusunan dinamik dalam Excel mengubah sepenuhnya cara anda membina hamparan: dengan memahami cara limpahan berfungsi, bagaimana julat output bertindak, bagaimana ia berbeza daripada formula CSE lama dan cara menggunakan contoh praktikal untuk mengabaikan ralat, melakukan jumlah bersyarat atau membandingkan julat, anda akhirnya dapat menggunakan model yang lebih fleksibel, automatik dan andal, menjimatkan masa dengan setiap kemas kini dan meminimumkan ralat manual.