엑셀 셀 병합 자동화! 필터+정렬 다 되는 조건부서식 완벽 해결법

실습 가이드

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.

셀 병합처럼 보이게 만드는 표시 형식 : ";;;"

보고서를 작성할 때 셀 병합은 가독성을 높이는 데 꼭 필요한 기능입니다. 그러나 셀을 병합하면 범위의 첫 번째 셀에만 데이터가 남기 때문에 필터, 정렬, 이동 등 핵심 기능이 제한되는 문제가 발생합니다. 그렇다고 셀 병합을 사용하지 않으면 보고서의 가독성이 떨어진다는 점도 부담스럽습니다.

이번 강의에서는 실제로 셀을 병합하지 않고 기존 데이터를 그대로 유지하면서 셀 병합이 적용된 것처럼 보이도록 만드는 자동화 서식을 만드는 방법을 다룹니다. 이 방식은 데이터를 보존한 상태에서 시각적인 병합 효과만 적용하므로 필터·정렬·행 숨기기 같은 작업에서도 자유롭게 사용할 수 있다는 큰 장점이 있습니다.

기존의 '셀 병합' 기능을 활용한 문제 해결 방법이 궁금하시다면 아래 이전 강의에서 단계별로 정리해두었으니 함께 참고해보세요.😊

1. 예제파일을 실행한 후 [자동보고서] 시트로 이동합니다. 먼저 GROUPBY 함수를 사용해서 구분과 브랜드별 매출을 집계하는 보고서를 만들어보겠습니다.

   

   오빠두Tip : GROUPBY 함수의 자세한 사용법은 이전 강의에서 정리했으니 참고해보세요!
   
2. H4셀에 아래와 같이 GROUPBY 함수를 입력하면 구분과 브랜드별 매출이 집계된 보고서가 출력됩니다.
   =GROUPBY(매출내역[[구분]:[브랜드]],매출내역[매출],SUM,,2,,매출내역[지점]=I2)

   

3. 아래 보고서에서 '구분' 값이 연속으로 나타나는 경우 셀 병합을 적용할 수 있습니다. 이때 각 셀을 기준으로 윗 셀과 값이 동일하면 값을 숨겨서 마치 셀이 병합된 것처럼 보이도록 서식을 적용할 수 있습니다. 아래 그림과 같이 '구분' 값이 연속된 범위에서 각 항목의 첫 셀을 제외한 나머지 범위를 선택합니다. 이때 키보드 Ctrl 키를 누른 상태로 범위를 드래그하면 인접하지 않은 여러 범위를 동시에 선택할 수 있습니다.

   

4. 선택한 범위에서 우클릭한 후 [셀 서식]으로 이동하고 [사용자 지정] 표시 형식에 ";;;" 을 입력합니다. 엑셀 셀 서식에서 세미콜론(;)은 각각 "양수;음수;0;텍스트" 영역을 구분하는 기호이므로 ";;;" 를 입력하면 모든 값이 화면에 표시되지 않도록 설정할 수 있습니다.

   

5. [확인] 버튼을 클릭하여 서식을 적용하면 아래 그림과 같이 셀 병합이 적용된 것과 같은 보고서가 완성되는 것을 확인할 수 있습니다.

   

6. 서식이 정상적으로 적용된 것을 확인했다면 Ctrl + Z 를 눌러서 이전 단계로 되돌립니다. 이번에는 조건부서식을 활용해서 ";;;" 표시 형식이 자동으로 적용되는 셀 병합 자동화 보고서를 만들어보겠습니다.

셀 병합이 된 것처럼 보이도록 하는 조건부서식

조건부서식을 사용하면 각 셀을 기준으로 윗 셀과 값이 동일할 때만 ";;;" 서식이 적용되어, 데이터가 바뀌어도 자동으로 셀 병합이 된 것처럼 보이는 동적인 보고서를 만들 수 있습니다.

1. 먼저 조건부서식을 적용할 범위를 선택합니다. 이번 예제에서는 '구분' 항목이 입력된 H4:H30 범위를 넉넉하게 선택하겠습니다.

   

2. [홈] 탭에서 [조건부서식] - [새 규칙] - [수식을 사용하여 서식을 지정할 셀 결정]을 선택해 새로운 조건부서식 규칙을 추가합니다.

   

3. 엑셀 화면 좌측 상단의 '이름 상자'에서 현재 활성화된 셀이 H4셀인 것을 확인합니다. 따라서 조건부서식의 수식은 H4셀을 기준으로 작성합니다.

   

4. 수식 입력창에 다음과 같이 수식을 입력합니다. 현재 셀을 기준으로 윗 셀과 값이 같을 때 서식이 적용되는 규칙입니다.
   =H4=H3

   

5. [서식] 버튼을 클릭한 후 [표시 형식] 탭 - [사용자 지정] 입력창에 ";;;" 을 입력합니다.

   

6. [확인] 버튼을 클릭해 조건부서식을 적용하면 셀 병합이 된 것처럼 보이는 보고서가 완성됩니다. 이제 목록상자에서 지점을 변경해도 자동으로 셀 병합 서식이 적용되는 것을 확인할 수 있습니다.

   

