Rumus Dan Fungsi Microsoft Excel
FUNGSI TEKS
Fungsi ini dikhususkan untuk memanipulasi jenis data
string. Ada
1. LEFT
Fungsi ini digunakan untuk mengambil sejumlah karakter
dari sebelah kiri. Adapun cara penulisan fungsi ini adalah:
=LEFT(Teks,n)
dimana : Teks : merupakan string yang akan
dimanipulasi. Bisa berupa teks biasa ataupun nama suatu sel.
N : banyaknya jumlah karakter yang diambil dari Teks
dari sebelah kiri.
Contoh :
= LEFT (“Yogyakarta ”;
5) _ Yogya
= LEFT (“Aisyah”; 3) _
Ais
2. RIGHT
Seperti halnya fungsi left, fungsi right juga
digunakan untuk memanipulasi teks, yaitu mengambil sejumlah karakter tertentu dari sebelah kanan. Adapun syntaxnya adalah:
=RIGHT(Teks,n)
dimana : Teks : merupakan string yang akan
dimanipulasi. Bisa berupa teks biasa ataupun nama suatu sel.
N : banyaknya jumlah karakter yang diambil dari Teks
dari sebelah kanan.
Contoh :
= RIGHT (“Yogyakarta ”;
5) _ karta
= RIGHT (“Aisyah”; 3) _
yah
3. MID
Lain halnya dengan fungsi left dan right, fungsi mid
ini berfungsi untuk mengambil karakater berdasarkan ketentuan yang ditentukan sendiri. Adapun cara penulisannya
adalah:
=MID(Teks,m,n)
dimana : Teks : merupakan string yang akan
dimanipulasi. Bisa berupa teks biasa ataupun nama suatu sel.
M : urutan awal pengambilan karakter.
N : banyaknya jumlah karakter yang diambil dari Teks
di mulai karakter ke-M.
Contoh :
= MID (“Yogyakarta ”;
5; 4) _ akar
= MID (“Aisyah”; 2; 4) _
isya
4. VALUE
Fungsi value ini digunakan untuk mengembalikan angka
yang bertype string menjadi jenis numerik kembali.
Perintahnya adalah:
=VALUE(DataAngka)
dimana : DataAngka : adalah angka yang bertype data
string yang akan dikonversikan ke type data numerik
Contoh :
= VALUE(“12345”) _ 12345
FUNGSI LOGIKA
Fungsi ini digunakan untuk menyeleksi suatu kondisi
dari data yang ada dan memberikan hasil atau nilai yang berbeda sesuai dengan ketentuan yang diberikan. Untuk
memudahkan pemahaman fungsi logika, maka kita dapat membagi fungsi logika ini menjadi beberapa jenis, antara lain:
1. LOGIKA SANGAT SEDERHANA
Kita menyebut logika sederhana karena dalam kondisi
ini hanya ada dua keadaan yang kita uji. Misalkan kalau buka pria berarti wanita, begitu juga sebaliknya kalau buka
wanita pasti pria. Maka jika hanya ada dua keadaan yang diuji anda bisa menggunakan logika sangat sederhana ini
dengan syntax:
=IF(Kondisi;B;S)
dimana: Kondisi : merupakan keadaan atau kondisi atau
nilai dari suatu data yang kita uji. Nilai kondisi hanya
ada dua yaitu benar atau salah.
B : pernyataan atau hasil jika kondisi bernilai Benar
S : pernyataan atau hasil jika kondisi bernilai Salah.
Modul
Microsoft Excel Xp
Contoh:
Misalkan kita memeriksa apakah kode yang ada pada sel
A1 itu A atau B. Jika kodenya A, maka tuliskan Pria dan jika B isikan Wanita.
=IF(A1=”A”; “Pria”; “Wanita”)
atau
=IF(A1=”B” ; “Wanita”; “Pria”)
2. LOGIKA SEDERHANA
Jika keadaan yang harus diseleksi lebih dari dua, maka
kita bisa menggunakan fungsi logika sederhana. Adapun cara penulisan logika sederhana ini adalah:
=IF(Kondisi-1;B-1;IF(Kondisi-2;B-2;…;IF(Kondisi-n;B-n)))
Jumlah IF yang digunakan adalah sejumlah kondisi atau
minimal sejumlah kondisi-1
Contoh:
Jika sel A1 berisi data M, maka hasilnya Motor, jika
S, Sepeda dan jika B, maka hasilnya Becak.
= IF (A1=”M”; ”Motor”; IF (A1=”S”; ”Sepeda”; IF (A1=”B”;
”Becak”)))
atau = IF (A1=”M”; ”Motor”; IF (A1=”S”; ”Sepeda”; ”Becak”))
jumlah kurung tutup dibelakang rumus adalah sejumlah
IF.
3. LOGIKA OR
Logika OR ini digunakan jika ada seleksi dengan
menggunakan kata atau. Adapun cara penulisan dari fungsi ini adalah:
=IF(OR(Kondisi-1;Kondisi-2);B;S)
dimana: Kondisi-1 dan Kondisi-2 merupakan keadaan yang
diuji
B : pernyataan yang akan di isikan jika kondisi-1 dan
atau kondisi-2 bernilai benar
S : pernyataan yang akan dipakai jika Kondisi-1 dan
Kondisi-2 bernilai salah.
Contoh:
Jika sel A1 berisi data L atau sel B1 berisi data M,
maka dapat tunjangan 2500.
=IF(OR(A1=”L”; B1=”M”); 2500; 0)
atau
=IF(OR(B1=”M”; A1=”L”); 2500; 0)
4. LOGIKA AND
Sama seperti halnya logika OR, pada logika AND juga
harus ada dua kondisi atau keadaan yang diuji. Bedanya pernyataan Benar hanya akan dihasilkan jika kedua
kondisi bernilai benar.
=IF(AND(Kondisi-1;Kondisi-2);B;S)
dimana: Kondisi-1 dan Kondisi-2 merupakan keadaan yang
diuji
B : pernyataan yang akan di isikan jika kondisi-1 dan
Kondisi-2 bernilai benar
S : pernyataan yang akan dipakai jika Kondisi-1 atau
Kondisi-2 atau keduanya bernilai salah.
Contoh:
Jika sel A1 berisi data L dan sel B1 berisi data M,
maka dapat tunjangan 2500.
=IF(AND(A1=”L”; B1=”M”); 2500; 0)
atau
=IF(AND(B1=”M”; A1=”L”); 2500; 0)
5. LOGIKA AND OR
Dari logika AND dan OR yang telah kita bahas, bisa
dihasilkan sebuah logika baru yaitu AND OR. Untuk kombinasi ini, tentunya harus ada minimal tiga kondisi yang
diuji.
=IF(AND(OR(Kondisi-1;Kondisi-2); Kondisi-b); B; S)
dimana: Kondisi-1 dan Kondisi-2 adalah kondisi dari
logika OR sekaligus merupakan kondisi-a dari AND
Kondisi-b merupakan kondisi kedua dari fungsi AND
Contoh:
Jika sel A1 berisi data L atau sel B1 berisi data M,
dan C1 berisi data Q maka dapat tunjangan 2500.
=IF(AND(OR(A1=”L”; B1=”M”); C1=”Q”); 2500; 0)
6. LOGIKA OR AND
Untuk logika OR AND juga memiliki syntax yang hampir
sama yaitu:
=IF(OR(AND(Kondisi-a;Kondisi-b); Kondisi-2); B; S)
Contoh:
Jika sel A1 berisi data L dan sel B1 berisi data M,
atau C1 berisi data Q maka dapat tunjangan 2500.
=IF(OR(AND(A1=”L”; B1=”M”); C1=”Q”); 2500; 0)
CONTOH APLIKASI
Untuk mencoba memahami Excel lebih jauh, kita mulai
dengan aplikasi langsung. Siapkan sebuah buku kerja dan lembar kerja kosong, kemudian masukan data seperti dibawah
ini:
Ketika suatu nanti anda bekerja pada suatu perusahaan,
dan anda disuruh untuk menghitung gaji karyawan, tentunya anda membutuhkan perhitungan matematis, anda bisa
menggunakan microsoft Excel sebagai salah satu solusinya. Ketentuan perusahaan dalam menentukan gaji tiap karyawan, bisa
anda bayangkan sebagai soal yang harus anda kerjakan. Adapun soal itu adalah sebagai berikut:
1. Status pegawai diisi berdasarkan kode. Jika
karakter ke-3 kode =1, maka status menikah, dan jika karakter ke-3 dari kode=0 berarti statusnya belum menikah.
Untuk memudahkan kita menyelesaikan soal diatas, kita
buat catatan kecil untuk menguraikan masalah tersebut. Misalnya kita membuat catatan sebagai berikut:
Jika karakter ke-3 kode=1, status = menikah
Jika karakter ke-3 kode=0, status = belum menikah
Kemudian kita terjemahkan kedalam bahasa Excel,
menjadi:
IF(MID(A6;3;1)=”1”;”Menikah”;
IF(MID(A6;3;1)=”0”;”Belum Menikah”))
Karena hanya ada 2 kemungkinan, maka bisa disingkat
menjadi:
=IF(MID(A6;3;1)=”1”;”Menikah”;”Belum Menikah”)
anda ketik rumus tersebut pada sel D6 (karena
baris yang diseleksi adalah baris 6, sedangkan kita mengisi kolom D)
2. Jenis kelamin diisi berdasarkan karakter ke-3 dari
kanan, jika L=Laki-Laki dan P=Perempuan.
Seperti pada soal nomor satu diatas, kita buat logika
sederhananya dahulu:
Jika karakter ke-5=L, Laki-Laki. Selain itu Perempuan.
Sehingga bisa ditulis dalam Excel menjadi:
=IF(MID(A6;5;1)=”L”;”Laki-Laki”;”Perempuan”)
3. Jabatan diisi berdasarkan karakter pertama dari
kode, jika K=Karyawan, M=Manajer, S=Sekretaris
=IF(LEFT(A6,1)=”K”;”Karyawan”;IF(LEFT(A6,1)=”M”,”Manajer”,”Sekretaris))
4. Gaji pokok diisi dengan 2500000 untuk manajer,
1000000 untuk sekretaris serta 400000 untuk karyawan.
=IF(F6=”Manajer”;2500000;IF(F6=”Sekretaris”;1000000;400000))
5. Tunjangan makan diberikan sebesar 300000 untuk
manajer, 100000 untuk sekretaris dan 60000 untuk karyawan
=IF(F6=”Manajer”;300000;IF(F6=”Sekretaris”;100000;60000))
6. Tunjangan Makan diberikan hanya untuk karyawan
laki-laki sebesar 50000
=IF(AND(E6=”Laki-Laki”;F6=”Karyawan”);50000;0)
7. Khusus untuk manager tunjangan lembur sebesar
200000 dan lainnya sebesar 30000
=IF(F6=”Manajer”;200000;30000)
8. Tunjangan jabatan berupa sebuah mobil hanya untuk
manager atau sekretaris.
=IF(OR(F6=”Manajer”;F6=”Sekretaris”);”Mobil”;”-“)
9. Potongan masing-masing 15 persen dari seluruh gaji
=SUM(G6:K6) * 15%
10. Gaji bersih merupakan kalkulasi seluruh gaji
dikurangi potongan.
=SUM(G6:K6) – L6