Вспомогательная колонка сделает это проще. Посмотрим, сможем ли мы обойтись без этого.
Базовый подход
На самом деле мы вообще не собираемся использовать COUNTIF , просто простой инкрементный счетчик.
Обратите внимание, что мы сделали вашу цель продаж динамической и определили ее в ячейке D1 . Оказывается, было бы немного проще иметь его в другом месте; поэтому, если вы следуете этим инструкциям, вы можете вместо этого поместить его в G1 и настроить все ссылки на ячейки $D$1 , следующие за $G$1 .
Эта формула работает
=IF(C2<$D$1,"dq",IF(AND(C1<$D$1,C2>=$D$1),1,D1+1))
=IF(C2<$D$1,"dq", если текущая запись не соответствует требованиям, дисквалифицировать ее.
IF(AND(C1<$D$1,C2>=$D$1),1, если текущая запись является первым последовательным классификатором (C1<$D$1 проверяет предыдущую запись на статус 'dq'; C2>=$D$1 проверяет текущую запись на квалификацию), сделайте ее значение равным 1
D1+1)) Иначе - то есть, если текущая запись не является ни дисквалифицированным итогом, ни первым квалифицированным итогом, сделайте ее значение на единицу больше, чем запись над ней.
Эта формула копирует вниз с D2. Обратите внимание, что в этой точной настройке возникают проблемы, если первая запись (строка 2) является квалифицирующей записью, и вы используете D1 поскольку я должен определить цель, поскольку D1 выглядит как полоса 500. Есть несколько тривиальных способов обойти это; в целях примера, который я только что выбрал, чтобы указать, что первая запись не подходит, для простоты.
Отсюда должно быть довольно просто добавить следующую пару функций.
Добавить 'длинная полоса'
=MAX(INDIRECT("D2:D"&COUNTA(D:D)))
Здесь мы используем динамическую ссылку на ячейку, поэтому нам не нужно беспокоиться о расширении диапазона. Мы не можем использовать D:D , опять же, потому что этот максимум вернется как 500 в D1 . Но вы можете просто написать =MAX($D$2:$D$9999) или что угодно, если хотите. В любом случае использование INDIRECT - это хорошая практика.
И затем, по-видимому, у вас есть идентифицирующая информация в A и B - возможно, дата, которую вы хотели бы связать с вашей «самой длинной полосой»?
Связанные данные
=INDEX(B:D,MATCH(G2,D:D,0),1)
Это простое INDEX MATCH чтобы найти (первый) экземпляр самой длинной достигнутой полосы, как вычислено в ячейке выше.
Ограничения
- Как я уже упоминал, поскольку мы не используем массивы или что-либо действительно сложное, в случае связей это возвращает первое появление полосы такой длины. Я бы назвал это функцией - ты не установил новый рекорд, пока не побил последний, верно?
- По той же причине этот подход не будет работать очень хорошо, если вы хотите знать, что и когда были n- ными длинными полосами для любых значений n больше 1. Ну, он может сказать вам , что эти полосы были - использовать
LARGE для этого - но так как длина (п - -longest полосы , где п> 1), по определению, имели место более чем один раз, то поиск , который тянет на дате для этой серии не будет работать с этим подходом.
- Это требует, чтобы набор данных был таким, как показано / дано в вашем примере - очевидно, записи о продажах должны быть упорядочены по дате; возможно, более важно отметить, что он настроен только на работу с листом, содержащим данные для одного хранилища.
