© Сорокин Б.Б. 2016 г.

Сравнение значений двух файлов

Добрый день, мне необходимо решить небольшую проблему (сравнение значений двух файлов). Файл "1", содержит много группировок и в этом файле находится вся основная информация, во втором файле "2" всего два столбца. Требуется в файле "2" в третьем столбце напротив каждого номера написать наименование контрагента, которому этот номер принадлежит, т.е. этот номер нужно найти в файле "1" и оттуда взять инф. какому агенту он принадлежит. Если номера из файла "2" нет в файле "1" то просто будет пустая ячейка.

Вот и наступил "звездный час" самой красивой функции Excel - ВПР! Как применять эту функцию для сравнения баз данных я уже описывал здесь, однако вернемся к конкретному вопросу.

Файл 1 представляет собой структурированный отчет, экспортированный из 1С в Excel и после удаления структуры имеет следующий вид.

файл 1

Файл 1

файл 2

Файл 2

В графе "Характеристика номенклатуры" можно рассмотреть номера сотовых телефонов, номера сим-карт, характеристики тарифных планов, ФИО контрагентов и много чего еще. Возможно, для отчета это и хорошо, но для базы данных это очень плохо, как это все удовольствие автоматизировать, если заранее неизвестно где, чего и сколько. Кроме того, при более внимательном рассмотрении выяснилось, что в некоторых записях к номерам телефонов добавляется лидирующая восьмерка, в одних записях сначала идет номер телефона, потом номер сим-карты в других - строго наоборот. Так сказать, ошибки ввода, каждый забивал, как хотел. Учитывая, что в первом файле почти тридцать восемь тысяч строк править эти ошибки руками невозможно. Попытки получить именно выгрузку нормальной не структурированной базы данных из 1С к успеху не привели, видимо администратора 1С в этой организации нет.

Файл 2 порадовал больше, там все просто номера телефонов и деньги, осталось к этим номерам привязать контрагента из файла 1.

Начнем с файла 1. Надо придумать формулу, которая позволит выделить из записей собственно номера телефонов и учтет при этом замеченные ошибки ввода. Несколько облегчает задачу то, что количество знаков в номере телефона заранее известно, постоянно и равно 10, а знак "запятая" всегда разделяет номер телефона и сим-карты. Попробуем это использовать в формуле.

формула

Здесь функция НАЙТИ(",";B12)=11 ищет позицию запятой и если она равна "11" это означает что номер телефона стоит в начале и лидирующей "8" нет. В этом случае с помощью функции ЛЕВСИМВ(B12;10) выбираем первые 10 знаков и получаем номер телефона. Если это условие не выполняется, проверяем наличие запятой на двенадцатой позиции - НАЙТИ(",";B12)=12. Выполнение этого условия говорит о том, что номер телефона стоит первым, но в нем присутствует лидирующая "8". В этом случае с помощью функции ПСТР(B12;2;10) выбираем десять знаков номера телефона начиная со второй позиции. Невыполнение и этого условия означает, что в записи сначала идет номер сим-карты, а потом номер телефона. В этом случае просто выбираем десять правых символов с помощью функции ПРАВСИМВ(B12;10). Таким образом, при помощи формулы нам удалось выделить номера телефонов с учетом замеченных ошибок ввода.

выделение номера телефона

Конечно, в записях возможны и другие ошибки, которые сразу незаметны. Например, вообще отсутствует запятая и тогда наша формула выдаст ошибку или еще что-нибудь. Поэтому будет не лишним проверить и эти варианты.

проверка на ошибки

Здесь в столбце "Ошибка" функция ЕОШ(C12) проверяет наличие ошибки в столбце "Номер ТФ". В столбце "Первый символ" функция ЛЕВСИМВ(C12;1) отбирает первый символ из столбца "Номер ТФ". По фильтру можно увидеть везде этот символ равен "9" или нет. Можно еще придумать другие проверки чтобы вычислить ошибки в тридцати восьми тысячах строк записей!!!

После того как с выделением номеров закончено остается прокопировать в соседнем столбце контрагентов напротив номеров телефонов. Дело тоже достаточно хлопотное, но вполне выполнимое. В результате получаем следующую табличку.

таблица

Теперь берем файл 2 и с помощью функции ВПР привязываем контрагентов файла 1 к номерам в файле 2. Учитывая, что при отсутствии искомого номера в файле 1 надо оставить пустую ячейку, формула будет выглядеть так.

формула

Здесь функция ЕСЛИ возвращает пустое значение "" если функция ВПР возвращает ошибку #Н/Д т.е. не находит искомый номер в файле 1, если номер найден, функция ЕСЛИ возвращает ФИО контрагента из файла 1. Функция СЦЕПИТЬ(A3) используется для того, чтобы задать искомому номеру в файле 2 текстовый формат, так как номера, полученные в результате выборки в файле 1 имеют именно текстовый формат.

Получаем результат.

привязка контрагентов

Вобщем, как то так. Написал, как думал, если что непонятно, пишите на мыло, отвечу.