{"id":11532,"date":"2025-01-15T04:12:35","date_gmt":"2025-01-15T04:12:35","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/15\/xay-dung-bang-tinh-thue-tncn-tren-excel-phan-3\/"},"modified":"2025-01-15T04:12:35","modified_gmt":"2025-01-15T04:12:35","slug":"xay-dung-bang-tinh-thue-tncn-tren-excel-phan-3","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/15\/xay-dung-bang-tinh-thue-tncn-tren-excel-phan-3\/","title":{"rendered":"X\u00c2Y D\u1ef0NG B\u1ea2NG T\u00cdNH THU\u1ebe TNCN TR\u00caN EXCEL \u2013 PH\u1ea6N 3"},"content":{"rendered":"
\n

Ph\u1ea7n 1<\/a>\u00a0\u00a0 \u2013 \u00a0\u00a0Ph\u1ea7n 2<\/a>\u00a0\u00a0 \u2013 \u00a0\u00a0Ph\u1ea7n 3<\/a><\/strong><\/p>\n

Vi\u1ec7c ph\u1ea3i t\u00ednh thu\u1ebf Thu nh\u1eadp c\u00e1 nh\u00e2n lu\u00f4n l\u00e0 m\u1ed9t vi\u1ec7c khi\u1ebfn nh\u1eefng ng\u01b0\u1eddi l\u00e0m k\u1ebf to\u00e1n\u00a0\u0111au\u00a0\u0111\u1ea7u. M\u1ed9t ph\u1ea7n b\u1edfi c\u00e1ch t\u00ednh bi\u1ec3u thu\u1ebf lu\u1ef9 ti\u1ebfn ph\u1ee9c t\u1ea1p, m\u1ed9t ph\u1ea7n n\u1eefa l\u00e0 vi\u1ec7c lu\u1eadt thu\u1ebf hay ph\u1ea3i\u00a0\u0111i\u1ec1u ch\u1ec9nh.<\/p>\n

B\u00e0i vi\u1ebft n\u00e0y s\u1ebd gi\u00fap c\u00e1c b\u1ea1n hi\u1ec3u r\u00f5 c\u00e1ch t\u00ednh, x\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c chu\u1ea9n tr\u00ean file excel,\u00a0\u0111\u1ed3ng th\u1eddi c\u00f3 th\u1ec3 tu\u1ef3 bi\u1ebfn theo th\u1ef1c t\u1ebf. Nh\u01b0ng\u00a0\u0111\u1ec3 hi\u1ec3u r\u00f5 h\u01a1n, c\u00e1c b\u1ea1n c\u1ea7n n\u1eafm r\u00f5 n\u1ed9i dung c\u1ee7a lu\u1eadt thu\u1ebf TNCN.<\/p>\n

Ph\u1ea7n 3: R\u00fat g\u1ecdn theo c\u00e1ch t\u00ednh Thu\u1ebf lu\u1ef9 ti\u1ebfn\u00a0\u2013 C\u00e1ch l\u00e0m th\u1ee9\u00a02<\/strong><\/p>\n

Nh\u01b0\u00a0\u0111\u00e3 n\u00f3i\u00a0\u1edf ph\u1ea7n tr\u01b0\u1edbc, vi\u1ec7c s\u1eed d\u1ee5ng 7 c\u00f4ng th\u1ee9c cho 7 b\u1eadc t\u00ednh thu\u1ebf kh\u00e1 d\u00e0i v\u00e0 d\u1ec5 nh\u1ea7m, n\u00ean\u00a0\u1edf ph\u1ea7n n\u00e0y, ch\u00fang ta s\u1ebd h\u1ecdc 1 c\u00e1ch l\u00e0m nhanh h\u01a1n, c\u00f3 th\u1ec3 cho ph\u00e9p g\u1ed9p 7 h\u00e0m\u00a0IF l\u1ed3ng v\u00e0o nhau m\u1ed9t c\u00e1ch d\u1ec5 d\u00e0ng h\u01a1n v\u00e0 kh\u00f4ng b\u1ecb nh\u1ea7m l\u1eabn.<\/p>\n

Vi\u1ec7c quan tr\u1ecdng nh\u1ea5t trong ph\u1ea7n n\u00e0y,\u00a0\u0111\u00f3 l\u00e0 vi\u1ec7c hi\u1ec3u n\u1ed9i dung v\u1ec1 THU\u1ebe LU\u1ef8 TI\u1ebeN<\/p>\n

\u0110\u1ec3 gi\u1ea3m th\u1eddi gian vi\u1ebft c\u0169ng nh\u01b0 ph\u00e2n t\u00edch, c\u00e1c b\u1ea1n h\u00e3y xem h\u00ecnh sau:<\/p>\n

<\/p>\n

\n
\n

D\u00f9 \u0111\u00e3 c\u00f3 ph\u1ea7n m\u1ec1m, nh\u01b0ng k\u1ef9 n\u0103ng Excel v\u1eabn c\u1ef1c k\u1ef3 quan tr\u1ecdng v\u1edbi k\u1ebf to\u00e1n, b\u1ea1n \u0111\u00e3 v\u1eefng Excel ch\u01b0a? H\u00e3y \u0111\u1ec3 t\u00f4i gi\u00fap b\u1ea1n, \u0111\u0103ng k\u00fd kho\u00e1 h\u1ecdc Excel:<\/p>\n


\n
\n<\/a>\n<\/div>\n<\/div>\n

\n
\n
\n
\n<\/a>\n<\/div>\n<\/div>\n
\n
\n
\n
\n<\/a>\n<\/div>\n<\/div>\n

1. Thu\u1ebf lu\u1ef9 ti\u1ebfn l\u00e0 g\u00ec?<\/strong><\/em><\/p>\n

(\u0110\u00e2y l\u00e0 c\u00e1ch g\u1ecdi c\u1ee7a c\u00e1 nh\u00e2n t\u00e1c gi\u1ea3, ngo\u00e0i ra b\u1ea1n c\u00f3 th\u1ec3 t\u1ef1\u00a0\u0111\u1eb7t t\u00ean cho n\u00f3 l\u00e0 g\u00ec c\u0169ng\u00a0\u0111\u01b0\u1ee3c)<\/p>\n

\u1ede b\u00e0i tr\u01b0\u1edbc ta\u00a0\u0111\u00e3 bi\u1ebft v\u1ec1 kh\u00e1i ni\u1ec7m Kho\u1ea3ng lu\u1ef9 ti\u1ebfn.<\/p>\n

V\u00e0 th\u00eam n\u1eefa, ta bi\u1ebft\u00a0\u0111\u01b0\u1ee3c khi Thu nh\u1eadp t\u00ednh thu\u1ebf (TNTT) v\u01b0\u1ee3t qu\u00e1 gi\u1edbi h\u1ea1n\u00a0\u1edf b\u1eadc n\u00e0o\u00a0\u0111\u00f3, th\u00ec t\u1ea1i b\u1eadc\u00a0\u0111\u00f3 s\u1ebd t\u00ednh tr\u1ef1c ti\u1ebfp tr\u00ean KLT * Thu\u1ebf su\u1ea5t, ch\u1ee9 kh\u00f4ng c\u00f2n ph\u1ee5 thu\u1ed9c TNTT n\u1eefa.<\/p>\n

Do\u00a0\u0111\u00f3, ta c\u00f3 th\u1ec3 th\u1ea5y s\u1ed1 ti\u1ec1n thu\u1ebf\u00a0\u0111\u01b0\u1ee3c t\u00ednh\u00a0\u1edf c\u00e1c b\u1eadc m\u00e0 c\u00f3 gi\u1edbi h\u1ea1n nh\u1ecf h\u01a1n TNTT s\u1ebd lu\u00f4n l\u00e0 1 s\u1ed1 x\u00e1c\u00a0\u0111\u1ecbnh.<\/p>\n

S\u1ed1\u00a0\u0111\u00f3\u00a0\u0111\u01b0\u1ee3c t\u00ednh nh\u01b0 c\u00f4ng th\u1ee9c trong h\u00ecnh.<\/p>\n

\n

Thu\u1ebf lu\u1ef9 ti\u1ebfn B\u1eadc n\u00e0y =\u00a0KLT b\u1eadc tr\u01b0\u1edbc \u0111\u00f3\u00a0* Thu\u1ebf su\u1ea5t b\u1eadc tr\u01b0\u1edbc \u0111\u00f3\u00a0+ Thu\u1ebf lu\u1ef9 ti\u1ebfn b\u1eadc tr\u01b0\u1edbc\u00a0\u0111\u00f3<\/p>\n<\/blockquote>\n

Thu\u1ebf lu\u1ef9 ti\u1ebfn b\u1eadc 1 = 0, v\u00ec\u00a0\u1edf b\u1eadc 1 kh\u00f4ng c\u00f3 thu\u1ebf lu\u1ef9 ti\u1ebfn b\u1eadc tr\u01b0\u1edbc\u00a0\u0111\u00f3, v\u00e0 b\u1eaft\u00a0\u0111\u1ea7u nh\u1ecf nh\u1ea5t l\u00e0 0<\/p>\n

2. S\u1eed d\u1ee5ng t\u00ednh thu\u1ebf nh\u01b0 th\u1ebf n\u00e0o?<\/strong><\/em><\/p>\n

X\u00e9t v\u00ed d\u1ee5 cho d\u1ec5 hi\u1ec3u<\/p>\n

Kho\u1ea3n A = 40 tri\u1ec7u<\/p>\n

Nh\u01b0 c\u00e1ch t\u00ednh c\u1ee7a Ph\u1ea7n 2, ta bi\u1ebft s\u1ed1 thu\u1ebf ph\u1ea3i n\u1ed9p t\u1eebng b\u1eadc l\u00e0:<\/p>\n

<\/p>\n

N\u1ebfu ta ph\u00e2n t\u00edch theo h\u01b0\u1edbng kh\u00e1c, s\u1ebd th\u1ea5y l\u00e0:<\/p>\n

40 tri\u1ec7u > 32 tri\u1ec7u. N\u00ean s\u1ed1 thu\u1ebf t\u00ednh t\u1edbi h\u1ebft b\u1eadc 4 s\u1ebd l\u00e0 1 s\u1ed1 c\u1ed1\u00a0\u0111\u1ecbnh (t\u1ea1m g\u1ecdi l\u00e0 B)<\/p>\n

\u1ede b\u1eadc 5, ch\u1ec9 c\u00f2n 8 tri\u1ec7u, t\u00ednh tr\u00ean thu\u1ebf su\u1ea5t 25% = 2 tri\u1ec7u (t\u1ea1m g\u1ecdi l\u00e0 C)<\/p>\n

V\u1eady t\u00ednh nhanh cho B nh\u01b0 th\u1ebf n\u00e0o?<\/p>\n

B\u00a0\u0111\u01b0\u1ee3c bi\u1ebft l\u00e0 s\u1ed1 thu\u1ebf lu\u1ef9 ti\u1ebfn t\u1edbi b\u1eadc 5= S\u1ed1 thu\u1ebf t\u1ed1i\u00a0\u0111a b\u1eadc 1 +\u00a0S\u1ed1 thu\u1ebf t\u1ed1i\u00a0\u0111a b\u1eadc 2 +\u00a0S\u1ed1 thu\u1ebf t\u1ed1i\u00a0\u0111a b\u1eadc 3 +\u00a0S\u1ed1 thu\u1ebf t\u1ed1i\u00a0\u0111a b\u1eadc 4<\/p>\n

(S\u1ed1 thu\u1ebf t\u1ed1i\u00a0\u0111a m\u1ed7i b\u1eadc = Kho\u1ea3ng lu\u1ef9 ti\u1ebfn * Thu\u1ebf su\u1ea5t b\u1eadc\u00a0\u0111\u00f3)<\/p>\n

= 250,000 + 500,000 +1,200,000 + 2,800,000<\/p>\n

= 4,750,000 = Thu\u1ebf lu\u1ef9 ti\u1ebfn b\u1eadc\u00a05<\/p>\n

V\u1eady hi\u1ec3u ng\u1eafn g\u1ecdn s\u1ebd l\u00e0:<\/p>\n

\n

A = 40 tri\u1ec7u = B + C<\/p>\n

B = Thu\u1ebf lu\u1ef9 ti\u1ebfn b\u1eadc 5\u00a0= 4,750,000<\/p>\n

C = S\u1ed1 c\u00f2n l\u1ea1i\u00a0\u1edf b\u1eadc 5 = 8,000,000 * 25% = 2,000,000<\/p>\n

A = 4,750,000 + 2,000,000 = 6,750,000<\/p>\n<\/blockquote>\n

Khi\u00a0\u0111\u00f3\u00a0\u00e1p d\u1ee5ng thu\u1ebf lu\u1ef9 ti\u1ebfn s\u1ebd t\u00ednh nhanh h\u01a1n c\u00e1ch l\u00e0m th\u00f4ng th\u01b0\u1eddng.<\/p>\n

3. C\u00f4ng th\u1ee9c t\u1ed5ng qu\u00e1t<\/strong><\/em><\/p>\n

X\u00e9t 1 kho\u1ea3n TNTT l\u00e0 A = s\u1ed1 b\u1ea5t k\u1ef3<\/p>\n

\n

N\u1ebfu A n\u1eb1m trong gi\u1edbi h\u1ea1n b\u1eadc n\u00e0o, th\u00ec A = Thu\u1ebf lu\u1ef9 ti\u1ebfn b\u1eadc \u0111\u00f3 +\u00a0(A \u2013 Gi\u1edbi h\u1ea1n b\u1eadc tr\u01b0\u1edbc\u00a0\u0111\u00f3) * Thu\u1ebf su\u1ea5t b\u1eadc\u00a0\u0111\u00f3<\/p>\n<\/blockquote>\n

Do ch\u1ec9 c\u00f3 7 b\u1eadc, ta c\u00f3 th\u1ec3 l\u1ed3ng gh\u00e9p 7 h\u00e0m\u00a0IF v\u1edbi c\u00f9ng 1 n\u1ed9i dung tr\u00ean\u00a0\u0111\u1ec3 t\u00ednh.<\/p>\n

\n

=IF1(A<0,0,IF2(A<B\u1eadc 1, A*TS\u00a0b\u1eadc 1,IF3(A < B\u1eadc 2, TLT 2\u00a0+ (A \u2013 B\u1eadc 1)*TS b\u1eadc 2,IF4(A < B\u1eadc 3, TLT3 + (A \u2013 b\u1eadc 2)*TS B\u1eadc 3,IF5(A<B\u1eadc 4, TLT4 + (A \u2013 B\u1eadc 3)*TS B\u1eadc 4,IF6(A<B\u1eadc 5, TLT5\u00a0+ (A \u2013 B\u1eadc4)*TS B\u1eadc\u00a05,IF7(A<B\u1eadc 6, TLT6\u00a0+ (A \u2013 B\u1eadc 5)*TS B\u1eadc\u00a06,TLT7 + (A \u2013 B\u1eadc 6)*TS B\u1eadc\u00a07)))))))<\/p>\n<\/blockquote>\n

L\u01b0u\u00a0\u00fd:<\/p>\n