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

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

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

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

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

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

Таблица 3

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

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

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

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

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

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

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

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

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

Таблица 4

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

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

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

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

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

Таблица 5.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Руб.

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

Формирование заголовка отчета

Каждый отчет должен начинаться с заголовка (титула). В заголовке любого отчета в обязательном порядке должны содержаться следующие реквизиты:

1. Наименование отчета – текстовое наименование отчета, отражающее содержание представленной в отчете информации.

Для суммовой оборотно-сальдовой ведомости движения материалов предлагается следующий шаблон наименования отчета.

Пример.

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

Здесь вместо прочерка проставляется: «ТМЦ», «Товаров на складе», «Денежных средств» и др.

Как правило, оформляется жирным шрифтом и прописными буквами с выравниванием по центру или по ширине.

2. Объект –то, к чему относится данный отчет. Например, необходим отчет по движению материалов на складе по Складу №1.

Пример:

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

по складу СКЛАД №1

Формулы массивов

Для обработки массивов информации в MS Excel существует специальный тип формул – формулы массивов.

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

Для создания массива необходимо:

1. Выделить группу ячеек мышью или с помощью клавиатуры (Shift + «стрелки»)

2. Вставка ® Имя ® Присвоить …

3. В поле «Имя» диапазона указать имя массива. Например, “Блок1”.

4. Нажать клавишу Enter или Ok.

Таким образом, получим массив Блок1, доступный для дальнейшей обработки.

Основные операции обработки массивов:

1. Суммирование значений элементов.

= СУММ (Блок1)

2. Определение среднего значения по значениям элементов массива

= СРЗНАЧ (Блок1)

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

Структура формулы массива

{= < Операция > ( < Условие >* <Выражение>)}

где { } (фигурные скобки) – результат нажатия Ctrl + Shift + Enter при завершении работы с формулой. Круглые скобки вставляются автоматически после нажатия Ctrl + Shift +Enter и указывают на то, что данная формула является формулой массива. Далее все формулы массива будем брать в фигурные скобки.

Операция – функция обработки данных (операции над группой ячеек). Например, функция «СУММ».

Условие – совокупность выражений вида

ЕСЛИ(Условие1;1;0) * ЕСЛИ(Условие2;1;0) … ЕСЛИ(УсловиеN;1;0),

где ЕСЛИ(Условие1;1;0) –функция, возвращающая 1, в случае истинности условия, 0 – если условие ложно. Совокупность данных выражений, соединенных оператором умножения реализует функцию отбора элемента массива:

F ( x ) = 1 * 1 * 1 …

Условие1 – условное выражение Например: B5 = НаимТовар, что означает: если в очередной элемент массива НаимТовар равен значению ячейки B5 текущего листа, то результат - 1, иначе - 0.

Выражение -арифметическое выражение, в составе которого имеются наименования массивов.

Например,

(ПриходТовар – РасходТовар)

означает, что от каждого элемента массива ПриходТовар будет отниматься элемент массива РасходТовар.

Внимание!Массивы в формулах массивов должны иметь одинаковые размерности, т.е. содержать одинаковое число элементов.

Пример формулы массива:

{= СУММ ( ЕСЛИ(НаимТовар=B5;1;0) * ЕСЛИ(ДатаРегистр>=НачДата;1;0)*

* ЕСЛИ(ДатаРегистр<=КонДата;1;0) * (ПриходТовар– РасходТовар)}

* жирным шрифтом выделены массивы.

Формула означает, что в результате ее расчета будет вычислено выражение (ПриходТовар – РасходТовар) для каждого элемента массивов, после чего эти выражения будут просуммированы функцией СУММ, т.е.

Условие1 * (ПриходТовар[1] – РасходТовар[1]) + Условие2 * (ПриходТовар[2] –
РасходТовар[2]) + … + УсловиеN * (ПриходТовар[N] – РасходТовар[N])

здесь Условие1-УсловиеN – результат вычисления выражения

ЕСЛИ(НаимТовар=B5;1;0) * ЕСЛИ(ДатаРегистр>=НачДата;1;0)*

* ЕСЛИ(ДатаРегистр<=КонДата;1;0),

которое принимает значение 0 или 1 в зависимости от условий, для каждого элемента. Если результат вычисления произведения условий равен нулю, то, следовательно, результат вычисления 0 * (ПриходТовар[1] – РасходТовар[2]) также равен нулю и данный элемент массива не участвует в расчете результата для формулы массива в целом.