Эта проблема
Вы не можете сделать это с помощью формулы оценки, потому что это не является целью этой функции. Вот почему это называется оценить, это для оценки формул. То, что вы хотите, это какая-то распаковка. Это немного особая необходимость, поэтому она не реализована как инструмент в Excel, но есть решения, если вы создаете некоторые функции / макросы Visual Basic.
Создайте модуль кода VBA (макрос), как вы можете видеть в этом руководстве.
- Нажмите Alt+F11
- Нажмите на
Module во Insert .
- Вставьте код.
Function CellFormula(Target As Range) As String
CellFormula = Target.Formula
End Function
Затем введите в ячейку следующее: =CellFormula(A1)
Это скажет формула ячейки. Единственная проблема с этим кодом, что он работает только для одного уровня. Если вы тоже хотите распаковать содержащиеся в ячейках формулы, вам нужен более сложный код с рекурсией.
Решение
Это был длинный путь, но я создал для вас макрос VBA, который реализует эту функцию. Я не утверждаю, что этот код будет работать для каждой формулы, но он будет работать в большинстве / некоторых из них. Также я не утверждаю, что этот код будет генерировать формулы, которые эквивалентны оригинально введенному коду или будут давать тот же результат, что и оригинал.
Исходный код
Option Explicit
Function isChar(char As String) As Boolean
Select Case char
Case "A" To "Z"
isChar = True
Case Else
isChar = False
End Select
End Function
Function isNumber(char As String, isZero As Boolean) As Boolean
Select Case char
Case "0"
If isZero = True Then
isNumber = True
Else
isNumber = False
End If
Case "1" To "9"
isNumber = True
Case Else
isNumber = False
End Select
End Function
Function CellFormulaExpand(formula As String) As String
Dim result As String
Dim previousResult As String
Dim cell As Range
Dim stringArray() As String
Dim arraySize As Integer
Dim n As Integer
Dim trimmer As String
Dim c As Integer 'character number
Dim chr As String 'current character
Dim tempcell As String 'suspected cell's temporaly result
Dim state As Integer 'state machine's state:
Dim stringSize As Integer
result = formula
previousResult = result
state = 0
stringSize = 0
For c = 0 To Len(formula) Step 1
chr = Mid(formula, c + 1, 1)
Select Case state
Case 0
If isChar(chr) Then
state = 1
tempcell = tempcell & chr
ElseIf chr = "$" Then
state = 5
tempcell = tempcell & chr
Else
state = 0
tempcell = ""
End If
Case 1
If isNumber(chr, False) Then
state = 4
tempcell = tempcell & chr
ElseIf isChar(chr) Then
state = 2
tempcell = tempcell & chr
ElseIf chr = "$" Then
state = 6
tempcell = tempcell & chr
Else
state = 0
tempcell = ""
End If
Case 2
If isNumber(chr, False) Then
state = 4
tempcell = tempcell + chr
ElseIf isChar(chr) Then
state = 3
tempcell = tempcell + chr
ElseIf chr = "$" Then
state = 6
tempcell = tempcell + chr
Else
state = 0
tempcell = ""
End If
Case 3
If isNumber(chr, False) Then
state = 4
tempcell = tempcell + chr
ElseIf chr = "$" Then
state = 6
tempcell = tempcell + chr
Else
state = 0
tempcell = ""
End If
Case 4
If isNumber(chr, True) Then
state = 4
tempcell = tempcell + chr
Else
state = 0
stringSize = stringSize + 1
ReDim Preserve stringArray(stringSize - 1)
stringArray(stringSize - 1) = tempcell
tempcell = ""
End If
Case 5
If isChar(chr) Then
state = 1
tempcell = tempcell + chr
Else
state = 0
tempcell = ""
End If
Case 6
If isNumber(chr, False) Then
state = 4
tempcell = tempcell + chr
Else
state = 0
tempcell = ""
End If
Case Else
state = 0
tempcell = ""
End Select
Next c
If stringSize = 0 Then
CellFormulaExpand = result
Else
arraySize = UBound(stringArray)
For n = 0 To arraySize Step 1
Set cell = Range(stringArray(n))
If Mid(cell.formula, 1, 1) = "=" Then
trimmer = Mid(cell.formula, 2, Len(cell.formula) - 1)
If trimmer <> "" Then
result = Replace(result, stringArray(n), trimmer)
End If
End If
Next
If previousResult <> result Then
result = CellFormulaExpand(result)
End If
End If
CellFormulaExpand = result
End Function
Function CellFormula(rng As Range) As String
CellFormula = CellFormulaExpand(rng.formula)
End Function
Чтобы заставить его работать, просто создайте макрос (как я описал в начале ответа) и скопируйте код. После этого вы можете использовать его с =CellFormula(A1) где A1 может быть любой вид ячейки 1x1.
Случаи это работает
Я создал несколько примеров, чтобы вы могли увидеть это в действии. В этом случае я демонстрирую использование со строками. Вы можете видеть, что это работает отлично. Единственная небольшая ошибка в том, что алгоритм изменяет точки с запятой на запятые. После их замены (как я сделал в этом примере) вы получите правильный вывод.
Здесь вы можете увидеть, как это работает с числами. Теперь мы сталкиваемся с первой проблемой: алгоритм не заботится о последовательности математических операций, поэтому красное число равно 6, когда оно должно быть 10. Если мы поместим чувствительные операции (такие как сложение и вычитание) в круглые скобки, то заданная формула, введенная назад, даст тот же результат, что вы можете видеть в зеленом числе внизу, которое говорит 10.
Случаи это не работает
Этот алгоритм не идеален. Я только попытался реализовать наиболее распространенные варианты использования, поэтому его можно улучшить, добавив больше функций, которые обрабатывают другие случаи, например диапазоны.
Как вы можете видеть в этом примере, я использовал SUM() с диапазоном в качестве параметра. Поскольку алгоритм расшифровывает содержимое ячеек сверху вниз, он начинается с замены параметров SUM() не чем-либо другим. Поэтому : остается на своем месте, в то время как вокруг него все заменяется, поэтому рядом с ним заменяются новые ячейки, которые изменят его значение. Таким образом, вывод будет неправильным. Так что в этом случае вы можете использовать этот макрос только для изучения исходной формулы.
