엑셀 잘하는 방법은?! 함수도 VBA도 아닌 바로 이것!

함수, VBA가 아닌, 엑셀을 정말로 잘하기 위해서 반드시 알아야 할 '데이터 관리 방법'의 중요성과 간단한 피벗테이블/슬라이서 예제를 살펴봅니다.

홈페이지 » 엑셀 잘하는 방법은?! 함수도 VBA도 아닌 바로 이것!

강의소개

이번 강의에서는 엑셀을 엑셀답게 잘 사용하기 위해서 반드시 알아야 할 팁을 소게합니다. 많은 분들이 엑셀 함수나 VBA를 잘 사용하는 것을 엑셀 잘하는 방법이라고 이야기합니다. 하지만 엑셀은 '데이터베이스'로 부터 태어난 '스프레드 시트' 프로그램 입니다.

따라서 엑셀을 잘 사용하려면 함수나 VBA를 잘 다루는 것보다, '데이터를 체계적으로 구축'하는 방법을 이해하는 것이 훨씬 중요한데요. 엑셀은 계속 진화해왔고, 지금 이시간에도 신규기능이 꾸준히 추가되고 있습니다.

따라서 복잡한 배열함수나 기능을 사용하지 않아도, 데이터만 체계적으로 잘 구축한다면 클릭 몇번만으로 훌륭한 실시간 데이터 분석 툴을 만들 수 있습니다.

이번 강의에서는 VLOOKUP 함수와 피벗테이블만 사용하여 데이터를 분석하는 방법을 알아봅니다. 그리고, 데이터를 구조화해서 사용해야 하는 이유를 하나씩 짚어봅니다.

업데이트 노트
주기적으로 갱신되는 제품단가를 같이 관리하고 싶어요

주기적 또는 비주기적으로 업데이트 되는 제품가격을 엑셀로 관리해야 할 경우, 함수를 어떻게 작성해야 할까요? 아래 매일 입력되는 단가를 관리하는 방법에 대한 강의를 추가 업데이트 해드렸습니다.

영상강의
큰 화면으로 보기

예제파일 다운로드

일부 예제파일은 오빠두엑셀 홈페이지 회원에게만 제공됩니다.

  • [기초레벨업] 어디서도 알려주지 않는 엑셀 잘하는 방법
    예제파일
  • [기초레벨업] 어디서도 알려주지 않는 엑셀 잘하는 방법
    완성파일
  • [기초레벨업] 어디서도 알려주지 않는 엑셀 잘하는 방법
    보충파일

1. 엑셀을 잘 하는것 = 함수를 잘 쓰는 것?

엑셀은 진화하고 있습니다. 곧 있으면 오피스 365 사용자에게 공식적으로 엑셀 2019 업데이트가 이루어질 예정입니다. 엑셀은 정말 다재다능한 프로그램인데요. 엑셀에서 크게 사용되는 구성은 아래 3가지로 나눌 수 있습니다.

 1. 함수  SUM 함수, VLOOKUP 함수, IF 함수, 등등..
 2. 기능  피벗테이블, 필터, 차트/그래프, 등등..
 3. 고급기능  파워피벗, 파워쿼리, VBA, 등등..

예전 엑셀에서는 아래의 방식으로 자료를 처리해야 했습니다.

엑셀 잘하는 방법 1
일방통행식 데이터 편집
:: 한번 작업된 데이터의 재편집이 어려움 :: 낮은 호환성 ::

하지만 엑셀이 진화하면서, 이제는 함수나 기능의 중요성보다 입력되는 ‘원본 데이터’의 중요성이 더 강조되고 있습니다.

즉, 예전에는 사용자가 엑셀의 함수나 기능을 공부하고 사용해서 원하는 데이터를 출력해야 했지만, 앞으로의 엑셀에서는 잘 구조화된 데이터를 작성하고 입력할 수 있다면, 간단한 함수와 몇번의 마우스 클릭으로 원하는 데이터를 작성하고 출력할 수 있게 되었습니다.

엑셀 잘하는 방법 총정리 데이터베이스 엑셀
구조화된 데이터를 사용하면 유연성 및 호환성을 높일 수 있습니다.

2. 데이터 구조화하기 - 데이터 정규화

데이터 정규화에 대한 내용은 이전 강의에서 자세히 다뤄드렸습니다. 아래 링크를 참고해주세요.

3. 구조화되지 않은 데이터 사용시 문제점

아래 파일은 실제로 많이 사용되는 월별 매출관리 대장 양식입니다. 매월 판매기록을 각각의 시트로 나누어 관리하고, ‘합계’ 시트에는 해당 년도의 모든 월별 판매기록을 모아서 출력합니다.

일반적인 매출관리 장부 양식
일반적으로 사용되는 월매출관리 대장양식

이와 같이 월별로 시트를 나누어 관리할 경우, 아래와 같은 문제점이 발생합니다.

  1. 월별 매출의 세부내용 확인 및 편집의 어려움
  2. 새로운 항목 (메뉴 또는 거래처) 추가 시, 여러 개의 시트에 적용하는 번거로움.
  3. 데이터 구조 변경 시, 연결된 수식이 깨질 수 있는 위험.
  4. 월별 자료를 하나의 자료로 취합하기가 어려움.
  5. 그 외 여러가지 문제점..

