#excel #date #if-statement
#excel #Дата #if-оператор
Вопрос:
У меня возникла проблема с созданием формулы, поэтому я хочу попросить вас о помощи.
В таблице Excel 150 000 строк, и с помощью этой формулы я хочу сэкономить время.
У меня есть дата, имя и статус, и мне нужно посмотреть в другой ячейке, какое имя было 4 раза или более раз подряд «ХОРОШО» или «ОК»
Пример ввода:https://imgur.com/aRALd9S
Я думаю, что IF И диапазона дат этого достаточно, но я не знаю, как собрать это воедино.
Большое спасибо за ваши предложения!
Вот что у меня есть на данный момент: https://imgur.com/Y5WAov5
=COUNTIFS($D$2:$D$15;D2;$C$2:$C$15;»OK»;$D$2:$D$15;D2;$E$2:$E$15;»>=»amp;E2;$E$2:$E$15;»<=»amp;E2 7) (COUNTIFS($D$2:$D$15;D2;$C$2:$C$15;»GOOD»))
С помощью этого я могу подсчитать, сколько раз у меня было имя, которое является нормальным или ХОРОШИМ и находится в диапазоне одной недели, но я все еще не знаю, что мне нужно изменить, что я перестану считать, когда там будет значение false
Комментарии:
1. Что вы уже пробовали?
2. было бы неплохо иметь некоторый пример и ожидаемый результат
3. Используйте countif() или countifs() — вы можете подсчитать, сколько раз встречается это имя или одно и то же в пределах диапазона дат…
4. Я новичок, и у меня есть только базовые знания, и пока я просто пытался сделать это без даты. Я не исключаю, что решение просто предложение. После этого я хочу собрать ее самостоятельно.
5. @SolarMike значит, это будут countifs () с условием и(), что имя имеет статус «ОК» или «ХОРОШО»??
Ответ №1:
Что ж, вот кое-что, что вы могли бы попробовать. Вы могли бы сделать это в одной формуле с формулой массива, но с 150 тысячами строк кажется намного лучше избегать формул массива и использовать вспомогательные столбцы, где это необходимо.
Первый вспомогательный столбец содержит только идентификатор пользователя, если строка содержит FALSE:
=IF(H2=FALSE,I2,"")
Второй вспомогательный столбец содержит смещение от текущей строки к следующей FALSE для того же человека:
=IFERROR(MATCH(I2,K2:K$15,0)-1,16-ROW())
Итак, теперь вы можете использовать в основном ту же самую формулу COUNTIFS, но заменяя каждый диапазон индексом, который указывает, сколько строк вы должны посчитать:
=IF(H2=FALSE,0,COUNTIFS(I2:INDEX(I2:I$15,L2),I2,H2:INDEX(H2:H$15,L2),"GOOD",J2:INDEX(J2:J$15,L2),">="amp;J2,J2:INDEX(J2:J$15,L2),"<="amp;J2 7))
IF(H2=FALSE,0,COUNTIFS(I2:INDEX(I2:I$15,L2),I2,H2:INDEX(H2:H$15,L2),"OK",J2:INDEX(J2:J$15,L2),">="amp;J2,J2:INDEX(J2:J$15,L2),"<="amp;J2 7))
Примечание 1
16 во втором уравнении учитывает случай, когда после текущей строки больше нет строк с пометкой FALSE, поэтому сопоставление завершается неудачей. Это заставляет Countifs подсчитывать все, начиная с текущих строк и заканчивая концом данных.
Примечание 2 — расширение до большего диапазона данных
Вы должны быть в состоянии заменить цифру 16 на countA (I: I), размер данных плюс заголовки.
Не должно возникнуть проблем с использованием большего диапазона для индекса, например
=IF(H2=FALSE,0,COUNTIFS(I2:INDEX(I2:I$150000,L2),I2,H2:INDEX(H2:H$150000,L2),"GOOD",J2:INDEX(J2:J$150000,L2),">="amp;J2,J2:INDEX(J2:J$150000,L2),"<="amp;J2 7))
IF(H2=FALSE,0,COUNTIFS(I2:INDEX(I2:I$150000,L2),I2,H2:INDEX(H2:H$150000,L2),"OK",J2:INDEX(J2:J$150000,L2),">="amp;J2,J2:INDEX(J2:J$150000,L2),"<="amp;J2 7))
но увеличение диапазона поиска в СООТВЕТСТВИИ до 150 тыс. строк во второй формуле серьезно влияет на производительность при повторении 150 тыс. раз. Единственное решение, которое я могу придумать на данный момент, — это посмотреть, можно ли установить максимум на расстоянии от любого вхождения имени до следующего вхождения имени с FALSE рядом с ним.
Комментарии:
1. Привет, Том, пожалуйста, могу я спросить тебя, что означает число 16 здесь «16-ROW ()» =IFERROR (СОВПАДЕНИЕ (I2, K2: K $ 15,0)-1,16-ROW()) В случае, если у меня, например, 150 тысяч строк, должен ли я поместить туда 150 000-ROW ()? И в случае, если я добавлю больше данных позже, должен ли я по-прежнему использовать эту формулу? Спасибо
2. Я добавлю примечание к своему ответу.