Pelajaran 1Menggunakan makro VBA untuk otomatisasi sederhana: merekam makro, mengedit modul, eksekusi aman, peristiwa buku kerja (Workbook_Open, Worksheet_Change)Otomatisasi tugas berulang dengan makro VBA, mulai dari perekaman hingga pengeditan kode di modul. Pelajari praktik eksekusi aman, gunakan peristiwa buku kerja seperti Workbook_Open dan Worksheet_Change, serta bangun otomatisasi yang andal untuk pembaruan dan pemformatan tugas.
Merekam dan menjalankan makro dasarMengedit makro di modul standarMenggunakan Workbook_Open untuk tugas startupMenggunakan Worksheet_Change untuk logika inputKeamanan makro dan eksekusi amanMenetapkan makro ke tombol dan bentukPelajaran 2Otomatisasi pembaruan: memperbarui Power Query dan PivotTable dengan VBA atau tombolOtomatisasi pembaruan Power Query dan PivotTable menggunakan VBA dan kontrol antarmuka. Buat tombol dan makro sederhana untuk memperbarui beberapa objek secara berurutan, tangani kesalahan, dan pastikan pengguna selalu melihat data terkini.
Pilihan pembaruan manual vs otomatisVBA untuk memperbarui semua kueri sekaligusMemperbarui PivotTable dengan makroTombol pembaruan pada dasborMenangani kesalahan pembaruan dan pencatatanPelajaran 3Fungsi teks dan tanggal: TEXT, DATE, EOMONTH, MONTH, YEAR, FORMAT untuk periode pelaporanGunakan fungsi teks dan tanggal untuk membangun periode pelaporan yang kuat. Kerjakan TEXT, DATE, EOMONTH, MONTH, YEAR, dan pemformatan untuk membuat label bulan, periode fiskal, dan ringkasan berbasis tanggal dinamis untuk dasbor dan laporan berulang.
Membangun tanggal dari komponen dengan DATEPerhitungan bulan, tahun, dan EOMONTHTEXT untuk label periode kustomRentang bulanan bergulir dan tahun-ke-tanggalMenangani periode fiskal vs kalenderPelajaran 4Praktik terbaik untuk versioning, cadangan, dan mendokumentasikan sumber data dalam buku kerjaTerapkan praktik terbaik untuk versioning buku kerja, cadangan, dan dokumentasi. Lacak perubahan sumber data, pertahankan log perubahan, dan sisipkan catatan jelas agar pengguna masa depan dapat memahami langkah pembaruan dan ketergantungan.
Konvensi penamaan file dan versioningStrategi cadangan dan lokasi penyimpananMempertahankan log perubahan buku kerjaMendokumentasikan koneksi data eksternalMemberi anotasi kueri dan rumus kunciPelajaran 5Rumus tingkat lanjut: XLOOKUP/VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF/IFS, logika bersarangTerapkan rumus tingkat lanjut seperti XLOOKUP, VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF, dan IFS. Bangun ekspresi logika bersarang, tangani kesalahan dengan anggun, dan rancang rantai pencarian yang kuat untuk model pelaporan kompleks.
Perbandingan XLOOKUP vs VLOOKUPINDEX/MATCH untuk pencarian fleksibelSUMIFS dan COUNTIFS dengan kriteriaIFS dan struktur logika bersarangPenanganan kesalahan dengan IFERROR atau IFNAPelajaran 6Merancang struktur buku kerja: lembar terpisah untuk Penjualan, SDM, Keuangan, Dasbor, Kamus DataRancang struktur buku kerja yang jelas dengan lembar khusus untuk Penjualan, SDM, Keuangan, dasbor, dan kamus data. Tetapkan standar penamaan, bantuan navigasi, dan pemisahan lapisan data mentah, staging, dan presentasi.
Memisahkan data, staging, dan laporanLembar khusus untuk Penjualan, SDM, KeuanganTata letak dasbor dan bantuan navigasiLembar kamus data pusatPenamaan lembar dan skema warna tabPelajaran 7Dasar-dasar Power Query: mengimpor, membersihkan, menggabungkan file departemen bergandaKuasai Power Query untuk mengimpor, membersihkan, dan menggabungkan file departemen. Konfigurasikan koneksi, terapkan langkah transformasi, gabungkan dan tambahkan tabel, serta muat hasil ke model Excel sambil menjaga kueri dapat diperbarui dan didokumentasikan dengan baik.
Menghubungkan ke folder dan buku kerjaMembersihkan dan membentuk tabel mentahMenggabungkan dan menambahkan file departemenMengelola langkah kueri dan kesalahanMemuat kueri ke tabel atau model dataMendokumentasikan logika dan sumber kueriPelajaran 8Validasi data dan input terkontrol: dropdown, daftar, peringatan kesalahan untuk paste bulananKontrol input pengguna dengan aturan validasi data, daftar, dan peringatan kesalahan. Bangun dropdown untuk template paste bulanan, batasi entri tidak valid, dan rancang pesan ramah yang memandu pengguna sambil melindungi rumus dan struktur.
Membuat kontrol dropdown berbasis daftarRumus validasi kustom untuk aturanPesan input dan peringatan kesalahanMemvalidasi template paste bulananMengunci struktur sambil mengizinkan inputPelajaran 9Kolom dan ukuran yang dihitung: kolom tabel terstruktur vs ukuran PivotBedakan kolom yang dihitung di tabel dari ukuran di PivotTable dan Model Data. Pelajari kapan menggunakan masing-masing, bagaimana mereka memengaruhi kinerja, dan cara merancang perhitungan yang dapat digunakan kembali untuk pelaporan konsisten di seluruh pivot.
Membuat kolom yang dihitung di tabelMendefinisikan ukuran di Model DataDasar konteks baris vs konteks filterMemilih antara kolom dan ukuranMenggunakan kembali ukuran di seluruh PivotTablePelajaran 10Menggunakan Tabel Excel dan rentang bernama untuk rentang dinamis dan referensi terstrukturManfaatkan Tabel Excel dan rentang bernama untuk membuat rentang dinamis dan referensi terstruktur. Konversi rentang ke tabel, gunakan nama tabel dalam rumus, dan definisikan rentang bernama yang diperbarui secara otomatis seiring pertumbuhan data.
Mengonversi rentang menjadi Tabel ExcelReferensi terstruktur dalam rumusBaris total dan ringkasan berbasis tabelRentang bernama dinamis dengan rumusTabel yang memasok PivotTable dan grafikPelajaran 11Logika kondisional untuk bendera dan kategori: IF dengan pencarian, SWITCH, CHOOSEGunakan logika kondisional untuk membuat bendera dan kategori yang mendorong analisis. Gabungkan IF dengan fungsi pencarian, dan terapkan SWITCH dan CHOOSE untuk menyederhanakan logika bersarang, membuat model lebih mudah diaudit dan disesuaikan seiring waktu.
Meninjau pola IF dan IF bersarangIF dengan bendera XLOOKUP atau VLOOKUPMenggunakan SWITCH untuk logika multi-kondisiMenggunakan CHOOSE untuk pemilihan skenarioMengaudit dan menguji rumus logikaPelajaran 12Bekerja dengan rentang besar secara efisien: rumus array, perilaku spill, fungsi LETPelajari cara menangani rentang besar dengan array dinamis, rentang spill, dan fungsi LET. Pahami pertimbangan kinerja, penanganan kesalahan, dan cara mengganti rumus array lama dengan pola perhitungan modern yang dapat dipelihara.
Rumus array CSE lama vs array dinamisMemahami dan mengontrol rentang spillMenggunakan LET untuk menyederhanakan rumus kompleksMenggabungkan LET dengan FILTER dan SORTTips kinerja untuk rentang array besar
