엑셀 데이터 기능을 대체하는 함수들(FILTER, SORT, SORTBY, UNIQUE)

안녕하세요.

 

오피스 365와 엑셀 2019 버전에 새로 추가된 함수들 중 엑셀 데이터 기능을 대체하는 함수들이 있습니다.

 

FILTER 함수는 고급 필터 기능과 같고, SORT, SORTBY 함수는 정렬 기능, UNIQUE 함수는 중복된 항목 제거 기능과 같은 역할을 합니다.

 

예제를 이용해 이 함수들을 소개하겠습니다.

이 강좌를 따라하기 위해서는 오피스 365 버전이나 엑셀 2019 버전 이상이 있어야 합니다.

 

실습 파일 다운로드

FILTER, SORT, UNIQUE 함수.xlsx

0.01MB

완성 파일 다운로드

FILTER, SORT, UNIQUE 함수_완성.xlsx

0.02MB

 

FILTER 함수 - 고급 필터 기능

거래내역 중 지점명이 중동점인 항목만 뽑아 L열에 나타내려고 합니다.

이때 사용할 수 있는 방법이 필터 기능입니다.

 

필터 기능에 대해서는 이미 강좌가 있으므로 등록된 강좌를 참고하세요.

 

엑셀 67강] 조건에 맞는 데이터만 화면에 표시 - 텍스트 필터

 

엑셀 72강] 고급 필터

 

FILTER 함수를 이용해 나타내 보겠습니다.

 

찾으려는 지점명을 함수 안에 직접 나타낼 수도 있지만, 다른 지점명으로도 찾을 수 있도록 특정 셀에 지점명을 입력하면 셀 주소를 참조해 수식이 동작하도록 작성하겠습니다.

 

찾을 지점명을 입력할 위치를 나타내기 위해 [J1] 셀에 [B1] 셀 지점명 필드명을 복사해 붙여넣습니다.

[J2] 셀에 중동점을 입력합니다.

 

필터된 결과를 나타낼 [L2] 셀에 커서를 두고 수식을 작성합니다.

=fi를 입력하면 나타나는 함수 목록에서 FILTER를 더블클릭합니다.

단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.

첫번째 배열 인수 창에 커서를 두고 [A2] 셀을 선택한 뒤 단축키 <Ctrl + Shift + 아래쪽 화살표>, <Ctrl + Shift + 오른쪽 화살표>를 눌러 [A2:H57] 셀 범위를 나타냅니다.

두번째 포함 인수 창에 커서를 두고 조건으로 사용될 셀 범위인 [B2:B57] 셀 범위를 나타내기 위해 [B2] 셀을 선택한 다음 단축키 <Ctrl + Shift + 아래쪽 화살표>를 눌러 B2:B57이 나타나도록 합니다.

이어서 =(이퀄)을 입력하고 [j2] 셀을 선택해 나타냅니다.

마지막 If_empty 인수 창에 커서를 두고 큰 따옴표를 연속으로 2번 입력해 조건에 해당하는 값이 없을 땐 빈 값이 나타나도록 입력합니다.

[확인]을 누르면 결과가 표시됩니다.

결과값이 나타난 [L2] 셀 위에 필드명을 나타내기 위해 [A1:H1] 셀 범위를 선택해 복사하고 [L1] 셀에서 붙여넣기 합니다.

[L2:S10] 셀에 나타난 결과 값에 보기좋게 서식을 적용하기 위해 [A2:H2] 셀 범위를 복사하고, [L2:S10] 셀 범위를 선택한 뒤 붙여 넣기 옵션 중 서식을 선택해 적용합니다.

열 너비도 맞추기 위해 [L2] 셀을 선택하고 마우스 오른쪽 클릭해서 [선택하여 붙여넣기]를 눌러 대화상자를 나타냅니다.

 

[열 너비]를 클릭하고 [확인]을 누릅니다.

 

UNIQUE 함수 - 중복된 항목 제거 기능

[J2] 셀에 조건으로 사용되는 지점명을 사용자가 직접 입력하지 않고, 거래내역의 지점명을 자동으로 나타내어 사용자가 선택하면 조건으로 적용할 수 있도록 데이터 유효성 검사 기능을 이용하겠습니다.

 

