엑셀 테이블에서 데이터를 찾는 방법. Excel의 색인 및 검색 기능 - vpr을 위한 최고의 대안

비록 크긴 하지만 전체 테이블이나 인접 셀 범위를 통해 검색하는 것이 서로 다른 인접하지 않은 범위에 분산된 부분으로 나누어진 여러 테이블을 통하거나 별도의 시트에 있는 것보다 확실히 더 쉽습니다. 그렇게 해도 자동 검색여러 테이블에 걸쳐 동시에 심각한 장애물이 발생할 수 있습니다. 그러나 모든 데이터를 하나의 테이블로 구성하는 것은 어렵고 때로는 거의 불가능합니다. ~에 구체적인 예 Excel의 여러 테이블에서 동시 검색을 위한 올바른 솔루션을 보여 드리겠습니다.

여러 범위에 걸친 동시 검색

시각적인 예를 들어, 한 시트의 인접하지 않은 범위에 위치한 세 개의 간단한 별도 테이블을 만들어 보겠습니다.

제품 20개를 생산하는데 필요한 수량을 검색해야 합니다. 안타깝게도 이 데이터는 다른 열과 행에 있습니다. 그러므로 우선 해당 제품을 생산하는 데 얼마나 걸리는지 확인해야 합니다(첫 번째 표).

얻은 데이터를 바탕으로 즉시 다른 테이블에서 검색을 진행하여 특정 생산량에 참여해야 하는 작업자 수를 찾아야 합니다. 얻은 결과는 세 번째 표의 데이터와 비교되어야 합니다. 따라서 세 개의 테이블에 대한 한 번의 검색 작업으로 필요한 비용(금액)을 즉시 결정합니다.

일반 Excel 사용자는 VLOOKUP과 같은 수식 기반 기능을 사용하여 솔루션을 찾습니다. 그리고 3단계로 검색을 수행합니다(각 테이블에 대해 별도로). 특별한 공식을 이용하여 1단계만 검색하면 바로 기성품 결과를 얻을 수 있는 것으로 나타났습니다. 이렇게 하려면:

1. 셀 E6에 다음의 조건인 값 20을 입력합니다. 검색어.
2. E7 셀에 다음 수식을 입력합니다.

20개 생산 비용입니다. 특정 제품.

VLOOKUP을 사용한 수식이 여러 테이블에서 작동하는 방식:

이 수식의 작동 원리는 기본 VLOOKUP 함수(첫 번째)의 모든 인수에 대한 순차적 검색을 기반으로 합니다. 먼저, 세 번째 VLOOKUP 함수는 첫 번째 테이블에서 셀 E6의 값으로 지정된 제품 20개를 생산하는 데 필요한 시간을 검색합니다(필요한 경우 변경 가능). 그런 다음 두 번째 VLOOKUP 함수는 주 함수의 첫 번째 인수 값을 찾습니다.

세 번째 함수를 검색한 결과 두 번째 함수의 첫 번째 인수인 값 125를 얻습니다. 모든 매개변수를 수신한 후 두 번째 함수는 두 번째 테이블에서 생산에 필요한 작업자 수를 찾습니다. 결과적으로 값 5가 반환되며 이 값은 기본 함수에서 사용됩니다. 수신된 모든 데이터를 기반으로 수식은 계산의 최종 결과를 반환합니다. 즉, 특정 제품 20개를 생산하는 데 $1,750의 금액이 필요합니다.

이 원리를 사용하면 여러 시트의 VLOOKUP 함수에 대한 수식을 사용할 수 있습니다.

직원의 전화번호를 사용하여 직원의 내선 번호를 찾고 매출 금액에 대한 커미션 비율을 정확하게 추정해야 한다고 가정해 보겠습니다. 데이터를 검색하여 목록에서 특정 데이터를 빠르고 효율적으로 찾고, 해당 데이터가 올바르게 사용되고 있는지 자동으로 확인합니다. 데이터를 본 후 계산을 수행하고 결과를 표시하여 반환 값을 나타낼 수 있습니다. 데이터 목록에서 값을 검색하고 결과를 표시하는 방법에는 여러 가지가 있습니다.

