About Course:

Data Analysis Master Program covered Advance Excel, VBA/Macros, MS Access, SQL, Power BI & Python Practical Training which is designed by industry expert. Data Analysis training course consist of study material, corporate assignment with live data of banking & Finance, workforce operations, HR reports etc. Inside the Data Analytics course has deep understanding of concepts & logic, Data Analysis, Automation Reporting, create dashboard, user forms in Access, SQL Queries to Figure meaningful data, Visualization in Tableau/Power BI, Python and basic of Business Intelligent etc.  Sage Academy, Data Analysis Course is to unlock the great career in good companies.

Data Analysis professional course are currently available at our Delhi on weekdays or weekends.

 

Who can learn this course:

Sage Academy is a rapidly developing corporate practical training and development company based in Delhi that helps people to learn new skills and become job-worthy. Enrolling in our Data analytics program that is helpful to career enhancement of experienced professionals or Freshers. People who have taken a sabbatical from their careers like Graduates can also learn new skills and become employable with the help of our practical training and certifications courses. We promise to meet all your training and development needs and make you an expert in a few weeks. Trust us once and you’ll be happy you did.

 

Data Analytics Master Certification Training Program

 

Module 1 - Advanced MS Excel and Back-End Operations: Introduction to Advanced MS Excel:-

 

Circular References error, Rectify Circular Reference, Formula Auditing, Structured Reference Solve The Real World Excel Problems with Functions:

 

Module 1 - Mathematical Functions:

Abs, Sum, Sumif, Sumifs,Count, Counta, Countif, Countifs,Countblank, Average, Averagea, Averageif, Averageifs.

Subtotal, Aggregate, Rand, Randbetween, Roundup, Rounddown, Round, Sumproduct

 

Module 2 - If Formulas:

If, If with OR, If with AND, If with AND &OR, If with Countif, If With Sumif.

If with Trim, If with Concatenation, If with Left, Mid, Right.

If with Other formulas, Complex formulas writing in If.

Nested If (For Multiple Conditions), If condition used more than one time in the same formula.

 

Module 3 - More If Formulas

Nested if with Multiple Text Functions, TAX Calculation, Other Critical Lookup Formulas

Nested if with VLook-up And Hlookup.

Introduction to Name Manager: Name Ranges and Apply the Name Ranges on the combination of Cells.

 

Module 4 - Date & Time Function

Date, Day, Month, Year, Edate, Eomonth, Networkdays, Workday, Weeknum, Weekday, Hour, Minute, Second, Now, Today, Time

 

Module 5 - Statistical Function & Other Functions 

Isna, Isblank, Iserr, Iseven, Isodd, Islogical, Isytext, Max, Min, Mid, Maxa, Maxifs, Median, Minifs, Mina, Var, Vara, Correl, Geomen, Rank, Percentile, Frequency.

Information Function: IsOdd, IsEven, IsError, IsNumber, IsText, IsBlank, IsRef, ISNA Some Other Data Base Functions :Dsum,DCount, DAverage, DMax, Dmin

 

Module 6 - Lookup & Reference Functions

Discussion on Lookup Functions, Use of Lookup, Vlookup, Hlookup, Index, Indirect Match, Offset, Choose, Rows, Columns, Transpose.

Vlookup, Hlookup with Name Range And Match .

Find Data In Opposition by Index Match.

 

Module 7 - Array With Multiple Formulas                                                                           

Array in Multiple formulas, Array with Lookup functions.

Array With Sumifs, Countifs, Sumproduct , Large Functions.

 

Module 8 - Text Functions & Data Validation 

Char, Clean, Code, Concatenate, Find, Search, Substitute, Replace, Len, Right, Left, Mid, Lower, Upper, Proper, Text, Trim, Value, Large, Small, Filters (Basic, Advanced, Conditional), Sort (Ascending, Descending, Cell/ Font Color), Conditional Formatting, Data Validation, Group & Ungroup, Data split.

 

Module 9 - Pivot Table and Pivot Charts with Slicer

Do the Multiple Field Setting in Pivot Table.

Pivot form Multiple Source of Data, Data Ranges, Name Range.

