엑셀 원장 자동화 수식 총정리: 총원장 한 번 입력으로 거래처별 원장·미수금까지

엑셀 원장 자동화는 같은 거래를 두 번 적지 않게 만드는 것입니다. 날짜순으로 모든 거래를 적는 총원장 한 곳에만 입력하고 거래처별 원장과 미수금 현황은 수식이 총원장에서 끌어와 채우게 하면 됩니다. 이 글에는 마트에 전단·POP·현수막을 납품하는 고객사의 거래장부를 자동화할 때 실제로 쓴 수식을 그대로 옮겨 두었습니다. 잔액 누계, 입금자 이름으로 거래처 찾기, 조건에 맞는 행만 다른 시트로 가져오기, 거래처별 합계 집계, 검증 칸까지 복사해서 바로 쓰실 수 있습니다.

왜 같은 거래를 두 번 적게 될까요

자동화 전 거래처 시트. 총원장과 같은 거래를 한 번 더 적던 모습(예시 데이터로 재현)

납품 업체의 엑셀 장부는 대개 날짜순으로 모든 납품과 입금을 적는 총원장과, 거래처마다 하나씩 만든 거래처 시트로 이뤄집니다. 둘 다 필요하니 같은 거래를 총원장에 한 번, 거래처 시트에 또 한 번 적게 됩니다. 그러다 한쪽이 빠지거나 두 번 들어가면 월말에 두 합계의 차액을 손으로 찾아야 하고 통장 입금내역을 붙여넣다 수식이 지워지기도 합니다.

아래 방법은 입력을 총원장 하나로 줄이고 나머지는 모두 수식에 맡기는 방식입니다.

총원장 열 구성

아래 수식은 모두 이 열 구성을 기준으로 합니다. 사람이 적는 칸은 흰색, 수식 칸은 회색으로 칠해 두고 "흰 칸에만 적는다"는 규칙을 정해 두면 수식이 덮이는 사고가 줄어듭니다. 데이터는 5행부터 시작하고 4행에는 지난해에서 넘어온 잔액(전년이월)을 둡니다.

엑셀 합계 수식: 빈칸이면 비워 두기

합계 칸(G5)은 납품가와 잡물을 더하되, 둘 다 비어 있으면 0 대신 빈칸으로 둡니다. 입금만 있는 줄에 0이 찍히지 않아 장부가 깔끔해집니다.

=IF(COUNT(E5:F5)=0,"",E5+F5)

엑셀 잔액 누계 수식: 빈 줄이 있어도 깨지지 않게

자동화한 총원장. 회색 칸(합계·잔액·거래처)은 수식이 채웁니다(예시 데이터)

받을 돈 누계는 보통 "바로 윗줄 잔액 + 이번 줄 합계 − 이번 줄 입금"으로 계산합니다. 그런데 윗줄이 비어 있거나 월 합계 같은 줄이 끼면 계산이 끊깁니다. 그래서 바로 윗줄 대신 "위쪽에서 마지막으로 나온 숫자"를 찾아 이어 가게 했습니다. 잔액 칸(I5)에 이렇게 쓰고 아래로 채웁니다.

=IF(B5="","",LOOKUP(9E+307,I$4:I4)+N(G5)-N(H5))

LOOKUP(9E+307, 범위)는 범위 안에서 마지막 숫자를 돌려주는 오래된 요령입니다. 9E+307은 엑셀이 다룰 수 있는 가장 큰 수에 가까워서 이보다 큰 값을 찾다 못 찾고 마지막 숫자에서 멈춥니다.

N()은 빈칸이나 글자를 0으로 바꿔 줘서 입금만 있는 줄, 납품만 있는 줄 모두 오류 없이 계산됩니다. 적요(B)가 비어 있는 줄은 빈칸으로 둡니다.

입금자 이름으로 거래처 자동 입력하기 (INDEX·MATCH)

통장에 찍히는 이름은 거래처 이름과 다를 때가 많습니다. 대표자 개인 이름으로 들어오기도 하고 "(주)"가 붙거나 잘리기도 합니다. 그래서 '거래처매핑' 시트를 따로 만들어 A열에는 통장에 찍히는 이름이나 적요, B열에는 그 거래처를 적어 둡니다. 거래처 이름 자체도 자기 자신으로 한 줄씩 넣어 두면 납품 줄도 같은 수식으로 처리됩니다. 총원장의 거래처 칸(K5)은 이렇게 씁니다.

=IF(B5="","",IF(L5<>"",L5,IFERROR(IF(INDEX(거래처매핑!$B$3:$B$300,MATCH(TRIM(B5),거래처매핑!$A$3:$A$300,0))="","미등록",INDEX(거래처매핑!$B$3:$B$300,MATCH(TRIM(B5),거래처매핑!$A$3:$A$300,0))),"미등록")))

