중복 발주내역에서 ‘최신 단가’만 뽑는 엑셀 실무 정리법

엑셀퀘스트 스터디클럽 · 데이터위자드

발주내역을 계속 쌓다 보면 같은 거래처, 같은 품목인데 단가가 여러 번 바뀌는 경우가 많습니다. 문제는 보고서나 견적 비교표를 만들 때 현재 적용해야 할 최신 단가만 필요하다는 점입니다. 단순히 중복 제거를 누르면 어떤 단가가 남는지 불안하고, 정렬을 잘못하면 예전 단가가 살아남기도 합니다.

오늘은 실무에서 자주 쓰는 방식으로 거래처 + 품목 기준 최신 단가표를 만드는 방법을 정리해 보겠습니다. 함수로 빠르게 확인하는 방법, Power Query로 반복 작업을 줄이는 방법, 마지막으로 새로고침용 VBA까지 같이 다룹니다. 난이도는 중간 이상이지만, 중간중간 확인 순서를 넣어두었으니 천천히 따라오시면 됩니다.

1. 예제 데이터 구조부터 잡기

먼저 발주내역은 아래처럼 누적형 데이터로 관리하는 것이 좋습니다. 월별로 시트를 나누는 방식보다 한 시트에 계속 쌓는 방식이 나중에 훨씬 편합니다.

발주번호	발주일	거래처	품목코드	품목명	수량	단가	키
PO-2401-001	2024-01-08	한빛상사	A-100	알루미늄 케이스	30	12500	한빛상사\|A-100
PO-2403-018	2024-03-15	한빛상사	A-100	알루미늄 케이스	50	13200	한빛상사\|A-100
PO-2403-021	2024-03-16	도원부품	B-220	고정 브라켓	80	4100	도원부품\|B-220

핵심은 마지막 열의 키입니다. 거래처와 품목코드를 합쳐서 하나의 기준값을 만들어 둡니다. 품목명은 오타가 생길 수 있으므로 가능하면 품목코드를 기준으로 잡는 것이 안전합니다.

=[@거래처]&"|"&[@품목코드]

데이터 범위는 반드시 표로 변환해 두는 것을 추천합니다. 범위 안 아무 셀이나 선택한 뒤 Ctrl + T를 누르고, 표 이름을 예를 들어 tbl발주로 바꿔두면 수식이 훨씬 읽기 쉬워집니다.

2. 함수로 최신 단가 가져오기

이 기능은 Microsoft 365 또는 Excel 2021 이상에서 원활히 사용할 수 있습니다.

최신 단가표 시트에는 거래처, 품목코드, 품목명 정도를 기준 목록으로 만들고, 최신 발주일과 최신 단가를 가져오면 됩니다. 먼저 특정 거래처와 품목코드의 최신 발주일을 구하는 수식입니다.

=MAXIFS(tbl발주[발주일], tbl발주[키], [@거래처]&"|"&[@품목코드])

이제 해당 최신 발주일에 맞는 단가를 가져옵니다.

=LET(
    k, [@거래처]&"|"&[@품목코드],
    d, MAXIFS(tbl발주[발주일], tbl발주[키], k),
    XLOOKUP(1, (tbl발주[키]=k)*(tbl발주[발주일]=d), tbl발주[단가], "확인필요")
)

이 수식은 두 단계를 한 번에 처리합니다. 먼저 같은 키의 최신 발주일을 찾고, 그 발주일과 키가 동시에 일치하는 행의 단가를 가져옵니다. 단가뿐 아니라 발주번호, 수량, 담당자 같은 열도 같은 방식으로 가져올 수 있습니다.

같은 날짜에 발주가 2번 있으면?

실무에서는 같은 거래처, 같은 품목이 같은 날짜에 두 번 발주되는 경우가 있습니다. 이때는 발주일만으로는 최신 여부가 애매합니다. 발주번호가 순서대로 증가한다면 발주번호까지 같이 기준에 넣는 것이 좋습니다.

