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

[A M I R]

[h=2]Creating PivotTables

[h=4]To Add a Slicer:

Select any cell in your PivotTable. The Options tab will appear on the Ribbon.


From the Options tab, click the Insert Slicer command. A dialog box will appear.

Select the desired field. In this example, we will select Salesperson. Then click OK.

The slicer will appear next to the PivotTable. Each item that is selected will be highlighted in blue. In the example

below, the slicer contains a list of all of the different salespeople, and four of them are currently selected.

​

 

[A M I R]

[h=2]Creating PivotTables

[h=4]Using the Slicer:
Just like with report ******s, only the selected items are used in the PivotTable. When you select or deselect

items, the PivotTable will instantly reflect the changes. Try selecting different items to see how they affect the PivotTable.

• To select a single item, just click on it.
• To select multiple items, hold down the Control (Ctrl) key on your keyboard, and then click on each item that you want.
• You can also select multiple items by clicking and dragging the mouse. This is useful if the desired items are adjacent to one another, or if you want to select all of the items.
• To deselect an item, hold down the Control (Ctrl) key on your keyboard, and then click on the item.

​

 

[A M I R]

[h=2]Creating PivotTables

[h=3]PivotCharts
PivotCharts are like regular charts, except they display data from a PivotTable. As with a regular

chart, you'll be able to select a chart type, layout and style to best represent the data. In this

example, we'll use a PivotChart so we can visualize the trends in each sales region.

​

 

[A M I R]

[h=2]Creating PivotTables

[h=4]To Create a PivotChart:
Select any cell in your PivotTable. The Options tab will appear in the Ribbon.

From the Options tab, click the PivotChart command.

From the dialog box, select the desired chart type (3-D Clustered Column, for example) and click OK.

The PivotChart will appear in the worksheet. If you want, you can move it by clicking and dragging.

If you make any changes to the PivotTable, the PivotChart will adjust automatically.




​

 

[A M I R]

[h=2]Creating PivotTables

[h=3]Challenge!

• Open an existing Excel workbook. If you want, you can use this example.
• Create a PivotTable using the data in the workbook.
• Experiment with different Row Labels and Column Labels.
• ****** the report with a slicer.
• Create a PivotChart.
• If you are using the example, use the PivotTable to answer the question, "Which salesperson sold
• the lowest amount in January?" Hint: First decide which fields you need in order to answer the question.

 

[A M I R]

[h=2]Using What-If Analysis

[h=3]Using What-If Analysis

The real power in Excel comes from its ability to perform multiple mathematical calculations for you. One of the tools in

Excel that you can use to perform these calculations is a data tool called what-if analysis. What-if analysis allows you

to see the effect that different values have in formulas. Have you ever thought, "What interest rate do I need to qualify

for to have a car payment of $400 on the car I want?" This question can be answered using What-If Analysis.



In this lesson, you will learn how to use a what-if analysis tool called Goal Seek.

​

 

[A M I R]

[h=2]Using What-If Analysis

[h=3]What-If Analysis
In many worksheets, there may be some cells whose values are unknown, or you may just want to change certain

cells to see what the outcome is. What-if analysis is perfect for these situations. It allows you to experiment and

answer questions with your data, even when the data is incomplete.

​

 

[A M I R]

[h=2]Using What-If Analysis

[h=3]Goal Seek
Goal Seek is a type of what-if analysis that is useful if you know the desired result, but need to find the input value

that will give you that result. For example, suppose you need a loan to buy a new car. You already know that you want

a loan amount of $20,000, a 60-month term (the length of time it takes to pay off the loan), and a payment of no

more than $400 a month. However, you're not sure yet what the interest rate is going to be.




In the image below, you can see that Interest Rate is left blank, and Payment is $333.33. That's because the payment is

being calculated by a specialized function called the PMT (Payment) function, and $333.33 is what the monthly payment

would be if there were no interest ($20,000 divided by 60 monthly payments).

If we typed different values into the empty Interest Rate cell, we could eventually find the value that causes Payment

to be $400, and that would be the highest interest rate that we could afford. However, Goal Seek can do this automatically

by starting with the result and working backward.





​

 

[A M I R]

[h=2]Using What-If Analysis

[h=4]To Insert the Payment Function:

1. Select the cell where you want the function to be.
2. From the Formula tab, select the Financial command.

A drop-down menu will appear showing all finance-related functions. Scroll down and select the PMT function.

A dialog box will appear
.
Enter the desired values and/or cell references into the different fields. In this example, we're only using Rate, Nper (the number of payments), and Pv (the loan amount)

Click OK. The result will appear in the selected cell. Note that this is not our final result, as we still don't know what the interest rate will be.

.
​

 

[A M I R]

[h=2]Using What-If Analysis

[h=4]To Use Goal Seek to Find the Interest Rate:
From the Data tab, click the What-If Analysis command.

Select Goal Seek.

A dialog box will appear containing three fields:

• Set cell: This is the cell that will contain the desired result (in this case, the monthly payment). In this example, we will set it to B5 (it doesn't matter whether it's an absolute or relative reference).
• To value: This is the desired result. We'll set it to -400. Since we're making a payment that will be subtracted from our loan amount, we have to enter the payment as a negative number.
• By changing cell: This is the cell where Goal Seek will place its answer (in this case, the interest rate). We'll set it to B4.

When you're done, click OK. The dialog box will tell you whether or not Goal Seek was able to find a solution. In


this example, the solution is 7.42%, and it has been placed in cell B4. This tells us that a 7.42% interest rate

will give us a $400-a-month payment on a $20,000 loan that is paid off over 5 years, or 60 months.

​

 

[A M I R]

[h=2]Using What-If Analysis

[h=3]Other Types of What-If Analysis
For more advanced projects, you may want to look at the other two types of what-if analysis: scenarios and

data tables. Rather than starting from the desired result and working backward, like Goal Seek, these options

allow you to test multiple values and see how the result changes.




Below is an introduction to some of the things you can do with scenarios and data tables.

Scenarios let you substitute values for multiple cells (up to 32) at the same time. It is especially well-suited to showing

best-case and worst-case scenarios. You can create as many scenarios as you want, and then compare them without

having to manually change all of the values. In the example below, each scenario contains a term and an interest rate. When

each scenario is selected, it will replace the values in the spreadsheet with its own values, and the result will be recalculated.

For more information about scenarios, check out this article on the Microsoft site.


Data Tables allow you to take one or two variables in a formula and replace them with as many different values as you want, and

then view the results in a table. This option is especially powerful because it shows multiple results at the same time, unlike Scenarios

or Goal Seek. In the example below, 24 possible results are shown in the table; doing the same task with Scenarios would take much longer.

For more information about data tables, check out this article on the Microsoft site.

​

 

[A M I R]

[h=2]Using What-If Analysis

[h=3]Challenge!

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

Insert the PMT function into the worksheet. If you are using the example, insert the function into cell B5.


Use Goal Seek to find the interest rate you will need in order to have a monthly payment of $400. What interest rate would you need if you could only afford a $380 monthly payment?
​