[엑셀함수정리] 실무에서 쓰이는 엑셀 함수 (+엑셀 중복값 찾기부터 중복값 제거까지)

안녕하세요. 설도 벌써 일주일이 거의 다 지나갔네요! 시간이 정말 빨리 지나가는 것 같습니다 ㅎㅎ 이번 포스팅은 직장에서 실무에서 많이 쓰는 엑셀 함수 정리해서 알려드리려고 합니다. 엑셀 함수는 이제 누구나 할 줄 알아야할정도로 중요한 기본 능력중 하나로 여겨지고 엑셀 함수 정리를 보고 배워가야 하는 이유가 이제 웬만한 사무직에서 일하는 직무면 백이면 백 사람 뽑을 때 우대조건으로 컴퓨터 잘 다루는 사람이거나 엑셀을 잘 다루는 사람을 넣잖아요. 엑셀을 잘 다루면 업무속도가 원활해져서 업무처리가 다른 신입에 비해 훨씬 빠르기 때문인것같아요. 그만큼 엑셀에는 시간 단축을 위한 좋은 기능이 많다는 말이겠죠? 제 생각엔 몰라서 못쓰는건 있어도 없어서 못쓰는 기능은 없을 정도 아닐까요? 직장에 들어가면 엑셀을 많이 쓸 텐데. 요즘은 엑셀 자격증이 사무직 취준생에겐 거의 필수라서 대부분이 자격증은 갖고 있겠지만, 그중에서 엑셀에 대한 실무적인 부분은 얼마나 많이 알고 있을까요? 저도 자격증 공부하면서 딸 때는 재미 들려서 풀었었는데. 따고 나서는 엑셀을 전혀 안 쓰다 보니까, 그나마있던 자신감 마저 사라졌습니다 ㅠㅠ 포스팅을 준비하면서 다시 기억을 살리긴 했지만요! 그럼, 엑셀 함수 정리와 엑셀 중복값 찾기 및 제거에 대해서 바로 알아보시죠!

 

 

함께 보면 좋은 포스팅: [실무에서 배운] 엑셀 파일 합치기│엑셀 표 만들기

 

 

 

엑셀함수정리 - SUMIF 함수

SUMIF

=SUMIF(범위,"조건",합계범위)

범위에서 조건을 검사하여 합계 범위에 해당하는 셀의 합계를 구하는 함수 식입니다. 흑자국와 적자국이라는 조건으로 각 나라별로 구분해서 합계를 구해줘야 하는데요. 풀이는 아래에서 확인하실게요.

SUMIF

=SUMIFS(합계를 구할 범위, 범위1, "조건1", 범위2, "조건2"...)

SUMIF함수는 조건에 취합하는 셀만 골라서 합계를 구하는 함수 식인데요. 우리가 평소에 SUM함수 식은 정말 많이 쓰면서 일일이 범위나 셀을 클릭하면서 하잖아요? 아닐수있는데 저는 그랬습니다 ㅋㅋ하나하나 클릭하면서 엄청 오래걸렸죠. 그래서 이제는 범위와 조건만 넣으면 그런 수고를 덜어줄수있는 SUMIF 함수식 배워가서 활용하시길 바랍니다! 만약 조건이 하나 이상인 값의 합계를 구하려고 한다면 여러 조건을 만족하는 셀의 합계를 구할 수 있는 SUMIFS함수 사용하셔야 합니다. 구하는 방식은 SUMIF와 다르지 않지만 제2의 조건이 들어가야 하는 범위와 조건을 필요한 만큼 설정해주셔야 합니다. 사진에 나오는 계산식에서 보시면 범위를 E4:E11로 잡아두었습니다. 여기서 저는 앞에 $ 붙여서 범위가 변하지 않게 설정해놨습니다. 조건은 흑자국끼리만 계산하는 거라 0보다 이상인 수로 했습니다. 조건을 넣을 때 Fx를 눌러서 나오는 창에서는 조건을 넣을 때 "큰 따옴표"를 안 해도 되지만 그냥 바로 식을 적을 땐 안 넣으면 결과가 안 나오니까 주의해주세요! 

 

 

 

