Unique 함수를 이용한 중복된 항목 제거와 데이터 유효성 검사

1. 문제

Unique와 데이터유효성검사.xlsx

0.01MB

 

아래와 같은 자료가 있을 때

 

 

구분에서 중복을 제거한 유일한 데이터만을 추출할 때 데이터 탭의 '중복된 항목 제거' 명령을 사용할 수도 있지만,

https://lsw3210.tistory.com/556 참고

 

Unique 함수를 이용할 수도 있습니다.

 

2. Unique 함수

Unique 함수는 단어 그대로 유일한 값을 찾아주는 함수로서

엑셀 2021 이상에서만 사용 가능한 단점이 있습니다.

 

구문은 

=UNIQUE(array,[by_col],[exactly_once])

이며, array만 필수 요소입니다.

 

3. 유일값 추출

가. Unique 함수의 한계

E1셀에 =unique(b:b)라고 입력하면

구분부터 시작하는데, 맨 아래가 0으로 표시되므로,

 

Unque의 array를 B열의 데이터 개수, 다시 말해 데이터가 들어 있는 셀의 개수로 지정해야 하므로 Offset함수와 연결해야 합니다.

 

또한, 중간에 데이터가 비어 있고, 그다음에 데이터가 나올 경우는 일부 데이터가 포함되지 않을 수 있으므로 데이터를 정렬해서 중간이 비어 있지 않도록 해야 합니다.

 

나. Offset 함수

Offset 함수의 구문은 아래와 같으며

OFFSET(reference, rows, cols, [height], [width])

 

reference는 참조 셀로서 기준이 되는 셀입니다.

 

rows와 cols는 reference를 기준으로 행 또는 열로 이동할 숫자로서

현재 위치는 0이고,

행 수는 위로 이동할 때는 -, 아래로 내려갈 때는 +이고,

열 수는 왼쪽으로 이동할 때는 -, 오른쪽으로 이동할 때는 +인데,

생략하면 0이 됩니다.

 

따라서, rows와 cols를 생략하면 참조 셀이 됩니다.

 

height와 width는 옵션인데, height는 높이, width는 너비이며, 생략하면 기본값은 1입니다.

 

따라서, 위 경우에는 참조 셀은 B1셀이 되고, height(높이)를 지정하기 위해서는 CountA함수를 사용해야 합니다.

 

다. CountA 함수

Count함수는 숫자만을 세는데 비해서, CountA함수는  숫자뿐만 아니라 모든 유형의 데이터, 다시 말해 비어있지 않은 셀의 개수를 모두 세어주는 함수입니다.

 

라. 완성된 수식

완성된 수식은

=unique(offset(b1,,,counta(b:b)))

입니다.

 

rows와 cols가 비어 있으므로 참조셀(시작셀)은 B1셀이 되고,

B열에서 데이터가 들어 있는 셀의 개수를 높이로 하는 범위에서 유일한 값을 찾는 수식입니다.

 

결과는 아래와 같습니다.

 

 

4. 데이터 유효성 검사

데이터 유효성 검사도 마찬가지로 '목록'의 원본을 =E:E로 입력해서 E열로 지정하면

 

맨 아래에 빈칸이 표시되므로 (E열의 데이터 개수-1)를 Offset 함수의 높이로 지정해야 합니다.

 

그러면 데이터 유효성 검사의 원본의 수식은

=OFFSET(E2,,,COUNTA(E:E)-1)

이 됩니다.

 

확인 버튼을 누르면

맨 아래 공백이 제거되었습니다.

 

Unique와 데이터유효성검사(완성).xlsx

0.01MB