Panduan dan contoh rumus array

Panduan dan contoh rumus array

Catatan: Kami ingin secepatnya menyediakan konten bantuan terbaru dalam bahasa Anda. Halaman ini diterjemahkan menggunakan mesin dan mungkin terdapat kesalahan tata bahasa atau masalah keakuratan. Kami bertujuan menyediakan konten yang bermanfaat untuk Anda. Dapatkah Anda memberi tahu kami apakah informasi ini bermanfaat untuk Anda di bagian bawah halaman ini? Berikut artikel dalam bahasa Inggris untuk referensi.

Rumus array adalah rumus yang bisa melakukan beberapa perhitungan pada satu atau beberapa item dalam satu array. Anda akan berpendapat array sebagai satu baris atau kolom nilai, atau kombinasi baris dan kolom nilai. Rumus array bisa menghasilkan beberapa hasil, atau satu hasil.

Dimulai dengan September 2018 pembaruan untuk Office 365, rumus yang bisa menampilkan hasil beberapa akan secara otomatis mengalir baik ke bawah, atau seluruh menjadi sel. Perubahan ini dalam perilaku juga disertai dengan beberapa fungsi array dinamisbaru. Rumus array dinamis, apakah mereka menggunakan fungsi yang sudah ada atau fungsi array dinamis, hanya perlu memasukkan ke dalam satu sel, lalu mengonfirmasi dengan menekan Enter. Rumus array yang lebih lama, warisan memerlukan terlebih dahulu memilih rentang seluruh output, lalu konfirmasi rumus dengan Ctrl + Shift + Enter. Mereka sering dirujuk sebagai rumus CSE .

Anda bisa menggunakan rumus array untuk melakukan tugas-tugas yang kompleks, seperti:

  • Dengan cepat membuat kumpulan data sampel.

  • Menghitung jumlah karakter yang terdapat di dalam rentang sel.

  • Menjumlahkan hanya angka yang memenuhi kondisi tertentu, misalnya nilai terendah dalam rentang, atau angka yang terletak di antara batas atas dan bawah.

  • Menjumlahkan setiap nilai ke-n dalam serangkaian nilai.

Dalam contoh berikut ini memperlihatkan kepada Anda cara membuat rumus array multisel dan sel tunggal. Jika memungkinkan, kami telah disertakan contoh dengan beberapa fungsi array dinamis, serta rumus array yang ada dimasukkan sebagai array dinamis dan warisan.

Mengunduh contoh kami

Mengunduh buku kerja contoh dengan semua rumus contoh-contoh array dalam artikel ini.

Latihan ini menunjukkan cara menggunakan rumus array multisel dan sel tunggal untuk menghitung serangkaian angka penjualan. Rangkaian langkah pertama menggunakan rumus multisel untuk menghitung rangkaian subtotal. Rangkaian kedua menggunakan rumus sel tunggal untuk menghitung jumlah total.

  • Rumus array multisel

    Fungsi array multisel dalam sel H10 = F10:F19 * G10:G19 untuk menghitung jumlah mobil dijual oleh harga satuan

  • Di sini kami menghitung penjualan Total dari Coupe dan sedan untuk tiap tenaga penjualan dengan memasukkan = F19:F19 * G10:G19 dalam sel H10.

    Saat Anda menekan Enter, Anda akan melihat hasil tumpahan ke bawah ke sel H10:H19. Perhatikan bahwa rentang tumpahan disorot dengan batas saat Anda memilih semua sel dalam rentang tumpahan. Anda juga mungkin memperhatikan bahwa rumus di sel H10:H19 berwarna abu-abu. Berada hanya ada di sana untuk referensi, jadi jika Anda ingin menyesuaikan rumus, Anda akan perlu untuk memilih sel H10, di mana rumus master berada.

  • Rumus array sel tunggal

    Rumus array sel tunggal untuk menghitung total keseluruhan dengan =SUM(F10:F19*G10:G19)

    Dalam sel H20 buku kerja contoh, ketik atau salin dan tempelkan =SUM(F10:F19*G10:G19), dan lalu tekan Enter.

    Dalam kasus ini, Excel mengalikan nilai dalam array (rentang sel F10 hingga G19), dan lalu menggunakan fungsi SUM untuk menambahkan Total bersama-sama. Hasilnya adalah $1,590,000 penjualan total keseluruhan.

    Contoh ini memperlihatkan bagaimana kuat tipe rumus ini dapat. Misalnya, Anda memiliki 1.000 baris data. Anda bisa menjumlahkan sebagian atau semua data dengan membuat rumus array dalam sel tunggal sebagai ganti menyeret rumus ke bawah sampai ke baris 1000. Juga, perhatikan bahwa rumus sel tunggal dalam sel H20 benar-benar independen multisel rumus (rumus dalam sel H10 hingga H19). Ini adalah keuntungan lain menggunakan rumus array — fleksibilitas. Anda bisa mengubah rumus lain dalam kolom H tanpa mempengaruhi rumus H20. Juga dapat praktik yang baik agar independen Total seperti ini, karena membantu memvalidasi akurasi hasil Anda.

  • Rumus dinamis array juga menawarkan keunggulan berikut:

    • Konsistensi    Jika Anda mengklik salah satu sel dari H10 ke bawah, Anda melihat rumus yang sama. Konsistensi yang bisa membantu memastikan akurasi lebih besar.

    • Keamanan    Anda tidak bisa menimpa komponen dari rumus array multisel. Misalnya, klik sel H11 dan tekan Delete. Excel tidak akan mengubah array output. Untuk mengubah itu, Anda perlu pilih sel kiri atas dalam array, atau sel H10.

    • Ukuran file lebih kecil    Anda bisa menggunakan satu rumus array sering bukan beberapa rumus menengah. Sebagai contoh, contoh penjualan mobil menggunakan satu rumus array untuk menghitung hasil dalam kolom E. Jika Anda telah menggunakan rumus standar seperti = F10 * G10, F11 * G11, F12 * G12, dll, Anda akan menggunakan 11 rumus yang berbeda untuk menghitung hasil yang sama. Bukan itu besar, tapi bagaimana jika Anda memiliki ribuan baris Total? Lalu dapat membuat perbedaan besar.

    • Efisiensi    Fungsi array bisa berupa efisien untuk membuat rumus yang kompleks. Array =SUM(F10:F19*G10:G19) rumus yang sama seperti ini: = SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Menumpahkan    Rumus array yang dinamis secara otomatis akan mengalir ke dalam rentang output. Jika sumber data dalam tabel Excel, lalu rumus array dinamis Anda akan secara otomatis mengubah ukuran saat Anda menambahkan atau menghapus data.

    • #SPILL! kesalahan    Array dinamis diperkenalkan #SPILL! kesalahan, yang menunjukkan bahwa rentang dimaksudkan tumpahan diblokir karena beberapa alasan. Saat Anda menyelesaikan penyumbatan, rumus akan secara otomatis mengalir.

Konstanta array adalah komponen dari rumus array. Anda membuat konstanta array dengan memasukkan daftar item lalu menutup daftar itu secara manual dengan kurung kurawal ({ }), seperti ini:

= {1,2,3,4,5} atau = {"Januari", "Maret", "Maret"}

Jika Anda memisahkan item dengan menggunakan koma, Anda membuat array horizontal (baris). Jika Anda memisahkan item dengan menggunakan titik koma, Anda membuat array vertikal (kolom). Untuk membuat array dua dimensi, Anda membatasi item dalam setiap baris dengan tanda koma, dan membatasi setiap baris dengan titik koma.

