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

Вход

Регистрация

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

 

= Мир MS Excel/найти фио в произвольном тексте - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » найти фио в произвольном тексте (Макросы/Sub)
найти фио в произвольном тексте
lFJl Дата: Понедельник, 22.08.2016, 09:54 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 188
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
Доброго дня.
Имеется колонка1, содержащая ФИО+должность, должность+ФИО. Фио могут содержать инициалы, написаны полностью.
И имеется колонка2 с ФИО полностью.
Нужно на против каждого значения в колонке1 найти фио из колонки2, подкинете идею, как это сделать?
Макрос/формула, не важно.
К сообщению приложен файл: __.xlsx(11Kb)
 
Ответить
СообщениеДоброго дня.
Имеется колонка1, содержащая ФИО+должность, должность+ФИО. Фио могут содержать инициалы, написаны полностью.
И имеется колонка2 с ФИО полностью.
Нужно на против каждого значения в колонке1 найти фио из колонки2, подкинете идею, как это сделать?
Макрос/формула, не важно.

Автор - lFJl
Дата добавления - 22.08.2016 в 09:54
Pelena Дата: Понедельник, 22.08.2016, 10:27 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 9885
Репутация: 2266 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Здравствуйте.
Однофамильцы есть в списке?
Если нет, то можно формулой массива
Код
=ЕСЛИОШИБКА(ИНДЕКС(Таблица4[Колонка2];ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(ЛЕВСИМВ(Таблица4[Колонка2];ПОИСК(" ";Таблица4[Колонка2])-1);[@Колонка1]));0));"нет данных")
К сообщению приложен файл: 0230389.xlsx(11Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Однофамильцы есть в списке?
Если нет, то можно формулой массива
Код
=ЕСЛИОШИБКА(ИНДЕКС(Таблица4[Колонка2];ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(ЛЕВСИМВ(Таблица4[Колонка2];ПОИСК(" ";Таблица4[Колонка2])-1);[@Колонка1]));0));"нет данных")

Автор - Pelena
Дата добавления - 22.08.2016 в 10:27
SLAVICK Дата: Понедельник, 22.08.2016, 11:31 | Сообщение № 3
Группа: Модераторы
Ранг: Старожил
Сообщений: 1849
Репутация: 614 ±
Замечаний: 0% ±

2007,2010,2013,2016
lFJl, давайте сразу полнее примеры.
Вариант Pelena, для Вашего примера - идеален, но если будут однофамильцы - даст сбой. (см. пример)

Как вариант УДФ-ка - будет работать с однофамильцами:
[vba]
Код
Option Compare Text
Function d(r As Range, r1 As Range)
Dim C As Range, M, I%
M = Split(Trim(Replace(r, ".", ". ")), " ")
    For I = 0 To UBound(M)
        For Each C In r1
            If InStr(1, C, M(I)) Then
                If (C) Like "*" & M(I) & "*" & Replace(M(I + 1), ".", "") & "*" & Replace(M(I + 2), ".", "") & "*" Then d = C: Exit Function
            End If
        Next
    Next
End Function
[/vba]
К сообщению приложен файл: 0230389.xlsm(19Kb)


Иногда все проще чем кажется с первого взгляда.
 
Ответить
СообщениеlFJl, давайте сразу полнее примеры.
Вариант Pelena, для Вашего примера - идеален, но если будут однофамильцы - даст сбой. (см. пример)

Как вариант УДФ-ка - будет работать с однофамильцами:
[vba]
Код
Option Compare Text
Function d(r As Range, r1 As Range)
Dim C As Range, M, I%
M = Split(Trim(Replace(r, ".", ". ")), " ")
    For I = 0 To UBound(M)
        For Each C In r1
            If InStr(1, C, M(I)) Then
                If (C) Like "*" & M(I) & "*" & Replace(M(I + 1), ".", "") & "*" & Replace(M(I + 2), ".", "") & "*" Then d = C: Exit Function
            End If
        Next
    Next
End Function
[/vba]

Автор - SLAVICK
Дата добавления - 22.08.2016 в 11:31
lFJl Дата: Понедельник, 22.08.2016, 11:37 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 188
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
Однофамильцы есть в списке?

Да, есть. Но попробую хоть так пройтись.
Хотел макросом пройтись, каждую ячейку с базой ФИО сверить, и в случае положительного результата - записать значение, но чет пока туговато
 
