메뉴 건너뛰기

theqoo

팁/유용/추천 엑셀 초급반
80,342 7190
2020.10.18 21:46
80,342 7190
워드만 쓰던 나에게, 입사했더니 눈앞에 엑셀이?
기본도 몰라서 우물쭈물하는 당신에게... 만 필요할수도 있는 엑셀 초급반! 

회사에 신입이 왔는데 나이 30개 먹고 아무렴 엑린이 일수가 있구나!
싶어서 만든 엑셀 초급반임 ㅎㅎ (어려운 실무는 아님)

(사실 하나 하라고 던져 줬더니 서식 다 파괴해서 슬펐다카더라)


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) ]

이번 달의 마지막 날을 반환한다 (30or 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) ]

xy

 

[ =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 (저장) 을 습관화하자








댓글 7190
댓글 더 보기
새 댓글 확인하기

번호 제목 날짜 조회
전체공지 JTBC 드라마 '설강화' 관련 JTBC 법무팀의 법적 대응 안내문 공유 21.12.30 16만
전체공지 온에어관련 부탁 공지 21/09/17 자로 해제 알림 6 21.01.10 146만
전체공지 더쿠 이용 규칙 [📢 210427 추가수정갱신 5) 항목 中 -여초 저격식 활동 위주 및 분란조장 하러 오는 일부 남초 회원들 경고 항목 추가 수정 갱신 알림 및 무통보 차단 중] 20.04.29 840만
전체공지 더쿠 필수 공지 :: 성별관련 언금 공지 제발 정독 후 지키기! (위반 적발 시 차단 강화) 16.05.21 1048만
전체공지 *.。+o●*.。【200430-200502 더쿠 가입 마감 **현재 theqoo 가입 불가**】 *.。+o●*.。 4434 15.02.16 398만
공지 더쿠 모바일에서 유튜브 링크 올릴때 주의할 점 457 21.08.23 25만
공지 더쿠에 쉽게 동영상을 올려보자 2693 20.05.17 34만
공지 슬기로운 더쿠생활 : 더쿠 이용팁 3847 20.04.30 93만
공지 🔥🔥스퀘어 저격판으로 사용 강력 금지!!!!!!!!!🔥🔥 무통보 차단 주의!!!!!!!!!🔥🔥 1223 18.08.31 356만
모든 공지 확인하기()
2107007 8년 전 오늘 발매된_ "싫습니다" 1 05:40 29
2107006 [단독] 여성청소년 관련 수사 '조직개편'…전문성 극대화 2 04:53 261
2107005 미국에서 최흉악범이 간다는 교도소 21 04:36 1624
2107004 회전초밥: 손님 더이상 접시가 없어요.. 9 04:13 2658
2107003 기자회견중 뜬금없이 노래해달라는 기자와 돈부터 내라는 가수 6 04:01 2051
2107002 에르메스 개 밥그릇 25 03:58 2356
2107001 다시 태어난다면 태어날곳 고르기 57 03:50 1244
2107000 "1층이라더니 지하층"..분양자들 '분통' 10 03:42 2044
2106999 로버트 패틴슨, 봉준호 감독의 차기작 출연 예정 129 03:10 7759
2106998 유형별로 보는 뉴이스트 강동호 점프 모음 (feat. 체공시간) 16 03:07 473
2106997 잊을만하면 도지는 블랙베리병 24 02:59 2793
2106996 왼쪽 얼굴과 오른쪽 얼굴이 아예 다른 사람 같은 주지훈 38 02:57 2621
2106995 알티터진 nct 태용 영통 폴라로이드 러브 챌린지 19 02:57 1408
2106994 먹다가 죽을 것 같아서 봉인한 팝콘 27 02:54 3281
2106993 종류가 겁나 다양한 아이스크림 7 02:51 1688
2106992 송도‧청라‧영종 시민 대중교통보다 승용차 이용↑ 23 02:39 1019
2106991 8년간 일하고 은퇴하는 탐지견에게 열어준 깜짝 파티.twt 12 02:33 2006
2106990 8년 전 오늘 발매된_ "Girls Girls Girls" 28 02:32 1385
2106989 한 유튜버의 가품에 대한 생각 512 02:30 2.1만
2106988 여중생에 술 먹이고 집단 성폭행·촬영한 일당 중형 24 02:24 1115