Certificate in Excel Advance

Course Overview

Excel 高級商務課程，需要學員有一定的Excel相關經驗。我們的導師由樞紐分析表(Pivot Table)開始解釋如何使用Excel製作大量的報告 ，以及如何在日常工作中快速處理大量的數據，並且完成檢查的工作確保資料的準確性 (完成公司中的Do-check-review工作流程)。所有學員完成課程後將能夠熟練地操作Excel 製作出樞紐分析表, 樞紐分析表圖表 (Pivot Table, Pivot Charts), 更加深入地運用邏輯函數，同時使用多個 If 函數，完成大量檢查數據的工作。例如: If + Iserror + Vlookup 組合。

本課程融合了多年的實戰經驗，令各位學員能夠充分利用及了解MS Office各版本的特點。由1997年的紮實基礎到 Office 365 版本的最新技術，都能夠靈活運用。

歡迎到本電腦中心的網上報名系統報讀以上課程，網站連結： https://www.ExcelProEdu.com.hk

如有任何課程查詢或預約報名歡迎致電 3702-0208 或電郵 info@excelproedu.com.hk 與我們聯絡！
 

課程內容：

Introduction to Pivot Table

• What is Pivot Table
• Insert a Pivot Table
• Prepare Data for pivot table
• Setting Filter, Columns, Rows and Values
• Using Value field settings to summarize values
• How to use 2016 Recommended Pivot Table
• Adding blank line between fields
• Look for a data solution

Common Skills and Tricks

• Link Tables
• Link Tables for same files, cross files, new documents and websites
• Link Table file path caution
• Advance logical test and functions
• Applying IF AND OR function for business senarios
• Applying Mixture of IF AND OR functions
• Understanding the Logical functions
• Strategic approach to Multiple complex IF functions
• Application of complex mixtures
• ISERROR function
• International Excel data checking approach
• Data validation with data in sequence
• Cross data validation approach with VLOOKUP
• Cross data validation approach with IF ISERROR VLOOKUP
• Import data from a website
• Import financial, retail and other data from databases
• Import financial, retail and other data from ACCESS
• Advance data checking

Data clearing and management

• Applying Trace Precedents
• Applying Trace Dependents
• Applying Trace to investigate data calculations
• How to Remove Trace Arrows
• Using Show All formula
• Using Evaluation to debug complex Excel Formulas
• Data checking approach with Evaluation
• Using Group to manage large data
• Applying Ungroup to grouped data
• Applying Filter with advance scenarios
• Using Remove Duplicate to delete repeated data
• Applying Text to Column to handle exported data
• Handling system data from CSV, TEXT and Excel files
• Rearrange data by using Sort by Alphabets, Values and Icons