Обновление данных в сводной таблице

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

Изменение структуры сводной таблицы

Если требуется значительное изменение структуры, нужно сделать следующее:

· активизировать ячейку в сводной таблице;

· щёлкнуть на кнопке Мастер сводных таблиц на панели инструментов Сводная таблица;

· в окне третьего шага Мастера внести необходимые изменения в структуру; для удаления заголовка столбца из любой области сводной таблицы достаточно перетащить заголовок за пределы макета сводной таблицы; на место удаленного можно поместить нужный заголовок столбца;

· щёлкнуть Готово.

Рис. 2.45. Панель управления сводными таблицами

 

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

Удалить какую-то область можно тоже в самой сводной таблице: перетащить заголовок удаляемой области за пределы сводной таблицы.

 

Изменение итоговой функции

По умолчанию для числовых значений в области данных сводной таблицы Excel использует функцию СУММА, а для текстовых элементов функцию "Кол-во значений". Эту функцию можно изменить:

· выделить ячейку в области данных сводной таблицы;

· нажать кнопку Поле сводной таблицы на панели инструментов Сводные таблицы;

· выбрать функцию в списке Операция.

Группировка данных

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

Создание групп элементов данных:

выделить элементы внутри одной области (например, в области Столбец), которые нужно объединить в группу;

щёлкнуть на кнопке Группировать на панели инструментов Сводная таблица.

 

Excel добавит в сводную таблицу новое поле с названием группы: Группа1, Группа2 … Группу можно переименовать: щёлкнуть мышью на имени группы и ввести новый текст.

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

 

Самостоятельная работа

· Открыть файл Функции_Фамилия. В таблицу Список_сотрудников добавить столбец «Премия к 8 Марта». Ввести в ячейке столбца 5000 для штатных сотрудниц, 2000 – женщин-совместителей. Значения 5000 и 2000 ввести в отдельные ячейки и в формулах указывать ссылки на эти ячейки.

· Определить общую сумму премий.

· Определить, при какой величине премии для штатных сотрудниц общая сумма премий не превысит 80000.

· Открыть рабочую книгу «Персональный бюджет». В таблице Персональный_бюджет подсчитать величину налога с общей суммы доходов. Схема расчета налога:

o доход до 50000 – налог 12% от дохода;

o доход до 80000 – налог 6000 + 20% от суммы, превышающей 50000;

o свыше 80000 – налог 12000 + 30% от суммы, превышающей 80000.

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

· Рассчитать ежемесячные выплаты по кредиту в 45000 руб, выданному на 5 лет, для различных значений процентной ставки.

· Рассчитать ежемесячные выплаты по кредиту в 45000 руб для различных значений процентной ставки и разных сроках выплаты кредита.

· Рассчитать варианты сроков выплаты кредита в 45000 руб для разных вариантов процентной ставки при выплате по кредиту 800 руб ежемесячно.

· Рассчитать варианты сроков выплаты кредита в 45000 руб для разных вариантов процентной ставки и значений ежемесячной выплаты.

· Создать рабочую книгу «Поиск решения».

 

 

§21. Excel. Анализ данных в таблицах

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

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

Для того чтобы метод Подбор параметра работал правильно, должны соблюдаться два условия:

· ячейка, для которой Excel будет подбирать значение, должна содержать константу;

· указанная ячейка должна влиять на формулу, для которой нужно получить требуемое значение.

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

· активизировать ячейку с формулой;

· вызвать команду Правка/Перейти;

· щелкнуть на кнопке Выделить;

· отметить вариант Влияющие ячейки.

Порядок использования метода Подбор параметра:

· активизировать ячейку с итоговой формулой;

· вызвать команду Сервис / Подбор параметра; в поле Установить в ячейке должен быть указан адрес ячейки с формулой;

· в поле Значение ввести то значение, которое должна в итоге иметь формула;

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

После нажатия кнопки ОК начнется процесс подбора значения в указанной ячейке. Excel выводит на экран окно Результат подбора параметра, в котором отражается процесс поиска значения. Если решение будет найдено, можно нажать кнопку ОК, чтобы ввести найденные значения в ячейки рабочего листа. Если нажать кнопку Отмена, то все указанные ячейки сохранят исходные значения.

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

 

