Как настроить условную формулу с несколькими критериями?

306
Lisa Beck

Недавно мне нужно было найти совпадения в любом месте столбца. Эта статья здесь

Как определить ячейки в столбце A, которые содержат любое из значений в столбце B

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

Это может помочь узнать следующее:

  • Общее количество элементов в столбце E меньше, чем в столбце T (в значительном размере).
  • Ячейки в столбце E содержат одно слово, и в этом столбце нет дубликатов.
  • Ячейки в столбце T содержат одно слово, и в этом столбце нет дубликатов.
  • Ячейки в столбце V содержат либо «Совпадение», «þ» (отмеченный флажок), либо являются пустыми.
  • Содержимое ячеек в столбце E может или не может существовать в столбце T.
  • «Соответствие» означает, что ячейки в столбце T соответствуют словам в столбце, не показанном на изображении выше. Другими словами, он не предназначен для передачи совпадения с содержимым ячеек в столбце Е.

Теперь вот что я хотел бы сделать с этими столбцами информации:

  • Если содержимое ячейки в E не совпадает ни с одной из ячеек в T, верните «Только V3» в столбце X.
  • Если содержимое ячейки в E находится в столбце T, а столбец V содержит «Match», верните «Match» в столбце X.
  • Если содержимое ячейки в E находится в столбце T, а столбец V содержит флажок («þ»), верните «þ» (флажок) в столбце X.

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

В случае, если это имеет значение, мне нужна эта формула для работы в Excel 2007.

Заранее спасибо всем, кто знает, как это сделать и готов поделиться этим с нами.

-1
пожалуйста, опубликуйте формулу, которую вы использовали в первую очередь. Máté Juhász 7 лет назад 0
Справедливо, @ MátéJuhász. Вот одна из моих последних попыток: = ЕСЛИ (ТОЧНО (E2, T2: T2331), & ЕСЛИ (V2 = "Совпадение", "Совпадение") ИЛИ ЕСЛИ (V2 = "þ", "þ"). Lisa Beck 7 лет назад 0
Приведенная выше формула просто вернет соответствующий код для тех слов, которые содержатся в столбце T. Я даже не знаю, с чего начать, чтобы добавить «Только V3», если слово не содержится в столбце T. Я подумал, что Я должен работать над тем, чтобы решить один аспект этой проблемы, прежде чем добавлять к нему, но единственная формула, которая может выполнить все три условия, перечисленные в пунктах, указанных в моем вопросе выше, будет идеальной. Похоже, мне может понадобиться поиграться с ISERROR, но я не слишком знаком с этой функцией. На самом деле, я немного не в себе, поэтому любая помощь приветствуется. Lisa Beck 7 лет назад 0
Я также пробовал это: = IF (EXACT (E2, T2: T2331), "" & IF (V2 = "Match", "Match") ИЛИ IF (V2 = "þ", "þ")), что просто дает "ЛОЖНЫЙ." Я предполагаю, что одна из частей головоломки, которую мне не хватает, это как определить, соответствует ли одна ячейка в одном столбце какой-либо из ячеек в другом столбце. Пост, на который я ссылаюсь в моем вопросе выше, дал мне простую формулу, когда вы сравниваете одну ячейку с соседней, а не одну ячейку с целым столбцом ячеек. Я предполагаю, что мне нужно использовать какую-то функцию LOOKUP, и если да, то как и какую? Lisa Beck 7 лет назад 0
Я должен добавить, что первый код, который я перечислил в этой теме, был _intended_, чтобы вернуть соответствующий код. Это на самом деле не работает. Lisa Beck 7 лет назад 0

2 ответа на вопрос

0
Máté Juhász

Если содержимое ячейки в E найдено в столбце T, а в столбце V содержится «Match», верните «Match» в столбце X.
Если содержимое ячейки в E найдено в столбце T, а в столбце V содержится флажок ( «þ»), верните «þ» (флажок) в столбце X.

Я бы просто добавил, «если содержимое ячейки в E найдено в столбце T, а столбец V пуст, а затем вернуть пустую строку в столбце X».
И мы можем упростить все вышеперечисленное, если найдено содержимое ячейки в E в столбце T, затем верните столбец V в столбец X

Таким образом, вам не нужно использовать сложные условия:
=SUBSTITUTE(IF(ISNUMBER(MATCH(T1,$E$1:$E$19,0)),V1,"Only "&ADDRESS(ROW(V1),COLUMN(V1),4)),"0","")

куда

  • ISNUMBER(MATCH(T1,$E$1:$E$19,0)) - проверяет, существует ли значение из T1 в столбце E
  • "Only "&ADDRESS(ROW(V1),COLUMN(V1),4)) - генерирует адрес V1
  • IF(ISNUMBER(...),V1,"Only "&...) - Если T1 существует в столбце E, то вводится V1, иначе «Только V1»
  • =SUBSTITUTE(IF(...),"0","")- если V1 пусто, то формула конвертирует его в 0, это преобразует его в""
  • это формула массива, поэтому вам нужно вставить ее, нажав CTRL+ SHIFT+ENTER

Я действительно ценю ваши усилия здесь, @ MáteJuhász. Я должен был добавить, что «Только V3» указывает, что весь список в столбце E происходит от чего-то, что я называю V3 (или, точнее, «Том 3»). Итак, я не использовал АДРЕС FX. Однако недавно я прочитал об ADDRESS fx, и то, что вы используете его в формуле, дало мне некоторые идеи для некоторых других вещей, которые я хочу сделать. Lisa Beck 7 лет назад 0
Что касается остальной части формулы, похоже, что она не работает, поскольку каждая отдельная ячейка вернула «Only V3» (и я знаю, что по крайней мере одна должна быть найдена как совпадение в другом списке). Возможно, я не правильно использовал вашу формулу. Это то, что я использовал: = ЗАМЕНА (ЕСЛИ (ISNUMBER (МАТЧ (X1, $ F $ 1: $ F $ 19,0)), V1, «Только V3»), «0», «») (который был введен как массив, согласно вашим инструкциям). Lisa Beck 7 лет назад 0
почему вы включили в него `X1`? `$ F $ 1: $ F $ 19` - это тот же диапазон для вас ?? в какую клетку вы его ввели? Máté Juhász 7 лет назад 0
Не все мои эксперименты добрались до этой темы. Один из них включал в себя то, что включало дополнительные столбцы. Это был все еще правильный столбец для использования. Lisa Beck 7 лет назад 0
-1
Lisa Beck

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

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

в которой я наткнулся на следующую формулу:

= IF (ISERROR (MATCH (A1, B: B, 0)), «Нет совпадений», «Совпадение»)

Я применил это к моей проблеме с этой формулой [упрощено, чтобы было легче читать]:

= IF (ISERROR (MATCH (C2, '[OTHER WORKBOOK.xlsx] SHEET'! $ F: $ F, 0)), "Нет совпадений", "Совпадение")

Обратите внимание, что мой оригинальный столбец E стал столбцом F из-за вставки столбца по другим причинам.

В итоге получилось так:

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

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

Похожие вопросы