함수걱정 끝! :: 엑셀 직장인 필수 함수 모음 (20개) - 2편

실무자를 위한 엑셀 필수함수 중 텍스트/조건과 관련된 9개의 필수함수와 기초 차트/그래프 제작법을 단계별로 살펴봅니다.

홈페이지 » 함수걱정 끝! :: 엑셀 직장인 필수 함수 모음 (20개) - 2편

엑셀 직장인 필수 함수 20개 총정리 2탄 (텍스트/조건 함수)

이번 강의는 직장인 필수 함수 20개의 두번째 강의로, 텍스트/조건 함수 9개의 실전 사용법과 가공된 데이터를 기반으로 기초 차트를 만드는 방법을 단계별로 알아봅니다. 각 함수 및 차트 만들기에 대한 상세설명은 아래 관련 링크를 참고하세요.

엑셀 직장인 필수 함수 2탄 :: 목차 바로가기
영상강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [실무기초] 실무자 기초함수 20개 [2/2]
    예제파일
  • [실무기초] 실무자 기초함수 20개 [2/2]
    완성파일

1. 셀의 글자수 계산하기 – LEN 함수

= LEN ( 문자열 )
셀 또는 문자열의 글자수를 반환합니다.
LEN 함수 상세설명 바로가기

LEN 함수를 사용하여 업체명의 글자수를 계산합니다. 해당 업체의 글자수가 5글자 이상일 경우, 라벨 추가여부를 “O”로 출력합니다.

IF 함수를 같이 응용하여, 글자수가 5글자 이상인지 여부를 확인합니다. 예제파일 R2셀에 아래 수식을 붙여넣기 한 뒤, 자동채우기하여 마무리합니다.

=IF(LEN(B2)>5,"O","X")
직장인 필수 함수 엑셀 LEN 함수 예제
LEN 함수를 사용하여 글자수를 계산합니다.

2. 업체 구분 분류하기 (특정 단어 포함 여부 확인) – FIND 함수

= FIND ( 찾을문자, 기존문자열, [검색시작위치] )
기존 문자열에서 원하는 문자를 찾은 뒤, 문자위치를 숫자로 반환합니다.
FIND 함수 상세설명 바로가기

FIND 함수는 특정문자 포함여부를 확인합니다. 와일드카드를 사용하여 특정문자의 부분일치여부를 확인해야 할 경우 SEARCH 함수를 사용합니다..

이번 강의에서는 제조사명에서 '모터' 단어의 포함여부를 검색하여 ‘모터업체’를 구분합니다.  제조사명에 '모터'라는 단어가 포함되지 않을경우, FIND 함수는 오류를 반환합니다.

따라서 IF 함수와 ISERROR 함수를 동시에 이용하여 공식을 작성합니다. 아래 수식을 예제파일의 T2셀에 붙여넣기 한 뒤, 자동채우기하여 마무리합니다.

=IF(ISERROR(FIND("모터",B10)),"","모터업체")
직장인 필수 함수 엑셀 FIND 함수 예제
FIND 함수를 사용하여 '모터'가 들어간 업체명을 찾습니다.

3. 특정 업체의 정보 조회하기 – VLOOKUP 함수

= VLOOKUP ( 찾을문자, 참조범위, 열번호, [일치옵션] )
범위의 맨 좌측에서 원하는 값을 찾은뒤, 같은 행에 위치하는 다른 값을 반환합니다.
VLOOKUP 함수 상세설명 바로가기

VLOOKUP 함수는 범위에서 원하는 값을 찾은 뒤, 해당 값의 다른 정보를 찾아 반환하는 함수입니다.

VLOOKUP 함수는 직장인 필수 함수 중 실무자라면 반드시! 알아야 할 가장 중요한 엑셀 필수 함수가 되겠습니다.^^*

VLOOKUP 함수를 이용하여 특정업체의 국가, 직원수 등의 정보를 조회합니다. 예제파일의 W2셀에 아래 수식을 붙여넣기 합니다. X2, Y2셀에도 각각 다른 열번호로 수식을 입력합니다.

=VLOOKUP(V6,B:F,2,0)
엑셀 직장인 필수 함수 VLOOKUP 예제
VLOOKUP 함수로 업체정보를 조회합니다.

4. 최고/최저 매출 구하기 – MIN/MAX 함수