먼저 B열 지점명을 중복 제거해서 [J4] 셀 위치에 나타내겠습니다.

 

[J4] 셀에 커서를 두고 =un을 입력하고 나타난 함수 목록에서 UNIQUE를 더블클릭합니다.

단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.

첫번째 Array 인수 창에 커서를 두고 [B2:B57] 셀 범위를 선택해 나타나도록 합니다.

나머지 인수는 비워두고 [확인]을 눌러 결과를 나타냅니다.

중복된 항목을 제거할 범위가 한 개 열이기 때문에 By_col, Exactly_once 인수는 비워둘 수 있습니다.

 

 

SORT, SORTBY 함수 - 정렬 기능

[J4] 셀부터 지점명을 중복 제거해서 나타냈는데 그대로 데이터 유효성 검사 기능 목록으로 사용하면 지점명을 입력된 순서대로 표시하기 때문에 사용자가 찾아서 선택하기 불편합니다.

 

SORT 함수를 이용해 오름차순으로 정렬하겠습니다.

 

[J4] 셀을 선택하고 더블 클릭해 수식을 나타냅니다.

 

=(이퀄) 뒤에 커서를 두고 so를 입력하면 함수 목록에 SORT가 표시됩니다.

SORT 함수를 더블 클릭해 나타냅니다.

<Enter>를 눌러 수식을 완성합니다.

 

이제 [J2] 셀을 선택하고 [데이터]-[데이터 도구]-[데이터 유효성 검사]를 선택합니다.

[데이터 유효성] 대화상자에서 [제한 대상]은 목록, [원본]은 [J4:J20] 셀 범위를 선택해 나타냅니다.

데이터 유효성 검사 기능이 적용되어 [J2] 셀 옆에 역삼각형이 표시되고 목록을 선택해 지점명을 바꾸면 바뀐 지점명과 거래한 내역이 표시됩니다.

필요한 기능은 모두 구현했습니다.

 

 

마무리 작업

[J4:J20] 셀에 있는 지점명 목록을 보이지 않도록 서식을 설정합니다.

[J4:J20] 셀 범위를 선택하고, 보이지 않도록 하는 방법은 여러 가지 방법이 있지만 간단하게 글자색을 흰색으로 지정해 보이지 않도록 하겠습니다.

 

[J4:J20] 셀 범위를 선택해 [홈]-[글꼴]-[글자색]을 누르고 흰색을 선택합니다.

글자색이 흰색이라 겉으로 보이지 않습니다.

 

[L2:S10] 셀 범위에 나타난 FILTER 함수 결과를 일자 기준으로 정렬하겠습니다.

 

[L2] 셀을 더블 클릭해 수식을 나타내고 =(이퀄) 뒤에 커서를 두고 so를 입력해 SORT 함수를 더블클릭합니다.

<Enter>를 누르면 경고창이 표시되지만 [예]를 누르면 수식에서 잘못된 부분을 자동 수정해 결과가 올바르게 표시됩니다.

지금 선택된 중동점은 결과가 9개이지만 주례점을 선택하면 결과가 2개입니다.

그래서 적용된 테두리가 보기 싫게 남겨져 있습니다.

 

결과 개수에 맞게 셀 서식이 적용되도록 조건부 서식을 적용하겠습니다.

 

먼저 [L2:S10] 셀 범위를 선택해 [글꼴]-[테두리]-[테두리 없음]을 선택합니다.

현재 거래내역에서 지점명에 해당하는 가장 많은 결과가 중동점으로 9개 입니다.

 

그래서 현재 선택된 [L2:S10] 셀 범위에 조건부 서식을 적용합니다.

좀 더 많은 결과가 나올 수 있다면 조건부 서식을 적용할 셀 범위를 넓게 선택해 작업해야 합니다.

 

[홈]-[스타일]-[조건부 서식]-[새 규칙]을 선택합니다.

[새 서식 규칙] 대화상자에서 [규칙 유형 선택]에서 [수식을 사용하여 서식을 지정할 셀 결정]을 선택하고 [규칙 설명 편집]에서 =(이퀄)을 입력한 뒤 [L2] 셀을 클릭합니다.

