{"id":5911,"date":"2024-12-21T11:17:16","date_gmt":"2024-12-21T11:17:16","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/xay-dung-cong-thuc-tinh-bao-cao-chi-phi-tien-luong-theo-bo-phan\/"},"modified":"2024-12-21T11:17:16","modified_gmt":"2024-12-21T11:17:16","slug":"xay-dung-cong-thuc-tinh-bao-cao-chi-phi-tien-luong-theo-bo-phan","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/xay-dung-cong-thuc-tinh-bao-cao-chi-phi-tien-luong-theo-bo-phan\/","title":{"rendered":"X\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c t\u00ednh b\u00e1o c\u00e1o chi ph\u00ed ti\u1ec1n l\u01b0\u01a1ng theo b\u1ed9 ph\u1eadn"},"content":{"rendered":"
\n

Trong c\u00f4ng vi\u1ec7c k\u1ebf to\u00e1n ti\u1ec1n l\u01b0\u01a1ng tr\u00ean excel<\/a> (hay H\u00e0nh ch\u00ednh nh\u00e2n s\u1ef1) \u1edf nh\u1eefng doanh nghi\u1ec7p c\u00f3 nhi\u1ec1u b\u1ed9 ph\u1eadn, ph\u00f2ng ban, ch\u00fang ta c\u00f3 th\u1ec3 ph\u1ea3i l\u1eadp m\u1ed9t b\u00e1o c\u00e1o v\u1ec1 chi ph\u00ed ti\u1ec1n l\u01b0\u01a1ng theo b\u1ed9 ph\u1eadn. C\u00f4ng vi\u1ec7c n\u00e0y tr\u00ean\u00a0excel\u00a0\u0111\u01b0\u1ee3c th\u1ef1c hi\u1ec7n nh\u01b0 th\u1ebf n\u00e0o,\u00a0sau\u00a0\u0111\u00e2y hocexcel.online s\u1ebd h\u01b0\u1edbng d\u1eabn c\u00e1c b\u1ea1n th\u1ef1c hi\u1ec7n theo 2 c\u00e1ch l\u00e0m nh\u00e9:<\/p>\n

\u0110\u1ec1 b\u00e0i: Cho s\u1eb5n n\u1ed9i dung b\u1ea3ng t\u1ed5ng k\u1ebft ti\u1ec1n l\u01b0\u01a1ng h\u00e0ng th\u00e1ng, y\u00eau c\u1ea7u l\u1eadp b\u00e1o c\u00e1o chi ph\u00ed l\u01b0\u01a1ng theo b\u1ed9 ph\u1eadn nh\u01b0 h\u00ecnh sau:<\/p>\n

<\/p>\n

H\u01b0\u1edbng d\u1eabn c\u00e1ch gi\u1ea3i quy\u1ebft:<\/strong><\/p>\n

B\u01b0\u1edbc 1<\/strong>: X\u00e1c\u00a0\u0111\u1ecbnh m\u1ee5c ti\u00eau c\u1ea7n\u00a0\u0111\u1ea1t\u00a0\u0111\u01b0\u1ee3c<\/p>\n

Y\u00eau c\u1ea7u\u00a0\u1edf\u00a0\u0111\u00e2y l\u00e0 t\u00ednh chi ph\u00ed ti\u1ec1n l\u01b0\u01a1ng theo b\u1ed9 ph\u1eadn, theo t\u1eebng th\u00e1ng. V\u00ec v\u1eady\u00a0\u1edf\u00a0\u0111\u00e2y c\u00f3 2\u00a0\u0111i\u1ec1u ki\u1ec7n:<\/p>\n

+ Theo b\u1ed9 ph\u1eadn<\/p>\n

+ Theo th\u00e1ng<\/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
\n

Xem th\u00eam:\u00a0Excel k\u1ebf to\u00e1n<\/a><\/p>\n<\/blockquote>\n

B\u01b0\u1edbc 2<\/strong>: L\u1ef1a ch\u1ecdn c\u00f4ng th\u1ee9c t\u00ednh:<\/p>\n

C\u00e1c c\u00f4ng th\u1ee9c\u00a0excel gi\u00fap t\u00ednh t\u1ed5ng theo \u0111i\u1ec1u ki\u1ec7n g\u1ed3m: SUMIF \/ SUMIFS \/ SUMPRODUCT<\/p>\n

V\u00ec\u00a0\u1edf b\u01b0\u1edbc 1\u00a0\u0111\u00e3 x\u00e1c\u00a0\u0111\u1ecbnh l\u00e0 c\u00f3 2\u00a0\u0111i\u1ec1u ki\u1ec7n, v\u00ec v\u1eady kh\u00f4ng s\u1eed d\u1ee5ng\u00a0\u0111\u01b0\u1ee3c h\u00e0m Sumif, m\u00e0 ch\u1ec9 s\u1eed d\u1ee5ng\u00a0\u0111\u01b0\u1ee3c h\u00e0m SUMIFS ho\u1eb7c SUMPRODUCT<\/p>\n

B\u01b0\u1edbc 3<\/strong>: X\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c t\u00ednh<\/p>\n

C\u00e1ch 1: \u00e1p d\u1ee5ng h\u00e0m SUMIFS<\/em><\/strong><\/p>\n

C\u1ea5u tr\u00fac h\u00e0m SUMIFS<\/a> nh\u01b0 sau:<\/p>\n

\n

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],\u2026)<\/p>\n<\/blockquote>\n