= MIN ( 값1, [값2], ... ) / = MAX ( 값1, [값2], ... )
주어진 값 또는 범위내에서 최소/최대값을 반환합니다.
MIN 함수 상세설명 바로가기
MAX 함수 상세설명 바로가기

MIN 함수와 MAX 함수를 사용하여 모든 업체의 최고/최저 매출액을 계산합니다. 예제파일의 W10, Y10 셀에 아래 수식을 각각 붙여넣기합니다.

= MAX(E:E) / = MIN (E:E)
엑셀 MAX 함수 예제
MAX/MIN 함수로 업체별 최고/최저 매출을 구합니다.

5. 국가별 최고/최저 매출 구하기 – MAXIFS / MINIFS 함수

= MAXIFS/MINIFS ( 최대/최소값범위, 조건범위1, 조건1, [조건범위2], [조건2], … )
조건을 만족하는 최대/최소값을 구합니다. (오피스 365 또는 엑셀 2019 이상 버전에서만 사용가능합니다.)
MAXIFS 함수 상세설명 바로가기
MINIFS 함수 상세설명 바로가기

MAXIFS 함수와 MINIFS 함수를 사용하여 각 국가별 제조사의 최고/최저매출액을 계산합니다. MAXIFS 함수와 MINIFS 함수는 엑셀 2019 이후 또는 오피스 365 사용자만 사용가능합니다.

예제파일의 W13, Y13 셀에 아래 수식을 각각 붙여넣기 한 뒤, 아래로 자동채우기하여 마무리합니다.

=MAXIFS (E:E,C:C,V13) / = MINIFS (E:E,C:C,V13)
엑셀 MAXIFS 함수 예제
MAXIF 함수로 국가별 최고매출액을 계산합니다.

6. 여러 조건을 만족하는 매출합계 계산 – SUMIFS 함수

= SUMIFS ( 합계범위, 조건범위1, 조건1, [조건범위2], [조건2], … )
여러개의 조건을 만족하는 값의 합계를 계산합니다.
SUMIFS 함수 상세설명 바로가기

SUMIFS 함수를 사용하여 여러개의 조건(얘: 특정 국가의 특정 직원수)를 만족하는 업체의 매출합계를 계산합니다. 예제파일의 X35셀에 아래 수식을 붙여넣기합니다.

=SUMIFS(E:E,C:C,V13,D:D,W13)
엑셀 SUMIFS 직장인 필수 함수 예제
SUMIFS 함수로 국가/직원수 조건을 만족하는 업체의 총매출을 계산합니다.

7. 여러 조건을 만족하는 평균계산 – AVERAGEIFS 함수

= AVERAGEIFS ( 평균범위, 조건범위1, 조건1, [조건범위2], [조건2], ... )
여러 개의 조건을 만족하는 값의 산술평균을 계산합니다.
AVERAGEIFS 함수 상세설명 바로가기

AVERAGEIF 함수를 사용하여 여러 개의 조건을 만족하는 제조사의 평균 매출을 계산합니다. AVERAGE 함수의 주의사항과 마찬가지로, AVERAGEIFS 또한 '산술평균'을 계산한다는 점을 주의하여 사용합니다.

예제파일의 Y35셀에 아래 수식을 붙여넣기합니다.

=AVERAGEIFS(E:E,C:C,V13,D:D,W13)
엑셀 AVERAGEIFS 함수 예제
AVERAGEIFS 함수로 국가/직원수를 만족하는 업체의 평균매출을 계산합니다.

8. 가공된 데이터를 차트/그래프로 만들기

보고서는 보고를 받는 사람이 한눈에 보기 편하고 이해하기 쉽도록 만들어져야 합니다. 그러한점에서, 엑셀은 가공된 데이터를 시각화하는데에 매우 최적화되어 있는데요. 지금까지 편집한 데이터를 바탕으로 3가지의 보고서용 차트를 간단히 제작합니다.

보고서용 차트를 더욱 깔끔하게 만드는 차트만들기 5단계 방법은 아래 차트만들기 자세히 설명드렸습니다.

