Thursday, February 9, 2017

Kursus MS Excel anjuran TED LEARNING

MS Excel 2013 : Excel Dashboards & Interactive Reports 

RM 1,400 per pax
course duration :   27 & 28 Feb 2017 |6 & 7 March 2017
Venue : Binjai 8 , KLCC  
Dashboard popularity is continuing to grow as organizations look for easier ways to access information within large data sets and to analyze that information in hopes of managing performance, making better decisions and lowering overall costs.
Dashboard reports allow managers to get high-level overview of the business. Essentially, a dashboard report is a way to visually present critical data in summary form so that you can make quick and effective decisions. Excel is an excellent tool to make powerful dashboards that can provide analysis, insight and alert managers in timely manner.
A dashboard is a business tool that displays a set of PIs (performance indicators), KPIs (key performance indicators), and any other relevant information to a business user.  Dashboard data is often displayed in real-time after retrieval from one or more data sources in a business. Dashboards are interactive, allowing an executive to drill into particular aspects of the display or switch between facets or views of the data.
Basic Requirements
Users must have basic knowledge of Excel charts, pivot tables, functions and formulas.

Course Contents

Chapter 1

Dashboards

Introduction To Dashboards
Excel Dashboards
What Makes Good Dashboard
Using The Proper Visualizations
Dashboard Creation Process
Why Is the Chart Type Important?

Chapter 2

Data Visualisation Tools

When to Use A Secondary Axis
Combining Two Chart Types
Formatting Tricks

Chapter 3

Analysing Data With Pivot Reports

What Are Pivot Reports?
Creating A Dynamic Range For Data Expansion
Creating a Pivot Report
Incorporating Visual Analysis With Conditional Formatting
Grouping Data

Adding Interactive Elements

Slicing & Dicing Data
Querying Pivot Reports
Creating Pivot Charts

Chapter 4

Creating Interactive Reports

An Alternative To The VLOOKUP Function
Charting Tricks
Showing Positive & Negative indicators

Variance Analysis Dashboard

Returning A Value Based On User Selection
Incorporating Form Controls
Incorporating Hyperlinks

Chapter 5

Automating Dashboards With Functions

Offset Function
CountA Function
Structuring Your Data
Linked Textbox
Named Formulas
Creating Gauge / Speedometer Charts

Chapter 6

Visual Basic For Applications

What Is VBA?
Basics of VBA & Macros
Executing A Procedure
Converting A Workbook To A Macro Enabled Workbook

Big Data using Power Query & Power Pivot 2013

RM 2,000 per pax
course duration :  27 & 28 Feb |  27 & 28 March 2017 | 27 & 28 April 2017 
Venue : Binjai 8 , KLCC  
Power Pivot is an add-in for Microsoft Excel 2013 that enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and Pivot Charts, and then further analyze the data so that you can make timely business decisions without requiring IT assistance.
This hands-on course demonstrates how to take advantage of self-service business intelligence using Microsoft PowerPivot for Excel.
Course Objectives:
During the training, you will use Power Pivot to:
  • Import external data from multiple sources.
    • Client database
    • Excel tables
    • CSV files
    • Handling massive data from multiple sheets.
    • Create relationships between multiple tables.
    • How to create a user friendly models
    • Perform calculations in Power Pivot
      • Calculated columns.
      • Calculated fields.
      • Create PivotTables and Pivot Charts.
      • Introduction to Data Analysis Expression language (DAX)
Target Audience:
Excel Power Users, data analysts, business analysts, managers, MIS professionals and BI Consultants / BI Developers.
Prerequisites:
Delegates should be proficient users of Excel and be experienced in creating Excel PivotTables & PivotCharts and also be familiar with Functions & Formulas.
At Course Completion:
Upon successful completion of this course, the delegate should be proficient with PowerPivot and have the necessary skills to implement PowerPivot applications, manipulate data and perform data analysis.

Introduction To PowerPivot

This module explains what is a power pivot and how you can download and install the power pivot on your computer.
  • What is Power Pivot
  • Limitation of the normal Pivot Table
  • The Power Pivot Interface

Handling Data In Power Pivot

