返回主页
Anne Teoh
  • ☛  Certified Microsoft Excel Expert
  • ☛  Certified Train The Trainer - PSMB
  • ☛  Certified as Microsoft Office Specialist Excel,Word,Powerpoint (2013/2016)
10月 19日-20日 2020年(一至二) 7小时 / 2天
09:00AM - 12:30PM RM250
中文



☛  Who is this course for?
This course is designed for Clerks, Officers, Executives, Supervisors, Administrators, Managers of all levels; and personnel who already know and understand and want to further enhance their knowledge and practical uses of Microsoft Excel.
☛  Prerequisites:
   ⚬   Basic knowledge of Microsoft Excel is essential with the following pre-requisites:
   ⚬   • Have attended Microsoft Excel: Foundation & Intermediate Level; OR
   ⚬   • Able to switch between task applications
   ⚬   • Able to create simple to complex formulas and functions, like:
   ⚬   • COUNTA, COUNTIF & COUNTIFS Function
   ⚬   • AVERAGEA, AVERAGEIF & AVERAGEIFS Function
   ⚬   • SUMIF & SUMIFS Function
   ⚬   • IF Functions
   ⚬   • Nested Functions
   ⚬   • Database Function
   ⚬   • Able to validate data in a Worksheet
   ⚬   • Able to apply Filter data using Auto & Advanced Filters
   ⚬   • Able to clean Duplicate Records

☛  Methodology:
   ⚬   This program will be conducted with interactive lectures, PowerPoint presentation, discussions and practical exercise

☛  Course Objectives:
   ⚬   Upon completion of this program, participants should be able to create:
   ⚬   • Subtotal Using the Subtotal Function
   ⚬   • Analyze data Using Pivot Tables
   ⚬   • Perform What If Analysis using:
   ⚬   o Goal Seek
   ⚬   o Solver
   ⚬   o Input Table
   ⚬   o Scenarios
   ⚬   • Use VLOOKUP function to extract data
   ⚬   • Nesting INDEX and MATCH Function
   ⚬   • Combining & Consolidating Data
   ⚬   • Record and Run a Macro

Course Outline

Module 1: Getting the Most from Your Data
☛   Topic A: Outlining and Grouping Data
   ⚬    • Using Automatic Outlining
   ⚬    • Displaying and Collapsing Levels
   ⚬    • Grouping Data Manually
   ⚬    • Creating Subtotals

Module 2: What If Analysis?
☛   Topic A: Using Data Analysis Tools
   ⚬    • Using a One or Two Input Data Table
   ⚬    • Using Goal Seek
☛   Topic B: Exploring Scenarios
   ⚬    • What is a Scenario?
   ⚬    • Creating a Scenario
   ⚬    • Saving Multiple Scenarios
   ⚬    • Creating a Scenario Summary Report
☛   Topic C: Using Solver
   ⚬    • Understanding Solver
   ⚬    • Generating Reports and Scenarios with Solver
   ⚬    • Changing Solver Values
   ⚬    • Managing Solver Constraints
   ⚬    • Using Solver as a Goal Seek Tool

Module 3: Advanced Excel Task
☛   Topic A: Working with Array Formulas
   ⚬    • What are Array Formulas?
   ⚬    • Defining Basic Array Formulas
   ⚬    • Using Functions within Array Formulas
   ⚬    • Using the IF Function in Array Formulas
   ⚬    • Using IFERROR with Array Formulas
☛   Topic B: Using the VLOOKUP Function
   ⚬    • Understanding VLOOKUP and HLOOKUP
   ⚬    • Using VLOOKUP to Find Data
   ⚬    • How to Find an Exact Match with VLOOKUP
   ⚬    • Finding an Approximate Match with VLOOKUP
   ⚬    • Using VLOOKUP as an Array Formula
☛   Topic C: Using the Advanced Function
   ⚬    • Using the INDEX Function
   ⚬    • Using the MATCH Function
   ⚬    • Combining the MATCH and INDEX function
☛   Topic D: Linking, Consolidating, and Combining Data
   ⚬    • Linking Workbooks
   ⚬    • Consolidating Workbooks
   ⚬    • Combining Worksheets

Module 4: Advanced Chart
☛   Topic A: Create Advanced Chart
   ⚬    • Dual Axis Charts
   ⚬    • Creating Custom Chart Templates

Module 5: Analyzing Data with PivotTable, Slicer and PivotCharts
☛   Topic A: Create a PivotTable
   ⚬    • Start with Questions End with Structure
   ⚬    • Create PivotTable Dialog Box
   ⚬    • PivotTable Fields Pane
   ⚬    • Summarize Data in a PivotTable
   ⚬    • Show Values As Functionality of a PivotTable
☛   Topic B: Filter Data by Using Slicer
   ⚬    • Slicers
   ⚬    • Insert Slicers Dialog Box
☛   Topic C: Analyzing Data with PivotChart
   ⚬    • Creating PivotChart
   ⚬    • Applying a Style to a PivotChart

Module 6: Enhancing Workbooks
☛   Topic A: Customizing Workbooks
   ⚬    • What is a Hyperlink?
   ⚬    • Inserting Hyperlinks
   ⚬    • Editing Hyperlinks
   ⚬    • Formatting Hyperlinks
   ⚬    • Using Hyperlinks in Excel

Module 7: Macros and Form Controls
☛   Topic A: Macros
   ⚬    • Recording Macros
   ⚬    • Saving to Macros Enabled Workbook
   ⚬    • Closing and Re-Opening a Macro Workbook
   ⚬    • Security Warning Message
   ⚬    • Playing a Macro
   ⚬    • Assigning a Keystroke to a Macro
☛   Topic B: Form Controls
   ⚬    • What is a Form Control?
   ⚬    • Adding a Control to a Worksheet
   ⚬    • Assigning a Macro to a Control
   ⚬    • Using Form Controls

Module 8: Publishing & Protection
☛   Topic A: Finishing Workbook
   ⚬    • Publishing to PDF or XPS
☛   Topic B: Protecting Worksheet and Workbook
   ⚬    • Protecting the Current Sheet
   ⚬    • Protecting an Entire Workbook
   ⚬    • File Protection



线上课程购票流程

1. 确认课程后, 点击购买
2. 请选择购票的数量
3. 请填写您的个人资料:(必须填写,我们将在购票后向您发送线上课程的链接)
      -   姓名
      -   联络电话
      -   电邮地址
      -   公司名字
      -   公司地址
4. 付款
5. 付款成功后您将收到电子邮件通知。
6. 我们会电邮线上课程Zoom的链接给您。

所有线上课程都将透过Zoom来进行。请在上课前把Zoom安装好。




联系我们 | 隐私条款 | 使用者条例 | 常见问题