У меня есть XLS длинных чисел, которые должны быть последовательными / смежными, однако они не всегда — это означает, что данные могут быть 1 2 3 5 6, где 4 пропущено. Мне нужно определить начало / конец каждой последовательной группировки (диапазон данных) — 1-3 и 5-6. Пока что я создал формулы, которые работают, однако у меня есть 2 вспомогательные строки для этого. Есть ли более элегантное / эффективное решение, не требующее вспомогательных столбцов? В общем, шаги следующие:
Импортируйте данные на отдельный рабочий лист.
Отсортируйте импортированные данные с помощью формулы.
Выполните простое вычитание текущей ячейки за вычетом ячейки над ней.
Каждая строка, в которой отличие от № 3 не равно 1, генерирует новый номер группы для идентификации каждой последовательной группировки.
Рассчитайте MIN / MAX. У меня 2 рабочих варианта:
5.a МАКС (ЕСЛИ (…. и МИН (ЕСЛИ (….=MAX(IF($H$5:$I$14=ROW(H1),$E$5:$E$14)) =MIN(IF($H$5:$I$14=ROW(I1),$E$5:$E$14))
5.b АГРЕГАТ
=AGGREGATE(14,6,($E$5:$E$14)/($H$5:$H$14=ROW(H1)),1)
=AGGREGATE(15,6,($E$5:$E$14)/($H$5:$H$14=ROW(I1)),1)
Шаги 3-5 показаны ниже на изображении фиктивных данных.
Опять же, ищем способ избавиться от вспомогательных столбцов («Разница последнего значения» и «Группировка непрерывных диапазонов»).
Если есть формула, которая не требует, чтобы данные сначала сортировались, это было бы необычно. В настоящее время используется эта формула для сортировки импортированных данных:
=IFERROR(SMALL($L$7:$L$640,ROWS($N$6:N6)),"")
Если импортированные данные находятся в столбце L, а формула — в столбце N.
РЕКОМЕНДАЦИИ
Выберите максимум каждой группы с соответствующими значениями
Макс. / Мин. ЕСЛИ // Агрегат.
Считайте смежные значения
Как создать MINIF и MAXIF