This module explains how to bring your data into Excel whether it's an external source, text files, Excel data and other sources. It also shows you how to build relationships between tables which is vital to data analysis.
  • Importing Data
  • Creating Linked Tables
  • Managing Relationships
  • Creating Your Power Pivot Table

Power Pivot At Work

This module explains how to use create and format your reports as creating reports from Power Pivot may differ from a normal pivot table. It also touches on making your data model more user friendly for end users.
  • Creating and customising reports that are user friendly
  • Formatting your Numbers
  • Customising your data model
  • Performing calculations with calculated columns
  • Performing calculations with calculated fields
  • Working with dates
  • Using slicers

Introduction To DAXS

PowerPivot has its own syntax for defining calculation expressions. It is conceptually similar to an Excel expression, but it has specific functions that allow you to create more advanced calculation on data stored in multiple tables.
  • DAXS operators
  • Calculated Columns
  • Calculated Fields
  • Date calculations

Microsoft Excel VBA (Macro Programming) For Non Programmers

RM 1,400 per pax
course duration :  22 & 23 Feb |  13 & 14 March 2017 | 6 & 7 April 2017 
Venue : Binjai 8 , KLCC  
Joining in usage of both Microsoft Excel and VBA are to increase your productivity, become more powerful and flexibility to spreadsheets, automating processes and improving user interaction.
Students are required knowledge from Intermediate to Advanced of Microsoft Excel before joining this course. Students are not required programming knowledge background.
Microsoft Excel VBA training course increase user productivity thru automated and worksheets process by using the inbuilt programming language. It will benefits students with the automated worksheet in MS Excel built in programming language.   
Course Objectives
Upon completion of this Microsoft Excel VBA training course, participants should have learned Microsoft Excel VBA sufficiently to be able to:
  • Create recorded macros in Microsoft Excel
  • Use the macro recorder to create a variety of macros
  • Understand the Microsoft Excel object model and VBA concepts
  • Create command procedures
  • Create and use variables
  • Write code to manipulate Microsoft Excel objects
  • Use a range of common programming techniques

Course content

This Microsoft Excel VBA training course covers the following topics:
Day 1

Recorded Macros

  • Understanding Microsoft Excel macros
  • Setting macro security
  • Saving a workbook as a macro enabled workbook
  • Recording a simple macro
  • Running a recorded macro
  • Viewing a macro
  • Editing a macro
  • Running a macro from the toolbar
  • Assigning a keyboard shortcut to a macro
  • Tips for developing macros

Recorder workshop
  • Preparing data for an application
  • Recording a macro that updates opening balances
  • Recording a macro to open text files
  • Creating objects to run macros
  • Assigning a macro to an object

Understanding VBA

  • VBA terminology
  • About objects
  • Accessing the Microsoft Excel object model
  • Using the immediate window
  • Working with object collections
  • Setting property values
  • Working with worksheets
  • Using the object browser

Using the VBA editor

  • The VBA editor
  • Opening and closing the editor
  • Working with the project explorer
  • Working with the properties window
  • Working with a code module
  • Stepping through a procedure

Procedures

  • About procedures
  • Creating a command procedure
  • Making sense of intellisense
  • Using the edit toolbar
  • Commenting statements
  • Indenting code

Using variables

  • Understanding variables
  • Creating and using variables
  • The scope of variables
  • Procedure level scoping
  • Module level scoping
  • Passing variables by reference
  • Passing variables by value
  • Data types
  • Declaring data types
Day 2

Using Microsoft Excel objects

  • Application methods and properties
  • Workbook methods and properties
  • Viewing Microsoft Excel and the editor together
  • Using workbook objects
  • Worksheets methods and properties
  • Using worksheet objects
  • Range methods and properties
  • Using range objects
  • Using objects in a procedure

Programming techniques

  • The msgbox function
  • Sending messages to the user
  • Inputbox techniques
  • Using the inputbox function
  • Using if for multiple conditions
  • Looping with known or specified iterations
  • The do...loop statement
  • Looping with unknown iterations
Warmest Regards , 
Noor Shawani 
TED LEARNING  
E : training@ted.com.my
T : 03-2742 1828 | 03-2386 7788

No comments: