Loading

블로그채널

Search !

엑셀 행 열 끝 셀 번호 찾기


엑셀 행 열 끝 셀 번호 찾기


엑셀 서식을 만든 후에 가끔 계산이 잘못되거나 원치 않은 결과가 나올 때가 있습니다. 예를 들어 SUM 함수를 사용해서 합계를 만들 경우, 합계가 맞지 않게 되는데요.

수식이 뭐가 틀렸는지 찾아보다가 SUM 함수에서 지정한 영역 밖에 있는 데이타가 합계처리가 안되어 계산 착오가 일어나게 됩니다. 




먼저 엑셀 행과 열 개념부터 살펴볼까요? 엑셀에서 행은 가로인 ROW이고, 열은 세로인 COLUMNS입니다. (행렬 = 가로세로)


영어사전에는 ROW가 열, 줄로 표기되어 있어서, ROW가 열이 아닌가 하는 착각을 하는데요. 가로로 늘어선 줄이라고 생각하시면 되겠습니다. 

반면, 열은 세로를 말하고 COLUMNS은 기둥을 뜻하므로, 외우기 쉽겠죠? 


SUM 함수를 사용하다보면, 영역지정을 할 때 엑셀 끝 행 번호를 몰라 A1000, 이렇게 잡아두곤 합니다. 그런데 생각치도 못하게 데이터가 더 길어져 A1001에 데이타가 들어가면 계산이 맞지 않습니다. 


차라리 엑셀 행 끝 번호나, 엑셀 열 끝 번호를 넣어둔다면, 나중에 계산이 틀려서 고생하는 일은 없게됩니다. 


엑셀 행 셀 끝 번호: XFD1

엑셀 열 셀 끝 번호: A1048576


=SUM(A1:A1048576) 


위와 같이 SUM 함수 수식을 만들면 첫번째 열 전체를 더하는 수식이 됩니다. 


=SUM(A1:XFD1)  


위와 같이 SUM 함수 수식을 만들면 첫번째 행 전체를 더하는 수식이 되지요. 


사실 저 수식을 쓰는 것보다 더 좋은 수식을 알려드릴게요. 


=SUM(A1:A1048576) 똑같은 수식은 =SUM(A:A) 


=SUM(A1:XFD1) 똑같은 수식은 =SUM(1:1) 


이렇게 하면 엑셀 행 끝 번호나, 엑셀 열 끝 번호를 몰라도 되니 편하게 쓸 수 있습니다. 



만약 공사현장에서 업체별 몇 공수(인원수)나 투입되었는지 임금이 얼마나 들어갔는지 알고 싶을 때 쓰는 수식을 알려드릴게요. 

이미 만들어진 시트"2020-03"을 참조해서 공수 및 임금 부분에 대한 수식을 다음과 같이 만들 수 있습니다. 

공수 합계
=SUMIF('2020-03'!B:B,'2020-03합계'!A23,'2020-03'!F:F)


임금 합계
=SUMIF('2020-03'!B:B,'2020-03합계'!A23,'2020-03'!G:G)



위 수식은 SUMIF를 이용해 왼쪽 셀에 있는 내용(예:"주일건설")이 있으면 해당 공수를 전부 더하겠다는 수식입니다. 


여기서 B:B, F:F 처럼 열 전체에 대해 영역을 지정할 때 편하게 쓸 수 있습니다. 


SUMIF를 잘 사용하면 통계나 합계 낼 때 굉장히 유용하게 사용할 수 있습니다. 프로그래밍 언어에서 IF을 이용하여 합계 내는 것과 유사합니다. 중첩된 IF문을 쓰고 싶다면 SUMIFS가 있습니다. 


엑셀 열 or 엑셀 행 합계 낼때 유용한 함수 하나 알려드릴게요. 


=SUM(OFFSET(C2, 0,0, ROW()-2,1))




단순하게 SUM 함수를 쓰면 위와 같이 삽입해서 추가하면 어때요? 10,000이 더 대해져 11,111원이 되어야하는데 그렇지 않죠. 귀찮게 매번 수식을 고쳐야 합니다. 


이때 사용하기 좋은 함수가 OFFSET이예요. 


OFFSET은 주어진 참조 영역으로부터 지정한 행과 열만큼 떨어진 위치의 참조 영역을 반환하죠. 


=SUM(OFFSET(C1, 0,0, ROW()-1,1))


위 수식을 자세히 살펴보겠습니다. 


OFFSET(reference, rows, cols, height, width) 

OFFSET(기준셀, ROW위치, COL위치, 세로높이, 가로폭) 


위 수식은 기준셀(참조셀)이 C1이고 0,0이니 기준셀에서 ROW및 COL을 시작하고, 가로폭은 1이고, 세로높이는 현재 ROW()번호를 받아 -1만큼 해서 세로높이를 지정한다는 이야기입니다. 


만약에 ROW()를 설정해서 -1을 하지 않으면 순환참조가 되어 버립니다. 이렇게 하면 매번 삽입을 해도 SUM함수를 고치지 않아도 됩니다.