[리모트] 엑셀 하나로 충분한 데이터 분석 기본

엑셀의 기본 원리

수식의 결과값이 텍스트(계산X)면 왼쪽 정렬 / 숫자(계산O)면 오른쪽 정렬

 

상대 참조: 함수를 복사/붙여넣기 할 때 참조된 셀이 함께 이동하는 참조

* 수식 작성 시 입력 셀을 활용할 때 수식이 셀을 참조한다고 표현

절대참조: 함수를 복사/붙여넣기 해도 참조된 셀이 이동하지 않는 참조 (F4키로 설정 가능)

* 대문자 앞 달러 표시는 열 이동 불가, 숫자 앞 달러 표시는 행 이동 불가 의미

엑셀 단축키

윈도우랑 맥은 많이 달라서 단축키는 거의 사용하지 않을듯 싶다.. ^.^

IF 함수와 중첩으로 데이터 분류하기

(1) IF(조건, True일 때 값, False일 때 값)

: IF함수 단독으로 사용할 때

(2) IF(조건1, 조건1 True일 때 값, IF(조건2, 조건2 True일 때 값, 조건2 False일 때 값))

: IF함수 중첩으로 사용할 때

COUNT 함수 계열로 데이터 개수 세기

(1) COUNT(시작:끝)

: C열($C:$C)에서 숫자데이터 들어 있는 셀의 개수 파악

* 특정 범위(시작:끝)

(2) COUNTA(시작:끝)

: 특정 범위에서 데이터가 들어가 있는 셀(비어있지 않은 셀)의 개수 파악

(3) COUNTBLANK(시작:끝)

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

(4) COUNTIF(시작:끝, 조건)

• COUNTIF(C5:C12, "A") : "A"를 만족하는 범위 내의 셀의 개수
• COUNTIF(C5:C12, ">=5") : 범위 내에서 >=5 를 만족하는 셀의 개수 (꼭 따옴표 사용해야함)

 (5) COUNTIFS(범위1, 조건1, 범위2, 조건2)

: 특정 범위에서 2개 이상의 조건을 동시에 만족하는 셀의 개수 파악(조건이 1개일 때도 사용 가능, 2개 이상일 때도 사용 가능)

VLOOKUP

: 공통 기준 열을 기준으로 n번째 있는 데이터를 불러오는 함수

=VLOOKUP(찾을 데이터, 시트!시작범위:끝범위, 열(시작범위열이 1일때를 기준으로 숫자), 0)

이름	사번	팀	직급
유재석	215427	=VLOOKUP($C5,LIST!$D:$J,3,0)	=VLOOKUP($C5,LIST!$D:$J,4,0)
이수근	210217	=VLOOKUP($C6,LIST!$D:$J,3,0)	=VLOOKUP($C6,LIST!$D:$J,4,0)
송지효	213902	=VLOOKUP($C7,LIST!$D:$J,3,0)	=VLOOKUP($C7,LIST!$D:$J,4,0)

 

근데, 이렇게 열 번호를 입력하면 일일이 바꿔줘야 하는 문제가 있다.

=VLOOKUP(찾을 데이터, 시트!시작범위:끝범위, 열번호 적혀있는 컬럼 사용, 0)

	1	3
이름	사번	팀
유재석	215427	=VLOOKUP($C5,LIST!$D:$J,$D$3,0)
이수근	210217	=VLOOKUP($C6,LIST!$D:$J,$D$3,0)
송지효	213902	=VLOOKUP($C7,LIST!$D:$J,$D$3,0)

 

이렇게 찾아야하는 열을 숫자로 저장해 열번호가 적혀있는 컬럼을 사용하는 VLOOKUP을 사용해도 되지만,

열번호를 참조하더라도 가장 처음에 불러올 데이터가 몇 번째 열인지를 직접세어야 한다.

→ 열이 많은경우 시간이 오래 걸릴 수 있음

VLOOKUP + MATCH함수

MATCH함수는 내가 찾고 싶은 값이 한 행/열에서 몇 번째에 위치하는지 숫자로 알려준다.(결과는 항상 숫자)

=MATCH(찾을 값, 시트!시작:끝, 0/1)

즉, LOOKUP에 MATCH함수를 대입해 찾으면 된다.

이름	사번	팀
유재석	215427	=VLOOKUP($C12,LIST!$D:$J,MATCH($D$11,LIST!$D$4:$J$4,0),0)
이수근	210217	=VLOOKUP($C13,LIST!$D:$J,MATCH($D$11,LIST!$D$4:$J$4,0),0)
송지효	213902	=VLOOKUP($C14,LIST!$D:$J,MATCH($D$11,LIST!$D$4:$J$4,0),0)

=VLOOKUP(찾을 값, 찾을시트!시작행:끝행, MATCH(찾을컬럼명, 찾을시트!시작행열:끝행열, 0),0)

만약, 사번이 주어지지 않았을 때엔 동명이인이 발생하게 된다. 이 경우엔 어떻게 값을 찾아야할까?

그러면 새로운 열에 '순번+이름' 값을 넣고, 해당 값을 찾아주면 된다.

