Таблицы подстановки
Целый ряд практических задач требует варьирования итогового показателя при изменении входного параметра с определенным шагом. Сценарии в этом случае не технологичны из-за трудоемкости их формирования. Инструмент Таблица подстановки используется для расчета значений показателя, вычисляемого по некоторой формуле для вариантов значений одного или двух входных параметров. Далее показано, как по данным табл. 1.24 определить варианты премиального фонда при изменении премиального процента от 5 до 12% с шагом 1%.
Разместим все значения премиального процента как столбец со сдвигом на одну ячейку вниз и на одну ячейку влево от ячейки с формулой расчета премиального фонда.
Проверим итоговую формулу. При работе с таблицей подстановки с одной переменной формула должна ссылаться на ячейку с начальным значением изменяемого параметра. В нашем случае это ячейка, в которой расположено значение премиального процента (F2).
Выделим диапазон таблицы данных — минимальный прямоугольный блок ячеек, включающий в себя формулу и все значения входного диапазона (рис. 1.36).
На вкладке Данные нажимаем кнопку Анализ «что если» и выбираем команду Таблица данных.
Рис. 1.36. Диапазон для таблиц подстановки с одной переменной
Глава 1. Инструментальные средства обработки экономической информации
В появившемся диалоговом окне в поле Подставлять значения по строкам в надо задать местонахождение ячейки с начальным значением изменяемого параметра (в нашем примере — $F$2).
Нажимаем кнопку OK. MS Excel выведет значения формулы при каждом значении входного параметра премиального процента. При создании таблицы подстановки MS Excel вводит в каждую ячейку диапазона результатов формулу массивов .
Важно! Изменять содержимое ячеек в диапазоне результатов нельзя! Если при создании таблицы подстановок допущена ошибка, то следует выделить все результаты и нажать клавишу Delete.
Можно включить любое количество выходных формул при создании таблицы подстановки с одной переменной. Важно, чтобы все формулы использовали одну и ту же входную ячейку
Еще одна возможность, предоставляемая MS Excel, — создание таблиц, которые отражают влияние уже двух переменных на целевое значение. Рассмотрим тот же пример, только теперь попробуем получить варианты итоговой премиальной суммы при изменении как премиального процента от 5 до 12% с шагом 1%, так и нормы выполнения плана от 40000 до 90000 руб. с шагом 10000.
Эта ситуация требует другой организации данных: введем значения первого параметра (премиальный процент). Расположим этот ряд сразу под ячейкой, содержащей формулу расчета целевого показателя (рис. 1.37).
Введем значения второго параметра — нормы выполнения плана в строке выше и правее на одну ячейку от первого диапазона (справа от ячейки с формулой).
Проверим формулу для расчета премии. Она должна ссылаться на ячейки F2 и G2, может быть, через ссылки на другие формулы.
Выделим диапазон таблицы данных — минимальный прямоугольный блок, включающий в себя входные значения параметров и формулу.
На вкладке Данные нажимаем кнопку Анализ «что если» и выбираем команду Таблица данных.
В появившемся диалоговом окне в поле Подставлять значения по строкам в задаем адрес ячейки с начальным значением первого изменяемого параметра (в нашем примере премиальный процент — $F$2), в поле Подставлять значения по столбцам в указываем адрес ячейки с исходным значением второго изменяемого параметра (в нашем примере премиальный процент — $G$2).
- 00
- -r^
Рис. 1.37. Работа с таблицей подстановки с двумя переменными
Глава 1. Инструментальные средства обработки экономической информации
После нажатия OK MS Excel выведет значения целевого показателя для каждой комбинации значений входных параметров (премиального процента и процента выполнения плана). Результат представлен в табл. 1.29.
Таблица 1.29
Сформированная таблица вариантов премиального фонда
Настройка таблицы подстановки в MS Project Pro
Видео материала «Настройка таблицы подстановки в MS Project Pro»
Как правильно настраивать таблицы подстановки в MS Project Pro
Ниже приведенная информация является справочным материалом. Подробнее о данном материале и его практическом применении вы можете узнать, просмотрев видео.
Настройка таблицы подстановки в MS Project
Для настройки подстановки нажмите на кнопку «Подстановка…». В открывшемся окне «Изменение таблицы подстановки» Вы можете изменить настройки подстановки. Подстановка используется для того что бы пользователь корректно вводил значения в поле.
Для изменения формы представления нажмите кнопку «Изменить маску». Появится окно «Определение кода структуры».
- Для того чтобы описать код структуры для задач на первом уровне иерархии выберите первую строку в столбце «Последовательность«, а затем выберите тип символов из выпадающего списка: цифры, если Вы хотите использовать числовой код, прописные буквы, если Вы хотите использовать буквенный код, состоящий из прописных букв, строчные буквы, если Вы хотите использовать буквенный код состоящий из строчных букв., знаки, если Вы хотите использовать комбинацию из чисел, прописных и строчных букв.
- В столбце «Длина» укажите число символов, которые должны входить в соответствующую группу кода. Полная длина всего кода не может превышать 255 символов.
- В столбце Разделитель укажите символ, который будет использоваться для разделения групп кода. Вы можете использовать разные символы для разделения различных пар групп кода, либо вообще не использовать разделителей для кода. Для этого очистите поле Separator.
- Повторите шаги 1–3 для всех групп кода, которые Вы будете использовать.
- Для того чтобы запретить пользователям оставлять пустыми позиции кода при вводе поставьте флажок в поле «Допускаются только новые коды со значениями во всех уровнях маски».
- Для сохранения результатов работы нажмите на кнопку «OK», для отмены Ваших действий нажмите на кнопку «Отмена».
Для того чтобы определить таблицу подстановки укажите следующие значения:
- В поле «Значение» укажите значение поля.
- В поле «Описание» укажите описания значения.
В окне определите возможные значения для всех групп кода с использованием клавиш «стрелка влево» и «стрелка вправо». При этом вводимые Вами значения должны иметь тип, который Вы определили для соответствующей группы кода. По завершении работы нажмите на кнопку «Закрыть».
Для того чтобы показать определенный Вами столбец в таблице списка задач вставьте поле определенного настраиваемого столбца.
Создание и удаление поля подстановки
Создание поля подстановки не только делает данные более понятными, но и позволяет избежать ошибок данных, ограничивая значения, которые можно вводить. Поле подстановки может отображать понятное пользователю значение, связанное с другим значением в таблице исходных данных. Например, вам нужно записать заказ клиента в таблице «Заказы». Однако все сведения о клиентах отслеживаются в таблице «Клиенты». Вы можете создать поле подстановки, отображающее сведения о клиенте в элементе управления «поле со списком» или «список». Затем, когда вы выбираете клиента в этом элементе управления, в записи заказа сохраняется соответствующее значение, например значение первичного ключа клиента.
Примечание. В Access есть другие типы полей списков: поле списка значений, которое хранит только одно значение из допустимых, определенных в свойстве, и многозначное поле, в котором можно хранить до 100 значений, разделенных запятой (,). За дополнительной информацией обращайтесь к статьям Создание и удаление поля списка значений и Создание и удаление многозначного поля.
В этой статье
- Что такое поле подстановки?
- Создание поля подстановки в Конструкторе
- Сведения о связанных и отображаемых значениях
- Обновление свойств поля подстановки
- Удаление поля подстановки
- Свойства поля подстановки
Что такое поле подстановки?
Поле подстановки — это поле таблицы, значение которого получено из другой таблицы или запроса. По возможности следует создавать поле подстановки с помощью мастера подстановок, который упрощает процесс, автоматически заполняя соответствующие свойства полей и создавая нужный тип связи между таблицами.
Создание поля подстановки в Конструкторе
- Откройте таблицу в режиме Конструктор.
- В первой доступной пустой строке щелкните ячейку в столбце Имя поля и введите имя поля подстановки.
- В столбце Тип данных этой строки щелкните стрелку, а затем в раскрывающемся списке выберите пункт Мастер подстановок. Примечание. Мастер подстановок в зависимости от выбранных в нем настроек создает списки трех типов: поле подстановки, поле списка значений и многозначное поле.
- Внимательно следуйте указаниям мастера.
- На первой странице выберите вариант Объект «поле подстановки» получит значения из другой таблицы или другого запроса и нажмите кнопку Далее.
- На второй странице выберите таблицу или запрос со значениями и нажмите кнопку Далее.
- На третьей странице выберите одно или несколько полей и нажмите кнопку Далее.
- На четвертой странице выберите порядок сортировки для полей при отображении в списке и нажмите кнопку Далее.
- На пятой странице настройте ширину столбца, чтобы упростить чтение значений и нажмите кнопку Далее.
- На шестой странице при необходимости измените имя поля, установите флажок Включить проверку целостности данных, выберите вариант Каскадное удаление или Ограничить удаление и нажмите кнопку Готово. Дополнительные сведения о применении проверки целостности данных см. в статье Создание, изменение и удаление отношения.
Сведения о связанных и отображаемых значениях
Поле подстановки предназначено для замены отображаемого числа, например ИД, более понятным значением, таким как имя. Например, вместо отображения идентификатора контакта Access может показать имя контакта. Идентификатор контакта является связанным значением. Оно автоматически ищется исходной таблице или запросе и заменяется именем контакта. Имя контакта является отображаемым значением.
Важно понимать разницу между отображаемым и связанным значением поля подстановки. Отображаемое значение автоматически выводится в режиме таблицы (по умолчанию). Тем не менее сохраняется именно связанное значение, использующееся в условиях запроса, а также приложением Access при связывании таблиц.
Ниже в примере поля подстановки «КомуНазначено»:
1 Имя сотрудника является отображаемым значением
2 ИД сотрудника является связанным значением, сохраняемым в свойстве Присоединенный столбец поля подстановки.
Обновление свойств поля подстановки
Если для создания поля подстановки используется мастер подстановок, его свойства задаете вы. Чтобы изменить структуру многозначного поля, укажите свойства Подстановки.
- Откройте таблицу в Конструкторе.
- Щелкните имя поля подстановки в столбце Имя поля.
- В разделе Свойства поля откройте вкладку Подстановка.
- Задайте свойству Тип элемента управления значение Поле со списком, чтобы видеть все доступные изменения свойств, отражающие ваш выбор. Дополнительные сведения см. в разделе Свойства поля подстановки.
Удаление поля подстановки
Важно! При удалении поля подстановки, в котором содержатся данные, эти данные теряются без возможности восстановления, отменить это действие нельзя. Поэтому перед удалением каких-либо полей или других компонентов базы данных создавайте резервную копию базы данных. Также удаление поля подстановки может быть запрещено, так как применяется проверка целостности данных. Дополнительные сведения см. в статье Создание, изменение и удаление отношения.
Удаление из режима таблицы
- Откройте таблицу в режиме Режим таблицы.
- Найдите поле подстановки, щелкните правой кнопкой мыши строку заголовка и выберите команду Удалить поле.
- Нажмите кнопку Да, чтобы подтвердить удаление.
Удаление из конструктора
- Откройте таблицу в режиме Конструктор.
- Щелкните область выделения строки рядом с полем подстановки, а затем нажмите клавишу DELETE, либо щелкните правой кнопкой мыши область выделения строки и выберите команду Удалить строки.
- Нажмите кнопку Да, чтобы подтвердить удаление.
Свойства поля подстановки
Тип элемента управления
Укажите это свойство, чтобы задать отображаемые свойства:
- Поле со списком содержит список всех доступных свойств.
- Список содержит список всех доступных свойств кроме свойств Число строк списка, Ширина списка и Ограничиться списком.
- Текстовое поле не отображает свойства и преобразует поле в поле, доступное только для чтения.
Тип источника строк
Определяет, откуда брать значения для поля подстановки: из другой таблицы или запроса либо из списка указанных вами значений. В качестве источника вы также можете выбрать имена полей таблицы или запроса.
Указывает таблицу, запрос или список значений, из которых извлекаются значения для поля подстановки. Если свойство Тип источника строк имеет значение Таблица или запрос или Список полей, в этом свойстве должно быть указано имя таблицы или запроса либо инструкция SQL, представляющая запрос. Если свойство Тип источника строк имеет значение Список значений, это свойство должно содержать список значений, разделенных точками с запятой.
Указывает столбец в источнике строк, в котором содержится значение, хранящееся в столбце подстановок. Может принимать любое значение в диапазоне между 1 и числом столбцов в источнике строк.
Столбец, из которого извлекается значение, может отличаться от отображаемого столбца.
Определяет число столбцов в источнике строк, которые можно отобразить в поле подстановки. Чтобы выбрать столбцы для отображения, нужно задать ширину столбцов в свойстве Ширина столбцов.
Определяет, нужно ли отображать заголовки столбцов.
Задает ширину каждого столбца. Отображаемое значение в поле подстановки — это один или несколько столбцов, для которых в свойстве Ширина столбцов указано значение, отличное от нуля.
Если столбец не нужно отображать, например столбец «Код», укажите значение «0» для его ширины.
Число строк списка
Определяет количество строк, отображаемых в поле подстановки.
Определяет ширину элемента управления, появляющегося при отображении поля подстановки.
Определяет возможность ввода значения, отсутствующего в списке.
Разрешить несколько значений
Определяет возможность выбора нескольких значений в поле подстановки.
Нельзя изменить значение этого свойства с «Да» на «Нет».
Разрешить изменение списка значений
Определяет возможность редактирования элементов поля подстановки, основанного на списке значений. Если это свойство имеет значение Да, при щелчке правой кнопкой мыши поля подстановки, основанного на списке значений из одного столбца, в меню появится команда Изменение элементов списка. Если поле подстановки содержит несколько столбцов, это свойство игнорируется.
Форма изменения элементов списка
Указывает существующую форму, используемую для изменения элементов списка в поле подстановки, основанном на таблице или запросе.
Только значения источника строк
Показывает только значения, соответствующие текущему источнику строк, если свойство Разрешить несколько значений имеет значение Да.
2.4 Пример выполнения задания «Использование таблицы подстановки в ms Excel»
Таблица подстановки позволяет проводить анализ изменения результата при произвольном диапазоне исходных данных. Создание таблицы подстановки может оказаться очень удобным, если существует множество данных и требуется получить результат по какой-то формуле.
На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные.
Таблицу подстановки можно использовать для:
– изменения одного исходного значения, просматривая при этом результаты одной или нескольких формул;
– изменения двух исходных значений, просматривая результат только одной формулы.
1. Использование Таблицы подстановки с одной изменяющейся переменной и несколькими формулами.
Перед созданием Таблицы подстановки необходимо подготовить рабочий лист, на котором будет решаться анализируемая задача. Например, рассчитаем сопротивление платиновой проволоки в зависимости от температуры. Отобразим лист с подготовленными данными (рис. 2.7). В ячейку D6 введена формула, по которой вычисляется сопротивление.
Рис. 2.7. Подготовка исходных данных
Для того, чтобы создать таблицу подстановки с одной переменной, следует сформировать таблицу таким образом, чтобы введенные значения были расположены либо в столбце, либо в строке. Формулы, используемые в таблицах подстановки с одной переменной, должны ссылаться на ячейку ввода, т.е. ячейку, в которую подставляются значения из таблицы данных.
Затем в отдельный столбец или отдельную строку следует ввести список значений, которые будут подставляться в ячейку ввода (рис. 2.8). Если значения расположены в столбце, следует ввести формулу или адрес формулы в ячейку, расположенную на одну строку выше и на одну ячейку правее первого значения.
Рис. 2.8. Подготовка изменяемого диапазона и расчетных формул
для использования одномерной Таблицы подстановки
Затем следует выделить диапазон ячеек, содержащий формулу и значения подстановки (в нашем случае это диапазон B11:C18) и выбрать команду на вкладке «Данные» – «Таблица данных». В поле «Подставлять значения по столбцам в» ввести ссылку для значений подстановки в столбце. Excel будет подставлять значения температур в эту ячейку, просчитает формулу, расположенную в заголовке выделенного диапазона, и поместит под ней список результатов. Таблица подстановки заполнится значениями сопротивления, соответствующими каждой температуре (рис. 2.9).
Рис. 2.9. Заполнение таблицы подстановки
2. Использование Таблицы подстановки с двумя изменяющимися переменными.
Рассмотри применение Таблицы подстановки с двумя изменяющимися переменными на следующем примере: пусть требуется подобрать параметры тока и напряжения для платиновой проволоки. В качестве параметров изменяются напряжение и ток. Сначала следует подготовить рабочий лист с условиями поставленной задачи (рис. 2.10).
Рис. 2.10. Подготовка исходных данных
Таблицы подстановки с двумя переменными используют одну формулу с двумя наборами значений. Формула должна ссылаться на две ячейки ввода. Для того, чтобы создать таблицу подстановки для данного примера в ячейку листа B10 введем формулу, которая ссылается на две ячейки ввода и аналогична формуле в ячейке D8, которая применялась для расчета сопротивления. Ниже формулы введем значения первой переменной (параметр тока), правее формулы в строку введем значения второй переменной (напряжение) (рис. 2.11).
Рис. 2.11. Создание таблицы подстановки
Затем следует выделить диапазон ячеек, содержащий формулу и оба набора данных подстановки, выбрать команду Данные — Таблица подстановки. В поле Подставлять значения по столбцам в ввести ссылку для значений подстановки в столбце ($D$4), в поле Подставлять значения по строкам, соответственно, ссылку для значений подстановки в строке ($D$5). Получившаяся Таблица подстановки представлена ниже (рис. 2.12).
Рис. 2.12. Заполненная таблица подстановки
Примечание: После построения таблицы подстановки нельзя редактировать отдельно взятую формулу внутри таблицы. Значения данных внутри таблицы можно изменить, меняя значения исходных данных в левом столбце и верхней строке.
Мастер подстановок. Данный Мастер представляет собой средство для создания формул, основанных на функциях ИНДЕКС() и ПОИСКПОЗ().
Перед использованием Мастера подстановок следует предусмотреть следующее:
- Расположение исходных данных на рабочем листе.
- Расположение возвращаемых функций данных и данных для поиска (их нахождение в соответствующих колонках).
- Строка для начала поиска.
- Место на рабочем листе для помещения результата.