2016.06.21

엑셀 붙여넣기 가이드··· 데이터 가져오기∙구문 분석하기

JD Sartain | PCWorld
다른 스프레드시트 또는 데이터베이스에서 가져온 데이터는 보통 이미 쉼표, 탭, 공백, 사용자 지정 문자 등 필드 구분 기호를 이용해 필드(Field)로 분리되어 있다. 이런 데이터베이스를 엑셀로 손쉽게 가져와서 모든 필드를 개별 열에 정렬할 수 있다. 회사에서 온라인으로 비용을 청구하거나 은행 업무를 처리할 때 일반적으로 기록 사본을 전자 서식으로 제공하기도 한다.

가장 보편적인 데이터 교환 형식은 CSV(Comma Separated Values)인데, 사용하기에 무척 편리하다. 그러나 모든 데이터를 하나의 셀에 가져오면 무슨 일이 생길까?

데이터 가져오기 및 구문 분석
웹 페이지, 문서 작성 파일, 기타 텍스트 파일 등에서 데이터 블록을 복사하고 붙여넣기(Paste) > 선택하여 붙여넣기(Special) > 텍스트(Text) 명령을 사용해 엑셀에 붙여 넣으면 모든 데이터가 한 개의 셀에 복사된다. 즉, 데이터는 여러 줄짜리여도 모든 필드가 구분되지 않은 채로 하나의 셀 안에 있다는 뜻이다. 최악의 상황이다! 이제 어떻게 해야 할까?


참고: 데이터가 인접한 열로 넘어가는 듯 보이지만 첫 번째 열에만 붙여 넣은 정보가 들어 있다.

2. 데이터(Data) > 텍스트 나누기(Text to Columns)를 선택한다. 텍스트 나누기 마법사(Convert Text to Columns Wizard) 대화창에서 구분 기호로 분리됨(Delimited) 버튼을 누른 후, 다음(Next)을 클릭한다.



3. 다음 대화 창(3단계 중 2단계, 아래 그림)에서 필드를 구분하는 문자인 구획 문자를 선택한다. CSV 파일에서는 쉼표다. 탭이나 세미콜론을 사용하는 데이터베이스도 있다. 이 데이터베이스에서 데이터를 분리하는 유일한 문자는 공백이기 때문에 공백(Space) 상자를 선택한다.

4. 하나의 문자열에 여러 개의 공백이 나타날 때는, 연속된 구분 기호를 하나로 처리(Treat consecutive delimiters as one)라는 상자를 선택한다. 이 설정은 데이터 필드들 사이에 하나 이상의 문자로 구성된 구획 문자가 있거나 데이터에 복수의 사용자 지정 구획 문자가 있을 때도 적용된다.

5. 기록이 작은 따옴표나 큰 따옴표 안에 들어있지 않기 때문에 텍스트 한정자(Text qualifier) 드롭다운 목록에서 없음(None)을 선택한다.

6. 마지막으로 다음을 클릭한다.



7. 다음 대화창(3단계 중 3단계, 아래)에서 엑셀은 각 열의 형식을 변경할 수 있는 옵션을 제공한다. 첫 번째 열이 기본으로 선택된다. 헤더에 '일반(General)'이라고 되어 있다. 일반은 모든 숫자 필드에 적용된다. 여기에서는 텍스트 필드이기 때문에 텍스트 버튼을 클릭하여 이 형식을 변경한다.

8. 두 번째 열의 아무 곳이나 클릭하면 강조 표시된다. 선택된 일반의 형식이 올바르지 않은 경우 위의 옵션에서 해당 열에 올바른 형식을 클릭한다. 여기에서는 텍스트가 올바른 형식이다.

9. 가져온 모든 필드에 이 과정을 반복한다. 그리고 마침(Finish) 버튼을 클릭한다.


