후회없는 열정맨

숫자로 이루어진 데이터를 표기할 때 숫자의 단위가 커지게 되면 데이터의 가독성이 떨어지게 된다. 숫자의 뒷자리가 길어지게 되면서 수많은 [0]이 표현되게 되어 숫자를 읽고 판단하는 것이 어려워진다. 이런 경우 숫자를 이용한 데이터를 만들더라도 자료가 한눈에 들어오지 않아 자료의 완성도가 떨어진다. 

자료의 완성도를 높이면서 쉽게 읽히고, 보기 쉬운 데이터를 만들기 위해서는 표기되는 숫자의 단위를 일정하게 만들어서 유의미한 숫자를 강조하는 것이 좋다. 단위를 줄이는 방법으로는 크게 두가지 방법이 있다. 

1. 단위를 제곱으로 나누어 단위 줄이기

단위를 지정하지 않고 십억을 숫자로 표현하게 되면 [1,000,000,000원]으로 표기하게 된다. 이렇게 단위를 지정하지 않고 숫자를 표현하면 데이터의 용량은 커지고 자료의 가독성이 떨어진다. [1,000,000,000원]의 기본 단위를 억원으로 지정하고 숫자를 표현하게 되면 [10억 원]으로 표현이 되고, 훨씬 가독성 있는 데이터를 만들 수 있다. 억 원을 나타내기 위해서 숫자를 제곱으로 나타 낼경우에는 특수문자 [^]를 사용하면 된다. [^]를 엑셀에서 제곱을 나타내는데, 내가 제곱을 표현하고자 하는 숫자의 뒤에 [^] 표시를 하고 제곱하고자 하는 수를 기재하면 된다. 단위에서 [0]을 줄이는 방법이 바로 제곱값을 사용하는 것이다. 예를 들어 숫자 [10]의 [6] 제곱을 표현하고자 한다면, [10^6]으로 나타낼 수 있고, 이 뜻은 10의 6승, 즉 10의 6 제곱인 것이다. 

하기의 그림을 예시로 보면 제곱값을 적용하고자 하는 셀을 선택하고 해당셀을 10의 6제곱으로 나누어 준다. 하기와 같이 나타낼 때는 별도의 칸의 숫자의 단위인 백만 원을 표기하여 데이터를 작성한다면 훨씬 보기 쉬운 자료가 될 것이다. 10의 6 제곱으로 나누어준 숫자는 실제로도 [0]이 6개 삭제된 숫자로 표기가 된다. 

2. 사용자지정 서식에서 단위 지정하기

사용자지정 서식에서 숫자의 단위를 지정하는 경우에는 데이터가 원본 그대로 유지된다는 이점이 있다. 상기의 제곱값으로 나누는 경우는, 데이터의 단위가 나눗셈을 통해 아예 사라져 버리지만 사용자지정 서식을 사용하는 경우에는 데이터를 원본 그대로 유지하면서 화면에 표현되는 숫자의 단위만 조정을 할 수 있다. 

단위를 변환하고자 하는 셀을 지정한 뒤에, 마우스의 오른쪽 버튼을 눌러서 [셀서식]을 선택한다. [셀서식]의 가장 하단 부분에는 [사용자 지정] 이 있다. [사용자 지정]을 선택하면 여러가지 형식이 나타나는데, 여기에서 스크롤바를 내려보면 숫자로 표기된 형식이 있다. 숫자로 표기된 형식에 [,]를 찍어주면 [,] 마다 백의 단위가 단축된다. [,]를 두 번 넣는다면 백만 원 단위로 표현을 할 수 있다. [,]를 한 번만 넣는다면 백원원 단위를 단축시킬 수 있다. 하기의 그림에서는 [,]를 두 번 기입하여 백만 원 단위로 숫자를 간략하게 표기했다. [확인]을 누른 뒤에 해당 셀을 마우스로 클릭해 보면, 화면상에 보이는 숫자는 [1]이지만 셀에 있는 숫자는 [1,000,000]으로 표기된 것을 확인할 수 있다. 이렇게 [사용자지정] 서식을 사용하게 되면 원본 데이터를 변형하지 않고 단위를 간략하게 표기하거나 단축시킬 수 있다. 

