Tentang Array dan Penggunaan Array

 => Array Formula adalah formula yang melibatkan formula untuk data range atau multi cell yang pada kondisi normal hanya untuk cell-cell tunggal. Digunakan untuk  Menghitung jumlah karakter yang terdapat dalam satu rentang sel dan Menjumlahkan hanya angka yang memenuhi syarat tertentu.

=>Cara Menggunakan array formula dengan CSE Formula (Control+ Shift + Enter Formula)  karena mengharuskan penggunaan kombinasi tiga tombol pada keyboard.

=>Contoh Pembuatan Pengguna array formula.

a. Buatlah data Excel dan buat satu workbook baru.

b. Dengan data tersebut, kita akan mengisi cell-cell pada kolom SubTotal – berdasarkan perkalian dari kolom Jumlah dan Harga.

c. Buatlah selection pada range alamat E2:E5 yang berada di bawah kolom SubTotal.

d. Dengan posisi tersebut di atas, ketik formula =C2:C5*D2:D5, jangan tekan tombol apapun setelah Anda melengkapi formula.

e. eksekusi formula tersebut sebagai array formula dengan menekan tombol keyboard CONTROL + SHIFT + ENTER (CSE).

f. Perhatikan bahwa formula yang kita masukkan tadi telah diapit oleh pasangan kurung kurawal { … } di rumus excel. Ini menandakan bahwa range cell tersebut telah dianggap sebagai array formula .

g. Sekarang cobalah pilih salah satu cell dari E2:E5 dan coba hapus dengan menekan tombol Delete. Akan terjadi error dengan pesan seperti berikut – You cannot change part of an array.

h. Ini artinya range dari Array Formula adalah satu kesatuan dan tidak dapat dihapus secara individual.

i. Selesai.

=> Tentang tombol eksekusi untuk Array Formula.

               Rumus array sering disebut rumus CSE (Ctrl+Shift+Enter) karena sebagai ganti menekan Enter,

 Anda menekan Ctrl+Shift+Enter untuk menyelesaikannya.Tombol eksekusi untuk membuat rumus array dalam satu rentang sel dan

 menggunakan rumus array itu untuk menghitung satu kolom atau baris subtotal. 

Anda juga dapat menempatkan rumus array di sel tunggal lalu menghitung satu jumlah.

 Rumus array yang mencakup beberapa sel disebut rumus multisel, dan rumus array dalam satu sel disebut rumus sel tunggal.

=> Tentang Single-Cell dan Multi-Cell Formula.

- Terlihat bahwa hasil atau output eksekusi formula berupa array atau terdiri dari beberapa cell - sebagai satu kesatuan. Untuk hasil demikian, kita namakan formula tersebut Multi-Cell Formula.


Namun array formula tidak harus selalu menghasilkan multi-cell tetapi juga dapat menghasilkan satu cell saja, dan dengan demikian disebut Single-Cell Formula.


Kedua jenis output tersebut tetap memiliki karakteristik yang sama, yaitu pada bagian formulanya terdapat array yang dijadikan sebagai parameter atau

 input bagi operator ataupun fungsi yang mengolahnya.

=>  contoh pemakaian Single-Cell dan Multi-Cell Formula.

A.Multi-cell

1. Salin seluruh tabel  dan tempelkan ke sel A1 di lembar kerja kosong di Excel.

