Страницы: 1
RSS
Выявление расхождений в двух столбцах содержащих текстовые ячейки, Выявление расхождений в адресах в двух столбах, причем адреса прописаны в столбцах по разным принципам
 
Добрый день. Есть адресные данные в двух столбцах текстового содержания, информация в столбцах идентична, отличается только тем, что данные расположены неодинаково и самое главное есть лишние слова или символы, либо недостающие символы. Нужно выявить в каких ячейках столбца А идет несоответствие данным столбца С. Желательно обойтись без макросов и по возможности наиболее простым способом. Надеюсь на помощь, так как это ежедневная головная боль, причем это реестр в урезанном виде и их бывает до 10-15 в день.  
Изменено: vikki - 04.08.2022 22:09:24
 
Цитата
написал:
Добрый день. Есть адресные данные в двух столбцах текстового содержания, информация в столбцах идентична, отличается только тем, что данные расположены неодинаково и самое главное есть лишние слова или символы, либо недостающие символы. Нужно выявить в каких ячейках столбца А идет несоответствие данным столбца С. Желательно обойтись без макросов и по возможности наиболее простым способом. Надеюсь на помощь, так как это ежедневная головная боль, причем это реестр в урезанном виде и их бывает до 10-15 в день.  
Крикну. А в ответ - тишина...
 
Конечно, в это время тут больше всего помогающих  :D
 
vikki, мало конкретики.
1) откуда эксель возьмёт индекс?
2) республику ищите соотв. формулой ПОИСК() по ключевому слову "респ " и получаете необходимы кусок строки через ПСТР() или ПРАВБ();
3) "СЕВЕРНАЯ ОСЕТИЯ - АЛАНИЯ РЕСП, , " - вами не описано почему идут подряд две запятые через пробел?
4) город ищите по аналогии республики;
5) с улицами проблематично описать алгоритм. Например, получить часть строки с начала и до ", г " (а если не город, а село, хутор, пгт...) - не проблема, но дальше
- надо вычленить все возможные типы улиц,
- номер дома, в принципе можно, но с доп. обозначениями проблема: то номер с буквой, то буква отдельно указана после слова "литера", то после "кв." ничего нет и идёт сразу город.
Без анализа всех возможных вариантов написания адресов невозможно дать универсальное решение, тем более без макроса. Уже были подобные вопросы и универсальное решение - вручную приводить в соответствие. Автоматизировать можно часть работы и то с ручной проверкой результата.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Поняла, буду работать вручную. Спасибо за информацию :)
 
vikki, можете оптимизировать свою работу. Заполняете последовательно все части адреса, а после делаете их сцепку в единое целое. Например, отфильтровали одну республику, заполнили поле "республика" в отфильтрованных ячейках (за одну операцию: после фильтрации выделили пустые видимые ячейки внутри этого поля, в активную ячейку внесли что надо и нажали Ctrl+Enter). Остальное по аналогии. А более мелкие детали только вручную по каждому адресу.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Цитата
написал:
vikki, можете оптимизировать свою работу. Заполняете последовательно все части адреса, а после делаете их сцепку в единое целое. Например, отфильтровали одну республику, заполнили поле "республика" в отфильтрованных ячейках (за одну операцию: после фильтрации выделили пустые видимые ячейки внутри этого поля, в активную ячейку внесли что надо и нажали Ctrl+Enter). Остальное по аналогии. А более мелкие детали только вручную по каждому адресу.
Хорошее предложение, но так как эту операцию нужно повторять не один раз в день, да и ошибки бывают в одном реестре от 2 до 10, то в самом деле легче глазками просматривать. Спасибо за то, что вникли в мою проблему :))
 
