웹사이트 검색

엑셀의 UNIQUE 함수는 이 방법을 사용하지 않으면 열을 건너뛸 수 없습니다

엑셀의 UNIQUE 함수는 데이터를 정리하는 데 혁신적인 도구이지만, 한 가지 짜증나는 제한이 있습니다: 인접한 열에서만 작동한다는 점입니다. 하지만 추가 함수를 그 안에 중첩하면 포함하고 싶지 않은 열을 무시하는 동적 맞춤 목록을 만들 수 있습니다.

기본 사항: UNIQUE가 일반적으로 작동하는 방식

Excel의 UNIQUE 함수는 2021년 이후 출시된 독립 실행형 Excel 버전, Microsoft 365용 Excel, 웹용 Excel, 최신 모바일 및 태블릿 앱에서 사용할 수 있으며, 다음과 같은 구문을 사용합니다:

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

위치 :

  • array (필수): 고유 값을 추출하려는 범위 또는 표입니다.
  • by_col (선택 사항): Excel에게 행 대신 열을 비교하도록 지시합니다(TRUE로 설정하면, 기본값은 FALSE).
  • exactly_once (선택 사항): 소스에서 한 번만 나타나는 항목을 반환합니다(값이 TRUE로 설정된 경우). 모든 고유 값을 나열하는 대신 반환하며, 기본값은 FALSE입니다.

UNIQUE 함수는 동적 배열 계열의 일부로, 하나의 셀에만 수식을 입력해도 결과가 자동으로 주변 셀로 흘러 들어갑니다. 결과 주위에는 얇은 파란색 테두리(흘러나오는 범위)가 나타나며, 만약 중간에 무언가가 막고 있으면 #SPILL! 오류가 표시됩니다.

인접한 열 추출

집안 지출을 T_Expenses라는 엑셀 표에 기록하고 있다고 상상해 보세요. 그리고 결제 카테고리와 매장의 모든 고유 조합 목록을 생성하고 싶다고 가정해 봅시다.

이들은 테이블에서 인접한 열이므로 array 인수에서 콜론(:)을 사용하여 함께 연결할 수 있습니다.

=UNIQUE(T_Expenses[[Category]:[Store]])

인접하지 않은 열을 추출하는 문제

이제, 테이블에서 인접하지 않은 열인 카테고리와 결제 방법의 모든 고유 조합 목록을 생성하고 싶다고 가정해 보겠습니다.

UNIQUE 함수에서 특정 열만 개별 인수로 선택하려고 하면 #VALUE! 오류가 나타납니다. 이는 UNIQUE 함수가 하나의 연속된 배열을 요구하기 때문입니다. 두 개의 별도 열을 쉼표로 구분하여 선택하면, Excel은 두 번째 열을 by_col 인수로 사용하려고 하지만, 이 인수는 TRUE 또는 FALSE 값을 요구하므로 함수가 오류를 일으킵니다.

이 문제를 해결하는 두 가지 방법이 있습니다.

빠른 해결책: UNIQUE 안에 CHOOSECOLS 중첩하기

열을 건너뛰려면, 원하는 데이터만 포함된 가상 테이블을 UNIQUE 함수에 제공해야 합니다. CHOOSECOLS 함수는 바로 이것을 위해 설계되었습니다. 이 함수는 인덱스 번호를 사용하여 더 큰 범위나 테이블에서 특정 열을 선택할 수 있게 해줍니다.

T_Expenses 테이블에서 Category는 두 번째 열이고, Method는 네 번째 열입니다. 따라서 고유 목록을 얻기 위해서는 CHOOSECOLS 함수를 다음과 같이 중첩하면 됩니다:

=UNIQUE(CHOOSECOLS(T_Expenses,2,4))

CHOOSECOLS는 전체 T_Expenses 테이블을 보고 2열과 4열만 제외한 모든 것을 무시합니다. 그런 다음 이 새로 생성된 두 열 가상 테이블을 UNIQUE 함수에 전달하면, UNIQUE 함수가 검사를 수행하고 결과를 반환합니다.

완벽한 해결책: 동적 헤더를 위해 MATCH 사용하기

CHOOSECOLS 메서드는 완벽하게 작동하지만 한 가지 결점이 있습니다. 바로 하드코딩된 인덱스 번호에 의존한다는 점입니다. T_Expenses 테이블에 새 열을 삽입하면 4번째 열이 더 이상 Method 열이 아니므로, 수식이 잘못된 데이터를 표시하게 됩니다.

공식을 파괴 불가능하게 만들려면 대신 MATCH 함수를 사용하여 이름으로 열을 찾을 수 있습니다.

=UNIQUE(
CHOOSECOLS(
T_Expenses,
MATCH(G1,T_Expenses[#Headers],0),
MATCH(H1,T_Expenses[#Headers],0)
))

수식 입력줄에 수식을 입력할 때 Alt+Enter를 눌러 줄 바꿈을 만드세요. 이렇게 하면 수식을 더 쉽게 작성하고 읽을 수 있습니다.

여기서는 2와 4를 직접 입력하는 대신, MATCH 함수가 대신 계산해 줍니다. 첫 번째 MATCH 함수는 셀 G1(“Category”)의 텍스트를 확인하고 테이블의 헤더 행에서 이를 검색합니다. 마지막의 0은 엑셀이 정확히 일치하는 값을 찾도록 지시합니다. 그러면 선택된 범위를 기준으로 상대적인 숫자를 반환하는데, 만약 Category가 두 번째 열이라면 결과는 2가 됩니다. 두 번째 MATCH 함수는 셀 H1(“Method”)의 텍스트에 대해 똑같이 작동하며, 4를 반환합니다.

다음과 같은 이유로 이것이 확실한 해결책입니다:

  • 구조적 자유: 범주 열을 테이블의 끝으로 이동하거나 중간에 다섯 개의 새 열을 삽입할 수 있습니다. MATCH 함수는 항상 이름을 헤더에서 찾기 때문에 하드코딩된 인덱스 번호에 의존하지 않아도 수식이 자동으로 업데이트됩니다.
  • 오류 방지: 이 방법은 수십 개의 열이 있는 큰 테이블에서 열을 잘못 계산할 위험을 제거합니다.
  • 동적 선택: 이 수식은 G1과 H1 셀과 연결되어 있으므로, 이 셀들의 열 이름을 변경하기만 하면 새로운 고유 목록을 생성할 수 있습니다.

전문가 팁: INDIRECT 함수를 사용하여 헤더 드롭다운을 만들어 보세요

페어링을 더 직관적으로 만들기 위해, G1과 H1 셀을 드롭다운 메뉴로 바꿀 수 있습니다. 이렇게 하면 단순한 오타로 인해 수식이 깨지는 것을 방지할 수 있습니다.

하지만 기술적인 장벽이 있습니다. 엑셀의 데이터 유효성 검사 도구는 구조화된 참조(예: T_Expenses[#Headers])를 기본적으로 지원하지 않습니다. INDIRECT 함수를 사용하면 텍스트 문자열을 데이터 유효성 검사가 이해할 수 있는 유효한 참조로 변환하여 이 문제를 우회할 수 있습니다.

먼저 동적 헤더 선택이 들어갈 셀을 선택합니다(이 경우 G1과 H1) 그리고 데이터 탭에서 '데이터 유효성 검사' 아이콘을 클릭합니다.

이제 '허용' 드롭다운 메뉴에서 '목록'을 선택하고, 다음 INDIRECT 수식을 '원본' 필드에 입력합니다:

=INDIRECT("T_Expenses[#Headers]")

"확인"을 클릭하면 대화 상자가 닫히고, G1 및 H1 셀에는 T_Expenses 테이블의 모든 헤더를 표시하는 클릭 가능한 화살표가 나타납니다.

목록에서 새 열 이름을 선택하는 순간, MATCH 함수가 새 위치를 찾고, CHOOSECOLS가 데이터를 가져오며, UNIQUE가 목록을 즉시 업데이트합니다. 또한 INDIRECT 함수가 테이블 헤더를 특정해서 보기 때문에, T_Expenses 테이블에 새 열을 추가하면 데이터 유효성 검사 설정을 업데이트하지 않아도 드롭다운 메뉴에 자동으로 나타납니다.

UNIQUE 함수는 동적 배열에 기반하기 때문에, 데이터를 처리하는 데 있어서 오래된 중복 제거 도구보다 훨씬 안전하고 효율적인 방법입니다. 오래된 도구는 정보를 영구적으로 덮어쓰지만, 이 설정을 사용하면 마스터 목록이 그대로 유지되고, 스프레드시트를 아무리 재배열해도 고유 요약은 정확하게 유지됩니다.

관련기사