kursus excel asas dan lanjutan

Kursus Microsoft Spreadsheet : Excel Asas dan Lanjutan

Kursus Microsoft SpreadSheet [Excel] Asas dan Lanjutan Unit Sistem Maklumat

Hospital Universiti Sains Malaysia

30 Mac 2011nota daripada versi 07 April 2010

Unit Sistem Maklumat, HUSM Mac 2011


Kandungan Kursus

0. Pengenalan • Pengenalan kepada Microsoft Spreadsheet• Asas Microsoft Excel Sepintas Lalu

◦ Sel ◦ Isih ◦ Graf

• Lanjutan Microsoft Excel secara Am

1. Formula 1.1. Pengenalan kepada Formula

a) Definisi b) Sintaks c) Palang Formula d) Operator

1.2. Asas Formula 1.3. Formula Lanjutan 1.4. Kesalahan dalam Formula

2. Fungsi 2.1. Pengenalan Kepada fungsi

a) Definisi fungsi b) Sintaks fungsi c) Argumen dan Operator d) Alatan Wizard untuk fungsi

2.2. Fungsi Matematik 2.3. Fungsi Logik 2.4. Fungsi Tarikh dan Masa 2.5. Fungsi untuk teks

3. Manipulasi Paparan Excel 3.1. Menamakan sel 3.2. Komen sel

4. Carta 4.1. Pengenalan kepada carta 4.2. Mencipta carta



Pengenalan kepada Microsoft Excel

Microsoft Excel adalah aplikasi komputer lembaran kerja elektronik (Electronic SpreadSheet) untuk membuat pelbagai pengiraan, penjadualan dan membina graf.

Lembaran Kerja adalah khusus kepada mengurus maklumat dan data samada dari bentuk teks, nombor, matawang, nilai-nilai saintifik, jadual, graf dan sebagainya. Berbeza dengan pemproses perkataan seperti Ms Word, lembaran kerja khas untuk menyusun data, pengiraan, memasukkan formula matematik, automasi pengiraan dan aturcara mudah

Lembaran kerja elektronik memudahkan sebarang kerja yang membabitkan pengiraan dengan fungsi mesin kira siap terbina dalam aplikasi ini.

Ms Excel hanyalah satu contoh aplikasi yang ada untuk tujuan ini. Aplikasi yang boleh digunakan untuk tujuan yang sama termasuklah :

Open Office Calc : dapatkan secara percuma di openoffice.org

Google Document, Spreadsheet : gunakan secara online di docs.google.com

Star Office suite

* Google Document menawarkan aplikasi spreadsheet ini secara percuma, online tanpa perlu install apa-apa di komputer anda. Anda hanya perlukan akses ke internet dan pelayar web yang terbaru seperti firefox 3.6, Google Chrome, Internet Explorer 10 ke atas.

* Google Document juga menawarkan khidmat perkongsian dokumen. Satu dokumen boleh dikongsi secara online dan lebih daripada seorang penulis boleh berkerja sama pada dokumen yang sama pada satu masa (kolaborasi) dan sekali lagi, tanpa perlu install apa-apa perisian.



Antaramuka Pengguna (UI) Excel 2003



Excel 2007 in Windows XP



Sel adalah nama untuk mewakilkan satu kotak di dalam lembaran kerja. Setiap satu sel boleh memegang sebarang nilai, huruf atau perkataan, formula, fungsi dan lain-lain elemen. Terdapat berbagai fungsi yang boleh diaplikasikan pada sel.

Gelintar (bergerak antara) sel

1. Gabung Sel ( merge cells)◦ Menggabungkan dua atau lebih sel

▪ Letak cursor pada kotak pertama

▪ Terangkan (highlight) ke kotak terakhir

▪ Klik menu Format dan pilih Cells



▪ Klik pada alignment, klik kotak merge cells dan klik ok



▪ Hasilnya :

2. Memasukkan Fungsi / Formula

Memasukkan fungsi pada sel memudahkan kita untuk melakukan pengiraan secara automatik.

Contohnya, dengan memasukkan fungsi AUTOSUM pada satu sel, pengiraan jumlah setiap item dalam satu jadual akan dilakukan secara automatik.



