엑셀 마지막 행 값 가져오기를 함수를 사용하는 방법과 매크로 VBA를 사용한 방법에 대해서 알아보겠습니다.
엑셀 마지막 행 값 가져오기 함수1
처음 소개할 함수는 INDEX , COUNTA 함수를 사용하는 방법입니다.
INDEX함수로 불러올 범위를 지정합니다.
COUNTA함수로 비워있지 않는 셀의 개수를 구합니다.
COUNTA(A2:A10)은 9를 반환합니다. A부터 I 까지 셀이 다 채워져 있기 때문입니다.
즉, A2:A10 범위에서 9번째 행의 값을 가져오는게 됩니다.
위 방법은 모든 셀 안에 값이 있고, 범위를 지정한 다음 마지막 셀의 값을 찾을때 유용하게 쓰이는 방법입니다.
위 이미지처럼 만약 불러올 범위안에 빈셀이 있으면 마지막셀을 못 불러오게 되는점 참고 바랍니다.
E와 G사이에 비워져 있기 때문에 A2:A10에서 8번째 값을 불러오게 됩니다.
엑셀 마지막 행 값 가져오기 함수2
두번째 방법은 LOOKUP 함수를 사용하여 범위내의 마지막 값을 반환하는 방법입니다.
불러올 범위가 비워있던 채워져 있던 범위내 마지막 셀의 값을 불러오는 방법입니다.
LOOKUP의 논리는 조금 복잡 할 수 있습니다.
먼저 LOOKUP 함수를 이해하려면 TRUE FALSE 함수의 숫자 계산방법을 알고 있어야합니다.
TRUE는 1을 반환하고 FALSE는 0을 반환합니다.
VLOOKUP 함수나 MATCH함수에서도 정확히 일치하는 값을 0을 넣어서 함수를 완성 시킬 수 도 있지만, FALSE 값을 넣어서 정확히 일치하는 값을 반환 할 수 있습니다.
1/(A12:A20<>"") 는 A12:A20범위내에 "" 빈칸이 <> 아닌 값을 배열로 표시한 다음 1로 나누어 줍니다.
A12:A20 범위에는 빈칸이 없으므로 다 TRUE 값을 반환 할 것이고, 즉 {1;1;1;1;1;1;1;}의 행 배열 값이 나오게 됩니다. 만약 빈칸이 있으면1 나누기 0을 하여 #DIV/0! 나누기 오류를 반환 하게 됩니다.
여기서 중요한 것은 셀이 비어있지 않으면 1 비어있으면 #DIV/0! 나누기 오류가 반환됩니다.
LOOKUP 함수의 LOOKUP_VALUE가 2인 이유는 나누기 오류가 나오던 1이 나오던 항상 1보다는 크기때문입니다. 다시 말해 일치하는 값은 찾을 수가 없습니다. 2를 {1;1;1;1;#DIV/0!;1;1} 배열에서 찾으려고 하는것 부터가 안되기 때문입니다. 즉, LOOKUP 함수의 마지막 TRUE가 반환된 위치, 다시말해 비어있지 않은 마지막 셀의 값을 반환하는 것 입니다.
충분한 설명이 되었나 모르겠네요.
LOOKUP 함수는 일치하는 값은 애초부터 없었습니다.
즉, 비어있지 않은 (TRUE)값을 찾을건데, 비어있지 않은 아지막 셀의 값을 반환하는 것 입니다.
2를 나누는 이유는 일치하는 값이 애초부터 없게 하기 위함입니다.
만약, 1을 나누게 되면 여러개의 빈셀중 마지막 빈셀의 위의 값을 가져오게 됩니다.
엑셀 마지막 행 값 가져오기 함수3
세번째 방법은 OFFSET 함수와 COUNTA 함수를 사용하여 특정 열에서 비어있지 않은 셀의 수를 계산 해서 OFFSET함수로 범위내 마지막 셀의 값을 찾아서 반환합니다.
OFFSET은 동적 배열 함수로서 원하는 위치가 참조를 통해 바뀌어서 원하는 배열 만큼 반환하는 함수로서 엑셀 고수분들이 많이 사용하는 함수입니다.
OFFSET 첫번째 인수(reference)를 F1 을 잡은 이유는
F1부터 시작해서 맨 마지막 행의 값을 찾아야 하기 때문입니다.
두번째 인수(rows) Counta함수로 비어있지 않는 셀의 개수를 구합니다.
즉, 위 표에서는 A부터 J인 숫자 10을 반환합니다.
F1에서 아래로 10-1= 9개를 내려오면 J가 반환되는 것 입니다.
COUNTA를 쓰면 항상 주의 사항이 있습니다.
항상 빈칸이 채워져 있어야하며, 빈칸이 있을시엔 rows 인수에 제대로된 숫자가들어가지 않습니다.
=OFFSET(F1,9,0) = =OFFSET(F1,COUNTA(F:F)-1,0) 가 되는 것입니다.
세번째 인수(cols)는 0을 넣어서 해당하는 F열의 위치를 고정하기 위함입니다.
만약 마지막 행 값을 찾으려고하는데 맨 위의 셀이 비워져있으면
위 이미지와 같이 J값을 찾아오는게 아니라 I 값을 찾아옵니다.
이런 경우에는 -1를 지우면 됩니다.
=OFFSET(F1,COUNTA(F:F),0)
이런식으로 말이죠.
1행에 값이 있을경우 Counta(범위) -1
2행에 값이 있을경우 Counta(범위)
3행에 값이 있을경우 Counta(범위)+1
...
이런식으로 진행되면 됩니다.
중요! 항상 열에는 모든셀에 값이 있어야합니다.
정리하면
=INDEX(A2:A10,COUNTA(A2:A10)) (빈셀이 있으면 안됨)
=LOOKUP(2,1/(A12:A20<>""),A12:A20) (빈셀이 있어도 됨)
=OFFSET(F1,COUNTA(F:F)-1,0) (빈셀이 있으면 안됨)
엑셀 마지막 행 값 가져오기 vba 매크로 활용
매크로를 사용하려면 .xlsm 혹은 .xlsb 으로 확장자를 변경하셔야합니다.
사용된 코드는 아래와 같습니다.
Sub 마지막행값반환()
Dim lastrow As Long '마지막 행 변수설정
Dim Asht As Worksheet ' 워크시트를 저장하는 변수설정
Set Asht = ActiveSheet ' 활성화된 워크시트를 설정
lastrow = Asht.Cells(Asht.Rows.Count, "A").End(xlUp).Row ' A열에 마지막 데이터가 있는 행을 찾기
Asht.Range("B2").Value = Asht.Cells(lastrow, "A").Value ' 마지막 행의 값을 B2셀에 붙여넣기
End Sub
비밀번호는 댓글 주시면 알려드리겠습니다.
※알고있으면 유용한 엑셀 기능 ※
'엑셀공부 > VBA 하루에 하나씩' 카테고리의 다른 글
| 엑셀 vba for 반복문 vba 공부하기 #1 (0) | 2023.12.06 |
|---|---|
| 엑셀 다른 값 끼리 셀 색상 구분하기 VBA 매크로 활용 (0) | 2023.09.14 |
| 엑셀 매크로 차단 해제 매크로 세팅 방법 EXCEL VBA (0) | 2022.10.09 |
