Excel – 열 또는 행의 마지막 값 찾기
이 튜토리얼에서는 Excel에서 열 또는 행의 마지막 값을 조회하는 방법에 대해 설명합니다.
열의 마지막 값
LOOKUP 함수를 사용하여 열에서 비어 있지 않은 마지막 셀을 찾을 수 있습니다.
=LOOKUP(2,1/(B:B<>""),B:B)
이 수식을 자세히 살펴보겠습니다.
수식의 B:B<>”” 부분은 True 및 False 값을 포함하는 배열을 반환합니다: {FALSE, TRUE, TRUE,…}로, B 열의 각 셀이 비어 있는지(FALSE)를 테스트합니다.
=LOOKUP(2,1/({FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;...),B:B)이 불리언 값은 0 또는 1로 변환되며 1에서 나누는 값으로 사용됩니다.
=LOOKUP(2,{#DIV/0!;1;1;1;1;1;#DIV/0!;,B:B)LOOKUP 함수의 lookup_vector입니다. 이 경우 lookup_value는 2이지만 lookup_vector에서 가장 큰 값은 1이므로 LOOKUP 함수는 배열의 마지막 1과 일치하고 result_vector에 해당 값을 반환합니다.
열에 숫자 값만 있고 데이터가 1행부터 시작하며 데이터 범위가 연속인 경우 INDEX 함수와 COUNT 함수를 사용하여 더 간단한 수식을 사용할 수 있습니다.
=INDEX(B:B,COUNT(B:B))
COUNT 함수는 연속 범위(4)의 데이터로 채워진 셀의 수를 반환하고 INDEX 함수는 해당 행(4번째)에 있는 셀의 값을 제공합니다.
데이터 범위에 숫자와 숫자가 아닌 값이 혼합되어 있거나 빈 셀이 있는 경우 발생할 수 있는 오류를 방지하려면 LOOKUP 함수를 ISBLANK 및 NOT 함수와 함께 사용할 수 있습니다.
=LOOKUP(2,1/(NOT(ISBLANK(B:B))),B:B)
ISBLANK 함수는 1과 0에 해당하는 True 및 False 값이 포함된 배열을 반환합니다. NOT 함수는 참(1)을 거짓(0)으로, 거짓(0)을 참( 1)으로 변경합니다. 이 결과 배열을 반전시키면(1을 이 배열로 나누면) 다시 #DIV/0! 오류와 1이 포함된 결과 배열을 얻을 수 있으며, 이 배열은 LOOKUP 함수에서 조회 배열(lookup_vector)로 사용할 수 있습니다. 결론적으로 LOOKUP 함수는 첫 번째 예제에서와 동일하게 조회 배열(lookup_vector)의 값 중 1과 일치하는 마지막 위치에 있는 결과 벡터의 값을 반환합니다.
마지막 항목이 있는 행 번호를 반환하고 싶을 경우, 첫 번째 예제에서 사용한 수식에서 결과 벡터(result_vector)를 ROW 함수로 대체하면 됩니다.
=LOOKUP(2,1/(B:B<>""),ROW(B:B))
행의 마지막 값
숫자 데이터로 채워진 행에서 비어 있지 않은 마지막 셀의 값을 가져오려면 비슷한 접근 방식을 사용하되 다른 함수를 사용할 수 있습니다: OFFSET 함수와 MATCH 및 MAX 함수를 함께 사용할 수 있습니다.
=OFFSET(Reference, Rows, Columns)=OFFSET(B2,0,MATCH(MAX(B2:XFD2)+1,B2:XFD2,1)-1)
이 수식이 어떻게 작동하는지 좀 더 자세히 살펴보겠습니다.
MATCH 함수
MATCH 함수를 사용하여 B2부터 시작하여 2행에 있는 모든 값의 최대값 + 1 미만인 셀 값의 개수를 카운트합니다.
=MATCH(lookup_value, lookup_array, [match_type])=MATCH(MAX(B2:XFD2)+1,B2:XFD2,1)
MATCH 함수의 lookup_value는 2행에 있는 모든 값의 최대값+1 입니다. 이 값은 2행에 존재하지 않고 match_type이 1(lookup_value보다 작거나 같음)로 설정되어 있으므로 MATCH 함수는 배열에서 마지막으로 “확인된” 셀의 위치, 즉 B2:XFD2 범위의 데이터로 채워진 셀 수를 반환합니다(최신 버전의 Excel에서 XFD는 맨 마지막 열입니다).
OFFSET 함수
그런 다음 OFFSET 함수를 사용하여 MATCH 함수가 반환한 위치의 셀 값을 가져옵니다.
=OFFSET(B2,0,C4-1)
