Garis panduan dan contoh formula tatasusunan

Garis panduan dan contoh formula tatasusunan

Penting: Artikel ini diterjemahkan oleh mesin, lihatlah notis penafian. Sila dapatkan versi Bahasa Inggeris artikel ini di sini sebagai rujukan anda

Untuk menjadi pengguna power Excel, anda perlu tahu cara untuk menggunakan Formula tatasusunan yang boleh melaksanakan pengiraan yang anda tidak boleh lakukan menggunakan Formula tatasusunan. Artikel berikut berdasarkan pada satu siri lajur Excel Power pengguna ditulis oleh Colin Wilcox dan disesuaikan daripada Bab 14 dan 15 Formula 2002 Excel, buku yang ditulis oleh John Walkenbach, Excel MVP.

Mengetahui tentang formula tatasusunan

Formula tatasusunan kerap dirujuk sebagai formula CSE (Ctrl+Shift+Enter) kerana daripada hanya menekan Enter, anda menekan Ctrl+Shift+Enter untuk melengkapkan formula.

Mengapa menggunakan formula tatasusunan?

Jika anda ada pengalaman menggunakan formula dalam Excel, anda tahu bahawa anda boleh menjalankan operasi yang agak canggih. Contohnya, anda boleh mengira jumlah kos pinjaman terhadap bilangan tahun yang ditentukan. Anda boleh menggunakan formula tatasusunan untuk melakukan tugas kompleks, seperti:

  • Mengira bilangan aksara yang terkandung dalam julat sel.

  • Menjumlahkan hanya nombor yang memenuhi syarat tertentu, seperti nilai terendah dalam julat atau nombor yang jatuh antara sempadan atas dan bawah.

  • Menjumlahkan setiap nilai ke-n dalam julat nilai.

Pengenalan ringkas kepada tatasusunan dan formula tatasusunan

Formula tatasusunan ialah formula yang boleh melaksanakan berbilang pengiraan pada satu atau lebih item dalam tatasusunan. Anda boleh anggap tatasusunan sebagai baris nilai, lajur nilai atau gabungan baris dan lajur nilai. Formula tatasusunan boleh mengembalikan sama ada berbilang hasil atau hasil tunggal. Contohnya, anda boleh mencipta formula tatasusunan dalam julat sel dan menggunakan formula tatasusunan untuk mengira subjumlah lajur atau baris. Anda juga boleh menempatkan formula tatasusunan dalam sel tunggal dan mengira jumlah tunggal. Formula tatasusunan yang termasuk berbilang sel dipanggil formula berbilang sel dan formula tatasusunan dalam sel tunggal dipanggil formula sel tunggal.

Contoh dalam seksyen seterusnya menunjukkan anda cara untuk mencipta formula tatasusunan berbilang sel dan sel tunggal.

Cubalah!

Latihan ini menunjukkan anda cara untuk menggunakan formula tatasusunan berbilang sel dan sel tunggal untuk mengira set angka jualan. Set pertama langkah menggunakan formula berbilang sel untuk mengira satu set subjumlah. Set kedua menggunakan formula sel tunggal untuk mengira jumlah besar.

Formula tatasusunan berbilang sel

Berikut ialah buku kerja yang dibenamkan dalam pelayar anda. Walaupun ia mengandungi sampel data, anda perlu tahu bahawa anda tidak boleh mencipta atau mengubah formula tatasusunan dalam buku kerja terbenam - anda memerlukan program Excel. Anda boleh melihat jawapan dalam buku kerja terbenam dan sedikit teks yang menerangkan cara formula tatasusunan berfungsi, tetapi untuk benar-benar memahami formula tatasusunan, anda perlu melihat buku kerja tersebut dalam Excel.

Mencipta formula tatasusunan berbilang sel
  1. Salin seluruh jadual di bawah dan tampalnya ke dalam sel A1 dalam lembaran kerja kosong dalam Excel.

    Juru
    jual

    Jenis
    Kereta

    Bilangan
    Terjual

    Harga
    Seunit

    Jumlah
    Jualan

    Barnhill

    Sedan

    5

    33000

    Coupe

    4

    37000

    Ingle

    Sedan

    6

    24000

    Coupe

    8

    21000

    Jordan

    Sedan

    3

    29000

    Coupe

    1

    31000

    Pica

    Sedan

    9

    24000

    Coupe

    5

    37000

    Sanchez

    Sedan

    6

    33000

    Coupe

    8

    31000

    Formula (Jumlah Besar)

    Jumlah Besar

    '=SUM(C2:C11*D2:D11)

    =SUM(C2:C11*D2:D11)

  2. Untuk melihat Jumlah Jualan coupe dan sedan untuk setiap jurujual, pilih E2:E11, masukkan formula =C2:C11*D2:D11 kemudian tekan Ctrl+Shift+Enter.

  3. Untuk melihat Jumlah Besar semua jualan, pilih sel F11, masukkan formula =SUM(C2:C11*D2:D11) kemudian tekan Ctrl+Shift+Enter.

Anda boleh memuat turun buku kerja ini dengan mengklik butang hijau Excel dalam bar hitam di bahagian bawah buku kerja. Kemudian anda boleh membuka fail dalam Excel, memilih sel yang mengandungi formula tatasusunan dan menekan Ctrl+Shift+Enter untuk membolehkan formula berfungsi.

