Tag Archives: ISERROR

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

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

Menyembunyikan Error Perhitungan dengan Conditional Formating

Meskipun excel mampu melakukan perhitungan dengan tepat namun error dalam perhitungan matematis maupun dengan fungsi jika salah satu input bernilai salah maka akan ditampilkan error, error tersebut kadang tidak enak dipandang apabila hasil perhitungan merupakan sebuah laporan yang akan dipublikasikan, nah sebagai ilustrasi perhatikan contoh kasus dibawah ini :

Pada gambar diatas adalah contoh laporan penjualan yang akan dipublikasikan, dari gambar tersebut terdapat error pada cell D5 error terjadi karena dilakukan perhitungan pembagian namun angka pembagi karena bernilai 0 (nol) maka hasiilnya akan ditampilkan warning berupa pesan #DIV/0 (Division by zero), seperti kita ketahui bahwa bilangan berapapun tidak bisa dibagi dengan nol, nah dari kasus diatas bagaimana caranya agar pesan error tersebut tidak ditampilkan ? cara yang akan kita lakukan adalah menyembunyikannya dengan conditional formating.

Silahkan anda buat lembar kerja seperti tampak pada gambar diatas, lakukan perhitungan untuk kolom rata-rata pada cell D4 dengan formula :

=ROUNDDOWN(C4/B4,0)

Rumus diatas melakukan pembagian dan hasilnya dibulatkan kebawah 0 decimal dibelakang koma dengan fungsi ROUNDDOWN, kemudian lakukan peng-copy-an untuk baris dibawahnya.
Tahap berikutnya kita sembunyikan pesan error dengan conditional formating
1. blok cel d4 sampai d7 (kolom rata-rata)
2. Klik icon conditional formating, pilih manage rule (Pada Excel 2003 Pilih Menu FOrmat – Conditional Formating)

3. Pada kotak dialog Conditional Formating Rule Manager, klik New Rule

4. Pada kotak dialoh new formating rule pilih use formula to determine wich cell to format

5. kemudian pada kotak Format value where this formula is true isi dengan
=ISERROR(D4)
6. Kemudiak klik tombol format
7. Pada tab font pilih warna font yang senada / sama warnanya dengan warna bacground cell dalam hal ini hijau

8. Klik OK pada semua kotak dialog sampai hilang kotak dialog
9. Sebagai hasil akhir maka anda akan melihat bahwa error #DIV/0 kini telah hilang !