Страницы: 1
RSS
Перечислить числа, указанные в диапазоне a-b
 
Здравствуйте коллеги. Создаю первый раз тему на форуме, сильно не пинайте новичка.

Даны разные данные в ячейках, диапазоны чисел, например: 1996 - 2000, нужно отобразить эти данные в другой ячейке в таком виде: 1996, 1997, 1998, 1999, 2000

Подскажите пожалуйста, как правильно сделать?  
 
1. Руками?
2. Формулами?
3. Макросом?
4. Power Query?

P.s. мы обычно просим приложить к теме небольшой файл пример
Изменено: New - 11.10.2021 07:59:33
 
New, А зачем вопросы, ибо на вопрос
Цитата
lex Park написал:
как правильно сделать?
ответ
Цитата
New написал:
1. Руками
2. Формулами
3. Макросом
4. Power Query

при этом в зависимости от диапазона
Цитата
lex Park написал:
1996 - 2000
это может не реализуется вовсе,  если диапазон захватывает 6553 года. ну или реализуется только новых версиях Excel, или для буквально незначительных диапазонов классичискими функциями..
Изменено: БМВ - 11.10.2021 08:33:44
По вопросам из тем форума, личку не читаю.
 
Диапазон охватывает от 1977 до 2021

1. Руками сильно долго. Было бы с 5-10 строк я бы даже не заморачивался, но там 4000+ строк...
2. Не знаю какую именно формулу или функцию использовать.
3. Макросы - страшное, непонятное слово)))
4. Пойду гуглить

Коллеги, желательно это сделать формулой, так как руками делать 4392 строки не удобно, долго да  бессмысленно.
Вот пример данных какие мне необходимы:
Изменено: vikttur - 11.10.2021 09:05:35
 
Код
=ОБЪЕДИНИТЬ(", ";1;СОРТ(СЛМАССИВ(ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(D2;" — ";"</i><i>")&"</i></j>";"//i[2]")-ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(D2;" — ";"</i><i>")&"</i></j>";"//i[1]")+1;1;ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(D2;" — ";"</i><i>")&"</i></j>";"//i[1]");ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(D2;" — ";"</i><i>")&"</i></j>";"//i[2]");1)))
=ОБЪЕДИНИТЬ(", ";1;ПОСЛЕД(ПРАВСИМВ(D2;4)-ЛЕВСИМВ(D2;4)+1;1;ЛЕВСИМВ(D2;4);1))
Изменено: Тимофеев - 11.10.2021 09:11:02
 
Не работает эта формула Excell 2019
Изменено: vikttur - 11.10.2021 10:06:47
 
печальный эксель
 
БМВ, формулу не подскажете?
 
Код
=ОБЪЕДИНИТЬ(", ";1;ЕСЛИ(СТРОКА($A$1:$A$2021)*(СТРОКА($A$1:$A$2021)>=--ЛЕВСИМВ(D2;4))*(СТРОКА($A$1:$A$2021)<=--ПРАВСИМВ(D2;4))=0;"";СТРОКА($A$1:$A$2021)*(СТРОКА($A$1:$A$2021)>=--ЛЕВСИМВ(D2;4))*(СТРОКА($A$1:$A$2021)<=--ПРАВСИМВ(D2;4))))
Изменено: Тимофеев - 11.10.2021 10:07:09
 
Тимофеев, попробовал, пустая ячейка.
 
массивно
 
Можно такой формулой:
Код
=ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(I$1;ПОИСК(ПСТР(D2;3;2);I$1)-1;ПОИСК(ПСТР(D2;10;2);I$1)-ПОИСК(ПСТР(D2;3;2);I$1)+3);",";", 19");".";", 20");3;99) 

*Немного подкорректировал. Удлинил результат. Файл перезалил.

Изменено: Светлый - 11.10.2021 22:36:58
 
для периода в 7 лет и классических функций
=MID(TEXT(
SUMPRODUCT(RIGHT(LEFT(D2;4)+ROW(A$1:INDEX(A:A;RIGHT(D2;4)-LEFT(D2;4)+1))-1;2)*100^(RIGHT(D2;4)-LEFT(D2;4)-ROW(A$1:INDEX(A:A;RIGHT(D2;4)-LEFT(D2;4)+1))+1));REPT(""", "&LEFT(D2;2)&"""00";MIN(100-MOD(LEFT(D2;4);100);RIGHT(D2;4)-LEFT(D2;4)+1))&REPT(""", "&LEFT(RIGHT(D2;4);2)&"""00";RIGHT(D2;4)-LEFT(D2;4)+1-MIN(100-MOD(LEFT(D2;4);100);RIGHT(D2;4)-LEFT(D2;4)+1)));3;99)
По вопросам из тем форума, личку не читаю.
 
Мой вариант (максимально оптимизировал формулу, прежний вариант оставлять не вижу смысла):
Код
=TEXTJOIN(", ";1;IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDEX($A$1:$A$20000;MIN(0+LEFTB($D2;4));):INDEX($A$1:$A$20000;MAX(0+RIGHTB($D2;4));));NOT(ROW($A$1:$A$20000));0));ROW(INDEX($A$1:$A$20000;MIN(0+LEFTB($D2;4));):INDEX($A$1:$A$20000;MAX(0+RIGHTB($D2;4));)));ROW($A$1:$A$50));""))

Или короче, но отнюдь не лучше.
Код
=TEXTJOIN(", ";1;IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT(MIN(0+LEFTB($D2;4))&":"&MAX(0+RIGHTB($D2;4))));NOT(ROW($A$1:$A$20000));0));ROW(INDIRECT(MIN(0+LEFTB($D2;4))&":"&MAX(0+RIGHTB($D2;4)))));ROW($A$1:$A$50));""))

Не мерял как там по производительности, но по краткости и читаемости the winner однозначно код от Тимофеев.
Можно даже чуточку сократить, совсем будет конфетка.
Код
=TEXTJOIN(", ";1;IFERROR(1/(1/ROW($A$1:$A$2021)*(ROW($A$1:$A$2021)>=--LEFT(D2;4))*(ROW($A$1:$A$2021)<=--RIGHT(D2;4)));""))

З.Ы. Поскольку у меня нет TEXTJOIN воспользовался найденной в сети UDF (источник в ней же и указан).
Изменено: memo - 11.10.2021 16:23:54
 
Цитата
lex Park написал: Поробовал, пустая ячейка.
Должно работать в 2019 (пример из #10 ) - вводите как формулу массива (Ctrl+Shift+Enter одновременно).
Владимир
 
Всем спасибо за участие.
 
Цитата
memo написал:
но по краткости и читаемости the winner однозначно код от  Тимофеев .
ну ну
=TEXTJOIN(", ";1;ROW(INDEX(A:A;LEFT(D2;4)):INDEX(A:A;RIGHT(D2;4))))  :D

с textjoinто каждый может :-)
Изменено: БМВ - 11.10.2021 17:58:55
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
с textjoinто каждый может
Ну классно же)) И это ничуть не умаляя ваши заслуги, как замечательного формулиста :)
 
memo,  UDF - это почти макрос а так
Цитата
БМВ написал:
ну или реализуется только новых версиях Excel,
. Интереснее без оного.
Если б не  длинная последовательность, которую в фомулу воткнуть и будет очень длинно и ограничение в 199, то вариант №12 интересен, мой изначально ориентирован на пример и редко машины выпускались более 7 лет.
по этому должно  хватить.
По вопросам из тем форума, личку не читаю.
 
Или можно немного формулы сократить:
Код
=ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(I$1;(ЛЕВБ(D2;4)-1970)*3+1;(ПРАВБ(D2;4)-ЛЕВБ(D2;4)+1)*3);",";", 19");".";", 20");3;999)
и без дополнительной ячейки:
Код
=ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(",70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99.00.01.02.03.04.05.06.07.08.09.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30";(ЛЕВБ(D2;4)-1970)*3+1;(ПРАВБ(D2;4)-ЛЕВБ(D2;4)+1)*3);",";", 19");".";", 20");3;999) 

*И немного сокращённая формула БМВ.

Код
=ПСТР(ТЕКСТ(СУММ(ЕСЛИОШИБКА(ПРАВБ(СТРОКА(ИНДЕКС(A:A;ЛЕВБ(D2;4)):ИНДЕКС(A:A;ПРАВБ(D2;4)));2)*100^-СТРОКА($1:$7);));ПОВТОР(",\ 1900";ТЕКСТ(МИН(7;2000-ЛЕВБ(D2;4);ПРАВБ(D2;4)-ЛЕВБ(D2;4)+1);"0;\0"))&ПОВТОР(",\ 2\000";МИН(7;ПРАВБ(D2;4)-ЛЕВБ(D2;4)+1)-ТЕКСТ(МИН(7;2000-ЛЕВБ(D2;4);ПРАВБ(D2;4)-ЛЕВБ(D2;4)+1);"0;\0")));3;99)
Изменено: Светлый - 12.10.2021 07:57:29
 
Код
=ОБЪЕДИНИТЬ(",";;СТРОКА(ДВССЫЛ("A"&--ЛЕВСИМВ(D2;4)&":A"&--ПРАВСИМВ(D2;4))))
=ОБЪЕДИНИТЬ(",";;СТРОКА(ДВССЫЛ("A"&--ЛЕВБ(D2;4)&":A"&--ПРАВБ(D2;4))))
Изменено: Тимофеев - 12.10.2021 09:35:32
 
Тимофеев,  а вот отправлять в полет совершенно лишнее.
По вопросам из тем форума, личку не читаю.
 
Цитата
Светлый написал:
*И немного сокращённая формула БМВ.
На региональных настройках US не сработала.
Скрин
 