1. 국가별 최고/최저 매출 차트 만들기
  1. 키보드 Ctrl 키를 누른채로 국가, 최고매출, 최저매출 범위를 선택합니다.

    직장인 필수 함수 국가명 매출 범위 선택
    국가범위와 최고/최저매출 범위를 동시에 선택합니다.

  2. 화면 상단의 '삽입' - '추천차트'를 클릭합니다. 세로막대형그래프가 자동으로 추천됩니다. 차트를 삽입합니다.

    세로막대형 차트 삽입
    '삽입' - '추천차트'로 세로막대형 그래프를 삽입합니다.

  3. 차트제목을 변경한 뒤, 세로축과 눈금선을 삭제합니다.

    차트 제목 변경 눈금선 삭제
    차트제목을 변경한 뒤, 세로축과 눈금선을 삭제합니다.

  4. 차트의 '+' 버튼을 클릭한 뒤, '데이터레이블'을 추가하여 차트 만들기를 마무리합니다.

    직장인 필수 함수 데이터 레이블 변경
    데이터레이블을 추가하여 차트만들기를 마무리합니다.

2. 국가별 평균매출 차트 만들기
  1. 키보드 Ctrl 키를 누른채로 국가, 평균매출 범위를 선택합니다.

    직장인 필수함수 국가 평균매출 범위
    국가범위와 평균매출범위를 동시에 선택합니다.

  2. 상단의 '삽입' - '추천차트'를 클릭하여 세로막대형그래프를 삽입합니다.

    평균매출 차트 삽입 완료
    '삽입' - '추천차트'로 세로막대형 그래프를 삽입합니다.

  3. 차트제목을 변경한 뒤, 세로축과 눈금선을 삭제합니다.

    평균매출 차트 꾸미기
    차트제목을 변경한 뒤, 세로축과 눈금선을 삭제합니다.

  4. 차트의 '+' 버튼을 클릭한 뒤, '데이터레이블'을 추가합니다.

    차트 데이터레이블 추가
    데이터레이블을 추가합니다.

  5. 차트의 채우기색상을 변경하여 차트 만들기를 마무리합니다.

    차트 채우기 색상 변경
    차트의 채우기색상을 변경하여 차트만들기를 마무리합니다.

국가별 업체 현황차트 만들기 (엑셀 2019 이후 또는 오피스 365 버전)
  1. 국가와 업체수 범위를 선택합니다.

    직장인 필수 함수 국가 업체수 선택
    국가와 업체수 범위를 선택합니다.

  2. 상단의 '삽입' - '추천차트' - '모든차트'로 이동한 뒤, '지도' 차트를 삽입합니다.

    지도 차트 삽입
    삽입 - 추천차트로 '지도'차트를 삽입합니다.

  3. 차트 제목을 변경하여 차트 만들기를 마무리합니다.

    국가별 업체 현황 직장인 필수 함수 완료
    차트제목을 변경하여 차트만들기를 마무리합니다.

4.9 19 투표
게시글평점
guest
44 댓글
Inline Feedbacks
모든 댓글 보기
김상범
김상범
2019년 12월 24일 3:36 오전

김찬호님. VLOOKUP 강의 역시 열심히 현업 파일을 열어넣고 적용 중 입니다. 늘 감사 드립니다. VLOOKUP 함수를 적용하다 궁금한 점이 있습니다만, - VLOOKUP은 찾으려는 값의 텍스트가 동일해야만 값을 찾아올 수 있는… 더보기 »

오빠두엑셀
오빠두엑셀
2019년 12월 24일 3:36 오전
답글 남기기  김상범

안녕하세요? 오빠두엑셀이에요^_^* 답변드립니다! 1. VLOOKUP 함수는 값의 텍스트가 동일해야만 값을 찾아올 수 있어요. 2. "매 출 액" 인데 "매출액"으로 참조해야 할 경우, SUBSTITUTE 함수를 사용해보시겠어요?^_^ =SUBSTITUTE(셀," ","") 을 입력하시면 "매… 더보기 »

김상범
김상범
2019년 12월 24일 8:30 오전
답글 남기기  오빠두엑셀

감사합니다!

jiwon
jiwon
2020년 1월 31일 9:17 오후
답글 남기기  김상범

댓글 추가하기안녕하세요 유투브 잘보고 있습니다 ...질문이 있습니다 🙂

E:E 대신에 E2:E35 이걸 쓰면 왜 안나올까요?ㅠㅠ

