Diploma in Big Data Analysis and Workflow Automation

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

Big Data and PowerAutomate Course

課程介紹

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

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

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

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

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

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

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

課程結構

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

Excel Advanced Course
Excel Advanced Course

Advanced Excel and PowerPivot

Power BI Course
Power BI Course

PowerBI Data Analysis

Python Course
Python Course

Python Data Analysis

PowerAutomate Course
PowerAutomate Course

Power Automate


課程內容

Excel

Advanced Excel + PowerPivot

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

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

Big Data Course

Excel Advanced Course

Certificate in Advanced Excel with PowerPivot (CPD2023)

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

  • 29th Feb 2024
  • 7:00pm - 9:30pm
  • Every Thursday
  • 2 lessons

$980

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
  • Learn how to create dashboard
Excel
TechnologyIntroduction
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
  • Create the Dashboard


Data Analysis

Power BI

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

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

Big Data and Data Analysis

PowerBI Logo

Certificate in PowerBI Data Analysis (CPD2023)

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

  • 18th Jan 2024
  • 7:00pm - 9:30pm
  • Every Thursday
  • 2 lessons

$980

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
TechnologyIntroduction
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 流程自動化。

Big Data and Data Analysis

Python Logo

Certificate in Python Data Analysis (CPD2023)

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

  • 1st Feb 2024
  • 7:00pm - 9:30pm
  • Every Thursday
  • 2 lessons

$980

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
TechnologyIntroduction
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 便能將雲端及桌面的工作流程打通, 使流程的覆蓋面更廣泛更全面。大大加強工作效率及減少出錯機會。

Big Data and Data Analysis

PowerAutomate Logo

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

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

  • 22nd Feb 2024
  • 7:00pm - 9:30pm
  • Every Thursday
  • 2 lessons

$980

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
TechnologyIntroduction
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

Diploma in Big Data Analysis and Workflow Automation

由淺入深逐步了解資料分析的各種技巧

  • Excel and PowerPivot Data Analysis
  • PowerBI and DAX Data Analysis
  • Python Data Analysis with Excel and PowerBI

除了資料分析外, 亦可協助公司打造各種自動化的工作流程 (Power Automate + SharePoint) 。 自動化不僅能大大減輕人力需求, 亦能減少出錯機會 , 是辦公室軟件專才必須掌握的知識。

Card image

本課程的目標是培訓數據分析及能建立自動化流程的專才。 課程分為多部分, 學員可選擇報讀個別單元, 亦可選擇一次報讀所有單元。

除了即時面授課程之外, 亦會有課堂錄影片段可供回顧。 觀看期為期一年, 學員可無限重溫重播。

你將會學到


Excel Data Analysis

  • 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

Power BI Data Analysis

  • 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

Python Data Analysis

  • 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

Power Automate

  • 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

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.

Microsoft MOS Master Microsoft MOS Word Microsoft MOS Excel Microsoft MOS PowerPoint Microsoft MOS Access CompTIA Data Plus
相關專業認證
  • Microsoft MCSE, MCDBA
  • Microsoft Certified System Developer
  • Microsoft Office Specialist Master
  • Cisco CCNA,CCDA,CCNP,CCDP
  • Sun Microsystems – Certified Java Programmer
  • Oracle – Certified Database Professional
  • Linux - LPI Level 1 & 2
  • Microsoft Certified: Power BI Data Analyst Associate
相關教學經驗
  • 為積金局 (MPF) IT 員工提供 Android 及 iPhone 視像培訓課程
  • 為香港教育局提供 Android 培訓課程予中學電腦科導師
  • 為香港教育大學 IT 員工提供 Cordova 跨平台流動程式開發課程
  • 為房屋署員工 IT 員工提供 HTML5 跨平台流動程式開發課程
  • 為房屋署員工 IT 員工提供 Android 及 iPhone 平台流動程式開發課程
  • 為香格里拉大酒店IT 員工提供 Android 流動程式開發課程
  • 為勞工處提供 HTML5 遊戲培訓課程及電子商店培訓課程
  • 為中國銀行IT 員工提供 Android 及 iPhone 流動程式開發課程
  • 為香港郵政IT 員工提供 Angular 8 程式開發課程
  • 為 VTC 職業訓練局提供各種各類 IT 培訓課程
  • 為醫管局員工 IT 員工提供跨平台流動程式開發課程

Diploma in Big Data Analysis and Workflow Automation

課程學費: $2,980
Package Discount
  • 面授課程8堂
  • 開課時間2024-1-18 開始 逢星期四 7:00pm-9:30pm
  • 觀看時限12個月
  • 入學要求無需任何程式設計背景,有興趣請便可報讀
  • 課程代碼:DNR06202201