Ввод дат и сальдо на начало дня
Задачей макроса КалендарьДатыСальдо (рис. 21.7.) является ввод формул:
определения номера года, в котором может находиться вводимый в ячейку D2 номер месяца;
даты, за которую производятся ежедневные расчеты в платежном календаре;
остатков денежных средств на начало каждого дня.
Как помните, при создании таблицы на листе СуммПотоки было задано ограничение вертикального расположения таблицы строкой 200 или, исходя из рабочей области, таблица охватывает временной интервал в 198 дней. Ограничение было связано с увеличением скорости обработки информации. Причем алгоритм создания этой таблицы, самостоятельно определяет дату, с которой будет начинается этот временной интервал. Как следствие, если начальная дата находится во второй половине года, то временной интервал перейдет и на следующий год.
Кроме того, создавая различные приложения и модели, на протяжении всей книги, мы стремились к тому, чтобы пользователь при работе с компьютером делал как можно меньше ненужных движений. К таким "движениям" можно отнести и ввод номера года, за который будет формироваться платежный календарь. Поручим Excel самостоятельно производить выбор года. Основной критерий — год должен находиться во временном диапазоне, формируемом на листе СуммПотоки и определяться номером вводимого месяца в ячейку D2 на листе ПК.
Несмотря на кажущуюся сложность, задача довольно проста и определяется одной формулой, по критериям: заданный временной диапазон и значение номера месяца. Формула определения номера года в ячейке D3:
=ЕСЛИ(ИЛИ(ГОД(МИН(СуммПотоки!B3:B200))=ГОД(МАКС(СуммПотоки!B3:B200));МЕСЯЦ(МИН(СуммПотоки!B3:B200))<=D2);ГОД(МИН(СуммПотоки!B3:B200));ГОД(МАКС(СуммПотоки!B3:B200))) (21.8)
в первом аргументе функции ЕСЛИ функция ИЛИ проверяет два условия:
равен ли максимальный год в области дат на рабочем листе СуммПотоки минимальному номеру года в том же диапазоне. Простыми словами — будет ли в этом интервале встреча Нового года?;
больше или равен минимальный номер месяца в этой же области дат номеру месяца в ячейке D2.
При соблюдении одного из условий, или обоих одновременно, формула возвращает минимальное значение года в области дат на рабочем листе СуммПотоки. В противном случае возвращается номер максимального года в этой же области.
Формула формирования даты в ячейке D6:
=ЕСЛИ(ЕОШ(СЦЕПИТЬ(D7;".";$D$2;".";$D$3)*1);0;СЦЕПИТЬ(D7;".";$D$2;".";$D$3)*1) (21.9)
с помощью функции СЦЕПИТЬ соединяет в единое целое день (ячейка D7), месяц (ячейка D2) и год (ячейка D2). Алгоритм вычислений таких формул описан в главе 7. Цикл заполнения платежного календаря предполагает, что в месяце 31 день. Следовательно, как в нашем примере, если формировать календарь за февраль, в котором 28 дней, то, в этом месяце, начиная с 29-го числа, будет возвращаться значение ошибки #ЗНАЧ. Поэтому функцию СЦЕПИТЬ в первом аргументе функции ЕСЛИ проверяет функция ЕОШ, которая при обнаружении ошибки возвратит значение ИСТИНА. В таком случае функция ЕСЛИ возвратит значение 0, в противном — значение даты в формате Excel, созданной функцией СЦЕПИТЬ.
По этой дате формулой в ячейке D9 определяется остаток денежных средств на начало дня, используя остаток денежных средств в таблице денежных потоков на рабочем листе СуммПотоки:
=ЕСЛИ(ЕНД(ВПР(D6-1;СуммПотоки!$B$3:$E$200;4;ЛОЖЬ));0;ВПР(D6-1;СуммПотоки!$B$3:$E$200;4;ЛОЖЬ)) (21.10)
Основная функция поиска данных формулы — функция ВПР. При отсутствии даты в диапазоне поиска, для устранения возвращаемого формулой значения ошибки, в первом аргументе функции ЕСЛИ применена функция ЕНД. А функция ЕСЛИ выбирает — при отсутствии даты, возвращает значение 0, в противном случае значение остатка денежных средств на заданную дату, найденное функцией ВПР.
Запись макроса КалендарьДатыСальдо заключается в последовательном вводе этих формул в ячейки D3, D6:AH6 и D9:AH9 и последующем редактировании кода VBA после записи макроса.
Рис. 21.7. Подпрограмма КалендарьДатыСальдо