만약 간략하게 표기된 단위를 원래 대로 복원 하고 싶다면 복원하고 싶은 셀을 선택한 후에 하기의 그림과 같이 쉼표를 눌러 주면 된다. 쉼표를 누르면 기존의 데이터와 같이 화면상에서도 [1,000,000]로 표기가 되는 것을 확인 할 수 있다. 

엑셀에서 데이터를 편집할 때 외부에서 복사하여 붙여 넣은 데이터의 경우, 외부의 공백 데이터 까지도 모두 복사하기 때문에 엑셀상에 공백 데이터가 함께 붙여 넣어지는 경우가 있다. 데이터가 하나 혹은 두 개인 경우는 쉽게 공백 문자를 삭제할 수 있지만, 수만 개의 데이터에 공백 문자가 있는 경우에는 수기로 삭제를 하기에 많은 시간이 소요된다. 

단순히 붙여 넣어진 데이터는 띄어쓰기로 표현되는 공백이 몇개인지 가늠하기도 어렵기 때문에 수기로 수정한다고 해도 일부 공백 문자가 제거되지 않아 데이터를 편집할 때 오류가 생긴다. 외부에서 가지고 온 데이터는 경우에 따라 [숫자]가 아닌 [텍스트]로 인식되기 때문에 엑셀 함수가 적용되지 않고, 간단한 [더하기], [빼기], [나누기], [곱하기] 등의 사칙 연산도 어렵게 된다. 이런 공백 문자를 [유령 문자]라고 부르며, [유령 문자]를 삭제해야만 사칙 연산과, 엑셀 함수를 적용할 수 있다. 

1. Ctrl 이용하여 모두 바꾸기.

1번 방법 : 제일 간단한 방법이며 예외의 사항 없이 모든 유령 문자 (띄어쓰기 유사문자) 를 제거 할 수 있다. 

[Ctrl + H] - [찾을 내용안에 유령문자를 복사하여 입력] - [바꿀 내용에는 아무것도 입력하지 않음] - [모두 바꾸기 클릭] 
본 방법으로 유령 문자를 제거 할 수 있다. 띄어쓰기와 우사한 문자로 표현된 공백의 문자를 단축키 [Ctrl + H]를 사용하여 모두 바꾸기 처리를 해준다.  찾을 내용안에 유령문자를 복사하여 붙여넣어준다. 입력할 때는 텍스트에 있는 유령문지를 복사하여 기입해 주면된다.

꼭 한 번만 눌러주어야 모든 내용이 바뀌게 된다. 유령문자를 여러개 넣은 상태로 [모두 바꾸기]를 클릭하면, 유령문자가 두 번 연속으로 입력된 유령문자에만 적용되기 때문에 꼭 한 번만 넣어주고 모두 바꾸기를 선택한다. 

유령 문자는 하기의 그림과 같이, 셀을 선택했을때 보이지 않는 글자로 표기가 된다. 하기 회색 음영의 부분이 유령 문자가 기재되어 있는 부분이다. 해당 부분의 한칸을 복사하여 붙여 넣어 준다.

 
2. Alt 이용 하여 모두 바꾸기

상단의 그림과 동일하지만, 아스키코드를 입력하여 바꿀 수 있다. 이 방법의 경우는 코드를 잘못 누르는 경우 잘 적용되지 않는다. 1번 방법과 차이점이 있다면 찾을 내용 안에 [Alt +160] 를 입력하는 것이다. 

[Ctrl + H] - [찾을 내용 안에 [Alt +160] 숫자 입력] - [바꿀 내용에는 아무것도 입력하지 않음] - [모두 바꾸기 클릭]
여기서 입력하는 [160]은 유령문자를 아스키코드로 표현한 것이다. 즉 아스키코드에서 유령문자의 실체는 [160]번 인 것이다.

