Итак, у меня есть список пациентов. А затем у меня есть список заказов на продажу, которые соответствуют клиническим визитам, которые им нужно было получить. Я хочу увидеть, кто был осмотрен, а кто нет.
Итак, на листе 1 у меня есть список пациентов и их идентификационные номера (идентификатор пациента уникален): Идентификатор пациента, Имя пациента.
Затем на листе 2 у меня есть список заказов на продажу (идентификатор заказа на продажу уникален, идентификатор пациента — нет): идентификатор пациента, идентификатор заказа на продажу, дата заказа на продажу.
Мне нужно, чтобы Excel просмотрел заказы на продажу на листе 2, нашел совпадения с идентификатором пациента, а затем перенес даты в указанной последовательности.
Таким образом, Лист 1 будет выглядеть так: Идентификатор пациента, Имя пациента, Дата заказа на продажу 1, Дата заказа на продажу 2, ноль (когда больше нет совпадений).
Я прочитал все, что смог найти о функциях ВПР и ИНДЕКС/ПОИСКПОЗ, и, похоже, не могу найти функцию, которая работала бы при наличии нескольких совпадений на втором листе (идентификатор пациента был бы одним и тем же для каждого заказа на продажу на листе 2).
Пожалуйста помоги.
решение1
Вот решение, которое не требует никаких вспомогательных столбцов или сортировки любой из таблиц. Оно просто использует одну относительно простую формулу массива.
Настройте два рабочих листа следующим образом, назвав второй лист Sheet2:
Массив введите ( Ctrl+ Shift+ Enter) следующую формулу в ячейку C3первого листа и скопируйте-вставьте/заполните в C3:G7:
{=IFERROR(SMALL(IFERROR(1/(1/((Sheet2!$A$2:$A$20=$A3)*(Sheet2!$C$2:$C$20))),""),C$2),"")}
Имейте в виду, что если заполнена последняя ячейка «Дата» таблицы «Пациенты» на Листе 1, томожетбольше дат, которые не отображаются. В примере рабочего листа я добавил формулу в столбец справа от таблицы, чтобы предупредить, если это так:
Эта формула, массив, введенный H3и скопированный/вставленный в H3:H7, выглядит следующим образом:
{=IF(ISERROR(SMALL(IFERROR(1/(1/((Sheet2!$A$2:$A$20=$A3)*(Sheet2!$C$2:$C$20))),""),G$2+1)),"","more")}
решение2
Эту проблему можно решить, подсчитав заказы на одного клиента. Существует множество вариантов решения этой проблемы, но вот один из них.
Шаг 1:
Отсортируйте таблицу заказов, отсортировав сначала самые новые заказы.
Шаг 2:
Добавьте столбец в конце ваших заказов на продажу с формулой, которая подсчитывает количество клиентов. В новом столбце, во второй строке, напишите формулу =Countif(B$2:B2,B2).
Скопируйте формулу вниз.
B— это столбец, в котором находятся номера клиентов.
Шаг 3:
Добавьте столбец с уникальным ключом для определения номера клиента и количества заказов. Во втором новом столбце, во второй строке напишите формулу=B2&" "&X2
B— номер клиента и Xколичество заказов, которые вы сделали на шаге 2.
Шаг 4:
В таблице пациентов добавьте столбец, который индексирует идентификатор для самого нового заказа, второго самого нового заказа и т. д., чтобы вернуть дату. Используйте , IFERROR()чтобы вернуть пробел, если совпадений нет, и используйте абсолютные ссылки, чтобы формулы можно было легко скопировать:
(Я попытался перевести формулу из моего норвежского Excel)
=IFERROR(INDEX('Order list'!$C:$C,MATCH('Patient list'!$A2&" "&'Patient list'!B$1,'Order list'!$Y:$Y,0)),"")
где Cнаходится столбец с датами, а Yгде находится ключ в шаге 3.
Надеюсь, это поможет вам найти правильное решение для ваших таблиц. Вероятно, вы сможете автоматизировать формулы в таблице заказов и т. д.
Заказы:
Пациенты:
