LINEBURG
страница 59 |
ПРИМЕЧАНИЕ
В результате выполнения команды меню Правка • Вставить все блоки ДЗ, Д4, , КЗ, К4,
содержат одинаковые формулы
18 Внести изменение в формулы сальдо на начало каждого учетного периода,
начиная с периода 3
О выделить блок ячеек ДЗ с помощью команды меню Правка • Перейти,
О выполнить команду меню Правка • Заменить, указать замену 1 на 2 (для
текущего учетного периода), нажать кнопку Заменить все,
О выделить блок ячеек КЗ с помощью команды меню Правка • Перейти,
О выполнить команду меню Правка • Заменить, указать замену 1 на 2 (для
текущего учетного периода), нажать кнопку Заменить все
19 Повторить предыдущий пункт для всех последующих учетных периодов, за
меняя 1 на 3 для периода 4, 1 на 4 для периода 5, 1 на 6 для периода 7 и т п
20 Создание вспомогательного массива чисел для расчета дебетовых и кредито
вых оборотов по счетам
О в ячейку BF5 ввести значение 0,
О в ячейку BF6 ввести значение 2,
490 Глава 6 Специальные информационные технологии анализа данных в Microsoft Excel
О выделить ячейки BF5-BF6, установить курсор на маркер автозаполнения
и при нажатой левой кнопке мыши протянуть на высоту столбца (до ито
говой строки)
21 Закрыть файл с сохранением — команда меню Файл • Закрыть
ПРИМЕЧАНИЕ
Формулы для расчета дебетовых и кредитовых оборотов по счетам пока не вводятся
Журнал хозяйственных операций
ЖХО является основным учетным регистром для хранения бухгалтерских про
водок, формируемых на основании первичных учетных документов Минималь
но необходимая структура данных ЖХО — столбцы таблицы - Дата операции,
Счет дебет, Субсчет дебет, Счет кредит, Субсчет кредит, Код вида документа, №
документа, Сумма по операции, Комментарий.
Структура данных ЖХО (рис 6 36) может быть дополнена справочной информа
цией, обеспечивающей углубленный аналитический учет, например, счета ана
литического учета для дебетовой и кредитовой части проводки, код структурно
го подразделения — место совершения операции и др.
т Е
!к
F G LМ
|Н 1, IJ
д 1 Кс 1 № док-та 1 Сумма 1 Комментарии Проверка
| Дата Дс
Блок БазаДаиных Блок
Проверка
Рис. 6.36. Структура данных листа ЖХО
Структура данных ЖХО отвечав! всем требованиям, предъявляемым к спискам
(базе данных) Microsoft Excel К ЖХО могут применяться основные информаци
онные технологии обработки списков фильтрация записей по условиям отбора,
агрегирование первичной информации, консолидация различных списков ЖХО
и др
ЖХО может формироваться как в результате ручного •заполнения, так и путем
обмена информацией с другими программными системами, например с готовы
ми программами бухгалтерского учета Лист ЖХО позволяет сохранить до 65 535
записей, при интенсивности 12-15 проводок в день время «жизни» ЖХО более
15 лет
Для ввода проводок в ЖХО следует обеспечить контроль вводимой информации
При вводе данных используется справочник счетов, субсчетов, выполняется кон
троль полноты ввода основных данных по проводке ЖХО может формироваться
на основе типовых проводок, содержащих заданную корреспонденцию счетов и
алгоритм расчета суммы проводок
491
Компьютерная система бухгалтерского учета на базе Microsoft Excel
Последовательность действий
1 Открыть файл БУХГАЛТЕРСКИЙ УЧЕТ XLS с помощью команды меню Файл •
Открыть
2 Вставить, если необходимо, новый лист с помощью команды меню Вставка •
Лист
3 Переименовать новый лист с помощью команды меню Формат • Лист • Пере
именовать, лист — ЖХО
4 Создать именованный блок Контировка, содержащий все ячейки столбцов А и В
О выделить заголовки столбцов А и В,
О выполнить команду меню Вставка • Имя • Присвоить, ввести имя блока —
Контировка
ВНИМАНИЕ
Блок Контировка имеет предельные размеры строк — 65 535, предпа шачен для выбора
счета, субсчета по дебету и кредиту проводки из справочника План счетов Для уменьше
ния числа строк блока следует выбрать команду Вставка • Имя • Присвоить, выбра1ь име
нованный блок и в строке Формула изменить координаты блока Например, для блока
Контировка установлены границы =ЖХО'$А $В Путем корректировки форму гы диапазона
ячеек блока можно изменить размерность блока, например на 500 записей =ЖХО'$А1 $В501
5 Ввести имена столбцов
О установить курсор в ячейку А1,
О ввести имя столбца — Д Дс (3 пробела между буквами Д и Д),
ВНИМАНИЕ
Имена столбцов содержат фиксированное число символов, используются в дальнейшем
для автоматического формирования имен других столбцов Поэтому строго следует вы
держать синтаксис имен столбцов
О установить курсор в ячейку В1,
О ввести имя столбца — К Кс (3 пробела между буквами К и К)
6 Создать в ячейках столбцов А и В поля со списком для выбора счет, субсче
та для дебетовой и кредитовой части проводки
О выполнить команду меню Правка • Перейти, выбрать блок Контировка,
О выполнить команду меню Данные • Проверка, указать тип данных — Спи
сок, источник — блок ячеек Выбор
7 Изменить формат ячеек столбцов А-В с помощью команды меню Формат •
Ячейки, на вкладке Выравнивание установить Переносить по словам
ПРИМЕЧАНИЕ
Если не изменить формат ячеек столбцов А и В, длинные названия счетов будут мешать
работе в окне ЖХО
492 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel
8. Создать именованный блок БазаДанных:
О выделить названия столбцов D-K;
О выполнить команду меню Вставка • Имя • Присвоить, указать стандартное
имя блока — БазаДанных.
ВНИМАНИЕ
Блок БазаДанных имеет предельные размеры строк — 65 535. Блоки Контировка и База-
Данных должны иметь одинаковый размер. При необходимости следует изменить размер
ность блока БазаДанных.
9. Форматировать блок БазаДанных:
О выделить ячейки столбцов Е-1, выполнить команду меню Формат • Ячейки,
на вкладке Число указать Общий; на вкладке Вид — выбрать определенный
цвет заливки фона;
О выделить столбец D, выполнить команду меню Формат • Ячейки, на вклад
ке Число указать Дата, тип 14.03.99;
О выделить столбец J, выполнить команду меню Формат • Ячейки, на вклад
ке Число указать Денежный.
10. Ввести формулы в ячейки столбцов Е-Н для создания корреспонденции сче
тов проводки:
О выделить ячейки столбца Е (либо только входящие в блок БазаДанных со
кращенного размера); ввести формулу для дебетового счета проводки
=ЛЕВСИМВ(А1;3); нажать клавиши Ctrl+Enter для распространения форму
лы но всем ячейкам столбца Е;
О выделить ячейки столбца F (либо только входящие в блок БазаДанных со
кращенного размера); ввести формулу для дебетового субсчета =ПСТР(А1 ;5;2);
нажать клавиши Ctrl+Enter для распространения формулы по всем ячейкам
столбца F;
О выделить ячейки столбца G (либо только входящие в блок БазаДанных со
кращенного размера); ввести формулу для кредитового счета проводки
=ЛЕВСИМВ(В1;3); нажать клавиши Ctrl+Enter для распространения форму
лы по всем ячейкам столбца G;
О выделить ячейки столбца (либо только входящие в блок БазаДанных со
кращенного размера): ввести формулу для кредитового субсчета =ПСТР(В1;5;2);
нажать клавиши Ctrl+Enter для распространения формулы по всем ячей
кам столбца Н.
11. Ввести названия столбцов: Дата, № Документа, Сумма, Комментарий, Провер
ка (рис. 6.36).
12. Ввести условия проверки в ячейки столбца D для даты проводки:
О выделить ячейки столбца D;
О выполнить команду меню Данные • Проверка, указать тип данных — Дата,
значение между 01.01.2001 и 01.01.20002, например.
13. Ввести условия проверки в ячейки столбца J для суммы проводки:
О выделить ячейки столбца J;
Компьютерная система бухгалтерского учета на базе Microsoft Excel 493
О выполнить команду меню Данные • Проверка, указать тип данных — Дейст
вительное, значение — Больше или равно 0.
14. Создать именованный блок ячеек Проверка:
О выделить столбец М;
О выполнить команду меню Вставка • Имя • Присвоить, имя блока — Проверка.
ВНИМАНИЕ
Блок имеет предельные размеры — 65 535 строк. Блоки Контировка, БазаДанных и Провер
ка должны иметь одинаковый размер. При необходимости следует изменить размерность
блока Проверка.
15. Ввести формулы в ячейки столбца М для проверки заполнения полей про
водки:
О выделить ячейки столбца М (либо только входящие в блок Проверка, на
чиная с ячейки М2).
О ввести формулу:
=ECnM(HnM(EnyCTO(D:D);E:E="";F:F="";G:G="";H:H="";EnyCTO(J:J));
"Ошибка";"")
О нажать клавиши Ctrl+Enter для распространения формулы по всем ячейкам
столбца.
ПРИМЕЧАНИЕ
Если блоки (БазаДанных, Контировка, Проверка) имеют предельный размер по вертика
ли — 65 535 строк, операция добавления новых строк на листе ЖХО не выполняется. Опе
рация удаления сгрок таблицы — команда меню Правка • Удалить не приводит к уменьше
нию размерности блоков, так как при удалении строк блоки восстанавливают свои грани
цы, но в конец блоков будут добавлены строки, не содержащие формул. Если блоки имеют
меньшую размерность, то при добавлении новых записей следует ВРУЧНУЮ перекопиро
вать соответствующие формулы в столбцах Е-Н, М. Для таблиц, содержащих большое чис
ло формул, на момент реорганизации структуры следует отключить автоматический
пересчет формул — команда меню Сервис • Параметры, на вкладке Вычисления установить
флажок Вычисления вручную (нажать клавишу F9).
16. Защитить ячейки листа ЖХО от изменений и скрыть формулы:
О выделить ячейки столбцов А-В, Е-Н, М;
О выполнить команду меню Формат • Ячейки, на вкладке Защита установить
Защищаемая ячейка, Скрыть формулы;
О выделить ячейки столбцов 1-К;
О выполнить команду меню Формат • Ячейки, на вкладке Защита, снять флаж
ки Защищаемая ячейка, Скрыть формулы;
О выполнить команду меню Сервис • Защита • Защитить лист.
17. Закрыть файл с сохранением — команда меню Файл • Закрыть.
494 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel
Типовые бухгалтерские операции
Для большинства хозяйственных операций существует фиксированный набор
бухгалтерских проводок и определенный алгоритм разложения исходной суммы
операции на составляющие.
Например, типовая операция приобретения оплаченных материалов, при усло
вии, что налог на добавленную стоимость (НДС) включен в цену (рис. 6.37).
Счет Субсчет Формула разложения суммы
Субсчет Счет
К К
Д Д
060 Хх =С*100/(100+НДС)
010 Хх
019 060 Хх
Хх =С*НДС/(100+НДС)
051
060 Хх =С
Рис. 6.37. Типовая операция покупки оплаченных товарно-материальных
ценностей. С — сумма по операции, НДС — ставка налога
на добавленную стоимость
Например, типовая операция покупки и оплаты основных средств, передачи их
в эксплуатацию (рис. 6.38).
Счет Субсчет Счет Субсчет Формула разложения суммы
К К
Д Д
008 Хх 060 =С*100/(100+НДС)
XX
019 Хх 060 =С*НДС/(100+НДС)
XX
=с
060 Хх 051
008 Хх =С*100/(НДС+100)
001 Хх
Рис. 6.38. Типовая операция покупки приобретения основных средств:
С — стоимость основных средств, НДС — налог
на добавленную стоимость, включен в цену
Часто повторяющиеся хозяйственные операции объявляются типовыми. Для
удобства подготовки бухгалтерских проводок и автоматизации расчета сумм по
проводкам создаются шаблоны типовых операций.
Создание листа Типовые операции
Типовые операции готовятся на отдельном листе — Типовые операции. Необхо
димо обеспечить выбор счета, субсчета по дебету и кредиту проводки из плана
счетов; настройку значений параметров типовой операции.
Типовая операция состоит из строк двух типов (рис. 6.39):
• параметры операции (для каждого параметра выделена отдельная строка);
• типовые проводки операции.
495
Компьютерная система бухгалтерского учета на базе Microsoft Excel
:
_Р .6,_JULL —LL *L˜, _ ! , _ | И j f
-< А Й С D
И
№ № док
•| Операции п/п Дата Д Дс к Кс та Cyuua ' Коииентарий
˜ | Д Дс
1 К Кс
Ш Ц оплата 1 !Сумма оплаты
>2' - •
- ' '
хг 2 28%|нДС
Т^Цойлат»
ТМЦ оплата 3 010 01 060 00 0 Сумма ТМЦ
4 рИ«Сь»0ШФР*
ТМЦ оплата 4 019 03 060 00 О'НДС на ТМЦ
_?|04йаэна.адоор*
5 060 00 051 00 О'Сумма оплаты
ej<m«p*ost oop* ТМЦюшга
ОСтхупка 1 |Сумма оплаты
Л| 2
ОСтщта да|ндс
ч^ ' v
3 008 05 060 00 0 Сумма ОС
ООвщпт
ТрЭ№Г1|мШаФР*
0 0 шкагжа 4 060 00 060 00 0.Сумма НДС
кшйда^оеошл* 5 00 00 0 Оплата ОС
060 051
ОС тщгт
«ра Ша е О^ ш шГ^
в и абОЗ ^
00алв»к* Б 008 05 О'Ввод В ЭКСЛЛ ОС
001 01
«в О
Рис. 6.39. Структура данных листа ТО
Строки одной типовой операции последовательно нумеруются, имеют общее
название, которое указывается в поле Операция. Строки параметров содержат
название и значение параметров, значение параметров указывается в поле Сум
ма, название параметра — в поле Комментарий.
Последовательность работ по созданию листа Типовые операции:
1. Открыть файл БУХГАЛТЕРСКИЙ Y4ET.XLS с помощью команды меню Файл •
Открыть.
2. Вставить, если необходимо, новый лист с помощью команды меню Вставка •
Лист.
3. Переименовать новый лист с помощью команды меню Формат • Лист • Пере
именовать, лист — ТО.
4. Ввести имена столбцов (имена содержат фиксированное число символов, ис
пользуются для автоматического формирования имен других столбцов);
О в ячейку А1 ввести имя столбца: Д Дс (3 пробела между буквами Д и Д);
О в ячейку В1 ввести имя столбца : К Кс (3 пробела между буквами К и К).
5. Создать поля со списком для выбора счета, субсчета дебетовой и кредитовой
части проводки:
О выделить столбцы А и В;
О выполнить команду меню Данные • Проверка, указать тин данных — Спи
сок, источник — блок Выбор.
6. Ввести название столбцов: Операция, № п/п, Дата, Сумма, Комментарий, Дата,
№ Документа, Сумма, Комментарий (см. рис 6 39).
7. Задать форматы ячеек столбцов:
О выделить ячейки столбцов А-В; выполнить команду меню Формат • Ячей
ки, на вкладке Выравнивание установить Переносить по словам;
О выделить ячейки столбца F; выполнить команду меню Формат • Ячейки,
на вкладке Число указать Дата, тип 14.03.99.
О выделить ячейки столбца L, выполнить команду меню Формат • Ячейки,
на вкладке Число указать Денежный
496 Глава 6 Специальные информационные технологии анализа данных в Microsoft Excel
8. Ввод формул в ячейки первой строки таблицы для формирования заголов
ков столбцов:
О формула выделения дебетового счета — ячейка G1: =ПЕВСИМВ(А1 ;3);
О формула выделения дебетового субсчета — ячейка Н1- =ПСТР(А1;5;2);
О формула выделения кредитового счета — ячейка И: =ЛЕВСИМВ(В1; 3);
О формула выделения кредитового субсчета — ячейка Л : =ПСТР(В1; 5 ;2).
9. Ввести условия проверки в ячейки столбца F для даты проводки:
О выделить ячейки столбца F;
О выполнить команду меню Данные • Проверка, указать тип данных — Дата,
значение между 01.01.2001 и 01.01.20002, например.
10. Для строк параметров типовой операции заполнить:
О название типовой операции — в поле Операция;
О порядковый номер параметра — в поле № п/п;
О значение параметра — в поле Сумма;
О произвольный текст — в поле Комментарий.
ПРИМЕЧАНИЕ
При вводе параметров и шаблонов бухгалтерских проводок одной и той же типовой опе
рации название операции копируется из предыдущих строк, № п/п заполняется с исполь
зованием автозапотнения
11. Для строк шаблонов бухгалтерских проводок заполнить:
О после выбора в столбцах А и В корреспонденции счетов автоматически за
полняются поля. Д, Дс. К, Кс;
О названия операции — в поле Операция (путем копирование значения из
предыдущей записи типовой операции или из строки параметра типовой
операции);
О номера по порядку — в поле № п/п (режим автозаполнения);
О формула для вычисления суммы проводки — в поле Сумма (формула мо
жет содержать ссылки на параметры типовой операции);
О комментарий — в поле Комментарий.
Лист Типовые операции может содержать любое число типовых операций. Для
новой типовой операции необходимо обеспечить формулы в ячейках столбцов
G-J, автозаполнение названия операции и номера строки.
Строки типовых операций можно упорядочить с помощью команды меню Дан
ные • Сортировка, ключи сортировки - Операция и № п/п.
Подготовка бухгалтерских проводок на основе
типовой операции
Для подготовки бухгалтерских проводок на основе типовой операции следует:
Компьютерная система бухгалтерского учета на базе Microsoft Excel 497
• отфильтровать записи списка типовых операций по названию операции —
команда меню Данные • Фильтр • Автофильтр, установить фильтр по полю Опе
рация;
• заполнить недостающие исходные данные шаблонов операции и значения
параметров (дата, номер документа, комментарий);
• выделить блок ячеек, переносимых в ЖХО;
• выполнить команду меню Правка • Копировать;
• перейти на лист ЖХО, установить курсор в место вставки строк;
страница 59 |
Copyright © Design by: Sunlight webdesign