Методические указания (стр. 4 )

4. Переименуйте Лист1 в Заказ. Для этого установите указатель на ярлык Лист1, нажмите правую кнопку мыши, в контекстном меню выберите команду Переименовать и вместо прежнего имени листа Лист1 введите новое имя Заказ.

5. Получите итоговую сумму по столбцу Сумма. Для этого установите курсор в ячейку E12 и нажмите кнопку автосуммирования. Введите сформированную формулу нажатием клавиши Enter.

6. Добавьте к существующим листам рабочей книги еще один. Для этого установите указатель на один из ярлыков, нажмите правую кнопку мыши и выберите команду Вставить. В диалоговом окне Вставка выберите значок с названием Лист и нажмите ОК.

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

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

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

Январь Таблица 2

Наименование	Количество	Стоимость
Математика	400
Физика	399
История	100
Литература	600
Химия	45
Биология	356
Философия	700
Экономика	873
Информатика	287
Психология	2087

Февраль Таблица 3

Наименование	Количество	Стоимость
Математика	260
Физика	160
История	400
Литература	32
Химия	146
Биология	176
Философия	879
Экономика	911
Информатика	1000
Психология	3000

Март Таблица 4

Наименование	Количество	Стоимость
Математика	274
Физика	406
История	167
Литература	548
Химия	98
Биология	200
Философия	654
Экономика	1007
Информатика	809
Психология	3086

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

·  открывается диалоговое окно создания сводной таблицы. В поле Таблица или диапазон вводится адрес диапазона исходных данных для построения сводной таблицы, например, Заказ!$A$1:$G$11, и щелкните по кнопке Далее;

·  в окне Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист;

·  В правой части рабочей области представлена панель (отображается при выделении любой ячейки сводной таблицы), содержащая список опций выбора полей для отображения в сводном отчете и раздел из четырех белых полей, которые отождествляются с группами, по которым распределяютсяполя сводной таблицы. Распределение полей по группам осуществляется перетаскиванием мышью. постройте сводную таблицу, перетащив поле Название с панели инструментов Сводные таблица в область полей строк, поле Квартал – в область полей столбцов, а поле Сумма – в область элементов данных рабочего листа.

19. Поменяйте местами строки и столбцы сводной таблицы. Для этого щелкните на поле Квартал и перетащите его на поле Название, а поле Название – на место поля Квартал.

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

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

25. Выход из Excel: Office/Закрыть.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сначала решим простую задачу по математике: найдём численное решение уравнение .

В ячейке B4 вводим начальное значение х, в B5 =exp(-B4)-B4. Чтобы в B4 оказался корень уравнения, в B5 должен быть 0. Вкладка Данные/Анализ «что-если»/Подбор параметра. В открывшемся диалоговом окне указываем целевую ячейку B5 (Поле Установить в ячейке), Изменяя значение ячейки B4. В поле Значение вводим 0. Всё. Задача решена:х=0,567138

1. Теперь более сложная задача. На Лист1 введите данные калькуляции цены книги, приведенные в табл. 1. Константами должны быть:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Калькуляция Таблица 1

	А	В
1	Количество экземпляров (шт.)	20 000
2	Доход (руб.)	3
3	Себестоимость реализованной продукции (руб.)	1
4	Валовая прибыль (руб.)	2
5	Проценты накладных расходов	30%
6	Затраты на зарплату (руб.)
7	Затраты на рекламу (руб.)	25 000
8	Накладные расходы (руб.)
9	Валовые издержки (руб.)	1
10
11	Прибыль от продукции (руб.)
12
13
14	Цена продукции (руб.)	150
15	Себестоимость продукции (руб.)	50

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