Ответить
Сообщение
Однофамильцы есть в списке?

Да, есть. Но попробую хоть так пройтись.
Хотел макросом пройтись, каждую ячейку с базой ФИО сверить, и в случае положительного результата - записать значение, но чет пока туговато

Автор - lFJl
Дата добавления - 22.08.2016 в 11:37
Pelena Дата: Понедельник, 22.08.2016, 11:42 | Сообщение № 5
Группа: Модераторы
Ранг: Экселист
Сообщений: 9885
Репутация: 2266 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Можно первую букву имени захватить
Код
=ЕСЛИОШИБКА(ИНДЕКС(Таблица4[Колонка2];ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(ЛЕВСИМВ(Таблица4[Колонка2];ПОИСК(" ";Таблица4[Колонка2])+1);[@Колонка1]));0));"нет данных")

Можно и полностью инициалы, но либо формула усложниться, либо доп. столбец понадобится


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеМожно первую букву имени захватить
Код
=ЕСЛИОШИБКА(ИНДЕКС(Таблица4[Колонка2];ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(ЛЕВСИМВ(Таблица4[Колонка2];ПОИСК(" ";Таблица4[Колонка2])+1);[@Колонка1]));0));"нет данных")

Можно и полностью инициалы, но либо формула усложниться, либо доп. столбец понадобится

Автор - Pelena
Дата добавления - 22.08.2016 в 11:42
lFJl Дата: Понедельник, 22.08.2016, 12:15 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 188
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
Можно и полностью инициалы, но либо формула усложниться, либо доп. столбец понадобится

Я сделал бы, сначала ищем полностью, если ошибка то по инициалам
 
Ответить
Сообщение
Можно и полностью инициалы, но либо формула усложниться, либо доп. столбец понадобится

Я сделал бы, сначала ищем полностью, если ошибка то по инициалам

Автор - lFJl
Дата добавления - 22.08.2016 в 12:15
Pelena Дата: Понедельник, 22.08.2016, 12:47 | Сообщение № 7
Группа: Модераторы
Ранг: Экселист
Сообщений: 9885
Репутация: 2266 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
сначала ищем полностью, если ошибка то по инициалам

