Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

Функции TOROW / ПОСТРОК и TOCOL / ПОСТОЛБЦ

Эти функции делают массив плоским — в одну строку или в один столбец соответственно. Можно превратить несколько столбцов в один вертикальный или горизонтальный список.

А дальше можно обрабатывать этот список: например, получить список уникальных значений, без повторов — с помощью функции УНИК / UNIQUE:

=УНИК(ПОСТОЛБЦ(диапазон))

И таким образом мы получим список спикеров (без повторов), хотя изначально это три отдельных столбца, и к ним функцию УНИК применять для решения этой задачи было бесполезно (возвращались бы уникальные строки, а не значения).

WRAPROWS / СВЕРНСТРОК и WRAPCOLS / СВЕРНСТОЛБЦ

Эти функции делают обратную операцию — превращают плоский массив в двумерный. Бывает полезно, чтобы исправить какую-нибудь выгрузку в виде списка и получить ее в табличном виде.

Первый аргумент этих функций — собственно массив (список), а второй (он называется wrap_count) — сколько строк или столбцов нужно получить на выходе:

=СВЕРНСТОЛБЦ(массив; число строк)

В следующем примере мы превращаем список (те же данные, что и в предыдущем примере), в котором каждые 4 строки — это время и три фамилии, в таблицы. В случае со СВЕРНСТОЛБЦ в ней будет 4 строки, в случае со СВЕРНСТРОК — 4 столбца. Второй аргумент обеих функций — 4 (это цикличность наших данных).

CHOOSEROWS / ВЫБОРСТРОК и CHOOSECOLS / ВЫБОРСТОЛБЦ

Эти функции извлекают заданные (по номерам) строки или столбцы из массива. То есть можно извлечь, например, первый и седьмой столбец. Или извлечь весь массив, поменяв порядок столбцов:

=ВЫБОРСТРОК(массив; номер первой извлекаемой строки; номер второй; …)

Давайте извлечем первую и последнюю строку из таблицы. Первая — это первая, вторым аргументом функции ВЫБОРСТРОК можно задать единицу. А чтобы получать номер последней на данный момент строки, можно посчитать число значений с помощью функции СЧЁТЗ / COUNTA:

=ВЫБОРСТРОК(Название таблицы;1;СЧЁТЗ(Столбец из таблицы для подсчета значений))

EXPAND / РАЗВЕРНУТЬ

Эта функция увеличивает массив, добавляя к исходному массиву (диапазону) какое-то заданное значение:

=РАЗВЕРНУТЬ (исходный массив, число строк в новом массиве, число столбцов, чем заполнить)

Если последний аргумент не задать, то новые значения будут ошибками #H/Д (#N/A).

В следующем примере мы также делаем новый массив размерами 3 × 3 (то есть добавляем к исходному из диапазона A1:B2 одну строку и один столбец), но новые значения задаем как нули.

Функция ПОСЛЕД / SEQUENCE

Файл с примерами: ПОСЛЕД.xlsx

Функция ПОСЛЕД / SEQUENCE появилась вместе с динамическими массивами в Excel 2021 и Microsoft 365, то есть отсутствует во всех «коробочных» версиях Excel вплоть до 2019.

Но она есть в Google Таблицах, там эта функция при любом языке формул называется SEQUENCE.

Эта функция возвращает массив из чисел, заданный следующими параметрами — ее аргументами:

• строки (rows);

• столбцы (columns);

• начало (start);

• шаг (step).

Например, такая функция выведет столбец с числами от 1 до 1000:

=ПОСЛЕД(1000;1;1;1)

А такая — диапазон размеров 3 × 3 с числами от 10 до 100:

=ПОСЛЕД(3;3;10;10)

Так как даты в Excel и Google Таблицах — это числа (одна единица = 1 календарный день), то можно выводить и их. Вот несколько примеров.

Все даты за заданный период

Если есть две ячейки, в которых указаны даты начала и окончания периода, то с помощью ПОСЛЕД можно сформировать список всех дат в этом периоде.

Допустим, мы хотим сформировать их в один столбец, тогда числом строк здесь будет продолжительность периода (конец периода минус начало периода и плюс один день, если мы хотим включать последнюю дату периода), столбец будет один, шаг — 1 (один день). Начало последовательности — это первая дата периода:

=ПОСЛЕД(конец периода-начало периода + 1; 1; начало периода; 1)

Все даты текущего месяца

Допустим, нам нужны все даты текущего месяца в строку. Понадобится такая конструкция:

=ПОСЛЕД(1 строка; число дней в месяце; дата начала месяца; шаг=1)

Первый день текущего месяца можно получить так (текущий год + текущий месяц + первое число):

=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)

А количество дней в месяце — так:

=ДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0))

Функция КОНМЕСЯЦА / EOMONTH возвращает последнюю дату месяца, а функция ДЕНЬ / DAY возвращает количество дней у этой даты.

Остается соединить это в одну конструкцию: