Создание таблицы подстановки с двумя входами
Предположим, что необходимо представить в виде таблицы конечные суммы по вкладам, если начальная сумма вклада изменяется от 10 000 до 100 000 руб. с шагом 10 000 руб., а время хранения вклада — от 12 до 60 месяцев (5 лет). Скопируйте начальную таблицу из предыдущего задания на новый лист и выполните следующие действия:
2.1. В ячейку В5 введите число 10000.
2.2. Выделите диапазон ячеек В5:В14.
2.3. Вызовите окно Прогрессия и в поле Шаг введите значение 10000, ОК. В диапазоне В5:В14 будет создана последовательность входных значений (числа от 10 000 до 100 000 с шагом 10 000).
2.4. В ячейку С4 введите число 12.
2.5. Выделите диапазон ячеек C4:G4.
2.6. Вызовите окно Прогрессия и в поле Шаг введите значение 12, ОК. В диапазоне C4:G4 будет создана другая последовательность входных значений (числа от 12 до 60 с шагом 12).
2.7. Выделите диапазон ячеек B4:G14.
2.8. Вызовите окно Таблица данных. В в поле ввода Подставлять значения по строкам в введите В1 (или сначала щелкните на этом поле, а затем — на ячейке В1).
2.9. В поле ввода Подставлять значения по столбцам щелкните на ячейке ВЗ. Щелкните на кнопке ОК. Таблица подстановки будет создана.
2.10. Выделите диапазон ячеек B5:G14 и установите для него формат Финансовый. Щелкните на кнопке ОК. Сравните полученные результаты с рисунком 3.
Рис. 3 Таблица подстановки с двумя входами
Удаление и преобразование таблиц подстановки
Внимание! В таблице подстановки нельзя менять данные в какой-либо ячейке. Чтобы устранить ошибку, необходимо создать новую таблицу подстановки, предварительно удалив ранее созданную. Для того, чтобы удалить таблицу подстановки необходимо:
3.1. Выделить диапазон ячеек, содержащих результирующие значения.
3.2. Вызвать правой кнопкой мыши меню и выбрать Очистить содержимое (или нажать клавишу Del)
Если всё же необходимо поменять какие-либо значения, то можно преобразовать результирующие значения (ячейки, содержащие формулы) в обычные числа. Для этого выделите диапазон ячеек, содержащих результирующие значения. «Зацепив» выделенную область правой кнопкой мыши, перетащите её на свободное место. В появившемся меню выберите Только значения.
 |
4. Использование таблиц подстановки для подсчета дивидендов
В ячейках В1:В4 содержатся следующие начальные данные (рис. 4):
Рис. 4. Таблица данных для подсчёта дивидендов
Общая сумма причитающихся дивидендов подсчитывается по простой формуле =В1*В2*В3.
4.1. Таблица подстановки с одним входом для подсчета дивидендов
Предположим, что необходимо представить в виде таблицы суммы дивидендов, если количество акций изменяется от 25 000 до 300 000 с шагом 25 000. Чтобы создать такую таблицу, а также отформатировать ее, выполните следующие действия.
1. В ячейку А5 введите число 25000 и заполните Прогрессией диапазон ячеек А5:А16 с шагом 25000. В диапазоне В5:В16 будет создана последовательность входных значений (числа от 25 000 до 300 000 с шагом 25 000).
2. В диапазоне ячеек А4:В16, используя окно Таблица данных (Подставлять значения по строкам) в введите В2.
3. В диапазоне ячеек В5:В16 задайте формат Финансовый.
4.2. Таблица подстановки с двумя входами дня подсчета дивидендов
Предположим, что необходимо представить в виде таблицы суммы дивидендов, если количество акций изменяется от 25 000 до 300 000 с шагом 25 000, а цена одной акции изменяется от 750 до 1000 руб. с шагом 50 руб. Чтобы создать такую таблицу, выполните следующие действия.
4.2.1. В ячейку В5 введите число 25000 и заполните диапазон ячеек В5:В16 Прогрессией с шагом 25000.
4.2.2. В ячейку С4 введите число 750.
4.2.3. Выделите диапазон ячеек С4:Н4 и заполните его прогрессией шагом 50. Будет создана последовательность входных значений (числа от 750 до 1000 с шагом 50).
4.2.4. Выделите диапазон ячеек В4:Н14 и откройте диалоговое окно Таблица данных.
4.2.5. В диалоговом окне Таблица данных в поле ввода Подставлять значения по строкам щелкните на ячейке В2, в поле ввода Подставлять значения по столбцам щелкните на ячейке В1.
4.2.6. Выделите диапазон ячеек С4:Н16 задайте формат ячеек Финансовый. Щелкните на кнопке ОК.
Возможные ошибки при работе с Таблицами подстановки.
Выделенная область недопустима. Это сообщение появляется тогда, когда Excel не может использовать выделенную область как основу для создания таблицы подстановки. Причиной этого может быть то, что выделена только одна ячейка, либо выделенная область не является прямоугольником или состоит из несмежных диапазонов. Чтобы исправить эту ошибку, закройте сообщение и правильно выделите область, где будет находиться таблица подстановки, и снова откройте окно Таблица данных.
Введенный текст не является правильной ссылкой или именем. Это сообщение появляется тогда, когда в поле ввода Подставлять значения по строкам или в поле ввода Подставлять значения по столбцам диалогового окна Таблица данных введено нечто, что программа не может распознать как ссылку на ячейку. Чаще всего такая ошибка возникает тогда, когда вы вручную вводите адрес ячейки, а не указываете ячейку, щелкая на ней. Чтобы исправить ошибку, закройте сообщение об ошибке, а затем введите в поле ввода правильный адрес ячейки. Затем снова щелкните на кнопке ОК.