Наглядность в сложных формулах

10656 10.12.2017 Скачать пример

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

comfort-formulas1.png

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

Программисты, однако, давно нашли способ удобного представления вложенных друг в друга объектов (процессов, циклов, вычислений). В большистве языков программирования (в том числе и в VBA) для этого успешно используется два основных приема:

  • система отступов слева при написании кода - каждый уровень отступов обозначает свою степень вложенности
  • комментарии для объяснения сложных или неочевидных фрагментов кода

 comfort-formulas2.png

Нечто похожее можно изобразить и с формулами в Excel.

Переносы и отступы

Для разбиения длинной формулы на несколько отдельных строк, например, по отдельным функциям или аргументам, можно использовать сочетание клавиш ALT+Enter, предварительно установив курсор в строке формул в нужное место. Саму строку формул (начиная с версии Excel 2007) можно спокойно увеличить по высоте, потянув за нижний край:

comfort-formulas3.png

Отступы между функциями и аргументами можно смело делать клавишей пробела - это никак не повлияет на формулу и не приведет к ошибкам в вычислениях:

comfort-formulas4.png

Комментарии

Примечания или комментарии к фрагментам формул можно делать с помощью функции Ч (N), которая превращает любой текст в ноль, т.е. никак не повлияет на результат:

comfort-formulas5.png

Если же формула или аргумент должен выдавать не число, а текст, то функцию Ч плюсовать уже нельзя - получим ошибку ЗНАЧ, т.к. нельзя складывать числа и текст. Для комментирования текста вместо нее можно использовать функцию ПОВТОР (REPT). Она, по идее, повторяет заданный текст N-ое количество раз, но N мы можем задать равное нулю и приклеить получившуюся пустую строку к нашей формуле с помощью символа склеивания &:

comfort-formulas6.png

В комплексе, все вышеперечисленное позволяет ощутимо облегчить понимание сложных формул в "тяжелых случаях":

comfort-formulas7.png

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

Ссылки по теме

  


Dyroff
25.04.2018 00:44:37
Действительно, очень крутая штука, часто применяю ее на практике, особенно когда формула длинная. Ведь потом, возвращаясь через время даже к собственноручно написанной формуле, бывает очень трудно разобраться что к чему:)
Ссылка
Людмила
08.08.2018 12:41:33
Очень здорово! Красота должна быть везде. Спасибо!
Ссылка
Станислав Ситников
23.10.2018 17:30:07
Да, это хорошо.
Ссылка
alex po
14.09.2020 13:59:19
Пример не скачивается - перенаправление на эту же страницу
Ссылка
Andrej730
17.10.2020 15:36:05
Сделал VBA макрос, добавляющий отступы для формулы в ячейке.
Sub formula_formatter()
    Dim base_formula As String
    Dim new_formula As String
    Dim indent_level As Integer
    Dim tab_size As Integer
    
    Dim i As Integer
    Dim ch As String
    
    tab_size = 4
    base_formula = Application.Trim(Replace(Selection.formula, Chr(10), " "))
    Debug.Print (base_formula)
    new_formula = ""
    indent_level = 0
    For i = 1 To Len(base_formula)
        ch = Mid(base_formula, i, 1)
        If ch = "," Then
            new_formula = new_formula & "," & Chr(10) & Space(tab_size * indent_level)
        ElseIf ch = "(" Then
            indent_level = indent_level + 1
            new_formula = new_formula & "("
        ElseIf ch = ")" Then
            indent_level = indent_level - 1
            new_formula = new_formula & Chr(10) & Space(tab_size * indent_level) & ")"
        Else
            new_formula = new_formula & ch
        End If
    Next i
    
    Selection.formula = new_formula
End Sub
Ссылка
Андрей Бучак
19.05.2021 09:25:30
Иногда смотрю на свои конструкции, и точно как в начале статьи 
=ЕСЛИ(ЕОШИБКА(ЛЕВСИМВ((ПСТР(ЯЧЕЙКА("filename";A1);ПОИСК("[";ЯЧЕЙКА("filename";A1))+1;ПОИСК("]";ЯЧЕЙКА("filename";A1))-ПОИСК("[";ЯЧЕЙКА("filename";A1))-1));НАЙТИ("_";(ПСТР(ЯЧЕЙКА("filename";A1);ПОИСК("[";ЯЧЕЙКА("filename";A1))+1;ПОИСК("]";ЯЧЕЙКА("filename";A1))-ПОИСК("[";ЯЧЕЙКА("filename";A1))-1)))-1));" ";ЛЕВСИМВ((ПСТР(ЯЧЕЙКА("filename";A1);ПОИСК("[";ЯЧЕЙКА("filename";A1))+1;ПОИСК("]";ЯЧЕЙКА("filename";A1))-ПОИСК("[";ЯЧЕЙКА("filename";A1))-1));НАЙТИ("_";(ПСТР(ЯЧЕЙКА("filename";A1);ПОИСК("[";ЯЧЕЙКА("filename";A1))+1;ПОИСК("]";ЯЧЕЙКА("filename";A1))-ПОИСК("[";ЯЧЕЙКА("filename";A1))-1)))-1))
разобраться в такой "красоте", обычно, может только ее автор. Да и то - если не больше недели прошло
в данном случае прошло больше недели, и я уже не объясню внятно, зачем столько вложений, что  и как без этого не работало, и почему это вообще сейчас работает
:D:D:D
Ссылка
Альберт Иванков
18.03.2023 22:33:33
Спасибо большое Николай!
Не могу понять у меня пример не скачивается из хрома.
Подскажите плиз в чем проблема.
Спасибо.
Ссылка
Наверх