Excel의 Vlookup- 어떤 공식이 가장 빠릅니까?

오늘, 우리는 여러 가지 공식으로 Excel에서 Vlookup을 수행하고 계산 속도를 측정하고 일관성을 평가하므로 승자를 선택할 수 있습니다.

다른 테이블에서 정보를 찾는 것은 Excel에서 가장 일반적인 작업 중 하나입니다. 유감스럽게도, 클래식 VLOOKUP 기능은 처리 능력과 유연성을 처리 할 때 악명 높습니다. 수년에 걸쳐 Excel 사용자가 인덱스 매치 공식과 같은 자체 솔루션을 제시 한 것은 놀라운 일이 아닙니다. 운 좋게도 Microsoft는 Vlookup이 너무 많은 약점을 가지고 있으며 더 강력한 후속 인 Xlookup 기능을 발표했다는 것을 마침내 깨달았습니다. 그렇다면 어떤 것이 사용하기에 가장 적합합니까?

테이블에 수십 개의 행만있는 경우 대부분의 방법이 충분히 빠르며 차이를 알지 못할 것입니다. 그러나 행의 수가 수천으로 측정되면 기능의 올바른 선택이 중요합니다. 성능 차이는 10 배 이상일 수 있습니다! 자, 타이머를 시작하고 누가 가장 빠른지 확인합시다 :)

• 소스 데이터
• vlookup 기능
• 인덱스 매치 공식
• 오프셋 일치 공식
• xlookup 함수
• Excel 테이블의 Vlookup
• 동적 배열이있는 vlookup
• 전원 쿼리와 일치하는 일치
• 추가 보너스 : 병합 테이블 마법사
• 요약 및 결론

소스 데이터

모든 예에서, 우리는 다음 데이터 세트를 잘 사용하고 있습니다.

• 50 만 행이 포함 된 메인 테이블
• 500 행이 포함 된 조회 테이블

우리의 목표는 항목 이름과 일치하고 조회 테이블에서 메인 테이블로 가격을 가져 오는 것입니다.

각 방법에 대해 C2의 공식을 입력하여 C500001을 통해 복사하여 50 만 셀을 계산하는 데 탁월한 시간을 측정합니다.

당연히 결과는 CPU 성능, RAM 크기, Excel 버전 등과 같은 많은 요소에 따라 달라집니다.이 실험에서는 절대적인 숫자가 아닙니다. 비교하여 각 공식의 성능, 장점 및 단점을 이해하는 것이 더 중요합니다.

우리의 예는 당신이 기능에 대한 기본 지식을 가지고 있다고 가정하며, 우리는 그들의 구문에 크게 머물지 않을 것입니다. 편의를 위해 심층 자습서에 대한 링크가 포함되어 있습니다.

모든 테스트는 Dell 노트북 (Intel Core i5-8250U; RAM 16GB)에서 수행되었습니다. Excel 32 비트, 버전 2011, 빌드 13415, 베타 채널. Excel 64 비트에서 동료가 동일한 테스트를 수행했으며 일부 결과는 크게 다릅니다!

vlookup 공식

Excel에서 일치하는 데이터를 찾고 검색 할 때 가장 먼저 떠오르는 기능은 좋은 오래된 Vlookup입니다. 사실, 그것은 그렇게 좋지는 않지만, 우리는 이것을 조금 나중에 이야기 할 것입니다 :)

vlookup (lookup_value, table_array, col_index_num, [range_lookup])

조회 테이블 (E2 : F501)에서 메인 테이블 (A2 : B500001)으로 가격을 가져 오려면 Vlookup 공식에 대한 다음 인수를 정의합니다.

• Lookup_Value : B2- 조회 테이블에서 검색 할 값.
• table_array : $ e $ 3 : $ F $ 501- 공식을 복사 할 때 이동하지 않도록 $ 부호로 참조를 잠그십시오.
• col_index_num : 2- 데이터는 조회 테이블의 ² 열에서 검색해야합니다.
• Range_Lookup : False- 정확한 일치를 찾고 있습니다.

완전한 공식은 다음과 같습니다.

=VLOOKUP(B2, $E$3:$F$501, 2, FALSE)

위의 공식은 C2로 이동 한 다음 오른쪽 하단의 플러스 부호를 두 번 클릭하여 전체 열의 공식을 복사합니다. 두 번 클릭하는 순간, 우리는 스톱워치를 실행 하고이 계산에 6.6 초가 걸린다는 것을 알 수 있습니다.

