[엑셀 기초] VLOOKUP부터 피벗테이블까지, 실무 필수 함수 완벽 정리

1. 업무 효율을 높이는 엑셀 자동화의 기초

재테크 자산 관리나 사무 행정 업무에서 가장 중요한 것은 반복되는 노가다를 줄이는 것입니다. 오늘은 실무에서 가장 빈번하게 사용되는 핵심 함수 4가지와 데이터 분석의 꽃인 피벗테이블 활용법을 예제를 통해 정리해보겠습니다.

 

 

위의 미완성본을 아래 완성본으로 채워나가보겠습니다.

 

 

https://www.youtube.com/watch?v=JdVuBqHqeos 유튜브 공여사들에서 Raw Data를 참고했습니다.

---

2. VLOOKUP 함수: 데이터 자동 매핑

특정 값을 기준으로 다른 표에서 원하는 정보를 찾아오는 함수입니다.

• 활용 예시: '이름'을 입력하면 '소속팀'과 '직급'이 자동으로 입력되도록 설정할 수 있습니다.
• 함수 공식: =VLOOKUP(찾을값, 참조범위, 열번호, 0)
• 실습 내용: 기준정보 테이블(H2:J11)을 고정하고 데이터 영역(B3:G22)에 값을 불러오는 구조를 설계했습니다.

 

3. INDEX & MATCH: VLOOKUP의 한계 극복

VLOOKUP은 기준 열의 오른쪽에 있는 데이터만 가져올 수 있지만, INDEX와 MATCH를 결합하면 위치에 상관없이 데이터를 탐색할 수 있습니다.

• INDEX: 범위 내에서 특정 행과 열이 교차하는 값을 반환합니다.
• MATCH: 찾는 값이 특정 범위 내에서 몇 번째 위치에 있는지 숫자로 알려줍니다.
• 결합 사용: =INDEX(출력범위, MATCH(기준값, 기준범위, 0)) 구조로 사용하면 훨씬 유연한 데이터 관리가 가능합니다.
• 실습 내용: =INDEX($I$3:$I$11,MATCH(B3,$J$3:$J$11,0))을 사용하면 ( $는 범위를 고정시키는 역할로, F4를 클릭하면 입력할 수 있습니다. ) 소속팀이 어떤 본부인지 불러오는 구조를 만들 수 있습니다.

4. SUMIF & COUNTIF: 조건부 통계 및 집계

수많은 데이터 중 특정 조건에 맞는 데이터만 골라 합계를 구하거나 개수를 셀 때 사용합니다.

• SUMIF: 조건에 맞는 셀의 합계를 구합니다. (예: 본부별 점심 식대 합산)

• COUNTIF: 조건에 맞는 셀의 개수를 셉니다. (예: 메뉴별 주문 수량 파악)

• 결과 확인: 실습 파일 우측의 '답안' 영역에서 본부별 금액과 메뉴별 수량이 정확히 집계되는 것을 확인할 수 있습니다.

5. 피벗테이블: 데이터 분석과 요약

함수를 일일이 입력하지 않고도 대량의 데이터를 마우스 드래그만으로 요약할 수 있는 강력한 기능입니다.

• 전체 데이터를 선택한 뒤(표 아무 셀을 클릭하고 Control + A 를 누르면 전체 데이터가 선택됩니다.)[삽입] > [피벗테이블]을 실행하면, 복잡한 집계표를 단 몇 초 만에 생성할 수 있습니다.

 

• 추가할 위치에 따라 새 워크시트 혹은 기존 워크시트를 선택하고, 기존 워크시트라면 범위를 마우스로 지정해주면 됩니다. 
• 예시대로 팀에따른 금액합계를 구하고싶다면 행에 소속팀, 값에 금액을 드래그해서 넣으시면 됩니다. 

---

6. 엑셀 실습 파일 공유 안내

포스팅에 사용된 '메뉴별 자동 집계 시스템' 실습 파일 원본이 필요하신 분들을 위해 자료를 공유해 드립니다.

• 공유 방법: 첨부 파일을 확인해주세요 

엑셀.xlsx

0.02MB