LINEBURG


<< Пред. стр.

страница 60
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

• выполнить команду меню Правка • Специальная вставка, указать Значения.
Для удобства выполнения специальной вставки (без вызова меню) можно со­
здать макрос, запускаемый с помощью «горячих» клавиш после установки кур­
сора в место вставки на листе ЖХО.
Последовательность действий по созданию макроса.
• скопировать подготовленные проводки на листе ТО в буфер обмена с помо­
щью команды меню Правка • Копировать;
• перейти на лист ЖХО;
• выполнить команду меню Сервис • Макрос • Начать запись;
в диалоговом окне Запись макроса ввести имя 1 макроса, сочетание клавиш

для вызова макроса — Ctrl+q, указать сохранение макроса в текущей рабочей
книге;
• нажать кнопку ОК;

ВНИМАНИЕ
Если панель инструментов Остановить запись не выводится автоматически, после начала
записи макроса следует выполнить команду Вид • Панели инструментов и вывести ее на
экран.

• на панели инструментов Остановить запись нажать кнопку Относительная
ссылка;
• установить курсор в место вставки буфера обмена;
• выполнить команду меню Правка • Специальная вставка • Значения;
• на панели инструментов Остановить запись нажать кнопку Остановить запись.

ПРИМЕЧАНИЕ
При вставке проводок, подготовленных на основе типовой операции, происходит увели­
чение размерности блоков, если первоначальное число строк ие превышало 65 535. В про­
тивном случае блоки не раздвигаются. Происходит замещение формул в столбцах Е-Н
для вставленных строк.



1
Первым символом имени макроса должна быть буква, не допускаются пробелы или спе­
циальные символы.
498 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


После проверки работоспособности макроса выполнить команду меню Файл •
Закрыть рабочую книгу Бухучет.х1э с сохранением.

Критерии фильтрации проводок Ж Х О
Для автоматического формирования оборотов по счетам, субсчетам использу­
ются встроенные функции категории Работа с Базой данных, для которых за­
даются три аргумента:
• диапазон ячеек, образующих список (базу данных Microsoft Excel), или имя
блока базы данных. Блок ячеек включает имена полей и строки;
• имя или порядковый номер поля в структуре записи базы данных, для кото­
рого осуществляется вычисление. Нумерация начинается с первого поля, вы­
полняется слева направо;
• условия фильтрации записей, участвующих в вычислениях.
Для формирования дебетовых и кредитовых оборотов по счету, субсчету на лис­
те ОСВ используется встроенная функция БДСУММ. В качестве списка выступает
блок под именем БазаДанных, расположенный на листе ЖХО. Поле, по которому
выполняется расчет, называется Сумма, соответствует сумме бухгалтерской про­
водки. Условия должны обеспечивать фильтрацию проводок для определенного
счета, субсчета за указанный интервал времени, обычно за месяц.
Лист Критерии содержит специальным образом подготовленные условия фильт­
рации записей листа ЖХО для всех счетов, субсчетов за все периоды учета. Ус­
ловия для фильтрации записей за один учетный период определенного счета,
субсчета включает 2 строки (рис. 6.40):
• наименование полей списка (БД Microsoft Excel);
• значение счета, субсчета по дебету и кредиту;
• дата начала и дата конца учетного периода.


д Дата Дата К Кс
Дс
010 >=01.03.01 <01.04.01 010 01
01

Рис. 6.40. Условия для вычисления оборотов по счету, субсчету

Для фильтрации проводок но дебету счета, субсчета в качестве условия исполь­
зуется диапазон ячеек, включающий столбцы Д, Дс, Дата, Дата. Для фильтрации
проводок по кредиту счета, субсчета в качестве условия используется диапазон
ячеек, включающий столбцы Дата, Дата, К, Кс. Поле Дата используется дважды:
для указания нижней и верхней границы учетного периода.

ПРИМЕЧАНИЕ —
Имена полей диапазона условий в точности совпадают с именами списка (базы данных)
на листе ЖХО.
499
Компьютерная система бухгалтерского учета на базе Microsoft Excel


