Document

Document technical information

Format pdf
Size 793.1 kB
First found May 3, 2016

Document content analisys

Language
English
Type
not defined
Concepts
no text concepts found

Organizations

Transcript

Course Definition.

What is Excel?
 Excel is an electronic spreadsheet program that can be used for storing, organizing and
manipulating data.

Why Excel?
 Excel makes calculations of complex formulas easy
 Excel works great as a basic reporting tool
 To groom your career as reporting analyst
 To broaden your opportunity’s horizon in various sectors. (Like IT, BPO, KPO, Banks, others
etc.)

Who should go for Excel/VBA?
 A professional / Student looking forward to learn a basic technology and groom his/
her career in IT/KPO/Bank/BPO sector.
 A professional / Student looking forward to groom his/her career in
Reporting/Analysis. (Report Automation)
 A manager trying to track his/ her team performance and fulfil all reporting task
(Including manual/ automated) he/she handles.
 A teacher trying to figure out how your students are doing as compared to their peers
 A small business owner wondering which product to keep and which to retire
 A scientist trying to figure out the homogeneity of a dataset
 A housewife keeping track of household expenses and determining if it’s a good idea
to send her children to $1000/mth tuition classes
 A freelancer wants to earn some money seating home by doing some easy excel
work.

What is a Report?
 A document containing information organized in a narrative, graphic, or tabular form,
prepared on ad hoc, periodic, recurring, regular, or as required basis. Reports may
refer to specific periods, events, occurrences, or subjects, and may be communicated
or presented in oral or written form.
(Present a Suitable Example with some suitable scopes)

What is a dashboard?
 A dashboard is a visual display of the most important information needed to achieve
one or more objectives; consolidated and arranged on a single screen so the
information can be monitored at a glance.
Here are the key characteristics of a dashboard:

All the visualizations fit on a single computer screen — scrolling to see more violates
the definition of a dashboard.

It shows the most important performance indicators / performance measures to be
monitored.
 Interactivity such as filtering and drill-down can be used in a dashboard; however,
those types of actions should not be required to see which performance indicators are
under performing.
Example

Excel Functions and VBA
 Excel provides the user with a large collection of ready-made functions, more than
enough to satisfy the average user
 VBA (Visual Basic for Applications) is the magic that allows you to get your
Office programs doing whatever you want. You can automate tedious procedures,
get your different applications to communicate with each other, and build the tools
that you wish Microsoft had made. And you don't even have to be a Rocket
Scientist!
From simple macros to complex procedures and custom functions... once you start using
VBA you will wonder how you ever managed without it.

How you are going learn with us?
 Learn While Working…
 Understanding most commonly used excel formulas and various scenarios.
 Training on how to record or write macros.
 Report Automations and various tools.
 The work environment provides a context for learning that is very different to that
provided within Colleges/universities. Not only do people learn in different ways, but
they also learn different things. Although the workplace appears to be primarily
concerned with your capability (what you do and how you perform), it is equally
important to be able to do the right thing at the right time.
 In practice this means that you have
