Helaian Penipuan Formula Excel: Panduan Perantaraan

Jadi anda telah berjaya menguasai asas Excel – pujian kepada anda! Sekarang, anda telah memutuskan untuk memulakan sesuatu dan menyelami perkara yang lebih maju, bukan? Anda bernasib baik kerana kami telah mengumpulkan helaian panduan Excel pertengahan yang berguna untuk anda!

Helaian panduan perantaraan Microsoft Excel ini merangkumi pelbagai topik yang akan meningkatkan kemahiran anda dalam analisis berangka, pemprosesan teks dan logik tarikh dan masa yang kompleks.

Dengan menjadikan Helaian Penipuan Pertengahan Excel sebagai rujukan desktop, anda boleh menyelami formula dan fungsi Microsoft Excel yang lebih berkuasa.

Sekarang, pastikan anda terus membaca, tetapi juga cetak dan simpan helaian cheat di bawah!

Mari kita mulakan!

Excel-formulas-cheat-sheet-intermediate-1 Muat turun

Isi kandungan

• Formula Matematik Pertengahan
  • Logik Bersyarat
• Formula Perangkaan Pertengahan
  • 1. MINA dan MAXA
  • 2. COUNTA dan COUNTIF
• Formula Excel untuk Analisis Kewangan
  • 1. Fungsi PRODUK
  • 2. QUOTIENT dan Fungsi LOG
  • Fungsi untuk Senario Pelaburan
• Formula Tarikh dan Masa Excel Pertengahan
  • 1. Fungsi Bulan Pertengahan
  • 2. Fungsi Minggu Pertengahan
  • 3. Fungsi Masa Perantaraan
• Fungsi Excel untuk Algebra Bersyarat dan Logik
  • 1. Fungsi JIKA
  • 2. DAN Fungsi
  • 3. ATAU Fungsi
  • 4. BUKAN Fungsi
• Sel Rujukan
  • 1. Fungsi TIDAK LANGSUNG
  • 2. Fungsi INDEKS
  • 3. Fungsi OFFSET
• Formula Teks Perantaraan
  • 1. Fungsi TEXTJOIN
  • 2. GANTI Fungsi
  • 3. Fungsi PENGGANTI
• Formula Carian Pertengahan
• Formula untuk Mengendalikan Ralat Microsoft Excel
  • 1. Fungsi IFERROR
  • 2. Fungsi IFNA
• Formula dan Pintasan Fungsi
• Pemformatan Sel Pertengahan
• Fikiran Akhir

Formula Matematik Pertengahan

Helaian cheat pemula kami merangkumi fungsi matematik pemula yang paling biasa seperti fungsi SUM. Pengguna pertengahan harus membiasakan diri dengan fungsi yang lebih kompleks ini:

• ABS() mengembalikan nilai mutlak sesuatu nombor.

• SQRT() mengembalikan punca kuasa dua nombor

• RAND() menyediakan nombor rawak antara 0 dan 1

Logik Bersyarat

Pengguna pertengahan juga harus tahu cara melakukan pengiraan matematik berdasarkan berbilang syarat menggunakan fungsi ini:

• SUMIFS(julat_jumlah, julat_kriteria1, kriteria1, [….])

• COUNTIFS(julat_kriteria1, kriteria1, [julat_kriteria2, kriteria2], …)

• AVERAGEIFS(julat_kriteria1, kriteria1, [julat_kriteria2, kriteria2], …)

Fungsi menilai kriteria yang disediakan dan menghasilkan nilai logik BENAR atau SALAH.

Katakan anda mempunyai data jualan berikut dalam lajur A, B dan C:

Untuk mengira jumlah jualan bagi t-shirt merah, gunakan formula SUMIFS:

=SUMIFS(C2:C6, A2:A6, “T-Shirt”, B2:B6, “Merah”)

Untuk mengira bilangan baris dengan t-shirt merah, gunakan formula COUNTIFS:

=COUNTIFS(C2:C6, A2:A6, “T-Shirt”, B2:B6, “Merah”)

Untuk mengira purata jualan bagi t-shirt merah, gunakan formula AVERAGEIFS:

=AVERAGEIFS(C2:C6, A2:A6, “T-Shirt”, B2:B6, “Merah”)

Formula Perangkaan Pertengahan

