Setiap analis keuangan The Analyst Trifecta® Guide Panduan utama tentang bagaimana menjadi seorang analis keuangan kelas dunia. Apakah Anda ingin menjadi analis keuangan kelas dunia? Apakah Anda ingin mengikuti praktik terbaik industri terkemuka dan menonjol dari yang lain? Proses kami, yang disebut The Analyst Trifecta® terdiri dari analitik, presentasi & soft skill menghabiskan lebih banyak waktu di Excel daripada yang mungkin mereka akui. Berdasarkan pengalaman bertahun-tahun, kami telah menyusun formula Excel paling penting dan canggih yang harus diketahui oleh setiap analis keuangan kelas dunia.
1. PENCOCOKAN INDEKS
Rumus: = INDEX (C3: E9, MATCH (B13, C3: C9,0), MATCH (B14, C3: E3,0))
Ini adalah alternatif lanjutan dari rumus VLOOKUP atau HLOOKUP (yang memiliki beberapa kekurangan dan keterbatasan). INDEX MATCH Index Match Formula Menggabungkan fungsi INDEX dan MATCH adalah rumus pencarian yang lebih efektif daripada VLOOKUP. Pelajari cara menggunakan INDEX MATCH dalam tutorial Excel ini. Indeks mengembalikan nilai sel dalam tabel berdasarkan kolom dan nomor baris dan Match mengembalikan posisi sel dalam satu baris atau kolom. Pelajari caranya dalam panduan ini adalah kombinasi kuat dari rumus Excel yang akan membawa analisis keuangan dan pemodelan keuangan Anda ke tingkat berikutnya.
INDEX mengembalikan nilai sel dalam tabel berdasarkan nomor kolom dan baris.
MATCH mengembalikan posisi sel dalam satu baris atau kolom.
Berikut adalah contoh rumus INDEX dan MATCH yang digabungkan bersama. Dalam contoh ini, kami mencari dan mengembalikan tinggi badan seseorang berdasarkan namanya. Karena nama dan tinggi adalah variabel dalam rumus, kita dapat mengubah keduanya!
Rumus Kecocokan Indeks Menggabungkan fungsi INDEX dan MATCH adalah rumus pencarian yang lebih andal daripada VLOOKUP. Pelajari cara menggunakan INDEX MATCH dalam tutorial Excel ini. Indeks mengembalikan nilai sel dalam tabel berdasarkan kolom dan nomor baris dan Match mengembalikan posisi sel dalam satu baris atau kolom. Pelajari caranya dalam panduan ini
Untuk penjelasan langkah demi langkah atau cara menggunakan rumus ini, lihat panduan gratis kami tentang cara menggunakan INDEX MATCH di Excel Index Match Formula Menggabungkan fungsi INDEX dan MATCH adalah rumus pencarian yang lebih hebat daripada VLOOKUP. Pelajari cara menggunakan INDEX MATCH dalam tutorial Excel ini. Indeks mengembalikan nilai sel dalam tabel berdasarkan kolom dan nomor baris dan Match mengembalikan posisi sel dalam satu baris atau kolom. Pelajari caranya dalam panduan ini.
2. JIKA digabungkan dengan DAN / ATAU
Rumus: = IF (AND (C2> = C4, C2 <= C5), C6, C7)
Siapapun yang menghabiskan banyak waktu melakukan berbagai jenis model keuangan Jenis Model Keuangan Jenis model keuangan yang paling umum meliputi: model laporan 3, model DCF, model M&A, model LBO, model anggaran. Temukan 10 jenis teratas tahu bahwa rumus IF bersarang bisa menjadi mimpi buruk. Menggabungkan IF dengan fungsi AND atau OR dapat menjadi cara yang bagus untuk menjaga rumus agar lebih mudah diaudit dan lebih mudah dipahami oleh pengguna lain. Pada contoh di bawah ini, Anda akan melihat bagaimana kami menggunakan kombinasi fungsi individual untuk membuat rumus yang lebih canggih.
Untuk perincian mendetail tentang cara melakukan fungsi ini di Excel, lihat panduan gratis kami tentang cara menggunakan Pernyataan IF dengan DAN / ATAU IF Antara Dua Angka Unduh templat gratis ini untuk pernyataan IF antara dua angka di Excel. Dalam tutorial ini, kami menunjukkan kepada Anda langkah demi langkah cara menghitung IF dengan pernyataan AND. Pelajari cara membuat pernyataan IF yang akan menguji apakah sel berisi nilai antara dua angka dan kemudian menampilkan hasil yang Anda inginkan jika kondisi tersebut terpenuhi. 
Pernyataan IF Antara Dua Angka Unduh templat gratis ini untuk pernyataan IF antara dua angka di Excel. Dalam tutorial ini, kami menunjukkan kepada Anda langkah demi langkah cara menghitung IF dengan pernyataan AND. Pelajari cara membuat pernyataan IF yang akan menguji apakah sel berisi nilai antara dua angka dan kemudian mengeluarkan hasil yang Anda inginkan jika kondisi tersebut terpenuhi
3. OFFSET dikombinasikan dengan SUM atau AVERAGE
Rumus: = SUM (B4: OFFSET (B4,0, E2-1))
Fungsi OFFSET Fungsi OFFSET Fungsi OFFSET dikategorikan dalam fungsi Excel Lookup dan Referensi. OFFSET akan mengembalikan berbagai sel. Artinya, ini akan mengembalikan sejumlah baris dan kolom dari kisaran awal yang telah ditentukan. sendiri tidak terlalu canggih, tetapi ketika kita menggabungkannya dengan fungsi lain seperti SUM atau AVERAGE kita dapat membuat rumus yang cukup canggih. Misalkan Anda ingin membuat fungsi dinamis yang dapat menjumlahkan sejumlah variabel sel. Dengan rumus SUM biasa, Anda dibatasi pada penghitungan statis, tetapi dengan menambahkan OFFSET Anda dapat membuat referensi sel berpindah.
Cara kerjanya: Untuk membuat rumus ini bekerja, kami mengganti sel referensi akhir dari fungsi SUM dengan fungsi OFFSET. Ini membuat rumus menjadi dinamis dan sel yang direferensikan sebagai E2 adalah tempat Anda dapat memberi tahu Excel berapa banyak sel berurutan yang ingin Anda tambahkan. Sekarang kita punya beberapa rumus Excel tingkat lanjut!
Di bawah ini adalah tangkapan layar dari formula yang sedikit lebih canggih ini yang sedang bekerja.
Seperti yang Anda lihat, rumus SUM dimulai di sel B4, tetapi diakhiri dengan variabel, yaitu rumus OFFSET yang dimulai dari B4 dan dilanjutkan dengan nilai di E2 ("3"), minus satu. Ini memindahkan akhir rumus penjumlahan ke 2 sel, menjumlahkan data 3 tahun (termasuk titik awal). Seperti yang Anda lihat di sel F7, jumlah sel B4: D4 adalah 15, yang diberikan oleh rumus offset dan penjumlahan.
Pelajari cara membuat rumus ini selangkah demi selangkah di kursus Excel lanjutan kami.
4. PILIH
Rumus: = PILIH (pilihan, opsi1, opsi2, opsi3)
Fungsi CHOOSE CHOOSE Fungsi CHOOSE dikategorikan di bawah fungsi Pencarian dan Referensi Excel. Ini akan mengembalikan nilai dari array yang sesuai dengan nomor indeks yang diberikan. Fungsi ini akan mengembalikan entri ke-n dalam daftar yang diberikan. Sebagai seorang analis keuangan, fungsi CHOOSE berguna dalam memilih dari sekumpulan data tertentu. Misalnya, kami sangat 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, bayangkan Anda memiliki tiga asumsi berbeda untuk pertumbuhan pendapatan tahun depan: 5%, 12%, dan 18%. Menggunakan rumus PILIH, Anda dapat mengembalikan 12% jika Anda memberi tahu Excel bahwa Anda menginginkan pilihan # 2.
Baca lebih lanjut tentang analisis skenario di Excel Analisis Skenario Analisis skenario adalah teknik yang digunakan untuk menganalisis keputusan dengan berspekulasi berbagai kemungkinan hasil dalam investasi keuangan. Dalam pemodelan keuangan, ini.
Untuk melihat demonstrasi video, lihat Kursus Rumus Excel Tingkat Lanjut kami.
5. XNPV dan XIRR
Rumus: = XNPV (tingkat diskonto, arus kas, tanggal)
Jika Anda seorang analis yang bekerja di perbankan investasi Jalur Karir Perbankan Investasi Panduan karier perbankan investasi - rencanakan jalur karier IB Anda. Pelajari tentang gaji bank investasi, cara mendapatkan pekerjaan, dan apa yang harus dilakukan setelah berkarir di IB. Divisi perbankan investasi (IBD) membantu pemerintah, perusahaan, dan institusi meningkatkan modal dan menyelesaikan merger dan akuisisi (M&A). , penelitian ekuitas, perencanaan & analisis keuangan (Analis FP&A FP&A Menjadi Analis FP&A di sebuah perusahaan. Kami menguraikan gaji, keterampilan, kepribadian, dan pelatihan yang Anda butuhkan untuk pekerjaan FP&A dan karier keuangan yang sukses. Analis, manajer, dan direktur FP&A adalah bertanggung jawab untuk memberikan analisis dan informasi yang mereka butuhkan kepada eksekutif), atau bidang keuangan perusahaan lainnya yang memerlukan diskon arus kas,maka formula ini adalah penyelamat!
Sederhananya, XNPV dan XIRR memungkinkan Anda untuk menerapkan tanggal tertentu untuk setiap arus kas yang sedang didiskon. Masalah dengan rumus NPV dan IRR dasar Excel adalah bahwa mereka mengasumsikan periode waktu antara arus kas adalah sama. Secara rutin, sebagai analis, Anda akan menghadapi situasi di mana arus kas tidak diatur waktunya secara merata, dan rumus ini adalah cara Anda memperbaikinya.
Untuk rincian lebih lanjut, lihat panduan rumus IRR vs XIRR gratis kami XIRR vs IRR Mengapa menggunakan XIRR vs IRR. XIRR memberikan tanggal tertentu untuk setiap arus kas sehingga lebih akurat daripada IRR saat membangun model keuangan di Excel. serta panduan XNPV kami.
6. SUMIF dan COUNTIF
Rumus: = COUNTIF (D5: D12, ”> = 21 ″)
Kedua rumus tingkat lanjut ini adalah penggunaan yang bagus dari fungsi bersyarat. SUMIF menambahkan semua sel yang memenuhi kriteria tertentu, dan COUNTIF menghitung semua sel yang memenuhi kriteria tertentu. Misalnya, bayangkan Anda ingin menghitung semua sel yang lebih besar dari atau sama dengan 21 (usia legal untuk minum di AS) untuk mengetahui berapa banyak botol sampanye yang Anda perlukan untuk acara klien. Anda dapat menggunakan COUNTIF sebagai solusi lanjutan, seperti yang ditunjukkan pada gambar di bawah.
Dalam kursus Excel lanjutan kami, kami memecah rumus ini dengan lebih detail.
7. PMT dan IPMT
Rumus: = PMT (suku bunga, # periode, nilai sekarang)
Jika Anda bekerja di perbankan komersial Profil Karier Perbankan Komersial Jalur karier Perbankan Komersial menyediakan produk kredit kepada klien seperti pinjaman berjangka, jalur kredit bergulir, fasilitas sindikasi, layanan pengelolaan kas, dan produk pendapatan tetap lainnya. Sebagai analis kredit atau manajer akun, Anda menyampaikan nasihat keuangan, real estat, FP&A FP&A Perencanaan dan Analisis Keuangan (FP&A) adalah fungsi penting di sebuah perusahaan. Profesional FP&A mendukung pengambilan keputusan eksekutif untuk CEO, CFO dan Dewan Direksi dengan perencanaan, analisis, dan pemodelan. Pelajari apa pekerjaan seorang analis, manajer, atau direktur FP&A - gaji, persyaratan, pendidikan, keterampilan, atau posisi analis keuangan apa pun yang berhubungan dengan jadwal utang, Anda pasti ingin memahami dua rumus terperinci ini.
Rumus PMT memberi Anda nilai pembayaran yang sama selama masa pinjaman. Anda dapat menggunakannya dalam hubungannya dengan IPMT (yang memberi tahu Anda pembayaran bunga untuk jenis pinjaman yang sama), kemudian pisahkan pembayaran pokok dan bunga.
Berikut adalah contoh cara menggunakan fungsi PMT untuk mendapatkan pembayaran hipotek bulanan untuk hipotek $ 1 juta dengan harga 5% selama 30 tahun.
8. LEN dan PANGKAS
Rumus: = LEN (teks) dan = TRIM (teks)
Ini adalah formula yang sedikit kurang umum, tetapi tentunya sangat canggih. Aplikasi ini sangat bagus untuk analis keuangan The Analyst Trifecta® Guide Panduan utama tentang bagaimana menjadi analis keuangan kelas dunia. Apakah Anda ingin menjadi analis keuangan kelas dunia? Apakah Anda ingin mengikuti praktik terbaik industri terkemuka dan menonjol dari yang lain? Proses kami, yang disebut The Analyst Trifecta® terdiri dari analitik, presentasi & soft skill yang perlu mengatur dan memanipulasi data dalam jumlah besar. Sayangnya, data yang kami dapatkan tidak selalu terorganisir dengan sempurna dan terkadang ada masalah seperti ruang ekstra di awal atau akhir sel
Dalam contoh di bawah ini, Anda dapat melihat bagaimana rumus TRIM membersihkan data Excel.
9. CONCATENATE
Rumus: = A1 & "lebih banyak teks"
Concatenate sebenarnya bukan sebuah fungsi - ini hanyalah cara inovatif untuk menggabungkan informasi dari sel yang berbeda dan membuat lembar kerja lebih dinamis. Ini adalah alat yang sangat ampuh bagi analis keuangan yang melakukan pemodelan keuangan (lihat panduan pemodelan keuangan gratis kami Panduan Pemodelan Keuangan Gratis Panduan pemodelan keuangan ini mencakup tip Excel dan praktik terbaik tentang asumsi, pendorong, peramalan, menghubungkan tiga pernyataan, analisis DCF, lebih Belajarlah lagi).
Pada contoh di bawah, Anda dapat melihat bagaimana teks "New York" plus "," digabungkan dengan "NY" untuk membuat "New York, NY". Ini memungkinkan Anda membuat header dan label dinamis di lembar kerja. Sekarang, alih-alih memperbarui sel B8 secara langsung, Anda bisa memperbarui sel B2 dan D2 secara independen. Dengan kumpulan data yang besar, ini adalah keterampilan berharga yang Anda miliki.
10. Fungsi CELL, LEFT, MID dan RIGHT
Fungsi Excel tingkat lanjut ini dapat digabungkan untuk membuat beberapa rumus yang sangat canggih dan kompleks untuk digunakan. Fungsi CELL bisa mengembalikan berbagai informasi tentang konten sel (seperti nama, lokasi, baris, kolom, dan lainnya). Fungsi LEFT bisa mengembalikan teks dari awal sel (kiri ke kanan), MID mengembalikan teks dari titik awal sel mana pun (kiri ke kanan), dan KANAN mengembalikan teks dari akhir sel (kanan ke kiri).
Di bawah ini adalah ilustrasi dari ketiga rumus ini yang bekerja.
Untuk melihat bagaimana ini dapat digabungkan dengan cara yang efektif dengan fungsi CELL, kami memecahnya untuk Anda selangkah demi selangkah di kelas rumus Excel tingkat lanjut kami.
Lebih banyak pelatihan rumus Excel
Kami berharap 10 rumus Excel tingkat lanjut teratas ini bermanfaat bagi Anda. Mereka harus berusaha keras untuk meningkatkan keterampilan analisis keuangan dan pemodelan keuangan Anda. Keterampilan Pemodelan Keuangan Pelajari 10 keterampilan pemodelan keuangan yang paling penting dan apa yang dibutuhkan untuk menjadi ahli dalam pemodelan keuangan di Excel. Keterampilan terpenting: akuntansi.
Di bawah ini adalah sumber keuangan lainnya untuk membantu Anda menjadi pengguna Excel yang handal:
- Lembar contekan rumus Excel Rumus Excel Lembar contekan Keuangan lembar contekan rumus Excel akan memberi Anda semua rumus yang paling penting untuk melakukan analisis dan pemodelan keuangan dalam lembar bentang Excel. Jika Anda ingin menjadi master analisis keuangan Excel dan ahli dalam membangun model keuangan maka Anda telah datang ke tempat yang tepat.
- Pintasan keyboard Excel Pintasan Excel PC Mac Pintasan Excel - Daftar pintasan MS Excel yang paling penting & umum untuk pengguna PC & Mac, keuangan, profesi akuntansi. Pintasan keyboard mempercepat keterampilan pemodelan Anda dan menghemat waktu. Pelajari pengeditan, pemformatan, navigasi, pita, tempel khusus, manipulasi data, rumus dan pengeditan sel, dan shortuct lainnya
- Kursus kilat Excel gratis
- Kursus Excel tingkat lanjut
- Excel untuk pemodelan keuangan
