Document Sample
thesis Powered By Docstoc
					Program: 1

Build a worksheet with a student record and calculate the result based on
average marks.

   1. Create a new blank worksheet. On the File menu, click New, and then click
      Blank Workbook on the New Workbook.
   2. Data can be entered into an active cell. A cell can be made active by clicking
      the mouse pointer on the desired cell.
   3. Cell can have values such as numbers, text , dates etc.
   4. As per the program given enter Rollno, name, marks1, marks2, marks3 of
      different subjects. Average and grades is to be calculated.
   5. Average can be calculated using the formula AVERAGE(Cell Range).
   6. A formula establishes relationship between 2 or more cells. The cells in which
      formulas are stored displays the result of the calculation.
   7. Steps involved are:
          -     double click on the cell.
          -     Type ‘=’ sign
          -     Type in the formula
          -     Press enter key
   8. The formula can be copied into the relative succeeding cells using copy and
      paste option in the right click button of the mouse.
   9. Thus using the formula we can get the average of different roll nos.
   10. According to the problem the roll no getting average >60 gets 1st class, >40
      gets 2nd class, <=40 gets 3rd class.
   11. The formula to be typed is IF(H5>=60,”1stclass”,IF(H5>40,”2nd class”,
      IF(H5<=40,”3rd class”,))).

      The above formula can also be copied to different cells. Thus we get different


Create a bar graph based on students performance

   1. Select the data range on which the chart has to be created.
   2. from insert menu choose chart option.
   3. Select on this sheet option from the chart sub-menu.
   4. Select the area where the chart has to be displayed.
   5. After selection the chart wizard is invoked, it displays a dialog box in which
      the range selected is shown. If the range is correct then click on NEXT, else
      EDIT the range.
   6. Types of chart available with the wizard is displayed. Select the type which is
      needed and then click on the NEXT button we select the column type.
   7. Various chart formats for the type of chart selected is shown. Select the format
      and click on the NEXT button.
   8. A dialog box is shown in which the sample chart is displayed and whether an
      axis label is required or not is asked. The cell that contains labels are to be
      specified. After selecting the correct option click on the NEXT button.
   9. Enter the chart name and axis titles. Click on FINISH button.
   10. The chart gets created and displayed on the selected are.

Program 3:

Create a net sales report based on conditions.

   1. Create a table with the following attributes or column names that is Item
       Name, Quantity, Rates, Total Sales, Discount and Net Sales.
   2. In the item column type some item names. In the quantity and rate columns
       type some values.
   3. For the total sales, you have to take the product of quantity and rate. So, go to
       insert in the menu bar, it will display some options in the select functions, it
       will again display sub options. In that click on the product and give the cell
       address for the product and press enter key.
   4. The value will be displayed in the cell. For the rest of the values go to the edit
       and click on copy and drag the cell and again go to edit and click on paste. It
       will automatically display the other values.
   5. for finding the discount you have to write the formula with ‘IF’ condition. So,
       goto the formula bar and click on equal to sign(=) and type the formula with
       cell address and condition that is
       H5*30/100))). Display the value for other cell just copy and paste the cells.

   6. For finding the Net sales you have to subtract Total Sales with Discount. So
       Go to the formula bar click on equal to sign(=) and give the cell address that is
       =(G5-H5) and press enter key.
   7. After creating the whole table save it.

Program 4:

Create a salary report based on salaries.

   1. Create a salary table in MS-EXCEL with the field names being EMPNO,
   2. Enter details of employees in the table
   3. Then calculate the HRA using the following formula: IF(G5<5000,
       G5*25%,if(G5>5000,G5*50%,if(G5>10000, G5*60%)))
   4. Then calculate the DA using the following formula: if(E4<5000,
       G5*15%,if(G5>5000,G5*5%,if(G5>10000, G5*20%)))
   5. Display the results in different columns under their respective headings.
   6. Save the worksheet.

Program 5:

Create a students’ report using conditional formatting options.

Conditional format: A format, such as cell shading or font color, that Excel
automatically applies to cells if a specified condition is true.

   1. Create a new blank worksheet. On the File menu, click New, and then click
       Blank Workbook on the New Workbook.
   2. Data can be entered into an active cell. A cell can be made active by clicking
       the mouse pointer on the desired cell.
   3. Cell can have values such as numbers, text , dates etc.
   4. As per the program given enter Rollno, name, marks1, marks2, marks3 of
       different subjects. Average and grades is to be calculated.
   5. On the Format menu, click Conditional Formatting.
           a. To use values in the selected cells as the formatting criteria, click Cell
               Value Is, select the comparison phrase, and then type a constant value
               or a formula. If you enter a formula, start it with an equal sign (=).

               To use a formula as the formatting criteria (to evaluate data or a
               condition other than the values in selected cells), click Formula Is and
               then enter the formula that evaluates to a logical value of TRUE or

           b. Click Format.
           c. Select the formatting you want to apply when the cell value meets the
               condition or the formula returns the value TRUE.
           d. To add another condition, click Add, and then repeat steps 1 through 3.