=$L$2로 나타난 상태에서 <F4>키를 두 번 눌러 =$L2 혼합참조로 나타냅니다.

이어서 <>"" 를 입력해 수식을 완성합니다.

최종 수식 모양입니다.

=$L2<>""

결과 값이 나타날 첫번째 셀이 [L2] 셀을 기준으로 수식을 작성했지만 조건부 서식이 적용되면 결과가 나타날 각 행에 조건식이 적용되어 서식이 나타납니다.

 

[서식]을 누르고 [셀 서식] 대화상자에서 [테두리] 탭 오른쪽에 있는 [윤곽선]을 선택하고 [확인]을 누릅니다.

다시 [확인]을 눌러 조건부 서식을 마무리 합니다.

이제 다른 지점명을 선택해도 결과 행에만 테두리가 표시됩니다.

 

동적 범위 선택과 이름 정의

거래내역이 추가되거나 삭제될 때 제대로 동작하지 않습니다.

 

이름 정의와 OFFSET 함수를 이용해 바뀌는 데이터 범위를 자동으로 맞출 수 있도록 설정하겠습니다.

 

전에 작성한 강의 참고하세요.

 

엑셀 50강] 이름 범위를 동적 범위로 설정하여 함수식에서 활용하기

[수식]-[정의된 이름]-[이름 관리자]를 선택합니다.

이미 등록되어 있는 이름은 앞에서 고급 필터 기능을 쓸 때 자동으로 만들어진 이름인데 동작하는 부분과는 상관 없기 때문에 무시합니다.

[새로 만들기]를 누릅니다.

[새 이름] 대화상자에서 [이름]은 거래내역, [참조 대상]에 커서를 두고 직접 적어 수식을 작성합니다.

=offset(Sheet1!$A$2,0,0,counta(Sheet1!$A:$A)-1,8)

[확인]을 눌러 새 이름을 등록합니다.

 

[참조 대상]에서 수식을 작성할 때는 직접 키보드로 글을 적어야 합니다.

셀을 참조하면 절대 참조로 표시됩니다.

 

추가된 거래내역 이름이 있습니다.

조건으로 사용될 지점명 필드 범위로 이름으로 정의합니다.

[새로 만들기]를 누릅니다.

[이름]에 지점명, [참조 대상]에 수식을 직접 입력합니다.

=offset(Sheet1!$B$2,0,0,counta(Sheet1!$B:$B)-1,1)

[확인]을 눌러 새 이름을 등록합니다.

 

마지막으로 [J4] 셀부터 나타나는 중복된 항목 제거된 지점명 범위를 이름 정의합니다.

[새로만들기]를 누릅니다.

[이름]은 중복제거지점명, [참조 대상]에 수식을 작성합니다.

=offset(Sheet1!$J$4,0,0,counta(Sheet1!$J:$J)-2,1)

필요한 이름을 모두 정의했습니다.

이제 정의된 이름을 수식에 적용하겠습니다.

 

[L2] 셀에 커서를 두고 단축키 <Shift + F3>을 눌러 [함수 인수] 대화상자를 표시합니다.

FILTER 함수를 수정해야 하므로 수식입력줄에서 FILTER 글자 부분을 클릭합니다.

그러면 [함수 인수] 대화상자에 FILTER 함수 부분이 표시됩니다.

첫번째 배열 인수 부분을 거래내역으로 바꿉니다.

두번째 포함 인수 부분은 지점명=J2로 바꿉니다.

[확인]을 눌러 수식 수정을 마무리 합니다.

 

데이터 유효성 검사 기능 부분도 수정하기 위해 [J2] 셀을 선택하고, [데이터]-[데이터 도구]-[데이터 유효성 검사]를 누릅니다.

[원본] 부분을 =중복제거지점명으로 수정합니다.

[확인]을 누릅니다.

 

겉으로 보이는 모습은 전과 차이 없어 보이지만 범위 선택하는 부분을 동적 범위 선택 방법으로 처리해서 거래내역이 추가되거나 삭제되어도 제대로 동작합니다.

 

이상 강좌를 마칩니다.

수고하셨습니다.