Рональд импортирует в Excel ряд измерений уровня сигнала в виде серии значений. Ему нужно подсчитать, сколько последовательных групп ценностей существует в этой серии, которые опускаются ниже определенного порога. Например, он может иметь следующие размеры:

27, 22, 22, 30, 32, 18, 22, 23, 28, 39, 24, 27, 35, 25, 21

Если он хочет узнать количество групп, в которых члены этих групп были младше 26, ответ будет 4. Обратите внимание, что это группировки последовательных значений ниже 26, а не количество отдельных значений ниже 26. Таким образом, в этом В этом случае четыре группы будут показаны скобками следующим образом:

27, [22, 22], 30, 32, [18, 22, 23], 28, 39, [24], 27, 35, [25, 21]

Рональду интересно, какую формулу он может использовать, чтобы вычислить количество группировок, которые опускаются ниже некоторого произвольного порога, который он может указать.

На самом деле есть несколько разных подходов к этому. Первый — использовать «столбец результатов», который по существу отмечает изменения пороговых значений и группировки последовательностей. Например, если у вас были указанные выше значения в столбце A рабочего листа (начиная с ячейки A2) и пороговое значение в ячейке E1, то вы можете использовать следующую формулу в каждой ячейке справа от значения в столбце A:

=IF(A2>=$E$1,B1,IF(A1<$E$1,B1,B1+1))

Формула сохраняет текущую сумму групп ниже порогового значения. Максимальное (или последнее значение) столбца B обеспечивает общее количество групп ниже порогового значения. Формула проверяет, находится ли значение слева в столбце A выше или ниже порога.

Если оно выше или нет и предыдущее значение в столбце A также было ниже, то текущая сумма не увеличивается. В противном случае он увеличивается, потому что начинается новая группировка.

Связанный способ подсчета — использовать вместо этого эту формулу в столбце B:

=IF(A2>=$E$1,0,IF(A1<$E$1,0,1))

В результате получается столбец B, содержащий серию из 0 или 1 значений. Единственный раз, когда встречается значение 1, — это начало серии, которая ниже порога. Это позволяет легко суммировать все значения в столбце B, который обеспечивает количество группировок.

Если вы не хотите использовать столбец результатов, вы можете использовать формулу массива, чтобы вычислить количество. В следующей формуле снова предполагается, что анализируемые значения находятся в столбце A, начиная с A2, а пороговое значение находится в ячейке E1. Также помните, что формулы массива вводятся нажатием Ctrl + Shift + Enter.

=SUM(IF((A2:A16<$E$1)((A2:A16((A1:A15<$E$1)ISNUMBER(A1:A15))),1))

Формула в основном выполняет то же действие, что и предыдущая формула столбца результатов (определяет 0 или 1 в зависимости от того, начинается ли группировка ниже порога), а затем суммирует эти значения.

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

Function CountGroups(ByVal MyRange As Range, Threshold As Single)

Dim Cell As Range     Dim bInGroup As Boolean     Dim iCount As Integer

Application.Volatile     iCount = 0     bInGroup = False     For Each Cell In MyRange         If Application.IsNumber(Cell) Then             If Cell < Threshold Then 'Less than the threshold?

If Not bInGroup Then  'Only count if starting new group                     iCount = iCount + 1                     bInGroup = True     'Mark as being in group                 End If             Else                 bInGroup = False        'No longer in a group             End If         End If     Next     CountGroups = iCount End Function

Функция просматривает каждую ячейку в диапазоне и вычисляет, является ли она началом новой группы ниже порога или нет. Вы используете эту функцию, используя следующую формулу на рабочем листе:

=CountGroups(A2:A16,E1)

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (3171) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

link: / excelribbon-Counting_Groupings_Below_a_Threshold [Подсчет группировок ниже порога].