Выявление расхождений в двух столбцах содержащих текстовые ячейки, Выявление расхождений в адресах в двух столбах, причем адреса прописаны в столбцах по разным принципам
Добрый день. Есть адресные данные в двух столбцах текстового содержания, информация в столбцах идентична, отличается только тем, что данные расположены неодинаково и самое главное есть лишние слова или символы, либо недостающие символы. Нужно выявить в каких ячейках столбца А идет несоответствие данным столбца С. Желательно обойтись без макросов и по возможности наиболее простым способом. Надеюсь на помощь, так как это ежедневная головная боль, причем это реестр в урезанном виде и их бывает до 10-15 в день.
написал: Добрый день. Есть адресные данные в двух столбцах текстового содержания, информация в столбцах идентична, отличается только тем, что данные расположены неодинаково и самое главное есть лишние слова или символы, либо недостающие символы. Нужно выявить в каких ячейках столбца А идет несоответствие данным столбца С. Желательно обойтись без макросов и по возможности наиболее простым способом. Надеюсь на помощь, так как это ежедневная головная боль, причем это реестр в урезанном виде и их бывает до 10-15 в день.
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-й строки закончилась и можно переходить к строке ниже. А может вам такой проверки не хватит и нужно сверять полностью область, город, улицу, дом, и литеру... можно более подробнее?
Вы правы. Не раскрыла проблему. Да, данные в столбцах должны быть всегда равны по строкам параллельно. Данные в части: республика, город, индекс - не интересуют, т. к. в них не бывает несоответствий. Нужно выявить в столбце А различия по номеру дома, корпусу, квартире или лишнему слову "литера". Цвет ячеек в столбце А значения не имеет. Например, в 4-ой строке в столбце А указан адрес ".... д.43, литера Б....", тогда как верный адрес в столбце С "..., 43, Б", в данном случае расхождение только на слово "литера" - его не должно быть.
Ну, как без макросов - я не знаю, я в формулах не разбираюсь. А макросом пока кажется так - из левой таблицы берём текст до города (там будет улица, номер дома, литера), разбиваем этот текст по пробелам и потом начинаем в цикле искать каждое слово в правой таблице. Если какого-то слова нет (названия улицы нет, или номер дома нет, или слова "литера"), то значит строка из второй таблицы не совпадает со строкой первой таблицы. По идее, это можно сделать функцией на макросе
JayBhagavan, не совсем. Нужно выявить, в каких строках неидентичные данные, потому как далее предстоит ручная корректровка в личном кабинете на сайте почты. Неидентичные данные в трех строках: 4, 6, 11, остальные совпадают. Применив формулу хотелось бы увидеть расхождения в этих трех строчках.
vikki, в моем файле есть макрос в виде самописной функции - ПроверкаАдреса (сейчас уже точно не помню как назвал). То есть я создал свою функцию, которая будет работать как обычная формула, но только в этом файле. Вам надо будет открывать именно этот файл, вставлять значения в левую и правую таблицу и в столбце С протягивать формулу вниз. Эта функция будет выдавать либо ОК, либо "Не совпадает", либо "Ошибка, не найдено сочетание "пробел буква г пробел". То есть открыли именно этот файл, заполнили 2 таблицы, протянули формулу, там где показало "Не совпадает" проверили, что не совпадает, можно отфильтровать по Не совпадает в столбце С, если данных много и работаете уже с этими данными. Скажем так это будет у вас файл для проверки адресов и всё. Данная функция не будет работать в других файлах Excel, а только в этом, т.к. макрос написан только внутри этого файла
написал: vikki, в моем файле есть макрос в виде самописной функции - ПроверкаАдреса (сейчас уже точно не помню как назвал). То есть я создал свою функцию, которая будет работать как обычная формула, но только в этом файле. Вам надо будет открывать именно этот файл, вставлять значения в левую и правую таблицу и в столбце С протягивать формулу вниз. Эта функция будет выдавать либо ОК, либо "Не совпадает", либо "Ошибка, не найдено сочетание "пробел буква г пробел". То есть открыли именно этот файл, заполнили 2 таблицы, протянули формулу, там где показало "Не совпадает" проверили, что не совпадает, можно отфильтровать по Не совпадает в столбце С, если данных много и работаете уже с этими данными. Скажем так это будет у вас файл для проверки адресов и всё. Данная функция не будет работать в других файлах Excel, а только в этом, т.к. макрос написан только внутри этого файла
Цитата
New, да я разобралась. Поработала со своими файлами пользуясь этой формулой и собрала список, когда формула проверки пишет "Не совпадает", хотя расхождения не критичны и мне нужно, чтобы по расхождениям приведённым в файле со 2-ой по 13-ую строки (отмечены красным шрифтом) формула проставляла “ОК", если, конечно, это возможно, а также при наличии у вас времени на это. Благодарна вам за помощь.
New, просмотрела файл. Вполне все устраивает. Буду тестировать на новой неделе. Это большая экономия времени. Спасибо за неоценимую помощь. Здоровья и успехов в жизни *
New, не удержалась и стала разбираться с формулой. Да, вижу, что строки выделенные красным цветом ушли из расхождений. Отлично. Но я заметила, что расхождения, которые правильно выявила первая формула (в строках 19, 23, 26, 31, 34 в адресах лишнее слово "литера" и в 34 строке ещё и неполный адрес) во втором файле не выявлены (в последнем файле они в строках 20, 24, 27, 32, 35 ) - напротив этих адресов стоит "ОК", а мне как раз они-то и нужны, чтобы поработать с ними вручную в базах.
vikki, попробуйте поставить утилиту Микрософт Fuzzy Lookup - она как раз задумана для поиска таких совпадений. Перед этим желательно поменять весь текст в одной из таблиц, чтобы были все буквы в левой таблице - заглавные, либо в правой - заглавной были только первые буква слов, остальные прописные. Также через через автозамену меняйте в Екселе КВАРТИРА на кв. (ну, или наоборот), ДОМ на д., двойную запятую ,, на одинарную ,. Дальше устанавливаете точность совпадения - советую для скорости начать с 25-30 -%. Если данных много - придётся оставлять на ночь. Если при такой точности совпадения всё сопоставилось - поздравляю. Иначе придётся устанавливать более точный процент совпадения, что сильно сказывается на времени поиска. Сначала потренируйтесь на маленькой таблице (как у вас в примере), чтоб найти оптимальный минимальный процент точности совпадений.
написал: vikki , попробуйте поставить утилиту Микрософт Fuzzy Lookup - она как раз задумана для поиска таких совпадений. Перед этим желательно поменять весь текст в одной из таблиц, чтобы были все буквы в левой таблице - заглавные, либо в правой - заглавной были только первые буква слов, остальные прописные. Также через через автозамену меняйте в Екселе КВАРТИРА на кв. (ну, или наоборот), ДОМ на д., двойную запятую ,, на одинарную ,. Дальше устанавливаете точность совпадения - советую для скорости начать с 25-30 -%. Если данных много - придётся оставлять на ночь. Если при такой точности совпадения всё сопоставилось - поздравляю. Иначе придётся устанавливать более точный процент совпадения, что сильно сказывается на времени поиска. Сначала потренируйтесь на маленькой таблице (как у вас в примере), чтоб найти оптимальный минимальный процент точности совпадений.
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, спасибо за уделенное время и проведенную работу:). Буду пользоваться последней формулой.