MS Excel에서는 때때로 목록의 일부 기준이나 조건에 따라 값을 결정하거나 찾아야 할 수 있습니다. 각 작업을 감독하는 해당 사람의 이름이 포함된 작업 계획이 있다고 가정합니다. 이제 주어진 사람을 기준으로 모든 작업의 이름을 행에 나열해야 합니다. 이러한 방식으로 Excel은 셀 값을 기반으로 목록을 채우는 다양한 방법을 제공합니다. 이 기사에서는 Excel에서 셀 값을 기반으로 목록을 채우는 방법을 살펴보겠습니다.
연습 워크북 다운로드
Excel에서 셀 값을 기반으로 목록을 채우는 6가지 방법
1. 셀 값을 기반으로 하는 목록 자동 완성
프로젝트 작업자의 이름으로 프로젝트 목록을 작성해 보겠습니다. 각 프로젝트에서 작업자의 이름은 "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) 이 부분은 입력한 프로젝트 이름과 데이터세트가 일치하며 정확히 일치하는 것으로만 간주됩니다.
- 행(B2:D11) 데이터세트의 행 수를 계산하고 있습니다.
- INDEX($B$3:$D$11, ROW(B2:D11), MATCH($C$16,$B$3:$D$3,0)) 수식의 이 부분은 일치하는 프로젝트 작업자 이름을 찾는 것입니다. 주어진 데이터 세트에서 데이터를 찾을 수 없으면 #NA 오류가 발생합니다.
- 마지막으로, IFERROR 모든 종류의 오류를 처리하는 것입니다.
2단계 :이제 드롭다운 목록에서 프로젝트 이름을 선택합니다.
3단계: 모든 작업자의 이름이 표시됩니다.
2. 수식으로 특정 셀 값을 기반으로 행 채우기
다른 접근 방식으로 작업자의 이름을 검색하는 방법을 살펴보겠습니다. 데이터 세트에서 한 사람이 여러 프로젝트에 할당될 수 있습니다. 이제 우리의 임무는 작업자의 이름을 사용하여 프로젝트 이름을 찾는 것입니다. 여기서 주요 데이터세트는 다음과 같습니다.
1단계: G6 셀에 다음 수식을 입력합니다. Enter 키를 누릅니다. 키
=FILTER(B4:B16, G5=C4:C16) 공식 설명
- 필터 에서 함수, B4:B16 데이터를 추출할 범위입니다.
- G5 이 셀에서 입력 이름을 제공하고 이름 범위 C4:C16와 비교합니다.
- FILTER 기능에 대해 자세히 알아보려면 이 링크를 확인하세요.
2단계: 이제 G5 셀에 이름을 입력하십시오. Enter 키를 누릅니다.
3. 첫 번째 드롭다운에서 변경 차단
다양한 식품 목록이 여러 개 있다고 가정해 보겠습니다. 각 목록은 다른 목록과 다르며 특정 식품 항목은 유효한 목록에 있어야 합니다.
다른 워크시트에서는 유형에 따라 식품 품목을 선택합니다.
우리의 관심사는 B열에서 식품 유형을 선택하면 C열(항목)에서 해당 식품 유형 또는 목록 아래에 있는 항목만 사용할 수 있다는 것입니다.
1단계: 식품 항목 셀을 선택하고 데이터 유효성 검사를 엽니다.
2단계: 그런 다음 소스에 다음 수식을 작성하십시오.
=IF(B4="",Foods, INDIRECT("FakeRange"))
3단계: 경고가 나타납니다. 예 를 클릭하십시오. 버튼
4단계: 이제 식품 유형을 선택한 다음 항목을 선택하십시오.
5단계: 식품 유형 및 품목을 입력한 후에는 식품 품목을 변경할 수 없습니다. 따라서 매치메이킹에 오류가 발생할 가능성이 없습니다.
4. 기준에 따라 Excel에서 고유 목록 만들기
고유 값 목록을 찾는 측면에서 Excel은 다양한 방법을 제공합니다. 중복 값이 있는 방법 2와 동일한 데이터 세트를 고려해 보겠습니다. 이제 우리의 목표는 공식을 사용하여 고유한 목록을 찾는 것입니다.
1단계: G6 셀에 다음 수식을 입력합니다.
=UNIQUE(FILTER(B4:B22,C4:C22=G5))
- 필터(B4:B22, C4:C22=G5) 이 함수는 방법 2와 동일합니다. 데이터 세트에서 일치하는 모든 이름을 추출합니다. 중복 일치 항목이 있는 경우 FILTER 함수도 계산합니다.
- FILTER 에서 반환된 중복 값을 제거하려면 기능에서 UNIQUE 를 사용했습니다. 기능. 이 함수는 일치하는 데이터에서 모든 중복 값을 제거합니다. 기능에 대해 자세히 알아보려면 이 링크를 방문하세요.
2단계: 이제 이름을 입력하세요. 셀 G5 Enter 키를 누릅니다.
5. 배열 수식을 사용하여 한 열의 기준을 충족하는 목록의 모든 행 추출
ID가 있는 제품 데이터세트가 있습니다. , 브랜드 , 모델 및 단가 . 이제 우리의 임무는 브랜드 이름이 입력한 브랜드 이름과 일치하는 행을 찾는 것입니다. 셀 H5 및 H7 .
1단계 :B19 셀에 다음 수식을 입력합니다. CTRL 을 누르십시오. + 쉬프트 + 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. Excel에서 종속 드롭다운 목록 만들기
MS Excel에서 드롭다운 목록은 데이터 입력 양식이나 Excel 대시보드를 수행할 때 유용한 기능입니다.
항목 목록을 셀에 드롭다운으로 표시하며 사용자는 드롭다운에서 선택할 수 있습니다. 이는 셀 세트에 입력해야 하는 이름, 제품 또는 지역 목록이 있는 경우 유용할 수 있습니다.
세 가지 다른 식품 항목 목록이 있다고 가정하고 이제 해당 목록을 사용하여 Excel에서 종속 드롭다운 목록을 만듭니다.
1단계: 데이터 검증 열기 옵션
2단계: 데이터 유효성 검사에서 창에서 허용 을 선택하십시오. 목록 으로 소스 선택 아래와 같이
3단계: 드롭다운 을 찾을 수 있습니다. 식품 유형 열에 나열
4단계: 이제 모든 데이터세트를 선택하고 선택 항목에서 만들기를 클릭합니다. 수식 아래의 옵션 탭
5단계: 팝업이 나타납니다. 맨 위 행을 클릭합니다. 그런 다음 확인 을 누릅니다. 버튼
6단계: 이제 D14 셀로 이동합니다. 데이터 유효성 검사를 엽니다. . 허용 목록 으로 설정됨 소스에 다음 수식을 작성합니다. . 마지막으로 확인 을 누릅니다. 버튼
=INDIRECT(B14)
7단계: 경고가 나타납니다. 예 를 누르십시오. 버튼
8단계: 이제 첫 번째 드롭다운 목록에서 식품 유형을 선택하고 다른 드롭다운 목록에서 관련 항목 목록을 찾습니다.
8단계: 최종 출력은 다음과 같습니다.
기억해야 할 사항
| 일반적인 오류 | 표시될 때 |
|---|---|
| #VALUE! FILTER 오류 | 포함 인수에는 배열 인수와 호환되는 크기가 있어야 합니다. 그렇지 않으면 FILTER가 #VALUE를 반환합니다! |
| #NA! 오류 | 수식이 데이터세트에서 데이터를 찾지 못하면 이 오류가 반환됩니다. IFERROR 의 도움이 필요합니다. 이 오류를 처리하는 함수입니다. |
| 목록의 이름 지정 | 목록 이름을 지정할 때 공백을 사용할 수 없습니다. 이름에 공백이 있으면 "_ ". |
결론
다음은 Excel에서 셀 값을 기반으로 목록을 채우는 몇 가지 방법입니다. 나는 각각의 예와 함께 모든 방법을 보여 주었지만 다른 많은 반복이 있을 수 있습니다. 또한 사용되는 기능의 기본 사항에 대해 논의했습니다. 이를 달성할 수 있는 다른 방법이 있다면 언제든지 공유해 주십시오.
추가 자료
- 한 Excel 워크시트에서 다른 Excel 워크시트로 자동으로 데이터 전송
- Excel의 다른 워크시트에서 자동으로 채우는 방법
- Excel에서 데이터로 마지막 행까지 채우는 방법(3가지 빠른 방법)