2. Untuk melihat Penjualan Total dari coupe dan sedan untuk tiap tenaga penjualan, pilih E2:E11, masukkan rumus =C2:C11*D2:D11, lalu tekan Ctrl+Shift+Enter.
3.Untuk Jumlah Total semua penjualan, pilih sel F11, masukkan rumus =SUM(C2:C11*D2:D11), dan tekan Ctrl+Shift+Enter.
Anda dapat mengunduh buku kerja ini dengan mengklik tombol Excel hijau pada bilah hitam di bagian bawah buku kerja. Kemudian Anda dapat membuka file itu di Excel, memilih sel yang berisi rumus array, lalu tekan Ctrl+Shift+Enter untuk menjalankan rumus tersebut.
Jika Anda bekerja di Excel, pastikan bahwa Sheet1 aktif, lalu pilih sel E2:E11. Tekan F2 lalu ketik rumus =C2:C11*D2:D11 di sel yang aktif, E2. Jika menekan Enter, Anda akan melihat bahwa rumus hanya dimasukkan di sel E2, dan menampilkan 165000. Setelah mengetikkan rumus, sebagai ganti Enter, tekan Ctrl+Shift+Enter. Sekarang Anda akan melihat hasilnya di sel E2:E11. Perhatikan bahwa pada bilah rumus, rumus akan muncul sebagai {=C2:C11*D2:D11}. Itu menunjukkan rumus array, sebagaimana diperlihatkan dalam tabel berikut ini.
Apabila Anda menekan Ctrl+Shift+Enter, Excel akan mengurung rumus dengan karakter kurung kurawal ({ }) dan menyisipkan rumus di setiap sel dalam rentang yang dipilih. Hal ini terjadi sangat cepat, sehingga yang Anda lihat di kolom E adalah jumlah total penjualan untuk setiap jenis mobil untuk setiap tenaga penjualan. Jika Anda memilih E2, lalu E3, E4, dan seterusnya, Anda akan melihat bahwa rumus yang sama diperlihatkan: {=C2:C11*D2:D11}.

B.Single-Cell

1. Di sel F10 buku kerja, ketikkan rumus berikut, lalu tekan Ctrl+Shift+Enter:
=SUM(C2:C11*D2:D11)
Dalam kasus ini, Excel mengalikan nilai dalam array (rentang sel C2 sampai D11) lalu menggunakan fungsi SUM untuk menjumlahkan semua total. Hasilnya adalah jumlah total penjualan $1.590.000,00. Contoh ini menunjukkan betapa canggihnya tipe rumus ini. Sebagai contoh, misalnya Anda memiliki 1.000 baris data. Anda dapat menjumlahkan sebagian atau semua data itu dengan membuat rumus array di sel tunggal sebagai ganti menyeret rumus menuruni 1.000 baris.
Perhatikan juga bahwa rumus sel tunggal di sel G11 terpisah sama sekali dari rumus multisel (rumus di sel E2 sampai E11). Ini adalah keunggulan lain menggunakan rumus array — fleksibilitas. Anda dapat mengubah rumus di kolom E atau menghapus semua kolom itu, tanpa mempengaruhi rumus di G11.
Rumus array juga menawarkan keunggulan berikut:

·         Konsistensi    Jika Anda mengklik salah satu sel dari E2 ke bawah, Anda melihat rumus yang sama. Konsistensi ini dapat membantu memastikan akurasi yang lebih tinggi.

·         Keselamatan    Anda tidak bisa menimpa komponen rumus array multi sel. Sebagai contoh, klik sel E3 dan tekan Delete. Anda harus memilih seluruh rentang sel (E2 sampai E11) dan mengubah rumus untuk seluruh array, atau membiarkan array seperti itu. Sebagai langkah pengaman tambahan, Anda harus menekan Ctrl+Shift+Enter untuk mengonfirmasi perubahan rumus.

·         Ukuran file lebih kecil    Anda bisa sering menggunakan rumus array tunggal daripada beberapa rumus menengah. Sebagai contoh, buku kerja ini menggunakan satu rumus array untuk menghitung hasil di kolom E. Jika menggunakan rumus standar (misalnya =C2*D2, C3*D3, C4*D4…), Anda akan harus menggunakan 11 rumus yang berbeda untuk mendapatkan hasil yang sama.

7.=> Pada bilah rumus, masukkan rumus berikut, lalu tekan Ctrl+Shift+Enter:

=SMALL(A5:A14,{1;2;3})

Nilai 400, 475, dan 500 akan ditampilkan berturut-turut dalam sel A16 sampai A18.

Rumus ini menggunakan konstanta array untuk mengevaluasi fungsi SMALL tiga kali dan mengembalikan anggota terkecil (1), kedua terkecil (2), dan ketiga terkecil (3) dalam array yang terdapat dalam sel A1:A10. Untuk mencari nilai lainnya, tambahkan argumen lain ke konstanta tersebut dan jumlah sel hasil yang ekuivalen ke rentang A12:A14. Anda juga dapat menggunakan fungsi tambahan dengan rumus ini, seperti SUM atau AVERAGE. Misalnya:

