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

[A M I R]

[h=2]Working with Sparklines

[h=4]To Change the Display Range:

Select the sparklines that you want to change.

In the Design tab, click the Axis command. A drop-down menu will appear.

Under Vertical Axis Minimum Value Options and Vertical Axis Maximum Value Options, select Same for All Sparklines.

The sparklines will update to reflect the new range.

​

 

[A M I R]

[h=2]Working with Sparklines

[h=3]Challenge!
Open an existing Excel workbook. If you want, you can use this example.

Create a sparkline on the first row of data. If you are using the example, create a sparkline for the first salesperson.

Use the fill handle to create sparklines for the remaining rows.

Change the sparkline type.

Create markers for the High Point and Low Point.

​

 

[A M I R]

[h=2]Using Conditional Formatting

[h=3]Introduction

Imagine you have a spreadsheet with thousands of rows of data. It would be extremely difficult to see

patterns and trends just from examining the raw data. Excel gives us several tools that will make this task easier. One

of these tools is called conditional formatting. With conditional formatting, you can apply formatting to one or more

cells based on the value of the cell. You can highlight interesting or unusual cell values, and

visualize the data using formatting such as colors, icons, and data bars.

In this lesson, you will learn how to apply, modify, and remove conditional formatting rules.

​

 

[A M I R]

[h=2]Using Conditional Formatting

[h=3]Conditional Formatting

Conditional formatting applies one or more rules to any cells that you want. An example of a rule might

be "If the value is greater than 5,000, color the cell yellow." By applying this rule to the cells in a worksheet, you'll

be able to see at a glance which cells are over 5,000. There are also rules that can mark the top 10 items, all cells that

are below the average, cells that are within a certain date range, and many more.

​

 

[A M I R]

[h=2]Using Conditional Formatting

[h=4]To Create a Conditional Formatting Rule:
Select the cells that you want to add the formatting to.

In the Home tab, click the Conditional Formatting command. A drop-down menu will appear.

Select Highlight Cells Rules or Top/Bottom Rules. We will choose Highlight Cells Rules for this example. A menu will appear with several rules.

Select the desired rule (Greater Than, for example).

From the dialog box, enter a value in the space provided, if applicable. In this example, we want to format cells

that are greater than $5,000, so we'll enter 5000 as our value. If you want, you can enter a cell reference instead of a number.

Select a formatting style from the drop-down menu.

The formatting will be applied to the selected cells.

If you want, you can apply more than one rule to your cells.

​

 

[A M I R]

[h=2]Using Conditional Formatting

[h=3]Conditional Formatting Presets
Excel has a number of presets that you can use to quickly apply conditional formatting to your cells. They are grouped into three categories:

Data Bars are horizontal bars added to each cell, much like a bar graph.

Color Scales change the color of each cell based on its value. Each color scale uses a two or three

color gradient. For example, in the Green - Yellow - Red color scale, the highest values are green, average

values are yellow, and the lowest values are red.

Icon Sets add a specific icon to each cell based on its value.

​

 

[A M I R]

[h=2]Using Conditional Formatting

[h=4]To Use Preset Conditional Formatting:
Select the cells you want to add the formatting to.

In the Home tab, click the Conditional Formatting command. A drop-down menu will appear.

Select Data Bars, Color Scales or Icon Sets (Data Bars, for example). Then, select the desired preset.

The conditional formatting will be applied to the selected cells.

 

[A M I R]

[h=2]Using Conditional Formatting

[h=4]To Remove Conditional Formatting Rules:

Select the cells that have conditional formatting.

In the Home tab, click the Conditional Formatting command. A drop-down menu will appear.

Select Clear Rules.

A menu will appear. You can choose to clear rules from the Selected Cells,

You can edit or delete individual rules by clicking on the Conditional Formatting command and selecting

Manage Rules. This is especially useful if you have applied multiple rules to the cells.

​

 

[A M I R]

[h=2]Using Conditional Formatting

[h=3]Challenge!
Open an existing Excel workbook. If you want, you can use this example.

Apply conditional formatting to a range of cells with numerical values. If you are using the example, apply the formatting to all of the sales data.

Apply a second conditional formatting rule to the same set of cells.

Explore the Conditional Formatting Rules Manager dialog box.

Clear all conditional formatting rules from the worksheet.

​

 

[A M I R]

[h=2]Creating PivotTables

[h=3]Creating PivotTables

PivotTable reports (or, simply PivotTables) make the data in your worksheets much more manageable by summarizing the data

and allowing you to manipulate it in different ways. PivotTables can be an indispensable tool when used with large, complex

spreadsheets, but they can be used with smaller spreadsheets as well.


In this lesson, you will learn the basics of creating and manipulating PivotTables.

​

 

[A M I R]

[h=2]Creating PivotTables

