Отобразите строки, которые соответствуют критериям COUNTIF

Вот очень простой пример. Мы хотим посчитать количество значений в столбце B, которые превышают 10 . Мы помещаем критерии в ячейку C1 и в C2 введите:

=COUNTIF(B:B,C1) 

Теперь мы знаем, что есть 11 пунктов, которые способствуют подсчету. Теперь мы хотим их найти.
Введите следующую пользовательскую функцию в стандартном модуле:

Public Function CountIfFinder(rng As Range, crit As String) As String Dim r As Range, DQ As String  DQ = Chr(34) crit = DQ & crit & DQ CountIfFinder = "" Set rng = Intersect(rng, rng.Parent.UsedRange)  For Each r In rng s = "=countif(" & r.Address & "," & crit & ")" If Evaluate(s) = 1 Then CountIfFinder = CountIfFinder & "," & r.Address(0, 0) Next r  CountIfFinder = Mid(CountIfFinder, 2) End Function 

Выберите ячейку (скажем, D1 ) и введите:

=CountIfFinder(B:B,C1) 

Пользовательские функции (UDF) очень просты в установке и использовании:

1. ALT-F11 открывает окно VBE
2. ALT-I ALT-M открывает новый модуль
3. вставьте материал и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить UDF:

1. откройте окно VBE, как указано выше
2. очистить код
3. закройте окно VBE

Чтобы использовать UDF из Excel:

= MyFunction (А1)

Чтобы узнать больше о макросах в целом, смотрите:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

а также

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

а подробности о UDF смотрите в:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Макросы должны быть включены, чтобы это работало!

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

Я собираюсь составить подробное объяснение каждого из шагов, показанных ниже, чтобы объяснить, как они работают.

Шаг 1:

Демонстрирует условие поиска, которое мы установили. В этом примере мы ищем все строки, где Gender = M

Ниже показана эквивалентная функция COUNTIF, которая возвращает число строк, найденных с этим условием: 3

Шаг 2:

Установите формулу массива =IF($B$2:$B$8=$B$11,ROW($B$2:$B$8)). Это формула массива, использующая расширение обычной функции IF. Он сравнивает значения в массиве B2:B8с B11и возвращает результаты сравнения как массив значений. Когда сравнение истинно, результатом является число ROW (), иначе FALSE (потому что при сравнении ложно не указано значение).

Чтобы понять это далее, вы можете начать с более простых формул IF, как показано ниже, и поэкспериментировать с различными параметрами в value_if_true и value_if_false и понять результаты `IF (B2 = B11, ROW (B2),) '

`ЕСЛИ (В2 = В11, СТРОКА (В2), "несоответствие")»

Теперь попробуйте то же самое, изменив B11 на F, а затем посмотрите, что произойдет с результатами.

Шаг 3: Здесь мы используем функцию SMALL для возврата n-го наименьшего значения в массиве. Однако хитрость здесь заключается в изменении n-го значения в каждой строке. Таким образом, первая строка должна показывать наименьшее значение в массиве F2:F8, вторая строка должна возвращать 2-е наименьшее, а третья строка должна возвращать третье наименьшее значение.

Поэтому мы используем ROW () - 1, чтобы получить соответствующую настройку n-й переменной, а все остальное легко.

Шаг 4:

В конце шага 3 у нас есть количество строк, в которых выполняется наше условие поиска. Теперь на этом шаге все, что нам нужно сделать, это использовать функцию INDEX для извлечения значений строк, соответствующих этим номерам строк.

Для этого сначала выберите ячейки M2: O2, нажмите F2и курсор будет расположен в ячейке M2. Введите формулу INDEX($A$1:$C$8,J2,0)и нажмите Ctrl + Shift + Enter вместе, чтобы это работало как формула массива. 0 в этой формуле вынуждает возвращать всю строку вместо значений из определенного столбца из диапазона A1: C8.

Теперь выберите M2: O4 и нажмите Ctrl + D, чтобы скопировать формулу в самой верхней строке в ячейки ниже.

БИНГО!

Оставьте свои комментарии, если вам нужны разъяснения, и я буду более чем рад разъяснить. Я использовал ряд упрощений и разбил шаги, чтобы объяснить функционирование. Все эти формулы могут быть объединены вместе для достижения одинаковых результатов за один раз.

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