Все мы знаем знаменитую функцию Excel — функции ВПР. Обычно он используется для поиска значений с уникальным идентификатором. Но это не единственная функция, которую можно использовать для поиска значений в Excel. Есть много других функций и формул, которые можно использовать для поиска значения. В этой статье я познакомлю вас со всеми этими функциями и формулами поиска в Excel. Некоторые из них даже лучше, чем функция ВПР в Excel. Итак, дочитайте до конца.

1. link: / lookup-formulas-Introduction-of-vlookup-function [Функция ВПР в Excel]

1

Функция поиска firstexcel, конечно же, является функцией VLOOKUP. Эта функция известна не просто так. Мы можем использовать эту функцию не только для поиска. Но основная задача этой функции — искать значения в таблице слева направо.

Синтаксис функции ВПР:

=VLOOKUP(lookup_value,

table_array, col_index_number, [range_lookup])

_Lookup_value: _ Значение, по которому вы хотите искать в первом столбце массива таблиц.

_Table_array: _ Таблица, в которой вы хотите искать / искать _col_index_number: _ * Номер столбца в Table Array, из которого вы хотите получить результаты.

_ [range_lookup]: _ FALSE, если вы хотите найти точное значение, TRUE, если вы хотите приблизительное совпадение.

Преимущества ВПР:

  • Легко использовать.

Быстрое многократное использование * Лучше всего подходит для поиска значений в вертикальном порядке.

Недостатки:

Он используется только для вертикального просмотра. Он возвращает только первое совпавшее значение.

  • Статичен, пока не используется функция ПОИСКПОЗ.

  • Невозможно найти значения слева от значения поиска.

Вы можете подробно прочитать об этой формуле поиска в Excel ссылка: / lookup-formulas-Introduction-of-vlookup-function [здесь].

2. link: / lookup-formulas-hlookup-function-in-excel [Функция Excel HLOOKUP]

2

Функция HLOOKUP — это недостающая часть функции VLOOKUP. Функция HLOOKUP используется для поиска значений по горизонтали. Другими словами, когда вы хотите найти значение в Excel, сопоставив значение в столбцах и получив значения из строк, мы используем функцию HLOOKUP. Это полная противоположность функции ВПР.

Синтаксис HLOOKUP

=HLOOKUP(lookup value, table array, row index number, [range_lookup] )

искомое значение: * искомое значение.

Таблица Массив: * Таблица, в которой вы ищете значение.

Номер индекса строки: * Номер строки в таблице, из которой вы хотите получить данные.

[range_lookup]: это тип соответствия. 0 для точного совпадения и 1 для приблизительного совпадения. *

Преимущества функции HLOOKUP:

  • Он может искать значения по горизонтали.

  • Легко использовать.

Быстрое многократное использование

Недостатки:

Он используется только для горизонтального поиска. Он возвращает только первое совпавшее значение.

  • Статичен, пока не используется функция ПОИСКПОЗ.

  • Невозможно найти значения выше значений поиска в таблице.

Вы можете узнать об этой функции поиска в Excel link: / lookup-formulas-hlookup-function-in-excel [здесь].

3. link: / lookup-formulas-use-index-and-match-to-lookup-value [Формула поиска INDEX-MATCH]

341

Там, где недоступны функции ВПР и ГПР, доступна эта формула. Это лучшая формула поиска в Excel до Excel 2016 (XLOOKUP уже в пути).

Общая формула INDEX MATCH

=INDEX (Result_Range,MATCH(lookup_value,lookup range,0))

Result_Range: это диапазон, из которого вы хотите получить значение.

Lookup_value: это значение, которое вы хотите сопоставить.

Lookup_Range: * Это диапазон, в котором вы хотите сопоставить значение поиска.

Преимущества формулы поиска INDEX-MATCH:

  • Может искать в четырех направлениях. Он может искать значения слева и вверх от поискового значения.

  • Динамический.

  • Нет необходимости определять индекс строки или столбца.

