Latihan Excel untuk admin dan pelaporan
Cara Membuat Rekap Penjualan di Excel: dari Transaksi Harian sampai Laporan yang Bisa Dicek
Bayangkan setiap sore kamu menerima file transaksi dari dua cabang. Atasan tidak membutuhkan daftar ratusan baris; ia hanya ingin tahu berapa total penjualan, cabang mana yang lebih besar, dan produk apa yang paling banyak menyumbang nilai transaksi. Masalahnya, file mentah sering membuat angka terlewat atau terhitung dua kali.
Banyak orang langsung membuat grafik ketika diminta laporan. Padahal sebelum tampilannya bagus, angka dasarnya harus bisa ditelusuri dan dibuktikan. Di panduan ini kamu akan menyusun data penjualan, menghitung nilai setiap transaksi, membuat rekap dengan SUMIFS, mencoba alternatif PivotTable, lalu memeriksa hasilnya dengan perhitungan manual.
Jawaban cepat: gunakan satu baris untuk satu transaksi, kolom angka yang benar-benar numerik, rumus jumlah × harga, lalu SUMIFS untuk menjumlahkan menurut cabang atau produk. Untuk ringkasan yang mudah diganti-ganti sudut pandangnya, pertimbangkan PivotTable. Kita gunakan enam transaksi contoh agar setiap hasilnya bisa kamu periksa sendiri.
Mulai latihan rekap penjualan ↓Sebelum menghitung, pahami perbedaan data transaksi dan rekap
Data transaksi mencatat kejadian satu per satu: tanggal, cabang, produk, jumlah barang, dan harga. Sementara rekap merangkum kejadian itu menjadi jawaban untuk pertanyaan tertentu, misalnya “berapa nilai penjualan cabang Jakarta?” atau “berapa total produk Buku?”.
Kuncinya adalah struktur: satu baris mewakili satu transaksi contoh, satu kolom mewakili satu jenis data, dan header hanya ada satu kali. Jangan menyisipkan subtotal di antara baris transaksi, karena subtotal tersebut dapat terhitung ulang ketika kamu membuat rekap keseluruhan.
Tiga konsep sebelum memilih rumus
- Nilai transaksi: kuantitas barang dikalikan harga satuan.
- Total keseluruhan: jumlah semua nilai transaksi, tanpa menerapkan filter cabang.
- Total bersyarat: jumlah nilai transaksi yang memenuhi satu atau beberapa kriteria; inilah tugas SUMIFS.
1. Siapkan enam transaksi penjualan contoh
Buka workbook kosong. Pada baris 1, buat enam header: Tanggal (A), Cabang (B), Produk (C), Qty (D), Harga Satuan (E), Total (F). Isi A2:E7 seperti tabel berikut. Kolom Total adalah hasil yang diharapkan, bukan angka yang harus kamu ketik satu per satu.
| Baris | Tanggal (A) | Cabang (B) | Produk (C) | Qty (D) | Harga (E) | Total (F) |
|---|---|---|---|---|---|---|
| 2 | 1 Okt | Jakarta | Buku | 2 | 15000 | 30000 |
| 3 | 1 Okt | Bandung | Pulpen | 3 | 8000 | 24000 |
| 4 | 2 Okt | Jakarta | Pulpen | 4 | 8000 | 32000 |
| 5 | 2 Okt | Jakarta | Buku | 1 | 15000 | 15000 |
| 6 | 3 Okt | Bandung | Buku | 2 | 15000 | 30000 |
| 7 | 3 Okt | Jakarta | Map | 5 | 5000 | 25000 |
Angka pada kolom Qty dan Harga harus tersimpan sebagai angka: masukkan 15000, bukan mengetik “Rp15.000” sebagai teks. Kamu boleh memakai format mata uang setelahnya. Untuk kolom tanggal, masukkan tanggal Excel sungguhan dengan pengaturan wilayah yang sesuai; tampilan “1 Okt” di tabel ini hanya ringkasan visual.
- Di F2 ketik
=D2*E2; seharusnya menghasilkan 30000. - Salin rumus F2 ke F7. Periksa nilai tiap transaksi dengan tabel di atas.
- Di H1 tulis Total pemeriksaan, lalu di H2 ketik
=SUM(F2:F7); total keseluruhan harus 156000 (Rp156.000). Posisi H2 sengaja di luar tabel transaksi agar baris 8 tersedia untuk data baru. - Untuk memakai Excel Table, pilih A1:F7 saja (jangan ikutkan H1:H2), tekan Ctrl+T, centang “My table has headers”, lalu konfirmasi. Cara ini membuat filter dan penambahan baris lebih mudah.
Mengapa total pemeriksaan diletakkan di H2? Karena itu bukan transaksi baru. Jika baris total menjadi bagian data sumber, hasil rekap bisa terhitung dua kali. Pada dataset besar, sebaiknya letakkan total pemeriksaan terpisah dari tabel transaksi atau gunakan fitur Total Row bawaan Table.
2. Membuat rekap per cabang dan produk dengan SUMIFS
Sekarang bayangkan atasan bertanya: “Dari Rp156.000 itu, berapa yang berasal dari Jakarta?” Menggunakan SUM biasa tidak cukup untuk memilih satu cabang. SUMIFS memungkinkan kita menjumlahkan satu kolom berdasarkan syarat di kolom lain.
Total cabang Jakarta
=SUMIFS($F$2:$F$7,$B$2:$B$7,"Jakarta")
Artinya: jumlahkan F2:F7, tetapi hanya untuk baris yang kolom B-nya berisi Jakarta. Hasilnya 102000(Rp102.000).
Total produk Buku di cabang Jakarta
=SUMIFS($F$2:$F$7,$B$2:$B$7,"Jakarta",$C$2:$C$7,"Buku")
Dua syarat harus cocok pada baris yang sama: cabang Jakarta dan produk Buku. Hasilnya 45000 dari transaksi baris 2 dan 5 (30.000 + 15.000).
Dalam SUMIFS, urutannya adalah rentang angka yang akan dijumlahkan, kemudian pasangan rentang kondisi dan nilainya. Semua rentang harus memiliki ukuran yang sama. Jika Excel menggunakan titik koma sebagai pemisah argumen, gunakan misalnya=SUMIFS($F$2:$F$7;$B$2:$B$7;"Jakarta").
| Ringkasan | Hasil yang benar | Cara cek manual |
|---|---|---|
| Total seluruh transaksi | Rp156.000 | 30 + 24 + 32 + 15 + 30 + 25 ribu |
| Jakarta | Rp102.000 | 30 + 32 + 15 + 25 ribu |
| Bandung | Rp54.000 | 24 + 30 ribu |
| Jakarta, produk Buku | Rp45.000 | 30 + 15 ribu |
| Seluruh produk Buku | Rp75.000 | 30 + 15 + 30 ribu |
3. Kapan lebih baik memakai PivotTable?
SUMIFS cocok ketika bentuk laporan sudah jelas, misalnya satu baris untuk satu cabang. Tetapi kalau kamu sering ditanya “coba lihat berdasarkan produk”, “sekarang per cabang”, atau “gabungkan keduanya”, PivotTable membantu mengubah sudut pandang laporan dengan drag-and-drop.
- Pilih satu cell di data transaksi A1:F7, lalu buka Insert → PivotTable. Pastikan sumbernya hanya data transaksi, bukan H2 sebagai total pemeriksaan.
- Letakkan Cabang di Rows dan Total di Values.
- Periksa agar agregasi Values adalah Sum of Total, bukan Count of Total. Jika Excel menghitung Count, periksa apakah kolom Total tersimpan sebagai teks.
- Hasil cabang seharusnya Jakarta 102000, Bandung 54000, dan Grand Total 156000.
- Jika ingin memeriksa kombinasi cabang dan produk, letakkan Produk di bawah Cabang pada Rows. Jika data sumber diperbarui, pastikan data baru termasuk dalam sumber PivotTable, lalu gunakan Refresh untuk memperbarui hasilnya.
PivotTable tidak otomatis memperbaiki input yang salah. Rumus dan PivotTable harus menghasilkan total yang sama bila sumber serta aturannya sama. Pilih alat berdasarkan pekerjaan, bukan karena fiturnya terlihat paling canggih.
4. Pemeriksaan sebelum rekap dikirim ke atasan
Misalnya jumlah transaksi bertambah besok. Kalau kamu memasukkan baris baru tetapi rumus SUMIFS masih berhenti di F7, sebagian penjualan akan hilang dari laporan. Itulah alasan memeriksa cakupan sumber datasama pentingnya dengan memilih rumus yang benar.
- Rekonsiliasi total: Jakarta Rp102.000 + Bandung Rp54.000 harus sama dengan keseluruhan Rp156.000.
- Tipe data: jumlah, harga, dan total harus numerik. “Rp15000” yang diketik sebagai teks bisa membuat rumus atau PivotTable keliru.
- Data dobel: periksa nomor transaksi asli jika tersedia; contoh ini tidak memiliki ID transaksi, jadi jangan sembarang menghapus baris hanya karena produk dan nilainya sama.
- Rentang: ketika baris transaksi baru ditambahkan, perbarui cakupan SUMIFS dan rekap. Referensi terstruktur Excel Table dapat membantu membuat rumus lebih tahan terhadap pertambahan baris.
- Periode dan filter: pastikan laporan mencakup tanggal yang benar; jangan mengira baris yang tersembunyi oleh filter selalu tidak dihitung oleh SUM atau SUMIFS.
Tantangan mandiri 5 menit
Tambahkan transaksi baru di baris 8: cabang Bandung, produk Map, Qty 2, harga 5000. Nilai transaksinya harus 10000. Setelah semua rentang rekap diperbarui mencakup baris 8 (termasuk rumus pemeriksaan di H2), total seluruh penjualan semestinya menjadi 166000, Bandung 64000, dan Jakarta tetap 102000. Jangan memasukkan baris total pemeriksaan lama sebagai transaksi.
Kembali ke hub tutorial Exceluntuk memilih materi sesuai masalah. Jika rekapmu bergantung pada tabel master yang tidak cocok, baca jugacara mengatasi VLOOKUP #N/A.