• TRIM: 통장 내역을 붙여넣을 때 딸려 오는 앞뒤 공백을 지워 이름이 정확히 맞게 합니다.
• L5: 같은 사람이 여러 거래처 대금을 보내는 식으로 자동 판정이 틀리는 줄은 L열에 거래처를 직접 적으면 그게 우선합니다.
• "미등록": 매핑 표에 없는 이름이면 "미등록"이 뜹니다. 매핑 표에 한 줄 더하면 그다음부터는 저절로 맞춰집니다.

엑셀 텍스트 날짜를 날짜로 바꾸기 (통장 내역 붙여넣기)

은행에서 복사한 입금내역은 "2026.07.31 14:06:23"처럼 점과 시각이 붙은 글자로 들어옵니다. 엑셀은 이걸 날짜로 알아보지 못해 정렬이 어긋납니다. 정렬 키 칸(M5)에서 날짜와 시각을 숫자로 바꿔 둡니다.

=IF(B5="","",IFERROR(IF(ISNUMBER(A5),A5,DATEVALUE(SUBSTITUTE(LEFT(TRIM(A5),10),".","-"))+IFERROR(TIMEVALUE(MID(TRIM(A5),12,8)),0)),99999)+ROW()/10000000)

• 이미 날짜로 들어온 칸은 그대로 쓰고 글자면 앞 10자리의 점을 하이픈으로 바꿔 DATEVALUE로 날짜를, 12번째 글자부터 8자리를 TIMEVALUE로 시각을 만듭니다.
• 끝의 ROW()/10000000은 같은 날짜·시각인 줄이 있어도 정렬 키가 겹치지 않게 아주 작은 값을 더하는 것입니다. 다음 단계의 MATCH가 정확히 한 줄을 찾으려면 키가 겹치면 안 됩니다.
• 날짜를 읽을 수 없는 줄은 99999(아주 먼 미래)가 되어 맨 뒤로 갑니다.

조건에 맞는 행만 다른 시트로 가져오기 (AGGREGATE·MATCH·INDEX)

거래처 시트는 직접 적지 않습니다. 총원장에서 그 거래처 줄만 날짜순으로 채워집니다(예시 데이터)

이제 거래처 시트에는 아무것도 적지 않습니다. 총원장에서 그 거래처의 줄만 날짜순으로 골라 오는 수식을 둡니다. 필터 함수가 없는 엑셀 2010 이후 버전에서도, 배열 수식(Ctrl+Shift+Enter) 없이 동작하는 방법입니다.

① 도우미 열(Z)에 총원장 행 번호 찾기. 거래처 시트의 Z1에 거래처 이름을 적고 Z5에 아래 수식을 넣어 100줄 정도 아래로 채웁니다. 다 되면 Z열은 숨깁니다.

=IFERROR(MATCH(AGGREGATE(15,6,총원장!$M$5:$M$2000/(총원장!$K$5:$K$2000=$Z$1),ROWS($Z$5:Z5)),총원장!$M$5:$M$2000,0)+4,"")

• 총원장!$M$5:$M$2000/(총원장!$K$5:$K$2000=$Z$1): 거래처가 맞는 줄은 정렬 키가 그대로 남고 안 맞는 줄은 0으로 나눈 오류가 됩니다.
• AGGREGATE(15,6,…,k): 15는 k번째로 작은 값(SMALL), 6은 오류를 무시하라는 뜻입니다. 그래서 그 거래처 줄의 정렬 키가 날짜 순서대로 하나씩 나옵니다. 총원장이 날짜순이 아니어도 거래처 시트는 날짜순이 됩니다.
• ROWS($Z$5:Z5): 아래로 채울수록 1, 2, 3…이 되어 k 역할을 합니다.
• MATCH(…)+4: 찾은 정렬 키가 총원장 범위의 몇 번째인지 구하고 범위가 5행부터라 4를 더해 실제 행 번호로 만듭니다.

② 그 행의 값 가져오기. A열(날짜)·B열(적요)처럼 글자가 들어갈 수 있는 칸은 이렇게, 열마다 총원장!B:B 부분만 그 열로 바꿔 씁니다.

=IF($Z5="","",IF(INDEX(총원장!B:B,$Z5)="","",INDEX(총원장!B:B,$Z5)))

부수·납품가·잡물·합계·입금처럼 숫자 칸은 0이 찍히지 않게 이렇게 씁니다.

=IF($Z5="","",IF(N(INDEX(총원장!E:E,$Z5))=0,"",INDEX(총원장!E:E,$Z5)))

③ 거래처별 잔액 누계. 거래처 시트의 3행 I3에 전년이월을 두고 첫 줄(I5)은 전년이월에서, 그 아래는 바로 윗줄에서 이어 갑니다.

=IF($Z5="","",N(I3)+N(G5)-N(H5))
=IF($Z6="","",N(I5)+N(G6)-N(H6))

④ 합계와 현재 잔액은 표 위(4행)에 고정. 줄이 늘어도 위치가 변하지 않아 한눈에 보입니다. 몇 건이 자동으로 들어왔는지도 함께 보여 줍니다.

D4: =SUM(D5:D104)        (E4~H4도 같은 모양)
I4: =N(I3)+G4-H4
J4: =COUNTIF(Z5:Z104,">0")&"건 (총원장 자동 반영)"