실제 워크 시트에서 Vlookup을 수행 할 때 많은 사람들이 Table_Array 의 전체 열을 제공하여 향후 추가 할 수 있습니다.

=VLOOKUP(B2, E:F, 2, FALSE)

성능에 영향을 미칩니 까? 예, 그렇습니다. 전체 열을 계산하는 데 14.2 초가 걸립니다. 믿기 ​​어려웠으므로 두 번 확인했습니다. 동일한 결과 - 범위의 속도의 절반 미만.

신속한 것 외에도, 당신의 공식은 강력하고 내구성이 있어야합니까? 유감스럽게도 Vlookup은 신뢰성과 탄력성을 자랑 할 수 없습니다.

vlookup 제한

이미 언급했듯이 Excel Vlookup에는 여러 가지 자극적 인 제약이 있습니다. 가장 중요한 것은 다음과 같습니다.

• 왼쪽을 볼 수 없습니다. Vlookup 함수는 테이블 배열의 가장 왼쪽 열에서만 살펴보고 오른쪽에서 정보를 반환 할 수 있습니다.
• 열 삽입 또는 삭제에서 생존하지 않습니다. 리턴 열은 색인 번호로 지정되므로 새 열이 추가되거나 테이블 배열에서 제거 되 자마자 VLOOKUP 공식이 작동을 중지합니다.
• 조회 값은 255 자로 제한됩니다.

계산 시간 : 범위 -6.6 초; 전체 열 -14.2 초.

심층 튜토리얼 : 초보자를위한 Excel Vlookup 예제

인덱스 매치 공식

많은 사용자들에게 Excel에서 고급 형태의 조회는 마법 색인 매치 공식입니다. 일반적인 형태로 다음과 같습니다.

index ( return_column , match ( lookup_value , lookup_column , 0)))

데이터 세트의 경우 공식 이이 형식을 취합니다.

=INDEX($F$2:$F$501, MATCH(B2, $E$2:$E$501, 0))

Vlookup과 마찬가지로 조회 및 반환 범위에 절대 참조를 사용하여 공식이 아래 셀에 올바르게 복사되도록하십시오.

동일한 시트에 두 테이블이 두 테이블을 사용하면 인덱스 매치는 Vlookup보다 훨씬 느리게 수행되었습니다 (6.6 초에 8.9).

그러나 조회 테이블을 다른 워크 시트 로 옮기면 공식이 훨씬 빠르게 작동하기 시작합니다 (~ 5 초). 이는 Vlookup보다 낫습니다.

어쨌든, 인덱스 매치는 여러 가지 중요한 이점으로 시간을 보완합니다.

인덱스 매치 장점

• 오른쪽에서 왼쪽으로 볼 수 있습니다. 예, 인덱스 매치 공식은 조회 열이 어디에 있는지 신경 쓰지 않습니다. Vlookup과 달리 테이블 배열 대신 조회 범위를 명시 적으로 정의하기 때문입니다.
• 컬럼 삽입 및 결실에 면역. 인덱스 일치를 사용하면 인덱스 번호가 아닌 리턴 범위를 지정하기 때문에 열을 안전하게 추가하고 제거 할 수 있습니다.
• 조회 값의 크기에 대한 제한이 없습니다. Vlookup은 255 자로 제한되지만 인덱스 매치는 더 긴 문자열 처리에 아무런 문제가 없습니다.
• 위의 링크 된 예와 같이 여러 기준으로 Vlookup을 수행 할 수 있습니다.
• 2 차원 조회를 수행하고 특정 행과 열의 교차점에서 값을 반환 할 수 있습니다.

계산 시간 : 범위 -8.9 초; 전체 열 -17.7 초; 다른 시트에서 -5.2 초.

심층 튜토리얼 : Excel의 인덱스 매치 공식

오프셋 일치 공식

여기에 Vlookup의 많은 한계가없는 Excel에서 수직으로 볼 수있는 공식이 하나 더 있습니다.

오프셋 ( lookup_table , match ( lookup_value , 오프셋 ( lookup_table , 0, n , 행 ( lookup_table ), 1), 0) -1, m , 1, 1)

어디:

• n- 테이블의 시작에서 조회 열로 이동할 열의 수를 지정하는 조회 열 오프셋입니다.
• M- 리턴 열로 이동할 열의 수를 결정하는 리턴 열 오프셋입니다.

