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

Механизм составления оборотно-сальдовой ведомости традиционным образом производится в несколько этапов:

1. Выбирается группа объектов, по которым необходимо составить оборотно-сальдовую ведомость.

2. Последовательно по каждому элементу данной группы производится выборка операций в рамках определенного периода. При этом операции по поступлению вписываются в графу приход, операции по выбытию (отпуску) – в графу расход. После этого производится суммирование по графам «Приход» и «Расход».

3. Агрегированные (суммированные) данные по каждому элементу выписываются в отдельную таблицу.

4. Если нет входящий остатков, то необходимо выполнить шаги 1-3 для периода < Начало деятельности > - < Начальная дата - 1 >.

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

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

ЖУРНАЛ РЕГИСТРАЦИИ ОПЕРАЦИЙ

№ п.п.	Дата	Кому (от кого), Ф.И.О. или название организации	Наименование материала	Ед. изм.	Приход, ед.	Расход, ед.
	12.03.2001	Иванов	Гвозди	кг
	13.03.2001	Петров	Гвозди	кг
	15.03.2001	Сидоров	Гвозди	кг
	15.03.2001	Петров	Гвозди	кг
	15.03.2001	Иванов	Порошок	пач
	16.03.2001	Иванов	Порошок	пач
	19.03.2001	Иванов	Гвозди	кг
	20.03.2001	Сидоров	Гвозди	кг
	25.03.2001	Сидоров	Порошок	пач

Из указанных исходных данных на листе Лист2 необходимо получить таблицу вида:

ОБОРОТНО-САЛЬДОВАЯ ВЕДОМОСТЬ
ПО ДВИЖЕНИЮ МАТЕРИАЛОВ НА СКЛАДЕ

с 15.03.2001 по 20.03.2001

Склад: СКЛАД №1

Группа товаров: Разное

№ п.п.	Наименование	Ед. изм.	Остаток на начало, ед.	Приход, ед.	Расход, ед.	Остаток на конец, ед.
	Гвозди	кг.	?	?	?	?
	Порошок	пач.	?	?	?	?

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

?	Пример выполнения работы

1. Необходимо создать форму (создать книгу Excel и набрать таблицу) в соответствии с вариантом на листе Лист1 новой книги Excel.

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

- выделить диапазон ячеек-значений определенного столбца;

- Вст а вка ® И мя ® П рисвоить… ® В поле имя указать имя (например, НаимТовар) ® Ok.

- Горячая клавиша для присвоения имен ячейкам: Ctrl+F3.

- Таким образом, мы получим имена НомерП, ДатаРег, НаимТовар, ЕдИзм, ПриходКолво, РасходКолво.

3. Переходим на Лист2 и создаем форму оборотно-сальдовой ведомости в соответствии с заданием.

4. Присвоить имена ячейкам, в которых хранятся дата начало периода и дата конца отчетного периода.

- Выделяем ячейку, которая содержит начальную дату периода;

- Ctrl + F3 ® Имя: НачДата ® Ok.

- Выделяем ячейку, которая содержит конечную дату периода;

- Ctrl + F3 ® Имя: КонДата ® Ok.

Вводим в первую строку, после шапки оборотно-сальдовой ведомости в графу Остаток на начало следующую формулу

=СУММ((ЕСЛИ($B8= НаимТовар;1;0)) * (ЕСЛИ(ДатаРег <НачДата;1;0)) *
(ПриходКолво - РасходКолво))

и, не выходя из режима редактирования, нажать Ctrl + Shift + Enter для того, чтобы Excel идентифицировала данную формулу как формулу обработки массива. Формула примет вид:

{=СУММ((ЕСЛИ($B8= НаимТовар;1;0)) * (ЕСЛИ(ДатаРег <НачДата;1;0)) *
(ПриходКолво - РасходКолво))}

Рассмотрим составляющие данной формулы.

(ПриходКолво-РасходКолво) – означает, что от каждого элемента массива ПриходКолво необходимо отнять элемент массива РасходКолво.

= {СУММ((ПриходКолво-РасходКолво))} - означает, что необходимо произвести суммирование всех элементов указанных массивов.

(ЕСЛИ($B8=НаимТовар;1;0)) – означает наложение ограничения на суммирование выражения (ПриходКолво-РасходКолво), а именно: товар должен быть равен товару, указанному в ячейке B8. Таким осуществляется косвенная выборка, т.е. суммируются только те элементы массивов, у которых наименование товара равно B8.

(ЕСЛИ(ДатаРег<НачДата;1;0)) – ограничения по дате совершения операции.

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

Вводим в первую строку, после шапки оборотно-сальдовой ведомости в графу Приход следующую формулу

{=СУММ((ЕСЛИ($B8= НаимТовар;1;0)) * (ЕСЛИ(ДатаРег >=НачДата;1;0)) *
(ЕСЛИ(ДатаРег <=КонДата;1;0)) * ПриходКолво)}

