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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск соответствия в двух столбиках по части содержимого - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск соответствия в двух столбиках по части содержимого (Формулы/Formulas)
Поиск соответствия в двух столбиках по части содержимого
PsyArcus Дата: Понедельник, 21.03.2016, 15:39 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 60% ±

Excel 2010
Коллеги, добрый день!

Кто может помочь с задачкой в экселе?

По сути надо сравнить 2 столбика.
Найти во втором столбике в ячейках присутствие содержимого из первого столбика.

Т.е у меня в первом столбике домены сайтов.

Во втором столбике e-mail, где после @ может быть домен из первого столбика.

Надо выяснить, сколько email-ов во втором столбике имеют домен из первого.
И соответственно разместить в третьем столбике ячейки с email напротив ячеек из первого, если у них общий домен.

Помогите пожалуйста изобрести все эти формулы для этого?
 
Ответить
СообщениеКоллеги, добрый день!

Кто может помочь с задачкой в экселе?

По сути надо сравнить 2 столбика.
Найти во втором столбике в ячейках присутствие содержимого из первого столбика.

Т.е у меня в первом столбике домены сайтов.

Во втором столбике e-mail, где после @ может быть домен из первого столбика.

Надо выяснить, сколько email-ов во втором столбике имеют домен из первого.
И соответственно разместить в третьем столбике ячейки с email напротив ячеек из первого, если у них общий домен.

Помогите пожалуйста изобрести все эти формулы для этого?

Автор - PsyArcus
Дата добавления - 21.03.2016 в 15:39
Nic70y Дата: Понедельник, 21.03.2016, 16:03 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3476
Репутация: 722 ±
Замечаний: 0% ±

Excel 2013
файл-пример приложите
в третьем столбике
особенно это не понятно, как оно должно выглядить


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщениефайл-пример приложите
в третьем столбике
особенно это не понятно, как оно должно выглядить

Автор - Nic70y
Дата добавления - 21.03.2016 в 16:03
PsyArcus Дата: Понедельник, 21.03.2016, 17:01 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 60% ±

Excel 2010
В начале

1 столбик
domen.ru
site.ru
adress.ru
slova.com
excel.ru

2 столбик
info@glav.ru
slova@slova.com
nichego@plov.ru
office@site.ru

В итоге

1 столбик
domen.ru
site.ru
adress.ru
slova.com
excel.ru

2 столбик
info@glav.ru
slova@slova.com
nichego@plov.ru
office@site.ru

3 столбик
---
office@site.ru
---
slova@slova.com
---
[moder]Файл-пример - это пример в файле.


Сообщение отредактировал _Boroda_ - Понедельник, 21.03.2016, 17:03
 
Ответить
СообщениеВ начале

1 столбик
domen.ru
site.ru
adress.ru
slova.com
excel.ru

2 столбик
info@glav.ru
slova@slova.com
nichego@plov.ru
office@site.ru

В итоге

1 столбик
domen.ru
site.ru
adress.ru
slova.com
excel.ru

2 столбик
info@glav.ru
slova@slova.com
nichego@plov.ru
office@site.ru

3 столбик
---
office@site.ru
---
slova@slova.com
---
[moder]Файл-пример - это пример в файле.

Автор - PsyArcus
Дата добавления - 21.03.2016 в 17:01
abtextime Дата: Понедельник, 21.03.2016, 17:03 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 427
Репутация: 60 ±
Замечаний: 0% ±

Excel 2010
Все равно ничего не будем советовать, пока в Excel не сделаете пример :)
Давайте, не ленитесь :)
 
Ответить
СообщениеВсе равно ничего не будем советовать, пока в Excel не сделаете пример :)
Давайте, не ленитесь :)

Автор - abtextime
Дата добавления - 21.03.2016 в 17:03
Nic70y Дата: Понедельник, 21.03.2016, 17:17 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3476
Репутация: 722 ±
Замечаний: 0% ±

Excel 2013
Судя по Вашему "примеру" 2 столбик не может содержать разные адреса с одинаковым доменом. Так?


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)


