Bekerja dengan Rumus Ms Excel

Contoh penggunaan Fungsi If

Alamat Absolut Dalam Rumus

Alamat Absolut adalah Alamat sel atau range yang selalu merujuk pada satu tempat .pada penulisan absolut selalu ditambahkan tanda “$” pada nama kolom dan nama barisnya, sebagai contoh penulisan alamat sel “A1” adalah alamat sel relatif, sedangkan penulisan alamat sel “$A$1” adalah alamat absolut.

Fungsi Logika, teks, Tanggal dan Waktu

Beberapa fungsi logika antaralain:
Fungsi Not, And, OR dan If
Dalam menggunakan fungsi logika kita memerlukan operator pembanding

Operator pembanding yang kita gunakan adalah :
Operator Keterangan dan Contoh
= Sama dengan
> Lebih Besar
<>= Lebih Besar atau sama dengan
<= Lebih Kecil atau sama dengan <> Tidak sama

Fungsi Teks dipakai untuk mengolah atau memanipulasi data teks
Beberapa fungsi teks yang sering di pakai :
- fungsi Left
dipakai untuk mengambil sebagian isi teks di mulai dari sebelah kiri.
- fungsi Right
dipakai untuk mengambil sebagian isi teks di mulai dari sebelah kanan.
- Fungsi Mid
Dipakai untuk mengambil sebagian isi teks dari tengah
- Fungsi Len
Untuk menghitung jumlah karakter dalam bentuk teks.
- Fungsi lower
Untuk mengubah semua hurup dalam teks menjadi hurup Kecil.
- Fungsi Upper
Untuk mengubah semua hurup dalam teks menjadi hurup besar
- Fungsi Trim
Menghapus semua spasi pada teks
Fungsi Tanggal dan Waktu
Dipakai untuk mengolah atau memanipulasi data tanggal dan waktu.
- Fungsi date
- Fungsi today
- Fungsi time

Fungsi Vlookup
Digunakan untuk mencari suatu nilai pada kolom paling kiri suatu tabel dan mengambil nilai kolom lain yang di tentukan pada baris yang sama
Bentuk penulisan dari vlookup:
=vlookup(nilai yang dicari;nama tabel;rangelookup)

Fungsi Hlookup
Digunakan untuk mencari suatu nilai pada Baris paling kiri suatu tabel dan mengambil nilai kolom lain yang di tentukan pada baris yang sama
Bentuk penulisan dari hlookup:
=Hlookup(nilai yang dicari;nama tabel;rangelookup)

Grafik

Grafik biasa digunakan untuk menampilkan komposisi data secara visual, menggunakan gambar atau simbol tertentu sehingga mempermudah pemahaman dan pengertian tentang data secara menyeluruh.

Membuat grafik

Langkah – langkah membuat grafik :
- dari menu insert, pilih chart atu klik icon chart wizard.
- Tentukan pilihan grafik
- Tentukan range data
- Menentukan judul grafik.

Dalam linear programming, masalah kita tersebut dapat diformulasikan dalam model matematik yang meliputi tiga tahap :

A. Variabel Keputusan: Menentukan variabel yang tak diketahui (variabel keputusan) dan menyatakan dalam simbol matematik

B. Fungsi tujuan: Membentuk fungsi tujuan yang ditunjukkan sebagai suatu hubungan linier (bukan perkalian) dari variabel keputusan

C. Fungsi kendala: Menentukan semua kendala masalah tersebut dan mengekspresikan dalam persamaan dan pertidaksamaan yang juga merupakan hubungan linier dari variabel keputusan yang mencerminkan keterbatasan sumberdaya masalah itu.

Ok, setelah mempelajari tulisan pada seri 1, 2, 3, dan 4 dari seri Solver, sebagai latihan coba Sdr. Rumuskan bagaimana menformulasikan secara matematis variabel keputusan, fungsi tujuan dan fungsi kendala dari kasus kita ini.

