여러 번, 하나의 셀만 데이터로 채워지고 그 아래의 셀은 다음 값이 나올 때까지 비어 있는 Excel 데이터 세트를 만날 수 있습니다.
아래 그림과 같이:
이 형식이 어떤 사람들에게는 효과가 있지만 이러한 종류의 데이터의 문제는 피벗 테이블을 생성하거나 계산에 사용할 수 없다는 것입니다.
쉽게 고칠 수 있습니다!
이 튜토리얼에서는Excel에서 다음 채우기 값까지 빠르게 셀 채우기.
간단한 이동 특수 대화 기술, VBA 또는 파워 쿼리를 사용하여 이 작업을 쉽게 수행할 수 있습니다.
시작하겠습니다!
내용
방법 1 – Go To Special + 수식을 사용하여 채우기
아래와 같은 데이터세트가 있고 A열과 B열에 데이터를 채우고 싶다고 가정합니다.
B 열에서 목표는 그 아래의 마지막 빈 셀까지 "프린터"를 채우고 "스캐너"가 시작될 때 셀이 비어 있을 때까지 아래 셀의 "스캐너"를 채우는 것입니다.
아래 단계는 특수로 이동하여 모든 빈 셀을 선택하고 간단한 수식으로 채우십시오.
- 채울 데이터 선택(이 예에서는 A1:D21)
- "홈" 탭으로 이동
- 편집 그룹에서 찾기 및 선택 아이콘을 클릭합니다(드롭다운 메뉴에 더 많은 옵션이 표시됨)
- "이동"옵션을 클릭하십시오
- 열리는 이동 대화 상자에서 특수 버튼을 클릭합니다.그러면 "특별 항목으로 이동" 대화 상자가 열립니다.
- 특별 항목으로 이동 대화 상자에서 공백을 클릭하십시오.
- 확인을 클릭하십시오
위의 단계는 데이터 세트의 모든 빈 셀을 선택합니다(아래 참조).
선택한 빈 셀 중 하나의 셀이 나머지 선택 영역보다 밝은 것을 알 수 있습니다.이 셀은 수식을 입력하려는 활성 셀입니다.
우리의 방법은 어떤 경우에도 작동하므로 선택 항목에서 셀의 위치에 대해 걱정하지 마십시오.
이제 선택한 빈 셀에 데이터를 채우는 단계는 다음과 같습니다.
- 키보드에서 등호(=) 키를 클릭합니다.활성 셀에 등호가 삽입됩니다.
- 위쪽 화살표 키를 누릅니다.이렇게 하면 활성 셀 위에 셀의 셀 참조가 삽입됩니다.예를 들어 우리의 경우 B3는 활성 셀이고 이 두 단계를 수행하면 셀에 =B2가 입력됩니다.
- Ctrl 키를 누른 상태에서 Enter 키를 누릅니다.
위의 단계는 위의 값이 있는 모든 빈 셀을 자동으로 삽입합니다.
너무 많은 단계처럼 보일 수 있지만 일단 익숙해지면 Excel에서 몇 초 만에 데이터를 빠르게 채울 수 있습니다.
이제 이 방법을 사용할 때 알아야 할 두 가지 사항이 더 있습니다.
수식을 값으로 변환
첫 번째는 수식을 값으로 변환하는 것입니다(따라서 정적 값이 있고 나중에 데이터를 변경할 때 엉망이 되지 않도록).
날짜 형식 변경
데이터에 날짜를 사용하는 경우(예제 데이터에서 사용한 것처럼) 날짜 열에 채워진 값이 날짜가 아니라 숫자라는 것을 알 수 있습니다.
날짜 열의 출력이 원하는 날짜 형식이면 문제가 없는 것이며 다른 작업을 수행할 필요가 없습니다.
그러나 해당 날짜가 올바른 형식이 아니면 미세 조정이 필요합니다.올바른 값이 있는 동안 셀에 날짜로 표시되도록 형식을 변경하기만 하면 됩니다.
이를 수행하는 단계는 다음과 같습니다.
- 날짜가 포함된 열 선택
- 홈 탭을 클릭합니다.
- 숫자 그룹에서 형식 드롭다운 메뉴를 클릭하고 날짜 형식을 선택합니다.
이 패딩을 수시로 해야 하는 경우 이 이동 특수 및 수식 기법을 권장 합니다.
몇 가지 단계가 있지만 간단하고 데이터 세트의 결과를 제공합니다.
그러나 이 작업을 자주 수행해야 하는 경우 다음에 설명하는 VBA 및 Power Query 방법을 살펴보는 것이 좋습니다.
방법 2 - 간단한 VBA 코드로 작성
매우 간단한 VBA 매크로 코드를 사용하여 마지막 빈 값까지 Excel의 셀을 빠르게 채울 수 있습니다.
파일에 VBA 코드를 한 번만 추가하면 동일한 통합 문서 또는 시스템의 여러 통합 문서에서 해당 코드를 여러 번 쉽게 재사용할 수 있습니다.
다음은 선택 항목의 각 셀을 반복하고 빈 셀을 채우는 VBA 코드입니다.
Sub FillDown() In Selection If cell = "" then cell.FillDown End If Next End Sub
위의 코드는 For 루프를 사용하여 선택 영역의 각 셀을 반복합니다.
For 루프에서 If-Then 조건을 사용하여 셀이 비어 있는지 확인합니다.
셀이 비어 있으면 위 셀의 값으로 채워지고 그렇지 않은 경우 for 루프는 셀을 무시하고 다음 셀로 이동합니다.
이제 VBA 코드가 있으므로 Excel에서 이 코드를 넣을 위치를 보여 드리겠습니다.
- 채울 데이터 선택
- 개발자 탭을 클릭하십시오
- Visual Basic 아이콘을 클릭합니다(또는 바로 가기 키 Alt+F11을 사용할 수 있음).그러면 Excel에서 Visual Basic 편집기가 열립니다.
- 왼쪽의 Visual Basic 편집기에는 프로젝트 탐색기가 있습니다.보이지 않으면 메뉴에서 보기 옵션을 클릭한 다음 프로젝트 탐색기를 클릭합니다.
- 여러 Excel 통합 문서가 열려 있는 경우 프로젝트 탐색기에 모든 통합 문서 이름이 표시됩니다.데이터가 있는 통합 문서의 이름을 찾습니다.
- 통합 문서에서 개체를 마우스 오른쪽 단추로 클릭하고 삽입으로 이동한 다음 모듈을 클릭합니다.이 통합 문서에 대한 새 모듈이 삽입됩니다.
- 위 단계에서 방금 삽입한 "모듈"을 두 번 클릭합니다.해당 모듈의 코드 창을 엽니다.
- VBA 코드를 복사하여 코드 창에 붙여넣습니다.
- 코드의 아무 곳에나 커서를 놓고 도구 모음의 녹색 버튼을 클릭하거나 키보드 단축키 F5를 사용하여 매크로 코드를 실행합니다.
위의 단계는 VBA 코드를 실행하고 데이터가 채워집니다.
나중에 이 VBA 코드를 다시 사용하려면 이 파일을 매크로 사용 Excel 통합 문서(.XLSM 확장자 포함)로 저장해야 합니다.
당신이 할 수 있는 또 다른 일은 이 매크로를 빠른 실행 도구 모음에 추가하는 것입니다. 이 도구 모음은 항상 표시되며 단 한 번의 클릭으로 이 매크로에 액세스할 수 있습니다(백엔드에 코드가 있는 통합 문서에서).
따라서 다음에 입력해야 할 데이터가 있을 때 선택하고 빠른 실행 도구 모음에서 매크로 버튼을 클릭하면 됩니다.
이 매크로를 개인용 매크로 통합 문서에 추가한 다음 시스템의 모든 통합 문서에서 사용할 수도 있습니다.
개인용 매크로 통합 문서와 여기에 코드를 추가하는 방법에 대한 자세한 내용을 보려면 여기를 클릭하십시오.
방법 3 - 파워 쿼리를 사용하여 작성
파워 쿼리클릭 한 번으로 입력할 수 있는 기능이 내장되어 있습니다.
여전히 파워 쿼리를 사용하여 데이터를 변환하거나 여러 워크시트 또는 여러 통합 문서의 데이터를 결합하는 경우 권장됩니다.
기존 워크플로의 일부로 파워 쿼리의 채우기 옵션을 사용하여 빈 셀을 빠르게 채울 수 있습니다.
파워 쿼리를 사용하려면 데이터를 Excel 테이블 형식으로 사용하는 것이 좋습니다.데이터를 Excel 테이블로 변환할 수 없는 경우 데이터에 대해 명명된 범위를 만든 다음 파워 쿼리에서 해당 명명된 범위를 사용해야 합니다.
아래에는 Excel 테이블로 변환된 데이터 세트가 있습니다.데이터 세트를 선택하고 삽입 탭으로 이동한 다음 테이블 아이콘을 클릭하면 됩니다.
파워 쿼리를 사용하여 다음 값까지 데이터를 채우는 단계는 다음과 같습니다.
- 데이터세트에서 아무 셀이나 선택
- 데이터 탭을 클릭합니다.
- 데이터 가져오기 및 변환 그룹에서 워크시트에서를 클릭합니다.그러면 파워 쿼리 편집기가 열립니다.빈 셀은 파워 쿼리에서 "null" 값을 표시합니다.
- 파워 쿼리 편집기에서 채우려는 열을 선택합니다.이렇게 하려면 Ctrl 키를 누른 상태에서 선택하려는 열의 머리글을 클릭합니다.이 예에서는 날짜 및 제품 열이 됩니다.
- 선택한 제목을 마우스 오른쪽 버튼으로 클릭
- 채우기 옵션으로 이동하여 아래쪽을 클릭합니다.이것은 모든 빈 셀을 데이터로 채울 것입니다
- 파일 탭을 클릭한 다음 닫기 및 로드를 클릭합니다.통합 문서에 결과 테이블이 있는 새 워크시트가 삽입됩니다.
이 접근 방식이 너무 과한 것처럼 들릴 수 있지만 파워 쿼리를 사용할 때의 가장 큰 이점 중 하나는 원본 데이터가 변경될 때 결과 데이터를 빠르게 업데이트할 수 있다는 것입니다.
예를 들어 원본 데이터에 더 많은 레코드를 추가하는 경우 위의 모든 작업을 다시 수행할 필요가 없습니다.결과 테이블을 마우스 오른쪽 버튼으로 클릭하고 새로 고침을 클릭하면 됩니다.
파워 쿼리 방법은 잘 작동하지만 워크플로에서 이미 파워 쿼리를 사용하고 있을 때 사용하는 것이 가장 좋습니다.
채울 빈 셀이 있는 데이터 세트가 하나만 있는 경우 이동 특수 방법 또는 VBA 방법(위에 설명됨)을 사용하는 것이 더 편리합니다.
따라서 이 세 가지 간단한 방법을 사용하여Excel에서 다음 값까지 빈 셀 채우기.
이 튜토리얼이 유용했기를 바랍니다.
