{"id":11238,"date":"2025-01-15T02:11:22","date_gmt":"2025-01-15T02:11:22","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/15\/ung-dung-excel-trong-cong-viec-ke-toan-luong-bai-1-tiep-theo\/"},"modified":"2025-01-15T02:11:22","modified_gmt":"2025-01-15T02:11:22","slug":"ung-dung-excel-trong-cong-viec-ke-toan-luong-bai-1-tiep-theo","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/15\/ung-dung-excel-trong-cong-viec-ke-toan-luong-bai-1-tiep-theo\/","title":{"rendered":"\u1ee8ng d\u1ee5ng Excel trong c\u00f4ng vi\u1ec7c k\u1ebf to\u00e1n l\u01b0\u01a1ng \u2013 B\u00e0i 1 (ti\u1ebfp theo)"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><strong>Ph\u1ea7n 1:\u00a0Excel v\u1edbi b\u1ea3ng ch\u1ea5m c\u00f4ng<\/strong><\/p>\n<p><strong>B\u00e0i 1: L\u1eadp b\u1ea3ng ch\u1ea5m c\u00f4ng \u2013 cho b\u1ed9 ph\u1eadn h\u00e0nh ch\u00ednh<\/strong><\/p>\n<p><strong>3. Y\u00eau c\u1ea7u 2: Ch\u1ea5m c\u00f4ng theo gi\u1edd b\u1eaft\u00a0\u0111\u1ea7u v\u00e0 gi\u1edd k\u1ebft th\u00fac<\/strong><\/p>\n<p>Th\u00f4ng th\u01b0\u1eddng vi\u1ec7c ch\u1ea5m c\u00f4ng theo gi\u1edd b\u1eaft\u00a0\u0111\u1ea7u v\u00e0 gi\u1edd k\u1ebft th\u00fac\u00a0\u0111\u01b0\u1ee3c th\u1ef1c hi\u1ec7n th\u00f4ng qua m\u00e1y ch\u1ea5m c\u00f4ng.<\/p>\n<p>T\u00f4i s\u1ebd v\u00ed d\u1ee5 v\u1edbi c\u00e1c b\u1ea1n k\u1ebft qu\u1ea3 thu\u00a0\u0111\u01b0\u1ee3c t\u1eeb m\u1ed9t m\u00e1y ch\u1ea5m c\u00f4ng nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-8668 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/bcc_kx-01.png\" width=\"1327\" height=\"441\" title=\"bcc_kx-01\"><\/p>\n<p><strong>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<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<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-273.gif\" title=\"ex101_92019-nho-273\"><br \/>\n<\/a>\n<\/div>\n<\/div>\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>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\/2025\/01\/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\/2025\/01\/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:\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: 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\/2025\/01\/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\/2025\/01\/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>B\u01b0\u1edbc 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\/2025\/01\/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<\/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-273.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":11239,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-11238","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\/11238","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=11238"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/11238\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/11239"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=11238"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=11238"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=11238"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}