Tips kerja · 9 menit baca
Kebanyakan pekerjaan di spreadsheet sebenarnya berputar di pertanyaan yang sama: berapa total yang sesuai kriteria ini, berapa kali data ini muncul, bagaimana memberi label otomatis, dan bagaimana mencari data dari tabel lain. Lima rumus di artikel ini menjawab pertanyaan-pertanyaan itu.
Aku menyebutnya rumus yang sering dipakai untuk kerjaan sehari-hari, bukan peringkat resmi. Kelimanya berjalan di Microsoft Excel dan Google Sheets. Semua contoh memakai satu tabel kecil yang sama supaya kamu bisa mengetik ulang dan mencocokkan hasilnya sendiri.
Persiapan
Buat tabel di bawah ini di sheet kosong, mulai dari sel A1. Dengan begitu alamat sel di rumus akan sama persis dengan yang ada di artikel.
Tabel contoh yang dipakai di semua rumus (data fiktif)
| A: Produk | B: Harga | C: Terjual | D: Status | |
|---|---|---|---|---|
| 1 | Produk | Harga | Terjual | Status |
| 2 | Kopi | 18000 | 12 | Lunas |
| 3 | Teh | 12000 | 8 | Belum |
| 4 | Kopi | 18000 | 5 | Lunas |
| 5 | Roti | 10000 | 20 | Belum |
Baris 1 adalah judul kolom. Data dimulai dari baris 2 sampai baris 5, makanya rentang rumus ditulis A2:A5.
Pemisah argumen
Di komputer dengan pengaturan regional Indonesia, argumen rumus dipisahkan titik koma (;). Di pengaturan regional lain biasanya koma (,). Artikel ini memakai titik koma. Kalau rumusmu error padahal sudah benar, ganti ; menjadi , atau sebaliknya.
Rumus 1
SUMIF menjumlahkan angka hanya dari baris yang memenuhi syarat. Cocok untuk pertanyaan seperti “berapa total kopi yang terjual?” tanpa harus menyaring tabel secara manual.
Bentuk umumnya: =SUMIF(rentang_yang_dicek; kriteria; rentang_yang_dijumlah). Rentang pertama dicocokkan dengan kriteria, lalu angka di rentang ketiga pada baris yang cocok dijumlahkan.
Total kopi yang terjual
=SUMIF(A2:A5;"Kopi";C2:C5)Hasil: 17
Kopi muncul di baris 2 (12 terjual) dan baris 4 (5 terjual). 12 + 5 = 17.
Variasi yang berguna
Jumlahkan hanya penjualan yang 10 atau lebih
=SUMIF(C2:C5;">=10";C2:C5)Hasil: 32
Hanya baris 2 (12) dan baris 5 (20) yang memenuhi syarat >=10. 12 + 20 = 32. Karena rentang yang dicek sama dengan rentang yang dijumlah, argumen ketiga boleh dihilangkan.
- Kriteria teks ditulis di antara tanda kutip, misalnya "Kopi".
- Kriteria perbandingan juga di dalam kutip, misalnya ">=10".
- Kriteria bisa mengambil dari sel lain, misalnya =SUMIF(A2:A5;F1;C2:C5) dengan F1 berisi nama produk. Jadi satu rumus bisa dipakai untuk produk apa pun.
Rumus 2
COUNTIF menghitung jumlah sel yang memenuhi kriteria. Bedanya dengan SUMIF: COUNTIF menghitung banyaknya baris, bukan menjumlahkan angka.
Berapa transaksi yang sudah lunas
=COUNTIF(D2:D5;"Lunas")Hasil: 2
Kata “Lunas” ada di baris 2 dan baris 4, jadi hasilnya 2.
Berapa transaksi yang belum lunas
=COUNTIF(D2:D5;"Belum")Hasil: 2
Kata “Belum” ada di baris 3 dan baris 5.
Perbedaan penting
SUMIF menjawab “berapa totalnya”, COUNTIF menjawab “berapa banyaknya”. Kalau kamu ingin tahu jumlah transaksi kopi, pakai COUNTIF. Kalau ingin tahu jumlah kopi yang terjual, pakai SUMIF.
Rumus 3
IF memeriksa sebuah kondisi lalu menampilkan satu hasil kalau kondisinya benar dan hasil lain kalau salah. Bentuknya: =IF(kondisi; hasil_kalau_benar; hasil_kalau_salah).
Beri label Laris atau Biasa
=IF(C2>=10;"Laris";"Biasa")Hasil: Laris
Terjual di C2 adalah 12. Karena 12 lebih besar dari atau sama dengan 10, kondisinya benar dan hasilnya “Laris”. Salin rumus ke bawah untuk baris lainnya: Teh (8) menjadi “Biasa”, Roti (20) menjadi “Laris”.
Lebih dari dua kemungkinan
IF bisa disusun bertingkat. Rumus di bawah memeriksa kondisi paling ketat dulu, lalu turun ke kondisi berikutnya.
Tiga tingkat label
=IF(C2>=15;"Sangat laris";IF(C2>=10;"Laris";"Biasa"))Hasil: Laris
Untuk C2 = 12: tidak memenuhi >=15, lalu memenuhi >=10, hasilnya “Laris”. Untuk Roti (20) hasilnya “Sangat laris”.
Urutan kondisi
Pada IF bertingkat, tulis kondisi yang paling ketat lebih dulu. Kalau kamu menaruh >=10 di depan, nilai 20 akan berhenti di sana dan tidak pernah jadi “Sangat laris”.
Rumus 4
VLOOKUP mencari sebuah nilai di kolom paling kiri tabel, lalu mengambil nilai di kolom lain pada baris yang sama. Bentuknya: =VLOOKUP(yang_dicari; tabel; nomor_kolom; FALSE).
Cari harga Teh
=VLOOKUP("Teh";A:B;2;FALSE)Hasil: 12000
Rumus mencari “Teh” di kolom A, lalu mengambil nilai dari kolom ke-2 dalam rentang A:B, yaitu kolom Harga. Hasilnya 12000.
Arti tiap argumen
- 1yang_dicari: nilai yang kamu cari, di sini "Teh". Bisa juga alamat sel.
- 2tabel: rentang yang berisi data. Kolom pertama rentang inilah tempat nilai dicari.
- 3nomor_kolom: kolom keberapa dalam rentang tabel yang nilainya ingin diambil. A:B berarti A = 1 dan B = 2.
- 4FALSE: cari yang persis sama. Ini yang paling aman untuk teks. TRUE (atau dikosongkan) memakai pencocokan perkiraan dan baru cocok kalau datanya terurut.
Dua batasan VLOOKUP
Kolom yang dicari harus berada paling kiri di rentang tabel, dan nomor kolom dihitung dari kolom paling kiri itu. Selain itu, VLOOKUP mengembalikan hasil pertama yang ditemukan. Karena Kopi muncul dua kali di tabel contoh, VLOOKUP untuk “Kopi” hanya mengambil baris yang pertama.
Kalau kamu memakai Excel versi terbaru atau Google Sheets, ada fungsi XLOOKUP yang lebih fleksibel dan tidak mengharuskan kolom pencarian di paling kiri. Artikel ini memakai VLOOKUP karena paling sering dijumpai di file kerja yang sudah ada.
Rumus 5
Kalau VLOOKUP tidak menemukan datanya, ia mengembalikan pesan #N/A yang membuat tabel terlihat berantakan. IFERROR membungkus rumus lain dan mengganti error apa pun dengan teks yang kamu tentukan. Bentuknya: =IFERROR(rumus; nilai_kalau_error).
Cari produk yang tidak ada
=IFERROR(VLOOKUP("Susu";A:B;2;FALSE);"Tidak ada")Hasil: Tidak ada
“Susu” tidak ada di tabel, jadi VLOOKUP biasa akan menampilkan #N/A. Dengan IFERROR, yang tampil adalah “Tidak ada”.
Jangan menutupi masalah
IFERROR menyembunyikan semua jenis error, termasuk kesalahan penulisan rumus. Pakai ketika kamu paham kenapa error itu muncul. Kalau tidak, kamu bisa tidak sadar ada rumus yang salah.
Pemecahan masalah
Pesan error yang paling sering muncul
| Pesan | Kemungkinan penyebab | Yang dicoba |
|---|---|---|
| #N/A | Data yang dicari tidak ada di kolom pencarian. | Periksa ejaan dan spasi tersembunyi. Bungkus dengan IFERROR kalau memang wajar tidak ada. |
| #NAME? | Nama fungsi salah ketik, atau teks tanpa tanda kutip. | Periksa ejaan fungsi, dan pastikan teks di rumus ada di dalam tanda kutip. |
| Rumus ditolak / tidak dikenali | Pemisah argumen tidak sesuai pengaturan regional. | Ganti ; menjadi , atau sebaliknya. |
| Hasil 0 padahal harusnya ada | Kriteria tidak cocok persis, atau angka tersimpan sebagai teks. | Cek ejaan kriteria dan format sel angka. |
Cara cepat untuk memastikan masalahnya ada di pemisah: ketik rumus sederhana seperti =SUM(1;2). Kalau muncul error, ganti menjadi =SUM(1,2). Yang berhasil itulah pemisah yang dipakai komputermu.
Latihan
Cara terbaik supaya rumus ini menempel adalah mengetiknya sendiri. Coba kerjakan urutan ini di tabel contoh, lalu cocokkan dengan jawabannya.
- 1Hitung total roti yang terjual. Jawaban: 20.
- 2Hitung ada berapa produk yang statusnya Belum. Jawaban: 2.
- 3Beri label di setiap baris: “Laris” kalau terjual 10 atau lebih, selain itu “Biasa”.
- 4Ambil harga Roti dengan VLOOKUP. Jawaban: 10000.
- 5Ambil harga Susu dan tampilkan “Tidak ada” kalau tidak ketemu.
Rumus ini bisa dipakai di Excel dan Google Sheets?+
Bisa. Kelimanya berjalan di dua-duanya. Yang bisa berbeda hanya pemisah argumen, tergantung pengaturan regional.
Kenapa rumusku error padahal sudah sama dengan contoh?+
Paling sering karena pemisah argumen. Coba ganti titik koma (;) menjadi koma (,) atau sebaliknya.
Kenapa muncul #N/A?+
Biasanya data yang dicari tidak ada di tabel, atau ada perbedaan kecil seperti spasi di akhir teks. Bungkus rumus dengan IFERROR supaya tampilan tetap rapi.
Apakah ada file contoh yang bisa diunduh?+
Belum. Tabelnya kecil dan bisa dibuat sendiri dari contoh di artikel ini.
Semua data di artikel ini fiktif dan hanya untuk contoh perhitungan.
Ivan Darmawan
Tutorial Claude AI buat kerjaan & bisnis