엑셀 체크박스로 완료 개수와 업무 완료율 계산하기

체크박스가 있는 업무표에서 수식이 읽는 것은 체크 그림이 아니라 셀에 저장된 값이다. 새 셀 체크박스라면 그 셀을 바로 COUNTIF에 넣을 수 있고, 예전 양식 컨트롤이라면 먼저 셀과 연결해야 한다. 완료 건수가 맞게 나온 다음에는 그 숫자를 무엇으로 나눌지 정한다. 완료된 칸만 정확하게 세어도 분모에 빈 업무 행이 들어가면 비율은 낮아진다.

Microsoft Q&A에는 네 개의 체크박스 중 두 개를 선택했을 때 50%를 표시하고 싶다는 질문이 있다. 2023년 답변은 각 상자의 연결 셀에 TRUE/FALSE를 남긴 뒤 그 값을 계산하도록 안내한다. 네 상자가 모두 해야 할 일이라면 전체를 세면 된다. 업무가 늘어날 것에 대비해 빈 행에도 상자를 넣어 둔 표에서는, 아직 일이 없는 행을 계산에서 빼야 한다. 체크박스 비율에 관한 실제 질문과 답변

셀 체크박스와 예전 양식의 연결 셀

Windows용 Microsoft 365 Excel에서 삽입 → 체크박스 메뉴를 사용할 수 있다면, 빈 C2:C11 범위를 선택해 한 번에 넣을 수 있다. 이 방식은 셀의 TRUE/FALSE 값에 체크박스 모양을 적용한다. 선택한 셀의 값이 수식으로 전달되므로 별도의 연결 셀을 만들지 않는다. Microsoft의 셀 체크박스 사용법

기존 업무표를 받은 경우에는 상자를 어떤 방법으로 만들었는지부터 살핀다. 개발 도구 → 삽입 → 양식 컨트롤에서 넣은 상자는 셀 위에 놓이는 개체다. C2 위에 그려져 있다고 해서 C2에 완료 값이 저장되는 것은 아니다. 오른쪽 클릭 메뉴에 ‘컨트롤 서식’이 보이는 상자라면 이 방식일 수 있다. Excel 양식 컨트롤 안내

양식 컨트롤의 ‘컨트롤 서식’에서 컨트롤 탭을 열고 셀 연결에 C2처럼 결과를 받을 주소를 지정한다. 다음 업무의 상자는 C3에 연결한다. 각 상자를 체크했다가 해제해 연결 셀이 TRUE와 FALSE로 바뀌는지 본다. 연결할 칸에 기존 업무명이나 다른 수식이 들어 있다면 덮어쓰지 않도록 빈 보조 열을 사용한다.

이전 상자를 복사해 늘렸다면 두 개를 따로 눌러 서로 다른 연결 셀이 바뀌는지도 확인한다. 체크박스 두 개가 같은 셀을 사용하면 서로 다른 업무의 완료 여부를 저장할 자리가 하나뿐이다. 외형을 정렬하기 전에 한 행의 업무와 한 칸의 상태가 대응하도록 맞춰야 건수 계산도 의미가 있다.

새 삽입 메뉴가 보이지 않는다면 지금 받은 표가 양식 컨트롤을 사용하는지도 살핀다. 그 표를 계속 쓸 때는 각 상자의 연결 셀을 수식에 넣으면 된다. 아래 새 목록 예제는 셀 체크박스를 넣을 수 있는 Windows Microsoft 365 환경을 기준으로 한다.

체크한 개수부터 맞추기

업무명은 A열, 담당자는 B열, 완료 체크박스는 C열에 둔다. 머리글은 1행이고 입력 범위는 2~11행이라고 가정한다. 다음은 설명을 위한 업무 5개의 예시다. 7~11행은 업무명이 비어 있고 체크박스도 해제된 상태다.

C열에서는 위 값이 상자의 선택 상태로 보인다. 집계 칸을 별도로 골라 아래 수식을 넣으면 선택한 상자는 3개로 계산된다. COUNTIF는 지정한 범위에서 조건을 만족하는 셀을 세는 함수이며, 여기서는 조건에 논리값 TRUE를 사용한다. COUNTIF 구문과 조건 사용법

=COUNTIF(C2:C11,TRUE)

이 수식은 특수문자 ‘✓’나 ‘완료’라는 글자를 세지 않는다. 실제 체크박스의 TRUE 값을 세므로, 집계가 0이라면 상자 하나를 눌러 참조한 셀의 값이 달라지는지 본다. 문자 표시를 쓰는 별도 목록이라면 그 문자에 맞는 조건이 필요하다.

예시에서 C4를 체크하면 건수는 4, 다시 해제하면 3이어야 한다. 이 한 건의 변화를 먼저 대조하면 넓은 범위를 한꺼번에 수정하지 않고도 연결 셀이나 수식 범위를 찾을 수 있다. 수식은 체크박스를 넣은 C열 안이 아니라 오른쪽 집계 영역에 둔다.

빈 업무 행을 뺀 완료율

C2:C11에 상자를 10개 넣어 두고 그중 3개를 체크했다면, 상자 전체를 분모로 삼은 비율은 30%다. 그러나 실제로 적은 업무가 5개이고 그중 3개를 마쳤다면 업무 완료율은 60%다. 앞으로 쓸 다섯 행을 지금 해야 할 일로 포함했는지가 두 결과의 차이다.

미선택 상자도 FALSE라는 값이 있으므로 COUNTA(C2:C11)는 10을 센다. COUNTA는 비어 있지 않은 셀을 세며 FALSE도 포함한다. 따라서 업무명 5개를 직접 입력한 이 예제에서는 A열을 전체 업무 수의 기준으로 삼는다. COUNTA가 세는 값

E2에 ‘전체 업무’, F2에 아래 수식을 넣는다. A2:A11에는 업무명과 실제 빈 셀만 둔다. 공백 한 칸이나 빈 문자열을 반환하는 수식, 오류 값도 COUNTA에 포함되므로 겉으로 빈 것처럼 보이는 이름을 그대로 두면 안 된다.

=COUNTA(A2:A11)

분모를 업무명 열로 바꿨는데도 전체 업무가 6개로 나온다면, 다음에 쓸 A7을 살펴본다. 이 예시의 실제 업무는 여전히 5개이고 C7은 체크하지 않은 상태다. A7에 스페이스 한 칸이나 ="" 수식이 들어 있으면 업무명이 보이지 않아도 COUNTA는 그 셀을 하나로 센다. 완료한 3건은 그대로인데 나눌 수가 6이 되어 비율이 50%로 낮아지는 것이다.

A7을 선택해 수식 입력줄을 보고, 비어 있는 별도 집계 칸에서 =COUNTA(A7)도 확인할 수 있다. 실제 빈 셀은 0, 위 공백과 빈 문자열 수식은 1이다. 앞으로 쓸 빈 행임을 확인했다면 A7의 내용을 지우고 전체 업무 수가 5로 돌아오는지 본다. 여러 행을 한꺼번에 지우면 실제 업무명이나 필요한 수식까지 잃을 수 있으므로 의심한 칸부터 대조한다. 업무명을 수식으로 불러오는 표라면 그 수식을 지워 맞추지 말고, 업무가 없는 행을 전체 건수에서 어떻게 제외할지 별도로 설계해야 한다.

분자도 같은 업무 목록에서 세어야 한다. 업무명 A7이 실제로 비어 있는데 C7을 실수로 체크하면 단순 COUNTIF는 4를 센다. 업무명이 있고 완료 값이 TRUE인 행만 세도록 두 조건을 넣으면 그 체크는 제외되고 완료 업무는 3개다. E3에 ‘완료 업무’, F3에 다음 수식을 넣는다. COUNTIFS의 모든 조건 범위는 같은 크기로 맞춘다. 여러 조건을 함께 세는 COUNTIFS

=COUNTIFS(A2:A11,"<>",C2:C11,TRUE)

"<>"는 여기서 업무명이 비어 있지 않은 행을 고르는 조건이다. 이름 없는 체크를 완료 업무에 넣지 않는 것은 이 업무표의 집계 규칙이다. 완료한 일을 지웠다가 빈 행의 체크만 남은 경우에도 왜 숫자가 달라지는지 설명할 수 있다. 업무를 취소한 이력을 남겨야 한다면 이름을 지우기보다 별도의 취소 상태를 두는 표가 더 적합하다.

업무가 0개일 때와 백분율 표시

F2가 전체 업무 수, F3가 완료 업무 수라면 F4에서 =F3/F2로 나눈다. 새 주간 목록처럼 업무가 하나도 없을 때는 나눗셈 대신 빈칸을 보여 주도록 IF를 넣는다. 이 예제에서는 업무를 등록하기 전에는 비율을 표시하지 않고, 첫 업무명을 입력한 뒤부터 완료율을 보여 준다. IF의 조건별 결과 반환

=IF(F2=0,"",F3/F2)

