[Excel] 업무에 필수적으로 사용되는 함수와 활용

원하는 부분 추출/대체하기
LEFT, RIGHT, MID, REPLACE, CHOOSE, SUBSITUTE

ex. 주민번호에서 생년월일, 뒷자리 추출

 

 

1. 생년 : 왼쪽에서 숫자 2개와 '20' 이라는 숫자 추가

LEFT 함수 > 함수 인수 대화상자

 

Text : 추출할 데이터가 있는 셀

Num_charts : 몇개를 추출할 것인지

 

Num_charts를 2로 하고 앞에 '20&' 붙이기!!

 

 

 

2. 주민번호 뒷자리

RIGHT 함수 > 함수 인수 대화상자

 

 

3. 월, 일

중간에 있는 함수인 MID 이용

 

Start_num : 추출을 시작할 부분 (3은 3번째 숫자부터 라는 의미)

 

참고

문자열을 연결해줄때는 &" " 이렇게 큰따옴표안에 문자를 넣어야 함

 

 

4. 이름 가운데 글자 처리 (이름이 2글자라면?)

REPLACE 함수

 

Start_num : 대체 처리할 숫자의 시작점

Num_charts : 대체 처리할 숫자의 개수

Num_text : 대체 처리할 내용

 

 

5. 성별 구분

CHOOSE 함수

 

 

 

6. 주민번호에서 하이픈 없애기

SUBSTITUTE : 특정 문자를 찾아 다른 문자로 변경

* SUBSTITUTE로 구한 함수는 텍스트 형식을 유지 

 

 

날짜 함수 사용법

1. 오늘로부터 남은일/지난일 수 등을 찾고 싶으면?

TODAY 함수 : 현재 날짜를 구함

사용자 지정에 0"일"로 표시한다고 해야지 며칠 남았는지 뜸!

 

 

2. 날짜형식일 경우 년월일을 추출할 때는 '날짜함수'를 사용해야 함

YEAR / MONTH / DAY

Ctrl + ; 현재 날짜

 

 

3. DATEDIF (시작날짜,끝날짜,Unit) : 두 날짜간의 간격을 계산

Unit : 시작 날짜와 끝 날짜 사이의 간격 

 

 

VLOOKUP 함수 : 조건에 맞는 데이터를 표에서 찾아오는 함수

EX. 학생정보와 일치하는 연락처 찾아오기

 

Lookup_value : 조건에 해당하는 찾으려는 값  - ex. 학생정보

Table_array : 데이터를 가져올 부분을 첫열로 해서 설정  -  ex. 연락처가 있는 표  (수식을 복사해도 참조셀이 변하지 않게 하기!)

Col_index_num : Table_array로 지정된 부분에서 몇번째 부분인지

Range_lookup : 정확하게 일치하는 값을 찾으려면 False, 비슷하게 일치하는 값을 찾으려면 True

(False는 0으로 대신 입력 가능)

 

 

수식 적용 후 예상치 못한 값이 적용된다면 '표시 형식'이 어떻게 되어 있는지 먼저 살펴보기

ex. 연락처 형식으로 넣고 싶으나 숫자로 표현 => 000-000-0000 (0은 숫자를 나타냄)

 

Tabble_array 이름을 설정할수도 있음

이름상자에 원하는 이름을 설정하면, Table_array에 해당 이름을 넣어도 값이 추출됨!

 

 

INDEX, MATCH 함수

Vlookup함수는 첫열에서 값을 선택해서 오른쪽 방향으로 2,3... 열에 해당하는 값을 가져옴

만약 가져오려는 데이터가 왼쪽에 있으면 못 가져옴  - ex. 이름, 소속, 직위

 

=> INDEX, MATCH함수 사용

 

 

INDEX 함수 : 범위에서 행과 열이 일치하는 값을 찾는 함수

Row_num : 찾으려는 값의 행 번호

Column_num : 구하려고 하는 열 번호

 

 

MATCH 함수 : 행번호를 구하는 함수

Lookup_value : 찾고자 하는 값

Lookup_array : 찾고자 하는 값을 찾는 셀 범위

 

=> 두 함수를 동시에 이용하면 원하는 값이 있는 행번호를 찾기 > 그 행번호에 해당하는 함수 가져오기 가능

Row_num에 MATCH 함수 사용

 

 

ex. 주민번호에 해당하는 이름 찾기

1. MATCH : 주민번호 이름 상자에서 B3이 있는 값의 행번호를 찾기

2. INDEX 함수

직원 명부 이름상자의 범위에 있는 값 중에서 B3셀 주민번호가 있는 행의 이름 열이 있는 부분(1열)의 값을 가져옴

 

 

일치하는 데이터와 불일치하는 데이터 추출
고급필터 기능 + COUNTFITS

 

EX) 2월과 3월 모두 거래가 일어난 거래처 구하기!

 

고급필터를 사용하려면 시트에 조건식을 입력해야 함! 

2월과 비교했을때 3월에도 있는 거래처를 찾아야 함

COUNTFITS 함수 > 2월 거래처명 절대 참조 > 3월 첫 셀 참조 

= 2월 거래에 비해서 3월에는 몇 번 있는지 수가 나옴 

=> 2월 3월 모두 거래가 일어났다면 COUNTFITS값은 1 이상이어야 함

=> COUNTFITS 함수>=1

 

고급필터를 이용해서 데이터를 추출! (데이터-고급)

이렇게 조건 범위를 설정해야하기에 셀에서 조건 필드를 하나 만들어놔야함!

 

 

 

일반 수식으로 원하는 값을 구할 수 없는 것을 <배열수식>으로 해결

EX. 비고가 없는 구역의 면적 합계를 구하기

SUMFITS 함수 사용

 

Sum_range : 실제 합을 구할 범위 (면적 영역)

Criteria_range : 조건이 맞는지 검사하는 범위 (비고 영역)

Criteria : 조건을 지정하는 인수 (공백을 찾아야 함=> " " - 공백을 의미)

 

EX2. 비고가 있는 값을 구한다면?

Criteria에 공백을 뜻하는 " " 이 아닌, 같지 않다는 연산자의 <> 을 입력 but SUMFITS 함수에서는 안 됨 => 배열 수식 이용

 

배열 수식 활용

D3:D9 <>"" : D3~D9 셀이 비어있지 않다면

*C3:C9 : C3~C9를 조건에 곱하라! (TRUE는 연산으로 하면 1, FALSE는 0임!)