Certificate in Excel Advance

課程編號: CEA1001

完成課程後您能學會

所有學員完成課程後將能夠熟練地操作Excel 製作出樞紐分析表, 樞紐分析表圖表 (Pivot Table, Pivot Charts), 更加深入地運用邏輯函數，同時使用多個If 函數，完成大量檢查數據的工作。Eg. If + Iserror + Vlookup組合

Certificate in Excel Advance 商務課程簡介

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

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

課程特色：適合高速及集中的你，快速學習全部技術，投入日常工作中提升效率 。

課程時數：共 1 堂  每堂 3 小時

課程材料：筆記一份，練習檔案一份。

上課模式：一人一機，真人導師教授課程。

公司培訓：本課程適用於公司團體培訓， 詳情可與我們職員聯絡。

報名資格：課程適合任何人士報讀

報名方法：1）網上即時報名  2)  銀行入數報名

上課地點：銅鑼灣

立即報名：按此報名

證書認可 : 完成課程後可以申領證書一份

課程內容：

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
 Insert a Pivot table in a worksheet
 Rearrange data fields for Pivot Table
 Apply business data management
 Apply business analysis with formulas
 How to refresh with data updates
 How to accommodate with new data
 Table layout setting
 Pivot table reporting settings
 New version vs Old version
 Pivot table design
 How to use 2016 Recommended Pivot Table
 Adding blank line between fields
 Look for a data solution

Insert Charts for Pivot Tables
 How to insert charts for Pivot Tables
 Using Recommended charts for Pivot Tables
 Controlling pivot chart by pivot table fields
 How to manipulate pivot charts
 Setting graphic designs for pivot charts
 Adding and removing field head from pivot charts
 Insert Bar Chart
 Insert Pie Chart
 Insert Line Chart
 Insert Area Chart
 Apply trend analysis to pivot charts
 Apply linear forecast to pivot charts
 Moving the pivot charts to PowerPoint and Word
 Pivot Chart reporting 

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
 Rand function
 Randbetween function
 International Excel data checking approach
 Data validation with data in sequence
 Data validation with data calculations
 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
 Special Paste – Paste with same column width
 Special Paste – Paste with Values only
 Special Paste – Paste with Transpose of data
 Special Paste – Paste with Linked tables
 Special Paste – Paste with Pictures and Linked Pictures 

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
 Using Remove Duplicate to simulate a Pivot Table
 Advance analysis by using Remove Duplicate
 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