Для определенного учетного периода диапазоны условий фильтрации проводок
по всем счетам, субсчетам располагаются «вертикально», перед каждой строкой
условия находится строка с именами полей списка (базы данных).
Последовательность действий.
1. Открыть файл БУХГАЛТЕРСКИЙ y4ET.XLS с помощью команды меню Файл •
Открыть.
2. Вставить, если необходимо, новый лист с помощью команды меню Вставка •
Лист.
3. Переименовать новый лист с помощью команды меню Формат • Лист • Пере­
именовать, лист — Критерии.
4. Вставить блок План_счетов на лист Критерии:
О выделить блок План_счетов с помощью команды меню Правка • Перейти;
О скопировать блок в буфер обмена с помощью команды меню Правка • Ко­
пировать;
О установить курсор в ячейку А2 на листе Критерии;
О выполнить команду меню Правка • Вставка;
О установить курсор в ячейку Е2 на листе Критерии;
О выполнить команду меню Правка • Вставка;
О выделить столбцы С, D, G, Н;
О выполнить команду меню Правка • Очистить • Все;
О выделить строку 2;
О выполнить команду меню Правка • Очистить • Все.
5. Сформировать в строке 1 листа Критерии заголовки диапазона условий:
О выделить заголовки полей для диапазона условий на листе ЖХО;
О выполнить команду меню Правка • Копировать;
О перейти на лист Критерии;
О установить курсор в ячейку А1;
О выполнить команду меню Правка • Специальная вставка • Вставить значе­
ния;
О привести заголовки к нужному виду (рис. 6.40).
6. Записать ограничения на даты проводок для учетного периода:
О для нижней границы: >=01.01.01 (если учет начинается с 01.01.2001 и для
даты в ЖХО выбран формат типа 14.03.99);
О для верхней границы: <01.02.01 (если учет начинается с 01.01.2001 и для
даты в ЖХО выбран формат типа 14.03.99).

ВНИМАНИЕ
Вид ограничения по датам должен соответствовать формату даты на листе ЖХО.

7. Размножить заданные ограничения по дате методом автозаполнения ячеек:
500 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


О выделить ячейки C2-D2, содержащие ограничения по дате;
О установить курсор на маркер автозаполнения и протянуть выделенную
область при нажатой правой кнопке мыши для заполнения ячеек столб­
цов С и D.

Создание диапазона условий для первого
учетного периода
Для выполнения встроенной функции БДСУММ необходимо подготовить усло­
вия фильтрации для каждого счета, субсчета. Условие содержит имена полей
списка (базы данных Microsoft Excel) — ЖХО. Таким образом, перед каждой
строкой на листе Критерии, содержащей свет, субсчет, необходимо вставить на­
звания полей.
Для выполнения вставки заголовков полей создается макрос, который копирует
текущую строку заголовков и вставляет ее перед очередным счетом, субсчетом.
Последовательность действий по созданию макроса.
• установить курсор в строке заголовков в ячейку, содержащую имя столбца — Д;
• выполнить команду меню Сервис • Макрос • Начать запись.
в диалоговом окне Запись макроса ввести имя 1 макроса, сочетание клавиш

для вызова макроса — Ctrl+t, указать сохранение макроса в текущей рабочей
книге;
• нажать кнопку ОК;
• на панели инструментов Остановить запись нажать кнопку Относительная ссыл­
ка;
• выделить диапазон ячеек, содержащих имена полей заголовка текущей стро­
ки;
• выполнить команду меню Правка • Копировать;
• установить курсор на две строки ниже исходной в ячейку, содержащую но­
мер счета;
• выполнить команду меню Вставка • Скопированные ячейки, указать добавле­
ние диапазона со сдвигом вниз;
• нажать клавишу Esc для снятия выделения копируемого блока ячеек;
• установить курсор в ячейку строки заголовка, содержащую имя столбца Д;
• нажать кнопку Остановить запись;
• установить курсор в ячейку, содержащую имя столбца Д;
• нажать клавиши Ctrl+t для выполнения вставки строки заголовков;
• повторить предыдущее действие для всех остальных строк таблицы.




