다중 조건 조회 및 중복 항목 제거 |
작성자 : 아별닷컴 주인장 오주원 |
|
|
|
|
|
|
|
|
최초 게시물 : 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이 됩니다. |