如何在公式中使用 R1C1 格式的文本

Question

为了通过VBA开发动态公式，我不得不使用间接(ADDRESS(.我现在被几十个看起来像这样的公式所困：

=INDIRECT(ADDRESS(37,10),1)*(INDIRECT(ADDRESS(37,7),1)+(INDIRECT(ADDRESS(69,10),1)*INDIRECT(ADDRESS(77,10),1))+(INDIRECT(ADDRESS(70,10),1)*INDIRECT(ADDRESS(78,10),1))+(INDIRECT(ADDRESS(71,10),1)*INDIRECT(ADDRESS(79,10),1))+(INDIRECT(ADDRESS(72,10),1)*INDIRECT(ADDRESS(80,10),1))+(INDIRECT(ADDRESS(73,10),1)*INDIRECT(ADDRESS(81,10),1))+(INDIRECT(ADDRESS(74,10),1)*INDIRECT(ADDRESS(82,10),1))+(INDIRECT(ADDRESS(75,10),1)*INDIRECT(ADDRESS(83,10),1)))

我不能将这些公式与规划求解一起使用，因为它对规划求解来说太多了。当我手动将公式转换为"A1"格式时，求解器就可以工作了。我需要自动化翻译，因为这是一个模板。

我希望用">

单元格"替换"间接(地址("可能会起作用。没有运气。我可以使用的表达式是否可以替换将读取 R1C1 格式的"间接(地址("？或者你有更好的解决方案吗？

谢谢！

 r = 0
    Do Until r = 6
        i = 0
        Do Until i = DCShipFrom
        Cells(70 + r, 7 + DCShipFrom + i).Select
        Selection.FormulaR1C1 = "=IF(AND(INDIRECT(ADDRESS(" & 67 & "," & 7 + DCShipFrom + i & "),1)<=" & "INDIRECT(ADDRESS(" & 70 + r & "," & 6 & "),1)," & "INDIRECT(ADDRESS(" & 67 & "," & 7 + DCShipFrom + i & "),1)>" & "INDIRECT(ADDRESS(" & 69 + r & "," & 6 & "),1)),1,0)"
        i = i + 1
        Loop
    r = r + 1
    Loop
    enter code here

Answer 1

试试这个，它使公式动态，但更简单：

For r = 0 To 6
    For i = 0 To DCShipFrom
        With ActiveSheet
            .Cells(70 + r, 7 + DCShipFrom + i).Formula = "=IF(AND(" & .Cells(67, 7 + DCShipFrom + i).Address(0, 0) & "<=" & .Cells(70 + r, 6).Address(0, 0) & "," & .Cells(67, 7 + DCShipFrom + i).Address & " >" & .Cells(69 + r, 6).Address & "),1,0)"
        End With
    Next i
next r

或者，如果您想要 R1C1：

For r = 0 To 6
    For i = 0 To DCShipFrom
        With ActiveSheet
            .Cells(70 + r, 7 + DCShipFrom + i).FormulaR1C1 = "=IF(AND(R" & 67 & "C" & 7 + DCShipFrom + i & " <= R" & 70 + r & "C" & 6 & ",R" & 67 & "C" & 7 + DCShipFrom + i & " > R" & 69 + r & "C" & 6 & "),1,0)"
        End With
    Next i
Next r

Answer 2

随着ADDRESS(37, 10)返回$J 37 美元的使用，

INDIRECT(ADDRESS(37, 10), 1)  'or INDIRECT(ADDRESS(37,10)) since xlA1 is default

随着ADDRESS(37, 10, 1, 0)返回R37C10的使用，

INDIRECT(ADDRESS(37, 10, 1, 0), 0)  'the zeroes denote xlR1C1 addressing

您可能需要考虑以下情况之一，

INDEX($J:$J, 37)
INDEX($A:$Z, 37, 10)

索引是非易失性的。间接和地址都是易失性的。

Answer 3

您可以通过在

间接语句末尾添加 ，FALSE 将 INDIRECT 与 R1C1 格式一起使用，并使其动态化，您可以创建行和列变量，例如

INDIRECT("R" & myrow & "C" & mycol,FALSE)