☰

Проверьте, с какого ключевого слова из списка начинается ячейка, и верните соответствующее ключевое слово.

tag-icon • tag-icon microsoft-excel worksheet-function
Проверьте, с какого ключевого слова из списка начинается ячейка, и верните соответствующее ключевое слово.

Я пытаюсь взять список товаров и извлечь их производителя из начала названия товара. Каждое название товара начинается с производителя. Это осложняется тем, что некоторые товары имеют других производителей в тексте названия; мне нужно увидеть, с чего начинается товар. Я имею дело с более чем 50 000 товаров и более чем 3000 производителей. Формула, которую я получил на данный момент, выглядит так:

=LOOKUP(1,1/(FIND($C$2:$C$5,B2)),$C$2:$C$5)

Иногда это работает, иногда нет. Например, в приведенном ниже рабочем листе строки 2 и 3 верны, а строка 4 — нет. Результатом в ячейке  A4для «Mike's Fun Toys» (в ячейке B4) должно быть «Mike's», но выводится как «Fun».

Рабочий лист с производителями, перечисленными в обратном алфавитном порядке.

(Вот данные в текстовом виде, которые вы можете скопировать и вставить:

+---+---------+-----------------+---------------+
|   |    A    |        B        |       C       |
+---+---------+-----------------+---------------+
| 1 | Formula | Items           | Manufacturers |
+---+---------+-----------------+---------------+
| 2 | Brown   | Brown Cat Toys  | Mike's        |
| 3 | Cat     | Cat Fun Toys    | Fun           |
| 4 | Fun     | Mike's Fun Toys | Cat           |
| 5 |         |                 | Brown         |
+---+---------+-----------------+---------------+

)

Но когда я меняю порядок столбцов  C(Производители):

Рабочий лист с производителями, перечисленными в (обычном) алфавитном порядке

Строка 4 становится правильной («Майк»), но строка 2 становится неправильной.

В столбце A формула находится от начала до конца. Ожидаемые результаты:

A2 - Brown
A3 - Cat
A4 - Mike's

Как заставить формулу работать независимо от порядка столбцов  C?

решение1

Для читателей, которые не понимают формулу Райана Марка, это

  • Поиск позиции каждого из названий производителей в данном названии продукта. Это приводит к
    • значение 1для имени производителя, с которого начинается название продукта (потому что оно появляется в начале)11-й символ),
    • более высокие номера для названий других производителей, которые появляются в названии продукта (потому что они появляются позже, на более высоких позициях), и
    • код #VALUE!ошибки для названий производителей, которые не указаны в названии продукта.
  • Инвертируем каждое из вышеприведенных выражений (делим на 1), в результате чего получаем
    • 1для имени производителя, с которого начинается название продукта (которое мы и хотим найти),
    • меньшие положительные числа для названий других производителей, которые встречаются в названии продукта (потому что 1, деленная на число, большее 1, дает отношение меньшее 1), и
    • код #VALUE!ошибки для названий производителей, которые не указаны в названии продукта.
  • Используя LOOKUPдля поиска 1в приведенном выше примере.

Например, для ячейки A4(соответствующей «Игрушкам Майка» в ячейке B4), на первом изображении, мы получаем, по порядку,

  • 1, потому что «Mike's» ( C2) начинается с «Mike's Fun Toys»,
  • 8, потому что «Веселье» ( C3) появляется на 8-м месте в «Игрушках Майка» и
  • #VALUE!и #VALUE!, поскольку «Кот» ( C4) и «Коричневый» ( C5) не встречаются в «Забавных игрушках Майка».

Инвертирование, которое приводит к 1,  0.125( 1/8),  #VALUE!и #VALUE!. Затем он ищет 1в этом массиве. Это «должно» работать, потому что 1— первый результат, а «Mike's» — первое имя в столбце  C.

Проблему можно увидеть на странице справки LOOKUP:

ДляИСКАТЬДля корректной работы функции искомые данные должны быть отсортированы в порядке возрастания.

и явно 1за которым следует 0.125не сортируется в порядке возрастания.

Как LOOKUPпредполагает, мы можем решить это с помощью MATCH. Формула, которую вы хотите получить, которая использует тот же базовый подход, что и ваша формула (за исключением инвертирования, которое не нужно), выглядит так:

=INDEX($C$2:$C$5, MATCH(1, FIND($C$2:$C$5,$B2), 0))

Третий аргумент MATCHназывается «match_type». Я установил его 0здесь, что означает, что MATCHбудет искать первый элемент в массиве, который точно равен 1, и не будет предполагать, что массив отсортирован.

Это формула массива, поэтому при ее вводе нужно нажать Ctrl+ Shift+ .Enter

Связанный контент