Group Pivot Table Items, Multi-level Pivot Table, Calculated Field/Item

Perform the % calculation on the basis of multiple fields, Using Slicer.

 

Module 10 - Advanced Chart Technique

How To Make Dynamic Charts, Bar Charts, Pie Charts, Scatter Chart, Line Chart, Column Chart, Speedometer Chart, Gantt Chart, Pareto Charts, Advanced Dashboard

 

Module 11 - Report Development

 

Module 12 - VBA - Macros & Dashboard Programming

Quick Review of Macros

Introducing the Visual Basic Editor

Uses of record Macros

Understanding and creating modules

 

Module 13 -  Programming Concepts

VBA Sub and Function Procedures

How to create a message box

Write a Program to update and retrieve information using Input Box

Understanding and using Select Case statement

How do I define a variables and Rules for defining a Variables Name and Type

Creating And using Variables

Working with range Objects

How to save and Protect Modules

 

Module 14 - Decision Makers

If……Then……Else

If……Then……ElseIf……If

 

Module 15 -  Other Kinds of Loops

Working with Do While u. Loop Procedure

Do…… Until Loop and Do…… Loop Until

Do……While loop and Do…… Loop While

For each…… Next

For……Next

 

Module 16 -  Workbooks Objects

Workbook Save and Save AS

Open Single and Multiple Workbooks

Close Specify and Multiple Workbooks

Activate From one workbook to another Workbook

Open Workbook from Specific Path

Get Workbook Name and Paths

Hide and Unhide for Single and Multiple Workbooks

Protect and Unprotect Worksheets

 

Module 17 - Worksheet Objects insert a single and Multiple Worksheets

Delete Specific and multiple worksheets

Get Count of Worksheets

Select a Specific and all Worksheets

Get All Worksheets Name

Hide and Unhide For Single and Multiple Worksheets

Rename for Single and Multiple Worksheets

Protect and unprotect worksheets

Sort and Move worksheets

Calculate entire worksheet

Using VBA and worksheet Function

 

Module 18 -  Cell objects insert Single and multiple Row , Column and Cells

Delete Single and Multiple row, Column and Cells

Get Range or Address of Cell and Selection

Navigate from one Cell to another Cell

Select specific Range, Cell, Rows and Column

Types of Selection and Offset method

Insert Function In cell

 

Module 19 -  Reading and Writing Arrays

Defining Arrays

 

Module 20 - Form Controls and User Forms (Create and Design an user Form)

Working With User Forms & User Forms Events Like List Box, Combo Box, Option Buttons, Check Box, Text Box, Labels, Command Button, Toggle Button.

How To Create Dynamic Dashboard On User Form With Different Controls

How To Link Various User Form With Each Other To Create A Complete Interface Between User And System.

 

Module 21 - VBA Programming Functions

Create a Sum Functions

Create Multiply Function

Create Count Function

Extract Text & Number

Proper Function

Vlookup Function

Public or Private function

 

Module 22 -  Excel VBA Power Programming for VBA Macros

Working with Dynamic Ranges. Protecting worksheets, Cells and Ranges. Working with Multiple Files. Opening &saving Files

How to Analyze Data On multi Worksheets And Build Summary sheets

How to Access the Windows File and Folder System to Open and Close Workbooks

How to protect your code Against Errors

How to create Your own custom Business Worksheet Function in VBA

How to create Basic Report Generation Tools Using Excel VbA, Microsoft Word and PowerPoint

How to use the Excel Visual Basic Macro record Excel Tasks in VBA And then Interpret the code

How To Establish Connection Between Excel Vba And Outlooks Through Vba

Effective Error Handling

Automation Development Reports & Live Projects

 

Module 23 - Understanding MS Access Databases

Starting and Opening an Existing Database

Moving Around in Access

Understanding Datasheet View & Design View

Using the Mouse Pointer to Navigate

Using the Keyboard to Navigate

 

Module 24 - Creating Tables

Creating a Database

Creating a Table Using the Wizard

Creating and Modifying a Table

Adding Fields to Tables

Adding and Editing Records

Printing Tables

Moving and Deleting Fields

