Atik Dhama

berbagi pengalaman

Belajar Excel Kelas XI Materi Kelas XI Microsoft Excel Pengetahuan Semester Genap Tip & Trik

Membuat Blanko Nota Penjualan (lanjutan)

MEMBUAT BLANKO NOTA PENJUALAN

Bagian 2 (melengkapi blanko dengan formula)

 

 

Setelah file blanko Nota Penjualan selesai dibuat, sesuai format berikut:

Blanko Nota
Blanko Nota

Maka yang diingin selanjutnya adalah kolom Deskripsi/Nama Barang akan tampil secara otomatis apabila sudah mengisikan Kode, begitu juga untuk Harga akan tampil secara otomatis apabila sudah mengisikan Kode. Sehingga didapat pernyataan untuk menyatakan syarat tampilnya Deskripsi/Nama Barang adalah:

Jika Kode diisi (tidak kosong) maka tampilkan Deskripsi/Nama Barang selain itu Kosong.

Karena Deskripsi/Nama Barang serta Harga ingin tampil otomatis maka kita harus mempunyai sebuah daftar Deskripsi/Nama Barang dan daftar Harga dalam hal ini kita butuhkan sebuah tabel Deskripsi.

Selanjutnya siapkan tabel database Deskripsi/Nama Barang pada tempat lain yang tidak akan tercetak (sebaiknya pada Sheet yang berbeda). Berikut contoh tabelnya:

Tabel Referensi
Tabel Referensi

Tabel di atas disebut sebagai tabel referensi (table array). Agar lebih mudah, maka alamat range dari tabel di atas kita beri nama nama_barang dengan cara :

  1. Seleksi data di atas dimulai dari sel A2 sampai C5, yang merupakan akhir dari tabel.

    Define Name
    Define Name
  2. Klik tab Formula, lalu klik Define Name. Sesuai seperti gambar di atas!

    nama_barang
    nama_barang
  3. Lanjutkan dengan mengetikkan nama_barang pada kotak isian Name sebagai pengganti alamat dari range yang diseleksi. Akhiri dengan mengklik tombol OK.
  4. Maka akan terlihat pada name box adalah nama_barang. Seperti terlihat pada gambar berikut!

    Tabel nama_barang
    Tabel nama_barang

Berikutnya untuk menampilkan Deskripsi/Nama Barang sesuai tabel di atas pada blanko Nota dibutuhkan sebuah fungsi yang bernama VLOOKUP, dimana syntaxnya adalah:

VLOOKUP(lookup_value;table_array;col_index_num;[range_lookup])

Lookup_value         :     nilai yang akan dibandingkan dengan kolom pertama pada tabel (A7)

Table_array              :     alamat range tabel (nama_barang)

Col_index_num     :     nomor index kolom dari tabel yang kolomnya ingin ditampilkan (yang ingin ditampilkan Deskripsi/Nama Barang maka nomor indexnya adalah 2, bila yang diinginkan Harga Satuan maka nomor indexnya adalah 3)

[range_lookup]      :     bersifat opsional namun dalam hal ini diperlukan sekali (diisi dengan 1 atau TRUE jika nilai yang dibandingkan terdapat rentang dan bisa atau boleh mendekati nilai yang dibandingkan dengan tabel, diisi 0 atau FALSE jika nilai yang dibandingkan harus benar-benar sama persis/exact match)

