Data Analyst Certification Practical Training Course Modules -

Module 1 - Advanced MS Excel and Excel 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:

Mathematical Functions:

• Abs, Sum, Sumif, Sumifs,Count, Counta, Countif, Countifs,Countblank, Average, Averagea, Averageif, Averageifs.
• Subtotal, Aggregate, Rand, Randbetween, Roundup, Rounddown, Round, Sumproduct

If Formulas:

• If, If with OR, If with AND, If with AND &OR, If with Countif, If With Sumif.
• If with Other formulas, Complex formulas writing in If.

More If Formulas:

• Nested if with Multiple Text Functions, TAX Calculation, Other Critical Lookup Formulas
• Introduction to Name Manager: Name Ranges and Apply the Name Ranges on the combination of Cells.

Date & Time Function:-

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

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 2 - VBA - Macros & Dashboard Programming

What is VBA & How VBA Works?

• Quick Review of Macros
• Introducing the Visual Basic Editor
• Uses of record Macros
• Understanding and creating modules

Programming Concepts

• VBA Sub and Function Procedures
• 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

Decision Makers

• If……Then……Else
• If……Then……ElseIf……If

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

Workbook Objects Create or Add Single and Multiple Workbooks

• 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

vWorksheet 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
• Protect and unprotect worksheets

Module 3 - MS Access & SQL

MS Access is a Relational Database Management System (RDBMS) Topics Covered as follows:

MS - Access:-

Understanding 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

Creating Tables

• Creating a Database
• Creating a Table Using the Wizard
• Creating and Modifying a Table
• Adding Fields to Tables
• Moving and Deleting Fields
• Deleting Records

Working with Tables

• 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
• Importing Records From an External Source

Designing a Form

• Creating a Form Using Auto Form
• Creating a Form Using the Form Wizard
• Adding Controls to a Form
• Resizing and Moving Controls
• Entering Records into a Form

Designing a Report

• 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 and Using Queries

• Creating and Running a Query
• Using Comparison Operators
• Creating a Calculated Field
• Creating a Multiple-Table Query

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

SQL:-

SQL Overview

• Relational database concepts, specific products
• SQL syntax rules
• Data definition, data manipulation, and data control statements
• Getting acquainted with the course database and editor

SQL SELECT statements

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

SQL Functions and Expressions

• 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

SQL Updating

• The INSERT, UPDATE and DELETE statements
• Column constraints and defaults
• Referential integrity constraints

SQL Joins

• Inner joins with original and SQL 92 syntax
• Table aliases
• Left, right and full outer joins, Inner joins
• Self-joins

Introduction to Power BI:- Power BI Component, Types of Reports in Real-time Usage, Power BI Tools and Implementation Plan Power BI Licensing and Excel Analytics.

Power BI Desktop Tool:

• 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

Hierarchies & 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.

Power Bi Visuals:

• Fields, Formats and Analytics Options
• Table Visuals & Properties, Data Bar and Data Scaling Options
• Divergent Colors and Data Labeling Matrix : Sub Totals, Grand Totals
• 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

Power Bi Visuals with Different Types:

• 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