17 Rumus Excel Tingkat Lanjut – Yang Harus Diketahui Untuk Semua Profesional

Jika Anda mendapati diri Anda sering melenturkan tangan dan membuang-buang waktu untuk melakukan sesuatu secara manual di Excel, Anda kehilangan betapa hebatnya Excel jika Anda tahu cara menggunakannya dengan benar. Microsoft Excel dikenal dengan formulanya yang dapat bekerja secara efisien pada sejumlah besar data di dalam sel, tanpa banyak keterlibatan manual yang diperlukan dalam prosesnya.

Kami telah menyusun 17 formula excel tingkat lanjut yang memiliki kemampuan luar biasa untuk mengubah tugas-tugas manual yang panjang menjadi pekerjaan beberapa detik. Ini adalah formula yang harus dimiliki setiap profesional di ujung jari mereka untuk menghemat waktu berharga mereka sendiri, atau untuk mengesankan atasan mereka pada saat yang kritis.

Lihat daftarnya dan beri tahu kami favorit Anda!

Peserta didik menerima kenaikan Gaji rata-rata 58% dengan tertinggi hingga 400%.

1. PERTANDINGAN INDEKS

Rumus = INDEKS(C3:E9,MATCH(B13, C3:C9,0),MATCH(B14,C3:E3,0))

Alternatif yang sangat baik untuk rumus VLOOKUP atau HLOOKUP di Excel yang memiliki beberapa kelemahan untuk melakukan tugas pencarian. INDEX MATCH adalah formula kombinasi dari 2 fungsi terpisah:

INDEX: Rumus ini mengembalikan nilai sel dalam tabel berdasarkan kolom dan nomor baris.

MATCH: Rumus ini mengembalikan posisi sel dalam baris atau kolom.

Contoh kombinasi rumus INDEX dan MATCH adalah: Saat Anda mencari dan mengembalikan tinggi badan seseorang berdasarkan namanya, Anda dapat menggunakan rumus ini untuk mengubah kedua variabel (dalam hal ini, nama dan tinggi badan) menggunakan rumus INDEX MATCH .

Sumber

Mari kita uraikan langkah demi langkah:

Bagaimana Menggabungkan INDEX dan MATCH

• Ketik INDEX
• Pilih Area yang ingin Anda INDEKS
• Kunci area dengan F4
• Temukan Baris yang ingin Anda cari datanya
• Ketik MATCH dengan rangkaian informasi dan kunci area dengan F4
• Ketik 0 untuk yang sama persis, tutup kurung
• Tekan Enter

Ide Proyek Ilmu Data yang Menarik

Sekarang Anda memiliki kumpulan kolom dan baris yang sepenuhnya dinamis dan fungsional di lembar tempat semua menyisir sel dan baris untuk data yang tepat dilakukan secara otomatis.

2. RATA-RATA

Untuk melakukan rumus rata-rata untuk menemukan rata-rata rentang angka apa pun, berikut adalah langkah-langkah yang perlu Anda ikuti:

Masukkan nilai, sel, atau skala sel yang Anda hitung dalam format.

Rumus harus dimulai dengan =AVERAGE(angka1, angka 2, dst.)

Jika Anda ingin melakukan rata-rata rentang sel dalam lembar, berikut adalah langkah-langkah yang perlu Anda ikuti:

• Masukkan nilai, sel, atau skala sel yang Anda hitung dalam format, seperti yang Anda lakukan di atas
• Rumusnya akan seperti =AVERAGE(Nilai Awal: Nilai Akhir).

Jika Anda bertanya-tanya bagaimana cara memasukkan nilai untuk wilayah sel, Anda dapat memilih area pada lembar Anda dengan mouse Anda dan menguncinya dengan F4. Ini akan membuat Excel mengerjakan sisa pekerjaan untuk Anda tanpa melakukan penjumlahan dan pembagian gambar dengan jumlah item dalam grup.

3. SUMIF

