본문 바로가기

엑셀팁20

엑셀TIP] Alt + Enter로 여러 줄로 입력된 데이터 나누기 엑셀에서 한 셀에 데이터를 입력하다 "Alt + 엔터키"를 누르면 여러 줄로 입력할 수 있다. 여러줄로 입력할 수 있는게 편리하긴 하지만 데이터를 관리하려면 여러 열로 나눠주는게 맞다. 아래 그림처럼 우편번호, 주소, 전화번호가 모두 한 셀에 입력된 데이터를 어느 친절한(?) 직원으로부터 전달받았다고 가정하고 한 셀에 여러 줄로 입력된 내용을 분리해서 나눠보자. 데이터들을 셀블록으로 잡고 [데이터 탭 - 텍스트 나누기]를 클릭한다. - [Excel] - 엑셀] 한 셀의 내용 나누기 - 텍스트 나누기 위 그림을 자세히보면 데이터들이 일정한 구분기호로 구분되어 분리가능하다는 것을 알 수 있다. [다음] 버튼을 클릭한다. 텍스트 마법사 3단계 중 2단계.. 2016. 9. 5.
엑셀TIP] 셀 병합 해제하고 데이터 일괄 입력하기 엑셀을 잘 활용하기 위한 방법으로 가능하면 셀 병합을 하지 않도록 권한다. 꼭 필요하다면 마지막 보고서 단계에서만 사용하길 권장하지만... 어찌 일이라는게 내 맘대로만 되는가. 병합한 셀을 풀고 똑같은 데이터를 반복 입력해야 할 일이 생기기 마련이다. 이거라도 조금 편하게 입력해보자. ^^ 아래 그림을 보자. 보고서용으로 근무부서를 셀병합 해놨는데 다른 데이터로 사용하거나 피벗테이블을 만들 필요가 있어서 셀병합을 해제하고 다시 원래의 데이터를 입력해야 하는 상황을 가정해보자. 일단 셀 병합은 푼다. 블록을 잡은 상태에서 메뉴에서 [병합하고 가운데 맞춤]을 클릭하면 된다. 이렇게 블록이 선택되어 있는 상태 그대로 진행한다. (혹시 블록이 해제되었으면 셀 빈칸 전체를 블록으로 잡는다) 블록이 선택된 상태에서.. 2016. 8. 23.
엑셀TIP] 소수점 부분을 한글로 표기하기 블로그 댓글 중 소수를 한글로 표기하는 방법에 대한 질문이 있었다. "123,123.52"를 "일십이만삼천일백이십삼점오이"로 변환하는 방법에 대한 질문이었는데 그 때는 잘 몰라서 그랬는지, 잘못생각했는지 상당히 복잡하게 답변을 달아놨더랬다. 소수점을 기준으로 정수부와 소수부를 나누고 각각 한글로 변환한 다음에...... 쩝... 오늘 다시 이것저것 실험해보다 간단한 방법을 알게되서 여기에 정리한다. ^^;; 먼저 text()함수와 서식 [dbnum4]를 이용해서 소수부분을 읽고 .(점)이 찍히는 것만 substitute()함수로 "점"이라는 텍스트로 바꿔주는 방법을 사용했다. - 엑셀] 숫자 표시하기 (한글로, 한자로, 자리 맞춰서)- 엑셀] 텍스트 바꾸기 - REPLAC.. 2016. 8. 15.
엑셀TIP] 여러 파일 한꺼번에 열기 여러 개의 엑셀 파일을 동시에 작업하는 경우 한꺼번에 파일을 열 수 있는 방법. 한 꺼번에 파일을 여는 방법 1 탐색기에서 동시에 열고 싶은 파일들을 선택한다. (서로 떨어져 있는 파일들은 Ctrl키를 누르면서 클릭하면 되고, 연속적으로 붙어있는 파일들은 파일 리스트의 처음과 마지막을 Shift키를 누르면서 클릭하면 된다) 이렇게 파일들이 선택된 상태에서 엔터를 똭~ 쳐주자. 각각의 파일이 모두 열린다. 한 꺼번에 파일을 여는 방법 2 이미 엑셀에서 작업중에 다른 파일들을 열어야 한다면 [파일 - 열기]를 선택하면 탐색기와 동일한 창이 열린다. 이 상태에서 위 첫번째 방법과 동일하게 Ctrl 키나 Shift키를 누르고 필요한 파일들을 선택한 다음 [열기] 버튼을 클릭하면 된다. 한 꺼번에 파일을 여는 방.. 2016. 7. 6.
엑셀TIP] 행 / 열 숨기기로 필요한 부분만 남기기 엑셀로 보고용이나 배포용 자료를 만들 때 사용할만한 팁. 데이터가 들어있는 부분만 남기고 나머지 부분은 숨기기로 없애서 깔끔하게 만들어보자. 빠른 작업을 위해 단축키를 사용해서 작업해보자(이걸 마우스로 하면 상당히 번거롭다. ^^;) 아래 그림에서 보고서 부분만 남기고 숨기려고 한다. H열부터 숨겨보자. H열 아무곳이나 클릭한 후 [Ctrl + Shift + → ]를 눌러서 H열 이후 모든 열을 선택한다. 오른쪽 끝까지 모든 행이 선택되었다. 이 상태에서 열 숨기기 단축키 [Ctrl + 0] 을 누른다. H열부터 모든 열이 숨기기 상태가 되었다. 이제 10행부터 아래쪽 행들을 숨겨보자. 10행의 아무 셀이나 클릭하고 [Ctrl + Shift + ↓]를 눌러서 10행 이후 모든 열을 선택한다 이 상태에서 .. 2016. 7. 5.
엑셀TIP] 자동필터에도 변경되는 일련번호(연번) 붙이기 엑셀 보고서에 일련번호(연번)을 주면서 자동필터나 숨기기로 행이 변경되더라도 자동으로 연번이 수정되도록 함수를 이용해보자. AGGREGATE 함수를 이용해서 숨겨진 행은 무시하고 계산하도록(counta) 해보자. 첫번째 인수인 기능(function)은 "3 - COUNTA"를 선택하고 두번째 인수인 옵션은 "5 - 숨겨진 행 무시"를 선택한다. 세번째 참조값은 "$B$2:B2"를 입력한다 (절대주소 B2로부터 카운트) 그리고 자동채우기 핸들을 제일 아래까지 드래그한다. 자동필터로 행정, 기술직만 조회해도 연번이 자동으로 조정된다. 연번 수정은 중간에 있는 행을 숨기기해도 자동으로 조정된다. - 엑셀] 병합된 셀에 일련번호 넣기 - 엑셀] 에러(오류) 값 제외하고 계산하기. (Aggrega.. 2016. 6. 30.
엑셀TIP] 병합된 셀에 일련번호 넣기 엑셀을 이용해서 데이터를 관리할 때는 상관없지만, 보고서 등을 만들 때 중복되는 값은 병합해서 표를 작성하는 경우가 많다. 이 경우 연번(일련번호)을 작성할 때의 방법에 대해 알아본다. 아래 그림같이 보고서를 만들려고 할 때, 병합한 셀마다 행의 숫자가 다르다. 1번을 입력한 셀의 오른쪽 아래 자동채우기 핸들을 끌어서 작성해보려고 하면 다음과 같은 에러가 나타난다. 즉, 바꿔 말하면 병합한 셀의 행 숫자가 모두 같다면 자동채우기로 작성할 수 있다는 뜻이다. 아래 그림처럼 병합한 셀이 모두 3행씩 동일한 경우라면 자동채우기 핸들로 일련번호를 채울 수 있다. 다시 앞의 조건으로 돌아와서 COUNTA함수를 이용해서 일련번호를 작성해보자. 일련번호가 들어가는 전체 범위를 블록으로 잡고 수식을 "=COUNTA($.. 2016. 6. 29.
엑셀TIP] 화살표로 화면 이동시키기 가끔 주변 사람들에게 듣는 질문 중에 자주 듣는 질문이 엑셀을 사용할 때 키보드 화살표로 셀 이동이 안된다는 질문이다. 화살표를 움직여도 셀 위 아래로 움직이지 않는다고... 그러면서 덧붙이기를 화살표 키를 누르면 화면이 흔들린다고... ^^;; 범인은 스크롤락 키이다. 평소 거의 사용할 일 없는 기능키. Scroll Lock. 보통 F12 오른쪽 PrtSc(프린트 스크린), ScrLK(스크록 락), Pause(포즈) 삼총사 중 가운데 있는 키이다. 경험상 키보드 입력 작업을 할 때 그 아래에 있는 Ins 키나 Home 키를 누를 때 같이 눌려서 깜놀하게 하나보다. 엑셀에세 스크롤락 키가 ON 되어 있으면 화살표 키를 눌렀을 때 셀 단위로 움직이는게 아니라 활성화된 셀은 움직이지 않고 고정된 상태에서 화.. 2016. 6. 10.
엑셀TIP] 떨어져 있는 셀에 같은 값 입력하기 엑셀에서 연속해 있지 않은 셀에 동일한 값을 빠르게 입력하는 방법에 대해 알아본다. 그냥 하나씩 입력한다고 해도 별 문제 없겠지만, 효율성을 생각할 때 한 번 알아두면 두고두고 유용하게 사용할 수 있는 방법이다. 교육수료일자가 없는 셀에 "미수료"라고 입력해야 하는 상황을 가정해보자. 아래 알아보는 두 가지 방법 중에서 상황에 따라서, 또는 자신에게 익숙한 방법으로 사용하면 될 듯하다. Ctrl + Enter 현재는 빈 칸인 떨어져 있는 셀들에 "미수료"라고 입력하는 경우를 생각해보자. 1. 먼저 Ctrl 키를 누른 상태에서 각 셀을 클릭해서 비연속적인 들을 모두 선택한다. 2. 셀들이 선택되어 있는 상태에서 "미수료"를 입력한다. 3. Ctrl + Enter를 누르면 모든 셀에 "미수료"가 입력된다. .. 2016. 1. 2.

티스토리툴바