근태 엑셀을 AI로 반자동으로 만든 순서, 월별 시트에서 대장 하나로

근태 엑셀은 처음에 달마다 시트 두 장이었다. “1월근태”에 출입기록을 붙여넣고, “1월연차”에 연차를 적었다. 한 해가 지나면 시트가 스물네 장을 넘었다. 근태 시트에는 수식이 없어서 지각과 연장근무는 눈으로 봤다.

지금은 입력은 대장 몇 장에 하고, 월만 고르면 대시보드와 직원별 집계, 주간보고가 바뀐다. 수식과 시트 구성은 AI로 만들었다. 다만 완전 자동은 아니다. 출입기록 붙여넣기와 지각 판정은 여전히 사람이 한다. 데이터가 쌓이면서 엑셀이 많이 느려져서, 이제는 직접 만드는 업무 프로그램으로 옮기려고 한다.

글에 넣은 화면은 실제 엑셀의 구성을 따르되 색, 팀 이름, 숫자를 모두 바꿔 다시 그린 것이다.

달마다 시트를 나누면 1년치를 한 번에 볼 수 없다

월별 시트는 만들 때는 편하다. 새 달이 되면 시트를 복사해 붙여넣으면 된다. 문제는 여러 달을 묶어 봐야 할 때다. 한 사람의 1년 연장근무를 보려면 열두 장을 넘겨야 하고, 월별 추이를 내려면 시트마다 합계를 따로 모아야 한다.

그래서 월별 시트를 없애고 출근부 한 장에 모든 날을 쌓았다. 월은 시트가 아니라 날짜 열로 구분한다. 이렇게 두는 게 관리도 편하고, 나중에 통계를 낼 때도 쓸모가 있었다.

시트는 세 가지로 나눴다. 사람이 채우는 대장, 입사·퇴사 때만 고치는 기준표, 손대지 않는 보고 시트다. 아래에서 차례로 적는다.

입력하는 시트는 종류마다 대장 하나로 만든다

출근부처럼 연차, 지각, 연장근무, 건강검진도 각각 한 장짜리 대장으로 만들었다. 한 줄에 한 건, 모든 줄에 날짜가 있게 한다. 이 두 가지만 지키면 어떤 월이든 날짜로 골라 집계할 수 있다.

출근부에 출입기록을 붙여넣으면 줄마다 들어 있는 수식이 계산을 한다. 출근·퇴근 판정, 근무시간, 기본·연장·휴일 시간이 저절로 채워진다. 그날 연차나 검진이 있으면 연차 대장과 검진 대장을 찾아 출근부에 표시한다.

직원표 한 장을 모든 시트의 기준으로 둔다

대장마다 부서와 직급을 손으로 적으면 입퇴사나 부서 이동이 있을 때마다 여러 곳을 고쳐야 한다. 그래서 직원표를 따로 두고, 대장에는 이름만 적는다. 이름을 쓰면 부서, 팀, 직급이 직원표에서 채워진다.

• 입사하면 직원표 아래 빈 줄에 한 사람을 추가한다.
• 퇴사하면 그 사람의 퇴사일 칸만 채운다. 재직 인원 수가 저절로 바뀐다.
• 직원표에는 “관리 대상” 열이 있다. 임원은 자동으로 제외되고, 예외가 있으면 손으로 바꾼다.

보고 시트는 입력 시트를 읽기만 한다

보고용 시트는 대시보드, 직원별 집계, 연장근로 분석, 월별 추이, 주간보고다. 보고 시트에는 아무것도 입력하지 않는다. 모두 대장에서 숫자를 읽어 온다. 입력하는 곳과 보는 곳이 섞이면, 보고서의 숫자가 계산된 값인지 누가 손으로 고친 값인지 알 수 없다.

월을 고르는 칸은 대시보드 한 곳에만 있다. “5월”을 고르면 숨겨 둔 설정 시트가 이것을 그달 첫날부터 마지막 날까지의 날짜 범위로 바꾸고, 대시보드와 직원별 집계, 연장근로 분석이 모두 그 범위로 다시 계산된다. 월별 추이는 1월부터 12월까지를 늘 같이 보여 주고, 주간보고는 주차를 따로 고른다.

연장근로 분석에는 환산 인원을 넣었다. 연장근로가 몇 사람 몫의 일인지를 보는 숫자다. 한 달 연장근로 312시간을 월 소정근로시간 174시간으로 나누면 1.8명이다. 174시간은 주 40시간을 한 달 평균 주 수(약 4.35주)로 곱한 값이다. 인건비로 볼 때는 연장근로 가산 1.5배를 곱해서 312 × 1.5 ÷ 174 = 약 2.7명분으로 본다.

붙여넣는 값은 형식부터 맞춘다

반자동 엑셀에서 가장 자주 틀어지는 곳은 밖에서 가져와 붙여넣는 값이다. 출입관리 프로그램에서 받은 월별 집계를 붙여넣었더니, 월이 날짜로 들어온 줄과 “2026-05” 같은 글자로 들어온 줄이 섞였다. 그래서 어느 쪽으로 들어와도 같은 글자 형식으로 바꾸는 열을 옆에 두고, 집계는 그 열로 한다.

수식 범위도 미리 정해 둬야 한다. 대장 수식은 1,000줄까지 들어가 있고, 넘치면 마지막 줄 수식을 복사해 늘린다. 이런 약속은 파일 첫 장의 사용법 시트에 적어 두었다. 시트마다 무슨 일을 하는지, 어디는 입력하고 어디는 건드리면 안 되는지가 적혀 있다.

AI로 엑셀을 만들 때 요청하는 법

수식을 하나씩 짜 달라고 하기보다 어떤 시트가 필요하고 무엇을 입력하면 무엇이 나와야 하는지를 먼저 말한다.

근태 엑셀 새로 짜줘.
달마다 있는 시트를
출근부 하나로 합치고
연차, 지각, 연장근무는
대장을 따로 만들어.
이름만 쓰면 부서가 채워지게.

구조를 만들 때는 실제 근태 파일 대신 가짜 이름과 가짜 시각으로 만든 예시 파일로 해도 충분하다. 수식이 맞게 도는지 확인한 뒤 실제 기록은 내가 붙여넣으면 된다. 근태 기록에는 사람 이름과 출퇴근 시각이 그대로 들어 있다.

엑셀로는 느려지고, 여전히 손이 간다

반자동으로 만든 뒤 한계도 분명해졌다.

• 데이터가 길어지니 속도가 너무 느려졌다. 출근부의 줄마다 수식이 여러 개씩 들어 있어서, 몇 달치가 쌓이자 수식이 수만 개가 됐다.
• 완전 자동은 아니어서 손이 간다. 출입기록을 받아 붙여넣고, 지각은 사유를 보고 판정해 표시하고, 입퇴사가 있으면 직원표를 고친다.

그래서 이 엑셀을 직접 만드는 업무 프로그램으로 옮기려고 한다. 엑셀에서 나눠 둔 대장과 직원표는 프로그램에서도 표 하나씩으로 그대로 옮길 수 있다. 업무 프로그램은 자료 아카이브를 만들면서 한 번 해 봤다.

출퇴근 기록을 근태로 판정하는 기준, 연장근로와 연차를 적는 기준은 출입기록을 근태로 바꾸는 기준부터 이어지는 근태관리 연재에 따로 정리했다.