Unique 함수와 데이터 유효성 검사의 문제점

데이터유효성검사 목록.xlsx

0.01MB

 
 
 

1.  데이터 유효성 검사 - 날짜 목록

아래와 같이 날짜별 지점별 판매량 데이터가 있을 경우

 
E2셀을 클릭하고,
데이터 탭, 데이터 도구 그룹, 데이터 유효성 검사 명령을 누른 후
제한 대상으로 목록을 선택하고,
원본에 커서를 넣고, A열을 클릭해서 A열 모두로 지정하고 확인 버튼을 누르면,

 
유효한 데이터만 표시되고, 아래에 공백이 없습니다.

 
날짜가 모두 표시되지 않으므로 E열 오른쪽의 경계선을 오른쪽으로 끌어서 너비를 조절합니다.

 
이때 열 너비를 자동 조절하기 위해 E열과 F열의 경계선을 더블 클릭하면 오히려 선택된 값에 맞게 좁아져서 안됩니다.

 
이때 홈 탭, 표시 형식 그룹에서 표시 형식을 숫자에서 간단한 날짜로 변경하면 열 너비가 자동 조절됩니다.
 
 

2.  Unique 함수를 사용해 목록을 만들 경우 문제점

Unique 함수를 이용해 중복된 날짜를 제거하고 고유한 날짜만 G2셀에 만들어보겠습니다.
 
Unique함수의 구문은
=UNIQUE(array,[by_col],[exactly_once])
으로 array만 필수요소이고, by_col(열 지정), exactly_once(정확히 일치하는 경우만)은 옵션이므로 지정하지 않아도 되며,
 
Unique 함수는 아래와 같이 Microsoft 365, 웹용 Excel 등에서만 작동하는 함수입니다.

 
G2셀에 =unique(A2셀까지 입력하고, Shift + Ctrl + ↓를 입력해서 맨 아래 셀까지를 범위로 지정하고 엔터키를 누릅니다.
그러면 유일한 값이 표시되는데, 숫자로 표시됩니다. 

 
G열을 클릭한 다음 홈 탭, 표시 형식 그룹에서 간단한 날짜를 클릭해서 날짜 형식으로 수정합니다. E열도 클릭해서 간단한 날짜 형식으로 수정합니다.

 
그리고, E2셀을 클릭한 다음 데이터 유효성 검사 목록의 원본을 G2셀부터 G열의 맨 아래셀까지로 수정하고(Shift + Ctrl + ↓ 키 두 번 클릭), 확인 버튼을 누릅니다.

 
 
그리고, E2셀의 콤보 상자 버튼을 누른 후 아래를 보면 위와는 달리 공백이 여러 칸 있습니다.

 

3. 해결책

가. 원본 수정

목록 상자에서 공백을 제거하려면
G열에 표시되는 개수만큼만 원본으로 지정하면 됩니다.
 
그런데, 날짜의 개수가 가변적이므로 Offset과 Count함수를 사용하는데,
 
Offset함수는 지정된 위치(reference)에서 행(rows)과 열(cols)만큼 이동한 후 높이(height)와 너비(width)에 해당하는 범위에 대한 참조를 반환하는 함수이며,
  ※ 구문 : OFFSET(reference, rows, cols, [height], [width])
 
Count함수는 숫자의 개수를 세주는 함수인데, 날짜도 숫자이므로 괜찮습니다.
  ※ 구문 : COUNT(value1, [value2], ...)
 
E2셀의 원본을 아래와 같이 수정하고, 확인 버튼을 누르면,
=OFFSET(G2,0,0,COUNT($G$2:$G$1048576))

 
숫자의 개수만큼만 표시되므로 범위가 넓더라도 아래에 공백이 없습니다.

 

나. 날짜에서 0(1900-01-00) 제거

G2셀의  unique함수도 Offset과 Count함수를 이용해 아래와 같이 수정합니다.
=UNIQUE(OFFSET(A2,0,0,COUNT(A2:A1048576)))

 
그러면 맨 아래에 있던 1900-01-00이 제거됩니다.
 

다. 목록 상자에 공백 추가

특정 날짜를 선택할 수도 있지만 빈 값을 선택해야 하는 경우도 있으므로 맨 아래에 공백을 추가하려면 count함수 뒤에 +1을 추가하면 됩니다.

 
그러면 공백이 맨 아래에 하나만 추가됩니다.

 

라. 데이터 영역을 표로 지정하기

A2셀에 커서를 놓고 Ctrl+T 또는 삽입 - 표를 누릅니다.

 
그러면 범위가 알아서 A1에서 C13으로 정해지고, 머리글 포함에도 체크됩니다. 머리글 포함이란 '첫째 줄이 머리글'이라는 의미입니다. 확인 버튼을 누릅니다.

 
그러면 탭 표시줄 맨 오른쪽에 테이블 디자인 탭이 생기고, 맨 왼쪽 속성 그룹을 보면 표 이름이 표1로 지정되었습니다.

 
표를 이용하면 날짜에 해당하는 A2셀부터 A13셀까지를 A2:A13이 아니라 표1[날짜]로 간단하게 지정할 수 있고, 범위가 늘어나더라도 자동으로 범위가 수정되는 장점이 있습니다.
 
G2셀을 클릭하고, 데이터 유효성 검사를 누른 후
=UNIQUE(OFFSET(A2,0,0,COUNT(A2:A1048576)))에서 
A2:A1048576를 표1[날짜]로 수정하는데,
 
표1[까지 입력하면 날짜 등 열 이름이 표시되므로, 날짜로 이동 후 탭을 누르거나, 마우스로 더블 클릭해서 선택하고 ]를 입력합니다. 그리고, 엔터 키를 누르면

 
똑같이 유일한 날짜만 추출되는데,

 
A14셀에 데이터를 한 줄 추가하면 G열의 날짜가 하나 늘어나게 되는 장점이 있습니다.

 
표를 범위로 변환하기 위해 테이블 디자인 탭의 속성 그룹에서 '범위로 변환'을 누르면

 
'표를 정상 범위로 변환'할 것인지 묻는데, 예를 클릭하면

 
머리글에 있던 필터링 컨트롤이 없어집니다. 그런데, 색은 원래대로 돌아오지 않습니다.

  ※색을 원래대로 돌리려면 데이터 영역을 선택한 후 홈 탭, 스타일에서 표준을 선택하면 됩니다.
 
 
정상 범위인 경우는 A15셀에 날짜를 추가하더라도 count 함수의 범위가 A2에서 A14까지로 고정이기 때문에 Unique 함수의 결과가 달라지지 않습니다.

 

데이터유효성검사 목록(완성).xlsx

0.01MB