St. Paul’s College of Management & IT
(Affiliated to Osmania University & Approved by AICTE)
SyNo: 603, 604 & 605. Turkayamjal (vi.), Hayathnagar (M), R.R.Dist.
CERTIFICATE
This is to be certify that Mr. /Ms. ……………………………..
of M.B.A I Year/I Semester with H.NO.: ……………………
has submitted ITAB-LAB record during the Academic year
20 -20 .
Internal Examiner:
External Examiner:
Head of the Department
Date:
INDEX
S.NO
PROGRAM NAME
1
Excel Introduction
2
Create an Excel sheet for students based on
their marks list.
3
Create a Graph in Excel for student Table
4
Create an Excel sheet for employee salary
report
(ta- 6% of basic salary, da-12 %of basic salary,
hra 5% of basic salary and pf is 6% of basic
salary) find out the net salary of employees
and rank of employees( who draw 15000 –I
rank, 10000- II rank for remaining people give
the III rank).
5
Calculating the Mean and Standard Deviation
with
Excel
6
Creating Macro in Excel
7
ACCESS INTRODUCTION
8
CREATE TABLE IN ACCESS:
9
Creating an Access Form
10
Creating an Access Report
11
Choosing a Table to Query
PG-NO
1. Excel Introduction
WhatisExcel?
Microsoft Excel is a spreadsheet program that is used to store, sort and efficiently
crunch numbers. Accountants use Excel to keep track of transactions for their
company. Students might use Excel to help draw conclusions from the data they
gathered on a science project.
No matter what you are using Excel for it is important that you have the basic
knowledge required to do the everyday operations you will encounter while using this
program.
TheExcelSpreadsheet
A spreadsheet originally existed in the physical world as a piece of paper that you
could actually write on. These paper spreadsheets were used mainly by Accountants
to help organize data in a logical and easy to view manner. However, the spreadsheets
of the Information Age reside on your computer, though they still have the basic form
of the old spreadsheets.
TheSpreadsheet,TheContainer
The spreadsheet (or worksheet) contains or will contain all the data that you enter.
When you start up Excel, you should be greeted with a blank spreadsheet like the one
we have below.
Parts of theSpreadsheet
Nearly every part of the spreadsheet has its own term and it is quite useful to know
your spreadsheet vocabulary to make using Excel that much easier. There are three
basic parts of an Excel Spreadsheet:
1. Column - The vertical segments that you see on the spreadsheet are called
columns.
2. Row - The horizontal segments are referred to as rows.
3. Cell - Each box that is created from a row and column intersecting is referred
to as a cell.
There are all very important terms, so we will now be going into greater depth on
exactly what they are.
Excel uses letters to represent columns and numbers to represent rows. This may
be a little confusing at first, but with practice and a little effort this will become
second nature to you.
Excel Cells
Now that you understand columns and rows you can grasp the labeling system for
cells. When a column and a row intersect they form a cell. The very first cell, where
column A and row 1 intersect is referred to as A1. It seems that the column label
always comes first and the row second. Try to remember that!
Here's a picture of Cell A1:
If you are ever having trouble trying to find a cell, we recommend the following
technique (we use cell G13 as an example):
1. Find the column on the column bar by looking for the letter of the cell (in this
case that would be G).
2. Find the row on the row bar by looking for the number of the cell (in this case
that would be 13).
3. After you have found both the column and the row trace an invisible line from
each to the cell where they intersect and click there if you wish to enter data.
2. Create an Excel sheet for students based on their
marks list.
1.
Enter the fields in the row.
a.
SNo
b.
Name
c.
Telugu
d.
English
e.
Hindi
F.
math
g.
Social
h.
Science
i.
Total
j.
%age
k.
Remarks
l.
Devision
2.
Generate the Roll No through Fill->Series command from edit menu.
3.
Enter the Name and Marks of the students.
4.
Calculate the Total of first student in cell I4 Using the formula =SUM (C4:H4)
and press enter.
5.
Copy the formula from cell I4 and paste it in other cells under the Total
heading from cell I5 to the cell I13.
6.
The formula will be copied to each cell and total will be calculated according
to the corresponding rows.
7.
Now calculate the percentage in the similar manner but the formula will be
changed.
8.
In the Cell J4 write the formula as = round (I4/700*100), 2). The round
command will round off the values to the two places decimal according to the
above function.
9.
Copy the formula to cells from J4 to J13 to find out the percentage of other
students.
10.
Now to find out that the student is pass or fail write the formula
==IF(AND(C4>=35,D4>=35,E4>=20,F4>=35,G4>=35,H4>=35),"pass","fail"
) and copy the formula from cell K4 to K13.
11.
Now find out the student class (division)
write the formula
=IF(AND(J4>60,K4="pass"),"first",IF(AND(J4>=50,K4="pass"),"second",IF(
AND(J4>=35,K4="pass"),"thaird","nill"))) an copy the formula k4 to k13.
3. Create a Graph in Excel for student Table
1.
Now to draw a graph Select the Cells from b4 to i4. Leave the rest of the cells
as these are not required for the graphs.
2.
Choose the Chart Command from insert menu.
3.
The following screen will appear. Select the required Chart type from the list.
4.
In our case we are using the column chart.
5.
Press the next button
6.
In the following window you can adjust the range of selection. As it is not
required in our case.
7.
Select the move chart
8.
Click on the ‘new Sheet’ option.
9.
Press the finish button and you will find the chart in the following manner.
4) Create an Excel sheet for employee salary report
(ta- 6% of basic salary, da-12 %of basic salary, hra 5% of
basic salary and pf is 6% of basic salary) find out the net
salary of employees and rank of employees( who draw 15000
–I rank, 10000- II rank for remaining people give the III
rank).
1) Enter the fields in to the excel sheet.
2) Enter the eno, name, basic salary into the fields.
3) Find out the TA from basic salary. The formula is = (c4/100)*6. Copy the same
formula up to D8.
4) Find out the DA from basic salary. The formula is = (c4/100)*12. Copy the same
formula from E4 to E8.
5) Find out the HRA from basic salary. The formula is = (c4/100)*6. Copy the same
formula from F4 to F8.
6) Find out the GROSSSPAY from the table. The formula is = sum (C4:F4). Copy the
same formula G4 to G8.
7) Find out the PF from basic salary. The formula is = (c4/100)*6. Copy the same
formula H4 to G8.
8) Find out the net salary. Deduct the PF from GROSSSALARY. The formula is =G4
– H4.
9) Find out the ranks depends upon their income. The formula is =IF (AND
(I4>15000), "I",IF (AND (I4>10000),"II","III")).
5)
Calculating the Mean and Standard Deviation
With Excel
1. Enter the scores in one of the columns on the Excel spreadsheet (see the
example below). After the data has been entered, place the cursor where you wish to
have the mean (average) appear and click the mouse button. Now move the cursor to
the Function Wizard (fx) button and click on it.
2. A dialog box will appear. Click on Statistical from the left section of the box
and AVERAGE on the right section. After you have made those two selections, click
on Next> at the bottom of the dialog box.
3. Enter the cell range for your list of numbers in the number 1 box. For example,
if your data were in column A from row 1 to 11, you would enter A1:A11. Instead of
typing the range, you can also move the cursor to the beginning of the set of scores
you wish to use and click and drag the cursor across them. Once you have entered the
range for your list, click on Finish at the bottom of the dialog box.
4. The mean (average) for the list will appear in the cell you selected.
5. Place the cursor where you wish to have the standard deviation appear and click
the mouse button. Now move the cursor to the Function Wizard (fx) button and click
on it.
6. A dialog box will appear. Click on Statistical from the left section of the box
and STDEV (for a sample) on the right section (Note: If your data is from a
population, click on STDEVP). After you have made your selections, click on Next>
at the bottom of the dialog box.
7. Enter the cell range for your list of numbers in the number 1 box. For example,
if your data were in column A from row 1 to 11, you would enter A1:A11. Instead of
typing the range, you can also move the cursor to the beginning of the set of scores
you wish to use and click and drag the cursor across them. Once you have entered the
range for your list, click on Finish at the bottom of the dialog box.
8. The standard deviation for the list will appear in the cell you selected.
6) Creating Macro in Excel
Let's say you work in a group that uses Excel to track accounts receivable. Every
week you and your co-workers each submit a report in which your manager expects to
see overdue amounts formatted so that they're easy to see: the numbers are bold and
red and the cells have red borders. Here's how to record a macro to apply these
formats:
1. In the workbook where you track your accounts receivable, click one of the cells
you're going to format.
2. Point to Macro on the Tools menu, and then click Record New Macro.
3. In the Record Macro dialog box, type a name for the macro in the Macro name box.
Macro names must start with a letter and can include letters, numbers, and underscore
characters, but can't include spaces. You don't need to change the other boxes:
When you click OK, the Stop Recording toolbar appears, and you're ready to record.
Until you stop the recording, every Excel command and keystroke will be recorded in
the macro, in the order in which they are entered.
4.
Now format the cell the way your boss wants it flagged: click Cells on the Format
menu, click the Font tab, click Bold under Font style, click Red for Color, click the
Border tab, click Red for Color, click the border thickness you want, click Outline,
and then click OK.
5. To finish recording the macro, click the Stop Recording button:
Now you have a macro that can perform in a single operation all 12 mouse clicks that
it took to format the cell.
Use the macro you created
The next time you need to flag a cell, you can run the macro. If you're going to use the
macro frequently, you can create a toolbar button for it, or assign a keystroke for it, or
both.
Run your macro using the Tools menu
1.
2.
3.
Click the cell you want to format.
On the Tools menu, point to Macro, and then click Macro.
Click the name of your macro, and then click Run.
Create a toolbar button that runs your macro
1.
2.
3.
4.
On the Tools menu, click Customize, and then click the Commands tab.
Under Categories, click Macros.
Drag the custom button to the toolbar where you want it.
On the Customize dialog box, click Modify Selection, and then click Assign
Macro.
5.
In the Assign Macro dialog box, click the name of your macro, and then click
OK.
6.
To change the appearance of the button, click Modify Selection again, point
to Change Button Image, and click one of the available images; or click Edit
Button Image and use the Button Editor to create your own image.
7.
Click Close.
Assign a keystroke to run your macro
1.
2.
3.
On the Tools menu, point to Macro, and then click Macro.
Click the name of your macro, and then click Options.
In the Shortcut key box, type the key to use along with CTRL to run your
macro.
NOTE Avoid using a keystroke that's already used for other Excel
operations, such as CTRL+C for copy.
7) . ACCESS INTRODUCTION
Microsoft Access stores information in what is called a database. For now it is
good enough to know that your data is put into a database and not worry about the
details. We will be explaining databases and other key Access elements in a later
lesson.
There are four major steps to using Microsoft Access:
1. Database Creation: Create your Microsoft Access database and specify what
kind of data you will be storing. A retail business might create a database to
store all their sales information (i.e. items sold, customer, employee,
commission, etc)
2. Data Input: After your database is created the data the store gathers every
business day can be entered into the Access database.
3. Query: This is a fancy term to basically describe the process of retrieving
information from the database.
4. Report(optional): Information from the database is organized in a nice
presentation that can be printed in an Access Report.
The name database is actually a very descriptive name. The database is two things
in one:
Data...: A place to store your data. This data could be a record of sales,
employees, salaries, or anything else....base: It is the basic building block that
many other features in Access use to function. With a properly created
database you can create informative reports about the data, custom charts to
visually display values, and create queries. These items will all be covered in
more detail later. Without a database filled with data you can do...nothing.
A database is a little bit more complex then some think it should be. You cannot
enter data into a database without first creating a table
AReal DatabaseExample
People use Access for various reasons, but let's assume someone named Bob is
using it to store information about Bob's Shoe Store. Bob gathers raw information
about shoes sold from a cash register then he manually types this data into Access.
Now the tricky part, of course, is where does he enter this data?
CreatinganAccess Database
Bob wants to store information in Access, but doesn't know how many databases
he needs to create!
Although you may require many databases in the future, it is usually sufficient to
have one database per project. This means Bob should have one database for his
business, but would need a separate database if he wanted to store his family's
information.
To create a new database in Access follow these steps:
1. Start Access
2. From the menu choose File < New
3. The "New File" side bar will be displayed on the right-hand side of the screen
4. Left-click the option "Blank database...", which will then ask you to name
your database. Helpful Hint: Access databases are saved with the .mdb
extension.
5. Name your file (we chose TizagDB) and press Create. This will automatically
save your blank database, so remember where you put it!
6. The Access Database interface should now be displayed and you are well on
your way to learning Access!
Now that the database has been created we can begin to create our first Access
Table. A table resides within a database and holds information specific to a certain
area.
Finding theChartWizard
The chart wizard can be found inside the Reports area of Access. Navigate to the
reports are.
The chart wizard can be found by clicking the green New button and selecting
Chart Wizard.
Choosing theRightChart
Our data consists of product types and total sales, so a histogram would probably
be the best choice for this data.
1. With Chart Wizard selected, choose the Query "qry_ProdSales" from the drop
down list. This is what our chart will be constructed from.
2. Click OK
3. Add both available fields to the chart and click Next.
4. Select the Bar Chart and press Finish.
5. Your new chart should look something like this:
6. Close the chart preview and save your chart as "cht_ProdSales"
This chart doesn't cleary show all the products, so we are going to have to widen it
a bit.
8) . CREATE TABLE IN ACCESS:
A table in Access is quite different then a table in real life. Instead of having
wooden legs and being used for meals, Access Tables are a grid made up of rows and
columns. Here's an example of a table in Access:
There are for key components we want you to learn right now:
1. tbl_Sales: The name of our table is the example is "tbl_Sales". Note that we
could have simply called our sales table Sales, but by including a prefix tbl_
there is absolutely no confusion and is a great Access habit to pick up!
2. Columns: A column is one vertical section of the table (i.e. up-and-down
sections). The vertical columns have their label at the top and these labels
should describe the type of information that will be stored. The columns in this
table are: Employee, Product, Price and SaleNumber.
3. Rows: A row is one horizontal segment of the table (i.e. left-to-right sections).
One record takes up exactly one row. For example, in this table one sale at
Bob's Shoe Store was a pair of slippers, which sold for $5.00. This record was
entered left-to-right as follows: Employee-Bob, Product-Slipper, Price-$5.00,
SaleNumber-3.
4. Cells: A cell is simply the intersection of a row and a column. Can you find
the cell that contains the value $150.00? Which row and column intersected at
this cell? When you enter information into Access it will often be one cell at a
time!
These definitions may seem confusing at first and if that is the case, please read
through this lesson, play around in Access then revisit this page to seem if it is
starting to make more sense. If you stick with it you'll be amazed at how much you
can learn!
Now that we've covered the basics of Access Tables let's actually create one!
CreatinganAccessTable
When you create a table in Access you have to know what the table will store and
what format that information will be in. For example if you wanted to store the
product identification numbers involved in a sale, then you might label that column
"ProductID" and specify that only numbers should be stored for that column.
We'll be creating the table tbl_Sales that you saw above, but remember this is only
the table creation stage and we will not be entering data just yet!
1. With the Tables object tab selected, double-click the "Create table in Design
view"
2. This will bring up the Table Design View
3. There are three columns here that should be explained in detail:
o Field Name: This is where you type the name for your column. A
common practice is to make it one word and to use capitalization for
multiple words squished into one (e.g. SaleNumber)
o Data Type: This column is where you specify the type of data that will
be stored. If you are storing money then select Currency. The most
common types of data are: Text, Number, Currency and Date/Time.
o Description: Here you can type optional notes to remind yourself or
provide useful information for others who might be viewing this file
later.
4. The first column in our tbl_Sales example was Employee, so let's enter in
Employee in the Field Name column and choose Text from the Data Type
column. If click inside the Data Type column you will see that it is actually a
drop down select box with many options to choose from. Select the Text
option.
5. Enter the following information for our remaining three columns of tbl_Sales:
o Field Name: Product, Data Type: Text
o Field Name: Price, Data Type: Currency
o Field Name: SaleNumber, Data Type: Number
6. Before we are finished here, we need to make a Primary Key. A primary key
is restriction that we place on a column stating that there can be no duplicate
values in that column. We will be talking about keys later, but for now rightclick in the SaleNumber row and choose Primary Key from the pop-up menu.
7. We have finished our table's outline so click the X in the top right to close the
design view (don't close Access, just the Design Window).
This will also bring up a prompt to name your Access Table.
8. Click yes and enter "tbl_Sales" for your table's name.
Although this process of creating an Access table might seem overly complicated,
with time you'll be able to create and edit existing tables very quickly.
9) . Creating an Access Form
Although we haven't recommended the various wizards that Access had available
in the previous lessons, the form wizard is actually very useful and should save you a
bunch of time! Let's create a simple data input form for the new employee!
1. Navigate to the Forms section in Access
2. Double-click "Create form by using wizard"
3. We want all of the fields from tbl_Sales to be included in this form, so first
select tbl_Sales from the drop down box "Tables/Queries"
4. The single right arrow will add one selected field at a time, but we want all the
fields. The shortcut to add every field from a given table or query is to click
the double arrow button. Do that and click Next.
5. Choose a Columnar layout and press Next
6. Choose any style and press Next (we chose "Sumi Painting")
7. Change the form's title to frm_EmployeeEntry
and click Finish
Open up your form and check it out!
Entering Data Using AccessForms
Now that the form has been created, Bob just needs to teach his employee how to
enter in the data. Lucky for Bob it's as easy as one-two-three and won't take him long
to bring his new employee up to speed.
1. Open up frm_EmployeeEntry (easy!)
2. At the bottom of the form is a set of arrows to navigate through the records.
To get to the end of the existing records and begin entering data you need to
click the arrow with an asterisk(*).
3. Clicking that button will bring you to the first blank record, which would be
the sixth in our case. You would then enter all the data for that record and
click the right arrow to advance to the next blank record. After all the new
records have been entered, close the form and pat yourself on the back.
When you enter data into this form it will automatically add it to our existing
tbl_Sales because we specified that table when we created our form.
10) . Creating an Access Report
Bob wants a report to show the sales for each product, as well as the total sales for
his company. Luckily, because he has all his sales information in an Access database,
he can create this report in about a minute!
Let's explore how you would create this basic sales report in Access.
1. Navigate to the Reports section in Access
2. Double-click "Create report by using wizard"
3. Select the query we created in the Access Query lesson qry_ProdSales and
add both fields to the report.
4. Click Next
5. At the grouping step, add the Product field by clicking the right arrow and
click Next
6. At the sorting step, select Price from the drop-down-box then click Summary
Options ...
7. Check the Sum box, so the report will include totals for the Price field and
click OK
and click Finish
8. Click Next to advance to the layout options
9. Choose a Stepped layout and a Portrait orientation, then click Next
10. At the Style screen choose Bold and click Next
11. Name the report rpt_Sales and click finish
and click Finish
Open up your report and check it out!
11) . Choosing a Table to Query
Before you can create a query you have to navigate to the Query Tab in your
Access database. Select Queries from the Objects Pane.
Although you could use the Wizard, we will guide you through the process of
creating an Access query with the "Design view". We feel this is helpful for
beginners, so they don't feel overwhelmed when they need to do something the
Wizard doesn't allow them to.
1. Double-click "create Query in Design view"
2. Add the table tbl_Sales
3. Your Query window should now have the tbl_Sales table added to it
You have just completed the setup process for making a query. Every time you
make a query you have to first choose which table(s) you want to select data from.
Currently, our database only has one table, so we don't have a lot of choices here.
Now we can begin to create our custom Access query.
Creatinga CustomQuery
Bob wants a query that will just return the list of items sold and for how much. He
doesn't care about the sale number or the employee. To make this query we are going
to have specify the fields we want to see and ignore the others.
Access lets you quickly select fields you want to see by a simple drag and drop
method. For ever field that a table has there is an entry in the quick table viewer.
Notice that the quick view of tbl_Sales displays the fields: *, Employee, Product,
Price and SaleNumber. Note: The field * is a wildcard, meaning it will select all the
fields if you choose *.
We only want Product and Price, so let's start by dragging Product down from
tbl_Sales to the first column. Notice that when you drop the Product field into the first
column it populates two of the fields and checks the "Show" box:
Drag and drop the Price field into the adjoining column and you should have
something like:
That's it! You're done! Close the Query window and save your file query as
qry_ProdSales.
RunningYourFirstQuery!
Well you've finished writing the backend for your query, so let's if it works.
Double-click your newly created query and you should see something like this:
Bob can now print out this handy report and review his sales in an easy-to-read
fashion, while away from his computer. If you would like to make any changes to the
report just right-click rpt_Sales and choose the "Design View" option from the popup
menu.