Аудит справочника номенклатуры по выгрузке: десять проверок

Аудит справочника номенклатуры это подсчёт ошибок по всему файлу выгрузки, без выборки. Десять счётчиков в Excel показывают, сколько карточек без артикула и единицы, сколько одинаковых наименований, сколько позиций заведено с разными единицами и сколько помечено на удаление. На это уходит несколько часов. Дубли, записанные разными словами, такой подсчёт не находит, и это его граница.

Аудит отвечает на вопрос «в каком состоянии справочник сейчас». Он не говорит, сколько займёт чистка: для этого нужна выборка и пересчёт в часы, и это отдельная задача оценки объёма. Здесь только счётчики, которые Excel считает по каждой строке.

Что нужно на входе: выгрузка из шести колонок

Для всех десяти проверок хватает одного файла. В нём шесть колонок:

1. Код карточки в базе.
2. Наименование в том виде, в каком его видят пользователи.
3. Артикул, если он ведётся.
4. Единица измерения, базовая.
5. Группа или папка справочника.
6. Пометка удаления: да или нет.

Помеченные на удаление карточки в выгрузку стоит включить. Без них проверка 8 не посчитается. Как собрать такой файл, описано в статье о выгрузке номенклатуры в Excel. Если в базе есть поле с датой последнего движения, его можно добавить седьмой колонкой. Это необязательно.

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

Заполненность: артикул, единица, группа

Первые три проверки самые простые. Они показывают, по каким полям вообще можно сравнивать строки.

Проверка 1. Пустой артикул. Формула =СЧИТАТЬПУСТОТЫ(C2:C100000) считает пустые ячейки в колонке артикула. Разделите результат на число строк, получится доля. Ячейки с пробелом или прочерком формула пустыми не считает. Их ищут фильтром по значениям «-», «нет», «б/а».

Проверка 2. Пустая единица. Та же формула по колонке единиц. Карточка без единицы обычно заведена вручную в спешке. Такие строки полезно просмотреть целиком.

Проверка 3. Общая группа. Пустая группа встречается редко. Чаще позиции лежат в папках «Прочее», «Разное», «Новая», «Основная». Формула =СЧЁТЕСЛИ(E2:E100000;"Прочее") считает одну такую папку. Список таких названий у каждого справочника свой, его составляют по сводной таблице групп.

Одинаковые наименования и почти одинаковые

Точные повторы наименования удобнее искать сортировкой. Функция СЧЁТЕСЛИ здесь подводит. Знаки «*» и «?» она понимает как шаблон поиска, а в номенклатуре они встречаются часто: «Анкер 10*100». На длинных наименованиях она возвращает ошибку.

Проверка 4. Точные повторы. Отсортируйте лист по наименованию. В соседнем столбце напишите =ЕСЛИ(B3=B2;1;0) и протяните вниз. Сумма столбца равна числу строк, у которых есть точная пара выше. Сравнение в Excel не различает регистр, поэтому «БОЛТ» и «болт» тоже совпадут.

Проверка 5. Почти одинаковые. Сделайте столбец с приведённым наименованием. Формула убирает лишние пробелы, точки и запятые и переводит текст в нижний регистр:

=СТРОЧН(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;".";" ");",";" ")))

Отсортируйте по этому столбцу и повторите сравнение соседних строк. Разница между проверкой 5 и проверкой 4 показывает, сколько повторов прячется за пробелами и знаками. Так обычно находят пары «Болт М12х60» и «Болт М12х60.».

Проверка 6. Один артикул, разные наименования. Отсортируйте по артикулу и сравните соседние строки по артикулу и по наименованию сразу: =ЕСЛИ(И(C3=C2;C3<>"";B3<>B2);1;0). Такие пары бывают дублем, записанным по-разному. Бывают и ошибкой: один артикул вписан в две разные карточки. Обе ситуации требуют просмотра.

Одна позиция с разными единицами

Проверка 7. Постройте сводную таблицу. В строки положите приведённое наименование из проверки 5, в столбцы единицу, в значения количество. Строки, где заполнено больше одного столбца, означают одну вещь, заведённую в штуках и в килограммах, в метрах и в бухтах, в литрах и в канистрах.

Высокая доля здесь обычно значит, что фасовку вели отдельными карточками. Так ли это у вас, видно по самим парам. Подробнее о том, как вести упаковки внутри одной карточки, в статье про единицы и упаковки.

Отдельно посмотрите на написание самих единиц. «шт», «шт.», «штука» и «Шт» в сводной таблице дают разные столбцы. Это ошибка в справочнике единиц. Считайте её отдельной строкой итога.

Карточки с пометкой на удаление и без движения

Проверка 8. Пометка на удаление. Формула =СЧЁТЕСЛИ(F2:F100000;"Да") считает помеченные карточки. Само число мало что говорит. Важнее пересечение: есть ли у помеченных карточек живые пары с тем же наименованием. Поставьте фильтр по пометке и посмотрите результат проверки 4 для этих строк. Если у помеченной строки есть живой близнец, её когда-то уже признали дублем, но не объединили до конца.

Карточки без движения считаются, только если в выгрузке есть дата последнего документа. Тогда фильтр «старше двух лет» даёт кандидатов в архив. Без этой колонки проверку пропустите и отметьте в итоге, что она не делалась.

Латиница и кириллица в одном названии

Проверка 9. Латинские «a», «c», «e», «o», «p», «x» выглядят как русские. Строка «Болт М12» с латинской «М» для Excel отличается от строки с русской. Такие пары проверки 4 и 5 не поймают.

Формула ниже считает в наименовании латинские буквы, похожие на русские:

=СУММПРОИЗВ(ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(СТРОЧН(B2);{"a";"c";"e";"o";"p";"x";"y";"k";"m";"t";"h";"b"};"")))

Если результат больше нуля, строку стоит открыть. Часть таких строк законна: в обозначении подшипника «2RS» или в кабеле «LS» латиница нужна. Поэтому счётчик даёт кандидатов, а решает человек.

Проверка 10. Служебные слова в наименовании. Пользователи помечают карточки прямо в названии: «не использовать», «старое», «дубль», «!!!», «удалить». Формула =СЧЁТЕСЛИ(B2:B100000;"*не исп*") считает одну такую метку. Каждая найденная строка означает решение, которое приняли и не довели до конца.

Как собрать итог в одну таблицу

Итог аудита помещается на один лист. По каждой проверке укажите число строк и долю от всего справочника. Рядом оставьте ссылку на фильтр или столбец, где эти строки видно.

Руководителю удобнее читать итог готовыми фразами. Например: «В 1 из 6 карточек нет артикула, такие позиции сравниваются только по наименованию». Цифры здесь условные, свои вы получите из таблицы. Каждая строка итога должна вести к действию: заполнить, решить правило, проверить пары.

Целевых значений по долям здесь нет специально. Справочник ремонтного склада и справочник торговой компании устроены по-разному, и одна и та же доля значит для них разное. Полезнее сравнивать свой справочник с ним же через полгода.

Что аудит не покажет: дубли по смыслу

Все десять проверок сравнивают текст. Строки «Подш. 180205» и «Подшипник 6205-2RS» описывают один подшипник, но в тексте у них общего мало. Строки «Кабель ВВГнг 3х2,5» и «Каб. ВВГ-нг 3*2.5 мм2» после приведения тоже не совпадут. Такие дубли в справочнике МТР составляют основную массу, и Excel их не видит. Почему простые способы на этом ломаются, разобрано в статье о поиске дублей при разных названиях.

Есть и обратная ошибка. Проверка 5 может свести вместе строки, которые различаются одним символом и означают разные вещи: «6205-2RS» и «6205-2Z», «Ру16» и «Ру25». Поэтому результат проверок 4–6 нельзя сразу отдавать на объединение. Это список кандидатов.

Предполагаю, что после аудита у вас останется вопрос, сколько дублей прячется за разными словами. Так ли это?

Что делать с результатом

• Проверки 1–3 и 10 исправляются по месту. Заполните поля, разнесите «Прочее» по группам, доведите до конца помеченные карточки.
• Проверки 4–6 дают список кандидатов в дубли. Перед объединением каждую пару смотрит человек, который знает эти позиции.
• Проверка 7 требует решения о правилах: фасовка отдельной карточкой или упаковкой внутри одной. Сначала правило, потом объединение.
• Проверка 8 часто показывает недоделанные чистки прошлых лет. Их стоит закрыть до новой.
• Проверка 9 исправляется заменой букв, но только в словах, где латиница случайна.

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

Порядок работ по итогам тоже важен. Сначала закройте недоделанные решения из проверок 8 и 10, потом утвердите правила по единицам, и только после этого берите кандидатов в дубли. Иначе часть пар придётся пересматривать дважды.

Где этот раздел найти в интерфейсе, написано в материале где в 1С находится раздел НСИ. Чем различаются два поля названия, разобрано в материале чем наименование отличается от полного наименования. Про коды единиц и их загрузку см. классификатор единиц измерения в 1С. О том, что слово «нормализация» значит в разных областях, см. три значения слова «нормализация данных».

Если доли по проверкам 4–6 и 9 высокие, справочник почти наверняка содержит и дубли по смыслу. Их поиск уже выходит за пределы Excel. Его можно проверить на части своего справочника: пришлите 5–10 тысяч строк на бесплатную пробу, и в ответ придёт тот же файл с группами дублей и листом «Вопросы к вам».

Вопросы про аудит справочника

Сколько строк достаточно для аудита?

Весь справочник. Аудит считает доли по каждой строке, выборка здесь не нужна. Формулы из статьи работают и на сотнях тысяч строк. Сортировка со сравнением соседних строк при этом работает быстрее, чем СЧЁТЕСЛИ по всему столбцу.

Нужна ли для аудита база 1С?

Нет. Хватает файла выгрузки с шестью колонками. База нужна один раз, чтобы сделать выгрузку. Дату последнего движения тоже берут из базы, если хотят посчитать карточки без движения.

Чем аудит отличается от оценки объёма чистки?

Аудит считает ошибки по всему файлу и показывает состояние справочника. Оценка объёма берёт выборку, смотрит дубли по смыслу и переводит результат в часы работы.

Как часто повторять аудит?

Удобно после каждой чистки и затем раз в квартал или полгода. Один и тот же набор проверок показывает, растут ли доли снова и где именно.