매일 입력하는 단가, 가장 효율적인 엑셀 관리방법! :: 실무기초

주기적으로 변하는 데이터를 효율적으로 관리하기 위한 데이터 정리방법 및 함수공식의 원리와 실전 사용예제를 알아봅니다.

홈페이지 » 매일 입력하는 단가, 가장 효율적인 엑셀 관리방법! :: 실무기초

엑셀로 매일 입력하는 단가, 효율적인 실전 관리법!

매일 입력하는 단가 목차 바로가기
영상강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [실무기초] 매일 입력되는 단가 실전 관리법
    예제파일

사용된 공식
= LOOKUP( 2, 1/(($거래처범위=거래처)*($제품명범위=제품명)*($날짜범위<=기준날짜)), $단가범위 )
매일 입력하는 단가 공식 인수 설명
매일 입력하는 단가를 관리하기 위한 공식에 사용된 인수입니다.
공식 설명

LOOKUP 함수를 응용하여 매일 입력되는 단가를 효율적으로 관리하는 방법을 알아봅니다. 이번 강의에서는 단가를 가져올 조건으로 '거래처', '제품명', '날짜' 3가지를 구분하였지만 필요에 따라 더 다양한 조건을 적용할 수도 있습니다.

  • 검색조건 중, '날짜범위'는 반드시 '오름차순'으로 정렬되어야 합니다. (이전날짜가 위에 오도록)

    매일 입력 단가 날짜 오름차순 정렬
    날짜 범위는 반드시 '오름차순'으로 정렬되어야 합니다.

  • LOOKUP 함수는 검색범위의 아래에서 위로 올라오면서 값을 검색합니다. 본 공식은 LOOKUP 함수의 이런 점을 응용하여, '기준일'로부터 가장 최근에 등록된 제품의 가격을 검색합니다.

    LOOKUP 아래에서 위로 검색
    LOOKUP 함수는 범위의 아래에서부터 위로 올라오며 값을 검색합니다.

  • LOOKUP 함수 마지막값 찾기 공식에 대한 자세한 내용은 아래 관련 포스트를 참고하세요.
관련 기초함수

강의에 사용된 공식 알아보기

강의에서 사용된 공식
=LOOKUP( 2, 1/((tbl제품정보[거래처]=B3)*(tbl제품정보[제품]=C3)*(tbl제품정보[등록일]<=A3)), tbl제품정보[단가] )
‘// 거래처, 제품 및 등록일 조건을 모두 만족할 경우의 최근값을 출력범위인 '단가' 범위에서 찾아 반환합니다.
공식의 동작원리 단계별 알아보기
  1. LOOKUP 함수의 검색조건으로 입력된 수식을 우선 살펴보겠습니다.
    각 조건인 거래처, 제품, 등록일을 모두 만족할 경우 계산결과로 1을 반환합니다.
    그렇지 않고, 조건을 하나라도 만족하지 않으면 1/0 이 되어 #DIV/0 오류를 반환합니다.
    = LOOKUP( 2, 1/((tbl제품정보[거래처]=B3)*(tbl제품정보[제품]=C3)*(tbl제품정보[등록일]<=A3)), tbl제품정보[단가] )
    조건 만족시 1 반환
    조건을 하나라도 만족하지 않아 반환된 #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 함수가 있으며, 이런 점을 응용하면 다양한 방법으로 함수를 활용할 수 있습니다.
  2. LOOKUP 함수는 검색범위에서 값을 '아래에서 위로 올라오면서 검색'합니다. 또한 검색범위에서 찾으려는 값과 정확히 일치하는 값이 없을 경우, 해당 값보다 작거나 같은 '유사일치'로 검색합니다.
    공식에서 찾을값은 '2' 입니다. 즉, 검색범위에서 '2'보다 같거나 작은 값을 아래에서부터 위로 올라오면서 검색합니다.

    매일 입력하는 단가 최근 값 반환
    범위에서 가장 마지막으로 반환된 1을 찾은 뒤, 해당 단가를 반환합니다.

5 7 투표
게시글평점
guest
13 댓글
Inline Feedbacks
모든 댓글 보기
호야
호야(@517-2)
2020년 3월 25일 12:45 오전
게시글평점 :
     

잘보고 갑니다.

joy
joy
2020년 4월 5일 2:46 오후
게시글평점 :
     

좋은 정보 감사합니다^^*

엑린이
엑린이(@1125)
2020년 4월 18일 3:50 오후
게시글평점 :
     

좋은 강의 감사합니다. 이해가 잘 안되서 그러는데 왜 찾을값인 2의 의미가 무엇인가요?

에이사 버터필드
에이사 버터필드(@387)
2020년 6월 11일 1:23 오후
답글 남기기  엑린이

2보다 작은 값을 찾게 되는 거래요, 엑셀에서는 이런 묘한 방법들이 중요한듯 해요. 저도 잘은 모르지만요, 아직 기초단계여서요.

에이사 버터필드
에이사 버터필드(@387)
2020년 6월 11일 1:22 오후
게시글평점 :
     

이해가 잘 됩니다. 감사합니다.

뿌까뿌가
뿌까뿌가(@-2438)
2020년 7월 8일 7:45 오후
게시글평점 :
     

함수가 조금 어렸네요 ㅜㅜ 기초라서 그래도 처음보다 몇번 더 들으니깐 조금씩 이해도가 쌓이네요 ㅎ

Fainter
Fainter(@firecodenaver-com)
2020년 10월 27일 8:20 오전
게시글평점 :
     

Lookup 함수는 이해가 됐는데 indirect 함수와 table 이름 조합하는게 헷깔리고 어렵네요. 약간의 질문이 생겼습니다. 강의 초반에 배열test sheet 에서 범위를 선택해서 함수를 설명해주시는데 범위 선택을 $로 고정하지 않고 넣으셨는데 어떻게… 더보기 »

운비
운비(@zumai94naver-com)
2021년 4월 11일 9:55 오후
게시글평점 :
     

역시나 좋은강의입니다~감사합니다.

뚱이뚱이뚱이
뚱이뚱이뚱이(@-4068)
2021년 4월 13일 1:26 오전
게시글평점 :
     

선생님 안녕하세요. 질문드릴게있는데, 강의 내용중에 #DIV/0 오류로 만들어주는 이유가 있나요? LOOKUP함수를 통해 2를 찾는다고 하면 0으로 값이 나올 경우, 오류값이 아닌 0으로 나와도 되는게 아닌가 싶어서 문의드립니다. 강의 덕분에 항상… 더보기 »

돌고래지롱
돌고래지롱(@cjh6432nate-com)
2021년 9월 9일 9:49 오전
게시글평점 :
     

좋은정보감사합니다.*.*
질문)index,match(max).......이런 강의도 들었는데요..이것과 오늘 강의와 차이점을 무엇인가요?

퇴근하고싶
퇴근하고싶(@hue_04naver-com)
2021년 9월 27일 8:32 오후
게시글평점 :
     

감사합니다!!

토백이
토백이(@21yongyonghanmail-net)
2021년 12월 1일 2:30 오후
게시글평점 :
     

좋은 강의 고맙습니다. lookup 함수의 숨겨진 비밀을 알게 되어 많은 도움이 됐습니다^^

13
0
여러분의 생각을 댓글로 남겨주세요.x