엑셀함수정리 - COUNTIF 함수

COUNTIF

=COUNTIF(영역,"조건")

영역에서 조건에 만족하는 셀의 개수를 구하는 함수 식입니다. 정가 2만원 이상 책 권수를 countif함수를 사용해서 갯수를 파악해볼게요. 풀이는 아래 참고해주세요.

 

COUNTIF

=COUNTIFS(합계를 구할 범위, 범위1, "조건1", 범위2, "조건2"...)

COUNT함수 또한 SUM함수와 마찬가지로 쉬우면서 많이 쓰는 함수 식인데요. COUNT함수는 뒤에 뭔가 붙어서 파생된? 함수 식이 정말 많아요.  공백의 셀 개수를 새는 COUNTBLANK와 비어있지 않는 셀의 개수를 알려주는 COUNTA 그리고 대표적으로 많이 쓰이는 지금 배우고 있는 COUNTIF도 있습니다. COUNTIF 함수 식도 SUMIF와 같이 만약 조건이 하나 이상인 셀의 개수를 구하려고 한다면 여러 조건을 만족하는 셀의 합계를 구할 수 있는 COUNTIFS함수 사용하셔야 합니다. 구하는 방식은 SUMIFS와 같이 제2의 조건이 들어가야 하는 범위와 조건을 필요한 만큼 설정해주셔야 합니다. 여기서도 범위 영역 부분을 정하고 나서 절대 참조를 했습니다. 저는 안 해도 되는 건 안 하지만 웬만하면 넣는 게 편하더라고요. 드래그해서 복사할 때 서식이 바뀌어서 결과가 잘못 나올 수도 있으니까요 ㅠㅠ 여기서 조건도 "큰 따옴표" 넣는 것만 주의해주시면 될 것 같네요.

 

 

 

 

엑셀함수정리 - MID 함수

MID

=MID(텍스트,시작위치,개수)

텍스트의 시작 위치부터 지정한 개수만큼 표시하는 함수 식입니다. 주민번호로 회원의 성별을 구분해볼건데. 풀이는 아래 참고해주세요.

MID

MID함수는 텍스트에서 시작 위치를 지정해주고 원하는 개수만큼의 텍스트를 뽑아오는 함수 식입니다. 이번에 사용한 함수 식은 뭔가 한개만 쓰기에는 사용용도를 잘 모를것 같기도해서 IF함수와 같이 연계해서 사용해봤습니다. 주민번호를 보고 남자인지 여자인지 자동으로 셀에 입력이 되게 만들기 위해서 논리값에 MID함수를 이용해서 C4셀의 텍스트의 8번째 자리에서 시작하는 한개를 가져오게 했습니다. 주민등록증에서 남, 여를 구분하는건 뒷자리 첫번째가 1인지 2인지 잖아요? 근데 요즘은 3이랑 4도 써야할것같기도하네요 ㅋㅋ 그리고 하이픈"-"도 텍스트로 구분되기 때문에 시작위치는 8번째로 잡은겁니다. 여기까지했으면 MID 함수 사용은 끝인데 결과값이 1이라고 논리값에 넣어뒀기 때문에 참이면 남자고 거짓이면 여자로 나와게 됩니다. 아 그리고 비슷하게 사용되는 함수는 LEFT와 RIGHT 함수입니다.

 

 

 

 

 

 

엑셀함수정리 - RANK 함수

RANK

=RANK(찾는값,찾는값의범위,순위를정할방법)

수 목록 내에서 지정한 수의 크기 순위를 구하는 함수식입니다. 총점으로 10개 팀의 순위를 결과로 나타내주는 문제네요. 풀이는 아래에서 확인해주세요.

RANK

RANK함수는 말 그대로 범위 내에서 순위를 지정한 값의 순위를 찾을 때 많이 쓰는데요. RANK함수뿐 아니라 컴활 실기시험에서는 CHOOSE함수와 같이 사용해서 1~3등까지 '본선 진출'이라는 결과값을 나오게도 사용합니다. 이번 함수에서 주의할 것은 내림차순과 오름차순 정도인데요. 수영이나 육상 같은 종목은 낮은 점수가 1등으로 기록되어야 하잖아요. 그래서 [Order] 부분에 오름차순인 1을 설정해주셔야 합니다. 나머지 큰 수가 높은 순위를 가지는 것들은 그냥 생략을 하셔도 좋고 아니면 0을 입력하셔도 됩니다. 

 

 

 

엑셀함수정리 - IFERROR 함수

IFEEROR

=IFERROR(정상수식,오류발생내용)

함수 식 식 자체의 값이 오류인 경우 Value_if_error를 반환하는 함수 식입니다. 원서번호로 해당 학생이 어느 학과에 지원했는지 나타내는 문제입니다. HLOOKUP과 연계해서 풀이하는 문제네요.

IFERROR

IFERROR는 값이나 식에 오류가 있는 셀에 Value_if_error부분에 입력한 텍스트가 나오게 하는 식입니다. IF와는 다르게 FALSE일 때 값만 입력하기 때문에 어려운 것도 없습니다. 여기도 "큰 따옴표" 까먹지 말고 주의해야 하는 정도 같네요. 이번 식에서는 HLOOKUP과 같이 연계했는데. A라는 코드에 맞는 학과명이 없어서 '-하이픈' 처리하기 위해서 IFERROR함수 식을 사용해서 식을 완성했습니다.

 

 

 

엑셀함수정리 - VLOOKUP 함수

VLOOKUP

=VLOOKUP(찾는값,표범위,열번호,찾을방식)

열 범위에서 원하는 값을 추출하는 함수 식입니다. 대출기간에 따른 수수료율로 고객의 지급수수료를 찾는 문제입니다. 문제풀이는 아래를 참고해주세요.

 

VLOOKUP

VLOOKUP은 실기시험칠 때도 그렇고 실무에서도 그렇고 진짜 많이 사용되는 함수 식으로 알려져 있습니다. VLOOKUP에서 V는 Vertical 수직의 약자입니다. 그래서 표범 위에서 찾을 값을 열 번호를 입력해서 값을 구하는 방식으로 사용되는 거죠. VLOOKUP은 사실 간단하게 하는 팁은 없고 직접 몇 번 해보면서 감을 익히는 게 중요한 것 같아요. 하나 정도 있다면, 표범 위부분 절대 참조를 꼭 해주셔야 한다는 것 정도? 절대참조를 안 하고 식을 완성 후에 드래그로 복사해버리면 범위에 입력된 셀 범위도 한 칸씩 내려가서 값이 안 나옵니다. 찾을 방식에서 정확한 값을 찾으려고 하려면 생략이나 0 또는 false를 입력하셔도 되고 근삿값을 찾을 때는 1 또는 true를 입력하시면 됩니다.

 

함께 보면 좋은 포스팅: [엑셀단축키모음] 엑셀에서 많이 쓰이는 기능과 엑셀 단축키까지 알아보자!

 

 

 

 

엑셀함수정리 - HLOOKUP 함수

HLOOKUP

=HLOOKUP(찾는값, 표범위, 행번호, 찾을방식)

행 범위에서 원하는 값을 찾는 함수 식입니다. VLOOKUP과 동일한 함수인데. 표가 수평이란 차이점이 있네요. 자동차 코드로 차량명을 찾는 문제입니다.

HLOOKUP

