Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering

Август 25, 2022

Главная
Информатика
Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering

Содержание

2. Objectives Evaluate a single condition using the IF function Evaluate multiple conditions using the AND function
3. Objectives Check for data entry errors using the IFERROR function Summarize data using the COUNTIF, SUMIF,
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
5. Working with Logical Functions New Perspectives on Microsoft Office Excel 2007
6. Working with Logical Functions A nested IF function is when one IF function is placed inside
7. Working with Logical Functions The OR function is a logical function that returns a TRUE value
8. Using Lookup Tables and Functions A lookup table is a table that organizes data you want
9. Using Lookup Tables and Functions New Perspectives on Microsoft Office Excel 2007
10. Using Lookup Tables and Functions New Perspectives on Microsoft Office Excel 2007
11. Highlighting Duplicate Records with a Custom Format Select the column you want to search for duplicates
12. Highlighting Duplicate Records with a Custom Format New Perspectives on Microsoft Office Excel 2007
13. Using the Conditional Formatting Rules Manager Each time you create a conditional format, you are defining
14. Using the IFERROR Function Error values such as #DIV/0!, #N/A, and #VALUE! indicate that some element
15. Using the IFERROR Function New Perspectives on Microsoft Office Excel 2007
16. Summarizing Data Conditionally You can calculate the number of cells in a range that match criteria
17. Summarizing Data Conditionally You use the AVERAGEIF function to calculate the average of values in a
18. Summarizing Data Conditionally The COUNTIFS function counts the number of cells within a range that meet
19. Using Advanced Filtering Advanced filtering, similar to filtering, displays a subset of the rows in a
20. Using Advanced Filtering New Perspectives on Microsoft Office Excel 2007
21. Using Advanced Filtering Click the Data tab on the Ribbon, and then, in the Sort &
22. Using Database Functions to Summarize Data Functions that perform summary data analysis (SUM, AVERAGE, COUNT, and
23. Using Database Functions to Summarize Data New Perspectives on Microsoft Office Excel 2007
25. Скачать презентацию

Слайд 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

Слайд 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

Слайд 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

Слайд 5

Working with Logical Functions
New Perspectives on Microsoft Office Excel 2007

Слайд 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

Слайд 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

Слайд 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

Слайд 9

Using Lookup Tables and Functions
New Perspectives on Microsoft Office Excel 2007

Слайд 10

Using Lookup Tables and Functions
New Perspectives on Microsoft Office Excel 2007

Слайд 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

Слайд 12

Highlighting Duplicate Records with a Custom Format
New Perspectives on Microsoft Office

Excel 2007

Слайд 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

Слайд 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

Слайд 15

Using the IFERROR Function
New Perspectives on Microsoft Office Excel 2007

Слайд 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

Слайд 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

Слайд 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

Слайд 19

Using Advanced Filtering
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

Слайд 20

Using Advanced Filtering
New Perspectives on Microsoft Office Excel 2007

Слайд 21

Using Advanced Filtering
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

Слайд 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

Слайд 23

Using Database Functions to Summarize Data
New Perspectives on Microsoft Office Excel

2007

Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering

Содержание

ObjectivesEvaluate a single condition using the IF functionEvaluate multiple conditions using

ObjectivesCheck for data entry errors using the IFERROR functionSummarize data using

Working with Logical FunctionsIF FunctionIF(logical_test, value_if_true, [value_if_false])AND Function=IF(AND(G2="FT",M2>=1),K2*0.03,0)Structured ReferencesYou can replace

Working with Logical FunctionsNew Perspectives on Microsoft Office Excel 2007

Working with Logical FunctionsA nested IF function is when one IF

Working with Logical FunctionsThe OR function is a logical function that

Using Lookup Tables and FunctionsA lookup table is a table that

Using Lookup Tables and FunctionsNew Perspectives on Microsoft Office Excel 2007

Using Lookup Tables and FunctionsNew Perspectives on Microsoft Office Excel 2007

Highlighting Duplicate Records with a Custom FormatSelect the column you want

Highlighting Duplicate Records with a Custom FormatNew Perspectives on Microsoft Office

Using the Conditional Formatting Rules ManagerEach time you create a conditional

Using the IFERROR FunctionError values such as #DIV/0!, #N/A, and #VALUE!

Using the IFERROR FunctionNew Perspectives on Microsoft Office Excel 2007

Summarizing Data ConditionallyYou can calculate the number of cells in a

Summarizing Data ConditionallyYou use the AVERAGEIF function to calculate the average

Summarizing Data ConditionallyThe COUNTIFS function counts the number of cells within

Using Advanced FilteringAdvanced filtering, similar to filtering, displays a subset of

Using Advanced FilteringNew Perspectives on Microsoft Office Excel 2007

Using Advanced FilteringClick the Data tab on the Ribbon, and then,

Using Database Functions to Summarize DataFunctions that perform summary data analysis

Using Database Functions to Summarize DataNew Perspectives on Microsoft Office Excel

Похожие презентации

Objectives
Evaluate a single condition using the IF function
Evaluate multiple conditions using

Objectives
Check for data entry errors using the IFERROR function
Summarize data using

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

Working with Logical Functions
New Perspectives on Microsoft Office Excel 2007

Working with Logical Functions
A nested IF function is when one IF

Working with Logical Functions
The OR function is a logical function that

Using Lookup Tables and Functions
A lookup table is a table that

Using Lookup Tables and Functions
New Perspectives on Microsoft Office Excel 2007

Using Lookup Tables and Functions
New Perspectives on Microsoft Office Excel 2007

Highlighting Duplicate Records with a Custom Format
Select the column you want

Highlighting Duplicate Records with a Custom Format
New Perspectives on Microsoft Office

Using the Conditional Formatting Rules Manager
Each time you create a conditional

Using the IFERROR Function
Error values such as #DIV/0!, #N/A, and #VALUE!

Using the IFERROR Function
New Perspectives on Microsoft Office Excel 2007

Summarizing Data Conditionally
You can calculate the number of cells in a

Summarizing Data Conditionally
You use the AVERAGEIF function to calculate the average

Summarizing Data Conditionally
The COUNTIFS function counts the number of cells within

Using Advanced Filtering
Advanced filtering, similar to filtering, displays a subset of

Using Advanced Filtering
New Perspectives on Microsoft Office Excel 2007

Using Advanced Filtering
Click the Data tab on the Ribbon, and then,

Using Database Functions to Summarize Data
Functions that perform summary data analysis

Using Database Functions to Summarize Data
New Perspectives on Microsoft Office Excel