Nah setelah merumuskan model linear programming tersebut, sekarang kita masuk ke aplikasinya dalam Solver Excel untuk memecahkan (mencari optimisasinya).

Buka program Excelnya, dan perhatikan tampilan di bawah ini:

Judul-judul dan nama-nama silakan Anda ketik, sesuai dengan keinginan (asal selnya jangan berbeda ya, nanti bingung ngikutin). Atau silakan saja ikuti seperti tampilan diatas.
Ketik biaya pengiriman per unit barang dari masing-masing pabrik ke masing-masing gudang mulai dari sel C10 sampai sel F15
Ketik biaya pengiriman per unit barang dari masing-masing pabrik ke masing-masing daerah mulai dari sel C18 sampai sel G23
Ketik biaya pengiriman per unit barang dari masing-masing gudang ke masing-masing daerah mulai dari sel C26 sampai sel G37

Setelah itu sediakan range untuk hasil optimisasi, dengan cara lihat pada tampilan di bawah ini:

Pada sel G41 ketik rumus =SUM(C41:F41), copy sampai sel G46.
Pada sel C47 ketik rumus =SUM(C41,C44), copy pada range C47:F49
Pada range C50:F52, ketik kapasitas masing-masing gudang berdasarkan masing-masing produk
Pada sel H55 ketik rumus =SUM(C55:G55), copy sampai sel H60.
Pada sel G63 ketik rumus =SUM(G41,H55), copy sampai sel G68.
Pada range H63:H68, ketik kapasitas masing-masing pabrik berdasarkan jenis produk
Pada sel H72 ketik rumus =SUM(C72:G72), copy sampai sel H83.
Pada sel I72 ketik rumus =C47, copy sampai sel I83
Pada sel C84 ketik rumus =SUM(C55,C58,C72,C75,C78,C81), copy pada range C84:G86.
Pada range C87:G89 ketika kendala permintaan masing-masing daerah berdasarkan jenis produk.
Pada sel C91 ketik rumus =SUMPRODUCT(C10:F15,C41:F46)+SUMPRODUCT(C18:G23,C55:G60)+SUMPRODUCT(C26:G37,C72:G83)

Setelah mempersiapkan semua data dan rumus tersebut, kemudian klik Tool kemudian Data Analysis kemudian Solver (urutan ini kadang-kadang tidak sama pada berbagai versi MS Office. Yang penting, Anda dapatkan menu Solver, dan kemudian di klik).

Selanjutnya akan muncul tampilan Solver Parameters berikut:

Isikan (atau blok) Set Target Cel dengan $C$91 (lokasi hasil total biaya). Klik Equal To: pada Min. Isikan (atau blok) By Changing Cells: dengan $C$41:$F$46,$C$55:$G$60,$C$72:$G$83 (lokasi hasil perhitungan barang yang dikirimkan). Kemudian klik Add untuk mengisikan fungsi kendala 1.

Selanjutnya akan muncul tampilan berikut:

Isikan Cell Reference, tanda dan Constraint sesuai dengan tampilan di atas. Ini artinya, kita menyatakan bahwa barang yang dikirimkan dari pabrik baik ke gudang maupun ke daerah pasar harus lebih kecil atau sama dengan kapasitas produksi pabrik.

Klik OK, kemudian klik Add, dan isikan lagi fungsi kendala kedua, seperti tampilan berikut:

Isikan Cell Reference, tanda dan Constraint sesuai dengan tampilan di atas.

Ini artinya, kita menyatakan bahwa barang yang dikirimkan dari pabrik ke masing-masing gudang harus sama atau lebih kecil dari kapasitas gudang, seperti yang kita nyatakan pada fungsi kendala.

Klik OK, kemudian klik Add, dan isikan lagi fungsi kendala ketiga, seperti tampilan berikut:

Isikan Cell Reference, tanda dan Constraint sesuai dengan tampilan di atas. Ini artinya, kita menyatakan bahwa barang yang dikirimkan ke daerah pasar harus lebih besar atau sama dengan permintaan pasar.

