☰

Excel - 값 빼기를 사용하여 동일한 ID 쌍 집계

tag-icon • tag-icon microsoft-excel
Excel - 값 빼기를 사용하여 동일한 ID 쌍 집계

소스 데이터:여기에 이미지 설명을 입력하세요

내가 찾고 있는 결과:여기에 이미지 설명을 입력하세요

수천 번의 발생에 대해 이 작업을 수행하는 수식을 원합니다(항상 쌍으로, 흐름 데이터임). 가장 큰 값에서 가장 작은 값을 빼야 합니다. 더 큰 값을 가진 라인이 남아 있어야 합니다.

답변1

데이터 세트는 행별로 쌍으로 그룹화되므로 이를 활용하여 값을 식별하고 비교합니다. 수식은 데이터의 첫 번째 행이 홀수 또는 짝수 행에 있는지 확인합니다. 모든 쌍을 식별하는 데 사용되는 앞으로 이동합니다.

odd; even, odd; even, odd; even,등.
또는
even; odd, even; odd, even; odd,등.

공식

  1. 수식은 명명된 범위를 사용하여 data수식을 더 쉽게 따르고 유지 관리할 수 있도록 합니다.$A2:$C1000
  2. 한 쌍의 두 구성원이 결코 동일한 값을 가지지 않거나 만약 그렇다면 그들은 관심이 없다는 질문에서 기본 가정이 있습니다. 그렇다면 둘 다 이후 결과에서 필터링됩니다 x-x=0.
=LET(
   rowOffset, IF(ISEVEN(ROW(INDEX(data,1,1))),0,1),
   newValues,
      MAP(INDEX(data,,3), LAMBDA(r,IF(r<>"",
         IF(ISEVEN(ROW(r)+rowOffset),
             IF(r>OFFSET(r,1,0),r-OFFSET(r,1,0),0),
                 IF(r>OFFSET(r,-1,0),r-OFFSET(r,-1,0),0)),0))),
   newData, HSTACK(INDEX(data,,1), INDEX(data,,2), newValues),
   FILTER(newData, INDEX(newData,,3)>0))

노트

Line |  Code
-----+------------------------------------------------
  1  |  =LET(
  2  |   rowOffset, IF(ISEVEN(ROW(INDEX(data,1,1))),0,1),
  3  |   newValues,
  4  |      MAP(INDEX(data,,3), LAMBDA(r,IF(r<>"",
  5  |         IF(ISEVEN(ROW(r)+rowOffset),
  6  |             IF(r>OFFSET(r,1,0),r-OFFSET(r,1,0),0),
  7  |                 IF(r>OFFSET(r,-1,0),r-OFFSET(r,-1,0),0)),0))),
  8  |   newData, HSTACK(INDEX(data,,1), INDEX(data,,2), newValues),
  9  |   FILTER(newData, INDEX(newData,,3)>0))
선 노트
#1 사용허락하다중간 계산을 저장하고 수식에 이름을 정의할 수 있는 기능입니다.
#2 rowOffset같음 0또는1

​람다공식(4행)은 첫 번째 쌍 멤버가 짝수 행에 있다고 가정합니다.
rowOffset필요에 따라 행 번호를 오프셋합니다.
색인data범위 의 행 1 열 1을 식별하는 데 사용됩니다.INDEX(data,1,1)
열인덱싱된 셀의 행 번호를 가져옵니다.ROW(INDEX(data,1,1))
짝수이다행 번호가 짝수이면 TRUE를 반환하고 ISEVEN(ROW(INDEX(data,1,1))
마지막으로만약에짝수인 경우에는 0이거나 홀수인 경우 1입니다.IF(ISEVEN(ROW(INDEX(data,1,1))),0,1)
#삼 newValues결과를 저장하도록 선언람다기능
#4 람다함수는 값의 열을 생성합니다0또는 쌍의 차이.

색인data열 3 에서 이전 값을 가져오는 데 사용됩니다. INDEX(data,,3)
색인이 생성된 열지도로람다기능MAP(INDEX(data,,3),
​람다r매핑된 값을 보유하도록 정의합니다 .MAP(INDEX(data,,3), LAMBDA(r,
만약에문은 나머지 부분을 건너뜁니다.람다매핑된 값이 비어 있는 경우IF(r<>"",
#5 r쌍의 첫 번째 또는 두 번째 구성원인지 확인하십시오.

열rROW(r)첫 번째 멤버가 홀수 행에 있는 경우 의 행 번호가 추가 됩니다.
rowOffsetROW()+rowOffset
짝수이다r의 행 + rowOffset가 짝수 이면 TRUE를 반환합니다.ISEVEN(ROW()+rowOffset)
만약에's value if TRUE= 첫 번째 멤버, & value if false= 두 번째 멤버IF(ISEVEN(ROW()+rowOffset),
#6 첫 번째 구성원이 두 번째 구성원과 가치를 비교합니다.
IF(r>OFFSET(r,1,0),r-OFFSET(r,1,0),0)

만약에r값보다 큰지 테스트합니다.오프셋아래의 1행과 0열의 "참인 경우의 값"은 IF(r>OFFSET(r,1,0),"거짓인 경우의 값" 아래의 값
에서 빼는 것입니다.rr-OFFSET(r,1,0)
0
#7 두 번째 구성원은 첫 번째 구성원과 값을 비교합니다.
IF(r>OFFSET(r,-1,0),r-OFFSET(r,-1,0),0)

만약에r값보다 큰지 테스트합니다.오프셋위의 1개 행 및 0개 열의 "참인 경우 값" IF(r>OFFSET(r,-1,0),은 위의 값
에서 빼는 것입니다. e rr-OFFSET(r,-1,0)
"거짓인 경우의 값"은0
#8 열 newDataID 1, 열 ID2 및newValues

색인data범위 에서 첫 번째 열 ID1을 가져옵니다.INDEX(data,,1)
색인data또한 범위 에서 두 번째 열 ID2를 가져옵니다.INDEX(data,,2)
H스택인덱싱된 열을 newValues3개 열 배열로 결합합니다.newData
HSTACK(INDEX(data,,1), INDEX(data,,2), newValues)
#9 필터newData세 번째 열 값이 0보다 큰 배열
FILTER(newData, INDEX(newData,,3)>0))

관련 정보