Базовые функции Excel хорошо подходят для простых вычислений, но быстро усложняются при сложном анализе данных. В результате появляются вложенные формулы, которые трудно читать, множество вспомогательных столбцов, загромождающих таблицу, и формулы, которые могут перестать работать при изменении данных. Именно здесь на помощь приходят матричные формулы Excel.
Формулы массива позволяют выполнять вычисления по целым диапазонам данных в одной формуле. Поэтому вы можете Выполняйте молниеносный поиск, фильтровать и сортировать с помощью одного мощного выражения вместо того, чтобы писать отдельные формулы для каждой строки или столбца. Это не новость для Excel, но некоторые люди продолжают придерживаться старых способов ведения дел, когда эти функции могут сделать их работу проще и эффективнее.
Быстрые ссылки
5. XLOOKUP
Превосходит VLOOKUP каждый раз.
XLOOKUP — это функция поиска, которая должна была существовать с самого начала. В отличие от VLOOKUP, которая заставляет подсчитывать столбцы и выполняет поиск только вправо, XLOOKUP работает в любом направлении и использует фактические ссылки на столбцы. Синтаксис функции следующий:
=XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode])
Вот что означает каждый параметр:
- искомое_значение: Конкретное значение, которое вы ищете. Это может быть номер детали, код продукта или любой идентификатор в вашем наборе данных.
- искомый_массив: Диапазон, в котором Excel выполняет поиск искомое_значение Ваш. Обычно это один столбец или строка, содержащая ваши критерии поиска.
- возвращаемый_массив: Диапазон, содержащий значения, которые вы хотите получить. Это может быть один столбец, несколько столбцов или даже целый раздел таблицы.
- if_not_found (необязательно): Пользовательский текст или значение, отображаемое при отсутствии совпадений. Это устраняет раздражающие ошибки #N/A и позволяет вместо них отображать «Не найдено» или «Проверьте номер детали».
- match_mode (необязательно): Управляет типом соответствия. Используйте 0 для точного соответствия (по умолчанию), -1 для следующего точного или меньшего соответствия, 1 для следующего точного или большего соответствия и 2 для соответствия с подстановочными знаками.
- режим_поиска (необязательно): Задаёт направление поиска. Используйте значение 1 для поиска от первого к последнему (по умолчанию), -1 для поиска от последнего к первому и 2 для двоичного поиска по отсортированным данным.
Рассмотрим пример таблицы инвентаризации механического оборудования. Следующая формула ищет деталь с номером «BRG-002» в диапазоне идентификаторов и возвращает соответствующие данные. Если деталь отсутствует, вместо ошибки отображается сообщение «Деталь не найдена».
=XLOOKUP("BRG-002", A:A, A:H, "Часть не найдена")
XLOOKUP позволяет извлекать данные из разных столбцов без громоздких вычислений столбцов, которые есть в VLOOKUP, что делает его одним из самых важных Функции Excel для быстрого поиска данных.
4. SUMPRODUCT
Электростанция для условных расчетов
Функция СУММПРОИЗВ не только складывает числа, но и умножает матрицы и суммирует результаты. Это делает её полезной для сложных условных вычислений, требующих нескольких вспомогательных столбцов.
Имеет следующую формулу:
=SUMPRODUCT(array1, [array2], [array3], ...)
здесь, array1 Это первый диапазон значений, которые нужно умножить — обычно это основной столбец данных, например количество или стоимость. array2 Это необязательный второй диапазон для умножения, который часто содержит критерии или условную логику с использованием операторов сравнения.
Они становятся более полезными при использовании логических операторов внутри массивов. Например, при вводе условий типа (supplier="Siemens") Excel преобразует результаты ИСТИНА/ЛОЖЬ в 1/0, что позволяет выполнять вычисления.
Например, следующая формула рассчитывает общую стоимость запасов деталей, поставляемых только компанией Siemens. Формула умножает количество на себестоимость единицы товара, но только для тех строк, где поставщик соответствует критериям.
=SUMPRODUCT(D2:D100*H2:H100*(G2:G100="Siemens"))
Аналогично, следующая формула позволяет рассчитать общую стоимость запаса подшипников в хорошем состоянии:
=SUMPRODUCT((C2:C100="Bearings")*(D2:D100>=15)*H2:H100)
Одновременно применяются два условия: категория должна быть «Подшипники», а уровень запасов должен составлять 15 единиц или выше, что помогает нам определить категории подшипников с достаточным охватом запасов.
В отличие от традиционных функций СУММ с несколькими критериями, СУММПРОИЗВ не требует сложных вложенных структур, поскольку она обрабатывает несколько условий в одной удобочитаемой формуле. Функции СУММ в Excel, Как и функции СУММЕСЛИ и СУММЕСЛИМН, они отлично подходят для простого условного суммирования, но функция СУММПРОИЗВ превосходит все ожидания, когда необходимо умножить значения перед суммированием или выполнить более сложные логические операции.
3. ФИЛЬТР
Упрощает динамическое извлечение данных
Функция FILTER извлекает строки из набора данных на основе заданных вами условий. В отличие от ручной фильтрации, эта функция генерирует динамические результаты, которые автоматически обновляются при изменении исходных данных. Синтаксис функции FILTER следующий:
=ФИЛЬТР(массив, включить, [if_empty])
Вот чем управляет каждый вход:
- массив (диапазон): Полный диапазон данных, которые вы хотите отфильтровать. Включает все столбцы, которые вы хотите включить в результаты, а не только столбец критериев.
- включать: Логическое условие, которое определяет, какие строки возвращать, — использует операторы сравнения для создания массивов ИСТИНА/ЛОЖЬ для каждой строки.
- if_empty (необязательно): Отображает сообщение, если ни одна строка не соответствует заданным критериям. Предотвращает ошибки #CALC! и отображает содержательный текст, например: «Нет соответствующих результатов».
Функция работает, оценивая ваше условие по каждой строке диапазона. Если условие возвращает значение ИСТИНА, вся эта строка отображается в отфильтрованных результатах. Вот пример из таблицы инвентаризации механического оборудования:
=FILTER(A2:H101, (C2:C101="Bearings")*(G2:G101="Timken"))
Эта формула извлекает все строки, где ресурс — «Timken», а категория — «Подшипники». Звездочка (*) создаёт условие «И» путём перемножения логических массивов.
Когда вы добавляете новые данные в свой исходный диапазон, Использование функции ФИЛЬТР в Excel Это более разумно, чем ручная сортировка и временные таблицы, поскольку отфильтрованные результаты автоматически обновляются. Это делает этот метод полезным для создания динамических информационных панелей и отчётов.
2. УНИКАЛЬНЫЙ
Извлечение уникальных значений без дубликатов
Функция UNIQUE извлекает уникальные значения из диапазона данных и автоматически исключает дубликаты. Эта функция важна для создания раскрывающихся списков, анализа категорий данных и построения сводных отчётов. Формула:
=UNIQUE(массив, [по_столбцу], [точно_один раз])
Вот как работает каждый вход:
- массив (диапазон): Диапазон, содержащий данные, из которых вы хотите удалить дубликаты, — это может быть один столбец, несколько столбцов или целый раздел таблицы.
- по_колу (необязательно): Значение FALSE сравнивает строки для определения уникальности (значение по умолчанию), а значение TRUE — столбцы. Однако в большинстве сценариев используется сравнение строк по умолчанию.
- exact_once (необязательно): FALSE возвращает все уникальные значения, включая те, которые встречаются несколько раз (по умолчанию), а TRUE возвращает только значения, которые встречаются ровно один раз в наборе данных.
Функция UNIQUE оценивает каждую строку или значение в массиве и возвращает только первое вхождение каждого уникального элемента. Порядок соответствует исходной последовательности данных. Вот пример:
=УНИКАЛЬНЫЙ(G2:G22)
Эта формула извлекает все уникальные названия поставщиков из столбца G «Поставщик» и создаёт чистый список дубликатов. Я использую её для создания раскрывающихся списков поставщиков или сводных отчётов.
Вы также можете использовать его по всей таблице, как показано ниже:
=УНИКАЛЬНЫЙ(A2:F100)
Возвращает уникальные комбинации по всем столбцам (от A до F), отображая отдельные записи инвентаризации. Если две детали имеют одинаковые значения в каждом столбце, в результатах отобразится только одна из них.
При работе с большими наборами данных UNIQUE избавляет от утомительного процесса ручного удаления дубликатов. Динамические результаты обновляются по мере поступления новых данных, а поскольку UNIQUE создаёт матрицы переполнения, этот подход избавляет от необходимости менять размер таблиц, автоматически масштабируя их для размещения всех уникальных значений. Я использую его для поддержания чистоты референтных списков и создания надёжных диапазонов проверки данных.
1. СОРТИРОВКА и СОРТИРОВКА ПО
Организуйте свои данные, не ставя под угрозу исходные данные
Функции SORT и SORTBY динамически организуют данные, сохраняя исходные данные. SORT выполняет базовую сортировку по положению столбца, тогда как SORTBY сортирует данные на основе значений в разных столбцах, обеспечивая большую гибкость для сложной сортировки.
SORT использует следующую структуру:
=СОРТИРОВАТЬ(массив, [индекс_сортировки], [порядок_сортировки], [по_столбцу])
Вот что контролирует каждый параметр:
- массив: Диапазон данных, которые вы хотите сортировать, включает все столбцы, которые должны появиться в отсортированных результатах.
- sort_index (необязательно): Номер столбца в массиве для сортировки. Используйте 1 для первого столбца, 2 для второго и т. д. (по умолчанию — 1).
- sort_order (необязательно): Используйте 1 для сортировки по возрастанию (по умолчанию) и -1 для сортировки по убыванию.
- по_колу (необязательно): FALSE для сортировки по строкам (по умолчанию), TRUE для сортировки по столбцам — в большинстве сценариев используется сортировка по строкам.
Функция SORTBY имеет следующий вид:
=СОРТИРОВАТЬ ПО(массив, по_массив1, [порядок_сортировки1], [по_массив2], [порядок_сортировки2], ...)
Его транзакции включают в себя:
- массив: Диапазон данных для сортировки — аналогично функции SORT, он содержит все столбцы, которые вы хотите включить в результаты.
- по_массиву1: Диапазон, содержащий значения, определяющие порядок сортировки, может быть любым столбцом, даже за пределами диапазона основного массива.
- sort_order1 (необязательно): 1 для сортировки по возрастанию (по умолчанию), -1 для сортировки по убыванию.
- по_массиву2, сортировке_порядок2 (необязательно): Дополнительные критерии сортировки для многоуровневой сортировки.
Если рассмотреть пример из таблицы инвентаризации оборудования, то эти функции обрабатывают реальные сценарии сортировки:
=СОРТ(A2:H22, 4, -1)
Эта формула сортирует весь инвентарь по уровню запасов в порядке убывания, начиная с позиций с наибольшим запасом. Формула сортирует по столбцу 4 (уровни запасов), сохраняя при этом все взаимосвязи между строками.
Я использую функцию СОРТИРОВКА. Вместо SORT вы можете использовать её для лучшего контроля над критериями сортировки и несколькими уровнями сортировки. Например, следующая формула сначала сортирует товары в алфавитном порядке по категориям, а затем по уровням запасов от самого высокого к самому низкому в каждой категории.
=СОРТИРОВАТЬ ПО(A2:H22, C2:C22, 1, D2:D22, -1)
Организованные электронные таблицы, более продуманные результаты
Формулы массива избавляют от нагромождения вспомогательных столбцов и вложенных функций, которые затрудняют ведение электронных таблиц. Вы получаете отдельные формулы, обрабатывающие несколько операций, что делает рабочие книги более аккуратными и профессиональными.
Одним из заметных преимуществ являются динамические функции, где результаты автоматически обновляются при изменении исходных данных. Это исключает необходимость ручного обновления или использования неисправных строк формул, делая ваши электронные таблицы более надёжными для текущего анализа.
Библиотека функций массивов Excel продолжает расширяться, выходя за рамки этих базовых инструментов. Когда мне нужно объединить данные из нескольких источников, я использую функции VSTACK и HSTACK для объединения диапазонов. Вместе эти функции создают мощные рабочие процессы обработки данных, которые были бы невозможны с использованием традиционных формул.