Цитата
memo написал:
На региональных настройках US не сработала.
Проверить не могу. М.б. что-то с шаблоном функции ТЕКСТ.
Формулу ещё сократил:

Код
=ПСТР(ТЕКСТ(СУММ(ЕСЛИОШИБКА(ПРАВБ(СТРОКА(ДВССЫЛ(ЛЕВБ(D2;4)&":"&ПРАВБ(D2;4)));2)/100^СТРОКА($1:$7);));ПОВТОР(",\ 1900";ТЕКСТ(МИН(7;2000-ЛЕВБ(D2;4);ПРАВБ(D2;4)-ЛЕВБ(D2;4)+1);"0;\0"))&ПОВТОР(",\ 2\000";ТЕКСТ(МИН(7;МИН(ПРАВБ(D2;4);ЛЕВБ(D2;4)+7)-1999;ПРАВБ(D2;4)-ЛЕВБ(D2;4)+1);"0;\0")));3;99) 
*Тогда с региональными:

Код
=ПСТР(ТЕКСТ(СУММ(ЕСЛИОШИБКА(ПРАВБ(СТРОКА(ДВССЫЛ(ЛЕВБ(D2;4)&":"&ПРАВБ(D2;4)));2)/100^СТРОКА($1:$7);));ПСТР(1/2;2;1)&ПОВТОР(",\ 1900";ТЕКСТ(МИН(7;2000-ЛЕВБ(D2;4);ПРАВБ(D2;4)-ЛЕВБ(D2;4)+1);"0;\0"))&ПОВТОР(",\ 2\000";ТЕКСТ(МИН(7;МИН(ПРАВБ(D2;4);ЛЕВБ(D2;4)+7)-1999;ПРАВБ(D2;4)-ЛЕВБ(D2;4)+1);"0;\0")));4;99)

Файл перезалил.

Изменено: Светлый - 12.10.2021 10:26:57
 
Цитата
Светлый написал:
*Тогда с региональными:
С региональными съедается единица в начале, да и запятые пропали.
С региональными

Добавил ПОДСТАВИТЬ() работает, но опять без запятых.
Без запятых

Ну и в обоих случаях диапазон 1975-2020 обрывается на 81 году.
 
Цитата
memo написал:
Ну и в обоих случаях диапазон 1975-2020 обрывается на 81 году.
Цитата
БМВ написал:
для периода в 7 лет и классических функций
Дальше будет врать.
Попробуйте перед запятой поставить обратный слэш.
Код
=ПСТР(ТЕКСТ(СУММ(ЕСЛИОШИБКА(ПРАВБ(СТРОКА(ДВССЫЛ(ЛЕВБ(D2;4)&":"&ПРАВБ(D2;4)));2)/100^СТРОКА($1:$7);));ПСТР(1/2;2;1)&ПОВТОР("\,\ 1900";ТЕКСТ(МИН(7;2000-ЛЕВБ(D2;4);ПРАВБ(D2;4)-ЛЕВБ(D2;4)+1);"0;\0"))&ПОВТОР("\,\ 2\000";ТЕКСТ(МИН(7;МИН(ПРАВБ(D2;4);ЛЕВБ(D2;4)+7)-1999;ПРАВБ(D2;4)-ЛЕВБ(D2;4)+1);"0;\0")));4;99)
 
Цитата
Светлый написал:
Дальше будет врать.
Точно, забыл.
Цитата
обратный слэш
Сработало!
 
Код
=ОБЪЕДИНИТЬ(", ";1;МОДА.НСК(ПОСЛЕД(ПРАВБ(F2;4)-ЛЕВБ(F2;4)+1;1;ЛЕВБ(F2;4);1);СТРОКА(A1:A2021)))
=ОБЪЕДИНИТЬ(", ";1;НАИМЕНЬШИЙ(СТРОКА(A1:A2021);ПОСЛЕД(--ПРАВБ(F2;4)-ЛЕВБ(F2;4)+1;1;--ЛЕВБ(F2;4);1)))
Изменено: Тимофеев - 12.10.2021 12:50:35
 
Цитата
БМВ написал:
для периода в 7 лет и классических функций
А что, перебор уже под запретом?
Код
=ЛЕВБ(D2;4)
& ЕСЛИ(ЛЕВБ(D2;4)+1<=--ПРАВБ(D2;4); ", " & ЛЕВБ(D2;4)+1;)
& ЕСЛИ(ЛЕВБ(D2;4)+2<=--ПРАВБ(D2;4); ", " & ЛЕВБ(D2;4)+2;)
& ЕСЛИ(ЛЕВБ(D2;4)+3<=--ПРАВБ(D2;4); ", " & ЛЕВБ(D2;4)+3;)

и т.д.
Изменено: sokol92 - 12.10.2021 13:16:48
Владимир
 
Цитата
sokol92 написал:
А что, перебор уже под запретом?
это не интересно.  :D
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх