Rumusan Excel & Fungsi: Belajar dengan CONTOH Asas

Isi kandungan:

Anonim

Rumus dan fungsi adalah asas membina data berangka di Excel. Artikel ini memperkenalkan anda kepada formula dan fungsi.

Dalam artikel ini, kita akan membahas topik berikut.

  • Apakah Rumus dalam Excel?
  • Kesalahan yang harus dielakkan semasa bekerja dengan formula di Excel
  • Apakah Fungsi di Excel?
  • Kepentingan fungsi
  • Fungsi biasa
  • Fungsi Numerik
  • Fungsi rentetan
  • Fungsi Masa Tarikh
  • Fungsi V Lookup

Data Tutorial

Untuk tutorial ini, kami akan bekerjasama dengan set data berikut.

Belanjawan bekalan rumah

S / N PERKARA QTY HARGA JUMLAH KECIL Adakah Mampu?
1 Mangga 9 600
2 Jeruk 3 1200
3 Tomato 1 2500
4 Minyak masak 5 6500
5 Air tonik 13 3900

Jadual Projek Pembinaan Rumah

S / N PERKARA TARIKH MULA TARIKH TAMAT JANGKA MASA (HARI)
1 Tanah ukur 04/02/2015 07/02/2015
2 Lay Foundation 10/02/2015 15/02/2015
3 Bumbung 27/02/2015 03/03/2015
4 Mengecat 09/03/2015 21/03/2015

Apakah Rumus dalam Excel?

FORMULAS IN EXCEL adalah ungkapan yang beroperasi pada nilai dalam pelbagai alamat sel dan operator. Sebagai contoh, = A1 + A2 + A3, yang mencari jumlah julat nilai dari sel A1 hingga sel A3. Contoh formula yang terdiri daripada nilai diskrit seperti = 6 * 3.

=A2 * D2 / 2

DI SINI,

  • memberitahu Excel bahawa ini adalah formula, dan harus menilai.
  • "A2" * D2" membuat rujukan ke alamat sel A2 dan D2 kemudian mengalikan nilai yang terdapat dalam alamat sel ini.
  • "/" adalah pengendali aritmetik bahagian
  • "2" adalah nilai diskrit

Formula latihan amali

Kami akan bekerjasama dengan data sampel untuk anggaran rumah untuk mengira jumlah keseluruhan.

  • Buat buku kerja baru di Excel
  • Masukkan data yang ditunjukkan dalam anggaran bekalan rumah di atas.
  • Lembaran kerja anda akan kelihatan seperti berikut.

Kami sekarang akan menulis formula yang mengira subtotal

Tetapkan fokus ke sel E4

Masukkan formula berikut.

=C4*D4

DI SINI,

  • "C4*D4" menggunakan pendaraban operator aritmetik (*) untuk menggandakan nilai alamat sel C4 dan D4.

Tekan kekunci enter

Anda akan mendapat hasil berikut

Gambar animasi berikut menunjukkan kepada anda cara memilih alamat sel secara automatik dan menerapkan formula yang sama pada baris lain.

Kesalahan yang harus dielakkan semasa bekerja dengan formula di Excel

  1. Ingatlah peraturan Kurungan Pembahagian, Pendaraban, Penambahan & Pengurangan (BODMAS). Ini bermaksud ungkapan adalah tanda kurung dinilai terlebih dahulu. Bagi pengendali aritmetik, pembahagian dinilai terlebih dahulu diikuti dengan pendaraban kemudian penambahan dan pengurangan adalah yang terakhir untuk dinilai. Dengan menggunakan peraturan ini, kita dapat menulis semula formula di atas sebagai = (A2 * D2) / 2. Ini akan memastikan bahawa A2 dan D2 terlebih dahulu dinilai kemudian dibahagi dua.
  2. Rumus hamparan Excel biasanya berfungsi dengan data berangka; anda boleh memanfaatkan pengesahan data untuk menentukan jenis data yang harus diterima oleh sel iaitu nombor sahaja.
  3. Untuk memastikan bahawa anda bekerja dengan alamat sel yang betul yang dirujuk dalam formula, anda boleh menekan F2 pada papan kekunci. Ini akan menyoroti alamat sel yang digunakan dalam formula, dan anda boleh membuat pemeriksaan silang untuk memastikan alamat tersebut adalah alamat sel yang diinginkan.
  4. Apabila anda bekerja dengan banyak baris, anda boleh menggunakan nombor siri untuk semua baris dan mempunyai jumlah rekod di bahagian bawah helaian. Anda harus membandingkan jumlah nombor siri dengan jumlah rekod untuk memastikan formula anda merangkumi semua baris.

