공식 예제와 Excel 일치 함수

이 튜토리얼은 공식 예제와 함께 Excel에서 일치 함수를 사용하는 방법을 설명합니다. 또한 VLOOKUP 및 MATCH를 사용하여 동적 공식을 만들어 조회 공식을 개선하는 방법을 보여줍니다.

Microsoft Excel에는 다양한 셀에서 특정 값을 찾는 데 도움이되는 다양한 조회/참조 기능이 있으며, 그 중 하나입니다. 기본적으로 다양한 셀에서 항목의 상대 위치를 식별합니다. 그러나 경기 함수는 순수한 본질보다 훨씬 더 많은 것을 할 수 있습니다.

• Excel 매치 기능 - 구문 및 기본 용도
• Excel에서 매치를 사용하는 방법 - 공식 예제
  • 와일드 카드와 일치하는 공식
  • 사례에 민감한 일치 공식
  • 일치 및 차이에 대한 2 개의 열 비교 (ISNA Match)
  • vlookup과 일치
  • hlookup과 일치

Excel 매치 함수 - 구문 및 사용

Excel의 일치 함수는 다양한 셀에서 지정된 값을 검색하고 해당 값의 상대 위치를 반환합니다.

매치 함수의 구문은 다음과 같습니다.

매치 (lookup_value, lookup_array, [match_type])

Lookup_value (필수) - 찾으려는 값. 셀 기준뿐만 아니라 숫자, 텍스트 또는 논리적 값 일 수 있습니다.

Lookup_Array (필수) - 검색 할 셀 범위.

match_type (선택 사항) - 매치 유형을 정의합니다. 이 값 중 하나 일 수 있습니다 : 1, 0, -1. match_type 인수는 0으로 설정된 인수는 정확한 일치 만 반환하는 반면, 다른 두 유형은 대략적인 일치를 허용합니다.

• 1 또는 생략 (기본값) - 근사 일치 (가장 가까운). 조회 배열에서 조회 값보다 작거나 같은 가장 큰 값을 찾습니다. 조회 배열을 오름차순 순서로 정렬해야합니다.
• 0- 정확한 일치. 배열에서 조회 값과 정확히 같은 첫 번째 값을 찾습니다. 정렬이 필요하지 않습니다.
• -1- 근사치 일치 (가장 가까운). 조회 값보다 크거나 같은 가장 작은 값을 찾습니다. 조회 배열은 가장 큰 것부터 가장 작은 것부터 z에서 A에서 A까지 내림차순으로 정렬해야합니다.

매치 함수를 더 잘 이해하려면이 데이터를 기반으로 간단한 공식을 만들어 봅시다. 학생의 이름은 A 열의 이름과 B 열의 시험 점수를 가장 크고 가장 작은 것부터 정렬합니다. 특정 학생 (예 : Laura )이 어디에 있는지 알아 보려면이 간단한 공식을 사용하십시오.

=MATCH("Laura", A2:A8, 0)

선택적으로, 일부 셀에 조회 값을 넣고 (이 예에서 E1) Excel 일치 공식에서 해당 셀을 참조 할 수 있습니다.

=MATCH(E1, A2:A8, 0)

위의 스크린 샷에서 볼 수 있듯이 학생 이름은 임의 순서로 입력되므로 Match_Type 인수를 0 (정확한 일치)로 설정했습니다.이 일치 유형 만 조회 배열에서 값을 정렬 할 필요가 없기 때문입니다. 기술적으로 매치 공식은 Laura의 상대적 위치를 범위에서 반환합니다. 그러나 점수는 가장 큰 것에서 가장 작은 것으로 정렬되기 때문에 Laura는 모든 학생들 사이에서 5 ^번째 최고 점수를 가지고 있음을 알려줍니다.

팁. Excel 365 및 Excel 2021에서는 현대적이고 더 강력한 경기의 후속 인 Xmatch 함수를 사용할 수 있습니다.

성냥 기능에 대해 알아야 할 4 가지

방금 본 것처럼 Excel에서 Match를 사용하는 것은 쉽습니다. 그러나 거의 모든 다른 기능의 경우와 마찬가지로 다음과 같이 알아야 할 몇 가지 특이성이 있습니다.

