내용
오늘 우리는 Excel의 가장 유용한 기능 중 하나를 설명하는 일련의 기사를 시작합니다. VPR (조회). 동시에 이 기능은 가장 복잡하고 이해도가 낮은 기능 중 하나입니다.
이 튜토리얼에서는 VPR 미숙한 사용자가 학습 과정을 최대한 명확하게 할 수 있도록 기본 사항을 최대한 간단하게 설명하려고 노력할 것입니다. 또한 함수의 가장 일반적인 사용 사례를 보여 주는 Excel 수식을 사용하여 몇 가지 예를 연구합니다. VPR.
Excel의 VLOOKUP 함수 – 일반 설명 및 구문
그래서 그것은 무엇입니까 VPR? 우선 엑셀 기능입니다. 그녀는 무엇을합니까? 지정한 값을 조회하고 다른 열에서 해당 값을 반환합니다. 기술적으로 말하자면, VPR 주어진 범위의 첫 번째 열에서 값을 찾고 같은 행의 다른 열에서 결과를 반환합니다.
가장 일반적인 응용 프로그램에서 기능 VPR 주어진 고유 식별자에 대해 데이터베이스를 검색하고 데이터베이스에서 이와 관련된 일부 정보를 추출합니다.
함수 이름의 첫 글자 VPR (VLOOKUP) 의미 В세로 (V세로). 그것으로 당신은 구별 할 수 있습니다 VPR 에 GPR (HLOOKUP), 범위의 맨 위 행에서 값 검색 - Г수평의 (H가로).
함수 VPR Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP 및 Excel 2000에서 사용할 수 있습니다.
VLOOKUP 함수의 구문
함수 VPR (VLOOKUP)의 구문은 다음과 같습니다.
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
보다시피 함수는 VPR Microsoft Excel에는 4가지 옵션(또는 인수)이 있습니다. 처음 XNUMX개는 필수이고 마지막 XNUMX개는 선택 사항입니다.
- lookup_value (lookup_value) – 찾을 값입니다. 값(숫자, 날짜, 텍스트) 또는 셀 참조(조회 값 포함) 또는 다른 Excel 함수에서 반환된 값일 수 있습니다. 예를 들어, 이 수식은 값을 찾습니다. 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
조회 값이 조회 중인 범위의 첫 번째 열에서 가장 작은 값보다 작으면 함수 VPR 오류를 보고할 것입니다 #에서 (#해당 없음).
- table_array (테이블) – 두 개 이상의 데이터 열. 기능을 기억하십시오. VPR 항상 인수에 지정된 범위의 첫 번째 열에서 값을 찾습니다. table_array (테이블). 볼 수 있는 범위에는 텍스트, 날짜, 숫자, 부울과 같은 다양한 데이터가 포함될 수 있습니다. 이 함수는 대소문자를 구분하지 않으므로 대문자와 소문자가 동일하게 간주됩니다. 따라서 공식은 값을 찾습니다. 40 의 세포에서 A2 에 A15, A는 인수에 지정된 A2:B15 범위의 첫 번째 열이기 때문에 table_array (테이블):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- col_index_num (column_number)는 찾은 행의 값이 반환될 지정된 범위의 열 번호입니다. 주어진 범위에서 가장 왼쪽 열은 1, 두 번째 열은 2, 세 번째 열은 3 등등. 이제 전체 공식을 읽을 수 있습니다.
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)가치를 찾는 공식 40 범위 안에 A2 : A15 B 열에서 해당 값을 반환합니다(B가 A2:B15 범위의 두 번째 열이기 때문에).
인수의 값이 col_index_num (column_number) 미만 1그때 VPR 오류를 보고할 것입니다 #값! (#값!). 범위의 열 수보다 많은 경우 table_array (표), 함수는 오류를 반환합니다 # REF! (#링크!).
- 범위_조회 (range_lookup) – 무엇을 찾을지 결정:
- 정확히 일치, 인수는 같아야 합니다. 그릇된 (거짓);
- 근사 일치, 인수 같음 진정한 코드 (TRUE) 또는 전혀 지정되지 않았습니다.
이 매개변수는 선택사항이지만 매우 중요합니다. 이 자습서의 뒷부분에서 VPR 정확하고 근사적으로 일치하는 항목을 찾는 공식을 작성하는 방법을 설명하는 몇 가지 예를 보여 드리겠습니다.
VLOOKUP 예
기능을 바랍니다 VPR 당신에게 조금 더 명확해집니다. 이제 몇 가지 사용 사례를 살펴보겠습니다. VPR 실제 데이터가 있는 공식에서
VLOOKUP을 사용하여 다른 Excel 시트에서 검색하는 방법
실제로 함수가 있는 수식 VPR 동일한 워크시트에서 데이터를 검색하는 데 거의 사용되지 않습니다. 종종 다른 시트에서 해당 값을 찾아 검색합니다.
사용하기 위해서는 VPR, 다른 Microsoft Excel 시트에서 검색, 인수에 있어야 합니다. table_array (표) 셀 범위가 뒤에 오는 느낌표로 시트 이름을 지정합니다. 예를 들어 다음 공식은 범위가 A2 : B15 라는 이름의 시트에 있습니다. Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
물론 시트 이름을 수동으로 입력할 필요는 없습니다. 수식을 입력하기 시작하면 인수가 나오면 table_array (표), 원하는 시트로 전환하고 마우스로 원하는 셀 범위를 선택합니다.
아래 스크린샷에 표시된 수식은 워크시트의 A열(A1:B1 범위의 첫 번째 열)에서 "제품 2"이라는 텍스트를 찾습니다. 학비 안내.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
텍스트 값을 검색할 때는 일반적으로 Excel 수식에서와 같이 따옴표("")로 묶어야 합니다.
인수를 위해 table_array (표) 항상 절대 참조($ 기호 포함)를 사용하는 것이 바람직합니다. 이 경우 수식을 다른 셀에 복사할 때 검색 범위가 변경되지 않은 상태로 유지됩니다.
VLOOKUP으로 다른 통합 문서에서 검색
기능하려면 VPR 두 Excel 통합 문서 사이에서 작업하는 경우 시트 이름 앞에 대괄호 안에 통합 문서 이름을 지정해야 합니다.
예를 들어 아래는 값을 찾는 공식입니다. 40 시트에 Sheet2 책에 숫자.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
다음은 Excel에서 수식을 만드는 가장 쉬운 방법입니다. VPR다른 통합 문서에 대한 링크:
- 두 책을 모두 펼치십시오. 이것은 필수는 아니지만 이 방법으로 수식을 만드는 것이 더 쉽습니다. 통합 문서 이름을 수동으로 입력하고 싶지 않습니까? 또한 실수로 인한 오타로부터 사용자를 보호합니다.
- 함수 입력 시작 VPR그리고 논쟁에 관해서 table_array (표), 다른 통합 문서로 전환하고 필요한 검색 범위를 선택합니다.
아래 스크린샷은 통합 문서에서 검색이 범위로 설정된 수식을 보여줍니다. 가격표.xlsx 시트에 학비 안내.
함수 VPR 검색된 통합 문서를 닫고 통합 문서 파일의 전체 경로가 아래와 같이 수식 입력줄에 나타나는 경우에도 작동합니다.
통합 문서 또는 시트의 이름에 공백이 포함된 경우 아포스트로피로 묶어야 합니다.
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
VLOOKUP을 사용하여 수식에서 명명된 범위 또는 테이블을 사용하는 방법
여러 기능에서 동일한 검색 범위를 사용하려는 경우 VPR, 명명된 범위를 만들고 해당 이름을 수식에 인수로 입력할 수 있습니다. table_array (테이블).
명명된 범위를 만들려면 셀을 선택하고 필드에 적절한 이름을 입력하기만 하면 됩니다. 영문 이름, 수식 입력줄 왼쪽에 있습니다.
이제 제품 가격을 찾기 위해 다음 공식을 쓸 수 있습니다. 제품 1:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
대부분의 범위 이름은 전체 Excel 통합 문서에서 작동하므로 인수에 대한 시트 이름을 지정할 필요가 없습니다. table_array (표), 수식과 검색 범위가 다른 워크시트에 있는 경우에도 마찬가지입니다. 다른 통합 문서에 있는 경우 범위 이름 앞에 통합 문서의 이름을 지정해야 합니다. 예를 들면 다음과 같습니다.
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
공식이 훨씬 더 명확해 보이죠? 또한 수식을 다른 셀에 복사할 때 명명된 범위가 변경되지 않기 때문에 명명된 범위를 사용하는 것은 절대 참조에 대한 좋은 대안입니다. 즉, 수식의 검색 범위가 항상 올바른 상태를 유지할 수 있습니다.
명령을 사용하여 셀 범위를 본격적인 Excel 스프레드시트로 변환하는 경우 작업대 (표) 탭 삽입 (삽입)을 선택한 다음 마우스로 범위를 선택하면 Microsoft Excel에서 자동으로 열 이름(또는 전체 테이블을 선택하는 경우 테이블 이름)을 수식에 추가합니다.
완성된 수식은 다음과 같습니다.
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
또는 다음과 같이 할 수도 있습니다.
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
명명된 범위를 사용할 때 링크는 함수를 복사하는 위치에 관계없이 동일한 셀을 가리킵니다. VPR 통합 문서 내에서.
VLOOKUP 수식에서 와일드카드 사용
다른 많은 기능과 마찬가지로, VPR 다음 와일드카드 문자를 사용할 수 있습니다.
- 물음표(?) – 단일 문자를 대체합니다.
- 별표(*) – 모든 문자 시퀀스를 대체합니다.
함수에서 와일드카드 사용 VPR 많은 경우에 유용할 수 있습니다. 예를 들면 다음과 같습니다.
- 당신이 찾아야 할 텍스트를 정확히 기억하지 못할 때.
- 셀 내용의 일부인 단어를 찾고 싶을 때. 알고 VPR 옵션이 활성화된 것처럼 전체 셀의 내용으로 검색 전체 셀 내용 일치 (전체 셀) 표준 Excel 검색.
- 셀 내용의 시작 또는 끝에 추가 공백이 있는 경우. 그러한 상황에서 공식이 작동하지 않는 이유를 알아 내려고 오랫동안 두뇌를 쌓을 수 있습니다.
예 1: 특정 문자로 시작하거나 끝나는 텍스트 찾기
아래 표시된 데이터베이스에서 특정 고객을 검색하려고 한다고 가정해 보겠습니다. 그의 성은 기억나지 않지만 "ack"으로 시작한다는 것은 알고 있습니다. 다음은 작업을 잘 수행할 수 있는 공식입니다.
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
이제 정확한 이름을 찾았으므로 동일한 공식을 사용하여 이 고객이 지불한 금액을 찾을 수 있습니다. 이렇게 하려면 함수의 세 번째 인수를 변경하면 됩니다. VPR 원하는 열 번호로 우리의 경우 이것은 C열(범위에서 3번째)입니다.
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
다음은 와일드카드를 사용한 몇 가지 예입니다.
~ "man"으로 끝나는 이름 찾기:
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ "ad"로 시작하고 "son"으로 끝나는 이름 찾기:
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ 목록에서 5자로 구성된 이름을 찾습니다.
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
기능하려면 VPR 와일드 카드를 사용하면 항상 사용해야 하는 네 번째 인수로 올바르게 작동했습니다. 그릇된 (거짓). 검색 범위에 와일드카드가 있는 검색어와 일치하는 값이 두 개 이상 있으면 처음으로 찾은 값이 반환됩니다.
예 2: VLOOKUP 수식에서 와일드카드와 셀 참조 결합
이제 함수를 사용하여 검색하는 방법에 대한 약간 더 복잡한 예를 살펴보겠습니다. VPR 셀의 값으로. A 열이 라이센스 키 목록이고 B 열이 라이센스를 소유한 이름 목록이라고 가정해 보겠습니다. 또한 C1 셀에 라이센스 키의 일부(여러 문자)가 있고 소유자의 이름을 찾고 싶습니다.
이것은 다음 공식을 사용하여 수행할 수 있습니다.
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
이 수식은 지정된 범위의 C1 셀에서 값을 조회하고 B열에서 해당 값을 반환합니다. 첫 번째 인수에서는 셀 참조 앞뒤에 앰퍼샌드(&) 문자를 사용하여 텍스트 문자열을 연결합니다.
아래 그림에서 볼 수 있듯이 기능은 VPR 라이센스 키에 셀 C1의 문자 시퀀스가 포함되어 있기 때문에 "Jeremy Hill"을 반환합니다.
인수에 유의하십시오. table_array 위 스크린샷의 (table)은 셀의 범위를 지정하는 대신 테이블의 이름(Table7)을 포함합니다. 이것은 이전 예제에서 수행한 작업입니다.
VLOOKUP 함수에서 정확히 일치하거나 근사적으로 일치
마지막으로 함수에 대해 지정된 마지막 인수를 자세히 살펴보겠습니다. VPR - 범위_조회 (간격_보기). 수업 시작 부분에서 언급했듯이 이 주장은 매우 중요합니다. 동일한 수식에서 값과 완전히 다른 결과를 얻을 수 있습니다. 진정한 코드 (참) 또는 그릇된 (거짓).
먼저 Microsoft Excel에서 정확한 일치와 대략적인 일치가 무엇을 의미하는지 알아보겠습니다.
- 만약 인수 범위_조회 (range_lookup)은 다음과 같습니다. 그릇된 (FALSE), 수식은 정확히 일치하는 항목을 찾습니다. 즉, 인수에 제공된 것과 정확히 동일한 값입니다. lookup_value (lookup_value). 범위 t의 첫 번째 열에 있는 경우가능_배열 (테이블) 인수와 일치하는 두 개 이상의 값을 만납니다. lookup_value (search_value), 첫 번째 값이 선택됩니다. 일치하는 항목이 없으면 함수에서 오류를 보고합니다. #에서 (#해당 없음). 예를 들어 다음 수식은 오류를 보고합니다. #에서 (#N/A) A2:A15 범위에 값이 없는 경우 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - 만약 인수 범위_조회 (range_lookup)은 다음과 같습니다. 진정한 코드 (TRUE), 수식은 대략적인 일치를 찾습니다. 보다 정확하게는 먼저 기능 VPR 정확히 일치하는 항목을 찾고 일치하는 항목이 없으면 대략적인 항목을 선택합니다. 근사 일치는 인수에 지정된 값을 초과하지 않는 가장 큰 값입니다. lookup_value (조회_값).
만약 인수 범위_조회 (range_lookup)은 다음과 같습니다. 진정한 코드 (TRUE) 또는 지정되지 않은 경우 범위의 첫 번째 열에 있는 값은 오름차순, 즉 가장 작은 것에서 큰 것 순으로 정렬되어야 합니다. 그렇지 않으면 기능 VPR 잘못된 결과를 반환할 수 있습니다.
선택의 중요성을 더 잘 이해하기 위해 진정한 코드 (진실) 또는 그릇된 (FALSE), 함수가 있는 몇 가지 공식을 더 살펴보겠습니다. VPR 그리고 결과를 보세요.
예 1: VLOOKUP으로 정확히 일치하는 항목 찾기
정확히 일치하는 항목을 검색하려면 함수의 네 번째 인수인 VPR 중요해야 그릇된 (거짓).
첫 번째 예의 표로 돌아가서 어떤 동물이 속도로 움직일 수 있는지 알아봅시다. 50 시간 당 마일. 나는 이 공식이 당신에게 어떤 어려움도 일으키지 않을 것이라고 믿습니다:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
검색 범위(A열)에는 두 개의 값이 포함되어 있습니다. 50 – 세포에서 A5 и A6. 수식은 셀에서 값을 반환합니다. B5. 왜요? 정확히 일치하는 항목을 찾을 때 함수가 VPR 검색되는 값과 일치하는 첫 번째 값을 사용합니다.
예 2: VLOOKUP을 사용하여 대략적인 일치 찾기
기능을 사용할 때 VPR 대략적인 일치를 찾기 위해, 즉 인수가 범위_조회 (range_lookup)은 다음과 같습니다. 진정한 코드 (TRUE) 또는 생략된 경우 가장 먼저 해야 할 일은 범위를 첫 번째 열을 기준으로 오름차순으로 정렬하는 것입니다.
기능 때문에 매우 중요합니다. VPR 주어진 값 다음으로 가장 큰 값을 반환한 다음 검색이 중지됩니다. 올바른 정렬을 게을리하면 매우 이상한 결과나 오류 메시지가 표시됩니다. #에서 (#해당 없음).
이제 다음 공식 중 하나를 사용할 수 있습니다.
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
보시다시피, 나는 동물 중 어느 동물이 가장 빠른 속도를 가지고 있는지 찾고 싶습니다. 69 시간 당 마일. 그리고 여기에 함수가 나에게 반환한 결과가 있습니다. VPR:
보시다시피 수식은 결과를 반환했습니다. 영양 (영양), 그의 속도 61 목록에는 다음이 포함되지만 시간당 마일 Cheetah 빠른 속도로 달리는 (치타) 70 시간당 마일이고 70은 69보다 61에 가깝습니다. 왜 이런 일이 발생합니까? 기능 때문에 VPR 근사적으로 일치하는 항목을 검색할 때 검색되는 값보다 크지 않은 가장 큰 값을 반환합니다.
이 예제가 함수 작업에 대한 정보를 제공하기를 바랍니다. VPR Excel에서는 더 이상 그녀를 외부인으로 보지 않습니다. 이제 메모리에 더 잘 고정하기 위해 우리가 공부한 자료의 요점을 간단히 반복하는 것이 나쁘지 않습니다.
Excel의 VLOOKUP – 이것을 기억해야 합니다!
- 함수 VPR 엑셀이 왼쪽으로 보이지 않습니다. 항상 인수로 지정된 범위의 가장 왼쪽 열에서 값을 찾습니다. table_array (테이블).
- 기능 VPR 모든 값은 대소문자를 구분하지 않습니다. 즉, 대소문자가 동일합니다.
- 찾고 있는 값이 찾고 있는 범위의 첫 번째 열에 있는 최소값보다 작으면 함수 VPR 오류를 보고할 것입니다 #에서 (#해당 없음).
- 세 번째 인수인 경우 col_index_num (column_number) 미만 1기능 VPR 오류를 보고할 것입니다 #값! (#값!). 범위의 열 수보다 큰 경우 table_array (표), 함수는 오류를 보고합니다 # REF! (#링크!).
- 인수에 절대 셀 참조 사용 table_array (표) 수식을 복사할 때 올바른 검색 범위가 유지되도록 합니다. 대안으로 Excel에서 명명된 범위 또는 표를 사용해 보십시오.
- 대략적인 일치 검색을 수행할 때 찾고 있는 범위의 첫 번째 열이 오름차순으로 정렬되어야 함을 기억하십시오.
- 마지막으로 네 번째 인수의 중요성을 기억하십시오. 값 사용 진정한 코드 (진실) 또는 그릇된 (FALSE) 고의적으로 많은 두통을 없앨 수 있습니다.
함수 자습서의 다음 기사에서 VPR Excel에서는 다음을 사용하여 다양한 계산을 수행하는 것과 같은 고급 예제를 배웁니다. VPR, 여러 열에서 값 추출 등. 이 튜토리얼을 읽어주셔서 감사합니다. 다음 주에 다시 뵙기를 바랍니다!