Rekap Penjualan 4.200 Baris dengan #N/A, Sel Gabungan, dan Cetak A4
Setiap awal bulan seorang operator merekap penjualan tiga cabang. Data mentah diekspor dari aplikasi kasir ke satu lembar: 4.200 baris, baris pertama berisi nama perusahaan sehingga judul kolom tidak berada di baris satu, nama cabang digabung (merge) untuk 12 baris sekaligus, dan kolom harga terbaca sebagai teks karena ada spasi di belakang angka serta pemisah ribuan berupa titik. Rumus =VLOOKUP(A2;Daftar_Harga!A2:B90;2;0) menghasilkan #N/A pada 37 baris, sementara beberapa baris lain menampilkan harga yang salah karena kode barang tercatat ganda di tabel harga. Pimpinan meminta rekap total per cabang dan per bulan, grafik tren enam bulan, serta hasil cetak rapi di kertas A4 untuk dijilid, esok pagi. Berkas disimpan sebagai .xlsx di folder bersama dan akan dipakai lagi bulan depan dengan pola data yang sama.
1. Jelaskan kemungkinan penyebab #N/A dan harga yang salah pada rumus VLOOKUP tersebut, serta urutan langkah diagnosis yang Anda lakukan sebelum memperbaiki data.
Read the worked answer
#N/A berarti kunci tidak ditemukan, sedangkan harga salah berarti kunci ditemukan tetapi baris yang terambil keliru. Penyebab umum: (1) ada spasi atau karakter tak terlihat pada kode barang - gunakan TRIM dan CLEAN, bandingkan panjang dengan LEN; (2) tipe data berbeda, kode di data berupa teks sedangkan di tabel harga berupa angka atau sebaliknya - periksa dengan ISTEXT dan ISNUMBER; (3) argumen keempat bukan 0/FALSE sehingga pencarian bersifat mendekati; (4) rentang tabel tidak absolut ($A$2:$B$90) sehingga bergeser ketika rumus disalin ke bawah; (5) kode ganda di tabel harga, dan VLOOKUP selalu mengambil kecocokan pertama sehingga harga bisa berasal dari baris lama.
Urutan diagnosis: hitung berapa kode yang tidak ditemukan dengan COUNTIF terhadap tabel harga, periksa beberapa contoh secara manual, lalu cek duplikat dengan COUNTIF > 1. Jika duplikat memang ada, tentukan aturan bisnis mana yang berlaku (misalnya harga terbaru) dan selesaikan dengan XLOOKUP atau INDEX-MATCH yang eksplisit, atau bersihkan tabel referensi agar unik. Jangan menambal dengan IFERROR sebelum penyebab diketahui, karena harga yang salah akan tersembunyi.
2. Pimpinan meminta rekap per cabang dan per bulan serta grafik tren. Jelaskan bagaimana Anda merapikan data mentah agar bisa diolah PivotTable, dan bagaimana memverifikasi hasilnya.
Read the worked answer
PivotTable tidak bisa membaca sel gabungan, baris judul ganda, atau baris kosong. Langkah restrukturisasi pada salinan data mentah: (1) hapus baris nama perusahaan di atas dan jadikan baris pertama sebagai header tunggal dengan nama kolom unik; (2) pilih kolom cabang yang digabung, klik Unmerge, sorot sel kosong, lalu isi ke bawah dengan nilai di atasnya (Ctrl+Enter) agar setiap baris membawa nama cabangnya; (3) ubah kolom harga dari teks ke angka - hilangkan spasi dengan TRIM, buang pemisah ribuan dengan Find & Replace atau SUBSTITUTE, lalu tempel sebagai Values dan pastikan dengan ISNUMBER; (4) bersihkan baris kosong dan baris total di tengah data karena akan terhitung ganda; (5) ubah rentang menjadi Tabel (Ctrl+T) agar pivot ikut memperbarui saat data ditambah.
Setelah itu buat PivotTable: baris = Cabang dan Bulan, nilai = SUM penjualan; grafik tren enam bulan dibuat dari PivotChart. Verifikasi dengan SUMIFS pada data asli untuk dua atau tiga kombinasi cabang-bulan. Jika angkanya berbeda, hampir pasti masih ada baris ganda, nilai teks, atau sel gabungan yang belum diisi.
3. Susun rencana kerja dan pengaturan cetak agar hasil A4 rapi untuk dijilid esok pagi, termasuk cara memastikan angka yang dicetak sudah benar.
Read the worked answer
Persiapan cetak: Page Layout > Size A4, orientasi menyesuaikan jumlah kolom (umumnya Landscape untuk rekap bulanan), margin sempit, lalu Scale to Fit Width = 1 page dengan tinggi otomatis. Pada Sheet > Print Titles, tetapkan baris header agar terulang di setiap halaman, dan batasi Print Area hanya pada tabel rekap, bukan seluruh lembar. Gunakan Page Break Preview untuk menempatkan pemisah agar satu cabang tidak terpotong di tengah.
Sebelum mencetak, verifikasi: pastikan tidak ada lagi #N/A atau sel kosong pada kolom kunci, bandingkan total pivot dengan SUMIFS dari data mentah, dan cocokkan jumlah baris yang terekap dengan jumlah transaksi sumber. Cetak satu halaman uji untuk memeriksa teks terpotong dan header terbaca.
Urutan kerja karena tenggat esok pagi: bersihkan data, susun pivot, verifikasi angka, baru atur cetak - bukan mencetak lebih dulu lalu memperbaiki. Simpan berkas Excel bertanggal dan ekspor versi PDF sebagai arsip cetak.