쇼핑몰 가격표를 만들거나 견적서를 정리하다 보면 “20% 할인하면 얼마지?”, “이 가격이 몇 퍼센트 할인된 거지?” 같은 계산이 자꾸 나와요. 계산기로 하나씩 두드릴 수도 있지만, 엑셀 수식 몇 개만 익혀 두면 상품이 수백 개여도 몇 초 만에 끝나요.
엑셀에는 “할인율” 전용 함수가 따로 없어요. 그래서 사칙연산과 셀 참조, 그리고 IF 같은 기본 함수를 조합해서 계산해요. 이 글에서는 할인율 구하기, 할인가 구하기, 정상가 역산, 여러 행에 한 번에 적용하기, 조건별 할인율 적용 순서로 나눠서 실제 수식을 보여드려요.
할인율 구하는 기본 수식
할인율은 “얼마나 깎아 줬는지”를 정상가 대비 비율로 나타낸 값이에요. 공식은 (정상가 – 할인된 가격) / 정상가 예요. 결과에 100을 곱하면 퍼센트 숫자가 되고, 곱하지 않고 셀 서식을 퍼센트로 바꾸면 엑셀이 알아서 %를 붙여 줘요.
예를 들어 정상가 10,000원짜리가 8,000원에 팔린다면 (10,000 – 8,000) / 10,000 = 0.2, 즉 20%예요. 엑셀에서는 B2 셀에 정상가, C2 셀에 판매가가 있을 때 D2 셀에 아래처럼 입력해요.
- =(B2-C2)/B2 를 입력하고 셀 서식을 백분율로 바꾸면 20%로 보여요. 이 방식은 나중에 다른 수식에서 이 셀을 다시 참조할 때 0.2라는 실제 값이 유지돼서 편해요.
- =(B2-C2)/B2*100 처럼 100을 곱하면 20이라는 숫자가 나와요. 이때는 서식이 일반이라 % 기호가 붙지 않으니, 열 제목에 “할인율(%)”이라고 적어 두면 헷갈리지 않아요.
- =1-C2/B2 도 같은 결과예요. 판매가가 정상가의 몇 %인지를 구한 뒤 1에서 빼는 방식인데, 수식이 짧아서 즐겨 쓰는 분이 많아요.
정상가와 판매가가 같으면 결과가 0%, 판매가가 정상가보다 높으면 음수가 나와요. 정상가 칸이 비어 있으면 0으로 나누는 꼴이라 #DIV/0! 오류가 뜨니, 빈 행이 섞인 표라면 =IF(B2=0,””,(B2-C2)/B2) 처럼 감싸 두는 게 좋아요.
할인가 계산 수식
반대로 정상가와 할인율을 알고 할인된 가격을 구하는 경우가 가장 흔해요. 공식은 정상가 – (정상가 × 할인율) 이에요. 정상가 10,000원에 20% 할인이면 10,000 – 2,000 = 8,000원이에요.
B14 셀에 정상가, C14 셀에 할인율이 있을 때 수식은 두 가지 형태로 쓸 수 있어요. 할인율 칸에 20이라는 숫자를 넣었다면 =B14-(B14*C14/100) 이고, 셀 서식이 백분율이라 실제 값이 0.2라면 =B14*(1-C14) 라고 쓰면 돼요. 두 번째 방식이 훨씬 간단해서, 할인율은 퍼센트 서식으로 입력하는 걸 권해요.
여기서 (1-할인율)은 “정상가의 몇 %를 내는가”를 뜻해요. 20% 할인이면 80%를 내는 거니까 0.8을 곱하는 거예요. 이 개념을 이해하면 할인이 여러 번 겹치는 경우도 쉽게 풀려요. 예를 들어 20% 할인 후 추가로 10% 쿠폰을 쓴다면 =B14*(1-20%)*(1-10%) 로 계산하고, 결과는 정상가의 72%예요. 흔히 하는 실수가 20%+10%를 더해 30% 할인으로 계산하는 건데, 실제로는 28% 할인이라서 두 값이 달라요.
수량이 있는 견적서라면 단가 × 수량 × (1 – 할인율) 로 한 번에 계산해요. 단가가 D5, 수량이 E5, 할인율이 F5라면 =D5*E5*(1-F5) 예요. 여기서 1은 100%를 뜻하고, 이 수식을 합계 열에 채워 두면 수량이나 할인율을 바꿀 때마다 자동으로 금액이 갱신돼요.
정상가 역산하는 방법
할인된 가격과 할인율만 알고 원래 가격이 궁금할 때도 있어요. 중고 거래나 세일 상품 가격 비교에서 자주 쓰는 계산이에요. 공식은 정상가 = 할인가 / (1 – 할인율) 이에요.
할인가 8,000원, 할인율 20%라면 8,000 / (1 – 0.2) = 8,000 / 0.8 = 10,000원이에요. 엑셀에서 B24에 할인가, C24에 할인율(숫자 20으로 입력)이 있다면 =B24/(1-C24/100) 이고, 할인율을 퍼센트 서식(0.2)으로 넣었다면 =B24/(1-C24) 예요.
많은 분이 여기서 실수하는 지점이 있어요. 8,000원에 20%를 “더해서” 9,600원이라고 계산하는 거예요. 하지만 20%는 정상가 기준의 비율이지 할인가 기준이 아니라서, 정답은 10,000원이에요. 8,000원에 25%를 더해야 10,000원이 되는 셈이라 두 값이 다르다는 걸 기억해 두세요.
- 부가세 역산도 같은 원리예요. 부가세 포함 금액에서 공급가액을 구할 때 =합계/1.1 로 계산하는데, 이것도 “1 + 비율”로 나누는 역산이에요.
- 검산은 순방향 수식으로 해요. 역산해서 나온 정상가에 =정상가*(1-할인율) 을 적용했을 때 원래 할인가가 나오면 맞게 계산한 거예요.
절대참조로 여러 상품에 한 번에 적용하기
할인율이 하나로 정해져 있고 상품이 100개라면, 할인율을 별도 셀 하나에 적어 두고 모든 행이 그 셀을 바라보게 하는 게 정석이에요. 이때 필요한 개념이 절대참조예요. 셀 주소 앞에 $를 붙여서 수식을 아래로 복사해도 주소가 움직이지 않게 고정하는 방식이에요.
예를 들어 F1 셀에 할인율 20%를 적어 두고, A열에 상품명, B열에 정상가를 넣었다고 해 볼게요. C2 셀에 =B2*(1-$F$1) 을 입력하고 아래로 끌어 내리면 C3은 =B3*(1-$F$1), C4는 =B4*(1-$F$1) 로 정상가 부분만 바뀌고 할인율 셀은 그대로 유지돼요. $ 없이 F1이라고 쓰면 두 번째 행에서 F2, 세 번째 행에서 F3으로 밀려서 엉뚱한 값이 나와요.
키보드로는 수식 입력 중에 셀 주소를 클릭한 뒤 F4 키를 누르면 $F$1, F$1, $F1, F1 순서로 바뀌어요. 행만 고정하고 싶을 때(F$1), 열만 고정하고 싶을 때($F1)를 구분해서 쓰면 표 전체에 수식 하나로 계산하는 이차원 할인표도 만들 수 있어요.
이렇게 만들어 두면 할인율이 20%에서 25%로 바뀌었을 때 F1 셀 하나만 고치면 표 전체가 자동으로 다시 계산돼요. 셀 이름 정의 기능으로 F1에 “할인율”이라는 이름을 붙이면 =B2*(1-할인율) 처럼 읽기 쉬운 수식도 만들 수 있어요.
IF 함수로 조건별 할인율 적용하기
구매 금액이 클수록 할인율이 높아지는 식의 조건부 할인은 IF 함수로 풀어요. 예를 들어 10만 원 이상이면 10%, 5만 원 이상이면 5%, 그 미만이면 할인 없음이라는 규칙이라면 아래처럼 써요.
- =IF(B2>=100000,B2*0.9,IF(B2>=50000,B2*0.95,B2)) 처럼 IF를 겹쳐서 큰 조건부터 차례로 검사해요. 큰 금액 조건을 먼저 써야 10만 원 이상 값이 5만 원 조건에 먼저 걸리는 일이 없어요.
- 적립금 조건이 함께 있으면 AND, OR을 써요. 예를 들어 =IF(AND(B2>=50000,C2=”회원”),B2*0.9,B2) 는 5만 원 이상이면서 회원인 경우에만 10% 할인을 줘요.
- 구간이 많아지면 별도 표와 VLOOKUP을 써요. 금액 구간과 할인율을 작은 표로 만들어 두고 =B2*(1-VLOOKUP(B2,$H$2:$I$5,2,TRUE)) 처럼 조회하면 IF를 5중, 6중으로 겹치지 않아도 돼요.
구간 표를 쓸 때는 금액 열이 오름차순으로 정렬돼 있어야 VLOOKUP의 근사 일치(마지막 인수 TRUE)가 제대로 동작해요. 0원, 50,000원, 100,000원 같은 하한값을 왼쪽 열에 적고 오른쪽 열에 0%, 5%, 10%를 적으면 되고, 규칙이 바뀌면 표만 수정하면 돼요.
소수점 처리와 서식 정리
할인가를 계산하다 보면 원 단위 아래로 소수점이 생기는 경우가 많아요. 예를 들어 9,900원에 15% 할인이면 8,415원으로 딱 떨어지지만, 12,345원에 7% 할인이면 11,480.85원이 나와요. 표시만 반올림되고 실제 값은 그대로 남아 합계가 1원씩 어긋나는 일이 생길 수 있어서, 금액 계산에는 ROUND 계열 함수를 쓰는 게 안전해요.
- ROUND(수식,0) 은 소수점 아래를 반올림해서 원 단위로 만들어요. 일반적인 할인가 계산에 가장 무난해요.
- ROUNDDOWN(수식,-1) 은 10원 단위 아래를 버려요. 가게에서 10원 미만은 받지 않는 방식으로 가격을 정할 때 써요.
- ROUNDUP과 CEILING 은 올림 처리예요. 할인 후 가격을 100원 단위로 맞추고 싶다면 =CEILING(B2*(1-F1),100) 이 편해요.
표시 서식도 함께 손봐 두면 좋아요. 할인율 열은 셀 서식에서 백분율과 소수점 자릿수를 정하고, 금액 열은 쉼표 스타일(Ctrl+Shift+1)을 적용하면 천 단위 구분이 붙어서 한눈에 읽혀요. 할인율이 특정 값 이상인 행만 색을 넣고 싶다면 조건부 서식에서 “셀 값이 0.3 이상”이라는 규칙을 추가하면 30% 이상 할인 상품만 자동으로 강조돼요.
정리해 두면 좋은 공식 모음
자주 쓰는 공식을 한곳에 모아 두면 다음에 다시 찾을 때 편해요. 셀 주소는 정상가가 B2, 할인가가 C2, 할인율이 D2라고 가정했어요.
- 할인율: =(B2-C2)/B2 를 구한 뒤 서식을 백분율로 바꿔요.
- 할인가: =B2*(1-D2) 예요. 할인율이 20처럼 숫자면 =B2*(1-D2/100) 을 써요.
- 할인 금액(깎인 돈): =B2-C2 또는 =B2*D2 예요. 영수증에 “할인액” 항목이 따로 필요할 때 써요.
- 정상가 역산: =C2/(1-D2) 예요. 결과를 검산하려면 다시 =정상가*(1-D2) 로 계산해 보세요.
- 중복 할인: =B2*(1-D2)*(1-E2) 예요. 할인율을 더하지 않고 곱해서 적용해요.
정리하면 엑셀 할인율 계산의 핵심은 “1에서 할인율을 뺀 값을 곱한다”는 한 가지 생각이에요. 이것만 알면 할인가, 정상가 역산, 중복 할인이 모두 같은 원리로 풀려요. 여기에 절대참조와 IF, ROUND를 더하면 견적서나 가격표를 실무에서 바로 쓸 수 있는 수준으로 만들 수 있어요.