Таблицы подстановки

Таблицы подстановки позволяют проводить для имеющихся данных и формул анализ типа «что, если»: «что, если цены поднять на 1%?», «что, если объем продаж возрастет на 7%?», «что, если не будет получен этот кредит?». С помощью таблиц подстановки можно быстро получить результаты вычислений для разных вариантов исходных данных, а не вводить последовательно все возможные варианты исходных данных непосредственно в формулы. Используя таблицы подстановки совместно со статистическими, финансовыми и другими функциями, можно быстро и эффективно анализировать финансовую, маркетинговую и научно-исследовательскую информацию.

Таблицы подстановок могут создаваться в двух вариантах:

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

· значения одной формулы для различных вариантов двух аргументов формулы.

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

1 вариант. В данном варианте таблица подстановок имеет следующую структуру.

 

Рис. 2.46. Структура таблицы подстановки

В область 1 (в ячейки первого столбца таблицы подстановок) следует ввести разные значения какого-либо аргумента формулы. В область 2 (в ячейки первой строки таблицы подстановок) следует ввести или сами формулы, или ссылки на ячейки, в которые формулы были предварительно занесены.

Для расчета таблицы подстановок следует:

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

· вызвать команду Данные/Таблица подстановки;

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

Пример. Расчет объема вклада, который будет накоплен за 3 года при взносе в 500 ежемесячно и при ставке 12% в год. Рабочий лист, подготовленный для решения этой задачи, будет иметь следующий вид.

Таблица 2.30

Образец исходных данных для решения задачи

	А	В
1	Период	3
2
3	Ежемесячный взнос	- 500
4
5	Процентная ставка	12%
6
7	Размер вклада	=БЗ(В5/12;В1*12;-В3;0;0)

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

Таблица 2.31

Образец исходных данных для решения задачи

	A	B	C	D	E
1	Период	3			=B7
2				9%
3	Ежемесячный взнос	- 500		9,50%
4				10%
5	Процентная ставка	12%		10,50%
6				11%
7	Размер вклада	21 538		11,50%

 

При вызове команды Данные / Таблица подстановки в строке «Подставлять значения по строкам в» следует указать адрес В5, так как в эту ячейку надо вводить разные значения процентной ставки для получения разных значений функции. Рассчитанная таблица подстановки показана ниже.

Таблица 2.32

Рассчитанная таблица подстановки

	21 538
9%	20576
9,50%	20733
10%	20891
10,50%	21050
11%	21212
11,50%	21374

 

2 вариант. В данном варианте таблица подстановки имеет следующую структуру:

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

 

 

 

 

Рис. 2.47. Структура таблицы подстановки по 2 варианту

 

Для расчета таблицы следует:

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

· вызвать команду Данные/Таблица подстановки;

· в строку Подставлять значения по столбцам в ввести адрес ячейки, содержащей второй аргумент формулы;

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

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

Таблица 2.33

Таблица подстановки с возможными значениями вклада

21 538	400	550	600	650	700	750
9%	16461	22634	24692	26749	28807	30865
9,50%	16586	22806	24879	26953	29026	31099
10%	16713	22980	25069	27158	29247	31336
10,50%	16840	23156	25261	27366	29471	31576
11%	16969	23333	25454	27575	29696	31817
11,50%	17099	23512	25649	27786	29924	32061

 

В окне команды Таблица подстановки следовало бы в строку Подставлять значения по столбцам в ввести адрес В3, а в строку Подставлять значения по строкам в – адрес В5.

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

Напомним, что при изменении каких-либо исходных данных Excel автоматически пересчитывает все формулы, имеющиеся на рабочем листе, в том числе, и таблицы подстановок. При больших размерах таблиц подстановок вычисления могут сильно замедляться. Для того, чтобы при автоматическом пересчете рабочего листа Excel не пересчитывал таблицы подстановок, следует включить опцию Автоматически, кроме таблиц на вкладке Вычисления команды Сервис/Параметры. В данном случае для пересчета всего рабочего листа вместе с таблицами подстановки следует нажать клавишу F9.

