Блог

Пример

Существует диапазон A, в котором хранится время и его значение, и он обновляется динамически (не в примере).

Из этого диапазона A я хочу создать динамический диапазон B для каждого дня и его максимального значения.

Filter() не работает с arrayformula, и я не знаю, работает ли запрос с ним тоже.

Вы можете сделать это просто с помощью запроса:

 =ArrayFormula(query({int(A4:A),B4:B},"select Col1,max(Col2) where Col2 is not null group by Col1 label Col1 'Date'"))
  

при условии, что вы соответствующим образом форматируете столбец даты в результате.

Редактировать

Чтобы удалить метки столбцов, просто поместите пустую строку, как показано ниже:

 =ArrayFormula(query({int(A4:A),B4:B},"select Col1,max(Col2) where Col2 is not null group by Col1 label Col1 '',max(Col2) ''"))
  

Вы можете попробовать это (протестировано в Excel, потому что ваш Google Sheet заблокирован, но он должен работать идеально)

=SUMPRODUCT(MAX(--($A$5:$A$31>D5)*--($A$5:$A$31<D5 1)*$B$5:$B$31))

Вот как это работает:

1. --($A$5:$A$31>D5) вернет массив из 1 и 0, если значение ячейки больше, чем ссылка на дату. Допустим, ссылка на дату равна 24/01/2020, тогда возвращаемый массив будет {1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}
2. --($A$5:$A$31<D5 1) будет сделано то же самое, но только если значение ячейки меньше d5 1 (на следующий день). Таким образом, для 24/01/2020 мы получим {1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
3. Сначала * будут умножены оба массива, поэтому {1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1} * {1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0} = {1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
4. Второй * умножит предыдущий массив на значения в диапазоне B5:B31 , это означает {1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0} * {0,333;0,667;0,667;0,667;0,667;0,667;0,667;0,667;0,667;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;0,667} = {0,333;0,667;0,667;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
5. MAX получит максимальное значение из предыдущего массива (в данном случае 0,667 )
6. SUMPRODUCT добавлена, чтобы мы могли работать с массивами. Обычный Max не сделал бы этого сам по себе.

ПРИМЕЧАНИЕ: Пожалуйста, обратите внимание, что моим десятичным разделителем является запятая, а разделителем аргументов — точка с запятой, поэтому, вероятно, вам нужно будет исправить это в соответствии с вашими языковыми настройками

попробуйте:

 =SORTN(SORT(FILTER({INT(A5:A), B5:B}, B5:B<>""), 1, 0, 2, 0), 9^9, 2, 1, 1)