Cara Membuat Buku Besar Otomatis di Excel - Bagian III

Buku Besar otomatis Excel - Artikel ini merupakan kelanjutan dari dua artikel sebelumnya.

Artikel yang pertama kita sudah bahas tentang Sheet pertama berisi nama - nama Akun atau CoA yang akan kita gunakan dalam pembuatan Buku Besar.

Sedangkan artikel yang kedua kita sudah bahas Sheet kedua tentang Jurnal yang juga akan kita gunakan didalam Buku Besar.

Dalam Sheet yang kedua inilah cukup banyak Define Name atau Nama Range yang harus dibuat.

Sebelum mempelajari artikel yang ketiga ini silahkan pelajari terlebih dahulu artikel yang pertama dan kedua supaya mudah untuk memahami pembahasan yang ketiga.

Link pembahasan pertama dan pembahasan yang kedua saya tempatkan dibawah artikel ini dan silahkan klik jika ingin mempelajarinya.

Membuat Buku Besar Berdasarkan Jurnal dan CoA Dalam Excel

Sheet yang ketiga atau Buku Besar merupakan pembahasan utama dalam tiga artikel ini.

Sumber data yang akan diambil adalah Jurnal yang sudah dibuat sesuai dengan transaksi yang terjadi.

Tampilan dari Buku Besar yang akan kita buat adalah sebagai berikut :

Untuk mengisi rincian transaksinya cukup dengan klik atau pilih Nomor Akun yang ada dalam cell B2.

Ada dua cara untuk menggunakan atau membuat Buku Besar yang tampilannya seperti diatas.

Cara pertama Buku Besar hanya dibuat satu saja kemudian jika ingin melihat transaksinya cukup dengan mengganti Akunnya saja.

Cara yang kedua membuat Buku Besar satu persatu sesuai dengan jumlah Akun yang kita buat.

Untuk cara yang kedua ini sangat mudah, pertama silahkan copy dan pastekan Buku besarnya sesuai dengan jumlah yang dibutuhkan.

Selanjutnya ubah Akun dalam Cellnya masing - masing dan otomatis transaksi - transaksi yang ada dalam jurnal akan tampil didalam Buku Besar tersebut.

Jangan khawatir, rumus pada Buku Besar ini sudah saya setting supaya bisa copy dan paste untuk memperbanyak jumlah dari Buku Besarnya.

Untuk rumus yang digunakan serta cara membuat rumusnya mari kita bahas satu persatu sampai dengan selesai.

1. Rumus Excel Bagian Header Buku Besar

Bagian Header yang saya maksud adalah bagian atas dari tampilan Buku Besar yaitu Kode, Nama, Posisi, Saldo Awal, D/K dan Jmlh Baris.

Sebenarnya bagian dari Header yang benar - benar digunakan dalam rumus hanya Kode, Jmlh Baris dan Saldo Awal saja.

Untuk itu jika memang bagian yang lainnya selain tiga tersebut boleh untuk tidak ditampilkan.

Ada tiga rumus Excel yang digunakan dalam bagian Header ini yaitu IF, VLOOKUP dan COUNTIF.

Untuk yang pertama bagian Kode atau Cell B2, pada bagian ini memang tidak ada rumus yang digunakan.

Hanya saja kita sudah buatkan Dropdown list yang isinya adalah Akun atau CoA yang ada didalam Sheet CoA.

Untuk pembuatannya saya tidak akan jelaskan karena sudah dibahas dalam artikel yang kedua tentang Jurnal.

Kedua adalah Cell Nama atau cell B3, Cell ini akan berisi nama dari Akun yang sudah kita pilih didalam cell B2.

Adapaun rumus Excel yang digunakan didalam Cell B3 untuk menampilkan Nama Akun adalah sebagai berikut :

=IF(B2<>"";VLOOKUP(B2;Tabel_CoA;2;0))

Dengan rumus tersebut jika kita ubah CoA dalam Cell B3 maka secara otomatis namanya akan ikut berubah mengikuti CoA yang kita pilih tersebut.

Bagian Tabel_CoA pada rumus tersebut merupakan Nama Range atau Define Name yang sudah dibahas dan dijelaskan dalam artikel bagian pertama.

Supaya artikel ini tidak terlalu panjang saya asumsikan kita sudah sama - sama paham dan mengetahui cara penggunaan rumus Excel VLOOKUP, IF, dan juga COUNTIF.

Baca Juga : Penggunaan dan Fungsi Rumus Excel VLOOKUP

Untuk itu saya akan tuliskan rumusnya tetapi hanya akan dijelaskan yang menurut saya penting saja.

Adapun rumus lainnya didalam Header Buku Besar ini adalah sebagai berikut :

Posisi : =IF(B2<>"";VLOOKUP(B2;Tabel_CoA;3;0))

Saldo Awal : =IF(B2<>"";VLOOKUP(B2;Tabel_CoA;5;0))