You can specify up to three conditions. If none of the specified
conditions are true, the cells keep their existing formats.

Program: 6

Create a Database for the following data – empno, empname, salary, designation,
deptno. Empno should be Primary key.

Create a database by using a Database Wizard

You can use a Database Wizard to create in one operation the required tables, forms,
and reports for the type of database you choose— this is the easiest way to start
creating your database. The wizard offers limited options to customize the database.

   1. Click New on the toolbar.
   2. In the New File task pane, under Templates, click On my computer.
   3. On the Databases tab, click the icon for the kind of database you want to
       create, and then click OK.
   4. In the File New Database dialog box, specify a name and location for the
       database, and then click Create.
   5. Follow the instructions in the Database Wizard.

Create a table

   1. Select the design view which contains three columns:- field name, data type,
   2. Enter empno in field name and auto number for the data type.
   3. Select this column and set primary key.
   4. Next enter emp name and provide data type as text. In the next row enter
       salary and give data type as number and so on for all the fields.
   5. Save the table and provide it with a name and a table is created.
   6. Open the table and enter the data you want to.

Program 7:

Create a relationship by the given tables.

When you create a relationship (relationship: An association that is established
between common fields (columns) in two tables. A relationship can be one-to-one,
one-to-many, or many-to-many.) between tables, the related fields don't have to have
the same names.

   1. Create Employee table and Department table.
   2. Close any tables you have open. You can't create or modify relationships
       between open tables.
   3. Go to Tools – Relationships.
   4. If you haven't yet defined any relationships in your database, the Show Table
       dialog box is automatically displayed.
   5. Double-click the names of the tables you want to relate, and then close the
       Show Table dialog box. To create a relationship between a table and itself,
       add that table twice.
   6. Drag the field that you want to relate from one table to the related field in the
       other table. To drag multiple fields, press the CTRL key, click each field, and
       then drag them.
   7. To create a relationship, drag the pointer from on dept to another.
   8. Relationships window will be displayed. Click on ‘Enforce Referential
       Integrity’ and click on create.
   9. One to many relationships will be created. Save and close it.

Program 8:

Write a query to retrieve for empno, empname, salary from Emp table and
Deptname and location from dept table, where Deptno is 1.

   1. Click on queries tab and then on NEW.
   2. New query wizard window will be displayed select the following field names
      from both the tables DeptNo, DeptName, EmpNo, EmpName, Designation
      and Salary then click on ‘Next’.
   3. It will ask ‘would you like a detail or summary query?’ click on ‘Detail and go
      to ‘Next’.
   4. Give the query name as ‘DEPT DETAILS Query’ and click on ‘Finish’
   5. ‘DEPT DETAILS Query’ with the entries will be displayed. Save and Close it.

Program 9:

Designing a form based on a database.

Form lets you to display and enter data in a convenient format that resembles fill in
the blank forms. Your forms can be plain or elaborate with graphics, lines and
automatic look up features that make data entry quick and easy. To start creating the
form in the same way as the table as query you click on the form tab on the database
window. If an existing form has to be opened in the design view. Select the name of
the form from the list. Click on the design button if it has to be opened the form view
click on open button.

Creating a control form:

   1. Click on the NEW button under forms.
   2. Select the design view option
   3. Now the database displays a blank form
   4. Choose the COMMAND BUTTON from the toolbox and click it on the form
       to create control buttons i.e., Dept, emp, exit.
   5. Choose FORM OPERATIONS and OPENFORM for the dept details, dept
       details, Click NEXT.
   6. Choose the form for the app. NEXT.

Program 10:

Creating and printing a report.

A report is an effective way to present your data in a printed format. Because you
have control over the size and appearance of everything on a report, you can display
the information the way you want to see it.

Most reports are bound to one or more table and query in the database. A report's
record source refers to the fields in the underlying tables and queries. A report need
not contain all the fields from each of the tables or queries that it is based on.

   1. In the Database window, click Reports under Objects.
   2. Click the New button on the Database window toolbar.
   3. In the New Report dialog box, click the wizard that you want to use. A
        description of the wizard appears in the left side of the dialog box.
   4. Click the table or query that contains the data you want to base your report on.

        Note You don't need to do this step if you clicked Report Wizard in step 3—
        you can specify the record source for the report in the wizard.

   5. Click OK.
   6. Follow the instructions in the wizard.

        If the resulting report doesn't look the way you want, you can change it in
        Design view.


       If you want to include fields from multiple tables and queries in your report,
        don't click Next or Finish after selecting the fields from the first table or query
        in the Report Wizard. Repeat the steps to select a table or query, and pick
        fields that you want to include in the report, until you have selected all the
        required fields.
       If you click one of the AutoReport options, Microsoft Access uses the
        autoformat you last specified, either in the Report Wizard or using the
        AutoFormat command on the Format menu in Design view.


Shared By: