آموزش کامل Excel 2003 -EN

[A M I R]

[h=2]Using functions

[h=3]Excel's Different Functions




There are many different functions in Excel 2003. Some of the more common functions include:

​

 

[A M I R]

[h=2]Using functions

[h=5]Statistical Functions:

• SUM - summation adds a range of cells together.

• AVERAGE - average calculates the average of a range of cells.

• COUNT - counts the number of chosen data in a range of cells.

• MAX - identifies the largest number in a range of cells.

• MIN - identifies the smallest number in a range of cells.

​

 

[A M I R]

[h=2]Using functions

[h=5]Financial Functions:

• Interest Rates

• Loan Payments

• Depreciation Amounts

​

 

[A M I R]

[h=2]Using functions

[h=5]Date and Time functions:

• DATE - Converts a serial number to a day of the month

• Day of Week

• DAYS360 - Calculates the number of days between two dates based on a 360-day year

• TIME - Returns the serial number of a particular time

• HOUR - Converts a serial number to an hour

• MINUTE - Converts a serial number to a minute

• TODAY - Returns the serial number of today's date

• MONTH - Converts a serial number to a month

• YEAR - Converts a serial number to a year

You don't have to memorize the functions but should have an idea of what each can do for you.

​

 

[A M I R]

[h=2]Using functions

[h=3]Finding the Sum of a Range of Data
The AutoSum function allows you to create a formula that includes a cell range-many cells in a column, for example, or many cells in a row.

​

 

[A M I R]

[h=2]Using functions

[h=5]To Calculate the AutoSum of a Range of Data:

• Type the numbers to be included in the formula in separate cells of column B (Ex: type 128 in cell B2, 345 in cell B3, 243 in cell B4, 97 in cell B5 and 187 cell B6).

• Click on the first cell (B2) to be included in the formula.

• Using the point-click-drag method, drag the mouse to define a cell range from cell B2 through cell B6.

• On the Standard toolbar, click the Sum button.

• The sum of the numbers is added to cell B7, or the cell immediately beneath the defined range of numbers.

• Notice the formula, =SUM(B2:B6), has been defined to cell B7.

​

 

[A M I R]

[h=2]Using functions

[h=3]Finding the Average of a Range of Numbers

The Average function calculates the average of a range of numbers. The

Average function can be selected from the AutoSum drop-down menu.

​

 

[A M I R]

[h=2]Using functions

[h=5]To Calculate the Average of a Range of Data:

• Type the numbers to be included in the formula in separate cells of column B (Ex: type 128 in cell B2, 345 in cell B3, 243 in cell B4, 97 in cell B5 and 187 cell B6).

• Click on the first cell (B2) to be included in the formula.

• Using the point-click-drag method, drag the mouse to define a cell range from cell B2 through cell B6.

• On the Standard toolbar, click on the drop-down part of the AutoSum button.

• 
• Select the Average function from the drop-down Functions list.
• 
  
  
  
• The average of the numbers is added to cell B7, or the cell immediately beneath the defined range of numbers.

• Notice the formula, =AVERAGE(B2:B6), has been defined to cell B7.
• 
  
  
  

​

 

[A M I R]

[h=2]Using functions

[h=3]Accessing Excel 2003 Functions [h=5]To Access Other Functions in Excel:

• Using the point-click-drag method, select a cell range to be included in the formula.

• On the Standard toolbar, click on the drop-down part of the AutoSum button.

• If you don't see the function you want to use (Sum, Average, Count, Max, Min), display additional functions by selecting More Functions.

• The Insert Function dialog box opens.

• There are three ways to locate a function in the Insert Function dialog box:

You can type a question in the Search for a function box and click GO, or


You can scroll through the alphabetical list of functions in the Select a function field, or



You can select a function category in the Select a category drop-down list and review the corresponding function names in the Select a function field.

Select the function you want to use and then click the OK button.

​

 

[A M I R]

[h=2]Using functions

[h=3]Challenge!

• Open your Monthly Budget file.

• Type the following in Row 1:

• February in D1.

• March in E1.

• April in F1.

• May in G1.

• June in H1.

• July in I1.

• August in J1.

• September in K1.

• October in L1.

• November in M1.

• December in N1.

• Total in O1.

• Type the amount of your expenses in each cell in Column D (cells 2 through 17), just like you did with Column C in a previous challenge.

