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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск совпадений, при наличии нескольких значений в ячейке - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск совпадений, при наличии нескольких значений в ячейке (Формулы/Formulas)
Поиск совпадений, при наличии нескольких значений в ячейке
Kerby Дата: Четверг, 19.06.2014, 17:59 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Доброго времени!

Суть проблемы:
Есть база номеров изделий двух фирм (два столбца), а так же столбец в который вписываются номера прочих фирм (при их обнаружении, вносятся через alt+Enter).
При подставлении номера повторно, благодаря наложенному условному форматированию, тут-же загарается "семафор". Но если значение было в ячейке с несколькими значениями, то условное форматирование его не видит.

Вопрос:
Можно как-то научить систему видеть совпадения в ячейкас с несколькими значениями.

Прилагаю простенький файл-пример.
К сообщению приложен файл: __2014-06-19.xlsx (9.0 Kb)
 
Ответить
СообщениеДоброго времени!

Суть проблемы:
Есть база номеров изделий двух фирм (два столбца), а так же столбец в который вписываются номера прочих фирм (при их обнаружении, вносятся через alt+Enter).
При подставлении номера повторно, благодаря наложенному условному форматированию, тут-же загарается "семафор". Но если значение было в ячейке с несколькими значениями, то условное форматирование его не видит.

Вопрос:
Можно как-то научить систему видеть совпадения в ячейкас с несколькими значениями.

Прилагаю простенький файл-пример.
Автор - Kerby
Дата добавления - 19.06.2014 в 17:59
Pelena Дата: Четверг, 19.06.2014, 18:19 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19195
Репутация: 4423 ±
Замечаний: ±

Excel 365 & Mac Excel
Так подойдёт?
К сообщению приложен файл: _2014-06-19.xlsx (9.5 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеТак подойдёт?
Автор - Pelena
Дата добавления - 19.06.2014 в 18:19
Kerby Дата: Четверг, 19.06.2014, 18:28 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Да, спасибо огромное! Ушел анализировать формулу) Плюс!
 
Ответить
СообщениеДа, спасибо огромное! Ушел анализировать формулу) Плюс!
Автор - Kerby
Дата добавления - 19.06.2014 в 18:28
Kerby Дата: Четверг, 19.06.2014, 18:45 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Заменой зоны работы формулы ПОИСК с $A$1:$C$7 на $A:$C отправил эксель в космическое путешествие, до альфы-центара не долетел, грохнул процесс. Похоже надо быть осторжней в своих желаниях...
 
Ответить
СообщениеЗаменой зоны работы формулы ПОИСК с $A$1:$C$7 на $A:$C отправил эксель в космическое путешествие, до альфы-центара не долетел, грохнул процесс. Похоже надо быть осторжней в своих желаниях...
Автор - Kerby
Дата добавления - 19.06.2014 в 18:45
Pelena Дата: Четверг, 19.06.2014, 18:54 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 19195
Репутация: 4423 ±
Замечаний: ±

Excel 365 & Mac Excel
Так должно быстрее работать
Код
=ЕСЛИ(A1<>"";СУММ(--ЕЧИСЛО(ПОИСК(A1;$A:$C)))>1)

Но оперировать со столбцами целиком всё же не рекомендуется

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеТак должно быстрее работать
Код
=ЕСЛИ(A1<>"";СУММ(--ЕЧИСЛО(ПОИСК(A1;$A:$C)))>1)

Но оперировать со столбцами целиком всё же не рекомендуется
Автор - Pelena
Дата добавления - 19.06.2014 в 18:54
Kerby Дата: Четверг, 19.06.2014, 19:02 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Нет, снова попытка межзведного перелета. Я задал до 2222 строки, мне хватит и обаботка долго не идет.

Заметил одну особенность, которую не увидел в начале - а можно сделать так, чтобы и в 3-й колонке выделялась ячейка с совпадением?
 
Ответить
СообщениеНет, снова попытка межзведного перелета. Я задал до 2222 строки, мне хватит и обаботка долго не идет.