(1) To understand both the general context and the specific situation you are
expected to deal with
(2) To decide what needs to be done by yourself and possibly also with others, and
(3) To implement what you have decided, individually or with others, through
performing a series of actions. All three of these processes contribute to your
perceived capabilityy.
(4) Many Brains environment
(5) Bound to do. Bound to learn.
Questions and Answers
Program A for Excel Learners (40 hrs)
Day 1 – (4 Hrs )
Course definition
Excel Formula (What/ How to write)
Count(), Counta(), Countif(), Countifs(), Countblank(),Sum(), Sumif(), Sumifs(),
Sumproduct(),Average(), min(), max
Exercise above with some suitable example or scenario
Day 2 – (4 Hrs)
LEFT(Text, # Characters)
=RIGHT(Text, # Characters)
=MID(Text, Start #, # Characters)
=TRIM(Text)
=PROPER(Text)
=CLEAN(Text)
=UPPER(Text)
=LOWER(Text)
=FIND(Text to Find, Within Text)
=SEARCH(Text to Find, Within Text)
=LEN(Text)
Gives leftmost portion of text for #
characters
Gives rightmost portion of text for #
characters
Gives text from Start # for # characters
Removes extra spaces
Makes first letter in each word of text
uppercase
Removes all non-printable characters
from text
Makes text ALL UPPERCASE
Makes text all lowercase
Finds starting position of text within
text; case sensitive
Same as above, but not case sensitive
Gives # of characters in text
=SUBSTITUTE(Within Text, Text to
Replace, Text to Swap In)
=REPLACE(Within Text, Start #, #
Characters, Text to Swap In)
=VALUE(Text)
=TEXT(Value, Format Text)
Replace text within text based on search
for “Text to Replace”
Replace text within text based on
character position at “Start #”
Converts text to number
Shows number or text in different
format
Exercise a report to work on, which may use all above functions.
Day 3 - (4 Hrs)
Function
=DATE (Year, Month, Day)
=DATEVALUE (Text)
=YEAR (Date)
=MONTH (Date)
=DAY (Date)
=NETWORKDAYS (Start Date, End
Date)
=EOMONTH (Start Date, # Months)
What It Does
Creates date in Excel
Converts text to real date
Returns year of date
Returns month of date
Returns day of date
Calculates # of business days between
two dates
Last day of month after # months
Exercise a report which utilizes all the above functions
Day 4 – (4 Hrs)
=VLOOKUP (Value, Table, Column #)
=HLOOKUP (Value, Table, Row #)
=MATCH (Value, Row or Column Range)
=INDEX (Table, Row #, Col #)
=INDIRECT (Reference)
=ADDRESS (Row #, Col #)
=CHOOSE (Number, Item1, Item2…)
=OFFSET(Cell, # Rows, # Cols)
Cell freeze concepts and applicable scenarios
Looks up Value in Left Column and Returns
cell in specified Column # and Row # that
contains Value
Looks up Value in Top Row and Returns
cell in specified Row # and Column # that
contains Value
Finds Item’s Position in Row/Column
Retrieves cell value at Row # and Column #
Returns cell at reference given by Reference
text
Creates cell reference text
Selects Item from List based on Number
Move # of Rows and Columns from Cell
Exercise a report which utilizes all the above functions
Day 5 – (4 hrs)
Conditional Formatting, Data sorting and Filtering.
Pivot Tables, Calculated Fields, Named Ranges.
IF (condition, [value_if_true], [value_if_false])
IF (OR (A1<150000, A1>250000), 0, A1)
IF (AND (A1>100, B1<200), 25, 0)
Exercise:
Example: Create a data table with some Student ID, name and Subjects,
Enter dummy marks. 45 is pass mark.
Any students passed in all the subjects should declared as pass. And if passed then
Average marks >=80 A, >=60 B else C. Fail for Fail.
Day 6 to 10 (4*5= 20 hrs.)
Project. (With Expert Support)
Gathering Requirements (Learn how to get the customer’s requirement)
Understanding Data Source
Drafts and signoffs (via email- learning business communication)
Online / Live presentation.
CV preparation
Interview Tips
Certifications
For Excel + VBA
Program A + Program B
Program B (40 hrs.)
Day 11 – (4 hrs.)
Introduction to VBA Macros
Understanding various way to referencing a cell.
Understanding excel Object hierarchy
Recording Macros and understanding code behind
Editing, writing a macro code and saving.
Debugging
Day 12 – (4 hrs.)
VBA Programing concept
VBA Syntax and Semantics
Variable Type and declaration, Comment line
Decision making with operators
Repeating actions with loops
Day 13 – (4 hrs.)
Procedures and Events
Functions
User Forms and GUI
Day 14 – (4 hrs.)
Sort, Filter, Find, Conditional formatting, Pivot, Charts with macros
Error Handling
Day 15 – (4 hrs.)
ODBC Connectivity, Query handling.
Day 16 to 20 (5*4=20 hrs)
Project. (With Expert Support)
Gathering Requirements (Learn how to get the customer’s requirement)
Understanding Data Source
Drafts and signoffs (via email- learning business communication)
Online / Live presentation.
CV preparation
Interview Tips
Certifications
×

Report this document