Повторы SKU по складу в Excel: как настроить подсветку

1. Определите, какой повтор действительно требует проверки

Работайте с копией одного снимка остатков на определённый момент. Для нашего примера выбран один кабинет, одна единица учёта — штуки — и один статус запаса. По внутреннему условию таблицы каждой паре «SKU + склад» должна соответствовать одна строка. Это учебная структура, а не описание универсального формата выгрузки WB или Ozon.

Если источник отдельно показывает партии, размеры, сроки годности или доступный и резервный запас, двух полей недостаточно. Два остатка одного SKU на одном складе тогда могут быть законными строками. Включите нужные признаки в определение записи до настройки поиска. Иначе аккуратная подсветка будет регулярно отправлять на проверку корректные данные.

2. Подготовьте семь учебных строк

Разместите SKU в столбце A, код склада в B, количество в C. Строка 1 содержит заголовки, строки 2–8 — данные ниже. В D добавим число совпадений. Символ «—» в последней строке таблицы означает действительно пустую ячейку A8: не вводите туда тире.

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

3. Посчитайте совпадения пары в отдельном столбце

В D2 введите формулу =ЕСЛИ(ИЛИ(A2="";B2="");"";СЧЁТЕСЛИМН($A$2:$A$8;A2;$B$2:$B$8;B2)) и скопируйте до D8. Английский вариант: =IF(OR(A2="",B2=""),"",COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,B2)). Названия функций и разделитель аргументов зависят от локализации Excel.

СЧЁТЕСЛИМН считает строки, удовлетворяющие всем заданным условиям одновременно. В примере сравниваются и SKU, и склад. Диапазоны имеют одинаковый размер. Абсолютные ссылки удерживают границы поиска, а A2 и B2 при копировании переходят к следующей строке.

Проверка пустых полей оставляет счётчик незаполненным. Это сознательный сигнал «ключ не готов», а не ноль совпадений. В документации Microsoft отдельно отмечено, что ссылка на пустую ячейку в критерии COUNTIFS трактуется как нулевое значение; поэтому незавершённую запись мы не отправляем в обычный подсчёт.

4. Подсветите всю строку по счётчику

Для настольного Excel выделите A2:D8, оставив A2 активной ячейкой. Откройте «Главная → Условное форматирование → Создать правило» и выберите использование формулы. Введите =И($A2<>"";$B2<>"";$D2>1); английский вариант — =AND($A2<>"",$B2<>"",$D2>1). Задайте светлую заливку, при которой текст остаётся читаемым.

В управлении правилами проверьте поле «Применяется к»: =$A$2:$D$8. У ссылок $A2, $B2 и $D2 закреплены столбцы, но не номер строки. Поэтому цвет относится к текущей строке. Ссылка $D$2 заставила бы все строки смотреть на один счётчик.

Число в D оставьте видимым: оно объясняет цвет и позволяет проверить результат без различения оттенков. Правило «Повторяющиеся значения» для одного столбца A отвечает на другой вопрос — повторяется ли SKU вообще. Для нашего складского снимка этого недостаточно.

5. Сверьте результат с контрольными строками

В учебном файле счётчики D2:D8 должны быть 2, 1, 2, 2, 1, 2 и пустое значение. Подсвечиваются строки 2, 4, 5 и 7. Это четыре строки в двух повторяющихся группах. Число подсвеченных строк нельзя называть количеством лишних записей: для каждой группы ещё предстоит выяснить причину.

Строка 3 с A-101 на складе SPB остаётся без подсветки: такого сочетания больше нет. Строка 8 тоже не окрашивается нашим правилом, но её нужно отдельно отобрать по пустому SKU. Отсутствие цвета здесь не означает готовность данных.

Установка формулы и заливки не меняет семь исходных строк и сумму 54. Такой контроль помогает заметить случайное удаление или редактирование значения во время настройки. Пример рассчитан независимо; фактическое отображение правила проверьте в своей версии Excel.

6. Разберите каждую отмеченную группу

У A-101/MSK два значения по 12. Равенства пары и количества ещё недостаточно для удаления. Сверьте момент снимка, партию, статус запаса и происхождение строк. Только если источник подтверждает, что это одна и та же запись, исключайте одну копию в рабочем результате, сохраняя основание.

У B-202/MSK значения 5 и 6. Нельзя автоматически выбрать большее, сложить их или оставить первое. Это может быть исправление, разное время выгрузки или неучтённая детализация. Пока причина не установлена, группа остаётся спорной.

Если в нашем условном примере подтвердить только повтор на 12, число строк станет 6, а контрольная сумма — 42. Но объявлять 42 достоверным остатком нельзя: группа B-202 всё ещё не разобрана, а четыре штуки в строке без SKU не привязаны к товару. Команда Excel «Удалить дубликаты» изменяет данные, поэтому её не следует путать с диагностической подсветкой.

7. Проверьте ограничения текстовых кодов

До применения формулы к рабочему файлу просмотрите допустимый алфавит кодов. В критериях COUNTIFS знаки * и ? имеют специальное значение: они соответствуют последовательности символов и одному символу. Microsoft описывает знак ~ для поиска буквальных * и ?. Простая формула из примера рассчитана на коды без таких символов; для другого формата сначала настройте и испытайте буквальное сравнение.

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

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

8. Испытайте правило после добавления данных

Формулы примера ограничены строкой 8. Новая строка 9 не попадёт в поиск сама по себе только потому, что расположена рядом. В тестовой копии добавьте A-101/MSK с количеством 3, расширьте оба диапазона поиска до строки 9, протяните счётчик и измените область подсветки до A2:D9. У трёх строк этой группы ожидается счётчик 3.

Отдельно добавьте тот же SKU с новым складом: для этой пары ожидается 1. Верните учебные изменения перед работой с реальными данными. Такой тест проверяет границы метода и помогает обнаружить ошибочно закреплённый номер строки.

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

9. Передайте результат с понятными исключениями

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

Не превращайте цвет в статус согласования. Достаточно короткой записи с ответственным, ссылкой на источник и объяснением исправления. При следующем снимке повторите проверку на новых данных: вчерашнее решение не делает сегодняшние совпадения автоматически безопасными.

После сверки файла переходите к вопросу бизнеса: какие изменения по товарам требуют внимания и что проверить перед пополнением. В разделе HelpStat «Рука на пульсе» можно начать с доступного обзора показателей. Формулы и проверка складского файла остаются отдельной процедурой команды.