Выполнение лабораторной работы. 1. Загрузите программу Excel 2013.

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

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

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

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

Таблица 2

Таблица 3

Таблица 4

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

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

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

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

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

· выполните: Данные/ Работа с данными/ Консолидация;

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

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

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

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

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

Таблица 5

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

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

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

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

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

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

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

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

· в окне Поля сводной таблицы перетащите поле Название в область полей СТРОКИ, поле Квартал – в область полей КОЛОННЫ, а поле Сумма – в область полей ЗНАЧЕНИЯ.

Таблица 6

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

19. Переименуйте лист со сводной таблицей в Сводная_таблица.

20. Постройте сводную диаграмму на основе сводной таблицы. Для этого щелкните по любой ячейке сводной таблицы, а затем выполните следующее: Вставка/Диаграммы/ Гистограмма/Гистограмма с накоплением.

Пример Гистограммы

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

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

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

· Выполните Главная/Редактирование/Сортировка и фильтр /Сортировка от А до Я (по возрастанию).

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

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

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

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

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

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

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

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

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

· Выполните Главная/Редактирование/Сортировка и фильтр/Сортировка от А до Я (по возрастанию);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

· щелкнуть стрелку в заголовке столбца Цена;

· выбрать Числовые фильтры/Больше;

· ввести«больше 85»;

· щелкнуть кнопку OК.

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

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

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

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

· создайте таблицу критериев, скопировав имя столбца Тираж в ячейку D17, а имя столбца Цена в ячейку Е17;

· введите логическое условие >10000 в ячейку D18 и логическое условие <90 в ячейку Е18;

Примечание. Если условия отбора находятся в одной строке таблицы критериев, то они объединяются логическим оператором И, например:

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

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

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

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

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

Создание таблицы с записями, в которых тираж, выпущенных книг больше 1000.

30. Самостоятельно (!) создайте расширенный фильтр для поиска в таблице Заказ информации о продукции, тираж которой превышает 10000 или цена которой меньше 90 рублей, предварительно скопировав таблицу листа Заказ на лист По_критерию2.

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

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

· на листе Средняя_цена создайте новую область критериев, поместив в ячейку I4 заголовок столбца Цена больше средней;

· в ячейку D12 поместите формулу расчета средней цены: =СРЗНАЧ(D2:D11);

· в ячейку I5 введите критерий поиска: =D2>$D$12. В ячейку будет выведено логическое значение Ложь;

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

· введите в диалоговое окно Расширенный фильтр данные для поиска:

- установите флажок Фильтровать список на месте;

- исходный диапазон A1:G11;

- диапазон критериев I4-I5;

· нажмите ОК. Список выведен на рисунке.

Таблица «Цена больше средней»

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