우리는 최근에 가장 유용한 Excel 기능 중 하나인 VPR 데이터베이스에서 워크시트 셀로 필요한 정보를 추출하는 데 사용할 수 있는 방법을 보여주었습니다. 우리는 또한 함수에 대한 두 가지 사용 사례가 있다고 언급했습니다. VPR 그리고 그 중 하나만 데이터베이스 쿼리를 처리합니다. 이 기사에서는 함수를 사용하는 덜 알려진 또 다른 방법을 배웁니다. VPR Excel에서.
아직 이 작업을 수행하지 않았다면 이 기능에 대한 마지막 기사를 반드시 읽으십시오. VPR, 아래의 모든 정보는 귀하가 첫 번째 기사에서 설명한 원칙에 이미 익숙하다고 가정하기 때문입니다.
데이터베이스로 작업할 때 함수 VPR 고유 식별자가 전달되어 찾고자 하는 정보(예: 제품 코드 또는 고객 식별 번호)를 식별하는 데 사용됩니다. 이 고유 코드는 데이터베이스에 있어야 합니다. 그렇지 않으면 VPR 오류를 보고합니다. 이 기사에서는 이 함수를 사용하는 방법을 살펴보겠습니다. VPRid가 데이터베이스에 전혀 존재하지 않는 경우. 마치 기능이 VPR 근사 모드로 전환하고 무언가를 찾고 싶을 때 제공할 데이터를 선택합니다. 특정 상황에서는 이것이 바로 필요한 것입니다.
삶의 예. 우리는 작업을 설정
다양한 판매 지표를 기반으로 수수료를 계산하는 실제 예를 들어 이 기사를 설명하겠습니다. 우리는 매우 간단한 옵션으로 시작하여 문제에 대한 유일한 합리적인 해결책이 함수를 사용하는 것일 때까지 점차적으로 복잡해질 것입니다. VPR. 가상 작업의 초기 시나리오는 다음과 같습니다. 영업 사원이 30000년에 $30 이상의 매출을 올리는 경우 그의 수수료는 20%입니다. 그렇지 않으면 수수료는 XNUMX%에 불과합니다. 테이블 형식으로 넣어 보겠습니다.
판매자는 셀 B1에 판매 데이터를 입력하고 셀 B2의 수식은 판매자가 기대할 수 있는 정확한 수수료율을 결정합니다. 결과적으로 결과 비율은 셀 B3에서 판매자가 받아야 하는 총 수수료를 계산하는 데 사용됩니다(단순히 셀 B1과 B2를 곱함).
표에서 가장 흥미로운 부분은 B2 셀에 있습니다. 이것은 수수료율을 결정하는 공식입니다. 이 수식에는 IF (만약에). 이 기능에 익숙하지 않은 독자를 위해 작동 방식을 설명하겠습니다.
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
상태 다음 중 하나의 값을 취하는 함수 인수입니다. 진정한 코드 (참), 또는 그릇된 (거짓). 위의 예에서 표현식 B1
B1이 B5보다 작다는 것이 사실입니까?
또는 다르게 말할 수도 있습니다.
해당 연도의 총 판매액이 임계값보다 적은 것이 사실입니까?
이 질문에 답하자면 예 (TRUE), 함수는 다음을 반환합니다. true인 경우 값 (TRUE인 경우 값). 우리의 경우 이것은 셀 B6의 값이 될 것입니다. 즉, 총 판매가 임계값 미만일 때 수수료율입니다. 질문에 답하자면 아니 (FALSE) 그런 다음 반환 거짓이면 값 (FALSE인 경우 값). 우리의 경우 이것은 셀 B7의 값, 즉 총 판매가 임계값을 초과할 때 수수료율입니다.
보시다시피 총 판매액이 $20000인 경우 셀 B2에서 20%의 수수료를 받습니다. $40000의 값을 입력하면 수수료율이 30%만큼 변경됩니다.
이것이 우리의 테이블이 작동하는 방식입니다.
우리는 작업을 복잡하게
좀 더 어렵게 합시다. 다른 임계값을 설정해 보겠습니다. 판매자가 $40000 이상을 번 경우 수수료율은 40%로 증가합니다.
모든 것이 간단하고 명확해 보이지만 B2 셀의 수식은 눈에 띄게 복잡해집니다. 공식을 자세히 보면 함수의 세 번째 인수가 IF (IF) 또 다른 본격적인 기능으로 변신 IF (만약에). 이러한 구성을 함수를 서로 중첩하는 작업이라고 합니다. Excel은 이러한 구문을 기꺼이 허용하고 작동하기도 하지만 읽고 이해하기가 훨씬 어렵습니다.
우리는 기술적인 세부 사항(왜 그리고 어떻게 작동하는지)을 탐구하지 않을 것이며 중첩된 함수를 작성하는 뉘앙스로 들어가지 않을 것입니다. 결국, 이것은 기능 전용 기사입니다. VPR, Excel에 대한 완전한 가이드가 아닙니다.
어쨌든 수식은 더 복잡해집니다! $50 이상의 매출을 올리는 판매자에게 50000%의 수수료율에 대한 또 다른 옵션을 도입하면 어떻게 될까요? 그리고 누군가 $60000 이상을 판매했다면 수수료 60%를 지불합니까?
이제 B2 셀의 수식은 오류 없이 작성되었더라도 완전히 읽을 수 없게 되었습니다. 프로젝트에서 4단계 중첩이 있는 수식을 사용하려는 사람은 거의 없다고 생각합니다. 더 쉬운 방법이 있어야합니다?!
그리고 그런 방법이 있습니다! 기능은 우리를 도울 것입니다 VPR.
VLOOKUP 함수를 적용하여 문제를 해결합니다.
테이블의 디자인을 약간 변경해 보겠습니다. 우리는 동일한 필드와 데이터를 모두 유지하지만 새롭고 더 간결한 방식으로 정렬합니다.
잠시 시간을 내어 새 테이블을 확인하십시오. 요금표 이전 임계값 테이블과 동일한 데이터를 포함합니다.
주요 아이디어는 기능을 사용하는 것입니다 VPR 표에 따라 원하는 관세율을 결정하기 위해 요금표 판매량에 따라. 판매자는 표의 34988개 임계값 중 하나와 같지 않은 금액으로 상품을 판매할 수 있습니다. 예를 들어, 그는 $XNUMX에 판매할 수 있지만 그러한 금액은 없습니다. 기능이 어떻게 되는지 보자 VPR 그러한 상황에 대처할 수 있습니다.
VLOOKUP 함수 삽입
B2 셀(수식을 삽입할 위치)을 선택하고 VLOOKUP (VLOOKUP) Excel 함수 라이브러리: 방식 (공식) > 함수 라이브러리 (함수 라이브러리) > 조회 및 참조 (참조 및 배열).
대화 상자가 나타납니다 함수 인수 (함수 인수). 다음으로 시작하여 인수의 값을 하나씩 채웁니다. 조회 _ 값 (조회_값). 이 예에서는 셀 B1의 총 판매액입니다. 필드에 커서를 놓습니다. 조회 _ 값 (Lookup_value)를 선택하고 B1 셀을 선택합니다.
다음으로 기능을 지정해야 합니다. VPR데이터를 찾는 곳. 이 예에서 이것은 테이블입니다. 요금표. 필드에 커서를 놓습니다. 테이블_배열 (표) 및 전체 표 선택 요금표헤더를 제외하고.
다음으로 수식을 사용하여 데이터를 추출할 열을 지정해야 합니다. 테이블의 두 번째 열에 있는 수수료율에 관심이 있습니다. 그러므로 논증에 대하여 Col_index_num (Column_number) 값 2를 입력합니다.
마지막으로 마지막 인수를 소개합니다. 범위_조회 (간격 조회).
중요 사항: 함수를 적용하는 두 가지 방법 사이의 차이를 만드는 것은 이 인수의 사용입니다. VPR. 데이터베이스로 작업할 때 인수 범위_조회 (range_lookup)에는 항상 값이 있어야 합니다. 그릇된 (FALSE) 정확히 일치하는 항목을 검색합니다. 우리가 기능을 사용할 때 VPR, 이 필드를 비워두거나 값을 입력해야 합니다. 진정한 코드 (진실). 이 옵션을 올바르게 선택하는 것이 매우 중요합니다.
명확하게 하기 위해 소개하겠습니다. 진정한 코드 (TRUE) 필드에서 범위_조회 (Interval_lookup). 필드를 비워두면 오류가 발생하지 않습니다. 진정한 코드 기본값:
모든 매개변수를 입력했습니다. 이제 우리는 OK, Excel은 함수를 사용하여 수식을 만듭니다. VPR.
총 판매 금액에 대해 여러 가지 다른 값을 실험하면 공식이 올바르게 작동하는지 확인합니다.
결론
기능이 VPR 데이터베이스, 인수와 함께 작동 범위_조회 (range_lookup) 수락해야 함 그릇된 (거짓). 그리고 다음과 같이 입력한 값은 조회 _ 값 (Lookup_value)가 데이터베이스에 있어야 합니다. 즉, 정확히 일치하는 항목을 찾고 있습니다.
이 기사에서 살펴본 예에서는 정확히 일치할 필요가 없습니다. 기능이 있는 경우입니다. VPR 원하는 결과를 반환하려면 근사 모드로 전환해야 합니다.
예 : 판매량이 $34988인 판매원에 대한 수수료 계산에 사용할 요율을 결정하려고 합니다. 기능 VPR 절대적으로 정확한 30% 값을 반환합니다. 그러나 수식이 정확히 30%를 포함하고 20% 또는 40%가 아닌 행을 선택한 이유는 무엇입니까? 대략적인 검색이란 무엇을 의미합니까? 분명히 하자.
주장할 때 범위_조회 (interval_lookup) 값이 있습니다 진정한 코드 (TRUE) 또는 생략, 함수 VPR 첫 번째 열을 반복하고 조회 값을 초과하지 않는 가장 큰 값을 선택합니다.
중요 사항 : 이 체계가 작동하려면 테이블의 첫 번째 열을 오름차순으로 정렬해야 합니다.