Mencari jumlah Invoice/Faktur yang loncat

Semua Invoice atau faktur seharusnya memiliki nomer urut (Prenumbered) karena itu merupakan salah satu internal kontrol didalam perusahaan, namun dalam pemeriksaan kita menemui daftar penjualan yang secara kasat mata tidak urut atau loncat. Data ini kalo jumlahnya kecil mungkin tidak terlalu masalah bisa kita cek secara manual namun jika udah melibatkan data yang puluhan ribu maka kita akan kesulitan

Berikut adalah cara untuk mengenali apakah ada Nomor yang loncat serta cara menghitung ada berapa jumlahnya :

Langkah2nya secara garis besar :

  1. Tentukan dikarakter berapa nomer urut/prenumbered-nya (biasanya aka dibelakang), misalnya Invoice no : "16/XXX/YYY/00015" berarti ada setelah karakter "/" yang terakhir
  2. Tentukan berapa nilai maksimal atau nilai terakhir dari nomer urut untuk tiap jenis invoice , misalnya jenis "16/XXX/YYY/" nomer terakhir adalah 00100
  3. Tentukan berapa jumlah dari tiap jenis invoice , misalnya jenis "16/XXX/YYY/" setelah kita hitung hanya ada 30
  4. Jadi terdapat 70 invoice yang hilang atau loncat

Gampang kan?, dan ini tidak memerlukan rumus yang aneh2, hanya gunakan fungsi text seperti LEN, LEFT,MID,SUBSTITUTE, INT

Data dan hasil bisa didonlot disini

Cari Invoice Loncat (2.3 MiB, 5 downloads)

I. Tentukan dikarakter berapa nomer urutnya

  1. Lihat gambar berikut ini, ada di Folder List Invoices
  2. Posisi nomer urut ada setelah karakter "/" terakhir, kita bisa gunakan fungsi right untuk ambil lima angka terakhir namun rupanya WP tidak konsisten karena ada nomer yang 5 digit, 3 digit, dll
  3. Sudah aku bikinkan step by step rumusnya
  4. Jadi kita cari dulu posisi karakter "/" ada di mana?
  5. kita bisa gunakan FIND namun fungsi ini hanya menemukan kata/karakter yang pertama kali ketemu
  6. Disini logikanya kita akan ubah karakter "/" terakhir dengan karakter unik lain (misalnya "|") lalu kita find
  7. Untuk mengganti karakter tertentu dengan karakter lain kita gunakan SUBSTITUTE,  fungsi ini unik karena bisa mengganti semua karakter yang ketemu atau karakter yang ke berapa yang ketemu, misalnya hanya mengganti karakter "/" yang ke -2, ke-3, dst
  8. untuk mengetahui nomer berapa karakter "/" yang terakhir maka kita hitung ada berapa karakter "/" di nomer faktur tsb
  9. Secara kasat mata kita tau karakter ke-3 namun sekali lagi WP tidak konsisten karena ada nomer "16/MAG/FAK/PB/03829" yang memilik 4 karakter "/"
  10. Jadi kita gunakan rumus LEN dan SUBSTITUTE
  11. Jadi logikanya kita buang semua karakter "/" lalu panjangnya kita bandingin dengan panjang nomer awal sebelum dibuang karakter "/"
  12. Kita buang dulu karakter "/"
  13. =SUBSTITUTE(A2,"/","")
  14. Hasilnya "16MAGFAKPB03829"
  15. Kita bandingkan Panjang Nomer awal dengan Panjang nomer yang gak ada karakter "/"-nya
  16. =LEN(B1)-LEN(SUBSTITUTE(A2,"/",""))
  17. =LEN("16/MAG/FAK/PB/03829")-LEN("16MAGFAKPB03829")
  18. =19-15
  19. =4
  20. Jadi ada 4 karakter "/"
  21. Lalu kita ganti karakter terakhir dengan "|", gunakan SUBSTITUTE namun hanya dikarakter terakhir
  22. =SUBSTITUTE(B1,"/","|",B3)
  23. =SUBSTITUTE("16/MAG/FAK/PB/03829","/","|",4)
  24. hasilnya "16/MAG/FAK/PB|03829"
  25. Lalu kita search karakter "|"
  26. =FIND(B1,"|")
  27. Hasilnya adalah 14
  28. Kita ambil type Invoice (Nomer invoice diluar Nomer Urut)
  29. =LEFT(B1,14)
  30. Hasilnya "16/MAG/FAK/PB/"
  31. Ambil Nomer urut, ambil karakter setelah karakter "/" terakhir+1
  32. =MID(B1,B5+1)
  33. Hasilnya "03829"
  34. Kita convert ke number
  35. =VALUE(B7)
  36. hasilnya 3829
  37. Rumus tadi lalu terapkan ke sheet yang berisi daftar Semua Invoice lalu kita kasih nama "List Invoices"

II. Tentukan untuk tiap type invoice berapa jumlah invoice yang telah dikeluarkan/nomor invoice terakhir

  1. Caranya adalah cari nomer urut terakhir dari semua nomer invoice
  2. Jadi kita sort semua invoice tapi descendent jadi dari nomer urut terbesar ke terkecil
  3. Kenapa mesti disort begitu karena akan kita gunakan fungsi VLOOKUP untuk tiap type invoice
  4. VLOOKUP akan mencari apakah type invoice ada di list semua invoice NAMUN hanya akan memberikan posisi invoice yang pertama kali ketemu
  5. Karena dibalik urutannya maka pasti yang ketemu adalah nomor yang terakhir
  6. Pertama kali kita sort dulu Sheet List Invoice secara menurun pada kolom NO Faktur/ kolom A
  7. Lalu kita buat sheet baru yang berisi semua Type invoice (kolom F)
  8. Kita copas kolom F dan Paste ke sheet baru yang kita beri nama No Faktur Loncat
  9. Lalu kita buang semua Duplikat (Data - Remove Duplicates)
  10. Dikolom B kita buat rumus
  11. =VLOOKUP(A2,'List Invoice'!F:H,3,FALSE)
  12. ini hasilnya
  13. Kita buat rumus untuk mencari atas berapa jumlah invoice yang telah dikeluarkan sesuai daftar
  14. Kita gunakan fungsi COUNTIF dengan kriteria Type Invoice
  15. Bikin rumus dikolom C
  16. =COUNTIF('List Invoice'!F:F,A2)
  17. ketemulah jumlah aktual invoice yg ada di List Daftar Invoice
  18. Kita bandingkan antara nomer terakhir dengan Jumlah Invoce yg ada
  19. Itulah jumlah Invoices yang loncat
  20. Ini hasilnya
  21. Coba kita cek misalnya item ke 4
  22. 16/MAG/WTC/
  23. Kita lihat di List Invoices apakah bener invoice cuman 3 biji dan nomer teralhir adalah 27 yang berarti ada 24 Invoice yang gak dilaporkan
  24. Terbukti benar
  25. Semoga Trik audit ini berguna untuk mencari koreksi

Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *