{"id":1757,"date":"2024-12-02T05:59:10","date_gmt":"2024-12-02T05:59:10","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/ky-thuat-xu-ly-du-lieu-may-cham-cong-bang-excel\/"},"modified":"2024-12-02T05:59:10","modified_gmt":"2024-12-02T05:59:10","slug":"ky-thuat-xu-ly-du-lieu-may-cham-cong-bang-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/ky-thuat-xu-ly-du-lieu-may-cham-cong-bang-excel\/","title":{"rendered":"K\u1ef9 thu\u1eadt x\u1eed l\u00fd d\u1eef li\u1ec7u m\u00e1y ch\u1ea5m c\u00f4ng b\u1eb1ng excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Ch\u1ea5m c\u00f4ng b\u1eb1ng m\u00e1y ch\u1ea5m c\u00f4ng, vi\u1ec7c t\u01b0\u1edfng ch\u1eebng nh\u01b0 gi\u00fap \u0111\u1ee1 nhi\u1ec1u cho nh\u1eefng ng\u01b0\u1eddi l\u00e0m nh\u00e2n s\u1ef1, nh\u01b0ng th\u1ef1c t\u1ebf l\u1ea1i \u0111em \u0111\u1ebfn kh\u00f4ng \u00edt nh\u1eefng kh\u00f3 kh\u0103n, phi\u1ec1n to\u00e1i:<\/p>\n<ul>\n<li>Vi\u1ec7c ng\u01b0\u1eddi lao \u0111\u1ed9ng \u201cqu\u00ean\u201d kh\u00f4ng ch\u1ea5m, khi\u1ebfn cho m\u00e1y \u0111\u01b0a ra k\u1ebft qu\u1ea3 sai<\/li>\n<li>Vi\u1ec7c s\u1eed d\u1ee5ng k\u1ebft qu\u1ea3 ch\u1ea5m c\u00f4ng t\u1eeb m\u00e1y v\u00e0o vi\u1ec7c t\u00ednh l\u01b0\u01a1ng l\u1ea1i kh\u00f4ng th\u1ec3 l\u00e0m \u0111\u01b0\u1ee3c ngay, m\u00e0 c\u1ea7n ph\u1ea3i s\u00e0ng l\u1ecdc l\u1ea1i.<\/li>\n<li>M\u1ed7i ph\u1ea7n m\u1ec1m m\u00e1y ch\u1ea5m c\u00f4ng l\u1ea1i m\u1ed9t ki\u1ec3u, g\u00e2y kh\u00f3 cho ng\u01b0\u1eddi s\u1eed d\u1ee5ng<\/li>\n<\/ul>\n<p>B\u00e0i vi\u1ebft n\u00e0y <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong><\/a> s\u1ebd h\u01b0\u1edbng d\u1eabn c\u00e1c b\u1ea1n k\u1ef9 n\u0103ng \u0111\u1ec3 c\u00f3 th\u1ec3 x\u00e2y d\u1ef1ng 1 b\u1ea3ng excel v\u1edbi ch\u1ee9c n\u0103ng s\u00e0ng l\u1ecdc th\u00f4ng tin t\u1eeb m\u00e1y ch\u1ea5m c\u00f4ng \u0111\u1ec3 cho ra k\u1ebft qu\u1ea3 nh\u01b0 \u00fd mu\u1ed1n.<\/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-674d4cad3e360\" 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\/ky-thuat-xu-ly-du-lieu-may-cham-cong-bang-excel.html#1_Chuan_bi\" title=\"1. Chu\u1ea9n b\u1ecb\">1. Chu\u1ea9n b\u1ecb<\/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\/ky-thuat-xu-ly-du-lieu-may-cham-cong-bang-excel.html#2_Thuc_hien\" title=\"2. Th\u1ef1c hi\u1ec7n\">2. Th\u1ef1c hi\u1ec7n<\/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\/ky-thuat-xu-ly-du-lieu-may-cham-cong-bang-excel.html#3_Toi_uu_bang_xu_ly\" title=\"3. T\u1ed1i\u00a0\u01b0u b\u1ea3ng x\u1eed l\u00fd\">3. T\u1ed1i\u00a0\u01b0u b\u1ea3ng x\u1eed l\u00fd<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"1_Chuan_bi\"><\/span>1. Chu\u1ea9n b\u1ecb<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>\u2013 File excel k\u1ebft xu\u1ea5t d\u1eef li\u1ec7u t\u1eeb m\u00e1y ch\u1ea5m c\u00f4ng<\/p>\n<p>\u2013 File excel m\u1edbi (.xlsx ho\u1eb7c .xls l\u00e0 \u0111\u01b0\u1ee3c. Ngo\u00e0i ra n\u1ebfu mu\u1ed1n n\u00e2ng cao th\u00eam VBA c\u00f3 th\u1ec3 d\u00f9ng file .xlsm)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-8386\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/bcc_kx.png\" width=\"1364\" height=\"496\" title=\"bcc_kx\"><\/p>\n<p style=\"text-align: center\">V\u00ed d\u1ee5 v\u1edbi b\u1ea3ng d\u1eef li\u1ec7u k\u1ebft xu\u1ea5t t\u1eeb m\u00e1y ch\u1ea5m c\u00f4ng ra nh\u01b0 tr\u00ean<\/p>\n<h2><span class=\"ez-toc-section\" id=\"2_Thuc_hien\"><\/span><strong>2. Th\u1ef1c hi\u1ec7n<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong>2.1. B\u01b0\u1edbc 1<\/strong>: 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)<\/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\/2024\/12\/ex101_92019-nho-126.gif\" title=\"ex101_92019-nho-126\"><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\/2024\/12\/vba101_92019-nho-51.gif\" title=\"vba101_92019-nho-51\"><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\/2024\/12\/ex101_92019-nho-126.gif\" title=\"ex101_92019-nho-126\"><br \/>\n<\/a>\n<\/div>\n<\/div>\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>Xem th\u00eam: <strong>H\u01b0\u1edbng d\u1eabn <a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-tao-bang-cham-cong-tren-excel-chi-tiet-nhat.html\">c\u00e1ch t\u1ea1o b\u1ea3ng ch\u1ea5m c\u00f4ng<\/a> b\u1eb1ng Excel c\u1ef1c k\u1ef3 chi ti\u1ebft<\/strong><\/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<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 style=\"list-style-type: circle\">\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>2.2. B\u01b0\u1edbc 2<\/strong>: C\u1ea5u tr\u00fac l\u1ea1i b\u1ea3ng d\u1eef li\u1ec7u<\/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><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-8395 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/bcc_cau-truc-01.png\" width=\"1093\" height=\"439\" title=\"bcc_cau-truc-01\"> <img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-8396 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/bcc_cau-truc-02.png\" width=\"1012\" height=\"415\" title=\"bcc_cau-truc-02\"><\/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: <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>2.3. L\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\/2024\/12\/bcc_loc-kx-01.png\" width=\"1203\" height=\"290\" title=\"bcc_loc-kx-01\"><\/p>\n<p style=\"text-align: center\">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\/2024\/12\/bcc_loc-kx-02.png\" width=\"1097\" height=\"457\" title=\"bcc_loc-kx-02\"><\/p>\n<p style=\"text-align: center\">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 style=\"text-align: left\"><strong>2.4. T\u00ednh gi\u1edd c\u00f4ng trong ng\u00e0y<\/strong><\/p>\n<p style=\"text-align: left\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-8399 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/bcc_xuly-01.png\" width=\"1528\" height=\"442\" title=\"bcc_xuly-01\"><\/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<blockquote>\n<p>Xem th\u00eam:\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-lap-trinh-vba-trong-excel\">l\u1edbp h\u1ecdc VBA \u1edf H\u00e0 N\u1ed9i<\/a><\/p>\n<\/blockquote>\n<p><strong>2.5. X\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\/2024\/12\/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<blockquote>\n<p>CS7 = COUNTIF(BN7:CR7,\u201d&gt;=\u201d&amp;8)<\/p>\n<\/blockquote>\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<blockquote>\n<p>CT7 =\u00a0COUNTIFS(BN7:CR7,\u201d&lt;\u201c&amp;0)<\/p>\n<\/blockquote>\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<blockquote>\n<p>CU7 =\u00a0CS7+CT7 \u00a0 ho\u1eb7c \u00a0CU7 =\u00a0CS7+(CT7\/2)<\/p>\n<\/blockquote>\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<blockquote>\n<p>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<\/blockquote>\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<blockquote>\n<p>Mu\u1ed9n 5\u2032 =&gt; CW7 =\u00a0COUNTIFS($D7:$BM7,\u201d&gt;\u201d&amp;\u201d8:05\u2033,$D7:$BM7,\u201d&lt;\u201c&amp;\u201d8:10\u201d,$D$6:$BM$6,1)<\/p>\n<p>Mu\u1ed9n 10\u2032 =&gt; CX7 =\u00a0COUNTIFS($D7:$BM7,\u201d&gt;\u201d&amp;\u201d8:10\u2033,$D7:$BM7,\u201d&lt;\u201c&amp;\u201d8:15\u201d,$D$6:$BM$6,1)<\/p>\n<p>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<\/blockquote>\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=\"3_Toi_uu_bang_xu_ly\"><\/span>3. T\u1ed1i\u00a0\u01b0u b\u1ea3ng x\u1eed l\u00fd<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>VBA\u00a0gi\u00fap \u00edch\u00a0r\u1ea5t nhi\u1ec1u trong c\u00f4ng vi\u1ec7c: gi\u00fap b\u1ea1n t\u0103ng t\u1ed1c trong qu\u00e1 tr\u00ecnh x\u1eed l\u00fd tr\u00ean Excel, gi\u00fap l\u1eadp c\u00e1c b\u00e1o c\u00e1o t\u1ef1 \u0111\u1ed9ng, \u0111i\u1ec1u khi\u1ec3n c\u00e1c ch\u01b0\u01a1ng tr\u00ecnh kh\u00e1c nh\u01b0 Word, Power Point, Outlook\u2026 gi\u00fap bi\u1ebfn file Excel c\u1ee7a b\u1ea1n tr\u1edf th\u00e0nh 1 ph\u1ea7n m\u1ec1m chuy\u00ean nghi\u1ec7p\u2026<\/p>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 h\u1ecdc VBA m\u1ed9t c\u00e1ch \u0111\u1ea7y \u0111\u1ee7, c\u00f3 h\u1ec7 th\u1ed1ng, b\u1ea1n h\u00e3y tham gia kho\u00e1 h\u1ecdc\u00a0<a href=\"http:\/\/bit.ly\/2OAUBOg\">VBA101 \u2013 VBA c\u01a1 b\u1ea3n d\u00e0nh cho ng\u01b0\u1eddi m\u1edbi b\u1eaft \u0111\u1ea7u<\/a>\u00a0c\u1ee7a h\u1ec7 th\u1ed1ng H\u1ecdc Excel Online. Hi\u1ec7n nay h\u1ec7 th\u1ed1ng \u0111ang c\u00f3 nhi\u1ec1u \u01b0u \u0111\u00e3i khi b\u1ea1n \u0111\u0103ng k\u00fd kh\u00f3a h\u1ecdc n\u00e0y. Chi ti\u1ebft xem t\u1ea1i:<\/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\/2024\/12\/ex101_92019-nho-126.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":1758,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-1757","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\/1757","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=1757"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/1757\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/1758"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=1757"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=1757"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=1757"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}