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

Категория: Информатика
Нажмите для полного просмотра!
Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №1Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №2Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №3Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №4Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №5Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №6Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №7Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №8Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №9Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №10Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №11Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №12Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №13Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №14Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №15Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №16Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №17Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №18Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №19Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №20Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №21Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №22Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №23Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering, слайд №24
Скачать
Вы можете ознакомиться и скачать Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering. Презентация содержит 24 слайдов. Презентации для любого класса можно скачать бесплатно. Если материал и наш сайт презентаций Вам понравились – поделитесь им с друзьями с помощью социальных кнопок и добавьте в закладки в своем браузере.

Слайды и текст этой презентации

Слайд 1


Excel Tutorial 7 Using Advanced Functions, Conditional Formatting, and Filtering
Описание слайда:
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 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
Описание слайда:
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

Слайд 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
Описание слайда:
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

Слайд 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])
Описание слайда:
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])

Слайд 5


Working with Logical Functions
Описание слайда:
Working with Logical Functions

Слайд 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")))
Описание слайда:
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")))

Слайд 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"))))
Описание слайда:
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"))))

Слайд 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])
Описание слайда:
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])

Слайд 9


Using Lookup Tables and Functions
Описание слайда:
Using Lookup Tables and Functions

Слайд 10


Using Lookup Tables and Functions
Описание слайда:
Using Lookup Tables and Functions

Слайд 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
Описание слайда:
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

Слайд 12


Highlighting Duplicate Records with a Custom Format
Описание слайда:
Highlighting Duplicate Records with a Custom Format

Слайд 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
Описание слайда:
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

Слайд 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")
Описание слайда:
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")

Слайд 15


Using the IFERROR Function
Описание слайда:
Using the IFERROR Function

Слайд 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])
Описание слайда:
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])

Слайд 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])
Описание слайда:
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])

Слайд 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...])
Описание слайда:
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...])

Слайд 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
Описание слайда:
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

Слайд 20


Using Advanced Filtering
Описание слайда:
Using Advanced Filtering

Слайд 21


Using Advanced Filtering Click the Data tab on the Ribbon, and then, in the Sort & Filter group, click the Advanced button
Описание слайда:
Using Advanced Filtering Click the Data tab on the Ribbon, and then, in the Sort & Filter group, click the Advanced button

Слайд 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)
Описание слайда:
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)

Слайд 23


Using Database Functions to Summarize Data
Описание слайда:
Using Database Functions to Summarize Data

Слайд 24


Using Database Functions to Summarize Data
Описание слайда:
Using Database Functions to Summarize Data

Скачать

Похожие презентации
Mypresentation.ru
Загрузить презентацию