Microsoft Excel – DATABSE TIK Agribisnis UMY 2016
Heri Akhmadi, S.P., M.A.
Today’s Lecture • Menggunakan kombinasi rumus/formula dan fungsi • Membuat Database dengan fungsi VLOOKUP
Menggunakan Database
Review: Arithmetic Operators
Review: Logical Operator • In Excel the following "operators" are used Operator > < >= <= = <>
Meaning greater than less than greater than or equal to less than or equal to equal to not equal to
• Examples 3>2 3<2
true false
Review: Common Errors The following are some errors that may appear in a spreadsheet (there are others too). • ####### • Cell is too narrow to display the results of the formula.
• #NAME? • You used a cell reference in the formula that is not formed correctly (e.g. =BB+10 instead of =B3+10)
• #VALUE! • Usually the result of trying to do math with a textual value. Example: =A1*3 where A1 contains the word “hello”
• #DIV/0! • Trying to divide by zero. Example: =3/A1 where A1 contains 0 (zero)
• Circular Reference • Using a formula that contains a reference to the cell that the formula “lives in”. Example: putting the formula =A1+1 in cell A1 or putting the formula =SUM(A1:B2) in any of the cells A1, B1, A2, B2
MENGGUNAKAN RUMUS/FUNGSI • Fungsi sebenarnya adalah rumus yang sudah ada disediakan oleh Excel, yang akan membantu dalam proses perhitungan. Kita tinggal memanfaatkan sesuai dengan kebutuhan. Umumnya penulisan Fungsi harus dilengkapi dengan argumen, baik erupa angka, label, rumus, alamat sel atau range. Argumen ini harus ditulis dengan diapit tanda kurung (). Contoh : menjumlahkan nilai yang terdapat pada sel D1 sampai D10, rumus yang dituliskan adalah : "=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10".
• Akan lebih mudah jika menggunakan fungsi SUM, dengan menuliskan "=SUM(D1:D10)".
RUMUS/FUNGSI dalam Excel secara umum ada 3: 1. Rumus Matematis 2. Rumus Statistik 3. Rumus Logika
Rumus Matematis Fungsi
Kegunaan
Penulisan
SUM
Menjumlahkan pada angka pada sel atau range ttt
=SUM(angka 1, angka 2,...) =SUM(alamat range)
AVERAGE
Menampilan rata-rata dari suatu range data numerik
=AVERAGE (angka 1, angka 2,...) =AVERAGE(alamat range)
SQRT
mengakarkan nilai dalam sel
=SQRT(angka 1) SQRT(alamat range)
CONVERT
Mengkonversi unit satuan, baik itu jarak, berat maupun waktu
=CONVERTT(number;from_u nit;to_unit) =CONVERT(A3;”km”;”m”)
Rumus Statistik Fungsi
Kegunaan
Penulisan
MAX
Menampilkan nilai terbesar dari suatu range data numerik
=MAX(angka 1, angka 2,...) =MAX(alamat range)
MIN
Menampilan nilai terkecil dari suatu range data numerik
=MIN(angka 1, angka 2,...) MIN(alamat range)
COUNT
Menghitung jumlah data yang bersifat numerik dari suatu range
=COUNT(angka 1, angka 2,...) =COUNT(alamat range)
Rumus Statistik COUNTIF
Menghitung jumlah data yang bersifat numerik yang terdapat pada range tertentu sesuai dengan kriteria yang ditentukan
=COUNTIF(range,criteria)
COUNTA
Menghitung jumlah semua jenis data dari suatu range
=COUNTA(angka 1, angka 2,...) =COUNTA(alamat range)
SUMIF
Menjumlahkan data yang bersifat numerik dalam suatu range data dengan kriteria tertentu
=SUMIF(range,criteria,sum _range)
Fungsi Logika “If” Memilih antara 2 kemungkinan atau lebih dari perbandingan logika yang diberikan Bentuk Umum : =IF(logika, perintah-1, perintah-2)
Fungsi Logika “If” Formula View
Values View
Contoh Fungsi Logiga “If”
Fungsi Lookup Fungsi
Kegunaan
Penulisan
VLOOKUP
Menampilkan data berdasarkan tabel referensi dalam format vertikal. Syarat penyusunan tabel referensi harus urut.
=VLOOKUP(lookup_value,ta ble_array,col_index,num)
HLOOKUP
Menampilkan data berdasarkan tabel referensi dalam format horisontal. Syarat penyusunan tabel referensi harus urut.
=HLOOKUP(lookup_value,ta ble_array.col_index,num)
Dari rumus diatas, dapat dilihat bahwa bedanya hanya pada nomor indeksnya saja, kalau kita pakai HLOOKUP, maka digunakan nomor indeks baris (Row_index_num), tapi kalu pakai VLOOKUP digunakan nomor indeks kolom (Col_index_num).
FUNGSI HLOOKUP (X,Y,Z) • Mencari nilai dalam suatu tabel atau digunakan untuk membaca tabel secara horisontal dengan menggunakan nilai kunci pembanding serta offset baris yang akan dibaca. • X:
kunci pembacaan tabel yaitu kesamaan antara tabel anak dan tabel induk dimana tabel induklah yang harus menyesuaikan dengan tabel anak. • Y : nama range yang akan dibaca. Dalam pemberian nama range, judul kolom tidak diikutkan. Judul kolom harus diurutkan secara menaik atau ascending. • Z : nilai offset yang digunakan sebagai pembacaan pada baris data.
FUNGSI HLOOKUP (X,Y,Z)
FUNGSI HLOOKUP (X,Y,Z) • Pada tabel anak beri nama range: caranya blok range B2:F4, kemudian pada kotak name (Name box) ketikkan nama rangenya misalnya Laporan. Kemudian tekan ENTER. • Pada tabel induk sel B3 ketikkan rumus : • =HLOOKUP(A3,Laporan,2) • kemudian drag rumus ke bawah sampai sel B7 • Pada sel C3 ketikkan rumus: • =HLOOKUP(A3,Laporan,3) • kemudian drag ke bawah sel C7
FUNGSI VLOOKUP (X,Y,Z) • Digunakan untuk mencari nilai dalam suatu kolom tabel atau untuk membaca tabel secara vertikal dengan menggunakan nilai kunci pembanding serta offset kolom yang akan dibaca.
• X : kunci pembacaan tabel • Y : nama range yang akan dibaca, dan harus dalam keadaan urut secara menaik atau ascending. • Z : nilai offset yang digunakan sebagai pembacaan pada kolom data.
FUNGSI VLOOKUP (X,Y,Z) sheet1.
sheet2
Tabel anak diletakkan pada sheet1 dan tabel induk diletakkan pada sheet2. Untuk membaca tabel terlebih dahulu harus memberi nama range. Caranya blok range A3:C7, kemudian pada kotak Name (Name box) ketikkan nama rangenya, misalnya Data. Tekan Enter untuk mengakhiri proses. Secara otomatis akan ditambahkan sebuah range dengan nama Data.
FUNGSI VLOOKUP (X,Y,Z) • pada sel B3 Sheet2 ketikkan rumus • =VLOOKUP(B3,DATA,2) • (membaca tabel anak dengan range DATA kolom ke 2 => Barang) • pada sel C3 ketikkan rumus • =VLOOKUP(B3,DATA,3) • (untuk membaca kolom ke 3 => Vendor)
