Membuat Nota Penjualan Dengan Excel

Nota penjualan merupakan dokumen yang mutlak dibuat sebagai bukti fisik penjualan barang apabila toko tidak memiliki sistem yang komprehensif untuk mencatat transaksi penjualan. Untuk toko-toko kecil membuat nota bisa menggunakan bantuan excel, tidak perlu menggunakan aplikasi Point Of Sale (POS) kalau memang tidak memiliki dana untuk membelinya atau volume transaksi nya masih kecil.

Berikut adalah contoh membuat nota dengan excel menggunakan Fungsi Match dan  Fungsi Index yang telah dibahas pada artikel sebelumnya. Untuk jelasnya silahkan perhatikan screenshoot nota penjualan dibawah ini :

Perhatikan pada gambar diatas, tugas dari operator komputer untuk mengisi nota penjualan diatas hanya mengisi kolom kode barang dan jumlah sedangkan kolom yang lainnya (berwarna abu-abu) akan diisi secara otomatis mengambil dari daftar barang yang disimpan dalam tabel referensi seperti tampak pada gambar dibawah ini :

Untuk membuat nota seperti diatas berikut adalah langkah-langkahnya :

1. Buat tampilan nota seperti tampak pada gambar pertama !

2. Buat daftar barang yang akan jadi referensi seperti tampak pada gambar kedua, pada contoh diatas terdapat 4 buah barang kenyataannya toko atau warung pasti memiliki puluhan bahkan ratusan barang, jadi pada prakteknya data seluruh barang di toko atau warung.

3. Pada tabel referensi kolom nama barang (Cell K5:K8) diberi nama barang, gunakan fasilitas insert name define.

4.  Berikutnya masih pada tabel referensi kolom harga barang (Cell L5:L8) beri nama harga, gunakan faslitas insert name define.

5. Terakhir di tabel referensi beri nama kode pada kolom kode barang (Cell M5:M8) dengan fasilitas insert name define.

6. Pada formulir nota penjualan beri nama harga_satuan pada kolom harga (cell F9:F14) dengan fasilitas insert name define.

7. Pada formulir nota penjualan beri nama jml_terjual pada kolom harga (cell G9:G14) dengan fasilitas insert name define.

8. Pada formulir nota penjualan beri nama harga_x_jml pada kolom harga (cell H9:H14) dengan fasilitas insert name define.

10. Pada Cell G11, buat tanggal secara dinamis untuk membuat tanggal kwitansi dengan fungsi date menggunakan formula :

=now()

11. Agar kolom nama barang otomatis terisi ketika mengisi kode pada kolom kode barang, maka pada kolom nama barang (cell E9) ketik formula diawah ini dan copy pada baris dibawah nya :

=IF(NOT(ISBLANK(D9)),INDEX(data_barang,MATCH(D9,kode,0),1),””) 

Maksud dari formula diatas adalah apabila cell D9 / kolom kode barang  tidak kosong (Fungsi ISBLANK), lakukan pencarian data menggunakan Fungsi Index dan Fungsi Match pada tabel referensi, namun jika D9 kosong maka kolom nama barang pun akan kosong.

12. Berikutnya pada kolom harga baris pertama di formulir nota  ketik formula berikut :

=IF(NOT(ISBLANK(D9)),INDEX(data_barang,MATCH(D9,kode,0),2),0)

Maksud dari formula diatas adalah apabila cell D9 / kolom kode tidak kosong (Fungsi ISBLANK), lakukan pencarian data menggunakan Fungsi Index dan Fungsi Match pada tabel referensi, namun jika D9 kosong maka kolom nama barang pun akan kosong.

13. Pada kolom total (cell H9) ketik formula berikut untuk mengkalikan harga satuan dengan banyaknya barang yang dibeli :

=F9*G9

14. Pada baris Total DIbayar (Cell H15) ketik formula berikut :

=SUMPRODUCT(harga_satuan,jml_terjual)

Formula diatas dimaksudkan menjumlahkan hasil perkalian dari tabel harga dengan tabel Jml pada nota penjualan, atau bisa juga menggunakan formula :

=SUM(harga_x_jml)

Simpan dokumen anda, sebagai latihan silahkan download file latihan diatas.

