Не знаю, на сколько полезной для Вас может стать эта функция. Я пользуюсь ею часто и очень ею доволен. Итак опишем проблему. Как правило, ОДБ содержит данные об остатках только за рабочие дни, так как в выходные операционный день попросту не открывается. Когда необходимо вычислить средний остаток по счетам или средний баланс за месяц, возникает некоторое несоответствие. Традиционно проценты по привлеченным и размещенным средствам начисляются за календарные дни. Если брать для расчета среднюю арифметическую, то результат будет приближен к реальности, но неточен.
Раньше такую задачу я решал "рабоче-крестьянским" методом. В таблицу, содержащую поле дат и поле остатков руками вставлялись недостающие (выходные) дни. После этого недостающие данные об остатков копировались из имеющихся и вычислялась средняя. В принципе весь этот процесс при небольшой тренировке занимает не так уж и много времени. Но, во-первых, такой подход чреват техническими ошибками. А, во-вторых, ведь мы же с Вами автоматизаторы и должны автоматизировать все что можно ;) Ну а если, без шуток, подумайте каково вычислять среднюю по данным за год.
На единственном листе этой рабочей книги приведена таблица остатков на счетах клиентов и на корреспондентском счете банка. Обратите внимание, что данные за некоторые календарные дни пропущены. Остатки по счетам зафиксированы на утро дня, а остатки по корреспондентскому счету на вечер. Разница в том, что если в таблице указанны остатки на утро, то остатки на утро выходных дней будут равняться остатку на следующий рабочий день. Если же указанны остатки на вечер, то в выходные дни остатки будут равняться остатку на вечер предыдущего рабочего дня. Как правило, остатки приводятся на утро операционного дня, но мы рассмотрели две ситуации для демонстрации работы функции.
Синтаксис функции следующий: =ChronAverange (<Диапазон дат>; <Диапазон значений>; [0 - если остаток на вечер])
Диапазон дат - столбец, содержащий даты. Диапазон значений - столбец, содержащий значения (остатки). Третий параметр необязательный. Тут нужно поставить "0", если указанны остатки на вечер. Данные могут располагаться только в столбцах. При этом столбцы должны быть одинаковой высоты.
Для использования этой функции в собственных Excel-документах нужно скопировать из данного примера модуль (в редакторе VBA) в собственную книгу. Если вы не знаете как это делается, можно готовить собственные документы на базе этого примера, то есть сохранять его под другим именем и после редактировать его как Вам заблагорассудится.
ОГРАНИЧЕНИЯ!
Обратите внимание, что в таблице присутствуют данные на 31.08.2002, хотя это выходной! Функция не может понять, какой именно период вы хотите учитывать. Расчет ведется от первой до последней указанной даты, а недостающие данные вставляет. Так как остатки по корреспондентским счетам указанны на вечер, правильный расчет должен учитывать все даты с 01.08 по 31.08. Если указать, как в случае с остатками на текущих счетах, период по 01.09, функция посчитает один лишний день. В таких случаях последний рабочий день периода (или первый календарный день следующего периода при остатках на утро), к сожалению, нужно вставлять самостоятельно.
Например, если попытаться рассчитать сумму доходов по кредиту овердрафт исходя из среднего арифметического остатка и установленной по счету ставке, сумма не будет соответствовать фактически начисленным доходом. Зачастую такой погрешностью можно пренебречь, но многие потребители финансового анализа, особенно начальство, любят и уважают точность.
Для решения этой задачки, я сделал небольшую функцию в VBA. Напомню, что пользователь Excel может создавать собственные функции, встраивая их в модули проекта. Такая функция используется на листе точно также, как и любая другая стандартная функция.
Загрузите рабочую книгу MS Excel по этой ссылке.
В данной таблице вычисляется средний остаток за август. Для остатков на текущих счетах учитывается период с 02.08 по 01.09.2002. Для остатков по корсчету - с 01.08 по 31.08.2002. В первой итоговой строке вычисляется простая средняя. Во второй - средняя с учетом выходных. Здесь используется пользовательская функция ChronAverange.