이 기사에서는

정확히 일치하여 목록에서 수직으로 값 찾기

VLOOKUP 함수를 사용하거나 INDEX 및 MATCH 함수의 조합을 사용하여 이 작업을 수행할 수 있습니다.

VLOOKUP 함수 사용 예

VLOOKUP 함수.

INDEX 및 MATCHES의 예

그것은 무엇을 의미합니까?

=INDEX(C2:C10에서 MATCH(배열 B2:B10의 첫 번째 값 "Cabbage")와 일치하는 값을 반환해야 합니다.)

수식은 C2:C10 셀에서 다음에 해당하는 첫 번째 값을 찾습니다. 양배추(B7), C7의 값을 반환합니다( 100 ) - 해당하는 첫 번째 값 양배추.

자세한 내용은 INDEX 함수 및 MATCH 함수를 참조하세요.

대략적인 일치로 목록의 값을 세로로 찾습니다.

이렇게 하려면 VLOOKUP 함수를 사용하세요.

중요한:첫 번째 행의 값이 오름차순으로 정렬되어 있는지 확인하세요.

위의 예에서 VLOOKUP 함수는 A2:B7 범위에서 6번 지각한 학생의 이름을 찾습니다. 테이블에 항목이 없습니다. 6 지각하므로 VLOOKUP 함수는 6 미만에서 다음으로 가장 높은 일치 항목을 찾고 이름과 관련된 값 5를 찾습니다. 데이브, 따라서 반환 데이브.

자세한 내용은 VLOOKUP 함수를 참조하세요.

크기를 알 수 없는 목록에서 정확히 일치하는 세로 값 찾기

이 작업을 수행하려면 OFFSET 및 MATCH 함수를 사용하십시오.

메모:이 접근 방식은 데이터가 매일 업데이트되는 외부 데이터 범위에 있는 경우에 사용됩니다. B 열에 가격이 있다는 것을 알고 있지만 서버가 반환하는 데이터 행 수를 모르고 첫 번째 열이 사전순으로 정렬되지 않았습니다.

C1범위의 왼쪽 위 셀(시작 셀이라고도 함)입니다.

Match("오렌지"; C2: C7; 0) C2:C7 범위에서 주황색을 찾습니다. 범위에 시작 셀을 포함하면 안 됩니다.

1 - 반환 값이 반환되어야 하는 시작 셀 오른쪽에 있는 열 수입니다. 이 예에서 반환 값은 D 열에 있습니다. 매상.

정확히 일치하여 목록에서 가로로 값 찾기

이 작업을 수행하기 위해 GLOOKUP 함수가 사용됩니다. 아래는 예시입니다.

LOOKUP 함수는 열을 검색합니다. 매상그리고 지정된 범위의 5번째 줄의 값을 반환합니다.

자세한 내용은 LOOKUP 함수를 참조하세요.

조회 마법사를 사용하여 조회 수식 만들기(Excel 2007에만 해당)

메모:조회 마법사 추가 기능은 Excel 2010에서 중단되었습니다. 기능성기능 마법사와 사용 가능한 검색 및 참조(참조) 기능으로 대체되었습니다.

Excel 2007에서 조회 마법사는 행 및 열 머리글이 있는 워크시트 데이터를 기반으로 조회 수식을 만듭니다. 조회 마법사를 사용하면 한 열의 값을 알고 있으면 행에서 다른 값을 찾을 수 있으며 그 반대의 경우도 마찬가지입니다. 조회 마법사는 생성하는 수식에 인덱스와 MATCH를 사용합니다.