아스키코드는 미국에서 표준화가 추진된 7비트 부호이다. 1963년 당시의 ASA(American Standards Association：미국 표준 협회)에 의해 제정되어 미국의 표준 부호가 되었다. 해당 코드는 노트북과 같은 개인형 컴퓨터와 소형 컴퓨터를 중심으로 세계적으로 보급되고 있으며, 미국뿐만 아니라 국제적으로 널리 사용되고있다. 개인이 사용하는 키보드에도 아스키코드를 입력할 수 있게 되어있다. 

주의 사항이 있다면, [Alt +160]을 입력 할 때 숫자를 연속적으로 입력해야 입력한 숫자가 아스키코드로 인식될 수 있기 때문에, [And]의 개념으로 [1] and  [6] and [0]을 연속적으로 입력해야 한다. 숫자를 입력할 때 숫자를 입력하는 사이에 시간이 벌어지게 되면 코드를 인식하지 못하게 되어, 유령문자는 변경되지 않는다. 반드시 주의하여 연속적으로 기입해야 한다.

아스키코드를 이용하여 변경 하는 경우에는 변경이 안 되는 경우가 있기 때문에 하기의 그림을 이용하여 설명을 덫 붙인다. 하기 그림과 같이 [Alt]를 누른 상태에서 연속적으로 우측에 있는 숫자 키패드의 [1] and  [6] and [0]을 찾을 내용안에 입력한 뒤, 모두 바꾸기를 클릭하여 변경을 마무리한다. 

데이터를 다루다 보면 일정한 규칙이 없는 데이터에 규칙을 만들어야 하는 경우가 있다. 일반적으로 영어를 사용할 때 고유명사의 경우 첫 문자의 스펠링을 대문자로 적어야 하지만 소문자로 적혀 있거나, 문장의 첫 번째에 오는 단어를 대문자로 써야 하나 소문자로 기재하는 등의 오류를 발견할 수 있다. 발견된 오류들을 모두 수기로 변경하기에는 많은 시간이 소요된다. 또한, 수기로 변경한 데이터들은 휴먼에러를 초래할 수 있기 때문에 엑셀의 기본 함수 기능인 UPPER, LOWER, PROPER 등을 사용하여 휴먼에러 없이 데이터를 변경하는 것이 좋다. 

회사에서의 나의 직무상 데이터를 취급하는 일이 많은데, UPPER, LOWER, PROPER 등의 함수를 사용하여, 수기로 변경했다면 한 시간 이상이 소요되는 일들을 단 1분 만에 해결했다. 

1. UPPER 함수 

지정한 셀의 텍스트를 모두 대문자로 바꾸는 함수이다. 하기의 표와 같이 대문자로 변경하고자 하는 대상 문자를 특정한 엑셀의 셀에 기입한다. 하기와 같이 함수식을 셀에 기입하고 지정한 셀을 함수에 넣어준다. [=UPPER(    )] 함수의 결과 값으로 하기의 표와 같이 모든 텍스트가 대문자로 변경되는 결과 값을 얻을 수 있다. 

2. Lower 함수 

UPPER 함수와 반대로 지정한 셀의 모든 텍스트를 소문자로 바꾸는 함수 이다. 텍스트가 모두 대문자로 기재되어 있는 경우 Lower 함수를 사용하여 모든 텍스트를 한 번에 소문자로 변경할 수 있다. UPPER 함수를 적용했을 때와 동일하게, 하기의 표와 같이 함수식을 셀에 기입하고, 텍스트를 변경하고자 하는 셀을 함수에 넣어준다. [=Lower(   )] 함수의 결과 값으로 하기의 표와 같이 모든 텍스트가 소문자료 변경되는 결과 값을 얻을 수 있다. 

3. Proper 함수 

지정된 문자의 첫 글자를 대문자로 변경 하고 나머지 문자는 소문자로 반환하는 함수이다. 고유 명사인 사람의 이름, 가게의 이름, 기관의 이름등을 표기할 때는 앞 글자를 대문자로 표기해야 한다. 이런 경우에 유용하게 사용할 수 있는 함수이다. 하기의 표와 같이 함수를 지정된 셀에 기입하고, 변경하고자 하는 대상 문자를 함수에 넣는다. [=Proper] 함수를 사용하여 하기의 표와 같이 앞글자가 대문자로 변경된 결괏값을 얻을 수 있다. 

엑셀에서 사용 할 수 있는 함수는 무수히 많지만, 알지 못해 사용하지 못하는 경우가 많다. 정보를 알지 못하는 경우 업무를 하는데에 더 많은 시간을 소모하게 된다. 가능하다면, 되도록 많은 정보를 습득하여 업무 시간을 단축하고, 빠른 퇴근을 할 수 있기를 희망한다. 

방대한 양의 데이터를 분석할 때 중복된 항목을 찾아 중복 항목을 제거하거나, 중복된 항목이 있는지를 점검할 수 있다. 데이터에서 행 전체를 제거할 것 인지, 일부 열에 있는 행의 값만 상제 할 것인지에 따라 선택해야 하는 영역이 달라지게 된다. 중복 값을 제거하는 기능을 사용하면, 일일이 매뉴얼로 작업을 하는 것보다 시간을 훨씬 단축할 수 있으니 중복된 항목을 찾고자 한다면 하기의 내용에 따라 진행해 보면 좋을 것 같다. 

1. 중복값 표시 

보유하고 있는 데이터에서 중복된 항목이 있는지를 쉽게 확인 하는 방법

1) 중복된 값을 확인하고자 하는 데이터 영역을 드레그하여 선택 

2) 홈 클릭 

3) 홈에 있는 항목 중 조건부 서식 클릭 

4)셀 규칙 강조 클릭 

5) 중복 값 클릭 

중복된 값이 있는 경우, 적용할 서식 부분에서 서식을 설정하여 중복 값을 나타 낼 수 있다. 적요할 서식에는 엑셀에서 흔히 사용하는 지정된 양식이 기재되어 있지만, 본인이 원하는 서식을 지정하여 중복값을 표시하고자 하는 경우, 사용자 지정 서식을 클릭하여 원하는 서식으로 지정하여 나타 낼 수 있다. 

만약 데이터 전체가 아닌, 일분 영역에 대해서만 중복값을 확인하고자 한다면 확인하고 싶은 데이터 영역만 드래그 하여 선택하면 된다. 위의 데이터의 경우 B3:N29 영역까지는 모두 선택했지만, B3:B29 영역까지만 마우스로 드래그 하여 지정하면 Company에 대해서만 중복된 값을 확인할 수 있다. 

2. 중복값 제거 

종복된 항목을 제거하려 할 때에는 우선 전체 데이터 영역을 드래그 하여 선택한 후, 데이터 항목을 클릭한다. 데이터 항목 선택 후 중복된 항목 제거를 선택한다. 하기 그림처럼 모든 데이터 영역을 드래그 하여 선택하였다면 중복값을 제거하려고 할 때 제거하려는 [열] 항목을 선택할 수 있다. 

하기 그림의 Company 항목에서 중복 되는 항목을 제거하려 한다면, Company 항목만 선택한 후에 [확인] 버튼을 눌러준다. Company 항목이 아닌 다른 항목을 선택하려 한다면, 다른 항목을 선택 후에 동일하게 [확인] 버튼을 눌러준다. 

하기 그림의 경우 Company를 선택 후에 [확인] 버튼을 눌렀을 때의 결과 값이다. 고유한 값인 세개의 회사 이름과 결과만 남고 나머지 데이터는 제거된다.

엑셀에서 데이터를 활용하여 분석을 하는 경우 피벗 기능을 활용하면 분석에 시간을 단축 할 수 있다. 피벗은 다량의 데이터를 정리 할 수 있는 강력한 도구이다. 피벗 기능을 활용하면, 데이터의 비교는 물론 패턴과 추세를 확인 할 수 있다. 이번 포스팅에는 피벗데이블을 설정하는 방법과 설정된 피벗데이블의 원본 데이터를 변경하는 방법을 포스팅 해보겠다. 

