Внимание! Работу производите только в своей рабочей книге!!!
ЗАДАНИЕ 1. Сортировка базы данных
Под сортировкой понимается упорядочивание записей БД по одному или нескольким полям. Сортировка осуществляется «на месте» - непосредственно в таблице. Строки таблицы в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем порядке (от 1 до 9, от А до Я) или в убывающем обратном порядке.
В Excel одновременную сортировку можно провести по 1 – 3 полям. При этом поля, по которым производится сортировка, выбираются пользователем в окне диалога. Если сортировка производится только по одному полю, то проще воспользоваться кнопками сортировки, которые находятся на кнопочной панели Стандартная (при этом курсор должен быть установлен в поле, по которому производится сортировка).
Проведем двухуровневую сортировку БД Кадры, используя следующие критерии: первичный - по убыванию количества детей; вторичный - по алфавиту групп семейного положения.
1. Установите курсор в таблицу базы данных.
2. Выберите команду меню Данные/Сортировка...
3. Заполните диалоговое окна Сортировка диапазонасогласно рис. 15.
Рис. 15
4. В результате сортировки должно получиться следующее (см. рис.16).
Рис. 16
ТЕСТОВОЕ ЗАДАНИЕ 1
Номер варианта совпадает с номером компьютера!
Провести двухуровневую сортировку БД согласно критериям, приведенным в таблице 7. Скопировать отсортированную базу данных на чистый рабочий лист, который назвать Сортировка
Таблица 7
| № варианта | Критерии сортировки | |
| Первичный | Вторичный | |
| Вначале мужчины, а затем женщины | По убыванию возраста работника | |
| По должностям в алфавитном порядке | По возрастанию возраста работника | |
| Вначале мужчины, а затем женщины | По фамилиям в алфавитном порядке | |
| По должностям в алфавитном порядке | По убыванию размера оклада | |
| Вначале мужчины, а затем женщины | По должностям в алфавитном порядке | |
| Вначале женщины, а затем мужчины | По убыванию количества детей | |
| По должностям в алфавитном порядке | Вначале женщины, а затем мужчины | |
| Вначале женщины, а затем мужчины | По возрастанию размера оклада | |
| Вначале мужчины, а затем женщины | По возрастанию количества детей | |
| По фамилиям в алфавитном порядке | По именам в алфавитном порядке | |
| По фамилиям в алфавитном порядке | По убыванию возраста работников | |
| По возрастанию размера оклада | По убыванию количества детей | |
| По фамилиям в алфавитном порядке | По возрастанию размера оклада |
ЗАДАНИЕ 2. Фильтрация базы данных по одному полю
Фильтрация позволяет находить и отбирать для обработки некоторое подмножество записей в БД. В отфильтрованном списке выводятся на экран только те строки, которые содержат определенное значение или отвечают определенным критериям, при этом остальные строки оказываются скрытыми. Отфильтрованные данные можно копировать на другие участки текущего рабочего листа или на любой другой рабочий лист.
В программе Excel можно использовать для фильтрации данных команду Автофильтрили команду Расширенный фильтр. В большинстве случаев достаточно команды Автофильтр, однако если нужно использовать сложные критерии для выборки данных, следует воспользоваться командой Расширенный фильтр.
Важно!
При выполнении запросов к БД можно использовать маски ввода:
* - любое количество любых символов
? - один произвольный символ
Используя Автофильтр, провести выборку записей из БД согласно критерию - фамилии, состоящие из трех или четырех букв.
1. Установите курсор в таблицу листа Кадры.
2. Выполните команду меню Данные/Фильтр/Автофильтр. В результате строка имен полей БД превратится в поля с раскрывающимися списками (рис. 17).
Рис. 17
3. Выберите в раскрывающемся списке поля Фамилиявариант Условие...
4. Заполните диалоговое окноПользовательский автофильтр(см. рис. 18)
Рис. 18
5. Скопируйте полученные результаты на лист Задание 2.
6. С целью подготовки к выполнению следующего задания отмените результаты фильтрации посредством выбора в раскрывающемся списке поля Фамилия позиции (Все).Можно также выполнить команду Данные/Фильтр/Отобразить все
ТЕСТОВОЕ ЗАДАНИЕ 2
Номер варианта совпадает с номером компьютера!
Используя Автофильтр, провести выборку записей из БД согласно приведенным в таблице 8 критериям фильтрации. Скопировать отфильтрованную базу данных на чистый рабочий лист под именем Фильтр 1.
Таблица 8
| № варианта | Критерии фильтрации |
| Фамилии, начинающиеся с “Б”, и 3-й буквой “р” | |
| Лица, не имеющие детей или имеющие более четырех детей | |
| Продавцы всех категорий | |
| Имеющие имя “Александр” или “Алексей” | |
| Вдовцы или вдовы | |
| Имеющие отчества “Александрович” или “Александровна” | |
| Имеющие оклады от 2500 до 3000 руб. | |
| Заведующие или их заместители любых подразделений | |
| Холостые мужчины или незамужние женщины | |
| Фамилии, начинающиеся на “Ми” или “Ни” | |
| Имеющие оклады менее 2000 руб. или более 3000 руб. | |
| Лица, имеющие 2-х или 3-х детей | |
| Фамилии, начинающиеся на "А" или "С" |
ЗАДАНИЕ 3. Фильтрация базы данных по нескольким полям
Используя многошаговую фильтрацию, провести выборку записей из БД согласно критерию "женщины, имеющие трех и более детей".
1. Перейдите на лист Кадры.
2. Выберите в раскрывающемся списке поля Количество детейвариант (Условие...)
3. Заполните диалогового окнаПользовательский автофильтрсогласно рис. 19.
Рис. 19
4. Выберите в раскрывающемся списке поля Полвариант ж. В результате в базе данных будут отображаться только записи о женщинах, имеющих трех и более детей.
5. Скопируйте полученные результаты на лист Задание 3.
6. Отмените результаты фильтрации БД посредством выбора в меню команды Данные / Фильтр / Отобразить все.
ТЕСТОВОЕ ЗАДАНИЕ 3
Номер варианта совпадает с номером компьютера!
Используя многоуровневую фильтрацию, провести выборку записей из БД согласно приведенным в таблице 9 критериям фильтрации. Результаты скопируйте на чистый рабочий лист, который назовите Фильтр 2.
Таблица 9
| № варианта | Критерии фильтрации |
| Продавцы любых категорий с окладом ниже 2000 руб. | |
| Женщины кассиры или кассиры-контролеры | |
| Вдовы или разведенные женщины, имеющие детей | |
| Незамужние или разведенные, не имеющие детей | |
| Разведенные, имеющие детей | |
| Вдовы и вдовцы с окладом ниже 2500 руб. | |
| Незамужние продавцы 1-й и 2-й категорий | |
| Продавцы любых категорий с именами Елена или Вера | |
| Мужчины-бухгалтеры | |
| Мужчины с окладом выше 2000 руб. | |
| Женщины заместители любых категорий | |
| Мужчины продавцы любых категорий | |
| Женщины с окладом менее 2000 руб. |
ЗАДАНИЕ 4. Расширенный фильтр
Расширенный фильтр используется в случае сложных условий отбора записей. Для выполнения расширенной фильтрации БД над таблицей создается специальная область-диапазон условий. Диапазон условий –это блок ячеек, содержащий набор условий поиска, который можно использовать совместно с командой Расширенный фильтр для отбора данных списка. Диапазон условий состоит по крайней мере из одной строки подписей условий и одной строки собственно условий.
Рассмотрим технологию использования расширенного фильтра на примере выполнения одношаговой фильтрации согласно критерию "женщины, имеющие трех и более детей".
1. Для размещения диапазона условий вставьте четыре строки над таблицей исходной БД.
2. Скопируйте наименования полей БД в первую строку диапазона условий.
3. Внесите во вторую строку созданного диапазона условия выборки записей, как это изображено на рис. 20.
Важно!
Сложный критерий фильтрации формируется из простых критериев в отдельных ячейках диапазона условий по правилу: объединение критериев в строке осуществляется при помощи логической операции И, в столбце – логической операции ИЛИ.
Рис. 20
4. Установите курсор в БД.
5. Выберите в меню команду Данные/Фильтр/Расширенный фильтр...
6. Заполните диалоговое окно Расширенный фильтр.
Рис. 21
7. Скопируйте полученные результаты на лист Задание 4.
8. Для выполнения следующего задания отмените результаты фильтрации командой Данные / Фильтр / Отобразить все.
ТЕСТОВОЕ ЗАДАНИЕ 4
Номер варианта совпадает с номером компьютера!
Используя операцию расширенного фильтра, выполнить фильтрацию согласно критериям, заданным в таблице 9. Результат вместе с блоком критериев скопировать на лист Задание 4и сравнить полученные результаты.
ЗАДАНИЕ 5. Запросы к базе данных
Данное задание предполагает использование функций категории Работа с базой данных, которые применяют механизм расширенной фильтрации с последующей обработкой ее результатов:
· БДСУМ - суммирование значений в указанном столбце;
· БСЧЁТ - подсчет числа значений в указанном столбце, который должен содержать не текстовые значения;
· ДМАКС - нахождение максимального значения в указанном столбце;
· ДМИН - нахождение минимального значения в указанном столбце;
· ДСРЗНАЧ - вычисление среднеарифметического значения в указанном столбце.
Все вышеперечисленные функции имеют три аргумента:
- диапазон ячеек, занимаемых исходной БД;
- ячейка с именем поля-столбца, по которому после фильтрации производится суммирование, подсчет числа значений, поиск максимума и прочее;
- диапазон ячеек диапазона условий фильтрации.
Важно!
При использовании функции БСЧЁТ в качестве имени поля, по которому производится подсчет числа записей, прошедших фильтрацию, следует указать поле не текстового типа, например, арифметического (см. ниже пример выполнения задания 5).
Реализовать запрос к БД, используя функции категории Работа с базой данных. Привести расчетную формулу для подсчета числа работников, состоящих в браке и не имеющих детей.
1. Заполните блок критериев исходной БД новыми условиями выборки записей, как это показано на рис. 22.
Рис. 22
2. Введите в ячейку A86 (под таблицей исходной БД) текст пояснения планируемого результата запроса (например, количество бездетных работников, состоящих в браке).
3. Установите курсор в ячейку E86, где должен появиться результат подсчета, и вызовите Мастер функций.
4. Выберите категорию функций Работа с базой данных и имя функции БСЧЁТ.
5. Заполните диалоговое окно с указанием трех аргументов функции (см. рис. 23).
Рис. 23
6. Завершите диалог с Мастером функций, в результате чего в ячейку E86 должна быть введена формула:=БСЧЁТ(A5:I84;G5;H1:I3), где G5 - ячейка имени поля с арифметическими значениями - окладами, используемыми для подсчета числа записей, удовлетворяющих условиям фильтрации.
ТЕСТОВОЕ ЗАДАНИЕ 5
Номер варианта совпадает с номером компьютера!
Создайте запрос к БД для своего варианта в таблице 10, используя функции категории Работа с базой данных. Результат запроса скопируйте на чистый рабочий лист, который переименуйте в Запрос.
Таблица 10
| № варианта | Запрос к базе данных |
| Количество вдов и вдовцов | |
| Максимальный оклад у мужчин | |
| Минимальный оклад у женщин | |
| Количество женщин-продавцов 1-й категории | |
| Средний оклад у заведующих любых подразделений | |
| Общее количество детей у разведенных | |
| Средний оклад у бухгалтеров | |
| Количество холостяков с окладом выше 2500 руб. | |
| Максимальное количество детей у вдовцов и вдов | |
| Сумма окладов продавцов любых категорий | |
| Средний оклад продавцов любых категорий | |
| Количество вдов, имеющих детей | |
| Количество бездетных мужчин |
ЗАДАНИЕ 6. Создание сводных таблиц
При необходимости обобщить и проанализировать подробные данные, которые находятся в базе данных Microsoft Excel, можно использовать сводную таблицу или отчет сводных диаграмм. В отчете сводной таблицы автоматически создаются общие и промежуточные итоги. Создание такого отчета реализуется с помощью Мастера сводных таблиц, состоящего из четырех шагов:
Шаг 1 - подтверждение создания таблицы на основе данных, находящихся в списке или базе данных Microsoft Excel;
Шаг 2 - выделение диапазона ячеек, занимаемых БД;
Шаг 3 - разметка сводной таблицы при помощи создания Макета;
Шаг 4 - выбор варианта расположения сводной таблицы: лист с БД или отдельный лист.
Рассмотрим построение сводной таблицы, отображающей минимальные оклады по каждой группе семейного положения отдельно для женщин и мужчин.
1. Выберите в меню команду Данные/Сводная таблица...
2. На первом шаге диалога с Мастером сводных таблиц выберите вариант Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel.
3. На втором шаге выделите диапазон ячеек, занимаемых БД.
4. На третьем шаге диалога с Мастером сводных таблиц создайте Макет, для чего:
· перетащите поле Семейное положениев область строк сводной таблицы;
· перетащите поле Полв область столбцов сводной таблицы;
· перетащите поле Окладв область данных сводной таблицы;
· раскройте список вариантов вычислений в области данных двойным щелчком в соответствующем участке области данных и выберите позицию Минимум, как это показано на рис. 24.
Рис. 24
5. На четвертом шаге диалога с Мастером сводных таблиц выберите вариант Поместить таблицу на новый лист. Должен получиться перекрестный запрос, как показано на рис. 25.
Рис. 25
ТЕСТОВОЕ ЗАДАНИЕ 6
Номер варианта совпадает с номером компьютера!
Реализовать перекрестный запрос к БД, используя операцию построения сводной таблицы. Сохраните результаты на отдельном листе с именем Тест 6.
Таблица 11
| № варианта | Запрос к БД |
| Количество работников в каждой должности отдельно для женщин и мужчин | |
| Количество детей для различных групп семейного положения отдельно для женщин и мужчин | |
| Средний оклад работников в каждой должности отдельно для женщин и мужчин | |
| Максимальное количество детей для различных групп семейного положения отдельно для женщин и мужчин | |
| Максимальный оклад в каждой должности отдельно для женщин и мужчин | |
| Количество детей для женщин и мужчин отдельно по каждой группе семейного положения | |
| Минимальное количество детей для различных групп семейного положения отдельно для женщин и мужчин | |
| Средний оклад для женщин и мужчин отдельно по каждой категории должностей | |
| Минимальный оклад для женщин и мужчин отдельно по каждой должности | |
| Максимальное количество детей для различных групп семейного положения отдельно для женщин и мужчин | |
| Минимальное количество детей для женщин и мужчин отдельно для каждой группы семейного положения | |
| Максимальный оклад для женщин и мужчин отдельно по каждой категории продавцов | |
| Максимальное количество детей для женщин и мужчин отдельно для каждой группы семейного положения |
Библиографический список
1. Васильев А.Н. Научные вычисления в Microsoft Excel. - М. : Издательский дом «Вильямс», 2004. - 512 с.
2. Гельман В.Я. Решение математических задач средствами Excel : практикум. - СПб. : Питер, 2003. - 240 с.
3. Куправа Т.А. Excel: Практическое руководство. - М. : Диалог-МИФИ, 2004. - 240 с.
4. Петрунин Ю.Ю. Решение экономических задач в Excel. - М. : Изд-во МГУ, 2001. - 88 с.