안녕하세요. 오랜만에 포스팅을 하네요. 오늘은 OFFSET함수를 활용해 교육 참석자 명단을 가져오는 방법에 대해 공부해보겠습니다. 인사노무팀에서 근무하시는 분들께 가장 직접적으로 도움이 될 내용입니다. 다른 팀이라 하더라도 OFFSET 함수의 개념은 알고 계시면 실무에 큰 도움이 되실 것이니 이번 기회에 OFFSET 함수를 꼭 공부하시기 바랍니다. 그럼 예제를 보면서 알아보겠습니다.
위와 같이 교육 참석자 명단이 있습니다. 각 팀별로 교육 참석자 명단을 시트별로 작성한다고 가정하겠습니다. 예시는 경영관리팀, PM팀 두 개만 적어놨지만 실제 회사에서는 훨씬 팀이 많겠죠? 팀이 얼마 되지 않는다면 인사명부에서 복사해서 붙여 넣기 해도 되지만 회사 규모가 크고 팀이 많을 경우 생각보다 시간이 많이 걸릴 수 있습니다. 이걸 자동으로 가져와 보겠습니다.
우선 OFFSET 함수에 대한 정의를 살펴보겠습니다.
◈OFFSET 함수란?
- 셀 또는 셀 범위에서 지정된 수의 행과 열로 구성되는 범위에 대한 참조를 반환합니다.
◈OFFSET 함수 구문 및 인수
- 함수 구문 : =OFFSET(reference, rows, cols, [height], [width])
- 인수
1) Reference(필수요소) : 오프셋을 기본으로 할 참조. 참조는 인접한 셀의 셀 또는 범위를 참조해야 함.
2) rows(필수요소) : 기본 참조 셀에서 아래로 몇 번째 이동하는지 정하는 인수
3) cols(필수요소) : 기본 참조 셀에서 오른쪽으로 몇 번째 이동하는지 정하는 인수
4) height(선택 요소) : 반환되는 참조의 높이(행수)
5) width(선택 요소) : 반환되는 참조의 너비(열수)
개념이 어느 정도 이해되셨나요? 글로만 봐서는 잘 이해가 안 갈 수 있으니 예제를 보면서 한번 더 살펴보겠습니다.
위와 같이 인사명부가 있습니다. 저 인사명부에서 성명과 사번을 교육 참석자 명단에 가져올 겁니다. 경영관리팀 명단을 가져와보겠습니다.
일단 정답을 먼저 알려드리고 수식을 분석해보겠습니다. B40셀의 수식을 살펴보겠습니다.
우선 첫 번째 인수인 Reference를 '인사명부!$G$1'로 지정했습니다. Reference 인수가 기본으로 할 참조라고 했었죠? 더 쉽게 말하자면 시작점이 어디냐는 겁니다. 인사명부 시트의 G1셀부터 시작하겠다는 의미입니다.
자 이제 두 번째, 세 번째 인수에 대해 살펴볼까요? 앞전에 개념 설명을 다시 말씀드리면 rows는 아래로 몇 번째 이동 인지 cols는 오른쪽으로 몇 번째 이동하는지 숫자를 입력하는 겁니다. 인사명부 시트를 다시 볼까요?
저희는 경영관리팀 6명 명단을 가져올 겁니다. 그래서 G14셀인 경영관리팀이 아래로 몇 번째 이동하는지 입력하면 됩니다. G14셀이니 13을 입력하면 되겠죠? 그냥 숫자로 13를 입력해도 원하는 값을 가져올수는 있습니다. 그런데 팀이 많을 경우 각 팀마다 몇번째 위치해있는지 일일히 세알려야하는 노가다?) 작업을 해야합니다. 그리고 13를 입력하게 되면 14행의 명단은 가져올 수 있겠지만 15행은 가져오지 못합니다. 그래서 황황호 씨를 명단에 가져오려면 수식을 13에서 14로 별도로 바꿔줘야 하는 불편함이 있습니다. 그러면 그냥 복사해서 가져오는 것만 못하겠죠?
여기서 저희는 MATCH 함수를 활용할 겁니다. MATCH함수에 대한 자세한 설명은 제가 이전에 포스팅했던 INDEX MATCH함수 글을 참조하시면 도움이 되실 겁니다. 간단하게 설명하자면 내가 원하는 값이 지정한 범위에서 몇 번째 위치에 있는지 숫자로 나타내 주는 함수입니다.
K39셀이 14로 나와있죠? MATCH함수를 활용해 경영관리팀이 G열에서 몇 번째에 위치해 있는지를 나타내는 겁니다.
그리고 L39셀을 보시면 COUNTIFS 함수로 경영관리팀이 총 몇 명인지 나타냈습니다. 그럼 수식을 드래그할 때 몇 번까지 하면 되는지 바로 알 수 있겠죠?
다시 교육 참석자 명단 시트로 가보겠습니다.
경영관리팀 시트 K39, 즉 14에서 -2를 빼주면 12가 됩니다. 거기서 A열의 연번을 순서대로 더해주면 13,14,15~ 이런 식으로 rows 인수를 자동으로 입력이 가능합니다. 그리고 세 번째 인수인 cols는 사 번의 경우 오른쪽으로 2번 이동하면 되기 때문에 숫자로 2를 입력했습니다. 성명에는 1을 입력하면 되겠죠?
오늘은 offset 함수에 대해 알아보았습니다. 다른 함수에 비해 조금 고급 함수에 속해서 이해가 어려우실 수 있지만 알고 계시면 실무에 큰 도움이 되실 겁니다. 궁금한 점은 댓글 남겨주시면 답변드리겠습니다.
2021.12.03 - [엑셀/함수] - 엑셀 COUNTIF 함수로 조건에 맞는 개수구하기
엑셀 COUNTIF 함수로 조건에 맞는 개수구하기
안녕하세요. 오늘은 엑셀 실무에서 자주 쓰이는 함수 중에 하나인 COUNTIF 함수에 대해 알아보겠습니다. 엑셀을 사용하다 보면 특정 셀의 개수를 구해야 하는 경우가 있습니다. 개수를 세는 방법
ryusfactory.tistory.com
2021.05.01 - [엑셀/함수] - 특정 문자를 다른 문자로 바꾸는 함수(REPLACE, SUBSTITUTE 함수)
특정 문자를 다른 문자로 바꾸는 함수(REPLACE, SUBSTITUTE 함수)
텍스트에서 특정 문자열을 다른 문자열로 바꿀 때 사용하는 함수로 REPLACE 함수와 SUBSTITUTE 함수가 있습니다. REPLACE 함수는 특정 위치에 있는 문자열을 다른 문자열로 바꿀 때 사용하고, SUBSTITUTE
ryusfactory.tistory.com
'엑셀 > 함수' 카테고리의 다른 글
| TEXT함수로 날짜표시형식 바꾸기(with DATE,MID,FIND 함수) (0) | 2023.12.25 |
|---|---|
| 엑셀 COUNTIF 함수로 조건에 맞는 개수구하기 (2) | 2021.12.03 |
| 특정 문자를 다른 문자로 바꾸는 함수(REPLACE, SUBSTITUTE 함수) (0) | 2021.05.01 |
| 엑셀 RANK 함수 사용법 (0) | 2021.03.24 |
| 엑셀 CHOOSE 함수 사용 방법 (0) | 2021.02.15 |
댓글