이직을 하고 나서의 현재 직무는 영업 실적과, 이익을 분석하는 일이다. 그렇기 때문에, 이전에는 간단한 함수를 활용하여 업무를 하는 경우가 많았다면, 요즘은 피벗테이블을 활용하여 다량의 데이터를 분석하는 경우가 많다. 피벗 테이블은 디잔인 설정시에 테이블의 색상을 변경 할 수 있고, 레이아웃도 함께 변경 할 수 있다. 변경한 데이터를 가지고 추가적인 편집 없이 데이터를 사용 할 수 있는게 장점이다. 

1. 엑셀에서 분석을 하고자 하는 데이터를 하기의 그림과 같이 선택한다. 선택된 데이터를 블럭 처리 후에, 삽입을 항목을 클릭한다. 삽입항목을 클릭하면, 가장 왼쪽 부분에 피벗테이블이라는 항목을 확인 할 수 있다. 피벗 테이블 항목을 클릭하면, 데이블을 선택한 범위에서 만들것 인지, 외부에서 데이터를 가지고 올 것인지 선택 할 수 있는 선택 항목이 나온다. 

이번의 경우, 드레그하여 블럭 처리를 한 부분에 대한 피벗테이블을 설정할 것이기 때문에, [테이블/범위에서]를 클릭한다.[테이블/범위에서] 클릭시 하기 그림과 같이 피벗테이블을 배치 할 위치를 선택 할 수 있다. 금번의 경우 [새 워크시트]를 선택하여, 진행한다. 

2. [새 워크 시트]를 누르게 되면, 새로운 Sheet가 생성되면서 테이블의 필드를 설정 할 수 있는 항목이 생성된다. 행과 열에 맞추어, 원하는 항목을 배치시킨다. 금번 데이터의 경우 일자별, 회사별 납품 수량을 확인 하는 데이터를 만들 예정이기 때문에, 피벗 테이블 피드는 열에 일자를 넣고, 행에 회사를 넣고, 값에 납품 수량을 넣어 보았다. 

3. 상기 기술한 바와 같이, 열에 일자를 넣고, 행에 회사를 넣고, 값에 납품 수량을 넣어 테이블을 만들게 되면, 하기의 그림과 같이 설정된다. 각 월별 납품 수량을 더하기 함수를 사용하지 않고 한번에 확인 할 수 가 있다. 

만약 담당자를 기준으로 납품 수량을 확인하고 싶다면, 행 부분에 담당자를 넣으면 된다. 

4. 여기까지는 일반적인 피벗테이블을 만드는 방법이다. 하지만, 간혹 데이터가 추가 되는 경우가 있다. 데이터가 추가되는 경우 새로운 피벗 테이블을 만들지 않고, 기존의 피벗테이블에서 내용을 수정 할 수 있다. 

하단의 왼쪽 그림과 같이, 데이터가 추가되는 경우에는, 기존에 작성한 피벗테이블에 마우스를 클릭한다. 마우스로 클릭을 하면, 피벗 데이블 분석이라는 항목을 확인 할 수 있다. 피벗 테이블 분석에서, 원본 데이터 변경을 클릭한다. 

5. 원본 데이터를 변경하게 되면, 기존의 Sheet에서 데이터를 선택 할 수 있도록, 원본 데이터가 있는 Sheet으로 자동적으로 Sheet가 옮겨 진다. 시트가 옮겨 지게 되면서, 분석 하 데이터를 선택할 수 있는 새로운 창이 나타난다. 추가된 데이터는 엑셀의 A열 부터 E열까지의, 16번째 행에서 19번째 행까지 이다. 따라서, 표 또는 범위 선택 부분에 19를 기재해준다. 추가된 데이터가 끝난 범위를 의미하는 19를 입력 후에 확인 버튼을 누르면 오른쪽 그림과 같이 [DDD] 회사의 데이터가 추가된것을 확인 할 수 있다.