Excel Скачатьпрезентацию <<  Электронные таблицы Excel Microsoft Excel 2007  >>
 Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Objectives Objectives Working with Logical Functions Working with Logical Functions Working with Logical Functions Working with Logical Functions Using Lookup Tables and Functions Using Lookup Tables and Functions Using Lookup Tables and Functions Highlighting Duplicate Records with a Custom Format Highlighting Duplicate Records with a Custom Format Using the Conditional Formatting Rules Manager Using the IFERROR Function Using the IFERROR Function Summarizing Data Conditionally Summarizing Data Conditionally Summarizing Data Conditionally Using Advanced Filtering Using Advanced Filtering Using Advanced Filtering Using Database Functions to Summarize Data Using Database Functions to Summarize Data Using Database Functions to Summarize Data
Слайды из презентации «Excel 2007» к уроку информатики на тему «Excel»

Автор: Course Technology. Чтобы увеличить слайд, нажмите на его эскиз. Чтобы использовать презентацию на уроке, скачайте файл «Excel 2007.ppt» бесплатно в zip-архиве размером 2583 КБ.

Скачать презентацию

## Excel 2007

содержание презентации «Excel 2007.ppt»
СлайдТекст
1

Filtering.

2

### Objectives

Evaluate a single condition using the IF function Evaluate multiple conditions using the AND function Calculate different series of outcomes by nesting IF functions Test whether one or more conditions are true with the OR function Return values from a table with the VLOOKUP function Check for duplicate values using conditional formatting

New Perspectives on Microsoft Office Excel 2007

2

3

### Objectives

Check for data entry errors using the IFERROR function Summarize data using the COUNTIF, SUMIF, and AVERAGEIF functions Review the COUNTIFS, SUMIFS, and AVERAGEIFS functions Use advanced filters Summarize data using Database functions

New Perspectives on Microsoft Office Excel 2007

3

4

### Working with Logical Functions

IF Function IF(logical_test, value_if_true, [value_if_false]) AND Function =IF(AND(G2="FT",M2>=1),K2*0.03,0) Structured References You can replace the specific cell or range address with a structured reference, the actual table name or column header =SUM(Employee[Annual Salary])

New Perspectives on Microsoft Office Excel 2007

4

5

### Working with Logical Functions

New Perspectives on Microsoft Office Excel 2007

5

6

### Working with Logical Functions

A nested IF function is when one IF function is placed inside another IF function to test an additional condition =IF([Pay Grade]=1,2500,IF([Pay Grade]=2,5000, IF([Pay Grade]=3, 7500,"Invalid pay grade")))

New Perspectives on Microsoft Office Excel 2007

6

7

### Working with Logical Functions

The OR function is a logical function that returns a TRUE value if any of the logical conditions are true and a FALSE value if all the logical conditions are false =IF(OR([Years Service]<1,[Annual Salary]>100000),0, IF([Pay Grade]=1,\$T\$1,IF([Pay Grade]=2,\$T\$2, IF([Pay Grade]=3,\$T\$3,"Invalid pay grade"))))

New Perspectives on Microsoft Office Excel 2007

7

8

### Using Lookup Tables and Functions

A lookup table is a table that organizes data you want to retrieve into different categories The categories for the lookup table, called compare values, are located in the table’s first column or row To retrieve a particular value from the table, a lookup value (the value you are trying to find) needs to match the compare values VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

New Perspectives on Microsoft Office Excel 2007

8

9

### Using Lookup Tables and Functions

New Perspectives on Microsoft Office Excel 2007

9

10

### Using Lookup Tables and Functions

New Perspectives on Microsoft Office Excel 2007

10

11

### Highlighting Duplicate Records with a Custom Format

Select the column you want to search for duplicates In the Styles group on the Home tab, click the Conditional Formatting button, point to Highlight Cells Rules, and then click Duplicate Values Click the values with arrow, then click Custom Format In the Format Cells dialog box, set the formatting you want to use Click the OK button in each dialog box

New Perspectives on Microsoft Office Excel 2007

11

12

### Highlighting Duplicate Records with a Custom Format

New Perspectives on Microsoft Office Excel 2007

12

13

### Using the Conditional Formatting Rules Manager

Each time you create a conditional format, you are defining a conditional formatting rule A rule specifies the type of condition (such as formatting cells greater than a specified value), the type of formatting when that condition occurs

New Perspectives on Microsoft Office Excel 2007

13

14

### Using the IFERROR Function

Error values such as #DIV/0!, #N/A, and #VALUE! indicate that some element in a formula or a cell referenced in a formula is preventing Excel from returning a calculated value The IFERROR function can determine if a cell contains an error value and display the message you choose rather than the default error value =IFERROR(VLOOKUP(L2,HealthPlanRates,2,False)*12,"Invalid code")

New Perspectives on Microsoft Office Excel 2007

14

15

### Using the IFERROR Function

New Perspectives on Microsoft Office Excel 2007

15

16

### Summarizing Data Conditionally

You can calculate the number of cells in a range that match criteria you specify using the COUNTIF function, which is sometimes referred to as a conditional count =COUNTIF(range,criteria) You can add the values in a range that meet criteria you specify using the SUMIF function, which is also called a conditional sum =SUMIF(range,criteria[,sum_range])

New Perspectives on Microsoft Office Excel 2007

16

17

### Summarizing Data Conditionally

You use the AVERAGEIF function to calculate the average of values in a range that meet criteria you specify =AVERAGEIF(range,criteria[,average_range])

New Perspectives on Microsoft Office Excel 2007

17

18

### Summarizing Data Conditionally

The COUNTIFS function counts the number of cells within a range that meet multiple criteria COUNTIFS(criteria_range1,criteria1[,criteria_range2, criteria2...]) The SUMIFS function adds values in a range that meet multiple criteria SUMIFS(sum_range,criteria_range1,criteria1[,criteria_ range2, criteria2...]) The AVERAGEIFS function calculates the average of values within a range of cells that meet multiple conditions AVERAGEIFS(average_range,criteria_range1,criteria1 [,criteria_range2, criteria2...])

New Perspectives on Microsoft Office Excel 2007

18

19

Advanced filtering, similar to filtering, displays a subset of the rows in a table or range of data The criteria range is an area in a worksheet, separate from the range of data or Excel table, used to specify the criteria for the data to be displayed after the filter is applied to the table

New Perspectives on Microsoft Office Excel 2007

19

20

New Perspectives on Microsoft Office Excel 2007

20

21

Click the Data tab on the Ribbon, and then, in the Sort & Filter group, click the Advanced button

New Perspectives on Microsoft Office Excel 2007

21

22

### Using Database Functions to Summarize Data

Functions that perform summary data analysis (SUM, AVERAGE, COUNT, and so on) on a table of values based on criteria that you set are called the Database functions, or Dfunctions DfunctionName(table range, column to summarize, criteria range)

New Perspectives on Microsoft Office Excel 2007

22

23

### Using Database Functions to Summarize Data

New Perspectives on Microsoft Office Excel 2007

23

24

### Using Database Functions to Summarize Data

New Perspectives on Microsoft Office Excel 2007

24

«Excel 2007»
http://900igr.net/prezentatsii/informatika/Excel-2007/Excel-2007.html
cсылка на страницу
Урок

## Информатика

126 тем
Слайды
Презентация: Excel 2007.ppt | Тема: Excel | Урок: Информатика | Вид: Слайды