[엑셀] 특정 열의 중복데이터를 삭제하고 추출하는 방법

업체에서 이런 저런 일들을 받을때, 주로 중소기업들은 엑셀로 일을 많이 하지요.

이 자료를 웹에 제대로 올릴려면 그 내용을 잘 추출해서 가공해야만 합니다.

물론, 수동으로 입력할 수도 있지만...

요즘같은 시대에는 좀... 그렇지 않습니까?

좋은 도구들이 있는데 말이죠.

이번에 엑셀로 자료를 추출하다가 배운 것들을 좀 공유하려고 합니다.

 

H/Door의 내용을 공백들을 제거한 후 중복된 것을 제거하고 싶었습니다.

결과는 아래와 같이 잘 추출되었네요.

몇번의 시행착오를 거쳐서 만든건데, 이과정을 기록으로 남기고 싶어서 적습니다.

자꾸, 그 해당시트에서 뭘 해볼려고 하니까 잘 안되더라구요.

엑셀서식부터 자동필터까지 여러가지 복합적으로 얽혀있어서 좀 힘들었지요.

그래서 그냥 통째로 다른 시트 만들어서 그 열을 복사했습니다. 4천줄이 넘더군요.

별도로 만든 후 고급필터를 사용했습니다. chatGPT를 힘을 좀 빌렸지요.

엑셀의 고급 필터 기능을 이용하여 특정 셀 범위에서 중복된 값을 제거하고 고유한 값을 추출하는 과정에서 목록 범위, 조건 범위, 복사 위치의 각 요소에 대해 자세히 설명드리겠습니다.

고급 필터의 각 요소 설명
목록 범위:

필터링할 데이터가 포함된 범위입니다. 이 범위에서 중복된 값을 제거하고 고유한 값을 추출합니다.
조건 범위:

데이터 필터링에 사용할 조건이 포함된 범위입니다. 조건 범위는 선택사항이며, 특정 조건에 맞는 데이터를 필터링할 때 사용됩니다.
조건 범위는 두 줄 이상이어야 하며, 첫 번째 줄은 조건의 제목(컬럼 이름)을 포함해야 합니다.
복사 위치:

필터링된 결과를 복사하여 붙여넣을 위치입니다. "다른 위치에 복사" 옵션을 선택한 경우 지정할 수 있습니다.
고유한 값을 추출하는 방법 (예시: I4부터 I열 끝까지)
다음은 I4부터 I열 끝까지의 모든 행에서 중복된 값을 제거하고 고유한 문자열만 추출하는 방법입니다.

1. 목록 범위 설정
I4부터 I열 끝까지의 데이터를 선택합니다. I열의 데이터 범위가 I4부터 I1048576까지라면, 이 범위 전체를 선택합니다.
또는, 데이터가 중간에 끊겨있을 경우, 데이터의 끝을 직접 선택합니다.
2. 고급 필터 사용
데이터 범위 선택:

예시에서는 I4부터 I1048576까지 선택합니다. 데이터 범위가 다를 경우 적절히 선택합니다.
데이터 탭 선택:

상단 메뉴에서 데이터 탭을 클릭합니다.
고급 필터 선택:

데이터 탭에서 정렬 및 필터 그룹에서 고급을 클릭합니다.
고급 필터 설정:

고급 필터 창이 나타나면 다음과 같이 설정합니다:
필터 옵션 선택:
"다른 위치에 복사"를 선택합니다.
목록 범위:
I4부터 I열 끝까지의 범위를 선택합니다. (예: I4:I1048576)
조건 범위:
조건이 필요 없으므로 빈 상태로 둡니다.
복사 위치:
고유 값을 복사하여 붙여넣을 위치를 지정합니다. 예를 들어, J4 셀부터 시작하도록 설정합니다. (예: J4)
고유 기록만:
하단의 고유 기록만 옵션을 체크합니다.
확인 버튼 클릭:

설정이 완료되면 확인 버튼을 클릭합니다.
예시 절차:
목록 범위 선택: I4부터 I열 끝까지 (I4:I1048576).
복사 위치 선택: J4.
고유 기록만 선택: 체크.
절차 요약:
I4부터 I열 끝까지의 데이터 선택.
데이터 탭 > 고급 클릭.
다른 위치에 복사 선택.
목록 범위: I4
설정.
복사 위치: J4 설정.
고유 기록만 옵션 체크.
확인 클릭.
이렇게 하면 I열의 중복된 값을 제거하고 고유한 값만 J열에 복사됩니다. 추가적인 설명이나 도움이 필요하시면 언제든지 말씀해 주세요!

