Contoh Laporan Penjualan Harian Excel dengan Rumus Otomatis

Laporan penjualan harian Excel yang otomatis cuma butuh dua sheet: satu buat ngetik transaksi, satu buat ringkasan yang narik angkanya sendiri pakai SUMIFS. Begitu disetel, kerjaan harianmu tinggal ngetik baris baru.

Yang bikin rekap harian makan waktu itu bukan ngetiknya. Yang makan waktu itu ngitung ulang total, bikin perbandingan sama kemarin, dan nyalin angkanya ke format laporan.

Tiga hal itu bisa dikerjain rumus. Aku bakal bangun sheet-nya dari kosong pakai data Toko Amanah, minimarket 1 pintu di Depok.

Struktur file kayak gimana yang bikin laporannya otomatis?

Kuncinya misahin data mentah dari tampilan laporan. Data mentah cuma nambah ke bawah dan nggak pernah diformat cantik. Sheet laporan yang ngambil angka dari situ pakai rumus, dan itu yang diprint atau dikirim ke pemilik toko.

Tiga sheet yang dipakai:

Sheet	Isi	Diisi kapan
Transaksi	Satu baris satu penjualan	Tiap ada transaksi, atau input massal tiap tutup toko
Produk	Kode, nama, kategori, harga jual, harga pokok	Sekali di awal, update kalau harga berubah
Laporan	Semua rumus ringkasan	Nggak pernah diisi manual

Sheet Transaksi punya kolom ini:

A: tanggal        (format tanggal, bukan teks)
B: jam
C: no_struk
D: kode_produk
E: nama_produk    (otomatis dari XLOOKUP)
F: qty
G: harga_satuan   (otomatis dari XLOOKUP)
H: total          (otomatis, =F*G)
I: harga_pokok    (otomatis dari XLOOKUP)
J: laba_kotor     (otomatis, =H-(F*I))
K: metode_bayar

Yang diketik manual cuma kolom A, B, C, D, F, dan K. Enam kolom sisanya ngitung sendiri.

Langkah pertama sebelum apa-apa: blok data di sheet Transaksi lalu tekan Ctrl + T. Di kotak Table Design, ganti namanya jadi Trx. Ini yang bikin rumusmu otomatis ikut melar pas ada baris baru.

Lakuin hal yang sama di sheet Produk, kasih nama Prd.

Rumus apa aja yang bikin kolom otomatis terisi?

Tiga kolom di sheet Transaksi diisi dari sheet Produk pakai XLOOKUP. Ketik di baris kedua, lalu Excel bakal nyalin sendiri ke baris baru karena datanya udah berbentuk Table.

E2 =IFERROR(XLOOKUP([@kode_produk];Prd[kode];Prd[nama]);"kode tidak ada")

G2 =IFERROR(XLOOKUP([@kode_produk];Prd[kode];Prd[harga_jual]);0)

I2 =IFERROR(XLOOKUP([@kode_produk];Prd[kode];Prd[harga_pokok]);0)

H2 =[@qty]*[@harga_satuan]

J2 =[@total]-([@qty]*[@harga_pokok])

Kolom E sengaja ngeluarin tulisan "kode tidak ada" kalau kodenya salah ketik. Ini jauh lebih berguna dari pesan error N/A, karena kasirmu langsung tau harus ngapain.

Kalau Excel kamu versi 2019 ke bawah, XLOOKUP belum ada. Gantinya:

E2 =IFERROR(INDEX(Prd[nama];MATCH([@kode_produk];Prd[kode];0));"kode tidak ada")

Kombinasi INDEX dan MATCH lebih tahan banting dari VLOOKUP karena nggak rusak kalau ada kolom disisipin. Penjelasan lengkapnya ada di panduan INDEX MATCH.

Gimana cara bikin ringkasan harian yang ngitung sendiri?

Di sheet Laporan, taruh sel tanggal di B1. Semua rumus di bawahnya ngacu ke sel itu, jadi ganti satu sel doang bikin seluruh laporan berubah.

B1 =TODAY()

Kalau kamu mau lihat kemarin, ketik =TODAY()-1. Atau ketik tanggalnya manual kalau mau lihat hari tertentu.

Blok ringkasannya:

