Rumus Excel Lanjutan

Daftar 10 Rumus & Fungsi Excel Lanjutan 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 penerapannya dalam mencari nilai tertentu dari tabel lain, yang memiliki satu variabel standar di seluruh tabel ini. Misalnya, Anda memiliki dua tabel yang merinci gaji dan nama karyawan perusahaan, dengan ‘ID Karyawan’ sebagai kolom utama. Anda ingin mendapatkan gaji dari Tabel B di Tabel A.

Anda dapat menggunakan VLOOKUPMenggunakan VLOOKUPFungsi excel VLOOKUP mencari nilai tertentu dan mengembalikan kecocokan yang sesuai berdasarkan pengenal unik. Pengidentifikasi unik secara unik terkait dengan semua catatan database. Misalnya, ID karyawan, nomor daftar siswa, nomor kontak pelanggan, alamat email penjual, dll., adalah pengidentifikasi unik. selengkapnya seperti di bawah ini.

Ini akan menghasilkan tabel di bawah ini ketika kami menerapkan rumus Excel lanjutan ini di sel lain dari kolom ‘Gaji Karyawan’.

Seret rumus ke seluruh sel.

Ada tiga batasan utama VLOOKUP:

Anda tidak dapat memiliki kolom utama di sebelah kanan kolom yang ingin Anda isi nilainya dari tabel lain. Kolom “Gaji Karyawan” tidak boleh sebelum “ID Karyawan”.
Dalam nilai duplikat di kolom utama di Tabel B, nilai pertama akan diisi di sel.
Jika Anda memasukkan kolom baru ke dalam database (misalnya, menyisipkan kolom baru sebelum “Gaji Karyawan” pada Tabel B), keluaran rumus dapat berbeda berdasarkan posisi yang telah Anda sebutkan dalam rumus (dalam kasus di atas , hasilnya akan kosong).

#2 – Formula INDEX di Excel

Rumus Excel lanjutan ini digunakan untuk mendapatkan nilai sel dalam tabel tertentu dengan menentukan jumlah baris, kolom, atau keduanya. Misalnya untuk mendapatkan nama karyawan pada pengamatan ke-5. Di bawah ini adalah datanya.

Kita dapat menggunakan rumus Excel lanjutan di bawah ini:

Kita bisa menggunakan rumus INDEX yang sama untuk mendapatkan nilai di sepanjang baris. Jadi, misalnya, saat menggunakan nomor baris dan kolom, sintaksnya akan terlihat seperti ini:

Rumus di atas akan kembali sebagai ‘Rajesh Ved.’

Catatan: Jika Anda memasukkan baris lain ke baris ke-5, rumus akan kembali sebagai “Chandan Kale”. Oleh karena itu, output akan tergantung pada setiap perubahan dalam tabel data dari waktu ke waktu.

#3 – Rumus PERTANDINGAN di Excel

Rumus tingkat lanjut Excel ini mengembalikan nomor baris atau kolom ketika string atau nomor tertentu berada dalam rentang yang diberikan. Dalam contoh di bawah ini, kami mencoba mencari “Rajesh Ved” di kolom “Nama Karyawan”.

Rumusnya akan seperti yang diberikan di bawah ini:

Fungsi MATCH Fungsi MATCH Fungsi MATCH mencari nilai tertentu dan mengembalikan posisi relatifnya dalam rentang sel tertentu. Outputnya adalah posisi pertama yang ditemukan untuk nilai yang diberikan. Menjadi fungsi pencarian dan referensi, ini berfungsi untuk pencocokan tepat dan perkiraan. Misalnya, jika rentang A11:A15 terdiri dari angka 2, 9, 8, 14, 32, rumus ‘MATCH(8,A11:A15,0)’ mengembalikan 3. Hal ini karena angka 8 berada pada urutan ketiga posisi. read more 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 keterbatasan VLOOKUP.

#4 – JIKA DAN Rumus di Excel

