1. 아별툴
  2. 아별툴 패밀리
  3. 엑셀 질문하기
  4. 엑셀 강좌
  5. 엑셀 팁
  6. 엑셀 자료실
  7. 엑셀 연구과제
  8. 엑셀 북마크
  9. 관련 프로그램 소개
글 수 14

[강좌] 다중 조건 조회 및 중복 항목 제거하여 개수 구하기

엑셀일반 조회 수 15538 추천 수 1 2009.10.10 02:23:20
[레벨:30]아별 *.130.73.76

 다중 조건 조회 및 중복 항목 제거 
작성자 : 아별닷컴 주인장 오주원
최초 게시물 : http://www.abyul.com/zbxe/69678
중복 항목의 갯수를 구하는 방법에 대한 질문이 많아서 정리를 했습니다.
삶에 도움이 되었으면 좋겠습니다. ^-^;;
저작자의 동의 없이 본 게시물을 임의의 공간에 배포 / 전제를 금지합니다.
첨부된 엑셀 파일을 다운 받으셔서 보시면, 수식을 직접 편집하면서 볼 수 있습니다.
파일 다운 받기 abyul_20091009_matchingToMultipleOption.xls
 ▲ 문제 제시  　 　 　 　 　 　 　 　 　
직원들의 근태 데이터가 주어지고,
다음과 같은 질문이 있을 경우에 이를 해결하기 위한 방법을 설명하는 방식으로 강좌는 진행될 것입니다. 
 1. 지각한 횟수 　 　 　 　 　
 2. 지각한 사람 　
 3. 서울 살면서 지각한 사람 - 괘씸한 　
 4. 서울 살면서 직급이 사원급이면서 지각한 사람 - 어처구니 없는
 5. 직위가 사원 중에서 지각한 사람 - 개념 없는 　 　
< 직원들의 근태 데이터 >
성명 성별 직위 직급 주소1 주소2 근태
박승민 남자 과장 간부 경기 용인시 결근
홍지민 여자 사원 사원 서울 광진구 결근
백현정 여자 사원 사원 서울 서초구 지각
황준영 남자 대리 사원 경기 군포시 결근
이미경 여자 차장 간부 서울 서초구 결근
남경자 여자 사원 사원 서울 성북구 지각
오정웅 남자 대리 사원 서울 마포구 결근
백현정 여자 사원 사원 서울 서초구 지각
황준영 남자 대리 사원 경기 군포시 결근
이서윤 여자 차장 간부 서울 송파구 지각
강은경 여자 대리 사원 서울 성동구 지각
황준영 남자 대리 사원 경기 군포시 지각
이미영 여자 차장 간부 서울 강남구 결근
유중수 남자 사원 사원 서울 구로구 결근
조은정 여자 대리 사원 경북 구미시 지각
백현정 여자 사원 사원 서울 서초구 지각
황준영 남자 대리 사원 경기 군포시 지각
김수빈 여자 상무 임원 서울 강남구 결근
박승민 남자 과장 간부 경기 용인시 결근
김상훈 남자 차장 간부 서울 중랑구 지각
 ▲ 강좌 시작합니다. 
다중 조건을 만족하는 개수를 구하는 방법과
다중 조건을 만족하는 항목 중 중복을 제거한 개수를 구하는 방법
 솔직히 저 아래 2번째의 피벗테이블을 사용하면 다중 조건의 항목들을 쉽게 뽑아낼 수 있는데,  　 　
 아래 배열 수식들을 사용하는지는 잘 모르겠습니다. 물론 필요할 때도 있지만, 배열 수식보다는 피벗테이블의 활용을 늘리세요~ 
 ▲ 배열 함수의 활용  　  - 수식 작성 후 Ctrl+Shift+Enter로 입력 