Jika anda bekerja dalam Excel, pastikan bahawa Helaian1 adalah aktif kemudian pilih sel E2:E11. Tekan F2 kemudian taipkan formula =C2:C11*D2:D11 dalam sel semasa E2. Jika anda menekan Enter, anda akan dapat melihat bahawa ia memasukkan formula sahaja dalam sel E2 dan memaparkan 165000. Sebaliknya, selepas menaipkan formula, tekan Ctrl+Shift+Enter dan bukan Enter sahaja. Kini anda akan dapat melihat hasil dalam sel E2:E11. Perhatikan bahawa dalam bar formula, formula muncul sebagai {=C2:C11*D2:D11}. Ini memberitahu anda bahawa ia merupakan formula tatasusunan seperti yang ditunjukkan dalam jadual berikut.

Apabila anda menekan Ctrl+Shift+Enter, Excel mengurung formula dengan aksara pendakap ({ }) dan menyelitkan tika bagi formula dalam setiap sel julat yang dipilih. Ini berlaku sangat cepat, jadi perkara yang anda lihat dalam lajur E ialah jumlah amaun jualan untuk setiap jenis kereta untuk setiap jurujual. Jika anda memilih E2, kemudian pilih E3, E4 dan seterusnya, anda akan melihat formula yang sama ditunjukkan: {=C2:C11*D2:D11}.

Jumlah dalam lajur E dikira oleh formula tatasusunan

Mencipta formula tatasusunan sel tunggal

Dalam sel F10 pada buku kerja, taipkan formula berikut, kemudian tekan Ctrl+Shift+Enter:

=SUM(C2:C11*D2:D11)

Dalam kes ini, Excel mendarabkan nilai dalam tatasusunan (julat sel C2 hingga D11) lalu menggunakan fungsi SUM untuk menambah jumlah bersama. Hasilnya ialah jumlah besar $1,590,000 dalam jualan. Contoh ini menunjukkan kuasa sebenar formula jenis ini. Contohnya, katakan anda mempunyai 1,000 baris data. Anda boleh menjumlahkan sebahagian atau semua data tersebut dengan mencipta formula tatasusunan dalam sel tunggal berbanding menyeret formula ke bawah menerusi 1,000 baris.

Juga, perhatikan bahawa formula sel tunggal dalam sel G11 adalah bebas sepenuhnya daripada formula berbilang sel (formula dalam sel E2 hingga E11). Ini ialah satu lagi kelebihan dalam menggunakan formula tatasusunan — kefleksibelan. Anda boleh mengubah formula dalam lajur E atau memadamkan lajur tersebut, tanpa menjejaskan formula dalam G11.

Formula tatasusunan turut menawarkan kelebihan berikut:

  • Kekonsistenan    Jika anda mengklik sebarang sel dari E2 ke bawah, anda lihat formula yang sama. Kekonsistenan tersebut boleh membantu memastikan ketepatan yang lebih tinggi.

  • Keselamatan    Anda tidak boleh menulis gantikan komponen bagi formula tatasusunan berbilang sel. Contohnya, klik sel E3 dan tekan Delete. Anda perlu sama ada memilih keseluruhan julat sel (E2 hingga E11) dan mengubah formula untuk keseluruhan tatasusunan atau meninggalkannya sahaja seperti sedia ada. Sebagai langkah keselamatan tambahan, anda perlu menekan Ctrl+Shift+Enter untuk mengesahkan perubahan pada formula.

  • Saiz fail lebih kecil    Anda boleh sentiasa menggunakan formula tatasusunan tunggal selain daripada menggunakan beberapa formula perantaraan. Contohnya, buku kerja menggunakan satu formula tatasusunan untuk mengira hasil dalam lajur E. Jika anda pernah menggunakan formula standard (seperti =C2*D2, C3*D3, C4*D4…), anda mungkin telah menggunakan 11 formula berlainan untuk mengira hasil yang sama.

Sintaks formula tatasusunan

Secara umumnya, formula tatasusunan menggunakan sintaks formula standard. Semuanya bermula dengan tanda sama dengan (=) dan anda boleh menggunakan kebanyakan fungsi Excel terbina dalam formula tatasusunan anda. Perbezaan utama adalah apabila menggunakan formula tatasusunan, anda menekan Ctrl+Shift+Enter untuk memasukkan formula. Apabila anda membuat ini, Excel mengurungkan formula tatasusunan anda dengan pendakap — jika anda menaip pendakap secara manual, formula anda akan ditukarkan kepada rentetan teks dan ia tidak akan berfungsi.

Fungsi tatasusunan merupakan cara yang benar-benar berkesan untuk membina formula yang kompleks. Formula tatasusunan =SUM(C2:C11*D2:D11) ialah sama dengan: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Memasukkan dan mengubah formula tatasusunan

Penting    Tekan Ctrl+Shift+Enter pada bila-bila masa anda perlu memasukkan atau mengedit formula tatasusunan. Ini digunakan pada kedua-dua formula sel tunggal dan berbilang sel.

Pada bila-bila masa anda bekerja dengan formula berbilang sel, turut ingat bahawa:

  • Pilih julat sel untuk memegang hasil anda sebelum anda memasukkan formula. Anda melakukan ini apabila anda mencipta formula tatasusunan berbilang sel apabila anda memilih sel E2 hingga E11.

  • Anda tidak dapat mengubah kandungan sel individu dalam formula tatasusunan. Untuk mencuba ini, pilih sel E3 dalam buku kerja dan tekan Delete. Excel memaparkan mesej yang memberitahu anda bahawa anda tidak dapat mengubah bahagian tatasusunan.

  • Anda boleh mengalihkan atau memadamkan keseluruhan formula tatasusunan, tetapi anda tidak dapat mengalihkan atau memadamkan sebahagian daripadanya. Dengan kata lain, untuk mengecilkan formula tatasusunan, anda perlu memadamkan formula sedia ada dahulu dan kemudian bermula semula.

  • Untuk memadamkan formula tatasusunan, pilih keseluruhan formula (contohnya, =C2:C11*D2:D11), tekan Delete, kemudian tekan Ctrl+Shift+Enter.

  • Anda tidak dapat menyelitkan sel kosong ke dalam atau memadamkan sel daripada formula tatasusunan berbilang sel.

