Шаблон остатков в Excel: выпадающие списки, защита и проверка
Хороший шаблон останавливает ошибку при вводе: единицы из списка, защищённые формулы и подсветка неверных строк. Как его собрать.
Большинство ошибок в таблице остатков появляется после пересчёта, при ручном вводе данных. Единица записывается как «шт» вместо «шт.», количество превращается в текст, формула случайно затирается. Находить ошибку потом дорого, а остановить её при вводе почти бесплатно. Для этого нужен готовый шаблон, который не принимает неверное значение и закрывает важные ячейки от правки. Ниже — как собрать такой шаблон.
Выбор из списка
Каждый столбец, который заполняется вручную, — источник ошибок. Для повторяющихся значений (единица, склад, статус, причина) включите выбор из списка. В Excel это делается командой проверки данных в меню «Данные» (Data Validation): тип «Список», источник — диапазон значений на отдельном листе. Сообщение об ошибке тоже напишите сами: конкретная фраза «Выберите единицу из списка» помогает лучше, чем стандартное «Введённое значение неверно».
Списки храните в самом шаблоне, на отдельном листе «Списки». Если появится новая единица, вы запишете её в одном месте, и все выпадающие списки обновятся. Названия единиц в списке точно сверьте с названиями в вашей системе, иначе при загрузке получите ошибку «не найдено».
Для столбца с количеством тоже задайте правило: только целое или десятичное число, не меньше нуля. Попытка ввести текст вызовет сообщение об ошибке, и проблема «количество стало текстом» уйдёт.
Для кода товара список получится слишком длинным, поэтому пойдите другим путём. Сотрудник вводит только код, а название и единицу формула (VLOOKUP или XLOOKUP) подтягивает с листа номенклатуры. Если код введён неверно, вместо названия появится «не найдено», и ошибка заметна сразу. Название вручную не пишется, поэтому не возникает и разницы между «Мука 50 кг» и «Мука, 50 кг».
Защита формул
В шаблоне есть ячейки двух видов: те, что заполняет сотрудник, и те, где стоят формулы или заголовки. Вторая группа блокируется. Шаги:
- Выделите ячейки ввода и снимите с них защиту (по умолчанию все ячейки заблокированы).
- Защитите лист. Права оставьте минимальными: только выбор и правка разрешённых ячеек.
- Выделите ячейки ввода заметным цветом, например светло-жёлтым. Сотрудник с первого взгляда видит, куда писать.
Защита листа — не средство безопасности, а страховка от случайной ошибки. Пароль где-нибудь сохраните, иначе потом и сами не сможете поменять шаблон.
Подсветка ошибок и столбец «Статус»
С помощью условного форматирования сделайте неверную строку сразу заметной. Полезные правила:
- код повторяется: COUNTIF($A:$A;$A2)>1;
- количество отрицательное или пустое;
- единица пустая, но количество введено;
- длина кода отличается от заданной: LEN($A2)<>6.
Пока строка красная, работу над ней не продолжают, сначала её исправляют. Запишите это правило в инструкцию к шаблону.
Вместе с цветом добавьте последний столбец «Статус»: он проверяет все условия строки сразу. Примерная формула: IF(AND(A2<>"";ISNUMBER(D2);D2>=0;COUNTIF($A:$A;A2)=1);"OK";"ошибка"). В верхней части таблицы покажите число ошибок: COUNTIF(H:H;"ошибка"). Перед отправкой файла это число должно быть равно нулю.
Условный пример: в шаблон ввели 800 строк, счётчик показал «ошибок: 6». Шесть строк находятся через фильтр за минуту. Без шаблона эти шесть ошибок вылезли бы после загрузки, при сверке с полкой, а искать их причину пришлось бы гораздо дольше.
Правила работы с шаблоном
Даже лучший шаблон не работает, если ему не доверяют. Несколько простых правил:
- существует только один текущий шаблон, и он лежит в общей папке;
- на первом листе записаны инструкция и номер версии;
- менять структуру шаблона (добавлять и удалять столбцы) может только один ответственный;
- для файлов на старом шаблоне записано правило переноса в новый: какой столбец куда переходит.
Покажите сотрудникам, как работать с шаблоном, за пять минут и оставьте у них перед глазами один заполненный образец. Тот, кто видел, как заполняется образец, ошибается заметно реже.
Вывод
Шаблон с выбором из списка, закрытыми формулами, подсветкой ошибок и столбцом «Статус» останавливает ошибку прямо при вводе. SkladSoft при объединении таблиц Excel в один чистый список настраивает шаблон на этих принципах и передаёт перечень найденных в файлах ошибок вместе с журналом изменений.