Bikin Spreadsheet Otomatis: Rumus Excel & Google Sheets yang Wajib Dikuasai

Spreadsheet otomatis dibangun dari tiga kebiasaan: data mentah disimpan rapi di satu sheet (satu baris per transaksi), laporan dibuat di sheet terpisah memakai rumus seperti SUMIFS, XLOOKUP, dan FILTER, dan input dijaga dengan validasi data. Setelah itu, setiap transaksi baru langsung mengubah laporan tanpa perlu menghitung ulang secara manual. Rumus di bawah berlaku untuk Excel 365 dan Google Sheets.

Struktur Data yang Benar

Sheet Transaksi:

• Satu baris satu transaksi, tanpa baris kosong dan tanpa baris total di tengah data.
• Tanggal diisi sebagai tanggal, bukan teks.
• Di Excel, ubah rentang menjadi Table (Ctrl+T) agar rumus otomatis mencakup baris baru.

Rumus Spreadsheet Otomatis yang Wajib Dikuasai

SUMIFS: total dengan beberapa syarat

=SUMIFS(Transaksi!F:F, Transaksi!B:B, "Kopi Susu", Transaksi!C:C, "Bandung")

Total penjualan Kopi Susu di cabang Bandung. Ganti teks dengan referensi sel agar bisa dipakai ulang: =SUMIFS(Transaksi!F:F, Transaksi!B:B, A2, Transaksi!C:C, B$1).

COUNTIFS: menghitung jumlah baris

=COUNTIFS(Transaksi!C:C, "Bandung", Transaksi!A:A, ">="&DATE(2026,11,1))

XLOOKUP: mencari data dari tabel lain

=XLOOKUP(B2, Produk!A:A, Produk!C:C, "Tidak ditemukan")

Mengambil harga dari sheet Produk berdasarkan nama produk. Lebih fleksibel daripada VLOOKUP karena kolom hasil tidak harus di kanan kolom kunci, dan ada nilai cadangan bila tidak ketemu.

IF dan IFS: status otomatis

=IFS(F2>=500000, "Besar", F2>=100000, "Sedang", TRUE, "Kecil")

UNIQUE dan FILTER: daftar dan saringan dinamis

=UNIQUE(Transaksi!B2:B)                          // daftar produk tanpa duplikat
=FILTER(Transaksi!A2:F, Transaksi!C2:C="Bandung")   // semua transaksi Bandung

Hasilnya "tumpah" ke sel di bawahnya dan ter-update otomatis saat data bertambah.

QUERY (khusus Google Sheets)

=QUERY(Transaksi!A:F, "select B, sum(F) where C = 'Bandung' group by B order by sum(F) desc label sum(F) 'Omzet'", 1)

Satu rumus untuk rekap per produk yang diurutkan. Sintaksnya mirip SQL, dijelaskan di belajar database untuk pemula.

Contoh Laporan Bulanan

Sheet Laporan:

• Sel B1: pilihan bulan (validasi data berisi daftar bulan).
• Kolom A: =UNIQUE(Transaksi!B2:B) untuk daftar produk.
• Kolom B: omzet produk di bulan terpilih:

=SUMIFS(Transaksi!F:F, Transaksi!B:B, A2,
        Transaksi!A:A, ">="&B$1,
        Transaksi!A:A, "<"&EDATE(B$1, 1))

• Kolom C: persentase terhadap total: =B2/SUM(B:B), format persen.
• Tambahkan grafik batang dari kolom A dan B.

Untuk mengecek persentase atau selisih pertumbuhan secara cepat, gunakan Kalkulator Persentase. Untuk harga setelah diskon dan PPN, ada Kalkulator Diskon & PPN.

Menjaga Input Tetap Bersih

• Validasi data untuk kolom Produk dan Cabang: daftar pilihan dari sheet master, sehingga tidak ada "Bdg", "bandung", dan "Bandung" yang terhitung berbeda.
• Validasi angka: Qty harus bilangan bulat lebih dari 0.
• Proteksi sheet Laporan agar rumus tidak terhapus tidak sengaja.
• Form sebagai input: di Google Sheets, Google Forms bisa menjadi tempat input transaksi yang langsung masuk ke sheet.

Biarkan AI Menulis Rumus yang Rumit

Kalau kebutuhanmu tidak tercakup di atas, jelaskan struktur kolom ke ChatGPT, Claude, atau Gemini dan minta rumusnya beserta penjelasan. Contoh prompt dan cara mengecek hasilnya ada di analisis data dengan AI.

Kesalahan yang Sering Terjadi

• Menggabungkan sel (merge) di area data, yang merusak rumus dan filter.
• Angka disimpan sebagai teks, misalnya "18.000" dengan titik ribuan yang diketik manual. Rumus menganggapnya bukan angka.
• Rumus diketik ulang di setiap bulan pada sheet berbeda, padahal cukup satu laporan dengan pilihan bulan.
• Referensi absolut dan relatif tertukar. Gunakan $ untuk mengunci baris atau kolom yang tidak boleh bergeser saat rumus disalin.

Unduh Ringkasannya

Simpan versi ringkasnya untuk dibuka saat bekerja: Cheat Sheet Rumus Excel & Google Sheets berisi semua rumus di artikel ini, ditambah rumus teks, tanggal, arti error, dan shortcut dalam 2 halaman PDF. Gratis, cukup masuk dengan akun gratis.

Langkah Berikutnya

Setelah laporan berjalan otomatis, gunakan AI untuk membaca pola dan menemukan temuan penting. Kalau usahamu mulai butuh pencatatan yang lebih serius, baca juga AI untuk bisnis kecil.