в графу Расход следующую формулу

{=СУММ((ЕСЛИ($B8= НаимТовар;1;0)) * (ЕСЛИ(ДатаРег >=НачДата;1;0))
* (ЕСЛИ(ДатаРег <=КонДата;1;0)) * РасходКолво)}

в графу Остаток следующую формулу =D8+E8-F8, т.е. (Остаток на начало + Приход – Расход).

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Что такое формула массива?

2. Для чего используются формулы массива?

3. Опишите состав и порядок использования формул массива в вычислениях?

4. Как задаются фигурные скобки, ограничивающие формулу массива.

5. Опишите механизм, который лежит в основе технологии формул массива. Что делает формула массива?

6. Почему при использовании формул массива необходимо именовать диапазоны ячеек?

7. Как создать формулы массива, использующие диапазоны ячеек в других книгах Excel?

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

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

Вариант 1

20 - 30 мин.

Произведена предоплата товаров и услуг:

№	Дата операции	Кому (организация или Ф.И.О.)	Сумма, руб.	Примечание
	10.03.2001	ООО "СИГМА"	20 000	за доску
	13.03.2001	ООО "Альбион"	80 000	за Кафель белый 20 х 20
	15.03.2001	ОАО "Металлоконструкции"	59 3000	за арматуру
	19.03.2001	ООО "Стройматериалы"	50 000	за стройматериалы
	26.03.2001	ООО "СИГМА"	100 000	за стройматериалы

Поступление продукции на склад:

№	Дата операции	Наименование товара	Ед. изм.	От кого (организация или Ф.И.О.)	Кол-во	Цена, руб.	Сумма, руб.
	15.03.2001	Доска обрезная 25 см	м3	ООО "СИГМА"		2 000	20 000
	16.03.2001	Кафель белый 20 х 20 M-500	тн	ООО "Альбион"			47 600
	16.03.2001	Кафель белый 20 х 20 M-400	тн	ООО "Альбион"			40 800
	18.03.2001	Арматура d12	тн	ОАО "Металлоконструкции"		9 000	270 000
	18.03.2001	Арматура d16	тн	ОАО "Металлоконструкции"		8 500	323 000
	20.03.2001	Кирпич	тыс. шт	ООО "Стройматериалы"			9 600
	20.03.2001	Доска обрезная 25 см	м3	ООО "СИГМА"		1 900	95 000
	22.03.2001	Песок	маш.	ООО "Стройматериалы"			15 000
	25.03.2001	Шифер 12-волн.	шт.	ООО "СИГМА"			9 000
	30.03.2001	Кирпич	тыс. шт.	ООО "Стройматериалы"			19 200

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

СОСТАВ ДЕБИТОРСКОЙ И КРЕДИТОРСКОЙ ЗАДОЛЖЕННОСТИ

с «___» __________ 2001г. по «___» __________ 2001г.

№ п/п	Наименование организации	Дебиторская задолженность, руб	Кредиторская задолженность, руб.
?	?	?	?
…	…	…	…

Составленная форма должна автоматически пересчитываться при изменении периода расчета и состава исходных данных.

Вариант 2

20 - 30 мин.

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

№ п.п.	Дата	Кому (от кого), Ф.И.О. или название организации	Наименование материала	Ед. изм.	Приход, ед.	Расход, ед.
1.	12.03.2001	Иванов	Гвозди	кг
2.	13.03.2001	Петров	Гвозди	кг
3.	15.03.2001	Сидоров	Гвозди	кг
4.	15.03.2001	Петров	Гвозди	кг
5.	15.03.2001	Иванов	Порошок	пач
6.	16.03.2001	Иванов	Порошок	пач
7.	19.03.2001	Иванов	Гвозди	кг
8.	20.03.2001	Сидоров	Гвозди	кг
9.	25.03.2001	Сидоров	Порошок	пач
10.	12.04.2001	Иванов	Гвозди	кг
11.	13.04.2001	Петров	Гвозди	кг
12.	15.05.2001	Сидоров	Гвозди	кг
13.	15.06.2001	Петров	Гвозди	кг
14.	15.06.2001	Иванов	Порошок	пач
15.	16.06.2001	Иванов	Порошок	пач
16.	19.06.2001	Иванов	Гвозди	кг
17.	20.07.2001	Сидоров	Гвозди	кг
18.	15.05.2001	Сидоров	Гвозди	кг
19.	15.06.2001	Петров	Гвозди	кг
20.	15.06.2001	Иванов	Порошок	пач
21.	16.06.2001	Иванов	Порошок	пач
22.	19.06.2001	Иванов	Гвозди	кг
23.	20.07.2001	Сидоров	Гвозди	кг

Вариант 3

20 - 30 мин.

