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

[A M I R]

[h=2]Working with Basic Functions

[h=3]Function Library

There are hundreds of functions in Excel, but only some will be useful for the kind of data you

are working with. There is no need to learn every single function, but you may want to explore

some of the different kinds to get ideas about which ones might be helpful to you as you create new spreadsheets.


A great place to explore functions is in the Function Library on the Formulas tab. Here you may search and select


Excel functions based on categories such as Financial, Logical, Text, Date & Time, and more. .


​

 

[A M I R]

[h=2]Working with Basic Functions

[h=3]Insert Function

The Insert Function command allows you to easily search for a command by entering a description of what you are looking for.


​

​

 

[A M I R]

[h=2]Working with Basic Functions

[h=3]AutoSum

The AutoSum command allows you to automatically return results for common functions.

​

​

 

[A M I R]

[h=2]Working with Basic Functions

[h=3]Recently Used

Use the Recently Used command to access functions that you have recently worked with.


​

​

 

[A M I R]

[h=2]Working with Basic Functions

[h=3]Financial

The Financial category contains functions for financial calculations like determining a payment (PMT) or interest rate for a loan (RATE).


​

​

 

[A M I R]

[h=2]Working with Basic Functions

[h=3]Logical

Functions in the Logical category check arguments for a value or condition. For

example, if an order is over $50 add $4.99 for shipping, but if it is over $100, do not charge for shipping (IF).
​

​

 

[A M I R]

[h=2]Working with Basic Functions

The Text category contains functions that work with the text in arguments using tasks

like converting text to lowercase (LOWER) or replacing text (REPLACE).
​

​

 

[A M I R]

[h=2]Working with Basic Functions

[h=3]Date & Time

The Date & Time category contains functions for working with dates and time and will

return results like the current date and time (NOW) or the seconds (SECOND).
​

​

 

[A M I R]

[h=2]Working with Basic Functions

[h=3]Lookup & Reference

The Lookup & Reference category contains functions that will return results for finding

and referencing. For example, you can add a hyperlink (HYPERLINK) to a cell or return

the value of a particular row and column intersection (INDEX).
​

​

 

[A M I R]

[h=2]Working with Basic Functions

[h=3]Math & Trig

The Math & Trig category includes functions for numerical arguments. For example, you

can round values (ROUND), find the value of Pi (PI) multiply (PRODUCT), subtotal (SUBTOTAL), and much more.
​

​

 

[A M I R]

[h=2]Working with Basic Functions

[h=3]More Functions

More Functions contains additional functions under categories for

Statistical, Engineering, Cube, Information, and Compatibility.
​

​

 

[A M I R]

[h=2]Working with Basic Functions

[h=4]To Insert a Function from the Function Library:


Select the cell where the answer will appear (I6, for example)

Click on the Formulas tab.

From the Function Library group, select the function category you desire. In this example, we will choose Date & Time.

Select the desired function from the Date & Time drop-down menu. We will choose the NETWORKDAYS

function to count the days between the order date and receive date in our worksheet.


The Function Arguments dialog box will appear. Insert the cursor in the first field and then enter or select the cell(s) you desire (G6, for example).


Insert the cursor in the next field and then enter or select the cell(s) you desire (H6, for example).


Click OK and the result will appear. Our results show that it took 5 days to receive the order.


​

 

[A M I R]

[h=2]Working with Basic Functions

[h=3]Insert Function Command


The Insert Function command is convenient because it allows you to search for a

function by typing a description of what you are looking for or by selecting a category to

peruse. The Insert Function command can also be used to easily enter or select more than one argument for a function.

​

 

[A M I R]

[h=2]Working with Basic Functions

[h=4]Using the Insert Function command:
In this example, we want to find a function that will count the total number of supplies listed

in the Office Supply Order Log. The basic COUNT function only counts cells with numbers; we

want to count the cells in the Office Supply column, which uses text. Therefore, we will need to find a formula that counts cells with text.


Select the cell where the answer will appear (A27, for example)

Click on the Formulas tab and select the Insert Function command.


The Insert Function dialog box will appear.


Type a description of the function you are searching for and click Go. For our

example, we will type: Count cells with text. (You may also search by selecting a category.)


Review the results to find the function you desire. We will use COUNTA. Then click OK.


The Function Arguments dialog box will appear. Insert the cursor in the

first field and then enter or select the cell(s) you desire (A6:A14, for example).


Insert the cursor in the next field and then enter or select the cell(s) you

desire (A19:A23, for example). (You may continue to add additional arguments if needed.)


Click OK and the result will appear. Our results show that 14 Total Supplies were ordered from our log.


​

 

[A M I R]

[h=2]Working with Basic Functions

[h=3]Challenge!

Open an existing Excel 2010 workbook. If you want, you can use this example.

Create a function that contains more than one argument.

Use AutoSum to insert a function. If you are using the example, insert the MAX function in cell E15 to find the highest priced supply.

Insert a function from the Functions Library. If you are using the example, find the PRODUCT function (multiply) to calculate the Unit Quantity times the Unit Price in cells F19 through F23.

Use the Insert Function command to search and explore functions.

​

 

[A M I R]

[h=2]Sorting Data

[h=3]Introduction

With over 17 billion cells in a single worksheet, Excel 2010 gives you the ability to work with an enormous

amount of data. Arranging your data alphabetically, from smallest to largest, or other criteria, can help you find the information you're looking for more quickly.


In this lesson, you will learn how to sort data to better view and organize the contents of your spreadsheet.


​

 

[A M I R]

[h=2]Sorting Data

[h=3]Basic Sorting

Sorting is a common task that allows you to change or customize the order of your spreadsheet data. For

example, you could organize an office birthday list by employee, birthdate, or department, making it easier

to find what you're looking for. Custom sorting takes it a step further, giving you the ability to sort multiple

levels (such as department first, then birthdate, to group birthdates by department), and more.






​

 

[A M I R]

[h=2]Sorting Data

[h=4]To Sort in Alphabetical Order:

Select a cell in the column you want to sort by. In this example, we will sort by Last Name.


Select the Data tab, and locate the Sort and ****** group.

Click the ascending command to Sort A to Z, or the descending command to Sort Z to A.


The data in the spreadsheet will be organized alphabetically.


Sorting options can also be found on the Home tab, condensed into the Sort & ****** command.

​

 

[A M I R]

[h=2]Sorting Data

[h=4]To Sort in Numerical Order:
Select a cell in the column you want to sort by.


From the Data tab, click the ascending command to Sort Smallest to Largest, or the descending command to Sort Largest to Smallest.

The data in the spreadsheet will be organized numerically.


​

 

[A M I R]

[h=2]Sorting Data

[h=4]To Sort by Date or Time:

Select a cell in the column you want to sort by.


From the Data tab, click the ascending command to Sort Oldest to Newest, or the descending command to Sort Newest to Oldest.

The data in the spreadsheet will be organized by date or time.

​