RumusHitung.com – Halo guys, bagaimana kabarnya? Semoga masih semangat yaa.. Kali ini rumushitung akan melanjutkan pembahasan mengenai rumus Excel. Pada pembahasan sebelumnya, sobat telah mempelajari tentang gabungan rumus Average pada Excel. Selanjutnya, kita akan bahas mengenai pengelompokan data (grouping) pada Excel.
Ada 10 rumus pengelompokan data (grouping) pada Excel, antara lain :
1) Kategorikan teks dengan kata kunci
2) Kelompokkan nilai teks
3) Kelompokkan nomor dengan interval yang tidak sama
4) Kelompokkan nomor dengan VLOOKUP
5) Kelompokkan waktu menjadi kelompok 3 jam
6) Kelompokkan waktu ke dalam kelompok yang tidak sama
7) Jika sel berisi salah satu dari banyak hal
8) Memetakan input ke nilai
9) Memetakan teks ke angka
10) Menghitung kelompok dengan ukuran n
Contents
- 1 Kategorikan Teks dengan Kata Kunci
- 2 Kelompokkan Nilai Teks Arbitrer (Semaunya)
- 3 Kelompokkan Nomor dengan Interval Yang Tidak Sama
- 4 Kelompokkan Nomor dengan VLOOKUP
- 5 Kelompokkan Waktu Menjadi Kelompok 3 Jam
- 6 Kelompokkan Waktu ke dalam Kelompok Yang Tidak Sama
- 7 Jika Sel Berisi Salah Satu dari Banyak Hal
- 8 Memetakan Input ke Nilai Abriter (Semaunya)
- 9 Memetakan Teks ke Angka
- 10 Menghitung Kelompok dengan Ukuran n
Kategorikan Teks dengan Kata Kunci
Untuk mengkategorikan teks menggunakan kata kunci dengan kecocokan “berisi”, gunakan fungsi SEARCH dengan bantuan dari INDEX dan MATCH. Pada contoh yang ditunjukkan di atas, rumus di sel C5 :
{=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0))}
Dimana keywords adalah rentang bernama E5: E14, dan categories adalah rentang bernama F5: F14.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Pada intinya, ini adalah fungsi INDEX dan MATCH.
Di dalam fungsi MATCH, fungsi SEARCH digunakan untuk mencari sel di kolom B untuk setiap kata kunci yang terdaftar dalam kata kunci rentang bernama (E5: E14) :
SEARCH(keywords,B5)
Karena mencari beberapa item (dalam kata kunci rentang bernama), akan memperoleh kembali beberapa hasil seperti :
{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!}
Kesalahan #VALUE! terjadi ketika SEARCH tidak bisa menemukan teks. Saat SEARCH menemukan kecocokan, ia menghasilkan angka yang sesuai dengan posisi teks di dalam sel.
Untuk mengubah hasil tersebut menjadi format yang lebih bisa digunakan, gunakan fungsi ISNUMBER, yang mengonversi semua nilai menjadi TRUE / FALSE seperti :
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
Larik ini masuk ke fungsi MATCH sebagai lookup_array, dengan lookup_value disetel TRUE. MATCH menghasilkan posisi TRUE pertama yang ditemukannya dalam larik (7 dalam kasus ini) yang diberikan ke fungsi INDEX sebagai row_num :
=INDEX(categories,7)
INDEX menghasilkan item ke-7 dalam kategori, “Auto”, sebagai hasil akhir.
Dengan fungsi XLOOKUP
Dengan fungsi XLOOKUP, rumus ini bisa disederhanakan. XLOOKUP bisa menggunakan logika yang sama yang digunakan di dalam fungsi MATCH di atas, jadi rumus yang setara seperti :
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP menempatkan TRUE pertama dalam larik, dan menghasilkan nilai yang sesuai dari kategori.
Rumus Dynamic Array hanya tersedia di Office 365.
Kelompokkan Nilai Teks Arbitrer (Semaunya)
Jika ingin mengelompokkan atau mengklasifikasikan data berdasarkan nilai teks arbitrer, bisa menggunakan VLOOKUP sebagai ganti rangkaian panjang pernyataan IF bertumpuk.
Triknya adalah dengan membuat tabel khusus yang akan memetakan nilai ke semua grup yang Anda butuhkan.
Dalam contoh yang ditunjukkan, rumus di F5 :
=VLOOKUP(E5,key,2,0)
Rumus ini menggunakan nilai dalam sel E5 untuk nilai pencarian, rentang bernama “key” (H5: I9) untuk tabel pencarian, 2 untuk menunjukkan “kolom ke-2”, dan 0 sebagai argumen terakhir menunjukkan kecocokan yang sama persis. Bisa juga menggunakan FALSE, bukan nol jika diinginkan.
VLOOKUP hanya mencari nilai dan menghasilkan nama grup dari kolom ke-2 di tabel. Kedua kolom dalam tabel dapat berisi nilai apa pun yang Anda perlukan, dan tabel tidak perlu diurutkan karena VLOOKUP menggunakan pencocokan tepat.
Seperti biasa, jika VLOOKUP tidak bisa menemukan kecocokan, VLOOKUP akan menghasilkan #N/A.
Kelompokkan Nomor dengan Interval Yang Tidak Sama
Untuk mengelompokkan angka ke dalam interval dengan ukuran yang tidak sama, Anda bisa menggunakan fungsi LOOKUP. Dalam contoh yang diperlihatkan, fungsi LOOKUP digunakan untuk mengelompokkan orang menurut usia ke dalam interval ukuran yang tidak sama. Rumus di sel D3 :
=LOOKUP(C3;F$3:F$6;G$3:G$6)
Dengan nilai hardcode
Jika ingin melakukan pengelompokan semacam ini tanpa tabel pada lembar kerja, bisa memasukkan nilai hardcode ke LOOKUP sebagai konstanta array seperti :
=LOOKUP(C3{0;10;36;50};{“<10″;”10-35″;”36-49″;”50+”})
Kelompokkan Nomor dengan VLOOKUP
Jika ingin mengelompokkan menurut angka, bisa menggunakan fungsi VLOOKUP dengan tabel pengelompokan kustom. Hal ini memungkinkan untuk membuat grup yang sepenuhnya khusus atau semaunya.
Pada contoh yang ditunjukkan, rumus di sel F7 :
=VLOOKUP(D5,age_table,2,TRUE)
Rumus ini menggunakan nilai dalam sel D5 untuk nilai pencarian, rentang bernama “age_table” (G5: H8) untuk tabel pencarian, 2 untuk menunjukkan “kolom ke-2”, dan TRUE sebagai argumen terakhir menunjukkan perkiraan kecocokan.
Catatan: argumen terakhir adalah opsional, dan default ke TRUE, tapi saya suka mengatur mode pencocokan secara eksplisit.
VLOOKUP hanya mencari usia dan menghasilkan nama grup dari kolom ke-2 di tabel. Kolom ini bisa berisi nilai apa pun yang diinginkan.
Tabel pivot
Tabel pivot bisa mengelompokkan nomor secara otomatis, tetapi pendekatan VLOOKUP memungkinkan ingin melakukan pengelompokan kustom sepenuhnya.
Kelompokkan Waktu Menjadi Kelompok 3 Jam
Untuk mengelompokkan waktu ke dalam keranjang (misal, Kelompokkan menurut 2 jam, kelompokkan menurut 3 jam, dll.), dapat menggunakan fungsi FLOOR. Pada contoh yang ditampilkan, rumus di sel E3 :
=FLOOR(D3;”3:00″)
Fungsi FLOOR tahu cara membaca waktu, jadi FLOOR mengartikan 3:00 sebagai ekuivalen desimalnya, 0,125. Kemudian sederhana membulatkan ke bawah setiap kali ke kelipatan terdekat 0,125. Gunakan pendekatan yang sama ini untuk mengelompokkan waktu ke dalam keranjang standar apa pun yang disukai.
Jika memiliki waktu dalam rentang satu hari atau lebih, gunakan fungsi MOD untuk mengekstrak waktu saja.
Tabel pivot
Secara otomatis, tabel pivot mengelompokkan waktu ke dalam kelompok 1 jam, tetapi tidak bisa secara otomatis mengelompokkan ke dalam kelompok waktu lain. Namun, dengan menggunakan pendekatan yang diuraikan di halaman ini, dapat mengelompokkan waktu sesukanya, lalu menjalankan data yang dihasilkan melalui tabel pivot untuk meringkas.
Kelompokkan Waktu ke dalam Kelompok Yang Tidak Sama
Untuk mengelompokkan waktu ke dalam keranjang yang tidak sama (misal, 12 AM-7 AM, 07 AM-12 PM, dll.), gunakan fungsi VLOOKUP. Pada contoh yang ditunjukkan, rumus n E5 :
=VLOOKUP(D5,bucket,2,1)
Jika Sel Berisi Salah Satu dari Banyak Hal
Untuk menguji sel untuk salah satu dari beberapa string, dan menghasilkan kustom untuk kecocokan pertama yang ditemukan, Anda bisa menggunakan rumus INDEX / MATCH berdasarkan fungsi SEARCH. Pada contoh yang ditunjukkan, rumus di sel C3 :
=INDEX(F$3:F$6;MATCH(TRUE;ISNUMBER(SEARCH(E$3:E$6;B3));0))
Memetakan Input ke Nilai Abriter (Semaunya)
Jika Aingin memetakan atau menerjemahkan input ke nilai arbitrer, gunakan fungsi VLOOKUP.
Pada contoh, perlu memetakan angka 1-6 sebagai berikut :
| Input | Output |
|---|---|
| 1 | 10 |
| 2 | 81 |
| 3 | 17 |
| 4 | 23 |
| 5 | 13 |
| 6 | 31 |
Karena tidak ada cara untuk mendapatkan keluaran (yaitu sewenang-wenang), kita perlu melakukan semacam pencarian. Fungsi VLOOKUP menyediakan cara mudah untuk melakukan ini.
Dalam contoh yang ditunjukkan, rumus di F5 adalah :
=VLOOKUP(F4;B3:C8;2;0)
Rumus ini menggunakan nilai dalam sel F4 untuk nilai pencarian, rentang B3:C8 untuk tabel pencarian, 2 untuk menunjukkan “kolom ke-2”, dan nol sebagai argumen terakhir untuk memaksa pencocokan tepat.
Meskipun dalam kasus ini memetakan input numerik ke output numerik, rumus yang sama akan menangani nilai teks untuk input dan output.
Alternatif menggunakan fungsi CHOOSE
Jika memiliki jumlah input yang terbatas, dan jika input berupa angka yang dimulai dengan 1, bisa juga menggunakan fungsi CHOOSE. Untuk contoh yang diperlihatkan rumus ekuivalen berdasarkan CHOOSE seperti :
=CHOOSE(F4;10;81;17;23;13;31)
Fungsi CHOOSE tidak berguna untuk data dalam jumlah besar tetapi untuk kumpulan data yang lebih kecil yang dipetakan ke indeks berbasis 1, ini memiliki keuntungan sebagai solusi “semua dalam satu”.
Memetakan Teks ke Angka
Untuk memetakan atau menerjemahkan input teks ke nilai numerik arbitrer, gunakan fungsi VLOOKUP dengan tabel sederhana.
Pada contoh, perlu memetakan lima nilai teks (status) ke kode status numerik sebagai berikut :
| Status | Kode |
|---|---|
| Dimulai | 10 |
| Normal | 20 |
| Terlambat | 30 |
| Lengkap | 40 |
| Dibatalkan | 50 |
Karena tidak ada cara untuk memperoleh output (yaitu semaunya), perlu melakukan semacam pencarian. Fungsi VLOOKUP menyediakan cara mudah untuk melakukan ini.
Dalam contoh yang ditampilkan, rumus di F5 adalah :
=VLOOKUP(F4;B3:C7;2;0)
Rumus ini menggunakan nilai dalam sel F4 untuk nilai pencarian, rentang B3:C7 untuk tabel pencarian, angka 2 untuk menunjukkan “kolom ke-2”, dan nol sebagai argumen terakhir untuk memaksa pencocokan tepat.
Meskipun dalam kasus ini memetakan nilai teks ke output numerik, rumus yang sama dapat menangani teks ke teks, atau angka ke teks.
Menghitung Kelompok dengan Ukuran n
Untuk membuat jumlah berjalan grup dengan ukuran variabel, Anda dapat menggunakan fungsi COUNTA dan CEILING. Dalam contoh yang ditampilkan, C3 berisi rumus ini :
=CEILING(COUNTA($B$3:B3)/F$2;1)
Inti dari rumus ini adalah fungsi COUNTA, yang dikonfigurasi dengan rentang yang meluas seperti ini :
COUNTA($B3:B3)
Saat rumus disalin ke bawah kolom, rentang yang dimulai dengan B3 meluas untuk menyertakan setiap baris baru, dan COUNTA menghasilkan hitungan berjalan dari semua entri yang tidak kosong dalam rentang tersebut.
Hasil COUNTA kemudian dibagi dengan “ukuran”, dikonfigurasi sebagai rentang bernama F2. Menggunakan sel di lembar kerja untuk ukuran kelompok memungkinkan pengelompokan diubah kapan saja tanpa mengedit rumus. Rentang bernama hanya digunakan untuk kemudahan membaca dan kenyamanan.
Nilai yang dihasilkan kemudian diolah dengan fungsi CEILING, dengan signifikansi 1. CEILING adalah fungsi pembulatan yang selalu membulatkan ke atas ke unit signifikansi berikutnya. Dalam contoh ini, menyebabkan nilai pecahan dibulatkan ke bilangan bulat berikutnya.
Menangani Sel Kosong
Jika rentang yang dihitung berisi sel kosong atau kosong, bisa membungkus rumus di dalam fungsi IF seperti :
=IF(B3<>” “;CEILING(COUNTA($B$3:B3)/F2;1);” “)
Di sini, akan menjalankan operasi penghitungan dan pembulatan yang dijelaskan di atas hanya jika sel di kolom B tidak kosong. Jika kosong, maka akan dilewatkan hitungan dan menghasilkan string kosong.
Demikian penjelasan mengenai pengelompokan data (grouping) pada Excel, semoga bermanfaat. Sekian terima kasih.