티스토리 뷰
엑셀 강의 1강의 내용은 이런 것이었습니다. ① 엑셀의 기본적인 워크플로우는 '데이터 > 가공 > 보여주기'이다. ② 그래서 '데이터'가 무엇인지부터 제대로 익혀야 한다.
지난 10개의 강의를 통해 '데이터'에 대한 것은 대략 말씀드렸습니다. 드디어 엑셀에서 '가공'이란 어떻게 하는 것인지 말씀드릴 차례입니다.
'가공'의 정의
엑셀에서 데이터의 가공은 '기존의 데이터로부터 새로운 데이터를 얻어내는 행위'라 할 수 있습니다. 이때 새로운 데이터는 기존의 데이터보다 우리에게 필요하고 유익한 것이어야 합니다.
제 1강에서 말씀드렸던 예를 다시 가져와 보겠습니다.
하늘색으로 칠해진 영역이 기록되어 있던 '기존의 데이터' 부분입니다. 이 값들을 계산하여 월별 기름값이라는 '새로운 데이터'를 만들어 냈습니다.
우리가 결국 궁금해하는 것은 '월별 기름값'일 것이기 때문에 기존 데이터를 가공하여 우리에게 더 유익한 데이터를 만들어 낸 셈입니다.
즉, 가공의 정의를 축약한다면 '더 유용한 데이터를 생산해내는 것'이라고 말할 수도 있겠습니다. 이제부터는 이 점을 꼭 인식하며 가공 파트의 강의를 진행해보도록 하겠습니다.
엑셀 데이터의 '가공' 방법
엑셀 데이터를 가공하는데는 여러가지 방법이 있을 수 있습니다.
(1) 함수를 사용하는 방법
'엑셀'하면 다들 함수를 떠올릴 정도로, 함수는 엑셀에서 가장 유용하게 사용되는 데이터 가공 방법입니다. 아래와 같은기존 데이터(판매량, 단가, 마진율)로부터 새로운 데이터(이익금)을 가공해낸다고 해보겠습니다.
'판매량 X 단가 X 마진율'을 계산하면 이익금이 나오기 때문에, 이익금 열에는 좌측에 있는 값들을 모두 곱해주는 함수을 입력해주면 됩니다.
여기서 B2:D2 부분은 곱해야할 영역을 지정해주는 주소입니다. 자세한 설명은 이후 가공 파트 강의에서 진행할 예정이니 지금은 눈으로 따라와주시기만 해도 좋습니다.
동일한 함수를 3, 4열에도 입력하고 나자 이렇게 간단하게 이익금 계산이 끝났습니다.
(2) '찾기 및 바꾸기'를 사용하는 방법
엑셀에서 기본적으로 제공하는 기능인 '찾기 및 바꾸기(Ctrl+H)'도 데이터를 가공할 때 자주 사용하게 되는 기능입니다.
예를 들어, 아래와 같은 기존 데이터(입국일, 출국일)로부터 새로운 데이터(체류기간)을 구하는 작업을 한다고 해보겠습니다.
이와 같은 데이터의 가공 방법은 날짜와 시간에 관한 표시형식 부분에서 간략히 말씀드렸던 적이 있습니다.
먼저, 10강에서 말씀드렸던 대로 데이터의 가공은 [출국일]에서 [입국일]을 빼주는 것만으로 할 수 있습니다.
그러나 막상 이렇게 수식을 적용하고 나면 예상치 못한 결과를 보게 됩니다.
바로 수식에 잘못된 요소가 전달되었다는 [#VALUE!] 라는 에러 메시지가 출력되는 것입니다. 왜 이런 잘못된 결과가 나오는지는 B열과 C열의 정렬을 보면 알 수 있습니다.
B열과 C열은 좌측으로 정렬되어 있습니다. 이는 이들이 [날짜(숫자)]가 아닌 [문자(텍스트)] 형태로 저장되어 있다는 것을 의미합니다. 숫자가 아닌 문자를 서로 빼었으니 오류가 발생한 것이지요. 이런 문제는 9강에서 말씀드렸던 바가 있습니다.
이러한 문제는 [20.10.1] 형태로 적혀 있는 데이터를 [20-10-1]과 같이 '엑셀 프로그램이 날짜로 인식할 수 있는 형태'로 바꿔줌으로써 해결할 수 있습니다.
이럴 때 사용하는 것이 '찾기 및 바꾸기' 기능입니다.
'찾기 및 바꾸기'를 실행하여 구두점(.)을 대쉬(-)로 바꾸어주겠다는 내용을 입력하였습니다. [모두 바꾸기]를 실행하면 다음과 같은 결과가 얻어집니다.
표시형식을 제대로 지정해놓지 않아 결과가 이상하게 나온 것처럼 보입니다. 그러나 사실은 모든 데이터가 제대로 정리되었다는 것을 알 수 있습니다. 왜냐하면,
- B열의 입국일은 날짜 형태로 바뀌었고,
- C열의 출국일은 숫자로 표시되었지만 표시형식을 날짜로 바꿔주면 해결될 일이며
- D열의 체류기간은 [#VALUE!] 에러가 없어지고 기간을 나타내는 값이 잘 적혀있기 때문입니다.
날짜의 표시형식을 간략하게 yy-mm-dd 형태로 바꿔보겠습니다.
기존 데이터(문자 형태의 날짜 데이터)를 새로운 데이터(숫자 형태의 날짜 데이터)로 가공한 뒤, 차이를 계산하여 우리에게 최종적으로 필요한 데이터(체류기간)을 만드는 예로 보시면 되겠습니다.
(3) 기타 엑셀 기능들 활용
'함수'와 '찾기 및 바꾸기' 기능을 사용하면 사실 엑셀에서 구현할 수 있는 데이터의 가공을 대부분 처리할 수 있습니다. 그러나 그 밖에 엑셀에는 데이터의 가공을 위하여 만들어 놓은 기능들이 정말 많습니다.
예를 들어, '데이터 나누기', '중복된 항목 제거' 등 기능이 그러한 것들인데요. 이러한 기능들은 간단한 경우가 많으니 이 자리에서 모두 예시를 들어보기 보다 관련 강의를 진행할 때 소개할 수 있도록 해보겠습니다.
'가공'을 할 때 주의사항
가공을 할 때 항상 신경을 쓰셔야할 것이 있습니다. 그것은 바로 '데이터'의 개념에 대한 것입니다. 두 번째 예에서 보았듯이, 데이터가 '문자'이냐 '숫자'이냐에 따라서 데이터가 우리에게 얼마나 유용하게 쓰일 수 있는지가 달라집니다.
따라서 데이터를 가공할 때에는 '기존 데이터'가 어떤 데이터였는지 신경을 쓰셔야 하고, 또한 우리가 만들어내는 '새로운 데이터'가 어떤 데이터형이 될 지도 신경을 쓰셔야 합니다.
문자로 되어 있는 게 유용한 데이터는 문자로 만들고, 숫자로 되어 있는 게 유용한 데이터는 숫자로 만드는 것. 그것이 우리가 신경써야 할 부분입니다.
그래서 '가공'에 대한 설명을 하기에 앞서 '데이터'에 대한 강의를 10개에 걸쳐 자세히 말씀드린 것입니다.
예시 파일