Dasar-dasar Rumus Array di Excel

Di artikel ini, kami akan membahas bagaimana cara membuat rumus array di Excel.

Bagi pengguna Excel, rumus array penting dipelajari karena efisiensi dan kemampuannya dalam menciptakan perhitungan dinamis. Kita tidak perlu membuat rumus satu per satu untuk setiap nilai.

Butuh waktu dan tenaga untuk belajar rumus array. Tapi, sekali dikuasai, bisa hemat waktu dan tenaga juga.

Rumus array bukan topik yang bisa dicerna sekali baca. Karena itu, kami berusaha menjelaskan sesederhana mungkin.

Apa Itu Array Dalam Excel?

Array adalah daftar.

Bila pernah lihat emak belanja dan bawa secarik kertas berisi daftar belanjaan?

• Nipis.
• Tempe.
• Kangkung.
• Wortel.

Daftar tersebut adalah array.

Bila diterjemahkan ke array di Excel, maka daftar di atas akan menjadi seperti di bawah ini.

{“Nipis”, “Tempe”, “Kangkung”, “Wortel”}

Pada Excel, array terdiri dari dua tipe, yaitu array constant dan array range.

Apakah Rumus Array di Excel Itu?

Rumus array di Excel adalah rumus yang menampilkan mengembalikan (menampilkan) hasil tidak hanya di cell di mana rumus ditulis, tapi juga di cell-cell lain.

Perbedaan antara rumus array dan rumus biasa di Excel adalah jumlah informasi yang diproses dan dikembalikan pada suatu waktu. Rumus array bisa proses dan mengembalikan beberapa informasi pada suatu waktu sekaligus, sementara rumus biasa cuma satu.

Ada dua macam rumus array di Excel, yaitu array constant dan array range.

Pada kelanjutannya, teman-teman akan menemui beberapa fungsi di Excel yang merupakan rumus array, seperti SEQUENCE, CHOOSECOLS, TRANSPOSE, SORT, atau FILTER.

Apakah Array Constant Itu?

Array constant adalah array atau daftar yang tersusun oleh nilai-nilai. Daftar tersebut diawali dan diakhiri dengan kurung kurawal serta dipisahkan oleh tanda titik koma (untuk array horisontal) atau koma (untuk array vertikal).

Penggunaan titik koma atau koma untuk menghasilkan array horisontal atau vertikal bisa berbeda-beda untuk tiap komputer, tergantung pada pengaturan pemisah daftar atau pengaturan komputer tersebut.

Bila anggota berupa teks, awali dan akhiri setiap teks dengan tanda “. Sementara itu, anggota array berupa angka tidak perlu menggunakan tanda “.

{“Nipis”, “Tempe”, “Kangkung”, “Wortel”}

{“Nipis”; “Tempe”; “Kangkung”; “Wortel”}

{5, 2, 2, 1}

{5; 2; 2; 1}

Cara Membuat Rumus Array di Excel dengan Array Constant

Pada demo ini kita akan membuat rumus array constant ke dalam rumus.

(Demo 01) Berikut cara membuat rumus array di Excel dengan array constant.

1. Klik cell di mana rumus ingin ditempatkan.
2. Tuliskan rumus ={"Nipis", "Tempe", "Kangkung", "Wortel"}.
3. Tekan Enter pada keyboard.

Hasilnya bisa dilihat seperti pada gambar di bawah ini.

Bila kita pilih salah satu cell di mana hasil ditampilkan, Excel akan menandai semua cell anggota array.

Perhatikan perbedaan warna isi cell di Formula bar antara cell A1 dan B1. Isi cell B1 berwarna abu-abu. Artinya, isi cell tersebut tidak bisa diedit sementara isi A1 bisa.

Hasil perhitungan rumus array yang meluber keluar dari cell di mana rumus ditulis disebut sebagai spilling.

Nilai-nilai di B1, C1, dan D1 adalah hasil spilling. Bila sudah terisi sebelumnya, cell-cell ini disebut sebagai obstructing cells dan akan mengakibatkan munculnya pesan error #SPILL!.

Yang Perlu Diperhatikan Saat Membuat Rumus Array di Excel

Ada beberapa hal yang perlu diperhatikan saat membuat rumus array di Excel.

1. Seperti menggunakan rumus biasa, kita perlu menekan kunci Enter di keyboard untuk konfirmasi.

