Excel 데이터 분석-조회 기능

Excel 기능을 사용하여-

  • 데이터 범위에서 값 찾기-VLOOKUP 및 HLOOKUP
  • 테이블 또는 범위 내에서 값 또는 값에 대한 참조 가져 오기-INDEX
  • 셀 범위에서 지정된 항목의 상대 위치 얻기-MATCH

또한 이러한 함수를 결합하여 보유한 입력에 따라 필요한 결과를 얻을 수 있습니다.

VLOOKUP 함수 사용

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

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

어디

  • lookup_value− 조회하려는 값입니다. Lookup_value는 값 또는 셀에 대한 참조 일 수 있습니다. Lookup_value는 table_array에서 지정한 셀 범위의 첫 번째 열에 있어야합니다.

  • table_array− VLOOKUP이 lookup_value 및 반환 값을 검색 할 셀 범위입니다. table_array는 다음을 포함해야합니다.

    • 첫 번째 열의 lookup_value 및

    • 찾고자하는 반환 값

      Note− lookup_value를 포함하는 첫 번째 열은 오름차순으로 정렬하거나 정렬하지 않을 수 있습니다. 그러나 결과는이 열의 순서를 기반으로합니다.

  • col_index_num− 반환 값을 포함하는 table_array의 열 번호입니다. 숫자는 테이블 배열의 가장 왼쪽 열에 대해 1로 시작합니다.

  • range_lookup− VLOOKUP에서 정확히 일치하는 항목을 찾거나 대략적인 일치 항목을 찾을 지 여부를 지정하는 선택적 논리 값입니다. range_lookup은

    • 생략,이 경우 TRUE로 간주되고 VLOOKUP이 대략적인 일치 항목을 찾으려고합니다.

    • TRUE,이 경우 VLOOKUP은 대략적인 일치 항목을 찾으려고합니다. 즉, 정확히 일치하는 항목이 없으면 lookup_value보다 작은 다음으로 큰 값이 반환됩니다.

    • FALSE,이 경우 VLOOKUP은 정확히 일치하는 항목을 찾으려고합니다.

    • 1,이 경우 TRUE로 간주되고 VLOOKUP이 대략적인 일치 항목을 찾으려고합니다.

    • 0,이 경우 FALSE로 간주되고 VLOOKUP이 정확히 일치하는 항목을 찾으려고합니다.

Note− range_lookup이 생략되거나 TRUE 또는 1이면 VLOOKUP은 table_array의 첫 번째 열이 오름차순으로 정렬 된 경우에만 올바르게 작동합니다. 그렇지 않으면 잘못된 값이 발생할 수 있습니다. 이 경우 range_lookup에 FALSE를 사용하십시오.

range_lookup TRUE와 함께 VLOOKUP 함수 사용

학생 점수 목록을 고려하십시오. 점수 간격 및 합격 카테고리가 포함 된 배열에서 VLOOKUP을 사용하여 해당 등급을 얻을 수 있습니다.

table_array −

점수를 얻은 첫 번째 열 표시는 오름차순으로 정렬됩니다. 따라서 range_lookup 인수에 TRUE를 사용하면 필요한 대략적인 일치를 얻을 수 있습니다.

이 배열의 이름을 Grades.

셀 범위를 기억할 필요가 없도록 이러한 방식으로 배열 이름을 지정하는 것이 좋습니다. 이제 다음과 같이 보유한 마크 목록의 등급을 조회 할 준비가되었습니다.

보시다시피

  • col_index_num − table_array의 반환 값 열이 2임을 나타냅니다.

  • 그만큼 range_lookup 사실이다

    • table_array 등급의 조회 값을 포함하는 첫 번째 열은 오름차순입니다. 따라서 결과는 정확합니다.

    • 대략적인 일치에 대한 반환 값도 얻을 수 있습니다. 즉, VLOOKUP은 다음과 같이 계산합니다-

점수 패스 카테고리
<35 불합격
> = 35 및 <50 병종
> = 50 및 <60 이급
> = 60 및 <75 첫번째 교시
> = 75 차별화 된 퍼스트 클래스

다음과 같은 결과를 얻을 수 있습니다.

range_lookup FALSE와 함께 VLOOKUP 함수 사용

각 제품에 대한 제품 ID 및 가격이 포함 된 제품 목록을 고려하십시오. 새 제품이 출시 될 때마다 제품 ID와 가격이 목록 끝에 추가됩니다. 이는 제품 ID가 오름차순 일 필요가 없음을 의미합니다. 제품 목록은 다음과 같습니다.

table_array −

이 배열의 이름을 ProductInfo로 지정하십시오.

제품 ID가 첫 번째 열에 있으므로 VLOOKUP 기능을 사용하여 제품 ID가 지정된 제품의 가격을 얻을 수 있습니다. 가격은 열 3에 있으므로 col_index_ num은 3이어야합니다.

  • range_lookup을 TRUE로하는 VLOOKUP 함수 사용
  • range_lookup을 FALSE로하는 VLOOKUP 함수 사용

ProductInfo 배열의 정답은 171.65입니다. 결과를 확인할 수 있습니다.

당신은 당신이 얻은 것을 관찰합니다-

  • range_lookup이 FALSE 인 경우 올바른 결과
  • range_lookup이 TRUE 인 경우 잘못된 결과입니다.

ProductInfo 배열의 첫 번째 열이 오름차순으로 정렬되지 않았기 때문입니다. 따라서 데이터가 정렬되지 않을 때마다 FALSE를 사용해야합니다.

HLOOKUP 함수 사용

