Analyzing Business Data

Jika anda belum mampu memaksimalkan kemampuan Ms. Excel anda untuk menganalisa data dari bisnis anda, eBook ini akan membantu anda. Ebook ini berisikan tentang bagaimana memanfaatkan fitur yang ada di Ms Excel untuk melakukan analisa data.

Analyzing Business Data.chm

Excel Hacks

Jika selama ini hanya fungsi excel yang sederhana dan juga format grafik yang biasa-biasa saja, maka anda dapat meningkatkan kemampuan anda dengan membaca E-book ini. Karena di sini mungkin sesuatu yang belum anda bayangkan sebelumnya bisa dilakukan di Excel.

Excel Hacks.chm

Dynamic Chart lewat Menu Scroll Bar

Salah satu contoh penggunaan grafik ini adalah misal anda ingin mengetahui progres produksi 6 bulanan dibandingkan dengan targetnya

Dengan adanya menu Scrollbar anda dapat menggeser periode bulannya.

Sebagai contoh grafik di atas menunjukkan target vs produksi Feb'08 - Jul'08

Langkah-langkah untuk membuatnya adalah sebagai berikut:

1. Definisikan terlebih dahulu range "Periode_Produksi", "Target", dan "Produksi"
   Insert > Name > Define
   Names in workbook: Periode_Produksi
   Refers to : =OFFSET($A$5,$D$5,0,$E$5,1)
   kemudian
   Names in workbook: Target
   Refers to : =OFFSET($B$5,$D$5,0,$E$5,1)
   kemudian
   Names in workbook: Produksi
   Refers to : =OFFSET($C$5,$D$5,0,$E$5,1)
   
   
2. Buatlah Line Chart
   Pada Source Data, pilih Series. Definisikan series sebagai berikut:
   Name: ="Produksi"
   Values: =dynamic.xls!Produksi (jika nama filenya adalah dynamic.xls)
   Category (X) axis labels: =dynamic.xls!Periode_Produksi
   selanjutnya buatlah series yang kedua
   Name: ="Target"
   Values: =dynamic.xls!Target
   
   
3. Buatlah Scroll Bar dari toolbar Form
   Copy-kan kedalam grafik dan pada tab Control formatlah sebagai berikut:
   Minimum value: 0
   Maximum value: 18 (karena hanya ada 24 periode maka maximum valuenya = 24-periode = 24-6=18)
   Cell link: D5
   
   
4. Jika anda menginginkan angka pada H5 tidak terlihat, maka:
   Format Cell > Number > Custom
   Pada Type ketikkan ;;;

Untuk mendownload selengkapnya dapat di klik pada link berikut:

dynamic.xls

Sumber: Andy Pope

Dynamic Chart lewat Menu Drop-Down List

Jika kita merasa kesulitan untuk membuat grafik yang enak dibaca dengan data seperti tabel di atas maka Dynamic Chart mungkin dapat menjadi salah satu alternatif bagi anda.

Pada menu Drop_Down List anda dapat memilih nama mahasiswa sehingga dapat anda bandingkan dengan nilai rata-rata kelas.

Langkah-langkah untuk membuatnya adalah sebagai berikut:

1. Definisikan terlebih dahulu range "Nama_Mahasiswa" dan "Nilai"
   Insert > Name > Define
   Names in workbook: Nama_Mahasiswa
   Refers to : =OFFSET($B$4,$H$5,0,1,1)
   kemudian tambahkan
   Names in workbook: Nilai
   Refers to : =OFFSET($B$4,$H$5,1,1,5)
   
   
2. Buatlah Column Chart
   Pada Source Data, pilih Series. Definisikan series sebagai berikut:
   Name: =dynamic.xls!Nama_Mahasiswa (jika nama filenya adalah dynamic.xls)
   Values: =dynamic.xls!Nilai
   Category (X) axis labels: =C4:G4
   selanjutnya buatlah series yang kedua
   Name: Rata-rata
   Values: =C12:G12
   Tambahkan judul pada grafik.
   Chart Title: Grafik
   
   
3. Buatlah Combo Box dari toolbar Form
   Copy-kan kedalam grafik dan pada tab Control formatlah sebagai berikut:
   Input range: B5:B11
   Cell link: H5
   Untuk memilih nama mahasiswa anda tinggal memilihnya pada menu Drop-Down List
   
   
4. Modifikasi grafik sesuai dengan keinginan anda.
   Jika ingin memberikan judul grafik yang bisa berubah sesuai dengan Nama_Mahasiswa yang kita pilih, maka:
   Gunakan fungsi Concacenate atau "&" pada cell yang kosong, misal di B14
   Ketikkan : =CONCATENATE("Hasil Tes dari ",INDEX(B5:B11,H5))
   atau ="Hasil Tes dari "&INDEX(B5:B11,H5)
   Klik pada Chart Title kemudian ketik = kemudian klik B14 dan tekan Enter
   
   
