budget excel logo
8-800-100-93-44
звонок по России бесплатный

Подавляющее большинство отечественных предприятий как основную учётную систему используют 1С. Однако, несмотря на все свои достоинства, 1С – не самая совершенная система.

Поэтому в большинстве компаний существует необходимость дополнительно обрабатывать данные, получаемые из 1С. Совершенно естественно, что для этого используется Excel.

Однако, если выгрузить данные из 1С в том виде, в котором они там представлены – их будет очень сложно автоматически обработать при помощи Excel.

Поэтому большинство компаний вынуждено обрабатывать данные, полученные из 1С, практически в ручном режиме.

В сегодняшней статье мы с вами разберём несколько хитростей, которые позволяют быстро перевести данные, получаемые из 1С в такой вид, в котором они станут удобны для автоматической обработки при помощи Excel.

Для начала давайте посмотрим, какие данные и как обычно выгружают из 1С. Чаще всего данные для выгрузки в Excel получают из отчётов.

Выглядит это примерно так. Например, нам нужны данные по 20 счёту за январь 2006 года. Мы открываем 1С, заходим в меню «Отчёты» и выбираем, например, отчёт «Обороты счёта».

Выбираем 20 счёт, период – январь 2006 года и формируем отчёт. Как мы видим – в отчёте много статей, подразделений счетов и т.д..

1.jpg

Естественно, этот отчёт можно сохранить в Excel. Для этого выбираем меню файл -> сохранить копию.

2.jpg

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


Всё. Данные успешно перенеслись в Excel.

Однако не всё так просто, как кажется на первый взгляд.

Давайте попробуем сформировать этот же отчёт за следующий месяц. Вот что получилось:

Как мы видим – и данных, и счетов в этом отчёте гораздо меньше, чем в предыдущем.

Дело в том, что в 1С (как впрочем, и практически в любую другую информационную систему) изначально «зашита» определённая логика построения отчёта.

В данном случае логика такова, что если за период операций не было – они не показываются.

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

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

Так что суть проблемы понятна – теперь давайте посмотрим, как её решать.

Для начала давайте посмотрим, какая структура данных наиболее удобна для Excel.

Удобнее всего настроить автоматическую обработку данных в Excel, когда в верхней строчке листа находится заголовок, а все строчки ниже занимают данные. Выглядит это примерно так:

Однако, если выгружать данные из отчётов 1С, структура данных для обработки в Excel совершенно не подходит.

Тогда давайте разберёмся – что такое отчёт в 1С? Отчёт – это данные, выбранные и сгруппированные программой по определенным признакам.

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

Теперь посмотрим – где можно получить неструктурированную информацию в 1С. В главном меню нужно выбрать операции à регистры бухгалтерии.

И выбрать регистр журнал операций бухгалтерского учёта.

Система выведет регистр операций бухгалтерского учёта, который можно сохранить в Excel.

Для этого щёлкните правой клавишей мыши в любом месте списка и выберите пункт «Вывести список».

На экране появится окно, в котором можно выбрать  нужные элементы. Давайте пока оставим все элементы и нажмём кнопку «ОК».

На экране появится таблица, которую можно сохранить в Excel.

Для этого в меню «Файл» выбираем «Сохранить копию», указываем тип файла «Лист Excel» и нажимаем кнопку «Сохранить».

Получаем вот такую таблицу в Excel:

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

Однако полученная таблица всё равно непригодна для автоматической обработки в Excel. Данные в полученной таблице содержатся в одном столбце, но в каждой третьей строке.

Такая структура очень неудобна для автоматической обработки. И сейчас наша задача состоит в том, чтобы «собрать» данные в разные столбцы, но в одной строке.

Чтобы сделать это, нам понадобится знание двух функций – «Строка» и «Индекс».

Начнём с функции «Строка». Эта функция возвращает номер строки, в которой она находится. Синтаксис функции простейший. Формула, которая возвращает номер текущей строки, будет выглядеть так: «=СТРОКА()».

Следующая рассматриваемая функция – ИНДЕКС. Эта функция умеет получать данные из массива по координатам.

Синтаксис функции выглядит так: =ИНДЕКС(Массив;НомерСтроки;НомерСтолбца)

Массив – это диапазон ячеек, из которого нужно получить данные.

Номер строки – порядковый номер нужной строки в массиве. Номер строки считается от начала массива, а не от первой строки листа.

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

Выглядит функция так:

 

Формула, приведённая на экране, выведет значение 440.

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

Однако для решения нашей задачи нам понадобится работа с одномерными вертикальными диапазонами – попросту говоря, со столбцами. В этом случае функция «ИНДЕКС» работает так – в качестве массива ей нужно указать столбец и номер строки, номер столбца в этом случае указывать не нужно.

Функция получит элемент с нужным номером из столбца. Выглядит это так:

Хорошо. Подготовительная работа выполнена. Следующий наш шаг – создать таблицу результатов.

Структура таблицы результатов должна быть удобна для обработки в Excel и должна содержать все нужные поля, но расположены они должны быть в одну строку.

Для этого создадим в книге новый лист и на нём расположим названия нужных нам полей в одну строку. В итоге у нас должен получиться такой лист:

Теперь приступим к переносу данных на этот лист. Для начала – зададим имена диапазонам на исходном листе.

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

Теперь для Excel столбец «А» исходного листа называется «Реквизиты документа». И это имя можно использовать в формулах в любом месте книги.

Последовательно присвоим имена всем столбцам на исходном листе: Столбец «В» - СчётДТ; Столбец «С» - СубконтоДТ; Столбец «D» - ДанныеДТ; Столбец «E» - СчётКТ; Столбец «F» - СубконтоКТ; Столбец «G» - ДанныеКТ; Столбец «H» - ДанныеПроводки.

Давайте посмотрим на лист результатов и постараемся понять, как можно получить данные из каждой третьей строки в одну строку.

Нам понадобятся два основных понятия – множитель и смещение.

Для начала рассмотрим множитель.

Если данные содержатся в каждой третьей строке – множитель будет равен трём. Соответственно, если бы данные содержались не в каждой третьей строке, а в каждой пятой – множитель был бы равен пяти.

Давайте возьмём для примера столбец «А» на листе результатов.

В ячейки столбца этого столбца нам надо получить значения из ячеек столбца «А» с исходного листа.

Во вторую строку листа результатов нужно получить значение из четвертой ячейки исходного листа, в третью строку результатов – из седьмой строки исходника, в четвёртую – из десятой и т.д..

И это должно делаться полностью автоматически.

Если вы помните – на исходном листе мы присвоили столбцу «А» имя «РеквизитыДокумента», его мы и будем использовать в наших формулах.

Теперь на листе результатов введём в ячейку А2 следующую формулу:

=ИНДЕКС(РеквизитыДокумента;СТРОКА()*3)

Давайте разберём, как работает эта формула в данной ячейке.

Функция СТРОКА(), которая входит в формулу, получает номер строки, в которой находится формула. В нашем случае – это 2 и умножает его на множитель 3. Итого получается 6.

А потом при помощи функции «Индекс» получает шестую строку из диапазона «РеквизитыДокумента».

Такая же формула, расположенная в третье строке, получит номер строки 3, умножит его на множитель 3 (итого 9) и получит девятую строку из диапазона «РеквизитыДокумента».

Таким образом, формула во второй ячейке получит значение «МебельСтройКомплект завод», и формула в третьей ячейке – тоже «МебельСтройКомплект завод».

Однако, хотя мы и получаем каждую третью строку – это совсем не то, что нам нужно. Нужные нам значения находятся в строках 4 и 7.

Для того, чтобы получить эти значения, зададим величину смещения. В данном случае смещение составит минус 2 строки. Это значит, что нужная строка находится на 2 строке выше, чем результат вычисления выражения СТРОКА()*Множитель.

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

Теперь функции получат правильные результаты:

И если протянуть формулы вниз – они будут последовательно получать данные из диапазона «РеквизитыДокумента» по строкам 4,7,10,13,16 и т.д..

Хорошо. Теперь займёмся вторым нужным нам параметром – Документ. Чтобы получить документы по строкам в ячейку В2 листа результатов введём формулу:

=ИНДЕКС(РеквизитыДокумента;СТРОКА()*3-1)

Эта формула находится во второй строке и функция СТРОКА() вернёт значение 2, потом формула умножит его на 3 (множитель) и вычтет из него 1 (смещение). В итоге получится 5. А функция ИНДЕКС получит пятый элемент из массива «РеквизитыДокумента».

А при протягивании формулы вниз – эта формула будет получать из массива «РеквизитыДокумента» строки 5,8,11,14,17 и т.д..

Теперь займёмся третьим столбцом – Организация. В ячейку С3 на листе результатов введём формулу: =ИНДЕКС(РеквизитыДокмента;СТРОКА()*3).

Эта формула получит шестой элемент из массива «Реквизиты документа», а при протягивании вниз будет получать 9,12,15,18 и т.д. элементы.

По аналогии настраиваем формулы для остальных столбцов.

Потом протягиваем формулы вниз – и данные, которые на исходном листе содержались в каждой третьей строке – на листе результатов растягиваются их в одну строку.

У нас получается структура данных, которая очень удобна для обработки в Excel. К этим данным можно применять автофильтры, обрабатывать их различными функциями (например – СУММЕСЛИ или ВПР), строить сводные таблицы, формировать запросы к этим данным и т.д..

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

.