실무에서 자주 사용하는 중복 합계, COUNTIF(), VLOOKUP(), 데이터 유효성 검사(드롭박스), 이름 정의 따라하기

안녕하세요.

백문이 불여일견(百聞 不如一見)

백견이 불여일행(百見 不如一行)

이라는 말이 있죠.

 

엑셀 실무에서 자주 사용할 만한 것들 한번 따라해봅시다.

우선 데이터를 아무거나 상관없이 입력해볼게요.

원본 데이터

입력 다하셨나요? 데이터 별도로 가지고 계신거 사용하셔도 되구요. 형태만 맞춰 주시면 될 것 같아요.

그럼 아래 이미지 보면서 중복합계 설명드릴게요.

▣ 중복합계

중복 합계

1 위치에 중복합계를 보이게 할려고 저는 F4 셀을 선택했고

다른 원하는 위치가 있으면 다른 셀 선택하시면 됩니다.

 

2 데이터-통합 눌러서 통합창이 뜨면 순서대로 3 버튼 눌러서 범위 드래그로 지정하고

그림대로 따라하면 되겠네요.

(4 확인 누르면 품목은 지워져 있을텐데 그냥 적어주세요.)

-- 중복 합계 끝 --

 

 

이제 품목이 몇번 중복이 되었는지 COUNTIF() 함수를 사용해 확인해 볼게요.

▣ COUNTIF() 함수

COUNTIF() 함수

그림대로 범위 지정해주시고, 조건 선택해주시면 어려울 건 없을 것 같네요.

조건은 지금은 값만 선택했는데 수식으로 설정할 수도 있습니다.

여기서 퀴즈

예를 들면 명을 범위 지정하고 2이상인 수는 몇개인가라고 수식을 사용한다면?

=COUNTIF(D5:D24,">=2")

이렇게 작성하면 됩니다.(드래그하면 보임)

-- COUNTIF()함수 끝 --

 

 

이번에는 데이터 유효성 검사의 드롭박스, VLOOKUP()함수를 사용해 원하는 값의 항목들을 보려고 합니다.

그전에 이름 정의 설명 한번 드릴게요.

▣ 이름 정의

이름 정의

범위를 드래그 하고 2번에 이름 적고 엔터 누르면 이름 정의 되고 끝.

이름 정의 편집하고 삭제 하려면 수식-이름 관리자에서 가능.

함수에서 자주 사용하는 범위를 이름 정의하면 수식 적을 때 편리합니다.

예를 들어 위에 COUNTIF사용한거에 응용하면 =COUNTIF(품목,F5) 이렇게 입력할 수 있겠죠.

(그런데 위와 같이 입력하면 1로 나올거에요 이름정의가 원본 목록이 아니고 이미 중복 처리된 목록으로 지정한거라서요)

-- 이름 정의 끝 --

 

 

▣ 데이터 유효성 검사로 드롭 박스 설정하기

데이터 유효성 검사(드롭박스)

데이터-데이터 유효성 검사에서 그림대로 따라하시면 되겠습니다. 범위는 =품목 이렇게 직접 타이핑 하시면 됩니다.

-- 데이터 유효성 검사(드롭박스) 끝 --

 

 

▣ VLOOKUP() 함수로 원하는 값 찾기

기본 구조는 =VLOOKUP(찾을 값, 범위, 열번호(왼쪽부터), 일치옵션)

M5셀에 =VLOOKUP(L5,F5:H12,2,0) 입력 

N5셀에 =VLOOKUP(L5,F5:H12,3,0) 입력

간단히 설명드리면,

감귤이라는 값을 찾아 중복 합계 데이터 범위내에서 몇 번째 열 데이터를 불러온다로 보시면 되겠네요.

일치옵션은 실무에서 FALSE 또는 0을 입력하여 '정확히 일치' 옵션으로 사용하는 것이 일반적,

TRUE 또는 1 유사일치는 정확히 일치하는 값이 없을 경우 그 위의 값을 반환 하고

주의할 점은 범위가 반드시 오름 차순으로 정렬 되어있어야 합니다.

 

추가 팁을 드리면 F4 셀에서 데이터-필터하셔서 오름차순 정렬 하시면 드롭박스 목록이 오름차순으로 보이실거에요.

-- VLOOKUP() 함수 끝 --