5. Jika anda menginginkan angka pada H5 tidak terlihat, maka:
   Format Cell > Number > Custom
   Pada Type ketikkan ;;;

Untuk mendownload selengkapnya dapat di klik pada link berikut:

dynamic.xls

Sumber: Andy Pope

Thermometer Chart

Langkah-langkah untuk membuat Thermometer Chart:

1. Buatlah column chart dari tabel diatas, ubahlah grafik dari column ke row.
   Sehingga terbentuk grafik dengan 4 series, yaitu: Holder, Mercury, Bulb, Frame.
2. Ubahlah axis dari series Holder ke dalam Secondary Axis, sehingga muncul 2 axis.
3. Ubahlah axis dari series Mercury ke dalam Secondary Axis.
4. Pada series Holder settinglah Overlapnya = 100.
   Format Data Series > Options > Overlap : 100.
5. Pada series Frame settinglah Overlapnya = 100 dan Gap Width = 0
6. Pada Primary Y Axis lakukanlah Format Axis> Scale:
   
   • Minimum = 0, Maximum = 140, Major Unit = 10, Minor Unit = 5
   • Format Axis > Patterns > Lines > None
Pada Seconday Y Axis lakukanlah Format Axis> Scale:

• Minimum = 0, Maximum = 140, Major Unit = 10, Minor Unit = 5
  
• Format Axis > Patterns > Lines > None
7. Pada X Axis lakukanlah Format Axis > Patterns
   
   • Lines : None; Tick mark labels : None
     
8. Hilangkanlah Legend
   Aturlah panjang dan lebar dari Plot Area supaya pada Chart Area-nya agar bisa maksimum.
   Aturlah lebar Chart Area.
   Aturlah Gap Width dari series Holder untuk mengatur jarak termometer dengan skala.
   Aturlah ukuran Font dari Primary dan Secondary Axis.
9. Untuk menghilangkan nilai yang negatif maka lakukanlah Format Axis pada primary maupun secondary axis:
   Ubahlah format numbernya ke dalam Custom : General;[White]General;General
   Pastikan backgroundnya transparan, yaitu dengan Format Axis > Font > Background : Transparent
10. Buatlah Plot Area-nya transparan
    Format Plot Area
    
    • Border: None, Area: None
      
Format Axis Major Gridlines

• Color: White
11. Ubahlah Patterns Area supaya menyerupai termometer.
    Holder, Patterns Area: Fill Effects > Gradient : Abu-abu
    Mercury, Patterns Area: Fill Effects > Gradient : Merah
    Bulb, Patterns Area: Fill Effects > Gradient : Merah
    Holder, Patterns Area: None
12. Aplikasi Penggunaan:
    Contoh:
    Berikut ini adalah progress produksi harian terhadap target:

Untuk mendownload selengkapnya dapat di klik pada link berikut:

Thermometer.xls

Sumber: Andy Pope

Gantt Chart

Gantt Chart biasa digunakan untuk menggambarkan schedule dari suatu project. Dengan grafik ini kita bisa mengetahui total waktu yang diperlukan dari suatu project. Masing-masing bar menyatakan waktu yang diperlukan masing-masing tahapan. Dengan adanya visualiasasi grafik ini, kita bisa melihat dan mengidentifikasi overlaping dari masing-masing tahapan.

Langkah-langkahnya adalah sebagai berikut:

1. Misalkan datanya terdapat pada B2:D10
2. Insert Chart > Bar > Stacked Bar
   Pilihlah data dari B3:D10
   Horizontal Axis Labels : B3:B10
   Series 1 : C3:C10
   Series 2: D3:D10
3. Hilangkan Fill Color untuk Series 1 dengan memilih No Fill Color
4. Hilangkan Legend
5. Aturlah Horizontal (Value) Axis
   Axis Options:
   Minimum: misalkan 28/12/2009 maka kita ubah dalam bentuk number yaitu: 40175
   Major Unit : 7 menyatakan minggu
   Number > Custom: dd/mm
6. Tinggal di modifikasi sesuai keinginan
   
   • Tambahkan Primary Horizontal Gridlines
   • Tambahkan Chart Title
   • Ubah Fill color

Untuk mendownload selengkapnya dapat di klik pada link berikut:

Gantt Chart.xls

Vlookup yang bisa menengok ke kiri

Emang Vlookup bisa menengok ke kiri?

Gak bisa boss...

Trus gimana???

Ada fungsi lain yang fungsinya sama dengan vlookup

Caranya?

Misalkan ada data sebagai berikut:

Coba aja pake formula vlookup, hasilnya adalah :

=VLOOKUP(E4,B4:C10,1,0)

hasilnya #N/A

Untuk itu kita gunakan fungsi berikut:

=INDEX($B$3:$B$9,MATCH(E3,$C$3:$C$9,0))