Maka, pada rajah di atas, sel D2 akan melakukan penjumlahan secara automatik berdasarkan sel yang terlibat seperti dalam rajah di bawah :

Penjumlahan yang dilakukan adalah hasil tambah nilai di dalam sel B2 dan C2. Keputusan ini akan dipaparkan pada sel D2.



Isih [ SORTING ]

Isih adalah proses menyusun semula data secara menaik atau menurun.

1. Senarai 2. Pilih senarai

3. Tekan butang sorting pada toolbar


Menaik Menurun


Graf dan CartaHamparan Kerja Elektronik terdiri daripada satu alatan yang membolahkan

pengguna menterjemah data dalam bentuk yang lebih interaktif. Data-data yang dimasukkan di dalam lembaran kerja akan menjadi rujukan untuk aplikasi hamparan elektronik menghasilkan graf atau carta.

Excel adalah satu contoh aplikasi hamparan kerja elektronik yang mempunyai fungsi menjana graf daripada data pengguna.

Data :

Graf Palang Menegak :



Rumus & Formula

DEFINISI

Contoh :

Formula

Formula dalam excel adalah bermula dengan '=' kerana formula ini ditulis di atas satu sel. Sel tersebut akan memaparkan nilai hasil pengiraan daripada formula tersebut.


FORMULA

Bar Formula

=D1 + D2

Formula atau rumus adalah pernyataan matematik untuk suatu proses seperti penambahan.


Operator Matematik

Operator Logik

Keutamaan Operator

1. Semua operasi di dalam kurungan2. Nombor kuasa3. Semua pendaraban dan pembahagian daripada kiri ke kanan4. Semua penambahan dan pengurangan daripada kiri ke kanan5. Penggabungan ( & )6. Sama, lebih kecil daripada (< ) atau lebih besar daripada ( > )

Contoh :

Formula : =((D1^2*D2) + D3/2-D4*2 + (D1+D2+D3))/D6

D1 = 2, D2 = 3, D3 = 4, D4 = 2, D6 = 3

Jawapan Anda : ___________________ (Kira Secara Manual)___________________ (Kira Melalui Excel)


^ Kuasa/ Bahagi* Darab- Tolak+ Tambah

< Lebih Kecil<= Lebih kecil atau Sama<> Tidak Sama> Lebih Besar>= Lebih Besar atau Sama= Sama


Mesej Ralat BiasaMesej yang dipaparkan dalam sel apabila berlaku sesuatu ralat samada dalam

pengiraan, penamaan, rujukan dan ejaan. Mesej yang dipaparkan membawa maksud tertentu. Di bawah ini adalah mesej yang biasa dipaparkan apabila ralat berlaku.

Message Cause

####### Maklumat atau data tidak muat dalam sel.

Penyelesaian : Lebarkan panjang sel

#REF! Terdapat sel di dalam formula samada tidak wujud atau nama sel dieja salah.

Penyelesaian : Periksa semula formula dan rujukan sel

#NAME? Nama sel atau fungsi dieja salah

Penyelesaian : Periksa semula formula dan betulan nama sel dan fungsi

#VALUE! Satu sel dalam formula mengandungi teks dan bukannya nilai bernombor.

Penyelesaian : betulkan formula atau betulkan nilai dalam sel

#DIV/0! Formula bahagi dibahagi dengan kosong ( 0 ).

Penyelesaian : Periksa semula formula atau gunakan pernyataan bersyarat ( IF ) untuk memastikan pengiraan hanya dilakukan hanya apabila pembawah tidak sama dengan kosong.

KAEDAH MEMASUKKAN FORMULA

1. Klik pada sel yang mana hasil pengiraan akan dipaparkan2. Klik pada tanda '=' pada palang formula

- apabila tanda '=' ditekan, palang formula akan berubah

* sebelum

* Selepas



3. Masukkan formula dengan dimulakan oleh tanda sama '='

Contoh : =B2+B3+B4+B5

4. Klik pada tanda betul

5. Hasil pengiraan berdasarkan formula akan dipaparkan.

CONTOH-CONTOH FORMULA MATEMATIK MENGIKUT ATURCARA KOMPUTER

PendarabanA1*A2

PembahagianA1/A2

PenambahanA1+A2

PenguranganA1-A2

Hasil Tambah A1+A2+A3+A4

