modul bnb ms. excel

8/17/2019 Modul Bnb Ms. Excel

1/33

Modul Praktis Belajar Microsoft Excel

SMK Bangun Nusa Bangsa 1

SMK BANGUN NUSA BANGSA

2014

Microsoft Excel 2007Modul Praktikum Komputer Office

BNB Group


2/33



A. Mengenal Microsof Excel

Microsoft Excel atau Microsoft Office Excel adalah sebuah program aplikasi lembar

kerja spreadsheet yang dibuat dan didistribusikan oleh Microsoft Corporation untuk sistem

operasi Microsoft Windows dan Mac OS. Aplikasi ini memiliki fitur kalkulasi dan pembuatan

grafik yang, dengan menggunakan strategi marketing Microsoft yang agresif, menjadikan

Microsoft Excel sebagai salah satu program komputer yang populer digunakan di dalam

komputer mikro hingga saat ini. Bahkan, saat ini program ini merupakan program spreadsheet

paling banyak digunakan oleh banyak pihak, baik di platform PC berbasis Windows maupun

platform Macintosh berbasis Mac OS, semenjak versi 5.0 diterbitkan pada tahun 1993.

Aplikasi ini merupakan bagian dari Microsoft Office System, dan versi terakhir adalah versi

Microsoft Office Excel 2007 yang diintegrasikan di dalam paket Microsoft Office System

2007 .

B. Operasi Dasar

1.

Bootinglah komputer anda dengan cara menekan tombol power pada CPU & Monitor

anda.

2.

Klik tombol Start - All Program – Microsoft Office - Microsoft Excel 2007, jika pada

dekstop komputer sudah ada shortcutnya, anda tinggal klik ganda shortcutnya.

Sebelum mulai memasuki pembahasan Microsoft Excel, ada baiknya kita mengenal

lebih dulu bagaimana tampilan Microsoft Excel itu, beserta beberapa istilah-istilah umum

yang akan digunakan.

Name

Cell Formula Bar

Row (Baris)

Kolom

Vertical Scroll Bar

ZoomHorizontal Scroll BarWorkSheet


3/33



Dalam Microsoft Excel terdapat 5 komponen utama yaitu :

1. Row Heading

Row Heading (Kepala garis), adalah penunjuk lokasi baris pada lembar kerjayang aktif. Row Heading juga berfungsi sebagai salah satu bagian dari penunjuk sel(akan dibahas setelah ini). Jumlah baris yang disediakan oleh Microsoft Excel adalah65.536 baris.

2. Column Heading

Column Heading (Kepala kolom), adalah penunjuk lokasi kolom pada lembar kerjayang aktif. Sama halnya dengan Row Heading, Column Heading juga berfungsi sebagai salahsatu bagian dari penunjuk sel (akan dibahas setelah ini). Kolom di simbol dengan abjad A – Zdan gabungannya. Setelah kolom Z, kita akan menjumpai kolom AA, AB s/d AZ lalu kolomBA, BB s/d BZ begitu seterus sampai kolom terakhir yaitu IV (berjumlah 256 kolom).Sungguh suatu lembar kerja yang sangat besar, bukan. (65.536 baris dengan 256 kolom).

3.

Cell PointerCell Pointer (penunjuk sel), adalah penunjuk sel yang aktif. Sel adalah perpotongan

antara kolom dengan baris. Sel diberi nama menurut posisi kolom dan baris. Contoh. Sel A1 berarti perpotongan antara kolom A dengan baris 1.

4.

Range

Range adalah kumpulan dari beberapa Cell

Contoh : cell A1:A10 ( terdiri dari cell A1 s/d A10 )

5. Formula Bar

Formula Bar, adalah tempat kita untuk mengetikkan rumus-rumus yang akan kitagunakan nantinya. Dalam Microsoft Excel pengetikkan rumus harus diawali dengan tanda ‘=’. Misalnya kita ingin menjumlahkan nilai yang terdapat pada sel A1 dengan B1, maka padaformula bar dapat diketikkan =A1+B1 .

C.

Menggerakkan Penunjuk Sel (Cell Pointer)

Cell Pointer berfungsi untuk penunjuk sel aktif. Yang dimaksud dengan sel aktif ialahsel yang akan dilakukan suatu operasi tertentu. Untuk menggerakan ponter dengan Mousedapat dilakukan dengan meng-klik sel yang diinginkan. Untuk sel yang tidak kelihatan kitadapat menggunakan Scroll Bar untuk menggeser layar hingga sel yang dicari kelihatan laluklik sel tersebut. Untuk kondisi tertentu kita lebih baik menggunakan keyboard. Berikut daftar

tombol yang digunakan untuk menggerakan pointer dengan keyboard :

Tombol Fungsi← ↑ → ↓ Pindah satu sel ke kiri, atas, kanan atau bawah

Tab Pindah satu sel ke kanan

Enter Pindah satu sel ke bawah

Shift + Tab Pindah satu sel ke kiri

Shift + Enter Pindah satu sel ke atas


4/33



Home Pindah ke kolom A pada baris yang sedang dipilih

Ctrl + Home Pindah ke sel A1 pada lembar kerja yang aktif

Ctrl + End Pindah ke posisi sel terakhir yang sedang digunakan

PgUp Pindah satu layar ke atas

PgDn Pindah satu layar ke bawah

Alt + PgUp Pindah satu layar ke kiri

Alt + PgDn Pindah satu layar ke kanan

Ctrl + PgUp Pindah dari satu tab lembar kerja ke tab lembar berikutnya

Ctrl + PgDn Pindah dari satu tab lembar kerja ke tab lembar sebelumnya

D. Mengenal Tipe Data