Исходная информация представлена в нижеследующей таблице. Ячейки, отмеченные знаком вопроса необходимо рассчитать (Кол-во * Цена). Требуется с использованием технологии формул массива на основе исходных данных составить:

- оборотно-сальдовую ведомость количественного учета;

- форму для определения прибыли от реализации (метод оценки товарно-материальных запасов – «по средней»).

ПОСТУПЛЕНИЕ И РЕАЛИЗАЦИЯ ТОВАРОВ ОАО "Торговля+"

				Поступление товаров от поставщиков	Реализация товаров
№ п.п.	Дата	Кому (от кого), Ф.И.О. или название организации	Наименование товара	Ед. изм.	Кол-во, ед.	Цена, руб	Сумма, руб.	Ед. изм.	Кол-во, ед.	Цена, руб	Сумма, руб.
	01.06.2001	Организация 1	Товар1	тн			?				?
	02.06.2001	Организация 2	Товар2	шт.			?				?
	03.06.2001		Товар1	тн			?	тн			?
	04.06.2001	Организация 2	Товар3	шт.			?				?
	05.06.2001	Организация 2	Товар4	кг			?				?
	06.06.2001	Предприниматель 1	Товар5	шт.		13,5	?				?
	07.06.2001		Товар2	шт.			?	шт.			?
	08.06.2001		Товар2	шт.			?	шт.			?
	09.06.2001	Организация 2	Товар3	шт.			?	шт.			?
	10.07.2001	Организация 2	Товар4	кг			?				?
	11.07.2001		Товар4	кг			?	кг			?
	12.07.2001		Товар4	кг			?	кг			?
	13.07.2001	Предприниматель 1	Товар5	шт.			?				?
	14.07.2001	Организация 1	Товар1	тн			?				?
	15.07.2001	Организация 2	Товар2	шт.			?				?
	16.07.2001		Товар1	тн			?	тн			?
	17.07.2001	Предприниматель 2	Товар6	шт.			?				?
	18.07.2001	Предприниматель 2	Товар6	шт.			?	шт.			?
	01.06.2001	Организация 1	Товар1	тн			?				?
	02.06.2001	Организация 2	Товар2	шт.			?				?
	03.06.2001		Товар1	тн			?	тн			?
	04.06.2001	Организация 2	Товар3	шт.			?				?
	05.06.2001	Организация 2	Товар4	кг			?				?
	06.06.2001	Предприниматель 1	Товар5	шт.		13,5	?				?
	07.06.2001		Товар2	шт.			?	шт.			?
	08.06.2001		Товар2	шт.			?	шт.			?
	09.06.2001	Организация 2	Товар3	шт.			?	шт.			?
	01.05.2001	Организация 2	Товар4	кг			?				?
	03.05.2001		Товар4	кг			?	кг			?
	12.07.2001		Товар4	кг			?	кг			?
	13.07.2001	Предприниматель 1	Товар5	шт.			?				?
	14.07.2001	Организация 1	Товар1	тн			?				?
	15.07.2001	Организация 2	Товар2	шт.			?				?
	16.07.2001		Товар1	тн			?	тн			?
	17.07.2001	Предприниматель 2	Товар6	шт.			?				?
	18.07.2001	Предприниматель 2	Товар6	шт.			?	шт.			?

Занятие №9

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

Цель работы

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

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

Система документооборота – система организации движения документов, основная цель которой - контроль за местоположением документов и их движением. Любая система документооборота основана на регистрации документов – фиксировании поступления, выбытия, перемещения документов в специальных книгах-регистрах. Электронная система документооборота предусматривает регистрацию движения документов в базе данных.

При регистрации документов необходимо отнести документ к какой-либо группе:

- по времени создания документа (к какому-либо периоду);

- по типу документа (письма, приказы, распоряжения и т.п.);

- по месту возникновения документа (подразделение, отдел, внешняя организация);

- по другим признакам (исполнителю, структурному подразделению, дополнительной информации).

Большие потоки информации в виде различного рода документов (писем, приказов, распоряжений, договоров, инструкций, отчетов, бизнес-планов, предложений, технической документации, различных расчетов, прайсов и т.п.) ежедневно проходит через современные организации. Количество электронных документов постоянно растет и на сегодняшний день составляет не менее 50% от числа всех документов. Это объясняется широким внедрением информационных технологий в различные сферы общества.

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

Часто от того насколько быстро будет найден и обработан тот или иной документ зависит очень многое.

Традиционно вопрос организации электронного документооборота на большинстве предприятий ограничивается в лучшем случае разбросом документов по папкам. Следует ли говорить, что содержимое каталога, например «с:\Мои документы\Письма» со 150 файлами, является крайне неинформативным: для того, чтобы найти спустя какое-то время конкретное письмо, посвященное определенной тематике, потребуется просмотреть весь этот список.

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