Analisis Data Berdasarkan Formula Excel

Jika anda mempunyai pengalaman menggunakan Excel, pasti anda telah faham bagaimana Excel berfungsi.  Kebiasaan kebanyakan pengguna menggunakan Excel bagi perkara-perkara yang berkaitan dengan nombor.  Contohnya jadual senarai bilangan guru mengikut sekolah, markah pelajar, pengiraan kewangan dan sebagainya.  Namun tahukah anda sebenarnya Excel mempunyai keupayaan lebih daripada itu.  Excel juga boleh dijadikan sebagai satu pangkalan data dengan melibatkan fungsi form, query dan analisis carta.  Selain itu, Excel juga akan menjadi lebih efektif apabila pengguna mahir menggunakan sintak formula yang boleh dibina secara manual atau menggunakan butang Insert Function.

 

Kebiasaannya pengguna menggunakan sintak SUM, COUNT, dan Operasi asas seperti tambah, tolak, darab bahagi.  Namun banyak lagi sintak-sintak yang tidak rumit tetapi berupaya memberikan hasil analisis yang cepat dan tepat.  Anda boleh menggunakan fungsi Insect Function sebelum anda benar-benar mahir menggunakan sintak-sintak di dalam Excel.  Setelah anda mahir, anda boleh menggunkan kreativiti sendiri untuk menganalisis data.  Jika anda lebih mahir, anda boleh membangunkan satu sistem atau aplikasi yang boleh menganalisis data secara automatik.

Terdapat dua kaedah yang boleh digunakan untuk menaip sintak formula di dalam Excel iaitu,

  1. Menaip secara manual.
  2. Menggunakan fungsi Insect Function.

Sebelum memulakan proses input sintak formula di dalam Excel, anda perlu memastikan List Seperator bagi tetapan komputer anda.  Ini boleh dilakukan dengan mengemaskininya di bahagian Control Panel > Regional Setting.

Buka Control Panel komputer anda.

1 – Klik pada pada perkataan Change keyboard or… di bawah Clock , Language and Region.

2 – Pada bahagian Format anda boleh tukarkan format tarikh mengikut format Malaysia DD/MM/YYYY.Regional 1

3 – Klik Butang Addational Setting dan pastikan simbol List Separator anda sama ada “;” atau “,”.  Anda boleh memilih mengikut kesuakaan anda.

Regional 2

List separator ini adalahpenting ketika anda memasukkan sintak formula di dalam sel Excel.  Contohnya formula Count If iaitu =CountIf(Range; Criteria).  Simbol “;” di antara Range dan Criteria itu adalah List separator yang dimaksudkan.

Header manual

Menaip formula secara manual adalah dengan cara memasukkan formula ke dalam cell Excel.  Kemahiran ini memerlukan anda mengetahui dan telah biasa dengan penggunaan sintak formula di dalam Excel. Contohnya bagi Formula mengira bilangan bulan kenaikan gaji di dalam data maklumat guru.  Simbol sama dengan (=) perlu diletakkan dihadapan semua sintak formula.  Simbol (=) tersebut memberi arahan kepada Excel sel untuk menjana formula yang ditaip.  Jika simbol (=) tidak ditulis, Excel menganggap sel tersebut adalah berbentuk teks.

Formula yang digunakan adalah =COUNTIF(Range, Criteria)

1 – Buka fail Excel LatihanFormula.  Kemudian bina satu senarai bulan kenaikan gaji di dalam sheet lain.  Ini akan dijadikan sebagai Range.

2 – Bina satu jadual dengan medan BULAN PERGERAKAN GAJI dan BILANGAN.  Bulan akan dijadikan sebagai Criteria iaitu syarat yang akan memenuhi formula.info13 – Taip formula =COUNTIF(A2;A66,C3) di dalam sel D3. Selain menaip, anda boleh masukkan Range dan Criteria dengan menggunakan tetikus iaitu dengan hanya pilih senarai Range PERGERAKAN GAJI dan klik pada sel D3.  Tekan Enter

M2.1 1

4 – Untuk baris yang lain anda hanya perlu salin (copy and paste) dari sel D3 atau drag dari sel tersebut.

M2.1 2

Header FunctionM2.3 8 warning

1 – Bina satu sheet baru. Salin dari sheet LatihanFormula NO.KP, TARIKH LAHIR dan OPSYEN BERSARA ke dalam sheet baru tersebut.

2 – Tambah satu lajur baru dan namakan sebagai UMUR.M2.3 1

