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

[A M I R]

[h=2]Printing

[h=4]To Modify Margins While in Print Preview:
The margins of your worksheet may need to be adjusted to make data fit more

comfortably on the printed page. You can adjust the margins in Print Preview.



Click the File tab.

Select Print to access the Print pane.

Click on the Show Margins button. Your margins will appear.

Hover your mouse over one of the margin markers

until the double arrow

appears.

Click and drag the margin to your desired location.

Release the mouse. The margin is modified.

​

 

[A M I R]

[h=2]Printing

[h=4]To Use Print Titles:

Imagine how difficult it would be to read a worksheet if the column and row headings only

appeared on the first page. The Print Titles command allows you to select specific rows and columns to appear on each page.





Click the Page Layout tab.

Select the Print Titles command.

The Page Setup dialog box appears. Click the icon at the end of the Rows to repeat at top field.

Your mouse becomes the small selection arrow

. Click on the rows you want to

appear on each printed page. The Rows to repeat at top dialog box will record your selection.

Click the icon at the end of the Rows to repeat at top field.

Repeat for Columns to repeat at left, if necessary.

Click OK. You can go to Print Preview to see how each page will look when printed.
​

 

[A M I R]

[h=2]Printing

[h=4]To Insert a Break:

Click the Page Layout tab.

Determine the placement of the break by clicking on the row below, cell below, or column to the right of where

you want the break to appear. For example, select column C and a break will appear after column B.

Select the Insert Page Break command from the Breaks drop-down menu.

The break is inserted. You can go to Print Preview to confirm it appears in the correct place on the page.



​

 

[A M I R]

[h=2]Printing

[h=3]Challenge!

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

Try printing two active worksheets. If you are using the example, try printing the worksheets labeled 2009 and 2010.

Try printing only a selection of cells.

Change the page orientation to Landscape.

Try fitting a large worksheet on one page.

Try modifying the margins of a worksheet in Print Preview.

If you are using the example, use the Print Titles command to make row 1 and column A appear on every page.


Create a page break so that only columns A and B appear on one page.


​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=3]Creating Complex Formulas

Excel is a spreadsheet application that can help you calculate and analyze numerical information for household budgets, company

finances, inventory, and more. To do this, you need to understand complex formulas.


In this lesson, you will learn how to write complex formulas in Excel following the order of operations. You will also learn

about relative and absolute cell references and how to copy and fill formulas containing cell references.

​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=3]Complex Formulas

Simple formulas have one mathematical operation, such as 5+5. Complex formulas have

more than one mathematical operation, such as 5+5-2. When there is more than one operation in a

formula, the order of operations tells us which operation to calculate first. In order to use Excel to

calculate complex formulas, you will need to understand the order of operations.

​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=4]Order of Operations

Excel calculates formulas based on the following order of operations:


Operations enclosed in parentheses

Exponential calculations (to the power of)


Multiplication and division, whichever comes first

Addition and subtraction, whichever comes first



A mnemonic that can help you remember the order is Please Excuse My Dear Aunt Sally.

---

[h=5]Example 1
The following example demonstrates how to use the order of operations to calculate a formula:

---

[h=5]Example 2



In this example, we will review how Excel will calculate a complex formula using the order of

operations. The selected cell will display the percent of total Pete Lily seeds sold that were white.

First, Excel will calculate the amount sold in parentheses: (19*1.99)=37.81 White Lily seeds and (33*1.99)=65.67 Total Lily seeds.

Second, it will divide the White Lily seeds amount by the Total Lily seeds amount: 37.81/65.67=.5758.

Last, it will multiply the result by 100 to obtain the value as a percent: .5758*100=57.58.


Based on this complex formula, the result will show that 57.58% of the total Pete Lily seeds sold were

white. You can see from this example, that it is important to enter complex formulas with the correct

order of operations. Otherwise, Excel will not calculate the results accurately.​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=4]To Create a Complex Formula Using the Order of Operations:

In this example, we will use cell references in addition to actual values, to create a complex formula that will add tax to the nursery order.





Click the cell where you want the formula result to appear (for example, F11).

Type the equal sign (=).

Type an open parenthesis, then click on the cell that contains the first value you want in the formula (for example, F4).

Type the first mathematical operator (for example, the addition sign).

Click on the cell that contains the second value you want in the formula (for example, F5), and then type a closed parenthesis.

Type the next mathematical operator (for example, the multiplication sign).

Type the next value in the formula (for example, 0.055 for 5.5% tax).

Click Enter to calculate your formula. The results show that $2.12 is the tax for the nursery order.

​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=3]Working with Cell References

In order to maintain accurate formulas, it is necessary to understand how cell references

respond when you copy or fill them to new cells in the worksheet.





Excel will interpret cell references as either relative or absolute. By default, cell references are relative

references. When copied or filled, they change based on the relative position of rows and columns. If

you copy a formula (=A1+B1) into row 2, the formula will change to become (=A2+B2).




Absolute references, on the other hand, do not change when they are copied or

filled and are used when you want the values to stay the same.






​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=3]Relative References

Relative references can save you time when you are repeating the same kind of calculation across multiple rows or columns.



In the following example, we are creating a formula with cell references in row 4 to calculate

the total cost of the electric bill and water bill for each month (B4=B2+B3). For the upcoming

months we want to use the same formula with relative references (C2+C3, D2+D3, E2+E3, etc.) For

