Как использовать Excel для расчета одновременных полос

Question

Я хочу использовать Excel, чтобы вычислить "лучшую" и "текущую" полосу в диапазоне данных.

Скажите, что цель продаж магазина в течение дня - 500. У меня есть столбец, который содержит данные о ежедневных продажах за последние 5 лет. Как я могу использовать эти данные, чтобы вычислить мой лучший пробег дней, соответствующий цели продаж, и мой текущий пробег дней.

Я пытался использовать COUNTIF(C:C, "> = 500"), но проблема в том, что он просто подсчитывает все дни, когда была достигнута цель, не учитывает лучшую "полосу" или количество дней в записи. строка, в которой цель была достигнута.

Искренне спасибо заранее.

Answer 1

Вспомогательная колонка сделает это проще. Посмотрим, сможем ли мы обойтись без этого.

Базовый подход

На самом деле мы вообще не собираемся использовать 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 чтобы найти (первый) экземпляр самой длинной достигнутой полосы, как вычислено в ячейке выше.

Ограничения

1. Как я уже упоминал, поскольку мы не используем массивы или что-либо действительно сложное, в случае связей это возвращает первое появление полосы такой длины. Я бы назвал это функцией - ты не установил новый рекорд, пока не побил последний, верно?
2. По той же причине этот подход не будет работать очень хорошо, если вы хотите знать, что и когда были n- ными длинными полосами для любых значений n больше 1. Ну, он может сказать вам , что эти полосы были - использовать LARGE для этого - но так как длина (п - -longest полосы , где п> 1), по определению, имели место более чем один раз, то поиск , который тянет на дате для этой серии не будет работать с этим подходом.
3. Это требует, чтобы набор данных был таким, как показано / дано в вашем примере - очевидно, записи о продажах должны быть упорядочены по дате; возможно, более важно отметить, что он настроен только на работу с листом, содержащим данные для одного хранилища.