close

Вход

Забыли?

вход по аккаунту

код для вставкиСкачать
ЛАБОРАТОРНАЯ РАБОТА №3
Решение экономических задач средствами сводных таблиц.
Использование электронных таблиц при решении задач с использованием функции
подбора параметров
Цель работы:
1. Изучение технологии создания сводных таблиц.
2. Группировка и обновление данных в сводных таблицах.
3. Консолидация диапазонов исходных таблиц.
4. Изучение автоматизированной функции подбора параметров.
Часть 1. Работа со сводными таблицами
Теоретические сведения.
Сводная таблица – инструмент обработки баз данных. Поскольку в этом случае
сразу подводятся итоги, выполняется сортировка и фильтрация списков, то сводная
таблица является мощным инструментом обработки данных.
Перед построением сводной таблицы на основе списка следует убрать из него
промежуточные итоги и фильтры.
Рассмотрим создание сводной таблицы на примере Таблицы 1 (рис. 1),
содержащей данные о продажах издательской фирмы. Список упорядочен по кварталам,
инвентарным номерам, каналам распространения, количеству проданных книг и
полученным от продажи средствам.
Рисунок 1 – Информация о продажах издательской фирмы (Таблица 1) .
При создании сводной таблицы можно использовать один из четырех типов
источников данных:
˗ список Excel;
˗ внешний источник данных;
˗ несколько диапазонов консолидации;
˗ другую сводную таблицу.
В данном примере создадим сводную таблицу из списка Excel.
Прежде всего, выделим ячейку в списке, на основе которого будем создавать
таблицу. Этот шаг не является обязательным, но позволяет экономить время. На вкладке
Вставка в группе Таблицы выберем команду Сводная таблица (рис. 2).
Рисунок 2 – Группа Таблицы вкладки Вставка
Откроется диалоговое окно создания сводных таблиц (рис. 3), включающее в себя 2
шага:
– задание типа источника данных для анализа.
– указание места размещения таблицы.
Рисунок 3 – Окно мастера сводных таблиц.
Разместите указатели в необходимых строках.
После нажатия кнопки ОК, на рабочем листе будет отображен пустой макет
сводной таблицы и список полей сводной таблицы с кнопками для каждого поля из
источника данных (рис. 4).
Рисунок 4 – Область создания сводной таблицы
Сперва необходимо работать в правой области окна Список полей сводной
таблицы: перетащите поля Инв.№ и Канал в область Название строк, поля Год и
Квартал – в область Название столбцов, поле Получено – в область Значение (рис. 5). В
каждую область можно поместить любое количество полей. Чтобы удалить поле,
перетащите его название за пределы макета.
Рисунок 5 – Создание сводной таблицы
Вы получите сводную таблицу следующего вида:
Рисунок 6 – Готовая сводная таблица
Реорганизация сводной таблицы
Чтобы реорганизовать сводную таблицу, просто нужно перетащить одну или
несколько кнопок полей. Например, чтобы переместить поле с оси столбцов на ось строк,
достаточно перетащить его кнопку из области столбцов в область строк в Списке полей
сводной таблицы. Можно также изменять порядок отображения полей по оси столбцов
или строк сводной таблицы. В нашем примере мы поместили поле Инв.№ слева от поля
Канал. А также на вкладке Конструктор в группе Макет выполнили команду
Промежуточные итоги и запросили Показывать все промежуточные итоги в нижней
части группы. Получил сводную таблицу следующего вида:
Рисунок 7 – Реорганизованная сводная таблица.
Создание сводной диаграммы
Создать сводную диаграмму можно создать не зависимо от сводной таблицы. Для
этого необходимо выполнить команду Сводная диаграмма на вкладке Создание в группе
Таблицы. Технология создания сводной диаграмм схожа с процедурой создания сводной
таблицы: после нажатия соответствующей кнопки появляться диалоговое окно
построения диаграммы, в котором необходимо указать источник данных и место
размещение диаграммы.
Сводную диаграмму также можно создать после построения сводной таблицы.
Для этого нужно выделить любую ячейку в сводной таблице – появляются контекстные
вкладки Параметры и Конструктор. На вкладке Параметры в группе Сервис есть
команда Сводная диаграмма. При нажатии на эту кнопку открывается диалоговое окно
Вставка диаграммы, где можно выбрать удовлетворяющий тип диаграммы. Диаграмма
и таблица являются объектами и изменения в одном из объектов, сразу же отражаются в
другом.
На рисунок 8 показана сводная диаграмма, связанная с Таблицей, приведенной на
рисунке 7.
Рисунок 8 – сводная диаграмма.
Можно перестроить сводную диаграмму так же, как и таблицу, перетаскивая
кнопки полей с одной оси на другую. Для удаления поля достаточно перетащить его за
пределы диаграммы.
Обновление сводной таблицы
Хотя сводная таблица связана с исходными данными, но она не обновляется
автоматически при изменении исходных данных. Чтобы обновить сводную таблицу,
следует выделить в ней ячейку и на вкладке Параметры в группе Данные выполнить
команду Обновить или использовать одноименную кнопку в контактном меню.
Группировка данных
Excel автоматически группирует элементы внутреннего поля для каждого
заголовка внешнего поля и, если требуется, создает промежуточные итоги для каждой
группы элементов внутреннего поля. Но иногда удобнее группировать элементы иным
способом, например, собрать мелкие элементы в большие группы. Excel предлагает
несколько вариантов группировки элементов.
Предположим, что после создания сводной таблицы (рис. 6), требуется сравнить
подписку (Заказ по почте), с розничной продажей, объединив каналы Внутри страны и
Международный в один сводный канал с именем Розничный. Чтобы создать такую
группу, следует:
Выделить заголовки Внутри страны и Международный в любом месте таблицы.
Выбрать команду Группа по выделенному в группе Группировать на вкладке
Параметры. Выделенные элементы будут сгруппированы в новый элемент с именем
Группа1.
Выделить ячейку с заголовком Группа1 и ввести новое имя Розничный.
Рисунок 9 – Группировка данных в сводной таблицы.
Чтобы удалить все группы и вернуть поле к первоначальному (не
сгруппированному) состоянию, нужно выделить сгруппированный элемент и выбрать
команду Разгруппировать группе Группировать на вкладке Параметры..
Внимание! Если одна сводная таблица служит источником данных для другой,
группировка в любой из них влияет на связанную таблицу.
Вычисления в сводных таблицах
К числовым полям, помещенным в область данных сводной таблицы, по
умолчанию применяется функция Сумма, а к любым нечисловым полям – функция
Количество.
Чтобы использовать другие итоговые функции, нужно в Списке полей сводной
таблицы в области Значения открыть раскрывающейся список (рис. 10) и выбрать
Параметры полей значений.
Рисунок 10 – Окно Список полей сводной таблицы
В появившемся диалоговом окне Параметры полей значений можно выбрать
требуемую функцию.
Рисунок 11– Вычисление поля сводной таблицы.
Кнопка Дополнительные вычисления позволяет использовать ряд дополнительных
вычислений. Например, можно в области данных отобразить долю от итога строки или
столбца, в котором находится ячейка, долю текущего значения от заданного элемента или
вывести накопленный итог (рис.12).
Рисунок 12 – Дополнительные вычисления в поле сводной таблицы.
По умолчанию дополнительные вычисления не производятся. Чтобы применить
дополнительную операцию нужно раскрыть список Дополнительных вычислений,
выбрать в нем нужный вариант и затем в списках Поле и Элемент, указать базовое поле и
базовый элемент, которые определяют данные, используемые при дополнительном
вычислении. В табл. 2 приведены краткие описания дополнительных вычислений.
Таблица 2
Дополнительные вычисления
Дополнительные
вычисления
Описание
Значение ячеек в области данных отображаются в виде
разности с заданным элементом базового поля
Значение ячеек в области данных отображаются в процентах
Доля (%)
от заданного элемента базового поля
Значение ячеек в области данных отображаются в виде
Приведенное отличие
разности
с
заданным
элементом
базового
поля,
(%)
нормированной на значение этого элемента
С нарастающим итогом в Значение ячеек в области данных отображаются в виде
поле
нарастающего итога
Доля от суммы по строке Значение ячеек в области данных отображаются в процентах
(%)
от итога строки
Доля от суммы по
Значение ячеек в области данных отображаются в процентах
столбцу (%)
от итога столбца
Доля от общей суммы
Значение ячеек в области данных отображаются в процентах
(%)
от общего итога
При определении значений ячеек в области данных
Индекс
используется
следующее
выражение:
((Значение
в
ячейке)*(Общий итог))/((Итог строки)*(Итог столбца))
Отличие
Диалоговое окно Параметры полей значений также можно вызвать, выделив
любую ячейку в столбце с числовыми данными и выбрав соответствующую команду в
контекстом меню или команду Параметры поля в группе Активное поле на вкладке
Параметры | Работа со сводными таблицами.
Использование вычисляемых полей и элементов
В сводную таблицу можно включать вычисляемые поля и элементы. Вычисляемое
поле – это новое поле, полученное с помощью операций над существующими полями
сводной таблицы. Вычисляемый элемент – это новый элемент в существующем поле,
полученный с помощью операций над другими элементами этого поля. При создании
вычисляемых полей и элементов допускаются арифметические операции с любыми
данными сводной таблицы, но нельзя ссылаться на данные рабочего листа, находящиеся
вне таблицы.
Чтобы создать вычисляемое поле, выделите в сводной таблице необходимую
числовую ячейку. На вкладке Параметры выберите команду Вычисления. В
открывшемся списке – команду Поля, элементы и наборы – Вычисляемое поле.
Появится окно диалога, представленное на рис. 13.
Рисунок 13 – Дополнительные вычисления в поле сводной таблицы.
В поле Имя нужно ввести имя вычисляемого поля. В поле Формула создать
формулу, используя стандартные арифметические операции и ссылки на другие поля.
Чтобы ввести в формулу имя поля, нужно выделить его в списке и нажать кнопку
Добавить поле.
Консолидация диапазонов исходных таблиц
С помощью сводных таблиц можно консолидировать данные из таблиц
расположенных на разных рабочих листах. Обязательным условием такой консолидации
является единая структура таблиц. При этом каждая таблица должна содержать данные
одного временного (или другого типа) диапазона. Например, данные о продажах
сформированы на разных листах с именами 2011 год, 2012 год, 2013 год. Для построения
сводной ведомости за несколько лет, не обязательно переносить данные на один лист,
можно воспользоваться средством консолидации мастера сводных таблиц.
Для консолидации данных можно пользоваться командой Консолидация (доступна
на вкладке Данные в группе Работа с данными).
Для консолидации нескольких диапазонов можно воспользоваться мастером
сводных таблиц и диаграмм.
Чтобы добавить мастер сводных страниц и диаграмм на панель быстрого доступа,
выполните следующие действия:
a. Щелкните стрелку рядом с панелью инструментов, а затем щелкните пункт
Дополнительные команды.
b. В группе Выбрать команды из выберите значение Все команды.
c. В списке выберите пункт Мастер сводных таблиц и диаграмм, нажмите кнопку
Добавить, а затем – кнопку ОК.
Рисунок 14 – Окно Параметры Excel
o На странице мастера Шаг 1 выберите параметр в нескольких диапазонах
консолидации, а затем нажмите кнопку Далее.
o На странице мастера Шаг 2а выберите параметр Создать поля страницы, а
затем нажмите кнопку Далее.
o На странице мастера Шаг 2б выполните следующие действия:
˗ Для каждого из диапазонов ячеек нажмите кнопку Свернуть,
, чтобы
временно скрыть диалоговое окно, выделите диапазон на рабочем листе,
нажмите кнопку Развернуть
, а затем – кнопку Добавить. Укажите
диапазон каждой таблицы, включая в него заголовки столбцов и строк,
кроме итоговых.
˗ В разделе Во-первых, укажите количество полей страницы сводной
таблицы укажите значение 0, а затем нажмите кнопку Далее.
o На странице мастера Шаг 3 укажите расположение сводного отчета и
нажмите кнопку Готово.
Суммирование значений исходных диапазонов происходит по умолчанию. Кроме
функции суммирования могут использоваться и другие функции: СЧЕТ(), СРЗНАЧ(),
МАКС(), МИН(), ПРОИЗВЕД(), ДИСП() – несмещенная дисперсия, ДИСПР() –
смещенная
дисперсия,
СТАНДОТКЛОН()
–
несмещенное
отклонение,
СТАНДОТКЛОНП() – смещенное отклонение.
Для того, чтобы выбрать другую итоговую функцию используйте вышеописанные
способы.
Задание для выполнения по части 1:
1. Создать таблицу по интересующей Вас теме, например:
˗ Учет выпуска продукции;
˗ Начисление заработной платы;
˗ Учет успеваемости студентов, и т.д.
Таблица должна быть создана за определенный период времени (год, квартал,
месяц).
2. На основе созданной таблицы разработать:
˗ сводную таблицу;
˗ диаграмму на основе сводной таблицы;
˗ группировку данных в сводной таблице.
3. Провести вычисления в сводной таблице (на основе встроенных функций (а) и
с помощью вычисляемых полей (б)).
4. Провести консолидацию данных (а) и консолидацию диапазонов (б). Для этого
разработать несколько (2-3) дополнительных таблиц, по аналогии с исходной. Каждая
таблица разрабатывается за отдельный временной период и расположена на отдельном
листе.
Часть 2. Использование функции подбора параметров
Подбор параметра является удобным и простым для понимания инструментом
решения уравнений. Он реализует алгоритм численного решения уравнения, зависящего
от одной или нескольких переменных.
Процесс решения с помощью данного метода распадается на два этапа:
1. Задание на рабочем листе ячеек, содержащих переменные решаемого уравнения
(так называемых влияющих ячеек), и ячейки, содержащей формулу уравнения (зависимой
или целевой ячейки).
2. Ввод адресов влияющих и целевой ячеек в диалоговое окно Подбор параметра
и получение ответа.
Рассмотрим пример 1: Найти решение уравнения 3x2-2у2=5
В ячейку А1 введем предполагаемое значение переменной х,
в ячейку А2 предполагаемое значение переменной у,
а в ячейку А4 – формулу:=3*А1^2-2*A2^2
Рисунок 15 – Решение уравнения с помощью функции Подбор параметров.
Далее необходимо перейти на вкладку Данные и в группе Работа с данными
нажать кнопу Анализ «что если», из предложенного списка выбрать Подбор параметра
и заполнить поля ввода в диалоговом окне.
Рисунок 16 – Окно функции Подбор параметра.
Нажав кнопку ОК получим результат выполнения процедуры подбора параметра.
Рисунок 17 – Результат выполнения функции.
По выводимому в результирующее окно Текущему значению можно судить о
степени точности найденного результата. Если нажать кнопку ОК, то содержимое
влияющих ячеек (в нашем случае это А1 и А2) будет заменено на решения уравнения.
Следует отметить, что поскольку рассматриваемое уравнение зависит от двух переменных
и имеет бесконечное множество решений, то какие числа мы получим в ячейках А1 и А2,
непосредственно зависит от их исходного содержания.
Пример 2.
Определить, при какой ежемесячной процентной ставке можно за год накопить
50000 р., внося каждый месяц сумму на 10% больше предыдущей, начав с первого
платежа 1000 р.
С помощью одной лишь финансовой функции эту задачу решить нельзя, в ней
слишком много неизвестных. Для начала необходимо смоделировать реальный поток
платежей (оформить в виде таблицы), затем найти накопленную к концу года сумму,
нарастив каждый платеж по предполагаемой ставке (так как в задаче дана ежемесячная
ставка) на соответствующее число процентных периодов (первый платеж на 12 месяцев
вперед, второй – на 11 и т. п.), и только после этого с помощью Подбора параметра найти
истинное значение процентной ставки.
Создаем таблицу, куда вносим данные: вносимая сумма, срок и процентная
ставка:
Вносимая сумма (ПС) =А2+А2*0,1
Рассчитываем будущую стоимость =БС ($D$2;В2;;-А2)
Рассчитываем сумму по БС.
Затем Данные – Работа с данными – Анализ «что если» – Подбор параметра.
Заполняем строки диалогового окна Подбор параметра:
Установить в ячейке $C$14,
Значение 50000
Изменяя значение в ячейке $D$2
Получаем:
Рисунок 18 – Таблица в числовом виде
Рисунок 19 – Таблица в формульном виде
Задания для выполнения по части 2:
С помощью приведенного примера научиться использовать функцию подбор
параметра и выполнить на отдельном листе книги Excel задания по вариантам, указанным
преподавателем.
Вариант 1
1.Какую сумму необходимо положить на депозит в начале года, чтобы к концу года
накопить 300000 р. при годовой процентной ставке 12,5%, при условии, что ежемесячно
на счет будет вноситься сумма, на 15% превышающая предыдущую.
2. Определить, при какой ежеквартальной процентной ставке можно за год
накопить 35000 р., внося каждый квартал платеж на 20% больше предыдущего, начав с
первого платежа 5000 р.
3. Какую сумму необходимо положить на депозит, чтобы к концу года накопить
120000 р. при годовой процентной ставке 14,5%, при условии, что каждые два месяца на
счет будет вноситься сумма, на 10% превышающая предыдущую.
Вариант 2
1. Какую сумму необходимо положить на депозит, чтобы к концу года накопить
90000 р. при годовой процентной ставке 11,2%, при условии, что каждый квартал на счет
будет вноситься сумма, на 10% превышающая предыдущую.
2. Определить, при какой ежемесячной процентной ставке можно за год накопить
200000 р., внося каждый месяц платеж на 20% больше предыдущего, начав с первого
платежа 5000 р.
3. Какую сумму необходимо положить на депозит, чтобы к концу года накопить
120000 р. при годовой процентной ставке 14,5%, при условии, что каждые два месяца на
счет будет вноситься сумма, на 10% превышающая предыдущую.
Требования к отчету
Отчет должен содержать:
- титульную страницу;
- цели работы;
- исходные таблицы;
- ход выполнения заданий;
- распечатки таблиц, диаграмм полученных в результате выполнения задания
части 1 и распечатку таблицы с выполненным решением для заданий части 2 – в числовом
и формульном виде;
- выводы.
1/--страниц
Пожаловаться на содержимое документа