Level 2

Rumus dan Fungsi Excel

Dalam Level 2 (Rumus dan Fungsi Excel) ini, Anda akan mempelajari perbedaan dan cara menggunakan rumus dan fungsi di excel.

BAB 1 – Rumus VS Fungsi

  1. Rumus VS Fungsi: Perbedaan rumus dan fungsi serta contoh-contoh rumus (seperti perkalian, pembagian, pangkat dll)
  2. Subtract: Rumus Pengurangan pada Excel
  3. Multiply: Rumus Perkalian pada Excel
  4. Divide: Rumus Pembagian pada Excel
  5. Square Root: Rumus Pangkat pada Excel
  6. Percentage: Rumus Persentase pada Excel
  7. Named Range: Cara membuat Range bernama agar rumus lebih mudah di pahami
  8. Dynamic Named Range: Range bernama dinamis dapat meluas secara otomatis ketika Anda menambahkan Cell baru dalam Named Range.
  9. Paste Options: Beberapa pilihan Paste (berguna jika Anda menyalin cell yang menggunakan rumus)
  10. Not Equal To: Contoh menggunakan rumus “Tidak sama dengan <>”.
  11. Cm to Inches: Cara mengubah satuan centimeter ke inchi
  12. Discount: Rumus menghitung persentase diskon, harga diskon, dan harga asli di Excel.

BAB 2 – COUNT & SUM

  1. COUNT & SUM: Jenis-jenis dan cara menggunakan Fungsi Count dan SUM di Excel (COUNT, COUNTIF, COUNTIFS, SUM, SUMIF, SUMIFS)
  2. COUNT Blank/Non-Blank Cells: Fungsi untuk menghitung jumlah Cell kosong dan yang berisi.
  3. COUNTIF: Contoh Lengkap cara menggunakan rumus COUNTIF (Kriteria berdasarkan angka, teks, menghitung error, dll)
  4. COUNTIFS: Cara menggunakan Fungsi COUNTIFS untuk menghitung banyak data dengan 2 atau lebih kriteria.
  5. COUNT Characters: Cara menghitung jumlah karakter dalam Cell atau Range dan cara menghitung berapa kali karakter tertentu muncul dalam Cell atau Range.
  6. COUNT Cells with Text: Fungsi untuk menghitung jumlah Cell yang berisi huruf
  7. SUM: Fungsi SUM Lengkap (seperti menjumlahkan Range tertentu, semua kolom, cell yang berjauhan, angka besar, menjumlahkan otomatis, menjumlahkan setiap baris ke-n, dll)
  8. Running Total: Menjumlahkan total berjalan (Total berjalan akan berubah setiap nilai baru di tambahkan)
  9. SUMIF: Cara menggunakan Fungsi SUMIF untuk menjumlahkan dengan syarat (Lengkap)
  10. SUMIFS: Cara Menggunakan Fungsi SUMIFS untuk menjumlahkan data dengan banyak Syarat / kriteria.
  11. SUMPRODUCT: Cara menjumlahkan yang lebih kompleks (pahami setiap contohnya)

BAB 3 – Logical

  1. Logical: Jenis-jenis Fungsi logika (IF, AND, OR, NOT) dan cara menggunakannya (Level dasar)
  2. IF Function: untuk uji logika (menggunakan syarat). Contohnya Jika A1 > 70, maka B2 = “Lulus”. (Single IF, IF Berganda, gabungan IF + AND + OR dll)
  3. Comparison Operators: Operator Pembanding untuk Fungsi Logical (seperti sama dengan “=”, lebih besar dari “>”, lebih kecil dari “<“, tidak sama dengan “<>”, dll)
  4. OR: Contoh menggunakan Fungsi OR (Lengkap). Gabungan IF + OR, AND + OR, OR + WEEKDAY, dan XOR
  5. Roll the Dice: Eksperimen contoh menggunakan Rumus IF kompleks.
  6. IFs: Hanya bisa digunakan pada Excel versi 2016 atau lebih baru. Kegunaan Fungsi ini sama dengan Fungsi IF Bertumpuk.
  7. Contains Specific Text: Bertujuan untuk memeriksa apakah cell berisi teks tertentu.
  8. SWITCH: Hanya bisa digunakan pada Excel versi 2016 atau lebih baru. Kegunaan fungsi ini hampir menyerupai fungsi IFs
  9. IF Cell is Blank: Gabungan Fungsi IF + NOT + ISBLANK untuk cek cell kosong atau tidak. Lalu menggunakan Rumus Perkalian dsb.

