1 / 22

ADVANCED MICROSOFT EXCEL Lesson 11 Filtering, Extracting, and Using Database Functions

ADVANCED MICROSOFT EXCEL Lesson 11 Filtering, Extracting, and Using Database Functions. Objectives. Search a List using AutoFilter. Search for records using the Top 10 feature. Display records using custom filters and search operators. Display records using advanced filters.

wardah
Télécharger la présentation

ADVANCED MICROSOFT EXCEL Lesson 11 Filtering, Extracting, and Using Database Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ADVANCED MICROSOFT EXCELLesson 11Filtering, Extracting, and Using Database Functions

  2. Objectives • Search a List using AutoFilter. • Search for records using the Top 10 feature. • Display records using custom filters and search operators. • Display records using advanced filters. • Use advanced filters to find multiple criteria. • Use DSUM and DAVERAGE.

  3. AutoFilter: Displays a subset of the data in a worksheet that meet certain criteria. Copy to range: Area you create in a worksheet where Excel will copy the records meeting the criteria specified in the Criteria Range. Criteria range: Area you create in a worksheet where you indicate what information Excel is to locate for you within the list. Custom filter: In Excel, a filter in which you indicate specific criteria for which you want to search. Key Terms

  4. DSUM: In Excel, a function that adds the numbers in a column located in a list or database which match the conditions you specify. DAVERAGE: In Excel, a function that averages the numbers in a column located in a list or database which match the conditions you specify. Average refers to taking the sum of a range of numbers and then dividing this total by the number of items in the range. Filter: A method of screening out all database records except those that match your selection criteria. In Excel, you can filter a list so that only the rows that meet specific criteria will be displayed. Rows of data that do not meet the criteria will not be displayed. Key Terms (cont.)

  5. AutoFilter Arrows Searching a List Using AutoFilter In both AutoFilter and Advanced Filter, you will be selecting or entering specific criteria that you wish to locate in the fields. You can quickly display records that meet a specific criterion by using AutoFilters and can also print the displayed records as well. If you are having difficulty understanding the filtering concept, you might want to use the analogy that they are just asking a question (by entering criteria) and Excel is displaying the answer (the records that meet the criteria). AutoFilter arrows AutoFilter drop-down list

  6. Top 10 AutoFilter Searching for Records Using Top 10 The Top 10 feature may sound somewhat misleading. It not only displays the top 10 items in a list, but this feature will display all rows that fall within the upper or lower limits specified either by item or percentage. Top 10 AutoFilter dialog box

  7. Search Operators Search operators are used to expand a search. You need understand these operators before trying the search operator exercises.

  8. Search Operators

  9. Custom AutoFilter Dialog Box Custom filter: let you search for values using the search operators, display rows that contain one value or another, or display rows that meet more that one condition for a column Search Operator list boxes Criteria text boxes

  10. Custom Filter Using AND When you use the AND criteria, only records that meet both of the criteria specified will be displayed.

  11. Custom Filter Using OR Records will be displayed that meet one of the criteria or the other. These records do not have to meet both criteria.

  12. Wildcard Search Operators Wildcards allow more flexibility when searching for criteria. You do not need to know all of the criteria when searching. For example, you would not need to know a person’s complete last name if you were searching for a person within the list.

  13. Wildcard Search Operators Troubleshooting Tip Sometimes wildcards display more results than desired. Therefore, it may be necessary to get more specific with the criteria before entering the wildcard. For example, if you were looking for a person with the last name of Albright, but you were not sure of the spelling, if you entered A*, all records that have a person’s last name that begins with A would be displayed. You could narrow the search by entering Al* as the criteria.

  14. Advanced Filters When copying to another location using Advanced Filters, you will need to select the Copy To option each time you filter because this option is not the default. When using more than one row of criteria in the Advanced Filter criteria range remember to include all of the criteria range row numbers in the Advanced Filter dialog box. List Criteria range Copy to range

  15. Advanced Filters (cont.) By increasing the size of the criteria range, criteria can be entered on more than one row of the criteria range to find data that meets one criteria or another. AND criteria would be placed on the same row in the criteria range.

  16. Using Advanced Filters to Find Multiple Criteria

  17. Using DSUM and DAVERAGE • The DSUM function allows for a quick sum. • DSUM and DAVERAGE are used to calculate sums and averages on data within a data list or database.

  18. Using DAVERAGE • The DAVERAGE function averages the values within a database.

  19. Time Saver • Select the cell containing the desired column label and AutoFilter • ALT + Down Arrow • Then use to up and down arrow keys or press enter to select an item

  20. Summary • AutoFilters can find records within a list that meet specific criteria. • Excel’s search operators let you quickly select or create your search criteria. • Custom filters allow you the flexibility to search for values using search operators, AND, and OR.

  21. Summary (cont.) • Wildcards are characters that may be used when you’re not sure about the complete value for which you’re searching. • Advanced filters are used to find records that meet more complex criteria. • The DSUM and DAVERAGE functions sum and average values in your database.

  22. Homework - Excel Lesson 11 • Page 55 • Review Questions • Project 11-1 • Project 11-2

More Related