Cara membuat laporan penjualan otomatis di Excel: dari tabel sumber sampai rekap yang ikut berubah sendiri

Peta belajar Excel untuk admin menyebut rekap penjualan bulanan sebagai titik akhir yang wajar, tetapi hanya menyinggungnya dalam satu baris tabel. Artikel ini membongkar baris itu: bagaimana satu berkas penjualan disusun supaya laporannya ikut berubah sendiri setiap kali transaksi baru masuk, tanpa satu rumus pun diedit ulang. Penilaian redaksi di artikel ini berdasarkan sumber yang dicantumkan di bagian akhir.

Otomatis di sini bukan makro dan bukan Power Query, melainkan berkas yang rentangnya tumbuh sendiri, rekapnya menghitung ulang sendiri, dan kolom harganya terisi sendiri, memakai fungsi bawaan yang ada di setiap Excel modern. Semua contoh memakai koma sebagai pemisah argumen; kalau pengaturan wilayah Excel-mu Indonesia, ganti koma dengan titik koma.

Langkah 1: satu tabel sumber, satu baris satu transaksi

Laporan otomatis gagal di tempat yang sama, dan tempat itu bukan rumusnya. Penyebabnya bentuk data sumber: rekap per cabang sudah diketik manual di sheet yang sama, baris subtotal menyelip di tengah, nama bulan jadi judul kolom. Bentuk semacam itu enak dibaca manusia dan mustahil dihitung mesin.

Aturannya satu kalimat: satu baris satu kejadian, satu kolom satu jenis informasi, tanpa sel gabung dan tanpa baris kosong di tengah. Untuk penjualan, bentuk minimumnya seperti ini.

Kolom Total ditulis sebagai rumus, bukan diketik; angka yang diketik tangan berhenti benar pada koreksi pertama. Kalau datamu berasal dari ekspor sistem kasir, pekerjaan merapikannya adalah isi kelas Menata Data di Spreadsheet, dan itu harus selesai sebelum langkah berikutnya.

Langkah 2: jadikan Tabel Excel supaya rentangnya tumbuh sendiri

Ini langkah yang paling sering dilewati dan paling menentukan. Pilih satu sel di dalam data, tekan Ctrl+T, pastikan kotak header tercentang, lalu beri nama tabelnya — misalnya tblPenjualan. Sejak itu Excel memperlakukan blok data sebagai satu objek bernama yang batasnya melebar sendiri setiap ada baris baru.

=SUM(tblPenjualan[Total])
=SUMIFS(tblPenjualan[Total], tblPenjualan[Cabang], "Bandung")

' Versi rentang tetap yang harus diedit setiap kali data bertambah:
=SUM($E$2:$E$5000)
=SUMIFS($E$2:$E$5000, $C$2:$C$5000, "Bandung")

Nama tabel diikuti nama kolom di dalam kurung siku disebut referensi terstruktur. tblPenjualan[Total] berarti seluruh badan kolom Total tanpa header, berapa pun panjangnya sekarang; [@Total] di dalam tabel yang sama berarti sel Total di baris ini saja. Dua bentuk itu menutup hampir semua kebutuhan sehari-hari.

Penilaian redaksi: ini satu-satunya langkah di artikel ini yang hasilnya langsung terasa tanpa menambah satu rumus pun. Dokumentasi Microsoft menempatkan Tabel Excel sebagai fitur dasar, bukan fitur lanjutan, dan sebagian besar laporan yang rusak tiap bulan rusak karena baris barunya jatuh di luar rentang rumus.

Langkah 3: kolom bantu tanggal untuk bulan dan kuartal

Rekap penjualan dipotong per bulan atau per kuartal, sedangkan kolom Tanggal berisi tanggal harian. Ada dua cara menjembataninya dan keduanya sah. Yang pertama menambahkan kolom bantu di dalam tabel sumber.

' Ketik di baris pertama; Excel menyalinnya ke seluruh kolom sendiri
[@Periode]  =TEXT([@Tanggal], "yyyy-mm")
[@Kuartal]  ="Q" & ROUNDUP(MONTH([@Tanggal]) / 3, 0)

TEXT mengubah tanggal jadi teks mengikuti pola di argumen kedua; pola yyyy-mm menghasilkan 2026-09, bentuk yang urut sendiri saat diurutkan menaik. MONTH mengembalikan angka 1 sampai 12 dari sebuah tanggal. ROUNDUP membulatkan ke atas sebanyak desimal di argumen kedua, jadi ROUNDUP(9/3, 0) adalah 3 dan ROUNDUP(10/3, 0) adalah 4 — persis batas kuartal.

