Формирование оборотной ведомости средствами MS Excel 2 страница

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

Внимание! Для выполнения работ настоящего занятия необходимо создать на сервере в сетевой папке файл регистрации документов установленной формы и провести регистрацию документов с разных машин компьютерного класса.

Под регистрацией документа понимается процесс создания отдельной записи (строки) в файле электронной таблицы Excel, с указанием всех необходимых реквизитов документа. Указанные реквизиты документа в дальнейшем с ростом количества зарегистрированных документов становятся критериями поиска документа в информационной базе. Поэтому заполнение всех реквизитов строго обязательно.

Пример регистрационной таблицы на базе MS Excel:

Дата	№ док-та	Вид документа	Содержание	Отдел	Исполнитель	Заказчик	Ссылка
14.03.2001	1-О	Приказ	Учетная политика на 2001 г.	Администрация	Иванов И.И.	Федорова В.К.	c:\Мои документы\Приказы\ Общие\ Приказ1.doc
15.03.2001	1-К	Приказ	Прием на работу Сидорова А.К.	Отдел кадров	Иванов И.И.	Каиров А.К.	c:\Мои документы\Приказы\ Кадровые\ Приказ1.doc
…	…	…	…	…	…	…	…

Графа Ссылка в данной таблице формируется специальным способом: Вст а вка ® Гип е рссылка ® «5 последние файлы» (или Alt + 5) ® и выбрать только что созданный Вами файл из списка. Если интересующий Вас файл отсутствует в списке, необходимо нажать кнопку «Файл…»и выбрать необходимый файл.

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

Таким образом, после регистрации документов в книге Excel документы, созданные в сети, становятся доступными через графу «гиперссылка» для любого пользователя, которому открыт доступ к файлу регистрации документов. Такое использование технологии позволяет вне зависимости от организации структуры каталогов на отдельных машинах, а также вне зависимости от места сохранения документа получить доступ к нему через единую информационную базу. Пользователь, не имея прямого доступа к дискам, папкам и файлам другого компьютера сети, тем не менее, может пользоваться общедоступными ресурсами. При этом не требуется никакой перенастройки параметров сети – любой пользователь, имеющий доступ и файлу (базе данных) регистрации документов получает доступ к распределенной по различным компьютерам сети информационной базе документов, что значительно повышает эффективность совместной работы. Далее необходимо создать возможность быстрого доступа к любому документу – создать возможности поиска. Необходимо определить автофильтр по всем графам документа, что позволит быстро найти любой документ при помощи реализованного в MS Excel механизма фильтрации (отбора) данных по определенным критериям:

- выделить строку заголовка;

- Д анные ® Ф ильтр ® А втофильтр.

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

Основным при выполнении лабораторной работы является отработка простейших навыков обработки документов в сети на базе гипертекстовой технологии.

è	Порядок выполнения работы

1. Изучение теоретического материала.

2. Выполнение вариантов заданий с помощью рассмотренных инструментов, средств, приемов и технологий

3. Составление отчета о проделанной работе. Отчет должен содержать следующие разделы:

- наименование работы;

- цель работы;

- пошаговое последовательное описание процесса выполнения варианта задания по видам выполняемых действий.

4. Результат выполнения варианта задания должен быть сохранен под именем ФИО_Работа№_Вариант№ (например, «ИвановНН_Работа9 _Вариант1.xls») на жесткий диск в папку «Мои документы\ИТ в экономике» и на дискету – в двух копиях (две копии одной и той же информации в разных папках на дискете).

5. Представление результатов выполнения работы (отчета и файлов на дискете) для проверки преподавателю.

6. Защита выполненной работы: ответ на контрольные вопросы к теоретическому материалу занятия и ответ на замечания преподавателя по выполненной работе.

7. Оценка преподавателем выполненной работы.

s	Контрольные вопросы

1. Понятие гиперссылки.

2. Как возможно использовать гиперссылки для более быстрого доступа к различным документам?

3. Зачем необходимо регистрировать документы?

4. Опишите содержание формы регистрации документов в книге Excel?

5. Как произвести вставку гиперссылки в ячейку Excel? Что такое текст гиперссылки?

6. Опишите быстрое создание гиперссылки на последний редактируемый документ.