Сообщение отредактировал Nic70y - Понедельник, 21.03.2016, 17:18
 
Ответить
СообщениеСудя по Вашему "примеру" 2 столбик не может содержать разные адреса с одинаковым доменом. Так?

Автор - Nic70y
Дата добавления - 21.03.2016 в 17:17
abtextime Дата: Понедельник, 21.03.2016, 17:22 | Сообщение № 6
Группа: Проверенные
Ранг: Обитатель
Сообщений: 427
Репутация: 60 ±
Замечаний: 0% ±

Excel 2010
Мда, нужно не только посчитать емейлы с нужным доменом, но и отсечь дубликаты. Вечер перестает быть томным :)
 
Ответить
СообщениеМда, нужно не только посчитать емейлы с нужным доменом, но и отсечь дубликаты. Вечер перестает быть томным :)

Автор - abtextime
Дата добавления - 21.03.2016 в 17:22
PsyArcus Дата: Понедельник, 21.03.2016, 17:34 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 60% ±

Excel 2010
Извините, что сразу файл не приложил.

Прикладываю.

Как раз с реальной ситуацией.
[moder]А пример итогового результата? А что делать, если несколько значений из 2-го столбца (пример - *.rsk-factory.ru, их 3 штуки)
К сообщению приложен файл: 2columns.xlsx(90Kb)


Сообщение отредактировал _Boroda_ - Понедельник, 21.03.2016, 17:56
 
Ответить
СообщениеИзвините, что сразу файл не приложил.

Прикладываю.

Как раз с реальной ситуацией.
[moder]А пример итогового результата? А что делать, если несколько значений из 2-го столбца (пример - *.rsk-factory.ru, их 3 штуки)

Автор - PsyArcus
Дата добавления - 21.03.2016 в 17:34
abtextime Дата: Понедельник, 21.03.2016, 18:07 | Сообщение № 8
Группа: Проверенные
Ранг: Обитатель
Сообщений: 427
Репутация: 60 ±
Замечаний: 0% ±

Excel 2010
с дополнительными столбцами C
Код
=ПРАВБ(B2;ДЛСТР(B2)-ПОИСК("@";B2)+1)

и D
Код
="@"&ПОДСТАВИТЬ(A2;"www.";"")


Результат в E
Код
=СЧЁТЕСЛИ($C$2:$C$437;D2)


Мозгов не хватает в массивную формулу причесать (
 
Ответить
Сообщениес дополнительными столбцами C
Код
=ПРАВБ(B2;ДЛСТР(B2)-ПОИСК("@";B2)+1)

и D
Код
="@"&ПОДСТАВИТЬ(A2;"www.";"")


Результат в E
Код
=СЧЁТЕСЛИ($C$2:$C$437;D2)


Мозгов не хватает в массивную формулу причесать (

Автор - abtextime
Дата добавления - 21.03.2016 в 18:07
_Boroda_ Дата: Понедельник, 21.03.2016, 18:18 | Сообщение № 9
Группа: Модераторы
Ранг: Экселист
Сообщений: 9348
Репутация: 3923 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
в массивную формулу причесать
Ну, в массивную на стал, обычная пойдет?
Код
=СЧЁТЕСЛИ(B$2:B$437;"*@"&ПОДСТАВИТЬ(A2;"www.";))

Это если количество выводить. А вот если значения, то нужен ответ на вопрос, заданный мною в комментарии к последнему Вашему посту.
К сообщению приложен файл: 2columns_1.xlsb(96Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
в массивную формулу причесать
Ну, в массивную на стал, обычная пойдет?
Код
=СЧЁТЕСЛИ(B$2:B$437;"*@"&ПОДСТАВИТЬ(A2;"www.";))

Это если количество выводить. А вот если значения, то нужен ответ на вопрос, заданный мною в комментарии к последнему Вашему посту.

Автор - _Boroda_
Дата добавления - 21.03.2016 в 18:18
abtextime Дата: Понедельник, 21.03.2016, 18:20 | Сообщение № 10
Группа: Проверенные
Ранг: Обитатель
Сообщений: 427
Репутация: 60 ±
Замечаний: 0% ±

Excel 2010
если ближе к ТЗ :), то в E

Код
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(D2;$C$2:$C$437;0));"";ИНДЕКС($B$2:$B$437;ПОИСКПОЗ(D2;$C$2:$C$437;0)))


