Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Поиск одного значения в нескольких столбцах - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск одного значения в нескольких столбцах (Формулы/Formulas)
Поиск одного значения в нескольких столбцах
Exsodus1407 Дата: Пятница, 25.06.2021, 20:13 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Добрый день. Подскажите, как сделать. В ячейку G2 вписываю значение, которое нужно найти, оно ищется в столбцах C2:C7, D2:D7, E2:E7. Как только оно находится, то в ячейку H2 выводится значение из ячейки в столбце B2:B7 в соответствующей строке. Например значение Х3, оно находится в ячейке D4, ему соответствует значение A13. Пробовал через ВПР, но оно ищет только в крайнем левом столбце, а у меня их может быть много. Подскажите как это сделать, может через формулы или макросом? Спасибо.
К сообщению приложен файл: 11.xls(23.0 Kb)
 
Ответить
СообщениеДобрый день. Подскажите, как сделать. В ячейку G2 вписываю значение, которое нужно найти, оно ищется в столбцах C2:C7, D2:D7, E2:E7. Как только оно находится, то в ячейку H2 выводится значение из ячейки в столбце B2:B7 в соответствующей строке. Например значение Х3, оно находится в ячейке D4, ему соответствует значение A13. Пробовал через ВПР, но оно ищет только в крайнем левом столбце, а у меня их может быть много. Подскажите как это сделать, может через формулы или макросом? Спасибо.

Автор - Exsodus1407
Дата добавления - 25.06.2021 в 20:13
jakim Дата: Пятница, 25.06.2021, 20:35 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1015
Репутация: 277 ±
Замечаний: 0% ±

Excel 2010
Формула для MS Excel2010+.

Код
=IFERROR(INDEX(B$2:B$700;AGGREGATE(15;6;ROW($1:$1000)/($C$2:$E$700=G$2);ROWS($2:2)));"")
К сообщению приложен файл: Copy_of_11.xlsx(8.5 Kb)
 
Ответить
Сообщение
Формула для MS Excel2010+.

Код
=IFERROR(INDEX(B$2:B$700;AGGREGATE(15;6;ROW($1:$1000)/($C$2:$E$700=G$2);ROWS($2:2)));"")

Автор - jakim
Дата добавления - 25.06.2021 в 20:35
Светлый Дата: Воскресенье, 27.06.2021, 08:10 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1639
Репутация: 437 ±
Замечаний: 0% ±

Excel 2013, 2016
Если приоритет имеют столбцы, то массивная формула:
Код
=ИНДЕКС(B:B;ПРАВБ(МИН(ЕСЛИ(C2:E7=G2;СТОЛБЕЦ(C:E)*100+СТРОКА(2:7)));2))
Если строки, то:
Код
=ИНДЕКС(B:B;МИН(ЕСЛИ(C2:E7=G2;СТРОКА(2:7))))


Программировать проще, чем писать стихи.
 
Ответить
СообщениеЕсли приоритет имеют столбцы, то массивная формула:
Код
=ИНДЕКС(B:B;ПРАВБ(МИН(ЕСЛИ(C2:E7=G2;СТОЛБЕЦ(C:E)*100+СТРОКА(2:7)));2))
Если строки, то:
Код
=ИНДЕКС(B:B;МИН(ЕСЛИ(C2:E7=G2;СТРОКА(2:7))))

Автор - Светлый
Дата добавления - 27.06.2021 в 08:10
Exsodus1407 Дата: Пятница, 30.07.2021, 22:24 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Здравствуйте. Вопрос похожий, поэтому, чтобы не создавать лишнюю тему, спрошу здесь. Если так нельзя, то удалите сообщение и я создам новую тему. В ячейках B3 и E3 нужна формула, которая будет проверять столбцы A, C, D, F и так далее на наличие определенного значения, например 100 и выводить значение из ячейки в 15 строке столбца с найденным значением. В файле примере это 3. Была мысль сделать это через ГПР, но не знаю, как организовать поиск в отдельных не находящихся рядом столбцах, а не таблице. Спасибо.
К сообщению приложен файл: 145.xlsx(8.7 Kb)
 
Ответить
СообщениеЗдравствуйте. Вопрос похожий, поэтому, чтобы не создавать лишнюю тему, спрошу здесь. Если так нельзя, то удалите сообщение и я создам новую тему. В ячейках B3 и E3 нужна формула, которая будет проверять столбцы A, C, D, F и так далее на наличие определенного значения, например 100 и выводить значение из ячейки в 15 строке столбца с найденным значением. В файле примере это 3. Была мысль сделать это через ГПР, но не знаю, как организовать поиск в отдельных не находящихся рядом столбцах, а не таблице. Спасибо.

Автор - Exsodus1407
Дата добавления - 30.07.2021 в 22:24
Светлый Дата: Суббота, 31.07.2021, 00:05 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1639
Репутация: 437 ±
Замечаний: 0% ±

Excel 2013, 2016
Здравствуйте!
Формула массива вводится одновременным нажатием Ctrl+Shift+Enter:
Код
=МИН(ЕСЛИ(Ч(СМЕЩ(B2;СТРОКА(1:12);{0;2;3;5}))=100;Ч(СМЕЩ(B15;;{0;2;3;5}))))


Программировать проще, чем писать стихи.
 
Ответить
СообщениеЗдравствуйте!
Формула массива вводится одновременным нажатием Ctrl+Shift+Enter:
Код
=МИН(ЕСЛИ(Ч(СМЕЩ(B2;СТРОКА(1:12);{0;2;3;5}))=100;Ч(СМЕЩ(B15;;{0;2;3;5}))))