Rumus di excel ini dilambangkan sebagai SUMIF(range, criteria, [sum range]). Ini akan menghasilkan jumlah nilai dalam Rentang sel yang diinginkan yang akan memenuhi persyaratan yang Anda tetapkan. Misalnya, =SUMIF(C3: C12, ">70.000) akan mengembalikan jumlah nilai antara sel C3 dan C12 hanya dari sel yang memiliki nilai lebih dari 70.000.

Dalam hal keuangan, gaji, atau bahkan perhitungan biaya, Anda memerlukan nilai prospek yang terkait dengan karyawan tertentu yang ditentukan oleh kondisi yang Anda tetapkan. Melakukan ini secara manual sangat memakan waktu dan memperlambat banyak hal.

Rumus untuk kondisi di atas dapat berupa =SUMIF(range, criteria, [sum_range]), di mana Range dapat didefinisikan sebagai Rentang dari lembar yang perlu Anda pilih nilainya.

[sum_range] didefinisikan sebagai Rentang tambahan atau opsional yang akan Anda tambahkan selain Rentang pertama yang dimasukkan.

Berikut ini contohnya:

Sumber

4. OFFSET DIGABUNGKAN DENGAN SUM

Sendirian, fungsi OFFSET mungkin tidak berguna, tetapi ketika dikombinasikan dengan layanan lain, Anda bisa mendapatkan rumus kompleks dengan hasil yang lebih cepat jika Anda ingin membuat peran dinamis yang dapat menjumlahkan jumlah variabel sel apa pun, rumus SUM reguler, yaitu statis perlu digabungkan dengan fungsi OFFSET.

Jadi untuk mengetahui jumlah nilai dalam sel variabel, rumusnya adalah:

=SUM(B4:OFFSET(B4,0,E2-1))

Sumber

Di sini referensi akhir dari Fungsi SUM diganti dengan fungsi OFFSET. Rumus SUM yang dimulai dari B4 diakhiri dengan variabel dan merupakan rumus OFFSET yang dimulai dari B4, dilanjutkan dengan nilai di E2 (“3”) dikurangi satu. Ini membantu metode untuk memindahkan dua sel dan menjumlahkan tiga tahun data. Dalam referensi di atas, Anda dapat melihat bahwa sel F7 menampung jumlah sel B4: D4 = 15.

5. JIKA DAN

Rumus ini berguna dalam situasi ketika Anda perlu membuat kondisi tertentu untuk menyisir tumpukan data. Pernyataan IF membantu dalam menggunakan fungsi IF excel lanjutan untuk membuat bidang baru berdasarkan kondisi ini di trek yang sudah ada.

Misalnya, jika Anda ingin menandai karyawan dengan gaji di atas 50K dan ID karyawan lebih dari 3, maka rumusnya adalah:

JIKA(DAN(E4>3,F4>50000)1,0)

Sumber

Karena tidak ada kasus nyata dalam data di atas, rumus akan mengembalikan nilai 0.

6. CONCATENATE

Jika Anda memiliki banyak string teks di lembar data Anda dan ingin membuat data muncul dalam satu baris, rumus ini adalah pilihan Anda. Misalnya, jika Anda ingin mewakili ID karyawan dan nama Karyawan dalam satu kolom, metodenya harus:

=CONCATENATE(B3, C3)

Sumber

7. PMT

Fungsi ini akan membantu Anda mengetahui pembayaran di masa mendatang untuk pinjaman, dengan tingkat bunga tertentu, jumlah pokok, dan durasi pinjaman. Inilah yang Anda butuhkan untuk memulai:

• Jangka waktu pinjaman
• Pokok awal (uang) pinjaman
• Nilai masa depan
• Jenis pinjaman

Sekarang, jika Anda ingin mencari pembayaran bulanan untuk jumlah pokok, rumusnya adalah:

= PMT (tarif, per, PV, [fv], [jenis])

Mari kita pahami ini dengan sebuah contoh:

Sumber

=PMT(C2/12.B2.A2)

Dan bagian terbaiknya adalah, Anda dapat menyeret sudut kanan bawah sel PMT melintasi bidang untuk secara otomatis menghitung jumlah pembayaran bulanan untuk semua bidang!

Jenis Pekerjaan Ilmu Data Populer

8. TRIM

Ini adalah salah satu rumus yang paling sering digunakan di excel yang membersihkan ruang yang tidak diinginkan di bidang. Misalnya: Jika Anda perlu menghapus spasi di awal beberapa nama, Anda dapat melakukannya dengan menggunakan fungsi TRIM:

=TRIM(C6)

Sumber

Ini akan mengembalikan Anda dengan nilai Chandan Kale tanpa spasi tambahan yang menempel di bagian depan atau akhir.

9. LEN

Ini adalah fungsi yang memberi tahu Anda jumlah karakter dalam string teks. Salah satu cara paling menarik untuk menggunakan ini adalah, misalnya, jika Anda ingin menyoroti donatur yang menyumbang lebih dari $1.000 dengan menghitung jumlah digit di kolom donasi, rumusnya adalah:

=LEN(B2)

Sumber

Dan jika Anda ingin menyorot semua sel di kolom Donasi, maka Anda perlu:

• Pilih tab Beranda di menu
• Klik pada Pemformatan Bersyarat (di bilah alat)
• Pilih Gunakan Rumus untuk menentukan sel mana yang akan diformat

Sumber

Di sini, jika Anda membuat kondisi ">3", maka apa pun yang lebih dari $1.000 akan menerima pemformatan unik, yang dapat Anda pilih dengan mengklik opsi Format.

10. PILIH

Ini adalah fungsi yang bagus untuk analisis skenario dalam pemodelan keuangan. Ini memungkinkan Anda untuk memilih di antara sejumlah opsi tertentu dan mengembalikan "pilihan" yang telah Anda pilih. Misalnya, jika Anda memiliki tiga nilai berbeda untuk pertumbuhan pendapatan tahun depan berdasarkan asumsi, menggunakan fungsi PILIH, Anda dapat mengembalikan jumlahnya sesuai kebutuhan Anda.

Rumusnya adalah:

=PILIH(C7,D3,D4,D5)

Sumber

11. SEL, KIRI, TENGAH, dan KANAN

Semua fungsi di atas dapat digabungkan dengan banyak cara untuk mendapatkan beberapa formula lanjutan.

• Fungsi CELL digunakan untuk mengembalikan berbagai jenis informasi tentang isi sel
• Layanan LEFT digunakan untuk mengganti teks dari awal sel.
• Fungsi MID dapat mengembalikan teks dari salah satu titik awal sel.
• Fungsi KANAN hanya mengembalikan teks dari akhir sel.

Sumber

12. XNPV dan XIRR

Untuk semua analis yang menemukan perbankan investasi, penelitian ekuitas, atau bidang keuangan perusahaan lainnya yang membutuhkan arus kas diskon, maka formula ini pasti akan menghemat banyak waktu dan menggerutu!

Pertanyaan & Jawaban Wawancara Ilmu Data

Misalnya, jika Anda ingin menghitung Nilai Bersih Sekarang (NPV) untuk investasi apa pun menggunakan tingkat diskonto tertentu dan arus kas yang terjadi dalam interval tidak teratur, gunakan fungsi berikut.

=XNPV(tingkat diskonto, arus kas, tanggal)

Sumber

Di sisi lain, fungsi XIRR memberi tahu Anda tingkat pengembalian internal (di mana arus keluar = arus masuk) untuk serangkaian arus kas yang terjadi pada interval yang tidak teratur, seperti:

13. COUNTA ( )

Analis sering ditemukan berjuang untuk menemukan jumlah sel dengan nilai (angka, kesalahan, teks, nilai logis) dan yang kosong. Fungsi COUNTA() dapat membantu Anda mengetahui nama sel yang tidak kosong dalam rentang yang dipilih. Misalnya, rumus untuk menghitung nilai untuk lembar data yang memiliki kolom A1-A10 adalah:

=COUNTA(A1: A10)

Sumber

14. FV

Rumus ini berguna jika Anda ingin menginvestasikan uang dalam sesuatu dan mengetahui nilainya. Persyaratan formula FV adalah:

• Tingkat bunga pinjaman
• Jumlah Pembayaran
• Pembayaran untuk setiap periode
• Saldo Awal Saat Ini
• Jenis Pinjaman

Misalnya, jika Anda ingin membandingkan beberapa CD kosong dan Anda memiliki warisan $20.000 untuk diinvestasikan dalam CD. Suku bunga direpresentasikan dalam format desimal; pembayaran adalah nol. Rumus skenarionya adalah:

=FV(A2/12,B2,C2,D2)

Sumber

Hasilnya akan menjadi:

Sumber

15. RANDBANTARA

Fungsi ini membantu memilih nomor dalam rentang angka yang telah ditentukan sebelumnya secara acak. Setelah Anda memasukkan angka terendah dan tertinggi dalam rumus, Excel dapat memilih data yang tepat dari bidang yang dilampirkan nama-nama dalam Rentang dan memilihnya secara acak. Metode skenarionya adalah:

=RANDBETWEEN(titik awal, titik akhir)

Sumber

16. KECIL

Fungsi KECIL di Excel mengembalikan nilai numerik berdasarkan posisi nilai dalam daftar yang diberi peringkat berdasarkan kepentingan. Fungsi ini membantu untuk mengambil "nilai terkecil ke-n" dari array atau Rentang sel seperti nilai terkecil, nilai terendah ke-2, nilai terendah ke-3, dll.

Sintaks untuk rumusnya adalah

= KECIL (kali, rentang)

Sebagai contoh,

Sumber

Karena fungsi KECIL otomatis, Anda perlu menyediakan rentang dan bilangan bulat untuk 'n' untuk menentukan nilai peringkat. Nama resmi untuk argumen ini adalah 'array' dan 'k'.

17. KUARTIL

Fungsi ini mengembalikan kuartil (masing-masing dari empat kelompok yang sama) dalam kumpulan data tertentu dan dapat mengembalikan nilai minimum, kuartil pertama, nilai maksimum kuartil kedua. Fungsi ini membawa jumlah kuartil bidang dalam array. Fungsi mengembalikan nilai numerik sesuai dengan persentil yang diminta.

Sintaks: =QUARTILE (array, quart)

Sumber

KESIMPULAN:

Microsoft Excel sangat tak terelakkan dalam hal tempat kerja abad ke-21, tetapi triknya adalah itu tidak harus terlalu menakutkan. Jadikan itu teman Anda, dan saksikan produktivitas Anda meningkat!