Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов
Теперь (в Microsoft 365, в Excel 2021) достаточно просто ввести в одну ячейку формулу, в которой аргументами будут диапазоны, а не ячейки и которая на выходе будет возвращать целый диапазон, и просто нажать Enter (или иным образом завершить ввод формулы).
Синим контуром выделены размеры диапазона, который возвращает формула. Это цельная неделимая сущность: нельзя выделить какую-либо ячейку, кроме первой (левой верхней, в данном случае D2), и удалить фрагмент этого возвращаемого массива. Можно удалить только всю формулу целиком.
Если выделить одну из ячеек, возвращаемых формулой, мы увидим саму формулу, но она будет выделена в строке формул серым, редактировать ее нельзя, это можно делать только в первой ячейке.
Строго говоря, формулы массива имелись и ранее во всех версиях Excel, но они были менее удобными:
— требовалось заранее выделить диапазон, в котором будет вводиться формула массива, то есть знать заранее размерность результата;
— нажать после Ctrl + Shift + Enter.
С новыми формулами массива появилась и новая ошибка — #ПЕРЕНОС!
Если на пути вывода данных будут другие формулы или значения, то формула будет возвращать ошибку, так как она не может «перезаписать» ваши данные, удалить их, чтобы вывести свой результат. Формула может лишь просигнализировать о том, что пустых ячеек внизу и справа недостаточно, чтобы вывести результат. Эта ошибка называется #ПЕРЕНОС! (#SPILL!).
В Google Таблицах в таких ситуациях отображается ошибка #ССЫЛ! (#REF!).
НОВЫЕ ФУНКЦИИ
Появились и новые функции. Они как раз выводят целый массив значений, который может динамически изменяться (если изменились исходные данные, обрабатываемые функцией).
Допустим, если условию, заданному в функции ФИЛЬТР / FILTER, начнут соответствовать не 10 строк, а 11, то она выведет 11 строк. В этом и есть отличие новых динамических массивов: формулы теперь могут возвращать массив разного размера, размерность массива не задается заранее. Благодаря этому появились новые функции, которые мы рассматриваем.
Эти функции (кроме СОРТПО / 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). Тогда ссылки будут выглядеть соответствующим образом:
=ФИЛЬТР(Данные;Данные[Канал продаж]="Дистрибуция")
Здесь тоже важно соблюдать одинаковую размерность — так, следующая функция будет возвращать ошибку, потому что «Данные» — это вся таблица, но без заголовков, а Данные[[#Все];[Канал продаж]] — это столбец «Канал продаж» с заголовками включительно (на 1 строку больше).
=ФИЛЬТР(Данные;Данные[[#Все];[Канал продаж]]="Дистрибуция")
Если нужно отфильтровать все строки, кроме какого-то определенного значения, используется знак (а вернее, два знака) «не равно»:
=ФИЛЬТР(Данные! A1:E997;Данные! D1:D997<> "Дистрибуция")
Условия также могут быть на числовые значения, тогда могут использоваться знаки сравнения. Следующий пример — фильтрация строк с количеством более 200:
=ФИЛЬТР(Данные;Данные[Количество]>200)
Для фильтрации по датам используйте функцию
