12 Rumus Excel yang Sering Dipakai di Kantor, plus Contohnya
Masih menjumlah pakai kalkulator di samping Excel? Ini 12 rumus Excel yang paling sering dipakai di kantor, dari SUM sampai XLOOKUP, dengan contoh data penjualan.
Atasan minta rekap penjualan per sales sebelum jam makan siang, dan kamu masih menjumlah satu per satu dengan kalkulator. Padahal pekerjaan seperti ini bisa selesai dalam hitungan menit. Kamu tidak perlu menghafal ratusan rumus. Selusin rumus di bawah ini sudah menutup sebagian besar kebutuhan admin, staf keuangan, dan staf penjualan.
Data contoh yang dipakai
Semua contoh memakai tabel penjualan mini berikut, di sheet dengan judul kolom di baris 1. Di kantor, datamu tentu lebih panjang (misalnya sampai baris 101), tetapi cara kerjanya sama.
| Baris | A Tanggal | B Sales | C Produk | D Jumlah | E Harga | F Total (D x E) |
|---|---|---|---|---|---|---|
| 2 | 02/10 | Rina | Kopi | 10 | 25.000 | 250.000 |
| 3 | 02/10 | Budi | Teh | 20 | 15.000 | 300.000 |
| 4 | 03/10 | Rina | Kopi | 40 | 25.000 | 1.000.000 |
| 5 | 03/10 | Sari | Gula | 5 | 30.000 | 150.000 |
| 6 | 04/10 | Budi | Kopi | 8 | 25.000 | 200.000 |
| 7 | 04/10 | Rina | Teh | 30 | 15.000 | 450.000 |
Kolom F berisi =D2*E2 dan ditarik ke bawah. Total semua transaksi adalah Rp2.350.000.
Pemisah rumus mengikuti pengaturan wilayah komputer. Kalau rumus dengan koma tidak jalan, ganti koma menjadi titik koma.
Rumus 1 sampai 4: hitung dasar
| Rumus | Contoh | Hasil pada data di atas |
|---|---|---|
| SUM | =SUM(F2:F7) |
2.350.000 |
| AVERAGE | =AVERAGE(F2:F7) |
sekitar 391.667 |
| MAX / MIN | =MAX(F2:F7) dan =MIN(F2:F7) |
1.000.000 dan 150.000 |
| ROUND | =ROUND(F2*11%,0) |
27.500 (pajak 11% dari 250.000) |
Untuk data yang terus bertambah, tulis rentang lebih panjang atau ubah data menjadi Tabel agar rumus ikut menyesuaikan.
Rumus 5 dan 6: menghitung dengan syarat
COUNTIF menghitung baris yang memenuhi syarat. =COUNTIF(B2:B7,"Rina") menghasilkan 3.
SUMIF menjumlahkan nilai yang memenuhi syarat. =SUMIF(B2:B7,"Rina",F2:F7) menghasilkan 1.700.000 (250.000 + 1.000.000 + 450.000).
Untuk syarat lebih dari satu pakai SUMIFS. Total penjualan Rina untuk produk Kopi: =SUMIFS(F2:F7,B2:B7,"Rina",C2:C7,"Kopi") menghasilkan 1.250.000 (250.000 + 1.000.000).
Ingin lebih dalam? Artikel ini baru membahas sebagian kecil. Di ebook Excel untuk Kerja Kantor, kamu akan mempelajari lebih jauh:
- Merapikan data menjadi Tabel Excel dan sepuluh pintasan keyboard yang paling sering dipakai
- SUM, AVERAGE, COUNTIF, IF, SUMIFS, COUNTIFS, ROUND, dan referensi absolut ($)
- XLOOKUP, VLOOKUP, IFERROR, dan daftar pilihan agar tidak ada salah ketik
Semua langkahnya disusun berurutan dan bisa langsung dipraktikkan. Lihat isi bukunya.
Rumus 7: IF untuk keputusan otomatis
=IF(F2>=1000000,"Target","Belum") menulis "Target" bila transaksi minimal Rp1.000.000. Pada data contoh, hanya baris 4 yang bertulis "Target". Untuk bonus 2% pada transaksi yang mencapai target: =IF(F4>=1000000,F4*2%,0) menghasilkan 20.000, sedangkan untuk F2 hasilnya 0.
Rumus 8 dan 9: VLOOKUP dan XLOOKUP
Misalnya ada sheet bernama Harga: nama produk di kolom A, harga di kolom B (Kopi 25.000, Teh 15.000, Gula 30.000). Di E2 kamu tidak perlu mengetik harga, cukup ambil dari sana.
- VLOOKUP:
=VLOOKUP(C2,Harga!A:B,2,FALSE)menghasilkan 25.000 untuk Kopi. - XLOOKUP (Excel 2021, Microsoft 365, Google Sheets):
=XLOOKUP(C2,Harga!A:A,Harga!B:B,"Tidak ada").
XLOOKUP lebih fleksibel. Kolom yang dicari tidak harus berada paling kiri, dan kamu bisa menentukan teks bila data tidak ditemukan. Di Excel versi lama, pakai VLOOKUP. Argumen FALSE pada VLOOKUP berarti pencocokan persis, jangan dilupakan.
Rumus 10: IFERROR agar laporan bersih
=IFERROR(VLOOKUP(C2,Harga!A:B,2,FALSE),0) mengisi 0 bila produk tidak ada di daftar harga, sehingga #N/A tidak muncul di laporan. Tetapi hati-hati: IFERROR menyembunyikan semua jenis galat. Sesekali periksa dulu penyebabnya sebelum menyembunyikannya.
Rumus 11: TRIM merapikan data salinan
Data dari sistem lain sering membawa spasi berlebih, sehingga "Rina " dan "Rina" dianggap berbeda dan SUMIF-mu terlihat salah. =TRIM(B2) menghapus spasi di awal, di akhir, dan spasi ganda di tengah.
Rumus 12: TEXT dan & untuk menggabung teks
="Total "&B2&": Rp"&TEXT(F2,"#,##0") menghasilkan kalimat seperti Total Rina: Rp250,000. Pemisah ribuan di dalam TEXT juga mengikuti pengaturan wilayah, jadi bisa berupa titik atau koma.
Contoh kasus: rekap per sales dalam 5 menit
- Di H2 sampai H4, tulis Rina, Budi, Sari.
- Di I2:
=SUMIF($B$2:$B$7,H2,$F$2:$F$7), tarik ke bawah. - Di J2:
=COUNTIF($B$2:$B$7,H2). - Di K2:
=IFERROR(I2/J2,0).
| Sales | Total (I) | Transaksi (J) | Rata-rata (K) |
|---|---|---|---|
| Rina | 1.700.000 | 3 | sekitar 566.667 |
| Budi | 500.000 | 2 | 250.000 |
| Sari | 150.000 | 1 | 150.000 |
Jumlah kolom I adalah 2.350.000, sama dengan SUM total. Cocokkan seperti ini setiap kali membuat rekap sebagai pemeriksaan cepat. Tanda $ mengunci rentang agar tidak bergeser saat rumus ditarik.
Skenario lengkap: dari data mentah ke laporan rapi
Bayangkan kamu menerima file penjualan hasil ekspor dari kasir. Isinya berantakan: nama sales kadang memakai spasi tambahan, harga belum terisi, dan atasan ingin laporan siap sore ini. Alurnya bisa kamu ikuti pelan-pelan.
Pertama, bersihkan nama sales. Buat kolom bantu di sebelah kolom B, isi dengan TRIM, lalu salin hasilnya sebagai nilai ke kolom aslinya. Sesudah itu, "Rina " dan "Rina" tidak lagi terhitung sebagai dua orang berbeda.
Kedua, isi harga dari daftar harga memakai VLOOKUP yang dibungkus IFERROR. Jika ada produk yang tidak ditemukan, sel akan berisi 0. Filter kolom harga untuk melihat baris yang bernilai 0, lalu periksa apakah itu salah ketik pada nama produk atau memang produk baru yang belum masuk daftar.
Ketiga, hitung total dengan mengalikan jumlah dan harga, kemudian tarik ke seluruh baris. Cek hasilnya dengan SUM, lalu bandingkan dengan angka total dari sistem kasir. Kalau selisihnya nol, datamu aman. Kalau tidak, biasanya ada baris yang terlewat atau angka yang tersimpan sebagai teks.
Keempat, buat rekap per sales dengan SUMIF dan COUNTIF seperti contoh di atas. Terakhir, tambahkan kolom IF untuk menandai siapa yang mencapai target. Seluruh proses ini memakan waktu sekitar setengah jam saat pertama kali, dan akan jauh lebih singkat di bulan berikutnya karena rumusnya tinggal dipakai ulang. Simpan file ini sebagai templat, lalu cukup tempel data baru setiap bulan.
Kesalahan yang sering terjadi
- Angka tersimpan sebagai teks, sehingga SUM menghasilkan 0. Ubah format sel menjadi Number.
- Rentang tidak ikut bertambah saat baris baru ditambah. Pakai Tabel (Ctrl + T).
- Lupa FALSE pada VLOOKUP sehingga hasilnya salah tanpa pesan galat.
- Mengetik ulang hasil rumus sebagai angka. Laporan tidak lagi berubah saat data berubah.
Pertanyaan yang sering diajukan
Kenapa rumus tampil sebagai teks? Format sel kemungkinan Text, atau ada spasi sebelum tanda sama dengan. Ubah ke General lalu ketik ulang.
VLOOKUP atau XLOOKUP? XLOOKUP lebih mudah, tetapi hanya ada di versi baru. Kalau file dibuka di Excel lama, VLOOKUP lebih aman.
Apakah rumusnya sama di Google Sheets? Sebagian besar sama. Perbedaan biasanya pada pemisah rumus dan format angka.
Kalau artikel ini membantu, langkah berikutnya ada di ebook Excel untuk Kerja Kantor.
Pelajari sampai tuntas di “Excel untuk Kerja Kantor”
“Menguasai Microsoft Excel” adalah salah satu syarat yang paling sering tertulis di lowongan kerja. Untuk sebagian besar pekerjaan kantor, yang dibutuhkan bukan ratusan rumus, mela...
