Lessons 29–30 · 2 lessons · B. Boltayev, A. Azamatov, A. Asqarov, M. Sodiqov, G. Azamatova. Fundamentals of Informatics and Computer Technology, Grade 8. Second edition. “O‘zbekiston milliy ensiklopediyasi” State Scientific Publishing House, Tashkent, 2015
29
Filtering data
Textbook: pp. 124–126
GoalUses AutoFilter to pick the records that meet a condition, combines conditions and uses the result.
New words
filtering: showing only the records that meet a given condition · filtrlash (saralash)AutoFilter: drop-down arrows on the header cells used to set filter conditions · avtofiltrcondition: a rule such as «greater than 15» or «contains tarix» · shartclear filter: showing all records again · filtrni tozalash
Explanation
Filtering shows only the records of a list that meet a given condition; the other rows are not deleted, only hidden. The Data → Filter command puts drop-down arrows (AutoFilter) on the header cells. Through an arrow you can tick certain values or set a condition: equals, does not equal, greater, less, between, “begins with”, “contains”, blank or not blank. Two conditions joined with AND keep records where both hold; with OR they keep records where at least one holds. If filters are set on several columns, the conditions work together. Formulas in the table are kept, but a plain SUM also adds hidden rows; for the total of the visible rows only, use SUBTOTAL(9; range). Clearing the filter brings all records back, and the result can be copied elsewhere.
Worked examples
Ages 12, 15, 17, 20, 33. Condition: “at least 15” AND “less than 20” – 15 and 17 stay. With OR instead of AND everybody would stay, since every number meets at least one condition.
A library list: Title, Author, Date borrowed, Date returned. In the “Date returned” column, “blank” shows the books not yet returned and “not blank” shows the returned ones. In the “Title” column, “contains tarix” picks the books with the word “tarix” (history) in the title.
Class activity
“Filter hunt”: the teacher states a condition, for example “age over 13, name begins with A”, and groups set the AutoFilter and count how many records remain.
Practice
1
What is the main difference between sorting and filtering?
Sorting changes the order of the records, while filtering hides those that do not meet the condition.
2
Ages 12, 15, 17, 20, 33. How many records does “at least 15 AND less than 20” keep?
2
3
The prices visible in a filtered table are 500, 700, 300 and 1000 so‘m. What does SUBTOTAL(9; ...) give?
2500
4
Why can OR not be used for the condition “temperature at least 20 AND at most 25”?
With OR every number meets at least one condition, so the filter would exclude nothing.