☰

상위 3개 값을 선택하고 합산합니다.

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2007 power-query
상위 3개 값을 선택하고 합산합니다.

다음과 같은 데이터가 있다고 가정해 보겠습니다.

PLAYER    |Team      |Points
Smith     |Suns      |25
Jones     |Suns      |15
Martin    |Suns      |23
Chen      |Suns      |3
Williams  |Suns      |17
Quill     |Marvel    |40
Banner    |Marvel    |1
Stark     |Marvel    |1
Odinson   |Marvel    |1
Parker    |Marvel    |3
Curly     |Spurs     |2
Franke    |Spurs     |5
Wayne     |Spurs     |23
Weasley   |Wizards   |21
Potter    |Wizards   |19
Granger   |Wizards   |15
Thompson  |Bobcats   |12
Boehme    |Bobcats   |13

각 팀의 상위 3개 점수를 합산하는 공식을 작성하려면 어떻게 해야 합니까?

예를 들어 다음 데이터를 반환합니다.

Team   |Points
Suns   |65
Marvel |44
Spurs  |30
Wizards|55
Bobcats|25

"팀원의 중간값보다 큰 값이면" 필터를 사용해서 생각해봤는데, 멤버가 3명 미만이면 안 되더라고요.

저도 생각해 보았 sumif으나 같은 이유로 적합하지 않을 것 같습니다.

또한 MIDDLE 3 점수의 합도 찾고 있지만 상위 3개를 찾는 방법에 대한 조언을 받으면 이를 중간 3에 적용하는 방법을 알아낼 수 있을 것이라고 확신합니다.

파워 쿼리가 도움이 될 수 있나요? 저도 잠깐 해봤지만 프로는 아니거든요. (꼭 그래야만 한다면 VBA에서 뭔가를 작성할 수도 있지만 가능하다면 그 경로를 따르지 않는 것이 좋습니다. 왜냐하면 결국 웹에서 공유하기 위해 Google 시트에 넣고 싶기 때문입니다.)

답변1

팀별로 데이터를 정렬하고 점수를 내림차순으로 정렬하세요. 그런 다음 각 팀의 첫 번째 점수를 찾고 다음 세 점수를 합산하거나 더 적은 경우 해당 팀의 점수 수를 합하는 이 공식을 사용합니다.

=SUM(INDEX(C:C,MATCH(F2,B:B,0)):INDEX(C:C,MATCH(F2,B:B,0)+MIN(COUNTIF(B:B,F2)-1,2)))

여기에 이미지 설명을 입력하세요

답변2

이 공식은 각 팀의 상위 3개 점수를 합산합니다.

=SUM(IFERROR(LARGE(IF(B$2:B$19=E2,C$2:C$19),{1,2,3}),""))

배열 수식이므로 CTRLShiftEnter그냥 가 아닌 으로 입력해야 합니다 Enter.

결과는 아래와 같습니다:

여기에 이미지 설명을 입력하세요

작동 방식:IF()E열에 나열된 팀의 점수 목록을 반환합니다. 그런 다음 상위 LARGE()3개 점수만 가져옵니다. IFERROR()점수가 3점 미만인 경우를 처리합니다. 마지막으로 SUM()그것들을 합산합니다.

편집: 중간 3개 점수의 합을 얻으려면 리터럴 배열과 "area_num"을 선택하는 수식을 사용하여 INDEX()의 참조 형식을 사용하려고 했습니다.

=SUM(IFERROR(LARGE(IF(B$2:B$19=E11,C$2:C$19),INDEX(({1,2,3},{2,3,4}),,,INT(COUNTIF(B2:B19,E11)/2))),""))

그러나 배열 상수를 참조로 허용하지 않습니다. 마침내 배열을 지정하기 위해 도우미 열을 사용하여 작동하게 되었습니다.

=SUM(IFERROR(LARGE(IF(B$2:B$19=E11,C$2:C$19),INDEX((H$1:H$3,H$2:H$4),,,INT(COUNTIF(B2:B19,E11)/2))),""))

답변3

내 접근 방식은 상위 3위와 중간 3위 점수의 합을 구하는 데 아주 약간만 다릅니다.

여기에 이미지 설명을 입력하세요

  1. 기본 필드인 Team name의 소스 데이터를 오름차순으로 정렬하고, 보조 필드인 Points의 소스 데이터를 내림차순으로 정렬했습니다. (상위 3개 점수의 합을 수동으로 계산할 수 있도록 하기 위해)
  2. 오름차순 및 내림차순으로 팀 목록을 생성하는 수식을 사용했습니다.
  3. 마지막으로 위 답변 중 하나의 공식을 사용하여 상위 3개 점수와 중간 3개 점수의 합을 계산했습니다.

다음의 공식은 다음과 같습니다.

팀 목록(오름차순):

{=INDEX($D$216:$D$233, MATCH(0, COUNTIF($J$215:J215, $D$216:$D$233), 0))}

팀 목록(내림차순):

  =IFERROR(LOOKUP(2,1/(COUNTIF($G$215:G215,$D$216:$D$233)=0),$D$216:$D$233),"")

오름차순으로 팀의 상위 3개 점수 합계:

{=SUM(IFERROR(LARGE(IF(($D$216:$D$233=J216),$E$216:$E$233),{1,2,3}),0))}

오름차순으로 팀의 중간 3개 점수 합계:

=SUM(IFERROR(LARGE(IF(($D$216:$D$233=J216),$E$216:$E$233),{2,3,4}),0))

이 공식에 대한 설명은 아래 참고 2를 참조하세요.

노트:

  1. J216을 G216으로 대체하여 팀의 상위 및 중간 3점을 내림차순으로 가져옵니다.
  2. 팀의 최대 개수는 5개이므로 중간 3개의 점수를 2,3,4번으로 가정했습니다.이렇게 하면 점수가 2~3개만 있어도 총점에서 가장 높은 점수가 제거됩니다.. 점수가 2~3개밖에 없는 팀이모두점수가 총계에 기여한 다음 위의 답변 중 하나를 고려하십시오.

관련 정보