기준일 하나만 입력했을 뿐인데, 재직자/입사자/퇴사자/퇴사예정자 명단이 자동으로 뚝딱 생성된다면? 실무 효율성, 제대로 끌어올릴 수 있어요.
안녕하세요, 엑셀 좀 써봤다 싶은 실무자분들, 특히 인사 데이터를 자주 다루는 분들께 이 글이 진심으로 도움이 되었으면 해요. 제가 최근에 처리한 인사 현황 업무에서 ‘기준일 기반 자동화’ 방식으로 시트를 구성했는데, 그 편리함이 정말 대단했거든요. 퇴사일과 입사일이 섞인 데이터를 일일이 필터링하고 정렬할 필요 없이, 기준일 하나만 바꾸면 네 가지 명단이 자동으로 갱신되는 구조를 만들었습니다. 이 경험을 토대로, 어떻게 구성했고 어떤 함수들을 활용했는지 지금부터 차근차근 소개해드릴게요.
목차
시트 구성: 전체 흐름 이해하기
이 자동화 시스템은 두 개의 시트로 구성돼 있어요. 하나는 모든 인사 데이터를 담고 있는 직원명부 시트, 다른 하나는 기준일에 따라 명단이 자동으로 추출되는 재직입퇴사자현황 시트입니다.
각 명단은 INDEX와 MATCH, 그리고 여러 조건식을 조합해 추출되며, 이 구조 덕분에 실시간으로 명단이 자동 갱신되죠. 기준일을 입력하면, 그 날짜를 기준으로 네 가지 명단이 아래와 같이 구분됩니다:
- 재직자: 입사일이 기준일 이전, 퇴사일이 없거나 기준일 이후
- 입사자: 입사일이 기준일과 정확히 일치
- 퇴사자: 퇴사일이 기준일과 동일
- 퇴사예정자: 퇴사일이 기준일 이후 30일 이내
주요 함수 설명: INDEX부터 --(조건)까지
| 함수 | 기능 설명 |
|---|---|
| INDEX | 지정된 범위에서 행/열 번호에 해당하는 값을 반환 |
| MATCH | 해당 값이 몇 번째 열 또는 행에 있는지 위치 반환 |
| LARGE + ROW | 조건을 만족하는 행 번호를 정렬된 형태로 추출 |
| IFERROR | 에러 발생 시 빈칸 혹은 0으로 처리 |
| VALUE | 문자열 형태의 날짜를 실제 날짜로 변환 |
| --(조건) | TRUE/FALSE 값을 1과 0으로 바꿔 조건 계산 가능 |
실전 수식 예시와 해설
아래는 네 가지 명단을 추출하기 위해 사용된 실제 수식이에요. 수식마다 어떤 조건을 사용했는지 잘 살펴보면 자동화의 핵심을 이해할 수 있어요.
- 재직자 수식: 입사일 ≤ 기준일 (퇴사일이 비어있거나 기준일보다 이후)
- 입사자 수식: 입사일 = 기준일
- 퇴사자 수식: 퇴사일 = 기준일
- 퇴사예정자 수식: 퇴사일 > 기준일 AND 퇴사일 ≤ 기준일 + 30
사용법: 기준일 하나로 네 가지 명단 출력하기
사용법은 정말 간단해요. ‘재직입퇴사자현황’ 시트의 기준일 셀에 원하는 날짜를 입력하기만 하면 됩니다. 그 즉시 아래 네 가지 명단이 자동으로 갱신돼요. 물론 이 기능이 작동하려면 직원명부 시트의 데이터가 미리 잘 정리되어 있어야겠죠.
| 명단 종류 | 기준 조건 |
|---|---|
| 재직자 | 입사일 ≤ 기준일, 퇴사일이 없거나 기준일 이후 |
| 입사자 | 입사일 = 기준일 |
| 퇴사자 | 퇴사일 = 기준일 |
| 퇴사예정자 | 기준일 이후 30일 이내 퇴사 예정 |
실무 팁과 자주 생기는 문제 해결법
자동화 구조가 아무리 잘 되어 있어도, 실무에서는 항상 예상치 못한 문제가 생기기 마련이에요. 그래서 제가 직접 경험한 몇 가지 이슈와 해결 팁을 정리해볼게요.
- 입사일/퇴사일이 빈 셀로 남아있는 경우: VALUE 함수가 오류를 낼 수 있으니, IFERROR로 꼭 감싸주세요.
- 날짜 형식 통일 안 됐을 때: VALUE 함수로 통일해서 수식에 넣는 게 좋아요.
- 명단이 비정상적으로 적게 나올 때: 수식 내 조건이 맞지 않는 경우가 많아요. 날짜 연산 확인 필수!
- 배열수식 입력 시: 이 자동화 수식들은 대부분 배열수식이에요. 반드시 Ctrl + Shift + Enter로 입력해 중괄호 { }가 자동으로 붙도록 해주세요. 안 그러면 수식이 작동하지 않습니다!
정리: 엑셀만으로 구현하는 인사 자동화
이번 글을 통해 보여드리고 싶었던 건, 거창한 매크로나 VBA 없이도 엑셀 기본 함수만으로도 충분한 자동화가 가능하다는 거예요. 특히 인사 데이터처럼 반복적인 구조를 가진 표에서는, 조건 기반으로 INDEX/MATCH 조합을 사용하면 마법처럼 필요한 데이터를 꺼낼 수 있죠.
- 반복 작업을 줄이고,
- 실수를 방지하며,
- 업무 시간을 단축하는 데 딱이죠.
기준일은 "2024-03-01"과 같은 날짜 형식으로 입력해야 오류 없이 계산됩니다. 날짜가 텍스트로 저장되어 있다면 VALUE 함수로 변환하세요.
IFERROR 함수를 활용해 오류 발생 시 0 또는 빈칸으로 처리하는 것이 가장 안정적입니다. 예: IFERROR(수식, "")
조건에 맞는 여러 값을 추출할 때는 LARGE 함수가 가장 직관적이에요. 조건을 만족하는 행 번호를 정렬된 순서로 뽑는 데 필수입니다.
대부분 날짜 비교 조건에서 오류가 있는 경우입니다. 입사일, 퇴사일이 숫자(날짜) 형식으로 되어 있는지 확인해보세요.
퇴사예정자는 기준일 이후 30일 이내로 퇴사일이 잡혀 있는 직원을 의미합니다. 수식에 기준일+30이 포함되어 있어요.
MATCH 함수가 열 위치를 자동으로 찾아주기 때문에 열 순서가 달라도 무방합니다. 다만 열 제목은 정확히 일치해야 해요.
오늘 소개한 엑셀 기반의 재직입퇴사자 자동화 시트, 실무에서 한 번만 제대로 세팅해두면 정말 게임 체인저가 될 수 있어요. 저는 실제로 이 구조를 팀원들과 공유해서, 반복되는 인사 명단 요청이나 업무 보고서를 훨씬 빠르게 처리할 수 있었답니다. 엑셀을 단순한 입력 도구로만 보지 마세요. 이렇게 기본 함수만으로도 강력한 자동화가 가능하다는 걸 꼭 경험해보시길 바랍니다.
혹시 글을 읽고 따라 해보셨거나, 구현 도중 막히는 부분이 있었다면 댓글로 꼭 알려주세요! 더 나은 버전으로 함께 발전시켜 나가요. 그리고 이 방식이 유용했다면, 주위 동료들에게도 살짝 공유해보시는 건 어떨까요? 😉
'회사생활' 카테고리의 다른 글
| [엑셀실무]주민등록번호 처리 함수 총정리(REPLACE,CHOOSE 함수 활용) (0) | 2025.04.02 |
|---|---|
| 폭싹 속았수다? 국세청 ‘원클릭’ 종합소득세 환급 서비스로 5년치 세금 돌려받기 (0) | 2025.03.31 |
| [광고]어르신 건강 챙기기, 뉴케어 한 팩으로 충분했어요 (1) | 2025.03.29 |
| 의료기관 취업 전, 꼭 필요한 범죄경력조회 회보서 직접 발급하기 (0) | 2025.03.25 |
| 애드센스 티스토리 승인 완료! 내가 했던 준비 전부 알려드립니다. (1) | 2025.03.23 |