а я не понял, что хочет vikki. У вас есть 2 списка -  1-й в столбце А и 2-й в столбце С. (один красненький, второй беленький).
В вашем файле эти списки равны или не равны?
Они всегда равны по строкам параллельно? или, условно, адрес Гадиева, 77 в первой (левой) таблице во 2-й строке, и он же может быть в 10-й строке второй (правой) таблице?
Что такое неточности для вас? Для меня вообще все данные в этих двух таблицах неточные, т.к. отличаются буквами, числами, наличием индексов и т.д.
Вы как-то быстро обижаетесь (аа, ну, вас, буду глазами делать), и не хотите более подробно описать что нужно делать.
Может вам действительно придётся всю жизнь делать эту задачу глазами, но вы хотя бы попытайтесь объяснить нам, что вы пытаетесь делать.
Если бы вы написали - вот 3-я строка в левой таблицы отличается от 3-й строки правой таблицы и поэтому мне и нужно автоматически найти эту 3-ю строку левой таблицы.
А так я посмотрел - вроде, улицы и дома совпадают построчно в этих 2-х таблицах, одна красная, другая белая.... что хотят от нас не понятно.
Может вам хватит проверки наличия улицы из 1-й таблицы одной строки во 2-й таблицы этой же строке и вас это устроит, т.е. если во 2-й строке левой таблицы улица Гадиева, 77 и это же улица и дом есть на этой же строке во второй таблицы, то адреса совпадают и на этом проверка 2-й строки закончилась и можно переходить к строке ниже. А может вам такой проверки не хватит и нужно сверять полностью область, город, улицу, дом, и литеру... можно более подробнее?
Изменено: New - 04.08.2022 23:01:29
 
Цитата
написал:
Они всегда равны по строкам параллельно
Вы правы. Не раскрыла проблему.
Да, данные в столбцах должны быть  всегда равны по строкам параллельно. Данные в части: республика, город, индекс - не интересуют, т. к. в них не бывает несоответствий. Нужно выявить в столбце А различия по номеру дома, корпусу, квартире или лишнему слову "литера". Цвет ячеек в столбце А значения не имеет. Например, в 4-ой строке в столбце А указан адрес ".... д.43, литера Б....", тогда как верный адрес в столбце С "..., 43, Б", в данном случае расхождение только на слово "литера" - его не должно быть.
 
New, хотят чтобы исходные данные (слева) стали как вправо при помощи формул и без макросов. Я так понял.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Ну, как без макросов - я не знаю, я в формулах не разбираюсь. А макросом пока кажется так - из левой таблицы берём текст до города (там будет улица, номер дома, литера), разбиваем этот текст по пробелам и потом начинаем в цикле искать каждое слово в правой таблице. Если какого-то слова нет (названия улицы нет, или номер дома нет, или слова "литера"), то значит строка из второй таблицы не совпадает со строкой первой таблицы. По идее, это можно сделать функцией на макросе
Изменено: New - 05.08.2022 00:43:20
 
Цитата
написал:
JayBhagavan
JayBhagavan, не совсем.
Нужно выявить, в каких строках неидентичные данные, потому как далее предстоит ручная корректровка в личном кабинете на сайте почты. Неидентичные данные в трех строках: 4, 6, 11, остальные совпадают. Применив формулу хотелось бы увидеть расхождения в этих трех строчках.
 
vikki, у меня получилось вот так (см. файл)
Изменено: New - 05.08.2022 00:41:18
 
Цитата
написал:
vikki, у меня получилось вот так (см. файл)
Круто, круто, круто... New, искренне благодарю за избавление от рутинной работы.  
Изменено: vikki - 05.08.2022 07:23:32
 
Цитата
написал:
vikki, у меня получилось вот так (см. файл)
New, научите как пользоваться этой формулой. Чего-то никак выходит.
 
vikki, Почитайте ТУТ про пользовательские функции
 