여러 달에 걸쳐 특정 제품의 판매량을 기록하는 테이블이 있습니다. 테이블에서 데이터를 찾아야 하며 검색 기준은 행 및 열 제목이 됩니다. 단, 행 또는 열 범위별로 별도로 검색해야 합니다. 즉, 기준 중 하나만 사용됩니다. 따라서 여기서는 INDEX 함수를 사용할 수 없고 특별한 수식이 필요합니다.

Excel 표에서 값 찾기

이 문제를 해결하기 위해 위에서 설명한 조건에 해당하는 회로도 표에 예를 설명합니다.

수직 및 수평으로 값을 검색하기 위한 테이블이 있는 시트:

표 자체 위에는 결과가 표시된 줄이 있습니다. B1 셀에 검색어 기준, 즉 열 제목 또는 행 이름을 입력합니다. 그리고 D1 셀에서 검색 수식은 해당 값을 계산한 결과를 반환해야 합니다. 그런 다음 두 번째 수식은 F1 셀에서 작동하며 해당 월을 검색하기 위한 기준으로 B1 및 D1 셀의 값을 이미 사용합니다.

Excel 문자열에서 값 찾기

이제 우리는 제품 4의 최대 판매량과 몇 달에 최대 판매가 있었는지 알아봅니다.

열로 검색하려면:

몇 월에 어떤 유형으로 발견되었는지 가장 큰 판매 2분기 동안 제품 4.

Excel 행에서 값을 찾는 공식의 원리:

VLOOKUP(세로 보기) 함수의 첫 번째 인수는 검색 기준이 있는 셀에 대한 링크를 지정합니다. 두 번째 인수는 검색 중에 표시할 셀 범위를 지정합니다. VLOOKUP 함수의 세 번째 인수는 Product 4라는 행에 대해 값을 가져와야 하는 열 번호를 나타내야 합니다. 하지만 이 번호를 미리 알 수 없으므로 COLUMN 함수를 사용하여 Product 4에 대한 열 번호 배열을 만듭니다. 범위 B4:G15.

이를 통해 VLOOKUP 함수는 전체 값 배열을 수집할 수 있습니다. 결과적으로 Product 4 행의 각 열에 해당하는 모든 값이 메모리에 저장됩니다(즉, 360; 958; 201; 605; 462; 832). 그런 다음 MAX 함수는 이 배열에서 최대 숫자만 가져와 수식 계산 결과에 따라 셀 D1의 값으로 반환할 수 있습니다.

보시다시피 수식의 디자인은 간단하고 간결합니다. 이를 기반으로 유사한 방식으로 특정 제품에 대한 다른 지표를 찾을 수 있습니다. 예를 들어 MIN 또는 AVERAGE 함수를 사용하여 판매량의 최소값 또는 평균값을 구할 수 있습니다. 판매 보고서에 대한 가장 편안한 분석을 구현하기 위해 더 복잡한 기능을 사용하여 이 기본 공식을 적용하는 것을 방해하는 것은 없습니다.

한 셀의 시작 부분을 기준으로 열 머리글을 얻는 방법은 무엇입니까?

예를 들어, 두 번째 공식을 사용하여 최대 판매가 있었던 달을 얼마나 효과적으로 표시했는지를 알 수 있습니다. 두 번째 수식에서는 MAX 함수 없이 첫 번째 수식의 뼈대를 사용했다는 사실을 쉽게 알 수 있습니다. 수식의 주요 구조는 VLOOKUP(B1,A5:G14,COLUMN(B5:G14),0)입니다. MAX 함수를 이전 수식에서 얻은 값을 첫 번째 인수로 사용하는 MATCH로 대체했습니다. 이제 이는 해당 월을 검색하는 기준으로 사용됩니다. 결과적으로 SEARCH 함수는 제품 4의 판매량 최대값이 있는 열 2의 번호를 반환하고 그 이후에는 날짜 및 열 번호별로 값을 반환하는 INDEX 함수가 작업에 포함됩니다. 인수에 지정된 범위에서. 열 번호는 2이고 월 이름이 저장되는 범위의 행 번호는 어쨌든 1이므로 B4 범위에서 해당 값을 얻으려면 INDEX 함수만 사용하면 됩니다. G4 - 2월(두 번째 달).

