엑셀 목록 기능 사용법 — 한 셀에 여러 값 넣기 (Ctrl+J, HAS 함수)

쉼표로 이어 쓴 여러 값을 엑셀이 '값 여러 개짜리 목록'으로 인식하게 만드는 신기능을 정리합니다. 목록을 만드는 방법(리본 메뉴, Ctrl+J), 목록으로 계산하고 필터하는 방법, 새 함수 HAS·HASANY·HASALL·FLATTEN의 쓰임까지 한 번에 확인하실 수 있습니다.

이번 영상에서 습득할 수 있는 주요 내용

1. 지금까지의 방식은 어땠을까?

예제의 [기존방식 비교] 시트에는 직원별 보유 스킬이 쉼표로 이어져 있습니다. 김민수는 '엑셀, 파워쿼리, VBA' 세 가지를 갖고 있지만, 엑셀은 이것을 그냥 글자 한 덩어리로 봅니다. 그래서 스킬 개수를 세려면 이런 수식을 써야 했습니다.

C2: =LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

전체 글자 수에서 쉼표를 지운 글자 수를 빼면 쉼표 개수가 나오고, 거기에 1을 더하는 방식입니다. VBA를 할 줄 아는 사람인지 확인하려면 글자를 검색하는 SEARCH 함수가 필요했습니다.

D2: =IF(ISNUMBER(SEARCH("VBA",B2)),"가능","")

원하는 결과는 구해지지만 수식이 길고 복잡합니다. 셀 안에 값이 여러 개 있다는 사실을 엑셀이 몰랐기 때문에 돌아가야 했던 우회로입니다. 이제 엑셀이 그것을 알게 되었습니다.

2. 목록 만들기

[예제데이터] 시트에는 '보유 스킬'과 '최근 3회 점수' 열이 있습니다. 먼저 보유 스킬 열을 목록으로 바꿔 보겠습니다.

언뜻 보기에는 달라진 게 없지만, 엑셀은 이제 이 셀을 글자 한 덩어리가 아니라 값 세 개짜리 목록으로 인식합니다. 내용을 고치려면 셀을 더블클릭하거나 F2를 누르거나 수식 입력줄을 클릭하시면 되고, 항목을 추가하거나 지우는 것도 평소처럼 하시면 됩니다.

리본 메뉴 대신 범위를 선택하고 Ctrl + J를 눌러도 범위가 목록으로 바뀝니다. 목록 상태에서 Ctrl + J를 한 번 더 누르면 쉼표로 이어진 원래 텍스트 상태로 돌아옵니다. 언제든 되돌릴 수 있으니 부담 없이 눌러 보셔도 됩니다.

3. 목록으로 계산하기

계산 작업을 해보기 위해, '최근 3회 점수' 열(C2:C6)도 목록으로 바꿉니다. 김민수의 점수는 85, 90, 78 세 개가 한 셀에 들어 있습니다. 평균은 이렇게 구합니다.

E2: =AVERAGE(C2)

AVERAGE 함수에 셀 하나만 지정했습니다. 예전 같으면 말이 안 되는 수식이지만 목록이기 때문에 가능하고, 셀 안에 든 세 점수의 평균을 구해 줍니다. 각 직원이 보유한 스킬 개수를 구하는 것도 COUNTA 함수를 쓰면 간단하게 해결됩니다.

D2: =COUNTA(B2)

이전에는 LEN과 SUBSTITUTE를 여러 번 중첩했던 긴 수식이 이렇게 짧아졌습니다.

4. 항목별로 필터하기

[데이터] 탭에서 자동 필터를 적용한 뒤 '보유 스킬' 열의 필터 버튼을 눌러 보세요. '엑셀, 파워쿼리, VBA'라는 덩어리가 아니라 엑셀, 파워쿼리, VBA, 파워포인트처럼 항목별로 나뉘어 나타납니다. 여기서 VBA만 체크하면 VBA를 가진 사람만 바로 걸러집니다.

텍스트일 때는 [텍스트 필터] > [포함] 조건을 따로 만들어야 했지만, 이제는 항목을 고르기만 하면 됩니다.

5. 목록 전용 새 함수: HAS, HASANY, HASALL

목록 기능과 함께 새 함수가 생겼습니다. 영어로 '가지고 있다'는 뜻의 HAS에서 파생된 이름들이라, 이름이 곧 뜻입니다.

함수	의미	TRUE가 되는 경우	비유
HAS	이 값이 있나요?	찾는 값이 목록에 있을 때	-
HASANY	하나라도 있나요?	찾는 값 중 하나라도 있을 때	OR
HASALL	전부 있나요?	찾는 값이 모두 있을 때	AND

① HAS — 이 값이 있나요?

F2: =HAS(B2,"VBA")

첫 번째 인수는 목록이 들어 있는 셀, 두 번째 인수는 찾을 값입니다. VBA 스킬 보유자만 TRUE로 나옵니다.

