🏢 企業培訓 · Office - Excel

Certificate in Excel Advanced for Business Data Analysis
Master your data, Master your life

✨ Master your data, Master your life

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

WhatsApp 查詢查看課程內容

課程時數（小時）

每班最多人數

$8,800

每班收費

20+

導師教學年資

★★★★★

課程簡介

掌握 Certificate in Excel Advanced for Business Data Analysis 的實戰應用

Master your data, Master your life

Learning Objectives

課程目標

透過實作掌握企業工作所需的核心技能。

✓

Formula

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

✓

Pivot Table

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

✓

Data Protection

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

✓

Sort and Filter

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

✓

Data Breakdown

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

✓

Charting

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

✓

Functions

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

✓

Macros

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

✓

Data Model

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

✓

Transformation

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

✓

Relationships

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

✓

Measure

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

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

Dannis Mok

Principal Lecturer · NCC Education / University of Greenwich

Dannis has rich experience in business web and apps system development and over 20 years of teaching experience. He has delivered workshops for corporate clients, government departments and local universities.

企業客戶

導師曾教授以下客戶 - 辦公室軟件或者相關課程

Prince Hotel 香港太子酒店
Marco Polo Hong Kong Hotel 馬哥孛羅香港酒店
Kerry Warehouse 嘉里貨倉
Labor Department 勞工處
Baguio Green Group 碧瑤綠色集團
香港耆康老人福利會
Education Bureau 教育局
Hong Kong Institute of Education 香港教育大學
Hong Kong Housing Authority 香港房屋委員會
Hong Kong ICAC 香港廉政公署
Hang Seng Bank 恒生銀行
Civil Aivation Department 民航處
Darty Asia
UA Finance 亞洲聯合財務有限公司
基督教香港信義會
NCSI Hong Kong Ltd
香港善道會
恆生銀行
聯合國兒童基金會
City University of Hong Kong 香港城市大學
香港明愛
救世軍
City Facilities Management Holdings Ltd
HACEO 香港飛機工程
JLL 仲量聯行
Hong Kong VTC 職業訓練局
Hong Kong IVE 香港專業教育學院
Adidas Hong Kong
Polyplastics
Apex Logistics
Defond 德豐
JAS Worldwide
VTech Hong Kong 偉易達香港
VTech Hong Kong 偉易達香港
YWCA 女青年會
東華三院
iRobot Hong Kong
Bureau Veritas
Boardway 百老滙
StarLite Holdings (星光集團)
Puma Hong Kong
Marriott International 萬豪國際
WheeLock會德豐
香港立信德豪會計師事務所 (BDO)
Kering Group
中華電力有限公司
Wilko Worldwide Limited
華懋集團
機電工程署
無國界醫生
Unilever Hong Kong 聯合利華
獅王 (Lion Corporation)
菱電商事株式会社
Merck & Co 默克藥廠
九龍木球會
香港航空發動機維修服務有限公司
維他奶國際集團
Toyota 豐田汽車
adidas Hong Kong
連續五年 (2017,2018,2019,2020,2021) 為VTC 職業訓練局的員工作培訓
連續八年為勞工處YES的會員作”辦公室軟件”培訓
浸會大學
西門子 Siemens
Fujitsu Hong Kong
Luxasia
Miele
Pacific Coffee
PersolKelly
Schmoll Group
Johnson & Johnson
周大福集團
Tory Burch
南洋商業銀行
新鴻基
迪士尼樂園
一田百貨

Our Clients

與各大企業機構同行

我們曾為多間銀行、政府部門及大學提供培訓服務。

WhatsApp 9045 5522 Call Us 2136 1234 Ask a Question

WhatsApp 致電查詢 Ask Question

Certificate in Excel Advanced for Business Data AnalysisMaster your data, Master your life

課程簡介

掌握 Certificate in Excel Advanced for Business Data Analysis 的實戰應用

課程目標

Formula

Pivot Table

Data Protection

Sort and Filter

Data Breakdown

Charting

Functions

Macros

Data Model

Transformation

Relationships

Measure

Dannis Mok

相關專業認證

企業客戶

與各大企業機構同行

相關課程 Related Courses

Enrollment Form for Excel Level 1

Please fill in the details

Enrollment details

Your enrollment details are as follows:

Class Code:

First Name:

Last Name:

Email:

DOB:

Mobile:

Other Phone:

Gender:

Chinese Name:

Address:

Enrollment Confirmation

Thank for your enrollment

We have already reserved a seat for you and wee will contact you soon to inform your payment procedures

Enquiry Form

Please fill in the details

Enquiry Confirmation

Thank for your Enquiry

We have received your question and will feedback you soon

Success

Error

Thank for your Enquiry

We have received your question and will feedback you soon

Certificate in Excel Advanced for Business Data Analysis
Master your data, Master your life