Nah... , ketemu lagi dengan admin jempot2 office. Semoga Sahabat2 saya saat ini dalam keadaan sehat wal afiat. dan selalu bergairah menjalani kehidupan ini. Ya iyalah.....
Masih pada LibreOffice Calc tutorial, dan juga masih berkisar pada fungsi vlookup dan kode barang di LibreOffice Calc, tetapi kali ini akan menyidang fungsi vlookup yang nakal. Mengapa nakal?
dibawah ini pengaduannya ...
Misalkan di sheet(2) ada kode AB10 = 125, AB20 = 250 dan AC05 = 50, pada sheet(1) ada 4 kode, yaitu AB10, AB20, AB100 dan AC05, setelah rumus =vlookupnya ditempatkan di sheet(1), biasanya hasil AB100 mengambil data AB10 dan AB20, cara mengatasinya gimana Mas Kurnia? kalau sudah ketemu kasi kabar ya, TQ.
Dari pengaduan diatas, ternyata KODE AB100 suka nyolong data temannya kode AB10. Tentu AB10 ini bingung, kok data saya ada di AB100? Untungnya si AB100 ini nyolongnya nyolong duplikat sehingga AB10 ini tetap aja tidak merasa dirugikan.... he....he.....
Bagaimana bisa seperti itu ?
Kekeliruan pertama :
penulisan fungsi vlookup yang tidak memenuhi persyaratan ( lihat : vlookup dan kode barang di LibreOffice Calc ).
Kekeliruan kedua :
Kode AB100 tidak didaftarkan pada sheet2 sehingga tidak bisa diindek oleh fungsi vlookup.
Dengan gabungan 2 kekeliruan tersebut, maka jadinya kode AB100 datanya tidak valid alias nyolong itu tadi.
Mari kita telusuri tuh apa sebabnya kode AB100 nyolong data dari kode AB10
Visualisasi sheet2 seperti ini :
pada sheet1 ada 4 kode, yaitu AB10, AB20, AB100 dan AC05, setelah rumus =vlookupnya ditempatkan di sheet1, biasanya hasil AB100 mengambil data AB10 dan AB20.
Notasi rumus vlookupnya saya sesuaikan dengan syarat sort order yaitu :
=VLOOKUP(B7,Sheet2.$A$3:$B$25,2,0)
Coba teliti :
Apakah kode barang AB100 sudah terdaftar pada Sheet2 seperti gambar diatas? Karena tidak ditemukan pada tabel daftar kode di sheet2 maka hasilnya adalah #N/A atau not available/ dan yang dibawahnya hasilnya adalah suatu peringatan yang berbunyi “KODE BARANG TIDAK DITEMUKAN”.
Disini saya membuat input data khusus untuk kode AB100 2 baris, dan setiap barisnya notasi rumusnya berbeda.
Pada Cell D4 sampai dengan D7 notasi rumusnya adalah : =VLOOKUP(B7,Sheet2.$A$3:$B$25,2,0)
Hasilnya ya… itu tadi #N/A
Pada Cell D8 notasi rumusnya adalah =IFERROR(VLOOKUP(B8,Sheet4.$A$3:$B$25,2,0),"KODE BARANG TIDAK DITEMUKAN") dan peringatan yang muncul semoga bisa dimengerti (KODE BARANG TIDAK DITEMUKAN).
Kedua notasi rumus diatas itu akan selalu valid meski harus menghasilkan perhitungan seperti penjelasan diatas.
Mengapa terjadi seperti ini ? - biasanya hasil AB100 mengambil data AB10 dan AB20
Itu karena notasi rumusnya seperti ini : =VLOOKUP(B7,Sheet2.$A$3:$B$25,2) atau =VLOOKUP(B7,Sheet2.$A$3:$B$25,2,1)
Perhatikan pada tabel diatas, kode AB100 (2 baris) semuanya mengambil data dari kode AB10.
Kesimpulan :
Tambahan
Karena penulisan notasi rumus dan atau adanya suatu kode yang tidak terdaftar maka jumlah juga ikut mengalami kekeliruan atau hasil penjumlahan tidak bisa dihitung. Bagaimana caranya pada penjumlahan akhirnya bisa menghitung pada data yang valid saja? Untuk data yang tidak valid akan dihitung sebagai nilai nol (0). Sehingga tidak mengganggu struktur tabel.
Bagaimana caranya?
Ganti notasi rumus ini :
=IFERROR(VLOOKUP(B8,Sheet4.$A$3:$B$25,2,0),"KODE BARANG TIDAK DITEMUKAN") menjadi
=IFERROR(VLOOKUP(B8,Sheet4.$A$3:$B$25,2,0), 0)
Perhatikan pada kode AB100, nilai pada harga barang adalah 0, sehingga pada kolom jumlah nilainya juga menjadi 0 (nol)
Setelah kode barang AB100 didaftarkan sebagai kode yang diindex oleh fungsi vlookup pada sheet2 maka hasilnya seperti dua gambar di bawah ini :
Kesimpulan :
- Pada sheet1 kode AB100 tidak terdaftar sebagai kode yang menjadi acuan untuk input data yang seharusnya didaftarkan dahulu pada sheet2 sehingga index data pada sheet1 dapat sinkron dengan sheet2. Sehingga seharusnya pada sheet2 kode yang terdaftar adalah AB10 = 125, AB20 = 250 AB100= 500 dan AC05 = 50
- Notasi rumus fungsi vlookup. Pada penjelasan saya diatas bahwa notasi rumusnya ada yang kurang memenuhi syarat. Sekali lagi sebagai contoh : =VLOOKUP(B7,Sheet2.$A$3:$B$25,2,0) akan menghasilkan #N/A artinya data yang tidak valid karena tidak ditemukan pada sheet2 sehingga tidak terindek oleh fungsi vlookup
- Dengan menggunakan notasi rumus =VLOOKUP(B7,Sheet2.$A$3:$B$25,2) akan menghasilkan data tetapi data yang salah karena mengambil data dari kode AB10. Syarat terakhir yaitu sort order (0 atau 1) tidak dipenuhi pada notasi rumus ini. Pada Microsoft Excel sort order ini dinyatakan dengan true atau false
Tambahan
Karena penulisan notasi rumus dan atau adanya suatu kode yang tidak terdaftar maka jumlah juga ikut mengalami kekeliruan atau hasil penjumlahan tidak bisa dihitung. Bagaimana caranya pada penjumlahan akhirnya bisa menghitung pada data yang valid saja? Untuk data yang tidak valid akan dihitung sebagai nilai nol (0). Sehingga tidak mengganggu struktur tabel.
Bagaimana caranya?
Ganti notasi rumus ini :
=IFERROR(VLOOKUP(B8,Sheet4.$A$3:$B$25,2,0),"KODE BARANG TIDAK DITEMUKAN") menjadi
=IFERROR(VLOOKUP(B8,Sheet4.$A$3:$B$25,2,0), 0)
Perhatikan pada kode AB100, nilai pada harga barang adalah 0, sehingga pada kolom jumlah nilainya juga menjadi 0 (nol)
Setelah kode barang AB100 didaftarkan sebagai kode yang diindex oleh fungsi vlookup pada sheet2 maka hasilnya seperti dua gambar di bawah ini :
Misalkan di sheet(2) ada kode AB10 = 125, AB20 = 250 dan AC05 = 50, pada sheet(1) ada 4 kode, yaitu AB10, AB20, AB100 dan AC05, setelah rumus =vlookupnya ditempatkan di sheet(1), biasanya hasil AB100 mengambil data AB10 dan AB20, cara mengatasinya gimana Mas Kurnia? kalau sudah ketemu kasi kabar ya, TQ
Demikian LibreOffice tutorial ini khususnya fungsi vlookup pada LibreOffice Calc yang merupakan update dari artikel saya sebelumnya yang berjudul VLOOKUP dan kode barang di LibreOffice Calc.