7. Какие стандартные средства необходимо использовать в дальнейшем для поиска необходимого документа в форме регистрации.

8. Опишите порядок выполнения работы. Как должна быть оформлена работа? Как необходимо представлять результаты проделанной работы?

Ä	Варианты заданий

Вариант 1

20 - 30 мин.

ФОРМА РЕГИСТРАЦИИ

Дата	№ док-та	Вид документа	Содержание	Отдел	Исполнитель, Ф.И.О.	Заказчик, Ф.И.О.	Гиперссылка

Создать форму регистрации в сетевой папке r:\Student\Work под именем «Регистрация документов.xls». Провести регистрацию произвольных документов в регистрационной форме таким образом, чтобы было возможно быстро их вызвать через гиперссылки. Продемонстрировать умение пользоваться автофильтром для поиска документов. Показать как производится вызов документов с другого компьютера локальной вычислительной сети.

Оценивается степень владения технологией.

Вариант 2

20 - 30 мин.

ФОРМА РЕГИСТРАЦИИ

Дата	Предмет	Номер лабораторной работы	Группа	Ф.И.О. студента	Гиперссылка на файл с выполненной работой

Создать форму регистрации в сетевой папке r:\Student\Work под именем «Регистрация выполнения лабораторных работ.xls».

Провести регистрацию файлов с отчетами по выполнению вариантов заданий (произвольными файлами) в регистрационной форме.

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

Оценивается степень владения технологией.

Занятие №10

Решение задач бухгалтерского учета средствами Excel

Цель работы

Рассмотреть способы автоматизации учетных работ и формирование отчетности средствами Excel. Закрепить на практике навыки применения базовых информационных технологий в такой области как бухгалтерский учет.

&	Теоретический материал

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

Бухгалтерия, как подразделение, существует в каждом предприятии. Основная задача бухгалтерии – ведение учета, контроль за движением товарно-материальных и денежных потоков, ведение взаиморасчетов с поставщиками и покупателями, начисление налогов и составление и сдача регламентированной отчетности о деятельности предприятия в установленные сроки в соответствующие контролирующие органы.

Совокупность средств, подходов, технических приемов в сочетании с нормативно-правовой базой образуют технологию бухгалтерского учета.

Развитие информационных технологий не обошло стороной область бухгалтерского учета. Существует большое количество бухгалтерских программ от универсальных – до ориентированных на определенную группу предприятий и определенный участок учета. Среди широко известных можно перечислить программные продукты фирмы 1С (1С Бухгалтерия 7.7., 1С Торговля и склад 7.7., 1С Зарплата и кадры, 1С Производство + Услуги + Бухгалтерия, 1С Предприятие 7.7.), фирмы Интеллект-Сервис (БЕСТ, БЕСТ-ОФИС, БЕСТ-ПРО) и т.п.

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

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

Среди основных операций выполняемых у бухгалтерии следует выделить:

1. Организация регистрации хозяйственных операций.

2. Получение карточки счета.

3. Получение шахматной ведомости по счетам.

4. Получение оборотно-сальдовой ведомости по счетам.

5. Формирование главной книги.

6. Получение баланса и других регламентированных отчетов.

Выполнение данных операций традиционным способом (т.е. с помощью ручки, бумаги и калькулятора) является довольно трудоемким делом особенно при большом количестве хозяйственных операций. При изменении исходных данных в этом случае необходимо переделывать соответствующие сводные регистры и отчетность. Если взять среднее по размерам предприятие, то это огромный объем вычислительной работы. Об эффективности труда в данном случае говорить не приходится.

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

Технология сводных таблиц применяется для получения сводной информации по группам операций, получения шахматного баланса и других задач. Использование формул массива позволяет решить задачу формирования оборотно-сальдовых ведомостей, формирования необходимой отчетности.

Любой учет начинается с регистрации – регистрации операций. Регистрация хозяйственных операций в бухгалтерском учете производится в журнале хозяйственных операций – форме следующего вида:

ЖУРНАЛ ХОЗЯЙСТВЕННЫХ ОПЕРАЦИЙ

№ операции	Дата операции	Дебет (к получеию)	Кредит (к оплате)	Содержание операции	Сумма
…	…	…	…	…	…
…	…	…	…	…	…

