｜"텍스트 나누기" 사용하기

여러 자료들을 다루다 보면 엑셀처럼 셀 구분이 정확하게 되어있는 자료도 있지만,

셀 구분이 되어있지 않은 채로 코드화된 데이터를 사용하게 되는 일도 있습니다.

셀 구분이 되지 않으면 해당 자료를 활용하기는 매우 어렵습니다.

｜"텍스트 나누기" 따라하기

아래와 같은 자료가 있다고 가정하겠습니다.

데이터는 텍스트처럼 한 셀에  "/"표시로 구분되어 있으며, 4종류의 정보를 담고있는 것으로 보입니다.

"/"표시로 구분되어있기 때문에, 이를 활용하여 텍스트 나누기를 해보겠습니다.

먼저, 텍스트가 기재된 영역을 선택해줍니다.

데이터 메뉴- 텍스트 나누기를 선택합니다.

"/" 표시로 구분되어있는 데이터이기 때문에, "구분 기호로 분리됨"을 선택합니다.

구분기호를 지정하기 위해 "기타"를 선택한 뒤 "/"를 표시해줍니다.

구분되어 기재될 각 영역의 셀형식을 지정해 줄 수 있습니다. 각각의 열을 선택하여 지정해준 뒤 '마침'을 누릅니다.

하나로 합쳐져있던 텍스트데이터가 각 셀에 입력이 되게 됩니다.

각각의 데이터를 모두 사용할 때 이 기능을 활용하면 유용하며, 저들 중 일부의 데이터만 추출할 경우에는 LEFT, RIGHT 함수를 활용할 수 도 있습니다.

※LEFT, RIGHT 함수 활용법은 아래 링크에서 확인하실 수 있습니다.

안녕하세요!

김대리입니다. 

이번에는 유효성 검사를 설정하는 방법에 대해서 얘기하려고 합니다.

그럼, 데이터 유효성 검사가 무엇일까요? 

엑셀로 문서를 작성하는 경우가 많이 있으실겁니다.

제가 일하는 곳에서도 엑셀로 많은 자료를 관리하고 있습니다. 

먼저 위의 그림처럼 기존에 만들어져있는 문서에 정보를 추가하신다고 가정하겠습니다. 

직급에 커서를 가져가보았을때 이처럼 값을 기재하는 것이 아닌, 

'목록에서 선택(보기에서 선택)' 하게 하는 문서들이 있었을 것으로 생각합니다.

정확한 정보를 기재하게 하기 위해서 먼저 정보들을 확정지어 목록으로 만든 뒤, 

문서 작성자에게는 해당 목록에서 선택을 하게 하는 기능을 만들때

우리는 유효성 검사라는 기능을 사용하게 됩니다. 

즉, 유효성 검사란, '이 셀에 들어가는 정보가 유효한 정보인지 검사하는 기능' 이라고 할 수 있겠습니다. 

먼저 예제를 따라서 시작해보겠습니다. 

유효성검사 예제.xlsx

현재 예제에는 유효성 검사가 설정되어있지않습니다. 

우리는 소속, 직급, 입사일 모두에 유효성 검사를 생성시켜보도록 하겠습니다. 

가장 먼저 해야할 일은 정확한 소속과 직급을 기재하기위해 소속과 직급의 종류를 확인하는 것입니다. 

참석대상이 되는 팀과 회사에 존재하는 직급을 모두 확인한 뒤, 

새 시트를 만들어서 위와 같이 기재해주었습니다.(시트명은 '참조'로 하였습니다.)

A열과 B열은 제가 편의상 붙여서 적어두었을뿐 관계는 없습니다. (다른 시트에 작성하셔도 됩니다.)

다시 원래의 시트로 돌아와서 소속이 적혀있는 C열을 선택해줍니다.

(헤더가 적혀있는 C1셀은 제외했습니다.)

상단 메뉴에서 '데이터' - '데이터유효성검사'를 클릭합니다.

그러면 '데이터 유효성'이라는 메뉴가 뜨게되고 이 곳에서 우리는 데이터의 성격을 지정해줄 수 있습니다. 

