퇴근이 빨라지는 Excel, Excellator

폴더에 있는 파일 중 이름을 엑셀로 가져올 파일을 모두 선택

> Shift를 누른 상태로 우클릭

> 팝업 매뉴 중 경로로 복사 선택

> 엑셀에 붙여넣기

경로까지 복사 되니까..

엑셀에서 

데이터 > 텍스트 나누기 > 구분기호로 분리됨 > 기타 선택 후 \(돈표시) 입력

2강에서 새 통합문서도 만들어 보았고, 데이터도 입력해 보았으니
이제 워크시트를 조작해 봅시다

워크시트를 삽입해보자
작업을 하다보면 워크시트가 추가로 필요할 때가 있다.
데이터가 있는 워크시트와 차트가 있는 워크시트를 나누거나, 
데이터와 피벗테이블을 나누거나…
지점별로 데이터를 나누어 작성하거나…
반별로 학생 데이터를 작성하거나..

아무튼 워크시트가 여려개 필요한 경우 아래와 같이 추가할 수 있다.
방법1) 메뉴기능을 사용한다. 홈>삽입>시트삽입 메뉴
방법2) 워크시트 우측 끝에 있는 + 버튼을 이용해서 워크시트를 삽입한다.

보통은 방법2를 사용한다.

워크시트를 이동해보자
아래 그림처럼 1학년 1반이 오른쪽에, 2반이 왼쪽일 때
우리는 대부분 왼쪽에 1반이 있는 것을 좋아한다…
워크시트 자리를 바꾸고 싶다.

어쩌지…
그냥 워크시트 이름을 클릭&드래그 해보시라..
원하는 위치에 가져다 놓을 수 있다.

워크시트를 통으로 복사하고 싶으면?
예를 들어 이런 경우가 있다.
하나의 시트에 엄~~~청 복잡한 양식을 만들었다.
두번은 만들 수가 없다..
근데 똑같은 시트가 여러개 필요한 상황…

또는

엄~~~청 복잡한 데이터가 있는데
원본에서 편집하고 분석하고, 테스트 하기에는 
원본 데이터를 보호하고 싶다.
그래서 원본 데이터와 똑같은 데이터가 추가로 필요한 상황….

그럴땐 이렇게 하면 된다.
복사할 시트를 클릭한 상태로 ctrl 버튼을 누르면, 커서 부분에 +아이콘이 생성된다.
이제 그 상태로 원하는 위치에 끌어다 놓으면 똑같은 시트가 생성된다.
시트 이름은 원래 시트 끝에 (2)라고 숫자가 붙으며, 하나 더 복사하면 (3)..등으로 순서대로 이름이 변경된다.

퇴근이 빨라지는 Tips!
복사가 안되는데요?
안되는 경우가 있다. 
원본시트를 클릭하고 있으면 왼쪽이나 오른쪽에 작은 검정색 역삼각형 모양이 생기는데
처음 생긴 방향으로 원본시트 바로 옆으로 끌고가면
복사가 되지 않는다.
검정색 역삼각형이 생긴 반대방향으로 끌고 가거나
가장 명확한 방법은 검정색 역삼각형 방향 상관 없이 시트를 하나 건너뛰면 확실하게 복사 된다.

워크시트 삭제는 어케 하나요?
간단하다. 
삭제할 시트 이름을 우클릭 해보시라..
여러 메뉴들이 나오는데, 그중 삭제를 클릭하면 
진심 삭제할거냐고 물어보고, 네~~ 하면 삭제해준다.

삭제전에 잠깐..아래 Tips 먼저 보고 삭제하시길...

퇴근이 빨라지는 Tips!
삭제 전에 잠깐…
워크시트 삭제는 Ctrl+Z로 되돌릴 수 없다.
만약 1시간 정도 고민고민 해서 작업한 파일인데…
아직 저장도 안했는데…
삭제하면 안되는 시트를 삭제 했다면???
낭패다….
하지만 방법은 있다.
지금 저장중인 파일은 다른이름으로 저장(파일B) 후
작업 전 파일(파일A)을 다시 열어서 필요한(삭제한) 시트를 다른이름으로 저장한 파일로 옮기면 된다.