3 – Daripada tab Formulas, pilih ikon fx Insert Function.m2-3-2.png

4 – Pilih Logical dari Or select category

5 – Pilih function IFM2.3 3

6 – Taip kriteria yang dikehendaki di dalam kotak Logical_test.
sel B2 boleh dipilih menggunakan kursor yang di klik pada sel tersebut. Kotak garisan putus-putus akan kelihatan.  Perkara yang sama juga dilakukan untuk sel D2.

7 – Taipkan di dalam kotak value_if_true jika kriteria pada Logical_test tepat dan taip juga di dalam kotak value_if_false jika kriteria tidak tepat.
Contoh di sini,
jika UMUR(2018 – Year(B2)) melebihi(>) umur OPSYEN BERSARA(D2), Excel akan mengeluarkan hasil SILA SEMAK. Dan jika UMUR kurang daripada OPSYEN BERSARA, Excel akan mengeluarkan hasil OK.M2.3 4

Contoh sintak fungsi IF yang perlu ditaip bagi situasi di atas. kemudian klik OK.M2.3 5

8 – Salin nilai bagi sel C2 ke dalam semua sel lajur C.M2.3 6

M2.3 info1

9 – Bagi menyemak semula hasil, gunakan fungsi filter.  Highlight baris 1.

10 – klik menu Filter dari ikon Sort & Filter di dalam tab Home.M2.3 7

11 – Klik anak panah pada lajur UMUR.

12 – Hilangkan tik pada OK. Klik OK.M2.3 8

M2.3 8 warning

Dan anda akan dapat melihat hasilnya.  Oleh itu rekod yang telah difilter menunjukkan umur individu telah melebihi tempoh bersara wajibnya tetapi nama masih berada di dalam pangkalan data.M2.3 9


SINTAK FORMULA EXCEL YANG BIASA DIGUNAKAN UNTUK MENGANALISIS DATA.

Bil Fungsi Kod Formula Penerangan
1 SUM SUM(number1,[number2],…]) Menjumlahkan nilai dalam beberapa sel.
2 SUMIF SUMIF(range, criteria, [sum_range]) Menjumlahkan nilai dalam beberapa sel mengikut satu kriteria yang ditetapkan
3 SUMIFS SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) Menjumlahkan nilai dalam beberapa sel mengikut beberapa kriteria yang ditetapkan
4 IF IF(logical_test, [value_if_true], [value_if_false]) Digunakan untuk memberikan maklumbalas yang berbeza sama ada situasi betul dan situasi salah.
5 LOOKUP LOOKUP(lookup_value,  lookup_vector,  [result_vector]) Digunakan untuk mengenal pasti satu nilai di antara satu baris atau satu lajur.
6 VLOOKUP VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Memulangkan satu nilai daripada satu set data berdasarkan kriteria yang ditetapkan.  Carian dilakukan mengikut baris baris.
7 COUNT COUNT(value1, [value2], …) Mengira bilangan sel yang mengandungi nombor.
8 COUNTIF COUNTIF(range, criteria) Mengira bilangan sel yang memenuhi satu kriteria yang ditetapkan.
9 COUNTIFS COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) Mengira bilangan sel yang memenuhi lebih dari satu kriteria yang anda tetapkan.
10 DATEDIF DATEDIF(start_date,end_date,unit) Mengira tempoh masa antara dua tarikh sama ada dalam kiraan hari, bulan atau tahun.  Gunakan unit “D” – hari, “M” – bulan, “Y” – tahun.
11 CONCATENATE CONCATENATE(text1, [text2], …) Menggabungkan beberapa sel teks ke dalam satu sel.
12 RANK RANK(number,ref,[order]) memulangkan senarai urutan bagi beberapa nombor dalam sel yang dipilih.
13 EXACT EXACT(text1, text2) Mengenal pasti sama ada dua sel yang mengandungi teks yang sama.

Latihan M4

Berdasarkan kepada maklumat data yang diberikan,

  1. Bina satu column baru yang menggunakan fungsi IF.  Muat turun bahan latihan Excel terlebih dahulu di sini [Bahan Muat Turun].
    A)  Namakan column baru tersebut KATEGORI NEGERI LAHIR.
    B)  Masukkan fungsi IF untuk memulangkan nilai Guru Perak bagi yang lahir di Perak dan Selainnya adalah Guru luar Perak.
  2. Kemudian, bina satu jadual Bilangan Guru perak dan Guru luar Perak dan gunakan fungsi COUNTIF untuk menentukan bilangannya.

Q&ASELAMAT MENCUBA.