1. 엑셀 함수

1) 날짜

[ YEAR() ] : 연도

[ MONTH() ] : 월

[ DAY() ] : 일

[ TODAY() ] : 오늘

[ DATE() ] : 날짜

=date(year(),month(),day())

각각의 함수 참조 가능

 

* [ Ctrl + ; ] 오늘 날짜를 텍스트로 불러오기

* YY : 연도 중 뒤의 2자리만 표시

* YYYY : 연도를 4자리로 표시

* M : 월을 1~12로 표시

* MM : 월을 01~12로 표시

* MMM : 월을 Jan~Dec로 표시

* MMMM : 월을 January~December로 표시

* D : 일을 1~31로 표시

* DD : 일을 01~31로 표시

* DDD : 요일을 Sun~Sat로 표시

* DDDD : 요일을 Sunday~Saturday로 표시

 

[ =Weekday(날짜) ]

날짜의 요일을 지정한 숫자로 변경한다.

일=1, 월=2, 화=3, 수=4, 목=5, 금=6, 토=7

※ 셀서식에서 표시 형식을 [aaa] 로 지정하면

“월” 로, 표시 형식을 [aaaa] 로 지정하면

“월요일” 로 값이 반환된다.

→ 해당 날짜가 무슨 요일인지 반환

 

[ =EOMONTH(날짜,개월) ]

입력된 날짜에서 몇 ‘개월’ 건너 뛴 달의

‘마지막 날짜’가 나오는 함수

ex) D2 = 1980-12-08

=eomonth(D2,1) → 1981-01-31

 

[ =Eomonth(today(),0) ]

이번 달의 마지막 날을 반환한다 (30일 or 31일)

※ 이번달 0, 저번달 -1, 다음달 1 //

의 마지막 날

ex) [ =Eomonth(today(),-1)+1 ]

이번 달의 1일을 반환한다.

2) 찾기 (Find)

[ =LEFT(A4,4) ]

A4 셀의 왼쪽에서 1~4값 참조

 

[ =RIGHT(A4,4) ]

A4 셀의 오른쪽에서 1~4값 참조

 

[ =MID(A4,5,2) ]

A4의 셀 값 중 5번째 값부터, (~)

그 두 번째 값까지 참조

ex) A4 = 2018032 → 03

 

[ =Max(범위) ]

범위에서 제일 큰 수를 찾는다.

 

[ =Min(범위) ]

범위에서 제일 작은 수를 찾는다.

 

[ =CHOOSE(색인번호, 값1,값2,값3,....) ]

* 색인번호 = 조건 (숫자 or 수식 or 참조)

* (조건, 값이 1인 경우 반환값, 2인 경우 반환값,

3인 경우 반환값.....)

색인번호를 참조하여 값 목록 중

순번에 위치한 값을 반환한다.

ex) =choose(4,3,4,5,6) → 6

3,4,5,6 중 4번째 있는 값

 

[ =LARGE(범위,N) ]

N번째로 큰 수를 찾아라

 

[ =VLOOKUP(검색하고자 하는 셀, 범위,

필요한 셀의 열, 인수) ]

값을 찾는다 (열)

 

[ =HLOOKUP(검색하고자 하는 셀, 범위,

필요한 셀의 행, 인수) ]

값을 찾는다 (행)

 

* 인수가 0 = False, 정확한 값

* 인수가 1 = True, 근사값

	A	B	C	D
1	품목	수량	단가	구매처
2	볼펜	5	3,000	다이소
3	연필	2	2,500	드림디포
4	가위	4	1,000	알파문구

	G	E
1	품목	단가
2	볼펜

위와 해당하는 값을 추출하면?

E2 [ =vlookup(G2,A2:D4,3,0) ]

 

[ =Match(찾을 값, 찾을 범위, 찾기 옵션) ]

지정한 범위 중에 내가 찾고자 하는 셀은

몇 번째에 있는가.

찾기 옵션 : 0 = 정확한 일치 값,

-1 = 이상 값 중 최소값, 1 = 이하 값 중 최댓값

 

[ =Index(범위,행,열) ]

범위에서 해당 행*옆 값을 출력

표나 범위에서 값 또는 값에 대한 참조를 반환.

내가 원하는 셀의 내용을 복사 해준다.

 

[ =Index(범위,Match(X,범위,0),Match(Y,범위,0)) ]

표나 범위에서 X와 일치하는 (가로)행에서,

Y와 일치하는(세로)열의 데이터를 찾아 나타낸다.

 

※ [ =If(IsError(원래수식),"",원래수식) ]

값이 오류값일 경우 표시하지 않는다.

 

