구글시트 QUERY: DATEDIF 와 VLOOKUP을 사용해서 거래처 미방문일수 계산하기

영업이 중심이 되는 산업에 종사하고 계시다면, 거래처에 언제 연락했고 방문했는지를 기록으로 남겨두면서 언제쯤 다시 방문하면 좋을지 체크해야할 필요성이 있으시죠?

영업일지를 작성하면, 자동으로 거래처 목록에 최종방문일자 또는 미방문일수를 표시하도록 만들 수 있습니다.

datedif 와 vlookup을 사용해서 표시하는 방법을 알아보겠습니다.

일반적으로 영업일지를 작성하는 시트와 거래처 시트는 따로 관리하는데, 설명을 간결하게 하기 위해서 한 페이지에서 작업하겠습니다.

왼쪽에는 영업일지, 오른쪽에는 거래처 시트라고 하겠습니다. 다른 정보들이 있을 수 있지만 설명의 초점인 영업일자와 미방문일수만 써두었습니다. 영업일지에는 영업내용, 담당자 등이 들어갈 수 있고, 거래처시트에는 거래처 전화번호, 담당자명, 주소, 사업자등록번호 등 다양하게 들어갈 수 있겠지요.

이제 거래처시트(화면의 오른쪽 부분)에서 미방문일수를 계산하는 방법을 살펴보겠습니다.

왼쪽의 영업일지에는 계속 영업일자와 거래처가 누적이 됩니다.

미방문일수를 계산하기 위해서는 특정 거래처의 가장 마지막 영업일자를 찾아서 오늘과 비교해주어야 합니다.

가장 마지막 영업일자를 찾는 것이 키인데, 단순히 VLOOKUP만 사용해서는 원하는 값을 찾을 수 없습니다.

예를 들어 VLOOKUP의 정렬을 FALSE로 해보겠습니다.

거래처1의 영업일자가 3개가 있는데, 그중에 가장 위의 값을 가져옵니다.

그렇다고 정렬을 TRUE 한다고 해서, 올바른 값을 찾아주지도 않습니다. 애매하게 중간에 있는 값을 가져왔습니다. 이렇게 가져오는 이유는 TRUE는 VLOOKUP 에서 첫번째 열이 정렬이 되어있을 때에 사용하는 것이기 때문입니다.

단순히 VLOOKUP으로 마지막 영업일자를 찾을 수 없었는데, 여기서 QUERY가 필요하게 됩니다.

이제 옆에 QUERY로 목록을 하나 만들텐데, 거래처를 첫번째 열로, 영업일자를 두번째 열로 불러오면서 거래처와 방문일자를 정렬해보겠습니다.

A1:B11 에서 열을 선택하는데, 거래처를 먼저 표시하고 영업일자를 다음 열에 표시할 것이기 때문에 SELECT B, A 로 써줍니다. 쉼표 뒤의 1은 첫번째 행을 라벨로 쓰겠다는 의미입니다.

QUERY에 따라 데이터는 B와 A열을 그대로 가져오게 됩니다. 그러면 VLOOKUP을 시도한 것과 다를 바가 없겠죠.

이제 보기 좋게, 거래처로 먼저 정렬하고, 영업일자로 정렬하기 위해서 QUERY에 ORDER BY 를 적용합니다.

DESC를 써주면 내림차순으로 정렬이 됩니다. 가장 높은 숫자 또는 날짜부터 낮아지는 순서입니다. (ASC를 써주면 오름차순으로 정렬이 됩니다. )

이제 결과가 아래와 같이 나옵니다. 거래처 1의 영업일자가 내림차순으로 정렬이 되었습니다.

이제 QUERY 결과를 VLOOKUP 해서 원하는 마지막 영업일자를 가져올텐데요.

VLOOKUP을 적용할 때에 범위는 이름을 지정하는 것이 편합니다. VLOOKUP을 복사해서 붙여넣을 때에 상대참조였다가 문제가 생기는 일을 미연에 방지할 수 있고, 후에 범위를 바꿀 때에도 유용합니다.

H2:I11 까지의 범위에 미방문일수라는 이름을 추가해주겠습니다. (이름을 추가하는 방법은 이전 포스트를 참조하세요)

그리고 아까 VLOOKUP 을 사용했던 셀 옆에 QUERY 결과를 VLOOKUP 해보겠습니다.

가장 위의 값을 적용할 것이기에 FALSE를 사용합니다. (만약 TRUE를 적용하면 가장 빠른 영업일자를 표시하게 되겠죠)

QUERY와 VLOOKUP을 사용해서 원하는 마지막 영업일자를 구했습니다.

그럼 이제, 오늘과 마지막 영업일자와의 차이를 구해서 미방문일수를 계산해야겠지요.

datedif 함수는 날짜의 차이를 계산해주는데, 시작일, 종료일, 단위를 넣게 되어있습니다.

시작일에는 query-vlookup 으로 얻은 결과를 넣고, 종료일은 today()로 오늘을 넣어줍니다. 단위는 “D”라고 쓰면 일을 계산합니다. 수식은 다음과 같습니다.

이제 아래와 같은 결과를 얻게 됩니다. 오늘(7/8) 영업한 거래처1의 경우에 미방문일수가 0로 정확하게 나옵니다.

사진에는 최근방문이력없음이 표시되는데, 현업에서는 이렇게 표시되는 것이 #N/A로 표시되는 것보다 훨씬 보기가 좋겠죠? 이 부분은 다음에 if와 isna 함수 글에서 다루도록 하겠습니다.

---

오늘의 샘플을 확인해보세요. PC를 권장드립니다.