Selain daripada fungsi asas seperti MIN dan MAX, Excel mempunyai rangkaian formula statistik yang lebih luas untuk pengguna pertengahan. Berikut adalah beberapa yang paling berguna:

• MINA

• MAXA

• COUNTA

• COUNTIF

1. MINA dan MAXA

Fungsi MIN dan MAX yang lebih biasa digunakan mengabaikan nilai teks.

Fungsi MINA dan MAXA alternatif mempertimbangkan kedua-dua teks dan nombor apabila mencari nilai tertinggi atau terendah. Nilai teks dinilai seolah-olah ia adalah sifar.

Perbezaan ditunjukkan dalam gambar di bawah. Baris pertama menilai nilai minimum 10, manakala baris kedua menilai sebagai 0 kerana kehadiran nilai teks.

2. COUNTA dan COUNTIF

Fungsi COUNTA digunakan untuk mengira bilangan sel yang tidak kosong dalam julat.

Fungsi COUNTIF kurang spesifik kerana digunakan untuk mengira bilangan sel dalam julat yang memenuhi syarat atau kriteria tertentu.

Anda juga boleh menggunakan fungsi ini untuk mengira bilangan nilai yang berbeza dalam lajur. Video ini menunjukkan cara melakukannya.

Formula Excel untuk Analisis Kewangan

Terdapat beberapa formula yang anda ingin gunakan semasa melakukan analisis kewangan seperti ramalan pelaburan:

• PRODUK(nombor1, [nombor2…])

• QUOTIENT(penbilang, penyebut)

• LOG(nombor, [asas])

1. Fungsi PRODUK

Katakan anda ingin mengira nilai masa depan pelaburan awal sebanyak $1,000 (sel B1) pada kadar faedah tahunan 4% (sel B2) selepas lima tahun (sel B3).

Gunakan fungsi PRODUCT untuk mengira nilai masa hadapan dengan formula ini:

=B1 * PRODUK(1 + B2)^B3

Formula ini akan mengembalikan nilai masa depan pelaburan anda.

2. QUOTIENT dan Fungsi LOG

Langkah seterusnya ialah mengira berapa tahun yang diperlukan untuk pelaburan meningkat dua kali ganda dalam nilai pada kadar faedah yang diberikan.

Gunakan fungsi QUOTIENT dalam kombinasi dengan fungsi LOG untuk mengira ini seperti berikut:

=QUOTIENT(LOG(2) / LOG(1 + B2), 1)

Formula ini akan mengembalikan 17 dengan data sampel, menunjukkan bahawa pelaburan akan mengambil masa 17 tahun untuk menggandakan nilai pada kadar faedah tahunan 4%.

Gambar ini menunjukkan formula dalam lembaran kerja Excel :

Fungsi untuk Senario Pelaburan

Ini adalah beberapa fungsi yang sesuai untuk senario kewangan tertentu. Salah satu daripadanya mungkin betul-betul apa yang anda perlukan:

• NPV – Fungsi NPV (Nilai Kini Bersih) akan mengira nilai semasa bersih pelaburan berdasarkan siri aliran tunai masa hadapan berdasarkan kadar diskaun.

• ACCRINT – Fungsi ACCRINT mengira faedah terakru sekuriti yang membayar faedah berkala. Ini berguna untuk menentukan faedah yang diperoleh ke atas sekuriti sejak tarikh pembayaran terakhir sehingga tarikh penyelesaian tertentu.

• INTRATE – Fungsi INTRATE mengira kadar faedah untuk sekuriti yang dilaburkan sepenuhnya.

• PMT – Fungsi PMT mengira jumlah pembayaran bagi sekuriti hutang.

• IRR – Fungsi IRR menyediakan kadar pulangan dalaman.

• HASIL – Fungsi YIELD menyediakan hasil sekuriti berdasarkan kadar faedah, nilai muka dan kematangan.

Formula Tarikh dan Masa Excel Pertengahan

Fungsi tarikh dan masa asas dalam Excel termasuk fungsi NOW dan TODAY untuk tarikh semasa. Pengguna pertengahan juga harus tahu cara mengekstrak komponen dari tarikh tertentu menggunakan:

• DAY(tarikh)

• BULAN(tarikh)

• YEAR(tarikh)

Formula =MONTH(“23 April 2023″) akan mengembalikan hasil 4 untuk bulan ke-4. Begitu juga, fungsi DAY dan YEAR masing-masing akan mengembalikan 23 dan 2023.