Purata(A1+A2+A3+A4)/4

Mencari Peratus

(A1/(A1+A2+A3+A4))*100



LATIHAN FORMULA:

Soalan :

Dengan menggunakan formula, kira

1. Jumlah semua markah2. Purata markah keseluruhan3. Purata markah lelaki4. Purata markah perempuan5. Beza purata markah lelaki dan perempuan6. Sisihan piawai ( ? )


Ahmad 98Bakar 65Burhan 68Fatimah 89Faisal 78Fahmi 85Farhan 82Haryati 80Intan 88Jaafar 58Khalid 78Khairun 65Lokman 65Munira 69


DEFINISI FUNGSI

Fungsi boleh dibahagikan kepada beberapa kategori termasuklah :

1. Kewangan2. Masa dan Tarikh3. Matematik dan Trigonometri4. Statistik5. Pangkalan Data6. Teks7. Logik

Contoh :

=SUM(D1:D5)

Fungsi SUM(A:B) adalah mencari hasil tambah di antara semua sel daripada sel A hingga ke sel B.


FUNGSI

Bar formula juga untuk memasukkan fungsi

Set pengiraan yang berbeza yang menggabungkan banyak proses


Antara Fungsi-Fungsi

Nama Fungsi Keterangan

COUNT Mengira bilangan data dalam suatu julat data

AVERAGE Mengira purata bagi satu julat data

MAX Memberikan nilai paling besar

SUM Mencari hasil tambah semua data

MIN Memberikan nilai paling kecil

ROUND Membulatkan data

TODAY Memaparkan tarikh hari ini pada sistem

Cara menggunakan Fungsi

Data :

