Excel-将字符串转换为公式 [英] Excel - Convert String to a formula

问题描述

我有一个通过一系列串联字符串生成的公式.可以通过一个示例对此进行更好的解释:

I have a formula that is generated via a series of concatenated strings. This is better explained with an example:

打开一个新的Excel工作簿

open a new Excel workbook

 A1 = 5

 C1 = 5 & "+A1" [A1 is seen as text. C1 will contain "5+A1" ]

我希望E1包含作为公式计算的C1的结果，因此基本上在此示例10中.

I would like E1 to contain the result of C1 evaluated as a formula, so basically in this example 10.

我尝试使用INDIRECT(C1)，但是没有用.

I tried to use INDIRECT(C1) but it did not work.

更新:它必须与自定义"单元格名称一起使用.因此，例如，如果我将名称"mycell"分配给A1，则字段C1将包含"5 + mycell"

UPDATE: It must work with Custom cell names. so if for example i assign the name "mycell" to A1, then the field C1 will contain "5+mycell"

更新2:我的真实数据:

UPDATE 2: my real data:

第1页，单元格Y20包含以下内容:

sheet 1, cell Y20 contains this:

-0,0000014369849267*Drehzahl^2 + 0,0401449351560780*Drehzahl - 32,5068828005099000

第2页，单元格J7包含以下内容:

sheet 2, cell J7 contains this:

= 'Sheet 1'!Y20

第2页，单元格J8包含以下内容:

sheet 2, cell J8 contains this:

= eval(J7)

第2页，单元格C7重命名为Drezahl

sheet 2, cell C7 is renamed as Drezahl

更新3:不适用于我的公式:

Update 3: a formula that is not working for me:

它需要4个自定义的重命名单元格:"Drezahl"(值= 3550)Kondtemp(45)Saugtemp(-45)Schlitten(100)计算结果必须约为91,17

it requires 4 custom renamed cells: "Drezahl" (value=3550) Kondtemp (45) Saugtemp (-45) Schlitten(100) the result of the calc must be about 91,17

((((-0,0000014369849267*Drehzahl^2 + 0,0401449351560780*Drehzahl - 32,5068828005099000) + ((8,95609756097562+(18,1/2050)*Drehzahl))*(1/25)*(45-Kondtemp))*((0,0000262088992942*Saugtemp^3 + 0,0050210402289170*Saugtemp^2 + 0,3711985619535020*Saugtemp + 9,9227907759641600)*((0,361075074412964)*(3550/Drehzahl)-0,361075074412964+1))*((((100-50)/(91,4-46,3))*((0,014285714285714*Schlitten^2 - 1,262285714285720*Schlitten + 74,214285714286400)-46,3)+50)-50))/50)+((0,0000063790283394*Drehzahl^2 - 0,0103039106823734*Drehzahl + 2,6771661029208000)*(-0,0002159827213823*Kondtemp^2 - 0,0034865782165998*Kondtemp + 1,5954952175254600))*(((0,000000003885345*Saugtemp^6 + 0,000000666998414*Saugtemp^5 + 0,000042907134551*Saugtemp^4 + 0,001268740583850*Saugtemp^3 + 0,020179866978636*Saugtemp^2 + 0,418860651690801*Saugtemp + 9,465861006018070)*((((7,68293017999336)/(-2050)*(Drehzahl-3550)+1)-1)/(45)*(Saugtemp+45)+1)))*(1/-50*((((100-50)/(91,4-46,3))*((0,014285714285714*Schlitten^2 - 1,262285714285720*Schlitten + 74,214285714286400)-46,3)+50)-100))`

推荐答案

您可以在工作簿中添加非常简单的用户定义函数:

You can add very simple user defined function to your workbook:

Function eval(str As String)
    Application.Volatile
    eval = Evaluate(Evaluate(Replace(str, ",", ".")))
End Function

并在E1中这样调用:=eval(C1)

注释:

1)对于C1中的数据存储类似不带=符号的文本:5 & "A1"的情况，我使用了双Evaluate.如果您的数据以=符号存储:=5 & "A1"，则可以使用eval = Evaluate(str)或保留eval = Evaluate(Evaluate(str))-它不会对齐

1) I've used double Evaluate for the case when data in C1 stores like text without = sign : 5 & "A1". If your data stored with = sign: =5 & "A1", you can use eval = Evaluate(str) or leave eval = Evaluate(Evaluate(str)) - it doesen't metter

2)由于VBA需要.作为分隔符，所以我使用了Replace函数

2) since VBA needs . as delimeter , I've used Replace function

新更新:

但是，Evaluate只能与小于或等于255个字符的字符串一起使用. 在这种情况下，您可以使用以下UDF:

But, Evaluate can operate only with strings less or equal 255 characters.. In that case you can use following UDF:

Public app As Excel.Application
Public wb As Workbook

Function getValue(formulaString As String)
    Application.Volatile
    Application.DisplayAlerts = False

    'set default falue to #REF..if we'd get normal value - we'll change getValue to it'
    getValue = CVErr(xlErrRef)

    getValue = Evaluate(Evaluate(Replace(formulaString, ",", ".")))
    If Not IsError(getValue) Then
    Exit Function
    End If

    'if we appear here - second WB is closed...'
    On Error GoTo ErrHandler

    If app Is Nothing Then Set app = New Excel.Application
    If wb Is Nothing Then Set wb = app.Workbooks.Open(ThisWorkbook.FullName)
    With wb.Sheets(ThisWorkbook.ActiveSheet.Name)
        With .Range(Application.ThisCell.Address)
            .FormulaLocal = IIf(Left(formulaString, 1) <> "=", "=" & formulaString, formulaString)
            .Calculate
            getValue = .Value
        End With
    End With

ErrHandler:
    Application.DisplayAlerts = True

End Function

最后一点:将以下代码添加到ThisWorkbook模块:

and last thing: add following code to the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   If Not wb Is Nothing Then wb.Close False
   If Not app Is Nothing Then app.Quit
   Set app = Nothing
   Set wb = Nothing
End Sub

这篇关于Excel-将字符串转换为公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！