엑셀로 매일 입력하는 단가, 효율적인 실전 관리법!
매일 입력하는 단가 목차 바로가기
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 매일 입력되는 단가 실전 관리법예제파일
사용된 공식
= 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을 찾은 뒤, 해당 단가를 반환합니다.
- 검색조건 중, '날짜범위'는 반드시 '오름차순'으로 정렬되어야 합니다. (이전날짜가 위에 오도록)
- 강의 초반에 배열test sheet 에서 범위를 선택해서 함수를 설명해주시는데 범위 선택을 $로 고정하지 않고 넣으셨는데 어떻게 한방에 밑에까지 채우기 되는지 궁금합니다.
- 그리고 구매기록에 주황색 영역에서 단가부분 함수가 처음에는 {가 들어갔었다가 다시보니 사라졌는데 {로 표시된 이유가 있을까요?
- 또 거래처 단가 등을 표시할때 [@거래처] 가 있는데 @이란 기능이 어떤기능을 하는건지 궁금합니다. 초반 기초강의에서 본거같은데 잘 모르겠어요 ㅠㅠ
연관강의가 있어서 다 볼 수는 없지만, 강의를 보면서 일단 정주행 부터 다하고 다시 처음부터 시작하기 하면 어느정도 고급스킬로 다가설거라는 목표로 열심히 배우고 있습니다. ^^ 좋은 강의 감사드립니다.- 커서를 셀의 오른쪽 아래로 가져가신 뒤, 더블클릭 하시면 좌/우 행 높이에 맞춰 자동채우기가 됩니다
- { 는 배열수식 기호입니다. 자세한 내용은 아래 링크를 참고해보세요.https://www.oppadu.com/%EB%B0%B0%EC%97%B4%ED%95%A8%EC%88%98-%EB%B0%B0%EC%97%B4%EC%88%98%EC%8B%9D/
- [@] 기호는 표의 '구조적참조' 기호입니다. [@필드명]으로 입력하면 수식이 입력된 셀과 동일한 행의 필드값을 참조합니다.
답변이 도움이 되셨길 바랍니다. :) 감사합니다.