[Excel] 중복하여 기록된 데이터에서 동일한 데이터는 하나만 추출하여 고유한 목록을 생성하는 방법
아래와 같이 상품에 대한 주문을 Excel로 작성하였다고 가정할 때, 이 업체에 상품을 공급한 공급자의 목록을 추출하여 봅시다.
방법 1: 고급 필터 이용하기
1. 필터를 적용할 공급자 열을 선택합니다.
2. [데이터] 탭의 [정렬 및 필터] 그룹에서 [고급] 단추를 선택합니다.
[97-2003 버전]
[데이터] 메뉴의 [필터]-[고급 필터]를 선택합니다.
3. [다른 장소에 복사]를 선택한 뒤, [복사 위치]에는 데이터가 표시될 범위의 첫 번째 셀 위치를 선택합니다.
4. [동일한 레코드는 하나만]을 선택 후 [확인]을 누릅니다.
[97, 2000 버전]
[동일한 레코드는 하나만] 대신 [고유 레코드만] 메뉴를 선택합니다.
5. [복사 위치]에 선택했던 셀을 기준으로 고유한 목록이 표시됩니다.
방법2: 수식과 이름 정의 이용하기
수식과 이름 정의에 대한 자세한 설명을 보려면 아래 ‘부연설명’ 섹션을 참고하시기 바랍니다.
A. A2:A29 셀을 선택합니다.
B. [수식] 탭의 [정의된 이름] 그룹 중 [이름 관리자]를 선택합니다.
[새로 만들기]를 누릅니다.
[97-2003인 경우]
[삽입]-[이름]-[정의]를 누릅니다.
C. ‘이름’ 필드에는 수식에서 사용할 이름 ‘공급자범위’를 적습니다.
[97-2003인 경우]
[통합 문서에 있는 이름]에 ‘공급자범위’를 적습니다.
D. ‘참조 대상’에는 실제 데이터가 들어있는 셀 범위의 주소 =시트이름!$A$2:$A$29가 맞는지 확인 합니다.
E. 확인을 눌러 닫습니다.
F. B2 셀을 선택합니다.
G. 아래의 수식을 입력합니다.
=IF(ISERROR(SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))))
H. 배열 수식이므로 Ctrl+Shift+Enter를 누릅니다. 수식 입력 줄에서 { =IF(ISERROR(SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1)))) } 로 입력된 것을 확인할 수 있습니다.
I. ROW(A1)의 행 번호를 자동으로 증가시키기 위해서 B29셀까지 자동 채우기 핸들로 채워 내립니다.
부연설명:
수식을 이용할 때에는 다음과 같은 순서대로 구합니다.
(1) 공급자 범위(A2:A29)에서 특정 공급자가 나타난 위치들 중 제일 처음 나온 위치를 찾아 기록합니다. ‘A 식품 ㈜’의 경우 제일 처음 발생한 위치가 A2:A29 범위 내에서 1행이므로 1이라는 숫자 값이 나타납니다.
(2) A2는 A2:A29 범위에서 1행입니다. (1)의 결과 값 중 1이라는 위치 정보를 가진 항목은 8개입니다. 각 위치 번호의 발생 빈도 수를 구합니다.
(3) 빈도 수가 0보다 큰 경우 위치 번호를 반환합니다.
(4) 위치 번호를 순서대로 표시하기 위해서 정렬 시킵니다.
(5) 위치에 해당하는 공급자 이름 가져와 표시합니다.
1. 공급자 범위에서 특정 공급자가 나타난 위치 중 첫 번째 위치 찾아 기록하기: MATCH 함수
MATCH 함수
MATCH 함수는 셀 범위에서 지정된 항목을 검색한 다음 범위 내에서 해당 항목의 상대 위치를 반환합니다.
참고 자세한 사항은 Excel에서 F1을 눌러 도움말 창을 열고, MATCH로 검색하여 MATCH 함수에 대한 도움말을 참고 하시기 바랍니다.
공급자 목록에서 특정 공급자가 제일 처음에 나온 위치를 찾으려면 아래와 같이 함수를 작성합니다.
A. B2:B29 범위를 선택합니다.
B. 아래와 같이 수식을 입력합니다.
=MATCH($A$2:$A$29, $A$2:$A$29, 0)
C. 배열 수식이므로 Ctrl+Shift+Enter를 누릅니다. 수식 입력줄에서 확인하면 { =MATCH($A$2:$A$29, $A$2:$A$29, 0) } 으로 표시됩니다.
배열 수식
배열 수식을 사용하면 다른 수식으로 할 수 없는 계산을 수행할 수 있습니다.
위 수식의 경우 A2를 A2:A29에서 찾아 상대 위치를 반환하고, 그 다음 값인 A3를 A2:A29에서 찾아 상대 위치를 반환합니다. A4에서부터 A29 역시 동일한 방법으로 찾아 상대 위치를 반환하게 됩니다.
참고 배열 수식에 대한 자세한 사항은 Excel에서 F1을 눌러 도움말 창을 열고, 배열로 검색한 뒤, 배열 수식 지침 및 예제를 참고하시기 바랍니다.
2. 각 위치 번호의 발생 빈도 수 구하기 : FREQUENCY 함수
FREQUENCY 함수
FREQUENCY 함수를 사용하여 지정한 범위에 들어가는 값의 개수를 구할 수 있습니다. 배열을 반환하는 수식은 배열 수식으로 입력해야 합니다.
아래의 예제를 참고하십시오.
참고 자세한 사항은 Excel에서 F1을 눌러 도움말 창을 열고, FREQUENCY로 검색하여 도움말을 참고 하시기 바랍니다.
아래의 표와 같이 B열의 결과의 각 항목을 FREQUENCY 함수를 이용하여 빈도수를 구할 수 있습니다.
A. B2:B29 범위를 선택합니다.
B. 수식을 아래와 같이 입력합니다.
=FREQUENCY( MATCH($B$2:$B$29, $B$2:$B$29, 0) , MATCH($B$2:$B$29, $B$2:$B$29, 0) )
C. 배열 수식이므로 Ctrl+Shift+Enter를 누릅니다. 수식 입력줄을 확인하면 { =FREQUENCY(MATCH($B$2:$B$29, $B$2:$B$29, 0), MATCH($B$2:$B$29, $B$2:$B$29, 0)) } 로 표시됩니다.
3. 빈도 수가 0보다 큰 경우 위치 번호를 반환: IF 함수, ROW 함수
IF 함수
지정한 조건이 TRUE이면 특정 값을 반환하고 FALSE이면 다른 값을 반환합니다.
=IF(A2<=100,"예산 내","예산 초과")
위 함수는 A2의 값이 100보다 작거나 같은 경우 “예산 내”로 표시하고, 100보다 큰 경우 “예산 초과”로 표시합니다.
ROW 함수
참조의 행 번호를 반환합니다.
=ROW(A3)
위 함수는 3을 반환 합니다.
만약, 위치 정보가 0보다 크면, A2:A29의 행 번호를 표시합니다.
아래의 표와 같이 구할 수 있습니다.
A. B2:B29 범위를 선택합니다.
B. 수식을 아래와 같이 입력합니다.
=IF( FREQUENCY(MATCH($A$2:$A$29, $A$2:$A$29, 0), MATCH($A$2:$A$29, $A$2:$A$29, 0)) >0, ROW($A$2:$A$29))
C. 배열 수식이므로 Ctrl+Shift+Enter를 누릅니다. 수식 입력줄에서 확인하면 { =IF(FREQUENCY(MATCH($A$2:$A$29, $A$2:$A$29, 0), MATCH($A$2:$A$29, $A$2:$A$29, 0))>0, ROW($A$2:$A$29)) } 로 표시됩니다.
4. 위치 정보를 정렬: SMALL 함수
SMALL 함수
데이터 집합에서 k번째로 작은 값을 반환합니다. 이 함수를 사용하면 데이터 집합에서 특정 상대 순위를 갖는 값을 반환할 수 있습니다.
참고 자세한 사항은 Excel에서 F1을 눌러 도움말 창을 열고, SMALL로 검색하여 SMALL 함수에 대한 도움말을 참고 하시기 바랍니다.
아래의 표와 같이 ROW(A1)은 1, ROW(A2)는 2, ROW(A3)은 3입니다.
{=SMALL($B$2:$B$29, ROW(A1) )}의 경우 B2:B29에서 1번째로 작은 숫자를 반환하고, {=SMALL($B$2:$B$29, ROW(A2) )}의 경우 B2:B29에서 2번째로 작은 숫자를 반환합니다.
첫 번째 셀에 수식을 입력 후 아래로 드래그하여 채우면 A1, A2, A3... 으로 셀 주소가 자동으로 증가하므로 순서대로 정렬할 수 있습니다.
A. B2에 아래의 수식을 입력합니다.
=SMALL( IF(FREQUENCY(MATCH($A$2:$A$29, $A$2:$A$29, 0), MATCH($A$2:$A$29, $A$2:$A$29, 0))>0, ROW($A$2:$A$29)) , ROW(A1))
B. 배열 수식이므로 Ctrl+Shift+Enter를 누릅니다. 수식 입력줄에서 확인하면 { =SMALL(IF(FREQUENCY(MATCH($A$2:$A$29, $A$2:$A$29, 0), MATCH($A$2:$A$29, $A$2:$A$29, 0))>0, ROW($A$2:$A$29)), ROW(A1)) } 로 표시되어 있는 것을 확인할 수 있습니다.
C. ROW(A1)의 행 번호를 자동으로 증가시키기 위해서 B29셀까지 자동 채우기 핸들로 채워 내립니다.
자동 채우기 핸들
5. 위치 정보에 따른 A열 데이터 값 가져오기: INDIRECT 함수, ISERROR 함수
INDIRECT 함수
텍스트 문자열로 지정된 참조를 반환합니다. 참조가 바로 계산되어 해당 내용이 표시됩니다. 수식 자체는 변경하지 않고서 수식 안에 있는 셀에 대한 참조를 변경하려는 경우에 INDIRECT 함수를 사용합니다.
위와 같은 표에서 =INDIRECT($A$1)을 구하면 A1 셀에서 참조하는 B2의 값, 즉 31이 반환됩니다.
=INDIRECT(“$B$1”)을 구하면 B1 셀의 값, 즉 27이 반환됩니다.
ISERROR 함수
오류 값으로는 #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! 및 #VALUE!가 있습니다.
ISERROR는 참조 셀의 결과가 오류인지 여부를 판단해 주며, IF문과 함께 사용하여 오류 처리를 할 수 있습니다.
=IF(ISERROR(A1), “”, A1)
이 수식은 A1의 값이 오류이면 공백(“”)을, 오류가 아니면 A1의 값을 반환합니다.
만약, 공급자 위치가 없고 오류 값이 있다면 빈 공백(“”)으로 표시하고, 그렇지 않다면 A열에서 값을 찾아옵니다.
공급자의 실제 이름을 가져오려면 A열에 있기 때문에 =INDIRECT(“A”&공급자 위치)로 구할 수 있습니다.
A. B2 셀을 선택합니다.
B. 아래의 수식을 입력합니다.
=IF(ISERROR(SMALL(IF(FREQUENCY(MATCH($A$2:$A$29,$A$2:$A$29,0),MATCH($A$2:$A$29,$A$2:$A$29,0))>0,ROW($A$2:$A$29)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH($A$2:$A$29,$A$2:$A$29,0),MATCH($A$2:$A$29,$A$2:$A$29,0))>0,ROW($A$2:$A$29)),ROW(A1))))
C. 배열 수식이므로 Ctrl+Shift+Enter를 누릅니다. 수식 입력줄을 확인하면 { =IF(ISERROR(SMALL(IF(FREQUENCY(MATCH($A$2:$A$29,$A$2:$A$29,0),MATCH($A$2:$A$29,$A$2:$A$29,0))>0,ROW($A$2:$A$29)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH($A$2:$A$29,$A$2:$A$29,0),MATCH($A$2:$A$29,$A$2:$A$29,0))>0,ROW($A$2:$A$29)),ROW(A1)))) } 로 입력된 것을 확인할 수 있습니다.
D. ROW(A1)의 행 번호를 자동으로 증가시키기 위해서 B29셀까지 자동 채우기 핸들로 채워 내립니다.
6. 이름 정의하기
수식에서는 공급자 범위가 변경되면, 수식을 다시 수정해야 하는 어려움이 있습니다.
공급자 범위에 Excel의 이름 정의 기능을 이용하면, 좀 더 쉽게 범위를 수정할 수 있습니다.
A1:A4를 ‘데이터범위’로 이름을 정의해 놓으면, =SUM($A$1:$A$4)의 수식 대신 =SUM(데이터범위)로 입력하여 결과를 구할 수 있습니다. (결과, 16)
이름 정의하기
1. A1:A4를 선택합니다.
[수식] 탭의 [정의된 이름] 그룹 중 [이름 관리자]를 선택합니다.
[97-2003인 경우]
[삽입]-[이름]-[정의]를 누릅니다.
3. [새로 만들기]를 누릅니다.
4. ‘이름’ 필드에는 수식에서 사용할 이름을 원하는 대로 적습니다.
‘참조 대상’에는 실제 데이터가 들어있는 셀 범위의 주소를 적습니다.
[97-2003인 경우]
[통합 문서에 있는 이름]에 적습니다.
5. [확인]을 누릅니다.
6. 셀 범위 A1:A4를 선택한 상태에서 Excel의 이름 상자를 확인하면 ‘데이터범위’라는 이름으로 정의되어 있음을 확인할 수 있습니다.
아래와 같이 5행이 추가 되었을 때
‘데이터범위’의 셀 범위를 A1:A5로 수정하면 수식의 결과는 자동으로 25로 변경됩니다.
이름을 수정하려면
A. [수식] 탭의 [정의된 이름] 그룹 중 [이름 관리자]를 선택합니다.
B. ‘데이터범위’이름을 선택 후 [편집]을 눌러 수정하거나, [참조 대상]을 수정합니다.
[97-2003인 경우]
[삽입]-[이름]-[정의]를 누릅니다.
‘데이터범위’를 선택 후 참조 대상을 수정한 뒤 [확인]을 누릅니다.
따라서 아래의 수식에서 $A$2:$A$29 범위를 ‘공급자범위’로 변경하면 공급자가 더 추가 기록 되었을 때 이름 정의된 범위만 수정하면 간단하게 결과를 변경할 수 있습니다.
=IF(ISERROR(SMALL(IF(FREQUENCY(MATCH( $A$2:$A$29, $A$2:$A$29,0),MATCH( $A$2:$A$29, $A$2:$A$29,0))>0,ROW( $A$2:$A$29)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH( $A$2:$A$29, $A$2:$A$29,0),MATCH( $A$2:$A$29, $A$2:$A$29,0))>0,ROW ($A$2:$A$29)),ROW(A1))))
=IF(ISERROR(SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))))
A. A2:A29 셀을 선택합니다.
B. [수식] 탭의 [정의된 이름] 그룹 중 [이름 관리자]를 선택합니다.
[새로 만들기]를 누릅니다.
[97-2003인 경우]
[삽입]-[이름]-[정의]를 누릅니다.
C. ‘이름’ 필드에는 수식에서 사용할 이름 ‘공급자범위’를 적습니다.
[97-2003인 경우]
[통합 문서에 있는 이름]에 ‘공급자범위’를 적습니다.
D. ‘참조 대상’에는 실제 데이터가 들어있는 셀 범위의 주소 =시트이름!$A$2:$A$29가 맞는지 확인 합니다.
E. 확인을 눌러 닫습니다.
F. B2 셀을 선택합니다.
G. 아래의 수식을 입력합니다.
=IF(ISERROR(SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))))
H. 배열 수식이므로 Ctrl+Shift+Enter를 누릅니다. 수식 입력 줄에서 { =IF(ISERROR(SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1)))) } 로 입력된 것을 확인할 수 있습니다.
I. ROW(A1)의 행 번호를 자동으로 증가시키기 위해서 B29셀까지 자동 채우기 핸들로 채워 내립니다.
작성자: 한규진