{"id":10754,"date":"2025-01-14T23:31:42","date_gmt":"2025-01-14T23:31:42","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/14\/xay-dung-cong-thuc-tinh-cong-ca-dem-tu-du-lieu-may-cham-cong\/"},"modified":"2025-01-14T23:31:42","modified_gmt":"2025-01-14T23:31:42","slug":"xay-dung-cong-thuc-tinh-cong-ca-dem-tu-du-lieu-may-cham-cong","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/14\/xay-dung-cong-thuc-tinh-cong-ca-dem-tu-du-lieu-may-cham-cong\/","title":{"rendered":"X\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c t\u00ednh c\u00f4ng ca \u0111\u00eam t\u1eeb d\u1eef li\u1ec7u M\u00e1y ch\u1ea5m c\u00f4ng"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Khi th\u1ef1c hi\u1ec7n c\u00f4ng vi\u1ec7c ch\u1ea5m c\u00f4ng tr\u00ean\u00a0Excel, ch\u00fang ta th\u01b0\u1eddng ngh\u0129 t\u1edbi nh\u1eefng b\u1ea3ng ch\u1ea5m c\u00f4ng\u00a0\u0111\u01a1n gi\u1ea3n nh\u01b0 th\u1ebf n\u00e0y:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12901 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/pic01-7.png\" width=\"1594\" height=\"248\" title=\"pic01-7\"><\/p>\n<p>Nh\u01b0ng th\u1ef1c t\u1ebf l\u1ea1i kh\u00f4ng\u00a0\u0111\u01a1n gi\u1ea3n nh\u01b0 v\u1eady:<\/p>\n<ul>\n<li>H\u00ecnh th\u1ee9c ch\u1ea5m c\u00f4ng b\u1eb1ng tay th\u01b0\u1eddng\u00a0\u00edt s\u1eed d\u1ee5ng, h\u1ea7u h\u1ebft d\u00f9ng m\u00e1y ch\u1ea5m c\u00f4ng<\/li>\n<li>Doanh nghi\u1ec7p c\u00f3 nhi\u1ec1u h\u00ecnh th\u1ee9c ho\u1ea1t\u00a0\u0111\u1ed9ng, nhi\u1ec1u b\u1ed9 ph\u1eadn, n\u00ean c\u00f3 nhi\u1ec1u n\u1ed9i dung ch\u1ea5m c\u00f4ng kh\u00e1c nh\u01b0: C\u00f4ng s\u1ea3n xu\u1ea5t, c\u00f4ng l\u00e0m th\u00eam, l\u00e0m ban\u00a0\u0111\u00eam, ca 3\u2026<\/li>\n<li>D\u1eef li\u1ec7u t\u1eeb m\u00e1y ch\u1ea5m c\u00f4ng kh\u00f4ng s\u1eed d\u1ee5ng tr\u1ef1c ti\u1ebfp\u00a0\u0111\u1ec3 t\u00ednh l\u01b0\u01a1ng\u00a0\u0111\u01b0\u1ee3c, m\u00e0 c\u1ea7n ph\u1ea3i x\u1eed l\u00fd l\u1ea1i tr\u00ean\u00a0Excel\u00a0\u0111\u1ec3 t\u00ednh to\u00e1n,\u00a0\u0111i\u1ec1u ch\u1ec9nh<\/li>\n<\/ul>\n<p>Trong b\u00e0i vi\u1ebft n\u00e0y, H\u1ecdc\u00a0Excel Online xin tr\u00edch d\u1eabn m\u1ed9t y\u00eau c\u1ea7u t\u1eeb th\u1ef1c t\u1ebf m\u00e0 r\u1ea5t nhi\u1ec1u b\u1ea1n\u00a0\u0111\u00e3 g\u1eb7p ph\u1ea3i nh\u01b0ng ch\u01b0a bi\u1ebft c\u00e1ch gi\u1ea3i quy\u1ebft,\u00a0\u0111\u1ec3 ch\u00fang ta bi\u1ebft th\u00eam v\u1ec1 th\u1ef1c t\u1ebf c\u00f4ng vi\u1ec7c v\u00e0 c\u00e1ch x\u1eed l\u00fd c\u00f4ng vi\u1ec7c\u00a0\u0111\u00f3 tr\u00ean\u00a0excel. Y\u00eau c\u1ea7u\u00a0\u0111\u00f3 nh\u01b0 sau:<\/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-6786f3ddc30ef\" 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\/xay-dung-cong-thuc-tinh-cong-ca-dem-tu-du-lieu-may-cham-cong.html#De_bai\" title=\"\u0110\u1ec1 b\u00e0i\">\u0110\u1ec1 b\u00e0i<\/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\/xay-dung-cong-thuc-tinh-cong-ca-dem-tu-du-lieu-may-cham-cong.html#Cach_lam\" title=\"C\u00e1ch l\u00e0m\">C\u00e1ch l\u00e0m<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/xay-dung-cong-thuc-tinh-cong-ca-dem-tu-du-lieu-may-cham-cong.html#Buoc_1_Phan_tich_yeu_cau_de_bai\" title=\"B\u01b0\u1edbc 1: Ph\u00e2n t\u00edch y\u00eau c\u1ea7u\u00a0\u0111\u1ec1 b\u00e0i\">B\u01b0\u1edbc 1: Ph\u00e2n t\u00edch y\u00eau c\u1ea7u\u00a0\u0111\u1ec1 b\u00e0i<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/xay-dung-cong-thuc-tinh-cong-ca-dem-tu-du-lieu-may-cham-cong.html#Buoc_2_Xay_dung_bo_cuc_ket_qua\" title=\"B\u01b0\u1edbc 2: X\u00e2y d\u1ef1ng b\u1ed1 c\u1ee5c k\u1ebft qu\u1ea3\">B\u01b0\u1edbc 2: X\u00e2y d\u1ef1ng b\u1ed1 c\u1ee5c k\u1ebft qu\u1ea3<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/xay-dung-cong-thuc-tinh-cong-ca-dem-tu-du-lieu-may-cham-cong.html#Buoc_3_Xay_dung_cong_thuc_tinh\" title=\"B\u01b0\u1edbc 3: X\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c t\u00ednh\">B\u01b0\u1edbc 3: X\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c t\u00ednh<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/xay-dung-cong-thuc-tinh-cong-ca-dem-tu-du-lieu-may-cham-cong.html#Buoc_4_Hoan_thien_bang_tinh\" title=\"B\u01b0\u1edbc 4: Ho\u00e0n thi\u1ec7n b\u1ea3ng t\u00ednh\">B\u01b0\u1edbc 4: Ho\u00e0n thi\u1ec7n b\u1ea3ng t\u00ednh<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"De_bai\"><\/span>\u0110\u1ec1 b\u00e0i<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>T\u1eeb m\u00e1y ch\u1ea5m c\u00f4ng, k\u1ebf to\u00e1n tr\u00edch xu\u1ea5t d\u1eef li\u1ec7u ra\u00a0Excel k\u1ebft qu\u1ea3 ch\u1ea5m c\u00f4ng c\u1ee7a nh\u00e2n vi\u00ean A \u0111\u1ec3 t\u00ednh s\u1ed1 gi\u1edd l\u00e0m vi\u1ec7c ban\u00a0\u0111\u00eam. D\u1eef li\u1ec7u tr\u00edch xu\u1ea5t nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12902 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/pic02-7.png\" width=\"517\" height=\"554\" title=\"pic02-7\"><\/p>\n<p>bi\u1ebft r\u1eb1ng quy\u00a0\u01b0\u1edbc t\u00ednh c\u00f4ng nh\u01b0 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-218.gif\" title=\"ex101_92019-nho-218\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<div class=\"code-block code-block-2\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/vba101_92019-nho-17.gif\" title=\"vba101_92019-nho-17\"><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-218.gif\" title=\"ex101_92019-nho-218\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<ul>\n<li>Ca 2: L\u00e0m vi\u1ec7c t\u1eeb 15h-23h th\u00ec\u00a0\u0111\u01b0\u1ee3c t\u00ednh l\u00e0m\u00a0\u0111\u00eam 1h (t\u1eeb 22-23h)<\/li>\n<li>Ca 3: L\u00e0m vi\u1ec7c t\u1eeb 23h-7h s\u00e1ng h\u00f4m sau th\u00ec\u00a0\u0111\u01b0\u1ee3c t\u00ednh l\u00e0m\u00a0\u0111\u00eam 8h (t\u1eeb 23h-7h)<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Cach_lam\"><\/span>C\u00e1ch l\u00e0m<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_1_Phan_tich_yeu_cau_de_bai\"><\/span>B\u01b0\u1edbc 1: Ph\u00e2n t\u00edch y\u00eau c\u1ea7u\u00a0\u0111\u1ec1 b\u00e0i<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>B\u1ea1n s\u1ebd b\u1eaft\u00a0\u0111\u1ea7u y\u00eau c\u1ea7u n\u00e0y th\u1ebf n\u00e0o:<\/p>\n<ul>\n<li>L\u1eadp t\u1ee9c m\u1edf\u00a0Excel l\u00ean v\u00e0 m\u00e0y m\u00f2 c\u00f4ng th\u1ee9c?<\/li>\n<li>L\u00ean m\u1ea1ng h\u1ecfi\u00a0 v\u00e0 t\u00ecm ng\u01b0\u1eddi tr\u1ee3 gi\u00fap?<\/li>\n<li>D\u00e0nh nhi\u1ec1u th\u1eddi gian xem\u00a0\u0111\u1ec1 b\u00e0i v\u00e0 suy lu\u1eadn t\u00ednh logic, sau\u00a0\u0111\u00f3 m\u1edbi th\u1eed l\u00e0m ho\u1eb7c nh\u1edd ng\u01b0\u1eddi gi\u00fap?<\/li>\n<\/ul>\n<blockquote>\n<p>Xem th\u00eam:\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/excel-co-ban\">T\u00e0i li\u1ec7u Excel c\u01a1 b\u1ea3n<\/a><\/p>\n<\/blockquote>\n<p>C\u00f3 l\u1ebd tr\u01b0\u1eddng h\u1ee3p th\u1ee9 3 s\u1ebd\u00a0\u00edt ng\u01b0\u1eddi l\u00e0m theo. Nh\u01b0ng b\u1ea1n c\u00f3 bi\u1ebft r\u1eb1ng\u00a0\u0111\u00f3 l\u00e0 c\u00e1ch t\u1ed1t nh\u1ea5t\u00a0\u0111\u1ec3 ch\u00fang ta h\u1ecdc c\u00e1ch gi\u1ea3i quy\u1ebft v\u1ea5n\u00a0\u0111\u1ec1 kh\u00f4ng? Ngay c\u1ea3 ng\u01b0\u1eddi c\u00f3 th\u1ec3 gi\u1ea3i quy\u1ebft y\u00eau c\u1ea7u\u00a0\u0111\u00f3 c\u0169ng ph\u1ea3i d\u00e0nh nhi\u1ec1u th\u1eddi gian xem l\u1ea1i\u00a0\u0111\u1ec1 b\u00e0i v\u00e0 suy lu\u1eadn t\u00ednh logic, sau\u00a0\u0111\u00f3 m\u1edbi c\u00f3 th\u1ec3\u00a0\u0111\u01b0a ra l\u1eddi gi\u1ea3i gi\u00fap b\u1ea1n.<\/p>\n<p>Vi\u1ec7c\u00a0\u0111\u1ecdc\u00a0\u0111\u1ec1 b\u00e0i, ph\u00e2n t\u00edch\u00a0\u0111\u1ec1 b\u00e0i l\u00e0 r\u1ea5t quan tr\u1ecdng. Khi\u00a0\u0111i h\u1ecdc ch\u00fang ta\u00a0\u0111\u01b0\u1ee3c d\u1ea1y nhi\u1ec1u, h\u1ecdc nhi\u1ec1u nh\u01b0ng khi\u00a0\u0111i l\u00e0m r\u1ea5t nhi\u1ec1u b\u1ea1n qu\u00ean m\u1ea5t\u00a0\u0111i\u1ec1u n\u00e0y.<\/p>\n<p>\u0110\u1ec1 b\u00e0i\u00a0\u0111\u01b0\u1ee3c di\u1ec5n\u00a0\u0111\u1ea1t l\u1ea1i nh\u01b0 sau:<\/p>\n<ul>\n<li>M\u1ed7i ng\u00e0y l\u00e0m vi\u1ec7c c\u00f3 3 ca \u0111\u01b0\u1ee3c \u0111\u00e1nh s\u1ed1 t\u1eeb 1, 2, 3, m\u1ed7i ca c\u00f3 gi\u1edd v\u00e0o v\u00e0 gi\u1edd ra ri\u00eang\u00a0(theo thi\u1ebft l\u1eadp c\u1ee7a m\u00e1y ch\u1ea5m c\u00f4ng) =&gt; V\u00f9ng d\u1eef li\u1ec7u t\u1eeb c\u1ed9t C\u00a0\u0111\u1ebfn c\u1ed9t H<\/li>\n<li>Nh\u01b0ng\u00a0\u0111\u00f3 l\u00e0 thi\u1ebft l\u1eadp quy\u00a0\u01b0\u1edbc c\u1ee7a m\u00e1y ch\u1ea5m c\u00f4ng, c\u00f2n ng\u01b0\u1eddi lao\u00a0\u0111\u1ed9ng khi l\u00e0m vi\u1ec7c v\u1eabn ch\u1ea5m c\u00f4ng ch\u1ee7 y\u1ebfu v\u00e0o ca 1 (C\u1ed9t C v\u00e0 D ch\u1ee9a th\u1eddi gian theo ca 2, ca 3)<\/li>\n<li>V\u00ec v\u1eady ch\u00fang ta\u00a0\u0111\u1ec1u c\u00f3 th\u1ec3 coi b\u1ea5t c\u1ee9 ca n\u00e0o c\u0169ng c\u00f3 th\u1ec3 l\u00e0 ca 1, ca 2, ca 3 t\u00f9y theo th\u1eddi gian l\u00e0m vi\u1ec7c c\u1ee7a ca\u00a0\u0111\u00f3 =&gt; Ca n\u00e0o c\u00f3 th\u1eddi gian ch\u1ea5m c\u00f4ng th\u00ec\u00a0\u0111\u1ec1u\u00a0\u0111\u01b0\u1ee3c x\u00e9t<\/li>\n<li>Ca 2\u00a0\u0111\u01b0\u1ee3c hi\u1ec3u l\u00e0 gi\u1edd v\u00e0o trong kho\u1ea3ng 15 gi\u1edd (c\u00f3 th\u1ec3 coi nh\u01b0 sau 12ht\u1edbi tr\u01b0\u1edbc 17h), gi\u1edd ra trong kho\u1ea3ng 23 gi\u1edd (c\u00f3 th\u1ec3 coi nh\u01b0 sau 22h v\u00e0 tr\u01b0\u1edbc 24h)<\/li>\n<li>Ca 3\u00a0\u0111\u01b0\u1ee3c hi\u1ec3u l\u00e0 gi\u1edd v\u00e0o trong kho\u1ea3ng 23 gi\u1edd (c\u00f3 th\u1ec3 coi nh\u01b0 sau 22h v\u00e0 tr\u01b0\u1edbc 24h), gi\u1edd ra trong kho\u1ea3ng 7 gi\u1edd (c\u00f3 th\u1ec3 coi nh\u01b0 tr\u01b0\u1edbc 8h),\u00a0\u1edf\u00a0\u0111\u00e2y bao g\u1ed3m\u00a0\u0111i\u1ec1u ki\u1ec7n gi\u1edd v\u00e0o ph\u1ea3i &gt;22 gi\u1edd<\/li>\n<\/ul>\n<p>Logic kh\u00e1 ph\u1ee9c t\u1ea1p ph\u1ea3i kh\u00f4ng n\u00e0o? B\u1ea3n th\u00e2n v\u1ea5n\u00a0\u0111\u1ec1 ph\u1ee9c t\u1ea1p th\u00ec vi\u1ec7c di\u1ec5n gi\u1ea3i logic c\u0169ng ph\u1ee9c t\u1ea1p. Nh\u01b0ng khi ch\u00fang ta\u00a0\u0111\u00e3 c\u00f3 th\u1ec3 di\u1ec5n gi\u1ea3i\u00a0\u0111\u01b0\u1ee3c logic\u00a0\u0111\u00fang th\u00ec vi\u1ec7c x\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c, c\u00e1ch l\u00e0m tr\u00ean\u00a0Excel s\u1ebd tr\u1edf n\u00ean d\u1ec5 d\u00e0ng h\u01a1n nhi\u1ec1u, b\u1edfi v\u1ea5n\u00a0\u0111\u1ec1\u00a0\u0111\u00e3 trong ph\u1ea1m vi ki\u1ec3m so\u00e1t c\u1ee7a ch\u00fang ta r\u1ed3i.<\/p>\n<p>Vi\u1ec7c ph\u00e2n t\u00edch y\u00eau c\u1ea7u\u00a0\u0111\u1ec1 b\u00e0i s\u1ebd gi\u00fap ch\u00fang ta:<\/p>\n<ul>\n<li>N\u1eafm\u00a0\u0111\u01b0\u1ee3c y\u00eau c\u1ea7u cu\u1ed1i c\u00f9ng =&gt; L\u1ef1a ch\u1ecdn\u00a0\u0111\u01b0\u1ee3c ph\u01b0\u01a1ng ph\u00e1p ti\u1ebfp c\u1eadn ph\u00f9 h\u1ee3p (D\u00f9ng h\u00e0m g\u00ec\u00a0\u0111\u1ec3 ra k\u1ebft qu\u1ea3, b\u1ed1 c\u1ee5c k\u1ebft qu\u1ea3 th\u1ebf n\u00e0o)<\/li>\n<li>Hi\u1ec3u r\u00f5 c\u1ea5u tr\u00fac,\u00a0\u0111\u1eb7c tr\u01b0ng c\u1ee7a d\u1eef li\u1ec7u ban\u00a0\u0111\u1ea7u =&gt; D\u1ef1a v\u00e0o\u00a0\u0111\u00f3\u00a0\u0111\u1ec3 c\u00f3 h\u01b0\u1edbng gi\u1ea3i quy\u1ebft ph\u00f9 h\u1ee3p (C\u1ea7n l\u00e0m nh\u1eefng b\u01b0\u1edbc n\u00e0o)<\/li>\n<li>Gi\u1ea3i th\u00edch r\u00f5 c\u00e1c\u00a0\u0111\u1eb7c\u00a0\u0111i\u1ec3m c\u1ee7a d\u1eef li\u1ec7u =&gt; Bi\u1ebft\u00a0\u0111\u01b0\u1ee3c t\u1eebng\u00a0y\u1ebfu t\u1ed1trong c\u00f4ng th\u1ee9c s\u1ebd\u00a0\u0111\u01b0\u1ee3c x\u00e2y d\u1ef1ng nh\u01b0 th\u1ebf n\u00e0o (N\u1ed9i dung c\u1ee5 th\u1ec3 t\u1eebng b\u01b0\u1edbc)<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_2_Xay_dung_bo_cuc_ket_qua\"><\/span>B\u01b0\u1edbc 2: X\u00e2y d\u1ef1ng b\u1ed1 c\u1ee5c k\u1ebft qu\u1ea3<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Vi\u1ec7c x\u00e2y d\u1ef1ng b\u1ed1 c\u1ee5c gi\u00fap ch\u00fang ta\u00a0\u0111\u1ecbnh h\u00ecnh\u00a0\u0111\u01b0\u1ee3c t\u1eebng ph\u1ea7n c\u00f4ng vi\u1ec7c c\u1ea7n l\u00e0m, t\u1ed5ng k\u1ebft\u00a0\u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 c\u00f4ng vi\u1ec7c.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12903 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/pic03-5.png\" width=\"850\" height=\"594\" title=\"pic03-5\"><\/p>\n<p>V\u00ec d\u1eef li\u1ec7u c\u00f3 3 ca c\u00f3 th\u1ec3 c\u00f3 d\u1eef li\u1ec7u ch\u1ea5m c\u00f4ng (v\u00ec ch\u00fang ta kh\u00f4ng th\u1ec3 bi\u1ebft tr\u01b0\u1edbc d\u1eef li\u1ec7u m\u00e1y ch\u1ea5m c\u00f4ng k\u1ebft xu\u1ea5t ra nh\u01b0 th\u1ebf n\u00e0o, n\u00ean s\u1ebd coi nh\u01b0 c\u1ea3 3 ca\u00a0\u0111\u1ec1u c\u00f3 th\u1ec3 c\u00f3 d\u1eef li\u1ec7u) =&gt; K\u1ebft qu\u1ea3 t\u00ednh s\u1ebd t\u01b0\u01a1ng\u00a0\u1ee9ng ra 3 c\u1ed9t theo t\u1eebng ca (t\u01b0\u01a1ng\u00a0\u1ee9ng c\u1ed9t I, J, K). C\u00f3 th\u00eam c\u1ed9t t\u1ed5ng c\u1ed9ng\u00a0\u0111\u1ec3 t\u00ednh t\u1ed5ng s\u1ed1 gi\u1edd l\u00e0m\u00a0\u0111\u00eam theo t\u1eebng ng\u00e0y<\/p>\n<blockquote>\n<p>T\u00ecm hi\u1ec3u v\u1ec1 <a href=\"http:\/\/blog.ezworkapp.com\">kh\u00f3a h\u1ecdc Word Excel<\/a> v\u1edbi c\u00e1c chuy\u00ean gia<\/p>\n<\/blockquote>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_3_Xay_dung_cong_thuc_tinh\"><\/span>B\u01b0\u1edbc 3: X\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c t\u00ednh<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Khi x\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c t\u00ednh, ch\u00fang ta c\u1ea7n ph\u1ea3i lu\u00f4n d\u1ef1a v\u00e0o logic\u00a0\u0111\u00e3 ph\u00e2n t\u00edch\u00a0\u1edf tr\u00ean\u00a0\u0111\u1ec3 x\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c.<\/p>\n<p>X\u00e9t cho c\u1ed9t I (t\u01b0\u01a1ng\u00a0\u1ee9ng v\u1edbi th\u1eddi gian ca 1 c\u1ee7a m\u00e1y ch\u1ea5m c\u00f4ng)<\/p>\n<p>X\u00e9t c\u00e1c tr\u01b0\u1eddng h\u1ee3p c\u00f3 th\u1ec3 x\u1ea3y ra:<\/p>\n<ul>\n<li>Kh\u00f4ng c\u00f3 d\u1eef li\u1ec7u =&gt; K\u1ebft qu\u1ea3 s\u1ebd kh\u00f4ng c\u00f3<\/li>\n<li>Gi\u1edd v\u00e0o v\u00e0 gi\u1edd ra t\u01b0\u01a1ng\u00a0\u1ee9ng v\u1edbi\u00a0\u0111i\u1ec1u ki\u1ec7n l\u00e0\u00a0ca 2 =&gt; L\u1ea5y k\u1ebft qu\u1ea3 gi\u1edd l\u00e0m \u0111\u00eam theo ca 2<\/li>\n<li>Gi\u1edd v\u00e0o v\u00e0 gi\u1edd ra t\u01b0\u01a1ng\u00a0\u1ee9ng v\u1edbi\u00a0\u0111i\u1ec1u ki\u1ec7n l\u00e0 ca 3 =&gt; L\u1ea5y k\u1ebft qu\u1ea3 gi\u1edd l\u00e0m\u00a0\u0111\u00eam theo ca 3<\/li>\n<\/ul>\n<p>C\u00e1c tr\u01b0\u1eddng h\u1ee3p n\u00e0y\u00a0\u0111\u1ec1u l\u00e0 d\u1ea1ng lu\u1eadn l\u00fd N\u1ebfu\u2026 th\u00ec\u2026 =&gt; S\u1eed d\u1ee5ng h\u00e0m\u00a0IF\u00a0\u0111\u1ec3 th\u1ef1c hi\u1ec7n.<\/p>\n<ul>\n<li>Tr\u01b0\u1eddng h\u1ee3p kh\u00f4ng c\u00f3 d\u1eef li\u1ec7u\u00a0\u1edf Gi\u1edd v\u00e0o ho\u1eb7c Gi\u1edd ra\u00a0=&gt; IF(OR(C3=\u201d\u201d,D3=\u201d\u201d),0,\u00a0<em>k\u1ebft qu\u00e1 n\u1ebfu c\u1ea3 2\u00a0\u00f4 gi\u1edd v\u00e0o v\u00e0 gi\u1edd ra c\u00f3 d\u1eef li\u1ec7u<\/em>) =&gt;\u00a0IF1<\/li>\n<li>Tr\u01b0\u1eddng h\u1ee3p\u00a0\u0111i\u1ec1u ki\u1ec7n l\u00e0 ca 2 =&gt;\u00a0IF(AND(HOUR(C3)&gt;12,HOUR(C3)&lt;17,HOUR(D3)&lt;24),1,\u00a0<em>k\u1ebft qu\u1ea3 n\u1ebfu kh\u00f4ng th\u1ecfa m\u00e3n\u00a0\u0111i\u1ec1u ki\u1ec7n ca 2<\/em>) =&gt;\u00a0IF2<\/li>\n<li>Tr\u01b0\u1eddng h\u1ee3p\u00a0\u0111i\u1ec1u ki\u1ec7n l\u00e0 ca 3 =&gt;\u00a0IF(AND(HOUR(C3)&gt;=22,HOUR(D3)&lt;8),8, <em>k\u1ebft qu\u1ea3 n\u1ebfu kh\u00f4ng th\u1ecfa m\u00e3n\u00a0\u0111i\u1ec1u ki\u1ec7n ca 3<\/em>) =&gt;\u00a0IF3<\/li>\n<\/ul>\n<p>N\u1ebfu gi\u00e1 tr\u1ecb kh\u00f4ng th\u1ecfa m\u00e3n\u00a0IF1 th\u00ec x\u00e9t\u00a0IF2, n\u1ebfu gi\u00e1 tr\u1ecb kh\u00f4ng th\u1ecfa m\u00e3n\u00a0IF2 th\u00ec x\u00e9t\u00a0IF3, n\u1ebfu gi\u00e1 tr\u1ecb kh\u00f4ng th\u1ecfa m\u00e3n\u00a0IF3 th\u00ec = 0<\/p>\n<p>Nh\u01b0 v\u1eady khi ch\u00fang ta l\u1ed3ng gh\u00e9p c\u1ea3 3 h\u00e0m\u00a0IF n\u00e0y v\u00e0o 1\u00a0\u0111\u1ec3 l\u00e0m c\u00f4ng th\u1ee9c chung cho\u00a0\u00f4 I3 nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12907 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/pic05-5.png\" width=\"1300\" height=\"138\" title=\"pic05-5\"><\/p>\n<p>L\u00e0m t\u01b0\u01a1ng t\u1ef1 v\u1edbi c\u00f4ng th\u1ee9c t\u00ednh ca 2\u00a0\u1edf c\u1ed9t J (\u00a0\u00f4 J3) , ca 3\u00a0\u1edf c\u1ed9t K (\u00f4 K3) nh\u01b0 sau<\/p>\n<blockquote>\n<p>J3 =IF(OR(E3=\u201d\u201d,F3=\u201d\u201d),0,IF(AND(HOUR(E3)&gt;12,HOUR(E3)&lt;16,HOUR(F3)&lt;24),1,IF(AND(HOUR(E3)&gt;=22,HOUR(F3)&lt;8),8,0)))<\/p>\n<p>K3 =IF(OR(G3=\u201d\u201d,H3=\u201d\u201d),0,IF(AND(HOUR(G3)&gt;12,HOUR(G3)&lt;16,HOUR(H3)&lt;24),1,IF(AND(HOUR(G3)&gt;=22,HOUR(H3)&lt;8),8,0)))<\/p>\n<\/blockquote>\n<p>Copy c\u00f4ng th\u1ee9c xu\u1ed1ng c\u00e1c d\u00f2ng ph\u00eda d\u01b0\u1edbi, ch\u00fang ta c\u00f3 k\u1ebft qu\u1ea3 nh\u01b0 sau<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12908 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/pic06-4.png\" width=\"839\" height=\"594\" title=\"pic06-4\"><\/p>\n<p>Nh\u01b0 v\u1eady ch\u00fang ta\u00a0\u0111\u00e3 t\u00ednh\u00a0\u0111\u01b0\u1ee3c gi\u1edd l\u00e0m\u00a0\u0111\u00eam cho t\u1eebng tr\u01b0\u1eddng h\u1ee3p ch\u1ea5m c\u00f4ng r\u1ed3i. B\u00e2y gi\u1edd c\u00f3 th\u1ec3 chuy\u1ec3n sang b\u01b0\u1edbc cu\u1ed1i c\u00f9ng.<\/p>\n<blockquote>\n<p>\u0110\u0103ng k\u00ed ngay:\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-excel-ke-toan.html\">kho\u0301a h\u1ecdc Excel k\u1ebf to\u00e1n<\/a> 1 th\u1ea7y 1 tr\u00f2<\/p>\n<\/blockquote>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_4_Hoan_thien_bang_tinh\"><\/span>B\u01b0\u1edbc 4: Ho\u00e0n thi\u1ec7n b\u1ea3ng t\u00ednh<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>T\u1ea1o c\u00e1c c\u00f4ng th\u1ee9c t\u00ednh t\u1ed5ng s\u1ed1 c\u00f4ng\u00a0\u0111\u1ec3 ho\u00e0n thi\u1ec7n b\u1ea3ng t\u00ednh.<\/p>\n<p>Vi\u1ec7c n\u00e0y s\u1ebd gi\u00fap ch\u00fang ta c\u00f3\u00a0\u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 cu\u1ed1i c\u00f9ng nh\u01b0 mong mu\u1ed1n.<\/p>\n<p>L3 = SUM(I3:K3) =&gt; Filldown cho v\u00f9ng L3:L23<\/p>\n<p>D\u00f2ng t\u1ed5ng c\u1ed9ng L24 = SUM(L3:L23)<\/p>\n<p>B\u1ea3ng t\u00ednh sau khi ho\u00e0n thi\u1ec7n nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12909 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/pic07-4.png\" width=\"836\" height=\"595\" title=\"pic07-4\"><\/p>\n<p>Ch\u00fang ta c\u00f3 th\u1ec3 m\u1edf r\u1ed9ng b\u1ea3ng t\u00ednh cho\u00a0\u0111\u1ee7 s\u1ed1 ng\u00e0y trong th\u00e1ng (31 ng\u00e0y) \u0111\u1ec3 t\u00ednh cho b\u1ea5t k\u1ef3 th\u00e1ng n\u00e0o, v\u00e0 ch\u1ec9 c\u1ea7n thay\u00a0\u0111\u1ed5i th\u1eddi gian ch\u1ea5m c\u00f4ng c\u1ee7a nh\u00e2n vi\u00ean kh\u00e1c v\u00e0o b\u1ea3ng l\u00e0 c\u00f3 ngay k\u1ebft qu\u1ea3 cho nh\u00e2n vi\u00ean\u00a0\u0111\u00f3 r\u1ed3i.<\/p>\n<p>S\u1eed d\u1ee5ng Excel \u0111\u1ec3 \u1ee9ng d\u1ee5ng cho vi\u1ec7c l\u1ecdc v\u00e0 t\u00ednh to\u00e1n d\u1eef li\u1ec7u r\u1ea5t hi\u1ec7u nghi\u1ec7m ph\u1ea3i kh\u00f4ng n\u00e0o. \u0110\u1ec3 t\u1eadn d\u1ee5ng t\u1ed1i \u0111a t\u00ednh n\u0103ng vi di\u1ec7u c\u1ee7a Excel, b\u1ea1n c\u1ea7n th\u00e0nh th\u1ea1o c\u00e1c h\u00e0m, c\u00f4ng c\u1ee5 c\u0169ng nh\u01b0 c\u00e1c t\u00ednh n\u0103ng kh\u00e1c. T\u1ea1i kh\u00f3a h\u1ecdc\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-218.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":10755,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-10754","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\/10754","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=10754"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/10754\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/10755"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=10754"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=10754"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=10754"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}