[엑셀] 유용한 엑셀 함수

엑셀을 다루다보면 요긴하게 사용할 함수들을 알아둘 필요가 있다.

앞으로 유용하게 사용할 함수들을 공부하면 알기 쉽게 정리하고 추가할 생각이다.

 

셀에 나온 내용중에서 특정구간을 유용하게 이용하고자 하는 경우에는

concatenate 함수, mid 함수를 사용하여 원하는 내용을 발췌 하면 좋다.

 

셀의 내용중에 불필요하게 들어간 공백을 제거하고 싶다면 

trim(A1) 함수를 이용하면 된다.

 

선택하여 붙여넣기 - 값 을 편하게 입력하려면....

복사하기 : Ctrl + C

붙여넣기 : Alt + HVV  (값 붙여넣기)

 

중복검사

내용 : 특정구간에 데이터가 1000개가 있고, 다른 셀에 특정값이 있을 때 다른 셀의 값이 특정구간에 존재하는지 여부 검사

함수 : IF(COUNTIF(조건구간,비교하고자하는 셀),"중복","신규")

 

자체셀에서 중복 값, 문장 찾아내기

IF(COUNTIF($A$3:$A$8,A4)>1,"중복","")

이건 일단 오름차순 정렬을 먼저하고 하는게 중복값 비교도 되고 용이하다.

 

VLOOKUP(찾을 값,배열,반환할 값의 열이 몇번째인가, FALSE)

첫번째 열에서 일치하는 값을 찾은 다음, 오른쪽 몇번째 값을 가져와라

다시 말하면, 배열에서 찾을 값과 일치하는 행을 반환한다. (행을 찾는다)

** 배열은 첫번째 열

    배열보다 왼쪽에 있는 값은 반환이 불가능

 

찾는 값보다 왼쪽에 있는 배열도 찾기를 원한다면 INDEX/MATCH 함수를 사용하라!!!

INDEX(원하는 값 조건범위 문자열, MATCH(찾는값,찾을 조건범위 문자열,0))

조건을 만족하는 하나의 값만 출력 가능

 

INDEX(array,행,열)

열의 값을 입력하지 않으면 자동으로 1열이 반환된다. 즉 이 경우에는 INDEX(array,행)

행을 찾으려면 MATCH 함수를 이용한다. 

MATCH 함수는 지정한 값의 첫번째 위치값을 반환받는 함수

MATCH함수는 지정한 값을 배열에서 찾아 상대 위치를 구해준다.

*형식: MATCH(lookup_value,lookup_array,match_type) - lookup_value: 데이터 테이블에서 찾고자 하는 값입니다. - lookup_array: 찾으려고 하는 값이 포함된 데이터 테이블 범위입니다. - match_type: 찾는 방법을 지정하는 옵션으로 숫자 -1, 0, 1이 있습니다. 1 lookup_value보다 작거나 같은 값 중에서 최대값 반환 0 lookup_value와 같은 첫째 값 반환 -1 lookup_value보다 크거나 같은 값 중 가장 작은 값을 반환

 

 

  * match(현재 SHHET의 찾고자 하는 값, 찾고자 하는 값과 동일 값이 들어간 (타 SHEET) 배열, 0) 처럼 입력해야 함

     index 에서 찾는 배열은 macth 함수에서 사용하는 배열보다 더 넓은 범위의 배열

Vlookup 과 Match 함수를 사용하여 다른 곳의 값을 참조하는 경우 꼭 알아둬야 할 것은 

해당 함수가 대/소문자를 구별하지못하기 때문에.. 

대/소문자를 구별해야 할 경우 이 두 함수를 사용하지 말아야 한다는 것

 

만약 N/A 라는 것이 나오는 것을 나오지 않게 하고 싶다면

IFERROR(INDEX($A$2:$B$41,MATCH(G3,$B$2:$B$41,0),1),"") 와 같이 IFERROR 함수를 사용하라 

 

FIND 함수

FIND(찾고자 하는 값, 찾는 값이 들어간 셀,1) = 찾는 값이 들어간 셀의 시작점 위치를 반환

원하는 게 있는 것지 여부라면 =COUNT(FIND({"지우개","연필","볼펜"},B3))

