Меню

Как делать таблицы подстановки



Создание таблиц подстановки

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

Excel позволяет создавать таблицы подстановки следующих типов:

— таблицы подстановки с одной переменой и с одной или несколькими формулами;

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

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

— процентной ставки на сумму возврата вклада;

— процентной ставки на коэффициент увеличения вклада;

— величины процентной ставки и изменения срока вклада на сумму возврата вклада.

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

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

2. Сделайте активным лист 3 и присвойте ему имя Таблицы подстановки.

3. Перед началом таблицы вставьте две пустые строки.

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

5. В качестве переменной используйте процентную ставку (ячейку ввода В5), которая может принимать значение от 3 до 10%. Введите эти значения в столбец D согласно приведенному ниже образцу (рис. 68).

6. В ячейку, находящуюся правее и выше первого введенного в столбец D значения, введите формулу для вычисления суммы возврата вклада: = В3*В6.

7. Выделите диапазон ячеек D2:E10, содержащий подставляемые значения процентных ставок и формулу для расчета суммы возврата вклада.

A B C D E
Процент Сумма возврата
=B3*B6
Размер вклада 5 000руб. 3 %
Срок вклада 4 %
Процентная ставка 5 % 5 %
Коэфф. увеличения 1,28 6 %
Сумма возврата 6 381руб. 7 %
8 %
9 %
10 %

Рис. 68. Данные для таблицы подстановки с одной переменной.

8. Введите команду ДАННЫЕ→Таблица подстановки и в диалоговом окне в поле Подставлять значения по строкам в: в введите абсолютный адрес ячейки ввода (с процентной ставкой) — $B$5.

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

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

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

3. Добавьте в таблицу подстановки в ячейку F2 вторую формулу для расчета коэффициента увеличения вклада: =(1+B5)^B4.

4. Выделите необходимый диапазон ячеек (D2:F10), введите команду ДАННЫЕ→Таблица подстановки и в диалоговом окне введите абсолютный адрес ячейки ввода $B$5.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Создание таблиц подстановки

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

Таблицы подстановки используются в следующих случаях:

· Имеется один набор данных для одной ячейки (одной переменной), на которую ссылаются несколько формул. В этом случае создается так называемая таблица подстановки с одним входом.

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

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

Команда Данные — Таблица подстановки позволяет создавать таблицы подстановки.

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

общий макет таблицы подстановки с одним входом.

Таблицу можно расположить в любом месте рабочего листа. Левый столбец содержит различные значения входного параметра. Верхняя строка содержит формулы или ссылки на ячейки с формулами, по которым рассчитывается результат. Можно использовать любое количество ссылок на формулы (или только одну). Верхняя левая ячейка таблицы не используется. Excel вычисляет значения, которые получаются в результате подстановки каждого из исходных значений во входную ячейку, и помещает результат в соответствующий столбец (в ячейку, которая находится под ячейкой с соответствующей формулой или ссылкой на формулу).

Читайте также:  Рельеф и климат африки таблица

Пример. Расчет ипотечной ссуды (файл Таблица_подстановки)

На рабочем листе расположены данные

ПЛТ – стандартная финансовая функция расчета процентов по кредиту

Источник

Создание и удаление поля подстановки

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

Примечание. В Access есть другие типы полей списков: поле списка значений, которое хранит только одно значение из допустимых, определенных в свойстве, и многозначное поле, в котором можно хранить до 100 значений, разделенных запятой (,). За дополнительной информацией обращайтесь к статьям Создание и удаление поля списка значений и Создание и удаление многозначного поля.

В этой статье

Что такое поле подстановки?

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

Создание поля подстановки в Конструкторе

Откройте таблицу в режиме Конструктор.

В первой доступной пустой строке щелкните ячейку в столбце Имя поля и введите имя поля подстановки.

В столбце Тип данных этой строки щелкните стрелку, а затем в раскрывающемся списке выберите пункт Мастер подстановок.

Примечание. Мастер подстановок в зависимости от выбранных в нем настроек создает списки трех типов: поле подстановки, поле списка значений и многозначное поле.