BAB 4 – Cell References

  1. Cell References: Pentingnya Cell References pada Excel dalam menggunakan Rumus / Fungsi. Pelajari semua tentang Referensi Relatif, Absolut Kolom / Baris, dan Absolut ( Lambang Dollar $ )
  2. Copy Exact Formula: Cara copy rumus / fungsi otomatis menyesuaikan referensi.
  3. 3D-reference: Referensi Rumus / Fungsi untuk Sheet berbeda.
  4. External References: Cara menggunakan Rumus / Fungsi pada File Excel (WorkBook) berbeda.
  5. Hyperlinks: Cara menghubungkan (tersinkron) File Excel ke File lainnya atau ke Situs Website dll.
  6. Percent Change: Contoh menggunakan Nilai Absolut / Referensi Absolut / Symbol $ lengkap.

BAB 5 – Date & Time

  1. Date & Time: Cara memasukkan tanggal dan waktu pada Excel dengan dan tanpa menggunakan Rumus / Fungsi
  2. DateDif: Cara menggunakan Fungsi Dateif untuk tanggal pada Excel lengkap.
  3. Today’s Date: Cara menggunakan Fungsi Date dan Now untuk mendapatkan tanggal hari ini serta menggunakan Static Date.
  4. Calculate Age: Cara menghitung umur di Excel lengkap (seperti 25th 5bln 21hr)
  5. Time Difference: Cara menghitung jarak antara waktu A dan B dll (lengkap)
  6. Weekdays: Cara menggunakan Fungsi WEEKDAY, NETWORKDAYS danWORKDAY untuk menghitung hari atau hari kerja dalam 2 rentang tanggal
  7. Dasy until Birthday: Cara menghitung sisa hari menjelang hari ulang tahun Anda.
  8. Time Sheet: Membuat kalkulator waktu otomatis dalam file Excel Anda.
  9. Last Day of the Month: Cara menggunakan Fungsi EOMONTH untuk mendapatkan tanggal terakhir pada bulan ini.
  10. Holidays: Cara mengetahui tanggal hari libur tahun berapapun (2020, 2021, 2022, 2023 dll) di Excel (lengkap)
  11. Quarter: Cara mengetahui tanggal tertentu terdapat pada Kuartal berapa dengan mudah. Misalnya, tanggal 2 September 2020 berada pada Kuartal 3 tahun 2020.
  12. Day of the Year: Cara mengetahui sisa hari dalam tahun ini atau tahun lainnya. SUB-BAB ini menggabungkan Fungsi Date & Year.

BAB 6 – Text

  1. Text Functions: Cara menggabungkan kata pada 2 atau lebih cell, mengambil kata tertentu pada cell, replace kata tertentu tanpa mengubah kata aslinya.
  2. Separate Strings: Cara memisahkan kata pada cell tanpa mengubah nilai aslinya.
  3. Count Words: Cara menghitung jumlah karakter, menghapus spasi yang berlebihan, dll (lengkap)
  4. Text to Columns: Cara memisahkan konten dalam 1 cell menjadi kolom terpisah. Biasanya terjadi pada file csv yang di download dan sering dianggap berantakan.
  5. Find Function: Cara menggunakan Fungsi Find untuk menemukan karakter tertentu didalam file Excel Anda.
  6. SEARCH: Cara menggunakan Fungsi Search di Excel untuk mencari posisi karakter pada cell tertentu.
  7. Change Case: Cara otomatis mengubah kata menjadi huruf kapital.
  8. Remove Spaces: Cara menghapus spasi atau spasi berlebihan pada Cell. Tersedia juga cara menghapus karakter khusus pada cell.
  9. Compare Text: Cara membandingkan 2 cell dengan isi yang sama. Contoh ini menggunakan metode pembanding peka huruf besar & kecil dan tidak.
  10. Subtitute VS Replace: Cara menggunakan Fungsi Subtitute dan Replace (lengkap) untuk mengganti kata / karakter tertenu pada cell.
  11. Text: Cara menggunakan Fungsi Text untuk menggabungkan angka dan huruf kedalam 1 cell.
  12. Concatenate: Cara menggabungkan 2 atau lebih cell menggunakan Fungsi Concatenate dan operator &.
  13. Substring: Menggabungkan fungsi MID, LEFT, RIGHT, FIND, LEN, SUBTITUTE, REPT, TRIM dan MAX untuk mengekstrak karakter tertentu pada cell. Contoh, mengambil angka pada kalimat yang berbeda.

