Bagaimana cara untuk membuat aplikasi buku besar pada Microsoft Excel dari jurnal umum yang telah ada?

gambar 1. "buku besar"

gambar 2. "jurnal umum"

gambar 3. "neraca saldo"

1. buka sheet baru, ganti nama sheet menjadi "jurnal umum".
2. buatlah format jurnal umum.
3. klik cell di bawah kolom akun, misal nama cell itu "C5".
4. klik formula lalu klik insert function.
5. pilih vlookup lalu klik ok.
6. isikan lookup value dengan cell C5.
7. isikan table array dengan cara, bukalah sheet yang berisi data daftar akun lalu blok datanya dan jangan lupa absolutkan dengan cara klik F4.
8. isikan col_index_num dengan "2" (menunjukkan urutan kolom dalam tabel)
9. isikan range lookup dengan "false" apabila tabel data tidak diurutkan, dan isikan dengan "true" apabila sebaliknya.
10. klik ok.
11. lalu akan muncul tanda #N/A, copy rumus tersebut sejumlah yang dibutuhkan.
12. isi jurnal umum dengan cara sbb: 
    isi kolom tanggal sesuat dengan tanggal transaksi, isi kolom kode rekening sesuai dengan jurnal transaksi.
13. pada baris terakhir, jumlahkan total kolom "debet" dan "kredit" pada jurnal umum untuk memastikan keseimbangan jumlah debet dan kredit.

1. Bukalah sheet "jurnal umum".
2. Blok data jurnal umum beserta judulnya lalu copylah.
3. paste data yang dicopy di sheet baru yang kamu beri nama "buku besar".
4. Editlah judul dari data yang sudah dipaste menjadi "buku besar", lalu hapus jumlah total debet dan kredit yang ada di data tersebut karena jumlah total debet dan kredit tidak dipakai pada buku besar.
5. Urutkan data tersebut yang akan diposting di buku besar dengan cara blok data lalu pilih menu data lalu klik sort.
6. Lalu muncullah kotak dialog sort, isikan kolom sort by dengan nomor akun lalu klik ok.
7. Buka sheet baru, lalu ganti nama menjadi  "BB OK".
8. Posting data di sheet "buku besar" ke sheet "BB OK".
9. Atur sesuai format buku besar.

1. Buka sheet baru lalu ganti nama menjadi "neraca saldo".
2. Bukalah sheet yang berisi neraca saldo awal.
3. Copylah data yang ada di sheet neraca saldo awal ke sheet "neraca saldo".
4. Editlah data tersebut dengan merubah judulnya menjadi "neraca saldo" dan hapuslah data uang yang ada di kolom "debet" dan "kredit".
5. Isilah kolom "debet" dan "kredit" dengan seluruh saldo akhir yang ada dalam neraca saldo awal dan buku besar.
6. Tambahkan di baris paling bawah kolom "jumlah" untuk jumlah total debet dan kredit.

Modul Komputer Akuntansi (Spreedsheet) Bagian 3

Akun buku besar kita mulai membuat pada Sheet7. Jika dalam workbook anda tidak ada Sheet7 dapat ditambahkan dengan cara; klik Menu Insert kemudian Workseet

Bentuk kolom akun sama seperti akun buku besar secara manual, terdiri dari; kolom Tanggal, Keterangan, Ref, Debet dan Kredit ( untuk mutasi ) serta Saldo Debet dan Kredit.

Secara lengkap langkah-langkah  pembuatan kolom akun buku besar sebagaimana di bawah ini

PEMBUATAN KOLOM

• Pada sel A1 ketik Buku Besar ( bentuk dan ukuran Font disesuaikan sendiri)
• Untuk rata tengah judul ; blok A1 sampai dengan G1, kemudian klik  ikon Merger and Center 
• Kolom tanggal dimulai dari sel A3 .
• Kolom transaksi mulai dari baris ke 5 sampai dengan baris ke 170
• Atur ukuran kolom yang sesuai

Bentuk kolom akun buku besar seperti di bawah ini :

Gambar 17. Akun Buku Besar

