Anda disini: M Jurnal » Rumus Excel » Rumus ARRAY Excel: Pengertian, Aturan Penulisan + Contoh

Rumus ARRAY Excel: Pengertian, Aturan Penulisan + Contoh

Rumus ARRAY Excel: Belajar Formula khusus (pengertian, aturan penulisan, cara menggunakan serta contoh)

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…

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:

Dasar Rumus / Formula ARRAY :
{=
}

  1. Tanda Sama Dengan =. Dalam menggunakan Rumus / Fungsi Excel, Anda bisa mengawalinya dengan tanda sama dengan.
  2. Argument … Bisa berupa beberapa range data atau sebuah Rumus biasa atau Fungsi seperti SUM, AVERAGE dll. Argument ini disebut juga ARRAY.
  3. 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 5x1 sama dengan Range A1:A5, yaitu sama-sama tersusun dalam 1 kolom vertikal dan banyak baris.

Rumus ARRAY 1 Dimensi Vertikal

#2 Rumus ARRAY 1 Dimensi Horizontal

Sudah jelas, Matriks Ordo 1x5 sama dengan Range G1:K1, yaitu sama-sama tersusun dalam 1 kolom vertikal dan banyak baris.

Rumus ARRAY 1 Dimensi Horizontal

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.

Rums ARRAY 2 Dimensi

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:

Contoh Soal Rumus ARRAY Excel

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.

Hitung selisih data

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.

Hitung Rata-rata dari selisih data

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:

Cara menggunakan Rumus ARRAY Cell Tunggal pada Excel (Formula ARRAY)
  1. Pertama, ketik Fungsi tanpa argument, =AVERAGE(
  2. Kedua, pilih (blok) Range Data Pertama yaitu F2:F6
  3. Ketiga, ketik tanda kurang sehingga rumus menjadi =AVERAGE(F2:F6
  4. Keempat, karena kita akan mencari selisih data kolom F dan E, maka pilih (blok) Range Data Kedua yaitu E2:E6
  5. Kelima, ketik tanda tutup kurung “)”.
  6. Terakhir, tekan tombol CTRL, SHIFT dan ENTER pada Keyboard secara bersamaan. dan lihat hasilnya:
Cara menggunakan Rumus ARRAY Cell Tunggal Excel (Formula ARRAY)

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:

Rumus ARRAY Cell Tunggal:
{=
AVERAGE(F2:F6E2:E6)}

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:F6E2:E6).

Kemudian Excel akan mengekstrak ARRAY ini (kedua Range data) menjadi argument dasar Fungsi AVERAGE yaitu =AVERAGE(F2E2,F3E3,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 F2E2 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.

Contoh Kesalahan pada Formula ARRAY Excel

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:

Contoh Kesalahan pada Formula ARRAY Excel

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.

Pembuktian Hasil Rumus ARRAY

#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:

Contoh Cara menggunakan Rumus ARRAY Multi Cell pada Microsoft Excel
  1. 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.
  2. Kedua, ketik tanda sama dengan untuk memulai Rumus.
  3. Ketiga, pilih (blok) Range data pertama yaitu F2:F6
  4. Keempat, ketik tanda kurang –
  5. Kelima, pilih (blok) range data kedua yaitu E2:E6
  6. Terakhir, tekan CTRL, SHIFT dan ENTER secara bersamaan. Dan lihat hasilnya:
Contoh Cara menggunakan Rumus ARRAY Multi Cell pada Microsoft Excel

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

Contoh Cara menggunakan Rumus ARRAY Multi Cell pada Microsoft 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:

  1. 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.
  2. Menghapus salah satu cell yang menggunakan Rumus ARRAY. Sama seperti point 1, Anda hanya bisa menghapus semua cell yang menggunakan Rumus ARRAY tersebut.
  3. Tidak bisa sembarangan Autofill / Copy-Paste rumus.
  4. 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:

Formula ARRAY tidak bisa dihapus sebagian

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.

Contoh Tidak bisa Autofill / Copy Paste Rumus ARRAY Multi Cell

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:

Contoh kesalahan konstanta pada Formula

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:

Solusi ketika ada kesalahan dalam menggunakan Rumus ARRAY Multi Cell

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:

Solusi ketika terjadi kesalahan

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 ?

Solusi Tidak bisa mengubah rumus pada Cell (ARRAY Formula)

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…

Pintasan Panduan ARRAY Formulas Excel

  1. ARRAY Formulas: (Anda Disini)
  2. Constant ARRAY: Mengenal Konstanta pada Rumus ARRAY (1 Dimensi Vertikal, Horizontal serta 2 Dimensi).
  3. Named Constans: Cara memberikan nama (identitas) pada Konstanta ARRAY agar bisa digunakan kembali dengan mudah.
  4. COUNT-Errors: Menggunakan Array untuk menghitung jumlah kesalahan rumus.
  5. COUNT-Unique Values: Menghitung nilai unik menggunakan Array, Fungsi UNIQUE dll
  6. COUNT-with Or Criteria: Menghitung dengan atau tanpa kriteria. Contoh complete.
  7. SUMEvery Nth Row: Cara menjumlahkan setiap baris ke-n pada Excel dengan Array
  8. SUM-Largest Numbers: Cara menjumlahkan angka terbesar dalam range tertentu.
  9. SUM-Range with Errors: Cara menjumlahkan data pada range yang terdapat pesan error Excel
  10. SUMwith Or Criteria: Cara menjumlahkan dengan kriteria (contoh kompleks)
  11. Most Frequently Occuring Word: Cara mencari kata yang paling sering muncul di Excel.
  12. System of Linear Equations: Cara menghitung persamaan linier pada Excel.

Apakah Panduan ini membantu menyelesaikan permasalahan Anda ?

Rolan Mardani

Traktir Saya secangkir kopi supaya kuat begadang untuk membuat konten-konten Panduan yang berkualitas dan membantu menyelesaikan permasalahan Anda.

Komentar Paling Lambat di Balas Pukul 23:59 :)