Mengembangkan formula tatasusunan

Kadangkala, anda mungkin perlu mengembangkan formula tatasusunan. Proses ini tidak rumit, tetapi pastikan untuk mengikuti panduan di atas.

Pada lembaran kerja ini, kami telah menambahkan beberapa lagi baris jualan, dalam baris 12 hingga 17. Di sini, kami ingin mengemas kini formula tatasusunan agar ia kini termasuk baris tambahan ini.

Pastikan anda melakukan ini dalam program desktop Excel (selepas anda memuat turun buku kerja pada komputer anda).

Mengembangkan formula tatasusunan
  1. Salin seluruh jadual ini pada sel A1 dalam lembaran kerja Excel.

    Juru
    jual

    Jenis
    Kereta

    Bilangan
    Terjual

    Harga
    Seunit

    Jumlah
    Jualan

    Barnhill

    Sedan

    5

    33000

    165000

    Coupe

    4

    37000

    148000

    Ingle

    Sedan

    6

    24000

    144000

    Coupe

    8

    21000

    168000

    Jordan

    Sedan

    3

    29000

    87000

    Coupe

    1

    31000

    31000

    Pica

    Sedan

    9

    24000

    216000

    Coupe

    5

    37000

    185000

    Sanchez

    Sedan

    6

    33000

    198000

    Coupe

    8

    31000

    248000

    Toth

    Sedan

    2

    27000

    Coupe

    3

    30000

    Wang

    Sedan

    4

    22000

    Coupe

    1

    41000

    Young

    Sedan

    5

    32000

    Coupe

    3

    36000

    Jumlah Besar

  2. Pilih sel E18, masukkan formula Jumlah Besar =SUM(C2:C17*D2:D17) dalam sel A20 dan tekan Ctrl+Shift+Enter.
    Jawapan sepatutnya 2,131,000.

  3. Pilih julat sel yang mengandungi formula tatasusunan semasa (E2:E11), tambahkan sel kosong (E12:E17) yang bersebelahan data baru. Dengan kata lain, pilih sel E2:E17.

  4. Tekan F2 untuk bertukar kepada mod edit.

  5. Dalam bar formula, ubah C11 kepada C17, ubah D11 kepada D17 kemudian tekan Ctrl+Shift+Enter.
    Excel mengemas kini formula dalam sel E2 hingga E11 dan meletakkan tika formula dalam sel baru, E12 hingga E17.

  6. Taipkan formula tatasusunan = SUM(C2:C17*D2*D17) dalam sel F17 agar ia merujuk kepada sel dari baris 2 hingga baris 17 dan tekan Ctrl+Shift+Enter untuk memasukkan formula tatasusunan.
    Jumlah besar yang baru sepatutnya 2,131,000.

Kelemahan menggunakan formula tatasusunan

Formula tatasusunan memang hebat, tetapi ia mempunyai beberapa kelemahan:

  • Anda mungkin kadangkala terlupa untuk menekan Ctrl+Shift+Enter. Ini boleh berlaku kepada pengguna Excel yang paling berpengalaman sekali pun. Ingat untuk menekan gabungan kekunci ini pada bila-bila masa anda memasukkan atau mengedit formula tatasusunan.

  • Pengguna lain buku kerja anda mungkin tidak memahami formula anda. Secara praktik, formula tatasusunan biasanya tidak diterangkan dalam lembaran kerja. Jadi, jika individu lain perlu mengubah suai buku kerja anda, anda perlulah mengelakkan formula tatasusunan atau memastikan mereka tahu tentang sebarang formula tatasusunan dan memahami cara untuk mengubahnya, jika perlu.

  • Bergantung pada kelajuan pemprosesan dan memori komputer anda, formula tatasusunan besar boleh memperlahankan kiraan.

Halaman Atas

Mengetahui tentang pemalar tatasusunan

Pemalar tatasusunan ialah satu komponen formula tatasusunan. Anda mencipta pemalar tatasusunan dengan memasukkan senarai item dan mengurung senarai tersebut secara manual dengan pendakap ({ }), seperti ini:

={1,2,3,4,5}

Sekarang, anda pasti tahu anda perlu menekan Ctrl+Shift+Enter apabila anda mencipta formula tatasusunan. Oleh sebab pemalar tatasusunan ialah komponen formula tatasusunan, anda perlu mengurungkan pemalar dalam pendakap secara manual dengan menaipnya. Anda kemudian menggunakan Ctrl+Shift+Enter untuk memasukkan keseluruhan formula.

Jika anda memisahkan item menggunakan koma, anda akan mencipta tatasusunan mendatar (baris). Jika anda memisahkan item menggunakan koma bertitik, anda akan mencipta tatasusunan menegak (lajur). Untuk mencipta tatasusunan dua dimensi, anda membatasi item dalam setiap baris menggunakan koma dan anda membatasi setiap lajur menggunakan koma bertitik.

Berikut adalah tatasusunan dalam baris tunggal: {1,2,3,4}. Berikut adalah tatasusunan dalam lajur tunggal: {1;2;3;4}. Berikut pula adalah tatasusunan bagi dua baris dan empat lajur: {1,2,3,4;5,6,7,8}. Dalam tatasusunan dua baris, baris pertama ialah 1, 2, 3, dan 4, dan baris kedua ialah 5, 6, 7, dan 8. Koma bertitik tunggal memisahkan dua baris, antara 4 dan 5.