1. Klik pada kotak D2 ( paparan jumlah B2 dan C2 2. Klik Menu Insert > Function3. Pada Function Category, klik All.

Pada Function Name, klik SUM. Klik OK.



4. Paparan berikut untuk menentukan sel yang terlibat. Dalam contoh ini, sel yang terlibat adalah B2 dan C2.

5. Klik pada ikon pada kekotak teks Number1

6. Klik dan heret (click and drag) mula dari kota B2 hingga C27. Klik OK



8. Keputusan

9. Salin formula sama kepada sel yang lain.

Dalam lembaran kerja elektronik seperti Excel, fungsi salin dan tampal (copy and paste) yang biasanya membuat salinan nilai/teks ke lokasi baru adalah berbeza dengan aplikasi lain. Dalam lembaran kerja elektronik, fungsi salin dan tampal adalah dibuat kepada formula yang wujud pada sel tersebut. Ini bermakna, dengan menyalin sel D2 yang menunjukkan hasil tambah B2 dan C2, dan ditampal pada sel D3, hasil tambah B3 dan C3 akan dipaparkan pada sel D3 dan bukannya nombor '35' sepertimana di dalam sel D2.

Cuba : 1. Klik pada sel D22. Tekan Ctrl+c atau menu Edit>Copy atau klik kanan tikus > copy3. Klik pada sel D34. Tekan Ctrl+v atau menu Edit>Paste atau klik kanan tikus > paste

Perhatikan nilainya adalah '31' dan bukannya 35. Nilai 31 ini adalah hasil tambah 15 dan 16 dari sel B3 dan C3.



Salin dan Tampal (banyak)

Hasil tambah pada D2 boleh disalin dan tampal ke semua sel JUMLAH untuk setiap baris yang terlibat. Kita tidak perlu salin dan tampal berkali-kali untuk setiap sel tetapi hanya dengan menggunakan fungsi cursor yang ada pada lembaran kerja.

Kaedah :

1. Perhatikan cursor hitam pada sel

2. Klik dan tahan tikus pada cursor hitam tersebut3. Heret hingga ke sel terakhir4. Lepaskan butang tikus

Hasil : Setiap sel pada lajur D3 hinnga D7 akan memaparkan hasil tambah dua sel di kirinya secara automatik.


Cursor Hitam


FUNGSI-FUNGSI LAIN

CONTOH FUNGSI KEWANGAN [FINANCIAL]

TREND(julat data)

fungsi TREND akan meramalkan nilai seterusnya berdasarkan julat yang diberi. Seperti contoh, dengan memilih julat keuntungan untuk 6 bulan sebelumnya, nilai ramalan untuk keuntungan bulan hadapan dapat ditentukan.

KAEDAH

=TREND(B3:B8)



FUNGSI MASA DAN TARIKH

1. Fungsi DATE

Tarikh adalah salah satu elemen penting dalam persembahan data untuk setiap lembaran kerja. Tarikh berkait rapat dengan rekod, julat masa dan perbezaan masa untuk setiap rekod. Penulisan Tarikh dalam lembaran kerja elektronik adalah mudah, hanya perlu tulis mengikut urutan hari-bulan-tahun. 2 digit untuk hari, 2 digit untuk bulan atau 3 huruf untuk bulan dan 4 digit untuk tahun. Digit ini dipisahkan dengan tanda hubung seperti '-' atau '/'.

Contoh : 20-10-200120-Dis-2010

Atau :

1. Terangkan dahulu data2. Klik menu Format > Cells3. Pada Number pilih Date4. Pilih format tarikh yang diinginkan5. Klik Ok

2. Fungsi MASA

Sama seperti Tarikh, format masa adalah dengan menulis mengikut turutan Jam, Minit dan saat serta dipisahkan dengan tanda noktah bertindih ':'.

Contoh :

14:55:45

Untuk format lain, boleh menggunakan cara di bawah

1. Terangkan dahulu data2. Klik menu Format > Cells3. Pada Number pilih Time4. Pilih format tarikh yang diinginkan5. Klik Ok



FUNGSI MATEMATIK

Lembaran kerja elektronik digunakan untuk menguruskan data-data nombor samada untuk analisis, pengiraan, statistik dan kemudian data-data ini dipersembahkan dalam bentuk lain samada jadual, graf, carta dan laporan.

Data-data digit sentiasa memerlukan operasi matematik yang dilakukan ke atasnya untuk mencari jumlah, purata, peratusan kenaikan atau penurunan, peratusan keuntungan, margin keuntungan dan lain-lain.

Antara Fungsi Matematik

FUNGSI PENERANGAN

POWER(nombor, kuasa) Menggandakan nombor ke bilangan kuasacth:

42 =POWER(4,2)

SUM(no1, no2, no3) Mengira hasil tambah semua nombor

LOG10(nombor) Mengira logaritma pada asas 10 bagi nombor

INT(nombor) Memulangkan integer terdekat bagi nombor

SQRT(nombor) Memulangkan nilai punca kuasa dua bagi nombor

RADIANS(nombor) Memulangkan nilai radian bagi nombor(sudut)

SIN(nombor radian) Memberikan nilai sin bagi nombor dalam radian.

COS(nombor radian) Memberikan nilai cos bagi nombor dalam radian

TAN(nombor radian) Memberikan nilai tan bagi nombor dalam radian



Fungsi Untuk Teks

1. Left : mengambil karakter yang ada di sebelah kiri dari satu kesatuan karakter.

Penulisan : =LEFT(teks, jumlah_karakter)

2. Right : mengambil karakter yang ada di sebelah kanan dari satu kesatuan karakter.Penulisan : =RIGHT(teks, jumlah_karakter)

3. Mid : mengambil karakter yang ada di tengah dari satu kesatuan karakter.Penulisan : =MID(teks, angka_awal, jumlah_karakter)

4. Upper : mengubah semua karakter dalam setiap kata yang ada pada suatu teks menjadi huruf besar / kapital.

Contoh : =UPPER(“saya”) menghasilkan SAYA

5. Lower : mengubah semua karakter dalam setiap kata yang ada pada suatu teks menjadi huruf kecil.

Contoh : =LOWER(“SAYA”) menghasilkan saya

6. Proper : mengubah karakter pertama dalam setiap kata yang ada pada suatu teks menjadi huruf besar / kapital dan mengubah huruf berikutnya dengan huruf kecil.

Contoh : =PROPER(“saya sedang praktikum”) menghasilkan Saya Sedang Praktikum

7. Len : mengambil karakter terkiri sejumlah yang dinyatakan pada argumen jumlah_karakter.

Contoh : =UPPER(“Praktikum”) menghasilkan 9

8. Text : mengubah angka menjadi teks dengan jumlah desimal sesuai yang dinyatakan pada argumen format_teks.

Penulisan : =TEXT(angka, format_teks)Contoh : =TEXT(100/4,“0.00”) menghasilkan 25.00

9. Dollar : mengubah angka menjadi teks mata uang dolar, disertai pembulatan dan jumlah desimal menurut argumen jumlah_desimal.

Penulisan : =DOLLAR(angka, jumlah_desimal)Contoh : =DOLLAR(22/7,6) menghasilkan $3.142857.=DOLLAR(22/7,5) menghasilkan $3.14286.

10. Char : mengubah angka antara 1 sampai 255 menjadi karakter kode ASCII.Contoh : =CHAR(125) menghasilkan }.

