Excel에서 셀 값을 기준으로 다른 요소 곱하기

Question

다음을 계산하는 공식을 작성하려고 합니다.

=< 10,000 x 1.00

10,0001-12,500 x 1.25

12,501 - 15,000 x 1.50

예:

숫자가 8,520이면 (8,520 x 1.00) = 8,520

숫자가 11,560이라면 (10,000 x 1.00) + (1,560 x 1.25) = 11,950

숫자가 13,500이면 (10,000 x 1.00) + (2,500 x 1.25) + (1,000 x 1.50) = 14,625

Answer 1

이는 if를 사용하여 수행할 수 있습니다. 중첩된 if를 작성하는 방법은 다른 값에서 발생하는 작업을 수행하는 것입니다. A2에서 시작하는 데이터 열을 가정하면

1. A2<10000. 결과는 a2 + 0 또는 A2입니다.
2. 10000 < A2 <12501 결과는 a2 +(.25 * (A2-10000) )입니다.
3. 12500 < A2 <15000 결과는 a2 +(.25* (a2-10000)) + (.25 *( A2-12500)) 입니다.

이를 단일 수식으로 변환하려면 각 경우에 if를 사용하면 됩니다. 흐름은 =if(test,true,false)입니다. 여기서 테스트가 true이면 참 값을 얻고, 그렇지 않으면 거짓 값을 얻습니다. 첫 번째 솔루션은 3개의 ifs를 사용합니다. false 테스트 결과는 0이고, true 결과는 답입니다.

• =IF(A2<10001,A2,0)+IF(AND(A2>10000,A2<12501),A2+(0.25*(A2-10000))+IF(AND(A2>12500,A2<15001),A2+ (0.25*(A2-10000))+(0.25*(A2-12500))))

이는 대수학을 사용하여 단순화할 수 있습니다.

• =A2+IF(A2>10000,(0.25*(A2-10000)),0)+IF(A2>12500,(0.25*(A2-12500)))

15,000보다 큰 숫자를 처리하는 방법에 따라 두 형식 중 하나를 변경할 수 있습니다.

Answer 2

좋아요, 처음 생각했던 것보다 조금 더 복잡했어요.

다음과 같은 테이블을 생성합니다.

그런 다음 다음 배열 수식을 사용합니다.

=SUM(IF(A2>$D$2:$D$4,IF(A2<$E$2:$E$4,A2-$D$2:$D$4,$E$2:$E$4-$D$2:$D$4)*$F$2:$F$4,0))

배열 수식이므로 편집 모드를 종료할 때 Enter 대신 Ctrl-Shift-Enter를 사용하여 확인해야 합니다. 올바르게 수행되면 Excel에서 {}수식을 입력합니다.

---

@fixer의 공식을 기반으로 공식을 동적으로 만들어 다음과 같은 테이블을 만듭니다.

보시다시피 이제 요인은 실제 요인이 아니라 이전보다 증가한 것입니다.

그런 다음 다음 공식을 사용할 수 있습니다.

=A2+SUMPRODUCT((A2>$D$3:INDEX(D:D,MATCH(1E+99,D:D)))*MOD(A2,$D$3:INDEX(D:D,MATCH(1E+99,D:D)))*$E$3:INDEX(E:E,MATCH(1E+99,D:D)))

이제 테이블이 커지거나 작아지면 데이터 세트에 대한 참조도 커지는 점에서 수식은 동적입니다. INDEX(D:D,MATCH(1E+99,D:D))열의 마지막 셀을 찾아 데이터 세트의 범위로 설정합니다 .