Есть ли способ заполнить пропущенные даты и добавить соответствующее значение 0 в Excel?
У меня есть данные об осадках во временном ряду, который я пытаюсь построить в Excel. Это выглядит так:
В этом формате данные пропускают дни, когда не было дождя, то есть 2 и 4 февраля. Это важно показать на сюжете. Есть ли способ заполнить эти пробелы на временной шкале, а затем заполнить другой столбец 0?
Ответы 2
=HSTACK(SEQUENCE(MAX(A:A)-MIN(A:A)+1,1,MIN(A:A),1),BYROW(SEQUENCE(MAX(A:A)-MIN(A:A)+1,1,MIN(A:A),1),LAMBDA(x,IFNA(VLOOKUP(x,A1:B5,2,FALSE),0))))
или
=LET(a,SEQUENCE(MAX(A:A)-MIN(A:A)+1,1,MIN(A:A),1),HSTACK(a,BYROW(a,LAMBDA(x,IFNA(VLOOKUP(x,A1:B5,2,FALSE),0)))))
С идеей от @David Leal использовать TAKE для использования в MIN, MAX, поэтому в качестве аргумента требуется только один диапазон:
=LET(a,A1:B5,b,TAKE(a,,1),c,SEQUENCE(MAX(b)-MIN(b)+1,1,MIN(b),1),HSTACK(c,BYROW(c,LAMBDA(x,IFNA(VLOOKUP(x,a,2,FALSE),0)))))
Результат:
Или просто перетащите дату от даты начала вниз и используйте формулу VLOOKUP, также перетащенную вниз:
=IFNA(VLOOKUP(D1,$A$1:$B$5,2,FALSE),0)
Результат:
(В примерах используется формат даты yyyy/mm/dd)
Можно попробовать следующее, поставить в ячейку D1 следующую формулу (формула 1):
=LET(AB,A1:B5, A,TAKE(AB,,1), md,MIN(A), d,SEQUENCE(MAX(A)-md+1,,md),
IFERROR(INDEX(AB,XMATCH(d,A),{1,2}),HSTACK(d,N(d<0))))
или используя XLOOKUP следующим образом:
=LET(AB,A1:B5, A,TAKE(AB,,1), md,MIN(A), d,SEQUENCE(MAX(A)-md+1,,md),
IFERROR(HSTACK(d,XLOOKUP(d,A,DROP(AB,,1))),HSTACK(d,N(d<0))))
Обновлять
Как отметил @VBasic2008 в разделе комментариев, для подхода XLOOKUP можно упростить использование 4-го входного аргумента следующим образом:
=LET(AB,A1:B5, A,TAKE(AB,,1), md,MIN(A), d,SEQUENCE(MAX(A)-md+1,,md),
HSTACK(d,XLOOKUP(d,A,DROP(AB,,1),0)))
Комментарий о работоспособности обоих вариантов XLOOKUP и INDEX/XMATCH. Обе функции могут быть оптимизированы для больших наборов данных, вызывающих бинарный поиск, поскольку даты (lookup_array) сортируются в порядке возрастания, поэтому мы можем установить входной аргумент search_mode в 2 в обоих случаях для XMATCH и XLOOKUP соответственно. С точки зрения производительности для 631K строк с использованием search_mode в обоих случаях:
INDEX/XMATCHверсия:1.5secsв среднемXLOOKUPверсия:1secв среднем
что ожидается, поскольку в первой версии есть дополнительный вызов функции: IFERROR. Не имея такого дополнительного вызова, обе версии имеют одинаковую производительность, т.е. INDEX/XMATCH против XLOOKUP.
Для формулы 1 он генерирует все ожидаемые даты (d), затем идентифицирует строки из ввода AB, где находятся даты (если не возвращает {#N/A,#N/A} в данной строке). Мы используем IFERROR для получения вывода. Мы генерируем постоянный массив нулевых значений, используя следующий трюк N(d<0), потому что даты в Excel всегда являются положительными числами.
Вот результат:
Вот что я сделал: =LET(Dates,A2:A21,Rain,B2:B21,m,MIN(Dates),s,SEQUENCE(MAX(Dates)-m+1,,m),HSTACK(s,XLOOKUP(s,Dates,Rain,0))). Таким образом, я позволяю столбцам не быть смежными и избавляю себя от дел TAKE, DROP или INDEX. Трудно определить входы.
хороший улов @ VBasic2008 Я просто адаптировал предыдущий подход к XLOOKUP, не упрощая его, как вы. Следуя вашему предложению, это будет формула: =LET(AB,A1:B5, A,TAKE(AB,,1), md,MIN(A), d,SEQUENCE(MAX(A)-md+1,,md), HSTACK(d,XLOOKUP(d,A,DROP(AB,,1),0))). Дайте мне знать, если вы хотите, чтобы я обновил свой вопрос и отдал вам должное, или если вы предпочитаете опубликовать свой подход. Спасибо
Вот почему я разместил комментарий. Всегда не стесняйтесь обновлять, используя любое из моих предложений. Вы просто забыли об удобном 4-м XLOOKUP аргументе.
@ VBasic2008 Спасибо, я понял, что набор входных данных (даты) отсортирован в порядке возрастания, поэтому для большого набора данных было бы лучше в обоих случаях XMATCH и XLOOKUP использовать входной аргумент search_mode и установить его в 2.
Большой! Я не понимаю
HSTACK(d,N(d<0))в версииXLOOKUP. Вы не можете простоHSTACK(d,XLOOKUP(d,A,DROP(AB,,1),0))обойтись без него и безIFERROR?