내용
이 단원에서는 함수가 VPR (VLOOKUP)은 Excel 2013, 2010, 2007 및 2003에서 작동하고 싶지 않으며 일반적인 오류를 식별 및 수정하고 한계를 극복하는 방법 VPR.
여러 이전 기사에서 우리는 함수의 다양한 측면을 탐구했습니다. VPR 엑셀에서. 주의 깊게 읽었다면 이제 이 분야의 전문가가 되셨을 것입니다. 그러나 많은 엑셀 전문가들이 믿는 데는 이유가 있다. VPR 더 복잡한 기능 중 하나입니다. 그것은 많은 문제와 오류의 원인이되는 많은 제한 사항과 기능을 가지고 있습니다.
이 기사에서는 오류에 대한 간단한 설명을 찾을 수 있습니다. #에서 (#해당 없음), # NAME? (#NAME?) 및 #값! 함수로 작업할 때 나타나는 (#VALUE!) VPR, 그리고 그것들을 다루는 기술과 방법. 가장 일반적인 경우와 가장 분명한 이유부터 시작하겠습니다. VPR 작동하지 않으므로 기사에 제공된 순서대로 예제를 연구하는 것이 좋습니다.
Excel의 VLOOKUP 함수에서 #N/A 오류 수정
수식에서 VPR 오류 메시지 #에서 (#N/A) 의미 사용할 수 없습니다 (데이터 없음) – Excel에서 원하는 값을 찾을 수 없을 때 나타납니다. 이것은 여러 가지 이유로 발생할 수 있습니다.
1. 원하는 값의 철자가 잘못되었습니다.
이 항목을 먼저 확인하는 것이 좋습니다! 수천 줄로 구성된 매우 많은 양의 데이터로 작업하거나 찾고 있는 값을 수식에 작성할 때 오타가 자주 발생합니다.
2. VLOOKUP으로 근사치 검색 시 #N/A 오류
근사 일치 검색 조건, 즉 인수가 있는 수식을 사용하는 경우 범위_조회 (range_lookup)이 TRUE이거나 지정되지 않은 경우 수식이 오류를 보고할 수 있습니다. # N / A 두 가지 경우:
- 조회할 값이 조회 중인 배열에서 가장 작은 값보다 작습니다.
- 검색 열은 오름차순으로 정렬되지 않습니다.
3. VLOOKUP과 정확히 일치하는 항목을 찾을 때 #N/A 오류
정확히 일치하는 항목, 즉 인수를 찾고 있는 경우 범위_조회 (range_lookup)이 FALSE이고 정확한 값을 찾을 수 없으면 수식에서도 오류를 보고합니다. # N / A. 함수를 사용하여 정확하고 근사적으로 일치하는 항목을 검색하는 방법에 대해 자세히 알아보기 VPR.
4. 검색 열이 맨 왼쪽이 아닙니다.
아시다시피 가장 중요한 제한 사항 중 하나는 VPR 왼쪽을 향할 수 없기 때문에 테이블의 조회 열이 맨 왼쪽에 있어야 합니다. 실제로, 우리는 종종 이것을 잊어버려 작동하지 않는 공식과 오류로 이어집니다. # N / A.
결정: 검색 열이 가장 왼쪽에 있도록 데이터 구조를 변경할 수 없는 경우 함수의 조합을 사용할 수 있습니다. INDEX (색인) 및 더 많이 노출 (MATCH)에 대한보다 유연한 대안으로 VPR.
5. 숫자는 텍스트로 형식이 지정됩니다.
또 다른 오류 소스 # N / A 수식에서 VPR 기본 테이블 또는 조회 테이블에 있는 텍스트 형식의 숫자입니다.
이것은 일반적으로 외부 데이터베이스에서 정보를 가져오거나 선행 XNUMX을 유지하기 위해 숫자 앞에 아포스트로피를 입력할 때 발생합니다.
텍스트 형식의 숫자의 가장 명백한 표시는 아래 그림에 나와 있습니다.
또한 숫자를 형식으로 저장할 수 있습니다. 일반 (일반적인). 이 경우 눈에 띄는 기능은 하나뿐입니다. 숫자는 셀의 왼쪽 가장자리에 정렬되지만 기본적으로 오른쪽 가장자리에 정렬됩니다.
결정: 단일 값인 경우 오류 아이콘을 클릭하고 숫자로 변환 컨텍스트 메뉴에서 (숫자로 변환).
숫자가 많은 상황이라면 숫자를 선택하고 선택한 영역을 마우스 오른쪽 버튼으로 클릭합니다. 나타나는 컨텍스트 메뉴에서 셀 서식 (셀 서식) > 탭 번호 (숫자) > 형식 번호 (숫자)를 누르고 OK.
6. 시작이나 끝에 공백이 있습니다.
이것은 오류에 대한 가장 명백한 이유입니다. # N / A 기능상 VPR, 특히 대부분의 데이터가 화면 밖에 있을 때 큰 테이블로 작업할 때 이러한 추가 공간을 보기가 시각적으로 어렵습니다.
해결 방법 1: 기본 테이블의 추가 공백(VLOOKUP 함수가 있는 곳)
기본 테이블에 추가 공백이 있는 경우 인수를 묶어 수식이 올바르게 작동하는지 확인할 수 있습니다. lookup_value (lookup_value) 함수로 정돈 (손질):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
해결 방법 2: 조회 테이블의 추가 공백(조회 열)
검색 열에 추가 공백이 있는 경우 – 간단한 방법 # N / A 공식에서 VPR 피할 수 없습니다. 대신에 VPR 함수 조합과 함께 배열 수식을 사용할 수 있습니다. INDEX (인덱스), 더 많이 노출 (매치) и 정돈 (손질):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
이것은 배열 수식이므로 누르는 것을 잊지 마십시오. Ctrl + Shift + Enter 평소 대신 엔터 버튼수식을 올바르게 입력합니다.
오류 #VALUE! VLOOKUP이 있는 수식에서
대부분의 경우 Microsoft Excel에서 오류를 보고합니다. #값! (#VALUE!) 수식에 사용된 값이 데이터 유형과 일치하지 않는 경우. 에 관하여 VPR, 오류에는 일반적으로 두 가지 이유가 있습니다. #값!.
1. 찾고 있는 값이 255자보다 깁니다.
주의: 기능 VPR 255자를 초과하는 값은 검색할 수 없습니다. 찾고 있는 값이 이 제한을 초과하면 오류 메시지가 표시됩니다. #값!.
결정: 다양한 기능 사용 인덱스+매치 (색인 + 일치). 다음은 이 작업에 적합한 공식입니다.
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. 검색 통합 문서의 전체 경로가 지정되지 않았습니다.
다른 통합 문서에서 데이터를 검색하는 경우 해당 파일의 전체 경로를 지정해야 합니다. 보다 구체적으로, 통합 문서 이름(확장명 포함)을 대괄호 [ ] 안에 포함하고 그 뒤에 시트 이름, 느낌표가 와야 합니다. 책이나 시트 이름에 공백이 포함된 경우 이 모든 구성은 아포스트로피로 묶어야 합니다.
다음은 함수의 전체 구조입니다. VPR 다른 책에서 검색하려면:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
실제 공식은 다음과 같습니다.
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
이 수식은 셀 값을 조회합니다 A2 열에 B 시트에 Sheet1 통합 문서에서 새로운 가격 열에서 해당 값을 추출합니다. D.
테이블 경로의 일부가 생략되면 함수 VPR 작동하지 않고 오류를 보고합니다. #값! (룩업 테이블이 있는 통합 문서가 현재 열려 있는 경우에도).
기능에 대한 자세한 내용은 VPR다른 Excel 파일을 참조하려면 VLOOKUP을 사용하여 다른 통합 문서 검색 단원을 참조하십시오.
3. Column_num 인수가 1보다 작습니다.
누군가가 다음보다 작은 값을 입력하는 상황을 상상하기 어렵습니다. 1값을 추출할 열을 나타냅니다. 이 인수의 값이 내부에 중첩된 다른 Excel 함수에 의해 계산되는 경우 가능하지만 VPR.
그래서 만약 그 논쟁이 일어난다면 col_index_num (column_number) 미만 1기능 VPR 또한 오류를 보고합니다 #값!.
만약 인수 col_index_num (column_number)가 지정된 배열의 열 수보다 큽니다. VPR 오류를 보고할 것입니다 # REF! (#SSYL!).
오류 #NAME? VLOOKUP에서
가장 간단한 경우는 실수입니다. # NAME? (#NAME?) – 실수로 함수 이름을 오류와 함께 쓰는 경우 나타납니다.
해결책은 분명합니다. 철자를 확인하십시오!
VLOOKUP이 작동하지 않음(제한 사항, 주의 사항 및 결정)
다소 복잡한 구문 외에도, VPR 다른 Excel 기능보다 더 많은 제한이 있습니다. 이러한 제한으로 인해 겉보기에 간단한 공식이 VPR 종종 예상치 못한 결과를 초래합니다. 아래에서 몇 가지 일반적인 시나리오에 대한 솔루션을 찾을 수 있습니다. VPR 잘못되었습니다.
1. VLOOKUP은 대소문자를 구분하지 않습니다.
함수 VPR 대소문자를 구분하지 않으며 소문자와 대문자를 동일하게 받아들입니다. 따라서 테이블에 대소문자만 다른 요소가 여러 개 있는 경우 VLOOKUP 함수는 대소문자에 관계없이 발견된 첫 번째 요소를 반환합니다.
결정: 수직 검색(LOOKUP, SUMPRODUCT, INDEX, MATCH)을 수행할 수 있는 다른 Excel 기능을 다음과 조합하여 사용하십시오. 정확한경우를 구별하는 A. 자세한 내용은 강의에서 배울 수 있습니다. Excel에서 VLOOKUP을 대소문자를 구분하는 4가지 방법.
2. VLOOKUP은 찾은 첫 번째 값을 반환합니다.
아시다시피 VPR 찾은 첫 번째 일치 항목에 해당하는 지정된 열의 값을 반환합니다. 그러나 원하는 값의 두 번째, 세 번째, 네 번째 또는 기타 반복을 추출하도록 할 수 있습니다. 모든 중복 값을 추출해야 하는 경우 기능 조합이 필요합니다. INDEX (인덱스), 가장 작은 (작은) 그리고 LINE (열).
3. 테이블에 열이 추가되거나 제거되었습니다.
불행히도 공식 VPR 새 열이 조회 테이블에 추가되거나 제거될 때마다 작동을 중지합니다. 이것은 구문 때문에 발생합니다 VPR 검색의 전체 범위와 데이터 추출을 위한 특정 열 번호를 지정해야 합니다. 당연히 열을 삭제하거나 새 열을 삽입하면 지정된 범위와 열 번호가 모두 변경됩니다.
결정: 그리고 다시 기능이 도움이됩니다. INDEX (색인) 및 더 많이 노출 (성냥). 공식에서 인덱스+매치 검색 열과 조회 열을 별도로 정의하여 관련 검색 수식을 모두 업데이트할 필요 없이 원하는 만큼의 열을 삭제하거나 삽입할 수 있습니다.
4. 수식을 복사할 때 셀 참조가 깨짐
이 제목은 문제의 본질을 철저하게 설명하고 있습니다. 맞습니까?
결정: 항상 절대 셀 참조를 사용하십시오(기호 $) 레코드 범위, 예를 들어 $ A $ 2 : $ C $ 100 or $ A : $ C. 수식 입력줄에서 F4.
VLOOKUP – IFERROR 및 ISERROR 기능 사용
오류 메시지로 사용자를 놀라게 하고 싶지 않다면 # N / A, #값! or # NAME?, 빈 셀이나 자신의 메시지를 표시할 수 있습니다. 배치하여이 작업을 수행 할 수 있습니다. VPR 함수로 오류 (IFERROR) Excel 2013, 2010 및 2007 또는 여러 기능 사용 IF+ISERROR (IF+ISERROR) 이전 버전에서.
VLOOKUP: IFERROR 함수로 작업
함수 구문 오류 (IFERROR)는 간단하고 자체적으로 말합니다.
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
즉, 첫 번째 인수에 대해 오류를 검사할 값을 삽입하고 두 번째 인수에 대해 오류가 발견되면 반환할 내용을 지정합니다.
예를 들어, 다음 수식은 찾고 있는 값을 찾을 수 없는 경우 빈 셀을 반환합니다.
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
함수의 표준 오류 메시지 대신 사용자 고유의 메시지를 표시하려는 경우 VPR, 다음과 같이 따옴표로 묶습니다.
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: ISERROR 함수로 작업
기능부터 오류 Excel 2007에 나타났습니다. 이전 버전에서 작업할 때 조합을 사용해야 합니다. IF (만약) 그리고 에오십카 (ISERROR) 다음과 같이:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
예를 들어, 공식 IF+ISERROR+조회, 공식과 유사 IFERROR + 조회위에 표시된:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
오늘은 그게 다야. 이 짧은 튜토리얼이 가능한 모든 실수를 처리하는 데 도움이 되기를 바랍니다. VPR 수식이 올바르게 작동하도록 합니다.