Ada banyak contoh ketika seseorang perlu membuat flag berdasarkan beberapa kendala. Kita semua sudah familiar dengan sintaks dasar IF. Kami menggunakan fungsi IF excel lanjutan iniFungsi Excel IF FunctionIF di Excel mengevaluasi apakah kondisi yang diberikan terpenuhi dan mengembalikan nilai tergantung pada apakah hasilnya ‘benar’ atau ‘salah’. Ini adalah fungsi bersyarat Excel, yang mengembalikan hasil berdasarkan terpenuhi atau tidak terpenuhinya kriteria yang diberikan. baca lebih lanjut untuk membuat bidang baru berdasarkan beberapa batasan bidang yang ada. Tapi bagaimana jika kita perlu mempertimbangkan banyak kolom saat membuat bendera? Misalnya, dalam kasus di bawah ini, kami ingin menandai semua karyawan yang gajinya lebih dari 50.000. Tapi ‘ID Karyawan’ lebih besar dari 3.

Kami akan menggunakan rumus JIKA DAN dalam kasus seperti itu. Silakan temukan di bawah tangkapan layar untuk hal yang sama.

Itu akan mengembalikan hasilnya sebagai 0.

Kita dapat memiliki banyak syarat atau batasan untuk membuat bendera berdasarkan beberapa kolom menggunakan AND.

#5 – JIKA ATAU Rumus di Excel

Demikian pula, kita dapat menggunakan fungsi OR di ExcelMenggunakan Fungsi OR Di ExcelFungsi OR di Excel digunakan untuk menguji berbagai kondisi, memungkinkan Anda membandingkan dua nilai atau pernyataan di Excel. Jika setidaknya salah satu argumen atau kondisi bernilai BENAR, itu akan mengembalikan BENAR. Demikian pula, jika semua argumen atau kondisi adalah FALSE, itu akan mengembalikan FASLE.read more daripada AND jika kita perlu memenuhi salah satu dari banyak kondisi.

Jika ada kondisi yang terpenuhi dalam kasus di atas, sel akan terisi sebagai 1, selain itu 0. Kita dapat mengganti 1 atau 0 dengan beberapa substring dengan tanda kutip ganda (“”).

#6 – Rumus SUMIF di Excel

Dalam beberapa analisis, Anda mungkin perlu memfilter pengamatan saat menerapkan fungsi penjumlahan atau penghitungan. Dalam kasus seperti itu, fungsi SUMIF Excel tingkat lanjut ini di excelFungsi SUMIF Di ExcelFungsi SUMIF Excel menghitung jumlah rentang sel berdasarkan kriteria yang diberikan. Kriteria dapat mencakup tanggal, angka, dan teks. Misalnya, rumus ‘=SUMIF(B1:B5, ‘<=12′)’ menambahkan nilai dalam rentang sel B1:B5, yang kurang dari atau sama dengan 12. baca selengkapnya siap membantu kami. Ini memfilter semua pengamatan berdasarkan kondisi tertentu dalam rumus excel lanjutan ini dan meringkasnya. Misalnya, Bagaimana jika kita ingin mengetahui jumlah gaji hanya karyawan dengan ID karyawan lebih dari 3?

Dengan menerapkan rumus SUMIFS Rumus SUMIFS SUMIFS adalah versi yang disempurnakan dari rumus SUMIF di Excel yang memungkinkan Anda untuk menjumlahkan berbagai rentang data dengan mencocokkan beberapa kriteria. Baca selengkapnya:

Rumus mengembalikan hasil sebagai 322000.

Kami juga dapat menghitung jumlah karyawan dalam organisasi yang memiliki ID karyawan lebih dari 3 menggunakan COUNTIF, bukan SUMIF.

#7 – Rumus CONCATENATE di Excel

Fungsi lanjutan Excel ini adalah salah satu rumus yang digunakan dengan beberapa varian. Rumus Excel tingkat lanjut ini membantu kita menggabungkan beberapa string teks menjadi satu string teks. Misalnya, jika kita ingin menampilkan “ID Karyawan” dan “Nama Karyawan” dalam satu kolom.

Kita dapat menggunakan rumus CONCATENATE ini di sini.

Formula di atas akan menghasilkan ‘1Aman Gupta’.

