TL;DR
Dari 500-an fungsi Excel, kerjaan analis harian cuma butuh sekitar 40 yang kebagi tujuh kelompok: agregasi, lookup, logika, teks, tanggal, statistik, dan array dinamis. Enam yang paling sering dipanggil adalah SUMIFS, COUNTIFS, XLOOKUP, IF, IFERROR, dan TRIM. Urutan belajar yang paling cepat kepake: agregasi dan logika dulu, baru lookup, array dinamis terakhir.
Dari 500-an fungsi yang ada di Excel, kerjaan analis harian cuma butuh sekitar 40 fungsi.
Sisanya jarang kepake, atau udah digantiin fungsi yang lebih baru.
Aku susun daftar ini dari pola kerjaan rekap penjualan, bersihin data mentah, dan bikin laporan bulanan. Dikelompokkan per tugas, jadi kamu bisa langsung loncat ke kelompok yang lagi kamu butuhin.
Fungsi wajib analis kebagi tujuh kelompok: agregasi (SUM, COUNTIFS), lookup (XLOOKUP, INDEX MATCH), logika (IF, IFERROR), teks (TRIM, TEXT), tanggal (EOMONTH, DATEDIF), statistik (MEDIAN, STDEV.S), dan array dinamis (UNIQUE, FILTER). Kuasai tujuh kelompok ini, sekitar 90% kerjaan rekap harian kelar tanpa nyontek.
Urutan belajarnya juga penting. Mulai dari agregasi dan logika, baru lookup, terakhir array dinamis.
Ini kelompok yang paling sering kepake. Semua laporan penjualan ujungnya nambahin angka dan ngitung baris.
| Fungsi | Sintaks singkat | Buat apa |
|---|---|---|
| SUM | =SUM(B2:B100) | Total satu kolom angka |
| SUMIF | =SUMIF(A2:A100;"Bandung";B2:B100) | Total dengan satu syarat |
| SUMIFS | =SUMIFS(B2:B100;A2:A100;"Bandung";C2:C100;"Kopi") | Total dengan banyak syarat |
| AVERAGE | =AVERAGE(B2:B100) | Rata-rata, abaikan sel kosong |
| COUNTA | =COUNTA(A2:A100) | Hitung sel yang ada isinya |
| COUNTIF | =COUNTIF(C2:C100;"Kopi") | Hitung baris yang cocok satu syarat |
| COUNTIFS | =COUNTIFS(C2:C100;"Kopi";B2:B100;">50000") | Hitung baris dengan banyak syarat |
Catatan pemisah argumen: aku pakai titik koma karena itu default Excel dengan regional Indonesia. Kalau Excel kamu setelan English, ganti jadi koma.
Konsep di balik kelompok ini namanya aggregate function, dan logikanya sama persis di SQL maupun pivot table.
Kelompok kedua yang paling sering ditanyain. Intinya nyocokin ID atau nama dari satu tabel ke tabel lain.
| Fungsi | Sintaks singkat | Buat apa |
|---|---|---|
| XLOOKUP | =XLOOKUP(D2;A2:A100;B2:B100;"Nggak ketemu") | Cari nilai ke segala arah, punya argumen kalau nggak ketemu |
| VLOOKUP | =VLOOKUP(D2;A2:C100;3;FALSE) | Cari ke kanan, masih ada di file lama |
| INDEX | =INDEX(B2:B100;5) | Ambil nilai dari posisi baris tertentu |
| MATCH | =MATCH(D2;A2:A100;0) | Cari posisi baris sebuah nilai |
| XMATCH | =XMATCH(D2;A2:A100) | Versi baru MATCH, bisa cari dari bawah |
| OFFSET | =OFFSET(A1;3;2) | Geser referensi sekian baris dan kolom |
Kalau Excel kamu versi Microsoft 365, pakai XLOOKUP aja. VLOOKUP cuma bisa nyari ke kanan dan gampang rusak kalau ada kolom disisipin di tengah.
Kombinasi INDEX MATCH masih relevan buat file yang harus kompatibel sama Excel 2019 ke bawah.
Ini yang bikin laporanmu nggak berantakan pas ada data aneh.
| Fungsi | Sintaks singkat | Buat apa |
|---|---|---|
| IF | =IF(B2>1000000;"Besar";"Kecil") | Percabangan dua hasil |
| IFS | =IFS(B2>5000000;"A";B2>1000000;"B";TRUE;"C") | Percabangan banyak kondisi tanpa IF bertumpuk |
| AND | =AND(B2>0;C2="Lunas") | Semua syarat harus benar |
| OR | =OR(C2="Lunas";C2="Cicil") | Salah satu syarat benar udah cukup |
| IFERROR | =IFERROR(XLOOKUP(...);0) | Ganti pesan error jadi nilai yang kamu mau |
| SWITCH | =SWITCH(C2;"JKT";"Jakarta";"BDG";"Bandung";"Lainnya") | Petakan kode jadi label |
Pembahasan panjang SWITCH beserta bedanya dengan IFS ada di rumus SWITCH Excel.
Data ekspor dari kasir atau marketplace hampir selalu kotor. Kelompok ini yang bersihin.
| Fungsi | Sintaks singkat | Buat apa |
|---|---|---|
| TRIM | =TRIM(A2) | Buang spasi berlebih di awal, akhir, dan tengah |
| LEFT | =LEFT(A2;3) | Ambil sekian karakter dari kiri |
| MID | =MID(A2;4;5) | Ambil karakter dari posisi tengah |
| SUBSTITUTE | =SUBSTITUTE(A2;"Rp ";"") | Ganti potongan teks tertentu |
| TEXTJOIN | =TEXTJOIN(", ";TRUE;A2:C2) | Gabung banyak sel pakai pemisah |
| TEXT | =TEXT(B2;"#.##0") | Ubah angka jadi teks dengan format tertentu |
TRIM biasanya yang pertama aku jalanin. Spasi siluman di akhir nama kota bikin XLOOKUP gagal padahal keliatannya sama persis.
Masalah semacam ini masuk kategori data quality, dan efeknya nular ke semua laporan turunan.
| Fungsi | Sintaks singkat | Buat apa |
|---|---|---|
| TODAY | =TODAY() | Tanggal hari ini, otomatis ganti |
| EOMONTH | =EOMONTH(A2;0) | Tanggal akhir bulan dari sebuah tanggal |
| DATEDIF | =DATEDIF(A2;TODAY();"m") | Selisih dua tanggal dalam hari, bulan, atau tahun |
| WEEKDAY | =WEEKDAY(A2;2) | Nomor hari dalam seminggu, Senin sampai Minggu |
| NETWORKDAYS | =NETWORKDAYS(A2;B2;libur) | Hitung hari kerja antara dua tanggal |
EOMONTH kepake banget buat bikin kolom bantu bulan. Semua transaksi Oktober jadi punya nilai 31 Oktober, gampang di-group.
| Fungsi | Sintaks singkat | Buat apa |
|---|---|---|
| MEDIAN | =MEDIAN(B2:B100) | Nilai tengah, tahan sama nilai ekstrem |
| STDEV.S | =STDEV.S(B2:B100) | Sebaran data dari sampel |
| PERCENTILE.INC | =PERCENTILE.INC(B2:B100;0,9) | Nilai di persentil tertentu |
| RANK.EQ | =RANK.EQ(B2;$B$2:$B$100;0) | Peringkat sebuah nilai dalam daftar |
| CORREL | =CORREL(B2:B100;C2:C100) | Korelasi dua kolom angka |
MEDIAN sering lebih jujur dari AVERAGE buat data transaksi. Satu order korporat gede bisa narik rata-rata jauh dari kenyataan, dan itu ciri outlier.
Kelompok terbaru, cuma jalan di Microsoft 365 dan Excel 2021 ke atas. Sekali nulis, hasilnya numpah ke banyak sel.
| Fungsi | Sintaks singkat | Buat apa |
|---|---|---|
| UNIQUE | =UNIQUE(A2:A100) | Daftar nilai unik tanpa duplikat |
| FILTER | =FILTER(A2:C100;C2:C100="Kopi") | Saring baris sesuai syarat |
| SORT | =SORT(A2:C100;3;-1) | Urutkan hasil tanpa ubah data asli |
| SEQUENCE | =SEQUENCE(12) | Bikin deret angka berurutan |
| SUMPRODUCT | =SUMPRODUCT(B2:B100;C2:C100) | Kali antar kolom lalu jumlahin |
Gabungan =SORT(UNIQUE(A2:A100)) ngasih daftar kota unik yang udah urut. Satu rumus, nol klik menu.
Daftar resmi semua fungsi beserta argumennya bisa kamu cek di dokumentasi fungsi Excel dari Microsoft.
Dataset toko_berkah punya 1.842 baris transaksi Oktober 2025 dari 4 cabang. Kolomnya: tanggal, cabang, kategori, qty, total.
Ini lima rumus yang aku pakai buat bikin rekapnya:
=SUM(E2:E1843) keluar Rp 487.310.000.=SUMIF(B2:B1843;"Bandung";E2:E1843) keluar Rp 118.240.000, atau 24,3% dari total.=COUNTIFS(C2:C1843;"Kopi";E2:E1843;">100000") keluar 213 transaksi.=MEDIAN(E2:E1843) keluar Rp 164.500, padahal rata-ratanya Rp 264.556.=SORT(UNIQUE(B2:B1843)).Selisih median dan rata-rata di poin 4 itu yang paling menarik. Jaraknya 61%, tanda ada segelintir order besar yang narik rata-rata ke atas.
Kalau kamu lapor pakai rata-rata doang, gambaran transaksi hariannya jadi kelewat optimis.
Lupa kunci referensi. Rumus RANK.EQ tanpa $B$2:$B$100 bakal geser rentangnya pas di-drag ke bawah. Peringkatnya jadi ngaco.
Pakai VLOOKUP tanpa FALSE. Argumen keempat kosong berarti pencarian perkiraan. Hasilnya bisa nyantol ke baris tetangga tanpa kasih error.
Numpuk IFERROR di mana-mana. Error yang ketutup bukan berarti data udah benar. Cek dulu penyebabnya, baru bungkus.
Angka masih berupa teks. Hasil ekspor sering nyimpen angka sebagai teks, jadi SUM ngasih nol. Cek pakai =ISNUMBER(E2) sebelum panik.
Rentang beda panjang di SUMIFS. Rentang syarat dan rentang jumlah harus sama tingginya. Kalau nggak, Excel ngasih #VALUE!.
Sekitar 40 fungsi, dan kamu nggak perlu hafal sintaksnya persis. Yang penting kamu tau fungsi mana buat masalah apa, sisanya bisa dicek pas nulis. Excel juga nampilin urutan argumen begitu kamu ketik tanda kurung. Fokus dulu ke SUMIFS, COUNTIFS, XLOOKUP, IF, IFERROR, dan TRIM. Enam ini nutup mayoritas kerjaan rekap harian.
XLOOKUP dulu kalau Excel kamu Microsoft 365 atau Excel 2021 ke atas. Sintaksnya lebih pendek, bisa nyari ke kiri, dan punya argumen bawaan kalau nilainya nggak ketemu. Tapi tetap pelajari VLOOKUP secukupnya, soalnya file warisan di kantor masih banyak yang pakai. Kamu bakal sering diminta benerin file orang lain yang isinya VLOOKUP.
Tiga penyebab paling sering: angka kesimpan sebagai teks, ada spasi tersembunyi di kriteria, atau rentang syarat dan rentang jumlah nggak sejajar. Coba jalanin TRIM ke kolom teksnya, lalu cek =ISNUMBER(sel) di kolom angka. Kalau hasilnya FALSE, ubah dulu jadi angka beneran pakai Text to Columns atau kali satu.
UNIQUE, FILTER, SORT, dan SEQUENCE cuma ada di Microsoft 365 dan Excel 2021 ke atas. Di Excel 2019 atau 2016 fungsinya nggak dikenali, jadi muncul #NAME?. Gantinya, pakai kombinasi INDEX MATCH atau Remove Duplicates dari menu Data. Kalau file kamu bakal dibuka orang dengan Excel lama, hindari array dinamis biar nggak rusak.
Rumus cukup buat file di bawah puluhan ribu baris dan proses yang jarang diulang. Begitu kamu ngerjain hal yang sama tiap minggu, misalnya gabungin lima file ekspor, Power Query lebih hemat. Dia nyimpen langkah pembersihan dan tinggal di-refresh. Aturan praktisnya: rumus buat analisis sekali jalan, Power Query buat proses yang berulang.
Empat puluh fungsi di atas nutup hampir semua kerjaan rekap analis. Kelompokin per tugas, jangan hafal per abjad.
Mulai dari agregasi dan logika, soalnya dua kelompok itu yang paling sering dipanggil. Lookup nyusul, array dinamis terakhir.
Simpan halaman ini, lalu coba satu rumus dari tiap kelompok di file kerjaanmu minggu ini. Lanjut baca cara VLOOKUP banyak kriteria di Excel kalau lookup-mu mulai butuh lebih dari satu kunci.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Punya ratusan angka dan mau tau sebarannya masuk ke rentang mana aja? FREQUENCY ngitung distribusi frekuensi sekali jalan, tanpa nulis COUNTIFS berkali-kali.
SUMIFS ribet kalau kriterianya banyak dan sering ganti. Rumus database Excel kayak DSUM baca kriteria dari sel terpisah, jadi kamu tinggal ubah tabel kecil tanpa nyentuh rumus.
DATEDIF ngasih umur bulat, tapi kadang kamu butuh angka desimal kayak 27,6 tahun buat hitung masa kerja atau bunga. YEARFRAC ngasih pecahan tahun dengan presisi.