Excel сложные формулы vba

Итог: ознакомьтесь с 3 советами по написанию и созданию формул в макросах VBA с помощью этой статьи и видео.

Уровень мастерства: Средний

Автоматизировать написание формул

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

Поначалу написание формул в VBA может быть немного сложнее,
поэтому вот три совета, которые помогут сэкономить время и упростить процесс.

Совет № 1: Свойство Formula

Свойство Formula является членом объекта Range в VBA. Мы можем использовать его для установки / создания формулы для отдельной ячейки или диапазона ячеек.

Есть несколько требований к значению формулы, которые мы устанавливаем с помощью свойства Formula:

1. Формула представляет собой строку текста, заключенную в кавычки. Значение формулы должно начинаться и заканчиваться кавычками.
2. Строка формулы должна начинаться со знака равенства = после первой кавычки.

Вот простой пример формулы в макросе.

Sub Formula_Property()

  ' Формула представляет собой строку текста, заключенную в кавычки
  ' Начинается со знака =
  Range("B10").Formula = "=SUM(B4:B9)"

End Sub

Свойство Formula также можно использовать для чтения существующей формулы в ячейке.

Совет № 2: Используйте Macro Recorder

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

Вот шаги по созданию кода свойства формулы с помощью средства записи макросов.

1. Включите средство записи макросов (вкладка «Разработчик»> «Запись макроса»)
2. Введите формулу или отредактируйте существующую формулу.
3. Нажмите Enter, чтобы ввести формулу.
4. Код создается в макросе.

Если ваша формула содержит кавычки или символы амперсанда, макрос записи будет учитывать это. Он создает все подстроки и правильно упаковывает все в кавычки. Вот пример.

Sub Macro10()
' Используйте средство записи макросов для создания кода для сложных формул с
' специальны символы и относительные ссылки

  ActiveCell.FormulaR1C1 = "=""Total Sales: "" & TEXT(R[-5]C,""$#,###"")"
    
End Sub

Совет № 3: Нотация формулы стиля R1C1

Если вы используете средство записи макросов для формул, вы
заметите, что он создает код со свойством FormulaR1C1.

Нотация стиля R1C1 позволяет нам создавать как относительные (A1), абсолютные ($A$1), так и смешанные ($A1, A$1) ссылки в нашем макрокоде.

R1C1 обозначает строки и столбцы.

Относительные ссылки

Для относительных ссылок мы указываем количество строк и
столбцов, которые мы хотим сместить от ячейки, в которой находится формула.
Количество строк и столбцов указывается в квадратных скобках.

Следующее создаст ссылку на ячейку, которая на 3 строки выше
и на 2 строки справа от ячейки, содержащей формулу.

Отрицательные числа идут вверх по строкам и столбцам слева.

Положительные числа идут вниз по строкам и столбцам справа.

Абсолютные ссылки

Мы также можем использовать нотацию R1C1 для абсолютных ссылок. Обычно это выглядит как $A$2.

Для абсолютных ссылок мы НЕ используем квадратные скобки. Следующее создаст прямую ссылку на ячейку $A$2, строка 2, столбец 1

При создании смешанных ссылок относительный номер строки или
столбца будет зависеть от того, в какой ячейке находится формула.

Проще всего использовать макро-рекордер, чтобы понять это.

Свойство FormulaR1C1 и свойство формулы

Свойство FormulaR1C1 считывает нотацию R1C1 и создает
правильные ссылки в ячейках. Если вы используете обычное свойство Formula с
нотацией R1C1, то VBA попытается вставить эти буквы в формулу, что, вероятно,
приведет к ошибке формулы.

Поэтому используйте свойство Formula, если ваш код содержит
ссылки на ячейки ($ A $ 1), свойство FormulaR1C1, когда вам нужны относительные
ссылки, которые применяются к нескольким ячейкам или зависят от того, где
введена формула.

Если ваша электронная таблица изменяется в зависимости от
условий вне вашего контроля, таких как новые столбцы или строки данных,
импортируемые из источника данных, то относительные ссылки и нотация стиля
R1C1, вероятно, будут наилучшими.

Я надеюсь, что эти советы помогут. Пожалуйста, оставьте
комментарий ниже с вопросами или предложениями.

Время на прочтение
4 мин

Количество просмотров 28K

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))),)

Формула массивная. Считает проценты по дням, учитывает високосные года.

Спасибо за внимание!

This tutorial will teach you how to create cell formulas using VBA.

Formulas in VBA

Using VBA, you can write formulas directly to Ranges or Cells in Excel. It looks like this:

Sub Formula_Example()

    'Assign a hard-coded formula to a single cell
    Range("b3").Formula = "=b1+b2"
    
    'Assign a flexible formula to a range of cells
    Range("d1:d100").FormulaR1C1 = "=RC2+RC3"

End Sub

There are two Range properties you will need to know:

• .Formula – Creates an exact formula (hard-coded cell references). Good for adding a formula to a single cell.
• .FormulaR1C1 – Creates a flexible formula. Good for adding formulas to a range of cells where cell references should change.

For simple formulas, it’s fine to use the .Formula Property.  However, for everything else, we recommend using the Macro Recorder…

