엑셀로 매일 입력하는 단가, 효율적인 실전 관리법!
매일 입력하는 단가 목차 바로가기
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 매일 입력되는 단가 실전 관리법예제파일
사용된 공식
= LOOKUP( 2, 1/(($거래처범위=거래처)*($제품명범위=제품명)*($날짜범위<=기준날짜)), $단가범위 )
매일 입력하는 단가를 관리하기 위한 공식에 사용된 인수입니다. 공식 설명
LOOKUP 함수를 응용하여 매일 입력되는 단가를 효율적으로 관리하는 방법을 알아봅니다. 이번 강의에서는 단가를 가져올 조건으로 '거래처', '제품명', '날짜' 3가지를 구분하였지만 필요에 따라 더 다양한 조건을 적용할 수도 있습니다.
- 검색조건 중, '날짜범위'는 반드시 '오름차순'으로 정렬되어야 합니다. (이전날짜가 위에 오도록)
날짜 범위는 반드시 '오름차순'으로 정렬되어야 합니다. - LOOKUP 함수는 검색범위의 아래에서 위로 올라오면서 값을 검색합니다. 본 공식은 LOOKUP 함수의 이런 점을 응용하여, '기준일'로부터 가장 최근에 등록된 제품의 가격을 검색합니다.
LOOKUP 함수는 범위의 아래에서부터 위로 올라오며 값을 검색합니다. - LOOKUP 함수 마지막값 찾기 공식에 대한 자세한 내용은 아래 관련 포스트를 참고하세요.
관련 기초함수
강의에 사용된 공식 알아보기
강의에서 사용된 공식
=LOOKUP( 2, 1/((tbl제품정보[거래처]=B3)*(tbl제품정보[제품]=C3)*(tbl제품정보[등록일]<=A3)), tbl제품정보[단가] )
‘// 거래처, 제품 및 등록일 조건을 모두 만족할 경우의 최근값을 출력범위인 '단가' 범위에서 찾아 반환합니다.공식의 동작원리 단계별 알아보기
- LOOKUP 함수의 검색조건으로 입력된 수식을 우선 살펴보겠습니다.
각 조건인 거래처, 제품, 등록일을 모두 만족할 경우 계산결과로 1을 반환합니다.
그렇지 않고, 조건을 하나라도 만족하지 않으면 1/0 이 되어 #DIV/0 오류를 반환합니다.= LOOKUP( 2, 1/((tbl제품정보[거래처]=B3)*(tbl제품정보[제품]=C3)*(tbl제품정보[등록일]<=A3)), tbl제품정보[단가] )
조건을 하나라도 만족하지 않아 반환된 #DIV/0 오류는 검색 대상에서 제외됩니다. = 1/((tbl제품정보[거래처]=B3)*(tbl제품정보[제품]=C3)*(tbl제품정보[등록일]<=A3))
= 1/{(TRUE,FALSE,FALSE,FALSE,…)*(TRUE,FALSE,FALSE,FALSE,…)*(TRUE,FALSE,FALSE,FALSE,…)}
= 1/{(1,0,0,0...)*(1,0,0,0...)*(1,0,0,0)}
= 1/{1, 0, 0, 0, …}
= {1/1, 1/0, 1/0, 1/0 ..}
= 1, #DIV/0, #DIV/0, #DIV/0, ...
'// LOOKUP 함수는 인수로 '배열'을 받을 수 있습니다. 이러한 함수는 대표적으로 SUMPRODUCT 함수가 있으며, 이런 점을 응용하면 다양한 방법으로 함수를 활용할 수 있습니다. - LOOKUP 함수는 검색범위에서 값을 '아래에서 위로 올라오면서 검색'합니다. 또한 검색범위에서 찾으려는 값과 정확히 일치하는 값이 없을 경우, 해당 값보다 작거나 같은 '유사일치'로 검색합니다.
공식에서 찾을값은 '2' 입니다. 즉, 검색범위에서 '2'보다 같거나 작은 값을 아래에서부터 위로 올라오면서 검색합니다.
범위에서 가장 마지막으로 반환된 1을 찾은 뒤, 해당 단가를 반환합니다.
- 검색조건 중, '날짜범위'는 반드시 '오름차순'으로 정렬되어야 합니다. (이전날짜가 위에 오도록)
