Эксель подстановка данных в зависимости от введенного значения

Обновлено: 25.06.2022

Помогите, пожалуйста с формулой. Нужна подстановка значений из листа с данными в лист расчета. Ранее делали расчет на 3 и 4 м, все нормально. появилась необходимость расширения данных и что-то не клеится(

Помогите, пожалуйста с формулой. Нужна подстановка значений из листа с данными в лист расчета. Ранее делали расчет на 3 и 4 м, все нормально. появилась необходимость расширения данных и что-то не клеится( Иваныч77

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Здравствуйте! 2 недели пытаюсь решить задачу, которую сам перед собой поставил, но так до сих пор и не получилось.

Есть документ, который состоит из двух листов, на одном листе поля ввода данных (титульный лист) на втором - некая база данных о клиентах.

Задача стоит следующая, сделать так, чтобы при вводе в определенные ячейки на выбор (их три: телефон, гос. номер, паспорт) excel искал это значение в "базе" и если бы находил, то во все остальные ячейки подставлял данные от нужного клиента

В целом, у меня получилось сделать это по одному значению, если добавить второе или третье, то появляется ошибка зациклинности.

Логику я понимаю, а вот с формулой какая-то беда.

Нужно найти (я делал через индекс и поискпоз) номер телефона и если нашел, то вставить в нужные ячейки нужные данные, если телефон не нашел, то искать по паспорту, если всё норм, то вставить, если нет, то искать по гос. номеру, если норм, то вставить, если нет, то оставить пустым.

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

Надеюсь объяснил нормально)

Файл примера вложил, темно-синим помечены ячейки, по которым ведётся поиск

Здравствуйте! 2 недели пытаюсь решить задачу, которую сам перед собой поставил, но так до сих пор и не получилось.

Есть документ, который состоит из двух листов, на одном листе поля ввода данных (титульный лист) на втором - некая база данных о клиентах.

Задача стоит следующая, сделать так, чтобы при вводе в определенные ячейки на выбор (их три: телефон, гос. номер, паспорт) excel искал это значение в "базе" и если бы находил, то во все остальные ячейки подставлял данные от нужного клиента

В целом, у меня получилось сделать это по одному значению, если добавить второе или третье, то появляется ошибка зациклинности.

Логику я понимаю, а вот с формулой какая-то беда.

Нужно найти (я делал через индекс и поискпоз) номер телефона и если нашел, то вставить в нужные ячейки нужные данные, если телефон не нашел, то искать по паспорту, если всё норм, то вставить, если нет, то искать по гос. номеру, если норм, то вставить, если нет, то оставить пустым.

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

Надеюсь объяснил нормально)

Файл примера вложил, темно-синим помечены ячейки, по которым ведётся поиск Narahon

Есть документ, который состоит из двух листов, на одном листе поля ввода данных (титульный лист) на втором - некая база данных о клиентах.

Задача стоит следующая, сделать так, чтобы при вводе в определенные ячейки на выбор (их три: телефон, гос. номер, паспорт) excel искал это значение в "базе" и если бы находил, то во все остальные ячейки подставлял данные от нужного клиента

В целом, у меня получилось сделать это по одному значению, если добавить второе или третье, то появляется ошибка зациклинности.

Логику я понимаю, а вот с формулой какая-то беда.

Нужно найти (я делал через индекс и поискпоз) номер телефона и если нашел, то вставить в нужные ячейки нужные данные, если телефон не нашел, то искать по паспорту, если всё норм, то вставить, если нет, то искать по гос. номеру, если норм, то вставить, если нет, то оставить пустым.

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

Надеюсь объяснил нормально)

Файл примера вложил, темно-синим помечены ячейки, по которым ведётся поиск Автор - Narahon
Дата добавления - 05.06.2018 в 16:54

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

Довольно часто требуется рассчитать итоговый результат для различных комбинаций вводных данных. Таким образом пользователь сможет оценить все возможные варианты действий, отобрать те, результат взаимодействия которых его удовлетворяет, и, наконец, выбрать самый оптимальный вариант. В Excel для выполнения данной задачи существует специальный инструмент – «Таблица данных» («Таблица подстановки»). Давайте узнаем, как им пользоваться для выполнения указанных выше сценариев.

Использование таблицы данных