고급필터 사용

 

위의 버튼을 누른 후

 

조건범위는 필요없고 위와 같이 간단하게 정리할 수 있습니다.

하지만, 이에 선행되어야 할 공백제거에 대해서 좀 알려드립니다.

행의 공백을 제거하는 방법, 아니 공백의 셀을 제거하는 방법입니다.

엑셀에서 A열의 모든 빈 셀을 제거하고 나머지 데이터를 위로 이동시키는 방법을 설명드리겠습니다.

이를 위해 두 가지 방법을 소개합니다: 하나는 엑셀의 기본 기능을 사용한 방법이고, 다른 하나는 VBA 매크로를 사용하는 방법입니다.

방법 1: 엑셀의 기본 기능을 사용한 빈 셀 제거 A열 선택: A열 전체를 선택합니다. (A1 셀을 클릭한 후 Ctrl + Shift + 아래쪽 화살표 키를 누르면 A열의 모든 셀이 선택됩니다.)

찾기 및 선택: 상단 메뉴에서 홈 탭을 클릭하고, 찾기 및 선택 그룹에서 찾기 및 선택을 클릭한 후 이동 옵션을 선택합니다.

빈 셀 선택: 이동 옵션 대화 상자에서 빈 셀을 선택하고 확인을 클릭합니다.
A열에서 모든 빈 셀이 선택됩니다.
셀 삭제: 상단 메뉴에서 홈 탭을 클릭하고, 셀 그룹에서 삭제를 클릭합니다.

팝업 메뉴에서 셀 삭제를 선택합니다.

셀 삭제 옵션: 위로 셀 이동을 선택하고 확인을 클릭합니다

. 이 과정을 통해 A열의 빈 셀이 제거되고, 아래에 있는 셀이 위로 이동합니다.

 

위의 과정으로 실행하면 행이 많다면 '응답없음'이 엑셀에 뜰 정도로 시간이 좀 걸립니다.

각 행을 지우고 위로 올리는 작업을 하는거니

느긋하게 기다리면 됩니다.

그냥 오류난줄 알고 창을 닫으면 안되요~

이렇게 해서 잘 정리된 자료를 받았지만, 이런 문제가 있었습니다.

뜻밖의 결과는 아니지만, 셀에 엔터키가 먹혀있는것이죠.

보통 'Alt+Enter'를 쳐서 저렇게 표현하는 경우가 종종있지요. 셀 안에 다 표시하고 싶을때, 하지만

데이터 처리할때는 쥐약같은 것입니다.

그래서 위의 enter키를 전부 공백으로 수정하는 작업을 했습니다.

 

위와 같이 하는 코드는 아래와 같습니다.

엑셀에서 셀 내의 줄 바꿈(Alt+Enter)을 공백으로 대체하고 하나의 줄로 연결된 문자열로 만드는 방법을 소개하겠습니다.

이를 위해 SUBSTITUTE 함수를 사용하여 줄 바꿈 문자를 공백으로 대체할 수 있습니다.

SUBSTITUTE 함수 사용법 SUBSTITUTE 함수는 지정된 텍스트에서 특정 문자를 다른 문자로 대체합니다

. 줄 바꿈 문자는 CHAR(10)로 나타낼 수 있습니다. 공식을 C열에 적용하는 방법: B열 데이터 확인: B열에 있는 데이터를 확인합니다.

예를 들어, B2 셀에 '304HL/TI_BK(방화)-2와 같은 데이터가 있습니다.

C열에 공백으로 연결된 한 줄로 표현하는 공식 적용: C열의 첫 번째 셀(C2)부터 공식을 적용합니다.

공식은 다음과 같습니다: excel 코드 복사 =SUBSTITUTE(B2, CHAR(10), " ") 이 공식은 B2 셀의 줄 바꿈 문자를 공백으로 대체합니다.

공식을 다른 셀에 복사: C2 셀의 오른쪽 아래 모서리를 드래그하여 공식을 C열의 다른 셀에 복사합니다.

예시: B2 셀의 내용: scss 코드 복사 304HL/TI_BK(방화)-2 304HL/TI_BK-2 EB-24 C2 셀에 적용할 공식 
: excel 코드 복사 =SUBSTITUTE(B2, CHAR(10), " ")

