Basic
Formulas and
Functions
Sesi 2 - Data Analyst with Excel
Entering and editing formula, basic
arithmetic and logic functions
Formula & Fungsi, Apa bedanya?
● Formula adalah ekspresi yang menghitung ● Fungsi adalah rumus yang sudah
nilai dalam cell atau dalam rentang cell. ditentukan sebelumnya yang sudah
● Misalnya, =A1+A2+A3 adalah rumus yang tersedia di Excel. Fungsi melakukan
menjumlahkan nilai dalam sel A1 hingga A3. perhitungan tertentu dalam urutan tertentu
berdasarkan nilai yang ditentukan, yang
disebut argumen, atau parameter.
● Misalnya, untuk menjumlahkan nilai dari A1
sampai A3 kita dapat menggunakan fungsi
=SUM(A2:A4)
● Seluruh fungsi dalam Excel dapat
ditemukan pada tab Rumus
Tidak perlu hafal semua fungsi!
Ada 400+ fungsi di Excel, dan jumlahnya terus bertambah dari versi ke versi. Tentu saja,
hampir tidak mungkin untuk menghafal semuanya, dan kita sebenarnya tidak perlu
melakukannya. Ada fitur dalam Excel yang bernama Function Wizard yang akan
membantu kita menemukan fungsi yang paling sesuai untuk tugas tertentu. Kemudian
ada fitur yang bernama Excel Formula Intellisense yang akan merekomendasikan
sebuah argumen fungsi segera setelah kita mengetikkan nama fungsi yang diawali
dengan tanda sama dengan di sel:
Fungsi Dasar dalam Excel
Nama Fungsi Kegunaan Contoh
SUM menjumlahkan angka yang ada di sel-sel tertentu =SUM(A2:A6)
AVERAGE menghitung rata-rata angka pada range sel tertentu =AVERAGE(A2:A6)
MAX & MIN mencari angka tertinggi atau terendah di dalam suatu deretan data =MAX(A2:A6)
=MIN(A2:A6)
COUNT & COUNTA menghitung jumlah sel =COUNTA(A1:I1)
=COUNT(A1:I1)
IF mengambil salah satu dari dua atau lebih nilai berdasarkan suatu kondisi =IF(A1>B1;
“Benar”;”Salah”)
TRIM menghapus spasi berlebih yang ada di dalam sel =TRIM(A1)
LEN menghitung jumlah karakter yang ada di dalam satu sel =LEN(A1)
AND mencari tahu apakah isi dari suatu sel benar (TRUE) atau salah (FALSE). Fungsi AND =AND(A1>50;A1<100)
harus memenuhi semua kriteria yang ada di rumus logika
OR mencari tahu apakah kriteria yang ditentukan TRUE atau FALSE. Fungsi OR bisa =OR(A1>50;A1<100)
dipenuhi dengan salah satu kriteria saja.
1. Jumlahkan revenue pada cabang
Medan pada bulan Mei!
2. Berapakah rata-rata jumlah
Quiz! barang yang dibeli di cabang
Medan?
3. Pada cabang Medan bulan
keberapa revenue mendapatkan
Kerjakan Quiz berikut revenue tertinggi ?
ini! 4. Berapa kali terjadi perubahan
harga sepanjang tahun 2017?
Simbol Fungsi
+ Penjumlahan
– Pengurangan
* Perkalian
/ Pembagian
Simbol % Persen
Logika ^ Perpangkatan
= Sama dengan
< Kurang dari
<= Kurang dari atau sama dengan
> Lebih dari
>= Lebih dari sama dengan
<> Tidak sama dengan
Basic Text and Date Function
LEFT, RIGHT, MID
Ketiga fungsi berikut adalah fungsi yang paling sering digunakan untuk memanipulasi text.
LEFT : digunakan untuk mengekstrak karakter dari kiri pada data.
RIGHT : digunakan untuk mengekstrak karakter dari kanan pada data.
MID : digunakan untuk mengekstrak karakter dari tengah data.
Contoh
● Ketika kita menuliskan formula : =LEFT(C6;4) pada
suatu cell pada tabel Kategori Produk, hasil yang
muncul adalah Atas
Maksud dari formula tersebut adalah : “Ambil 4
karakter dari ‘Atasan’ dimulai dari kiri” . Maka dari itu
hasilnya adalah ‘Atas’
● Ketika kita menuliskan formula : =RIGHT(C6;4) pada
suatu cell pada tabel Kategori Produk, hasil yang
muncul adalah ‘ahan’
Maksud dari formula tersebut adalah : “Ambil 4
karakter dari ‘Atasan’ dimulai dari kanan”. Maka dari
itu hasilnya adalah ‘ahan’
● Ketika kita menuliskan formula : =MID(C6;3;3) pada
suatu cell pada tabel Kategori Produk, hasil yang
muncul adalah ‘asa’.
Maksud dari formula tersebut adalah : “Ambil 3
karakter dari ‘Atasan’ dimulai dari karakter ke 3,
dengan jumlah 3 karakter” yang berarti 3 karakter dari
kiri adalah ’a’ dan 3 karakter yang akan diambil berarti
‘asa’
LEN
Fungsi LEN, kata LEN berasal dari LENGTH yang berarti panjang, berfungsi untuk menghitung jumlah
karakter dalam string teks dan menghitung huruf, angka, karakter khusus, karakter yang tidak dapat
dicetak, dan semua spasi dari sel Excel. Dengan kata sederhana, fungsi LENGTH digunakan untuk
menghitung panjang teks dalam sel Excel. Contoh penggunaaan ada di gambar berikut :
Menggabungkan LEN dan TRIM
Kita dapat menggabungkan antara LEN yang berfungsi untuk
menghitung jumlah karakter dan TRIM yang berfungsi untuk menghapus
semua spasi dari teks kecuali satu spasi antar kata.
contoh penggunaan :
Pada Tabel Kategori Produk, kita akan menghitung jumlah karakter
Quiz Time
Pada tabel Transaksi, Sunyi kebingungan karena ketika mencoba fungsi LEN untuk menghitung jumlah karakter pada kode produk yang
seharusnya berjumlah 8 (Kategori berjumlah 3 karakter + tanda hubung (-) + kode angka yang terdiri dari 4 karakter sehingga
jumlahnya harusnya ada 8 karakter. Namun Sunyi menemukan ada kode produk yang ditulis dengan 9 karakter padahal jika dilihat-lihat
kode tersebut tidak ada yang salah semua jumlahnya 8 jika dihitung manual. Kira-kira mengapa hal ini bisa terjadi? Dan bagaimana
cara untuk memperbaiki-nya??
Aggregate functions
Apa itu fungsi logika?
Logika dalam excel pada dasarnya berfungsi untuk menguji apakah suatu pernyataan atau data memenuhi
kriteria. Berdasarkan hasil pengujian kriteria ini, kita dapat melakukan hal lainnya. Fungsi logika dapat
membantu kita untuk memberikan informasi, melakukan perhitungan yang berbeda, atau melakukan
pengujian yang lebih lanjut.
Fungsi IF adalah fungsi dasar dalam logika. Fungsi ini menggunakan format
=IF(condition, true, false)
IF
Fungsi logika AND
OR
Fungsi diatas dapat diartikan sebagai berikut:
Jika total penjualan pada cell A2 lebih dari 75000, maka akan bernilai “High”,
sebaliknya jika A2 kurang dari 75000, maka akan bernilai “Low”.
Karena 100,000 lebih dari 75,000 , maka akan bernilai High.
Fungsi AND digunakan untuk membandingkan dua atau lebih kondisi.
Fungsi logika AND akan bernilai TRUE ketika seluruh kondisi terpenuhi.
Bentuk umum dari fungsi AND yaitu:
IF =AND(Condition1, Condition2, …)
Fungsi logika AND
OR
Tapi, ketika ada satu kondisi yang tidak terpenuhi, maka fungsi AND akan
bernilai FALSE
Penjualan di bulan
Februari kurang
dari 75,000
sehingga fungsi
AND bernilai FALSE
Fungsi OR juga berfungsi untuk membandingkan dua atau lebih kondisi.
Perbedaan dengan fungsi AND yaitu fungsi OR akan bernilai TRUE ketika ada
minimal 1 kondisi yang terpenuhi. Bentuk umum fungsi OR yaitu:
IF =OR(Condition1, Condition2,...)
Fungsi logika AND
Meskipun
penjualan pada
OR bulan Januari
kurang dari 75,000.
Tapi fungsi logika
OR akan bernilai
TRUE karena sudah
ada 1 kondisi yang
terpenuhi
Penggunaan fungsi Logika
Fungsi logika dapat digunakan untuk menampilkan informasi yang berbeda berdasarkan hasil dari kondisi
yang dipergunakan. Selain memberikan informasi baru, kita juga dapat menambahkan perhitungan baru
berdasarkan hasil dari fungsi logika
Selain menambahkan informasi baru, kita juga dapat menambahkan formula
baru kedalam fungsi logika. Misalkan kita ingin menambahkan faktor komisi
menampilkan penjualan. Transaksi yang tergolong high sale akan mendapatkan komisi 5%
informasi baru sementara low sale tidak akan mendapatkan komisi sama sekali.
Kita dapat menggunakan perintah berikut
Fungsi logika =IF(I2="High sale";H2*$M$1;0)
Menambahkan
perhitungan
baru
Ketika transaksi tergolong high
sale, komisi akan bernilai 5% dari
total penjualan dan bernilai 0
ketika transaksinya low sale
Fungsi logika dapat digunakan untuk menambahkan informasi baru. Misalkan
kita memutuskan bahwa transaksi dengan total lebih dari sama dengan
menampilkan 10,000 adalah high sale dan yang kurang dari 10,000 adalah low sale.
informasi baru
Kita dapat menggunakan fungsi logika untuk menampilkan informasi baru
Fungsi logika dengan fungsi berikut ini:
Menambahkan
perhitungan
baru
Ketika total
penjualan >=10,000
maka kita akan
memunculkan
informasi High Sale,
dan sebaliknya.
Fungsi logika NESTED IF
Jika kita ingin menambahkan fungsi logika baru atau memiliki lebih dari satu kondisi, kita dapat menambahkan
fungsi logika IF dalam sebuah fungsi logika IF. Hal ini dinamakan dengan NESTED IF.
Berikut adalah persamaan umum dari NESTED IF:
=IF(Condition 1, TRUE, IF(Condition 2, TRUE, IF(Condition 3, TRUE, FALSE)))
Misalkan kita memiliki data berikut ini dan ingin mengkategorikan penjualan per-bulan menjadi target perusahaan menjadi 4
grup (below target, meet target, above target, dan exceptional). Dengan menggunakan kriteria berikut ini:
Fungsi logika NESTED IF
Kita dapat memanfaatkan fungsi IF, NESTED IF, dan AND untuk mendapatkan status target penjualan. Berikut ini
adalah fungsi yang dapat kita gunakan:
=IF(B2<=120000;"Below target";
IF(AND(B2>120000;B2<=140000);"Meet target";
IF(AND(B2>140000;B2<=150000);"Above target";
"Exceptional")))
Perintah IFERROR
Terkadang kita menemukan pesan error ketika menggunakan fungsi IF.
Perhatikan contoh berikut ini:
Kita dapat
menggunakan
perintah Total
Penjualan / Lama
menjadi Agen
Kita ingin
menghitung
rata-rata penjualan
pertahun dari 4 sales
disamping
Seperti yang dijelaskan di materi Excel
sebelumnya, ketika kita membagikan
sebuah bilangan dengan 0, akan
menghasilkan pesan error !DIV/0
Kita dapat mengatasi pesan error tersebut dengan memberikan keterangan bahwa Mawar merupakan sales yang baru
bekerja sehingga rata-rata penjualan per-tahun masih belum ada.
Kita dapat menggunakan perintah IFERROR untuk mengatasi hal ini.
Perintah IFERROR
Struktur umum dari perintah IFERROR yaitu:
=IFERROR(Value, Value jika error)
Nilai yang muncul
ketika terjadi ERROR
Fungsi rata-rata
penjualan per-tahun
Kita dapat mengatasi pesan ERROR dalam Excel dengan menggunakan fungsi IFERROR.
TRUE FALSE
TRUE dan FALSE adalah nilai kebenaran yang digunakan oleh fungsi
logika dan pengujian menggunakan operator aritmatika pada Microsoft
Excel. Fungsi ini dapat ditulis langsung pada sel di lembar kerja Microsoft
Excel atau menggunakan fungsi TRUE() untuk TRUE dan FALSE() untuk
FALSE.
Rumus Fungsi TRUE adalah keluaran untuk nilai kebenaran benar oleh
fungsi logika dan operator aritmatika.
Rumus Fungsi FALSE adalah keluaran untuk nilai kebenaran salah oleh
fungsi logika dan operator aritmatika.
Evaluate Formula
Fitur Evaluate Formula berguna untuk mengidentifikasi dan memperbaiki kesalahan pada
rumus yang kita tulis. Kita juga dapat menggunakannya untuk memahami rumus kompleks,
melihat bagaimana setiap bagian dari fungsi untuk mencapai hasil akhir.
Cara untuk menggunakan fitur ini adalah :
1. Pilih cell yang ingin diperiksa
2. Pilih Tab Formulas >> Evaluate Formulas atau F9
3. Akan muncul dialog Evaluate Formula , klik Evaluate
Jika bagian rumus yang digarisbawahi adalah referensi ke rumus lain,Excel akan
mengevaluasi rumus satu persatu dalam 1 fungsi (jika ada lebih dari 1 fungsi dalam 1
rumus). Klik Step in untuk menampilkan mengevaluasi rumus lain. Klik Step out untuk
kembali ke rumus sebelumnya
4. Untuk melihat evaluasi lagi, klik Restart dan untuk mengakhiri evaluasi, klik Tutup.
Arti Error
Mengenal pesan error dalam fungsi
Dalam menggunakan fungsi di microsoft Excel, kita sering menemukan pesan error yang mengakibatkan
perhitungan Excel tidak berjalan dengan benar. Pesan error yang sering muncul yaitu:
#NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #NA.
Pada dasarnya pesan error ini memberikan petunjuk penyebab kesalahan dari formula yang kita buat.
Jadi kalau kita memahami arti dari pesan error, kita dapat mengetahui letak kesalahan dan mengatasi
error dalam Excel.
Mengenal pesan error dalam
fungsi
Berikut ini adalah arti dari pesan error yang muncul dalam Excel
No Pesan error Pengertian dan letak error
1 #NULL! Range tidak beririsan, bernilai NULL, atau mengacu pada range yang
salah
2 #DIV/0! Ada nilai yang dibagi dengan angka 0. Ingat bilangan yang dibagi
dengan 0 hasilnya tidak terhingga.
3 #VALUE! Tipe data yang dimasukkan salah atau tidak sesuai.
4 #REF! Ada referensi yang tidak valid
5 #NUM! Rumus menghasilkan nilai yang tidak valid
6 #N/A Nilai yang kita cari tidak dapat ditemukan
7 #NAME? Nama range, tabel, atau fungsi tidak dapat ditemukan
#NULL
Pesan error #NULL muncul jika rentang cell yang kita pakai dalam rumus Excel tidak beririsan atau
mengacu pada range yang salah. Kesalahan ini bisa disebabkan oleh kesalahan penulisan rumus,
terdapat karakter yang tidak pada tempatnya, atau tidak ada pemisah argumen yang benar.
Untuk memperbaiki error ini, kita perlu memperhatikan penulisan rumus yang kita lakukan, dalam kasus
dibawah kita bisa menambahkan separator koma (,) atau semikolon (;) untuk memperbaikinya.
Error #DIV/0!
Pesan error #DIV/0! muncul jika kita membagi sebuah bilangan dengan 0. Dimana kita ketahui bahwa
bilangan yang dibagi dengan 0 akan menghasilkan angka tak terhingga atau unlimited.
Tidak ada alternatif untuk memperbaiki error ini karena pada dasarnya pembagian terhadap 0 ini adalah
sifat dasar operasi matematika. Kita cukup menghapus kolom atau membiarkan isi kolomnya kosong
saja.
Error #VALUE!
Pesan error #VALUE! muncul jika data yang kita masukkan ke dalam rumus tidak sesuai dengan tipe data
yang diminta atau diharapkan oleh rumus Excel. Atau bisa juga disebabkan oleh adanya campuran dari
tipe data yang kita masukkan dalam rumus Excel.
Sebagai contoh: Kita ingin mengalikan angka total penjualan, tetapi di dalam range data yang kita pilih
terdapat teks. Maka akan menghasilkan pesan error #VALUE.
Error #VALUE!
Untuk mengatasi pesan error ini, kita perlu memperhatikan rumus yang kita gunakan. Pada contoh
sebelumnya kita mengalikan Cell E2 dengan G2 dimana E2 merupakan teks. Dalam hal ini, terdapat
kesalahan dalam peletakan range, dimana seharusnya kita mengalikan cell F2 dengan G2.
Jadi dengan memperhatikan cell yang kita gunakan dalam perhitungan, kita dapat mengatasi error ini.
Error #REF!
Pesan error #REF! muncul jika data yang kita memasukkan referensi cell yang salah atau tidak valid. Hal
ini bisa terjadi karena kita menggunakan cell yang sudah dihapus, kita pindah, atau karena cell yang kita
pakai ada pada workbook lain yang tidak tersedia.
Misalkan sebelumnya kita sudah melakukan perhitungan pada cell L2, yaitu penjumlahan cell H2 sampai
H16. Lalu tanpa sengaja menghapus kolom H. Maka, hasil daripada cell L2 akan bernilai #REF!.
Error #REF!
Pesan error #REF! muncul jika kita memasukkan referensi cell yang salah atau tidak valid. Hal ini bisa
terjadi karena kita menggunakan cell yang sudah dihapus, kita pindah, atau karena cell yang kita pakai
ada pada workbook lain yang tidak tersedia.
Misalkan sebelumnya kita sudah melakukan perhitungan pada cell L2, yaitu penjumlahan cell H2 sampai
H16. Lalu tanpa sengaja menghapus kolom H. Maka, hasil daripada cell L2 akan bernilai #REF!.
Error #REF!
Ketika kita tanpa sengaja menghapus kolom H, maka rumus yang berada pada kolom L2 akan bernilai
#REF!
Untuk menghindari error ini, kita perlu memperhatikan referensi cell yang kita gunakan untuk memastikan
kita menggunakan referensi cell yang tepat.
Error #NUM!
Pesan error #NUM! biasanya akan muncul ketika Excel menghasilkan nilai numerik yang tidak valid. Nilai
numerik yang tidak valid ini biasa karena nilai tersebut terlalu besar atau terlalu kecil dan melebihi
spesifikasi yang dimiliki Excel.
Contoh kita menghitung nilai 10^1000 maka Excel akan mengeluarkan pesan error #NUM!
Error #N/A
Pesan error #N/A dapat berarti bahwa rumus Excel menghasilkan nilai yang tidak ada atau Excel tidak
dapat menemukan nilai yang direferensikan.
Biasanya pesan error ini muncul pada fungsi LOOKUP, VLOOKUP, atau HLOOKUP * fungsi ini akan dibahas di
pertemuan Excel berikutnya.
Misalkan kita memiliki data penjualan per-hari dari tanggal 1-26 Juni. Tapi pada persamaan di kolom D2,
kita ingin mencari penjualan di tanggal 27 Juni. Karena tidak terdapat data penjualan tanggal 27 Juni di
kolom A:B. Maka Excel akan memberikan pesan error #N/A.
Error #N/A
Error #NAME
Pesan error #NAME muncul karena Excel tidak mengenali nama tabel atau nama fungsi . Hal ini bisa
terjadi karena kesalahan penulisan rumus atau fungsi yang kita gunakan tidak terdapat dalam Excel.
Misalkan pada contoh dibawah, kita ingin menggunakan fungsi SUM, tetapi kita salah mengetikkan
fungsinya menjadi SUN. Maka Excel akan memberikan pesan error #NAME? karena fungsi SUN tidak
terdapat dalam Excel.
Fungsi berguna lainnya
Hide Function
Saat kita menulis suatu fungsi dalam sel, pengguna lain (yang melihat file kita) bisa melihat
rumus dengan dua cara:
1. Klik dua kali pada sel dan masuk ke mode edit
2. Memilih sel dan melihat rumus di formula bar
Namun jika tidak ingin pengguna lain dapat melihat rumus yang kita gunakan atau bahkan
mengeditnya, kita dapat menyembunyikan formula yang kita tulis. Caranya adalah dengan
memanfaatkan fitur Hide Function
1. Pilih sel di kolom yang memiliki rumus yang ingin Anda sembunyikan
2. Klik tab 'Beranda'
3. Di grup 'Number, klik ikon panah miring kecil di kanan bawah
4. Di kotak dialog 'Format Cells' yang terbuka >> klik tab 'Protection' >> Centang Hidden >>
OK
Step by Step
2 3
4
Lock Worksheet
1. Klik Review tab pada Ribbon cel>> Pada grup Protect>> klik Protect Sheet
2. Di kotak dialog Protect Sheet, masukkan kata sandi yang diperlukan jika Anda ingin
membuka kunci lembar kerja (jika tidak ingin menggunakan kata sandi, dapat
membiarkannya kosong)
3. OK
Circular Reference
● Ketika kita menulis rumus Excel yang merujuk kembali ke selnya sendiri, baik secara langsung
maupun tidak langsung, itu membuat adanya error yang disebut Circular Reference
● Misalnya, jika memilih sel A1 dan mengetikkan =A1 di dalamnya, ini akan membuat referensi
melingkar Excel. Memasukkan rumus atau perhitungan lain pada cell A1 akan memiliki efek yang
sama, misalnya =A1*2 atau =IF(A1=1, "OK").
● Yang terjadi ketika kita melakukan kesalahan circular reference, excel akan memperingatkan kita
dengan dialog box berupa warning sebagai berikut :
Pengenalan Misi
Misi dapat diakses di: [Link]