[ =If(IsError(Index(절대참조 된 전체범위,

Match(일치기준값,절대참조 된 세로행 범위,0),

기준열)),"",앞에수식=index~,Match~)

 

=IF(ISERROR(INDEX('20180706'!$B$5:$L$42,

MATCH('7월 객실 수'!F3,'20180706'!$B$5:

$B$42,0),11)),"",INDEX('20180706'!$B$5:$L$42,

MATCH('7월 객실 수'!F3,'20180706'!$B$5:

$B$42,0),11))

만약(if) 20180706탭의 해당범위(B5:L42)에서

해당범위(B5:L42) 중 7월 객실 수'!F3와 동일한 값이 속한 행을 찾아 (Match)

그 열에 있는 값을 구하고(11번째), 그값이 오류값이면 (IsError, 존재하지 않으면)

표기하지 마라 ("")

[ =SEARCH(찾을 문자열, 찾을 대상, 시작위치) ]

찾을 대상에서 찾고 싶은 문자열이 처음 만나는

절대적인 위치가 나온다. 시작위치를 지정하면

그 위치부터 찾는다. 공백도 1칸으로 계산한다.

ex) =search("세“,”세금 명세서“)

→ 1 [세]금 명세서

ex2) =search("세“,”세금 명세서“,3)

→ 5 세금 명[세]서

시작 위치는 지정하지만, 값은 절대적 위치값.

 

[ =FIND() ] 찾기 함수, 대소문자 구분함.

3) 계산 외 (Sum, Count 포함)

[ =Sumif(조건범위,"조건",더할범위) ]

조건 범위 중 조건에 참인 경우,

더할 범위를 더한다.

 

[ =Sumifs(더할범위,조건범위,"조건",

조건범위,"조건", · · · ·) ]

조건범위 중 조건에 모두 참인 경우,

더할 범위를 더한다.

 

[ =Count(범위) ]

Only 숫자 셀을 센다.

 

[ =Counta(범위) ]

데이터가 있는 셀을 센다. (숫자, 문자)

 

[ =Countblank(범위) ]

데이터가 없는, 공백의 칸을 센다.

 

[ =Countif(범위,조건) ]

범위 내 조건에 일치하는 셀의 개수를 세어준다.

ex) [ =Countif(범위,">1") ] 1보다 크면 세어라

 

[ =Countif(범위,조건) ]

범위 내 조건에 일치하는 셀의 개수를 세어준다.

ex) [ =Countif(범위,">1") ] 1보다 크면 세어라

 

[ =AVERAGE(범위) ]

평균 구하기. AVERAGE 함수 범위에 반드시

절대참조가 들어가야 한다.

 

[ =DAVERAGE(데이터범위, 열 번호, 조건) ]

- 데이터범위 : 데이터 전체 범위

- 열 번호 : 평균을 구할 필드의 열 번호

- 조건 : ex) 종류=사과

해당 범위에서 조건에 맞는 자료를 대상으로

지정된 열에서 평균을 계산한다.

조건에 맞는 데이터의 평균을 구한다.

 

[ =POWER(X,Y) ]

x의 y승

 

[ =SQRT() ]

루트, 제곱근

=sqrt(16) → 4

 

4) 수치표기

[ =ABS() ] 절댓값을 무시한다

 

[ =INT(-7.4) ] → [-8]

소숫점 아래를 버리고, 가장 가까운 정수로 내림한다. 마이너스인 경우 작은 쪽의 정수로 표기한다.

 

[ =TRUNC(x,인수) ]

지정한 자릿수만을 소숫점 아래에 남기고,

나머지 자리를 버린다. (0인 경우 생략이 가능)

 

[ =Roundup(원본*X%,-1) ]

ex) 단가가 10% 인상된 경우

※ X% : 10% 인상시 110% 로 입력

※ -1 : 마지막 자리를 0으로 하여 반올림

※ 인수가 1~ : 소숫점 자릿수를 늘림

※ 인수가 -1~ : ~단위 절사(0), 반올림

[ =Round(값,0) ] 반올림

[ =Rounddown(값,0) ] 내림함수

5) 기타

[ =Rank(X, 범위) ]

범위상에서 X의 순위를 나타낸다.

 

[ =MOD() ]

나머지를 구한다

ex) =mod(13,5) → 3 (남는 값)

=mod(13,-5) → -2 (부족한 값)

 

[ =TRIM(텍스트) ]

텍스트의 양쪽 공백을 제거한다.

ex) =trim(" Bra zil") → "Bra zil"

 

[ =PROPER(문자열) ]

첫글자는 대문자로 나머지는

소문자로 표기한다. (띄어쓰기 반영)

ex) =proper("republic of korea")

→ Republic Of Korea

 

[ =LOWER(문자열) ]

모든 문자를 소문자로 만든다.

ex) =lower("Republic Of Korea")

→ republic of korea

 

[ =UPPER(문자열) ]

모든 문자를 대문자로 만든다.

ex) =upper("Republic Of Korea")

→ REPUBLIC OF KOREA 

-------------------------------------------------------------------------

2. 엑셀 프로그램 기타 팁

* [ # ] : 유효한 숫자 표시

* [ , ] : 숫자의 단위수 표시

* [ 0 ] : 소수 자릿수를 “0”의 개수만큼 표시

* [ @ ] : 문자에 입력되는 표시형식으로

앞, 뒤에 입력된 내용이 표시

* 표시형식에서 쉼표(,)를 이용하면

천단위씩 생략하여 표시한다.

ex) 입력값이 100,000,000

