COUNTIF·SUMIF·VLOOKUP과 피벗테이블로 업무 보고서를 만드는 방법

TL;DR

원본 데이터에 VLOOKUP으로 금액과 본부 정보를 보완한 뒤, COUNTIF로 메뉴별 주문 수량을 세고 SUMIF로 본부별 금액을 합산하면 된다. 조회 범위는 F4로 절대참조하고 VLOOKUP은 정확히 일치하는 값을 찾도록 FALSE를 사용한다. 완성된 원본 데이터로 피벗테이블을 만들면 같은 결과를 더 간편하게 보고서 형태로 정리할 수 있다.

Transcript

공대 나온 여자 사람들 공여사들의 김과장입니다. 반갑습니다. 저는 회사 생활한지 8년차 되는 공여사인데요. 학과생 200명 중에 여학우가 10명 정도 되는 전자과를 졸업했고요. 지금은 10명 중에 1명이 여자인 남초회사를 다니고 있어요 제가 회사에서 담당하는 일은 주로 .. 회식장소 찾기 팀 워크샵 기획하기 특기는 아저씨들이랑 농담 따먹기 .. 소개가 길었네요.. 제 채널은 공대 나온 여자사람들 공여사들이 회사 다니는 이야기하는 채널이고요. 오늘은 옆자리 부장님 같은 엑셀 무식자들 도 이거 한 편이면 못 만들 게 없는 엑셀 핵심 기본기를 가져와봤습니다. 사실.. 저도 단축키 날아다니면서 쓰는 사람은 아니거든요 8년을 ... Read More

Key Insights

• COUNTIF는 지정한 범위에서 조건과 일치하는 항목의 개수를 세므로 메뉴별 주문 수량을 계산하는 데 사용된다.
• 수식을 아래로 복사할 때 검색 범위를 유지하려면 F4를 눌러 셀 주소에 달러 표시가 붙는 절대참조를 설정해야 한다.
• VLOOKUP은 메뉴나 팀을 기준표에서 찾아 단가나 본부처럼 대응하는 정보를 원본 데이터에 추가할 때 활용된다.
• VLOOKUP의 마지막 인수에 FALSE를 지정하면 유사한 값이 아니라 조회값과 정확히 일치하는 항목을 찾는다.
• VLOOKUP은 조회 열과 반환 열의 순서가 맞아야 하며, 예제에서는 기준 열을 복사해 표의 순서를 조정했다.
• SUMIF는 본부 열을 조건 범위로, 특정 본부를 조건으로, 금액 열을 합계 범위로 지정해 본부별 비용을 계산한다.
• 피벗테이블에서 숫자 필드는 값 영역에 넣으면 합계로, 한글 텍스트 필드는 개수로 자동 집계될 수 있다.
• 원본 데이터에 필요한 필드를 먼저 보완한 뒤 피벗테이블을 만들면 동일한 자료를 여러 보고서 형태로 재구성할 수 있다.
• 🎬 이 채널의 다른 영상:
  • 직원이 스스로 움직이게 하는 업무 요청 시스템은 어떻게 설계할까?
  • 소기업은 AI 도입 전에 단일 진실 공급원을 어떻게 구축해야 하나?
  • 작은 회사는 어떻게 AI가 활용할 업무 맥락을 체계적으로 쌓는가?
• 📺 이 채널: 공여사들

Questions & Answers

Q: COUNTIF로 메뉴별 주문 수량을 어떻게 계산하나요?

메뉴가 입력된 전체 범위를 첫 번째 인수로 지정하고, 집계할 메뉴가 적힌 셀을 조건으로 선택하면 된다. 첫 수식을 작성한 뒤 아래로 복사하면 조건 셀은 치킨마요, 참치마요, 치킨제육도시락처럼 행에 따라 바뀌며 각 메뉴의 주문 횟수가 계산된다.

Q: 수식을 복사할 때 COUNTIF 범위가 움직이지 않게 하려면 어떻게 하나요?

검색 범위의 시작 셀과 끝 셀을 절대참조로 고정하면 된다. 수식에서 해당 셀 주소에 커서를 놓고 F4를 누르면 달러 표시가 추가되며, 이후 수식을 아래로 붙여 넣어도 메뉴 데이터의 검색 범위는 그대로 유지된다.

Q: VLOOKUP으로 메뉴별 단가를 원본 데이터에 어떻게 추가하나요?