можно и так
К сообщению приложен файл: 3381845.xlsx(12Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
сначала ищем полностью, если ошибка то по инициалам

можно и так

Автор - Pelena
Дата добавления - 22.08.2016 в 12:47
SLAVICK Дата: Понедельник, 22.08.2016, 12:53 | Сообщение № 8
Группа: Модераторы
Ранг: Старожил
Сообщений: 1849
Репутация: 614 ±
Замечаний: 0% ±

2007,2010,2013,2016
Хотел макросом пройтись,.... но чет пока туговато

Вы мой вариант смотрели? чем не подошел?


Иногда все проще чем кажется с первого взгляда.
 
Ответить
Сообщение
Хотел макросом пройтись,.... но чет пока туговато

Вы мой вариант смотрели? чем не подошел?

Автор - SLAVICK
Дата добавления - 22.08.2016 в 12:53
lFJl Дата: Понедельник, 22.08.2016, 13:03 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 188
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
SLAVICK, Прошу прощения, что-то не увидел, завтра утром обязательно посмотрю!
 
Ответить
СообщениеSLAVICK, Прошу прощения, что-то не увидел, завтра утром обязательно посмотрю!

Автор - lFJl
Дата добавления - 22.08.2016 в 13:03
RAN Дата: Понедельник, 22.08.2016, 21:35 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4277
Репутация: 829 ±
Замечаний: 0% ±

2010
Как вариант УДФ-ка - будет работать с однофамильцами:

Не будет :(
Начальник 4 Сидоров П.С.| Сидоров Сидор Сидорович| Сидоров Павел Сидорович| Сидоров Павел Сидорович
Начальник 5 Сидоров П.С.| Сидоров Сидор Сидорович| Сидоров Петр Сидорович | Сидоров Павел Сидорович

Мы можем определить, что Начальник 4, это Сидоров Петр Сидорович, а Начальник 5, это Сидоров Павел Сидорович, только потому, что это у нас это где-то на "корочке" записано.
Чтобы Excel мог их отличать, ему тоже надо это на "корочке" записать.
А ежели они оба Пётры, только один лысый, а другой с бородой, то совсем капут! lol


Быть или не быть, вот в чем загвоздка!

Сообщение отредактировал RAN - Понедельник, 22.08.2016, 22:08
 
Ответить
Сообщение
Как вариант УДФ-ка - будет работать с однофамильцами:

Не будет :(
Начальник 4 Сидоров П.С.| Сидоров Сидор Сидорович| Сидоров Павел Сидорович| Сидоров Павел Сидорович
Начальник 5 Сидоров П.С.| Сидоров Сидор Сидорович| Сидоров Петр Сидорович | Сидоров Павел Сидорович

Мы можем определить, что Начальник 4, это Сидоров Петр Сидорович, а Начальник 5, это Сидоров Павел Сидорович, только потому, что это у нас это где-то на "корочке" записано.
Чтобы Excel мог их отличать, ему тоже надо это на "корочке" записать.
А ежели они оба Пётры, только один лысый, а другой с бородой, то совсем капут! lol

Автор - RAN
Дата добавления - 22.08.2016 в 21:35
SLAVICK Дата: Вторник, 23.08.2016, 10:04 | Сообщение № 11
Группа: Модераторы
Ранг: Старожил
Сообщений: 1849
Репутация: 614 ±
Замечаний: 0% ±

2007,2010,2013,2016
то Сидоров Петр Сидорович, а Начальник 5, это Сидоров Павел Сидорович, только потому,

я имел ввиду однофамильцев а не полностью одинаковых П.С. :D .
таких конечно - только по "корочке" и определишь. Но их можно легко выделить УФ и пройтись глазками со своей корочкой и перечитать. Думаю будет не много полных совпадений, :) .
А ежели они оба Пётры, только один лысый,

ну тут уже точно killed .


Иногда все проще чем кажется с первого взгляда.
 
Ответить
Сообщение
то Сидоров Петр Сидорович, а Начальник 5, это Сидоров Павел Сидорович, только потому,

я имел ввиду однофамильцев а не полностью одинаковых П.С. :D .
таких конечно - только по "корочке" и определишь. Но их можно легко выделить УФ и пройтись глазками со своей корочкой и перечитать. Думаю будет не много полных совпадений, :) .
А ежели они оба Пётры, только один лысый,

ну тут уже точно killed .

Автор - SLAVICK
Дата добавления - 23.08.2016 в 10:04
lFJl Дата: Среда, 24.08.2016, 07:37 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 188
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
решил проблему формулой:
Код
=ЕСЛИОШИБКА(ИНДЕКС(Таблица7[ФИО];ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(ЛЕВСИМВ(Таблица7[ФИО];99);[@ФИО]));0));
ЕСЛИОШИБКА(ИНДЕКС(Таблица7[ФИО];ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(ЛЕВСИМВ(Таблица7[Столбец1];99);[@ФИО]));0));"_" & [@ФИО]))

Сначала искал полное совпадение ФИО, потом инициалы, и результат меня устроил. Из неудобств неудобно было время работы(5500 строк в колонке 1 и 3500 в колонке 2)
SLAVICK,
Функцию проверил(в колонке 1 оставил 100 строк) Результат так тож нормальный :)
Но поскольку уже решено формулой, переделывать не стал.

Спасибо всем за помощь! :)
 
Ответить
Сообщениерешил проблему формулой:
Код
=ЕСЛИОШИБКА(ИНДЕКС(Таблица7[ФИО];ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(ЛЕВСИМВ(Таблица7[ФИО];99);[@ФИО]));0));
ЕСЛИОШИБКА(ИНДЕКС(Таблица7[ФИО];ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(ЛЕВСИМВ(Таблица7[Столбец1];99);[@ФИО]));0));"_" & [@ФИО]))

Сначала искал полное совпадение ФИО, потом инициалы, и результат меня устроил. Из неудобств неудобно было время работы(5500 строк в колонке 1 и 3500 в колонке 2)
SLAVICK,
Функцию проверил(в колонке 1 оставил 100 строк) Результат так тож нормальный :)
Но поскольку уже решено формулой, переделывать не стал.

Спасибо всем за помощь! :)

Автор - lFJl
Дата добавления - 24.08.2016 в 07:37
Мир MS Excel » Вопросы и решения » Вопросы по VBA » найти фио в произвольном тексте (Макросы/Sub)
Страница 1 из 11
Поиск:

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