Excel и Google Workspace
Фильтрация, сортировка, объединение, разворот и обход массивов в Excel/Sheets.
30 формул
FILTER возвращает только те строки диапазона A2:D20, где статус в B2:B20 равен "Оплачен", а сумма в D2:D20 больше 1000. Результат разливается на лист как динамический массив.
UNIQUE возвращает список уникальных значений из диапазона A2:A100 в том порядке, в котором они впервые встречаются. Функция помогает быстро получить справочник клиентов, товаров, городов или категорий из длинной таблицы.
SORT возвращает строки диапазона A2:D20, отсортированные по четвертому столбцу в порядке убывания. Исходная таблица не меняется, а результат выводится как отдельный динамический массив.
ARRAYFORMULA применяет выражение B2:B10*C2:C10 сразу ко всем строкам диапазона. В результате Google Sheets выводит столбец произведений: цена умножается на количество для каждой строки.
FILTER с REGEXMATCH в Google Sheets: формула =FILTER(A2:C100,REGEXMATCH(B2:B100,"Москва|СПб")) помогает величины range, text_range, regex заданы для одной и той же ситуации, периода или объекта. В тексте есть условия, пример, ошибки и проверка результата.
ARRAYFORMULA с IF для расчета целого столбца: формула =ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C)) помогает величины range, condition, result заданы для одной и той же ситуации, периода или объекта. В тексте есть условия, пример, ошибки и проверка результата.
BYROW и LAMBDA для построчной суммы: формула =BYROW(B2:E20,LAMBDA(row,SUM(row))) помогает величины range, row заданы для одной и той же ситуации, периода или объекта. В тексте есть условия, пример, ошибки и проверка результата.
MAP и LAMBDA для преобразования каждого значения: формула =MAP(A2:A,LAMBDA(x,TRIM(x))) помогает величины range, x заданы для одной и той же ситуации, периода или объекта. В тексте есть условия, пример, ошибки и проверка результата.
TOCOL превращает прямоугольный массив в один вертикальный столбец. По умолчанию значения читаются построчно, а scan_by_column=TRUE переключает обход по столбцам, сохраняя выбранный порядок позиций.
TAKE возвращает заданное число соседних строк или столбцов с начала или конца массива. Знак rows и columns задаёт сторону выбора, а исходная матрица и порядок её элементов сохраняются.
DROP удаляет заданное число соседних строк или столбцов с начала или конца массива и возвращает оставшуюся прямоугольную часть. Исходный диапазон при этом не изменяется.
TOROW разворачивает прямоугольный массив в одну горизонтальную разлившуюся строку, сохраняя порядок по строкам или переключаясь на обход по столбцам.
WRAPROWS раскладывает одномерный вектор по строкам с заданным числом элементов, а недостающие позиции заполняет выбранным значением.
WRAPCOLS превращает одномерный список в матрицу, заполняя каждый столбец сверху вниз. Аргумент wrap_count задаёт высоту столбца, а pad_with заполняет недостающие позиции последней колонки.
CHOOSECOLS возвращает только названные столбцы исходного массива, допускает перестановку, повтор и отрицательные номера с конца.
CHOOSEROWS извлекает отдельные строки массива по их позициям, сохраняя порядок аргументов и позволяя обращаться к строкам с конца.
VSTACK последовательно ставит массивы один под другим и возвращает единый разливающийся диапазон. При разной ширине недостающие позиции получают #N/A.
HSTACK ставит массивы рядом и возвращает общий разлившийся диапазон. Высота равна максимуму высот аргументов, а недостающие строки получают #N/A.
EXPAND увеличивает массив до заданного числа строк и столбцов, заполняя новые ячейки pad_with или #N/A по умолчанию. Уменьшение размера даёт #VALUE!.
REDUCE последовательно применяет LAMBDA к элементам массива и возвращает один финальный аккумулятор. Это удобная основа для сумм, произведений и условных счётчиков.
SCAN применяет LAMBDA к элементам массива и возвращает все промежуточные аккумуляторы. Функция строит нарастающие итоги и помогает увидеть ход вычисления.
TRANSPOSE поворачивает диапазон: строки становятся столбцами, а столбцы строками. Формула удобна для подготовки отчетов, импорта и динамических массивов. Прием особенно удобен в повторяемых офисных отчетах.
SEQUENCE создает динамический массив последовательных чисел с заданным числом строк, столбцов, началом и шагом. Формула заменяет ручную нумерацию. Прием особенно удобен в повторяемых офисных отчетах.
FILTER возвращает строки или столбцы, которые соответствуют заданным условиям. В Google Таблицах это быстрый способ сделать живую выборку без ручного фильтра интерфейса.
ARRAYFORMULA применяет расчет сразу к диапазону и возвращает массив результатов. В Google Таблицах она помогает не копировать формулу вручную по каждой строке.
UNIQUE удаляет повторяющиеся значения, а SORT сортирует результат. Вместе они создают живой список уникальных клиентов, товаров, городов или статусов.
Функция FILTER возвращает все строки из диапазона, которые удовлетворяют условиям. Это удобно для отфильтрованных отчётов, без ручной сортировки и промежуточных формул.
С помощью SORT можно сортировать диапазон сразу по нескольким колонкам с отдельным направлением сортировки для каждого ключа.
UNIQUE для очистки повторов показывает, как по формуле =UNIQUE(A2:A200) получить проверяемый результат из исходных данных. В материале уточнены обозначения, условия применения и типовые ошибки при подстановке.
ARRAYFORMULA автоматически применяет формулу к диапазону без копирования вниз по каждой строке, сохраняя логику в одной ячейке.
