Jim은 직원 이름과 급여 목록이있는 상황을 설명했습니다. 그는 가장 높은 급여를받는 5 명의 직원이 누구인지 확인하려고합니다. 그는 LARGE 함수를 사용하여 5 개의 가장 큰 급여를 식별 한 다음 VLOOKUP을 사용하여 해당 급여에 속하는 이름을 반환하려고합니다. 이것은 상위 5 개의 급여에 중복이 없으면 잘 작동합니다 (사람들은 동일한 급여를받습니다). 있는 경우 VLOOKUP은 해당 급여에서 첫 번째 직원의 이름 만 반환합니다.

모든 고유 이름을 반환하려면 몇 가지 작업을 수행 할 수 있습니다.

한 가지 방법은 공식을 모두 사용하여 우회하는 것입니다. 대신 Excel에서 자동 필터 기능을 사용할 수 있습니다.

  1. 데이터 표에서 아무 셀이나 선택합니다.

  2. 데이터 선택 | 필터 | 자동 필터. Excel은 표의 각 열 머리글 오른쪽에 드롭 다운 화살표를 추가합니다.

  3. 급여 열 맨 위에있는 드롭 다운 목록을 사용하여 Top 10을 선택합니다. Excel에서 Top 10 AutoFilter 대화 상자를 표시합니다. (그림 1 참조)

  4. 중앙 컨트롤을 10에서 5로 조정합니다.

  5. 확인을 클릭하십시오. Excel은 목록에 상위 5 개의 급여를 표시합니다.

이러한 단계를 수행하면 특히 직원 급여에 동률이있는 경우 실제로 5 개 이상의 레코드가 표시 될 수 있습니다. 필터는 상위 5 개 급여를 식별 한 다음 급여와 일치하는 모든 레코드를 표시합니다.

자동 필터를 사용하지 않으려는 경우 다른 옵션은 직원 목록의 각 레코드에 대해 고유 한 것이 있는지 확인하는 것입니다. 예를 들어 직원 이름이 B 열에 있고 급여가 C 열에있는 경우 A 열에 다음 공식을 사용하여 각 레코드를 고유하게 만들 수 있습니다.

=C2+ROW()/100000000

이렇게하면 행 번호를 100,000,000으로 나눈 값이 추가되고 고유 한 값이 만들어집니다. 예를 들어 2 행과 A 열 49에 동일한 급여가 98,765.43 인 경우 다음과 같습니다.

98765.43000002 98765.43000049

큰 숫자 (100,000,000)는 65536 행에 동일한 숫자가 있으면 다음과 같이됩니다.

98765.43065536

그리고이 경우에도 소수점 이하 2 자리로 반올림 한 값은 여전히 ​​실수입니다. LARGE 및 VLOOKUP이 A 열의 “고유하지 않은”값으로 수행되면 목록 내에서 사람의 위치를 ​​기준으로 가장 큰 급여 (및 관련 사람)를 반환합니다.

세 번째 방법은 RANK 및 COUNTIF 함수를 사용하여 급여 목록의 각 값에 대해 고유 한 “순위”를 반환하는 것입니다. 급여 범위가 B1 : B50이면 C1 셀에 다음을 입력하고 범위 아래로 복사합니다.

=RANK(B1,$B$1:$B$50)+COUNTIF($B$1:B1,B1)-1

이제 순위 값에 INDEX를 사용하여 각 급여와 관련된 이름을 반환 할 수 있습니다.

마지막으로, 네 번째 방법은 원하는 정보를 반환 할 수있는 매크로를 만드는 것입니다. 매크로를 구현하는 방법에는 여러 가지가 있습니다. 다음은 그 중 하나 일뿐입니다.

Function VLIndex(vValue, rngAll As Range, _   iCol As Integer, lIndex As Long)

Dim x As Long     Dim lCount As Long     Dim vArray() As Variant     Dim rng As Range     On Error GoTo errhandler

Set rng = Intersect(rngAll, rngAll.Columns(1))

ReDim vArray(1 To rng.Rows.Count)

lCount = 0     For x = 1 To rng.Rows.Count         If rng.Cells(x).Value = vValue Then             lCount = lCount + 1             vArray(lCount) = rng.Cells(x).Offset(0, iCol).Value         End If     Next x

ReDim Preserve vArray(1 To lCount)

If lCount = 0 Then         VLIndex = CVErr(xlErrNA)

ElseIf lIndex > lCount Then         VLIndex = CVErr(xlErrNum)

Else         VLIndex = vArray(lIndex)

End If errhandler:

If Err.Number <> 0 Then VLIndex = CVErr(xlErrValue)

End Function

이 사용자 정의 함수에 전달 된 매개 변수는 값, 조회 할 셀 범위, 조회를 위해이 범위의 “오프셋”(오른쪽 열 수는 양수, 왼쪽 열 수는 음수) 및 중복 수입니다 (1은 첫 번째 값, 2는 두 번째 값 등).

예를 들어이를 사용하려면 A1 : B1에 열 머리글이 포함되고 A2 : A100에 급여가 포함되고 B2 : B100에 직원 이름이 포함되어 있다고 가정합니다.

E2 셀에 다음을 입력하여 테이블에서 가장 큰 급여를 결정할 수 있습니다.

=LARGE($A$2:$A$100,ROW()-1)

F2 셀에 다음 수식을 입력하여 행에 중복 항목이 있는지 확인하고 해당 중복 항목의 현재 “값”을 추적 할 수 있습니다.

=IF(E2=E1,1+F1,1)

G2 셀에서 사용자 정의 함수를 호출하는 다음 공식을 사용할 수 있습니다.

=VLIndex(E2,$A$2:$A$100,1,F2)

E2 : G2 셀을 E3 : G6에 복사하면 급여가 가장 큰 직원의 이름 (G 열)이 나타납니다.

_ 참고 : _

이 페이지 (또는 ExcelTips 사이트의 다른 페이지)에 설명 된 매크로를 사용하는 방법을 알고 싶다면 유용한 정보가 포함 된 특별 페이지를 준비했습니다.

link : / excelribbon-ExcelTipsMacros [새 브라우저 탭에서 특별 페이지를 열려면 여기를 클릭하세요].

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (3077)은 Microsoft Excel 97, 2000, 2002 및 2003에 적용됩니다.