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 P E R T E M U A N 5 F O R M U L A & F U N G S I

APLIKASI MANAJEMEN PERKANTORAN B P E RT E M UA N 5 – F O R M U L A & F U N G S I

PEMBAHASAN •Penggunaan rumus - rumus Perhitungan Sederhana •Pengalamatan Sel (Alamat Relatif dan Alamat Absolut) •Jenis-Jenis Function & Penggunaan Fuction •Teks CONCATENATE,LEFT.RIGHT,LEN,MID,TRIM, dan lain-lain •Matematika SUM,ABS,SQRT,SUMIF,ROUND,dan lain-lain •Logika AND,OR,NOT,TRUE,FALSE,IF •Statistik AVERAGE,COUNT,COUNTIF,MAX,MIN, dan lain-lain •Penanggalan DATE,DAY,HOUR,MONTH,NOW, dan lain-lain •HLOOKUP,VLOOKUP

PENGGUNAAN RUMUS - RUMUS PERHITUNGAN SEDERHANA

PENGGUNAAN RUMUS - RUMUS PERHITUNGAN SEDERHANA • 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().

Ciri-ciri dari fungsi ini maupun fungsi-fungsi lain yaitu : – Diawali dengan tanda “=” – Memasukkan letak data ketika menuliskan rumus

PENGGUNAAN RUMUS - RUMUS PERHITUNGAN SEDERHANA • Fungsi Aritmatika Fungsi aritmatika adalah suatu fungsi matematika sederhana pada Microsoft Excel yang terdiri dari penjumlahan, pengurangan, perkalian, pembagian, pangkat dan persentase.

• Fungsi : – Penjumlahan – Pengurangan

– Perkalian – Pembagian – Pemangkatan – Persentase

PENGGUNAAN RUMUS - RUMUS PERHITUNGAN SEDERHANA 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)". Contoh lain :

1. Pejumlahan = menjumlahkan satu angka bilangan dengan angka bilangan yang lain. • Contoh : =A2+B2

PENGGUNAAN RUMUS - RUMUS PERHITUNGAN SEDERHANA 2.

Pengurangan = mengurangkan satu angka bilangan dengan angka bilangan yang lain. Contoh : =A2-B2

3.

Perkalian = mengalikan satu angka bilangan dengan angka bilangan yang lain. Contoh : =A2*B2

3.

Pembagian = membagi satu angka bilangan dengan angka bilangan yang lain. Contoh : =A2/B2

PENGGUNAAN RUMUS - RUMUS PERHITUNGAN SEDERHANA 4.

Pangkat = memangkatkan satu angka bilangan dengan angka bilangan yang lain. Contoh : =A2^B2

5.

Persentase = mempersentasekan suatu angka bilangan Contoh : =A2%

PENGALAMATAN SEL

PENGALAMATAN SEL Sel Absolut dan Sel Relatif • Dalam membaca sebuah alamat sel, Microsoft Excel mengenal 2 jenis pembacaan alamat sel, yaitu alamat sel relative dan alamat sel absolute.

• Alamat sel relative ialah alamat sel yang dapat berubah jika disalin atau dicopy dari sel satu ke sel lain, sedangkan alamat sel absolute ialah alamat sel yang tidak berubah jika disalin atau dicopy dari sel satu ke sel lain. • Ketika sebuah rumus alamat sel relative dan dicopy dari satu sel ke sel lain, Microsoft Excel tidak membuat salinan berupa rumus, namun langkah ini akan mengubah alamat sel relative kolom dan baris yang dipindahkan.

PENGALAMATAN SEL • Sel Relatif :

alamat yang jika dituliskan kedalam bentuk rumus atau fungsi akan berubah jika dicopy ke cell lain. Contoh : sel C1 "=(A1+B1)" dicopy ke sel C2, berubah menjadi "=(A2+B2)“

• Sel Absolut :

alamat yang dituliskan dengan tanda $ didepan baris dan kolom. tekan tombol F4 untuk menghasilkan alamat absolut pada formula bar. Contoh : sel B1 berisi formula $A$1*5,B1 dicopy kan ke sel C3 formula pada C3 tetap berisi formula $A$1*5

PENGALAMATAN SEL Contoh Penggunaan Sel Relatif

PENGALAMATAN SEL Contoh Penggunaan Sel Absolut

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION Fungsi

Contoh

Keterangan

SUM

=SUM(A1:100)

Menjumlahkan sel A1 sampai sel A100

AVERAGE

=AVERAGE(B1:B10)

Menghitung nilai rata-rata sel B1 sampai sel B10

MAX

=MAX(C1:C100)

Mencari nilai tertinggi dari sel C1 sampai C100

MIN

=MIN(D1:D100)

