Rumus Excel Tingkat Lanjut

Daftar 10 Rumus & Fungsi Excel Tingkat Lanjut Teratas

Anda dapat mengunduh Templat Rumus Excel Tingkat Lanjut ini di sini - Templat Rumus Excel Tingkat Lanjut

# 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 yang memiliki detail 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:

  1. 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.
  2. Dalam kasus nilai duplikat di kolom utama pada Tabel B, nilai pertama akan diisi di dalam sel.
  3. 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 di rumus (Dalam kasus di atas, output 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 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 data di baris ke-5, rumus akan menghasilkan "Chandan Kale". Karenanya, keluarannya akan bergantung pada setiap perubahan yang terjadi di tabel data dari waktu ke waktu.

# 3 - MATCH Formula di Excel

Rumus lanjutan Excel ini mengembalikan nomor baris atau kolom ketika ada kecocokan string atau angka tertentu dalam rentang yang diberikan. Dalam contoh di bawah ini, kami mencoba menemukan posisi "Rajesh Ved" di kolom Nama Karyawan.

Rumusnya akan seperti yang diberikan di bawah ini:

Fungsi MATCH akan mengembalikan 5 sebagai nilainya.

Argumen ke-3 digunakan untuk pencocokan persis. 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 sudah 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 hanya 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 menyaring 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 kami 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 hanya untuk melakukan itu.

Rumus di atas akan menghasilkan “1Aman Gupta”.

Kita dapat memiliki satu varian lagi dengan meletakkan satu tanda hubung antara ID dan NAME. Ex. CONCATENATE (B3, "-", C3) akan menghasilkan "1-Aman Gupta". Kita juga bisa menggunakan ini ke dalam VLOOKUP saat LOOKUP dalam nilai excel adalah campuran lebih dari satu variabel.

# 8 - Rumus KIRI, MID, dan KANAN di Excel

Kita dapat menggunakan rumus excel lanjutan ini jika kita ingin mengekstrak substring tertentu dari string tertentu. Rumus yang disebutkan dapat digunakan berdasarkan persyaratan 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 RIGHT pada 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 lanjutan ini dengan kombinasi fungsi lain seperti SUM atau AVERAGE dapat berguna dalam memberikan sentuhan dinamis pada kalkulasi. 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.