В графе «Номер операции» отражается порядковый номер проводимой операции с начала отчетного периода (месяца, квартала, года).

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

В графах «Дебет» и «Кредит», предназначенных для реализации принципа двойной записи, необходимо указать корреспондирующие счета, образующие проводку хозяйственной операции. Например, при поступлении денежных средств с расчетного счета в кассу в графу «Дебет» необходимо будет ввести цифру 50, соответствующую счету 50 «Касса» бухгалтерского учета, а в графу «Кредит» - ввести цифру 51 (счет 51 «Расчетный счет).

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

Для регистрации хозяйственных операций c помощью базовых технологий Excel создается форма, предназначенная для ввода исходной информации по операциям:

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

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

После установки автофильтра на шапке таблицы журнала регистрации будет возможно производить отбор по определенным критериям. Например, выбрать все операции по дебету счета 50 (поступление денежных средств в кассу).

Подробнее механизм отбора данных с помощью автофильтра и формирования итогов рассмотрен в Задании №1 «Базовые технологии Excel».

Журнал-ордер (ведомость) счета представляет собой выборку дебетовых операций по счету с кредита других счетов:

Журнал-ордер счета создается с помощью технологии сводных таблиц (Занятие №5) и позволяет получать журнал-ордер по любому счету.

Шахматная ведомость предназначена для отражения сумм сводных проводок за определенных период (создается с помощью сводных таблиц).

Оборотно-сальдовая ведомость по счетам за определенный период представляет собой расчетную форму, в которой отражаются обороты по счетам (дебет и кредит), а также входящие и исходящие остатки по счетам, за определенный период времени. Форма создается с помощью технологии работы с формулами массивов (см. Занятие №8).

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

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

1. Переместить курсор на одну из ячеек столбца «Дата».

1. Д анные ® Г руппа и структура ® Г руппировать...

3. Выбрать тип группировки – «Кварталы, Годы».

После выбора способа группировки по времени необходимо нажать Ok.

Результат представлен на рисунке ниже:

Рис. Расчетная форма «Главная книга».

è	Порядок выполнения работы

1. Изучение теоретического материала.

2. Выполнение вариантов заданий с помощью рассмотренных инструментов, средств, приемов и технологий

3. Составление отчета о проделанной работе. Отчет должен содержать следующие разделы:

- наименование работы;

- цель работы;

- пошаговое последовательное описание процесса выполнения варианта задания по видам выполняемых действий.

4. Результат выполнения варианта задания должен быть сохранен под именем ФИО_Работа№_Вариант№ (например, «ИвановНН_Работа1 _Вариант1.xls») на жесткий диск в папку «Мои документы\ИТ в экономике» и на дискету – в двух копиях (две копии одной и той же информации в разных папках на дискете).

5. Представление результатов выполнения работы (отчета и файлов на дискете) для проверки преподавателю.

6. Защита выполненной работы: ответ на контрольные вопросы к теоретическому материалу занятия и ответ на замечания преподавателя по выполненной работе.

7. Оценка преподавателем выполненной работы.

s	Контрольные вопросы

1. Описать порядок формирования оборотно-сальдовой ведомости.

2. Описать порядок формирования электронной формы «Карточкасчета».

3. Описать порядок формирования электронной формы «Шахматный баланс» и «Главная книга».

4. Каким образом производится группировка данных по учетным периодам в сводных таблицах?

5. Опишите порядок выполнения работы. Как должна быть оформлена работа? Как необходимо представлять результаты проделанной работы?

Ä	Варианты заданий

Вариант 1

40 - 120 мин.

Используя технологии Excel на основе исходных данных составить электронные формы с автоматическим формировании сводной информации:

1. Форма «Карточка счета за период».

2. Форма «Шахматный баланс по счетам за период».

3. Форма «Оборотно-сальдовая ведомость по счетам за период».

4. Форма «Главная книга».

Исходные данные:

Журнал хозяйственных операций ОАО «ОРИОН»

