Как найти несколько значений в столбце excel

Как в Excel вывести несколько значений ячейки по условию?

Есть таблица с перечнем продуктов. В ней отмечено, на каком складе этот продукт есть.

В данный момент по формуле =ОБЪЕДИНИТЬ(«,»;;ЕСЛИ($B$1:$G$1=A9;$A$2:$A$6;»»)) выводятся все продукты, а нужно, чтобы выводились только те, где стоит «+».
Думаю, что можно добавить функцию ЯЧЕЙКА, но не знаю в какое место и как её подставить в выражении.

• Вопрос задан более двух лет назад
• 224 просмотра

3 комментария

Простой 3 комментария

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

Первый столбец — номера, второй — наименование работы.

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

Пример:
Требуется найти несколько чисел, которым соответствует та или иная работа. Например это будет 1,2,4,6,7,14,17,19,20. В фильтрах можно выделить эти значения. Но если этот перечень будет большим и будет много чисел — глаза сломать.

Вложения

Список.xlsx (9.7 Кб, 32 просмотров)

Лучшие ответы ( 1 )
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

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

Поиск нескольких значений в таблице и вывод их суммы
Доброго времени суток. Есть таблица Продаж: столбцы — дни недели (повторяются); строки -.

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

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

4133 / 2237 / 940
Регистрация: 01.12.2010
Сообщений: 4,624

Сообщение было отмечено Aidwxw как решение

Решение

Aidwxw, Если Вас устраивает фильтр, то скопируйте весь нижеопубликованный код строго в модуль листа и вводите числа(через запятую) в ячейку D1

1 2 3 4 5 6

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$1" Then Range("A1").CurrentRegion.AutoFilter 1, Split(Target, ","), xlFilterValues 'Range("A:B").AutoFilter 1, Split(Target, ","), xlFilterValues End If End Sub

Регистрация: 13.09.2016
Сообщений: 8

pashulka, Спасибо за это решение!!
Как я понял, данный макрос будет искать в столбцах А и B, вне зависимости от того, сколько там будет параметров?
А какие есть ещё варианты?

4133 / 2237 / 940
Регистрация: 01.12.2010
Сообщений: 4,624

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

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$1" Then If Me.FilterMode = True Then Me.ShowAllData Dim t$(), r1 As Range, r2 As Range t = Split(Target, ",") Set r1 = Range("B1", Cells(Rows.Count, "A").End(xlUp)) r1.AutoFilter 1, t, xlFilterValues Set r2 = Worksheets.Add.Range("A1:B1") r1.Copy r2: r2.Columns.AutoFit 'Me.ShowAllData 'Данные-Сортировка и фильтр-Очистить End If End Sub

Множественный ВПР() — выводим несколько значений по ключевому значению в MS EXCEL

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

Пусть дана исходная таблица с номерами заказов и товарами. Номера заказов могут повторяться.

У заказа 2 три повтора, в строке 3, 4 и 6. Перечень заказов не сортирован.

Выведем все наименования товаров для заказа 2, а также количество этих товаров.

Так как в ключевом столбце (Заказ) теперь несколько одинаковых значений, то функция ВПР() не годится — она выведет только самое первое, т.е. товар Манго. Чтобы вывести все 3 значения у заказа 2 создадим служебный столбец рядом с исходной таблицей.

Поместим в него формулу =СЧЁТЕСЛИ($B$8:B8;B8)

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

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

определим позиции повтора в исходной таблице (оранжевый столбец в нижней таблице). Это обычная формула (не формула массива).

Наименование товара выведем с помощью простой формулы =ИНДЕКС(C$8:C$14;$B23), аналогично выведем Количество.

Изменив в желтой ячейке номер заказа на 1, нижняя табличка изменится.

Покажем как работает формула =СУММПРОИЗВ(($B$8:$B$14=$A$20)*($E$8:$E$14=A23)*(СТРОКА($B$8:$B$14)-СТРОКА($B$7))) для заказа 1:

• три выражения $B$8:$B$14=$A$20, $E$8:$E$14=A23 и СТРОКА($B$8:$B$14)-СТРОКА($B$7) порождают 3 массива чисел
• первый массив — это номера позиций искомого заказа в исходной таблице. Выражение дает , т.е.ИСТИНА соответствует номерам позиций 1 и 5 (выделено зеленым). Убедиться в этом просто — достаточно выделить выражение в строке формул и нажать клавишу F9
• второе выражение дает массив , который представляет номер повтора заказа (данный массив соответствует первому повтору). Номер повтора заказа из А23 (1) и из А24 (2) сравнивается номерами повторов заказов в столбце Е. Например, номер повтора 1 есть во многих строках: 1, 2, 3, 7 и часть из них не соответствует заказу №1. Это как раз и показывает массив — значение ИСТИНА стоит у элементов массива №№1, 2, 3, 7
• произведение этих массивов дает множество, которое соответствует и номеру заказа (1) и его повтору в исходной таблице (1 в строке 23, и 2 в строке 24). Вот этот массив , где 1 соответствует ИСТИНА. 1 получается перемножением 2-х ИСТИНА, все остальные комбинации ЛОЖЬ*ЛОЖЬ, ЛОЖЬ*ИСТИНА дают 0. Перемножение происходит попарно.
• наконец, умножая предыдущий массив на массив порядковых номеров в исходной таблице получим только тот номер, который соответствует и номеру заказа и его повтору. Он будет только один, остальные в массиве будут равны 0. Вот этот массив для заказа 1 и его повтора 2: Это 5-я позиция в исходной таблице.
• Функция СУММПРОИЗ() складывает все элементы массива, т.к. ненулевой элемент заведомо только один, то функция отбросит все 0 и выведет позицию.

