{"id":4618,"date":"2024-12-07T19:54:34","date_gmt":"2024-12-07T19:54:34","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/07\/huong-dan-cach-ung-dung-ham-search-va-countifs-de-thong-ke-tinh-trang-lam-viec\/"},"modified":"2024-12-07T19:54:34","modified_gmt":"2024-12-07T19:54:34","slug":"huong-dan-cach-ung-dung-ham-search-va-countifs-de-thong-ke-tinh-trang-lam-viec","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/07\/huong-dan-cach-ung-dung-ham-search-va-countifs-de-thong-ke-tinh-trang-lam-viec\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn c\u00e1ch \u1ee9ng d\u1ee5ng h\u00e0m SEARCH v\u00e0 COUNTIFS \u0111\u1ec3 th\u1ed1ng k\u00ea t\u00ecnh tr\u1ea1ng l\u00e0m vi\u1ec7c"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>B\u1ea1n l\u00e0m c\u00f4ng vi\u1ec7c qu\u1ea3n l\u00fd nh\u00e2n s\u1ef1 nh\u01b0ng ch\u01b0a bi\u1ebft c\u00e1ch th\u1ed1ng k\u00ea t\u00ecnh tr\u1ea1ng l\u00e0m vi\u1ec7c c\u1ee7a t\u1eebng ng\u01b0\u1eddi tr\u00ean Excel? H\u00e3y c\u00f9ng H\u1ecdc Excel Online t\u00ecm hi\u1ec3u c\u00e1ch \u1ee9ng d\u1ee5ng h\u00e0m\u00a0SEARCH v\u00e0 h\u00e0m\u00a0COUNTIFS\u00a0\u0111\u1ec3 th\u1ed1ng k\u00ea t\u00ecnh tr\u1ea1ng l\u00e0m vi\u1ec7c nh\u00e9. Chi ti\u1ebft nh\u01b0 sau:<\/p>\n<p>Gi\u1ea3 s\u1eed ch\u00fang ta c\u00f3 1 b\u1ea3ng d\u1eef li\u1ec7u th\u1ed1ng k\u00ea v\u1ec1 c\u00e1c c\u00f4ng vi\u1ec7c, ng\u01b0\u1eddi th\u1ef1c hi\u1ec7n v\u00e0 t\u00ecnh tr\u1ea1ng th\u1ef1c hi\u1ec7n c\u1ee7a t\u1eebng ng\u01b0\u1eddi nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-20857\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/de-bai-thong-ke-cong-viec-tung-nguoi.png\" width=\"1084\" height=\"461\" title=\"de-bai-thong-ke-cong-viec-tung-nguoi\"><\/p>\n<p>Y\u00eau c\u1ea7u \u0111\u1eb7t ra l\u00e0 ch\u00fang ta c\u1ea7n th\u1ed1ng k\u00ea \u0111\u1ec3 l\u00ean b\u00e1o c\u00e1o t\u1ed5ng h\u1ee3p v\u1ec1 t\u1eebng nh\u00e2n vi\u00ean theo c\u00e1c t\u00ecnh tr\u1ea1ng c\u00f4ng vi\u1ec7c, t\u1ed5ng s\u1ed1 c\u00f4ng vi\u1ec7c c\u1ee7a t\u1eebng ng\u01b0\u1eddi.<\/p>\n<p>\u0110\u1ec3 gi\u1ea3i quy\u1ebft \u0111\u01b0\u1ee3c y\u00eau c\u1ea7u n\u00e0y, ch\u00fang ta c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n theo c\u00e1c b\u01b0\u1edbc 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-6754a7f96eaf2\" 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-ung-dung-ham-search-va-countifs-de-thong-ke-tinh-trang-lam-viec.html#Phan_tich_dac_diem_du_lieu\" title=\"Ph\u00e2n t\u00edch \u0111\u1eb7c \u0111i\u1ec3m d\u1eef li\u1ec7u\">Ph\u00e2n t\u00edch \u0111\u1eb7c \u0111i\u1ec3m d\u1eef li\u1ec7u<\/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-ung-dung-ham-search-va-countifs-de-thong-ke-tinh-trang-lam-viec.html#Tim_nhan_vien_co_ten_trong_cot_Nguoi_thuc_hien_bang_ham_SEARCH\" title=\"T\u00ecm nh\u00e2n vi\u00ean c\u00f3 t\u00ean trong c\u1ed9t Ng\u01b0\u1eddi th\u1ef1c hi\u1ec7n b\u1eb1ng h\u00e0m SEARCH\">T\u00ecm nh\u00e2n vi\u00ean c\u00f3 t\u00ean trong c\u1ed9t Ng\u01b0\u1eddi th\u1ef1c hi\u1ec7n b\u1eb1ng h\u00e0m SEARCH<\/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\/huong-dan-cach-ung-dung-ham-search-va-countifs-de-thong-ke-tinh-trang-lam-viec.html#Buoc_1_Tao_moi_nhan_vien_1_cot_trong_bang_du_lieu\" title=\"B\u01b0\u1edbc 1: T\u1ea1o m\u1ed7i nh\u00e2n vi\u00ean 1 c\u1ed9t trong b\u1ea3ng d\u1eef li\u1ec7u\">B\u01b0\u1edbc 1: T\u1ea1o m\u1ed7i nh\u00e2n vi\u00ean 1 c\u1ed9t trong b\u1ea3ng d\u1eef li\u1ec7u<\/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\/huong-dan-cach-ung-dung-ham-search-va-countifs-de-thong-ke-tinh-trang-lam-viec.html#Buoc_2_Dung_ham_SEARCH_tim_vi_tri_xuat_hien_cua_moi_nhan_vien\" title=\"B\u01b0\u1edbc 2: D\u00f9ng h\u00e0m SEARCH t\u00ecm v\u1ecb tr\u00ed xu\u1ea5t hi\u1ec7n c\u1ee7a m\u1ed7i nh\u00e2n vi\u00ean\">B\u01b0\u1edbc 2: D\u00f9ng h\u00e0m SEARCH t\u00ecm v\u1ecb tr\u00ed xu\u1ea5t hi\u1ec7n c\u1ee7a m\u1ed7i nh\u00e2n vi\u00ean<\/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\/huong-dan-cach-ung-dung-ham-search-va-countifs-de-thong-ke-tinh-trang-lam-viec.html#Buoc_3_Dung_ham_COUNTIF_de_dem_tong_so_cong_viec_thuc_hien_cua_moi_nhan_vien\" title=\"B\u01b0\u1edbc 3: D\u00f9ng h\u00e0m COUNTIF \u0111\u1ec3 \u0111\u1ebfm t\u1ed5ng s\u1ed1 c\u00f4ng vi\u1ec7c th\u1ef1c hi\u1ec7n c\u1ee7a m\u1ed7i nh\u00e2n vi\u00ean\">B\u01b0\u1edbc 3: D\u00f9ng h\u00e0m COUNTIF \u0111\u1ec3 \u0111\u1ebfm t\u1ed5ng s\u1ed1 c\u00f4ng vi\u1ec7c th\u1ef1c hi\u1ec7n c\u1ee7a m\u1ed7i nh\u00e2n vi\u00ean<\/a><\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/huong-dan-cach-ung-dung-ham-search-va-countifs-de-thong-ke-tinh-trang-lam-viec.html#Hoan_thien_bao_cao_tong_hop_voi_ham_COUNTIFS_va_INDIRECT\" title=\"Ho\u00e0n thi\u1ec7n b\u00e1o c\u00e1o t\u1ed5ng h\u1ee3p v\u1edbi h\u00e0m COUNTIFS v\u00e0 INDIRECT\">Ho\u00e0n thi\u1ec7n b\u00e1o c\u00e1o t\u1ed5ng h\u1ee3p v\u1edbi h\u00e0m COUNTIFS v\u00e0 INDIRECT<\/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-7\" href=\"https:\/\/blog.hocexcel.online\/huong-dan-cach-ung-dung-ham-search-va-countifs-de-thong-ke-tinh-trang-lam-viec.html#Dem_ket_qua_bao_cao_voi_ham_COUNTIFS\" title=\"\u0110\u1ebfm k\u1ebft qu\u1ea3 b\u00e1o c\u00e1o v\u1edbi h\u00e0m COUNTIFS\">\u0110\u1ebfm k\u1ebft qu\u1ea3 b\u00e1o c\u00e1o v\u1edbi h\u00e0m COUNTIFS<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/blog.hocexcel.online\/huong-dan-cach-ung-dung-ham-search-va-countifs-de-thong-ke-tinh-trang-lam-viec.html#Xac_dinh_tham_chieu_tung_nhan_vien_voi_ham_ADDRESS\" title=\"X\u00e1c \u0111\u1ecbnh tham chi\u1ebfu t\u1eebng nh\u00e2n vi\u00ean v\u1edbi h\u00e0m ADDRESS\">X\u00e1c \u0111\u1ecbnh tham chi\u1ebfu t\u1eebng nh\u00e2n vi\u00ean v\u1edbi h\u00e0m ADDRESS<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/blog.hocexcel.online\/huong-dan-cach-ung-dung-ham-search-va-countifs-de-thong-ke-tinh-trang-lam-viec.html#Hoan_thien_cong_thuc_COUNTIFS_bang_cach_ket_hop_ham_INDIRECT\" title=\"Ho\u00e0n thi\u1ec7n c\u00f4ng th\u1ee9c COUNTIFS b\u1eb1ng c\u00e1ch k\u1ebft h\u1ee3p h\u00e0m INDIRECT\">Ho\u00e0n thi\u1ec7n c\u00f4ng th\u1ee9c COUNTIFS b\u1eb1ng c\u00e1ch k\u1ebft h\u1ee3p h\u00e0m INDIRECT<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Phan_tich_dac_diem_du_lieu\"><\/span>Ph\u00e2n t\u00edch \u0111\u1eb7c \u0111i\u1ec3m d\u1eef li\u1ec7u<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Trong b\u1ea5t k\u1ef3 y\u00eau c\u1ea7u l\u00e0m vi\u1ec7c n\u00e0o tr\u00ean Excel th\u00ec vi\u1ec7c ph\u00e2n t\u00edch \u0111\u1ec3 hi\u1ec3u \u0111\u1eb7c \u0111i\u1ec3m d\u1eef li\u1ec7u l\u00e0 h\u1ebft s\u1ee9c quan tr\u1ecdng. Khi n\u1eafm \u0111\u01b0\u1ee3c \u0111\u1eb7c \u0111i\u1ec3m d\u1eef li\u1ec7u r\u1ed3i th\u00ec ch\u00fang ta m\u1edbi c\u00f3 th\u1ec3 c\u00f3 ph\u01b0\u01a1ng \u00e1n ph\u00f9 h\u1ee3p gi\u00fap gi\u1ea3i quy\u1ebft c\u00e1c y\u00eau c\u1ea7u \u0111\u00f3. C\u1ee5 th\u1ec3 trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y ch\u00fang ta c\u00f3:<\/p>\n<p><strong>C\u1ed9t Ng\u01b0\u1eddi th\u1ef1c hi\u1ec7n<\/strong>: Nhi\u1ec1u ng\u01b0\u1eddi c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n chung 1 c\u00f4ng vi\u1ec7c, trong \u0111\u00f3 t\u00ean c\u00e1c nh\u00e2n vi\u00ean \u0111\u01b0\u1ee3c c\u00e1ch nhau b\u1edfi d\u1ea5u ch\u1ea5m ph\u1ea9y. C\u00e1c nh\u00e2n vi\u00ean n\u00e0y \u0111\u1ec1u thu\u1ed9c Danh s\u00e1ch nh\u00e2n vi\u00ean trong b\u1ea3ng b\u00e1o c\u00e1o. V\u1edbi vi\u1ec7c tr\u00ecnh b\u00e0y nh\u01b0 v\u1eady th\u00ec kh\u00f4ng th\u1ec3 d\u00f9ng h\u00e0m t\u00ednh tr\u1ef1c ti\u1ebfp t\u1edbi c\u1ed9t n\u00e0y \u0111\u01b0\u1ee3c. =&gt; Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, ch\u00fang ta c\u00f3 th\u1ec3 d\u00f9ng h\u00e0m \u0111\u1ec3 t\u00ecm ki\u1ebfm xem t\u00ean nh\u00e2n vi\u00ean c\u1ea7n b\u00e1o c\u00e1o c\u00f3 xu\u1ea5t hi\u1ec7n trong t\u1eebng c\u00f4ng vi\u1ec7c kh\u00f4ng =&gt; S\u1eed d\u1ee5ng h\u00e0m SEARCH \u0111\u1ec3 t\u00ecm ki\u1ebfm theo chu\u1ed7i k\u00fd t\u1ef1.<\/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\/2024\/12\/ex101_92019-nho-373.gif\" title=\"ex101_92019-nho-373\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><strong>C\u1ed9t T\u00ecnh tr\u1ea1ng<\/strong>: Bao g\u1ed3m 2 t\u00ecnh tr\u1ea1ng l\u00e0 Ho\u00e0n th\u00e0nh v\u00e0 Ch\u01b0a ho\u00e0n th\u00e0nh. Ph\u00f9 h\u1ee3p v\u1edbi y\u00eau c\u1ea7u b\u00e1o c\u00e1o theo \u0111i\u1ec1u ki\u1ec7n l\u00e0 t\u00ecnh tr\u1ea1ng.<\/p>\n<p>Nh\u01b0 v\u1eady b\u00e1o c\u00e1o trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y g\u1ed3m 2 \u0111i\u1ec1u ki\u1ec7n l\u00e0 Nh\u00e2n vi\u00ean v\u00e0 T\u00ecnh tr\u1ea1ng. Do \u0111\u00f3 c\u00f3 th\u1ec3 d\u00f9ng h\u00e0m COUNTIFS \u0111\u1ec3 th\u1ef1c hi\u1ec7n b\u00e1o c\u00e1o d\u1ea1ng \u0111\u1ebfm theo nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Tim_nhan_vien_co_ten_trong_cot_Nguoi_thuc_hien_bang_ham_SEARCH\"><\/span>T\u00ecm nh\u00e2n vi\u00ean c\u00f3 t\u00ean trong c\u1ed9t Ng\u01b0\u1eddi th\u1ef1c hi\u1ec7n b\u1eb1ng h\u00e0m SEARCH<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Do\u00a0trong c\u1ed9t Ng\u01b0\u1eddi th\u1ef1c hi\u1ec7n\u00a0c\u00f3 th\u1ec3 c\u00f3 nhi\u1ec1u h\u01a1n 1 nh\u00e2n vi\u00ean trong 1 \u00f4, m\u00e0 b\u00e1o c\u00e1o y\u00eau c\u1ea7u l\u1eadp ri\u00eang theo t\u1eebng ng\u01b0\u1eddi. Nh\u01b0 v\u1eady ch\u00fang ta ph\u1ea3i s\u1eed d\u1ee5ng c\u1ed9t ph\u1ee5 \u0111\u1ec3 t\u00e1ch t\u1eebng nh\u00e2n vi\u00ean ra. T\u00ecm ki\u1ebfm xem trong m\u1ed7i c\u00f4ng vi\u1ec7c c\u00f3 xu\u1ea5t hi\u1ec7n nh\u00e2n vi\u00ean \u0111\u00f3 kh\u00f4ng.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_1_Tao_moi_nhan_vien_1_cot_trong_bang_du_lieu\"><\/span>B\u01b0\u1edbc 1: T\u1ea1o m\u1ed7i nh\u00e2n vi\u00ean 1 c\u1ed9t trong b\u1ea3ng d\u1eef li\u1ec7u<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>\u0110\u1ec3 th\u1ef1c hi\u1ec7n thao t\u00e1c n\u00e0y, ch\u00fang ta <strong>copy<\/strong> v\u00f9ng danh s\u00e1ch nh\u00e2n vi\u00ean. Sau \u0111\u00f3 t\u1ea1i c\u1ed9t ti\u1ebfp theo trong b\u1ea3ng d\u1eef li\u1ec7u, th\u1ef1c hi\u1ec7n <strong>Paste Special<\/strong>.<\/p>\n<p>Trong c\u1eeda s\u1ed5 Paste Special ch\u1ecdn m\u1ee5c <strong>Transpose<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-20872\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/copy-paste-transpose-theo-tung-cot.png\" width=\"1576\" height=\"463\" title=\"copy-paste-transpose-theo-tung-cot\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_2_Dung_ham_SEARCH_tim_vi_tri_xuat_hien_cua_moi_nhan_vien\"><\/span>B\u01b0\u1edbc 2: D\u00f9ng <a href=\"http:\/\/blog.ezworkapp.com\/ham-find-va-search-voi-cac-cong-thuc-vi-du.html\">h\u00e0m SEARCH<\/a> t\u00ecm v\u1ecb tr\u00ed xu\u1ea5t hi\u1ec7n c\u1ee7a m\u1ed7i nh\u00e2n vi\u00ean<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>T\u1ea1i \u00f4 I3 (b\u1eaft \u0111\u1ea7u cho nh\u00e2n vi\u00ean th\u1ee9 1) d\u00f9ng h\u00e0m nh\u01b0 sau:<\/p>\n<p style=\"padding-left: 30px\">=SEARCH(I$2,$G3)<\/p>\n<p>H\u00e0m n\u00e0y c\u00f3 \u00fd ngh\u0129a: T\u00ecm v\u1ecb tr\u00ed c\u1ee7a \u0111o\u1ea1n text \u00f4 I2 trong \u0111o\u1ea1n text \u00f4 G3. C\u1ed1 \u0111\u1ecbnh d\u00f2ng 2 t\u1ea1i tham chi\u1ebfu I2 \u0111\u1ec3 khi sao ch\u00e9p c\u00f4ng th\u1ee9c cho nh\u1eefng ng\u01b0\u1eddi kh\u00e1c s\u1ebd c\u00f3 th\u1ec3 thay \u0111\u1ed5i c\u1ed9t I, nh\u01b0ng kh\u00f4ng thay \u0111\u1ed5i d\u00f2ng 2 khi sao ch\u00e9p xu\u1ed1ng d\u01b0\u1edbi. T\u01b0\u01a1ng t\u1ef1 nh\u01b0 v\u1eady s\u1ebd c\u1ed1 \u0111\u1ecbnh c\u1ed9t G \u0111\u1ec3 kh\u00f4ng thay \u0111\u1ed5i t\u1ecda \u0111\u1ed9 c\u1ed9t n\u00e0y khi sao ch\u00e9p c\u00f4ng th\u1ee9c sang ph\u1ea3i.<\/p>\n<p>K\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 l\u00e0:<\/p>\n<ul>\n<li>N\u1ebfu c\u00f3 xu\u1ea5t hi\u1ec7n: tr\u1ea3 v\u1ec1 s\u1ed1 th\u1ee9 t\u1ef1 t\u1ea1i v\u1ecb tr\u00ed b\u1eaft \u0111\u1ea7u t\u00ecm th\u1ea5y trong to\u00e0n b\u1ed9 d\u00e3y k\u00fd t\u1ef1<\/li>\n<li>N\u1ebfu kh\u00f4ng xu\u1ea5t hi\u1ec7n: tr\u1ea3 v\u1ec1 l\u1ed7i #Value!<\/li>\n<\/ul>\n<p>Khi filldown c\u00f4ng th\u1ee9c cho c\u1ea3 c\u1ed9t n\u00e0y ta s\u1ebd th\u1ea5y c\u00e1c k\u1ebft qu\u1ea3 l\u00e0:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-20873\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ket-qua-tim-kiem-bang-ham-search-01.png\" width=\"564\" height=\"425\" title=\"ket-qua-tim-kiem-bang-ham-search-01\"><\/p>\n<p>\u0110\u1ec3 tr\u00e1nh l\u1ed7i #VALUE! th\u00ec ch\u00fang ta s\u1eed d\u1ee5ng h\u00e0m b\u1eaby l\u1ed7i l\u00e0 <a href=\"http:\/\/blog.ezworkapp.com\/xu-ly-loi-cong-thuc-excel.html\">h\u00e0m IFERROR<\/a>. Nh\u1eefng gi\u00e1 tr\u1ecb n\u00e0o ra l\u1ed7i #VALUE! s\u1ebd thay b\u1eb1ng 0<\/p>\n<p>C\u00f4ng th\u1ee9c \u0111\u1ea7y \u0111\u1ee7 trong \u00f4 I3 s\u1ebd l\u00e0:<\/p>\n<p style=\"padding-left: 30px\">=IFERROR(SEARCH(I$2,$G3),0)<\/p>\n<p>K\u1ebft qu\u1ea3 sau khi b\u1eaby l\u1ed7i v\u00e0 \u00e1p d\u1ee5ng sang nh\u1eefng nh\u00e2n vi\u00ean kh\u00e1c nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-20874\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ket-qua-tim-kiem-bang-ham-search-sau-khi-bay-loi.png\" width=\"963\" height=\"421\" title=\"ket-qua-tim-kiem-bang-ham-search-sau-khi-bay-loi\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_3_Dung_ham_COUNTIF_de_dem_tong_so_cong_viec_thuc_hien_cua_moi_nhan_vien\"><\/span>B\u01b0\u1edbc 3: D\u00f9ng h\u00e0m COUNTIF \u0111\u1ec3 \u0111\u1ebfm t\u1ed5ng s\u1ed1 c\u00f4ng vi\u1ec7c th\u1ef1c hi\u1ec7n c\u1ee7a m\u1ed7i nh\u00e2n vi\u00ean<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>T\u1ea1i d\u00f2ng T\u1ed5ng c\u1ed9ng, ch\u00fang ta s\u1ebd \u0111\u1ebfm t\u1ed5ng s\u1ed1 c\u00f4ng vi\u1ec7c th\u1ef1c hi\u1ec7n c\u1ee7a m\u1ed7i nh\u00e2n vi\u00ean. \u0110i\u1ec1u n\u00e0y \u0111\u01b0\u1ee3c hi\u1ec3u l\u00e0 m\u1ed7i l\u1ea7n xu\u1ea5t hi\u1ec7n gi\u00e1 tr\u1ecb &gt;0 t\u1ee9c l\u00e0 nh\u00e2n vi\u00ean \u0111\u00f3 c\u00f3 th\u1ef1c hi\u1ec7n c\u00f4ng vi\u1ec7c.<\/p>\n<p>Do \u0111\u00f3 h\u00e0m COUNTIF s\u1ebd \u0111\u1ebfm theo gi\u00e1 tr\u1ecb l\u1edbn h\u01a1n 0<\/p>\n<p>K\u1ebft qu\u1ea3 nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-20875\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tinh-tong-cong-bang-ham-countif.png\" width=\"966\" height=\"447\" title=\"tinh-tong-cong-bang-ham-countif\"><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Hoan_thien_bao_cao_tong_hop_voi_ham_COUNTIFS_va_INDIRECT\"><\/span>Ho\u00e0n thi\u1ec7n b\u00e1o c\u00e1o t\u1ed5ng h\u1ee3p v\u1edbi h\u00e0m COUNTIFS v\u00e0 INDIRECT<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Th\u00f4ng qua c\u00e1c b\u01b0\u1edbc \u1edf tr\u00ean, ch\u00fang ta \u0111\u00e3 c\u00f3 \u0111\u01b0\u1ee3c 1 b\u1ea3ng d\u1eef li\u1ec7u ph\u00f9 h\u1ee3p \u0111\u1ec3 l\u00e0m b\u00e1o c\u00e1o t\u1ed5ng h\u1ee3p, khi m\u1ed7i nh\u00e2n vi\u00ean \u0111\u1ec1u c\u00f3 1 c\u1ed9t d\u1eef li\u1ec7u t\u01b0\u01a1ng \u1ee9ng \u0111\u1ec3 t\u00ednh.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Dem_ket_qua_bao_cao_voi_ham_COUNTIFS\"><\/span>\u0110\u1ebfm k\u1ebft qu\u1ea3 b\u00e1o c\u00e1o v\u1edbi <a href=\"http:\/\/blog.ezworkapp.com\/ham-countifs-dem-theo-nhieu-dieu-kien.html\">h\u00e0m COUNTIFS<\/a><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>C\u00e1ch l\u00e0m th\u00f4ng th\u01b0\u1eddng l\u00e0 ch\u00fang ta s\u1ebd d\u00f9ng ngay h\u00e0m COUNTIFS nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-20876\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/lap-bao-cao-tong-hop-voi-ham-countifs.png\" width=\"559\" height=\"242\" title=\"lap-bao-cao-tong-hop-voi-ham-countifs\"><\/p>\n<p>T\u1ea1i \u00f4 B3 \u0111\u1eb7t h\u00e0m =COUNTIFS($I$3:$I$19,\u201d&gt;0\u2033,$H$3:$H$19,B$2)<\/p>\n<p>Trong \u0111\u00f3:<\/p>\n<ul>\n<li>I3:I19 l\u00e0 v\u00f9ng k\u1ebft qu\u1ea3 t\u01b0\u01a1ng \u1ee9ng v\u1edbi nh\u00e2n vi\u00ean L\u00e2m trong b\u1ea3ng d\u1eef li\u1ec7u \u0111\u00e3 th\u1ef1c hi\u1ec7n \u1edf tr\u00ean<\/li>\n<li>&gt;0 l\u00e0 ch\u1ec9 \u0111\u1ebfm nh\u1eefng gi\u00e1 tr\u1ecb l\u1edbn h\u01a1n 0 trong v\u00f9ng I3:I19<\/li>\n<li>H3:H19 l\u00e0 v\u00f9ng T\u00ecnh tr\u1ea1ng c\u1ee7a c\u00e1c c\u00f4ng vi\u1ec7c<\/li>\n<li>B2 l\u00e0 gi\u00e1 tr\u1ecb t\u1ea1i \u00f4 B2=Ho\u00e0n th\u00e0nh, ch\u1ec9 \u0111\u1ebfm nh\u1eefng gi\u00e1 tr\u1ecb ho\u00e0n th\u00e0nh trong v\u00f9ng H3:H19<\/li>\n<\/ul>\n<p>K\u1ebft qu\u1ea3 thu \u0111\u01b0\u1ee3c l\u00e0 3 =&gt; \u0110\u00fang<\/p>\n<p>Tuy nhi\u00ean ch\u00fang ta th\u1ea5y m\u1ed7i nh\u00e2n vi\u00ean l\u1ea1i \u1edf 1 c\u1ed9t kh\u00e1c nhau. Khi \u0111\u00f3 sao ch\u00e9p c\u00f4ng th\u1ee9c xu\u1ed1ng nh\u1eefng nh\u00e2n vi\u00ean kh\u00e1c th\u00ec ch\u00fang ta ph\u1ea3i s\u1eeda tham chi\u1ebfu c\u1ed9t I th\u00e0nh c\u00e1c c\u1ed9t t\u01b0\u01a1ng \u1ee9ng v\u1edbi nh\u00e2n vi\u00ean ti\u1ebfp theo. Vi\u1ec7c n\u00e0y kh\u00e1 th\u1ee7 c\u00f4ng.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Xac_dinh_tham_chieu_tung_nhan_vien_voi_ham_ADDRESS\"><\/span>X\u00e1c \u0111\u1ecbnh tham chi\u1ebfu t\u1eebng nh\u00e2n vi\u00ean v\u1edbi h\u00e0m ADDRESS<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 t\u1ef1 \u0111\u1ed9ng x\u00e1c \u0111\u1ecbnh t\u1ecda \u0111\u1ed9 tham chi\u1ebfu c\u1ee7a m\u1ed7i nh\u00e2n vi\u00ean, ch\u00fang ta c\u00f3 th\u1ec3 l\u00e0m nh\u01b0 sau:<\/p>\n<p>B\u01b0\u1edbc 1: Ch\u00e8n th\u00eam 1 c\u1ed9t T\u1ecda \u0111\u1ed9 sau c\u1ed9t T\u00ean nh\u00e2n vi\u00ean<\/p>\n<p>B\u01b0\u1edbc 2: S\u1eed d\u1ee5ng h\u00e0m Address k\u1ebft h\u1ee3p h\u00e0m Match cho v\u1ecb tr\u00ed \u00f4 B3 nh\u01b0 sau:<\/p>\n<p>B3=\u201dDebai!\u201d&amp;ADDRESS(2,MATCH($A3,$A$2:$O$2,0))&amp;\u201d:\u201d&amp;ADDRESS(1+COUNTA($H$3:$H$19),MATCH($A3,$A$2:$O$2,0))<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-20877\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/xac-dinh-toa-do-tham-chieu-cua-nhan-vien.png\" width=\"1547\" height=\"507\" title=\"xac-dinh-toa-do-tham-chieu-cua-nhan-vien\"><\/p>\n<ul>\n<li>Trong \u0111\u00f3 s\u1eed d\u1ee5ng <a href=\"http:\/\/blog.ezworkapp.com\/su-dung-ham-match.html\">h\u00e0m\u00a0MATCH<\/a>($A3,$A$2:$O$2,0) \u0111\u1ec3 t\u00ecm v\u1ecb tr\u00ed c\u1ed9t t\u01b0\u01a1ng \u1ee9ng v\u1edbi t\u00ean nh\u00e2n vi\u00ean t\u1ea1i v\u1ecb tr\u00ed \u0111\u1eb7t c\u00f4ng th\u1ee9c. \u1ede \u0111\u00e2y ch\u00fang ta \u0111\u1eb7t c\u00f4ng th\u1ee9c t\u1ea1i B3 n\u00ean x\u00e9t cho nh\u00e2n vi\u00ean t\u1ea1i \u00f4 A3<\/li>\n<li>H\u00e0m Address s\u1ebd tr\u1ea3 v\u1ec1 t\u1ecda \u0111\u1ed9 1 \u00f4 n\u1ebfu x\u00e1c \u0111\u1ecbnh \u0111\u01b0\u1ee3c s\u1ed1 d\u00f2ng, s\u1ed1 c\u1ed9t.<\/li>\n<li>\u0110\u1ec3 x\u00e1c \u0111\u1ecbnh c\u1ee5 th\u1ec3 Sheet n\u00e0o, ch\u00fang ta c\u00f3 th\u1ec3 k\u1ebft h\u1ee3p th\u00eam t\u00ean Sheet v\u00e0 d\u1ea5u ! \u1edf ph\u00eda tr\u01b0\u1edbc \u0111\u1ec3 gi\u00fap tham chi\u1ebfu ch\u00ednh x\u00e1c h\u01a1n. Tr\u01b0\u1eddng h\u1ee3p tham chi\u1ebfu n\u1eb1m c\u00f9ng sheet th\u00ec c\u00f3 th\u1ec3 b\u1ecf qua t\u00ean sheet.<\/li>\n<\/ul>\n<p>T\u01b0\u01a1ng t\u1ef1 nh\u01b0 v\u1eady khi copy c\u00f4ng th\u1ee9c cho c\u00e1c nh\u00e2n vi\u00ean ti\u1ebfp theo ch\u00fang ta c\u00f3 t\u1ecda \u0111\u1ed9 nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-20878\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/xac-dinh-toa-do-tham-chieu-cua-nhan-vien-full.png\" width=\"693\" height=\"200\" title=\"xac-dinh-toa-do-tham-chieu-cua-nhan-vien-full\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Hoan_thien_cong_thuc_COUNTIFS_bang_cach_ket_hop_ham_INDIRECT\"><\/span>Ho\u00e0n thi\u1ec7n c\u00f4ng th\u1ee9c COUNTIFS b\u1eb1ng c\u00e1ch k\u1ebft h\u1ee3p <a href=\"http:\/\/blog.ezworkapp.com\/cach-su-dung-ham-indirect-trong-excel-cong-thuc-va-vi-du-minh-hoa.html\">h\u00e0m INDIRECT<\/a><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Khi \u0111\u00e3 c\u00f3 \u0111\u1ecba ch\u1ec9 t\u1ecda \u0111\u1ed9 tham chi\u1ebfu t\u01b0\u01a1ng \u1ee9ng theo t\u00ean nh\u00e2n vi\u00ean, ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m INDIRECT \u0111\u1ec3 l\u1ea5y n\u1ed9i dung trong c\u00e1c tham chi\u1ebfu \u0111\u00f3 thay v\u00ec ph\u1ea3i s\u1eed d\u1ee5ng tr\u1ef1c ti\u1ebfp \u0111\u1ecba ch\u1ec9 tham chi\u1ebfu.<\/p>\n<p>C\u00f4ng th\u1ee9c COUNTIFS ph\u00eda tr\u00ean c\u00f3 th\u1ec3 s\u1eeda l\u1ea1i l\u00e0:<\/p>\n<p style=\"padding-left: 30px\">C3=COUNTIFS(<strong>INDIRECT($B3)<\/strong>,\u201d&gt;0\u2033,$I$3:$I$19,C$2)<\/p>\n<p style=\"padding-left: 30px\">(So v\u1edbi c\u00f4ng th\u1ee9c \u1edf tr\u00ean th\u00ec c\u00e1c tham chi\u1ebfu b\u1ecb t\u0103ng 1 c\u1ed9t do ch\u00fang ta m\u1edbi ch\u00e8n th\u00eam 1 c\u1ed9t l\u00e0 T\u1ecda \u0111\u1ed9 tham chi\u1ebfu)<\/p>\n<p>L\u00fac n\u00e0y c\u00f3 th\u1ec3 sao ch\u00e9p c\u00f4ng th\u1ee9c t\u1eeb C3 t\u1edbi c\u00e1c nh\u00e2n vi\u00ean kh\u00e1c b\u00ean d\u01b0\u1edbi, sang c\u1ed9t Ch\u01b0a ho\u00e0n th\u00e0nh b\u00ean ph\u1ea3i.<\/p>\n<p>K\u1ebft qu\u1ea3 thu \u0111\u01b0\u1ee3c l\u00e0:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-20879\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/hoan-thien-ham-countifs-ket-hop-indirect.png\" width=\"1601\" height=\"590\" title=\"hoan-thien-ham-countifs-ket-hop-indirect\"><\/p>\n<p>Nh\u01b0 v\u1eady b\u00e1o c\u00e1o \u0111\u00e3 ho\u00e0n th\u00e0nh 1 c\u00e1ch ch\u00ednh x\u00e1c r\u1ed3i ph\u1ea3i kh\u00f4ng n\u00e0o. N\u1ebfu b\u1ea1n c\u1ea7n d\u1eef li\u1ec7u \u0111\u1ec3 l\u00e0m m\u1eabu th\u00ec c\u00f3 th\u1ec3 t\u1ea3i file m\u1eabu t\u1ea1i \u0111\u1ecba ch\u1ec9:<\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/bit.ly\/2O0ByMn\">http:\/\/bit.ly\/2O0ByMn<\/a><\/p>\n<p>C\u00e1c h\u00e0m trong Excel th\u1eadt \u0111a d\u1ea1ng v\u00e0 th\u00fa v\u1ecb \u0111\u00fang kh\u00f4ng n\u00e0o. Vi\u1ec7c k\u1ebft h\u1ee3p linh ho\u1ea1t c\u00e1c h\u00e0m trong Excel s\u1ebd gi\u00fap c\u00f4ng vi\u1ec7c c\u1ee7a ch\u00fang ta \u0111\u01a1n gi\u1ea3n h\u01a1n, t\u1ef1 \u0111\u1ed9ng h\u01a1n v\u00e0 ti\u1ebft ki\u1ec7m th\u1eddi gian, gi\u00fap t\u0103ng hi\u1ec7u qu\u1ea3 c\u00f4ng vi\u1ec7c m\u1ed9t c\u00e1ch \u0111\u00e1ng k\u1ec3. Mu\u1ed1n hi\u1ec3u r\u00f5 v\u1ec1 c\u00e1c h\u00e0m trong Excel, s\u1eed d\u1ee5ng t\u1ed1t c\u00e1c c\u00f4ng c\u1ee5 c\u1ee7a Excel, b\u1ea1n c\u00f3 th\u1ec3 tham gia kh\u00f3a h\u1ecdc <a href=\"http:\/\/bit.ly\/2ODI5O7\">EX101 \u2013 Excel t\u1eeb c\u01a1 b\u1ea3n t\u1edbi chuy\u00ean gia d\u00e0nh cho ng\u01b0\u1eddi \u0111i l\u00e0m<\/a> c\u1ee7a h\u1ec7 th\u1ed1ng H\u1ecdc Excel Online. Hi\u1ec7n nay h\u1ec7 th\u1ed1ng \u0111ang c\u00f3 nhi\u1ec1u \u01b0u \u0111\u00e3i r\u1ea5t h\u1ea5p d\u1eabn cho b\u1ea1n khi \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<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-373.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":4619,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-4618","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-nang-cao"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/4618","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=4618"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/4618\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/4619"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=4618"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=4618"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=4618"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}