☰

데이터 예시

tag-icon • tag-icon microsoft-excel worksheet-function
데이터 예시

저는 신생 e스포츠 팀의 조직 시트를 작성 중입니다. 여기서 제 목표는 우리 팀 선수들의 게임 내 평균 순위를 계산하는 것입니다.

셀 F2과 모든다른열의 행에는 F"S3" 또는 "G1"(실버 3 또는 골드 1 순위)과 같은 순위 식별자가 있습니다.

이러한 순위 식별자(I1은 1, I2는 2 등) 간의 숫자 매핑을 포함하는 다른 시트가 있습니다.

따라서 이 범위( )의 다른 모든 행에 대해 식별자를 가져와 시트에 대한 F2:F100a를 통해 숫자 형식으로 변환하고 결과를 수행한 다음 해당 숫자를 역순으로 다시 텍스트 형식으로 변환해야 합니다. .​VLOOKUPLookupsFLOOR(AVERAGE()VLOOKUP

이미 몇 가지 단계를 완료했지만 이와 같은 이전 수식의 결과를 평균하는 방법을 잘 모르겠습니다.

텍스트 버전에서 순위의 숫자 버전을 얻으려면 공식은 다음과 같습니다.

=VLOOKUP(F2, Lookups!A2:B29,2, FALSE)

내가 모르는 것은:

  • 다른 모든 셀을 생략하는 셀 참조를 수행하는 방법
  • 대량의 VLOOKUP 결과를 평균화하는 방법

데이터 예시

메인 시트

기본 스프레드시트

조회 테이블

조회 스프레드시트

두 열의 각 세트는 단일 플레이어를 나타내며 해당 플레이어의 순위는 짝수 열의 F 행에 저장됩니다. 조회 시트에서 S3는 숫자 점수 10을 나타냄을 알 수 있습니다. 다음 플레이어의 G2는 점수 15가 되는 식입니다.

이 숫자의 합을 구한 후 평균을 구하여 팀의 평균 점수를 제공합니다. 조회 테이블로 돌아가면 이전 작업과 반대로 수행되어 시트에 나열된 모든 플레이어의 평균 점수에 해당하는 단일 순위를 제공합니다.

현재 시트에 있는 세 명의 플레이어를 고려하면 S3, G2, NA를 선택하겠습니다. 이것은 나에게 10, 15, 10의 점수를 줍니다. 이들의 평균은 11(내림)이며 조회 테이블을 사용하여 다시 S2 순위에 해당합니다. 이 가상 수식을 포함하는 셀에는 "S2"가 표시됩니다.

답변1

큐:F3, F5, F7 등은 공백으로 유지됩니까?
답: 그럴 것입니다. 아무 것도 입력하면 안 된다는 것을 보여주기 위해 해당 필드를 회색으로 표시했습니다.

가장 쉬운 방법은 이러한 빈 셀을 사용하여 개별 순위에 대한 숫자 값을 보관하는 것입니다. 그런 다음 F2:F101의 평균을 계산하면 실제 숫자만 고려됩니다. 셀 번호 형식을 사용하면 숫자가 표시되지 않도록 숨길 수 있습니다 ;;;. 이렇게 하면 원시 값을 유지하면서 수식에서 입력되거나 반환된 모든 항목에 대해 빈 셀이 표시됩니다.

50번 복사하여 붙여넣기를 원하지 않으면 첫 번째 코드 줄에서 기본 워크시트의 이름을 조정한 후 이 짧은 하위 프로시저를 실행하세요.

Option Explicit

Sub PopulateLookups()

    With Worksheets("sheet1")
        With .Range(.Cells(2, "F"), .Cells(.Rows.Count, "F").End(xlUp))
            With .SpecialCells(xlCellTypeConstants, xlTextValues)  '<~~ see footnote
                With .Offset(1, 0)

                    .FormulaR1C1 = "=vlookup(r[-1]c, lookups!r2c1:r29c2, 2, false)"
                    .NumberFormat = ";;;"

                End With
            End With
        End With
    End With

End Sub

이제 다음 중 하나를 사용하여 조회 값의 평균을 얻을 수 있습니다.

=average(F2:F100)
=average(F:F)

빈 셀의 용도를 변경하고 싶지 않다면 '도우미 열'이 실행 가능한 대안입니다. 도우미 열 방법에 대해 공백 방법을 테스트했는데 결과는 동일했습니다.

¹ 기존 순위가 입력되지 않고 수식으로 반환되는 경우xlCellType상수로 교체해야합니다xlCellType공식.

답변2

결과를 찾을 수 없을 때 빈 텍스트를 반환하는 iferror 수식 안에 vlookup을 배치하여 다른 모든 행에 값이 필요한 첫 번째 조회를 해결하겠습니다.

=IFERROR(VLOOKUP(F2,Lookups!A2:B29,2,0),"")

다음으로 소수점 이하 자릿수를 자르기 위해 반올림 공식 내에서 평균 공식을 사용할 수 있습니다. 평균 수식은 빈 행을 무시합니다.

마지막으로 vlookup 공식은 항상 왼쪽에서 시작하여 오른쪽으로 이동하기 때문에 이 평균에서 "순위"를 가져오는 데 작동하지 않습니다. 여기서는 오른쪽을 보고 왼쪽으로 이동해야 합니다. 이를 위해 Index/Match 콤보를 사용합니다.

=INDEX(Lookups!A2:B29,MATCH(E11,Lookups!B2:B29,1),1)

일치하는 부분에 대해서는 정확히 일치하지 않을 수도 있다는 가정하에 넘어가지 않고 가장 가까운 값을 찾을 수 있도록 "1" 매개변수를 사용했습니다. 자세한 정보와 옵션은 Excel 내부의 색인 및 일치 수식 설명을 확인하세요.

나는 작성된 수식을 보여주는 샘플 시트에 이것을 만들었습니다. 도움이 되었기를 바랍니다:공식 예가 표시된 샘플 솔루션

답변3

짝수 행의 평균을 얻으려면 다음 공식을 사용하십시오.

=IF(ROW()/2 = ROUND(ROW()/2,0),VLOOKUP(A1,H$1:I$5,2,FALSE),"")

그렇지 않으면 다음을 사용하십시오.

=IF(ROW()/2 <> ROUND(ROW()/2,0),VLOOKUP(A1,H$1:I$5,2,FALSE),"")

=AVERAGE(G1:G5)그런 다음 평균을 구하는 데 사용합니다.

관련 정보