Pelajaran 1Menggunakan makro VBA untuk automasi mudah: rakam makro, edit modul, pelaksanaan selamat, peristiwa buku kerja (Workbook_Open, Worksheet_Change)Automasikan tugas berulang dengan makro VBA, dari merakam hingga mengedit kod dalam modul. Pelajari amalan pelaksanaan selamat, gunakan peristiwa buku kerja seperti Workbook_Open dan Worksheet_Change, dan bina automasi boleh dipercayai untuk tugas penyegaran dan pemformatan.
Merakam dan menjalankan makro asasMengedit makro dalam modul standardMenggunakan Workbook_Open untuk tugas permulaanMenggunakan Worksheet_Change untuk logik inputKeselamatan makro dan pelaksanaan selamatMengekalkan makro pada butang dan bentukPelajaran 2Automasi penyegaran: menyegarkan Power Query dan PivotTables dengan VBA atau butangAutomasikan penyegaran Power Query dan PivotTables menggunakan VBA dan kawalan antara muka. Bina butang dan makro mudah untuk menyegarkan berbilang objek secara berurutan, urus ralat dan pastikan pengguna sentiasa melihat data terkini.
Pilihan penyegaran manual vs automatikVBA untuk menyegarkan semua pertanyaan serentakMenyegarkan PivotTables dengan makroButang penyegaran pada papan pemukaMengendalikan ralat penyegaran dan logPelajaran 3Fungsi teks dan tarikh: TEXT, DATE, EOMONTH, MONTH, YEAR, FORMAT untuk tempoh pelaporanGunakan fungsi teks dan tarikh untuk membina tempoh pelaporan yang kukuh. Bekerja dengan TEXT, DATE, EOMONTH, MONTH, YEAR dan pemformatan untuk mencipta label bulan, tempoh fiskal dan ringkasan dinamik berdasarkan tarikh untuk papan pemuka dan laporan berulang.
Membina tarikh dari komponen dengan DATEPengiraan bulan, tahun dan EOMONTHTEXT untuk label tempoh tersuaiJulat bulanan bergulung dan sehingga tarikhMengendalikan tempoh fiskal vs kalendarPelajaran 4Amalan terbaik untuk versining, sandaran dan mendokumentasikan sumber data dalam buku kerjaLaksanakan amalan terbaik untuk versining buku kerja, sandaran dan dokumentasi. Jejak perubahan pada sumber data, kekalkan log perubahan dan selit nota jelas supaya pengguna masa depan boleh faham langkah penyegaran dan pergantungan.
Konvensyen penamaan dan versining failStrategi sandaran dan lokasi storanMengekalkan log perubahan buku kerjaMendokumentasikan sambungan data luaranMencatat pertanyaan dan formula utamaPelajaran 5Formula lanjutan: XLOOKUP/VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF/IFS, logik bersarangTerapkan formula lanjutan seperti XLOOKUP, VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF dan IFS. Bina ungkapan logik bersarang, urus ralat dengan baik dan reka rantaian carian kukuh untuk model pelaporan kompleks.
Perbandingan XLOOKUP vs VLOOKUPINDEX/MATCH untuk carian fleksibelSUMIFS dan COUNTIFS dengan kriteriaIFS dan struktur logik bersarangPengendalian ralat dengan IFERROR atau IFNAPelajaran 6Mereka struktur buku kerja: lembaran berasingan untuk Jualan, HR, Kewangan, Papan Pemuka, Kamus DataReka struktur buku kerja yang jelas dengan lembaran khusus untuk Jualan, HR, Kewangan, papan pemuka dan kamus data. Tetapkan standard penamaan, bantuan navigasi dan pemisahan lapisan data mentah, pentas dan persembahan.
Memisahkan data, pentas dan laporanLembaran khusus untuk Jualan, HR, KewanganSusun atur papan pemuka dan bantuan navigasiLembaran kamus data pusatPenamaan lembaran dan skim warna tabPelajaran 7Asas Power Query: mengimport, membersih, menggabung fail jabatan bergandaKuasai Power Query untuk mengimport, membersih dan menggabung fail jabatan. Konfigurasikan sambungan, terapkan langkah transformasi, gabung dan lampir jadual serta muat hasil ke model Excel sambil mengekalkan pertanyaan boleh disegarkan dan didokumentasikan dengan baik.
Menyambung ke folder dan buku kerjaMembersih dan membentuk jadual mentahMenggabung dan melampir fail jabatanMengurus langkah pertanyaan dan ralatMemuat pertanyaan ke jadual atau model dataMendokumentasikan logik dan sumber pertanyaanPelajaran 8Pengesahan data dan input terkawal: jatuh-turun, senarai, amaran ralat untuk tampal bulananKawal input pengguna dengan peraturan pengesahan data, senarai dan amaran ralat. Bina jatuh-turun untuk templat tampal bulanan, hadkan kemasukan tidak sah dan reka mesej mesra yang membimbing pengguna sambil melindungi formula dan struktur.
Mencipta kawalan jatuh-turun berasaskan senaraiFormula pengesahan tersuai untuk peraturanMesej input dan amaran ralatMengesahkan templat tampal bulananMengunci struktur sambil membenarkan inputPelajaran 9Lajur dikira dan ukuran: lajur jadual berstruktur vs ukuran PivotBezakan lajur dikira dalam jadual daripada ukuran dalam PivotTables dan Model Data. Pelajari bila menggunakan setiap satu, bagaimana ia mempengaruhi prestasi dan cara mereka pengiraan boleh digunakan semula untuk pelaporan konsisten merentasi pivot.
Mencipta lajur dikira dalam jadualMendefinisikan ukuran dalam Model DataAsas konteks baris vs konteks penapisMemilih antara lajur dan ukuranMenggunakan semula ukuran merentasi PivotTablesPelajaran 10Menggunakan Jadual Excel dan julat dinamik untuk julat dinamik dan rujukan berstrukturManfaatkan Jadual Excel dan julat dinamik untuk mencipta julat dinamik dan rujukan berstruktur. Tukar julat kepada jadual, gunakan nama jadual dalam formula dan tentukan julat dinamik yang dikemas kini secara automatik apabila data berkembang.
Menukar julat kepada Jadual ExcelRujukan berstruktur dalam formulaBaris jumlah dan ringkasan berasaskan jadualJulat dinamik dinamik dengan formulaJadual memberi makan PivotTables dan cartaPelajaran 11Logik bersyarat untuk bendera dan kategori: IF dengan carian, SWITCH, CHOOSEGunakan logik bersyarat untuk mencipta bendera dan kategori yang memacu analisis. Gabungkan IF dengan fungsi carian dan terapkan SWITCH dan CHOOSE untuk memudahkan logik bersarang, menjadikan model lebih mudah diaudit dan disesuaikan dari masa ke masa.
Mengkaji corak IF dan IF bersarangIF dengan bendera XLOOKUP atau VLOOKUPMenggunakan SWITCH untuk logik berbilang syaratMenggunakan CHOOSE untuk pilihan senarioMengaudit dan menguji formula logikPelajaran 12Bekerja dengan julat besar secara cekap: formula array, tingkah laku limpah, fungsi LETPelajari cara mengendalikan julat besar dengan array dinamik, julat limpah dan fungsi LET. Fahami pertimbangan prestasi, pengendalian ralat dan cara menggantikan formula array lama dengan corak pengiraan moden yang boleh dipelihara.
Formula array CSE lama vs array dinamikMemahami dan mengawal julat limpahMenggunakan LET untuk memudahkan formula kompleksMenggabungkan LET dengan FILTER dan SORTPetua prestasi untuk julat array besar
