Rumus ARRAY Excel dapat melakukan perhitungan rumit yang tidak bisa Anda selesaikan dengan menggunakan Rumus / Fungsi biasa (non-array). Namun untuk menggunakannya membutuhkan sedikit kemampuan teknis.
Jika Anda ingin menjadi mahir dalam menggunakan Rumus ARRAY pada Excel, ada baiknya belajar dari dasar terlebih dahulu. Dasar penggunaan Rumus ARRAY ini akan menjadi bekal Anda untuk lanjut ke tingkat yang lebih sulit. Sudah siap ??? Mari kita mulai…
Table of Contents
Pengertian Rumus / Formula ARRAY Excel
Dalam bahasa pemrograman komputer, ARRAY lebih sering dikenal sebagai suatu variabel yang menyimpan lebih dari 1 buah data (dengan tipe yang sama) ke dalam memory.
Sementara pada Microsoft Excel, ARRAY adalah suatu Konstanta yang berupa sebuah range atau sekumpulan item / nilai / data / cell yang berasal dari sebuah kolom / baris ataupun beberapa kolom dan baris.
Misalnya, Range A1:A10 atau A2:F2 atau A1:C5 dan sejenisnya bisa disebut sebuah ARRAY.
Penting! Untuk memudahkan Anda memahami materi ini, Saya harap Anda sudah tahu apa itu Range pada Excel. Jika belum, silahkan kunjungi Panduan Range Excel M Jurnal.
Dengan kata lain, Rumus / Fungsi ARRAY dalam Excel adalah sebuah Formula yang melakukan kalkulasi (perhitungan) data, baik pada satu atau beberapa item yang terdapat didalam ARRAY yang kemudian memberikan satu atau beberapa hasil.
Jika masih sulit memahami penjelasan tersebut, maka ada baiknya pahami aturan penulisan ARRAY Formula terlebih dahulu.
Mudah-mudahan, Anda bisa memahami lebih dalam tentang ARRAY Formula dari penjelasan berikut: (Saya upayakan tidak menggunakan bahasa teknis)
Aturan Penulisan Rumus ARRAY Excel
Semua Rumus dan Fungsi Excel memiliki aturan penulisan masing-masing. Begitu juga dengan ARRAY Formula. Untuk tahap pengenalan, coba pahami aturan penulisan Rumus ARRAY berikut:
- Tanda Sama Dengan =. Dalam menggunakan Rumus / Fungsi Excel, Anda bisa mengawalinya dengan tanda sama dengan.
- Argument … Bisa berupa beberapa range data atau sebuah Rumus biasa atau Fungsi seperti SUM, AVERAGE dll. Argument ini disebut juga ARRAY.
- Buka dan Tutup Kurung Kurawal { }. Tanda ini wajib Ada dalam Formula Anda. Tanda ini merupakan ciri khas Formula ARRAY pada Excel.
Note: Jangan Ketik Kurung Kurawal {…} secara manual, karena Excel yang akan menyisipkannya secara otomatis. Ada caranya…
Dimensi Rumus ARRAY Excel
Apa itu Dimensi pada Rumus ARRAY ? Sederhananya begini… Anda ingat pelajaran Matriks pada Matematika ? Yaa.. kurang lebih hampir sama dengan matriks.
Misalnya, Matriks Ordo 5×1 adalah matriks yang tersusun dalam 5 Baris dan 1 Kolom. Sedangkan Matriks Ordo 5×2 tersusun dalam 5 Baris dan 2 Kolom. Baris dan kolom inilah yang menentukan jenis Dimensi Rumus ARRAY.
Biar semakin jelas, Saya uraikan ke 3 jenis Dimensi Rumus ARRAY ini:
#1 Rumus ARRAY 1 Dimensi Vertikal
Rumus Array 1 Dimensi Vertikal artinya data tersusun dalam 1 kolom vertikal (dengan jumlah baris lebih dari 1). Jadi Matriks Ordo 5×1 sama dengan Range A1:A5, yaitu sama-sama tersusun dalam 1 kolom vertikal dan banyak baris.
#2 Rumus ARRAY 1 Dimensi Horizontal
Sudah jelas, Matriks Ordo 1×5 sama dengan Range G1:K1, yaitu sama-sama tersusun dalam 1 kolom vertikal dan banyak baris.
Inilah Rumus ARRAY 1 Dimensi Horizontal. Sejauh ini Saya yakin tidak sulit memahaminya..
#3 Rumus ARRAY 2 Dimensi
Karena ada 2 Dimensi, sudah pasti range data tersusun lebih dari 1 baris dan kolom. Jadi kurang lebih seperti Matriks Ordo 5×2 atau range E2:F6 yang terdiri dari 5 baris dan 2 kolom.
Jenis, Contoh + Cara Menggunakan Rumus ARRAY
Ketika menggunakan Rumus / Fungsi biasa, Anda perlu menekan Enter / Tombol Tab setelah memasukan Rumus / Fungsi. Misalnya, coba perhatikan contoh soal berikut:
Setiap Mahasiswa mengikuti Test dan mendapatkan nilai (Kolom E). Karena tidak ada Mahasiswa yang mendapatkan nilai >=75, Maka semua mengikuti Test Ulang (Kolom F). Kemudian, Saya ingin mengetahui berapa Rata-rata Peningkatan nilai Mahasiswa.
Jika Soal seperti ini, alur yang paling mudah adalah dengan menghitung Selisih Test Ulang dengan Test terlebih dahulu. Hasilnya tampak pada Kolom G. Kemudian, Anda bisa menggunakan Fungsi AVERAGE dari Range G2:G6.
Penting! Setidaknya Anda juga harus memahami cara menggunakan Rumus AVERAGE terlebih dahulu. Selengkapnya silahkan kunjungi Panduan Fungsi AVERAGE Excel.
Sejauh ini tidak ada yang salah dengan proses dan tahap perhitungan pada contoh tersebut.
Namun, dengan menggunakan ARRAY Formula, Anda hanya perlu menggunakan 1 Rumus tanpa menghitung selisih Nilai Test Ulang dengan Nilai Test. Teknik ini bisa menghemat waktu Anda.
#1 Rumus ARRAY Cell Tunggal
Rumus ARRAY Cell Tunggal artinya Anda akan menampilkan hasil perhitungan hanya pada 1 cell saja. Sementara, sumber data bisa terdiri dari beberapa cell (sebuah range).
Misalnya pada contoh sebelumnya, Anda bisa mengatahui Rata-rata Peningkatan Nilai Mahasiswa secara keseluruhan hanya menggunakan Rumus ARRAY pada Fungsi AVERAGE dengan cara berikut:
- Pertama, ketik Fungsi tanpa argument, =AVERAGE(
- Kedua, pilih (blok) Range Data Pertama yaitu F2:F6
- Ketiga, ketik tanda kurang sehingga rumus menjadi =AVERAGE(F2:F6–
- Keempat, karena kita akan mencari selisih data kolom F dan E, maka pilih (blok) Range Data Kedua yaitu E2:E6
- Kelima, ketik tanda tutup kurung “)”.
- Terakhir, tekan tombol CTRL, SHIFT dan ENTER pada Keyboard secara bersamaan. dan lihat hasilnya:
Benar bukan ? Excel memberikan hasil yang sama. Coba lihat pada Formula Bar, Rumus AVERAGE tersebut di apit kurung kurawal seperti berikut: {=AVERAGE(F2:F6-E2:E6)}.
Note: Jangan Ketik Kurung Kurawal {…} secara manual, karena Excel yang akan menyisipkannya secara otomatis.
Selain itu, didalam Fungsi AVERAGE terdapat 2 buah Range Data didalam 1 argument yaitu F2:F6 dan E2:E6. Lihat, tidak ada Operator Pemisah Rumus ( , atau ; ) antara Range data tersebut.
Seperti yang sudah Anda pelajari pada Aturan Penulisan Rumus AVERAGE, bahwa dalam 1 argument rumus tidak boleh terdapat lebih dari 1 range data (kecuali ARRAY Formula).
Apabila terdapat lebih dari 1 Range data, maka disebut pula sebagai ARRAY Formula.
Saya Jelaskan Bagaimana Cara Kerja Formula Tersebut.
Pertama-tama, perhatikan Rumus ARRAY yang digunakan:
Perhatikan! Terdapat 2 range data yang berbeda dengan jumlah kolom dan baris yang sama (F2:F6 dan E2:E6) serta di antara kedua range tersebut terdapat tanda kurang (F2:F6–E2:E6).
Kemudian Excel akan mengekstrak ARRAY ini (kedua Range data) menjadi argument dasar Fungsi AVERAGE yaitu =AVERAGE(F2–E2,F3–E3,F4-E4,F5-E5,F6-E6).
Penting! Setidaknya Anda juga harus paham bahwa Operator Pemisah Rumus pada Excel dengan Format English (,) dan Indonesia (;). Jika belum tahu, silahkan kunjungi Panduan Format English vs Indonesia
Lihat, argument F2–E2 kemudian F3-E3 dan seterusnya… Rumus ini sama saja dengan menghitung selisih antara nilai Tests Ulang dengan Nilai Test Mahasiswa. Untungnya, Anda tidak perlu membuat rumus tersebut.
Sehingga angka yang digunakan Excel untuk melanjutkan Fungsi AVERAGE adalah =AVERAGE(25,39,49,50,19) dan mendapatkan hasil 36.4. Mudah bukan ???
Saya Buktikan Kebenaran Hasil Formula ini…
Jika Anda kurang yakin, inilah beberapa pertanyaan yang sering diajukan sekaligus Saya berikan buktinya:
1. Benarkah Kurung Kurawal tersebut tidak boleh diketik secara manual ?
Coba buktikan… Silahkan edit Rumus pada Cell I1, maka tanda kurung kurawal tersebut akan menghilang secara otomatis.
Kemudian coba Anda ketik kurung kurawal secara manual dan tekan enter, maka Excel tidak akan menghitung apapun. Melainkan menunjukkan hasil Seperti yang Anda ketik.
2. Bisa gak tanpa menggunakan CTRL + SHIFT + ENTER ?
Untuk ARRAY Formula, TIDAK BISA. Jika dipaksakan, maka Excel akan memberikan hasil Pesan Error #VALUE! seperti gambar berikut:
3. Apa benar hasil Rumus ARRAY tersebut sama dengan Proses Perhitungan Biasa ?
Ya, Pasti Sama dong. Anda bisa membuktikannya dengan bantuan Fungsi IF. Coba gunakan Rumus berikut: =IF(I1=G7,”Sama Persis”,”Tidak Sama”) untuk membuktikannya.
#2 Rumus ARRAY Multi Cell
Sudah pasti jelas bahwa Rumus ARRAY Multi Cell digunakan ketika Anda ingin menggunakan Rumus ARRAY ke beberapa (banyak) cell secara bersamaan.
Saya masih menggunakan contoh yang pertama. Sebelumnya, pada Kolom G, Saya menggunakan Rumus =F2-E2 sebagai rumus pengurangan.
Penting! Saya juga menulis 8 Trik Rumus Pengurangan Excel. Selengkapnya silahkan kunjungi Panduan Rumus Kurang Excel
Sementara Anda juga bisa menggantikan Rumus tersebut dengan ARRAY Formula Multi Cell dengan cara sebagai berikut:
- Pertama, pilih (blok) semua cell yang akan menampilkan hasil Rumus ARRAY mulai dari Cell paling sudut kiri atas. Dalam contoh ini blok dari Cell H2 sampai H6.
- Kedua, ketik tanda sama dengan untuk memulai Rumus.
- Ketiga, pilih (blok) Range data pertama yaitu F2:F6
- Keempat, ketik tanda kurang –
- Kelima, pilih (blok) range data kedua yaitu E2:E6
- Terakhir, tekan CTRL, SHIFT dan ENTER secara bersamaan. Dan lihat hasilnya:
Anda akan melihat, semua Cell pada range H2:H6 akan menggunakan rumus yang sama yaitu {=F2:F6-E2:E6}.
Bagimana dengan makna dan cara kerjanya ? Secara garis besar sama saja. Excel akan mengurangi cell pertama dari Range data pertama dengan cell pertama dari range data kedua. Begitu juga dengan cell kedua dan seterusnya.
Saya ada contoh lain nih…
Misalnya Saya ingin mengali Range data pertama dengan angka 2. Artinya setiap cell akan Saya kali dengan angka 2.
Anda cukup mengganti tanda kurang (-) menjadi asteriks (*) untuk perkalian serta mengubah Range data ke dua menjadi angka 2, kemudian tekan CTRL, SHIFT dan ENTER.
Penting! Setidaknya Anda juga harus memahami bagaimana cara menghitung perkalian pada Excel. Selengkapnya bisa Anda pelajari pada Panduan Rumus Perkalian Excel
Jadi, ARRAY bisa menggunakan Range data / Cell / Nilai yang diketik secara manual (Konstanta) / rumus. Anda bisa mempelajarinya pada SUB-BAB berikutnya (Lihat Pintasan Panduan)
Note: Satu-satunya perbedaan adalah, Rumus ARRAY Multi Cell digunakan sekaligus pada banyak cell. Sementara Rumus ARRAY Cell Tunggal hanya digunakan untuk 1 cell saja.
Fakta Rumus ARRAY Multi Cell !
Ketika Anda menggunakan Rumus ARRAY Multi Cell dengan 2 range data atau lebih, maka Anda tidak bisa menggunakan beberapa fitur Editing seperti:
- Menghapus Row / Colum dari Range Data yang menggunakan Rumus ARRAY. Misalnya Range A1:A10 menggunakan ARRAY Formula, maka Anda tidak bisa menghapus salah satu / beberapa baris dari Row 1 – 10 kecuali menghapus semua Row yang menggunakan ARRAY Formula tersebut.
- Menghapus salah satu cell yang menggunakan Rumus ARRAY. Sama seperti point 1, Anda hanya bisa menghapus semua cell yang menggunakan Rumus ARRAY tersebut.
- Tidak bisa sembarangan Autofill / Copy-Paste rumus.
- Menghapus salah satu / sebagian cell yang menggunakan Rumus ARRAY Multi Cell Excel. Sama seperti point 1 dan 2.
Misalnya, Saya ingin menghapus Cell H4, dan lihat, Excel memberikan notif “Your cannot change part of an array” seperti gambar berikut:
Ini artinya, Cell tersebut merupakan bagian dari Rumus ARRAY. Jadi jika ingin menghapusnya, Anda harus menghapus semua dari cell H2 sampai H6.
Sejauh ini, baru itu beberapa fakta yang sudah Saya temukan. Tapi jika Anda menemukan fakta lain, silahkan sampaikan pada kolom komentar ya…
Bagaimana Jika Saya Terlanjur Memasukkan Fungsi Tersebut Hanya pada 1 Cell ?
Misalnya, Saya ingin Membagi Nilai Test dengan SUM Nilai Test. Kemudian Saya menggunakan Rumus ini {=E2:E6/SUM(E2:E6)} hanya pada Cell J2. Lalu coba Anda lakukan AutoFill atau Copy-Paste rumus pada Cell J2 ke cell J3 sampai J6.
Penting! Pastikan Anda sudah memahami cara menggunakan Rumus SUM. Jika belum, silahkan kunjungi Panduan Fungsi SUM Excel.
Sekarang kita coba buktikan, apakah Rumus pada Cell J3 sampai J6 sesuai dengan tujuan kita. Saya menghitung SUM nilai Test pada Cell F12. Kemudian membagi masing-masing nilai Test dengan dengan SUM Test dan lihat hasilnya:
Dan ternyata, 4 cell tidak memiliki hasil yang sama dengan hasil pada Rumus ARRAY Multi Cell (J3:J6). Lalu apa yang salah ? Coba klik cell J4 dan lihat Reference rumusnya juga berubah:
Reference Fungsi SUM yang seharusnya (E2:E6) ikut berubah setelah melakukan Autofill / Copy-Paste Rumus. Tentu ini tidak sesuai dengan tujuan kita.
Penting! Mengapa reference rumusnya bisa berubah ? Itu karena Saya melakukan Autofill atau Copy-Paste Rumus yang menggunakan Reference Cell Relatif
Untuk mengatasinya (khusus Rumus ARRAY), silahkan pilih (blok) Range J2:J6. Kemudian, tekan tombol F2 pada Keyboard untuk mengaktifkan formula bar. Terakhir silahkan tekan tombol CTRL, SHIFT dan ENTER secara bersamaan dan lihat hasilnya:
Benar bukan ? Sekarang hasil Rumus ARRAY tersebut sudah lebih tepat.
Bonus: Mau Merubah Rumus ARRAY ? Cukup Ubah Rumus pada 1 Cell Saja
Salah satu keuntungan ketika Anda menggunakan Rumus ARRAY Multi Cell adalah ketika Anda ingin mengubah Rumus, maka Anda hanya perlu mengubah Rumus pada 1 Cell saja.
Misalnya Rumus ARRAY pada Kolom J {=E2:E6/SUM(E2:E6)} ingin Saya ubah menjadi {=E2:E6*2}. Saya hanya mengubah Rumus pada Cell J4, kemudian tekan CTRL, SHIFT dan ENTER secara bersamaan.
Secara otomatis, Excel mengubah semua Rumus ARRAY pada Cell J2 sampai J6. Bagaimana, mudah bukan ?
Sampai disini, Saya harap Anda sudah benar-benar memahami dasar-dasar menggunakan Rumus ARRAY Excel (Cell Tunggal maupun Multi Cell).
Pada materi selanjutnya, Anda akan belajar lebih dalam tentang Konstanta pada Rumus ARRAY serta beberapa contoh yang lebih kompleks. Perhatikan bahwa Konstanta ini sangat penting. Jadi jangan Sampai melewatkan materinya ya…