Практическая работа служит для получения практических навыков по изучению следующих тем
 Скачать 111.5 Kb.
|
|
Практическая работа СТРУКТУРИРОВАНИЕ, КОНСОЛИДАЦИЯ ДАННЫХ И ПОСТРОЕНИЕ СВОДНЫХ ТАБЛИЦ Практическая работа служит для получения практических навыков по изучению следующих тем: манипулирование данными, расположенными на разных листах рабочей книги; списки и операции со списками (фильтрация, сортировка); использование диалоговых окон для изменения информации в списках; структура таблицы (создание и удаление); консолидация данных, расположенных на разных листах рабочей книги методом использования команды Данные/Консолидация; построение сводных таблиц. Цель работы: научиться объединять данные расположенные на разных листах, формировать структурированные отчеты в табличной форме, осуществлять поиск и фильтрацию данных в таблицах. Основные сведения о списках, структуре рабочего листа, консолидации и сводных таблицах Список - это упорядоченный набор данных, база данных на рабочем листе. Столбцы списка называются полями, строки – записями. Ведение списка можно осуществлять в диалоговом окне (форме). Над списками можно выполнять такие операции, как фильтрация и сортировка. В процессе сортировки списка переупорядочиваются строки в соответствии с содержимым одного, двух или трех столбцов. Фильтрация – это быстрый способ выделения подмножества данных списка для последующей работы с ним. В результате фильтрации списка на экран выводятся только те строки, которые содержат определенные значения, либо те, которые удовлетворяют некоторому набору условий поиска (критерию). Структура таблицы наиболее полезна для создания итоговых отчетов, в которых не нужно приводить все детали. Один рабочий лист может иметь только одну структуру (горизонтальную, вертикальную или обе). Структура может иметь до восьми уровней вложения. Консолидация – это комбинирование или накопление информации из двух или более рабочих листов в нескольких рабочих книгах. Основным фактором, влияющим на успешность консолидации, является способ размещения информации в рабочих листах. Если эти способы во всех рабочих листах одни и те же, в этом случае задача консолидации становится достаточно простой. Сводная таблица - это динамический итог данных, содержащихся в базе данных. Создается сводная таблица только при помощи специального средства "Мастер сводных таблиц". Выполнение практической работы Перед вами стоит задача совместной обработки нескольких таблиц – списков, расположенных на разных листах рабочей книги. 1. Загрузить программу Excel. 2. На листе рабочей книги (Лист1) создать табл. 1 с исходными данными о заказе партий запчастей у некоторой фирмы на год: Таблица 1
3. Рассчитать цену одного экземпляра по каждому наименованию заказанной продукции путем ввода и последующего копирования формулы. 4. Переименовать "Лист1" в "Заказ". Для этого установить указатель на ярлык "Лист1", нажать правую кнопку мыши, в контекстном меню выбрать команду Переименовать и вместо прежнего имени листа Лист1 ввести новое имя Заказ. 5. Получить итоговую сумму по столбцу "Сумма". Для этого установить курсор в ячейку Е12 и нажать кнопку автосуммирования. Ввести сформированную формулу. 6. Создать структуру построенной таблицы для скрытия детальных числовых данных. Для этого установите курсор внутри таблицы и выполните команду Данные/Группа и Структура/Создание структуры. На экране структуры таблицы щелкните кнопку "-", чтобы скрыть столбцы с числами, а затем кнопку "+" для показа скрытой информации. Удалить структуру, выполнив команду Данные/Группа и Структура/Удалить структуру. Добавить к существующим листам рабочей книги еще один. Для этого установить указатель на один из ярлыков, нажать правую кнопку мыши и выбрать команду Добавить. В диалоговом окне Вставка выделить значок с названием "Лист" и нажать кнопку "Ok". Переименовать "Лист2", "Лист3", "Лист4" в "Январь", "Февраль", "Март" (табл. 2, 3, 4), так как они будут содержать информацию о реализации запчастей за первые три месяца года. Ввод данных осуществлять в соответствии с указаниями следующего пункта. Январь Таблица 2
Февраль Таблица 3
Март Таблица 4
10. Сгруппировать листы "Январь", "Февраль", "Март" для ввода общей для них информации. Группа листов создается щелчком мышью на ярлыке листа при нажатой клавише <CTRL>. Для ввода индивидуальной для каждой таблицы информации листы разгруппировать путем выбора в контекстном меню команды Разгруппировать листы. 11. Ввести и размножить формулы для подсчета стоимости и итоговых сумм во введенные таблицы. 12. Использовать консолидацию рабочих листов для получения итоговой информации о продаже литературы в целом за квартал (Январь, Февраль, Март). Для этого: добавить новый лист, переименовать его в "Квартал"; выделить ячейку на новом листе "Квартал", начиная с которой будут размещены итоговые данные (например, А1); выполнить команду Данные/ Консолидация; в диалоге "Консолидация" выбрать в списке функций функцию Сумма; в строку "Ссылка" ввести абсолютную ссылку на консолидируемые данные (например, Январь!$А$2:$С$12) и нажать кнопку "Добавить"; повторить ввод и добавление данных для ввода всей консолидируемой информации; включить флажок "значения левого столбца"; нажать кнопку "Ok". 13. Изменить некоторые данные в одном из консолидируемых листов, например количество проданных Адсорберов в Январе. Изменятся ли данные в итоговой таблице? 14. Установить связанную консолидацию данных. Для этого вставить новый рабочий лист, переименовать его в "Квартал1", активизировать ячейку начала формирования итоговой таблицы (например, А1) , выполнить все положения пункта 12, добавив флажок "Создавать связи с исходными данными". В полученной структурированной таблице просмотреть скрытые данные, нажав кнопки "2" или "+". Изменить некоторые данные в одном из консолидируемых листов, например, количество проданных Адсорберов в Январе. Изменятся ли данные в итоговой таблице? Построить сводную таблицу, информирующую о сумме проданных деталей по каждому наименованию отдельно. Для этого: активизировать рабочий лист "Заказ"; вызвать мастер сводных таблиц, выполнив команду Данные/ Сводная таблица; в окне "Мастер сводных таблиц – шаг 1 из 4" выбрать источник, откуда будут поступать данные для построения таблицы (первую из предложенных опций: "В списке или в базе данных Microsoft Excel") и щелкнуть по кнопке "Далее>"; в окне шага 2 ввести область исходных данных для построения сводной таблицы, например, Заказ!$А$1:$F$11, и щелкнуть по кнопке "Далее>"; в окне шага 3 для определения внешнего вида сводной таблицы требуется в макете сводной таблицы перетащить поле таблицы "Название" в поле макета "столбец", поле таблицы "Квартал" в поле макета "строка", "Сумма" должна быть расположена в поле "данные", а затем щелкнуть по кнопке "Далее>"; в окне шага 4 установить переключатель "Новый лист" и нажать кнопку "Готово". Изменить исходные данные (сначала убрать, а затем добавить одну строку в исходную таблицу) и в контекстном меню (щелчком правой клавиши мыши на поле сводной таблицы) выбрать команду Обновить данные. Поменять местами строки и столбцы сводной таблицы. Для этого снова запустить Мастер сводных таблиц и в диалоге шага 3 повернуть макет таблицы на 90 градусов: в строках вывести названия изданий, а в столбцах – квартал, после чего завершить диалог. Открыть макет сводной таблицы (3 шаг) и перетащить поле "Название" в область макета "страница", закончить диалог и обратить внимание на изменения в сводной таблице, затем нажать кнопку "Отобразить страницы" панели инструментов "Сводная таблица". 21. Отсортировать данные таблицы "Заказ" по возрастанию цены. Для этого: сделать текущей ячейку поля "Цена"; нажать кнопку инструментального меню "Сортировать по возрастанию". 22. Выполнить многоуровневую сортировку по двум ключам: сначала по цене, потом по названиям в порядке возрастания значений этих ключей. Для этого: установить курсор в область данных таблицы "Заказ"; вызвать команду Данные/Сортировка; в диалоговом окне в область "Сортировать по" ввести первый ключ сортировки "Цена"; в область "Затем по" ввести второй ключ сортировки "Название"; нажать кнопку "Параметры..." и ознакомиться с возможными вариантами задания параметров сортировки; щелкнуть кнопку "Ok". 23. Выполнить подсчет промежуточных итогов по Количеству шт. в партии отдельно по кварталам, предварительно отсортировав данные таблицы "Заказ" по возрастанию номера квартала. Для этого: сделать текущей ячейку поля "Квартал"; нажать кнопку инструментального меню "Сортировать по возрастанию"; выполнить команду Данные/Итоги; в диалоговом окне команды "Промежуточные итога" в области "При каждом изменении в" выбрать "Квартал", в области "Операция" выбрать "Сумма", в области "Добавить итоги по" выбрать " Количество шт. в партии "; щелкнуть кнопку "Ok". 24. Аннулировать промежуточные итоги таблицы "Заказ". Для этого: установить указатель мыши на таблицу "Заказ"; выполнить команду Данные/Итоги; в диалоговом окне команды "Промежуточные итоги" щелкнуть по кнопке "Убрать все". 25. Использовать форму базы данных для просмотра и корректировки записей таблицы "Заказ". Для этого: выделить область рабочего листа, где находятся данные вместе с названием столбцов; выполнить команду Данные/Форма; переместиться вперед и назад по таблице, используя кнопки "Далее" и "Назад"; удалить последнюю строку таблицы нажатием кнопки "Удалить"; добавить новую строку, используя кнопку "Добавить"; закрыть диалог нажатием кнопки "Закрыть". 26. Использовать форму базы данных для поиска в таблице "Заказ" информации о запчастях, Количество шт. в партии которых превышает 10000. Для этого: выделить область рабочего листа, где находятся данные вместе с названием столбцов; выполнить команду Данные/Форма; в диалоговом окне "Заказ" щелкнуть по кнопке "Критерии"; в поле Количество шт. в партии ввести критерий поиска (>10000); переместиться вперед и назад по найденным записям, используя кнопки "Далее" и "Назад"; закрыть диалог нажатием кнопки "Закрыть". 27. Самостоятельно выполнить: сортировку данных таблицы "Заказ" по возрастанию значений поля "Номер"; поиск в таблице "Заказ" информации о запчастях, заказанных в первом квартале с Количеством шт. в партии менее 20000. 28. Использовать автофильтр для вывода в таблице "Заказ" информации только о запчастях, цена которых более 5000 руб. Для выполнения этого задания необходимо: отметить область рабочего листа с данными и с заголовками; выполнить команду Данные/Фильтр/Автофильтр; раскрыть список на поле "Цена", выбрать пункт "Условие" и ввести выражение "больше 5000"; щелкнуть кнопку "Ok". 29. Отменить автофильтр, для этого выполнить команду Данные/Фильтр и снять пометку с позиции Автофильтр. 30. Использовать усиленный фильтр для получения данных о запчастях, цена которых менее 5000, заказанных в третьем квартале Количеством шт. в партии более 10000. Для этого: скопировать имена столбцов "Цена", "Квартал" и "Количество шт. в партии" в ту часть рабочего листа, которая не содержит данных для поиска: H1,I1,J1; в клетки H2,I2,J2 ввести критерии поиска (<5000, >10000, Кв3); выполните команду меню Данные/Фильтр/Расширенный фильтр; в диалоге "Расширенный фильтр" задать область, где находятся данные (интервал списка) и область, в которой заданы критерии поиска; в группе "Обработка" укажите, что фильтрация будет выполняться на месте; щелкните кнопку "Ok". 31. Сохранить рабочую книгу в файле с именем lab3.xls. 32. Для выхода из Excel выберите из меню команду Файл/Выход. Задания для самостоятельной работы Проанализируйте данные о произведённых ремонтных работах некоторого автосервиса, в качестве исходных данных используйте данные в файле copir/ 311-315_ИТ_в_ПД /ТО.xls. Результаты по каждому вопросу представьте на отдельном листе. Проанализируйте суммы ремонта по видам ремонта Для этого существует несколько способов: Первый: Отсортируйте всю таблицу по столбцу вид ремонта. Выполните команду Данные/ Итоги просуммируйте Сумму ремонта при каждом изменении вида ремонта. Отобразите только итоги по видам ремонта Второй: Постройте сводную таблицу, где в строках будут виды ремонта, а в области данных сумма ремонта, можно в область столбцов перенести фамилию мастера. Постройте диаграмму. Проанализируйте суммы ремонтов по видам ремонта и маркам автомобилей. Проанализируйте количество ремонтов по маркам автомобилей. Получите фамилии владельцев а/м форд, для этого дважды кликните по соответствующей ячейке сводной таблицы или примените фильтр. Проанализируйте количество и суммы ремонтов по мастерам-исполнителям. Найдите информацию о ремонтах на сумму более15000 руб. Найдите информацию о ремонтах на сумму более15000 руб. а/м Audi мастера Зайцева (примените фильтр). Подготовьте информацию о клиентах проходивших ТО1 отдельно по каждой марке. Подготовьте информацию о времени работы мастеров отдельно за каждый месяц. Для этого постройте сводную таблицу в область строк поместите дату ремонта, в области столбцов ФИО мастера, в область данных сумму по времени работ. Сгруппируйте данные по месяцам: установите курсор на любую ячейку с датой и выполните команду Данные/ Группировка Сгруппируйте ремонты на мелкие, средние и крупные (до 3000р.; от3000 до10000р.; свыше 10000р.) и подсчитайте их количество и среднюю сумму по группе. Проанализируйте количество ремонтов по видам ремонтов отдельно по маркам автомобилей Audi, Nissan и Skoda. Постройте сводную таблицу, где в области строк будут виды ремонта, в области столбцов – модели а/м, марки а/м поместите в область страниц и выберите только марки Audi, Nissan и Skoda, а в область данных поместите сумму ремонта. |