formula INDEX membutuhkan array dan row number

array yang di kita cari datanya adalah $B$3:$B$9

sedangkan untuk row number kita harus membuatnya secara otomatis.

Makanya kita gunakan fungsi MATCH

Didalam fungsi MATCH yang kita perlukan adalah value yang mau kita lookup, array dan match_type

Yang kita lookup adalah E3, array dimana kita mencari data E3 adalah $C$3:$C$9 dan match_type = 0

Copy formula itu ke bawah dan kita dapatkan :

O... gitu to...

Lha iyes to yes...

Alhamdulillah dapat ilmu baru.....

Alhamdulillah dapet sharing ilmu .....

Waterfall Chart

Waterfall Chart biasa digunakan untuk mengetahui efek dari faktor-faktor penyebab perubahan "initial value". Dari grafik ini bisa diketahu kontribusi masing-masing faktor sampai ke nilai akhir. Kontribusi dari faktor tersebut bisa berupa nilai negatif maupun positif. Jika faktor tersebut bernilai negatif maka akan mengurangi "initial value", begitu juga sebaliknya.

Berikut ini adalah contoh penggunaan Waterfall Chart:

Diketahui Target Produksi dan faktor-faktor yang mempengaruhinya sehingga terdapat deviasi antara Target dengan Aktual. Faktor-faktor yang mempengaruhi perbedaan Target dengan Aktualnya diantaranya : Jumlah Alat, Availability Alat (jam ready for use dari alat), Utilisasi (jam alat tersebut digunakan), dan Produktivitas Alat.

Dari data tersebut Waterfall Chart-nya adalah sebagai berikut:

Dari target awal terdapat penambahan nilai (Gain) dari produksi dari jumlah alat sehingga nilainya menjadi naik. Nilai tersebut dikurangi dengan Availability Alat (Loss) karena tidak sesuai dengan target. Begitu seterusnya untuk Utilisasi dan Produktivitas, sehingga didapat nilai Aktual.

Untuk mengetahui cara membuat chart tersebut bisa didownload pada link berikut:

Waterfall.xls

Diagram Pareto

Vilfredo Pareto, seorang ekonom Itali, menemukan aturan 80/20 dengan melakukan studi akan distribusi kekayaan dari berbagai negara. Ia menyimpulkan bahwa 20% minoritas menguasai 80% kekayaan masyarakat. Aturan ini tetap relevan diterakan pada berbagai bidang termasuk dalam inisiatif pengembangan kualitas : 20% dari kecatatan akan menyebabkan 80% dari masalah.

Penelitian lebih lanjut oleh Dr. Juran dalam manajemen kualitas menyatakan aturan vital few and trivial many atau 20% dari sesuatu yang bertanggungjawab akan 80% hasil-hasilnya. Aturan ini juga berarti sesuatu yang sedikit (20%) adalah vital dan yang banyak (80%) adalah sepele.

Contoh, Suatu perusahaan bisnis makanan cepat saji ingin berbenah karena pelanggannya merasa kurang puas. Namun perusahaan itu masih binggung mana yang harus ditangani terlebih dahulu. Berikut ini hasil kuesioner keluhan dari pelanggannya:

Adapun hasil diagram paretonya:

Dari diagram pareto terlihat masalah vital yang dihadapi perusahaan tersebut adalah "Rasa tidak standar" yaitu sebesar 30%.

Berikut ini adalah format excel untuk membuat diagram pareto secara otomatis.

Pareto Chart.xls

Mukadimah

Assalamu'alaikum wr. wb.

Alhamdulillahirobbil 'alamin.

Hari ini 2 Maret 2010 saya mencoba membuat blog yang berisikan sharing tentang Microsoft Excel. Niat ini sudah muncul lama, namun belum juga terealisasikan karena masih belum sadar arti pentingnya sharing ilmu. Sampai pada suatu saat saya melihat web sejenis yang dengan cuma-cuma men-share ilmu "per-excel-an" di kancah dunia business.

Memang tidak banyak yang bisa saya share, namun besar harapan saya bisa bermanfaat bagi siapapun yang membaca baik yang expert maupun pemula. Minimal sebagai pemacu kepada yang expert untuk mau membagi ilmunya dan pemacu bagi pemula untuk terus belajar.

Tak lupa mohon doanya kepada pembaca sekalian agar setiap apa yang saya tuliskan menjadi ilmu yang bermanfaat, yang senantiasa mengalir walaupun penulis meninggal. Dan semoga ini menjadi awal untuk menghantarkan saya menajdi enterpreneur muda yang mandiri yag mampu membuka lapangan pekerjaan baru.

Semoga bermanfaat dan semoga lekas semangat.....

Wassalamu'alaikum wr. wb.

Jakarta Utara,

Selasa Wage - 2 Maret 2010

Wildan Fauzi