[h=3]PivotTables

When you have a lot of data, it can sometimes be difficult to analyze all of it. A PivotTable summarizes

the data, making it easier to manage. Best of all, you can quickly and easily change the PivotTable to see

the data in a different way, making this an extremely powerful tool.





​

 

[A M I R]

[h=2]Creating PivotTables

[h=3][h=4]Using PivotTables to Answer Questions[h=3]
The example below contains sales statistics for a fictional company. There is a row for each order, and it includes the

order amount, the name of the salesperson who made the sale, the month, the sales region, and the customer's account number.

Suppose we wanted to answer the question, "What is the amount sold by each salesperson?" This could be

time-consuming, as each salesperson appears on multiple rows, and we would need to add up all of the order

amounts for each salesperson. Of course, we could use the Subtotal feature to add them, but we would still have a lot of data to sift through.




Luckily, a PivotTable can instantly do all of the math for us and summarize the data in a way that's not

only easy to read, but easy to manipulate. When we're done, the PivotTable will look something like this:

As you can see, the PivotTable is much easier to read. It only takes a couple of steps to create one, and once

you create it you'll be able to take advantage of the PivotTable's powerful features.

​

 

[A M I R]

[h=2]Creating PivotTables

[h=4]To Create a PivotTable:

Select the table or cells (including column headers) containing the data you want to use.

From the Insert tab, click the PivotTable command.

The Create PivotTable dialog box will appear. Make sure the settings are correct, and then click OK.

A blank PivotTable will appear on the left, and the Field List will appear on the right

​

 

[A M I R]

[h=2]Creating PivotTables

[h=4]To Add Fields to the PivotTable:

Now, you'll need to decide which fields to add to the PivotTable. Each field is simply a column

header from the source data. It may be helpful to recall the question that you are trying to

answer. In this example, we want to know the total amount sold by each salesperson, so we'll just need the Order Amount and Salesperson fields.





In the Field List, place a checkmark next to each field you want to add.


The selected fields will be added to one of the four Areas below the Field List. In this example, the

Salesperson field is added to the Row Labels area, and the Order Amount is added to the Values area. If a field is not in the desired area, you can drag it to a different one.



The PivotTable now shows the amount sold by each salesperson.

Just like with normal spreadsheet data, you can sort the data in a PivotTable using the Sort & ****** command

in the Home tab. You can also apply any type of formatting that you want. For example, you may want to

change the Number Format to Currency. However, be aware that some types of formatting may disappear when you modify the PivotTable.




​

 

[A M I R]

[h=2]Creating PivotTables

[h=3]Pivoting Data
One of the best things about PivotTables is that they let you "pivot" the data in order to look

at it in a different way. This allows you to answer multiple questions and even experiment

with the data to learn new things about it.




In our example, we used the PivotTable to answer the question "What is the total amount sold by each

salesperson?" But now we'd like to answer a new question, such as "What is the total amount sold in

each month?" We can do this by simply changing the Row Labels.





​

 

[A M I R]

[h=2]Creating PivotTables

[h=4]To Change the Row Labels:

Drag any existing fields out of the Row Labels area, and they will disappear.

Drag a new field from the Field List into the Row Labels area. In this example, we're using the Month field.

The PivotTable will adjust to show the new data. In this example, it now shows us the total Order Amount for each month.

​

 

[A M I R]

[h=2]Creating PivotTables

[h=4]To Add Column Labels:
So far, our PivotTable has only shown one column of data at a time. In order to show multiple columns, you'll need to add Column Labels.

Drag a field from the Field List into the Column Labels area. In this example, we're using the Region field.

The PivotTable will now have multiple columns. In this example, there is a column for each region.

​

 

[A M I R]

[h=2]Creating PivotTables

[h=3]Report ******s
Sometimes you may want focus on just a portion of the data and ****** out everything else. In our example, we'

re going to focus on certain salespeople, to see how they affect the total sales.





​

 

[A M I R]

[h=2]Creating PivotTables

[h=4]To Add a Report ******:
Drag a field from the Field List into the Report ****** area. In this example, we're using the Salesperson field.

The report ****** appears above the PivotTable. Click the drop-down arrow on the right side of the ****** to view the list of items.


Select the item that you wish to view. If you want to select more than one item, place a checkmark

next to Select Multiple Items. Then click OK. In the example below, we are selecting five salespeople.

Click OK. The PivotTable will adjust to reflect the changes.

​

 

[A M I R]

[h=2]Creating PivotTables

[h=3]Slicers
Slicers were introduced in Excel 2010 to make ******ing data easier and more interactive. They're basically

just report ******s, but they're more interactive and faster to use, as they let you quickly select items and

instantly see the result. If you ****** your PivotTables a lot, you might want to use slicers instead of report ******s.

​