Category Archives: Studi Kasus

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

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

Membatasi Tipe Data Pada Cell

Microsoft Excel secara garis besar membagi data yang bisa diinput kedalam cell menjadi empat jenis data yaitu text/string, numerc, date dan time. Dan secara default pula cell bisa menerima seluruh tipe data sehingga kita bisa meninput apapun kedalam sebuah cell, misal pada cell A1 anda bisa mengisi tanggal, nama, jam atau bahkan jumlah uang.

Namun adakalanya kita menginginkan agar file excell yang kita buat hanya bisa disi tipe data tertentu saja pada suatu range area cell. Untuk jelasnya perhatikan gambar berikut :

Dari gambar diatas tampak formulir pendataan Kolom nomor pendaftar yang hanya boleh berisi angka, nama pendaftar boleh diisi karakter apapun, tanggal lahir hanya boleh berisi tanggal dari tanggal 01 Jan 1995 s.d 31 Des 1998, dan nila rata-rata Ujian Nasional yang hanya boleh berisi angka decimal dengan rentang 0,00 s.d 10,00

Untuk Jelasnya perhatikan lembar kerja diatas yang merupakan lembar kerja pendataan pendaftar siswa baru pada suatu sekolah. Dari lembar kerja diatas berdasarkan ketentuan pada penjelasan sebelumnya bahwa kolom Nomor pendaftaran hanya boleh diisi angka dengan jangakauan antara 12001 sampai dengan 13000, maka cara pengerjaannya adalah :

Pengaturan Kolom No. Daftar

Simpan pointer di cell B7

Klik ribbon Data kemudian tombol Data Validation

Pada Tab setting tentukan aturan seperti tampak pada gambar dibawah ini !

Kemudian pada tab Error Alert isi seperti tampak pada gambar dibawah ini !

Lakukan copy cell B7 ke B8 sampai B16
Isi dengan data angka antara 12001 sampai 13000, dan jika diisi diluar jangkauan tersebut akan keluar pesan error seperti berikut :

Pengaturan Kolom Tanggal Lahir

Berikutnya kita buat aturan agar kolom tanggal lahir hanya boleh diisi dengan tanggal dengan jangkauan antara 01 Jan 1995 sampai dengan 31 Des 1998, jika tanggal lahir diisi dengan tanggal di luar jangkauan tersebut atau datanya bukan berupa tanggal maka akan tampak pesan error seperti berikut :

Adapun proses pengerjaanya adalah :

Simpan pointer di cell D7

Buka kotak dialog data validation (perhatikan gambar), tentukan seting seperti pada gambar dibawah ini :

Kemudian klik tombol Error Alert dan isi setting seperti tampak pada gambar dibawah ini :

Lakukan copy cell D7 ke cell D8 sampai D16, dan coba lakukan input data.

Pengaturan Kolom Rata-rata Nilai UN

Simpan pointer di cell E7

Buka kotak dialog Data Validation dan pada tab seting isi seperti tampak pada gambar dibawah ini :

Berikutnya pada tab error alert isi seperti tampak pada gambar dibawah ini :

Klik Ok untuk mengakhiri

Copy cell E7 ke cell E8 sampai E16, dan lakukan pengujian, jika nilai yang diinput tidak berada pada jangkauan 0.00 sampai 10.00 maka akan keluar sepert pada gambar berikut :

Membuat Neraca Lajur dengan MS Excel

Neraca lajur dalam akuntansi merupakan alat bantu dalam penyusunan laporan keuangan. Menurut jenis perusahaannya neraca lajur dibagi menjadi dua jenis yaitu neraca lajur perusahaan industri dan neraca lajur perusahaan non industri (dagang dan jasa). Dalam contoh kali ini akan dibuat neraca lajur untuk perusahaan industri dimana contoh ini merupakan contoh kasus dari blog penulis yang lain (Blog Akuntansi Biaya).

Neraca lajur untuk perusahaan industri terdiri dari 6 kolom  yaitu :

  • Neraca Saldo
  • Ayat Penyesuaian
  • Neraca Saldo Setelah Penyesuaian
  • Harga Pokok Produksi
  • Rugi laba
  • Neraca

Pada pembahasan tutorial kali ini akan dibahas cara penyelesaian mulai dari kolom Neraca saldo setelah Penyesuaian, namun sebelum itu kita lakukan pengelompokan rekening terlebih dahulu untuk mempermudah proses posting ke masing-masing kolom. Adapun pengelompokan rekening adalah sebagai berikut :

Sedangkan screenshoot neraca lajur dapat dilihat pada gambar dibawah ini :

Perhatikan kolom ayat penyesuaian, pengisian kolom ayat penyesuaian dengan cara mengambil nilainya dari sheet penyesuaian.

Sedangkan untuk mengisi kolom Debet pada kolom N.S. Penyesuaian digunakan formula sebagai berikut :

=IF(OR(D7+F7-G7<0,E7<>0),0,D7+F7-G7)

Maksud dari formula diatas adalah jika kolom D7+F7-G7 hasilnya adalah nol atau kolom E7 tidak sama dengan 0, maka kolom NS Penyesuaian diisi 0, namun  ketika D7+F7-G7 hasilnya lebih dari 0 maka isi kolom NS Penyesuaian adalah NS D7+F7-G7

