excel - 公式过长-达到字符数限制-VBA是否可以正常工作？

我正在创建一个工具，用于汇总我们放入Excel工作簿中的某些数据。因此，从本质上讲，在我们将工作簿的名称输入到摘要工具之后，我使用了“间接”功能从工作簿中提取数据。通常，这个想法：

IFERROR(INDIRECT("'[" & Refs!$B$4 & "]" & $A$1 & "'!" & EmplRefs!$C91),0)=C$76,1,0)

其中$ B $ 4是第一个文件名的位置，然后我将执行+IFERROR(INDIRECT ... $B$5等。这工作正常。但是，我需要对其进行设置，使其最多可容纳70个文件。在特定的公式中，如果它是IF / AND语句，并且在公式/单元格的字符数限制之外，这也可以很好地工作。

如果将其放入VBA，VBA会允许使用超长公式，还是相同的限制？

我对向单元格添加公式的理解：

Sub voccomplete()
    Worksheets("Employment").Range("E83").Formula = "=[giant formula here]"
End Sub

欢迎获得有关此信息的其他想法（我不太熟悉VBA，我通常可以按照说明进行操作）...这是有问题的公式（仅可容纳25个文件）：

IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$4& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$4& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$5& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$5& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$6& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$6& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$7& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$7& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$8& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$8& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$9& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$9& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$10& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$10& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$11& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$11& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$12& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$12& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$13& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$13& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$14& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$14& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$15& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$15& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$16& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$16& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$17& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$17& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$18& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$18& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$19& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$19& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$20& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$20& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$21& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$21& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$22& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$22& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$23& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$23& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$24& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$24& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$25& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$25& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$26& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$26& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$27& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$27& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)+IF(AND(IFERROR(INDIRECT("'[" & Refs!$B$28& "]" & $A$1 & "'!" & EmplRefs!$C91),0)=B$76,IFERROR(INDIRECT("'[" & Refs!$B$28& "]" & $A$1 & "'!" & EmplRefs!$B93),0)="Completed"),1,0)

电脑资讯：Windows 7专业版，Excel 2016

最佳答案

因此，您要遍历工作簿，查看A1中定义的工作表，并查看C91中定义的单元格，并将其与b76中保存的值进行比较-如果该值相等并且B93中定义的单元格也等于“已完成” ”，然后加1。

您需要一个用户定义函数，该函数需要定义要查看的工作簿的范围（B4：B74吗？），并将范围C91和B93以及“ B76”的内容作为地址

 Public Function NumberCompleted(WorkbooksToLookAt as range, S1 as string, S2 as string,s3 as string) as long
 Dim wb as workbook
 Dim ws as worksheet
 Dim r as range
 dim c as range
 dim counter as long
 For each r in Workbookstolookat
     set wb = workbooks(r)
     set ws = wb.worksheets(range("a1").text)
     set c = ws.range(s1)
     if c = ws.range(s2) then
           if ws.range(s3)="Completed" then
                 counter = counter + 1
           end if
      end if
    Next r
    NumberCompleted = counter
    End Function

您将在电子表格中使用的

  =NumberCompleted(B4:B74,C91,"B76",B93)

注意：我在用手机，所以我无法运行它，因此可能存在语法错误