Tipe data yang akan dimasukan ke dalam sel ada 3 jenis:

1. Data Value (Data Angka)

Data ini dapat di proses untuk hitungan matematika. Termasuk data angaka adalah

data tanggal dan waktu.

Untuk data tanggal 1 = 1 Januari 2014

Untuk data waktu 1 = 1 hari (24 jam), Maka contoh 0.5 = 12.00, 0.25 = 06.00

2. Data Label (data karakterbukan angka)

Data ini adalah semua karakter selain angka, bila angka dipaksakan untuk menjadi

label maka diawali apostrop (‘)

3. Data Rumus

Data rumus diawali dengan ‘=’. Excel akan memberitahu anda setiap ada penulisan

hruf yang tidak dikenal Excel anda bisa menegetahui apakah data merupakan rumus

atau bukan bisa melihat di formula Bar (fx)

E.

Mengenal Alamat Cells

Penulisan alamat sel di Microsoft Excel ada 3 macam :1.

Alamat sel relatif : alamat sel yang bila rumus tersebut dikopi sejauh x kolom dan y

baris maka alamat sel dalam rumus akan berubah bertamba x kolom dan y baris

Contoh: A3

2.

Alamat sel absolut : alamat sel tetap baik kolom amupun baris, sel terkunci dari

perubahan baik baris maupun kolom.

Contoh : $B$3

3.

Alamat sel semi absolut : Kolom tetap baris berubah atau kolom berubah baris tetap

Contoh : $B4, C$5


5/33



Untuk mengubah – ubah jenis alamat sel maka anda bisa menggunakan F4 untuk memahami

ini maka ketiklah data seperti dibawah ini.

1. Alamat Sel Relatif

Ketikan rumus di sel B8 : =B2. Kemudian kopilah 5

baris kebawah dan dua kolom ke kanan. Amati..

2. Alamat sel absolut

Selanjutnya ketiklah rumus di F8 : =$B$2 dan kopilahdengan cara yang sama dengan sebelumnya. Amati...

3. Alamat Sel Semi Absolut (di kunci kolomnya)

Ketik di sel B13 : =$B2, Setelah dikopi amati...

4. Alamat sel semi absolut (dikunci barisnya), ketik di

sel F13 : =B$2, setelah dikopi bandingkanlah..

F. Membuat Perhitungan Matematika Sederhana

Sebelum kita mempelajari materi ini maka anda harus tahu hierarki.

Hierarki (prioritas penyelesaian rumus) pada perhitungan matematika adalah :

Prioritas Operator Kegunaan Matematika Excel1 ( ) Kurung (4+5):3 =(4+5)/3

2 ^ Pemangkatan 52 =5^2

=SQRT( ) Akar √ 25 =SQRT (25)3 * Perkalian 4 x 5 =4 * 5

/ Pembagian 5:2 =5/2

+ Penjumlahan 7 + 8 =7 + 8

- Pengurangan 8 - 3 = 8 - 3

Contoh : Bila anda mengetik rumus = 3+3/2 maka penyelesaiannya bukan 3+3 baru dibagi 2

melainkan 3/2 dahulu baru ditambah 3Latihan :

Tulislah rumus excelnya dan ketiklah di sel excel dan cocokan hasilnya:

Rumus matematika Rumus excel Hasil

(23+3)

(15− 2)+ (4) 6

402150+51

2(/ ) 8


6/33



G. Latihan membuat tabel sederhana

Ketikanlah data sebagai berikut :

Setelah mengetik semua data disamping,

perhatikan total harga (sel F7), total harga

adalah jumlah barang dikalikan harga.

Maka ketik di sel F7 rumus: =D7*E7

(enter) atau anda bisa klik sel yang bersangkutan, tanpa mengetikan nama sel.

Untuk mengkopikan hasil kebawah dengan

rumus yang sama, maka anda tidak perlu

repot mengetik rumus lagi tapi cukup

mengklik drag sel F7 kebawah atau cara

yang lebih cepat adalah melakukan doble

klik pada handle (tanda + pada ujung kanan

bawah sel) sel F7

Selanjutnya untuk total uang (sel f14)

adalah jumlah dari f7 sampai f13 caranya:

1. Aktifkan sel f14

2.

Ketikan =sum(f7:f13) atau klik toolbar sigma (∑) dan klik drag sel yang akan dijumlah.3. Tekan enter

H. Pengaturan layout sederhana

1. Mengganti nama sheet

Gantilah nama sheet1 menjadi latin1 dengan cara doble klik pada sheet dan ketik nama

baru. Selanjutnya tekan enter.

2.

Menggabungkan beberapa selPerhatikan judul tabel data barang agar dapat pas ditengah

tabel maka harus menggabungkan beberapa sel menjadi satu

sel.

Caranya : blok sel B2 sampai F2 selanjutnya klik menu

home – klik icon Merge & Center – pilih Merge & Center ,

untuk memisahkan kembali pilih Unmerge Cell, selanjutnya

atur ukuran fonnya menjadi 16 Bold. Dengan cara yang

samagabungkan sel baris – baris berikutnya, untuk judul

saja.

3.

Mengurutkan dataUrutkan data-datanya berdasarkan harga satuan yang kecil terlebih dahulu

Caranya : blok semua data (tanpa memblok header /kepala tabel) kemudian

pilih menu home – klik icon short & filter.. selanjutnya pilih lah harga

sebagai patokan pengurutan . Pilih Ascending supaya pengurutan dimulai

angka yang lebih kecil dahulu klik OK


7/33



4. Mengatur lebar baris dan kolom

Aturlah lebar kolom dan baris agar sesuai tampilan yang diharapkan tariklah kolom atau