Catatan: Pada Excel versi 2019 ke bawah, kita perlu menekan kombinasi kunci Ctrl+Shift+Enter. Itulah sebabnya kenapa rumus array kadang disebut juga rumus CSE (singkatan dari Control, Shift, dan Enter).

2. Pada array range, Excel menambahkan kurung kurawal di awal dan di akhir rumus. Kurung-kurung ini otomatis hilang saat kita edit rumus. Bila kita coba-coba tambahkan sendiri kurung kurawal sebelum menekan Enter, yang ada malahan error.

Apakah Dimensi Array di Excel Itu?

Pada contoh array-array di atas, array hanya terdiri dari satu kolom atau satu baris. Array semacam ini disebut sebagai array satu dimensi atau array 1-D, kerennya gitu.

Array yang terdiri dari beberapa kolom atau baris disebut sebagai array dua dimensi atau array 2-D.

Array 2-D dibuat dengan menggunakan kombinasi tanda koma (sebagai pemisah kolom) dan tanda titik koma (sebagai pemisah baris).

Cara membuat array dua dimensi di Excel untuk constant array adalah menyusun anggota-anggota dari suatu baris terlebih dahulu di mana setiap anggota array dipisahkan oleh tanda koma. Sebagai penanda baris baru, gunakan tanda titik koma.

Menggunakan rumus array constant 2-D sama dengan array constant 1-D.

={"Nipis", "Tempe", "Kangkung", "Wortel"; 10, 2, 2, 8}

Hasil rumus di atas bisa dilihat pada gambar di bawah ini.

={"Nipis", 10; "Tempe", 2; "Kangkung", 2; "Wortel", 8}

Cara Memberi Nama Array Constant di Excel

Array dengan format seperti rumus array seperti  ={"Januari", "Februari", "Maret"} bisa jadi sulit diingat bagi sebagian orang. Atau, kalau pun ingat, merepotkan setiap kali harus ditulis ulang.

Excel punya cara untuk mempermudah beban kerja otak atau jari kita.

(Demo 02) Berikut cara memberi nama array constant di Excel.

1. Pada tab Formulas → Kelompok perintah Defined Names, klik Define Name.
2. Pada Name, berikan nama yang sesuai untuk array yang diinginkan.
3. Pada Scope, tentukan cakupan nama, yaitu hanya pada worksheet aktif atau workbook aktif.
4. Berikan komentar di Comment sebagai keterangan nama, bila dibutuhkan.
5. Pada Refers to, Masukkan rumus array yang diinginkan, yaitu ={"Januari", "Februari", "Maret"}.
6. Klik OK.

Coba ketikkan =TriwulanSatu di worksheet. Excel secara otomatis akan menampilkan array constant sesuai dengan nama yang kita ketikkan.

Array Range di Excel

Mungkin teman-teman bertanya. Bila hasil rumus ={"Nipis", "Tempe", "Kangkung", "Wortel"} adalah seperti yang ditunjukkan oleh Gambar 02, apakah berarti range data (seperti pada Gambar 07) yang kita buat sendiri tanpa menggunakan array constant adalah array juga?

Bila range data tersebut digunakan sebagai parameter bagi rumus array, maka range data tersebut juga array. Array semacam ini disebut sebagai array range.

Seperti array constant, array range juga bisa berbentuk array 1-D (terdiri dari satu kolom atau baris) atau 2-D (terdiri dari beberapa kolom atau baris).

Array range dinyatakan dalam format range biasa, seperti A1:D4.

Cara Membuat Rumus Array di Excel dengan Array Range

Perhatikan gambar di bawah ini.

Kita berniat mengisi besaran nominal komisi yang diterima setiap sales pada range D3:D7. Setelahnya, kita akan mengisi total komisi di D8.

Kita, tentu saja, bisa lakukan dengan cara biasa, yaitu dengan:

• =B3*C3 di cell D3, lalu copykan D4:D7.
• =SUM(D3:D7) di D8.

Tapi, kita akan coba menyelesaikannya dengan rumus array range. Kalau tidak, ya, percuma aja bahasan artikel ini tentang rumus array.

Secara khusus, rumus array gak beda dengan rumus biasa.

Berikut langkah pikiran kami dalam menyusun rumus array range.

