6 функций Excel для быстрого поиска данных

Работа с большими наборами данных требует умения быстро находить то, что вам нужно. К счастью, в Microsoft Excel есть ряд функций, которые могут в этом помочь.

1. Функция НАЙТИ

Функция НАЙТИ в Excel возвращает числовую позицию указанного символа или строки (строки символов) в более крупной строке. Эта функция полезна для точного извлечения текста, манипулирования им и условного форматирования.

Синтаксис функции НАЙТИ:

НАЙТИ(текст_поиска, текст_в_поиске, (начальная_позиция))

Здесь, текст_поиска — это символ или подстрока, которую вы хотите найти, и text_to_search_in — это большая текстовая строка, которую вы хотите найти. начальная_позиция Параметр указывает, где в строке вы хотите, чтобы функция начала поиск.

Примечание. Любые параметры в квадратных скобках являются необязательными.

Вот пример того, как работает функция НАЙТИ:

=НАЙТИ(“12345”, “Заказ № 12345 – Выполнен”)

Приведенная выше формула вернет 9, поскольку подстрока 12345 начинается с этой позиции.

6 функций Excel для быстрого поиска данных Рисунок 1

Функция НАЙТИ чувствительна к регистру. Например, если вы ищете букву А в подстроке яблоко, вы получите ошибку.

Если вы хотите найти что-то, не чувствительное к регистру, используйте вместо этого функцию ПОИСК. Синтаксис этой функции аналогичен функции НАЙТИ.

=ПОИСК(текст_поиска, текст_в_поиске, (начальная_позиция))

2. Функция СОРТИРОВАТЬ

Функция СОРТИРОВКА может помочь вам отсортировать данные в диапазоне по возрастанию или убыванию, чтобы упростить поиск определенных данных в электронной таблице Excel.

Синтаксис функции СОРТИРОВКА:

СОРТИРОВКА(диапазон_для_сортировки, (индекс_сортировки), (порядок_сортировки), (сортировка_по))

Диапазон_to_sort Параметр — это диапазон, который вы хотите отсортировать. Используйте сорт_индекс параметр, указывающий номер столбца или строки для сортировки в диапазоне (по умолчанию — 1).

sort_order Параметр определяет порядок сортировки списка, где 1 — по возрастанию, а -1 — по убыванию (по умолчанию — по возрастанию). И Сортировать по указывает, следует ли сортировать по строкам (FALSE) (по умолчанию) или по столбцам (TRUE).

Мы отсортируем диапазон на скриншоте ниже на основе второго столбца и в порядке убывания.

6 функций Excel для быстрого поиска данных Рисунок 2

Вот как формула будет выглядеть в Excel:

=СОРТИРОВАТЬ(A2:B6, 2, -1)

Теперь диапазон будет отсортирован по убыванию.

6 функций Excel для быстрого поиска данных Рисунок 3

3. Функция ФИЛЬТР

Функция ФИЛЬТР оценивает диапазон данных на основе условия и возвращает только строки и столбцы, соответствующие условию.

Синтаксис функции ФИЛЬТР:

ФИЛЬТР(диапазон_до_фильтра, условие, (значение_если_пусто))

Диапазон_to_filter Параметр — это массив или диапазон ячеек, которые вы хотите отфильтровать. состояние Параметр — это критерий, определяющий, какой контент будет возвращен в отфильтрованных результатах. value_if_empty Параметр указывает, какой контент будет возвращен, если ни один контент не соответствует условию.

Мы отфильтруем область на снимке экрана ниже, чтобы показывать только сотрудников отдела продаж.

6 функций Excel для быстрого поиска данных Рисунок 4

Вот изображение формулы в действии:

=ФИЛЬТР(A2:C6, B2:B6 = “Продажи”)

Теперь вы должны видеть только строки и столбцы, соответствующие указанным критериям.

6 функций Excel для быстрого поиска данных Рисунок 5

4. Функция ИНДЕКС

Если вы хотите получить значение определенной ячейки в диапазоне данных, вы можете использовать функцию ИНДЕКС. Вам просто нужно указать строку и столбец, в которых будет расположено значение.

Синтаксис функции ИНДЕКС:

ИНДЕКС(диапазон_для_поиска, строка_в_поиск_в, (столбец_в_поиск_в))

range_to_search Параметр — это диапазон, из которого вы получите значение. row_to_search_in и columns_to_search_in — это номера строк и столбцов, в которые попадает значение (считайте их координатами).

Например, на скриншоте ниже мы возьмем балл (C4), который Алиса получила на тесте по английскому языку.

Диапазон в Excel отображает результаты тестов учащихся по различным предметам.
Формула выглядит следующим образом:

=ИНДЕКС(A1:C4, 4, 3)

Эта формула вернет 88, потому что именно такой балл получила Алиса на тесте по английскому языку.

6 функций Excel для быстрого поиска данных Рисунок 6

5. Функция ПОИСКПОЗ

Функция ПОИСКПОЗ в Excel ищет диапазон данных для указанного значения, а затем возвращает его относительную позицию. Затем вы можете использовать возвращаемое значение с такими функциями, как INDEX, для динамического извлечения данных и манипулирования ими.

Синтаксис функции ПОИСКПОЗ:

ПОИСКПОЗ(значение_для_поиска, диапазон_в_поиска_в, (тип_сопоставления))

Здесь, value_to_search значение, которое вы хотите найти, и range_to_search_in — это диапазон, в котором вы ищете это значение.

Тип_соответствия Параметр указывает тип используемого соответствия. Вот типы, которые вы можете использовать:

Тип соответствия

Описывать

1 (по умолчанию)

Возвращает наибольшее значение, меньшее или равное value_to_search

0

Возвращает точное совпадение

-1

Возвращает наименьшее значение, большее или равное value_to_search.

В этом примере мы будем использовать функцию ПОИСКПОЗ, чтобы найти позицию Кэрол в диапазоне, требуя, чтобы она была точным совпадением.

6 функций Excel для быстрого поиска данных Рисунок 7

Вот как будет выглядеть функция в Excel:

=ПОИСКПОЗ(“Кэрол”, A2:A6, 0)

После выполнения приведенной выше формулы функция вернет 3, поскольку точное совпадение Кэрол — это третий элемент в диапазоне.

6 функций Excel для быстрого поиска данных Рисунок 8

6. Найдите XLOOKUP.

С помощью функции XLOOKUP вы указываете значение для поиска в одном диапазоне, а затем извлекаете соответствующее значение из другого диапазона. В отличие от функций HLOOKUP и VLOOKUP, XLOOKUP позволяет выполнять поиск в любом направлении, что делает поиск данных в электронной таблице более гибким.

Синтаксис функции XLOOKUP:

XLOOKUP(значение_для_поиска_вверх, диапазон_для_проверки, диапазон_для_возврата, (если_значение_не_найдено), (тип_соответствия), (режим_поиска))

В этом синтаксисе value_to_look_up это ценность, которую вы ищете, range_to_check — это диапазон, из которого находится значение, которое вы хотите получить, и range_to_return откуда будет получено соответствующее значение искомого значения. Для работы функции вам нужно указать только эти 3 параметра, поэтому мы сосредоточимся только на них.

Поясним это на примере, основанном на скриншоте ниже. В примере требуется найти банан (A2) и вернуть его цвет из соответствующего столбца (B).

6 функций Excel для быстрого поиска данных Рисунок 9

Вот как формула будет выглядеть в Excel:

=XLOOKUP(“Банан”, A:A, B:B)

Выполнение этой формулы вернет Yellow (B2) — соответствующее значение, основанное на поиске значения Banana.

6 функций Excel для быстрого поиска данных Рисунок 10

Освоение шести функций Excel — НАЙТИ, СОРТИРОВКА, ФИЛЬТР, ИНДЕКС, ПОИСКПОЗ и ПРОСМОТР — поможет вам быстро находить, сортировать и извлекать информацию из больших наборов данных. Это может значительно повысить производительность вашей работы с Excel и способность эффективно анализировать данные.

Дэвид Пак

Обновление от 9 декабря 2024 г.