Kami dapat memiliki satu varian lagi dengan menempatkan satu tanda hubung antara ID dan NAMA. Misalnya, CONCATENATE(B3,”-“,C3) akan menghasilkan “1-Aman Gupta”. Kita juga bisa menggunakan ini di VLOOKUP saat LOOKUP di ExcelLOOKUP Di ExcelFungsi LOOKUP excel mencari nilai dalam rentang (baris tunggal atau kolom tunggal) dan mengembalikan kecocokan yang sesuai dari posisi yang sama dari rentang lain (baris tunggal atau kolom tunggal). Kecocokan yang sesuai adalah informasi yang terkait dengan nilai yang dicari. read more value adalah campuran lebih dari satu variabel.

#8 – Rumus KIRI, TENGAH, dan KANAN di Excel

Kita bisa menggunakan rumus Excel lanjutan ini untuk mengekstrak substring tertentu dari string yang diberikan. Seseorang dapat menggunakannya berdasarkan kebutuhan kami. Misalnya, Jika kita ingin menghapus 5 karakter pertama dari “Nama Karyawan”, kita dapat menggunakan rumus LEFT di Excel Formula LEFT Di ExcelFungsi kiri mengembalikan jumlah karakter dari awal string. Misalnya, jika kita menggunakan fungsi ini sebagai =LEFT ( “ANAND”,2), hasilnya akan AN.baca lebih lanjut dengan nama kolom dan parameter kedua sebagai 5.

Outputnya diberikan di bawah ini:

Penerapan Rumus RIGHT Pada Rumus ExcelRIGHT Pada fungsi ExcelRight adalah fungsi teks yang memberikan jumlah karakter dari ujung string yaitu dari kanan ke kiri. Misalnya, jika kita menggunakan fungsi ini sebagai =RIGHT ( ‘ANAND’,2) ini akan memberi kita ND sebagai hasilnya. Baca lebih lanjut juga sama. Hanya saja kita akan melihat karakter dari kanan string. Namun, dalam kasus fungsi MID di excelFungsi MID Di ExcelFungsi mid di Excel adalah fungsi teks yang menemukan string dan mengembalikannya dari bagian tengah spreadsheet mana pun. baca lebih lanjut, kita harus memberikan posisi awal string teks yang diperlukan dan panjang string.

#9 – Formula OFFSET di Excel

Fungsi Excel tingkat lanjut ini, dikombinasikan dengan SUM atau AVERAGE, dapat memberikan sentuhan dinamis pada perhitungan. Paling baik digunakan saat kita memasukkan baris kontinu ke dalam database yang sudah ada. OFFSET ExcelOFFSET ExcelFungsi OFFSET di excel mengembalikan nilai sel atau rentang (sel yang berdekatan) yang merupakan jumlah baris dan kolom tertentu dari titik referensi. read more menyediakan rentang di mana kita perlu menyebutkan sel referensi, jumlah baris, dan kolom. Misalnya, jika kita ingin menghitung rata-rata 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 kita gaji.

Ini akan memberi kita jumlah gaji 5 karyawan pertama.

#10 – Rumus TRIM di Excel

Rumus Excel tingkat lanjut ini digunakan untuk membersihkan ruang yang tidak penting dalam teks. Misalnya, jika kita ingin menghapus spasi di awal suatu nama, kita dapat menggunakannya dengan menggunakan fungsi TRIM di Excel Fungsi TRIM Di ExcelFungsi Trim di Excel melakukan persis seperti yang tersirat dari namanya: memangkas beberapa bagian dari string apa pun. Fungsi dari rumus ini adalah untuk menghilangkan spasi dalam string yang diberikan. Itu tidak menghapus satu spasi di antara dua kata, tetapi menghapus spasi lain yang tidak diinginkan. Baca lebih lanjut seperti di bawah ini:

Keluaran yang dihasilkan adalah ‘Chandan Kale’ tanpa spasi sebelum Chandan.

Artikel yang Direkomendasikan

Artikel ini adalah panduan untuk Rumus Tingkat Lanjut di Excel. Kami membahas 10 rumus Excel Lanjutan teratas dan fungsi Excel Lanjutan dengan templat yang dapat diunduh. Anda dapat mempelajari lebih lanjut tentang Excel dari artikel berikut: –