제가 4년 전 찍은 엑셀 영상이 얼마 전 500만 뷰를 돌파했는데요

이 영상이 제 채널의 첫 번째 영상이다 보니까

설명이 아쉬운 부분들이 좀 있었는데요

특히 이번에는 이 영상을 새로 찍은 버전을 보고 싶다는 분이 계셔서

4년이나 더 늙어버린 김 과장 버전으로

500만 뷰 영상을 리메이크 해보도록 하겠습니다.

4년 전이나 지금이나 제가 드리는 말씀은 똑같죠.

직장인 엑셀에서 우리가 꼭 알아야 할

수식 3개 + 기능 1가지: COUNTIF, SUMIF, VLOOKUP 그리고 피벗 테이블입니다.

이 4가지를 가지고 직장인 엑셀에서 우리가 항시

겪게 되는 이 문제들을 직접 풀어보도록 할게요.

문제를 같이 풀어보실 분들은 영상 아래 본문을 펼쳐서

엑셀 파일을 다운로드받아 주시고요. 문제가 나와 있습니다.

회사에서 점심시간에 사장님과 도시락 미팅을 한다며

도시락 메뉴 주문을 받고 있어요. 그런데 우리가 궁금한 건 이런 거죠.

메뉴별로 몇 개씩 주문할지, 본부별로 얼마의 예산을 배부할지입니다.

표를 보면 각 소속팀의 직원들이 메뉴를 하나씩 고른 모양이에요.

그런데 제가 취합은 받았지만 메뉴별로 몇 개를 주문할지,

본부별로 얼마씩 예산을 배부할지는

한눈에 들어오지 않습니다. 그래서 위에 있는 문제부터 풀어볼게요.

치킨마요가 몇 개인지, 참치마요가 몇 개인지 세면 되는데,

우리가 이 함수를 모르면 위에부터 하나, 둘, 셋, 넷, 다섯, 여섯, 일곱

이렇게 세야 할 거예요. 그런데 그러기에 우리 나이 먹어서

눈도 침침해 가지고 참치인지 치킨인지

잘 보이지도 않죠. 그래서 우리는 엑셀을 쓸 겁니다.

여기다 "=" 하고 COUNTIF: 범위에서 조건에 해당하는 값을 세어라.

COUNTIF, range가 옵니다. 어디서 셀 거냐 하는 범위를 정해주면 되는데,

이 범위를 드래그해서 선택해줍니다. 콤마 찍고,

그중에 누구를 셀 건데, 나는 치킨마요를 셀 거야 라고 해서

 "치킨마요"라고 적어주면 될 거예요.

괄호 닫고 엔터를 치면 7이라는 값이 나왔어요.

우리가 방금 센 7개와 동일한 값이죠.

그런데 여기서도 참치마요를 세 줘야 하는데,

마찬가지로 COUNTIF 함수를 사용하고, 범위를 선택하고, 콤마를 찍고,

참치마요를 입력하자니 벌써 두 번째밖에 안 됐는데,

귀찮은 겁니다. 그래서 다시 위로 돌아가서요,

F2 키를 누르고, 우리는 이 뒤쪽에 치킨마요를 직접 입력하지 않고,

지우고, 콤마 뒤로 치킨마요가 들어있는 이 셀을

직접 클릭해 줄게요. 그러면 괄호 닫고

엔터를 치면 똑같이 7이라는 값이 나왔죠.

마찬가지로 여기서도 참치마요 대신 지우고,

이 셀을 선택해 주고 엔터를 치면 되는데, 이것마저도 매번 귀찮겠죠.

그래서 엑셀이 필요한데요. 자, 첫 번째 셀의 수식을

잘 봐주세요. 여기서 F2 키를 누르면,

이 범위가 파란색으로 표시되어 있고,

두 번째 선택한 이 치킨마요가 빨간색으로 되어 있어요.

얘를 Ctrl+C로 복사해서 Shift+마지막 셀을 누르면

연달아서 선택을 해 주거든요. 여기다가 Ctrl+V로 붙여넣어 볼게요.

그랬더니 아까 전에는 분명히 치킨마요를 가리키고 있었는데

두 번째 셀에서는 참치마요를, 세 번째 셀에서는 치킨제육도시락을

가리키고 있어요. 우리가 이 셀의 수식을 한 칸 아래쪽에 붙여넣으면

얘가 참조하고 있던 셀도 한 칸 아래로

따라서 내려오는 건데요. 문제는 뭐냐면

여기까지 잘 따라서 내려온 건 좋았어요.

