Задание № 10
Рассчитать необходимое число платежей (количество периодов выплат) для того, чтобы текущий счет вкладчика вырос до желаемого значения при заданной фиксированной ставке процентов.
Исходные данные для решения задачи следующие:
платежи, регулярно выплачиваемые вкладчиком – PL (в тыс. руб.);
размер годовой процентной ставки – FS (%);
сумма на счету в конце срока платежа – BS (в тыс. руб.).
В результате решения задачи должен быть сформирован следующий документ:
Вкладчики
|
Размер ежемесячных
выплат
|
Годовая процентная ставка
|
Сумма на счете в конце срока платежа
|
Кол-во периодов выплат (лет)
|
Петров А.А.
|
-200
|
17,6%
|
5000
|
|
Федоров К.Г.
|
-100
|
18,7%
|
3000
|
|
Суворова Е.Н.
|
-500
|
38,0%
|
7000
|
|
Архипова К.О.
|
-150
|
26,5%
|
10000
|
|
Доронин Н.Е.
|
-120
|
13,5%
|
15000
|
|
Левченко П.Р.
|
-100
|
27,2%
|
7000
|
|
Ерофеев Н.С.
|
-120
|
20,5%
|
5000
|
|
Веселов Г.М.
|
-300
|
7,9%
|
12000
|
|
Каренин Н.Г.
|
-350
|
8,2%
|
15000
|
|
Выполнить сортировку документа, обеспечив алфавитный порядок вкладчиков.
С помощью команды Расширенный фильтр сформировать новый документ со структурой исходного документа, но содержащий информацию только о тех вкладчиках, сумма на счете в конце срока платежа которых выше среднего уровня этого показателя по всем вкладчикам.
На основании исходного документа Анализ поступлений средств на счета вкладчиков коммерческого банка сформировать следующий документ:
Расчетная величина
|
Значение
|
Максимальный размер выплаты
|
|
Минимальный размер выплаты
|
|
Максимальная сумма на счете в конце срока платежа
|
|
Минимальная сумма на счете в конце срока платежа
|
|
Максимальная процентная ставка
|
|
Минимальная процентная ставка
|
|
Построить на новом рабочем листе EXCEL диаграмму, в которой показать на гистограмме изменение сумм на счетах по годам выплат с нарастающим итогом для тех вкладчиков, у которых совпадает количество лет выплат. Вывести легенду и название графика Анализ изменений сумм на счетах вкладчиков.
Выполнить фильтрацию исходного документа, оставив в нем только тех вкладчиков, годовая ставка процентов которых больше 20,0%.
Построить линейную диаграмму, на которой показать структуру сумм на счетах в конце срока платежа по вкладчикам, оставшимся в документе после фильтрации. Вывести легенду и название графика Анализ поступлений средств на счета вкладчиков.
РАБОТА №2
Обработка и анализ экономической информации в EXCEL
I. Создать таблицы для расчета заработной платы 10 работникам предприятия:
1. Создать рабочую книгу Расчет зарплаты.xls. Переименовать текущий лист рабочей книги в лист с именем «Справочник».
|
|
Справочник работников предприятия
|
|
|
|
|
|
|
|
|
|
Таб. Номер
|
Фамилия, имя, отчество
|
Разряд
|
Должность
|
Член
профсоюза
|
Дата
поступления
|
Отдел
|
Кол-во
льгот
|
%
удержания
алиментов
|
Выделить ячейки заголовка и ввести команду ФОРМАТ/Ячейки. Выбрать на вкладке «Выравнивание» и указать горизонтальное по значению и вертикальное по верхнему краю.
Ввести 10 строк с информацией с учетом следующих правил:
табельные номера (4 знака) вводить в произвольном порядке;
разряд вводить в соответствии с табл. 2;
отделы номеровать числами: 1, 2, 3;
количество льгот по налогообложению от 0 до 5;
предусмотреть 3 варианта должностей;
для членов профсоюза вводить 1, для других работников – 0;
установить пользовательский формат даты ДД.ММ.ГГГГ. Для этого выделить ячейки с датами и в контекстном меню выполнить пункт «Форматирование ячейки». На вкладке «Числа» установить в окне «Числовые форматы» (все форматы), а в окне тип изменить или ввести ДД.ММ.ГГГГ;
процент удержания алиментов указать для 3-х человек в диапазоне от 25 до 50.
Отсортировать строки таблицы по возрастанию табельного номера.
Необходимо рассчитать:
ЗП=ЗПР*ФТ/Т;
удержание в пенсионный фонд:
УПФ=ЗП*0.01;
удержание подоходного налога:
УПН=(ЗП-УПФ-МЗП*Л)*ПРПДН/100;
удержание профсоюзных взносов:
УПВЗН=ЗП*ПРВЗН/100
(только для членов профсоюза);
УАЛ=ПРАЛ*(ЗП-УПН)/100
(только для лиц, выплачивающих алименты);
ЗПВ=ЗП-УПФ-УПН-УПВЗН,
где:
ФT – фактически отработанное время (дней);
Л – количество льгот;
ЗПР – оклад работника в соответствии с его разрядом;
МЗП – минимальный размер оплаты труда;
Т – плановое количество рабочих дней в месяце;
ПРАЛ – процент удержания алиментов;
ПРВЗН – процент удержания профсоюзных взносов;
ПРПДН – процент удержания подоходного налога.
Оклад работника зависит от его квалификации (разряда). Эта зависимость представлена в Разрядной сетке (Таблица 2).
Таблица 2
Разрядная сетка
-
Разряд
|
Оклад
|
1
|
80000
|
2
|
120000
|
3
|
160000
|
4
|
200000
|
5
|
240000
|
6
|
280000
|
7
|
320000
|
В процессе решения задачи необходимо будет задавать размер минимальной зарплаты и количество рабочих дней в месяце.
На листе «Справочник» в ячейках H2:H12 создать список праздничных дней текущего года, например: 01.01.1998, 02.01.1998, 07.01.1998, 08.03.1998, 01.05.1998 и т. д. Установить для этих ячеек соответствующий формат отображения дат.
Создать таблицу для расчета зарплаты за ЯНВАРЬ месяц:
|
|
Расчет зарплаты за январь 1998 г.
|
|
|
|
|
|
|
|
|
Таб. Номер
|
Фамилия, имя, отчество
|
Оклад
|
Отработано дней
|
Начислено рублей
|
Удержано
в пенсионный фонд
|
Льготы
|
Подоходн. налог
|
Перейти на другой лист рабочей книги. Переименовать его в «январь». Ввести «шапку» таблицы.
Заполнить колонку с табельными номерами работников (10 строк) в произвольном порядке.
Ввести расчетные формулы в строку первого работника:
для определения фамилии по табельному номеру ввести в ячейку формулу поиска фамилии в справочнике: =ВПР ();
для вставки формулы установить селектор в заданную клетку и щелкнуть по кнопке «=». Выбрать в окне со списком функций: функцию просмотра и ссылки ВПР;
щелкнуть мышью по клетке с таб. номером. В поле «Искомое значение» появится соответствующий адрес;
щелкнуть мышью по полю «Табл_массив», активизируя его. Выделить мышью область клеток справочной таблицы, перейдя на лист справочника. В активизированном поле появятся соответствующие адреса справочной таблицы. Последовательно устанавливать курсор на адреса клеток и нажимать клавишу «F4» для замены адресов на абсолютные;
занести в поле «Номер_индекса_ столбца» значение 2 (для фамилии);
ввести в поле «Диапазон_просмотра» текст «ЛОЖЬ» или число 0.
Ввести внизу под расчетной таблицей справочные данные для текущего месяца:
размер минимальной оплаты труда;
процент удержания подоходного налога;
процент удержания профсоюзных взносов;
процент удержания для выплат пенсионный фонд;
текущая расчетная дата, например 01.01.1987;
плановое количество рабочих дней в месяце.
Рассчитать плановое количество рабочих дней в январе, используя стандартную функцию:
ЧИСТРАБДНИ (нач_дата;кон_дата;праздники)
Перед вводом этой функции убедиться, что она доступна, т. е. имеется в списке стандартных функций категории «Дата и время». Если этой функции в списке нет, то ввести ее в список, выполнив команду СЕРВИС/Надстройка и включить на вкладке флажок «Пакет анализа».
Ввести в качестве начальной даты текст: «01.01.1998».
Ввести в качестве конечной даты текст: «31.01.1998».
Ввести в качестве 3-го аргумента (праздники) диапазон ячеек на листе «Справочник», содержащий список праздничных дней.
После ввода и отладки всех формул 1-ой строки скопировать их вниз для каждого работника. Предусмотреть невозможность отрицательных значений подоходного налога.
Присвоить имя «Справ» диапазону ячеек справочника. Для этого выделить блок ячеек справочника и выполнить команду ВСТАВКА/Имя/Присвоить.
Добавить столбцы для вычисления сумм удержанных алиментов с учетом текущей даты и срока окончания удержания. Дополнить справочник информацией по срокам окончания удержания.
Рассчитать сумму «Удержано всего» по каждому работнику.
Рассчитать сумму «К выдаче» как разность между начисленной суммой и суммой «удержано всего». Предусмотреть невозможность выдачи отрицательных сумм.
Подсчитать итоговые суммы по всем работникам в целом и в т.ч. по каждому отделу. Для этого использовать функции СУММ и СУММЕСЛИ.
Функция консолидации данных позволяет получать результирующие таблицы, содержащие итоги для данных, расположенных в различных областях одного или нескольких рабочих листах или даже в разных книгах. Консолидируемые данные могут быть динамически связаны с исходными данными таким образом, что при изменении исходных автоматически обновляется итоговые данные.
Задание. Сформировать сводную таблицу с итогами по начислениям и удержаниям заработной платы методом консолидации. Необходимо создать новую таблицу, содержащую итоги по каждому работнику за полгода и имеющую связь с исходными данными в таблицах расчета заработной платы за период январь-июнь.
Технология формирования сводной таблицы
открыть новый лист, присвоив ему имя «Консолидация»;
выделить на листе начальную ячейку, например А5, и выполнить команду ДАННЫЕ/Консолидация;
в окне «Консолидация» выбрать в качестве функции «Сумму», ввести ссылку на консолидируемые ячейки таблицы, находящейся на листе «январь». Для этого щелкнуть в окне ссылки, затем по ярлыку листа «январь» и выделить ячейки справа и вниз, начиная с ячейки «Фамилия, имя, отчество»;
вернуться в окно консолидации и нажать кнопку «Добавить»;
повторить описанные выше действия для следующих месяцев
расчета;
после добавления ссылок на данные июня в окне консолидации включить флажки «использовать в качестве имени значения левого столбца» и «создавать связи с исходными данными»;
щелкнуть по кнопке «Ok». На экране появится таблица консолидации. Добавить к таблице отсутствующие заголовки столбцов;
используя расположенную слева схему управления структурой включить детальный (второй) уровень просмотра. Переключить на первый уровень просмотра итогов;
проверить наличие динамической связи с исходными таблицами, изменив в них первичные данные, например количество отработанных дней.
Списком называется таблица, содержащая однородные данные по строкам и столбцам. Списки можно сортировать, осуществлять в них поиск данных по различным критериям, обрабатывать с помощью фильтров и форм данных. Найденные в списке данные могут в дальнейшем подвергаться дополнительной обработке: удалению, корректировке, включению в вычисления, построению на их основе диаграмм.
1. Использование формы данных. С помощью форм можно просматривать и вводить данные в список, искать строки по содержимому ячеек, а также удалять строки из списка.
Задание 1. Использовать форму данных для просмотра и корректировки записей таблицы «Справочник работников». Форма — это наиболее удобный способ для просмотра, изменения, добавления и удаления записей списка, а также для поиска записей, удовлетворяющих заданным условиям. Перед работой с формой необходимо задать заголовки столбцов списка. Эти заголовки используются для создания полей формы.
Технология:
выделить область рабочего листа с таблицей «Справочник работников», начиная со строки, содержащей названия столбцов;
выполнить команду ДАННЫЕ/Форма и вывести данные одной строки в форму;
переместиться вперед и назад по таблице, используя кнопки «Далее» и «Назад»;
удалить последнюю строку таблицы нажатием кнопки «Удалить»;
добавить новую строку, используя кнопку «Добавить»;
закрыть диалог нажатием кнопки «Закрыть»;
используя кнопку «Критерии», найти данные работника по его фамилии, вывести в форму сведения только по членам профсоюза, просмотреть сведения о работниках, имеющих только 1 льготу.
2. Использование автофильтра. В Microsoft Excel предусмотрены различные методы анализа данных в списке. К списку можно применить фильтр, чтобы отобрать только записи, соответствующие определенным условиям. Для этого служит команда ДАННЫЕ/Автофильтр.
Задание 2. Использовать автофильтр для вывода в таблице «Справочник работников» информации только о работниках, оклад которых меньше заданного значения.
Технология:
отметить область рабочего листа с данными и с заголовками;
выполнить команду ДАННЫЕ/Фильтр/Автофильтр;
раскрыть список на поле «Оклад», выбрать пункт «Условие» и ввести выражение «меньше <значение>»;
щелкнуть кнопку «Ok»;
отменить автофильтр, для этого выполнить команду ДАННЫЕ/Фильтр и выбрать из списка пункт «все» для поля «Оклад».
Задание 3. Использовать автофильтр для вывода в таблице «Справочник работников» информации о работниках:
фамилии которых начинается на заданную букву;
1-ого и 3-его отделов.
3. Использование расширенного фильтра. Расширенный фильтр позволяет более гибко настроить фильтр для просмотра сведений из списка данных. Просмотр осуществляется на основании условий отбора. В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на возвращаемое формулой значение. Порядок составления условий отбора смотрите в разделе помощи Excel «Примеры условий отбора расширенного фильтра».
Задание 4. Использовать расширенный фильтр для получения данных о работниках 1-го отдела с окладом меньшим 5000000 руб. и не являющих ся членами профсоюза.
Технология:
скопировать имена столбцов «Отдел», «Оклад» и «Член профсоюза» в ту часть рабочего листа, которая не содержит данных для поиска, например в ячейки H1,I1,J1;
в ячейки H2,I2,J2 ввести соответственно критерии поиска (1, <50000,0);
выполнить команду меню ДАННЫЕ/Фильтр/ Расширенный фильтр;
в диалоге «Расширенный фильтр» задать область, где находятся исходные данные и область, в которой заданы критерии поиска (диапазон условий);
в группе «Обработка» указать, что фильтрация будет выполняться на месте;
щелкнуть кнопку «Ok»;
восстановить список, выполнив команду ДАННЫЕ/Фильтр/Восстановить все.
Задание 5. Использовать расширенный фильтр для получения данных о работниках 1-го и 3-его отделов, с окладом меньшим 500000 руб. и не являющихся членами профсоюза.
Использование электронных таблиц при решении задач с использованием функции «Подбор параметра»
Подбор параметра является способом прогнозирования значений с помощью анализа «что-если». При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение.
Задание 1. Подобрать цену на продукцию и величину накладных расходов для получения заданной прибыли, используя метод подбора параметров.
Технология решения:
1. На Лист 1 новой книги ввести данные калькуляции цены книги, приведенные в таблице 1. Константами должны быть: количество экземпляров, % накладных расходов, затраты на зарплату, затраты на рекламу, цена продукции и себестоимость продукции (в таблице эти значения показаны на сером фоне). Остальные данные должны быть вычислены на основании расчетных формул. Введите формулы и сверьте результаты расчета по ним с данными, приведенными в таблице.
Таблица 1
Количество экземпляров
|
20 000
|
Доход
|
120 000 000 р.
|
Себестоимость реализованной продукции
|
40 000 000 р.
|
Валовая прибыль
|
80 000 000 р.
|
% накладных расходов
|
30
|
Затраты на зарплату
|
5 000 000 р.
|
Затраты на рекламу
|
1 000 000 р.
|
Накладные расходы
|
36 000 000 р.
|
Валовые издержки
|
42 000 000 р.
|
Прибыль от продукции
|
38 000 000 р.
|
Цена продукции
|
6 000 р.
|
Себестоимость продукции
|
2 000 р.
|
2. Переименовать Лист 1 в Калькуляция и скопировать отлаженную таблицу с формулами в Лист 2. Исследуйте информацию, представленную на таблице листа Калькуляция. Увеличение прибыли может быть достигнуто за счет изменения количества экземпляров, себестоимости, валовых издержек и уровня накладных расходов.
3. Подобрать такую цену книги, чтобы прибыль от продукции составила 50 000 000 руб. Для этого:
выбрать команду меню СЕРВИС/Подбор параметра;
в диалоге «Подбор параметра» в окне «Установить в ячейке» указать целевую ячейку, содержащую значение прибыли от продукции (абсолютную ссылку), в окне «Значение» указать то значение, которое должно быть достигнуто
(50 000 000) и в окне «Изменяя ячейку» ввести абсолютную ссылку на ячейку, содержащую значение цены;
щелкнуть кнопку «Ok».
4. Ознакомиться с результатами выполнения операции подбора параметра в окне «Состояние подбора параметра» и щелкнуть кнопку «Ok» для изменения значений ячеек таблицы в соответствии с найденным решением.
5. Вернуться к исходному состоянию таблицы, используя описанный в пунктах 3, 4 способ подбора параметра.
6. Самостоятельно определить, каков должен быть показатель % накладных расходов, чтобы прибыль за продукцию составила 40 000 000 рублей.
Справка для составления расчетных формул:
Доход = Цена продукции * Количество экземпляров.
Себестоимость реализованной продукции = Себестоимость продукции* Количество кземпляров.
Валовая прибыль = Доход – Себестоимость реализованной продукции.
Накладные расходы = Доход * % накладных расходов.
Валовые издержки = Накладные расходы + Затраты на зарплату + Затраты на рекламу.
Прибыль от продукции = Доход – Себестоимость реализованной продукции.
РАБОТА № 3
Решение экономических задач средствами сводных таблиц
Цель работы:
Изучение технологии создания сводных таблиц;
Группировка и обновление данных в сводных таблицах;
Консолидация диапазонов исходных таблиц.
Пример:
Сводная таблица – инструмент обработки баз данных. Поскольку в этом случае сразу подводятся итоги, выполняется сортировка и фильтрация списков, то сводная таблица является мощным инструментом обработки данных.
Перед построением сводной таблицы на основе списка следует убрать из него промежуточные итоги и фильтры.
Рассмотрим создание сводной таблицы на примере Таблицы1, содержащей данные о продажах издательской фирмы. Список упорядочен по кварталам, инвентарным номерам, каналам распространения, количеству проданных книг и полученным от продажи средствам.
Таблица 1
При создании сводной таблицы можно использовать один из четырех типов источников данных:
Список Excel;
Внешний источник данных;
Несколько диапазонов консолидации;
Другую сводную таблицу.
В данном примере создадим сводную таблицу из списка Excel.
Прежде всего, выделим ячейку в списке, на основе которого будем создавать таблицу. Этот шаг не является обязательным, но позволяет экономить время. В меню Данные выберем команду Сводная таблица. Будет запущен мастер сводных таблиц, включающий в себя следующие шаги:
Задание типа источника данных и вида создаваемого отчета.
Указание местонахождения исходных данных.
Указание места размещения таблицы.
Первое окно диалога мастера сводных таблиц представлено на рис.1
рис. 1
На следующем шаге исходный диапазон будет определен автоматически, если перед построением была выделена только одна ячейка списка.
В последнем окне мастера следует указать место, где будет расположена сводная таблица.
После нажатия кнопки Готово, на рабочем листе будет отображен пустой макет таблицы и панель инструментов Сводные таблицы с кнопками для каждого поля из источника данных (рис. 2).
рис.2
Перетащите кнопки полей Инв.№ и Канал в область строк, кнопки Год и Квартал – в область столбцов, кнопку Получено – в область данных. В каждую область можно поместить любое количество полей. Чтобы удалить поле, перетащите его кнопку за пределы макета.
Вы получите сводную таблицу следующего вида:
Таблица 2
Реорганизация сводной таблицы
Чтобы реорганизовать сводную таблицу, просто нужно перетащить одну или несколько кнопок полей. Например, чтобы переместить поле с оси столбцов на ось строк, достаточно перетащить его кнопку из области столбцов в область строк. Можно также изменять порядок отображения полей по оси столбцов или строк сводной таблицы. В нашем примере мы поместили поле Инв.№ слева от поля Канал. Если поменяем их местами, получим сводную таблицу следующего вида:
Таблица 3
Создание сводной диаграммы
Можно создать сводную диаграмму, при построении сводной таблицы, установив соответствующий переключатель в первом окне мастера сводных таблиц. Сводную диаграмму также можно создать после построения сводной таблицы. Для этого нужно выделить любую ячейку в сводной таблице и нажать кнопку Мастер диаграмм на панели инструментов Сводные таблицы.
Диаграмма и таблица являются объектами и изменения в одном из объектов, сразу же отражаются в другом.
На рис. 3 показана сводная диаграмма, связанная с Таблицей 3.
рис. 3
Можно перестроить сводную диаграмму так же, как и таблицу, перетаскивая кнопки полей с одной оси на другую. Для удаления поля достаточно перетащить его за пределы диаграммы.
Обновление сводной таблицы
Хотя сводная таблица связана с исходными данными, но она не обновляется автоматически при изменении исходных данных. Чтобы обновить сводную таблицу, следует выделить в ней ячейку и в меню Данные выбрать команду Обновить данные, или использовать одноименную кнопку на панели инструментов Сводные таблицы.
Группировка данных
Excel автоматически группирует элементы внутреннего поля для каждого заголовка внешнего поля и, если требуется, создает промежуточные итоги для каждой группы элементов внутреннего поля. Но иногда удобнее группировать элементы иным способом, например, собрать мелкие элементы в большие группы. Excel предлагает несколько вариантов группировки элементов.
Предположим, что после создания сводной таблицы (Таблица 2), требуется сравнить подписку (Заказ по почте), с розничной продажей, объединив каналы Внутри страны и Международный в один сводный канал с именем Розничный. Чтобы создать такую группу, следует:
Выделить заголовки Внутри страны и Международный в любом месте таблицы.
Выбрать команду Группировать в подменю Группа и структура меню Данные.
Будет создано новое поле с именем Канал2 и выделенные элементы будут сгруппированы в новый элемент с именем Группа1.
Выделить любую ячейку с заголовком Группа1 и ввести новое имя Розничный.
рис. 4
Чтобы удалить все группы и вернуть поле к первоначальному (не сгруппированному) состоянию, нужно выделить сгруппированный элемент и выбрать команду Разгруппировать в подменю Группа и структура меню Данные.
Внимание! Если одна сводная таблица служит источником данных для другой, группировка в любой из них влияет на связанную таблицу.
Вычисления в сводных таблицах
К числовым полям, помещенным в область данных сводной таблицы, по умолчанию применяется функция Сумма, а к любым нечисловым полям – функция Количество.
Чтобы использовать другие итоговые функции, нужно выделить любую ячейку в области данных и нажать кнопку Параметры поля на панели инструментов Сводные таблицы. В появившемся диалоговом окне Вычисление поля сводной таблицы, показанное на рис. 5, выбрать требуемую функцию.
рис. 5
Кнопка Дополнительно, позволяет использовать ряд дополнительных вычислений. Например, можно в области данных отобразить долю от итога строки или столбца, в котором находится ячейка, долю текущего значения от заданного элемента или вывести накопленный итог (рис.6).
рис. 6
По умолчанию дополнительные вычисления не производятся. Чтобы применить дополнительную операцию нужно раскрыть список Дополнительных вычислений, выбрать в нем нужный вариант и затем в списках Поле и Элемент, указать базовое поле и базовый элемент, которые определяют данные, используемые при дополнительном вычислении. В Таблице 4 приведены краткие описания дополнительных вычислений.
Таблица 4
Дополнительные вычисления
|
Описание
|
Отличие
|
Значение ячеек в области данных отображаются в виде разности с заданным элементом базового поля
|
Доля (%)
|
Значение ячеек в области данных отображаются в процентах от заданного элемента базового поля
|
Приведенное отличие (%)
|
Значение ячеек в области данных отображаются в виде разности с заданным элементом базового поля, нормированной на значение этого элемента
|
С нарастающим итогом в поле
|
Значение ячеек в области данных отображаются в виде нарастающего итога
|
Доля от суммы по строке (%)
|
Значение ячеек в области данных отображаются в процентах от итога строки
|
Доля от суммы по столбцу (%)
|
Значение ячеек в области данных отображаются в процентах от итога столбца
|
Доля от общей суммы (%)
|
Значение ячеек в области данных отображаются в процентах от общего итога
|
Индекс
|
При определении значений ячеек в области данных используется следующее выражение: ((Значение в ячейке)*(Общий итог))/((Итог строки)*(Итог столбца))
|
Использование вычисляемых полей и элементов
В сводную таблицу можно включать вычисляемые поля и элементы. Вычисляемое поле – это новое поле, полученное с помощью операций над существующими полями сводной таблицы. Вычисляемый элемент – это новый элемент в существующем поле, полученный с помощью операций над другими элементами этого поля. При создании вычисляемых полей и элементов допускаются арифметические операции с любыми данными сводной таблицы, но нельзя ссылаться на данные рабочего листа, находящиеся вне таблицы.
Чтобы создать вычисляемое поле, выделите в сводной таблице любую ячейку. Затем выберите команду Формулы в меню Сводная таблица на панели инструментов Сводные таблицы. В подчиненном меню Формулы выберите команду Вычисляемое поле, и появится окно диалога, представленное на рис. 7.
рис. 7
В поле Имя нужно ввести имя вычисляемого поля. В поле Формула создать формулу, используя стандартные арифметические операции и ссылки на другие поля. Чтобы ввести в формулу имя поля, нужно выделить его в списке и нажать кнопку Добавить поле.
Консолидация диапазонов исходных таблиц
С помощью сводных таблиц можно консолидировать данные из таблиц расположенных на разных рабочих листах. Обязательным условием такой консолидации является единая структура таблиц. При этом каждая таблица должна содержать данные одного временного (или другого типа) диапазона. Например, данные о продажах сформированы на разных листах с именами 2003год, 2004 год, 2005 год. Для построения сводной ведомости за несколько лет, не обязательно переносить данные на один лист, можно воспользоваться средством консолидации мастера сводных таблиц.
Для этого следует: перейти на новый лист, выбрать в меню Данные/Сводная таблица. На первом шаге мастера надо установить флажок В нескольких диапазонах консолидации. На втором шаге по умолчанию переключатель Создать одно поле страницы. Далее необходимо указать диапазон каждой таблицы, включая в него заголовки столбцов и строк, кроме итоговых. Затем формируется макет итогового документа, только не отображаются имена полей консолидируемых таблиц. Можно задать любые имена полей после завершения формирования сводной таблицы. Суммирование значений исходных диапазонов происходит по умолчанию. Кроме функции суммирования могут использоваться и другие функции: СЧЕТ(), СРЗНАЧ(), МАКС(), МИН(), ПРОИЗВЕД(), ДИСП() – несмещенная дисперсия, ДИСПР() – смещенная дисперсия, СТАНДОТКЛОН() – несмещенное отклонение, СТАНДОТКЛОНП() – смещенное отклонение.
Для того, чтобы выбрать другую итоговую функцию для вычисления поля данных, нужно выделить любой элемент в этом поле, и на панели Сводные таблицы выбрать кнопку Параметры поля. В появившемся диалоговом окне кроме выбора функции, можно изменить имя поля.
Задание для выполнения:
Создать таблицу по интересующей Вас теме, например:
Учет выпуска продукции;
Начисление заработной платы;
Учет успеваемости студентов, и т.д.
Таблица должна быть создана за определенный период времени (год, квартал, месяц). На основе созданной таблицы разработать:
Сводную таблицу;
Диаграмму на основе сводной таблицы;
Группировку данных в сводной таблице;
Провести вычисления в сводной таблице (на основе встроенных функций и с помощью вычисляемых полей);
Провести консолидацию данных (для этого разработать несколько дополнительных таблиц, по аналогии с исходной. Каждая таблица разрабатывается за отдельный временной период и расположена на отдельном листе).
|