=SUM(SMALL(A 5 :A1 4 ,{1;2;3}))

=AVERAGE(SMALL(A 5 :A1 4 ,{1;2;3}))

Menemukan nilai terbesar n dalam satu rentang

Untuk mencari nilai terbesar dalam satu rentang, Anda dapat mengganti fungsi SMALL dengan fungsi LARGE. Selain itu, contoh berikut ini menggunakan fungsi ROW dan INDIRECT.

1. Pilih sel A1 hingga A3.
2. Pada bilah rumus, masukkan rumus berikut, lalu tekan Ctrl+Shift+Enter:

=LARGE(A5:A14,ROW(INDIRECT("1:3")))

Nilai 3200, 2700, dan 2000 akan muncul berturut-turut dalam sel A1 hingga A3.

Pada titik ini, mungkin membantu untuk mengetahui sedikit tentang fungsi ROW dan INDIRECT. Anda bisa menggunakan fungsi ROW untuk membuat array bilangan bulat berurutan. Sebagai contoh, pilih kolom kosong 10 sel di buku kerja latihan Anda, masukkan rumus array ini di sel A5:A14, lalu tekan Ctrl+Shift+Enter:

=ROW(1:10)

Rumus ini membuat kolom 10 bilangan bulat berurutan. Untuk melihat kemungkinan masalah, sisipkan satu baris di atas rentang yang memuat rumus array (yaitu, di atas baris 1). Excel menyesuaikan referensi baris, dan rumus mengembalikan bilangan bulat dari 2 sampai 11. Untuk mengatasinya, tambahkan fungsi INDIRECT pada rumus:

=ROW(INDIRECT("1:10"))

Fungsi INDIRECT menggunakan string teks sebagai argumennya (itulah sebabnya rentang 1:10 ditutup dengan tanda kutip ganda). Excel tidak menyesuaikan nilai teks apabila Anda menyisipkan baris atau memindahkan rumus array. Hasilnya, fungsi ROW akan selalu mengembalikan array bilangan bulat yang Anda inginkan.

Mari kita periksa rumus yang Anda gunakan sebelumnya — =LARGE(A5:A14,ROW(INDIRECT("1:3"))) — dimulai dari tanda kurung dalam ke arah luar: Fungsi INDIRECT mengembalikan satu unit nilai teks, dalam kasus ini nilai 1 sampai 3. Fungsi ROW kemudian mengembalikan array kolom tiga sel. Fungsi LARGE menggunakan nilai dalam rentang sel A5:A14, dan dievaluasi tiga kali, sekali untuk setiap referensi yang dikembalikan oleh fungsi ROW. Nilai 3200, 2700, dan 2000 dikembalikan ke array kolom tiga sel. Jika ingin mencari nilai lainnya, Anda dapat menambahkan rentang sel yang lebih besar ke fungsi INDIRECT.

Terakhir, Anda dapat menggunakan rumus ini dengan fungsi lain, misalnya SUM dan AVERAGE.

Menemukan string teks terpanjang dalam satu rentang sel

Rumus ini hanya bekerja apabila rentang data hanya berisi satu kolom sel. Di Sheet3, masukkan rumus berikut di sel A16, lalu tekan Ctrl+Shift+Enter:

=INDEX(A6:A9,MATCH(MAX(LEN(A6:A9)),LEN(A6:A9),0),1)

Teks "bunch of cells that" akan muncul di sel A16.

Mari kita periksa lebih dekat rumus ini, mulai dari elemen dalam dan ke arah luar. Fungsi LEN mengembalikan panjang setiap sel dalam rentang sel A6:A9. Fungsi MAX menghitung nilai terbesar di antara item tersebut, yang terkait dengan string teks terpanjang, yaitu di sel A7.

Di sini akan mulai terlihat sedikit kompleks. Fungsi MATCH menghitung offset (posisi relatif) sel yang berisi string teks terpanjang. Untuk melakukannya, diperlukan tiga argumen: nilai pencarian, array pencarian, dan tipe yang cocok. Fungsi MATCH mencari array pencarian untuk nilai pencarian yang ditetapkan. Dalam kasus ini, nilai pencarian adalah string teks terpanjang:

