MICROSOFT EXCEL
Rumus-Rumus
Presented by KIF 2008
KEMPLUD AL-DAL-DULY
Page 1 of 33
MICROSOFT EXCEL
DAFTAR ISI……………………………..……………………………………...
1
PENDAHULUAN……………………………………………………………….
2
1. Pengantar………………………………………………………………..
2
2. Bentuk-Bentuk Bentuk-Bentuk Symbol dalam Microsoft Excel………………………
2
3. Definisi-Definisi Definisi-Definisi Penting………………………………………………..
3
RUMUS-RUMUS RUMUS-RUMUS DALAM MICROSOFT EXCEL…………………………
4
1. Pembulatan……………………………………………………………...
4
2. Perhitungan 1…………………………………………………………...
5
3. Perhitungan 2…………………………………………………………...
6
4. Convert……………………………………………………………….....
9
5. Logika……………………………………………………………………
12
6. Pencarian………………………………………………………………..
14
7. Time dan Date…………………………………………………………..
16
8. Text………………………………………………………………………
18
9. Pertanyaan………………………………………………………………
19
10. Error……………………………………………………………………..
21
11. Tampilan dan Pembacaan……………………………………………...
21
12. Shortcut dalam Microsoft Excel 1……………………………………..
22
13. Shortcut dalam Microsoft Excel 2……………………………………..
25
14. Shortcut dalam Microsoft Excel 3……………………………………..
26
CONTOH APLIKATIF………………………………………………………...
27
1. Arah Qiblat……………………………………………………………...
27
2. Konfersi Masehi Hijriyah………………………………………………
29
PENYIMPANAN DAN PENGAMANAN…………………………………….
30
PENUTUP……………………………………………………………………….
31
PROFIL SINGKAT…………………………………………………………….
32
KEMPLUD AL-DAL-DULY
Page 2 of 33
MICROSOFT EXCEL
PENDAHULUAN
1. Pengantar Puji Syukur kehadirat Allah SWT Tuhan yang Maha Esa dan tak pernah menyia-nyiakan hamba-NYA. Shalawat beserta salam semoga tercurah limpahkan kepada kekasih kita semua, Nabi besar Muhammad SAW, kekasih yang selalu mencintai ummatnya. Seiring kemajuan zaman, kebutahan manusiapun semakin banyak, sedangkan waktu tak pernah berubah dari dulu sampai sekarang. Oleh krenanya, manusia menciptakan alat-alat untuk membantu pekerjaan mereka. Begitupun dengan alat bantu hitung manusia yang terus berkembang. Dari hanya menggunakan biji-bijian sampai penggunaan komputer. Salah satu alat hitung dalam komputer adalah Microsoft Excel. Tulisan
ini
berusah
mengulas
sedikit
mengenai
rumus-rumus
dalam
menggunakan fungsi Microsoft Excel sebagai alat bantu hitung yang mudah dan praktis. Sebenarnya, masih banyak rumus-rumus perhitungan yang ada dalam Microsoft Excel. Akan tetapi penulis sengaja, karena keterbatasan kemampuan dan waktu, hanya menampikan sebagian karena yang sebagian inilah yang sering digunakan dalam perhitungan oleh objek pasar (sasaran penyebaran tulisan) dan penulis sendiri. Jika pembaca masih berminat atau merasa bingung, silahkan langsung melihat pada referensi penulis, Excel Exce l Help, atau bertanya langsung pada penulis. p enulis. 2. Bentuk-bentuk Symbol dalam Microsoft Excel a. Bentuk
“+” tebal hitam : tampil dipojok kanan bawah sel berfungsi untuk
mencopy formula dari sel yang dicopy dengan cara mendragnya kebawah maupun kesamping kanan. Bentuk selnya seperti
dan bertempat di kotak hitam kecil keci l
tebal dipojok kanan bawah. b. Bentuk “+” besar putih: berada disekitar sel berfungsi berfungs i untuk memblok sel. c. Bentuk
dengan tanda panah menuju 4 arah: berfungsi memindah sel dengan
mendrag, dan men-cut sel.
KEMPLUD AL-DAL-DULY
Page 3 of 33
MICROSOFT EXCEL 1
3. Definisi-definisi penting:
a. Cell : kotak-kotak kecil yang ada dalam Microsoft Excel yang berfungsi untuk
menyimpan dan mengolah data. Jumlah cell dalam Microsoft Excel adalah 17.179.869.184.2 b. Cell, worksheet,workbook aktif : cell, worksheet, dan workbook yang sedang
dipilih atau digunakan. c. Column (Kolom): kumpulan cell yang tersusun dari kiri
sampai kanan dan
diberi nama dengan abjad seperti A, B, C sampai XFD atau berjumlah 16.384. d. Rows (Baris): kumpulan cell yang tersusun dari atas sampai bawah dan diberi
nama dengan angka, yaitu 1, 2, 3, sampai 1048576. e. Workbook: buku kerja tempat worksheet. f.
Worksheet: lembar kerja tempat, tempat cell dan data.
g. Ribbon : bagian worksheet paling atas yang berisi kumpulan icon.
h. Number : bilangan, biasanya bisa diisi langsung berupa angka atau satu cell.
Namun terkadang bisa diisi lebih dari satu cell. i.
Range : susunan dari sejumlah data (cell). Range tidak bisa digantikan dengan
angka dan harus tersusun. Artinya, susunan cell tidak boleh terpisah atau berurutan seperti a1:b10. j.
Array : kumpulan dari sejumlah data (baik cell, angka, maupun huruf). Berbeda
dengan range, susunan cell dalam array bisa terpisah seperti a1:a5,a6:a10 dan bisa berebentuk angka yang tersusun seperti {a,b,c,d,e,f,g,h,i,j} maupun yang terpisah seperti {a,b,c,d,e},{f,g,h,i,j}.
1
Definisi-definisi ini asli dari penulis, tanpa sumber, sehingga jika ada dirasa kurang pas, dimohon untuk langsung konfirmasi kepada penulis. 2 Untuk mengetahui jumlah cell pada worksheet, tekan CTRL+SPACEBAR sebanyak 1 atau 2x. KEMPLUD AL-DAL-DULY Page 4 of 33
MICROSOFT EXCEL
RUMUS-RUMUS DALAM MICROSOFT EXCEL 1.
Pembulatan a.
Int {=int(number)}: lengkapnya Integer ( bilangan bulat/utuh): berfungsi untuk
menghilangkan angka desimal. Caranya, Isi number dengan angka atau sel yang akan dihilangkan angka desimalnya.3 b.
4
Trunc {=trunc(number,[number_digits] )}: lengkapnya truncates (memotong)
berfungsi untuk menhilangkan beberapa angka desimal. Banyaknya angka yang tersisa tergantung angka yang dimasukkan pada rumus bagian kedua. Caranya, isi number dengan angka/ sel yang akan dihilangkan angka desimalnya dan isi
[number_digits] dengan angka yang fungsinya adalah untuk menentukan jumlah angka desimal yang akan ditampilkan. Jika number_digits tidak diisi, maka dianggap tidak ada angka desimal atau sama dengan Int. c.
Round {=round(number,[number_digits])}: pembulatan angka desimal ke
angka yang lebih dekat. Banyaknya angka yang tersisa tergantung angka yang dimasukkan pada rumus bagian kedua ([number_digits]). Caranya sama dengan trunc. d.
Roundup {=roundup(number,[number_digits]}: pembulatan angka desimal
keangka yang lebih besar. Caranya sama dengan trunc. e.
Rounddown
{=rounddown(number,[number_digits]):
pembulatan
angka
desimal keangka yang lebih kecil. Caranya sama dengan trunc. f.
Mround
{=mround(number,multiple)}:
lengkapnya
multiple
round
(pembulatan dengan kelipatan), yaitu pembulatan angka kepada kelipatan angka tertentu yang nilainya mendekati angka pada number. Caranya, isi number dengan angka / sel yang nilainya akan dibulatkan, dan isi multiple dengan angka yang akan menjadi terget pembulatan number. Misal =mround(21,5), artinya, pembulatan angka 21 ke angka kelipatan 5 yang nilainya mendekati angka 21, yaitu 20. g.
Ceiling
{=ceiling(number,significance)}:
sama
dengan
mround tetapi
pembulatannya menuju pada angka yang lebih besar. Caranya isi number dengan
3
Jika angka yang dihilangkan angka desimalnya adalah bilangan negatif, maka angka yang dihasilkan angka yang lebih kecil. 4 Tanda [] dalam beberapa formula (rumus) berarti bisa diabaikan. Artinya, jika tidak diisipun rumus akan berjalan walaupun dengan konsekuensi berbeda. Tapi pada beberapa rumus seperti dalam Index, lambang ini tidak berarti bisa diabaikan alias harus difunsikan sebagaimana mestinya. KEMPLUD AL-DAL-DULY Page 5 of 33
MICROSOFT EXCEL
angka / sel yang nilainya akan dibulatkan dan isi significance dengan angka yang akan menjadi target pembulatan number. Misalnya =ceiling(21,5), artinya pembulatan angka 21 menjadi angka kelipatan 5 yang lebih besar dari 21, yaitu 25. h.
{=floor(number,significance)}:
Floor
kebalikan
dari
ceiling,
yaitu
pembulatannya menuju pada angka yang lebih kecil. Misalnya =floor(21,5), artinya pembulatan angka 21 menjadi angka kelipatan 5 yang lebih kecil dari 21, yaitu 20. i.
Even {=even(number)}: round up ke angka genap terdekat.
j.
Iseven {=iseven(number)}: penilaian genap terhadap angka. Jika angkanya genap
mak a “TRUE” dan jika angkanya ganjil maka “FALSE”. k.
Odd {=odd(number)}: round up ke angka ganjil terdekat.
l.
Isodd (=isodd(number)}: penilaian ganjil terhadap angka. Jika angkanya ganjil
mak a “TRUE” dan jika angkanya genap maka “FALSE”. m. Contoh aplikasi: tulislah tabel berikut pada Microsoft Excel.
Coloumn Row 1 2
A 21 21.2367
B
Hasil
5 =int(a2)
21
3
=trunc(a2,2)
21.23
4
=round(a2,3)
21.237
5
=roundup(a2,1)
21.3
6
=rounddown(a2,2)
21.23
7 8
=mround(a1,b1) =ceiling(a1,b1)
20 25
9
=floor(a1,b1)
20
10
=even(a1)
22
11
=iseven(a1)
FALSE
12
=odd(b1)
5
13
=isodd(b1)
TRUE
2.
Perhitungan 1
Penjelasan pada bagian ini langsung menggunakan contoh karena secara definitif sudah sangat jelas dan diketahui umum, sehingga penulisannya hanya akan menambah halaman tanpa menambah manfaat.
KEMPLUD AL-DAL-DULY
Page 6 of 33
MICROSOFT EXCEL
Colomn Row 1
A 20
C5
B
hasil
30
-
30
Penjumlahan
2
=A1+B1
-
50
Penguranga
3
=A1-B1
-
-10
Perkalian
4
=A1*B1
-
600
Pembagian
5
=A1/B1
-
0.6666667
Pangkat
6
=B1^2
-
900
Akar
7
=SQRT(B1)
-
5.477225575
Sin α
8
=SIN(A1*PI()/180)
=sin(radians(a1))
0.34202014
Cos α
9
=COS(A1*PI()/180)
=cos(radians(a1))
0.93969262
Tan α
10
=TAN(A1*PI()/180)
=tan(radians(a1))
0.36397023
Arc sin α
11
=ASIN(B8)*180/PI()
=degrees(asin(b8))
20
Arc cos α
12
=ACOS(B9)*180/PI()
= degrees(acos(b9))
20
Arc tan α
13
=ATAN(B10)*180/PI()
= degrees(atan(b10))
20
Arc cotan α
14
=ATAN(1/B10)*180/PI()
=degrees(atan(1/b10)
70
Atan2
15
=atan2(a1,b1)
-
0.982793723
3.
Perhitungan 2 a.
Sum {=sum(number1,[number2],…. )}: berfungsi menjumlahkan suatu bilangan.
Maksud number1, [number2],…. adalah data I, data II, dan seterusnya yang akan dijumlahkan. b.
Sumif {=sumif(range,criteria,[sum_range])}: menjumlahkan data pada range
dengan syarat atau pada kondisi tertentu. Misalkan =sumif(a1:b10,”>16”). Ini artinya, hanya menjumlahkan sel-sel yang berada pada range a1 sampai b10 yang angkanya lebih besar dari 16. Fungsi tadi akan berbeda jika [sum_range] mempunyai nilai atau diisi. Misalkan =sumif(c1:c10,”>16”,a1:a10). Ini artinya hanya menjumlahkan angka-angka pada range a1 sampai a10 yang sejajar dengan angka-angka pada range c1 sampai c10 yang nilainya lebih besar dari 16. c.
Sumifs
{=sumifs(sum_range,criteria_range1,criteria1,criteria_range2,
criteria2,..)}: menjumlahkan suatu range tertentu hanya pada kondisi-kondisi atau
pada syarat-syarat tertentu dimana keondisi atau syaratnya lebih dari satu. Misalkan =sumifs(b2:e2,b3:e3,”>3%”,b4:e4,”>=2%”). Jika rumus tadi ditulis dalam kata-kata, maka kurang lebih: “jumlahkan angka -angka pada range b2 5
Untuk trigonometri, Coloumn B adalah cara 1 dan coloumn C adalah cara 2. Atan2 (=atan2(x_num,y_num) : berfungsi menghitung sudut yang dibuat dari 2 titik koordinat x dan y. x_num diisi dengan angka/sel yang posisinya menjadi titik x, sedangkan y_num diisi dengan angka / sel yang posisinya menjadi titik y. atan2 sama dengan =atan(x/y). KEMPLUD AL-DAL-DULY Page 7 of 33 6
MICROSOFT EXCEL
sampai e2, dimana angka-angka pada range b3 sampai e3-nya lebih besar dari 3% dan angka-angka pada range b4 sampai e4-nya lebih dari sama dengan 2%”. Artinya ada hubungan antara range1, range2 dan range3 dimana yang dihitung adalah range1 sedangkan range2 dan range3 adalah syarat untuk menjalankan fungsi sum pada range1. d.
Sumproduct
perkalian
{=sumproduct(array1,[array2],[array3] ,…..)}:
beruntun dalam beberapa bagian. Misal =sumproduct(a1:a4;b1:b4), artinya a1*b1+a2*b2+a3*b3+a4*b4. e.
Sumsq {=sumsq(number1,[number2],[number3],…..)}: penjumlahan angka-
angka setelah angka-angka itu dipangkatkan. Misal =sumqs(a1:a3;b1:b3), artinya a12+a22+a32+b12+b22+b32. f.
Sumxmy2 {=sumxmy2(array_x,array_y)}: menjumlahkan angka-angka pada
array dengan mengikuti rumus “ ( − )2 “. Misalkan =sumxmy2(c1:c4,d1:4), artinya (c1-d1)2+(c2-d2)2+(c3-d3)2+(c4-d4)2. g.
Sumx2my2 {=sumx2my2(array_x,array_y)} : menjumlahkan angka-angka pada
range
dengan
mengikuti
rumus
( 2 − 2 )
“
“.
Misalkan
=sumx2my2(c1:c4,d1:4). Artinya (c12-d12)+(c22-d22)+(c32-d32)+(c42-d42). h.
Sumx2py2 {=sumx2py2(array_x,array_y)} : menjumlahkan angka-angka pada
range
dengan
mengikuti
rumus
“
( 2 + 2 )
“.Misalkan
=sumx2my2(c1:c4,d1:4), artinya (c12+d12)+(c22+d22)+(c32+d32)+(c42+d42). i.
Average {=average(number1,[number2],…)} : mencari nilai rata-rata dari suatu
number. j.
Averageif
{=averageif(range,criteria,[average_range])}:
rata-rata
terhadap
bagian tertentu dengan 1 kriteria. Pr insip kerjanya sama dengan sumif, akan tetapi nilai yang dihasilkan adalah nilai rata-rata. k.
Averageifs
{=averageifs(average_range,criteria_range1,criteria1,[criteria_
range2],[criteria2])}: mencari nilai rata-rata dari suatu range tertentu hanya pada
kondisi-kondisi atau syarat-syarat tertentu. Praktiknya sama dengan sumifs, akan tetapi yang dicari adalah averagenya. l.
Gcd {=gcd(number1,[number2],…)} : mencari pembagi yang nilainya paling
besar pada suatu bilangan. Misalnya =gcd(4,8,6), artinya mencari bilangan yang bisa membagi angka 4, 8 dan 6. m. Log {=log(number,[base])}: mencari nilai logaritma. Caranya masukkan angka
yang ingin dicari nilai logaritmanya pada bagian number, kemudian base tidak KEMPLUD AL-DAL-DULY
Page 8 of 33
MICROSOFT EXCEL
perlu diisi karena hasilnya sama dengan log10 yang digunakan pada tabel logaritma pada umumnya. Quotient {=quotient(numerator,denominator)}: menemukan hasil bagi tanpa
n.
koma. Caranya masukkan pembilang pada bagian numerator dan penyebut pada bagian denominator. Misal =quotient(19;3), artinya 19 dibagi 3 = 6. Hasil ini akan sama dengan menggunakan rumus =int(19/3) Mod {=mod(number,divisor)} : rumus ini berfungsi untuk mencari sisa
o.
pembagian. Caranya isi bagian number dengan angka atau cell yang telah diisi angka dan masukkan angka pembagi pada bagian divisor. Misal =mod(7;2), artinya 7 dibagi 2 sisanya 1. Power {=number,power)}: rumus ini digunakan untuk mencari pangkat. Misal
p.
=power(5,4), artinya 5 pangkat 4. Product (=product(number1,[number2]) : rumus ini digunakan untuk perkalian
q.
beruntun. Masing-masing number bisa diisi angka, 1 sel, bahkan range. Misal =product(a1:a10), maka perhitungannya adalah a1*a2*a3*a4,…sampai a10. r.
Sign {=sign(number)}: berfungsi mengembalikan nilai pada sel menjadi bentuk
lambang, yaitu 1 jika positif, -1 jika negatif, dan 0 jika nol. s.
Contoh aplikatif:
Nama
Coloumn
A
Rows 1
B
C
D
Baju
Celana
Topi
Hasil
2
Harga 2010
75000
225000
50000
3
2011
2%
3%
5%
4
2012
3%
-1%
-2%
5
2013
1%
2%
-1%
6 7
2
3
4
>16
8
3
4
5
>2%
9
4
5
6
<1%
11
>2
≥2
<3
12
4
5
6
Sum
13
=sum(b2:d2)
350000
Sumif
14
=sumif(b2:d2,”>50000”)
350000
Sumif
15
=sumif(b3:d3,d8,b2:d2)
275000
Sumifs
16
=sumifs(b2:d2,b4:d4,d9,b3:
275000
Sumprod
17
=sumproduct(a7:a9,c7:c9)
47
Sumsq
18
=sumsq(a7:a9,c7:c9)
106
10
KEMPLUD AL-DAL-DULY
7
Page 9 of 33
MICROSOFT EXCEL
Sumxmy
19
=sumxmy2(a7:c7,a8:c8)
3
Sumx2m
20
=sumx2my2(a9:c9,a8:c8)
27
Sumx2py
21
=sumx2py2(b7:b9,c7:c9)
127
Average
22
=average(a7:c9)
4
Averageif
23
=averageif(a7:c7,a11,a9:c9)
5.5
Averageif 24
=averageifs(a9:c9,a7:c7,a11
6
Gcd
25
=gcd(c7:c9)
1
Log
26
=log(c12)
0.7781
Quotient
27
=quotient(d12,a7)
3
Mod
28
=mod(d12,a7)
1
Power Product
29 30
=power(b7,a7) =product(a7:b9)
9 1440
Sign
31
=sign(d4)
-1
4. Convert {=convert(number,from_unit,to_unit): berfungsi untuk menkorversi nilai.
Convert yang akan dibahas disini adalah convert measurement (konversi ukuran) dan convert time (konversi waktu). Caranya, isi number dengan angka yang akan dikonversi, from_unit dengan satuan asal dari number, dan to_unit dengan satuan hasil. Perhatikan bahasa yang digunakan Microsooft Excel dalam proses konversi berikut ini: i. Berat dan Massa Weight and mass
From_unit or to_unit
Gram
"g"
Slug
"sg"
Pound mass (avoirdupois)
"lbm"
U (atomic mass unit)
"u"
Ounce mass (avoirdupois)
"ozm"
ii. Konversi jarak Distance
From_unit or to_unit
Meter
"m"
Statute mile
"mi"
Nautical mile
"Nmi"
Inch
"in"
Foot
"ft"
Yard
"yd"
KEMPLUD AL-DAL-DULY
Page 10 of 33
MICROSOFT EXCEL Angstrom
"ang"
Pica (1/72 in.)
"Pica"
iii. Konversi waktu Time Year
From_unit or to_unit "yr"
Day
"day"
Hour
"hr"
Minute
"mn"
Second
"sec"
iv. Konversi tekanan Pressure
From_unit or to_unit
Pascal
"Pa" (or "p")
Atmosphere
"atm" (or "at")
mm of Mercury
"mmHg"
v. Konversi gaya Force
From_unit or to_unit
Newton
"N"
Dyne
"dyn" (or "dy")
Pound force
"lbf"
vi. Konversi Energi Energy
From_unit or to_unit
Joule
"J"
Erg
"e"
Thermodynamic calorie
"c"
IT calorie
"cal"
Electron volt
"eV" (or "ev")
Horsepower-hour
"HPh" (or "hh")
Watt-hour
"Wh" (or "wh")
Foot-pound
"flb"
BTU
"BTU" (or "btu")
vii. Konversi power Power
KEMPLUD AL-DAL-DULY
From_unit or to_unit
Page 11 of 33
MICROSOFT EXCEL Horsepower
"HP" (or "h")
Watt
"W" (or "w")
viii. Konversi magnetic Magnetism
From_unit or to_unit
Tesla
"T"
Gauss
"ga"
ix. Konversi temperatur Temperature
From_unit or to_unit
Degree Celsius
"C" (or "cel")
Degree Fahrenheit
"F" (or "fah")
Kelvin
"K" (or "kel")
x. Konversi zat cair Liquid measure
From_unit or to_unit
Teaspoon
"tsp"
Tablespoon
"tbs"
Fluid ounce
"oz"
Cup
"cup"
U.S. pint
"pt" (or "us_pt")
U.K. pint
"uk_pt"
Quart
"qt"
Gallon
"gal"
Liter
"l" (or "lt")
xi. Konversi awalan Prefix
Multiplier
Abbreviation
Exa
1E+18
"E"
peta
1E+15
"P"
Tera
1E+12
"T"
Giga
1E+09
"G"
mega
1E+06
"M"
Kilo
1E+03
"k"
hecto
1E+02
"h"
dekao
1E+01
"e"
KEMPLUD AL-DAL-DULY
Page 12 of 33
MICROSOFT EXCEL deci
1E-01
"d"
centi
1E-02
"c"
milli
1E-03
"m"
micro
1E-06
"u"
nano
1E-09
"n"
pico
1E-12
"p"
femto
1E-15
"f"
atto
1E-18
"a"
xii. Contoh aplikatif No. A
B
Hasil
1
31
=convert(a1,”yr”,”sec”)
978285600
2
50
=convert(a2,”ft”,”m”)
15,24
3
100 =convert(a3,”cel”,”fah”) 122
4
70
=convert(a4,”cup”,”lt”)
23,6639716
5. Logika a.
If {=if(logical_test, value_if_true, value_if_false)}: berguna untuk melakukan
beberapa perhitungan sekaligus dengan berbagai kemungkinan. Misalnya =if(a1
dari atau sama
dengan b3), maka lihat atau gunakan nilai pada b4”. b.
And {=and(logikal1,[logikal2],…): mengecek argumen atau logika yang dipilih.
Jika semuanya sesuai, maka nilainya “TRUE”, dan jika ada salah satu argumen atau
logika
yang
tidak
sesuai,
maka
nilaianya
“FALSE”. Misalnya
=and(b1b5). Artinya:”jika b1 lebih kecil dari b2,b3 lebih ke cil dari
b4 dan b3 lebih besar dari b5, maka nilainya adalah TRUE. Sedangkan jika b1 lebih besar dari b2, b3 lebih besar dari b4 atau b3 lebih kecil dari b5, maka nilainya adalah FALSE. c.
Or {=or(logikal1,[logikal2],….)}: sama dengan “and”, bedanya terletak pada
nilai logika yang diberikan. Jika salah satu argumen atau logika sesuai, maka nilainya “TRUE”, dan jika tidak ada yang sesuai maka nilainya “FALSE”. Misalnya =and(b1b5). Artinya:”jika b1 lebih kecil dari b2,b3 lebih
kecil dari b4 atau b3 lebih besar dari b5, maka nilainya adalah TRUE. Sedangkan KEMPLUD AL-DAL-DULY
Page 13 of 33
MICROSOFT EXCEL
jika b1 lebih besar dari b2, b3 lebih besar dari b4 dan b3 lebih kecil dari b5, maka nilainya adalah FALSE. d.
Iferror {=iferror(value,value_if_error)}: pemberitahuan adanya eror. Caranya,
isi bagian value dengan sel yang akan dicek atau diberi tanda error. Sedangkan bagian value_if_error diisi kata-kata atau apapun yang dikehendaki jika nilai dari sel yang maksud bernilai salah atau terjadi error. e.
Rand {=ran()}: menggunakan nomor secara acak. Misal =ran() berati bilangan
acak dari 0-1, =Rand()*100 berarti angka acak dari 0 - 100. f.
Randbetween{=randbetween(bottom,top)}: menggunakan nomor secara acak
antara bilangan tertentu. Bagian bottom adalah batas bawah, sedangkan top adalah batas atas. g.
Roman {=roman(number,[form])}: bentuk romawi dari angka arab (angka yang
biasa digunakan seperti 1, 2, 3, dan seterusnya). Caranya bagian number diisi angka yang ingin dikonversi, dan form adalah bentuk dari roman yang diinginkan. jika form
tidak diisi, artinya menggunakan form null. Form roman dalam
Microsoft Excel ada 4, dan batas maksimalnya adalah 3999. Form 0 adalah bentuk angka romawi classic yang biasa dipakai. Sedangkan yang lainyya hanya bentuk sederhana dari bentuk klasik. Misalnya =roman(499,0), artinya angka 499 dalam bentuk angka romawi classic. h.
Contoh aplikatif
Nama
Coloumn
A
Rows
B
Hasil
1
2
3
2
Salah
5
If And
3 4
=if(a1
=a1/a2
=and(a1
Betul TRUE
Or
5
=or(a1b2)
TRUE
Iferror
6
=iferror(b3,”ono
Rand
7
=rand()
0.783929
Randbetween
8
=randbetween(100,1000)
923
roman
9
=roman(3999,0)
MMMCMXCIX
KEMPLUD AL-DAL-DULY
sing
ono sing salah
Page 14 of 33
MICROSOFT EXCEL
6. Pencarian a.
7
Lookup
atau
{=lookup(lookup_value,array)
,lookup_vector,
result_vector)}
:
=lookup(lookup_value
=lookup(lookup_value,array) berfungsi
mencari nilai pada column terakhir dari array. Caranya sama dengan vlookup dan hlookup, hanya saja bagian col_index_num tidak digunakan. Sedangkan =lookup(lookup_value,lookup_vector, result_vector)} berfungsi mencari nilai
pada column tertentu. Caranya masukkan angka yang akan dicari pada bagian lookup_value, tempat (array baik column maupun rows) dimana angka tersebut
berada pada bagian lookup_vector dan tempat hasil dari nilai yang akan dicari pada bagian result_value. b.
Vlookup{=vlookup(lookup_value,table_array,col_index_num,[range_lookup] )}: lengkapnya vertical look up, artinya pencarian sesuatu secara vertikal atau atas
bawah. Caranya, tentukan angka atau huruf yang akan dicari pada bagian lookup_value ,
tentukan
tempat
angka
dan
pencariannya
pada
bagian
table_array , dan tentukan kolom pencarian dari angka tersebut pada bagian index_num. Bagian [range_lookup] hanya bisa diisi dengan kata TRUE atau
FALSE dan bisa diisi atau tidak. Fungsi [rang_lookup] adalah menambah fungsi jika nilai pada bagian lookup_value tidak ditemukan pada table_array. TRUE berarti mencari angka yang lebih kecil dari angka awal, sedangkan FALSE berfungsi mengakhiri proses dengan memunculkan tulisan #N/A. c.
Hlookup
{=hlookup(hlookup_value,table_array,col_index_num,[range_
lookup]}: lengkapnya horizontal look up, artinya pencarian sesuatu secara
horizontal atau pencarian ke arah samping. Caranya dan ketentuannya sama dengan
vlookup,
akan
tetapi
sistem
pencariannya
menggunakan
systim
menyamping. d. Index
{=index(array,row_num,[column_num])atau=index(reference,row_
num,[column_num],[area_num])}: pencarian
nilai berdasarkan rows dan
colomn. Cara menggunakan rumus {=index(array,row_num,[column_num]) adalah isi array dengan sejumlah cell yang akan dijadikan rujukan atau basic data, row_num dengan urutan baris dan column_num dengan urutan column yang
ingin
diketahui
nilainya.
Sedangkan
untuk
menggunakan
=index(reference,row_num, [column_num], [area_num]), pada prinsipnya
7
Nilai dari yang dicari sebaiknya tidak berupa kata yang 2 huruf awalnya mempunyai kesamaan dan tidak berurutan. Dengan kata lain, sebaiknya berupa angka, kata yang didahului angka atau kata yang huruf awalnya berbeda. KEMPLUD AL-DAL-DULY Page 15 of 33
MICROSOFT EXCEL
sama dengan =index (array,row_num, [column_num]), akan tetapi pada bagian ini terdapat beberapa array. Kemudian bagian [area_num] diisi dibagian berapa index diberlakukan. e.
Match
{=match(lookup_value,lookup_array,[match_type])}:
menentukan
posisi suatu nilai pada suatu Array. Caranya, isi bagian lookup_value dengan nilai yang akan dicari, tentukan posisi pencarian dimana lookup_value termasuk didalamnya, kemudian tentukan match_type -nya. Jika match_type tidak diisi, maka dianggap null (0). 0 berarti susunan data pada array boleh acak, 1 berarti data pada array harus berurutan dari nilai yang terkecil(A-Z atau 1,2,…), sedangkan -1 berarti data pada array harus berurutan dari yang terbesar (Z-A, …,1,0,-1,….). f.
Rank {=rank(number,ref,[order]}: mencari urutan keberapa suatu nilai dalam
rangking (tingkatan). Caranya number diisi dengan nilai yang akan dicari rangkingnya, ref diisi dengan array atau referance tempat number berada, sedangkan order diisi dengan angka 1 atau 0. Angka 1 berati urutan rangking dilakukan berdasarkan nilai, sedangkan 0 berarti urutan rangking dilakukan berdasarkan posisi suatu nilai dalam sebuah range dari yang Z-A setelah nilai tersebut diurutkan baik sistem A-Z maupun Z-A. g.
Choose {=choose(index_num,value1,[value2],…)} : berfungsi mencari nilai
pada cell (value1,value2, dst) berdasarkan urutan tertentu pada index_num. Caranya, isi bagian number_num dengan angka sebagai acuan pencarian dan isi value1, Value2, dan seterusnya dengan cell-cell yang menjadi tempat pencarian
nilai pada number-Value. h.
Nama
Contoh aplikatif
Coloumn
A
Rows
B
C
D
Hasil
1
1
2
3
4
2 3
2 3
3 4
4 5
5 6
4
4
5
6
7
5
5
6
7
8
6
1
2
3
4
Lookup
7
=lookup(a6,a1:d5)
4
Vlookup Hlookup
8 9
=vlookup(a6,a1:d5,4) =hlookup(a6,a1:d5,5)
4 5
Index
10
=index(a1:d5,b6,c6)
Match
11
=match(d6,a1:a5)
KEMPLUD AL-DAL-DULY
=index(a1:b5,c1:d5,a6,b6,c6)
3&4 4 Page 16 of 33
MICROSOFT EXCEL
Rank
12
=rank(c6,c1:c5,1)
1
Choose
13
=choose(b6,b1,c1,d1,d2,d3)
3
8
7. Time dan Date a.
Now {=now()} : menampilkan waktu dari computer atau laptop dengan format
bulan/tanggal/tahun jam:menit. b.
Today {=today()} : menampilkan tanggal dari computer atau laptop dengan
format bulan/tanggal/tahun.9 c.
Date {=date(year,month,day)}: menampilkan data dalam bentuk date (tanggal)
dengan susunan bulan/hari/tahun (bentuk standar). Tahun dimulai dari 1900, sehingga jika tahun diisi 1 maka tahun yang dimaksud adalah 1901. d.
Time {=time(hour,minute,second)}: menampilkan data dalam bentuk time
(waktu) dengan susunan jam:menit (bentuk standar). e.
Datevalue {=datevalue(“date_text” )} : mengetahui nilai angka dari text
berbentuk tanggal. Penulisan date_text bisa berbentuk bulan/tanggal/tahun (angka semua) atau tanggal-bulan-tahun (tanggal dan tahun berbentuk angka, sedangkan bulan berbentuk text). Misalkan 5-January-1900, berarti nilainya adalah 5 karena nilai disini adalah jumlah hari dari 1 Januari 1900. Jika datevalue yang ingin diketahui (date_text) berupa data dalam cell, maka bisa menggunakan rumus =b3, jika data yang dimaksud berada pada cell b3, kemudian ganti format cellnya
kedalam bentuk General. f.
Timevalue {=timevalue(“time_text”)} : mengetahui nilai angka dari text
berbentuk time. Format “time_text” –nya adalah “jam:menit:detik”, sedangkan rumus
yang
digunakan
dalam
penentuan
tanggal
ini
adalah
(jam+menit/60+detik/3600)/24. Jika yang ingin diketahui adalah data dalam cell, maka bisa menggunakan rumus seperti pada datevalue . g.
Edate {=edate(start_date,month)} : mengurangi atau menambah bulan (month)
dari tanggal tertentu (start_date) dengan tanggal yang tetap. h.
Eomonth : mengurangi atau menambah bulan dari tanggal tertentu (start_date) ,
tetapi menggunakan tanggal terakhir dari bulan yang dihasilkan. i.
Day {=day(serial_number)} : hanya menampilkan urutan hari (day) atau tanggal
dari cell berformat date. 8
Rumus-rumus ini penting jika kita ingin menggunakan data dati cell berbentuk / berformat date atau time karena format tersebut tidak bisa digunakan untuk perhitugan secara langsung. Jika cell berformat date digunakan, maka nilainya menggunakan nilai dari tanggal (datevalue). 9 Format tanggal dan waktu ini bisa dirubah dengan merubah format tanggalnya. Caranya tekan ctrl+1, pilih format cell, pilih date atau time , pilih model yang diinginkan, kemudian ok. KEMPLUD AL-DAL-DULY Page 17 of 33
MICROSOFT EXCEL
j.
Month {=month(serial_number)} : hanya menampilkan urutan bulan (month)
dari cell berformat date. k.
Year {=year(serial_number)} : hanya menampilkan urutan bulan (year) dari cell
berformat date l.
Hour {=hour(serial_number)} : hanya menampilkan jam (hour) dari cell
berbentuk atau berformat time. m. Minute {=minute(serial_number)} : hanya menampilkan menit (minute) dari
cell berbentuk atau berformat time. n.
Second {=second(serial_number)} : hanya menampilkan jam (second) dari cell
berbentuk atau berformat time. o.
Networkdays {=networkdays(start_date,end_date,[holidays])} : menampilkan
hari kerja selama pada waktu tertentu (start_date sampai end_date) dengan hari libur pada sabtu, ahad dan senin10 serta holidays (jika diisi dan diluar hari libur sabtu, ahad dan senin). p.
Weekday {=weekday(serial_number,[return_type])}: menampilkan jarak dari
hari libur ke hari aktif dengan ketentuan (return_type) 1=sabtu, 2=ahad dan 3=senin. q.
Weeknum {=weeknum(serial_number,[return_type])}: menampilkan urutan
minggu keberapa tanggal yang dimaksud (serial_number) dengan ketentuan (return_type) 1 jika setiap minggu dimulai hari Ahad atau 2 jika setiap minggu
dimulai hari Senin. r.
Workday {=workday(start_day,days,[holidays])}:
menambahkan
tanggal
(start_day) dengan hari kerja tertentu (day) dikurangi hari libur tambahan [holidays]. s.
Contoh aplikatif:
Coloumn
A
Rows
B
C
D
1
=now()
2010
11
1
2
=today()
2010
12
26
3
=date(b1,c1,d1)
=date(b2,c2,d2)
4 5
=time(b4,c4,d4) =datevalue("11/25/2020")
13 53
6
Hasil
11/1/2010 12
11 =a3+7
13:12:11 44160
=timevalue("23:33:44")
=d5+7
0.981759259
7
=edate(a3,c4)
=d6+7
11/1/2010
8
=eomonth(a3,2)
=d7+7
1/31/2011
10
Hari libur ini adalah ketentuan dalam Microsoft Excel. KEMPLUD AL-DAL-DULY
Page 18 of 33
MICROSOFT EXCEL
9
=day(a3)
1
10
=month(a3)
11
11
=year(a3)
2010
12
=hour(a4)
13
13
=minute(a4)
12
14
=second(a4)
11
15
=networkdays(a3,b3,d5:d8)
36
16
=weeknum(b3,1)
17
=workday(a3,b5,d5:d8) 8. Text
=weeknum(b3,2)
53 1/19/2011
11
a.
Code {=code(text)}:
b.
Chart {=chart(number)}:
c.
Left {=left(text,num_chars)} : berfungsi mengambil text dari bagian paling kiri.
Caranya isi Text dengan kata,bilangan atau cell dan num_chars (bilangan character) dengan urutan karakter yang akan diambil. d.
Right {=left(text,num_chars)} : berfungsi mengambil text dari bagian paling
kanan, caranya sama dengan left. e.
Mid{=mid(text,start_num,num_chars)} : berfungsi mengambil text dari bagian
tengah. Caranya, isi text dengan kata, bilangan, atau cell, start_num dengan angka yang menunjukan dari urutan keberapa text akan diambi, dan num-chars dengan angka yang menunjukan bilangan keberapa setelah start_num yang akan diambil. f.
Len {=len(text)}: berfungsi menghitung jumlah karakter dalam 1 cell.
g.
Proper {=proper(text)}: berfungsi mengkapitalkan huruf awal dalam text.
h.
Upper {=upper(text)}: berfungsi mengkapitalkan semua huruf dalam text
i.
Replace
{=replace(old_text,start_num,num_chars,new_text)}
:
berfungsi
mengganti text. Caranya, isi old_text dengan text atau cell yang akan diganti textnya, isi start_num dengan awal urutan huruf yang akan diganti, num_chars dengan batas akhir urutan huruf yang akan diganti dan isi new_text dengan text pengganti. j.
Rept {=rept(text,number_times)} : berfungsi melipatgandakan jumlah text.
k.
Search {=search(find_text,within_text,[start_num])} : berfungsi menentukan
posisi text. Caranya, isi find_text dengan text yang akan dicari posisinya,
11
Perlu diingat bahwa setiap text harus diapit oleh tanda petik kecuali text t ersebut berupa cell yang berisis text. KEMPLUD AL-DAL-DULY Page 19 of 33
MICROSOFT EXCEL
within_text dengan text atau cell tempat pencarian find_text dan isi start_num
dengan batas awal pencarian. l.
Substitute {=substitute(text,old_text,new_text,[instance_num])}: sama dengan replace, tetapi yang diganti merupakan text langsung tanpa menggunakan urutan
text. Caranya, isi text dengan text atau cell tempat proses substutusi dilakukan, old_text dengan text yang akan diganti dan new_text dengan text pengganti. m. T {=t(value)} : seperti pertanyan apakah value berupa text atau bukan. Jika value
adalah text maka hasinya adalah value itu sendiri sedangkan value bukan text, maka hasilnya berupa cell kosong. n.
Text {=text(value,format_text)}: berfungsi merubah text kedalam ke format
tertentu. o.
Value {=Value(text)}: menentukan nilai dari text berupa angka dan berformat
tertentu sepert percent, time dan date . p.
Contoh aplikatif: Column
A
Row
C
1
=code(b1)
A
KIF2008
2
=char(a1)
3
4
3
=left(c1,b2)
23.9877
hh:mm:ss
4
=right(c1,c2)
5
=mid(c1,3,d2)
6
=len(c1)
7
=proper(c1)
8
=upper(a7)
9
=replace(c1,1,3,"FALAK")
10
=rept(b1,c2)
11
=search("a",a9,1)
12 13
=substitute(c1,"2008"," =t(c1)
14
=text(b3,c3)
15
=value(a14)
9. Pengecekan
a.
B
D
2
is
12
ISBLANK (value) : pengecekan mengenai apakah cell yang dimaksud berisi data
atau
tidak. Jika kosong maka nilainya TRUE sedangkan apabila berisi data
nilainya FALSE.
12
Jika bentuk cell sesuai, maka nilainya TRUE dan jika tidak sesuai nilainya FALSE. KEMPLUD AL-DAL-DULY
Page 20 of 33
MICROSOFT EXCEL
b.
ISERR (value) : pengecekan mengenai apakah cell yang dimaksud terjadi error
atau tidak. c.
ISERROR (value): pengecekan mengenai apakah cell yang dimaksud mengalami
error atau tidak. d.
ISLOGICAL(value) : pengecekan mengenai apakah cell yang dimaksud adalah
logika atau bukan?. Maksud logika disini adalah nilai TRUE atau FALSE. e.
ISNA(value) : pengecekan mengenai apakah cell yang dimaksud adalah jenis eror
#N/A? f.
ISNONTEXT(value) : pengecekan mengenai apakah cell yang dimaksud adalah
bukan text atau text?. g.
ISNUMBER (value) : pengecekan mengenai apakah cell yang dimaksud adalah
bentuk number? h.
ISTEXT(value) : pengecekan mengenai apakah cell yang dimaksud adalah text?
i.
ISREF(value) : pengecekan mengenai apakah cell yang dimaksud berupa
referensi? j.
Delta(number1,number2): pengecekan mengenai apakah number1 sama dengan number2.
k.
13
Gestep(number1,number2) : pengecekan mengenai apakah number1 sama
dengan atau lebih besar dari number2. l.
Contoh aplikatif: Column Row
13
A
B
C
HASIL
1
=isblank(b1)
=-time(12,34,32)
1
false
2
=iserr(b3)
=f1/0
2
false
3 4
=iserror(b2) =islogical(a3)
=lookup(0,f1:f5) =if(f1
3 4
true true
5
=isna(b3)
=23^8672356751
5
true
6
=isnontext(b4)
=vlookup(2,f1:f4,2)
6
true
7
=isnumber(b2)
=f6/f2
8
=istext(a1)
false
9
=isref(c3)
true
10
=delta(c1,c2)
0
11
=gestep(c2,c1)
1
false
Nilai dari Delta dan Gestep menggunakan angka 1 jika benar dan angka 0 jika salah.
KEMPLUD AL-DAL-DULY
Page 21 of 33
MICROSOFT EXCEL
10. Error a.
##### : terjadi apabila ruang cell tidak cukup memuat tampilan data atau
penggunaan data negative pada cell dengan format time atau date. b.
#DIV/0! : (DIVISOR/0)terjadi apabila nilai dibagi dengan angka 0.
c.
#N/A : jika tahapan rumus tidak sesuai dengan rumus, data tidak ada dalam
logika, vlookup dan sejenisnya, atau data dalam rumus bernilai #N/A. d.
#NAME? : terjadi apabila text yang digunakan tidak dikenali atau tidak sesuai
dengan ketenatuan rumus sehingga perlu diperiksa ulang nama atau sebutan agar sebuah rumus bisa dijalankan. e.
#NULL! : terjadi apabila terjadi perpotongan cell yang digunakan dalam rumus,
ada cell. f.
#NUM! : terjadi apabila number yang digunakan tidak sesuai dengan rumus atau
tidak bisa ditampilkan karena terlalu besar atau terlalu kecil. g.
#REF! : terjadi apabila cell yang dijadikan rujukan tidak sesuai dengan rumus.
h.
#VALUE! : terjadi apabila argument atau operasi yang digunakan tidak sesuai
sehingga nilai yang dihasilkan tidak ada (VALUE tidak ditemukan). Column
E
Row
F
1
=-time(12,34,32)
1
2
=f1/0
2
3 4
=lookup(0,f1:f5) =if(f1
3 4
5
=23^8672356751
5
6
=vlookup(2,f1:f4,2)
yadi
7
=f6/f2
11. Tampilan dan Pembacaan a.
& : berfungsi menggabungkan beberapa rumus berbeda dalam satu cell dengan
tetap mempertahankan konsekuensi masing-masing rumus. b.
Derajat1 : Bagi angka yang dicari/dirubah kebentuk derajat dengan 24, klik
kanan atau ctrl-1, pilih number, pilih time, pilih model 37:30:55. c.
Derajat2 : Bagi angka yang dicari dengan 24, klik kanan atau ctrl-1, pilih o number, pilih custom, pilih model [h]:mm:ss, kemudian ganti dengan h mm’ss
(lambang derajat bisa dicari pada simbol di Microsoft Word).
KEMPLUD AL-DAL-DULY
Page 22 of 33
MICROSOFT EXCEL
d.
Derajat3 : Misalkan coloum E3 berisi angka 4, F3 berisi angka 24, dan G3 berisi
angka 56. Ketik rumus = E3&"°"&F3&"'"&G3&"""" pada sel H3. Cara ini menyebabkan isi dalam sel menjadi tidak berfungsi untuk perhitungan. e.
Derajat4
:
Misalkan
E4 berisi
54,45677.
Pada
cell
F5
isi
rumus
=int(E4)&"º"&int((E4-int(E4))*60)&"'"&round((((E4-INT(E4))*60)-int((E4int(E4))*60))*60,2)&"""". f.
Membuat Urutan: Unutk membuat urutan seperti January, February, dan
seterusnya dengan meng-copy-nya, klik Office Button
, pilih Excel Options ,
pilih popular, pilih Edit Custom List, ketik urutan yang dikehendaki pada kota List entris, klik add, kemudian klik ok. g.
Mengurutkan data : caranya, blok cell-cell yang akan diurutkan, klik kanan,
pilih sort A-Z (urutan a sampai z atau urutan dari yang terkecil kecil sampai yang terbesar) atau Z-A (kebalikan dari A-Z). Kemudian akan muncul kotak dialog sort warning, pilih expand the selection jika menginginkan effect sort meluas
pada cell sekitarnya (kanan-kiri), atau continue with the current selection jika menginginkan fungsi sort tidak berfungsi pada cell sekitarnya (kanan-kirinya). h.
F4 : berfungsi untuk mengunci cell ketika cell tersebut berada dalam sebuh fungsi
atau rumus yang dicopy ke cell yang lain. Caranya, klik atau tulis nama cell yang ingin dikunci, misalnya =C3, lalu tekan tombol F4 pada keyboard. Hasilnya akan berbentuk $C$3. i.
Freeze Pane : Mengunci membekukan (freeze) tampilan beberapa cell sehingga
ketika cell aktif dipindahkan, cell yang dikunci tidak akan menghilang mengikuti perubahan cell aktif seperti sebuah kaca (Pane). Caranya, pilih cell yang akan di Freeze, klik view, pilih Freeze Panes, pilih model Freeze yang diinginkan. 12. Shorcut dalam Microsoft Excel 1 Shortcut
Keterangan/Fungsi
Ctrl+shift+(
Menampakkan row yang disembunyikan.
Ctrl+shift+)
Menampakkan column yang disembunyikan.
Ctrl+shift+&
Memberi border pada cell.
Ctrl+shift_
Menghilangkan border dari cell.
Ctrl+shift+~
Mengubah format cell ke bentuk General.
KEMPLUD AL-DAL-DULY
Page 23 of 33
MICROSOFT EXCEL
Ctrl+shift+$
Mengubah format cell kebentuk mata uang dollar dengan tambahan “00” dibelakang koma.
Ctrl+shift+%
Mengubah format cell ke bentuk Percent
Ctrl+shift+^
Mengubah format cell ke bentuk Exponent ( bentuk angka dengan 2 angka dibelakang koma dengan perkalian 10pangkat xxxx misalnya 3.45600 menjado 3.46E+05
Ctrl+shift+#
Mengubah format cell ke bentuk Date
Ctrl+shift+@
Mengubah format cell ke bentuk Time
Ctrl+shift+!
Mengubah format cell ke bentuk angka dengan pemisah (,) ketika ribuan dan 2 angka dibelakang koma.
Ctrl+shift+*
Memilih (memblok) sekeliling cell yang berisi data.
Ctrl+shift+:
Insert Time
Ctrl+shift+"
Mengcopy nilai (hasil yang tampak pada cell) yang berada diatas cell tempat paste nilai.
Ctrl+shift+plus(+)
menampilkan Insert dialog box untuk menambah cell.
Ctrl+minus (-)
menampilkan Delete dialog box untuk menghapus cell.
Ctrl+;
Memasukkan Date (=today() pada rumus sebelumnya).
Ctrl+`
Pilihan untuk menampilkan Formula (rumus) atau hasil.
Ctrl+'
Mengcopy formula (rumus) yang berada diatas cell aktif.
Ctrl+1
Menampilkan Format Cells dialog box.
Ctrl+2
Mengaktifkan atau menonaktifkan Bold (tebal).
Ctrl+3
Mengaktifkan atau menonaktifkan Italic ( miring)
Ctrl+4
Mengaktifkan atau menonaktifkan Underlining (garis bawah).
Ctrl+5
Mengaktifkan atau menonaktifkan Strikethrough ( garis tengah).
Ctrl+6
Pilihan untuk menampilkan atau menyembunyikan Objek.14
Ctrl+8
Menampilkan atau tidak outline symbol.
14
Jika objek disembunyikan, maka salah satu contoh akibatnya adalah tidak bisa membuat table dan beberapa hal lain dalam Insert. KEMPLUD AL-DAL-DULY Page 24 of 33
MICROSOFT EXCEL
Ctrl+9
Menyembunyikan Row
Ctrl+0
Menyembunyikan Column
Ctrl+a
Jika 1 kali dan cell yang aktif adalah cell yang berisi data, maka befungsi memblock sebagian cell dan yang keduakalinya berfungsi memblock semua cell. Adapun bila yang pertama dilakukan pada cell aktif yang kosong, maka langsung berfungsi memblock semua cell.
Ctrl+b
Menebalkan atau tidak data dalam cell.
Ctrl+c
Meng-copy data atau formula dalam cell.
Ctrl+d
Meng-copy cell diatasnya.
Ctrl+f atau
Menampilkan fungsi Find dengan Find and Replace dialog box,
Shift+f5
SHIFT+F4 menampilkan kembali text yang dicari sebelumnya.
Ctrl+g atau
Menampilkan Go To dialog box.
F5 Ctrl+h
Menampilkan Replace disertai Find and Replace dialog box
Ctrl+i
Me-miring-kan atau tidak tulisan.
Ctrl+k
Menampilkan Insert Hyperlink.
Ctrl+n
Membuat workbook baru.
Ctrl+shift+o
Memilih /memblock semua cell yang berisi comment.
Ctrl+o
Menampilkan Open dialog box untuk membuka dan menemukan file.
Ctrl+p
Menampikan Print dialog box. Sedangkan CTRL+SHIFT+P berfungsi membuka Format Cells dialog box dan tab yang aktif adalah tab Font
Ctrl+r
Menc-copy 1 cell yang berada disebelah kiri.
Ctrl+s
Menyimpan file.
Ctrl+t
Menampilkan Create Table dialog box dan cell yang dipilih akan dikonversi menjadi statistic text.
KEMPLUD AL-DAL-DULY
Page 25 of 33
MICROSOFT EXCEL
Ctrl+u
Memberi atau menghilangkan garis bawah pada tulisa
Ctrl+shift+u
Melebarkan atau mempersempit Formula Bar
Ctrl+v
Menampilkan data yang di-copy atau di-cut.
Ctrl+w
Closes the selected workbook window.
Ctrl+x
Meng-cut cell atau data.
Ctrl+y
Mengulangi aksi yang telah dilakukan
Ctrl+z
Mengembalikan aksi kepada aksi sebelumnya.
Ctrl+shift+f
Menampilkan Format Cell dialogue box dengan yang aktf adalah bagian huruf
13. Shortcut dalam Microsoft Excel 2 Shortcut
Keterangan/Fungsi
F1
Menampilkan Microsoft Office Excel Help .
Ctrl+f1
Menampilkan Ribbon atau tidak.
Alt+shift+f1
Insert Worksheet baru.
F2
Mengaktifkan Formula Bar saat mengedit.
Ctrl+f2
Menambahkan atau mengedit comment
Shift+f2
Menampilkan Print Preview.
F3
Menampilkan Paste Name dialog box.*
Shift+f3
Menampilkan Insert Function dialog box.
F4
Sama dengan CTRL+Y
Ctrl+f4
Mengeluarkan Workbook
F5
Displays the Go To dialog box. .
Ctrl+f5
Mengembalikan ukuran Windows Workbook.
F6
Pilihan mengaktifkan Worksheet, Ribbon, task pane dan zoom contol.
Ctrl+f6
Mengganti Workbook yang aktif.
KEMPLUD AL-DAL-DULY
Page 26 of 33
MICROSOFT EXCEL
F7
Menampilkan Spelling dialog box mengecek spelling pada renge tertentu atau seluruh sheet yang aktif. performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press ENTER, or ESC to cancel.
Shift+f7
Menampilkan fungsi icon reserch (kamus)
F8
Memperluas cell.
Shift+f8
Menambah cell atau range yang dipilih atau diblock.
Alt+f8
Menampilkan Macro dialog box.
F9
Calculate activate worksheet.
Ctrl+f9
Minimize workbook.
F10
Sama dengan ALT.
Shift+f10
Menampilkan smart tag (sama dengan klik kanan mouse)
Ctrl+f10
Maximize windows atau restore wondows.
F11
Membuat chart dari data.
Shift+f11
Insert worksheet baru.
Alt+f11
Membuka Microsoft Basic Editor.
F12
Menampilkan Save As dialog box.
14. Shorcut dalam Microsoft Excel 3 Shortcut
Keterangan/Fungsi
Arrow keys
Memindahkan cell aktif.
Ctrl+arrow key
Memindahkan cell aktif ke ujung range atau ujung worksheet (jika range tidak ada)
Ctrl+shift+arrow key
Memperluas cakupan /block cell sampai ujung range atau ujung worksheet.
Shift+arrow key :
Mempeluas pemblokan cell sampai ke ujung range atau worksheet.
Home
Menuju Column pertama dalam 1 baris.
Ctrl+home
Memindahkan cell aktif ke cell awal (A1)
KEMPLUD AL-DAL-DULY
Page 27 of 33
MICROSOFT EXCEL
Ctrl+shift+home
Memblock cell dari cell aktif sampai cell awal (A1)
End+arrow
Menuju akhir text.
Ctrl+end
Menuju awal atau akhir cell yang berisi text.
Ctrl+shift+end
Memblock sell sampai awal atau akhir cell yang berisi text.
Enter
Mengakhiri rumus atau pekerjaan dengan menekan ok, cancel, dan lain lain.
Alt+enter
Menghapus isi cell dan memeberi warna cell dengan warna sebelumnya.
Esc
Membatalkan rumus yang sedang dikerjakan, dan lain lain, keluar dari menu, submenu atau message windows.
Home
Memindahkah cell aktif ke awal cell dalam 1 row dan halaman awal pada menu.
Page down dan
Memindahkan cell kehalaman sebelunya (DOWN) atau
page up
selanjutnya (UP) dalam satu column.
Alt+page down dan
Memindahkan cell kehalaman sebelunya (DOWN) atau
alt+page up
selanjutnya (UP) dalam satu rows.
Ctrl +page down
Memindahkan sheet aktif ke sheet sebelah kiri (UP) atau ke sheet
atau ctrl+page up
sebelah kanan (DOWN).
Spacebar
Menghapus dan memulai aksi baru pada cell.
Ctrl+spacebar
Memblock semua row dalam column.
Shift+spacebar
Memblock semua column dalam row.
Ctrl+shift+spacebar
Memblock range atau worksheet (jika range tidak ada).
Alt+spacebar
Menampilkan Control Menu untuk Microsoft Excel Window’s.
Tab
Berpindah dari 1 opsi ke opsi lain dalam dialog box.
CONTOH APLIKATIF a.
Arah Qiblat : i. tulis tabel rumus-rumus berikut dalam Microsoft Excel berdasarkan posisinya.
Posisi
Rumus/Keterangan
A2
Angka berapapun
KEMPLUD AL-DAL-DULY
Page 28 of 33
MICROSOFT EXCEL
A3
“Arah Qiblat”
A7
“Lintang Tempat”
A8
“Lintang Makkah”
A9
“Bujur Tempat”
A10
“Bujur Makkah”
B2
=VLOOKUP($A$2,$E$2:$F$65536,2)
B3
=ATAN(TAN((B8)*PI()/180)*COS((B7+C7/60+D7/3600)*PI()/180)/SI N((ABS(B10-(B9+C9/60+D10)))*PI()/180)SIN((B7+C7/60+D7/3600)*PI()/180)/TAN((ABS(B10(B9+C9/60+D10))*PI()/180)))*180/PI()
B7
=VLOOKUP($A$2,$E$2:$L$65536,3)
B8
=21+25/60+21.17/3600
B9
=VLOOKUP($A$2,$E$2:$L$65536,6)
B10
=39+49/60+34.56/3600
C3
=INT(B3)&"º
"&INT((B3-INT(B3))*60)&"'
"&ROUND((((B3-
INT(B3))*60)-INT((B3-INT(B3))*60))*60,2)&"""" C7
=VLOOKUP($A$2,$E$2:$L$65536,4)
C9
=VLOOKUP($A$2,$E$2:$L$65536,7)
D7
=VLOOKUP($A$2,$E$2:$L$65536,5)
D9
=VLOOKUP($A$2,$E$2:$L$65536,8)
E2
“No”
E3-akhir
Tulis urutan nomor dari 1,2,3, dan seterusnya.
F2
“Nama Tempat”
F2-akhir
Tulis nama-nama kota
G1,H1,I1
“Lintang Tempat”
G2,H2,H2
“Derajat”, “Menit”, “Detik”
G3-akhir
Nilai derajat dari kota
H3-akhir
Nilai menit dari kota
I4-akhir
Nilai detik dari kota
J1,K1,L1
“Bujur Tempat”
J2,K2,L2
“Derajat”, “Menit”, “Detik”
J3-akhir
Nilai derajat dari kota
K3-akhir
Nilai menit dari kota
L3-akhir
Nilai detik dari kota
KEMPLUD AL-DAL-DULY
Page 29 of 33
MICROSOFT EXCEL
ii. jika sudah selesai, Variable-nya (nilai yang perlu dirubah) adalah A2. Artinya,
jika A2 berubah, maka semuanya akan berubah karena dalam A2 sudah tersisi atau terkoneksi pada hampir semua cell. b.
Konversi Masehi-Hijriyah i. Bagian 1
Colo umn
B
D
Row 2
masehi
Hijriyah
3
01,01,2008
=I7&"
"&LOOKUP(H7+1,P1:Q12)&"
"&IF(I5-
J5<=0,G7,G7+1) =IF(G8=1,"Jum'at",IF(G8=2,"Sabtu",IF(G8=3,"Ahad",IF(G8=
4
4,"Senin",IF(G8=5,"Selasa",IF(G8=6,"Rabu","Kamis"))))))&" "&IF(H8=1,"Legi",IF(H8=2,"Pahing",IF(H8=3,"Pon",IF(H8=4 ,"Wage","Kliwon"))))
ii. Bagian 2 Coloumn
F
G
=int(left(
=int(mid(b3,4,2))
=int(right(b3,4))-1
=lookup(g2,k1:l1
=int(h2/4)*1461+mod(h2
=SUM(F3:H
2)
,4)*365
3)-13-
Row 2
H
I
b3,2)) 3
=f2
227016 4
=int(i3/10631)
=mod(i3,10631)
5
=g4*30
=int(h4/354)
=mod(h4,354 )
6
=IF(H5=0,0,VLO
=IF(I5-G6<=0,H5-1,H5)
=IF(I5-
OKUP(H5,M1:N
G6<=0,I5-
11,2,TRUE))
G6+355,I5G6)
7
=g5+h5
=match(i6,o1:o12)-1
=i6-lookup (h7+1,n1:o13
KEMPLUD AL-DAL-DULY
Page 30 of 33
MICROSOFT EXCEL
) =mid(i3,7)
8
=mod(i3,5)
iii. Bagian 3 Coloumn
K
Row
L
M
Dan
O
P
Q
1
1
0
2
1
0
1
Muharram
2
2
31
5
2
30
2
Shafar
3
3
59
7
3
59
3
Rabiul Awal
4
4
90
10
4
89
4
Rabiul Akhir
5
5
120
13
5
118
5
Jumadil Awal
6
6
151
15
6
148
6
Jumadil Akhir
7
7
181
18
7
177
7
Rajab
8
8
212
21
8
207
8
Sya’ban
9
9
243
24
9
236
9
Ramadhan
10
10
273
26
10
266
10
Syawal
11
11
304
29
11
296
11
Dzulqa’dah
12
12
334
12
325
12
Dzulhijjah
13
354
13
iv. Keterangan : untuk menggunakannya, masukkan tanggal, bulan, dan tahun
dengan menggunakan angka dan pisahkan dengan tanda koma (,). (lihat contoh diatas). PENYIMPANAN DAN PENGAMANAN 1.
Hidden : menyembunyikan column atau rows. Caranya, letakkan cursor pada
bagian column atau rows, block bagian yang akan di sembunyikan, klik kanan, pilih Hide. Bisa juga dengan menekan ctrl+0 untuk menyembunyikan column atau ctrl+9
untuk menyembunyikan row. Untuk menampilkan kembali, block bagian yang disembunyikan, klik kanan, dan pilih Unhide. 2.
Protect Sheet : melindungi sheet. Caranya, klik pada bagian Review, pilih protect sheet, pilih yang tidak ingin di protec, masukkan password, ok, masukan ulang
pasword, ok. Untuk membuka protect sheet , pada bagian Review pilih unprotect sheet, masukkan password, ok. KEMPLUD AL-DAL-DULY
Page 31 of 33
MICROSOFT EXCEL
3.
Encrypt document: klik pada bagian office Button. Pilih prepare, pilih dan klik Encrypt document, masukkan password, ok , masukkan ulang password, ok. Untuk
menghilangkan password, klik Office Button , pilih prepare, pilih dan klik Encrypt document, hapus password, ok. 4.
Allow users to edit range: sangat berguna untuk mengunci data atau rumus sementara
variabel (input manual data) masih bisa dilakukan, tetapi harus digabung dengan Protect sheet. . Caranya: pilih atau block cell yang akan dikunci, kemudian pilih Review, pilih Allow Users to Edit Ranges, klik New, isi Title dengan nama range,
Rafers to dengan cell yang akan di kunci (tidak usah diisi jika cell sudah diblock di awal), Range Password dengan password yang diinginkan, klik OK. Selanjutnya pilih Protect Sheet, masukkan password yang diinginkan, pilih proses yang tidak ingin dikunci, kemudian klik OK. 5.
Catatan: perintah ini tidak berfungsi jika worksheet tidak dikunci, sehingga untuk
membukanya sama seperti membuka Protect Sheet. PENUTUP
Terima kasih penulis ucapkan kepada para pembaca yang telah meluangkan sedikit waktunya untuk membaca tulisan ini. Penulis juga memohon maaf kepada pembaca jika masih banyak masalah atau rumus tentang Microsodt Officyang belum tercantum dalam tulisan ini. Rumus-rumus dan berbagai penjelasan maupun keterangan yang ada pada tulisan ini, hampir semuanya bersumber dari Excel Help dan pengetahun penulis dari pengalaman dan sedikit membaca buku-buku/ tulisan-tulisan tentang Microsoft Excel. Pembaca bisa langsung melihat pada Excel Help jika ada kata-kata atau contoh-contoh yang sulit difahami. Namun sedikit keterangan, bahwa semua contoh yang ada dalam tulisan ini sudah dicek dengan menggunakan Microsoft Excel dan hasil-hasil dalam tabel contoh adalah hasil-hasil dari Microft Excel. Kritik dan Saran sangat penulis harapkan dari para pembaca demi “menambali”
kekurangan-kekurangan yang ada. Kritik dan saran bisa secara langsung, lewat hp, maupun Facebook.
KEMPLUD AL-DAL-DULY
Page 32 of 33
