Сверка данных из разных отчетов, шаблонов, выгрузок порой занимает много времени, а часто ставит в тупик, навивает тоску и безысходность. Хорошим помощником в сверке данных является Excel.
Простой пример. Остаток на конец предыдущего месяца не идет с остатком на начало текущего месяца. К счастью у нас была сохранена ведомость за предыдущий месяц и можно найти расхождение.
При выводе в Excel отчет из 1С выглядит так как на рисунке.
Для красоты формата некоторые столбцы объединены (например, нужные нам, столбцы 1 и 2 и 7 и 8). Для дальнейшей работы нам их нужно снять объединение ячеек, т.е. выделить эти столбцы или весь лист, нажав на область выделенную синим прямоугольником. Когда нужное будет выделено нужно просто нажать на кнопку по красной стрелке.
Практические советы по работе с экономическими данными в Excel, читайте в моей книге "Excel для экономистов. 10 статей про отчеты, сверки, сопоставления"
В примере мы будем сравнивать июнь и июль. Для дальнейшей работы нам нужны:
- по ведомости за июнь столбцы Контрагенты и Дебетовое сальдо на конец;
- по ведомости за июль столбы Контрагенты и Дебетовое сальдо на начало
Все остальные ненужные столбцы удаляем, а из нужных столбцов делаем новую таблицу:
1. Шапку таблицы делать обязательно, т.к. без неё не будет формироваться сводная таблица;
2. Столбец примечание обязательный, т.к. он позволит разделить ваши данные на то, что сверяется и на то, с чем сверяется.
Так как мы сверяем только два периода, то в примечание только два признака "на конец" и на "на начало".
Делаем сводную таблицу
1) В подготовленной по предыдущем этапе таблице ставим указатель мыши на верхнюю левую ячейку (в нашем примере это – ячейка «Покупатель»
2) На закладке Вставка нажимаем пиктограмму «Сводная таблица»
3) Выскакивает диалоговое окно «Создание сводной таблицы», наша подготовительная таблица при этом выделяется пунктирной линией (она должна выделится полностью). Если в диалоговом окне все устраивает нажимает кнопку ОК
4) Появляется диалог по настройке сводной таблицы. Делаем настройки как в блоке 5.
5) При этом в левом верхнем углу будет отображаться макет сводной таблицы и данные, которые туда попадут.
6) Формат значений настраивается в блоке 6.
7) В результате получим такую таблицу
Столбец общий итог можно убрать в настройках, так как в данном случае он не имеет смысла и на нам не нужен. По большому счету в полученной таблице мы уже достигли поставленных целей, т.е. видим что остатки различаются по покупателям 5001, 7, 8. Дальше эта таблица не нужна, так как для разборок с коллегами Excel не нужен.
В нашем примете, таблица маленькая и различия видны сразу. На практике количество контрагентов в разы больше и различия могут быть не так очевидны.
Быстрой выявить отклонения можно двумя способами:
1. Выделить сводную таблицу (область выделения должна быть больше самой таблицы) и вставить ее как значение. Форматы пропадут, данные останутся и с ними можно будет делать любы расчеты и преобразования.
2. Вставить нужную формулу за пределами сводной таблицы
Формула должна быть вставлена выше самой таблице (строка 2), т.е. должна ссылаться не на саму таблицу, а на аналогичные ячейки выше. Если ссылка будет сделана на данные сводной таблицы, "протягивать" эту формулу вниз для вычисления отклонений по всем контрагентам нельзя, так как ссылки будут работать неверно. Можете проверить сами.
В отличие от первого способа, во втором не сбиваются форматы, работать удобнее.
Еще одно преимущество второго способа. Если цель нашей сверки не просто выявить расхождения, но и устранить их. Например, мы сверяем отгрузку за один период из разных баз данных (бухгалтерского учета и складской программы). Если по мере сверки выявленные расхождения будут устраняться, изменения мы можем вносить в исходную таблицу с данными, на основании которых была сделана сводная таблица, после чего сводную таблицу нужно будет обновить и отклонения будут пересчитаны.
Таким способом можно сравнить практически любые массивы данных, если они выгружаются в Excel. Возможные варианты применения:
- изменения в различных периодах,
- сверка аналогичных данных из разных приложений,
- сверка результатов выгрузки/загрузки данных
- поиск ошибок ввода
Если формат отчета, выгружаемого в Excel, не позволяет сформировать сводную таблицу, можете воспользоваться советами статьи "Как преобразовать форматы, чтобы было удобно работать в Excel"
Как сделать сопоставимыми списки для формирования сводной таблицы читайте в статье "Таблицы Excel для сопоставимости данных отчетности"
Для более быстрого поиска нужной статьи я сделала подборки:
Бюджетирование
Excel для работы с данными
Себестоимость и цены
Учет закупок и расчетов в Excel
Примеры в Excel и файлы к статьям можно найти по этой ссылке