엑셀 셀 병합 자동화! 필터+정렬 다 되는 조건부서식 완벽 해결법
셀 병합 자동화 서식 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 조건부 서식으로 셀 병합 자동화 보고서 만들기예제파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
셀 병합처럼 보이도록 하는 서식 : ";;;"
엑셀에서 셀 병합은 보고서를 작성할 때 꼭 필요하고 중요한 기능입니다. 하지만, 셀을 병합하면 범위의 첫번째 셀에만 데이터가 남게 되어, 필터 범위, 이동, 정렬 등의 기능이 제한되는 문제가 있습니다. 그렇다고 셀 병합을 쓰지 않으면, 가독성이 떨어지는 문제가 있죠.
그래서 오늘은 실제로 셀을 병합하지 않아도 기존 데이터는 유지한 채로 병합이 된 것처럼 보이게 하는 자동화 서식을 만드는 방법을 알아볼 예정입니다. 오늘 소개하는 조건부 서식을 사용하면 기존 데이터는 그대로 유지하면서 셀 병합이 된 것과 같은 서식을 만들 수 있어 '필터나 정렬, 행 숨기기' 등에서 유연하게 대응할 수 있는 장점이 있습니다.
기존의 '셀 병합' 기능을 사용한 문제 해결 방법이 궁금하실 경우, 아래 이전 강의에서 꼼꼼히 정리했으니 참고해보시길 바랍니다.😊
- 예제파일을 실행하고 [자동보고서] 시트로 이동합니다. 먼저, GROUPBY 함수를 사용해서 구분과 브랜드별 매출을 집계하는 보고서를 만들어 보겠습니다.
구분과 브랜드별 매출을 집계하는 보고서를 만듭니다. 오빠두Tip : GROUPBY 함수의 자세한 사용법은 이전 강의를 참고하세요!
- H4셀에 아래와 같이 GROUPBY 함수를 작성하면 구분과 브랜드별 매출을 집계한 보고서가 출력됩니다.
=GROUPBY(매출내역[[구분]:[브랜드]],매출내역[매출],SUM,,2,,매출내역[지점]=I2)
GROUPBY 함수로 집계 보고서를 생성합니다. - 아래 보고서에서 '구분' 값이 연속으로 나타나는 경우, 셀 병합을 적용할 수 있습니다. 그럴 경우, 각 셀을 기준으로 바로 윗 셀과 값이 동일할 때 값을 숨겨주면, 마치 셀이 병합된 것처럼 보이도록 서식을 적용할 수 있습니다. 아래 그림과 같이 '구분' 값이 연속된 범위에서, 각 항목의 첫 셀을 제외한 나머지 범위를 선택합니다. 키보드 Ctrl 키를 누른 상태로 범위를 드래그하면 인접하지 않은 여러 범위를 동시에 선택할 수 있습니다.
각 셀을 기준으로 윗 셀과 값이 값을 경우 셀 병합 서식을 적용합니다. - 선택한 범위를 우클릭한 후 [셀 서식]으로 이동하고, [사용자 지정] 에서 표시 형식으로 ";;;" 를 입력합니다. 엑셀 셀 서식에서 세미콜론(;)은 각각 "양수;음수;0;텍스트" 를 구분하는 기호입니다. 따라서 ";;;" 를 입력하면 모든 값이 화면에 표시되지 않도록 설정할 수 있습니다.
셀 서식의 표시 형식으로 ";;;"를 입력합니다. - [확인] 버튼을 클릭해서 서식을 적용하면 아래 그림과 같이 셀 병합이 적용된 것과 같은 보고서가 완성된 것을 확인할 수 있습니다.
셀 안의 값은 유지한 채, 셀 병합이 적용 된 듯한 보고서가 완성됩니다. - 서식이 잘 적용된 것을 확인하였으면 Ctrl + Z 를 동시에 눌러서 이전 단계로 돌아옵니다. 이제 조건부 서식을 사용해서 ";;;" 서식이 자동으로 적용되는 셀 병합 자동화 보고서를 만들어보겠습니다.
셀 병합이 된 것처럼 보이도록 하는 조건부 서식
조건부서식을 사용하면 각 셀을 기준으로 윗 셀과 값이 동일할 경우 ";;;" 서식을 적용해서, 자동으로 셀 병합이 된 것 처럼 보이도록 하는 서식을 만들 수 있습니다.
- 먼저 조건부서식을 적용할 범위를 선택합니다. 이번 예제에서는 '구분'이 입력된 H4:30까지 범위를 넉넉하게 선택하겠습니다.
조건부서식을 적용할 범위를 넉넉히 선택합니다. - [홈] 탭 - [조건부서식] - [새 규칙] 에서 [수식을 사용하여 서식을 지정할 셀 결정]을 선택해서 새로운 조건부 서식 규칙을 추가합니다.
조건부서식 - 새 규칙 - 수식을 사용하여 서식을 지정할 셀 결정으로 새 규칙을 추가합니다. - 엑셀 화면 좌측 상단의 '이름 상자'를 보면, 현재 활성화된 셀이 H4셀인 것을 확인할 수 있습니다. 따라서 조건부서식의 수식은 H4셀을 기준으로 작성합니다.
이름 상자에서 활성화 된 셀 주소를 확인합니다. - 수식 입력창에 다음과 같이 수식을 입력합니다. (현재 셀 기준으로 윗 셀과 값이 같을 경우 서식을 적용)
=H4=H3
현재 셀이 윗 셀과 동일할 경우를 조건으로 입력합니다. - [서식] 버튼을 클릭하고 [표시 형식] 탭 - [사용자 지정] 에서 입력창에 ";;;" 을 입력합니다.
";;;" 서식을 적용합니다. - [확인] 버튼을 클릭해서 조건부서식을 적용하면, 셀 병합이 된 것처럼 보이는 보고서가 완성됩니다. 이제 목록상자를 클릭해서 지점을 변경해도 자동으로 셀 병합 서식이 적용되는 것을 확인할 수 있습니다.
셀 병합이 자동으로 적용되는 보고서가 완성됩니다.
소계 및 합계 영역 표시 및 강조하기
이제 보고서의 각 항목별 마지막 값에 자동으로 ‘소계’를 붙이고, 소계와 합계 항목을 자동으로 강조하는 조건부서식을 적용해보겠습니다.
- 먼저 구분 항목의 각 그룹별 마지막 셀 "XXX 소계" 형식으로 표시되어야 합니다. 이를 위해 조건부서식을 활용하여 현재 셀의 값이 아래 셀과 다를 경우, 해당 값 뒤에 "소계" 라는 단어가 추가되도록 설정합니다. 단, 보고서의 마지막 행인 '총합계'는 아래 셀이 항상 비어 있으므로 이 조건을 그대로 적용하면 "총합계 소계"로 잘못 표시될 수 있습니다. 따라서, 조건은 "현재 셀을 기준으로 아래 셀의 값이 다르고, 동시에 아래 셀이 비어있지 않은 경우"에만 "소계"가 붙도록 설정합니다.
각 그룹별 마지막 셀에는 "소계"를 표시합니다. - 이전과 동일하게 조건부 서식을 적용할 구분 범위로 H4:H30 범위를 넉넉하게 선택한 후, [홈] 탭 - [조건부 서식] - [새 규칙] - [수식을 사용하여 서식을 지정할 셀 결정]으로 규칙을 추가합니다. 입력창에는 아래와 같이 AND 함수로 수식을 입력합니다.
=AND(H4<>H5,H5<>"")
현재 셀이 아래 셀과 다를 경우 서식을 적용하도록 조건을 작성합니다. - [서식] 버튼을 클릭하고 [표시 형식] - [사용자 지정]에서 형식으로 아래와 같이 입력합니다.
@" 소계"
// @ 는 텍스트를 의미합니다.
텍스트 뒤에 소계를 넣는 서식을 적용합니다. - [확인] 버튼을 클릭하면 아래 그림과 같이 각 그룹별 마지막 셀은 값 뒤에 "소계"가 표시됩니다.
각 그룹별 마지막 항목 값에 '소계'가 표시됩니다. - 이제 소계와 합계 영역에 테두리를 추가해서 보고서 가독성을 높여보겠습니다. 아래 보고에서 '소계'와 '합계' 영역을 구분하는 조건은 "I 열의 값이 비어있으면서, 동시에 H 열의 값이 비어있지 않은 경우" 입니다.
I 열의 값이 비어있고, H열에 값이 있을 경우를 소계/합계로 강조합니다. - 조건부서식을 적용할 범위로 H4:J30 까지 범위를 넉넉하게 선택한 후, [홈] 탭 - [조건부서식] - [새 규칙] - [수식을 사용하여 서식을 지정할 셀 결정] 에서 입력창에 아래와 같이 수식을 작성합니다.
=AND($I4="",$H4<>"")
// 전체 행에 서식을 적용하기 위해 알파벳 앞에 $ 기호를 추가합니다.
조건부서식으로 새 규칙을 추가합니다. 오빠두Tip : 조건부서식으로 전체 행을 강조하는 원리에 대한 자세한 설명은 아래 기초 입문 강의에서 꼼꼼히 정리했으니 참고하세요!👇
- [서식] 버튼을 클릭한 후, [글꼴] 탭에서 글꼴 스타일은 굵게, [테두리] 탭에서 위 아래 테두리를 추가합니다.
글꼴은 굵게 변경하고, 위 아래 테두리를 추가합니다. - [확인] 버튼을 클릭하면 아래 그림과 같이 소계와 합계 영역이 강조된 보고서가 완성됩니다. 이제 목록상자에 지점을 변경해보세요. 셀 병합이 자동으로 적용되면서, 각 그룹별 마지막 셀에는 '소계'가 자동으로 표시되고, 소계와 합계는 자동으로 강조되는 보고서로 사용할 수 있습니다.
셀 병합, 소계 표시, 자동 강조가 모두 적용된 보고서가 완성됩니다. - 이 외에도 GROUPBY 함수와 차트를 활용하면, 아래 그림과 같이 간단한 대시보드를 만들 수 있습니다. 대시보드를 만드는 전체 과정은 영상 강의를 참고하세요!
셀 병합 서식으로 차트와 보고서를 동시에 시각화할 수 있습니다.
엑셀 모든 버전에서 가능한 "셀 병합 완벽 자동화 서식"
앞서 살펴본 ";;;" 서식은 대부분의 경우 유용하게 사용할 수 있지만, 행을 숨기거나 필터를 적용했을 때 제대로 작동하지 않는 한계가 있습니다. 그래서, 엑셀 모든 버전에서 100% 완벽하게 작동하는 셀 병합 자동화 마스터 공식을 준비했습니다.
=OR(AND(OFFSET(기준셀,-1,0)=기준셀,SUBTOTAL(103,OFFSET(기준셀,-1,0))>0),LOOKUP(2,1/(SUBTOTAL(103,OFFSET(OFFSET(INDIRECT("기준셀",TRUE),-1,0),ROW($윗$셀:윗셀)-ROW(OFFSET(INDIRECT("기준셀",TRUE),-1,0)),0))),$윗$셀:윗셀)=기준셀)공식의 사용법은 간단합니다. 공식을 메모장에 복사한 후, "기준셀", "윗셀", "$윗$셀"을 찾아 하나씩 변경합니다. 예를 들어, 기준셀이 B4일 경우 "기준셀 → B4, 윗셀 → B3, $윗$셀 → $B$3"으로 변경합니다. 이 공식을 사용하면 숨겨진 범위나 필터가 적용된 범위에서도 셀 병합 서식이 깨지는 문제를 해결할 수 있습니다.심지어 중간에 데이터가 변경되어도 실시간으로 업데이트 되는 놀라운 경험을 하게 되실 겁니다.
공식을 만드는 전체 과정을 위캔두 멤버십 라이브 강의에서 단계별로 꼼꼼히 정리했으니, 아래 영상을 참고해주세요!👇
- 예제파일을 실행하고 [자동보고서] 시트로 이동합니다. 먼저, GROUPBY 함수를 사용해서 구분과 브랜드별 매출을 집계하는 보고서를 만들어 보겠습니다.
