내용
이 작은 튜토리얼은 함수를 만드는 방법을 설명합니다 VPR (VLOOKUP) 대/소문자를 구분하고 Excel에서 대/소문자를 구분하여 검색할 수 있는 몇 가지 다른 수식을 표시하고 각 기능의 장단점을 지적합니다.
모든 Excel 사용자는 수직 검색을 수행하는 기능을 알고 있다고 생각합니다. 그렇군요 기능입니다 VPR. 그러나 그것을 아는 사람은 거의 없다. VPR 대소문자를 구분하지 않습니다. 즉, 소문자와 대문자가 동일합니다.
다음은 무능력을 보여주는 빠른 예입니다. VPR 레지스터를 인식합니다. 세포에서 가정 A1 "bill" 값과 셀을 포함합니다. A2 – "청구서", 공식:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... "bill"에 대한 검색을 중지하고 해당 값이 목록의 첫 번째 값이기 때문에 셀에서 값을 추출합니다. B1.
이 기사의 뒷부분에서 수행하는 방법을 보여 드리겠습니다. VPR 대소문자를 구분합니다. 또한 Excel에서 대소문자 구분 검색을 수행할 수 있는 몇 가지 기능을 더 배우게 됩니다.
가장 간단한 것부터 시작하겠습니다. 관람 (조회) 및 SUMPRODUCT (SUMPRODUCT), 불행히도 몇 가지 중요한 제한 사항이 있습니다. 다음으로 조금 더 복잡한 공식을 자세히 살펴보겠습니다. 인덱스+매치 (INDEX+MATCH), 어떤 상황과 어떤 데이터세트에서도 완벽하게 작동합니다.
VLOOKUP 함수는 대소문자를 구분합니다.
이미 알고 있듯이 일반적인 기능은 VPR 대소문자를 구분하지 않습니다. 그러나 대소문자를 구분하는 방법이 있습니다. 이렇게 하려면 다음 예와 같이 테이블에 보조 열을 추가해야 합니다.
열에서 가정 B 제품 식별자(항목)가 있고 열에서 제품 가격과 해당 설명을 추출하려고 합니다. C и D. 문제는 식별자에 소문자와 대문자가 모두 포함되어 있다는 것입니다. 예를 들어, 셀 값 B4 (001Tvci3u) 및 B5 (001Tvci3U)는 마지막 문자의 경우만 다르며, u и U 각각.
상상할 수 있듯이 일반적인 검색 공식은
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
돌아올거야. $ 90, 값 이후 001TVci3u 은(는) 다음보다 이전 검색 범위에 있습니다. 001TVci3U. 하지만 우리에게 필요한 것은 그게 아니잖아요?
기능으로 검색하려면 VPR Excel에서 대소문자를 구분하는 경우 도우미 열을 추가하고 해당 셀을 다음 수식으로 채워야 합니다(여기서 B는 조회 열).
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
이 수식은 원하는 값을 별도의 문자로 나누고 각 문자를 해당 코드로 바꿉니다(예: A 대신 65세에 a 코드 97) 그런 다음 이러한 코드를 고유한 숫자 문자열로 결합합니다.
그 후, 우리는 간단한 기능을 사용합니다 VPR 대소문자 구분 검색:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
기능의 올바른 작동 VPR 대소문자 구분은 다음 두 가지 요소에 따라 달라집니다.
- 도우미 열은 볼 수 있는 범위에서 가장 왼쪽 열이어야 합니다.
- 찾고 있는 값은 실제 값 대신 문자 코드를 포함해야 합니다.
CODE 기능을 올바르게 사용하는 방법
보조 열의 셀에 삽입된 수식은 모든 검색 값에 동일한 수의 문자가 있다고 가정합니다. 그렇지 않은 경우 가장 작은 숫자와 가장 큰 숫자를 알고 많은 기능을 추가해야 합니다. 오류 (IFERROR) 가장 짧은 검색 값과 가장 긴 검색 값의 차이는 몇 문자입니다.
예를 들어, 가장 짧은 검색 값이 3자이고 가장 긴 검색 값이 5자인 경우 다음 공식을 사용하십시오.
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
기능을 위해 PSTR (MID) 다음 인수를 제공합니다.
- 첫 번째 인수 – 본문 (텍스트)는 추출할 문자를 포함하는 텍스트 또는 셀 참조입니다(이 경우 B2).
- 첫 번째 인수 – 시작 번호 (start_position)은 추출할 첫 번째 문자의 위치입니다. 당신은 입력 1 첫 번째 기능에서 PSTR, 2 – 두 번째 기능에서 PSTR 등
- 첫 번째 인수 – num_chars (number_of_characters) – 텍스트에서 추출할 문자 수를 지정합니다. 항상 1개의 문자만 필요하므로 모든 함수에서 다음을 작성합니다. 1.
제한 사항 : 함수 VPR Excel에서 대/소문자를 구분하는 검색에 가장 적합한 솔루션은 아닙니다. 첫째, 보조 컬럼의 추가가 필요합니다. 둘째, 수식은 데이터가 동질적이거나 검색된 값의 정확한 문자 수를 알고 있는 경우에만 제대로 작동합니다. 그렇지 않은 경우 아래에 표시된 솔루션 중 하나를 사용하는 것이 좋습니다.
대소문자 구분 검색을 위한 LOOKUP 기능
함수 관람 (LOOKUP) 관련 VPR, 그러나 그 구문은 보조 열을 추가하지 않고 대소문자를 구분하는 검색을 허용합니다. 이렇게 하려면 다음을 사용하십시오. 관람 기능과 결합 정확한 (정확한).
이전 예에서 데이터를 가져오면(보조 열 제외) 다음 공식이 작업에 대처합니다.
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
범위 내 수식 검색 A2 : A7 셀 값과 정확히 일치 F2 대소문자를 구분하고 같은 행의 B열에서 값을 반환합니다.
처럼 VPR기능 관람 아래 스크린샷에서 볼 수 있듯이 텍스트 및 숫자 값과 동일하게 작동합니다.
중요! 기능을 위해서는 관람 올바르게 작동했다면 조회 열의 값을 오름차순으로 정렬해야 합니다. 즉, 가장 작은 것에서 큰 것 순으로 정렬해야 합니다.
기능이 어떻게 작동하는지 간단히 설명하겠습니다 정확한 위의 공식에서 이것이 핵심이기 때문입니다.
함수 정확한 첫 번째 인수와 두 번째 인수의 두 텍스트 값을 비교하여 정확히 같으면 TRUE를 반환하고 같지 않으면 FALSE를 반환합니다. 기능이 정확한 대소문자를 구분합니다.
공식이 어떻게 작동하는지 봅시다 보기+정확:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- 함수 정확한 셀 값을 비교 F2 열의 모든 요소와 함께 A (A2:A7). 정확히 일치하는 항목이 있으면 TRUE를 반환하고 그렇지 않으면 FALSE를 반환합니다.
- 첫 번째 함수 인수를 제공하기 때문에 관람 값이 TRUE이면 정확히 일치하는 항목이 발견된 경우에만 지정된 열(여기서는 열 B)에서 해당 값을 추출합니다(대소문자 구분).
이 설명이 명확하고 이제 주요 아이디어를 이해하기를 바랍니다. 그렇다면 우리가 추가로 분석할 다른 기능에는 어려움이 없을 것입니다. 그들은 모두 같은 원리로 작동합니다.
제한 사항 : 조회 열의 데이터는 오름차순으로 정렬되어야 합니다.
SUMPRODUCT – 텍스트 값을 찾고 대소문자를 구분하지만 숫자만 반환합니다.
제목에서 이미 이해하셨듯이, SUMPRODUCT (SUMPRODUCT)는 대소문자를 구분하는 검색을 수행하는 데 도움이 되는 또 다른 Excel 함수이지만 숫자 값만 반환합니다. 이 옵션이 적합하지 않으면 즉시 번들로 진행할 수 있습니다. 인덱스+매치, 모든 경우와 모든 데이터 유형에 대한 솔루션을 제공합니다.
먼저 이 함수의 구문을 간략하게 설명하겠습니다. 이렇게 하면 다음에 나오는 대소문자를 구분하는 공식을 더 잘 이해하는 데 도움이 됩니다.
함수 SUMPRODUCT 주어진 배열의 요소를 곱하고 결과의 합계를 반환합니다. 구문은 다음과 같습니다.
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
대소문자를 구분하는 검색이 필요하기 때문에 함수를 사용합니다. 정확한 (EXACT) 승수 중 하나로 이전 예:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
당신이 기억하는 것처럼 정확한 셀 값을 비교 F2 열의 모든 요소와 함께 A. 정확히 일치하는 항목이 있으면 TRUE를 반환하고 그렇지 않으면 FALSE를 반환합니다. 수학 연산에서 Excel은 TRUE를 다음과 같이 취합니다. 1, 및 FALSE에 대한 0추가 SUMPRODUCT 이 숫자를 곱하고 결과를 합산합니다.
곱할 때 항상 제공하기 때문에 XNUMX은 계산되지 않습니다. 0. 열에서 정확히 일치하면 어떻게 되는지 자세히 살펴보겠습니다. A 발견하고 돌아왔다 1… 기능 SUMPRODUCT 열의 숫자를 곱합니다. B on 1 결과를 반환합니다. 정확히 같은 숫자입니다! 이는 다른 곱의 결과가 XNUMX이고 결과 합계에 영향을 미치지 않기 때문입니다.
불행히도 기능 SUMPRODUCT 곱할 수 없기 때문에 텍스트 값과 날짜를 사용할 수 없습니다. 이 경우 오류 메시지가 나타납니다. #값! (#VALUE!) 셀에서와 같이 F4 아래 그림에서:
제한 사항 : 숫자 값만 반환합니다.
INDEX + MATCH – 모든 데이터 유형에 대한 대소문자 구분 검색
마지막으로, 우리는 모든 데이터 세트에서 작동하는 대소문자를 구분하는 무제한 검색 공식에 가깝습니다.
이 예는 디저트에 가장 좋은 것이 남아 있기 때문이 아니라 이전 예에서 얻은 지식이 대소문자를 구분하는 공식을 더 빠르고 더 잘 이해하는 데 도움이 되기 때문입니다. 인덱스+매치 (인덱스+매치).
아마 짐작하셨겠지만, 기능의 조합은 더 많이 노출 и INDEX 보다 유연하고 강력한 대안으로 Excel에서 사용 VPR. VLOOKUP 대신 INDEX 및 MATCH 사용 기사에서는 이러한 기능이 함께 작동하는 방식을 완벽하게 설명합니다.
요점만 요약하겠습니다.
- 함수 더 많이 노출 (MATCH) 주어진 범위에서 값을 검색하고 상대 위치, 즉 행 및/또는 열 번호를 반환합니다.
- 다음으로 기능 INDEX (INDEX)는 지정된 열 및/또는 행에서 값을 반환합니다.
공식으로 인덱스+매치 대소문자를 구분하여 검색할 수 있으므로 하나의 기능만 추가하면 됩니다. 또 무엇인지 짐작하기 어렵지 않다 정확한 (정확한):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
이 공식에서 정확한 기능과 함께 작동하는 것과 같은 방식으로 작동합니다. 관람, 그리고 동일한 결과를 제공합니다:
공식을 참고하십시오 인덱스+매치 중괄호로 묶인 배열 수식은 다음을 눌러 완성해야 합니다. Ctrl + Shift + Enter.
INDEX+MATCH가 대소문자를 구분하는 검색에 가장 적합한 솔루션인 이유는 무엇입니까?
번들의 주요 장점 INDEX и 더 많이 노출:
- 와 달리 보조 열을 추가할 필요가 없습니다. VPR.
- 검색 열을 정렬할 필요가 없습니다. 관람.
- 숫자, 텍스트 및 날짜와 같은 모든 유형의 데이터와 함께 작동합니다.
이 공식은 완벽해 보이지 않나요? 사실은 그렇지 않습니다. 그리고 그 이유입니다.
조회 값과 연결된 반환 값 열의 셀이 비어 있다고 가정합니다. 수식은 어떤 결과를 반환합니까? 아니? 공식이 실제로 무엇을 반환하는지 봅시다.
죄송합니다. 수식은 XNUMX을 반환합니다! 순수한 텍스트 값으로 작업하는 경우 큰 문제가 되지 않을 수 있습니다. 그러나 테이블에 "실제" XNUMX을 포함한 숫자가 포함되어 있으면 문제가 됩니다.
사실 앞에서 논의한 다른 모든 조회 수식(VLOOKUP, LOOKUP 및 SUMPRODUCT)은 동일한 방식으로 작동합니다. 하지만 완벽한 공식을 원하시죠?
수식을 대소문자를 구분하려면 인덱스+매치 완벽, 함수에 넣어 IF (IF) 반환 값이 있는 셀을 테스트하고 비어 있으면 빈 결과를 반환합니다.
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
이 공식에서:
- B 반환 값이 있는 열입니다.
- 1+ 함수에서 반환된 셀의 상대 위치를 바꾸는 숫자입니다. 더 많이 노출, 셀의 실제 주소로. 예를 들어 우리 함수에서 더 많이 노출 주어진 검색 배열 A2 : A7, 즉, 셀의 상대 위치 A2 의지 1, 배열의 첫 번째 항목이기 때문입니다. 그러나 세포의 실제 위치 A2 열에 2, 그래서 우리는 추가합니다 1차이를 보완하고 기능을 갖기 위해 간접적 인 (INDIRECT) 원하는 셀에서 값을 검색했습니다.
아래 그림은 수정된 대소문자 구분 공식을 보여줍니다. 인덱스+매치 행동 중. 반환된 셀이 비어 있으면 빈 결과를 반환합니다.
수식을 열로 다시 작성했습니다. 비:디스크린샷의 수식 입력줄에 맞춥니다.
수식 반환 0반환된 셀에 XNUMX이 포함된 경우.
링크를 원하시면 INDEX и 더 많이 노출 반환 값이 비어 있을 때 일부 메시지가 표시되면 다음과 같이 수식의 마지막 따옴표("")에 이를 작성할 수 있습니다.
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
