Таблица 5

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

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

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 поместите формулу расчета средней цены: =СРЗНАЧ(D2:D11);

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

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

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

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

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

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

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

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

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

ЛАБОРАТОРНАЯ РАБОТА № 4

Использование сценариев модели “что-если”,

средств подбора параметра и поиска решения

для анализа данных

Цель лабораторной работы

Лабораторная работа служит для получения практических навыков использования таких полезных средств EXCEL, как построение сценариев, подбор параметра и поиск решения для анализа данных.

Основные сведения об использовании сценариев,

подборе параметра и поиске решения

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

Данный процесс может быть рассмотрен в обратном порядке – нахождение исходных данных, которые, будучи подставленными в формулы, дают необходимые значения в ячейке результата.

Для проведения такого анализа «что-если» наоборот EXCEL имеет два средства: подбор параметра и поиск решения.

Подбор параметра определяет значение одной входной ячейки, которое требуется для получения желаемого результата в ячейке результата.

Поиск решения определяет значения в нескольких входных ячейках, которые требуются для получения желаемого результата.

Содержание лабораторной работы

Выполнение данной лабораторной работы состоит из двух самостоятельных, не связанных между собой разделов:

• исследование информации, представленной в табл. 1 «Калькуляция» на основе формульных зависимостей с использованием средства Подбор параметра и последующим построением сценариев с помощью Диспетчера сценариев;

• использование средства Поиск решения для решения двух задач линейного программирования.

Подбор параметра

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

Примечание. Средство подбора параметров поддерживает только одно входное значение переменной.

1. На Лист1 введите данные калькуляции цены книги, приведенные в табл. 1.

Таблица 1

Калькуляция

Константами должны быть:

количество экземпляров;

проценты накладных расходов;

затраты на зарплату;

затраты на рекламу;

цена продукции;

себестоимость продукции

(в таблице эти значения показаны на темно-сером фоне жирным шрифтом). Остальные данные должны быть представлены в виде расчетных формул:

Доход = Цена продукции ^x Количество экземпляров;

Себестоимость реализованной продукции = Себестоимость продукции ^x Количество экземпляров;

Валовая прибыль = Доход – Себестоимость реализованной продукции;

Накладные расходы = Доход ^x Проценты накладных расходов;

Валовые издержки = Накладные расходы + Затраты на зарплату + Затраты на рекламу;

Прибыль от продукции = Валовая прибыль – Валовые издержки.

Введите формулы и сверьте результаты расчета по ним с данными, приведенными в табл. 1.

2. Переименуйте Лист1 в Калькуляция и скопируйте отлаженную таблицу с формулами в Лист2. Исследуйте информацию, представленную на таблице листа Калькуляция. Увеличение прибыли может быть достигнуто за счет изменения количества экземпляров, себестоимости, валовых издержек и уровня накладных расходов.

3. Подберите такую цену книги, чтобы прибыль от продукции составила 1500 000 руб.

Для этого:

• на вкладке Данные в группе Работа с данными выберите команду Анализ “что-если”, а затем выберите в списке пункт Подбор параметра;

• в диалоговом окне Подбор параметра в поле Установить в ячейке с помощью мыши укажите целевую ячейку, содержащую значение прибыли от продукции ($B$11), в поле Значение укажите то значение, которое должно быть достигнуто (1 500 000) и в поле Изменяя ячейку введите абсолютную ссылку на ячейку, содержащую значение цены ($B$14);

• нажмите ОК.

4. Ознакомьтесь с результатами выполнения операции подбора параметра в окне Результат подбора параметра и в таблице 1. Нажмите OK.

5. Вернитесь к исходному состоянию таблицы, используя описанный в пунктах 3, 4 способ подбора параметра.

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

Построение сценариев

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

7. По данным рабочего листа Лист2 постройте сценарии решения задачи расчета значения прибыли за продукцию путем изменения параметров «Цена» и «Проценты накладных расходов».

8. Для построения каждого сценария необходимо:

• на вкладке Данные в группе Работа с данными выбрать команду Анализ “что-если”, а затем выбрать в списке пункт Диспетчер сценариев;

• в диалоговом окне Диспетчер сценариев нажать кнопку Добавить;

• в окне Добавления сценария ввести в поле Название сценария имя (например, «Изменение цены 1»);

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

• нажать OK;

• в окне Значения ячеек сценария ввести значение изменяемого параметра (например, для цены ввести 175);

• нажать OK.

9. Повторите указанные в пункте 8 действия для добавления в список сценариев еще трех сценариев расчета прибыли, изменяя параметры «Цена» (200) и «Проценты накладных расходов» (20% и 40%);