Formula Array pada MS Excel

Formula Array pada MS Excel

Pada Microsoft Excel, array formula adalah formula yang dapat melaksanakan beberapa perhitungan sekaligus untuk menghasilkan satu atau beberapa hasil. Array formula dapat melakukan perhitungan pada satu atau sekelompok nilai yang disebut array argument. Masing-masing array argument harus mempunyai jumlah baris dan kolom yang sama. Penulisan array formula sama seperti penulisan formula yang biasa hanya saja setelah penulisan tidak diakhiri dengan ENTER melainkan CTRL+SHIFT+ENTER. Array formula yang dimasukkan akan tampak diapit tanda kurung kurawal {..}.

Di bawah ini akan diberikan contoh untuk memperjelas bagaimana menggunakan array formula.

Misalkan saja, kita mempunyai tabel daftar pegawai disertai keterangan departemen dan besar gaji yang diterima masing-masing seperti pada gambar di atas. Di bawahnya, misalkan perhitungan-perhitungan yang akan dilaksanakan terhadap tabel tersebut.

Perhitungan pertama, misalnya mencari besarnya gaji yang diterima pegawai bernama "Anton" yang berada pada departemen "B". Jika saja terdapat satu kolom lagi misalnya NIP yang berbeda antara pegawai yang satu dengan yang lain dan terletak dikolom paling kiri, akan lebih mudah dengan menggunakan fungsi VLOOKUP(). Tetapi pada contoh di atas, kolom tersebut tidak ada, sedang nama pegawai yang bernama Anton ada dua namun berbeda departemen. Anggap saja pada setiap departemen nama pegawaianya berbeda, maka kita dapat menggunakan array formula untuk menghitungnya. Pilih cell yang akan menampung hasil perhitungan, dan masukkan formula berikut:

=SUM((A2:A11="Anto")*(B2:B11="B")*(C2:C11))

Setelah menuliskan formula tersebut akhiri dengan CTRL+SHIFT+ENTER, sehingga saat kita seleksi lagi cell tersebut, formula tersebut akan tampak diapit tanda kurung kurwal.

{=SUM((A2:A11="Anto")*(B2:B11="B")*(C2:C11))}

Perhitungan tersebut menghasilkan nilai 500.000 yang dilakukan dengan mencari kolom A2:A11 yang bernilai "Anto" dan kolom B2:B11 yang bernilai "B" dan hasilnya mengambil dari kolom C2:C11. Seandainya terdapat dua pegawai bernama Anton pada departemen B maka hasil adalah penjumlahan gaji dua "Anton" tersebut.

Jika kita mencari jumlah pegawai yang mempunyai gaji di atas 800.000, kita akan dapat mencarinya dengan menggunakan fungsi COUNTIF.

=COUNTIF(C2:C11;">800000")

Tetapi bagaimana kalau kriterianya ditambah lagi, misalnya jumlah pegawai yang mempunyai gaji di atas 800.000 tetapi dibawah 1.700.000? Kita dapat menggunakan array formula berikut:

=SUM((C2:C11>800000)*(C2:C11<1700000)*1)

akhiri dengan CTRL+SHIFT+ENTER sehingga tampak menjadi

{=SUM((C2:C11>800000)*(C2:C11<1700000)*1)}

Kalau mencari jumlah gaji yang diterima oleh pegawai yang mempunyai gaji di atas 800.000 tetapi dibawah 1.700.000, kita masukkan array formula

{=SUM((C2:C11>800000)*(C2:C11<1700000)*(C2:C11))}

Selanjutnya kita akan mencari jumlah pegawai yang ada pada departemen A dan B, array formula yang kita masukkan adalah

{=SUM((B2:B11="A")*1)+SUM((B2:B11="B")*1)}

dan jumlah gaji yang diterima oleh semua pegawai pada departemen A dan B dapat diperoleh dengan memasukkan array formula

{=SUM((B2:B11="A")*(C2:C11))+SUM((B2:B11="B")*(C2:C11))}

Formula untuk kriteria seperti C2:C11>800000 atau B2:B11="A" akan menghasilkan nilai boolean TRUE atau FALSE, dan dalam operasi tersebut untuk TRUE akan menghasilkan nilai 1 dan FALSE menghasilkan nilai 0.

Pada pencarian besar gaji di atas 800.000 tetapi dibawah 1.700.000, setiap kriteria dikalikan dengan kriteria yang lain selanjutnya dikalikan dengan kolom hasil yang akan dijumlah, karena kriteria tersebut bersifat kumulatif, harus memenuhi kedua kriteria yang ada. Sedang pada pencarian besar gaji pada departemen A dan B, setiap kriteria dikalikan dulu dengan kolom hasil yang akan dijumlah baru ditambahkan dengan perkalian kriteria lainnya dengan kolom hasil yang akan dijumlah, karena pada perhitungan ini kita cukup memenuhi satu kriteria saja yaitu departemen A atau departemen B.

Dan yang perlu diperhatikan selanjutnya, jika kita akan mengedit array formula yang telah dimasukkan, maka tanda kurung kurawal {..} akan hilang. Dan untuk menyudahi pengeditan tersebut maka penekanan CTRL+SHIFT+ENTER adalah keharusan.

Semoga benang merah dari apa yang saya tuliskan dapat diperoleh, sehingga kemudian dapat menerapkannya pada perhitungan yang lebih komplek yang sulit dilakukan dengan fungsi-fungsi yang kita kenal sebelumnya.

http://maseko.com/2005/09/19/array-formula-microsoft-excel