엑셀 단가표 만드는법: 단가가 바뀌면 지난 주문서까지 같이 바뀝니다

7월에 단가를 올렸더니 5월 주문서 금액이 같이 올라갔습니다.

단가표 셀에 새 단가를 덮어썼고, 지난 주문서가 그 셀을 참조하고 있었습니다. 이미 나간 견적과 이미 발행한 세금계산서 금액이 화면에서 달라집니다.

엑셀 단가표 만드는법을 덮어쓰기로 잡으면 반드시 한 번은 겪습니다.

엑셀 단가표 만드는법은 한 줄씩 쌓는 것입니다

품목마다 단가 한 칸을 두면 지난 단가가 사라집니다. 대신 단가가 바뀔 때마다 줄을 추가합니다.

품목코드   적용시작일     단가
P001      2026-01-01    12000
P001      2026-07-01    13500
P002      2026-01-01     8000

같은 품목이 여러 줄로 있고, 각 줄에 언제부터 적용되는지가 붙어 있습니다. 7월에 올려도 6월 주문은 12,000원을 그대로 봅니다.

엑셀 단가표 만드는법을 검색하면 대개 품목과 단가 두 열짜리 표가 나옵니다. 그 구조는 지금 단가를 보는 데는 충분하고, 지난 금액을 다시 계산하는 순간 무너집니다.

주문일에 맞는 단가를 찾아오는 수식

적용시작일이 주문일보다 앞선 줄 중에서 가장 최근 것을 가져옵니다.

=MAXIFS(단가!C:C,단가!A:A,$B2,단가!B:B,"<="&$A2)

여기서 A2는 주문일, B2는 품목코드입니다. MAXIFS가 없는 버전이면 이렇게 씁니다.

=LOOKUP(2,1/((단가!$A$2:$A$500=$B2)*(단가!$B$2:$B$500<=$A2)),단가!$C$2:$C$500)

두 번째 수식은 단가표가 적용시작일 오름차순으로 정렬돼 있어야 맞게 나옵니다. 정렬이 흐트러지면 조용히 틀린 단가를 가져오므로, 표를 만들 때 정렬을 한 번 걸어 두고 새 줄은 아래에만 추가합니다.

거래처별 단가는 열을 늘리지 말고 줄을 늘립니다

거래처마다 단가가 다르면 열을 옆으로 붙이고 싶어집니다. 거래처가 늘 때마다 열이 늘어나고, 수식이 열 번호를 세게 됩니다.

거래처 코드 열을 하나 추가하는 편이 낫습니다.

품목코드   거래처코드   적용시작일     단가
P001      기본         2026-01-01    12000
P001      C002        2026-01-01    11000

조회할 때 거래처 코드로 먼저 찾고, 없으면 기본 줄로 떨어뜨립니다.

=IFERROR(MAXIFS(단가!D:D,단가!A:A,$B2,단가!B:B,$C2,단가!C:C,"<="&$A2),
         MAXIFS(단가!D:D,단가!A:A,$B2,단가!B:B,"기본",단가!C:C,"<="&$A2))

거래처 코드를 붙이는 방법은 거래처 관리에 적어 뒀습니다.

단가가 없는 품목을 매달 셉니다

이 표에서 사람이 봐야 하는 숫자는 이것 하나입니다.

=COUNTIF(단가결과범위,0)

단가가 0으로 나온 줄은 단가표에 그 품목이 없거나, 주문일보다 앞선 적용시작일이 없는 경우입니다. 신제품이 나왔는데 단가표에 안 올라간 달에 이 숫자가 튑니다.

0을 그냥 두면 매출이 조용히 줄어듭니다. 오류로 표시되지 않아서 아무도 안 봅니다.

엑셀 단가비교는 같은 표에서 바로 나옵니다

이력으로 쌓아 두면 인상률 계산이 따로 필요 없습니다.

=단가_신 / 단가_구 - 1

품목별로 직전 단가와 현재 단가를 나란히 놓으면 어느 품목이 얼마나 올랐는지 한 화면에 보입니다. 엑셀 단가비교를 위해 표를 새로 만들 일이 없어집니다.

원가가 오른 만큼 판매단가를 올렸는지도 같은 표에서 확인합니다. 매입 단가를 같은 구조로 하나 더 두면 품목별 마진이 시점별로 나옵니다.

엑셀 단가 자동 설정과 엑셀 단가 함수는 여기까지가 안전합니다

주문서에서 품목을 고르면 단가가 자동으로 들어오게 만드는 것까지는 수식으로 됩니다. 그다음이 문제입니다.

특가나 임시 할인은 단가표에 없습니다. 담당자가 셀을 덮어써서 넣게 되고, 그 순간 수식이 지워집니다. 다음 달에 그 셀만 단가가 안 따라옵니다.

그래서 단가 열과 조정 열을 나눠 둡니다. 단가 열은 수식으로 두고, 할인은 조정 열에 금액이나 비율로 적습니다. 적용 단가는 두 열을 합쳐서 계산합니다. 이렇게 두면 누가 얼마를 깎았는지도 남습니다.

전체 순서는 엑셀 매출 관리 일곱 단계에 있습니다. 단가표는 견적서와 그대로 이어집니다.

자주 받는 질문

Q. 단가가 자주 안 바뀌는데도 이력으로 만들어야 하나요

A. 자주 안 바뀔수록 이력으로 두는 편이 낫습니다. 1년에 한 번 바뀌면 그 한 번을 덮어쓰는 순간 지난 열두 달 금액이 전부 달라집니다. 줄을 추가하는 방식은 관리 부담이 거의 없습니다.

Q. 적용시작일 대신 적용종료일을 두면 안 되나요

A. 종료일을 같이 두면 두 날짜를 매번 맞춰야 하고, 하루 비거나 겹치면 단가가 0이거나 두 개가 됩니다. 시작일만 두고 다음 줄이 나오기 전까지 유효한 것으로 보는 구조가 덜 깨집니다.

Q. 엑셀 단가 함수로 VLOOKUP을 쓰면 안 되나요

A. 품목당 단가가 하나면 VLOOKUP으로 충분합니다. 같은 품목이 여러 줄이 되는 순간 VLOOKUP은 첫 줄만 가져오므로 MAXIFS나 LOOKUP으로 바꿔야 합니다.

Q. 품목이 수천 개라 수식이 느립니다

A. 단가표를 표(Ctrl+T)로 만들고 참조를 표 이름으로 바꿉니다. 그래도 느리면 파워 쿼리에서 병합으로 처리해 값으로 받아 두고, 수식은 확인용으로만 남깁니다.

단가가 0으로 나온 줄이 없는 달만 그대로 청구합니다. 그 한 숫자부터 세어 두려 합니다.