이때 파일A의 필요한(삭제한) 시트를 우클릭 하면 이동/복사라는 메뉴가 있는데
클릭해보시라. 파일B로 복사할 수 있는 기능이 있다.
물론 파일B에 새 시트를 삽입하고
파일A에서 필요한 데이터를 복사>새 시트에 붙여넣기 하는 방법도 있다.

그래서 엑셀로 뭘 할까…
엑셀로는….못하는 게 없다..는게 내 생각이다.
|문서를 만들 수 있다.
워드나 한글 프로그램으로 할수도 있지만
엑셀로도 문서를 만들 수 있다.
특히 표 기반의 문서를 만드는데는 아주 탁월하다
세금계산서 같은 형태의 문서는 엑셀로 만들어 사용하는 경우가 아주 많다.

|기본적으로 데이터 분석 툴이다.
엑셀은 데이터를 분석하고, 시각화(차트 등)를 하는데 최적화 되어있는 프로그램 이다.
엑셀을 이용해서 사칙연산은 물론이고, 
엑셀에서 기본적으로 제공하는 1천여개 이상의 함수를 사용해서 
다양한 데이터 분석을 수행할 수 있다.
또한 피벗이라는 강력한 분석툴을 제공하고 있으며
다양한 형태의 차트를 이용할 수 있다.

|그냥 계산기로도 사용할 수 있다.
함수와 사용방법이 동일한데, 
그냥 단순한 계산기로도 사용할 수 있다.

|데이터 분석을 자동화 할수도 있다
엑셀에는 매크로 기능과 VBA 기능을 제공한다.
매크로는 한마디로 말하면, 내가 하는 행동을 녹화해 그대로 반복하게 하는 기능 같은 것을 말하는 것으로, 엑셀에서 간단하게 매크로 기록하기 기능을 사용해서 단순반복 업무를 자동화 할 수 있고,
나아가
초등학교 때 컴퓨터 학원에서 basic 프로그래밍을 배운 수준의 코딩 능력이 있으신 분들은 
VBA를 사용해서 더 어려운 작업도 프로그램으로 수행 시킬 수 있다.
VBA는 실제로 Visual Basic Application의 약어이며, Basic 언어 기반으로 만들어진 자동화 도구이다.

위에서 엑셀 함수가 1천여개나 된다고 했는데
엑셀에는 사용자가 원하는 함수를 작성하는 ‘사용자정의 함수’ 기능이 있어서
실질적으로 사용할 수 있는 함수의 개수는 무한하다고 할 수 있다.

지식in 질문에 expert님이 답변해주신 내용을 이해하고,

나중에 활용하고자 정리하는 내용임

데이터는

품목 | 날짜 | 평가결과

도출하고자 하는 결과물은

          날짜1      |    날짜2   | 날짜3

품목  평가결과 | 평가결과 | 평가결과

형태임