'제한 대상'은 '이 셀에 들어가는 정보는 아래와 같은 값이어야 한다.'를 지정해주는 것입니다. 

-정수 : 정수만 들어가야한다.(예 : -8, 0, 2 등)

-소수점 : 소수점 이하의 숫자가 있어야 한다. (예 : -3.2, 1.4, 8.12 등)

-목록 : 지정한 목록에 있는 항목이어야 한다.

-날짜 : 날짜가 들어가야한다.

-시간 : 시간이 들어가야 한다.

-텍스트 길이 : 지정한 텍스트 길이에 맞는 값이어야 한다.

우리는 '소속'이라는 것을 지정해주고 싶기 때문에, '제한 대상'에서 '목록'을 선택해줍니다. 

'드롭다운 표시'란, 해당 셀을 선택했을때, 목록이 주르륵 나와서 선택할 수 있게 하는 기능입니다. 

하단의 '원본'을 선택 후, 우리가 위에서 만들었던 팀목록이 적힌 위치를 선택해줍니다. 

위의 그림처럼 자동으로 절대참조가 걸리게 됩니다. 

"설명 메시지" 탭에서는 해당 셀을 선택하였을 경우 드롭다운 표시가 되는 것처럼, 

셀에 대한 설명 메시지가 뜨도록 설정하는 내용입니다.

'제목' 부분에 "당신의 소속을 선택해주세요." 라고 기재해주도록 하겠습니다.

'설명 메시지' 부분에 존재하는 소속을 적도록 하겠습니다. 

"오류 메시지" 탭에서는 '유효하지 않은 값'을 입력했을때 

입력을 거절하며 오류를 표시하는 것을 설정하는 부분입니다.

-중지 : 메시지를 보여주며 입력이 거절됩니다.

-경고 : 메시지를 보여주며 입력이 가능합니다.

-정보 : 메시지를 보여줍니다. 

우리는 리스트에 모든 부서를 기재했기 때문에 '중지'를 선택 후 위와 같은 오류 메시지를 기재하겠습니다.

"IME 모드" 탭에서는 해당 셀을 선택하였을 경우 자판의 설정을 바꾸도록 하는 기능입니다. 

변경할 필요가 없으므로 현재 상태 유지로 확인을 눌러보겠습니다 .

소속에 해당하는 C열에 커서를 가져가면 메세지와 함께 드롭메뉴가 표시되며, 

해당하는 팀들을 선택할 수 있습니다.

드롭다운에서 선택하지 않고 저곳에 없는 '비서실'이라는 팀을 수기로 입력하게 되면

위와 같은 '오류 메시지'를 볼 수 있습니다.

똑같은 방법으로 D열의 직급을 선택하는 부분에 직급에 대한 유효성 검사를 추가해보시면 되겠습니다. 

E열의 입사일은 '데이터 유효성' 메뉴에서 

'제한 대상'을 날짜로 선택한 뒤 날짜 범위만 지정해주시면 

다른 기능은 똑같이 사용할 수 있습니다.

우리의 예제는 참석자가 10명 안쪽이기때문에 수기로 입력해도 상관이 없지만, 

극단적인 예로 참석자가 10,000명일 경우에는 데이터의 일관성이 중요한 문제로 부각됩니다.

(예 : 소속을 영업1팀, 영업 1팀, 영업1 팀, 영업 1 팀 등으로 기재할 경우 

피벗테이블에서 모든 팀이 각각의 팀으로 구성이 됩니다.)

이처럼 유효성 검사는 데이터의 정확도를 높혀서 데이터 분석의 정확도를 높히는 역할을 합니다.

감사합니다!

안녕하세요. 김대리입니다. 

이번에는 피벗테이블(PIVOT TABLE)에 대해서 얘기해보겠습니다. 

저는 피벗테이블이 엑셀에서 가장 유용한 기능이라고 생각합니다. 

피벗테이블이란, 

데이터가 나열되어있는 정보들을 활용하여,

필요한 정보를 표의 형태로 요약시킬 수 있는 기능입니다.

지난번에 SUMIFS 함수에 대해서 얘기하면서, 

위와 같은 데이터를 활용할 때에는 SUMIFS함수보다 피벗테이블이 유용하다고 적었었습니다. 

이번에는 똑같은 예제를 가지고 SUMIFS함수와 똑같은, 아래의 정보를 만들어보도록 하겠습니다. 

<SUMIFS함수로 만든 결과물>

피벗테이블을 만드는 것은 어렵지 않습니다. 

사실, 피벗테이블을 활용하기 위해서 자료를 맨 위의 예산표처럼 만드는 것과, 

피벗테이블을 만든 후 원하는 자료의 형태를 만드는것이 더 중요합니다. 

※예제 파일을 다운받아서 따라해주세요.

PIVOT table예제.xlsx

①먼저, 결과물(표)을 위치시킬 곳에 커서를 둔 뒤(저는 새 시트의 A1셀에 커서를 두었습니다.),

②상단 "삽입" 메뉴에서 가장 좌측에 있는 "피벗테이블"을 클릭합니다. 

(클릭 후 "피벗 테이블 만들기"라는 메뉴가 뜨면 두가지를 지정해야합니다.

<분석할 데이터>와 <피벗테이블보고서를 넣을 위치>가 그 두가지 입니다만, 

저와 똑같이 진행하셨다면 피벗테이블을 넣을 위치는 새시트 A1에 지정하였기때문에

<분석할 데이터>만 지정해주시면 됩니다.) 

③예산표를 드래그하여 지정해줍니다. (최상단의 헤더까지 포함시켜주셔야합니다.) 

(*A1:D23까지 지정해주셔도 됩니다만, 저는 그 아랫쪽까지 모두 포함하는 A:D로 지정하였습니다.)

③까지 완료하게 되면, 위와같은 피벗테이블이 생성됩니다. 

피벗테이블에 커서를 올리게 되면 우측에 피벗테이블필드가 생성되었을겁니다. 

이후에는 이 피벗테이블을 통해 원하는 정보를 만들면 되겠습니다. 

우리는 위와 같은 정보를 만들고자 하는 것이므로, 똑같은 모양을 만들어보겠습니다. 

만드는 방법은 간단합니다. 

상단에 있는 '필드' 들을 아래쪽에 있는 '필터', '열', '행', '값' 등에 드래그해서 옮겨주면 됩니다. 

위와같은 표의 모양을 만들기 위해 '회계연도'와 '기능'을 옮겨보겠습니다. 

이렇게 열과 행을 설정하게 되면, 좌측에 있는 피벗 테이블은 아래처럼 변하게 됩니다. 

이렇게 만들게 되면, 우리가 원하는 표의 모양이 완성되었습니다. 

이제 SUMIFS함수를 채워넣었듯 안쪽에 금액의 합계를 표시하기 위해, 

'피벗 테이블 필드'의 하단에 있는 '값'에 "금액"필드를 옮겨보겠습니다. 

이렇게 "금액"을 옮겨주게 되면

이와같이 우리가 원하는 데이터를 요약할 수 있습니다. 

SUMIFS함수를 설명하며, '이런 자료를 피벗테이블을 활용하는 것이 낫다.' 라고 말씀드렸던 이유는 아래와 같습니다.

회사에서 일을 하다보면, SUMIFS함수를 통해 자료를 만든 후에 정보이용자로부터 

Q. 예산항목별로 2017년 2018년 비교한 자료는 없나요? 

Q. 기능별 비교를 포함한 세부항목으로 볼 수는 없나요?

등의 새로운 요구를 받을 때가 많습니다. 

SUMIFS함수나 다른 함수를 활용할 경우 새로운 결과물을 짜기 위해서 함수도 알아야 하고, 

하나하나 오류가 생길 위험도 감수해야 합니다. 

하지만 피벗테이블은 '피벗 테이블 필드'에서 원하는 자료를 열과 행에 넣는것만으로 다양한 자료로 가공이 가능합니다. 

또한, 현재는 2017년과 2018년의 자료만 기재되어있지만, 2019년이 되고 2020년이 되었을때,

예산표의 하단에 2019년 자료를 추가만 해주신 후 피벗테이블을 새로고침하시면, 

추가된 자료까지 한번에 볼 수 있습니다. (이것이 피벗테이블 데이터영역을 A:D로 한 이유입니다.)