10. 이제 '월'을 분리해야 한다. 우선, Modern Months 열 옆에 열을 삽입하여 분석한 데이터가 G열의 정보를 덮어 쓰지 않도록 한다.

11. 커서를 G열로 이동한다. 삽입(Insert) > 시트 열 삽입(Insert Sheet Column)을 선택한다.

12. F열의 두 번째 필드 “Modern Months”을 강조 표시하고 위의 지시사항에 따라 이 필드를 두 개의 필드로 분리한다. 사용자 지정 구획 문자는 슬래시 키이다.

13. 열을 일반에서 텍스트로 변경하고 마침을 클릭한다. 이제 월이 하나가 아닌 두 개의 열에 위치하고 있다.


텍스트 함수로 데이터 다듬기
1. 우선 "시간"이란 단어를 "월"로 바꾸어 보자. Ctrl+ H를 누른다. 바꾸기(Replace) 탭을 클릭한다. 찾을 내용(Find What) 검색 상자에 시간을 입력하고 바꿀 내용(Replace With) 검색 상자에 월을 입력한 후, 모두 바꾸기(Replace All) 버튼을 클릭한다.'

2. 이제 H와 I열을 하나의 필드로 합쳐보자. 커서를 J열로 이동하고 다음의 텍스트 수식을 입력한다: =CONCATENATE(H3, “ “, I 3). 따옴표 사이에 공백이 있기 때문에 엑셀은 두 단어 사이에 공백을 위치시킨다. 이 수식을 J3부터 J14까지 복사한다.

3. 다음 단계에서는 H 및 I열을 삭제하여 스프레드시트에 연결된 J열만 표시되도록 해야 한다. 하지만 H와 I열을 삭제하면 J열이 사라진다. 왜냐하면 앞의 두 열을 기준으로 수식을 표현하기 때문이다. J를 수식에서 텍스트 값으로 변환해야 한다.

4. J3: J14를 선택한다. (복사를 위해) Ctrl+ C를 누르고 K3열로 이동하여 마우스 오른쪽 클릭하고 선택하여 붙여넣기를 선택한다. 선택하여 붙여넣기 대화상자에서 값(Values)을 선택하고 OK를 클릭한다.


5. 이제 K열에는 완성된 문자열이 있기 때문에 H, I, J열을 삭제할 수 있다.


참고: 엑셀 2016에는 CONCAT라는 새로운 텍스트 함수가 있다. 엑셀에서는 CONCATENATE와 비슷하지만 단순한 셀 참조와는 달리 더 짧고 입력이 용이하며 범위 참조를 지원하기 때문에 더 낫다. 여러 범위의 내용을 합칠 수 있기 때문이다. 예를 들어 A3:C3, “ “, D3: F3는 A3, B3, C3 셀의 내용을 D3, E3, F3 셀로 합치면서 두 범위 사이에 공백을 둔다.

6. 엑셀 2016에서는 CONCATENATE이 텍스트 함수(Text Functions, 수식(Formulas) > 텍스트(Text) 아래)이 드롭다운 목록에 표시되지 않는다. CONCAT 함수보다 이 함수를 선호하는 경우 수식(Formulas) > 함수 더 보기(More Functions) > 호환성(Compatibility) 아래에서 찾을 수 있다. 메뉴에서 함수를 선택하면 각 수식의 문법을 알려주는 마법사 대화상자에 접근할 수 있다.


마지막으로 한 가지 유용한 요령을 제공하고자 한다. 두 개 이상의 셀에 있는 데이터를 합치는 가장 빠르고 쉬운 방법은 앰퍼샌드(Ampersand)를 사용하는 방법이다. 이 작은 기호가 CONCAT 및 CONCATENATE 함수만큼 효율적으로 기능한다. =A3& “ “ &B3 같은 수식만 입력하면 된다. 이 단순한 수식은 A3 셀 내용과 B3 셀 내용을 합치고 둘 사이에 공백을 둔다. 

editor@itworld.co.kr 




