☰

이 수수료 구조에 대해 Excel에서 공식을 어떻게 생성합니까?

tag-icon • tag-icon microsoft-excel worksheet-function
이 수수료 구조에 대해 Excel에서 공식을 어떻게 생성합니까?

값이 있는 셀이 있습니다 A1 = 3,500,000.

나는 다음 공식에 따라 350만 달러에 대한 수수료를 계산하고 싶습니다.

첫 백만 달러에는 7% 추가. 두 번째 백만 달러에는 5% 추가. 세 번째 백만에 3% 추가. 그 이후에는 2%.

이상적으로는 이 모든 것이 하나의 셀에서 발생하도록 하여 이를 수행하는 전체 표나 시트를 갖는 것보다 하나의 셀에 하나의 수식을 사용하고 싶습니다.

감사해요!

답변1

단일 셀에 있는 단일 수식으로 이 문제를 해결하는 것은 확실히 가능하지만 수식을 쉽게 따르고 변경하기 쉽게 만들고 싶다면 표가 더 깔끔할 것입니다.

--

단일 셀 솔루션

단일 셀 솔루션의 경우 값이 A1에 있으면 이 공식은 계층별 요금을 계산합니다.

= A1 * 0.07 + MAX(A1-1000000,0) * -0.02 + MAX(A1-2000000,0) * -0.02 + MAX(A1-3000000,0) * -0.01

총 가치에 0.07을 곱한 다음 각 계층에 대해 새 계층과 이전 계층 간의 수수료 차이를 뺍니다. 이를 수행하는 방법은 다양합니다. 예를 한 가지만 제공했습니다.

--

간단한 테이블 솔루션

간단한 테이블 솔루션의 경우 다음을 수행할 수 있습니다.

여기에 이미지 설명을 입력하세요

각 계층의 가치를 계산하는 공식:

E2: =MIN(C2,$A$1)

E3: =MIN(C3,$A$1)-SUM($E$2:E2)

E4: =MIN(C4,$A$1)-SUM($E$2:E3)

E5: =MAX($A$1-C4,0)

총 수수료를 계산하는 공식:

D6: =SUMPRODUCT(D2:D5,E2:E5)

이 설정은 동적입니다. 임계값이나 수수료를 변경할 수 있으며 공식은 계속 작동합니다.

나중에 추가 계층이 필요할 경우 XML 테이블을 사용하는 것이 좋습니다. 4개 계층만 사용하지만 해당 계층에 대한 기준점과 수수료가 변경될 수 있는 경우 위의 간단한 표로 충분합니다.

답변2

이를 위해 (또한 매우 유사한 세금 계산 문제에 대해) 적절하게 구조화된 조회 테이블을 사용할 수 있습니다.

귀하의 커미션 테이블

  • 1열 = 매출의 기본 금액
  • 2열 = 각 계층의 백분율
  • 3열 = 해당 등급 이전에 획득한 커미션 금액

아래 표에서
G1: 0
G2: =(E3-E2)*F2+G2
필요한 만큼 입력하세요 .

그런 다음 데이터를A1

=(A1-VLOOKUP(A1,commissionTbl,1))*VLOOKUP(A1,commissionTbl,2)+VLOOKUP(A1,commissionTbl,3)

여기에 이미지 설명을 입력하세요

커미션 구조가 변경되면 표를 편집하는 것이 공식을 편집하는 것보다 훨씬 간단하다는 점에 유의하세요.

답변3

저는 3가지 가능성을 보여드리고 싶습니다.

여기에 이미지 설명을 입력하세요

  1. Tier 1, Tier 2, Tier 3, 그 다음 Tier 4의 가치를 별도로 계산하여 최종적으로 합계를 구합니다.

이를 위해 셀의 수식을 확인하십시오.

E2: =IF(D2<=1000000, D2*0.07, 1000000*0.07)

F2: =IF(D2>1000000, IF(D2<=2000000, (D21-1000000)*0.05, 1000000*0.05), 0)

G2: =IF(D2>2000000, IF(D2<=3000000, (D2-2000000)*0.03, 1000000*0.03), 0)

H2: =IF(D2>3000000, (D2-3000000)*0.02, 0)

그리고 마지막은I2: =SUM(E2:H2)

2. 또 다른 하나는 의 단일 공식 솔루션입니다 I5.

셀 I5:

=IF(D2<=1000000, D2*0.07, IF(D2<=2000000, 1000000*0.07 + (D2-1000000)*0.05, IF(D2<=3000000, 1000000*0.07 + 1000000*0.05 + (D2-2000000)*0.03, 1000000*0.07 + 1000000*0.05 + 1000000*0.03 + (D2-3000000)*0.02)))

3. 그리고 마지막은 I7LET 함수가 사용된 Excel의 상위 버전에 대한 Formula입니다.

셀 I7:

=LET( amount, D2,
        tier1, MIN(amount, 1000000),
        tier2, MAX(0, MIN(amount, 2000000) - 1000000),
        tier3, MAX(0, MIN(amount, 3000000) - 2000000),
        tier4, MAX(0, amount - 3000000),
        tier1*0.07 + tier2*0.05 + tier3*0.03 + tier4*0.02)

주의 필요에 따라 셀 참조를 조정합니다.

관련 정보