Prosedur berikut ini akan memberi Anda latihan membuat konstanta horizontal, vertikal, dan dua dimensi. Kami akan memperlihatkan contoh yang menggunakan fungsi urutan untuk secara otomatis menghasilkan konstanta array, serta secara manual memasukkan konstanta array.

  • Membuat konstanta horizontal

    Menggunakan buku kerja dari contoh sebelumnya, atau membuat buku kerja baru. Pilih sel kosong mana pun dan masukkan =SEQUENCE(1,5). Fungsi urutan disusun baris 1 dengan 5 array kolom sama dengan = {1,2,3,4,5}. Hasil berikut akan ditampilkan:

    Membuat konstanta horizontal array dengan =SEQUENCE(1,5) atau = {1,2,3,4,5}

  • Membuat konstanta vertikal

    Pilih sel kosong mana saja dengan ruang di bawahnya, dan masukkan =SEQUENCE(5), atau = {1; 2; 3; 4; 5}. Hasil berikut akan ditampilkan:

    Membuat konstanta array vertikal dengan = SEQUENCE(5), atau = {1; 2; 3; 4; 5}

  • Membuat konstanta dua dimensi

    Pilih sel kosong mana saja dengan ruang di sebelah kanan dan bawahnya, dan masukkan =SEQUENCE(3,4). Anda akan melihat hasil berikut:

    Membuat baris 3 dengan 4 kolom konstanta array dengan =SEQUENCE(3,4)

    Anda juga bisa memasukkan: atau = {1,2,3,4 5,6,7,8; 9,10,11,12}, tapi Anda akan ingin memperhatikan mana Anda menaruh semi titik versus koma.

    Saat Anda melihat, opsi urutan menawarkan keuntungan signifikan atas memasukkan nilai konstanta array Anda secara manual. Terutama, menghemat waktu Anda, tetapi juga dapat membantu mengurangi kesalahan dari entri manual. Ada juga lebih mudah dibaca, terutama saat titik semi bisa sulit untuk membedakan dari koma pemisah.

Berikut adalah contoh yang menggunakan array konstanta sebagai bagian dari rumus lebih besar. Dalam contoh buku kerja, masuk ke lembar kerja konstanta dalam rumus , atau buat lembar kerja baru.

Dalam sel D9, kami dimasukkan =SEQUENCE(1,5,3,1), tapi Anda juga bisa memasukkan 3, 4, 5, 6 dan 7 di sel A9:H9. Tidak ada yang khusus tentang pilihan angka itu tertentu, kami hanya memilih sesuatu selain 1-5 untuk perbedaan.

Dalam sel E11, masukkan = SUM (D9:H9*SEQUENCE(1,5)), atau = SUM (D9:H9* {1,2,3,4,5}). Rumus yang mengembalikan 85.