Инструмент «Таблица данных» предназначен для того, чтобы рассчитывать результат при различных вариациях одной или двух определенных переменных. После расчета все возможные варианты предстанут в виде таблицы, которую называют матрицей факторного анализа. «Таблица данных» относится к группе инструментов «Анализ «что если»», которая размещена на ленте во вкладке «Данные» в блоке «Работа с данными». До версии Excel 2007 этот инструмент носил наименование «Таблица подстановки», что даже более точно отражало его суть, чем нынешнее название.

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

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

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

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

Способ 1: применение инструмента с одной переменной

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

Итак, в настоящее время нам предлагаются следующие условия кредитования:

  • Срок кредитования – 3 года (36 месяцев);
  • Сумма займа – 900000 рублей;
  • Процентная ставка – 12,5% годовых.

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

Нужно рассчитать, какова будет сумма ежемесячного платежа, включающего в себя погашение тела кредита и выплат по процентам. Для этого в Экселе имеется оператор ПЛТ.

Вводные данные для расчета ежемесячного платежа в Microsoft Excel

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

«Ставка» — аргумент, определяющий процентную ставку кредитных выплат. Показатель выставляется за период. У нас период выплат равен месяцу. Поэтому годовую ставку в 12,5% следует разбить на число месяцев в году, то есть, 12.

«Кпер» — аргумент, определяющий численность периодов за весь срок предоставления кредита. В нашем примере период равен одному месяцу, а срок кредитования составляет 3 года или 36 месяцев. Таким образом, количество периодов будет рано 36.

«ПС» — аргумент, определяющий приведенную стоимость кредита, то есть, это размер тела кредита на момент его выдачи. В нашем случае этот показатель равен 900000 рублей.

«БС» — аргумент, указывающий на величину тела кредита на момент его полной выплаты. Естественно, что данный показатель будет равен нулю. Этот аргумент не является обязательным параметром. Если его пропустить, то подразумевается, что он равен числу «0».

«Тип» — также необязательный аргумент. Он сообщает о том, когда именно будет проводиться платеж: в начале периода (параметр – «1») или в конце периода (параметр – «0»). Как мы помним, у нас платеж проводится в конце календарного месяца, то есть, величина этого аргумента будет равна «0». Но, учитывая то, что этот показатель не является обязательным, и по умолчанию, если его не использовать, значение и так подразумевается равным «0», то в указанном примере его вообще можно не применять.

    Итак, приступаем к расчету. Выделяем ячейку на листе, куда будет выводиться расчетное значение. Клацаем по кнопке «Вставить функцию».

Переход в Мастер функций в Microsoft Excel

Переход в окно аргументов функции ПЛТ в Microsoft Excel

Ставим курсор в поле «Ставка», после чего кликаем по ячейке на листе со значением годовой процентной ставки. Как видим, в поле тут же отображаются её координаты. Но, как мы помним, нам нужна месячная ставка, а поэтому производим деление полученного результата на 12 (/12).

В поле «Кпер» таким же образом вносим координаты ячеек срока кредита. В этом случае делить ничего не надо.

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

Окно аргументов функции ПЛТ в Microsoft Excel

Результат вычисления функции ПЛТ в Microsoft Excel

Подготовленная таблица в Microsoft Excel

Расчет общей суммы кредита в Microsoft Excel


Расчет суммы процентов в Microsoft Excel

Запуск инструмента Таблица данных в Microsoft Excel

Окно инструмента Таблица данных в Microsoft Excel

Таблица заполненна данными в Microsoft Excel

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

Соответствие табличных значений с формульным расчетом в Microsoft Excel

Проанализировав данный табличный массив, следует сказать, что, как видим, только при ставке 9,5% годовых получается приемлемый для нас уровень ежемесячного платежа (менее 29000 рублей).

Приемлимый уровень ежемесячного платежа в Microsoft Excel

Способ 2: использование инструмента с двумя переменными

Конечно, отыскать в настоящее время банки, которые выдают кредит под 9,5% годовых, очень сложно, если вообще реально. Поэтому посмотрим, какие варианты существуют вложиться в приемлемый уровень ежемесячного платежа при различных комбинациях других переменных: величины тела займа и срока кредитования. При этом процентную ставку оставим неизменной (12,5%). В решении данной задачи нам поможет инструмент «Таблица данных» с использованием двух переменных.

    Чертим новый табличный массив. Теперь в наименованиях столбцов будет указываться срок кредитования (от 2 до 6 лет в месяцах с шагом в один год), а в строках — величина тела кредита (от 850000 до 950000 рублей с шагом 10000 рублей). При этом обязательным условием является то, чтобы ячейка, в которой находится формула расчета (в нашем случае ПЛТ), располагалась на границе наименований строк и столбцов. Без выполнения данного условия инструмент при использовании двух переменных работать не будет.

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

Запуск инструмента Таблица данных в программе Microsoft Excel

Окно инструмента Таблица данных в программе Microsoft Excel

Таблица данных заполнена в Microsoft Excel

Выделение таблицы в Microsoft Excel

Переход к условному форматированию в Microsoft Excel

Окно настроек условного фрматирования в Microsoft Excel

Ввыделение ячеек цветом соответствующих условию в Microsoft Excel

Проанализировав табличный массив, можно сделать некоторые выводы. Как видим, при существующем сроке кредитования (36 месяцев), чтобы вложиться в выше обозначенную сумму ежемесячного платежа, нам нужно взять заём не превышающий 860000,00 рублей, то есть, на 40000 меньше первоначально запланированного.

Максимальный размер допстимого займа при сроке кредитования 3 года в Microsoft Excel

Если же мы все-таки намерены брать кредит размером 900000 рублей, то срок кредитования должен составлять 4 года (48 месяцев). Только в таком случае размер ежемесячного платежа не превысит установленную границу в 29000 рублей.

Срок кредитования при изначальной величине займа в Microsoft Excel

Таким образом, воспользовавшись данным табличным массивом и проанализировав «за» и «против» каждого варианта, заёмщик может принять конкретное решение об условиях кредитования, выбрав наиболее отвечающий его пожеланиям вариант из всех возможных.

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

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

Закрыть

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Закрыть

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Функция ПОДСТАВИТЬ в Excel выполняет динамическую замену определенной части строки на указанное новое значение и возвращает новую строку, содержащую замененную часть текста. Благодаря этой функции можно подставлять значения из другой ячейки. Рассмотрим возможности функции на конкретных примерах в Excel.

Функция ПОДСТАВИТЬ при условии подставляет значение

Пример 1. В результате расчетов, произведенных в некотором приложении, были получены некоторые значения, записанные в таблицу Excel. Некоторые величины рассчитать не удалось, и вместо числового представления была сгенерирована ошибка “NaN”. Необходимо заменить все значения “NaN” на число 0 в соответствующих строках.


Для замены и подстановки используем рассматриваемую формулу в качестве массива. Вначале выделим диапазон ячеек C2:C9, затем введем формулу через комбинацию Ctrl+Shift+Enter:


Функция ЧЗНАЧ выполняет преобразование полученных текстовых строк к числовым значениям. Описание аргументов функции ПОДСТАВИТЬ:

  • B2:B9 – диапазон ячеек, в которых требуется выполнить замену части строки;
  • “NaN” – фрагмент текста, который будет заменен;
  • 0 – фрагмент, который будет вставлен на место заменяемого фрагмента.

Для подстановки значений во всех ячейках необходимо нажать Ctrl+Shift+Enter, чтобы функция была выполнена в массиве. Результат вычислений:


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

Автозамена значения в текстовых ячейках с помощью функции ПОДСТАВИТЬ

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


Для выполнения заданного условия используем формулу:

Примечание: в данном примере ПОДСТАВИТЬ также используется в массиве Ctrl+Shift+Enter.

В результате получим:


Формула с макросом регулярного выражения и функция ПОДСТАВИТЬ


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

  1. Открыть редактор макросов (Ctrl+F11).
  2. Вставить исходный код функции (приведен ниже).
  3. Выполнить данный макрос и закрыть редактор кода.

Public Function RegExpExtract(Text As String , Pattern As String , Optional Item As Integer = 1) As String
On Error GoTo ErrHandl
Set regex = CreateObject( "VBScript.RegExp" )
regex.Pattern = Pattern
regex. Global = True
If regex.Test(Text) Then
Set matches = regex.Execute(Text)
RegExpExtract = matches.Item(Item – 1)
Exit Function
End If
ErrHandl:
RegExpExtract = CVErr(xlErrValue)
End Function

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

Регулярные выражения могут быть различными. Например, для выделения любого символа из текстовой строки в качестве второго аргумента необходимо передать значение «w», а цифры – «d».

Для решения задачи данного Примера 3 используем следующую запись:


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

Особенности использования функции ПОДСТАВИТЬ в Excel

Функция ПОДСТАВИТЬ имеет следующую синтаксическую запись:

  • текст – обязательный аргумент, характеризующий текстовую строку, в которой необходимо выполнить замену части текста. Может быть задан как текстовая строка («некоторый текст») или ссылка на ячейку, которая содержит текстовые данные.
  • стар_текст – часть текстовой строки, принимаемой в качестве первого аргумента данной функции, которую требуется заменить. Аргумент обязателен для заполнения.
  • нов_текст – обязательный для заполнения аргумент, содержащий текстовые данные, которые будут вставлены на место заменяемой части строки.
  • [номер_вхождения] – числовое значение, характеризующее номер вхождения старого текста, который требуется заменить на фрагмент нового текста. Возможные варианты записи:
  1. Аргумент явно не указан. Функция ПОДСТАВИТЬ определит все части текстовой строки, соответствующие фрагменту текста стар_текст, и выполнит их замену на нов_текст;
  2. В качестве аргумента передано числовое значение. Функция ПОДСТАВИТЬ заменит только указанное вхождение. Отсчет начинается слева направо, число 1 соответствует первому вхождению. Например, функция =ПОДСТАВИТЬ(«текст №1, №2, №3»;«текст»;«новый»;1) вернет значение «новый_текст №1, №2, №3».
  1. Аргумент [номер_вхождения] должен быть задан из диапазон целых положительных чисел от 1 до n, где n определяется максимально допустимой длиной строки, содержащейся в объекте данных (например, в ячейке).
  2. Если в текстовой строке, представленной в качестве аргумента текст не содержится фрагмент, переданный в качестве аргумента стар_текст, функция ПОДСТАВИТЬ вернет строку текст без изменений.
  3. Если число вхождений заменяемого фрагмента в обрабатываемой строке меньше, чем числовое значение, переданное в качестве аргумент [номер_вхождения], функция ПОДСТАВИТЬ вернет текстовую строку в исходном виде. Например, аргументы функции («а 1 а 2 а 3»;«а»;«б»;4) вернут строку «а 1 а 2 а 3».
  4. Рассматриваемая функция чувствительная к регистру, то есть строки «Слово» и «слово» не являются тождественными.
  5. Для решения аналогичных задач по замене части символов текстовой строки можно использовать функцию ЗАМЕНИТЬ. Однако, в отличие от функции ПОДСТАВИТЬ, для ее использования необходимо явно указывать позицию начального символа для замены, а также количество символов, которые необходимо заменить. Функция автоматически выполняет поиск указанной части строки и производит ее замену, поэтому в большинстве случаев предлагает более удобный функционал для работы с текстовыми строками.

Быстрая замена значений по условию в Excel


Есть две таблицы, одна оригинал, вторая для изменений. Все ученики кто получил 4 и выше имеют результат «Прошёл», у кого 3 и ниже — «Не прошёл».

Если будем использовать условное форматирование, придётся писать формулу каждый раз и если таблица очень большая и сразу хочется увидеть результат — лучше воспользоваться макросом.

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

  • вкладка «Разработчик», блок кнопок «Код»;
  • жмём «Visual Basic»;
  • добавляем новый модуль Insert -> Module.

Вставляем следующий код.

Sub Result()
Dim cell As Range
‘ Проверка каждой ячейки диапазона на прохождение
For Each cell In Range(«I2:L10»).Cells
If cell.Value
cell.Value = «Не прошёл»
ElseIf cell.Value >= 4 Then
cell.Value = «Прошёл»
End If
Next
End Sub


В этом случае используется связка из команд IF и ElseIf, что позволяет нам несколько расширить понимание формулы ЕСЛИ. Напомню, что макрос будет применён только к ячейкам из диапазона I2:L10!

Запускаем выбор макросов с помощью сочетания клавиш Alt+F8 и нажимаем кнопку «Выполнить».


Получаем результат. Во второй таблице я сразу вижу кто из учеников прошёл дальше, а кто нет. Ну и кому что необходимо подтянуть:)


Таким образом решается некая ограниченность формулы ЕСЛИ, ведь условия в макросе можно продолжить и подобрать свои, всего лишь изменив значения.

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

Примечание: В конце этой статьи есть небольшое видео о том, как вывести редактор запросов.

В Power Query можно заменить одно значение другим в выбранных столбцах.

Задача

Замена текстовых значений

С помощью ленты редактора запросов

На ленте Редактора запросов выберите на вкладке Преобразование Замена значений.

С помощью контекстного меню Редактора запросов:

Щелкните текстовую ячейку правой кнопкой мыши и выберите команду Замена значений.

Чтобы заменить текстовые значения:

В диалоговом окне Замена значений введите значения в поля Значение для поиска и Заменить на.

Установите флажок Сравнивать содержимое ячейки целиком, чтобы заменить все содержимое ячеек. В противном случае при использовании команды Замена значений будут заменены все значения, соответствующие условию Значение для поиска.

Замена числовых, логических значений или значений даты и времени

С помощью ленты редактора запросов

На ленте Редактора запросов, на вкладке « преобразовать » выберите Замена значений.

С помощью контекстного меню Редактора запросов:

Щелкните ячейку с числовым значением или значением даты и времени правой кнопкой мыши и выберите команду Замена значений.

Чтобы заменить числовые, логические значения или значения даты и времени:

В диалоговом окне Замена значений введите значения в поля Значение для поиска и Заменить на.

Примечание: Редактор запросов отображается только при загрузке, редактировании или создании нового запроса с помощью Power Query. В видео показано окно редактора запросов, которое отображается после изменения запроса в книге Excel. Чтобы просмотреть редактор запросов, не загружая и не изменяя существующий запрос в книге, в разделе Получение внешних данных на вкладке ленты Power Query выберите Из других источников > Пустой запрос. В видео показан один из способов отображения редактора запросов.

Как автоматически заполнять другие ячейки при выборе значений в раскрывающемся списке Excel?

Допустим, вы создали раскрывающийся список на основе значений в диапазоне A2: A8. При выборе значения в раскрывающемся списке необходимо, чтобы соответствующие значения в диапазоне B2: B8 автоматически подставлялись в определенную ячейку. Например, когда вы выбираете Наталию из раскрывающегося списка, соответствующий балл 40 будет заполнен в E2, как показано на скриншоте ниже. В этом руководстве представлены два метода, которые помогут вам решить проблему.


Выпадающий список автоматически заполняется функцией ВПР.

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

1. Выберите пустую ячейку, в которую вы хотите автоматически подставить соответствующее значение.

2. Скопируйте и вставьте в нее приведенную ниже формулу, а затем нажмите Enter ключ.

=VLOOKUP(D2,A2:B8,2,FALSE)


Внимание: В формуле D2 это выпадающий список ЯЧЕЙКА, A2: B8 диапазон таблицы включает значение поиска и результаты, а также число 2 указывает номер столбца, в котором находятся результаты. Например, если результаты находятся в третьем столбце диапазона таблицы, измените 2 на 3. Вы можете изменить значения переменных в формуле в зависимости от ваших потребностей.

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


Легко выбирайте несколько элементов из раскрывающегося списка в Excel:

Вы когда-нибудь пробовали выбрать несколько элементов из раскрывающегося списка в Excel? Здесь Выпадающий список с множественным выбором полезности Kutools for Excel может помочь вам легко выбрать несколько элементов из раскрывающегося списка в диапазоне, на текущем листе, в текущей книге или во всех книгах. См. Демонстрацию ниже:
Загрузите Kutools для Excel прямо сейчас! (30-дневная бесплатная трасса)

Выпадающий список автоматически заполняется с помощью Kutools for Excel

Y вы можете легко заполнить другие значения на основе выбора из раскрывающегося списка, не запоминая формулы с Найдите значение в списке формула Kutools for Excel.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выберите ячейку для поиска значения автозаполнения (говорит ячейка C10), а затем щелкните Кутулс > Формула Помощник > Формула Помощник, см. снимок экрана:


3. В Помощник по формулам диалоговом окне укажите следующие аргументы:

  • В Выберите формулу коробка, найдите и выберите Найдите значение в списке;
    Советы: Вы можете проверить Фильтр введите определенное слово в текстовое поле, чтобы быстро отфильтровать формулу.
  • В Таблица_массив поле, щелкните кнопка для выбора диапазона таблицы, который содержит значение поиска и значение результата;
  • В Look_value поле, щелкните кнопку, чтобы выбрать ячейку, содержащую искомое значение. Или вы можете напрямую ввести значение в это поле;
  • В Колонка поле, щелкните кнопку, чтобы указать столбец, из которого вы вернете совпадающее значение. Или вы можете ввести номер столбца в текстовое поле, если вам нужно.
  • Нажмите OK.


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


Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Демо: раскрывающийся список автоматически заполняется без запоминания формул

Статьи по теме:

Автозаполнение при вводе текста в раскрывающемся списке Excel
Если у вас есть раскрывающийся список проверки данных с большими значениями, вам нужно прокрутить список вниз только для того, чтобы найти нужное, или введите все слово напрямую в поле списка. Если есть способ разрешить автозаполнение при вводе первой буквы в выпадающем списке, все станет проще. В этом руководстве представлен метод решения проблемы.

Создать раскрывающийся список из другой книги в Excel
Создать раскрывающийся список проверки данных среди листов в книге довольно просто. Но если данные списка, необходимые для проверки данных, находятся в другой книге, что вы будете делать? В этом руководстве вы узнаете, как подробно создать раскрывающийся список из другой книги в Excel.

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

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

Помогите, пожалуйста с формулой. Нужна подстановка значений из листа с данными в лист расчета. Ранее делали расчет на 3 и 4 м, все нормально. появилась необходимость расширения данных и что-то не клеится(

Помогите, пожалуйста с формулой. Нужна подстановка значений из листа с данными в лист расчета. Ранее делали расчет на 3 и 4 м, все нормально. появилась необходимость расширения данных и что-то не клеится( Иваныч77

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Здравствуйте! 2 недели пытаюсь решить задачу, которую сам перед собой поставил, но так до сих пор и не получилось.

Есть документ, который состоит из двух листов, на одном листе поля ввода данных (титульный лист) на втором - некая база данных о клиентах.

Задача стоит следующая, сделать так, чтобы при вводе в определенные ячейки на выбор (их три: телефон, гос. номер, паспорт) excel искал это значение в "базе" и если бы находил, то во все остальные ячейки подставлял данные от нужного клиента

В целом, у меня получилось сделать это по одному значению, если добавить второе или третье, то появляется ошибка зациклинности.

Логику я понимаю, а вот с формулой какая-то беда.

Нужно найти (я делал через индекс и поискпоз) номер телефона и если нашел, то вставить в нужные ячейки нужные данные, если телефон не нашел, то искать по паспорту, если всё норм, то вставить, если нет, то искать по гос. номеру, если норм, то вставить, если нет, то оставить пустым.

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

Надеюсь объяснил нормально)

Файл примера вложил, темно-синим помечены ячейки, по которым ведётся поиск

Здравствуйте! 2 недели пытаюсь решить задачу, которую сам перед собой поставил, но так до сих пор и не получилось.

Есть документ, который состоит из двух листов, на одном листе поля ввода данных (титульный лист) на втором - некая база данных о клиентах.

Задача стоит следующая, сделать так, чтобы при вводе в определенные ячейки на выбор (их три: телефон, гос. номер, паспорт) excel искал это значение в "базе" и если бы находил, то во все остальные ячейки подставлял данные от нужного клиента

В целом, у меня получилось сделать это по одному значению, если добавить второе или третье, то появляется ошибка зациклинности.

Логику я понимаю, а вот с формулой какая-то беда.

Нужно найти (я делал через индекс и поискпоз) номер телефона и если нашел, то вставить в нужные ячейки нужные данные, если телефон не нашел, то искать по паспорту, если всё норм, то вставить, если нет, то искать по гос. номеру, если норм, то вставить, если нет, то оставить пустым.

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

Надеюсь объяснил нормально)

Файл примера вложил, темно-синим помечены ячейки, по которым ведётся поиск Narahon

Есть документ, который состоит из двух листов, на одном листе поля ввода данных (титульный лист) на втором - некая база данных о клиентах.

Задача стоит следующая, сделать так, чтобы при вводе в определенные ячейки на выбор (их три: телефон, гос. номер, паспорт) excel искал это значение в "базе" и если бы находил, то во все остальные ячейки подставлял данные от нужного клиента

В целом, у меня получилось сделать это по одному значению, если добавить второе или третье, то появляется ошибка зациклинности.

Логику я понимаю, а вот с формулой какая-то беда.

Нужно найти (я делал через индекс и поискпоз) номер телефона и если нашел, то вставить в нужные ячейки нужные данные, если телефон не нашел, то искать по паспорту, если всё норм, то вставить, если нет, то искать по гос. номеру, если норм, то вставить, если нет, то оставить пустым.

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

Надеюсь объяснил нормально)

Файл примера вложил, темно-синим помечены ячейки, по которым ведётся поиск Автор - Narahon
Дата добавления - 05.06.2018 в 16:54

Читайте также: