Halo guys bertemu lagi dengan RumusHitung.com. Masih dengan materi rumus excel ya… Sebelumnya kita sudah membahas rumus fungsi excel berupa logika, date and time, lookup and reference, dan text. Selanjutnya rumushitung akan membahas rumus fungsi excel yang ke-5, yaitu Rumus Fungsi Dynamic Array. Langsung saja kita simak penjelasannya.
Contents
Rumus Fungsi Dynamic Array
Ada 10 fungsi Excel dynamic array yang akan kita pelajari, antara lain : fungsi FILTER, LET, RANDARRAY, SEQUENCE, SINGLE, SORT, SORTBY, UNIQUE, XLOOKUP, dan XMATCH.
1. FILTER
Fungsi Excel FILTER memfilter rentang data berdasarkan kriteria yang disediakan, dan akan mengekstrak rekaman yang cocok.
Penulisan rumus : =FILTER(array;include;[if_empty])
Keterangan :
- array – rentang atau larik untuk difilter.
- include – larik Boolean, disediakan sebagai kriteria.
- if_empty – nilai yang akan diganti saat tidak ada hasil yang dikembalikan.
Fungsi FILTER memfilter berbagai data berdasarkan kriteria yang diberikan. Hasilnya adalah larik nilai yang cocok dengan rentang aslinya. Jika larik ini adalah hasil akhir (yaitu hasil tidak diserahkan ke fungsi lain) hasil yang cocok akan “tumpah” ke lembar kerja. Dalam bahasa yang sederhana, fungsi FILTER memungkinkan dengan mudah mengekstrak rekaman yang cocok dari kumpulan data yang lebih besar berdasarkan kriteria yang diberikan.
Hasil dari FILTER bersifat dinamis. Saat nilai dalam data sumber berubah, atau larik data sumber diubah ukurannya, maka hasil dari FILTER akan diperbarui secara otomatis.
Contoh :
Misal, memfilter pada tabel yang sudah ada datanya, seperti contoh gambar tabel Excel di atas. Dengan penulisan rumusnya :
=FILTER(B6:D19;D6:D19=H2;”No result”)
Karena nilai dalam sel H2 adalah “Merah“, maka fungsi FILTER mengekstrak (memfilter) data dari larik dimana kolom grup berisi “merah”. Semua rekaman yang cocok diganti ke lembar kerja yang dimulai dari sel F6 tempat rumus berada.
Nilai juga dapat di hardcode (dimasukkan langsung ke dalam rumus). Rumus dibawah mempunyai hasil yang sama seperti di atas dengan “Merah” di hardcode atau dimasukkan ke dalam rumus. Dengan penulisan rumusnya :
=FILTER(B6:D19;D6:D19=”Merah”;”No result”)
Jika tidak menghasilkan apapun saat tidak ada data yang cocok itu ditemukan, ganti “No result” menjadi string kosong (“”) pada if_empty, maka penulisan rumus menjadi :
=FILTER(B6:D19;D6:D19=”Merah”;””)
2. LET
Fungsi Excel LET mempermudah dalam menulis rumus kompleks tertentu, dengan memungkinkan untuk mendeklarasikan dan menetapkan nilai ke variabel di dalam rumus.
Penulisan rumus : =LET(name1;value1;[name2/value2];….;calculation)
Keterangan :
- name1 – nama depan untuk ditetapkan. Harus dimualai dengan sebuah surat.
- value1 – nilai atau kalkulasi untuk diberikan ke name1.
- name2/value2 – [opsional] nama dan nilai kedua. Dimasukkan sebagai sepasang argumen.
- calculation – perhitungan menggunakan nama dan nilai yang ditetapkan.
Fungsi LET dimaksudkan untuk mempermudah penulisan rumus yang lebih kompleks, dengan memungkinkan untuk mendeklarasikan dan menetapkan nilai ke variabel di dalam rumus. Setelah variabel diberi nama, maka variabel dapat diberi nilai statis, atau nilai berdasarkan perhitungan. ini memungkinkan rumus untuk merujuk ke variabel dengan nama sebanyak yang diperlukan, sementara nilai variabel ditetapkan di satu tempat saja.
Variabel diberi nama dan nilai yang berpasangan (nama1/nilai1, nama2/nilai2, dan seterusnya). Fungsi LET dapat menangani hingga 126 pasangan nama/nilai, tetapi hanya pasangan nama/nilai yang dibutuhkan. Perhitungan yang digunakan untuk mengganti hasil akhir dari LET selalu muncul sebagai argumen terakhir untuk fungsi tersebut.
Contoh :
Di bawah ini adalah bentuk umum rumus fungsi LET dengan satu variabel :
Pada rumus di bawah, x adalah variabel (name1) dengan mengganti angka 5 (value1), maka x adalah 5. Jadi, pada bagian argumen name2/value2 merupakan x + 2, maka menjadi 5 + 2, akan menghasilkan 7. Dengan penulisan rumus :
=LET(x;5;x+2) / / menghasilkan 7
Bentuk rumus fungsi LET dengan dua variabel :
Contoh di bawah menunjukkan x dan y adalah variabel (name1) dengan mengganti 15 dan 10 (value1), dan argumen name2/value2 merupakan x – y, maka menjadi 15 – 10, akan menghasilkan 5. Dengan penulisan rumus seperti :
=LET(x;15;y;10;x-y) / / menghasilkan 5
Ada contoh lagi berupa gabungan fungsi LET dengan fungsi lain, seperti fungsi SQUENCE, FILTER, dan WEEKDAY), bisa dilihat pada contoh gambar di atas. Dengan penulisan rumus :
=LET(dates;SEQUENCE(C4-C3+1;1;C3;1);FILTER(dates;WEEKDAY(dates;2)<6))
3. RANDARRAY
Fungsi Excel RANDARRAY diantaranya menghasilkan larik angka acak. Ukuran atau larik ditentukan oleh argumen baris dan kolom. Nilai yang dihasilkan dapat berupa desimal atau bilangan bulat.
Penulisan rumus : =RANDARRAY([rows];[columns];[min];[max];[integer])
Keterangan :
- rows – [opsional] jumlah baris untuk diganti. Defaultnya 1.
- columns – [opsional] jumlah kolom yang akan diganti. Defaultnya 1.
- min – [opsional] nilai minimum untuk diganti. Defaultnya 0.
- max – [opsional] nilai maksimum untuk diganti. Defaultnya 1.
- integer – [opsional] menampilkan bilangan bulat, Boolean, TRUE, atau FALSE. Defaultnya FALSE.
Fungsi RANDARRAY adalah fungsi dynamic array yang mengganti array angka acak antara 0 dan 1. Ukuran atau array ditentukan oleh argumen baris (rows) dan kolom (columns) yang disediakan. Nilai yang dihasilkan oleh RANDARRAY bisa berupa nilai desimal atau bilangan bulat. Saat RANDARRAY mengganti beberapa hasil di lembar kerja, hasil akan masuk ke sel yang berdekatan.
Dalam contoh yang ditampilkan, fungsi RANDARRAY digunakan untuk menghasilkan 32 nilai dalam rentang 8 baris kali 4 kolom. Rumusnya dimasukkan dalam sel B3, maka penulisannya adalah :
=RANDARRAY(8;4)
4. SEQUENCE
Fungsi Excel SEQUENCE menghasilkan daftar nomor urut dalam sebuah array. Array bisa satu dimensi, atau dua dimensi. Nilai awal dan langkah adalah argumen.
Penulisan rumus : =SEQUENCE(rows;[columns];[start];[step])
Keterangan :
- rows – jumlah baris untuk diganti.
- columns – [opsional] jumlah kolom yang akan diganti.
- start – [opsional] nilai awal (defaultnya 1).
- step – [opsional] kenaikan antara setiap nilai (default ke 1).
Contoh :
Dalam contoh yang ditunjukkan pada gambar di atas, rumus di sel B3 adalah :
=SEQUENCE(8;4;1;4)
Dengan konfigurasi ini, SEQUENCE mengganti larik angka berurutan, 8 baris (rows) kali 4 kolom (columns), dimulai dari 1 pada argumen (start) dan di tambah 4 (kelipatan 4) pada argumen (step). Maka akan menghasilkan 32 angka yang dimulai dari 1 dan berakhir pada 125, seperti contoh gambar yang ditampilkan di atas.
5. SINGLE
Klik link di bawah untuk melihat contoh gambarnya : https://exceljet.net/sites/default/files/styles/original_with_watermark/public/images/functions/main/exceljet_single_function.png?itok=mpm6rFJ3
Fungsi Excel SINGLE mengganti nilai tunggal menggunakan persimpangan implisit (terkandung didalamnya). Fungsi SINGLE awalnya diperkenalkan dengan Dynamic Array, tetapi kemudian ditinggalkan karena mendukung karakter @.
Penulisan rumus (syntax) : =SINGLE(value)
Keterangan :
- value – nilai untuk dievakuasi menggunakan persimpangan implisit.
Fungsi SINGLE terutama untuk kompatibilitas mundur dengan rumus persimpangan implisit di versi Excel yang lebih lama.
Jika argumen yang diberikan adalah rentang, SINGLE akan mengganti sel di persimpangan baris atau kolom sel rumus. Jika tidak ada persimpangan, atau lebih dari satu persimpangan, maka fungsi SINGLE menghasilkan kesalahan #VALUE!.
Dalam contoh yang diperlihatkan, rumus berikut di D6 dan mengganti semua nilai dalam rentang B4:B8 :
=B4:B8
Ketika fungsi single (tunggal) dibungkus di sekitar rentang yang sama, SINGLE akan menghasilkan 3, yang terkait dengan B6, yang berpotongan dengan rumus di sel D6. Dengan penulisan rumus :
=SINGLE(B4:B8)
6. SORT
Fungsi Excel SORT mengurutkan konten rentang atau larik. Nilai yang dapat diurutkan berdasarkan satu atau beberapa kolom. Fungsi SORT mengganti larik hasil yang dinamis.
Penulisan rumus (syntax) :
=SORT(array;[sort_index];[sort_order];[by_col])
Keterangan :
- array – rentang atau larik baris / kolom untuk diurutkan.
- sort_index – [opsional] indeks kolom yang akan digunakan untuk menyortir.
- sort_order – [opsional] 1 = menaik, -1 = menurun. Defaultnya adalah urutan naik.
- by_col – [opsional] TRUE = urutkan berdasarkan kolom, FALSE = urutkan berdasarkan baris. Defaultnya adalah FALSE.
Fungsi Excel SORT mengekstrak dan mengurutkan daftar nilai unik dari suatu rentang. Hasilnya adalah larik nilai dinamis. jika larik ini adalah hasil akhir (yaitu tidak dialihkan ke fungsi lain) nilai larik akan “tumpah” ke lembar kerja ke dalam rentang yang secara otomatis diperbarui saat nilai unik baru ditambahkan atau dihapus dari rentang sumber, atau saat nilai sumber berubah.
Secara default, fungsi SORT akan mengurutkan nilai dalam urutan naik menggunakan kolom pertama. Gunakan argumen opsional sort_index dan sort_order untuk mengontrol kolom mana yang akan diurutkan, dengan urutan naik atau turun.
Contoh :
Seperti contoh yang ditampilkan, data termasuk pada kolom B dan skor pada kolom C. Dengan rumus pada sel E3 seperti :
=SORT(B3:C16;2) / / menghasilkan nilai berurutan menaik
Untuk mengurutkan berdasarkan skor dalam urutan menurun, setel sort_order dengan -1, maka rumusnya seperti :
=SORT(B3:C16;2;-1) / / hasilnya berurutan menurun
7. SORTBY
Fungsi Excel SORTBY mengurutkan konten rentang atau larik berdasarkan nilai dari rentang atau larik lain. Rentang atau larik yang digunakan untuk mengurutkan tidak perlu muncul dalam hasil.
Penulisan rumus (syntax) :
=SORTBY(array;by_array;[sort_order];[array/order];…)
Keterangan :
- array – rentang atau larik untuk diurutkan.
- by_array – rentang atau larik untuk diurutkan (by).
- sort_order – [opsional] urutan sortir. 1 = naik (default), -1 = turun.
- array/order – [opsional] pasangan larik dan urutan tambahan.
Contoh :
Seperti pada contoh di atas seperti fungsi SORT, namun rumusnya berbeda, untuk array adalah B3:C16, by_array adalah C3:C16, dan opsional sort_order adalah -1. Kemudian dirumuskan ke dalam sel 3, maka penulisan rumus seperti :
=SORTBY(B3:C16;C3:C16;-1) / / hasilnya menurun
8. UNIQUE
Fungsi Excel UNIQUE mengganti daftar nilai unik dalam daftar atau rentang. Nilai bisa berupa teks, angka, tanggal, waktu, dll.
Penulisan rumus : =UNIQUE(array;[by_col];[exactly_once])
Keterangan :
- array – rentang atau larik untuk mengekstrak nilai unik.
- by_col – [opsional] bagaimana membandingkan dan mengekstrak. Menurut baris = FALSE (default), menurut kolom = TRUE.
- exactly_once – [opsional] TRUE = nilai yang muncul sekali, FALSE = semua nilai unik (default).
Contoh :
Seperti contoh di atas, untuk mengganti nilai unik dari dalam rentang sel B4:B13, gunakan rumus fungsi UNIQUE seperti :
=UNIQUE(B4:B13)
9. XLOOKUP
Fungsi Excel XLOOKUP yaitu pengganti yang modern dan fleksibel untuk fungsi yang lebih lama, seperti VLOOKUP, HLOOKUP, dan LOOKUP. Fungsi XLOOKUP mendukung pencocokan perkiraan dan tepat, karakter pengganti (* ?) untuk pencocokan parsial dan pencarian dalam rentang vertikal atau horizontal.
Penulisan rumus :
=XLOOKUP(lookup;lookup_array;return_array;[not_found];[match_mode];[search_mode])
Keterangan :
- lookup – nilai pencarian.
- lookup_array – larik atau rentang yang akan dicari.
- return_array – larik atau rentang untuk diganti atau dikembalikan.
- not_found – [opsional] nilai yang akan diganti jika tidak ditemukan kecocokan.
- match_mode – [opsional] 0 = pencocokan tepat (default), -1 = pencocokan tepat atau terkecil berikutnya, 1 = pencocokan tepat atau terbesar berikutnya, 2 = pencocokan karakter pengganti.
- search_mode – [opsional] 1 = pencarian dari pertama (default), -1 = pencarian dari terakhir, 2 = pencarian biner menaik, -2 = pencarian biner menurun.
Not found message (tidak ditemukan pesan)
Ketika XLOOKUP tidak menemukan kecocokan, XLOOKUP menghasilkan kesalahan #N/A, seperti fungsi kecocokan lainnya di Excel. Tidak seperti fungsi pencocokan lainnya, XLOOKUP mendukung argumen opsional yang disebut not_found yang dapat digunakan untuk menimpa kesalahan #N/A ketika kesalahan itu akan muncul. Nilai umum untuk not_found mungkin “Not found”, “No match”, “No result”, dll. Ketika memberikan nilai untuk argumen not_found, apit teks dengan tanda kutip ganda (“”).
Match type (jenis kecocokan)
Secara default, XLOOKUP akan melakukan pencocokan tepat. Perilaku pencocokan dikontrol oleh argumen opsional yang disebut match_mode, yang memiliki opsi berikut :
Search mode (mode pencarian)
Secara default, XLOOKUP akan mulai mencocokkan dari nilai data pertama. Perilaku pencarian dikendalikan oleh argumen opsional yang disebut search_mode, yang menyediakan opsi berikut :
Pencarian biner sangat cepat, tetapi data harus diurutkan sesuai keinginan. Jika data tidak diurutkan dengan benar, pencarian biner bisa memberikan hasil tidak valid yang terlihat normal.
Contoh #1 – Dasar pencocokan tepat
Secara default, XLOOKUP melakukan pencocokan tepat. Dalam contoh di bawah, XLOOKUP akan digunakan untuk mengambil “peringkat” berdasarkan pencocokan tepat pada “film”. Dengan rumus di sel H3 seperti :
=XLOOKUP(H2;B3:B6;D3:D6)
Contoh #2 – Dasar pencocokan perkiraan
Untuk mengaktifkan perkiraan kecocokan, berikan nilai untuk argumen match_mode. Dalam contoh di bawah, XLOOKUP digunakan untuk menghitung diskon berdasarkan kuantitas, yang membutuhkan kecocokan perkiraan. Rumus di sel F3 menyediakan -1 untuk match_mode untuk mengaktifkan perkiraan kecocokan dengan perilaku “sama persis (tepat) atau terkecil berikutnya” :
=XLOOKUP(E3;B3:B6;C3:C6;;-1)
Contoh #3 – Banyak nilai (multi value)
XLOOKUP bisa mengganti lebih dari satu nilai pada waktu yang sama untuk pertandingan yang sama. Contoh di bawah memperlihatkan bagaimana XLOOKUP bisa dikonfigurasi untuk mengganti tiga nilai yang cocok dengan satu rumus. Dengan rumus di sel H4 seperti :
=XLOOKUP(G4;B4:B12;C4:E12)
Perhatikan kembali array (C4:E12) termasuk 3 kolom (pertama, terakhir, departemen). Ketiga nilai di hasilkan dan masuk ke kisaran H4:J4.
10. XMATCH
Fungsi Excel XMATCH melakukan pencarian dan mengganti posisi dalam rentang vertikal atau horizontal. Ini adalah penerus yang lebih kuat dan fleksibel untuk fungsi MATCH. XMATCH mendukung perkiraan dan pencocokan tepat, pencarian terbalik, dan karakter pengganti (* ?) untuk kecocokan parsial.
Penulisan rumus :
=XMATCH(lookup_value;lookup_array;[match_mode];[search_mode])
Keterangan :
- lookup_value – nilai pencarian.
- lookup_array – larik atau rentang yang akan dicari.
- match_mode – [opsional] 0 = pencocokan tepat (default), -1 = pencocokan tepat atau terkecil berikutnya, 1 = pencocokan tepat atau lebih besar berikutnya, 2 = pencocokan karakter pengganti.
- search_mode – [opsional] 1 = pencarian dari pertama (default), -1 = pencarian dari terakhir, 2 = pencarian biner menaik, -2 = pencarian biner menurun.
Match type (jenis pencocokan)
Argumen ketiga untuk XMATCH adalah match_mode. Ini adalah argumen opsional yang mengontrol perilaku kecocokan sebagai berikut :
Search mode (mode pencarian)
Argumen ke-4 XMATCH adalah search_mode. Argumen ini adalah opsional yang mengontrol perilaku pencarian sebagai berikut :
Contoh #1 – Dasar pencocokan
Pada contoh gambar yang ditampilkan paling atas, XMATCH digunakan untuk mengambil posisi “Yupiter” pada daftar planet dalam rentang sel B4:B11. Dengan rumus di sel G5 seperti :
=XMATCH(G4;B4:B11) / / menghasilkan 5
Perhatikan XMATCH secara default sama persis. Jika “Yupiter” di G4 salah ejaan, maka XMATCH akan menghasilkan #N/A.
Contoh #2 – INDEX dan XMATCH
XMATCH bisa digunakan seperti MATCH dengan INDEX. Untuk mendapatkan kembali diameter Yupiter berdasarkan contoh paling atas, maka rumusnya adalah :
=INDEX(C4:C11;XMATCH(G4;B4:B11)) / / hasilnya 142,984
Demikian pembahasan mengenai rumus fungsi excel dynamic array kita akhiri sampai disini. Jangan lupa dipelajari dan dipahami supaya dapat menambah ilmu pengetahuan serta wawasan yang bermanfaat bagi kita semua. Sekian terima kasih.
Artikel lainnya: Cara Menentukan KPK dan FPB Menggunakan Ms Excel Cara Mencari Sin Cos Tan dengan Excel