Baris	Rumus
Total omzet	=SUMIFS(Trx[total];Trx[tanggal];$B$1)
Jumlah struk	=COUNT(UNIQUE(FILTER(Trx[no_struk];Trx[tanggal]=$B$1)))
Jumlah item terjual	=SUMIFS(Trx[qty];Trx[tanggal];$B$1)
Rata-rata per struk	=IFERROR(B3/B4;0)
Laba kotor	=SUMIFS(Trx[laba_kotor];Trx[tanggal];$B$1)
Margin kotor	=IFERROR(B7/B3;0)
Omzet kemarin	=SUMIFS(Trx[total];Trx[tanggal];$B$1-1)
Perubahan	=IFERROR((B3-B9)/B9;0)

Rumus jumlah struk pakai UNIQUE dan FILTER, dua fungsi array dinamis yang cuma ada di Microsoft 365 dan Excel 2021 ke atas. Buat versi lama, pakai ini:

=SUMPRODUCT((Trx[tanggal]=$B$1)/COUNTIFS(Trx[no_struk];Trx[no_struk];Trx[tanggal];$B$1))

Rumus itu ngitung nomor struk unik tanpa fungsi array baru. Agak berat kalau datanya di atas 20 ribu baris, tapi jalan.

Ringkasan per kategori

Di bawah blok utama, taruh daftar kategori di kolom A, lalu:

B14 =SUMIFS(Trx[total];Trx[tanggal];$B$1;Trx[kategori];$A14)
C14 =IFERROR(B14/$B$3;0)

Kolom C ngasih kontribusi tiap kategori dalam persen. Format selnya jadi persentase, satu angka desimal.

Ringkasan metode bayar

=SUMIFS(Trx[total];Trx[tanggal];$B$1;Trx[metode_bayar];$A20)

Ini penting buat rekonsiliasi kas. Angka tunai di laporan harus cocok sama uang di laci kasir waktu tutup toko.

Contoh hasil: laporan harian Toko Amanah

Ini isi sheet Laporan Toko Amanah tanggal 15 Juli 2025, dari 214 baris transaksi hari itu.

Ringkasan	Nilai
Total omzet	Rp 8.412.600
Jumlah struk	142
Item terjual	487
Rata-rata per struk	Rp 59.243
Item per struk	3,4
Laba kotor	Rp 1.128.300
Margin kotor	13,4%
Omzet kemarin	Rp 7.891.400
Perubahan	+6,6%

Pecahan per kategori:

Kategori	Omzet	Kontribusi	Margin
Sembako	Rp 4.108.200	48,8%	8,1%
Minuman	Rp 1.874.500	22,3%	18,9%
Snack	Rp 1.216.800	14,5%	21,4%
Perawatan	Rp 892.400	10,6%	16,2%
Rokok	Rp 320.700	3,8%	4,2%

Yang keliatan dari tabel ini, dan nggak bakal keliatan dari angka omzet doang:

Sembako nyumbang 48,8% omzet tapi cuma 29,5% laba kotor. Marginnya 8,1%, paling tipis kedua setelah rokok. Kategori ini penting buat narik orang masuk, bukan buat untung.

Snack cuma 14,5% omzet tapi marginnya 21,4%. Kalau kontribusi snack naik dari 14,5% ke 18%, laba kotor harian nambah sekitar Rp 89.000, atau Rp 2,67 juta sebulan, tanpa nambah pelanggan.

Toko Amanah mindahin rak snack dari lorong belakang ke sisi antrean kasir. Dalam 30 hari, kontribusi snack naik ke 17,8% dan margin kotor total naik dari 13,4% ke 14,9%.

Angka yang nemuin peluang itu kolom margin per kategori. Tiga rumus SUMIFS.

Cara nambahin peringatan otomatis

Dua peringatan yang paling kepake buat toko harian.

Omzet di bawah target

Taruh target harian di sel E1, lalu:

=IF($B$3<$E$1;"Di bawah target, kurang "&TEXT($E$1-$B$3;"Rp #.##0");"Target tercapai")

Produk yang stoknya mau habis

Kalau sheet Produk kamu punya kolom stok dan stok minimum:

=TEXTJOIN("; ";TRUE;IF(Prd[stok]<=Prd[stok_min];Prd[nama];""))

Di Excel 2019 ke bawah, rumus ini harus dimasukin pakai Ctrl + Shift + Enter.

Buat nandain baris omzet yang turun pakai warna merah otomatis, pakai conditional formatting dengan aturan berbasis rumus.

Kesalahan umum

Kolom tanggal kesimpen sebagai teks

Ini penyebab nomor satu SUMIFS keluar nol. Cek perataan selnya. Tanggal asli rata kanan. Kalau rata kiri, perbaiki lewat Data lalu Text to Columns lalu pilih format DMY.

Jam ikut kesimpen di kolom tanggal

