Выполнение лабораторной работы.

Пример структуры для скрытия детальных числовых данных

7. Удалите структуру, выделив, ячейки с числовыми значениями и выполнив команду Данные/Разгруппировать/столбцы.

8. Добавьте к существующим листам рабочей книги еще один, щелкнув по ярлыку Вставить лист.

9. Переименуйте Лист2, Лист3, Лист4 в Январь, Февраль, Март, так как они будут содержать информацию о реализации продукции за первые три месяца 2011 года (табл. 2,3,4).

10. Сгруппируйте листы Январь, Февраль, Март для ввода общей для них информации (названия столбцов и наименование товара). Группа листов создается щелчком мышью на ярлыке листа при нажатой клавише CTRL.

11. Для ввода индивидуальной для каждой таблицы информации разгруппируйте листы путем выбора в контекстном меню команды Разгруппировать листы.

Таблица 2

Таблица 3

Таблица 4

12. Введите и размножьте формулы для подсчета стоимости и итоговых сумм во введенные таблицы. Для нахождения цены каждой продукции в таблице на листе Заказ используйте функцию ВПР:

=ВПР(Март!A3;Заказ!$B$2:$F$11;3;ложь)*B3

(для таблицы 4)

13. Используйте консолидацию рабочих листов для получения итоговой информации о продаже литературы в целом за квартал (Январь, Февраль, Март). Для этого:

· Добавьте новый лист переименуйте его в Консолидация.

· Выделите ячейку на новом листе Консолидация, начиная с которой будут размещены итоговые данные (например, A1);

· Выполните: команду Консолидация вкладкиДанные группы/ Работа с данными.

· в диалоговом окне Консолидация выберите в списке функций функцию Сумма;

· в строку Ссылка введите абсолютную ссылку на консолидируемые данные (например, Январь!$A$2:$C$12) и нажмите кнопку Добавить;

· повторите ввод и добавление данных для ввода всей консолидируемой информации (Март!$A$2:$C$12 и Февраль!$A$2:$C$12);

· включите флажок значения левого столбца;

· нажмите кнопку OK.

Таблица 5

14. Измените некоторые данные в одном из консолидируемых листов. Изменятся ли данные в итоговой таблице?

15. Установите связанную консолидацию данных. Для этого вставьте новый рабочий лист, переименуйте его в Консолидация 1, активизируйте ячейку начала формирования итоговой таблицы (например, A1), выполните все положения пункта 13, добавив флажок Создавать связи с исходными данными.

16. В полученной структурированной таблице просмотрите скрытые данные, нажав кнопки «2» или «+».

17. Измените некоторые данные в одном из консолидируемых листов. Изменятся ли данные в итоговой таблице на этот раз?

18. Постройте сводную таблицу, информирующую о сумме изданной литературы по каждому наименованию отдельно. Для этого:

· активизируйте рабочий лист Заказ;

· вызовите мастер сводных таблиц и диаграмм, выполнив команду Вставка/Таблицы/Сводная таблица;

· в окне Создание сводной таблицы выберите источник, введите диапазон исходных данных для построения сводной таблицы, например, Заказ!$A$1:$G$11, установите переключатель Новый лист и нажмите кнопку ОК;

· постройте сводную таблицу, перетащив в окне Список полей сводной таблицы поле Название в область полей строк, поле Квартал – в область полей столбцов, а поле Сумма – в область значений.

Пример сводной таблицы со списком полей

19. Измените исходные данные (сначала уберите, а затем добавьте одну строку в исходную таблицу) при этом проверьте обновления таблицы, выполнив команду Обновить все вкладки Данные группы Подключения (также можно выполнить аналогичные действия, используя вкладку Параметры группы Данные).

Примечание. При работе со сводными таблицами на ленте появляется дополнительная вкладка Работа со сводными таблицами – Параметры, Конструктор.

20. Постройте сводную диаграмму (разрезанная круговая) на основе сводной таблицы, выполнив команду Работа со сводными таблицами/ Параметры/Сервис/Сводная диаграмма.

Пример разрезанной круговой диаграммы.

21. Самостоятельно постройте сводную таблицу, отражающую количество наименований учебников каждого автора, выпущенных в каждом квартале.

22. Отсортируйте данные таблицы Заказ по возрастанию цены. Для этого:

· Выделите ячейку поля Цена;

· Выполните команду Сортировка от А до Я кнопки Сортировка и фильтр вкладки Главная группы Редактирование.

23. Выполните многоуровневую сортировку по двум ключам: сначала по цене, потом по названиям в порядке возрастания значений этих ключей. Для этого:

· установите курсор в область данных таблицы Заказ;

· выполните команду Настраиваемая сортировка вкладки Данные группы Сортировка и фильтр;

· в диалоговом окне в область Сортировать по введите первый ключ сортировки «Цена»;

· Нажмите на «Добавить уровень» и введите в значение Затем по «Наименование товара»

· Щелкните кнопку OK

24. Выполните подсчет промежуточных итогов по тиражу выпуска в разрезе кварталов, предварительно отсортировав данные таблицы Заказ по возрастанию номера квартала. Для этого:

· удалите итоговую сумму в столбце Сумма;

· сделайте текущей ячейку поля Квартал;

· Выполните команду Сортировка от новых к старым вкладки Данные группы Сортировка и фильтр.

· выполните команду Промежуточный итог вкладки Данные группы Структура;

· в диалоговом окне команды Промежуточные итоги в области «При каждом изменении в» выберите Квартал, в области «Операция» выберите Сумма, в области «Добавить итоги по» выберите Сумма;

· установите флажки Заменить текущие итоги и Итоги под данными;

· щелкните кнопку OK.

25. Аннулируйте промежуточные итоги таблицы Заказ. Для этого:

· установите указатель мыши на таблицу Заказ;

· выполните команду Промежуточный итог вкладки Данные группы Структура;

· в диалоговом окне команды Промежуточные итоги щелкните по кнопке Убрать все.

26. Выполните подсчет многоуровневых итогов по стоимости выпуска учебников в разрезе авторов и кварталов. Для этого:

· выполните многоуровневую сортировку таблицы Заказ сначала по авторам, а затем по кварталам;

· с помощью команды Промежуточный итог вкладки Данные группы Структура подсчитайте суммарную стоимость выпуска учебников каждого автора;

Пример промежуточных итогов (Задание 26)

· повторно выполните команду Промежуточный итог вкладки Данные группы Структура для подсчета суммарной стоимости продукции в каждом квартале, сняв в диалоговом окне команды флажок Заменить текущие итоги.

27. Используйте автофильтр для вывода в таблице Заказ информации только о книгах, цена которых более 85 руб. Для этого:

· выделите область столбца Цена с данными и заголовком;

· выполните команду Данные/Фильтр;

· нажмите стрелку списка в заголовке столбца Цена, выберите Числовые фильтры/больше;

· введите 85, нажмите OК.

28. Отмените фильтр, выполнив команду Данные/Сортировка и фильтр/Очистить. Чтобы убрать значок автофильтра из заголовка столбца, повторно нажмите Фильтр на вкладке Данные в группе Сортировка и фильтр.

29. Воспользуйтесь расширенным фильтром для поиска в таблице Заказ информации о продукции, количество которой превышает 10000 И фамилии авторов начинаются на букву «С». Для этого:

· скопируйте лист Заказ и переименуйте его в По_критерию;

· в ячейку D15 введите текст «Критерий»;

· создайте таблицу критериев, скопировав имя столбца (поля) Тираж в ячейку D16, а имя столбца Автор в ячейку Е16;

· в ячейки D17 и Е17 введите условия: >10000 и С*;

· создайте заголовки столбцов для результирующей таблицы, скопировав имена всех столбцов основной таблицы в диапазон ячеек А20:G20.

· поместите курсор в пределы основной таблицы и выполните команду Данные/Сортировка и фильтр/Дополнительно;

· в диалоговом окне Расширенный фильтр установите флажок Скопировать результат в другое место, задайте исходный диапазон А1:G11, диапазон условий D16:E17 и диапазон таблицы результатов A20:G20;

· нажмите ОК (ниже представлен вид выполненного задания):

Создание таблицы с записями, в которых тираж, выпущенных книг больше 10000 и фамилии авторов начинаются на букву «С»

30. Если условия отбора должны объединяться логическим оператором ИЛИ, они должны находиться в разных строчках таблицы критерия, например:

31. С помощью расширенного фильтра найдите все книги, цена которых находится в диапазоне от 80 до 100 рублей.

Для этого:

· создайте новый диапазон условий: в ячейки I4 и K4 поместите заголовок столбца Цена. В ячейки I5 и K5 поместите условия отбора: >=80 и <=100;

· с помощью команды Данные/Сортировка и фильтр /Дополнительно выполните фильтрацию данных, сославшись на диапазон условий I4:K5 и поместив результат фильтрации в свободное место рабочего листа.

32. Самостоятельно с помощью расширенного фильтра найдите все книги, тираж которых больше 10000 или цена меньше 80 рублей.

32. Сохраните рабочую книгу в файле с именем lab3.xls.