(MAX(LEN( A6 : A9 ))

dan bahwa string itu terdapat dalam array ini:

LEN( A6:A9 )

Argumen tipe yang cocok adalah 0. Tipe yang cocok dapat berupa nilai 1, 0, atau -1. Jika Anda menetapkan 1, MATCH mengembalikan nilai terbesar yang kurang dari atau sama dengan nilai pencarian. Jika Anda menetapkan 0, MATCH mengembalikan nilai pertama yang tepat sama dengan nilai pencarian. Jika Anda menetapkan -1, MATCH menemukan nilai terkecil yang lebih besar dari atau sama dengan nilai pencarian yang ditetapkan. Jika Anda menghilangkan tipe yang cocok, Excel mengasumsikan 1.

Terakhir, fungsi INDEX memperhitungkan argumen ini: array, serta nomor baris dan kolom dalam array tersebut. Rentang sel A6:A9 berisi array, fungsi MATCH mengembalikan alamat sel, dan argumen final (1) menetapkan bahwa nilai itu berasal dari kolom pertama dalam array.

Menerapkan rumus array tingkat lanjut ke pekerjaan

Bagian ini menyediakan contoh rumus array tingkat lanjut.

Menjumlahkan rentang yang berisi nilai kesalahan

Fungsi SUM di Excel tidak bekerja apabila Anda mencoba menjumlahkan rentang yang berisi nilai kesalahan, misalnya #N/A. Contoh ini menunjukkan cara menjumlahkan nilai dalam rentang bernama Data yang berisi kesalahan.

=SUM(IF(ISERROR(Data),"",Data))

Rumus ini membuat array baru yang berisi nilai aslinya dikurangi setiap nilai kesalahan. Dimulai dari fungsi dalam ke arah luar, fungsi ISERROR mencari rentang sel (Data) untuk kesalahan. Fungsi IF mengembalikan nilai tertentu jika kondisi yang Anda tetapkan dievaluasi TRUE dan nilai lain jika dievaluasi FALSE. Dalam kasus ini, fungsi akan mengembalikan string kosong ("") untuk semua nilai kesalahan karena dievaluasi ke TRUE, dan mengembalikan nilai yang tersisa dari rentang (Data) karena dievaluasi ke FALSE, yang berarti tidak berisi nilai kesalahan. Fungsi SUM kemudian menghitung jumlah total untuk array yang difilter.

Menghitung jumlah nilai kesalahan dalam satu rentang

Contoh ini serupa dengan rumus sebelumnya, tetapi mengembalikan jumlah nilai kesalahan dalam rentang yang bernama Data sebagai ganti memfilternya:

=SUM(IF(ISERROR(Data),1,0))

Rumus ini membuat array yang berisi nilai 1 untuk sel yang berisi kesalahan dan nilai 0 untuk sel yang tidak berisi kesalahan. Anda dapat menyederhanakan rumus dan mendapatkan hasil yang sama dengan menghapus argumen ketiga untuk fungsi IF, seperti ini:

=SUM(IF(ISERROR(Data),1))

Jika Anda tidak menetapkan argumen, fungsi IF mengembalikan FALSE jika sel tidak berisi nilai kesalahan. Anda dapat menyederhanakan lagi rumus tersebut:

=SUM(IF(ISERROR(Data)*1))

Versi ini bekerja karena TRUE*1=1 dan FALSE*1=0.

Menjumlahkan nilai berdasarkan kondisi

Anda mungkin perlu menjumlahkan nilai berdasarkan kondisi. Sebagai contoh, rumus array ini menjumlahkan hanya bilangan bulat positif dalam rentang yang bernama Sales:

=SUM(IF(Sales>0,Sales))

Fungsi IF membuat array nilai positif dan nilai salah. Fungsi SUM pada dasarnya mengabaikan nilai salah karena 0+0=0. Rentang sel yang Anda gunakan dalam rumus ini dapat terdiri dari jumlah baris dan kolom berapa pun.

Anda juga dapat menjumlahkan nilai yang memenuhi lebih dari satu kondisi. Misalnya, rumus array ini menghitung nilai yang lebih besar dari 0 dan kurang dari atau sama dengan 5:

=SUM((Sales>0)*(Sales<=5)*(Sales))

Perhatikan bahwa rumus ini mengembalikan kesalahan jika rentang berisi satu atau beberapa sel nonnumerik.

Anda juga dapat membuat rumus array yang menggunakan tipe atau kondisi OR. Misalnya, Anda dapat menjumlahkan nilai yang kurang dari 5 dan lebih besar dari 15:

=SUM(IF((Sales<5)+(Sales>15),Sales))

Fungsi IF menemukan semua nilai yang lebih kecil dari 5 dan lebih besar dari 15 lalu meneruskan nilai itu ke fungsi SUM.

Anda tidak dapat menggunakan fungsi AND dan OR langsung dalam rumus array karena fungsi itu memberi hasil tunggal, baik TRUE maupun FALSE, dan fungsi array memerlukan array hasil. Anda dapat mengatasinya dengan menggunakan logika yang ditampilkan dalam rumus sebelumnya. Dengan kata lain, Anda menjalankan operasi matematika, seperti penambahan atau perkalian, pada nilai yang memenuhi kondisi OR dan AND.

Menghitung rata-rata tanpa nol

Contoh ini menunjukkan cara mengeluarkan nol dari rentang apabila Anda perlu menghitung nilai rata-rata dalam rentang tersebut. Rumus ini menggunakan rentang data bernama Sales:

=AVERAGE(IF(Sales<>0,Sales))

Fungsi IF membuat array nilai yang tidak sama dengan 0 lalu meneruskan nilai itu ke fungsi AVERAGE.

Menghitung jumlah selisih antara dua rentang sel

Rumus array ini membandingkan nilai dalam dua rentang sel bernama MyData dan YourData dan mengembalikan jumlah perbedaan antara keduanya. Jika konten kedua rentang itu identik, rumus ini akan mengembalikan 0. Untuk menggunakan rumus ini, rentang sel harus berukuran sama dengan dimensi yang sama (misalnya, jika MyData adalah rentang 3 baris kali 5 kolom, YourData juga harus 3 baris kali 5 kolom):

=SUM(IF( MyData =YourData,0,1))

Rumus ini membuat array baru dengan ukuran yang sama sebagai rentang yang dibandingkan. Fungsi IF mengisi array dengan nilai 0 dan nilai 1 (0 bila tidak cocok dan 1 untuk sel yang identik). Fungsi SUM kemudian mengembalikan jumlah nilai dalam array tersebut.

Anda dapat menyederhanakan rumus seperti ini:

=SUM(1*( MyData <> YourData ))

Seperti rumus yang menghitung nilai kesalahan dalam satu rentang, rumus ini bekerja karena TRUE*1=1, dan FALSE*1=0.

Menemukan lokasi nilai maksimum dalam satu rentang

Rumus array ini mengembalikan nomor baris nilai maksimum dalam rentang kolom tunggal yang bernama Data:

=MIN(IF(Data=MAX(Data),ROW(Data),""))

Fungsi IF menciptakan array baru yang terkait dengan rentang yang bernama Data. Jika sel yang terkait berisi nilai maksimum dalam rentang, array akan berisi nomor baris. Jika tidak, array berisi string kosong (""). Fungsi MIN menggunakan array baru sebagai argumen kedua dan mengembalikan nilai terkecil, yang terkait dengan nomor baris dari nilai maksimum dalam Data. Jika rentang yang bernama Data berisi nilai maksimum yang identik, rumus akan mengembalikan baris nilai pertama.

Jika Anda ingin mengembalikan alamat sel sebenarnya dari nilai maksimum, gunakan rumus ini:

=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))

