I

Mathematical Functions:- Sum, Sumif, Sumifs, Count, Counta, Countblank, countif, countifs, average, average, average, average, subtotal, total, rand, randbetween, roundup, rounddown, round, sumproduct

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

Text Functions & Data Validation:- Sort, Sort, Search, Options, Change, Lane, Right, Left, Middle, Lower, Upper, Reasonable, Text, Trim, Value, Large, Small Filters (Basic, Advanced, Conditional)(Ascending, Descending, Cell/ Font Color), Conditional Formatting, Data Validation, Group & Ungroup, Data split.

Statistical Function & Other Functions:-Isna, Isblank, Isr, Itaven, Isod, Isylical, Eastext, Max, Min, Lane, Right, Left, Mid, Max, Maxifs, Median, Minifs, Meena, Wind, Coral, Gammon

Logical Functions:- And, Or, If, Iferror, Not, Nested If

Lookup & Reference Functions:- VLookup, HLookup, Index, Match, Offset, Indirect, Address, Column, Columns, Row, Rows, Choose, Arrays Concept In Lookup Formula’s, Past Special, Past link

Pivot Table and Charts, Import and Export data, Protect/Unprotect sheets/workbooks. Worksheet formatting and Print Display

Data Collection Method With Data Quality, Collaboration & Security Like Share Your Workbook On Share Drive With Quality

Analysis single / multi-dimensional analysis, like three-dimensional (3D) tables, sensitive analysis like data table, manual what-if analysis, threshold value, target search, one-convertible data table, two-variable data table

Advanced Dashboard

Report Development - (real world data)

 

 

 

DASHBOARD and VBA / Macros Programming Objectives

Dashboard reports allow the user to get a high level of business experience and help in making quick decisions.

Dashboards are Often Called as Management Information System (MIS), Which Provides Information that Organizations Require to Manage Themselves Efficiently and Effectively.

• To Define KPIs (Key performance Indicator), Customer Service Dashboards or Project Management Dashboard (Gantt Chart)

• Dashboard Reports Based on Tables and Number or Charts/Graphs or Both.

Introduction to programming introduction for logical thinking flowcharts and algorithms

Define objective, start and end points; Identifying the solution and breaking it in sequential phases

Step-by-step instructions, flowcharts, process flow diagrams. Introduction to Excel Macros

Full review of VBA language (subs, work, variables, arrays, loops, logic ... etc)

 

• Overview Of Commanding Excel Using VBA Including A Discussion Of Objects, Properties And Methods

• The Power of Macros – What, When, How to use Macros. Introduction to Object Oriented Programming

• Operators, Expressions, Loops, Logic Decision and Calling

• Introduction to objects, its functions, methods and properties

• Details of events, how to use events, and when, 'macro' Visual Basic Editor (Vibe) preparation - Developer tab, security

• Vibe, Property Window, Project Explorer, Code Protection for Code How To Use Vibe - Features, Options, Intelligence Technology

• Debugging modes, bookmarks, breakpoints, watch windows, instant windows and local windows inbuilt VBE help feature - Tips and tricks

• Code control vs. ActiveX control code

• Message Box and Input Box Working with Data in Excel through VBA

• Data Types, Constants and Variables

• Different type of data type; How and When to use Variables to Store Information.

• For-Next, For-Each, Do-While, Do until, Do Loop Decision-Making and Code Branching

• If-Then-Else, Select-Case, And/or Nested Conditions

• What is user’s Defined Functions? How to create & use them.

• Use of Arrays in VBA programming with one dimensional, two dimensional or multi-dimensional analysis

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 create summary sheets

• How To Access Windows File And Folder System To Open And Close Workbooks

• How to Protect Your Code Against Error

• How to Use Excel And VBA To Create Basic Dash Boards

• How to Create Your Own Custom Business Worksheet Functions In VBA

• How To Make Basic Report Generation Tools Using Excel VBA, Microsoft Word And PowerPoint

