Fungsi
Matematika Pada Microsoft Excel
Berikut
adalah beberapa fungsi matematika yang penting yang terdapat pada Microsoft
Excel
Fungsi-Fungsi Matematika Pada MS Excel
ABS(angka)
Menghasilkan nilai abosulut dari suatu angka
CEILING(angka)
Menghasilkan pembulatan angka ke atas
COS(angka)
Menghasilkan nilai kosinus dari suatu sudut. angka tersebut dalam radian, jadi jika kamu pengen cari nilai kosinus suatu sudut (dalam derajat), maka kamu perlu ubah dulu menjadi radian gunakan fungsi RADIANS(sudut)
DEGREES(sudut)
Mengubah nilai sudut radian ke derajat
FLOOR(angka)
Membulatkan angka ke bawah
INT(angka)
Membulatkan angka ke nilai terdekat
LN(angka)
Menghasilkan nilai Lon atau disebut juga Natural Logaritma suatu angka
LOG(angka)
Menghasilkan nilai Logartima suatu angka
PI()
Menghasilkan nilai pi = 3,14
POWER(angka,pangkat)
Menghasilkan nilai dari angka pangkat berapa...
RADIANS(sudut)
Mengubah nilai sudut derajat ke radian
RAND()
Menghasilkan nilai acak dari 0 sampai 1
SIN(sudut)
Menghasilkan nilai sinus suatu sudut (radian)
SQRT(angka)
Menghasilkan akar pangkat dua suatu angka
SUM(angka1,
angka2,angka...)
Menjumlahkan angka-angka
TAN(sudut)
Menghasilkan nilai tangen suatu sudut(radian) |
Fungsi
Statistik di dalam Microsoft Excel
1.SUM, berfungsi untuk melakukan
penjumlahanRumus :=SUM(Range), Contoh
:SUM (A1:A20)
2.SUMIF,brfungsi untuk mencari
Total jumlah berdasarkan
kriteriaRumus :=SUMIF(Range,Criteria,Sum_Range),Contoh
:SUMIF(
B2:B6,”Komputer ”,C2:C6)
3.AVERAGE, Berfungsi untuk mencari nilai Rata-RataRumus
:=AVERAGE(Range), Contoh :SUM
(A1:A20)
4.MIN, Untuk
Mencari Nilai terkecilRumus :=MIN(Range), Contoh :MIN
(A1:A20)
5 . MAX, Untuk Mencari Nilai
terkecilRumus :=MAX(Range), Contoh :MAX (A1:A20)
6. COUNT,
Untuk menghitung jumlah data yang berbentuk numerikRumus := COUNT(Range),
Contoh : COUNT(A1:A20)
7.COUNTA,Untuk
menghitung jumlah data yang berbentuk numerik / Karakter Rumus := COUNTA(Range),
Contoh : COUNTA(A1:A20)
8.COUNTIF, Untuk menghitung jumlah
data yang berbentuk numerik /
Karakter berdasarkan numerik
/ Karakter yang dicariRumus :=
COUNTIF(Range,”Kriteria”), Contoh
: COUNTIF(A1:A20,”Komputer”)Contoh :
COUNTIF(B2:B6,”Komputer”)
9.COUNTBLANK, Untuk Mencari jumlah
data yang KosongRumus :=
COUNTBLANK(Range), Contoh :
COUNTBLANK (A1:A20
Fungsi Logika Pada
Excel
Digunakan untuk memeriksa kondisi benar atau salah. Penulisan
perintahnya adalah sebagai berikut :
=IF(Kondisi,pernyataan jika Benar,pernyataan jika Salah)
=IF(Kondisi,pernyataan jika Benar,pernyataan jika Salah)
Operator-operator perbandingan yang dapat digunakan
dalam Excel adalah :
>, <, <>, =, <= dan >=
>, <, <>, =, <= dan >=
Contoh :
Di sel A1 berisi data 100, di sel B1 berisi data 200.
Jika di sel C1 terdapat fungsi =IF(A1>B1,"MAHAL","MURAH")
maka di sel C1 tercetak MURAH
Jika ada lebih dari 2 kondisi, maka digunakan fungsi logika majemuk atau bersarang (nested if).
Contoh :
Contoh :
Kolom Grade syarat pengisiannya adalah sebagai
berikut :
Jika Nilai 100-90 maka Grade A
Jika Nilai 89-80 maka Grade B
Jika Nilai 79-70 maka Grade C
Jika Nilai 69-60 maka Grade D
Jika Nilai kurang dari 60 maka Grade E
Jika Nilai 100-90 maka Grade A
Jika Nilai 89-80 maka Grade B
Jika Nilai 79-70 maka Grade C
Jika Nilai 69-60 maka Grade D
Jika Nilai kurang dari 60 maka Grade E
Maka untuk kolom Grade fungsi logikanya adalah :
=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","E"))))
=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","E"))))
Catatan :
Banyaknya fungsi =IF() ditentukan oleh banyaknya kondisi-1.
Jadi jika kondisi atau syarat yang diperiksa 3, maka fungsi =IF() adalah 2,
Jika kondisinya 4 berarti fungsi =IF() adalah 3, dan seterusnya.
Banyaknya fungsi =IF() ditentukan oleh banyaknya kondisi-1.
Jadi jika kondisi atau syarat yang diperiksa 3, maka fungsi =IF() adalah 2,
Jika kondisinya 4 berarti fungsi =IF() adalah 3, dan seterusnya.
Logika
Kadangkala kita diperhadapkan pada dua atau lebih
kondisi/kriteria yang diperiksa, maka untuk menyelesaikan hal ini digunakanlah
operator logika. Macam-macam operator logika yang terdapat dalam Excel adalah :
=AND(kriteria 1,kriteria 2, ...)
Akan bernilai benar jika semua kriteria benar. Misalnya :
AND(TRUE,TRUE) hasilnya adalah TRUE
AND(TRUE,FALSE) hasilnya adalah FALSE
AND(FALSE,TRUE) hasilnya adalah FALSE
AND(FALSE,FALSE) hasilnya adalah FALSE
=AND(kriteria 1,kriteria 2, ...)
Akan bernilai benar jika semua kriteria benar. Misalnya :
AND(TRUE,TRUE) hasilnya adalah TRUE
AND(TRUE,FALSE) hasilnya adalah FALSE
AND(FALSE,TRUE) hasilnya adalah FALSE
AND(FALSE,FALSE) hasilnya adalah FALSE
=OR(kriteria 1,kriteria 2, ...)
Akan bernilai benar jika salah satu kriteria benar. Misalnya :
OR(TRUE,TRUE) hasilnya adalah TRUE
OR(TRUE,FALSE) hasilnya adalah TRUE
OR(FALSE,TRUE) hasilnya adalah TRUE
OR(FALSE,FALSE) hasilnya adalah FALSE
Akan bernilai benar jika salah satu kriteria benar. Misalnya :
OR(TRUE,TRUE) hasilnya adalah TRUE
OR(TRUE,FALSE) hasilnya adalah TRUE
OR(FALSE,TRUE) hasilnya adalah TRUE
OR(FALSE,FALSE) hasilnya adalah FALSE
=NOT(kriteria atau kondisi)
Hasilnya adalah kebalikan dari kondisi. Misalnya :
NOT(TRUE) hasilnya adalah FALSE
NOT(FALSE) hasilnya adalah TRUE
Hasilnya adalah kebalikan dari kondisi. Misalnya :
NOT(TRUE) hasilnya adalah FALSE
NOT(FALSE) hasilnya adalah TRUE
Contoh Kasus
Pada gambar diatas, kolom yang diisikan secara
manual adalah kolom No, Nama, Status dan Lama Kerja sedangkan untuk kolom Pokok
dan Tunjangan didapatkan dari rumus dengan ketentuan sebagai berikut :
Pokok :
Jika Lama Kerja>15 -> 2.000.000
Jika Lama Kerja>10 -> 1.500.000
Jika Lama Kerja>5 -> 1.000.000
Selain itu 750.000
Tunjangan :
Jika berstatus Nikah (N) maka mendapatkan Tunjangan 15% dari Pokok;
Jika Belum Nikah (B) dan Lama Kerja lebih dari 10 thn maka mendapatkanTunjangan 7,5%
dari Pokok;
Jika berstatus Belum Nikah (B) dan Lama Kerja lebih dari 3 thn maka mendapatkan
Tunjangan 3% dari Pokok;
Selain itu tidak mendapatkan Tunjangan.
Pokok :
Jika Lama Kerja>15 -> 2.000.000
Jika Lama Kerja>10 -> 1.500.000
Jika Lama Kerja>5 -> 1.000.000
Selain itu 750.000
Tunjangan :
Jika berstatus Nikah (N) maka mendapatkan Tunjangan 15% dari Pokok;
Jika Belum Nikah (B) dan Lama Kerja lebih dari 10 thn maka mendapatkanTunjangan 7,5%
dari Pokok;
Jika berstatus Belum Nikah (B) dan Lama Kerja lebih dari 3 thn maka mendapatkan
Tunjangan 3% dari Pokok;
Selain itu tidak mendapatkan Tunjangan.
Penyelesaian :
Pokok :
Untuk mencari Pokok, karena terdapat 4 buah kriteria/syarat maka kita menggunakan 4
buah fungsi IF di dalamnya. Rumusnya adalah sebagai berikut :
=IF(D2>=15,2000000,IF(D2>=10,1500000,IF(D2>=5,1000000,750000)))
Pokok :
Untuk mencari Pokok, karena terdapat 4 buah kriteria/syarat maka kita menggunakan 4
buah fungsi IF di dalamnya. Rumusnya adalah sebagai berikut :
=IF(D2>=15,2000000,IF(D2>=10,1500000,IF(D2>=5,1000000,750000)))
Tunjangan :
Untuk mencari Tunjangan, terdapat 4 buah kriteria sehingga fungsi IF yang digunakan ada 3 buah serta terdapat operator logika "dan" didalamnya maka rumusnya adalah sebagai berikut :
=IF(C2="N",15%*E2,IF(AND(C2="B",D2>10),7,5%*E2,IF(AND(C2="B",D2>3),3%*E2,0)))
Untuk mencari Tunjangan, terdapat 4 buah kriteria sehingga fungsi IF yang digunakan ada 3 buah serta terdapat operator logika "dan" didalamnya maka rumusnya adalah sebagai berikut :
=IF(C2="N",15%*E2,IF(AND(C2="B",D2>10),7,5%*E2,IF(AND(C2="B",D2>3),3%*E2,0)))
Fungsi Pembacaan Tabel
Fungsi berikut ini digunakan untuk membaca tabel
data/informasi dengan membandingkan nilai kunci yang terletak di kolom pertama
tabel data secara vertikal.
=VLOOKUP(Nilai Kunci, Range Tabel, Offset Kolom)
=VLOOKUP(Nilai Kunci, Range Tabel, Offset Kolom)
Sedangkan bila pembandingannya dilakukan secara horizontal (per baris) maka
fungsinya adalah sebagai berikut :
=HLOOKUP(Nilai Kunci, Range Tabel, Offset Baris)
=HLOOKUP(Nilai Kunci, Range Tabel, Offset Baris)
Catatan :
Nilai Kunci, adalah data yang berupa angka, kode tertentu atau teks yang digunakan sebagai pembanding data
Range Tabel, Adalah alamat tabel yang berisi data atau informasi lengkap sebagai acuan atau referensi untuk mengisi tabel soal.
Syarat Pembacaan Tabel :
Data pembanding pada tabel data harus terletak di kolom pertama
Range Tabel dan harus urut naik (Ascending)
Data pembanding pada kolom pertama bisa berupa teks, angka atau
data logika, huruf besar atau kecil tidak dibedakan.
Offset Kolom atau Offset baris
Adalah nomor kolom atau nomor baris data yang akan dibaca. Misalnya diketahui lembar kerja dengan tabel berikut ini :
Nilai Kunci, adalah data yang berupa angka, kode tertentu atau teks yang digunakan sebagai pembanding data
Range Tabel, Adalah alamat tabel yang berisi data atau informasi lengkap sebagai acuan atau referensi untuk mengisi tabel soal.
Syarat Pembacaan Tabel :
Data pembanding pada tabel data harus terletak di kolom pertama
Range Tabel dan harus urut naik (Ascending)
Data pembanding pada kolom pertama bisa berupa teks, angka atau
data logika, huruf besar atau kecil tidak dibedakan.
Offset Kolom atau Offset baris
Adalah nomor kolom atau nomor baris data yang akan dibaca. Misalnya diketahui lembar kerja dengan tabel berikut ini :
Untuk mengisi bagian kosong di kolom NAMA BRG, dipergunakan fungsi sebagai berikut :
=VLOOKUP(B2. $E$3:$f$5, 2)
Nilai Kunci ada di kolom B2 karena sama dengan kolom
pertama tabel data.
Range Tabel beralamat tetap (absolut) karena itu diberi awalan '$s' dan ditentukan mulai data yang ada dibawah judul kolom sampai dengan akhir label.
Offset kolomnya 2, karena data yang diinginkan adalah NAMA BRG yang terletak dikolom ke-2 dari range tabel data
Range Tabel beralamat tetap (absolut) karena itu diberi awalan '$s' dan ditentukan mulai data yang ada dibawah judul kolom sampai dengan akhir label.
Offset kolomnya 2, karena data yang diinginkan adalah NAMA BRG yang terletak dikolom ke-2 dari range tabel data
Fungsi lookup
Fungsi Hlookup dan Fungsi Vlookup merupakan bagian dari
Fungsi Lookup dan Referensi yaitu digunakan untuk pengolahan data dengan
mencocokan data dengan data pada tabel lainnya sebagai referensi.
Logika sederhana tentang penggunaan fungsi Hlookup dan Vlookup pada Microsoft Exceladalah ketika Anda mencari nomor telepon seseorang di buku telepon, Anda pasti akan mencari namanya terlebih dahulu baru kemudian melihat nomor telephonenya. Dengan fungsi Hlookup dan Vlookup, Anda akan mendapatkan nilai yang diinginkan berdasarkan kriteria yang Anda berikan yang terlebih dahulu akan dicocokan pada tabel referensi yang ada.
Syarat penyusunan tabel yang digunakan sebagai referensi adalah pada baris atau kolom pertama harus berdasarkan urutan dari kecil ke besar yaitu jika pada baris atau kolom pertama berupa data angka atau nilai maka urutannya harus -1,0,1 dan jika berupa data teks urutannya harus A-Z.
Bentuk Penulisan Fungsi Hlookup dan Vlookup :
Logika sederhana tentang penggunaan fungsi Hlookup dan Vlookup pada Microsoft Exceladalah ketika Anda mencari nomor telepon seseorang di buku telepon, Anda pasti akan mencari namanya terlebih dahulu baru kemudian melihat nomor telephonenya. Dengan fungsi Hlookup dan Vlookup, Anda akan mendapatkan nilai yang diinginkan berdasarkan kriteria yang Anda berikan yang terlebih dahulu akan dicocokan pada tabel referensi yang ada.
Syarat penyusunan tabel yang digunakan sebagai referensi adalah pada baris atau kolom pertama harus berdasarkan urutan dari kecil ke besar yaitu jika pada baris atau kolom pertama berupa data angka atau nilai maka urutannya harus -1,0,1 dan jika berupa data teks urutannya harus A-Z.
Bentuk Penulisan Fungsi Hlookup dan Vlookup :
=HLOOKUP(lookup_value;table_array;row_index_num;range_lookup)
=VLOOKUP(lookup_value;table_array;col_index_num;range_lookup)
=VLOOKUP(lookup_value;table_array;col_index_num;range_lookup)
lookup_value = nilai atau teks sebagai dasar untuk pembacaan tabel
table_array = merupakan tabel referensi
row_index_num atau col_index_num = nomor indeks baris (row) atau kolom (col) pada tabel referensi yang berisi nilai yang kita inginkan berdasarkan hasil pencocokan
range_lookup = bernilai True atau False
Masih bingung ? :-D
Contoh penggunaan fungsi HLOOKUP dan VLOOKUP :
Buka lembar kerja baru dan buatlah tabel seperti gambar berikut :
TABEL 1 merupakan tabel referensi yang disusun horisontal
TABEL 2 merupakan tabel referensi yang disusun vertikal
TABEL 3 dan TABEL 4 merupakan tabel yang mengandung nilai mengikuti tabel referensi
Penggunaan fungsi HLOOKUP dan VLOOKUP terletak pada bentuk tabel referensi yang kita gunakan. Nah, jika kita menggunakan tabel referensi dengan bentuk horisontal seperti pada TABEL 1, maka fungsi yang kita gunakan adalah fungsi HLOOKUP dan jika kita menggunakan tabel referensi yang berebentuk vertikal seperti TABEL 2 maka fungsi yang digunakan adalah VLOOKUP.
TABEL 3 menggunakan TABEL 1 sebagai tabel referensi, dan TABEL 4 menggunakan TABEL 2 sebagai tabel referensi
Lihat TABEL 3 dan masukkan fungsi berikut pada cell berikut ini :
Cell D12 masukkan fungsi =HLOOKUP(C12;C3:G5;2)
Cell D13 masukkan fungsi =HLOOKUP(C13;C3:G5;2)
Cell E12 masukkan fungsi =HLOOKUP(C12;C3:G5;3)
Cell E13 masukkan fungsi =HLOOKUP(C13;C3:G5;3)
Artinya : Nilai Cell D12, D13, E12 dan E13 adalah nilai yang terletak pada tabel referensi yang terletak pada range C3:G5 di baris ke-2 atau baris ke-3, yang diambil berdasarkan nilai pada cell C12 atau C13.
Jika fungsi yang dimasukkan benar maka TABEL 3 akan terlihat seperti berikut :
Lihat TABEL 4 dan masukkan fungsi berikut pada cell berikut ini :
Cell D17 masukkan fungsi =VLOOKUP(C17;I4:K8;2)
Cell D18 masukkan fungsi =VLOOKUP(C18;I4:K8;2)
Cell E17 masukkan fungsi =VLOOKUP(C17;I4:K8;3)
Cell E18 masukkan fungsi =VLOOKUP(C18;I4:K8;3)
Artinya : Nilai Cell D17, D18, E17 dan E18 adalah nilai yang terletak pada tabel referensi yang terletak pada range I4:K8 di baris ke-2 atau baris ke-3, yang diambil berdasarkan nilai pada cell C17 atau C18.
Jika fungsi yang dimasukkan benar maka TABEL 4 akan terlihat seperti berikut :
Jika diperhatikan fungsi yang kita gunakan pada contoh diatas tidak mengikutsertakan range_lookup sesuai dengan bentuk umum penulisannya. range_lookup mempunyai nilai True dan False. Nah, pada contoh diatas kita tidak menggunakannya maka range_lookup akan secara otomatis bernilai True.
Nilai TRUE berarti akan mengambil nilai terdekat, dan nilai FALSE akan mengambil nilai yang persis sama. untuk bagaimana perbedaan penggunannya, Anda bisa mencobanya dengan menambahkan nilai TRUE dan FALSE pada fungsi diatas dan lihat perubahannya. Misalnya pada Cell D12 masukan
fungsi =HLOOKUP(C12;C3:G5;2;TRUE) atau=HLOOKUP(C12;C3:G5;2;FALSE) dan lihat perbedaan nya.