Kalau selnya isinya 15/07/2025 14:32, Excel nganggap itu angka yang beda dari 15/07/2025. SUMIFS-nya bakal ngasih nol. Pisahin jam ke kolom sendiri, atau pakai kriteria rentang: ">="&$B$1 dan "<"&$B$1+1.

Bikin sheet baru tiap hari

Ini kebiasaan yang bikin data kamu nggak bisa dianalisis. Semua transaksi masuk ke satu sheet, dibedain lewat kolom tanggal. Sheet Laporan yang ganti tampilan, bukan datanya.

Format cantik di sheet data mentah

Merge cell, baris kosong pemisah, dan subtotal di tengah data bikin rumus dan pivot table rusak. Simpen semua kecantikan di sheet Laporan.

Rumus pakai seluruh kolom

Nulis SUMIFS(H:H;A:A;B1) maksa Excel baca sejuta baris tiap kali ngitung. Pakai referensi Table kayak Trx[total], jauh lebih ringan.

Nggak pernah backup

Simpan salinan tiap akhir bulan sebagai file terpisah. Satu file rusak tanpa backup bisa ngilangin catatan setahun.

FAQ

Kenapa SUMIFS-ku hasilnya nol padahal datanya ada?

Penyebab paling sering, kolom tanggal di data mentah kesimpen sebagai teks, bukan tanggal. Cek dengan lihat perataannya. Tanggal asli rata kanan, teks rata kiri. Perbaikinya pakai Text to Columns lalu pilih format Date, atau blok kolomnya dan pakai Data lalu Text to Columns dengan pilihan DMY. Penyebab kedua, ada spasi di belakang teks kriteria, jadi bungkus kriterianya pakai TRIM.

Gimana biar tanggal laporannya ganti otomatis tiap hari?

Pakai rumus TODAY di sel tanggal laporan. Rumus itu ngasih tanggal hari ini dan ikut berubah tiap file dibuka. Kalau kamu mau lihat kemarin, tulis TODAY dikurangi 1. Tapi hati-hati kalau file-nya kamu simpan sebagai arsip, karena angkanya ikut berubah. Buat arsip, salin hasilnya lalu tempel sebagai nilai pakai Paste Special.

Pakai VLOOKUP atau XLOOKUP buat narik harga produk?

Pakai XLOOKUP kalau Excel-mu versi Microsoft 365 atau Excel 2021 ke atas. XLOOKUP bisa nyari ke kiri, nggak rusak kalau kolom disisipin, dan punya argumen bawaan buat nilai yang nggak ketemu. Kalau kamu masih pakai Excel 2016 atau 2019, XLOOKUP nggak tersedia, jadi pilihannya VLOOKUP atau kombinasi INDEX dan MATCH yang lebih tahan perubahan struktur.

Rumusnya jadi lambat pas data udah ribuan baris, gimana?

Tiga hal yang paling bikin berat. Pertama, rentang seluruh kolom kayak A titik dua A. Ganti pakai referensi Table biar Excel cuma baca baris terisi. Kedua, rumus volatil kayak TODAY, NOW, dan OFFSET yang ngitung ulang tiap ada perubahan sekecil apa pun. Ketiga, conditional formatting yang dipasang ke ribuan sel. Kalau masih berat juga, pindah ke pivot table buat bagian ringkasannya.

Laporan ini bisa dipakai di Google Sheets?

Bisa, hampir semua rumusnya sama. SUMIFS, COUNTIFS, TODAY, dan IFERROR jalan persis sama di Google Sheets. XLOOKUP juga udah didukung. Bedanya di Google Sheets kamu punya QUERY yang bisa gantiin beberapa rumus sekaligus, dan Apps Script kalau mau kirim ringkasan otomatis lewat email tiap sore. Struktur dua sheet yang sama tetap dipakai.

Penutup

Tiga langkah biar laporanmu jalan sendiri mulai besok:

1. Pisahin sheet Transaksi dari sheet Laporan, lalu ubah dua-duanya jadi Table pakai Ctrl + T.
2. Taruh satu sel tanggal di sheet Laporan, dan ikat semua SUMIFS ke sel itu. Ganti tanggalnya, seluruh laporan ikut berubah.
3. Tambahin kolom margin per kategori. Di Toko Amanah, kolom itu yang nunjukin snack cuma 14,5% omzet tapi marginnya 21,4%.

Kalau kamu mau kerangka yang udah jadi tinggal isi, ambil dari template laporan penjualan Excel. Sintaks lengkap argumen SUMIFS ada di halaman fungsi SUMIFS dan dokumentasi resmi Microsoft.