엑셀을 이용하여 회사의 목표를 달성하기 위한 KPI 진척도 또는 일별 업무의 성과 여부의 진척율을 구해야 할 경우, 엑셀 체크박스와 COUNTIF 함수를 사용하면 시인성이 높은 진척 상황 추척 파일을 만들 수 있습니다.
엑셀 체크박스를 사용하는 방법
엑셀 체크박스는 손쉽게 사용할 수 있는 엑셀의 기능 중 하나입니다. 기본 엑셀을 사용하는 방법과 무료 엑셀을 사용하는 방법 2가지를 알아보겠습니다.
기본 엑셀 체크 박스 (진척율)
아래와 같이 일별의 업무 달성 여부를 표시하는 테이블을 만들려고 합니다.
진척도를 알아보는 방법은 크게 두가지를 나뉩니다.
- “O” 와 “X” 를 사용하여 달성 여부를 기록하는 방법입니다. (아래 사진의 첫번째 방법)
- 엑셀 체크 박스를 이용하여 손쉽게 달성여부를 기록하는 방법입니다. (아래 사진의 두번째 방법)
엑셀 체크박스 자동 셀 연결 방법
엑셀 개발도구를 설정하는 방법, 체크박스를 셀안에 넣는 방법, 체크박스의 값을 지정하는 방법 그리고 매크로를 이용하여 체크박스의 값을 자동으로 지정하는 방법에 대해 알아보겠습니다.
엑셀 개발도구 설정 방법
체크박스를 엑셀 셀안에 넣기 위해서는 우선 “개발도구 = Developer”를 활성화 시켜야 합니다.
엑셀 기능 추가 방법
엑셀에서 체크박스 기능을 사용하기 위해 엑셀의 리본메뉴에 개발도구를 추가 하는 방법은 여러가지가 있습니다.
- 엑셀 상단에 빈 공간에 마우스 커서를 둔 뒤 오른쪽 클릭 이후 [리본 메뉴 사용자 지정]을 선택하는 방법이 있습니다.
- [파일], [옵션], [리본 메뉴 사용자 지정] 을 선택하는 방법이 있습니다.
- […] 3개의 점이 있는 리본메뉴 상단을 클릭 후 [리본 메뉴 사용자 지정] 을 선택하는 방법이 있습니다.
이번에는 맥북 엑셀 유저를 위한 엑셀 메뉴 추가 방법에 대해 알아보겠습니다.
우선 […] 3개 점을 클릭하여 줍니다. 이후 아래의 화면에서 [more commands…] 을 클릭하여 줍니다.
아래와 같이 [Ribbon] 과 [Quick Access Toolbar] 의 기능이 활성화 되며, 메인 메뉴를 추가 하기 위해 [Ribbon]을 선택합니다.
[Main tabs] 에서 개발도구를 선택하여 주면, 엑셀 체크박스를 사용할 수 있습니다.
엑셀 셀안에 체크박스 넣는 방법
엑셀 개발도구를 선택 후 체크박스를 선택합니다. 이후 원하는 곳에 마우스 좌클릭을 하면 아래와 같이 체크박스가 생성됩니다.
엑셀 셀 안에 체크박스를 삽입하기 위해서 불필요한 문구는 삭제한 뒤 셀 안에 체크박스가 들어갈 수 있도록 마우스로 크기를 조정하여 줍니다.
셀 안에 체크박스를 넣은 뒤 해당셀을 복사 붙이기 하면 원하는 만큼 체크박스를 확장 할 수 있습니다.
엑셀 진척율 구하는 방법
엑셀 진척도를 계산하는 방법의 핵심은 “체크박스를 셀에 연결”하는 것입니다. 각각의 체크박스를 수동으로 엑셀의 셀과 연결하는 방법과, 엑셀 매크로를 이용한 방법 두 가지로 나뉩니다.
엑셀 체크박스 셀 연결 (수동)
진척율을 계산하기 위해서는 표시된 박스와 셀을 연결하여 값을 도출하여야 합니다. 아래와 같이 체크박스를 마우스로 우클릭한 뒤 [Format control] 을 선택합니다.
원하는 엑셀 셀을 입력하여 체크박스의 선택에 따라 값이 나올 수 있도록 합니다.
위와 같이 체크박스와 연결할 셀의 위치를 입력하면, 아래와 같이 셀의 선택에 따라 [TRUR or FALSE] 로 값이 연결됩니다.
엑셀 진척율 데이터의 시인성을 높이기 위해 글자색을 셀의 바탕 색상과 동일하게 설정합니다.
이후 그 다음 체크박스도 셀과 연결하기 위하여 위와 같은 과정을 반복합니다.
모든 체크박스를 셀과 연결하면 아래와 같이 표시된 박스들을 기준으로 진척율을 확인할 수 있습니다.
엑셀 진척율을 구하는 또다른 중요 기능은 [COUNTIFS] 함수의 사용입니다.
사용된 엑셀 함수 수식은 아래와 같습니다.
L5 Cell =COUNTIF(C5:K5,TRUE)/COUNTA(C5:K5)
엑셀 체크박스 셀 연결 (엑셀 매크로)
위에서는 수동으로 체크박스와 셀을 연결하는 방법에서 알아보았습니다. 이번에는 엑셀 매크로 기능을 활용하여 아래와 같이 수동으로 연결하기에는 생산성이 떨어지는 경우에 활용할 수 있는 방법을 알아보겠습니다.
우선 [L column] 에는 아래의 수식으로 변경합니다.
L5 Cell =COUNTIF(C5:K5,TRUE)/9
위의 수식에 9는 체크박스의 개수 입니다.
엑셀 매크로를 사용하기 위해 아래와 같이 선택합니다.
엑셀 매크로 이름을 설정하는 방법은 띄어쓰기 없이 이름을 설정하는 것입니다.
엑셀 VBA 에 아래와 같이 입력합니다.
아래의 엑셀 VBA 명령문을 사용하면, 체크박스와 엑셀 셀이 자동으로 연결됩니다.
Sub auto()
Dim chk As CheckBox
Dim Ws As Worksheet
Set Ws = ActiveSheet
For Each chk In Ws.CheckBoxes
With chk
.LinkedCell = _
.TopLeftCell.Address
End With
Next chk
End Sub
엑셀 매크로 설정이 완료되면 개발도구의 버튼을 사용하여 매크로 실행을 할 수 있는 버튼을 삽입합니다.
삽입 할 버튼에 위에 만든 매크로 [auto] 를 연결합니다.
아래와 같이 매크로 실행 버튼을 삽입 후 실행하면, 모든 체크박스가 각각의 셀에 자동으로 연결되어 모든 열의 진척율을 계산할 수 있습니다.
무료 엑셀 사용(체크박스 셀 연결)
엑셀 무료로 사용할 수 있는 방법인 [Google Sheets] 를 활용하여 체크박스와 엑셀의 셀을 자동으로 연결하여 사용합니다.
구글 시트를 활용하면 위의 모든 과정은 필요가 없습니다.
이유는 체크박스 삽입 시 칸에 자동으로 맞추어지며, 자동으로 체크박스가 해당 셀과 연결된 상태로 설정되기 때문입니다.
사용하는 방법에 대해서 알아보겠습니다.
구글 시트의 삽입 탭에서 체크박스를 선택합니다.
원하는 셀에 체크박스를 아래와 같이 삽입 후 표시해 주면, 아래와 같이 엑셀 진척율을 확인할 수 있습니다.
결론
빅 데이터를 기준으로 위의 기능을 활용하고자 한다면 엑셀 오피스 사용을 추천해 드리며, 빅 데이터 없이 진척율만을 확인하기 위함이라면 구글 시트 사용을 추천해 드립니다.