엑셀 데이터가 있는 마지막 셀 위치를 찾는 수식을 알아보겠습니다. 방대한 데이터의 마지막 위치를 찾을 때 중간에 빈 셀이 있거나 입력된 데이터의 형식이 다양하면 마지막 데이터의 위치를 찾는 수식을 작성하기 쉽지 않습니다. 오늘은 데이터의 범위, 빈 셀의 유무와 상관없이 마지막 데이터의 위치를 찾는 수식을 알아보겠습니다.
1. 데이터 구조 파악
엑셀에 입력된 데이터의 마지막 위치를 찾기 위해서 데이터의 구조를 파악해야 합니다. 우선 수식을 작성하기 전에 마지막 위치를 찾기 위한 데이터의 분석이 필요합니다. 오늘 수식을 작성하기 위해 먼저 파악해야 할 점을 나열해보겠습니다.
① 데이터 중간의 빈 셀의 유무
② 입력된 데이터 형식 – 문자, 숫자, 특수 문자
③ 데이터의 범위
위에 내용을 파악하는 이유는 데이터 상황에 맞춰 사용할 수 있는 함수가 다르기 때문입니다. 빈 셀이 없는 일관성 있는 데이터는 COUNTA(데이터 범위) 형식으로 수식을 작성하면 마지막 데이터 위치를 확인할 수 있습니다. 빈 셀이 존재하지 않기 때문에 카운팅이 된 셀이 마지막 데이터의 위치가 되기 때문입니다. 오늘 작성하게 될 수식은 위에 사항이 모두 포함되더라도 작동할 수 있는 수식이니 천천히 따라 오시기 바랍니다.
2. 데이터가 있는 마지막 행 위치
오늘 작성하게 될 수식은 MAX, IF, ROW, COLUMN 함수를 사용하겠습니다. 사용하게 되는 함수에 대해서 간단히 알아보겠습니다.
① ROW 함수 : 지정한 셀의 행 위치를 반환하는 함수입니다. 예를 들어 ROW(A2)를 입력하게 되면 행 값인 2를 반환하게 됩니다.
② IF 함수 : 조건을 입력하고 조건이 참, 거짓일 때 동작을 지정하고 실행합니다.
③ MAX 함수 : 주어진 값에서 가장 큰 값을 호출합니다.
④ COLUMN 함수 : 지정한 셀의 열 위치를 반환하는 함수입니다.
= MAX( IF ( A:A<>””,ROW (A:A),0))
수식 동작에 대해서 설명하겠습니다. 데이터가 입력된 범위는 A:A에 있고 데이터가 입력된 마지막 행 번호를 호출하는 수식입니다. IF 함수의 조건에 A:A<>””를 입력합니다. A:A 영역이 “”(빈 셀) 이 아닐 경우에 해당하는 조건이며 A:A 영역 전체에 조건에 맞는 셀을 검색합니다. A:A 영역 안에 조건에 해당하는 셀은 ROW(A:A)라는 수식이 동작하여 행 번호를 호출하게 되고 조건이 맞지 않으면 0을 호출합니다. IF 함수의 결괏값으로 호출된 값 중에서 MAX 함수를 통해 가장 큰 값 (마지막 행 위치)을 호출합니다. 단 여기서 호출된 값은 INDEX, MATCH 함수와 다르게 상대적인 값이 아닙니다. ROW 함수를 이용했기 때문에 셀 고유의 행 위치를 호출합니다.
3. 데이터가 있는 마지막 열 위치
위에서 작성한 수식에서 행 위치를 반환하는 ROW 함수 대신 열의 위치를 반환하는 COLUMN 함수를 이용하여 데이터가 입력된 마지막 열 위치를 파악하는 수식을 작성하겠습니다.
= MAX ( IF ( 1:1<>””,COLUMN (1:1) ,0))
수식의 동작은 데이터가 있는 마지막 행 위치에서 알아본 수식과 동작이 거의 동일합니다. 열의 위치를 알기 위한 COLUMN 함수를 사용하였으며 데이터의 영역은 1:1로 변경되었습니다. 1:1에 입력된 데이터에서 마지막으로 입력된 열의 번호를 호출하게 됩니다. 호출되는 값은 상대적인 위칫값이 아닌 셀 고유의 열 위치를 호출합니다.
마치며
오늘은 엑셀에서 데이터가 있는 마지막 셀 위치 확인하는 수식을 작성하였습니다. 이번 포스팅을 간단히 정리하겠습니다.
① 입력된 데이터 형식에 따라 수식과 함수를 다르게 사용해야 한다.
– 빈 셀이 없으면 COUNTA(데이터 범위)를 사용하여 마지막 위치 파악
② 입력된 데이터 중간에 빈 셀이 있어도 마지막 데이터 위치를 파악할 수 있다.
– MAX 함수를 이용하여 가장 큰 값을 호출
③ 데이터 행, 열 위치에 따라서 사용하게 되는 함수가 다르다.
– ROW 함수, COLUMN 함수, 데이터 범위를 다르게 사용하여 수식 작성