Оригинал статьи: https://www.benlcollins.com/spreadsheets/new-analytical-functions-2023/
Следуя по пятам за прошлогодней серией новых лямбда-функций, Google недавно анонсировала еще одну группу новых аналитических функций для Sheets.
В этот новый пакет включены долгожданная функция LET, 8 новых функций управления массивом, новая статистическая функция и новая функция datetime.
Давайте начнем с рассмотрения новых функций массива. Функция LET находится в конце поста.
Новые функции манипулирования массивами
Хотя появилось восемь новых функций управления массивами, их можно рассматривать как четыре пары функций горизонтального / вертикального массива.
Хотя большая часть такого поведения уже была возможна с существующими функциями, эти новые функции упрощают синтаксис и поэтому являются долгожданным обновлением. Они также поддерживают равенство функций с Excel, что поможет людям из этого мира.
Функция TOROW
TOROW преобразует диапазон в одну строку.
Эта формула преобразует входной массив в формате A1: C2 в одну строку:
=TOROW(A1:C2)
В нем есть необязательные аргументы, которые определяют, как обрабатывать пустые ячейки или значения ошибок, а также следует ли сканировать столбцы вниз или по строкам при сканировании диапазона ввода.
Дополнительная информация в документации Google.
Функция TOCOL
Функция TOCOL преобразует диапазон в один столбец. Она ведет себя так же, как функция FLATTEN.
Эта формула в ячейке A5 преобразует каждую строку в формат столбца и помещает их одну поверх другой:
=TOCOL(A1:C2)
Как и функция TOROW, она имеет необязательные аргументы, которые определяют, как обрабатывать пустые ячейки или значения ошибок, а также следует ли сканировать столбцы вниз или по строкам при сканировании диапазона ввода.
Дополнительная информация в документации Google.
Функция CHOOSEROWS
Учитывая диапазон данных, функция CHOOSEROWS позволяет выбирать строки по номеру строки.
Например, в этой формуле выбираются первая, вторая и четвертая строки:
=CHOOSEROWS(A1:C5,1,2,4)
Этого, конечно, также можно достичь с помощью функции FILTER или функции QUERY. Тем не менее, эта новая функция является удобной альтернативой для случаев, когда вы знаете номера строк и вам не нужно выполнять условный тест для каждой строки.
Дополнительная информация в документации Google.
Функция CHOOSECOLS
CHOOSECOLS - долгожданное дополнение к семейству функций.
Это позволяет выбирать определенные столбцы из диапазона, что ранее требовало функции запроса и было более неудобным, поскольку для столбцов использовались буквенные ссылки.
В этом примере выбираются первый и третий столбцы из диапазона ввода:
=CHOOSECOLS(A1:C5,1,3)
Дополнительная информация в документации Google.
Функция WRAPROWS
WRAPROWS берет одномерный диапазон (строку или столбец) и преобразует его в двухмерный диапазон путем обертывания строк.
Для заполнения любых дополнительных ячеек требуется три аргумента: 1) диапазон ввода, 2) количество переносов, представляющее собой максимальное количество элементов в новых строках, и 3) значение pad.
В этом примере формула разбивает первую строку на несколько строк с максимальным количеством двух элементов в каждой строке. Обратите внимание на вторую запятую в формуле. При этом значение pad становится пустым, что означает, что ячейка B8 пуста.
=WRAPROWS(A1:I1,2,)
Дополнительная информация в документации Google.
Функция WRAPCOLS
WRAPCOLS берет одномерный диапазон (строку или столбец) и преобразует его в двухмерный диапазон путем обертывания столбцов.
Для заполнения любых дополнительных ячеек требуется три аргумента: 1) диапазон ввода, 2) количество переносов, представляющее собой максимальное количество элементов в новых столбцах, и 3) значение pad.
В этом примере формула разбивает первую строку на несколько столбцов, в каждом столбце должно быть не более двух элементов. Обратите внимание на вторую запятую в формуле. При этом значение pad становится пустым, что означает, что ячейка E4 пуста.
=WRAPCOLS(A1:I1,2,)
Дополнительная информация в документации Google.
Функция VSTACK
Функция VSTACK объединяет диапазоны данных по вертикали.
Например, вы могли бы использовать VSTACK для простого объединения двух (или более) наборов данных:
=VSTACK(A1:B5,D2:E5)
Он берет данные в диапазоне A1: B5 (который включает строку заголовка) и добавляет данные из D2: E5 снизу, так что у вас есть все это в одной таблице:
Дополнительная информация в документации Google.
Функция HSTACK
HSTACK объединяет диапазоны данных по горизонтали.
Например, эта формула в A14 объединяет три диапазона данных по горизонтали:
=HSTACK(A1:A5,C3:C7,A7:A11)
Дополнительная информация в документации Google.
Функция MARGINOFERROR
Функция MARGINOFERROR вычисляет предел погрешности для диапазона значений при заданном уровне достоверности.
Для этого требуется два аргумента: 1) диапазон значений и 2) уровень достоверности.
Давайте используем этот набор данных в качестве примера:
Функция MARGINOFERROR - это:
=MARGINOFERROR(C2:C11,0.99)
которые вычисляют предел погрешности с уровнем достоверности 99%:
Возможно, это легче понять на графике, показывающем пределы погрешности (1 стандартное отклонение) и среднее значение (красная линия):
Дополнительная информация в документации Google.
Функция EPOCHTODATE
EPOCHTODATE преобразует временную метку эпохи Unix в обычную дату-время (в универсально координированном часовом поясе UTC).
Для этого требуется два аргумента: 1) временная метка эпохи Unix и 2) необязательный аргумент unit.
Временная метка Unix выглядит следующим образом:
1676300687
Функция EPOCHTODATE принимает это в качестве входных данных (например, временная метка Unix находится в ячейке A1 в этом примере):
=EPOCHTODATE(A1)
Результатом работы функции является:
2/13/2023 15:04:47
Дополнительная информация в документации Google.
Функция LET
Функция LET позволяет использовать в ваших формулах определенные именованные переменные. Это мощный метод, позволяющий сократить количество повторяющихся выражений в ваших формулах.
LET пример 1
Рассмотрим этот пример LET, который классифицирует общий объем продаж с помощью функции IFS.
Функция LET позволяет нам определить переменную “sales”, которая ссылается на СУММУ продаж в столбце C. Мы можем повторно использовать переменную “sales” в любом другом месте этой формулы:
=LET( sales , SUM(C2:C11) , IFS( sales>6000000 , "Exceed target" , sales>5000000 , "On target" , sales>4000000 , "Below target" ))
Без LET формула многократно повторяет выражение суммы, что затрудняет изменение этой формулы:
=IFS( SUM(C2:C11)>6000000 , "Exceed target" , SUM(C2:C11)>5000000 , "On target" , SUM(C2:C11)>4000000 , "Below target")
LET пример 2
Эта формула — с использованием LET, ПОСЛЕДОВАТЕЛЬНОСТИ и ФИЛЬТРА — позволит получить все дни недели (понедельник – пятница) на год вперед, начиная с сегодняшнего дня:
=LET( dates , SEQUENCE(365,1,TODAY(),1) , FILTER( dates , WEEKDAY(dates,2)<6 ))
Используя LET, вы можете избежать повторения выражения SEQUENCE.
Дополнительная информация в документации Google.