Daftar 10 Rumus & Fungsi Excel Tingkat Lanjut Teratas
# 1 - Rumus VLOOKUP di Excel
Fungsi excel tingkat lanjut ini adalah salah satu rumus yang paling banyak digunakan di excel. Ini terutama karena kesederhanaan rumus ini dan aplikasinya dalam mencari nilai tertentu dari tabel lain, yang memiliki satu variabel umum di seluruh tabel ini. Misalkan Anda memiliki dua tabel dengan rincian tentang gaji dan nama Karyawan perusahaan dengan ID Karyawan sebagai kolom utama. Anda ingin mendapatkan gaji dari Tabel B di Tabel A.
Anda dapat menggunakan VLOOKUP seperti di bawah ini.
Ini akan menghasilkan tabel di bawah ini ketika kita menerapkan rumus excel lanjutan ini di sel lain dari kolom Gaji Karyawan.
Seret Rumus ke Sel lainnya.
Ada tiga batasan utama VLOOKUP:
- Anda tidak dapat memiliki kolom utama di sebelah kanan kolom yang ingin Anda isi nilainya dari tabel lain. Dalam hal ini, kolom Gaji Karyawan tidak boleh sebelum ID Karyawan.
- Dalam nilai duplikat di kolom utama di Tabel B, nilai pertama akan diisi di dalam sel.
- Jika Anda memasukkan kolom baru ke dalam database (mis. Sisipkan kolom baru sebelum Gaji Karyawan di Tabel B), output rumus dapat berbeda berdasarkan posisi yang telah Anda sebutkan dalam rumus (Dalam kasus di atas, hasilnya akan kosong)
# 2 - Rumus INDEX di Excel
Rumus excel tingkat lanjut ini digunakan untuk mendapatkan nilai sel dalam tabel tertentu dengan menentukan jumlah baris, kolom, atau keduanya. Ex. Untuk mendapatkan nama seorang karyawan pada observasi ke 5 , Berikut datanya.
Kita bisa menggunakan rumus excel lanjutan seperti di bawah ini:
Rumus INDEX yang sama dapat digunakan untuk mendapatkan nilai di sepanjang baris. Saat menggunakan nomor baris dan kolom, sintaksnya akan terlihat seperti ini:
Rumus di atas akan menghasilkan "Rajesh Ved".
Catatan: Jika Anda memasukkan baris lain ke dalam 5 th berturut-turut, rumus akan kembali “Chandan Kale.” Oleh karena itu, keluarannya akan bergantung pada setiap perubahan yang terjadi dalam tabel data dari waktu ke waktu.
# 3 - MATCH Formula di Excel
Rumus lanjutan Excel ini mengembalikan nomor baris atau kolom ketika ada string atau angka tertentu dalam rentang tertentu. Dalam contoh di bawah ini, kami mencoba menemukan "Rajesh Ved" di kolom Nama Karyawan.
Rumusnya akan seperti yang diberikan di bawah ini:
Fungsi MATCH akan mengembalikan 5 sebagai nilainya.
3 rd argumen yang digunakan untuk pertandingan tepat. Anda juga dapat menggunakan +1 dan -1 berdasarkan kebutuhan Anda.
Catatan: Seseorang dapat menggabungkan INDEX dan MATCH untuk mengatasi batasan VLOOKUP.
# 4 - JIKA DAN Rumus di Excel
Ada banyak contoh ketika seseorang perlu membuat flag berdasarkan beberapa batasan. Kita semua sudah familiar dengan sintaks dasar IF. Kami menggunakan fungsi IF excel lanjutan ini untuk membuat bidang baru berdasarkan beberapa kendala pada bidang yang ada. Tetapi bagaimana jika kita perlu menggunakan beberapa kolom sebagai pertimbangan saat membuat sebuah bendera. Ex. Dalam kasus di bawah ini, kami ingin menandai semua karyawan yang gajinya lebih dari 50K tetapi ID karyawan lebih dari 3.
Kami akan menggunakan IF AND dalam kasus seperti itu. Silakan temukan di bawah tangkapan layar untuk hal yang sama.
Ini akan mengembalikan hasilnya sebagai 0.
Kita dapat memiliki banyak kondisi atau batasan untuk membuat sebuah flag berdasarkan beberapa kolom menggunakan AND.
# 5 - JIKA ATAU Formula di Excel
Demikian pula, kita juga dapat menggunakan fungsi OR di excel daripada AND jika kita perlu memenuhi salah satu dari banyak kondisi.
Dalam kasus di atas, jika salah satu kondisi terpenuhi, kita akan memiliki sel yang diisi sebagai 1 lainnya 0. Kita juga dapat mengganti 1 atau 0 dengan beberapa substring dengan tanda kutip ganda ("").
# 6 - Rumus SUMIF di Excel
Dalam beberapa analisis, Anda mungkin perlu memfilter beberapa pengamatan saat menerapkan fungsi penjumlahan atau penghitungan. Dalam kasus seperti itu, fungsi SUMIF excel lanjutan di excel ini siap membantu kami. Ini memfilter semua pengamatan berdasarkan kondisi tertentu yang diberikan dalam rumus excel lanjutan ini dan menyimpulkannya. Ex. Bagaimana jika kita ingin mengetahui jumlah gaji hanya para karyawan yang memiliki ID karyawan lebih dari 3.
Dengan Menerapkan Rumus SUMIFS:
Formula mengembalikan hasil sebagai 322000.
Kami juga dapat menghitung jumlah karyawan dalam organisasi yang memiliki ID karyawan lebih dari 3 saat menggunakan COUNTIF dan bukan SUMIF.
# 7 - CONCATENATE Formula di Excel
Fungsi lanjutan excel ini adalah salah satu rumus yang dapat digunakan dengan banyak varian. Rumus excel tingkat lanjut ini membantu kita menggabungkan beberapa string teks menjadi satu string teks. Mis, Jika kita ingin menampilkan ID karyawan dan Nama Karyawan dalam satu kolom.
Kita dapat menggunakan rumus CONCATENATE ini untuk melakukan itu.
Rumus di atas akan menghasilkan “1Aman Gupta”.
Kita bisa memiliki satu varian lagi dengan meletakkan satu tanda hubung antara ID dan NAME. Ex. CONCATENATE (B3, "-", C3) akan menghasilkan "1-Aman Gupta". Kita juga dapat menggunakan ini ke dalam VLOOKUP saat LOOKUP dalam nilai excel adalah gabungan lebih dari satu variabel.
# 8 - Rumus KIRI, MID, dan KANAN di Excel
Kita dapat menggunakan rumus excel lanjutan ini untuk mengekstrak substring tertentu dari string tertentu. Ini dapat digunakan berdasarkan kebutuhan kami. Ex. Jika kita ingin mengekstrak 5 karakter pertama dari Nama Karyawan, kita dapat menggunakan rumus LEFT di excel dengan nama kolom dan parameter kedua adalah 5.
Outputnya diberikan di bawah ini:
Penerapan rumus KANAN di excel juga sama. Hanya saja kita akan melihat karakter dari kanan string. Namun, dalam kasus fungsi MID di excel, kita harus memberikan posisi awal string teks yang diperlukan dan panjang string.
# 9 - Formula OFFSET di Excel
Fungsi excel tingkat lanjut ini dengan kombinasi fungsi lain seperti SUM atau AVERAGE, dapat memberikan sentuhan dinamis pada kalkulasi. Ini paling baik digunakan dalam kasus ketika kita memasukkan baris kontinu ke dalam database yang ada. OFFSET Excel memberi kita rentang di mana kita perlu menyebutkan sel referensi, jumlah baris, dan kolom. Ex. Jika kita ingin menghitung rata-rata dari 5 karyawan pertama di perusahaan tempat kita memiliki gaji karyawan yang diurutkan berdasarkan ID karyawan, kita dapat melakukan hal berikut. Perhitungan di bawah ini akan selalu memberi kami gaji.
- Ini akan memberi kami jumlah gaji dari 5 karyawan pertama.
# 10 - TRIM Formula di Excel
Rumus excel tingkat lanjut ini digunakan untuk membersihkan spasi yang tidak penting dari teks. Ex. Jika kita ingin menghilangkan spasi di awal beberapa nama, kita bisa menggunakannya dengan menggunakan fungsi TRIM di excel seperti di bawah ini:
Output yang dihasilkan adalah "Chandan Kale" tanpa spasi sebelum Chandan.
