Wednesday, January 20, 2021

Kemahiran MS Excel - Fungsi TEXT, VLOOKUP & COUNTIF


Sila klik pada video untuk ikuti rakaman sesi.

Pada kesempatan ini saya berkongsi sedikit kemahiran bagaimana menggunakan Microsoft Excel dalam pengurusan takwim bagi melaksanakan kerja buat saya selaku SIPartners+ (Rakan Peningkatan Sekolah). Perkongsian berkaitan topik ini telah saya laksanakan bersama rakan-rakan SIP+ Negeri Johor dan Malaysia dengan menggunakan platform Google Meet.

Sila klik link  Tamplate Takwim untuk memuat bahan yang digunakan bagi tujuan latihan.

Antara kemahiran MS Excel yang disentuh dalam sesi perbengkelan dalam pengurusan Takwim  tersebut ialah menggunakan fungsi asas MS Excel yang berikut;

-Fungsi TEXT

-Fungsi LOOKUP

-Fungsi COUNTIF

Tiga fungsi asas di atas telah digunakan dalam pengurusan takwim. Oleh itu pada sesi  ini saya sertakan sedikit nota untuk dijadikan sebagai rujukan dalam menggunakan fungsi-fungsi asas MS Excel tersebut dalam kerja buat kita.

a)Fungsi TEXT

Fungsi TEXT membolehkan anda mengubah cara nombor muncul dengan menerapkan pemformatan kepadanya dengan kod format. Ia berguna dalam situasi di mana anda ingin memaparkan nombor dalam format yang lebih mudah dibaca, atau anda ingin menggabungkan nombor dengan teks atau simbol. Berikut sedikit penerangan baekaitan fungsi TEXT.

Dalam bentuk termudah, fungsi TEXT mengatakan:

= TEXT (Nilai yang ingin anda format, "Format kod yang ingin anda terapkan")

=TEXT(Value you want to format, "Format code you want to apply")

Berikut adalah beberapa contoh popular, yang boleh anda salin terus ke Excel untuk bereksperimen sendiri. Perhatikan kod format dalam tanda petik.

FORMULA

KETERANGAN

=TEXT(1234.567,"$#,##0.00")

Mata wang dengan pemisah ribuan dan 2 perpuluhan, seperti $ 1,234.57. Perhatikan bahawa Excel membundarkan nilai menjadi 2 tempat perpuluhan.

=TEXT(TODAY(),"MM/DD/YY")

Tarikh hari ini dalam format MM / DD / YY, seperti 03/14/12

=TEXT(TODAY(),"DDDD")

Hari ini dalam seminggu, seperti hari Isnin

=TEXT(NOW(),"H:MM AM/PM")

Masa sekarang, seperti 13:29

=TEXT(0.285,"0.0%")

Peratusan, seperti 28.5%

=TEXT(4.34 ,"# ?/?")

Pecahan, seperti 4 1/3

=TRIM(TEXT(0.34,"# ?/?"))

Pecahan, seperti 1/3. Perhatikan ini menggunakan fungsi TRIM untuk membuang ruang utama dengan nilai perpuluhan.

=TEXT(12200000,"0.00E+00")

Notasi saintifik, seperti 1.22E + 07


b) Fungsi VLOOKUP

Kita akan gunakan VLOOKUP apabila kita perlu mencari maklumat dalam jadual atau jarak demi baris. Sebagai contoh dalam bengkel ini, kita mencari nama sekolah dalam satu lajur, atau dalam fungsi lain cari nama guru atau kakitangan  berdasarkan ID mereka.

Dalam bentuk termudah, fungsi VLOOKUP mengatakan:

= VLOOKUP (Apa yang anda ingin cari, di mana anda ingin mencarinya, nombor lajur dalam julat yang mengandungi nilai untuk dikembalikan, mengembalikan suatu Perkiraan atau padanan Tepat - ditunjukkan sebagai 1 / BENAR, atau 0 / SALAH).

=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

Sila rujuk video bagimana VLOOKUP digunakan untuk mendapatkan nama hari dalam bahasa Melayu dari sumber Bahasa Inggeris.

Bagaimana untuk memulakan fungsi VLOOKUP

