Tag Archives: Insert Name Define

Penyelesaian Algoritma Decision Tree C4.5 dengan Ms Excel

Contoh kasus kali ini penulis akan memberikan contoh penyelesaian algoritma decision tree C4.5 menggunakan Ms Excel. Namun tidak akan menjelaskan panjang lebar apa tu algoritma decision tree C4.5, untuk jelasnya anda bisa membaca teori nya dari artikel Algoritma Decision Tree C4.5.

Sebagai contoh kasus awal merupakan Soal Ujian Tengah Semester Pascasarjana STMIK LIKMI Bandung, berikut penulis gambarkan deskripsi kasus yang akan dipecahkan menggunakan algoritma decision tree C4.5.  Penulis memiliki serangkaian data  yang tersimpan dalam format tabel dimana data merupakan hasil observasi dari 11 proses produksi dengan tingkat kecepatan, kelembaban dan temperatur yang berbeda-beda sehingga menghasilkan produk dengan dua kategori yaitu produk baik dan produk cacat, dengan algoritma decision tree akan mencoba mengklasifikasikan produk dengan kriteria apa yang akan dikelompokan kedalam hasil baik dan buruk, untuk jelasnya perhatikan tabel dibawah ini :

Untuk menyelesaikan kasus diatas dengan algoritma decision tree C4.5 berikut langkah-langkahnya :

1. Buat nama pada cell area dengan ketentuan sebagai berikut :

  • cell C3 sampai cell C14 diberi nama data_temp
  • cell D3 sampai cell D14 diberi nama data_cepat
  • cell E3 sampai cell E14 diberi nama data_suhu
  • cell F3 sampai cell F14 diberi nama data_hasil

Untuk memberi nama cell seperti diatas silahkan baca artikel Memberi Nama Pada Cell Area

2. Berdasarkan data pada tabel pertama buat calon percabangan, adapun calon percabangan yang mungkin terjadi dari tabel pertama adalah :

3. Berikutnya buat tabel untuk menghitung frekwensi yang muncul dari setiap hasil produk berikut dengan proporsi-nya serta entropy-nya / H(t), sehingga tampak seperti berikut :

Berikut adalah formula-formula yang digunakan untuk mengisi tabel diatas:

  • Cell I17 (Frekwensi Baik)  :
    =COUNTIFS(data_hasil,H17)
  • Cell J17 (Proporsi Baik /Pj) :
    =I17/I19
  • Cell K17 (log2.Pj) :
    =Log(J17,2)
  • Cell L17 (Entropy Baik / -P.log2.Pj) :
    =(-K17)*J17
  • Untuk mengisi baris berikutnya anda cukup melakukan copy paste pada baris dibawahnya:
  • Cell I19 (Total Frekwensi ) :
    =SUM(I17:I18)
  • Cell L19 (Total Entropy ) :
    =SUM(L17:L18)

4. Berikutnya kita akan menghitung Gain dari masing-masing kriteria untuk mencari gain tertinggi yang akan dijadikan cabang keputusan, untuk itu gunakan bantuan tabel seperti dibawah ini :

Adapun formula-formula yang digunakan untuk mengisi kolom-kolom diatas adalah :

  • Cell E25 (Produk dengan hasil baik pada  temperatur tinggi) :
    =COUNTIFS(data_temp,D25,data_hasil,$E$24)
  • Cell F25 (Produk dengan hasil cacat pada  temperatur tinggi) :
    =COUNTIFS(data_temp,D25,data_hasil,$F$24)
  • Cell G25 (Proporsi Produk baik terhadap keseluruhan produk pada temperatur tinggi):
    =IFERROR((E25/(E25+F25)),0)
  • Cell H25 (Proporsi Produk cacat terhadap keseluruhan produk pada temperatur tinggi):
    =IFERROR(F25/(E25+F25),0)
  • Cell I25 (-P(baik) X LOG2 P(baik)) :
    =IFERROR((-G25*LOG(G25,2)),0)
  • Cell J25 (-P(cacat) X LOG2 P(cacat)) :=IFERROR((-H25*LOG(H25,2)),0):
  • Cell K25 (Total) :=I25+J25
  • Cell L25 (P=Temp..) :
    =COUNTIFS(data_temp,D25)/COUNTA(data_temp)
  • Cell M25 (Total X P(Temp=..)) :
    =K25*L25
  • Cell N25 (E(Total*P(Temperatur=..))):
    =SUM(M25:M26)
  • Cell O25 (Gain masing-masing cabang) :
    =$L$19-N25

 

