Excel 테이블에서 데이터를 사용하는 3 가지 가장 좋아하는 방법

Excel 데이터 추출 및 요약을위한 세 가지 실용적인 기능 아름답게 포맷되고 필터링 된 테이블이있는 대형 통합 문서가 있다고 가정합니다. 작업이 완료되었다고 생각할 수도 있지만 실제로 Excel 은이 테이블에 대해 더 많은 일을하기를 기다리고 있습니다. 이 기사는 Excel 테이블에서 정보를 추출하거나 요약하는 데 사용될 수있는 세 가지 기능 또는 기능의 조합을 소개합니다.

vlookup and hlookup

vlookup 및 hlookup은 테이블의 특정 위치에서 값을 찾고 검색하는 데 사용됩니다.

vlookup은 수직 데이터 테이블에 의존하고 테이블의 첫 번째 열 (수직)을 찾습니다. hlookup은 수평 데이터 테이블에 의존하고 테이블의 첫 번째 행 (수평)을 찾습니다.

vlookup 여기에는 테스트 점수 목록과 각 학년에 필요한 등급이 있습니다 (표 1이라고합니다). 또한 학생 성적이있는 테이블이 있습니다 (이 시점에서 표 2라고 함). Excel이 표 1의 정보를 사용하여 표 2의 누락 된 열을 완성하기를 원합니다.

나는 Excel이 표 1의 첫 번째 열에서 값을 찾아 표 2의 각 학생의 성적을 반환하기를 원하기 때문에 Vlookup을 사용합니다. Vlookup 함수의 구문은 다음과 같습니다.
of :
a

는 찾아야 할 값입니다 (위의 예에서는 e의 값입니다) b

는 기준 값을 포함하는 테이블입니다 (이 경우 셀 A1 내지 B9 또는 표 1) c

는 해당 테이블의 열 번호입니다 (레벨을 반환하기를 원하므로 표 1의 두 번째 열입니다). d

는 조회 값 ( "true") 또는 정확한 조회 값 ( "False")을 근사할지 여부를 알리는 선택 조건입니다. 비워두면 기본값이 참입니다.

따라서, 제 경우에는 셀 F2 에이 공식을 입력하여 TOM의 등급을 계산 한 다음 AutoFill을 사용하여 테이블에서 다른 등급을 찾습니다. > 나는 $ 기호를 사용하여 위의 값

b

에 대한 절대 참조를 만들었습니다. 점수 경계 테이블에는 단일 점수에 할당 된 순위가 아닌 범위가 포함되어 있기 때문에 값

d

<code>=VLOOKUP(a,b,c,d)</code>

에 "true"를 사용했습니다.

hlookup 여기, 우리는 동일한 레벨 경계 정보를 가지고 있지만 이번에는 수평으로 표시됩니다. 이것은 우리가 가져 오려는 데이터가 경계 테이블의 두 번째 행에 있음을 의미합니다.
• hlookup 함수의 구문은 vlookup의 구문과 유사합니다.
• of :
  • a 는 찾을 수있는 값입니다 (이 예에서는 C 열의 값), b 는 검색 값을 포함하는 셀에 대한 절대적인 참조입니다 (이 경우 A1 ~ i2) c
  는 테이블의 행 번호입니다 (레벨을 반환하기를 원하므로 두 번째 행입니다). d (선택 사항)은 "true"(근사값) 또는 "false"(정확한 값)입니다. 그래서 나는 셀 C5 에이 공식을 입력하여 TOM의 등급을 계산 한 다음 AutofILL을 사용하여 표에서 다른 등급을 찾습니다 :
.
색인 및 일치 값을 찾고 검색하는 또 다른 효과적인 방법은 특히 함께 사용될 때 인덱스와 일치를 통한 것입니다. 인덱스는 정의 된 위치의 값을 찾고 반환하는 반면 매치는 값의 비트를 찾고 반환합니다. 함께 동적 데이터 검색을 구현할 수 있습니다.
• 단일 문법 이러한 기능을 함께보기 전에 간단히 개별적으로 보자. 인덱스의 구문은 입니다

a 는 데이터를 포함하는 셀의 범위이고, b

는 평가할 행 번호이며, c

<code>=VLOOKUP(a,b,c,d)</code>

는 평가 될 열 숫자입니다.

이를 기반으로 는 셀 B2에서 D8을 평가하고 해당 범위의 네 번째 행과 두 번째 열에서 값을 반환합니다.

일치하려면

를 따릅니다 여기서 x

는 우리가 찾고있는 값, y

는 우리가 찾고있는 값의 범위이고 z

(선택 사항)은 다음과 같습니다. 일치하는 유형.

이를 기반으로

는 숫자 5가 B2에서 B8 범위의 위치를 ​​알려주고 0은 Excel에게 정확한 일치를 수행하도록 지시합니다.

조합으로 사용 이 예에서는 Excel이 주어진 달에 지정된 플레이어가 점수를 매기는 목표 수를 알려주기를 원합니다. 보다 구체적으로, 나는 3 개월 동안 플레이어가 얼마나 많은 목표를 득점했는지 알고 싶지만 언제든지 해당 조건을 변경할 수 있도록이 공식을 만들 것입니다.

이렇게하려면 테이블에서 플레이어 C의 위치를 ​​결정한 다음 데이터의 세 번째 열에서 값을 알려주기 위해 Excel이 필요합니다.

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

셀 G4에서는 Excel이 원래 데이터에서 찾아보고 값을 반환하기를 원하기 때문에 인덱스 기능으로 시작합니다. 그런 다음 해당 데이터를 찾아야 할 곳을 Excel에게 알려 드리겠습니다.

