Баланс участника (долг/поступление): Графа 5 – Графа 6

 

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

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

Для решения этой задачи будем использовать MS Excel, механизм сводных таблиц и операции с матрицами.

 

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

 

1. Создать книгу Excel и на отдельном листе набрать журнал хозяйственных операций (см. выше).

2. Вставить N пустых строк в начало таблицы, где N – количество участников. В первых N строках в графах 4 и 5 необходимо перечислить список участников, в последующих N строках также перечислить в графе 5 список участников.

Таблица 3

№ операции	Дата операции	Наименование услуги	Кто оказал услугу	Кому оказал услугу	Сумма, руб.
			A1	A1
			A2	A2
			A3	A3
			A4	A4
			A5	A5
			A6	A6
			A7	A7
			A8	A8

 

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

3. На основании журнала хозяйственных операций (набранной таблицы) создать сводную таблицу, где в качестве имен строк будут значения из графы 4, а в качестве имен столбцов – значения из графы 5.

- Выделить таблицу ® Данные ® Сводная таблица ® … ответить на вопросы.

 

 

- Перетащить графу 4 в область строк таблицы.

- Перетащить графу 5 в область столбцов таблицы.

- Перетащить графу 6 в область данных таблицы.

 

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

Для этого в области данных вызывается контекстное меню, где выбирается пункт «Параметры поля». (см. рис. ниже). Далее в появившемся диалоге выбирается «Сумма» в качестве операции по полю 6.

 

 

В результате получим следующего таблицу вида:

 

Таблица 4

СВОДНАЯ ТАБЛИЦА

 

Сумма по полю 6	Графа5
Графа4	A1	A2	A3	A4	A5	A6	A7	A8	Общий итог
A1		10 000							10 000
A2	30 000		33 000				8 000		71 000
A3	15 000							34 000	49 000
A4		20 000							20 000
A5
A6
A7					26 000				26 000
A8		5 000							5 000
Общий итог	45 000	35 000	33 000		26 000		8 000	34 000	181 000

 

По сумме значений строки таблицы (Графа «Общий итог») определяем, сколько должны каждому участнику. Так участнику А1 должны 10 000.

По сумме значений столбца таблицы (Строка «Общий итог») определяем, сколько должен каждый участник. Так участник А1 должен 45 000.

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

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

Таблица 5.

ТРАНСПОНИРОВАННАЯ СВОДНАЯ ТАБЛИЦА

Сумма по полю 6	Графа4
Графа5	A1	A2	A3	A4	A5	A6	A7	A8	Общий итог
A1		30 000	15 000						45 000
A2	10 000			20 000				5 000	35 000
A3		33 000							33 000
A4
A5							26 000		26 000
A6
A7		8 000							8 000
A8			34 000						34 000
Общий итог	10 000	71 000	49 000	20 000			26 000	5 000	181 000

 

Теперь необходимо от исходной матрицы отнять транспонированную.

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

1. Скопировать сводную таблицу в буфер обмена (Выделить ячейки таблицы ® П равка ® К опировать).

2. Выбрать первую точку вставки: перейти на необходимую ячейку.

3. Вставить таблицу значений сводной таблицы: П равка ® Сп е циальная вставка ® Только значения ® Ок.

4. Выбрать вторую точку вставки ниже текущей таблицы: перейти на соответствующую ячейку.

5. Вставить транспонированную таблицу значений сводной таблицы: Пр а вка ® Сп е циальная вставка ® Только значения ® Ок .

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

 

Результатом будет являться таблица конечных расчетов (см. Табл. 6), в которой в строке «Общий итог» будет отражена сумма к получению, а в столбце «Общий итог» – сумма долга. Если в строке «Общий итог» получилось отрицательное число, значит конечный результат деятельности участника А отрицательный, т.е. он должен указанную сумму (см. таблицу ниже).

Таблица 6.

Таблица окончательных расчетов или результатов деятельности

	ДОЛЖНИКИ
	A1	A2	A3	A4	A5	A6	A7	A8	Общий итог
A1		-20 000	-15 000						-35 000
A2	20 000		33 000	-20 000			8 000	-5 000	36 000
A3	15 000	-33 000						34 000	16 000
A4		20 000							20 000
A5							-26 000		-26 000
A6
A7		-8 000			26 000				18 000
A8		5 000	-34 000						-29 000
Общий итог	35 000	-36 000	-16 000	-20 000	26 000		-18 000	29 000

 

 

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

 

 

 

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

 

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

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

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

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

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

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

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

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

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

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

 

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

 

1. Что такое сводная таблица? Для решения каких задач требуется создание сводных таблиц?

2. Опишите принцип форматирования сводной таблицы.

3. Опишите технологию создания сводных таблиц в Excel.

4. В чем состоит задача о взаимных расчетах?