1. 일치 함수는 값 자체가 아니라 어레이에서 조회 값의 상대 위치를 반환합니다.
2. 일치는 대소 문자를 사용하지 않으므로 텍스트 값을 다룰 때 소문자와 대문자를 구별하지 않습니다.
3. 조회 배열에 몇 가지 조회 값이 포함 된 경우 첫 번째 값의 위치가 반환됩니다.
4. 조회 배열에서 조회 값을 찾을 수없는 경우 #N/A 오류가 반환됩니다.

Excel에서 매치를 사용하는 방법 - 공식 예제

Excel Match 함수의 기본 사용을 알았으므로 기본을 넘어서는 몇 가지 공식 예제에 대해 논의 해 봅시다.

와일드 카드와 부분 일치

다른 많은 기능과 마찬가지로 Match는 다음 과일드 카드 문자를 이해합니다.

• 물음표 (?) - 단일 문자를 대체합니다
• Asterisk (*) - 일련의 문자를 대체합니다

메모. 와일드 카드는 match_type가 0으로 설정된 매치 공식에서만 사용할 수 있습니다.

와일드 카드가있는 일치 공식은 전체 텍스트 문자열이 아니라 문자열의 일부만 일치시키려는 상황에서 유용합니다. 요점을 설명하려면 다음 예를 고려하십시오.

지난 달의 지역 리셀러 및 판매 수치 목록이 있다고 가정합니다. 목록에서 특정 리셀러의 상대적 위치를 찾고 싶지만 (내림차순으로 판매 금액으로 정렬) 그의 이름을 정확히 기억할 수는 없지만 첫 번째 캐릭터를 기억합니다.

리셀러 이름이 A2 : A11 범위에 있고 "CAR"로 시작하는 이름을 검색한다고 가정하면 공식은 다음과 같습니다.

=MATCH("car*", A2:A11,0)

매치 공식을보다 다재다능하게 만들려면 일부 셀에 조회 값을 입력하고 (이 예에서 E1) 해당 셀을 와일드 카드 문자로 연결할 수 있습니다.

=MATCH(E1&"*", A2:A11,0)

아래 스크린 샷에 표시된 것처럼 공식은 "카터"의 위치 인 2를 반환합니다.

조회 값의 하나의 문자 만 교체하려면 "?"를 사용하십시오. 이와 같은 와일드 카드 운영자 :

=MATCH("ba?er", A2:A11,0)

위의 공식은 " 베이커 "라는 이름과 일치하고 상대적 위치 (5)를 다시 실행합니다.

사례에 민감한 일치 공식

이 튜토리얼의 시작 부분에서 언급했듯이 일치 함수는 대문자와 소문자를 구별하지 않습니다. 사례에 민감한 일치 공식을 만들려면 문자 케이스를 포함하여 셀을 정확하게 비교하는 정확한 기능과 조합하여 일치하는 것을 사용하십시오.

다음은 데이터와 일치하는 일반적인 사례에 민감한 공식입니다.

일치 (true, exact ( 조회 배열 , 조회 값 ), 0)

공식은 다음 논리와 함께 작동합니다.

• 정확한 기능은 조회 값을 조회 배열의 각 요소와 비교합니다. 비교 셀이 정확히 같으면 함수는 true, 그렇지 않으면 false를 반환합니다.
• 그런 다음 매치 함수는 정확한별로 반환 된 배열의 각 값과 true ( Lookup_value )를 비교하고 첫 번째 일치의 위치를 ​​반환합니다.

CTRL 시프트 엔트를 올바르게 작성 해야하는 배열 공식이라는 점을 명심하십시오.

조회 값이 셀 E1에 있고 조회 배열이 A2 : A9라고 가정하면 공식은 다음과 같습니다.

=MATCH(TRUE, EXACT(A2:A9,E1),0)

다음 스크린 샷은 Excel의 케이스 민감성 일치 공식을 보여줍니다.

일치 및 차이에 대한 2 개의 열 비교 (ISNA Match)