우리의 경우, 첫 번째 열에서 검색하기 때문에 조회 열 오프셋 (n)은 0이므로 시프트가 필요하지 않습니다. 일치가 두 번째 열에 있기 때문에 리턴 열 오프셋 (m)은 1이므로 1 열을 오른쪽으로 이동해야합니다.

=OFFSET($E$2:$F$501, MATCH(B2, OFFSET($E$2:$F$501, 0, 0, ROWS($E$2:$F$501), 1), 0) -1, 1, 1, 1)

이전 솔루션과 비교할 때 공식은 너무 번거 롭습니다. 그러나 Vlookup 또는 Index Match보다 훨씬 빠릅니다. 5 억 행을 계산하는 데 3 초 미만이 걸립니다! 전체 열에서 오프셋은 약간 느리게 3.5 초입니다.

=OFFSET(E:F, MATCH(B2, OFFSET(E:F, 0, 0, ROWS(E:F), 1), 0) -1, 1, 1, 1)

그러나 Excel 64 비트에서는 결과가 약 7.5 초 정도 인상적이지 않습니다. 왜 그게? Microsoft Guys에게 좋은 질문 :)

오프셋 일치 장점

속도 외에도이 공식에는 몇 가지 다른 장점이 있습니다.

• 오른쪽에서 왼쪽으로 vlookup과 어퍼 hlookup을 수행 할 수 있습니다.
• 열 및 행 값을 기반으로 양방향 조회를 수행 할 수 있습니다.
• 열이 조회 테이블에 삽입되거나 삭제되면 파손되지 않습니다.

오프셋 일치 단점

복잡한 구문.

계산 시간 : 범위 -2.9 초; 전체 열 -3.5 초.

심층 자습서 :

• 공식 예제와 함께 오프셋 함수
• Excel에서 일치 함수를 사용하는 방법

xlookup 공식

Microsoft 365 가입자는 워크 시트에서 정보를 찾을 수있는 새롭고 강력한 기능을 제공합니다.

XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])

우리의 목적을 위해, 마지막 3 개의 인수의 기본값은 잘 작동하므로 처음 3 개의 매개 변수 만 지정합니다. 인수의 이름은 직관적이며 추가 설명없이 공식을 이해할 수 있다고 생각합니다.

=XLOOKUP(B2, $E$2:$E$501, $F$2:$F$501)

xlookup 장점

전통적인 vlookup과 비교하여 Xlookup 함수는 다음과 같은 많은 개선 사항이 있습니다.

• 단순화되고 더 의미있는 구문
• 오른쪽, 왼쪽, 하단 또는 위로 어떤 방향 으로든 수직 및 수평으로 찾는 능력.
• 정렬 된 데이터의 경우 일반 검색보다 훨씬 빠른 특수 바이너리 검색 모드가 있습니다.
• 마지막 발생을 얻으려면 역순으로 검색하십시오.
• 여러 값을 반환하는 능력.
• Excel Xlookup에서 여러 기준으로 설명 된대로 둘 이상의 조건을 처리하십시오.
• 오류 기능이 내장되어 있습니다.

xlookup 단점

Xlookup은 Excel 365 및 2021에서만 제공됩니다. Excel 2019, Excel 2016 및 이전 버전에서는 지원되지 않습니다.