업무 5개 중 3개 완료일 때 계산값은 0.6이다. F4를 선택하고 홈 탭의 백분율 스타일을 적용하면 60%로 보인다. 수식에서 100을 곱할 필요는 없다. 이미 60으로 만든 값에 백분율 서식을 적용하면 6000%가 된다. Excel 백분율 계산과 표시

이 비율은 업무 건수 기준이다. 자료 취합 한 건과 며칠 걸리는 검토 한 건을 똑같이 1건으로 센다. 소요 시간이나 난이도를 반영한 프로젝트 진척률로 보고하려면 업무별 가중치를 따로 정해야 한다. 체크박스가 있다고 그 기준까지 자동으로 생기는 것은 아니다.

담당자별 완료 건수와 상태 문구

담당자별로 보려면 같은 행의 B열 조건을 추가한다. H2에 담당자 이름 ‘민지’를 입력했다면, 업무명이 있고 담당자가 H2와 같으며 완료된 행은 다음 수식으로 센다. 예시에서는 자료 취합과 초안 작성의 2건이다.

=COUNTIFS(A2:A11,"<>",B2:B11,H2,C2:C11,TRUE)

민지의 전체 업무는 아래 수식으로 구하면 3건이다. 담당자별 완료율은 완료한 2건을 맡은 3건으로 나눠 구한다. 팀 전체 5건으로 나눈 40%는 민지가 끝낸 일이 팀 전체 업무에서 차지하는 비중이다. 담당자 이름 끝에 공백이 붙거나 표기가 다르면 같은 사람을 다르게 집계할 수 있으므로 입력 이름도 맞춘다.

=COUNTIFS(A2:A11,"<>",B2:B11,H2)

상자만으로 상태를 읽기 어렵다면 D열에 설명을 붙일 수 있다. D2에 아래 수식을 넣고 D11까지 복사한다. 이름 없는 예약행은 빈칸으로, 이름이 있는 업무는 완료 또는 미완료로 표시한다. C열은 TRUE/FALSE 입력을 유지하고 D열을 읽기용으로 쓰는 구성이다.

=IF(A2="","",IF(C2,"완료","미완료"))

업무가 늘어 12행 이후까지 쓰기 시작하면 위 수식의 범위도 함께 늘린다. 업무명 범위만 늘리고 완료 범위를 11행에서 끝내면 COUNTIFS의 범위 크기가 달라져 계산에 오류가 난다. 새 행에 업무명을 쓰고 상자를 체크했을 때 전체 건수와 완료 건수가 각각 한 건씩 늘어나는지 확인한다.

공유할 때 값과 체크박스 모양 확인하기

동료에게 넘길 때는 계산 결과만 보이는지와 직접 체크를 바꿀 수 있는지를 따로 확인한다. 상대 화면에 TRUE/FALSE만 나온다면 체크박스 모양을 표시하는 환경인지 살피고, 원본의 값이 없어졌다고 곧바로 판단하지 않는다. 상대가 업무를 갱신해야 하는 표라면 같은 파일의 복사본으로 한 항목을 바꾸고 집계까지 달라지는지 확인한다.

특히 개발 도구의 구형 양식 컨트롤은 Microsoft가 웹 Excel의 편집 제약을 안내한다. 지원하지 않는 개체를 제거하고 브라우저 편집을 진행하면 상자가 사라질 수 있으므로 기존 양식을 받았을 때는 데스크톱에서 여는 방법을 먼저 검토한다. 이 제한을 새 셀 체크박스까지 웹에서 모두 사용할 수 없다는 뜻으로 넓혀 해석하지 않는다.

제출용 사본에서 체크박스 모양만 없애려면 새 셀 체크박스의 범위를 선택하고 홈 → 지우기 → 서식 지우기를 사용할 수 있다. TRUE/FALSE는 남지만 그 범위의 다른 서식도 함께 지워진다. Delete는 같은 동작이 아니다. 선택된 상자가 있으면 먼저 체크가 해제되고, 모두 해제된 상태에서 누르면 상자가 제거되므로 완료 기록을 보존하는 용도로 쓰지 않는다.

공유 전 예시처럼 완료 3건, 전체 5건을 직접 세어 비율 60%와 대조한다. 빈 업무 행 하나를 체크해도 완료 업무 수는 늘지 않는지, 실제 업무 하나를 더 완료하면 4건으로 바뀌는지도 본다. 동료가 표를 이어 쓸 때도 이 두 가지 변화를 확인하면 새 업무가 집계에 들어가는지, 빈 행이 잘못 포함되는지를 직접 판단할 수 있다.