Next

Fungsi Average(…) Fungsi ini digunakan untuk mencari nilai rata-rata dari sekumpulan data(range). Bentuk umum penulisannya adalah ; =AVERAGE(number1,number1,…), dimana number1, number2, dan seterusnya adalah range data yang akan dicari nilai rata-ratanya. Untuk mengisi nilai rata-rata pada contoh diatas, maka rumusnya adalah =AVERAGE(E8:G8) kemudian tekan tombol enter. Hal yang sama juga bisa dilakukan untuk mengisi sel i8 INGAT posisi penunjuk sel harus berada pada sel i8 sebelum perintah tersebut dilaksanakan. 2. Fungsi Logika IF(…) Fungsi ini digunakan jika data yang dimasukkan mempunyai kondisi tertentu. Misalnya, jika nilai sel A1=1, maka hasilnya 2, jika tidak, maka akan bernilai 0. Biasanya fungsi ini dibantu oleh operator relasi (pembanding) seperti berikut:
Lambang Fungsi = Sama dengan < Lebih kecil dari > Lebih besar dari <= Lebih kecil atau sama dengan >= Lebih besar atau sama dengan <> Tidak sama dengan Bentuk umum penulisan fungsi ini adalah ; =IF(logical_test,value_if_true,value_if_false), artinya kalau ekspresi logika (logical_test) bernilai benar, maka perintah pada value_if_true akan dilaksanakan, jika salah, maka perintah pada value_if_false yang akan dilaksanakan. Lihat contoh berikut::
Kolom keterangan diisi dengan ketentuan, jika status sama dengan K, maka keterangannya KAWIN, jika tidak, maka keterangan berisi TIDAK KAWIN. Ini dapat diselesaikan dengan rumus =IF(C3=”K”, “KAWIN”,”TIDAK KAWIN”). Pada rumus diatas kita lihat bahwa jika datanya bertipe Teks/alpha numerik harus diapit oleh tanda kutip dua, lain halnya dengan tipe data numerik, tidak boleh menggunakan tanda kutip. Untuk kondisi IF bertingkat, coba perhatikan rumus untuk mengisi nilai huruf pada contoh pertama tadi. (kasus nilai mahasiswa) =IF(H8>=81,"A",IF(H8>=66,"B",IF(H8>=56,"C",IF(H8>40,"D","E")))), hal yang sama juga bisa dilakukan untuk mengisi kolom keterangan, dengan ketentuan, Jika nilai hurufnya sama dengan “A” maka keterangan “SANGAT MEMUASKAN, jika “B” maka “MEMUASKAN”, jika “C”, maka “CUKUP”, dan jika “D” maka bernilai “KURANG”, selain dari itu, maka bernilai “GAGAL”. Coba anda isi, OK !. 3. Fungsi Max(…) Fungsi ini digunakan untuk mencari nilai tertinggi dari sekumpulan data (range). Bentuk umum penulisannya adalah ; =MAX(number1,number1,…), dimana number1, number2, dan seterusnya adalah range data (numerik) yang akan dicari nilai tertingginya. Untuk mengisi sel H16 pada contoh diatas, maka rumusnya adalah =MAX(H8:H15) 4. Fungsi Min(…) Sama halnya dengan fungsi max, bedanya fungsi min digunakan untuk mencari nilai terendah dari sekumpulan data numerik. Kita dapat mengisi sel H17 dengan rumus =MIN(H8:H15). 5. Fungsi Count(…) Fungsi Count digunakan untuk menghitung jumlah data dari suatu range yang kita pilih. Pada contoh diatas, range yang kita pilih adalah (H8:H15). Maka dapat ditulis rumusnya untuk mengisi sel H19 dengan =COUNT(H8:H15). 6. Fungsi Sum(…) Fungsi SUM digunakan untuk menjumlahkan sekumpulan data pada suatu range. Bentuk umum penulisan fungsi ini adalah =SUM(number1,number2,…). Dimana number1, number2 dan seterusnya adalah range data yang akan dijumlahkan.

=> keuntungan dari penggunaan array  :

1. Konsistensi : Setiap cell dari hasil Multi-Cell memiliki formula yang sama.
2. Keamanan : Setiap cell dari hasil Multi-Cell tidak dapat dihapus atau diedit sebagian. Dengan demikian kita dapat melakukan proteksi terhadap formula yang diberikan.
3. Efisiensi ukuran file : Dari kedua contoh terakhir, kita dapat melihat bahwa untuk menghitung nilai total maupun rata-rata sebenarnya tidak diperlukan field SubTotal (intermediate field).

Dengan demikian tidak ada penyimpanan tambahan yang diperlukan, dan akan membuat ukuran file menjadi lebih kecil.