☰

Заполнить столбец из другого столбца для каждой строки в группе

tag-icon • tag-icon microsoft-excel
Заполнить столбец из другого столбца для каждой строки в группе

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

Ради этого поста я упрощу задачу: у меня есть as/sheet с тремя столбцами (A, B и C) с заголовками [тип данных] следующим образом: Name [текст], ID [число], CreateDate [дата]. Данные были отсортированы по Col A, чтобы намеренно выделить повторяющиеся значения в этом столбце. ID уникальны; датынетуникальный.

В примере имеется девять строк, которые можно «сгруппировать» в три группы за счет дублирующихся значений в столбце A. Таким образом, для строк 2 и 3 значение в столбце A равно abc, для строк 4, 5 и 6 значение в столбце A равно def, а для строк 7-10 оно равно ghi.

Первая задача — определить последнюю дату создания в каждой из «групп строк». Так что в этом примере это будет 5/11/1999 для строк 2 и 3, 3/12/2001 для строк 4-6 и 5/11/1999 для строк 7-10. Я делаю это, используя формулу массива {=MAX(IF(A2=$A:$A,$C:$C))} в столбце D.

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

Поместить соответствующий идентификатор в столбце E в той же строке легко (оператор IF) – см. строки 3, 6 и 9 – но я не притворяюсь, что это решение. Но я не могу понять, как заполнить другие строки в каждой группе тем же идентификатором – в моем прикрепленном примере это были бы строки 2, 4 и 5, 7 и 8 и 10. Я не могу просто выполнить поиск в столбце C, потому что эти значения не уникальны. После тщетных попыток с различными формулами я в растерянности. Я предполагаю, что он должен полагаться на столбец C (соответствующие значения) и функции, такие как INDEXи  MATCH... или, может быть, VBA.

     +------+------+------------+------------+-----------------+
     |  A   |   B  |     C      |     D      |        E        |
+----+------+------+------------+------------+-----------------+
|  1 | Name |  ID  | CreateDate | LatestDate | CorrespondingID |
|  2 | abc  |   1  | 4/12/1998  | 5/11/1999  |         2       |
|  3 | abc  |   2  | 5/11/1999  | 5/11/1999  |         2       |
|  4 | def  |   3  | 1/12/1999  | 3/12/2001  |         5       |
|  5 | def  |   4  | 5/11/1999  | 3/12/2001  |         5       |
|  6 | def  |   5  | 3/12/2001  | 3/12/2001  |         5       |
|  7 | ghi  |  17  | 1/17/1999  | 5/11/1999  |        55       |
|  8 | ghi  |  42  | 2/4/1999   | 5/11/1999  |        55       |
|  9 | ghi  |  55  | 5/11/1999  | 5/11/1999  |        55       |
| 10 | ghi  |  83  | 3/28/1999  | 5/11/1999  |        55       |
+----+------+------+------------+------------+-----------------+

(Более короткий пример доступен в виде изображения.)

решение1

Моя ссылка:здесь. TLDR: используйте версию формулы сопоставления индексов без массива.

В E2 поместите:

=INDEX(B:B,MATCH(1,INDEX((A2=A:A)*(D2=C:C),0,1),0))

Идея: внутренний index() генерирует список 0 и 1, который соответствует обоим критериям имени и даты. Затем внешний index() используется для «загрузки» имени из A:A.

решение2

Это решение использует метод сопоставления смещения. Использование сопоставления для поиска даты, а затем смещение на основе даты для поиска соответствующего идентификатора:

=СМЕЩ(C2,ПОИСКПОЗ(D2,C2:C,0)-1,-1)

Перетащите это в ячейку E2, затем перетащите вниз по всей ячейке E.

решение3

Для заданной строки (например, строки 2) вы хотите найти идентификатор (столбец  B) из строки н(т.е. Cell  ), где имя ( ) равно имени из текущей строки ( ), а CreatedDate ( ) равна LatestDate из текущей строки ( ). Строка BnAnA2CnD2нуникален, потому что идентификаторы уникальны, и поэтому «the"строка — это максимальная строка. Логичным ответом было бы небольшое расширение вашей формулы для столбца  D:

=MAX(IF(AND(A2=$A:$A,D2=$C:$C), $B:$B))

К сожалению, ANDпохоже, не работает в формулах массива. Поэтому мы используем стандартный трюк: TRUE = 1 (или что-то, кроме нуля) и  FALSE = 0, поэтому мы можем смоделировать ANDс помощью умножения ( AND(TRUE,TRUE) =  TRUE как 1 × 1 = 1,  =  как 0 × AND(FALSE,anything)FALSEчто-либо = 0). Поэтому мы меняем вышесказанное на

=MAX(IF((A2=$A:$A)*(D2=$C:$C), $B:$B))

(вводится как формула массива, с Ctrl+ Shift+ Enter, конечно):

Обратите внимание, что это работает с неуникальными CreatedDates. Это работает даже если CreatedDates не находятся в том же порядке, что и ID, как в примере выше (где данные сортируются по Columns  Aи  B).

Связанный контент