Macro Recorder and Cell Formulas

The Macro Recorder is our go-to tool for writing cell formulas with VBA.  You can simply:

• Start recording
• Type the formula (with relative / absolute references as needed) into the cell & press enter
• Stop recording
• Open VBA and review the formula, adapting as needed and copying+pasting the code where needed.

I find it’s much easier to enter a formula into a cell than to type the corresponding formula in VBA.

Notice a couple of things:

• The Macro Recorder will always use the .FormulaR1C1 property
• The Macro Recorder recognizes Absolute vs. Relative Cell References

VBA FormulaR1C1 Property

The FormulaR1C1 property uses R1C1-style cell referencing (as opposed to the standard A1-style you are accustomed to seeing in Excel).

Here are some examples:

Sub FormulaR1C1_Examples()

    'Reference D5 (Absolute)
    '=$D$5
    Range("a1").FormulaR1C1 = "=R5C4"
    
    'Reference D5 (Relative) from cell A1
    '=D5
    Range("a1").FormulaR1C1 = "=R[4]C[3]"
    
    'Reference D5 (Absolute Row, Relative Column) from cell A1
    '=D$5
    Range("a1").FormulaR1C1 = "=R5C[3]"
    
    'Reference D5 (Relative Row, Absolute Column) from cell A1
    '=$D5
    Range("a1").FormulaR1C1 = "=R[4]C4"

End Sub

Notice that the R1C1-style cell referencing allows you to set absolute or relative references.

Absolute References

In standard A1 notation an absolute reference looks like this: “=$C$2”.  In R1C1 notation it looks like this: “=R2C3”.

To create an Absolute cell reference using R1C1-style type:

• R + Row number
• C + Column number

Example:  R2C3 would represent cell $C$2 (C is the 3rd column).

    'Reference D5 (Absolute)
    '=$D$5
    Range("a1").FormulaR1C1 = "=R5C4"

Relative References

Relative cell references are cell references that “move” when the formula is moved.

In standard A1 notation they look like this: “=C2”. In R1C1 notation, you use brackets [] to offset the cell reference from the current cell.

Example: Entering formula “=R[1]C[1]” in cell B3 would reference cell D4 (the cell 1 row below and 1 column to the right of the formula cell).

Use negative numbers to reference cells above or to the left of the current cell.

    'Reference D5 (Relative) from cell A1
    '=D5
    Range("a1").FormulaR1C1 = "=R[4]C[3]"

Mixed References

Cell references can be partially relative and partially absolute.  Example:

    'Reference D5 (Relative Row, Absolute Column) from cell A1
    '=$D5
    Range("a1").FormulaR1C1 = "=R[4]C4"

VBA Formula Property

When setting formulas with the .Formula Property you will always use A1-style notation.  You enter the formula just like you would in an Excel cell, except surrounded by quotations:

    'Assign a hard-coded formula to a single cell
    Range("b3").Formula = "=b1+b2"

VBA Formula Tips

Formula With Variable

When working with Formulas in VBA, it’s very common to want to use variables within the cell formulas.  To use variables, you use & to combine the variables with the rest of the formula string. Example:

Sub Formula_Variable()
    Dim colNum As Long
    colNum = 4

    Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNum

End Sub

VBA Programming | Code Generator does work for you!

Formula Quotations

If you need to add a quotation (“) within a formula, enter the quotation twice (“”):

Sub Macro2()
    Range("B3").FormulaR1C1 = "=TEXT(RC[-1],""mm/dd/yyyy"")"
End Sub

A single quotation (“) signifies to VBA the end of a string of text. Whereas a double quotation (“”) is treated like a quotation within the string of text.

Similarly, use 3 quotation marks (“””) to surround a string with a quotation mark (“)

MsgBox """Use 3 to surround a string with quotes"""
' This will print <"Use 3 to surround a string with quotes"> immediate window

Assign Cell Formula to String Variable

We can read the formula in a given cell or range and assign it to a string variable:

'Assign Cell Formula to Variable
Dim strFormula as String
strFormula = Range("B1").Formula

Different Ways to Add Formulas to a Cell

Here are a few more examples for how to assign a formula to a cell:

1. Directly Assign Formula
2. Define a String Variable Containing the Formula
3. Use Variables to Create Formula

Sub MoreFormulaExamples ()
' Alternate ways to add SUM formula
' to cell B1
'
  Dim strFormula as String
  Dim cell as Range
  dim fromRow as long, toRow as long

  Set cell = Range("B1")

  ' Directly assigning a String
  cell.Formula = "=SUM(A1:A10)"

  ' Storing string to a variable
  ' and assigning to "Formula" property
  strFormula = "=SUM(A1:A10)"
  cell.Formula = strFormula

  ' Using variables to build a string 
  ' and assigning it to "Formula" property
  fromRow = 1
  toRow   = 10
  strFormula = "=SUM(A" & fromValue & ":A" & toValue & ")
  cell.Formula = strFormula
End Sub

Refresh Formulas

As a reminder, to refresh formulas, you can use the Calculate command:

Calculate

To refresh single formula, range, or entire worksheet use .Calculate instead:

Sheets("Sheet1").Range("a1:a10").Calculate