#,##0 → 100,000,000

#,##0, → 100,000

#,##0,, → 100

* [ * ] : 모든 문자열 (와일드카드)

* [ ? ] : 한 개의 문자열

* 셀에 글자를 길게쓰면 칸에서 끊기는 게 아니라 다음 열로 쭉 넘어갑니다.

* Enter를 치면 다음행으로 넘어가고

한셀에 일부러 밑으로 글자를 잔뜩 쓰면 (Alt + Enter)

셀에 글씨가 넘쳐버리니까 (깨짐)

억지로 한셀에 글자를 많이 쓸 필요는 없습니다.

게다가 전체적으로 틀이 잡혀있는 경우 전부 일그러짐..

* Shift 를 누르면 셀 드래그를

Ctrl 를 누르면 셀 부분 선택을 할 수 있다.

* 열과 행

	1열	2열	3열
1행
2행
3행

* 채우기 핸들시 ★절대참조★

* ~보다 크고, ~보다 작은 값 강조하기

(데이타 시트에서 값 찾아내기)

1) 해당 셀 영역 드래그

2) 조건부 서식 클릭

3) 셀 강조 규칙

4) 다음 값의 사이에 있음

5) 서식 지정

 

* 복사 - 선택하여 붙여넣기 - (값) 을 하면

서식의 값이 복사된다.

※ 단축키 [Ctrl+C, Ctrl+Art+T, 값 V, 서식 T]

 

* [ Ctrl + 5 ] 취소선 단축키 

* 서식 복사 - 붙여넣기

해당 서식 (표 테두리, 글자서식) 을 똑같이 재현할 수 있다.

서식 복사 O, 수식 복사 X

* 서식이 있는 곳에 서식과 상관없이 값만 복사하고 싶다면

복사 - 붙여넣기 - 오른쪽 하단 리본메뉴 - 주변 서식에 맞추기

* 숫자를 텍스트화 하려면 숫자앞에 

[ ' ]표시를 넣으면 된다. ex) [ '2345 ] 

* 셀에 글자가 너무 많으면 인쇄할때 #####으로 나온다

* 셀 오른쪽 클릭, 셀서식, 맞춤, 셀에 맞춤에 체크를 하면

####이 해결되는 대신 글자가 셀안에 맞춰서 작게 들어가게 된다.

* 리본메뉴에서 인쇄미리보기를 체크하면 인쇄미리보기 박스가 생긴다.

* 인쇄 미리보기를 누르면 현재 인쇄될 페이지를 볼 수 있다. (2007기준)

* 해당 영역만큼만 인쇄를 하고싶다면, 

영역 잡기 - 페이지 레이아웃 - 인쇄영역 - 인쇄영역설정

* 페이지가 너무 커서 2장 이상으로 나온다면

인쇄미리보기 - 페이지설정 - 자동맞춤

* 정렬을 예쁘게 하고 싶다면

인쇄미리보기 - 페이지설정 - 여백 - 가운데맞춤 (가로,세로) 체크

* 쪽수넣기, 상호넣기 등 바닥글을 삽입하고 싶은 경우

인쇄미리보기 - 페이지설정 - 머리글/바닥글 - 바닥글 편집

* 셀 안에서 텍스트 줄바꿈을 간편하게 하고 싶다면

셀 입력 상태에서 [ ALT + ENTER ]

* 셀이 전부 병합된 상태에서 

병합을 해제 하여 셀 서식이 엉망이 됬을때는

셀 서식을 다시 지정하는 것보다

다른셀의 기존 서식을 복사해서 갖다 붙이는게 편하다.

* 표시 형식 탭에서는 누르기만 해도 

숫자의 표시 형식을 간단하게 바꿀 수 있다 (\,%,0,000)

* 행이나 열을 삽입하고 싶을 때는 해당 행, 열 (1 or A ...) 을 

마우스로 잡고 [Ctrl + "+" ], 삭제는 [ Ctrl + "-" ]

* 모든 행/열을 동일하게 크기를 늘리고 싶다면

해당 행/열을 동시에 잡은 후, 한 행/열 크기를 늘리면 된다.

* 세밀하게 늘리고 싶을 때는 오른쪽 클릭 열 너비/행 높이

* 메모를 실수로 삽입했을 때는 

다시 오른쪽 클릭 - 메모삭제가 귀찮다면 [Ctrl + Z]로 지워버리자

* 시트1 오른쪽 클릭 - 이름바꾸기

* 시트1 오른쪽 클릭 - 이동/복사 - 복사본만들기

* 시트1 오른쪽 클릭 - 이동/복사 - Book1 클릭 - 새 통합문서 - 확인

(새 통합문서로 복사하기)

* 시트1과 시트2도 물론 참조가 됨

* Ctrl + S (저장) 을 습관화하자