Дебет-Кредит
Украинский бухгалтерский еженедельник
#49'2001: Практичная бухгалтерия - Автоматически

Автоматизации бухучёта: Excel

Урок 20
Регистры бухгалтерского учета на малом предприятии

Владимир ЛАВРЕНОВ


Предыдущие уроки "Автоматизации бухучёта: Excel"

Думаю, вы убедились, что Журнал операций, выполненный с помощью Excel, вполне может служить базой для ведения аналитического учета на предприятии (см. предыдущий урок в "ДК" №46/2001).

Используя специальный модуль, вы легко получите сгруппированные данные для заполнения многих журналов-регистров. Однако такой метод обладает одним недостатком: данные всегда будут группироваться в рамках рабочего Журнала операций, что в некоторых случаях не совсем удобно. Excel предоставляет возможность максимально эффективно использовать данные, которые вы вносите в Журнал операций, для их всестороннего анализа. Для этого понадобится Сводная таблица (далее - СТ). Ее возможности ограничены только вашим воображением и данными, которыми вы оперируете.

Сводная таблица - универсальный регистр бухгалтерского учета

Сводная таблица используется в случаях, когда необходимо сравнить связанные итоги, особенно если список анализируемых фактов довольно велик. Отчеты сводных таблиц используются в Microsoft Excel для автоматического выполнения сортировки, вычисления промежуточных и общих итогов с самой разнообразной комбинацией сравниваемых данных. На рисунке 1 показан общий вид отчета сводной таблицы, построенной с использованием данных Журнала операций.

Рисунок 1

Вы видите, что в приведенном примере можно легко проследить корреспонденции счетов по операциям, проведенным в рамках договора №3 (см. ячейку В1, рис. 1), а также обороты по дебету и кредиту этих счетов и их сальдо. Вы также можете изменять представление данных для просмотра других взаимосвязей, дополнительных подробностей или вычисления итогов.

Создание Сводной таблицы

Основой для создания СТ может служить любой список, суммирующий группы данных. Мы же используем Журнал операций, предварительно несколько изменив его. В бухгалтерском учете удобно обобщать и анализировать данные, связанные принадлежностью к одной операции, одному договору и т. д. Поэтому для создания такого "связующего элемента" добавьте в свой Журнал еще одну графу. Для вставки дополнительной графы "№ документа" выделите в таблице столбец "Сумма операции" (рис. 2) и в контекстном меню выберите команду "Добавить ячейки со сдвигом вправо". Если у вас был установлен автофильтр, снимите его перед корректировкой таблицы.

Рисунок 2

Внимание! Чтобы не нарушить работу модуля анализа счета и ссылочную целостность введенных имен, выделите не весь столбец, а только ту его часть, которая относится к таблице. Все дополнительные графы, вставляемые в Журнал, должны располагаться перед графой "Сумма операции".

В новую графу вы будете записывать номер документа или договора, к которому относится та или иная проводка. Для удобства рекомендую на отдельном листе вести список таких документов (рис. 2). Кстати, дополнить Журнал операций можно также и графой, где будет отмечаться принадлежность проводки к налоговому учету. Тогда в Сводной таблице вы легко сгруппируете и проанализируете правильность ведения налогового учета.

Создадим Сводную таблицу: шаг за шагом.

Шаг первый. В меню "Данные" выберите строку "Сводная таблица". Появится окно Мастера сводных таблиц (рис. 3). На этом этапе вы можете задать любой источник данных, на основе которых будет создаваться СТ. В нашем примере необходимо выбрать вариант "В списке или базе данных MSExcel". Если вы захотите использовать несмежные графы или расположенные на других листах, выберите переключатель нескольких диапазонах консолидации". Теперь можно продолжить, нажав на кнопку "Далее".

Рисунок 3

Шаг второй. Выбираем диапазон исходных данных (также см. рис. 3). Диапазон можно указать, введя в поле адреса ячеек. Однако удобнее воспользоваться кнопкой и перейти в Журнал операций. Выделите в таблице Журнала, например, ячейки С11:К113 (отмените объединение ячеек в "шапке" таблицы, если таковые имеются, см. рис. 2).

Внимание! Первая строка выбираемого диапазона обязательно должна содержать названия граф.