5. Selanjutnya untuk mengisi Cell E26 sampai M26 (Cabang 1 Temperatur Normal), anda cukup mengcopy dari Cell E25 sampai M25 (Cabang 1 Temperatur Tinggi).

6. Berikutnya untuk mengisi cell E27 sampai O25, anda ulangi langkah nomor 4 dengan cara meng-copy cell E25 sampai O25, namun anda ubah pada bagian data_temp didalam formula dengan data_cepat, karena kita akan mengisi calon cabang 2 Kecepatan tinggi dan kecepatan rendah

7. Dan untuk mengisi Cell E28 sampai M28 (Cabang 2 Kelembaban Normal), anda cukup mengcopy dari Cell E27 sampai M27 (Cabang 2 Kelembaban Tinggi).

8. Selanjutnya untuk mengisi cell E29 sampai O29, anda ulangi langkah nomor 4 dengan cara meng-copy cell E25 sampai O25, namun anda ubah pada bagian data_temp didalam formula dengan data_suhu, karena kita akan mengisi calon cabang 3 Kelembaban tinggi, kelembaban normal, dan kelembaban rendah.

9. Dan untuk mengisi Cell E30 sampai M30 dan  Cell E31 sampai M31  (Cabang 2 Kelembaban sedang dan rendah), anda cukup mengcopy dari Cell E29 sampai M29 (Cabang 2 Kelembaban Tinggi).

10. Setelah selesai terisi semua saatnya menarik kesimpulan calon cabang yang mana yang akan dijadikan cabang sesungguhnya, hal ini dilakukan dengan mencari nilai tertinggi dari kolom Gain , dari kolom tersebut didapat gain 0,46 (calon cabang 1) yang akhirnya menjadi cabang, sehingga tahap pertama kita bisa membuat pohon keputusan level pertama seperti berikut :

Dari gambar diatas terlihat dua simbol yaitu simbol kotak dan simbol elips, simbol kotak menandakan keputusan final artinya, jika temperatur nya tinggi maka hasil produksi nya pasti cacat, namun jika temperatur nya rendah kita tidak bisa menentukan apakah cacat atau baik, karena belum final keputusannya maka langkah pertama pada langkah-langkah diatas anda ulang lagi, namun membuang bagian produk yang diproduksi menggunakan temperatur tinggi (proses 1 s.d. 6), sehingga tabel data pertama akan menjadi seperti berikut setelah mengeliminasi hasil produk dengan temperatur tinggi.

  • cell C3 sampai cell C8 diberi nama data_temp_2
  • cell D3 sampai cell D8 diberi nama data_cepat_2
  • cell E3 sampai cell E8 diberi nama data_suhu_2
  • cell F3 sampai cell F8 diberi nama data_hasil_2

Untuk memberi nama cell seperti diatas silahkan baca artikel Memberi Nama Pada Cell Area

11. Hitung  entropi untuk tabel diatas (setelah mengeliminasi temperatur tinggi)

Untuk formula menghitung sama dengan pada langkah ke3, tugas anda hanya mengganti area data_hasil dengan data_hasil_2.

12. Berikutnya kita akan menghitung Gain dari masing-masing kriteria untuk mencari gain tertinggi yang akan dijadikan cabang keputusan, untuk itu gunakan bantuan tabel seperti dibawah ini :

Hitung kolom-kolom diatas menggunakan formula pada langkah-langkah ke-4 tugas anda hanya mengganti cell area data_hasil menjadi data_hasil_2, data_temp menjadi data_temp_2, data_hasil menjadi data_hasil_2.

13. Setelah seluruhnya terisi cari Gain tertinggi, kebetulan kedua calon memiliki gain yang sama yaitu 0.00 jika ditemukan hal demikian gunakan yang yang mana saja dalam hal ini calon cabang 3 yang terpilih untuk cabang ke-dua.

14. Dari hasil tersebut maka Decision Tree-nya adalah sebagai berikut :

Sebagai bahan latihan silahkan download file latihan excel-nya.

DOWNLOAD

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. 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

Mencari Hari Pasaran Jawa Dengan Excel

