요약: 이 자습서에서는 Excel 수식을 사용하여 목록에서 항목의 마지막 항목을 찾는 방법을 배웁니다.
최근에는 회의 안건을 작성하고 있었습니다.
나는 사람들의 목록과 그들이 "회장을 회의"한 날짜가 있는 Excel 목록을 가지고 있습니다.
나는 또한 목록의 중복으로 인해 한 사람이 마지막으로 "회의 의장"이 된 시간을 알아야 합니다(즉, 한 사람이 회의 의장을 여러 번 담당했음을 의미함).
가장 최근의 위원장이 재배정되지 않도록 해야 하기 때문입니다.
그래서 Excel 함수 마술을 사용하여 작업을 수행하기로 결정했습니다.
아래는 최종 결과입니다. 드롭다운에서 이름을 선택할 수 있으며 목록에서 해당 이름이 마지막으로 발생한 날짜를 알려줍니다.
엑셀 함수에 대해 잘 알고 계시다면, 이를 할 수 있는 엑셀 함수가 없다는 것을 아실 것입니다.여기서 우리는 기적을 만듭니다.
이 자습서에서는 이 작업을 수행하는 세 가지 방법을 보여 드리겠습니다.
내용
마지막 항목 찾기 - MAX 함수 사용
다음은 목록의 마지막 값을 반환하는 Excel 수식입니다.
=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
이 공식이 작동하는 방식은 다음과 같습니다.
- MAX 함수는 마지막으로 일치하는 이름의 줄 번호를 찾는 데 사용됩니다.예를 들어 이름이 Glen이면 11행에 있으므로 11을 반환합니다.목록은 두 번째 행에서 시작하므로 1을 뺍니다.따라서 Glen의 마지막 발생은 목록에서 10입니다.
- SUMPRODUCT는 SUMPRODUCT가 배열 수식을 처리할 수 있기 때문에 Control + Shift + Enter를 사용할 필요가 없도록 하는 데 사용됩니다.
- INDEX 함수는 이제 마지막으로 일치하는 이름의 날짜를 찾는 데 사용됩니다.
마지막 항목 찾기 - LOOKUP 함수 사용
다음은 동일한 작업을 수행하는 또 다른 공식입니다.
=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
이 공식이 작동하는 방식은 다음과 같습니다.
- 조회 값은 2입니다(이유를 알 수 있습니다. 계속 읽으십시오)
- 조회 범위는 1/($A$2:$A$14=$D$3)입니다. 일치하는 이름을 찾으면 1을 반환하고 그렇지 않으면 오류를 반환합니다.따라서 배열로 끝납니다.例如，查找值為 Glen，數組將為 {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}。
- 세 번째 매개변수([result_vector])는 결과를 제공하는 범위(이 경우 날짜)입니다.
이 공식이 작동하는 이유는 LOOKUP 함수가 근사 일치 기술을 사용하기 때문입니다.즉, 정확히 일치하는 항목을 찾을 수 있으면 반환하지만 찾을 수 없으면 끝까지 전체 배열을 스캔하고 조회 값보다 작은 다음으로 가장 큰 값을 반환합니다.
이 경우 조회 값은 2이고 배열에서는 1 또는 오류가 발생합니다.따라서 전체 배열을 스캔하고 이름에 대한 마지막 일치 값인 마지막 1의 위치를 반환합니다.
마지막 항목 찾기 - 사용자 정의 함수(VBA) 사용
다른 방법도 보여드리겠습니다.
VBA를 사용하여 사용자 정의 함수(사용자 정의 함수라고도 함)를 만들 수 있습니다.
사용자 정의 함수를 만드는 이점은 사용이 간편하다는 것입니다.대부분의 작업이 VBA 백엔드에서 발생하기 때문에 매번 복잡한 수식을 만드는 것에 대해 걱정할 필요가 없습니다.
VLOOKUP 수식과 매우 유사한 간단한 수식을 만들었습니다.
사용자 정의 함수를 생성하려면 VB 편집기에 VBA 코드가 필요합니다.나중에 코드와 VB 편집기로 가져오는 단계를 알려 드리겠습니다. 하지만 먼저 작동 방식을 보여드리겠습니다.
다음은 결과를 제공하는 공식입니다.
=마지막 항목 조회($D$3,$A$2:$B$14,2)
수식은 세 가지 매개변수를 사용합니다.
- 값 찾기(D3 셀의 이름이 됨)
- 범위 찾기(이름과 날짜가 포함된 범위 – A2:B14)
- 열 번호(결과를 원하는 열입니다)
수식을 만들고 코드를 VB 편집기에 넣으면 다른 일반 Excel 워크시트 함수처럼 사용할 수 있습니다.
수식에 대한 코드는 다음과 같습니다.
함수 LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Columns(1).Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells(i, 1) then LastItemLookup = LookupRange.Cells(i, ColumnNumber) 종료 함수 End If Next i End 함수
이 코드를 VB 편집기에 넣는 단계는 다음과 같습니다.
- 개발자 탭으로 이동합니다.
- Visual Basic 옵션을 클릭합니다.그러면 백엔드에서 VB 편집기가 열립니다.
- VB 편집기의 프로젝트 탐색기 창에서 코드를 삽입할 통합 문서의 개체를 마우스 오른쪽 단추로 클릭합니다.프로젝트 탐색기가 표시되지 않으면 보기 탭으로 이동하여 프로젝트 탐색기를 클릭합니다.
- 삽입으로 이동하여 모듈을 클릭하십시오.그러면 통합 문서에 대한 모듈 개체가 삽입됩니다.
- 코드를 복사하여 모듈 창에 붙여넣습니다.
이제 통합 문서의 모든 시트에서 수식을 사용할 수 있습니다.
매크로가 포함되어 있으므로 통합 문서를 .XLSM 형식으로 저장해야 합니다.또한 사용하는 모든 통합 문서에서 이 수식을 사용하려면 개인 매크로 통합 문서에 저장하거나 추가 기능을 만들 수 있습니다.