각 문제점은 영상강의에서 자세히 설명드렸습니다.

4. 엑셀에서 구조화 된 데이터를 사용시 좋은점

이러한 문제점을 해결하기 위해, 아래와 같이 데이터를 구조화하여 시트를 구성하면 좀 더 간결하면서도 효율적인 매출분석 툴을 작성할 수 있습니다.

본 강의에서 다루는 파일은 VBA 매크로 없이 온전히 엑셀의 VLOOKUP 함수와 피벗테이블 기능만 사용한 것으로, 여기에 몇 가지 간단한 VBA 코드만 더해주면 더욱 완벽한 매출관리 툴을 작성할 수 있게 됩니다.

데이터 구조화 된 월 매출관리 대장 양식
데이터를 구조화하여 작성한 월 매출관리 대장

데이터를 구조화하면 아래처럼 4개의 시트로 간결하게 구성할 수 있습니다.

  • 보고서 : [구매자료] 시트에서 월별/구매처별로 자료를 취합하여 피벗테이블로 출력
  • 구매자료 : [구매처자료]와 [제품자료]에서 정보를 받아와 각 구매처별로 구매한 제품의 개수를 입력하여 나열합니다.
  • 구매처자료 : 오류방지 및 체계적인 관리를 위해 구매처정보를 별도로 관리합니다. (선택사항)
  • 제품자료 : 오류방지 및 체계적인 관리를 위해 제품정보를 별도로 관리합니다. (선택사항)

영상에서 보여드렸듯이, 엑셀에서 데이터를 구조화하여 입력하고 관리할 경우 이전에 있던 여러가지 문제점을 말끔히 해결 할 수 있습니다. 여러 복잡한 함수나 기능을 이용한 것이 아닌, 엑셀의 기본함수와 기본기능만 적용해도 누구나 쉽게 효율적으로 자료를 관리할 수 있게됩니다.

5. 피벗테이블 & 피벗차트, 그리고 슬라이서

구조화된 데이터를 바탕으로 피벗테이블을 작성하였으면 이제는 피벗테이블의 슬라이서를 200% 활용할 수 있습니다. 이번 강의에서는 슬라이서 두개만 추가해서 아주 간단하면서도 강력한 매출분석 툴을 만들어 보겠습니다.

엑셀 슬라이서 추가
구조화된 데이터를 바탕으로 슬라이서를 효율적으로 사용합니다.

만들어진 피벗테이블을 기반으로 위와 같이 슬라이서를 추가해주면, 원하는 구매처별로 선택한 메뉴의 월별 판매량을 하나의 시트안에서 실시간으로 확인할 수 있습니다.

슬라이서를 응용하여 다양한 원페이지 대시보드를 만드는 방법도 준비해드렸으니 자세한 내용은 관련링크를 참고하세요.

5 27 투표
게시글평점
guest
40 댓글
Inline Feedbacks
모든 댓글 보기
정건영
정건영
2020년 1월 10일 6:53 오후
게시글평점 :
     

좋은 강의 감사합니다

서녘별
서녘별
2020년 2월 12일 8:39 오후
게시글평점 :
     

강의잘 들었습니다. 문의 사항이 있습니다. 1월부터 7월까지 단가가 일정하다가 8월부터 단가 상승으로 인하여 단가가 변동이 된다면 이는 데이타 정형화는 어떻게 해야 할까요?

난향
난향
2020년 4월 9일 3:22 오후
답글 남기기  서녘별

저도 궁금했던 자료였는데....감사합니다.

김진철
김진철
2020년 2월 15일 9:35 오전
게시글평점 :
     

당신은 천재임 너무 유용한정보 감사합니다

sukim03
sukim03
2020년 3월 4일 11:59 오후
게시글평점 :
     

예제파일 다운로드 열기가 안되는데 왜그런건지ㅜ

박장수
박장수
2020년 3월 17일 1:58 오후
게시글평점 :
     

궁금한게 있어서 질문드립니다. 예제파일로 실습을 하려는데 예제파일엔 구매자료, 구매처자료, 제품자료 시트가 없고, 합계,1,2,... 시트가 있어서 완성파일로 질문드려요. 완성파일의 구매자료 시트의 표 자료를 피벗테이블로 만들면, 영상과 달리, '보고서에 추가할 필드'에 분기,… 더보기 »

FafaLei
FafaLei
2020년 3월 18일 1:24 오후
게시글평점 :
     

완전 위로가 되고 공감되는 강의 입니다. 가장 중요한것은 데이터를 분석하는 능력과 그 데이터를 잘 정렬하는것이 가장 중요하다는 것이 맞는 것 같습니다. 이것을 통해서 분석이 되고 그것을 바탕으로 효율을 더 끌어… 더보기 »

엑엑셀셀
엑엑셀셀
2020년 3월 22일 12:56 오전
게시글평점 :
     