• Pada sel A2 ketik Nama Akun : (gunakan rata kanan))
• Pada sel B2 isikan formula =IF(G2="";"";VLOOKUP(G2;akun;2;0))
• Formula ini berhungsi untuk menampilkan nama akun yang sesuai dengan nomor akun yang diisikan pada sel G2.
• Pada sel F2 ketik No Akun : (gunakan rata kanan atau klik ikon   pada toolbar)
• Pada sel G2 tempat mengisi nomor akun yang secara otomatis nama akun akan tampil pada sel B2 sesuai dengan daftar akun

Keterangan Formula pada sel B2 =IF(G2="";"";VLOOKUP(G2;akun;2;0))

• =IF(G2="";"" jika pada sel G2 kosong ( tidak ada isinya ) maka pada sel B2 juga kosong
• VLOOKUP(G2;akun;2;0)) jika tidak ( jika sel G2 ada isinya ) maka maka dengan formula VLOOKUP data pada sel G2 akan digunakan sebagai dasar membaca   tabel akun (yang diberi nama akun pada Define Name), kolom 2. Angka 0 (nol) terakhir berarti jika nilai yang diisi pada sel G2 tidak terdapat pada tabel maka nilai baca tidal ditampilkan / salah, biasanya tampil  #N/A. 

Pada  kolom 2 tabel akun adalah nama akun. Jadi kalau pada sel G2 diisi nomor akun maka dengan formula fungsi tersebut akan menampilkan nama akun yang sesuai.

Misalkan pada sel G2 diiri 112 maka pada sel B2 akan tampil Piutang Dagang.

FORMULA KOLOM AKUN BUKU BESAR

Pada buku besar kita akan membuat formula fungsi untuk mencatat secara otomatis data yang bersumber dari Jurnal Umum ,Jurnal Penyesuaian. dan juga data yang bersumber dari Jurnal Penutup

Formula fungsi untuk mencatat data dari Jurnal Umum

1. Tanggal

Mengisi tanggal Saldo Awal :

• Letakkan kursor pada sel A5

• Ketik sama dengan ( = )

• Klik sheet ID 

• Letakkan kursor pada sel B4 lalu enter

Hasilnya pada sel A5 adalah formula  =ID!B4, atau anda dapat mengetik langsung formula tersebut pada sel A5

Dengan formula ini maka tanggal saldo awal pada sheet ID akan selalu tampil pada sel A5

Mengisi tanggal transaksi :