※주의할점 

A. 데이터를 추가한 뒤 '새로고침'을 누를 것.

B. 값 필드 설정에서 '값의 형태'를 잘 지정할 것(합계인지, 갯수인지)

C. 자료를 활용하다 행레이블, 열레이블에 누락되는 것이 있는지 확인.

.

안녕하세요! 김대리입니다.

이번에는 '조건부 서식' 이라는 기능에 대해서 알아보겠습니다.

'조건부 서식'은 셀에 입력되는 값에 따라 서식이 자동으로 변하는 기능입니다.

예를들어, 셀에 '파란색'이라는 글자가 입력되면,

아래와 같이.

그리고 셀에 '빨간색"이라는 글자가 입력되면,

아래와 같이

서식이 알아서 변동되는 엑셀의 기능입니다.

조건부 서식을 설정하는 방법은 다음과 같습니다.

위와 같이 서식이 알아서 변하게 하고 싶은 셀을 선택한 뒤,(우리 파일에서는 셀 C1)

홈-조건부 서식-새 규칙을 선택합니다.

그러면 아래와 같이 '새 서식 규칙'이라는 창이 뜨며 규칙유형을 선택하는 창이 나오게 됩니다.

우리는 값이 파란색일 경우, 빨간색일 경우에만 셀의 서식을 변경시키고 싶기 때문에,

두번째 칸에 있는 "▶ 다음을 포함하는 셀만 서식 지정"을 선택합니다. 

아랫쪽에 '규칙 설명 편집' 이라는 부분에서 '파란색'이 들어가면 서식이 변하는 기능을 설정해줄 수 있습니다.

'셀 값'을 선택한 뒤,

'해당 범위'를 눌러 '='을 선택합니다.

(왜냐면 우리는 셀 값='파란색'일 경우에만 서식을 적용할거니까요!)

'='을 선택하게 되면 우측에 값을 입력할 수 있게 되고, 그 칸에 위와 같이 '파란색'을 입력합니다.

이렇게 되면 '파란색'이 입력되었을 경우 '미리보기'에 나오는 서식을 적용시키게 됩니다.

그러면 '서식(F)'를 눌러서

글꼴 탭에서 글자색을

'채우기' 탭에서 셀의 배경을 변경하도록 합니다.

그러고 나서 "확인"을 누르면 아래와 같이 

미리보기 화면에 파란색 배경에 파란색 글자색이 보이게 되고,

​

이것은

"'파란색'이라는 글자가 입력되면 파란 배경에 파란 글자색으로 변경시키겠다."

는 뜻이 됩니다.

위에서 했던 과정을 그대~로 똑같이 반복하면 "빨간색"을 입력하게 되면 빨간배경과 빨간 글자색으로 변경되는 서식을 적용해줄 수 있습니다.

빨간색에 대한 서식적용까지 해주고 난 뒤, 조건부서식-규칙관리에 들어가게 되면 현재 적용되어있는 조건부 서식이 무엇무엇인지 볼 수 있습니다. 

우리는 파란색, 빨간색 이라는 글자가 입력되면 서식을 변경시킨다는 조건부 서식을 적용시켰기때문에 아래와 같은 창을 볼 수 있습니다. 

위와같은 화면이 보이면 조건부 서식이 제대로 적용된 것이고,

​

C1셀에 빨간색, 파란색을 입력하게 되면 서식이 변하는 것을 확인할 수 있습니다.

이번 포스팅에서는 단순히 입력 값이 정확하게 '파란색'일 경우

​

서식을 적용하는 방법에 대하여 예시를 들어보았습니다.

​

값의 범위 등에 대한 규칙 설명을 보시면 더욱 복잡한 방법으로 응용이 가능한 기능입니다. :)

※ 엑셀의 함수(수식)들에서는 TRUE와 FALSE의 값을 보여주는 함수들이 있습니다.

​

예를들어, A열과 B열이 같은지 확인하는 수식으로 아래와 같은 수식을 걸어볼 수 있습니다.

(수식 '=' 활용)

이렇게 수식을 걸게 되면, 빨강=빨강, 노랑=노랑 인 부분은 TRUE, 파랑=빨강인 부분은 FALSE를 보여주게 됩니다. 