Поиск решения

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

Задачи, которые лучше всего решаются с помощью метода Поиск решения, имеют три особенности:

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

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

· имеются ограничения; ограничения могут относиться к результату, к изменяемым данным или другим величинам, влияющим на результат или изменяемые данные; например, объем используемого сырья не может превышать объем имеющегося сырья на складе; время работы сотрудников фирмы за сутки не может быть больше 24 часов и т.д.

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

Использование метода Поиск решения:

· активизировать ячейку, содержащую оптимизируемое значение;

· вызвать команду Сервис/Поиск решения;

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

· выбрать вариант значения в целевой ячейке, которое будет пытаться получить Excel: в группе Равной установить опцию Максимальному значению, Минимальному значению, Значению (указать конкретное число);

· в поле Изменяя ячейки указать ссылку на диапазон ячеек, значения в которых должны изменяться при поиске оптимального решения;

· ввести ограничения; для этого:

· нажать кнопку Добавить; раскроется окно Добавление ограничения;

· в поле Ссылка на ячейку указать ячейку, для которой задается ограничение;

· в поле, содержащем операции сравнения, выбрать операцию (<, >, >= …); вариант «цел» используется для изменяемых значений, для которых допустимы только целочисленные значения;

· в поле Ограничение ввести константу или ссылку на ячейку, содержащую ограничивающее значение; если была выбрана операция «цел», данное поле не заполняется;

· если задаваемое ограничение является последним, нажать ОК; если следует ввести следующее ограничение, нажать Добавить и повторить весь процесс;

· нажать кнопку Выполнить.

 

Пример. Предприятие выпускает телевизоры, стерео- и акустические системы, используя комплектующие (Таблица 2.34 ).

В ячейках D4:F8 указывается количество комплектующих каждого вида, используемых при выпуске одного изделия каждого вида. В ячейках B4:B8 указано количество комплектующих, имеющееся на складе.

В ячейках С4:С8 указано количество комплектующих, использованных для выпуска изделий. Эти значения описываются следующими формулами:

ячейка С4: =$D$2*D4+$E$2*E4+$F$2*F4

ячейка С5: =$D$2*D5+$E$2*E5+$F$2*F5

ячейка С6: =$D$2*D6+$E$2*E6+$F$2*F6

ячейка С7: =$D$2*D7+$E$2*E7+$F$2*F7

ячейка С8: =$D$2*D8+$E$2*E8+$F$2*F8

 

Ячейки D10:F10 содержат прибыль, получаемую при выпуске изделий каждого вида. Прибыль описывается формулами:

ячейка D10 =75*D2^$G$8

ячейка E10 =50*E2^$G$8

ячейка F10 =35*F2^$G$8

В ячейке D11 указана общая прибыль (формула =СУММ(D10:F10)).

 

Таблица 2.34

Образец ввода исходных данных

	A	B	C	D	E	F	G
1				Телевизор	Стерео	Ак .сист.
2			Количество->	0	0	0
3	Наим. изд.	Склад	Использ.
4	Шасси	450	0	1	1	0
5	Кинескоп	250	0	1	0	0	Уменьшение
6	Динамик	800	0	2	2	1	коэфф.
7	Блок пит.	450	0	1	1	0	отдачи
8	Элек. плата	600	0	2	1	1	0,9
9				Прибыль:
10			По видам изделий	0р.	0р.	0р.
11			Всего	0р.

 

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

Ограничения данной задачи:

· количество использованных комплектующих не может превышать количества, имеющегося на складе;

· количество выпущенных изделий должно быть положительным целым числом.

Для решения данной задачи в окне Поиск решения надо ввести следующие значения:

· в поле Установить целевую ячейку – ссылку D11;

· в группе Равной вариант Максимальному значению;

· в поле Изменяя ячейки – ссылку D2:F2;

· ограничения:

· C4:C8 <= B4:B8;

· D2:F2 = цел;

· D2:F2 > 0.

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

Набор ограничений, заданных для задачи, можно изменить:

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

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

· для создания нового ограничения нажать кнопку Добавить, задать ограничение, нажать ОК.