Senin, 22 Maret 2010
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.
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.
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:
- 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) - 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 - 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 - Jika anda menginginkan angka pada H5 tidak terlihat, maka:
Format Cell > Number > Custom
Pada Type ketikkan ;;;
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:
- 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) - 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 - 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 - 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 - 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:
Sumber: Andy Pope
Thermometer Chart
Langkah-langkah untuk membuat Thermometer Chart:
- Buatlah column chart dari tabel diatas, ubahlah grafik dari column ke row.
Sehingga terbentuk grafik dengan 4 series, yaitu: Holder, Mercury, Bulb, Frame. - Ubahlah axis dari series Holder ke dalam Secondary Axis, sehingga muncul 2 axis.
- Ubahlah axis dari series Mercury ke dalam Secondary Axis.
- Pada series Holder settinglah Overlapnya = 100.
Format Data Series > Options > Overlap : 100. - Pada series Frame settinglah Overlapnya = 100 dan Gap Width = 0
- 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
- Pada X Axis lakukanlah Format Axis > Patterns
- Lines : None; Tick mark labels : None
- Lines : None; Tick mark labels : None
- 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. - 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 - Buatlah Plot Area-nya transparan
Format Plot Area- Border: None, Area: None
Format Axis Major Gridlines - Border: None, Area: None
- Color: White
- 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 - Aplikasi Penggunaan:
Contoh:
Berikut ini adalah progress produksi harian terhadap target:
Untuk mendownload selengkapnya dapat di klik pada link berikut:
Sumber: Andy Pope
Selasa, 16 Maret 2010
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:
- Misalkan datanya terdapat pada B2:D10
- Insert Chart > Bar > Stacked Bar
Pilihlah data dari B3:D10
Horizontal Axis Labels : B3:B10
Series 1 : C3:C10
Series 2: D3:D10 - Hilangkan Fill Color untuk Series 1 dengan memilih No Fill Color
- Hilangkan Legend
- 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 - Tinggal di modifikasi sesuai keinginan
- Tambahkan Primary Horizontal Gridlines
- Tambahkan Chart Title
- Ubah Fill color
Gantt Chart.xls
Jumat, 12 Maret 2010
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 .....
Langganan:
Postingan (Atom)
