■ 목차
1. 함수 설명
2. Filter 함수의 구문 / 사용방법
3. Filter 함수 사용해 보기
4. Filter 함수 응용 - 여러 개의 조건 필터링하기
5. Filter 함수 응용 - 날짜 필터링 하기
6. Filter 함수 응용 - Sort(Filter) 함수 - 필터링 결과를 정렬해 보자
** 핵심 요약
- Filter 함수는 최신 엑셀 프로그램(엑셀 2021, MS365)에서 사용이 가능한 함수입니다.
- 엑셀의 "동적 배열" 기능을 사용하는 최신 함수로 기존의 필터 기능을 함수로 구현하였으며
- 원본 데이터에서 Filter 함수로 원하는 데이터를 간단하게 추출하여 다른 장소에 결괏값만을 표시하는 것이 가능하게 되었습니다.
1. 함수 설명
- Filter 함수는 필터 기능이 함수로 구현된 것으로 원하는 조건의 값들만을 수식을 이용해서 추출할 수 있는 함수입니다.
- 최신 엑셀 버전 (엑셀 2021, MS365)에 새로이 추가된 함수로 함께 추가된 Unique 함수, Sort 함수와 함께 다양한 방식으로 응용이 가능해 보입니다.
- 그래서 과거 버전의 엑셀에는 없는 함수로 이전 버전의 엑셀에서는 추가해서 사용하는 것이 불가합니다.
- 또한 필터링 방식이 조건의 부울(True/False) 배열에 따라서 필터링을 하기 때문에 여러 개의 And 조건이나 혹은 Or 조건의 필터링 또한 간단하게 하는 것이 가능한데
- 이를 위해서는 배열과 엑셀에서 True/Fasle을 어떻게 취급하는지에 대한 간단한 지식이 있어야 하는 부분이 있기는 하지만
- 그렇게 어려운 개념이 아니기 때문에 다양하게 활용이 가능할 것 같습니다.
- 또한 최신 엑셀 버전에서 제공하는 "동적 배열"과 연계되어 편리하게 사용이 가능한 함수이니 많이들 사용하고 응용해 보시기 바랍니다.
2. Filter 함수의 구문 / 사용방법
- 아래 구문은 엑셀 도움말의 설명 내용입니다. 우리는 엑셀 함수의 구문을 다 외울 수도 없고 그럴 필요도 없습니다.
- 다만 엑셀 도움말과 함수사용 시 표시되는 풍선도움말의 용어들에 익숙해질 필요가 있기 때문에 기회가 될 때마다 편하게 봐주시기 바랍니다.
- FILTER(array, include, [if_empty])
- array 필수 요소입니다. 필터링할 범위 또는 배열입니다.
- include 필수 요소입니다. 조건으로 제공되는 부울 배열입니다.
- if_empty [선택 사항] 반환된 결과가 없을 때 반환할 값입니다.
- 첫 번째 인수는 array (= 배열, 셀 범위)는 필터링할 범위입니다. 보통 필터링할 원본 데이터의 범위에 해당하는 셀 주소를 입력하게 됩니다.
- 배열을 입력할 수 있다고는 하지만 배열을 직접 입력하는 방식은 거의 사용 하지 않기 때문에 무시하셔도 될 듯합니다.
- 두 번째 인수는 include로 포함될 (=필터링)될 조건을 입력합니다.
- 필터링될 조건이라고 하면 보통은 필터링할 조건에 해당하는 셀의 범위와 조건을 수식으로 입력(ex A3:A10 = "인사")하게 됩니다.
- 이때 "부울 배열"이라는 것이 만들어지게 되는데 "부울 배열"이라면 간단하게 "참/거짓"으로 만들어진 배열이라고 보시면 됩니다.
- { True ; True ; False ; False ; True }처럼 수식이 참인 경우는 True, 거짓인 경우는 False로 결괏값을 가지는 배열이라고 보시면 됩니다.
- 그리고 여기서 엑셀은 내부적으로 "참 = True = 숫자 1", "거짓 = False = 숫자 0"처럼 관리 하기 때문에
- 여러 개의 다중 조건을 만족하는 And와 Or 개념의 조건식을 만들어서 사용하는 것이 가능하게 되는데 이는 예제를 통해서 알아보도록 하겠습니다.
- 조건식을 입력할 때 특히 텍스트를 조건식으로 직접 입력하는 경우는 반드시 큰따옴표( " " )를 사용해서 텍스트임을 명시적으로 표시를 해주셔야 합니다.
- 그리고 항상 우리를 곤란하게 만드는 것이 "날짜"입니다. 필터링 대상이 되는 범위와 필터링 조건 모두 정확하게 "날짜 형식"을 잘 사용해줘야 합니다.
- 세 번째 인수는 If_empty로 만약에 조건에 해당하는 값이 없는 경우에 표시할 내용입니다.
- 보통은 텍스트로 "결과 없음" 혹은 ""로 빈 셀을 표시하고 만약 생략하는 경우에는 "#CALC!"의 오류값을 반환하게 됩니다.
3. Filter 함수 사용해 보기
- 아래 조직표에서 특정 부서를 조건으로 데이터를 필터링해 보도록 하겠습니다.
- 필터링 함수의 장점은 아래와 같이 필터링 결과를 별도로 분리해서 결괏값을 만들 수 있을 뿐만 아니라 수식으로 자동화가 가능하다는 부분입니다.
- 수식으로 필터링할 조건을 연결하여 조건의 입력값을 변경해 가면서 자동으로 결괏값 조회가 가능하기 때문입니다.
- 1) 함수 입력 : " = filter ( "
- 수식 입력을 위한 등호 ( " = " )와 함수 filter를 입력하고 인수 입력을 위해서 괄호 열기를 키보드로 입력합니다.
- 2) 첫 번째 인수 array 입력하기 : " = filter (B3:E7, "
- 첫 번째 인수는 array로 필터링하고자 하는 원본 데이터의 전체 영역을 마우스 드래그로 선택하여 셀 주소 "B3:E7"을 입력합니다.
- 그리고 인수 구분자 쉼표 ( " , " )를 키보드로 입력합니다.
- 3) 두 번째 인수 include 입력하기 : " = filter (B3:E7, C3:C7=H2, "
- 두 번째 인수는 include로 필터일 조건에 해당하는 수식입니다. 우리는 "생산"부서에 해당하는 데이터를 필터링하기 위해서
- 부서(C3:C7)의 데이터가 생산(H2)인 값을 찾기 위해서 "C3:C7 = H2"와 같이 해당하는 셀 범위를 마우스 드래그하고 등호와 조건에 해당하는 셀을 마우스로 선택하여 입력합니다.
- 그리고 세 번째 인수를 입력하기 위하여 인수 구분자 쉼표 ( " , " )를 키보드로 입력합니다.
- 4) 세 번째 인수 if_empty 입력하기 : " = filter (B3:E7, C3:C7=H2, "") "
- 세 번째 인수는 if_empty로 만약에 필터링하고자 하는 값이 없는 경우에 반환할 값입니다.
- 우리는 빈 셀을 반환하는 것으로 공백 없이 큰따옴표 ( "" )를 키보드로 입력해 줍니다.
- 그리고 괄호 닫기를 입력하고 "Enter"키를 눌러서 수식 입력을 마무리합니다.
- Filter 함수는 "동적 배열 함수"이기 때문에 배열 수식이지만 "Ctrl + Shif + Enter"의 기존의 배열수식으로 입력해 줄 필요가 없습니다.
- 그냥 "Enter"을 눌러 수식을 입력하는 것만으로도 배열 수식 입력이 가능하며 결과 또한 "필요한 크기만큼의 배열 형태"로 출력이 됩니다.
- "동적 배열 함수"에 대해서는 따로 기회가 되면 한 번 설명을 드리도록 하겠습니다.
- 오늘 그냥 기존의 배열 수식에서 처럼 "Ctrl + Shif + Enter"의 입력 필요 없이 "Enter"키로 입력할 수 있는 편리한 배열 함수라는 정도로 이해를 하시면 될 것 같습니다.
- 5) 필터링 조건 변경 : "생산 → 품질"
- 필터링 조건을 "생산"에서 "품질"로 변경을 해보도록 하겠습니다.
- 우리는 필터링 조건을 "H2"에 입력된 값을 사용하도록 수식을 입력했기 때문에
- 간단하게 H2셀의 값을 "생산 → 품질"로 다시 입력하는 것만으로 필터링 결과를 변경하는 것이 가능합니다. ^^
- 이렇게 Filter 함수는 수식관계를 이용해서 자동화하는 것이 가능하고
-다만 주의하실 점은 "동적 배열"에서 결괏값이 입력될 공간을 미리 충분히 확보해서 결괏값 표기에 오류가 생기기 않도록 해주시면 됩니다.
- 6) 기타 : 배열 결과는 한 덩어리의 데이터이다 - 필요하면 "복사 - 값 붙여 넣기"을 사용하자
- 동적 배열이나 일반적인 배열 함수의 결괏값은 모두 한 덩어리의 데이터입니다.
- 즉 개별적으로 하나의 값이나 일부의 값을 셀에서 직접 수정하는 것은 허용되지 않습니다.
- 그래서 결과 데이터를 활용하기 위해서는 일반적으로 필요한 데이터 영역만을 마우스로 선택 "복사 → 값 붙여 넣기"를 해서 사용할 수 있습니다.
4. Filter 함수 응용 - 여러 개의 조건 필터링하기
- Filter 함수를 사용함에 있어서 여러 개의 조건으로 필터링하기 위해서는 필터링 조건이 적용되는 방식을 이해할 필요가 있습니다.
- "부울(True/False) 배열에 따라서 필터링한다"라고 엑셀은 설명을 하고 있는데 좀 어렵네요. ㅠㅠ
- 부울(Boolean)이란 단 2가지 값인 참(=True = 숫자 1)과 거짓(=False = 숫자 0)만을 이용하는 방식입니다.
- Filter 함수가 입력된 수식창에서 조건에 해당하는 include 인수를 마우스로 선택하고 "F9(수식 계산 단축키)"키를 눌러주면 아래와 같이 계산된 값을 미리 볼 수 있게 됩니다.
- 여기서 부서(C3:C7)의 값이 품질(H2)인 경우는 True 아닌 경우는 False로 부울(Boolean) 결괏값이 만들어지게 됩니다.
- 그리고 참(True = 숫자 1)이 되는 조건을 만족하는 행만 결괏값으로 필터링되어 보이게 되는 원리입니다.
- 그리고 여기서 엑셀의 중요한 개념인 True = 숫자 1, False = 숫자 0이 적용이 되는데
- { FALSE ; FALSE ; FALSE ; TRUE ; TRUE } = { 0 ; 0 ; 0 ; 1 ; 1 }과 동일한 값이 된다는 의미입니다.
- 그래서 True * True = 1 * 1 = 1 = True가 되고 True * False = 1 * 0 = 0 = Fasle 가 될 수 있습니다.
- 배열 수식을 활용하기 위해서 아주 중요한 개념입니다.
- 그리고 다음으로 여러 개의 조건으로 "생산" 부서 이면서(AND) "남"인 경우는
- " (부서 = 생산) * (성별 = 남) "과 같이 AND 조건 적용을 하기 위해서 곱하기( * )를 사용하게 됩니다.
- 이 경우 2개의 조건이 아래와 같은 순서로 배열곱의 계산이 되고 AND 조건으로 적용이 되게 됩니다.
- include 인수 : ( C3:C7=H2 )*( E3:E7=H3 )
- include 인수 : { FALSE ; TRUE ; TRUE ; FALSE ; FALSE } * { TRUE ; FALSE ; TRUE ; FALSE ; FALSE }
- include 인수 : { 0 ; 1 ; 1 ; 0 ; 0 }*{ 1 ; 0 ; 1 ; 0 ; 0 }
- include 인수 : { 0 ; 0 ; 1 ; 0 ; 0 }
- 배열 계산에 익숙하지 않으신 분들은 조금 어려운 내용이 될 수도 있지만 ㅠㅠ
- 여러 개의 조건을 모두 적용하고자 한다면 곱하기로 "(조건 1) * (조건 2)"처럼 사용하시고
- 3개 이상의 조건도 모두 곱하기를 사용해 주시면 됩니다. "(조건 1) * (조건 2) * (조건 3) * …"
- 그러면 OR 조건은 어떻게 사용해야 할까요? 넵, 더하기를 해주시면 됩니다 " (조건 1) + (조건 2) "
5. Filter 함수 응용 - 날짜 필터링 하기
- 날짜 형식을 필터링하는 경우 가장 중요한 것은 원본 데이터 및 조건 데이터가 모두 "날짜 형식"에 맞게 작성되는 것입니다.
- 날짜는 근본 데이터는 숫자입니다. "1900/01/01"이 숫자 1에 해당하고 하루에 숫자 1씩 더해지는 방식으로 날짜 데이터는 엑셀에서 관리됩니다.
- 그리고 우리가 보는 날짜 형식은 그냥 보이는 형식일 뿐입니다.
- 셀에 날짜를 입력하는 가장 간단한 방식은 "2024/01/01"과 같이 입력을 하는 방식이고
- 이렇게 입력이 되면 엑셀은 자동으로 날짜가 입력되었다는 것을 인지하고 보이는 것은 "2024-01-01"과 같이 보여주고
- 내부적으로는 "1900/01/01"을 숫자 1로 해서 하루에 숫자 1씩 더해서 "45292"라는 숫자 데이터를 내부적으로 관리하게 됩니다.
- 함수로 날짜 데이터를 만드는 경우는 Date 함수를 "=Date(2023, 10, 29)"과 같이 사용해 주시면 됩니다.
- 아래 간단한 날짜기준한 필터링 예제를 참조하시기 바랍니다.
- 이 경우 필터링된 결괏값으로 날짜 데이터가 숫자 형식으로 보이는 경우 "셀 서식"에서 날짜 형식으로 변경을 해주시기 바랍니다.
- ① 날짜 필터로 사용이 가능한 방식입니다.
- =FILTER(B3:D7, B3:B7> G2, "")
- =FILTER(B3:D7, B3:B7> DATE(2023,4,1), "")
- ② 날짜 필터로 사용이 불가능한 방식입니다.
- =FILTER(B3:D7, B3:B7>2023/04/01, "") → 숫자의 나누기 계산이 적용됨
- =FILTER(B3:D7, B3:B7>"2023/04/01", "") → 큰따옴표 ( " " ) 사용으로 날짜형식이 아닌 텍스트 형식으로 적용됨
6. Filter 함수 응용 - Sort(Filter) 함수 - 필터링 결과를 정렬해 보자
- Sort 함수는 최신 엑셀버전에 추가된 동적 배열 함수로 엑셀의 정렬 기능을 함수로 구현한 것입니다.
- 그리고 Filter, Unique 함수와 함께 사용되어 오름차순 혹은 내림차순으로 정렬된 결괏값을 얻을 수 있습니다.
- 오늘은 간단하게 예제를 소개드리니 어떻게 사용을 하는지 별첨 파일을 참조하시기 바랍니다.
- 아래 예제는 성별 "여"로 필터링하고 "이름"을 기준으로 오름차순으로 정렬한 예제입니다.
- 함수 안에 함수를 사용하는 방식이고 필터링된 결괏값(배열)을 Sort 함수의 원본 데이터로 이용하는 방식입니다.
- 적용 수식 : =SORT(FILTER(B3:E7, E3:E7=H2),1)
** 핵심 요약 : Filter 함수 사용법 - 함수로 필터링하기
1. 함수 설명
- Filter 함수는 최신 엑셀 프로그램(엑셀 2021, MS365)에서 사용이 가능한 함수이다.
- 원본 데이터에서 Filter 함수로 원하는 데이터를 간단하게 추출하여 다른 장소에 결괏값만을 표시하는 것이 가능하다.
2. Filter 함수의 구문 / 사용방법
- FILTER(array, include, [if_empty])
- array 필수 요소입니다. 필터링할 범위 또는 배열입니다.
- include 필수 요소입니다. 조건으로 제공되는 부울 배열입니다.
- if_empty [선택 사항] 반환된 결과가 없을 때 반환할 값입니다.
- 첫 번째 인수 array는 필터링할 범위로 보통 필터링할 데이터 범위에 해당하는 셀 주소를 입력한다.
- 두 번째 인수는 include로 포함될 (=필터링)될 조건을 입력한다.
- 필터링할 조건에 해당하는 셀의 범위와 조건을 수식으로(ex A3:A10 = "인사") 입력하게 된다.
- 조건식을 입력할 때 특히 텍스트를 조건식으로 직접 입력하는 경우는 반드시 큰따옴표( " " )를 사용해서 텍스트임을 명시적으로 표시를 해야 하고
- 날짜를 조건으로 적용할 경우 원본 데이터 및 조건 모두 "날짜 형식"으로 적용해줘야 한다.
- 세 번째 인수는 If_empty로 만약에 조건에 해당하는 값이 없는 경우에 표시할 내용이다.
3. Filter 함수 사용해 보기
- 특정 부서를 조건으로 데이터를 필터링하는 경우 아래와 같이 한다.
- 1) 함수 입력 : " = filter ( "
- 2) 첫 번째 인수 array 입력하기 : " = filter (B3:E7, "
- 3) 두 번째 인수 include 입력하기 : " = filter (B3:E7, C3:C7=H2, "
- 4) 세 번째 인수 if_empty 입력하기 : " = filter (B3:E7, C3:C7=H2, "") "
- 5) 필터링 조건 변경 :수식관계를 이용하는 경우 간단하게 "생산 → 품질"로 조건만 수정하면 된다.
- 6) 기타 : 배열 결과는 한 덩어리의 데이터이다 - 필요하면 "복사 - 값 붙여 넣기"을 사용하자
4. Filter 함수 응용 - 여러 개의 조건 필터링하기
- Filter 함수는 조건 인수로 부울(True/False) 배열에 따라서 필터링하는데
- 부울(Boolean)이란 단 2가지 값인 참(=True = 숫자 1)과 거짓(=False = 숫자 0)만을 이용하는 방식이다.
- 이렇게 True/False와 배열곱을 활용하여 여러 개의 조건적용이 가능한데 "생산" 부서 이면서(AND) "남"을 필터링하는 경우는
- " (부서 = 생산) * (성별 = 남) "과 같이 AND 조건 적용을 하기 위해서 곱하기( * )를 사용하게 된다.
- 3개 이상의 조건도 모두 곱하기를 사용해 주시면 된다. "(조건 1) * (조건 2) * (조건 3) * …"
- OR 조건은 더하기를 해주면 된다. " (조건 1) + (조건 2) "
5. Filter 함수 응용 - 날짜 필터링 하기
- 날짜 형식을 필터링하는 경우 가장 중요한 것은 원본 데이터 및 조건 데이터가 모두 "날짜 형식"에 맞게 작성되는 것이다.
- 날짜를 입력하는 가장 간단한 방식은 "2024/01/01"과 같이 입력을 하는 방식이고
- 함수로 날짜 데이터를 만드는 경우는 Date 함수를 "=Date(2023, 10, 29)"과 같이 사용해 주면 된다.
- ① 날짜 필터로 사용이 가능한 방식
- =FILTER(B3:D7, B3:B7> G2, "")
- =FILTER(B3:D7, B3:B7> DATE(2023,4,1), "")
- ② 날짜 필터로 사용이 불가능한 방식
- =FILTER(B3:D7, B3:B7>2023/04/01, "") → 숫자의 나누기 계산이 적용됨
- =FILTER(B3:D7, B3:B7>"2023/04/01", "") → 큰따옴표 ( " " ) 사용으로 날짜형식이 아닌 텍스트 형식으로 적용됨
6. Filter 함수 응용 - Sort(Filter) 함수 - 필터링 결과를 정렬해 보자
- Sort 함수는 최신 엑셀버전에 추가된 동적 배열 함수로 엑셀의 정렬 기능을 함수로 구현한 것이다.
- 함수 안에 함수를 사용하는 방식이고 필터링된 결괏값(배열)을 Sort 함수의 원본 데이터로 이용하는 방식이다.
- 적용 수식 : =SORT(FILTER(B3:E7, E3:E7=H2),1)
[엑셀 함수 강좌-플러스] - 999. 엑셀 함수 Plus 목차
999. 엑셀 함수 Plus 목차
[엑셀 함수 강좌-플러스] - 001. (엑셀 함수 Plus) Text 함수 사용법 - 원하는 서식(포맷, 날짜/시간, 소수점, 퍼센트, 시간, 요일)을 텍스로 만들어 보자 [엑셀 함수 강좌-플러스] - 002. (엑셀 함수 Plus) Tr
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글