Сообщение отредактировал abtextime - Понедельник, 21.03.2016, 19:01
 
Ответить
Сообщениеесли ближе к ТЗ :), то в E

Код
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(D2;$C$2:$C$437;0));"";ИНДЕКС($B$2:$B$437;ПОИСКПОЗ(D2;$C$2:$C$437;0)))

Автор - abtextime
Дата добавления - 21.03.2016 в 18:20
PsyArcus Дата: Понедельник, 21.03.2016, 18:57 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 60% ±

Excel 2010
Это если количество выводить. А вот если значения, то нужен ответ на вопрос, заданный мною в комментарии к последнему Вашему посту.

Спасибо большое за пример, уже становится яснее.

Вот показал в файлике, что было бы здорово получить.

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

Так мы увидим, есть ли среди почтовых адресов вообще упомянутые компании с их адресами сайтов.
К сообщению приложен файл: 7766389.xlsb(96Kb)
 
Ответить
Сообщение
Это если количество выводить. А вот если значения, то нужен ответ на вопрос, заданный мною в комментарии к последнему Вашему посту.

Спасибо большое за пример, уже становится яснее.

Вот показал в файлике, что было бы здорово получить.

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

Так мы увидим, есть ли среди почтовых адресов вообще упомянутые компании с их адресами сайтов.

Автор - PsyArcus
Дата добавления - 21.03.2016 в 18:57
abtextime Дата: Понедельник, 21.03.2016, 19:03 | Сообщение № 12
Группа: Проверенные
Ранг: Обитатель
Сообщений: 427
Репутация: 60 ±
Замечаний: 0% ±

Excel 2010
только сейчас заметил, что файл не прикрепился изза объема. Исправляюсь
К сообщению приложен файл: _2columns.xlsx(86Kb)
 
Ответить
Сообщениетолько сейчас заметил, что файл не прикрепился изза объема. Исправляюсь

Автор - abtextime
Дата добавления - 21.03.2016 в 19:03
Nic70y Дата: Понедельник, 21.03.2016, 19:09 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3476
Репутация: 722 ±
Замечаний: 0% ±

Excel 2013
Код
=ЕСЛИОШИБКА(ВПР("*"&ПОДСТАВИТЬ(A2;"www.";);B:B;1;);"")
=ЕСЛИОШИБКА(ВПР("*@"&ПОДСТАВИТЬ(A2;"www.";);B2:B$18000;1;);"")


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)


Сообщение отредактировал Nic70y - Понедельник, 21.03.2016, 19:17
 
Ответить
Сообщение
Код
=ЕСЛИОШИБКА(ВПР("*"&ПОДСТАВИТЬ(A2;"www.";);B:B;1;);"")
=ЕСЛИОШИБКА(ВПР("*@"&ПОДСТАВИТЬ(A2;"www.";);B2:B$18000;1;);"")

Автор - Nic70y
Дата добавления - 21.03.2016 в 19:09
PsyArcus Дата: Понедельник, 21.03.2016, 19:13 | Сообщение № 14
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 60% ±

Excel 2010
только сейчас заметил, что файл не прикрепился изза объема. Исправляюсь


Всё просто великолепно даже без прикрепленного файла!
Опробовал метод - работает именно так, как надо!
Очень великое спасибо за помощь! Этот мир держится на таких людях, как вы)

Убирать пустые строки кстати мне не нужно, потому как в полной версии файла там идет запись о компании, которая не указала свой сайт.

Остается теперь еще 2 вопроса:
1. что можно сделать с тем, что некоторые компании указали несколько сайтов и они разделены знаком " | "
Просто скопировать куда-то текстом и произвести автозамену не выйдет, потому что потеряем связи с разного рода данными в строке, типа телефона компании.