Cara kedua tidak memakai kolom bantu dan menyaring langsung dengan batas tanggal. Berguna kalau kamu tidak boleh mengubah tabel sumber karena dipakai orang lain.

' $A5 berisi tanggal 1 di bulan yang direkap, misal 01/09/2026
=SUMIFS(tblPenjualan[Total],
        tblPenjualan[Tanggal], ">=" & $A5,
        tblPenjualan[Tanggal], "<=" & EOMONTH($A5, 0))

EOMONTH mengembalikan hari terakhir sebuah bulan; argumen keduanya jumlah bulan yang digeser, dan 0 berarti bulan yang sama. Perhatikan tanda & yang menyambung operator perbandingan dengan alamat sel: kriteria SUMIFS berbentuk teks, jadi alamat selnya harus disambung, bukan ikut masuk ke dalam tanda kutip.

Penilaian redaksi: untuk laporan bulanan yang akan diwariskan ke staf berikutnya, kolom bantu menang, karena siapa pun bisa menyaring kolom Periode dengan tangan lalu mencocokkannya dengan hasil rumus. Teknik tanggal selengkapnya ada di kelas Mengolah Teks dan Tanggal.

Langkah 4: rekap dengan SUMIFS

Bentuk yang paling sering diminta adalah kisi: periode menurun di kolom A mulai baris 5, cabang melintang di baris 4 mulai kolom B, lalu satu rumus di B5 yang disalin ke seluruh kisi.

' Di sel B5, lalu salin ke kanan dan ke bawah
=SUMIFS(tblPenjualan[Total],
        tblPenjualan[Periode], $A5,
        tblPenjualan[Cabang],  B$4)

' Jumlah transaksinya, bukan nilainya
=COUNTIFS(tblPenjualan[Periode], $A5, tblPenjualan[Cabang], B$4)

Argumen pertama adalah rentang yang dijumlahkan. Sesudahnya berpasangan: rentang kriteria, lalu kriterianya. Pasangan pertama membaca kolom Periode dan mencocokkannya dengan $A5, pasangan kedua membaca kolom Cabang terhadap B$4. SUMIFS menjumlahkan baris yang memenuhi semua pasangan sekaligus, bukan salah satunya. COUNTIFS memakai pola sama tanpa rentang penjumlahan di depan.

Tanda dolar di $A5 dan B$4 adalah bagian yang paling sering salah. $A5 mengunci kolomnya saja: disalin ke kanan rujukannya tetap di kolom A, disalin ke bawah nomor barisnya ikut turun mengikuti periode. B$4 kebalikannya, barisnya terkunci dan kolomnya bergeser mengikuti cabang. Kalau ditulis polos tanpa dolar, seluruh kisi mengambil sel yang salah dan hasilnya tetap terlihat seperti angka wajar.

SUMIFS atau PivotTable

PivotTable menghasilkan rekap serupa tanpa satu pun rumus. Keduanya benar; yang membedakan adalah apa yang terjadi sesudahnya.

Penilaian redaksi: untuk laporan rutin yang bentuknya sudah tetap, SUMIFS lebih mudah dipertanggungjawabkan, karena setiap angka membawa rumusnya sendiri dan tidak ada langkah menyegarkan yang bisa terlupa. Untuk menjelajah data yang belum kamu kenal, PivotTable menang telak.

Langkah 5: menempelkan harga dan nama produk dengan XLOOKUP

Tabel penjualan umumnya hanya menyimpan kode produk; nama dan harga masternya ada di sheet lain. Jadikan daftar master itu Tabel Excel juga, beri nama tblProduk, lalu tulis rumus ini di kolom bantu tblPenjualan.

[@Nama] =XLOOKUP([@Kode], tblProduk[Kode], tblProduk[Nama], "Kode tidak ada", 0)

' Mengambil dua kolom sekaligus, Nama sampai Harga
=XLOOKUP([@Kode], tblProduk[Kode], tblProduk[[Nama]:[Harga]], "Kode tidak ada", 0)

Argumennya berurutan: nilai yang dicari, rentang tempat mencarinya, rentang yang dikembalikan, nilai pengganti bila tidak ketemu, lalu mode pencocokan. Angka 0 di argumen kelima berarti pencocokan persis. Argumen keenam, arah pencarian, jarang dipakai dan boleh dikosongkan.