11. Code : mengubah karakter pertama menjadi kode ASCII.Contoh : =CODE(“Infor”) menghasilkan 73.=CODE(“Ikan”) menghasilkan 73 juga.



Manipulasi, Mengubah Dan Merekabentuk

Paparan lembaran kerja boleh dilakukan manipulasi, boleh diubah dan boleh direkabentuk supaya persembahan data adalah lebih menarik dan berkesan. Penggunaan warna, komen interaktif dan format kertas yang sesuai menjadikan persembahan data tidak lagi secara konvensional dan bosan.

Lembaran kerja mempunyai fungsi untuk mengubah paparan dengan menambah warna, mengubah nama dan teks serta merekabentuk jadual yang lebih menarik

Pengeditan dan Pemformatan Worksheet

UbahSuai Worksheet

Pengeditan dapat dilakukan dengan tiga cara, yaitu :

1. Tekan F2 di keyboard pada cell yang akan diedit2. Double klik pada cell yang mau diedit3. Mengedit pada Formula Bar


MANIPULASI PAPARAN


Perintah untuk Pengeditan

Menu Bar “Edit”

Fill : menyalin data ke lokasi worksheet yang berdekatanPerintah fill terdiri dari empat pilihan :1. Up : isi data pada cell dari bawah ke atas2. Down : isi data pada cell dari atas ke bawah3. Left : isi data pada cell dari kanan ke kiri4. Right : isi data pada cell dari kiri ke kanan

Delete : untuk menghapus cell beserta dengan isinya.1. Tombol Delete : menghapus isi cell terpilih tanpa memindahkan pilihan dari worksheet2. Shift Cell Left : menggeser cell ke kiri



3. Shift Cell Up : menggeser cell ke atas4. Entire Row : menghapus 1 baris5. Entire Column : menghapus 1 kolom

Gambar 4. Kotak Dialog “Delete”

Delete Sheet : untuk menghapus sheet yang sedang aktifMove or Copy Sheet : untuk memindahkan atau menggandakan sheet yang sedang aktif



Gambar 5. Kotak Dialog “Move or Copy”



Perintah untuk Pemformatan Worksheet

Gambar 6. Menu Bar “Format”

Cells… : mengatur format dari komponen yang berhubungan dari cellKomponen-komponen yang dapat diformat

∼ number (format type data) ∼ alignment (format tampilan vertikal, horizontal, dan derajat kemiringan) ∼ font (format tulisan) ∼ border (format garis) ∼ patterns(format warna pada cell) ∼ protection (mengunci ataupun menyembunyikan cell)



Gambar 7. Kotak Dialog “Format Cells”

Row : mengatur format dari baris1. Height : mengatur panjang dari suatu baris2. Autofit : mengembalikan ke ukuran baris yang standart3. Hide : menyembunyikan baris yang ditunjuk4. Unhide : mengembalikan baris yang baru saja dilakukan hide (baris yang tersembunyi)



Gambar 8. Menu “Row”Column : mengatur format dari kolom1. Width… : mengatur lebar dari suatu kolom2. Autofit Selection : mengembalikan ke ukuran kolom yang standart3. Hide : menyembunyikan kolom yang ditunjuk4. Unhide : mengembalikan kolom yang baru saja dilakukan hide (baris yang tersembunyi)5. Standard Width… : mengatur standart lebar dari suatu kolom

Gambar 9. Menu “Column”



