Kimm에는 3 개의 열이있는 워크 시트가 있습니다. A 열에는 사람의 성이 있고 B 열에는 이름이, C 열에는 개인의 전화 번호가 포함됩니다. Kimm이 그 사람의 이름과 성을 알고 있다면 (각각 F1과 F2 셀에 있음) 이름과 성을 일치하는 첫 번째 사람의 전화 번호를 반환하는 데 사용할 조회 수식의 종류가 궁금합니다.

실제로 전화 번호를 알아내는 데 사용할 수있는 몇 가지 다른 공식이 있습니다. 대부분의 접근 방식은 항상 Ctrl + Shift + Enter를 사용하여 셀에 입력되는 배열 수식을 사용합니다. 수식을 더 쉽게 이해할 수 있도록 명명 된 범위로 작업하는 것이 가장 좋습니다. 예를 들어 다음 이름을 설정합니다.

  • A 열에서 모든 성을 선택하고 LNames와 같은 이름을 지정하십시오.

  • B 열에서 모든 이름을 선택하고 FNames와 같은 이름을 지정하십시오.

  • C 열에서 모든 전화 번호를 선택하고 전화와 같은 이름을 지정하십시오.

다음은 전화 번호를 찾는 데 사용할 수있는 다양한 배열 수식입니다.

=SUMPRODUCT(--(LNames=F2),--(FNames=F1),Phones)

=INDEX(Phones,MATCH(F1&F2,FNames&LNames,))

=INDEX(Phones,INDEX(MATCH(F1&F2,FNames&LNames,0),))

=OFFSET(C1,MATCH(F1&F2,FNames&LNames,),)

또한 다음과 같이 ROW 함수에 의존하는 배열 수식을 생성 할 수 있습니다.

=INDEX(Phones,SUMPRODUCT((F1&F2=FNames&LNames)*(ROW(FNames)-1)))

=INDEX(Phones,MIN(IF((FNames=F1)*(LNames=F2),(ROW(Phones)-1))))

이 방법 (ROW 함수에 의존)을 사용하는 경우 실제 데이터 앞에 나타나는 행 수를 반영하도록 수식의 “-1″부분을 조정해야 할 수 있습니다. 이 경우 데이터 테이블의 첫 번째 행이 각 열의 머리글로 구성되기 때문에 행이 1 씩 감소합니다. 데이터 자체는 2 행에서 시작합니다.

지금까지 사용 된 모든 공식은 비교를 위해 이름과 성을 결합하는 데 의존합니다. 이것은 경우에 따라 “거짓 긍정”에 빠질 가능성이 있습니다. 예를 들어 데이터에있는 이름 중 두 개가 Thom Astonfield와 Thomas Tonfield라고 가정 해 보겠습니다. 이 공식에서 이름의 문자의 대소 문자는 중요하지 않으므로 이러한 개인의 성과 이름을 결합하면 정확히 동일합니다. 따라서 Thomas Tonfield의 전화 번호를 찾고 Thom Astonfield 다음에 그의 이름이 목록에 나타나면 항상 Thomas 대신 Thom의 전화 번호를 받게됩니다.

이 잠재적 인 문제를 해결하기 위해 이름과 성 사이에 일종의 구분 기호를 포함 할 수 있습니다. 팁 앞부분의 공식 중 하나를 사용하여 다음과 같이 조정 만하면됩니다.

=INDEX(Phones,SUMPRODUCT((F1&":"&F2=FNames&":"&LNames)*(ROW(FNames)-1)))

성과 이름 사이에 콜론을 추가하면 구분 기호 역할을하여 오 탐지 가능성을 제거합니다.

또한 셀 F1 및 F2에 데이터에 전혀 표시되지 않는 이름이 포함되어 있으면 수식에서 다시 가져온 정보가 잘못된 것임을 알아야합니다. 사용 된 함수의 변형에 따라 실제 오류 조건 (예 : # N / A 또는 #REF)이 반환 될 수 있습니다.

또는 일반 잘못된 데이터를 다시 얻을 수 있습니다. 오류 조건이 반환되는 경우 다음과 같은 방식으로 일치 항목을 찾을 수없는 가능성을 고려하여 수식을 조정할 수 있습니다.

=IF(ISERROR(INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0))), "no phone",INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0)))

이러한 유형의 수식을 사용할 때 염두에 두어야 할 몇 가지 사항이 있습니다. 무엇보다도, 정보의 신뢰성은 데이터 목록에있는 정보의 품질에 크게 좌우됩니다. 데이터에 철자가 틀리거나 공백이 있거나 이상한 순서로 정렬되거나 같은 사람에 대한 항목이 여러 개있는 경우 수식이 반환하는 내용에 영향을 미칠 수 있습니다.

데이터의 품질이 확실하지 않은 경우 수식 대신 Excel의 필터링 기능을 사용하는 것이 좋습니다. 자동 필터를 적용하면 데이터의 처음 두 열을 사용하여 성과 이름을 선택할 수 있습니다. 그러면 선택한 개인의 전화 번호가 반환됩니다. 매우 간단하고 필요한 데이터를 쉽게 선택할 수 있습니다.

마지막으로, 문제를 해결하는 데 사용할 수있는 다른 접근 방식이 있다는 것을 인식해야합니다. 예를 들어 DGET 함수를 사용하는 수식을 만들 수 있지만 이렇게하려면 워크 시트 또는 통합 문서에 작은 기준 테이블을 추가해야합니다. Kimm이 워크 시트에 중간 결과를 추가 할 수 없다고 지정했기 때문에 기준 테이블을 추가해야하므로 DGET 함수를 솔루션으로 포함하지 않기로 편집 결정을 내 렸습니다.

또한 매크로 사용에 익숙하다면 데이터를 검사하고 요청 된 전화 번호를 반환하는 사용자 정의 함수를 만들 수도 있습니다. 이러한 접근 방식을 사용할 때의 이점은 함수가 실제로 반환하는 정보를 처리 할 때 더 큰 유연성을 제공한다는 것입니다.

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (10478)은 Microsoft Excel 97, 2000, 2002 및 2003에 적용됩니다.

Excel (Excel 2007 이상)의 리본 인터페이스에 대한이 팁의 버전은 여기에서 찾을 수 있습니다.

link : / excelribbon-Pulling_a_Phone_Number_with_a_Known_First_and_Last_Name [알려진 성과 이름으로 전화 번호 가져 오기].