Lihat Formula Hamparan 10 Excel Teratas

Apakah Fungsi di Excel?

FUNGSI DALAM EXCEL adalah formula yang telah ditetapkan yang digunakan untuk nilai-nilai tertentu dalam urutan tertentu. Fungsi digunakan untuk tugas cepat seperti mencari jumlah, kiraan, rata-rata, nilai maksimum, dan nilai minimum untuk berbagai sel. Sebagai contoh, sel A3 di bawah mengandungi fungsi SUM yang mengira jumlah julat A1: A2.

  • SUM untuk penjumlahan julat nombor
  • RATA - RATA untuk mengira purata julat nombor tertentu
  • COUNT untuk mengira bilangan item dalam julat tertentu

Kepentingan fungsi

Fungsi meningkatkan produktiviti pengguna ketika bekerja dengan excel . Katakan anda ingin mendapatkan jumlah keseluruhan untuk anggaran keperluan rumah di atas. Untuk menjadikannya lebih mudah, anda boleh menggunakan formula untuk mendapatkan jumlah keseluruhan. Dengan menggunakan formula, anda perlu merujuk sel E4 hingga E8 satu persatu. Anda mesti menggunakan formula berikut.

= E4 + E5 + E6 + E7 + E8

Dengan fungsi, anda akan menulis formula di atas sebagai

=SUM (E4:E8)

Seperti yang anda lihat dari fungsi di atas yang digunakan untuk mendapatkan jumlah rentang sel, jauh lebih efisien menggunakan fungsi untuk mendapatkan jumlahnya daripada menggunakan rumus yang harus merujuk banyak sel.

Fungsi biasa

Mari lihat beberapa fungsi yang paling biasa digunakan dalam formula ms excel. Kami akan bermula dengan fungsi statistik.

S / N FUNGSI KATEGORI PENERANGAN PENGGUNAAN
01 RUMUSAN Math & Trig Menambah semua nilai dalam julat sel = SUM (E4: E8)
02 MIN Statistik Mencari nilai minimum dalam julat sel = MIN (E4: E8)
03 MAX Statistik Mencari nilai maksimum dalam julat sel = MAX (E4: E8)
04 PURATA Statistik Mengira nilai purata dalam julat sel = RATA-RATA (E4: E8)
05 BANYAK Statistik Mengira bilangan sel dalam julat sel = COUNT (E4: E8)
06 LEN Teks Mengembalikan bilangan watak dalam teks rentetan = LEN (B7)
07 SUMIF Math & Trig Menambah semua nilai dalam julat sel yang memenuhi kriteria yang ditentukan. = SUMIF (julat, kriteria, [rentang_jumlah]) = SUMIF (D4: D8, "> = 1000", C4: C8)
08 AVERAGEIF Statistik Mengira nilai purata dalam julat sel yang memenuhi kriteria yang ditentukan. = AVERAGEIF (julat, kriteria, [purata_rentang]) = AVERAGEIF (F4: F8, "Ya", E4: E8)
09 HARI Masa tarikh Mengembalikan bilangan hari antara dua tarikh = HARI (D4, C4)
10 SEKARANG Masa tarikh Mengembalikan tarikh dan masa sistem semasa = SEKARANG ()

Fungsi Numerik

Seperti namanya, fungsi ini beroperasi pada data berangka. Jadual berikut menunjukkan beberapa fungsi angka biasa.

S / N FUNGSI KATEGORI PENERANGAN PENGGUNAAN
1 ISNUMBER Maklumat Mengembalikan Benar jika nilai yang dibekalkan adalah berangka dan Salah jika tidak berangka = ISNUMBER (A3)
2 RAND Math & Trig Menghasilkan nombor rawak antara 0 dan 1 = RAND ()
3 BULAN Math & Trig Membundarkan nilai perpuluhan ke bilangan titik perpuluhan yang ditentukan = ROUND (3.14455,2)
4 PERUBATAN Statistik Mengembalikan nombor di tengah-tengah set nombor yang diberi = MEDIAN (3,4,5,2,5)
5 PI Math & Trig Mengembalikan nilai Math Function PI (π) = PI ()
6 KUASA Math & Trig Mengembalikan hasil nombor yang dinaikkan menjadi kuasa. KUASA (bilangan, kuasa) = KUASA (2,4)
7 MOD Math & Trig Mengembalikan Sisa apabila anda membahagi dua nombor = MOD (10,3)
8 ROMAN Math & Trig Menukar nombor menjadi angka rumi = ROMAN (1984)

Fungsi rentetan

Fungsi asas excel ini digunakan untuk memanipulasi data teks. Jadual berikut menunjukkan beberapa fungsi rentetan biasa.

