개발 이야기

엑셀에서 한 곳만 수정해 여러 시트에 값 반영하기

공사·용역·물품을 따로 보면서도 수정은 한 번만 하도록, 통합 입력과 수식 기반 조회를 분리했어요.

사이트 공개일
기록 수정일
기술과 주제
Excel 수식 · INDEX·MATCH · 데이터 구조
이 글의 목차

종류별로 보고 싶다는 요청 뒤에 있던 문제

구매실적을 공사·용역·물품 시트로 나눠 보면 찾기 편해요. 그런데 세 시트에서 각각 값을 고치면, 계약금액을 어디서 수정했는지 헷갈리기 쉬워요. 이 파일에서는 ‘통합 실적관리’ 시트에서만 입력하고 수정하도록 했어요. 구매 종류를 공사에서 물품으로 바꾸면, 해당 행이 공사 목록에서 빠지고 물품 목록에 나타나도록 수식으로 연결했어요.

업무 흐름

  1. 01통합 실적관리에서 입력·수정
  2. 02구매 종류에 맞는 행 찾기
  3. 03공사·용역·물품에서 조회·출력
업무 흐름 개념도

계약번호로 찾으면 안 되는 이유

계약번호를 키로 삼아 조회하면 간단해 보이지만, 같은 계약에 지급 내역이 여러 행일 수 있었어요. 계약번호가 같다는 이유로 한 행만 가져오면 실적 일부가 사라져요.

그래서 조회 시트는 계약이 아닌 원본 행을 기준으로 연결했어요. 공사에 해당하는 첫 번째 원본 행, 두 번째 원본 행을 차례로 찾고, 각 행의 값을 그대로 가져와요. 중간에 빈 행이 있어도 목록에서는 이어서 보여줘요.

계약 건수를 계산할 때는 번호 중복을 제외해야 해요. 하지만 상세 목록에서는 같은 번호의 지급 내역을 남겨야 하죠. 같은 데이터라도 조회와 집계가 사용하는 단위는 달랐어요.

원본 위치를 먼저 구하고 각 열을 읽어요

수식은 세 단계로 나눴어요. 먼저 종류에 맞는 원본 행에 순번을 붙여요. 다음으로 조회 목록의 순번에 해당하는 원본 위치를 찾고, 마지막으로 그 위치의 각 열을 가져와요.

아래는 실제 공사 조회 수식에서 원본 시트명을 입력으로 줄인 예시예요. D열은 구매 종류, H열은 금액이고 입력 범위는 8~507행이에요. 조회 시트의 AO·AP열은 숨긴 보조열로 사용했어요.

excel
AO8 =IF(입력!D8="공사",COUNTIF(입력!$D$8:D8,"공사"),"")

C5  =COUNTIF(입력!$D$8:$D$507,"공사")

AP8 =IF(ROW()-7>$C$5,"",MATCH(ROW()-7,$AO$8:$AO$507,0))

H8  =IF($AP8="","",IF(ISBLANK(INDEX(입력!$H$8:$H$507,$AP8)),"",INDEX(입력!$H$8:$H$507,$AP8)))

AO열의 순번은 원본 행과 같은 위치에 있어요. AP열은 화면에 표시할 첫 번째·두 번째 항목이 원본 범위의 몇 번째에 있는지 찾아요. 각 출력 열은 AP열을 공유해 값을 읽어요. 모든 열에 종류를 찾는 긴 조건을 반복하지 않아, 연결 구조를 추적하기 쉬워졌어요.

예를 들어 원본 8행과 13행이 공사라면 AO열에는 각각 1과 2가 생겨요. 조회 목록의 첫 두 행은 원본 범위 안의 위치 1과 6을 읽어요. 원본 사이에 다른 종류나 빈 행이 있어도 공사 목록에는 빈틈이 생기지 않아요!

빈칸과 0은 같지 않아요

조회 수식을 만들 때 원본이 빈칸인데 0으로 표시되는 문제도 살폈어요. 반대로 값이 0이라는 이유만으로 빈칸으로 바꾸면, 실제로 입력한 0원과 아직 입력하지 않은 금액을 구별할 수 없어요.

수식은 ISBLANK(INDEX(...))로 원본이 비어 있는지 먼저 확인해요. 원본이 비었을 때만 빈 문자열을 반환하고, 숫자 0은 그대로 보여줘요. 조회할 원본 행 자체가 없을 때도 별도로 빈칸을 반환해요.

이 구분은 모양을 다듬는 데서 끝나지 않아요. 입력 누락을 찾을 때도 ‘비어 있음’과 ‘입력된 0’이 다른 의미를 갖기 때문이에요.

잘 나오는 화면보다 바뀐 뒤의 화면을 확인했어요

처음 목록이 나오는 것만으로 끝내지 않았어요. 실제 업무에서는 입력한 종류를 바꾸거나, 금액을 수정하거나, 잘못 넣은 행을 지우는 일이 생겨요.

바꿔 본 조건확인한 동작
원본 중간에 빈 행이 있음해당 종류의 목록은 이어서 표시
같은 계약번호를 여러 행에 입력각 지급 행과 금액을 모두 유지
공사를 물품으로 변경공사에서 빠지고 물품 목록에 반영
금액 수정 또는 원본 삭제조회 내용·건수·합계가 함께 갱신
마지막 입력행 사용마지막 행도 조회와 합계에 포함
금액 0 입력빈칸으로 바꾸지 않고 0 표시

제작 당시에는 500행의 입력 범위를 정하고 그 안에서 조회·집계를 맞췄어요. 범위를 늘릴 때는 입력 표만 늘리는 것으로 끝나지 않아요. 조회 보조열과 출력 수식, 집계 범위도 함께 확장해야 해요.

이 설계에서 지키려던 기준은 단순했어요. 사람은 한 곳에서 고치고, 필요한 목록은 여러 모양으로 볼 수 있어야 한다는 것. 엑셀에서도 입력 위치와 조회 위치를 분리해 두니, 시트를 늘리면서 수정할 곳까지 늘리는 문제를 피할 수 있었어요.

엑셀에서 관리하던 입력·조회 업무를 팀이 함께 쓰는 내부 도구로 옮기려면, 누가 원본을 수정하고 누가 결과를 조회할지부터 정리해 보세요. 업무용 웹 시스템 서비스 보기