Array Formulas. Kenalan yuuk !

---

Muatan :

• Sekilas tentang array formula, cara membuat, input dan output yang dibutuhkan, cara penulisan ke cell(s)
• Kondisi-kondisi umum yang membutuhkan array formula
• Contoh penggunaan array formula beserta step-by-step proses kalkulasi dalam sebuah array formula

---

Kilasan

Array formula adalah formula yang bekerja dengan input berupa array dan mengkalkulasi setiap item array. Array adalah kumpulan item. Array formula ditandai dengan adanya kurung kurawal yang melingkupi formula.
Contohnya {=Average(IF( A2:A5 = "Jakarta" , B2:B5 ))}

Cara membuatnya adalah dengan menulis formula seperti biasa dan diakhiri dengan menekan 3 (tiga) tombol bersamaan, yaitu tombol CTRL SHIFT ENTER (CSE) sebagai pengganti penekanan tombol ENTER pada umumnya penulisan formula.



Input berupa array yang dapat berbentuk :

• banyak baris x 1 kolom (array 1 dimensi vertikal)
• 1 baris x banyak kolom (array 1 dimensi horisontal)
• banyak baris x banyak kolom (array 2 dimensi)

Array formula tidak bisa bekerja pada array lebih dari 2 dimensi.

Output array formula dapat berupa :

• Satu nilai yang disebabkan oleh penggunaan fungsi agregat (seperti Sum,Count,dsb)
• Array

Jadi, pada dasarnya array formula selalu menghasilkan array. Hasil akhir array formula akan menjadi sebuah nilai (1 item) saja ketika dikenai fungsi agregat.

Peringatan !!!

Tidak semua fungsi Excel bisa menerima input berupa array.

Penulisan output pada cells Excel dapat dilakukan pada :

• Satu cell
  Bila output berupa array, maka yang ditulis adalah item pertama dari hasil atau hasil dari fungsi yang menghasilkan satu value yang diberi input berupa array.
• Blok array
  • Bila output berupa array, maka setiap item array hasil akan ditulis berurutan menurut dimensi array hasil. Ketika blok array lebih luas dibanding dimensi array output, maka error value #N/A! yang ditulis pada cell yang tidak beririsan dengan dimensi array output.
  • Pada output berupa array 1 dimensi vertikal maka cell pada baris diluar jumlah item array akan menghasilkan errorvalue #N/A!, dan ditulis pada kolom pertama. Kolom kedua dan seterusnya dari blok array akan berisi sama dengan kolom pertama./li>
  • Pada output berupa array 1 dimensi horisontal maka akan berkelakuan seperti pada 1 dimensi vertikal, hanya saja, orientasinya menjadi horisontal.

---

Bilamana array formula dibutuhkan ?

Array formula dibutuhkan ketika hasil output sebuah cell memerlukan komputasi terhadap banyak item sekaligus. Komputasi dapat berupa pemilihan atau pencarian data :

• berdasar banyak kriteria, misal berdasar customer tertentu dan bulan tertentu
• pada sebuah daftar hasil ekstraksi sebuah data, seperti produk dengan kode produksi diawali angka 1
• pada suatu daftar hasil penggabungan atau operasi matematis beberapa kolom data

Pada Excel 2007 ada beberapa fungsi baru yang ditambahkan, yang dapat mengurangi penggunaan array formula, seperti fungsi SumIFs, CountIFs, AverageIFs, dan lainnya. Beberapa kasus masih tidak memungkinkan untuk diselesaikan dengan fungsi-fungsi baru tersebut.

---

Contoh penggunaan array formula dan step-by-step kalkulasi

Seluruh formula di bawah ini adalah array formula, kecuali telah diterangkan bahwa bukan array formula, maka jangan lupa untuk mengakhiri penulisan formula dengan menekan 3 tombol, yaitu tombol CTRL SHIFT ENTER sebagai pengganti penekanan tombol ENTER.

• Data
  
  Dibawah ini adalah data yang akan digunakan sebagai contoh kasus.
  
  • Kode Invoice (inv_id)
    Tipe data kolom ini adalah numerik. Tiga digit terakhir adalah nomor order masing-masing customer. Kode customer adalah digit di depan nomor order. Contoh untuk inv_id 1001 berarti customer kode 1 dengan nomor order 1.
  • Kode Produk (product_id)
    Tipe data kolom ini adalah teks 10 karakter yang terdiri dari empat bagian, yaitu :
    • Kode lokasi produksi (tipe data teks - kapital; 2 karakter pertama)
    • Nomor seri produk (tipe data numerik; 3 digit mulai karakter ke-3)
    • Kode material (tipe data teks; 2 karakter mulai karakter ke-6)
    • Kode warna produk (tipe data teks - kapital; 3 karakter terakhir)
    Contoh kode produk GL105alGRN berarti diproduksi dilokasi GL dengan nomor seri produk 105 menggunakan material AL dan produk berwarna GRN.


