Office Specialist Study Plan - 辦公室專才修讀計劃

Big Data Analysis + Business Workflow Automation

商業大數據分析及工作流程自動化，是提升辦公室效率的兩大利器。

課程介紹

本修讀計劃目標是訓練學員高級辦公室軟件技術。

未來辦公室需要的人才，是要能活用軟件創造價值。例如能否有數據處理能力，有效處理公司各部門的資料。能否綜合不同的資料來源，利用公式及圖表預測未來趨勢。除了數據能力之外，亦能夠利用自動化工具去協助將重複的工作變成自動化。例如可以將請假流程自動化，批核流程自動化，出單流程自動化以及訂貨流程自動化等等。

本課程分為四部分，互相獨立，沒有依賴。學員可以因應需求個別報讀或一次個報讀四個單元，一次個報讀會有文憑證書及學費減免。

第一部分 - Excel 數據分析
此部分將訓練學員所有 Excel的高級功能，更加會教授 Power Pivot，將Excel能力提升一級。

第二部分 - PowerBI 數據分析
這個免費的微軟軟件是熱門的資料分析工具，學員可以利用 PowerBI 建立大量美觀的圖表，以及建立 Measure 作高級資料分析。

第三部分 - Python 數據分析
如果需要將資料分析提升到另一境界，便需要利用 Python程式語言配合 Excel以及 PowerBI 去強化數據處理能力，超越 Excel 一百萬行的限制。

第四部分 - Power Automate 流程自動化
這個免費的微軟軟件是協助公司打造自動化流程的 RPA 工具，學員可利用它配合 SharePoint 以及各種雲端服務，建立雲端流程及桌面流程，令繁瑣的工作自動化。

課程片段

PowerBI Relationship (08m:59s)

Python Pandas (06:32)

Excel PowerPivot (04m:47s)

PowerAutomate Auto Sum Up (06:32)

課程結構

本課程由4個教授部分所組成 (共有 8 堂 - 24 小時面授課程) 學員可因應自己需求導讀個別單元或整個課程

Advanced Excel and PowerPivot

PowerBI Data Analysis

Python Data Analysis

Power Automate

課程內容

Excel

Advanced Excel + PowerPivot

2 堂面授課程 + 6 小時課程錄影

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

Certificate in Advanced Excel with PowerPivot (CPD2023)

適合初學者修讀，無需任何經驗，由淺入深教學

26th Oct 2023
7:30pm - 10:00pm
Every Thursday
2 lessons

$1,080

Enroll Online

What you can learn ?

Learn how to use formula using absolute address
Learn how to lookup data using various methods
Learn how to use text functions to break down text
Learn how to use date functions to calculate dates
Learn how to use highlight data by conditions
Learn how to perform what if analysis
Learn how to setup Pivot table
Learn how to record Macros for automation
Learn how to use Power Pivot to build model
Learn how to use Power Query to transform data
Learn how to use measures to store formula

Excel

Technology	Introduction
Excel	辦公室必備軟件。任何文職人員或管理層均需要使用 Excel, 能有效運用 Excel 將決定你的辦公室表現。
Excel Power Pivot	Power Pivot 有類似 Power BI 的功能, 是 Excel 上的一個免費的插件。安裝後便可以如 Power BI 一樣, 能匯聚不同的資料來源,並建立資料模型作出不同的分析。

COURSE OUTLINE

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

Data Analysis

Power BI

2 堂面授課程 + 6 小時課程錄影

Power BI 及 Excel Power Pivot 是 Microsoft 專門用來做資料分析及製作互動圖表的免費工具。隨著大數據時代的的來臨，任何公司都會有大量的資料可供分析，從而發現商業智慧。我們會一步一步學習如何利用這些工具，將不同的資料作出適當的整理並格式化 (Power Query Editor)，然後將這些資料適當地建立關係 (Relationships) 形成資料模型 (Data Model)。最後便可以倚靠這些模型協助製作出互動圖表 (Dashboard)，並學習使用 DAX functions 作出深度的分析超越 Excel 的數據分析能力。

Certificate in PowerBI Data Analysis (CPD2023)

適合初學者修讀，無需任何經驗，由淺入深教學

9th Nov 2023
7:30pm - 10:00pm
Every Thursday
2 lessons

$1,280

Enroll Online

What you can learn ?

