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

Не торопитесь удалять созданную сводную таблицу, если она выглядит не так, как нужно. Чтобы исправить положение, достаточно изменить структуру таблицы. Просто перетащите кнопки Полей либо в другую область этой таблицы, либо за ее пределы (рис. 38).

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

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

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

• Чтобы изменить выполняемую операцию, например вместо суммы значений элементов данных вычислить среднее или дисперсию, щелкните на кнопке с названием операции в разделе Значения на панели задач Список полей сводной таблицы. В открывшемся меню выберите элемент Параметры полей значений, в диалоговом окне Параметры поля значений укажите новую функцию и щелкните на кнопке ОК.
  Рис.38. Чтобы перегруппировать данные, просто перетащите кнопки полей в другую область сводной таблицы

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

Использование таблиц подстановки — наиболее простой способ выполнения анализа «что-если». Данная функция программы Excel позволяет создавать таблицы для вычисления результатов одной или нескольких формул для нескольких значений одной (или двух) переменных. В этой книге рассматриваются только таблицы данных с одной переменной.

Проще всего объяснить принципы построения таблицы данных на примере расчета ожидаемого объема продаж на следующий год (рис. 39). Будем считать, что известен объем продаж текущего года и годовой прирост. Чтобы создать таблицу расчета ожидаемого объема продаж, если годовой прирост варьируется в некоторых пределах, выполните ряд действий.

Рис. 39. Простая таблица для рассчета

1. В пустые ячейки листа (или в ячейки отдельного листа) введите значения, которые хотите подставить в ячейку ввода. На рис. 39 исходные значения введены в диапазон ячеек В8:В17.

2. Введите формулы или ссылки на формулы, которые будут вычисляться для различных исходных значений. В таблице на рис. 39 использована ссылка на ячейку, которая содержит формулу вычисления ожидаемой прибыли: =В5.

3. Выделите диапазон ячеек, содержащий значения подстановки (столбец В8:В17) и ссылку на формулу (С7).

4. 4 Перейдите во вкладку Данные ленты инструментов.

5. Щелкните на кнопке Анализ "что-если" и в открывшемся меню выберите элемент Таблица данных. Откроется диалоговое окно Таблица данных (рис. 40).

Рис. 40. Чтобы создать таблицу данных, заполните поля этого диалогового окна

6. Укажите ячейку листа, используемую в качестве ячейки ввода. В примере ячейкой ввода является ВЗ (годовой прирост). Значения, подставляемые в ячейку ввода, содержатся в диапазоне В8:В17, поэтому в поле Подставлять значения по столбцам в необходимо ввести формулу =ВЗ, а поле Подставлять значения по строкам в оставить незаполненным. Если значения, подставляемые в ячейку ввода, находятся в верхней строке (а формулы — в столбце слева от формируемой таблицы), необходимо заполнить поле Подставлять значения по строкам в, а поле Подставлять значения по столбцам в оставить пустым.

7. Щелкните на кнопке ОК. Программа выполнит все вычисления и заполнит созданную таблицу (рис. 41).

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

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

Функция Подбор параметра позволяет определить значение, которое нужно подставить в ячейку ввода для получения необходимого результата. Например, определить размер ссуды, исходя из указанной величины ежемесячных выплат (учитывая, что процентная ставка и срок погашения ссуды фиксированы), можно с помощью функции ПЛТ. А как подобрать такой размер ссуды, чтобы ежемесячные выплаты не превышали определенного значения?

Рис. 41. Анализ «что-если» готов

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

1. Откройте рабочий лист, в котором нужное значение вычисляется по формуле.

2. Перейдите во вкладку Данные ленты инструментов.

3. Щелкните на кнопке Анализ «что-если» и в открывшемся меню выберите элемент Подбор параметра. Откроется диалоговое окно Подбор параметра (рис. 42).

4. Заполните диалоговое окно. В поле Установить в ячейке введите ссылку на ячейку, содержащую нужную формулу. В поле Значение введите искомый результат, а в поле Изменяя значение ячейки введите ссылку на ячейку, значение которой требуется подобрать. Формула в ячейке, указанной в поле Установить в ячейке, должна ссылаться на изменяемую ячейку.

5. Щелкните на кнопке ОК. Программа отобразит найденное решение в диалогозом окне Результаты подбора параметра и заменит им исходное значение указанной ячейки (рис. 43).

Рис 42. Подбор параметра

Рис. 43. Функция подбора параметра предлагает решение

Чтобы заменить исходное значение найденным решением, щелкните на кнопке ОК.