Excel заполнение данных по условиям

Логический оператор ЕСЛИ в Excel применяется для записи определенных условий. Сопоставляются числа и/или текст, функции, формулы и т.д. Когда значения отвечают заданным параметрам, то появляется одна запись. Не отвечают – другая.

Логические функции – это очень простой и эффективный инструмент, который часто применяется в практике. Рассмотрим подробно на примерах.

Синтаксис функции ЕСЛИ с одним условием

Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные.

=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)

Разберем синтаксис функции:

Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).

Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).

Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).

Пример:

Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».

Внимание! Слова в формуле необходимо брать в кавычки. Чтобы Excel понял, что нужно выводить текстовые значения.

Еще один пример. Чтобы получить допуск к экзамену, студенты группы должны успешно сдать зачет. Результаты занесем в таблицу с графами: список студентов, зачет, экзамен.

Обратите внимание: оператор ЕСЛИ должен проверить не цифровой тип данных, а текстовый. Поэтому мы прописали в формуле В2= «зач.». В кавычки берем, чтобы программа правильно распознала текст.



Функция ЕСЛИ в Excel с несколькими условиями

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

Синтаксис будет выглядеть следующим образом:

=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))

Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.

Примеры несколько условий функции ЕСЛИ в Excel:

Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.

В этом примере мы добавили третье условие, подразумевающее наличие в табеле успеваемости еще и «двоек». Принцип «срабатывания» оператора ЕСЛИ тот же.

Расширение функционала с помощью операторов «И» и «ИЛИ»

Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.

Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с.

Функции И и ИЛИ могут проверить до 30 условий.

Пример использования оператора И:

Пример использования функции ИЛИ:

Как сравнить данные в двух таблицах

Пользователям часто приходится сравнить две таблицы в Excel на совпадения. Примеры из «жизни»: сопоставить цены на товар в разные привозы, сравнить балансы (бухгалтерские отчеты) за несколько месяцев, успеваемость учеников (студентов) разных классов, в разные четверти и т.д.

Чтобы сравнить 2 таблицы в Excel, можно воспользоваться оператором СЧЕТЕСЛИ. Рассмотрим порядок применения функции.

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

Исходные данные (таблицы, с которыми будем работать):

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

В строку формул записываем: =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Сравниваемый диапазон – это вторая таблица.

Чтобы вбить в формулу диапазон, просто выделяем его первую ячейку и последнюю. «= 0» означает команду поиска точных (а не приблизительных) значений.

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

Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).

Скачать все примеры функции ЕСЛИ в Excel

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

Поиск и подстановка по нескольким условиям

Постановка задачи

Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно — без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?

Предположим, что у нас есть база данных по ценам товаров за разные месяцы:

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

Способ 1. Дополнительный столбец с ключом поиска

Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

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

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы: Простой способ, знакомая функция, работает с любыми данными.

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

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS), появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

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

Минусы: Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

О том, как спользовать связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) в качестве более мощной альтернативы ВПР я уже подробно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:

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

   

3. Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы: Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы: Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

Ссылки по теме

• Как искать и подставлять данные с помощью функции ВПР (VLOOKUP)
• Что такое формулы массива и как их использовать
• Как использовать связку функций ИНДЕКС и ПОИСКПОЗ вместо ВПР
• Как извлечь сразу все значения, а не только первое с помощью ВПР

Выпадающий список в MS EXCEL на основе Проверки данных

​Смотрите также​ ней выпадающий список​: Спасибо большое. Всё​: Вы бы нарисовали​ + Shift +​ мы выбрали Легковой,​ список А​ подмена значения ячейки​ Пустая ячейка недопускается.​ столбцом?​ в Excel, смотрите​ устанавливаем выпадающие списки.​ в первом столбце​В EXCEL не предусмотрена​ с элементами выпадающего​ на использовании Именованного​Преимущество​

​При заполнении ячеек данными,​RAN​ получилось. Я пробовал​

​ по подробнее конечный​ Enter !!!​ во втором -​ это то, что​ E1 на «текст»​В итоге мы​

​Заранее, спасибо.​ в статье «Как​ Тип данных –​ (у нас –​ регулировка размера шрифта​ списка в другую​ диапазона, другой –​

​: быстрота создания списка.​ часто необходимо ограничить​

А. Простейший выпадающий список — ввод элементов списка непосредственно в поле Источник

​: Можно и в​ также, но с​ результат. Вам нужно​Скачать зависимые выпадающие списки​ Fiat.​ мы хотим получить:​

​ (автозамена если текст),​​ имеем в ячейке​​vikttur​ сделать связанные выпадающие​ выбираем «Список». А​ это наименование товара​​ Выпадающего списка. При​​ книгу (например, в​ функции ДВССЫЛ().​

​Элементы для выпадающего списка​ возможность ввода определенным​ D2, но только​ $$: =ЕСЛИ(A1=1;$A$2:$A$14;$C$2:$C$14) -​ чтобы в итоге​ в Excel​

​Мы будем перемещать ячейку​Итак, мы имеем:​​ что видимо невозможно​​ либо заранее написанный​: Это неинтересно. И​ списки в Excel​ в строке «Источник»​ – ячейки А2:А4​ большом количестве элементов​

​ книгу Источник.xlsx), то​​Используем именованный диапазон​ можно разместить в​ списком значений. Например,​ на один раз.​ не работало​​ при выбора ГОСТ​​Не нажмите OK случайно.​ H4 на столько​тип автомобиля: Легковой, Фургон​ сделать без VBA…​ текст из списка,​
​ для реализации, и​​ легко».​

Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)

​ указываем имя диапазона.​ должны совпадать с​ имеет смысл сортировать​ нужно сделать следующее:​Создадим Именованный диапазон Список_элементов,​ диапазоне на листе​ имеется ячейка, куда​В ячейке формула​

​al_anya​ все отводы отобразились​ Поэтому, когда вы​​ строк, пока не​​ и Внедорожник (Категория)​DV68​

​ либо число из​​ для практического применения.​Spare​ Например, «=Наименование_товара».​ ячейками В1:D1).​ список элементов и​
​в книге Источник.xlsx создайте​​ содержащий перечень элементов​ EXCEL, а затем​ пользователь должен внести​ может либо быть,​: Добрый вечер! У​ одной строкой в​ перейдете в меню​ найдем позицию первого​​производитель: Fiat, Volkswagen i​​: Добавьте к этому​ указанного диапозона.​vikttur​: Добрый день, Уважаемые​Подробнее, как установить​Если наименований много,​ использовать дополнительную классификацию​ необходимый перечень элементов;​ выпадающего списка (ячейки​

​ в поле Источник​ название департамента, указав​ либо не быть.​ меня такая проблема:​ ячейке или так​ «Данные», «Проверка данных»​ легкового Fiatа. Поэтому​ Suzuki (Подкатегория) и​ формат ячейки​Serge 007​: Задача. Сформировать отдельный​

​ коллеги.​ выпадающий список, смотрите​ то столбец можно​ элементов (т.е. один​в книге Источник.xlsx диапазону​

B. Ввод элементов списка в диапазон (на любом листе)

​A1:A4​ инструмента Проверки данных​ где он работает.​ После первой вставки​ в ячейке D1​ же как на​

​ и выберите как​ в колонке Тип​модель: … немножечко их​0;;;»ТЕКСТ»​

​: ОДИН выпадающий список​ список.​Возник вопрос по​ в статье «Выпадающий​ транспонировать в строку.​

​ выпадающий список разбить​ ячеек содержащему перечень​на листе Список).​ указать ссылку на​ Логично, предварительно создать​ данных формулы не​ нужно создать условие:​ листе ВЫЧИСЛЕНИЯ в​

​ Тип данных «список»,​
​ мы должны иметь​ есть (Подподкатегория)​Будет визуализация​​ — НИКАК​​Реализация. Количество записей​
​ связанному выпадающему списку,​

• ​ список в Excel».​​ Как это сделать,​​ на 2 и​
• ​ элементов присвойте Имя,​Для этого:​
• ​ этот диапазон.​ список департаментов организации​ будет.​

​ если А1=Россия, то​ столбец, каждый в​ а в поле​ значение Легковой, а​

• ​В то же время​
• ​richhamm​richhamm​ С15. Если СТРОКА()​

​ никак не получается​
​Устанавливаем​ смотрите в статье​ более).​ например СписокВнеш;​выделяем​​Предположим, что элементы списка​​ и позволить пользователю​al_anya​ раскрывающийся список для​ отдельной ячейке? Если​

​ «Источник» вставьте эту​ в колонке Производитель​ мы имеем следующие​: Спасибо огромное, визуализация​: Хорошо, а если​ больше этого количества,​ задать для второго​зависимые выпадающие списки в​

​ «Как поменять местами​Например, чтобы эффективно работать​откройте книгу, в которой​​А1:А4​​ шт;кг;кв.м;куб.м введены в​ лишь выбирать значения​: Serge_007, Да, я​ России, если А1=Украина,​ в столбец, то​

​ формулу и подтвердите​

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

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

​Этот список должен быть​ нужна замена значения.​ списка по значению​

​ D15, закончились и​ условий. В примере​.​ в Excel» тут.​ насчитывающем более 300​ с выпадающим списком;​

• ​нажимаем Формулы/ Определенные имена/​A1:A4​
• ​ Этот подход поможет​ принципе нереально?​ для Украины. Подскажите​ один раз только​
• ​ Ctrl + Shift​ использовали промежуточный столбец​ отсортирован в следующей​
• ​После долгой переписки​ ячейки. Или это​ эти — Е15​ для удобства понимания​

​Это второй уровень​Как настроить Excel,​ сотрудников, то его​выделите нужный диапазон ячеек,​ Присвоить имя​, тогда поле Источник​ ускорить процесс ввода​Serge_007​ реально ли? Спасибо​ выберите ГОСТ? Не​ + Enter. Вот​ (это было бы​ очередности:​ приходишь к самому​

​ в принципе не​ и т.д.​ есть примечания.​ выпадающих списков.​ чтобы при добавлении​

​ следует сначала отсортировать​
​ вызовите инструмент Проверка​в поле Имя вводим​ будет содержать =лист1!$A$1:$A$4​ и уменьшить количество​: Попробуйте встать МЕЖДУ​ABC​ понятно как будет​ и вся магия.​ отличным решением, но​Тип.​ первому ответу «НИКАК»,​ возможно запихнуть в​vikttur​Заранее благодарю.​Внимание!​ ячеек в список​ в алфавитном порядке.​ данных, в поле​ Список_элементов, в поле​Преимущество​ опечаток.​ ступенек лестницы​: смотрите имена (Ctrl+F3)​ выглядеть итог.​arturnt​ хотели бы показать​

​Производитель.​​ не хватило самой​
​ проверку данных?​: Погорячился. Можно проще.​vikttur​Перед тем, как​ столбца A, автоматически​ Затем создать выпадающий​ Источник укажите =ДВССЫЛ(«[Источник.xlsx]лист1!СписокВнеш»);​ Область выбираем Книга;​: наглядность перечня элементов​Выпадающий список можно создать​Реально?​

​ и проверку данных​arturnt​: Здравствуйте, уважаемые форумчане!​ вам что-то более​Модель.​ малости…​asd192​ Реализация в файле.​: Именованный диапазон:​ устанавливать выпадающие списки​ писалось название нового​

​ список, содержащий буквы​При работе с перечнем​Теперь на листе Пример,​ и простота его​ с помощью Проверки​al_anya​al_anya​: Выбрали один из​ помогите, пожалуйста, решить​ крутое ),​Он может быть любой​Serge 007​: Подробнее можете расписать?​Spare​=СМЕЩ(Список!$B$1;ПОИСКПОЗ(М!$C$15;Список!$A$1:$A$10;)-1;;ПОИСКПОЗ(М!$E$15;Список!$A$1:$A$10;)-ПОИСКПОЗ(М!$C$15;Список!$A$1:$A$10;)+СЧЁТЕСЛИ(Список!$A$1:$A$100;М!$E$15);1)​ в столбце В,​ столбца, смотрите в​ алфавита. Второй выпадающий​

excel2.ru

Связанные выпадающие списки в Excel.

​ элементов, расположенным в​​ выделим диапазон ячеек,​ модификации. Подход годится​​ данных ​: Просто мне кое-кто​​: Спасибо огромное!​​ ГОСТов из выпадающего​ задачу в Эксель.​​ то мы бы​ длины. Что еще​:​Наверняка, сначала, нужно​: Спасибо большое, сижу​Действительно для случая,​ выберите в первой​
​ статье «Как добавить​​ список должен содержать​ другой книге, файл​​ которые будут содержать​
​ для редко изменяющихся​или с помощью элемента​ тут говорит что​al_anya​ списка, например «ГОСТ1″​ Во вложении на​ искали комбинацию этих​ важно: стоит добавить​Не по теме:​ решить проблему в​ разбираюсь)​ когда на листе​ верхней ячейке столбца​ столбец в Excel​
​ только те фамилии,​ Источник.xlsx должен быть​ Выпадающий список.​ списков.​ управления формы Поле​ это Я НЕ​: Возможно ли дополнительно​ в ячейке А2​
​ листе «ВЫЧИСЛЕНИЯ» есть​ данных: Легковой Fiat.​
​ к нему еще​
​Напоминает известный анекдот:​ соседних столбцах, а​Мда, сколько много​ «список» значения ячеек​ А любое значение.​ автоматически».​ которые начинаются с​ открыт и находиться​вызываем Проверку данных;​Недостатки​
​ со списком (см.​ МОГУ, что можно​ создать условие в​ (скрин1)​ база отводов и​ Однако у нас​ два меньших списка,​В армии проверка.​
​ потом поместить результат​ чего еще не​ C15:E15 следуют одно​ Главное, чтобы эта​Как сделать в Excel​ буквы, выбранной первым​ в той же​в поле Источник вводим​: если добавляются новые​
​ статью Выпадающий (раскрывающийся)​ с помощью макросов​​ ячейке D1: если​После выбора ГОСТа,​ госты, в столбце​ нет такого столбца,​ необходимых для Типа​ Идёт генерал и​ в один выпадающий​
​ знаю, расти и​ за другим.​ ячейка не была​ динамический диапазон​ списком. Для решения​ папке, иначе необходимо​ ссылку на созданное​
​ элементы, то приходится​ список на основе​ ​ и т.д.​
​ не заполнены ячейки​ при выделении ячейки​ “D” и “E”.​ но мы можем​ и Производителя, то​ видит что у​ список.​ расти.​Spare​ пустой. У нас​- чтобы размер​ такой задачи может​ указывать полный путь​ имя: =Список_элементов.​
​ вручную изменять ссылку​ элемента управления формы).​Serge_007​ А1 и С1​​ B2 должен появиться​ В столбце “B”​ создать его «на​ есть к категории​ солдата не чищены​richhamm​MrGrave​: Спасибо огромное, оказывается​
​ – это ячейка​​ диапазонов списков при​ быть использована структура​​ к файлу. Вообще​
​Примечание​ на диапазон. Правда,​В этой статье создадим​: Хорошо. Передайте этому​ выводить следующий текст:»заполните​ возможность вызвать список​ и “C” у​ лету», другими словами,​ (первый список) и​ ботинки (сапоги).​: В данном случае​
​: Задача такая.​ все проще чем​ А2.​ добавлении или убавлении​
​ Связанный список или​​ ссылок на другие​Если предполагается, что​​ в качестве источника​​ Выпадающий список с​ кому-то, что бы​
​ недостающие данные»​
​ Отводов соответствующий данному​ меня формируется список​ используя формулу массива.​ подкатегории (второй список).​Диалог:​ ячейка используется для​В ячейке А1​ я думал.​Выделяем диапазон в​ ячеек менялся автоматически,​ Вложенный связанный список.​
​ листы лучше избегать​ перечень элементов будет​ можно определить сразу​ помощью Проверки данных​ это он встал​ABC​ ГОСТУ(скрин2)​ всех неповторяющихся ГОСТов,​ Набирая эту формулу,​ Эти дополнительные списки​
​Г: Солдат, почему​ ввода значения. Т.е.​ может быть только​Тему можно закрывать​ столбце В (у​ смотрите в статье​
​Выпадающие списки в Excel​ или использовать Личную​ дополняться, то можно​ более широкий диапазон,​ (Данные/ Работа с​ между ступенек​: , не получится​если в ячейке​ который я использую​ вы можете себе​ списки выглядят следующим​ не чищены ботинки?​ списка по сути​ три варианта текстового​
​vikttur​ нас – это​ «Чтобы размер таблицы​бывают разные. Есть​ книгу макросов Personal.xlsx​
​ сразу выделить диапазон​ например,​ данными/ Проверка данных)​Можно при этом​ без макроса​ А3 я выбиру​ в качестве списка​ представить, что такой​
​ образом:​С: А Вас​ нет — список​ значения «один; два;​
​: Не спешите. Е5​ В2:В3). Снова через​ Excel менялся автоматически».​ простой​ или Надстройки.​ большего размера, например,​A1:A100​

excel-office.ru

Связанный выпадающий список по нескольким условиям.

​ с типом данных​​ использовать подручные инструменты​D1 проверка данных:​
​ другой гост, то​ на листе “Отводы”,​ промежуточный столбец существует,​Дело в том, что​ это не касается!​ пришел в голову​ три», они выбираются​ может быть не​
​ функцию «Проверка данных»​

​Теперь нужно присвоить​​раскрывающийся список Excel в​
​Если нет желания присваивать​
​А1:А10​. Но, тогда выпадающий​ Список.​С помощью макроса​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=Если(C1<>»»;Двссыл(A1))​

​ в ячейке B3​​ в столбце “A”.​ и вы увидите,​ эти списки не​
​Г: ?! (бьёт​

​ как решение задачи​​ с выпадающего списка​ заполнено?​ выбираем «Тип данных»​

​ имена всем этим​​ ячейке​ имя диапазону в​

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

​ через «проверку данных».​​vikttur​ – список. А​ спискам. У нас​. Есть​ файле Источник.xlsx, то​
​ случае Выпадающий список​ пустые строки (если,​ по разному.​ быстро удалять/вставлять формулы​: А поподробнее можно?​

​ другой список, отличный​​ листе “Отводы”, в​ ;-)​ записей по Типу​Г: Что значит​Само ограничение выглядит​В ячейке B1​: Поправка — «Е15»,​ в строке «Источник»​ в списках четыре​многоуровневые зависимые выпадающие списки​ формулу нужно изменить​ может содержать пустые​ например, часть элементов​

​Самым простым способом создания​​ и списки из​ABC​ от B2 Прикрепленные​ столбце “B” список​
​Для определения положения Легковой​

​ и Производителю, находящихся​​ не касается?! ПОЧЕМУ​ так: =ЕСЛИ(ЕТЕКСТ(A1);ЗАМЕНИТЬ(A1;1;255;»текст»);ЕСЛИ(И(A1>1000;A1 Только​ необходимо организовать выпадающий​

​ конечно же.​​ пишем такую формулу​ диапазона (четыре столбца).​
​ в Excel​ на =ДВССЫЛ(«[Источник.xlsx]лист1!$A$1:$A$4»)​ строки.​ была удалена или​ Выпадающего списка является​ одной и той​: проверка данных проверяет​

​ файлы скрин1.jpg (24.81​​ тех отводов, которые​ Fiat, мы, конечно,​

​ в списке Моделей.​​ НЕ ЧИЩЕНЫ?!​ замена не работает​
​ список по условию​Spare​ =ДВССЫЛ(А2)​ Легко и быстро​

planetaexcel.ru

Выпадающий список по условию

​. Это, когда, в​​СОВЕТ:​
​Избавиться от пустых строк​ список только что​ ввод элементов списка​ же ячейки. Ну​ только заведенные значении​ КБ) скрин2.jpg (58.04​ удовлетворяют условию ГОСТ.​
​ будем использовать функцию​ Вы можете создать​С: Знаете, завхоз​Serge 007​ — на каждое​: Да к сожалению​Этой формулой мы говорим​
​ сделать так.​
​ зависимости от выбранных​Если на листе​ и учесть новые​ был создан). Чтобы​ непосредственно в поле​
​ это как быстро​ (Вашем примере ДВССЫЛ(A1)-дисп.​ КБ)​Sanja​ ПОИСКПОЗ. Смотрите:​
​ их с помощью​ не выдал щётку,​: Забавно​ значение ячейки A1,​ поторопился.​
​ Excel, что список​Как присвоить имя диапазону​

​ данных в первом​​ много ячеек с​
​ элементы перечня позволяет​ пустые строки исчезли​

​ Источник инструмента Проверка​​ прыгать с одной​

CyberForum.ru

Выпадающий список с условием на значение

​ имен), может ругаться​​gling​
​: Зачем такие сложности?​ПОИСКПОЗ(B4&C4;F5:F39&G5:G39;0)​ инструмента «Удалить дубликаты»​ гуталина в части​Я правильно понял?​ выпадает свой диапазон​Видимо имеете ввиду​ нужно показывать, в​ в​ столбце выпадающего списка,​
​ правилами Проверки данных,​ Динамический диапазон. Для​ необходимо сохранить файл.​ данных.​ ступеньки на другую.​ при ОШИБКЕ.​

​: Вам нужны в​​ Фильтруйте на месте.​Вышеописанное означает, что мы​

​ (например, это показано​​ нет…​Код =ЕСЛИ(ЕЧИСЛО(A1)*((A1>1000)*(A1​ значений для выбора.​ E15, да может​ зависимости от значения​Excel.​ меняется выпадающий список​

​ то можно использовать​​ этого при создании​
​Второй недостаток: диапазон источника​Предположим, в ячейке​ Если делать это​al_anya​ ПРИЕМАХ про них​ Ну, если уж​

​ хотим знать позицию​​ в этом видео​Г: А меня​richhamm​Например:​ быть не всегда​ в ячейке столбца​Выделяем диапазон ячеек​ в ячейках второго​
​ инструмент Выделение группы​ Имени Список_элементов в​ должен располагаться на​

​B1​​ очень быстро, то​
​: ABC, Т.е. мне​
​ рассказано и показано​

​ сильно хочется, Сводная​​ Легкового Fiatа (отсюда​ продолжительностью около 2​ это не касается!!!​: Да, все верно.​- для значения​ заполнено.​ А.​ всех списков сразу​ столбца, третьего, т.д.​ ячеек (Главная/ Найти​
​ поле Диапазон необходимо​ том же листе,​необходимо создать выпадающий​ со стороны будет​ нужно вместо предыдущей​ как сделать. Рядом​ таблица​ и связь B4&C4).​ минут). Когда мы​
​С: Дык я​ Только такое условие​ A1=»один», выпадающий список​Плюс могут быть​Здесь все просто.​

​ вместе с шапкой​​Здесь разберём​ и выделить/ Выделение​

​ записать формулу =СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))​​ что и выпадающий​ список для ввода​ казаться что человек​
​ формулы в проверке​arturnt​arturnt​ Где? В нашем​ это сделали, тогда​ так сразу и​ должно быть в​ в ячейке B1​

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

​Использование функции СЧЁТЗ() предполагает,​​ список, т.к. для​ единиц измерений. Выделим​ стоит между ними​
​ данных вставить новую?​: , Спасибо большое,​: Извените, не понял,​ воображаемом вспомогательном столбце,​ …​

​ сказал…​​ проверке данных на​

​ будет из вариантов​

​ к примеру 1.1.​
​ диапазона (столбца) состоит​ у нас это​ в Excel​ Проверка данных этого​ что заполнение диапазона​
​ правил Проверки данных нельзя​
​ ячейку​:D​
​ Извините, что не​ прочитаю и обязательно​
​ что Вы имели​ то есть: F5:F39&G5:G39.​
​​Итак, как сделать два​ той ячейке куда​
​ «1; 2; 3;»​ потом 3.4.​ из нескольких слов.​ диапазон А1:D4. На​
​.​ инструмента позволяет выделить​
​ ячеек (​ использовать ссылки на​B1​

CyberForum.ru

Связанные выпадающие списки и формула массива в Excel

​al_anya​ догоняю​ отпишусь!​ ввиду, когда говорили​ И здесь самая​Для ячеек, которые должны​ связанных списка в​ вводится значение. Все​- для значения​vikttur​ Например, «Зимние пальто».​ закладке «Формулы» в​Например, в первом​

Два связанных выпадающих списка с формулой массива

​ ячейки, для которых​A:A​ другие листы или​и вызовем Проверку​: Спасибо вам за​al_anya​, Тоже спасибо​ фильтровать на месте?​ большая сложность всей​ стать раскрывающимися списками​ Excel: категория, подкатегория​ бы хорошо,только «текст»​ A1=»два», выпадающий список​: С Е15 решаемо​ А в имени​ разделе «Определенные имена»​ столбце из выпадающего​ проводится проверка допустимости​), который содержит элементы,​ книги (это справедливо​

​ данных.​

• ​ такой емкий ответ,​: Формулу поменяла, а​
• ​ за помощь.​Sanja​
• ​ формулы.​ в меню «Данные»​

​ и категория более​ не подставляется в​ в ячейке B1​

​ (определение последней заполненной​ диапазона нельзя ставить​ нажимаем функцию «Создать​

1. ​ списка выбрали «Пальто».​
2. ​ данных (заданная с​
3. ​ ведется без пропусков​

​ для EXCEL 2007​Если в поле Источник​ передам, дословно цитируя​ где добавить, чтобы​Могу ли я​: На листе ВЫЧИСЛЕНИЯ​Остальное уже проще, а​ выбираем «Проверка данных»​ нижнего уровня. Своими​ ячейку (E1 из​ будет из вариантов​ ячейки). Но с​ пробел. Имя диапазона​

​ из выделенного фрагмента».​ Во втором столбце​ помощью команды Данные/​ строк (см. файл​ и более ранних).​ указать через точку​steysi​ текст выводился?​ вас как-то плюсами​ включите Автофильтр​ наибольшего внимания требует​ и как тип​ словами в данном​ примера)​ «11; 22; 33;»​

​ условием «пункты не​

Первый и второй связанный выпадающий список: Тип и Производитель

​ напишем так «Зимние_пальто».​ В появившемся диалоговом​ появился выпадающий список​ Работа с данными/​ примера, лист Динамический​Избавимся сначала от второго​

​ с запятой единицы​: Подскажите пожалуйста как​Pelena​

​ отблагодарить?)​arturnt​ функция СЧЁТЕСЛИМН, которая​ данных выбираем «Список».​

​ случае нижний уровень​И да, пустая​- для значения​ подряд» плохо.​

Третий связывающий выпадающий список: Модель

​ Но формула ДВССЫЛ​ окне оставляем галочку​ размеров этого пальто.​ Проверка данных). При​ диапазон).​ недостатка – разместим​ измерения шт;кг;кв.м;куб.м, то​ сделать и возможно​: Я бы для​arturnt​: В моем случае​ проверяет, сколько есть​Для Типа как источник​ — это «подподкатегория»​ ячейка не допускается.​

​ A1=»три», выпадающий список​Нужен макрос по​ не найдет этот​ только у строки​ А, если в​ выборе переключателя Всех​Используем функцию ДВССЫЛ()​ перечень элементов выпадающего​ выбор будет ограничен​ ли это в​ такого текста использовала​: , Статья помогла!​ не все так​ Легковых Fiatов. В​ данных мы просто​ если она вообще​ Т.е. до ввода​ в ячейке B1​ формированию списка. Формулами​ диапазон. Тогда формулу​ «В строке выше».​ первом столбце этой​ будут выделены все​Альтернативным способом ссылки на​ списка на другом​ этими четырьмя значениями.​ екселе, выпадающий список​ ячейку, где считается​ Благодарю​ просто, на листе​ частности, она проверяет,​ указываем диапазон B7:B9.​ существует… Но для​

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

​Нажимаем «ОК». Всё, имена​

​ же ячейки из​ такие ячейки. При​ перечень элементов, расположенных​ листе.​Теперь смотрим, что получилось.​ со следующим условием:​ цена. То есть​haver​ «Вычисления» будет что​ сколько раз в​

​Для Производителя мы уже​ лучшего понимания данного​ а как только​ «111; 222; 333;»​ доп. столбцов не​ =ДВССЫЛ(ПОДСТАВИТЬ(A2;» «;»_»))​ присвоены. На закладке​ выпадающего списка выбрали​ выборе опции Этих​ на другом листе,​В правилах Проверки данных (также​ Выделим ячейку​ 0-нет льгот, 1-скидка​ если введены не​

​: Как сделать выпадающий​

​ то вроде базы,​ списке встречаются такие​

​ используем формулу, которая​ обучающего материала, предположим,​ начинаем вводить, то​Такое в принципе​ обойтись.​Если список на​ «Формулы» нажимаем функцию​ «Брюки», то во​ же выделяются только​ является использование функции​ как и Условного​

​B1​ в оплате 25%,​

​ все данные, там​ список, который будет​ где будут и​ записи, которые в​ подробно описана здесь.​ что существует.​ результат — либо​ возможно?​Spare​ другом листе, то​ «Диспетчер имен».​ втором столбце будет​ те ячейки, для​

exceltable.com

Список по условию

​ ДВССЫЛ(). На листе​​ форматирования) нельзя впрямую​. При выделении ячейки​ 2- 50%???? Нужно​ текст «заполните…», как​ состоять из соответствующего​ отводы, и трубы​ столбце F5:F39 имеют​ Она выглядит так:​В любом случае, с​ текст, либо значение​Vlad999​: Понял спасиб. Тогда​ в формуле указываем​Здесь перечислены все наши​ выпадающий список с​ которых установлены те​
​ Пример, выделяем диапазон​ указать ссылку на​ справа от ячейки​ вычислить льготу и​ только данные введены,​

​ диапазона ячеек по​​ итд.​ значение Легковой, а​Модель — описание для​ самого начала напишем,​ в указанном диапозоне.​

​: да возможно.​​ решается тупо, выбрали​ название этого листа.​ диапазоны списков. Проверили​ размерами брюк.​

​ же правила проверки​​ ячеек, которые будут​ диапазоны другого листа​

​ появляется квадратная кнопка​​ оплату​ считается формула. Как-то​ условию? Т.е. если​Также, будет куча​ в столбце G5:G39​ этой записи сделаем​ что этот учебный​ячейка E1 из​
​Где будут храниться​ в ячейке е15​ Напишем так. =ДВССЫЛ(»Размеры!А2:А4»)​ всё. Можно подкорректировать​Итак, сделаем две​ данных, что и​ содержать выпадающий список,​ (см. Файл примера):​
​ со стрелкой для​Вячеслав Я​ так​ какая-то ячейка, например,​ листов, для отводов,​ — Fiat. Функция​

​ таким же самым​​ материал является продолжением​ примера, т.е. «текст»​ значения 1,2,3,11,22,33,111,222,333.​ один пункт все​Нажимаем «ОК». Теперь​ размер диапазона. Мы​ таблицы. Саму таблицу​ для активной ячейки.​ вызываем Проверку данных,​Пусть ячейки, которые должны​ выбора элементов из​:​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ИЛИ(A2=»»;C2=»»;D2=»»);»заполните данные»;СУММПРОИЗВ(($G$2:$G$25=A2)*($H$2:$H$25=$D$2)*(C2>=$I$2:$I$25)*(C2​ равна 0, то​ для труб, фланцев,​ выглядит так:​ образом.​ материала: Как сделать​

​ это тоже ИСТИНА,​​Vlad999​ перечислили, сменили пункт​ во втором столбце​ уменьшили размер диапазона​ сделаем на странице​
​Примечание​ в Источнике указываем​ содержать Выпадающий список,​ выпадающего списка.​steysi​al_anya​
​ в выпадающий список​ заглушек. И все​СЧЁТЕСЛИМН(F5:F39;B4;G5:G39;C4)​Теперь рассмотрим, как связать​ зависимые выпадающие списки​ а ЛОЖЬ -​: пример​ в е15 написали​ установлены выпадающие списки,​ «Юбка», чтобы в​

​ книги «Таблица». А​​:​ =ДВССЫЛ(«список!A1:A4»).​ размещены на листе​Недостатки​

​, можно написать формулу​​: Спасибо!​ загоняется один диапазон​ эти листы будут​
​А вся формула для​ выпадающий список в​
​ в ячейках Excel,​ пустая ячейка​richhamm​

​ остальные. Не очень​​ которые меняются, в​ выпадающем списке не​

planetaexcel.ru

Выпадающий список с условием в Excel

​ списки сделаем на​​Если выпадающий список​Недостаток​ Пример,​этого подхода: элементы​ в ячейке и​al_anya​ ячеек, а если​ обращаться в базу​ раскрывающегося списка это:​ Excel. Поскольку Модель​ в котором подробно​DV68​

​: Доброго времени суток!​​ красиво конечно но​ зависимости от того,​
​ было пустой строки.​
​ странице «Размеры». У​ содержит более 25-30​

​: при переименовании листа​​а диапазон с перечнем​ списка легко потерять​ растащить далее по​: Всем добрый день!​ 1, то -​

CyberForum.ru

Как создать раскрывающийся список при определенном условии (ЕСЛИ и раскрывающийся список)

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

​ – формула перестает​​ элементов разместим на​ (например, удалив строку​

​ ячейкам:​​ А все-таки можно​

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

​ ячейках первого столбца.​​первый выпадающий список в​ таблица.​
​ с ним становится​
​ работать. Как это​

​ другом листе (на​​ или столбец, содержащие​

​Код =ЕСЛИ(A2=0;B2*1;ЕСЛИ(A2=1;B2*1,25;ЕСЛИ(A2=2;B2*1,5;»Больше скидок​​ ли вставить текст​Димит​Хотел сделать приложение​ нескольких ячейках -​ от Производителя -​

​ из таких списков.​​richhamm​ из 2-х записей:​ людям, боюсь в​ Получилось так.​ ячейки столбца А​И мы сделали такие​

​ неудобно. Выпадающий список​​ можно частично обойти​ листе Список в​ ячейку​

​ нет»))) Пример во​​ именно в ТУ​: В источник можно​ на C# там​ не забудьте обозначить​ значит мы будем​ Рекомендуем вам ознакомиться​: Немного переделал, так​ 1-я «текст», 2-я​ этом случаем макрос​Копируем формулу вниз по​
​.​

​ списки.​​ одновременно отображает только​

​ см. в статье​​ файле примера).​B1​ вложении. Результат по​ ячейку. Извините, не​ поставить формулу, например:​ проще будет из​

​ ячейки как абсолютные​​ использовать формулу массива.​

​ с ним, потому​​ больше подходит под​ пустая ячейка. В​

​ не подходит.​​ столбцу. Мы создали​У нас, в​Внимание!​
​ 8 элементов, а​ Определяем имя листа.​Для создания выпадающего списка,​); не удобно вводить​ столбцу​ моя прихоть​

​=ЕСЛИ(A1=1;A2:A14;C2:C14)​​ Excel данные вытащить...но​ ссылки! И еще​ Предположим, мы хотим​

​ что здесь подробно​​ задачу:​ пустую ячейку будет​
​Spare​

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

​С​​al_anya​(Данные-Проверка-Параметры, Тип данных-список,​ не одобряют такой​ СУПЕРВАЖНО, чтобы вся​
​ отобразить в нем​ описывается только то,​
​=ИЛИ(И(E1>1000;E1 В проверке​ заноситься число с​: Viktur, а не​ в Excel.​ диапазон A2:A3. И,​ столбцов (В, С,​ нужно пользоваться полосой​ диапазон ячеек, находящегося​ на другом листе,​ Подход годится для​steysi​: в ЯЧЕЙКУ d2,​ источник-формула)​ подход​​ запись была подтверждена​

​ легковые модели Fiat.​​ как сделать тот​ данных работает шикарно.​ условием больше 1000​

Выпадающий список с определенным условием

​ могли бы показать​​Другой способ сделать​ через «Проверки данных»​ D) должны полностью​ прокрутки, что не​ в другой книге​ можно использовать два​ маленьких (3-5 значений)​: ОО спасибо большое!!!​Serge_007​haver​

​gling​​ комбинацией клавиш Ctrl​​ В первом списке​​ другой связанный выпадающий​ Только нужна еще​ и меньше 2000.​ пример с доп​
​ связанный выпадающий список​ на закладке «Данные»,​ совпадать с названием​ всегда удобно.​​Если необходимо перенести диапазон​

​ подхода. Один основан​​ неизменных списков.​ ))) Сейчас попробую)))​

CyberForum.ru

​: У Вас в​

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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