Задача о взаимных расчетах

Цель работы: Используя технологию сводных таблиц решить задачу о взаимных расчетах.

Предположим, что имеется N участников совместной деятельности (А1…АN) – юридические и физические лица. В процессе совместной деятельности они поставляют друг другу материалы, оказывают услуги и т.д.

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

Таблица 3.1

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

№ опер.	Дата операции	Наименование услуги	Кем оказана услуга	Кому оказана услуга	Сумма, руб.

	15.03.2011г.	У1	ОАО «Исток»	ООО «Иртекс»	2 000,00
	16.03.2011г.	У2	ООО «Иртекс»	ОАО «Исток»	50 000,00
	16.03.2011г.	У3	ОАО «Альянс»	ОАО «Исток»	12 500,00
	17.03.2011г.	У4	ООО «Иртекс»	ОАО «Альянс»	11 000,00
	17.03.2011г.	У5	ОАО «Строй-инвест»	ООО «Иртекс»	7 000,00
	20.03.2011г.	У6	ЗАО «Оазис»	ЗАО «Мираж»	47 000,00
	21.03.2011г.	У7	ООО «Мебельщик»	ООО «Иртекс»	14 200,00
	25.03.2011г.	У8	ОАО «Альянс»	ООО «Мебельщик»	3 000,00
	26.03.2011г.	У9	ООО «Иртекс»	ЗАО «Оазис»	2 400,00

где: У1- У9 – какая – либо услуга (поставка материалов, готовой продукции и т.д.)

Необходимо определить:

1. Кто кому и сколько должен.

2. Общий объем оказанных взаимных услуг.

3. Определить баланс каждого участника.

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

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

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

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

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

- выделить таблицу → Вставка → Сводная таблица → выполнить необходимые действия;

- перенести графу 4 в область строк таблицы;

- перенести графу 5 в область столбцов таблицы;

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

В результате получится таблица, приведенная на рис. 3.2.

Рис. 3.2 Сводная таблица

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

Рис. 3.3. Контекстное меню – «Параметры полей значений»

Получаем следующий результат:

Рис. 3.4 Готовая сводная таблица

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

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

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

Рис. 3.5. Сводная и транспонированная таблицы

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

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

2. Перейти на необходимую ячейку для вставки;

3. Вставить таблицу значений сводной таблицы: Контекстное меню → Специальная вставка → Значения;

4. Скопировать транспонированную таблицу и вставить ее ниже исходной таблицы значений (Контекстное меню → Специальная вставка → Значения);

Рис. 3.6. Значения сводной и транспонированной таблицы

5. Скопировать транспонированную таблицу значений → перейти на верхнюю левую ячейку исходной таблицы значений → Выполнить «Специальная вставка» → Вычесть → ОК (рис. 3.7.).

Рис. 3.7. Меню «Специальная вставка»

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

Рис. 3.8. Таблица окончательных расчетов

Задания

Вариант 1

Таблица 3.2

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

№	Дата	Наименование услуги	Кто оказал услугу	Кому оказал услугу	Сумма, руб.
	26.01.2012	Услуга 1	Предприятие 5	Предприятие 9	98 500,00
	05.04.2012	Услуга 2	Предприятие 5	Предприятие 1	20 500,00
	22.03.2012	Услуга 3	Предприятие 8	Предприятие 5	93 200,00
	22.02.2012	Услуга 4	Предприятие 9	Предприятие 2	50 145,00
	18.02.2012	Услуга 5	Предприятие 7	Предприятие 2	2 100,00
	02.03.2012	Услуга 6	Предприятие 2	Предприятие 9	1 800,00
	23.04.2012	Услуга 7	Предприятие 6	Предприятие 4	86 420,00
	05.03.2012	Услуга 8	Предприятие 1	Предприятие 5	2 035,00
	15.02.2012	Услуга 9	Предприятие 4	Предприятие 5	61 703,00
	09.01.2012	Услуга 1	Предприятие 7	Предприятие 3	33 600,00
	26.03.2012	Услуга 2	Предприятие 5	Предприятие 7	41 624,00
	28.02.2012	Услуга 5	Предприятие 2	Предприятие 9	53 683,00
	26.02.2012	Услуга 6	Предприятие 2	Предприятие 8	16 155,00
	23.04.2012	Услуга 7	Предприятие 3	Предприятие 1	90 164,00
	28.03.2012	Услуга 1	Предприятие 9	Предприятие 3	46 109,00
	12.03.2012	Услуга 2	Предприятие 9	Предприятие 4	4 452,00
	22.03.2012	Услуга 11	Предприятие 3	Предприятие 2	86 708,00
	22.03.2012	Услуга 8	Предприятие 6	Предприятие 2	47 274,00

Необходимо: на основании исходных данных вычислить результат совместной деятельности предприятий матричным способом.

Вариант 2

Таблица 3.3

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

№	Дата	Наименование услуги	Кредитор	Заемщик	Сумма, руб.
	17.01.2012	Услуга 1	Предприятие 7	Организация 6	50 488,00
	10.03.2012	Услуга 2	Организация 5	Предприятие 5	8 300,00
	29.04.2012	Услуга 3	Организация 4	Предприятие 8	21 921,00
	08.02.2012	Услуга 4	Предприятие 1	Организация 7	26 900,00
	22.02.2012	Услуга 5	Предприятие 5	Организация 9	62 100,00
	17.04.2012	Услуга 6	Предприятие 9	Предприятие 2	80 942,00
	15.01.2012	Услуга 7	Предприятие 7	Предприятие 7	500,00
	07.04.2012	Услуга 8	Предприятие 2	Организация 8	600,00
	19.04.2012	Услуга 9	Предприятие 8	Организация 4	16 724,00
	24.01.2012	Услуга 1	Организация 5	Предприятие 9	30 790,00
	26.01.2012	Услуга 2	Предприятие 9	Организация 9	36 276,00
	27.02.2012	Услуга 5	Организация 1	Предприятие 7	76 428,00
	16.01.2012	Услуга 6	Предприятие 3	Предприятие 9	36 523,00
	23.02.2012	Услуга 7	Организация 9	Предприятие 8	12 741,00
	03.03.2012	Услуга 1	Предприятие 9	Организация 4	60 891,00
	23.02.2012	Услуга 2	Организация 3	Организация 5	95 180,00
	03.03.2012	Услуга 11	Организация 3	Организация 4	43 501,00
	15.04.2012	Услуга 8	Организация 4	Организация 1	2 765,00