TL;DR
Tabel laporan penjualan yang benar punya satu baris untuk satu item transaksi, dengan kolom minimal berisi tanggal, nomor transaksi, kode produk, nama produk, kategori, jumlah, harga satuan, diskon, total, dan channel penjualan. Data mentah dipisah dari hasil rekap, dan daftar produk disimpan di sheet master terpisah supaya nama produk nggak ditulis beda-beda. Struktur ini bikin pivot table dan rumus SUMIFS jalan tanpa perlu bersih-bersih dulu.
Tabel laporan penjualan yang benar punya satu baris untuk satu item transaksi, dengan kolom yang tetap dan tipe data yang konsisten dari baris pertama sampai terakhir.
Kedengeran sepele. Tapi struktur kolom yang salah di minggu pertama bakal nagih waktumu tiap akhir bulan, terus-terusan.
Aku pernah lihat file penjualan warung yang isinya 14 sheet, satu sheet per bulan, dengan urutan kolom yang beda tiap sheet. Rekap setahun butuh dua hari kerja. Padahal datanya cuma 8 ribuan baris.
Di bawah ini 10 kolom wajib, aturan penyusunan barisnya, dan cara misahin data mentah dari hasil rekap.
Tabel laporan penjualan yang benar adalah satu tabel datar berisi transaksi mentah, di mana tiap baris mewakili satu item yang terjual dan tiap kolom menyimpan satu jenis informasi. Nggak ada sel gabungan, nggak ada baris kosong, dan nggak ada subtotal nyempil di tengah data. Semua rekap dihitung di sheet lain.
Bentuk ini yang bikin pivot table, SUMIFS, dan grafik jalan tanpa kamu perlu bersih-bersih dulu.
Lawannya adalah tabel yang dibikin buat enak dilihat manusia. Bulan jadi kolom, ada baris total di tengah, kolom kategori digabung pakai merge cell. Rapi di layar, tapi setiap alat analisis bakal nyerah baca datanya.
Sepuluh kolom di bawah ini cukup buat hampir semua usaha kecil. Urutannya sengaja dibikin dari yang paling sering dipakai buat filter di paling kiri.
| Nama kolom | Isi | Format | Contoh |
|---|---|---|---|
| tanggal | Tanggal transaksi | Date | 09/07/2025 |
| no_transaksi | Nomor struk, sama untuk item dalam satu struk | Text | TRX-0912 |
| kode_produk | Kode unik dari sheet master | Text | MG-001 |
| nama_produk | Ditarik otomatis dari master | Text | Minyak Goreng 1L |
| kategori | Ditarik otomatis dari master | Text | Sembako |
| qty | Jumlah unit terjual | Number | 3 |
| harga_satuan | Harga jual per unit saat transaksi | Number | 18500 |
| diskon | Potongan dalam rupiah, isi 0 kalau nggak ada | Number | 1000 |
| total | Hasil rumus, bukan ketikan | Number | 54500 |
| channel | Tempat transaksi terjadi | Text | Toko |
Kolom total diisi rumus, bukan angka ketikan:
=F2*G2-H2Kalau kamu punya lebih dari satu kasir, tambahin kolom kasir. Kalau kamu jualan di beberapa cabang, tambahin cabang. Dua kolom itu murah biayanya sekarang, dan mahal banget kalau baru ditambah setelah data numpuk setahun.
Karena kamu butuh dua angka yang berbeda dari data yang sama: jumlah struk dan jumlah item terjual.
Kalau satu struk berisi tiga produk, tulis tiga baris dengan no_transaksi yang sama. Dari situ kamu bisa hitung dua-duanya. Jumlah struk pakai nomor transaksi unik, jumlah item pakai jumlah baris.
Cara yang salah dan sering kejadian: nulis tiga produk dalam satu sel dipisah koma. Bentuk ini bikin pivot table baca satu kombinasi produk sebagai satu produk. Kamu nggak akan pernah tau berapa banyak minyak goreng yang beneran laku.
Buat ngitung jumlah struk unik di Excel:
=SUMPRODUCT(1/COUNTIF(B2:B5000;B2:B5000))Rumus ini ngitung berapa nilai berbeda ada di kolom nomor transaksi. Di Google Sheets ada versi yang lebih pendek pakai =COUNTUNIQUE(B2:B5000).
Empat aturan yang bikin hidupmu gampang enam bulan lagi:
harga_satuan, bukan Harga Satuan (Rp). Judul laporan taruh di sheet lain.Rp 18.500 sebagai teks. Simpan 18500, terus atur tampilan rupiahnya lewat format sel.9 Juli. Cek cepat: kalau angkanya rata kiri di sel, itu masih teks dan belum kebaca sebagai tanggal.Aturan kedua dan ketiga yang paling sering dilanggar, dan dua-duanya bikin SUMIFS balikin nol tanpa pesan error apa pun.
Bikin sheet kedua namanya master_produk, isinya empat kolom: kode_produk, nama_produk, kategori, harga_normal. Satu baris satu produk, dan kode produk nggak boleh dobel.
Di sheet transaksi, kamu cuma input kode produknya. Nama dan kategori ditarik otomatis:
=XLOOKUP(C2;master_produk!A:A;master_produk!B:B;"kode tidak ditemukan")Buat Excel versi lama yang belum punya XLOOKUP:
=IFERROR(VLOOKUP(C2;master_produk!A:D;2;FALSE);"kode tidak ditemukan")Supaya kode produk nggak salah ketik, pasang dropdown di kolom kode. Blok kolom C, masuk Data, Data Validation, pilih List, arahkan ke kolom kode di sheet master. Cara lengkapnya ada di panduan data validation Excel.
Efeknya besar. Tanpa master, orang bakal ngetik Minyak Goreng 1L, minyak goreng 1 liter, dan Mnyk Goreng 1L di tiga hari berbeda. Pivot table baca itu sebagai tiga produk.
Setelah kolom siap, ubah jadi Table (Excel) atau range bernama (Sheets). Ini yang bikin rumus dan pivot ikut tumbuh waktu data nambah.
tbl_penjualan.rekap. Semua pivot table dan grafik taruh di sini.Keuntungan Table: baris baru yang kamu ketik di bawah otomatis masuk ke tabel, dan pivot table di sheet rekap tinggal di-refresh. Penjelasan resminya ada di dokumentasi Microsoft soal Excel Table.
Di Google Sheets, kamu bisa tarik rekapnya pakai QUERY tanpa pivot sama sekali:
=QUERY(transaksi!A:J;
"select E, sum(I)
where A is not null
group by E
order by sum(I) desc
label sum(I) 'Omzet'";1)Cara kerja fungsinya aku jelasin di fungsi QUERY Google Sheets. Kalau mau kolom total kehitung sendiri sampai baris paling bawah, pakai ARRAYFORMULA.
Data toko_berkah punya Ngulik Data isinya 8.400 transaksi warung kelontong di Semarang selama tiga bulan. Versi awalnya diketik manual tanpa kode produk.
Waktu aku hitung nilai unik di kolom nama produk, hasilnya 412 nama berbeda. Padahal produk yang beneran dijual cuma 142. Sisanya beda ejaan, beda kapital, dan beda singkatan buat barang yang sama.
Artinya hampir 3 dari 10 nama produk di data itu duplikat yang nyamar. Semua laporan produk terlaris yang dibikin dari data itu salah, dan salahnya nggak kelihatan karena angkanya tetap keluar.
| Kondisi | Nama produk unik | Produk asli | Waktu rekap bulanan |
|---|---|---|---|
| Sebelum, ketik manual | 412 | 142 | Sekitar 3 jam |
| Sesudah, pakai kode produk | 142 | 142 | Sekitar 12 menit |
Perbaikannya nggak rumit. Bikin sheet master 142 produk, kasih kode, pasang dropdown di kolom kode, dan nama plus kategori ditarik pakai XLOOKUP.
Keputusan yang berubah setelah datanya bersih: produk terlaris ternyata bukan mie instan seperti yang dikira pemiliknya, tapi minyak goreng kemasan. Nama minyak goreng tadinya kepecah jadi 6 ejaan berbeda, jadi angkanya nggak pernah kelihatan utuh di pivot table.
Bikin satu sheet per bulan. Ini yang paling sering. Dua belas sheet dengan struktur mirip tapi nggak persis sama bikin rekap tahunan jadi kerja manual. Simpan semua di satu sheet, pakai kolom tanggal buat misahin.
Naruh subtotal di tengah data. Baris berisi kata Total di antara transaksi bikin pivot table ngitung angka yang sama dua kali. Semua total taruh di sheet rekap.
Nulis rupiah sebagai teks. Rp 18.500 yang diketik langsung itu teks, bukan angka. SUM-nya bakal balikin 0. Simpan 18500, atur tampilannya lewat format sel.
Nambah kolom di tengah setelah data jalan. Rumus yang nunjuk kolom tertentu bakal geser dan diam-diam salah. Tambahin kolom baru di ujung kanan.
Nggak nyimpen harga saat transaksi. Harga produk berubah tiap beberapa bulan. Kalau kolom harga selalu ditarik dari master, laporan bulan lalu ikut berubah waktu harga naik. Simpan harga jual di kolom transaksi, master cuma buat nilai awalnya.
Minimal sepuluh: tanggal, nomor transaksi, kode produk, nama produk, kategori, jumlah, harga satuan, diskon, total, dan channel penjualan. Sepuluh kolom ini cukup buat jawab hampir semua pertanyaan rekap, mulai dari omzet harian sampai produk terlaris per channel. Kalau usahamu punya kasir lebih dari satu, tambahin kolom nama kasir biar bisa dipakai buat rekap shift.
Jangan. Satu baris harus mewakili satu item dalam satu transaksi. Kalau satu struk berisi tiga produk, tulis tiga baris dengan nomor transaksi yang sama. Cara ini bikin kamu tetap bisa hitung jumlah struk pakai nomor transaksi unik, sekaligus hitung penjualan per produk. Menggabungkan beberapa produk dalam satu sel bikin pivot table nggak bisa mecah datanya.
Karena kalau nama diketik ulang tiap transaksi, ejaannya bakal beda-beda. Minyak Goreng 1L, minyak goreng 1 liter, dan Mnyk Goreng 1L kebaca sebagai tiga produk berbeda oleh pivot table. Dengan sheet master, kamu cukup input kode produk, dan nama plus harga ditarik otomatis pakai VLOOKUP atau XLOOKUP. Satu kali betulin di master, semua laporan ikut bener.
Pakai rumus. Isi kolom total dengan jumlah dikali harga satuan dikurangi diskon, jangan angka ketikan. Kalau diketik manual, kamu nggak punya cara ngecek apakah angkanya cocok sama qty dan harga. Rumus juga bikin koreksi jadi gampang: ubah jumlahnya, totalnya ikut berubah sendiri tanpa kamu perlu inget benerin dua tempat.
Perlu. Sheet data mentah isinya cuma transaksi, satu baris satu item, tanpa baris kosong dan tanpa subtotal di tengah. Rekap bulanan taruh di sheet lain, ambil datanya pakai pivot table atau SUMIFS. Kalau subtotal dicampur di sheet mentah, pivot table bakal ngitung angka yang sama dua kali dan omzetmu kelihatan dobel.
Tiga hal yang paling nentuin:
Coba buka file penjualanmu sekarang, hitung berapa nama produk unik yang ada di sana, terus bandingin sama jumlah produk yang beneran kamu jual. Selisihnya bakal ngasih tau seberapa mendesak kamu butuh sheet master.
Kalau mau langsung pakai kerangka yang udah jadi, ambil dari template laporan penjualan Excel. Buat ngerapiin data lama yang terlanjur dobel, lanjut ke cara hapus duplikat di Excel.
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.