그리고 지금,이 새로운 기능이 얼마나 빠른지 봅시다. 11.2 초 - 상당히 실망 :(

범위 대신 열 참조를 사용하면 어떻게됩니까? =XLOOKUP(B2, E:E, F:F)

24.5 초. 말도 없습니다… Vlookup의 거의 두 배나 느립니다.

계산 시간 : 범위 -11.2 초; 전체 열 -24.1 초.

포괄적 인 튜토리얼 : Excel Xlookup 기능이 있습니다

Excel 테이블의 Vlookup

아시다시피, Excel 테이블의 데이터는 셀 주소 대신 테이블 및 열 이름을 사용하여 특별한 방식으로 참조 할 수 있습니다. 이것을 구조화 된 참조 라고하며 계산 속도에 영향을 미치는지 궁금합니다.

이를 확인하려면 범위를 테이블로 변환하고 공식을 다시 테스트합시다.

편의를 위해 테이블은 main_table (a1 : c500001) 및 lookup_table (e1 : f5001)이라고합니다.

테이블 참조를 만들려면 첫 번째 셀 (C2)에서 공식을 입력하기 시작하고 참조하려는 셀과 범위를 선택하면 Excel이 구조화 된 참조를 자동으로 삽입합니다.

예를 들어 Vlookup 공식의 모습은 다음과 같습니다.

=VLOOKUP([@Item], Lookup_table, 2, FALSE)

Excel 테이블의 훌륭한 특징은 단 하나의 셀로 공식으로 들어가 자마자 동일한 열의 다른 모든 셀에 즉시 채워진다는 것입니다. 게다가, 테이블은 본질적으로 역동적이며 테이블 옆에 입력 할 새로운 데이터를 포함하도록 자동으로 확장됩니다.

표에서 VLOOKUP 공식은 2.3 초 안에 계산되었고, 2.6 초 만에 인덱스 일치, 2.7 초 만에 오프셋 및 일치, 3.3 초 안에 Xlookup이 계산되었습니다. 보시다시피, 계산 속도는 범위에 비해 크게 증가합니다.

공식은 참조를 위해 아래에 나열되어 있습니다.

=INDEX(Lookup_table[Price], MATCH([@Item], Lookup_table[Item], 0))

=OFFSET(Lookup_table, MATCH([@Item], OFFSET(Lookup_table, 0, 0, ROWS(Lookup_table), 1), 0) -1, 1, 1, 1)

=XLOOKUP([@Item], Lookup_table[Item], Lookup_table[Price])

흥미롭게도 Excel 테이블은 정기적으로 참조 하더라도 매우 빠릅니다. 즉, 첫 번째 범위 (A1 : C500001) 만 테이블로 변환하고 일반 VLOOKUP 공식을 사용하여 조회 범위 에서 데이터를 가져 오면 주 테이블의 전체 열이 약 2.5 초로 계산됩니다!

계산 속도 : 공식에 따라 2.3 ~ 3.3 초

엔드 투 엔드 튜토리얼 :

• 예제가있는 Excel 테이블
• Excel 테이블의 구조화 된 참조

동적 배열이있는 vlookup

2020 년 1 월에 발생한 Excel 365 계산 엔진의 획기적인 변화는 소위 동적 배열 에 대한 지원을 추가했습니다. 요컨대, 이들은 하나의 셀에 입력 된 공식에 기초하여 자동으로 계산하고 값을 여러 셀로 반환 하는레이션 가능한 배열입니다.

동적 배열의 가장 좋은 점 중 하나는 거의 모든 전통적인 Excel 기능과 함께 사용할 수 있다는 것입니다. Vlookup 공식의 경우 다음과 같습니다.

=VLOOKUP(B2:B500001, E2:F500001, 2, FALSE)

클래식 VLOOKUP 기능과의 차이점은 단일 조회 값이 아니라 첫 번째 인수에 전체 조회 배열을 제공한다는 것입니다. 공식은 하나의 셀로 만 입력되므로 절대 참조로 범위를 잠그는 것에 대해 걱정할 필요가 없습니다.

성능에 관해서는 동적 어레이가 Excel 테이블보다 훨씬 빠르게 작동합니다! 50 만 개의 셀이 거의 즉시 결과로 채워져 있습니다. 1.8 초 - 매우 인상적입니다!

다른 결과는 다음과 같습니다.

인덱스 매치 -4.4 초

=INDEX(F2:F501, MATCH(B2:B500001, E2:E501, 0))

XLOOKUP -7.3 초

=XLOOKUP(B2:B500001, E2:E501, F2:F501)

흠… 디자인에 의해 역동적 인 것으로 추정되는 Xlookup은 이전 기능보다 더 나쁘게 수행합니다. 기이한!

계산 속도 : 공식에 따라 1.8 ~ 7.3 초

심층 튜토리얼 : Excel 동적 배열, 기능 및 공식

전원 쿼리와 일치하는 일치

완전성을 위해 작업에 대한 하나의 가능한 솔루션 인 전원 쿼리를 테스트합시다. 물론, 공식 계산을 쿼리 업데이트와 비교하는 것은 정확하지 않지만 더 빠른 호기심이 많습니다 :)

전원 쿼리 사용의 상세한 단계는 아래에 언급 된 별도의 튜토리얼에 설명되어 있습니다. 여기서는 결과를 평가할 것입니다.

