Вопрос или проблема
Я надеюсь на дополнительную помощь в моем исследовании птиц. Все были так полезны, и я использую все ваши предложения, это действительно помогло мне справиться с более чем 6500 случаями!
Вот моя новая дилемма: я хочу проверить ячейку (ДИАГНОЗ ДЛЯ КАЖДОГО СЛУЧАЯ), чтобы увидеть, включает ли она какую-либо строку из диапазона элементов (ВСЕ ДИАГНОЗЫ) И также отмечена “Y” для квалификатора (ШОК, в этом примере) для этого элемента в диапазоне.
Я пробовал =IF(SUMPRODUCT(--ISNUMBER(SEARCH($B,D2:D4)))>0,"Y","N"), размещенный в $C, и это привело меня на полпути, но мне нужен этот второй квалификатор, и, похоже, я не могу сделать это самостоятельно.
В этом примере столбец B – это столбец поиска, столбец D – это диапазон, столбец E – квалификатор для диапазона, а столбец C – желаемый вывод. Я предпочел бы сделать это без необходимости конвертировать мои данные в таблицы.
| СЛУЧАЙ | ДИАГНОЗЫ ДЛЯ КАЖДОГО СЛУЧАЯ | ЖЕЛАЕМЫЙ ВЫВОД | ВСЕ ДИАГНОЗЫ (ДИАПАЗОН) | ШОК (ДИАПАЗОН) |
|---|---|---|---|---|
| 1 | ШОК, ЛЕГОЧНЫЙ; ГИПОКСИЯ | Y | ШОК | Y |
| 2 | ШОК | Y | ШОК, ЛЕГОЧНЫЙ | Y |
| 3 | ШОК, ЛЕГОЧНЫЙ | Y | ГИПОКСИЯ | N |
| 4 | N | |||
| 5 | ГИПОКСИЯ | N |
Вот один из способов, который вы можете попробовать:
=IF(OR(IFERROR(SEARCH($D$2:$D$4,TEXTSPLIT(B2,"; ")),0)*($E$2:$E$4="Y")),"Y","N")
Или, если вы хотите вернуться ко всему массиву, тогда:
=MAP(B2:B6,LAMBDA(x,IF(OR(IFERROR(SEARCH(D2:D4,TEXTSPLIT(x,"; ")),0)*(E2:E4="Y")),"Y","N")))
Можно использовать функцию TEXT(), чтобы вернуть Y и N, а также настраиваемое форматирование, как в последнем запросе:
=TEXT(N(OR(IFERROR(SEARCH($D$2:$D$4,TEXTSPLIT(B2,"; ")),0)*($E$2:$E$4="Y"))),"[=1]\Y;\N")
Также, если применимо, можно воспользоваться новой функцией, доступной в MS365 Office Insiders, TRIMRANGE() или ее функциями.
=LET(
_Search, DROP(B.:.B,1),
_Range, DROP(D.:.E,1),
MAP(_Search,LAMBDA(x,
IF(OR(IFERROR(SEARCH(TAKE(_Range,,1),TEXTSPLIT(x,"; ")),0)*
(DROP(_Range,,1)="Y")),"Y","N"))))
- отсортируйте “ВСЕ ДИАГНОЗЫ” для более быстрого поиска с помощью
XMATCH - разделите каждую запись диагноза по
;или'и выполните поиск - по крайней мере один совпадает, если
SUM(--ISNUMBER(XMATCH(TRIM(TEXTSPLIT(d_e, {",",";"})), all_diag> 0
введите в C2 (и отрегулируйте диапазоны, чтобы они соответствовали вашим данным):
=LET(
all_diag, SORT(TOCOL(TRIM(D2:D100), 1)),
diag_for_each, B2:B100, qualified, E2:E100 = "Y",
includes_diag, MAP(diag_for_each, LAMBDA(d_e,
IF(ISBLANK(d_e), 0,
SUM(--ISNUMBER(XMATCH(TRIM(TEXTSPLIT(d_e, {",",";"})), all_diag, , 2)))))),
IFS(ISBLANK(diag_for_each), "", includes_diag * qualified, "Y", TRUE, "N")
)
Ответ или решение
Решение задачи проверки наличия строки и дополнительных критериев в Excel
Вы столкнулись с задачей анализа данных в Excel, касающейся вашего исследования о птицах. Ваша задача состоит в том, чтобы проверить, содержит ли определенная ячейка строку из заданного диапазона и удовлетворяет ли она дополнительному критерию из другого столбца. Давайте рассмотрим, как легко это можно сделать, используя формулы Excel.
Описание проблемы
Вы имеете четыре столбца данных:
- DIAGNOSES FOR EACH CASE (Диагнозы для каждого случая) – столбец B
- DESIRED OUTPUT (Желаемый результат) – столбец C, куда будет выводиться результат
- ALL DIAGNOSES (RANGE) (Все диагнозы) – столбец D
- SHOCK (RANGE) (Шок) – столбец E, где вы помечаете "Y" для соответствия критерию
Ваша цель – для каждого случая проверить, содержится ли какой-либо диагноз из столбца D в соответствующем диагнозе из столбца B и установлен ли соответствующий критерий в столбце E.
Подход к решению
Мы можем использовать формулы SEARCH, IF, SUMPRODUCT и другие, чтобы создать сложную логику. Начнем с простой формулы и постепенно добавим дополнительные условия.
Формулы для решения задачи
- Использование формулы с
IFиSEARCH:
Вы можете использовать следующую формулу в ячейке C2:
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH($D$2:$D$4, B2)))) > 0 * ($E$2:$E$4="Y"), "Y", "N")Эта формула проверяет, содержит ли ячейка из столбца B хотя бы один диагноз из диапазона D и соответствует ли она дополнительному критерию в столбце E.
- Для обработки всех значений массива:
Если вы хотите сразу обработать все случаи в диапазоне, вы можете воспользоваться функцией MAP и LAMBDA:
=MAP(B2:B6, LAMBDA(x, IF(SUMPRODUCT(--(ISNUMBER(SEARCH($D$2:$D$4, x)))) * (($E$2:$E$4="Y")), "Y", "N")))Эта формула возвращает "Y" или "N" в зависимости от выполнения условий для каждой строки.
- Другой способ с использованием массива и функций
TEXTSPLIT:
Если ваши диагнозы разделены символом ;", " (запятая и пробел), можно использовать функцию TEXTSPLIT, чтобы разбить их в формуле:
=IF(OR(IFERROR(SEARCH($D$2:$D$4, TEXTSPLIT(B2, "; ")), 0) * ($E$2:$E$4="Y")), "Y", "N")Эта формула разбивает строки диагнозов и проверяет наличие соответствующего критерия, после чего возвращает "Y" или "N".
Завершение
Решение вашей задачи в Excel возможно с помощью различных формул, в зависимости от структуры данных и версии Excel, которую вы используете. Приведенные выше формулы можно адаптировать под ваши специфические нужды. Экспериментируйте с формулами, чтобы получить желаемые результаты, и не забудьте корректно настроить диапазоны в соответствии с вашими данными.
Понимание этих техник не только облегчает анализ данных, но и улучшает ваши навыки работы с Excel, что является неотъемлемой частью эффективного управления исследовательскими проектами.