회사에서 엑셀 파일로 여러 사람과 함께 작업하다 보면, "분명 완료된 데이터인데 누군가 값을 변경"하는 작은 실수로 보고서 전체가 엉키는 문제를 종종 겪게 됩니다. 물론 시트 보호나 파일 암호화 기능이 있지만, 수십 명이 함께 보는 파일에 매번 암호를 걸고 해제하며 공유하면 업무 생산성이 크게 저하되는 문제가 있습니다.
완료를 체크하면 수정을 막을 수 있는 서식을 활용해보세요!👍
이번 글에서는 '완료'만 체크하면 선택한 값은 자동으로 수정이 제한되고 '완료 시간'까지 자동으로 기록되는 보고서를 1분 안에 만드는 방법을 정리합니다.
- 완료 목록상자 만들기 : 먼저 "완료"를 체크하는 목록 상자를 만들어보겠습니다. 예제파일에서 완료를 입력할 범위인 D3:D16 범위를 선택한 후, [데이터] 탭 - [데이터 유효성 검사]를 실행합니다.
완료 목록을 적용할 범위를 선택하고 데이터 유효성 검사로 이동합니다.
- 데이터 유효성검사 창이 나타나면, [설정] 탭의 제한 대상으로 '목록'을 선택하고 원본 입력창에 완료라고 입력합니다. (다른 항목도 필요하다면 쉼표로 구분하여 입력할 수 있습니다. 예: 진행중,완료,보류 등..)
제한 대상으로 목록을 선택하고 입력창에 완료를 입력합니다.
- [확인] 버튼을 클릭하면 완료를 선택하는 목록상자가 완성됩니다.
완료를 선택하는 목록상자가 완성됩니다.
- 이제 보고서의 핵심 기능인, '완료'를 선택한 순간 데이터 수정이 방지되는 기능을 적용하겠습니다. 먼저 보호하고 싶은 데이터가 입력된 범위를 선택합니다. 예제파일에서는 수수료가 입력된 C3:C16 범위를 선택합니다.
보호하고 싶은 데이터를 선택한 후, 데이터 유효성 검사를 실행합니다.
- 범위를 선택한 상태에서 [데이터] - [데이터 유효성 검사]를 실행한 후, 제한대상으로 '사용자 지정'을 선택합니다. 이후 입력창에 다음과 같이 수식을 작성합니다.
$D3<>"완료"
· D3셀은 완료 체크를 확인할 범위의 시작셀을 입력합니다.
· 셀 주소에서 알파벳 앞에만 $기호를 붙이는 것을 주의하세요!
· $기호 (셀 참조)로 전체 행에 서식을 적용하는 방법은 아래 관련 강의에서 알기 쉽게 정리했으니 참고하세요!👇
제한 대상으로 사용자 지정을 선택하고 수식을 입력합니다.
- 이제 [오류 메시지] 탭을 클릭한 후, 사용자에게 표시할 오류 문구를 작성합니다.
제목 : 수정 불가
메시지 : 지급 완료된 항목은 수정할 수 없습니다.
오류 메시지 탭에서 사용자에게 표시할 오류 문구를 작성합니다.
- 이제 지급이 완료된 항목의 수수료를 변경하면 오류 메시지가 나오면서 수정이 제한되는 보고서가 완성됩니다.
지급 완료된 항목을 수정하면 안내창이 나오면서 수정이 제한됩니다.
- 마지막으로 '완료'를 체크한 순간의 시간을 기록해보겠습니다. 예제파일에서 시간을 기록할 범위의 첫번째 셀인 E3셀에 아래 공식을 입력합니다.
=IF(셀<>"",IF(INDIRECT("R"&ROW()&"C"&COLUMN(),0)="",NOW(),INDIRECT("R"&ROW()&"C"&COLUMN(),0)),"")
시간을 기록학 셀에 공식을 입력합니다.
- 공식에서 "셀"을 시간 기록을 확인할 셀 주소로 변경합니다. 예제에서는 D3셀로 변경합니다.
공식에서 '셀'을 시간 기록을 동작할 셀 주소로 변경합니다.
- 자동채우기로 수식을 입력한 후, '완료'를 체크하면 완료가 체크된 시간이 기록됩니다.
완료를 체크하면 시간이 기록됩니다.
- 위 시간 기록 공식을 넣은 후, 완료를 체크했을 때 아래 그림과 같이 순환참조 오류가 나올 수 있습니다.
완료를 체크하면 순환 참조 오류가 발생할 수 있습니다.
- 이 경우 [파일] 탭 - [옵션] - [수식] 에서 '반복 계산 사용'을 체크하면 순환 참조 오류가 해결됩니다.
순환 참조 오류가 발생할 경우, 수식 - 반복 계산 사용을 체크합니다.
