offset, counta, 이름관리자 활용한 동적범위 간단 정리

offset, counta, 이름관리자 활용한 동적범위 간단 정리

 

데이터를 보관 및 정리하고 이 데이터를 바탕으로 의미있는 정보를 도출하는 프로그램은 엑셀만큼 쉽고 편리한 게 없다. 전문가 수준에는 엑셀보다 더 편리한 프로그램이 많겠지만, 일반인 수준에서는 엑셀만 잘 다뤄도 능력자 소리를 듣는다. 오늘은 엑셀 관련 업무를 하면서 도움이되는 기능을 공유하고자 한다.

 

일명 동적범위라 하는 offset, counta, 이름관리자 기능을 조합하는 '동적범위'라는 기능이다. 동적범위라는 건 범위가 '동(動 움직일 동)'자를 써서 변할 수 있음을 나타내는 것이다.

 

1) offset, counta, 이름관리자 기능 정리

---

우선 offset, counta, 이름관리자 기능에 대해 간단하게 알아보자.

OFFSET: 주어진 참조 영역으로부터 지정한 행과 열만큼 떨어진 위치의 참조 영역을 돌려줍니다

조금 어려울 수 있는데, 범위를 만드는 함수라고 생각하면 쉽다.

 

COUNTA: 범위에서 비어있지 않은 셀의 개수를 구합니다

count all를 줄여 counta로 표현한 것 같다. 말그대로 비어있지 않은 셀의 개수를 센다. 빈 셀 빼고 다 센다는 거다. count 함수는 숫자만 카운팅하지만 counta는 숫자건 문자건 상관없이 셀에 데이터가 들어있으면 무조건 카운팅 하는 함수이다.

1,2,영수,4,5 라는 배열을 counta로 하면 5개를 인식하지만, count로 하면 4개로 인식한다. (숫자가 4개)

 

이름관리자

이름관리자는 쉽게 말해서 일종의 집합을 만드는 것이다. 5명으로 이뤄진 하나의 클래스 명단을 '우리반' 이라는 이름을 붙여 하나의 집합으로 관리할 수 있다. 이렇게 이름관리자로 집합을 만들어두면 매번 범위를 드래그할 필요 없이 집합 이름을 입력할 수 있다.

간단하게 vlookup 함수를 이용하면 셀 범위가 들어갈 자리에 '우리반'이라고 입력해도 올바른 값을 나타낸다.

이름관리자에 대해서는 다음 포스팅에서 좀 더 자세하게 다뤄볼 예정이다.

 

2) 동적범위 만드는 방법

---

아까 이름관리자로 만들었던 우리반을 예시로 설명하겠다. 아래 명단과 같이 5명이 한 반으로된 클래스에서 다른 학생이 전학 온 경우를 가정하자. 그러면 번호 6번이 추가되고 이름도 추가된다. 여기서 만약에 아까 지정했던 '우리반' 집합으로 청소당번이 6번으로 뽑으면 오류가 난다.

이런 경우에는 이름관리자 기능에 들어가서 범위를 수정해줄 수 있지만, 번거롭다. 여기서 동적범위 기능을 활용하면 매번 이름관리자에서 범위를 바꾸지 않아도 된다. 함수는 간단하다.

OFFSET(기준셀,0,0,counta(높이를 세는 알파벳),counta(폭을 세는 숫자))

이 경우에는 공식대로 적용해보면 OFFSET(B5,0,0,counta(B:B)-1,counta(4:4))가 된다. 그래서 셀에 입력해보면 엥? 오류가 뜬다. 오류가 뜨는 게 정상이다. 왜냐하면 셀 하나에서는 범위를 표현할 수 없기 때문이다. 이 함수를 그대로 복사해서 이름관리자 기능으로 이동한다. 그리고 주의할 점은 높이에서 1을 빼주어야 한다. 그 이유는 번호, 이름과 같은 구분을 해주는 행이 있기 때문이다. 만약 이런 구분을 해주는 게 열(세로)에 있다면 폭에서 -1을 해주어야 한다.

이렇게 작성한 함수를 이름관리자에 가서 붙여넣기 하면 된다. 단, 붙여넣고 나서 바로 체크표시를 누르지 말고, F4를 눌러 절대참조로 바꾸는 게 좋다. 그래야 오류가 발생하지 않는다.

동적범위를 설정하고 번호와 이름을 추가해주면 바로바로 반영이 되는 걸 볼 수 있다.

 

주의할 사항

---

데이터가 있는 셀 바깥 쪽 영역에는 아무런 데이터가 없어야 한다. 안그러면 counta 함수가 해당 셀도 카운팅하기 때문에 값이 맞지 않게 된다.

 

마무리하며

---

동적범위는 엑셀에서 정말 편한 기능이다. 특히 엑셀로 어떤 명단, 데이터를 관리할 때는 그 데이터가 무조건 변하기 마련이다. 추가되거나 삭제된다. 그럴때마다 번거롭게 셀 영역을 바꿔주는 것도 일이다. 이렇게 함수를 잘 활용하면 업무에 들어가는 시간을 대폭 줄일 수 있다.

엑셀로 관리하는 명단이 있다면 동적범위 기능을 꼭 써보길 바랍니다. 감사합니다.