색 기준 필터, 고급 필터, sum+if 배열 함수

필터에 텍스트 필터, 숫자, 날짜 등 필터가 있지만 이외에도 색 기준 필터가 있어 유용하게 사용할 수 있습니다.
https://lsw3210.tistory.com/entry/%EC%97%91%EC%85%80-%EB%B0%B0%EC%9A%B0%EA%B8%B06-%ED%95%84%ED%84%B0-%ED%95%84%ED%84%B0-%EC%A7%80%EC%9A%B0%EA%B8%B0-%EB%8B%A4%EC%8B%9C-%EC%A0%81%EC%9A%A9

엑셀 배우기(6) - 필터, 필터 지우기, 다시 적용

 
아래 데이터를 기준으로 설명하겠습니다.

지목별_토지현황(국가통계포털,행안부).xlsx

0.03MB

 
 
 
텍스트 필터 등에 사용할 수 있는 사용자 지정 필터는

 
2가지 조건만을 지정할 수 있는 한계점이 존재합니다.

 
그런데 이런 한계를 극복할 수 있는 간단한 방법이 번거롭지만 색 기준 필터입니다.
 

1. 예제 1

토지 지목중 Ctrl 키를 누른 상태에서 대지, 공장용지, 창고용지 3개를 누른 다음 채우기 색 중 노란색을 클릭합니다.

 
그러면 아래와 같이 3개 셀이 노란색으로 채워집니다.

 
이제 토지 지목별 옆의 필터 버튼을 누르면
위 2번째 캡쳐 화면과 달리 '색 기준 필터' 메뉴가 활성화 되어 있습니다.

 
색 기준 필터의 오른쪽 꺽기를 누르면 '셀 색 기준 필터'로 2가지 색이 표시됩니다. 여기서 노란색을 클릭하

 
노란색으로 칠한 지목만 선택됩니다.

 
이제 합계 등을 구하면 됩니다.
홈 탭 > 편집 그룹 > 자동 합계를 클릭하면 Sum이 아니라 SubTotal 함수가 적용됩니다.

 
천 단위 구분 기호 ,가 없으므로 홈 탭 > 표시 형식 그룹 > 쉼표 스타일을 누르면 위에 있는 숫자들과 숫자가 표시되는 위치가 다릅니다.

 
이 때는 복사하려고 하는 서식이 있는 셀, 여기서는 B15셀을 클릭한 다음 홈 탭 > 클립보드 그룹 > 서식 복사 명령을 누른 다음

 
적용할 셀인 B31셀을 클릭하면 됩니다.

 

2. 예제 2

홈 탭 > 편집 그룹 > 정렬 및 필터 > 지우기 명령을 눌러 적용된 필터를 지웁니다.

 
이번에는 하천, 제방, 구거, 유지에 초록색을 칠해보겠습니다.

 
그리고, 색 기준 필터 오른쪽 꺽기를 누르면 '셀 색 기준 필터'가 3개 표시되는데, 하나만 선택가능하고, 2개 선택이 불가능합니다.

 
초록색을 선택하면 결과는 아래와 같습니다.

 

3. 예제 3

만약 노란색과 초록색을 포함하는 데이터를 추출한다고 하면 오른쪽 숫자 데이터에서 색을 칠하면 됩니다.

 
그리고, B열에서 필터 버튼을 누른 후 색 기준 필터를 누르고, 주황색을 선택하면 됩니다.

 

4. 한계 및 해결책

보면서 색칠을 하기때문에 실수할 수도 있고, 데이터가 많다면 매우 비효율적이 될 것입니다.
따라서 이 때는 고급 필터 또는 Sum과 If 함수를 결합한 배열 함수를 이용하는 방법을 생각할 수 있습니다.
 

가. 고급 필터

 
고급 필터는 데이터 탭 > 정렬 및 필터 그룹에 있습니다.

 
미리 조건을 지정하기 위해 D열에 토지 지목별(1)을 복사해 붙이고, 아래에 해당하는 지목을 복사해서 붙여넣고,
 
조건에 맞는 데이터중 표시될 필드명을 F1, G2셀에 기재해야 합니다.
 
그리고, 결과가 표시될 위치, 목록 범위, 조건 범위, 복사 위치를 지정하고 확인 버튼을 누르면

 
아래와 같이 데이터가 추출되는데 합계를 구하기 위해 홈 탭 > 편집 그룹 > 자동 합계를 누르면

 
이번에는 SubTotal이 아니라 Sum함수를 이용해 합계가 구해집니다.

 

나. Sum과 If 함수를 결합한 배열 함수

Sum과 If 함수를 결합한 배열 함수는 이전에는 수식 작성 후 Ctrl + Shift + Enter키를 눌러서 입력했는데, Microsoft 365에서는 Enter키만 눌러도 됩니다. 따라서 아래 수식을 보면 수식 왼쪽과 오른쪽에 중괄호({  }) 표시가 없습니다.,

 
수식은 
=SUM(IF(($A$3:$A$30="대지")+($A$3:$A$30="공장용지")+($A$3:$A$30="창고용지")+($A$3:$A$30="하천")+($A$3:$A$30="제방")+($A$3:$A$30="구거")+($A$3:$A$30="유지"),($B$3:$B$30)))
 
으로 Or 조건이므로 ($A$3:$A$30="대지") 등 지목에 해당하는 조건을 +로 연결했고(And 조건은 * 사용), 조건을 만족하는 경우 ($B$3:$B$30)에서 해당하는 지목의 수치만 Sum을 합니다.

 

그러나, =SUM(IF(OR((A3:A30="대지"),(A3:A30="공장용지")),(B3:B30)))라고, +대신 or 함수를 사용하면 조건이 하나라도 충족되면 True이기 때문에 전체 계가 구해지므로 안됩니다.

지목별_토지현황(국가통계포털,행안부)(완성).xlsx

0.03MB