Penilaian redaksi: argumen keempat diisi sejak rumus pertama ditulis, bukan ditambahkan setelah muncul masalah. Tanpanya, kode yang tidak ditemukan menghasilkan #N/A, dan satu #N/A membuat SUM di bawahnya ikut #N/A sehingga laporan terlihat rusak total padahal yang salah satu baris. Ini yang membedakannya dari VLOOKUP, yang butuh IFERROR terpisah untuk hasil sama.

Langkah 6: memeriksa supaya laporannya tidak diam-diam salah

Laporan otomatis punya bahaya yang tidak dimiliki rekap manual: karena tidak ada yang mengetik ulang angkanya, tidak ada pula yang melihat angkanya. Kesalahan yang memunculkan pesan error mudah ketahuan; yang berbahaya adalah yang menghasilkan angka wajar. Empat rumus kontrol berikut ditaruh sekali di sudut sheet laporan, dan semuanya harus nol.

' 1. Total kisi harus sama dengan total mentah
=SUM(B5:F16) - SUM(tblPenjualan[Total])

' 2. Angka yang diam-diam jadi teks: COUNT hanya menghitung angka
=COUNTA(tblPenjualan[Total]) - COUNT(tblPenjualan[Total])

' 3. Tanggal yang diam-diam jadi teks
=SUMPRODUCT(--ISTEXT(tblPenjualan[Tanggal]))

' 4. Kode yang tidak ada di daftar master
=COUNTIF(tblPenjualan[Nama], "Kode tidak ada")

Rumus pertama paling sering menangkap masalah: kalau selisihnya bukan nol, ada cabang atau periode yang belum punya kolom di kisi, atau nama cabang yang ejaannya berbeda. Rumus kedua memanfaatkan beda COUNTA yang menghitung semua sel terisi dan COUNT yang hanya menghitung angka; selisihnya jumlah sel yang terlihat angka tetapi teks. Rumus ketiga memakai minus ganda untuk mengubah hasil benar-salah jadi 1 dan 0 supaya bisa dijumlahkan.

Langkah 7: merapikan supaya bisa dibaca tanpa dijelaskan

Sampai di sini angkanya sudah benar dan ikut berubah sendiri. Sisanya pekerjaan tampilan, dan seluruhnya dikerjakan lewat toolbar, bukan rumus.

1. Beri seluruh kisi format Akuntansi atau format kustom tanpa desimal. Dua desimal pada angka penjualan hanya melebarkan kolom tanpa menambah informasi.
2. Bekukan judul dengan Freeze Panes, kursor di sel tepat di bawah dan di kanan area judul, supaya laporan panjang tetap terbaca saat digulir.
3. Tambahkan kolom total dan baris total di tepi kisi dengan SUM biasa, lalu tebalkan. Angka itu yang dicari lebih dulu.
4. Pakai format bersyarat untuk perbandingan, bukan hiasan: satu skala warna pada kolom pertumbuhan mengalahkan lima warna tanpa arti tetap.
5. Pasang validasi data pada kolom Cabang dan Kode di sheet sumber, supaya ejaan baru tidak bisa masuk lewat pengetikan.
6. Tulis satu baris keterangan di atas laporan: periode data, kapan terakhir diperbarui, dan dari sheet mana sumbernya.

Bagian tampilan ini dilatih di kelas Format dan Tampilan Laporan Excel, sementara susunan rekap dan rumusnya ada di kelas Rekap dan Laporan Penjualan. Urutannya tidak boleh dibalik: rekap dulu sampai angkanya benar, tampilan sesudahnya. Satu celah yang tersisa, judul cabang di kisi yang tetap harus kamu ketik sendiri, ditutup rumus larik dinamis SORT dan UNIQUE di kelas Rumus Larik Dinamis di Excel.

Cara menguji bahwa laporannya benar-benar otomatis

Tambahkan sepuluh transaksi baru di baris bawah tabel sumber, termasuk satu cabang dan satu kode produk yang sudah ada, lalu buka sheet laporan tanpa menyentuh rumus apa pun. Kalau angkanya berubah dan empat rumus kontrol tetap nol, berkasmu selesai. Kalau ada angka yang tidak bergerak, penyebabnya hampir selalu satu dari tiga: rentang yang belum jadi Tabel Excel, kolom bantu yang tidak menempel ke tabel, atau ejaan cabang yang berbeda dari judul kolom.