Создание таблицы подстановки с двумя переменными
Таблица подстановки с двумя переменными позволяет решить еще одну задачу - определить влияние величины процентной ставки и изменения срока вклада на сумму возврата вклада.
При создании таблицы подстановки с помощью двух переменных формула для определения значений должна находиться в месте пересечения столбца и строки с подставляемыми значениями.
Используемая формула должна ссылаться на две различные ячейки ввода.
Скопируйте из первой таблицы во второй экземпляр таблицы диапазон ячеек D3:D10.
Введите формулу расчета суммы возврата вклада в ячейку, находящуюся выше первого подставляемого значения процентной ставки.
В строку правее формулы введите значения подстановки для второй переменной - срока вклада - от 5 до 10 лет.
Выделите диапазон ячеек, содержащий формулу и оба набора значений для подстановки.
Введите команду ДАННЫЕ →Таблица подстановки.
В диалоговом окне Таблица подстановки введите абсолютные адреса двух ячеек ввода.
Ссылку на ячейку ввода для значений подстановки, расположенных в столбце, указывают в поле Подставлять значения по строкам.
Ссылку на ячейку ввода для значений подстановки, расположенных в строке, указывают в поле Подставлять значения по столбцам.
Проанализируйте полученные результаты.
Сохраните документ.
В третьем экземпляре исходной таблицы создайте еще одну таблицу подстановки с двумя переменными: процентной ставкой и размером вклада, подставляя значения размера вклада от 4 000 до 8 000 р.
Сохраните работу.
Задание 4.Автоматическое подведение общих и промежуточных итогов. Работа со структурой таблицы
Скопируйте из папки преподавателя в свою папку файл Итоги.xls и создайте в нем 2 новых рабочих листа.
Присвойте новым рабочим листам имена Итогии Вычисления.
Скопируйте рабочий лист Исходныйна лист Итоги,введите в ячейку G5 нужную формулу и выполните вычисления в столбце Объем продаж.
На листе Итогиприведены данные по реализации лицензионного программного обеспечения двумя продавцами фирмы "Юпитер". Требуется вычислить суммарное количество программ, реализованных каждым продавцом, объем продаж для каждого продавца и подвести итоги в целом по фирме.
Для решения этой задачи удобно использовать средства Excel для автоматического подведения общих и промежуточных итогов – команду ДАННЫЕ →Итоги.
Выполните следующую последовательность действий:
1) отсортируйте данные в таблице по столбцу Продавец;
2) выделите всю таблицу - диапазон ячеек A4:G22и введите команду меню ДАННЫЕ →Итоги;
3) в диалоговом окне Промежуточные итогииз списка При каждом изменении в:выберите столбец Продавец;
4) из списка Операциявыберите функцию Сумма;
5) в списке Добавить итоги по:выберите столбцы, содержащие значения, по которым необходимо подвести итоги. - Количествои Объем продаж;
6) щелкните по кнопке ОК.
Обратите внимание на появление в таблице новых строк с итоговыми данными.
Выделите итоговые данные полужирным курсивом и размером шрифта 12 пт.
Сохраните работу.
Общие и промежуточные итоги динамически связаны с исходными (детальными) данными, и все изменения в исходных данных будут автоматически отражаться в итоговых результатах.
Внесите какие-либо изменения в ячейки столбца Количество продажи проанализируйте результат.
При подведении итогов автоматически создается структура таблицы.
На листе Итоги символы структурыотображаются слева от номеров строк (рис. 4.17).
Рис. 4.17. Символы структуры
На листе Итогиимеется три уровня структуры - 1, 2 и 3.
Щелчок по номеру уровня позволяет скрыть детали уровней с большими номерами и показать детали этого уровня и всех уровней с меньшими номерами.
Для того чтобы показать все данные в таблице, нужно щелкнуть по самому большому номеру. Чтобы скрыть детальные данные всех уровней структуры, нужно щелкнуть по номеру 1. Данные уровня 1 скрыты быть не могут.
Линейки уровней показывают, какие группы строк или столбцов входят в каждый уровень структуры.
Исследуйте возможности работы со структурой таблицы на листе Итоги.Скройте все детальные данные и отобразите только результаты подведения итогов.
Сохраните работу.
Задание 5.Выполнение вычислений и построение диаграмм на основе итоговых данных
На основе таблицы с итоговыми данными можно выполнять дополнительные вычисления.
Скопируйте рабочий лист Итогина лист Вычисления.
Определите эффективность работы каждого продавца, его вклад в общий объем продаж.
Для этого в итоговую таблицу на листе Вычислениядобавьте заголовок нового столбца Н- Доля, %и введите формулу для расчета процентной доли каждого продавца в общем объеме продаж.
Отформатируйте таблицу с итогами уровней 1 и 2 рамками и заливкой.
Откройте все уровни структуры щелчком по номеру уровня 3 и скопируйте формулу во все ячейки столбца Доля, %, используя маркер автозаполнения.
Сохраните работу.
Проверьте правильность расчетов, используя функцию Автовычисления. Дляэтого выполните следующие действия:
• выделите необходимый диапазон ячеек таблицы;
• щелкните правой кнопкой мыши по полю для автовычислений в Строке состояния Excel;
• выберите из меню функцию Сумма;
• просмотрите результат в строке состояния в поле для автовычислений и сравните их с данными в таблице.
На основе таблицы с итоговыми данными можно построить диаграмму, использующую только видимые элементы структуры.
Скройте в таблице все детальные данные, щелкнув по номеру уровня 2, и постройте объемную столбчатую диаграмму на основе данных столбцов В и Н.
Диаграмма может обновляться, скрывать или показывать данные в соответствии с тем, скрываются или показываются спомощью символов структуры отдельные детали таблицы.
Откройте в таблице одну из групп детальных данных и обратите внимание на изменение диаграммы.
Сохраните работу.