2. как можно выделить цветом всю строку, которая содержит любое значение в столбце E?
[moder]Не надо складывать все вопросы в одну тему. Читайте Правила форума[/moder]


Сообщение отредактировал Pelena - Понедельник, 21.03.2016, 19:23
 
Ответить
Сообщение
только сейчас заметил, что файл не прикрепился изза объема. Исправляюсь


Всё просто великолепно даже без прикрепленного файла!
Опробовал метод - работает именно так, как надо!
Очень великое спасибо за помощь! Этот мир держится на таких людях, как вы)

Убирать пустые строки кстати мне не нужно, потому как в полной версии файла там идет запись о компании, которая не указала свой сайт.

Остается теперь еще 2 вопроса:
1. что можно сделать с тем, что некоторые компании указали несколько сайтов и они разделены знаком " | "
Просто скопировать куда-то текстом и произвести автозамену не выйдет, потому что потеряем связи с разного рода данными в строке, типа телефона компании.

2. как можно выделить цветом всю строку, которая содержит любое значение в столбце E?
[moder]Не надо складывать все вопросы в одну тему. Читайте Правила форума[/moder]

Автор - PsyArcus
Дата добавления - 21.03.2016 в 19:13
PsyArcus Дата: Понедельник, 21.03.2016, 19:24 | Сообщение № 15
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 60% ±

Excel 2010
=ЕСЛИОШИБКА(ВПР("*"&ПОДСТАВИТЬ(A2;"www.";);B:B;1;);"")


Спасибо, но чутка не корректно работает.

Для адреса www.mebel.ru
находит kaskad@aikon-mebel.ru
 
Ответить
Сообщение
=ЕСЛИОШИБКА(ВПР("*"&ПОДСТАВИТЬ(A2;"www.";);B:B;1;);"")


Спасибо, но чутка не корректно работает.

Для адреса www.mebel.ru
находит kaskad@aikon-mebel.ru

Автор - PsyArcus
Дата добавления - 21.03.2016 в 19:24
PsyArcus Дата: Понедельник, 21.03.2016, 19:27 | Сообщение № 16
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 60% ±

Excel 2010
=ЕСЛИОШИБКА(ВПР("*@"&ПОДСТАВИТЬ(A2;"www.";);B2:B$18000;1;);"")


А вот так уже работает верно.
Только как бы мне так размножить формулу на весь столбик, чтобы счетчик B2 не увеличивался на каждой строке?
 
Ответить
Сообщение
=ЕСЛИОШИБКА(ВПР("*@"&ПОДСТАВИТЬ(A2;"www.";);B2:B$18000;1;);"")


А вот так уже работает верно.
Только как бы мне так размножить формулу на весь столбик, чтобы счетчик B2 не увеличивался на каждой строке?

Автор - PsyArcus
Дата добавления - 21.03.2016 в 19:27
abtextime Дата: Понедельник, 21.03.2016, 19:28 | Сообщение № 17
Группа: Проверенные
Ранг: Обитатель
Сообщений: 427
Репутация: 60 ±
Замечаний: 0% ±

Excel 2010
1. Если не усложнять чрезмерно формулы, то через Данные - Текст по столбцам = разделитель "|" делим на три или сколько там столбца. Остальное понятно, просто через СЦЕПИТЬ() или & цепляем результаты в одну строку.
2. Условным форматированием.

Сейчас Вам помогут более детально (если модераторы разрешат в этой теме), а я, сорри, спешу...
[moder]Не разрешат[/moder]


Сообщение отредактировал Pelena - Понедельник, 21.03.2016, 19:30
 
Ответить
Сообщение1. Если не усложнять чрезмерно формулы, то через Данные - Текст по столбцам = разделитель "|" делим на три или сколько там столбца. Остальное понятно, просто через СЦЕПИТЬ() или & цепляем результаты в одну строку.
2. Условным форматированием.