일치 및 차이점에 대한 두 목록을 확인하는 것은 Excel에서 가장 일반적인 작업 중 하나이며 다양한 방식으로 수행 할 수 있습니다. ISNA/매치 공식은 그 중 하나입니다.

if (ISNA (일치 (list1 , list2 , 0)의 1st value, "List 1", "")

목록 1에 존재하지 않는 목록 2의 값에 대해, 공식은 " 목록 1에 있지 않은 " "을 반환합니다. 그리고 다음은 다음과 같습니다.

• 일치 함수는 목록 2 내에서 목록 1에서 값을 검색합니다. 값이 발견되면 상대 위치, #n/a 오류를 반환합니다.
• Excel의 ISNA 함수는 #N/A 오류 ( "사용할 수 없음"을 의미 함)를 확인합니다. 주어진 값이 #n/a 오류 인 경우 함수는 true를 반환하고 그렇지 않으면 거짓말을 반환합니다. 우리의 경우, true는 목록 1의 값이 목록 2 내에서 찾을 수 없음을 의미합니다 (예 : #n/a 오류는 일치에 의해 반환 됨).
• 목록 1에 나타나지 않는 값에 대해 사용자가 TRUE를 보는 것이 매우 혼란 스러울 수 있으므로 ISNA 주변의 IF 함수를 래핑하여 " List 1에 있지 않음 "또는 원하는 텍스트를 표시합니다.

예를 들어, a 열 A의 값과 열의 값을 비교하기 위해, 공식은 다음과 같은 모양을 취합니다 (여기서 B2는 가장 최상위 셀).

=IF(ISNA(MATCH(B2,A:A,0)), "Not in List 1", "")

당신이 기억 하듯이, Excel의 매치 함수는 그 자체로는 사례에 민감합니다. 문자 케이스를 구별하려면 Lookup_Array 인수에 정확한 기능을 포함시키고 Ctrl Shift Enter를 눌러이 배열 공식을 작성하는 것을 잊지 마십시오.

=IF(ISNA(MATCH(TRUE, EXACT(A:A, B2),0)), "Not in List 1", "")

다음 스크린 샷은 두 가지 공식을 모두 보여줍니다.

Excel에서 두 목록을 비교하는 다른 방법을 배우려면 다음 자습서를 참조하십시오. Excel에서 2 개의 열을 비교하는 방법.

Excel vlookup 및 매치

이 예제는 이미 Excel Vlookup 기능에 대한 기본 지식이 있다고 가정합니다. 그리고 만약 당신이 그렇게한다면, 당신은 수많은 한계에 도달 할 가능성이 있습니다 (Excel Vlookup이 작동하지 않는 이유에 대한 자세한 개요는 찾을 수 있습니다). 더 강력한 대안을 찾고 있습니다.

Vlookup의 가장 성가신 단점 중 하나는 조회 테이블 내에서 열을 삽입하거나 삭제 한 후 작동을 중지한다는 것입니다. VLOOKUP은 지정할 수있는 리턴 열의 수를 기준으로 일치하는 값을 가져 오기 때문에 발생합니다 (색인 번호). 공식에서 인덱스 번호가 "하드 코딩"되기 때문에 새 열이 추가되거나 테이블에 삭제 될 때 Excel을 조정할 수 없습니다.

Excel Match 함수는 조회 값의 상대적 위치를 다루므로 VLOOKUP의 COL_INDEX_NUM 인수에 완벽하게 맞습니다. 다시 말해, 리턴 열을 정적 번호로 지정하는 대신 일치를 사용하여 해당 열의 현재 위치를 얻습니다.

일을 쉽게 이해하기 위해 학생들의 시험 점수와 함께 테이블을 다시 사용해 보겠습니다 (이 튜토리얼의 시작 부분에서 사용한 것과 유사) 이번에는 상대적 위치가 아니라 실제 점수를 검색 할 것입니다.

조회 값이 셀 F1에 있다고 가정하면 테이블 어레이는 $ 1 : $ c $ 2입니다 (공식을 다른 셀에 복사하려면 절대 셀 참조를 사용하여 잠그는 것이 좋습니다). 공식은 다음과 같습니다.

=VLOOKUP(F1, $A$1:$C$8, 3, FALSE)

우리가 뽑을 수있는 수학 점수가 테이블의 3 ^rd 열이기 때문에 3 ^Rd 인수 ( col_index_num )는 3으로 설정됩니다. 아래 스크린 샷에서 볼 수 있듯이이 일반 Vlookup 공식은 잘 작동합니다.

그러나 열을 삽입하거나 삭제할 때까지 :

그래서 왜 #ref! 오류? col_index_num이 3으로 설정된 Col_index_num 은 Excel에게 세 번째 열에서 값을 얻도록 지시하는 반면 이제 테이블 배열에는 2 개의 열만 있습니다.

이러한 일이 발생하지 않도록하기 위해 다음 경기 기능을 포함하여 Vlookup 공식을보다 역동적으로 만들 수 있습니다.

MATCH(E2,A1:C1,0)

어디:

• E2는 조회 값이며, 이는 반환 열의 이름과 정확히 동일합니다 . 즉 값을 가져 오려는 열 (이 예제의 수학 점수 ).
• A1 : C1은 테이블 헤더를 포함하는 조회 배열입니다.

그리고 지금,이 일치 함수는 다음과 같이 vlookup 공식의 col_index_num 인수에 포함시킵니다.

=VLOOKUP(F1,$A$1:$C$8, MATCH(E2,$A$1:$C$1, 0), FALSE)

추가 또는 삭제하는 열에 관계없이 완벽하게 작동하는지 확인하십시오.

위의 스크린 샷에서 사용자가 워크 시트의 다른 장소로 이동하더라도 공식이 올바르게 작동하도록 모든 셀 참조를 잠그 었습니다. 아래의 스크린 샷에서 볼 수 있는데, 열을 삭제 한 후에는 공식이 잘 작동합니다. 또한 Excel 은이 경우 절대 참조를 올바르게 조정할 수있을만큼 똑똑합니다.

Excel HLOOKUP 및 MATH

비슷한 방식으로 Excel 매치 함수를 사용하여 HLOOKUP 공식을 개선 할 수 있습니다. 일반적인 원칙은 VLOOKUP의 경우와 본질적으로 동일합니다. 매치 함수를 사용하여 리턴 열의 상대 위치를 얻고 해당 숫자를 hlookup 공식의 row_index_num 인수에 공급합니다.

조회 값이 셀 B5에 있고 테이블 배열이 B1 : H3이고 리턴 행의 이름 (일치에 대한 조회 값)은 셀 A6에 있고 행 헤더는 A1 : A3이며 전체 공식은 다음과 같습니다.

=HLOOKUP(B5, B1:H3, MATCH(A6, A1:A3, 0), FALSE)

방금 보시다시피 Hlookup/Vlookup & Match의 조합은 정기적 인 Hlookup 및 Vlookup 공식에 비해 개선 된 것입니다. 그러나 일치 함수가 모든 제한을 제거하지는 않습니다. 특히 Vlookup 경기 공식은 여전히 ​​왼쪽을 볼 수 없으며 HLookup 경기는 최상위 이외의 다른 행에서 검색되지 않습니다.

위의 (및 기타) 한계를 극복하려면 인덱스 매치의 조합을 사용하는 것을 고려하십시오.이 조합은 Excel에서 조회하고 Vlookup 및 Hlookup보다 우수한 많은 측면에서 강력하고 다재다능한 방법을 제공합니다. 자세한 지침 및 공식 예제는 Excel의 Index & Match에서 찾을 수 있습니다. Vlookup에 대한 더 나은 대안입니다.

이것이 Excel에서 매치 공식을 사용하는 방법입니다. 바라건대,이 튜토리얼에서 논의 된 예는 귀하의 작업에 도움이 될 것입니다. 읽어 주셔서 감사합니다. 다음 주에 우리 블로그에서 뵙기를 바랍니다!

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

Excel 매치 공식 예제 (.xlsx 파일)

위 내용은 공식 예제와 Excel 일치 함수의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!