실무에서 가장 많이 사용되는 엑셀 - ⅴ

 

엑셀 실무에서 가장 많이 사용되는 함수와 기능: 다섯 번째 글

이번 글에서는 데이터 변환 및 정리를 중심으로, 엑셀의 강력한 데이터 처리 도구인 Power Query와 Flash Fill 기능을 소개합니다. 이 두 가지 도구는 데이터의 구조를 변경하거나 반복적인 작업을 자동화하는 데 매우 유용하며, 실무에서 데이터를 효율적으로 관리할 수 있도록 돕습니다.

1. Power Query: 데이터 추출, 변환, 로드(ETL)

Power Query는 엑셀에서 데이터를 효율적으로 처리하고 변환할 수 있는 도구입니다. 특히, 여러 데이터 소스에서 데이터를 가져와 정리하거나 통합할 때 유용합니다.

반응형

Power Query 주요 기능:

• 데이터 추출: 다양한 소스(SQL, CSV, Excel 등)에서 데이터를 가져올 수 있습니다.
• 데이터 변환: 중복 제거, 열 분할, 데이터 형식 변경 등 다양한 변환 작업을 수행할 수 있습니다.
• 데이터 로드: 변환된 데이터를 엑셀 워크시트 또는 데이터 모델로 로드합니다.

예시: 열 분할과 피벗 작업

• 상황: 아래와 같은 데이터를 처리해야 한다고 가정합니다.
  

  KeyID	Sales Rep	Amount
  A001	John Smith	500
  A002	Sarah Lee	300

   
• 작업 목표: Sales Rep과 Amount를 분리하고 KeyId 기준으로 피벗 테이블 생성.

단계:

1. Power Query 활성화:
   • 데이터 탭 > Power Query > 데이터 가져오기 > 워크시트 선택.
2. 열 분할:
   • Sales Rep 열을 선택하고 "열 분할" > 구분 기호(공백) 기준으로 분리.
3. 피벗 테이블 생성:
   • KeyId를 기준으로 피벗하여 데이터를 재구조화.
4. 결과:
   • 정리된 데이터가 새로운 워크시트에 로드됩니다.

2. Flash Fill: 패턴 인식으로 데이터 자동 변환

Flash Fill은 데이터를 자동으로 변환하는 엑셀 내장 기능입니다. 패턴을 인식하여 데이터를 정리하거나 결합하는 데 유용하며, 복잡한 수식 없이도 빠르게 작업을 완료할 수 있습니다.

Flash Fill 사용법:

1. 원본 데이터 옆에 원하는 결과의 첫 번째 값을 입력합니다.
2. 엑셀이 패턴을 인식하면 나머지 셀에 동일한 작업을 자동으로 적용합니다.
3. 단축키 Ctrl + E를 눌러 Flash Fill을 실행합니다.

728x90

예시: 이름 결합

• 상황: 아래와 같은 데이터를 처리한다고 가정합니다.
  

  Fiist Name	Last Name
  John	Smith
  Sarah	Johnson

   
• 작업 목표: 이름과 성을 결합하여 전체 이름 생성.

단계:

1. 새 열에 "John Smith"를 입력합니다.
2. Ctrl + E를 눌러 Flash Fill 실행.
3. 결과:

First Name	Last Name	Full Name
John	Smith	John Smith
Sarah	Johnson	Sarah Johnson

 

3. TEXTJOIN 함수

TEXTJOIN 함수는 여러 텍스트 값을 하나로 결합하며, 특정 구분 기호를 삽입할 수 있습니다.

사용법:

=TEXTJOIN(", ", TRUE, A1:A3)

예시: 목록 결합

• 상황: 과일 목록을 하나의 문자열로 결합.
• 데이터:
• 과일

  Apple

  Banana

  Cherry

   
• 공식: =TEXTJOIN(", ", TRUE, A1:A3)
• 결과: "Apple, Banana, Cherry"

SMALL

4. Transpose 함수

Transpose 함수는 행과 열의 위치를 바꿉니다.

사용법:

1. 데이터를 복사합니다.
2. 붙여넣기 옵션에서 "Transpose"를 선택합니다.

예시: 행과 열 전환

• 원본 데이터:
  

  A	B
  1	2
  3	4

   
• 결과:
  

  	A	B
  Row1	1	3
  Row2	2	4