Diferença média e direção entre valores no Excel com espaços em branco

Question

Eu tenho uma planilha parecida com esta:

Folha 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       

O que eu gostaria de poder fazer é encontrar a diferença média e a direção entre os valores em cada coluna para cada linha. Por exemplo, as primeiras 4 linhas seriam semelhantes:

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

Os espaços em branco representam valores N / A devido a informações insuficientes e as diferenças são calculadas sucessivamente, ou seja, col2-col1, col3-col2, col4-col3

Se eu pegar as diferenças e criar uma tabela duplicada com a fórmula =C2-B2 copiada em todos os problemas, surgem sempre que houver um espaço em branco entre dois valores ou no início da linha. Existe uma maneira fácil de corrigir isso ou outra maneira de fazer isso que eu possa estar faltando?

    

Answer 1

Se você quiser uma solução VBA, copie isso para um módulo.

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

Então você faria a ligação assim:

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

    

Answer 2

Eu usaria uma declaração if para verificar se as células estão preenchidas

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

Se você calcular a média da linha, as células em branco serão ignoradas e você deverá obter o valor desejado.

Para sua tabela abaixo. A mudança média seria

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

O número de células que aumentam pode ser

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

O número de células que diminuem seria

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

Isso pressupõe que a tabela tenha apenas 3 colunas de largura e seja expandida para seus dados.

O Sumproduct é usado principalmente para fazer cálculos de arrays. Você também pode usar outras fórmulas usando ctrl + shift + enter, mas estas devem fazer o truque.

    

Answer 3

Eu usaria o suplemento de consulta de energia para isso. Eu criei um protótipo que você pode ver ou baixar - é a "demonstração do Power Query - movimento e diferença médios em uma linha.xlsx" no meu One Drive:

link

O Power Query tem um comando UnPivot para transformar linhas em colunas (o que ignora as células vazias) e você pode adicionar uma coluna Index para acompanhar a posição relativa das células. Na minha primeira consulta, usei esses comandos para transformar sua tabela em uma longa lista de linhas (uma para cada célula).

Em seguida, na segunda consulta eu mesclei essa lista consigo mesma para relacionar cada valor de célula à próxima célula não vazia à esquerda. Então os cálculos, e. Diferenças, + Movimentos tornaram-se bastante fáceis. A última etapa é agrupar o número da linha e agregar os cálculos

Por fim, comecei novamente a partir dos dados de entrada e da segunda consulta, para obter a lista completa de linhas (incluindo aquelas sem diferenças).

Em seguida, o benefício da abordagem do Power Query é que você não precisa codificar no VBA, 99% disso é feito clicando na interface do usuário do Power Query ou editando facilmente o código gerado. Você também não precisa de fórmulas aninhadas complexas - você pode reformular os dados até que os cálculos se tornem fáceis.

O comando Power Query Unpivot é uma maravilha - ele será expandido automaticamente para atender a novas colunas se elas forem adicionadas à tabela de origem e removerem valores em branco.