Создание таблицы подстановки с двумя переменными
Таблица подстановки с двумя переменными позволяет решить еще одну задачу - определить влияние величины процентной ставки и изменения срока вклада на сумму возврата вклада.
При создании таблицы подстановки с помощью двух переменных формула для определения значений должна находиться в месте пересечения столбца и строки с подставляемыми значениями.
Используемая формула должна ссылаться на две различные ячейки ввода.
Скопируйте из первой таблицы во второй экземпляр таблицы диапазон ячеек 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 пт..
Сохраните работу.
Общие и промежуточные итоги динамически связаны с исходными (детальными) данными, и все изменения в исходных данных будут автоматически отражаться в итоговых результатах.
Внесите какие-либо изменения в ячейки столбца Количество продаж и проанализируйте результат.
При подведении итогов автоматически создается структура таблицы.
На листе Итоги символы структуры отображаются слева от номеров строк.
Номера уровней структуры
|
![Создание таблицы подстановки с двумя переменными Создание таблицы подстановки с двумя переменными - student2.ru](/images/demografiya/sozdanie-tablicy-podstanovki-s-dvumya-peremennymi-160927-4.gif)
|
![Создание таблицы подстановки с двумя переменными Создание таблицы подстановки с двумя переменными - student2.ru](/images/demografiya/sozdanie-tablicy-podstanovki-s-dvumya-peremennymi-160927-5.jpg)
Щелчок по номеру уровня позволяет скрыть детали уровней с большими номерами и показать детали этого уровня и всех уровней с меньшими номерами.
Для того чтобы показать все данные в таблице, нужно щелкнуть по самому большому номеру. Чтобы скрыть детальные данные всех уровней структуры, нужно щелкнуть по номеру 1. Данные уровня 1 скрыты быть не могут.
Линейки уровней показывают, какие группы строк или столбцов входят в каждый уровень структуры.
Исследуйте возможности работы со структурой таблицы на листе Итоги. Скройте все детальные данные и отобразите только результаты подведения итогов.
Сохраните работу.
Задание 2. Выполнение вычислений и построение диаграмм на основе итоговых данных
На основе таблицы с итоговыми данными можно выполнять дополнительные вычисления.
Скопируйте рабочий лист Итогина лист Вычисления.
Определите эффективность работы каждого продавца, его вклад в общий объем продаж.
Для этого в итоговую таблицу на листе Вычислениядобавьте заголовок нового столбца Н - Доля, % и введите формулу для расчета процентной доли каждого продавца в общем объеме продаж.
Отформатируйте таблицу с итогами уровней 1 и 2 рамками и заливкой.
Откройте все уровни структуры щелчком по номеру уровня 3 и скопируйте формулу во все ячейки столбца Доля, %, используя маркер автозаполнения.
Сохраните работу.
Проверьте правильность расчетов, используя функцию Автовычисления. Для этого выполните следующие действия:
• выделите необходимый диапазон ячеек таблицы;
• щелкните правой кнопкой мыши по полю для автовычислений в Строке состояния Excel;
• выберите из меню функцию Сумма;
• просмотрите результат в строке состояния в поле для автовычислений и сравните их с данными в таблице.
На основе таблицы с итоговыми данными можно построить диаграмму, использующую только видимые элементы структуры.
Скройте в таблице все детальные данные, щелкнув по номеру уровня 2, и постройте объемную столбчатую диаграмму на основе данных столбцов В и Н.
Диаграмма может обновляться, скрывать или показывать данные в соответствии с тем, скрываются или показываются с помощью символов структуры отдельные детали таблицы.
Откройте в таблице одну из групп детальных данных и обратите внимание на изменение диаграммы.
Сохраните работу.