Dalam dunia akuntansi, buku besar berfungsi sebagai pusat informasi untuk mencatat semua transaksi yang telah terjadi dan dikelompokkan berdasarkan akun. Bagi pelaku UMKM atau staf administrasi keuangan, memahami cara membuat buku besar otomatis di Excel sangat membantu dalam meningkatkan efisiensi pencatatan dan meminimalisir kesalahan.
Pada artikel ini, kita akan membahas cara membuat buku besar otomatis di Microsoft Excel dengan menarik data dari jurnal umum menggunakan rumus dan kode UDF (User Defined Function). Kita akan mengulas struktur jurnal umum, buku besar, dan bagaimana setiap bagian saling terhubung.
Struktur Jurnal Umum
Jurnal umum berfungsi sebagai tempat awal pencatatan transaksi. Setiap transaksi dicatat berdasarkan tanggal, nomor bukti, keterangan, kode akun, nama akun, dan nilai debet atau kredit.
Sebagai contoh, lihat gambar jurnal umum berikut ini:
Transaksi dicatat secara kronologis, dan terdiri dari:
- Tanggal: Waktu terjadinya transaksi.
- Keterangan: Deskripsi transaksi.
- Kode akun & nama akun: Mengacu pada akun yang terlibat.
- Debet dan Kredit: Nilai transaksi untuk masing-masing akun.
Dengan struktur seperti ini, kita dapat memanfaatkan data di jurnal umum untuk disusun ulang ke dalam buku besar.
Struktur Buku Besar
Buku besar merangkum transaksi dari jurnal umum berdasarkan akun tertentu. Berikut adalah tampilan buku besar otomatis:
Setiap baris diisi dengan informasi:
- Tanggal transaksi
- Nomor bukti
- Keterangan transaksi
- Jumlah debet/kredit
- Saldo berjalan
Untuk cara membuat buku besar otomatis di Excel seperti ini, kita akan menggunakan dua alat utama:
- Fungsi UDF untuk mengambil tanggal dari jurnal umum.
- Fungsi FILTER untuk mengisi kolom debet dan kredit.
Rumus dan UDF untuk Membuat Buku Besar Otomatis
Dalam cara membuat buku besar otomatis ini kita akan menggunakan 2 komponen, yakni:
A. Fungsi UDF: AmbilTanggalTerdekat
Untuk mengisi tanggal transaksi, kita menggunakan fungsi UDF berikut:
Function AmbilTanggalTerdekat(namaAkun As String, rngAkun As Range, rngTanggal As Range, urutanKe As Long) As Variant
Dim arrAkun As Variant, arrTgl As Variant
Dim i As Long, hitung As Long
Dim posBaris As Long
arrAkun = rngAkun.Value
arrTgl = rngTanggal.Value
hitung = 0
posBaris = 0
For i = 1 To UBound(arrAkun, 1)
If arrAkun(i, 1) = namaAkun Then
hitung = hitung + 1
If hitung = urutanKe Then
posBaris = i
Exit For
End If
End If
Next i
If posBaris = 0 Then
AmbilTanggalTerdekat = ""
Exit Function
End If
If arrTgl(posBaris, 1) <> "" Then
AmbilTanggalTerdekat = arrTgl(posBaris, 1)
Else
For i = posBaris - 1 To 1 Step -1
If arrTgl(i, 1) <> "" Then
AmbilTanggalTerdekat = arrTgl(i, 1)
Exit Function
End If
Next i
AmbilTanggalTerdekat = ""
End If
End Function
Penjelasan singkat:
- Fungsi ini mencari tanggal yang relevan untuk akun tertentu berdasarkan urutan kemunculannya.
namaAkun: Nama atau kode akun yang dicari.rngAkun: Rentang kolom akun dari jurnal umum.rngTanggal: Rentang kolom tanggal dari jurnal umum.urutanKe: Kemunculan keberapa dari akun tersebut yang ingin ditampilkan.
Fungsi ini sangat membantu untuk mengambil tanggal yang sesuai secara otomatis untuk setiap baris buku besar. Cara pengaplikasikannya adalah dengan input rumus berikut di sel C12 kemudian salin ke bawah dan ke samping sampai sel E111:
=AmbilTanggalTerdekat($D$5; 'Jurnal Umum'!$E$7:$E$50; 'Jurnal Umum'!B$7:B$50; ROW(A1))Dan berikut adalah hasilnya:
B. Rumus untuk Mengisi Kolom Debet dan Kredit
Rumus berikut digunakan pada kolom Debet dan Kredit di buku besar di sel F12:
=IFERROR(FILTER('Jurnal Umum'!$G$7:$H$50; 'Jurnal Umum'!$E$7:$E$50=D5);"")Dan berikut hasilnya:
Penjelasan struktur rumus:
FILTER: Menyaring data dari kolom G dan H (Debet dan Kredit) berdasarkan kecocokan kode akun.'Jurnal Umum'!$E$7:$E$50=D5: Memastikan bahwa hanya transaksi dengan kode akun yang sama dengan sel D5 (kode akun yang sedang dianalisis) yang ditampilkan.IFERROR: Menghindari error jika tidak ada data yang cocok.
Dengan rumus ini, setiap baris transaksi yang memiliki kode akun yang sama akan muncul secara otomatis di bagian Debet dan Kredit di buku besar.
Keuntungan Membuat Buku Besar Otomatis
Berikut beberapa alasan mengapa cara membuat buku besar otomatis ini sangat disarankan:
- Efisiensi waktu: Data langsung tersaring dari jurnal umum tanpa harus input ulang.
- Mengurangi kesalahan: Tidak ada duplikasi atau ketidaksesuaian antara jurnal dan buku besar.
- Dinamis: Saat jurnal diperbarui, buku besar ikut berubah otomatis.
- Skalabilitas tinggi: Cocok digunakan untuk puluhan hingga ratusan transaksi.
Tips Implementasi
Untuk memaksimalkan cara membuat buku besar otomatis, berikut beberapa tips:
- Gunakan tabel terstruktur di jurnal umum untuk memudahkan referensi.
- Pastikan kode akun di jurnal umum konsisten dan tidak mengandung spasi tambahan.
- Gunakan validasi data agar hanya akun yang valid yang bisa dipilih.
- Letakkan UDF ke dalam Module di VBA Editor agar bisa digunakan di lembar Excel manapun.
Penutup
Dengan memahami cara membuat buku besar otomatis di Excel seperti yang telah dijelaskan di atas, Anda dapat menyusun laporan keuangan yang lebih cepat dan akurat. Kombinasi rumus FILTER, fungsi UDF AmbilTanggalTerdekat, dan struktur jurnal umum yang rapi akan menjadi fondasi penting dalam sistem pembukuan digital sederhana namun andal.
Jika Anda sedang membangun sistem keuangan untuk UMKM atau bisnis pribadi, tidak ada salahnya mulai menerapkan cara membuat buku besar otomatis di Excel ini. Selain hemat biaya, Anda juga akan lebih memahami arus keuangan bisnis secara langsung.
Download Template Gratis
Bagi Anda yang ingin mencoba teknik ini secara langsung, kami telah menyiapkan template gratis yang dirancang khusus untuk membantu Anda mempraktikkan Cara Membuat Buku Besar Otomatis di Microsoft Excel Berdasarkan Data dari Jurnal Umum ini. File Excel bisa didownload melalui tautan berikut:
Atau juga jika ingin mempelajari langkah demi langkah secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana teman-teman akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!
0 Komentar