들어가기 전에
서식이 잘 지정되고, 필터링되고, 정렬된 표로 된 통합 문서는 작업이 끝났다고 생각할 수 있지만 더 많은 작업을 처리할 수 있습니다. Excel 표에서 정보를 추출하거나 요약할 때 가장 많이 사용하는 세 가지 함수 또는 함수 조합을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: My 3 Favorite Ways to Use Data in Excel Tables
- URL: https://www.howtogeek.com/ways-to-use-data-in-excel-tables/
1. VLOOKUP과 HLOOKUP
VLOOKUP 과 HLOOKUP은 모두 테이블의 특정 위치에서 값을 찾아 검색하는 데 사용됩니다.
- VLOOKUP은 데이터 테이블이 수직이라는 전제 하에 테이블의 첫 번째 열(수직)을 찾습니다.
- HLOOKUP은 데이터 테이블이 수평인지 확인하고 테이블의 첫 번째 행(수평)을 찾습니다.
VLOOKUP
시험 성적 목록과 각 성적에 필요한 점수가 있습니다(표 1). 또한 학생들의 점수가 포함된 표도 있습니다(표 2). Excel에서 표 1의 정보를 사용하여 표 2의 누락된 열을 채우고자 합니다.
이럴 때에는 VLOOKUP을 사용하면 됩니다. 왜냐하면 Excel에서 표 1의 첫 번째 열에 있는 값을 조회하여 표 2에 있는 각 학생의 성적을 반환하기를 원하기 때문입니다. VLOOKUP 함수의 구문은 다음과 같습니다.
=VLOOKUP( a , b , c , d )
- a는 조회할 값입니다(위의 예에서는 열 E의 값입니다).
- b는 참조 값을 포함하는 표입니다(이 경우 A1~B9 셀 또는 표 1).
- c는 해당 테이블의 열 번호입니다(등급을 반환하길 원하므로 테이블 1의 두 번째 열입니다).
- d 는 Excel에 대략적으로("TRUE") 또는 정확히("FALSE") 값을 찾도록 지시하는 선택적 기준입니다. 비워두면 기본값은 TRUE입니다.
F2 셀에 다음 수식을 입력하고 수식을 아래로 복사합니다.
=VLOOKUP(E2,$A$1:$B$9,2,TRUE)
HLOOKUP
동일한 등급 정보가 있지만 이번에는 수평으로 표시됩니다. 즉, 가져오려는 데이터는 경계 표의 두 번째 행에 있습니다.
HLOOKUP 함수의 구문은 VLOOKUP와 비슷합니다.
=HLOOKUP( a , b , c , d )
- a는 조회할 값입니다(이 예에서는 열 C의 값).
- b는 조회 값이 포함된 셀에 대한 절대 참조입니다(이 경우 A1~I2).
- c는 해당 테이블의 행 번호입니다(등급을 반환하길 원하므로 두 번째 행입니다).
- d (선택 사항)는 대략적인 값의 경우 "TRUE"이고 정확한 값의 경우 "FALSE"입니다.
C5 셀에 다음 수식을 입력하고 수식을 아래로 복사합니다.
=HLOOKUP(B5,$A$1:$I$2,2,TRUE)
2. INDEX와 MATCH
값을 조회하고 검색하는 또 다른 효과적인 방법은 INDEX와 MATCH를 사용하는 것입니다. 특히 함께 사용할 때 그렇습니다. INDEX는 정의된 위치에서 값을 찾아 반환하는 반면 MATCH는 값의 위치를 찾아 반환합니다. 두 가지를 함께 사용하면 동적 데이터 검색이 가능합니다.
개별 구문
이러한 함수를 함께 사용하는 방법을 살펴보기 전에 각 함수를 간단히 살펴보겠습니다.
INDEX의 구문은 다음과 같습니다.
INDEX(a,b,c)
a는 데이터가 들어 있는 셀 범위이고, b는 평가할 행 번호이며, c는 평가할 열 번호입니다
INDEX(B2:D8,4,2)
셀 B2부터 D8까지를 평가하고 해당 범위 내의 네 번째 행 두 번째 열에 있는 값을 반환합니다.
MATCH 함수는 다음과 같은 형식으로 사용합니다.
MATCH(x,y,z)
x는 찾으려는 값이고, y는 값을 찾을 범위이며, z (선택 사항)는 일치 유형입니다.
MATCH(5,B2:B8,0)
이 수식은 B2~B8 범위 내에서 숫자 5가 어디에 있는지 알려주고, 0 은 Excel에서 정확히 일치하는 항목을 찾으라는 의미입니다.
함께 사용
다음과 같은 예제에서, 특정 선수가 주어진 달에 넣은 골 수를 알고자 합니다. 더 구체적으로, 선수 C가 세 번째 달에 넣은 골 수를 알고 싶지만, 언제든지 이러한 기준을 변경할 수 있도록 하는 수식을 만들어 봅니다.
이렇게 하려면 Excel을 사용하여 플레이어 C가 표의 어느 위치에 있는지 확인한 다음, 데이터의 세 번째 열에 있는 값을 알려주는 것이 필요합니다.
(1) G4 셀에서, Excel이 원시 데이터에서 값을 찾아 반환하기를 원하기 때문에 INDEX 함수로 시작하겠습니다. 그런 다음 Excel에 해당 데이터를 찾을 위치를 알려줍니다.
=INDEX(B2:D8,
(2) INDEX 구문의 다음 부분은 행 번호이며, 이는 셀 G2에 지정한 플레이어에 따라 달라집니다. 예를 들어, 플레이어 A를 찾으려면 첫 번째 행이 됩니다. 이를 위해 MATCH 함수를 시작합니다.
(3) Excel에서 셀 G2에 입력한 플레이어를 플레이어 열(A2:A8)의 해당 셀과 일치시키고 해당 행 번호가 어느 것인지 알아내기를 원하기 때문입니다. 또한 Excel에서 정확한 검색 결과를 반환하기를 원하기 때문에 끝에 0을 추가했습니다.
=INDEX(B2:D8,MATCH(G2,A2:A8,0),
(4) 이제 Excel에 INDEX 함수의 행 번호를 알려줬으니, 열 번호로 마무리해야 합니다. 이 경우, 열 번호는 G3 셀에 입력한 월 번호를 나타냅니다.
=INDEX(B2:D8,MATCH(G2,A2:A8,0),G3)
(5) Enter 키를 누르면 Excel에서 플레이어 C가 3개월 동안 5골을 넣었다는 결과를 알려줍니다.
이제 조회 테이블에서 어떤 값이든 변경해 모든 달의 모든 플레이어의 총점을 찾을 수 있습니다.
3. COUNTIF와 SUMIF
이름에서 알 수 있듯이, 이 두 함수는 사용자가 설정한 기준에 따라 값을 계산하고 합산합니다.
COUNTIF
COUNTIF는 특정 기준을 포함하는 셀을 계산합니다. 구문은 다음과 같습니다.
COUNTIF(a, b)
a는 카운팅 하는 범위이고, b는 카운팅 기준입니다. 두 개 이상의 기준을 포함하려면 COUNTIFS를 사용합니다.
COUNTIFS(a, b, c, d)
a 와 b는 첫 번째 범위-기준 쌍이고, c 와 d는 두 번째 범위-기준 쌍입니다. 최대 127개 쌍을 가질 수 있으며, 기준이 텍스트이거나 논리 기호 또는 수학 기호인 경우에는 큰따옴표로 묶어야 합니다.
다음 급여표에서 40,000파운드 이상을 버는 사람의 수와 1,000파운드 이상의 보너스를 받는 서비스 직원의 수를 별도로 계산하려고 합니다.
급여가 40,000파운드가 넘는 근로자의 수를 세려면 셀 D8에 다음 수식을 입력합니다.
=COUNTIF(C2:C6,">40000")
C2:C6는 급여가 있는 범위이고, ">40000" 은 기준입니다.
이번에는 1,000파운드 이상의 보너스를 받는 군인의 수를 계산합니다. 두 가지 기준이 있으므로 COUNTIFS 함수를 사용합니다.
=COUNTIFS(B2:B6,"Services",D2:D6,">1000")
SUMIF
SUMIF는 설정한 기준에 따라 셀을 합산합니다. COUNTIF와 비슷한 원리로 작동하지만 괄호 안에 더 많은 인수가 있습니다. 구문은 다음과 같습니다.
SUMIF(a, b, c)
a는 합계를 계산하기 전에 평가하려는 셀 범위이고, b 는 해당 평가에 대한 기준(값이나 셀 참조가 될 수 있음)이며, c (선택 사항)는 a 와 다를 경우 추가할 셀입니다.
이번에는 세 가지를 계산해야 합니다. 40,000파운드가 넘는 급여의 합계, 서비스 부서의 총 급여, 그리고 급여가 35,000파운드가 넘는 직원의 보너스 합계입니다.
40,000파운드가 넘는 급여 합계를 계산하려면 다음 수식을 D8 셀에 입력합니다.
=SUMIF(C2:C6,">40000")
2:C6는 표의 급여를 참조하고, ">40000" 은 Excel에 이 금액보다 큰 값만 합산하라고 지시합니다.
다음으로, 서비스 부서의 총 급여를 알아보고자 합니다. 셀 C9에 다음 수식을 입력합니다.
=SUMIF(B2:B6,"Services",C2:C6)
B2:B6는 부서 열을 참조하고, "Services"는 Excel에 특히 서비스 부서의 직원을 찾고 있다고 알려주고, C2:C6는 Excel에 이러한 직원의 급여를 합산하라고 알려줍니다.
마지막 과제는 35,000파운드 이상을 버는 직원들이 보너스로 얼마나 벌었는지 알아내는 것입니다. 셀 C10에 다음을 입력합니다.
=SUMIF(C2:C6,">35000",D2:D6)
C2:C6는 Excel에 급여를 평가하라고 지시하고, ">35000" 은 해당 급여에 대한 기준이며, D2:D6는 Excel에 기준을 충족하는 개인의 보너스를 합산하라고 지시합니다.
SUMIFS
Excel에는 SUMIFS 함수도 있는데, 이 함수는 동일한 프로세스를 수행하지만 두 개 이상의 기준에 대해 수행합니다. SUMIF와는 매우 다른 구문을 사용합니다.
SUMIFS(a, b, c, d, e)
여기서 a는 합을 구할 셀 범위이고, b는 평가되는 첫 번째 범위이며, c 는 b 의 기준이고 , d 와 e는 다음 범위-기준 쌍입니다(최대 127개 쌍을 가질 수 있음).
위의 표를 사용하여, 45,000파운드 이상을 버는 인사 직원의 보너스를 합산하고 싶다고 가정하고, 입력할 수식은 다음과 같습니다.
=SUMIFS(D2:D6,B2:B6,"Personnel",C2:C6,">45000")
마치며
이상에서 설명한 함수를 모두 익혔다면 XLOOKUP 함수도 사용해보세요 . 이 함수는 데이터를 다시 정렬하지 않고도 조회 값 열의 왼쪽과 오른쪽에 있는 값을 조회하여 VLOOKUP의 단점 중 일부를 해결합니다.
'Excel' 카테고리의 다른 글
| 엑셀에서 파이썬 사용하는 법 (5) | 2024.11.11 |
|---|---|
| Excel로 캘린더를 만드는 3가지 방법 (2) | 2024.11.10 |
| Excel을 마스터 하기 위한 최고의 리소스 5가지 (6) | 2024.11.08 |
| Excel에서 중복을 제거하는 3가지 방법 (3) | 2024.11.07 |
| Excel의 피벗 테이블은 실제로 어떤 용도로 쓰이나요? (4) | 2024.11.07 |