Hari pasaran merupakan hari dalam kalender jawa dimana dalam satu minggu terdiri dari 5 hari berbeda dengan kalender masehi dimana hari minggu terdapat 7 hari. Urutan hari dalam hari pasaran terdiri dari Pahing, Pon, Wage, Kliwon dan Legi, dengan menggunakan excel kita bisa melakukaan pencarian hari pasaran dengan menggunakan fungsi lookup dan mod. Tentang hari pasaran perhitungan manualnya bisa anda baca di “Blog Spektrum Pemikiranku

Untuk jelasnya perhatikan gambar dibawah ini :

Pada gambar diatas hari pasaran disimpan pada awal hari dengan no index array 0 (bukan 1 !) dimulai dengan nama hari pasaran kliwon. Setelah anda membuat data seperti pada tampilan diatas blok cell A1 sampai dengan F2 dan beri nama dengan nama array_hari menggunakan fasilitas insert name define.

Jika anda ingin mengetahui hari pasaran dan hari kalender masehi , maka cukup memasukan pada kolom tanggal saja, tanggal berapa yang akan dicari hari pasarannya dan hari kalender masehi-nya.

Untuk mengetahui hari kalender masehi (cell B5) gunakan formula :

=TEXT(B4;”ddd”)

Rumus diatas merubah Cell B4 dengan fungsi Text dengan format ddd (coba anda ganti format nya dengan single d atau double d, apa yang terjadi ? )

Sedangkan untuk mencari hari pasaran pada cell B6 adalah :

=HLOOKUP(MOD(B4;5);array_hari;2)

Formula diatas mencari hasil sisa pembagian (modulus) tanggal dengan fungsi mod dimana nilai pembagi nya adalah 5 (sesuai jumlah hari pasaran dalam satu minggu nya) dan hasil pembagi tersebut dicocokan ke tabel array dengan fungsi HLOOKUP.

Sebagai latihan silahkan downoad file kerja artikel ini.

DOWNLOAD

Membuat Rekapitulasi Data Dengan DSUM

Pernahkan anda memiliki kasus untuk merekap data atau dalam pekerjaan anda diminta membuat rekapitulasi atas serangkaian data yang anda miliki. Misal merekap jumlah gaji per divisi pada sebuah institusi, nah untuk jelasnya perhatikan tampilan gambar dibawah ini.

Pada gambar ditampilkan misal anda memiliki serangkaian daftar gaji karyawan

Dari data diatas anda dimina laporan untuk membuat laporan jumlah komponen penghaslan dan potongan serta jumlah per divisi sehingga dimisalkan anda diminta ringkasan laporan daftar gaji seperti tampak pada gambar dibawah ini :

Formula apa yang akan anda gunakan ?? SUM ? ya bisa anda gunaka fungsi sum, namun akan sangat merepotkan apabila data pegawaian sangat banyak. Untuk masalah ini Excel menyediakan fungsi penjumlahan untuk database yaitu fungsi DSUM, fungsi DSUM itu sendiri adalah untuk menjumlahkan angka-angka dalam kolomdi database sesuai dengan kondisi yang anda tetapkan.

Untuk membuatnya rekap atas data yang dimiliki langkah-langkahnya adalah sebagai berikut:

Siapkan data dan simpan di worksheet pertama dan ganti namanya menjadi data.

Siapkan Format Rekapitulasi simpan di worksheet kedua dan ganti namanya menjadi rekap per divisi.

Pada saat anda membuat kolom di bagian rekapitulasi pastikan menggunakan nama kolom sesuai dengan nama kolom yang digunakan pada worksheet data, perhatikan gambar berikut :

Jadi ketika pada sheet data anda menggunakan kata divisi maka pada sheet rekap gunakan juga kata divisi, dan ketika pada kolom divisi di sheet data anda mengetik Gudang dan Logistik maka pada sheet rekap pun gunakan Gudang dan Logostik jangan menggunakan Gudang & Logistik Prinsipnya fungsi ini mencari kecocokan antara data dan kriteria yang anda tentukan !.

Buat nama range area dengan menggunakan fasilitas insert name define, adapun nama nama yang dibuat adalah :

Rumus dasar untuk menggunakan fungsi DSUM adalah :
DSUM(Database,Kolom,Kriteria)

