Методические указания по выполнению лабораторных работ в среде табличного процессора EXCEL 2003 (стр. 4 )

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

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

6. Создайте структуру построенной таблицы для скрытия детальных числовых данных. Для этого установите курсор внутри таблицы и выполните команду Данные/Группа и Структура/Создание структуры. На экране структуры таблицы щелкните кнопку «–», чтобы скрыть столбцы с числами, а затем кнопку «+» для показа скрытой информации.

7. Удалите структуру, выполнив команду Данные/Группа и Структура/Удалить структуру.

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

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. Постройте сводную таблицу, информирующую о сумме изданной литературы по каждому наименованию отдельно. Для этого:

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

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

·  в окне шага 1 выберите источник, откуда будут поступать данные для построения таблицы – «В списке или в базе данных Microsoft Excel», а также вид создаваемого отчета – «Сводная таблица» и щелкните по кнопке Далее;

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

·  в окне шага 3 установите переключатель Новый лист и нажмите кнопку Готово;

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

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

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

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

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

23. Откройте сводную таблицу, щелкните на поле Название и перетащите его из области полей строк в область полей страниц. Затем на панели инструментов Сводные таблицы из списка Сводная таблица выберите команду Отобразить страницы. В появившемся окне Отображение страниц нажмите ОК. Обратите внимание на изменения в сводной таблице и на появление новых листов с наименованиями учебников в рабочей книге Excel.

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

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

·  сделайте текущей ячейку поля Цена;

·  нажмите кнопку инструментального меню Сортировка по возрастанию.

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

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

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

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

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

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

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

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

·  нажмите кнопку инструментального меню Сортировка по возрастанию;

·  выполните команду Данные/Итоги;

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

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

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

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

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

·  выполните команду Данные/Итоги;

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

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

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

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

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

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

·  выделите область рабочего листа, где находятся данные вместе с названием столбцов;

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

·  переместитесь вперед и назад по таблице, используя кнопки Далее и Назад;

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

·  добавьте новую строку, используя кнопку Добавить;

·  закройте диалог нажатием кнопки Закрыть.

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

·  выделите область рабочего листа, где находятся данные вместе с названием столбцов;

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

·  в диалоговом окне Заказ щелкните по кнопке Критерии;

·  в поле тираж введите критерий поиска (>10000);

·  переместитесь вперед и назад по найденным записям, используя кнопки Далее и Назад;

·  закройте диалог нажатием кнопки Закрыть.

32. Самостоятельно выполните:

·  сортировку данных таблицы Заказ по возрастанию значений поля Номер;

·  поиск в таблице Заказ информации о книгах, выпущенных в первом квартале тиражом менее 20000.

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

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

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

·  раскрыть список на поле Цена, выбрать пункт Условие и ввести выражение «больше 85»;

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

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

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

·  создайте область критериев (условий), скопировав имена столбцов Цена, Квартал и Тираж в ячейки I1,J1 и K1 соответственно;

·  создайте область выходных данных (результатов), скопировав имена всех столбцов таблицы в диапазон ячеек M1:P1;

·  в ячейки I2,J2,K2 введите критерии поиска (<80, Кв1, >10000);

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

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

·  в диалоге Расширенный фильтр установите флажок Скопировать результат в другое место, задайте исходный диапазон ($А$1:$H$11), диапазон условий ($I$1:$K$2) и диапазон результатов ($M$1:$P$1);

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

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

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

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

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

·  выполните расширенный фильтр, поместив результат в диапазон $M$7:$P$7.

37. Самостоятельно с помощью расширенного фильтра получите данные о книге с максимальной суммой выпуска.

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

39. Для выхода из Excel выберите из меню команду Файл/Выход.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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. Исследуйте информацию, представленную на таблице листа Калькуляция. Увеличение прибыли может быть достигнуто за счет изменения количества экземпляров, себестоимости, валовых издержек и уровня накладных расходов.