HLOOKUP도 VLOOKUP과 마찬가지로 실기시험에서나 실무에서나 정말 많이 사용되는 함수 식입니다. HLOOKUP에서 H는 VLOOKUP과 같이 Horizon 수평의 약자이고 표범 위 부분을 보면 데이터가 열이 아닌 행으로 길게 입력되어 있는 걸 알 수 있습니다. HLOOKUP도 VLOOKUP과 사용 방식은 별반 다르지 않아. VLOOKUP 사용하는 것에 익숙해지면 저절로 될 것 같네요. 찾을 방식에서 정확한 값을 찾으려고 하려면 생략이나 0 또는 false를 입력하셔도 되고 근삿값을 찾을 때는 1 또는 true를 입력하시면 됩니다. 

 

 

 

엑셀함수정리 - REPLACE 함수

REPLACE

=REPLACE(바꿀텍스트,시작위치,바꿀개수,새 텍스트)

텍스트의 일부를 다른 텍스트로 변환하는 함수 식입니다. 주민번호와 전화번호의 일부를 다른 텍스트로 변경해주는 문제네요.

REPLACE

REPLACE 함수 식은 텍스트에서 원하는 부분의 텍스트를 내가 바꾸고 싶은 대체하는 함수 식입니다. 대부분의 함수 식이 그렇듯 Replace도 대체하다 라는 뜻을 가졌는데. 뜻 그대로 사용돼서 어렵지 않네요. 사진에서 사용된 함수는 보통 주민등록번호는 뒷자리가 보안으로 사용되니까 8번째 자리에서 시작해서 뒷자리 7개를 바꾼다고 설정해뒀네요. 전화번호 같인 경우는 뒷자리로 본인 확인을 많이 하기 때문에 가운데 번호를 보안 처리하기 위해서 5번째에서 시작한 4자리 글자만 *표시로 수정하게 했습니다. REPLACE함수는 주민번호나 전화번호 보안으로 별 표시를 해야 할 때 이럴 때 많이 사용되는 것 같아요. 일일이 가져와서 별로 바뀌는 것도 사실 가능은 하겠지만 그것 만큼 시간낭비가 어딨을까요 ㅋㅋ 쇼핑몰 주문내역이나 회원관리 이런 거 할 때 사용하기 좋습니다.

 

 

 

엑셀함수정리 - 엑셀 중복 값 찾기

중복값찾기

 

[홈] 탭 - [조건부서식] - [셀 강조 규칙] - [중복 값]

엑셀 중복 값 찾기는 조건부 서식을 이용하는 것 말고도 다른 것도 있는데. 가장 간단하게 이용할 수 있는 건 조건부 서식이라서 이걸로 알려드리려고 가져왔습니다. 조건부 서식을 이용할 때 중복 값을 찾고 싶은 부분을 범위 설정을 하신 다음에 홈 탭에 있는 조건부 서식에서 셀 강조 규칙에 들어가면 있는 중복 값 부분을 누르면 바로 중복 값인 셀끼리 지정한 색깔로 표시가 됩니다.

 

 

 

 

 

엑셀함수정리 - 엑셀 중복 값 제거

중복값제거

[데이터]- [중복된 항목 제거]

 

엑셀의 기능에는 중복 값 찾기도 있지만 중복 값 제거도 많이 쓰이죠. 중복 값을 찾은 다음 직접 지우는 불편함을 없앨 수도 있고 좋습니다. 중복 값 제거도 범위를 설정한 다음에 [데이터] 탭에서 오른쪽 부분에 보시면 중복된 항목 제거가 있습니다. 저는 원서 번호, 이름, 거주지, 지원학과까지 모두 지워야 하니까. 모든 카테고리를 체크된 상태로 확인을 눌렀는데요. 사진에서 보시는 바와 같이 가장 아래 있던 중복 데이터가 사라졌습니다. 중복 값 제거 기능도 있지만 중복 값을 합치는 기능도 있으니까. 참고하세요!

 

 

지금까지 엑셀 함수 정리와 기능을 알아봤는데요. 몇 가지 기능을 더 준비했는데. 쓰다 보니까 길어져서 그냥 2탄으로 포스팅을 만들어야겠다는 생각이 드네요. 도움이 되셨으면 좋겠습니다. 포스팅 끝!