엑셀 기본적기능 별책부록
uno-weekly를 진행하다 보니 기본적인 기능들의 사용법에 대하여
매번 설명을 하자니 이미 알고 있는 사항을 보는 분들에게 지루할 것 같고
그래서 기본적인 설명을 생략하자니 초보님들은 그냥 억장이 막히고 그래서
별책부록으로 기본적인 사항들을 반복없이 이곳의 화일들을 보시고 익히시면서
주간지를 보시는 것이 효율적일 것 같다고 생각하여 이 페이지를 마련했다
지난번에 있었던 토픽별 인덱스는 주간지 본문과 중복이 되는 것같아서 없애기로 하고
이 페이지의 내용을 차곡차곡 올려 놓도록 하기로 했으니 초보님들은 이곳의 화일로
기본을 익히면서 주간지의 응용문제를 보시면 될 것이다
또한 [워크시트함수]에 있어서는 별도의 [워크시트함수페이지]를 준비하였으니 매일 하나씩
함수를 익혀나가시기 바란다
숫자의 서식 | Number Format
이것은 앞으로 몇일에 걸쳐서 이야기를 하여야 할 것같으니
한번보고 끝나지 않을 것이니..숫자의 서식에 관심이 있으면
알아서 관심이 없다면 문제가 없지만..모르면서 관심이 없으면 엑셀하지 않겠다는 소리!!
아무튼 몇일 계속 될 것이다
엑셀은 눈에 보이는 시트는 실은 두겹(Double Layer)로 되어있다
하나는 눈에 안보이고 하나는 눈에 보이고..
실제 3이라는 정보를 3.00 으로도 표현하고 003으로도 표현하고 원하는데로
표현하는 것이 우리가 눈에 보는 부분의 정보이다
실제값은 3인데 다양하게 표현할수있는 것이다
3000이라는 숫자를 3,000 으로 1000자리로 끊어서 하기도 하고..
그러니 관심을 갖고 마스터를 하셔야 할 것이다
우선 셀에 이런 저런 정보를 주욱 입력하고
입력된 정보를 모두 선택하고 셀서식을 하여 보자
정보가 입력된 범위(숫자이던 문자이던 상관없이..)를 선택후 오른쪽 마우스를 크릭하여
나타나는 단축메뉴의 [셀서식]을 선택하면
나타나는 대화상자에서 아래의 그림과 같이 해보면
입력했던 정보가 모두 사라진다..
사라진것이 아니고 값을 갖고 있는데 눈에 안보이게 처리한 것이다
그럼 세미코론3개의 비밀이 뭔지 궁금해 하시고 내일 또 하자
숫자의 서식에는 크게 두가지가 있다
하나는 엑셀에서 제공해주는 기본적인 몇가지 서식과
사용자가 현장의 조건과 입맛에 맞게 직접 만들어서 사용하는 서식
엑셀에서 제공하는 서식..백분율,시간,날짜등등은 바보라도 그냥 서식하고 싶은
범위를 선택하고 찍으면 된다
이것도 못찍으면 [줘도 못먹는 것]이고..아무튼..
여기에서 이야기하는 것은 사용자정의서식[User Defined Number Format]이다
0값은 나타나지 않게 ,그리고 문자도 나타나지 않게 하고 싶다면
#;#;;
라고 하면 0값과 문자는 모두 안보인다
세미코론을 중심으로 아래와 같이 4개로 구분된다
양수의 값서식;음수의 값서식;0값 서식;문자서식
그래서 아래와 같이
#;#;;
라고 하면 #표시는 숫자표시부호이니 양수와 음수는 있는 대로 나타나게 하고
세번째 0부분을 아무것도 해주지 않았으니 아무표시도 나타나지 않고
네번째 문자부분도 아무 서식도 해주지 않아서 문자가 안보인다
이렇게 4개로 구획하여 각각서식을 해줄수 있는 편리한 기능인 것이다
그럼 숫자와 문자가 들어있는 범위에서 문자가 있는 셀만 보이게 하고
나머지는 전부 안보이게 하고 싶다면 어떻게 할까??
양수서식;음수서식;0값서식;문자서식
에서 양수,음수,0값모두 빈탕으로 주고 문자를 서식하겠다는 싸인은
골뱅이 @를 쳐주면 되는 것이다
문자서식자리에서 빈탕이면 사라지고 @를 주면 문자를 표현해라!!이 소리다!!
이 사용자정의서식을 사용하면
하나의 셀을 다양하게 서식할수 있는 것이다..
0값을 X로 표현하고 싶다고 치자..
그럼 0값일때 서식을 하는 ;;; 세미코론의 3번째에 X로 표현하는 서식을 해주면된다
물론 양수값을 모두 X라고 표현하고 싶다면 세미코론구분의 첫번째에 X라고
표현해주도록 하면 되고..
위의 그림을 예로 하여 각자 연습을 해보셔야 상황에 따라
자유롭게 서식을 할 수 있을 것이다
위의 원칙에 따라서 각각의 숫자의 서식의 부호들을 보면
#표시는 숫자가 있으면 숫자를 그대로 표현한다
0표시는 숫자가 없어도 0을 표시한다 예를 들면 실제의 값이 3인데
서식을 0#이라고 하면 03 으로 표현이 되는 것이다
#.00 이라고 쩜을 찍고 00을 붙였다면 3이라는 숫자는 3.00 이라고 표현된다
%를 서식하면 3이라는 숫자는 숫자에 100을 곱하고 %가 붙는다
3이라는 숫자는 300%로 표현되는 것이다
숫자의 뒤에 빈자를 두개 만들고 싶다면
#__ 라고 언더바를 두개 쳐주면 된다
참조테이블의 의미
엑셀에서는 참조테이블을 참으로 많이 사용하는데
참조테이블이 어떻게 구성되어야 하는지를 잘 모른다
저어기 있잖아요..VLOOKUP으로 참조를 하여 값을 갖여 오는데
테이블에 값이 여러개 있을때는 어떻게 하지요??!@#
이것은 질문 자체가 형성되지 않는 질문이다
참조테이블(Reference Table)은 유일한 값만 모여있는 조견표와 같은 것이니까..
작업테이블(보고서테이블)은 여러개의 값이 당연히 있겠지만
참조테이블은 유일한 정보만 모아 놓은 테이블이고
그렇게 구성하여야 한다
위의 그림같은 원칙을 지켜야 참조함수들이 제 역할을 발휘한다
좀 문짜를 써서 말하자면,,,데이타베이스적으로 말하자면
[기본키값(Primary Key)을 갖고 있는 테이블은 모두 참조테이블로 사용할 수 있다]
는 말이 가장 바른 답이다
논리식,논리값, 이것이 도대체뭔가??!!
613회에서 응용문제를 풀어 드리다 보니까..
SUMPRODUCT함수를 사용하게 된다..
이것을 또 설명해야 하나..말아야 하나..반복하여 설명하지 말고 이곳에 올려야지..
그래서 이곳에 다시 SUMPRODUCT함수와 관련하여 기본개념을 설명해 본다
SUMPRODUCT함수를 활용하려면 가장 기본적인것이 논리식과 논리값을 알아야 한다
그냥 아는 것이 아니고 생각이 그렇게 되어야 한다
그렇지 않으면 SUMPRODUCT응용문제 100번을 해보아도 소용없다
그런데 논리값,논리식이 어려운가??!!쥐뿔 이것처럼 간단한것도 없는데
그렇게 잘 이해를 못하는 것인지..안하려고 하는 것인지..아무튼 논리라는 단어만
들어가면 뻐한 표정들을 짓는다
그렇다면 SUMPRODUCT함수는 죽을때까지 활용못한다
또한 논리값을 먹구 사는 IF함수조차 꺼려한다
그것은 엑셀을 그냥 표작성이나 예쁘게 하려고 한다..
실은 논리값,논리식을 이해 못하면 조건부서식도 못한다
즉 역동적이고 보기 좋은 테이블조차 꾸미지 못한다는 것이다
그냥 남이 하는것 보고 우와..!@#하고 감탄만 하다가 죽는다
즐거운 논리식,논리값이 되지 않고는 엑셀과 친해질 수 없다
논리값은 딱 두개다..TRUE와 FALSE 딱 두개다
예,아니오 딱 두개 인것이다
겨울이면 겨울옷을 입고..
여름이면 여름옷을 입는다..
위의 두개가 논리식이고 논리값에 의한 조치를 취한것이다
3월은 봄이냐???
이라고 한것이 논리식이다..[3월=봄]맞으면 TRUE틀리면 FALSE
9월은 가을이냐??
이라고 한것도 논리식이다..
이렇게 확인하고 TRUE이면 봄옷을 꺼내입고 가을이면 가을 옷을 꺼내입는다
세상 살이 자체가 논리식이다
물론 계절에 상관없이 옷을 입는다면
갑동이가 빨강색옷을 입었나??? 그렇다..그럼 값은 TRUE
그래서 나도 빨강색옷을 입는다..TRUE에 대한 결과,행동이다
이렇게 사람은 어떤 논리식이던지 머리속에서 잠재적으로 작동한다
그것을 엑셀에 옮기자는 것인데 그것을 잘 못한다
엑셀에 옮기려면 무슨 부호같은 것이 있어 할 것아니냐??
즉 수식으로 옮기려면..
논리식을 구성하는 비교연산자(Comparision Operators)가 있다
> 크냐??< 작으냐??=같냐???<>다르냐??>=크거나같냐??<=작거나 같냐??
이것만 알면 된다
각자가 바보라고 생각하고 아래의 버튼을 반복크릭하면서
연습해보시기를...
이렇게 작성된 논리식의 결과값은 죽었다 깨어나도 TRUE와 FALSE두개의
값밖에 없다
이값을 SUMPRODUCT함수,IF함수,AND함수,OR함수등이 먹구 살고
조건부서식도 이 논리값을 먹구 살구..
유효성검사도 이 논리값을 먹구 살구..
그러니 논리값..즉 논리식을 모르고 엑셀을 하려고 하면 오산이다
그런데 좀더 구체적으로 설명을 좀 해주지 않구??!#@!
구체적으로 설명하고 자시고 할 일도 없이 간단한것이다..
너무 간단하여 설명하기도 우습다
그러니 논리값을 모르면 문제가 크다..반드시 알고 자유롭게 구사하여야 겠다
라는 동기부여만 해드리는 것이 좋을 것이다
알고자 하는 욕구가 생기면 가장 간단하게 잘 설명된곳은 두말하면 잔소리
엑셀의 도움말이다..
그것을 이곳에 옮길 필요는 없을 것이다
본강에서 논리값을 사용하는 응용문제들을 계속 접하면 된다
아니..논리값,논리식가 전부다
양식콘트롤 사용법
아주 간단하지만 초보님들이 헷갈려하는 것이 콘트롤이라는 녀석!!
또한 매번 설명하기 번거로운 주제가 콘트롤이다
양식콘트롤은 엑셀을 좀더 효율적으로 활용할수 있는 도구..
콘트롤도구상자의 콘트롤과 양식콘트롤을 헷갈리지 마시고 잘 활용하시기를..
HowToUseExcelControls.
ActiveX콘트롤 사용법_1
양식콘트롤과 ActiveX콘트롤은 초보님들이 항상 헷갈려서 많은 질문을
하는 부분이고 이것을 만들어 놓으니..한결 가쁜해진다..
매번 응용문제를 풀다가 수시로 나타나는 컨트롤을 매번 설명하기도 그렇고
매번 같은 설명은 설명하는 사람 입장에서는 무식한 삽질이다..
이렇게 만들어 놓으니 짐을 벗어 놓은 것 같다
앞으로 주간지에서 응용문제와 다양한 질문의 답을 하다가 보면 이것을 참조하라고
하면 간단하게 해결이 될 것이니...
양식콘트롤과 ActiveX콘트롤은 보기에는 비슷해 보여도 완전히 종자가 틀린 물건이다
특히 ActiveX콘트롤은 VBA프로그래밍을 하는 분들에게는 필수적으로 시간을
절약해주는 기특한 물건이니..이미 어느 정도 아시는 분일지라도
꼭 다운 받아서 즐거운 프로그래밍이 되시기 바란다..
경우에 따라서 종종 아래의 화일이 업데이팅될 수도 있을 것이다
어디에서 양식콘트롤을 사용하고 또 어떤 경우에 ActiveX콘트롤을 사용하고를
잘 가름하는데 도움이 될 것이고..
ActiveX콘트롤은 설명을 하자면 매우 방대한 량이 되므로 2권으로 나누어서 올리도록 한다
HowToUseActiveXControl_1.
ActiveX콘트롤 사용법_2
Image콘트롤과 Label콘트롤등의 유용한 기본적기능들
[ActiveX콘트롤 사용법]은 VBA입문자들은 보시고 많이 응용하시고
엑셀 초보님들은 그냥 [양식컨트롤]과 [ActiveX콘트롤]의 차이점을 아시고
헷갈리지 않기 위한 경험으로 보시면 좋겠다
특히 Image콘트롤과 Label콘트롤의 성질머리를 잘 이해하면 구현하고 싶은 것들이
더 많이 가능해 질것이다
HowToUseActiveXControl_2.
배열수식(Array Formula)과 배열상수(Array Constant) 사용법
아래와 같은 테이블같이 첫행에 1,2,3,4,5그리고 다음행에 6,7,8,9,10 과 같이
연속된 숫자를 행을 바꿔가면서 입력한다면 어떻게 할까??
일일이 입력하여야 하나??
배열수식이라는 것을 알면 아주 많은 응용을 할수 있게 되고
배열 상수라는 것을 활용하면 좀더 간략하고 쎄련된 수식을 작성할 수 있게 된다
배열수식과 배열상수에 대한 기본적인 개념을 담아 놓았으니 주간화일의 보충정보로
활용하시기 바란다
부족한 부분이 발견되면 추가하여 놓을 것이다
HowToUseArrayFormulaAndArrayConstant.
주요단축키 | Essential Hot keys
아래의 단축키정도는 자유자재로 써야 엑셀 잘한다는 소리듣는다
또한 VBA에서 소루션을 만들때 새로시트를 삽입하지 못하게
메뉴를 이것 저것 열심히모두 감추었으나
단축키를 죽이지 않으면 말짱 헛일하는 셈이다
새로 시트삽입하는 단축키 Shift+F11키도 죽여야 한다
그러려면 아래와 같이 할수 있을 것이다
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "+{F11}"
End Sub
Private Sub Workbook_Open()
Application.OnKey "+{F11}", "noNewSheet"
End Sub
일반 모듈시트에 아래와 같이 작성한다
Sub noNewSheet()
MsgBox "시트 삽입못합니다"
End Sub
즉 다시 말하면
Application.OnKey "+{F11}","실행하고싶은프로시져이름"
이라고 하면 엑셀이 갖고 있는 Shift+{F11}키의 명령을 사라지고
여러분이 작성하고 지정한 프로시져가 실행된다
만약 여러분이 작성한 프로시져가 없고 그냥 엑셀의 명령을 죽이려면 그냥
Application.OnKey "+{F11}",""
와 같이 하면 된다
그리고 다시 엑셀의 명령으로 돌아가고 싶으면
Application.OnKey "+{F11}"
두번째 매개변수를 아무것도 주지 않으면 된다
---------------------------------------------------------------
General|일반적인 작업
New file | 새화일 만들기 | Ctrl + N
Open file | 화일 열기 | Ctrl + O
Save file | 화일저장하기 | Ctrl + S
Move between open workbooks | 열린문서사이의 이동 | Ctrl + F6
Close file | 화일 닫기 | Ctrl + F4
Save as | 화일 저장하기 | F12
Display the print menu | 프린트메뉴보기 | Ctrl + P
Select whole spreadsheet | 시트상의 모든 셀 선택 | Ctrl + A
Select column | 열 선택 | Ctrl + Space
Select row | 행선택 | Shift + Space
Undo last action | 이전작업취소 | Ctrl + Z
Redo last action | 이전작업반복 | Ctrl + Y
Start a formula | 수식입력하기 | =
Exit Excel | 엑셀 끝내기(모든 문서 동시에 닫힘) | Alt + F4
Navigating| 이동하기
Move to next cell in row | 선택된 행에서 다음셀로 이동 | Tab 혹은 Enter
Move to previous cell in row | 선택된 행에서 이전셀로 이동 | Shift+Tab 혹은 Shift+Enter
Up one screen | 이전 스크린으로 이동 | Page Up
Down one screen | 다음 스크린으로 이동 | Page Down
Move to next worksheet | 다음 시트로 이동 | Ctrl + Page Down
Move to previous worksheet | 이전 시트로 이동 | Ctrl + Page Up
Go to first cell in data region | 데이타영역의 첫째셀로 이동 | Ctrl + Home
Go to last cell in data region | 데이타영역의 마지막셀로 이동 | Ctrl + End
Formatting Text In Worksheet | 텍스트 서식하기
Bold toggle for selection | 선택된 문자 굵은체로 토글 | Ctrl + B
Italic toggle for selection | 선택된 문자 이태릭체로 토글 | Ctrl + I
Underline toggle for selection | 선택된 문자 밑줄치기 토글 | Ctrl + U
Strikethrough for selection | 선택된 문자 중간줄치기 토글 | Ctrl + 5
Change the font | 폰트바꾸기 | Ctrl + Shift + F
Change the font size | 폰트사이즈 바꾸기 | Ctrl + Shift + P
Apply outline borders | 선택된 셀 테두리치기 | Ctrl + Shift + 7
Remove all borders | 선택된 범위 테두리모두 없애기 | Ctrl + Shift + Underline
Wrap text in same cell | 셀에서 줄바꿈 | Alt + Enter
Formatting Cells | 범위 서식하기
Format cells | 셀 서식대화상자 띄우기 | Ctrl + 1
Format as currency | 통화서식하기 | Ctrl + Shift + 4
Format as date | 날짜서식하기 | Ctrl + Shift + #
Format as percentage | 백분율 서식하기 | Ctrl + Shift + 5
Format as number | 숫자서식하기 | Ctrl + Shift + 1
Editing/Deleting Text | 텍스트 편집/삭제하기
Delete one character to right | 오른쪽 문자지우고 | Delete
Delete one character to left | 왼쪽문자지우기 | Backspace
Edit active cell | 활성화셀 편집모드로 바꾸기 | F2
Cancel cell entry | 셀편집취소하기 | Escape Key
Selecting Cells | 셀 선택하기
Select entire worksheet | 시트상의 전체셀 선택하기 | Ctrl + A
Select entire row | 전체행선택하기 | Shift + Spacebar
Select entire column | 전체열 선택하기 | Ctrl + Spacebar
Select Table All | 테이블범위 모두 선택하기 | Ctrl+*
Manual select | 한셀씩 확장선택하기 | Shift + Left, Right, Up, Down 방향키
Copying and Moving Text | 텍스트복사 이동
Cut | 잘라내기 | Ctrl + X
Copy | 복사 | Ctrl + C
Paste | 붙여넣기 | Ctrl + V
Inserting Text Automatically | 텍스트자동입력하기
Insert the date | 현재날짜입력하기 | Ctrl + ; (쎄미코론)
Insert the time | 현재시간입력하기 | Ctrl + Shift + ; (쎄미코론)
Insert columns/rows | 행이나 열삽입하기 |Ctrl + Shift + +(더하기 부호)
Insert a new worksheet | 새시트삽입하기 | Shift + F11
Misc | 기타
Find text | 문자찾기대화상자 | Ctrl + F
Replace text dialog | 문자바꾸기대화상자 | Ctrl + H
Create a chart Sheet | 챠트시트만들기 | F11
Go to a cell command | 이동대화상자 | Ctrl + G
Spell Check | 영문 맞춤법 체크 | F7
Edit a cell comment | 셀메모삽입하기 | Shift + F2
수식을 넣은 셀을 간단하게 값으로 바꿔버리고 싶을때가 있다
물론 복사/선택하여 붙여넣기/값만 붙이기를 이야기하는 것이 아니다
그냥 하나의 셀에 수식이 있을때
F2키누르고 F9키 누르고 엔터키치면 끝!!!
VBA코드로는 Range("A1").Value=Range("A1").Value 면되고
셀을 참조하는 주소를 바꿀때
수식을 선택하고 변경하고 싶은 참조주소에 커서를 위치한후
F4키를 반복하여 누르면 한번 누를때마다 상대주소와 절대주소가 순서대로
바뀌어 나간다
=A1*B1 에서A1에 커서를 위치한후 F4키를 누르면
=$A$1*B1 이 되고 또 누르면
=A$1 *B1이 되고 또 누르면
=$A1*B1 이 되고 또 누르면
=A1*B1 이 다시 된다
또한 수식중에 중간부분을 계산을 해보고 싶을때는 계산하고 싶은 수식중의 일부 혹은
전부를 선택한후 F9키(계산키)를 누르면 계산의 결과값을 볼수 있다
이때 그냥 엔터를 치면 수식이 사라지니 ESC키를 눌러서 취소하는 것이 좋고..
F9키는 계산키이다 시트를 계산옵션을 수동으로 하였을때 시트 전체를
다시 계산하고 싶을 때 F9키를 누르면 된다
수식을 입력할때 주소를 참조할면서 콤마를 찍어야 하는 경우가 많다
예를 들어서
=SUM(A1,F3,,,,,)
와 같이 주소를 하나,하나 선택을 할때 콤마를 자판으로 찍으면 바보..
콘트롤 키를 누른 상태로 그냥 계속 선택해 나가면 엑셀이 알아서
콤마를 찍어준다..
단축키가 이것외에도 많지만 많이 사용하는 것만 나열하였다
앞으로도 계속 업데이트하여 나갈것이니..이것이 끝난 페이지가 아니라는 것을
알고 요의 주시하면 좋은 일들이 생길것이다
VBA로 프로그래밍을 할때는 필요없는 엑셀의 기능을 없애는 것이 중요하니
엑셀의 기능을 수행하는 단축키는 모두 찾아서 죽여야 할것이다