② HASANY — 하나라도 있나요?

G2: =HASANY(B2,{"VBA","파이썬"})

VBA나 파이썬 중 하나라도 할 줄 아는 사람을 찾는 수식입니다. 찾을 값들을 중괄호 안에 큰따옴표와 쉼표로 구분해 넣는 형식을 눈여겨보세요.

③ HASALL — 전부 있나요?

H2: =HASALL(B2,{"엑셀","VBA"})

엑셀과 VBA를 둘 다 할 줄 아는 사람만 TRUE가 됩니다. 참고로 HAS 계열 함수는 대소문자를 구분하지 않습니다.

6. FLATTEN — 목록 펼치기

FLATTEN은 '평평하게 만들다'라는 뜻 그대로, 한 셀에 뭉쳐 있던 목록을 셀 여러 개로 쫙 펼쳐 주는 함수입니다. 우리 회사에 어떤 스킬들이 있는지 보고 싶다면 스킬 범위를 FLATTEN에 넣으면 됩니다.

I2: =FLATTEN(B2:B6)

동적 배열 함수라서 한 셀에만 입력하면 모든 직원의 스킬이 아래로 펼쳐집니다. 그런데 같은 값이 여러 번 나오므로, 중복을 없애 주는 UNIQUE 함수를 겹쳐 보겠습니다.

I2: =UNIQUE(FLATTEN(B2:B6))

엑셀, 파워쿼리, VBA 등 여섯 가지 스킬이 중복 없이 한 번씩만 나옵니다. 쉼표로 이어진 글자였다면 쪼개고, 합치고, 중복을 지우는 작업이 만만치 않았을 텐데, 수식 하나로 끝났습니다.

여기까지 따라오셨다면 '목록'의 기본기는 모두 익히신 겁니다. 이것만으로도 관련 업무가 많이 간소화됩니다. 그런데 이런 생각이 드시지 않나요? 스킬별로 몇 명이 있는지, 누가 어떤 스킬을 가졌는지 한눈에 보고 싶다는 생각 말입니다.

7. 목록 100% 활용하는 법 [멤버십 회원용]

직원 다섯 명과 스킬 여섯 가지를 잘 정리해 놓은 '직원별 스킬 보유 현황표'가 있으면 훨씬 보기 좋겠죠? 오늘 배운 함수 몇 개에 엑셀 기본 함수 몇 가지를 조합하면, 이 현황표를 훨씬 쉽게 완성할 수 있습니다.

이 방법은 직원 스킬뿐 아니라 설문조사에서 여러 개를 고른 응답, 수강생이 신청한 여러 과목처럼 쉼표로 뭉쳐 있는 데이터라면 어떤 것이든 동일한 방법을 적용할 수 있습니다. 쉼표 텍스트를 쪼개고 정리하던 작업이 엄청나게 줄어듭니다.

MVP TIP — 목록 기능을 쓸 때 알아야 할 사항

1. 먼저 목록으로 바꿔 보고, 안 맞으면 Ctrl+J로 되돌리세요

Ctrl+J는 목록과 쉼표 텍스트를 오가는 토글입니다. 내 데이터가 목록으로 잘 인식되는지 부담 없이 시험해 보고, 아니다 싶으면 한 번 더 눌러 원래 상태로 돌아오시면 됩니다.

2. '찾기'는 HAS 계열로, '펼치기'는 FLATTEN으로 구분하세요

특정 값이 들어 있는지만 확인하면 된다면 HAS, HASANY, HASALL을 쓰고, 전체 항목을 목록으로 뽑아 봐야 한다면 FLATTEN(필요하면 UNIQUE까지)을 쓰면 됩니다. 목적에 따라 함수를 고르는 것이 핵심입니다.

3. 공유할 파일이라면 상대방의 엑셀 버전을 먼저 확인하세요

'목록'은 아직 미리 보기 기능입니다. 엑셀 2021이나 2024 등 이 기능이 없는 버전을 쓰는 분께 목록이 들어간 파일을 보낼 때는, 상대방 엑셀에서 어떻게 보이는지 미리 확인해 보시길 권합니다.

자주 묻는 질문 (FAQ)

마치며

한 셀에 여러 값을 담는 신기능 '목록'의 만드는 방법, 계산과 필터, 그리고 HAS·HASANY·HASALL·FLATTEN 함수까지 살펴보았습니다. 아직은 모든 사람들이 사용할 수는 없지만, 쉼표로 연결된 데이터를 쪼개고 정리하느라 고생하셨다면 앞으로의 변화가 꽤 반가우실 겁니다.

이 기능을 실무에서 활용하는 구체적인 방법, 즉 직원별 스킬 보유 현황표를 만드는 과정이 궁금하시다면 멤버십 회원용 풀 버전에서 이어서 확인해보세요.