5. Опишите последовательность решения задачи с использованием технологии сводных таблиц.

6. Что является результатом решения задачи о взаимных расчетах.

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

 

 

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

 

Вариант 1

20 - 30 мин.

 

Исходные данные: Журнал хозяйственных операций

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

 

 

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

 

№ операции	Дата операции	Наименование услуги	Кто оказал услугу	Кому оказал услугу	Сумма, руб.
	26.01.01	Услуга1	Организация 5	Организация 9	85 368
	02.04.01	Услуга2	Организация 5	Организация 1	22 470
	22.03.01	Услуга3	Организация 8	Организация 5	94 973
	22.02.01	Услуга4	Организация 9	Организация 2	53 465
	19.02.01	Услуга5	Организация 7	Организация 2	2 126
	02.03.01	Услуга6	Организация 2	Организация 9	1 963
	23.04.01	Услуга7	Организация 6	Организация 4	2 106
	05.03.01	Услуга8	Организация 1	Организация 5	93 860
	15.02.01	Услуга9	Организация 4	Организация 5	2 135
	09.01.01	Услуга1	Организация 7	Организация 3	65 703
	26.03.01	Услуга2	Организация 5	Организация 7	33 616
	28.02.01	Услуга5	Организация 2	Организация 9	42 624
	26.02.01	Услуга6	Организация 2	Организация 8	54 386
	23.04.01	Услуга7	Организация 3	Организация 1	55 161
	28.03.01	Услуга1	Организация 9	Организация 3	46 109
	12.03.01	Услуга2	Организация 9	Организация 4	4 452
	22.03.01	Услуга11	Организация 3	Организация 2	68 708
	22.03.01	Услуга8	Организация 6	Организация 2	74 274

 

 

Вариант 2

20 - 30 мин.

 

Исходные данные: Журнал хозяйственных операций.

 

Требуется: На основании исходных данных вычислить результат совместной деятельности для каждого участника матричным способом с помощью MS Excel 2000.

 

 

 

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

 

№ операции	Дата операции	Кто дал заем	Кому дал заем	Сумма, руб.
	18.01.01	Организация7	Предприниматель6	50 488
	10.03.01	Предприниматель5	Организация5	8 472
	29.04.01	Предприниматель4	Организация8	22 921
	08.02.01	Организация1	Предприниматель7	26 977
	22.02.01	Организация5	Предприниматель9	62 302
	17.04.01	Организация9	Организация2	84 429
	15.01.01	Организация7	Организация7
	07.04.01	Организация2	Предприниматель8
	19.04.01	Организация8	Предприниматель4	16 427
	24.01.01	Предприниматель5	Организация9	30 097
	26.01.01	Организация9	Предприниматель9	32 676
	27.02.01	Предприниматель1	Организация7	67 428
	16.01.01	Организация3	Организация9	36 325
	23.02.01	Предприниматель9	Организация8	21 741
	03.03.01	Организация9	Предприниматель4	60 198
	23.02.01	Предприниматель3	Предприниматель5	59 081
	03.03.01	Предприниматель3	Предприниматель4	34 501
	15.04.01	Предприниматель4	Предприниматель1	2 567

 

 

Занятие №8

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

 

Цель работы

 

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

 

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

 

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

Оборотно-сальдовая ведомость представляет собой таблицу следующего вида:

 

№ п.п.	Наименование	Остаток на начало	Приход	Расход	Остаток на конец

 

где Наименование – наименование объекта (Ф.И.О. сотрудников, товары на складе, материалы и т.п.), Остаток на начало – входящий остаток на первое число отчетного периода., Приход – поступление по данному наименованию, Расход – расход по данному наименованию, Остаток на конец – остаток на последнее число отчетного периода.

Оборотно-сальдовые ведомости бывают трех видов:

1. Количественные – отражающие движение товарно-материальных ценностей (далее ТМЦ) в натуральных показателях (шт., кг., м. и т.п.)

В этом случае форма оборотно-сальдовой ведомости примет следующий вид:

 

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

 

2. Суммовые – отражающие движение товарно-материальных ценностей (далее ТМЦ) в денежных единицах (руб., тыс. руб., у.е. и т.п.)

В этом случае форма оборотно-сальдовой ведомости примет следующий вид:

 

№ п.п.	Наименование	Остаток на начало, руб.	Приход, руб.	Расход, руб.	Остаток на конец, руб.

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

 

Допускается следующая модификация:

Руб.

№ п.п.	Наименование	Остаток на начало	Приход	Расход	Остаток на конец

 

3. Количественно-суммовые – являются результатом сочетания количественных и суммовых оборотно-сальдовых ведомостей:

 

№ п.п.	Наименование	Ед. изм	Кол-во	№ п.п.	Наименование	Ед. изм	Остаток на начало	Приход	Расход	Остаток на конец