너무 게으르거나 읽을 시간이 없는 사람 - 비디오를 보십시오. 자세한 내용과 뉘앙스는 아래 텍스트에 있습니다.
문제의 공식화
그래서 우리는 두 개의 테이블을 가지고 있습니다 - 주문 테이블 и 가격표:
작업은 나중에 비용을 계산할 수 있도록 제품 이름에 초점을 맞춰 가격 목록의 가격을 주문 테이블로 자동으로 대체하는 것입니다.
해법
Excel 함수 집합에서 범주 아래 참조 및 배열 (조회 및 참조) 기능이 있다 VPR (조회).이 함수는 위에서 아래로 이동하는 지정된 테이블(가격 목록)의 가장 왼쪽 열에서 주어진 값(이 예에서는 "Apples"라는 단어)을 찾고 찾은 후 인접한 셀의 내용을 표시합니다. (23 루블) .도식적으로이 기능의 작동은 다음과 같이 나타낼 수 있습니다.
기능을 더 쉽게 사용하려면 한 번에 한 가지 작업을 수행하십시오. 가격 목록의 셀 범위에 고유한 이름을 지정하십시오. 이렇게하려면 "헤더"(G3 : H19)를 제외한 가격 목록의 모든 셀을 선택하고 메뉴에서 선택하십시오. 삽입 – 이름 – 할당 (삽입 — 이름 — 정의) 또는 누르십시오 CTRL + F3 다음과 같은 이름(공백 없이)을 입력합니다. 가격... 이제 앞으로 이 이름을 사용하여 가격 목록에 연결할 수 있습니다.
이제 우리는 기능을 사용합니다 VPR… 입력할 셀(D3)을 선택하고 탭을 엽니다. 수식 – 함수 삽입 (수식 - 함수 삽입)… 카테고리에서 참조 및 배열 (조회 및 참조) 기능을 찾아 VPR (조회) 키를 누릅니다 OK... 함수에 대한 인수를 입력하는 창이 나타납니다.
우리는 그것들을 차례로 채웁니다:
- 원하는 값 (조회 값) – 함수가 가격 목록의 가장 왼쪽 열에서 찾아야 하는 제품의 이름. 우리의 경우 B3 셀의 "Apples"라는 단어입니다.
- 작업대 (테이블 배열) – 원하는 값 uXNUMXbuXNUMX가 취해진 테이블, 즉 가격 목록. 참고로 앞서 언급한 "Price"라는 이름을 사용합니다. 이름을 지정하지 않은 경우 테이블을 선택할 수 있지만 버튼을 누르는 것을 잊지 마십시오 F4달러 기호로 링크 고정, 그렇지 않으면 수식을 D3:D30 열의 나머지 셀로 복사할 때 아래로 미끄러지기 때문입니다.
- 열 번호 (열 인덱스 번호) – 일련 번호(문자 아님!) 가격 값을 가져올 가격 목록의 열. 이름이 있는 가격 목록의 첫 번째 열은 1로 번호가 매겨져 있으므로 번호가 2인 열의 가격이 필요합니다.
- 간격 조회 (범위 조회) – 이 필드에는 FALSE 또는 TRUE의 두 가지 값만 입력할 수 있습니다.
- 값을 입력하면 0 or 거짓말하는 (거짓), 실제로 이것은 검색만 허용됨을 의미합니다. 정확히 일치, 즉 함수가 가격 목록의 주문 테이블에 지정된 비표준 품목을 찾지 못하면(예: "코코넛"이 입력된 경우) #N/A(데이터 없음) 오류가 생성됩니다.
- 값을 입력하면 1 or TRUE (진실), 그러면 정확한 검색을 허용하지 않지만 대략적인 일치, 즉 "coconut"의 경우 함수는 "coconut"에 최대한 가까운 이름을 가진 제품을 찾고 이 이름에 대한 가격을 반환합니다. 대부분의 경우 이러한 대략적인 대체는 실제로 존재했던 잘못된 제품의 값을 대체함으로써 사용자에게 속임수를 사용할 수 있습니다! 따라서 대부분의 실제 비즈니스 문제의 경우 대략적인 검색은 허용하지 않는 것이 가장 좋습니다. 예를 들어 단계 할인을 계산할 때와 같이 텍스트가 아닌 숫자를 찾는 경우는 예외입니다.
모든 것! 누르는 것이 남아있다. OK 입력한 함수를 전체 열에 복사합니다.
# N / A 오류 및 억제
함수 VPR (조회) #N/A 오류를 반환합니다. (#해당 없음) 만약:
- 정확한 검색 활성화됨(인수 간격 보기 = 0) 원하는 이름이 에 없습니다. 작업대.
- 대략적인 검색 포함(간격 보기 = 1), 그러나 작업대, 검색이 발생하는 은(는) 이름의 오름차순으로 정렬되지 않습니다.
- 이름의 필수 값이 나오는 셀의 형식(예: 우리의 경우 B3)과 테이블의 첫 번째 열(F3: F19)의 셀 형식이 다릅니다(예: 숫자 및 텍스트 ). 이 경우는 텍스트 이름 대신 숫자 코드(계좌 번호, 식별자, 날짜 등)를 사용할 때 특히 일반적입니다. 이 경우 기능을 사용할 수 있습니다. Ч и TEXT 데이터 형식을 변환합니다. 다음과 같이 보일 것입니다.
=VLOOKUP(텍스트(B3),가격,0)
이에 대한 자세한 내용은 여기에서 확인할 수 있습니다.
- 코드에 공백이나 인쇄할 수 없는 보이지 않는 문자(줄 바꿈 등)가 포함되어 있기 때문에 함수가 필요한 값을 찾을 수 없습니다. 이 경우 텍스트 기능을 사용할 수 있습니다. 정돈 (손질) и 인쇄(깨끗한) 제거하려면:
=VLOOKUP(TRIMSPACES(CLEAN(B3)),가격,0)
=VLOOKUP(트림(CLEAN(B3));가격;0)
오류 메시지를 표시하지 않으려면 # N / A (#해당 없음) 함수가 정확히 일치하는 항목을 찾을 수 없는 경우 함수를 사용할 수 있습니다. 오류 (IF 오류)… 예를 들어, 이 구성은 VLOOKUP에 의해 생성된 모든 오류를 가로채서 XNUMX으로 바꿉니다.
= IFERROR(VLOOKUP(B3, 가격, 2, 0), 0)
= IFERROR(VLOOKUP(B3, 가격, 2, 0), 0)
PS
하나의 값이 아니라 전체 집합을 한 번에 추출해야 하는 경우(여러 개의 다른 값이 있는 경우) 배열 수식으로 샤먼화해야 합니다. 또는 Office 365의 새로운 XLOOKUP 기능을 사용하세요.
- VLOOKUP 기능(VLOOKUP 2)의 개선된 버전입니다.
- VLOOKUP 기능을 사용하여 단계(범위) 할인의 빠른 계산.
- INDEX 및 MATCH 기능을 사용하여 "왼쪽 VLOOKUP"을 만드는 방법
- VLOOKUP 함수를 사용하여 목록의 데이터로 양식을 채우는 방법
- 테이블에서 첫 번째가 아니라 모든 값을 한 번에 꺼내는 방법
- PLEX 애드온의 VLOOKUP2 및 VLOOKUP3 기능