☰

Excel 中空白值之间的平均差异和方向

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 中空白值之间的平均差异和方向

我有一张看起来像这：

第 1 页

    1   2   3   4   5   6   7   8   9   10  11
1                                           6
2                                       3   5
3                                           
4                               2   4   9   4
5                                           
6                                   4   6   6
7       5   3   3       3   10  8   4       8
8                                           
9                           4   11  12  12  6
10                                          
11  8   5   5       4   9   4   7   6

我希望能够找到每行每列值之间的平均差异和方向。例如，前 4 行如下所示：

     Average Difference # + Movements   # -Movements
1           
2           2                    1              0
3           
4       (2+5+5)/3                2              1

空白表示由于信息不足而产生的N/A值，并依次计算差值，即col2-col1, col3-col2, col4-col3

如果我只是取差异并制作一个复制表，并将公式=C2-B2复制过来，那么只要两个值之间或行开头有空格，就会出现问题。有没有简单的方法可以解决这个问题，或者我可能错过的其他方法？

答案1

如果您想要 VBA 解决方案，请将其复制到模块中。

Function Score(R As Range, Col As String)
    Dim ThisCell As Range
    Dim Dif As Integer
    Dim Cnt As Integer
    Dim PosMove As Integer
    Dim NegMove As Integer
    Dim PrevNum As Integer
    Dim ThisNum As Integer
    PrevNum = 9999
    For Each ThisCell In R.Cells
        If IsNumeric(ThisCell.Text) Then
            ThisNum = ThisCell.Value
            If PrevNum <> 9999 Then
                Cnt = Cnt + 1
                If ThisNum > PrevNum Then
                    Dif = Dif + (ThisNum - PrevNum)
                    PosMove = PosMove + 1
                ElseIf ThisNum < PrevNum Then
                    Dif = Dif + (PrevNum - ThisNum)
                    NegMove = NegMove + 1
                End If
            End If
            PrevNum = ThisNum
        End If
    Next
    Select Case LCase(Col)
        Case "avg"
            If Dif = 0 Or Cnt = 0 Then
                Score = 0
            Else
                Score = Dif / Cnt
            End If
        Case "pos"
            Score = PosMove
        Case "neg"
            Score = NegMove
    End Select
End Function

然后你可以像这样拨打电话：

=score(A1:K1,"avg")
=score(A1:K1,"pos")
=score(A1:K1,"neg")

答案2

我将使用 if 语句来检查单元格是否已填充

=IF(COUNT(B2:B3)=2,B3-B2,"")

如果您随后对行进行平均，则空白单元格将被忽略，并且您应该得到所需的值。

下表中。平均变化为

=SUMPRODUCT(--(A2:C2<>""),--(A3:C3<>""),(A3:C3-A2:C2))/SUMPRODUCT(--(A2:C2<>""),--(A3:C3<>""))

增加的细胞数量可能

=SUMPRODUCT(--(A2:C2<A3:C3))

细胞数量减少将是

=SUMPRODUCT(--(A2:C2>A3:C3))

假设表格只有 3 列宽，可扩展您的数据。

Sumproduct 主要用于进行数组计算。您也可以使用 ctrl+shift+enter 来使用其他公式，但这些公式应该可以解决问题。

答案3

我将使用 Power Query 插件来实现这一点。我构建了一个原型，您可以查看或下载 - 其“Power Query 演示 - 一行内的平均移动和差异.xlsx”位于我的 One Drive 中：

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

Power Query 有一个 UnPivot 命令，可以将行转换为列（忽略空单元格），您可以添加一个 Index 列来跟踪单元格的相对位置。在我的第一个查询中，我使用这些命令将您的表格转换为一长串行（每个单元格一行）。

然后在第二个查询中，我将该列表与其自身合并，以将每个单元格值与左侧的下一个非空白单元格关联起来。然后计算（例如差异、+ 移动）变得相当容易。最后一步是按行号分组并汇总计算

最后，我再次从输入数据开始并合并第二个查询，以获取完整的行列表（包括没有差异的行）。

Power Query 方法的好处在于您不需要使用 VBA 编写代码，99% 的工作都是通过在 Power Query UI 中单击或通过轻松编辑生成的代码来完成的。您也不需要复杂的嵌套公式 - 您可以重塑数据，直到计算变得简单。

Power Query Unpivot 命令非常神奇 - 如果将新列添加到源表，它将自动扩展以适应新列，并删除空白值。

相关内容