DOWNLOAD

Fungsi Match

Setelah pada artikel sebelumnya kita membahas tentang fungsi index, kali ini kita masih membahas fungsi yang tergolong dalam kelompok lookup dan referensi. Fungsi match kali ini merupakan penjelasan atas contoh kasus sebelumnya yaitu Mencari Nilai Dengan Fungsi Index #Bag. 2.

Tujuan dari pada fungsi match adalah mencari posisi suatu nilai dalam sebuah array / matriks yang berordo (y X 1) atau matrik dengan banyak baris tetapi satu kolom, bedanya dengan fungsi indek yaitu terletak pada data yang dihasilkan, jika pada fungsi index yang diperoleh adalah data yang dihasilkan berdasarkan posisi baris dan kolomnya sedangkan pada fungsi match yang dihasilkan adalah posisi baris (urutan) dari pada data yang dicari.

Untuk jelasnya perhatikan contoh data dibawah ini :

Perhatikan dari gambar diatas kita bisa mencari posisi suatu data dengan menggunakan fungsi MATCH, sebagai contoh kita mencari posisi data dengan kata kunci pencarian adalah kode barang, maka pada cell D12 pada gambar dibawah ketik formula berikut :

=MATCH(D11,B4:B7,0)

Maksud dari formula diatas adalah kita mencari posisi Data  20091003 (pada cell D11) dari sekumpulan array (B4 sampai B7) dengan tipe pencarian adalah tepat (sama persis, bertipe 0). Hasilnya menunjukan data 20091003 berada pada urutan ke-1.

Selanjutnya perhatikan contoh berikut, kita mencoba kembali mencari data tetapi ada contoh kali ini kata kunci pencarian adalah berdasarkan nama barang !, perhatikan gambar dibawah ini :

Pada contoh diatas kita akan mencari posisi / urutan data dengan kata kunci Buku (Cell D16) dari sekumpulan Array C4 sampau C7, maka formulanya adalah :

=MATCH(D16,C4:C7,0)

Maksud dari formula diatas adalah kita mencari posisi Data  BUKU (pada cell D16) dari sekumpulan array (C4 sampai C7) dengan tipe pencarian adalah tepat (sama persis, bertipe 0). Hasilnya menunjukan data Buku berada pada urutan ke-5.

Sekilas tampak bahwa informasi yang dihasilkan dari fungsi match tidak begitu berarti karena hasilnya pasti angka numeric antara 1 dan seterusnya. Fungsi Match akan sangat berguna apabila digabung dengan fungsi lain semisal fungsi index seperti pada contoh di artikel sebelumnya yaitu  “Mencari Nilai Dengan Fungsi Index #Bag. 2“.

Fungsi INDEX

Artikel kali ini merupakan penjelasan dari fungsi index dari dua artikel contoh kasus sebelumnya yaitu mencari nilai dengan fungsi index bag pertama dan mencari nilai dengan fungsi index bagian kedua.

Fungsi index merupakan salah satu fungsi untuk mengambil sebuah nilai dengan memanfaatkan referensi cell (perpotongan kolom dan garis) dari sekumpulan data (array), untuk jelasnya perhatikan gambar dibawah ini :

Dari contoh diatas kita akan mengambil nilai atas nama Rika untuk mata pelajaran matematika.

Dengan menggunakan fungsi index maka kita hanya menentukan area data (array) indeks baris dan indeks kolom, sehingga fungsi dasar dar fungsi index adalah :

=INDEX(array_data,indek_baris,indek_kolom)

maka untuk menampilkan nilai atas nama Rika untuk mata pelajaran matematika, formulanya adalah

=INDEX(D4:F7,2,3)

Dimana :

D4:F7 adalah array data (data yang akan dicari nilainya / area warna kuning)

2 adalah nomor indek baris untuk nama rika (data urutan baris ke -? dalam array data)

3 adalah nomor indek kolom untuk nilai matematika (data urutan kolom ke-2 dalam array data)

Jadi jika kita akan mencari nilai atas nama Farhan untuk mata pelajaran IPA maka fungsinya adalah :

=INDEX(D4:F7,3,1)

