Excel에서 중복을 제거하지만 빈 행은 유지하려면 어떻게 해야 하나요?

Excel에서 중복 제거 기능은 목록에서 반복되는 값을 쉽게 제거할 수 있는 편리한 방법을 제공합니다. 그러나 목록에 빈 행이 포함된 경우 일반적인 문제는 표준 기능이 이러한 빈 행도 함께 제거하여 데이터의 원래 구조를 방해할 수 있다는 점입니다. 빈 행이 논리적 구분자나 서식 용도로 사용되었다면 이는 특히 문제가 될 수 있습니다.

목표가 중복 항목을 제거하는 것이지만 모든 빈 행을 그대로 유지하는 것이라면몇 가지 효과적인 접근 방식이 있습니다. 이러한 방법들은 빈 행에 영향을 주지 않으면서 목록을 정리하는 데 도움을 주며, 보다 전문적인 데이터 관리와 워크시트의 시각적 구조를 유지합니다. 아래에서는 다양한 시나리오에서 Excel에서 이를 달성하기 위한 몇 가지 솔루션을 설명합니다.

수식과 중복 제거를 활용하여 중복을 제거하고 빈 행 유지하기

중복 및 고유 셀 선택 기능으로 중복 강조 및 선택하기

VBA 매크로: 자동으로 중복 제거하면서 빈 행 유지하기

Excel 수식/고급 필터: 빈 행을 유지하면서 고유 값 추출하기

수식과 중복 제거를 활용하여 중복을 제거하고 빈 행 유지하기

중복을 제거하면서 빈 행을 유지하려면 먼저 보조 열을 사용하여 빈 행을 식별하고 표시한 다음 Excel에 내장된 중복 제거 도구를 활용해야 합니다. 이 결합된 방법은 고유한 값은 유지하고, 모든 기존 빈 행은 건드리지 않습니다. 이 솔루션은 보조 열이 표시된 상태에서 대부분 수동으로 이루어지는 Excel 작업을 선호할 때 유용하게 작동합니다.

1단계. 인접한 열(예: 데이터가 A1에서 시작한다면 B1에서 시작)에 다음 수식을 입력하여 빈 행을 표시합니다.

=IF(LEN(TRIM(A1))=0,ROW(),"")

이 수식은 셀 A1이 비어 있거나 공백만 포함되어 있는지 확인합니다. 그렇다면 행 번호를 표시하고, 그렇지 않으면 해당 셀을 비워둡니다. 목록의 모든 항목 옆으로 수식을 드래그하여 모든 빈 행을 표시합니다.

참고: 데이터가 여러 열에 걸쳐 있다면 참조를 조정하세요(예: 필요에 따라 A1 또는 B1 사용). TRIM을 사용하면 공백만 있는 셀도 빈 것으로 간주됩니다.

2단계. 원본 데이터 열과 새로운 보조 열을 모두 선택합니다. 그런 다음 데이터 > 중복 제거로 이동합니다. 팝업 대화 상자에서 보조 열이 아닌 원본 열의 확인란만 선택하여 중복 항목이 대상 데이터를 기준으로 식별되도록 합니다.

팁: 데이터 선택에는 보조 열이 포함되도록 하지만, 중복 제거 키로는 원본 데이터 열을 사용하세요. 이렇게 하면 보조 열에서 표시된 빈 행이 실수로 제거되는 것을 방지할 수 있습니다.

3단계. 몇 개의 중복 값이 제거되었는지를 알리는 알림이 나타납니다. 클릭하세요 확인 메시지를 확인하고 닫습니다.

이제 목록에는 모든 고유한 값과 이전에 존재했던 모든 빈 행이 표시됩니다. 더 이상 필요 없다면 보조 열을 삭제할 수 있습니다.

적용 시나리오: 이 방법은 표준 Excel 기능만을 사용하길 원하거나 데이터가 단순할 때 권장됩니다. 데이터가 자주 업데이트되는 경우에는 완전히 자동화되지 않으며, 단계를 반복해야 합니다. 또한, 중복을 제거하기 전에 데이터 백업을 만들어 두세요. 파일을 저장하고 닫은 후에는 이 과정을 되돌릴 수 없습니다.

문제 해결: 빈 행이 여전히 제거되고 있다면, 보조 수식이 모든 빈 줄을 올바르게 표시했는지 확인하고, 대화 상자에서 주 열만 "중복 제거" 키로 사용하고 있는지 다시 확인하세요.

중복 및 고유 셀 선택 기능으로 중복 강조 및 선택하기

Kutools for Excel의 중복 및 고유 셀 선택 유틸리티는 기본적으로 빈 행을 유지하면서 중복 항목을 시각적으로 식별하는 데 특히 유용합니다. 이 솔루션은 중복 항목을 삭제하기 전에 수동으로 검토하거나 추가 작업을 위해 강조 표시하길 원하는 경우 적합합니다.

1. 데이터(빈 행 포함)가 포함된 범위를 선택한 다음 클릭하세요 Kutools > 선택 > 중복 및 고유 셀 선택.

2. 다음에서 중복 및 고유 셀 선택 대화 상자에서 중복 (첫 번째 제외) "규칙" 아래를 선택하세요. 또한 배경 색상 채우기 및 글꼴 색상 채우기 상자를 체크하여 선택한 색상으로 중복 항목을 자동으로 강조 표시할 수 있습니다.

팁: 전체 중복 행을 선택하려면 한 열이 아니라 전체 데이터 범위를 선택하고, 해당 유틸리티를 적용하세요.

3. 클릭하세요 확인대화 상자에 선택된 중복 셀 또는 행의 수가 표시됩니다. 클릭하세요 확인 닫습니다.