BAB 7 – Lookup & Reference

  1. Lookup & Reference: Dasar-dasar Fungsi Pencarian dan Referensi di Excel (VLOOKUP, HLOOKUP, MATCH, INDEX dan CHOSE)
  2. VLOOKUP: Cara menggunakan Fungsi VLOOKUP (lengkap)
  3. Tax Rates: Contoh kasus menghitung Pajak Penghasilan (PPH) menggunakan Fungsi VLOOKUP.
  4. INDEX and MATCH: Fungsi pengganti VLOOKUP yang lebih keren. Ikuti panduan ini jika Anda ingin fungsi pencarian tingkat lanjut diatas VLOOKUP
  5. Two-way Lookup: Mencari data tertentu berdasarkan 2 atau lebih kriteria. Seperti membuat form pencarian dengan 2 atau lebih kriteria.
  6. OFFSET: Cara menggunakan Fungsi OFFSET untuk mencari data berdasarkan lokasi (cell / sheet dll)
  7. Case-sensitive Lookup: Cara menggunakan Fungsi INDEX, MATCH, dan EXACT untuk mencari data (case-sensitive)
  8. Left Lookup: Cara mencari data ke arah kiri dari sumber. Contoh ini tidak bisa dilakukan menggunakan Fungsi VLOOKUP.
  9. Locate Maximum Value: Cara mengetahui nilai maximum pada data yang banyak. Anda juga bisa mengetahui pada cell mana nilai maximum tersebut berada.
  10. INDIRECT: Cara menggunakan Fungsi INDIRECT. Anda pasti menemukan inspirasi dari fungsi ini.
  11. Two-column Lookup: Cara mencari data berdasarkan 2 kriteria berbeda.
  12. Closest Match: Kombinasi Fungsi INDEX, MACTH, MIN dan ABS untuk mencari data yang paling mendekati kriteria Anda.
  13. Compare Two Columns: Membandingkan 2 kolom. Dapat digunakan untuk menemukan data duplikat dan unik.
  14. XLOOKUP: Jika Anda pengguna Office 365, Lebih baik menggunakan Fungsi XLOOKUP daripada VLOOKUP

BAB 8 – Financial

  1. Financial: Fungsi-Fungsi Finansial yang paling populer (PMT, RATE, NPER, PV dan FV).
  2. PMT: Cara menggunakan Fungsi PMT untuk menghitung pembayaran pinjaman berdasarkan pembayaran konstan dan suku bunga tetap (Lengkap)
  3. Loans with Different Durations: Contoh membandingkan 2 pinjaman dengan durasi yang berbeda.
  4. Investment or Annuity: Contoh menghitung Nilai masa depan dari Investasi (Fungsi FV) dan Nilai masa kini untuk anuitas (Fungsi PV)
  5. Compound Interest: Cara menghitung Bunga Majemuk di Excel
  6. Compound Annual Growth Rate: Cara menggunakan Fungsi RRI untuk menghitung Tingkat Pertumbuhan Tahunan Gabungan dari suatu investasi selama beberapa tahun.
  7. Loan Amortization Schedule: Cara membuat Jadwal Amortisasi Pinjaman. Contoh ini menggunakan gabungan beberapa Fungsi Excel.
  8. NPV: Contoh menggunakan fungsi NPV untuk menghitung nilai sekarang dari rangkaian arus kas masa depan dan mengurangi investasi awal.
  9. IRR: Contoh menggunakan fungsi IRR untuk menghitung tingkat pengembalian internal proyek (Tingkat Diskonto)
  10. Depreciation: Fungsi Excel untuk menghitung Penyusunan (Depreciation). Contoh lengkap (Fungsi SLN, SYD, DB, DDB, dan VDB)

