직장인 엑셀 실전에서 많이 쓰이는 함수 모음 #.1 - 논리, 날짜시간, 문자, 수학, 찾기

오랫만에 돌아온 엑셀 시간이다.

직장인으로서 엑셀은 뗄레야 뗄 수 없는 

존재임과 동시에 작업 효율화와 동시에

딜레마를 가져오게 하는 존재 중 하나이다.

오늘은 그런 엑셀 중에서 실전에서 제일 많이 사용되는 

내용들을 핵심적으로 가르쳐 주는 시간인데

그 중에서 논리 조건문, 날짜 및 시간, 문자, 수학, 찾기에

대한 내용 핵심적으로 간단하게 설명하려고 한다.

구구절절 길게 설명된 것보다 간단하고 핵심

스파르타식으로 회사에서 엑셀 속성 이론을 써먹어야

할 때 매번 열람하면 좋을 것이다.

필자는 함수 수식에 어려운 용어가 아닌

일반인도 이해하기 쉬운 단어를 넣었다.

물론 정석은 아니지만 속성 스파르타 엑셀은

이래야 하니까 말이다.

1. IF 조건문 AND , OR , NOT 조건문

첫째 줄은 다중 IF조건문이라고 보면 된다.

IF문은 간단하다

IF(조건, 맞다면 출력할 값, 틀리다면 출력할 값)

이렇게 되는데 출력할 값에 추가 조건을 더 넣을 수 있다.

예를 들면 A1의 값은 15이다.

A1의 값은 19보다 작으니까 틀린 조건이다.

하지만 틀렸을때 출력할 값에 조건이 또 하나 들어가 있다.

틀렸을때 조건문은 A1은 10보다는 크다는 것이다.

결국 맞는 값이기 때문에 B를 출력한다.

두번째 줄은 AND 조건문이다.

모든 조건에 맞아야 긍정의 값이 출력되는 것이다.

IF(AND(조건1, 조건2), 맞다면 출력할 값, 틀리다면 출력할 값)

A1의 값은 15

AND 조건인 A1은 20보다도 작다. 

거기다가 B1은 공백이기에 30보다 작다.

그렇기 때문에 모든 조건에 부합하지 않기 때문에 B를 출력한다.

세번째 줄은 OR 조건이다.

둘 중 하나라도 조건이 맞으면 긍정의 값을 출력한다.

IF(OR(조건1,조건2),맞다면 출력할 값, 틀리다면 출력할 값)

A1은 15, B1은 공백

둘도 조건에 부합하지 않기 때문에 B를 출력한다.

2. 시간 함수

첫번째 줄부터 설명을 한다.

첫번째는 파일을 열었을때 오늘 날짜를

출력하는 것이다. 

두번째 줄은 특정 값을 이용해서 

날짜를 표현하는 것이다.

DATE(년,월,일) 이 순서라고 보면 된다.

세번째 줄은 시간까지 출력되는 것이다.

네번째 줄은 특정값을 

TIME(시,분,초)로 표현하는 것이다.

역시 두번째 줄의 DATE함수와 같이  

미리 만들어진 특정 값으로 이용하는 것이다.

이 함수들은 간단하다

첫번째 줄은 TIME셀의 값의 시간값만

두번째 줄은 TIME셀의 분값

세번째 줄은 TIME셀의 SECOND값만 

출력해주는 값이다.

6번째 줄의 DAYS360함수는

특정 날부터 특정날까지 며칠 정도 되는지

계산해주는 값이다.

6번째 줄의 DAYS360(시작 날짜, 종료날짜)

결과는 7번째 줄의 결과값 5이다.

9번째 줄은 특정날짜의 요일을 알려주는 함수이다.

WEEKDAY(날짜,1)을 입력한다

두번째 인수값은 특정 요일을 메인 날짜로

바꾸기 위한 것인데 사실 잘 안써먹기 때문에 1로 그냥 놓는다.

그렇게 되면 2020년 10월 25일은 일요일이기 때문에

일요일값인 1로 출력이 된다.

만약 월요일이었다면 2, 토요일이었다면 7이 나온다.

3. 문자 나누기 함수 MID, LEFT , 

RIGHT, LOWER, UPPER, PROPER, TRIM

문자 관련 함수이다.

첫번째 함수는 MID(단어 위치, 시작 자리, 몇글자 추출 할건지)

이렇게 생각하면 된다.

SIMYEONG의 첫번째 글자인 S부터 시작해서 3글자를