• Kita ingin menampilkan total hasil perkalian nilai penjualan dengan komisi.
• Bentuk dasar perkalian dan komisi sendiri adalah rumus =B3*C3.
• Masing-masing B3 dan C3 adalah anggota dari dua range, yaitu B3:B7 dan C3:C7.
• Untuk mengalikan dua array range, lakukan dengan rumus =(B3:B7)*(C3:C7). Rumus ini kita gunakan untuk mendapatkan komisi setiap sales.
• Total Komisi adalah total penjumlahan dari komisi setiap sales, maka kita perlu menggunakan SUM terhadap hasil perkalian dua range array di atas.
• Dengan demikian, rumus array untuk Total Komisi adalah =SUM((B3:B7)*(C3:C7)).

Setelah menemukan rumus array, langkah berikutnya adalah mengisikan rumus itu di cell yang diinginkan.

(Demo 03) Berikut cara membuat rumus array di Excel dengan array range.

1. Klik cell D3, lalu isikan rumus =(B3:B7)*(C3:C7).
2. Tekan Enter.
3. Klik cell D8, lalu isikan rumus =SUM((B3:B7)*(C3:C7)).
4. Tekan Enter.

Hasilnya bisa dilihat seperti pada gambar di bawah ini.

Catatan: Rumus array di mana hasil perhitungan tumpah atau spilling ke cell-cell lain, seperti =(B3:B7)*(C3:C7) disebut rumus cell tunggal atau single-cell formula. Excel memproses dan menampilkan setiap perhitungan di cell-cellnya tersendiri.

Rumus array di mana perhitungan hanya tampil di cell di mana rumus ditulis, seperti =SUM((B3:B7)*(C3:C7)) disebut rumus multi cell atau multi-cells formula. Excel mengolah semua informasi di semua cell terpilih.

Cara Edit Rumus Array di Excel

Cara edit rumus array di Excel sama dengan cara edit rumus biasa.

Perbedaannya, kita perlu edit di cell di mana rumus dimasukkan, bukan di sembarang cell di mana rumus ditampilkan.

Bila kita coba-coba edit rumus di cell-cell di mana hasil rumus ditampilkan, Excel akan menampilkan pesan kesalahan #SPILL!.

(Demo 04) Berikut cara edit rumus array di Excel.

1. Klik cell di mana rumus berada. Tekan kunci F2.
2. Edit rumus array sesuai kebutuhan.
3. Tekan Enter untuk konfirmasi perubahan rumus.

Cara Copy Rumus Array di Excel

Untuk copy rumus array, copy paste terhadap cell sumber adalah cara umum.

Sebagai alternatif, kita bisa gunakan cell sumber sebagai cell referensi diikuti dengan tanda pagar. Contoh: =A1#.

(Demo 05) Berikut cara copy rumus array di Excel.

1. Pilih cell di mana hasil copy dan spilling akan ditempatkan.
2. Tuliskan rumus =A1#, di mana A1 adalah cell sumber dari mana rumus akan dicopy.
3. Tekan Enter.

Perhatian: Jangan lupa tambahkan tanda #. Tanda tersebut berarti kita berniat copy rumus dan hasilnya, termasuk spilling. Bila hanya =A1, Excel hanya akan copy nilai pada cell A1 atau sama dengan referensi biasa.

Cara Copy Hasil Rumus Array di Excel

Untuk copy hasil rumus array di Excel dan bukan rumusnya sendiri, gunakan perintah Paste Special.

(Demo 06) Berikut cara copy hasil rumus array di Excel dengan perintah Paste Special.

1. Pilih cell-cell atau range di mana hasil rumus berada.
2. Tekan shortcut Ctrl+C atau perintah Copy di Ribbon.
3. Pilih cell tujuan di mana hasil copy akan ditampilkan.
4. Tekan shortcut Ctrl+Alt+V untuk membuka kotak dialog Paste Special.
5. Pada kelompok Paste, pilih Values.
6. Klik OK.

Cara Menghapus Rumus Array di Excel

Seperti edit rumus array, menghapus rumus array juga hanya bisa dilakukan di cell sumber di mana rumus ditulis.

Bila kita keisengan coba hapus rumus di daerah spilling, maka Excel akan menampilkan pesan kesalahan.

Cara menghapus rumus array di Excel adalah cukup posisikan kursor cell di cell di mana rumus ditulis, lalu tekan Delete pada keyboard.

---

SebelumnyaBerikutnya

---