convenience, we can copy the formula in B4 into the rest of row 4 and Excel will calculate the value

of the bills for those months using relative references.

​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=4]To Create and Copy a Formula Using Relative References:

Select the first cell where you want to enter the formula (for example, B4).

Enter the formula to calculate the value you want (for example, add B2+B3).

Press Enter. The formula will be calculated.

Select the cell you want to copy (for example, B4) and click on the Copy command from the Home tab.

Select the cells where you want to paste the formula and click on the Paste command from the Home tab. (You may also drag the fill handle to fill cells.)

Your formula is copied to the selected cells as a relative reference (C4=C2+C3, D4=D2+D3, E4=E2+E3, etc.) and the values are calculated.
​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=3]Absolute References


There may be times when you do not want a cell reference to change when copying or filling

cells. You can use an absolute reference to keep a row and/or column constant in the formula.






An absolute reference is designated in the formula by the addition of a dollar sign ($). It

can precede the column reference, the row reference, or both.

In the below example, we want to calculate the sales tax for a list of products with varying prices. We

will use an absolute reference for the sales tax ($B$1) because we do not want it to change as we

are copying the formula down the column of varying prices.


​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=4]To Create and Copy a Formula Using an Absolute Reference:
Select the first cell where you want to enter the formula (for example, C4)

Click on the cell that contains the first value you want in the formula (for example, B4).

Type the first mathematical operator (for example, the multiplication sign).

Type the dollar sign ($) and enter the column letter of the cell you are making an absolute reference to (for example, B).

Type the dollar sign ($) and enter the row number of the same cell you are making an absolute reference to (for example, 1).

Press Enter to calculate the formula.

Select the cell you want to copy (for example, C4) and click on the Copy command from the Home tab.

Select the cells where you want to paste the formula and click on the Paste command from the Home tab. (You may also drag the fill handle to fill cells.)

Your formula is copied to the selected cells using the absolute reference (C5=B5*$B$1, C6=B6*$B$1, etc.) and your values are calculated.
​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=3]Challenge!

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

Create a complex formula that uses the addition and subtraction operations.

Create a complex formula that uses the multiplication and division operations.

Create a formula that uses an absolute reference. If you are using the example, calculate the sales tax in E4:E20. Use cell C23 as your absolute reference to the price of sales tax.


Create a formula that uses a relative reference. If you are using the example, create a formula that adds the price of

each item (in column D) and the sales tax for each item (in column E), and then multiplies the result by the quantity

of each item (in column F). Enter your results in the totals column (column G).

​

 

[A M I R]

[h=2]Working with Basic Functions

[h=3]Working with Basic Functions

Figuring out formulas for calculations you want to make in Excel can be tedious and complicated. Fortunately, Excel has

an entire library of functions or predefined formulas that you can take advantage of. You may be familiar with common

functions like sum, average, product or count, but there are hundreds of functions in Excel, even for things like

formatting text, referencing cells, calculating financial rates, analyzing statistics, and more.



In this lesson, you will learn the basics of inserting common functions into your worksheet by utilizing the

AutoSum and Insert Functions commands. You will also become familiar with how to search and find

various functions, including exploring Excel's Functions Library.

​

 

[A M I R]

[h=2]Working with Basic Functions

[h=3]Basic Functions
A function is a predefined formula that performs calculations using specific values in a particular order. One

of the key benefits of functions is that they can save you time since you do not have to write the formula

yourself. Excel has hundreds of different functions to assist with your calculations.




In order to use these functions correctly, you need to understand the different parts


of a function and how to create arguments in functions to calculate values and cell references.

​

 

[A M I R]

[h=2]Working with Basic Functions

[h=4]The Parts of a Function


The order in which you insert a function is important. Each function has a specific order, called

syntax, which must be followed for the function to work correctly. The basic syntax to create a

formula with a function is to insert an equal sign (=), a function name (SUM, for example, is

the function name for addition), and an argument. Arguments contain the information you

want the formula to calculate, such as a range of cell references.

​

 

[A M I R]

[h=2]Working with Basic Functions

Arguments must be enclosed in parentheses. Individual values or cell references inside the parentheses are separated by either colons or commas.

• Colons create a reference to a range of cells.
  
  For example, =AVERAGE(E19:E23) would calculate the average of the cell range E19 through E23.

• Commas separate individual values, cell references, and cell ranges in the parentheses. If there is more than one argument, you must separate each argument by a comma.
  
  For example, =COUNT(C6:C14,C19:C23,C28) will count all the cells in the three arguments that are included in parentheses.

 

[A M I R]

[h=2]Working with Basic Functions

[h=4]To Create a Basic Function in Excel:

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

Type the equal sign (=) and enter the function name (SUM, for example).

Enter the cells for the argument inside the parenthesis.

Press Enter and the result will appear.

​

 

[A M I R]

[h=2]Working with Basic Functions

[h=4]Using AutoSum to select Common Functions:

The AutoSum command allows you to automatically return the results for a range of cells for common functions like SUM and AVERAGE.




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

Click on the Home tab.

In the Editing group, click on the AutoSum drop-down arrow and select the function you desire (Average, for example).

A formula will appear in the selected cell E24. If logically placed, AutoSum will select your cells for

you. Otherwise, you will need to click on the cells to choose the argument you desire.

Press Enter and the result will appear.

The AutoSum command can also be accessed from the Formulas tab.



​