baris pada balok nama baris dan kolom saat pointer menunjukan panah bermata dua

5.

Membuat nomor urut

Buat nomor urut otomatis dengan cara : ketik pada sel B7 angka 1 dan pada sel B8 angka

2. Selanjutnya blok kedua sel, dan tarik handle sampai akhir kolom.

6.

Mengatur warna sel dan hurufUntuk kepala tabel aturlah warnanya Hijau muda dengan tulisan Biru tua caranya blok

kepala tabel (sel B6 sampai F6) kemudian atur supaya bold dan rata tengah lalu pilih

hijau muda (klik ) dan biru tua untuk huruf (klik )

7.

Mengatur Format Sel

Untuk harga maka seharusnya ada keterangan apa satuannya. Maka sekarang aktifkan di

sel E7. Gantilah 500 menjadi Rp. 500,_. Maka akan terlihat sel F7 akan menampilkan

hasil #Value artinya terjadi kesalahan karena data yang dihitung bukan data value/data

angka (karena sudah mengandung huruf Rp). Maka kita harus mengatur format angkanya

saja sebelumnya kembalikan pengetikan dengan undo.

Cara mengatur format sel:a. Blok sel E7 sampai E13

b.

Klik menu home – pada group number klik – curency pilih rupiah dengan 2 angka

desimal.

c. Klik Ok

Selanjutnya samakan format sel total harga dengan harga, caranya blok sel E7 sampai

E13 selanjutnya klik pada menu home kemudian klik drag sel F7 Sampai F13.

8. Menghilangkan baris, Gridline dan mengatur border

Sebelumnya buatlah garis tabel kosong utama dan total uang terhapus. Caranya aktifkan

baris kosong yang mau dihapus kemudian klik kanan – delete , setela itu gabungkan sel-