• Berapa kali customer 1 mendapatkan discount ?
  (Jawab : 1 pada sebuah cell hasil)
  Solusinya adalah mengkalkulasi digit didepan nomor order yang bernilai 1 dan nilai di kolom disc bernilai lebih dari 0. Hasil dari array formula dituntut menghasilkan 1 nilai saja (bukan array).
  
  Pada Excel 2007, masalah ini bisa diselesaikan dengan fungsi CountIFs yang bukan array formula sebagai berikut.
  =COUNTIFS(A2:A9,">1000",A2:A9,"<2000",E2:E9,">0")
  Kode customer selalu di depan nomor order yang 3 digit, artinya, kode customer dapat disusun dalam angka ribuan. Untuk kode customer 1 adalah dengan inv_id dari 1001 sampai 1999 (1000 < inv_id < 2000).
  
  Untuk seluruh versi Excel, penyelesaian dengan konsep seperti di atas adalah berupa array formula yang memanfaatkan fungsi Count disertai fungsi IF sebagai pemilih data.
  =COUNT( IF( (A2:A9>1000) * (A2:A9<2000) * (E2:E9>0) , 1 ) )
  
  Pada pemanfaatan array formula, bagian yang di-bold bisa digantikan dengan konsep lain, seperti :
  =COUNT( IF( (INT(A2:A9/1000)=1) * (E2:E9>0) , 1 ) )
  Nomor order yang pasti 3 digit mengindikasikan bahwa hasil fungsi INT pada inv_id yang dibagi 1000 akan menghasilkan kode customer.
  
  Selain memanfaatkan fungsi Count, dapat pula menggunakan fungsi Sum.
  =SUM( (INT(A2:A9/1000)=1) * (E2:E9>0) )
  Bagian IF pada pemanfaatan fungsi Count dapat dihilangkan, sehingga yang tersisa adalah komputasi kondisi.
  
  Proses komputasi array formula di atas, dengan Count atau Sum, dapat dibedah tahap demi tahap dalam sebuah cell hasil seperti gambar berikut ini.
  
  Step 1 sampai step 3
  adalah proses komputasi kondisi pertama, yaitu untuk menentukan apakah kode customer pada data di kolom inv_id sesuai dengan kode customer kriteria yang bernilai 1. Setiap step menghasilkan sebuah array 1 dimensi vertikal.
  
  Step 4 adalah proses komputasi kondisi kedua, yaitu menentukan apakah nilai discount pada data kolom disc sesuai kriteria lebih dari 0 (mendapat discount).
  
  Hasil array setiap step akan dikomputasi pada step berikutnya. Hasil array dari proses komputasi kedua kondisi (step 3 dan 4) bernilai TRUE atau FALSE.
  
  Step 5 adalah proses komputasi seluruh kondisi, yaitu berupa perkalian (yang setara dengan kata hubung dan pada masalah yang dihadapi) antara kedua hasil komputasi kondisi. Hasil step 5 pasti hanyalah 1 yang berarti sesuai seluruh kriteria, atau 0 yang berarti tidak terpenuhinya kriteria (minimal 1 kriteria tidak terpenuhi). Tipe data hasil step 5 adalah numerik.
  
  >> Untuk array formula yang menggunakan fungsi Sum :
  Step 7 adalah proses berikutnya, yaitu berupa penjumlahan seluruh hasil komputasi step 5 dan step ini adalah langkah akhir komputasi array formula yang menggunakan fungsi Sum.
  
  >> Untuk array formula yang menggunakan fungsi Count :
  Hasil step 5 yang bertipe numerik, belum dapat memberikan hasil yang tepat karena fungsi Count adalah menghitung cacah data numerik. Hasil Count terhadap hasil step 5 akan menghasilkan nilai 8. Oleh sebab itu, diperlukan pengkonversian hasil step 5 agar nilai 1 tetap berupa data bertipe numerik, dan nilai 0 menjadi data yang bertipe bukan numerik. Oleh sebab itu diperlukan proses komputasi selanjutnya, yaitu step 6.
  
  Step 6 adalah proses pengkonversian hasil step 5 dengan memanfaatkan kemampuan fungsi IF yang berguna untuk memilih data berdasar suatu kondisi. Kondisi yang digunakan adalah jika hasil step 5 bernilai benar (TRUE atau bukan nol), maka hasil keluaran fungsi IF adalah nilai 1 yang bertipe numerik. Sedangkan jika salah (FALSE atau nol), maka hasil keluaran fungsi IF adalah nilai default fungsi IF untuk kondisi salah, yaitu bernilai FALSE yang bertipe boolean.
  
  Step 8 adalah proses komputasi Count (hitung cacah nilai bertipe numerik) hasil step 6. Proses ini adalah langkah terakhir komputasi array formula yang menggunakan fungsi Count.