vikki, в моем файле есть макрос в виде самописной функции - ПроверкаАдреса (сейчас уже точно не помню как назвал). То есть я создал свою функцию, которая будет работать как обычная формула, но только в этом файле.
Вам надо будет открывать именно этот файл, вставлять значения в левую и правую таблицу и в столбце С протягивать формулу вниз. Эта функция будет выдавать либо ОК, либо "Не совпадает", либо "Ошибка, не найдено сочетание "пробел буква г пробел".
То есть открыли именно этот файл, заполнили 2 таблицы, протянули формулу, там где показало "Не совпадает" проверили, что не совпадает, можно отфильтровать по Не совпадает в столбце С, если данных много и работаете уже с этими данными. Скажем так это будет у вас файл для проверки адресов и всё. Данная функция не будет работать в других файлах Excel, а только в этом, т.к. макрос написан только внутри этого файла
Изменено: New - 05.08.2022 10:54:01
 
Цитата
написал:
vikki, в моем файле есть макрос в виде самописной функции - ПроверкаАдреса (сейчас уже точно не помню как назвал). То есть я создал свою функцию, которая будет работать как обычная формула, но только в этом файле.
Вам надо будет открывать именно этот файл, вставлять значения в левую и правую таблицу и в столбце С протягивать формулу вниз. Эта функция будет выдавать либо ОК, либо "Не совпадает", либо "Ошибка, не найдено сочетание "пробел буква г пробел".
То есть открыли именно этот файл, заполнили 2 таблицы, протянули формулу, там где показало "Не совпадает" проверили, что не совпадает, можно отфильтровать по Не совпадает в столбце С, если данных много и работаете уже с этими данными. Скажем так это будет у вас файл для проверки адресов и всё. Данная функция не будет работать в других файлах Excel, а только в этом, т.к. макрос написан только внутри этого файла
Цитата
New, да я разобралась. Поработала со своими файлами пользуясь этой формулой и собрала список, когда формула проверки пишет "Не совпадает", хотя расхождения не критичны и мне нужно, чтобы по расхождениям приведённым в файле со 2-ой по 13-ую строки (отмечены красным шрифтом) формула проставляла “ОК", если, конечно, это возможно, а также при наличии у вас времени на это. Благодарна вам за помощь.
 
см. файл
 
Цитата
написал:
см. файл
New, просмотрела файл. Вполне все устраивает. Буду тестировать на новой неделе. Это большая экономия времени.
Спасибо за неоценимую помощь. Здоровья и успехов в жизни *;)
Изменено: vikki - 06.08.2022 10:52:28
 
Цитата
написал:
см. файл
New, не удержалась и стала разбираться с формулой. Да, вижу, что строки выделенные красным цветом ушли из расхождений. Отлично.
Но я заметила, что расхождения, которые правильно выявила первая формула (в строках 19, 23, 26, 31, 34 в адресах лишнее слово "литера" и в 34 строке ещё и неполный адрес) во втором файле не выявлены (в последнем файле они в строках 20, 24, 27, 32, 35 ) - напротив этих адресов стоит "ОК", а мне как раз они-то и нужны, чтобы поработать с ними вручную в базах.
 
Проставьте в каком-нибудь столбце текст ОК и Ошибка, чтобы я понимал какие строки для вас ок, а какие не ОК, а я вечером посмотрю
Изменено: New - 06.08.2022 14:01:07
 
Цитата
написал:
Проставьте в каком-нибудь столбце текст ОК и Ошибка, чтобы я понимал какие строки для вас ок, а какие не ОК, а я вечером посмотрю
Ок
 
vikki, См. файл
P.S. Обратите внимание, что теперь функция, к сожалению, неверно считает последнюю строку на листе ШПИ
 
Цитата
написал:
vikki, См. файл
P.S. Обратите внимание, что теперь функция, к сожалению, неверно считает последнюю строку на листе ШПИ
Привет, New. Сегодня протестирую и отпишусь.
 
 vikki, попробуйте поставить утилиту Микрософт Fuzzy Lookup - она как раз задумана для поиска таких совпадений. Перед этим желательно поменять  весь текст в одной из таблиц, чтобы были все буквы в левой таблице - заглавные, либо в правой - заглавной были только первые буква слов, остальные прописные. Также через через автозамену меняйте  в Екселе КВАРТИРА на кв. (ну, или наоборот), ДОМ на д., двойную запятую ,, на одинарную ,. Дальше устанавливаете точность совпадения - советую для скорости начать с 25-30 -%. Если данных много - придётся оставлять на ночь. Если при такой точности совпадения всё сопоставилось - поздравляю. Иначе придётся устанавливать более точный процент совпадения, что сильно сказывается на времени поиска. Сначала потренируйтесь на маленькой таблице (как у вас в примере), чтоб найти оптимальный минимальный процент точности совпадений.
Изменено: Zagadka - 08.08.2022 07:51:46
 
Цитата
написал:
 vikki , попробуйте поставить утилиту Микрософт Fuzzy Lookup - она как раз задумана для поиска таких совпадений. Перед этим желательно поменять  весь текст в одной из таблиц, чтобы были все буквы в левой таблице - заглавные, либо в правой - заглавной были только первые буква слов, остальные прописные. Также через через автозамену меняйте  в Екселе КВАРТИРА на кв. (ну, или наоборот), ДОМ на д., двойную запятую ,, на одинарную ,. Дальше устанавливаете точность совпадения - советую для скорости начать с 25-30 -%. Если данных много - придётся оставлять на ночь. Если при такой точности совпадения всё сопоставилось - поздравляю. Иначе придётся устанавливать более точный процент совпадения, что сильно сказывается на времени поиска. Сначала потренируйтесь на маленькой таблице (как у вас в примере), чтоб найти оптимальный минимальный процент точности совпадений.
Цитата
Спасибо, попробую этот вариант тоже.
 
Цитата
написал:
vikki, См. файл
P.S. Обратите внимание, что теперь функция, к сожалению, неверно считает последнюю строку на листе ШПИ
Протестировала. Все работает, кроме нескольких ситуации (см. приложение).  
 
vikki, Потестируйте этот файл.
P.S. К сожалению, больше ничем не могу помочь. У вас слишком много условностей, где-то вы хотите учитывать слово "Литер" и "Литера", а где-то нет (Литера Б и просто "Б" - считать, что это идентично), так же с квартирами, где-то надо сравнивать Кв.1 и квартира 1 - идентичны, а где-то ул.Шолохова, 1, 1 должно подходить к кв.1. По некоторым адресам вообще не выявить расхождение, т.к. в левом адресе меньше слов, чем в правом (ул.Шолохова, д.1 и 500100 ул.Шолохова, д.1 НП7 - макросом тут невозможно определить, что это разные адреса (лишнее "НП7"), т.к. в левой таблицы меньше слов для поиска, чем в правой, а правая таблица всегда содержит больше слов, чем левая, а определить какие слова лишние невозможно. (ул.Шолохова - это пример из головы, а не из вашей таблицы)
 
Цитата
написал:
vikki, Потестируйте этот файл.
P.S. К сожалению, больше ничем не могу помочь. У вас слишком много условностей, где-то вы хотите учитывать слово "Литер" и "Литера", а где-то нет (Литера Б и просто "Б" - считать, что это идентично), так же с квартирами, где-то надо сравнивать Кв.1 и квартира 1 - идентичны, а где-то ул.Шолохова, 1, 1 должно подходить к кв.1. По некоторым адресам вообще не выявить расхождение, т.к. в левом адресе меньше слов, чем в правом (ул.Шолохова, д.1 и 500100 ул.Шолохова, д.1 НП7 - макросом тут невозможно определить, что это разные адреса (лишнее "НП7"), т.к. в левой таблицы меньше слов для поиска, чем в правой, а правая таблица всегда содержит больше слов, чем левая, а определить какие слова лишние невозможно. (ул.Шолохова - это пример из головы, а не из вашей таблицы)
New, спасибо за уделенное время и проведенную работу:). Буду пользоваться последней  формулой.
Страницы: 1
Наверх