Сводная таблица: группируем расхождения за 5 минут

Пересчёт закончен, сверка с 1С проведена, таблица расхождений готова: четыреста, шестьсот, а иногда и больше строк. Руководитель её читать не станет, а вы не успеете просмотреть каждую строку. Инструмент, который делает такую таблицу понятной с первого взгляда, в Excel давно есть: сводная таблица (Pivot Table). Она собирает строки по складам, товарным группам и причинам и показывает, где скопилась основная разница. Первый раз построить её можно меньше чем за пять минут, если исходная таблица подготовлена правильно.

Подготовьте исходную таблицу

Сводная зависит от качества данных. В таблице должны быть такие столбцы:

• код и название;
• товарная группа и склад;
• остаток 1С и факт;
• разница: факт минус 1С;
• цена и сумма разницы: разница × цена;
• вид: излишек, недостача или совпало;
• причина.

В столбец «вид» впишите формулу IF(G2>0;"излишек";IF(G2<0;"недостача";"совпало")), в русском Excel это ЕСЛИ. Превратите таблицу в «Умную» командой Ctrl+T: тогда при добавлении строк сводная будет их видеть. Заголовки должны стоять в одной строке, объединённых ячеек и пустых строк быть не должно.

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

Строим сводную таблицу

1. Выделите любую ячейку внутри таблицы и выберите Insert, PivotTable.
2. Перетащите товарную группу в область «Строки» (Rows).
3. Перетащите вид в область «Столбцы» (Columns).
4. В область «Значения» (Values) поставьте сумму разницы, способ расчёта Sum.
5. Вторым значением возьмите число кодов (Count): оно покажет, на скольких позициях есть разница.
6. Склад поставьте в область «Фильтры» (Filters).

Отсортируйте результат по убыванию суммы: самые крупные группы окажутся наверху. Чтобы заглянуть внутрь группы, добавьте в «Строки» после группы название товара: сводная раскроет каждую группу и покажет товары по отдельности.

На что смотреть

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

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

Условный пример: в таблице на 420 строк сводная показала, что одна группа (мука и масло) даёт две трети недостачи. Значит, разбор надо начинать с неё, а не с остальных пятидесяти групп.

Срез по причинам

Постройте сводную второй раз, поставив вместо группы причину. Условный пример: по сумме непроведённые документы дали 30 процентов, ошибки единиц 20, дубли 15, настоящая недостача 20, неизвестно 15. Первые три причины решаются исправлением данных, и только настоящая недостача и «неизвестное» требуют отдельной проверки по товару. Доля «неизвестно» служит мерой качества работы: чем она меньше, тем лучше найдены причины. Двойной щелчок по ячейке сводной (drill-down) открывает строки, из которых она состоит, на отдельном листе, и при повторной проверке причины это удобно.

Типичные ошибки

• Исходная таблица изменилась, а сводная не обновлена: правая кнопка, Refresh.
• Сумма разницы превратилась в текст: сводная её не считает.
• Столбец цены пуст: сумма разницы получится нулевой, и важный товар ускользнёт.
• Название склада записано по-разному («склад 1», «склад №1»): сводная сделает из них две разные строки.

Вывод

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