Please Enable JavaScript!
Gon[ Enable JavaScript ]

엑셀 Excel 문의해결같은 날 중복 방문한 손님 제외 하고 날짜 별 방문 수 계산

 

환경: Microsoft Excel 365

 

토요일 블로그 방문자로부터 메일이 왔더군요. 제 블로그를 보시고 도움을 요청하셨는데 지나 칠 수 없어 답을 드렸습니다. 다른 분들도 참고할 만한 내용이라 풀이 과정을 올렸습니다. 문제 내용을 요약 하자면 다음과 같습니다. 회사 업무 중 일년에 손님이 몇 명 오는지 파악 하려고 하는데 날짜와 이름 별로 중복 데이터를 제외하고 하루 방문자 수가 얼마 인지 함수로 짜 달라는 것입니다.

 

그림이 계산해야 할 방문자 수가 있는 엑셀의 내용입니다. 방문일자와 조합원성명이 일치하는 중복 방문자가 있죠? 이것을 제외시킨 방문자 수만 세는 것입니다.

엑셀 Excel 문의해결 – 같은 날 중복 방문한 손님 제외 하고 날짜 별 방문 수 계산

 

결론부터 말하자면 함수 하나로 해결하지는 못했습니다. 두 번의 계산을 거쳐 결과를 도출했습니다. 제가 프로그래머 이다 보니 VBA 매크로로 짜면 간단할 것 같은데 초보라고 하셔서 좀더 쉽게 이해할 수 있도록 함수로 작성했습니다. 계산 과정에서 사용한 함수는 다음과 같습니다.

 

l  IF 함수 : 분기 함수 입니다.

l  COUNTIFS 함수 : COUNTIF 의 확장으로 조건이 여러 개 들어갑니다.

l  COUNTIF 함수 : 조건에 맞는 값의 개수를 세는 함수입니다.

l  SUMIFS 함수 : 조건에 맞는 값의 합계를 구합니다.

 

첫 번째 열을 하나 만들어서 반복 데이터를 체크했습니다. 반복이 되는 순간에 1 이라는 숫자를 찍게 됩니다. 반복 수를 세는 COUNTIFS 의 첫 번째 파라미터에는 방문일자 전체 영역을 넣고 두 번째 파라미터에 비교할 방문일자 셀을 넣습니다. 세 번째 파라미터에는 조합원 성명 영역을 넣고 네 번째에 비교할 조합원 셀을 추가합니다. 이렇게 해서 방문일자와 조합원성명이 일치하는 셀을 찾아 1보다 큰 경우 1을 찍습니다. 그럼 결과적으로 중복된 행에만 1이 찍히게 됩니다.

 

=IF(COUNTIFS($A$2:$A2,A2,$C$2:$C2,C2) > 1, 1,"")

엑셀 Excel 문의해결 – 같은 날 중복 방문한 손님 제외 하고 날짜 별 방문 수 계산

 

다음은 위에서 계산한 반복 데이터를 이용해서 방문 일자에 방문한 사람의 수를 세어 보겠습니다. 계산 방식은 해당 방문일자의 전체 방문수에서 반복 데이터의 합계를 빼는 것입니다. 다시 말해서 같은 이름과 날짜로 중복 방문한 데이터는 빼는 것이죠. 그럼 중복 방문한 것과 상관없이 몇 명 방문했는지 나옵니다.

 

=COUNTIF($A$2:$A$25,A28) - SUMIFS($G$2:$G$25,$A$2:$A$25,A28)

 

COUNTIF($A$2:$A$25,A28) 가 방문일자별 합계값이며 SUMIFS($G$2:$G$25,$A$2:$A$25,A28) 가 방문일자별 반복데이터 합계가 됩니다. 계산 결과는 다음과 같습니다

엑셀 Excel 문의해결 – 같은 날 중복 방문한 손님 제외 하고 날짜 별 방문 수 계산


Posted by 녹두장군

댓글을 달아 주세요

  1. Favicon of https://anunmankm.tistory.com 버크하우스 2014.09.20 21:19 신고  댓글주소  수정/삭제  댓글쓰기

    잘 보고 갑니다. 좋은 하루 되시길요. ^^

  2. 주디 2017.02.04 02:11  댓글주소  수정/삭제  댓글쓰기

    잘봤습니다..혹시 같은날짜에 같은 이름이 있는 줄만 색칠하고싶으면 어찌해야할까요?(방문일자와 조합원성명 중복되는줄만)
    댓글로 묻기에는 어려운 내용일까요? 위 첫번째 비슷하게 해볼래도 잘 안되네용~

  3. 헬프 2020.11.30 19:48  댓글주소  수정/삭제  댓글쓰기

    구세주씨가 한 달에 몇 번 방문했는지는 어떻게 셀 수 있을까요?ㅠㅠ 도와주세요.