Deleting Records

Formatting a Table

Modifying Field Properties

Sorting Records in a Table

Finding Records in a Table

Using Filters with a Table

Establishing Relationships Between Tables

Creating Subdatasheets

Importing Records From an External Source

 

Module 25 - Forms & Reports Development

Creating a Form Using Auto Form

Creating a Form Using the Form Wizard

Adding Controls to a Form

Modifying Control Properties

Resizing and Moving Controls

Entering Records into a Form

Creating a Report Using Auto Report

Creating a Report Using Report Wizard

Adding a Control to a Report

Formatting a Report

Resizing and Moving Controls

Creating Calculated Controls

 

Module 26 - Creating Queries & SQL

Creating and Running a Query

Specifying Criteria in a Query

Using Comparison Operators

Creating a Calculated Field

Creating a Multiple-Table Query

Relational database concepts, specific products

SQL syntax rules

Data definition, data manipulation, and data control statements

Getting acquainted with the course database and editor

Clauses

The SELECT clause: columns and aliases, where expressions, order by expressions how null Values behave

Eliminating duplicates with DISTINCT arithmetic expressions

Replacing null values

Numeric operations, including rounding

Date and time functions

Nested table expressions

Case logic

Other expressions in specific DBMS Products

The INSERT, UPDATE and DELETE statements

Column constraints and defaults

Referential integrity constraints

Module 27 - Automating Tasks

Using Controls to Run a Macro

Assigning a Macro to an Event

Assigning a Macro to a Condition

Testing and Debugging a Macro

 

Module 27 - Microsoft POWER BI Desktop, Custom Visuals

Report Visuals, Fields, Pages and Filters

Data and Relationship Option, PBI Canvas

Get Data from DAT Files, Excel Files, Access Files

PBIX and PBIT Files And Re-Using Reports

Data Import Options Designing Simple / Basics Reports in PBI

Visual Interactions in Power BI - Options Spotlight Options with Visuals, Real-time Use

Slicer Visual in Power BI and Data Filters

Grouping and Binning with Fields, Bin Size and Biz Limits (Max, Min)

Creating Hierarchies. Drilldown, Drill Up Reports

Filters : Types and Usage in Real-time, Conditional Filters, Visual Filters, Page Filters, Report Filters

Drill-thru Filters with Hierarchy Levels TOP N Filters – Usage Filtering at Category Level, Import and Direct Query with Power BI.

Fields, Formats and Analytics Options

Table Visuals & Properties, Data Bar and Data Scaling Options

Divergent Colors and Data Labeling Matrix : Sub Totals, Grand Totals

Row Groups and Column Groups in Matrix

Slicer Visual - Properties, Alignment Single Select and All Options

Chart Reports - Common Properties Axis, Legend Types- Stacked Bar, Column, Line charts Clustered Bar, Column, Line Chart

Tree Map, Funnel and Gauge Reports, Map Reports

Single Row Card and Multi Row Cards

Callout Values in KPI Reports and Use, Indicator, Trend and Target Goals in KPIs

Using Buttons, Images in Power BI Canvas

Bookmarks in Power BI Desktop – Usage Using Bookmarks for Visual Filters

 

Module 28 - Data Modeling with Power Query

Power Query Usage & Operation Types, QUERY Concept, Properties, Validations

Power Query - Data Mash Up Operations

Basic Data Types, Literals and Values, Expressions

Primitives in M Language, Structured Data Values in Power Query

LIST, RECORD, TABLE, Connection Format Settings let, source, in statements in M Lang Functions, Parameters in Power Query

INVOKE Functions & Execution Results

Power BI Canvas: Edits, Applied Steps, Frowns, Query Header Row Formatting

Power Query Transformations Categories

Query Combine & Merge Transformations, Join Options In Merge Transformation

Truncate, Replace, Split, Reduce Rows, Manage Columns, Hide / Show Columns Grouping, Aggregations, Column Formats

Transpose, Reverse Rows Transformations

Power Query - Row Count And Replace, Data Type Detection - Scenarios, Use

Data Type Conversions And Value Replace Fill Up And Fill Down,Pivot And Unpivot Transformations

