Средняя разница и направление между значениями в Excel с пробелами

Question

У меня есть лист, который выглядит примерно так :

Лист 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 скопированной между проблемами, возникает всякий раз, когда между двумя значениями или в начале строки появляется пробел. Есть ли простой способ исправить тот или иной способ сделать это, что я мог бы пропустить?

Answer 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")

Answer 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, но они должны сделать свое дело.

Answer 3

Я бы использовал Power Query Add-In для этого. Я построил прототип, который вы можете просмотреть или загрузить - его демонстрацию Power Query - среднее движение и разницу по row.xlsx »в моем One Drive:

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

В Power Query есть команда UnPivot, чтобы превратить строки в столбцы (в которых игнорируются пустые ячейки), и вы можете добавить столбец индекса, чтобы отслеживать относительное положение ячеек. В моем первом запросе я использовал эти команды, чтобы преобразовать вашу таблицу в длинный список строк (по одной на каждую ячейку).

Затем во втором запросе я объединил этот список с самим собой, чтобы связать каждое значение ячейки со следующей непустой ячейкой слева. Тогда вычисления, например, Различия, + Движения стали довольно легкими. Последний шаг - сгруппировать номер строки и объединить вычисления.

Наконец, я снова начал с ввода данных и объединил 2-й запрос, чтобы получить полный список строк (в том числе без различий).

Преимущество подхода Power Query заключается в том, что вам не нужно кодировать в VBA, 99% этого делается путем щелчка по интерфейсу Power Query или путем простого редактирования сгенерированного кода. Вам также не нужны сложные вложенные формулы - вы можете изменять данные, пока вычисления не станут простыми.

Команда Power Query Unpivot удивительна - она автоматически расширяется, чтобы обслуживать новые столбцы, если они добавляются в исходную таблицу, и удаляет пустые значения.