Pengembangan contoh kasus dari fungsi index bisa dibaca pada artikel mencari nilai dengan fungsi index bag pertama

Mencari Nilai dengan Fungsi Index #Bag. 2

Artikel kali ini merupakan lanjutan dari artikel sebelumnya yaitu “Mencari Nilai dengan Fungsi Index #Bag. 1“, pada artikel sebelumnya anda harus melakukan secara manual ketika akan mencari nilai pada suatu cel dengan menggunakan fungsi index. Perhatikan kembali screenshoot dibawah ini :

Sebagai contoh untuk mencari jumlah pendaftar prodi kehutanan – S1 pada tahun 2008 maka anda harus melakukan penentuan index kolom dan index baris secara manual.

Pembuatan formula index apabila penentuan  index kolom dan baris dilakukan secara manual sungguh merepotkan apabila data tersebar dalam spreedsheet yang begitu banyak, nah pada artikel kali ini akan dibuat pengisian cell untuk mencari jumlah pendaftar menggunakan menu, sehingga pengguna tinggal memilih nama jurusan dan tahun pendaftaran maka otomatis akan ditampilkan hasil pencarian data dalam cell. Perhatikan kedua gambar dibawah ini !

Dari kedua gambar diatas untuk mencari jumlah pendaftar anda tinggal memilih program studi – jenjang serta tahun pada drop down yang disediakan. Nah pada aritikel kali ini kita akan memodifikasi artikel sebelumnya menjadi seperti tampak pada kedua gamabr diatas.

Berikut langkah-langkah pengerjaan :

1. Blok cell C7 sampai H11 (C7:H11) dan beri nama data dengan menggunakan fasilitas insert name define.

2. Buat tampilan seperti pada gambar ke-3 (ke-4)

3. Blok cell B7 sampai B11 (B7:B11) dan beri nama prodi dengan menggunakan fasilitas insert name define.

4. Blok cell C6 sampai H6 (C6:H6) dan beri nama tahun dengan menggunakan fasilitas insert name define.

5. Pada tampilan gambar ke-3 (ke-4), simpan kursor di cell K7 untuk membuat drop down jurusan, gunakan fasilitas data validation untuk membuatnya, berikut ketentuan pembuatannya. Pada bagian Allow pilih List selanjutnya pada bagian Source ketik =prodi, sesuaikan namanya sesuai pada langkah 3, jika telah selesai klik OK.

6.  Pada tampilan gambar ke-3 (ke-4), simpan kursor di cell K8 untuk membuat drop down tahun, gunakan fasilitas data validation untuk membuatnya, berikut ketentuan pembuatannya. Pada bagian Allow pilih List selanjutnya pada bagian Source ketik =tahun, sesuaikan namanya sesuai pada langkah 3, jika telah selesai klik OK.

7. Simpan kursor di K9 untuk mencari jumlah pendaftar, dan ketik formula berikut :

=IF(OR(ISBLANK(K7),ISBLANK(K8)),””,INDEX(data,IF(ISERROR(MATCH(K7,prodi,0)),””,MATCH(K7,prodi,0)),IF(ISERROR(MATCH(K8,tahun,0)),””,MATCH(K8,tahun,0))))

Untuk bahan latihan silahkan download filenya :

DOWNLOAD

Mencari Nilai dengan Fungsi Index #Bag. 1

Contoh kasus ini merupakan contoh untuk mencari nilai tunggal baik berupa angka maupun text dalam sekumpulan data, untuk jelasnya perhatikan screenshoot dibawah ini, pada screenshoot dibawah ini terdapat rekapitulasi penerimaan mahasiswa baru pada sebuah universitas dimana data disajikan dalam lajur kolom dan baris per program studi dan tahun penerimaan mahasiswa baru.

Dari contoh kasus diatas bagaimana caranya jika anda ditanya berapa jumlah pendaftar untuk program studi Kehutanan S1 pada tahun 2008 ? maka untuk menjawabnya anda mencari kehutanan dan menarik garis lurus kesebalah kanan dan mengambil perpotongan data dengan kolom 2008 maka anda akan menemukan nilai sebesar 46 !.

