엑셀과 구글 시트에서 데이터 정리를 자동화하는 필수 함수 모음

엑셀과 구글 스프레드시트에서 데이터를 정리할 때 반복적인 수작업을 줄이고 자동화하는 것이 핵심입니다. 데이터를 효율적으로 정리하면 분석과 보고가 더욱 쉬워지고 업무 효율성이 크게 향상됩니다. 이번 글에서는 엑셀과 구글 시트에서 데이터 정리를 자동화하는 필수 함수 모음을 소개하겠습니다.

---

1. 공백 및 불필요한 문자 제거 함수

데이터를 정리할 때 가장 먼저 해야 할 일은 공백과 불필요한 문자 제거입니다.

1-1. TRIM 함수 – 불필요한 공백 제거

excel복사편집=TRIM(A1)

✅ 기능:

• 텍스트 앞뒤 공백 및 연속된 공백을 하나로 줄임
• 중간 공백은 유지 (예: “사 과” → “사 과”)

✅ 활용 예시

A열 (원본 데이터)	B열 (TRIM 적용)
” 사과 “	“사과”
” 바나나 포도 “	“바나나 포도”

---

1-2. SUBSTITUTE 함수 – 특정 문자 및 모든 공백 제거

excel복사편집=SUBSTITUTE(A1, " ", "")

✅ 기능:

• 모든 공백을 삭제 (예: “사 과” → “사과”)
• 특정 문자를 다른 문자로 변경 가능

✅ 활용 예시

A열 (원본 데이터)	B열 (SUBSTITUTE 적용)
“구글 시트 활용법”	“구글시트활용법”
” 데이터 정리 “	“데이터정리”

💡 SUBSTITUTE 함수와 TRIM 함수를 결합하면 더욱 정교한 데이터 정리가 가능합니다.

excel복사편집=SUBSTITUTE(TRIM(A1), " ", "")

---

2. 데이터 중복 제거 및 정리 함수

데이터 정리에서 중복 제거는 필수적인 과정입니다.

2-1. UNIQUE 함수 – 중복 제거

excel복사편집=UNIQUE(A1:A100)

✅ 기능:

• 중복된 값을 제거하고 고유한 값만 반환
• 새로운 정리된 데이터 목록을 자동 생성

✅ 활용 예시

A열 (원본 데이터)	B열 (UNIQUE 적용)
사과	사과
바나나	바나나
사과	(제거됨)
포도	포도

---

2-2. COUNTIF 함수 – 중복값 확인

excel복사편집=COUNTIF(A:A, A1) > 1

✅ 기능:

• 해당 값이 데이터 범위 내 몇 번 등장하는지 확인
• 중복된 데이터 여부를 TRUE/FALSE로 표시

✅ 활용 예시

A열 (데이터)	B열 (중복 여부)
사과	TRUE
바나나	FALSE
사과	TRUE
포도	FALSE

필터를 활용해 TRUE 값이 있는 행을 삭제하면 중복을 쉽게 제거할 수 있습니다.

---

3. 데이터 정렬 및 필터링 함수

데이터를 정렬하고 필터링하면 더욱 쉽게 분석할 수 있습니다.

3-1. SORT 함수 – 자동 정렬

excel복사편집=SORT(A1:A100)

✅ 기능:

• 데이터를 오름차순 또는 내림차순으로 정렬
• 원본 데이터를 유지하면서 정렬된 목록을 생성

✅ 활용 예시

A열 (원본 데이터)	B열 (SORT 적용)
포도	바나나
바나나	사과
사과	포도

오름차순이 기본이며, 내림차순 정렬을 원하면 다음과 같이 설정하면 됩니다.

excel복사편집=SORT(A1:A100, 1, FALSE)

---

3-2. FILTER 함수 – 조건에 맞는 데이터만 표시

excel복사편집=FILTER(A1:A100, A1:A100="사과")

✅ 기능:

• 특정 조건을 충족하는 데이터만 표시
• 다양한 조건을 적용하여 데이터를 필터링 가능

✅ 활용 예시
“사과”가 포함된 데이터만 추출하려면 위 함수를 사용하면 됩니다.

---

4. 데이터 자동 계산 및 변환 함수

데이터를 정리한 후에는 자동으로 계산되는 함수를 활용하는 것이 중요합니다.

4-1. ARRAYFORMULA – 다중 셀에 자동 적용

excel복사편집=ARRAYFORMULA(A1:A100 * 2)

✅ 기능:

• 한 번의 수식 입력으로 전체 열에 적용
• TRIM, SUBSTITUTE, UNIQUE 등과 함께 사용 가능

✅ 활용 예시
A열의 값에 2를 곱한 값을 B열에 표시

---

4-2. IF 함수 – 조건에 따라 다른 값 반환

excel복사편집=IF(A1>100, "고가", "저가")

✅ 기능:

• 조건이 참이면 첫 번째 값, 거짓이면 두 번째 값 반환
• 데이터 분석과 분류에 유용

✅ 활용 예시

A열 (가격)	B열 (분류)
150	고가
90	저가
200	고가

---

5. Google Apps Script를 활용한 데이터 자동 정리

더 강력한 자동화를 원한다면 Google Apps Script를 활용할 수 있습니다.

✅ 모든 공백 제거 후 자동 정리하는 스크립트

javascript복사편집function cleanData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      values[i][j] = values[i][j].toString().trim();
    }
  }
  
  range.setValues(values);
}

이 코드를 실행하면 시트 전체의 공백이 자동으로 정리됩니다.

---

Q&A

Q1. 데이터 정리를 자동화하는 가장 쉬운 방법은?

TRIM, UNIQUE, SORT, ARRAYFORMULA 같은 기본 함수를 활용하는 것이 가장 간단한 방법입니다.

Q2. 여러 열을 한 번에 정리할 수 있나요?

네. ARRAYFORMULA와 UNIQUE 함수를 결합하면 가능합니다.

excel복사편집=ARRAYFORMULA(UNIQUE(A1:C100))

Q3. 자동 정리를 매일 실행할 수 있나요?

Google Apps Script에서 트리거 설정을 활용하면 특정 시간마다 자동 실행되도록 설정할 수 있습니다.