VBA – очень полезная вещь. Можно консолидировать данные из многих файлов и обрабатывать большие объемы информации с использованием интересных алгоритмов. Например, макрос Nodupes. Он использует возникающую в процессе выполнения кода ошибку как проверку наличия элемента в формируемой коллекции. Есть макросы, использующие рекурсию для формирования всех возможных комбинаций и перестановок.
Но я бы хотел показать несколько примеров использования стандартных функций листа программы Excel. Достаточно часто приходится городить огород из формул, чтобы вытащить из ячеек нужный текст или числа. Происходит это как правило из-за неверного представления данных. Числа — это числа, текст — это текст, не надо их смешивать. Даты (и время) это числа, с которыми можно и нужно производить вычисления. Не надо путать 14.03.2018 с 14 марта 2018 г. Второе это только пользовательский формат даты [$-F800], то, что мы видим в ячейке, а в строке формул будет 14.03.2018. И хватит об этом.
1. Допустим у вас есть таблица с временами начала и окончания каких-то событий, например, телефонных разговоров.
На рисунке часть таблицы _t2. Необходимо вычислить максимальное одновременное количество событий. Для этого есть такая формула:
=МАКС(МУМНОЖ((_t2[start]>=ТРАНСП(_t2[start]))*(_t2[start]<=ТРАНСП(_t2[end]));СТРОКА(_t2[start])^0))
Или в английской локали
=MAX(MMULT((_t2[start]>=TRANSPOSE(_t2[start]))*(_t2[start]<=TRANSPOSE(_t2[end])),ROW(_t2[start])^0))Формула массивная, ввод подтверждается одновременным нажатием кнопок Control, Shift, Enter (CSE). В строке формул будет видно, что формула в фигурных скобках. МУМНОЖ делает виртуальную матрицу размером число строк таблица на число строк таблицы, в строках которой результаты вычисления, как если бы в строки таблицы была введена следующая формула (для второй строки) =СУММПРОИЗВ(Ч((C$2:C2>A2)))/ =SUMPRODUCT(N((C$2:C2>A2))), и протянуть на всю таблицу.
2. То же самое, но посложнее. Кроме начала и окончания есть количество агрегатов или отдаваемая/получаемая мощность. Необходимо определить максимальную мощность в какой-то момент времени.
Часть таблицы _t1:
И сама формула:
=МАКС(МУМНОЖ((ТРАНСП(_t1[start]*1440)<=СТРОКА(1:1436)-1)*(ТРАНСП(_t1[stop]*1440)>=СТРОКА(1:1436)-1);_t1[power]))
=MAX(MMULT((TRANSPOSE(_t1[start]*1440)<=ROW(1:1436)-1)*(TRANSPOSE(_t1[stop]*1440)>=ROW(1:1436)-1),_t1[power]))Поскольку время — это доля единицы, умножаем значения на количество минут в сутках, чтобы получить целые числа. И второй аргумент МУМНОЖ – столбец значений мощности. В первом случае был сформирован столбец единиц.
3. Слева в таблице представлены суммы, соответствующие определенным интервалам. Необходимо вычислить суммы по месяцам. Предполагается, что суммы распределены равномерно внутри своих интервалов.
В столбце Н первые числа месяцев, в столбце I следующие формулы:
=СУММ(_tis[sum per day]*ЕСЛИОШИБКА((ЕСЛИ(КОНМЕСЯЦА(H3;0)>_tis[end];_tis[end];КОНМЕСЯЦА(H3;0))-ЕСЛИ(H3>_tis[start];H3;_tis[start])+1)^0,5;)^2)
=SUM(_tis[sum per day]*IFERROR((IF(EOMONTH(H3,0)>_tis[end],_tis[end],EOMONTH(H3,0))-IF(H3>_tis[start],H3,_tis[start])+1)^0.5,)^2)Формулы массивные. Можно было бы использовать СУММПРОИЗВ/SUMPRODUCT, но ЕСЛИОШИБКА не работает без массивного ввода. Также, к сожалению, МАКС и МИН не могут сформировать виртуальный массив значений. Поэтому для вычисления частей интервалов, приходящихся на месяц, используется ЕСЛИ. Так как полученные части могут быть отрицательными, искусственно вызывается ошибка (квадратный корень из отрицательного числа приводит к ней). В исходной таблице можно было обойтись без столбцов длительности интервала и дневной суммы. Формула стала бы немного длиннее.
4. Небольшое развлечение. Сколько в году может быть счастливых пятниц?
Количество пятниц:
=СУММПРОИЗВ((ДЕНЬ(СТРОКА(ИНДЕКС(A:A;B2):ИНДЕКС(A:A;C2)))=13)*(ДЕНЬНЕД(СТРОКА(ИНДЕКС(A:A;B2):ИНДЕКС(A:A;C2));2)=5))
=SUMPRODUCT((DAY(ROW(INDEX(A:A,B2):INDEX(A:A,C2)))=13)*(WEEKDAY(ROW(INDEX(A:A,B2):INDEX(A:A,C2)),2)=5))Даты пятниц:
=ЕСЛИОШИБКА(АГРЕГАТ(15;6;СТРОКА(ИНДЕКС($A:$A;$B2):ИНДЕКС($A:$A;$C2))/(ДЕНЬ(СТРОКА(ИНДЕКС($A:$A;$B2):ИНДЕКС(A:A;$C2)))=13)/(ДЕНЬНЕД(СТРОКА(ИНДЕКС($A:$A;$B2):ИНДЕКС($A:$A;$C2));2)=5);СТОЛБЕЦ(A$2));"")
=IFERROR(AGGREGATE(15,6,ROW(INDEX($A:$A,$B2):INDEX($A:$A,$C2))/(DAY(ROW(INDEX($A:$A,$B2):INDEX(A:A,$C2)))=13)/(WEEKDAY(ROW(INDEX($A:$A,$B2):INDEX($A:$A,$C2)),2)=5),COLUMN(A$2)),"")Это очень хорошо (здесь), что первая строка листа соответствует 01.01.1901. Поэтому достаточно легко сформировать массив дат года с помощью ИНДЕКС и СТРОКА и считать только пятницы тринадцатое.
5. Размер процентной ставки, зависящей от суммы, встречается достаточно часто. Если пользоваться ими неправильно, то график полученных сумм будет такой как красный график ниже:
Чтобы этого избежать, необходимо применять ставку только к части суммы, приходящейся на интервал. А для младших интервалов брать фиксированные суммы.
Для таких исходных данных:
Формула:
=ВПР(A3;tbl;2)*(A3-ВПР(A3;tbl;1))+СУММПРОИЗВ(ЕСЛИОШИБКА((tbl[limit]<=A3)*(tbl[limit]-СМЕЩ(tbl[limit];-1;))*СМЕЩ(tbl[rate];-1;);))
=VLOOKUP(A3,tbl,2)*(A3-VLOOKUP(A3,tbl,1))+SUMPRODUCT(IFERROR((tbl[limit]<=A3)*(tbl[limit]-OFFSET(tbl[limit],-1,))*OFFSET(tbl[rate],-1,),))
Формула массивная. В А3 – сумма, для которой нужно вычислить проценты. Исходные данные –tbl.
И, напоследок, кредитная линия с изменяемой процентной ставкой. Формула для расчета суммы процентов в зависимости от дат/сумм траншей/возвратов.
История кредита:
Изменение ставки:
=ЕСЛИ(B1=0;СУММ(ЕСЛИОШИБКА(ПРОСМОТР(_d;_s[[date]:[rate]]);)*ТРАНСП(B5:B16)*ИНДЕКС((_d>ТРАНСП(A5:A16))*(_d<=A2);;)*1/(365+(ОСТАТ(ГОД(_d);4)=0)))-СУММ(ЕСЛИОШИБКА(ПРОСМОТР(_d;_s[[date]:[rate]]);)*ТРАНСП(D5:D16)*ИНДЕКС((_d>ТРАНСП(C5:C16))*(_d<=A2);;)*1/(365+(ОСТАТ(ГОД(_d);4)=0)));)
=IF(B1=0,SUM(IFERROR(LOOKUP(_d,_s[[date]:[rate]]),)*TRANSPOSE(B5:B16)*INDEX((_d>TRANSPOSE(A5:A16))*(_d<=A2),,)*1/(365+(MOD(YEAR(_d),4)=0)))-SUM(IFERROR(LOOKUP(_d,_s[[date]:[rate]]),)*TRANSPOSE(D5:D16)*INDEX((_d>TRANSPOSE(C5:C16))*(_d<=A2),,)*1/(365+(MOD(YEAR(_d),4)=0))),)Формула массивная. Считает проценты по дням, учитывает високосные года.
Спасибо за внимание!
Автор: brodskiy andrey