Sah-sah saja anda menggunakan cara manual seperti diatas, masalahnya akan timbul apabila jumlah program studi banyak dan tahun pendaftaran lebih dari 5 periode seperti tampak contoh diatas, maka untuk mencarinya dibutuhkan waktu yang tidak sedikit sehingga pekerjaan pencarian darta jadi tidak effisien.

Untuk memudahkan pekerjaan seperti itu maka excel menyediakan suatu fungsi yang diberi nama fungsi INDEX untuk mencari nilai berdasarkan index kolom dan baris.

Berdasarkan contoh diatas maka apabila kita menggunakan fungsi index, untuk mencari jumlah pendaftar program studi kehutanan – S1 pada tahun 2008 maka langkah-langkahnya :

1. Blok  cell C7 sampai H11 (C7:H11) dan beri nama data dengan menggunakan fasilitas insert name define.

2. Pindahkan ke Cell Kosong misal B13 ketik formula berikut :

=index(data,3,2)

formula diatas artinya mencari nilai dari range yang bernama data dimana nilai yang dicari  berada pada baris ke-3 (Kehutanan-s1) dan kolom ke-2 (2008) maka ditemukan nilai 46.

Bagaimana ? masihkah ada kekurangan contoh diatas ? ya masih kekurangannya adalah anda harus mencari nomor urut baris dan nomor urut kolom data diatas tampak mudah karena datanya sedikit namun jika datanya besar akan bermasalah mencari nomor kolom dan baris, nah pada artikel bagian kedua kasus ini akan dimodifikasi sehingga pengguna cukup memilih tahun pendaftaran serta memilih program studi dan secara otomatis akan menghasilkan nilai yang dicari.. don’t mis it

Download File

Pembajakan atau bukan ya ??

Sedikit nge-break tak biasa dari aktifitas sebelumnya pada artikel ini penulis tidak akan menuils artikel tentang excel namun tentang ‘pembajakan’, tak biasanya penulis artikel diluar tema blog ini hal ini dikeranakan adanya penemuan dari seorang teman facebook di daerah jawa (timur apa tengah yah ?? lupa nanyain ) dimana beliau menemukan dua rtikel di blog ini jelas secara nyata dijadikann bahan Lembar Kerja Siswa (LKS) untuk mata pelajaran Teknologi Informasi dan Komunikasi (TIK) SMA kelas XI, hampir 99% redaksi yang tercantum di LKS tersebut mirip dengan artikel asli dari blog ini, apalagi gambar ilustrasi yang digunakan 100% milik penulis hal ini teridentifikasi oleh teman facebook dari ilustrasi tercantum nama penulis, nama istri dan nama anak pada contoh kasus tersebut.

Adapun dua artikel yang digunakan dalam LKS tersebut yaitu :

 

Untuk sedikit membuktikan berikut screen shoot dari LKS yang telah discan oleh teman facebook :

Sedikit ganjalan di hati penulis adalah tidak adanya konfirmasi ke penulis untuk menggunakan sebagaian dari artikel yang ada pada blog milik penulis untuk dijadikan bahan ajar, padahal sekecil apapun juga tulisan tersebut adalah hak cipta (hak intelektual) meskipun penulis tidak menuntuk biaya atas penggunaan materi dalam blog, setidaknya penulis memperoleh konfirmasi dari pengarang LKS atau buku tersebut, apalagi hal ini digunakan untuk media pendidikan yang notabene menjunjung tinggi pengahrgaan atas intelektualitas dan tidak mendidik untuk membajak kepada siswanya.

Sebagai pentutup sedikit penulis kutipan status facebook dari teman facebook tersebut :

temen dpt pr dari dosen ttg excel mnta di ajarin, stelah tak liat aq kaya’ pernah lihat format yg sama. setelah di inget” ternyata formatx tmen sendiri (Teu Wawuh). dosen ngasih pr jiplakan, mahasiswa akhirnya pandai njiplak…dosen kencing berdiri mahasiswa kencing sambil njoki…..(wkwkwkwkw) — with Teu Wawuh.

Sebagai catatan ‘teu wawuh’ adalah nama penulis di facebook …

Lah apalagi ini..kelas dosen ko njiplak …(tak bisa berkata-kata … tuing)..