D/K : =IF(B2<>"";VLOOKUP(B2;Tabel_CoA;4;0))

Jmlh Baris : =COUNTIF(Jurnal;B2)

Dari enam Cell dalam Header diatas ada dua yang akan saya bahas, pertama Saldo Awal dan rasanya ini sudah kita ketahui karena saldo awal akan diikut sertakan dalam Buku Besar kolom G.

Cell yang kedua adalah Jmlh Baris atau Cell G4, Cell ini memang biasanya tidak ada dalam Buku Besar.

Tetapi jika Buku Besar ini kita buat didalam Excel rasanya akan sangat penting untuk ditampilkan.

Angka yang muncul didalam Cell ini berfungsi untuk mengontrol jumlah baris transaksi yang ada didalam Sheet Jurnal.

Misalnya untuk CoA Kas didalam Jurnal ada 5 transaksi maka dalam Cell ini akan muncul angka 5.

Jadi, jika ada kekurangan baris transaksi dengan cepat kita akan mengetahuinya.

Katakan misalnya ternyata jumlah baris untuk Akun Kas hanya ada 3 maka dengan melihat angka 5 ini artinya kita harus menambahkan dua baris lagi kedalam buku besar.

Bahkan saya bisa memberikan konsep untuk menambahkan Conditional Formatting kedalam Cell G4.

Jika jumlah baris yang ada lebih kecil dari angka yang muncul dalam Cell G4 maka Cell G4 akan berubah menjadi warna merah.

Dengan kondisi tersebut maka kita akan dengan cepat bisa menemukan Akun mana dalam Buku Besar yang barisnya kurang karena cukup dengan mencari Cell yang berwarna merah.

Tapi saya sampaikan dalam contoh file ini saya belum tambahkan Conditional Formatting tersebut.

Baca Juga : Mewarnai Cell Otomatis Dengan Kriteria Dalam Excel

Silahkan kreasikan sendiri karena sudah banyak saya bahas tentang Conditional Formatting dalam artikel sebelumnya.

2. Kolom Kode Dalam Buku Besar

Pembahasan yang kedua adalah kolom Kode atau kolom A dalam Buku Besar Excel.

Kolom ini juga biasanya dalam Buku Besar memang tidak ada atau tidak ditampilkan.

Tetapi dalam Buku Besar Excel kita ini kolom A merupakan kolom yang sangat penting.

Kolom ini akan menampilkan kode transaksi yang akan menghubungkan Buku Besar dengan Jurnal.

Kode didalam kolom ini adalah 4 digit akun atau CoA dan satu digitnya adalah urutan transaksi.

Misalnya untuk kas Akunnya 1.1.1 jika ada 5 transaksi maka kode yang akan muncul adalah 1.1.1-1, 1.1.1-2, 1.1.1-3, 1.1.1-4 dan 1.1.1-5.

Jika kurang paham saya sudah jelaskan konsep kode ini didalam artikel yang kedua atau pembahasan Jurnal.

Selanjutnya ada dua rumus Excel didalam kolom ini yaitu untuk rumus baris pertama pada kolom serta rumus untuk baris kedua dan baris selanjutnya.

Adapun rumus untuk baris pertama dalam kolom atau cell A7 dalam contoh gambar diatas adalah sebagai berikut :

=B2&"-"&1

Sedangkan rumus Excel untuk Cell A8 dan cell selanjutnya didalam Buku Besar adalah sebagai berikut :

=IFERROR(IF((MID(A7;7;LEN(A7)-6)+1)<=COUNTIF(Jurnal;LEFT(A7;5));LEFT(A7;5)&"-"&MID(A7;7;LEN(A7)-6)+1;"");"")

Jika Buku Besar dicopy dan pastekan maka secara otomatis alamat baris dan kolom atau Cellnya akan berubah karena pola cell Absolut sudah saya sesuaikan.

Ada lima rumus Excel yang saya gunakan diatas dan semoga pembaca sudah paham masing - masing fungsi dari rumus - rumus tersebut.

Baca Juga : Cara Menggunakan Fungsi COUNTIF Dalam Microsoft Excel

Jika belum memahaminya silahkan buka dalam artikelnya masing - masing karena sudah saya bahas sebelumnya.

Kode didalam kolom ini akan menyesuaikan dengan angka yang muncul didalam Cell G4.

Misalnya angka yang muncul adalah 5 maka kode didalam kolom A ini juga jumlahnya akan ada 5.

3. Kolom Tanggal Dalam Buku Besar

Kolom yang kedua dalam Buku Besar Excel ini adalah Tanggal dan tentu kolom ini akan menampilkan Tanggal yang sumbernya dari Jurnal atau dari Sheet yang kedua.

Rumus Excel yang digunakan sangat mudah karena hanya menggunakan rumus Excel VLOOKUP saja.