평가결과 = index(데이터의 평가결과열,match(1,index((데이터의 품목열 = 결과물의품목 값)*(데이터의 날짜열 = 결과물의 날짜값)))

match함수의 두번째 항목이 배열이라는 점을 활용 하는 거고,

= 연산자는 같으면 1, 다르면 0을 반환하는 연산자임

즉 품목비교는 데이터의 품목열에서 특정  품목값과 같은 경우 1 아닌 경우 0을 가진 배열을 만들고

날짜비교는 데이터의 날짜열에서 특정 날짜값과 같은 경우 1 아닌 경우 0일 그진 배열을 만들어서

두 배열을 곱해서 index에 넣으면 품목과 날짜가 동시에 일치하는 위치만 1이 되는 배열이 만들어짐

이때 match(1,상기배열)로 1과 동일한 위치를 반환하고

다시 index에서 반환한 위치에 있는 값을 찾아 결과로 보여주는 것임 

본 강좌를 진행하기가 너무 어렵네요
아무래도 전문가가 아니고,.....
회사업무에...퇴근도 늦고...
회사에서는 파일 업로드도 안되고...

사실, 웹툰작가처럼, 몇개의 강좌를 미리 작성해 놔야 할텐데...
강좌를 작성할 시간이 부족해요...

그래서 돌아온 퇴근이 빨라지는 Tips 시리즈

엑셀파일 용량이 비정상적으로 커서 실행시 시간이 많이 걸려요...ㅠ

이런 경우가 종종 있죠?
데이터 양이 많지도 않고, 함수가 많이 포함된 것도 아니고
도대체 왜왜..
파일용량이 큰지 알수가 없을 때가 있어요

제가 파악한 원인은 두가지 정도 입니다.

데이터 삭제가 완전하지 않은 경우(찌꺼기 데이터)

예를 들어볼게요..
매월 데이터가 누적되는 형태의 자료를 관리하고 있어요
그럼 1월 ~~~~ 12월로 갈수록 데이터가 많아 지겠죠...
그러던 중, 12월~1월로 넘어가면서 새로운 데이터 관리를 위해 이전 데이터를 삭제하고
1월 데이터를 붙였어요...
헛..그런데 용량은 12월 파일보다 줄어들지 않아요..ㅠㅠ

이런 경우는 이전데이터를 delete 버튼으로 지워서 그런거에요
엑셀에서는 데이터를 delete로 지우면 그 자리에 데이터가 있었다는 것을 인식하고 있는 것 같아요
그래서...
수만줄의 데이터를 싸~~악 delete로 지우고
몇십줄을 다시 추가하면...
아래에 수만줄의 데이터가 삭제된 것으로 보지 않아요

이걸 어떻게 파악하냐...
모든 데이터 삭제 후 몇십줄을 붙인 상태에서 데이터 최 하단으로 커서를 내려보면
우측 스크롤바가 아래에 붙어 있어야 되는데,
상단에 위치하고 있는 경우에요...
그럼..딱 보기에도 아래에 데이터가 더 있나 싶죠..

[그림. 데이터 끝에 있는데 스크롤바가 다 내려와있지 않은 상황]

그림을 넣으려고 했는데, 위에서 말씀 드린 것 처럼 회사에서는 파일 업로드가 안되고

집에와서 재현해 보려고 하니 오피스365(저희집은 Family 버전) 에서는 해당 문제가 해결된 것 같네요

다시 말씀 드리면...재현이 안되서 그림을 첨부할 수 없..ㅠ

이런 경우 해결방법!
데이터 하단 빈공간 전체를 선택하고
마우스 우클릭 > 삭제 버튼(즉, 행삭제)을 눌러 삭제하세요...
그래도 스크롤바가 그대로 인데요??라고 생각할 수 있지만..
저장하시면 스크롤바 위치가 바뀌고, 용량도 똿...줄어들어 있을 거에요

추가 Tip! 위 박스안에 행삭제를 쉽게....하는 방법
원래 행(또는 열) 선택 > 마우스 우클릭 > 마우스 이동 > 삭제 클릭 하던 것을
쉽게 행(또는 열) 선택 > ctrl - (컨트롤과 마이너스 키) 로 할 수 있답니다.

두번째 케이스는

불필요한 서식이 삽입되어 있는 경우

아래 두 그림을 보시면 똑같죠...
근데 파일 용량은 크게 차이가 나고 있어요

[그림. 똑같은 내용인데, 파일 용량이 차이나는 그림 3개]
(요것도 오피스365에서는 재현이 안돼요..

어쩌면 다른 원인이 있을 수도 있구요...

아무튼 저는 아래와 같이 해결 한답니다~)

스크롤바 위치도 동일해서 원인을 알기가 어렵죠...

제가 오늘 업무중에 만난 상황인데요
메일에 첨부하려고 하니 용량 초과...
용량 확인하니..200M....헐..엑셀 파일 하나가 200M

그래서..위에 기술한 찌꺼기 데이터가 있나 싶어 스크롤바 확인하니
찌꺼기 데이터는 없음..
그럼 왜 그러지?

확인해보니, 엑셀시트 전체의 셀 색상이 흰색으로 채워져 있더라구요
셀 색상을 색없음으로 변경 후 저장하니
최종 용량은 747KB...
한250배 이상 차이가 나네요..

이렇게 불필요한 부분(찌꺼기 데이터, 서식 등)을 삭제해서 용량을 줄이면 파일 오픈 시간 단축, 저장할 때 시간 단축...즉 퇴근이 빨라집니다~~^^

엑셀로 작업을 하다보면
파일을 저장해야 하는데,
그냥 저장하기 두려워 지는 경우가 있다.

원본 파일은 그냥 두고 싶을 때,
내가 작업한 결과에 대한 확신이 없을 때 등등

이럴 때 다른이름으로 저장하게 되는데...
이게 좀 번거롭다.
상단 메뉴바에서 [파일] > [다른이름으로 저장] > 저장할 경로 찾고 > 이름 바꾸고 > 저장
[그림]

이걸 한번에 할 수 있는 단축키가 있다...

바로바로...
F12

다른이름으로 저장이 필요할 때
F12를 누르면
현재 파일이 위치한 경로에서 다른이름으로 저장 대화창이 바로 똿~~
[그림]

이거 별거 아닌거 같은데, 엄청 편리함

오피스 제품군에서 모두 적용 가능함.

통합문서 만들기

1. Excel을 엽니다.
2. 빈 통합 문서를 선택 하거나, Ctrl+N을 누릅니다.

[새로만들기 이미지]

퇴근이 빨라지는 Tips! 

ㅇ 엑셀을 열면 새로 만들기 화면을 띄우는데, 거기서 빈 통합문서를 선택하라는 뜻이다. 

새로만들기 화면에는 빈 통합 문서 외에도 여러가지 통합문서들이 있는데, 업무용으로, 개인용으로 많이 사용하는 서식을 작성해서 제공하는 것이니 한번씩 사용해보시기 바란다.

ㅇ 엑셀을 여는 방법으로 이미 만들어져 있는 엑셀파일을 더블클릭하는 방법도 있다. 해당 파일을 편집하려고 하면 이 방법이 더 편리하다.

ㅇ 꼭 새로만들기 화면이 아니더라도 엑셀이 실행된 상태에서 아무데서라도 Ctrl+N을 누르면 새 통합문서를 띄운다. 

데이터 입력

데이터를 수동으로 입력하려면

1. 입력하고자하는 셀을 선택하고 텍스트나 숫자를 입력합니다.
2. Enter 또는 Tab키를 눌러 다음 셀로 이동합니다.

퇴근이 빨라지는 Tips! 

1. 다음 셀로 이동할 때 Enter는 아래방향으로, Tab은 우측방향으로 이동한다.
2. Shift와 함께 사용하면 반대방향으로 이동한다. 즉 Shift+Enter는 위쪽, Shift+Tab은 좌측으로 이동
3. 약간 다른 얘기지만, 워크시트를 이동하는 단축키도 있는데, Ctrl+PgUp은 좌측 워크시트로 이동, Ctrl+PgDn은 우측 워크시트로 이동한다.

데이터를 반자동으로 입력하려면(채우기핸들 사용하기)

1. 연도, 월, 숫자, 요일 등 미리 지정된 형식의 데이터를 입력한 후
2. 채우기핸들로 아래 또는 우측 방향으로 끌고가면
3. 해당 데이터의 순서대로 채워진다.

퇴근이 빨라지는 Tips!

ㅇ 미리 지정되지 않은 값들도 데이터 계열로 입력할 수 있다. 

    예를 들어 우리 회사의 지점이 강남지점, 강북지점, 분당지점, 수원지점, 원주지점이 있다 치고, 강남지점을 직접 입력하고 채우기핸들로 강북~원주까지 자동 입력되도록 하려면..

1. 데이터 > 정렬 > 팝업창에서 정렬기준에 현재 오름차순으로 되어있는 부분을 클릭 후
2. 사용자 지정 목록을 선택하고 아래와 같이 입력, 추가 하면 된다.

내용출처: Excel에서 통합 문서 만들기 (microsoft.com)

앞으로 강좌의 원활한 진행을 위해 몇가지 용어를 설명하는 것으로 강좌를 시작하려고 한다.

여기서 엑셀의 모든 용어를 정의할 생각은 1도 없고, 그렇게 할 수도 없다.

사실 용어가 중요한 것은 아니라고 생각한다.

왜냐하면, MS지원사이트를 보면 나도 잘 알지 못하는 용어들이 너무나 많고, 그런거 하나 몰라도 여태 일만 잘 해왔기 때문이다.

하지만. 강의를 진행하는데 있어서 글을 쓰는 사람도 편하고, 읽는 사람도 쉽게 이해하기 위해서는

용어정의부터 시작하는 것은 어쩔수 없는 일인 것 같다.

일상적으로 사용하는 용어로 통일하면 좋겠는데

일단은 공식용어를 설명하고 용어 끝에 우리가 사용할 용어, 또는 필드에서 사용하는 용어를 덧붙이고,

앞으로의 강좌에서는 혼합해서 사용하게 될 것 같다.

그럼 시작해보자

통합문서(workbook)

하나의 파일이라고 생각하면 된다. 통합이라고(영어로 book이라고) 한 것은 하나의 파일안에 여러 개의 워크시트( worksheet)를 포함할 수 있기 때문이다.(뇌피셜임)

현장에서는 통상 파일이라고 해도 무방하다.

워크시트(worksheet)

 한장의 종이라고 생각하자, 하나의 페이지라고 생각해도 되고, 1페이지에 출력할 수 있는 단위라고 생각할 수도 있다. 

일반적으로는 그 이름을 줄여서 sheet 또는 시트라고 부른다.

통합문서에 워크시트가 여러 개 포함될 때에는 통합문서 하단에 탭 형태로 워크시트 이름을 표시하여 워크시트를 구분할 수 있다.

행(Row), 열(Column), 셀(Cell)

• 행(Row): 가로줄이다. 각 행 좌측에 행 이름이 숫자로 붙어있고 행을 부를때는 1행, 2행 등 숫자로 부른다.
• 열(Column): 세로줄이다, 각 열의 상단에 열 이름이 알파벳으로 붙어있고, 열을 부를때는 A열, B열 등으로 부른다.
• 셀(Cell): 행과 열이 교차하는 빈칸 하나를 셀이라고 부른다. 셀에는 주소가 있는데(셀주소) 예를 들어 A열과 1행이 교차하는 셀의 주소는 A1셀이다.

위 그림에서 좌측에 나열된 1, 2...등의 숫자는 행번호이고,

위쪽에 나열된 A, B... 등의 알파벳은 열이름이다.

하나의 워크시트에는 1,048,576개의 행(row)과 16,384개의 열(column)이 있어서

행과 열이 교차하는 지점인 셀(Cell)의 개수는 17,179,869,184개(약 171억8천만개) 이다..

지겨운거 안다..

미안하지만 3개만 더 하고 가자..

채우기핸들

셀 값을 자동으로 채워주는 기능을 하는 것으로 선택한 셀 우측하단에 조그만 네모모양이다.

예를 들어 ‘월요일’이라고 입력하고 채우기핸들을 잡고 드래그 하면 화요일, 수요일 등으로 채워진다.

자동채우기 옵션

채우기핸들로 값을 입력하는 경우, 자동으로 채우는 옵션을 선택하는 버튼이다.

자동채우기 했을때만 나오는 버튼으로

월요일을 입력하고 채우기핸들을 이용해 값을 입력했을 때 화요일, 수용일 등으로 완성되기를 원할수도 있지만,

월요일만으로 채워지기를 원할 수도 있다.

이런 옵션을 선택할 수 있는 기능

붙여넣기 옵션

자동채우기 옵션과 비슷한 기능으로 복사한 값을 붙여넣을 때 나오는 옵션버튼이다.

이 버튼을 클릭하면 복사한 값을 어떤 형태로 붙여넣을지 선택할 수 있다.

예를 들면..

값만 붙여넣기, 수식만 붙여넣기. 서식만 붙여넣기, 복사한 값과 곱해서 붙여넣기, 행열 바꿔 붙여넣기 등등

쓰고 있는 나도 지겹다. 

용어정의는 여기까지 하도록 하고, 

앞으로 새로 나타나는 용어는 그때그때 설명하기로 하자

몇 년 전 야심차게 시작했던 강좌가 흐지부지 되었다

사실 강좌라는게 전문가가 수년간 힘들게 준비해서 진행해도 쉽지 않은 것일텐데..
전문가도 아닌 사람이 아무런 준비없이 시작했기 때문에
어쩌면 흐지부지 될 수 밖에 없는 상황이었던 것 같다.

그 후 몇 년이 흘렀고,
나는 그동안 전문가가 되..지 않고 그대로지만...
그래도 다시한번 시작해보려고 한다.

이 강좌는 Microsoft 지원 사이트의 내용을 기반으로 작성될 것이고,
상기 사이트에서 가져온 내용인 경우에는 해당 강좌 끝부분에 출처를 명시할 것이다.

MS 지원사이트만 배껴오면 누가 이 강좌를 보겠나 싶어
수년간 엑셀로 업무를 하면서 터득한 노하우나 알아두면 편리한 기능들이 있으면
내용을 덧붙여 작성할 계획이다.

해당 내용은 퇴근이 빨라지는 Tips! 라고 표기할 예정이다.​

자..파이팅 해보자~