그런데 문제는 앞에 있는 범위까지 얘가 한 칸 아래로 수식이 붙여넣어질 때마다

범위도 한 칸씩 한 칸씩 아래로 따라서 내려왔어요.

그래서 다시 위로 올라가서 이 범위만큼은 고정을 해줬으면 좋겠는 거죠.

다행히 그런 기능이 엑셀에는 있는데요.

이렇게 얘네 둘을 드래그해서 선택하고

F4 키를 누르면 달러 표시($)가 F열 앞에, 6행 앞에, $F$6

F열 앞에 25행 앞에 $F$15가 걸리게 되는데

이렇게 달러 표시가 붙으면 그 셀의 수식을 다른 셀에 붙여넣더라도

이 범위가 항상 고정되어 있다는 뜻이에요.

절대 참조라고 부르는데요. 이 상태로 엔터를 치고

이 셀의 수식을 Ctrl+C로 복사해서 Shift+마지막 셀을 누르고

Ctrl+V로 붙여넣어 줄게요. 여기서 F2키를 눌렀더니

아까 전에 범위가 이 밑에까지 따라서 내려왔었는데

지금은 첫 번째 셀에서 선택한 범위와 동일하게

고정되어 있습니다. 이렇게 절대 참조와 COUNTIF를 활용해서

각 메뉴가 이 테이블에 몇 번 등장하는지를 세 줬는데요.

이번엔 두 번째 문제를 풀어볼게요.

본부별로 배분할 예산을 구해야 하는데요,

봤더니 이 테이블에서 지금 본부 값도 채워져 있지 않고

금액도 없어요. 그래서 얘는 우리가 직접 셀프로

값을 채워줘야 하는데요. 먼저 금액 값부터 채워 볼게요.

보면 치킨마요, 참치마요, 돈가스도련님

이런 걸 이 테이블에서 갖다가 두 번째 열에 오는 값을 불러줘야겠죠?

이럴 때 쓰는 게 바로 VLOOKUP 함수인데요. 만약에 이 VLOOKUP이라는 함수가 없었다면

치킨마요를 여기서 찾아서 얘를 복사해서 불러오고,

참치마요를 여기서 찾아서 얘를 복사해서 불러오고,

돈가스도련님도 찾아서 붙여넣기 해주고,

하다 보면 팀장님이 말하겠죠. "너 그렇게 해서 어느 세월에 다 할라 그러니,

똥멍청아!"라고 하겠죠. 그래서 여기서는

VLOOKUP으로 시작하는 함수가 하나밖에 안 남아서

탭(Tab)을 쳤을 때 자동완성이 되거든요.

이 상태에서 먼저 lookup_value가 옵니다. 누구를 찾을 건데,

나는 치킨마요를 찾을 거야. 콤마 찍고,

table_array가 옵니다. 치킨마요를 어디서 찾을 건데,

나는 이 범위에서 찾을 거야. 그런데 이 셀의 수식을 아래쪽에 붙여 넣더라도,

이 범위가 고정이 되도록 F4 키를 바로 눌러줄게요.

콤마 찍고 column index number가 옵니다. 몇 번째 열에 오는 값을 불러올 거냐

뭐냐면, VLOOKUP의 V는 버티컬이고요,

LOOKUP은 찾는다는 건데, 이 범위에서 내가 치킨마요를 찾는데,

수직으로 내려가면서 찾겠다는 거죠.

우리는 치킨마요를 찾을 건데, 이 범위의

첫 번째 열에서 위에서부터 수직으로 내려가면서

치킨마요를 찾는데, 찾다가 찾다가 치킨마요가 나오거든요.

그 값으로부터 오른쪽으로 몇 번째 열에 오는 값을 불러올 거냐

라는 걸 정해주는 함수예요. 그래서 이 column index number는

1열, 2열이니까 치킨마요로부터 오른쪽으로 2번째 열에 오는

조회수 500만 돌파! 회사에서 넘 이쁨받아 문제라는 "직장인 엑셀 엑기스" 영상을 다시 찍었다;;

공대 나온 여자 사람들 (※근데 1명임 주의.😜)

🟢 김과장
대기업 10년짬바. 자칭 일머리 천재
특) 엑셀도 쫌 함

🟡 오대리
N년차 직딩.
주식으로 날린 돈 N잡으로 메꾸는 중ㅠ

🟣 이대표
빽없는 1인기업가
한때 잘나가는 회사원이었으나 이젠 개털..
과연 몇 년이나 버틸까?🤔

📌LIVE방송 : 매주 수요일 밤 9시