데이터 분석 함수 (VLOOKUP, INDEX, MATCH 등)
직장인들이 자주 겪는 문제:
- 복잡한 데이터를 요약해야 하는 경우: 대량의 데이터를 분석하여 통찰력을 얻고 리포트를 작성해야 할 때.
- 데이터 간 연관 관계를 파악해야 하는 경우: 특정 값에 해당하는 데이터를 검색하거나 관련 데이터를 연결해야 할 때.
1. VLOOKUP 및 HLOOKUP 함수: 데이터 검색
VLOOKUP 함수: 세로 방향 데이터 검색
사용 사례:
문제: 제품 코드가 "A101"인 상품의 가격을 검색.
사용 방법:
=VLOOKUP("A101", A2:D100, 3, FALSE)- "A101": 검색하려는 값.
A2:D100: 검색 범위.
3: 반환할 열 번호(검색 범위에서 세 번째 열).
FALSE: 정확히 일치하는 값만 반환.
응용 팁:
- 범위 참조: 동적 이름 범위를 사용하면 데이터 추가 시 자동으로 업데이트됩니다.
=VLOOKUP(A1, ProductTable, 2, FALSE)
- 오류 처리: 검색값이 없을 경우 오류를 방지하기 위해
IFERROR를 사용.=IFERROR(VLOOKUP("A101", A2:D100, 3, FALSE), "Not Found")
HLOOKUP 함수: 가로 방향 데이터 검색
사용 사례:
문제: 특정 월의 매출 데이터를 가로 테이블에서 검색.
사용 방법:
=HLOOKUP("2024년 1월", A1:G5, 2, FALSE)- "2024년 1월": 검색할 값.
A1:G5: 검색 범위.
2: 반환할 행 번호.
FALSE: 정확히 일치하는 값을 반환.
활용 팁:
HLOOKUP은 테이블이 가로로 정렬된 경우 유용합니다.
- 테이블 구조를 동적으로 변경하려면
INDEX와 조합하여 사용 가능.
2. INDEX 및 MATCH 함수: 위치 기반 데이터 검색
INDEX 함수
INDEX는 특정 위치의 값을 반환하는 강력한 도구입니다.
사용 사례:
문제: "B3" 셀 위치에서 데이터를 가져오기.
=INDEX(A1:D10, 3, 2)A1:D10: 데이터 범위.
3: 행 번호.
2: 열 번호.
MATCH 함수
MATCH는 특정 값의 위치를 찾는 데 사용됩니다.
사용 사례:
문제: 제품 코드 "C202"의 행 번호를 찾기.
=MATCH("C202", A1:A100, 0)A1:A100: 검색 범위.
0: 정확히 일치하는 값 검색.
INDEX와 MATCH의 조합
사용 사례:
문제: 제품 코드 "D303"의 가격을 검색(행과 열 기반).
=INDEX(B2:D100, MATCH("D303", A2:A100, 0), 3)MATCH: "D303"의 위치를 찾음.
INDEX: 위치를 기준으로 데이터를 반환.
응용 팁:
MATCH와INDEX는VLOOKUP보다 유연하여 열 순서 변경 시에도 영향을 받지 않습니다.
- 동적 범위 지정과 함께 사용하면 데이터 확장에 유리합니다.
테이블 1: 제품 정보
| 제품코드 | 제품명 | 카테고리 | 가격 |
| A101 | 노트북 | 전자제품 | 1,000,000 |
| A102 | 스마트폰 | 전자제품 | 800,000 |
| A103 | 이어폰 | 액세서리 | 50,000 |
| A104 | 키보드 | 컴퓨터용품 | 30,000 |
| A105 | 모니터 | 전자제품 | 300,000 |
테이블 2: 월별 매출
| 2024년 1월 | 2024년 2월 | 2024년 3월 | |
| A101 | 10 | 15 | 12 |
| A102 | 20 | 18 | 25 |
| A103 | 50 | 60 | 45 |
| A104 | 30 | 25 | 20 |
| A105 | 5 | 8 | 10 |
1. LOOKUP 함수: 단순 검색
예제:
- 문제: 제품코드 "A103"의 카테고리를 검색.
- 사용 방법:
=LOOKUP("A103", A2:A6, C2:C6)
- 결과값: 액세서리
2. VLOOKUP 함수: 세로 방향 데이터 검색
예제:
- 문제: 제품코드 "A102"의 가격을 검색.
- 사용 방법:
=VLOOKUP("A102", A2:D6, 4, FALSE)
- 결과값: 800,000
응용:
- 오류 처리:
=IFERROR(VLOOKUP("A106", A2:D6, 4, FALSE), "Not Found")
- 결과값: Not Found (A106은 테이블에 없음)
3. HLOOKUP 함수: 가로 방향 데이터 검색
예제:
- 문제: 2024년 2월의 A103 판매량 검색.
- 사용 방법:
=HLOOKUP("2024년 2월", B1:D6, 4, FALSE)
- 결과값: 60
4. INDEX 함수: 위치 기반 데이터 검색
예제:
- 문제: 제품코드 "A104"의 카테고리를 검색.
- 사용 방법:
=INDEX(C2:C6, MATCH("A104", A2:A6, 0))
- 결과값: 컴퓨터용품
5. MATCH 함수: 위치 검색
예제:
- 문제: 제품코드 "A105"가 몇 번째 행에 있는지 확인.
- 사용 방법:
=MATCH("A105", A2:A6, 0)
- 결과값: 5 (A105는 5번째 행에 위치)
종합 응용 예제: INDEX와 MATCH의 조합
- 문제: 제품코드 "A102"의 2024년 3월 판매량 검색.
- 사용 방법:
=INDEX(B2:D6, MATCH("A102", A2:A6, 0), MATCH("2024년 3월", B1:D1, 0))
- 결과값: 25
추가함수
COUNTBLANK:- 비어 있는 셀 개수 계산.
- 예:
=COUNTBLANK(A1:A10).
RANK:- 값의 순위를 반환.
- 예:
=RANK(A1, A1:A10).
FORECAST:- 예측값 계산.
- 예:
=FORECAST(15, A1:A10, B1:B10).