1. Загрузите программу Excel 2013.
2. На листе рабочей книги (Лист1) создайте табл.1 с исходными данными, приведенными ниже.
Таблица 1
3. Рассчитайте цену одного экземпляра по каждому наименованию книжной продукции путем ввода следующей формулы:
= F 2/ E 2
4. Переименуйте Лист1 в Заказ.
5. Получите итоговую сумму по столбцу Сумма. Для этого выделите ячейкуF12, и на вкладке Главная дважды щелкните по кнопкеавтосуммирования (∑).
6. Создайте структуру построенной таблицы для скрытия детальных числовых данных. Для этого выделите столбцы с числовыми значениями и выполните следующие действия: Данные/Структура/Группировать/Колонны. На экране структуры таблицы щелкните кнопку «–», чтобы скрыть столбцы с числами, а затем кнопку «+» для показа скрытой информации
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;
Примечание. Если условия отбора находятся в одной строке таблицы критериев, то они объединяются логическим оператором И, например:
| Тираж | Цена |
| >1000 | <90 |
Если условия отбора находятся в разных строчках таблицы критериев, то они объединяются логическим оператором ИЛИ, например:
| Тираж | Цена |
| >1000 | |
| <90 |
· создайте заголовки столбцов для результирующей таблицы, скопировав имена всех столбцов основной таблицы в диапазон ячеек А20:G20.
· поместите курсор в пределы основной таблицы и выполните команду Данные/Сортировка и фильтр/Дополнительно;
· в диалоговом окне Расширенный фильтр установите флажок: Скопировать результат в другое место,задайте исходный диапазонA1:G11, диапазон условий D17:Е18 и диапазон заголовка таблицы результатовA20:G20;
· нажмите ОК (ниже представлен вид выполненного задания).
Создание таблицы с записями, в которых тираж, выпущенных книг больше 1000.
30. Самостоятельно (!) создайте расширенный фильтр для поиска в таблице Заказ информации о продукции, тираж которой превышает 10000 или цена которой меньше 90 рублей, предварительно скопировав таблицу листа Заказ на лист По_критерию2.
31. Используйте расширенный фильтр с вычисляемым критерием для получения данных о книгах, цена которых больше средней. Для этого:
· таблицу с листа Заказ скопируйте на новый лист и переименуйте его в Средняя_цена;
· на листе Средняя_цена создайте новую область критериев, поместив в ячейку I4 заголовок столбца Цена больше средней;
· в ячейку D12 поместите формулу расчета средней цены: =СРЗНАЧ(D 2: D 11);
· в ячейку I5 введите критерий поиска: = D 2>$ D $12. В ячейку будет выведено логическое значение Ложь;
· поместите курсор в пределы основной таблицы и выполните команду Данные/Сортировка и фильтр/ Дополнительно;
· введите в диалоговое окно Расширенный фильтр данные для поиска:
- установите флажок Фильтровать список на месте;
- исходный диапазонA1:G11;
- диапазон критериевI4-I5;
· нажмите ОК. Список выведен на рисунке.
Таблица «Цена больше средней»
32. Сохраните рабочую книгу в файле с именем lab3.xls x.
ЛАБОРАТОРНАЯ РАБОТА № 4
Использование сценариев модели “что-если”,
