Part 1-1.ch 02. 반드시 알아야 할 엑셀 함수

● 함수= 함수명(인수1, 인수2,[인수3]...)

 

● [] 가 있는 인수는 없어도 함수 실행가능

● 함수에 데이터를 입력하면 결과값을 출력

 

● sum(c5:c12) :범위 합

● sum(c5,c12) :각  합

● =average(): 평균

 

● =average + tap + 드래그 +tap

 

● 붙여넣기시 원하는 마지막칸에 1써놓으면 엑셀 끝까지 가지 않음!

 

● COUNTA(C5:C12): 특정범위에서 데이터가 들어가 있는데의 개수 파악 (빈셀은 세지않음)

● COUNTBLANK (C5:C12) : 특정범위에서 비어있는셀 개수세기

● COUNTIF(C5:C12."A"):특정범위에서 하나의 조건을 만족하는 셀의 개수파악

COUNTIF(C5:C12, ">=5")

● COUNTIFS(C5:C12,">5",C5:C12,"<8"): 특정범위에서 여러조건을 만족하는 셀의 개수파악

-> COUNTIFS(개수를파악할 데이터가 있는범위 1, "조건 1", 개수를파악할 데이터가 있는범위 2, "조건 2")

 

● 국어점수가 90점 이상이면 "합격", 그렇지 않으면 "불합격"으로 분류

 

