Создание таблицы подстановки с двумя переменными

Таблица подстановки с двумя переменными позволяет решить еще одну задачу - определить влияние величины процентной став­ки и изменения срока вклада на сумму возврата вклада.

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

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

Скопируйте из первой таблицы во второй экземпляр таблицы диапазон ячеек D3:D10.

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

В строку правее формулы введите значения подстановки для второй переменной - срока вклада - от 5 до 10 лет.

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

Введите команду ДАННЫЕ, Таблица подстановки.

В диалоговом окне Таблица подстановки введите абсолют­ные адреса двух ячеек ввода.

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

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

Проанализируйте полученные результаты.

Сохраните документ.

В третьем экземпляре исходной таблицы создайте еще одну таб­лицу подстановки с двумя переменными: процентной ставкой и раз­мером вклада, подставляя значения размера вклада от 4 000 до 8 000 р.

Сохраните работу.

Табличный процессор MS Excel

ПРАКТИЧЕСКАЯ РАБОТА 6

Подведение итогов

Задание 1. Автоматическое подведение общих и промежуточных итогов. Работа со структурой таблицы

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

Cоздайте в этой книге 2 новых рабочих листа.

Присвойте новым рабочим листам имена Итоги и Вычисления.

Скопируйте рабочий лист Исходный на лист Итоги, введите в ячейку G5 нужную формулу и выполните вычисления в столбце Объем продаж.

На листе Итоги приведены данные по реализации лицензионно­го программного обеспечения двумя продавцами фирмы "Юпитер".

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

Для решения этой задачи удобно использовать средства Excel для автоматического подведения общих и промежуточных итогов -команду ДАННЫЕ, Итоги.

Выполните следующую последовательность действий:

1) отсортируйте данные в таблице по столбцу Продавец;

2) выделите всю таблицу - диапазон ячеек A4:G22 и введите команду меню ДАННЫЕ, Итоги;

3) в диалоговом окне Промежуточные итоги из списка При ка­ждом изменении в: выберите столбец Продавец;

4) из списка Операция выберите функцию Сумма;

5) в списке Добавить итоги по: выберите столбцы, содержащие значения, по которым необходимо подвести итоги, - Количество и Объем продаж;

6) щелкните по кнопке ОК.

Обратите внимание на появление в таблице новых строк с ито­говыми данными.

Выделите итоговые данные полужирным курсивом и разме­ром шрифта 12 пт..

Сохраните работу.

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

Внесите какие-либо изменения в ячейки столбца Количество продаж и проанализируйте результат.

При подведении итогов автоматически создается структу­ра таблицы.

На листе Итоги символы структуры отображаются слева от номеров строк.

Номера уровней структуры

Скрыть группу

Показать группу

Щелчок по номеру уровня позволяет скрыть детали уровней с большими номерами и показать детали этого уровня и всех уров­ней с меньшими номерами.

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

Линейки уровней показывают, какие группы строк или столб­цов входят в каждый уровень структуры.

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

Сохраните работу.

Задание 2. Выполнение вычислений и построение диаграмм на основе итоговых данных

На основе таблицы с итоговыми данными можно выпол­нять дополнительные вычисления.

Скопируйте рабочий лист Итогина лист Вычисления.

Определите эффективность работы каждого продавца, его вклад в общий объем продаж.

Для этого в итоговую таблицу на листе Вычислениядобавьте заголовок нового столбца Н - Доля, % и введите формулу для рас­чета процентной доли каждого продавца в общем объеме продаж.

Отформатируйте таблицу с итогами уровней 1 и 2 рамками и заливкой.

Откройте все уровни структуры щелчком по номеру уровня 3 и скопируйте формулу во все ячейки столбца Доля, %, используя маркер автозаполнения.

Сохраните работу.

Проверьте правильность расчетов, используя функцию Авто­вычисления. Для этого выполните следующие действия:

• выделите необходимый диапазон ячеек таблицы;

• щелкните правой кнопкой мыши по полю для автовычисле­ний в Строке состояния Excel;

• выберите из меню функцию Сумма;

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

На основе таблицы с итоговыми данными можно построить диа­грамму, использующую только видимые элементы структуры.

Скройте в таблице все детальные данные, щелкнув по номеру уровня 2, и постройте объемную столбчатую диаграмму на основе данных столбцов В и Н.

Диаграмма может обновляться, скрывать или показывать дан­ные в соответствии с тем, скрываются или показываются с помо­щью символов структуры отдельные детали таблицы.

Откройте в таблице одну из групп детальных данных и обрати­те внимание на изменение диаграммы.

Сохраните работу.