Страницы: 1
RSS
Формулой найти значение по частичному совпадению текста
 
Всем добра! Такая задача (она описана в заголовке), не знаю, решаемо ли формулами, но если кто откликнется, буду очень рад.
Чтобы долго не объяснять, лучше сразу приложу файл
В нем формула нужна на листе "Лист1" ("Лист2" - вспомогательный, на нем таблица из которой формула должна искать)

Если будут вопросы - спрашивайте, но мне кажется, что в файле должно быть понятно.
 
Чет ерунду какую-то написал. Все. спать  Лучше: Всем спать (vikttur хулиганит) :)
Изменено: Sanja - 12.01.2016 00:03:34
Согласие есть продукт при полном непротивлении сторон
 
Формула массива:

=ЕСЛИОШИБКА(ИНДЕКС(Лист2!$B$2:$B$5;ПОИСКПОЗ(9E+307;ПОИСКПОЗ(Лист2!$A$2:$A$5;A2;0)));"-")
Изменено: KL - 11.01.2016 23:45:41
KL
 
Формула массива:
=ИНДЕКС(Лист2!$B$2:$B$6;МИН(ЕСЛИ(ЕЧИСЛО(ПОИСК(Лист2!$A$2:$A$6;A2));СТРОКА($A$2:$A$6)-1)))
В таблице добавлена пустая строка для обхода ошибки.
 
спасибо огромное за решение!!! но, если с формулой vikttur хоть что-то понятно как она работает, то вот формула KL неясна вообще.. зачем там две вложенные внутрь себя ПОИСКПОЗ() ??
Если можно, прошу Вас, KL, можете объяснить?
 
(Неуверенно) А можно я? :)

ПОИСКПОЗ(макс_возможное_число;ПОИСКПОЗ(тексты;A3;0))
Вложенная функция - массив из ошибок #Н/Д и позиции текста, который есть В А3
Внешняя функция - игнорирует ошибки, находит последнее число в массиве, полученном вложенной функцией.
Если числа нет - ошибка, которая убирается функцией ЕСЛИОШИБКА

Обратите внимание: вложенная ПОИСКПОЗ ищет точное совпадение, внешняя - неточное.
 
Цитата
vikttur написал: (Неуверенно) А можно я?
Мне лучше и не объяснить :)
KL
 
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/СЧЁТЕСЛИ(Лист1!A2;Лист2!$A$2:$A$5);Лист2!$B$2:$B$5);"-")
Изменено: Михаил С. - 12.01.2016 05:36:23
 
Михаил С., спасибо большое за еще одно решение, в нем даже ИНДЕКС и ПОИСКПОЗ не используется! (хотя, тоже, непонятно как все это работает и почему в качестве "диапазона" в СЧЁТЕСЛИ выступает одна ячейка)

:)
Изменено: Zhukov_K - 12.01.2016 09:39:16
 
Какая разница - одна ячейка или сто? В любом случае это диапазон.
1/СЧЁТЕСЛИ(Лист1!A2;Лист2!$A$2:$A$5)
1/число>0, 1/0
Массив из ошибок и числовых значений. ПРОСМОТР игнорирует ошибки, находит последнее числовое значение и показывает значение столбца В на этой позиции.
1/0 нужно для того, чтобы создать ошибку, иначе будет 0 и ПРОСМОТР воспримет его, как числовое значение (что правильно).
 
vikttur, спасибо большое Вам еще раз! за такие доступные и понятные объяснения! очень признателен!!
 
Добавлю, что ПОИСКПОЗ, ПРОСМОТР, ВПР и ГПР - по сути вариации одной и той же операции поиска и используют одни и те же алгоритмы. ИНДЕКС - едва ли не самая быстрая в пересчете функция в работе с массивами :) В большинстве случаев СУММЕСЛИ и СЧЁТЕСЛИ значительно медленнее всилу опять таки алгоритма, правда не в данном случае, т.к. количество операций сравнения одинаковое. Это я к тому, что я бы особо не радовался отсутствию ИНДЕКС и ПОИСКПОЗ взамен на СЧЁТЕСЛИ в формулах массива.
KL
 
понял, как раз хотел было спросить какая же из формул будет считать быстрее, но судя из того, что вы сказали - Ваша, а не Михаила С. и vikttur ? или я неправильно понял?
 
В данном (особом) случае различия если и будут, то минимальные :)

Изменено:

Пожалуй я поторопился с предыдущей ничьей :) На непустом списке в 5000 строк и при 25 непустых стоках критериев, средняя скорость по 5-и промерам такая:
KL 79 мсек
Vikttur   912 мсек (думаю, причина в конструкции ЕСЛИ(...)
Михаил С 158 мсек
Изменено: KL - 12.01.2016 19:02:29
KL
 
Цитата
KL написал:
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!$B$2:$B$5;ПОИСКПОЗ(9E+307;ПОИСКПОЗ(Лист2!$A$2:$A$5;A2;0)));"-")
Господа! Сохранил себе пример, хотел переделать под свои нужды, но если я меняю хоть что-то формула перестаёт работать...

Если меняю текст в существующих диапазонах, всё работает.
Если расширить диапазон Лист2!$B$2:$B$5 до Лист2!$B$2:$B$6 (аналогично с Лист2!$A$2:$A$5) уже выдаёт ошибку, в данном случае "-".
Добавляю название существующему диапазону Лист2!$B$2:$B$5, также выдаёт ошибку.
Даже если просто снять фиксацию диапазона $ тоже перестаёт работать...

Не подскажете, в чём может быть проблема?
 
Цитата
KL написал:
Формула массива:
Kastrulkin, это учитываете?
 
Цитата
Михаил С. написал:
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/СЧЁТЕСЛИ(Лист1!A2;Лист2!$A$2:$A$5);Лист2!$B$2:$B$5);"-")
Данная формула мне очень помогла. Но хотелось бы спросить, как добавить дополнительную проверку на пустую ячейку столба Лист1!A2?
Если Лист1!A2 пусто, вывести "-"
Спасибо.
Изменено: Daniel Defo - 13.10.2021 13:53:26
 
=Если(Епусто(Лист1!A2);"-";....)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Ігор Гончаренко, Спасибо. Делал также, но в конце формулы, поэтому и не получалось)))  
 
Добрый день! У меня похожая задача, но есть нюанс, при котором приведенные формулы не срабатывают. Помогите, пожалуйста, разобраться. Суть задачи: есть столбец с названиями этажа квартир от разных компаний. Каждая называет их по-разному. Например, кто-то первый этаж называет просто "1", кто-то "Этаж 1", кто-то "1 этаж" и т.п. Мне надо привести все названия к единому стандарту. Например, первый этаж всегда только "1", второй - только "2" и т.д. И все бы ничего но при использовании формулы для, например, "Этаж 12" формула "видит" только первую цифру и выводит "1", а не "12". Файл с примером прикладываю
 
вот файл
 
немного подправил файл (добавил столбец с желаемым результатом)
Страницы: 1
Наверх