Karena range_lookup untuk kasus ini adalah exact match maka kolom Kode harus dilakukan validasi yaitu dengan cara:

  1. Seleksi kolom Kode dari nota, lalu lanjutkan dengan mengklik tab Data, klik Data Validation.

    Data Validasi pada kolom Kode
    Data Validasi pada kolom Kode
  2. Maka akan tampil kotak dialog seperti berikut:

    Kotak Dialog Data Validasi
    Kotak Dialog Data Validasi
  3. Pilih List pada pilihan Validation Criteria Allow. Masukkan kriterianya, dalam hal ini adalah kode yang ada pada index pertama dari tabel (bisa dengan cara memberi nama seperti pemberian nama dari range A2:C5 yaitu nama_barang. Untuk kasus ini range A2:A5 diberi nama kode.

    List Source
    List Source
  4. Selanjutnya kita lakukan pengaturan dengan mengisi Source dengan =kode. Seperti terlihat pada gambar berikut!

    Source Setting
    Source Setting
  5. Kita juga bisa menambahkan petunjuk pengisian yang diatur pada tab Input Message seperti contoh berikut!

    Input Message
    Input Message
  6. Kita juga bisa memberikan peringatan jika terjadi kesalahan menginput data yang diatur pada tab Error Alert. Berikut contoh pengaturannya!

    Error Alert
    Error Alert
  7. Anda bisa melakukan pengaturan validasi untuk sel-sel lainnya sesuai kebutuhan.

Jika sudah melakukan validasi maka selanjutnya tinggal menerjemahkan pernyataan ke dalam formula Excel.

Untuk menampilkan Deskripsi/Nama Barang:

=VLOOKUP(A7;nama_barang;2;FALSE)

Karena nota tersebut berupa blanko maka sesuai syarat:

Jika Kode diisi (tidak kosong) maka tampilkan Deskripsi/Nama Barang selain itu Kosong.

Didapat:

Kode diisi (tidak kosong) adalah Kode tidak sama dengan Kosong (Kode yaitu A7, tidak sama dengan adalah operator logika dengan simbol <>, Kosong diwakili dengan tanda petik dua dua kali tanpa ada karakter apapun diantaranya “”)

Sehingga formulanya adalah:

=IF(A7<>””;VLOOKUP(A7;nama_barang;2;FALSE);””)

Sedangkan untuk harga satuan syaratnya hampir sama hanya menampilkan datanya saja yaitu nomor index kolom ke 3, sehingga formulanya adalah:

=IF(A7<>””;VLOOKUP(A7;nama_barang;3;FALSE);””)

Lalu untuk kolom Jumlah digunakan syarat:

Jika Kode diisi DAN Banyaknya (QTY) diisi maka jumlah adalah QTY dikali dengan Harga satuan selain itu Kosong.

Untuk tes logika dari fungsi IF adalah Kode diisi DAN Banyaknya (QTY) diisi. Karena tes logika tersebut terdiri dari dua logika maka bisa menggunakan tambahan fungsi AND atau OR untuk menggabungkannya. Sesuai pernyataan tersebut maka sebaiknya gunakan fungsi AND saja. Didapat formulanya adalah:

=AND(A7<>””;C7<>””)

dimana A7 adalah Kode dan C7 adalah QTY.

dari pernyataan:

Jika Kode diisi DAN Banyaknya (QTY) diisi maka jumlah adalah QTY dikali dengan Harga satuan selain itu Kosong.

Didapat formulanya adalah:

=IF(AND(A7<>””;C7<>””);C7*D7;””)

dimana D7 adalah Harga.

 

Untuk Total bisa menggunakan pernyataan:

Jika Jumlah dari Jumlah Harga lebih besar dari nol maka Total adalah Jumlah dari Jumlah Harga selain itu Kosong.

Tes logika menggunakan Jumlah dari Jumlah Harga lebih besar dari nol karena tidak ada penjual yang menjual barang dagangannya gratis, kalaupun ada tentu harus membeli terlebih dahulu salah satu produk yang lainnya. Didapat formulanya dari pernyataan Jumlah dari Jumlah Harga lebih besar dari nol adalah:

=SUM(E7:E13)>0

Sehingga hasil dari pernyataan:

Jika Jumlah dari Jumlah Harga lebih besar dari nol maka Total adalah Jumlah dari Jumlah Harga selain itu Kosong.

Didapat formulanya adalah:

=IF(SUM(E7:E13)>0; SUM(E7:E13);””)

Contoh Blanko Nota bisa anda download disini. Artikel dalam format PDF bisa didownload disini.

LEAVE A RESPONSE

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *