KHIZHNYAKOVCara Membuat Laporan Ringkasan dari Data Ekspor di Excel
Laporan dari Data Ekspor — Templat Excel — Lihat template →
Hampir semua sistem akuntansi, penjualan, atau layanan bisa mengekspor data ke Excel. Tetapi data mentah bukan laporan. Atasan atau kantor pusat meminta ringkasan: berapa total per bulan, kategori apa yang tumbuh, mitra mana yang terbesar. Dan setiap bulan seseorang mengerjakan ulang hal yang sama dengan tangan.
Artikel ini menunjukkan cara membangun laporan sekali jadi dari data ekspor, supaya bulan depan Anda cukup menempelkan data baru.
Langkah 1: siapkan lembar data yang bersih
Tempel data ekspor di satu lembar dengan baris judul. Pastikan minimal ada lima kolom: tanggal, kategori, mitra, jumlah, dan nilai. Nama kolom di sistem Anda mungkin berbeda; tidak masalah, yang penting konsisten.
Lalu tambahkan kolom bantu di kanan data (jangan menimpa data asli):
| Kolom bantu | Rumus | |
|---|---|---|
| Bulan | =MONTH(A2) | |
| Tahun | =YEAR(A2) | |
| Kunci | `=D2&" | "&B2` atau kombinasi lain yang Anda butuhkan |
Dengan kolom bulan dan tahun, SUMIFS menjadi sederhana, dan Anda tidak perlu menyaring tanggal berulang kali.
Langkah 2: periksa data sebelum membuat laporan
Laporan yang indah di atas data kotor menyesatkan. Sebelum meringkas, jalankan empat pemeriksaan:
- Tanggal kosong:
=COUNTBLANK(A2:A5001). - Teks sebagai pengganti angka:
=SUMPRODUCT(--ISTEXT(E2:E5001)). Nilai yang tampak seperti angka tetapi bertipe teks tidak ikut dijumlahkan. - Tanggal di luar periode laporan: hitung dengan COUNTIFS terhadap tahun laporan.
- Data ganda:
=SUMPRODUCT((COUNTIFS(A2:A5001,A2:A5001,C2:C5001,C2:C5001,E2:E5001,E2:E5001)>1)*1). Rumus ini berat pada data besar, jadi pakai hanya bila perlu.
Hasil keempatnya sebaiknya ditampilkan di satu lembar pengecekan agar Anda tahu apakah angka laporan layak dipercaya.
Langkah 3: total per bulan dan pertumbuhan
Total nilai bulan tertentu di tahun laporan (tahun di sel B1, nomor bulan di A5):
=SUMIFS(Data!$E:$E,Data!$G:$G,A5,Data!$H:$H,$B$1)
Pertumbuhan terhadap bulan sebelumnya:
=IFERROR(B6/B5-1,"")
Pertumbuhan terhadap bulan yang sama tahun lalu:
=IFERROR(B6/SUMIFS(Data!$E:$E,Data!$G:$G,A6,Data!$H:$H,$B$1-1)-1,"")
Contoh: penjualan Maret Rp180.000.000, Februari Rp150.000.000, Maret tahun lalu Rp200.000.000. Pertumbuhan bulanan +20%, tetapi dibanding Maret tahun lalu -10%. Dua angka itu menceritakan dua kisah berbeda, dan laporan yang baik menampilkan keduanya.
Langkah 4: peringkat kategori dan mitra
Daftar kategori unik dengan UNIQUE (Excel 365), lalu total dengan SUMIFS dan urutkan dengan SORTBY:
=TAKE(SORTBY(UNIQUE(Data!B2:B5001),SUMIFS(Data!E2:E5001,Data!B2:B5001,UNIQUE(Data!B2:B5001)),-1),5)
Pada versi lama, buat daftar kategori manual di lembar acuan, hitung SUMIFS tiap kategori, lalu ambil lima terbesar dengan LARGE dan INDEX/MATCH. Sepuluh mitra teratas dibuat dengan cara yang sama.
Matriks kategori × bulan cukup SUMIFS dengan dua kriteria. Dari matriks ini grafik batang total bulanan dan grafik lingkaran porsi kategori dibuat dalam beberapa klik.
Langkah 5: pilih periode bebas
Atasan sering meminta angka yang tidak ada di laporan baku, misalnya 12 Maret sampai 9 April untuk kategori tertentu. Sediakan sel tanggal awal, tanggal akhir, dan kategori, lalu:
=SUMIFS(Data!$E:$E,Data!$A:$A,">="&B1,Data!$A:$A,"<="&B2,Data!$B:$B,B3)
Satu rumus menjawab permintaan dadakan yang biasanya memakan setengah jam.
Kesalahan yang sering terjadi
- Menimpa data dengan hasil sortir manual. Simpan data asli apa adanya.
- Format tanggal campur aduk. Satu kolom berisi "03/04/2026" dan "2026-04-03" membuat bulan salah. Periksa lewat bagian pengecekan.
- Kategori ejaan beragam. "Alat tulis" dan "alat tulis " (dengan spasi) dianggap berbeda. Gunakan TRIM.
- Rumus menjangkau seluruh kolom pada data puluhan ribu baris. Tetapkan rentang yang jelas supaya berkas tetap ringan.
Templat siap pakai
Templat Excel berbayar ini sudah menyiapkan semuanya. Lembar «Pengaturan» berisi tahun laporan dan nama kolom data ekspor Anda. «Data» menyediakan judul kolom dan 5000 baris untuk menempel data, serta kolom bantu normalisasi (bulan, tahun, kunci). «Ringkasan» menampilkan total per bulan, pertumbuhan terhadap bulan sebelumnya dan bulan yang sama tahun lalu, 5 kategori teratas, 10 mitra teratas, matriks «kategori × bulan», serta grafik batang dan lingkaran. «Pilih Periode» menghitung nilai dan jumlah untuk periode apa pun dan kategori pilihan, sedangkan «Cek Data» menandai tanggal kosong, teks sebagai pengganti angka, tanggal di luar periode, dan data ganda, lengkap dengan penghitung dan penjelasan.
Batasannya: hingga 5000 baris, 30 kategori, dan 200 mitra. Berjalan di Excel 2007 ke atas dan LibreOffice, tanpa makro dan tanpa fungsi baru, berbahasa Indonesia.
FAQ
Apakah semua data ekspor bisa dipakai?
Selama memiliki tanggal, kategori, mitra, jumlah, dan nilai, umumnya bisa. Sesuaikan nama kolom dengan data Anda.
Mengapa total di laporan berbeda dengan sistem?
Biasanya karena data teks, data ganda, atau tanggal di luar periode. Periksa lembar pengecekan lebih dulu.
Berapa baris yang masih nyaman di Excel?
Beberapa puluh ribu baris masih bisa, tetapi rumus berat akan melambat. Bila data jauh lebih besar, agregasikan di sistem sumber.
Perlukah laporan dibuat ulang tiap bulan?
Tidak. Tempel data baru di lembar data, dan seluruh ringkasan serta grafik ikut berubah.
