Если ячейка содержит 1 или более ключевых слов, измените значение другой ячейки.

У меня есть столбец с некоторым строковым описанием.

for example:

Bob davids
mowing the lawn
tipping cows

Кроме того, на другом листе или в столбце у меня будет список ключевых слов. Например, рабочий список ключевых слов 1:

davids
work

play keyword list:

mowing
cows

Так как основной столбец заполнен этими текстовыми строками, я хотел бы, чтобы они автоматически проверяли каждый список ключевых слов, чтобы увидеть, существуют ли эти слова, и когда он находит совпадение, поместите заголовок списка (работа/воспроизведение) в следующую ячейку к этому.

Я знаю, что это возможно в VBA, и даже могу сделать это "на лету" в функции SelectionChange. Но можно ли обойтись без VBA типа условного форматирования?


excel conditional-formatting vba
person NickG    schedule 22.03.2011    source источник
comment
Есть ли ограничение на количество возможных слов в строке? Разделить их и найти легко. Однако, если их много, вам нужно написать очень длинную формулу, чтобы разделить каждое слово.   -  person paulmorriss    schedule 22.03.2011
comment
Хм, это отличается, но я бы сказал, что максимум 8   -  person NickG    schedule 22.03.2011
comment
Но для этого случая давайте просто предположим 2   -  person NickG    schedule 22.03.2011

Ответы (2)


arrow_upward
9
arrow_downward

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

=OR(NOT(ISERROR(FIND(<array of strings to look for>,<string to look in>))))

Это нужно ввести как формулу массива, чтобы она работала. Вы делаете это, вводя его с помощью Ctrl-Shift-Enter. Чтобы понять, как это работает, рассмотрим, что делает Excel при оценке реального примера:

=OR(NOT(ISERROR(FIND({"a","b","c"},"q b q"))))

'НАЙТИ' находит положение одной строки в другой. При вызове с массивом в качестве первого аргумента возвращается массив позиций (или #ЗНАЧ!, если строка поиска не найдена). Вы можете проследить оценку, введя эту формулу, а затем используя клавишу F9 для выражений внутри нее:

=OR(NOT(ISERROR({#VALUE!,3,#VALUE!})))
=OR(NOT({TRUE,FALSE,TRUE}))
=OR({FALSE,TRUE,FALSE})
=TRUE

Итак, для вашего примера предположим, что ваши строки, которые вы хотите найти, были в $B$6:$B$8, ваши рабочие строки — в $D$2:$D$3, а ваши игровые строки — в $E$2:$E$3. можно формулу поставить

=OR(NOT(ISERROR(FIND(D$2:D$3,$B6))))

в ячейке D6 введите ее как формулу массива, а затем перетащите ее через диапазон D6:E8, чтобы найти, в каких строках в B были рабочие или игровые слова. Затем вы можете использовать эти результаты для управления дальнейшими формулами или условным форматированием.

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

=OR(NOT(ISERROR(FIND({"a","b","c"},"bad"))))

будет оцениваться как ИСТИНА. (И если ваш забавный список включает «id», «id» в «davids» будет совпадать.)

Как это часто бывает с Excel, если вы делаете что-то понятное с ограниченным набором данных, вам может быть все равно. Но это может помешать попытке использовать формулу такого рода как часть общего «приложения», в котором есть пользователи, которые не знают причудливых трюков с массивами или точно того, что делает «НАЙТИ». (Вы можете как-то обойти это, поставив пробел после слов поиска и т. д., но это просто более таинственное вуду, ожидающее взлома, если вы передадите его кому-то другому.) Однако для быстрого и грязного сканирования это нормально. .

person jtolle    schedule 22.03.2011
comment
По какой-то причине ISERROR(FIND({"a","b","c"},"bad")) для меня оценивается только скалярным значением, которое, похоже, основано на первом результате операции поиска (т.е. find("a", "bad")). Любые идеи? - person Grzenio; 15.01.2015
comment
@Grzenio, вы ввели это как формулу массива и в диапазон, в котором достаточно места для отображения всех трех элементов? - person jtolle; 15.01.2015
comment
Это была моя ошибка, я не ввел это как формулу массива. Спасибо! - person Grzenio; 16.01.2015

arrow_upward
1
arrow_downward

Я собрал еще один способ сделать это для небольшого числа. Как было сказано, это быстро становится громоздким.

Введите в столбец A следующее: 1: лошадь, корова, свинья, курица, 2: собака, кошка, птица, 3: рыба, кошка, 4: лошадь, кошка, мышь, 5: обезьяна, человек, дельфин, 6: собака, кошка, бурундук.

В ячейке B1 введите следующее (и скопируйте в ячейку B2: B6):

=if(sum(iserror(find("cat",A1)),iserror(find("dog",A1)),iserror(find("fish",A1)),iserror(find("bird",A1)))<4,"House pet","")

Результат в B1 и B5 должен отображаться пустым, тогда как результат в B2, B3, B4 и B5 должен читаться как «Домашнее животное».

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

Я не мог заставить свои массивы работать - спасибо за сообщение выше, я собираюсь попробовать это сейчас и для своих более длинных списков.

пс. Не помню, что я делал с моим массивом OR ISERROR, но этот массив, который вы предоставили, прекрасно работал для моего списка из 80 ключевых слов. Спасибо!

person user2163442    schedule 13.03.2013