엑셀 시트 취합 + 통합 검색 자동화 보고서 만들기

VSTACK 함수로 여러 시트를 한 번에 취합하기

실무에서 대량의 데이터를 다루다보면, "연도별, 월별, 부서별" 로 나뉘어 있는 데이터를 매번 복사 붙여넣기로 합치는 작업이 동반되곤 합니다. 이럴 때, VSTACK 함수를 사용하면 간단하게 자동화할 수 있습니다.

1. 예제파일을 실행한 후, 결과를 받을 검색보고서 시트의 A2 셀을 선택하고 VSTACK 함수를 입력합니다.
   
2. 취합할 시트 목록의 첫번째 시트를 클릭한 후, Shift 키를 누른 채 마지막 시트를 클릭하면 시트 범위가 선택되며 다음과 같이 입력됩니다.

   =VSTACK('2023:2026'!)

   
   

   오빠두Tip : 이러한 참조 방식을 '3D 참조' 라고합니다.
3. 취합할 범위는 머리글을 제외한 A2 셀부터 새로 추가될 데이터를 고려해 G20 셀까지 넉넉하게 잡아 줍니다.
   
4. 엔터 키로 함수를 실행하면 선택한 모든 시트의 데이터가 한 번에 세로로 이어 붙은 결과가 표시됩니다. 다만 범위를 넉넉하게 잡았기 때문에 빈 행도 함께 들어오는 문제가 있습니다.
   
5. 따라서 빈 행을 걸러내기 위해 작성했던 VSTACK 수식을 FILTER 함수로 한 번 더 감싸줍니다. 이후, 필터링할 조건으로는 같은 VSTACK 범위에서 A 열(또는 다른 기준열)을 다시 참조해 사용합니다.
   
6. 조건식에는 비어 있지 않음을 의미하는 <>"" 기호를 붙여 줍니다. 등호가 마주 본 모양의 기호는 같지 않다는 뜻이고, 큰따옴표 두 개는 빈 값을 의미합니다. 조건식을 모두 작성한 후, 함수를 실행하면 실제 데이터가 있는 행만 남고 빈 행은 모두 정리된 통합 데이터가 완성됩니다.
   
7. 이제 새로운 시트가 추가되었을 때, 자동으로 취합되도록 시트 목록 양 끝에 시작 시트와 끝 시트를 더미로 추가합니다. 두 시트는 비워 두고 채우기 색을 빨간색으로 칠해 두면 다른 데이터 시트와 구분하기 쉽습니다.
   
8. 기존 VSTACK 수식에서 시작 시트를 시작, 마지막 시트를 끝으로 바꿔 주면, 두 더미 시트 사이에 어떤 시트를 끼워 넣어도 결과에 자동으로 반영됩니다.
   

FILTER와 ISNUMBER+SEARCH 공식으로 모든 필드 검색하기

일반적인 FILTER 함수는 특정 열 하나를 조건으로 검색하지만, 보고서를 만들 때는 단어가 어디에 있든 편하게 검색해야 할 때가 많습니다. 따라서, 각 행의 모든 값을 한 문장으로 합쳐 기준이 되는 필드를 만든 후, 더미 필드에서 단어를 찾는 방식으로 해결할 수 있습니다.

1. 오른쪽 빈 열에 BYROW 함수로 검색 기준 필드를 생성해보겠습니다. BYROW 함수는 선택한 범위의 각 행마다 함수를 적용한 결과를 반환하는 함수입니다.
   