1. Fungsi Bulan Pertengahan

Pengguna pertengahan kadangkala akan berurusan dengan menambah atau menolak bulan ke tarikh dan mencari penghujung bulan.

• EDATE(tarikh_mula, bilangan_bulan)

• EOMONTH(tarikh_mula, bilangan_bulan)

Contohnya, formula =EDATE(“23 April 2023”, 2) akan mengira tarikh dua bulan kemudian.

Formula =EOMONTH(23 April 2023”, 2) mengira akhir bulan dua bulan kemudian.

Hasilnya ialah "30 Jun 2023", yang mengambil kira bahawa terdapat hanya tiga puluh hari pada bulan Jun. Jika anda menyatakan 3 bulan, keputusannya ialah "31 Julai 2023".

2. Fungsi Minggu Pertengahan

Anda boleh menggunakan gabungan fungsi SUM dan WEEKDAY untuk mengira bilangan keseluruhan hari kerja dalam julat tarikh.

Fungsi WEEKDAY mengembalikan hari dalam seminggu untuk tarikh tertentu. Parameter kedua menentukan sistem penomboran dengan lalai ialah Ahad hingga Sabtu.

Jika anda mempunyai siri tarikh dalam julat sel B1:B40, gunakan formula ini untuk mengira hari kerja:

=JUMLAH(–(HARI MINGGU(B1:B40,2)>5))

3. Fungsi Masa Perantaraan

Fungsi YEARFRAC mengira pecahan setahun antara dua tarikh.

Fungsi TIMEVALUE digunakan untuk menukar masa yang diwakili sebagai teks kepada nombor perpuluhan yang mewakili perkadaran hari 24 jam.

Hasilnya ialah nombor siri Excel dengan 1 mewakili hari penuh 24 jam, 0.5 mewakili 12 jam, 0.25 mewakili 6 jam dan seterusnya.

Katakan anda mempunyai masa dalam format teks "17:45" dalam sel A1 dan anda mahu menukarnya kepada nombor perpuluhan . Ini adalah formulanya:

=TIMEVALUE(A1)

Nilai 0.74 mewakili perkadaran hari 24 jam yang telah berlalu pada masa lima belas minit sebelum pukul enam.

Ambil ke untuk menukar sel masa daripada format masa kepada format teks. Anda mungkin mahu memformat ini sebagai peratusan untuk menjadi lebih jelas.

Fungsi Excel untuk Algebra Bersyarat dan Logik

Fungsi bersyarat dan logik adalah penting untuk membuat keputusan dalam Excel. Fungsi berikut boleh digabungkan dengan formula Excel yang lebih asas untuk logik yang berkuasa:

1. IF(condition, value_if_true, value_if_false):

2. DAN(syarat1, syarat2, …)

3. ATAU(syarat1, syarat2, …)

4. BUKAN(syarat1, syarat2, …)

Fungsi IF menilai keadaan dan mengembalikan nilai yang berbeza bergantung pada sama ada keadaan itu benar atau palsu.

Fungsi AND, OR, dan NOT menyemak syarat yang benar dan membolehkan anda membuat keputusan dengan sewajarnya.

Berikut adalah beberapa contoh berdasarkan data jualan yang kami gunakan sebelum ini.

1. Fungsi JIKA

Katakan anda ingin memaparkan teks "Jualan Rendah" jika jualan untuk item kurang daripada 15. Jika jualan lebih tinggi, anda mahu memaparkan "Jualan Tinggi" dalam lajur baharu.

Gunakan formula ini dan salin ke baris lain:

=IF(C2<15, “Jualan Rendah”, “Jualan Tinggi”)

2. DAN Fungsi

Katakan anda ingin memaparkan BETUL atau SALAH untuk t-shirt dengan sekurang-kurangnya 15 jualan. Gunakan formula ini:

=DAN(A3=”T-Shirt”, C3>=15)

3. ATAU Fungsi

Katakan anda ingin menguji berbilang syarat dan mengembalikan TRUE jika mana-mana syarat dipenuhi dan FALSE sebaliknya, maka anda akan menggunakan fungsi OR.

Sebagai contoh, jika kita mempunyai markah ujian dalam sel A1 dan B1, dan kita ingin tahu sama ada skor sama ada melebihi 80, kita boleh menggunakan: =OR (A1>80, B1>80) . Ini akan mengembalikan BENAR jika salah satu skor (atau kedua-duanya) melebihi 80 dan SALAH jika kedua-duanya adalah 80 atau kurang.

