빈 행과 열은 많은 경우 테이블에서 문제가 될 수 있습니다. 정렬, 필터링, 요약, 피벗 테이블 생성 등을 위한 표준 기능은 더 뒤에 있는 데이터를 선택하지 않고 빈 행과 열을 테이블 나누기로 인식합니다. 이러한 간격이 많은 경우 수동으로 제거하는 것은 매우 비용이 많이 들 수 있으며 필터가 중단 시 "걸리기" 때문에 필터링을 사용하여 "대량"으로 한 번에 모두 제거하는 것은 작동하지 않습니다.
이 문제를 해결하는 몇 가지 방법을 살펴보겠습니다.
방법 1. 빈 셀 검색
이것은 가장 편리하지 않을 수 있지만 분명히 가장 쉬운 방법은 언급할 가치가 있습니다.
내부에 빈 행과 열이 많이 포함된 테이블을 처리한다고 가정합니다(명확성을 위해 강조 표시됨).
테이블의 첫 번째 열(B열)에 항상 도시 이름이 포함되어 있다고 가정합니다. 그러면 이 열의 빈 셀은 불필요한 빈 행의 표시가 됩니다. 모두 빠르게 제거하려면 다음을 수행하십시오.
- 도시가 있는 범위 선택(B2:B26)
- 키 누르기 F5 다음을 누릅니다. 강조 (스페셜로 이동) 또는 탭에서 선택 홈 — 찾기 및 선택 — 셀 그룹 선택 (홈 — 찾기 및 선택 — 스페셜로 이동).
- 열리는 창에서 옵션을 선택하십시오 빈 셀 (공백) 키를 누릅니다 OK – 테이블의 첫 번째 열에 있는 모든 빈 셀을 선택해야 합니다.
- 이제 탭에서 선택하십시오. 홈 명령 삭제 – 시트에서 행 삭제 (삭제 — 행 삭제) 또는 키보드 단축키를 누르십시오 Ctrl 키+마이너스 – 그리고 우리의 과제가 해결되었습니다.
물론 테이블 헤더를 기본으로 사용하여 똑같은 방식으로 빈 열을 제거할 수 있습니다.
방법 2: 빈 행 검색
이미 알아차렸을 수 있지만 이전 방법은 데이터에 빈 셀을 검색할 때 연결할 수 있는 완전히 채워진 행과 열이 반드시 포함되어 있는 경우에만 작동합니다. 그러나 그러한 확신이 없고 데이터에도 빈 셀이 포함될 수 있다면 어떻게 될까요?
예를 들어 다음 표를 살펴보십시오.
여기에서 접근 방식이 조금 더 까다로워질 것입니다.
- A2 셀에 함수를 입력하십시오. COUNT (카운타), 오른쪽 행의 채워진 셀 수를 계산하고 이 수식을 전체 테이블에 복사합니다.
- A2 셀을 선택하고 명령으로 필터를 켭니다. 데이터 – 필터 (데이터 — 필터) 또는 키보드 단축키 Ctrl 키+변화+L.
- 계산된 열, 즉 데이터가 없는 모든 행을 기준으로 XNUMX을 필터링해 보겠습니다.
- 필터링 된 라인을 선택하고 명령으로 삭제하는 것이 남아 있습니다. 홈 — 삭제 -' 시트에서 행 삭제 (홈 — 삭제 — 행 삭제) 또는 키보드 단축키 Ctrl 키+마이너스.
- 필터를 끄고 빈 줄 없이 데이터를 가져옵니다.
불행히도 이 트릭은 더 이상 열로 수행할 수 없습니다. Excel은 아직 열로 필터링하는 방법을 배우지 못했습니다.
방법 3. 시트의 모든 빈 행과 열을 제거하는 매크로
간단한 매크로를 사용하여 이 작업을 자동화할 수도 있습니다. 단축키 누르기 다른+F11 또는 탭에서 선택 개발자 — 비주얼 베이직 (개발자 — Visual Basic 편집기). 탭이라면 개발자 표시되지 않는 경우 다음을 통해 활성화할 수 있습니다. 파일 – 옵션 – 리본 설정 (파일 — 옵션 — 리본 사용자 정의).
열리는 Visual Basic 편집기 창에서 메뉴 명령을 선택합니다. 삽입 – 모듈 표시되는 빈 모듈에서 다음 줄을 복사하여 붙여넣습니다.
Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 to ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 then rng가 아무것도 아닌 경우 설정 rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Then rng가 아무것도 아닌 경우 설정 rng = Columns(r) Else Set rng = Union(rng, Columns( r)) End If Next r If Not rng Is Nothing Then rng.Delete End Sub
편집기를 닫고 Excel로 돌아갑니다.
이제 히트 조합 다른+F8 또는 버튼 매크로 탭 개발자. 열리는 창에는 방금 만든 매크로를 포함하여 현재 실행할 수 있는 모든 매크로가 나열됩니다. 삭제비어 있음. 그것을 선택하고 버튼을 클릭하십시오 달리기 (운영) – 시트의 모든 빈 행과 열이 즉시 삭제됩니다.
방법 4: 파워 쿼리
문제와 매우 일반적인 시나리오를 해결하는 또 다른 방법은 파워 쿼리에서 빈 행과 열을 제거하는 것입니다.
먼저 테이블을 파워 쿼리 쿼리 편집기에 로드해 보겠습니다. 키보드 단축키 Ctrl+T를 사용하여 동적 "스마트"로 변환하거나 데이터 범위를 선택하고 이름을 지정할 수 있습니다(예: Data) 수식 입력줄에서 다음으로 변환:
이제 데이터 – 데이터 가져오기 – 테이블/범위에서(데이터 – 데이터 가져오기 – 테이블/범위에서) 명령을 사용하고 모든 것을 파워 쿼리에 로드합니다.
그러면 모든 것이 간단합니다.
- 홈 – 줄 줄이기 – 줄 삭제 – 빈 줄 삭제(홈 – 행 제거 – 빈 행 제거) 명령으로 빈 줄을 삭제합니다.
- 첫 번째 도시 열의 제목을 마우스 오른쪽 버튼으로 클릭하고 상황에 맞는 메뉴에서 Unpivot Other Columns 명령을 선택합니다. 우리 테이블은 기술적으로 올바르게 호출되므로 정규화 – 세 개의 열로 변환: 시, 월 및 시 교차점의 값과 원래 테이블의 월. 파워 쿼리에서 이 작업의 특징은 소스 데이터의 빈 셀을 건너뛴다는 것입니다.
- 이제 역 연산을 수행합니다. 결과 테이블을 원래 형태로 되돌리기 위해 다시 XNUMX차원 테이블로 변환합니다. 탭에서 월이 있는 열을 선택합니다. 변환 팀을 선택 피벗 열 (변환 — 피벗 열). 열리는 창에서 값 열로 마지막(값)을 선택하고 고급 옵션에서 작업 집계하지 않음 (집계하지 마세요):
- 명령을 사용하여 결과를 Excel에 다시 업로드해야 합니다. 홈 — 닫기 및 로드 — 닫기 및 로드… (홈 — 닫기&로드 — 닫기&로드…)
- 매크로 란 무엇이며 어떻게 작동합니까? 매크로 텍스트를 복사하는 위치, 매크로를 실행하는 방법은 무엇입니까?
- 목록의 모든 빈 셀을 부모 셀의 값으로 채우기
- 주어진 범위에서 모든 빈 셀 제거
- PLEX 추가 기능을 사용하여 워크시트의 모든 빈 행 제거