Пользовательская функция: определение номеров элементов массива, значение которых на 20% выше среднего

@exx1dae · Регистрация: 14.11.2018

Добрый вечер, прошу помощи с пользовательской функцией. Я новичок в VBA, и никак не могу сообразить, как правильно переделать данную функцию, чтобы она заработала
Задание: Создать пользовательскую функцию определяющую номера элементов массива, значение которых на 20% выше среднего. В дополнительном последнем столбце таблицы вывести номера месяцев, когда было принято пациентов на 20% больше среднего.

мой набросок моей бедной не работающей функции

Visual Basic

Function avr(A As Byte, B As Byte) As Integer
 
C.Value = WorksheetFunction.Average(Range("B5:J17"))     ' (1*)
 
If A * 1.2 > C.Value Then avr = B       ' (2*)
End Function

1* само среднее значение всех ячеек. Если кто знает, как переделать так, чтобы искалось не только в диапазоне "B5:J17", буду очень благодарна.
2* тут предполагается, что А это значение каждой ячейки +20%, которое сравнивается со средним значением диапазона, что писать после "Then" и как вывести номера месяцев, к сожалению, понятия не имею

Буду очень благодарна любой помощи. Заранее спасибо

@Святой НякаЛайк · 17.12.2018, 13:23

Самое среднее по всем дням?
В вашей таблице это получается 10,27
А потом выбрать месяцы, в которых было принято на 20% больше, чем среднее по дням? То есть взять всё количество месяца и определить, больше ли оно, чем среднее в день, так?

Добавлено через 3 минуты
То есть получается, что Ямских в январе приняла 12 пациентов, а все остальные "план выполнили" - сделали больше, чем 12,3 пациента в месяц. (Тут ещё есть важное уточнение: в данном случае уместно использовать значение с разделителями, например 12,33 пациента? Просто на первый взгляд пациенты должны целиком идти, но если все указанные врачи являются хирургами, то у кого-то из них может на приём и полтора пациента придти...)

Добавлено через 2 часа 18 минут

Visual Basic

Function exx1dae(ByVal mas As Range) As Integer ' Функция, которая покажет среднее значение в диапазоне ячеек (ячейки подаются аргументом mas)
   Dim mas1
   mas1 = mas.Value
   Dim a&, b&, aver, tmp As Single
   If IsEmpty(mas1) Then exx1dae = 0   ' если пустое, то 0
   If Not IsArray(mas1) Then exx1dae = mas1: GoTo Factor   ' Если одна ячейка, то её значение и есть среднее число!
   aver = (1 + UBound(mas1, 1) - LBound(mas1, 1)) * (1 + UBound(mas1, 2) - LBound(mas1, 2))   ' узнаём, сколько элементов в массиве
   ' Теперь в цикле суммируем все значения массива в переменную tmp
   For a = LBound(mas1, 1) To UBound(mas1, 1)
      For b = LBound(mas1, 2) To UBound(mas1, 2)
         tmp = tmp + mas1(a, b)
      Next b
   Next a
   ' А теперь сумму всех (tmp) делим на количество всех (aver)
   exx1dae = tmp / aver
Factor:    exx1dae = Fix(exx1dae * 1.2)   ' Умножаем на 120%
End Function

Вот для начала функция, которая из диапазона ячеек рассчитает среднее значение и умножит на 120%
Функция сгодится как для использования прямо в ячейках, так и в коде. Возвращает целое число

@exx1dae · 17.12.2018, 15:02 **[ТС]**

спасибо большое, что ответили и помогаете разобраться с VBA, сейчас уточню

да, в таблице среднее значение 10,22227. Но так как в моей таблице все числа не дробные, а целые, то, например возьмем значение "8" и прибавив к восьми 20% получаем дробное значение, которое не будет превышать 10,27. Возьмем "9" и так же, прибавив 20%, видим, что значение - 10,8 => данное число, и все числа больше него, нам подходят и теперь нужно вывести месяц, в котором находится наша ячейка, а не само значение 10,8. Извиняюсь, что не уточнила все сразу.

Ночью пыталась переделать свою функцию, но получилось так, что функция считает те значения, которые мне нужно ( т.е, больше 9), но теперь возникла проблема, как сделать так, чтобы выводил номера месяцев, а не номера столбцов.

Visual Basic

Public Function avr(ParamArray A()) As Variant
Dim N As Variant, i As Variant
Dim b As Variant
N = UBound(A)
b = ""
For i = 0 To N
 
If A(i) * 1.2 > (WorksheetFunction.Average(Range("B5:J14"))) Then b = b & (i + 1) & " "
 
Next i
 
avr = b
 
End Function

@Святой НякаЛайк · 17.12.2018, 15:36

Попробуйте так:

Visual Basic

1	If a(i) * 1.2 > (WorksheetFunction.Average(Range("B5:J14"))) Then b = b & Month(Cells(4, i + 1)) & "; "

Добавлено через 11 минут
Это: b = ""
Необязательно. Когда создаёте переменную, она изначально пустая. Если объявить как Dim b as string, то как раз получите строковую переменную с нулевым значением. то есть b = "" - это по умолчанию.

Дальше: вместо того, чтобы делать

Visual Basic

Dim N As Variant
'...код 
N = UBound(A)
'...код 
For i = 0 To N
'...код

Можно сделать проще, а именно так:

Visual Basic

1	For i = 0 To UBound(A)

Результат ровно тот же, а строк кода и переменных меньше. В данном случае код может таким образом стать понятнее.

@exx1dae · 17.12.2018, 15:39 **[ТС]**

спасибо вам огромное, вы не представляете как помогли

@Святой НякаЛайк · 17.12.2018, 15:44

Минимум помощи, вы практически всё самостоятельно. Такие как вы на этом форуме - на вес золота!

@exx1dae 1 / 1 / 0 Регистрация: 14.11.2018 Сообщений: 7
	17.12.2018, 15:39 [ТС]	5
	спасибо вам огромное, вы не представляете как помогли 1

@Святой НякаЛайк 647 / 240 / 86 Регистрация: 28.10.2015 Сообщений: 504
	17.12.2018, 15:44	6
	Минимум помощи, вы практически всё самостоятельно. Такие как вы на этом форуме - на вес золота! 1

Опции темы

Пользовательская функция: определение номеров элементов массива, значение которых на 20% выше среднего

Решение