您好，以下是关于如何使用VBA或公式来查找“k”和“m”变量的巧妙方法：
对于“kx+m字符串”，有几种不同的情况，例如：
312*x+12
12+x*2
-4-x
等等。我相信我可以通过在Excel中编写非常复杂的公式来解决这个问题，但我想也许已经有人解决了这个和类似的问题。以下是迄今为止我最好的尝试，但它还不能处理所有情况（比如当kx+m字符串中有两个减号时）:
=TRIM(IF(NOT(ISERROR(SEARCH("~+";F5)));
IF(SEARCH("~+";F5)>SEARCH("~*";F5);RIGHT(F5;LEN(F5)-SEARCH("~+";F5));LEFT(F5;SEARCH("~+";F5)-1));
IF(NOT(ISERROR(SEARCH("~-";F5)));
IF(SEARCH("~-";F5)>SEARCH("~*";F5);RIGHT(F5;LEN(F5)-SEARCH("~-";F5)+1);LEFT(F5;SEARCH("~*";F5)-1));"")))
LINEST函数。如有需要，请替换StrFunc。Sub Extract()
Dim strFunc As String
Dim X(1 To 2) As Variant
Dim Y(1 To 2) As Variant
Dim C As Variant
X(1) = 0
X(2) = 100
strFunc = "312*x+12"
'strFunc = "12+x*2 "
'strFunc = "-4-X"
Y(1) = Evaluate(Replace(LCase$(strFunc), "x", X(1)))
Y(2) = Evaluate(Replace(LCase$(strFunc), "x", X(2)))
C = Application.WorksheetFunction.LinEst(Y, X)
MsgBox "K is " & C(1) & vbNewLine & "M is " & C(2)
End Sub
Option Explicit
Sub Sample()
Dim StrCheck As String
Dim posStar As Long, posBrk As Long, pos As Long, i As Long
Dim strK As String, strM As String
Dim MyArray(6) As String
MyArray(0) = "-k*(-x)+(-m)*(-2)"
MyArray(1) = "-k*x+(-m)*(-2)"
MyArray(2) = "-k(x)+(-m)*(-2)"
MyArray(3) = "-k(x)+(-m)(-2)"
MyArray(4) = "-kx+m"
MyArray(5) = "kx+m"
MyArray(6) = "k(x)+m"
For i = 0 To 6
StrCheck = MyArray(i)
Select Case Left(Trim(StrCheck), 1)
Case "+", "-"
posBrk = InStr(2, StrCheck, "(")
posStar = InStr(2, StrCheck, "*")
If posBrk > posStar Then '<~~ "-k*(-x)+(-m)*(-2)"
pos = InStr(2, StrCheck, "*")
If pos <> 0 Then
strK = Mid(StrCheck, 1, pos - 1)
Else
strK = Mid(StrCheck, 1, posBrk - 1)
End If
ElseIf posBrk < posStar Then '<~~ "-k(-x)+(-m)*(-2)"
pos = InStr(2, StrCheck, "(")
strK = Mid(StrCheck, 1, pos - 1)
Else '<~~ "-kx+m"
'~~> In such a case I am assuming that you will never use
'~~> a >=2 letter variable
strK = Mid(StrCheck, 1, 2)
End If
Case Else
posBrk = InStr(1, StrCheck, "(")
posStar = InStr(1, StrCheck, "*")
If posBrk > posStar Then '<~~ "k*(-x)+(-m)*(-2)"
pos = InStr(1, StrCheck, "*")
If pos <> 0 Then
strK = Mid(StrCheck, 1, pos - 2)
Else
strK = Mid(StrCheck, 1, posBrk - 1)
End If
ElseIf posBrk < posStar Then '<~~ "k(-x)+(-m)*(-2)"
pos = InStr(1, StrCheck, "(")
strK = Mid(StrCheck, 1, pos - 2)
Else '<~~ "kx+m"
'~~> In such a case I am assuming that you will never use
'~~> a >=2 letter variable
strK = Mid(StrCheck, 1, 1)
End If
End Select
Debug.Print "Found " & strK & " in " & MyArray(i)
Next i
End Sub
快照
虽然不多，但希望这能引导您走向正确的方向...
Function FindKXPlusM(ByVal str As String) As String
Dim K As String, M As String
Dim regex As Object, matches As Object, sm As Object
'' remove unwanted spaces from input string (if any)
str = Replace(str, " ", "")
'' create an instance of RegEx object.
'' I'm using late binding here, but you can use early binding too.
Set regex = CreateObject("VBScript.RegExp")
regex.IgnoreCase = True
regex.Global = True
'' test for kx+m or xk+m types
regex.Pattern = "^(-?\d*)\*?x([\+-]?\d+)?$|^x\*(-?\d+)([\+-]?\d+)?$"
Set matches = regex.Execute(str)
If matches.Count >= 1 Then
Set sm = matches(0).SubMatches
K = sm(0)
M = sm(1)
If K = "" Then K = sm(2)
If M = "" Then M = sm(3)
If K = "-" Or K = "+" Or K = "" Then K = K & "1"
If M = "" Then M = "0"
Else
'' test for m+kx or m+xk types
regex.Pattern = "^(-?\d+)[\+-]x\*([\+-]?\d+)$|^(-?\d+)([\+-]\d*)\*?x$"
Set matches = regex.Execute(str)
If matches.Count >= 1 Then
Set sm = matches(0).SubMatches
M = sm(0)
K = sm(1)
If M = "" Then M = sm(2)
If K = "" Then K = sm(3)
If K = "-" Or K = "+" Or K = "" Then K = K & "1"
If M = "" Then M = "0"
End If
End If
K = Replace(K, "+", "")
M = Replace(M, "+", "")
'' the values found are in K & M.
'' I output here in this format only for showing sample.
FindKXPlusM = " K = " & K & " M = " & M
End Function
然后你可以通过宏调用它，例如：
Sub Test()
Debug.Print FindKXPlusM("x*312+12")
End Sub
或者像使用公式一样使用它。例如，在单元格中放置以下内容：
=FindKXPlusM(B1)
我会使用正则表达式来搜索一个或多个数字；在 " *x" 后面找 m，在 "+" 后面找 k。
您的示例显示整数值。如果斜率和截距是浮点数呢？有符号值呢？这比您的示例更加复杂。
我建议最通用的解决方案是编写一个带有简单语法的词法分析器/解析器来处理它。我不知道 VB 或 .NET 为您提供了什么。ANTLR 是 Java 领域的一种解决方案；也有 ANTLR.NET。
我不确定所有这些努力能给您带来什么好处。您将如何处理提取出来的内容？我认为，用户填写 k 和 m 的数字类型单元格并从中计算 y = m*x + k 将与插入字符串并提取它们一样容易。
如果您的目标只是评估一个字符串，也许 eval() 就是您的答案：
kx+m 可以有最多 7 个运算符和最少 1 个运算符。在这种情况下，获取 "K" 和 "M" 值变得非常复杂。 - Siddharth Rout