Senarai 10 Rumusan & Fungsi Excel Teratas
# 1 - Formula VLOOKUP dalam Excel
Fungsi excel maju ini adalah salah satu formula yang paling banyak digunakan dalam excel. Ini terutama disebabkan oleh kesederhanaan formula ini dan penerapannya dalam mencari nilai tertentu dari jadual lain, yang mempunyai satu pemboleh ubah umum di seluruh jadual ini. Katakan anda mempunyai dua jadual dengan perincian mengenai gaji dan nama Pekerja syarikat dengan ID Pekerja sebagai lajur utama. Anda ingin mendapatkan gaji dari Jadual B di Jadual A.
Anda boleh menggunakan VLOOKUP seperti di bawah.
Ini akan menghasilkan jadual di bawah apabila kita menerapkan formula excel lanjutan ini di sel lain dari ruangan Gaji Pekerja.
Seret Formula ke Sel selebihnya.
Terdapat tiga pembatasan utama VLOOKUP:
- Anda tidak boleh mempunyai lajur utama di sebelah kanan lajur yang mana anda ingin mengisi nilai dari jadual lain. Dalam kes ini, lajur Gaji Pekerja tidak boleh sebelum ID Pekerja.
- Dalam nilai pendua di lajur utama dalam Jadual B, nilai pertama akan dihuni dalam sel.
- Sekiranya anda memasukkan lajur baru dalam pangkalan data (Contoh Masukkan lajur baru sebelum Gaji Karyawan dalam Jadual B), output formula mungkin berbeza berdasarkan posisi yang telah anda sebutkan dalam formula (Dalam kes di atas, hasilnya akan kosong)
# 2 - Formula INDEX dalam Excel
Formula excel lanjutan ini digunakan untuk mendapatkan nilai sel dalam jadual tertentu dengan menentukan jumlah baris, lajur, atau keduanya. Cth. Untuk mendapatkan nama pegawai pada pemerhatian ke- 5 , Berikut adalah data.
Kita boleh menggunakan formula excel lanjutan seperti di bawah:
Formula INDEX yang sama dapat digunakan untuk mendapatkan nilai sepanjang baris. Apabila menggunakan nombor baris dan lajur, sintaks akan kelihatan seperti ini:
Formula di atas akan mengembalikan "Rajesh Ved."
Nota: Jika anda memasukkan baris lain ke dalam 5 th berturut-turut, formula itu akan kembali "Chandan Kale." Oleh itu, output akan bergantung pada perubahan yang berlaku dalam jadual data dari masa ke masa.
# 3 - Formula PERTANDINGAN di Excel
Formula lanjutan Excel ini mengembalikan nombor baris atau lajur apabila terdapat rentetan atau nombor tertentu dalam julat yang diberikan. Dalam contoh di bawah ini, kami berusaha mencari "Rajesh Ved" di ruangan Nama Pekerja.
Formula adalah seperti yang diberikan di bawah ini:
Fungsi MATCH akan mengembalikan 5 sebagai nilai.
3 rd hujah digunakan untuk padanan tepat. Anda juga boleh menggunakan +1 dan -1 berdasarkan keperluan anda.
Catatan: Seseorang boleh menggabungkan INDEX dan MATCH untuk mengatasi batasan VLOOKUP.
# 4 - JIKA DAN Rumus dalam Excel
Terdapat banyak keadaan ketika seseorang perlu membuat bendera berdasarkan beberapa kekangan. Kita semua sudah biasa dengan sintaks asas IF. Kami menggunakan fungsi IF excel lanjutan ini untuk membuat bidang baru berdasarkan beberapa kekangan pada bidang yang ada. Tetapi bagaimana jika kita perlu mempertimbangkan beberapa lajur semasa membuat bendera. Cth. Dalam kes di bawah, kami ingin menandakan semua pekerja yang gajinya lebih besar daripada 50K tetapi ID pekerja lebih besar daripada 3.
Kami akan menggunakan JIKA DAN dalam kes seperti itu. Sila cari di bawah tangkapan skrin untuk perkara yang sama.
Ia akan mengembalikan hasilnya sebagai 0.
Kita boleh mempunyai banyak syarat atau kekangan untuk membuat bendera berdasarkan beberapa lajur menggunakan AND.
# 5 - JIKA ATAU Formula dalam Excel
Begitu juga, kita juga boleh menggunakan fungsi OR di excel dan bukannya AND jika kita perlu memenuhi salah satu syarat banyak.
Dalam kes-kes di atas, jika salah satu syarat dipenuhi, kita akan mempunyai jumlah sel sebagai 1 yang lain 0. Kita juga boleh mengganti 1 atau 0 dengan beberapa substring dengan tanda petik ganda (“”).
# 6 - Formula SUMIF dalam Excel
Dalam beberapa analisis, anda mungkin perlu menyaring beberapa pemerhatian ketika menerapkan fungsi jumlah atau hitungan. Dalam kes seperti ini, fungsi SUMIF excel canggih dalam excel berada di tangan kita. Ia menyaring semua pemerhatian berdasarkan syarat-syarat tertentu yang diberikan dalam formula excel lanjutan ini dan meringkaskannya. Cth. Bagaimana jika kita ingin mengetahui jumlah gaji hanya pekerja yang mempunyai ID pekerja lebih dari 3.
Dengan Menerapkan Formula SUMIFS:
Formula mengembalikan hasilnya sebagai 322000.
Kita juga dapat mengira bilangan pekerja dalam organisasi yang mempunyai ID pekerja lebih besar daripada 3 ketika menggunakan COUNTIF dan bukannya SUMIF.
# 7 - CONCATENATE Formula dalam Excel
Fungsi maju excel ini adalah salah satu formula yang dapat digunakan dengan pelbagai varian. Formula excel lanjutan ini membantu kami menggabungkan beberapa rentetan teks ke dalam satu rentetan teks. Cth, Sekiranya kita ingin menunjukkan ID pekerja dan Nama Pekerja dalam satu lajur.
Kita boleh menggunakan formula CONCATENATE ini untuk melakukannya.
Formula di atas akan menghasilkan "1Aman Gupta".
Kita boleh mempunyai satu varian lagi dengan meletakkan tanda hubung tunggal antara ID dan NAME. Cth. CONCATENATE (B3, "-", C3) akan menghasilkan "1-Aman Gupta". Kita juga boleh menggunakannya ke dalam VLOOKUP apabila LOOKUP dalam nilai excel adalah campuran lebih dari satu pemboleh ubah.
# 8 - Formula KIRI, MID, dan KANAN di Excel
Kita dapat menggunakan formula excel maju ini untuk mengekstrak substring tertentu dari rentetan yang diberikan. Itu boleh digunakan berdasarkan keperluan kami. Cth. Sekiranya kita ingin mengekstrak 5 aksara pertama dari Nama Karyawan, kita dapat menggunakan formula KIRI di excel dengan nama lajur dan parameter kedua sebagai 5.
Hasilnya diberikan di bawah:
Penerapan formula KANAN dalam excel juga sama. Cuma kita akan melihat watak dari sebelah kanan rentetan. Walau bagaimanapun, sekiranya fungsi MID dalam excel, kita mesti memberikan kedudukan permulaan rentetan teks yang diperlukan dan panjang tali.
# 9 - Formula OFFSET dalam Excel
Fungsi excel maju ini dengan kombinasi fungsi lain seperti SUM atau AVERAGE, dapat memberikan sentuhan dinamis pada perhitungan. Ia paling baik digunakan pada saat kita memasukkan baris berterusan ke dalam pangkalan data yang ada. OFFSET Excel memberi kita pelbagai di mana kita perlu menyebutkan sel rujukan, bilangan baris, dan lajur. Cth. Sekiranya kita ingin mengira purata 5 pekerja pertama di syarikat di mana kita mempunyai gaji pekerja yang disusun mengikut ID pekerja, kita boleh melakukan perkara berikut. Pengiraan di bawah akan selalu memberi kita gaji.
- Ini akan memberi kita jumlah gaji 5 pekerja pertama.
# 10 - Formula TRIM dalam Excel
Rumus excel maju ini digunakan untuk membersihkan ruang yang tidak penting dari teks. Cth. Sekiranya kita ingin membuang ruang pada awal beberapa nama, kita dapat menggunakannya dengan menggunakan fungsi TRIM di excel seperti di bawah:
Hasil yang dihasilkan adalah "Chandan Kale" tanpa ruang sebelum Chandan.
