На этом шаге мы продолжим изучение этого макроса.
Подпрограмма СортировкаПлатежей
Для записи макроса СортировкаПлатежей (рисунок 2), поместите табличный курсор в область созданной таблицы и, выполнив команду Данные | Сортировка, вызовите диалоговое окно Сортировка диапазона (рисунок 1).
Рис.1. Диалоговое окно Сортировка диапазона
Рис.2. Подпрограмма тройной сортировки по убыванию созданной таблицы
Это диалоговое окно позволяется произвести последовательную сортировку таблицы в выбранной последовательности и выбранному режиму: по убыванию или по возрастанию. Задайте сортировку всех трех столбцов с числовыми данными по убыванию в любой последовательности. Такая "тройная" операция сортировки дает стопроцентную гарантию, что все ячейки, содержащие суммы распределенных платежей в столбцах В:D, будут расположены в верхней области листа.
Обратите внимание на текст кода VBA полученной подпрограммы после его редактирования. Он хорошо подчеркивает свойство Sort по проведению тройной сортировки, не считая первую строку строкой заголовка.
Подпрограмма ФормулыРаспрПлатежей
Задача подпрограммы ФормулыРаспрПлатежей (рисунок 3) - ввести формулы в созданный лист Прогноз (2).
Рис.3. Подпрограмма ФормулыРаспрПлатежей
В диапазон ячеек G2:G26 вводятся ссылки на ячейку, расположенную выше. В диапазон J1:J26 вводятся формулы определения среднедневной подекадной суммы равномерных поступлений денежных средств в зависимости от декады. Формула в ячейке J1:
=ЕСЛИ(ЛЕВСИМВ($G$1;1)="В";0;
ЕСЛИ($F1="";0;ВПР($G$1;$A$1:$D$26;
ЕСЛИ(ДЕНЬ($F1)<=10;2;
ЕСЛИ(ДЕНЬ($F1)<=20;3;4));ЛОЖЬ)))
В первую очередь формула проверяет первый левый символ кода управленческого учета, и, если им является буква В (выплаты/расходы), то формула возвращает значение 0. Далее формула проверяет, присутствует ли в ячейке F1 дата. При ее отсутствии (день не рабочий) также возвращается значение 0. При не выполнении двух первых условий, функция ВПР производит поиск в области А1:D26 значений сумм, указанного в ячейке G1 кода управленческого учета, при этом номер столбца определяется в зависимости от номера дня даты, введенной в столбце F. Например, если номер дня до 10-и, то 2-й столбец, если выше 20-го номера, то столбец 4-й.
Формула в ячейке К1 аналогична предыдущей, но возвращает суммы расхода сумм равномерных платежей по первой букве П:
=ЕСЛИ(ЛЕВСИМВ($G$1;1)="П";0;
ЕСЛИ($F1="";0;ВПР($G$1;$A$1:$D$26;
ЕСЛИ(ДЕНЬ($F1)<=10;2;
ЕСЛИ(ДЕНЬ($F1)<=20;3;4));ЛОЖЬ)))
Эти формулы "начинают" работать только при выполнении макроса ЦиклФормированияПлатежей и их смысл будет описан далее.
Подпрограмма ЦиклФормированияПлатежей
Основой подпрограммы ЦиклФормированияПлатежей является цикл Do-Loop.
Рис.4. Подпрограмма ЦиклФормированияПлатежей
Последовательность выполнения ЦиклФормированияПлатежей подпрограммы следующая:
- переменной Y присваивается значение количества рабочих дней в месяце путем суммирования содержимого ячеек К1:М1 на рабочем листе Прогноз;
- далее выполнение подпрограммы переходит к циклу Do - Loop. Цикл будет выполняться до тех пор, пока переменная Z не будет равна нулю, о чем говорит оператор Until. В свою очередь, переменная Z равна сумме ячеек столбцов В (2), С (3) и D (4) строки, устанавливаемой переменной Х;
- переменная Х увеличивается с выполнением каждого цикла на единицу. Поэтому переменной Z постепенно присваивается сумма значений ячеек В1:D1, затем ячеек В2:D2 и так далее. Цикл продолжается до тех пор, пока суммы ячеек не будут равны нулю, что говорит о том, что далее ячейки, содержащие значения отсутствуют;
- ячейке G1 присваивается значение, находящееся в ячейке на пересечении строки равной переменной Х и столбца 1: (Х,1). При первом выполнении цикла это будет значение ячейки А1, при втором А2 и так далее с увеличением значения переменной Х на единицу при выполнении каждого цикла. При изменении содержимого ячейки G1 это значение повторяется в ячейках, нижестоящих в столбце G (смотри рисунок 3), а так как это значение текста кода управленческого учета, по которому производится выборка данных, то формулы в ячейках столбца J и K возвращают планируемое значение ежедневных оборотов денежных средств по этому коду управленческого учета. Ежедневное значение равно значению доли запланированных распределенных денежных потоков за эту декаду, деленное на количество рабочих дней в этой декаде;
- выделяется ячейка с координатами (Y, 11). Так как переменная Y равна количеству рабочих дней (их количество в июле 2003 года равно 23 дням), а 11-й столбец - это столбец К, то при расчетах за июль месяц должна быть выделена ячейка К18;
- от активной ячейки (К18) выделяется диапазон ячеек (F1:К18) и копируется в буфер обмена. Выделяемый диапазон определяет код VBA Range(ActiveCell, ActiveCell(-Y + 2, -4)), в котором адрес ячейки F1 определяет относительная адресация ячейки ActiveCell(-Y + 2, -4) относительно ячейки К18. Сдвиг по столбцам влево определяет значение -4, а по строкам - значение переменной -Y, уменьшенное на 2;
- далее переменной Row присваивается значение заполненных ячеек в столбце АА, увеличенное на единицу;
- в столбце АА выделяется ячейка на пересечении строки Row и 27-го столбца (столбец АА), в которую производится вставка значений скопированной области.
Рис.5. Лист Прогноз (2) с отсортированными данными распределенных платежей
На следующем шаге мы закончим изучение этого вопроса.