C2 셀의 결과: scss 코드 복사 304HL/TI_BK(방화)-2 304HL/TI_BK-2 EB-24 이 공식을 사용하면 B열의 각 셀에 있는 데이터를 줄 바꿈 대신 공백으로 대체하여 C열에 표시할 수 있습니다.

C2 셀에 공식을 입력한 후, 해당 셀의 내용을 복사하여 C열의 다른 셀에 적용하면 B열 전체의 데이터를 한 줄로 연결된 문자열로 만들 수 있습니다.

 

결국 이 한줄로 해결되었지요^^

=SUBSTITUTE(B2, CHAR(10), " ")

결과는 대만족입니다.

엑셀을 이용해서 웹에 자료를 올리는 분들에게 도움이 되는 글이 되었길 희망합니다.

엑셀 데이터를 웹에 올리기 위한 최적화 과정 정리

중소기업에서는 주로 엑셀을 활용하여 데이터를 관리하는 경우가 많습니다. 하지만 웹에서 데이터를 제대로 활용하려면 엑셀 데이터를 가공하여 올리는 과정이 필수적입니다. 이번에는 엑셀 데이터를 정리하는 과정에서 배운 점들을 공유하고자 합니다.

1. 중복 제거 및 공백 처리

H/Door 데이터에서 공백을 제거하고 중복된 값을 제거하는 과정 엑셀의 고급 필터 기능을 활용하여 중복된 데이터를 제거하고 고유한 값만 추출하는 방법을 적용했습니다.

고급 필터 활용법

1. 목록 범위 설정: I4부터 I열 끝까지 데이터를 선택합니다.
2. 데이터 탭 이동: 엑셀 상단 메뉴에서 [데이터] 탭을 선택합니다.
3. 고급 필터 실행: [정렬 및 필터] 그룹에서 [고급]을 클릭합니다.
4. 필터 설정:
   • "다른 위치에 복사" 옵션 선택
   • 목록 범위: I4:I1048576
   • 복사 위치: J4
   • "고유 기록만" 체크
5. 확인 클릭: 설정을 완료하고 실행하면 중복 제거된 데이터가 J4부터 출력됩니다.

2. 빈 셀 제거 후 데이터 위로 이동

엑셀에서 A열의 빈 셀을 제거하고 나머지 데이터를 위로 정렬하는 방법을 적용했습니다.

엑셀 기본 기능을 활용한 방법

1. A열 선택: A1을 클릭한 후 Ctrl + Shift + ↓를 눌러 모든 데이터를 선택합니다.
2. 빈 셀 찾기: [홈] 탭 > [찾기 및 선택] > [이동 옵션] 선택.
3. 빈 셀 선택: 이동 옵션 창에서 "빈 셀" 체크 후 확인.
4. 삭제 후 정렬: [홈] 탭 > [삭제] > "셀 삭제" 선택 후 "위로 이동" 체크 후 실행.

이 과정은 엑셀 행 수가 많을 경우 실행 시간이 다소 걸릴 수 있습니다. 응답 없음 상태가 나타나도 기다리면 정상적으로 완료됩니다.

3. 줄 바꿈(Alt+Enter) 제거 후 공백으로 대체

엑셀 셀 내에 Alt+Enter로 줄 바꿈이 포함된 경우, 데이터 처리 시 문제가 발생할 수 있습니다. 이를 해결하기 위해 SUBSTITUTE 함수를 사용했습니다.

SUBSTITUTE 함수 활용법

1. B열 데이터에서 줄 바꿈을 제거하고 공백으로 변환하려면 C열에 다음 공식을 입력합니다.

   =SUBSTITUTE(B2, CHAR(10), " ")
   

2. C열로 공식 복사: C2 셀의 오른쪽 아래 모서리를 드래그하여 공식 적용.
3. 결과 확인: B열의 데이터가 C열에서 한 줄로 정리됨.

예제 데이터 변환 전후

B열 원본 데이터 C열 변환 후

304HL/TI_BK(방화)-2	304HL/TI_BK(방화)-2
304HL/TI_BK-2	304HL/TI_BK-2
EB-24	EB-24

결론

엑셀 데이터를 웹에서 활용할 때, 중복 제거, 빈 셀 정리, 줄 바꿈 제거 등의 전처리 작업이 필수적입니다. 위 방법을 적용하면 대량 데이터를 빠르고 효율적으로 정리할 수 있습니다.

엑셀을 활용하여 데이터를 관리하는 분들에게 도움이 되길 바랍니다!