На этом шаге мы перечислим формулы, используемые для расчетов.

Выборка данных из рабочего листа ИсхДанные осуществляется в столбцах АА:АС листа СуммПотоки (рисунок 1) по введенному признаку движения денежных средств (ДДС) в столбец Н рабочего листа ИсхДанные.

Рис.1. Таблица отображения движения денежных средств в ежедневном разрезе

Для этого введите формулы в ячейку АА3:

  =ЕСЛИ(ИсхДанные!H10=1;ИсхДанные!D10;"")

и в ячейку АВ3:

  =ЕСЛИ($AA3="";0;ИсхДанные!I10)

Первая формула при наличии введенного признака ДДС в ячейку Н10 рабочего листа ИсхДанные возвращает значение даты, введенной в ячейку D10 того же рабочего листа. А вторая формула, при наличии даты в ячейке АА3, возвращает значение поступления денежных средств (далее ДС) в ячейке I10 рабочего листа ИсхДанные. Во второй формуле, при ссылке на ячейку АА3, введена абсолютная ссылка на столбец АА и поэтому при копировании содержимого ячейки АВ3 в ячейку АС3, ссылка в формуле на ячейку АА3 не изменится, но формула будет возвращать уже значение списанных ДС, введенных в ячейку J10 рабочего листа ИсхДанные.

Следующий элемент создания таблицы - ввод формул определения дат:

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

Определение даты, с которой начинается таблица, осуществляется в ячейке В3 по формуле, которая с помощью функции МИН производит поиск минимальной даты в столбце АА, а затем вычитает из нее один день:

  =МИН(AA:AA)-1

В ячейке В4 формула, добавляющая один день, которая затем копируется в область В4:В200:

  =B3+1

Формула контроля введенных дат находится в ячейке В1:

  =ЕСЛИ(МАКС(AA:AA)<=МАКС(B3:B200);0;
    "Обнаружена ошибка в вводе данных по дате!")

которая сравнивает наибольшую дату в столбце АА и, если она больше даты, введенной в таблицу формирования оборотов ДС, то возвращает текст: Обнаружена ошибка в вводе данных по дате!. Это говорит о том, что в таблице на рабочем листе ИсхДанные введены даты, которые превышают планируемый горизонт, а значит, при расчетах могут быть присутствовать ошибки.

Формула суммированных оборотов ДДС за день в ячейке С3:

  =СУММЕСЛИ($AA:$AA;$B3;AB:AB)

В ячейке D3 формула аналогична, но со знаком минус:

  =-СУММЕСЛИ($AA:$AA;$B3;AC:AC)

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

При создании формулы определения сальдо имеющихся всех денежных средств на первый день расчета в ячейке Е3:

  =ИсхДанные!D1+ОКРУГЛ(ИсхДанные!D2*ВПР(B3;
    '<путь к файлу>\[Курс.xls]Курс'!$A:$D;2;ЛОЖЬ);2)+
    ОКРУГЛ(ИсхДанные!D3*ВПР(B3;'<путь к файлу>\[Курс.xls]Курс'!$A:$D;3;ЛОЖЬ);2)

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

заданный диск;
имя директории;
имя файла - Курс;
имя рабочего листа - Курс.

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

Значение сальдо за последующие дни определяется по формуле в ячейке Е4, суммирующей значение сальдо предыдущего дня с суммой оборотов за день. Формула затем копируется в диапазон ячеек Е4:Е200:

  =E3+СУММ(C4:D4)

Рис.2. Формулы вычислений таблицы отображения движения денежных средств в ежедневном разрезе

На следующем шаге мы рассмотрим автоматизацию ввода формул расчета.

Предыдущий шаг Содержание Следующий шаг