Terdapat empat maklumat yang anda perlukan untuk membina sintaks VLOOKUP:

1) Nilai yang ingin anda cari, juga dipanggil nilai carian.

2) Julat di mana nilai carian berada. Ingat bahawa nilai carian harus selalu berada di lajur pertama dalam julat agar VLOOKUP berfungsi dengan betul. Contohnya, jika nilai carian anda berada dalam sel C2 maka julat anda harus bermula dengan C.

3) Nombor lajur dalam julat yang mengandungi nilai kembali. Contohnya, jika anda menentukan B2: D11 sebagai julat, anda harus mengira B sebagai lajur pertama, C sebagai lajur kedua, dan seterusnya.

4) Sebagai pilihan, anda boleh menentukan BENAR jika anda mahukan padanan anggaran atau SALAH jika anda mahukan padanan tepat dari nilai pengembalian. Sekiranya anda tidak menentukan apa-apa, nilai lalai akan selalu BETUL atau hampir sama.

Ini adalah contoh lain yang diberikan oleh Microsoft;



c) Fungsi COUNTIF

Gunakan COUNTIF, salah satu fungsi statistik, untuk mengira bilangan sel yang memenuhi kriteria; sebagai contoh, untuk mengira berapa kali bandar tertentu muncul dalam senarai pelanggan.

Dalam bentuk termudah, COUNTIF mengatakan:

= COUNTIF (Di mana anda mahu mencari ?, Apa yang anda mahu cari?)

=COUNTIF(Where do you want to look?, What do you want to look for?)

Sebagai contoh:

= COUNTIF (A2: A5, "London")

= COUNTIF (A2: A5, A4)

Contoh;

Untuk menggunakan contoh-contoh ini di Excel, salin data dalam jadual di bawah, dan tampalkannya di sel A1 lembaran kerja baru.

Data

Data

apples

32

oranges

54

peaches

75

apples

86


Berdasarkan data di atas cuba anda lakukan operasi berdasarkan syntaks berikut;

Formula

Description

=COUNTIF(A2:A5,"apples")

Mengira bilangan sel dengan epal dalam sel A2 hingga A5. Hasilnya adalah 2.

=COUNTIF(A2:A5,A4)

Mengira bilangan sel dengan epal dalam sel A2 hingga A5. Hasilnya adalah 2.

=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3)

Mengira bilangan epal (nilai dalam A2), dan jeruk (nilai dalam A3) dalam sel A2 hingga A5. Hasilnya adalah 3. Formula ini menggunakan COUNTIF dua kali untuk menentukan beberapa kriteria, satu kriteria setiap ungkapan. Anda juga boleh menggunakan fungsi COUNTIFS.

=COUNTIF(B2:B5,">55")

Mengira bilangan sel dengan nilai lebih besar daripada 55 dalam sel B2 hingga B5. Hasilnya adalah 2.

=COUNTIF(B2:B5,"<>"&B4)

Mengira bilangan sel dengan nilai tidak sama dengan 75 dalam sel B2 hingga B5. Ampersand (&) menggabungkan operator perbandingan untuk tidak sama dengan (<>) dan nilai dalam B4 dibaca = COUNTIF (B2: B5, "<> 75"). Hasilnya adalah 3.

=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")

Mengira bilangan sel dengan nilai lebih besar daripada (>) atau sama dengan (=) 32 dan kurang daripada (<) atau sama dengan (=) 85 dalam sel B2 hingga B5. Hasilnya adalah 3.

=COUNTIF(A2:A5,"*")

Mengira bilangan sel yang mengandungi sebarang teks dalam sel A2 hingga A5. Tanda bintang (*) digunakan sebagai watak wildcard untuk memadankan watak mana pun. Hasilnya adalah 4.

=COUNTIF(A2:A5,"?????es")

Mengira bilangan sel yang mempunyai tepat 7 aksara, dan diakhiri dengan huruf "es" dalam sel A2 hingga A5. Tanda tanya (?) Digunakan sebagai watak wildcard untuk memadankan watak individu. Hasilnya adalah 2.


Sekian sahaja untuk perkongsian kali ini. semoga sedikit  sebanyak dapat dimanfaatkan.

No comments:

Artikel Popular