새 거래처가 생기면 이 시트를 복사해 시트 이름과 Z1의 거래처 이름만 바꾸면 됩니다.

거래처별 합계·미수금 현황 자동 집계 (SUMIF·COUNTIF·INDIRECT)

현황판 시트에는 거래처마다 한 줄씩, A열에 거래처 이름(거래처 시트 이름과 똑같이)을 적고 나머지는 수식으로 채웁니다. 5행 기준입니다.

전년이월  B5: =IFERROR(N(INDIRECT("'"&A5&"'!I3")),0)
건수      C5: =COUNTIF(총원장!$K$5:$K$2000,A5)
합계      G5: =SUMIF(총원장!$K$5:$K$2000,A5,총원장!$G$5:$G$2000)   (부수·납품가·잡물·입금도 같은 모양)
미수금    I5: =B5+G5-H5
시트 잔액 J5: =INDIRECT("'"&A5&"'!I4")
차액      K5: =I5-J5
상태      L5: =IF(ROUND(K5,0)<>0,"⚠ 차액 확인","정상")

INDIRECT는 A열의 이름으로 그 거래처 시트를 찾아 칸을 읽어 옵니다.

검증 칸: 차액이 0이면 정상

현황판과 검증 칸. 차액과 미등록 건수가 0이면 정상입니다(예시 데이터)

자동화한 장부에는 "제대로 돌고 있나"를 알려 주는 칸이 꼭 있어야 합니다. 현황판 아래에 세 줄을 둡니다.

총원장 전체 건수: =COUNTIF(총원장!$B$5:$B$2000,"<>")
총원장 전체 합계: =SUMIF(총원장!$B$5:$B$2000,"<>",총원장!$G$5:$G$2000)
미등록 건수:     =COUNTIF(총원장!$K$5:$K$2000,"미등록")
차액:            (총원장 전체) − (현황판 거래처 합계)

미등록 건수와 차액이 모두 0이면 "정상"이 뜨게 해 두면, 엑셀을 잘 모르는 사람도 장부 상태를 바로 알 수 있습니다. 0이 아니면 매핑 표에 없는 이름이 있거나, 어떤 줄이 어느 거래처에도 잡히지 않은 것입니다.

자동화하면 숨어 있던 불일치가 드러납니다

기존 장부를 새 총원장에 옮겨 담으면서 원본의 총원장과 거래처 시트를 대조하게 되는데, 저희가 맡은 장부에서도 둘이 맞지 않는 곳이 나왔습니다. 빠진 줄, 두 번 적힌 줄, 날짜 없이 적힌 입금 같은 것들입니다. 이런 곳은 옮기는 사람이 고치지 말고 목록으로 정리해 장부 주인에게 확인받는 게 좋습니다. 어느 쪽이 맞는지는 주인이 가장 잘 압니다.

엑셀만으로 남는 문제, 그리고 프로그램으로 옮긴 이유

여기까지 하면 엑셀만으로도 이중 입력은 사라집니다. 하지만 이 방식은 수식이 지켜질 때만 동작합니다. 엑셀에 익숙하지 않은 담당자가 행을 통째로 붙여넣으면 수식 칸까지 덮어써지고 거래처 칸에 이름을 직접 써 넣으면 매핑 수식이 사라집니다.

그래서 저희는 다음 단계로 수식이 아예 없는 Windows 프로그램을 만들었습니다. 이런 신호가 보이면 엑셀 자동화보다 프로그램이 낫습니다.

• 장부를 쓰는 사람이 엑셀에 서툴러서 수식이 자주 깨진다.
• 통장 입금내역을 매번 붙여넣고 이미 적은 입금을 눈으로 골라내고 있다.
• 담당자마다 장부 파일이 따로 있어 합치고 맞추는 일이 반복된다.
• 그래도 결과는 지금까지 쓰던 엑셀 모양으로 받아 보고 싶다.

프로그램 첫 화면(현황). 아직 받을 돈 합계와 거래처별 미수금이 한눈에 보입니다(예시 데이터)

프로그램의 통장 내역 불러오기. 입금자 이름으로 거래처를 찾고 이미 적은 입금은 저절로 뺍니다(예시 데이터)

프로그램에서는 위의 수식들이 하던 일을 화면이 대신합니다. 엑셀로 저장하면 예전 장부와 같은 모양으로 나와서 따로 보관하거나 보내기도 편합니다. 어떻게 구성했는지는 마트 전단 납품 거래장부 구축 사례에 정리해 두었습니다.

거래장부나 미수금 관리처럼 매일 반복되는 엑셀 일을 줄이고 싶으시면 오닉소프트 업무 자동화를 참고하시거나 카카오톡 채널로 편하게 물어보세요. 지금 쓰시는 엑셀을 그대로 두고 손이 두 번 가는 곳만 없앨지, 프로그램으로 옮길지 함께 정해 드립니다.

거래장부 구축 사례 보기

카카오톡으로 문의하기