Klik OK, kemudian klik Add, dan isikan lagi fungsi kendala keempat, seperti tampilan berikut:

Isikan Cell Reference, tanda dan Constraint sesuai dengan tampilan di atas. Ini artinya, jumlah barang yang dikirimkan ke masing-masing gudang harus sama dengan jumlah barang yang keluar dari gudang

Setelah itu klik OK, maka akan muncul kembali tampilan Solver Paramaters. Selanjutnya, klik Options, maka akan muncul tampilan berikut:

Contenglah Assume Linear Model untuk menyatakan model kita adalah model Linear Programming. Contenglah Assume Non-Negative untuk menyatakan dalam fungsi kendala kita tidak boleh ada nilai produk yang negatif (Pilihan-pilihan lain kita abaikan dulu). Kemudian klik OK.

Setelah klik OK, akan muncul kembali tampilan Solver Parameter seperti sebelumnya. Setelah itu klik Solve. Akan muncul tampilan berikut:

Klik OK, maka akan keluar hasil optimisasi yang kita inginkan (untuk sementara pilihan lain kita abaikan dulu).

Perhatikan pada range C41:F46. Ini merupakan saran untuk pengiriman barang berdasarkan jenisnya dari masing-masing pabrik ke masing-masing gudang. Dari pabrik 1 kirimkan produk 1 ke gudang 1 sebanyak 35.000 unit, produk 2 sebanyak 2.000 unit dan produk 3 sebanyak 20.000 unit. Dengan cara yang sama bisa dibaca untuk angka-angka yang lainnya.

Perhatikan pada range C55:G60. Ini merupakan saran untuk pengiriman barang berdasarkan jenisnya dari masing-masing pabrik ke masing-masing daerah pasar. Dari pabrik 2, kirimkan produk 2 ke daerah A sebanyak 20.000 unit dan produk 3 sebanyak 25.000 unit. Dengan cara yang sama bisa dibaca untuk angka-angka yang lainnya.

Perhatikan pada range C72:G83. Ini merupakan saran untuk pengiriman barang berdasarkan jenisnya dari masing-masing gudang ke masing-masing daerah pasar. Dari gudang 3, kirimkan produk 1 ke daerah A sebanyak 30.000 unit. Dengan cara yang sama bisa dibaca untuk angka-angka yang lainnya.

Selanjutnya, perhatikan nilai-nilai total yang ada pada tampilan hasil tersebut. Semuanya telah memenuhi ke empat fungsi kendala yang kita nyatakan sebelumnya.

Dengan distribusi seperti ini, akan dicapai biaya transpor minimum sebesar Rp 798.300 ribu. (lihat sel C91)

Penjelasan Tahap Transformasi Data Ordinal ke Interval dg Excel

Tulisan ini menjawab pertanyaan Reza, yang meminta arti dari masing-masing rumus dalam tulisan saya mengenai transformasi data ordinal ke interval dengan Excel. Mudah-mudahan juga bermanfaat bagi pembaca yang lain, dan mudah-mudahan juga ada yang bersedia mengembangkan rumus-rumus yang saya buat sehingga bisa lebih sederhana dan mudah.

Pada tulisan tersebut, kasus data kita telah diketik pada kolom A mulai dari baris 16 (atau sel A16) sampai baris 36 (sel A36).

Berikut langkah-langkah dan penjelasan dari rumus-rumusa yang digunakan dalam mentransformasi data tersebut.

Untuk setiap pertanyaan, hitung frekuensi jawaban setiap data kategori/ordinal (pilihan jawaban).

Tahap yang kita lakukan adalah:

Ketik angka 1, 2, 3, 4, 5 secara berurut ke bawah mulai dari sel A4 sampai A8 (lihat kolom row pada tampilan di bawah)
Tulis rumus =COUNTIF(A$16:A36,A4) di sel B4. Selanjutnya kopi sampai ke sel B8. (lihat kolom ordinal pada tampilan di bawah)
Di sel B9 tulis rumus =SUM(B4:B8 )