Pendekatan lain adalah menggunakan fungsi LEFT, seperti array formula berikut.
=COUNT( IF( (--LEFT(A2:A9 , LEN(A2:A9) - 3) = 1) * (E2:E9>0) , 1 ) )
=SUM( (--LEFT(A2:A9 , LEN(A2:A9) - 3) = 1) * (E2:E9>0) )
Bagian LEN(A2:A9) - 3 akan menghasilkan jumlah karakter inv_id tanpa nomor order. Karena hasil dari fungsi LEFT berupa teks dan kode customer adalah numerik, maka diperlukan konversi teks hasil fungsi LEFT ke numerik (contoh di atas menggunakan karakter -- sebagai alat konversi).

Buat daftar 3 (tiga) harga jual (price) tertinggi untuk produk yang di produksi pada lokasi GL!

(Jawab : 136.7;133.15;132.75 pada tiga cell hasil)
Solusinya adalah mengumpulkan data price yang product_id nya menunjukkan lokasi GL, kemudian mengurutkannya secara menurun (descending) diakhiri dengan mengambil 3 (tiga) nilai pertamanya untuk diletakkan pada 3 (tiga) cells hasil.

Hasil bisa berupa :

• A. Tiga cells yang masing-masing berisi array formula yang menghasilkan 1 nilai
  Caranya :
  1. Pada cells hasil pertama, beri array formula berikut.
  =LARGE( (LEFT($B$2:$B$9 , 2) = "GL") * $D$2:$D$9 ,ROW(1:1) )
  2. Copy formula ke dua cells dibawahnya.
  
  Bagian ROW(1:1) adalah yang mengambil item array hasil komputasi kriteria lokasi dan nilai price pada nomor elemen array tertentu berdasar nilai hasil kalkulasi ROW(1:1). Jadi hasil array formula adalah sebuah nilai, bukan sebuah array, meskipun proses komputasinya sebagai array.
  
  Perhatian !!!
  Jika hasil akan ditampilkan horisontal (pada 3 cells sebaris), maka ganti bagian ROW(1:1) dengan COLUMN(A:A)


• B. Blok array berisi 3 cells ber-array formula yang menghasilkan array
  Caranya :
  1. Blok 3 (tiga) cells hasil sekolom.
  2. Tulis array formula berikut.
  =LARGE( (LEFT(B2:B9 , 2) = "GL") * D2:D9 , ROW(1:3) )
  
  Bagian ROW(1:3) menghasilkan array 3 item secara langsung, sehingga hasil array formula adalah sebuah array sekolom berisi 3 item.
  
  Perhatian !!!
  Jika cells hasil yang diblok adalah 3 cells horisontal, maka ganti bagian ROW(1:3) dengan COLUMN(A:C)

Dari kedua cara di atas, terdapat perbedaan pada peletakan tanda absolut reference (karakter $) dan pengaturan bagian penentu jumlah item hasil array formula (bagian dengan fungsi ROW). Ketika hasil array formula berupa array dan dituliskan pada sebuah blok array (tiga cells), maka seluruh referensi akan sama disemua cells dalam blok array, meskipun referensi tidak diberikan tanda absolut reference. Tanda absolut reference digunakan untuk menjaga ketetapan referensi ketika formula di-copy ke cell lain.

Proses komputasi di dalam array formula adalah seperti gambar berikut.

Step 1 dan step 2 adalah proses untuk mendapatkan data product_id yang diproduksi di lokasi GL.

Step 3 adalah proses pengambilan nilai kolom price untuk disertakan pada proses komputasi berikutnya.

Step 4 adalah proses pemilihan data yang sesuai kriteria lokasi produksi GL. Hasil step 4 menunjukkan bahwa nilai 0 adalah data yang tidak sesuai kriteria, dengan asumsi bahwa price selalu lebih dari 0.

Step 5 adalah proses pengurutan menurun hasil step 4 oleh fungsi LARGE. Pada langkah ini, bagian penentu nomor item yang diambil (yaitu oleh fungsi ROW) belum ikut dalam proses.

Step 6 adalah proses akhir dari hasil (A). Referensi dalam formula telah diberi tanda absolut reference agar tetap ketika formula di-copy ke cell lain. Pada proses akhir ini, bagian penentu item hasil step 5 yang diambil (fungsi ROW) juga telah dikalkulasi, dan fungsi ROW berisi referensi 1 baris. Hasil proses di langkah ini berupa 1 nilai bukan array.

Step 7 adalah proses akhir dari hasil (B). Hasil langkah ini berupa array 3 item akibat rujukan fungsi ROW terhadap 3 baris.

---

Closing :

Array formula merupakan alat sangat berguna dalam penyelesaian komputasi data menggunakan Excel. Meskipun berdaya guna tinggi, pemakaiannya harus diusahakan seefisien mungkin, karena karakteristiknya yang mengkalkulasi setiap item array dapat membuat performa kalkulasi Excel jadi menurun drastis. Untuk beberapa kasus, array formula dapat bekerja lebih cepat dibandingkan menggunakan formula panjang yang memanfaatkan kolom, baris, maupun cell bantu.

---

File(s) :

Array formula pada sheet Order_Detail

---