당신이 사용할 수있는 HLOOKUP 데이터가 열이 아닌 행에있는 경우 함수입니다.

제품 정보의 예를 들어 보겠습니다. 배열이 다음과 같다고 가정합니다.

  • 이 어레이의 이름을 ProductRange로 지정합니다. HLOOKUP 기능으로 상품 ID가 주어지면 상품 가격을 알 수 있습니다.

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

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

어디

  • lookup_value − 테이블의 첫 번째 행에서 찾을 값입니다.

  • table_array − 데이터를 조회하는 정보 테이블입니다.

  • row_index_num − 일치하는 값이 반환 될 table_array의 행 번호입니다.

  • range_lookup − HLOOKUP이 정확히 일치하는 항목을 찾거나 대략적인 일치 항목을 찾을 지 여부를 지정하는 논리 값입니다.

  • range_lookup 될 수 있습니다

    • 생략,이 경우 TRUE로 간주되고 HLOOKUP이 대략적인 일치 항목을 찾으려고합니다.

    • TRUE,이 경우 HLOOKUP은 대략적인 일치 항목을 찾으려고합니다. 즉, 정확히 일치하는 항목이 없으면 lookup_value보다 작은 다음으로 큰 값이 반환됩니다.

    • FALSE,이 경우 HLOOKUP은 정확히 일치하는 항목을 찾으려고합니다.

    • 1,이 경우 TRUE로 간주되고 HLOOKUP이 대략적인 일치 항목을 찾으려고합니다.

    • 0,이 경우 FALSE로 간주되고 HLOOKUP이 정확히 일치하는 항목을 찾으려고합니다.

Note− range_lookup이 생략되거나 TRUE 또는 1이면 table_array의 첫 번째 열이 오름차순으로 정렬 된 경우에만 HLOOKUP이 올바르게 작동합니다. 그렇지 않으면 잘못된 값이 발생할 수 있습니다. 이 경우 range_lookup에 FALSE를 사용하십시오.

range_lookup FALSE와 함께 HLOOKUP 함수 사용

제품 ID가 첫 번째 행에 있으므로 HLOOKUP 기능을 사용하여 제품 ID가 지정된 제품의 가격을 얻을 수 있습니다. 가격은 행 3에 있으므로 row_index_ num은 3이어야합니다.

  • range_lookup이 TRUE 인 HLOOKUP 함수를 사용하십시오.
  • range_lookup을 FALSE로하여 HLOOKUP 함수를 사용하십시오.

ProductRange 배열의 정답은 171.65입니다. 결과를 확인할 수 있습니다.

VLOOKUP의 경우와 같이

  • range_lookup이 FALSE 인 경우 올바른 결과

  • range_lookup이 TRUE 인 경우 잘못된 결과입니다.

ProductRange 배열의 첫 번째 행이 오름차순으로 정렬되지 않았기 때문입니다. 따라서 데이터가 정렬되지 않을 때마다 FALSE를 사용해야합니다.

range_lookup TRUE와 함께 HLOOKUP 함수 사용

VLOOKUP에서 사용되는 학생 표시의 예를 고려하십시오. 아래 표와 같이 열 대신 행에 데이터가 있다고 가정합니다.

table_array −

이 배열의 이름을 GradesRange로 지정합니다.

점수를 얻은 첫 번째 행 표시는 오름차순으로 정렬됩니다. 따라서 range_lookup 인수에 대해 TRUE와 함께 HLOOKUP을 사용하면 대략적인 일치로 Grades를 얻을 수 있으며 이것이 필요한 것입니다.

보시다시피

  • row_index_num − table_array의 반환 값 열이 2임을 나타냅니다.

  • 그만큼 range_lookup 사실이다

    • table_array Grades의 조회 값을 포함하는 첫 번째 열은 오름차순입니다. 따라서 결과는 정확합니다.

    • 대략적인 일치에 대한 반환 값도 얻을 수 있습니다. 즉 HLOOKUP은 다음과 같이 계산합니다-

점수 <35 > = 35 및 <50 > = 50 및 <60 > = 60 및 <75 > = 75
패스 카테고리 불합격 병종 이급 첫번째 교시 차별화 된 퍼스트 클래스

다음과 같은 결과를 얻을 수 있습니다.

INDEX 함수 사용

데이터 배열이있는 경우 배열에서 해당 값의 행 번호와 열 번호를 지정하여 배열에서 값을 검색 할 수 있습니다.

다음 판매 데이터를 고려하십시오. 여기에서 나열된 판매원에 의한 각 북부, 남부, 동부 및 서부 지역의 판매를 찾습니다.

  • 배열 이름을 SalesData로 지정합니다.

INDEX 함수를 사용하면 다음을 찾을 수 있습니다.

  • 특정 지역의 영업 사원의 판매.
  • 모든 판매원에 의한 지역의 총 판매.
  • 모든 지역의 영업 사원에 의한 총 판매.

다음과 같은 결과를 얻을 수 있습니다.

영업 사원의 행 번호와 지역의 열 번호를 모른다고 가정합니다. 그런 다음 index 함수로 값을 검색하기 전에 먼저 행 번호와 열 번호를 찾아야합니다.

다음 섹션에 설명 된대로 MATCH 함수를 사용하여 수행 할 수 있습니다.

MATCH 함수 사용

범위 내 항목의 위치가 필요한 경우 MATCH 함수를 사용할 수 있습니다. 다음과 같이 MATCH 및 INDEX 함수를 결합 할 수 있습니다.

다음과 같은 결과를 얻을 수 있습니다.