Excel pelbagai formula adalah formula yang menjalankan pengiraan pada nilai-nilai dalam satu atau lebih susunan berbanding satu nilai data tunggal. Dalam program spreadsheet, array adalah rangkaian atau siri nilai data yang berkaitan yang biasanya disimpan dalam sel bersebelahan dalam lembaran kerja. Panduan ini menerangkan cara menggunakan formula pelbagai dalam semua versi Excel moden.
Tutorial ini serasi dengan kebanyakan versi Excel termasuk 2007, 2010, 2013, 2016, dan Office 365.
Apakah Formula Array?
Rumus Array adalah serupa dengan formula biasa:
- Mereka bermula dengan tanda yang sama ( = )
- Gunakan sintaks yang sama sebagai formula biasa
- Gunakan pengendali matematik yang sama
- Ikuti perintah operasi yang sama
Dalam Excel, formula pelbagai dikelilingi oleh pendakap kerinting { } - pendakap ini tidak boleh ditaip; mereka mesti ditambah kepada formula dengan menekan Ctrl , Shift , dan Masukkan kunci selepas menaip formula ke dalam sel atau sel. Oleh sebab itu, formula tatasusunan kadang-kadang dirujuk sebagai a CSE formula dalam Excel.
Pengecualian kepada peraturan ini adalah apabila pendakap kerinting digunakan untuk memasukkan array sebagai hujah untuk fungsi yang biasanya mengandungi hanya satu nilai atau rujukan sel. Contohnya, dalam tutorial di bawah yang menggunakannya VLOOKUP dan juga PILIH berfungsi untuk membuat formula carian kiri, array dibuat untuk PILIH fungsi Index_num hujah dengan menaip pendakap sekitar array yang dimasukkan.
Langkah-langkah untuk Mencipta Formula Array
- Masukkan formula.
- Tahan Ctrl dan Shift kekunci pada papan kekunci.
- Tekan dan lepaskan Masukkan kunci untuk membuat formula tatasusunan.
- LepaskanCtrl dan Shift kunci.
Sekiranya dilakukan dengan betul, formula akan dikelilingi oleh pendakap kerinting, dan setiap sel memegang formula itu akan mengandungi hasil yang berbeza.
Mengedit Formula Array
Pada bila-bila masa rumus array disunting, pendakap kerinting hilang dari sekitar formula tatasusunan. Untuk mendapatkannya kembali, formula array mesti dimasukkan dengan menekan butang Ctrl, Shift, dan Masukkan kekunci sekali lagi sama seperti apabila formula array pertama kali dibuat.
Jenis-jenis Array Formula
Terdapat dua jenis formula tatasus utama:
- Formula pelbagai sel tunggal yang menjalankan pelbagai pengiraan dalam satu sel kerja lembaran kerja.
- Rumusan pelbagai sel sel yang terletak di lebih daripada satu sel kerja.
Formula Arus Sel Satu
Jenis array jenis ini menggunakan fungsi, seperti SUM, AVERAGE, atau COUNT, untuk menggabungkan output formula pelbagai berbilang sel ke dalam satu nilai dalam sel tunggal. Contoh satu formula tatasusunan sel tunggal ialah:
{= SUM (A1: A2 * B1: B2)}
Rumusan ini menambah produk bersama A1 * B1 dan A2 * B2, dan kemudian ia mengembalikan hasil tunggal dalam satu sel dalam lembaran kerja. Cara lain untuk menulis formula di atas ialah:
Formula Arus Multi-Sel
Seperti namanya namanya, formula array ini terletak di sel-sel lembaran kerja berganda, dan mereka mengembalikan array sebagai jawapan. Dengan kata lain, formula yang sama terletak dalam dua atau lebih sel, dan ia mengembalikan jawapan yang berbeza dalam setiap sel.
Setiap salinan, atau contohnya, daripada formula array, melakukan pengiraan yang sama dalam setiap sel yang didiami, tetapi setiap contoh formula menggunakan data yang berbeza dalam pengiraannya. Oleh itu, setiap contoh menghasilkan keputusan yang berbeza. Satu contoh rumus pelbagai sel sel akan kelihatan seperti:
{= A1: A2 * B1: B2}
Jika contoh di atas terletak di sel C1 dan C2 dalam lembaran kerja, maka hasilnya akan seperti berikut:
- Data dalam A1 didarabkan dengan data dalam B1, dan hasilnya disimpan dalam sel C1.
- Data dalam A2 didarabkan dengan data dalam B2, dan hasilnya disimpan dalam sel C2.
Menggunakan Array untuk Menukar Baris dan Lajur
The TRANSPOSE fungsi digunakan untuk menyalin data dari satu baris ke satu lajur atau sebaliknya. Fungsi ini adalah salah satu daripada beberapa dalam Excel yang mesti selalu digunakan sebagai formula tatasusunan.
Formula Array dan Fungsi Excel
Banyak fungsi terbina dalam Excel, seperti SUM, AVERAGE, dan COUNT, juga boleh digunakan dalam formula tatasusunan. Terdapat juga beberapa fungsi, seperti TRANSPOSE fungsi, yang mesti selalu dimasukkan sebagai array agar ia berfungsi dengan baik. Kegunaan banyak fungsi seperti INDEX dan MATCH atau MAX dan JIKA boleh dilanjutkan dengan menggunakannya bersama dalam formula tatasusunan.
Contoh-contoh Formula Array dalam Excel:
01 dari 08Buat Formula Array Sel Satu Mudah
Formula pelbagai sel tunggal biasanya terlebih dahulu menjalankan pengiraan berbilang sel dan kemudian menggunakan fungsi seperti AVERAGE atau SUM untuk menggabungkan output array menjadi satu keputusan tunggal.
02 dari 08Abaikan Nilai Ralat semasa Mencari Data
Formula array ini menggunakan AVERAGE, JIKA, dan ISNUMBER berfungsi untuk mencari nilai purata bagi data sedia ada sambil mengabaikan nilai ralat seperti # DIV / 0! dan #NAME?
03 dari 08Kira Sel Data
Menggunakan SUM danJIKA berfungsi dalam formula array untuk mengira sel-sel data yang memenuhi salah satu daripada beberapa syarat; teknik ini berbeza daripada menggunakan Excel COUNTIFS fungsi, yang memerlukan semua syarat yang ditetapkan untuk dipenuhi sebelum sel dikira.
04 dari 08Cari Nombor Positif atau Negatif Terbesar
Contoh ini menggabungkan MAX fungsi danJIKA berfungsi dalam formula tatasusunan yang akan menemui nilai terbesar atau maksimum untuk pelbagai data apabila kriteria tertentu dipenuhi.
05 dari 08Cari Nombor Positif atau Negatif yang Terkecil
Sama seperti contoh di atas, ini menggabungkan MIN fungsi dan JIKA berfungsi dalam formula tatasusunan untuk mencari nilai terkecil atau minimum untuk pelbagai data apabila kriteria tertentu dipenuhi.
06 dari 08Cari Nilai Tengah atau Median
The MEDIAN fungsi dalam Excel mendapati nilai tengah untuk senarai data. Dengan menggabungkannya dengan JIKA fungsi dalam formula tatasusunan, nilai tengah untuk kumpulan data yang berkaitan boleh didapati.
07 dari 08Buatlah Formula Lookup dengan Kriteria Pelbagai
Formula array ini melibatkan sarang MATCH dan INDEX berfungsi untuk mencari maklumat khusus dalam pangkalan data.
08 dari 08Buat Formula Lookup Kiri
The VLOOKUP fungsi biasanya hanya mencari data yang terletak di lajur ke kanan, tetapi dengan menggabungkannya dengan PILIH fungsi, anda boleh membuat formula pencarian kiri yang akan mencari lajur data di sebelah kiri Lookup_value hujah.