4. BUKAN Fungsi

Katakan anda ingin menyemak sama ada item itu bukan hoodie. Gunakan formula ini:

=NOT(A2=”Hoodie”)

Sel Rujukan

Seorang pemula akan menjadi biasa menggunakan rujukan mutlak dan relatif seperti $A$1 atau B2. Pengguna pertengahan harus membiasakan diri menggunakan rujukan tidak langsung, indeks dan offset:

1. INDIRECT(ref_text)

2. INDEX(julat, nombor_baris, nombor_lajur)

3. OFFSET(reference_cell, baris, lajur):

1. Fungsi TIDAK LANGSUNG

Fungsi INDIRECT mengembalikan nilai rujukan sel yang ditentukan yang dimasukkan sebagai teks. Untuk merujuk sel B3, gunakan formula ini:

=TIDAK LANGSUNG(“B3”)

Kelebihan menggunakan rujukan sel mudah ialah fungsi ini memberi anda rujukan dinamik. Ini bermakna Excel mengemas kini rujukan secara automatik apabila struktur hamparan berubah contohnya baris dipadamkan.

2. Fungsi INDEKS

Fungsi INDEX digunakan untuk merujuk sel dalam julat tertentu berdasarkan nombor baris dan lajur.

Untuk merujuk sel B3 dalam jadual enam baris, gunakan formula ini:

=INDEX(B1:B6, 3)

3. Fungsi OFFSET

Fungsi OFFSET mengembalikan sel atau julat yang merupakan bilangan baris dan lajur yang ditentukan dari sel rujukan.

Jika anda ingin memaparkan nilai sel yang merentasi dua sel dan satu sel turun dari A1, gunakan formula ini:

=OFFSET(A1, 2, 1)

Berikut adalah formula dalam tindakan:

Formula Teks Perantaraan

Pemula harus mengetahui fungsi teks seperti LEFT, yang mengekstrak satu atau lebih aksara dari sebelah kiri rentetan. Pengguna kuasa harus biasa dengan fungsi seperti ini:

1. TEXTJOIN(pembatas, abaikan_kosong, teks1, [teks2, …])

2. REPLACE(teks_lama, angka_mula, angka_huruf, teks_baru)

3. GANTI(teks, teks_lama, teks_baru, [bilangan_contoh])

1. Fungsi TEXTJOIN

Fungsi TEXTJOIN menggabungkan sel dengan pembatas yang anda sediakan. Anda juga boleh menentukan tempat untuk mengabaikan sel kosong.

Untuk menghasilkan senarai dipisahkan koma bagi beberapa item dalam lajur A, gunakan formula ini:

=TEXTJOIN(“, “, BENAR, A2:A4)

2. GANTI Fungsi

Fungsi ganti membolehkan anda menentukan kedudukan permulaan dan panjang rentetan yang anda mahu tukar ke sasaran.

Untuk menggantikan dua aksara pertama rentetan dengan "XX", gunakan formula ini:

=TEXTJOIN(A2, 1, 2, “XX”)

3. Fungsi PENGGANTI

Fungsi pengganti menggantikan kejadian tertentu bagi rentetan teks dalam rentetan teks lain dengan rentetan teks baharu.

Untuk menggantikan perkataan "Jeans" dengan "Legging", gunakan formula ini:

=SUBSTITUTE(A3, “Jeans”, “Leggings”)

Berikut adalah formula dalam tindakan:

Formula Carian Pertengahan

Fungsi carian biasa seperti VLOOKUP dan MATCH diliputi dalam helaian cheat pemula.

Pengguna pertengahan harus membiasakan diri dengan fungsi carian CHOOSE:

• PILIH(nombor_indeks, nilai1, [nilai2, …])

Fungsi CHOOSE mengembalikan nilai daripada senarai nilai berdasarkan nombor indeks yang ditentukan.

Katakan anda mempunyai lajur saiz t-shirt yang dilabelkan sebagai 1, 2 atau 3. Anda mahu memaparkan kategori kecil, sederhana dan besar masing-masing dalam baris yang sama. Gunakan formula ini:

=PILIH(A1, “Kecil”, “Sederhana”, “Besar”)

Formula untuk Mengendalikan Ralat Microsoft Excel