이제 목록의 중복 항목이 강조 표시되어 필요에 따라 수동으로 찾아 삭제할 수 있으며, 빈 행에는 전혀 영향을 주지 않습니다.

장점: 더 유연하고, 리스크가 없으며, 제거 전에 검토를 지원합니다. 빈 행은 기본적으로 변경되지 않습니다. 이 접근법은 시각적 제어가 필요하거나 사례별로 중복을 처리해야 할 때 유용합니다.

참고: 일괄로 중복을 삭제해야 하는 경우 강조 표시된 행을 정렬하거나 필터링할 수 있으며 원래 워크시트의 시각적 구조를 유지할 수 있습니다.

데모: Kutools for Excel을 사용하여 중복 및 고유 셀 선택하기

VBA 매크로: 자동으로 중복 제거하면서 빈 행 유지하기

빈 행을 유지하면서 중복을 자동으로 제거해야 하는 경우 VBA 매크로를 사용하는 것은 실용적인 선택입니다. 매크로를 사용하면 목록이 얼마나 자주 변경되더라도 한 번의 클릭으로 데이터를 정리할 수 있으며, 보조 열에 의존하지 않습니다. 이 방법은 워크시트 구조가 일관성이 있고 성능과 정확성을 보장하는 반복 가능한 솔루션이 필요한 경우 이상적입니다.

참고: 매크로 실행 후 행동을 쉽게 되돌릴 수 없으므로 항상 워크시트의 사본을 저장하세요.

1. 개발 도구 > Visual Basic로 이동합니다. VBA 편집기에서 삽입 > 모듈을 클릭합니다. 그런 다음 아래 코드를 새 모듈 창에 붙여넣습니다.

Sub RemoveDuplicatesKeepBlankRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dict As Object
    Dim cell As Range
    Dim checkRange As Range
    Dim i As Long
    
    Set ws = ActiveSheet
    Set dict = CreateObject("Scripting.Dictionary")
    
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Set checkRange = ws.Range("A1:A" & lastRow)
    
    Application.ScreenUpdating = False
    
    For i = lastRow To 1 Step -1
        Set cell = ws.Cells(i, 1)
        
        If Trim(cell.Value) = "" Then
            ' Blank row, do nothing and keep row
        Else
            If dict.Exists(cell.Value) Then
                cell.EntireRow.Delete
            Else
                dict.Add cell.Value, True
            End If
        End If
    Next i
    
    Application.ScreenUpdating = True
End Sub

2. 코드를 실행하려면 버튼을 클릭하거나 F5 키를 누릅니다(모듈이 선택된 상태에서). 매크로는 즉시 첫 번째 열(A)을 스캔하여 중복 값을 제거하지만 이전의 빈 행은 원래 위치에 남겨둡니다.

작동 원리: 매크로는 지정된 열의 하단에서 상단으로 루프를 돌며 이전 값을 삭제하되 모든 빈 줄은 유지합니다. 마지막으로 비어 있지 않은 셀을 식별하여 다양한 길이의 목록에 자동으로 조정됩니다.

매개변수 및 팁:

• 데이터가 다른 열에 있다면 Cells(i,1)을 관련 열 번호(예: B열은 2, C열은 3)로 변경하세요.
• 매크로는 마지막 행부터 위로 진행되며, 행 이동이 삭제 프로세스에 영향을 미치지 않도록 합니다.
• 공백만 포함된 셀도 빈 것으로 간주됩니다. 실제로 비어 있는 셀만 식별하려면 Trim(cell.Value) = ""를 cell.Value = ""로 바꾸세요.

문제 해결: 매크로가 예상대로 작동하지 않는다면 타겟 열에 병합된 셀, 숨겨진 행 또는 예상치 못한 데이터 형식이 있는지 확인하세요.

최적 사용처: 반복적인 정리 작업, 큰 데이터 세트 처리 또는 자동화된 워크플로의 일부로 매크로를 사용하는 경우.

Excel 수식/고급 필터: 빈 행을 유지하면서 고유 값 추출하기

원본 데이터에서 고유한 목록을 추출하고 빈 행을 원래 위치에 맞춰 유지하려면 고급 필터를 조건 수식과 함께 사용하거나 Excel 수식을 활용하여 새로운 목록을 작성할 수 있습니다. 이 방법은 보고서 작성이나 추가 분석을 위해 별도의 중복 제거된 목록을 생성하려는 경우 특히 유용하며, 원래 레이아웃(빈 행 포함)을 존중합니다.

다음 수식을 사용하여 빈 행을 자리에 유지하면서 고유 값 목록을 구성할 수 있습니다. 여기에 간단한 접근 방식을 소개합니다.

1. 새 열(예: B1)에서 다음 수식을 사용합니다(원본 데이터가 A1:A100에 있다고 가정).

=IF(TRIM(A1)="","",IF(COUNTIF(A$1:A1,A1)=1,A1,""))

2. 이 수식을 데이터의 모든 행으로 드래그합니다. 고유한 항목은 원래 위치에 나타나고, 이후 중복 항목은 빈 셀로 표시되며, 원래 빈 행은 계속 비어있게 됩니다.

주의사항: 숨겨진 값이나 앞뒤 공백이 유일성을 판단하는 데 영향을 미칠 수 있으니 주의하세요. TRIM은 이를 방지하는 데 도움이 됩니다. 이전 버전의 Excel에서는 메서드를 조정하거나 수동 필터링과 함께 보조 열을 사용해야 할 수도 있습니다.

적용 가능: 새로운 보고서 작성, 나란히 배열된 목록 또는 추가 처리를 위한 데이터 정리를 준비하는 경우. 이 접근법은 VBA나 추가 기능 없이 수식만을 사용하길 원하는 경우에도 이상적입니다.