• How To Use Excel Visual Basic Macro Recorder To Record Excel Work In VBA And Then Explain The Code

• Overview of using the User Form to create Business Wizard

• Working with user forms and user forms such as list box, combo box, option button, check box, text box, label, 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

Connection between Excel VBA & other platforms

• How to set up a connection between VBA and Internet Explorer to open any Internet website through VBA

• How To Install Connection Between Excel VBA And Power Presentation To Build Power Point Through VBA

• How To Install Connection Between Excel VBA And Access Databases To Update Data In Data Through VBA

• How To Setup Connection Between Excel VBA And VBA Approach

The most effective method to Establish Connection Between Excel VBA and MS Word through VBA

 

Testing and Debugging Your Code Effective Error Handling Automation Development Reports and Live Projects

 

SQL

 

Access is a social database administration framework (RDBMS) from Microsoft that consolidates the social Microsoft Jet Database Engine with a graphical UI and programming improvement apparatuses. …

it can also directly import or link data stored in other applications and databases. Introduction to SQL

Sql course overview

Setting up test environment

What is SQL?

Editor and Platform for Learning SQL

Full SQL in class

Quick Start Introduction

Using the original selection statement

Selection of rows

Selecting Columns

Counting rows

Data entry

Updating data

Data deletion

Import and export data

Fundamentals of SQL

• Databases and tables

• SQL syntax overview

• Data Definition, Data Manipulation, Data Control, Transactional Control statements

• Creating tables

• Deleting a table

• Inserting rows into a table

• Deleting rows from a table

• What is NULL?

• Controlling column behaviours with constraints

• Changing a schema with ALTER

• Filtering data with WHERE, LIKE, and IN

• Removing duplicates with SELECT DISTINCT

• Sorting with ORDER BY

How Relationships Work in SQL

• Understanding Joins

• Accessing related tables with JOIN

• Multiple related tables

Explaining SQL Strings

• About SQL strings

• Finding the length of a string

• Selecting part of a string

• Removing spaces with TRIM

• Making strings uppercase and lowercase

Numbers and SQL

• About numeric types

• Finding the type of a value

• Integer division and remainders

• Rounding numbers

SQL Functions and Clause

• The Aggregate functions MIN, MAX, AVG, SUM and COUNT, UPPER, LENGTH, LOWER

• The GROUP BY and HAVING clauses Grouping in a combination with joining

• Triggers in SQL

• Concept of Trigger

• Create Trigger for (Insert, Update, Delete)

• Alter Trigger

What are Subselects and Views in SQL

• Creating a simple subselect

• Searching within a result set

• Creating a view

• Creating a joined view

Maintaining SQL Server Database

• Backup Database

• Restore Database

SQL Server Job Creation

• How to create job in SQL Server Agent

• How to schedule job

Topics Covered as follows:

• Access environment and tools

• Database terminology and concept

• Designing database in Access

• Understanding RDBMS

• Working with the Design side of Tables

  • Create Query

  • Join Tables That Have No Common Fields

  • Work with Subdatasheets

  • Create Sub queries

• Working with the runtime of Tables

• Data Migration and Importing

• Working with the Design side of Queries

• Working with the runtime of Queries

• Working with the Design side of Forms

  • Adding Controls to Forms

  • Creating Sub forms

  • Organizing Information with Tab Pages

  • Display a summary of data in a form

  • Apply conditional formatting

  • Working with form, runtime of the management switchboard

  • Working with the design side of the report

  • Organize Report Information

  • Include charts in the Format Reports report

  • Add a calculated field to the report

  • Add a Sub answer to an Existing Report

  • Working with the runtime of Reports

  • Working with the Design side of Macros

  • Making a Macro Restricting Records Using a Condition

  • Robotizing Data Entry Using a Macro

  • Working with the runtime of Macros

  • Step by step instructions to make a useful particular

  • Assemble a certifiable business application

  • Putting by and large and organization