Внимательно следуйте указаниям мастера.

На первой странице выберите вариант Объект «поле подстановки» получит значения из другой таблицы или другого запроса и нажмите кнопку Далее.

На второй странице выберите таблицу или запрос со значениями и нажмите кнопку Далее.

На третьей странице выберите одно или несколько полей и нажмите кнопку Далее.

На четвертой странице выберите порядок сортировки для полей при отображении в списке и нажмите кнопку Далее.

На пятой странице настройте ширину столбца, чтобы упростить чтение значений и нажмите кнопку Далее.

На шестой странице при необходимости измените имя поля, установите флажок Включить проверку целостности данных, выберите вариант Каскадное удаление или Ограничить удаление и нажмите кнопку Готово.

Дополнительные сведения о применении проверки целостности данных см. в статье Создание, изменение и удаление отношения.

Сведения о связанных и отображаемых значениях

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

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

Ниже в примере поля подстановки «КомуНазначено»:

1 Имя сотрудника является отображаемым значением

2 ИД сотрудника является связанным значением, сохраняемым в свойстве Присоединенный столбец поля подстановки.

Обновление свойств поля подстановки

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

Откройте таблицу в Конструкторе.

Щелкните имя поля подстановки в столбце Имя поля.

В разделе Свойства поля откройте вкладку Подстановка.

Задайте свойству Тип элемента управления значение Поле со списком, чтобы видеть все доступные изменения свойств, отражающие ваш выбор. Дополнительные сведения см. в разделе Свойства поля подстановки.

Удаление поля подстановки

Важно! При удалении поля подстановки, в котором содержатся данные, эти данные теряются без возможности восстановления, отменить это действие нельзя. Поэтому перед удалением каких-либо полей или других компонентов базы данных создавайте резервную копию базы данных. Также удаление поля подстановки может быть запрещено, так как применяется проверка целостности данных. Дополнительные сведения см. в статье Создание, изменение и удаление отношения.

Удаление из режима таблицы

Откройте таблицу в режиме Режим таблицы.

Найдите поле подстановки, щелкните правой кнопкой мыши строку заголовка и выберите команду Удалить поле.

Нажмите кнопку Да, чтобы подтвердить удаление.

Удаление из конструктора

Откройте таблицу в режиме Конструктор.

Щелкните область выделения строки рядом с полем подстановки, а затем нажмите клавишу DELETE, либо щелкните правой кнопкой мыши область выделения строки и выберите команду Удалить строки.

Нажмите кнопку Да, чтобы подтвердить удаление.

Свойства поля подстановки

Тип элемента управления

Укажите это свойство, чтобы задать отображаемые свойства:

Поле со списком содержит список всех доступных свойств.

Список содержит список всех доступных свойств кроме свойств Число строк списка, Ширина списка и Ограничиться списком.

Текстовое поле не отображает свойства и преобразует поле в поле, доступное только для чтения.

Тип источника строк

Читайте также:  Таблица пропорций веса и роста для мужчин

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

Указывает таблицу, запрос или список значений, из которых извлекаются значения для поля подстановки. Если свойство Тип источника строк имеет значение Таблица или запрос или Список полей, в этом свойстве должно быть указано имя таблицы или запроса либо инструкция SQL, представляющая запрос. Если свойство Тип источника строк имеет значение Список значений, это свойство должно содержать список значений, разделенных точками с запятой.

Указывает столбец в источнике строк, в котором содержится значение, хранящееся в столбце подстановок. Может принимать любое значение в диапазоне между 1 и числом столбцов в источнике строк.

Столбец, из которого извлекается значение, может отличаться от отображаемого столбца.

Определяет число столбцов в источнике строк, которые можно отобразить в поле подстановки. Чтобы выбрать столбцы для отображения, нужно задать ширину столбцов в свойстве Ширина столбцов.

Определяет, нужно ли отображать заголовки столбцов.

