내가 자주 쓰는 엑셀 단축키와 함수 2탄

요번 포스팅에서는 함수 부분이 많이 나오는데요.

개인적으로 함수나 매크로를 잘 쓴다 해서 분석을 잘하는 건 아니라 생각합니다. 정확히 말하면 엑셀 기술이 좋은 것이지, 분석을 잘하는 건 아니라는 의미인데요. 엑셀은 다양한 데이터를 단시간내에 효율적으로 핸들링해주는 것이지, 그렇게 정리된 데이터를 해석하고, 인사이트를 뽑아내 주진 않기 때문입니다. 

사실 저도 , 저번 포스팅과 이번 포스팅에 작성할 내용이외엔... 모르는 함수와 단축키가 더 많습니다. 특히, 매크로나 VBA는 까막눈이나 마찬가지랍니다 =_=;; 그냥 기본적인 원리(?)를 알고 있는 정도고... 실제 업무에서 거의 활용하진 않습니다. (물론, 이 부분은 회사나/업무에 따라 많이 달라져서... 암튼 제 업무에선 그렇네요....)

또 사설이 길어질 거 같으니... 각설하고.....

(지극히 개인적 기준의) 자주쓰는 엑셀 단축키와 함수 2탄입니다~~~~~

혹시 다른 내용이 궁금하신분은 아래를 클릭하시면 됩니다.

2015/02/26 - 내가 자주 쓰는 엑셀 단축키와 함수 1탄 (엑셀뉴비에서 강의를 하기까지...)

2015/03/05 - 내가 자주 쓰는 엑셀 단축키와 함수 2탄

2015/03/13 - 내가 자주 쓰는 엑셀 단축키와 함수 3탄

---

(아래 내용은 엑셀 2010버젼을 기준으로, 개인적으로 자주 쓰는 것들만 작성하였습니다. 업무에 따라 자주 쓰는 기능이 다를 수 있습니다~~)

11. IF함수와 응용 (if/countif/sumif/averageif/countifs/sumifs/averageifs)

if함수를 아무리 공부해도 어려운 사람은... 조금 더 수작업이 들어가긴 하지만 필터기능 (Alt+D+F+F)을 활용해도 된다. 엑셀 교과서대로 하는 거보다, 자기가 쉽게 할 수 있는 방법이 제일 좋은 방법이다.

각설하고 -_-;; 예를 들어, 아래와 같은 고객별 방문일수 데이터가 있다고 치자. 

[그림6]

각 함수별 예제에 맞춘 수식은 아래와 같다.

ⓐ =if(만약~~라면,~~하겠다,아님~~하고) : 

C2셀에다가 =if(B2>0,"O","X") 라고 써보자. 이는, 만약 고객 '가'의 11월 방문일수가 0보다 크면 즉, 11월에 방문을 했으면 O로 표기하고 아님 X로 표기하겠다는 뜻이다. 수식을 완성했으면 C13셀까지 마찬가지로 적용해준다.

ⓑ =countif(~~이(가),~~인것을 세겠다) : 

E2셀에다가 =countif(C2:C13,"O") 라고 써보자. 이는, C2셀에서 C13셀의 값이 O인 것을 세겠다는 뜻이다. 9가 나오면 제대로 된 것!!

ⓒ =sumif(~~이(가),~~인,**를 합하겠다) : 

B15셀에다가 =sumif(C2:C13,"O",B2:B13) 라고 써보자. 이는, C2셀에서 C13셀의 값이 O인 놈의 B컬럼 방문일수를 더하겠다는 뜻이다. 180이 나오면 제대로 된 것!!

ⓓ =averageif(~~이(가),~~인,**를 평균내겠다) : 

B16셀에다가 =averageif(C2:C13,"O",B2:B13) 라고 써보자. 이는, C2셀에서 C13셀의 값이 O인 놈의 B컬럼 방문일수를 평균 내겠다는 뜻이다. 20이 나오면 제대로 된 것!!

ⓔ =countifs(~~이(가),~~이면서,**이(가)**인것을 세겠다) : 

아무 곳에다가 =countifs(C2:C13,"O",B2:B13,29)라고 써보자. 이는, ⓑ에서 적은 조건을 충족하면서, B2셀에서 B13셀에 있는 29인 셀이 몇 개인지 세겠다는 뜻이다. 2가 나오면 제대로 된 것!!

ⓕ =sumifs(@@를 합하겠다,~~이(가),~~이면서,**는(은),**인) : 

아무 곳에다가 =sumifs(C2:C13,"O",B2:B13,29)라고 써보자. 이는 ⓒ에서 적은 조건을 충족하면서, B2셀에서 B13셀에 있는 29인 셀을 모두 합하겠다는 뜻이다. 58이 나오면 제대로 된 것!!

ⓖ =averageifs(@@를 평균내겠다,~~이(가),~~이면서,**는(은)**인) : 

