엑셀 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 가장 기본이 되는 함수 사용법에 알아보기 ▶ 엑셀 Excel 수식 계산을 위한 절대 참조와 상대 참조 이해하기 ▶ 엑셀 Excel 같은 열에 있는 데이터 다시 입력하기 ▶ 엑셀 Excel 셀에 입력된 데이터가 수식인지 값인지 구분하기 ▶ 엑셀 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 가장 기본이 되는 함수 사용법에 알아보기 ▶ 엑셀 Excel 수식 계산을 위한 절대 참조와 상대 참조 이해하기 ▶ 엑셀 Excel 같은 열에 있는 데이터 다시 입력하기 ▶ 엑셀 Excel 셀에 입력된 데이터가 수식인지 값인지 구분하기 ▶ 엑셀 Excel 간단하게 수식 만들기