Добавление вычисляемого поля

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

Например, в банковской базе данных надо увеличить вклады на 15%, а также сравнить планируемые вклады с текущими. Это можно сделать с помощью вычисляемого поля. Вычисляемое поле должно размещаться в области данных сводной таблицы (его нельзя использовать в областях страницы, строки или столбца). Для создания вычисляемого поля следует выполнить следующие действия:

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

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

Пример 3. Анализ данных, полученных в результате опроса общественного мнения. Файл «Опрос», содержит часть необработанных данных в форме, которая обычно используется для исследования опроса общественного мнения. В каждой записи представлен ответ одного опрашиваемого.

Сводная таблица «Количество по полю город» показывает сколько лиц опрошено.

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

Анализ и распределение данных

В процессе обработки данных, как правило, возникают вопросы «Что будет если размер взносов уменьшится на … рублей?», или «Что будет, если изменится процентная ставка?». Для решения таких задач используются аналитические методы, которые позволяют определить как должна измениться исследуемая величина, одна или несколько, чтобы результатом вычислений было требуемое значение.

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

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

Задача. Пусть фирма наняла работника для выполнения разового задания. В срочном договоре на выполнения задания установлена определенная сумма (5000 рублей). Сколько следует начислить зарплату, чтобы после всех положенных отчислений осталась именно эта сумма.

Отчисления: подоходный налог, мед. страх, пенсионный фонд.

A	B	C	D	E	F
Фамилия	Зарплата	Под. Налог	Мед. Страх.	Пенс. фонд	на руки
Пушкин		= B1*12%	= B1*2%	= B1*1%	=B1-C1-D1-E1

Порядок действий

· вкладка «Данные» - группа «Работа с данными» -команда «Анализ если – что» - команда «Подбор параметра»

· откроется окно диалога «Подбор параметра»

· указываем ячейку значение в которой нужно изменить и само значение (F2 - 5000)

· указываем ячейку, значение которой нужно изменить для достижения необходимого результата формулы. Изменяемая ячейка должна содержать только число. (B2)

Будет получен результат (B2)

Задача. Расчет зарплаты сотрудников хозрасчетной поликлиники. Известен фонд заработной платы (200000 рублей). Зарплата сотрудника рассчитывается по формуле:
базовая зарплата*коэффициент + надбавка(0,5 или 1 или 2 базы)

Следует найти базовую зарплату, так чтобы зарплата всех сотрудников не превосходила фонд.

должность	кол-во ставок	коэф - т	надбавка	зарплата ед.	зарплата по ставкам
Главврач							база
Врач1
врач2
врач3
ст.медсестра
мед.сестра			0,5
санитарка
зав аптекой			0,5
бухгалтер
сторож