Использование VSTACK для накопления результатов
Я пытаюсь решить то, что должно быть простой проблемой.
На картинке ниже я показываю, что я пытаюсь сделать, и что у меня не получается.
Думаю, что диапазон «ожидаемого результата» говорит сам за себя.
Решение проблемы для одной строки (A) работает отлично. Решение проблемы для одной строки с помощью функции BYROW (B) не работает, и я не могу понять, почему это не работает. Решение задачи для нескольких строк (С) было для меня кошмаром, и я до сих пор не знаю, как получить желаемый результат.
Я думаю, что формула для случая (C) должна подразумевать функции LAMBDA, VSTACK и LET, но не могу ее получить.
Может ли кто-нибудь дать мне подсказку?
Я ищу решение с динамическими массивами.
Ответы 3
=LET(n,B6:B8,
c,C6:C8,
TOCOL(MAKEARRAY(ROWS(n),MAX(n),LAMBDA(x,y,IFS(INDEX(c,x)>=y,INDEX(n,x)))),2))
Он перебирает числа в n и повторяет его, если номер столбца массива, созданного MAKEARRAY (размер максимального числа в диапазоне b).
Если номер столбца текущей строки больше, чем номер n текущей строки, происходит ошибка, в противном случае возвращается n текущей строки.
TOCOL выравнивает массив, удаляя ошибки.
Альтернативно (добавлено в двоичный поиск благодаря объяснению производительности VBasics): =XLOOKUP(SEQUENCE(SUM(C6:C8)),SCAN(,C6:C8,LAMBDA(x,y,x+y)),B6:B8,,1,2)
Или, если вы собираетесь использовать VSTACK:
=LET(n,B6:B8,DROP(REDUCE("",SEQUENCE(ROWS(n)),LAMBDA(x,y,VSTACK(x,IF(SEQUENCE(INDEX(C6:C8,y)),INDEX(n,y))))),1))
Это просто и идеально!
Производительность этих формул, примененных к большому набору данных, ужасна. Брали двойки: MAKEARRAY всего за 300, REDUCE за 2500 и XLOOKUP за 3000 записей (стеков). Данные были =RANDARRAY(100000,,1000,9999,1), а повторы =RANDARRAY(100000,,1,20,1) были скопированы как значения. С REDUCE ничего не поделаешь, он медленный. MAKEARRAY, самое худшее, становится полной неожиданностью (для меня). Наконец, вы можете «сэкономить» XLOOKUP, установив для 6-го параметра значение 2 (двоичный ASC), когда он работает так же, как TOCOL(IFS(SEQUENCE...: 100 тыс. записей (+1 млн результирующих строк) менее чем за секунду.
@ VBasic2008 Я знаю, что REDUCE VSTACK - это медленная комбинация, но я добавил ее, поскольку о ней упоминалось в вопросе. Я ожидал, что makearray не будет медленным. Мне также интересно, насколько предложенный XLOOKUP работает по сравнению с предложенными вами решениями.
О DROP(REDUCE/VSTACK: Как только вы поймете, как им пользоваться, вы сможете «делать все что угодно». Ты начнешь суетиться как сумасшедший. Как только вы столкнетесь с достаточно большим набором данных, уже не так много. Тогда вам захочется найти более эффективные решения. Тем не менее, по моему мнению, это лучшая функция Excel. Представьте, что вам нужно срочно что-то решить, и вы не в силах разобраться в «глубокой математике», стоящей за этим. Затем вы просто воспользуетесь DROP(REDUCE("",SEQUENCE(10),LAMBDA(rr,r,VSTACK(rr,r))),1) и замените 10 и r на все, что вам нужно, и получите результат за считанные минуты...
... Это как VLOOKUP («все» знают, как им пользоваться) с той ключевой разницей, что «он может все» (пока не «все» знают, как им пользоваться). ИМО, любое решение, использующее его, действительно (вы не видите, чтобы я голосовал против). Кстати, я имел в виду «вы знаете, это медленно», но мне не хватило символов.
О XLOOKUP/SCAN: Просто добавив ,2 после 1, вы заставите свою медленную формулу работать с той же «молниеносной скоростью», что и моя TOCOL(IFS(SEQUENCE(. Я был шокирован, когда понял, что это так быстро, что мне придется изучить его подробно. О MAKEARRAY: Я ожидал, что он будет как минимум в два раза медленнее моей формулы, но был разочарован, когда он оказался намного медленнее, чем REDUCE. Иногда перестановка функций имеет огромное значение. Не уверен, применимо ли это здесь. На данный момент я точно не знаю, как это работает, но мне тоже придется это изучить.
Еще один способ EXPAND:)
=LAMBDA(data,
REDUCE(
"Result",
SEQUENCE(ROWS(data)),
LAMBDA(acc, cur,
LET(
val, INDEX(data, cur, 1),
times, INDEX(data, cur, 2),
VSTACK(acc, EXPAND(val, times, , val))
)
)
)
)(A2:B4)
Кроме того, поскольку вам нравится использовать LAMBDA и т. д., возможно, вы захотите посмотреть Excel Labs | Microsoft Garage, если вы еще этого не сделали.
Повторить массив по вертикали (TOCOL(IFS(SEQUENCE...)
Повторить столбец
- На основе ответа Маюха Бхаттачарьи на вопрос «Как преобразовать столбец возраста в набор, полезный для статистики?»:
=LET(data,A2:A4,repeats,B2:B4,
TOCOL(IFS(SEQUENCE(,MAX(repeats))<=repeats,data),2))
Повторить транспонированный ряд
- На основе ответа JvdV на вопрос «Повторяющиеся значения превосходят»:
=LET(data,B1:D1,repeats,B2:D2,
TOCOL(IFS(SEQUENCE(MAX(repeats))<=repeats,data),2,1))
Оба + дополнительный HSTACK (функция LAMBDA)
- Используйте следующее в диспетчере имен, чтобы создать функцию LAMBDA, например.
RepeatArray:
=LAMBDA(data,repeats,[h_stack],LET(
h,IF(ISOMITTED(h_stack),0,h_stack),
a,ROWS(data)=1,
m,MAX(repeats),
s,IFS(SEQUENCE(IF(a,m,1),IF(a,1,m))<=repeats,data),
IF(h,TOROW(s,2,a),TOCOL(s,2,a))))
- В любом месте книги теперь вы можете использовать
=RepeatArray(A2:A4,B2:B4)для вертикального расположения данных или=RepeatArray(A2:A4,B2:B4,1)для горизонтального расположения данных. То же самое касается и вертикальных входов.
Чтобы выбрать столбец, попробуйте
INDEX(r;;2).