Learn how to transform data in suitable format
Learn how to build the relationships between tables
Learn how to build the data model (star or snowflake)
Learn how to create various visualizations
Learn how to create report and dashboard
Learn how to upload the report to PowerBI Services
Learn how to use the PowerBI mobile apps
Learn how to write the DAX functions
Learn how to use the calculated columns
Learn how to create the measures
Learn how to create the KPI metrics

Power BI

Technology	Introduction
Power BI	Power BI 是 Microsoft 一個免費的資料分析及圖像化的工具。用家可利用 Power BI 連接不同的資料來源, 然後建立資料模型。再利用模型建立各種視覺圖像以及支援 DAX functions 作資料分析。
Excel Power Pivot	Power Pivot 有類似 Power BI 的功能, 是 Excel 上的一個免費的插件。安裝後便可以如 Power BI 一樣, 能匯聚不同的資料來源,並建立資料模型作出不同的分析。

COURSE OUTLINE

Installing and running PowerBI Desktop
Applying PowerBI Service free account
Installing PowerBI Mobile Apps
Understanding the PowerBI Desktop and Query Editor

Importing Excel data into PowerBI Desktop
Importing Access database into PowerBI Desktop
Importing CSV text files into PowerBI Desktops
Importing data from other data sources

Define data types for different columns
Transform existing columns (text,number,date)
Adding and removing columns or rows
Unpivot data by transposing columns and rows
Modifying query and undoing steps)

Appending multiple queries
Merging queries by different join types
Query from a folder
Understanding M language
Conditional columns and Add Column from Example

Understanding Star schema and Snowflake schema
Building the 1 to many and 1 to 1 relationships between tables
Understanding filter context and its flow direction
Understanding active and inactive relationships
Adding a Date table for time intelligence functions

Buidling various reports by using the visuals
Choosing various visuals for presenting data
Introducing Matrix,Columm,Stack,Bar,Pie,Waterfall,KPI,Gauge,Maps
Grouping and binning data
Applying various filters (Visual,Page,Report,Drillthrough)

Uploading data and reports from PowerBI Desktop
Building a PowerBI dashboard
Preparing data sets for Q&A natural language queries
Sharing dashboard to other PowerBI accounts
Accessing the reports through PowerBI Mobile

Creating calculated columns and measures using DAX functions
Understanding implicit and explicit measures
Using CALCULATE functions to alter the filter context
Using FILTER functions to filter data
Introduction to Time Intelligence functions
Understanding implicit and explicit measures
Using RELATED and RELATEDTABLES functions to relate data
Understanding SUMX,COUNTX,AVERAGEX,MAXX,MINX functions to summarize data
Creating calculated table by using SUMMARIZE functions

Understanding the limitations of Excel Pivot Table
Using Excel Power Query to load data into Power Pivot
Transform data using Excel Power Query
Build the relationships between the tables in Power Pivot
Create the Pivottable from Power Pivot data model
Create the Pivotchart from Power Pivot data model
Using the Slicer and Timeline to filter data

Create the calculated columns using DAX functions
Create the measures using DAX functions
Understanding the functions of Date table
Create the KPI metrics for each measure
Combining measures to create new measures
Export data from the data model

Data Analysis & Workflow Automation

Python + Excel

2 堂面授課程 + 6 小時課程錄影

Python 是一個易學易用的通用語言，新一代 Excel 將會有 Python 整合在內。利用派 Python 及其相關的程式庫 (Modules)，可以作出資料分析 (Data Analysis)，建立機械學習模型 (Machine Learning)，或者實行辦公室流程自動化(Workflow Automation)等功能。我們會一步一步教授 Python 的基本知識，學習基本編程。以及運用 Python 強化 Power BI 及 Excel的分析能力，及使用 Python 建立 Excel 流程自動化。

Certificate in Python Data Analysis (CPD2023)

適合初學者修讀，無需任何經驗，由淺入深教學

12th Oct 2023
7:00pm - 10:00pm
Every Thursday
2 lessons

$1,280

Enroll Online

What you can learn ?

Learn how to use variables to store simple data
Learn how to use List and Dictionary data structures
Learn how to use decision making
Learn how to use looping
Learn how to use Pandas to store data
Learn how to import and export Pandas data
Learn how to use draw charts using Matplotlib
Learn how to control Excel using OpenPyXL
Learn how to automate Excel operations
Learn how to use Python with PowerBI
Learn how to enhance PowerBI with Python

Python

Technology	Introduction
Python	Python 是一個通用的程式語言,被喻為最適合初學者修讀的程式語言。被廣泛應用在資料分析機械學習等領域。
Pandas	Pandas是一個專門為資料分析而設計的 Python Module。他的資料結構類似 Excel Table, Excel使用者可以很快掌Python Pandas的運作。
Matplotlib	Matplotlib 是一個 Python 專門用來繪圖的 Module。我們可以利用 Python 程式碼或繪畫各種各類的圖表。
OpenPyXL	OpenPyXL 是一個用來接駁 Excel 的 Python Module。可以利用他來控制 Excel各項各類事項,可以取代 Excel VBA 的功能。

COURSE OUTLINE

Environment Setup (Jupyter and VS Code)
Basic Syntax
Variable Types
Basic Operators
Decision Making
Loops (For, While)
Numbers and Strings
Lists and Tuples
Dictionary and Set
Functions and Lambda
List Comprehension
Modules (DateTime, Math, JSON, CSV)

Pandas Series and DataFrames
Pandas Read CSV and JSON
Change Column, Rows and Data Types
Select Rows by Index Position or Labels
Select Columns by Name or Index
Add Column or Rows to DataFrame
Drop Column or Rows From DataFrame
Iterate Over Rows
Apply functions
Join, Merge and Concat DataFrames

Read Excel file as Pandas DataFrame
Data checking for null, unique value and formatting
Data cleaning for null, spaces, cases and duplicates
Data preprocessing by merging, sorting, grouping and breakdown
Data extraction by location, by label, by condition
Data filtering by conditions (AND,OR,NOT)
Data summary by subtotal and pivot
Data output to Excel or to CSV

OpenPyXL Introduction
Read Excel File
Iterating Rows and Columns
Iterating from a range
Create and Add Content to a Workbook
Write a List to Worksheet
Rename, Add and Remove Worksheet
Insert and Delete Rows and Columns
Set the Font, Alignment and Color for the Cells
Use OpenPyXL to consolidate Excel invoices distributed in different files and sheets

Configure PowerBI with Python
Get Data using Python Scripts
Update the Python Script
Create the Visual using Python
Transform Data using Python
Introduction of Regular Expression
Regular Expression - Match, Search and Sub function
Example 1 - Remove Bad Email Address Rows
Example 2 - Mask out all phone numbers and ID card numbers
Example 3 - Call external API to process data (Using Google Geocoding API)

Workflow Automation

Power Automate Desktop + SharePoint

2 堂面授課程 + 6 小時課程錄影

Microsoft Windows 365 是一個建立辦公室操作環境的雲端服務，他的核心功能是基建於 SharePoint 及 OneDrive。再配搭其他服務例如 Outlook , Calendar, Teams, Form 和雲端版 Word，Excel，PowerPoint，能協助任何形式的企業利用雲端服務打造辦公室環境。配合 Power Automate 串連不同的服務 , 使工作流程自動化。如再配合免費的 Power Automate Desktop 便能將雲端及桌面的工作流程打通, 使流程的覆蓋面更廣泛更全面。大大加強工作效率及減少出錯機會。

Certificate in Workflow Automation using Power Automate and SharePoint (CWP2023)

適合初學者修讀，無需任何經驗，由淺入深教學

19th Oct 2023
7:00pm - 10:00pm
Every Thursday
2 lessons

$1,280

Enroll Online

What you can learn ?

Learn how to create cloud flow
Learn how to create desktop flow
Learn how to schedule the flow to run
Learn how to make decision in flow
Learn how to use loop in flow
Learn how to automate Excel operations
Learn how to send customized buld emails
Learn how to setup the approval flow
Learn how to use the SharePoint lists in flow
Learn how to use the SharePoint libraries in flow
Learn how to use the SharePoint forms in flow

Workflow

Technology	Introduction
Power Automate	Power Automate 是 Microsoft 一個雲端 Workflow 引擎。可以用來接駁各種各類的雲端服務,從而將工作流程自動化。
Power Automate Desktop	Power Automate Desktop 是 Microsoft 一個免費的 RPA (Robotic Process Automation) 工具,安裝在電腦後, 便可將電腦上的工作流程自動化。
SharePoint	SharePoint 是 Windows 365的核心服務。它可以建立文件資料庫(Document Library) 及共享清單 (List), 取替以往辦公室的共享 File Server。
Windows 365	Windows 365 前身叫 Office 365, 是 Microsoft 用來建構雲端辦公室的工具。它擁有 SharePoint, OneDrive, Teams, Forms 及雲端版的 Word, Excel, PowerPoint。

