Средняя функция

Question

У меня есть огромный набор данных, который обновляется каждый месяц. Я хотел бы проверить, отличаются ли новые данные от предыдущих месяцев в%.

Это выглядит так:

month name  data  
jan   551   2  
jan   552   20  
dec   553   12  
jan   553   13  
dec   554   9  
okt   555   2  
nov   555   2  
dec   555   2,5    

Итак, 5 имен, до 4 месяцев и разные данные пр. месяц, пр. название.

Я хотел бы сделать формулу, которая может сказать мне, если одно из имен отличается на i% по сравнению с предыдущими месяцами. Например, я хотел бы сказать, что "имя" 555 в декабре отличается на 25% по сравнению со средним для этого клиента.

Спасибо,
Андерс

---

Ну, я думаю, что я не слишком хорош, чтобы объясниться. Поэтому я постараюсь быть немного более ясным.

Я делаю это как функцию Excel.

Поэтому формула, которую я ищу, должна вычислять pr. Назовите средние цифры (в среднем). Это, конечно, меняется каждый месяц. И формула должна найти месячную разницу по сравнению со средней.

У меня около 2000 строк с 900 разными именами.

Answer 1

=(SUM(($A$2:$A$9=A2)*($B$2:$B$9=B2)*($C$2:$C$9))-(SUM(($B$2:$B$9=B2)*($C$2:$C$9))/SUM(--($B$2:$B$9=B2))))/SUM(($A$2:$A$9=A2)*($B$2:$B$9=B2)*($C$2:$C$9))

Это формула массива, и ее нужно вводить с помощью клавиш Control+Shift+Enter, а не просто Enter. Если ваша первая строка данных начинается с A2, эта формула переходит на D2 и заполняется до тех пор, пока у вас есть данные.

SUM(($A$2:$A$9=A2)*($B$2:$B$9=B2)*($C$2:$C$9))

Эта часть суммирует все данные с тем же именем и месяцем, что и строка, в которой вы находитесь.

(SUM(($B$2:$B$9=B2)*($C$2:$C$9))/SUM(--($B$2:$B$9=B2)))

Эта часть берет среднее (сумма / количество) всех данных с тем же именем, что и строка, в которой вы находитесь, независимо от месяца.

За декабрь 553, 12 вы получите -4,17%. Среднее значение для 553 составляет 12,5, а декабрь на 4,17% меньше среднего.

Answer 2

Предполагать:

avgN = average amount for customer n
currN = current (monthly) amount for customer n
delta = tolerance expressed as a decimal (.25 in your example)

Тогда формула

=abs((currN - avgN) / avgN) 

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

=if(abs((currN - avgN) / avgN) > delta, "tolerance exceeded", "within tolerance") 

Из вашего вопроса не ясно, хотите ли вы сделать это в коде VBA или в функции рабочего листа. Если первое, то вам нужно что-то вроде этого

Sub checkTolerance()

    Dim percentageChange As Double
    Dim currN As Double
    Dim avgN As Double
    Dim delta As Double

    ' Set the values of the variables above from somewhere (worksheet cell, user input, etc.)

    percentageChange = Abs((currN - avgN) / avgN)


    If percentageChange > delta Then 
        ' Do something when tolerance is exceeded
        MsgBox "tolerance exceeded"     

    Else
        ' Do something when amount is within tolerance
        MsgBox "within tolerance"
    End If 

End Sub