인덱스 구문의 다음 부분은 줄 번호이며 셀 G2에서 선언하는 플레이어에 따라 다릅니다. 예를 들어, 플레이어 A를 찾으려면 첫 번째 행입니다. 이렇게하려면 Excel이 셀 G2의 플레이어를 플레이어 열 (A2 : A8)의 해당 셀과 일치시키고 어떤 행이 있는지 알아 내기 때문에 경기 기능을 시작합니다. Excel이 정확한 검색을 반환하기를 원하기 때문에 마지막 0을 추가했습니다.

<code>=VLOOKUP(a,b,c,d)</code>

이제 Excel Index Function Line 번호에 말했기 때문에 열 번호로 수행해야합니다. 내 예에서 열 번호는 셀 G3에서 입력 한 월 번호를 나타냅니다.

Enter 키를 눌렀을 때 Excel은 3 개월 동안 플레이어 C가 5 골을 넣었다 고 올바르게 말했습니다.

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

이제 조회 테이블의 값을 변경하여 한 달에 플레이어의 총계를 찾을 수 있습니다.

countif and sumif

이름에서 볼 수 있듯이이 두 기능은 설정 한 조건에 따라 계산 및 합계 값을 계산합니다. 지정된 범위 내에 있더라도 조건에 포함되지 않은 것은 추가 또는 계산되지 않습니다.

countif Countif는 특정 조건을 포함하는 세포를 계산합니다. 구문은 입니다

여기서 는 계산하려는 범위이고 b 는 계산 조건입니다. 마찬가지로, 여러 조건을 포함시키고 싶다면 Countif를 사용합니다. 여기서 a 는 첫 번째 범위 조건 페어링이고, c

및 d

는 두 번째 범위 조건 페어링입니다 (귀하는 귀하 최대 127 쌍을 가질 수 있습니다).

조건이 텍스트 또는 논리적 또는 수학적 기호 인 경우 이중 인용문으로 둘러싸여 있어야합니다.

아래의 급여 테이블에서, 나는 40,000 파운드 이상을 벌고있는 사람들의 수와 £ 1,000 이상의 보너스를 별도로받는 사람들의 수를 계산하고 싶습니다.

<code>=HLOOKUP(a,b,c,d)</code>

급여가 £ 40,000 이상인 직원의 수를 계산하려면 셀 D8 에서이 공식을 입력해야합니다.

여기서 c2 : c6 ​​는 급여의 범위, "& gt; 40000"는 조건입니다. £ 1,000 이상의 보너스를받는 서비스 직원 수를 계산하려면 두 가지 조건이 있기 때문에 Countif를 사용합니다.

b2 : b6, "services" 부분은 첫 번째 범위 조건부 페어링,

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

d2 : d6, "& gt; 1000"

입니다. 위의 테이블에 쉼표가있는 수천 자리를 분리하더라도 쉼표마다 다른 기능이 있기 때문에이 쉼표를 공식에 포함시키지 않습니다. sumif sumif는 설정 한 조건에 따라 세포를 합산합니다. Countif와 유사하게 작동하지만 괄호 안에 더 많은 매개 변수를 포함합니다. 구문은 입니다 여기서 는 요청하기 전에 평가하려는 셀의 범위입니다. b

는 해당 평가 조건입니다 (이것은 값 또는 셀 참조 일 수 있음),

C (옵션)는 와 다른 경우 셀을 추가 할 셀입니다.

이번에는 세 가지를 계산해야합니다. 40,000 파운드 이상의 총 급여, 서비스 부서의 총 급여 및 £ 35,000 이상인 직원의 총 보너스.

먼저, 40,000 파운드 이상의 총 급여를 계산하려면 셀 D8에서 다음 공식을 입력해야합니다.

여기서 c2 : c6 ​​

테이블의 급여를 인용합니다.

다음으로 서비스 부서의 총 급여를 찾고 싶습니다. 셀 C9에서는

<code>=VLOOKUP(a,b,c,d)</code>

를 입력합니다

where b2 : b6 인용 부서 열, "서비스" 엑셀에게 서비스 부서의 직원을 찾고 있습니다. 이 직원들의 임금에 대해 합산됩니다. 나의 마지막 임무는 직원들이 35,000 파운드 이상을받는 보너스의 양을 찾는 것이 었습니다. 셀 C10에서는 를 입력합니다

여기서 c2 : c6 ​​

는 임금을 평가하기 위해 Excel에게 지시합니다. 개인 보너스 금액.

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

Excel에는 동일한 절차를 수행하지만 여러 조건에 대해 Sumifs 함수도 있습니다. Sumif의 구문과 매우 다릅니다 : 는 추가 할 셀의 범위, b 는 평가 될 첫 번째 범위, c is , > d

및 e 의 조건은 다음 범위 조건 페어링입니다 (최대 127 쌍을 가질 수 있음). 위의 표를 사용하여 £ 45,000 이상을 벌 수있는 직원의 보너스를 요약하고 싶다고 가정 해 봅시다. 다음은 입력 할 공식입니다

<code>=HLOOKUP(a,b,c,d)</code>

위의 함수를 마스터 한 후 Xlookup 함수를 사용해보십시오. Xlookup 함수를 사용해보십시오. Xlookup 함수를 사용해보십시오. Xlookup 함수는 데이터를 재정렬하지 않고 찾기 열의 왼쪽과 오른쪽에서 값을 찾아 VLOOKUP의 일부 단점을 해결하도록 설계되었습니다.

위 내용은 Excel 테이블에서 데이터를 사용하는 3 가지 가장 좋아하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!