엑셀(MS Excel)을 사용하다 보면 목록 안에서 특정 기준이나 조건에 해당하는 값을 찾아 추출해야 하는 경우가 자주 있습니다. 예를 들어 각 작업(task)과 담당자 이름이 함께 정리된 작업 계획표가 있다고 가정해 보겠습니다. 이때 특정 담당자를 선택하면 그 사람이 맡은 모든 작업 이름을 행 단위로 나열하고 싶어질 것입니다. 이처럼 엑셀은 셀 값을 기준으로 목록을 채우는 다양한 방법을 제공합니다. 이 글에서는 셀 값에 따라 목록을 채우는 6가지 방법을 예제와 함께 자세히 살펴보겠습니다.
실습용 워크북 내려받기
아래에서 연습용 파일을 내려받아 직접 따라 하며 익혀보세요.
엑셀에서 셀 값 기준으로 목록을 채우는 6가지 방법
1. 셀 값을 기반으로 목록 자동 채우기(AutoFill)
프로젝트별 참여 인력 명단을 만들어 보겠습니다. 각 프로젝트에는 Project_Number_Name_Serial 형식으로 인력 이름이 배정되어 있으며, 우리의 과제는 프로젝트명을 기준으로 해당 프로젝트의 모든 인력 이름을 찾아내는 것입니다.
1단계: D17 셀에 아래 수식을 입력한 후 Enter 키를 누릅니다.
=IFERROR(INDEX($B$3:$D$11,ROW(B2:D11),MATCH($C$16,$B$3:$D$3,0)),"")
수식 설명
- MATCH($C$16,$B$3:$D$3,0): 입력된 프로젝트 이름을 데이터 범위와 비교하며, 정확히 일치하는 값만 찾아냅니다.
- ROW(B2:D11): 데이터 범위의 행 번호를 반환합니다.
- INDEX($B$3:$D$11, ROW(B2:D11), MATCH($C$16,$B$3:$D$3,0)): 일치하는 프로젝트의 인력 이름을 찾습니다. 데이터를 찾지 못하면 #N/A 오류를 반환합니다.
- IFERROR: 위 과정에서 발생할 수 있는 모든 오류를 처리해 빈 값("")으로 표시합니다.
2단계: 드롭다운 목록에서 원하는 프로젝트 이름을 선택합니다.
3단계: 해당 프로젝트의 모든 인력 이름이 자동으로 표시됩니다.
2. 수식으로 특정 셀 값에 맞는 행 채우기
이번에는 다른 접근 방식으로 인력 이름을 검색해 보겠습니다. 하나의 인력이 여러 프로젝트에 동시에 배정될 수 있는 데이터 집합에서, 인력 이름을 기준으로 프로젝트명을 찾아내는 것이 목표입니다. 기본 데이터 집합은 다음과 같습니다.
1단계: G6 셀에 아래 수식을 입력한 후 Enter 키를 누릅니다.
=FILTER(B4:B16, G5=C4:C16)
수식 설명
- FILTER 함수에서 B4:B16은 데이터를 추출해 올 범위입니다.
- G5 셀에 입력한 이름이 C4:C16 범위의 이름들과 비교되며, 일치하는 행만 결과로 반환됩니다.
- FILTER 함수는 Microsoft 365 및 Excel 2021 이상 버전에서만 사용할 수 있습니다. 자세한 내용은 공식 문서를 참고하세요.
2단계: G5 셀에 아무 이름이나 입력하고 Enter 키를 누릅니다.
3. 첫 번째 드롭다운 변경 잠그기
서로 다른 음식 항목 목록이 여러 개 있다고 가정해 보겠습니다. 각 목록은 서로 구분되며, 특정 음식 항목은 반드시 올바른 목록에 속해야 합니다.
다른 워크시트에서는 음식 유형에 맞는 항목을 선택하게 됩니다.
여기서 핵심은 B열에서 음식 유형을 선택하면 C열(항목)에는 해당 유형에 속한 항목만 선택 가능하도록 만드는 것입니다.
1단계: 음식 항목 셀들을 선택한 뒤 데이터 유효성 검사(Data Validation)를 엽니다.
2단계: 원본(Source)에 아래 수식을 입력합니다.
=IF(B4="",Foods, INDIRECT("FakeRange"))
3단계: 경고 창이 나타나면 예(Yes) 버튼을 클릭합니다.
4단계: 이제 음식 유형을 먼저 선택한 후, 해당 유형에 맞는 항목을 선택합니다.
5단계: 음식 유형과 항목을 입력한 후에는 더 이상 항목을 임의로 변경할 수 없습니다. 덕분에 잘못된 조합이 입력될 가능성 자체가 사라집니다.
4. 조건에 따라 고유한 목록 만들기
중복 없는 고유값 목록을 추출할 때도 엑셀은 다양한 방법을 제공합니다. 2번 방법과 같은 데이터 집합에 중복값이 포함되어 있다고 가정하고, 수식만으로 고유 목록을 만들어 보겠습니다.
1단계: G6 셀에 아래 수식을 입력합니다.
=UNIQUE(FILTER(B4:B22,C4:C22=G5))
수식 설명
- FILTER(B4:B22, C4:C22=G5): 2번 방법과 동일하게 데이터 집합에서 일치하는 모든 이름을 추출합니다. 중복된 일치 항목도 함께 반환합니다.
- UNIQUE: FILTER 함수가 반환한 결과에서 중복값을 제거합니다. 함수에 대한 자세한 설명은 관련 문서를 참고하세요.
2단계: G5 셀에 원하는 이름을 입력하고 Enter 키를 누릅니다.
5. 배열 수식으로 한 열 기준을 충족하는 모든 행 추출하기
ID, 브랜드, 모델, 단가로 구성된 제품 데이터 집합이 있다고 가정해 보겠습니다. 이번 과제는 H5와 H7 셀에 입력한 브랜드 이름과 일치하는 모든 행을 찾아내는 것입니다.
1단계: B19 셀에 아래 수식을 입력한 후 Ctrl + Shift + Enter 키를 눌러 배열 수식으로 확정하고, 수식을 표 전체에 복사합니다.
=INDEX($B$4:$E$15, SMALL(IF(COUNTIF($H$5:$H$7,$C$4:$C$15), MATCH(ROW($B$4:$E$15), ROW($B$4:$E$15)), ""), ROWS(B19:$B$19)), COLUMNS($B$3:B3))
2단계: H5와 H7 셀에 브랜드 이름을 입력하고 Enter 키를 누릅니다.
6. 엑셀에서 종속 드롭다운 목록 만들기
엑셀에서 드롭다운 목록은 데이터 입력 양식이나 대시보드를 만들 때 매우 유용한 기능입니다. 셀 안에 항목 목록이 드롭다운 형태로 표시되어 사용자가 목록에서 값을 선택할 수 있습니다. 이름, 제품, 지역처럼 반복적으로 입력해야 하는 값들이 있을 때 특히 효과적입니다.
세 개의 서로 다른 음식 항목 목록이 있다고 가정하고, 이 목록들을 활용해 종속 드롭다운 목록을 만들어 보겠습니다.
1단계: 데이터 유효성 검사(Data Validation) 옵션을 엽니다.
2단계: 데이터 유효성 검사 창에서 제한 대상(Allow)을 목록(List)으로 설정하고 원본(Source)을 아래와 같이 선택합니다.
3단계: 음식 유형(Food Types) 열에 드롭다운 목록이 생성됩니다.
4단계: 전체 데이터 집합을 선택한 뒤 수식(Formulas) 탭에서 선택 영역으로부터 만들기(Create from Selection)를 클릭합니다.
5단계: 팝업 창이 나타나면 첫 행(Top row)을 체크하고 확인(Ok) 버튼을 누릅니다.
6단계: D14 셀로 이동해 데이터 유효성 검사를 엽니다. 제한 대상이 목록으로 설정되어 있는지 확인한 후, 원본에 아래 수식을 입력하고 확인 버튼을 누릅니다.
=INDIRECT(B14)
7단계: 경고 창이 나타나면 예(Yes) 버튼을 누릅니다.
8단계: 첫 번째 드롭다운 목록에서 음식 유형을 선택하면, 두 번째 드롭다운 목록에는 해당 유형에 속한 항목들만 표시됩니다.
9단계: 최종 완성된 결과는 다음과 같습니다.
꼭 알아두어야 할 사항
| 주요 오류 | 발생 상황 |
|---|---|
| FILTER 함수의 #VALUE! 오류 | include 인수의 크기가 array 인수와 호환되지 않으면 FILTER 함수는 #VALUE! 오류를 반환합니다. |
| #N/A 오류 | 수식이 데이터 집합에서 아무것도 찾지 못하면 이 오류가 반환됩니다. IFERROR 함수를 사용해 이 오류를 처리해야 합니다. |
| 목록 이름 지정 규칙 | 목록 이름에는 공백을 사용할 수 없습니다. 이름에 공백이 필요하면 밑줄(_)로 대체하세요. |
결론
지금까지 엑셀에서 셀 값을 기준으로 목록을 채우는 여러 가지 방법을 알아보았습니다. 각 방법을 실제 예제와 함께 설명했지만, 이외에도 다양한 응용이 가능합니다. 사용된 핵심 함수들의 기본 원리도 함께 다루었으니 상황에 맞게 활용해 보세요. 이 외에 다른 방법을 알고 계시다면 얼마든지 의견을 나눠 주시기 바랍니다.
함께 보면 좋은 글
- 한 엑셀 워크시트에서 다른 워크시트로 자동으로 데이터 전송하기
- 엑셀에서 다른 워크시트로부터 자동 채우기 하는 방법
- 엑셀에서 데이터가 있는 마지막 행까지 한 번에 채우기(3가지 빠른 방법)