2. табличный процессор ms excel
 Скачать 0.69 Mb.
|
Задание 3. Работа с базой данных. Поиск данных. Фильтрация данных. Сортировка и расчет промежуточных итогов. Сводные таблицы1. Работа с учебной базой данных. Откройте рабочую книгу, которую сохранили в лабораторной работе 2. На новом листе рабочей книги наберите учебную базу данных или скопируйте ее из файла Bazaprimer1.xls. Редактирование учебной базы данных. В учебной Базе данных (БД) внесите следующие дополнения. В БД добавьте поля с названиями Дата поступления и Дата Продажи. Заполните их любыми реальными данными. Заполнение данных выполните с использование прогрессии. Для этого в меню Главная выберите команды Редактирование - Заполнить – Прогрессия.  В БД добавьте поле с названием Срок продажи и заполните его формулами. Срок продажи = Дата продажи – Дата поступления. Вставьте после столбцов Дата поступления и Дата продажи столбцы Месяц поступления и Месяц продажи. Заполните новые столбцы формулами для отображения числа месяца из даты поступления с помощью функции Месяц (). В БД добавьте поле с названием «Итого Сумма» и вычислите его по формуле: Итого Сумма = Цена в $ * Количество Добавление новых записей в список Базы Данных и их редактирование. Добавление записей в БД можно выполнить с помощью ручного ввода данных в новые строки или с помощью команды Ячейки в меню Главная. Научитесь добавлять, удалять записи БД. Вручную добавьте с 40 по 44 записи в БД. При заполнении поля Наименование вводите только те названия, которые перечислены выше, задав произвольные данные в других полях БД. Примечание: поля БД необходимо заполнять однотипными данными. Например, в поле Наименование данные введены только строчными буквами. Записи БД с наименованием товара – телевизор, Телевизор, теле-р или ТЕЛЕВИЗОР могут быть восприняты как разные. С помощью команды Вставить в меню Ячейки и кнопки Добавить введите с 45 по 50 записи. Заполните их перечисленными товарами с произвольными данными. С помощью кнопки Удалить из БД удалите 45 запись, а затем вновь ее добавьте. Отредактируйте последнюю запись БД (измените ее). Возвратите прежние данные в последней записи с помощью кнопки Вернуть (возвращение предыдущего значения возможно, если вы еще не перешли к следующей записи). 2. Поиск данных Изучите способы поиска записей в БД с помощью команды Форма. Для задания условий поиска необходимо использовать кнопку Критерии в диалоговом окне Форма. При поиске можно применять символы ? и *, а также операторы сравнения = , <>, <,>, <= , >=. После задания всех условий поиска будет отображена первая найденная запись. Для отображения других найденных записей используются кнопки Назад и Далее. Выполните поиск следующих данных:
3. Фильтрация данных Фильтрация данных – это отбор данных по заданным условиям. Фильтрацию данных можно выполнить с помощью Автофильтра и Расширенного фильтра Работа с автофильтром. Автофильтр предоставляет простой доступ к мощным средствам рабочего листа. Результаты автофильтра отображаются на листе, при этом записи, не удовлетворяющие заданному критерию, скрыты Изучите фильтрацию данных с помощью Автофильтра. Решите следующие задачи с помощью команды Фильтр – Автофильтр в меню Данные:
Использование Расширенного фильтра. Расширенный фильтр применяют при более сложных условиях отбора записей из БД, чем при Автофильтре. При создании данного фильтра обязательно создается блок критериев (диапазон условий). Условия отбора в блоке критериев могут быть простые и вычисляемые. Правила создания блока критериев зависит от типа условий отбора. После создания блока критериев необходимо применить команду Дополнительно в меню Данные. 1. Работа Расширенного фильтра с простыми условиями Изучите правила работы Расширенного фильтра с простыми условиями. Копирование результатов фильтрации на другой лист. Пример создания простого Расширенного фильтра. Из списка БД отберите пылесосы, марка которых начинается на М или S, с ценой от 200$ до 500$ и отобразите в том же списке, а затем начиная с ячейки А55. Ячейки блока критериев выделите цветом. В ячейку А48 введите название фильтра – Выбор пылесосов маркой на М,S и ценой от 200$ до 500$. Порядок действий по созданию блока критериев. В ячейки А50 и B50 скопируйте соответственно из основной БД заголовки полей: Название и Марка, а в ячейки С50 и D50 – Цена в $. Таблица 2.5. Пример создания Расширенного фильтра с простыми условиями
В ячейки А51 и А52 введите условие фильтра путем копирования из БД наименование товара – пылесос. Заполните ячейки блока критериев согласно Таблице. Выполните фильтрацию данных. Установите курсор в любую ячейку БД. В меню Данные выберите команду Дополнительно. В диалоговом окне Расширенный фильтр заполните путем выделения диапазонов следующие поля: Исходный диапазон (А1: H41) и Диапазон условий (А50: D52). Включите кнопку: – Скопировать результат в другое место. Выделите курсором ячейку А55. Нажмите кнопку – ОК. Выполните самостоятельно следующие задания. :
Переименуйте новый лист с фильтрами как «Фильтры 1». 2. Работа Расширенного фильтра с вычисляемыми условиями Изучите правила работы Расширенного фильтра с вычисляемыми условиями. Пример создания Расширенного фильтра с вычисляемыми условиями. Из списка БД выберите телевизоры из магазинов 1 и 3, а затем из них те, цена которых ниже средней. Решение задачи представлено в таблице Выполните самостоятельно следующие задания. Блок критериев и результаты фильтрации отобразите на новом листе рабочей книги:
Переименуйте ярлык листа как «Фильтры 2». 4. Сортировка данных и подсчет промежуточных итогов Изучите команду Сортировка в меню Данные. Выполните задания:
Таблица 2.6. Пример решения задачи с вычисляемыми условиями
5. Сводные таблицы Создание сводной таблицы. Создать сводную таблицу можно с помощью команды Сводная таблица в меню Вставка. При этом работает Мастер Сводных таблиц. Изучите способы создания сводных таблиц, назначение основных кнопок на панели инструментов Сводная таблица. Создайте Сводную таблицу 1, где в области строк указано Наименование товаров, в области столбцов – Магазины, в области данных – Итого Сумма. В Сводной таблице должны быть подведены итоги по строкам и столбцам. Расположите Сводную таблицу на текущем листе, ниже Базы данных, а затем на новом листе. На новом листе, дайте название полученной таблице – Сводная таблица 1. Ярлыку сводной таблицы дайте имя – Сводная таблица 1. Создайте любую сводную таблицу на новом листе с названием ярлыка Сводная таблица 2. Редактирование сводной таблицы. Изучите способы редактирования сводных таблиц Изменение расположения полей в Сводной таблице. В сводных таблицах 1,2 измените по желанию расположение полей: поменяйте местами поля в области строк, столбцов, страниц. Верните Сводную таблицу 1 в исходное состояние. Добавление и удаление полей в Сводной таблице. Скопируйте сводную таблицу 1 на новый лист рабочей книги. Добавьте в области строк – поле Марка, в области страниц – поле Месяц продажи. Дайте название таблицы и ярлыка листа – Сводная таблица 3. Удалите в сводной таблице 3 поле Месяц продажи из области страниц. В сводной таблице 2 добавьте и удалите любые поля по желанию. Форматирование сводной таблицы. Форматирование таблицы выполняется стандартными способами: с помощью команды Ячейки в меню Формат или команды Формат ячеек в контекстном меню. Кроме того, имеется возможность задать Автоформат. Для этого на панели инструментов Сводная таблица в раскрывающемся списке Сводная таблица следует выбрать команду Формат Отчета. В Сводной таблице 1 задайте числовым данным финансовый, а затем денежный форматы. Задайте заливку ячеек, параметры для шрифта, границы по желанию. Вычисление итоговых значений. Изучите способы вычисления итоговых значений в строке Общий итог в виде суммы, среднего минимального и других значений с помощью команды Параметры поля.. в раскрывающем списке Сводная таблица на панели инструментов Сводной таблицы, а также с помощью команды Параметры поля .. в контекстном меню выделенной ячейки. Выделяют ячейку либо в области данных, либо в области строк (левый столбец сводной таблицы). В Сводной таблице 1 последовательно вычислите в строке Общий итог среднее, затем минимальное, максимальное значение по полю Сумма в $. Верните Сводную таблицу 1 в исходное состояние. Скрытие и отображение данных в сводной таблице. Изучите способы скрытия данных в Сводной таблице: с помощью включения (отключения) в раскрывающем списке области строк флажков тех элементов, которые следует скрыть или отобразить, с помощью команды Скрыть из контекстного меню выделенных объектов. Для отображения всех данных следует включить флажок Все. Скройте, а затем отобразите в Сводной таблице 1 следующие товары: видеокамеры, телевизоры. Отображение данных на отдельных листах. Изучите способы отображения данных из Сводной таблицы на отдельных листах рабочей книги: с помощью команды Отобразить страницы в раскрывающем списке Сводная таблица на панели инструментов Сводная таблица; с помощью команды Отобразить страницы контекстного меню кнопки в области страниц Сводной таблицы. При этом в рабочей книге автоматически создаются новые листы со сводными таблицами из элементов области страниц. Для Сводной таблицы 1 отобразите данные каждой марки на отдельных листах рабочей книги. Убедитесь, что в рабочей книге созданы новые листы для каждой марки. Сортировка данных. Сортировку значений элементов можно выполнить несколькими способами: с помощью команды Сортировка в меню Данные, кнопками сортировки по возрастанию или убыванию на стандартной панели инструментов. с помощью команды Сортировка и десятка лучших из раскрывающегося списка на панели Сводная таблица. В последнем случае предварительно необходимо установить курсор на кнопку раскрывающего списка в области строк или столбцов. Изучите различные способы сортировки. Выполните задания:
Группировка данных. Различают два основных вида группировки: группировка по временным диапазонам и группировка отдельных элементов (создание групп, состоящих их отдельных элементов сводной таблицы). Группировка по временным диапазонам Для создания временных диапазонов необходимо выполнить следующие действия: выделите кнопку поля, для которого идет группировка или установите курсор на любой элемент в группе, затем нажмите кнопку Группировать на панели Сводная таблица или выберите команду Структура - Группировать в меню Данные, в раскрывающемся списке Сводная таблица или контекстном меню. В диалоговом окне Группирование задайте начальное, конечное значение и шаг. В Сводной таблице 1 сгруппируйте магазины с 1 по 5, с шагом 2. Отмените группировку. Повторите группировку другими способами. Группировки отдельных элементов (создание групп). Данная группировка осуществляется аналогичными способами, что и группировка по временным диапазонам. Отличие заключается в том, что перед группировкой следует выделить отдельные элементы, входящие в группу. В Сводной таблице 3 создайте отдельную группу для товаров фирм: SONY, PANASONIC. В Сводной таблице 2 создать любую группу отдельных элементов. Вычисление промежуточных итогов. Изучите способы вычисления промежуточных итогов для поля Сводной таблицы Порядок действий: выделите кнопку поля или любую ячейку в поле, выберите команду Параметры поля …в контекстном меню выделенного объекта или в раскрывающемся списке Сводная таблица, или на одноименной панели инструментов, включите кнопку Другие (Итоги), с помощью клавиш Ctrl, Shift выделите одновременно все необходимые функции для подсчета промежуточных итогов (например, Сумма, Среднее, Минимальное), нажмите кнопку ОК. Для Сводной таблицы 3, расположенной на отдельном листе, вычислите и отобразите одновременно для каждого вида товара в поле Наименование следующие промежуточные итоги: сумма, среднее, минимальное значение. В сводной таблице 2 выполните подсчет промежуточных итогов. |