Автор - Светлый
Дата добавления - 31.07.2021 в 00:05
Exsodus1407 Дата: Суббота, 31.07.2021, 06:40 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - Exsodus1407
Дата добавления - 31.07.2021 в 06:40
Светлый Дата: Суббота, 31.07.2021, 19:37 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1639
Репутация: 437 ±
Замечаний: 0% ±

Excel 2013, 2016
Можно.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеМожно.

Автор - Светлый
Дата добавления - 31.07.2021 в 19:37
Exsodus1407 Дата: Суббота, 31.07.2021, 20:38 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Спасибо, попробовал, с числами работает, но у меня еще может быть текстовое значение в ячейках в 15 строке. А с текстом у меня не работает ваша формула.
 
Ответить
СообщениеСпасибо, попробовал, с числами работает, но у меня еще может быть текстовое значение в ячейках в 15 строке. А с текстом у меня не работает ваша формула.

Автор - Exsodus1407
Дата добавления - 31.07.2021 в 20:38
Светлый Дата: Суббота, 31.07.2021, 22:17 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1639
Репутация: 437 ±
Замечаний: 0% ±

Excel 2013, 2016
Тогда эту формулу:
Код
=ИНДЕКС(B15:G15;МИН(ЕСЛИ(ИНДЕКС(B3:G14;Ч(ИНДЕКС(СТРОКА(1:12);));Ч(ИНДЕКС({1;3;4;6};)))=100;{1;3;4;6})))


Программировать проще, чем писать стихи.
 
Ответить
СообщениеТогда эту формулу:
Код
=ИНДЕКС(B15:G15;МИН(ЕСЛИ(ИНДЕКС(B3:G14;Ч(ИНДЕКС(СТРОКА(1:12);));Ч(ИНДЕКС({1;3;4;6};)))=100;{1;3;4;6})))

Автор - Светлый
Дата добавления - 31.07.2021 в 22:17
Exsodus1407 Дата: Воскресенье, 01.08.2021, 07:51 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Спасибо, работает. Еще вопрос, если у меня не указано в столбцах искомое значение 100, либо указано любое другое цифровое или буквенное значение, ваша формула выдает 1. Можно чтобы был 0 или пустое значение? И еще хотел спросить, можете подсказать, что означают в формуле выбранные строки с 1 по 12? В таблице строк больше. Я хочу разобраться, чтобы когда буду делать свою таблицу, понимать, как работает формула.


Сообщение отредактировал Exsodus1407 - Воскресенье, 01.08.2021, 07:55
 
Ответить
СообщениеСпасибо, работает. Еще вопрос, если у меня не указано в столбцах искомое значение 100, либо указано любое другое цифровое или буквенное значение, ваша формула выдает 1. Можно чтобы был 0 или пустое значение? И еще хотел спросить, можете подсказать, что означают в формуле выбранные строки с 1 по 12? В таблице строк больше. Я хочу разобраться, чтобы когда буду делать свою таблицу, понимать, как работает формула.

Автор - Exsodus1407
Дата добавления - 01.08.2021 в 07:51
Светлый Дата: Воскресенье, 01.08.2021, 11:06 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1639
Репутация: 437 ±
Замечаний: 0% ±

Excel 2013, 2016
Немного расширить диапазон:
Код
=ИНДЕКС(A15:G15;МИН(ЕСЛИ(ИНДЕКС(A3:G20;Ч(ИНДЕКС(СТРОКА(1:12);));Ч(ИНДЕКС({2;4;5;7};)))=100;{2;4;5;7})))
строки с 1 по 12
Рассматриваем 12 строк.
Или так можно:
Код
=ИНДЕКС(A15:G15;МИН(ЕСЛИ(ИНДЕКС(A:G;Ч(ИНДЕКС(СТРОКА(3:14);));Ч(ИНДЕКС({2;4;5;7};)))=100;{2;4;5;7})))
Формулы массива.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеНемного расширить диапазон:
Код
=ИНДЕКС(A15:G15;МИН(ЕСЛИ(ИНДЕКС(A3:G20;Ч(ИНДЕКС(СТРОКА(1:12);));Ч(ИНДЕКС({2;4;5;7};)))=100;{2;4;5;7})))
строки с 1 по 12
Рассматриваем 12 строк.
Или так можно:
Код
=ИНДЕКС(A15:G15;МИН(ЕСЛИ(ИНДЕКС(A:G;Ч(ИНДЕКС(СТРОКА(3:14);));Ч(ИНДЕКС({2;4;5;7};)))=100;{2;4;5;7})))
Формулы массива.

Автор - Светлый
Дата добавления - 01.08.2021 в 11:06
Exsodus1407 Дата: Воскресенье, 01.08.2021, 16:53 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

То есть, если например в таблице 20 строк, нужно указывать со строки начала таблицы по строку окончания таблицы, например с 3 по 20? Просто ваша формула нормально работает даже если указаны с 1 по 12 строки, а значение я ввожу например в 14 строку.


Сообщение отредактировал Exsodus1407 - Воскресенье, 01.08.2021, 16:58
 
Ответить
СообщениеТо есть, если например в таблице 20 строк, нужно указывать со строки начала таблицы по строку окончания таблицы, например с 3 по 20? Просто ваша формула нормально работает даже если указаны с 1 по 12 строки, а значение я ввожу например в 14 строку.

Автор - Exsodus1407
Дата добавления - 01.08.2021 в 16:53
Светлый Дата: Воскресенье, 01.08.2021, 18:03 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1639
Репутация: 437 ±
Замечаний: 0% ±

Excel 2013, 2016
например с 3 по 20?
Да.
например в 14 строку
Поэкспериментируйте.


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
например с 3 по 20?
Да.
например в 14 строку
Поэкспериментируйте.

Автор - Светлый
Дата добавления - 01.08.2021 в 18:03
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск одного значения в нескольких столбцах (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования Jobsora jooble
© 2010-2021 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!