추출하는 것이기에 정답은 SIM이다.

두번째 함수는 LEFT와 RIGHT이다.

LEFT(단어 위치, 몇글자 추출)

RIGHT(단어 위치, 몇글자 추출)

함수의 이름에 걸맞게 LEFT는 맨 좌측부터 추출

RIGHT는 맨 우측부터 추출이다.

그렇게 예제대로 하면 SIMYEONG기준

LEFT(단어위치,3)은 SIM

RIGHT(단어위치,5)는 YEONG 이다.

LOWER(단어 혹은 셀 위치)은 대문자를 소문자로 변경

UPPER(단어 혹은 셀 위치)는 소문자를 대문자로 변경

PROPER(단어 혹은 셀 위치)는 첫번째 글자만 대문자로 변경

TRIM(단어 혹은 문장 혹은 셀 위치)는 공백을 다 지워주는 역할을 한다.

4. 효율적인 합과 계산하기 SUM , SUMIF, MOD

첫번째 줄은 SUM으로 SUM(시작셀:종료할 셀) 까지의 합을 

구하는 것이다.

두번째 줄은 SUMIF(조건을 따져볼 구간, 일치 조건 , 합할 구간)

을 의미한다.

B1에서 B10까지 취업이 써있는 셀의 A1~A10까지의

합을 구하는 것이다.

결국 1,3,5,7,10만 합계가 이뤄지기에

결과는 26이 나온다.

세번째 줄은 MOD함수인데 

MOD(값,나눌값)을 넣어서

나머지 값을 출력해주는 함수이다.

중간에 짤려서 다시 수식을 표현하자면

MOD((SUMIF(B1:B10,"취업",A1:A10),10)이다.

26의 10을 나누면 나머지 값은 6이 된다.

 

5. 잊기쉽고 어려운 VLOOKUP 함수

예제는 이렇게 보도록 하겠다.

제일 어렵고 짜증나는 것 중 

하나가 바로 VLOOKUP인데 해보면 간단하다.

VLOOKUP(비교하려는 셀의 위치, 비교해볼 대상들의 셀 모든 값, 열번호, 비교방법)

이렇게 된다.

- 첫 번째 인자 (D3) : 찾고자하는 기준이 되는 사람 코드가 B2에 있다.

- 두 번째 인자 ($F$2:$G$5) : 값을 찾을 범위를 지정한다. 

범위의 1 열에서 값을 찾는다.

여기서는 코드가 그 열이다. 

범위를 절대주소로 정해야한다( $로 표기된 것)

자동채우기를 해도 주소변경이 안되게끔 커버를 해준다.

- 세 번째 인자 (1) : 1열에서 기준에 맞는 값을 찾으면 1열의 값을 반환하라는 의미 이다.

- 네 번째 인자 (FALSE) : 정확히 일치할 때만 값을 반환하라는 뜻이다.

TRUE는 근소하게 일치할때도 값을 반환하는 의미이다

그의 결과값은 윗 사진의 아래 엑셀표이다.

6. 언젠가는 써먹게 되는 INDEX , MATCH 함수

우선 INDEX 함수부터 보도록 하겠다.

INDEX함수는 특정 셀 범위에서 특정된 좌표의 값을

가져오는 것이라고 보면 된다.

뜻은 다음과 같다.

INDEX(범위 , 행, 열)

2행 3열의 값을 가져온다는 의미라고 보면 된다.

주로 특정 좌표에서 특정 값만 가져오려고 할때 많이 쓴다.

위에서는 숫자의 3행 3열로 되어 있는데

2행인 2016년에 3열에 있는 상하이조 숫자값이니까

1505가 나온다.

MATCH는 특정 셀 범위에서

일치하는 값의 위치를 보여주는 함수이다.

물론 완전한 셀 주소가 아닌

특정 범위의 위치이기 때문에

주로 특정 값의 위치 찾는 용도로 사용된다.

MATCH(찾아낼 값, 찾아낼 범위, 일치)

MATCH("의사양반",C2:E2,0)

0은 완벽히 일치하는 값의 인덱스값을 찾아내도록

하는 것이다.

결과값은 두번째 열에 있었기에 2가 출력된다.

전문가처럼 설명하는 것이 아닌

정말 핵심만 빠르게 업무에서 쓰려고 하는

목적으로 간략하게 설명한 것이기 때문에

심층적으로 들어간다기 보다는

급하게 특정 용도로 쓰기 좋은 엑셀 함수 수식으로

생각하면 좋을 듯 하다.