2016.06.21

엑셀 붙여넣기 가이드··· 데이터 가져오기∙구문 분석하기

JD Sartain | PCWorld
다른 스프레드시트 또는 데이터베이스에서 가져온 데이터는 보통 이미 쉼표, 탭, 공백, 사용자 지정 문자 등 필드 구분 기호를 이용해 필드(Field)로 분리되어 있다. 이런 데이터베이스를 엑셀로 손쉽게 가져와서 모든 필드를 개별 열에 정렬할 수 있다. 회사에서 온라인으로 비용을 청구하거나 은행 업무를 처리할 때 일반적으로 기록 사본을 전자 서식으로 제공하기도 한다.

가장 보편적인 데이터 교환 형식은 CSV(Comma Separated Values)인데, 사용하기에 무척 편리하다. 그러나 모든 데이터를 하나의 셀에 가져오면 무슨 일이 생길까?

데이터 가져오기 및 구문 분석
웹 페이지, 문서 작성 파일, 기타 텍스트 파일 등에서 데이터 블록을 복사하고 붙여넣기(Paste) > 선택하여 붙여넣기(Special) > 텍스트(Text) 명령을 사용해 엑셀에 붙여 넣으면 모든 데이터가 한 개의 셀에 복사된다. 즉, 데이터는 여러 줄짜리여도 모든 필드가 구분되지 않은 채로 하나의 셀 안에 있다는 뜻이다. 최악의 상황이다! 이제 어떻게 해야 할까?


참고: 데이터가 인접한 열로 넘어가는 듯 보이지만 첫 번째 열에만 붙여 넣은 정보가 들어 있다.

2. 데이터(Data) > 텍스트 나누기(Text to Columns)를 선택한다. 텍스트 나누기 마법사(Convert Text to Columns Wizard) 대화창에서 구분 기호로 분리됨(Delimited) 버튼을 누른 후, 다음(Next)을 클릭한다.



3. 다음 대화 창(3단계 중 2단계, 아래 그림)에서 필드를 구분하는 문자인 구획 문자를 선택한다. CSV 파일에서는 쉼표다. 탭이나 세미콜론을 사용하는 데이터베이스도 있다. 이 데이터베이스에서 데이터를 분리하는 유일한 문자는 공백이기 때문에 공백(Space) 상자를 선택한다.

4. 하나의 문자열에 여러 개의 공백이 나타날 때는, 연속된 구분 기호를 하나로 처리(Treat consecutive delimiters as one)라는 상자를 선택한다. 이 설정은 데이터 필드들 사이에 하나 이상의 문자로 구성된 구획 문자가 있거나 데이터에 복수의 사용자 지정 구획 문자가 있을 때도 적용된다.

5. 기록이 작은 따옴표나 큰 따옴표 안에 들어있지 않기 때문에 텍스트 한정자(Text qualifier) 드롭다운 목록에서 없음(None)을 선택한다.

6. 마지막으로 다음을 클릭한다.



7. 다음 대화창(3단계 중 3단계, 아래)에서 엑셀은 각 열의 형식을 변경할 수 있는 옵션을 제공한다. 첫 번째 열이 기본으로 선택된다. 헤더에 '일반(General)'이라고 되어 있다. 일반은 모든 숫자 필드에 적용된다. 여기에서는 텍스트 필드이기 때문에 텍스트 버튼을 클릭하여 이 형식을 변경한다.

8. 두 번째 열의 아무 곳이나 클릭하면 강조 표시된다. 선택된 일반의 형식이 올바르지 않은 경우 위의 옵션에서 해당 열에 올바른 형식을 클릭한다. 여기에서는 텍스트가 올바른 형식이다.

9. 가져온 모든 필드에 이 과정을 반복한다. 그리고 마침(Finish) 버튼을 클릭한다.