Excel 열에서 값 찾기

작업의 두 번째 옵션은 월 이름을 기준으로 사용하여 테이블을 검색하는 것입니다. 이러한 경우 수식의 기본 구조를 변경해야 합니다. 즉, VLOOKUP 함수를 HLOOKUP으로 바꾸고 COLUMN 함수를 ROW로 바꿔야 합니다.

이를 통해 특정 달에 최대 판매량과 제품이 무엇인지 확인할 수 있습니다.

특정 달에 최대 판매량을 보인 제품을 찾으려면 다음을 수행해야 합니다.

Excel 열에서 값을 찾는 공식의 원리:

HLOO(Horizontal View) 함수의 첫 번째 인수에서는 검색 기준이 있는 셀에 대한 링크를 나타냅니다. 두 번째 인수는 표시되는 테이블 범위에 대한 참조를 지정합니다. 세 번째 인수는 메모리에 행 번호의 10개 요소 배열을 생성하는 ROW 함수에 의해 생성됩니다. 표 형식 부분에 10개의 행이 있기 때문입니다.

다음으로, GLOOKUP 함수는 각 줄 번호를 차례로 사용하여 테이블에서 특정 달(6월)에 대한 해당 판매량 값의 배열을 생성합니다. 다음으로 MAX 함수는 이 배열에서 최대값만 선택하면 됩니다.

주목! 다른 문제에 뼈대 수식을 사용할 때는 항상 GLOOKUP 검색 기능의 두 번째 및 세 번째 인수에 주의하세요. 인수에 지정된 범위에 포함되는 행 수는 테이블의 행 수와 일치해야 합니다. 그리고 번호 매기기는 두 번째 줄부터 시작해야 합니다!

나는 엄청난 양의 데이터를 보유하고 있으며 특정 특성을 확인하기 위해 제품 이름 등으로 검색해야 하는 경우가 많습니다. 일반 검색을 사용하면 테이블에 열이 많아 다시 확인해야 하는 경우가 많기 때문에 불편합니다. 어떻게든 자동화할 수 있나요?

이는 VLOOKUP 또는 GLOOKUP을 사용하여 수행할 수 있습니다. VLOOKUP은 필요한 값이 열에 있는 경우 사용됩니다. GPR - 줄을 서 있는 경우. 그러나 기능의 순서와 작동 원리는 동일합니다. 데이터가 고유한 경우(즉, 반복되지 않는 경우) 이러한 기능을 사용할 수 있습니다.

데이터가 많다고 가정하기 때문에 콤보박스를 만들어 필요한 데이터를 입력하는 과정을 단순화하고 오류를 방지할 수 있습니다. 이렇게 하려면 콤보 상자가 될 셀에 커서를 놓습니다(나중에 검색 결과를 표시하는 데 사용할 데이터가 없는 빈 시트에 이 작업을 수행할 수 있습니다). 탭에서 데이터여러 떼 직업데이터가 있는 버튼을 클릭하면 데이터 검증드롭다운 목록에서 항목을 선택하세요. 데이터 확인...

대화 상자에서 시험탭에 옵션현장에서 데이터 유형선택하다 목록. 나타나는 필드에서 원천상품 이름이 포함된 셀 범위에 대한 링크를 표시합니다. 확인을 클릭하세요.

다음과 같은 드롭다운 목록이 나타나야 합니다.

이제 필요한 값을 검색하는 함수를 직접 생성해 보겠습니다. 검색이 수행될 테이블은 먼저 원하는 값을 기준으로 오름차순으로 정렬되어야 합니다. 이 예에서는 전화 브랜드를 기준으로 합니다.

생성된 드롭다운 목록 옆 줄이나 검색 결과를 표시하려는 다른 위치에 마우스 커서를 놓습니다. 수식 탭 또는 버튼 사용 삽입 기능~에 수식 입력줄 VLOOKUP 기능을 선택합니다.