Задает ширину каждого столбца. Отображаемое значение в поле подстановки — это один или несколько столбцов, для которых в свойстве Ширина столбцов указано значение, отличное от нуля.

Если столбец не нужно отображать, например столбец «Код», укажите значение «0» для его ширины.

Число строк списка

Определяет количество строк, отображаемых в поле подстановки.

Определяет ширину элемента управления, появляющегося при отображении поля подстановки.

Определяет возможность ввода значения, отсутствующего в списке.

Разрешить несколько значений

Определяет возможность выбора нескольких значений в поле подстановки.

Нельзя изменить значение этого свойства с «Да» на «Нет».

Разрешить изменение списка значений

Определяет возможность редактирования элементов поля подстановки, основанного на списке значений. Если это свойство имеет значение Да, при щелчке правой кнопкой мыши поля подстановки, основанного на списке значений из одного столбца, в меню появится команда Изменение элементов списка. Если поле подстановки содержит несколько столбцов, это свойство игнорируется.

Форма изменения элементов списка

Указывает существующую форму, используемую для изменения элементов списка в поле подстановки, основанном на таблице или запросе.

Только значения источника строк

Показывает только значения, соответствующие текущему источнику строк, если свойство Разрешить несколько значений имеет значение Да.

Источник

Как делать таблицы подстановки

На этом шаге мы рассмотрим создание таблиц подстановки.

При работе с моделью «что-если» в определенный момент времени можно использовать только один сценарий (только один набор данных). Но что если необходимо сравнить результаты нескольких сценариев? Вот несколько вариантов решения подобной проблемы:

  • Отпечатать несколько копий рабочего листа с разными сценариями на каждом.
  • Скопировать модель на другие листы и расположить их окна на экране так, чтобы были видны все сценарии.
  • Вручную создать формулу, которая бы подытоживала значения из ключевых ячеек для каждого сценария.
  • Использовать команду Excel Данные | Таблица подстановки для автоматического создания итоговой таблицы.

Команда Данные | Таблица подстановки позволяет создавать удобные таблицы подстановки, которые позволяют проводить вычисления по формулам для одного из приведенных ниже случаев:

  • Имеется один набор данных для одной ячейки (одной переменной), на которую ссылаются несколько формул. В этом случае создается так называемая таблица подстановки с одним входом .
  • Имеются два набора данных для двух ячеек (две переменные), на которые ссылается одна формула. Создаваемая в этом случае таблица называется таблицей подстановки с двумя входами .

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

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

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

Рис.1. Общий макет таблицы подстановки

Таблицу можно расположить в любом месте рабочего листа. Левый столбец содержит различные значения входного параметра. Верхняя строка содержит формулы или ссылки на ячейки с формулами, по которым рассчитывается результат. Можно использовать любое количество ссылок на формулы (или только одну). Верхняя левая ячейка таблицы не используется. Excel вычисляет значения, которые получаются в результате подстановки каждого из исходных значений во входную ячейку, и помещает результат в соответствующий столбец (в ячейку, которая находится под ячейкой с соответствующей формулой или ссылкой на формулу).

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

Рис.2. Пример рабочего листа

Рассмотрим пример создания таблицы подстановки, в которой бы отражались значения, рассчитанные по формулам, находящимся в ячейках Размер ссуды, Месячная плата, Общая сумма, Общая сумма комиссионных , при изменении ставок от 7% до 9% с шагом 0,25%. На рисунке 3 показана заготовка таблицы подстановки для описанного примера. Строка 2 состоит из ссылок на соответствующие ячейки с формулами.

Рис.3. Подготовка к созданию таблицы подстановки с одним входом

Чтобы создать таблицу подстановки, выделите диапазон ячеек (для рассматриваемого примера G2:K11 ), а затем выберите команду Данные | Таблица подстановки . Появится диалоговое окно, показанное на рисунке 4.

Рис.4. Диалоговое окно Таблица подстановки

Вам необходимо определить ячейку листа, в которую должны подставляться исходные данные. Поскольку все исходные данные находятся в столбце, то адрес следует поместить в поле Подставлять значения по строкам в (для нашего примера следует ввести $D$7 ). Щелкните на кнопке OK , и Excel заполнит таблицу соответствующими результатами (рис. 5).

