다른 열의 일치 항목과 세 번째 열의 값이 처음으로 구별되는 항목을 기준으로 한 열의 값 합계

Question

다른 열의 값 일치를 기준으로 한 열의 값을 합산하는 수식 기반 솔루션을 찾으려고 노력 중입니다. 단, 세 번째 열의 고유한 값 발생에 대해서만 가능합니다.

다음은 단순화된 샘플 스프레드시트입니다.

F열(A,B,C)의 문자를 "Letters" 열 B와 일치시킨 다음 "Values" 열 D의 값을 "Numbers" 열 C의 고유 숫자별로 한 번씩 합산하여 다음과 같이 표시해야 합니다. 열 G "값의 합계".

G열의 셀에 올바른 합계가 표시되지만 이를 수행할 수 있는 수식이 없습니다. 어떤 도움이라도 주시면 감사하겠습니다!

Answer 1

이런 종류의 문제는 배열의 관점에서 생각하는 것이 도움이 됩니다.

값(열 D)에서 문자(열 B)가 "A"이고 숫자에서 중복 항목이 제거된 숫자의 배열(목록)을 얻을 수 있는 경우 배열을 합산하여 답을 얻을 수 있습니다.

이 표현은:

(B$2:B$12=F2)

열 B = "A" True/False인 값 배열을 제공합니다 . True이 하나:

(C$2:C$12<>C$3:C$13)

C 열의 셀이 다음 셀과 같지 않은 True/False값 배열을 제공합니다 . True중복 항목이 순차적 셀에 있기 때문에(항상 그렇지 않은 경우 아래 설명) 이 배열에는 False값이 추가로 나타나는 위치가 있으며 기본적으로 중복 항목을 필터링합니다. 이 두 배열을 함께 곱하면 다음과 같습니다.

(B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)

값을 1과 0으로 변환 True/False하고 합계에 포함하려는 위치에 1이 있는 배열을 제공합니다. 이 배열을 logical_test에서 로 사용 IF()하고 D 열을 다음으로 사용합니다 value_if_true.

IF((B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)>0,D$2:D$12)

1이 있는 곳마다 D열의 값 배열을 반환하고, False0이 있는 곳에 산재되어 있습니다. 이제 배열을 합산하면 됩니다. G2에서 채워진 이 공식은 아래와 같은 결과를 제공합니다.

=SUM(IF((B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)>0,D$2:D$12))

이는 배열 수식이므로 로 입력해야 합니다 CTRLShiftEnter.

Answer 2

데이터에 열을 추가할 수 있는 경우 주어진 간단한 예에서는 다음이 작동합니다.

1. 문자와 숫자를 연결하는 수식을 추가합니다. 이 수식에는 A열을 사용했습니다. 즉, Cell A2: =B2&C2입니다.
2. 이 수식을 아래로 끌어 A2:A12 셀에 적용하세요.
3. 문자와 숫자의 각 연결이 첫 번째 고유한 연결인지 테스트하는 수식을 추가합니다. 이 수식에는 E열을 사용했습니다. 즉, Cell E2: =COUNTIF(A$2:A2,A2)
4. 이 수식을 아래로 끌어 E2:E12 셀에 적용하세요.
5. H2에서 다음 공식을 사용하여 문자가 일치하는 값을 합산하지만 문자와 숫자의 첫 번째 연결(예: 셀 G2)에만 해당됩니다. =SUMIFS($D$2:$D$12,$B$2:$B$12,F2 ,$E$2:$E$12,1)
6. 이 수식을 아래로 끌어서 G2:G4 셀에 적용하세요.