병합 된 테이블은 전원 쿼리 편집기에서 8.5 초 만에 Excel로로드되었습니다. 공식과 달리 쿼리는 자동으로 업데이트되지 않습니다. 소스 데이터의 각 변경 후에는 데이터 또는 쿼리 탭에서 새로 고침 버튼을 클릭하여 결과 테이블을 수동으로 업데이트해야합니다. 우리의 500,000 행은 약 7 초 안에 새로 고침됩니다. 나쁘지는 않지만 Excel 공식은 더 잘할 수 있습니다. 쿼리를 설정하는 것이 한 클릭 프로세스가 아니라는 점을 고려할 때 다른 것이 작동하지 않는 경우에만 사용하는 마지막 방법 일 것입니다.

성능 : 8.5 초에 엑셀에로드; 상쾌한 7.6 초

심층 자습서 :

• 테이블을 Excel 전원 쿼리와 결합하는 방법
• Excel에서 전원 쿼리 사용 방법 - 실제 예
• 초보자를위한 Excel 튜토리얼의 전원 쿼리

추가 보너스 : 병합 테이블 마법사

Ultimate Suite의 사용자는 Excel Toolbox에 하나의 도구를 가지고있어 공통 열을 기반으로 두 테이블을 병합합니다. Excel의 것과 어떻게 비교되는지 봅시다.

병합 테이블 마법사를 실행하려면 Ablebits 데이터 탭에서 두 개의 테이블 병합 버튼을 클릭하십시오. 그런 다음 마법사의 단계를 따라 가면 과정을 안내합니다.

글쎄, 완료하는 데 약 3 초가 걸렸습니다. 50 만 레코드로 나쁘지 않습니다!

위의 메시지를 자세히 살펴보면 모든 경기가 발견 된 것은 아닙니다. 그러나 도구에 결함이 있다는 의미는 아닙니다. 조회 테이블에는 일부 항목 (조회 값)이 존재하지 않는다는 것을 알려줍니다. VLOOKUP 함수는이 경우 #N/A 오류를 반환하고 Merge 테이블 마법사는 셀 블랭크를 남깁니다.

성능 : 3.2 초

자세한 정보 : Excel에서 두 테이블을 병합하십시오

요약 및 결론

모든 예제를 신중하게 읽으면 이미 자신의 결론을 이미 작성했을 가능성이 높습니다. 세부 사항을 건너 뛰면이 비교 테이블에서 빠른 요약을 찾을 수 있습니다.

기능	몇 초 만에 계산 속도
	범위	전체 열	테이블	동적 배열
vlookup	6.6	14.2	2.3	1.8
인덱스 매치	8.9	17.7	2.6	4.4
오프셋 일치	2.9	3.5	2.7	-
xlookup	11.2	24.1	3.3	7.3
전원 쿼리	8.5
테이블 병합	3.2

아래에는 테스트 결과를 기반으로 한 몇 가지 관찰이 있습니다. 아마도 그들은 당신에게도 유용 할 것입니다.

• 모든 한계와 단점에도 불구하고 Vlookup은 특히 동적 배열에서 상당히 잘 작동합니다.
• 인덱스 일치는 예상만큼 빠르지 않습니다. 나에게 이것은 Vlookup과 같은 테이블 배열이 아니라 개별 열을 처리하기 때문에 매우 이상하게 보입니다.
• Xlookup은 많은 놀라운 기능을 가지고 있지만 거대한 데이터 세트에서 Vlookup 및 Index Match보다 느립니다. Microsoft가 향후 버전에서 성능을 향상시키기를 바랍니다.
• 오프셋 경기는 Excel 32 비트에서 가장 빠릅니다. 그러나 복잡한 구문 때문에 실수를 할 수있는 큰 기회가 있습니다. 또한, 그것은 동적 배열과 함께 작동하지 않으며, 적어도 강제 배열을 할 수 없었습니다.
• 절대적으로 필요한 경우가 아니라면 전체 열을 계산할 수는 없습니다. 이것은 공식이 두 배 이상 느리게 만듭니다.
• Excel 테이블 락! 엑셀을 최대한 활용하려면 가능한 한 사용하십시오.
• 다이나믹 어레이는 미래입니다.

이러한 관찰은 Dynamic Excel 365의 테스트를 기반으로합니다. 다른 버전에서 테스트 할 기회가 없었습니다. 당신이 그렇게했다면, 당신의 의견은 환영 받고 대단히 감사 할 것입니다!

다운로드를위한 연습 통합 문서

Excel에서 가장 빠른 Vlookup 공식 (.xlsx 파일, 74MB )

위 내용은 Excel의 Vlookup- 어떤 공식이 가장 빠릅니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!