1. 지각한 횟수
10 =COUNTIF(H26:H45,"지각")
2. 지각한 사람
7 =SUM(N(FREQUENCY(IF((H26:H45="지각"),MATCH(B26:B45,B26:B45,0)),MATCH(B26:B45,B26:B45,0))>0))
3. 서울 살면서 지각한 사람 - 괘씸한
5 =SUM(N(FREQUENCY(IF((H26:H45="지각")*(F26:F45="서울"),MATCH(B26:B45,B26:B45,0)),MATCH(B26:B45,B26:B45,0))>0))
4. 서울 살면서 직급이 사원급이면서 지각한 사람 - 어처구니 없는
3 =SUM(N(FREQUENCY(IF((H26:H45="지각")*(F26:F45="서울")*(E26:E45="사원"),MATCH(B26:B45,B26:B45,0)),MATCH(B26:B45,B26:B45,0))>0))
5. 직위가 사원 중에서 지각한 사람 - 개념 없는
2 =SUM(N(FREQUENCY(IF((H26:H45="지각")*(D26:D45="사원"),MATCH(B26:B45,B26:B45,0)),MATCH(B26:B45,B26:B45,0))>0))
* 위 배열 수식에 대한 분석은 맨 아래쪽 설명 참고.
 ▲ 피벗 테이블의 활용  　 　 　 　 　 　
1. 지각한 횟수
       10 =GETPIVOTDATA("성명",$B$92)
2. 지각한 사람
         7 =COUNTA(D94:D108)
3. 서울 살면서 지각한 사람 - 괘씸한
         5 =COUNTA(D94:D101)
4. 서울 살면서 직급이 사원급이면서 지각한 사람 - 어처구니 없는
         3 =COUNTA(D97:D100)
5. 직위가 사원 중에서 지각한 사람 - 개념 없는
         2 =COUNTA(H94:H95)
근태 지각
근태 지각 직위 사원
개수 : 성명 　 　 　 　 개수 : 성명 　
주소1 직급 성명 직위 요약 성명 요약
서울 간부 김상훈 차장 1 남경자 1
　 　 이서윤 차장 1 백현정 3
　 간부 요약 　 2 총합계 4
　 사원 강은경 대리 1
　 　 남경자 사원 1
　 　 백현정 사원 3
　 사원 요약 　 5
서울 요약 　 　 7
경기 사원 황준영 대리 2
　 사원 요약 　 2
경기 요약 　 　 2
경북 사원 조은정 대리 1
　 사원 요약 　 1
경북 요약 　 　 1
총합계 　 　 　 10
 ▲ 보조 필드를 추가하여 수식 단순화 시키기    - 수식 작성 후 Ctrl+Shift+Enter로 입력 
1. 지각한 횟수
       10 =COUNTIF(B132:B151,"지각")
2. 지각한 사람
         7 =SUM(IF(B132:B151="지각",(1/COUNTIF(G132:G151,G132:G151))))
3. 서울 살면서 지각한 사람 - 괘씸한
         5 =SUM(IF((B132:B151="지각")*(D132:D151="서울"),(1/COUNTIF(G132:G151,G132:G151))))
4. 서울 살면서 직급이 사원급이면서 지각한 사람 - 어처구니 없는
         3 =SUM(IF((B132:B151="지각")*(D132:D151="서울")*(F132:F151="사원"),(1/COUNTIF(G132:G151,G132:G151))))
5. 직위가 사원 중에서 지각한 사람 - 개념 없는
         2 =SUM(IF((B132:B151="지각")*(D132:D151="서울")*(E132:E151="사원"),(1/COUNTIF(G132:G151,G132:G151))))
* 참고로 "2. 지각한 사람"을 찾을때, 성명 필드를 대상으로 하면 안되고 수식으로 추가한 필드를 사용할 것
이름 필드를 대상으로 했을 경우, 아래와 같이 잘 못된 값이 나온다.
6.5 =SUM(IF(B132:B151="지각",(1/COUNTIF(C132:C151,C132:C151))))
근태 성명 주소1 직위 직급 근태성명
결근 박승민 경기 과장 간부 결근박승민
결근 홍지민 서울 사원 사원 결근홍지민
지각 백현정 서울 사원 사원 지각백현정
결근 황준영 경기 대리 사원 결근황준영
결근 이미경 서울 차장 간부 결근이미경
지각 남경자 서울 사원 사원 지각남경자
결근 오정웅 서울 대리 사원 결근오정웅
지각 백현정 서울 사원 사원 지각백현정
결근 황준영 경기 대리 사원 결근황준영
지각 이서윤 서울 차장 간부 지각이서윤
지각 강은경 서울 대리 사원 지각강은경
지각 황준영 경기 대리 사원 지각황준영
결근 이미영 서울 차장 간부 결근이미영
결근 유중수 서울 사원 사원 결근유중수
지각 조은정 경북 대리 사원 지각조은정
지각 백현정 서울 사원 사원 지각백현정
지각 황준영 경기 대리 사원 지각황준영
결근 김수빈 서울 상무 임원 결근김수빈
결근 박승민 경기 과장 간부 결근박승민
지각 김상훈 서울 차장 간부 지각김상훈
 ▲ Frequency 함수를 활용한 중복 항목 제거 풀이 