아무 곳에다가 =averageifs(C2:C13,"O",B2:B13,29)라고 써보자. 이는 ⓓ에서 적은 조건을 충족하면서, B2셀에서 B13셀에 있는 29인 셀을 모두 평균내겠다는 뜻이다. 29가 나오면 제대로 된 것!!

sumifs와 averageifs의 경우, countifs와 달리 수식요소의 순서가 다소 달라졌음에 주목하자. '~~를 합하겠다/평균내겠다' 부분이 제일 앞으로 나온 것이 가장 큰 특징!!

12. 특정셀 불러오기 (vlookup/index/match) 

예를 들어, 아래와 같은 데이터가 있다고 치자. 

[그림7]

각 함수별 예제에 맞춘 수식은 아래와 같다.

ⓐ =vlookup(찾을 놈,어디서 찾을래,어디서 中 몇 번째 열에 있는 놈을 불러올래,false) : 여기서, 찾는값과 불러올 값이 다름을 이해하고... C2셀에다가 =vlookup(A2,F2:H9,2,false)라고 써보자.

절대참조까지 걸어서 =vlookup($A2,$F$2:$H$9,2,false)라고 하면 금상첨화~!! 찾을 범위를 머릿말까지 포함된 F1:H9로 지정해도 상관없다.

아무튼... 이는 "'가'를 우측표에서 찾고, 우측표 內 2번째 컬럼(몸무게)중, 학생 '가'의 값을 불러와라"는 뜻이 된다. 

마지막으로 쓴 false는 왠만한 경우 항상 false라고 쓴다 보면 되는데, 뜻은... 학생 '가'라는 값과 "정확히 일치할때"만 값을 불러와라.. 정도로 보면 될 듯.

ⓑ =index(어디서 찾을래,그놈이 어디서 中 몇번째 행에 있는지,그놈이 어디서 中 몇번째 열에 있는지) : D3셀에다가 =index($F$1:$H$9,2,3)이라고 써보자. 이는 우측표의 2행*3열 값인...학생'나'의 먹은 사과개수인 38을 불러오겠단 뜻이다. 즉, index함수는 좌표를 찍어 그 값을 불러와주는 함수라 할 수 있다.

그런데, 찾는 값의 행과 열위치를 매번 직접 타이핑할 수는 없는 노릇!! =_=;; 이럴 때 아래 match함수를 활용해주면 된다. 

ⓒ =match(찾는 놈,어디서 찾을래,0) : D3셀에다가 적어두었던, =index($F$1:$H$9,2,3)의 행위치와 열위치를 match함수로 정의해보자. =index($F$1:$H$9,match($A2,$F$1:$F$9,0),match($D$1,$f$1:$H$1,0))가 될 것이다. 

단순히 2라고 썼던 부분을, '우측표 F열에서 학생 '가'와 정확히 일치하는 셀이 몇 번째 줄에 있는지'라고 정의를 한 것이고...

단순히 3이라고 썼던 부분을, '우측표 1행에서 '먹은사과개수'와 정확히 일치하는 셀이 몇 번째 열에 있는지'라고 정의를 한 것이다.

마지막으로 쓴 0은, 위 ⓐvlookup에서 본 false와 동일 의미라고 보면된다.

13. 드랍박스 (데이터유효성 검사)

예를 들어, 아래와 같은 데이터가 있다 치자. 

고객 별로 선택한 햄버거 메뉴를 작성해야 하는데, 정해진 메뉴 안에서만 작성가능토록 제한을 두고 싶을 때 (=유효한 데이터만 선택하도록)... 바로 데이터 유효성 검사를 실시하면 된다.

[그림8]

우선, C3셀에서 C14셀까지 선택 → 최상단 탭 중 [데이터] 클릭 → [데이터 도구] 섹션 중 [데이터 유효성 검사] 클릭 → 제한대상(A)을 [목록]으로 선택

ⓐ 이미 존재하는 목록 선택하기 : 하단 [원본(S)] 우측의 셀지정 아이콘을 클릭하여, D2부터 D13을 선택해준다.

ⓑ 직접 목록 작성하기 : 하단 [원본(S)]칸에다가, 블로그 태그 쓰듯이... 콤마(,)로 구분하면서 에그 맥머핀, 소시지 맥머핀, .... 하고 쭉 써준다.

14. 스크롤바 넣은 동적 현황표 만들기

매일 raw데이터만 채워넣으면 해당일 현황이 자동으로 나오고... 스크롤바를 이용해 어제 것도 한 번에 조회하고 싶을 땐?! 매크로를 사용할 수도 있지만... 위에서 언급된 수식과 스크롤바 하나만 있어도 동적 보고서를 만들 수 있다.

아래와 같은 동적 현황표를 만드는 것이 목표.

[그림9]

차근차근 해보자.

먼저, 아래 [그림10]의 F6부터 G17과 같은 raw데이터가 있을 것이고... 스크롤바를 제외한 부분을 똑같이 따라 만들어보자. 참고로, 빨간 테두리 안에 있는 부분이 동적으로 바뀌게 된다. 

