☰

그룹의 각 행에 대해 다른 열의 열 채우기

tag-icon • tag-icon microsoft-excel
그룹의 각 행에 대해 다른 열의 열 채우기

저는 데이터 마이그레이션 작업을 진행 중이고 100,000개가 넘는 행의 as/sheet를 보유하고 있으므로 수동 업데이트 이상의 솔루션이 필요합니다.

이 게시물을 위해 문제를 단순화하겠습니다. 이름 [텍스트], ID [번호], CreateDate [날짜]와 같은 제목 [데이터 유형]이 있는 세 개의 열(A, B & C)이 있는 as/sheet가 있습니다. ]. 의도적으로 해당 열의 중복 값에 초점을 맞추기 위해 데이터가 Col A를 기준으로 정렬되었습니다. ID는 고유합니다. 날짜는~ 아니다고유한.

이 예에는 A열의 중복된 값을 통해 세 그룹으로 '그룹화'될 수 있는 9개의 행이 있습니다. 따라서 행 2와 3의 경우 Col A의 값은 이고 abc, 행 4, 5, 6의 경우 Col A의 값입니다. 이고 def행 7-10의 경우 입니다 ghi.

첫 번째 작업은 각 '행 그룹'에서 최신 CreateDate를 결정하는 것입니다. 따라서 이 예에서는 행 2와 3의 경우 1999년 5월 11일, 행 4~6의 경우 2001년 3월 12일, 행 7-10의 경우 1999년 5월 11일입니다. {=MAX(IF(A2=$A:$A,$C:$C))} D열의 배열 수식을 사용하여 이 작업을 수행합니다 .

다음 작업은 어려운 것으로 판명되었습니다. 이제 각 행 그룹의 최신 날짜를 결정했으므로 해당 최신 날짜에 해당하는 ID를 열 E(행 그룹의 각 행)에 배치하여 결과가 다음 열 E와 같아지도록 하려고 합니다. 아래 예. 하지만 수동으로 수행하는 것이 아니라 수식/함수를 사용하여 수행해야 합니다. 이것은 마이그레이션 프로젝트를 위한 것이므로 예쁘지 않아도 됩니다.

같은 행의 E열에 일치하는 ID를 넣는 것은 쉽습니다(IF 문). 3, 6, 9행을 참조하세요. 하지만 그것이 해결책이라고 생각하지는 않습니다. 하지만 각 그룹의 다른 행을 동일한 ID로 채우는 방법을 알아낼 수는 없습니다. 첨부된 예에서는 행 2, 4 및 5, 7 및 8, 10이 됩니다. 조회만 할 수는 없습니다. 해당 값은 고유하지 않기 때문에 C열에 있습니다. 다양한 공식을 사용한 헛된 시도 끝에 나는 당황했습니다. 내 생각엔 C열(일치하는 값)과 INDEXand  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

이 솔루션은 오프셋 일치 방법을 사용합니다. 일치 항목을 사용하여 날짜를 찾은 다음 날짜를 기준으로 오프셋을 사용하여 해당 ID를 찾습니다.

=OFFSET(C2,MATCH(D2,C2:C,0)-1,-1)

이것을 E2 셀에 놓은 다음 E 전체에 걸쳐 아래로 드래그합니다.

답변3

B주어진 행(예: 행 2)에 대해 행에서  ID(열)를 찾으려고 합니다. N(즉, Cell  ) 여기서 이름( )은 현재 행( )의 이름과 같고 CreatedDate( )는 현재 행( )의 최신 날짜와 같습니다. 열 BnAnA2CnD2NID가 고유하므로 "그만큼” 행은 최대 행입니다. 논리적인 대답은 Column에 대한 공식을 약간 확장한 것입니다  D.

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

불행히도 AND배열 수식에서는 작동하지 않는 것 같습니다. 그래서 우리는 표준 트릭인 TRUE = 1(또는 0이 아닌 다른 것)과  = 0을 사용하므로 곱셈으로 FALSE 시뮬레이션할 수 있습니다 (  =  1 × 1 = 1,  =  0 × ANDAND(TRUE,TRUE)TRUEAND(FALSE,anything)FALSE아무것 = 0). 그래서 위의 내용을 다음과 같이 변경합니다.

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

( 물론 Ctrl+ Shift+ 를 사용하여 배열 수식으로 입력됨 ):Enter

이는 고유하지 않은 CreatedDates에서 작동합니다. 위의 예(데이터가 열  A및 를  기준으로 정렬되는 경우 B)와 같이 CreatedDates가 ID와 동일한 순서가 아닌 경우에도 작동합니다.

관련 정보