jiwon
jiwon
2020년 2월 7일 12:19 오후
답글 남기기  오빠두엑셀

= SUMIFS(E:E,C:C,V13,D:D,W13) 말입니다 ㅠㅠ

박대리님
박대리님(@-2064)
2020년 4월 24일 10:47 오전
답글 남기기  jiwon

E2:E35 할경우, 나머지 범위도 C2:C35로 해야한다고 알고있는데.. 시간이 많이 지났네요 다른 보시는 분들이라도 참고 하시기 바랍니다.

서녘별
서녘별(@-461)
2020년 2월 24일 6:15 오후
게시글평점 :
     

강의 잘 봤습니다. 너무 쉽게 설명 잘 해 주셔서 이해가 잘 되네요. 감사합니다.

소나로사
소나로사(@-982)
2020년 3월 8일 8:28 오전
게시글평점 :
     

이해가 쉽게 해주시니 감사합니다

엑엑셀셀
엑엑셀셀(@1171)
2020년 3월 20일 9:07 오후
게시글평점 :
     

1강부터 차근차근 배우고 있습니다 감사합니다

말리노
말리노(@-1179)
2020년 3월 24일 10:31 오전
게시글평점 :
     

안녕하세요! 강의 항상 감사합니다.
강의 내용중에서
= IF(ISERROR(FIND("모터",B10)),"","모터업체")
이 수식을
IFERROR 함수로 바꿀 수 있는 방법이 있을까요?
제가 혼자 하니까 자꾸 오류가 나더라구요 ㅜㅜ

Dalnim
Dalnim(@gusdn476naver-com)
2020년 5월 19일 2:32 오후
답글 남기기  오빠두엑셀

감사합니다!

노은
노은(@1365)
2020년 3월 30일 1:36 오전
게시글평점 :
     

점점 어려워 지지만 열심히 따라가보겠습니다.

닥코드
닥코드(@221-2)
2020년 4월 7일 2:14 오후
게시글평점 :
     

항상 좋은 내용 감사합니다.

루카대디
루카대디(@-1782)
2020년 4월 14일 8:17 오후
게시글평점 :
     

익숙할때까지 복습하겠습니다~

오주환
오주환
2020년 5월 3일 10:44 오전
게시글평점 :
     

IF(LEN(B2)>5,"ㅇ","X")조건식에서 IF(LEN(B2)">"&5,"O","X") 로 표현하느게 더 맞는 건가요??
수식은 문자여서 따옴표 안에 표시하는 것이 맞는거 같습니다.

올챙이
올챙이(@2095)
2020년 5월 12일 5:58 오후
게시글평점 :
     

실무 기초 3강... 수강 완료... 하나씩 배우는 재미가 있네요. 감사합니다.

modern
modern(@modern)
2020년 6월 9일 11:59 오전
게시글평점 :
     

감사합니다.

초코우유
초코우유(@-2274)
2020년 6월 13일 3:49 오후
게시글평점 :
     

잘 배워갑니다

엑셀초보
엑셀초보(@hh0323_naver-com)
2020년 6월 21일 6:13 오후
게시글평점 :
     

오빠두님 안녕하세요~! 영상의 12:40쯤, 함수를 복사붙여넣기 하는 부분에서요. 복사한 함수공식을 text 입력창에 직접 넣어야지만 기존 함수 안의 셀 값이 상대참조가 안 되는 건지요? 영상에서 W6 셀 (국가)안의 함수가 아래와 같을… 더보기 »

Last edited 1 년 전 by 엑셀초보
엑셀초보
엑셀초보(@hh0323_naver-com)
2020년 6월 21일 6:22 오후
게시글평점 :
     

앗, 이전 댓글이 뭔가 수정이 잘 안돼서 다시 올립니다ㅠㅠ   영상 12:40초쯤에 VLOOKUP 적용한 셀을 복사붙여넣기 하는 부분에서요. ① 셀을 바로 복사 붙여넣기하면 참조한 셀이나 범위가 상대참조가 되어버리네요. 복사 붙여넣기… 더보기 »

Last edited 1 년 전 by 엑셀초보
돌채
돌채(@1152)
2020년 7월 15일 9:00 오후
게시글평점 :
     

Maxifs/minifs 함수를 사용하지 못하는경우에는 어떻게 해야 하나요?!
한참을 고민해봤지마 접근 자체를 어떻게 할지 모르겠네요 ㅜ,.ㅠ