Недостатки:

  • Это может быть сложно для новых пользователей.

  • Использует две функции Excel в комбинации. Пользователи должны понимать работу link: / lookup-formulas-excel-index-function [INDEX]

и link: / lookup-formulas-excel-match-function [MATCH]

функция.

Вы можете узнать об этой формуле link: / lookup-formulas-use-index-and-match-to-lookup-value [здесь].

4: link: / lookup-formulas-how-to-lookup-values-using-excel-offset-match-function [Excel OFFSET-MATCH Lookup Formula]

image

Это еще одна формула, которую можно использовать для динамического поиска значений.

Эта формула поиска в Excel использует функцию СМЕЩЕНИЕ в качестве функции привязки и ПОИСКПОЗ в качестве функции подачи. Используя эту формулу, мы можем динамически извлекать значения из таблицы, просматривая строки и столбцы.

Общая формула,

=OFFSET(StartCell,MATCH(RowLookupValue,RowLookupRange,0),MATCH(ColLookupValue,ColLookupRange,0))

StartCell: * Это начальная ячейка таблицы поиска. Скажем, если вы хотите найти в диапазоне A2: A10, тогда StartCell будет A1.

RowLookupValue: это значение поиска, которое вы хотите найти в строках нижеStartCell. RowLookupRange: это диапазон, в котором вы хотите найти RowLookupValue. Это диапазон ниже StartCell (A2: A10). ColLookupValue: * Это значение поиска, которое вы хотите найти в столбцах (заголовках).

ColLookupRange: * Это диапазон, в котором вы хотите найти ColLookupValue. Это диапазон в правой части StartCell (например, B1: D1).

Преимущества этого метода поиска в Excel:

Быстро Может искать по горизонтали и вертикали.

  • Динамический

Недостатки:

  • Сложный для некоторых людей.

  • Необходимо понимать работу функций СМЕЩЕНИЕ и СООТВЕТСТВИЕ.

Вы можете узнать об этой формуле поиска link: / lookup-formulas-how-to-lookup-values-using-excel-offset-match-function [здесь]

в деталях.

5: link: / lookup-formulas-lookup-to-left-with-vlookup-function-in-excel [Excel LOOKUP Formula Multiple Values]

286

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

Эта формула возвращает все совпавшие значения из списка, а не только первое совпадение.

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

Общая формула

{=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(first cell of lookup_value_range)+1),ROW(1:1)))}

Массив: диапазон, из которого вы хотите получить данные.

lookup_value: ваше lookup_value, которое вы хотите отфильтровать.

lookup_value_range: диапазон, в котором вы хотите отфильтровать lookup_value.

Первая ячейка в диапазоне lookup_value: если ваш диапазон lookup_value равен $ A $ 5: $ A $ 100, то это $ A $ 5.

Важно: все должно быть link: / excel-range-name-absolute-reference-in-excel [absolute referenced]. lookup_value может быть относительным в соответствии с требованиями.

Введите его как формулу массива. После написания формулы нажмите клавиши CTRL + SHIFT + ENTER, чтобы преобразовать ее в формулу массива.

Как вы можете видеть на гифке, он возвращает все совпадения из таблицы Excel.

Преимущества:

  • Возвращает с несколькими совпадающими значениями из таблицы Excel.

  • Динамический

Недостатки:

  • Это слишком сложно для понимания нового пользователя.

Использует формулу массива. Необходимо определить возможное количество выходных данных и применить эту формулу как формулу массива с несколькими ячейками (не в Excel 2019 и 365).

  • Медленный.

Вы можете узнать об этой формуле link: / lookup-formulas-lookup-to-left-with-vlookup-function-in-excel [здесь]

в деталях.

6: link: / lookup-formulas-lookup-to-left-with-vlookup-function-in-excel [VLOOKUP-CHOOSE Lookup Excel Formula]

imageSo, most people say that it is not possible to lookup values from the left of the lookup value in Excel using VLOOKUP function. Well, I am sorry to say, but they are wrong. We can lookup to the left of the lookup value in Excel using VLOOKUP function with the help of the CHOOSE function.

Общая формула:

= VLOOKUP ( lookup_value , CHOOSE ( { 1 , 2 } , lookup_range , req_range ) , 2 , 0)

lookup_value: value для поиска lookup_range: range, где искать lookup_value req_range: range, где требуется соответствующее значение 2: второй столбец, num представляет req_range 0: искать точное совпадение В этой формуле мы в основном создаем виртуальную таблицу внутри формулу с помощью функции ВЫБРАТЬ. Функция ВЫБОР создает таблицу из двух столбцов. Первый столбец содержит диапазон поиска, а второй столбец — диапазон результатов.

Преимущества формулы поиска VLOOKUP-CHOOSE:

Вы можете найти слева от искомого значения Fast * Easy

Недостатки:

  • Функция ВЫБРАТЬ используется редко. Пользователи должны понимать его работу.

Вы можете узнать об этой формуле поиска link: / lookup-formulas-lookup-to-left-with-vlookup-function-in-excel [здесь].

Так что да, ребята, это разные функции поиска и формулы.

Это еще не все. В Excel может быть гораздо больше формул поиска, которые можно создать с использованием различных комбинаций формул Excel. Если у вас есть особые методы поиска, поделитесь ими в разделе комментариев ниже. Мы включим это в нашу статью с вашим именем.

Надеюсь, это было полезно и информативно. Если у вас есть какие-либо сомнения относительно этой статьи, спросите меня в разделе комментариев ниже.

Статьи по теме:

link: / excel-365-functions-10-new-functions-in-excel-2019-and-365 [10 новых функций в Excel 2019 и 365]: Хотя функций, доступных в Excel 2016 и старше, достаточно для работы Из-за каких-либо расчетов и автоматизации формулы иногда получаются сложными. Такие мелкие, но важные вещи решены в Excel 2019 и 365.

link: / lookup-formulas-17-things-about-excel-vlookup [17 вещей о ВПР в Excel]: ВПР — это просто не формула поиска, это нечто большее. ВПР обладает многими другими возможностями и может использоваться для различных целей. В этой статье мы исследуем все возможные варианты использования функции ВПР.

link: / excel-chart-10-creative-advanced-excel-charts-to-rock-your-dashboard [10 расширенных графиков Excel для улучшения вашей информационной панели]: Excel — это мощный инструмент визуализации данных, который можно использовать для создавайте потрясающие диаграммы в Excel. Эти 15 расширенных диаграмм Excel можно использовать, чтобы заворожить ваших коллег и начальников.

link: / excel-chart-4-creative-target-vs-success-charts-in-excel [4 диаграммы творческой цели и достижений в Excel]: диаграммы цели и достижений — это самые основные и важные диаграммы в любом бизнесе. . Так что лучше применить их как можно более творчески. Эти 4 креативных диаграммы могут сделать ваши дашборды потрясающими для презентации.

Популярные статьи:

link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-вашу-продуктивность [50 комбинаций клавиш Excel для повышения вашей продуктивности] | Выполняйте свою задачу быстрее. Эти 50 ярлыков заставят вас работать в Excel еще быстрее.

link: / формулы-и-функции-введение-функции-vlookup [Как использовать функцию Excel VLOOKUP] | Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листов. link: / tips-countif-in-microsoft-excel [Как использовать]

link: / введение-формулы-и-функции-функции-vlookup [Excel]

link: / tips-countif-in-microsoft-excel [Функция СЧЁТЕСЛИ] | Подсчитайте значения с условиями, используя эту удивительную функцию.

Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel] | Это еще одна важная функция приборной панели. Это поможет вам суммировать значения для конкретных условий.