Fungsi Array Formula pada Excel

Array yaitu kumpulan dari beberapa cell yang tersebar pada suatu kolom, baris atau pada beberapa baris dan kolom (multi columns and rows). Ilustrasinya dapat dilihat sebagai berikut.  

Array Formula adalah formula yang berfungsi untuk data range atau multi cell (cell-cell tunggal) maka setiap operasi dasar dapat digunakan array formula ini, tapi notasi dan entri antara satu formula dengan formula lainnya sedikit berbeda. Pada beberapa istilah, array formula disebut dengan CSE Formula (Control+ Shift +Enter Formula). Array Formula sering digunakan dalam pemprograman jadi bila anda seorang programing maka hal ini sudah biasa dikenal. Akan tetapi oprasi fungsi Array Formula pada Excel sedikit berbeda dalam bentuk konsepnya. Berikut contoh penggunaan operasi fungsi Array Formula yang dikemas dalam bentuk interaktif.

1. Buka Microsoft Excel dan buat satu workbook baru.
2. Isilah worksheet dengan data seperti pada gambar berikut. 

3.  Isi cell-cell pada kolom SubTotal berdasarkan perkalian dari kolom Jumlah dan Harga buatlah selection pada range E2:E5 yang berada di bawah kolom SubTotal.

4.   Ketik formula =C2:C5*D2:D5, jangan tekan tombol apapun   setelah Anda melengkapi formula tersebut.

5.  Eksekusi formula tersebut sebagai array formula dengan menekan tombol keyboard CTRL + SHIFT + ENTER (CSE). Posisi tombol terlihat warna kuning  pada gambar screenshot  keyboard berikut ini.

6.  Eksekusi tombol CSE akan terlihat seperti gambar berikut. Perhatikan formula telah diapit oleh pasangan kurung kurawal { … }. Ini menandakan bahwa range cell sudah dianggap sebagai array formula.

7.   Arahkan cursor ke cell E2:E5, maka akan mendapatkan formula yang sama.

8.   Pilih salah satu cell dari E2:E5 lalu hapus dengan menekan tombol Delete. Akan terjadi error dengan pesan seperti berikut – You cannot change part of an array. Ini artinya range dari Array Formula adalah satu kesatuan dan tidak dapat dihapus secara individual.

Penggunaan array formula di atas, terlihat bahwa hasil eksekusi berupa array atau terdiri dari beberapa cell sebagai satu kesatuan (Multi-Cell Formula). 

Akan tetapi array formula tidak selalu menghasilkan multi-cell melainkan satu cell saja (Single-Cell Formula). 

Kedua jenis ini sama, karena pada bagian formulanya terdapat array yang dijadikan sebagai input bagi operator. Lebih jelasnya bisa di lihat pada gambar berikut ini.

Contoh Single-Cell Formula

1.  Tempatkan cursor pada cell E6. Masukkan formula =SUM (C2:C5*D2:D5) pada cell tersebut dan tekan tombol Control + Shift +Enter.

 2.  Hasilnya tampak seperti pada gambar di bawah ini. Terlihat bahwa formula diapit oleh kurung kurawal sebagai tanda itu adalah array formula. Perhatikan bahwa formula ini langsung menghitung berdasarkan hasil perkalian array C2:C5 dan D2:D5.

Contoh Conditional Average Formula

1.   Pada worksheet ini kita akan menghitung rata-rata nilai subtotal untuk produk non telur. Dengan cara lakukan perubahan pada worksheet sebelumnya sehingga tampak seperti pada gambar berikut.

2.   Pada cell F7 tekan tombol Ctrl + Shift + Enter lalu masukan formula berikut ini =AVERAGE(IF(C2:C5=”Tidak”,D2:D5*E2:E5)). 

      Formula ini akan menyaring kategori yang bukan telur (nilai “Tidak” pada kolom C), dan  kemudian menghitung rata-rata dari sub total yang dihitung dari perkalian Jumlah dan Harga (kolom D danE). Hasilnya akan terlihat seperti pada gambar berikut.

Keuntungan Menggunakan Array Formula

1. Setiap cell dari hasil Multi-Cell memiliki formula yang sama (konsisten).
2. Setiap cell dari hasil Multi-Cell tidak dapat dihapus atau diedit sebagian. Dengan demikian kita dapat melakukan proteksi terhadap formula yang diberikan (Lebih Aman).
3. Setiap menghitung nilai total maupun rata-rata tidak diperlukan field SubTotal (Intermediate Field/Efisiensi).