Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Вывести минимальные и максимальные значения из промежутков последовательных чисел
 
#1
16.05.2025 12:58:31
Всем доброго времени суток.
Собственно сабж. В столбце А имеется список из последовательных чисел: 120,121,122 далее следует разрыв и идет число 124, означающее нарушение последовательности и завершение промежутка. С него же и начинается новый 124,125,126 и т.д. пока последовательность снова не нарушится.
В каждом промежутке надо найти начало и конец и прописать через тире. Например для первого это будет 120-122.
В приложенном файле два моих варианта.
Вопрос: можно ли сделать вариант покороче?
 
 
 
#2
16.05.2025 14:28:02
memo, вариант немного покороче:
Скрытый текст
...ну, и в свежих экселях поджать LET()ом
 
 
 
#3
16.05.2025 14:58:10
Для "свежих" Excel:
Код
=DROP(LET(
    nums, A2:A22,
    pos, SEQUENCE(ROWS(nums)),
    key, nums - pos,
    GROUPBY(
        key,
        nums,
        LAMBDA(g,
            IF(
                MIN(g) = MAX(g),
                TEXT(MIN(g), "0"),
                TEXT(MIN(g), "0") & "-" & TEXT(MAX(g), "0")
            )
        ),0,
        0
    )
),,1)
 
 
 
#4
16.05.2025 18:09:55
Цитата
поджать LET()ом
2021+:
д.массив
 
 
 
#5
16.05.2025 18:45:45
ну видимо почти тоже что и №2
=IFERROR(INDEX(A:A;SMALL(IF(A$2:A$23-A$1:A$22<>1;ROW(A$2:A$23));ROWS(H$2:H2)))&IF(SUM(SMALL(IF(A$2:A$23-A$1:A$22<>1;ROW(A$2:A$23));ROWS(H$2:H2)+{0\1})*{-1\1})=1;"";-INDEX(A:A;SMALL(IF(A$2:A$23-A$1:A$22<>1;ROW(A$2:A$23)-1);ROWS(H$2:H2)+1)));"")
По вопросам из тем форума, личку не читаю.
 
 
 
#6
16.05.2025 18:46:35
Павел \Ʌ/, Metanor,
Спасибо за варианты!  

Павел, понравился ваш вариант с отрицательными числами для старых версий.

БМВ, Благодарствую!
Изменено: memo - 16.05.2025 18:51:44
 
 
 
#7
17.05.2025 12:43:15
memo, пожалуста
Цитата
с отрицательными числами
натолкнуло на мысль об универсальности
т.е. добавить возможность работать с отрицательными числами, блоки могут идти не возрастанию, первое число может быть 1 (последнее -1), ссылка только на исходный диапазон...
решение правда несколько подросло:  )
формула
в том же 2021-ом экселе это всё - куда проще "обрулить":
д.массив
 
 
 
#8
17.05.2025 14:15:00
Павел \Ʌ/, Странно, но почему-то на 2013-ом офисе не взлетело.
Скрин
 
 
 
#9
17.05.2025 15:31:53
Цитата
memo написал:
надо найти начало и конец и прописать через тире
а если так? с доп.столбцом и результатом в 1 ячейку 1-й строкой?  8)
 
 
 
#10
17.05.2025 16:55:05
BodkhiSatva,
Все отлично. Только дома у меня по-прежнему Excel 2013-2016, где нет функции TEXTJOIN().
Сейчас пытаюсь разобраться, почему не работает последний вариант от Павел \Ʌ/,

UPDATE: Кажется разобрался. Дело в том, что ИНДЕКС в Excel 2013-2016 (не знаю, как в 2019) по-умолчанию не поддерживает массив строк (в качестве второго аргумента). Но, тем не менее есть некий трюк, который позволяет это сделать.

Вот такой вариант работает:
Код
=IFERROR(INDEX(B$2:B$22;AGGREGATE(15;6;ROW(B$2:B$22)/(B$2:B$22-INDEX(B$2:B$22;N(INDEX(ROW(B$2:B$22)-ROW(Q$2);)))<>1);ROW(Q1))-ROW(Q$2)+1)&IF(IFERROR(SUM(AGGREGATE(15;6;ROW(B$2:B$22)/(B$2:B$22-INDEX(B$2:B$22;N(INDEX(ROW(B$2:B$22)-ROW(Q$2);)))<>1);ROW(Q1:Q2))*{-1;1})-1;1);"_"&INDEX(B$2:B$22;AGGREGATE(15;6;ROW(B$2:B$22)/(IFERROR(INDEX(B$2:B$22;N(INDEX(ROW(B$2:B$22)-ROW(Q$2)+2;)));B$2:B$22)-B$2:B$22<>1);ROW(Q1))-ROW(Q$2)+1);"");"")


или так:
Код
=IFERROR(INDEX(B$2:B$22;SMALL(IF((B$2:B$22-INDEX(B$2:B$22;N(INDEX(ROW(B$2:B$22)-ROW(R$2);)))<>1);ROW(B$2:B$22));ROW(R1))-ROW(R$2)+1)&IF(IFERROR(SUM(SMALL(IF((B$2:B$22-INDEX(B$2:B$22;N(INDEX(ROW(B$2:B$22)-ROW(R$2);)))<>1);ROW(B$2:B$22));ROW(R1:R2))*{-1;1})-1;1);"_"&INDEX(B$2:B$22;SMALL(IF((IFERROR(INDEX(B$2:B$22;N(INDEX(ROW(B$2:B$22)-ROW(R$2)+2;)));B$2:B$22)-B$2:B$22<>1);ROW(B$2:B$22));ROW(R1))-ROW(R$2)+1);"");"")


(добавлен блок N(INDEX(ROW(B$2:B$22)-ROW(R$2))

Еще раз спасибо вам за этот вариант!
Изменено: memo - 17.05.2025 22:38:18
 
 
 
#11
19.05.2025 17:06:37
off
Цитата
memo,   почему не работает последний вариант...
Дело в том, что ИНДЕКС в Excel 2013-2016 (не знаю, как в 2019) по-умолчанию не поддерживает массив строк
сорян,  ...уже как то и не подумалось про это )  
но нужды в том не будет, если отбросить условие:
Цитата
ссылка только на исходный диапазон
 
 
 
Страницы: 1
Читают тему
Наверх