2. 데이터 분석을 위한 기초 수학/통계(이동훈 강사님)_06

▶ MATCH 함수

하나의 열이나 행을 잡아야 함

=MATCH("찾고싶은 문자" , 하나의 열 또는 행 범위 드래그 , 0)

=MATCH("F" , D1:D3 , 0)

=MATCH(C11, D1:D3 , 0)

 

▶ VLOOKUP 함수

=VLOOKUP( 중복된 값이 없는 컬럼에 입력된 셀(기준셀) , 기준셀부터 범위 선택 , 찾고싶은 컬럼의 순서 , 0)

=VLOOKUP(A4 , LIST!D:J , 3 , 0)

=VLOOKUP(A4 , LIST!D:J , MATCH(C11, D1:D3,0) , 0)

 

▶ 셀 & 셀

고유값이 없을 경우(=중복값이 있는경우) 행을 합쳐서 고유값을 만든다

=VLOOKUP( B5&C5 , LIST!D:J, 3 , 0) 

 

▶ INDEX 함수

=INDEX(범위 ,  범위의 첫 시작으로부터 몇번째 행 , [범위의 첫 시작으로부터 몇번째 열] )

=INDEX(A1:C3 , 2 , 3)

→ 행, 열의 숫자를 MATCH 함수로 대치 해주기!

 

▶ COUNT 함수

특정 범위에서 "숫자 데이터" 가 들어가 있는 셀의 개수 파악

=COUNT( 숫자 데이터의 개수를 파악할 셀 범위)

 

▶ COUNTA 함수

특정 범위에서 "모든 데이터" 가 들어가 있는 셀의 개수 파악

=COUNT( 값이 입력된 데이터의 개수를 파악할 셀 범위)

 

▶ COUNTA 함수

특정 범위에서 비어있는 셀의 개수 파악

=COUNT( 빈 셀의 개수를 파악할 셀 범위)

 

▶ COUNTIF 함수

특정 범위에서 하나의 조건을 만족하는 셀의 개수 파악

=COUNTIF( 데이터의 개수를 파악할 셀 범위 , "개수를 셀 데이터의 조건" )

=COUNTIF( C5:C12 , 1 )

=COUNTIF( C5:C12 , " >=5" )   # COUNTIF의 조건에는 ""가 붙는다!

 

▶ COUNTIFS 함수    #조건이 1개여도 사용가능!

특정 범위에서 2개 이상의 조건을 만족하는 셀의 개수 파악 

=COUNTIFS( 범위1 , "조건1" , 범위2 , "조건2" ) 

★ 나이의 조건이 바뀌는 경우가 생기기 때문에 마지막 조건을 부등호만 " " 안에 넣어주고

& 연산자와 해당 조건셀을 틀릭해서 값 넣어주기!

-> ">="$O24

 

▶SUMIF 함수

특정 조건을 만족하는 데이터의 합계 계산

 

▶ SUMIFS 함수

2개 이상의 조건을 만족하는 데이터의 합계 계산   #범위의 높이가 같아야 함!

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

=SUMIFS( 금액 , 연도 , "2023" , 구분 , "매출액")

★ SUMIFS 를 생활화 하자!

 

★ 피벗테이블은 로우테이블이 바뀌면 자동으로 갱신되지 않음, 우클릭해서 새로고침 해야 갱신됨

피벗테이블은 최종 결과물이 아니라 숫자를 참고하여 다른 자료를 만들때 사용하는 것이 편함

★ 함수로 만들면 로우테이블이 바뀌면 자동으로 갱신됨

 

▶FIND 함수

긴텍스트에서 특정 단어나 문장이 시작하는 위치를 숫자로 출력

띄어쓰기 포함하여 문자를 세며 대소문자를 구분함

(* 대소문자를 구분하지 않을 때는 SEARCH 함수 사용)

=FIND( "찾을 텍스트" , 긴 텍스트 셀 , [문자열을 찾기 시작할 위치] )   # 위치 생략시 1부터 시작

=FIND( " 아빠 " , A1 , 101 )   # 단어가 몇번째 있는지 알려줌

 

▶ IFERROR 함

=IFERROR( FIND( " 아빠 " , A1 , 101 )  , 0)   #찾는 단어가 없다면 0을 출력

=COUNTIFS( FIND함수 출력 셀 , " >0 " )   # 0값이 아닌 셀이 몇갠지 셀 수 있다

 

▶ LEFT, RIGHT 함수

=LEFT(데이터가 입력된 셀, 불러올 문자열 수)   # 왼쪽에서부터 N개 가져오기

=RIGHT(데이터가 입력된 셀, 불러올 문자열 수)  # 오른쪽에서부터 N개 가져오기

 

▶ MID 함수

=MID( 데이터가 입력된 셀 , 불러올 문자열의 시작 위치 , 불러올 문자열 수 )

=MID( B5 , 5 ,3 )   # B5에서 5번째부터 3글자 불러오기

 

▶텍스트 나누기

* - 으로 나누어져 있는 경우

=FIND( "-" , B5 , 1)   # -의 위치를 찾기

 

▶ 응용

AAAA-BBBB-CCCC-DDDD

 

첫번째 - 위치 : =FIND("-",$B5,1)

두번째 - 위치 : =FIND("-",$B5,$C5+1)

세번째 - 위치 : =FIND("-",$B5,D5+1)

 

AAAA찾기: =LEFT($B5,C5-1)

BBBB찾기: =MID($B5,$C5+1,$D5-$C5-1)

CCCC찾기: =MID($B5,$D5+1,$E5-$D5-1)   

#데이터가 입력된 셀 , 2번째 하이픈 하나다음부터 ,  3번째하이픈-2번째하이픈-1개 값만큼 불러오기

DDDD찾기: =RIGHT($B6,LEN($B6)-$E6)   /  =MID($B5,$E5+1,1000)

 

 

---

정확한 수치를 구하는 것이 아니라 의사결정의 방향을 구하려고 하는 것이기 때문에

큰 흐름을 보기위해 데이터 분석을 하자!