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

강의소개

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

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

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

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

업데이트 노트

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

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

영상강의

예제파일 다운로드

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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