Seperti formula tatasusunan, anda boleh menggunakan pemalar tatasusunan dengan kebanyakan fungsi terbina dalam yang disediakan Excel. Seksyen berikut memerihalkan cara untuk mencipta setiap jenis pemalar dan cara untuk menggunakan pemalar ini dalam fungsi Excel.

Halaman Atas

Mencipta pemalar satu dimensi dan dua dimensi

Prosedur berikut akan melatih anda dalam penciptaan pemalar mendatar, menegak dan dua dimensi.

Mencipta pemalar mendatar

  1. Gunakan buku kerja daripada contoh sebelumnya atau cipta buku kerja baru.

  2. Pilih sel A1 hingga E1.

  3. Dalam bar formula, masukkan formula berikut, kemudian tekan Ctrl+Shift+Enter:

    ={1,2,3,4,5}

    Dalam kes ini, anda harus menaip pendakap buka dan tutup ({ }).

    Hasil berikut dipaparkan.

    Pemalar tatasusunan mendatar dalam formula

Mencipta pemalar menegak

  1. Dalam buku kerja anda, pilih lajur dengan lima sel.

  2. Dalam bar formula, masukkan formula berikut, kemudian tekan Ctrl+Shift+Enter:

    ={1;2;3;4;5}

    Hasil berikut dipaparkan.

    Pemalar tatasusunan menegak dalam formula tatasusunan

Mencipta pemalar dua dimensi

  1. Dalam buku kerja anda, pilih blok sel selebar empat lajur dan tinggi tiga baris.

  2. Dalam bar formula, masukkan formula berikut, kemudian tekan Ctrl+Shift+Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}

    Anda melihat hasil berikut:

    Pemalar tatasusunan dua dimensi dalam formula tatasusunan

Menggunakan pemalar dalam formula

Berikut adalah contoh mudah yang menggunakan pemalar:

  1. Dalam buku kerja sampel, cipta lembaran kerja baru.

  2. Dalam sel A1, taipkan 3, kemudian taipkan 4 dalam B1, 5 dalam C1, 6 dalam D1 dan 7 dalam E1.

  3. Dalam sel A3, taipkan formula berikut dan kemudian tekan Ctrl+Shift+Enter:

    =SUM(A1:E1*{1,2,3,4,5})

    Perhatikan bahawa Excel mengurung pemalar dengan set pendakap lain kerana anda memasukkannya sebagai formula tatasusunan.

    Formula tatasusunan dengan pemalar tatasusunan

    Nilai 85 muncul dalam sel A3.

Seksyen berikut menerangkan cara formula berfungsi.

Sintaks pemalar tatasusunan

Formula yang anda baru sahaja gunakan mengandungi beberapa bahagian.

Sintaks bagi formula tatasusunan dengan pemalar tatasusunan

1. Fungsi

2. Tatasusunan disimpan

3. Operator

4. Pemalar tatasusunan

Unsur terakhir dalam kurungan ialah pemalar tatasusunan: {1,2,3,4,5}. Ingat bahawa Excel tidak mengurungkan pemalar tatasusunan dengan pendakap; anda perlu menaipnya. Juga ingat bahawa selepas anda memasukkan pemalar pada formula tatasusunan, anda perlu menekan Ctrl+Shift+Enter untuk memasukkan formula.

Oleh sebab Excel menjalankan operasi pada ungkapan yang dikurung dalam tanda kurung dahulu, dua unsur seterusnya yang dilaksanakan ialah nilai yang disimpan dalam buku kerja (A1:E1) dan operator. Pada masa ini, formula mendarabkan nilai dalam tatasusunan yang disimpan dengan nilai yang sepadan dalam pemalar. Ia bersamaan dengan:

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

Akhirnya, fungsi SUM menambah nilai dan jumlah 85 muncul dalam sel A3.

Untuk mengelak menggunakan tatasusunan disimpan dan untuk memastikan operasi berada dalam memori keseluruhannya, gantikan tatasusunan yang disimpan dengan pemalar tatasusunan yang lain:

=SUM({3,4,5,6,7}*{1,2,3,4,5})

Untuk mencuba ini, salin fungsi, pilih sel kosong dalam buku kerja anda, tampalkan formula ke dalam bar formula kemudian tekan Ctrl+Shift+Enter. Anda akan melihat hasil yang sama seperti yang anda lakukan dalam latihan lebih awal yang menggunakan formula tatasusunan:

=SUM(A1:E1*{1,2,3,4,5})

Unsur yang anda boleh gunakan dalam pemalar