Tahap berikutnya mengisi kolom Kredit pada NS Disesuaikan, gunakan formula sebagai berikut :

=IF(OR(E7+G7-F7<0,D7<>0),0,E7+G7-F7)

Maksud dari formula diatas adalah jika kolom E7+G7-F7 kurang dari 0 atau Kolom D7 tidak sama dengan 0 maka kolom Kredit NS disesuaikan isinya adalah 0, namun jika kolom E7+G7-F7 isinya lebih dari 0 maka isi kolom Kredit NS Disesuaikan adalah E7+G7-F7.

Untuk Mengisi kolom Harga pokok produksi kolom Debet gunakan formula berikut :

=IF(C7=5,H7,0)

Untuk Mengisi Harga pokok produksi kolom Kredit gunakan formula berikut :

=IF(C7=5,I7,0)

Untuk Mengisi Rugi  Laba kolom Debet gunakan formula berikut :

=IF(C7=6,H7,0)

Untuk Mengisi Rugi  Laba kolom Kredit gunakan formula berikut :

=IF(OR(C7=6,C7=4),I7,0)

Untuk Mengisi Neraca kolom  Debet gunakan formula berikut :

=IF(C7=1,H7,0)

Untuk Mengisi Neraca kolom  Kredit gunakan formula berikut :

=IF(OR(C7=2,C7=3),I7,0)

UNtuk jelasnya silahkan download file Neraca Lajur !

Download

Konversi Tipe Date Ke Text Untuk Program BIO UN SMA/SMK

Contoh kasus kali ini merupakan bentuk bantuan kepada rekan kerja yang bingung mengkonversi tanggal ke format text ketika rekan kerja sedang mengis laporan Biodata Peserta Ujian Nasioan SMK/SMA. Program yang digunakan dikirim langsung oleh pusat dibangun menggunakan visual foxpro.

Ada satu kejanggalan ketika penulis membongkar database dari program BIO UN buatan Puspendik dimana seluruh field tanggal baik tanggal lahir, maupun tanggal lainnya tidak bertipe date tetapi bertipe character dengan lebar 6 character (entah apa maksud nya programmer BIO UN menggunakan tipe ini ?) sehingga ketika seorang siswa lahir tanggal 19/03/1996 maka data diisi ke field tanggal lahir dengan format 190392, perhatikan struktur table dari database Bio UN

Program tersebut menyediakan utilitas impor dari excel masalahnya operator sekolah terlanjur mengetik tanggal lahir dan tanggal lainnya dengan format yang seharusnya yaitu format date, sehingga ketika dilakukan proses impor ke dalam program dari file excel selalu gagal ! Mau tidak mau maka tanggal harus diformat ke tipe data yang diinginkan oleh program yaitu tipe karakter dengan lebar 6, masalahnya excel tidak memiliki fungsi untuk merubah tanggal ke tipe text kalaupun menggunakan fungsi =text(value) maka akan hasilnya tidak berupa tanggal tetapi bilangan integer, perhatikan gambar dibawah bagaimana ketika tanggal dikonversi ke text dengan fungsi =text() pada excel

Sedangkan program Bio UN menginginkan kolom tanggal tampak seperti pada kolom B pada gambar dibawah ini, maka tentu tidak ada fungsi khusus yang bisa melakukan selain kita membuat formula sendiri untuk merubahnya !

Nah bagaimana membuat formulanya ?? berikut penulis ambil contoh untuk merubah tanggal tipe date pada cell A2 menjadi tipe text dengan lebar enam digit pada cell B2

=CONCATENATE((IF(LEN(DAY(A2))=1;CONCATENATE(“0″;DAY(A2));DAY(A2)));(IF(LEN(MONTH(A2))=1;CONCATENATE(“0″;MONTH(A2));MONTH(A2)));(RIGHT(YEAR(A2);2)))

Wow !! panjang ?? bagaimana logikanya ?? berikut penjelasannya :

  1. Formula diatas intinya mengambil tanggal dengan fungsi day, mengambil bulan dengan fungsi month dan mengambil year dengan fungsi year.
  2. Melakukan pengecekan panjang (LEN) karakter hasil fungsi day dan month jika day dan mont panjangnya hanya satu maka akan digabung dengan angka 0 dengan fungsi CONCATENATE jadi ketika bulan januari akan menghasilkan angka 1 dengan fungsi month karena panjangnya hanya 1 karakter maka diawalnya ditambahkan angka 0 dengan fungsi CONCATENATE begitu pula untuk fungsi day !
  3. Pada bagian akhir menggunakan fungsi year hasilnya akan berupa text sebanyak empat digit tahun namun kita ambil digit ke-3 dan ke-4 menggunakan fungsi right !
  4. Hasil fungsi day akan menghasilkan text antara 01 – 12, sedangkan fungsi month akan menghasilkan text antara 01 -31 dan year berupa tahun dengan format 2 digit digabung seluruhnya dengan fungsi CONCATENATE sehingga hasil akhir pada Cell B2 adalah 190380

Untuk bahan latihan silahkan download file contoh artikel ini !

DOWNLOAD