PENGENALAN MICROSOFT EXCEL Microsoft Excel adalah salah satu program spread sheet berbasis Windows yang dibuat oleh Microsoft. Aplikasi program ini banyak digunakan orang untuk mengolah data – data yang berhubungan dengan angka dan perhitungan formula matematis. Dalam aplikasinya. Untuk memulai menggunakan Microsoft Excel, langkah yang dilakukan adalah : 1. Tekan tombol Start pada tampilan Windows 2. Geser kursor mouse sampai pada menu Program 3. Klik menu Microsoft Excel
4. Dan akan terlihat tampilan seperti gambar dibawah ini. Pada gambar dibawah juga dijelaskan tentang bagian-bagian lembar kerja Microsoft Excel.
Baris Judul/Title bar Toolbar standar Baris Menu/Menu bar
Toolbar Formatting
Cell pointer
Sheet tab
Ada beberapa istilah dasar untuk pengoperasian Microsoft Excell yang penting untuk diketahui. 1. Workbook. Workbook bisa dibayangkan seperti sebuah buku, yang mana buku tersebut terdiri dari beberapa halaman untuk menulis, Halaman – halaman dari buku di sebut Sheet. 2. Worksheet. Worksheet merupakan tempat menulis pada masing-masing halaman dalam buku. Worksheet disebut juga lembar kerja atau spreadsheet yaitu tempat untuk mengetik naskah/dokumen. Worksheet terdiri dari kolom dan baris, mulai
dari kolom A sampai dengan IV (256 kolom) dan mulai dari baris nomor 1 sampai dengan baris nomor 65536, 3. Sel/cell, yaitu pertemuan antara kolom dan baris yang dipakai tempat untuk menuliskan data. Tiap-tiap sel mempunyai alamat yakni dengan menyebut nama kolomnya dan nomor barisnya. Contoh sel A2, sel C3, sel F10 dan seterusnya. 4. Range, adalah kumpulan dari beberapa sel yang tidak terpisah. Contoh :
Range A1..E1 terdiri dari sel A1, B1, C1, D1dan E1
Range C2..D4 terdiri dari sel C2, D2, C3, D3, C4 dan D4
Range C2..D4 Sel A2
Ada beberapa macam pengoperasian sheet yang meliputi : 1. Mengaktifkan sheet. Dalam keadaat default, sheet yang ditampilkan sebanyak 3 sheet. Sedangkan sheet yang aktif adalah sheet 1. Untuk mengaktifkan sheet yang lain dapat dilakukan dengan menekan sheet tab yang ada. Contoh : klik sheet tab sheet3 untuk mengaktifkan sheet3. 2. Menambah sheet. Untuk keperluan tertentu, sheet yang telah ditampilkan bisa ditambah sebanyak yang dibutuhkan. Caranya :
a. Klik kanan pada salah satu nama sheet b. Klik insert c. Tekan tombol Enter Selanjutnya sheet baru akan ditambahkan pada sheet tab. Klik insert, tekan tombol Enter Klik kanan salah satu nama sheet
3. Menghapus/menutup sheet. Untuk menghapus/menutup sheet –sheet yang tidak kita kehendaki dilakukan dengan cara : a. Klik kanan pada nama sheet yang akan dihapus b. Klik Delete c. Klik Ok Selanjutnya sheet tersebut sudah tidak nampk lagi pada sheet tab. Jika sheet dihapus maka seluruh isi sheet tersebut juga akan ikut terhapus. 4. Mengganti nama sheet. Untuk keperluan tertentu, nama Sheet1, Sheet2, Sheet3 dan seterusnya bisa kita ganti namanya dengan yang kita kehendaki. Cara yang dilakukan adalah sebagai berikut : a. Klik kanan pada salah satu nama sheet yang akan diberi nama baru b. Klik Rename c. Ketik nama sheet yang dikehendaki d. Setelah selesai, tekan Enter Selanjutnya nama sheet baru tersebut akan nampak pada sheet tabs.
Untuk melakukan operasi pengisian, penghapusan, pengeditan pada Microsoft Excel perlu diketahui juga tentang operasi Pointer/Kursor. Pointer/kursor adalah suatu batang persegi panjang yang terdapat pada lembar kerja (work sheet), yang berfungsi untuk menunjukkan kepada kita di sel mana kita sedang berada. Tombol Anak panah kanan Anak panah kiri Anak panah bawah Anak panah atas Page Up Page Down Ctrl + Ctrl + Ctrl + Ctrl + Home Ctrl + Home Ctrl + End
Fungsi Menggerakkan pointer satu sel ke kanan Menggerakkan pointer satu sel ke kiri Menggerakkan pointer satu sel ke bawah Menggerakkan pointer satu sel ke atas Menggulung dokumen satu layar ke atas Menggulung dokumen satu layar ke bawah Menggerakkan pointer ke kanan (akhir data) Menggerakkan pointer ke kiri (awal data) Menggerakkan pointer ke atas (awal data) Menggerakkan pointer ke bawah (akhir data) Menggerakkan pointer ke awal data Menggerakkan pointer ke awal worksheet Menggerakkan pointer ke akhir data
Untuk memasukkan data pada work sheet (lembar kerja) dilakukan dengan mengarahkan pointer pada sel yang akan dimasuki data yang dituju kemudian dilakukan proses pengisian data. Sebagai contoh dibawah ini adalah hasil ujian 3 mata pelajaran dari 3 siswa. Data tersebut akan dimasukkan ke dalam work sheet. No
Nama
Fisika
Matematika
Kimia
1.
Yudi
40
60
80
2.
Iwan
80
50
40
3.
Rizki
60
70
60
Cara mengisikan data tersebut : 1. Tempatkan pointer pada sel B2, Ketik No 2. Tempatkan pointer pada sel B3, Ketik Nama 3. Tempatkan pointer pada sel B4, Ketik Fisika 4. Tempatkan pointer pada sel B5, Ketik Matematika
5. Tempatkan pointer pada sel B6, Ketik Kimia 6. Dan seterusnya. Maka akan didapatkan hasil pengisian seperti gambar berikut :
Data yang diisikan pada sel seringkali berbeda panjangnya. Sehingga membutuhkan penyesuaian lebar sel untuk merapikannya. Cara yang dilakukan adalah : 1. Arahkan pointer mouse pada pembatas kolom 2. Tekan terus mouse dan drag ke kanan atau ke kiri untuk menyesuaikan lebarnya kolom.
Terdapat beberapa macam pengoperasian sel 1. Mengedit isi sel. Untuk mengedit data diatas misalnya pada isi sel B4 yaitu Rizki diganti menjadi Rizky. a. Tempatkan pointer ke sel B4 b. Tekan tombol fungsi F2 c. Pindahkan kursor ke huruf i dan gantilah dengan huruf y
d. Setelah selesai tekan Enter 2. Mengganti isi sel. Untuk mengganti isi sel B1 yaitu Yudi akan diganti menjadi Yuni a. Tempatkan pointer ke sel B1 b. Ketik langsung Yuni (isi sel tidak perlu dihapus terlebih dahulu, tetapi langsung ditimpa) c. Setelah selesai tekan Enter 3. Menghapus isi sel. Apabila tidak menghendaki suatu isi sel, maka bisa dilakukan dengan menghapus isi sel tersebut dengan cara: a. Tempatkan pointer ke sel yang akan di hapus isinya b. Tekan tombol Delete atau tombol Del Untuk memudahkan proses penggandaan, pemindahan, penghapusan dan pemformatan sekumpulan data dapat dilakukan dengan memblok sekumpulan data tersebut. Untuk membuat blok data dilakukan dengan cara : 1. Menggunakan tombol pada keyboard : a. Tempatkan pointer pada awal sel yang akan di blok b. Tekan tombol shift dan tombol panah secara bersamaan c. Geser tombol panah sampai pada akhir sel yang dikehendaki 2. Menggunakan mouse a. Tempatkan pointer mouse pada awal sel yang akan di blok b. Tekan tombol kiri mouse dan tahan c. Drag pointer mouse sampat pada akhir sel yang dikehendaki
Setelah dilakukan Blok data maka dapat dilanjutkan dengan beberapa operasi lanjutan :
1. Mengcopy data. Cara yang dilakukan adalah : a. Blok data pada sel yang akan dicopy b. Klik Icon Copy atau tekan Ctrl + C pada keyboard c. Tempatkan pointer pada sel kemana data tersebut akan dicopy d. Klik icon paste 2. Menghapus data Cara yang dilakukan adalah : a. blok seluruh data pada sel yang akan dihapus b. Tekan tombol Delete atau Del pada keyboard maka seluruh data yang ter blok akan terhapus. 3. Memindah data Cara yang dilakukan adalah : a. Blok seluruh data pad asel yang akan dipindahkan b. Klik icon Cut atau tekan Ctrl + X pada keyboard c. Tempatkan pointer ke sel tujuan data akan dipindahkan d. Klik icon Paste atau tekan Ctrl + V pada keyboard 4. Memformat data 5. Pemberian Border dan Shading
FORMULA MATEMATIKA SEDERHANA Operator hitung terdiri dari : 1. + digunakan untuk operasi tambah 2. – diganakan untuk operasi kurang 3. * digunakan untuk operasi kali 4. / digunakan untuk operasi bagi 5. ^ digunakan untuk operasi pangkat 6. % digunakan untuk operasi persen Dalam operasi perhitungan, operator hitung memiliki
hirarki (urutan) hitungan
sebagai berikut : 1. ( ) perhitungan dalam tanda kurung 2. ^ pemangkatan 3. /
pembagian
4. * perkalian 5. + dan - penambahan atau pengurangan Contoh :
Total (pada kolom D) dihitung dengan cara mengalikan kolom jumlah dengan kolom harga satuan. Formulanya adalah D5 = B5 * C5. Sedangkan Total penjualan adalah penjumlahan dari kolom total. Formulanya adalah D9 = D5 + D6 +D7 +D8. Atau dengan
menggunakan fungsi SUM. Total penjualan D9=Sum(D5:D8). Hasil akhir yang didapatkan adalah sebagai berikut.
FUNGSI STATISTIK Fungsi statistik terdiri dari berbagai macam fungsi antara lain:
COUNT, digunakan untuk menghitung jumlah data dalam suatu range/cakupan Rumus : COUNT(range)
MAX, digunakan untuk mencari nilai tertinggi dari suatu range/cakupan Rumus : MAX(range)
MIN, digunakan untuk mencari nilai tertinggi dari suatu range/cakupan Rumus : MIN(range)
AVERAGE, digunakan untuk mencari nilai rata-rata dalam suatu range Rumus : AVERAGE(range)
MODE, digunakan untuk mencari populasi terbanyak dalam suatu range (modus) Rumus : MODE(range)
Contoh penggunaan :
Nilai akhir E5=(C5* 40%)+(D5*60%)
Rata-rata nilai akhir E14=AVERAGE(E5:E10) Nilai akhir tertinggi E15=MAX(E5:E10) Nilai akhir terendah E16=MIN(E5:E10) Nilai akhir terbanyak E17=MODE(E5:E10)
FUNGSI LOGIKA Fungsi logika terdiri dari berbagai macam fungsi antara lain:
IF, digunakan untuk membandingkan 2 macam kondisi atau lebih Rumus : IF(perbandingan kondisi;nilai benar;nilai salah)
OR, digunakan untuk membandingkan 2 macam data atau lebih (proses akan dilaksanakan jika salah satu syarat terpenuhi) Rumus : OR(perbandingan1;perbandingan2;...)
AND, digunakan untuk membandingkan 2 macam data atau lebih (proses akan dilaksanakan jika semua syarat terpenuhi) Rumus : AND(perbandingan1;perbandingan2;...)
Contoh penggunaan :
Nilai akhir E4=(C4* 40%)+(D4*60%) Status F4=IF (E4>=60;”Lulus”;”Gagal”) Hasil yang didapatkan adalah sebagai berikut :
Contoh penggunaan :
Nilai akhir E4=(C4* 40%)+(D4*60%) Nilai huruf F4=IF(E4>80;"A";IF(AND(E4>65;E4<=80);"B";IF(AND(E4>55;E4<=65);"C";IF(AND( E4>40;E4<=55);"D";"E")))) F4=IF(E4>8;"BAIK";IF(AND(E4>6;E4<=8);"CUKUP";"KURANG")) Hasil yang didapatkan adalah sebagai berikut :
FUNGSI KEUANGAN Ada beberapa macam fungsi keuangan yang ada di Excell, diantaranya adalah :
PMT (Payment) Kegunaan : untuk menghitung pembayaran angsuran per periode, dari sejumlah pinjaman Rumus
: PMT(bunga;periode;jumlah pinjaman)
SLN (Straight Line) Kegunaan : Untuk menghitung nilai penyusutan dengan metode Garis Lurus (Straight Line) Rumus
: SLN(Harga pokok aktive;nilai residu;umur ekonomis)
DB (Declining Balance) Kegunaan : Untuk menghitung nilai penyusutan dengan metode saldo menurun Rumus
: DB(harga pokok aktiva;nilai residu;umur ekonomis;periode)
SYD (Sum of The years Digits) Kegunaan : Untuk menghitung nilai penyusutan dengan metode jumlah angka tahun Rumus
: SYD(harga pokok aktiva;nilai residu;umur ekonomis;periode)
Contoh aplikasi penggunaan fungsi angsuran:
Uang muka
E7=D7*25%
KPR
F7=D7-E7
Angsuran 5 Th G7=PMT(18%/12;5*12;F7) Hasil yang didapat adalah sebagai berikut :
Contoh aplikasi penggunaan fungsi penyusutan metode garis lurus :
Nilai penyusutan
C7=SLN(15000000;7500000;5) atau C7=SLN($B$7;$D$13;5)
Nilai Residu
D7=B7-C7
Harga Pokok Aktiva B2=D7 Hasil yang didapat adalah sebagai berikut :
Contoh aplikasi penggunaan fungsi penyusutan metode saldo menurun :
Nilai penyusutan th1 C6=DB(15000000;7500000;5;1) atau C6= DB($C$6;$E$12;5;1) Nilai Residu th1
D6=B6-C6
Harga Pokok Aktiva B2=D7 Nilai penyusutan th2 C7=DB(15000000;7500000;5;2) atau C7= DB($C$6;$E$12;5;2) Nilai residu th2
D7=B7-C7
Hasil yang didapat adalah sebagai berikut :
FUNGSI LOOKUP
=VLOOKUP(D6;$B$14:$C$18;2) =HLOOKUP(D6;$F$13:$J$14;2) =E6+F6
TABEL PIVOT Table pivot merupakan analisis canggih yang merangkum informasi dari field tertentu daam sebuah database. Penggunaan fasilitas tabel pivot memungkinkan anda dapat membuat tabel pelengkap yang informatif dan lebih hidup sehingga memudahkan dalam menganalisis data disertai dengan tampilan diagram atau grafik tanpa mengganggu dan mempengaruhi data asli. Dengan kata lain ketika membuat laporan seringkali kita harus menampilkan laporan tersebut dengan bentuk yang berbeda-beda. Langkah untuk mebuat tabel pivot. 1. Pilih menu data, pilih perintah Pivot Tabel 2. pilih sumber data yang anda miliki a. Microsoft excel list or database (untuk data dalam excel pada range tertentu) b. External data source (data yang ditulis dengan program spreadsheet lain) c. Multiple consolidation ranges (data berupa list yang sama namun dengan beberapa range) d. Another pivot tabel (data dari pivot table yang sudah ada) 3. bila anda sebelumnya sudah menentukan range daftar maka pada penetuan range sudah terisi range . Namun jika belum anda blok range dari daftar. 4. kotak dialog tabel pivot ditampilkan dan anda sudah bisa merancang tabel. Pada bagian ini terdapat row, coloumn, data dan page. Umumnya berisi keterangan data label sedangkan untuk bagian data umumnya berisi data numerik seperti penjualan, harga beli. 5. sebagai contoh geser tombol salesman ke bagian row. Menempatkan tombol salesman ke prosisi row berarti setiap nama salesman akan ditampilkan di baris terpisah dalam tabel. Geser tombol type ke bagian kolom. Menempatkan tombol type ke posisi kolom berarti setiap tipe barang akan ditampilkan di kolom terpisah dari tabel pivot. Geser tombol jumlah dan harga ke bagian data. Perhatikan jumlah dan harga berubah menjadi sum of jumlah dan sum of harga.
Salesman Dewa Bima Santo Bahari Dewa Bima Bima Santo Bahari Bahari Bima Bima Santo Santo Santo Dewa Dewa Bahari Dewa Santo
Bulan Jan Jan Jan Jan Jan Feb Feb Feb Feb Feb Feb Feb Mar Mar Mar Mar Mar Jan Jan Jan
Daerah Madiun Madiun Madiun Madiun Ngawi Ngawi Ngawi Ngawi Madiun Madiun Madiun Madiun Madiun Magetan Magetan Magetan Magetan Madiun Madiun Madiun
Type Besar Tanggung Kecil Besar Tanggung Kecil Besar Tanggung Kecil Besar Tanggung Kecil Besar Tanggung Kecil Besar Tanggung Kecil Besar Tanggung
Jumlah 259 157 340 220 236 265 325 125 222 110 128 302 360 260 209 227 305 331 238 225
Harga 1.100.750 471.000 850.000 935.000 708.000 662.500 1.381.250 375.000 555.000 467.500 384.000 755.000 1.530.000 780.000 772.500 964.750 915.000 827.500 1.011.500 675.000
Manajemen Data Latihan 1.
1. Terapkan fasilitas Data Form, Sorting, dan Auto Filter pada tabel berikut !
Penyelesaian:
1. Untuk Data Form: - Letakkan kursor di sembarang bagian tabel, lalu pilih Data Form. Kotak dialog Data Form akan muncul di layar.
Anda bisa menambah, menghapus, memulihkan record yang diubah, dan mencari menggunakan kotak dialog ini. 2. Untuk Sorting: - Tempatkan kursor sel dalam range tabel, atau tempatkan kursor sel di luar tabel yang berbatasan langsung dengan tabel tersebut. - Pilih Data Sort. Kotak dialog Sort akan ditampilkan. - Untuk mencegah judul kolom ikut diurutkan, pilih My List Has Header Row. - Tentukan judul kolom dari data yang akan diurutkan pada kotak Sort By. Kemudian tentukan metode pengurutan, apakah Ascending (dari awal ke akhir, atau dari A ke Z) atau Descending (dari akhir ke awal, atau dari Z ke A) - Pada kotak Then By, pilih judul kolom yang akan diurutkan pada tahap selanjutnya. Jika selesai pilih OK.
3. Untuk Filtering: - Tempatkan kursor dalam tabel, atau tempatkan kursor di luar tabel tapi berbatasan langsung dengan range tersebut. - Pilih Data Filter AutoFilter. Sekarang tabel akan memiliki tanda panah ke bawah (drop-down) pada setiap judul kolom. Anda bisa memilih salah satu option yang ada pada tiap menu drop-down dan lihat hasilnya.
Simpan dengan nama file Latihan12.
Latihan 2.
1. Terapkan fasilitas Data Validation pada kolom Jumlah dari tabel diatas. Data inputan yang diperbolehkan adalah data angka dan nilainya lebih dari 0, gunakan pula fasilitas Input Message dan Error Alert !
Penyelesaian:
1. Sorot kolom jumlah lalu pilih Data Validation. Kotak dialog Data Validation akan muncul pada layar. 2. Pada Tab Settings set hal-hal berikut: - Allow : Number - Data : greater than - Minimum : 0
3. Pada Tab Input Message set hal-hal berikut: - Title : Jumlah Pembelian - Input Message : Masukkan jumlah pembelian
4. Pada Tab Error Alert set hal-hal berikut: - Style : Stop - Title : Data Masukan Salah - Error Message : Anda hanya bisa memasukkan data angka mulai dari 1.
5. Berikut gambar hasilnya:
Simpan dengan nama file Latihan12.
ANALISIS WHAT IF : SCENARIO MANAGER Seringkali, pada sekumpulan data kita ingin tahu apa hasilnya jika salah satu atau lebih data berubah nilainya. Hal itu dinamakan scenario. Walaupun bisa dengan menggunakan banyak kolom untuk menginput berbagai scenario, namun Excel telah menyediakan fasilitas What-If Analysis. Dengan What-If Analysis ini, kita dapat menggunakan berbagai scenario untuk meramalkan hasil dari perubahan data-data. Kita dapat membuat dan menyimpan nilai-nilai kelompok yang berbeda pada worksheet dan kemudian beralih ke salah satu skenario baru untuk melihat hasil yang berbeda. Contoh.
Adi membeli 100 unit jaket pria dari supplier dengan harga beli 60,000. Berarti modal awal Adi di sel B5 adalah 60,000 x 100 = 6,000,000. Adi ingin mengetahui jika menetapkan harga jual sekian dan unit terjual adalah sekian, dalam berapa bulankah Adi akan mendapatkan Breakeven(pulang modal). Cara yang dilakukan adalah Adi menyiapkan semua data seperti tabel di atas. Pertama, Adi tetapkan harga jual 75,000 dan unit terjual adalah 100. Maka Adi mendapatkan keuntungan di sel E5 = (Harga jual-Harga beli) x unit terjual = (E3-B4) * E4 Breakeven di sel E7 = B5/E5&” bulan”
= modal awal : keuntungan
Tanda & (ampersand) dipakai untuk menggabungkan hasil perhitungan B5/E5 dengan kata “bulan”. Lalu, Adi mulai membuat scenario dengan mengubah nilai di sel E3 (Harga Jual) dan E4 (Unit Terjual). Caranya : 1. Klik ribbon Data 2. Klik What-If Analysis 3. Klik Scenario Manager 4. Keluar
Add
scenario
dialog
box
seperti
ini
:
5. Isikan : o
Scenario name : Penjualan Terbaik
o
Changing cells : E3,E4 (yaitu sel yang akan saya ubah-ubah nilainya : Harga jual dan Unit Terjual)
o
Comment : beri keterangan penjelas.
o
Klik OK
o
Keluar Scenario Values dialog box, isikan 75,000 untuk E3 dan 100 untuk sel E4.
o
Klik OK
o
Ulangi langkah ke-5 ini untuk menambah scenario lain. Saya memakai 4 scenario dalam kasus ini yaitu Penjualan Terbaik, Penjualan Sedang, Penjualan Kurang, dan Penjualan Buruk.
6. Setelah semua scenario dibuat, saya akan menampilkan hasilnya. Klik Summary di Add Scenario dialog box lalu pilih Scenario Summary, muncul Scenario Values dialog box dan saya memasukkan sel E7 sebagai result cells. 7. Maka hasilnya sebagai berikut :
Dari gambar diatas terlihat dalam berapa bulan akan terjadi breakeven jika harga jual dan unit terjual berubah-ubah sesuai scenario yang dibuat.
ANALISIS WHAT IF : GOAL SEEK Goal Seek disini berfungsi untuk mencari penyelesaian dari persamaan dengan satu variable yang tidak diketahui. Fitur Goal Seek pada Microsoft Excel ini terletak pada tab Data, What If Analysis.
Contoh 1 2x + 5 = 10, x=? Penyelesaian persamaan ini dapat dihitung menggunakan Goal Seek dengan langkahlangkah sebagai berikut: 1. Tentukan sel tempat kita akan meletakkan fungsi x, misal B2, biarkan sel tersebut kosong
o
2. Selanjutnya, ketika persamaan yang akan kita cari solusinya. Dalam contoh ini, kita ketik =2*B2+5 pada sel A4
o
3. Setelah kita tekan enter, maka hasilnya akan nampak seperti ini
o
4. Persamaan kita adalah 2x + 5 = 10,seharusnya sel A4 bernilai 10 namun karena B2=0, nilai 2*0 + 5 tentulah hanya akan menghasilkan nilai 5. 5. Agar sel A4 bernilai 10, mari kita gunakan fitur Goal Seek dimana goal kita adalah A4=10. Sehingga kita ketikkan data seperti gambar di bawah ini
o
Tetapkan sel (Set Cell), klik A4, agar menjadi (To Value) isi dengan 10, dengan mengubah sel (By changing cell), klik B2. Klik OK
6. Setelah kita klik OK, Excel akan melakukan perhitungan secara otomatis untuk menentukan variabel x (sel B2) agar 2x + 5 = 10 (A4=10) 7. Setelah perhitungan selesai, Excel akan menampilkan hasil seperti gambar di bawah ini
o o
Klik OK untuk menyimpan perubahan yang dilakukan oleh Goal Seek
8. Hasil Akhir, x=2,5
Contoh 2 Kegunaan Goal Seek ini tidak terbatas pada penggunaan yang sederhana saja. Goal Seek ini juga dapat digunakan untuk mencari penyelesaian masalah yang rumit, misal logaritma, logaritma natural, eksponen, dan sebagainya. 1. 2x+ 5 =15, x=? o
Solusi, x= 3,321928
o
2. log x = 90, x=? o
Isikan nilai 1 sebagai nilai mula-mula pada sel B2 agar sel A4 tidak menghasilkan error
o
Lakukan 4 kali Goal Seek untuk mencapai nilai yang diinginkan (A4=90)
o
Solusi, x= 9,999521394849E+89
\
SOLVER Ketika masalah peramalan mengandung lebih dari satu variabel, perlu menggunakan Solver add-in utilitas untuk menganalisis skenario. studi kasus multivariabel adalah sebagai bagian dari masalah keuangan dan pelatihan manajemen operasi. Dengan menggunakan Solver dapat membantu untuk memutuskan berapa banyak produk untuk memproduksi, atau bagaimana harga barang dan layanan. Contoh kasus pada sebuah kedai kopi. Bagaimana sebuah kedai kopi kecil menentukan jenis kopi itu harus dijual dan apa potensi pendapatannya. Sebuah kedai kopi saat ini menjual tiga minuman: reguler kopi, caffe latte premium, dan premium caffe mocha. Saat ini harga kopi biasa di $ 1,25, caffe latte di $ 2,00, dan caffe mocha di $ 2,25, tapi kami tidak yakin apa potensi pendapatan kita dan apa yang kita harus memberikan penekanan kepada setiap minuman. (Meskipun kopi premium membawa lebih banyak uang, bahan-bahan mereka lebih mahal dan mereka mengambil lebih banyak waktu untuk membuat daripada kopi biasa.) Kita dapat membuat beberapa perhitungan dasar dengan tangan, tapi kami ingin struktur penjualan kami data dalam lembar kerja sehingga kita berkala dapat menambah dan menganalisanya dengan menggunakan Solver. Solver adalah sebuah add-in utilitas, sehingga Anda harus memastikan bahwa itu diinstal pada sistem Anda sebelum Anda memulai. Jika perintah Solver tidak pada menu Tools Anda, pilih Tools, Add-Ins, dan pilih Solver Add-In pilihan dalam kotak dialog Add-Ins. Jika Solver tidak ada dalam daftar maka harus menginstalnya terlebih dahulu dengan menjalankan program Setup Office lagi dan memilih dari daftar Excel add-ins. Langkah pertama dalam menggunakan perintah Solver adalah untuk membangun sebuah worksheet Solver-friendly. Ini melibatkan menciptakan sel target untuk menjadi tujuan masalah untuk rumus misalnya, yang menghitung total pendapatan-dan satu atau lebih sel menugaskan variabel yang Solver dapat berubah untuk mencapai tujuan Anda. Worksheet Anda juga dapat berisi nilai-nilai lain dan formula yang menggunakan sel target dan sel-sel variabel. Bahkan, untuk Solver untuk melakukan tugasnya, masingmasing sel variabel Anda harus preseden dari sel target. (Dengan kata lain, rumus di sel target harus referensi dan tergantung pada sel-sel variabel untuk bagian dari perhitungannya.) Jika Anda tidak mengaturnya dengan cara ini, ketika Anda menjalankan
Solver, Anda akan mendapatkan pesan kesalahan, "Nilai-nilai Sasaran Set your tidak bertemu." Gambar 1 menunjukkan lembar kerja sederhana yang dapat kita gunakan untuk memperkirakan pendapatan mingguan untuk toko kopi kami contoh dan untuk menentukan berapa banyak cangkir setiap jenis kopi kami akan perlu menjual. The worksheet dalam gambar muncul dalam modus Formula Auditing, yang diaktifkan oleh Alat memilih, Formula Auditing, Mode Formula Auditing. Your G4 adalah sel target yang menghitung total pendapatan yang ketiga menghasilkan minuman kopi. Tiga baris yang berkumpul di sel G4 ditarik dengan memilih bahwa sel dan Peralatan memilih, Formula Auditing, Preseden Trace. Panah menunjukkan bagaimana rumus dalam G4 sel tergantung pada tiga perhitungan lain untuk hasilnya. (Untuk menghapus panah, pilih Tools, Formula Auditing, Hapus Semua Arrows.) Tiga variabel sel dalam worksheet adalah sel D5, D9, dan D13-ini adalah sel kosong yang nilai-nilainya kita ingin Solver untuk menentukan ketika menemukan cara untuk memaksimalkan pendapatan mingguan kami.Di sudut kanan bawah layar kami adalah daftar kendala kami berencana untuk digunakan dalam peramalan kami. Kendala adalah prinsip membatasi aturan atau membimbing yang mendikte bagaimana bisnis dijalankan. Misalnya, karena fasilitas penyimpanan dan kendala merchandise, kami saat ini hanya mampu menghasilkan 500 cangkir kopi (baik reguler dan premium) per minggu. Selain itu, pasokan cokelat membatasi produksi mochas caffe dengan 125 per minggu, dan pembatasan pendingin susu membatasi produksi minuman kopi premium menjadi 350 per minggu
.
Gambar 1: Perintah pada Tools, Formula submenu Audit membantu Anda memvisualisasikan hubungan antara sel-sel. Di sini sel target tergantung pada tiga sel lainnya, masing-masing berisi formula. Ini struktur kendala masalah, dan kami akan memasukkan mereka dalam kotak dialog khusus ketika kita menjalankan perintah Solver. Worksheet Anda harus mengandung sel-sel yang menghitung nilai-nilai yang digunakan sebagai kendala (dalam contoh ini, G8, G7, dan D13). Nilai-nilai yang membatasi untuk kendala yang tercantum dalam sel G11 melalui G13. Meskipun daftar kendala yang tidak perlu, itu membuat worksheet lebih mudah untuk mengikuti. Jika masalah Solver Anda berisi beberapa variabel dan kendala, Anda akan merasa lebih mudah untuk memasukkan data jika Anda nama sel kunci dan rentang dalam worksheet Anda dengan menggunakan Insert, Nama, Tentukan perintah. Menggunakan nama sel juga memudahkan untuk membaca kendala Solver Anda nanti. Menjalankan Solver Setelah Anda telah mendefinisikan masalah peramalan Anda dalam worksheet, Anda siap untuk menjalankan Solver add-in. Langkah-langkah berikut menunjukkan kepada Anda bagaimana menggunakan Solver untuk menentukan pendapatan mingguan maksimum untuk warung kopi Anda mengingat kendala berikut:
Tidak lebih dari 500 jumlah cangkir kopi (baik reguler dan premium)
Tidak lebih dari 350 cangkir kopi premium (baik caffe latte dan caffe mocha)
Tidak lebih dari 125 caffe mochas
Selain mengatakan pendapatan maksimum, Solver menghitung distribusi optimal kopi dalam tiga kelompok kopi. Untuk menggunakan Solver, ikuti langkah-langkah berikut: 1. Klik target sel-satu yang mengandung formula yang didasarkan pada sel variabel Anda ingin Solver untuk menentukan. Dalam contoh kita, seperti yang ditunjukkan pada Gambar 1, sel target G4. 2. Pilih Tools, Solver. The Solver Parameter kotak dialog akan terbuka, seperti yang ditunjukkan di sini:
3. Jika Target Set your kotak teks sudah tidak mengandung referensi yang benar, pilih kotak teks dan kemudian klik G4 sel untuk memasukkan $ G $ 4 sebagai sel target. 4. Pilih opsi Max mengikuti Equal Untuk label, karena Anda ingin mencari nilai maksimum untuk sel target. 5. Klik tombol di ujung kanan Dengan Mengubah Sel kotak teks untuk menutup kotak dialog. Pilih masing-masing sel variabel. Jika sel-sel berdampingan satu sama lain, cukup pilih grup dengan menyeret seluruh sel. Jika sel-sel yang noncontiguous, seperti dalam contoh kita, tahan tombol Ctrl dan klik setiap sel (ini akan menempatkan koma antara entri sel dalam kotak teks). Sebagai contoh kita, pilih sel D5, D9, dan D13 (tiga sel kosong disediakan untuk jumlah cangkir kopi yang harus dijual dalam kategori masing-masing), yang akan menempatkan nilai berikut dalam kotak Dengan Mengubah Sel teks: $ D $ 5, $ D $ 9, $ D $ 13, seperti yang ditunjukkan di sini:
Tip: Gunakan tombol Guess untuk melihat hasilnya Jika Anda mengklik tombol Guess, Solver mencoba untuk menebak di sel variabel dalam masalah peramalan Anda. Solver menciptakan menebak dengan melihat sel-sel direferensikan dalam rumus sel target. Jangan bergantung pada perkiraan ini, meskipunitu sering salah! 6. Kendala tidak diperlukan dalam semua masalah Solver, tetapi masalah ini memiliki tiga. Klik tombol Add untuk menambahkan kendala pertama menggunakan kotak dialog Add Kendala. Kendala pertama adalah bahwa Anda dapat menjual hanya 500 cangkir kopi dalam satu minggu. Untuk memasukkan kendala ini, klik sel G8 (sel yang berisi formula cangkir total), pilih <= dalam daftar drop-down operator, dan dengan titik penyisipan dalam kotak teks Kendala, klik G11 atau Max_cups jenis, dengan menggunakan karakter garis bawah untuk menghubungkan kata-kata. (. Max_cups adalah nama sel G11 di contoh kita) Kotak Kendala Tambah dialog akan terlihat seperti ini:
Setelah selesai, klik tombol Add di kotak dialog Tambah Kendala untuk memasuki kendala pertama.
Catatan Anda memiliki pilihan untuk mengetik nilai, mengklik sel, atau memasukkan referensi sel atau nama dalam kotak teks Kendala. Jika Anda mengklik sebuah sel yang memiliki nama yang ditetapkan, Excel akan menggunakan nama itu saat Anda menambahkan kendala. 7. Menentukan kendala-Anda dapat menjual kedua hanya 350 kopi premium dalam satu minggu. Dengan titik penyisipan dalam kotak Reference your teks, klik sel G7 (sel yang berisi formula cangkir premium), pilih <= dalam daftar drop-down operator, dan dalam teks Kendala kotak, Max_premium jenis (nama sel G12 ) atau klik sel G12. Setelah selesai, klik tombol Add untuk memasukkan kendala kedua. 8. Tentukan ketiga kendala-Anda dapat menjual hanya 125 mochas caffe dalam satu minggu. Klik cell D13 (sel variabel yang berisi jumlah cangkir mocha), pilih <= dalam daftar drop-down operator, dan dalam kotak teks Kendala, jenis Max_mocha (nama G13 cell) atau klik sel G13. 9. Klik tombol OK di kotak dialog Tambah Kendala untuk menampilkan semua tiga kendala di kotak Solver dialog. Seharusnya terlihat seperti yang ditunjukkan di sini:
Untuk memodifikasi salah satu kendala yang muncul dalam kotak Solver Parameters dialog, pilih kendala dan klik tombol Change. Untuk menyesuaikan iterasi dan parameter perhitungan dalam utilitas Solver, klik tombol Options dan membuat penyesuaian Anda.
10. Masalah peramalan Anda siap untuk pergi, jadi klik tombol Memecahkan untuk menghitung hasilnya. Setelah berhenti sebentar Solver akan menampilkan kotak dialog Solver Hasil menggambarkan bagaimana analisis optimasi pergi. Jika Solver berjalan ke masalah, Anda akan melihat pesan kesalahan, dan Anda dapat mengklik tombol Help untuk mempelajari lebih lanjut tentang kesulitan. Jika Solver menemukan solusi, Anda melihat kotak dialog berikut:
11. Untuk menampilkan solusi baru dalam worksheet Anda, pilih Jauhkan Solver option Solusi dalam kotak Solver Hasil dialog dan kemudian klik tombol OK. Solver akan menempatkan nilai optimum dalam sel target dan akan mengisi sel variabel dengan solusi yang paling cocok dengan kendala yang Anda tentukan, seperti yang ditunjukkan pada Gambar 2. Dalam contoh ini, anda terbatas untuk menjual 500 cangkir kopi per minggu, Anda dapat mengharapkan maksimal $ 918,75 dalam pendapatan dan distribusi minuman optimal Anda adalah 150 cangkir kopi biasa, 225 cangkir caffe latte, dan 125 cangkir caffe mocha. Meskipun model ini keuangan tidak mempertimbangkan variabel beberapa bisnis yang realistis, seperti biaya yang berkaitan dengan menjalankan toko dan manfaat dari melakukan pembelian volume, itu tidak membantu Anda untuk meramalkan jauh lebih mudah dan cepat daripada Anda bisa menggunakan pensil dan kertas.
Gambar 2: Ketika Solver selesai, nilai-nilai yang menghasilkan hasil yang optimal dalam sel target akan muncul di sel variabel. 1. Maksimum pendapatan 2. Tiga variabel yang mewakili campuran jumlah minuman untuk kembali maksimum, mengingat kendala Mengedit perkiraan Solver Mungkin fitur terbaik dari perkiraan Solver adalah bahwa Anda dapat dengan mudah mengeditnya untuk mengevaluasi tujuan-tujuan baru dan kontinjensi. Misalnya, jika Anda memutuskan bahwa Anda ingin mendapatkan tepat $ 700 per minggu dari minuman kopi, Anda dapat menggunakan Solver untuk memberi tahu Anda apa kombinasi optimum dari minuman akan. Menetapkan nilai target dalam Solver adalah sedikit seperti menggunakan Goal Seek perintah untuk menentukan nilai variabel yang tidak diketahui, meskipun dengan Solver Anda dapat menggunakan lebih dari satu variabel. Untuk mengedit perkiraan Solver Anda buat dalam latihan sebelumnya untuk menemukan variabel yang dibutuhkan untuk mencapai tujuan tertentu, ikuti langkah berikut: 1. Aktifkan worksheet di mana Anda sebelumnya menggunakan Solver. Dalam selsel variabel, meninggalkan nilai-nilai yang dihasilkan oleh Solver dalam latihan sebelumnya (yaitu, 150 di D5, 225 di D9, dan 125 di D13).
2. Pilih Tools, Solver. The Solver Parameter kotak dialog akan muncul, masih menampilkan target, variabel, dan kendala dari masalah Solver terakhir Anda. Anda akan menyesuaikan ini untuk menghitung tujuan peramalan baru. 3. Pilih Nilai Of pilihan dan 700 jenis dalam kotak teks ke kanan. Nilai Dari pilihan set sel target untuk tujuan tertentu sehingga Anda dapat menentukan campuran variabel yang Anda butuhkan untuk mencapai tonggak Anda. (. Dalam contoh ini, sel-sel variabel mewakili jumlah cangkir dari berbagai jenis kopi) kotak dialog Anda akan terlihat seperti ini:
4. Klik tombol Memecahkan untuk menemukan solusi untuk masalah peramalan Anda. Ketika Solver telah selesai, klik tombol OK di kotak dialog Solver Hasil. Gambar 3 menunjukkan solusi Solver menghasilkan jika, sebelum Anda berlari Solver, sel-sel variabel yang mengandung nilai-nilai yang dihasilkan dalam latihan sebelumnya (yaitu, 150, 225, dan 125). Hasil Solver baru menunjukkan bahwa Anda dapat membuat $ 700 dengan menjual 100 mochas, 175 latte, dan 100 kopi biasa.
Gambar 3: Bila Anda menetapkan tujuan target, Solver menghitung suatu bauran produk optimal yang memenuhi kendala Anda. Bagaimana jika ada lebih dari satu solusi untuk masalah ini? Pada contoh sebelumnya, Solver menetapkan bahwa Anda bisa menjual 100 mochas, 175 latte, dan 100 kopi secara teratur untuk mencapai tujuan penjualan Anda sebesar $ 700. Tapi Anda juga bisa mencapai angka $ 700 menggunakan campuran produk yang berbeda, misalnya, Anda bisa menjual 94 kopi reguler, 151 latte, dan 125 mochas mencapai $ 700. (Menggunakan campuran ini, pendapatan Anda benar-benar akan menjadi $ 700,75.) Jadi, bagaimana Solver memutuskan apa bauran produk optimal akan? Solver itu hanya dimulai dengan angka saat ini di sel variabel dan disesuaikan mereka sampai menemukan solusi yang dapat diterima (tunduk pada batasan yang dijelaskan dalam contoh sebelumnya). Inilah sebabnya, jika Anda menggunakan nilai awal yang berbeda dalam sel variabel sebelum Anda menjalankan Solver, Anda bisa mendapatkan hasil yang berbeda dari masalah dengan beberapa solusi. Bagaimana jika Solver mencapai batas iterasi tanpa mencari solusi ? Nilai-nilai yang dimulai pada sel-sel variabel dapat mempengaruhi solusi: Solver mungkin gagal untuk menemukan solusi atau mungkin mengatur waktu keluar sebelum mencapai solusi. Masukkan nilai dalam sel variabel yang jatuh dekat dengan apa yang
Anda percaya nilai-nilai akhir akan. Jika Solver masih mencapai batas iterasi tanpa tiba di solusi, Anda dapat menyesuaikan nilai awal dan restart atau klik Lanjutkan untuk menggunakan waktu solusi maksimal. Anda dapat mengatur kedua iterasi maksimum dan waktu maksimum dengan menggunakan tombol Options di kotak dialog Solver Parameters. Jika Anda ingin menggunakan campuran produk tertentu, Anda dapat mengambil keuntungan dari cara Solver mencapai hasilnya. Masukkan nilai-nilai yang Anda pikir mungkin dapat diterima dalam sel variabel sebelum Anda menjalankan Solver, dan Excel akan menggunakan mereka sebagai nilai-nilai awal ketika menghitung solusi Contoh : PT. Sejatera merupakan perusahaan yang bergerak dalam bidang industri keperluan olah raga seperti bola voli dan bola kaki. Bahan yang digunakan untuk memproduksi bola kaki dan bola voli adalah karet dan kulit. Untuk produksi 1 bola kaki memerlukan 3 ons karet dan 4 meter kulit. Sedangkan bola voli diperlukan 2 ons karet dan 3 meter kulit. Keuntungan yang didapat untuk bola voli per unitnya adalah Rp 150.000,- dan bola kaki per unitnya adalah Rp 170.000,-. Bahan yang tersedia untuk karet adalah 400 ons dan 900 meter kulit. Permasalahan yang harus dipecahkan : Hitung jumlah produksi yang dapat memberikan keuntungan maksimal. Dalam memproduksi masing-masing bola harus bernilai bilangan bulat tidak boleh dibawah nol. Hitung sisa kelebihan bahan yang tersedia jika sudah ditemukan produksi. Penyelesaian : Buatlah table di microsoft excel sebagai berikut (A,B,C,D menunjukan judul kolom sehingga tidak perlu dibuat karena sudah ada pada microsoft excel) dan (1,2,3,4,5,6 menunjukan judul baris tidak perlu dibuat karena sudah tersedia di excel. Ini dilakukan untuk mempermudah pengaturan saja):
Table Penyelesaian: A 1 Produk 2 Bola voli 3 Bola Kaki 4 5 6 Bahan tersedia 7 Bahan dibutuhkan 8 9
Sisa Bahan Keuntungan
B C Kebutuhan per unit Karet (ons) Kulit (m) 2 3 2 5
D keuntungan unit (Rp) 150.000 170.000
E per Jumlah Produksi 1 1
400 900 =E3*B3 =C3*E3 +E4*B4 +E4*C4 =B6-B7 =C6-C7 =E3*D3+E4*D4
Perhatikan judul kolom dan isian sesuai dangan cell yang terlihat. Jika ada tanda ”=” (tanpa kutip) di cell itu berarti rumus. Dalam solver yang harus diperhatikan adalah:
Kolom atau cell yang mengalami perubahan.
Dari contoh kasus diatas yang akan mengalami perubahan cell adalah pada kolom jumlah produksi (cell E3:E4). Mengapa ? karena kita harapkan solver-lah yang menentukan jumlah produksi yang tepat dengan kondisi bahan yang tersedia serta mendapatkan keuntungan yang maksimal.
Constraint (batasan/aturan) nyatanya.
Yang harus kita jaga/batasi adalah jangan sampai keuntungan besar tetapi bahan yang tersedia tidak mencukupi. Maka cell B7 tidak boleh lebih besar dari cell B6. Dan cell C7 tidak boleh lebih besar dari cell C6. Jumlah produksi harus lebih besar dari angka 0 dan Jumlah produksi harus bilangan integer.
Dan yang paling penting adalah target cell.
Target cell yang dimaksud adalah sesuai dengan keinginan kita diatas yaitu mencapai keuntungan maksimal. Cell yang menjelaskan keuntungan maksimal adalah cell B9. Berikut langkah-langkah untuk memenuhi perhatian solver diatas (dari point a,b dan c).
1. Letakkan cursor atau klik cell B9. Klik tools lalu pilih solver (ingat tadi saya sudah menjelaskan bagaiman menambah add-in pada solver). Lihat gambar berikut:
Perhatikan bahwa Set Target Cell terisi dengan $B$9. Karena memang kita akan mendapatkan keuntungan maksimal ini ditandai dengan equal to yang terpilih adalah Max. 1. Jika kita milihat gambar diatas maka terlihat By Changing cells terisi dengan $E$3:$E$4 karena memang cell tersebut yang akan mengalami perubahan nilai jumlah produksi sesuai dengan hasil perhitungan yang dilakukan oleh solver. 2. Perhatikan gambar diatas pada Subject to the constraints (saya ingatkan kembali untuk memperhatikan pada ”Dalam solver yang harus diperhatikan adalah” lihat point b). Aturannya adalah bahwa:
Maka cell B7 tidak boleh lebih besar dari cell B6. Dan cell C7 tidak boleh lebih besar dari cell C6.
Jika melihat gambar solver diatas klik tombol Add. Perhatikan gambar berikut:
Disitu terlihat cell $B$7 <= Cell $B$6 sesuai dengan aturan diatas. Klik tombol Add kembali untuk membuat aturan cell $C$7 <= $C$6. Perhatikan gambar berikut.
Jumlah produksi harus lebih besar dari angka 0
Klik tombol Add pada gambar 4. Kemudian pada Cell Reference diisi dengan $E$3:$E$4. Kemudian constraint diisi dengan 0. Sehingga akan terlihat bahwa $E$3:$E$4 >= 0.
Jumlah produksi harus bilangan integer.
Klik kembali tombol Add kemudian pada cell reference diisi dengan $E$3:$E$4 untuk lambang pilih int sedangkan contraint akan terisi otomatis yaitu kata integer. Lihat gambar berikut.
Gambar 5 Klik tombol OK. Maka secara keseluruhan solver akan terlihat sebagai berikut:
Langkah selanjutnya untuk melihat hasil adalah dengan cara klik tombol Solver. Harusnya dengan contoh kasus diatas keuntungan maksimal yang dicapai adalah Rp 31.000.000. Perhatikan gambar berikut.
Lihat pada kolom jumlah produksi berubah sesuai dengan hasil perhitungan yang dilakukan oleh solver.
CONTOH SOAL Perusahaan mebel “Toko Mebel”, membuat meja dan kursi dari kayu. Setiap meja membutuhkan pekerjaan tukang kayu rata-rata selama 4 jam dan pengecatan rata-rata 2 jam; setiap kursi membutuhkan pekerjaan tukang kayu rata-rata 3 jam dan pengecatan rata-rata 1 jam. Dalam satu minggu tersedia 240 jam kerja untuk tukang kayu dan 100 jam kerja untuk pengecatan. Jika dijual, setiap meja menghasilkan keuntungan rata-rata Rp. 700.000 dan setiap kursi Rp. 500.000. Ringkasan data mengenai meja dan kursi ada pada berikut : Pekerjaan
Jam yang dibutuhkan Jam kerja tersedia Kursi
per minggu kerja
Tukang kayu 4
3
240
Pengecatan
1
100
Meja 2
Pertanyaan: Berapa seharusnya produksi meja dan kursi dalam satu minggu kerja agar profit total perusahaan “Toko Mebel” maksimal? Gunakan Solver pada MS Excel.