Kalikan frekuensi dengan nilai ordinal/kategori

Di sel C4 tulis rumus =A4*B4. Copy sampai sel C8 (lihat kolom frek pada tampilan di bawah)

Berdasarkan frekuensi setiap kategori dihitung proporsinya.

Di sel D4 tulis rumus =B4/B$9. Copy sampai sel D8

Dari proporsi yang diperoleh, hitung proporsi kumulatif untuk setiap kategori.

Di sel E4 tulis rumus =D4+E3. Copy sampai sel E8

Hitung nilai Z untuk setiap proporsi kumulatif.

Di sel F4 tulis rumus =NORMSINV(E4). Copy sampai F7. (lihat kolom z_val)

Perhatikan, kita hanya mengcopy kedua rumus tersebut sampai F7 tidak sampai ke F8. Karena kalau kita copy sampai ke F8 akan menghasilkan #NUM! karena adanya nilai numerik yang invalid ketika kita mencoba mencari nilai Z untuk angka 1 (pada sel E8).

Tentukan pula nilai batas Z (nilai fungsi padat probabilitas pada absis Z) untuk setiap kategori.

Di sel G4 tulis rumus =(1/((2*PI())^0.5))*(EXP(-((F4^2)/2))). Copy sampai G7 (lihat kolom z*_val)
(Catatan: ada koreksi yang berharga dari notwelldefined, rumus ini di Excel tersedia dalam bentuk fungsi =NORMDIST(F4,0,1,0). Terima kasih)

Perhatikan, kita hanya mengcopy kedua rumus tersebut sampai G7 tidak sampai ke G8, karena nilai acuan di F8 pada langkah sebelumnya kita kosongkan.

Istilah PI() dalam rumus Excel adalah untuk menghitung nilai π yaitu suatu koefisien yang bernilai 3.141593. Istilah EXP dari rumus Excel adalah untuk menghitung e yaitu suatu koefisien yang bernilai 2.718282

Hitung scale value (interval rata-rata) untuk setiap kategori melalui persamaan berikut:

Kepadatan batas ada pada kolom G, dan daerah di bawah batas atas dan batas bawah dapat dilihat dari proporsi kumulatif, sehingga dalam perumusan Excelnya sebagai berikut:

Di sel H4 tulis rumus =(G3-G4)/(E4-E3). Copy sampai H8 (lihat kolom sv)

Hitung score (nilai hasil transformasi) untuk setiap kategori melalui persamaan:

Scale value adalah nilai yang telah dihitung pada kolom H. Scale Value_minartinya adalah nilai scale value absolut (artinya tanpa memperhatikan tanda positif atau negatif) paling kecil. Dalam rumus di atas tanda absolut adalah І…І. Sehingga dalam rumus Excel dapat dituliskan sebagai berikut:

Di sel I4 tulis rumus =H4+ABS(MIN(H$4:H$8))+1. Copy sampai I8. (lihat kolom interval)

Perhatikan rumus diatas, ABS adalah untuk menetapkan nilai absolut. MIN adalah operasi untuk mencari nilai terkecil.

Transformasi seluruh data asli kita ke interval:

Gunakan fungsi IF untuk mentransformasikan data asli kita, sesuai dengan hasil yang telah kita peroleh pada tahap sebelumnya (di kolom H)

Di sel B17 tulis rumus =IF(A17=1,I$4,IF(A17=2,I$5,IF(A17=3,I$6,IF(A17=4,I$7,I$8)))). Copy sampai B36.

Ok. Berikut tampilan hasil dari latihan yang pernah saya coba mengikuti langkah-langkah di atas

DIarsipkan di bawah: Tip-Trik | yang berkaitan: ekonometrik, excel, statistik

« Solusi Persoalan Transportasi Dua Tingkat Multi Produk (Seri Solver bag.5) Tak Perlu Ajari Kami Berpuasa »

