Внимание! работу производите только в своей рабочей книге!!!

ЗАДАНИЕ 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 с.

Статьи к прочтению:

NYUSHA / НЮША — Только… (Official clip) HD

Похожие статьи: