목차로 이동 

엑셀 일반 사용자는 피벗테이블을 자주 사용하지 않겠지만 재무, 기획, 마케팅 부서에서는 자주 쓰는 기능입니다.

피벗테이블은 부서별 연봉 집계와 같이 어떤 항목을 기준으로 집계 분석이 필요할 경우 이용합니다.

SUM() 함수 쓰면 되지 않느냐? 생각하는 분도 있겠지만 피벗테이블의 자동 그룹핑, 자동 정렬, 자동 집계 기능을 보면 왜 써야 하는지 이해가 될 것입니다.

아래 표에서 부서별 성과급 집계를 구하시오

(풀이)

삽입 메뉴에서 피벗테이블 아이콘을 클릭 하세요

피벗테이블 영역을 지정 할 때는 항목명칭도 포함해서 지정 하세요

새 워크시트에 아래와 같은 피벗테이블 화면이 생겼습니다.

아무것도 없는 화면입니다.

우측에 보이는 피벗 테이블 필드에서 부서명을 클릭하면 아무것도 없었던 피벗테이블 워크시트에 부서명을 중복없이 정렬하여 표시 합니다.

우측 피벗테이블필드에서 추가로 이름을 클릭하면 부서별 직원 이름이 정렬되어 나옵니다.

우측 피벗테이블에서 추가로 성과급을 클릭하면  부서명별 직원  성과급이 나옵니다.

우측 피벗 테이블 필드에 체크했던 항목을 체크해제 하면 다시 아무것도 없는 공란의 피벗테이블 영역이 보입니다.

이번엔 우측에 있는 피벗테이블필드에서 항목 모두를 체크한 후 필터영역에 부서명을 끌어 놓아 넣었습니다.

부서명이 상단에 보이시죠?  (모두)라 기재되어 있습니다. 클릭해서 원하는 부서명을 지정하면 지정한 부서명의 성과급만 조회 가능합니다.

위의 피벗테이블 결과는 팀이 위에서 아래로 순차 나열 되어 나왔습니다.

만약 부서별로 가로로 나열해서 표시하고 싶은 경우 부서명을 드래그 하여 열란에 끌어 놓으면 아래와 같이 정리되어 표시 됩니다.

목차로 이동 

목차로 이동 

목록 중 값 선택하게 만들기

업무를 하다 보면 양식을 배포 후 취합하는 경우가 많습니다.

그런데 문제는 사용자 마다 임의의 값을 입력하는 경우 통계 작업이 매우 힘듭니다.

예를 들면 부서명 입력란에 디지털팀, 디지탈팀, 디지털 팀, Digital팀, digital team ..... 의미는 같으나 명칭은 서로 다르게 입력할 경우

집계가 안 됩니다.

따라서 제시한 목록 중 선택 하게 하기가 필요 합니다.

부서명에 목록을 만들어 선택해서 입력하게 하시오.

(풀이)

우선 부서명에 들어갈 항목을 만듭니다.

부서명 입력란을 클릭합니다.

그리고 데이터 메뉴의 유효성검사 아이콘을 클릭합니다.

그러면 데이터 유효성 창이 아래와 같이 뜹니다.

유효성 조건을 목록으로 지정 합니다.

원본 영역선택아이콘을 클릭합니다.

부서명의 목록에 나올 값을 범위 지정합니다.

확인 버튼을 클릭합니다.

부서명 옆에 리스트 열기 아이콘이 생깁니다.

리스트 열기 아이콘을 클릭하면 선택하여 입력할 수 있습니다.

목차로 이동 

목차로 이동 

영역 지정은 너무나 많이 사용 되기 때문에 누구나 한번 이상은 다 봤을겁니다.

하지만 의외로 쉬운 영역 지정 방법을 모르는 사용자가 많더군요.

행이나 열이 엑셀 한페이지를 넘지 않는 사용자는 무의미 하겠지만 대부분 행이나 열이 한페이지를 넘긴 자료를 다룰겁니다.

아래 Sheet에서 값이 들어 있는 A1 ~ F9까지 영역을 지정 해보세요

(풀이)

첫번째 셀을 클릭합니다.

Shift + Ctrl + >(우측방향키)  를 동시에 누릅니다.

아래와 같이 입력된 값이 있는 마지막 열까지 영역 지정 됩니다

Shift + Ctrl +  v (아래방향키) 를 동시에 누릅니다.

아래와 같이 입력된 값이 있는 마지막 행까지 영역 지정 됩니다. 

목차로 이동 

목차로 이동 

업무를 하다보면 중복행 제거가 필요한 경우가 제법 많이 생깁니다.

다음 예제에서 중복행을 제거 해보세요

(풀이)

A열을 가나다(오름차순) 순으로 정렬

첫번째 상품 옆 열에 1을 입력합니다.

두번째 행에 바로 위에 있는 행 상품이름과 현재 행 상품이름이 같으면 0, 틀리면 1이 나오도록 IF문을 넣습니다.

만든 수식을 전체행에 복사 합니다.

표시된 증복검사 값을 옆 열에 값 복사 합니다. (주의 : 수식 복사로 하지 말고 값 복사 하세요)

전체 데이터를 값복사 열 기준으로 오름차순 정렬 합니다.

값 복사 value가 0인 행이 중복된 행이므로 행삭제 해줍니다.  끝

목차로 이동 

목차로 이동 

Sheet1의 인사대장에 고과항목을 넣으려고 한다.

이미 Sheet2에 사번별 고과점수표는 작성했기에 불러오기만 하면 된다.

어떻게 불러오면 될까? 예에선 2건이라 눈으로 찾을 수도 있지만 몇천건 이라면 어떻게 해야 할까?

sheet1

sheet2

(풀이)

Vlookup 함수는 값을 찾아 올 떄 유용하다.

수식 : vlookup ( 찾고자 하는 값,  찾을 범위, 찾을 범위 중 몇번째 열 값을 돌려 줄까?, 비슷해도 돼?)

Sheet1과 Sheet2의 공통점은 사번으로 구별 되어 있다는 점이다.

sheet2에서 해당 사번에 대한 고과를 가져오도록 하자. 

매우 중요한 점은 찾고자 하는 값을 사번으로 정했으면 범위 지정시 사번이 가장 앞 열에 나오도록 범위를 지정해야 한다.

범위 지정시 행이나 열이 바뀔때 자동으로 범위가 변경 되는 것을 막으려면 절대 위치 지정하기 위해 $를 붙여 준다.

가지고 오고 싶은 값은 고과 이므로 범위 중에 두번째 열이 고과이므로 2를 기재한다.

사번과 정확히 일치해야 생각한다면 FALSE  비숫하게 맞아도 된다면 TRUE 인데 FALSE가 좋겠다.

이젠 완성된 수식을 밑으로 드래그 해서 복사하면 고과 점수를 검색해 온다. 

목차로 이동 

목차로 이동 

엑셀을 하다 보면 절대 위치 지정이 필요 할 경우가 매우 많다.

예를 보자.

수경그룹에선 추석 보너스를 기본급 100%를 주기로 했다.

사장 수박은 총 보너스 금액 중 사원별 가져가는 비율을 알려달라고 인사팀에 지시한다.

인사팀 직원은 아래와 같이 값을 구한다.

 = 보너스금액 / 보너스 총합계금액    여기에 백분율로 보여주기 위해 100을 곱해줬다.

그리고 다음 사원도 동일한 식을 적용하기 위해 셀 복사를 했다.

그러자 아래와 같이 값이 나왔다.    

음, 비중은 백분율 값을 구하려 한 것이기에 100 초과 값은 나오면 안 되는데 124라니...

셀의 수식을 봤더니 아래와 같다.

원하는 수식은 아래와 같은데 어떻게 셀복사 하면 좋을까?

즉 합계 D2셀은 셀복사 할때 절대 위치 지정을 통해 고정 됐으면 좋겠다.

절대 위치 지정하려면 행 또는 열 앞에 $ 표시를 넣어 주면 된다.

절대 위치를 지정하면 셀복사 해도 위치에 따라 셀이 자동 변경되지 않는다.

풀이하면 아래와 같다.  

$행열   : 행은 절대고정, 열은 변경

$행$열 : 행은 절대고정, 열도 절대고정 

행$열  : 행은 변경, 열은 절대고정

목차로 이동 

목차로 이동 

로또 예상 번호를 산출 해 보자.

(풀이)

RAND() 함수는 임의의 0~1사이의 임의의 숫자를 생성하는 함수 이다.

RAND() 함수에 1000을 곱하면 0 ~ 1000사이의 값이 생성된다.   

MOD( A, 36) 함수는 A를 36으로 나눈 나머지 값을 준다.   결국 0 ~ 35 값이 생성된다.

로또번호는 36까지의 번호이니 0이 나오지 않도록 기본 값 1을 주도록 하자

결국 = MOD ( (RAND()  * 1000), 36) + 1

이 예제는 RAND() 함수와 MOD() 함수를 소개하고자 한 것이다.

목차로 이동 

목차로 이동 

수경 그룹 공채에 수십명이 지원 했다.

인사팀 수박씨는 인품에 가중치를 두어 채점표를 만들라는 사장님 지시를 받았다.

편의상 2명의 가중치 채점표를 작성해 보자.

(풀이)

가중치 환산 점수 구하는 공식  = 점수 x (가중치값 / 가중치전체합산값)

점수합계는 A, B 동일하지만 수경그룹의 인품 중시하는 가중치 환산 결과 B가 우수한 점수로 나왔다.

목차로 이동