• Letakkan kursor pada sel A6 kemudian ketik formula fungsi =IF( 
• Selanjutnya klik sel G2, dan tekan tombol F4 pada keyboard ( alamat sel G2 akan menjadi absolut ditandai dengan simbol dollar ( $ )
• Ketik tanda sama dengan ( = )
• Klik sheet JU, kemudian klik sel D6
• Ketik tanda titik koma ( ; )
• (masih pada sheet JU ) klik sel A6
• Ketik tanda titik koma ( ; )
• Ketik tanda kutip dua ( “ ) sebanyak dua kali
• Ketik tanda kurung tutup , lalu enter

Hasil langkah tersebut adalah dalam sel A6 terdapat formula fungsi  =IF($G$2=JU!D6;JU!A6;"")

Copy formula fungsi tersebut untuk sel A7 sampai dengan sel A100

2. Keterangan

• Pada sel B5 ketik Saldo Awal
• Pada sel B6 ketik formula fungsi =IF(
• Selanjutnya klik sel G2, dan tekan tombol F4 pada keyboard ( alamat sel G2 akan menjadi absolut ditandai dengan simbol dollar ( $ )
• Ketik tanda sama dengan ( = )
• Klik sheet JU, kemudian klik sel D6
• Ketik tanda titik koma ( ; )
• (masih pada sheet JU ) klik sel B6
• Ketik tanda titik koma ( ; )
• Ketik tanda kutip dua ( “ ) sebanyak dua kali
• Ketik tanda kurung tutup , lalu enter

Hasil langkah tersebut adalah dalam sel B6 terdapat formula fungsi  =IF($G$2=JU!D6;JU!B6;"")

Copy formula fungsi tersebut untuk sel B7 sampai dengan sel B100

3. Ref

• Pada sel C6 ketik formula fungsi =IF(
• Selanjutnya klik sel G2, dan tekan tombol F4 pada keyboard ( alamat sel G2 akan menjadi absolut ditandai dengan simbol dollar ( $ )
• Ketik tanda sama dengan ( = )
• Klik sheet JU, kemudian klik sel D6
• Ketik tanda titik koma ( ; )
• ketik tanda kutip dua JU tanda kutip dua (“JU”)
• Ketik tanda titik koma ( ; )
• Ketik tanda kutip dua ( “ ) sebanyak dua kali
• Ketik tanda kurung tutup , lalu enter

Hasil langkah tersebut adalah dalam sel C6 terdapat formula fungsi  

=IF(JU!$D6=$G$2;"JU";"")

Copy formula fungsi tersebut untuk sel C7 sampai dengan sel C100

4. Debet

• Sel D5 dikolongkan
• Sel D6 diisi formula fungsi =IF(
• Selanjutnya klik sel G2, dan tekan tombol F4 pada keyboard ( alamat sel G2 akan menjadi absolut ditandai dengan simbol dollar ( $ )
• Ketik tanda sama dengan ( = )
• Klik sheet JU, kemudian klik sel D6
• Ketik tanda titik koma ( ; )
• (masih pada sheet JU ) klik sel E6
• Ketik tanda titik koma ( ; )
• Ketik nol ( 0 ) 
• Ketik tanda kurung tutup , lalu enter

Hasil langkah tersebut adalah dalam sel B6 terdapat formula fungsi  =IF($G$2=JU!D6;JU!E6;0)

Copy formula fungsi tersebut untuk sel D7 sampai dengan sel D101

5. Kredit

• Sel E5 dikolongkan
• Sel E6 diisi formula fungsi =IF(
• Selanjutnya klik sel G2, dan tekan tombol F4 pada keyboard ( alamat sel G2 akan menjadi absolut ditandai dengan simbol dollar ( $ )
• Ketik tanda sama dengan ( = )
• Klik sheet JU, kemudian klik sel D6
• Ketik tanda titik koma ( ; )
• (masih pada sheet JU ) klik sel F6
• Ketik tanda titik koma ( ; )
• Ketik nol ( 0 ) 
• Ketik tanda kurung tutup , lalu enter

Hasil langkah tersebut adalah dalam sel B6 terdapat formula fungsi  =IF($G$2=JU!D6;JU!F6;0)

Copy formula fungsi tersebut untuk sel E7 sampai dengan sel E101

6. Saldo Debet

Mencatat Saldo Awal

Pada sel F5 isikan formula fingsi =vlookup(G2;ns;3;0)

Mencatat saldo debet

• Pada sel F6 isikan formula fungsi =IF(
• Klik sel F5 kemudian ketik tanda min ( - )
• Klik sel G5 kemudian ketik tanda plus ( + )
• Klik sel D6 kemudian ketik tanda min ( - )
• Klik sel E6 kemudian ketik tanda lebih besar dari ( > )
• Ketik angka nol ( 0 ), kemudian tanda titik koma ( ; )
• Klik sel F5 kemudian ketik tanda min ( - )
• Klik sel G5 kemudian ketik tanda plus ( + )
• Klik sel D6 kemudian ketik tanda min ( - )
• Klik sel E6 kemudian ketik titik koma ( ; )
• Ketik angka nol (0), kemudian kurung tutup lalu enter

Hasil langkah tersebut adalah dalam sel B6 terdapat formula fungsi  

=IF(F5-G5+D6-E6>0;F5-G5+D6-E6;0)

Copy formula fungsi tersebut untuk sel F7 sampai dengan sel F100

7. Saldo Kredit 

Mencatat Saldo Awal

Pada sel G5 isikan formula fingsi =vlookup(G2;ns;4;0)

Mencatat saldo kredit

• Pada sel G6 isikan formula fungsi =IF(
• Klik sel G5 kemudian ketik tanda min ( - )
• Klik sel F5 kemudian ketik tanda plus ( + )
• Klik sel E6 kemudian ketik tanda min ( - )
• Klik sel D6 kemudian ketik tanda lebih besar dari ( > )
• Ketik angka nol ( 0 ), kemudian tanda titik koma ( ; )
• Klik sel G5 kemudian ketik tanda min ( - )
• Klik sel F5 kemudian ketik tanda plus ( + )
• Klik sel E6 kemudian ketik tanda min ( - )
• Klik sel E6 kemudian ketik titik koma ( ; )
• Ketik angka nol (0), kemudian kurung tutup lalu enter

Hasil langkah tersebut adalah dalam sel B6 terdapat formula fungsi  

=IF(G5-F5+E6-D6>0; G5-F5+E6-D6;0)

Copy formula fungsi tersebut untuk sel G7 sampai dengan sel G100

PENJELASAN FORMULA FUNGSI

1. Sel A5 =ID!B4 ; fungsi ini akan membaca data yang ada pada sheet ID sel B4 yaitu tanggal sado awal

2. Sel A6

=IF($G$2=JU!D6;JU!A6;"")

• =IF($G$2 =JU!D6 artinya jika data pada sel G2 ( nomor akun )sama dengan data yang ada pada sheet JU sel D6 ( kolom REF )
• ;JU!A6 artinya maka pada sel A6 diisikan dengan data yang ada pada sheet JU sel A6, yaitu sel teratas pada kolom tanggal.
• ;"") artinya jika ketentuan tersebut tidak terpenuhi, yaitu sel  G2 tidak sama dengan sel D6 pada sheetJU maka sel A6 dikosongkan ( tanda kutip ganda diketik dua kali melambangkan teks kosong).

Fornula fungsi ini untuk merekam semua data kolom Tanggal yang berkaitan dengan transaksi yang sama dengan nomor akun yang terdapat pada sel G2.

Setiap formula fungsi selalu diawali dengan kurung buka dan kurung tutup.

3. Sel B6

=IF($G$2=JU!D6;JU!B6;"")

• =IF($G$2 =JU!D6 artinya jika data pada sel G2 ( nomor akun )sama dengan data yang ada pada sheet JU sel D6 ( kolom REF )
• ;JU!B6 artinya maka pada sel A6 diisikan dengan data yang ada pada sheet JU sel B6, yaitu sel teratas pada kolom keterangan.
• ;"") artinya jika ketentuan tersebut tidak terpenuhi, yaitu sel  G2 tidak sama dengan sel D6 pada sheetJU maka sel A6 dikosongkan ( tanda kutip ganda diketik dua kali melambangkan teks kosong).

Fornula fungsi ini untuk merekam semua data pada kolom Keterangan yang berkaitan dengan transaksi yang sama dengan nomor akun yang terdapat pada sel G2.

4. Sel C6

=IF(JU!$D6=$G$2;"JU";"")

• =IF($G$2 =JU!D6 artinya jika data pada sel G2 ( nomor akun )sama dengan data yang ada pada sheet JU sel D6 ( kolom REF )
• ;”JU” artinya maka pada sel A6 teks JU sebagai rujukan sumber jurnal yaitu Jurnal umum (JU)
• ;"") artinya jika ketentuan tersebut tidak terpenuhi, yaitu sel  G2 tidak sama dengan sel D6 pada sheetJU maka sel A6 dikosongkan ( tanda kutip ganda diketik dua kali melambangkan teks kosong).

Fornula fungsi ini untuk merekam semua data pada

5. Sel D6

=IF($G$2=JU!D6;JU!E6;"")

• =IF($G$2 =JU!D6 artinya jika data pada sel G2 ( nomor akun )sama dengan data yang ada pada sheet JU sel D6 ( kolom REF )
• ;JU!E5 artinya maka pada sel A6 diisikan dengan data yang ada pada sheet JU sel E6, yaitu sel teratas pada kolom Debet jurnal umum.
• ;"") artinya jika ketentuan tersebut tidak terpenuhi, yaitu sel  G2 tidak sama dengan sel D6 pada sheetJU maka sel A6 dikosongkan ( tanda kutip ganda diketik dua kali melambangkan teks kosong).

Fornula fungsi ini untuk merekam semua data pada kolom Debet Jurnal Umum yang berkaitan dengan transaksi yang sama dengan nomor akun yang terdapat pada sel G2

6. Sel E6

=IF($G$2=JU!D6;JU!F6;"")

• =IF($G$2 =JU!D6 artinya jika data pada sel G2 ( nomor akun )sama dengan data yang ada pada sheet JU sel D6 ( kolom REF )
• ;JU!F5 artinya maka pada sel A6 diisikan dengan data yang ada pada sheet JU sel F6, yaitu sel teratas pada kolom kredit  jurnal umum.
• ;"") artinya jika ketentuan tersebut tidak terpenuhi, yaitu sel  G2 tidak sama dengan sel D6 pada sheetJU maka sel A6 dikosongkan ( tanda kutip ganda diketik dua kali melambangkan teks kosong).

Fornula fungsi ini untuk merekam semua data pada kolom Kredit Jurnal Umum yang berkaitan dengan transaksi yang sama dengan nomor akun yang terdapat pada sel G2

7. Sel F5

=vlookup(G2;ns;3;0)

Dengan formula fungsi ini maka data yang ada pada sel G2 digunakan dasar untuk membaca tabel ns ( neraca saldo awal ) pada kolom 3. Dalam  hal ini kolom 3 berisi nilai sebelah debet neraca saldo awal. 

Sedangkan nol terakhir untuk indikator jika pada sel G2 diisi data yang tidak terdapat pada tabel ns maka formula akan menampilkan #N/A.

8. Sel F6

=IF(F5-G5+D6-E6>0;F5-G5+D6-E6;0)

Jika data sel F5 dikurangi dengan data sel G5, kemudian ditambah dengan data sel D6 dan dikurangi dengan data sel E6 lebih besar dari nol,

maka formula akan diberlakukan yaitu data sel F5 dikurangi dengan data sel G5, kemudian ditambah dengan data sel D6 dan dikurangi dengan data sel E6,

jika syarat pertama tidak terpenuhi maka sel F6 akan diisi nilai nol (0)

9. Sel G5

=vlookup(G2;ns;4;0)

Dengan formula fungsi ini maka data yang ada pada sel G2 digunakan dasar untuk membaca tabel ns ( neraca saldo awal ) pada kolom 4. Dalam  hal ini kolom 4 berisi nilai sebelah kredit neraca saldo awal. 

Sedangkan nol terakhir untuk indikator jika pada sel G2 diisi data yang tidak terdapat pada tabel ns maka formula akan menampilkan #N/A

10. Sel G6

=IF(G5-F5+E6-D6>0; G5-F5+E6-D6;0)

Jika data sel G5 dikurangi dengan data sel F5, kemudian ditambah dengan data sel E6 dan dikurangi dengan data sel D6 lebih besar dari nol,

maka formula akan diberlakukan yaitu data sel G5 dikurangi dengan data sel F5, kemudian ditambah dengan data sel E6 dan dikurangi dengan data sel D6,

jika syarat pertama tidak terpenuhi maka sel G6 akan diisi nilai nol (0)

Semua formula fungsi dari baris ke 6 dicopy sampai dengan baris ke 100, ini dilakukan karena panjang kolom jurnal umum yang kita buat sampai dengan baris ke 100

Hasil copy tersebut pada sel F100 akan berisi formula fungsi

=IF(F99-G99+D100-E100>0;F99-G99+D100-E100;0)

Dan sel G100 akan berisi formula fungsi

=IF(G99-F99+E100-D100>0;G99-F99+E100-D100;0)

Auto Filter

Jika semua formula buku besar selesai, kemudian pada sel G2 diisi nomor akun tertentu, maka yang tampil adalah semua transaksi yang berkaitan dengan nomor akun tersebut.

Misalkan diisi nomor 111, maka yang tampil adalah semua transaksi yang berkaitan dengn nomor akun 111 akan tampil. Akibatnya akan ada beberapa baris yang kosong. Untuk menyembunyikan baris-baris yang kosong kita bisa menggunakan fasilitas Auto Filter

Fasilitas ini digunakan untuk menampilkan baris-baris buku besar yang hanya berisi transaksi saja. Jadi baris-baris kosong atau tidak berisi transaksi akan disembunyikan.

Langkahnya adalah :

• Blok range A3 : A170 

• Klik Menu Data, Filter, 

• Hasil langkah ini maka pada sel A3 akan tampil pemandu Auto Filter

Cara menggunakan Auto Filter

• Klik pemandu Auto Filter

• Pilih NonBlank

Untuk menampilkan semua baris dilakukan dengan cara :

• Klik pemandu Auto Filter

• Pilih All

Dengan aplikasi ini , jika ingin menampilkan salah satu akun buku besar, cukup mengisi nomor akun pada sel G2, kemudian pilih non blank pada auto filter maka semua data akun akan tampil.

Sampai disini mengenai pembuatan akun buku besar, untuk materi selanjutnya saya akan membahas bagaimana membuat Neraca Saldo. Baca : Membuat Aplikasi Neraca Saldo (Trial Balance) Pada Ms Excel

Jika ada saran atau pertanyaan silakan komen. Selamat Mencoba