Рис.5. Результат анализа, проведенного с помощью таблицы подстановки с одним входом

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

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

Таблица подстановки с двумя входами позволяет отобразить на экране результаты расчетов при изменении двух входных параметров. Макет для этого типа таблицы показан на рисунке 6.

Рис.6. Макет таблицы подстановки с двумя входами

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

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

Рис.7. Пример расчета чистой прибыли после проведения рекламной акции

В этой модели используются две ячейки для ввода информации: количество разосланных рекламных материалов и предполагаемый процент ответов. Область результата включает следующее:

  • Стоимость печатных материалов . Стоимость печати одного рекламного буклета. Цена изменяется в зависимости от количества: 0,20 — если количество экземпляров не превышает 200000; 0,15 — от 200001 до 300000 экземпляров; 0,10 — если больше 300000. Стоимость отпечатаннх материалов (в зависимости от их количества) определяется по фомуле:
    =ЕСЛИ(Разослано_материалов .
  • Почтовые расходы . Их стоимость фиксирована и составляет 0,32 за одно почтовое отправление.
  • Число респондентов . Количество ответов, которое предполагается получить. Оно определяется в зависимости от процента предполагаемых ответов и количества разосланных материалов. Формула для этой ячейки следующая:
    =Процент_ответевших*Разослано_материалов .
  • Доход на одного респондента . Это фиксированное значение. Компании известно, что за каждый заказ она получит прибыль 22.
  • Суммарный доход . Суммарный доход вычисляется по простой формуле, в которой величина дохода, полученного от одного заказа, умножается на количество заказов:
    =Доход_на_одного_респондента*Число_респондентов .
  • Суммарные расходы . По формуле, находящейся в этой ячейке, вычисляются суммарные расходы на рекламу, в которую входит стоимость печатных материалов и почтовых услуг:
    =(Стоимость_печатных_материалов+Почтовые_расходы)*Разослано_материалов .
  • Чистая прибыль . Определяется как разность суммарных доходов и суммарных расходов.

Создадим таблицу подстановки с двумя входами, которая позволит вычислить чистую прибыль при разных комбинациях количества разосланных рекламных материалов и предполагаемого процента полученных ответов. Расположите таблицу в диапазоне G4:O14 . Чтобы создать таблицу подстановки, выделите указанный диапазон и выполните команду Данные | Таблица подстановки . В поле Подставлять значения по столбцам в — введите имя ячейки Процент_ответивших , а в поле Подставлять значения по строкам в — имя ячейки Разослано_материалов . На рисунке 8 показан результат выполнения выше описанных действий.

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

По данным таблицы подстановки с двумя входами можно построить трехмерные диаграммы (рис. 9).

Рис.9. Пример трехмерной диаграммы

Файл с данным примером можно взять здесь.

На следующем шаге мы рассмотрим анализ данных с помощью средства Диспетчер сценариев .

Источник

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

Для анализа данных при выборе оптимального варианта финансового решения зачастую применяются Таблицы подстановки в Excel.Они позволяют проводить анализ изменения результата при произвольном диапазоне исходных данных. На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные. Данный пример подходит для версий программы Microsoft Office Excel версий 2007, 2010 и 2013.

Таблицы подстановки данных можно использовать для

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

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

На конкретном примере

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

Порядок работы

  • Запустите MicrosoftExcel и создайте новую электронную книгу.
  • Создайте таблицу ежемесячных выплат по займу и платежей по процентам по образцу.

=ПЛТ ($В$4/12;$В$3*12;$В$2). Ежемесячная выплата составит 10178,42 р.

  • Расчет платежей по процентам происходит с помощью функции ПРОЦПЛАТ (). В ячейку D6 введите формулу:

=ПРОЦПЛАТ ($B$4;$D$5;$D$3;$D$2). Платежи по процентам составят 1350 р.

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

Источник

Adblock
detector