Materi Kelompok 4

Formula dan Fungsi
•
•
•
•
Formula/rumus : untuk melakukan kalkulasi terhadap nilai-nilai pada sel itu sendiri
maupun nilai yg tersimpan pada sel-sel yang lain
Formula/rumus dapat terdiri dari : Operator perhitungan, referensi alamat suatu sel,
nilai, fungsi sel, nama sel/nama range
Fungsi : Jenis formula khusus siap pakai yg disediakan oleh Excell
Fungsi sebenarnya adalah rumus yang sudah ada disediakan oleh Excel, yang akan
membantu dalam proses perhitungan. Umumnya penulisan Fungsi harus dilengkapi
dengan argumen, baik berupa 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)".
I.
Penggunaan Rumus-Rumus Perhitungan Sederhana
Simbol Operasi Perhitungan
Contoh
Hasilnya
+
Penambahan
=20+5
25
Pengurangan
=20-5
15
*
Perkalian
=20*5
100
/
Pembagian
=20/5
4
^
Perpangkatan
=2^5
32
%
Prosentase
=20%
0.2
II. PENGALAMATAN SEL
Terdapat 3 macam pengalamatan dalam Excel. Yaitu Alamat Sel Relatif, Alamat
Sel Semi Absolutda n Alamat Sel Absolut.
• Alamat Sel Relatif
merupakan alamat yang jika dituliskan kedalam bentuk rumus atau fungsi
akan berubah jika dicopy ke cell lain.
Jika suatu rumus, misal= A1 *B1 di-copy dari C1 sampai C10, maka alamat
barisnya akan berubah menjadi=A2*B2, =A3*B3, dst. Demikian juga bila rumus
tersebut dicopy dari C10 sampai H10 maka alamat kolomnya juga akan
berubah. Metode pengalamatan ini disebut sebagaialamat sel relatif, yaitu
alamat sel yang berubah-ubah baik kolom maupun barisnya.
Contoh : cell berisi formula A5*6 ,B3 dicopy ke C5 formula pada C5 berubah
menjadi B8*6
1|Aplikasi Manajemen Perkantoran B
•
•
Alamat Sel Semi Absolut
merupakan alamat yang dituliskan dengan tanda $ didepan baris atau
kolom sehingga nilai tidak akan berubah.
Contoh : Cell B1 berisi formula $A1*7,B1 dicopy kan ke D5 formula pada D5
menjadi $A5*7
Alamat Sel Absolut
merupakan alamat yang dituliskan dengan tanda $ didepan baris dan
kolom.tekan tombol F4 untuk menghasilkan alamat absolut pada formula bar.
Contoh : cell B1 berisi formula $A$1&5,B1 dicopy kan ke C3 formula pada C3
menjadi $A$1*5
III. JENIS-JENIS FUNGSI DAN PENGGUNAAN FUNGSI
A. Teks
CONCATENATE
digunakan untuk menggabungkan string menjadi satu kalimat
Bentuk Umum : =CONCATENATE(X1,X2,X3……)
Contoh:
=Concatenate(“Total”,”Nilai”)
”TotalNilai”
sel C2 berisi teks “Universitas”
Sel C3 bernilai teks “Gunadarma”
sel C4 berisi Nilai 50410.
maka
:
=Concatenate(c2,”-”,c3,” Telp.”,c4)
Universitas-Gunadarma Telp. 50410
LEFT
Mengambil beberapa huruf suatu teks dari posisi sebelah kiri
Bentuk Umum : =LEFT(X,Y)
X:
alamat sel atau teks yang penulisanya diapit dengan tanda petik ganda
Y:
jumlah atau banyaknya karakter yang diambi
Contoh:
=Left(“Informatika”,4)
”Info”
Jika sel C2 berisi teks “Informatika”,
=Left(C2,4) Info
RIGHT
Mengambil beberapa huruf suatu teks dari posisi sebelah kanan
Bentuk Umum : =RIGHT(X,Y)
X:
alamat sel atau teks yang penulisanya diapit dengan tanda petik ganda
Y:
jumlah atau banyaknya karakter yang diambil
Contoh:
=RIGHT(“Informatika”,5) ”atika”
2|Aplikasi Manajemen Perkantoran B
Jika sel C2 berisi teks “Informatika”,
=Left(C2,4) tika
LEN
Untuk menghitung panjang suatu teks dan nilai yang dihasilkan berupa angka.
Bentuk Umum: LEN(Teks)
Contoh :
Jika sel C2 berisi teks “Informatika”
=Len(C2) 11
MID
Mengambil beberapa huruf suatu teks pada posisi tertentu
Bentuk Umum :=MID(X,Y,Z)
X:
alamat sel atau teks yang penulisanya diapit dengan tanda petik ganda
Y:
Posisi awal karakter
Z:
jumlah atau banyaknya karakter yang diambil
Contoh:
=MID(“Informatika”,2,5) ”forma”
LOWER
mengubah semua karakter dalam setiap kata yang ada pada suatu teks dalam
huruf kecil
Bentuk Umum : =LOWER(X)
X:
alamat sel atau teks yang penulisanya diapit dengan tanda petik ganda
Contoh:
=LOWER(“INFORMATIKA”)
” informatika”
Jika sel C2 berisi teks “INFORMATIKA”,
=LOWER(C2) informatika
UPPER
mengubah semua karakter dalam setiap kata yang ada pada suatu teks dalam
huruf besar
Bentuk Umum : =UPPER(X)
X : alamat sel atau teks yang penulisanya diapit
dengan tanda petik ganda
Contoh:
=UPPER(“informatika”) ” INFORMATIKA”
Jika sel C2 berisi teks “informatika”,
=UPPER(C2) INFORMATIKA
PROPER
mengubah karakter pertama awal dalam setiap kata yang ada pada suatu teks
dalam huruf besar
Bentuk Umum : =PROPER(X)
X:
alamat sel atau teks yang penulisanya diapit dengan tanda petik ganda
3|Aplikasi Manajemen Perkantoran B
Contoh:
=PROPER(“informatika”) ” Informatika”
Jika sel C2 berisi teks “informatika”,
=PROPER(C2)
Informatika
TRIM
menghapus semua spasi dari string teks kecuali untuk ruang tunggal antara
kata-kata, penulisan nya : =TRIM(text1,text2,…)
FIND
Menentukan posisi satu huruf atau satu teks dari suatu kata atau kalimat.
Bentuk Umum : =FIND(X,Y,Z)
X : alamat sel atau teks yang penulisannya diapit dengan tanda petik ganda
Y:
kata atau kalimat yang mengandung satu huruf
atau satu teks yang
dicari posisinya yang dapat
diawakili oleh penulisan alamat sel.
Z:
nilai numerik yang menyatakan dimulainya posisi
pencarian.
Contoh:
=FIND(“f”,“Informatika”)
3
=FIND(“d”,“MADCOMS Madiun”) 11
=FIND(“M”,“MADCOMS Madiun”) 6
B.
FUNGSI Matematika
INT(Bilangan)
Untuk membulatkan angka ke nilai integer (bulat) yang terendah.
Contoh : =INT(5,8) hasilnya : 5
MOD(Bilangan,Pembagi)
Untuk mencari sisa pembagian. Sisa pembagian bertanda sesuai dengan
Pembagi.
Contoh :=MOD(9,2) artinya 9 dibagi 2 sisa 1, hasilnya adalah 1
SUM
Fungsinya : Untuk melakukan penjumlahan
Bentuk umum : =SUM(range sel)
Contoh : Misalkan kita akan menjumlahkan dari sel H5 sampai sel H15
Contoh : =SUM(H5:H15)
SUMIF
Menjumlahkan data yang bersifat numerik dalam suatu range data dengan
kriteria tertentu
Bentuk umum : =SUMIF(range,criteria,sum_range)
ROUND
Untuk membulatkan angka sesuai digit pembulatan. Digit nol terletak tepat
pada desimal sedangkan di depan d esimal adalah digit negatif dan di belakang
desimal adalah digit positif.
4|Aplikasi Manajemen Perkantoran B
Bentuk umum : =ROUND(Bilangan,Digit Pembulatan)
ABS
Fungsinya : Untuk mengubah angka-angka yang ada dalam daftar
argumennya menjadi bilangan mutlak (absolut)
Bentuk umum : =ABS(range sel)
Contoh : Misalkan kita bermaksud mengetahui secara mutlak dari
sel yang kita aktifkan, misal di sel F17 =ABS(F17)
SQRT
Fungsinya : Untuk menghitung akar dari bilangan X. Bilangan X tidak
boleh negatif
Bentuk umum : =SQRT(range sel)
Contoh
: = SQRT(25) 5
C. FUNGSI Logika
Fungsi logika adalah fungsi yang digunakan untuk menyelesaikan perhitunganperhitungan yang berhubungan dengan pengambilan keputusan fungsi logika yang
digunakan adalah :
Fungsi Logika IF
Memilih antara 2 kemungkinan atau lebih dari perbandingan logika yang
diberikan
Bentuk Umum : =IF(logical_test , Value_if_true , Value_if_false)
Contoh:
=IF(C2>70,”LULUS”,”TIDAK LULUS”)
Artinya jika isi sel C2 lebih besar dari 70 maka cetak kata LULUS, tetapi jika sel
C2 lebih kecil atau sama dengan 70 cetak kata TIDAK LULUS
=IF((A1*E1)<=7000,”MURAH”,”MAHAL”)
Artinya Jika hasil perkalian dari sel A1 dan E1 lebih kecil sama dengan 7000
maka cetak kata MURAH, tetapi jika hasil perkalian lebih dari 7000 cetak kata
MAHAL.
Nested IF
Adalah IF yang berbeda dalam IF, digunakan untuk menentukan kondisi dari
beberapa logika sekaligus
Contoh :
=IF(A2<20,”KURANG”,IF(A1<30,”CUKUP”,”BAIK”))
Jika nilai A2 kurang dari 20 dinyatakan KURANG,
jika nilai A1 kurang dari 30 dinyatakan CUKUP selain itu (lebih atau sama
dengan 30) dinyatakan BAIK
5|Aplikasi Manajemen Perkantoran B
AND
Fungsi ini menghasilkan nilai benar, bila berargumen benar. Hasil dari fungsi ini
juga dapat menghasilkan nilai salah, jika salah satu argumen salah.
TRUE jika semua argumen bernilai benar(TRUE); menghasilkan false jika satu
atau lebih argumen salah.
Bentuk umum =AND(X1,X2,X3….)
X1,X2,X3 : kondisi yang akan diuji atau alamat sel yang mengandung nilai logika
yang menghasilkan nilai benar(true) atau salah (false).
Contoh:
Jika sel B1=TRUE,sel B2=FALSE, sel B3=TRUE= AND(B1:B3)= False
=IF(AND(rata-rata>=65,rata-rata<=90,”LULUS”,”GAGAL”)
Hal ini berarti jika rata-rata lebih besar atau sama dengan 65 dan jika rata-rata
lebih kecil atau sama dengan 90 maka dinyatakan LULUS, tapi kalo salah satu
dari argumen itu bernilai salah maka hasilnya adalah GAGAL.
OR
Fungsi ini menghasilkan nilai benar, bila salah satu atau lebih berargumen benar.
Hasil dari fungsi ini juga dapat menghasilkan nilai salah, jika seluruh argumen
salah. Menghasilkan TRUE jika salah satu atau beberapa argumen bernilai
benar(TRUE); menghasilkan false jika semua argumen salah.
Bentuk Umum : =OR(X1,X2,X3….)
X1,X2,X3 : kondisi yang akan diuji atau alamat sel yang mengandung nilai logika
yang menghasilkan nilai benar(true) atau salah (false).
TRUE & FALSE
TRUE jika semua argumen bernilai benar(TRUE); menghasilkan false jika satu
atau lebih argumen salah.
=AND(X1,X2,X3….)
X1,X2,X3 : kondisi yang akan diuji atau alamat sel yang mengandung nilai logika
yang menghasilkan nilai benar(true) atau salah (false).
Contoh:
Jika sel B1=TRUE,sel B2=FALSE, sel B3=TRUE= AND(B1:B3) False
=IF(AND(rata-rata>=65,rata-rata<=90,”LULUS”,”GAGAL”)
Hal ini berarti jika rata-rata sebesar 65 dinyatakan LULUS, jika
rata-rata 64.5 maka dinyatakan GAGAL
D.
Fungsi Statistik
Excel menyediakan beberapa fungsi statistik yang digunakan untuk
menyelesaikan persoalan statistika. Macam-macam fungsi statistik sederhana
yang sering digunakan di Excel adalah sebagai berikut:
6|Aplikasi Manajemen Perkantoran B
1.
2.
3.
4.
5.
6.
AVERAGE : digunakan untuk mencari nilai rata-rata dari suatu range
MAX
: digunakan untuk mencari nilai tertinggi dari suatu range
MIN
: digunakan untuk mencari nilai terendah dari suatu range
COUNT
: digunakan untuk mencara banyaknya data dari satu range
MEDIAN(Angka atau Alamat Sel), untuk mencari Nilai Tengah
RANK Menghasilkan ranking suatu bilangan diantara sekumpulan data
Bentuk Penulisan :
=RANK(number,ref,order)
7. COUNTIF
: digunakan untuk mencari berapa jumlah data yang
memenuhi kriteria tertentu
Bentuk umum : =COUNTIF(range,criteria)
dimana pada area yang disebutkan di range akan dicari berapa jumlah sel
yang sesuai dengan criteria.
Contoh: dalam sebuah daftar nilai ingin diketahui berapa orang yang
mendapat nilai “A”.
=COUNTIF(B2:B57,”A”) artinya dicari berapa jumlah sel yang berisi “A” pada
range B2 sampai B57.
E. FUNGSI PENANGGALAN
DATE
=DATE(Tahun,Bulan,Tanggal)
Menghasilkan bilangan hari dari tahuhn 1900 bulan 1 (Januari) tanggal 1 hingga
tahun 2078 bulan 12 (Desember) tanggal 31. Jadi menghasilkan bilangan 1
sampai 65380 dalam hitungan hari. Jika penulisan bulan atau tanggal melebihi
bilangan standart, maka akan dimulai dari awal.
Misalnya=D A TE (19 98, 13,3 4) berarti tanggal 3 bulan Januari tahun 1998
Fungsi ini merupakan cara dasar untuk menuliskan tanggal,bulan dan tahun.
Bentuk penulisan : =DATE(years,mounth,date)
TIME(Jam,Menit,Detik)
Menghasilkan bilangan waktu dari 00:00:00 (12:00:00 AM) hingga 23:59:59
(11:59:59 PM). Jadi menghasilkan bilangan 1 sampai 0,99999999 dalam
hitungan hari.
Misalnya= TI M E(1 2:00 :00 ) akan menghasilkan bilangan0,5 atau=TI M E(
06:0 0:0 0) akan menghasilkan bilangan0,2
DAY
Fungsi ini berguna untuk menampilkan tanggal saja tanpa bulan dan tahun
Penulisan : =DAY(serial_number)
Contoh ; =DAY(DATE(1997,5,4)) MENGHASILKAN 4
7|Aplikasi Manajemen Perkantoran B
HOUR
Menghasilkan angka integer dari suatu jam ,tanpa menyebut menit dan detiknya.
Bentuk penulisan : =HOUR(serial_number)
Contoh : =HOUR(“3:30:30 PM”) menghasilkan 15
MOUNTH
Berguna untuk menampilkan bulan saja tanpa tanggal dan tahun
Penulisan : =MOUNTH(serial_number)
Contoh : =MOUNTH(“6-MAY”) menghasilkan 5
NOW
Menghasilkan momer seri yg menunjuk pda tanggal/waktu saat ini menurut
tanggal/waktu yang di catat oleh system computer
Penulisan : =NOW()
{Fungsi ini tidak memiliki argument karena exel ketika menerima fungsi ini
langsung membaca tanggal system saat itu}.
Nilai Kesalahan
Nilai kesalahan adalah salah satu bentuk kode yang dipergunakan Excel ketika
ia tidak dapat memahami sebuah formula yang Anda masukkan. Tampilnya nilai
kesalahan belum tentu disebabkan kesalahan yang terjadi pada sel di mana formula itu
ditulis. Kemungkinan ada kaitannya dengan nilai-nilai yang tertulis atau nilai yang hilang
dari sel-sel lain yang menjadi referensi dari formula tersebut.
Untuk lebih jelasnya, berikut ini macam-macam nilai kesalahan yang ditampilkan
bila ada kesalahan atau sesuatu yang tidak dimengerti oleh Excel.
8|Aplikasi Manajemen Perkantoran B
F. FUNGSI VLOOKUP DAN HLOOKUP
fungsi VLOOKUP
Fungsi Vlookup ini digunakan untuk membaca tabel secara vertical (tegak).
Bentuk penulisannya adalah :
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Dimana: • lookup_value adalah data yang akan dicocokkan.
• table_array adalah tempat pencarian data.
• col_index_num adalah data pada kolom keberapa yang hendak
diambil.
• range_lookup (optional) adalah nilai logika yang dimasukkan, jika diisi
TRUE maka akan dicari sampai data terdekat, sedang jika diisi FALSE
maka akan dicari data yang persis sama.
Contoh:
Solusi:
:
Pada A8 masukkan nilai NIM terlebih dahulu yang terdapat pada tabel NIMNama. kemudian pada B8 ketikkan formula =VLOOKUP(A8;$A$2:$B$5;2;0).
9|Aplikasi Manajemen Perkantoran B
fungsi hlookup
Fungsi Hlookup ini digunakan untuk membaca tabel secara horizontal
(mendatar).
Bentuk penulisannya adalah :
=HLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Dimana: • lookup_value adalah data yang akan dicocokkan.
• table_array adalah tempat pencarian data.
• col_index_num adalah data pada kolom keberapa yang hendak
diambil.
• range_lookup (optional) adalah nilai logika yang dimasukkan, jika
diisi TRUE maka akan dicari sampai data terdekat, sedang jika diisi
FALSE maka akan dicari data yang persis sama.
Contoh:
solusi
Pada C6 ketikkan formula =HLOOKUP(B6;$B$1:$F$3;2;0).
:
• lookup value
Pada prinsipnya sama dengan Vlookup, namun pada lookup value ini
memungkinkan kita untuk mengambil beberapa data dari tabel lain sabagai
referensi / patokan.
- Dari 2 tabel yakni tabel peminjaman dan tabel buku akan dibuat Daftar
Peminjaman Buku.
10 | A p l i k a s i M a n a j e m e n P e r k a n t o r a n B
Solusi:
– Nama pada cell B11 adl =VLOOKUP(A11;$A$2:$D$5;2;0)
– Perihal pada cell D11 adl = VLOOKUP(C11;$F$2:$H$5;2;0)
– Judul pada cell E11 adl = VLOOKUP(C11;$F$2:$H$5;3;0)
Function Wizard
Fungsi wizard merupakan kemudahan yang di sediakan bagi pemakai exel
untuk memasukan fungsi-fungsi yang tersedia dengan fasilitas ini , anda tidak
perlu menghafal seluruh fungsi excel. Anda akan dipersilahkan memilih fungsi
yang di kehendaki sesuai dengan kategori masing-masing.Setelah fungsi terpilih,
langkah selanjutna yaitu akan dipeliharakan wizard kedua yang mempersilahkan
anda memasukan masing-masing argumentnya. Untuk lebih jelasnya langkah
menggunakan Function Wizard adalah sebagai berikut :
1. Klik menu insert , function sehingga akan ditampilkan function wizard step I
Atau caranya klik pada toolbar:
11 | A p l i k a s i M a n a j e m e n P e r k a n t o r a n B
2. Pilih kategori yang dikehendaki pada kotak or select a category sehingga
pada kotak select function name yang berada di bawahnya di tampilkan
nama-nama fungsi excel dari kategori yang dimaksud . geserlah scroll bar
bila nama fungsi .yang dikehendaki belum Nampak pada kotak tersebut.
3. Pilihlah fungsi yang dikehendaki dengan mengklik nama fungsi tersebut
,kemudian klik tombol OK,selain itu juga dapat mengklik ganda pada nama
fungsi
4. Pada function wizard step 2 function argument diperlihatkan kotak edit dari
masing-masing argument dari fungsi tersebut untuk mengisikan pada kotak
edit tersebut anda dapat mengklik kotak tersebut kemudian ketikan
argumennya . apa bila argumentnya berupa sel,selain dengan cara mengetik
alamat sel juga dapat dilakukan dengan mengklik maupun memblok sel atau
range yang dikehendaki tombol OK
12 | A p l i k a s i M a n a j e m e n P e r k a n t o r a n B
5. Setelah pergeseran argument selesai ,akhiri dengan mengklik tombol ok.
13 | A p l i k a s i M a n a j e m e n P e r k a n t o r a n B