Lembaran curang pemula menyenaraikan mesej ralat biasa yang anda akan lihat apabila bekerja dengan Excel. Pengguna pertengahan seharusnya boleh menggunakan formula untuk menangani ralat dengan anggun.

1. IFERROR(nilai, if_error)

2. IFNA(nilai, nilai_jika_na)

1. Fungsi IFERROR

Ralat pembahagian dengan sifar adalah perkara biasa tetapi paparan lalai Excel bagi #DIV/0! tidak akan jelas kepada setiap pengguna.

Gunakan fungsi IFERROR untuk menggantikan ralat standard dengan "Tidak boleh bahagi dengan sifar" dengan formula ini:

=IFERROR(A1/B1, “Tidak boleh bahagi dengan sifar”)

Gambar ini menunjukkan ralat mentah dalam baris ketiga dan fungsi IFERROR mengendalikan baris keempat.

2. Fungsi IFNA

Fungsi IFNA dalam Excel digunakan untuk menangkap dan mengendalikan ralat #N/A.

Bahagian lain dalam helaian cheat ini menunjukkan fungsi CHOOSE. Fungsi ini akan mengembalikan ralat N/A jika nombor indeks bukan integer.

Katakan saiz t-shirt telah dimasukkan sebagai 1.5, dengan pengguna tersilap berfikir bahawa ini akan menunjukkan kategori di antara kecil dan sederhana. Sebaliknya, ia akan menunjukkan ralat #N/A .

Untuk memberikan ralat yang lebih berguna, gunakan formula ini:

=IFNA(PILIH(A2, “Rendah”, “Sederhana”, “Tinggi”), “Indeks tidak sah”)

Formula dan Pintasan Fungsi

Terdapat beberapa pintasan papan kekunci yang boleh membuatkan anda bekerja dengan lebih cekap dengan lembaran kerja anda.

• F2 : Edit sel aktif dan letakkan titik sisipan di hujung kandungan sel.

• F9 : Kira dan paparkan hasil bahagian formula yang dipilih.

• Ctrl + Shift + Enter : Masukkan formula tatasusunan.

• Shift + F3 : Buka kotak dialog Insert Function.

• Esc : Batalkan kemasukan formula dan kembali kepada kandungan sel asal.

Beberapa pintasan Excel ini mungkin tidak tersedia dalam semua bahasa atau susun atur papan kekunci.

Pemformatan Sel Pertengahan

Dalam analisis kewangan, pemformatan yang betul untuk sel yang mengandungi nombor, tarikh dan mata wang adalah penting. Untuk memformat sel, ikuti langkah berikut:

1. Pilih sel yang ingin anda formatkan.

2. Klik kanan pada sel yang dipilih dan pilih "Format Sel."

3. Pilih kategori yang sesuai (cth, Nombor, Mata Wang, Tarikh) dan gunakan format yang diingini.

Beberapa jenis pemformatan biasa yang perlu dipertimbangkan dalam kewangan termasuk:

• Mata Wang : $1,234.56

• Peratusan : 12.34%

• Perakaunan : ($1,234.00)

• Tarikh : 10-Mei-2023

Fikiran Akhir

Apabila anda semakin selesa dengan pelbagai formula dan fungsi yang diserlahkan dalam helaian panduan ini , anda akan mendapati diri anda lebih bersedia untuk mengendalikan tugas dan projek yang semakin kompleks.

Latihan adalah kunci untuk menguasai sebarang kemahiran, jadi jangan takut untuk mencuba formula ini dalam kerja seharian anda. Mereka akan membawa kepakaran anda dari peringkat pemula hingga peringkat pertengahan.

Ini hanyalah gambaran tentang fungsi perantaraan Excel yang boleh lakukan untuk memudahkan kerja anda, menyelaraskan analisis anda dan membantu anda menemui cerapan daripada data anda.

Keajaiban sebenar berlaku apabila anda mula menggabungkan fungsi dalam helaian panduan kami dan menyesuaikannya mengikut keperluan unik anda. Jadi, jangan berhenti di sini. Teruskan meneroka, teruskan percubaan dan anda akan mendapati bahawa Excel bukan sekadar alat, ia adalah pengubah permainan!

Rasa seperti anda ingin membawa sesuatu ke peringkat seterusnya? Semak Helaian Cheat Formula Lanjutan Excel kami.