현재 행의 메뉴를 조회값으로 선택하고, 메뉴명과 단가가 들어 있는 기준표를 조회 범위로 지정한 뒤 반환 열 번호로 2를 입력한다. 조회 범위는 F4로 고정하고 마지막 인수에는 FALSE를 넣어 메뉴명이 정확히 일치하는 단가를 불러온다.

Q: VLOOKUP에서 FALSE를 사용하는 이유는 무엇인가요?

FALSE는 조회값과 정확히 같은 항목을 찾도록 지정하기 위해 사용한다. 예제에서는 주문 데이터의 메뉴명이나 팀명을 기준표에 있는 동일한 이름과 대응시켜야 하므로, 유사한 값이 아니라 완전히 일치하는 결과를 가져오도록 FALSE를 입력한다.

Q: VLOOKUP으로 왼쪽 방향의 정보를 가져오기 어려울 때 어떻게 처리하나요?

예제에서는 INDEX와 MATCH를 사용하는 대신 기준 열을 한 번 더 복사해 VLOOKUP이 조회할 수 있는 순서로 표를 바꾼다. 팀 열이 먼저 오고 본부 열이 그다음에 오도록 구성한 뒤, 팀명을 조회값으로 삼아 두 번째 열의 본부 정보를 불러온다.

Q: SUMIF로 본부별 주문 금액을 어떻게 합산하나요?

본부가 입력된 열을 조건 범위로 지정하고 집계하려는 본부명을 조건으로 선택한 다음, 금액 열을 합계 범위로 지정하면 된다. 조건 범위와 합계 범위는 F4로 고정해 수식을 복사해도 변하지 않게 하고, 본부명 조건만 행별로 바뀌게 한다.

Q: 피벗테이블로 본부별 금액 합계를 어떻게 만드나요?

완성된 원본 데이터 범위를 선택해 피벗테이블을 삽입한 뒤, 본부 필드를 행 영역에 놓고 금액 필드를 값 영역에 배치하면 된다. 금액은 숫자이므로 예제에서는 자동으로 합계가 적용되며, SUMIF로 계산한 본부별 결과와 같은 형태의 표가 생성된다.

Q: 피벗테이블로 메뉴별 주문 개수를 어떻게 구하나요?

메뉴 필드를 행 영역에 놓고 한글 텍스트로 된 필드를 값 영역에 배치하면 된다. 텍스트 필드는 예제에서 자동으로 개수 집계가 적용되므로, 돈까스도련님이나 치킨마요 같은 각 메뉴가 주문 데이터에 몇 번 등장했는지 표로 확인할 수 있다.

Q: 업무용 원본 데이터는 어떤 순서로 준비하는 것이 효율적인가요?

먼저 기존 주문 자료에 금액과 본부처럼 분석에 필요한 필드를 추가하고, VLOOKUP으로 기준정보의 값을 각 행에 채우는 방식이 제시된다. 모든 정보가 포함된 원본 데이터를 만든 다음 전체 범위로 피벗테이블을 생성하면 원하는 집계표를 여러 형태로 추출할 수 있다.

Q: 숫자를 세 자리마다 구분해 표시하려면 어떻게 하나요?

Ctrl+1을 눌러 셀 서식으로 이동한 뒤 숫자가 세 자리마다 구분되어 보이도록 서식을 설정하면 된다. 설명에서는 화면에서 바로 서식을 지정하는 방식보다 셀 서식을 이용하면 파워포인트 등에 붙여 넣을 때 뒤쪽에 빈칸이 생기는 문제를 피할 수 있다고 제시한다.

Summary & Key Takeaways

• 먼저 COUNTIF로 주문 메뉴 범위에서 각 메뉴가 등장한 횟수를 센다. 수식을 아래로 복사할 때 검색 범위까지 함께 이동하지 않도록 F4를 눌러 달러 표시가 포함된 절대참조로 고정하고, 조건 셀만 상대참조로 남겨 메뉴별 수량을 한 번에 계산한다.

• 다음으로 VLOOKUP을 사용해 기준표에서 메뉴별 단가와 팀별 본부를 원본 데이터에 추가한다. 조회 범위를 절대참조로 고정하고 반환할 열 번호와 FALSE를 지정한다. 조회 방향이 맞지 않으면 기준표의 열을 복사해 순서를 바꾸는 실용적인 방법도 제시된다.

• 모든 정보를 포함한 원본 데이터가 완성되면 SUMIF로 본부별 금액을 합산하거나 피벗테이블로 결과를 만든다. 피벗테이블의 행 영역에 본부나 메뉴를 놓고 값 영역에 금액 또는 텍스트 필드를 배치하면 합계와 개수가 자동 집계된다.