sel total uang dan jadikan rapat kanan.Terakhir untuk memperjelas batas tabel maka hilangkan lah gridline (klik view hapus

centang garis bantu batas sel dan buatlah border (garis tabel). Carnya klik seperti

point ke 8.selajutnya blok tabel dari B6 sampai F14 maka berikan border dengan

mengklik tombol home dan pilih border Pilih fullborder

Cetaklah hasil tabel anda, perbaiki bila tidak sesuai dengan hasil dibawah ini :


8/33



Simpanlah file anda dengan nama Latihan1 dimedia simpan anda.

I. Membuat Grafik

Untuk membaca suatu data akan lebih mudah bila di visualisasikan melalui gambar

grafik. Sebagai latihan tetaplah aktif di file latihan tadi.Cara membuat grafik

1.

Blok range data Nama Barang dan Jumlah (tekan

Ctrl saat memblok secara terpisah)

2.

Klik menu insert – pada group chart pilih jenis

grafik yang diinginkan (Column, Line, Pie, Bar

Area, Scatter, Chart Line/Other Chart)

3.

Bentuk grafik tidak sembarangan. Untuk perbandingan kecepatan lebih cocok

menggunakan bentuk batang (column) Pilih tipe column – maka secara otomatis grafik

akan jadi dengan segera..mudahkan??? semangat..!!!

4.

Untuk mengedit grafik, klik grafiknya klik design.

5. Anda dapat mengubah layout grafik dengan cara klik icon pada group chart layout

6.

Untuk mengubah tipe grafik klik change

chart type

7.

Untuk menyeleksi data yang digunakan klik

select data

8.

Untuk memindahkan tempat grafik bersatu

dengan data atau terpisah klik icon move

chart location pilih As

Objection(bersatu dengan data) atau As A

New Sheet (terpisah dengan data)

9. Pada menu Layout klik Chart Title

untuk menentukan posisi judul chart,


9/33



anda bisa langsung menentukan judul grafik tadi.

10.

Sedang pada kotak Value Y (Axis) anda ketikan jumlah data yang diinginkan atau

secara otomatis

11.

Anda juga bisa pilih tab legend untuk menentukan posisi legend pada grafik atau

menghilangkannya.

12. Pilih tab data tabel untuk menampilkan data, label atau tidak menampilkan none

13.

Pilih tab gridlines untuk menambah effek gridlines (garis tabel) pada grafik baikgridlines yang horizontal ataupun yang vertikal

14. Grafik yang anda buat kelihatan tidak rapi ? lakukan Editing Huruf, Background

Grafik, dan lainnya dengan cara klik menu format seperti tadi – klik format selection

15. Anda dapat mengatur warna border, style border, shadow dan format 3 dimensi juga

pengaturan transparansi.

16.

Untuk masing-masing itu semua akan disuguhkan format warna solid, gradien, gambar

atau secara otomatis (automatic)

17.

Klik close jika anda selesai format

18. Lebarkan grafik dengan cara rezize object dan pindahkan dengan cara drag pada posisi

yang diinginkan

19.

Perhatikan grafik yang anda buat apakah seperti gambar dibawah ini, apabila belum

rapikan agar sama tampilannya.

J. Waktu dan fungsi lanjutan

Untuk pengaturan rumus, format tanggal, waktu, mata uang dan pengaturan desimal,

karena setiap negara pasti berbeda. Maka sebelum masuk kedalam rumus anda harus

mengeting dahulu regional setting.


10/33



Cara mengatur regional setting

1. Start – Control Panel

2. Klik ganda icon Regional And Language Option seperti

gambar disamping

3.

Pilih indonesia

4. Klik oke dan komputer akan

menampilkan pesan “ anda harusrestart dahulu sebelum setingan

ini sesuai dengan yang kita atur”

5.

Pilih no agar tidak merestart

1. Memasukan tanggal dan waktu

Untuk berlatih coba ketikan isian pada sel-sel berikut

C3 : = today C9 : =now()

C4 : =Ctrl + ; C10 : =Shift + Ctrl +;

C5 : =C4+2

K. Rumus – rumus Excel

Ada beberapa fungsi dalam excel yang sering digunakan antara lain:

1.

Fungsi StatistikMAX (Range) Menampilkan nilai tertinggi dalam range

MIN (Range) Menampilkan nilai terendah dalam range

AVERAGE (Range) Menampilkan nilai rata-rata suatu range

COUNT (Range) Menghitung banyak sel dalam range yang berisi data value

COUNTA (Range) Menghitung banyak sel dalam range yang berisi data

COUNTIF (Range ; X) Menghitung bayak sel yang isinya X

2. Fungsi Matematika

SUM ( Range) Menjumlahkan data dalam range

SUMIF ( Range1;X;Range2

) Hanya menjumlah data di range2 jika range=XROUND ( X;Y ) Membulatkan X kebilangan terdekat hingga Y desimal

ABS ( X ) Membuat positif absolut bilangan X

POWER ( X;Y ) XY

MOD ( X;Y ) Menampilkan sisa hasil pembagian X/Y


11/33



3.

Fungsi Text

UPPER (Text ) Membuat text menjadi huruf besar

LOWER (Text ) Membuat text menjadi huruf kecil

PROPER (Text ) Membuat huruf di awal kata menjadi besar

LEFT (Text;X )Mengambil karakter teks mulai dari kiri sebanyak X

karakter

RIGHT (Text;X )Mengambil karakter teks mulai dari kanan sebanyak X

karakter

MID (Text;X;Y )Mengambil karakter teks mulai dari karakter ke-X

sebanyak Y

LEN (Text ) Menghitung banyaknya karakter dalam teks

CONCATENATE ( X;Y;...dst ) Menggabungkan texs X dan Y dan seterusnya

4. Fungsi logika

IF (P;B;S) = Menulis B bila pernyataan (P) benar dan S jika salah

Fungsi logika membutuhkan pernyataan bukan sekedar data.

Pernyataan (P) itu mengandung salah satu dari tanda : >,


12/33



Latihan :

Ketiklah data berikut dengan membuat file baru dan simpan di media penyimpanan anda.

1.

JKL = Jenis Kelamin, jenis kelamin ditujukan pada karakter pertama pada kode ketik diD8 : =LEFT(C8;1) untuk pengetikan D9, anda tidak perlu ketik cukup klik sel. Kopikan

kebawah, bisa klik drag kebawah atau double klik handle.

2. Formatlah hasil dengan satuan dus

Caranya: Blok kolom G klik kanan Format Cells-Number-Custom. Aturlah type dari

general jadi # “Dus” setelah di enter lihat hasilnya.

3. Waktu = Jam pulang dikurangi jam datang (format dalam bentuk jam dan bulatkan

kedua angka desimal). Karena satuan sel awalnya adalah satuan hari, untuk menjadikan

jam maka harus dikalikan 24 (1 Hari = 24 jam)

Ketik di I8: =ROUND((F8-E8)*24;2)

4.

Kecepatan = hasil bagi dengan waktu (aturlah supaya menjadi bilangan desimal dua

angka dibelakang koma), Formatlah agar satuannya “dus/jam”

Ketik di I8:=ROUND(G8/H8;2)

5.

Kualitas = jika kecepatan kerjanya


13/33



Lihat hasilnya dibawah ini, kalau belum sesuai silahkan perbaiki....!

L. Pembuatan Resume

Dalam membuat resume, pastikan anda masih di file latihan anda sebelumnya, gantilah nama

seheet2 menjadi resume. Ketikan data dibawah ini.

Selajutnya ketik rumus sebagai berikut:

1.

C4 : =Countif (klik kolom D pada sheet

Data;”L”) artinya menghitung sel di kolom

D yang isinya “L”

2.

C5 : =Countif (klik kolom D pada sheet

Data;”P”) artinya menghitung sel di kolomD yang isinya “P”

3.

C6 : =C4+C5l

4. D8 : =SUMIF(klik kolom D pada sheet

data;”L”;klik kolom I pada sheet

Data)/C4 artinya jumlah kecepatan pria

dibagi jumlah pria

5.

D9 : =SUMIF(klik kolom D pada sheet data;”P”;klik kolom I pada sheet Data)/C4

artinya jumlah kecepatan Wanita dibagi jumlah wanita

6.

D10 : =AVERAGE(Klik Kolom I pada sheet data)7. D11 : =COUNTIF(klik klom J pada sheet Data;”BAIK”)

8. D11 : =COUNTIF(klik klom J pada sheet Data;”BURUK”)

Perhatikan hasil resume dibawah ini apakah sudah sama hasilnya kalau belum, benarkanlah..!


14/33



M.

Menyimpan sekaligus memberi password file

1.

Tekan F12, maka akan tampil kotak dialog Save As

2. Tentukan lokasi penyimpanan, misal di my dokumen –klik Tools –klik General

Option

3.

Ketika password to open – klik Ok - ketik ulang password tadi-Ok

4. Klik save, anda akan kembali ke lembar kerja anda dengan file yang sudah di

password

5.

Coba anda tutup filenya dan buka lagi pastikan akan ada permintaan untuk mengetikan

password, jika ya anda sudah berhasil.


15/33



Fungsi logika lanjutan

A. Fungsi LookupBila kemungkinan jawaban dari logika dibawah 4 kemungkinan, maka untuk menuliskan

rumus dengan menggunakan 3-4 IF belum menemui kesulitan. Namun bila kemungkinan

data dan pasangannya banyak, bila kita menggunakan IF betapa panjangnya dan rumit

rumus yang harus ditulis. Oleh karena itu excel menyediakan fungsi lookup. Caranya:

1.

Buatlah tabel bantu yang berisi data logika

2. Buatlah rumus lookup

Sebagai contoh tulislah data berikut.

1. Cara membuat Tabel Bantu

Tabel bantu Vlookup : Blok H9:I13 selanjutnya berilah nama pada Name Box dengan

nama Data_Kue selanjutnya tekan enter

Tabel bantu Hlookup : Blok I20:M21 selanjutnya berilah nama pada Name Box

dengan nama Harga_Kue selanjutnya tekan enter

2.

Memasukan rumus Lookup

Agar kita tinggal mengetikan kode saja dan langsung muncul jenis kue, Harga Kue yang

sesuai dengan kode yang kita masukan. Kita tinggal memakai rumus Vlookup atau

Hlookup tergantung tabel bantu. Kebawah atau kesamping.

Untuk Jenis kue masukanlah rumus pada sel C8 sebagai berikut:

=VLOOKUP(B8;Data_Kue;2;)

Keterangan:

B8 : Sel yang akan di uji

Data_Kue : Tabel bantu yang telah kita buat


16/33



2 : Kolom tabel bantu tempat data yang akan ditampilkan, penulisan titik

koma dibelakang adalah mutlak karena sumber data tidak berurutan.

Untuk Harga Kue masukanlah rumus pada sel D8 sebagai berikut:

=HLOOKUP(B8;Harga_Kue;2;)

Keterangan:

B8 : Sel yang akan di uji

Harga_Kue : Tabel bantu yang telah kita buat2 : Kolom tabel bantu tempat data yang akan ditampilkan, penulisan titik

koma dibelakang adalah mutlak karena sumber data tidak berurutan.

Untuk Total Harga masukanlah rumus pada sel F8 sebagai berikut:

=D8*E8

Kalau sudah selesai coba samakan dengan hasil dibawah ini, kalau belum

sesuaikanlah..mungkin ada kesalahan!


17/33



B. Validasi Input Data

Untuk membuat suatu resume pembayaran dari input data mentah, keuangan maka kita

memerlukan ketelitian data yang kita input agar resume benar. Untuk itulah excel

menyediakan validation untuk memvalidkan data yang kita input. Sebagai latihan bukalah file

Latihan yang pernah di buat.!

Cara membuata validasi data ( Data Validation)

1. Tambahkan Feld di Sel M7 dengan nama “List Kode”

2.

Kemudian Blok M8 sampai Dengan M14, klik menu Data-Data Validation

a.

Atur Allow menjadi List

b.

Tentukan sumber List yakni pada source, caranya: bloklah C8 sampai dengan C14

(C8:C14) tekan enter.


18/33



c. Input Message untuk mengingatkan saat anda akan memasukan data, boleh dikasih

boleh tidak.

d.

Error Alert digunakan untuk mengatur bila input salah, bisa menolak , bisa

memberi pesan dulu, bisa hanya sekedar memberi informasi. Pilih menolak semua

data masukan yang salah.

e. Setelah semua selesai klik Ok. Coba perhatikan hasilnya. Dan coba jika

memasukan sembarang data pada kolom List Kode.


19/33



C. Freeze Panes dan Filter

Freeze Panes panes adalah sebuah fitur atau tools yang disediakan oleh Microsoft Excel.

Kegunaan fitur ini adalah membekukan sel atau baris tertentu, sehingga jika digeser maka sel

yang di freeze (debekukan) tersebut akan tidak bergerak (statis) sedangkan baris (sell lainnya

tidak bisa bergerak), Selain itu freeze juga dapat digunakan untuk membagi ruang kerja

menjadi 4 bagian.

Fitur ini sangat berguna jika anda mempunyai data (tabel) yang panjang, sehingga

dengan membuat judulnya statis (tidak bergerak) / membekukan judul tabel, bisa membantu

anda melihat judul dari isi sel yang digeser.

Pastikan anda masih aktif di file anda (DATA

PRODUK).

Klik di sel B8. Bekukan sel-sel sebelah atas

dan kiri dari bagian B8 dengan cara : Klik

menu View- klik Freeze Panes. Coba gerakan

scroll bar baik vertical maupun horizontal.Bagaimana jadi lebih taukan fungsinya?

Untuk mengembalikan klik menu View-Freeze

Panes-Unfreeze Panes. Anda juga bisa

memilih Freeze Top Row untuk membekukan

baris bagian atas, dan Freeze First Column

untuk membekukan kolom pertama sheet yang

aktif.

Untuk mencari transaksi yang dilakukan untuk pengelompokan tertentu maka anda

dapat melakukan penyeleksian data dengan cara : blok kolom (Nama) dari B8 s/d B14 selanjutnya ketik menu home –klik icon shortt filter – Pilih Ascending (A-Z) atau

Descending (Z-A).

1. Memformat Sel

Untuk mengatur agar tulisan ditengah atau dibawah dari sel

hasil gabung atur melalui menu Home-Format

1. Row Height (ketinggian baris) anda bisa menentukan ketinggian

baris secara manual

2.

Auto Row Height (Ketinggian Baris Secara Otomatis)

3.

Column Widht (kelebaran kolom), anda bisa menentukankelebaran kolom bersama-sama secara manual.

4.

Hide and Unhide (menyembunyikan/menampilkan kolom/baris

yang disembunyikan)

5.

Rename Sheet (mengganti nama sheet)

6. Move Or Copy Sheet (memindahkan atau menyalin sheet)


20/33



7. Tab Color (memberi warana pada tab)

8. Protect Sheet (memproteksi sheet)

9. Lock cell (mengunci sel)

10. Format Cell (memformat cell)

2. Menyisipkan Kolom / Baris

Bloklah kolom D dan sisipkan kolom disitu dengancara klik menu Home – Klik Icon Insert

a.

Insert Cells, untuk memasukan sel tertentu,

kesebelah kanan, kiri, keatas atau kearah bawah

b. Insert Sheet Rows, untuk memasukan baris

c.

Insert Sheet Column untuk memasukan

kolom.

3. Menghapus Kolom / Baris

Bloklah kolom D dan sisipkan kolom disitu dengan

cara klik menu Home – Klik Icon Delete

a.

Delete cells, menghapus cell tertentu, kesebelah

kanan, kiri, keatas atau ke arah bawah

b. Delete Sheet Rows , untuk menghapus baris

c.

Delete Sheet Column untuk menghapus kolom.


21/33



Pengaturan Layout

A. Bekerja Dengan Beberapa windows

1. Bukalah beberapa file Excel (berextensi .xlsx)

yang anda punya.

2.

Selanjutnya, pilih menu View – klik icon Arrange

All pada group windows dan pilih menu Tiled

3.

Klik Ok

4. Anda juga bisa pilih Horizontal, Vertical, dsb

selain Tiled dan lihat hasilnya.

B. Menyembunyikan Windows

1.

Sembunyikan jendela tersebut dengan cara pilih menu

View klik Hide. Perhatikanlah yang terjadi.

2. Atur kembali lembar kerja anda dengan pilihan menu

View – Klik Icon Arrange All – klik Tiled dan klik Ok

3.

Untuk menampilkan kembali klik Unhide – akan adakotak dialog yang mengkonfirmasikan windows mana

yang akan ditampilkan, pilih salah satu - klik Ok.

C. Membuat Custom List

1. Buat workbook baru dengan cara tekan Ctrl+N

2. Klik Off Buton – Excel Option – klik Popular

– klik Edit Custom List

3.

Pada group Custom List klik New List

4.

Pada list entiries masukan data berikut secara berurutandan dibatasi dengan koma; Senin, Selasa, Rabu,

Kamis, Jumat,Sabtu, Minggu.

5. Klik Add untuk memasukan pada cusom List, untuk

membuat List Entries baru anda klik Custom List

entries ketikan data berikut : One, Two, Three,

Four,...dst

6.

Klik Add dan Ok

7. Ketikan kata senin pada sel B2 dan kopikan sampai sel

B10. Coba simpulkan..


22/33



D.

Membelah Windows Aktif (Split Cell )

Untuk bekerja pada dua sel yang berjauhan sering menyulitkan kita karena kita harus

sering menggulung scroll bar. Untuk kita bisa membelah tampilan.

1. Pastikan anda aktif pada lembar kerja anda

2.

Aktifkan sel F10

3.

Klik menu View – klik Icon Split Pada group windows

4.

Lembar kerja sebelah menjadi empat bagian5.

Cobalah geser masing-masing scroll barnya, setelah itu

anda akan lebih paham gunanya.

6. Untuk menghilangkan irisan, klik menu Icon Split yang

tadi anda klik...

E.

Memberi dan Menghilangkan komentar

1. Masih pada file yang sama, Klik Kanan Mouse

pada sel E5.

2. Klik “Insert Coment”

3.

Ketikan kalimat berikut: “Mohon dicekkembali!!!!”

4. Klik Pointer diluar kotak coment, maka sel yang telah di beri coment ada warna

merah, saat anda letakan pointer kesana akan muncul coment yang anda berikan.

5.

Untuk menghapusnya. Anda tinggal klik kanan sel yang akan dihapus commentnya

selanjutnya anda klik delete comment.

6.

Anda bisa membuat comment dari menu Review – Klik New Coment dan seterusnya,

untuk menghapus klik Icon Delete.dst.

F.

Auto Correct

1.

Tekan tombol Ctrl+N untuk membuat lembar kerja baru


23/33



2. Klik menu Office Button – klik Excel Option – klik Tab Proofing – klik Auto Correct

Option

3. Ketikan kata yang “dgn” pada kotak Replace kemudian ketik “dengan” pada with, ini

artinya anda akan mengganti kata “dgn” menjadi “dengan

4.

Klik Add

5. Ketikan lagi kata “bogor” pada kotak Replace dan kata “Bogor” pada kotak with

6.

Klik Add 7. Klik Ok jika sudah selesai

8. Ketikan kata “dgn” pada sel B5, tekan enter lalu tekan kata “bogor”........tekan enter

9.

Apa yang terjadi dalam hal ini semua kata yang anda ketikan pada kotak Replace akan

secara otomatis Conditional Formating digantikan oleh kata yang ada pada kotak with.

G. Conditional Formating

Bila kita akan membedakan formating cell untuk kondisi tertentu maka kita

menggunakan menu. Sebagai latihan lakukanlah tahapan berikut ini:

1. Buatlah lembar kerja baru dan membuat tabel seperti dibawah ini.

2.

Aturlah sel sesuai kondisi yakni agar isi sel berwarna:

Misalnya Merah dengan tulisan hitam jika harga >=400.000, caranya adalah

1.

Blok kolom harga (kolom C)

2.

Kemudian pilih menu Home – Klik Icon Conditional Formating pada group

Styles kemudian

3.

New Rule pilih format Only Cell That Contain

4.

Klik Ok bila pengaturan telah selesai lihatlah hasilnya.

5. Untuk menghapus format caranya adalah Blok Cell yang akan dihapus

formatingnya klik Conditional Formating – Clear Rule – Clear Rules from

selected cell. Dan jika akan menghapus semua formating di seluruh sheet, maka pilihnya adalah Clear rules from entire sheet


24/33



6.

Catatan: Conditional formating biasanya disesuaikan dengan data yang ada,

apakah data value atau label, berdasrkan cell yang berisi data tertentu,

berdasarkan rangking, nilai seppuluh tertinggi (Top 10 Item), kurang dari (less

than), data yang dibawah rata-rata ( above average), berdasarkan data yang

diduplikasi atau yang unik (Unique and duplicate value).

Berikut ini adalah hasil nya..

H.

Memproteksi File, Sheet dan CellTerkadang kita membutuhkan perlindungan sel (Cell Protection) dari perubahan baik isi

maupun rumus. Sebagai contoh tetaplah masih dalam lembar kerja yang sama(diatas)

1. Memproteksi Workbook

Fungsi:

a. Structure: memproteksi tampilan agar sheet tidak ditambah atau dikurangi


25/33



b. Windows : memproteksi tampilan file agar tidak bisa di close, di mini ataupun

maxsimize.

Caranya: Klik menu Review – klik icon Protect Workbook –Protect Structure

and windows password berikutnya Tab Protection pilih structure atau

windows.

2.

Memproteksi isi sheet

Fungsi: memproteksi seluruh sel dalam sheet dari perubahan

Caranya: Klik menu Review- Klik Icon Protect Sheet masukan password

3. Memproteksi Blok sel tertentu

Fungsi: memproteksi blok sel-sel tertentu, caranya

dilakukan dengan bertahap yakni:

a. Menghilangkan kuncian (locked ) semua cells,

dengan cara: tekan Ctrl+A, - klik Kanan

dilembar kerja – klik format cells – klik–

Hilangkan ceklist locked – Ok.

b.

Mengunci (Locked) sel yang akan di proteksi –

klik kanan pada lembar yang di blok – klik format

cells – klik Tab Protection – aktifkan lagi ceklist

Locked – Ok

c. Proteksi sel yang sudah dikunci

Proteksi sel yang sudah dikunci dengan cara: klik

menu Review – klik icon Protect sheet – ketikan

passwordnya – klik Ok – ketik ulang password

yang sama – klik Ok

I. Mengatur Zoom Pada Worksheet

Ada beberapa tampilan lembar kerja yang bisa di setting untuk kenyamanan kerja, dan pengaturannya adalah sebagai berikut.

1.

Klik menu View

2. Klik Zoom untuk mengatur besarnya jendela kerja yang

diinginkan dan bisa memilih yang 100%, 75%, 200% dst,

dan selanjutnya klik Ok.

3. Custom: Artinya kita bisa menentukan besarnya

prosentase sendiri, tapi tidak ada pada tampilan prosentase

4.

Fit Selection: sama dengan zoom to selection yaitu zoom

maksimal pada worksheet (400%)

5.

Atau klik icon 100% untuk mengatur besarnya 100%secara otomatis

6. Atau klik icon Zoom To Selection untuk mengatur

besarnya tampilan worksheet maksimal yang besarnya

sampai dengan 400%


26/33



J. Beberapa Tanda Error

Tanda Error wajib diketahui untuk menganalisa kesalahan dalam penulisan rumus excel.

Beberapa tanda error yang harus diketahui antara lain:

1. #VALUE : data seharusnya berisi data value (angka)

2.

#NAME : ada karakter seperti fungsi tapi tidak dikenal

3. #DIV/0! : tak terhingga karena dibagi 0

4.

#REF! : alamat sel pada rumus hilang karena di delete selnya5. #N/A : rumus lookup yang tidak sesuai datanya.

Untuk lebih paham maka ketiklah data berikut :

Selanjutnya ketik di sel:

F2 : =B2+C2

F3 : =SUME(B2:B3)

F4 : =B2/A2

F5 : =VLOOKUP(E2;$B$2:$C$4;2;)

F6 : =B3+B4 , selanjutnya B4 hilangkan selnya

dengan Edit-Delete

Setelah itu analisa kesalahan yang ada dan cobaanda perbaiki.!

K.

Mengatur Lampiran Lembar Kerja

Ada beberapa tampilan yang bisa kita setting saat kita bekerja atau setelah selesai yang biasanya kita tampilkan adalah kondisi normal.

1. Klik Menu View 2.

Pada group Workbook View klikicon Normal: ini adalah tampilannormal worksheet

3.

Klik icon Page layout akanmenampilkan tampilan lembar kerja

siap print yang akan menampilkanawal dan akhir halaman juga header dan footer

4. Icon page break preview: menampilkan halaman yang kan di print sekaligus arahcetak dan penomoran

5. Icon Custom View: menampilkan tampilan yang ditentukan saja6.

Icon Full Screen: akan menampilkan layer secara penuh dan tanpa toolbar dan menu bar

7.

Untuk menghilangkan gridlines dan formula bar maka tinggl hilangkan ceklistGridlines dan Formula Bar pada group Show/Hide


27/33



PRINT DAN PENDALAMAN UNJUK KERJA

A.

Mencetak data ke Printer

Berbeda dengan Ms. Word, Ms. Excel Mengandung sheet dan cells, sehingga pengaturan

pencetakan harus kita pahmi dengan baik.1. Mencetak sel-sel tertentu

Untuk keperluan ini anda harus men-set area pencetakan. Caranya blok area yang akandi print selanjutnya klik: menu File – Print, maka naskah akan segera terlihat dilayar

perhatikan kotak dialog berikut.

2. Mencetak Seluruh sel pada sheetSecara umum pencetakan di excel sama dengan di word hanya di excel penyebutanrange dalam kotak tersendiri.

Keterangan:1.

Untuk memilih jenis printer yang sesuai dan aktif digunakan2.

Untuk menseting printing, jenis kertas (A4, Legal, Letter), kualitas printing, blackand with, berwarna, dll

3.

Mencetak seluruh halaman yang ada di lembar kerja4.

Untuk menentukan naskah yang akan di print perhalaman5. Menentukan jumlah rangkap print kertas

6.

Melihat tampilan hasil pengaturan pencetakan yang akan kita cetak7. Untuk melakukan proses print apa bila pengaturannya sudah selesai

2

1

3

4

7

5

6


28/33



B. Pengaturan Setup

Untuk mengatur setup tiap halaman maka anda bisa masuk ke menu Page Layout-padagroup pagesetup tentukan:

1. Margin (untuk menentukan batas kertas)

2. Orientation (untuk menentukan Arah cetak)

3. Size (untuk menentukan ukuran kertas)

4. Print Area (untuk menentukan area pencetakan secara manual)

C. Hyperlink

Hyperlink adalah cara untuk menghubungkan suatu bagian dalam slide, file, program,

atau halaman web dengan bagian lainnya dalam bidang-bidang tersebut. Hyperlink digunakan

untuk menunjukan lokasi lain dari dari teks atau objek yang dipresentasikan. Hyperlink dapat

menghubungkan beberapa dokumen, file, objek, aplikasi, halaman web dan sebagainya.

Caranya adalah Klik area yang akan kita buat link – klik menu Insert – klik icon

Hyperlink maka akan ada tampilan seperti dibawah ini.

1. Existing Fille or web page ( menmbahkan File yang ada atau halaman web)

2.

Place In This Document (Mempatkan Dalam Dokumen Ini)

3. Create New Document (Membuat Dokumen Baru)

4. E-mail Address (Memasukan alamat e-mail)

Agar lebih paham maka ikuti langkah berkikut :

1.

Buatlah pada lembar kerja baru pada sheet1, diberi nama “MENU”


29/33



2.

Sheet2 diberi nama “DATA PELANGGAN”

3. Sheet3 diberi nama “DAFTAR MAKANAN”


30/33



4. Sheet2 diberi nama “DAFTAR ALAT TULIS”

5.

Setelah di buat semua lakukanlah langkah seperti dibawah ini.

6.

Pada Tombol kembali setting link ke sheet “MENU”

7.

Lakukanlah hal yang sama pada kotak “Daftar Makanan dan Daftar alat Tulis”

8.

Sudah lebih paham?? Lanjutkan...!


31/33



Latihan Fungsi Logika Lanjutan

Agar anda lebih memahami maka ketiklah pada lembar kerja baru tabel berikut.....

Isilah kolom yang kosong dengan ketentuan sebagai berikut:

1. No : Ambil dari Karakter terakhir kode karyawan sebanyak 2 karakter dan di Value

kan

2. Jenis Kelamin : Jika karakter ke 5 kode sebanyak 1 karakter adalah "L" maka "Pria".

Jika "P" maka tulis "Perempuan"

3. Jabatan : Jika Karakter ke 4 sebanyak 1 karakter adalah:

M = Manajer

C = SecurityS = Sekretaris

K = Karyawan

4.

Status : Jika karakter kode ke 6 sebanyak 1 karakter adalah "N" maka Menikah dan

Jika "S" maka Single.

5. Gaji Pokok : Gaji Pokok berdasar pada Jabatan

Manajer = 2.000.000

Sekretaris = 1.500.000

Karyawan = 1.000.000

Security = 500.000

6.

Tunjangan Jabatan : Tunjangan Jabatan tergantung pada JabatanJika Manajer maka 5% dai Gaji Pokok

Jika Karyawan maka 2,5% dai Gaji Pokok

Jika Sekretaris maka 3% dai Gaji Pokok

Jika Security maka 2% dai Gaji Pokok


32/33



7. THR :

Jika Manajer = 2 x Gaji Pokok

Jika Karyawan = 1 x Gaji Pokok

Jika Secretaris = 1,5 x Gaji Pokok

Jika Security = 0,5 x Gaji Pokok

8. Tunjangan Istri : Tunjangan Istri hanya pada karyawan yang berjenis Kelamin Laki-

Laki dan Statusnya Menikah.. sebanyak 2% dari gaji Pokok selain itu tidak dapatTunjangan.

9. Pajak Penghasilan : Pajak Penghasilan Sebanyak 2% dari Gaji Pokok

10. Pendapatan Bersih : Gaji pokok + Tunjangan jabatan + THR + Tunjangan Istri –

Pajak Penghasilan

Kalau sudah selesai jangan lupa simpan ke media penyimpanan anda....

Latihan Fungsi Lookup

Isilah Kolom kosong dengan ketentuan:

1. Nama Maskapai :

Jika karakter ke 1 = "G" maka "Garuda"

Jika karakter ke 1 = "M" maka "Merpati"

Jika karakter ke 1 = "B" maka "BnB"

Jika karakter ke 1 = "S" maka "Sukoi"


33/33


SMK B N B 33

2. Jurusan : Gunakan fungsi Vlookup

3. Berangkat : Gunakan fungsi Vlookup

4. Jam Tiba : Gunakan Fungsi Vlookup ditambah jam berangkat

5. Biaya Terbang : Gunakan fungsi Vlookup

6.

Potongan : Berdasarkan Kode Terakhir

Jika = "2" maka Potongan = 2 % * Biaya Terbang

Jika = "1" maka Potongan = 07.

Total : Biaya Terbang dikali karakter terakhir kode dikurangi Potongan

Selamat mengerjakan semoga menjadi lebih paham....setelah anda menyelesaikan semua

latihan ini. amiiin

modul bnb ms. excel

Documents