{"id":11293,"date":"2025-01-15T02:27:29","date_gmt":"2025-01-15T02:27:29","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/15\/huong-dan-cach-tinh-so-gio-lam-viec-tren-bang-cham-cong-excel-tu-may-cham-cong\/"},"modified":"2025-01-15T02:27:29","modified_gmt":"2025-01-15T02:27:29","slug":"huong-dan-cach-tinh-so-gio-lam-viec-tren-bang-cham-cong-excel-tu-may-cham-cong","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/15\/huong-dan-cach-tinh-so-gio-lam-viec-tren-bang-cham-cong-excel-tu-may-cham-cong\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn c\u00e1ch t\u00ednh s\u1ed1 gi\u1edd l\u00e0m vi\u1ec7c tr\u00ean b\u1ea3ng ch\u1ea5m c\u00f4ng Excel t\u1eeb m\u00e1y ch\u1ea5m c\u00f4ng"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Kh\u00f4ng ph\u1ea3i l\u00fac n\u00e0o vi\u1ec7c ch\u1ea5m c\u00f4ng tr\u00ean m\u00e1y ch\u1ea5m c\u00f4ng c\u0169ng\u00a0\u0111em l\u1ea1i hi\u1ec7u qu\u1ea3 ngay. Trong m\u1ed9t s\u1ed1 tr\u01b0\u1eddng h\u1ee3p ch\u00fang ta ph\u1ea3i\u00a0\u0111\u01b0a d\u1eef li\u1ec7u t\u1eeb m\u00e1y ch\u1ea5m c\u00f4ng sang\u00a0Excel th\u00ec m\u1edbi t\u00ednh\u00a0\u0111\u01b0\u1ee3c s\u1ed1 gi\u1edd c\u00f4ng l\u00e0m vi\u1ec7c c\u1ee7a nh\u00e2n vi\u00ean. Trong b\u00e0i h\u1ecdc n\u00e0y H\u1ecdc\u00a0Excel Online s\u1ebd h\u01b0\u1edbng d\u1eabn c\u00e1c b\u1ea1n c\u00e1ch t\u00ednh s\u1ed1 gi\u1edd l\u00e0m vi\u1ec7c tr\u00ean b\u1ea3ng ch\u1ea5m c\u00f4ng\u00a0Excel v\u1edbi d\u1eef li\u1ec7u\u00a0\u0111\u01b0\u1ee3c tr\u00edch xu\u1ea5t t\u1eeb m\u00e1y ch\u1ea5m c\u00f4ng. N\u1ed9i dung c\u1ee5 th\u1ec3 nh\u01b0 sau:<\/p>\n<p>Ch\u00fang ta c\u00f3 b\u1ea3ng d\u1eef li\u1ec7u tr\u00edch xu\u1ea5t t\u1eeb m\u00e1y ch\u1ea5m c\u00f4ng th\u00e1ng 07 n\u0103m 2017 g\u1ed3m:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-8386\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/bcc_kx.png\" width=\"1364\" height=\"496\" title=\"bcc_kx\"><\/p>\n<p>Y\u00eau c\u1ea7u: D\u1ef1a v\u00e0o s\u1ed1 gi\u1edd v\u00e0o, gi\u1edd ra theo t\u1eebng ng\u00e0y c\u1ee7a t\u1eebng nh\u00e2n vi\u00ean, h\u00e3y t\u00ednh s\u1ed1 gi\u1edd l\u00e0m vi\u1ec7c, s\u1ed1 l\u1ea7n\u00a0\u0111i mu\u1ed9n c\u1ee7a c\u00e1c nh\u00e2n vi\u00ean.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_61 counter-hierarchy ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Xem nhanh<\/p>\n<p><label for=\"ez-toc-cssicon-toggle-item-67871d1104614\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><\/span><\/span><\/label><\/p>\n<nav>\n<ul class=\"ez-toc-list ez-toc-list-level-1 \">\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/huong-dan-cach-tinh-so-gio-lam-viec-tren-bang-cham-cong-excel-tu-may-cham-cong.html#Cach_lam\" title=\"C\u00e1ch l\u00e0m\">C\u00e1ch l\u00e0m<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/huong-dan-cach-tinh-so-gio-lam-viec-tren-bang-cham-cong-excel-tu-may-cham-cong.html#Cach_toi_uu\" title=\"C\u00e1ch t\u1ed1i\u00a0\u01b0u\">C\u00e1ch t\u1ed1i\u00a0\u01b0u<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Cach_lam\"><\/span>C\u00e1ch l\u00e0m<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong>B\u01b0\u1edbc 1: Kh\u1ea3o s\u00e1t,\u00a0\u0111\u00e1nh gi\u00e1 v\u00e0 ph\u00e2n t\u00edch d\u1eef li\u1ec7u ngu\u1ed3n (b\u1ea3ng d\u1eef li\u1ec7u k\u1ebft xu\u1ea5t t\u1eeb m\u00e1y ch\u1ea5m c\u00f4ng ra\u00a0excel)<\/strong><\/p>\n<p>\u0110\u00e2y l\u00e0 b\u01b0\u1edbc r\u1ea5t quan tr\u1ecdng, Vi\u1ec7c ph\u00e2n t\u00edch b\u1ea3ng d\u1eef li\u1ec7u ngu\u1ed3n s\u1ebd gi\u00fap ch\u00fang ta c\u00f3 ph\u01b0\u01a1ng\u00a0\u00e1n x\u1eed l\u00fd ph\u00f9 h\u1ee3p v\u00e0 ch\u00ednh x\u00e1c.<\/p>\n<p>\u0110\u1ec3 th\u1ef1c hi\u1ec7n c\u00f4ng vi\u1ec7c n\u00e0y, ch\u00fang ta c\u1ea7n n\u1eafm\u00a0\u0111\u01b0\u1ee3c c\u00e1c nguy\u00ean t\u1eafc sau:<\/p>\n<div class=\"code-block code-block-3\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<p>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<p><a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/ex101_92019-nho-281.gif\" title=\"ex101_92019-nho-281\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<div class=\"code-block code-block-1\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/ex101_92019-nho-281.gif\" title=\"ex101_92019-nho-281\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<ul>\n<li>M\u1ee5c\u00a0\u0111\u00edch c\u00f4ng vi\u1ec7c: T\u00ednh c\u00f4ng l\u00e0m vi\u1ec7c theo th\u1eddi gian<\/li>\n<li>\u0110\u1ed1i t\u01b0\u1ee3ng li\u00ean quan: M\u00e3 nh\u00e2n vi\u00ean, Ng\u00e0y trong th\u00e1ng, Gi\u1edd v\u00e0o, Gi\u1edd ra<\/li>\n<li>Ti\u00eau ch\u00ed\u00a0\u0111\u00e1nh gi\u00e1 (d\u1ef1a tr\u00ean m\u1ee5c\u00a0\u0111\u00edch, k\u1ebft qu\u1ea3 c\u1ea7n\u00a0\u0111\u1ea1t\u00a0\u0111\u01b0\u1ee3c\u00a0\u0111\u1ec3\u00a0\u0111\u1eb7t ra ti\u00eau ch\u00ed cho c\u00e1c\u00a0\u0111\u1ed1i t\u01b0\u1ee3ng li\u00ean quan)<\/li>\n<\/ul>\n<ul>\n<li>\u00a0 \u00a0M\u00e3 Nh\u00e2n vi\u00ean: Li\u00ean t\u1ee5c theo d\u00f2ng, kh\u00f4ng\u00a0\u0111\u01b0\u1ee3c\u00a0\u0111\u1ec3 d\u00f2ng tr\u1ed1ng<\/li>\n<li>\u00a0 \u00a0Ng\u00e0y trong th\u00e1ng: Bao g\u1ed3m x\u00e1c\u00a0\u0111\u1ecbnh ng\u00e0y trong th\u00e1ng, th\u1ee9 trong tu\u1ea7n, ph\u00e2n bi\u1ec7t ng\u00e0y ngh\u1ec9 l\u1ec5, ngh\u1ec9 b\u00f9<\/li>\n<li>\u00a0 \u00a0Gi\u1edd v\u00e0o, gi\u1edd ra: C\u00e1c ng\u00e0y l\u00e0m vi\u1ec7c\u00a0\u0111\u1ec1u ph\u1ea3i c\u00f3 gi\u1edd v\u00e0o, gi\u1edd ra. Khi t\u00ednh c\u00f4ng s\u1ebd d\u1ef1a tr\u00ean vi\u1ec7c\u00a0\u0111\u00e1nh gi\u00e1 gi\u1edd v\u00e0o, gi\u1edd ra\u00a0\u0111\u1ec3 t\u00ednh ra c\u00e1c lo\u1ea1i c\u00f4ng.<\/li>\n<\/ul>\n<p>Ta c\u00f3 th\u1ec3 th\u1ea5y,\u00a0\u0111\u1ec3 t\u00ednh to\u00e1n\u00a0\u0111\u01b0\u1ee3c, c\u1ea7n ph\u1ea3i\u00a0\u0111i\u1ec1u ch\u1ec9nh l\u1ea1i c\u1ea5u tr\u00fac b\u1ea3ng d\u1eef li\u1ec7u sao cho\u00a0\u0111\u00fang c\u00e1c ti\u00eau ch\u00ed.<\/p>\n<p>Vi\u1ec7c t\u00ednh to\u00e1n tr\u00ean nhi\u1ec1u\u00a0\u0111i\u1ec1u ki\u1ec7n, n\u00ean h\u00e0m s\u1ebd s\u1eed d\u1ee5ng\u00a0\u0111\u1ec3 t\u00ednh to\u00e1n l\u00e0 SUMIFS, COUNTIFS\u00a0v\u00e0 h\u00e0m\u00a0IF<\/p>\n<p><strong>B\u01b0\u1edbc 2: C\u1ea5u tr\u00fac l\u1ea1i b\u1ea3ng d\u1eef li\u1ec7u<\/strong><\/p>\n<p>\u0110\u1ec3 r\u00fat g\u1ecdn b\u00e0i vi\u1ebft, m\u00ecnh s\u1ebd kh\u00f4ng n\u00eau t\u1eebng thao t\u00e1c, m\u00e0 s\u1ebd\u00a0\u0111\u01b0a h\u00ecnh m\u1eabu v\u00e0 g\u1ee3i\u00a0\u00fd c\u00e1c c\u00e1ch l\u00e0m\u00a0\u0111\u1ec3 c\u00e1c b\u1ea1n c\u00f3 th\u1ec3 t\u1ef1 l\u00e0m\u00a0\u0111\u01b0\u1ee3c:<\/p>\n<p>(B\u1ea3ng ch\u1ea5m c\u00f4ng n\u00e0y c\u00f3 c\u1ea5u tr\u00fac m\u1ed7i ng\u00e0y g\u1ed3m 2 c\u1ed9t, kh\u00e1c b\u00e0i tr\u01b0\u1edbc l\u00e0 m\u1ed7i ng\u00e0y 1 c\u1ed9t)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-8395 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/bcc_cau-truc-01-1.png\" width=\"1093\" height=\"439\" title=\"bcc_cau-truc-01-1\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-8396 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/bcc_cau-truc-02-1.png\" width=\"1012\" height=\"415\" title=\"bcc_cau-truc-02-1\"><\/p>\n<ul>\n<li>Ph\u1ea7n Th\u00e1ng v\u00e0 N\u0103m (<strong><em>d\u00f2ng 2, 3<\/em><\/strong>):\u00a0\u0110\u1eb7t b\u00ean ngo\u00e0i\u00a0\u0111\u1ec3 ti\u1ec7n vi\u1ec7c thay\u00a0\u0111\u1ed5i<\/li>\n<li>Ng\u00e0y trong th\u00e1ng (<em><strong>d\u00f2ng 4<\/strong><\/em>): D\u00f9ng h\u00e0m Date, b\u1eaft\u00a0\u0111\u1ea7u v\u1edbi ng\u00e0y 26 th\u00e1ng tr\u01b0\u1edbc\u00a0\u0111\u00f3 (v\u00ec b\u1ea3ng nguy\u00ean li\u1ec7u k\u1ebft xu\u1ea5t t\u00ednh t\u1eeb ng\u00e0y 26 n\u00ean ta s\u1ebd gi\u1eef nguy\u00ean y\u00eau c\u1ea7u n\u00e0y. T\u00ednh c\u00f4ng t\u1eeb ng\u00e0y 26 th\u00e1ng tr\u01b0\u1edbc\u00a0\u0111\u1ebfn ng\u00e0y 25 th\u00e1ng sau). Ri\u00eang 2 ng\u00e0y cu\u1ed1i c\u00f3 th\u1ec3 sang th\u00e1ng ti\u1ebfp theo (tr\u01b0\u1eddng h\u1ee3p th\u00e1ng c\u00f3 28-29 ng\u00e0y) n\u00ean c\u00f3 th\u1ec3 x\u1eed l\u00fd\u00a0\u0111\u1ec3 b\u1ecf tr\u1ed1ng n\u1ebfu sang th\u00e1ng sau, t\u1ee9c l\u00e0 ng\u00e0y 27 th\u00e1ng n\u00e0y\u00a0tr\u1edf\u00a0\u0111i l\u00e0\u00a0\u0111\u00e3 b\u01b0\u1edbc sang th\u00e1ng sau c\u1ee7a b\u1ea3ng t\u00ednh c\u00f4ng.<\/li>\n<li>Ph\u1ea7n M\u00e3 NV (<strong><em>c\u1ed9t A, B, C<\/em><\/strong>): M\u1ed7i m\u00e3 1 d\u00f2ng,\u00a0\u0111\u1ec3 t\u1eadp h\u1ee3p theo m\u00e3 NV ch\u00ednh x\u00e1c h\u01a1n.<\/li>\n<li>Th\u1ee9 trong tu\u1ea7n (<strong><em>d\u00f2ng 5<\/em><\/strong>): D\u00f9ng h\u00e0m Choose k\u1ebft h\u1ee3p v\u1edbi h\u00e0m\u00a0Weekday\u00a0\u0111\u1ec3 x\u00e1c\u00a0\u0111\u1ecbnh. K\u1ebft h\u1ee3p t\u00ednh n\u0103ng Conditional Formatting\u00a0\u0111\u1ec3 t\u00f4 m\u00e0u ph\u00e2n bi\u1ec7t ng\u00e0y CN v\u1edbi ng\u00e0y th\u01b0\u1eddng.<\/li>\n<li>Gi\u1edd v\u00e0o, gi\u1edd ra (<strong><em>d\u00f2ng 6<\/em><\/strong>): c\u00f3 th\u1ec3 quy\u00a0\u01b0\u1edbc s\u1ed1 1 l\u00e0 gi\u1edd v\u00e0o, s\u1ed1 2 l\u00e0 gi\u1edd ra.<\/li>\n<li><em>L\u01b0u\u00a0\u00fd: 1 ng\u00e0y c\u00f3 2 gi\u1edd, n\u00ean 2 c\u1ed9t s\u1ebd t\u00ednh cho 1 ng\u00e0y. V\u00ec v\u1eady khi x\u1eed l\u00fd ng\u00e0y\u00a0\u1edf d\u00f2ng 4 c\u1ea7n ch\u00fa\u00a0\u00fd n\u1ed9i dung n\u00e0y.<\/em><\/li>\n<\/ul>\n<p>T\u1ea5t c\u1ea3 nh\u1eefng n\u1ed9i dung tr\u00ean, m\u00ecnh\u00a0\u0111\u00e3 h\u01b0\u1edbng d\u1eabn chi ti\u1ebft trong b\u00e0i:\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/tao-bang-cham-cong-tren-excel.html\">T\u1ea1o b\u1ea3ng ch\u1ea5m c\u00f4ng tr\u00ean\u00a0excel<\/a>. Vui l\u00f2ng xem l\u1ea1i b\u00e0i n\u00e0y\u00a0\u0111\u1ec3\u00a0\u0111\u01b0\u1ee3c h\u01b0\u1edbng d\u1eabn chi ti\u1ebft.<\/p>\n<p><strong>B\u01b0\u1edbc 3:\u00a0L\u1ecdc v\u00e0 l\u1ea5y d\u1eef li\u1ec7u\u00a0<\/strong><\/p>\n<p>T\u1ea1i b\u1ea3ng k\u1ebft xu\u1ea5t, d\u00f9ng ch\u1ee9c n\u0103ng Data\/Filter\u00a0\u0111\u1ec3 l\u1ecdc b\u1ecf d\u00f2ng tr\u1ed1ng trong c\u1ed9t M\u00e3 NV, sau\u00a0\u0111\u00f3 copy d\u1eef li\u1ec7u sang b\u1ea3ng x\u1eed l\u00fd. S\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng Paste Special\/Value\u00a0\u0111\u1ec3 d\u00e1n d\u1eef li\u1ec7u b\u1ecf qua d\u00f2ng b\u1ecb\u00a0\u1ea9n.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-8397 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/bcc_loc-kx-01-1.png\" width=\"1203\" height=\"290\" title=\"bcc_loc-kx-01-1\"><\/p>\n<p>B\u1ea3ng k\u1ebft xu\u1ea5t s\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng l\u1ecdc (Data Filter)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-8398 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/bcc_loc-kx-02-1.png\" width=\"1097\" height=\"457\" title=\"bcc_loc-kx-02-1\"><\/p>\n<p>D\u1eef li\u1ec7u ch\u1ea5m c\u00f4ng \u1edf b\u1ea3ng k\u1ebft xu\u1ea5t =&gt; Copy \/ Paste Special -&gt; Value =&gt; Sang b\u1ea3ng x\u1eed l\u00fd<\/p>\n<p><strong>B\u01b0\u1edbc 4: T\u00ednh gi\u1edd c\u00f4ng trong ng\u00e0y<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-8399 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/bcc_xuly-01-1.png\" width=\"1528\" height=\"442\" title=\"bcc_xuly-01-1\"><\/p>\n<p>T\u1ea1o th\u00eam 1 v\u00f9ng, trong\u00a0\u0111\u00f3 m\u1ed7i ng\u00e0y ch\u1ec9 t\u01b0\u01a1ng\u00a0\u1ee9ng 1 c\u1ed9t. C\u00e1ch x\u1eed l\u00fd ng\u00e0y, th\u1ee9 gi\u1ed1ng v\u1edbi ph\u1ea7n tr\u01b0\u1edbc.<\/p>\n<p>=&gt; M\u1ee5c\u00a0\u0111\u00edch: T\u00ednh s\u1ed1 gi\u1edd c\u00f4ng l\u00e0m vi\u1ec7c theo t\u1eebng ng\u00e0y<\/p>\n<p>Gi\u1edd c\u00f4ng l\u00e0m vi\u1ec7c trong ng\u00e0y = Gi\u1edd ra \u2013 Gi\u1edd v\u00e0o<\/p>\n<p>Gi\u1edd ra = (T\u1ed5ng s\u1ed1 ph\u00fat quy\u00a0\u01b0\u1edbc t\u1ea1i th\u1eddi\u00a0\u0111i\u1ec3m ch\u1ea5m c\u00f4ng ra = S\u1ed1 gi\u1edd * 60 + S\u1ed1 ph\u00fat)\/60<\/p>\n<p>Gi\u1edd v\u00e0o =\u00a0\u00a0(T\u1ed5ng s\u1ed1 ph\u00fat quy\u00a0\u01b0\u1edbc t\u1ea1i th\u1eddi\u00a0\u0111i\u1ec3m ch\u1ea5m c\u00f4ng v\u00e0o\u00a0= S\u1ed1 gi\u1edd * 60 + S\u1ed1 ph\u00fat)\/60<\/p>\n<blockquote>\n<p>BN7 =ROUND(((HOUR(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,2))*60+MINUTE(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,2)))-(HOUR(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,1))*60+MINUTE(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,1))))\/60,2)<\/p>\n<\/blockquote>\n<p>B\u1ea1n c\u00f3 th\u1ec3 filldown, fillright c\u00f4ng th\u1ee9c t\u1eeb BN7 cho c\u00e1c\u00a0\u00f4 kh\u00e1c.<\/p>\n<p>* L\u01b0u\u00a0\u00fd:<\/p>\n<p>C\u00f3 th\u1ec3 c\u00f3 s\u1ed1\u00a0\u00e2m. S\u1ed1\u00a0\u00e2m l\u00e0 tr\u01b0\u1eddng h\u1ee3p Ch\u1ec9 c\u00f3 gi\u1edd v\u00e0o m\u00e0 kh\u00f4ng c\u00f3 gi\u1edd ra =&gt; L\u00fd do: Qu\u00ean ch\u1ea5m c\u00f4ng. Vi\u1ec7c n\u00e0y th\u01b0\u1eddng x\u1ea3y ra<\/p>\n<p><strong>B\u01b0\u1edbc 5:\u00a0X\u00e1c\u00a0\u0111\u1ecbnh k\u1ebft qu\u1ea3 ch\u1ea5m c\u00f4ng<\/strong><\/p>\n<p>V\u00ed d\u1ee5 ch\u00fang ta c\u1ea7n 1 s\u1ed1 k\u1ebft qu\u1ea3 ch\u1ea5m c\u00f4ng nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-8400 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/bcc_kq-01.png\" width=\"618\" height=\"454\" title=\"bcc_kq-01\"><\/p>\n<p>Ta c\u00f3 th\u1ec3 th\u1ea5y:<\/p>\n<ul>\n<li>Ng\u00e0y c\u00f4ng\u00a0\u0111\u1ee7 8h t\u00ednh d\u1ef1a tr\u00ean v\u00f9ng\u00a0\u0111\u00e3 x\u00e1c\u00a0\u0111\u1ecbnh c\u1ee5 th\u1ec3 s\u1ed1 gi\u1edd c\u00f4ng, v\u1edbi s\u1ed1 gi\u1edd l\u00e0m &gt;=8\u00a0(m\u1ee5c 2.4)<\/li>\n<\/ul>\n<p style=\"padding-left: 60px\">CS7 = COUNTIF(BN7:CR7,\u201d&gt;=\u201d&amp;8)<\/p>\n<ul>\n<li>Qu\u00ean ch\u1ea5m c\u00f4ng t\u00ednh d\u1ef1a tr\u00ean v\u00f9ng\u00a0\u0111\u00e3 x\u00e1c\u00a0\u0111\u1ecbnh s\u1ed1 gi\u1edd c\u00f4ng, v\u1edbi s\u1ed1 gi\u1edd &lt;0 (m\u1ee5c 2.4)<\/li>\n<\/ul>\n<p style=\"padding-left: 60px\">CT7 =\u00a0COUNTIFS(BN7:CR7,\u201d&lt;\u201c&amp;0)<\/p>\n<ul>\n<li>T\u1ed5ng s\u1ed1 ng\u00e0y c\u00f4ng =\u00a0\u0110\u1ee7 8h + Qu\u00ean ch\u1ea5m c\u00f4ng. V\u00ec qu\u00ean ch\u1ea5m c\u00f4ng c\u00f3 th\u1ec3 v\u1eabn\u00a0\u0111\u01b0\u1ee3c t\u00ednh l\u00e0 1 ng\u00e0y c\u00f4ng,<\/li>\n<li>ho\u1eb7c \u00a0T\u1ed5ng s\u1ed1 ng\u00e0y c\u00f4ng =\u00a0\u0110\u1ee7 8h + (Qu\u00ean ch\u1ea5m c\u00f4ng \/2) n\u1ebfu quy\u00a0\u01b0\u1edbc qu\u00ean ch\u1ea5m c\u00f4ng s\u1ebd coi l\u00e0 n\u1eeda ng\u00e0y c\u00f4ng.<\/li>\n<\/ul>\n<p style=\"padding-left: 60px\">CU7 =\u00a0CS7+CT7 \u00a0 ho\u1eb7c \u00a0CU7 =\u00a0CS7+(CT7\/2)<\/p>\n<ul>\n<li>Ng\u00e0y ngh\u1ec9 = (T\u1ed5ng s\u1ed1 ng\u00e0y c\u00f3 c\u00f4ng = 0) \u2013 (S\u1ed1 ng\u00e0y ngh\u1ec9 tu\u1ea7n = ch\u1ee7 nh\u1eadt) \u00a0 \u00a0Tr\u01b0\u1eddng h\u1ee3p ng\u00e0y ngh\u1ec9 tu\u1ea7n l\u00e0 ch\u1ee7 nh\u1eadt. N\u1ebfu ngh\u1ec9 c\u1ea3 T7, CN th\u00ec s\u1ebd tr\u1eeb th\u00eam s\u1ed1 ng\u00e0y th\u1ee9 7 trong th\u00e1ng<\/li>\n<\/ul>\n<p style=\"padding-left: 60px\">CV7 = IF(B7=\u201d\u201d,0,COUNTIF(BN7:CR7,0)-COUNTIF(BN$5:CR$5,\u201dCN\u201d)-COUNTIF(BN$4:CR$4,\u201d\u201d))<\/p>\n<ul>\n<li>\u0110i mu\u1ed9n: C\u0103n c\u1ee9 v\u00e0o gi\u1edd v\u00e0o l\u00e0m, so s\u00e1nh v\u1edbi ch\u1ec9 ti\u00eau 8h (tu\u1ef3 quy\u00a0\u01b0\u1edbc gi\u1edd v\u00e0o l\u00e0m c\u1ee7a\u00a0\u0111\u01a1n v\u1ecb)<\/li>\n<\/ul>\n<p style=\"padding-left: 60px\">Mu\u1ed9n 5\u2032 =&gt; CW7 =\u00a0COUNTIFS($D7:$BM7,\u201d&gt;\u201d&amp;\u201d8:05\u2033,$D7:$BM7,\u201d&lt;\u201c&amp;\u201d8:10\u2033,$D$6:$BM$6,1)<\/p>\n<p style=\"padding-left: 60px\">Mu\u1ed9n 10\u2032 =&gt; CX7 =\u00a0COUNTIFS($D7:$BM7,\u201d&gt;\u201d&amp;\u201d8:10\u2033,$D7:$BM7,\u201d&lt;\u201c&amp;\u201d8:15\u2033,$D$6:$BM$6,1)<\/p>\n<p style=\"padding-left: 60px\">Mu\u1ed9n tr\u00ean 15\u2032 =&gt; CY7 =\u00a0COUNTIFS($D7:$BM7,\u201d&gt;\u201d&amp;\u201d8:15\u2033,$D$6:$BM$6,1)<\/p>\n<p>=&gt; B\u1ea1n c\u00f3 th\u1ec3 filldown c\u00f4ng th\u1ee9c xu\u1ed1ng cho c\u00e1c m\u00e3 NV ti\u1ebfp theo<\/p>\n<p>K\u1ebft qu\u1ea3 c\u1ee7a m\u1ee5c 2.5 l\u00e0 k\u1ebft qu\u1ea3 cu\u1ed1i c\u00f9ng, c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng\u00a0\u0111\u1ec3 t\u00ednh l\u01b0\u01a1ng cho NV<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_toi_uu\"><\/span><span id=\"3_Toiuu_bang_xu_ly\">C\u00e1ch t\u1ed1i\u00a0\u01b0u<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>M\u00ecnh s\u1ebd g\u1ee3i\u00a0\u00fd m\u1ed9t v\u00e0i k\u1ef9 thu\u1eadt gi\u00fap t\u1ed1i\u00a0\u01b0u ho\u00e1 b\u1ea3ng x\u1eed l\u00fd:\u00a0(\u01afu ti\u00ean cho nh\u1eefng b\u1ea1n bi\u1ebft s\u1eed d\u1ee5ng VBA, n\u1ebfu kh\u00f4ng bi\u1ebft VBA th\u00ec l\u00e0m th\u1ee7 c\u00f4ng b\u1eb1ng tay)<\/p>\n<ul>\n<li>S\u1eed d\u1ee5ng VBA \u0111\u1ec3 l\u01b0u th\u00f4ng tin x\u1eed l\u00fd v\u00e0 k\u1ebft qu\u1ea3 t\u00ednh\u00a0\u0111\u01b0\u1ee3c sang 1 Sheet ri\u00eang\u00a0\u0111\u1ec3 l\u01b0u tr\u1eef k\u1ebft qu\u1ea3. B\u1ea3ng x\u1eed l\u00fd c\u00f3 th\u1ec3 l\u00e0m ti\u1ebfp cho c\u00e1c th\u00e1ng sau m\u00e0 kh\u00f4ng\u00a0\u1ea3nh h\u01b0\u1edfng t\u1edbi k\u1ebft qu\u1ea3 th\u00e1ng tr\u01b0\u1edbc.<\/li>\n<li>S\u1eed d\u1ee5ng VBA\u00a0\u1ea9n\/hi\u1ec7n c\u00e1c v\u00f9ng: D\u1eef li\u1ec7u ch\u1ea5m c\u00f4ng theo gi\u1edd v\u00e0o-ra (c\u1ed9t D \u0111\u1ebfn c\u1ed9t BM), D\u1eef li\u1ec7u x\u1eed l\u00fd t\u00ednh gi\u1edd l\u00e0m vi\u1ec7c (C\u1ed9t BN\u00a0\u0111\u1ebfn c\u1ed9t CR)\u00a0\u0111\u1ec3 l\u00e0m g\u1ecdn b\u1ea3ng x\u1eed l\u00fd. Khi c\u1ea7n l\u00e0m vi\u1ec7c v\u1edbi b\u1ea3ng n\u00e0o th\u00ec ta m\u1edf\u00a0\u1ea9n v\u00f9ng b\u1ea3ng\u00a0\u0111\u00f3 ra. Nh\u01b0 v\u1eady s\u1ebd g\u1ecdn m\u1eaft h\u01a1n. V\u00ec m\u1ee5c ti\u00eau cu\u1ed1i c\u00f9ng l\u00e0 k\u1ebft qu\u1ea3 t\u00ednh c\u00f4ng m\u00e0 th\u00f4i.<\/li>\n<\/ul>\n<p>C\u00f4ng vi\u1ec7c ch\u1ea5m c\u00f4ng, t\u00ednh l\u01b0\u01a1ng kh\u00e1 ph\u1ee9c t\u1ea1p v\u00e0\u00a0\u0111a d\u1ea1ng.\u00a0\u0110\u1ec3 t\u00ecm hi\u1ec3u th\u00eam nhi\u1ec1u ki\u1ebfn th\u1ee9c li\u00ean quan t\u1edbi c\u00f4ng vi\u1ec7c n\u00e0y, H\u1ecdc\u00a0Excel Online xin gi\u1edbi thi\u1ec7u v\u1edbi c\u00e1c b\u1ea1n kh\u00f3a h\u1ecdc \u201c\u1ee8ng d\u1ee5ng Excel VBA trong c\u00f4ng vi\u1ec7c Ti\u1ec1n l\u01b0\u01a1ng \u2013 nh\u00e2n s\u1ef1\u201d.\u00a0\u00a0H\u1ecdc xong kh\u00f3a h\u1ecdc n\u00e0y b\u1ea1n c\u00f3 th\u1ec3 n\u1eafm v\u1eefng\u00a0\u0111\u01b0\u1ee3c quy tr\u00ecnh ch\u1ea5m c\u00f4ng, t\u00ednh l\u01b0\u01a1ng trong doanh nghi\u1ec7p, t\u1ef1 tay x\u00e2y d\u1ef1ng 1 ph\u1ea7n m\u1ec1m qu\u1ea3n l\u00fd lao\u00a0\u0111\u1ed9ng, ch\u1ea5m c\u00f4ng t\u00ednh l\u01b0\u01a1ng tr\u00ean\u00a0Excel. Chi ti\u1ebft xem t\u1ea1i:<\/p>\n<p>\u00a0<\/p>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<p>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<p><a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/ex101_92019-nho-281.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":11294,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-11293","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-co-ban"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/11293","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/comments?post=11293"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/11293\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/11294"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=11293"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=11293"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=11293"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}