№ п/п	Дата	Корреспонденция счетов	Сумма, тыс. р.	Содержание операций
Дебет	Кредит
1.	5.10			2 000	Учр. взнос осн. средств
2.	5.10			4 000	Учр. взн. в денежной форме
3.	7.10			3 000	Предоплата товара
4.	9.10			3 000	Поступление товара
5.	11.10			3 000	Отгрузка товара
6.	13.10			4 500	Реализация товара
7.	15.10			3 000	Получение кредита
8.	17.10			6 000	Предоплата товара
9.	19.10			6 000	Поступление товара
10.	21.10			6 000	Отгрузка товара
11.	23.10			9 000	Реализация товара
12.	24.10			3 000	Возврат кредита
13.	24.10				Выплата % за кредит
14.	25.10			4 350	Финансовый результат – расчет
15.	4.11			9 000	Учр. взнос осн. средств
16.	5.11			14 000	Учр. взн. в денежной форме
17.	7.11			3 000	Предоплата товара
18.	8.11			3 000	Поступление товара
19.	10.11			3 000	Отгрузка товара
20.	11.11			4 500	Реализация товара
21.	12.11			3 000	Получение кредита
22.	17.11			6 000	Предоплата товара
23.	18.11			6 000	Поступление товара
24.	20.11			6 000	Отгрузка товара
25.	25.11			9 000	Реализация товара
26.	27.11			3 000	Возврат кредита
27.	27.11				Выплата % за кредит

Вариант 2

40 - 120 мин.

Используя технологии Excel на основе исходных данных составить электронные формы с автоматическим формированием сводной информации:

1. Форма «Карточка счета за период».

2. Форма «Шахматный баланс по счетам за период».

3. Форма «Оборотно-сальдовая ведомость по счетам за период».

4. Форма «Главная книга».

Исходные данные:

Журнал хозяйственных операций ООО «АЛЬФА»

№	Дата	Дебет	Кредит	Сумма, руб.	Содержание
1.	01.04.2001			6 000	Объявление Уст. Капитала
2.	05.04.2001			2 000	Взнос осн. средств - Иванов
3.	05.04.2001			4 000	Взнос ден. средств - Петров, Сидоров
4.	07.04.2001			3 000	Предоплата за товар АО "Игрек"
5.	09.05.2001			3 000	Поступил товар от АО "Игрек"
6.	11.05.2001			3 000	Отгружен товар 000 "Зет"
7.	13.05.2001			4 500	Оплачен товар 000 "Зет"
8.	13.05.2001		68.20	246,90	1-е начисление НДС
9.	13.05.2001		68.21	18,45	1-е начисление СН
10.	14.05.2001				Получено в банке на ком. расходы
11.	14.05.2001				Получено на ком. расходы - Иванов
12.	14.05.2001				Получено на ком. расходы - Сидоров
13.	14.05.2001				Получено на ком. расходы - Петров
14.	15.05.2001				Получен кредит банка
15.	17.05.2001				Предоплата за товар АО "Игрек"
16.	17.05.2001				Представлен аванс, отчет - Иванов
17.	17.05.2001				Представлен аванс, отчет - Сидоров
18.	17.05.2001	ТО			Приняты на учет материалы - Петров
19.	17.05.2001				Приняты на учет МБП -Петров
20.	17.05.2001	19.40			Учтен НДС по МБП -Петров
21.	19.06.2001				Поступил товар от АО "Игрек"
22.	23.06.2001		68.20		2-е начисление НДС
23.	24.06.2001				Выплачены % сверх ставки ЦБ + 3%
24.	24.06.2001				Возвращен кредит
25.	30.06.2001				Начислена з/плата за октябрь
26.	30.06.2001		69.10		Начислен взнос в ПФ - 1 % к удерж,
27.	30.06.2001		68.50	154,44	Начислен под. налог к удержанию
28.	30.06.2001			95,37	Начислены пр. удержания -Сидоров
29.	30.06.2001		69.11		Начислен взнос в ПФ - 28%
30.	30.06.2001		69,30	64,8	Начислен взнос в ФМС - 3.6%
31.	30.06.2001		69.20	97,20	Начислен взнос в ФСС - 5.4%
32.	30.06.2001		69.4		Начислен взнос в ФЗ - 2%
33.	30.06.2001		68.50		Начислен трансп. налог – 1%
34.	30.10.2001			3704,75	Закрыт счет 46 на прибыль

Дата добавления: 2014-11-16