Adapun rumus yang digunakan dalam Cell B7 adalah sebagai berikut :

=IFERROR(INDEX(Tgl;MATCH('Buku Besar'!A7;Ref;0));"")

Jika Kode didalam kolom A atau kolom Kode berubah maka secara otomatis kolom ini akan mengambil data Tanggal sesuai dengan Kodenya tersebut.

4. Kolom No. Jurnal Dalam Buku Besar

Kolom yang ketiga dalam Buku Besar Excel adalah No. Jurnal dan kolom ini sama dengan kolom Tanggal hanya saja yang ditampilkan adalah Nomor Jurnal dari Sheet yang kedua.

Adapun rumus yang digunakan dalam Cell C7 adalah sebagai berikut :

=IFERROR(INDEX(No_Jurnal;MATCH('Buku Besar'!A7;Ref;0));"")

Rumus inipun akan menampilkan nomor jurnal yang sumbernya dari Sheet Jurnal sesuai dengan kode yang muncul dalam kolom A.

5. Kolom Uraian, Debet dan Kredit dalam Buku Besar

Kolom selanjutnya adalah Uraian, Debet dan Kredit ketiga kolom ini sama dengan dua kolom selanjutnya yaitu akan menampilkan runcian transaksi saja.

Adapun rumus - rumus yang digunakan adalah sebagai berikut :

Uraian : =IFERROR(INDEX(Ket;MATCH('Buku Besar'!A7;Ref;0));"")

Debet : =IFERROR(INDEX(Debet;MATCH('Buku Besar'!A7;Ref;0));"")

Kredit : =IFERROR(INDEX(Kredit;MATCH('Buku Besar'!A7;Ref;0));"")

Rincian transaksi yang muncul dalam masing - masing kolomnya diambil berdasarkan kode dalam kolom A.

6. Kolom Saldo Dalam Buku Besar

Kolom yang terakhir dalam Buku Besar Excel adalah Saldo dan angka yang muncul dalam saldo harus benar atau jangan sampai salah karena akan digunakan didalam Neraca Saldo.

Untuk rumus dalam kolom ini saya akan bagi menjadi dua bagian, yaitu untuk saldo akun yang posisinya di Debet serta untuk saldo akun yang possinya ada di kredit.

Kenapa ini dibedakan ?

Supaya satu Buku Besar bisa digunakan untuk dua kondisi tersebut karena memang opsi copy dan paste harus bisa dilakukan dalam Buku Besar.

Jika Buku Besarnya dibuat satu persatu secara manual lalu apa bedanya dengan dibuat manual langsung ?

Selain itu pada kolom ini rumusnya juga saya bedakan antara baris pertama dengan baris selanjutnya.

Karena baris pertama akan menambahkan posisi saldo awal sedangkan baris selanjutnya tidak akan menambahkan saldo awal tersebut.

Adapun rumus yang digunakan dalam kolom Saldo Buku besar baris pertama atau Cell G7 adalah sebagai berikut :

=IF(VLOOKUP(LEFT(A7;5);Tabel_CoA;4;0)="D";G2+E7-F7;G2+F7-E7)

Sedangkan rumus Excel yang digunakan untuk baris kedua dan baris selanjutnya adalah sebagai berikut :

=IFERROR(IF(VLOOKUP(LEFT(A8;5);Tabel_CoA;4;0)="D";G7+E8-F8;G7+F8-E8);0)

Dengan dua rumus tersebut saldo akan otomatis ditambahkan sesuai dengan saldo awal serta angka yang muncul dalam kolom Debet ataupun Kredit.

Sampai dengan ini pembahasan Buku Besar dalam Excel sudah selesai dilakukan.

7. Contoh File Buku Besar Excel

Untuk contoh file saya cukup bingung apakah disertakan atau tidak perlu menyertakannya.

Karena banyak sekali Blogger yang melakukan Copy Paste sampai dengan link contoh filenya.

Tapi supaya proses pembelajaran kita mudah dilakukan saya akan tetap menyertakan link contoh filenya.

Contoh file ini bisa didapatkan secara gratis dengan password adh_excel11211 tetapi jika berkenan silahakn bantu Subscribe Channel Youtube ADH EXCEL dan jika banyak yang subscribe saya akan bahas artikel ini dalam bentuk video.

Jika membutuhkan contoh filenya silahkan klik tombol dibawah ini :

Download

Silahkan ambil dan pelajari filenya, mohon untuk tidak menyebarkan atau menyertakan file tersebut didalam pembahasan blog lain tanpa izin atau tanpa menyertakan link aktif sumber langsung ke artikel ini.

Itulah pembahasan kita kali ini tentang Buku Besar dalam Excel dan semoga artikel ini bermanfaat untuk semua pembaca.

Untuk pembahasan bagian kedua dari Buku Besar Excel silahkan klik link dibawah ini :

<---Buku Besar Excel Bagian II