1
Первым символом имени макроса должна быть буква, не допускаются пробелы или спе­
циальные символы.
Компьютерная система бухгалтерского учета на базе Microsoft Excel 501


ПРИМЕЧАНИЕ
В результате этих действий на листе Критерии будут подготовлены диапазоны условий
для первого учетного периода.

Создание диапазона условий для следующих
учетных периодов
Исходный диапазон ячеек в столбцах A-F копируется в правую часть таблицы,
с соблюдением промежутка в один столбец. Таким образом, шаг смещения по
горизонтали составляет 7 столбцов вправо для каждого следующего учетного
периода. При этом необходимо изменять дату начала и окончания учетного пе­
риода.
Последовательность действий.
1. Выделить столбцы A-F.
2. Выполнить команду меню Правка • Копировать.
3. Подготовить диапазон условий для нового учетного периода:
О установить курсор в ячейку Н1;
О выполнить команду Правка • Вставить;
О выделить столбец J (нижняя граница даты);
О выполнить команду меню Правка • Заменить с указанием: что — .01. заме­
нить на — .02. и нажать кнопку Заменить все;
О выделить столбец К (верхняя граница даты);
О выполнить команду меню Правка • Заменить с указанием: что — .02. заме­
нить на — .03. и нажать кнопку Заменить все.

ПРИМЕЧАНИЕ
Действия данного пункта повторить для всех учетных периодов, сдвигая курсор в область
вставки для нового учетного периода.

4. Команда меню Сервис • Защита • Защитить лист.
5. Закрыть файл с сохранением — команда меню Файл • Закрыть.


Формирование оборотов по счету
Обороты по счету, субсчету за определенный учетный период вычисляются на
листе ОСВ на основании данных ЖХО с помощью встроенной функции БДСУММ,
которая использует условия фильтрации записей ЖХО, подготовленные на лис­
те Критерии.
Последовательность действий.
1. Открыть файл БУХГАЛТЕРСКИЙ УЧЕТ-XLS с помощью команды меню Файл •
Открыть.
2. Перейти на лист ОСВ.
502 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


Формирование дебетовых оборотов по счету, субсчету
Последовательность действий.
1. Установить курсор в первую ячейку блока Д01— ячейка G5.
2. Ввести формулу:
=БДСУММ(БазаДанных;7;СМЕЩ(Критерии!$А$2;$ВР5;Е$1;2;4))
где БазаДанных — блок на листе ЖХО; 7 — порядковый номер поля Сумма,
условия фильтрации формируются встроенной функцией СМЕЩ. В функции
СМЕЩ используются параметры:
О Критерии!$А$2 — начало области ячеек на листе Критерии; абсолютная
ссылка;
О $BF5 — смещение по строкам от начала указанной области на листе Крите­
рии, абсолютная ссылка по столбцу;
О Е$1 — смещение по столбцам от начала указанной области на листе Кри­
терии, абсолютная ссылка по строке;
О 2 — высота выделяемой области условий на листе Критерии (количество
строк);
О 4 — ширина выделяемой области условий на листе Критерии (количество
столбцов).
С помощью СМЕЩ выделяется область условий для фильтрации записей блока
БазаДанных по дебету каждого счета, субсчета.
Для тиражирования формулы дебетовых оборотов следует:
• выделить ячейку G5;
О установить курсор на маркер автозаполнения;
• нажать левую кнопку мыши и протянуть курсор по всем ячейкам блока Д01;
• выделить блок Д01;
• выполнить команду меню Правка • Копировать;
О выделить при нажатой клавише Ctrl блоки ячеек: Д02, ДОЗ, Д04, Д05, ...
• выполнить команду меню Правка • Вставить.
В результате будут созданы формулы для вычисления дебетового оборота каж­
дого счета, субсчета по всем учетным периодам.