Dari formula dasar diatas untuk mengisi Gaji Pokok Per Divisi, maka formulanya :
=DSUM(data,”Gaji Pokok”,div_keuangan)
=DSUM(data,”Gaji Pokok”,div_kepegawaian)
=DSUM(data,”Gaji Pokok”,div_gudang)
=DSUM(data,”Gaji Pokok”,div_umum)
=DSUM(data,”Gaji Pokok”,div_pemasaran)

Sedangkan untuk mengisi Tunjangan Per Divisi
=DSUM(data,”Tunjangan”,div_keuangan)
=DSUM(data,”Tunjangan”,div_kepegawaian)
=DSUM(data,”Tunjangan”,div_gudang)
=DSUM(data,”Tunjangan”,div_umum)
=DSUM(data,”Tunjangan”,div_pemasaran)

Dan mengisi Potongan Per Divisi
=DSUM(data,”potongan”,div_keuangan)
=DSUM(data,”potongan”,div_kepegawaian)
=DSUM(data,”potongan”,div_gudang)
=DSUM(data,”potongan”,div_umum)
=DSUM(data,”potongan”,div_pemasaran)

Sedangkan untuk mencari mencari Total Penghasilan Per Divisi, gunakan formula berikut :
GajiPokok + Tunjangan – Potongan

Sebagai tahap akhir mencari Total per jenis penghasilan, gunakan formula :
Gaji Pokok : sum(rekap_gapok)
Tunjangan : sum(rekap_gapok)
Potongan : sum(rekap_gapok)

Seperti halnya fungsi agregasi, Selain DSUM juga berlaku DMIN, DMAX, DCOUNT, dan DAVERAGE, untuk penggunaanya anda tinggal mengganti DSUM dengan DMIN, DMAX, DCOUNT dan DAVERAGE.

Sebagai bahan latihan silahkan anda

DOWNLOAD

file latihan session ini !

Menjumlahkan Untuk Kondisi Tertentu

Didalam Excel terdapat fungsi agregasi yang digunakan untuk menjumlahkan serangkaian angka yaitu fungsi sum. Fungsi sum akan sangat bermanfaat jika jumlah data yang akan kita jumlahkan sangat banyak, namun akan sangat merepotkan ketika akan menjumlahkan dengan kondisi tertentu. Nah untuk jelasnya silahkan perhatikan screenshot dibawah ini :

Dari laporan penjualan diatas untuk mencari berapa jumlah penjualan total maka kita gunakan fungsi SUM, namun bagaimana jika kita ingin mencari jumlah penjualan untuk jenis barang Monitor LCD 15′ atau barang yang lainnya ?? Nah jika kasus yang dihadapi adalah kasus seperti ini maka fungsi SUM sudah tidak efisien lagi untuk digunakan sebagai penggantinya kita gunakan fungsi SUMIF.

Adapun langkah-langkah untuk menghitung jumlah dengan fungsi sumif adalah sebagai berikut :

  • Dari gambar diatas buat tabel referensi jenis barang (atau sesuai kebutuhan untuk kasus anda) dengan cara mendata semua jenis barang (lihat tabel kecil sebelah kanan !).
  • Beri nama tabel referensi (Cell H6 s.d. H10) tersebut dengan nama jenis_barang, gunakan fasilitas insert name define.
  • Siapkan tabel sesuai kasus anda, dalam hal ini gunakan gambar tabel diatas.
  • Beri nama kolom jenis barang (Cell D6 sampai D16) yang terjual dengan nama barang_terjual, gunakan fasilitas insert name define.
  • Beri nama kolom harga Satuan (Cell F6 sampai F16) yang terjual dengan nama harga_barang, gunakan fasilitas insert name define.
  • Beri nama Cell F17 dengan nama kriteria, gunakan fasilitas insert name define.
  • Buat List Barang pada cell F17 dengan Data Validation type List, sehingga ketika di klik cell tersebut menampilkan kotak drop down jenis barang.
  • Pada Sel F17 ketik Formula berikut :

    =SUMIF(barang_terjual;kriteria;harga_barang)

Demikian contoh kasus sumif, silahkan anda berexperimen dengan menggunakan kriteria yang lain untuk menjumlahkan berdasarkan kriteria. Untuk mempermudah latihan silahkan anda download file latihannya.

DISINI