Вопрос или проблема
Мне нужна формула, которая добавляет имя медсестры из столбца F в столбец D, если они занимаются этой комнатой (с G3 по K7).
Например: Медсестра A сейчас находится в комнатах 2101, 2108, 2155, 2165, поэтому ее имя должно отображаться в строке конкретной комнаты.
| Комната | Имя пациента/Возраст | Код | Медсестра |
|---|---|---|---|
| 2101 | Заполнено | ||
| 2102 | Заполнено | ||
| 2103 | Заполнено | ||
| 2104 | FC | ||
| 2105 | |||
| 2106 | Заполнено | ||
| 2107 | |||
| 2108 | Заполнено | ||
| 2109 | Заполнено | ||
| 2110 | Заполнено | ||
| 2151 | Заполнено | ||
| 2152 | |||
| 2153 | Заполнено | ||
| 2154 | Заполнено | ||
| 2155 | FC | ||
| 2156 | Заполнено | ||
| 2157 | Заполнено | ||
| 2161 | Заполнено | ||
| 2162 | Заполнено | ||
| 2163 | |||
| 2164 |
| Медсестра | Комната 1 | Комната 2 | Комната 3 | Комната 4 | Комната 5 |
|---|---|---|---|---|---|
| A | 2101 | 2108 | 2155 | 2165 | |
| B | 2102 | 2109 | 2156 | 2166 | |
| C | 2103 | 2110 | 2157 | 2167 | |
| D | 2104 | 2151 | 2161 | 2168 | |
| E | 2105 | 2152 | 2162 | 2169 | |
| F | 2106 | 2153 | 2163 | 2170 | |
| G | 2107 | 2154 | 2164 | 2170 |
Я использовал следующую =IF(A3=H3,F3,"") и это сработало, но только для одной ячейки. Я попробовал использовать несколько ячеек и в итоге сломал таблицу.
Попробуйте использовать любую из следующих формул по вашему выбору:
• Используя IFS() + TOCOL()
=TOCOL(IFS(A3=G$3:K$9,F$3:F$9),2)
• Или, используя REPT() + CONCAT()
=CONCAT(REPT(F$3:F$9,G$3:K$9=A3))
• Используя MAP() + TOCOL() + IFS() для возврата всего диапазона в виде массива.
=MAP(A3:A23,LAMBDA(x,TOCOL(IFS(G3:K9=x,F3:F9),2)))
• Или, старые версии — используя INDEX()+AGGREGATE()+IFERROR():
=IFERROR(INDEX(F$3:F$9,AGGREGATE(15,7,(ROW(F$3:F$9)-ROW(F$3)+1)/(G$3:K$9=A3),1)),"")
• Или, ETA LAMBDA() с использованием FILTER()+BYROW()+OR()
=FILTER(F$3:F$9,BYROW(G$3:K$9=A3,OR),"")
• Или, используя MAP()+ETA LAMBDA() –> BYROW()+FILTER()+OR()
=MAP(A3:A23,LAMBDA(x,FILTER(F3:F9,BYROW(G3:K9=x,OR),"")))
• Или используя MMULT() + FILTER()
=FILTER(F$3:F$9,MMULT(N(G$3:K$9=A3),TOCOL(--RIGHT(G$2:K$2))^0),"")
Представил возможные способы, так как можно вернуть результат разными способами, вышеперечисленные решения дают представление о концепции массивов и помогают в обучении.
Ответ или решение
Как использовать INDEX и MATCH для двустороннего поиска значений в Excel
Использование функций INDEX и MATCH в Excel позволяет осуществлять сложные двухмерные поиски данных. В этой статье мы разберем, как применять эти функции, чтобы извлекать имена медсестер на основе соответствующих им номеров палат, как это требуется в вашей задаче.
1. Обзор задачи
Ваша задача состоит в том, чтобы ввести имя медсестры в столбец D, основываясь на номерах палат, перечисленных в столбцах G до K. У вас есть два набора данных: один с палатами и соответствующими медсестрами, а другой — с распределением палат между медсестрами.
2. Подготовка данных
Допустим, ваш лист Excel выглядит следующим образом:
- В диапазоне G3:K7 у вас размещены номера палат.
- В столбце F3:F9 указаны имена медсестер.
- В столбце A3:A23 вам необходимо заполнить имена медсестер в зависимости от палат.
3. Установка формулы
Для реализации двустороннего поиска мы будем использовать функции INDEX и MATCH. Здесь мы представим формулу, которая проверяет, принадлежит ли конкретная палата определенной медсестре, и возвращает соответствующее имя.
Формула INDEX MATCH
Как правило, формула может выглядеть следующим образом:
=IFERROR(INDEX($F$3:$F$9, MATCH(A3, INDEX($G$3:$K$9, 0, ), 0)), "")Разбор компонентов формулы:
INDEX($F$3:$F$9, ...)— возвращает имя медсестры из столбца F.MATCH(A3, INDEX($G$3:$K$9, 0, ), 0)— ищет номер палаты (в ячейке A3) в диапазоне палат (G3:K7).IFERROR(..., "")— предотвращает появление ошибки, если совпадение не найдено.
4. Применение формулы по всему диапазону
Для того чтобы применить данную формулу ко всем необходимым строкам, просто перетащите маркер заполнения (маленький квадрат в правом нижнем углу ячейки) вниз по столбцу D.
5. Альтернативные подходы
Для более продвинутых пользователей есть альтернативные методы, например:
-
Использование массива:
=FILTER($F$3:$F$9, BYROW($G$3:$K$9, LAMBDA(x, x=A3)), "") -
Использование
AGGREGATE:=IFERROR(INDEX($F$3:$F$9, AGGREGATE(15, 6, (ROW($F$3:$F$9)-ROW($F$3)+1)/($G$3:$K$9=A3), 1)), "")
Эти методы могут быть более эффективными в зависимости от версии Excel и структуры ваших данных.
6. Заключение
Функции INDEX и MATCH являются мощными инструментами для работы с таблицами данных в Excel, что позволяет не только значительно упростить процесс поиска, но и повысить точность результатов. Практикуйте использование этих функций в различных сценариях для повышения ваших навыков и уверенности в работе с данными.
Теперь, используя предложенные формулы и подходы, вы сможете легко добавлять имена медсестер в зависимости от их распределения по палатам, что улучшит качество вашей таблицы и упростит анализ данных.