돌채
돌채(@1152)
2020년 7월 15일 9:14 오후
답글 남기기  돌채

생각하지 말고 부지런히 찾아볼껄 그랬나봐요~
이미 다 답변해 주셨었네요.. ㅎㅎ; 민망하네요...
"Max(if"같이 배열 함수로 사용하면 될 것을...
감사합니다. ^^

돌채
돌채(@1152)
2020년 7월 15일 9:42 오후
게시글평점 :
     

국가별 최고 매출을 구하는 중 금액이 아닌 "제조사"가 나오게 했으면 어떨까 싶어 아래와 같은 계산식을 사용했더니, "쌍용자동차"가 아닌 "재규어"가 나오더군요... match 결과가 13이 나와야 하는데 왜 32가 나오는지 알려주실 수… 더보기 »

돌채
돌채(@1152)
2020년 7월 16일 12:02 오후
답글 남기기  돌채

Match_type이 이렇게나 중요한지 몰랐네요~
밤에 고민하다가 낮에 끄적거리면서 알았습니다.
고민 해결입니다!! ㅋㅋㅋ 감사합니다.

오은혜
오은혜
2020년 9월 6일 1:31 오후
게시글평점 :
     

안녕하세요! 예제 내용 중 '국가별 최고/최저매출'을 구할 때 사용하는 함수인 max/minif를 연습하고있는데, 계속 오류가 납니다 ㅠ 오류내용은 ' 수식에서 입력할 수 없는 텍스트' 입니다. 제가 사용하고있는 버젼은 2016이예요! 혹시 완성본에… 더보기 »

Last edited 1 년 전 by 오은혜
오은혜
오은혜
2020년 9월 7일 10:33 오전
답글 남기기  오빠두엑셀

답변감사합니다!
알려주신 방법으로 다시해보았는데도 오류가뜹니다...ㅠㅠㅠㅠ

수식입력 후 Cntrl+Shift+Enter를 눌렀는데도요..ㅠ

오은혜
오은혜
2020년 9월 7일 10:58 오전
답글 남기기  오빠두엑셀

제가 보내주신 수식을 잘못 이해했습니다..저는 똑같이 수식을 넣고 단순 엔터가 아닌 컨트롤 쉬프트 엔터만 누르면된다고 생각했어요! 그러나 알려주신 아래와 같이 입력했는데..오류가납니다 ㅠ { =MAX(IF((조건범위1=조건1)*(조건범위2=조건2).., 최대값범위)) } ' 배열수식이므로 CTRL + SHIFT +… 더보기 »

미성
미성(@salaing9800naver-com)
2020년 11월 5일 4:07 오후
게시글평점 :
     

잘 배우고 갑니다

김대성
김대성(@k8327917naver-com)
2021년 7월 19일 3:23 오후
게시글평점 :
     

국가/직원수(다중조) 평균매출은왜 월평균매출을 기준으로 사용하지 않는건가요??

잘하고파
잘하고파(@-7161)
2021년 8월 14일 12:12 오후
게시글평점 :
     

오~~~너무너무 좋네요...잘 활용해보도록 하겠습니다.

어려운 엑셀
어려운 엑셀(@7301)
2021년 10월 4일 10:58 오전
게시글평점 :
     

안녕하세요 강의 잘 보고 있어요 궁금한 게 있습니다 v lookup 함수 4번째 총 매출액 때 폭 스 바 겐 기준으로 하면 값이 4,151,00 로 나와야 하는데 4151 이렇게 만 나오네요… 더보기 »

Last edited 2 월 전 by 어려운 엑셀
어려운 엑셀
어려운 엑셀(@7301)
2021년 10월 5일 1:28 오후
답글 남기기  오빠두엑셀

수동 설정 해야 하는군요 감사합니다!

나도_엑셀
나도_엑셀(@_-8)
2021년 10월 6일 4:10 오후
게시글평점 :
     

쩝. 그림의 떡.

토백이
토백이(@21yongyonghanmail-net)
2021년 11월 28일 4:43 오후
게시글평점 :
     

기초부터 실무예제까지 상세한 강의 고맙습니다^^

44
0
여러분의 생각을 댓글로 남겨주세요.x