피벗 작업을 위한 거라면 IF(COUNT(FIND({"지우개","연필","볼펜"},B3))>0,1,0)

 * 찾는 값이 하나라도 들어가면 1로 표기하고, 안들어가 있으면 0으로 표기하라

만약 값을 기록하도록 하고 싶다면 MID함수랑 같이 사용한다.

LEFT(찾는 값이 들어간 셀, 시작점에서의 숫자길이) 
   LEFT(A1, VALUE(FIND("-",A1,1)-1)) : 구분자 "-"를 기준으로 왼쪽의 값을 표시하라 
MID(찾는 값이 들어간 셀,시작점,시작점부터의 숫자길이) 

   MID(찾는 값이 들어간 셀, FIND(찾고자하는 값, 찾는 값이 들어간 셀,1), 숫자길이)
RIGHT(찾는 값이 들어간 셀, 오른쪽에서부터의 숫자길이) 
   RIGHT(A1,LEN(A1)-FIND("-",A1,1)

 

SUBSTITUTE(찾는 값이 들어간 셀, old text, new text, instance num)

  * instance num : 몇번째 old text 를 new text 로 바꿀 것인지 지정하는 숫자, 생략도 가능 

예제 : =MID(A2,FIND("ㅜ",SUBSTITUTE(A2," ","ㅜ",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),1),100)

  의미 해석 : 공백으로 떨어져 있는 값중에서 마지막 공백의 오른쪽 값을 반환하라

  FIND 함수를 통해 시작점 위치를 찾는다. 시작점으로부터 길이는 잘 모르지만 최대한 길게 잡아 100으로 설정했다.

  찾고자 하는 값이 공백이 몇번째 공백인지 여부를 찾기 위해서

  특정 값으로 대체하고 LEN 함수를 이용하여 intance num 값의 위치를 찾는다. 

 

특정기호로 된 것을 구분하는 것은

 데이터 -> 텍스트나누기 함수를 이용하면 편하다

 가령 구분자가 > 로 된 경우가 여러개 존재하는 경우 나누고자 하는 수만큼 열을 생성한 다음에  

 이 함수를 사용하면 쉽게 나누어진다. 

 

자리수를 4자리로 표현하면서 0001, 0002 등로 나오도록 하려면....

text(셀값,"0000") 로 지정하면 된다.

 

다중조건으로 값이 일치하는 것을 찾고자 할 경우에는 해결이 쉽지 않으면 두개의 셀을 하나의 셀처럼 인식시켜 비교하는 것도 방법이다.

C2&D2

 

길이가 일정하지 않는 것은 오름차순 정렬을 하면 제대로 정렬이 안된다.

A1

A10

A100

뭐 이런식으로 정렬이 되니 불편하다.

이건 텍스트 분리하기를 해서 하나로 다시 합치는 방법을 사용해보자

RIGHT(B2,LEN(B2)-1)  : 왼쪽 1개를 제외하고 나머지를 모두 기입하라

이것을 가지고 TEXT 셀값 맞추기를 하면 된다. TEXT(RIGHT(B2,LEN(B2)-1,"00000")

 

특정 셀이 숫자인지, 문자인지 여부 판별하는 식으로 자막 공백제거

=IF(ISNUMBER(I15)=TRUE,"A",IF(ISTEXT(I15)=TRUE,"B","C"))

 

색기준 정렬

 정렬필터에서 색기준으로 정렬하면 된다.

 특정단어가 들어간 필드만 색깔을 넣고자 할 경우에는 조건부서식을 활용하면 된다.

 

공백 없애기

  홍길동, 홍 길동 등으로 입력값이 서로 다를 때 비교하기가 모호해질 때 공백을 없애는 함수는

  =substitute(a1, " ", "") 이렇게 쓰면 문자열내의 모든 공백이 제거됨

  문자열 앞뒤 공백 제거는 =trim(a1) 함수를 사용

 셀에 보면 숫자인데 텍스트로 되어 있어서 실제로는 숫자로 인식 안되는 경우가 있다.

이럴 경우 숫자로 인식시키는 방법은

=VALUE(C1) 처럼 텍스트를 숫자로 인식시키는 함수를 사용한 다음에 다른 셀에 값만 붙여넣기를 하면 된다.