Pemalar tatasusunan boleh mengandungi nombor, teks, nilai logik (seperti TRUE dan FALSE), dan nilai ralat ( seperti #N/A). Anda boleh menggunakan nombor dalam integer, perpuluhan dan format saintifik. Jika anda menyertakan teks, anda perlu mengurungkan teks dengan tanda petikan (").

Pemalar tatasusunan tidak boleh mengandungi tatasusunan, formula atau fungsi tambahan. Dengan kata lain, ia boleh mengandungi teks atau nombor sahaja yang dipisahkan oleh koma atau koma bertitik. Excel memaparkan mesej amaran apabila anda memasukkan formula seperti {1,2,A1:D4} atau {1,2,SUM(Q2:Z8)}. Juga, nilai berangka tidak boleh mengandungi tanda peratus, tanda dolar, koma atau tanda kurung.

Menamakan pemalar tatasusunan

Cara terbaik untuk menggunakan pemalar tatasusunan adalah dengan menamakannya. Pemalar yang dinamakan menjadi lebih mudah untuk digunakan dan ia boleh menyembunyikan beberapa kerumitan formula tatasusunan anda daripada individu lain. Untuk menamakan pemalar tatasusunan dan menggunakannya dalam formula, lakukan perkara berikut:

  1. Pada tab Formula, dalam kumpulan Nama Tertakrif, klik Takrifkan Nama.
    Kotak dialog Takrifkan Nama muncul.

  2. Kotak Nama, jenis Suku1.

  3. Dalam kotak Merujuk kepada, masukkan pemalar berikut (ingat untuk menaip pendakap secara manual):

    ={"January","February","March"}

    Kandungan kotak dialog kini kelihatan seperti ini:

    Kotak dialog Edit Nama dengan formula

  4. Klik OK, kemudian pilih baris dengan tiga sel kosong.

  5. Taipkan formula berikut, kemudian tekan Ctrl+Shift+Enter.

    =Suku1

    Hasil berikut dipaparkan.

    Tatasusunan yang dinamakan dimasukkan sebagai formula

Apabila anda menggunakan pemalar bernama sebagai formula tatasusunan, ingat untuk memasukkan tanda sama dengan. Jika tidak, Excel mentafsirkannya tatasusunan sebagai rentetan teks dan formula anda tidak akan berfungsi seperti yang dijangka. Akhirnya, ingat bahawa anda boleh menggunakan gabungan teks dan nombor.

Menyelesaikan pemalar tatasusunan

Cari masalah berikut apabila pemalar tatasusunan anda tidak berfungsi:

  • Beberapa unsur mungkin tidak dipisahkan dengan aksara yang betul. Jika anda tidak memasukkan koma atau koma bertitik, atau jika anda meletakkannya pada tempat yang salah, pemalar tatasusunan mungkin tidak dicipta dengan betul atau anda mungkin melihat mesej amaran.

  • Anda mungkin telah memilih julat sel yang tidak sepadan dengan bilangan unsur dalam pemalar anda. Contohnya, jika anda memilih lajur enam sel untuk digunakan dengan pemalar lima sel, ralat nilai #N/A muncul dalam sel kosong. Sebaliknya, jika anda mempunyai terlalu kurang sel, Excel mengecualikan nilai yang tidak mempunyai sel yang sepadan.

Pemalar tatasusunan dalam penggunaan

Contoh berikut menunjukkan beberapa cara yang mana anda boleh gunakan pemalar tatasusunan dalam formula tatasusunan. Beberapa contoh menggunakan fungsi TRANSPOSE untuk menukarkan baris kepada lajur dan sebaliknya.

Mendarabkan setiap item dalam tatasusunan

  1. Cipta lembaran kerja baru kemudian pilih blok sel kosong selebar empat lajur dan setinggi tiga baris.

  2. Taipkan formula berikut, kemudian tekan Ctrl+Shift+Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*2

Kuasa duakan item dalam tatasusunan

  1. Pilih blok sel selebar empat lajur dan tinggi tiga baris.

  2. Taipkan formula tatasusunan berikut kemudian tekan Ctrl+Shift+Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

    Secara alternatifnya masukkan formula tatasusunan ini, yang menggunakan operator karet (^):

    ={1,2,3,4;5,6,7,8;9,10,11,12}^2

Mengubah urutan baris satu dimensi

  1. Pilih lajur dengan lima sel kosong.

  2. Taipkan formula berikut, kemudian tekan Ctrl+Shift+Enter:

    =TRANSPOSE({1,2,3,4,5})

Walaupun anda telah memasukkan pemalar tatasusunan mendatar, fungsi TRANSPOSE menukarkan pemalar tatasusunan menjadi lajur.

Mengubah urutan lajur satu dimensi

  1. Pilih baris dengan lima sel kosong.

  2. Masukkan formula berikut, kemudian tekan Ctrl+Shift+Enter:

    =TRANSPOSE({1;2;3;4;5})

Walaupun anda telah memasukkan pemalar tatasusunan menegak, fungsi TRANSPOSE menukarkan pemalar tatasusunan menjadi baris.

Mengubah urutan pemalar dua dimensi

  1. Pilih blok sel selebar tiga lajur dan setinggi empat baris.

  2. Masukkan pemalar berikut kemudian tekan Ctrl+Shift+Enter:

    =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

Fungsi TRANSPOSE menukarkan setiap baris menjadi satu siri lajur.

Halaman Atas

Menggunakan formula tatasusunan asas

Seksyen ini menyediakan contoh formula tatasusunan asas.

Mencipta tatasusunan dan formula tatasusunan daripada nilai sedia ada

Contoh berikut menerangkan cara untuk menggunakan formula tatasusunan untuk mencipta pautan antara julat sel dalam lembaran kerja berlainan. Ia juga menunjukkan anda cara untuk mencipta pemalar tatasusunan daripada set nilai yang sama.

Mencipta tatasusunan daripada nilai sedia ada

  1. Pada lembaran kerja dalam Excel, pilih sel C8:E10 dan masukkan formula ini:

    ={10,20,30;40,50,60;70,80,90}

    Pastikan anda menaip { (pendakap pembuka) sebelum anda menaip 10 dan } (pendakap penutup) selepas anda menaip 90 kerana anda sedang mencipta tatasusunan nombor.

  2. Tekan Ctrl+Shift+Enter, yang akan memasukkan tatasusunan nombor ini dalam julat sel C8:E10 dengan formula tatasusunan.
    Pada lembaran kerja anda, C8 hingga E10 sepatutnya kelihatan seperti ini:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Pilih julat sel C1 hingga E3.

  4. Masukkan formula berikut dalam bar formula dan kemudian tekan Ctrl+Shift+Enter:

    =C8:E10

    Tatasusunan sel 3x3 muncul dalam sel C1 hingga E3 dengan nilai sama yang anda lihat dalam C8 hingga E10.

Mencipta pemalar tatasusunan daripada nilai sedia ada

  1. Dengan sel C1:C3 yang dipilih, tekan F2 untuk bertukar kepada mod edit.
    Formula tatasusunan sepatutnya masih = C8:E10.

  2. Tekan F9 untuk menukar rujukan sel kepada nilai.
    Excel menukarkan nilai kepada pemalar tatasusunan. Kini formula sepatutnya ={10,20,30;40,50,60;70,80,90}, sama seperti C8:E10.

  3. Tekan Ctrl+Shift+Enter untuk memasukkan pemalar tatasusunan sebagai formula tatasusunan.

Mengira aksara dalam suatu julat sel

Contoh berikut menunjukkan anda cara untuk mengira bilangan aksara, termasuk ruang dalam julat sel.

  1. Salin seluruh jadual ini dan tampalkan ke dalam lembaran kerja dalam sel A1.

  2. Pilih sel A9 kemudian tekan Ctrl+Shift+Enter untuk melihat jumlah bilangan aksara dalam sel A2:A6 (66).

  3. Pilih sel A12 kemudian tekan Ctrl+Shift+Enter untuk melihat kandungan sel terpanjang A2:A6 (sel A3).

Data

Ini ialah

sekumpulan sel yang

bergabung

untuk membentuk

ayat tunggal.

Jumlah aksara dalam A2:A6

=SUM(LEN(A2:A6))

Kandungan sel terpanjang (A3)

=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

Formula berikut yang digunakan dalam sel A9, mengira jumlah bilangan aksara (66) dalam sel A2 hingga A6.

=SUM(LEN(A2:A6))

Dalam kes ini, fungsi LEN mengembalikan panjang setiap rentetan teks dalam setiap sel dalam julat. Fungsi SUM kemudian menambah bersama fungsi tersebut dan memaparkan hasil (66) dalam sel yang mengandungi formula, A9.

Cari nilai paling kecil n dalam julat

Contoh ini menunjukkan cara untuk mencari tiga nilai paling kecil dalam julat sel.

  1. Pilih sel A16 hingga A18.
    Set sel ini akan memegang hasil yang dikembalikan oleh formula tatasusunan.

  2. Dalam bar formula, masukkan formula berikut, kemudian tekan Ctrl+Shift+Enter:

    =SMALL(A5:A14,{1;2;3})

Nilai 400, 475 dan 500 muncul dalam sel A16 hingga A18, masing-masing.

Formula ini menggunakan pemalar tatasusunan untuk menilai fungsi SMALL tiga kali dan mengembalikan ahli terkecil (1), kedua terkecil (2), dan ketiga terkecil (3) dalam tatasusunan yang terkandung dalam sel A1:A10. Untuk mencari lagi nilai, anda menambah lagi argumen pada pemalar dan bilangan sel hasil yang sepadan pada julat A12:A14. Anda juga boleh menggunakan fungsi tambahan dengan formula ini, seperti SUM atau AVERAGE. Contohnya:

=SUM(SMALL(A 5 :A1 4 ,{1;2;3}))

=AVERAGE(SMALL(A 5 :A1 4 ,{1;2;3}))

Cari nilai paling besar n dalam julat

Untuk mencari nilai paling besar dalam julat, anda boleh menggantikan fungsi SMALL dengan fungsi LARGE. Selain itu, contoh berikut menggunakan fungsi ROW dan INDIRECT.

  1. Pilih sel A1 hingga A3.

  2. Dalam bar formula, masukkan formula ini, kemudian tekan Ctrl+Shift+Enter:

    =LARGE(A5:A14,ROW(INDIRECT("1:3")))

Nilai 3200, 2700 dan 2000 muncul dalam sel A1 hingga A3, masing-masing.

Pada masa ini, mengetahui sedikit sebanyak tentang fungsi ROW dan INDIRECT mungkin membantu. Anda boleh menggunakan fungsi ROW untuk mencipta tatasusunan integer berturutan. Contohnya, pilih lajur kosong 10 sel dalam buku kerja latihan anda, masukkan formula tatasusunan ini dalam sel A5:A14 dan kemudian tekan Ctrl+Shift+Enter:

=ROW(1:10)

Formula mencipta lajur dengan 10 integer berturutan. Untuk melihat potensi masalah, selitkan baris di atas julat yang mengandungi formula tatasusunan (iaitu, di atas baris 1). Excel melaraskan rujukan baris dan formula menjana integer dari 2 hingga 11. Untuk memperbetulkan masalah tersebut, anda perlu menambah fungsi INDIRECT pada formula:

=ROW(INDIRECT("1:10"))

Fungsi INDIRECT menggunakan rentetan teks sebagai argumennya (sebab itulah julat 1:10 dikurung oleh petikan kembar). Excel tidak hanya melaraskan nilai teks apabila anda menyelitkan baris atau sebaliknya mengalihkan formula tatasusunan. Hasilnya, fungsi ROW sentiasa menjana tatasusunan integer yang anda inginkan.

Mari kita teliti formula yang anda gunakan awal tadi — =LARGE(A5:A14,ROW(INDIRECT("1:3"))) — bermula daripada tanda kurung dalaman dan bergerak ke luar: Fungsi INDIRECT mengembalikan set nilai teks, dalam kes ini nilai 1 hingga 3. Fungsi ROW sebaliknya menjana tatasusunan lajur dengan tiga sel. Fungsi LARGE menggunakan nilai dalam julat sel A5:A14, dan ia dinilai tiga kali, sekali untuk setiap rujukan yang dikembalikan oleh fungsi ROW. Nilai 3200, 2700 dan 2000 dikembalikan oleh tatasusunan lajur tiga sel. Jika anda ingin mencari lagi nilai, anda perlu menambah julat sel yang lebih besar pada fungsi INDIRECT.

Akhir sekali, anda boleh menggunakan formula ini dengan fungsi lain, seperti SUM dan AVERAGE.

Mencari rentetan teks yang paling panjang dalam julat sel

Formula ini hanya berfungsi apabila julat data mengandungi lajur sel tunggal. Pada Helaian3, masukkan formula berikut dalam sel A16 dan tekan Ctrl+Shift+Enter:

=INDEX(A6:A9,MATCH(MAX(LEN(A6:A9)),LEN(A6:A9),0),1)

Teks "bunch of cells that" yang muncul dalam sel A16.

Mari kita lihat lebih dekat pada formula, bermula dari unsur dalaman dan bergerak ke luar. Fungsi LEN mengembalikan panjang setiap item dalam julat sel A6:A9. Fungsi MAX mengira nilai terbesar antara item tersebut, yang sepadan dengan rentetan teks terpanjang, yang berada dalam sel A7.

Di sini perkara menjadi sedikit rumit. Fungsi MATCH mengira ofset (kedudukan relatif) sel yang mengandungi rentetan teks terpanjang. Untuk melakukannya, ia memerlukan tiga argumen: nilai carian, tatasusunan carian, dan jenis padanan. Fungsi MATCH mencari tatasusunan carian untuk nilai carian yang ditentukan. Dalam kes ini, nilai carian ialah rentetan teks terpanjang:

(MAX(LEN( A6 : A9 ))

dan rentetan tersebut berada dalam tatasusunan ini:

LEN( A6:A9 )

Argumen jenis padanan ialah 0. Jenis padanan boleh terdiri daripada nilai 1, 0, atau -1. Jika anda menentukan 1, MATCH mengembalikan nilai terbesar yang kurang daripada atau sama dengan nilai carian. Jika anda menentukan 0, MATCH mengembalikan nilai pertama yang tepat dengan nilai carian. Jika anda menentukan -1, MATCH mencari nilai terkecil yang lebih besar daripada atau sama dengan nilai carian yang ditentukan. Jika anda mengecualikan jenis padanan, Excel menganggap 1.

Akhir sekali, fungsi INDEX mengambil argumen ini: tatasusunan dan baris dan nombor lajur dalam tatasusunan tersebut. Julat sel A6:A9 menyediakan tatasusunan, fungsi MATCH menyediakan alamat sel dan argumen akhir (1) menentukan bahawa nilai datang daripada lajur pertama dalam tatasusunan.

Halaman Atas

Menggunakan formula tatasusunan lanjutan

Seksyen ini menyediakan contoh formula tatasusunan lanjutan.

Menjumlahkan julat yang mengandungi nilai ralat

Fungsi SUM dalam Excel tidak berfungsi apabila anda cuba menjumlahkan julat yang mengandungi nilai ralat, seperti #N/A. Contoh ini menunjukkan anda cara untuk menjumlahkan nilai dalam Data julat bernama yang mengandungi ralat.

=SUM(IF(ISERROR(Data),"",Data))

Formula mencipta tatasusunan baru yang mengandungi nilai asal tolak sebarang nilai ralat. Bermula dari fungsi dalaman dan bergerak ke luar, fungsi ISERROR mencari dalam julat sel (Data) untuk ralat. Fungsi IF mengembalikan nilai khusus jika syarat yang anda tentukan menilai kepada TRUE dan nilai lain jika ia menilai kepada FALSE. Dalam kes ini, ia mengembalikan rentetan kosong ("") untuk semua nilai ralat kerana ia menilai kepada TRUE dan ia mengembalikan nilai yang tinggal daripada julat (Data) kerana ia menilai kepada FALSE, bermakna ia tidak mengandungi nilai ralat. Fungsi SUM kemudian mengira jumlah untuk tatasusunan bertapis.

Mengira bilangan nilai ralat dalam julat

Contoh ini serupa dengan formula sebelumnya, tetapi ia mengembalikan bilangan nilai ralat dalam Data julat yang bernama daripada menapisnya keluar:

=SUM(IF(ISERROR(Data),1,0))

Formula ini mencipta tatasusunan yang mengandungi nilai 1 untuk sel yang mengandungi ralat dan nilai 0 untuk sel yang tidak mengandungi ralat. Anda boleh meringkaskan formula dan mencapai hasil yang sama dengan mengalih keluar argumen ketiga untuk fungsi IF, seperti ini:

=SUM(IF(ISERROR(Data),1))

Jika anda tidak menentukan argumen, fungsi IF mengembalikan PALSU jika sel tidak mengandungi nilai ralat. Anda boleh mempermudahkan lagi formula:

=SUM(IF(ISERROR(Data)*1))

Versi ini berfungsi kerana TRUE*1=1 dan FALSE*1=0.

Menjumlahkan nilai berdasarkan syarat

Anda mungkin perlu menambah nilai berdasarkan syarat. Contohnya, formula tatasusunan ini menjumlahkan integer positif sahaja dalam julat yang bernama Jualan:

=SUM(IF(Sales>0,Sales))

Fungsi IF mencipta tatasusunan nilai positif dan nilai palsu. Fungsi SUM pada asasnya mengabaikan nilai palsu kerana 0+0=0. Julat sel yang anda gunakan dalam formula ini boleh terdiri daripada sebarang bilangan baris dan lajur.

Anda boleh juga menjumlahkan nilai yang memenuhi lebih daripada satu syarat. Contohnya, formula tatasusunan ini mengira nilai yang lebih besar daripada 0 dan kurang daripada atau sama dengan 5:

=SUM((Sales>0)*(Sales<=5)*(Sales))

Sentiasa ingat bahawa formula ini mengembalikan ralat jika julat mengandungi satu atau lebih sel bukan angka.

Anda juga boleh mencipta formula tatasusunan yang menggunakan jenis syarat OR. Contohnya, anda boleh menjumlahkan nilai yang kurang daripada 5 dan lebih daripada 15:

=SUM(IF((Sales<5)+(Sales>15),Sales))

Fungsi IF mencari semua nilai yang kecil daripada 5 dan lebih besar daripada 15 dan kemudian menghantar nilai tersebut ke fungsi SUM.

Anda tidak dapat menggunakan fungsi AND dan OR secara terus dalam formula tatasusunan kerana fungsi tersebut mengembalikan hasil tunggal, sama ada TRUE atau FALSE dan fungsi tatasusunan memerlukan tatasusunan hasil. Anda boleh menyelesaikan masalah secara sementara dengan menggunakan logik yang ditunjukkan dalam formula sebelumnya. Dengan kata lain, anda menjalankan operasi matematik, seperti penambahan atau pendaraban, pada nilai yang memenuhi syarat OR atau AND.

Mengira purata yang mengecualikan sifar

Contoh ini menunjukkan anda cara untuk mengalih keluar sifar dari julat apabila anda perlu mencari purata nilai dalam julat tersebut. Formula menggunakan julat data yang dinamakan Jualan:

=AVERAGE(IF(Sales<>0,Sales))

Fungsi IF mencipta tatasusunan nilai yang tidak sama dengan 0 dan kemudian menghantar nilai tersebut ke fungsi AVERAGE.

Mengira bilangan perbezaan antara dua julat sel

Formula tatasusunan ini membandingkan nilai dalam dua julat sel yang bernama MyData dan YourData dan mengembalikan bilangan perbezaan antara dua. Jika kandungan dua julat adalah sama, formula mengembalikan 0. Untuk menggunakan formula ini, julat sel perlu dalam saiz yang sama dan dimensi yang sama (contohnya, jika MyData ialah julat 3 baris kepada 5 lajur, YourData juga mestilah 3 baris kepada 5 lajur):

=SUM(IF( MyData =YourData,0,1))

Formula mencipta tatasusunan baru dengan saiz yang sama seperti julat yang anda bandingkan. Fungsi IF mengisikan tatasusunan dengan nilai 0 dan nilai 1 (0 untuk salah padanan dan 1 untuk sel serupa). Fungsi SUM kemudiannya mengembalikan jumlah nilai dalam tatasusunan.

Anda boleh mempermudahkan formula seperti ini:

=SUM(1*( MyData <> YourData ))

Seperti formula yang mengira nilai ralat dalam julat, formula berfungsi kerana TRUE*1=1 dan FALSE*1=0.

Mencari lokasi nilai maksimum dalam julat

Formula tatasusunan ini mengembalikan nombor baris bagi nilai maksimum dalam julat lajur tunggal bernama Data:

=MIN(IF(Data=MAX(Data),ROW(Data),""))

Fungsi IF mencipta tatasusunan baru yang sepadan dengan julat yang bernama Data. Jika sel yang sepadan mengandungi nilai maksimum dalam julat, tatasusunan mengandungi nombor baris. Sebaliknya, tatasusunan mengandungi rentetan kosong (""). Fungsi MIN menggunakan tatasusunan baru sebagai argumen keduanya dan mengembalikan nilai terkecil, yang sepadan dengan nombor baris bagi nilai maksimum dalam Data. Jika julat bernama Data mengandungi nilai maksimum yang sama, formula mengembalikan baris nilai pertama.

Jika anda ingin mengembalikan alamat sel sebenar bagi nilai maksimum, gunakan formula ini:

=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))

Halaman Atas

Perlukan bantuan lanjut?

Anda sentiasa boleh tanya pakar dalam Komuniti teknikal Excel, dapatkan sokongan dalam komuniti jawapanatau cadangkan ciri baru atau peningkatan pada Suara pengguna Excel.

Nota: Notis Penafian Penterjemahan Mesin: Artikel ini telah diterjemah oleh sistem komputer tanpa campur tangan manusia. Microsoft menawarkan penterjemahan mesin ini untuk membantu pengguna-pengguna yang tidak bertutur dalam Bahasa Inggeris supaya dapat menikmati kandungan mengenai produk, perkhidmatan dan teknologi Microsoft. Artikel ini mungkin mengandungi ralat perbendaharaan kata, sintaks atau tatabahasa kerana ia diterjemahkan oleh mesin.

Lihat Juga

Gambaran keseluruhan Formula

Kembangkan kemahiran anda
Jelajahi latihan
Dapatkan ciri baru terlebih dahulu
Sertai Office Insiders

Adakah maklumat ini membantu?

Terima kasih atas maklum balas anda!

Terima kasih atas maklum balas anda! Nampaknya ia mungkin akan membantu untuk menyambungkan anda kepada salah seorang daripada ejen sokongan Office kami.

×