Senarai 10 Rumusan & Fungsi Excel Lanjutan Teratas
Anda boleh memuat turun Templat Rumus Excel Lanjutan ini di sini - Templat Rumusan Excel Lanjutan# 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 yang sama di semua jadual ini. Katakan anda mempunyai dua jadual yang mempunyai 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 yang lain.
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, ruangan Gaji Kakitangan tidak boleh sebelum ID Pekerja.
- Sekiranya terdapat nilai pendua pada lajur utama dalam Jadual B, nilai pertama akan dihuni dalam sel.
- Sekiranya anda memasukkan lajur baru dalam pangkalan data (Contoh Masukkan kolum baru sebelum Gaji Karyawan dalam Jadual B), output formula mungkin berbeza berdasarkan posisi yang telah anda sebutkan dalam formula (Dalam kes di atas, output 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 kedua-duanya. Cth. Untuk mendapatkan nama pekerja 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".
Catatan: Sekiranya anda memasukkan baris lain ke dalam data pada Baris ke-5, formula akan mengembalikan "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 padanan rentetan atau nombor tertentu dalam julat yang diberikan. Dalam contoh di bawah ini, kami berusaha mencari kedudukan "Rajesh Ved" di ruangan Nama Karyawan.
Formula adalah seperti yang diberikan di bawah ini:
Fungsi MATCH akan mengembalikan 5 sebagai nilai.
Argumen ke-3 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 sudah ada. Tetapi bagaimana jika kita perlu mempertimbangkan beberapa lajur semasa membuat bendera. Cth. Dalam kes di bawah ini, 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 ATAU di excel dan bukannya AND jika kita hanya perlu memenuhi salah satu syarat banyak.
Dalam kes-kes di atas, jika salah satu syarat puas, kita akan mempunyai sel yang diisi sebagai 1 yang lain 0. Kita juga boleh menggantikan 1 atau 0 dengan beberapa substring dengan tanda petik ganda (“”).
# 6 - Formula SUMIF di 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 kita 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 menjadi 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 boleh menggunakan formula excel lanjutan ini. Sekiranya kita ingin mengekstrak substring tertentu dari rentetan tertentu. Rumus yang disebutkan dapat 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 di excel juga sama, cuma kita akan melihat watak dari sebelah kanan tali. Walau bagaimanapun, sekiranya fungsi MID dalam excel, kita mesti memberikan kedudukan awal rentetan teks yang diperlukan dan panjang tali.
# 9 - Formula OFFSET dalam Excel
Fungsi excel canggih ini dengan kombinasi fungsi lain seperti SUM atau AVERAGE dapat berguna dalam 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 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.