Заметил одну особенность, которую не увидел в начале - а можно сделать так, чтобы и в 3-й колонке выделялась ячейка с совпадением?
Автор - Kerby
Дата добавления - 19.06.2014 в 19:02
_Boroda_ Дата: Четверг, 19.06.2014, 19:58 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Сделал 3 типа УФ
Для столбцов А:В
1. Просто поиск повторов в столбцах А:В
2. Есть повтор в столбце С
Код
=ЕСЛИ(A1<>"";ПОИСКПОЗ("*"&A1&"*";$C:$C;))

3. Для столбца С
Код
=ИЛИ(ЕСЛИ($A$1:$B$5<>"";ЕЧИСЛО(ПОИСК("*"&СИМВОЛ(10)&$A$1:$B$5&СИМВОЛ(10)&"*";СИМВОЛ(10)&C1&СИМВОЛ(10)))))


1. и 2. на всем столбце работают быстро.
3. на всем столбце не пробовал, на 3000 ячеек работает быстро.

Файл перевложил и лишний $ в первой формуле убрал.
К сообщению приложен файл: _2014-06-19_3.xlsx (10.1 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеСделал 3 типа УФ
Для столбцов А:В
1. Просто поиск повторов в столбцах А:В
2. Есть повтор в столбце С
Код
=ЕСЛИ(A1<>"";ПОИСКПОЗ("*"&A1&"*";$C:$C;))

3. Для столбца С
Код
=ИЛИ(ЕСЛИ($A$1:$B$5<>"";ЕЧИСЛО(ПОИСК("*"&СИМВОЛ(10)&$A$1:$B$5&СИМВОЛ(10)&"*";СИМВОЛ(10)&C1&СИМВОЛ(10)))))


1. и 2. на всем столбце работают быстро.
3. на всем столбце не пробовал, на 3000 ячеек работает быстро.

Файл перевложил и лишний $ в первой формуле убрал.
Автор - _Boroda_
Дата добавления - 19.06.2014 в 19:58
Kerby Дата: Пятница, 20.06.2014, 13:13 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_, спасибо. Уже пару часов изучаю ваш файл. Работает не совсем корректно.

Почему-то выделяет ячейку А2, заметил, что выделяет ячйки во 2-м столбце при подстановке произвольных значений (пробовал "ssss", "0", "9"). Выделенеие "исчезает" если в первый столбец поставиль какое-либо значение (не всегда).

Так же не выделяет значения в 3-м столбце при наличии совпадений только в нем, как с единичными (вылечил расширением стандартного поиска совпадений), так и с множественными значениями.

Признаться пока не познал "дзен" в вашем задании параметров формулы "ПОИСК". Вы сначала задаете диапазон, а потом текущую ячейку. Если не сложно, разъясните на пальцах как excel обрабатывает такой запрос.

Прикладываю файл, к котором собрал все возможные варианты заполнения ячеек.
К сообщению приложен файл: _2014-06-19_2.xlsx (11.0 Kb)


Сообщение отредактировал Kerby - Пятница, 20.06.2014, 13:14
 
Ответить
Сообщение_Boroda_, спасибо. Уже пару часов изучаю ваш файл. Работает не совсем корректно.

Почему-то выделяет ячейку А2, заметил, что выделяет ячйки во 2-м столбце при подстановке произвольных значений (пробовал "ssss", "0", "9"). Выделенеие "исчезает" если в первый столбец поставиль какое-либо значение (не всегда).

Так же не выделяет значения в 3-м столбце при наличии совпадений только в нем, как с единичными (вылечил расширением стандартного поиска совпадений), так и с множественными значениями.

Признаться пока не познал "дзен" в вашем задании параметров формулы "ПОИСК". Вы сначала задаете диапазон, а потом текущую ячейку. Если не сложно, разъясните на пальцах как excel обрабатывает такой запрос.

Прикладываю файл, к котором собрал все возможные варианты заполнения ячеек.
Автор - Kerby
Дата добавления - 20.06.2014 в 13:13
_Boroda_ Дата: Пятница, 20.06.2014, 13:42 | Сообщение № 9
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Убейте доллар =ЕСЛИ(A1<>"";ПОИСКПОЗ("*"&$A1&"*";$C:$C;)) случайно затесался
Цитата Kerby, 20.06.2014 в 13:13, в сообщении № 8
не выделяет значения в 3-м столбце при наличии совпадений только в нем ... с множественными значениями
И не должен. Формула на это не рассчитана. Выделяет только в случае, если в столбце С есть что-то из столбцов А и В. Поизвращаться конечно можно - сделать сверку каждой части из каждой ячейки столбца С с каждой частью других ячеек из С, но, если даже и получится, то формула будет огромная и тормознутая.
Цитата Kerby, 20.06.2014 в 13:13, в сообщении № 8
не познал "дзен" в вашем задании параметров формулы "ПОИСК". Вы сначала задаете диапазон, а потом текущую ячейку.
На пальцах - ищем все значения диапазона в ячейке. Получаем массив ошибок (если не нашли) и каких-то чисел (если нашли). ЕЧИСЛО преобразовывает все это в ЛОЖЬ и ИСТИНА соответственно. ЕСЛИ($A$1:$B$5<>"" - заранее отсекает пустые ячейки. ИЛИ - если в массиве есть хоть одно ИСТИНА, то дает ИСТИНА

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеУбейте доллар =ЕСЛИ(A1<>"";ПОИСКПОЗ("*"&$A1&"*";$C:$C;)) случайно затесался
Цитата Kerby, 20.06.2014 в 13:13, в сообщении № 8
не выделяет значения в 3-м столбце при наличии совпадений только в нем ... с множественными значениями
И не должен. Формула на это не рассчитана. Выделяет только в случае, если в столбце С есть что-то из столбцов А и В. Поизвращаться конечно можно - сделать сверку каждой части из каждой ячейки столбца С с каждой частью других ячеек из С, но, если даже и получится, то формула будет огромная и тормознутая.
Цитата Kerby, 20.06.2014 в 13:13, в сообщении № 8
не познал "дзен" в вашем задании параметров формулы "ПОИСК". Вы сначала задаете диапазон, а потом текущую ячейку.
На пальцах - ищем все значения диапазона в ячейке. Получаем массив ошибок (если не нашли) и каких-то чисел (если нашли). ЕЧИСЛО преобразовывает все это в ЛОЖЬ и ИСТИНА соответственно. ЕСЛИ($A$1:$B$5<>"" - заранее отсекает пустые ячейки. ИЛИ - если в массиве есть хоть одно ИСТИНА, то дает ИСТИНА
Автор - _Boroda_
Дата добавления - 20.06.2014 в 13:42
Kerby Дата: Пятница, 20.06.2014, 14:41 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_, спасибо за ответ. То же стал приходить к мнению, что сравнивание и выделение ячеек с множественными значениями просто так не решить. Спасибо, что остановили от борьбы с "ветрянными мельницами".

В итоге думаю оставить связку из двух правил:
[vba]
Код
A:C
=ЕСЛИ(A1<>"";СУММ(--ЕЧИСЛО(ПОИСК(A1;$A$1:$C$2222)))>1)

C:C
=ИЛИ(ЕСЛИ($A$1:$B$2222<>"";ЕЧИСЛО(ПОИСК("*"&СИМВОЛ(10)&$A$1:$B$2222&СИМВОЛ(10)&"*";СИМВОЛ(10)&C1&СИМВОЛ(10)))))
[/vba]

С назначением разных цветов.
 
Ответить
Сообщение_Boroda_, спасибо за ответ. То же стал приходить к мнению, что сравнивание и выделение ячеек с множественными значениями просто так не решить. Спасибо, что остановили от борьбы с "ветрянными мельницами".

В итоге думаю оставить связку из двух правил:
[vba]
Код
A:C
=ЕСЛИ(A1<>"";СУММ(--ЕЧИСЛО(ПОИСК(A1;$A$1:$C$2222)))>1)

C:C
=ИЛИ(ЕСЛИ($A$1:$B$2222<>"";ЕЧИСЛО(ПОИСК("*"&СИМВОЛ(10)&$A$1:$B$2222&СИМВОЛ(10)&"*";СИМВОЛ(10)&C1&СИМВОЛ(10)))))
[/vba]

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

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