'업무 능력 향상/엑셀함수배우기'에 해당되는 글 24건

회사업무를 하다보면 시스템에서 가져온 값이 A셀처럼 되어 있는데 보고자료 형태는 위 표와 같이 만들어야 하는 경우가 있다.

이럴 경우 텍스트 나누기 함수를 이용하여 할 수도 있는데

그것도 좀 귀찮은 경우가 있다.

더군다나 나누기만 할게 아니라 다른 값이 들어가야 하는 경우가 생긴다면???

그럴 경우 FIND 함수를 활용하면 편하다. 

FIND 함수를 이용하여 ">" 부분을 기준으로 앞의 값과 뒤의 값을 찾아서 LEFT 함수, MID함수를 이용해서 구분1 셀과 구분2 셀에 넣어주면 된다.

먼저 FIND 함수를 살펴보자.

FIND(">",A3) 와 같이 함수를 써서 값을 확인해보니 6이라는 위치값을 알려준다.

눈으로 직접 카운트를 해보면 농수산물 글자가 4자, 공백이 1, >가 1 이렇게 해보니 왼쪽부터 6번째 위치라는게 눈으로 확인된다.

이제 LEFT 함수를 사용해서 

추출갯수를 직접 지정하는 대신에 FIND함수를 넣어서 이곳에 넣어준다.  

공백 1을 빼준다. 하지만 정확하게 하려면 여기서 2를 빼주면 된다.

이제 구분2에 값을 구하기 위해서는 

MID함수를 써서 시작위치는 FIND함수를 이용하여 시작위치를 구한다.

여기서는 FIND함수로 찾는 값인 ">"로부터 + 2 부터 시작된다는 걸 눈(육안)으로 확인할 수 있다.

추출갯수는 충분한 값인 30으로 그냥 정했다. 

이렇게 함으로써 LEFT함수, MID함수, FIND함수를 이용하여 원하는 결과를 얻는 과정을 살펴봤습니다.

알아두면 매우 유용하게 활용할 수 있답니다. 저도 필요하여 배워서 잘 써먹고 있네요. 

함수 활용을 하는 걸 직접 참조할 수 있도록 첨부파일로 올려둡니다.

FIND_LEFT_MID_sample.xlsx

엑셀 기본적인 함수 COUNT 함수에 대해서 알아보겠습니다.

COUNT : 정해진 범위의 숫자가 몇개인지 카운트하라

COUNTA : 정해진 범위의 숫자와 텍스트가 몇개인지 카운트하라

COUNTBLANK : 정해진 범위의 공백이 몇개인지 카운트하라

COUNTIF(범위,조건) : 범위에서 조건에 맞는게 몇개인지 카운트하라

아래 그림에서 정한 범위는 C2열에서 C16열까지이다.  

카운트할게 달랑 하나이면 굳이 지금처럼 절대값 사용하지 않아도 된다.

모양은 숫자처럼 보이는데 실제로는 텍스트로 된 것이 있다.

이건 COUNT 함수를 사용하면 숫자만 카운트 하라는 것이니 텍스트는 제외시킨다.

숫자와 문자 모두를 카운트하니 총 15개를 카운트했다.

셀중에 공백이 들어가 있을 수 있다.  

이럴 경우 공백을 찾을 때 유용하게 활용되는 함수이다.

COUNTIF(범위, 조건)이 가장 많이 활용되는 함수이다.

  - 조건 : ">4000" 이라는 조건이면 값이 4,000 보다 큰 것이 조건이 된다.

             ">= 4000" 은 4,000을 포함하여 큰 것을 찾으라는 것이다. 

카운트 하는 값은 직접 응용해보면 된다.

여러개의 조건을 모두 만족하는 개수를 구하고자 할 때는 COUNTIFS(범위1,조건1,범위2,조건2,범위3,조건3)

함수를 사용하면 된다. 

엑셀 FIND 함수를 이용한 방법을 알아보겠습니다.

FIND 함수

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

  - FIND 함수는 대문자, 소문자를 구분하여 값을 찾는다. 한글, 영문 구분없이 문자 하나를 1로 인식한다. 

  - FINDB 함수는 대문자, 소문자를 구분하여 값을 찾는데, 영문 문자는 1, 한글 문자는 2로 인식한다.

  - 대문자, 소문자 구분없이 찾고자 한다면 SEARCH 함수를 이용하면 된다. 

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

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

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

FIND 함수는 찾는 값이 들어 있으면 찾는 값의 위치를 반환한다.

만약 찾는 값이 없으면 아래 그림처럼 에러를 표시한다. 

 여기에 COUNT 함수를 더 적용하면

'마포로' 라는 텍스트가 여러번 들어가면 1이상의 숫자로 표기됨

즉, 마포로 마포로 라는 텍스트가 포함되어 있다면 결과는 2를 표기

마포로 라는 텍스트가 없다면 0 을 표기 

셀에 찾는 값이 들어 있으면 "있음", 없으면 "없음"으로 표기하고 싶다면

IF(COUNT(FIND("마포로",E2))>0,1,0)

으로 적어준다.

끝까지 다 적용했으면  

수식 --> 값으로 변경해주는 작업을 해준다.  (http://jsk005.blog.me/150158476649 게시물 참조)

이제 FIND 필드기준으로 내림차순 정렬을 하면  

원하는 값을 찾아서 작업한 결과가 나온다.

엑셀을 다루면 가장 먼저 접하는 함수가 IF 함수입니다.

조건식이 단일 식으로 된 건 쉽게 이해를 할 것입니다.

하지만 IF(AND(A3>3,B3>=2),"OK","틀림") 이라는 함수를 접하면 어떤 뜻인지를 바로 알 수 있어야 합니다.

AND 함수는 모든 조건이 다 참일 경우에만 참 값을 반환한다는 점을 알면 됩니다.

그럼 예제 그림을 통해서 간략하게 의미를 살펴보겠습니다.

혹시라도 위에 예제로 나온 이름은 임의로 적은 것이니 동일 이름을 가지신 분은 오해하지 마세욤

예제에는 함수식을 몇개를 써서 값을 준 것을 예제로 들어놓았으니 이해하는데 참고하세요.