Cara Menggunakan Rumus Array di Excel

Artikel ini membahas bagaimana menggunakan rumus array di Excel.

Setelah paham apakah array di Excel itu dan menempatkannya ke dalam rumus, langkah berikutnya adalah menerapkan pengetahuan itu ke dalam pada permasalahan yang sesuai.

Fungsi-fungsi Rumus Array

Sulit menuliskan secara spesifik apa fungsi rumus array, karena banyak sekali kasus yang bisa diselesaikan dengan penggunaan rumus ini.

Tujuan penggunaan rumus array pada kasus-kasus di sini adalah untuk menunjukkan bagaimana rumus array digunakan sebagai alternatif rumus biasa.

Rumus array bisa digunakan untuk, tapi tidak terbatas pada:

• Membuat data sampel.
• Mencara data terbesar di Excel.
• Menjumlahkan, menghitung kuantitas, atau merata-ratakan data dengan kriteria tertentu.
• Menjumlahkan, menghitung kuantitas, atau merata-ratakan data pada baris-baris tertentu.

Cara Menggunakan Rumus Array di Excel untuk Membuat Kepala Kolom Nama Bulan

Kadang, kita menggunakan nama-nama bulan sebagai kepala kolom.

Dengan rumus array, hal paling sederhana yang bisa kita lakukan adalah menggunakan array constant dengan nama-nama bulan.

