{"id":11636,"date":"2025-01-15T04:42:01","date_gmt":"2025-01-15T04:42:01","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/15\/theo-doi-sinh-nhat-bang-excel-quan-ly-nhan-su\/"},"modified":"2025-01-15T04:42:01","modified_gmt":"2025-01-15T04:42:01","slug":"theo-doi-sinh-nhat-bang-excel-quan-ly-nhan-su","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/15\/theo-doi-sinh-nhat-bang-excel-quan-ly-nhan-su\/","title":{"rendered":"Theo d\u00f5i sinh nh\u1eadt b\u1eb1ng excel \u2013 Qu\u1ea3n l\u00fd nh\u00e2n s\u1ef1"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\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-67873c97f21b9\" 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\/theo-doi-sinh-nhat-bang-excel-quan-ly-nhan-su.html#1_Tam_quan_trong\" title=\"1. T\u1ea7m quan tr\u1ecdng\">1. T\u1ea7m quan tr\u1ecdng<\/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\/theo-doi-sinh-nhat-bang-excel-quan-ly-nhan-su.html#2_Ket_qua_can_dat_duoc\" title=\"2. K\u1ebft qu\u1ea3 c\u1ea7n\u00a0\u0111\u1ea1t\u00a0\u0111\u01b0\u1ee3c\">2. K\u1ebft qu\u1ea3 c\u1ea7n\u00a0\u0111\u1ea1t\u00a0\u0111\u01b0\u1ee3c<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/theo-doi-sinh-nhat-bang-excel-quan-ly-nhan-su.html#3_Cach_lam\" title=\"3. C\u00e1ch l\u00e0m\">3. 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-4\" href=\"https:\/\/blog.hocexcel.online\/theo-doi-sinh-nhat-bang-excel-quan-ly-nhan-su.html#4_Nhan_xet\" title=\"4.\u00a0Nh\u1eadn x\u00e9t\">4.\u00a0Nh\u1eadn x\u00e9t<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"1_Tam_quan_trong\"><\/span>1. T\u1ea7m quan tr\u1ecdng<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Trong c\u00f4ng t\u00e1c qu\u1ea3n l\u00fd nh\u00e2n s\u1ef1, ngo\u00e0i vi\u1ec7c ch\u1ea5m c\u00f4ng, t\u00ednh l\u01b0\u01a1ng, qu\u1ea3n l\u00fd h\u1ee3p\u00a0\u0111\u1ed3ng\u2026 v\u1edbi v\u00f4 v\u00e0n nh\u1eefng th\u1ee9\u00a0\u0111au\u00a0\u0111\u1ea7u nh\u1ee9c\u00a0\u00f3c, b\u1ea1n c\u00f3 th\u1ec3 th\u01b0 gi\u00e3n v\u1edbi b\u00e0i vi\u1ebft n\u00e0y.<\/p>\n<p>Nh\u01b0 1 chi\u1ebfc \u0111\u1ed3ng h\u1ed3 b\u00e1o th\u1ee9c, n\u00f3 gi\u00fap b\u1ea1n c\u00f3 k\u1ebf ho\u1ea1ch\u00a0\u0111\u1ec3 t\u1ed5 ch\u1ee9c sinh nh\u1eadt cho c\u00e1c th\u00e0nh vi\u00ean trong c\u00f4ng ty.<\/p>\n<p>B\u1eb1ng vi\u1ec7c l\u00e0m n\u00e0y, b\u1ea1n c\u00f3 th\u1ec3 d\u1ec5 d\u00e0ng t\u1ea1o\u00a0\u0111\u01b0\u1ee3c thi\u1ec7n c\u1ea3m trong m\u1eaft m\u1ecdi ng\u01b0\u1eddi, nh\u1edd v\u1eady m\u00e0 c\u00f4ng vi\u1ec7c c\u1ee7a b\u1ea1n c\u0169ng s\u1ebd thu\u1eadn l\u1ee3i h\u01a1n.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"2_Ket_qua_can_dat_duoc\"><\/span>2. K\u1ebft qu\u1ea3 c\u1ea7n\u00a0\u0111\u1ea1t\u00a0\u0111\u01b0\u1ee3c<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>B\u1ea1n s\u1ebd c\u00f3 1 c\u00e1i b\u1ea3ng nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-8088 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/01.png\" width=\"977\" height=\"811\" title=\"01\"><\/p>\n<p>C\u1ee9 m\u1ed7i khi\u00a0\u0111\u1ed5i s\u1ed1 th\u00e1ng t\u1ea1i\u00a0\u00f4 D2,\u00a0tr\u1ea1ng th\u00e1i th\u00f4ng b\u00e1o s\u1ebd\u00a0\u0111\u01b0\u1ee3c thay\u00a0\u0111\u1ed5i 1 c\u00e1ch t\u1ef1\u00a0\u0111\u1ed9ng.<\/p>\n<p>Gi\u1edd ch\u00fang ta h\u00e3y xem c\u00e1ch l\u00e0m nh\u01b0 th\u1ebf n\u00e0o nh\u00e9!<\/p>\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-315.gif\" title=\"ex101_92019-nho-315\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"3_Cach_lam\"><\/span>3. C\u00e1ch l\u00e0m<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>\u0110\u1ea7u ti\u00ean b\u1ea1n t\u1ea1o form b\u1ea3ng d\u1eef li\u1ec7u, g\u1ed3m:<br \/>\n\u2013 T\u00ean b\u1ea3ng: \u0110\u1ec3 d\u00f2ng \u0111\u1ea7u ti\u00ean, c\u00f3 th\u1ec3 merge cell t\u1eeb c\u1ed9t A \u0111\u1ebfn c\u1ed9t G<br \/>\n\u2013 D\u00f2ng th\u1ee9 2 \u0111\u1ec3 ghi 2 c\u1ed9t: Th\u00e1ng v\u00e0 S\u1ed1 ng\u01b0\u1eddi<br \/>\n\u2013 C\u00e1ch ra 1 d\u00f2ng \u0111\u1ec3 d\u1ec5 co d\u00e3n kho\u1ea3ng c\u00e1ch ph\u1ea7n b\u1ea3ng so v\u1edbi t\u00ean<br \/>\n\u2013 D\u00f2ng th\u1ee9 4 b\u1ea1n d\u00f9ng \u0111\u1ec3 ghi ti\u00eau \u0111\u1ec1 c\u1ee7a c\u00e1c c\u1ed9t trong b\u1ea3ng: S\u1ed1 th\u1ee9 t\u1ef1, H\u1ecd v\u00e0 t\u00ean, Ch\u1ee9c v\u1ee5 (ph\u00f2ng ban), Ng\u00e0y sinh, Th\u00e1ng, Sinh nh\u1eadt, Th\u00f4ng b\u00e1o<br \/>\n\u2013 C\u00e1c d\u00f2ng ti\u1ebfp theo \u0111\u1ec3 ghi theo t\u1eebng ng\u01b0\u1eddi<\/p>\n<p>Ta c\u00f3 m\u1eabu nh\u01b0 sau:<\/p>\n<div class=\"separator\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/h2.jpg\" width=\"640\" height=\"416\" border=\"0\" title=\"h2\"><\/div>\n<div style=\"text-align: center\">H\u00ecnh 2: M\u1eabu b\u1ed1 c\u1ee5c b\u1ea3ng<\/div>\n<p>\u0110\u1ec3 th\u1ef1c hi\u1ec7n vi\u1ec7c k\u1ebb khung c\u1ee7a b\u1ea3ng, ta th\u1ef1c hi\u1ec7n nh\u01b0 sau:<br \/>\n\u2013 B\u00f4i \u0111en ph\u1ea7n b\u1ea3ng, g\u1ed3m t\u1eeb \u00f4 A4 \u0111\u1ebfn \u00f4 G50 (v\u00ed d\u1ee5 b\u1ea3ng t\u1edbi d\u00f2ng th\u1ee9 50)<br \/>\n\u2013 Ch\u1ecdn Format Cells<br \/>\n\u2013 Trong b\u1ea3ng Format Cells, b\u1ea1n ch\u1ecdn ph\u1ea7n Border<br \/>\n\u2013 \u0110\u1ec3 ch\u1ecdn \u0111\u01b0\u1eddng k\u1ebb, b\u1ea1n ch\u1ecdn m\u1ee5c Line ph\u00eda b\u00ean tr\u00e1i, \u0111\u1ec3 k\u1ebb \u0111\u01b0\u1eddng vi\u1ec1n, b\u1ea1n b\u1ea5m v\u00e0o c\u00e1c \u00f4 th\u1ec3 hi\u1ec7n v\u1ecb tr\u00ed \u0111\u01b0\u1eddng k\u1ebb t\u1ea1i m\u1ee5c Border. M\u1ee5c Presets gi\u00fap b\u1ea1n th\u1ef1c hi\u1ec7n k\u1ebb nhanh v\u1edbi c\u00e1c n\u1ed9i dung:<br \/>\n+ None: b\u1ecf \u0111\u01b0\u1eddng k\u1ebb<br \/>\n+ Outline: K\u1ebb vi\u1ec1n b\u00ean ngo\u00e0i b\u1ea3ng<br \/>\n+ Inside: K\u1ebb vi\u1ec1n b\u00ean trong b\u1ea3ng<br \/>\nM\u00f4 t\u1ea3 c\u1ee5 th\u1ec3 theo h\u00ecnh sau:<\/p>\n<div class=\"separator\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/h3.jpg\" width=\"604\" height=\"640\" border=\"0\" title=\"h3\"><\/div>\n<div style=\"text-align: center\">H\u00ecnh 3: Ph\u1ea7n Format cells \/ Bolder<\/div>\n<p>B\u1ea1n c\u00f3 th\u1ec3 b\u00f4i \u0111en ri\u00eang t\u1eebng ph\u1ea7n v\u00e0 k\u1ebb theo \u00fd mu\u1ed1n cho \u0111\u1ebfn khi \u01b0ng \u00fd.<\/p>\n<p>Sau khi k\u1ebb xong, b\u1ea1n nh\u1eadp 1 v\u00e0i n\u1ed9i dung: t\u00ean nh\u00e2n vi\u00ean, ng\u00e0y sinh v\u00e0o b\u1ea3ng<\/p>\n<div class=\"separator\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/h4.jpg\" width=\"640\" height=\"482\" border=\"0\" title=\"h4\"><\/div>\n<div style=\"text-align: center\">H\u00ecnh 4: V\u00ed d\u1ee5 v\u1ec1 t\u00ean nh\u00e2n vi\u00ean, b\u1ed9 ph\u1eadn, ng\u00e0y sinh c\u1ee7a t\u1eebng ng\u01b0\u1eddi<\/div>\n<p>Sau \u0111\u00e2y ta s\u1ebd th\u1ef1c hi\u1ec7n vi\u1ec7c x\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c cho nh\u1eefng c\u1ed9t, \u00f4 c\u00f2n l\u1ea1i: (L\u01b0u \u00fd d\u1ea5u ng\u0103n c\u00e1ch c\u00e1c ph\u1ea7n trong h\u00e0m c\u1ee7a t\u00f4i l\u00e0 d\u1ea5u ph\u1ea9y, c\u00f3 th\u1ec3 \u1edf m\u00e1y t\u00ednh c\u1ee7a b\u1ea1n l\u00e0 d\u1ea5u ch\u1ea5m ph\u1ea9y)<\/p>\n<p>* C\u1ed9t Th\u00e1ng sinh (C\u1ed9t E):<br \/>\nTa nh\u1eadp c\u00f4ng th\u1ee9c sau \u0111\u00e2y \u1edf \u00f4 E5, c\u00e1c \u00f4 ti\u1ebfp theo s\u1ebd copy c\u00f4ng th\u1ee9c xu\u1ed1ng:<br \/>\n<strong><em>=RIGHT(\u201c0\u2033&amp;MONTH(D5),2)<\/em><\/strong><br \/>\nHo\u1eb7c <em><strong>=TEXT(MONTH(D5),\u201d0#\u201d)<\/strong><\/em><\/p>\n<p>Ch\u00fa\u00a0\u00fd: \u201c0\u201d\u00a0\u1edf\u00a0\u0111\u00e2y l\u00e0 s\u1ed1 kh\u00f4ng, ch\u1ee9 kh\u00f4ng ph\u1ea3i l\u00e0 ch\u1eef O<br \/>\n2 c\u00f4ng th\u1ee9c tr\u00ean c\u00f3 k\u1ebft qu\u1ea3 gi\u1ed1ng nhau, \u0111\u1ec1u mang \u00fd ngh\u0129a l\u00e0 : L\u1ea5y gi\u00e1 tr\u1ecb th\u00e1ng c\u1ee7a c\u1ed9t Ng\u00e0y sinh, \u0111\u1ecbnh d\u1ea1ng d\u01b0\u1edbi d\u1ea1ng 2 ch\u1eef s\u1ed1, n\u1ebfu nh\u1ecf h\u01a1n 10 th\u00ec c\u00f3 s\u1ed1 0 \u1edf tr\u01b0\u1edbc. B\u1ea1n ch\u1ec9 n\u00ean d\u00f9ng 1 trong 2 c\u00f4ng th\u1ee9c.<\/p>\n<p>* C\u1ed9t Sinh nh\u1eadt (C\u1ed9t F)<br \/>\nTa nh\u1eadp c\u00f4ng th\u1ee9c sau \u0111\u00e2y \u1edf \u00f4 F5, c\u00e1c \u00f4 ti\u1ebfp theo s\u1ebd copy c\u00f4ng th\u1ee9c xu\u1ed1ng:<br \/>\n<em><strong>=IF(D5=\u201d\u201d,\u201d\u201d,DATE(YEAR(TODAY()),MONTH(D5),DAY(D5)))<\/strong><\/em><\/p>\n<p>L\u01b0u\u00a0\u00fd: H\u00e0m today()\u00a0\u0111\u1ec3 x\u00e1c\u00a0\u0111\u1ecbnh ng\u00e0y hi\u1ec7n t\u1ea1i. Khi\u00a0\u0111\u1eb7t trong h\u00e0m YEAR\u00a0\u0111\u1ec3 x\u00e1c\u00a0\u0111\u1ecbnh n\u0103m th\u00ec b\u1ea1n ph\u1ea3i l\u01b0u\u00a0\u00fd nh\u1eefng ch\u1ed7\u00a0\u0111\u00f3ng m\u1edf ngo\u1eb7c<\/p>\n<p>YEAR ( TODAY() ) \u00a0 =&gt; T\u00f4i s\u1eed d\u1ee5ng d\u1ea5u c\u00e1ch\u00a0\u0111\u1ec3 l\u00e0m r\u00f5 c\u00e1c d\u1ea5u ngo\u1eb7c, tr\u00e1nh b\u1ecb nh\u1ea7m l\u1eabn<br \/>\nT\u00f4i s\u1ebd ph\u00e2n t\u00edch c\u1ee5 th\u1ec3 \u00fd ngh\u0129a c\u1ee7a c\u00f4ng th\u1ee9c n\u00e0y nh\u01b0 sau:<br \/>\n+ D\u00f9ng h\u00e0m if, n\u1ebfu gi\u00e1 tr\u1ecb \u1edf \u00f4 D5 (ng\u00e0y sinh) l\u00e0 r\u1ed7ng (kh\u00f4ng c\u00f3 gi\u00e1 tr\u1ecb g\u00ec), th\u00ec s\u1ebd tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb r\u1ed7ng cho \u00f4 sinh nh\u1eadt (n\u1ebfu kh\u00f4ng c\u00f3 ng\u00e0y sinh th\u00ec s\u1ebd kh\u00f4ng c\u00f3 sinh nh\u1eadt)<br \/>\n+ C\u00f2n n\u1ebfu \u0111i\u1ec1u tr\u00ean l\u00e0 sai (t\u1ee9c l\u00e0 D5 kh\u00e1c r\u1ed7ng, c\u00f3 ng\u00e0y sinh), th\u00ec ng\u00e0y sinh nh\u1eadt s\u1ebd t\u00ednh nh\u01b0 sau:<br \/>\nH\u00e0m date: tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb ng\u00e0y th\u00e1ng, theo th\u1ee9 t\u1ef1 trong h\u00e0m: n\u0103m, th\u00e1ng, ng\u00e0y<br \/>\nN\u0103m: l\u00e0 Year(today()) t\u1ee9c l\u00e0 n\u0103m hi\u1ec7n t\u1ea1i. L\u01b0u \u00fd h\u00e0m today() s\u1ebd tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb l\u00e0 ng\u00e0y, th\u00e1ng, n\u0103m hi\u1ec7n t\u1ea1i. Do \u0111\u00f3 ta th\u00eam h\u00e0m Year \u0111\u1ec3 ch\u1ec9 l\u1ea5y n\u0103m hi\u1ec7n t\u1ea1i th\u00f4i.<br \/>\nTh\u00e1ng: l\u00e0 th\u00e1ng c\u1ee7a D5 (ng\u00e0y th\u00e1ng n\u0103m sinh) -&gt; ch\u1ec9 l\u1ea5y gi\u00e1 tr\u1ecb th\u00e1ng<br \/>\nNg\u00e0y: l\u00e0 ng\u00e0y c\u1ee7a D5 (ng\u00e0y th\u00e1ng n\u0103m sinh) -&gt; Ch\u1ec9 l\u1ea5y gi\u00e1 tr\u1ecb ng\u00e0y<\/p>\n<p>* C\u1ed9t Th\u00f4ng b\u00e1o (C\u1ed9t G)<br \/>\nTa nh\u1eadp c\u00f4ng th\u1ee9c sau \u0111\u00e2y \u1edf \u00f4 F5, c\u00e1c \u00f4 ti\u1ebfp theo s\u1ebd copy c\u00f4ng th\u1ee9c xu\u1ed1ng:<br \/>\n=IF(D5=\u201d\u201d,\u201d\u201d,IF(TODAY()&gt;F5,\u201d\u0110\u00e3 xong\u201d,IF(TODAY()+7&gt;=F5,\u201dChu\u1ea9n b\u1ecb t\u1ed5 ch\u1ee9c\u201d,IF(MONTH(D5)=$C$2,\u201dTrong th\u00e1ng c\u00f3 SN\u201d,\u201d\u201d))))<\/p>\n<p>T\u00f4i s\u1ebd ph\u00e2n t\u00edch c\u1ee5 th\u1ec3 \u00fd ngh\u0129a c\u1ee7a c\u00f4ng th\u1ee9c n\u00e0y nh\u01b0 sau:<br \/>\n+ H\u00e0m if th\u1ee9 nh\u1ea5t: N\u1ebfu D5 l\u00e0 r\u1ed7ng, th\u00ec s\u1ebd nh\u1eadn gi\u00e1 tr\u1ecb r\u1ed7ng (kh\u00f4ng c\u00f3 ng\u00e0y sinh th\u00ec kh\u00f4ng th\u00f4ng b\u00e1o)<br \/>\n+ H\u00e0m if th\u1ee9 2: s\u1ebd b\u1eaft \u0111\u1ea7u th\u1ef1c hi\u1ec7n khi h\u00e0m if th\u1ee9 1 nh\u1eadn gi\u00e1 tr\u1ecb logic c\u1ee7a tr\u01b0\u1eddng h\u1ee3p sai (t\u1ee9c l\u00e0 D5 kh\u00f4ng r\u1ed7ng, c\u00f3 ng\u00e0y sinh):<br \/>\n\u2013 Today()&gt;F5: n\u1ebfu ng\u00e0y hi\u1ec7n t\u1ea1i l\u1edbn h\u01a1n ng\u00e0y sinh nh\u1eadt, th\u00ec th\u00f4ng b\u00e1o l\u00e0 \u0110\u00e3 xong. T\u1eeb \u0110\u00e3 xong l\u00e0 k\u00fd t\u1ef1, n\u00ean \u0111\u01b0\u1ee3c \u0111\u1eb7t trong d\u1ea5u nh\u00e1y k\u00e9p \u0111\u1ec3 excel hi\u1ec3u.<br \/>\n+ H\u00e0m if th\u1ee9 3: \u0110\u1ec3 th\u1ef1c hi\u1ec7n th\u00f4ng b\u00e1o v\u1edbi \u0111i\u1ec1u ki\u1ec7n kh\u00e1c, \u0111\u01b0\u1ee3c \u0111\u1eb7t trong tr\u01b0\u1eddng h\u1ee3p h\u00e0m if th\u1ee9 2 nh\u1eadn gi\u00e1 tr\u1ecb logic l\u00e0 sai (t\u1ee9c l\u00e0 nh\u1eefng tr\u01b0\u1eddng h\u1ee3p ng\u00e0y hi\u1ec7n t\u1ea1i nh\u1ecf h\u01a1n ng\u00e0y sinh nh\u1eadt, ch\u01b0a \u0111\u1ebfn ng\u00e0y sinh nh\u1eadt)<br \/>\n\u2013 Today()+7&gt;=F5: n\u1ebfu ng\u00e0y hi\u1ec7n t\u1ea1i m\u00e0 c\u1ed9ng th\u00eam 7 nh\u1ecf h\u01a1n ho\u1eb7c b\u1eb1ng ng\u00e0y sinh nh\u1eadt (t\u1ee9c l\u00e0 c\u00e1ch ng\u00e0y sinh nh\u1eadt trong v\u00f2ng 1 tu\u1ea7n), th\u00ec s\u1ebd hi\u1ec7n th\u00f4ng b\u00e1o l\u00e0 \u201cChu\u1ea9n b\u1ecb t\u1ed5 ch\u1ee9c\u201d<br \/>\n+ H\u00e0m if th\u1ee9 4: Th\u1ef1c hi\u1ec7n th\u00f4ng b\u00e1o kh\u00e1c, v\u1edbi tr\u01b0\u1eddng h\u1ee3p sai c\u1ee7a h\u00e0m if th\u1ee9 3 (t\u1ee9c l\u00e0 tr\u01b0\u1eddng h\u1ee3p kh\u00f4ng ph\u1ea3i c\u00e1ch sinh nh\u1eadt 1 tu\u1ea7n, kh\u00f4ng ph\u1ea3i tr\u01b0\u1eddng h\u1ee3p \u0111\u00e3 qu\u00e1 ng\u00e0y sinh nh\u1eadt)<br \/>\n\u2013 Month(D5)=$C$2: n\u1ebfu th\u00e1ng sinh b\u1eb1ng v\u1edbi th\u00e1ng \u0111\u01b0\u1ee3c ch\u1ecdn \u1edf \u00f4 C2, th\u00ec s\u1ebd hi\u1ec7n th\u00f4ng b\u00e1o Trong th\u00e1ng c\u00f3 sinh nh\u1eadt<br \/>\nTr\u01b0\u1eddng h\u1ee3p sai, th\u00ec s\u1ebd nh\u1eadn gi\u00e1 tr\u1ecb r\u1ed7ng (t\u1ee9c l\u00e0 ngo\u00e0i t\u1ea5t c\u1ea3 nh\u1eefng kh\u1ea3 n\u0103ng n\u00eau tr\u00ean, th\u00ec \u0111\u1ec1u tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb r\u1ed7ng, kh\u00f4ng c\u00f3 th\u00f4ng b\u00e1o \u00a0=&gt; ch\u1ec9 c\u00f2n s\u00f3t l\u1ea1i tr\u01b0\u1eddng h\u1ee3p ch\u01b0a \u0111\u1ebfn sinh nh\u1eadt, v\u00e0 kh\u00f4ng ph\u1ea3i c\u00e1ch ng\u00e0y sinh 1 tu\u1ea7n, kh\u00f4ng ph\u1ea3i c\u00f3 sinh nh\u1eadt trong th\u00e1ng \u0111\u01b0\u1ee3c ch\u1ecdn \u1edf C2 =&gt; tr\u01b0\u1eddng h\u1ee3p n\u00e0y kh\u00f4ng c\u1ea7n th\u00f4ng b\u00e1o)<\/p>\n<p>* \u00d4 \u0111\u1ebfm t\u1ed5ng s\u1ed1 ng\u01b0\u1eddi c\u00f3 sinh nh\u1eadt trong th\u00e1ng (\u00f4 F2)<br \/>\n=COUNTIF($E$5:$E$50,$C$2)<br \/>\nN\u1ed9i dung c\u1ee7a h\u00e0m: \u0110\u1ebfm s\u1ed1 l\u1ea7n xu\u1ea5t hi\u1ec7n c\u1ee7a gi\u00e1 tr\u1ecb th\u00e1ng \u0111\u01b0\u1ee3c ch\u1ecdn (\u00f4 C2) trong n\u1ed9i dung c\u1ee7a c\u1ed9t Th\u00e1ng sinh (t\u1eeb \u00f4 E5 \u0111\u1ebfn \u00f4 E50)<\/p>\n<p>Sau khi \u0111\u00e3 x\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c \u1edf c\u00e1c c\u1ed9t v\u00e0 \u00f4 xong, ta s\u1ebd c\u00f3 k\u1ebft qu\u1ea3 nh\u01b0 sau:<\/p>\n<div class=\"separator\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/h5.jpg\" width=\"640\" height=\"484\" border=\"0\" title=\"h5\"><\/div>\n<div style=\"text-align: center\">H\u00ecnh 5: B\u1ea3ng k\u1ebft qu\u1ea3 sau khi x\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c<\/div>\n<p>Tuy nhi\u00ean, \u0111\u1ec3 thu\u1eadn ti\u1ec7n cho vi\u1ec7c theo d\u00f5i, ta s\u1ebd l\u00e0m th\u00eam ch\u1ee9c n\u0103ng t\u1ef1 \u0111\u1ed9ng t\u00f4 m\u00e0u, thay \u0111\u1ed5i \u0111\u1ecbnh d\u1ea1ng cho nh\u1eefng tr\u01b0\u1eddng h\u1ee3p c\u00f3 th\u00f4ng b\u00e1o. Ta th\u1ef1c hi\u1ec7n nh\u01b0 sau:<\/p>\n<p>\u2013 B\u00f4i \u0111en b\u1ea3ng d\u1eef li\u1ec7u t\u1eeb \u00f4 A5 \u0111\u1ebfn \u00f4 G50, ch\u1ecdn ch\u1ee9c n\u0103ng Conditional Formatting, sau \u0111\u00f3 ch\u1ecdn New Rule<\/p>\n<div class=\"separator\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/h6.jpg\" width=\"640\" height=\"482\" border=\"0\" title=\"h6\"><\/div>\n<div style=\"text-align: center\">H\u00ecnh 6: B\u1ea3ng New Formatting Rule hi\u1ec7n ra sau khi ch\u1ecdn Conditional Formatting \/ New Rule<\/div>\n<p>\u00a0<\/p>\n<div class=\"separator\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/h7.jpg\" width=\"400\" height=\"387\" border=\"0\" title=\"h7\"><\/div>\n<div style=\"text-align: center\">H\u00ecnh 7: Ch\u1ecdn d\u00f2ng cu\u1ed1i c\u00f9ng trong m\u1ee5c Select a Rule Type \u1edf b\u1ea3ng New Formatting Rule<\/div>\n<p>T\u1ea1i \u0111\u00e2y b\u1ea1n nh\u1eadp c\u00f4ng th\u1ee9c \u0111\u1ec3 l\u00e0m \u0111i\u1ec1u ki\u1ec7n nh\u01b0 sau:<br \/>\n=IF($G5=\u201d\u0110\u00e3 xong\u201d,TRUE,FALSE)<br \/>\n\u00dd ngh\u0129a: N\u1ebfu gi\u00e1 tr\u1ecb trong c\u1ed9t G, b\u1eaft \u0111\u1ea7u t\u1eeb \u00f4 G5 tr\u1edf \u0111i, c\u00f3 gi\u00e1 tr\u1ecb l\u00e0 \u0110\u00e3 xong, th\u00ec l\u00e0 \u0111\u00fang, c\u00f2n n\u1ebfu kh\u00f4ng ph\u00e1i th\u00ec l\u00e0 sai.<\/p>\n<p>Ti\u1ebfp theo b\u1ea1n b\u1ea5m v\u00e0o \u00f4 Format \u1edf ph\u00eda d\u01b0\u1edbi, ch\u1ecdn \u0111\u1ecbnh d\u1ea1ng theo \u00fd mu\u1ed1n.<\/p>\n<div class=\"separator\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/h8.jpg\" width=\"460\" height=\"400\" border=\"0\" title=\"h8\"><\/div>\n<div style=\"text-align: center\">H\u00ecnh 8: B\u1ea3ng Format Cells hi\u1ec7n ra sau khi b\u1ea5m v\u00e0o \u00f4 Format<\/div>\n<p>T\u1ea1i \u0111\u00e2y, bao g\u1ed3m:<br \/>\nNumber: \u0110\u1ecbnh d\u1ea1ng ki\u1ec3u d\u1eef li\u1ec7u<br \/>\nFont: \u0110\u1ecbnh d\u1ea1ng font ch\u1eef, k\u00edch c\u1ee1, m\u00e0u ch\u1eef\u2026<br \/>\nBorder: K\u1ebb khung<br \/>\nFill: M\u00e0u n\u1ec1n c\u1ee7a \u00f4<br \/>\nTh\u01b0\u1eddng th\u00ec ch\u1ec9 c\u1ea7n \u0111\u1ecbnh d\u1ea1ng t\u00f4 m\u00e0u ch\u1eef, t\u00f4 \u0111\u1eadm, v\u00e0 t\u00f4 m\u00e0u n\u1ec1n cho \u00f4.<\/p>\n<p>L\u00e0m t\u01b0\u01a1ng t\u1ef1 v\u1edbi c\u00e1c th\u00f4ng b\u00e1o kh\u00e1c, m\u1ed7i l\u1ea7n th\u00f4ng b\u00e1o b\u1ea1n l\u1ea1i t\u1ea1o 1 Rule m\u1edbi trong Conditional Formatting<\/p>\n<p>=IF($G5=\u201dChu\u1ea9n b\u1ecb t\u1ed5 ch\u1ee9c\u201d,TRUE,FALSE)<br \/>\n=IF(MONTH($D5)=$C$2,TRUE,FALSE)<\/p>\n<p>Sau khi ho\u00e0n th\u00e0nh, b\u1ea1n c\u00f3 th\u1ec3 ki\u1ec3m tra c\u00e1c rule b\u1eb1ng c\u00e1ch b\u1ea5m v\u00e0o Conditional Formatting, ch\u1ecdn Manager Rule<\/p>\n<div class=\"separator\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/h9.jpg\" width=\"640\" height=\"304\" border=\"0\" title=\"h9\"><\/div>\n<div style=\"text-align: center\">H\u00ecnh 9: B\u1ea3ng Conditional Formatting Rules Manager<\/div>\n<p>L\u01b0u \u00fd b\u1ea1n n\u00ean s\u1eafp x\u1ebfp th\u1ee9 t\u1ef1 c\u00e1c Rule theo \u0111\u00fang th\u1ee9 t\u1ef1 xu\u1ea5t hi\u1ec7n trong h\u00e0m If \u0111\u00e3 vi\u1ebft \u1edf c\u1ed9t Th\u00f4ng b\u00e1o (\u0111\u00e3 xong \u1edf \u0111\u1ea7u ti\u00ean, chu\u1ea9n b\u1ecb \u1edf th\u1ee9 2, c\u00f3 SN trong th\u00e1ng th\u1ee9 3)<\/p>\n<p>C\u00e1ch s\u1eafp x\u1ebfp: B\u1ea1n b\u1ea5m rule c\u1ea7n s\u1eafp x\u1ebfp, b\u1ea5m v\u00e0o n\u00fat m\u0169i t\u00ean ch\u1ec9 l\u00ean tr\u00ean ho\u1eb7c xu\u1ed1ng d\u01b0\u1edbi \u1edf c\u1ea1nh n\u00fat Delete Rule (Ph\u00eda tr\u00ean ch\u1eef Applies to) trong b\u1ea3ng tr\u00ean \u0111\u1ec3 di chuy\u1ec3n theo th\u1ee9 t\u1ef1 nh\u01b0 \u00fd mu\u1ed1n.<\/p>\n<p>Ta \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 nh\u01b0 sau:<\/p>\n<div class=\"separator\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/h10.jpg\" width=\"640\" height=\"486\" border=\"0\" title=\"h10\"><\/div>\n<p>Nh\u01b0 v\u1eady \u0111\u00e3 ho\u00e0n th\u00e0nh xong b\u1ea3ng theo d\u00f5i sinh nh\u1eadt.<br \/>\nB\u00e2y gi\u1edd b\u1ea1n th\u1eed thay \u0111\u1ed5i Th\u00e1ng t\u1ea1i \u00f4 C2, ho\u1eb7c thay \u0111\u1ed5i ng\u00e0y sinh c\u1ee7a c\u00e1c nh\u00e2n vi\u00ean s\u1ebd th\u1ea5y th\u00f4ng b\u00e1o hi\u1ec7n ra c\u00f3 ch\u00ednh x\u00e1c hay kh\u00f4ng<\/p>\n<h2><span class=\"ez-toc-section\" id=\"4_Nhan_xet\"><\/span><b>4.\u00a0Nh\u1eadn x\u00e9t<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Mu\u1ed1n th\u00eam d\u00f2ng, b\u1ea1n ch\u1ec9 c\u1ea7n copy c\u00f4ng th\u1ee9c t\u1eeb d\u00f2ng tr\u00ean xu\u1ed1ng c\u00e1c d\u00f2ng d\u01b0\u1edbi \u0111\u00e3 th\u00eam.<\/p>\n<p>Ch\u1ee9c n\u0103ng Conditional Formatting h\u1ed7 tr\u1ee3 vi\u1ec7c \u0111\u1ecbnh d\u1ea1ng \u00f4 theo c\u00e1c \u0111i\u1ec1u ki\u1ec7n m\u00e0 b\u1ea1n thi\u1ebft l\u1eadp, gi\u00fap vi\u1ec7c th\u00f4ng b\u00e1o tr\u00ean b\u1ea3ng t\u00ednh tr\u1edf n\u00ean t\u1ef1 \u0111\u1ed9ng v\u00e0 d\u1ec5 nh\u00ecn.<\/p>\n<p>Vi\u1ec7c x\u1eed l\u00fd c\u00e1c h\u00e0m ng\u00e0y th\u00e1ng s\u1ebd d\u00f9ng nhi\u1ec1u t\u1edbi h\u00e0m Date, today, month, year\u2026 \u00d4 n\u00e0o \u0111\u00e3 ch\u1ee9a gi\u00e1 tr\u1ecb ng\u00e0y th\u00ec b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng tr\u1ef1c ti\u1ebfp \u00f4 \u0111\u00f3 v\u00e0o c\u00e1c ph\u00e9p t\u00ednh\u00a0+ , \u2013 ng\u00e0y th\u00e1ng l\u1eabn nhau.<\/p>\n<p>Ch\u00fac c\u00e1c b\u1ea1n \u00e1p d\u1ee5ng t\u1ed1t ki\u1ebfn th\u1ee9c n\u00e0y v\u00e0o c\u00f4ng vi\u1ec7c c\u1ee7a m\u00ecnh.<\/p>\n<p>R\u1ea5t nhi\u1ec1u ki\u1ebfn th\u1ee9c ph\u1ea3i kh\u00f4ng n\u00e0o? To\u00e0n b\u1ed9 nh\u1eefng ki\u1ebfn th\u1ee9c n\u00e0y c\u00e1c b\u1ea1n \u0111\u1ec1u c\u00f3 th\u1ec3 h\u1ecdc \u0111\u01b0\u1ee3c trong kh\u00f3a h\u1ecdc\u00a0<a href=\"http:\/\/bit.ly\/2w6YAe8\">EX101 \u2013 Excel t\u1eeb c\u01a1 b\u1ea3n t\u1edbi chuy\u00ean gia<\/a>\u00a0c\u1ee7a H\u1ecdc Excel Online. \u0110\u00e2y l\u00e0 kh\u00f3a h\u1ecdc gi\u00fap b\u1ea1n h\u1ec7 th\u1ed1ng ki\u1ebfn th\u1ee9c m\u1ed9t c\u00e1ch \u0111\u1ea7y \u0111\u1ee7, chi ti\u1ebft. H\u01a1n n\u1eefa kh\u00f4ng h\u1ec1 c\u00f3 gi\u1edbi h\u1ea1n v\u1ec1 th\u1eddi gian h\u1ecdc t\u1eadp n\u00ean b\u1ea1n c\u00f3 th\u1ec3 tho\u1ea3i m\u00e1i h\u1ecdc b\u1ea5t c\u1ee9 l\u00fac n\u00e0o, d\u1ec5 d\u00e0ng tra c\u1ee9u l\u1ea1i ki\u1ebfn th\u1ee9c khi c\u1ea7n. Hi\u1ec7n nay h\u1ec7 th\u1ed1ng \u0111ang c\u00f3 \u01b0u \u0111\u00e3i r\u1ea5t l\u1edbn cho b\u1ea1n khi \u0111\u0103ng k\u00fd tham gia kh\u00f3a h\u1ecdc. Chi ti\u1ebft xem t\u1ea1i: <a href=\"https:\/\/hocexcel.online\" target=\"_blank\">HocExcel.Online<\/a><\/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<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-315.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":11637,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-11636","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\/11636","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=11636"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/11636\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/11637"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=11636"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=11636"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=11636"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}