실무에서 엑셀이나 구글 시트를 다루다 보면 열 분할이 필요할 때가 많습니다. 엑셀은 파워쿼리를 사용할 수 있고 구글 시트는 구글 시트 자체의 열 분할 기능을 사용해서 이 기능을 사용할 수 있습니다. 엑셀과 구글 시트 열 분할 사용 방법을 각각 설명하고 실제 응용 사례까지 설명합니다.
구글 시트 열 분할 사용 방법과 응용
다음과 같이 Full name의 이름 전체 데이터가 있는 셀에서 성과 이름을 버튼 한번으로 나눌 수 있습니다. 열 분할 기능을 사용하면 셀을 하나하나 타이핑 하지 않아도 됩니다.
먼저 Full name의 데이터를 복사해서 성 아래에 붙여 넣습니다. 그리고 데이터 – 텍스트를 열로 분할을 클릭합니다.
열 분할 클릭 한 번으로 성과 이름에 자동으로 데이터가 입력되었습니다.
이번에는 좀 더 응용해서 아래 처럼 “~” 을 통해서 기간이 표시되어있는 날짜 데이터를 열 분할을 통해서 두 개의 날짜 데이터로 분할 하고 두 날짜 사이의 간격을 구해보도록 하겠습니다.
열 분할할 셀을 선택 하고 데이터 – 텍스트를 열로 분할을 클릭 하면 이번엔 자동으로 열 분할이 일어나지 않고 구분선: 자동 감지라는 설정이 나타납니다.
자동 감지를 누르면 쉼표, 세미콜론, 마침표, 공백, 맞춤 등 다양한 설정을 할 수 있습니다. 여기서 맞춤을 선택하여 내가 원하는 설정대로 열 분할을 설정 합니다.
맞춤을 클릭하고 “.~” 을 입력하면 아래처럼 두 개의 날짜로 열 분할 되었습니다.
이번엔 응용해서 두 개의 날짜로 날짜 사이의 간격 일수를 구해보겠습니다. 먼저 열 분할된 두개의 날짜 데이터를 보면 첫 번째는 날짜 다음에 마침표가 없지만 두 번째 날짜 데이터는 날짜 다음에 마침표 점이 있습니다. Left 함수를 사용해서 마지막 마침표를 제외한 데이터만 입력되도록 수식을 작성해보겠습니다.
=left(참조셀,len(참조셀)-1)
여기서 left 함수는 참조 셀에서 몇 번째 까지 데이터를 가져오는지를 정하고 len함수는 참조 셀의 공백을 포함한 단어의 갯수를 계산합니다.
left 함수로 계산된 셀의 수식 셀을 아래로 드래그 하고 해당 데이터를 복사 – 오른쪽 마우스 – 선택하여 붙여 넣기 – 값만 붙여 넣어서 수식이 없는 날짜 데이터로 변경하겠습니다. 값만 붙여 넣는 단축기는 Control + shift + V 동시에 누르기 입니다.
구글 시트에서는 datedif 함수를 사용해서 날짜 사이의 간격(연수, 월수, 일수)을 구할 수 있습니다. 하지만 위에서 열 분할을 통해 분할 된 데이터를 바로 datedif 함수로 적용하면 에러가 발생합니다. 이유는 분할 된 데이터들이 텍스트로 집계된 서식들이기 때문인데요, 이를 찾기 및 바꾸기를 통해서 한 번에 해결해 보겠습니다. 찾기 및 바꾸기를 control + H 단축기를 눌러서 실행시키고 찾기는 “.”을 입력, 바꾸기는 “/” 를 입력해서 모두 바꾸기를 클릭합니다.
셀을 더블 클릭 해보면 날짜 형식으로 변경된 것을 볼 수 있습니다.
이제 datedif 함수를 사용해서 날짜 사이의 일수로 구해보겠습니다.
=datedif(시작 날짜, 종료 날짜, “d”)
d는 일수를 반환하고 참고로 m은 월수, y는 연수를 반환 합니다.
29일로 두 날짜 사이의 일수를 계산 했습니다. 구글 시트 열 분할 기능은 어려워 보이지만 실제로 사용해보면 클릭 몇 번과 간단한 수식으로 처리할 수 있는 데이터 입니다. 엑셀은 파워쿼리를 사용해야 하지만 구글 시트는 자체 기능만으로 구글 계정이 있는 모든 사용자들이 사용할 수 있습니다.
파워 쿼리를 사용한 엑셀 열 분할
위에서 구글 시트 열 분할 사용법을 설명했고 이번에는 엑셀에서 파워쿼리 기능을 통한 열 분할 사용법에 대해서 알아 보겠습니다.
엑셀에서 데이터 – 데이터 가져오기 및 변환 탭 – 테이블/범위에서를 클릭합니다.
자동으로 데이터가 선택 되고 표 만들기에서 데이터 범위 확인 후 확인을 클릭합니다.
파워쿼리 편집기가 실행 되었습니다. 여기서 홈 – 열 분할 – 구분 기호 기준을 클릭합니다.
구분 기호에 따라 열 분할 편집기가 실행되고 사용자 지정에서 .~ 을 입력 후 확인을 누릅니다.
두 개의 날짜 데이터로 열이 분할 되었습니다. 구글 시트와 다르게 파워쿼리 편집기로 열 분할 하면 자동으로 날짜 데이터로 분할 됩니다. 좌측 상단에서 닫기 및 로드를 클릭하면 엑셀 시트로 가공된 데이터가 로드 됩니다.
엑셀로 가공된 데이터를 불러왔습니다. 구글 시트에서는 기간 함수를 datedif를 통해서 수식을 작성했다면 엑셀에서는 days 함수를 통해서 일수를 계산할 수 있습니다. =days(마지막 날짜, 처음 날짜)를 입력합니다.
C 열에 days 함수를 통해서 일수 계산 수식을 입력했지만 일수가 표시 되지 않고 1900-01-29의 잘못된 날짜 형식이 입력 되어있습니다. 서식이 날짜로 설정되어 있기 때문에 숫자로 변경해주면 해결됩니다.
C열에 두 날짜 사이의 일수가 계산 되어진 것을 볼 수 있습니다.
구글 시트 datedif 함수 사용법 구글 홈페이지 바로가기
엑셀 파워쿼리란? 마이크로소프트 공식 홈페이지 바로가기
참고로 엑셀 2010 및 2013 버전은 파워쿼리 추가 기능 설치 후 파워쿼리를 사용할 수 있는점 참고 바랍니다. 구글 시트 열 분할은 구글 계정만 있다면 사용 가능합니다.
엑셀과 구글 시트 열 분할 사용법과 응용 사례까지 알아 보았습니다. 구글 시트 열 분할 외에 다양한 엑셀 꿀 팁 사용법에 대한 자세한 정보는 아래 링크를 참고해 주세요.