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

Эти функции (кроме СОРТПО / SORTBY) ранее были доступны только в Google Таблицах (там у этих функций названия на английском при любом языке формул), но теперь есть и в Excel в последнем пакете Microsoft Office.

Функция УНИК / UNIQUE

Функция УНИК / UNIQUE выводит массив уникальных значений (если аргумент — один столбец) или строк (если аргумент — несколько столбцов). Единственный обязательный аргумент — диапазон.

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

Если в исходном диапазоне станет меньше значений, функция будет возвращать массив другого размера.

Если аргументом УНИК будет диапазон из нескольких столбцов, то функция будет возвращать уникальные строки.

Функция СОРТ / SORT

Функция СОРТ / SORT сортирует диапазон — по столбцам или строкам:

=СОРТ (массив; [индекс_сортировки]; [порядок_сортировки]; [по_столбцу])

• массив (array) — диапазон данных, который будет возвращаться функцией в отсортированном виде;

• индекс_сортировки (sort_index) — номер столбца (строки), по которому идет сортировка;

• порядок_сортировки (sort_order) — порядок сортировки (1 = по возрастанию, −1 = по убыванию);

• по_столбцу (by_col) — сортировка по столбцам или строкам; если ИСТИНА / TRUE, то по столбцам (то есть будет меняться порядок столбцов); если ЛОЖЬ / FALSE, то по строкам (будет меняться порядок строк).

Применим функцию СОРТ к списку имен, пропустив все необязательные аргументы. В таком случае по умолчанию функция будет сортировать диапазон по возрастанию (по алфавиту для текстовых значений).

Можно объединить ее с функцией УНИК, чтобы получить отсортированный список уникальных значений (без дубликатов).

Если в диапазоне несколько столбцов, то понадобятся и другие аргументы функции СОРТ, чтобы указать, по какому столбцу и в каком порядке сортируем. Второй аргумент — порядковый номер столбца, а третий — порядок сортировки (1 = по возрастанию, −1 = по убыванию).

Функция СОРТПО / SORTBY

Функция СОРТПО / SORTBY возвращает диапазон, отсортированный по другому столбцу/строке (столбцам/строкам):

=СОРТПО (массив; [ключевой_массив1]; [порядок_сортировки1];…)

• массив (array) — диапазон данных, который будет возвращаться функцией в отсортированном виде;

• ключевой_массив1 (by_array1) — номер столбца (строки), по которому идет сортировка;

• порядок_сортировки1 (sort_order1) — порядок сортировки (1 = по возрастанию, −1 = по убыванию).

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

А СОРТПО позволяет это сделать: вывести один диапазон, отсортированный по значениям из другого.

В следующем примере мы выводим список специалистов, отсортированный по их ставке, при этом в отсортированном списке нет столбца со ставкой, на основе которого происходит сортировка.

Функция ФИЛЬТР / FILTER

Функция ФИЛЬТР / FILTER выводит значения из одного или нескольких столбцов, отфильтрованные по одному или нескольким условиям:

=ФИЛЬТР (массив; включить; [если_пусто])

• массив (array) — диапазон данных, который будет возвращаться функцией в отфильтрованном виде;

• включить (include) — условие фильтрации, записанное в следующем виде:

A1:A100 = "Москва" (фильтруем по слову «Москва» в столбце A). Диапазон условия должен быть той же размерности, что и диапазон данных:

• если_пусто (if_empty) — что возвращать, если функция не найдет никаких строк по условию.

Условия на текстовые значения записываются следующим образом:

диапазон="условие"

Например, только «Дистрибуция» в столбце D:

=ФИЛЬТР(Данные! A1:E997;Данные! D1:D997="Дистрибуция")

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

=ФИЛЬТР(Данные!A2:E997;Данные!D1:D998="Дистрибуция")

Фильтруемые данные могут быть не только обычным диапазоном, но и таблицей (Table). Тогда ссылки будут выглядеть соответствующим образом:

=ФИЛЬТР(Данные;Данные[Канал продаж]="Дистрибуция")