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
