Сортировка и фильтрация данных. Сводные таблицы.
Лабораторная работа№1
Работа с электронной таблицей как с базой данных.
Сортировка и фильтрация данных. Сводные таблицы.
Требования к оформлению работы: 1
МЕТОДИЧЕСКИЕ УКАЗАНИЯ.. 11
ПО ВЫПОЛНЕНИЮ Л/Р №1. 11
ПРИМЕРЫ ВЫПОЛНЕНИЯ ЗАДАНИЙ 2-7. 13
Контрольные вопросы
1. Дать определение базы данных.
2. При каких условиях MS Excel будет считать таблицу списком?
3. Что такое диапазон данных?
4. Что такое диапазон критериев?
5. Что такое диапазон для извлечения?
6. Способы ввода данных в базу данных.
7. Дать определение понятию сортировка.
8. Виды сортировок.
9. Что такое фильтрация списков?
10. Типы фильтров.
11. Что такое Автофильтр?
12. Что такое пользовательский фильтр?
13. Что такое расширенный фильтр? Его возможности.
14. Типы критериев отбора.
15. Методика применения расширенного фильтра.
16. Что такое сводная таблица?
17. Для чего используются сводные таблицы?
18. Этапы создания сводных таблиц.
Требования к оформлению работы:
1. Лабораторная работа выполняется в программе MS Excel.
2. Каждое задание выполняется на отдельном листе книге MS Excel.
3. Ярлык листа переименовать в соответствии с номером задания (Лист 1 –исходная таблица, лист 2 переименовать – задание 1, лист 3 – задание 2 и т.д.).
4. Вариант задания соответствует порядковому номеру списка студентов в журнале преподавателя.
База данных (БД) создана в среде табличного процессора MS Excel.
Исходная таблица:
| № П/п | Фамилия | Имя | Отчество | Пол | Дата рождения | Должность | Оклад | Семейное положение | Кол-во детей |
| Агафонов | Юрий | Александрович | м | 05.02.1964 | механик | 1 800р. | женат | ||
| Агеев | Сергей | Петрович | м | 25.05.1970 | товаровед | 2 500р. | женат | ||
| Андреев | Юрий | Дмитриевич | м | 12.08.1942 | бухгалтер | 2 100р. | вдовец | ||
| Андреев | Александр | Алексеевич | м | 22.09.1955 | продавец 2кат. | 2 450р. | женат | ||
| Андреев | Георгий | Алексеевич | м | 03.08.1944 | бухгалтер | 2 100р. | разведен | ||
| Андреев | Александр | Сергеевич | м | 19.02.1951 | сторож | 1 300р. | холост | ||
| Аникина | Ирина | Григорьевна | ж | 20.09.1973 | продавец 2кат. | 2 200р. | незамужем | ||
| Анисимов | Григорий | Сергеевич | м | 07.07.1964 | грузчик | 1 200р. | женат | ||
| Аннушкин | Сергей | Петрович | м | 02.11.1949 | зам.директора | 3 500р. | вдовец | ||
| Антоненко | Иван | Анатольевич | м | 26.12.1976 | продавец 2кат. | 2 060р. | женат | ||
| Атоманюк | Алексей | Федорович | м | 13.01.1977 | зав.секцией | 2 800р. | женат | ||
| Багирова | Елена | Константиновна | ж | 09.12.1974 | продавец 1кат. | 2 400р. | замужем | ||
| Базина | Людмила | Петровна | ж | 07.04.1954 | экономист | 2 900р. | замужем | ||
| Баранова | Ирина | Петровна | ж | 03.05.1977 | бухгалтер | 1 960р. | вдова | ||
| Березкина | Алла | Сергеевна | ж | 15.02.1971 | товаровед | 2 500р. | замужем | ||
| Бирюкова | Александра | Алексеевна | ж | 18.09.1980 | продавец 2кат. | 2 060р. | замужем | ||
| Богданова | Ирина | Игоревна | ж | 06.09.1975 | продавец 3кат. | 1 740р. | вдова | ||
| Бондарчук | Сара | Георгиевна | ж | 08.12.1968 | зав.секцией | 2 800р. | замужем | ||
| Борисов | Александр | Алексеевич | м | 16.05.1975 | экспедитор | 2 200р. | женат | ||
| Борисова | Татьяна | Александровна | ж | 21.08.1948 | зав.секцией | 2 600р. | вдова | ||
| Бурова | Татьяна | Александровна | ж | 11.05.1961 | кассир-контролер | 2 300р. | замужем | ||
| Васильева | Татьяна | Борисовна | ж | 24.07.1959 | юрист | 2 900р. | вдова | ||
| Гарин | Александр | Петрович | м | 22.01.1965 | продавец 1кат. | 2 400р. | вдовец | ||
| Гранина | Нина | Алексеевна | ж | 21.10.1978 | администратор | 2 600р. | замужем | ||
| Грачева | Алла | Алексеевна | ж | 09.09.1971 | гл.бухгалтер | 3 000р. | разведена | ||
| Григорьев | Александр | Сергеевич | м | 26.04.1960 | коммерч.агент | 3 200р. | холост | ||
| Данилова | Татьяна | Александровна | ж | 02.11.1959 | продавец 2кат. | 2 060р. | замужем | ||
| Дмитриева | Ирина | Игоревна | ж | 01.11.1965 | бухгалтер | 1 960р. | замужем | ||
| Дорофеева | Алла | Сергеевна | ж | 03.03.1978 | кассир-контролер | 2 400р. | незамужем | ||
| Дрозд | Татьяна | Александровна | ж | 30.07.1968 | зам.зав.секцией | 2 600р. | вдова | ||
| Егорова | Нина | Алексеевна | ж | 15.11.1968 | уборщица | 1 500р. | замужем | ||
| Ефимова | Алла | Сергеевна | ж | 21.11.1960 | продавец 2кат. | 2 100р. | замужем | ||
| Жук | Вера | Алексеевна | ж | 02.01.1959 | продавец 1кат. | 2 400р. | незамужем | ||
| Зверева | Бэлла | Гавриловна | ж | 12.04.1949 | продавец 3кат. | 1 840р. | замужем | ||
| Иванова | Нина | Алексеевна | ж | 10.03.1976 | продавец 2кат. | 2 100р. | незамужем | ||
| Ильина | Людмила | Николаевна | ж | 04.08.1975 | уборщица | 1 500р. | разведена | ||
| Каро | Наталья | Константиновна | ж | 11.07.1964 | продавец 3кат. | 1 750р. | незамужем | ||
| Ким | Ирина | Олеговна | ж | 12.06.1969 | продавец 3кат. | 1 800р. | незамужем | ||
| Киреева | Нина | Борисовна | ж | 03.03.1970 | фасовщица | 1 600р. | замужем | ||
| Китуничева | Вера | Дмитриевна | ж | 07.07.1964 | ст.кассир | 2 500р. | замужем | ||
| Коваленко | Анна | Давыдовна | ж | 17.07.1973 | продавец 3кат. | 1 800р. | разведена | ||
| Козлова | Елена | Николаевна | ж | 26.02.1963 | продавец 3кат. | 1 900р. | замужем | ||
| Константинов | Александр | Олегович | м | 11.02.1955 | бухгалтер | 2 000р. | вдовец | ||
| Лазаренко | Алла | Сергеевна | ж | 01.06.1965 | продавец 3кат. | 1 900р. | замужем | ||
| Леонова | Нина | Алексеевна | ж | 05.10.1975 | кассир | 2 200р. | замужем | ||
| Мартынихина | Анна | Дмитриевна | ж | 01.10.1965 | приемщик посуды | 1 600р. | замужем | ||
| Милашевич | Алла | Александровна | ж | 12.09.1975 | продавец 2кат. | 2 060р. | замужем | ||
| Минина | Лиана | Зарубовна | ж | 02.11.1975 | кассир | 2 250р. | замужем | ||
| Минц | Александр | Сергеевич | м | 07.02.1956 | охранник | 2 160р. | разведен | ||
| Михайлов | Алексей | Петрович | м | 05.08.1971 | приемщик посуды | 1 600р. | женат | ||
| Москвина | Алла | Сергеевна | ж | 01.08.1977 | зам.зав.секцией | 2 400р. | замужем | ||
| Мухина | Наталья | Олеговна | ж | 29.06.1962 | кассир | 2 300р. | замужем | ||
| Наливайко | Ирина | Олеговна | ж | 02.07.1975 | повар | 2 300р. | замужем | ||
| Нарышкина | Елена | Емельяновна | ж | 25.06.1975 | фасовщица | 1 900р. | замужем | ||
| Никодимов | Петр | Александрович | м | 31.01.1961 | продавец 3кат. | 1 900р. | разведен | ||
| Николаев | Петр | Алексеевич | м | 05.12.1939 | кассир | 2 300р. | вдовец | ||
| Николаева | Наталья | Константиновна | ж | 23.07.1961 | повар | 2 000р. | замужем | ||
| Озерова | Нина | Алексеевна | ж | 05.06.1964 | продавец 3кат. | 1 900р. | замужем | ||
| Павлова | Вера | Олеговна | ж | 17.10.1959 | продавец 3кат. | 1 800р. | незамужем | ||
| Петрова | Анна | Юрьевна | ж | 31.08.1963 | продавец 3кат. | 1 800р. | замужем | ||
| Петрова | Анна | Дмитриевна | ж | 07.04.1973 | бухгалтер | 1 900р. | незамужем | ||
| Петрушевич | Ирина | Олеговна | ж | 22.01.1980 | продавец 3кат. | 1 800р. | незамужем | ||
| Пушкина | Нина | Алексеевна | ж | 31.01.1958 | кассир | 2 300р. | незамужем | ||
| Рудакова | Анна | Дмитриевна | ж | 20.04.1972 | продавец 1кат. | 2 300р. | незамужем | ||
| Салтыкова | Нина | Алексеевна | ж | 15.03.1976 | кассир | 2 300р. | незамужем | ||
| Селедкина | Ирина | Олеговна | ж | 14.02.1969 | директор | 4 000р. | разведена | ||
| Смирнова | Елена | Емельяновна | ж | 24.01.1979 | кассир-контролер | 2 400р. | разведена | ||
| Соловьева | Алла | Сергеевна | ж | 10.06.1969 | продавец 3кат. | 1 960р. | разведена | ||
| Сурикова | Нина | Игоревна | ж | 27.12.1978 | зам.зав.секцией | 2 500р. | разведена | ||
| Тарасова | Жанна | Осиповна | ж | 13.07.1972 | продавец 3кат. | 1 900р. | разведена | ||
| Трофимова | Елена | Николаевна | ж | 10.09.1979 | продавец 1кат. | 2 400р. | разведена | ||
| Трушко | Нина | Олеговна | ж | 03.04.1972 | корзинщица | 1 540р. | замужем | ||
| Уханова | Эльвира | Ивановна | ж | 23.12.1974 | кассир | 2 300р. | разведена | ||
| Федоренко | Нина | Викторовна | ж | 01.05.1977 | зам.зав.секцией | 2 500р. | разведена | ||
| Федоров | Виктор | Александрович | м | 13.09.1970 | охранник | 2 100р. | холост | ||
| Чаадаев | Борис | Викторович | м | 19.05.1963 | коммерч.директор | 3 600р. | холост | ||
| Чупятов | Петр | Алексеевич | м | 26.03.1976 | грузчик | 1 200р. | разведен | ||
| Шакерзанов | Алексей | Дмитриевич | м | 17.04.1972 | сторож | 1 200р. | женат | ||
| Янковская | Жанна | Викторовна | ж | 21.12.1970 | зав.секцией | 2 700р. | разведена |
Учебная БД содержит поля (столбцы) исходных данных, поясняемые в таблице 1.
Таблица 1
Наименования полей базы данных о работниках универсама
| Столбец | Наименование поля | Пояснения |
| A | Фамилия | Фамилия работника |
| B | Имя | Имя работника |
| C | Отчество | Отчество работника |
| D | Пол | Пол работника |
| E | Дата рождения | Дата рождения работника |
| F | Должность | Должность, занимаемая работником |
| G | Оклад | Оклад работника в рублях |
| H | Семейное положение | Семейное положение работника |
| I | Количество детей | Количество детей-иждивенцев в семье работника |
Примечание.
Пол кодируется буквами м или ж.
Семейное положение может быть представлено текстовыми значениями: холост, женат, разведен, вдовец, замужем, незамужем, разведена, вдова.
Должности: директор, бухгалтер, зав.секцией, зам.зав.секцией, продавец 1кат., продавец 2кат., продавец 3кат., уборщица, грузчик и прочее.
ЗАДАНИЕ 1. Описать структуру существующей БД отдела кадров с данными о работниках универсама, созданную в среде табличного процессора MS Excel. При этом указать число полей, записей в БД и тип (формат) данных для каждого из полей (текстовый, числовой, денежный/финансовый или дата), заполнив копию таблицы 4.
Задание общее для всех вариантов.
Таблица 4
Описание структуры базы данных о работниках универсама
Диапазон ячеек, занимаемых БД - A1 : I80.
Число полей в БД - _____.
Число записей в БД - _____.
| Столбец | Наименование поля | Тип данных |
| A | Фамилия | |
| B | Имя | |
| C | Отчество | |
| D | Пол | |
| E | Дата рождения | |
| F | Должность | |
| G | Оклад | |
| H | Семейное положение | |
| I | Количество детей |
ЗАДАНИЕ2. Провести двухуровневую сортировку БД согласно критериям в таблице 5. При этом на первом этапе провести сортировку согласно первичному критерию, а на втором этапе для записей, имеющих одинаковые значения первичного критерия, предусмотреть сортировку согласно вторичному критерию.
Подробно по шагам описать действия пользователя с представлением в виде рисунка диалогового окна Сортировка диапазона (см. ниже пример выполнения задания 4).
Таблица 5
Варианты индивидуальных заданий
| Вариант | Критерии сортировки | |
| Первичный | Вторичный | |
| Вначале мужчины, а затем женщины | По убыванию возраста работника | |
| По алфавиту наименований должностей | По возрастанию возраста работника | |
| Вначале мужчины, а затем женщины | По алфавиту фамилий | |
| По алфавиту наименований должностей | По убыванию окладов | |
| Вначале мужчины, а затем женщины | По алфавиту наименований должностей | |
| Вначале женщины, а затем мужчины | По убыванию количества детей | |
| По алфавиту наименований должностей | Вначале женщины, а затем мужчины | |
| Вначале женщины, а затем мужчины | По возрастанию окладов | |
| Вначале мужчины, а затем женщины | По возрастанию количества детей | |
| Вначале женщины, а затем мужчины | По убыванию окладов | |
| Вначале женщины, а затем мужчины | По возрастанию возраста работника | |
| По алфавиту наименований должностей | По убыванию количества детей | |
| По убыванию количества детей | Вначале женщины, а затем мужчины | |
| По возрастанию количества детей | По алфавиту фамилий | |
| По убыванию окладов | По алфавиту фамилий | |
| По алфавиту наименований должностей | По алфавиту фамилий | |
| По убыванию возраста работника | Вначале мужчины, а затем женщины | |
| По убыванию окладов | По алфавиту наименований должностей | |
| По возрастанию количества детей | Вначале мужчины, а затем женщины | |
| По возрастанию количества детей | По возрастанию семенное положение | |
| По возрастанию семейное положение | По убыванию количества детей | |
| По возрастанию семейное положение | По убыванию окладов | |
| Вначале женщины, а затем мужчины | По возрастанию семейное положение | |
| Вначале мужчины, а затем женщины | По возрастанию семейное положение | |
| По возрастанию количества детей | По убыванию семейное положение | |
| Вначале мужчины, а затем женщины | По убыванию окладов |
ЗАДАНИЕ 3. Используя операцию автофильтра, провести выборку записей из БД согласно приведенным в таблице 6 критериям фильтрации. Подробно по шагам описать необходимые действия пользователя. При этом представить в виде рисунка диалоговое окно пользовательского автофильтра (см. ниже пример выполнения задания 5).
Таблица 6
Варианты индивидуальных заданий
| Вариант | Критерии фильтрации |
| Фамилии, начинающиеся на «Ми» или «Ни» | |
| Фамилии, начинающиеся с «Б», и 3-й буквой «р» | |
| Не имеющие детей или имеющие более четырех детей | |
| Продавцы всех категорий | |
| Имеющие имя «Александр» или «Алексей» | |
| Вдовцы или вдовы | |
| Имеющие отчества «Александрович» или «Александровна» | |
| Имеющие оклады от 2500 до 3000 руб. | |
| Заведующие или их заместители любых подразделений | |
| Холостые мужчины или незамужние женщины | |
| Фамилии, начинающиеся на «П», и 4-й буквой «р» | |
| Имеющие оклады от 1200 до 2500 руб. | |
| Фамилии, заканчивающиеся на «ова» или «ов» | |
| Имеющие имя «Татьяна» или «Нина» | |
| Имеющие отчества «Дмитриевич» или «Дмитриевна» | |
| Фамилии, начинающиеся на «А», и 3-й буквой «д» | |
| Имеющие отчества «Сергеевич» или «Сергеевна» | |
| Имеющие оклады от 1800 до 2300 руб. | |
| Фамилии, заканчивающиеся на «ева» или «ев» | |
| Имеющие имя «Анна» или «Алла» | |
| Имеющие имя «Алексей» или «Сергей» | |
| Имеющие оклады от 1840 до 2400 руб | |
| Фамилии, начинающиеся на «На» или «Ни» | |
| Разведенные мужчины или женщины | |
| Директоры или коммерческие директоры | |
| Кассир или кассир-контролер |
ЗАДАНИЕ 4. Используя многошаговую операцию автофильтра, провести выборку записей из БД согласно приведенным в таблице 7 критериям фильтрации. Подробно по шагам описать необходимые действия пользователя. При этом представить в виде рисунков диалоговые окна применяемых пользовательских автофильтров (см. ниже примеры выполнения заданий 5 и 6).
Таблица 7
Варианты индивидуальных заданий
| Вариант | Критерии фильтрации |
| Мужчины с окладом выше 2000 руб. | |
| Продавцы любых категорий с окладом ниже 2000 руб. | |
| Женщины кассиры или кассиры-контролеры | |
| Вдовы или разведенные женщины, имеющие детей | |
| Незамужние или разведенные, не имеющие детей | |
| Разведенные, имеющие детей | |
| Вдовы и вдовцы с окладом ниже 2500 руб. | |
| Незамужние продавцы 1-й и 2-й категорий | |
| Продавцы любых категорий с именами Елена или Вера | |
| Мужчины-бухгалтеры | |
| Замужние, имеющие детей | |
| Женщины с окладом выше 2000 руб. | |
| Незамужние, имеющие детей | |
| Незамужние или разведенные, имеющие детей | |
| Заведующие или зам. зав. секцией, имеющие детей | |
| Мужчины или женщины зав. секцией | |
| Мужчины с окладом ниже 3000 руб. | |
| Замужние продавцы 1-й и 2-й категорий | |
| Вдовы и вдовцы с окладом выше 1960 руб. | |
| Незамужние кассиры | |
| Женщины-бухгалтеры | |
| Вдовы или разведенные женщины, не имеющие детей | |
| Бухгалтеры с именами Ирина или Анна | |
| Бухгалтеры с окладом выше 1900 руб. | |
| Кассиры с окладом не ниже 2200 руб. | |
| Заведующие или зам. зав.секцией, не имеющие детей |
ЗАДАНИЕ 5. Используя операцию расширенного фильтра, выполнить одношаговую фильтрацию согласно критериям задания 4. Соответствующий блок критериев расположить над таблицей БД. Подробно по шагам описать необходимые действия пользователя. При этом представить в виде рисунков используемую часть такого блока и диалоговое окно расширенного фильтра (см. ниже пример выполнения задания 7).
ЗАДАНИЕ 6. Реализовать запрос к БД, используя функции категории Работа с базой данных. Подробно по шагам описать необходимые действия пользователя. При этом представить в виде рисунка используемую часть блока критериев и привести соответствующую расчетную формулу (см. ниже пример выполнения задания 8). Варианты запросов приведены в таблице 8.
Таблица 8
Варианты индивидуальных заданий
| Вариант | Запрос к базе данных |
| Сумма окладов продавцов любых категорий | |
| Количество вдов и вдовцов | |
| Максимальный оклад у мужчин | |
| Минимальный оклад у женщин | |
| Количество женщин-продавцов 1-й категории | |
| Средний оклад у заведующих любых подразделений | |
| Общее количество детей у разведенных | |
| Средний оклад у бухгалтеров | |
| Количество холостяков с окладом выше 2500 руб. | |
| Максимальное количество детей у вдовцов и вдов | |
| Количество разведенных мужчин и женщин | |
| Средний оклад у мужчин | |
| Средний оклад у женщин | |
| Количество мужчин-продавцов 2-й категории | |
| Количество женщин-продавцов 3-й категории | |
| Общее количество детей у вдовцов и вдов | |
| Максимальное количество детей у разведенных | |
| Максимальный оклад у бухгалтеров | |
| Количество вдов и вдовцов с окладом выше 2000 руб. | |
| Общее количество детей у вдовцов и вдов | |
| Количество холостяков и незамужних | |
| Сумма окладов у заведующих любых подразделений | |
| Количество кассиров с окладом не ниже 2250 руб. | |
| Количество незамужних кассиров | |
| Количество охранников | |
| Средний оклад у заведующих любых подразделений |
ЗАДАНИЕ 7. Реализовать перекрестный запрос к БД, используя операцию построения сводной таблицы. Подробно по шагам описать необходимые действия пользователя. При этом представить в виде рисунка диалоговое окно шага 3 из 4 Мастера сводных таблиц (см. ниже пример выполнения задания 9). Варианты запросов приведены в таблице 9.
Таблица 9
Варианты индивидуальных заданий
| Вариант | Запрос к БД |
| 1, 10, 19 | Количество работников в каждой должности отдельно для женщин и мужчин |
| 2, 11, 20 | Количество детей для различных групп семейного положения отдельно для женщин и мужчин |
| 3, 12, 21 | Средний оклад работников в каждой должности отдельно для женщин и мужчин |
| 4, 13, 22 | Максимальное количество детей для различных групп семейного положения отдельно для женщин и мужчин |
| 5, 14, 23 | Максимальный оклад в каждой должности отдельно женщин и мужчин |
| 6, 15, 24 | Минимальный оклад в каждой должности отдельно женщин и мужчин |
| 7, 16, 25 | Минимальное количество детей для различных групп семейного положения отдельно для женщин и мужчин |
| 8, 17, 26 | Сумма окладов работников в каждой должности отдельно для женщин и мужчин |
| 9, 18 | Количество работников в каждой должности отдельно для женщин и мужчин |
МЕТОДИЧЕСКИЕ УКАЗАНИЯ
ПО ВЫПОЛНЕНИЮ Л/Р №1
1. Выполнение заданий 2-5 сводится к составлению сценариев запросов к БД. Такие сценарии должны содержать подробное описание действий пользователя по выделению соответствующих диапазонов ячеек, выбору пунктов инструментального меню, заполнению полей диалоговых окон и прочее (см. примеры выполнения заданий).
2. Задания 5-7 предполагают реализацию запросов к БД, связанных с поиском и обработкой данных, которые соответствуют заданным условиям-критериям поиска.
Такие запросы в среде табличного процессора MS Excel могут выполняться различными способами:
· с использованием Формы, создаваемой при выборе пунктов инструментального меню Данные/Форма..., в которой следует щелкнуть мышью по кнопке Критерии.
· Далее надо ввести в соответствующее поле формы искомое значение, а для текстовых значений - хотя бы начальный уникальный фрагмент. Результатом поиска является отображаемая в форме первая от начала БД запись, в которой обнаружено совпадение с введенным искомым значением. Щелчками по кнопкам Следующая или Предыдущая, можно перейти к очередной или предыдущей такой записи;
· посредством использования операции Автофильтра (см. ниже примеры выполнения заданий 5 и 6);
· посредством выполнения операции Расширенного фильтра, который использует формируемый предварительно блок критериев поиска (см. ниже пример выполнения задания 7);
· с использованием функций категории Работа с базой данных, которые применяют механизм расширенной фильтрации с последующей обработкой ее результатов (см. ниже пример выполнения задания 8);
· путем построения сводной таблицы посредством диалога с Мастером сводных таблиц (см. ниже пример выполнения задания 9);
· с использованием пунктов инструментального меню Правка/Найти..., что применительно к таблице БД можно считать наименее эффективным.
3. В заданиях 5-6 требуется сформировать блок критериев, заполнив его заданными условиями выборки искомых записей. С этой целью предварительно необходимо зарезервировать диапазон ячеек для размещения блока критериев посредством вставки пустых строк над таблицей БД. Затем следует скопировать строку с наименованиями полей БД в первую строку блока критериев, например, с использованием папки обмена. Далее следует ввести, начиная со второй строки блока критериев, конкретные условия выборки записей. При этом следует иметь в виду, что комбинированный критерий фильтрации формируется из частных критериев в отдельных ячейках блока по правилу: объединение в строке – логической операцией И, в столбце – логической операцией ИЛИ. Полученные таким образом блоки критериев следует представить в контрольной работе в виде соответствующих рисунков.
4. Задание 6 предполагает использование функций категории Работа с базой данных, которые применяют механизм расширенной фильтрации с последующей обработкой ее результатов:
БДСУМ - суммирование значений в указанном столбце;
БСЧЁТ - подсчет числа значений в указанном столбце, который должен содержать не текстовые значения;
ДМАКС - нахождение максимального значения в указанном столбце;
ДМИН - нахождение минимального значения в указанном столбце;
ДСРЗНАЧ - вычисление среднеарифметического значения в указанном столбце.
Все вышеперечисленные функции имеют три аргумента:
· диапазон ячеек, занимаемых исходной БД;
· ячейка с именем поля-столбца, по которому после фильтрации производится суммирование, подсчет числа значений, поиск максимума и прочее;
· диапазон ячеек блока критериев фильтрации.
Следует иметь в виду, что при использовании функции БСЧЁТ в качестве имени поля, по которому производится подсчет числа записей, прошедших фильтрацию, следует указать поле не текстового типа, например, арифметического (см. ниже пример выполнения задания 8).
5. Перекрестный запрос к БД из задания 7 реализуется посредством диалога с Мастером сводных таблиц, состоящего из четырех шагов:
шаг 1 - подтверждение создания таблицы на основе данных, находящихся в списке или базе данных Microsoft Excel;
шаг 2 - выделения диапазона ячеек, занимаемых БД;
шаг 3 - разметка сводной таблицы посредством перетаскивания имен полей в соответствующие области создаваемой таблицы;
шаг 4 - выбор варианта расположения сводной таблицы (см. ниже пример выполнения задания 9).