COURSE OUTLINE

Understanding different types of flow
Components of a flow - connector, actions, and triggers
Using the Power Automate Web Portal
Using the Power Automate Mobile Apps
Build the flow from templates

Build a flow using email connectors
Reading and Filtering Email
Saving attachments to OneDrive or SharePoint
Using the Apply to each function to handle multiple attachments

Build a flow using file connectors
Making an archive for email attachments in SharePoint
Publishing files to Dropbox
Using Expression to format the folder name

Building a button flow using the template
Creating a button flow to email a manager
Executing a button flow using the mobile

Learning about push notifications
Configuring a notification for emails from the manager
Using the Notification connector and condition control

Sharing a cloud flow with others
Sharing a flow with run-only permission
Managing the shared flows

Understanding condition operators
Using expressions and multiple conditions (AND or OR)

Understanding Dataverse
Create an approval flow
Responding to approvals

Working with sequential approvals
Working with parallel approvals
Adding parallel branches

Understanding the Forms connector
Creating a basic form
Processing a form

Installing Power Automate Desktop
Create a basic desktop flow
Understanding variables and the expression
Using lists and data tables to store data
Create decision making and loops

Build the flow with Excel
Reading data from Excel
Writing data to Excel
Looping and handle the data in the Excel range
Accessing the file and the folders
Create a flow to check stocks and fill in orders

Understanding the UI elements
Create the flow by recording
Create the flow by capturing the UI elements
Create the flow to capture data from the web

Office Specialist Study Plan - 辦公室專才修讀計劃

Big Data Analysis + Business Workflow Automation

商業大數據分析及工作流程自動化，是提升辦公室效率的兩大利器。

課程介紹

課程片段

PowerBI Relationship (08m:59s)

Python Pandas (06:32)

Excel PowerPivot (04m:47s)

PowerAutomate Auto Sum Up (06:32)

課程結構

本課程由4個教授部分所組成 (共有 8 堂 - 24 小時 面授課程) 學員可因應自己需求導讀個別單元或整個課程

課程內容

Advanced Excel + PowerPivot

2 堂面授課程 + 6 小時課程錄影

Certificate in Advanced Excel with PowerPivot (CPD2023)

What you can learn ?

COURSE OUTLINE

Use of Formulas

Use of Functions

Time and Date Calculation

Text Handling

Charting Skills

Sorting and Filtering

Conditional Formatting

Data Validation

Worksheet and Workbook Protection

Macro Automation

What-If Analysis

Consolidating Workbooks

Lookup Functions

Pivot Table

PowerQuery and PowerPivot

Power BI

2 堂面授課程 + 6 小時課程錄影

Certificate in PowerBI Data Analysis (CPD2023)

What you can learn ?

COURSE OUTLINE

Understanding PowerBI

Importing Data to PowerBI

Data Cleansing and Transformation

Advanced Transformation

Design a Data Model

Report and Visualizations

PowerBI Services and Mobile

DAX functions

Excel Pivot Table

DAX functions and Measures

Python + Excel

2 堂面授課程 + 6 小時課程錄影

Certificate in Python Data Analysis (CPD2023)

What you can learn ?

COURSE OUTLINE

GETTING STARTED WITH PYTHON

WORKING WITH PANDAS

EXCEL DATA HANDLING WITH PYTHON

EXCEL AUTOMATION USING OPENPYXL

WORKING WITH POWERBI

Power Automate Desktop + SharePoint

2 堂面授課程 + 6 小時課程錄影

Certificate in Workflow Automation using Power Automate and SharePoint (CWP2023)

What you can learn ?

COURSE OUTLINE

Introducing Power Automate

Working with Email

Working with Files

Creating Instant cloud flow

Generating Push Notifications

Working with Shared Flows

Working with Conditions

Getting With Approval flow

Working with Multiple Approvals

Working with Microsoft Forms

Working with Power Automate Desktop

Working with Excel and Files

Working with UI elements

Diploma in Big Data Analysis and Workflow Automation

你將會學到

Excel Data Analysis

Power BI Data Analysis

Python Data Analysis

本課程由4個教授部分所組成 (共有 8 堂 - 24 小時面授課程) 學員可因應自己需求導讀個別單元或整個課程