Формирование кредитовых оборотов по счетам
Последовательность действий.
1. Установить курсор в первую ячейку блока К01- Н5.
2. Ввести формулу:
=БДСУММ(БазаДанных;7;СМЕЩ(Критерии!$С$2;$ВР5;Е$1;2;4))
где БазаДанных — блок на листе ЖХО; 7 — порядковый номер поля Сумма,
диапазон условий формируется встроенной функцией СМЕЩ. В функции
СМЕЩ используются параметры:
Компьютерная система бухгалтерского учета на базе Microsoft Excel 503


О Критерии!$С$2 — начало области ячеек на листе Критерии; абсолютная
ссылка;
О $BF5 — смещение по строкам от начала указанной области на листе Кри­
терии, абсолютная ссылка по столбцу;
О Е$1 — смещение по столбцам от начала указанной области на листе Кри­
терии, абсолютная ссылка по строке;
О 2 — высота выделяемой области условий на листе Критерии (количество
строк);
О 4 — ширина выделяемой области условий на листе Критерии (количество
столбцов).
С помощью СМЕЩ выделяется область условий для фильтрации записей блока
БазаДанных по кредиту каждого счета, субсчета.
Для тиражирования формулы кредитовых оборотов следует:
• выделить ячейку Н5;
• установить курсор на маркер автозаполнения;
• нажать левую кнопку мыши и протянуть курсор по всем ячейкам блока К01;
• выделить блок К01;
• выполнить команду меню Правка • Копировать;
• выделить при нажатой клавише Ctrl блоки ячеек: К02, КОЗ, К04, К05, ...
• выполнить команду меню Правка • Вставить.
В результате будут созданы формулы для вычисления кредитового оборота сче­
та, субсчета по каждому учетному периоду.
В итоговой строке ОСВ вводятся формулы для подсчета итоговых оборотов по
дебету и кредиту по всем счетам с помощью кнопки Автосуммирование. При
правильной работе должны выполняться соотношения:
• сумма дебетовых оборотов за период равна сумме кредитовых оборотов за
этот же период;
• сумма дебетовых сальдо на начало периода равна сумме кредитовых сальдо
на начало периода;
• закрыть файл с сохранением — команда меню Файл • Закрыть.

Анализ ЖХО
Для просмотра и анализа записей учетных регистров (ЖХО, ОСВ) могут приме­
няться стандартные информационные технологии работы со списками (базами
данных) Microsoft Excel:
• сортировка записей ЖХО в требуемой логической последовательности — ко­
манда меню Данные • Сортировка;
• фильтрация данных ЖХО (автофильтр, расширенный фильтр);
• получение промежуточных итогов по данным ЖХО для счетов, субсчетов по
дебету или кредиту — команда меню Данные • Итоги;
504 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


• формирование сводов — команда меню Данные • Сводная таблица на основа­
нии ЖХО;
• построение диаграмм динамики сальдо по счетам на основании ОСВ;
• прогнозирование значений сальдо по счетам на основании ОСВ и др.
Для выполнения сортировки строк листа ЖХО следует учитывать, что лист со­
держит обособленные блоки: Контировка, БазаДанных и Проверка. При сортиров­
ке не должны теряться связи ячеек внутри отдельных строк. Поэтому целесооб­
разно выделить все столбцы А-М и создать новый блок — ЖХО. Для выполнения
операции сортировки следует предварительно выделить блок ЖХО с помощью
команды меню Правка • Перейти.
При выполнении команды Данные • Сортировка следует указать идентифика­
цию полей по подписям (первой строке диапазона). Сортировка записей ЖХО
может выполняться по таким ключам, как:
• Дата по возрастанию — просмотр проводок определенного учетного периода
в хронологической последовательности;
О Д (счет по дебету), Дс (субсчет по дебету) по возрастанию, Дата по возраста­
нию — подборка всех проводок по каждому счету за все периоды учета;
• Д (счет по дебету), Дс (субсчет по дебету) по возрастанию, К (счет по креди­
ту) по возрастанию — подборка проводок с одинаковой корреспонденцией за
все периоды учета;
• Д (счет по дебету), Дс (субсчет по дебету) по возрастанию, Сумма по возрас­
танию — подборка проводок для одного счета, субсчета, упорядоченных по
возрастанию суммы операции и др.

