У компании Джеффри есть ряд отчетов, в которых используется большое количество формул массива CSE (Ctrl + Shift + Enter). Когда кто-то забывает удерживать Ctrl и Shift при нажатии Enter, полученные формулы не соответствуют правильному ответу. Проверять каждую ячейку и искать скобки \ {} утомительно и отнимает много времени. Джеффри задается вопросом, есть ли быстрый способ найти «недостающие скобки» или поднять флаг ошибки, если Ctrl + Shift + Enter не нажимается, а должно быть?

В Excel нет внутреннего или шаблонного метода для этого. Это означает, что вам нужно обратиться к решению, основанному на макросе.

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

Sub MakeCSE1()

Dim rCell As Range

For Each rCell In Selection         rCell.FormulaArray = rCell.Formula     Next rCell End Sub

Этот макрос предполагает, что вы выберете ячейки, которые нужно «преобразовать», прежде чем запускать макрос. При желании вы можете определить диапазон ячеек (дать диапазону имя), а затем запустить аналогичный макрос, который всегда выполняет свою работу с этим диапазоном.

Sub MakeCSE2()

Dim rng As Range     Dim rCell As Range     Dim rArea As Range

Set rng = Range("CSERange")

For Each rArea In rng.Areas         For Each rCell In rArea.Cells             If rCell.HasArray = False Then                 rCell.FormulaArray = rCell.Formula             End If         Next rCell     Next rArea End Sub

Этот макрос ищет диапазон с именем CSERange, а затем проверяет каждую ячейку в этом диапазоне. Если он не содержит формулы массива, формула преобразуется в формулу массива.

Обратите внимание на использование свойства HasArray, чтобы проверить, содержит ли ячейка формулу массива. Это свойство может быть полезным и в других отношениях. Например, вы можете создать простую пользовательскую функцию, например такую:

Function NoCellArray1(rng As Range) As Boolean     NoCellArray1 = Not rng.HasArray End Function

Эта функция возвращает True, если указанная ячейка не содержит формулы массива. Если он есть, возвращается False. Затем вы можете использовать эту функцию как основу для условного формата. Все, что вам нужно сделать, это создать формат, который будет использовать его таким образом:

=NoCellArray1(A5)

Поскольку NoCellArray возвращает True, если ячейка не содержит формулы массива, ваш условный формат может установить красный цвет ячейки или установить какой-либо другой видимый признак того, что ячейка не имеет необходимой формулы массива. Вы также можете использовать следующую функцию для выполнения той же задачи:

Function NoCellArray2(rng As Range) As Boolean     NoCellArray2 = (Evaluate(rng.FormulaArray) <> rng.Value)

End Function

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

+N("{")

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Right(Selection.FormulaArray, 5) = "(""{"")" Then         ActiveCell.Select         Selection.FormulaArray = ActiveCell.Formula     End If End Sub

Обратите внимание, что обработчик проверяет, заканчивается ли формула на («\ {«), и, если это так, принудительно обрабатывает формулу как формулу массива. Самое замечательное в этом подходе то, что вам больше никогда не придется нажимать Ctrl + Shift + Enter на листе — обработчик событий позаботится об этом за вас. Если в какой-то момент вы захотите преобразовать формулу обратно в обычную (без массива) версию, просто измените формулу, чтобы она не включала + N («\ {«).

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (473) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Checking_for_Proper_Entry_of_Array_Formulas [Проверка правильности ввода формул массива].