Mencari nilai terendah dari sel D1 sampai D100

SQRT

=SQRT(D10)

mengakarkan nilai dalam sel D10

TODAY

=TODAY()returns

Mengambil tanggal dari system komputer dengan format default

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION • FUNGSI TEKS •

CONCATENATE Menggabungkan beberapa teks dalam suatu teks

Bentuk Umum : =CONCATENATE(X1,X2,X3……) Contoh: =Concatenate(“Total”,”Nilai”) sel C2 berisi teks “Madcoms” Sel C3 bernilai teks “Madiun” sel C4 berisi Nilai 53246. maka : =Concatenate(c2,”-”,c3,” Telp.”,c4) Madcom-Madium Telp. 53246

”TotalNilai”

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION • LEFT Mengambil beberapa huruf suatu teks dari posisi sebelah kiri Bentuk Umum : =LEFT(X,Y) X: Y:

alamat sel atau teks yang penulisanya diapit dengan tanda petik ganda jumlah atau banyaknya karakter yang diambil

Contoh: =Left(“Madcom”,3)

”Mad”

Jika sel C2 berisi teks “Madcoms”, =Left(C2,5) Madco

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION • RIGHT Mengambil beberapa huruf suatu teks dari posisi sebelah kanan Bentuk Umum : =RIGHT(X,Y) X: Y:

alamat sel atau teks yang penulisanya diapit dengan tanda petik ganda jumlah atau banyaknya karakter yang diambil

Contoh: =RIGHT(“Madcom”,3) ”com” Jika sel C2 berisi teks “Madcom”, =Left(c2,5) adcom

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION • MID Mengambil beberapa huruf suatu teks pada posisi tertentu Bentuk Umum :=MID(X,Y,Z) X: Y: Z:

alamat sel atau teks yang penulisanya diapit dengan tanda petik ganda Posisi awal karakter jumlah atau banyaknya karakter yang diambil

Contoh: =MID(“Madcom”,2,4) ”adco” Jika sel C2 berisi teks “Madcom”, =Left(c2,4,3) com

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION • 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(“MADCOM”)

” madcom”

Jika sel C2 berisi teks “MADCOM”, =LOWER(c2) madcom

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION • 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(“D”,“MADCOMS Madiun”) 3 =FIND(“d”,“MADCOMS Madiun”) 11 =FIND(“M”,“MADCOMS Madiun”) 6

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION FUNGSI MATEMATIKA Contoh :

•

SUM digunakan untuk menampilkan hasil penjumlahan nilai numerik. Struktur penulisannya :

=SUM(range nilai numerik)

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION •

ABS Menentukan harga mulak (Absolut) nilai numerik.