BAB 9 – Statistical

  1. Statistical: Gambaran umum untuk Anda tentang Fungsi Statistik (AVERAGE, AVERAGEIF, MEDIAN, MODE, STANDARD DEVIATION, MIN, MAX, LARGE, dan SMALL).
  2. AVERAGE: Cara Menggunakan Fungsi Average untuk Menghitung Rata-rata (Lengkap)
  3. Negative Numbers to Zero: Trick mengubah angka negatif menjadi nol tanpa mengubah angka positif.
  4. Random Numbers: Cara menggunakan fungsi RAND, RANDBETWEEN, dan RANDARRAY untuk menghasilkan angka acak.
  5. RANK: Cara menggunakan fungsi RANK untuk menentukan peringkat dari daftar nilai.
  6. PERCENTILES and QUARTILES: Cara menggunakan fungsi PERCENTILES dan QUARTILES
  7. Box and Whisker Plot: Cara membuat Box and Wishker Plot di Excel
  8. AVERAGEIF and AVERAGEIFs: Cara menggunakan fungsi AVERAGEIF untuk menghitung rata-rata dengan 1 kriteria dan AVERAGEIFs untuk lebih dari 1 kriteria.
  9. Forecast: Cara memprediksi nilai masa depan dengan fungsi FORCAST.LINEAR dan FORECAST.ETS serta fitur Forecast Sheets
  10. MAXIFs and MINIFs: Cara menggunakan fungsi MAXIFs dan MINIFs untuk menghitung nilai tertinggi dan terendah dengan 1 atau lebih kriteria (khusus Excel 2016 atau lebih baru)
  11. Weighted Average: Cara menghitung rata-rata tertimbang / menggunakan bobot di Excel.
  12. MODE: Cara menggunakan fungsi MODE untuk mencari nilai yang sering muncul (modus).
  13. Standard Deviation: Cara menggunakan Fungsi STDEV.P untuk mencari Simpangan Baku dari Populasi dan STDEV.S untuk sample
  14. FREQUENCY: Cara menggunakan fungsi FREQUENCY untuk mengetahui seberapa sering nilai muncul dalam sekumpulan data.

BAB 10 – Round

  1. ROUND: Cara membulatkan angka pada Excel menggunakan Fungsi ROUND, ROUNDUP, dan ROUNDDOWN.
  2. Chop off Decimals: Menghilangkan desimal menggunakan fungsi INT dan TRUNC
  3. Nearest Multiple: Cara membulatkan angka berdasarkan kelipatan menggunakan fungsi MROUND, CEILING dan FLOOR
  4. Even and Odd: Cara membulatkan ke angka genap dan ganjil (fungsi EVEN dan ODD) serta mengetahui apakah suatu bilangan merupakan bilangan genap atau ganjil (fungsi ISEVEN dan ISODD)
  5. MOD: Fungsi MOD untuk mengetahui sisa bilangan setelah di bagi angka tertentu. Misalnya, 49:10 = 4 sisa 9. Maka Nilai yang akan tampil adalah 9.

BAB 11 – Formula Errors

  1. Formula Errors: Cara mengatasi kesalahan rumus / fungsi di Excel. Seperti #####, #NAME?, #VALUE!, #DIV/0!, dan #REF!.
  2. IFERROR: Buat teks / kata / nilai alternatif lain jika Excel menampilkan Error.
  3. ISERROR: Cara menggunakan fungsi ISERROR untuk mengetahui apakah rumus akan error jika digunakan pada data.
  4. AGGREGATE: Cara menggunakan fungsi AGGREGATE lengkap pada cell berisi pesan error (alternatif SUM, AVERAGE, COUNT dll jika didalam data terdapat pesan kesalahan)
  5. Circular References: Referensi melingkar untuk mencegah error.
  6. Formula Auditing: Cara cek kesalahan rumus dengan cepat.
  7. Floating Point Errors: Salah satu kesalahan perhitungan pada Excel, namun sangat jarang terjadi.

BAB 12 – Array Formulas

  1. Array Formulas: Mengenal Array pada rumus dan untuk apa gunanya
  2. Count Errors: Menggunakan Array untuk menghitung jumlah kesalahan rumus.
  3. Count Unique Values: Menghitung nilai unik menggunakan Array, Fungsi UNIQUE dll
  4. Count with Or Criteria: Menghitung dengan atau tanpa kriteria. Contoh complete.
  5. Sum Every Nth Row: Cara menjumlahkan setiap baris ke-n pada Excel dengan Array
  6. SUM Largest Numbers: Cara menjumlahkan angka terbesar dalam range tertentu.
  7. SUM Range with Errors: Cara menjumlahkan data pada range yang terdapat pesan error Excel
  8. SUM with Or Criteria: Cara menjumlahkan dengan kriteria (contoh kompleks)
  9. Most Frequently Occuring Word: Cara mencari kata yang paling sering muncul di Excel.
  10. System of Linear Equations: Cara menghitung persamaan linier pada Excel.

Prev: Level 1 | Dasar Excel

Next: Level 3 | Analisa Data