[엑셀 실무] 흩어진 데이터, 조건별로 텍스트 합치고 개수 세는 법 (TEXTJOIN, COUNTIF)

안녕하세요! 여러분의 칼퇴를 돕는 Dr. 데일리입니다.

학교 선생님이나 인사팀, 혹은 회원 관리를 하시는 분들이라면 이런 고민 한 번쯤 해보셨을 겁니다.

"엑셀 데이터가 세로로 쭉 나열되어 있는데, 이걸 담당자별로 한 줄에 싹 정리할 수는 없을까?"

예를 들어, 왼쪽 표처럼 선생님별로 학생이 한 명씩 입력된 데이터를, 오른쪽 표처럼 선생님 한 명당 학생 명단을 한 셀에 몰아넣고 인원수까지 구하고 싶은 경우죠.

(예시: A열-선생님, B열-학생 데이터가 있는 화면 캡처)

이거 하나하나 복사해서 붙여넣기 하셨나요? 오늘 알려드리는 두 가지 함수만 알면 10초 만에 해결할 수 있습니다. 바로 TEXTJOIN과 COUNTIF 함수입니다.

1. 조건에 맞는 텍스트 합치기: TEXTJOIN + IF 함수

가장 핵심적인 기능입니다. "홍길동 선생님이 담당하는 학생들만 콤마(,)로 연결해서 가져와라"라는 명령을 내려야 합니다.

과거에는 VBA를 쓰거나 복잡한 함수를 썼지만, 최신 엑셀에서는 TEXTJOIN 함수가 구세주입니다.

📌 사용 공식

정리할 표의 [담당 학생] 셀에 아래 수식을 입력하세요.

=TEXTJOIN(", ", TRUE, IF($A$2:$A$15=D2, $B$2:$B$15, ""))

🔍 수식 뜯어보기

이 수식이 어떻게 작동하는지 원리를 알면 응용하기 쉽습니다.

1. ", ": 텍스트 사이를 무엇으로 구분할지 정합니다. 여기선 쉼표와 띄어쓰기를 넣었습니다.

2. TRUE: 빈 셀은 무시하라는 뜻입니다. (이게 아주 중요합니다!)

3. IF(...): 여기가 핵심입니다.

   • $A$2:$A$15=D2: 원본 데이터의 선생님 범위(A열)에서 현재 행의 선생님 이름(D2)과 같은지 확인합니다.

   • $B$2:$B$15: 같다면 학생 이름(B열)을 가져옵니다.

   • "": 같지 않다면 빈칸으로 둡니다.

⚠️ 주의사항 (구버전 사용자)

Microsoft 365나 엑셀 2021 이상 버전에서는 그냥 엔터를 치면 되지만, 2019 이전 버전을 사용 중이라면 수식 입력 후 반드시 Ctrl + Shift + Enter를 동시에 눌러야 배열 수식으로 작동합니다. (수식 양옆에 중괄호 {}가 생기면 성공!)

2. 조건에 맞는 개수 구하기: COUNTIF 함수

학생 명단을 가져왔으니, 이제 몇 명인지 세어볼까요? 이건 훨씬 간단합니다. COUNTIF 함수를 사용합니다.

📌 사용 공식

정리할 표의 [학생 수] 셀에 아래 수식을 입력하세요.

=COUNTIF($A$2:$A$15, D2)

🔍 수식 뜯어보기

• $A$2:$A$15: 검색할 범위입니다. (전체 선생님 목록)

• D2: 찾을 조건입니다. (현재 행의 선생님 이름)

• 해석: 전체 목록에서 '홍길동'이라는 이름이 몇 번 등장하는지 세어라! (즉, 등장 횟수 = 담당 학생 수)

3. 마무리: 드래그 한 번으로 끝내기

이제 첫 번째 줄(홍길동 선생님)의 수식이 완성되었습니다.

만들어진 셀을 선택하고 채우기 핸들을 아래로 쭉 드래그하면, 나머지 곽도영, 이재경, 강민승 선생님의 데이터도 자동으로 채워집니다.

(예시: 수식을 아래로 드래그하여 완성된 표 캡처)

💡 Dr. 데일리의 요약

1. 흩어진 텍스트를 조건에 맞춰 한 셀에 합칠 땐 TEXTJOIN + IF 조합을 쓴다.

2. 단순히 조건에 맞는 개수만 셀 때는 COUNTIF가 제일 빠르다.

3. 수식 입력 시 범위에는 꼭 절대참조($)를 걸어주는 것을 잊지 말자.

이제 야근 없이 데이터를 깔끔하게 정리해 보세요!

궁금한 점은 댓글로 남겨주시면 답변드리겠습니다. :)