Сейчас Вам помогут более детально (если модераторы разрешат в этой теме), а я, сорри, спешу...
[moder]Не разрешат[/moder]

Автор - abtextime
Дата добавления - 21.03.2016 в 19:28
Nic70y Дата: Понедельник, 21.03.2016, 19:29 | Сообщение № 18
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3476
Репутация: 722 ±
Замечаний: 0% ±

Excel 2013
не увеличивался
B$2


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
не увеличивался
B$2

Автор - Nic70y
Дата добавления - 21.03.2016 в 19:29
StoTisteg Дата: Понедельник, 21.03.2016, 22:23 | Сообщение № 19
Группа: Авторы
Ранг: Ветеран
Сообщений: 541
Репутация: 45 ±
Замечаний: 0% ±

Excel 2010
посчитать емейлы с нужным доменом

Может вам ещё и регэксп для полной проверки на емельность написать? :)


Проверь всё. ThisWorkbook.Save. On Error Resume Next.

Сообщение отредактировал StoTisteg - Понедельник, 21.03.2016, 22:24
 
Ответить
Сообщение
посчитать емейлы с нужным доменом

Может вам ещё и регэксп для полной проверки на емельность написать? :)

Автор - StoTisteg
Дата добавления - 21.03.2016 в 22:23
PsyArcus Дата: Вторник, 22.03.2016, 10:53 | Сообщение № 20
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 60% ±

Excel 2010
Модератор:
Не надо складывать все вопросы в одну тему. Читайте Правила форума

Друзья, я понимаю, что вопрос не совсем в рамках этого топика, то он сильно завязан на контекст этого топика.

Хочу сделать условное форматирование с проверкой, есть ли результат в той формуле, которую вы мне подсказали.
В ячейке формула:
=ЕСЛИОШИБКА(ВПР("*@"&ПОДСТАВИТЬ(A2;"www.";);B$2:B$18000;1;);"")

Или другая:
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(D2;$C$2:$C$437;0));"";ИНДЕКС($B$2:$B$437;ПОИСКПОЗ(D2;$C$2:$C$437;0)))

Какой функцией можно проверить, дала ли формула результат или строка пустая?
И соответственно выделить условным форматирование строку, если там есть значение?
Обычной проверкой есть ли в ячейке текстовое значение не получается, потому как оно там в каждой.

Файл приложил.
[moder]Поздравляю - за один пост Вы словили 3 замечания: Нарушение п.3 Правил форума в части тегов, Нарушение п.5q Правил форума и игнорирование указаний администрации.
К сообщению приложен файл: 2columns.xlsb(74Kb)


Сообщение отредактировал _Boroda_ - Вторник, 22.03.2016, 11:01
 
Ответить
Сообщение
Модератор:
Не надо складывать все вопросы в одну тему. Читайте Правила форума

Друзья, я понимаю, что вопрос не совсем в рамках этого топика, то он сильно завязан на контекст этого топика.

Хочу сделать условное форматирование с проверкой, есть ли результат в той формуле, которую вы мне подсказали.
В ячейке формула:
=ЕСЛИОШИБКА(ВПР("*@"&ПОДСТАВИТЬ(A2;"www.";);B$2:B$18000;1;);"")

Или другая:
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(D2;$C$2:$C$437;0));"";ИНДЕКС($B$2:$B$437;ПОИСКПОЗ(D2;$C$2:$C$437;0)))

Какой функцией можно проверить, дала ли формула результат или строка пустая?
И соответственно выделить условным форматирование строку, если там есть значение?
Обычной проверкой есть ли в ячейке текстовое значение не получается, потому как оно там в каждой.

Файл приложил.
[moder]Поздравляю - за один пост Вы словили 3 замечания: Нарушение п.3 Правил форума в части тегов, Нарушение п.5q Правил форума и игнорирование указаний администрации.

Автор - PsyArcus
Дата добавления - 22.03.2016 в 10:53
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск соответствия в двух столбиках по части содержимого (Формулы/Formulas)
Страница 1 из 11
Поиск:

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