Сверка деклараций в Excel

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

Рассмотрим самый распространенный пример, когда сводная таблица создается на основании обычной таблицы Excel. Чтобы сводная таблица получилась показательной и помогла достичь желаемого результата, таблица с данными должна удовлетворять следующим требованиям:

• каждый столбец должен иметь заголовок;
• в каждый столбец должны вводиться значения только в одном формате. Например, столбец «Дата» должен содержать все значения только в формате Дата; столбец «Название» — названия компаний только в текстовом формате;
• в таблице должны отсутствовать незаполненные строки и столбцы;
• не должно быть объединенных ячеек.

Создайте сводную таблицу по результатам сверки деклараций с контрагентом.

Данные должны представлять собой простую таблицу Excel, каждая строка которой — это строчка из декларации организации или декларации контрагента. Объемы из собственной декларации должны быть положительными, а объемы из декларации контрагента — отрицательными. Объемы указываются таким образом для того, чтобы при равенстве этих двух величин в сумме они давали 0.

Приведенная на рисунке таблица удовлетворяет всем требованиям к исходным данным для сводных таблиц. Скачать пример Excel-файла

Чтобы построить на основе этих данных сводную таблицу:

1. Поставьте курсор на любой ячейке, находящейся внутри данных.
2. Перейдите на вкладку «Вставка» → «Таблицы» → «Сводная таблица».
   
3. В открывшемся окне уже будет задан диапазон данных для сводной таблицы. Выберите, куда разместить сводную таблицу, и нажмите «ОК».
4. Откроется пустой отчет сводной таблицы.
   
   
5. Справа от таблицы появится диалоговое окно «Поля сводной таблицы».
6. Если поле не появилось, нажмите правой кнопкой мыши на любом месте сводной таблицы и в верхнем меню выберите «Показать» → «Список полей».
   
7. Список полей в верхней части диалогового окна — это перечень всех заголовков из исходного списка. Четыре пустые области в нижней части экрана позволяют указать сводной таблице, как требуется обобщить данные. Пока эти области пусты, в таблице тоже ничего нет.
8. Чтобы добавить необходимые поля в сводную таблицу, перетащите их названия на панели «Поля сводной таблицы» в одну из четырех областей сводной таблицы — «Фильтры», «Колонны», «Строки» или «Значения». При этом сводная таблица сформируется автоматически.

Прежде чем приступить к перетаскиванию полей, определитесь, какой итог нужно получить. Ответ на этот вопрос даст представление, какое поле в какую область поместить.

Например, необходимо построить список товарно-транспортных накладных (ТТН) с указанием общего объема по каждой ТТН в своих декларациях, в декларациях контрагента и разницу между этими двумя значениями. Для этого поле «Объем» следует перетащить в область значений, поле «Номер ТТН/ГТД» — в область строк, поле «Источник данных» — в область столбцов, а поле «Поставка/возврат» — в область фильтров.

Подробнее о каждой из областей:

• Область значений. По полям, перенесенным в эту область, выполняются все расчеты исходных данных. В примере с расхождениями логичнее всего поместить в эту область поле «Объем». Функция расчета установится автоматически — «Сумма по полю Объем».
• Область строк. Поля, перенесенные в эту область, размещаются в левой части сводной таблицы и представляют из себя уникальные значения этого поля. В примере с расхождениями перетащите в эту область поле «Номер ТТН/ГТД».
• Область столбцов. Данные, перенесенные в это поле, размещаются в левой части сводной таблицы и представляют из себя уникальные значения этого поля. Область подходит для тех полей, по котором требуется видеть разбивку показателей. В примере с расхождениями перетащите в эту область поле «Источник данных».
• Область фильтров. Эта область является необязательной и находится в верхней части сводной таблицы. Помещение полей в область фильтра отчета позволяет фильтровать данные в таблице. В примере с расхождениями перетащите в эту область поле «Поставка/возврат».

Таким образом, получится сводная таблица по результатам сверки с контрагентом:

В строках таблицы перечислены накладные, содержащие расхождения. Для каждой накладной в столбцах «Декларация КА» и «Моя декларация» указан объем по накладной в декларации собственной организации и в декларации контрагента. Последний столбец является суммой двух предыдущих. Если объемы по накладной с двух сторон совпадают, то в общем итоге получается 0, т. к. данные с одной стороны со знаком «плюс», а с другой со знаком «минус». В противном случае в последнем столбце будет ненулевое значение, говорящее о том, что в чьих-то данных есть ошибка.

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

Добавление поля в таблицу

С помощью панели «Поля сводной таблицы» сводную таблицу можно настроить под нужное представление.

Например, посмотрев на расхождения в объемах по накладным, также необходимо сверится по дате ТТН и по коду АП. Для этого достаточно поочередно перетащить поля «Дата ТТН» и «Код АП» в область строк или просто поставить напротив них галочку в списке полей сводной таблицы.

В таком представлении сводной таблицы внутри каждой накладной появятся вложенные даты ТТН, а внутри каждой даты — коды АП. По каждой строке можно увидеть объем в декларации со стороны своей организации и со стороны контрагента.

Удаление поля из таблицы

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

Изменение порядка полей

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

В примере выше с расхождениями получилась таблица расхождений с группировкой сначала по номеру ТТН, а внутри номера — по дате ТТН. Для анализа ошибок ввода данных в систему потребуется обратный порядок полей: сначала дата, потом номер ТТН. Для этого в панели «Поля сводной таблицы» в области строки перетащите поле «Дата ТТН» вниз, под поле «Номер ТТН/ГТД».

Ниже рассмотрено использование сводной таблицы для поиска расхождений между алкогольными декларациями двух контрагентов.

Разные расхождения можно отследить по разным наборам выводимых полей. Все описанные действия подходят для формата сводной таблицы расхождений, формируемой в Контур.Алкосверке.

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

• в области значений находится поле «Объем». По нему вычисляется сумма значений;
• в области строк — поле «Номер ТТН/ГТД»;
• в области колонн — поле «Источник данных»;
• и в области фильтров — поле «Поставка/возврат».

Скачать пример Excel-файла таблицы

Те поля, которые не добавлены в таблицу, не учитываются при сверке. Указанное выше представление таблицы не учитывает расхождения по дате ТТН, по коду АП, по КПП подразделений и по реквизитам производителей.

В начальной конфигурации видны расхождения контрагентов в объемах по каждой накладной. Ненулевые значения в колонке «Общий итог» выделены красным цветом. При этой конфигурации таблицы легко увидеть накладные, отраженные только одной стороной: собственной организацией или контрагентом, а также найти расхождения в номерах накладных.

Например, накладная № 1002 с общим объемом в 3.15 дал есть только в собственной декларации, у контрагента такой накладной нет. Зато у него есть накладная № MSK1002 с тем же общим объемом. Из этого можно сделать вывод, что это одна и та же накладная, записанная по-разному. Чтобы посмотреть, есть ли еще расхождения в этой накладной, следует на листе с исходными данными найти строчку с ней и исправить номер в столбце «Номер ТТН/ГТД» на номер, указанный у контрагента. После чего необходимо обновить сводную таблицу. Для этого нажмите правой кнопкой мыши по любой ячейке сводной таблицы и выберите пункт «Обновить».

В тех накладных, «Общий итог» по которым равен 0.00000, также могут присутствовать расхождения по каким-либо полям. Чтобы проверить, имеются ли расхождения, следует изменять конфигурацию таблицы, добавляя в нее дополнительные поля и меняя эти поля местами.

Рассмотрим на Excel-файле из примера.

Чтобы проверить, есть ли расхождения в датах ТТН, необходимо перетащить поле «Дата ТТН» в область строки.

В таблице появится поле «Дата ТТН». В примере расхождений по датам нет.

Далее поле с датой ТТН уже не понадобится, поэтому следует удалить его, сняв галочку в полях сводной таблицы.

Чтоб проверить, есть ли расхождения в кодах продукции, следует перетащить поле «Код АП» в область строки. В таблице появится поле «Код АП». В примере есть расхождения.

В примере видно, что по накладной № 5043 суммарный объем сходится, но расходятся коды продукции: в собственной декларации указан код 402, в декларации контрагента — код 403. По остальным накладным коды продукции, указанные в собственной декларации и декларации КА, совпадают.

Поле «Код АП» также можно убрать из таблицы.

Аналогичным образом следует проверить, есть ли расхождения по полям «Мой КПП» и «КПП контрагента».

В файле-примере «Мой КПП» одинаково указан в собственной декларации и декларации КА, а по КПП контрагента есть расхождения почти во всех накладных.

Такое расхождение может возникнуть по двум причинам — если при заведении накладной было выбрано неверное подразделение контрагента или в собственной учетной системе указан КПП контрагента, которого нет в декларации контрагента. Чтобы понять, где допущена ошибка, следует убрать из таблицы поле «Номер ТТН/ГТД».

По такой конфигурации таблицы можно сказать, что у контрагента есть подразделение с КПП 610232001, данных о котором нет в собственной декларации, а соответствующие обороты указаны по КПП КА 610245001. Вместе с этим в собственной декларации оборот в 0,9 дал отражен по КПП КА 509950001, которого нет в данных самого контрагента. Добавив в таблицу поле «Номер ТТН/ГТД», можно увидеть, в каких накладных содержатся расхождения по КПП КА. Для устранения этих расхождений следует сверить свои данные с первичной документацией и связаться с контрагентом для выяснения правильных КПП.

Далее следует проверить, есть ли расхождения в реквизитах производителя. Для этого следует оставить в области строк только поле «Номер ТТН/ГТД» и добавить к нему поля с ИНН и КПП производителя/импортера.

По ИНН производителя расхождений в примере нет, поэтому это поле следует убрать из таблицы.

По КПП производителя расхождения есть — в накладной № 312 в собственной декларации и в декларации КА указаны разные КПП производителя.

Далее следует вернуться к первоначальной конфигурации сводной, оставив в области строк только поле «Номер ТТН/ГТД».

В примере в графе «Общий итог» по двум накладным № 2233 и № 311, которые есть и в собственной декларации, и в декларации контрагента, стоят ненулевые значения. Когда выше рассматривались расхождения без учета некоторых параметров, общий итог по двум этим накладным ни в одной из конфигураций сводной таблицы не становился равным нулю. Это означает, что в этих накладных расходятся объемы, указанные в собственной декларации и декларации контрагента.

Чтобы в этом убедиться, следует добавить в таблицу все сравниваемые поля: «Дата ТТН», «Код АП», «Мой КПП», «КПП контрагента», «ИНН производителя» и «КПП производителя». Для удобства работы следует отфильтровать данные только по номерам накладных № 2233 и № 311. Общий итог не равен нулю, следовательно, это расхождение в значении объема оборота.

Такая конфигурация таблицы содержит все сверяемые поля — это вид таблицы с учетом всех параметров. При удалении поля из таблицы оно перестает учитываться при сверке.

Таким образом, настраивая список полей сводной таблицы, можно менять внешний вид и степень подробности протокола расхождений.