● =IF(조건, 조건이 맞을 때 결과값,그렇지 않을때 결과값

● =IF(조건, 조건이 맞을 때 결과값, 조건, 조건이 맞을때 결과값, 그렇지 않을때 결과값)

● =IF(H5>=$N$11,$M$11,IF(H5>=$N$12,$M$12,$M$13))

 

● VLOOLUP: 공통 기준 열을 기준으로 n번째 있는 데이터를 찾아 오는 함수

V=Vertical: 수직의='열'을 중심으로

LOOK UP: -을 찾다

● 인간이 데이터를 "열별로"정리한느 것이 편하기 때문에 HLOOKUP을 잘 사용안함

● 공통기준열을 1번으로 하고 다음 열 부터 2,3,...이렇게 세는데

공통기준열 옆에 있는 데이터는 셀 수 없음

(그러나 인덱스 매치 함수를 이용하면 공통 기준열 왼쪽에 있는 데이터도 읽을 수 있다.

● 공통 기준 열에 중복된 데이터가 없어야 함

만약 기준열에 중복값이 있다면 처음값을 불러온다

->중복값이 없는 새로운 기준 열을 생성해 VLOOKUP 하기! -> 셀&셀

 

●  6을 MATCH 함수로 찾기

=VLOOKUP(C19&B19,'직원정보LIST(실습)'!$A:$J,6,0)

=VLOOKUP($C20&$B20,'직원정보LIST(완성)'!$A:$J,MATCH($D$18,'직원정보LIST(완성)'!$A$4:$J$4,0),0)

 

● VLOOKUP(찾을기준데이터셀,원래 데이터의 범위(공통준열부터 선택해야함),불러올 데이터의 열번호(공통기준열을 1열로 했을때, 0(0:정확히 일치할때만 불러옴, 1:근사치를 불러옴(기본값))

=VLOOKUP($C5,직원정보LIST!$D:$J,3,0)

=CLOOKUP(사번값,사번열부터 전체 데이터 선택, 전체데이터에서 불러올 데이터의 열번호 6, 0)

 

● MATCH: 내가 찾고 싶은 값이 한 행,열에서 몇번째에 있는지를 숫자로 알려줌

=MATCH( 내가 찾고 싶은값, 내가 찾고 싶은 값이 포함된 단일 열 또는 행 범위, [전확히 일치 or 근사치 넣는 곳인데 0으로 하기]

 

●  3을 MATCH 함수로 찾기

=VLOOKUP($G12,'직원정보LIST(실습)'!$D:$J,3,TRUE)

=VLOOKUP($G12,'직원정보LIST(실습)'!$D:$J,MATCH($H$11,'직원정보LIST(실습)'!$D$4:$J$4,0),TRUE)

 

● INDEX(범위, 내가 찾고 싶은 데이터의 범위 내 행번호, [내가 찾고 싶은 데이터의 범위 내 열번호 ])

=INDEX(전사손익계산서!$B$4:$O$24,MATCH($B7,전사손익계산서!$B$4:$B$24,0),MATCH($C$4,전사손익계산서!$B$4:$O$4,0))

 

●SUMIF: 특정 조건에 맞는 데이터들의 합계 계산

=SUMIF(더할 조건들의 범위, "조건",더할 값들의 범위)

●SUMIFS: 2개 이상의 조건을 동시에 만족하는 데이터들의 합계 계산

=SUMIFS(더할 값들의 범위, 더할 조건 범위1, "조건1",더할 조건 범위2,"조건2")

SUMIF 와 SUMIFS 의 차이점: SUMIFS는 더할 값들의 범위를 가장 먼저 지정해야함

=SUMIFS(RAW_SUPER_STORE!$Q:$Q , RAW_SUPER_STORE!$D:$D , 'SUMIF(S)함수로 조건에 맞는 합계 구하기(실습)'!J$5 , RAW_SUPER_STORE!$N:$N , 'SUMIF(S)함수로 조건에 맞는 합계 구하기(실습)'!$I23)

=SUMIFS( SALES, 연도, 2021, 지역, EAST)

 

● SUMPRODUCT: 인수(배열)끼리 곱하여 곱들의 합계를 계산

SUMIFS함수와 동일한 기능이다.

● 논리곱

T(1)*T(1)=T(1)

T(1)*F(0)=F(0)

F(1)*T(0)=F(0)

F(1)*F(0)=F(0)

 

● =SUMPRODUCT

=SUMPRODUCT ( (조건 1의 범위 = 조건값2의 셀) * (조건 2의 범위 = 조건값2의 셀) , 곱할 값범위)

=SUMPRODUCT ( ( 연도               = 2021             ) * (      지역              =    EAST     ) , SALES       )

 

● =IFERROR(수식이 입력된 셀,"오류가 발생했을때 표시할 값")

=IFERROR( 함수, "") -> 오류가 난 은 빈칸으로 두겠다.

 

● =FIND: 대소문자 구분함

=FIND("찾고싶은 텍스트",긴텍스트가 입력된 셀, [문자열을 찾기 시작할 위치, 안적으면 처음문자부터 즉 1이 기본값]

=FIND("-",B5)

->응용: 특정키워드를 언급한 사람의 수를 셀때 

일단 FIND를 이용해서 몇번째 문자열에서 해당 티워드가 나왔는지를 찾으면 무조건 1이상의 숫자가 뜸.

이때 COUNTIFS를 사용해서 1이상의 숫자를 카운트 하면

●=FIND("-",B5) <-값을 C5에 입력함

=FIND("-",B5,C5+1) <-값을 D5에 입력함

=FIND("-",B5,D5+1)

 

● =SEARCH: 대소문자 구분하지 않음

=SEARCH

 

●  LEFT / RIGHT : 텍스트의 가장 왼쪽/ 오른쪽부터 원하는 문자열까지 추

=LEFT(전체텍스트, 불러올 문자열 수)

=RIGHT(전체텍스트, 불러올 문자열 수)

=RIGHT(B5,3)

 

● MID : 텍스트의 중간 시작 위치부터 원하는 문자열까지 추출

=MID(B5,5,3) ->찾고싶은 데이터가 있는 열,시작위치,몇개가져올지

 

● =LEN(B5) B5셀의 텍스트 문자열의 총 길이

전체 문자열에서 마지막 몇개의텍스트만 불러오고 싶을때

=RIGHT($B5,LEN($B5)-E5)

 

● 날짜데이터

- 2020-02-02 형식

- 날짜 형식을 일반으로 바꾸면 1900년 1월1일을 기준으로 며칠째되는 날인지 5자리 숫자로 표시가 됨

이럴땐 서식을 날짜로 변경해주기

 

● 날짜데이터 계산

종료일 -  시작일 =기간: 종료일은 기간에서 제외됨

종료일을 기간에 포함해야된다면 수식에 +1하기

 

=DAYS(종료일셀,시작일셀)

=DAYS(H3,D3)+1

=DATEDIF(시작일셀, 종료일셀,"D")

=DATEDIF(D3,H3,"D")+1

 

"D" : 일 수

"M" : 월 수

"Y" : 연 수

"YM" : 올 해의 월 수

"MD" : 이번 달의 일 수

 

● D3: 2033-11-08

=YEAR(D3) ->2033

=MONTH(D3) -> 11

=DAY(D3) -> 8

 

=YEAR(D3)&"년" 하면 2033년 으로 표시됨

 

● NETWORKDAYS.INTL : 시작일과 종료일 사이의 기간중 내가 지정한 공휴일과 다른 휴일을 제외한 근무일 수 계산

NETWORKDAYS.INT(시작일, 종료일,[주말],[휴무일])