={"Jan", "Feb", "Mar", "Apr", "Mei", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Des"}

Selain itu, kita juga bisa menggunakan fungsi DATE yang dikombinasikan dengan beberapa fungsi lain untuk hasil yang sama.

=TEXT(DATE(YEAR(TODAY()); SEQUENCE(1; 12); 1); "mmm")

Penjelasan untuk rumus di atas adalah membuat tanggal (DATE) dengan mengambil tahun dari tanggal berjalan (YEAR dan TODAY) yang dikombinasikan dengan urutan (SEQUENCE) satu sampai dua belas, dan tanggal satu. Hasilnya kita konversi menjadi teks (TEXT) dengan format mmm.

Rumus seperti ini lebih fleksibel dan sesuai dengan penggunaan bahasa di komputer kita, serta menghilangkan resiko salah tulis nama bulan dan urutannya.

Hasil kedua rumus di atas bisa dilihat pada gambar di bawah.

Cara Transpose Array di Excel

Tidak semua data berorientasi kolom. Kadang, kita butuh orientasi baris.

Tapi, bikin dua array untuk setiap orientasi pastinya buang waktu dan tenaga.

Untungnya, Excel punya cara untuk mengubah orientasi data dengan fungsi TRANSPOSE.

Mengacu pada array yang sudah kita bahas, maka rumus cara transpose array di Excel adalah seperti di bawah ini.

=TRANSPOSE(TEXT(DATE(YEAR(TODAY()); SEQUENCE(1; 12); 1); "mmm"))

Atau

=TRANSPOSE({"Jan", "Feb", "Mar", "Apr", "Mei", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Des"})

Hasilnya bisa dilihat pada gambar di bawah ini.

Catatan: Fungsi TRANSPOSE berlaku dua arah. Bisa ganti orientasi dari kolom ke baris atau sebaliknya.

Cara Membuat Tabel Perkalian di Excel dengan Rumus Array

Salah satu favorit kami saat pertama kali mulai paham rumus array adalah membuat tabel perkalian.

Cara membuat tabel perkalian di Excel dengan rumus array bisa kita lihat di bawah ini.

=SEQUENCE(10) & " x " & SEQUENCE(1; 10) & " = " & SEQUENCE(1; 10) * SEQUENCE(10)

Seperti terlihat pada rumus di atas, kami menggunakan fungsi SEQUENCE untuk menampilkan komponen perkalian dan operator & untuk menggabungkan teks.

SEQUENCE pertama adalah untuk menciptakan bilangan pengali 1 sampai 10 ke bawah. SEQUENCE kedua akan menciptakan urutan angka yang dikali ke kolom-kolom di kanannya.

Hasilnya bisa teman-teman lihat pada gambar di bawah ini.

Mencari Data Terbesar di Excel dengan Rumus Array

Mengambil sampel data dari suatu range data adalah urusan biasa. Contohnya:

• Rata-rata atau jumlah dari X penjualan terbesar.
• Rata-rata X atau jumlah dari X penjualan terendah.

Kali ini kita akan mencari data terbesar di Excel dengan rumus array.

Perhatikan gambar di bawah.

Untuk mengambil penjualan terbesar berdasarkan tingkatan, gunakan fungsi LARGE dengan range data C3:C17.

=LARGE(C3:C17; 2)

Parameter kedua LARGE adalah peringkat data terbesar. Sebagai contoh, bila kita masukkan angka 2, maka LARGE akan mengembalikan data terbesar kedua.

LARGE hanya mengembalikan satu nilai berdasarkan peringkat data yang kita tentukan. Agar LARGE mengembalikan beberapa nilai sesuai dengan tingkatannya, gunakan array.

Sebagai contoh, untuk mengembalikan tiga penjualan terbesar, kita gunakan array {1, 2, 3}.

Dengan demikian, rumus menjadi:

=LARGE(C3:C17; {1, 2, 3})

Berhubung kita mau jumlahkan tiga penjualan terbesar, maka kita tambahkan SUM terhadap LARGE. Dengan demikian, rumus menjadi:

=SUM(LARGE(C3:C17; {1, 2, 3}))

Hasil dari rumus untuk mencari data terbesar di Excel bisa dilihat pada gambar di bawah ini.

Tips: Alih-alih langsung menggunakan rumus jadi, teman-teman bisa tes potongan-potongan rumus untuk melihat hasilnya. Tujuannya, kita bisa tau dari awal seandainya hasil rumus salah. Ingat, makin kompleks rumus, makin sulit cari kesalahan.

Mencari Jumlah Data di Baris-baris Tertentu

Perhatikan data di bawah ini.

Bagaimana bila kita hanya ingin memperoleh hanya total penjualan rumah saja?

Perhatikan baris-baris penjualan rumah. Semuanya baris ganjil. Dengan demikian, kita perlu memperoleh angka-angka baris ganjil.

Untuk memperoleh angka baris, gunakan fungi ROW. Berhubung data berada di C3:C12, maka rumusnya adalah:

=ROW(C3:C12)

Dengan fungsi ini, penjualan rumah oleh Catherine akan mengembalikan nilai 3. Perhatikan Gambar 7.

Baris ganjil adalah baris yang tidak habis dibagi dua, sementara genap habis.

Untuk mendapatkan nilai hasil bagi, gunakan fungsi MOD terhadap angka-angka baris.

=MOD(ROW(C3:C12); 2)

Rumus untuk menjumlahkan data penjualan adalah =SUM(C3:C12). Tapi, di antara range C3:C12 terdapat penjualan tanah juga. Kita harus menghilangkan penjualan tanah atau menjadikannya nol. Kita pilih cara kedua.

Untuk memeriksa apakah hasil rumus =MOD(ROW(C3:C12); 2) habis dibagi dua atau tidak, kita tambahkan operator <> untuk perbandingan.

=MOD(ROW(C3:C12); 2) <> 0

Hasil dari rumus =MOD(ROW(C3:C12); 2) <> 0 adalah TRUE or FALSE. Bila habis dibagi dua, maka FALSE. Bila tidak, maka TRUE.

Excel punya operator yang disebut double unary operator atau double dash atau tanda hubung ganda (–). Operator ini akan mengubah nilai TRUE menjadi 1 dan nilai FALSE menjadi 0.

=--(MOD(ROW(C3:C12); 2) <> 0)

Dari hasil =--(MOD(ROW(C3:C12); 2) <> 0), kita akan mengalikan nilai 0 (dari baris genap yang habis dibagi dua) ke penjualan tanah dan 1 (dari baris ganjil yang tidak habis dibagi dua) ke penjualan rumah. Dengan demikian, rumus final adalah:

=SUM(--(MOD(ROW(C3:C12); 2)<>0)*(C3:C12))

Hasilnya bisa dilihat pada gambar di bawah ini.

Mencari Jumlah Data di Baris-baris Tertentu dengan Fungsi IF

Rumus di atas terlihat keren. Bisa pakai operator double unary adalah tanda keren (perkara lainnya belakangan). Tapi, bagaimana bila kita tambah baris penjualan baru. Apartemen, misalkan.

Kondisinya akan jadi kompleks. Rumah bisa dibaris ganjil dan genap.

Kita perlu rumus baru yang bisa memisahkan penjualan rumah dari penjualan lainnya. Kita butuh fungsi IF.

Dengan IF, kita hanya perlu mengkondisikan baris rumah.

=IF(B3="Rumah"; C3; 0)

Kita ubah kondisi agar IF mengevaluasi array range, alih-alih satu cell saja.

=IF((B3:B17)="Rumah"; (C3:C17); 0)

Langkah akhir, kita perlu menjumlahkan hasil yang diperoleh IF.

=SUM(IF((B3:B17)="Rumah"; (C3:C17); 0))

Cara Menggunakan Rumus Array di Excel dengan Operator AND

Di atas, kita sudah belajar gimana menyaring hanya penjualan rumah baik dengan operator double unary dan fungsi IF.

Bagaimana bila kita mau tambahkan kriteria baru seperti penjualan rumah oleh Evelyne?

Kita bisa menggunakan operator AND. Operator AND dilambangkan dengan tanda asterisk (*).

Catatan: Mungkin teman-teman bingung. asterisk bukannya untuk perkalian? Kok untuk AND, sih? Yah, kami juga bingung awalnya. Tapi, memang, asterisk bisa digunakan sebagai perkalian, bisa juga sebagai operator AND.

Rumus kita mulai seperti di bawah ini.

=A3:A17="Eko" * B3:B17="Rumah"

Rumus ini dibaca bila nilai A3:A17 adalah Eko dan B3:B17 adalah Rumah.

Gambar di bawah ini adalah hasil uji rumus di atas.

Untuk menghindari hasil error seperti gambar di atas, sertakan tanda kurung bagi setiap evaluasi di rumus.

=(A3:A17="Eko") * (B3:B17="Rumah")

Berikutnya, kita mengaitkan array range penjualan ke rumus di atas. Sekali lagi, kita menggunakan operator *.

=(A3:A17="Eko") * (B3:B17="Rumah") * C3:C17

Karena kita bermaksud menjumlahkan semua penjualan rumah oleh Eko, maka tambahkan rumus SUM.

=SUM((A3:A17="Eko") * (B3:B17="Rumah") * C3:C17)

Perhatikan hasilnya pada gambar berikut.

Bila kita modikasi dengan rumus IF.

=SUM(IF((A3:A17="Eko") * (B3:B17="Rumah"); C3:C17))

Dan, bila kurang suka pakai operator AND karena bikin bingung, pakai fungsi AND.

=SUM(IF(AND(A3:A17="Eko"; B3:B17="Rumah"); C3:C17))

Pertanyaan: Teman-teman tau kenapa rumus pada Gambar 13 error dan perlu tanda kurung supaya benar?

Cara Menggunakan Rumus Array di Excel dengan Operator OR

Penggunaan operator AND dan OR pada dasarnya adalah sama. Perbedaannya hanya pada bagaimana mereka digunakan.

Kali ini kita akan menggunakan operator OR (+) untuk menghasilkan total penjualan rumah oleh Eko dan Evelyne.

Ada dua sales yang diinginkan, yaitu Eko atau Evelyne.

=(A3:A17="Eko") + (A3:A17="Evelyne")

Dengan memodifikasi rumus yang sudah ada, rumus akhir adalah seperti di bawah ini.

=SUM(((A3:A17="Eko") + (A3:A17="Evelyne")) * (B3:B17="Rumah") * C3:C17)

Hasilnya bisa dilihat seperti pada gambar di bawah ini.