Размер выбранного в соответствующем поле Мастера СТ диапазона должен быть достаточно большим, чтобы обеспечить запись проводок в Журнале, например, в течение квартала, полугода или года (в зависимости от количества осуществляемых на предприятии операций). Тогда это позволит поддерживать СТ актуальной в течение всего этого периода. Учитывайте при этом, что размеры диапазонов ячеек, которым присвоены на этом листе имена (ДатаОпер, ДебетЖ, КредитЖ, ВсегоЖ), тоже должны быть соответствующими. После выбора диапазона жмите на кнопку "Далее".

Шаг третий. Так как размеры СТ будут меняться в зависимости от сравниваемых данных и могут достигать внушительных размеров, желательно разместить СТ на новом листе. Установите переключатель "Новый лист". А теперь - главный этап в создании СТ. Щелкните кнопкой "Макетѕ", чтобы, собственно, и открыть конструктор СТ. Для создания полноценной Сводной таблицы, которая обеспечит все возможные комбинации записанных в Журнал данных, достаточно расположить кнопки полей, как показано на рисунке 4.

Рисунок 4

Главное - поместите кнопку "Дебет" на поле "Строка", кнопку "Кредит" - на поле "Столбец", а "Сумма операции" - на поле "Данные" (содержит итоговые данные). Остальные кнопки можно смело перетаскивать на поле "Страница", хотя использовать все кнопки для построения СТ не обязательно (это можно сделать непосредственно в созданной СТ). В процессе работы с СТ вы сможете расставить кнопки иначе, чтобы получить те же данные, но в другом разрезе.

В этом же окне (рис. 4), нажав кнопку "Параметрыѕ", можно установить определенные параметры для СТ. В общем случае положитесь на установки по умолчанию, однако проверьте в этом окне три поля:

этот флажок должен быть установлен, чтобы при открытии файла СТ обновлялась автоматически.

установка этого флажка даст возможность просматривать более подробные данные области СТ (при двойном щелчке на ячейке).

установите флажок и оставьте правое поле пустым. В противном случае в ячейках поля "Данные" с нулевыми значениями будет записано "0,00".

На этом создание сводной таблицы закончено и можно нажимать кнопку "Готово".

После этого в книге Баланс (если именно в ней находится Журнал операций) появится новый лист. Присвойте ему имя, например "Шахматка", и откройте его.

Как работать со Сводной таблицей

И, действительно, созданная вами Сводная таблица очень похожа на известную всем Шахматную ведомость, однако возможности ее намного больше по сравнению с любым бумажным или электронным аналогом (рис. 5). Каждое поле СТ (кроме поля "Данные") имеет раскрывающийся список, в котором можно выбрать или убрать один или несколько показателей.

Рисунок 5

На рисунке 5 показано, как можно скрыть кредитовые обороты по счетам, использованным в Журнале операций. Подбирая определенные комбинации дебетов и кредитов счетов, можно построить необходимый регистр бухгалтерского учета. Например, оставьте в поле "Кредит" только счета класса 4 и получите регистр - Журнал №7.

Видоизменять сводную таблицу и тем самым получать требуемый способ обработки данных можно различными способами. Наиболее применимый - перетаскивание "затененной" кнопки в нужные строки и столбцы Сводной таблицы.

Допустим, необходимо проверить выполнение договора №3, с детализацией по дням относящихся к нему проводок.

Для этого выберите в списке, раскрывающемся из ячейки В1, нужную строку ("3"). Затем перетащите кнопку "Дата операции" в зону кнопки "Дебет", так чтобы за ней появился знак "двутавра" (его изображение см. на рис. 6), и отпустите ее. Сводная таблица примет вид, показанный на рисунке 6. А теперь щелкните дважды на ячейке, расположенной в поле "Данные" (например, Е9), и откроется строка Журнала операций, детализирующая корреспонденцию Д-т 205 - К-т 631 по данному договору.

Рисунок 6

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

Например, можно видоизменить Сводную таблицу из стандартного представления "дебет-кредит" в "кредит-дебет". Такое представление используется для построения регистра-ведомости Журнала №1 "III. С кредита счета 31 "Счета в банках" в дебет счетов". Получить таблицу данных для этого регистра очень просто. В сводной таблице поменяйте местами кнопки "дебет" и "кредит", а в разворачиваемом списке (ячейка А6, рис. 7) оставьте выделенным только счет 31.

Рисунок 7

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


Следующий урок

© 2001
"Дебет-Кредит"
Редакция: debet-kredit@gc.kiev.ua