셀이 자동으로 채워지면 얼마나 좋을까요? 대부분의 경우 우리는 그것을 좋아할 것입니다. 오늘 우리는 다른 셀의 값을 기반으로 Excel에서 셀을 자동으로 채우는 방법을 보여 드리겠습니다. 이 세션에서는 Excel 2019를 사용할 예정이므로 원하는 버전을 자유롭게 사용하세요.
먼저 오늘 예제의 기반이 되는 데이터세트에 대해 알아보겠습니다.
여기에 직원의 이름, ID, 주소, 각 부서 및 입사 날짜와 같은 정보가 포함된 테이블이 있습니다. 이 데이터를 사용하여 셀을 자동으로 채우는 방법을 알아보겠습니다.
이것은 더미 데이터가 있는 기본 데이터 세트입니다. 실제 시나리오에서는 훨씬 더 크고 복잡한 데이터 세트를 접할 수 있습니다.
연습 워크북
아래 링크에서 연습 워크북을 다운로드할 수 있습니다.
다른 셀을 기반으로 셀 자동 채우기
여기에서 직원 이름을 제공하면 직원의 정보를 자동으로 찾을 수 있도록 예를 설정했습니다.
여기에서는 원래 테이블과 분리된 정보 필드를 도입했습니다. 이름, 로버트를 설정했다고 가정해 보겠습니다. .
그런 다음 Robert의 세부정보를 가져와야 합니다. . 어떻게 할 수 있는지 알아보겠습니다.
1. VLOOKUP 기능 사용
잠시 동안 "자동 채우기"는 잊어버리고 기준과 일치하는 데이터를 검색하는 것에 대해 생각해 보십시오. 어떤 기능이 떠오르나요? 뻔한 VLOOKUP 그 중 하나입니다.
조회 수직으로 구성된 데이터를 찾습니다. 자세한 내용은 이 VLOOKUP 문서를 확인하세요.
이제 VLOOKUP 을 사용하여 수식을 작성할 것입니다. 셀에서 원하는 정확한 데이터를 가져오는 함수입니다.
직원의 id를 구하는 공식을 써봅시다
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,2,0),"")
VLOOKUP 내 함수에 이름(I4)을 삽입했습니다. lookup_value로 . 그런 다음 전체 테이블 범위를 lookup_array .
직원 ID 두 번째 열이므로 2를 column_num으로 설정했습니다. .
IFERROR를 사용했습니다. VLOOKUP을 마무리하는 함수 공식. 이렇게 하면 공식에서 발생하는 모든 오류가 제거됩니다(함수에 대해 알아보려면 IFERROR 문서 참조).
부서 이름을 파생하려면 수식을 수정해야 합니다.
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,3,0),"")
여기에서 column_num을 변경했습니다. 원래 테이블의 위치에 따라. 부서 는 세 번째 열이므로 3을 사용했습니다.
가입 날짜 및 주소, 공식은 다음과 같습니다.
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,4,0),"")
그리고
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,5,0),””)
직원에 대한 세부 정보를 찾았습니다. 이제 이름을 변경하면 셀이 자동으로 업데이트됩니다.
드롭다운 목록이 있는 VLOOKUP
이전에는 이름을 수동으로 제공했습니다. 때로는 시간이 많이 걸리고 혼란스러워 보일 수 있습니다.
이 문제를 해결하기 위해 직원 이름에 대한 드롭다운 목록을 만들 수 있습니다. 드롭다운 목록 작성에 대해 알아보려면 기사를 확인하세요.
데이터 검증 대화 상자에서 목록 선택 이름의 셀 참조를 삽입하십시오.
B4:B9 이름이 포함된 범위입니다.
이제 드롭다운 목록이 표시됩니다.
이제 더 효과적이고 빠르게 이름을 선택할 수 있습니다.
VLOOKUP을(를) 사용하면 다른 셀이 자동으로 채워집니다. .
2. INDEX – MATCH 기능 사용
VLOOKUP을 통해 수행한 작업 대안으로 할 수 있습니다. INDEX-MATCH 조합을 사용할 수 있습니다. 셀을 자동으로 채우려면
매치 행, 열 또는 테이블에서 조회 값의 위치를 찾습니다. 색인 범위의 지정된 위치에 있는 값을 반환합니다. 자세한 내용은 INDEX, MATCH 문서를 참조하십시오.
공식은 다음과 같습니다.
=IFERROR(INDEX($C$4:$C$9,MATCH($I$4,$B$4:$B$9,0)),"")
여기에서 수식은 INDEX 내에서 id 범위를 제공했기 때문에 id 번호를 파생합니다. 그리고 MATCH 함수는 테이블의 기준 값과 일치하는 행 번호를 제공합니다(B4:B9 ).
부서 파생 INDEX 의 범위를 변경하겠습니다. 공식은 다음과 같습니다.
=IFERROR(INDEX($D$4:$D$9,MATCH($I$4,$B$4:$B$9,0)),"")
부서는 D4 범위에 있습니다. D9까지 .
가입 날짜 공식 될 것입니다
=IFERROR(INDEX($E$4:$E$9,MATCH($I$4,$B$4:$B$9,0)),"")
그리고 주소
=IFERROR(INDEX($F$4:$F$9,MATCH($I$4,$B$4:$B$9,0)),"")
이제 명확하게 하기 위해 선택 항목을 지우고 이름 중 하나를 선택하겠습니다.
자동으로 채워지는 다른 셀을 찾을 수 있습니다.
3. HLOOKUP 기능 사용
데이터가 수평 방향인 경우 HLOOKUP 을 사용해야 합니다. 기능. 이 기능에 대해 알아보려면 HLOOKUP 문서를 방문하세요.
이름 필드는 드롭다운 목록에서 설정됩니다. 나머지 필드는 자동으로 채워집니다.
id를 유도하기 위해 다음 공식을 사용할 것입니다.
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,2,0),"")
작업은 VLOOKUP과 유사합니다. 공식. HLOOKUP 내 함수에서 이름을 lookup_value로 제공했습니다. lookup_array와 같은 테이블 . ID는 두 번째 행에 있으므로 row_num 정확히 일치하려면 2이고 0입니다.
이제 부서의 공식은 다음과 같습니다.
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,3,0),"")
부서 는 세 번째 행이므로 row_num 여기 3입니다.
입사일의 공식을 쓰자
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,4,0),"")
가입 날짜 는 4번째 행이므로 row_num 여기 4입니다. 그런 다음 주소의 행 번호를 5로 변경합니다.
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,5,0),"")
셀을 지우고 드롭다운 목록에서 이름을 선택합니다.
이름을 선택하면 다른 셀이 자동으로 채워지는 것을 볼 수 있습니다.
4. 행에 대한 INDEX-MATCH
INDEX MATCH를 사용할 수도 있습니다. 행에 대한 조합. 공식은 다음과 같습니다.
=IFERROR(INDEX($C$4:$H$4,MATCH($C$11,$C$3:$H$3,0)),"") 이것은 id를 도출하기 위한 것이므로 C4:H4를 사용했습니다. 색인 직원 ID인 함수 행.
행 범위를 변경하여 부서 찾기
=IFERROR(INDEX($C$5:$H$5,MATCH($C$11,$C$3:$H$3,0)),"")
마찬가지로 가입 날짜 및 주소의 행 번호를 변경합니다.
=IFERROR(INDEX($C$6:$H$6,MATCH($C$11,$C$3:$H$3,0)),"") 여기 C6:H6 가입 날짜 입니다. 행.
그리고 C7:H7 주소 입니다. 행이므로 주소를 유도하는 공식은 아래에 설명된 것과 같습니다.
=IFERROR(INDEX($C$7:$H$7,MATCH($C$11,$C$3:$H$3,0)),”)
결론
오늘은 그게 다야. 다른 셀을 기반으로 셀을 자동으로 채우는 몇 가지 방법을 나열했습니다. 이 정보가 도움이 되기를 바랍니다. 이해하기 어려운 점이 있으면 언제든지 의견을 말하십시오. 여기에서 놓친 다른 방법을 알려주십시오.
추가 자료
- Excel에서 자동 완성 수식을 사용하는 방법(6가지 방법)
- Excel의 다른 셀을 기반으로 하는 셀 자동 채우기(5가지 방법)
- Excel의 자동 번호 매기기(9가지 접근 방식)
- Excel에서 숫자를 자동으로 채우는 방법(12가지 방법)
- 수정:Excel 자동 완성이 작동하지 않음(7개 문제)
- Excel의 여러 시트에 순차적 날짜를 입력하는 방법