Лабораторная работа №2. Использование технологии сводных таблиц для решения задачи «О взаимных расчетах»

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

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

Вставить N пустых строк в начало таблицы, где N- количество участников. В первых N строках в графах 4 и 5 необходимо перечислить список участников взаиморасчетов. В нашем примере участников восемь (А1, А2, ..., А8), т.к. участников всего восемь, а количество взаимодействий в данном случае не рассматриваем.

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

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

- Выделить таблицу и создать сводную таблицу.

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

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

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

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

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

По сумме строки таблицы определяем сколько должны каждому участнику (так участнику А1 должны 10 000).

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

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

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

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

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

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

Перейти на новый лист, выбрать первую точку вставки.

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

Переходим на лист где у нас две сводные таблицы и аналогичным способом копируем транспонированную таблицу (вторую).

Переходим на лист куда мы скопировали первую таблицу и прямо на нее же копируем следующим образом: Правка→Специальная вставка→выбираем пункт Значения, а в операции Вычесть→OK.

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