=LET(
    k, [@거래처]&"|"&[@품목코드],
    대상, FILTER(tbl발주, tbl발주[키]=k),
    최신행, TAKE(SORTBY(대상, CHOOSECOLS(대상,2), -1, CHOOSECOLS(대상,1), -1), 1),
    CHOOSECOLS(최신행, 7)
)

위 수식은 조건에 맞는 행만 걸러낸 뒤, 발주일을 내림차순으로 정렬하고, 발주번호도 내림차순으로 한 번 더 정렬한 다음 맨 위 한 행의 단가를 가져오는 방식입니다. 데이터가 크다면 계산량이 늘 수 있으므로, 수천 행 이상에서는 Power Query 방식도 함께 검토하는 편이 좋습니다.

3. 흔한 실수: 중복 제거를 먼저 누르는 경우

가장 많이 보는 실수는 발주내역을 거래처와 품목코드로 정렬한 뒤 데이터 > 중복된 항목 제거를 바로 누르는 것입니다. 이 기능은 편리하지만, 남길 행을 정확히 통제하지 않으면 예전 단가가 남을 수 있습니다.

중복 제거를 써야 한다면 최소한 아래 순서를 지켜야 합니다.

발주일을 최신순으로 정렬한다.
같은 날짜 중복이 있다면 발주번호도 최신순으로 정렬한다.
거래처, 품목코드 기준으로 중복 제거한다.
남은 단가가 실제 최근 발주내역과 맞는지 샘플 확인한다.

하지만 이 방법은 원본을 직접 건드리기 쉽고, 다음 달 데이터가 추가되면 다시 같은 작업을 해야 합니다. 그래서 반복 업무라면 함수 또는 Power Query로 분리하는 쪽이 안정적입니다.

4. Power Query로 최신 단가표 만들기

발주 파일이 매달 추가되거나, ERP에서 내려받은 원본을 정리해야 한다면 Power Query가 훨씬 좋습니다. 원본 표를 선택한 뒤 데이터 > 테이블/범위에서를 눌러 쿼리 편집기로 가져옵니다.

추천 흐름은 아래와 같습니다.

발주일 열의 데이터 형식을 날짜로 지정한다.
단가 열의 데이터 형식을 숫자로 지정한다.
거래처, 품목코드의 앞뒤 공백을 제거한다.
발주일 내림차순, 발주번호 내림차순으로 정렬한다.
정렬 상태를 고정한 뒤 거래처와 품목코드 기준으로 중복 제거한다.
결과를 최신단가표 시트에 로드한다.

쿼리에서 정렬 후 중복 제거를 할 때는 정렬이 유지되도록 처리하는 것이 중요합니다. 아래는 참고용 M 코드 흐름입니다.

let
    원본 = Excel.CurrentWorkbook(){[Name="tbl발주"]}[Content],
    형식변경 = Table.TransformColumnTypes(원본,{{"발주일", type date}, {"단가", Int64.Type}}),
    공백정리 = Table.TransformColumns(형식변경,{{"거래처", Text.Trim, type text}, {"품목코드", Text.Trim, type text}}),
    정렬 = Table.Sort(공백정리,{{"발주일", Order.Descending}, {"발주번호", Order.Descending}}),
    버퍼 = Table.Buffer(정렬),
    최신만 = Table.Distinct(버퍼, {"거래처", "품목코드"})
in
    최신만

Table.Buffer는 정렬 후 중복 제거를 할 때 의도한 순서가 흐트러지는 문제를 줄이는 데 도움이 됩니다. 특히 데이터가 많거나 단계가 복잡한 쿼리에서는 이런 작은 차이가 결과 신뢰도에 영향을 줍니다.

5. 확인 순서: 결과를 바로 믿지 말고 검증하기

최신 단가표를 만들고 나면 최소한 아래 항목은 확인해 보세요.

