공사일보 양식 만들기 1편 (기본 서식 및 이름 정의)
지난 포스팅(EX-02)에 이어 엑셀 공사일보 자동화 양식을 만들어가는 과정을 시작하겠습니다.
제가 실무에서 사용하기 위해 만든 파일을 바탕으로, 여러분도 각자의 현장 상황에 맞게 폼을 수정하고 적용하실 수 있도록 기초적인 부분부터 상세하게 설명해 보겠습니다.
오늘은 추후 버튼을 눌러 매일 새로운 날짜의 공사일보 시트를 생성할 때 뼈대가 되는 '입력서식(기본 폼)'을 설정하고, 이 파일의 핵심 기능인 '이전 시트 누계 데이터 자동으로 불러오기' 수식을 적용해 보겠습니다.
이전 포스팅에 첨부해 드린 실습 파일을 다운로드하신 후, 파일 내의 '입력서식' 시트를 열고 아래의 순서대로 진행해 주시기 바랍니다.
1. 공사일보 기본 정보 입력란 세팅
먼저 매일 반복해서 사용될 공사일보의 머리말 부분을 설정합니다. 아래의 이미지와 설명을 참고하여 서식을 지정해 주세요.
- ① 공사현장명: 현장의 이름을 입력하는 란입니다. 이 시트는 기본 폼이므로 변하지 않는 고정된 현장명을 적어둡니다.
- ② 날짜 서식: 해당 셀을 마우스 우클릭하여 [셀 서식]에 들어간 뒤, 원하는 날짜 표시 형태(예: 0000년 00월 00일)로 지정합니다.
- ③ 날씨: 현장에서 가장 빈번하게 기록되는 '맑음'을 기본값으로 미리 입력해 두면 편리합니다.
- ④, ⑤ 요일 및 결재란: 이 부분은 현재 양식에서는 일단 빈칸으로 둡니다.
- ⑥ 특기사항: 그날 현장의 주요 이슈나 보고 사항을 적는 란입니다.
공사일보를 작성할 때 번거로운 작업 중 하나가 어제의 누계 데이터를 오늘 일보로 옮겨 적는 일입니다. 엑셀의 '이름 정의' 기능을 활용해 이 과정을 자동화해 보겠습니다. ⑧번 누계 데이터 영역을 완성하기 위한 사전 작업입니다.
상단 메뉴 탭에서 [수식] - [이름 관리자]를 클릭합니다. (단축키: Ctrl + F3) 이후 [새로 만들기] 버튼을 누르고 아래의 4가지 항목을 순서대로 정확히 등록해 주세요.
- 첫 번째 등록
- 이름: 시트목록
- 참조 대상: =GET.WORKBOOK(1)
- 두 번째 등록
- 이름: 시트명
- 참조 대상: =GET.CELL(32,INDIRECT(ADDRESS(ROW(),COLUMN())))
- 세 번째 등록 (앞선 두 가지가 먼저 등록되어 있어야 작동합니다)
- 이름: 시트위치
- 참조 대상: =MATCH(시트명,시트목록,0)+(0*NOW())
- 네 번째 등록
- 이름: 앞시트
- 참조 대상: =INDIRECT(ADDRESS(ROW(),COLUMN(),,,INDEX(시트목록,시트위치-1)))
4개를 모두 등록하셨다면 [닫기]를 누릅니다.
3. 4가지 이름 정의의 원리와 작동 흐름
위에서 입력한 수식들이 어떻게 연계되어 작동하는지 논리적인 흐름을 살펴보겠습니다. 이 시스템은 [기초 데이터 수집] ➡️ [위치 파악] ➡️ [값 호출]이라는 3단계로 이루어집니다.
- [기초 데이터 수집]: 시트목록은 현재 열려있는 파일의 모든 시트 이름을 배열로 추출합니다. 시트명은 현재 작업 중인 시트의 이름을 추출합니다. 두 수식은 별도의 텍스트 가공 없이 데이터 형식이 일치하도록 설계되어 있습니다.
- [위치 파악]: 시트위치는 앞서 구한 두 가지 데이터를 비교하여, 현재 시트가 전체 탭 중에서 몇 번째에 위치하는지 순번을 확인합니다. 수식 끝에 포함된 +(0*NOW())는 시트 복사나 이름 변경 시 엑셀이 수식을 강제로 재계산하도록 돕는 필수적인 장치입니다.
- [값 호출]: 마지막 앞시트 수식은 확인된 시트 순번에서 1을 빼 이전 시트의 위치를 특정한 뒤, 현재 수식이 입력된 셀과 동일한 좌표에 있는 이전 시트의 데이터를 최종적으로 불러옵니다.
4. 출력 인원 및 누계 수식 완성하기
이제 설정한 이름 정의 기능을 실제 표에 적용하여 누계 시스템을 완성할 차례입니다.
- ⑦ 공종명: 1번부터 44번까지 현장 관리에 필요한 각 공종의 이름을 폼에 미리 세팅해 둡니다.
- ⑧ 총 누계 수식 (I열):
=금일투입(G열) + IFERROR(앞시트, 0)수식을 입력합니다. 이렇게 설정하면 '앞시트' 수식이 이전 시트의 '총 누계'를 정확히 물고 오기 때문에, 과거 데이터 수정 시 현재까지 도미노처럼 완벽하게 자동 업데이트됩니다.
혹시 낮은 버전의 엑셀일 경우 오류가 발생하면 '@앞시트' 입력 해보시기 바랍니다.
- ⑨ 금일 출력 인원 및 조건부 서식 (시각적 강조): 현장 상황과 투입 인원에 맞춰 수기로 입력하는 공간입니다. 매일 수십 개의 공종 중에서 오늘 실제 투입된 공종만 한눈에 파악할 수 있도록 '조건부 서식'을 걸어주면 아주 편리합니다.
- 금일 투입량을 입력하는 셀 범위(예: G열)를 쭉 드래그하여 선택합니다.
- 상단 메뉴에서 [홈] - [조건부 서식] - [새 규칙] - [수식을 사용하여 서식을 지정할 셀 결정]을 클릭합니다.
- 수식 입력란에
=해당 첫 번째 셀>0(예를 들어 첫 셀이 G31이라면=G31>0)을 입력합니다.
- [서식] 버튼을 누르고 글꼴 스타일을 '굵게' 지정하거나 눈에 띄는 텍스트 색상으로 변경한 후 확인을 누릅니다.
- 이제 투입 인원이 '0'보다 크게 입력되는 순간, 해당 항목이 자동으로 진하게 표시되어 오늘 작업한 내용만 쏙쏙 들어오게 됩니다.
- ⑩ 총 누계 및 전일 누계 수식 (도미노 자동 업데이트 방식):
과거 시트의 오류를 수정했을 때 이후 시트까지 완벽하게 연쇄 수정되도록 역산 로직을 적용합니다.
- 전일 누계 (H열): 역산 개념을 적용하여
=총누계(I열) - 금일투입(G열)수식을 입력합니다. (예: =I31-G31) - 입력이 끝났다면 두 수식을 아래쪽 셀 방향으로 자동 채우기(드래그)하여 복사해 줍니다.
- 전일 누계 (H열): 역산 개념을 적용하여
여기서 작성하고 있는 '입력서식' 시트는 앞으로 매일 새로운 날짜의 일보를 생성할 때 사용하는 '원본 템플릿' 역할을 합니다.
- 따라서 작업 진행 중 새로운 공종이 추가된다면, 반드시 이 '입력서식' 시트에도 내용을 추가해야 내일 생성될 일보 시트에도 동일하게 반영됩니다.
5. 장비 현황 및 누계 수식 완성하기
- 장비 현황의 누계 수식 또한 출력인원의 ⑧ ~ ⑩ 의 누계수식과 같은 방식으로 수식을 입력합니다.
이로써 새로운 시트를 복사할 때마다 전날의 누계 값이 자동으로 연동되는 공사일보의 기본 틀과 수식 입력이 완료되었습니다.
다음 포스팅 예고
다음 연재에서는 오늘 만든 서식에 활용성을 더해줄 VBA 매크로 코드를 적용해 보겠습니다.
클릭 한 번으로 오늘 날짜의 시트를 자동 생성하는 버튼 기능부터, 시트 개수가 많아졌을 때 필요한 시트 검색 기능, 그리고 지정된 엑셀 셀 크기에 맞춰 사진이 자동으로 삽입되는 사진 대장 코드까지 다룰 예정입니다.
효율적인 공사일보 관리를 위한 다음 과정도 기대해 주시기 바랍니다.