[已解决]求把间隔#N/A的5个数字的总和显示在指定位置的公式，非常感谢！

lsyylw · 发表于 2021-11-8 10:41

本帖最后由 lsyylw 于 2021-11-8 11:05 编辑

QQ截图20211108105430.png

2021-11-8-.zip (2.96 KB, 下载次数: 10)

cutecpu · 发表于 2021-11-8 11:24

=IF(ISNA(B22),NA(),AGGREGATE(9,6,INDEX(B:B,LARGE(IF(1-ISNA(B$3:B22),ROW($3:22)),5)):B22))

※數組公式，請記得按Ctrl+Shift+Enter

祝順心，南無阿彌陀佛！

lsyylw · 发表于 2021-11-8 11:30

cutecpu 发表于 2021-11-8 11:24
=IF(ISNA(B22),NA(),AGGREGATE(9,6,INDEX(B:B,LARGE(IF(1-ISNA(B$3:B22),ROW($3:22)),5)):B22))

※數組 ...

非常感谢老师，可能我用的是2003版的缘故，测试没有成功，老师您受累，有时间再帮我看看，拜托了！

cutecpu · 发表于 2021-11-8 11:49

这个最佳答案由 cutecpu 给出，感谢 cutecpu 的回答。

单击隐藏图章

lsyylw 发表于 2021-11-8 11:30
非常感谢老师，可能我用的是2003版的缘故，测试没有成功，老师您受累，有时间再帮我看看，拜托了！

=IF(ISNA(B22),NA(),SUM(OFFSET(B$1,LARGE(IF(1-ISNA(B$3:B22),ROW($3:22)),ROW($1:$5))-1,)))

lsyylw · 发表于 2021-11-8 12:02

cutecpu 发表于 2021-11-8 11:49
=IF(ISNA(B22),NA(),SUM(OFFSET(B$1,LARGE(IF(1-ISNA(B$3:B22),ROW($3:22)),ROW($1:$5))-1,)))

万分的感谢老师，这就是我想要的结果，公式非常非常好用。

林木水 · 发表于 2021-11-8 15:45

本帖最后由林木水于 2021-11-8 15:51 编辑

看第三个工作表 sheet3
1.首先把B列数据进行判断
在E列操作：=IFERROR(B3,"A")，往下填充公式
2.在F列进行判断
F2单元格输入0；F3单元格输入：=IF(E5="A",F4,F4+1)，往下填充公式
3.在D22单元格输入：
=IF(E22="A",NA(),INDEX(E:E,MATCH(F22,F:F,0))+INDEX(E:E,MATCH(F22-1,F:F,0))+INDEX(E:E,MATCH(F22-2,F:F,0))+INDEX(E:E,MATCH(F22-3,F:F,0))+INDEX(E:E,MATCH(F22-4,F:F,0)))
说明，如果E列值为A说明是B列是错误值，直接输出#N/A
如果不是错误的，分别用match定位是对应F列的数值-0，-1，-2，-3，-4的行数，在结合index得出要的数值求和

如果用VBA也可以，看第二个工作表sheet2

Sub 求和5个数值()
Dim i As Integer, j As Integer, k As Integer
Dim arr(1 To 5) As String
      For i = 22 To Sheet2.[d22].End(xlDown).Row
            If VBA.IsError(Sheet2.Cells(i, "b")) = True Then
                  Sheet2.Cells(i, "D").Value = "#N/A"
            Else
                     For j = i To 3 Step -1
                           '将5个值分别存入arr
                           If VBA.IsError(Sheet2.Cells(j, "b")) = False Then
                              k = k + 1
                              If k <= 5 Then
                                 arr(k) = Sheet2.Cells(j, "b").Value
                              '超过5就终止当前单元格的求和调到下一个单元格
                              Else
                                 Sheet2.[g1].Resize(5, 1) = Application.Transpose(arr)
                                 k = 0
                                 Sheet2.Cells(i, "D").Value = Application.WorksheetFunction.Sum(Sheet2.[g1].Resize(5, 1))
                                 Sheet2.[g1].Resize(5, 1) = ""
                                 GoTo 100
                              End If
                           End If
                        Next j
100:
                  End If
      Next i
End Sub

lsyylw · 发表于 2021-11-14 21:34

林木水发表于 2021-11-8 15:45
看第三个工作表 sheet3
1.首先把B列数据进行判断
在E列操作：=IFERROR(B3,"A")，往下填充公式

非常感谢老师，让您受累了。

林木水 · 发表于 2021-11-15 14:41

lsyylw 发表于 2021-11-14 21:34
非常感谢老师，让您受累了。

学习是一种信仰，加油

		自动登录	找回密码
密码			注册

[已解决]求把间隔#N/A的5个数字的总和显示在指定位置的公式，非常感谢！

评分

评分