[그림10]

① 빠른실행도구 모음에 컨트롤삽입 메뉴 추가하기 : 최상단 [파일]탭 클릭 → [옵션]클릭 → 좌측에서 [빠른실행 도구 모음] 클릭 → [다음에서 명령 선택(C)] 중에서, [개발 도구 탭] 선택 → [컨트롤 삽입]을 선택하고 중앙 [추가(A)] 클릭 → 하단 [확인]클릭 → 좌측 최상단에, 저장하기/새문서 등과 더불어 [컨트롤 삽입] 아이콘이 생김을 확인할 수 있다.

② 스크롤바 삽입하기 : ①에서 추가해둔 [컨트롤 삽입] 아이콘 클릭 → [양식 컨트롤] 섹션에서, 2번째 줄 3번째에 있는 [스크롤 막대(양식 컨트롤)] 클릭 → 위 [그림10]과 같이 적당한 위치에 드래그하여 스크롤바를 그려준다.

③ 스크롤바와 셀 연결하기 : ②에서 추가한 스크롤바를 우클릭 → [컨트롤 서식] 클릭 → [컨트롤]탭의 [셀연결(L)] 우측에 있는 셀지정 아이콘 클릭 → G3셀을 클릭하고 Enter → [확인]클릭

스크롤바의 화살표를 누를 때마다 G3의 숫자가 변하는 것을 보자. 최초의 링크가 탄생한 것이다!! 앞으로의 작업도 링크를 걸고 또 거는 것이라 보면 된다.

④ 기준점과 대상일자 설정하기 : 

거의 모든 경우, 기준점은 본인이 가지고 있는 raw데이터의 시작일자로 지정해주면 된다. 즉, G4셀에다가 2012-02-01이라고 써주자. 

→ C7셀에 G4+G3라고 쓴다.(행/열 절대참조까지 다 걸어주면 금상첨화!!) 뜻은 기준일로부터 스크롤바로 움직인 수만큼 더한 날짜 값이라는 의미이다.

→ C8셀에는 C7+1이라고 쓴다. 뜻은 C7셀 다음날이란 의미이다. 마찬가지로 C9셀에는 C8+1이라고 쓴다.

→ 스크롤바를 다시 움직여, G3, C7~9셀의 값이 변하는 것을 확인한다. 즉, 스크롤바와의 링크가 더 생성됬다!!

⑤ 대상일자에 해당하는 데이터 불러오기 : 위 12번에서 배운, 특정셀 불러오기를 활용해보자!! F6부터 G17셀에 적힌 raw데이터에서 대상일자와 일치하는 날짜를 찾아 해당일 이용자수를 불러오면 된다.

→ vlookup 활용 : D7셀에다가 =vlookup($C7,$F$6:$G$17,2,false)라고 쓰고 D8, D9까지 일괄적용해준다. 

→ index와 match함수 활용 : D7셀에다가 =index($F$6:$G$17,match($C7,$F$6:$F$17,0),2) 혹은, =index($F$6:$G$17,match($C7,$F$6:$F$17,0),match(D$6,$F$6:$G$6,0))으로 써준다. 

→ 이제 스크롤바를 다시 움직여, G3, C7~C9뿐만 아니라, D7~D9셀의 값도 제대로 변하는 걸 확인한다!!! 스크롤바와의 링크가 또 생겼다!!!

예제의 경우, raw데이터와 보고서 부분(빨간 박스 부분)이 날짜와 이용자수 밖에 없는 단촐한(ㅎㅎ) 형태이기 때문에 함수정의가 짧은 vlookup이 낫다. 그러나, raw데이터의 컬럼이 많고... 보고용 현황표에서 그 많은 지표를 모두 넣어야 할 때는... 지표 별로 컬럼 번호를 모두 외우고 있을 수 없기 때문에 index/match함수를 활용하는 것이 낫다.

⑥ 그래프 그리기 : C6셀부터 D9셀까지 선택 → 최상단 [삽입]탭에서 [차트]섹션의 [새로 막대형]을 클릭 → 그래프 內 세로축 숫자를 선택하고 우클릭하여 [축 서식(F)] 클릭 → [축 옵션]메뉴에서 최대값을 자동이 아닌 [고정(I)]으로 선택하고 1500정도로 입력 → [닫기]클릭

무조건 최소/최대값 등을 고정해야 하는 것은 아니다. 예제의 경우 일자가 지남에 따라 값이 증가함에도... 세로축의 최대값도 같이 증가해서 증가하는 것이 보이지 않기 때문에 조정한 것이다.

---

금번 포스팅도 길어져;;;; 아래 기타 부분은 다음 포스팅으로 ㅠㅠ!!!!

15. 그 밖에... (표/이름관리자/매크로)

16. 자주까진 아니지만 알아두면 쏠쏠한 함수

>> 제 블로그의 내용이 도움 되셨다면, 아래 하트 버튼(공감UP) 한번씩 눌러주세요 ^ㅁ^ <<