Kenapa hasil check-nya ngaco, dan cara benerinnya
Baca pelan-pelan ±7 menit. Praktiknya nanti di PC kantor, tinggal copy rumusnya.
Intinya dulu: datamu nggak salah, format number-mu juga nggak salah. Masalahnya ada dua, dan dua-duanya di rumus:
- Rumusnya geser satu baris. Rumus di baris 3 malah ngecek baris 2. Makanya hasilnya kelihatan acak.
- VLOOKUP cuma ngambil baris pertama yang ketemu. Padahal satu No PR di Sheet2 bisa punya banyak item.
Nomor 1 dibenerin 10 detik. Nomor 2 butuh ganti rumus (sudah kusiapin di Langkah 4).
Langkah 1
VLOOKUP itu sebenernya ngapain?
Bayangin kamu nyuruh orang: “Cariin No PR ini di Sheet2, terus bacain Total harga-nya.” Orang itu kerjanya begini:
B3→
② Turun di kolom B Sheet2 dari atas→
③ Berhenti di yang pertama cocok→
④ Geser ke kolom ke-16, bacain isinya
Di rumusmu, itu bagian ini:
VLOOKUP(B3, Sheet2!B:Q, 16, FALSE)
B3→ yang dicari (No PR)Sheet2!B:Q→ tempat nyarinya. Selalu nyari di kolom paling kiri (kolom B)16→ ambil kolom ke-16 dihitung dari B, yaitu kolom Q (Total harga)FALSE→ harus sama persis
Ini kelihatan di screenshot Sheet2-mu sendiri. No PR 251040049 ada tiga baris, VLOOKUP cuma baca yang kuning:
Yang perlu diinget: VLOOKUP itu “males”. Begitu ketemu satu, dia langsung berhenti. Dia nggak peduli di bawahnya masih ada No PR yang sama. Ini kunci masalah nomor 2.
Langkah 2
Masalah pertama: rumusnya geser satu baris
Screenshot-mu. Kotak merah = baris 7 dan 8. Klik gambarnya buat memperbesar, klik lagi buat balik.
Lihat screenshot-mu sendiri. Baris 7 dan baris 8 itu No PR-nya sama, harganya juga sama:
| B · NO PR | C · ITEM | F · HARGA TOTAL | K · HASIL CHECK | |
|---|---|---|---|---|
| 7 | 251140049 | screwdriver (flathead) 5 mm | 87,912.00 | Sama |
| 8 | 251140049 | screwdriver (flathead) 6 mm | 87,912.00 | Beda |
Rumusmu cuma lihat kolom B dan kolom F. Kalau B dan F-nya kembar, hasilnya harus kembar juga. Tapi ini satu Sama, satu Beda. Artinya rumus di baris itu bukan lagi ngelihat barisnya sendiri.
Penyebabnya: judul tabelmu ada di baris 2 (baris 1 kosong), jadi data pertama ada di baris 3. Tapi rumus yang kamu kirim isinya F2 dan B2. Jadi rumus di K3 ngecek baris 2, rumus di K4 ngecek baris 3, dan seterusnya. Semua telat satu baris.
Kalau diurutin, cocok persis sama screenshot-mu:
| B · NO PR | F · HARGA TOTAL | K yang muncul | Yang sebenernya dibandingin | |
|---|---|---|---|---|
| 3 | 251040049 | 4,036,800.00 | Beda | baris 2 = judul “HARGA TOTAL” vs judul “Total harga” |
| 4 | 251140010 | 61,849,200.00 | Sama | baris 3: 4,036,800 vs 4,036,800 ✓ |
| 5 | 251140011 | 4,029,755.10 | Beda | baris 4: 61,849,200 vs 7,148,400 |
| 6 | 251140049 | 66,600.00 | Sama | baris 5: 4,029,755.10 vs data PR 251140011 |
| 7 | 251140049 | 87,912.00 | Sama | baris 6: 66,600 vs 66,600 ✓ |
| 8 | 251140049 | 87,912.00 | Beda | baris 7: 87,912 vs 66,600 |
| 9 | 251140049 | 66,600.00 | Beda | baris 8: 87,912 vs 66,600 |
| 10 | 251140049 | 87,912.00 | Sama | baris 9: 66,600 vs 66,600 ✓ |
| 11 | 251140049 | 87,912.00 | Beda | baris 10: 87,912 vs 66,600 |
Catatan: Sheet2 untuk PR 251140049 dan 251140011 belum pernah kulihat. Di tabel ini aku anggap baris pertamanya 66,600 dan 4,029,755.10. Polanya cocok semua, tapi tetep cek sendiri ya (caranya di bawah).
Ini juga jawaban buat yang kamu bingungin kemarin:
- Pipa Mild steel (4,036,800) kok Beda? Padahal di Sheet2 angkanya sama. Karena K3 lagi ngebandingin judul kolom, bukan si pipa.
- Cable copper (61,849,200) kok Sama? Padahal sumbernya 2 baris. Karena K4 lagi ngecek si pipa di baris 3, dan si pipa memang cocok.
Cara cek (10 detik)
- Klik sel
K3(hasil check paling atas). - Lihat formula bar di atas. Kalau tulisannya
F2danB2, berarti bener geser.
Cara benerin
- Di
K3, ganti semua angka 2 jadi 3:=IFERROR(IF(F3=VLOOKUP(B3, Sheet2!B:Q, 16, FALSE), "Sama", "Beda"), "Kode Tidak Ditemukan")
- Enter, lalu klik
K3lagi dan dobel-klik kotak kecil di pojok kanan bawah sel biar rumusnya turun sampai baris terakhir.
Patokannya gampang: rumus di baris 3 harus nyebut B3 dan F3. Angka barisnya harus sama dengan baris tempat rumus itu berada.
Langkah 3
Masalah kedua: satu No PR, banyak baris
Habis Langkah 2, hasilnya bakal jauh lebih masuk akal. Tapi masih ada yang Beda padahal datanya bener. Ini gara-gara si VLOOKUP yang “males” tadi. Ada dua bentuk:
Bentuk A — di Sheet2 ada 2 baris, di sheet-mu dijumlah jadi 1
Contohnya Cable copper. Di sheet 2025 cuma satu baris, isinya 61,849,200. Di Sheet2:
| No PR | Item/Service Description | Total harga | |
|---|---|---|---|
| 251140010 | CABLE COPPER CORE XLPE… | 7,148,400.00 | ← VLOOKUP berhenti di sini |
| 251140010 | CABLE COPPER CORE XLPE… | 54,700,800.00 | nggak pernah dilihat |
Screenshot Sheet2-mu: dua baris, 7,148,400 dan 54,700,800. Klik buat memperbesar.
VLOOKUP ngasih 7,148,400. Dibandingin sama 61,849,200 → Beda. Padahal 7,148,400 + 54,700,800 = 61,849,200. Datamu bener, VLOOKUP-nya yang cuma baca separuh.
Bentuk B — satu No PR dipakai banyak item
Contohnya PR 251140049 (screwdriver, hex wrench, multimeter, dll). Semua baris itu nyari No PR yang sama, jadi VLOOKUP selalu ngasih harga item pertama. Yang cocok cuma item yang harganya kebetulan sama dengan item pertama. Sisanya Beda semua.
Jadi yang kamu bilang “sumber e 1 baris tapi no PR e podo kabeh, dobel” itu bener. Itu memang penyebabnya. VLOOKUP nggak bisa dipakai kalau kuncinya (No PR) nggak unik.
Langkah 4
Rumus pengganti
Karena No PR nggak unik, pertanyaannya kita ganti. Bukan lagi “baris pertama PR ini harganya berapa?”, tapi:
- No PR ini ada nggak di Sheet2?
- Di antara baris-baris PR ini, ada yang harganya sama nggak? (buat Bentuk B)
- Kalau nggak ada, jumlah semua baris PR ini sama nggak? (buat Bentuk A)
Copy ini ke K3, Enter, lalu dobel-klik kotak kecil di pojok sel biar turun ke bawah:
=IF(COUNTIF(Sheet2!B:B,B3)=0,"Kode Tidak Ditemukan",IF(COUNTIFS(Sheet2!B:B,B3,Sheet2!Q:Q,F3)>0,"Sama",IF(ROUND(SUMIF(Sheet2!B:B,B3,Sheet2!Q:Q),2)=ROUND(F3,2),"Sama (total PR)","Beda")))
Kelihatannya panjang, tapi isinya cuma tiga pertanyaan tadi:
| Potongan rumus | Artinya | Hasil |
|---|---|---|
COUNTIF(Sheet2!B:B,B3)=0 | Hitung berapa kali No PR ini muncul di Sheet2. Kalau nol → | Kode Tidak Ditemukan |
COUNTIFS(Sheet2!B:B,B3,Sheet2!Q:Q,F3)>0 | Hitung baris yang No PR-nya sama dan Total harga-nya sama. Kalau ada → | Sama |
SUMIF(Sheet2!B:B,B3,Sheet2!Q:Q) = F3 | Jumlahin semua Total harga PR ini. Kalau sama dengan harga di sheet-mu → | Sama (total PR) |
| selain itu | PR-nya ada, tapi harganya nggak ketemu di mana pun → | Beda |
Dites ke contohmu:
| NO PR | ITEM | HARGA TOTAL | Hasil rumus baru |
|---|---|---|---|
| 251040049 | Pipa Mild steel 6" | 4,036,800.00 | Sama |
| 251140010 | Cable copper | 61,849,200.00 | Sama (total PR) |
Dua hal kecil:
Sheet2!Q:Qitu kolom Total harga, sama seperti di rumusmu (kolom ke-16 dari B). Kalau kolomnya pernah digeser, ganti hurufnya.- Kalau Excel nolak rumusnya, coba ganti semua koma
,jadi titik koma;. Tapi karena rumus lamamu pakai koma dan jalan, harusnya aman.
Langkah 5
Kalau masih ada yang aneh
Yang tersisa sekarang harusnya dikit, dan itu yang beneran perlu kamu lihat. Urutan ngeceknya:
Masih “Kode Tidak Ditemukan”
- No PR-nya kosong. Bekas sel merge: habis di-unmerge, cuma sel paling atas yang ada isinya. Isi No PR di baris bawahnya.
- Ada spasi atau enter nyelip di No PR (ini yang kemarin kamu temuin di sel yang di-wrap text). Tes di sel kosong:
=LEN(B3). No PR-mu 9 digit, jadi kalau hasilnya bukan 9, ada karakter nyelip. Ketik ulang No PR-nya. - Memang nggak ada di Sheet2. Tes: Ctrl+F di Sheet2, cari nomornya.
Masih “Beda”
- Harga di sheet-mu gabungan sebagian item, bukan satu item dan bukan total satu PR. Rumus nggak bisa nebak ini. Filter No PR-nya di Sheet2, cek manual.
- Selisih desimal. Misal di sheet-mu 4,029,755.10 tapi di Sheet2 aslinya 4,029,755.104. Klik selnya, lihat angka asli di formula bar.
- Memang beda. Nah, ini temuan yang sebenernya kamu cari.
Satu batasan yang perlu kamu tahu: rumus ini ngecek “harga ini ada nggak di PR itu”, bukan “item ini harganya segini”. Soalnya nama item di sheet-mu beda tulisan sama di Sheet2 (misal “Pipa Mild steel 6" 6000 MM SCH 40” vs “PIPA MILD STEEL 6" 6000 MM SCH40”), jadi nama item nggak bisa dijadiin kunci. Buat ngecek harga, ini sudah cukup.
Ringkasan
- Klik
K3. Kalau rumusnya nyebut baris 2, itu geser. Angka baris di rumus harus sama dengan baris tempat rumusnya. - VLOOKUP cuma baca baris pertama yang cocok. Jangan dipakai kalau kuncinya bisa dobel.
- Kunci dobel → pakai
COUNTIFS(ada nggak?) danSUMIF(jumlahnya berapa?).
Kalau ada yang masih bingung, kirim screenshot sel + formula bar-nya ya.