메뉴 건너뛰기

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

회사에 신입이 왔는데 나이 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 (저장) 을 습관화하자








목록 스크랩 (6028)
댓글 7268
댓글 더 보기
새 댓글 확인하기

번호 카테고리 제목 날짜 조회
이벤트 공지 [💜리쥬란 코스메틱💜] 이제는 모공 고민까지!? ✨홈 에스테틱 케어 1인자✨ NEW 더마 힐러 포어 타이트닝 라인 2종 체험 이벤트 (100인) 434 02.18 16,604
공지 [공지] 언금 공지 해제 24.12.06 936,202
공지 📢📢【매우중요】 비밀번호❗❗❗❗ 변경❗❗❗ 권장 (현재 팝업 알림중) 24.04.09 5,408,104
공지 공지가 길다면 한번씩 눌러서 읽어주시면 됩니다. 23.11.01 8,879,946
공지 ◤더쿠 이용 규칙◢ [스퀘어 정치글 금지관련 공지 상단 내용 확인] 20.04.29 27,620,945
공지 정보 더쿠 모바일에서 유튜브 링크 올릴때 주의할 점 763 21.08.23 6,160,759
공지 정보 나는 더쿠에서 움짤을 한 번이라도 올려본 적이 있다 🙋‍♀️ 242 20.09.29 5,129,723
공지 팁/유용/추천 더쿠에 쉽게 동영상을 올려보자 ! 3478 20.05.17 5,744,542
공지 팁/유용/추천 슬기로운 더쿠생활 : 더쿠 이용팁 3992 20.04.30 6,169,945
공지 팁/유용/추천 ◤스퀘어 공지◢ [9. 스퀘어 저격판 사용 금지(무통보 차단임)] 1236 18.08.31 11,058,741
모든 공지 확인하기()
2637180 유머 알티 타고 있는 라이즈 소희 27 05:52 2,878
2637179 기사/뉴스 추억의 아이리버 MP3 플레이어, SM엔터 굿즈로 부활 4 05:19 2,243
2637178 기사/뉴스 '故 김새론 절친' 김보라, 빈소 앞에서 통곡...'허물없이 지낸 사이‘ 5 05:11 6,021
2637177 이슈 Q: ‘나 오늘 좀 멋지구나' 라고 생각..? 차준환: 없습니다 5 05:06 1,711
2637176 이슈 28년 전 오늘 발매♬ 토모사카 리에 '泣いちゃいそうよ' 04:55 656
2637175 유머 새벽에 보면 완전 추워지는 괴담 및 소름돋는 썰 모음 147편 1 04:44 858
2637174 이슈 2032년 지구 소행성 충돌 가능성 3.1%로 상승 22 04:43 3,262
2637173 이슈 피라미드 크기 체감 8 04:23 3,510
2637172 유머 사촌 오빠가 취직해서 직장에서 엄마가 진짜 좋아했더니 6 04:18 6,363
2637171 이슈 퀸근혜의 상당한 패기였던 정책 92 04:13 10,005
2637170 이슈 봉준호 감독 밝힌 차기 심해어 애니메이션 도입부 13 04:02 3,136
2637169 이슈 델타[엔데버] 항공 4819편 착륙 영상[전원생존] 2 04:01 2,468
2637168 유머 공공칠빵 하는 앵무새들 3 04:00 1,866
2637167 이슈 온앤오프 레몬보이스가 혼자 부른 더스트레인저 [리무진서비스] 2 03:47 1,171
2637166 정보 초등학생들 등하교를 도와주게된 일본 오타쿠들.jpg 11 03:29 4,350
2637165 이슈 중학교 1학년 음악 교과서에 실렸다는 부석순 “파이팅해야지” 8 03:06 2,575
2637164 이슈 러시아 아기 판다 카츄샤 🐼 나랑 놀아요 7 02:52 2,769
2637163 이슈 배우 박지현 인스타그램 업데이트 3 02:45 4,797
2637162 팁/유용/추천 다이어트의 바이블이라는 지식인 답변 260 02:38 25,711
2637161 이슈 블랙핑크 로제 인스타 업뎃 15 02:23 4,881