Sum_range l\u00e0 v\u00f9ng t\u00ednh t\u1ed5ng:\u00a0\u1edf\u00a0\u0111\u00e2y l\u00e0 c\u1ed9t \u201cTh\u1ef1c l\u0129nh\u201d<\/p>\n

Criteria_range1: v\u00f9ng ch\u1ee9a\u00a0\u0111i\u1ec1u ki\u1ec7n th\u1ee9 nh\u1ea5t, c\u1ed9t \u201cTh\u00e1ng\u201d<\/p>\n

Criteria1:\u00a0\u0111i\u1ec1u ki\u1ec7n th\u1ee9 1 n\u1eb1m trong v\u00f9ng\u00a0\u0111i\u1ec1u ki\u1ec7n th\u1ee9 1,\u00a0\u1edf\u00a0\u0111\u00e2y l\u00e0 c\u00e1c c\u1ed9t Th\u00e1ng trong B\u00e1o c\u00e1o. Trong c\u1ed9t Th\u00e1ng ch\u1ec9 c\u00f3 c\u00e1c gi\u00e1 tr\u1ecb th\u00e1ng d\u1ea1ng s\u1ed1, n\u00ean \u201cTh\u00e1ng 1\u201d \u0111\u01b0\u1ee3c hi\u1ec3u l\u00e0 s\u1ed1 \u201c1\u201d<\/p>\n

Criteria_range2: v\u00f9ng\u00a0\u0111i\u1ec1u ki\u1ec7n th\u1ee9 2, c\u1ed9t \u201cB\u1ed9 ph\u1eadn\u201d<\/p>\n

Criteria2:\u00a0\u0111i\u1ec1u ki\u1ec7n th\u1ee9c 2, n\u1eb1m trong c\u1ed9t th\u1ee9 2,\u00a0\u1edf\u00a0\u0111\u00e2y l\u00e0 c\u00e1c n\u1ed9i dung v\u1ec1 b\u1ed9 ph\u1eadn trong B\u00e1o c\u00e1o.<\/p>\n

C\u00e1ch vi\u1ebft h\u00e0m SUMIFS nh\u01b0 sau:<\/p>\n

<\/p>\n

\n

=SUMIFS($G$3:$G$26,$A$3:$A$26,1,$C$3:$C$26,$I3)<\/p>\n<\/blockquote>\n

C\u00e1c v\u00f9ng range ph\u1ea3i c\u1ed1\u00a0\u0111\u1ecbnh\u00a0\u0111i\u1ec3m\u00a0\u0111\u1ea7u v\u00e0\u00a0\u0111i\u1ec3m cu\u1ed1i\u00a0\u0111\u1ec3 khi copy c\u00f4ng th\u1ee9c s\u1ebd kh\u00f4ng thay\u00a0\u0111\u1ed5i\u00a0\u0111\u1ecba ch\u1ec9 tham chi\u1ebfu.<\/p>\n

Criteria1 l\u00e0 s\u1ed1 1, khi t\u00ednh cho c\u00e1c th\u00e1ng kh\u00e1c ph\u1ea3i thay\u00a0\u0111\u1ed5i t\u01b0\u01a1ng\u00a0\u1ee9ng<\/p>\n

Criteria2 l\u00e0\u00a0\u00f4 I3 s\u1ebd c\u1ed1\u00a0\u0111\u1ecbnh c\u1ed9t I\u00a0\u0111\u1ec3 khi copy c\u00f4ng th\u1ee9c sang nh\u1eefng\u00a0\u00f4 kh\u00e1c s\u1ebd gi\u1eef nguy\u00ean tham chi\u1ebfu t\u1edbi c\u1ed9t I<\/p>\n

C\u00e1ch 2:\u00a0\u00c1p d\u1ee5ng h\u00e0m\u00a0SUMPRODUCT<\/strong><\/em><\/p>\n

C\u1ea5u tr\u00fac h\u00e0m SUMPRODUCT:<\/p>\n

\n

=SUMPRODUCT(array1, [array2], [array3],\u2026)<\/p>\n<\/blockquote>\n

Xem th\u00eam<\/strong>: Ha\u0300m SUMPRODUCT trong Excel va\u0300 vi\u0301 du\u0323 c\u00f4ng th\u01b0\u0301c<\/a><\/p>\n

Array l\u00e0 c\u00e1c m\u1ea3ng d\u1eef li\u1ec7u.\u00a0\u1ede\u00a0\u0111\u00e2y ta quan t\u00e2m t\u1edbi 3 m\u1ea3ng d\u1eef li\u1ec7u:<\/p>\n