Unisoft Education

Office - Excel

Certificate in Excel Advanced for Business Data Analysis

Master your data, Master your life Excel商業分析證書課程

課程簡介

根據 Microsoft 的調查，只有約20%的人士能真正運用 Excel 的所有功能。大部分人士都只是懂得基本的 Excel運作，並不能將 Excel發揮極致。本課程為一個 Excel 進階課程，專為有志加強 Excel知識的人士而設。學習課程後，學員可充分利用 Excel 各項功能，使工作效率培升。

配合 Big Data 大數據的趨勢， Excel的全新分析工具 PowerPivot 將資料分析提升至另一層次。公司管理人員能輕易將數以百萬行的資料匯入 Excel 作出深度分析，不需使用Vlookup 也能輕易將資料表以視覺化的方法連結起來，製作出充滿互動性及精美的數據分析儀錶板(Dashboard)，讓公司各種 KPI 指標靈活顯示出來，使管理人員能瞬間掌握公司的業務狀況。

課程目標

Formula

懂得因應情境運用不同的地址格式編寫公式

Pivot Table

懂得如何建構樞紐分析表分析資料

Data Protection

懂得如何保護資料及追蹤修訂

Sort and Filter

懂得如何排序，篩選及條件化格式資料

Data Breakdown

懂得將資料分割及重組作不同的分析

Charting

懂得建構圖表表達資訊及追蹤趨勢

Functions

懂得活用各種公式及 Functions 作資料處理

Macros

懂得使用 Marco 將重複動作自動化

Data Model

如何由不同來源匯入資料至資料模型

Transformation

如何將匯入的資料作不同形式的重組及變形

Relationships

如何為資料表建立不同類型的關係

Measure

如何利用DAX functions 寫出各種實用的分析公式

課程相關資料

課程對象針對企業客戶而設
上課模式面授上課或者即時網上教學
上課安排客戶可到本校上課或要求導師上門授課
上課收費每班最多20人，每班收費$8,800
課程時數6小時
使用軟件Excel 2010, 2013, 2016. 2019, Office 365 均可
入學要求需懂 Excel 基本功能，方能事半功倍

課程內容

Understanding how to enter and amend a formula
Evaluate formula to debug it
Showing formula dependencies
Use of brackets in formula
Understanding relative and absolute addresses

How to get help on functions
Basic big 5 functions (SUM,COUNT,AVERAGE,MAX,MIN)
How to rank data using RANK(), RANK.EQ()
Logical functions - IF(), IFERROR()
Summarizing using logical functions: SUMIF(), COUNTIF(), AVERAGEIF()
Applying multiple conditions using SUMIFS(), COUNTIFS(), AVERAGEIFS()

Time calculation using TIME() function
Date calculation using DATE() function
Breaking down the date and time into components
Finding number of work days using WORKDAYS() and NETWORKDAYS()

Breaking down the text using LEFT(),RIGHT(),MID() functions
Use of Text to Column Wizard to break down the text
Combining text together using operator & and CONCATNEATE() function
Searching text using SEARCH() or FIND() functions

How to create a chart based on data provided
Drawing 4 basic chart type (Column, Bar, Line, Pie)
How to format various elements of a chart
How to add different trendlines and predict trends
How to use Combo charts and use of secondary axis

Basic sorting and filtering skills
Use of Text filter, Number filter and Date filter
Advanced filtering using Criteria range

Highlighting data using conditional formatting
Segmenting data using conditional formatting
Use of formula in conditional formatting

Restricting users to enter allowable values
Providing input tips to users
Customizing error messages for wrong data input

Protect the content in worksheet from changes
Protect the structure in workbook from changes
Allow portion of the worksheet editableg
Set password to open for read or write

Recording the macros for repeated operations
Editing the macro content using VBA editor
Copying macros between different workbooks
Setting shortcut and icon for playing back the macros

Use of Goal Seek to find target value given a condition
Use of Scenario Manager to set different scenario outcomes
Use of Data Tables to show different data set combination

Set a template for sharing with others
Consolidating workbooks by matching ranges
Consolidating workbooks by using formulas

Use of Vlookup and Hlookup functions to lookup values
Understanding the usage of Range search and Exact search
Use of Match and Index to overcome the shortcomings of Vlookup

Understanding and creating Pivot Table from data source
Summarizing data from different perspectives
Summarizing data using different functions and formats
Creating custom grouping in data source
Use of Slicers to filtering data
Creating Pivot Charts based on Pivot Table
Formatting Pivot report using different tools
Use of Custom Fields to derive new data

Using PowerQuery to import data into Excel
Transform data using PowerQuery
Understanding PowerPivot (Data Model)
Adding tables into PowerPivot
Join tables using relationships
Creating Pivot Tables and Pivot Charts using PowerPivot
Introducting DAX functions
Create the calculated columns and measures
Create the KPI for measures

Instructor

Dannis Mok

who has rich experience in business web and apps system development and have over 20 years of teaching experience. He has great passion in learning and teaching new technologies and his teaching style is clear to point and can explain complex technologies in simple terms. He has delivered various workshops and classes for different corporate, government departments and local universities and is the principal lecturer for NCC Education and University of Greenwich. Besides BSc degree in IT, he also got a MBA, MSc in IT and MSc in Telecommunication degree.

企業客戶