• Bentuk Umum : =ABS(x) Contoh: =(ABS(-21) =(ABS(-21)

 21 7

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION • INT Membulatkan bilangan pecahan dengan pembulatan ke bawah ke bilangan bulat terdekat. Bentuk Umum : =INT(X) Contoh: =(INT(219.71)  219 =(INT(-10.71)  -11

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION • ROUND Menghasilkan nilai pembulatan data numerik sampai jumlah digit desimal tertentu . Bentuk Umum : =ROUND(X,Y) Contoh: =(round(21.9120001,4)  21.912000 =(round(17.3120008,4)  17.3198

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION • Trunc Menghilangkan bagian dari nilai pecahan tanpa memperhatikan pembulatan dari suatu data numerik . Bentuk Umum : =TRUNC(X,Y) Contoh: =(trunc(21.20001,0) =(trunc(17.378,2)

 21  17

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION • Trunc Menghilangkan bagian dari nilai pecahan tanpa memperhatikan pembulatan dari suatu data numerik . Bentuk Umum : =TRUNC(X,Y)

Contoh: =(trunc(21.20001,0) =(trunc(17.378,2)

 21  17

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION • FUNGSI STATISTIK Fungsi statistik adalah fungsi yang digunakan untuk mengolah data yang berupa numerik (angka) hanya saja operasi ini dilakukan dengan statistik. • Contoh : •

MIN, digunakan untuk menampilkan nilai numerik yang terkecil. Struktur penulisannya : =MIN(range nilai numerik)



MAX, digunakan untuk menampilkan numerik terbesar. Struktur penulisannya : =MAX(range nilai numerik)


AVERAGE, digunakan untuk menampilkan rata-rata. Struktur penulisannya : =AVERAGE(range nilai numerik)


COUNT, digunakan untuk menghitung jumlah sel dalam suatu range atau group data yang berupa angka. Struktur penulisannya : =COUNT(range nilai numerik)


COUNT IF ,pada hakikatnya mempunyai fungsi untuk menghitung jumlah cell sesuai kriteria yang kita tentukan dari suatu range data yang tersedia. Struktur penulisannya adalah : =COUNTIF(range,criteria) Keterangan :

• Criteria diberi tanda (“ “) Contoh : • Banyak nilai yang kurang dari 75

Dari contoh di atas rangenya adalah B1:B6 , criterianya adalah <75 , maka rumus fungsi D3 ditulis =COUNTIF(B1:B6;”<75”)


COUNTA,digunakan untuk menghitung jumlah sel dalam satu range atau group data yang berupa angka dan huruf. Struktur penulisannya: =COUNTA(range nilai numerik)

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION • Fungsi Logika • Fungsi Logika merupakan sekumpulan mengoperasikan data secara logika.

fungsi

yang

berisi

perintah-perintah

untuk

Contoh : •

IF Tunggal , Struktur Penulisannya : =IF(logical_test,value_if_true,value_if_false) atau =IF(nilai yang dicek, nilai jika kondisi benar, nilai jika kondisi salah)

• Keterangan : value_if_true dan value_if_false diberi tanda (“ ”) • Contoh : Jika nilai lebih dari 70 maka “Tuntas” dan jika tidak maka “Remedi”


• Dari contoh diatas logical test adalah A2>70 , value_if_true adalah Tuntas, dan value_if_false adalah Remedi , maka rumus fungsi B2 ditulis =IF(A2>70;”Tuntas”;”Remedi”)


IF Ganda, ada kalanya permasalahan tidak cukup diselesaikan dengan satu (1) fungsi saja, tetapi harus diselesaikan dengan beberapa fungsi if. Struktur umum formula : =IF(logical_test 1,value_if_tru 1,IF(logical_test 2,value_if_tru 2,value_if_false)

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION Contoh : Jika A1 maka IPA , A2 maka IPS, dan A3 maka Bahasa

• Dari contoh diatas logical test 1 adalah B3=”A1” ,value_if_true 1 adalah IPA , logical test 2 adalah B3=”A2” , value_if_true 2 adalah IPS, dan value_if_false adalah Bahasa, maka rumus fungsi C3 ditulis =IF(B3=”A1”;”IPA”;IF(B3=”A2”;”IPS”;”Bahasa”))


SUM IF, sebuah fungsi untuk menjumlahkan nilai-nilai yang terdapat pada suatu range data, namun range yang diambil didasarkan pada kategori yang kita inginkan. Struktur Penulisannya: =SUMIF(range,criteria,sum_range) Keterangan

• Criteria diberi tanda (“ “)


• Dari contoh diatas rangenya adalah B3:B13, criterianya adalah XL , dan sum rangenya adalah C3:C13, maka rumus fungsi C14 ditulis =SUMIF((B3:B13;”XL”;C3:C13)


AVERAGE IF, sebuah fungsi untuk mencari nilai rata-rata yang terdapat pada suatu range data, namun range yang diambil didasarkan pada kategori yang kita inginkan. Struktur Penulisannya:

=AVERAGEIF(range,criteria,average_range) Keterangan : • Criteria diberi tanda (“ “) Contoh : • Mencari Nilai Rata-Rata Laki-Laki (L)


• Dari contoh di atas rangenya adalah C3:C12 , criterianya adalah “L”, dan average rangenya adalah D3:D12, maka rumus fungsi D15 ditulis =AVERAGEIF(C3:C12;”L”;D3:D12)

JENIS-JENIS FUNCTION & PENGGUNAAN FUCTION • Nested IF adalah IF yang berbeda dalam IF, digunakan untuk menentukan kondisi dari beberapa logika sekaligus =IF(A2<20,”KURANG”,IF(A1<30,”CUKUP”,”BAIK”)) • Jika nilai A1 kurang dari 20 dinyatakan KURANG, • jika nilai A1 kurang dari 30 dinyatakan CUKUP • selain itu (lebih atau sama dengan 30) dinyatakan BAIK.

HLOOKUP & VLOOKUP HLOOKUP (X,Y,Z)

Fungsi : mencari data atau mengurutkan nilai data dimana table ketentuannya berbentuk horizontal (data tersusun ke samping/mendatar) Rumus : =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) =HLOOKUP(data yang dicari, range table sumber, nomor index baris) • 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.

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

HLOOKUP & VLOOKUP VLOOKUP

Fungsi : mencari data atau mengurutkan nilai data dimana tabel ketentuannya berbentuk vertical (data tersusun ke bawah/menurun) Rumus : =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) =VLOOKUP(data yang dicari, range table sumber, nomor index kolom) • Contoh : • Z: 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)

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)

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 P E R T E M U A N 5 F O R M U L A & F U N G S I

Recommend Documents