6 Responses to “Penjelasan Tahap Transformasi Data Ordinal ke Interval dg Excel”

Fadli Reza, di/pada Agustus 3rd, 2008 pada 12:44 pm Dikatakan:

wah.. saya benar-benar berterimakasih atas ulasan Bapak yang detail ini. Saya tidak mengira kalau pertanyaan saya akan dibalas bapak dengan 1 halaman tulisan baru..

Sekali lagi terimakasih Pak
Erlangga Putra, di/pada Agustus 3rd, 2008 pada 7:04 pm Dikatakan:

Salam kenal dengan Pak Junaidi..

Saya sangat tertarik dengan method of succesive interval, karena berkaitan dengan TA saya.

Saya memiliki beberapa pertanyaan,semoga bpk dapat membantu saya.

1. Pak kalau skala ordinalnya ada satu nilai yang tidak diisi,apakah berpengaruh thd transformasi skala intervalnya?.Karena hasil kuesioner saya ada beberapa nilai skala yg tidak ada.

Contohnya dari skala 1-4, ada nilai yang terkumpul di skala 3 dan 4. Sehingga nilai 1 dan 2 frek. nya = 0, nah apabila ada nilai 0,untuk mencari nilai Z akan menghasilkan #Num, dan bila dikosongkan baru ada nilainya. Namun dengan proporsi=0 menghasikan z_*val = 0,399. Nilai tsb sama dengan proporsi 0,5 yang juga nilainya = 0,399. Menurut bapak bagaimana mengatasi masalah tersebut?. Jadi apakah metode MSI harus semua nilai skala terisi?.

Saya telah mencoba cara bpk..,sangat bermanfaat sekali.Terima kasih Pak.Hanya masalah pada frek yg nilainya =0 karena tidak dipilih oleh responden pada kuesioner saya. Jadi saya menghitungnya dengan menghilangkan nilai #num & Div/0! dan diganti dengan 0. Saya menghitungya secara manual dan disamakan dengan excel. Tapi saya masih ragu.jd mohon bantuannya dari bpk. Sekali lagi saya ucapkan terima kasih.

Jawab:
Maaf, agak lambat merespons. Kebetulan ada beberapa pekerjaan yang harus diselesaikan. Mengenai ada kategori yang kosong, dalam tulisan saya sebelumnya memang tidak bisa dihitung. Tapi dalam tulisan berikut ini, saya sudah mengembangkan rumusnya sehingga bisa dihitung secara otomatis. Silakan lihat tulisan mengenai “Transformasi Data Ordinal ke Interval dengan Excel (Kasus Kategori Tidak Terisi). Mudah-mudahan bisa membantu.
notwelldefined, di/pada Agustus 5th, 2008 pada 6:02 pm Dikatakan:

terima kasih, informasi ini sangat berguna pak. pusing sama data skala likert yang susah dianalisis…

sekedar nambahin dikit. di G4, kita bisa ganti dengan fungsi =normsdist(F4).
fungsinya sama, untuk mencari nilai pdf dari F4.

Contoh yang saya buat untuk rumus kelulusan tingkat SMP, jadi untuk jenjang lain silahkan lakukan perubahan seperlunya.

Karena ada dua skenario lulus, berarti rumus dimulai dengan operator logika “OR” dst.

Kemudia pada Cel “i5″ dimasukkan sebuah kriteria untuk fungsi COUNTIFdengan isi “>=6″. nila-nilai minimal dapat dimasukkan dalam fungsi atau di dalam sel sebagai referal seperti contoh disini.

Rumus untuk keterangan “Lulus” dan “Tidak Lulus” adalah sbb :

=IF(OR(AND(F2>=I$2,MIN(B2:E2)>=I$3),AND(MIN(B2:E2)>=I$4,

COUNTIF(B2:E2,I$5)=3,F2>=5.25)),”LULUS”,”TIDAK LULUS”)