서울에서 살면서 지각한 사람
5
　 　 　 중복제거 건수
　 　 FREQUENCY 　
성명 성명 주소1 근태 data_array bins_array 　 　
1 박승민 경기 결근 FALSE 1 0 0
2 홍지민 서울 결근 FALSE 2 0 0
3 백현정 서울 지각 3 3 3 1
4 황준영 경기 결근 FALSE 4 0 0
5 이미경 서울 결근 FALSE 5 0 0
6 남경자 서울 지각 6 6 1 1
7 오정웅 서울 결근 FALSE 7 0 0
8 백현정 서울 지각 3 3 0 0
9 황준영 경기 결근 FALSE 4 0 0
10 이서윤 서울 지각 10 10 1 1
11 강은경 서울 지각 11 11 1 1
12 황준영 경기 지각 FALSE 4 0 0
13 이미영 서울 결근 FALSE 13 0 0
14 유중수 서울 결근 FALSE 14 0 0
15 조은정 경북 지각 FALSE 15 0 0
16 백현정 서울 지각 3 3 0 0
17 황준영 경기 지각 FALSE 4 0 0
18 김수빈 서울 결근 FALSE 18 0 0
19 박승민 경기 결근 FALSE 1 0 0
20 김상훈 서울 지각 20 20 1 1
0 0
 ▲ Frequency 함수에 대한 엑셀 도움말 
* 주요 참고 사항 요약
FREQUENCY(data_array,bins_array)
해당 범위에서 주어진 구간 별로의 빈도 계산하는 함수
Data_array   원본 데이터 영역
Bins_array   빈도를 계산할 구간을 설정하는 영역
Data_array가 없으면 0을 반환. 위에서 IF(지각여부)로 일부러 Data_array가 없도록 했었지요.
반환된 배열의 개수는 bins_array 개수보다 한개더 많음.
　 　 　 　 　 　 　 　 　 　
FREQUENCY
값의 범위 내에서 해당 값의 발생 빈도를 계산하여 세로 배열 형태로 반환합니다. 예를 들어 FREQUENCY 함수를 사용하여 지정한 점수 범위에 들어가는 시험 성적의 개수를 구할 수 있습니다. FREQUENCY는 배열을 반환하므로 배열 수식으로 입력해야 합니다.
구문
FREQUENCY(data_array,bins_array)
Data_array   빈도를 계산할 값 집합의 참조 또는 배열입니다. data_array에 값이 없으면 FREQUENCY 함수는 0의 배열을 반환합니다.
Bins_array   data_array에서 값을 분류할 간격의 참조 또는 배열입니다. bins_array에 값이 없으면 FREQUENCY 함수는 data_array에 있는 요소 개수를 반환합니다.
주의
분포도를 나타낼 인접 셀 범위를 선택한 후, FREQUENCY를 배열 수식으로 입력합니다.
반환된 배열의 요소 개수가 bins_array의 요소 개수보다 하나 더 많습니다. 반환된 배열에 추가된 요소는 최대 구간을 초과하는 값의 개수를 반환합니다. 예를 들어 세 개의 셀에 입력된 세 개의 값 범위(구간)를 계산할 때는 FREQUENCY를 네 개의 셀에 입력합니다. 추가 셀은 세 번째 구간값보다 큰 data_array의 값 개수를 반환합니다.
FREQUENCY 함수에서 빈 셀과 텍스트는 무시됩니다.
배열을 반환하는 수식은 배열 수식으로 입력해야 합니다.
예제
이 예제에서는 모든 시험 점수가 정수라고 가정합니다.
예제를 빈 워크시트에 복사한 다음 보면 더 쉽게 이해할 수 있습니다.
방법
1. 빈 통합 문서나 워크시트를 만듭니다.
2. 도움말 항목에서 예제를 선택합니다. 행 또는 열 머리글을 선택하지 마십시오. 
도움말에서 예제 선택
3. Ctrl+C를 누릅니다.
4. 워크시트에서 A1 셀을 선택하고 Ctrl+V를 누릅니다.
5. 결과를 구하는 수식 보기와 결과 보기 사이를 전환하려면 Ctrl+`(억음 악센트 기호)를 누르거나 도구 메뉴에서 수식 분석을 가리킨 다음 수식 분석 모드를 클릭합니다.
A B
1 점수 저장소
2 79 70
3 85 79
4 78 89
5 85
6 50
7 81
8 95
9 88
10 97
수식 설명(결과)
0 70보다 작거나 같은 점수 개수입니다. (1)
저장소 71-79에 있는 점수 개수입니다. (2)
저장소 80-89에 있는 점수 개수입니다. (4)
90보다 크거나 같은 점수 개수입니다. (2)
참고  예제의 수식은 배열 수식으로 입력해야 합니다. 예제를 빈 워크시트에 복사한 후 수식 셀로 시작되는 범위 A13:A16을 선택하고 F2 키를 누른 다음 Ctrl+Shift+Enter를 누릅니다. 수식을 배열 수식으로 입력하지 않으면 결과는 항상 1이 됩니다.




'==========================================================================================================

'- 내용추가 : 2013.03.11

'- 2가지 이상의 조건을 만족하는 값 찾기.. VLOOKUP의 확장.. 

'==========================================================================================================


다운받기 : abyul.com_20130311_getDataWithMultipleOptions_2.xlsx


abyul.com_0000766-1.jpg








이 게시물을...
profile
첨부
엮인글 :
http://www.abyul.com/zbxe/69678/d32/trackback
목록

댓글 '10'

[레벨:2]찰스박

2010.02.03 07:45:36
*.228.154.151

좋은 강좌네요. 감사합니다. 시간날 때 공부해야 겠습니다.^^

[레벨:1]가족사랑

2010.11.27 08:02:12
*.184.43.198

정말 좋은 강좌네요. 이해하려면 한참 시간이 필요하지만 열심히 해 봐야겠습니다. 감사합니다.^_^

[레벨:0]aaaaaafffff

2011.06.23 08:57:19
*.166.148.164

천천히 공부해야 겠다.

[레벨:0]aaaaaafffff

2012.02.26 09:43:12
*.44.25.93

가끔  잃어버리면 다시 와서...

profile

[레벨:30]아별

2012.02.27 16:11:13
*.104.126.21

하하.. 이 수식들이 잘 기억이 안되는 녀석들이예요.. ^^;;

덕분에 가끔 와주셔서 댓글도 달아주시니 저는 감사할 따름입니다. ㅎㅎ

즐거운 하루 되세요.. ^^)/

[레벨:4]현대자동차

2012.03.03 12:03:42
*.29.96.161

감사합니다..

[레벨:4]알랑알랑♥

2012.07.16 14:10:45
*.161.30.117

보고 따라해서 5번 시도해서 1번 성공하고....

다시 랜덤주기로 반복중 -_-;;

그렇게 반복하기를 4번째인데...

(5번 시도해서 1번성공을-> 5번반복 -_-;;)

 

아직도 보면 생소한 강좌 ㅠㅠ

내가 엑셀을 너무 모르는것 같아 하염없이 작아져요 ㅠㅠ

profile

[레벨:30]아별

2012.07.17 03:02:05
*.104.126.21

띠용~~

제가 어렵게 설명해놨나요? ㅠㅠ

 

좀 내용 자체가 쉽지는 않은데..

지금 다시 보니.. 설명 자체가 어렵고 재미 없기는 하네요.. ^_^;;

 

그래도 5번에 1번은 성공하신다니.. 천만 다행입니다. 하하..

 

즐거운 하루 되세요.. ^^)/

[레벨:4]쟈니

2013.03.12 13:21:00
*.218.33.26

2가지 이상의 조건을 만족하는 값을 찾는 방법의 첨부 엑셀파일에서 회색의 수식칸이 다른 파일과 연결이 걸려있어 내용이 보이지 않네요. 그리고 마지막 보조필드 활용 VLOOKUP 에서 번호에 해당하는 수식의 변수가 반과 같아 똑같은 결과가 나온걸로 그림파일에도 첨부파일에도 되어있네요. 지적질이 아닙니다. 고퀄의 강좌에 등장하는 옥의 티에 대한 아쉬움이나 안타까움?^^

profile

[레벨:30]아별

2013.03.13 00:30:25
*.104.126.21

쟈니님..

아익후.. 급하게 올려서 실수가 있었네요.. ^_^;;;


특정셀의 값이 아닌 수식 자체를 갖고 오는 아별함수 F() 함수를 사용했던 것을 그냥 올려버렸습니다.

아별툴을 설치하면 볼 수 있다지요..?

근데 아별툴 사용자끼리라도 사람들마다 설치 경로가 달라서 호환이 잘 안되기도 합니다..

다음버전에는 C드라이브 특정 경로에 설치되도록해서 다른 컴퓨터에서도 동일하게 작동되도록 수정해야겠습니다.

C:\abyul\Excel\ 여기에 아별툴을 파일을 .. +_+;;

나중에 Excel말고 파워포인트나 워드 추가기능도 만들면 C:\abyul\PowerPoint\ 머 이런식으로.. ㅎㅎ


본문 내용을 수정하였습니다.

소중한 지적질 감사합니다. ^^

문서 첨부 제한 : 0Byte/ 2.00MB
파일 제한 크기 : 2.00MB (허용 확장자 : *.*)
List of Articles
번호 제목 글쓴이 날짜 조회 수
14 엑셀일반 함수에 포함된 "--" 기호 의미 [1] 파야 2014-11-24 2758
13 엑셀일반 [강의 준비 아이디어 ] 상대 참조와 절대 참조 설명용.. [레벨:30]아별 2014-09-03 2710
12 VBA [강의 준비 아이디어 ] 매크로가 필요할 때.. file [1] [레벨:30]아별 2014-06-23 3493
11 기타 [교육안내] 교육비 환급(80%~100%) 업무에 바로 쓰는 엑셀 2007 활용실무 imagefile [레벨:30]아별 2013-12-05 2786
10 VBA [강좌] VBA로 *.ini 파일 다루기.. imagefile [레벨:30]아별 2013-09-12 4912
9 VBA 유저폼에 이미지 쉽게 집어 넣기.. imagefile [레벨:30]아별 2012-09-06 7245
8 엑셀일반 [강좌] 셀안에 이미지 또는 도형 정렬하기 imagefile [4] [레벨:30]아별 2011-10-11 10960
7 [강좌] 차트에 보조선 넣기 imagefile [17] [레벨:30]아별 2011-08-09 7617
6 [강좌] 매크로 함수를 활용하여 수식셀 자동 색상 지정하기 imagefile [6] [레벨:30]아별 2011-07-19 6518
5 [강좌] 차트 데이터 레이블을 내 맘대로 다른 것으로 바꾸고 싶을때... imagefile [4] [레벨:30]아별 2011-07-14 6795
4 엑셀에서 자간이 벌어진것 같은 문자를 정상으로 만드는 ASC 함수 file [레벨:30]아별 2009-10-29 7061
» 엑셀일반 [강좌] 다중 조건 조회 및 중복 항목 제거하여 개수 구하기 imagefile [10] [레벨:30]아별 2009-10-10 15538
2 NETWORKDAYS() : 근무일수를 구하는 함수 imagefile [7] [레벨:30]아별 2009-08-25 10277
1 DATEDIF 함수 file [5] [레벨:30]아별 2009-07-17 9708
목록
쓰기