쇼핑몰 가격표를 만들거나 견적서를 정리하다 보면 엑셀에서 할인율을 계산할 일이 자주 생겨요. 공식 자체는 초등학교 산수지만 셀 참조를 잘못 잡아 수십 행이 통째로 틀어지는 경우가 흔해요. 이 글에서는 할인율, 할인가, 정상가 역산, 절대참조, 조건별 할인율 적용까지 실제 수식을 하나씩 정리해요.
할인율을 구하는 기본 수식
할인율은 (정상가 – 할인가) / 정상가로 계산해요. 정상가가 10,000원이고 할인가가 8,000원이면 (10,000 – 8,000) / 10,000 = 0.2, 즉 20%예요. 엑셀에서 정상가가 B2, 할인가가 C2에 있다면 D2에 아래처럼 입력해요.
=(B2-C2)/B2
이렇게 입력하면 0.2처럼 소수로 나와요. 셀 서식을 백분율로 바꾸거나 홈 탭의 % 버튼을 누르면 20%로 보여요. 수식 끝에 *100을 곱하고 서식을 그대로 두는 방법도 있지만, 백분율 서식과 *100을 함께 쓰면 2000%처럼 나오는 실수가 생겨서 둘 중 하나만 쓰는 게 좋아요.
정상가가 0인 행이 섞여 있으면 #DIV/0! 오류가 나요. 그럴 때는 =IF(B2=0,””,(B2-C2)/B2)처럼 감싸면 빈칸으로 처리돼서 표가 깔끔해져요.
할인율로 할인가를 구하는 수식
반대로 정상가와 할인율을 알고 할인된 가격을 구할 때는 정상가에서 할인액을 빼요. 검색된 예제에서도 자주 쓰는 형태로, 정상가가 B14, 할인율(정수 %)이 C14에 있다면 아래처럼 써요.
=B14-(B14*C14/100)
할인율을 20%처럼 백분율 서식으로 입력해둔 경우에는 /100이 필요 없어요. 이때는 =B14*(1-C14)로 쓰면 더 간단해요. 어떤 형식으로 입력됐는지에 따라 수식이 달라지므로, 셀에 20이라고 보이는지 20%라고 보이는지부터 확인해야 해요.
이 두 방식을 섞어 쓰면 결과가 100배 틀어져요. 표 전체에서 한 가지 방식만 쓰도록 통일하는 게 실수를 줄이는 가장 쉬운 방법이에요.
할인가에서 정상가 역산하기
세일 가격과 할인율만 남아 있고 원래 가격을 모를 때가 있어요. 이럴 때는 할인가를 (1 – 할인율)로 나눠요. 할인가가 B24, 할인율(정수 %)이 C24에 있다면 아래 수식이에요.
=B24/(1-C24/100)
예를 들어 20% 할인된 가격이 8,000원이라면 8,000 / 0.8 = 10,000원이에요. 많이들 8,000에 20%를 더해서 9,600원이라고 계산하는데, 할인율은 정상가를 기준으로 붙은 것이라 이렇게 하면 틀려요. 이 함정 하나만 알아도 가격 역산 실수를 크게 줄일 수 있어요.
절대참조($)를 써야 하는 이유
할인율을 셀 한 곳에 넣어두고 여러 상품에 적용할 때 가장 많이 실수하는 부분이 참조 고정이에요. 예를 들어 F1에 할인율 15%를 넣고 B열 가격에 적용한다면 C2에는 아래처럼 써야 해요.
=B2*(1-$F$1)
$F$1처럼 앞에 달러 기호를 붙이면 아래로 수식을 복사해도 F1을 계속 가리켜요. 달러 기호 없이 F1만 쓰면 아래로 복사하는 순간 F2, F3으로 밀려서 빈 셀을 곱하게 돼요. F4 키를 누르면 $F$1 형태로 바로 바뀌어요.
할인율이 바뀌면 F1 한 칸만 고치면 표 전체가 함께 바뀌어서 관리도 쉬워요. 할인율을 수식 안에 0.85처럼 직접 박아두면 나중에 일일이 찾아 고쳐야 해서 비추천이에요.
금액 구간에 따라 할인율이 달라질 때
구매 금액이 클수록 할인율이 올라가는 구조는 IF 함수로 처리해요. 예를 들어 10만 원 이상이면 10%, 50만 원 이상이면 15%를 적용하는 표라면 아래처럼 중첩해서 써요.
=IF(B2>=500000,B2*0.85,IF(B2>=100000,B2*0.9,B2))
큰 금액 조건을 먼저 검사해야 해요. 순서를 거꾸로 하면 50만 원 이상 값도 10만 원 이상 조건에 먼저 걸려서 10%만 적용돼요.
구간이 4~5개 이상으로 늘어나면 IF 중첩이 복잡해져요. 이때는 별도 표에 구간과 할인율을 적어두고 VLOOKUP이나 XLOOKUP의 근사 일치 기능으로 찾아오면 수식이 훨씬 짧고 고치기도 쉬워요. 새 구간이 생겨도 표에 한 줄만 추가하면 되니까요.
결과가 지저분하게 나올 때 손보는 법
할인가를 계산하면 소수점이 길게 붙는 경우가 많아요. 원 단위로 정리하려면 ROUND 함수를 써요.
- =ROUND(B2*(1-$F$1),0): 반올림해서 원 단위 정수로 만들어요.
- =ROUNDDOWN(B2*(1-$F$1),0): 소수점 이하를 버려요. 쇼핑몰은 보통 이 방식을 써요.
- =FLOOR(B2*(1-$F$1),10): 10원 단위 아래를 절사할 때 써요. 가격을 깔끔하게 맞추고 싶을 때 좋아요.
서식만 바꿔서 보이는 값을 반올림하면 실제 셀 값은 그대로라 합계가 어긋나요. 합계까지 정확히 맞추려면 반드시 함수로 반올림해야 해요.
정리
할인율은 (정상가-할인가)/정상가, 할인가는 정상가*(1-할인율), 정상가 역산은 할인가/(1-할인율)로 기억하면 돼요. 할인율 셀은 $로 고정하고, 구간 할인은 IF나 조회 함수로 처리하세요.
표 전체에서 할인율 입력 방식을 정수 %와 백분율 서식 중 하나로 통일하는 것만으로도 오류를 대부분 예방할 수 있어요.