2. 참조 범위로는 앞에서 작성한 VSTACK 수식의 시작 셀 뒤에 해시 기호를 붙여 동적 범위(A2#) 로 지정합니다. 이 동적범위를 활용하면, VSTACK 함수로 반환되는 결과 영역이 늘어나도 자동으로 범위가 확장됩니다.
   
3. 행별로 적용할 함수로는 CONCAT 함수를 넣어 각 행의 모든 값을 하나의 문장으로 이어 붙입니다.

   =BYROW(A2#, CONCAT)

   

4. 이제 오른쪽에 검색 결과를 표시할 셀을 선택하고 FILTER 함수를 입력하고, 검색할 범위로는 VSTACK 함수로 반환되는 전체 범위를 동적으로 선택합니다.
   
5. 조건으로는 단어 포함 검색을 처리하는 ISNUMBER+SEARCH 공식을 사용합니다. SEARCH 함수가 찾고자 하는 단어의 위치를 숫자로 반환하면, ISNUMBER 가 그 숫자 결과를 참(TRUE)으로 바꿔 조건을 만들어 줍니다.
   
   
   오빠두Tip : 단어 포함 검색 공식의 자세한 동작 원리는 이전 영상 강의를 참고하세요!
   
6. SEARCH 의 첫 번째 인수에는 검색어를 입력하는 셀을 지정하고, 두 번째 인수에는 앞서 만든 검색 기준 열을 해시 기호로 참조해 동적으로 연결합니다.
   
7. 괄호를 닫아 함수를 실행하면, 검색창에 단어를 입력할 때마다 모든 열을 대상으로 해당 단어가 포함된 행만 남는 보고서가 만들어집니다.
   

조건부 서식으로 검색어가 포함된 셀 실시간 강조하기

마지막으로 검색 결과 안에서 입력한 단어가 들어 있는 셀만 색으로 강조합니다. 조건부 서식의 텍스트 포함 규칙을 그대로 활용하되, 검색창이 비어 있을 때는 서식이 동작하지 않도록 중지 규칙을 함께 걸어 주는 것이 핵심입니다.

1. 검색 결과가 표시되는 범위를 새로 추가될 데이터까지 고려해 넉넉하게 선택한 뒤, 홈 탭의 조건부 서식에서 셀 강조 규칙 메뉴의 텍스트 포함 항목을 선택합니다.
   
2. 비교할 텍스트로는 검색어를 입력하는 검색창 셀을 지정하고, 강조 서식은 사용자 서식으로 들어가 채우기 색은 옅은 초록, 글꼴 색은 진한 초록의 굵은 글씨로 지정합니다.
   
3. 확인 버튼을 누르면 검색어와 일치하는 셀만 초록색으로 강조되는 것을 확인할 수 있습니다. 단, 텍스트포함 조건 하나만 적용하면, 검색창이 비어있을 때 모든 셀이 강조되는 문제가 생깁니다.
   
4. 따라서, 검색창이 비어있을 경우 조건부서식을 강제로 중단아는 '스탑 규칙'을 추가합니다. 같은 범위를 다시 선택하고 조건부 서식의 새 규칙을 클릭한 뒤, 마지막 항목인 수식을 사용하여 서식을 지정할 셀 결정을 선택합니다.
   
5. 수식 칸에는 검색창 셀이 비어 있는 경우를 판별하는 식을 입력합니다. 서식은 지정하지 않고 확인 버튼만 눌러 빈 규칙을 만들어 둡니다. 이 규칙은 실제 서식을 적용하기 위한 것이 아니라 다음 규칙을 멈추는 용도로만 사용됩니다.
   
6. 이제 조건부 서식을 적용한 범위를 선택한 상태에서, 조건부 서식의 규칙 관리로 이동합니다. 두 규칙의 적용 대상이 같은 범위인지 확인하고, 일치하지 않으면 동일한 범위로 통일합니다.
   
7. 방금 추가한 빈 규칙을 가장 위로 올린 뒤 True 일 경우 중지 옵션을 체크합니다. 조건부 서식은 위에서 아래 순서로 적용되므로, 검색창이 비어 있을 때는 이 규칙에서 멈춰 아래의 텍스트 포함 규칙이 작동하지 않습니다.
   
8. 적용 버튼을 누르고 닫기로 마무리하면, 검색창에 단어를 입력했을 때만 해당 단어가 포함된 셀이 실시간으로 초록색으로 강조되는 자동화 보고서가 완성됩니다.