RUMUS DAN FUNGSI PADA PERANGKAT LUNAK PENGOLAH ANGKA
Perangkat aplikasi pengolah angka adalah program yang
difungsikan untuk mengolah data berupa angka. Kemampuan aplikasi ini,
antara lain: pembuatan tabel data, pengolahan data dengan melibatkan
penggunaan rumus, grafik, database, dan Iain-Iain. Dalam hal ini, angka
yang sering muncul di bidang bisnis, ilmiah, perencanaan, pembuatan
statistik, pembuatan lembar kerja, pengelolaan angka untuk perhitungan
sebuah data (basis data) dan grafik.
Kali ini kita akan membahas sedikit tentang salah satu pengolah angka yang terkenal dari Microsoft yaitu Ms. Excel versi 2013 yang akan saya bahas. Dalam penggunaan pengolah angka ini banyak penggunaan rumus dalam prosesnya seperti menghitung rata-rata, jumlah, standar deviasi, dan fungsi logika lainnya dalam sebuah pengolahan angka.
1. AVERAGE (RATA-RATA)
Fungsi AVERAGE digunakan untuk menghitung nilai rata-rata dari sejumlah data pada beberapa sel. Aturan penulisannya sebagai berikut:
=AVERAGE(sel awal:sel akhir)
Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi AVERAGE juga bisa dituliskan sebagai berikut:
=AVERAGE(range)
Sebagai contoh, pada tabel berikut ini akan dihitung nilai rata-rata Penjualan per Tahun pada sel C7 untuk data tahun 2009 hingga 2012, yaitu data pada sel C3 hingga C6.
Maka formula yang digunakan adalah:
C7=AVERAGE(C3:C6)
Dan hasilnya akan ditampilkan pada sel C7 seperti tampak pada gambar berikut ini.
2. JUMLAH (SUM)
Fungsi yang pertama dan bisa dibilang merupakan salah satu fungsi yang paling banyak diketahui dan digunakan setiap pengguna aplikasi Excel adalah fungsi SUM. Fungsi SUM digunakan untuk menjumlahkan data dari beberapa sel. Aturan penulisannya sebagai berikut:
=SUM(sel awal:sel akhir)
Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi SUM juga bisa dituliskan sebagai berikut:
=SUM(range)
Sebagai contoh, pada tabel berikut ini akan dihitung nilai Total Penjualan pada sel C7 untuk data bulan Januari hingga bulan April, yaitu data pada sel C3 hingga C6.
Sesuai dengan aturan penulisan formula yang diketahui, maka cara penulisan fungsi SUM untuk menghitung nilai Total Penjualan tersebut adalah sebagai berikut:
Dengan demikian, formula yang telah Anda gunakan untuk menghitung nilai Total tersebut adalah:
C7=SUM(C3:C6)
Untuk memeriksa hasil dari penulisan formula ini, cobalah untuk merubah data pada sel C3 hingga C6 dan lihatlah apakah hasil perhitungan pada sel C7 telah berubah. Jika berubah, maka penulisan formula sudah sesuai.
Penulisan range pada formula ini memang lebih mudah dilakukan dengan menggunakan klik dan drag mouse, namun untuk range yang terlalu jauh, tentunya Anda pun dapat mengetikan alamat range tersebut secara manual.
3. FUNGSI IF (LOGIKA)
Dari semua fungsi logika yang terdapat dalam aplikasi Excel, fungsi IF adalah fungsi logika yang paling banyak diaplikasikan, terutama dalam penerapan formula lanjutan yang melibatkan banyak data dan keputusan bercabang. Kegunaan dari fungsi IF ini sebetulnya sangat sederhana, fungsi ini akan mengambil suatu kondisi tertentu kemudian menentukan nilai TRUE atau FALSE. Nilai TRUE adalah nilai dimana kondisi tersebut terpenuhi dan nilai FALSE adalah nilai untuk kondisi yang tidak terpenuhi.
Aturan penulisan fungsi IF ini adalah sebagai berikut:
=IF(kondisi;nilai_TRUE;nilai_FALSE)
Pada tabel tersebut kolom Status akan diisi dengan kata LULUS atau GAGAL. Namun untuk mengisinya ada kondisi yang harus dipenuhi, misalkan saja siswa akan lulus jika nilainya diatas 60. Dari pernyataan ini maka didapat beberapa point, yaitu:
D3=IF(C3>60;”LULUS”;”GAGAL”)
Arti dari formula tersebut jika diterjemahkan dalam kalimat biasa adalah “Jika nilai dalam sel C3 lebih besar dari 60, maka tuliskan kata LULUS pada sel D3. Namun jika nilainya ternyata tidak lebih besar dari 60 maka tuliskan kata GAGAL pada sel D3”.
Contoh hasilnya dapat Anda lihat pada gambar berikut ini:
Untuk memeriksa apakah formula yang dituliskan sudah benar, cobalah untuk mengganti nilai pada sel C3, misalnya menjadi 40, lalu perhatikan hasilnya. Jika tertulis GAGAL maka artinya formula yang digunakan sudah benar.
Lalu bagaimana mengisikan baris-baris selanjutnya? Menuliskan formula secara manual satu persatu jelas bisa, namun hal ini akan sangat menyita waktu. Cara cepatnya, gunakan saja fitur Auto Fill (Pelajari).
Dan hasilnya semua kolom Status kini telah terisi dengan nilai yang benar.
Fungsi logika IF yang telah dibahas sebelumnya adalah salah satu fungsi yang relatif banyak digunakan, terutama di lingkungan bisnis / perusahaan. Namun fungsi IF ini memiliki keterbatasan karena kondisi yang menjadi acuan hanya satu saja. Bagaimana jika data Anda menggunakan beberapa kondisi sekaligus?
Penerapan fungsi pada aplikasi Excel bisa digabungkan untuk memenuhi satu tujuan atau hasil tertentu. Dan dalam kasus ini, dimana data memiliki beberapa kondisi yang harus dibandingkan, maka fungsi yang digunakan tetap fungsi IF, namun bukan hanya satu IF (IF tunggal) melainkan beberapa IF sekaligus. Dalam aplikasi Excel, penggunaan beberapa IF (Multiple IF) dalam satu formula ini lazim disebut dengan fungsi IF Bercabang atau Nested IF.
Pada dasarnya aturan penulisan fungsi IF bercabang ini sama saja dengan IF tunggal, yaitu:
=IF(kondisi;nilai_TRUE;nilai_FALSE)
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;nilai_FALSE)))
Jika dijabarkan, maka formula tersebut berarti:
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;(IF(kondisi_3;nilai_TRUE3;nilai_FALSE)))))
Atau jika kondisinya ada 4 maka aturan penulisannya menjadi:
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;(IF(kondisi_3;nilai_TRUE3;(IF(kondisi_4;nilai_TRUE4;nilai_FALSE)))))))
Dan demikian seterusnya.
Agar penggunaan fungsi IF bercabang ini lebih jelas, perhatikan contoh tabel berikut ini.
Misalkan saja pada tabel daftar barang tersebut Anda diminta untuk mengisi nilai diskon pada sel C3 hingga C6, dengan kondisi bahwa produk Mouse dan produk Keyboard akan mendapatkan diskon sebesar 5%. Diluar produk tersebut tidak diberikan diskon.
Analisa dari kasus tersebut adalah ada 2 kondisi yang menjadi acuan, yaitu Mouse diskon 5% serta Keyboard diskon 5%. Kedua kondisi ini tentunya harus dimasukan kedalam formula IF satu persatu, boleh diawali dengan kondisi untuk Mouse ataupun untuk Keyboard. Sebagai contoh diambil kondisi untuk Mouse terlebih dahulu. Untuk memperjelas pembahasan, berikut ini dituliskan kembali aturan penulisan fungsi IF.
C3=IF(kondisi;nilai_TRUE;nilai_FALSE)
Kondisi pertama adalah Mouse dengan diskon 5%. Mouse serta produk-produk lainnya berada pada kolom 3 dan diawali dengan alamat sel B3. Maka formulanya akan menjadi:
C3=IF(B3=”Mouse”;nilai_TRUE;nilai_FALSE)
Nilai jika B3 adalah Mouse merupakan nilai_TRUE yaitu 5%, maka formulanya menjadi:
C3=IF(B3=”Mouse”;5%;nilai_FALSE)
Nilai_FALSE digunakan jika ternyata isi sel bukan Mouse, artinya produk lainnya. Dan karena ada satu kondisi lagi yaituKeyboard maka kita masukan kondisi tersebut sebagai kondisi pada fungsi IF berikutnya:
C3=IF(B3=”Mouse”;5%;(IF(B3=”Keyboard”;nilai_TRUE2;nilai_FALSE)))
Jika B3 adalah Keyboard maka nilai_TRUE2 adalah 5% karena kondisinya terpenuhi. Sehingga formulanya akan menjadi:
C3=IF(B3=”Mouse”;5%;(IF(B3=”Keyboard”;5%;nilai_FALSE)))
Jika ternyata B3 tidak berisikan Mouse ataupun Keyboard, maka tidak diberikan diskon sama sekali atau diskon sama dengan 0. Nilai ini tentunya merupakan nilai_FALSE untuk semua kondisi yang telah dibandingkan. Dengan demikian formula akhirnya adalah sebagai berikut:
C3=IF(B3=”Mouse”;5%;(IF(B3=”Keyboard”;5%;0)))
Dan formula tersebut jika dijalankan pada sel C3 tentunya akan menghasilkan nilai yang benar seperti tampak pada gambar berikut ini.
Dan untuk mengisi sel-sel berikutnya seperti biasa Anda dapat menggunakan Auto Fill.
Pelajari baik-baik penggunaan formula IF bercabang ini karena dalam penerapan aplikasi Excel lanjutan Anda akan banyak menggunakan formula ini. Hati-hati dengan penggunaan tanda kurung karena semakin banyak IF yang Anda gunakan maka Anda akan menggunakan tanda kurung yang banyak juga.
4. VLOOKUP
VLOOKUP termasuk kedalam salah satu fungsi yang paling banyak digunakan dalam aplikasi Excel. Sesuai dengan nama dasarnya yaitu lookup yang bisa diartikan melihat atau mencari, maka fungsi ini akan menghasilkan suatu formula untuk mengisi data pada tabel berdasarkan data pada tabel lainnya atau tabel referensi tertentu dengan menggunakan suatu nilai kunci yang spesifik. Awalan huruf V didepan kata lookup merupakan singkatan dari kata Vertical. Istilah vertical ini merujuk pada bentuk tabel referensi yang digunakan, dimana judul kolomnya terletak dibagian atas dan data-datanya tersusun kebawah secara vertikal. Contoh sebuah tabel vertical ini bisa dilihat pada gambar berikut ini.
Aturan penulisan VLOOKUP mungkin bisa dibilang agak rumit bagi yang baru saja mempelajari penggunaan formula pada Excel. Namun jika sudah terbiasa, aturan ini sebetulnya bisa dibilang sederhana, yaitu:
=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)
Penjelasan aturan penulisan tersebut sebagai berikut:
Pada contoh tersebut terdapat 2 buah tabel. Data pada tabel Laporan Penjualan jelas akan selalu bertambah tiap harinya. Setiap kali ada pembeli maka Anda tinggal mengisikan data tanggal, nama pembeli serta kode voucher-nya. Sementara data Voucher serta Harga akan terisi secara otomatis tiap kali Anda mengisikan data pada kolom Kode. Pengisian data secara otomatis ini bisa dicapai dengan melihat tabel referensi yaitu tabel Stok Gudang dan tentunya dengan menggunakan formula VLOOKUP.
Langkah pertama akan diisi dahulu data pada tabel Voucher yaitu data pada sel E12. Untuk mempermudah pembahasan, aturan penulisan VLOOKUP kembali dituliskan sebagai berikut:
E12=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)
nilai_kunci yang digunakan adalah data pada kolom Kode dalam tabel Laporan Penjualan, yaitu sel D12. Alasannya karena Voucher akan bisa terisi dengan bersandar pada data dalam kolom Kode tersebut.
Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;range_tabel_referensi;no_index_kolom;tipe_data)
Kemudian range_tabel_referensi jelas adalah range data pada tabel Stok Gudang yaitu B4:D7.
Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;B4:D7;no_index_kolom;tipe_data)
no_index_kolom Anda tentukan dengan melihat didalam range_tabel_referensi. Dalam hal ini Anda akan mengisi data pada kolom Voucher maka Anda lihat data untuk Voucher tersebut didalam range_tabel_referensi berada pada kolom keberapa?
Dari gambar diatas dapat dilihat bahwa data yang akan diambil berada pada kolom ke-2, maka no_index_kolom yang digunakan adalah 2. Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;B4:D7;2;tipe_data)
Yang terakhir adalah tipe_data. Disini yang akan digunakan adalah FALSE. Penjelasannya adalah karena data tersebut bersifat pasti, misalnya kode S sudah pasti untuk Simpati, kode E sudah pasti untuk Esia, dan demikian seterusnya. Dengan demikian formula lengkap untuk mengisi data pada kolom Voucher tersebut adalah:
E12=VLOOKUP(D12;B4:D7;2;FALSE)
Untuk mengisi data pada sel selanjutnya yaitu sel E13 dan E14 seperti biasanya Anda dapat menggunakan Auto Fill. Namun perhatikan bahwa tabel referensi yang menjadi acuan memiliki range alamat sel yang statis alias tetap. Oleh karenanya, sebelum Anda menjalankan proses Auto Fill maka Anda harus mengunci terlebih dahulu range tabel referensi tersebut agar menjadi sel yang absolut. Hingga formulanya akan menjadi:
E12=VLOOKUP(D12;$B$4:$D$7;2;FALSE)
Dan proses Auto Fill kini dapat dijalankan untuk mengisi data pada sel-sel berikutnya.
Berikutnya, bagaimana mengisi data pada kolom Harga yaitu sel F12 hingga F14? Formula yang digunakan tentunya tetap sama hanya saja no_index_kolom yang berbeda yaitu 3 karena data yang akan diisikan diambil dari kolom ke-3 pada tabel referensi.
Dan berikut ini formula yang digunakan setelah menggunakan sel absolut untuk range tabel referensinya.
F12=VLOOKUP(D12;$B$4:$D$7;3;FALSE)
Pada contoh diatas Anda telah melihat penggunaan tipe data FALSE yaitu tipe data yang pasti. Untuk tipe data TRUEaturan penulisan formula-nya tetap sama, hanya saja data yang akan diisikan adalah data yang tidak pasti atau berada pada range tertentu. Contoh untuk penggunaan tipe data TRUE ini bisa dilihat pada tabel berikut ini.
Anda harus mengisikan formula VLOOKUP pada kolom Nilai dalam tabel Daftar Nilai Siswa, yaitu sel E13 hingga selE15. Nilai yang akan diisikan tersebut mengacu pada range tertentu dalam tabel referensi, yaitu:
=VLOOKUP(D13;$B$4:$C$8;2;TRUE)
Yang harus Anda perhatikan untuk tipe data TRUE ini adalah urutan data pada tabel referensi harus menaik atau tersusun dari data terkecil hingga data terbesar.
5. STANDAR DEVIASI
Kita mempelajari nya terlebih dahulu tentang belajar standar deviasi nya menggunakan ms excel sebagai berikut ini ;
rumusnya yang anda tulis di formula excel...:
STDEV (number1, number2,…)
Dengan rincian :
Number1, number2, … yang tertera dan yang kita buat di formula adalah 1-255 argumen yang sesuai dengan sampel populasi nya yang ada . Anda juga dapat menggunakan, array tunggal atau referensi ke array, bukan argumen yang dipisahkan oleh koma (,)
Nah itu dia beberapa tutorial dalam penggunaan rumus di Microsoft Excel, semoga bermanfaat. Sukses untuk kita semua!
post ini saya ambil dari
http://mohamad_ravena.student.ipb.ac.id/perangkat-lunak-pengolah-angka/
Kali ini kita akan membahas sedikit tentang salah satu pengolah angka yang terkenal dari Microsoft yaitu Ms. Excel versi 2013 yang akan saya bahas. Dalam penggunaan pengolah angka ini banyak penggunaan rumus dalam prosesnya seperti menghitung rata-rata, jumlah, standar deviasi, dan fungsi logika lainnya dalam sebuah pengolahan angka.
1. AVERAGE (RATA-RATA)
Fungsi AVERAGE digunakan untuk menghitung nilai rata-rata dari sejumlah data pada beberapa sel. Aturan penulisannya sebagai berikut:
=AVERAGE(sel awal:sel akhir)
Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi AVERAGE juga bisa dituliskan sebagai berikut:
=AVERAGE(range)
Sebagai contoh, pada tabel berikut ini akan dihitung nilai rata-rata Penjualan per Tahun pada sel C7 untuk data tahun 2009 hingga 2012, yaitu data pada sel C3 hingga C6.
Maka formula yang digunakan adalah:
C7=AVERAGE(C3:C6)
Dan hasilnya akan ditampilkan pada sel C7 seperti tampak pada gambar berikut ini.
2. JUMLAH (SUM)
Fungsi yang pertama dan bisa dibilang merupakan salah satu fungsi yang paling banyak diketahui dan digunakan setiap pengguna aplikasi Excel adalah fungsi SUM. Fungsi SUM digunakan untuk menjumlahkan data dari beberapa sel. Aturan penulisannya sebagai berikut:
=SUM(sel awal:sel akhir)
Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi SUM juga bisa dituliskan sebagai berikut:
=SUM(range)
Sebagai contoh, pada tabel berikut ini akan dihitung nilai Total Penjualan pada sel C7 untuk data bulan Januari hingga bulan April, yaitu data pada sel C3 hingga C6.
Sesuai dengan aturan penulisan formula yang diketahui, maka cara penulisan fungsi SUM untuk menghitung nilai Total Penjualan tersebut adalah sebagai berikut:
- Pilih sel C7
- Ketikan tanda =
- Ketikan SUM atau gunakan fitur Intellisense
- Ketikan tanda (
- Klik sel C3 lalu drag ke sel C6
- Ketikan tanda )
- Tekan Enter untuk melihat hasilnya
Dengan demikian, formula yang telah Anda gunakan untuk menghitung nilai Total tersebut adalah:
C7=SUM(C3:C6)
Untuk memeriksa hasil dari penulisan formula ini, cobalah untuk merubah data pada sel C3 hingga C6 dan lihatlah apakah hasil perhitungan pada sel C7 telah berubah. Jika berubah, maka penulisan formula sudah sesuai.
Penulisan range pada formula ini memang lebih mudah dilakukan dengan menggunakan klik dan drag mouse, namun untuk range yang terlalu jauh, tentunya Anda pun dapat mengetikan alamat range tersebut secara manual.
3. FUNGSI IF (LOGIKA)
Dari semua fungsi logika yang terdapat dalam aplikasi Excel, fungsi IF adalah fungsi logika yang paling banyak diaplikasikan, terutama dalam penerapan formula lanjutan yang melibatkan banyak data dan keputusan bercabang. Kegunaan dari fungsi IF ini sebetulnya sangat sederhana, fungsi ini akan mengambil suatu kondisi tertentu kemudian menentukan nilai TRUE atau FALSE. Nilai TRUE adalah nilai dimana kondisi tersebut terpenuhi dan nilai FALSE adalah nilai untuk kondisi yang tidak terpenuhi.
Aturan penulisan fungsi IF ini adalah sebagai berikut:
=IF(kondisi;nilai_TRUE;nilai_FALSE)
- kondisi merupakan kriteria acuan yang dijadikan pembanding
- nilai_TRUE merupakan nilai untuk kondisi yang terpenuhi
- nilai_FALSE merupakan nilai untuk kondisi yang tidak terpenuhi
Pada tabel tersebut kolom Status akan diisi dengan kata LULUS atau GAGAL. Namun untuk mengisinya ada kondisi yang harus dipenuhi, misalkan saja siswa akan lulus jika nilainya diatas 60. Dari pernyataan ini maka didapat beberapa point, yaitu:
- Kondisi: Nilai diatas 60. Nilai ini berada pada sel C3 hingga C6. Dan karena ada nilai acuan yaitu 60 sebagai pembanding maka Anda harus menggunakan Operator Pembanding untuk melengkapi argumen pada kondisi ini. Operator pembanding yang digunakan tentunya tanda lebih besar “>“.
- Kondisi 1: Jika nilai diatas 60, maka siswa Lulus. Kondisi ini merupakan nilai untuk kondisi TRUE yaitu kondisi yang terpenuhi
- Kondisi 2: Jika nilai dibawah 60, artinya tidak diatas 60, maka siswa Gagal. Kondisi ini adalah nilai untuk kondisi FALSE yaitu kondisi yang tidak terpenuhi.
D3=IF(C3>60;”LULUS”;”GAGAL”)
Arti dari formula tersebut jika diterjemahkan dalam kalimat biasa adalah “Jika nilai dalam sel C3 lebih besar dari 60, maka tuliskan kata LULUS pada sel D3. Namun jika nilainya ternyata tidak lebih besar dari 60 maka tuliskan kata GAGAL pada sel D3”.
Contoh hasilnya dapat Anda lihat pada gambar berikut ini:
Untuk memeriksa apakah formula yang dituliskan sudah benar, cobalah untuk mengganti nilai pada sel C3, misalnya menjadi 40, lalu perhatikan hasilnya. Jika tertulis GAGAL maka artinya formula yang digunakan sudah benar.
Lalu bagaimana mengisikan baris-baris selanjutnya? Menuliskan formula secara manual satu persatu jelas bisa, namun hal ini akan sangat menyita waktu. Cara cepatnya, gunakan saja fitur Auto Fill (Pelajari).
Dan hasilnya semua kolom Status kini telah terisi dengan nilai yang benar.
Fungsi logika IF yang telah dibahas sebelumnya adalah salah satu fungsi yang relatif banyak digunakan, terutama di lingkungan bisnis / perusahaan. Namun fungsi IF ini memiliki keterbatasan karena kondisi yang menjadi acuan hanya satu saja. Bagaimana jika data Anda menggunakan beberapa kondisi sekaligus?
Penerapan fungsi pada aplikasi Excel bisa digabungkan untuk memenuhi satu tujuan atau hasil tertentu. Dan dalam kasus ini, dimana data memiliki beberapa kondisi yang harus dibandingkan, maka fungsi yang digunakan tetap fungsi IF, namun bukan hanya satu IF (IF tunggal) melainkan beberapa IF sekaligus. Dalam aplikasi Excel, penggunaan beberapa IF (Multiple IF) dalam satu formula ini lazim disebut dengan fungsi IF Bercabang atau Nested IF.
Pada dasarnya aturan penulisan fungsi IF bercabang ini sama saja dengan IF tunggal, yaitu:
=IF(kondisi;nilai_TRUE;nilai_FALSE)
- kondisi merupakan kriteria acuan yang dijadikan pembanding
- nilai_TRUE merupakan nilai untuk kondisi yang terpenuhi
- nilai_FALSE merupakan nilai untuk kondisi yang tidak terpenuhi
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;nilai_FALSE)))
Jika dijabarkan, maka formula tersebut berarti:
- Lihat kondisi_1, jika terpenuhi maka gunakan nilai_TRUE1.
- Jika kondisi_1 tidak terpenuhi, maka lihat kondisi_2.
- Jika kondisi_2 terpenuhi, maka gunakan nilai_TRUE2.
- Jika kondisi_2 tidak terpenuhi, maka gunakan nilai_FALSE.
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;(IF(kondisi_3;nilai_TRUE3;nilai_FALSE)))))
Atau jika kondisinya ada 4 maka aturan penulisannya menjadi:
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;(IF(kondisi_3;nilai_TRUE3;(IF(kondisi_4;nilai_TRUE4;nilai_FALSE)))))))
Dan demikian seterusnya.
Agar penggunaan fungsi IF bercabang ini lebih jelas, perhatikan contoh tabel berikut ini.
Misalkan saja pada tabel daftar barang tersebut Anda diminta untuk mengisi nilai diskon pada sel C3 hingga C6, dengan kondisi bahwa produk Mouse dan produk Keyboard akan mendapatkan diskon sebesar 5%. Diluar produk tersebut tidak diberikan diskon.
Analisa dari kasus tersebut adalah ada 2 kondisi yang menjadi acuan, yaitu Mouse diskon 5% serta Keyboard diskon 5%. Kedua kondisi ini tentunya harus dimasukan kedalam formula IF satu persatu, boleh diawali dengan kondisi untuk Mouse ataupun untuk Keyboard. Sebagai contoh diambil kondisi untuk Mouse terlebih dahulu. Untuk memperjelas pembahasan, berikut ini dituliskan kembali aturan penulisan fungsi IF.
C3=IF(kondisi;nilai_TRUE;nilai_FALSE)
Kondisi pertama adalah Mouse dengan diskon 5%. Mouse serta produk-produk lainnya berada pada kolom 3 dan diawali dengan alamat sel B3. Maka formulanya akan menjadi:
C3=IF(B3=”Mouse”;nilai_TRUE;nilai_FALSE)
Nilai jika B3 adalah Mouse merupakan nilai_TRUE yaitu 5%, maka formulanya menjadi:
C3=IF(B3=”Mouse”;5%;nilai_FALSE)
Nilai_FALSE digunakan jika ternyata isi sel bukan Mouse, artinya produk lainnya. Dan karena ada satu kondisi lagi yaituKeyboard maka kita masukan kondisi tersebut sebagai kondisi pada fungsi IF berikutnya:
C3=IF(B3=”Mouse”;5%;(IF(B3=”Keyboard”;nilai_TRUE2;nilai_FALSE)))
Jika B3 adalah Keyboard maka nilai_TRUE2 adalah 5% karena kondisinya terpenuhi. Sehingga formulanya akan menjadi:
C3=IF(B3=”Mouse”;5%;(IF(B3=”Keyboard”;5%;nilai_FALSE)))
Jika ternyata B3 tidak berisikan Mouse ataupun Keyboard, maka tidak diberikan diskon sama sekali atau diskon sama dengan 0. Nilai ini tentunya merupakan nilai_FALSE untuk semua kondisi yang telah dibandingkan. Dengan demikian formula akhirnya adalah sebagai berikut:
C3=IF(B3=”Mouse”;5%;(IF(B3=”Keyboard”;5%;0)))
Dan formula tersebut jika dijalankan pada sel C3 tentunya akan menghasilkan nilai yang benar seperti tampak pada gambar berikut ini.
Dan untuk mengisi sel-sel berikutnya seperti biasa Anda dapat menggunakan Auto Fill.
Pelajari baik-baik penggunaan formula IF bercabang ini karena dalam penerapan aplikasi Excel lanjutan Anda akan banyak menggunakan formula ini. Hati-hati dengan penggunaan tanda kurung karena semakin banyak IF yang Anda gunakan maka Anda akan menggunakan tanda kurung yang banyak juga.
4. VLOOKUP
VLOOKUP termasuk kedalam salah satu fungsi yang paling banyak digunakan dalam aplikasi Excel. Sesuai dengan nama dasarnya yaitu lookup yang bisa diartikan melihat atau mencari, maka fungsi ini akan menghasilkan suatu formula untuk mengisi data pada tabel berdasarkan data pada tabel lainnya atau tabel referensi tertentu dengan menggunakan suatu nilai kunci yang spesifik. Awalan huruf V didepan kata lookup merupakan singkatan dari kata Vertical. Istilah vertical ini merujuk pada bentuk tabel referensi yang digunakan, dimana judul kolomnya terletak dibagian atas dan data-datanya tersusun kebawah secara vertikal. Contoh sebuah tabel vertical ini bisa dilihat pada gambar berikut ini.
Aturan penulisan VLOOKUP mungkin bisa dibilang agak rumit bagi yang baru saja mempelajari penggunaan formula pada Excel. Namun jika sudah terbiasa, aturan ini sebetulnya bisa dibilang sederhana, yaitu:
=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)
Penjelasan aturan penulisan tersebut sebagai berikut:
- nilai_kunci: Adalah nilai yang dijadikan acuan untuk membaca tabel referensi. Nilai ini harus ada baik pada tabel yang akan diisi maupun pada tabel referensi.
- range_tabel_referensi: Adalah range dari tabel yang berisikan data referensi untuk mengisi hasil yang diharapkan. Pastikan bahwa range yang Anda pilih tidak menyertakan judul kolomnya.
- no_index-kolom: Adalah nomor urut data dalam tabel referensi yang akan dituliskan hasilnya. Dimulai dari kolom paling kiri pada tabel referensi tersebut dengan nomor index 1, dan seterusnya.
- tipe_data: Ada 2 jenis tipe data yaitu TRUE dan FALSE. Nilai TRUE Anda gunakan jika nilai datanya tidak pastiatau berada pada range tertentu dan nilai FALSE Anda gunakan jika nilai data berharga pasti.
Pada contoh tersebut terdapat 2 buah tabel. Data pada tabel Laporan Penjualan jelas akan selalu bertambah tiap harinya. Setiap kali ada pembeli maka Anda tinggal mengisikan data tanggal, nama pembeli serta kode voucher-nya. Sementara data Voucher serta Harga akan terisi secara otomatis tiap kali Anda mengisikan data pada kolom Kode. Pengisian data secara otomatis ini bisa dicapai dengan melihat tabel referensi yaitu tabel Stok Gudang dan tentunya dengan menggunakan formula VLOOKUP.
Langkah pertama akan diisi dahulu data pada tabel Voucher yaitu data pada sel E12. Untuk mempermudah pembahasan, aturan penulisan VLOOKUP kembali dituliskan sebagai berikut:
E12=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)
nilai_kunci yang digunakan adalah data pada kolom Kode dalam tabel Laporan Penjualan, yaitu sel D12. Alasannya karena Voucher akan bisa terisi dengan bersandar pada data dalam kolom Kode tersebut.
Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;range_tabel_referensi;no_index_kolom;tipe_data)
Kemudian range_tabel_referensi jelas adalah range data pada tabel Stok Gudang yaitu B4:D7.
Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;B4:D7;no_index_kolom;tipe_data)
no_index_kolom Anda tentukan dengan melihat didalam range_tabel_referensi. Dalam hal ini Anda akan mengisi data pada kolom Voucher maka Anda lihat data untuk Voucher tersebut didalam range_tabel_referensi berada pada kolom keberapa?
Dari gambar diatas dapat dilihat bahwa data yang akan diambil berada pada kolom ke-2, maka no_index_kolom yang digunakan adalah 2. Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;B4:D7;2;tipe_data)
Yang terakhir adalah tipe_data. Disini yang akan digunakan adalah FALSE. Penjelasannya adalah karena data tersebut bersifat pasti, misalnya kode S sudah pasti untuk Simpati, kode E sudah pasti untuk Esia, dan demikian seterusnya. Dengan demikian formula lengkap untuk mengisi data pada kolom Voucher tersebut adalah:
E12=VLOOKUP(D12;B4:D7;2;FALSE)
Untuk mengisi data pada sel selanjutnya yaitu sel E13 dan E14 seperti biasanya Anda dapat menggunakan Auto Fill. Namun perhatikan bahwa tabel referensi yang menjadi acuan memiliki range alamat sel yang statis alias tetap. Oleh karenanya, sebelum Anda menjalankan proses Auto Fill maka Anda harus mengunci terlebih dahulu range tabel referensi tersebut agar menjadi sel yang absolut. Hingga formulanya akan menjadi:
E12=VLOOKUP(D12;$B$4:$D$7;2;FALSE)
Dan proses Auto Fill kini dapat dijalankan untuk mengisi data pada sel-sel berikutnya.
Berikutnya, bagaimana mengisi data pada kolom Harga yaitu sel F12 hingga F14? Formula yang digunakan tentunya tetap sama hanya saja no_index_kolom yang berbeda yaitu 3 karena data yang akan diisikan diambil dari kolom ke-3 pada tabel referensi.
Dan berikut ini formula yang digunakan setelah menggunakan sel absolut untuk range tabel referensinya.
F12=VLOOKUP(D12;$B$4:$D$7;3;FALSE)
Pada contoh diatas Anda telah melihat penggunaan tipe data FALSE yaitu tipe data yang pasti. Untuk tipe data TRUEaturan penulisan formula-nya tetap sama, hanya saja data yang akan diisikan adalah data yang tidak pasti atau berada pada range tertentu. Contoh untuk penggunaan tipe data TRUE ini bisa dilihat pada tabel berikut ini.
Anda harus mengisikan formula VLOOKUP pada kolom Nilai dalam tabel Daftar Nilai Siswa, yaitu sel E13 hingga selE15. Nilai yang akan diisikan tersebut mengacu pada range tertentu dalam tabel referensi, yaitu:
- Nilai E: Score 0 s/d 39
- Nilai D: Score 40 s/d 59
- Nilai C: Score 60 s/d 79
- Nilai B: Score 80 s/d 89
- Nilai A: Score 90 s/d 59
=VLOOKUP(D13;$B$4:$C$8;2;TRUE)
Yang harus Anda perhatikan untuk tipe data TRUE ini adalah urutan data pada tabel referensi harus menaik atau tersusun dari data terkecil hingga data terbesar.
5. STANDAR DEVIASI
Kita mempelajari nya terlebih dahulu tentang belajar standar deviasi nya menggunakan ms excel sebagai berikut ini ;
rumusnya yang anda tulis di formula excel...:
STDEV (number1, number2,…)
Dengan rincian :
Number1, number2, … yang tertera dan yang kita buat di formula adalah 1-255 argumen yang sesuai dengan sampel populasi nya yang ada . Anda juga dapat menggunakan, array tunggal atau referensi ke array, bukan argumen yang dipisahkan oleh koma (,)
Nah itu dia beberapa tutorial dalam penggunaan rumus di Microsoft Excel, semoga bermanfaat. Sukses untuk kita semua!
post ini saya ambil darihttp://mohamad_ravena.student.ipb.ac.id/perangkat-lunak-pengolah-angka/
Perangkat aplikasi pengolah angka adalah program yang
difungsikan untuk mengolah data berupa angka. Kemampuan aplikasi ini,
antara lain: pembuatan tabel data, pengolahan data dengan melibatkan
penggunaan rumus, grafik, database, dan Iain-Iain. Dalam hal ini, angka
yang sering muncul di bidang bisnis, ilmiah, perencanaan, pembuatan
statistik, pembuatan lembar kerja, pengelolaan angka untuk perhitungan
sebuah data (basis data) dan grafik.
Kali ini kita akan membahas sedikit tentang salah satu pengolah angka yang terkenal dari Microsoft yaitu Ms. Excel versi 2013 yang akan saya bahas. Dalam penggunaan pengolah angka ini banyak penggunaan rumus dalam prosesnya seperti menghitung rata-rata, jumlah, standar deviasi, dan fungsi logika lainnya dalam sebuah pengolahan angka.
1. AVERAGE (RATA-RATA)
Fungsi AVERAGE digunakan untuk menghitung nilai rata-rata dari sejumlah data pada beberapa sel. Aturan penulisannya sebagai berikut:
=AVERAGE(sel awal:sel akhir)
Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi AVERAGE juga bisa dituliskan sebagai berikut:
=AVERAGE(range)
Sebagai contoh, pada tabel berikut ini akan dihitung nilai rata-rata Penjualan per Tahun pada sel C7 untuk data tahun 2009 hingga 2012, yaitu data pada sel C3 hingga C6.
Maka formula yang digunakan adalah:
C7=AVERAGE(C3:C6)
Dan hasilnya akan ditampilkan pada sel C7 seperti tampak pada gambar berikut ini.
2. JUMLAH (SUM)
Fungsi yang pertama dan bisa dibilang merupakan salah satu fungsi yang paling banyak diketahui dan digunakan setiap pengguna aplikasi Excel adalah fungsi SUM. Fungsi SUM digunakan untuk menjumlahkan data dari beberapa sel. Aturan penulisannya sebagai berikut:
=SUM(sel awal:sel akhir)
Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi SUM juga bisa dituliskan sebagai berikut:
=SUM(range)
Sebagai contoh, pada tabel berikut ini akan dihitung nilai Total Penjualan pada sel C7 untuk data bulan Januari hingga bulan April, yaitu data pada sel C3 hingga C6.
Sesuai dengan aturan penulisan formula yang diketahui, maka cara penulisan fungsi SUM untuk menghitung nilai Total Penjualan tersebut adalah sebagai berikut:
Dengan demikian, formula yang telah Anda gunakan untuk menghitung nilai Total tersebut adalah:
C7=SUM(C3:C6)
Untuk memeriksa hasil dari penulisan formula ini, cobalah untuk merubah data pada sel C3 hingga C6 dan lihatlah apakah hasil perhitungan pada sel C7 telah berubah. Jika berubah, maka penulisan formula sudah sesuai.
Penulisan range pada formula ini memang lebih mudah dilakukan dengan menggunakan klik dan drag mouse, namun untuk range yang terlalu jauh, tentunya Anda pun dapat mengetikan alamat range tersebut secara manual.
3. FUNGSI IF (LOGIKA)
Dari semua fungsi logika yang terdapat dalam aplikasi Excel, fungsi IF adalah fungsi logika yang paling banyak diaplikasikan, terutama dalam penerapan formula lanjutan yang melibatkan banyak data dan keputusan bercabang. Kegunaan dari fungsi IF ini sebetulnya sangat sederhana, fungsi ini akan mengambil suatu kondisi tertentu kemudian menentukan nilai TRUE atau FALSE. Nilai TRUE adalah nilai dimana kondisi tersebut terpenuhi dan nilai FALSE adalah nilai untuk kondisi yang tidak terpenuhi.
Aturan penulisan fungsi IF ini adalah sebagai berikut:
=IF(kondisi;nilai_TRUE;nilai_FALSE)
Pada tabel tersebut kolom Status akan diisi dengan kata LULUS atau GAGAL. Namun untuk mengisinya ada kondisi yang harus dipenuhi, misalkan saja siswa akan lulus jika nilainya diatas 60. Dari pernyataan ini maka didapat beberapa point, yaitu:
D3=IF(C3>60;”LULUS”;”GAGAL”)
Arti dari formula tersebut jika diterjemahkan dalam kalimat biasa adalah “Jika nilai dalam sel C3 lebih besar dari 60, maka tuliskan kata LULUS pada sel D3. Namun jika nilainya ternyata tidak lebih besar dari 60 maka tuliskan kata GAGAL pada sel D3”.
Contoh hasilnya dapat Anda lihat pada gambar berikut ini:
Untuk memeriksa apakah formula yang dituliskan sudah benar, cobalah untuk mengganti nilai pada sel C3, misalnya menjadi 40, lalu perhatikan hasilnya. Jika tertulis GAGAL maka artinya formula yang digunakan sudah benar.
Lalu bagaimana mengisikan baris-baris selanjutnya? Menuliskan formula secara manual satu persatu jelas bisa, namun hal ini akan sangat menyita waktu. Cara cepatnya, gunakan saja fitur Auto Fill (Pelajari).
Dan hasilnya semua kolom Status kini telah terisi dengan nilai yang benar.
Fungsi logika IF yang telah dibahas sebelumnya adalah salah satu fungsi yang relatif banyak digunakan, terutama di lingkungan bisnis / perusahaan. Namun fungsi IF ini memiliki keterbatasan karena kondisi yang menjadi acuan hanya satu saja. Bagaimana jika data Anda menggunakan beberapa kondisi sekaligus?
Penerapan fungsi pada aplikasi Excel bisa digabungkan untuk memenuhi satu tujuan atau hasil tertentu. Dan dalam kasus ini, dimana data memiliki beberapa kondisi yang harus dibandingkan, maka fungsi yang digunakan tetap fungsi IF, namun bukan hanya satu IF (IF tunggal) melainkan beberapa IF sekaligus. Dalam aplikasi Excel, penggunaan beberapa IF (Multiple IF) dalam satu formula ini lazim disebut dengan fungsi IF Bercabang atau Nested IF.
Pada dasarnya aturan penulisan fungsi IF bercabang ini sama saja dengan IF tunggal, yaitu:
=IF(kondisi;nilai_TRUE;nilai_FALSE)
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;nilai_FALSE)))
Jika dijabarkan, maka formula tersebut berarti:
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;(IF(kondisi_3;nilai_TRUE3;nilai_FALSE)))))
Atau jika kondisinya ada 4 maka aturan penulisannya menjadi:
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;(IF(kondisi_3;nilai_TRUE3;(IF(kondisi_4;nilai_TRUE4;nilai_FALSE)))))))
Dan demikian seterusnya.
Agar penggunaan fungsi IF bercabang ini lebih jelas, perhatikan contoh tabel berikut ini.
Misalkan saja pada tabel daftar barang tersebut Anda diminta untuk mengisi nilai diskon pada sel C3 hingga C6, dengan kondisi bahwa produk Mouse dan produk Keyboard akan mendapatkan diskon sebesar 5%. Diluar produk tersebut tidak diberikan diskon.
Analisa dari kasus tersebut adalah ada 2 kondisi yang menjadi acuan, yaitu Mouse diskon 5% serta Keyboard diskon 5%. Kedua kondisi ini tentunya harus dimasukan kedalam formula IF satu persatu, boleh diawali dengan kondisi untuk Mouse ataupun untuk Keyboard. Sebagai contoh diambil kondisi untuk Mouse terlebih dahulu. Untuk memperjelas pembahasan, berikut ini dituliskan kembali aturan penulisan fungsi IF.
C3=IF(kondisi;nilai_TRUE;nilai_FALSE)
Kondisi pertama adalah Mouse dengan diskon 5%. Mouse serta produk-produk lainnya berada pada kolom 3 dan diawali dengan alamat sel B3. Maka formulanya akan menjadi:
C3=IF(B3=”Mouse”;nilai_TRUE;nilai_FALSE)
Nilai jika B3 adalah Mouse merupakan nilai_TRUE yaitu 5%, maka formulanya menjadi:
C3=IF(B3=”Mouse”;5%;nilai_FALSE)
Nilai_FALSE digunakan jika ternyata isi sel bukan Mouse, artinya produk lainnya. Dan karena ada satu kondisi lagi yaituKeyboard maka kita masukan kondisi tersebut sebagai kondisi pada fungsi IF berikutnya:
C3=IF(B3=”Mouse”;5%;(IF(B3=”Keyboard”;nilai_TRUE2;nilai_FALSE)))
Jika B3 adalah Keyboard maka nilai_TRUE2 adalah 5% karena kondisinya terpenuhi. Sehingga formulanya akan menjadi:
C3=IF(B3=”Mouse”;5%;(IF(B3=”Keyboard”;5%;nilai_FALSE)))
Jika ternyata B3 tidak berisikan Mouse ataupun Keyboard, maka tidak diberikan diskon sama sekali atau diskon sama dengan 0. Nilai ini tentunya merupakan nilai_FALSE untuk semua kondisi yang telah dibandingkan. Dengan demikian formula akhirnya adalah sebagai berikut:
C3=IF(B3=”Mouse”;5%;(IF(B3=”Keyboard”;5%;0)))
Dan formula tersebut jika dijalankan pada sel C3 tentunya akan menghasilkan nilai yang benar seperti tampak pada gambar berikut ini.
Dan untuk mengisi sel-sel berikutnya seperti biasa Anda dapat menggunakan Auto Fill.
Pelajari baik-baik penggunaan formula IF bercabang ini karena dalam penerapan aplikasi Excel lanjutan Anda akan banyak menggunakan formula ini. Hati-hati dengan penggunaan tanda kurung karena semakin banyak IF yang Anda gunakan maka Anda akan menggunakan tanda kurung yang banyak juga.
4. VLOOKUP
VLOOKUP termasuk kedalam salah satu fungsi yang paling banyak digunakan dalam aplikasi Excel. Sesuai dengan nama dasarnya yaitu lookup yang bisa diartikan melihat atau mencari, maka fungsi ini akan menghasilkan suatu formula untuk mengisi data pada tabel berdasarkan data pada tabel lainnya atau tabel referensi tertentu dengan menggunakan suatu nilai kunci yang spesifik. Awalan huruf V didepan kata lookup merupakan singkatan dari kata Vertical. Istilah vertical ini merujuk pada bentuk tabel referensi yang digunakan, dimana judul kolomnya terletak dibagian atas dan data-datanya tersusun kebawah secara vertikal. Contoh sebuah tabel vertical ini bisa dilihat pada gambar berikut ini.
Aturan penulisan VLOOKUP mungkin bisa dibilang agak rumit bagi yang baru saja mempelajari penggunaan formula pada Excel. Namun jika sudah terbiasa, aturan ini sebetulnya bisa dibilang sederhana, yaitu:
=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)
Penjelasan aturan penulisan tersebut sebagai berikut:
Pada contoh tersebut terdapat 2 buah tabel. Data pada tabel Laporan Penjualan jelas akan selalu bertambah tiap harinya. Setiap kali ada pembeli maka Anda tinggal mengisikan data tanggal, nama pembeli serta kode voucher-nya. Sementara data Voucher serta Harga akan terisi secara otomatis tiap kali Anda mengisikan data pada kolom Kode. Pengisian data secara otomatis ini bisa dicapai dengan melihat tabel referensi yaitu tabel Stok Gudang dan tentunya dengan menggunakan formula VLOOKUP.
Langkah pertama akan diisi dahulu data pada tabel Voucher yaitu data pada sel E12. Untuk mempermudah pembahasan, aturan penulisan VLOOKUP kembali dituliskan sebagai berikut:
E12=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)
nilai_kunci yang digunakan adalah data pada kolom Kode dalam tabel Laporan Penjualan, yaitu sel D12. Alasannya karena Voucher akan bisa terisi dengan bersandar pada data dalam kolom Kode tersebut.
Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;range_tabel_referensi;no_index_kolom;tipe_data)
Kemudian range_tabel_referensi jelas adalah range data pada tabel Stok Gudang yaitu B4:D7.
Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;B4:D7;no_index_kolom;tipe_data)
no_index_kolom Anda tentukan dengan melihat didalam range_tabel_referensi. Dalam hal ini Anda akan mengisi data pada kolom Voucher maka Anda lihat data untuk Voucher tersebut didalam range_tabel_referensi berada pada kolom keberapa?
Dari gambar diatas dapat dilihat bahwa data yang akan diambil berada pada kolom ke-2, maka no_index_kolom yang digunakan adalah 2. Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;B4:D7;2;tipe_data)
Yang terakhir adalah tipe_data. Disini yang akan digunakan adalah FALSE. Penjelasannya adalah karena data tersebut bersifat pasti, misalnya kode S sudah pasti untuk Simpati, kode E sudah pasti untuk Esia, dan demikian seterusnya. Dengan demikian formula lengkap untuk mengisi data pada kolom Voucher tersebut adalah:
E12=VLOOKUP(D12;B4:D7;2;FALSE)
Untuk mengisi data pada sel selanjutnya yaitu sel E13 dan E14 seperti biasanya Anda dapat menggunakan Auto Fill. Namun perhatikan bahwa tabel referensi yang menjadi acuan memiliki range alamat sel yang statis alias tetap. Oleh karenanya, sebelum Anda menjalankan proses Auto Fill maka Anda harus mengunci terlebih dahulu range tabel referensi tersebut agar menjadi sel yang absolut. Hingga formulanya akan menjadi:
E12=VLOOKUP(D12;$B$4:$D$7;2;FALSE)
Dan proses Auto Fill kini dapat dijalankan untuk mengisi data pada sel-sel berikutnya.
Berikutnya, bagaimana mengisi data pada kolom Harga yaitu sel F12 hingga F14? Formula yang digunakan tentunya tetap sama hanya saja no_index_kolom yang berbeda yaitu 3 karena data yang akan diisikan diambil dari kolom ke-3 pada tabel referensi.
Dan berikut ini formula yang digunakan setelah menggunakan sel absolut untuk range tabel referensinya.
F12=VLOOKUP(D12;$B$4:$D$7;3;FALSE)
Pada contoh diatas Anda telah melihat penggunaan tipe data FALSE yaitu tipe data yang pasti. Untuk tipe data TRUEaturan penulisan formula-nya tetap sama, hanya saja data yang akan diisikan adalah data yang tidak pasti atau berada pada range tertentu. Contoh untuk penggunaan tipe data TRUE ini bisa dilihat pada tabel berikut ini.
Anda harus mengisikan formula VLOOKUP pada kolom Nilai dalam tabel Daftar Nilai Siswa, yaitu sel E13 hingga selE15. Nilai yang akan diisikan tersebut mengacu pada range tertentu dalam tabel referensi, yaitu:
=VLOOKUP(D13;$B$4:$C$8;2;TRUE)
Yang harus Anda perhatikan untuk tipe data TRUE ini adalah urutan data pada tabel referensi harus menaik atau tersusun dari data terkecil hingga data terbesar.
5. STANDAR DEVIASI
Kita mempelajari nya terlebih dahulu tentang belajar standar deviasi nya menggunakan ms excel sebagai berikut ini ;
rumusnya yang anda tulis di formula excel...:
STDEV (number1, number2,…)
Dengan rincian :
Number1, number2, … yang tertera dan yang kita buat di formula adalah 1-255 argumen yang sesuai dengan sampel populasi nya yang ada . Anda juga dapat menggunakan, array tunggal atau referensi ke array, bukan argumen yang dipisahkan oleh koma (,)
Nah itu dia beberapa tutorial dalam penggunaan rumus di Microsoft Excel, semoga bermanfaat. Sukses untuk kita semua!
post ini saya ambil dari
Kali ini kita akan membahas sedikit tentang salah satu pengolah angka yang terkenal dari Microsoft yaitu Ms. Excel versi 2013 yang akan saya bahas. Dalam penggunaan pengolah angka ini banyak penggunaan rumus dalam prosesnya seperti menghitung rata-rata, jumlah, standar deviasi, dan fungsi logika lainnya dalam sebuah pengolahan angka.
1. AVERAGE (RATA-RATA)
Fungsi AVERAGE digunakan untuk menghitung nilai rata-rata dari sejumlah data pada beberapa sel. Aturan penulisannya sebagai berikut:
=AVERAGE(sel awal:sel akhir)
Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi AVERAGE juga bisa dituliskan sebagai berikut:
=AVERAGE(range)
Sebagai contoh, pada tabel berikut ini akan dihitung nilai rata-rata Penjualan per Tahun pada sel C7 untuk data tahun 2009 hingga 2012, yaitu data pada sel C3 hingga C6.
Maka formula yang digunakan adalah:
C7=AVERAGE(C3:C6)
Dan hasilnya akan ditampilkan pada sel C7 seperti tampak pada gambar berikut ini.
2. JUMLAH (SUM)
Fungsi yang pertama dan bisa dibilang merupakan salah satu fungsi yang paling banyak diketahui dan digunakan setiap pengguna aplikasi Excel adalah fungsi SUM. Fungsi SUM digunakan untuk menjumlahkan data dari beberapa sel. Aturan penulisannya sebagai berikut:
=SUM(sel awal:sel akhir)
Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi SUM juga bisa dituliskan sebagai berikut:
=SUM(range)
Sebagai contoh, pada tabel berikut ini akan dihitung nilai Total Penjualan pada sel C7 untuk data bulan Januari hingga bulan April, yaitu data pada sel C3 hingga C6.
Sesuai dengan aturan penulisan formula yang diketahui, maka cara penulisan fungsi SUM untuk menghitung nilai Total Penjualan tersebut adalah sebagai berikut:
- Pilih sel C7
- Ketikan tanda =
- Ketikan SUM atau gunakan fitur Intellisense
- Ketikan tanda (
- Klik sel C3 lalu drag ke sel C6
- Ketikan tanda )
- Tekan Enter untuk melihat hasilnya
Dengan demikian, formula yang telah Anda gunakan untuk menghitung nilai Total tersebut adalah:
C7=SUM(C3:C6)
Untuk memeriksa hasil dari penulisan formula ini, cobalah untuk merubah data pada sel C3 hingga C6 dan lihatlah apakah hasil perhitungan pada sel C7 telah berubah. Jika berubah, maka penulisan formula sudah sesuai.
Penulisan range pada formula ini memang lebih mudah dilakukan dengan menggunakan klik dan drag mouse, namun untuk range yang terlalu jauh, tentunya Anda pun dapat mengetikan alamat range tersebut secara manual.
3. FUNGSI IF (LOGIKA)
Dari semua fungsi logika yang terdapat dalam aplikasi Excel, fungsi IF adalah fungsi logika yang paling banyak diaplikasikan, terutama dalam penerapan formula lanjutan yang melibatkan banyak data dan keputusan bercabang. Kegunaan dari fungsi IF ini sebetulnya sangat sederhana, fungsi ini akan mengambil suatu kondisi tertentu kemudian menentukan nilai TRUE atau FALSE. Nilai TRUE adalah nilai dimana kondisi tersebut terpenuhi dan nilai FALSE adalah nilai untuk kondisi yang tidak terpenuhi.
Aturan penulisan fungsi IF ini adalah sebagai berikut:
=IF(kondisi;nilai_TRUE;nilai_FALSE)
- kondisi merupakan kriteria acuan yang dijadikan pembanding
- nilai_TRUE merupakan nilai untuk kondisi yang terpenuhi
- nilai_FALSE merupakan nilai untuk kondisi yang tidak terpenuhi
Pada tabel tersebut kolom Status akan diisi dengan kata LULUS atau GAGAL. Namun untuk mengisinya ada kondisi yang harus dipenuhi, misalkan saja siswa akan lulus jika nilainya diatas 60. Dari pernyataan ini maka didapat beberapa point, yaitu:
- Kondisi: Nilai diatas 60. Nilai ini berada pada sel C3 hingga C6. Dan karena ada nilai acuan yaitu 60 sebagai pembanding maka Anda harus menggunakan Operator Pembanding untuk melengkapi argumen pada kondisi ini. Operator pembanding yang digunakan tentunya tanda lebih besar “>“.
- Kondisi 1: Jika nilai diatas 60, maka siswa Lulus. Kondisi ini merupakan nilai untuk kondisi TRUE yaitu kondisi yang terpenuhi
- Kondisi 2: Jika nilai dibawah 60, artinya tidak diatas 60, maka siswa Gagal. Kondisi ini adalah nilai untuk kondisi FALSE yaitu kondisi yang tidak terpenuhi.
D3=IF(C3>60;”LULUS”;”GAGAL”)
Arti dari formula tersebut jika diterjemahkan dalam kalimat biasa adalah “Jika nilai dalam sel C3 lebih besar dari 60, maka tuliskan kata LULUS pada sel D3. Namun jika nilainya ternyata tidak lebih besar dari 60 maka tuliskan kata GAGAL pada sel D3”.
Contoh hasilnya dapat Anda lihat pada gambar berikut ini:
Untuk memeriksa apakah formula yang dituliskan sudah benar, cobalah untuk mengganti nilai pada sel C3, misalnya menjadi 40, lalu perhatikan hasilnya. Jika tertulis GAGAL maka artinya formula yang digunakan sudah benar.
Lalu bagaimana mengisikan baris-baris selanjutnya? Menuliskan formula secara manual satu persatu jelas bisa, namun hal ini akan sangat menyita waktu. Cara cepatnya, gunakan saja fitur Auto Fill (Pelajari).
Dan hasilnya semua kolom Status kini telah terisi dengan nilai yang benar.
Fungsi logika IF yang telah dibahas sebelumnya adalah salah satu fungsi yang relatif banyak digunakan, terutama di lingkungan bisnis / perusahaan. Namun fungsi IF ini memiliki keterbatasan karena kondisi yang menjadi acuan hanya satu saja. Bagaimana jika data Anda menggunakan beberapa kondisi sekaligus?
Penerapan fungsi pada aplikasi Excel bisa digabungkan untuk memenuhi satu tujuan atau hasil tertentu. Dan dalam kasus ini, dimana data memiliki beberapa kondisi yang harus dibandingkan, maka fungsi yang digunakan tetap fungsi IF, namun bukan hanya satu IF (IF tunggal) melainkan beberapa IF sekaligus. Dalam aplikasi Excel, penggunaan beberapa IF (Multiple IF) dalam satu formula ini lazim disebut dengan fungsi IF Bercabang atau Nested IF.
Pada dasarnya aturan penulisan fungsi IF bercabang ini sama saja dengan IF tunggal, yaitu:
=IF(kondisi;nilai_TRUE;nilai_FALSE)
- kondisi merupakan kriteria acuan yang dijadikan pembanding
- nilai_TRUE merupakan nilai untuk kondisi yang terpenuhi
- nilai_FALSE merupakan nilai untuk kondisi yang tidak terpenuhi
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;nilai_FALSE)))
Jika dijabarkan, maka formula tersebut berarti:
- Lihat kondisi_1, jika terpenuhi maka gunakan nilai_TRUE1.
- Jika kondisi_1 tidak terpenuhi, maka lihat kondisi_2.
- Jika kondisi_2 terpenuhi, maka gunakan nilai_TRUE2.
- Jika kondisi_2 tidak terpenuhi, maka gunakan nilai_FALSE.
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;(IF(kondisi_3;nilai_TRUE3;nilai_FALSE)))))
Atau jika kondisinya ada 4 maka aturan penulisannya menjadi:
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;(IF(kondisi_3;nilai_TRUE3;(IF(kondisi_4;nilai_TRUE4;nilai_FALSE)))))))
Dan demikian seterusnya.
Agar penggunaan fungsi IF bercabang ini lebih jelas, perhatikan contoh tabel berikut ini.
Misalkan saja pada tabel daftar barang tersebut Anda diminta untuk mengisi nilai diskon pada sel C3 hingga C6, dengan kondisi bahwa produk Mouse dan produk Keyboard akan mendapatkan diskon sebesar 5%. Diluar produk tersebut tidak diberikan diskon.
Analisa dari kasus tersebut adalah ada 2 kondisi yang menjadi acuan, yaitu Mouse diskon 5% serta Keyboard diskon 5%. Kedua kondisi ini tentunya harus dimasukan kedalam formula IF satu persatu, boleh diawali dengan kondisi untuk Mouse ataupun untuk Keyboard. Sebagai contoh diambil kondisi untuk Mouse terlebih dahulu. Untuk memperjelas pembahasan, berikut ini dituliskan kembali aturan penulisan fungsi IF.
C3=IF(kondisi;nilai_TRUE;nilai_FALSE)
Kondisi pertama adalah Mouse dengan diskon 5%. Mouse serta produk-produk lainnya berada pada kolom 3 dan diawali dengan alamat sel B3. Maka formulanya akan menjadi:
C3=IF(B3=”Mouse”;nilai_TRUE;nilai_FALSE)
Nilai jika B3 adalah Mouse merupakan nilai_TRUE yaitu 5%, maka formulanya menjadi:
C3=IF(B3=”Mouse”;5%;nilai_FALSE)
Nilai_FALSE digunakan jika ternyata isi sel bukan Mouse, artinya produk lainnya. Dan karena ada satu kondisi lagi yaituKeyboard maka kita masukan kondisi tersebut sebagai kondisi pada fungsi IF berikutnya:
C3=IF(B3=”Mouse”;5%;(IF(B3=”Keyboard”;nilai_TRUE2;nilai_FALSE)))
Jika B3 adalah Keyboard maka nilai_TRUE2 adalah 5% karena kondisinya terpenuhi. Sehingga formulanya akan menjadi:
C3=IF(B3=”Mouse”;5%;(IF(B3=”Keyboard”;5%;nilai_FALSE)))
Jika ternyata B3 tidak berisikan Mouse ataupun Keyboard, maka tidak diberikan diskon sama sekali atau diskon sama dengan 0. Nilai ini tentunya merupakan nilai_FALSE untuk semua kondisi yang telah dibandingkan. Dengan demikian formula akhirnya adalah sebagai berikut:
C3=IF(B3=”Mouse”;5%;(IF(B3=”Keyboard”;5%;0)))
Dan formula tersebut jika dijalankan pada sel C3 tentunya akan menghasilkan nilai yang benar seperti tampak pada gambar berikut ini.
Dan untuk mengisi sel-sel berikutnya seperti biasa Anda dapat menggunakan Auto Fill.
Pelajari baik-baik penggunaan formula IF bercabang ini karena dalam penerapan aplikasi Excel lanjutan Anda akan banyak menggunakan formula ini. Hati-hati dengan penggunaan tanda kurung karena semakin banyak IF yang Anda gunakan maka Anda akan menggunakan tanda kurung yang banyak juga.
4. VLOOKUP
VLOOKUP termasuk kedalam salah satu fungsi yang paling banyak digunakan dalam aplikasi Excel. Sesuai dengan nama dasarnya yaitu lookup yang bisa diartikan melihat atau mencari, maka fungsi ini akan menghasilkan suatu formula untuk mengisi data pada tabel berdasarkan data pada tabel lainnya atau tabel referensi tertentu dengan menggunakan suatu nilai kunci yang spesifik. Awalan huruf V didepan kata lookup merupakan singkatan dari kata Vertical. Istilah vertical ini merujuk pada bentuk tabel referensi yang digunakan, dimana judul kolomnya terletak dibagian atas dan data-datanya tersusun kebawah secara vertikal. Contoh sebuah tabel vertical ini bisa dilihat pada gambar berikut ini.
Aturan penulisan VLOOKUP mungkin bisa dibilang agak rumit bagi yang baru saja mempelajari penggunaan formula pada Excel. Namun jika sudah terbiasa, aturan ini sebetulnya bisa dibilang sederhana, yaitu:
=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)
Penjelasan aturan penulisan tersebut sebagai berikut:
- nilai_kunci: Adalah nilai yang dijadikan acuan untuk membaca tabel referensi. Nilai ini harus ada baik pada tabel yang akan diisi maupun pada tabel referensi.
- range_tabel_referensi: Adalah range dari tabel yang berisikan data referensi untuk mengisi hasil yang diharapkan. Pastikan bahwa range yang Anda pilih tidak menyertakan judul kolomnya.
- no_index-kolom: Adalah nomor urut data dalam tabel referensi yang akan dituliskan hasilnya. Dimulai dari kolom paling kiri pada tabel referensi tersebut dengan nomor index 1, dan seterusnya.
- tipe_data: Ada 2 jenis tipe data yaitu TRUE dan FALSE. Nilai TRUE Anda gunakan jika nilai datanya tidak pastiatau berada pada range tertentu dan nilai FALSE Anda gunakan jika nilai data berharga pasti.
Pada contoh tersebut terdapat 2 buah tabel. Data pada tabel Laporan Penjualan jelas akan selalu bertambah tiap harinya. Setiap kali ada pembeli maka Anda tinggal mengisikan data tanggal, nama pembeli serta kode voucher-nya. Sementara data Voucher serta Harga akan terisi secara otomatis tiap kali Anda mengisikan data pada kolom Kode. Pengisian data secara otomatis ini bisa dicapai dengan melihat tabel referensi yaitu tabel Stok Gudang dan tentunya dengan menggunakan formula VLOOKUP.
Langkah pertama akan diisi dahulu data pada tabel Voucher yaitu data pada sel E12. Untuk mempermudah pembahasan, aturan penulisan VLOOKUP kembali dituliskan sebagai berikut:
E12=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)
nilai_kunci yang digunakan adalah data pada kolom Kode dalam tabel Laporan Penjualan, yaitu sel D12. Alasannya karena Voucher akan bisa terisi dengan bersandar pada data dalam kolom Kode tersebut.
Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;range_tabel_referensi;no_index_kolom;tipe_data)
Kemudian range_tabel_referensi jelas adalah range data pada tabel Stok Gudang yaitu B4:D7.
Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;B4:D7;no_index_kolom;tipe_data)
no_index_kolom Anda tentukan dengan melihat didalam range_tabel_referensi. Dalam hal ini Anda akan mengisi data pada kolom Voucher maka Anda lihat data untuk Voucher tersebut didalam range_tabel_referensi berada pada kolom keberapa?
Dari gambar diatas dapat dilihat bahwa data yang akan diambil berada pada kolom ke-2, maka no_index_kolom yang digunakan adalah 2. Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;B4:D7;2;tipe_data)
Yang terakhir adalah tipe_data. Disini yang akan digunakan adalah FALSE. Penjelasannya adalah karena data tersebut bersifat pasti, misalnya kode S sudah pasti untuk Simpati, kode E sudah pasti untuk Esia, dan demikian seterusnya. Dengan demikian formula lengkap untuk mengisi data pada kolom Voucher tersebut adalah:
E12=VLOOKUP(D12;B4:D7;2;FALSE)
Untuk mengisi data pada sel selanjutnya yaitu sel E13 dan E14 seperti biasanya Anda dapat menggunakan Auto Fill. Namun perhatikan bahwa tabel referensi yang menjadi acuan memiliki range alamat sel yang statis alias tetap. Oleh karenanya, sebelum Anda menjalankan proses Auto Fill maka Anda harus mengunci terlebih dahulu range tabel referensi tersebut agar menjadi sel yang absolut. Hingga formulanya akan menjadi:
E12=VLOOKUP(D12;$B$4:$D$7;2;FALSE)
Dan proses Auto Fill kini dapat dijalankan untuk mengisi data pada sel-sel berikutnya.
Berikutnya, bagaimana mengisi data pada kolom Harga yaitu sel F12 hingga F14? Formula yang digunakan tentunya tetap sama hanya saja no_index_kolom yang berbeda yaitu 3 karena data yang akan diisikan diambil dari kolom ke-3 pada tabel referensi.
Dan berikut ini formula yang digunakan setelah menggunakan sel absolut untuk range tabel referensinya.
F12=VLOOKUP(D12;$B$4:$D$7;3;FALSE)
Pada contoh diatas Anda telah melihat penggunaan tipe data FALSE yaitu tipe data yang pasti. Untuk tipe data TRUEaturan penulisan formula-nya tetap sama, hanya saja data yang akan diisikan adalah data yang tidak pasti atau berada pada range tertentu. Contoh untuk penggunaan tipe data TRUE ini bisa dilihat pada tabel berikut ini.
Anda harus mengisikan formula VLOOKUP pada kolom Nilai dalam tabel Daftar Nilai Siswa, yaitu sel E13 hingga selE15. Nilai yang akan diisikan tersebut mengacu pada range tertentu dalam tabel referensi, yaitu:
- Nilai E: Score 0 s/d 39
- Nilai D: Score 40 s/d 59
- Nilai C: Score 60 s/d 79
- Nilai B: Score 80 s/d 89
- Nilai A: Score 90 s/d 59
=VLOOKUP(D13;$B$4:$C$8;2;TRUE)
Yang harus Anda perhatikan untuk tipe data TRUE ini adalah urutan data pada tabel referensi harus menaik atau tersusun dari data terkecil hingga data terbesar.
5. STANDAR DEVIASI
Kita mempelajari nya terlebih dahulu tentang belajar standar deviasi nya menggunakan ms excel sebagai berikut ini ;
rumusnya yang anda tulis di formula excel...:
STDEV (number1, number2,…)
Dengan rincian :
Number1, number2, … yang tertera dan yang kita buat di formula adalah 1-255 argumen yang sesuai dengan sampel populasi nya yang ada . Anda juga dapat menggunakan, array tunggal atau referensi ke array, bukan argumen yang dipisahkan oleh koma (,)
Nah itu dia beberapa tutorial dalam penggunaan rumus di Microsoft Excel, semoga bermanfaat. Sukses untuk kita semua!
post ini saya ambil darihttp://mohamad_ravena.student.ipb.ac.id/perangkat-lunak-pengolah-angka/
Tidak ada komentar:
Posting Komentar