S / N FUNGSI KATEGORI PENERANGAN PENGGUNAAN KOMEN
1 MENINGGALKAN Teks Mengembalikan sebilangan aksara yang ditentukan dari permulaan (sebelah kiri) rentetan = KIRI ("GURU99", 4) Kiri 4 Watak "GURU99"
2 BETUL Teks Mengembalikan sebilangan watak yang ditentukan dari hujung (sebelah kanan) rentetan = KANAN ("GURU99", 2) Kanan 2 Karakter "GURU99"
3 MID Teks Mengambil sebilangan aksara dari tengah rentetan dari kedudukan dan panjang permulaan yang ditentukan. = MID (teks, angka_mula, bilangan_bilangan) = MID ("GURU99", 2,3) Mengambil Karakter 2 hingga 5
4 ISTEKS Maklumat Mengembalikan Benar jika parameter yang disediakan adalah Teks = ISTEXT (nilai) nilai - Nilai untuk diperiksa.
5 CARI Teks Mengembalikan kedudukan permulaan rentetan teks dalam rentetan teks yang lain. Fungsi ini peka huruf besar kecil. = MENCARI (cari_teks, dalam_teks, [mula_num]) = MENCARI ("oo", "Bumbung", 1) Cari oo di "Roofing", Hasilnya adalah 2
6 GANTI Teks Menggantikan bahagian rentetan dengan rentetan yang ditentukan. = GANTI (lama_teks, permulaan_num, bilangan_karkib, teks_baru) = GANTI ("Atap", 2,2, "xx") Gantikan "oo" dengan "xx"

Fungsi Masa Tarikh

Fungsi-fungsi ini digunakan untuk memanipulasi nilai tarikh. Jadual berikut menunjukkan beberapa fungsi tarikh biasa

S / N FUNGSI KATEGORI PENERANGAN PENGGUNAAN
1 TARIKH Masa tarikh Mengembalikan nombor yang mewakili tarikh dalam kod excel = TARIKH (2015,2,4)
2 HARI Masa tarikh Cari bilangan hari antara dua tarikh = HARI (D6, C6)
3 SEBULAN Masa tarikh Mengembalikan bulan dari nilai tarikh = BULAN ("4/2/2015")
4 MINIT Masa tarikh Mengembalikan minit dari nilai masa = MINIT ("12:31")
5 TAHUN Masa tarikh Mengembalikan tahun dari nilai tarikh = TAHUN ("04/02/2015")

Fungsi VLOOKUP

Fungsi VLOOKUP digunakan untuk melakukan tampilan menegak di lajur paling kiri dan mengembalikan nilai pada baris yang sama dari lajur yang anda tentukan. Mari kita jelaskan ini dalam bahasa orang awam. Anggaran bekalan rumah mempunyai lajur nombor siri yang mengenal pasti setiap item dalam anggaran secara unik. Katakan anda mempunyai nombor siri item, dan anda ingin mengetahui keterangan item, anda boleh menggunakan fungsi VLOOKUP. Inilah cara fungsi VLOOKUP berfungsi.

=VLOOKUP (C12, A4:B8, 2, FALSE)

DI SINI,

  • "=VLOOKUP" memanggil fungsi carian menegak
  • "C12" menentukan nilai yang akan dicari di lajur paling kiri
  • "A4:B8" menentukan susunan jadual dengan data
  • "2" menentukan nombor lajur dengan nilai baris yang akan dikembalikan oleh fungsi VLOOKUP
  • "FALSE," memberitahu fungsi VLOOKUP bahawa kami mencari padanan tepat dengan nilai carian yang disediakan

Gambar animasi di bawah menunjukkan ini dalam tindakan

Muat turun Kod Excel di atas

Ringkasan

Excel membolehkan anda memanipulasi data menggunakan formula dan / atau fungsi. Fungsi umumnya lebih produktif berbanding formula penulisan. Fungsi juga lebih tepat dibandingkan dengan formula kerana margin kesalahan membuat sangat minimum.

Berikut adalah senarai Formula dan Fungsi Excel yang penting

  • Fungsi SUM = =SUM(E4:E8)
  • Fungsi MIN = =MIN(E4:E8)
  • Fungsi MAX = =MAX(E4:E8)
  • Fungsi PURATA = =AVERAGE(E4:E8)
  • COUNT fungsi = =COUNT(E4:E8)
  • Fungsi HARI = =DAYS(D4,C4)
  • Fungsi VLOOKUP = =VLOOKUP (C12, A4:B8, 2, FALSE)
  • Fungsi TARIKH = =DATE(2020,2,4)