Manajemen Data / Ms. Excel

Mahir Fungsi Excel: GROUPBY

Microsoft Excel terus berkembang dengan fitur-fitur baru, khususnya untuk versi 365. Salah satu fitur unggulannya yaitu array dinamis yang memungkinkan hasil rumus mengisi rentang sel secara otomatis tanpa perlu diseret manual. Salah satu fungsi yang termasuk dalam array dinamis adalah GROUPBY. Fungsi GROUPBY memungkinkan kita untuk membuat ringkasan data menggunakan rumus saja. Fungsi ini mendukung pengelompokan (grouping) berdasarkan satu sumbu dan melakukan agregasi terhadap nilai-nilai yang terkait. Misalnya, jika kita memiliki tabel data penjualan, kita bisa membuat ringkasan penjualan berdasarkan tahun. Dengan menggunakan fungsi GROUPBY, kita dapat membuat agregasi secara cepat tanpa harus menggunakan kombinasi rumus yang rumit atau bahkan menggunakan tabel pivot.

Formula GROUPBY

Format sintaks fungsi GROUPBY adalah sebagai berikut:

GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])

Penjelasan parameter:

row_fields: Kolom atau kolom-kolom yang digunakan sebagai dasar pengelompokan
values: Kolom nilai yang akan diagregasi
function: Fungsi agregasi yang digunakan (misalnya SUM, AVERAGE, COUNT, dll.)
[field_headers]: (Opsional) Header nama field untuk hasil ringkasan. Terdapat beberapa pilihan jika diisi:
- 0: field tanpa header
- 1: field memiliki header (namun jangan ditampilkan)
- 2: field tanpa header (akan di-generate header default)
- 3: field memiliki header (tampilkan sebagai output)
[total_depth]: (Opsional) Level total/subtotal dalam ringkasan
- 0: tanpa tambahan baris Total
- 1: tambahkan kolom Grand Total
- 2: tambahkan kolom Total dan Grand Total (jika di-grouping dengan dua kolom atau lebih)
- -1: tambahkan kolom Grand Total (di baris bagian atas)
- -2: tambahkan kolom Total dan Grand Total (di baris bagian atas)
[sort_order]: (Opsional) Urutan sortir hasil
[filter_array]: (Opsional) Filter yang ingin diterapkan pada data
[field_relationship]: (Opsional) Hubungan antar field jika bekerja dengan beberapa tabel

Beberapa fungsi agregasi yang sering digunakan dalam GROUPBY:

SUM: Menjumlahkan nilai dalam kelompok tertentu
AVERAGE: Menghitung rata-rata dari setiap kelompok data
COUNT: Menghitung jumlah item dalam kelompok tertentu
MAX: Mengambil nilai tertinggi dari setiap kelompok
MIN: Mengambil nilai terendah dari setiap kelompok
STDEV: Menghitung standar deviasi dalam setiap kelompok, berguna untuk analisis statistik
UNIQUE: Menghitung nilai unik dari kelompok data
Dan lain-lain

Contoh Penggunaan `GROUPBY`

Bayangkan kita memiliki data seperti tabel berikut ini. Misalkan kita diminta untuk menghitung hal-hal berikut:

Berapa total pengeluaran untuk membayar gaji untuk setiap divisi?
Berapa Rata-rata besaran gaji per level jabatan?
Berapa jumlah karyawan di setiap divisi menurut level jabatan?

Pertanyaan-pertanyaan ini sangat mudah dijawab hanya dengan menggunakan fungsi GROUPBY saja.

Jumlah Gaji Menurut Divisi

Fungsi GROUPBY termasuk bagian fungsi dynamic array, sehingga kita hanya perlu menulis formulanya pada satu sel saja. Output fungsi ini akan secara otomatis akan mengisi sel-sel yang diperlukan. Jadi, pada contoh di bawah ini formula hanya dituliskan pada satu sel saja, misalkan di H2 sel berwarna kuning).

Penjelasan dari formula adalah sebagai berikut:

row_fields yaitu C2:C17 (range kolom sebagai dasar pengelompokan)
values yaitu E2:E17 (range kolom yang akan diagregasi, misalnya: jumlah, rata-rata dsb)
function yaitu SUM (fungsi Excel untuk penjumlahan)
[field_headers] yaitu 3 (range field yang kita berikan termasuk header, dan akan kita gunakan juga sebagai header output)
[total_depth] yaitu 1 (tambahkan Grand Total – Default)
[sort_order] yaitu 1 (Kolom Divisi diurutkan ascending)

Microsoft Excel

GROUPBY(C2:C17, E2:E17, SUM, 3, 1, 1)

Rata-Rata Gaji Menurut Jabatan

Untuk menghitung rata-rata gaji per jabatan kita dapat menggunakan GROUPBY dengan function AVERAGE. Agar contoh bervariasi, kali ini kita akan menampilkan output tanpa header. Header akan kita buat secara manual saja, misal dengan nama Jabatan dan Rata-Rata Gaji.

Berikut penjelasan dari formula yang digunakan:

row_fields yaitu D2:D17 (range kolom sebagai dasar pengelompokan)
values yaitu E2:E17 (range kolom yang akan diagregasi)
function yaitu AVERAGE (fungsi Excel untuk menghitung rata-rata)
[field_headers] yaitu 1 (range field yang diberikan termasuk header, namun tidak ingin menampilkan header tersebut)
[total_depth] yaitu 0 (Tidak perlu menambahkan baris Total)

Microsoft Excel

=GROUPBY(D2:D17, E2:E17, AVERAGE, 1, 0)

Jumlah Karyawan Menurut Divisi dan Jabatan

Pengelompokan tidak hanya bisa dilakukan untuk satu kolom saja. Kita bisa mengelompokkan berdasarkan dua atau lebih, misalnya untuk mengetahui jumlah karyawan untuk setiap jabatan di setiap divisi.

Berikut penjelasan formula yang digunakan:

row_fields yaitu C2:D17 (range kolom sebagai dasar pengelompokan, kolom C dan D)
values yaitu E2:E17 (range kolom yang akan diagregasi)
function yaitu COUNT (fungsi Excel untuk menghitung jumlah data)
[field_headers] yaitu 1 (range field yang diberikan termasuk header, namun tidak ingin menampilkan header tersebut)
[total_depth] yaitu 2 (Menambahkan baris Total dan Grand Total)