gpt4 book ai didi

excel - 如何使用具有一系列值的自动填充/填充

转载 作者:行者123 更新时间:2023-12-04 22:18:41 28 4
gpt4 key购买 nike

我一直在尝试让 Excel 在一组列上应用公式,然后将模式扩展到整个行集。
这导致了以下代码:

For i = 0 To avgsheetNames.Count - 1
If Contains(CStr(avgsheetNames(i)), "Scores") = True Then
With mainWorkBook.Worksheets(avgsheetNames(i))
strFormulas(1) = "=SUM(Aggregated_Internal_Scores!I2:I7)/6"
strFormulas(2) = "=SUM(Aggregated_Internal_Scores!J2:J7)/6"
strFormulas(3) = "=SUM(Aggregated_Internal_Scores!K2:K7)/6"
strFormulas(4) = "=SUM(Aggregated_Internal_Scores!L2:L7)/6"
strFormulas(5) = "=SUM(Aggregated_Internal_Scores!M2:M7)/6"
strFormulas(6) = "=SUM(Aggregated_Internal_Scores!N2:N7)/6"

strFormulas2(1) = "=SUM(Aggregated_Internal_Scores!I8:I13)/6"
strFormulas2(2) = "=SUM(Aggregated_Internal_Scores!J8:J13)/6"
strFormulas2(3) = "=SUM(Aggregated_Internal_Scores!K8:K13)/6"
strFormulas2(4) = "=SUM(Aggregated_Internal_Scores!L8:L13)/6"
strFormulas2(5) = "=SUM(Aggregated_Internal_Scores!M8:M13)/6"
strFormulas2(6) = "=SUM(Aggregated_Internal_Scores!N8:N13)/6"

mainWorkBook.Worksheets(avgsheetNames(i)).Range("C2:H2").Formula = strFormulas
mainWorkBook.Worksheets(avgsheetNames(i)).Range("C3:H3").Formula = strFormulas2
mainWorkBook.Worksheets(avgsheetNames(i)).Range("C2:H3").AutoFill Destination:=mainWorkBook.Worksheets(avgsheetNames(i)).Range("C2:H32")

End With
End If
正如你所看到的,我试图提供我想要的模式,从“Aggregated_Internal_Scores”表中提取的值应该遵循模式 I2:I7。 > I8:I13 > I14:I19等等。
但是,执行宏后,我得到的是 I2:I7 > I8:I13 > I4:I9 > I10:I15 ?
似乎 Excel 将 block C2:H3 作为模式,并在每个 block 的开头增加 2。
谁能解释我哪里出错了,以及我如何指定我希望提取工作表值遵循某种模式?
先感谢您!

最佳答案

利用:

mainWorkBook.Worksheets(avgsheetNames(i)).Range("C2:H32").Formula = "=SUM(INDEX(Aggregated_Internal_Scores!I:I,(ROW($ZZ1)-1)*6+2):INDEX(Aggregated_Internal_Scores!I:I,(ROW($ZZ1)-1)*6+7))/6"
替换 If 中的所有内容接着就,随即。

如果有 Office 365 和动态数组公式,则使用:
mainWorkBook.Worksheets(avgsheetNames(i)).Range("C2:H32").Formula2 = "=SUM(INDEX(Aggregated_Internal_Scores!I:I,SEQUENCE(6,,(ROW($ZZ1)-1)*6+2))/6"

关于excel - 如何使用具有一系列值的自动填充/填充,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/66444942/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com