10. 이제 '월'을 분리해야 한다. 우선, Modern Months 열 옆에 열을 삽입하여 분석한 데이터가 G열의 정보를 덮어 쓰지 않도록 한다.

11. 커서를 G열로 이동한다. 삽입(Insert) > 시트 열 삽입(Insert Sheet Column)을 선택한다.

12. F열의 두 번째 필드 “Modern Months”을 강조 표시하고 위의 지시사항에 따라 이 필드를 두 개의 필드로 분리한다. 사용자 지정 구획 문자는 슬래시 키이다.

13. 열을 일반에서 텍스트로 변경하고 마침을 클릭한다. 이제 월이 하나가 아닌 두 개의 열에 위치하고 있다.


텍스트 함수로 데이터 다듬기
1. 우선 "시간"이란 단어를 "월"로 바꾸어 보자. Ctrl+ H를 누른다. 바꾸기(Replace) 탭을 클릭한다. 찾을 내용(Find What) 검색 상자에 시간을 입력하고 바꿀 내용(Replace With) 검색 상자에 월을 입력한 후, 모두 바꾸기(Replace All) 버튼을 클릭한다.'

2. 이제 H와 I열을 하나의 필드로 합쳐보자. 커서를 J열로 이동하고 다음의 텍스트 수식을 입력한다: =CONCATENATE(H3, “ “, I 3). 따옴표 사이에 공백이 있기 때문에 엑셀은 두 단어 사이에 공백을 위치시킨다. 이 수식을 J3부터 J14까지 복사한다.

3. 다음 단계에서는 H 및 I열을 삭제하여 스프레드시트에 연결된 J열만 표시되도록 해야 한다. 하지만 H와 I열을 삭제하면 J열이 사라진다. 왜냐하면 앞의 두 열을 기준으로 수식을 표현하기 때문이다. J를 수식에서 텍스트 값으로 변환해야 한다.

4. J3: J14를 선택한다. (복사를 위해) Ctrl+ C를 누르고 K3열로 이동하여 마우스 오른쪽 클릭하고 선택하여 붙여넣기를 선택한다. 선택하여 붙여넣기 대화상자에서 값(Values)을 선택하고 OK를 클릭한다.


5. 이제 K열에는 완성된 문자열이 있기 때문에 H, I, J열을 삭제할 수 있다.


참고: 엑셀 2016에는 CONCAT라는 새로운 텍스트 함수가 있다. 엑셀에서는 CONCATENATE와 비슷하지만 단순한 셀 참조와는 달리 더 짧고 입력이 용이하며 범위 참조를 지원하기 때문에 더 낫다. 여러 범위의 내용을 합칠 수 있기 때문이다. 예를 들어 A3:C3, “ “, D3: F3는 A3, B3, C3 셀의 내용을 D3, E3, F3 셀로 합치면서 두 범위 사이에 공백을 둔다.

6. 엑셀 2016에서는 CONCATENATE이 텍스트 함수(Text Functions, 수식(Formulas) > 텍스트(Text) 아래)이 드롭다운 목록에 표시되지 않는다. CONCAT 함수보다 이 함수를 선호하는 경우 수식(Formulas) > 함수 더 보기(More Functions) > 호환성(Compatibility) 아래에서 찾을 수 있다. 메뉴에서 함수를 선택하면 각 수식의 문법을 알려주는 마법사 대화상자에 접근할 수 있다.


마지막으로 한 가지 유용한 요령을 제공하고자 한다. 두 개 이상의 셀에 있는 데이터를 합치는 가장 빠르고 쉬운 방법은 앰퍼샌드(Ampersand)를 사용하는 방법이다. 이 작은 기호가 CONCAT 및 CONCATENATE 함수만큼 효율적으로 기능한다. =A3& “ “ &B3 같은 수식만 입력하면 된다. 이 단순한 수식은 A3 셀 내용과 B3 셀 내용을 합치고 둘 사이에 공백을 둔다. 

editor@itworld.co.kr 


X