Как переписать формулы Excel в читабельном виде?

Ответ 1

В качестве примера, использующего вспомогательные столбцы, вы можете сократить формулу следующим образом:

[A1] =VLOOKUP(F16,$M$36:$N$41,2,FALSE)

[B1] =HEX2DEC(W$10)

[C1] =HEX2DEC(W16)

[D1] =HEX2DEC(W17)

то большая формула сокращается до

=IF(OR(ISERROR(G16),ISERROR(G17)),X16,IF(OR(G16="xxx",G16="yyy",G16="zzz"),Y16,IF(G16="333","N\A",IF(G17="333",Z16,IF(D17="",IF((B1-C1)/A1<0,0,(B1-C1)/A1), IF((D1-C1)/A1<0,0,(D1-C1)/A1))))))

Это особенно эффективно при использовании энергозависимых функций, таких как DATE или NOW, которые вы не хотите пересчитывать для каждой ячейки, когда он имеет тот же результат.

Является ли это более читаемым, возможно, нет, но вы можете пометить заголовки столбцов соответствующими комментариями

Ответ 2

Вы можете использовать Alt + Enter на панели формул, чтобы сделать вашу формулу многострочной. К сожалению, никаких вкладок не существует, поэтому становится утомительно создавать и редактировать. См. Также

http://www.dailydoseofexcel.com/archives/2005/04/01/excel-formula-formatter/

Ответ 3

Именование некоторых из ячеек, на которые вы ссылаетесь, может сделать все более удобочитаемым

Ответ 4

FormulaDesk - это бесплатная надстройка Excel, которая делает сложные формулы более читабельными и понятными без необходимости переписывать их. Это упрощает создание, редактирование, отладку и понимание формул. Он имеет два режима: "Редактировать вид" и "Исследуйте вид", который можно переключить.

• "Редактировать представление - это расширенный редактор формул с Intellisense и т.д. Он форматирует по мере ввода, вертикально компенсируя вложенные элементы для ясности и понятности.

• В представлении "Обзор" представлена ​​формула простым способом вложенности/свертывания с простейшим представлением верхнего уровня вашей формулы вначале, но вы можете развернуть до вложенных выражений. Это позволяет вам быстро понять, как/почему он возвращает текущий результат. Нажмите на зеленые полосы (свернутые результаты), чтобы развернуть. Кроме того, нажмите кнопку "Развернуть все", "Свернуть все".

• В обоих представлениях вы можете перемещаться по различным элементам (например: функции, параметры и т.д.), чтобы увидеть всплывающее окно с дополнительной информацией, например определение/описание, текущее значение и т.д. Наведение курсора на диапазон отображает текущее значение например.

• Существует немало других возможностей.

[Раскрытие: я являюсь автором FormulaDesk]

Ответ 5

Комбинация вспомогательных столбцов и названных диапазонов сделает эту формулу довольно простой.

На следующем изображении вы можете увидеть, как именованные диапазоны могут разгладить формулу:

Обратите внимание, что "цены" - это имя для диапазона A2: A7 и "inflated_prices" - это имя для B2: B7.

Заметьте также, что имена являются интеллектуальными: sum(prices) будет суммировать весь диапазон, тогда как =+prices*2 в B2 разрешается до =+A2*2, =+prices*2 в B3 разрешается до =+A3*2 и т.д.

Ответ 6

Вы можете существенно упростить формулу, сохраняя при этом единственную формулу. Вы повторяете почти то же выражение 4 раза с частью HEX2DEC/VLOOKUP, которая может быть сведена к одному экземпляру, если вы признаете, что этот

= ЕСЛИ (формула < 0,0, формула)

..... эквивалентно

= MAX (0, формула)

[для числовых результатов формулы]

и если вы вложите выражение IF (D17 = ""..... в основную формулу, то есть эту версию

=IF(ISERROR(G16&G17),X16,IF(OR(G16={"xxx","yyy","zzz"}), Y16,IF(G16="333","N\A",IF(G17="333",Z16,MAX(0,(HEX2DEC(IF(D17="",W$10,W17))-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,0))))))

Ответ 7

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

Function Magic(d17 As Range _
                , f16 As Range _
                , g16 As Range _
                , g17 As Range _
                , w10 As Range _
                , w16 As Range _
                , w17 As Range _
                , x16 As Range _
                , y16 As Range _
                , z16 As Range _
                , m36 As Range) As Variant


    Dim a As Variant
    Dim b As Variant

    If IsError(g16.Value) Or IsError(g17.Value) Then
        Magic = x16.Value
        Exit Function
    End If

    If g16.Value = "xxx" Or g16.Value = "yyy" Or g16.Value = "zzz" Then
        Magic = y16.Value
        Exit Function
    End If

    If g16.Value = "333" Then
        Magic = "N\A"
        Exit Function
    End If

    If g17.Value = "333" Then
        Magic = z16.Value
        Exit Function
    End If

    If d17.Value = "" Then
        a = Application.WorksheetFunction.Hex2Dec(w10.Value) _
                - Application.WorksheetFunction.Hex2Dec(w16.Value)
        a = a / Application.WorksheetFunction.VLookup(f16.Value, m36, 2, False)
        If a < 0 Then
            Magic = 0
            Exit Function
        Else
            Magic = a
            Exit Function
        End If
    Else
        b = Application.WorksheetFunction.Hex2Dec(w17.Value) _
                - Application.WorksheetFunction.Hex2Dec(w16.Value)
        b = b / Application.WorksheetFunction.VLookup(f16.Value, m36, 2, False)
        If b < 0 Then
            Magic = 0
            Exit Function
        Else
            Magic = b
            Exit Function
        End If
    End If
End Function

Чтобы было проще следовать логике формулы (и я не знал, что представляют собой ячейки), я назвал переменные для ссылок на ячейки. Вы захотите переименовать их в нечто значимое. Код принадлежит модулю.

Ответ 8

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

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

Но, не зная цель формулы - и книги, в которой он сидит, можно предложить только столько советов.

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

Ответ 9

FormulaDesk работает только с окнами.

В дополнение к FormulaDesk выше:

Здесь ссылка на текущую бета-версию FastExcel 3. Но они, похоже, существуют уже более десяти лет. $29 баксов. Я думаю, только окна. Сайт не ясен.

http://fastexcel.wordpress.com/2014/04/28/making-sense-of-complex-formulas-an-indenting-viewer-editer/

Аналогичная функциональность от Precision Calc. $12 Имеет бесплатную версию Nagware, если вам это нужно только время от времени. Только Windows.

http://precisioncalc.com/tf/what_is_the_formulator.html