엑셀 체크박스 만들기, 두 단계 점검표와 수식 연결

엑셀 체크박스는 Microsoft 365의 셀 체크박스 기능을 사용할 수 있다면 범위를 선택하고 삽입 탭의 확인란을 눌러 만들 수 있다. 체크한 셀은 TRUE, 해제한 셀은 FALSE 값을 갖기 때문에 다른 셀의 수식에서 바로 참조할 수 있다. 기존 개발 도구의 양식 컨트롤과는 설정 방식이 다르다.

단순히 네모를 넣는 데서 끝내지 않고, 파일 점검표의 준비·검토 상태를 구분하는 예제를 만들어 본다. 예제의 항목과 상태는 설명용으로 만든 가상 데이터이며 실제 업무 파일이나 프로그램 실행 결과를 재현한 것은 아니다.

엑셀 체크박스 메뉴부터 구분한다

먼저 삽입 탭에 셀 체크박스에 해당하는 확인란 메뉴가 있는지 살펴본다. Microsoft 공식 도움말이 안내하는 적용 대상은 Microsoft 365용 Excel과 Mac용 Microsoft 365 Excel이다. 메뉴가 없다고 해서 개발 도구를 켜면 동일한 기능이 생기는 것은 아니다.

이 글의 실습은 셀 체크박스를 기준으로 한다. 메뉴가 없는 환경에서는 사용 중인 제품과 기능 지원 여부부터 확인한다. 화면에 보이는 체크 표시 기호를 입력하는 것 역시 클릭으로 상태를 바꾸는 체크박스와 다르다. 기호를 넣고 TRUE·FALSE 값이 자동 생성되기를 기대하면 수식이 맞지 않는다.

엑셀 체크박스를 두 열에 넣어 점검표 만들기

가상 점검표는 파일이 준비됐는지와 내용을 검토했는지를 별도로 관리한다. 한 칸에 ‘완료’만 표시하면 어느 단계가 끝났는지 알기 어렵기 때문이다. A열에는 항목, B열에는 준비, C열에는 검토, D열에는 상태를 둔다.

1. A2부터 A4까지 ‘안내문’, ‘참고 자료’, ‘제출 파일’을 입력한다.
2. B2:C4의 빈 셀 범위를 선택한다.
3. 삽입 탭의 확인란을 선택한다.
4. B2와 C2를 각각 눌러 상태를 바꿔 본다.
5. 다음 절의 수식을 D2에 넣고 D4까지 복사한다.

실제 데이터가 없는 전체 열에 체크박스를 넣기보다 사용할 범위부터 정한다. 준비 열과 검토 열이 서로 다른 판단을 담고 있다는 점도 표 머리글에 적는다. 두 칸의 뜻이 불분명하면 수식은 맞아도 사용자가 서로 다르게 체크할 수 있다.

준비 전인데 검토만 체크했다면 어떻게 표시할까?

두 값이 모두 TRUE일 때만 ‘완료’로 표시할 수 있다. 여기에 준비가 FALSE인데 검토만 TRUE인 조합을 따로 표시하면, 단계가 뒤바뀐 입력을 찾는 데 도움이 된다. 아래 수식은 이 글에서 만든 가상 점검 순서를 표현한 예시다.

=IF(AND(NOT(B2),C2),"순서 확인",IF(AND(B2,C2),"완료",IF(B2,"검토 대기","준비 전")))

수식을 넣었다면 네 조합을 차례로 확인한다. ‘완료’ 한 경우만 시험하면 다른 조합에서 원하는 결과가 나오는지 알 수 없다. 이 표는 실제 Excel에서 실행한 측정 결과가 아니라 논리식에 따른 예상 결과이며, 자신의 파일에서도 입력을 바꿔 대조한다.

B2와 C2가 체크박스 셀인 경우 해당 셀을 바로 참조한다. 기존 양식 컨트롤을 쓰는 파일에서는 연결 셀을 확인한 다음 수식의 참조를 바꿔야 한다. 다른 행의 컨트롤이 같은 연결 셀을 가리키면 독립된 상태를 읽지 못할 수 있다.

조건문이 낯설다면 엑셀 IF 함수의 논리식 설명과 함께 읽는다. 이 예제에서는 실제 제출이나 승인을 자동 처리하지 않고 표의 상태 문구만 바꾼다.

여러 체크박스의 완료 개수를 셀 때 범위를 맞춘다

위 예제처럼 D열에 상태를 계산했다면 완료 항목 수는 D2:D4에서 ‘완료’를 센다. 준비만 체크된 항목과 두 단계가 끝난 항목을 섞어서 세지 않도록 집계할 기준을 정한다.

=COUNTIF(D2:D4,"완료")

가령 안내문과 참고 자료는 두 칸 모두 체크하고 제출 파일은 준비만 체크했다면 예상 완료 수는 2다. 제출 파일의 검토도 체크하면 3으로 바뀌어야 한다. 수식을 복사한 뒤 각 행의 참조가 B3·C3, B4·C4로 이동했는지도 확인한다.

항목을 추가했는데 상태 수식을 복사하지 않거나 집계 범위를 늘리지 않으면 새 행은 빠질 수 있다. 체크 표시가 보이는지와 집계에 포함됐는지는 별도로 점검한다. 숫자가 예상과 다를 때는 함수를 바꾸기 전에 실제 항목 범위, 상태 수식, 집계 범위를 나란히 대조한다.

엑셀 체크박스 삭제와 서식 지우기는 결과가 다르다

체크박스가 있는 셀 범위에서 Delete를 누를 때는 현재 상태에 따라 먼저 체크가 해제될 수 있다. 공식 안내는 선택한 체크박스가 모두 해제된 상태라면 삭제되고, 체크된 항목이 있었다면 해제 후 다시 Delete를 눌러 제거할 수 있다고 설명한다. 실제 점검표에서 삭제를 연습할 때는 복사본을 사용한다.

모양만 없애고 TRUE·FALSE 값은 남기려면 홈 → 지우기 → 서식 지우기를 사용한다. 다만 이 명령은 체크박스 모양만이 아니라 선택 셀의 다른 서식도 지운다. 값이 남는다는 점과 표의 색·테두리 같은 서식에 영향을 줄 수 있다는 점을 함께 이해해야 한다.

서식을 지운 뒤 TRUE와 FALSE가 보이는 것은 값이 남아 있기 때문이다. 기존 수식이 그 값을 참조하고 있다면 모양을 없앤 것만으로 집계가 사라지는 것은 아니다. 반대로 값을 지워도 되는지는 표의 사용 목적에 따라 판단한다.

엑셀 체크박스 설정을 마치면 네 가지 입력 조합, 상태 문구, 완료 개수, 새 행 추가 후 범위를 확인한다. 표를 잠가 배포해야 한다면 엑셀 시트 보호에서 편집 허용 범위도 함께 검토한다. 체크박스를 넣는 작업보다 각 체크의 의미와 계산 결과를 맞추는 작업이 점검표의 완성도를 결정한다.

출처

1. Microsoft — 셀 체크박스 사용
2. Microsoft — 양식 컨트롤
3. Microsoft — IF와 AND·OR·NOT 조합
4. Microsoft — COUNTIF 함수