• Delete the number in C15.

• Type a function in cell C15 that adds the range of cells, C2 through C14.

• Fill the formula from C15 to D15 through O15.

• Type your Income for the month of February in D16.

• Type a formula in O17 that adds your savings for the year. Since you have only entered data for
• the month of January and February, this amount indicates your savings for the two months.

• Save and close the Monthly Budget spreadsheet.

​

 

[A M I R]

[h=2]Working with multiple worksheets

[h=3]Introduction[h=4]By the end of this lesson, learners should be able to:

• Name Worksheets

• Insert and Delete Worksheets

• Group and Ungroup Worksheets

• Copy and Move Worksheets

​

 

[A M I R]

[h=2]Working with multiple worksheets

[h=3]Naming Worksheets


At the beginning of this course, we learned that the tabs displayed at the bottom

of the screen are named Sheet1, Sheet2 and Sheet3. These are not very informative

names. Excel 2003 allows you to define a meaningful name for each worksheet in a

workbook-Checkbook, Reports, Accounts-so you can quickly locate information.



​

 

[A M I R]

[h=2]Working with multiple worksheets

[h=5]To Name a Worksheet:

• Double-click the sheet tab to select it. The text is highlighted by a black box.
  
  
  
• Type a new name for the worksheet.
  
  
  
• Press the Enter key.

• The worksheet now assumes the descriptive name defined.

​

 

[A M I R]

[h=2]Working with multiple worksheets

[h=3]Inserting Worksheets

By default, each new workbook in Excel 2003 defaults to three worksheets named

Sheet1, Sheet2 and Sheet3. You have the ability to insert new worksheets if

needed or delete others you no longer want.

​

 

[A M I R]

[h=2]Working with multiple worksheets

[h=5]To Insert a New Worksheet:

• Choose Insert
  
  Worksheet from the menu bar.
  
  
  
• A new worksheet tab is added to the bottom of the screen. It will be named
• Sheet4, Sheet5 or whatever the next sequential sheet number may be in the workbook.

​

 

[A M I R]

[h=2]Working with multiple worksheets

[h=3]Deleting Worksheets


Any worksheet can be deleted from a workbook, including those that have

data in it. Remember, a workbook must contain at least one worksheet.

​

 

[A M I R]

[h=2]Working with multiple worksheets

[h=5]To Delete One or More Worksheets:

• Click on the sheet(s) you want to delete.

• Choose Edit
  
  Delete Sheet from the menu bar.
• 
  
  
  
• The following dialog box appears if the sheet being deleted contains information on it.
  
  
  
• Click the Delete button to remove the worksheet and all the data in it.

Another way to delete or insert a worksheet is to right-click on the

sheet to be deleted and then select Delete or Insert from the shortcut menu.

​

 

[A M I R]

[h=2]Working with multiple worksheets

[h=3]Grouping and Ungrouping Worksheets

A workbook is a multi-page Excel document that contains multiple worksheets. Sometimes

you will want to work with the worksheets one at a time as if each is a single unit. Other

times, the same information or formatting may need to be added to every worksheet. You

can type and retype the same information in each worksheet, or apply identical

formatting, or you can group the worksheet and enter the information once.


Worksheets can also be combined together into a group. Grouping worksheets allows you

to apply identical formulas and/or formatting across all the worksheets in the group. When

you group worksheets, any changes made to one worksheet will also be changed in any other

worksheets in the group. If many worksheets are to have the same

data--regions, departments, quarters, months, weeks and days, for

example--then you type it once and it will appear on every worksheet included in the grouping.

​

 

[A M I R]

[h=2]Working with multiple worksheets

[h=5]To Group Worksheets:
To select one worksheet, click on the sheet tab.


To select more than one worksheet, hold the Control key down and click on one or more worksheet tabs in the workbook.



To select all worksheets in a workbook, right-click on any worksheet tab and choose Select All Sheets from the shortcut menu.


When finished entering, moving, copying or formatting the data, you will need to ungroup

worksheets. If you do not ungroup the sheets, any work you do in one sheet will be duplicated in all the others.




​

 

[A M I R]

[h=2]Working with multiple worksheets

[h=5]To Ungroup Worksheets:

• Right-click on any of the selected worksheet tabs.

• Choose Ungroup Sheets from the shortcut menu.

​