{"id":6863,"date":"2024-12-21T16:19:23","date_gmt":"2024-12-21T16:19:23","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/xay-dung-bang-tinh-thue-tncn-tren-excel-phan-2\/"},"modified":"2024-12-21T16:19:23","modified_gmt":"2024-12-21T16:19:23","slug":"xay-dung-bang-tinh-thue-tncn-tren-excel-phan-2","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/xay-dung-bang-tinh-thue-tncn-tren-excel-phan-2\/","title":{"rendered":"X\u00c2Y D\u1ef0NG B\u1ea2NG T\u00cdNH THU\u1ebe TNCN TR\u00caN EXCEL \u2013 PH\u1ea6N 2"},"content":{"rendered":"
\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 \u0111au \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 \u0111i\u1ec1u ch\u1ec9nh. B\u00e0i vi\u1ebft n\u00e0y H\u1ecdc Excel Online<\/strong><\/a> s\u1ebd gi\u00fap c\u00e1c b\u1ea1n hi\u1ec3u r\u00f5 c\u00e1ch t\u00ednh, x\u00e2y d\u1ef1ng b\u1ea3ng t\u00ednh thu\u1ebf<\/strong> c\u00f4ng th\u1ee9c chu\u1ea9n tr\u00ean file excel, \u0111\u1ed3ng th\u1eddi c\u00f3 th\u1ec3 tu\u1ef3 bi\u1ebfn theo th\u1ef1c t\u1ebf.<\/em><\/p>\n

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

Ph\u1ea7n 2:\u00a0Ph\u00e2n t\u00edch logic c\u00f4ng th\u1ee9c \u2013 C\u00e1ch l\u00e0m th\u1ee9 1<\/strong><\/p>\n

Nh\u01b0 ph\u00e2n t\u00edch\u00a0\u1edf b\u00e0i 1: Ph\u00e2n t\u00edch b\u1ea3ng thu\u1ebf su\u1ea5t<\/a> , kho\u1ea3n thu nh\u1eadp ch\u1ecbu thu\u1ebf ph\u1ea3i ph\u00e2n chia ra 7 b\u1eadc theo bi\u1ec3u thu\u1ebf lu\u1ef9 ti\u1ebfn, do\u00a0\u0111\u00f3 ta c\u00f3 th\u1ec3 h\u00ecnh dung nh\u01b0 sau:<\/p>\n

<\/p>\n

H\u00ecnh 1: B\u1ea3ng thu\u1ebf su\u1ea5t thu\u1ebf TNCN<\/p>\n

Kho\u1ea3ng lu\u1ef9 ti\u1ebfn (KLT)\u00a0\u0111\u01b0\u1ee3c t\u00ednh b\u1eb1ng Gi\u1edbi h\u1ea1n t\u1ed1i\u00a0\u0111a b\u1eadc\u00a0\u0111\u00f3 \u2013 Gi\u1edbi h\u1ea1n t\u1ed1i\u00a0\u0111a c\u1ee7a b\u1eadc tr\u01b0\u1edbc\u00a0\u0111\u00f3<\/p>\n

1. N\u1ebfu A < 0, t\u1ee9c l\u00e0 Thu nh\u1eadp t\u00ednh thu\u1ebf ch\u01b0a n\u1eb1m trong ph\u1ea1m vi t\u00ednh thu\u1ebf, n\u00ean s\u1ebd coi = 0<\/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

\u00a0<\/p>\n

2. N\u1ebfu A > B\u1eadc 1, s\u1ebd t\u00ednh theo t\u1ed1i\u00a0\u0111a b\u1eadc 1= KLT 1\u00a0* L\u00e3i su\u1ea5t b\u1eadc 1<\/p>\n

c\u00f2n A < B\u1eadc 1, s\u1ebd t\u00ednh l\u00e0 A * L\u00e3i su\u1ea5t b\u1eadc 1<\/p>\n

C\u00f3 th\u1ec3 di\u1ec5n gi\u1ea3i logic tr\u00ean ra c\u00f4ng th\u1ee9c IF trong excel\u00a0l\u00e0:<\/p>\n

\n

Ti\u1ec1n thu\u1ebf b\u1eadc 1 = IF(A<0,0,IF(A-B\u1eadc 1>0,KLT 1 * L\u00e3i su\u1ea5t b\u1eadc 1,A* L\u00e3i su\u1ea5t b\u1eadc 1))<\/p>\n<\/blockquote>\n

3. Tr\u01b0\u1eddng h\u1ee3p A \u2013 B\u1eadc 1 >0, s\u1ebd x\u00e9t ti\u1ebfp sang b\u1eadc 2, A < B\u1eadc 1 s\u1ebd c\u00f3 k\u1ebft qu\u1ea3 l\u00e0 0\u00a0\u1edf b\u1eadc 2<\/p>\n

N\u1ebfu A > B\u1eadc 2, s\u1ebd t\u00ednh theo t\u1ed1i\u00a0\u0111a b\u1eadc 2 = KLT2\u00a0* L\u00e3i su\u1ea5t b\u1eadc 2<\/p>\n

C\u00f2n A < B\u1eadc 2, s\u1ebd t\u00ednh theo (A \u2013 B\u1eadc 1) *\u00a0L\u00e3i su\u1ea5t b\u1eadc 2<\/p>\n

\n

Ti\u1ec1n thu\u1ebf b\u1eadc 2\u00a0=\u00a0IF(A \u2013 B\u1eadc 1 <0,0,IF(A- B\u1eadc 2>0,KLT 2\u00a0* L\u00e3i su\u1ea5t b\u1eadc 2,(A- B\u1eadc 1) * L\u00e3i su\u1ea5t b\u1eadc 2))<\/p>\n<\/blockquote>\n

4. Tr\u01b0\u1eddng h\u1ee3p A \u2013 B\u1eadc 2 >0, s\u1ebd x\u00e9t ti\u1ebfp sang b\u1eadc 3,\u00a0A < B\u1eadc 2\u00a0s\u1ebd c\u00f3 k\u1ebft qu\u1ea3 l\u00e0 0\u00a0\u1edf b\u1eadc\u00a03<\/p>\n

N\u1ebfu A > B\u1eadc 3, s\u1ebd t\u00ednh theo t\u1ed1i\u00a0\u0111a b\u1eadc 3 = KLT\u00a03\u00a0* L\u00e3i su\u1ea5t b\u1eadc\u00a03<\/p>\n

C\u00f2n A < B\u1eadc 3, s\u1ebd t\u00ednh theo (A \u2013 B\u1eadc 2) *\u00a0L\u00e3i su\u1ea5t b\u1eadc\u00a03<\/p>\n

\n

Ti\u1ec1n thu\u1ebf b\u1eadc 3\u00a0=\u00a0IF(A- B\u1eadc 2\u00a0<0,0,IF(A \u2013 B\u1eadc 3>0,KLT 3\u00a0* L\u00e3i su\u1ea5t b\u1eadc 3,(A- B\u1eadc 2) * L\u00e3i su\u1ea5t b\u1eadc 3))<\/p>\n<\/blockquote>\n

L\u00e0m t\u01b0\u01a1ng t\u1ef1 cho c\u00e1c b\u1eadc 4, 5,6<\/p>\n

* L\u01b0u\u00a0\u00fd:\u00a0\u1edf b\u1eadc 7, s\u1ebd l\u00e0 N\u1ebfu A \u2013 B\u1eadc 6 >0, (A \u2013 B\u1eadc 6) * L\u00e3i su\u1ea5t b\u1eadc 7<\/p>\n

\u00a0<\/p>\n

T\u1ed5ng ti\u1ec1n thu\u1ebf s\u1ebd l\u00e0 = Ti\u1ec1n thu\u1ebf b\u1eadc 1 +\u00a0Ti\u1ec1n thu\u1ebf b\u1eadc 2 +\u00a0Ti\u1ec1n thu\u1ebf b\u1eadc 3 + \u2026 +\u00a0Ti\u1ec1n thu\u1ebf b\u1eadc 7<\/p>\n

\"X\u00c2Y
X\u00c2Y D\u1ef0NG B\u1ea2NG T\u00cdNH THU\u1ebe<\/figcaption><\/figure>\n

Nh\u1eadn x\u00e9t:<\/strong><\/p>\n

\u01afu\u00a0\u0111i\u1ec3m: <\/em><\/strong><\/p>\n