Получение всех возможных значений из нескольких диапазонов

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Получение всех возможных значений из нескольких диапазонов (Формулы/Formulas)

agamemnon

Дата: Среда, 29.03.2023, 18:53 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

2016

Здравствуйте! Помогите с решением следующей проблемы. Есть два столбца с начальным значением и конечным:

281359 281359
281363 281366
281376 281378

Шаг возможных значений в диапазоне равен 1. Как в итоге получить следующий столбец со значениями:

281359
281363
281364
281365
281366
281376
281377
281378

Ответить

DrMini

Дата: Среда, 29.03.2023, 19:53 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 1877

Репутация: 269 ±

Замечаний: 0% ±

Excel LTSC 2024 RUS

Цитата agamemnon, 29.03.2023 в 18:53, в сообщении № 1 (

)

Есть два столбца с начальным значением и конечным

Вот такое на ум пришло.

К сообщению приложен файл: min_maks.xlsx (9.5 Kb)

Ответить

Pelena

Дата: Среда, 29.03.2023, 19:56 | Сообщение № 3

Группа: Админы

Ранг: Местный житель

Сообщений: 19405

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

Ещё вариант

Код

=ЕСЛИОШИБКА(ЕСЛИ(E2<ВПР(E2;$A$2:$B$4;2);E2+1;ИНДЕКС($A$2:$A$4;ПОИСКПОЗ(E2;$A$2:$A$4)+1));"")

К сообщению приложен файл: agamemnon.xlsx (10.1 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

agamemnon

Дата: Среда, 29.03.2023, 21:49 | Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

2016

Цитата DrMini, 29.03.2023 в 19:53, в сообщении № 2 (

)

Вот такое на ум пришло.

Наверное не совсем понятно объяснил: столбцов два, но диапазонов в примере 3. И в выходном столбце должны быть значения входящие в эти 3 диапазона.

Цитата Pelena, 29.03.2023 в 19:56, в сообщении № 3 (

)

Ещё вариант

=ЕСЛИОШИБКА(ЕСЛИ(E2<ВПР(E2;$A$2:$B$4;2);E2+1;ИНДЕКС($A$2:$A$4;ПОИСКПОЗ(E2;$A$2:$A$4)+1));"")

Спасибо большое! Это было то что нужно! hands

Давно уже ломал голову как получить искомый результат. Сам бы я не сообразил. Спасибо ещё раз!

Ответить

Gustav

Дата: Среда, 29.03.2023, 21:59 | Сообщение № 5

Группа: Админы

Ранг: Участник клуба

Сообщений: 2797

Репутация: 1161 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

И мой заключительный выход как дежурного по новым функциям свежих версий Excel (2021+, 365, web) - формула динамического саморасширяющегося массива (для одной-единственной ячейки):

Код

=ТРАНСП(--ТЕКСТРАЗД(ОБЪЕДИНИТЬ(",";; BYROW(A1:B3; LAMBDA(r; ОБЪЕДИНИТЬ(",";; ПОСЛЕД(1; ИНДЕКС(r;2)-ИНДЕКС(r;1)+1; ИНДЕКС(r;1)))))); ","))

Вводим в C1 - ловим в C1:C8.

P.S. Либо чуть покороче, но придётся вместо одного диапазона A1:B3 вводить два - A1:A3 и B1:B3:

Код

=ТРАНСП(--ТЕКСТРАЗД(ОБЪЕДИНИТЬ(",";; MAP(A1:A3; B1:B3; LAMBDA(a;b; ОБЪЕДИНИТЬ(",";; ПОСЛЕД(1; b-a+1; a))))); ","))

Либо чуть подлиннее, но с принципиально другим механизмом получения результата - с накоплением итоговой строки, используя функцию REDUCE:

Код

=ТРАНСП(--ТЕКСТРАЗД(REDUCE(; BYROW(A1:B3; LAMBDA(r;ОБЪЕДИНИТЬ(",";; ПОСЛЕД(1; ИНДЕКС(r;2)-ИНДЕКС(r;1)+1; ИНДЕКС(r;1))))); LAMBDA(a;b; СЦЕП(a;",";b))); ","))

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Среда, 29.03.2023, 22:14

Ответить

agamemnon

Дата: Среда, 29.03.2023, 22:25 | Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

2016

Цитата Gustav, 29.03.2023 в 21:59, в сообщении № 5 (

)

=ТРАНСП(--ТЕКСТРАЗД(ОБЪЕДИНИТЬ(",";; BYROW(A1:B3; LAMBDA(r; ОБЪЕДИНИТЬ(",";; ПОСЛЕД(1; ИНДЕКС(r;2)-ИНДЕКС(r;1)+1; ИНДЕКС(r;1)))))); ","))

Вводим в C1 - ловим в C1:C8.

Выдаёт ошибку

Попробовал все три предложенных варианта - везде Excel выдал ошибку. Может подобные решения не работают в Excel 2016?

К сообщению приложен файл: 2119951.png (14.1 Kb)

Сообщение отредактировал agamemnon - Среда, 29.03.2023, 22:32

Ответить

bigorq

Дата: Среда, 29.03.2023, 22:32 | Сообщение № 7

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 197

Репутация: 47 ±

Замечаний: 0% ±

Нет

Цитата agamemnon, 29.03.2023 в 22:25, в сообщении № 6 (

)

Выдаёт ошибку

у вас в профиле офис 2016 указан, в нем нет ряда новых функций

Ответить

Gustav

Дата: Среда, 29.03.2023, 22:53 | Сообщение № 8

Группа: Админы

Ранг: Участник клуба

Сообщений: 2797

Репутация: 1161 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Цитата agamemnon, 29.03.2023 в 22:25, в сообщении № 6 (

)

Может подобные решения не работают в Excel 2016?

Да, не всё из перечисленного работает в 2016. Я же написал: 2021+. А 2016 - это, увы, в противоположную сторону: 2021-. Если интересно, можете в таблицах Google потренироваться. Там всё работает. Правда, некоторые функции немного иначе называются. Из наиболее заметных несоответствий: JOIN и SPLIT в Гугл вместо TEXTJOIN и TEXTSPLIT в Excel.

МОИ: Ник, Tip box: 41001663842605

Ответить

Nic70y

Дата: Четверг, 30.03.2023, 07:58 | Сообщение № 9

Группа: Друзья

Ранг: Экселист

Сообщений: 9005

Репутация: 2369 ±

Замечаний: 0% ±

Excel 2010

немассивный вариант по файлу от Елены

Код

=ЕСЛИ(ВПР(E2;A:B;2)=E2;ИНДЕКС(A:A;ПОИСКПОЗ(E2;A:A)+1);E2+1)

апдейт
написал в принципе ту же самую формулу, что и у Елены,
только обратил внимание

видать она по инерции ввела ее как массивную.

К сообщению приложен файл: 149.xlsx (9.3 Kb)

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Четверг, 30.03.2023, 08:17

Ответить

Pelena

Дата: Четверг, 30.03.2023, 09:48 | Сообщение № 10

Группа: Админы

Ранг: Местный житель

Сообщений: 19405

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

Цитата Nic70y, 30.03.2023 в 07:58, в сообщении № 9 (

)

по инерции ввела ее как массивную

хм, интересно...
У меня офис 365, поэтому я в принципе не делаю массивный ввод формул. Сейчас проверила у себя файл, нет фигурных скобок. Загрузила ещё раз с форума - нет фигурных скобок.
Видимо, Excel "умничает", ему показалось, что формула массивная

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Получение всех возможных значений из нескольких диапазонов (Формулы/Formulas)

Страница 1 из 1
1