Как использовать поиск в Excel по одному или нескольким значениям

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

Поиск в Excel

Самый простой и быстрый способ искать по слову, фразе, формуле и т.д в документе это использовать комбинацию клавиш Ctrl и F одновременно. Появится следующее окно.

Здесь понадобится ввести искомое слово, фразу и т.д.

Второй вариант поиска в Excel — на главной странице нажать вкладку «Найти и выделить». Выбираем «Найти», вводим нужные символы и запускается поиск.

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

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

В поле можно использовать подстановочные знаки * и ?.

Обычно их применяют в таких ситуациях:

1. * — может быть использована для замены букв. К примеру, если ввести «к*т», то найдутся все слова, которые начинаются на букву «к» и заканчиваются на «т», кит, кот и т.д. Звездочка заменяет как один символ, так и любое количество. Т.е. в нашем примере найдутся и слова кабинет, кабриолет и т.д.
2. ? — такой подстановочный знак заменяет только одну букву. Например, «к?т», выйдут слова, которые состоят из трех букв «кот», «кит».

Настройки поиска

Если необходимо установить определенные настройки, то в окне необходимо кликнуть «Параметры».

Тут можно задать следующие настройки:

• в строке «Искать» можно ограничить поиск только листом;
• в строке «Просматривать» можно выбрать вариант просмотра по строкам или по столбцам;
• строка «Область» позволяет выбрать искомые символы: формула либо значение;
• отметив галкой «Учитывать регистр» производится нахождение искомой комбинации с учетом регистра;
• если необходимо увидеть ячейку, которая содержит только заданные символы, нужно отметить «Ячейка целиком»;
• во вкладке «Формат» можно выбрать искомое форматирование.

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

Нажимая кнопку «Заменить», можно сразу заменить найденное на указанные значения.

В строку «Найти» нужно ввести искомую комбинацию значений, а в строке «Заменить» те символы, на которые следует заменить найденные.

Поиск по указанному интервалу

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

Этим способом ограничивается диапазон для нахождения комбинации.

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

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

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

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

Составим таблицу.

Формула будет в ячейке Е4, но можно использовать любую другую. Затем придерживаемся алгоритма:

1. В ячейке ставим знак равенства и прописываем функцию: «=ВПР(».
2. Аргумент 1 это Гранит7. Пишем «ВПР(«Гранит7»».
3. Аргумент 2 – это ячейки А4:А20. Формула получается такой: «ВПР(«Гранит7»; А4:А20;».
4. Аргумент 3, цифра 2, т.к. ищем значение во втором столбце. «ВПР(«Гранит7»; А4:А20;2».
5. Аргумент 4, дает понять функции, какое значение нужно искать, точное или приблизительное. Оно может быть ИСТИНА или ЛОЖЬ. ИСТИНА – приблизительно совпавшие значения, а ЛОЖЬ – точные. Мы ищем точные. Выглядит запись так: «ВПР(«Гранит7»; А4:А20;2;ЛОЖЬ)» или «=ВПР(A10;A4:B20;2;ЛОЖЬ)».
6. Кликаем по Enter и получаем искомое.

Программа Excel — начало работы с программой

Аналогичным способом можно добавлять условия в более сложных таблицах.

К примеру, добавим в таблицу столбец с месяцем. Попробуем увидеть в каком месяце Гранит17 стоит 1016.

Составляем формулу, добавляя новые значения. Аргументом 3 будет цифра 3.

Нажимаем ввод и получаем месяц «май».

Функция поиск в Excel

Также для обнаружения можно использовать функции ПОИСК и НАЙТИ.

Сначала рассмотрим функции НАЙТИ. С ее помощью можно автоматически определить позицию в текстовой строке, она различает прописные и строчные буквы.

Пример: найдем положение буквы «а» в слове Гранит6. Введем формулу =НАЙТИ(«а»;A9;3) и получим 3, т.е. «а» третья буква.

Теперь рассмотрим функцию ПОИСК. В данной формуле можно использовать подстановочные символы (? — один, * — несколько). Например, найдем в той же ячейке положение цифры 6. Введем формулу =ПОИСК(«6»;A9), получаем 8.