Formulas and Functions
При изменении этого элемента управления страница автоматически обновится

ПОИСКВСТРОКЕ

Функция ПОИСКВСТРОКЕ выполняет поиск указанного значения в диапазоне и возвращает значение из той же строки в другом столбце.

ПОИСКВСТРОКЕ(значение поиска; диапазон поиска; диапазон возврата; если не найдено; тип соответствия; тип поиска)

значение поиска: значение, поиск которого выполняется в диапазоне поиска. Аргумент значение поиска может содержать любое значение или строку РЕГВЫРАЖЕН.

диапазон поиска: ячейки, в которых выполняется поиск.

диапазон возврата: ячейки, в которые будут возвращены значения.

если не найдено: необязательный аргумент для указания сообщения, отображаемого в том случае, если соответствий не найдено.

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

точное или следующее наименьшее (-1): если нет соответствия, возвращается ошибка.

точное соответствие (0 или пропущено): если нет точного соответствия, возвращается ошибка.

точное или следующее наибольшее (1): если нет соответствия, возвращается ошибка.

подстановочный знак (2): символы «*», «?» и «~» имеют определенные значения. РЕГВЫРАЖЕН может использоваться в функции ПОИСКВСТРОКЕ только при использовании подстановочного знака.

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

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

От последнего к первому (-1): поиск выполняется от последнего элемента диапазона к первому.

От первого к последнему (1 или пропущено): поиск выполняется от первого элемента диапазона к последнему.

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

Примечания

  • Если диапазон поиска или диапазон возврата является сплошной ссылкой (например, «B»), верхние и нижние колонтитулы автоматически игнорируются.

Пример

В следующей таблице «Продукты» указаны продукты и их характеристики, такие как размеры и цена.

A

B

C

D

E

1

Продукт

Длина (см)

Ширина (см)

Вес (кг)

Цена

2

Продукт 1

16

17

10

82,00 $

3

Продукт 2

16

20

18

77,00$

4

Продукт 3

11

11

15

88,00$

5

Продукт 4

15

16

20

63,00$

Поиск с помощью функции ПОИСКВСТРОКЕ

С помощью функции ПОИСКВСТРОКЕ в электронную таблицу можно вставить формулу, которая возвращает любое связанное значение, указав сначала название продукта, а затем столбец с возвращаемым значением. Например, чтобы вернуть значение ширины продукта 1 в вышеприведенной таблице, можно использовать следующую формулу, которая возвращает 17 см:

В редакторе формул показана формула =ПОИСКВСТРОКЕ(Продукты::$A2; Продукты::A; Ширина).

В этой формуле использованы следующие аргументы:

  • значение поиска: Продукты::$A2 — абсолютная ссылка на ячейку в таблице «Продукты», содержащую продукт 1.

  • диапазон поиска: Продукты::A — столбец, в котором следует искать продукт 1.

  • диапазон возврата: ширина — столбец, содержащий возвращаемое значение, связанное с продуктом 1.

  • тип соответствия: пропущено. Если аргумент тип соответствия пропущен, функция ПОИСКВСТРОКЕ по умолчанию ищет точное соответствие.

Настройка строки если не найдено

Чтобы найти продукт определенной длины и вернуть его ширину, а также строку в том случае, если соответствий не найдено, можно использовать следующую формулу, возвращающую «Нет совпадений».

Редактор формул, в котором отображается формула: =ПОИСКВСТРОКЕ(13; Длина; Ширина; "Нет совпадений"; 0).

В этой формуле для конкретизации поиска используется аргумент если не найдено:

  • значение поиска: 13 — значение для поиска в диапазоне, указанном как диапазон поиска.

  • диапазон поиска: Длина — столбец для поиска.

  • диапазон возврата: Ширина — столбец, содержащий значение для возврата, если в результате поиска найдено соответствие значению поиска.

  • если не найдено: «Нет совпадений» — строка, отображаемая в том случае, если продукт с длиной 13 см не найден.

  • тип соответствия: точное соответствие (0). Выполняется поиск только по длине, равной 13 см.

Поиск следующего ближайшего значения

Функция ПОИСКВСТРОКЕ также может выполнять расширенный поиск на основе определенного значения и близких к нему значений. Если изменить тип соответствия в вышеуказанной формуле, можно вернуть ширину продукта длиной 13 см или следующего наименьшего значения. Следующая формула возвращает ширину 11 см:

Редактор формул, в котором отображается формула: =ПОИСКВСТРОКЕ(13; Длина; Ширина; "Нет совпадений"; 1; -1).

