RINGKASAN TUTORIAL MICROSOFT EXCEL MEMULAI PROGRAM: Klik : START PROGRAMS MICROSOFT EXCEL TAMPILAN MICROSOFT EXCEL: ADDRESS BAR FORMULA BAR CELL POINTER CELL TAB SHEET
Keterangan: Bagian-bagian yang diterangkan hanya bagian yang khas dalam microsoft excel atau program aplikasi spreadsheet lainnya: 1. Address Bar : tempat yang menunjukkan cell yang sedang aktif. Cell yang aktif ditunjukkan oleh adanya cell pointer. Alamat cell dilambangkan dengan huruf kolom dan nomor baris pembentuknya. Contoh A1 = cell pada kolom A baris nomor 1. 2. Formula Bar
: Tempat yang menampilkan data yang ada di dalam cell aktif (ditunjukk oleh cell
pointer) atau tempat memasukkan data ke dalam cell. 3. Cell Pointer
: Bingkai bergaris tebal yang menunjukkan cell yang sedang aktif.
4. Cell
: Satuan terkecil tempat diletakkannya sebuah data dalam aplikasi spreadsheet,
termasuk microsoft excel. Bila beberapa cell dipilih sekaligus / diblok, maka akan terbentuk suatu range. Range dilambangkan dengan alamat cell paling kiri atas dan alamat cell paling kanan bawah, dipisah dengan tanda colon ( : ). Contoh A1:C11. 5. Tab Sheet
: Bagian yang menunjukkan worksheet yang sedang digunakan. Pada gambar di
atas, worksheet yang digunakan adalah sheet 2. KONSEP UMUM: Microsoft excel merupakan aplikasi lembaran tersebar (spreadsheet) yang berfungsi untuk mengolah data, terutama berupa angka. Selain angka, dapat pula ditampilkan grafik. Sebuah file microsoft excel Ringkasan ms excel
page: 1
disebut pula satu buah workbook (buku kerja). Sebuah workbook terdiri dari sejumlah worksheet (lembar kerja). Banyaknya worksheet pada file baru Ms Excel adalah 3 buah masing-masing bernama sheet1, sheet2 dan sheet3. sebuah wroksheet terdiri dari sejumlah cell (lihat ket. Gambar tampilan excel). Cell-cell terbentuk dari 256 kolom dan 65536 baris, sehingga dalam sebuah worksheet terdapat 16.777.216 buah cell, sama pula artinya satu buah worksheet dapat menampung 16.777.216 data. OPERASI CELL: 1. Mengubah ukuran lebar kolom Cara 1. Cara 2
: :
Klik menu Format Column widht. Masukkan angka ukuran cell lalu OK/Enter. Drag batas kolom pada nama kolom.
Agar ukuran cell menyesuaikan dengan data yang paling panjang, digunakan cara sebagai berikut: klik menu format colomn autofit selections. 2. Mengubah ukuran tinggi baris Cara 1. Cara 2
: :
Klik menu Format Row height. Masukkan angka ukuran cell lalu OK/Enter. Drag batas baris pada nomr baris.
3. Menggabung cell. Pilih terlebih dahulu cell-cell yang akan digabungkan / dijadikan 1 cell. Lalu Cara 1.
:
Cara 2
:
Klik menu Format cells. Pilih tab menu alignment. Beri tanda cawang ( ) pada kotak merge cell. klik icon merge and center pada toolbar.
4. Meratakan teks dalam cell Blik teks yang aka di atur, lalu Cara 1. Cara 2
: :
Klik icon perataan teks yang dikehendaki pada toolbar. klik menu format cells. Pilih tab menu alignment. Pilih jenis perataan teks baik secara vertical maupun horizontal.
5. Mengatur Font dalam cell Blok teks yang akan diatur, lalu klik icon font maumpun font size yang dikehendaki pada toolbar. 6. Memberi Bingkai Blok dulu cell-cell yang akan diberi bingkai. Lalu Cara 1.
:
Cara 2
:
Klik menu Format Cells, pilih tab menu border. Atur dan tentukan sendiri bingkai yang dikehendaki. Klik jenis bingkai pada icon border pada toolbar.
7. Pihdah tab sheet Klik nama sheet yang dikehendaki.
Ringkasan ms excel
page: 2
LATIHAN: BUATLAH TABEL SEPERTI BERIKUT: Tabel pertama, kerjakan di sheet1 NOMOR
NAMA
S
I
A
Tabel kedua dikerjakan di sheet2 NOMOR
NAMA
KETERANGAN S I A
TIPE DAN FORMAT DATA Dalam Microsoft Excel dikenal beberapa macam tipe data. Data tersebut dapat diformat sesuai kebutuhan. Pada ringkasan ini akan dibahas tentang: 1. Tipe dan format data Teks. 2. Tipe dan format data numerik (angka) 3. Tipe dan format data mata uang. 4. Tipe dan format data tanggal dan jam 5. Tipe dan format data customize. Format data bertipe teks. Pada data bertipe teks, format yang dapat diberikan antara lain adalah: 1. Mengganti jenis, ukuran dan warna teks. Cara mengganti jenis, ukuran dan warna teks: a. blok cell / range yang berisi teks yang akan diformat. b. Pilih icon yang sesuai (font, size atau font color) pada toolbar 2. Mengatur perataan teks di dalam cell. a. blok cell / range yang berisi teks yang akan diformat b. Pilih icon alignment yang sesuai pada toolbar. 3. Mengatur arah penampilan teks di dalam cell. Teks di dalam cell dapat tercetak dengan sudut kemiringan tertentu dan lebih dari 0 derajat. Cara untuk menampilkannya adalah sebagai berikut: a. blok cell / range yang akan diformat. b. Klik menu: Format -> cell -> alignment -> angle :
-> ok.
Ringkasan ms excel
page: 3
Format data bertipe numerik Data bertipe numerik, jika bilangan yang dimasukkan adalah bilangan bulat, cukup dituliskan berupa angka seadanya, tanpa tanda pemisah ribuan ataupun tanda desimal. Banyaknya angka 0 dibelakang koma dapat (digit decimal) dapat diatur kemudian. Jika bilangan yang dituliskan memang bilangan pecahan, dapat diatur berapa banyaknya angka desimal di belakang koma. Pada data bertipe numerik, format yang dapat diberikan antara lain adalah: 1. Menambah / mengurangi digit desimal (angka di belakang koma). Misal dikehendaki muncul angka 2000,00 di dalam cell. Maka kita cukup menuliskan 2000 pada cell. Untuk mengatur banyaknya digit angka desimal (angka di belakang koma) adalah sebagai berikut: a. klik / blok data numerik yang akan diformat. b. Klik menu: Format -> cell -> number c.
klik category : number.
d. Pada kolom decimal place, tuliskan bilangan yang menunjukkan banyaknya angka di belakang koma. e. Klik Ok atau [enter] untuk contoh di atas, bilangan decimal place yang diisikan adalah 2. 2. Memberi / menghilangkan tanda pemisa ribuan (1000 sparator). Diumpamakan kita ingin memunculkan angka 1.000.000 pada cell. Kita cukup menuliskan 1000000. untuk memberikan tanda titik sebagai pemisah ribuan, caranya adalah: a. klik / blok data numerik yang akan diformat. b. Klik menu: Format -> cell -> number c.
klik category : number.
d. Pada kotak use 1000 [ ] sparator, berikan tanda cawang () untuk menggunakan dan hilangkan tanda cawang untuk membuang simbol pemisah ribuan. Pada data di atas, tanda cawang diberikan pada kolom use 1000 [ ] sparator. 3. Memilih jenis tampilan angka negatif. Angka negatif dapat ditampilkan dalam beragam rupa. Untuk memilihnya, gunakan cara: a. klik / blok data numerik yang akan diformat. b. Klik menu: Format -> cell -> number c.
klik category : number.
d. Lalu pilih rupa penampilan bilangan negatif pada pilihan negative numbers. Format data betipe mata uang. Untuk menuliskan nilai mata uang $ 1.000,00, kita tidak pelu menuliskan tanda dollar sebagai simbol mata uang, ataupun ,00 sebagai bilangan pecahannya. Cukup kita tulis 1000. untuk memunculkan simbol mata uang dan digit decimal caranya adalah: Ringkasan ms excel
page: 4
a. klik / blok data numerik yang akan diformat. b. Klik menu: Format -> cell -> number c.
pada pilihan category, dapat dipilih: 1) Currency, atau 2) Accounting
d. Simbol mata uang dipilih pada kolom symbol. e. Banyaknya angka di belakang tanda desimal diatur dalam kolom decimal place. Format data bertipe tanggal dan waktu Kita ingin menuliskan tanggal 10 Nop 2009 di dalam cell. Meskipun bisa saja dengan cara menuliskan 10 Nop 2009 langsung ke dalam cell, tapi komputer akan menggantinya menjadi 10/11/09. hal tersebut diapat dibuktikan. Sorotlah / aktifkan cell yang berisi tanggal tersebut, lalu lihatlah apa yang tertulis pada formula bar. Oleh karena itu, biasakan menuliskan tanggal dengan format dd/mm/yy. dd=day / hari, mm= month/bulan dan yy = year / tahun. Untuk menyulapnya menjadi format tampilan tanggal sebagaimana umumnya, gunakan cara berikut: a. klik / blok data numerik yang akan diformat. b. Klik menu: Format -> cell -> number c.
klik category: date.
d. Pilih format tampilan tanggal yang dikehendaki. e. OK. Demikian pula untuk menuliskan jam. Format penulisannya adalah hh:mm. Hh adalah jam dan mm adalah menit. Penulisan format jam seperti itu tidak lagi membutuhkan format cell. Format data bertipe customize. Format customize artinya data di dalam cell dapat ditampilkan sesuai selera kita. Beberapa customisasi data antara lain: 1. Menambahkan satuan di belakang data bertipe numerik Misal, data numerik yang kita tuliskan adalah 10, lalu kita ingin menyulapnya sehingga tampil menjadi 10 kg. Jika teks “kg” kita tuliskan langsung, maka data tersebut bukan lagi data numerik, melalinkan teks, sehingga tidak dapat digunakan pada operasi matematika. Cara yang kita tempuh adalah: a. klik / blok data numerik yang akan diformat. b. Klik menu: format -> cell -> number c.
Klik category: customize
d. pada kolom type, tuliskan 0 “Kg” e. OK atau [enter] bila tampilan yang kita inginkan adalah 10,00 Kg, maka pada kolom type kita tuliskan 0,00 “Kg”. Ringkasan ms excel
page: 5
2. Membuat leading zero. Bila di dalam tabel kita ada kolom nomor urut, lalu kita ingin nomor di dalamnya memiliki leading zero sehingga tampil menjadi 001, 002, 003, ... , 099, kita cukup menuliskan angkanya 1, 2, 3, ..., 99. Leading zero bisa diperoleh dengan menempuh langkah sebagai berikut: a. klik / blok data numerik yang akan diformat. b. Klik menu: format -> cell -> number c.
Klik category: customize
d. pada kolom type, tuliskan 000 banyaknya angka 0 disesuaikan banyaknya digit angka yang akan dimunculkan. e. OK atau [enter] 3. Menampilkan format tanggal secara lebih lengkap. Category date menawarkan sedikit format tampilan tanggal. Tampilan tanggal sesungguhnya ditentukan dari kode huruf d, m dan y. Kombinasi huruf-huruf tersebut padat tipe customize dapat membuat kita lebih leluasa menampilkan tanggal. Misalkan tanggal yang akan diformat adalah 1/2/10. untuk mengkustomisasi, lakukan langkah-langkah berikut: a. klik / blok data numerik yang akan diformat. b. Klik menu: format -> cell -> number c.
Klik category: customize
d. pada kolom type dapat kita tulis kode huruf d, m dan y. Sebagai berikut. Kode d, m, dan y dmy
Tanggal yang muncul 1 2 10
keterangan Angka hari dan bulan muncul 1 digit, angka tahun
tanpa bilangan abad. Terpisah oleh
spasi. dd-m-y
01-2-19
Angka hari muncul 2 digit. Angka bulan 1 digit. Terpisah oleh tanda -
ddd/mm/yyy
Sen/02/2010
Angka hari berubah menjadi singkatan nama hari, angka bulan muncul 2 digit, angka tahun dengan bilangan abad. Terpisah oleh /
dddd, d mmm yyy
Senin, 1 Feb 2010
Dddd menjadi nama hari, mmm menjadi singkatan nama bulan.
dddd, dd mmmm yyy Senin, 1 Februari 2010
Muncul nama hari lengkap, angka hari 2 digit, nama bulan lengkap dan angka tahun 4 digit.
Ringkasan ms excel
page: 6
SOAL LATIHAN Buatlah tabel dengan format data seperti di bawah ini. Lebar kolom disesuaikan dengan panjang data: No
Nama
001 Gareng
Tanggal lahir
Iuran
nilai
berat badan
10 Nopember 92 Rp
10.000,00
60,30
58,0 Kg
002 Petruk
08 Januari 91 Rp
12.500,00
75,19
62,2 Kg
003 Semar
11 Maret 60 Rp
8.500,00
65,20
70,5 Kg
004 Bagong
30 Juni 93 Rp
25.000,00
71,10
60,0 Kg
005 Mbilung
05 Mei 91 Rp
7.500,00
69,90
52,0 Kg
Jawablah pertanyaan – pertanyaan di bawah ini berdasarkan tabel di atas! 1. Kolom apakah yang menggunakan leading zero? 2. Berapa banyak angka 0 dituliskan agar mendapatkan leading zero seperti pada kolom tersebut? 3. Untuk mendapatkan format tanggal seperti tabel di atas, a. Berapa banyak kode huruf d dituliskan? b. Berapa banyak kode huruf m dituliskan? c.
Berapa banyak kode huruf y dituliskan?
4. Jika tanggal di atas dibuat menggunakan category customize, seperti apakah rangkaian kode huruf d, m dan y dituliskan? 5. Categori apakah yang digunakan pada kolom iuran? 6. Angka berapakah yang diisikan pada kolom decimal place untuk mendapatkan format mata uang seperti pada kolom iuran? 7. Apakah kotak use 1000 sparator di cawang untuk mendapatkan format mata uang seperti pada kolom iuran? 8. Angka berapakah yang diisikan pada kolom decimal place untuk mendapatkan format angka seperti pada kolom nilai? 9. Apa yang dituliskan pada kolom type untuk mendapatkan format tampilan angka seperti pada kolom berat badan?
Ringkasan ms excel
page: 7
RUMUS MATEMATIKA Microsoft Excel sering digunakan untuk melakukan perhitungan atas data-data numerik yang ditampung di dalamnya. Artinya, rumus matematika juga sering digunakan dalam microsoft excel atau spradsheet lainnya. Bagaimana kaidah-kaidah penulisan rumus? Aturan penulisan rumus: 1. Diawali dengan tanda sama dengan ( = ). 2. Tanda-tanda operator matematika: Simbol umum
Simbol di spreadsheet
Tidakaada
^
=2^3
Perkalian
x
*
=2*3
Pembagian
:
/
=8/2
Penambahan
+
+
=2+3
Pengurangan
-
-
=8-2
Nama operator Pemangkatan
Contoh
3. Urutan pengerjaan. Bila dalam suatu rumus terdapat semua tanda operator matematika, maka penghitungan matematika dikerjakan dengan urutan operasi: a. Semua operasi di dalam tanda kurung. b. Pemangkatan c.
Perkalian dan pembagian
d. Penambahan dan pengurangan Contoh: =6 + 5 * (4 – 3) / 2 ^ 1 akan sama dengan = 6 + 5 * 1 / 2^1 =6+5*1/2 = 6 + 2,5 = 8,5 4. Tanda-tanda operator pembanding (logika). Tanda ini sering digunakan di dalam fungsi. Hasil dari operasi logika adalah nilai True (benar) atau False (salah) Simbol umum
Simbol di spreadsheet
Sama dengan
=
'=
=5=(4+1)
Benar
Tidak sama dengan
<>
=5=(4+1)
Salah
Lebih besar
>
>
=5=(4+1)
Salah
Lebih besar atau sama dengan
>
>=
=5=(4+1)
Benar
Lebih kecil
<
<
=5=(4+1)
Salah
Lebih kecil atau sama dengan
<
<=
=5=(4+1)
Benar
Nama operator
Ringkasan ms excel
Contoh
hasil
page: 8
5. Cara / methode menuliskan rumus a. Metode langsung Pada metode ini, angka yang akan dihitung dituliskan secara langsung ke dalam rumus. Contoh: lebar 2 3 4
panjang 3 4 5
luas 6 <= ditulis: =2*3 12 <= ditulis: =3*4 20 <= ditulis: =4*5
b. Metode kepala tabel (header) pada metode ini, angka diganti dengan nama header / kepala tabel di atasnya. Contoh: lebar 2 3 4 c.
panjang 3 4 5
luas 6 <= ditulis: = lebar * panjang 12 <= ditulis: = lebar * panjang 20 <= ditulis: = lebar * panjang
Metode referensi (rujukan) cell pada metode ini, angka yang akan dihitung diganti dengan alamat cell tempat angka tersebut berada. Contoh: lebar 2 3 4
panjang 3 4 5
luas 6 <= ditulis: =$A$2*$B$2 12 <= ditulis: =$A$3*$B$3 20 <= ditulis: =$A$4*$B$4
keterangan:
tanda dolar didepan nama kolom dan nomor baris menunjukkan bahwa cell tersebut adalah cell absolute. Artinya, alamat cell tersebut tidak akan berganti/bergeser ketika rumus / fungsi yang mengandung cell absolut disalin ke cell lain.
Membuat cell absolut dapat dilakukan dengan menekan tombol F4 setelah menyorot cell / memblok range.
Alamat cell didapatkan dengan cara mengklik / menyorot cell dengan cell pointer setelah menuliskan tanda sama dengan ataupun tanda operator matematika.
SOAL LATIHAN. Buatlah tabel seperti di bawah ini. Isilah kolom yang masih kosong dengan rumus yang sesuai. Boleh menggunakan metode langsung, header tabel atau referensi cell. Panjang
lebar
tinggi
3
4
5
4
5
6
5
6
7
4
6
8
5 7 Keterangan: rumus volume rumus panjang seluruh rusuk rumus luas seluruh bidang Ringkasan ms excel
volume
panjang seluruh rusuk
Luas seluruh bidang
9 = panjang x lebar x tinggi = 4 x (panjang + lebar + tinggi) = 2 x((panjang x lebar)+(lebar x tinggi)+(panjang x tinggi)) page: 9
FUNGSI Fungsi bisa diartikan sebagai perintah yang mengandung rumusan tertentu. Sebagai contoh, untuk menghitung rata-rata dari sekumpulan nilai, seseorang harus menghitung jumlah nilai kemudian membaginya dengan banyaknya data. Rumusan seperti itu dapat dipermudah dengan sebuah fungsi bernama average. Ringkasan ini akan membahas tentang fungsi numerik sederhana, fungsi teks (string) sederhana, fungsi bersyarat dan fungsi pembacaan tabel. Aturan penulisan fungsi mirip dengan penulisan rumus, yaitu diawali dengan tanda sama dengan, boleh menggunakan metode langsung ataupun referensi cell. A. Fungsi Numerik Sederhana. Fungsi-fungsi numerik sederhana akan kita gunakan untuk menyelesaikan tabel berikut: A NAMA
1 2 3 4 5 6
EKO WAHYUDI GANJAR Be rat ke se luru ha n Pa ling Bon gsor
7 8 9
Pa ling Kun te t Be rat rata -rata Ba nya knya Na ma
B TINGGI 165 Cm 1 63 Cm 158 Cm
C BERAT 7 0,00 Kg 65,00 Kg 7 4,00 Kg
1. Sum Fungsi ini digunakan untuk mendapatkan jumlah nilai dari sekumpulan data. a. Sintak dasar =SUM(nilai-nilai) atau =SUM(range) b. Contoh: Untuk mengisi berat keseluruhan pada cell D2 pada, dapat digunakan fungsi sum sebagai berkut: 1) =sum(70;65;74), atau 2) =sum(C2;C32;C4), atau 3) =sum(C2:C4) semua menghasilkan nilai 209. 2. Max Fungsi ini digunakan untuk mendapatkan nilai terbesar dari sekumpulan data. a. Sintak dasar: =MAX(nilai-nilai) atau =MAX(range) b. Contoh Untuk mengisi paling bongsor pada cell B6, dapat digunakan fungsi MAX sebagai berikut: 1) =max(165;163;158), atau 2) =max(B2;B3;B4), atau 3) =max(B2:B4) semua menghasilkan nilai 165. Ringkasan ms excel
page: 10
3. Min Fungsi ini digunakan untuk mendapatkan nilai terkecil dari sekumpulan data. a. Sintak dasar =MIN (nilai-nilai) atau =MIN(range) b. Contoh: untuk mengisi paling kuntet pada cell B7 dapat digunakan fungsi min sebagai berikut: 1) =min(165;163;158), atau 2) =min(B2;B3;B4), atau 3) =min(B2:B4) semua menghasilkan nilai 158. 4. Average Fungsi ini digunakan untuk mendapatkan nilai rata-rata dari sekumpulan data. a. Sintak dasar =AVERAGE(nilai-nilai) atau =AVERAGE(range) b. Contoh untuk mengisi berat rata-rata pada cell B8 dapat digunakan fungsi average sebagai berikut: 1) =average(70;65;74), atau 2) =average(C2;C32;C4), atau 3) =average(C2:C4) semua menghasilkan nilai 69,57. 5. Count Fungsi ini digunakan untuk mendapatkan banyaknya data dari sekumpulan data. Data yang dapat dihitung oleh fungsi ini hanya data numerik. a. Sintak dasar =COUNT (nilai-nilai) atau =COUNT(range) b. Contoh untuk mengisi banyaknya nama dapat digunakan fungsi count sebagai berikut: 1) =count(165;163;158), atau 2) =count(70;65;74), atau 3) =count(B2;B3;B4), atau 4) =count(C2;C3;C4), atau 5) =count(B2:B4), atau 6) =count(C2:C4) perhatikan bahwa tipe data dalam kolom B dan kolom C adalah numerik. Jadi, funsi count dapat diterapkan pada kolom B atau kolom C. Fungsi count tidak dapat diterapkan pada kolom A karena tipe data pada kolom A adalah teks.
Ringkasan ms excel
page: 11
B. Fungsi Teks/String Sederhana Fungsi teks sederhana merupakan fungsi manipulasi teks. 1. Len Digunakan untuk menghitung banyaknya karakter (huruf, angka, dan tanda baca) di suatu cell. a. Sintak dasar: =LEN(“teks”) atau LEN(cell) b. Contoh 1
A BANYU BIRU NO 1 1
B
untuk menghitung banyaknya karakter pada CELL A1, fungsi LEN yang digunakan adalah 1) =LEN(A1), atau 2) =LEN(“BANYU BIRU NO 11”) Hasilnya adalah 16 2. Left Digunakan untuk mendapatkan sejumlah karakter dari sisi paling kiri suat teks. a. Sintak dasar: =LEFT(“teks”; banyaknya) atau = LEFT(cell; banyaknya) b. Contoh: 1
A BANYU BIRU NO 1 1
B
untuk mendapatkan 10 karakter paling kiri dari teks dalam cell A1, fungsi left yang digunakan adalah sebagai berikut: 1) =left(“BANYU BIRU NO 11”; 10) atau 2) =left(A1;10) semua menghasilkan teks BANYU BIRU. 3. Right Fungsi ini digunakan untuk mendapatkan sejumlah karakter dari sisi paling kanan suatu teks. a. Sintak dasar =RIGHT(“teks”; banyaknya) atau = RIGHT(cell; banyaknya) b. contoh 1
A BANYU BIRU NO 1 1
B
untuk mendapatkan 10 karakter paling kanan dari teks dalam cel A1 dapat digunakan fungsi right sebagai berikut: 1) =right(“BANYU BIRU NO 11”; 10) atau 2) =right(A1;10) semua menghasilkan teks BIRU NO 11. 4. Mid Fungsi ini untuk mendapatkan sejumlah karakter dimulai dari urutan karakter tertentu dari suatu teks. Ringkasan ms excel
page: 12
a. Sintak dasar: =MID(“teks”;mulai;banyaknya) atau =MID(cell;mulai;banyaknya) b. contoh A BANYU BIRU NO 1 1
1
B
untuk mendapatkan 6 karakter mulai dari karakter ke 3 teks dalam cel A1 dapat digunakan fungsi mid sebagai berikut: 1) =mid(“BANYU BIRU NO 11”; 2;4) atau 2) =mid(A1;2;4) c.
semua menghasilkan teks NYU BI.
C. Fungsi Bersyarat Fungsi bersyarat pada dasarnya adalah fungsi yang membutuhkan kriteria / persyaratan tertentu untuk menghasilkan output nilai. Ringkasan ini akan membahas fungsi IF, COUNTIF, DAN SUMIF. 1. Fungsi IF Digunakan untuk memilih satu nilai di antara dua pilihan berdasarkan kondisi / persyaratan tertentu. a. Sintak umum =IF(syarat; nilai output jika syarat terpenuhi; nilai output jika syarat tidak terpenuhi) keterangan: 1) syarat=operasi perbandingan/logika yang akan menghasilkan nilai benar atau salah. Bentuk operasi logika ini biasanya terdiri dari nilai/cell yang akan dibandingkan, tanda operator logika, dan nilai yang dijadikan pembanding. Contohnya:
10=5+2,
L10<15,
K12<>”L”,
dan lain sebagainya.
Perhatikan bahwa jika nilai pembandingnya berupa data teks, maka harus diapit tanda
kutip (double quote). 2) Nilai output jika syarat terpenuhi=nilai/data yang akan ditampilkan jika operasi logika menghasilkan nilai benar/true. Jika nilai / data bertipe teks, harus diapit tanda kutip. 3) Nilai output jika syarat tidak terpenuhi=nilai/data yang akan ditampilkan jika operasi logika menghasilkan nilai salah/false. Jika nilai / data bertipe teks, harus diapit tanda kutip.
Ringkasan ms excel
page: 13
b. Contoh: 1 2 3 4 5 6
A B NILAI BONUS 70 75 77 79 68
C KETERANGAN
Pada tabel di atas, untuk mengisi kolom bonus berlaku aturan: “jika nilai > 75, bonus=50000. Jika tidak, bonus=10000”. Sedangkan untuk mengisi kolom keterangan, berlaku aturan: “jika nilai > 70, keterangan= lulus. Jika tidak, keterangan=mengulang” Aturan tersebut diterjemahkan ke dalam fungsi if. Fungsi IF cukup dituliskan pada cel B2 dan C2. Untuk baris dibawahnya, cukup menyalinnya dengan cara mendrag fill handle. Fungsi IF untuk cell B2 ( kolom bonus) adalah sebagai berikut: =IF(A2>75;500000;10000) komputer akan memeriksa apakah nilai di cell A2 lebih besar dari 75. Karena nilai di cell A2 adalah 70 (tidak lebih dari 75), maka pernyataan A2>25 menghasilkan nilai False/Salah, sehingga data yang ditampilkan adalah 10000. Sedagnkan fungsi IF untuk cell C2 (kolom keterangan) adalah sebagai berikut: =IF(A2>70;”lulus”;”mengulang”) komputer akan memeriksa apakah nilai di cell A2 lebih besar dari 70. Karena nilai di cell A2 tidak lebih dari 70, maka pernyataan A2>70 menghasilkan nilai False/Salah, sehingga data yang ditampilkan adalah mengulang. 2. Fungsi COUNTIF Digunakan untuk menghitung banyaknya data yang memenuhi kriteria tertentu saja. Misalkan hitung banyaknya data di range A1:A20 yang isinya=10. a. Sintak umum =COUNTIF(range;”kriteria”) keterangan: 1) range=kumpulan cell yang akan dihitung banyaknya data. 2) kriteria=teks yang menunjukkan persyaratan penghitungan banyaknya data. b. Contoh: 1 2 3 4 5 6
A B C NILAI BONUS KETERANGAN 70 10.000,00 mengulang 75 10.000,00 lulus 77 50.000,00 lulus 79 50.000,00 lulus 68 10.000,00 mengulang
Ringkasan ms excel
page: 14
1) Untuk menghitung berapa banyaknya penerima bonus 50000, fungsi countif yang digunakan adalah: =countif(B2:B6;”=50000”) hasilnya adalah 2. 2) Untuk menghitung berapa banyaknya penerima bonus yang lebih kecil dari 50000 dapat digunakan fungsi countif sebagai berikut: =countif(B2:B6;”<50000”), hasilnya adalah 3 3) Untuk menghitung banyaknya kata mengulang, dapat digunakan fungsi countif: =countif(C2:C6;”mengulang”), hasilnya adalah 2. Perhatikan bahwa pada bagian kriteria yang beruap teks tidak dibutuhkan tanda operator logika. 3. Fungsi SUMIF Digunakan untuk menjumlahkan data yang memenuhi kriteria tertentu saja. Misalnya jumlahkan pemesanan kaos berukuran s, dan lainnya. a. Sintak umum: =SUMIF(range kriteria; kriteria; range penjumlahan) keterangan: 1) range kriteria=kumpulan cell yang memuat data-data yang memenuhi kriteria. 2) kriteria=teks yang menjadi kriteria, umumnya terdapat di dalam range kriteria. 3) Range penjumlahan=kumpulan cell yang memuat data-data bertipe numerik yang akan dijumlahkan. b. Contoh: perhatikan tabel berikut: 1 2 3 4 5 6 7 8 9 10 11 12
A PEMESAN SMK PB SMK PB SMK MUH SMK MUH SMK MUH
B UKURAN L XL LL XL L
C JUMLAH 50 51 24 35 60
RINGKASAN PESANAN UKURAN JUMLAH L LL XL
Jumlah pesanan kaos berukuran L dapat dihitung dengan fungsi sumif: =SUMIF(B2:B6;”L”;C2:C6) Namun jika ingin fungsi tersebut bisa disalin untuk ukuran LL dan XL di bawahnya, maka semua range harus dibuat absolute, dan kriteria diganti dengan alamat cell yang memuat ukuran kaos, sehngga menjadi: =SUMIF($B$2:$B$6;B10:$C$2:$C$6)
Ringkasan ms excel
page: 15
D. Fungsi Pembacaan Tabel Perhatikan dua tabel di bawah ini: 1 2 3 4 5 6 7 8
A B TABEL ANGKA ANGKA JAWA 1 SIJI 2 LORO 3 TELU 4 PAPAT 5 LIMA
C
D
INGGRIS ONE TWO THREE FOUR FIVE
E KAMUS ANGKA ANGKA JAWA INGGRIS
F 3
Apa yang kita lakukan untuk mengisi kolom angka 3 dalam bahasa jawa atau bahasa inggris? Tentunya kita akan merujuk ke tabel angka. Ada dua kemungkinan cara membaca tabel angka di atas. Kemungkinan pertama kita mengurut kepala tabel terlebih dahulu, kemudian mengurutkannya ke bawah. Kemunginan kedua kita mengurut kolom angka / kolom paling kiri, kemudian mengurutkannya ke kanan. Gambaran membaca tabel semacam itu sama dengan fungsi lookup pada aplikasi spreadsheet. Fungsi lookup adalah fungsi membaca tabel berdasarkan data referensi untuk mengambil data tertentu dari tabel tersebut. Salah satu cara mempermudah pekerjaan fungsi lookup, adalah memberi “nama” sebuah range / tabel. Kerjakan perintah berikut untuk mempelajari cara memberi nama suatu range/tabel: 1. Tuliskan kedua tabel di atas pada aplikasi excel. 2. Blok range A2 hingga C7. 3. Klik: menu Insert -> name -> define. 4. Isilah kolom Name, misalnya dengan nama angka. 5. Klik: tombol Add 6. Klik: OK Ada dua macam fungsi lookup: 1. VLOOKUP Pada fungsi VLOOKUP, data rujukan adalah data yang terdapat di dalam kolom paling kiri pada tabel yang dibaca. a. Sintak umum: =VLOOKUP(data referensi, range/nama tabel yang akan dibaca; nomor kolom tempat data yang akan diambil;pilihan pengurutan) keterangan: 1) data referensi=nilai/data yang akan dicari keberadaanya di dalam kolom paling kiri tabel yang akan dibaca. 2) range/nama tabel yang akan dibaca=range/nama tabel tempat data-data yang akan diambil berada. 3) Nomor kolom tempat data yang akan diambil=nomor urutan kolom yang berisi data yang akan diambil. Kolom paling kiri adalah urutan nomor 1. Ringkasan ms excel
page: 16
4) pilihan urutan= berisi 0 atau 1. boleh juga tidak diisikan. Jika diisi 1 atau tidak digunakan, maka data pada kolom pertama tabel yang dibaca harus dirutukan ascending. Jika diisi 0, data tidak perlu diurutkan. b. Contoh: untuk mengisikan nama angka 3 dalam bahasa jawa fungsi vlookupnya adalah: =VLOOKUP(F2;A2:C7;2) atau VLOOKUP(F2;angka;2) komputer akan mengidentifkasi isi/nilai pada cell F2. Setelah itu komputer membaca range yang ditentukan atau sesuai nama range yang diberikan, dimulai dari kolom paling kiri, yaitu kolom A untuk mencari keberadaan data yang sama dengan isi cell F2. Setelah ditemukan, komputer akan menggeser pencarian ke kolom kedua dari tabel rujukan, kolom tempat kata jawa berada. 2. HLOOKUP Pada fungsi HLOOKUP, data rujukan adalah data yang terdapat di dalam baris paling atas pada tabel yang dibaca. a. Sintak umum: =VLOOKUP(data referensi, range/nama tabel yang akan dibaca; nomor baris tempat data yang akan diambil;pilihan pengurutan) keterangan: 1) data referensi=nilai/data yang akan dicari keberadaanya di dalam kolom paling atas tabel yang akan dibaca. 2) range/nama tabel yang akan dibaca=range/nama tabel tempat data-data yang akan diambil berada. 3) Nomor baris tempat data yang akan diambil=nomor urutan baris yang berisi data yang akan diambil. Baris teratas adalah urutan nomor 1. 4) pilihan urutan= berisi 0 atau 1. boleh juga tidak diisikan. Jika diisi 1 atau tidak digunakan, maka data pada kolom pertama tabel yang dibaca harus dirutukan ascending. Jika diisi 0, data tidak perlu diurutkan. b. Contoh: untuk mengisikan nama angka 3 dalam bahasa jawa fungsi vlookupnya adalah: =HLOOKUP(E3;A2:C7;4;0) atau HLOOKUP(E3;angka;4;0) komputer akan mengidentifkasi isi/nilai pada cell E3. Setelah itu komputer membaca range yang ditentukan atau sesuai nama range yang diberikan, dimulai dari kolom paling atas, yaitu baris 2 untuk mencari keberadaan data yang sama dengan isi cell E3. Setelah ditemukan, komputer akan menggeser pencarian ke baris keempat dari tabel rujukan, baris yang sejajar dengan angka 3.
Ringkasan ms excel
page: 17
SOAL LATIHAN isilah cell yang kosong dengan fungsi numerik sederhana yang sesuai. A 1 2 3 4 5 6 7 8 9 10
NAMA SEMAR PETRUK GARENK BAGONQ
B
C
MTMTK BHS. INDO 70 71 71 72 69 74 68 75
D
E
F
IPA
JUMLAH
RATA-RATA
68 67 73 71
TERTINGGI TERENDAH BANYAKNYA NILAI
Perhatikan tabel berikut ini untuk mempraktikan fungsi bersyarat dan pembacaan tabel. A
1 2 3 4 5 6 7 8 9 10 11 12
B data siswa nomor induk nama 1001 Bawor 1002 Mbilung 1003 Togog 1004 Durna 1005 Slamet
C
D E F tabel nilai nomor induk Nama siswa Berat Badan 1005 65 1004 70 1003 72 1002 67 1001 76
G
H
Menu
Jumlah
Banyaknya siswa yang menerima bubur Banyaknya siswa yang menerima roti Jumlah prosi bubur yang diberikan Jumlah porsi roti yang diberikan
1. berilah nama “siswa” untuk tabel data siswa dari cell a2 hingga b7. 2. Kolom nama siswa diisi menggunakan fungsi pembacaan tabel berdasarkan nomor induk. 3. Kolom menu diisi dengan ketentuan:
jika berat badan lebih dari 70, menu adalah bubur. Jika berat badan tidak lebih dari 70, menu adalah roti. 4. Kolom jumlah diisi dengan ketentuan:
jika berat padan lebih dari 65, jumlah=3. Jika berat badan tidak lebih dari 65, jumlah = 2. 5. Banyaknya siswa yang menerima bubur ataupun roti diisi menggunakan fungsi countif. 6. Jumlah porsi bubur ataupun roti yang diberikan diisi menggunakan fungsi sumif.
Ringkasan ms excel
page: 18