소계 및 합계 영역 표시 및 강조하기

이번에는 보고서의 각 항목별 마지막 값에 자동으로 '소계'를 붙이고, 소계와 합계 영역을 강조하는 조건부서식을 추가해보겠습니다.

1. 먼저 구분 항목의 각 그룹 마지막 셀이 "XXX 소계" 형식으로 표시되도록 만들어야 합니다. 이를 위해 조건부서식을 활용해 현재 셀의 값이 아래 셀과 다를 경우 "소계" 라는 단어가 자동으로 붙도록 설정합니다. 다만, 보고서의 마지막 행인 '총합계'는 아래 셀이 항상 비어 있기 때문에 이 조건만 적용하면 "총합계 소계" 처럼 잘못 표시될 수 있습니다. 따라서 조건은 "현재 셀을 기준으로 아래 셀의 값이 다르고, 동시에 아래 셀이 비어 있지 않은 경우"에만 "소계"가 붙도록 작성합니다.

   

2. 앞서와 동일하게 조건부서식을 적용할 구분 범위로 H4:H30 을 넉넉하게 선택한 후, [홈] 탭 - [조건부서식] - [새 규칙] - [수식을 사용하여 서식을 지정할 셀 결정] 메뉴로 규칙을 추가합니다. 입력창에는 아래와 같이 AND 함수를 사용한 수식을 입력합니다.
   =AND(H4<>H5,H5<>"")

   

3. [서식] 버튼을 클릭한 후 [표시 형식] - [사용자 지정] 입력창에 다음과 같이 표시 형식을 입력합니다.
   @" 소계"
   // @ 는 텍스트를 의미합니다.

   

4. [확인] 버튼을 클릭하면 아래 그림과 같이 각 그룹의 마지막 셀에 값과 함께 "소계" 가 표시되는 것을 확인할 수 있습니다.

   

5. 이제 소계와 합계 영역에 테두리를 더해서 보고서의 가독성을 높여보겠습니다. 보고서에서 '소계'와 '합계' 영역을 구분하는 조건은 "I 열의 값이 비어 있으면서, 동시에 H 열의 값이 비어 있지 않은 경우" 입니다.

   

6. 조건부서식을 적용할 범위로 H4:J30 을 넉넉하게 선택한 후, [홈] 탭 - [조건부서식] - [새 규칙] - [수식을 사용하여 서식을 지정할 셀 결정] 입력창에 아래와 같이 수식을 작성합니다.
   =AND($I4="",$H4<>"")
   // 전체 행에 서식을 적용하기 위해 알파벳 앞에 $ 기호를 추가합니다.

   

   오빠두Tip : 조건부서식으로 전체 행을 강조하는 원리에 대한 자세한 설명은 아래 기초 입문 강의에서 정리했으니 함께 참고해보세요!👇
   
7. [서식] 버튼을 클릭한 후, [글꼴] 탭에서 글꼴 스타일을 굵게 지정하고, [테두리] 탭에서 위 아래 테두리를 추가합니다.

   

8. [확인] 버튼을 클릭하면 아래 그림과 같이 소계와 합계 영역이 강조된 보고서가 완성됩니다. 이제 목록상자에서 지점을 변경해보면, 셀 병합 서식이 자동으로 적용되면서 각 그룹의 마지막 셀에는 '소계'가 자동으로 표시되고, 소계와 합계 영역도 함께 강조되는 보고서로 활용할 수 있습니다.

   

9. 이 외에도 GROUPBY 함수와 차트를 함께 활용하면 아래 그림과 같이 간단한 대시보드를 만들 수 있습니다. 대시보드를 만드는 전체 과정은 영상 강의를 참고해주세요!

   

엑셀 모든 버전에서 사용 가능한 "셀 병합 완벽 자동화 서식"

앞서 살펴본 ";;;" 서식은 대부분의 보고서에서 유용하게 활용할 수 있지만, 행을 숨기거나 필터를 적용했을 때 정상적으로 작동하지 않는 한계가 있습니다. 그래서 엑셀 모든 버전에서 안정적으로 작동하는 셀 병합 자동화 마스터 공식을 함께 준비했습니다.

공식의 사용법은 단순합니다. 공식을 메모장에 복사한 후 "기준셀", "윗셀", "$윗$셀" 세 개의 키워드를 실제 셀 주소로 변경합니다. 예를 들어 기준셀이 B4라면 "기준셀 → B4, 윗셀 → B3, $윗$셀 → $B$3" 으로 바꿔주면 됩니다. 이 공식을 사용하면 숨겨진 범위나 필터가 적용된 범위에서도 셀 병합 서식이 깨지지 않으며, 중간에 데이터가 바뀌더라도 결과가 실시간으로 자동 반영됩니다.

공식을 만드는 전체 과정은 위캔두 멤버십 라이브 강의에서 단계별로 정리했으니, 아래 영상에서 함께 확인해보세요!👇