확인 항목	체크 방법	문제 발생 원인
최신 발주일이 맞는가	원본에서 거래처+품목코드 필터 후 날짜 비교	날짜가 텍스트로 저장됨
단가가 숫자인가	합계 또는 평균 계산 가능 여부 확인	쉼표, 원 문자, 공백 포함
거래처명이 중복처럼 보이는가	TRIM, CLEAN 또는 Power Query 공백 제거	앞뒤 공백, 특수 공백
동일 날짜 복수 발주가 있는가	거래처+품목+발주일 기준 개수 확인	발주번호 기준 미적용

특히 날짜가 텍스트로 저장된 경우가 많습니다. 겉으로는 2024-03-15처럼 보여도 실제로는 문자일 수 있습니다. 이 상태에서는 MAXIFS나 정렬 결과가 기대와 다르게 나올 수 있으니, 원본 단계에서 날짜 형식을 먼저 잡아주는 것이 좋습니다.

6. 피벗으로 단가 변동까지 같이 보기

최신 단가표만 있으면 현재 단가 확인은 쉽지만, 구매팀에서는 보통 단가가 얼마나 올랐는지도 궁금해합니다. 이때 원본 발주내역으로 피벗 테이블을 하나 추가해 두면 보고서 완성도가 올라갑니다.

행: 거래처, 품목코드, 품목명
열: 발주월
값: 평균 단가 또는 마지막 단가
필터: 주요 거래처, 품목군

단가 변동폭을 보려면 최신단가표에 이전 단가 열을 하나 더 만들고, 최신 직전 발주일을 구하는 방식으로 확장할 수 있습니다. 또는 Power Query에서 거래처+품목별로 인덱스를 매긴 뒤 1번 행은 최신, 2번 행은 직전 단가로 분리하는 방법도 좋습니다.

7. VBA로 새로고침 버튼 만들기

Power Query와 피벗을 같이 쓰는 파일이라면 버튼 하나로 전체 새로고침을 걸어두면 편합니다. 아래 코드는 현재 통합문서의 쿼리와 피벗을 새로고침하는 간단한 예시입니다.

Sub 최신단가_전체새로고침()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ThisWorkbook.RefreshAll
    Application.CalculateUntilAsyncQueriesDone

    Dim pc As PivotCache
    For Each pc In ThisWorkbook.PivotCaches
        pc.Refresh
    Next pc

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    MsgBox "최신 단가표 새로고침이 완료되었습니다.", vbInformation
End Sub

버튼은 개발 도구 > 삽입 > 양식 컨트롤 단추로 넣고, 위 매크로를 연결하면 됩니다. 원본 표에 새 발주내역을 붙여넣은 뒤 버튼만 누르면 최신단가표와 피벗 보고서가 함께 갱신됩니다.

8. 실무 응용 팁

이 방식은 단가표뿐 아니라 여러 업무에 그대로 응용할 수 있습니다.

고객별 마지막 상담일: 고객명 기준으로 최신 상담일과 상담내용 가져오기
설비별 최근 점검 결과: 설비코드 기준으로 마지막 점검일, 판정, 담당자 가져오기
상품별 최근 입고가: 상품코드 기준으로 최근 입고일과 매입단가 가져오기
직원별 최근 평가등급: 사번 기준으로 최근 평가월과 등급 가져오기

공통 원리는 같습니다. 기준 키를 만들고, 최신 날짜를 찾고, 그 행의 값을 가져온다입니다. 여기에 같은 날짜 중복이 있다면 보조 기준을 하나 더 추가하면 됩니다.

마무리

최신 단가표는 단순해 보이지만, 원본이 누적될수록 실수가 생기기 쉬운 작업입니다. 한두 번만 쓸 자료라면 함수로 빠르게 처리해도 좋고, 매월 반복되는 업무라면 Power Query로 구조를 만들어 두는 편이 안정적입니다. 여기에 새로고침 버튼까지 붙여두면 담당자가 바뀌어도 같은 방식으로 결과를 만들 수 있습니다.

다음에 발주내역을 정리할 때는 중복 제거부터 누르지 말고, 먼저 기준 키와 최신 판단 기준을 정해 보세요. 이 두 가지만 명확하면 단가표 품질이 확실히 좋아집니다.