=순번&이름 으로 만들 수 있다!

INDEX & MATCH 함수 활용

(1) INDEX 함수

: 특정 범위에서 행 번호와 열 번호로 원하는 데이터를 불러온다.

(2) INDEX + MATCH 함수

아래와 같은 데이터에서 1월의 데이터만 뽑고 싶을 때, 

=INDEX(데이터전체범위(시트!시작:끝), MATCH(찾을 행 값, 시트!시작:끝, 0), MATCH(찾을 열 값, 시트!시작:끝, 0))

구분	합계	1월	2월	3월	4월	5월
매출액	3,058,655,831	251,298,858	231,317,770	288,177,984	254,541,012	253,526,140
매출원가	1,001,883,926	80,899,586	88,333,825	80,618,143	86,962,896	81,645,057
제조인건비	332,213,399	24,455,001	32,381,791	24,987,099	28,242,923	27,094,154
재료비	331,141,247	26,597,254	31,515,126	27,159,650	30,838,642	25,938,292
기타원가	338,529,280	29,847,331	24,436,908	28,471,394	27,881,331	28,612,611
매출이익	2,056,771,905	170,399,272	142,983,945	207,559,841	167,578,116	171,881,083
(매출이익률)	67%	68%	62%	72%	66%	68%
판매관리비	982,963,565	83,971,479	80,545,915	79,127,192	82,928,155	78,332,425
인건비	201,155,145	15,386,460	16,115,673	17,524,558	19,524,959	17,908,950
판촉비	202,503,318	17,149,273	16,659,603	15,156,378	17,542,356	16,927,746
감가상각비	191,641,416	17,257,229	16,561,299	16,489,528	15,324,675	15,413,635
물류비	192,823,130	17,717,021	15,633,629	12,537,311	14,129,411	13,727,453
기타판관비	194,840,556	16,461,496	15,575,711	17,419,417	16,406,754	14,354,641
영업이익	1,073,808,340	86,427,793	62,438,030	128,432,649	84,649,961	93,548,658
(영업이익률)	35%	34%	27%	45%	33%	37%
영외수지	-2,247,570	-1,280,508	-612,456	-2,016,826	485,132	1,874,121
영업외수익	96,404,856	7,956,153	8,109,880	7,553,457	7,985,945	9,354,389
영업외손실	98,652,426	9,236,661	8,722,336	9,570,283	7,500,813	7,480,268
세전이익	1,071,560,770	85,147,285	61,825,574	126,415,823	85,135,093	95,422,779
(세전이익률)	35%	34%	27%	44%	33%	38%

=INDEX(RAW!$B$4:$O$24,MATCH($B7,RAW!$B$4:$B$24,0),MATCH($C$4,RAW!$B$4:$O$4,0)) 수식을 사용한다.

	1월
구분	금액
매출액	251,298,858
매출원가	80,899,586
매출이익	170,399,272
(매출이익률)	68%
판매관리비	83,971,479
영업이익	86,427,793
(영업이익률)	34%
세전이익	85,147,285
(세전이익률)	34%

SUMIF(S) 함수로 데이터 합계 구하기

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

IFERROR

IFERROR(수식입력, "오류가 발생했을 시 표시할 값")

=IFERROR(VLOOKUP(찾을 값, 찾을 범위, 열, 0), "오류가 발생했을 시 표시할 값")

이 경우 오류가 났을 때는 표시할 값이 표시되지만, 오류가 나지 않았을 때엔 정상적인 값이 출력된다.

FIND

원하는 텍스트의 위치를 알고 싶을 때 사용

=FIND(찾을 텍스트, 긴 텍스트, 문자열 찾기 시작할 위치(보통 1))

• 긴 텍스트에서 특정 단어나 문장이 시작하는 위치를 숫자로 출력
• 띄어쓰기까지 포함하여 문자를 세며 대소문자를 구분 → 대소문자를 구분하지 않아야 할 때는 SEARCH 함수 사용

만약, 특정 단어가 포함되어있는 행의 개수를 확인하고 싶다면, FIND로 단어의 위치를 찾은 뒤에

COUNTIF(FIND열 전체, ">0") 해주면 된다.

LEFT/RIGHT/MID 함수로 데이터 추출하기

• =LEFT(전체 텍스트, 불러올 문자 수)
• =RIGHT(전체 텍스트, 불러올 문자 수)
• =MID(전체 텍스트, 불러올 문자열의 시작 위치, 불러올 문자열 수)

날짜데이터

날짜데이터의 계산 : 종료일 - 시작일 = 기간

만약, 종료일을 기간에 포함하고 싶으면 종료일 - 시작일 + 1

하이픈(-)을 넣으면 자동으로 날짜로 인식한다. 

• 기간: =DAYS(종료일, 시작일), 배송일의 경우 =DAYS(종료일, 시작일)+1
• 기간: =DATEDIF(시작일, 종료일, "D"), 배송일의 경우 =DATEDIF(시작일, 종료일, "D")+1
  • "Y", "M", "D", "YM", "MD"가 있다.

피벗테이블

: 커다란 표를 요약하는 요약 통계표