목적 설명
목적.
영업 중심 산업에 종사하고 있다면 거래처에 언제 연락해서 방문했는지를 기록으로 남겨서 언제쯤 다시 방문하면 되는지 체크해야 되겠죠?
영업일지를 작성하면 자동으로 거래처목록에 #최종방문일 또는 #미방문일수를 표시하도록 할 수 있습니다.
#datedif과 #vlookup을 사용해서 어떻게 표시할지 봅시다.
설명
통상, 영업일지를 작성하는 시트와 거래처 시트는 따로 관리합니다만, 설명을 간결하게 하기 위해서 1 페이지로 작업하겠습니다.
왼쪽에는 영업일지, 오른쪽에는 거래처 시트라고 합니다.
다른 정보가 있을 수 있지만 설명의 초점인 영업일과 미방문일수만큼 적어놨습니다.
영업일지에는영업내용,담당자등이들어가고거래처시트에는거래처의전화번호,담당자명,주소,사업자등록번호등다양하게넣게되겠죠.
이제 거래처 시트(화면 우측부분)에서 미방문일수를 계산하는 방법을 알아보겠습니다.
좌측 영업일지에는 계속해서 영업일자와 거래처가 누적됩니다
미방문일수를 계산하시려면 특정거래처의 마지막 영업일을 찾아서 오늘과 비교하셔야 합니다.
마지막 영업일을 찾는 것이 키이지만 단순히 VLOOKUP만 사용해서는 원하는 값을 찾을 수 없습니다.
예를 들어 VLOOKUP 정렬을 FALSE로 해보겠습니다.
거래처 1 영업일이 3개 있는데 그 중 제일 높은 값을 가져옵니다.
그렇다고 정렬을 T RUE한다고 올바른 값을 찾지도 않습니다.
애매하게 중간에 있는 값을 가져왔어요.
이렇게 가지고 오는 이유는 TRUE는 VLOOKUP에서 첫 번째 줄이 나열되어 있을 때 사용하는 것이기 때문입니다.
단순한 VLOOKUP에서는 마지막 영업일을 찾을 수 없었지만, 여기서 #QUERY가 필요합니다.
그럼 옆에 QUERY라는 목록을 하나 만들건데 거래처를 첫 번째 줄로, 영업일을 두 번째 줄로 부르면서 거래처와 방문일을 나란히 해보겠습니다.
A1 : B11부터 열을 선택합니다만, 거래처를 먼저 표시하고 영업일을 다음 열에 표시할 테니, SELECT B, A를 써 주세요.
쉼표뒤에1은첫줄을라벨에쓴다는뜻이에요.
QUERY에 의해 데이터는 B와 A열로 그대로 돌아갑니다.
그러면 VLOOK UP을 시도한 거랑 다를 게 없죠.
자, 보기 좋게 거래처에 먼저 정렬하고 영업일에 정렬하기 위해서 QUERY에 ORDERBY를 적용합니다
DESC를 적어주시면 내림차순으로 정렬할 수 있습니다.
가장 높은 숫자 또는 날짜부터 낮아지는 순서입니다.
(ASC라고 써주시면 오름차순으로 정렬됩니다.
)
결과는 다음과 같이 나옵니다.
거래처 1의 영업일이 내림차순으로 정렬되었습니다.
앞으로 QUERY 결과를 VLO OKUP 하고 원하는 마지막 영업일을 갖도록 하겠습니다.
VLOOKUP 적용 시 범위는 이름을 지정하는 것이 편합니다.
VLOOKUP을 복사하여 붙여넣기 할 때 상대방 참조였으나 문제가 발생하는 것을 미연에 방지할 수 있고 나중에 범위를 바꿀 때에도 유용합니다.
H2:I11까지의 범위에 미방문일수라는 이름을 추가하십시오. (이름추가 방법은 이전 포스트셀에서 지정된 값만을 선택할 수 있도록 하는 [데이터 확인]과 [이름이 지정된 범위]를 참고하세요.)
그리고 아까 V LOOK UP을 사용한 셀 옆 QUERY 결과를 V LOOK UP 해보겠습니다.
최선의 값을 적용할 것이기 때문에 FALSE를 사용하고 있습니다.
만약 TRUE를 적용하면 가장 빠른 영업 일시를 표시하게 될 것입니다.
QUERY와 VLOOKUP을 이용해서 원하는 마지막 영업일을 알아냈습니다.
그럼, 오늘과 마지막 영업일의 차이를 찾아서 미방문 일수를 계산하지 않으면 안 되겠네요.
datedif 함수는 날짜 차이를 계산하는데, 시작일, 종료일, 단위를 넣도록 되어 있습니다.
시작일에는 query-vlookup으로 얻은 결과를 넣고 종료일은 today()로 오늘을 넣습니다.
학점은 D라고 쓰면 일을 계산합니다.
수식은 다음과 같습니다.
아래와 같은 결과가 나옵니다.
2021년 7월 8일 기준 영업한 거래처 1의 경우 미방문일수가 0으로 정확하게 나와있습니다.
사진에는 최근 방문 내역이 없음이 표시되지만 현업에서는 이렇게 표시되는 것이 #N/A로 표시되는 것보다 훨씬 보기 좋겠죠?
이 부분은 다음 if와 isna함수 문에서 다루도록 하겠습니다.
샘플을 확인해 주세요.
PC로 보면 더 정확하게 볼 수 있어요.
샘플을 보다
업무 효율성 200% 향상, with autooffice
프로젝트에 대한 문의는 구글폼을 이용해 주세요!
오토오피스 프로젝트 문의 Sign in to Google to save your progress. Learn more * Required 기업명 또는 성함을 알려주세요. * 기업문의의 경우에는 담당하시는 분의 성함과 직함도 적어주세요. Your answer 전화번호를 알려주세요. 휴대 전화 또는 유선 전화를 기입해 주세요. Your answer 이메일로 연락을 원하시면 추가해주세요 * 메일형식에 맞게 작성해주세요 Your answer 프로젝트 종류를 선택하세요. * Google 시트 업무 자동화 대시보드(Power BI, Goo…forms.gle