- 최초 작성일: 2014-11-18
- 최종 수정일: 2026-09-21
- 조회수: 35 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 출근기록부 만들기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
모처럼만의 엑셀 강좌입니다. ^^
어느 분이 주신 질문입니다.
[표 1]과 같은 형태로 된 근태기록부가 있습니다.
(자료의 형태와 이름은 강의 포맷에 맞게 바꾸었습니다)
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 갑돌이 | 병돌이 | 갑돌이 | 을돌이 | 갑돌이 | 갑돌이 | 을돌이 | 갑돌이 | 정돌이 | 갑돌이 | 을돌이 | 을돌이 | 임돌이 | 을돌이 | 갑돌이 |
| 을돌이 | 갑돌이 | 병돌이 | 병돌이 | 을돌이 | 을돌이 | 임돌이 | 병돌이 | 무돌이 | 기돌이 | 병돌이 | 병돌이 | 계돌이 | 정돌이 | 을돌이 |
| 병돌이 | 신돌이 | 정돌이 | 기돌이 | 병돌이 | 병돌이 | 병돌이 | 정돌이 | 기돌이 | 경돌이 | 정돌이 | 정돌이 | 병돌이 | 임돌이 | 병돌이 |
| 정돌이 | 정돌이 | 기돌이 | 경돌이 | 무돌이 | 정돌이 | 무돌이 | 무돌이 | 경돌이 | 신돌이 | 무돌이 | 갑돌이 | 정돌이 | 무돌이 | 정돌이 |
| 무돌이 | 기돌이 | 을돌이 | 정돌이 | 기돌이 | 기돌이 | 기돌이 | 기돌이 | 임돌이 | 을돌이 | 기돌이 | 무돌이 | 무돌이 | 기돌이 | 무돌이 |
| 기돌이 | 무돌이 | 경돌이 | 무돌이 | 경돌이 | 경돌이 | 경돌이 | 경돌이 | 병돌이 | 경돌이 | 경돌이 | 기돌이 | 경돌이 | 기돌이 | |
| 경돌이 | 경돌이 | 신돌이 | 신돌이 | 신돌이 | 신돌이 | 신돌이 | 임돌이 | 무돌이 | 임돌이 | 임돌이 | 경돌이 | 갑돌이 | 경돌이 | |
| 신돌이 | 임돌이 | 임돌이 | 계돌이 | 임돌이 | 정돌이 | 계돌이 | 임돌이 | 갑돌이 | 계돌이 | 신돌이 | 신돌이 | 신돌이 | ||
| 임돌이 | 계돌이 | 계돌이 | 계돌이 | 계돌이 | 계돌이 | |||||||||
| 계돌이 |
이것을 아래와 같은 모양으로 바꾸려면 어떻게 해야 할까요?
| 갑돌이 | 을돌이 | 병돌이 | 정돌이 | 무돌이 | 기돌이 | 경돌이 | 신돌이 | 임돌이 | 계돌이 | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | O | O | O | O | O | O | O | O | O | O |
| 2 | O | X | O | O | O | O | O | O | O | X |
| 3 | O | O | O | O | X | O | O | O | X | X |
| 4 | X | O | O | O | O | O | O | O | O | O |
| 5 | O | O | O | X | O | O | O | O | X | O |
| 6 | O | O | O | O | X | O | O | O | O | O |
| 7 | X | O | O | O | O | O | O | O | O | X |
| 8 | O | X | O | O | O | O | O | X | O | O |
| 9 | X | X | X | O | O | O | O | X | O | X |
| 10 | O | O | O | X | O | O | O | O | O | O |
| 11 | O | O | O | O | O | O | O | X | O | O |
| 12 | O | O | O | O | O | X | O | X | O | O |
| 13 | X | X | O | O | O | O | O | O | O | O |
| 14 | O | O | X | O | O | O | O | O | O | O |
| 15 | O | O | O | O | O | O | O | O | X | X |
- 최신 글을 불러오는 중...
엑셀 출근기록부 만들기
핵심 요약: 근태기록부를 사람별 출근표로 바꾸는 수식
날짜별로 출근한 사람 이름을 나열한 [표 1]을, 사람과 날짜별로 출근 여부를 O/X로 보여 주는 [표 2]로 바꿉니다. OFFSET, COUNTA, COUNTIF 함수를 조합한 수식 하나를 복사해서 채웁니다.
- 수식 입력 셀: [표 2]의 C30 셀(빨간색 셀)에 수식을 입력하고 오른쪽과 아래쪽으로 복사합니다.
- OFFSET 함수: 날짜 번호에 맞는 열의 이름 목록 범위를 만듭니다.
- COUNTIF 함수: 그 범위에 해당 이름이 있는지(1이면 출근) 셉니다.
- IF 함수: 결과가 1이면 "O", 아니면 "X"를 표시합니다.
1. 수식으로 [표 1]을 [표 2]로 바꾸기
VBA를 이용하여 해결할 수도 있겠지만 몇 가지 함수를 조합하면 해결이 가능합니다.
C30 셀(위 빨간색 셀)에 이런 수식을 입력하고, 오른쪽과 아래쪽으로 복사합니다.
=IF(COUNTIF(OFFSET($B$14,1,$B30-1,COUNTA($B$15:$B$24),1),C$29)=1,"O","X")Offset, Counta, Countif 함수를 사용하였습니다.
참고: 수식을 이루는 부분
OFFSET(...)은 [표 1]에서 해당 날짜 열의 이름 목록 범위를 만들고, COUNTIF(범위, C$29)는 그 범위에 [표 2] 머리글의 이름(C$29)이 몇 번 나오는지 셉니다. 결과가 1이면 출근이므로 IF가 "O", 아니면 "X"를 표시합니다. 셀 주소의 $B30은 열을 고정하고 C$29는 행을 고정한 혼합 참조라서, 오른쪽과 아래쪽으로 복사해도 각각 날짜 번호와 이름을 정확히 가리킵니다.
💡 TIP: 규칙 찾기
매사가 그렇듯, 일을 시작하기 앞서 어떤 규칙이 있는지 찾아내는 것이 중요합니다. 빈 종이를 한 장 꺼내서 셀 주소가 변함에 따라 Offset 함수의 인수 값이 어떻게 변해가는지 추적해 보시면 어렵지 않게 규칙을 발견할 수 있으리라 생각합니다.
정리 — 수식 구성 요소 한눈에 보기
| 수식의 부분 | 하는 일 |
|---|---|
OFFSET($B$14,1,$B30-1,COUNTA($B$15:$B$24),1) |
B14 기준 아래로 1행, 오른쪽으로 (날짜 번호-1)열 이동한 곳에서 시작하는, 높이는 명단 수·너비는 1열인 범위(해당 날짜의 이름 목록) |
COUNTA($B$15:$B$24) |
명단의 인원수(예제에서는 10) |
COUNTIF(범위, C$29) |
범위에서 [표 2] 머리글의 이름이 몇 번 나오는지 셈 |
IF(…=1,"O","X") |
1이면 "O"(출근), 아니면 "X"(미출근) |
자주 묻는 질문 (FAQ)
Q1. 날짜별로 출근자 이름을 나열한 근태기록부를 사람별 O/X 표로 바꾸려면 어떻게 하나요?
VBA 없이 OFFSET, COUNTA, COUNTIF 함수를 조합한 수식 하나로 됩니다. 예제에서는 C30 셀에 =IF(COUNTIF(OFFSET($B$14,1,$B30-1,COUNTA($B$15:$B$24),1),C$29)=1,"O","X")를 입력하고 오른쪽과 아래쪽으로 복사했습니다.
Q2. 이 수식에서 OFFSET 함수는 어떤 역할을 하나요?
날짜에 해당하는 열의 이름 목록을 범위로 잡아 줍니다. OFFSET($B$14,1,$B30-1,COUNTA($B$15:$B$24),1)은 B14 셀에서 아래로 1행, 오른쪽으로 (날짜 번호-1)열 이동한 위치에서 시작해 높이가 명단 수, 너비가 1열인 범위를 만듭니다. 날짜 번호($B30)가 커질수록 오른쪽 열로 이동합니다.
Q3. 높이 인수에 COUNTA($B$15:$B$24)를 사용한 이유는 무엇인가요?
각 날짜 열에서 읽을 행 수를 명단 전체 인원수로 맞추기 위해서입니다. 예제에서는 1일(B열)에 모든 사람이 출근해 B15:B24에 명단 10명이 모두 있으므로, 그 개수(COUNTA)를 각 날짜 열의 범위 높이로 사용했습니다.
마치며
다음 시간에…