잘 배워 갑니다

형들아빠
형들아빠
2020년 3월 22일 10:34 오전
게시글평점 :
     

원본 데이터가 잘못되어있으면 이후 작업이 너무 어려워요

visitor
visitor
2020년 3월 25일 7:10 오후
게시글평점 :
     

감사합니다.

엑린이
엑린이
2020년 4월 19일 6:57 오후
게시글평점 :
     

감사합니다

정지원
정지원
2020년 4월 26일 10:22 오후
게시글평점 :
     

예제가 대체 어디있을까요....ㅜㅜ
상단 예제파일열어보니 완성된거 밖에 없는데....

피피포포
피피포포
2020년 6월 4일 10:06 오후
게시글평점 :
     

강의 잘들었습니다. 요즘 엑셀 공부가 넘나 재미가 있습니다. 유튜브로 보다 홈피까지 왔습니다

에이사 버터필드
에이사 버터필드
2020년 7월 11일 1:37 오후
게시글평점 :
     

오늘도 잘 들었어요

서로해
서로해
2020년 8월 20일 5:24 오후
게시글평점 :
     

함수는 정말 어려워요~

마크툽
마크툽
2020년 9월 11일 8:05 오후
게시글평점 :
     

감사합니다. 영상과 같이 구매자료를 추가해서 피벗테이블을 업데이트(Alt+F5) 하면 새로추가한 년도자료가 영상과 같이 추가 되는 것이 아니고
자료가 요약되는거 같이 변경이 됩니다.
알려주셨으면 합니다.

Decker
Decker
2020년 11월 21일 7:32 오후
게시글평점 :
     

항상 도움이 됩니다.

봄봄
봄봄
2020년 12월 6일 5:19 오전
게시글평점 :
     

핵심있는 강의내용 감사드립니다.

엘지에스엘리베이터
엘지에스엘리베이터
2020년 12월 21일 11:28 오후
게시글평점 :
     

조흔내용 정말감사합니다

엑셀린이
엑셀린이
2021년 1월 7일 9:25 오후
게시글평점 :
     

좋은 강의 감사합니다. 완성파일을 받아 확인보니, 구매자료 시트에 구매월행에 연도+날짜만 있습니다. 표시형식만 바꾼 것으로 이해 했습니다. 보고서 시트에 있는 피벗테이블 필드 목록을 보면 '월' 이라는 항목이 있던데, 이것으로 날짜가 영어로… 더보기 »

이밤에
이밤에
2021년 1월 12일 1:54 오후
게시글평점 :
     

강의가 너무 재미있어요..
감사드려요.

uRme
uRme
2021년 1월 12일 3:51 오후
게시글평점 :
     

이런 교육이 체계적으로 이루어졌으면 좋겠습니다. 업무용으로 날라오는 엑셀 데이터 구축된거보면 진짜 개판이에요... ㅠㅠ 데이터만 제대로 되어있어도 얼마나 편한데...

dir
dir
2021년 1월 17일 1:44 오후
게시글평점 :
     

강의 보면서 하면 알겠는데 이내용을 필요한곳에 적용하려고 하니 어디서부터 해야 할지 막막하네요 ㅎㅎ

싼타부
싼타부
2021년 1월 22일 3:05 오후
게시글평점 :
     

참 대단하시네요,,, 엄청 도움이 되네요

김동현
김동현
2021년 2월 8일 10:17 오전
게시글평점 :
     

안녕하세요 거래처 별로 아이템의 단가가 다를 경우는 어떻게 해야 할까요?ㅠㅠ

왕고참
왕고참
2021년 2월 11일 10:33 오전
게시글평점 :
     

완전 원하던 강의였습니다.. 감사합니다.

KHChoi
KHChoi
2021년 2월 11일 6:28 오후
게시글평점 :
     

감사합니다.

loobyloo
loobyloo
2021년 2월 27일 11:48 오후
게시글평점 :
     

데이타 관리 피벗테이블 업무활용도에 굿이네요 좋은 강의 감사합니다.

JINTIE
JINTIE
2021년 3월 16일 2:28 오전
게시글평점 :
     

강의 매일 잘 보고 있습니다. 계속 힘내서 많은 도움 주시기 바래요. 오빠두와 열심히 배우는 JINTIE.

운비
운비
2021년 4월 11일 9:53 오후
게시글평점 :
     

따라해보니 정말쉽게 설명이 되더군요 감사합니다.

이휘소
이휘소
2021년 9월 1일 7:31 오전
게시글평점 :
     

감사합니다

akz
akz
2021년 10월 16일 8:37 오후
게시글평점 :
     

안녕하세요? 강의 잘 보고 있습니다.:) 제가 아직 초보라 따라하면서 제가 모르는 것이 있어서요;;완성본을 보면 피벗테이블에서 ① 구매월이 아닌 월이 행에 들어가있고 ② 슬라이서 버튼도 기존 슬라이서와 다르게 좀 더 깔끔한… 더보기 »

40
0
여러분의 생각을 댓글로 남겨주세요.x