Move, Filter And Converttolist() Split, Format, Merge, Extract, Parse, Date,Time

Module 29 - Data Modeling with DAX

DAX as library of Functions, Types, Variables, Operators Dax Formula With Excel, Limitations

DAX Architecture and Entity Sets

Rules OF DAX, Working Options, Syntax, Functions

ROW Context and Filter Context, DAX Structures and Syntax Options

Creating and Measuring with DAX Creating and Using Columns with DAX

Data Modeling Options in DAX, Detecting & Adding Relations for DAX

Power BI DAX Functions - Types, Usage, Cheat Sheet

Power BI Reports - DAX Functionalities Calculated Columns, Aggregated Measures

Quick Measures in DAX - Auto validations, DAX Performance Date and Time & Text Functions, Logical & Mathematical Functions

Data Modeling with DAX. Creating Roles

SELECTEDVALUE, FORMAT Functions RELATED, COUNTROWS CALCULATE, SUM, ALL

Report Development

 

Module 30 - Python Program with Data Analysis: Basic

Using Arithmetic Operators in Python

The Double Equality Sign

How to Reassign Values

Understanding Line Continuation

Indexing Elements

Structuring with Indentation

Operators

Create Functions with Parameters

 

Module 31 - Sequences, Iterations, Date and Time

Lists, List Slicing, Tuples, Dictionaries

For Loops, Conditions

Lists with the Range () Function

How to Use Date & Date Time Class

Calendar in Python

Python Text Calendar

 

Module 32 - Data Cleansing with Python

Sub Setting / Filtering / Slicing Data

Functions, Indexing or referring with column names/rows

Mutation of table (Adding/deleting columns or Rows)

Binning data (Binning numerical variables in to categorical variables)

Sorting (by data/values, index) - By one column or multiple columns - Ascending or Descending

Type conversions, Setting index

Handling duplicates /missing/Outliers

Creating dummies from categorical data (using get_dummies())

Applying functions to all the variables in a data frame (broadcasting)

Data manipulation tools (Operators, Functions, Packages, control structures, Loops, arrays etc.)

 

Module 33 - File Handling and Web Scrapping

File Objects, File Different Modes and Object Attributes

How to create a Text Fil and Append Data to a File and Read a File

Closing a file, Read, read line, read lines, write, write lines…!!

Renaming and Deleting Files

Directories in Python

Working with CSV files and CSV Module

Web Scraping with Beautiful Soup, Urllib2, Data frames

 

Module 34 - Data Handling and Manipulation

Introduction to NumPy and SciPy

Introduction To Type of Data Variables

Data Summarization Techniques

Building A Data Dictionary

Outlier Treatment

Missing Value Treatment.

Data manipulation using Pandas

Import and export

Database access with SQL.

 

Module 35 -Data Analytics and Visualization

Introduction to Seaborn and Matplotlib

Plotting with Matplotlib

Types of Charts & Graphs (Line, Bar, Histogram, Pie Chart, Scatter Plot)

 

Module 36 - Descriptive statistics And Inferential statistics

Measures of central tendency

Measures of dispersion

Bivariate, Univariate Analysis

Range, Skewness

Interpretation of histograms

Research methodologies

Basics of probability theory. Bayes Theorem

Probability distribution functions – Uniform, Bernoulli, Binomial, Normal, Log Normal, T

Continuous probability distributions

Hypothesis testing – 1 Sided test, 2-Sided tests. F test. T test. Chi Sq Test. ANOVA

Statistics and visualization using Python.

 

Module 37 -Time series forecasting and Clustering

Learn basic concepts of time series modeling

Smoothing techniques

Decomposition, Understanding the fundamentals of ARIMA

ARIMA modeling, model estimation & interpretation

Forecasting with regression and time series data

ARIMAX or dynamic regression models to build forecasting models with multiple regressors

Case Study = Case study on predicting sales for a large European retailer using real data.

Types of clustering & their uses

K-Means clustering

Hierarchical clustering

Case Study = Case study on retail customer segmentation using K Means clustering techniques on real data

Basic techniques for forecasting