ПРИМЕЧАНИЕ
При выполнении операции сортировки по возрастанию действует порядок, при котором
значение «пусто» предшествует другим значениям. Сортировка блоков большого размера
(например, 65 536 строк) с малым количеством заполненных записей связана с выводом
после сортировки пустых строк первыми. Поэтому работать с результатом сортировки по
возрастанию крайне неудобно, целесообразно для старшего ключа сортировки указы­
вать — по убыванию значений.

Фильтрация записей ЖХО в режиме автофильтра обеспечивает отбор записей по
любому сочетанию простых условий для отдельных полей:
• проводки, в которых используется определенный счет, субсчет по дебету (кре­
диту);
• все проводки за указанную дату;
• все проводки за интервал дат;
• проводки для определенной корреспонденции счетов по дебету и кредиту;
• проводки для указанного счета, субсчета по дебету (кредиту) за указанный
интервал дат;
• проводки, сумма операции которых находится в диапазоне;
Компьютерная система бухгалтерского учета на базе Microsoft Excel 505


• проводки, в которых не указан номер документа;
• первые 10 (любое число) проводок, у которых сумма операции наименьшая
(наибольшая);
• 10 % (любое число) проводок, у которых сумма операции наименьшая (наи­
большая), и т. д.
Более сложные условия фильтрации, а также создание копии отфильтрованных
записей ЖХО требуют подготовки диапазона условий (обычно на другом листе
рабочей книги).

Экспресс-итоги
Сальдо текущее, дебетовые и кредитовые обороты по каждому счету, субсчету
формируются автоматически на листе ОСВ. Этот регистр позволяет в динамике
отслеживать текущее состояние бухгалтерского учета. Но «история» возникно­
вения этих итогов не представлена в наглядном виде. Кроме того, нельзя под­
считать такие статистические итоги, как:
• количество проводок по дебету/кредиту счета, субсчета;
• итоги по субсчетам и итог по счету;
• определение максимальной и минимальной суммы операции по каждому
счету, субсчету и в целом по всем проводкам и др.
На листе ЖХО находится много формул, что затрудняет проведение анализа
проводок. Поэтому для целей анализа бухгалтерских данных можно скопиро­
вать соответствующие данные на другой лист:
• выполнить команду меню Вставка • Лист — новый лист для выполнения экс­
пресс-анализа ЖХО;
• выполнить команду меню Формат • Лист • Переименовать новый лист, лист —
Итоги;
• выделить блок с помощью команды меню Правка • Перейти — блок БазаДан-
ных;
• создать копию блока в буфере обмена с помощью команды меню Правка •
Копировать;
• выполнить команду меню Правка • Специальная вставка, указать Вставить зна­
чения (без формул).

ВНИМАНИЕ
Этот же прием применим и в том случае, когда блок содержит массив формул, которые не
допускают какого-либо перемещения ячеек, в том числе и сортировки строк.

Можно достаточно просто получать любые экспресс-итоги с помощью команды
Данные • Итоги. Результат итогов зависит от упорядочивания строк ЖХО. Един­
ственный недостаток этой технологии — статичность итогов, так как копия ав­
томатически не обновляется.
506 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


Вид итоговой таблицы зависит от варианта сортировки и выбранных парамет­
ров формирования итогов: выбор поля группирования, функции итога.
В табл. 6.14 приведены примеры экспресс-итогов по проводкам ЖХО. По одному
полю группирования можно получить несколько итогов с использованием раз­

<< Пред. стр.

страница 60
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

Copyright © Design by: Sunlight webdesign