آموزش Excel 2000-EN

[A M I R]

[h=2]Using Functions


[h=3]Introduction

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

​

 

[A M I R]

[h=2]Using Functions

[h=3]Using Functions
[h=5]Important Term:

• A function is a pre-defined formula that helps perform common mathematical functions

Each function has a specific order, called syntax. Syntax must be followed for the function to work correctly.




​

 

[A M I R]

[h=2]Using Functions

[h=5]Syntax Order:
All functions begin with the = sign.

After the = sign is the function name.

One or more arguments enclosed in parentheses. If there

is more than one argument, they are separated by commas

An example of a function with one argument:

An example of a function with more than one argument:

Excel has hundreds of different functions (pre-defined formulas) to assist

you with your calculations. As you know, building individual formulas can be

difficult and time consuming. Using Excel's functions can save you a lot of time and headaches.

​

 

[A M I R]

[h=2]Using Functions

[h=3]Excel's Different Functions
There are many different functions in Excel. For our purposes in Excel 2000, we will discuss some common functions.
.



​

 

[A M I R]

[h=2]Using Functions​

Statistical, including:

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

Financial, including:

Interest Rates

Loan Payments


Depreciation Amounts

​

 

[A M I R]

[h=2]Using Functions

Date and Time:

Day of Week

Year

Time

Date

You don't have to memorize the functions, but have an idea of what they will do for you.
​

 

[A M I R]

[h=2]Using Functions

[h=3]Accessing Functions
A common function, the Sum function is located on the Standard toolbar.

​

 

[A M I R]

[h=2]Using Functions

[h=5]To use the Sum Function:
If necessary, enter the numbers you want to calculate

Select the numbers you want to total.

On the Standard toolbar, click the Sum button.

Your total appears in the cell below the range you selected.

​

 

[A M I R]

[h=2]Using Functions

[h=5]To Access Other Common Functions:
Click the cell where you want the result of the function to appear.

Type =

On the Formula Bar, click the Edit Formula button.

Choose a function from the Function Box drop-down list.

The result of the function appears.

If you didn't see the function you were looking for, there are

other functions available through the Paste Function dialog box, explained below.



​

 

[A M I R]

[h=2]Using Functions

[h=5]To Access Other Available Functions:

Click on the cell in which you want to place the end result.

Type =.

On the Formula Bar, in the Function Box drop down list, choose More Functions.

The Paste Function dialog box opens.

(Choosing Insert

Functions or clicking the Paste Function button

opens the Paste Function dialog box as well.)


Notice the Function Categories available, including statistical, financial, and date and time.

When a function category is selected, the corresponding function names

display. Additional information about the function name displays in the lower part of the dialog box

Click OK.

​

 

[A M I R]

[h=2]Using Functions

[h=3]Challenge!
In Excel, recreate the spreadsheet shown below.

In cell address B11, enter the sum of the range B5:B9 using the Sum function.

• Copy the formula to the other columns (C11:F11), totaling columns C, D, E, and F.

In cell address B12, enter the average of the range B5:B9 using the Edit Formula button.

Copy the function to the other columns (C12:F12), averaging columns C, D, E, and F.

Use the Paste Function button

to enter the COUNT in cell B13. Copy the function to the other columns (C13:F13).

​

 

[A M I R]

[h=2]Inserting and Deleting Rows and Columns

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

• Insert rows and columns

• Delete rows and columns

​

 

[A M I R]

[h=2]Inserting and Deleting Rows and Columns

[h=3]Inserting Columns
In Excel, you can insert a column anywhere you need it. Excel moves the existing columns to make room for the new one.

​

 

[A M I R]

[h=2]Inserting and Deleting Rows and Columns

[h=5]To Insert a Column:
Click anywhere in the column where you want to insert a new column.

Choose Insert

Columns from the menu bar.

A new column is inserted to the left of the existing column.

OR


Click anywhere in the column where you want to insert a new column.

Right-click and choose Insert from the shortcut menu. The Insert dialog box opens.

Click the Entire Column radio button in the Insert dialog box.

A new column is inserted to the left of the existing column.
​

 

[A M I R]

[h=2]Inserting and Deleting Rows and Columns

[h=5]To Insert Multiple Columns:




Select more than one column.

Choose a method (menu or shortcut menu) to insert a new column.

The number of columns you originally selected are inserted into the spreadsheet

Observe how the existing column C is moved to column D to make room for a new column (and new content).

​

 

[A M I R]

[h=2]Inserting and Deleting Rows and Columns

[h=3]Inserting Rows
You can insert a row in a spreadsheet anywhere you need it. Excel moves the existing rows down to make room for the new one.

​

 

[A M I R]

[h=2]Inserting and Deleting Rows and Columns

[h=5]To Insert a Row:
Click anywhere in the row below where you want to insert the new row.

Choose Insert

Rows from the menu bar.

A new row is inserted above the cell(s) you originally selected.

OR

Click in the row below where you want to insert the new row. A cell in the row will be selected.

Right-click and choose Insert from the shortcut menu. The Insert dialog box opens.

Choose the Entire Row radio button. Click OK.

A new row is inserted above the cell(s) you originally selected.

To quickly insert a row or column, select the row or column heading. Right-click to open the shortcut menu and add a new row.

Below, observe how the existing row 16 is moved to row 18 after a new row is inserted.

​

 

[A M I R]

[h=2]Inserting and Deleting Rows and Columns



[h=3]Deleting Columns and Rows

Deleting columns and rows is much like inserting columns and rows.
​

 

[A M I R]

[h=2]Inserting and Deleting Rows and Columns

[h=5]To Delete Columns and Rows:
Select one or more columns or rows.

To clear the content but leave the emptied row in place, press the Delete key on your keyboard.


OR

To delete the contents and remove the row or column, choose Edit

Delete from the menu bar.

​