Course Details

Certificate in Excel Advanced for Business Data Analysis
Excel商業分析證書課程

Excel能瞬間將原始資料化為有實用價值的資訊,是現今企業的致勝之道。員工能活用 Excel工作,絕對能令公司更上一層樓。
Image
Course Introduction 課程簡介

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

Target and Duration 對像及時數

學習人士需懂得如何運用 Excel 基本功能,方能事半功倍。

課程為期六小時,由持有 Microsoft Excel 專家認證的豐富經驗導師任教。

Learning Targets 學習目標

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

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

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

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

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

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

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

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

Course Arrangement 課程安排

本課程針對企業客戶而設,客戶可組團到本校上課或要求導師上門授課均可。一班最多24人,價錢為 HK7,800元。 可安排一天或兩個半天上課。客戶使用軟件為 Excel 2010, 2013, 2016均可。此為標準課程內容,如需增強或減少某部分,請與客戶服務主任羅小姐 (21361234) 聯絡。

Course Content 課程內容

Cell and Range Concept

  • Understanding Relative and Absolute Addresses
  • Creating and managing Name for a Range
  • Data Types used in a cell (Number, Text, Date, Time and Boolean Values)
  • Data Formatting in a cell

Use of Formulas

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

Use of Functions

  • 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()

Charting Skills

  • 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

Sorting and Filtering

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

Conditional Formatting

  • Highligting data using conditional formatting
  • Segmenting data using conditional formatting
  • Use of formula in conditional formatting

Data Validation

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

Worksheet and Workbook protection

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

Time and Date calulation

  • 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()

Text Handling

  • 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

What-if Analysis

  • 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

Macro automation

  • 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

Sharing and Tracking Changes

  • Sharing and stop sharing the worksheets
  • Track changes and view the changes
  • Accept or rejects the changes made by others

Consolidating Workbooks

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

Lookup Functions

  • 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

Pivot Table

  • 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

Trainer Profile

Dannis Mok

who have rich experience in business web and apps system development and have over 15 years of teaching experience. He has great passion in learning and teaching new technologies. 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. He got a MBA, Msc in IT and Msc in Telecommunication.

相關專業認證
  • Microsoft MCSE, MCDBA, MCSD
  • Microsoft Office 2016 Specialist Master
  • Cisco CCNA,CCDA,CCNP,CCDP
  • Sun Microsystems – Certified Java Programmer
  • Oracle – Certified Database Professional
  • Linux - LPI Level 1 & 2
Technical Skills 相關教學經驗
  • 為積金局 IT 員工提供 Android 及 iPhone 視像培訓課程
  • 為香港教育局提供 Android 培訓課程與中學電腦科導師
  • 為香港教育大學 IT 員工提供跨平台流動程式開發課程
  • 為房屋署員工 IT 員工提供跨平台流動程式開發課程
  • 為香格里拉大酒店IT 員工提供跨平台流動程式開發課程
  • 為勞工處提供 HTML5 遊戲培訓課程及電子商店培訓課程
  • 為 VTC 職業訓練局提供各種各類 IT 培訓課程
Office Software 相關教學經驗
  • 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 亞洲聯合財務有限公司
  • 基督教香港信義會
Office Software 相關教學經驗
  • NCSI Hong Kong Ltd
  • HACEO 香港飛機工程
  • 香港善道會
  • 恆生銀行
  • 聯合國兒童基金會
  • Hong Kong VTC 職業訓練局
  • Hong Kong IVE 香港專業教育學院
  • VTech Hong Kong 偉易達香港
  • 香港明愛
  • JLL 仲量聯行
  • 救世軍
  • YWCA 女青年會
  • 連續兩年 (2017,2018) 為VTC 職業訓練局的員工作”辦公室軟件” 培訓
  • 連續五年為勞工處YES的會員作”辦公室軟件”培訓

Demo Video - Creating a Pivot Table for business data

More Questions?

Navigation

Social Media

IT Design