Menggunakan konstanta array dalam rumus. Dalam contoh ini, kami menggunakan = SUM (D9:H(*SEQUENCE(1,5))

Fungsi urutan menyusun nilai yang setara array konstanta {1,2,3,4,5}. Karena Excel melakukan operasi pada ekspresi yang dimasukkan dalam tanda kurung terlebih dahulu, dua elemen yang masuk ke play adalah nilai sel dalam D9:H9, dan operator perkalian (*). Pada titik ini, rumus mengalikan nilai dalam array tersimpan dengan nilai terkait dalam konstanta. Sebaiknya setara dengan:

=Sum(D9*1,E9*2,F9*3,G9*4,H9*5), atau =SUM(3*1,4*2,5*3,6*4,7*5)

Akhirnya, fungsi SUM menambahkan nilai, dan mengembalikan 85.

Untuk menghindari menggunakan array tersimpan dan menjaga operasi seluruhnya di memori, Anda bisa menggantinya dengan konstanta array yang lain:

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

Elemen yang bisa Anda gunakan dalam konstanta array

  • Konstanta array bisa berisi angka, teks, nilai logika (seperti TRUE dan FALSE), dan nilai kesalahan seperti #N/A. Anda bisa menggunakan angka dalam format bilangan bulat, desimal, dan ilmiah. Jika Anda menyertakan teks, Anda harus mengapit dengan tanda kutip ("text").

  • Konstanta array tidak boleh berisi array, rumus, atau fungsi tambahan. Dengan kata lain, konstanta array hanya boleh berisi teks atau angka yang dipisahkan dengan koma atau titik koma. Excel menampilkan pesan peringatan apabila Anda memasukkan rumus seperti {1,2,A1:D4} atau {1,2,SUM(Q2:Z8)}. Nilai numerik juga tidak boleh berisi tanda persen, tanda dolar, koma, atau kurung.

Salah satu cara terbaik untuk menggunakan konstanta array adalah nama mereka. Konstanta bernama bisa lebih mudah digunakan, dan mereka bisa menyembunyikan beberapa kerumitan rumus array Anda dari orang lain. Untuk memberi nama konstanta array dan menggunakannya dalam rumus, lakukan hal berikut:

Masuk ke rumus > ditentukan nama > Tentukan nama. Di kotak nama , ketikkan Quarter1. Di kotak merujuk ke , masukkan konstanta berikut (Ingatlah untuk mengetikkan tanda kurung kurawal secara manual):

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

Kotak dialog sekarang akan terlihat seperti ini:

Tambahkan konstanta array bernama dari rumus > nama yang ditentukan > > pengatur nama baru

Klik OK, lalu pilih setiap baris dengan tiga sel kosong, dan masukkan = Quarter1.

Hasil berikut akan ditampilkan:

Menggunakan konstanta array yang diberi nama dalam rumus, seperti = Quarter1, tempat Quarter1 telah ditetapkan sebagai = {"Januari", "Maret", "Maret"}

Jika Anda menginginkan hasil tumpahan secara vertikal dan bukan secara horizontal, Anda bisa menggunakan =mengubah urutan(Quarter1).

Jika Anda ingin menampilkan daftar 12 bulan, seperti yang mungkin Anda gunakan saat membuat laporan keuangan, Anda bisa dasar dari tahun saat ini dengan fungsi urutan. Hal yang rapi dan bagus tentang fungsi ini adalah bahwa meskipun hanya bulan ditampilkan, tanggal yang valid belakangnya yang bisa Anda gunakan dalam hitungan lain. Anda akan menemukan contoh-contoh ini pada lembar kerja bernama konstanta array dan kumpulan data sampel cepat di contoh buku kerja.

=Text(date(year(Today()),SEQUENCE(1,12),1),"mmm")

Gunakan kombinasi fungsi teks, tanggal, tahun, hari ini, dan urutan untuk menyusun daftar dinamis waktu 12 bulan

Ini menggunakan fungsi DATE untuk membuat tanggal berdasarkan tahun saat ini, urutan membuat konstanta array dari 1 hingga 12 untuk Januari hingga Desember, maka fungsi TEXT mengonversi format tampilan untuk "mmm" (Jan, Feb, bulan Maret, dsb.). Jika Anda ingin menampilkan nama bulan lengkap, seperti Januari, Anda akan menggunakan "mmmm".

Saat Anda menggunakan konstanta yang bernama sebagai rumus array, masukkan tanda sama dengan, seperti = Quarter1, bukan hanya Quarter1. Jika Anda tidak, Excel menafsirkan array sebagai string teks dan rumus Anda tidak akan berfungsi seperti yang diharapkan. Akhirnya, ingatlah bahwa Anda bisa menggunakan kombinasi fungsi, teks dan angka. Semua ini bergantung pada bagaimana kreatif yang Anda inginkan untuk mendapatkan.

Contoh berikut menunjukkan beberapa cara di mana Anda dapat memasukkan konstanta array untuk digunakan dalam rumus array. Beberapa contoh gunakan fungsi TRANSPOSE untuk mengonversi baris ke kolom dan sebaliknya.

  • Beberapa setiap item dalam satu array

    Masukkan = urutan (1,12) * 2, atau = {1,2,3,4; 5,6,7,8; 9,10,11,12} * 2

    Anda juga bisa membagi dengan (/), tambahkan dengan (+), dan mengurangi dengan (-).

  • Memangkatkan item dalam satu array

    Masukkan = urutan (1,12) ^ 2, atau = {1,2,3,4; 5,6,7,8; 9,10,11,12} ^ 2

  • Menemukan nilai akar kuadrat dari kuadrat item dalam satu array

    Masukkan =SQRT(SEQUENCE(1,12)^2), atau =SQRT({1,2,3,4;5,6,7,8;9,10,11,12}^2)

  • Mengubah urutan baris satu dimensi

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

    Meskipun Anda memasukkan konstanta array horizontal, fungsi TRANSPOSE akan mengonversi konstanta array tersebut menjadi kolom.

  • Mengubah urutan kolom satu dimensi

    Masukkan =TRANSPOSE(SEQUENCE(5,1)), atau = TRANSPOSE ({1; 2; 3; 4; 5})

    Meskipun Anda memasukkan konstanta array vertikal, fungsi TRANSPOSE akan mengonversi konstanta array tersebut menjadi baris.

  • Mengubah urutan konstanta dua dimensi

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

    Fungsi TRANSPOSE mengonversi setiap baris menjadi serangkaian kolom.

Bagian ini menyediakan contoh rumus array dasar.

  • Membuat array dari nilai yang ada

    Contoh berikut ini menjelaskan cara menggunakan rumus array untuk membuat array baru dari array yang sudah ada.

    Masukkan =SEQUENCE(3,6,10,10), atau = {10,20,30,40,50,60; 70,80,90,100,110,120; 130,140,150,160,170,180}

    Pastikan mengetikkan {(kurung kurawal buka) sebelum Anda mengetikkan 10, dan} (kurung kurawal tutup) setelah Anda mengetikkan 180, karena Anda sedang membuat array angka.

    Berikutnya, masukkan = D9 #, atau = D9:I11 di sel kosong. Array sel 3 x 6 muncul dengan nilai yang sama yang Anda lihat di D9:D11. Tanda # disebut tumpah operator rentang, dan ini adalah cara Excel referensi rentang seluruh array daripada mengetik itu.

    Menggunakan operator rentang tumpah (#) untuk referensi array yang sudah ada

  • Membuat konstanta array dari nilai yang ada

    Anda dapat melakukan hasil dari rumus array tumpah dan mengonversi yang menjadi komponen-komponen. Pilih sel D9, lalu tekan F2 untuk beralih ke edit mode. Berikutnya, tekan F9 untuk mengubah referensi sel untuk nilai yang Excel lalu mengonversi konstanta array. Saat Anda menekan Enter, rumus, = D9 #, sekarang akan = {10,20,30; 40,50,60; 70,80,90}.

  • Menghitung karakter dalam satu rentang sel

    Contoh berikut menunjukkan cara menghitung jumlah karakter dalam satu rentang sel. Ini termasuk spasi.

    Menghitung jumlah total karakter di rentang, dan array lainnya untuk bekerja dengan string teks

    = SUM (LEN(C9:C13))

    Dalam kasus ini, fungsi LEN mengembalikan pendeteksian panjang setiap string teks di setiap sel dalam rentang. Fungsi SUM lalu menambahkan nilai-nilai tersebut bersama-sama dan menampilkan hasil (66). Jika Anda ingin mendapatkan rata-rata jumlah karakter, Anda bisa menggunakan:

    = RATA-RATA (LEN(C9:C13))

  • Konten sel terpanjang dalam rentang C9:C13

    =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

    Rumus ini berfungsi hanya ketika rentang data berisi satu kolom sel.

    Mari kita mempelajari rumus, dimulai dari elemen dalam dan luar bekerja lebih lanjut. Fungsi LEN mengembalikan pendeteksian panjang setiap item di rentang sel D2:D6. Fungsi MAX menghitung nilai terbesar di antara item-item yang sesuai dengan string teks terpanjang, yang ada di sel D3.

    Berikut ini adalah tempat hal mendapatkan sedikit kompleks. Fungsi MATCH menghitung offset (posisi relatif) dari sel yang berisi string teks terpanjang. Untuk melakukan itu, memerlukan tiga argumen: nilai pencarian, array pencarian, dan jenis pencocokan. Fungsi MATCH mencari array pencarian untuk nilai yang ditentukan pencarian. Dalam kasus ini, nilai pencarian adalah string teks terpanjang:

    MAX(LEN(C9:C13)

    dan bahwa string itu terdapat dalam array ini:

    LEN(C9:C13)

    Cocok dengan tipe argumen dalam kasus ini adalah 0. Jenis pencocokan bisa berupa nilai-1, 0 atau 1.

    • 1 - mengembalikan nilai terbesar yang kurang dari atau sama dengan pencarian val

    • 0 - mengembalikan nilai pertama yang persis sama dengan nilai pencarian

    • 1 - mengembalikan nilai terkecil yang lebih besar dari atau sama dengan nilai tertentu pencarian

    • Jika Anda menghilangkan jenis pencocokan, Excel mengasumsikan 1.

    Akhirnya, fungsi INDEX mengambil argumen ini: array, dan nomor baris dan kolom dalam array tersebut. Rentang sel C9:C13 menyediakan array, fungsi MATCH menyediakan alamat sel dan argumen final (1) menentukan bahwa nilai yang berasal dari kolom pertama dalam array.

    Jika Anda ingin agar konten terkecil string teks, Anda akan menggantikan MAX dalam contoh di atas dengan MIN.

  • Menemukan nilai terkecil n dalam satu rentang

    Contoh ini memperlihatkan cara menemukan tiga nilai terkecil di rentang sel, mana array contoh data dalam sel B9:B18has telah dibuat dengan: = INT (RANDARRAY(10,1) * 100). Perhatikan bahwa RANDARRAY fungsi tidak tetap, sehingga Anda akan mendapatkan sekumpulan baru angka acak setiap kali Excel menghitung.

    Excel rumus array untuk menemukan nilai terkecil ke-n: =SMALL(B9#,SEQUENCE(D9))

    Masukkan =SMALL(B9#,SEQUENCE(D9), = kecil (B9:B18, {1; 2; 3})

    Rumus ini menggunakan konstanta array untuk mengevaluasi fungsi SMALL tiga kali dan kembali anggota 3 terkecil dalam array yang ada dalam sel B9:B18, di mana 3 adalah nilai variabel dalam sel D9. Untuk menemukan nilai lainnya, Anda bisa meningkatkan nilai dalam fungsi urutan, atau menambahkan lebih banyak argumen untuk konstanta. Anda juga bisa menggunakan fungsi tambahan dengan rumus ini, seperti SUM atau rata-rata. Misalnya:

    =SUM(SMALL(B9#,SEQUENCE(D9))

    =AVERAGE(SMALL(B9#,SEQUENCE(D9))

  • Menemukan nilai terbesar n dalam satu rentang

    Untuk menemukan nilai terbesar dalam rentang, Anda bisa mengganti fungsi SMALL dengan fungsi LARGE. Selain itu, contoh berikut menggunakan fungsi baris dan INDIRECT .

    Masukkan = besar (B9 #, baris (INDIRECT ("1:3"))), atau = besar (B9:B18,ROW(INDIRECT("1:3")))

    Pada titik ini, ini mungkin membantu untuk mengetahui sedikit tentang fungsi-fungsi baris dan INDIRECT. Anda bisa menggunakan fungsi ROW untuk membuat array berturut-turut bilangan bulat. Sebagai contoh, pilih kosong dan masukkan:

    =ROW(1:10)

    Rumus membuat kolom 10 berturut-turut integer. Untuk melihat masalah potensial, menyisipkan baris di atas rentang yang berisi rumus array (yaitu, di atas baris 1). Excel menyesuaikan referensi baris, dan rumus sekarang menghasilkan bilangan bulat dari 2 ke 11. Untuk memperbaiki masalah tersebut, Anda menambahkan fungsi INDIRECT ke rumus tersebut:

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

    Fungsi INDIRECT menggunakan string teks sebagai argumen (yang merupakan mengapa rentang 1:10 diapit tanda kutip). Excel tidak menyesuaikan nilai teks saat Anda menyisipkan baris atau jika tidak memindahkan rumus array. Hasilnya, fungsi ROW selalu menghasilkan array bilangan bulat yang Anda inginkan. Anda bisa dengan mudah menggunakan urutan:

    =SEQUENCE(10)

    Mari kita periksa rumus yang Anda menggunakan versi yang lebih lama — besar (B9 #, baris (INDIRECT ("1:3"))) = — dimulai dari dalam tanda kurung dan bekerja di luar: fungsi INDIRECT mengembalikan sekumpulan nilai teks, dalam hal ini nilai 1 sampai 3. Fungsi ROW pada gilirannya menghasilkan array sel tiga kolom. Fungsi LARGE menggunakan nilai dalam rentang sel B9:B18, dan dievaluasi tiga kali, satu kali untuk setiap referensi yang dihasilkan oleh fungsi ROW. Jika Anda ingin menemukan nilai lainnya, Anda menambahkan rentang sel lebih besar ke fungsi INDIRECT. Akhirnya, dengan contoh kecil, Anda bisa menggunakan rumus ini dengan fungsi lain, misalnya SUM dan AVERAGE.

  • Menjumlahkan rentang yang berisi nilai kesalahan

    Fungsi SUM dalam Excel tidak berfungsi ketika Anda mencoba untuk menjumlahkan rentang yang berisi nilai kesalahan, seperti #VALUE! atau #N/A. Contoh ini memperlihatkan kepada Anda cara menjumlahkan nilai dalam rentang yang bernama Data yang berisi kesalahan:

    Gunakan array untuk menangani kesalahan. Sebagai contoh, =SUM(IF(ISERROR(Data),"",Data) akan menjumlahkan rentang yang bernama Data bahkan jika dokumen menyertakan kesalahan, seperti #VALUE! atau #NA!.

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

    Rumus ini membuat array baru yang berisi nilai aslinya dikurangi setiap nilai kesalahan. Dimulai dari fungsi dalam ke arah luar, fungsi ISERROR mencari rentang sel (Data) untuk kesalahan. Fungsi IF mengembalikan nilai tertentu jika kondisi yang Anda tetapkan dievaluasi TRUE dan nilai lain jika dievaluasi FALSE. Dalam kasus ini, fungsi akan mengembalikan string kosong ("") untuk semua nilai kesalahan karena dievaluasi ke TRUE, dan mengembalikan nilai yang tersisa dari rentang (Data) karena dievaluasi ke FALSE, yang berarti tidak berisi nilai kesalahan. Fungsi SUM kemudian menghitung jumlah total untuk array yang difilter.

  • Menghitung jumlah nilai kesalahan dalam satu rentang

    Contoh ini adalah seperti rumus sebelumnya, tetapi mengembalikan jumlah nilai kesalahan dalam rentang yang bernama Data sebagai ganti memfilternya:

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

    Rumus ini membuat array yang berisi nilai 1 untuk sel yang berisi kesalahan dan nilai 0 untuk sel yang tidak berisi kesalahan. Anda dapat menyederhanakan rumus dan mendapatkan hasil yang sama dengan menghapus argumen ketiga untuk fungsi IF, seperti ini:

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

    Jika Anda tidak menetapkan argumen, fungsi IF mengembalikan FALSE jika sel tidak berisi nilai kesalahan. Anda dapat menyederhanakan lagi rumus tersebut:

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

    Versi ini bekerja karena TRUE*1=1 dan FALSE*1=0.

Anda mungkin perlu menjumlahkan nilai berdasarkan kondisi.

Anda bisa menggunakan array untuk menghitung berdasarkan kondisi tertentu. =Sum(if(Sales>0,Sales)) akan menjumlahkan semua nilai lebih besar dari 0 dalam rentang yang disebut penjualan.

Misalnya, rumus array ini menjumlahkan positif bilangan bulat dalam rentang yang bernama penjualan, yang mewakili sel E9:E24 dalam contoh di atas:

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

Fungsi IF membuat array nilai positif dan false. Fungsi SUM dasarnya mengabaikan nilai false karena 0 + 0 = 0. Rentang sel yang Anda gunakan di rumus ini bisa berisi jumlah baris dan kolom.

Anda juga bisa menjumlahkan nilai yang memenuhi kondisi lebih dari satu. Misalnya, rumus array ini menghitung nilai lebih besar dari 0 dan lebih kecil dari 2500:

=Sum((Sales>0)*(Sales<2500)*(Sales))

Perhatikan bahwa rumus ini mengembalikan kesalahan jika rentang berisi satu atau beberapa sel nonnumerik.

Anda juga bisa membuat rumus array yang menggunakan tipe kondisi OR. Misalnya, Anda bisa menjumlahkan nilai yang lebih besar dari 0 atau kurang dari 2500:

=Sum(if((Sales>0)+(Sales<2500),Sales))

Anda tidak bisa menggunakan AND dan atau fungsi dalam rumus array langsung karena fungsi tersebut memberikan hasil tunggal, baik TRUE atau FALSE, dan fungsi array memerlukan array hasil. Anda dapat mengatasi masalah dengan menggunakan logika yang diperlihatkan dalam rumus sebelumnya. Dengan kata lain, Anda melakukan operasi matematika, seperti penambahan atau perkalian pada nilai yang memenuhi kotak atau atau dan kondisi.

Contoh ini menunjukkan cara mengeluarkan nol dari rentang apabila Anda perlu menghitung nilai rata-rata dalam rentang tersebut. Rumus ini menggunakan rentang data bernama Sales:

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

Fungsi IF membuat array nilai yang tidak sama dengan 0 lalu meneruskan nilai itu ke fungsi AVERAGE.

Rumus array ini membandingkan nilai dalam dua rentang sel yang bernama MyData dan YourData dan mengembalikan jumlah selisih antara dua. Jika konten dua rentang identik, rumus akan menghasilkan 0. Untuk menggunakan rumus ini, rentang sel harus sama ukuran dan dimensi yang sama. Misalnya, jika MyData rentang 3 baris menurut kolom 5, YourData juga harus 3 baris menurut kolom 5:

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

Rumus ini membuat array baru dengan ukuran yang sama sebagai rentang yang dibandingkan. Fungsi IF mengisi array dengan nilai 0 dan nilai 1 (0 bila tidak cocok dan 1 untuk sel yang identik). Fungsi SUM kemudian mengembalikan jumlah nilai dalam array tersebut.

Anda dapat menyederhanakan rumus seperti ini:

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

Seperti rumus yang menghitung nilai kesalahan dalam satu rentang, rumus ini bekerja karena TRUE*1=1, dan FALSE*1=0.

Rumus array ini mengembalikan nomor baris nilai maksimum dalam rentang kolom tunggal yang bernama Data:

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

Fungsi IF menciptakan array baru yang terkait dengan rentang yang bernama Data. Jika sel yang terkait berisi nilai maksimum dalam rentang, array akan berisi nomor baris. Jika tidak, array berisi string kosong (""). Fungsi MIN menggunakan array baru sebagai argumen kedua dan mengembalikan nilai terkecil, yang terkait dengan nomor baris dari nilai maksimum dalam Data. Jika rentang yang bernama Data berisi nilai maksimum yang identik, rumus akan mengembalikan baris nilai pertama.

Jika Anda ingin mengembalikan alamat sel sebenarnya dari nilai maksimum, gunakan rumus ini:

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

Anda akan menemukan contoh sama seperti dalam contoh buku kerja di lembar kerja perbedaan antara dataset .

Latihan ini menunjukkan cara menggunakan rumus array multisel dan sel tunggal untuk menghitung serangkaian angka penjualan. Rangkaian langkah pertama menggunakan rumus multisel untuk menghitung rangkaian subtotal. Rangkaian kedua menggunakan rumus sel tunggal untuk menghitung jumlah total.

  • Rumus array multisel

Salin seluruh tabel di bawah ini dan tempelkan ke sel A1 di lembar kerja kosong.

Penjualan Orang

Mobil Tipe

Angka Terjual

Unit Harga

Total Penjualan

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

Rumus (Jumlah Total)

Jumlah Total

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

=SUM(C2:C11*D2:D11)

  1. Untuk melihat penjualan Total dari Coupe dan sedan untuk tiap tenaga penjualan, pilih sel E2: E11, masukkan rumus = C2: C11 * D2: D11, lalu tekan Ctrl + Shift + Enter.

  2. Untuk melihat jumlah Total semua penjualan, pilih sel F11, masukkan rumus =SUM(C2:C11*D2:D11), dan tekan Ctrl + Shift + Enter.

Ketika Anda menekan Ctrl + Shift + Enter, Excel mengelilingi rumus dengan kurung kurawal ({}) dan menyisipkan contoh rumus di tiap sel dari rentang yang dipilih. Ini terjadi dengan cepat, jadi apa yang Anda lihat di kolom E adalah jumlah penjualan total untuk setiap tipe mobil untuk tiap tenaga penjualan. Jika Anda pilih E2, lalu pilih E3, E4, dan seterusnya, Anda akan melihat bahwa rumus yang sama diperlihatkan: {= C2: C11 * D2: D11}.

Jumlah total di kolom E dihitung dengan rumus array

  • Membuat rumus array sel tunggal

Dalam sel D13 buku kerja, ketikkan rumus berikut, dan tekan Ctrl + Shift + Enter:

=SUM(C2:C11*D2:D11)

Dalam kasus ini, Excel mengalikan nilai dalam array (rentang sel C2 hingga D11) dan lalu menggunakan fungsi SUMuntuk menambahkan Total bersama-sama. Hasilnya adalah $1,590,000 penjualan total keseluruhan. Contoh ini memperlihatkan bagaimana kuat tipe rumus ini dapat. Misalnya, Anda memiliki 1.000 baris data. Anda bisa menjumlahkan sebagian atau semua data dengan membuat rumus array dalam sel tunggal sebagai ganti menyeret rumus ke bawah sampai ke baris 1000.

Juga, perhatikan bahwa rumus sel tunggal dalam sel D13 benar-benar independen multisel rumus (rumus di sel E2 hingga E11). Ini adalah keuntungan lain menggunakan rumus array — fleksibilitas. Anda bisa mengubah rumus di kolom E atau menghapus kolom yang sama sekali, tanpa mempengaruhi rumus di D13.

Rumus array juga menawarkan keunggulan berikut:

  • Konsistensi    Jika Anda mengklik salah satu sel dari E2 ke bawah, Anda melihat rumus yang sama. Konsistensi ini dapat membantu memastikan akurasi yang lebih tinggi.

  • Keamanan    Anda tidak bisa menimpa komponen dari rumus array multisel. Misalnya, klik sel E3 dan tekan Hapus. Anda harus memilih seluruh rentang sel (E2 melalui E11) dan mengubah rumus untuk seluruh array, atau biarkan array saat ini. Sebagai ukuran keamanan tambahan, Anda harus menekan Ctrl + Shift + Enter untuk mengonfirmasi perubahan apa pun rumus.

  • Ukuran file lebih kecil    Anda bisa sering menggunakan rumus array tunggal daripada beberapa rumus menengah. Sebagai contoh, buku kerja ini menggunakan satu rumus array untuk menghitung hasil di kolom E. Jika menggunakan rumus standar (misalnya =C2*D2, C3*D3, C4*D4…), Anda akan harus menggunakan 11 rumus yang berbeda untuk mendapatkan hasil yang sama.

Secara umum, rumus array menggunakan sintaks rumus standar. Semua dimulai dengan tanda sama dengan (=), dan Anda bisa menggunakan sebagian besar fungsi Excel bawaan dalam rumus array. Perbedaan utama adalah saat menggunakan rumus array, Anda menekan Ctrl + Shift + Enter untuk memasukkan rumus Anda. Saat Anda melakukan ini, Excel mengelilingi rumus array Anda dengan kurung kurawal — jika Anda mengetikkan tanda kurung kurawal secara manual, rumus Anda akan dikonversi ke string teks, dan tidak akan berfungsi.

Fungsi array bisa berupa efisien untuk membuat rumus yang kompleks. Array rumus =SUM(C2:C11*D2:D11) yang sama seperti ini: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Penting: Tekan Ctrl + Shift + Enter tiap kali Anda perlu memasukkan rumus array. Ini berlaku untuk sel tunggal maupun multisel rumus.

Setiap kali Anda bekerja dengan rumus multisel, perhatikan juga:

  • Pilih rentang sel untuk menampung hasil sebelum memasukkan rumus. Anda melakukannya apabila membuat rumus array multisel dengan memilih sel E2 sampai E11.

  • Anda tidak dapat mengubah isi setiap sel dalam satu rumus array. Untuk melakukannya, pilih sel E3 dalam buku kerja dan tekan Delete. Excel akan menampilkan pesan bahwa Anda tidak dapat mengubah sebagian array.

  • Anda dapat memindahkan atau menghapus seluruh rumus array, tetapi Anda tidak dapat memindahkan atau menghapus sebagian. Dengan kata lain, untuk menyusutkan rumus array, Anda harus menghapus rumus yang ada lalu memulai ulang.

  • Untuk menghapus rumus array, pilih seluruh rentang rumus (misalnya, E2: E11), lalu tekan Hapus.

  • Anda tidak bisa menyisipkan sel kosong, atau menghapus sel dari rumus array multisel.

Kadang-kadang, Anda mungkin perlu memperluas rumus array. Pilih sel pertama di rentang array yang ada, dan lanjutkan hingga Anda telah memilih seluruh rentang yang Anda inginkan untuk memperluas rumus. Tekan F2 untuk mengedit rumus, lalu tekan CTRL + SHIFT + ENTER untuk mengonfirmasi rumus setelah Anda sudah disesuaikan rentang rumus. Tombol ini untuk memilih seluruh rentang, dimulai dengan sel kiri atas dalam array. Sel kiri atas adalah yang mendapatkan diedit.

Rumus array memang hebat, tetapi ada kelemahannya:

  • Anda mungkin kadang-kadang lupa menekan Ctrl + Shift + Enter. Itu bisa terjadi bahkan pengguna Excel berpengalaman. Ingatlah untuk Tekan kombinasi tombol ini setiap kali Anda memasukkan atau mengedit rumus array.

  • Pengguna lain buku kerja Anda mungkin tidak memahami rumus Anda. Dalam praktiknya, rumus array biasanya tidak dijelaskan dalam lembar kerja. Oleh karena itu, jika orang lain perlu mengubah buku kerja Anda, Anda harus menghindari rumus array atau memastikan bahwa orang-orang mengetahui tentang rumus array apa pun dan memahami cara mengubahnya, jika mereka memerlukannya.

  • Tergantung kecepatan pemrosesan dan memori komputer Anda, rumus array yang besar dapat memperlambat perhitungan.

Konstanta array adalah komponen dari rumus array. Anda membuat konstanta array dengan memasukkan daftar item lalu menutup daftar itu secara manual dengan kurung kurawal ({ }), seperti ini:

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

Sekarang, Anda mengetahui Anda harus menekan Ctrl + Shift + Enter ketika Anda membuat rumus array. Karena konstanta array adalah komponen dari rumus array, Anda memasukkan konstanta dengan kurung kurawal secara manual mengetikkan mereka. Anda lalu gunakan Ctrl + Shift + Enter untuk memasukkan seluruh rumus.

Jika memisahkan item dengan koma, Anda membuat array horizontal (baris). Jika memisahkan item dengan titik koma, Anda membuat array vertikal (kolom). Untuk membuat array dua dimensi, batasi item di setiap baris dengan koma, dan batasi setiap baris dengan titik koma.

Berikut adalah array dalam baris tunggal: {1,2,3,4}. Berikut adalah array dalam kolom tunggal: {1; 2; 3; 4}. Dan berikut ini adalah sebuah array dua baris dan kolom empat: {1,2,3,4; 5,6,7,8}. Dalam array dua baris, baris pertama adalah 1, 2, 3 dan 4, dan baris kedua adalah 5, 6, 7 dan 8. Titik koma tunggal memisahkan dua baris, antara 4 dan 5.

Seperti halnya rumus array, Anda dapat menggunakan konstanta array dengan sebagian besar fungsi bawaan Excel. Bagian berikut menjelaskan cara membuat setiap konstanta dan cara menggunakannya dengan fungsi di Excel.

Prosedur berikut akan memberi Anda latihan membuat konstanta horizontal, vertikal, dan dua dimensi.

Membuat konstanta horizontal

  1. Di lembar kerja kosong, pilih sel A1 sampai E1.

  2. Di bilah rumus, masukkan rumus berikut, dan tekan Ctrl + Shift + Enter:

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

    Dalam kasus ini, Anda harus mengetik kurawal pembuka dan penutup ({}), dan Excel akan menambahkan set untuk Anda.

    Hasil berikut akan ditampilkan.

    Konstanta larik horizontal dalam rumus

Membuat konstanta vertikal

  1. Dalam buku kerja Anda, pilih satu kolom lima sel.

  2. Di bilah rumus, masukkan rumus berikut, dan tekan Ctrl + Shift + Enter:

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

    Hasil berikut akan ditampilkan.

    Konstanta array vertikal dalam rumus array

Membuat konstanta dua dimensi

  1. Dalam buku kerja Anda, pilih satu blok sel lebar tiga kolom kali tinggi tiga baris.

  2. Di bilah rumus, masukkan rumus berikut, dan tekan Ctrl + Shift + Enter:

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

    Anda akan melihat hasil berikut:

    Konstanta larik dua dimensi dalam rumus array

Menggunakan konstanta dalam rumus

Ini adalah contoh sederhana yang menggunakan konstanta:

  1. Dalam buku kerja contoh, buat lembar kerja baru.

  2. Di sel A1, ketikkan 3, lalu ketikkan 4 di B1, 5 di C1, 6 di D1, dan 7 di E1.

  3. Di sel A3, ketikkan rumus berikut, dan tekan Ctrl + Shift + Enter:

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

    Perhatikan bahwa Excel menutup konstanta dengan sepasang kurung kurawal, karena Anda memasukkannya sebagai rumus array.

    Rumus array dengan konstanta array

    Nilai 85 muncul di sel A3.

Bagian berikutnya menjelaskan cara kerja rumus.

Rumus yang baru saja Anda gunakan terdiri dari beberapa bagian.

Sintaks rumus array dengan konstanta array

1. Fungsi

2. Array tersimpan

3. Operator

4. Konstanta array

Elemen terakhir di dalam tanda kurung adalah konstanta array: {1,2,3,4,5}. Ingat bahwa Excel tidak mengelilingi konstanta array dengan kurung kurawal; Anda benar-benar mengetik mereka. Perhatikan juga bahwa setelah Anda menambahkan konstanta ke rumus array, Anda menekan Ctrl + Shift + Enter untuk memasukkan rumus.

Karena Excel menjalankan operasi dalam tanda kurung terlebih dahulu, kedua elemen berikutnya adalah nilai yang tersimpan di buku kerja (A1:E1) dan operator. Pada titik ini, rumus mengalikan nilai dalam array yang tersimpan dengan nilai yang terkait dalam konstanta. Ini ekuivalen dengan:

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

Terakhir, fungsi SUM menambahkan nilai-nilai, dan jumlah 85 ditampilkan di sel A3.

Untuk menghindari menggunakan array tersimpan dan menjaga operasi seluruhnya di memori, ganti array tersimpan dengan konstanta array yang lain:

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

Untuk mencoba ini, Salin fungsi, pilih sel kosong di buku kerja Anda, menempelkan rumus ke dalam bilah rumus, dan lalu tekan Ctrl + Shift + Enter. Anda akan melihat hasil yang sama seperti yang Anda lakukan di latihan lebih lama yang digunakan rumus array:

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

Konstanta array dapat berisi angka, teks, nilai logika (seperti TRUE dan FALSE), dan nilai kesalahan (seperti #N/A). Anda dapat menggunakan angka dalam format bilangan bulat, desimal, dan ilmiah. Jika menyertakan teks, Anda perlu menutup teks dengan tanda kutip (").

Konstanta array tidak boleh berisi array, rumus, atau fungsi tambahan. Dengan kata lain, konstanta array hanya boleh berisi teks atau angka yang dipisahkan dengan koma atau titik koma. Excel menampilkan pesan peringatan apabila Anda memasukkan rumus seperti {1,2,A1:D4} atau {1,2,SUM(Q2:Z8)}. Nilai numerik juga tidak boleh berisi tanda persen, tanda dolar, koma, atau kurung.

Salah satu cara terbaik untuk menggunakan konstanta array adalah nama mereka. Konstanta bernama bisa lebih mudah digunakan, dan mereka bisa menyembunyikan beberapa kerumitan rumus array Anda dari orang lain. Untuk memberi nama konstanta array dan menggunakannya dalam rumus, lakukan hal berikut:

  1. Pada tab rumus , dalam grup Nama yang ditentukan, klik Tetapkan nama.
    Kotak dialog Tetapkan nama muncul.

  2. Di kotak Nama, ketikkan Quarter1.

  3. Di kotak Merujuk ke, masukkan konstanta berikut (ingatlah untuk mengetikkan tanda kurung kurawal secara manual):

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

    Konten kotak dialog sekarang terlihat seperti ini:

    Kotak dialog Edit Nama dengan rumus

  4. Klik OK, lalu pilih satu baris tiga sel kosong.

  5. Ketikkan rumus berikut, dan tekan Ctrl + Shift + Enter.

    =Quarter1

    Hasil berikut akan ditampilkan.

    Array yang diberi nama yang dimasukkan sebagai rumus

Ketika Anda menggunakan konstanta bernama seperti rumus array, ingatlah untuk memasukkan tanda sama dengan. Jika tidak, Excel akan menafsirkan array itu sebagai string teks dan rumus Anda tidak akan bekerja seperti yang diharapkan. Terakhir, ingatlah bahwa Anda dapat menggunakan kombinasi teks dan angka.

Perhatikan masalah berikut apabila konstanta array Anda tidak bekerja:

  • Beberapa elemen mungkin tidak dipisahkan dengan karakter yang tepat. Jika Anda menghilangkan koma atau titik koma, atau jika Anda memasukkan satu di tempat yang salah, konstanta array tersebut mungkin tidak bisa dibuat dengan benar, atau Anda mungkin melihat pesan peringatan.

  • Anda mungkin memilih rentang sel yang tidak cocok dengan jumlah elemen dalam konstanta Anda. Misalnya, jika Anda memilih satu kolom enam sel untuk digunakan dengan konstanta lima sel, nilai kesalahan #N/A akan ditampilkan di sel yang kosong. Sebaliknya, jika Anda memilih terlalu sedikit sel, Excel akan menghilangkan nilai yang tidak memiliki sel yang terkait.

Contoh berikut menunjukkan beberapa cara di mana Anda dapat memasukkan konstanta array untuk digunakan dalam rumus array. Beberapa contoh gunakan fungsi TRANSPOSE untuk mengonversi baris ke kolom dan sebaliknya.

Mengalikan setiap item dalam satu array

  1. Buat lembar kerja baru, lalu pilih satu blok sel kosong lebar empat kolom kali tinggi tiga baris.

  2. Ketikkan rumus berikut, dan tekan Ctrl + Shift + Enter:

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

Memangkatkan item dalam satu array

  1. Pilih satu blok sel kosong lebar empat kolom kali tinggi tiga baris.

  2. Ketikkan rumus array berikut, dan 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}

    Atau alternatifnya, masukkan rumus array ini, yang menggunakan operator caret (^):

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

Mengubah urutan baris satu dimensi

  1. Pilih satu kolom lima sel kosong.

  2. Ketikkan rumus berikut, dan tekan Ctrl + Shift + Enter:

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

    Meskipun Anda memasukkan konstanta array horizontal, fungsi TRANSPOSE akan mengonversi konstanta array tersebut menjadi kolom.

Mengubah urutan kolom satu dimensi

  1. Pilih satu kolom lima sel kosong.

  2. Masukkan rumus berikut, dan tekan Ctrl + Shift + Enter:

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

Meskipun Anda memasukkan konstanta array vertikal, fungsi TRANSPOSE akan mengonversi konstanta array tersebut menjadi baris.

Mengubah urutan konstanta dua dimensi

  1. Pilih satu blok sel lebar tiga kolom kali tinggi empat baris.

  2. Masukkan konstanta berikut, dan tekan Ctrl + Shift + Enter:

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

    Fungsi TRANSPOSE mengonversi setiap baris menjadi serangkaian kolom.

Bagian ini menyediakan contoh rumus array dasar.

Membuat array dan konstanta array dari nilai yang ada

Contoh berikut ini menjelaskan cara menggunakan rumus array untuk membuat link antar rentang sel di lembar kerja yang berbeda. Contoh ini juga menunjukkan cara membuat konstanta array dari rangkaian nilai yang sama.

Membuat array dari nilai yang ada

  1. Di lembar kerja di Excel, pilih sel C8:E10, dan masukkan rumus ini:

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

    Pastikan mengetikkan { (kurung kurawal buka) sebelum Anda mengetikkan 10, dan } (kurung kurawal tutup) setelah Anda mengetikkan 90, karena Anda sedang membuat array angka.

  2. Tekan Ctrl + Shift + Enter, yang array angka di rentang sel C8: E10 dengan menggunakan rumus array. Pada lembar kerja Anda, C8 hingga E10 akan terlihat seperti ini:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Pilih rentang sel C1 hingga E3.

  4. Masukkan rumus berikut di bilah rumus, dan tekan Ctrl + Shift + Enter:

    =C8:E10

    Array sel 3 x 3 muncul di sel C1 hingga E3 dengan nilai yang sama yang Anda lihat di C8 hingga E10.

Membuat konstanta array dari nilai yang ada

  1. Dengan sel C1:C3 dipilih, tekan F2 untuk beralih ke edit mode.

  2. Tekan F9 untuk mengubah referensi sel ke nilai. Excel mengonversi nilai konstanta array. Rumus sekarang akan = {10,20,30; 40,50,60; 70,80,90}.

  3. Tekan Ctrl + Shift + Enter untuk memasukkan konstanta array tersebut sebagai rumus array.

Menghitung karakter dalam satu rentang sel

Contoh berikut ini menunjukkan cara menghitung jumlah karakter, termasuk spasi, dalam satu rentang sel.

  1. Salin seluruh tabel ini dan tempelkan ke lembar kerja di sel A1.

    Data

    This is a

    bunch of cells that

    come together

    to form a

    single sentence.

    Karakter total di A2:A6

    =SUM(LEN(A2:A6))

    Konten sel terpanjang (A3)

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

  2. Pilih sel A8, dan tekan Ctrl + Shift + Enter untuk melihat jumlah karakter dalam sel A2: A6 (66).

  3. Pilih sel A10, dan lalu tekan Ctrl + Shift + Enter untuk melihat konten terpanjang dari sel A2: A6 (sel A3).

Rumus berikut yang digunakan dalam sel A8 menghitung jumlah total karakter (66) dalam sel A2 hingga A6.

=SUM(LEN(A2:A6))

Dalam kasus ini, fungsi LEN mengembalikan pendeteksian panjang setiap string teks di setiap sel dalam rentang. Fungsi SUM lalu menambahkan nilai-nilai tersebut bersama-sama dan menampilkan hasil (66).

Menemukan nilai terkecil n dalam satu rentang

Contoh ini menunjukkan cara menemukan tiga nilai terkecil dalam satu rentang sel.

  1. Masukkan beberapa angka acak dalam sel A1:A11.

  2. Pilih sel C1 hingga C3. Sekumpulan sel yang akan memuat hasil yang dikembalikan oleh rumus array.

  3. Masukkan rumus berikut, dan tekan Ctrl + Shift + Enter:

    = SMALL(A1:A11,{1;2;3})

Rumus ini menggunakan konstanta array untuk mengevaluasi fungsi SMALL tiga kali dan kembali terkecil (1), kedua terkecil (2), dan ketiga terkecil (3) anggota di array yang dimuat dalam sel A1:A10 untuk menemukan nilai lainnya, Anda menambahkan beberapa argumen konstanta. Anda juga bisa menggunakan fungsi tambahan dengan rumus ini, seperti SUM atau rata-rata. Misalnya:

= SUM (KECIL (A1:A10, {1,2,3})

= AVERAGE (KECIL (A1:A10, {1,2,3})

Menemukan nilai terbesar n dalam satu rentang

Untuk mencari nilai terbesar dalam satu rentang, Anda dapat mengganti fungsi SMALL dengan fungsi LARGE. Selain itu, contoh berikut ini menggunakan fungsi ROW dan INDIRECT.

  1. Pilih sel D1 sampai D3.

  2. Di bilah rumus, masukkan rumus ini, dan tekan Ctrl + Shift + Enter:

    = LARGE(A1:A10,ROW(INDIRECT("1:3")))

Pada titik ini, ini mungkin membantu untuk mengetahui sedikit baris dan fungsi INDIRECT . Anda bisa menggunakan fungsi ROW untuk membuat array berturut-turut bilangan bulat. Sebagai contoh, pilih kolom kosong 10 sel dalam buku kerja latihan Anda, masukkan rumus array ini, dan lalu tekan Ctrl + Shift + Enter:

=ROW(1:10)

Rumus ini membuat kolom 10 bilangan bulat berurutan. Untuk melihat kemungkinan masalah, sisipkan satu baris di atas rentang yang memuat rumus array (yaitu, di atas baris 1). Excel menyesuaikan referensi baris, dan rumus mengembalikan bilangan bulat dari 2 sampai 11. Untuk mengatasinya, tambahkan fungsi INDIRECT pada rumus:

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

Fungsi INDIRECT menggunakan string teks sebagai argumennya (itulah sebabnya rentang 1:10 ditutup dengan tanda kutip ganda). Excel tidak menyesuaikan nilai teks apabila Anda menyisipkan baris atau memindahkan rumus array. Hasilnya, fungsi ROW akan selalu mengembalikan array bilangan bulat yang Anda inginkan.

Mari kita lihat rumus yang Anda gunakan versi yang lebih lama — = besar (A5:A14,ROW(INDIRECT("1:3"))) — dimulai dari dalam tanda kurung dan bekerja di luar: fungsi INDIRECT mengembalikan sekumpulan nilai teks, dalam hal ini nilai 1 sampai 3. Fungsi ROW pada gilirannya menghasilkan array kolumnar tiga sel. Fungsi LARGE menggunakan nilai dalam rentang sel A5:A14, dan dievaluasi tiga kali, satu kali untuk setiap referensi yang dihasilkan oleh fungsi ROW . Nilai 3200, 2700, dan 2000 dikembalikan ke array kolumnar tiga sel. Jika Anda ingin menemukan nilai lainnya, Anda menambahkan rentang sel lebih besar ke fungsi INDIRECT .

Sebagai contoh-contoh yang lebih lama, Anda bisa menggunakan rumus ini dengan fungsi lain, seperti jumlah dan rata-rata.

Menemukan string teks terpanjang dalam satu rentang sel

Masuk kembali ke contoh string teks yang lebih lama, masukkan rumus berikut di sel kosong, dan tekan Ctrl + Shift + Enter:

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

Teks "bunch of cells that" muncul.

Mari kita mempelajari rumus, dimulai dari elemen dalam dan luar bekerja lebih lanjut. Fungsi LEN mengembalikan pendeteksian panjang setiap item di rentang sel A2: A6. Fungsi MAX menghitung nilai terbesar di antara item-item yang sesuai dengan string teks terpanjang, yang ada di sel A3.

Di sini akan mulai terlihat sedikit kompleks. Fungsi MATCH menghitung offset (posisi relatif) sel yang berisi string teks terpanjang. Untuk melakukannya, diperlukan tiga argumen: nilai pencarian, array pencarian, dan tipe yang cocok. Fungsi MATCH mencari array pencarian untuk nilai pencarian yang ditetapkan. Dalam kasus ini, nilai pencarian adalah string teks terpanjang:

(MAX (LEN(A2:A6))

dan bahwa string itu terdapat dalam array ini:

LEN(A2:A6)

Argumen tipe yang cocok adalah 0. Tipe yang cocok dapat berupa nilai 1, 0, atau -1. Jika Anda menetapkan 1, MATCH mengembalikan nilai terbesar yang kurang dari atau sama dengan nilai pencarian. Jika Anda menetapkan 0, MATCH mengembalikan nilai pertama yang tepat sama dengan nilai pencarian. Jika Anda menetapkan -1, MATCH menemukan nilai terkecil yang lebih besar dari atau sama dengan nilai pencarian yang ditetapkan. Jika Anda menghilangkan tipe yang cocok, Excel mengasumsikan 1.

Akhirnya, fungsi INDEX mengambil argumen ini: array, dan nomor baris dan kolom dalam array tersebut. Rentang sel A2: A6 menyediakan array, fungsi MATCH menyediakan alamat sel dan argumen final (1) menentukan bahwa nilai yang berasal dari kolom pertama dalam array.

Bagian ini menyediakan contoh rumus array tingkat lanjut.

Menjumlahkan rentang yang berisi nilai kesalahan

Fungsi SUM di Excel tidak bekerja apabila Anda mencoba menjumlahkan rentang yang berisi nilai kesalahan, misalnya #N/A. Contoh ini menunjukkan cara menjumlahkan nilai dalam rentang bernama Data yang berisi kesalahan.

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

Rumus ini membuat array baru yang berisi nilai aslinya dikurangi setiap nilai kesalahan. Dimulai dari fungsi dalam ke arah luar, fungsi ISERROR mencari rentang sel (Data) untuk kesalahan. Fungsi IF mengembalikan nilai tertentu jika kondisi yang Anda tetapkan dievaluasi TRUE dan nilai lain jika dievaluasi FALSE. Dalam kasus ini, fungsi akan mengembalikan string kosong ("") untuk semua nilai kesalahan karena dievaluasi ke TRUE, dan mengembalikan nilai yang tersisa dari rentang (Data) karena dievaluasi ke FALSE, yang berarti tidak berisi nilai kesalahan. Fungsi SUM kemudian menghitung jumlah total untuk array yang difilter.

Menghitung jumlah nilai kesalahan dalam satu rentang

Contoh ini serupa dengan rumus sebelumnya, tetapi mengembalikan jumlah nilai kesalahan dalam rentang yang bernama Data sebagai ganti memfilternya:

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

Rumus ini membuat array yang berisi nilai 1 untuk sel yang berisi kesalahan dan nilai 0 untuk sel yang tidak berisi kesalahan. Anda dapat menyederhanakan rumus dan mendapatkan hasil yang sama dengan menghapus argumen ketiga untuk fungsi IF, seperti ini:

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

Jika Anda tidak menetapkan argumen, fungsi IF mengembalikan FALSE jika sel tidak berisi nilai kesalahan. Anda dapat menyederhanakan lagi rumus tersebut:

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

Versi ini bekerja karena TRUE*1=1 dan FALSE*1=0.

Menjumlahkan nilai berdasarkan kondisi

Anda mungkin perlu menjumlahkan nilai berdasarkan kondisi. Sebagai contoh, rumus array ini menjumlahkan hanya bilangan bulat positif dalam rentang yang bernama Sales:

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

Fungsi IF membuat array nilai positif dan nilai salah. Fungsi SUM pada dasarnya mengabaikan nilai salah karena 0+0=0. Rentang sel yang Anda gunakan dalam rumus ini dapat terdiri dari jumlah baris dan kolom berapa pun.

Anda juga dapat menjumlahkan nilai yang memenuhi lebih dari satu kondisi. Misalnya, rumus array ini menghitung nilai yang lebih besar dari 0 dan kurang dari atau sama dengan 5:

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

Perhatikan bahwa rumus ini mengembalikan kesalahan jika rentang berisi satu atau beberapa sel nonnumerik.

Anda juga dapat membuat rumus array yang menggunakan tipe atau kondisi OR. Misalnya, Anda dapat menjumlahkan nilai yang kurang dari 5 dan lebih besar dari 15:

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

Fungsi IF menemukan semua nilai yang lebih kecil dari 5 dan lebih besar dari 15 lalu meneruskan nilai itu ke fungsi SUM.

Anda tidak dapat menggunakan fungsi AND dan OR langsung dalam rumus array karena fungsi itu memberi hasil tunggal, baik TRUE maupun FALSE, dan fungsi array memerlukan array hasil. Anda dapat mengatasinya dengan menggunakan logika yang ditampilkan dalam rumus sebelumnya. Dengan kata lain, Anda menjalankan operasi matematika, seperti penambahan atau perkalian, pada nilai yang memenuhi kondisi OR dan AND.

Menghitung rata-rata tanpa nol

Contoh ini menunjukkan cara mengeluarkan nol dari rentang apabila Anda perlu menghitung nilai rata-rata dalam rentang tersebut. Rumus ini menggunakan rentang data bernama Sales:

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

Fungsi IF membuat array nilai yang tidak sama dengan 0 lalu meneruskan nilai itu ke fungsi AVERAGE.

Menghitung jumlah selisih antara dua rentang sel

Rumus array ini membandingkan nilai dalam dua rentang sel bernama MyData dan YourData dan mengembalikan jumlah perbedaan antara keduanya. Jika konten kedua rentang itu identik, rumus ini akan mengembalikan 0. Untuk menggunakan rumus ini, rentang sel harus berukuran sama dengan dimensi yang sama (misalnya, jika MyData adalah rentang 3 baris kali 5 kolom, YourData juga harus 3 baris kali 5 kolom):

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

Rumus ini membuat array baru dengan ukuran yang sama sebagai rentang yang dibandingkan. Fungsi IF mengisi array dengan nilai 0 dan nilai 1 (0 bila tidak cocok dan 1 untuk sel yang identik). Fungsi SUM kemudian mengembalikan jumlah nilai dalam array tersebut.

Anda dapat menyederhanakan rumus seperti ini:

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

Seperti rumus yang menghitung nilai kesalahan dalam satu rentang, rumus ini bekerja karena TRUE*1=1, dan FALSE*1=0.

Menemukan lokasi nilai maksimum dalam satu rentang

Rumus array ini mengembalikan nomor baris nilai maksimum dalam rentang kolom tunggal yang bernama Data:

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

Fungsi IF menciptakan array baru yang terkait dengan rentang yang bernama Data. Jika sel yang terkait berisi nilai maksimum dalam rentang, array akan berisi nomor baris. Jika tidak, array berisi string kosong (""). Fungsi MIN menggunakan array baru sebagai argumen kedua dan mengembalikan nilai terkecil, yang terkait dengan nomor baris dari nilai maksimum dalam Data. Jika rentang yang bernama Data berisi nilai maksimum yang identik, rumus akan mengembalikan baris nilai pertama.

Jika Anda ingin mengembalikan alamat sel sebenarnya dari nilai maksimum, gunakan rumus ini:

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

Pemberitahuan

Bagian dari artikel ini berdasarkan seri kolom Excel Power pengguna ditulis oleh Colin Wilcox, dan disesuaikan dari bab 14 dan 15 rumus 2002 Excel, buku yang ditulis John Walkenbach, MVP Excel mantan.

Perlu bantuan lainnya?

Anda selalu dapat bertanya kepada pakar di Komunitas Teknologi Excel, mendapatkan dukungan di Komunitas Jawaban, atau menyarankan fitur maupun fitur baru di Suara Pengguna Excel.

Lihat Juga

Larik dinamis dan perilaku luapan larik

Rumus array dinamis vs warisan CSE rumus array

Fungsi FILTER

Fungsi RANDARRAY

Fungsi SEQUENCE

Fungsi SINGLE

Fungsi SORT

Fungsi SORTBY

Fungsi UNIQUE

Kesalahan #LUAPAN! di Excel

Gambaran umum rumus

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×