Sheet : mengatur format dari sheet yang sedang aktif1. Rename : mengganti nama sheet yang sedang aktif2. Hide : menyembunyikan sheet yang sedang aktif3. Unhide : Menampilkan kembali sheet yang baru saja dilakukan hide (sheet yang tersembunyi)4. Background : memberi gambar sebagai background dari layar kerja MS Excel5. Tab Color : memberi warna pada garis bawah tulisan nama sheet

Gambar 10. Menu “Sheet”



G. MELUKIS GRAF (CHART)Program Microsoft Excell boleh melukis graf dengan cepat dan mudah. Dengan menggunakan data dari Workbook yang telah anda hasilkan anda boleh melukis berbagai bentuk carta seperti carta bar, carta pai dan lain-lain lagi.

Melukis Graf Bentuk Bar1. Pada baru, taipkan maklumat berikut.

2. Highlight dengan cara klik dan seret ke bawah dan ke kanan dari sel B4 hingga G8.3. Klik Insert.


MELUKIS GRAF / CARTA


4. Klik Chart (Kotak dialog chart wizard – Step 1 of 4 dipaparkan)5. Pada Chart Types klik Column.6. Pada Chart Sub-Types pilih salah satu bentuk graf7. Klik butang Next. Kotak dialog Chart Wizart – Step 2 of 4 dipaparkan.



8. Klik butang Next (Kotak dialog chart wizard – Step 3 of 4 dipaparkan)

9. Klik dalam kotak Chart Title.10. Taip tajuk graf ANALISA KEPUTUSAN UPSR.11. Klik dan taip: GRED dalam kotak Category (X) axis12. Klik dan taip: BIL. CALON dalam kotak Value (Y) axis.13. Klik butang Next. (Kotak dialog Chart Wizard – Step 4 of 4 dipaparkan)

14. Pilih As a new sheet: Jika anda ingin memasukkan graf sebagai helaian baru.



15. Klik butang Finish. (Sebuah graf lengkap dipaparkan).

16. Savekan hasil kerja anda dalam fail yang sama.



RENAME SHEETFungsi : Menamakan semula helaian kerja anda.a) Klik Sheet 1

b) Klik Format – Sheet – Rename

c) Namakan Sheet anda.



Soalan Latihan :

Berdasarkan Jadual Dibawah, kira GPA dan CGPA untuk setiap semester bagi pelajar berikut :

Nama Pelajar : Mohd Mukhlisin

Sem 1Subjek Markah Kredit Subjek

Bahasa kebangsaan A- 3

Bahasa Inggeris Perbualan A 3

Matematik Aljabar A 4

Asas Penulisas Saintifik A 4

Asas Akaun A 4


Bahasa Inggeris Profesional A- 3

Bahasa Mandarin C 3

Pendidikan Islam B 3

Matematik Diskrit C 4

Teori Ekonomi B- 4


Bahasa Kebangsaan Sastera A 3

Kenegaraan Malaysia B 3

Sejarah Tamadun Istam B+ 3

Statistik C+ 4

Ekonomi Macro A 4



A 4

A 3.66

A- 3.33

B+ 3

B 2.66

B- 2.33

C+ 2

C 1.66

C- 1.33

D+ 1

D 0.66

D- 0.33

E 0 [Gagal]Jadual Nilai Markah Kredit untuk Markah

Cara pengiraan CGPA untuk setiap sem :

Jumlah kredit markah = Markah kredit x nilai kredit subjek

Jumlah kredit subjek = Jumlah nilai kredit subjek

CGPA = jumlah kredit markah / jumlah kredit subjek

Cth :

Subjek 123 : A Cr : 3Subjek 234 : B- Cr : 4

Jumlah Kredir Markah = (4 x 3) + (2.33 x 4) = 12 + 9.32 = 21.32

Jumlah kredit subjek = 3 + 4 = 7

CGPA = 21.32 / 7 = 3.045 ~= 3.05 (Bundar)



Cara Pengiraan GPA

Formula :

Jumlah semua kredit markah untuk semua sem / Jumlah Semua kredit subjek untuk semua sem

Cth :

Sem 1Jumlah kredit markah = 21.32 Jumlah Kredit subjek = 7

Sem 2Jumlah kredit markah = 22.33 Jumlah kredit subjek = 8

GPA = (21.32 + 22.33 ) / (7+8) = 2.91

Selamat Berusaha


kursus excel asas dan lanjutan

Documents