VLOOKUP in Excel, 파트 2 데이터베이스없이 VLOOKUP 사용하기
최근 기사에서 우리는 Excel 함수를 소개했습니다. VLOOKUP 로컬 워크 시트의 셀로 데이터베이스에서 정보를 검색하는 데이 정보를 사용하는 방법에 대해 설명했습니다. 이 기사에서 VLOOKUP을 사용하는 방법은 두 가지였으며 데이터베이스 중 하나만 쿼리하는 방법에 대해 언급했습니다. 이 기사에서는 VLOOKUP 시리즈의 두 번째이자 마지막 인 VLOOKUP 함수에 대해 잘 알려지지 않은 다른 용도에 대해 살펴 봅니다..
아직 작성하지 않았다면, 첫 번째 VLOOKUP 기사를 읽으십시오.이 기사에서는이 기사에서 설명하는 개념 중 많은 부분이 이미 독자에게 알려져 있다고 가정합니다.
데이터베이스 작업시 VLOOKUP에는 데이터베이스에서 찾을 데이터 레코드 (예 : 제품 코드 또는 고객 ID)를 식별하는 "고유 식별자"가 전달됩니다. 이 고유 한 식별자 절대로 필요한 것 데이터베이스에 존재하지 않으면 VLOOKUP은 오류를 반환합니다. 이 기사에서는 식별자가 데이터베이스에 전혀 존재하지 않아도되는 VLOOKUP을 사용하는 방법에 대해 살펴볼 것입니다. VLOOKUP이 우리가 찾고있는 데이터를 반환하기 위해 "충분히 충분히 가깝습니다"접근 방식을 채택 할 수있는 것과 거의 같습니다. 경우에 따라 정확하게 우리가 필요로하는 것.
우리는 실제 사례를 통해이 기사를 설명 할 것입니다 - 판매 수치 세트에서 생성 된 커미션을 계산하는 사례. VLOOKUP을 사용하는 것이 문제의 유일한 합리적인 해결책이 될 때까지는 매우 간단한 시나리오부터 시작하여 점진적으로 복잡해질 것입니다. 가상의 회사의 초기 시나리오는 다음과 같습니다. 영업 사원이 특정 연도에 3 만 달러 이상의 매출을 올리면 해당 매출로 얻는 수수료는 30 %입니다. 그렇지 않으면 수수료가 20 %에 불과합니다. 지금까지는 매우 간단한 워크 시트입니다.
이 워크 시트를 사용하기 위해 영업 사원은 셀 B1에 판매 수치를 입력하고 B2 셀의 수식은 셀 B3에서 영업 사원이 빚진 총 수수료를 계산하는 데 사용할 수있는 올바른 수수료 수익을 계산합니다 ( B1과 B2의 단순한 곱셈이다).
셀 B2에는이 워크 시트의 유일한 흥미로운 부분이 포함되어 있습니다. 사용할 수있는 수수료 수수료를 결정하는 수식은 다음과 같습니다. 이하 $ 30,000의 문턱, 또는 위에 임계 값. 이 수식은 Excel이라는 Excel 함수를 사용합니다. 만약. IF에 익숙하지 않은 독자는 다음과 같이 작동합니다.
만약(조건, 참이면 참, 거짓이면 참)
어디에서 조건 다음 중 하나를 평가하는 표현식입니다. 참된 또는 그릇된. 위의 예에서 조건 표현이다. B1
보시다시피, 총 매출 2 만 달러를 사용하면 셀 B2에서 수수료율 20 %를 얻을 수 있습니다. 우리가 $ 40,000의 값을 입력하면, 우리는 다른 수수료 비율을 얻을 :
스프레드 시트가 작동 중입니다..
더 복잡하게 만들어 봅시다. 두 번째 기준점을 소개합시다 : 영업 사원이 4 만 달러 이상을 벌면 수수료율이 40 %로 증가합니다.
현실 세계에서 쉽게 이해할 수 있지만 B2 셀에서는 수식이 점점 복잡해지고 있습니다. 수식을 자세히 보면 원래의 IF 함수의 세 번째 매개 변수 ( false의 경우의 값)가 이제는 전체 IF 함수가되었습니다. 이것은 중첩 된 함수 (함수 내의 함수). 그것은 엑셀에서 완벽하게 유효합니다 (심지어 작동합니다!). 그러나 읽고 이해하기가 더 어렵습니다..
어떻게 작동하는지, 왜 작동하는지, 그리고 중첩 된 기능의 뉘앙스를 조사하지 않을 것입니다. 이것은 일반적으로 Excel이 아닌 VLOOKUP에 대한 자습서입니다..
어쨌든, 그것은 더 악화됩니다! 우리가 50,000 달러 이상을 벌면 50 % 수수료를받을 자격이 있고, 6 만 달러 이상을 벌면 60 %의 수수료를받을 수 있다고 결정할 때 어떨까요??
이제 셀 B2의 수식은 정확하지만 사실상 읽을 수 없게되었습니다. 어느 누구도 함수가 네 단계 깊게 중첩 된 수식을 작성해야합니다! 확실히 간단한 방법이 있어야합니다.?
확실히 그렇습니다. 구조에 VLOOKUP!
워크 시트를 약간 재 설계 해 봅시다. 우리는 모든 동일한 수치를 유지할 것이지만 그것을 새로운 방식으로 조직하십시오. 표의 방법:
잠시 시간을내어 새로운 것을 확인하십시오. 비율 표 위의 일련의 임계 값과 완전히 동일하게 작동합니다..
개념적으로, 우리가하려고하는 것은 VLOOKUP을 사용하여 요율 테이블에서 영업 사원의 판매 합계 (B1에서)를 조회하고 해당 수수료율을 우리에게 반환하는 것입니다. 영업 사원이 실제로 판매를 생성했을 수도 있습니다. 아니 요금 테이블 ($ 0, $ 30,000, $ 40,000, $ 50,000 또는 $ 60,000)의 5 개 값 중 하나. 그들은 $ 34,988의 판매를 창조 할지도 모른다. 34,988 달러는 아니 요율 테이블에 나타납니다. VLOOKUP이 문제를 해결할 수 있는지 보도록하겠습니다 ...
셀 B2 (수식을 넣을 위치)를 선택하고 나서 VLOOKUP 함수를 방식 탭:
그만큼 함수 인수 VLOOKUP 상자가 나타납니다. 인수 (매개 변수)를 하나씩 채우고 조회 _ 값, 이 경우 셀 B1의 판매 합계입니다. 커서를 조회 _ 값 필드를 선택한 다음 B1 셀을 한 번 클릭하십시오.
다음으로이 데이터를 조회 할 테이블을 VLOOKUP으로 지정해야합니다.이 예에서는 물론 요금 테이블입니다. 커서를 Table_array 필드를 선택한 다음 전체 요율표를 강조 표시합니다. - 표제를 제외하고:
다음으로 우리는 공식에서 우리에게 반환 할 정보가 들어있는 표의 열을 지정해야합니다. 이 경우 테이블의 두 번째 열에있는 수수료율을 원하므로 2 로 Col_index_num 들:
마지막으로 우리는 Range_lookup 들.
중요 :이 필드는 VLOOKUP을 사용하는 두 가지 방법을 구별하는 용도로 사용됩니다. VLOOKUP을 데이터베이스와 함께 사용하려면이 마지막 매개 변수, Range_lookup, 항상 설정해야합니다. 그릇된, 그러나이 다른 VLOOKUP의 사용과 함께, 우리는 그것을 공란으로 두거나 값을 입력해야합니다 참된. VLOOKUP을 사용할 때이 최종 매개 변수에 대해 올바른 선택을하는 것이 중요합니다.
명시 적으로, 우리는 참된 ~ 안에 Range_lookup 들. 기본값이기 때문에 공백으로 두어도 괜찮습니다.
우리는 모든 매개 변수를 완료했습니다. 이제 승인 버튼을 클릭하면 Excel에서 VLOOKUP 공식을 만듭니다.
몇 가지 판매 총액을 실험 해 보면 수식이 작동하고 있음을 만족시킬 수 있습니다..
결론
VLOOKUP의 "데이터베이스"버전에서 Range_lookup 매개 변수는 그릇된, 첫 번째 매개 변수에서 전달 된 값 (조회 _ 값) 절대로 필요한 것 데이터베이스에 있어야합니다. 다시 말해 정확한 일치를 찾고 있습니다..
그러나 VLOOKUP을 다른 용도로 사용하는 경우 정확하게 일치하는 것은 아닙니다. 이 경우에는 "충분히 가깝기에 충분합니다." 그러나 우리는 "충분히 가까이"라는 의미는 무엇입니까? 예를 들어 봅시다 : 판매 총액 $ 34,988의 커미션 비율을 검색 할 때 VLOOKUP 수식은 정답 인 30 %의 값을 반환합니다. 왜 테이블에서 30 %를 포함하는 행을 선택 했습니까? 사실,이 경우에 "충분히 가깝다"는 것은 무엇을 의미합니까? 정확 해 보겠습니다.
언제 Range_lookup 에 설정 됨 참된 (또는 생략 된 경우) VLOOKUP은 열 1을보고 일치합니다 최대 값보다 크지 않은 그만큼 조회 _ 값 매개 변수.
이 시스템이 작동하려면, 테이블은 열 1에서 오름차순으로 정렬되어야합니다.!
VLOOKUP으로 연습하고 싶다면이 기사에서 설명하는 샘플 파일을 여기에서 다운로드 할 수 있습니다.
