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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск значений по списку - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск значений по списку (Формулы)
Поиск значений по списку
Николай Дата: Вторник, 10.12.2013, 16:52 | Сообщение № 1
Группа: Гости
Всем добрый день. Помогите пожалуйста.
Имеется две таблицы.
Пример первой

Номер телефона
89562533535
9855558888
79852585555
89596962525, 85896569696
78524441525,
9563235522

Пример второй

Номер телефона
9855558888
85896569696
и т.д.

Теперь к главному:
в первой таблице в одной ячейке может быть один, два номера телефона, + еще какой то текст или запятые
во второй только 1 телефон в каждой ячейке без лишних символов.

Нужно сравнить эти списки что бы понять, какие телефоны из списка1 соответствуют списку2
ВПР не помогает, т.к. в первом может в одной ячейке быть несколько номером + запятые и лишние пробелы, во втором только 1 номер.
До меня пользовались ручным поиском. Т.е. нажимаем cntrl+F копируем один из номеров из списка 2 и ищем в списке 1.
Но сейчас нужно сравнить сотни телефонов, и в ручную это сделать невозможно.
Помогите, пожалуйста.
 
Ответить
СообщениеВсем добрый день. Помогите пожалуйста.
Имеется две таблицы.
Пример первой

Номер телефона
89562533535
9855558888
79852585555
89596962525, 85896569696
78524441525,
9563235522

Пример второй

Номер телефона
9855558888
85896569696
и т.д.

Теперь к главному:
в первой таблице в одной ячейке может быть один, два номера телефона, + еще какой то текст или запятые
во второй только 1 телефон в каждой ячейке без лишних символов.

Нужно сравнить эти списки что бы понять, какие телефоны из списка1 соответствуют списку2
ВПР не помогает, т.к. в первом может в одной ячейке быть несколько номером + запятые и лишние пробелы, во втором только 1 номер.
До меня пользовались ручным поиском. Т.е. нажимаем cntrl+F копируем один из номеров из списка 2 и ищем в списке 1.
Но сейчас нужно сравнить сотни телефонов, и в ручную это сделать невозможно.
Помогите, пожалуйста.
Автор - Николай
Дата добавления - 10.12.2013 в 16:52
Николай Дата: Вторник, 10.12.2013, 16:57 | Сообщение № 2
Группа: Гости
Уточнение.
В списке2 телефоны всегда начинаются с кода, т.е. без 8 или 7.
А в первом списке может быть и с 7 и с 8
 
Ответить
СообщениеУточнение.
В списке2 телефоны всегда начинаются с кода, т.е. без 8 или 7.
А в первом списке может быть и с 7 и с 8
Автор - Николай
Дата добавления - 10.12.2013 в 16:57
nicholas91 Дата: Среда, 11.12.2013, 08:19 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Pelena, вот пример файла. В нем две страницы.
К сообщению приложен файл: 0928144.xlsx (10.2 Kb)
 
Ответить
СообщениеPelena, вот пример файла. В нем две страницы.
Автор - nicholas91
Дата добавления - 11.12.2013 в 08:19
Pelena Дата: Среда, 11.12.2013, 08:50 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 19182
Репутация: 4420 ±
Замечаний: ±

Excel 365 & Mac Excel
Условным форматированием подойдет?
К сообщению приложен файл: 6907354.xlsx (10.7 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеУсловным форматированием подойдет?
Автор - Pelena
Дата добавления - 11.12.2013 в 08:50
nicholas91 Дата: Среда, 11.12.2013, 08:59 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Pelena, Да супер! Можете поподробнее рассказать про это?
 
Ответить
СообщениеPelena, Да супер! Можете поподробнее рассказать про это?
Автор - nicholas91
Дата добавления - 11.12.2013 в 08:59
Pelena Дата: Среда, 11.12.2013, 09:07 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 19182
Репутация: 4420 ±
Замечаний: ±

Excel 365 & Mac Excel
Вкладка Главная -- Условное форматирование -- Управление правилами, смотрим формулу
Код
=ЕЧИСЛО(ПРОСМОТР(2;1/ПОИСК(СМЕЩ('Список 2'!$B$1;;;СЧЁТЗ('Список 2'!$B:$B));B3)))

Про работу функции ПРОСМОТР(2;1/ПОИСК(... подробно рассказывала здесь
Функция СМЕЩ задает динамический диапазон, чтобы при добавлении номеров на лист2 они автоматически попадали в поиск

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВкладка Главная -- Условное форматирование -- Управление правилами, смотрим формулу
Код
=ЕЧИСЛО(ПРОСМОТР(2;1/ПОИСК(СМЕЩ('Список 2'!$B$1;;;СЧЁТЗ('Список 2'!$B:$B));B3)))

Про работу функции ПРОСМОТР(2;1/ПОИСК(... подробно рассказывала здесь
Функция СМЕЩ задает динамический диапазон, чтобы при добавлении номеров на лист2 они автоматически попадали в поиск
Автор - Pelena
Дата добавления - 11.12.2013 в 09:07
nicholas91 Дата: Среда, 11.12.2013, 09:09 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Pelena, спасибо большое. сейчас буду пробовать. А если не не нужно что бы они автоматически попадали в поиск?, т.е. есть два готовый списка и к ним ничего добавляться уже не будет.
 
Ответить
СообщениеPelena, спасибо большое. сейчас буду пробовать. А если не не нужно что бы они автоматически попадали в поиск?, т.е. есть два готовый списка и к ним ничего добавляться уже не будет.
Автор - nicholas91
Дата добавления - 11.12.2013 в 09:09
Pelena Дата: Среда, 11.12.2013, 09:12 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 19182
Репутация: 4420 ±
Замечаний: ±

Excel 365 & Mac Excel
Тогда формула упростится, т.е. Вы укажете конкретный диапазон, в Вашем примере 'Список 2'!$B$2:$B$7
Код
=ЕЧИСЛО(ПРОСМОТР(2;1/ПОИСК('Список 2'!$B$2:$B$7;B3)))

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеТогда формула упростится, т.е. Вы укажете конкретный диапазон, в Вашем примере 'Список 2'!$B$2:$B$7
Код
=ЕЧИСЛО(ПРОСМОТР(2;1/ПОИСК('Список 2'!$B$2:$B$7;B3)))
Автор - Pelena
Дата добавления - 11.12.2013 в 09:12
китин Дата: Среда, 11.12.2013, 09:18 | Сообщение № 9
Группа: Модераторы
Ранг: Экселист
Сообщений: 7015
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
интересно,а почему я в файле ничего не вижу?
а потому?что у меня 2007 и не поддерживает ссылки на другие листы в УФ

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Сообщение отредактировал китин - Среда, 11.12.2013, 09:23
 
Ответить
Сообщениеинтересно,а почему я в файле ничего не вижу?
а потому?что у меня 2007 и не поддерживает ссылки на другие листы в УФ
Автор - китин
Дата добавления - 11.12.2013 в 09:18
Pelena Дата: Среда, 11.12.2013, 09:19 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 19182
Репутация: 4420 ±
Замечаний: ±

Excel 365 & Mac Excel
Совсем ничего? %)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеСовсем ничего? %)
Автор - Pelena
Дата добавления - 11.12.2013 в 09:19
nicholas91 Дата: Среда, 11.12.2013, 09:20 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Pelena, В3 - это ячейка с листа 1, где начинается форматирование. Верно?
 
Ответить
СообщениеPelena, В3 - это ячейка с листа 1, где начинается форматирование. Верно?
Автор - nicholas91
Дата добавления - 11.12.2013 в 09:20
Pelena Дата: Среда, 11.12.2013, 09:23 | Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 19182
Репутация: 4420 ±
Замечаний: ±

Excel 365 & Mac Excel
Верно

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВерно
Автор - Pelena
Дата добавления - 11.12.2013 в 09:23
китин Дата: Среда, 11.12.2013, 09:24 | Сообщение № 13
Группа: Модераторы
Ранг: Экселист
Сообщений: 7015
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Елена совсем ничего.ни одного правила и ни одной формулы

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
СообщениеЕлена совсем ничего.ни одного правила и ни одной формулы
Автор - китин
Дата добавления - 11.12.2013 в 09:24
Pelena Дата: Среда, 11.12.2013, 09:26 | Сообщение № 14
Группа: Админы
Ранг: Местный житель
Сообщений: 19182
Репутация: 4420 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата китин, 11.12.2013 в 09:18, в сообщении № 10
почему я в файле ничего не вижу?

А так?
Версии до 2010 офиса не позволяют в УФ использовать диапазоны с других листов. Сделала именованный
К сообщению приложен файл: 5010229.xlsx (10.5 Kb) · 0928144.xls (26.5 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата китин, 11.12.2013 в 09:18, в сообщении № 10
почему я в файле ничего не вижу?

А так?
Версии до 2010 офиса не позволяют в УФ использовать диапазоны с других листов. Сделала именованный
Автор - Pelena
Дата добавления - 11.12.2013 в 09:26
_Boroda_ Дата: Среда, 11.12.2013, 09:33 | Сообщение № 15
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Еще вариант для условного форматирования
Код
=ИЛИ(-ЕЧИСЛО(ПОИСК('Список 2'!$B$2:$B$7;B3)))

Кстати, да. Лена вовремя про другой лист напомнила. Файл _2 с именованным диапазоном для Excel<=2007
К сообщению приложен файл: 6907354_1.xlsx (12.2 Kb) · 6907354_2.xlsx (10.8 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕще вариант для условного форматирования
Код
=ИЛИ(-ЕЧИСЛО(ПОИСК('Список 2'!$B$2:$B$7;B3)))

Кстати, да. Лена вовремя про другой лист напомнила. Файл _2 с именованным диапазоном для Excel<=2007
Автор - _Boroda_
Дата добавления - 11.12.2013 в 09:33
Pelena Дата: Среда, 11.12.2013, 09:35 | Сообщение № 16
Группа: Админы
Ранг: Местный житель
Сообщений: 19182
Репутация: 4420 ±
Замечаний: ±

Excel 365 & Mac Excel
[offtop]Всё время забываю про ИЛИ girl_sad [/offtop]

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение[offtop]Всё время забываю про ИЛИ girl_sad [/offtop]
Автор - Pelena
Дата добавления - 11.12.2013 в 09:35
nicholas91 Дата: Среда, 11.12.2013, 09:37 | Сообщение № 17
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Всем спасибо большое за помощь! Все работает. Пойду и я теперь работать)
 
Ответить
СообщениеВсем спасибо большое за помощь! Все работает. Пойду и я теперь работать)
Автор - nicholas91
Дата добавления - 11.12.2013 в 09:37
китин Дата: Среда, 11.12.2013, 09:57 | Сообщение № 18
Группа: Модераторы
Ранг: Экселист
Сообщений: 7015
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Елена,Саша спасибо увидел :D

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
СообщениеЕлена,Саша спасибо увидел :D
Автор - китин
Дата добавления - 11.12.2013 в 09:57
rsilver Дата: Четверг, 25.05.2017, 18:21 | Сообщение № 19
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Подскажите пожалуйста формулу, если необходимо искать текстовые строки... к примеру есть два списка (или столбца): Список1 и Список2 - каждый содержит несколько слов, разделенных пробелами. Задача выделить ячейки из Списка1, в которых есть фразы из Списка2. Указанная в этой беседе формула к сожалению работает только с цифрами...
 
Ответить
СообщениеПодскажите пожалуйста формулу, если необходимо искать текстовые строки... к примеру есть два списка (или столбца): Список1 и Список2 - каждый содержит несколько слов, разделенных пробелами. Задача выделить ячейки из Списка1, в которых есть фразы из Списка2. Указанная в этой беседе формула к сожалению работает только с цифрами...
Автор - rsilver
Дата добавления - 25.05.2017 в 18:21
Pelena Дата: Четверг, 25.05.2017, 18:37 | Сообщение № 20
Группа: Админы
Ранг: Местный житель
Сообщений: 19182
Репутация: 4420 ±
Замечаний: ±

Excel 365 & Mac Excel
rsilver, прочитайте Правила форума и создайте свою тему.
Эта тема закрыта

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщениеrsilver, прочитайте Правила форума и создайте свою тему.
Эта тема закрыта
Автор - Pelena
Дата добавления - 25.05.2017 в 18:37
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск значений по списку (Формулы)
  • Страница 1 из 1
  • 1
Поиск:

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