Бахтиёр, дело в том, что ваша формула не работает, если в поисковую строку я вбиваю 200 и более слов (или словосочитаний с разделителем). Как можно преодолеть это ограничение? Причина, по-моему, в том, что сама ячейка маленькая. Искомые слова или словосочитания можно поместить в столбик?
написал: Вручную вбиваете в поиск 200+ слов? А смысл в этой рутинной работе?
Все тоже самое, только ищем не 3 фамилии, а 200 более. Предположим, в списке (столбце А) 10000 строк. Ваша формула, которую вписываем, скажем, в ячейку С1, легко их находит. Когда-же ввожу 100 или 200 слов, то ячейка С1 ("уже не выдержвает нагрузку" и) выдает ошибку. Полагаю, если поисковый список (скажем, 200 фамилий) поместить банально в столбец С (диапазон С1-С200), то проблема разешиилась-бы. Но в интернете я не нашел формулы, которая переводит содержимое ячейки С1 (с формулой в строчку,) в диапазон С1-С200. Грубо говоря, поисковый текст надо поместить в столбец С, где каждая фамилия - отдельная (новая) строка.
Там нет ни формулы ни примера (как-бы это выглядело в Экселе). Там написано: раскидать на B1, B2..B5. Это мне не подходит. Мне надо поисковый список поместить в диапазоне С1-С200. Кстати, результат на поисковый запрос не всегда выдает корректно. Например, мне нужно найти только Redbox Music, но поиск мне выдает также и Redbox Cinema (т.е. и те словосочитания, где встречается Redbox).
Вообще, хотелось-бы без макроса. Разве нельзя содержимое ячейки (строки с формулой) перенести в столбец? Петров, Сидоров, Иванов, Голованов, Сердюков... (200 имен) надо превратить в Петров Сидоров Иванов Голованов ...
как найти в списке несколько фамилий, напр. Иванов, Петров, Сидоров?
Расширенный фильтр позволяет решить вашу задачу без всяких формул и макросов Исходный список в столбце А Условия выбора в столбце B Результат в столбце D
написал: Расширенный фильтр позволяет решить вашу задачу без всяких формул и макросов
Да, но я не понял, как тут искать и за что тянуть (вниз). Если искомые фамилии Иванов, Петров, Бонч-Бруевич, то почему в столбце D больше фамилий? Я не вижу формулу. Можете ее тут привести?
Я снова не понял. Как это будет работать, если мне надо ввести в столбик поисковый список (200-500 имен)? Приведенная таблица какая-то "замурованная" - дает мне лишь результат поиска трех фамилий, что я просил вчера. А что, если завтра мне надо найти 200-500 фамилий?! Куда мне их вписать, за что потянуть, чтобы увидеть результат? Должно быть нечто гибкое, динамичное, меняющееся, а не разовое "окирпиченное".
На листе "Лист1" 160 фамилий На листе "Лист2" 500 фамилий Отфильтруйте 1 (единички) в столбце B на "Лист2" --> Отфильтруются те фамилии, которые есть на "Лист1"
Теперь, если вы измените фамилии на "Лист1" (например, скопируете откуда-то и вставите на столбец A на "Лист1" другие 100 или 200 или больше фамилий) --> И заново отфильтруете 1 (единички) в столбце B на "Лист2" --> То - на "Лист2" отфильтруются те фамилии, которые вы вставили на "Лист1"
Бахтиёр, все вроде работает. Но я хотел-бы в листе 2 столбец формулы (ячейка В2) переместить в С2 (в В у меня будут спаренные со столбцом А данные). Скопировал, формулу из В2 в С2, но так не работает. Как подправить формулу?
И еще важный момент. Мне нужно, чтобы поиск велся не по штучным словам, что в 1-м листе, а по принципу нахождения конкретного слова из предложения или набора знаков (их может быть несколько десятков). Скажем, в листе 1: Позавчера Петров скандалил со своей соседкой. Задав в поиске Петров, хочу получить Позавчера Петров скандалил со своей соседкой. То есть, поиск должен отлавливать не только слово Петров, но и словосочитания со знаками, где встречается слово Петров. Еще лучше, чтоб была возможность найти точное совпадение, взятое в кавычки, например, "Позавчера Петров"., Можно отредактировать формулу?
Нашел в интернете формулу, которая приблизила решение моей задачи на 99%. =СЧЁТЕСЛИ(A2:A300;"*Музыка*") То есть, она находит слово Музыка в ячейках, где есть это слово, независимо от количества знаков до и после него (для этого в формуле применил символ *). Как сделать, чтобы поиск нашел мне в той же таблице еще слова, Кино, Сериал, Криминал, Документал? Я ставил после "*Музыка*" разные разделители, напр.,: ; / , но ни один из них них не подошел. То есть ошибку выдает при =СЧЁТЕСЛИ(A2:A300;"*Музыка*";"*Кино*";"*Сериал*") Почему так?
Эта формула вернёт: Сколько раз в диапазоне A2:A300 встречаются слова: "Музыка", "музыка", "Кино", "кино", "Сериал", "сериал" Она вернёт ОБЩЕЕ КОЛИЧЕСТВО СЛОВ, а НЕ КОЛИЧЕСТВО ЯЧЕЕК. Это ли вам нужно?
Может вам нужно напротив ячейки A2 написать =СУММ(СЧЁТЕСЛИ(A2;{"*Музыка*";"*Кино*";"*Сериал*"})) и протянуть формулу вниз?
А то, я запутался в ваших условиях. Может ВАМ стоит лучше создать новую тему, с файлом-примером: показав что имеется сейчас? и чего хочется добиться формулами? (то есть вручную записав, что должны фернут формулы)
Формула вернет: 0 - Если ни одного из этих слов нет ("Звучал хороший музон") 1 - Если только один из этих слов присутствует ("Звучала хорошая музыка") 2 - Если двое из этих слов присутствуют ("Звучала хорошая музыка из кинофильма "Белые лебеди"") 3 - Если трое из этих слов присутствуют ("Звучала хорошая музыка из киносериала "Авоська"") ...
написал: Может лучше создать новую тему, с файлом-примером: показав что имеется сейчас? и чего хочется добиться формулами? (то есть вручную записав, что должны фернут формулы)
Да, в исходном столбце А может быть максимум аж строк - 64000. Пока тестирую, получается. А подсветить найденные ячейки можно?
Нашел ошибку. Занес в поисковый запрос tivibu, но получил Tivibu Spor. Как откорректировать (формулу) заставить находить ячейки только с tivibu?
Excaz, если вам нужно как-то узнать/отметить те ячейки в диапазоне A2:A300 или A2:A10000 или A2:A65000, в которых содержатся слова: "Музыка", "Кино", "Сериал", то: Напишите в ячейку B2 или С2 или K2, без разницы, в любом свободном столбце в ячейку во 2-й строке (предположим вы написали в K2) формулу: =СУММ(СЧЁТЕСЛИ(A2;{"*Музыка*";"*Кино*";"*Сериал*"})) Скопируйте формулу и протяните вниз до нужного вам места. Теперь установите фильтр в 1-й строке и отфильтруйте в столбце K по условию >0 --> отфильтровались те строки, в которых имеются искомые слова. Теперь вы можете закрасить их.
Или же если перед вами стоит задача в конечном итоге оставить только те строки, в которых есть искомы слова, то: Вы после того, как установили фильтр, отфильтруйте столбец K по условию =0 --> отфильтровались те строки, в которых НЕ имеются искомые слова. Можно удалить их (СТРОКИ).
написал: Скопируйте формулу и протяните вниз до нужного вам места.
Нашел ошибку. Занес в поисковый запрос tivibu, но получил Tivibu Spor. Как откорректировать (формулу) и заставить находить ячейки точно по заданному критерию, в данном случае с tivibu?
Вот ещё макрос. 1) Скопируйте в мой файл нужные данные. 2) Нажмите Alt+F8 3) Запустите макрос "Начинай" 4) Запишите в поля на появившейся форме один или несколько искомых слов (каждое слово в отдельное поле) --> нажмите ok 5) Макрос попросит выделить диапазон, выделите диапазон с нужными словосочетаниями --> нажмите ok 6) Макрос покрасит цвет фона на красный в ячейках, где нашёл искомые слова.
Если устроит, остаётся переделать мой файл в надстройку, подключить и можно будет использовать в любом файле. Если что, пароль на VBA-проект: 1
Да прочитайте справку по расширенному фильтру, в конце концов. Он делает именно то, что вы хотите без всяких формул и быстро. Единственно не привычно, что стрелочек нет - сразу итог как будто так и было . Там все достаточно просто. Тут подробно. https://www.planetaexcel.ru/techniques/2/197/
написал: А в чем проблема установить хотя бы Excel 2007?Не может быть у вас такой древний компьютер... ))
Мой любимый Эксель 2003 года и все, что мне нужно здесь есть. Зачем мне переходить на более новые версии, которые только на нервы действуют своей усложненностью. А почему не работает моя формула с Music?