테이블- 필요한 인수가 첫 번째 열에 있도록 데이터가 있는 테이블을 선택하고 F4 버튼을 누르거나 $ 기호를 수동으로 입력하여 링크가 절대화되도록 합니다. 헤더 없이 데이터만 있는 테이블을 선택합니다.

열_번호- 검색 결과로 보고 싶은 데이터가 있는 열의 일련 번호를 나타냅니다.

타임랩스_뷰- FALSE - 검색에 지정된 데이터와 정확히 일치해야 하기 때문입니다.

확인을 클릭하세요.

작고 보기 어리석은 동물 열 번호생성된 VLOOKUP 함수에서는 검색 조건에 맞는 소스 테이블의 여러 값을 선택할 수 있습니다.

VLOOKUP 함수를 사용한 검색과 유사하지만 데이터를 정렬하지 않고 검색을 수행할 수 있습니까? 이 경우 VLOOKUP에서는 오류 9가 발생합니다.

SEARCH 및 INDEX 두 함수를 사용하여 한 열에 있는 값을 알고 있으면 행에서 모든 값을 찾을 수 있습니다.

예를 들어, 휴대폰 브랜드 이름과 가격이 포함된 테이블을 살펴보겠습니다.

필요한 값에 대한 검색을 단순화하기 위해 검색을 위해 지정하는 데이터(예: 제품 이름)로 구성된 콤보 상자를 만들 수 있습니다. 이렇게하려면 커서를 빈 셀에 놓고 탭으로 이동하십시오. 데이터그룹 데이터 작업그리고 버튼을 누르세요 데이터 검증, 드롭다운 목록에서 항목을 선택합니다. 데이터 확인...대화 상자에서 입력된 값의 유효성 검사탭에 옵션현장에서 데이터 유형선택하다 목록검색할 데이터가 포함된 범위를 나타냅니다.

그런 다음 탭을 사용하여 빈 셀에 커서를 놓습니다. 방식또는 버튼 삽입 기능~에 수식 입력줄검색 기능을 선택하세요.

검색_값- 맨 처음에 만든 콤보 상자가 포함된 셀을 나타냅니다.

View_array- 찾고 있는 셀이 포함된 셀 범위를 선택합니다. 이 예에는 휴대폰 브랜드가 포함된 열이 있지만 헤더 없이 이를 강조 표시해야 합니다.

일치_유형- "0"(영)을 지정합니다. 이렇게 하면 함수가 원하는 값과 일치하는 첫 번째 인수를 찾을 수 있습니다.

확인을 클릭하세요.

MATCH 함수는 원하는 값이 포함된 줄을 나타냅니다. 수식이 포함된 셀에 커서를 놓으면 수식 입력줄에 함수 구문이 표시됩니다. "=" 기호까지 선택하고 복사한 다음(Ctrl+C를 눌러) Enter를 눌러 종료합니다. 셀.

그런 다음 결과를 표시할 빈 셀에 커서를 놓고 탭을 사용하여 방식또는 버튼 삽입 기능~에 수식 입력줄 INDEX 기능을 선택하세요. 이 기능두 가지 유형의 인수가 포함되어 있으며 이에 대한 정보는 즉시 제공됩니다. "배열; row_number"가 필요합니다.

대화 상자에서 함수 인수다음 값을 나타냅니다.

정렬- 원하는 값과 나중에 표시할 데이터가 모두 포함된 전체 데이터 배열(테이블 헤더 제외)을 선택합니다. 이 예에서는 전화기 브랜드와 가격이 포함된 열입니다.

번호_날짜- 여기에 Ctrl+V를 눌러 MATCH 기능을 삽입합니다.

열_번호- 여기에서 배열의 열 번호를 지정해야 하며 그 값은 검색 결과로 표시됩니다.

확인을 클릭하세요.