수식을 걸어놓은 곳에 포스팅에서 설명드린 조건부 서식을 적용하는 것을 활용할 수 있습니다.

TRUE일 경우, FALSE일 경우 서식이 변경되는 조건부 서식을 적용시키면

이러한 결과물을 만들 수 있고,

​

오류가 있는 부분이 어디인지 시각적으로 쉽게 확인을 할 수 있습니다.

조건부서식을 활용하여 쾌적한 회사생활 합시다!

안녕하세요! 김대리입니다.

이번에는 처음보는 엑셀 파일을 접했을때 많이 사용하는 기능에 대해서 얘기해보겠습니다.

​

셀에 입력된 수식을 하나하나 A2... H25... 따라가기 힘들때 사용할 수 있는

"참조하는 셀 추적 / 참조되는 셀 추적" 입니다.

먼저 이러한 수식을 걸어둔 엑셀파일이 있다고 가정해보겠습니다.

(실무에서 만나는 파일은 더 복잡하겠네요!)

해당 파일은 위와 같은 결과물로 보이게 될 것입니다.

마케팅팀 A형 인원수를 나타내는 C4 셀의 값은 4 입니다.

엑셀파일의 어디에선가 C4셀을 재료로 사용하고 있으면 그 셀을 찾아주는 기능이

"참조하는 셀 추적"

​

입니다.

'(이 셀을)참조하는 셀을 추적한다' 라고 받아들이면 될 것 같습니다.

먼저 C4셀에 커서를 올린 뒤

수식-수식분석에 있는

 "참조하는 셀 추적"을 누르게 되면 

위처럼 C4셀을 재료로 사용하는 셀을 보기 쉽게 화살표로 보여줍니다.

"참조되는 셀 추적" 기능은 반대의 기능입니다.

해당 셀의 재료들을 찾는 기능입니다.

예를들어 총 인원수를 나타내는 J5셀의 재료는 G5, G6일 것입니다.

J5 셀에 커서를 올린 뒤 

수식-수식분석에 있는

"참조되는 셀 추적"을 누르게 되면

위처럼 J5셀의 재료들을 찾아 보여주게 됩니다.

이 참조하는 셀(참조되는 셀) 추적하기 기능을 한참 사용하게 되면, 

이렇게 복잡한 화살표들이 남게 됩니다.

이럴때는 오히려 셀의 관계를 보기 어렵기 때문에

수식-수식분석에 있는

연결선 제거를 눌러주시면 파란색 화살표가 사라지게 됩니다. :)

​참조하는 셀 추적/ 참조되는 셀 추적 기능을 사용하여 회사생활에 도움이 되시기를 바랍니다!

※ 참조되는 셀 추적하기 기능 외에, 해당 셀에 입력된 수식의 대상을 찾아가는 기능이 있습니다.

그 기능은 "CTRL+[" 입니다.

위의 파일을 예로 들어, J5에 커서를 올린뒤 CTRL+[를 누르게 되면 

J5 셀을 구성하는 G5, G6셀이 선택되게 됩니다.

또한, G5에 커서를 올린뒤 CTRL+[를 누르게 되면

G5 셀을 구성하는 C4:C7셀이 선택되게 됩니다.

편하신 셀 추적 기능을 사용하셔서 도움이 되시기를 바라겠습니다. :)

안녕하세요! 김대리입니다.

​

​
이번에는 엑셀의 사용을 조금 더 쾌적하게 할 수 있는

"빠른 실행"에 대해서 이야기해보려고 합니다.

빠른실행은 엑셀의 단축키와는 조금 다른 역할을 합니다.
예를 들어 "CTRL키"와 "숫자1"을 함께 누르면(CTRL+1) '셀서식 메뉴'를 불러오게 됩니다.

이것이 '단축키'입니다.

하지만 빠른실행은 엑셀 메뉴 좌측 상단에

이러한 모습으로 등록시켜 놓을 수 있습니다.

등록을 시킨 후 ALT 키를 누르면 

이러한 모습으로 숫자가 뜨게 됩니다.

​

​
알트를 누른 후 저 숫자들이 뜬 뒤 해당 숫자를 누르면 그 기능이 바로 실행됩니다.

이것이 '빠른 실행'입니다.

어떻게 보면 빠른실행과 단축키는 똑같다고 할 수도 있고, 다르다고 할 수 도 있습니다.

​사용하는 입장에서 본다면 엑셀을 더욱 쾌적하게 쓸 수 있게 해준다는 점에서

동일한 효과를 가져올 수 있겠네요. :)

그러면 처음 '빠른실행'을 등록하는 방법에 대해 설명드리겠습니다.

엑셀 좌측 상단의 위와 같은 버튼을 누르신 후

기타명령(M)을 선택하시면

위와같은 Excel 옵션 설정으로 넘어가게 되고,

'모든 명령'을 선택하시면 빠른 실행에 설정할 수 있는 모든 메뉴를 볼 수 있게 됩니다.

​

​

저는 빠른실행 메뉴에
틀고정, 가운데 맞춤, 병합하고 가운데 맞춤, 쉼표 스타일,

​참조되는 셀 추적, 참조하는 셀 추적, ​모든테두리
 기능을 추가해보도록 하겠습니다.

우측의 화살표로 순서를 조정할 수 있으며, 해당 순서에 따라 배정되는 빠른실행 번호가 달라지니 사용해보신 뒤 편하신대로 설정하시면 됩니다.

​

​

​

확인을 누르면

이렇게 빠른실행 메뉴가 추가가 됩니다!

그러면 빠른 실행을 사용해볼까요?

​
                                                                                                ①기능을 적용하고 싶은 셀을 선택한 뒤

                                                                                                ②ALT를 누르고

                                                                                                ③빠른실행 번호를 누르면

​
​해당 기능이 바로 실행됩니다.

​

예를 들어,
선택한 셀의 모든 테두리에 실선을 칠하고 싶다면, 

셀을 선택한 뒤

ALT키를 누르고 '모든 테두리'기능의 빠른실행 번호인 숫자8을 누르면

이렇게 해당 기능이 실행됩니다.

'빠른 실행'은 엑셀을 훨씬 쾌적하게 사용하는데 도움을 줍니다.

다들 자주 사용하는 기능을 빠른실행에 등록하셔서 조금은 편해지셨으면 좋겠습니다. :)

여러개의 액셀파일을 사용하다 보면 종종 생기는 문제가 있습니다.

숫자 데이터를 복사해서 가져다 써야하는데 아래 스크린샷처럼 

느낌표 " ! "가 뜨며 숫자가 텍스트로 복사되는 경우가 있습니다.

(텍스트 형식으로 저장된 숫자)

아래와 같이 데이터가 복사될 경우 수식 또는 함수에서

해당 값을 숫자로 인식하지 못하여 결과물에 큰 오류가 생기게 됩니다.

아래처럼 값이 입력될 경우 "표시형식" 에서 "숫자"로 변환을 하더라도 

오류가 발생한 채로 존재하게 됩니다.

데이터의 양이 적다면 아래와 같이 셀편집으로 들어가서 엔터를 치게 되면

​

​

​

​아래 처럼 숫자로 인식이 되며, 오류표시는 사라지로 수식에서도 제대로 된 값을 사용하게 됩니다.

​

예제에서는 값이 14개 밖에 되지 않기 때문에 셀편집으로 수정하여도 큰 시간이 걸리지 않지만,

수백, 수천행을 편집하게 될 경우 매우 큰 노동시간을 필요로 하게 됩니다.

​

​

​

한번에 해당 오류를 수정하는 방법은 아래와 같습니다.

​

텍스트로 입력되어있는 부분을 모두 위와 같이 선택한뒤,

데이터 - 데이터도구 - 텍스트나누기 기능을 선택합니다.

(해당 기능은 한 행에 여러개의 데이터가 합쳐져서 들어갔을 경우 해당데이터를 나눠주는 기능을 하지만, 지금의 우리에게는 필요가없으므로)

아무 것도 선택하지 않고 마침(F)를 선택합니다.

​

위와 같이 모든 값이 숫자로 변환이 되었고, 수식, 함수의 영향을 제대로 받게 됩니다.