Аргументы в этой формуле совпадают с аргументами в формуле выше, но для аргумента тип соответствия используется другое значение, в результате чего меняются параметры поиска в таблице:

  • тип соответствия: точное или следующее наименьшее (-1). В столбце «Длина» выполняется поиск длины, равной 13 см, или следующей наименьшей длины, если это значение не найдено.

Изменение порядка поиска

В некоторых случаях может быть полезно изменить порядок, в котором функция ПОИСКВСТРОКЕ выполняет поиск в таблице. Например, в вышеприведенной таблице есть два продукта с длиной 16 см. Поэтому при поиске значения 16 см в столбце «Длина» с помощью аргументов значение поиска и диапазон поиска будет найдено два потенциальных совпадения. Используя следующую формулу, можно изменить порядок поиска, в результате чего будет возвращено значение 20 см:

Редактор формул, в котором отображается формула: =ПОИСКВСТРОКЕ(16; Длина; Ширина; "Нет совпадений"; 1; -1).

В этой формуле используется аргумент тип поиска, устанавливающий порядок поиска совпадений в таблице функцией ПОИСКВСТРОКЕ:

  • значение поиска: 16 — значение для поиска в диапазоне, указанном как диапазон поиска.

  • диапазон поиска: Длина — столбец для поиска.

  • диапазон возврата: Ширина — столбец, содержащий значение для возврата, если в результате поиска найдено соответствие значению поиска.

  • если не найдено: «Нет совпадений» — строка, отображаемая в том случае, если продукт с длиной 16 см не найден.

  • тип соответствия: точное или следующее наибольшее (1). В столбце «Длина» выполняется поиск длины, равной 16 см, или следующего наибольшего значения, если это значение не найдено.

  • тип поиска: От последнего к первому (-1). Поиск выполняется от последнего значения к первому значению в столбце.

Использование функции ПОИСКВСТРОКЕ с другими функциями

Функцию ПОИСКВСТРОКЕ можно использовать с другими функциями, такими как СУММ. Например, с помощью следующей формулы можно вернуть 247 $ — сумму цен продуктов 1, 2 и 3:

Редактор формул, в котором отображается формула: =СУММ(ПОИСКВСТРОКЕ(Продукты::$A2; Продукты::A; Цена):ПОИСКВСТРОКЕ(Продукты::$A4; Продукты::A; Цена)).

В этом примере первая функция ПОИСКВСТРОКЕ выполняет поиск цены продукта 1, а вторая функция ПОИСКВСТРОКЕ — цены продукта 3. Двоеточие (:) между функциями ПОИСКВСТРОКЕ указывает на то, что функция СУММ должна вернуть не только общую цену продукта 1 и продукта 3, но и все значения между ними.

В следующей формуле функция ПОИСКВСТРОКЕ используется с функцией РЕГВЫРАЖЕН, в результате чего возвращается продукт 2 — первый продукт, ширина которого начинается с цифры 2:

Редактор формул, в котором отображается формула: =ПОИСКВСТРОКЕ(РЕГВЫРАЖЕН("^2.*"); Продукты::C2:C5; Продукты::A2:A5; ЛОЖЬ; 2).

В этом примере для аргумента тип соответствия используется значение «подстановочный знак (2)», в результате чего в функции РЕГВЫРАЖЕН применяются подстановочные знаки.

Дополнительные примеры

Возьмем следующую таблицу:

A

B

C

1

Имя

Воз­раст

Зар­пла­та

2

Анна

35

71000

3

Михаил

27

81000

4

Ольга

42

86000

5

Елена

51

66000

6

Олег

28

52000

7

Антон

49

62000

8

Иван

63

89000

9

Мария

22

34000

10

Алина

29

52000

11

Борис

35

52500

=ПОИСКВСТРОКЕ(49; B2:B11; C2:C11) возвращает 62000 — зарплату первого сотрудника возрастом 49 лет.

=ПОИСКВСТРОКЕ(60000; C2:C11; B2:B11,"Нет совпадений") возвращает «Нет совпадений», так как ни у кого из сотрудников зарплата не равна 60 000.

=ПОИСКВСТРОКЕ(РЕГВЫРАЖЕН("^Е.*"); A2:A11; B2:B11; ЛОЖЬ; 2) возвращает 42 — возраст Екатерины, первого сотрудника в диапазоне, чье имя начинается на «Е».

См. такжеМЕСТОВСТРОКЕ
Morty Proxy This is a proxified and sanitized view of the page, visit original site.
Эта страница полезна?
Максимальное количество символов: 250
Максимальное количество символов: 250.
Благодарим вас за отзыв.
Morty Proxy This is a proxified and sanitized view of the page, visit original site.