{"id":10905,"date":"2025-01-15T00:34:57","date_gmt":"2025-01-15T00:34:57","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/15\/hoc-excel-co-ban-cach-sua-loi-value-khi-su-dung-ham-average-va-ham-sum-trong-excel\/"},"modified":"2025-01-15T00:34:57","modified_gmt":"2025-01-15T00:34:57","slug":"hoc-excel-co-ban-cach-sua-loi-value-khi-su-dung-ham-average-va-ham-sum-trong-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/15\/hoc-excel-co-ban-cach-sua-loi-value-khi-su-dung-ham-average-va-ham-sum-trong-excel\/","title":{"rendered":"H\u1ecdc Excel C\u01a1 B\u1ea3n: c\u00e1ch s\u1eeda l\u1ed7i #VALUE! khi s\u1eed d\u1ee5ng h\u00e0m AVERAGE v\u00e0 h\u00e0m SUM trong Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Trong khi l\u00e0m vi\u1ec7c ho\u1eb7c h\u1ecdc h\u00e0m Excel, ch\u00fang ta c\u00f3 th\u1ec3 r\u1ea5t b\u1ef1c m\u00ecnh n\u1ebfu g\u1eb7p ph\u1ea3i nh\u1eefng l\u1ed7i kh\u00f4ng mong mu\u1ed1n v\u00e0 l\u1ed7i <strong>#VALUE!<\/strong> l\u00e0 m\u1ed9t trong nh\u1eefng l\u1ed7i hay g\u1eb7p nh\u1ea5t trong Excel. Trong b\u00e0i vi\u1ebft n\u00e0y, H\u1ecdc Excel Online s\u1ebd chia s\u1ebb v\u1edbi c\u00e1c b\u1ea1n t\u00ecm hi\u1ec3u \u0111\u01b0\u1ee3c nguy\u00ean nh\u00e2n r\u1ed3i t\u1eeb \u0111\u00f3 \u0111\u01b0a ra c\u00e1ch s\u1eeda l\u1ed7i #VALUE! khi s\u1eed d\u1ee5ng h\u00e0m AVERAGE v\u00e0 h\u00e0m SUM trong Excel.<\/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-678702b033eb6\" 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\/hoc-excel-co-ban-cach-sua-loi-value-khi-su-dung-ham-average-va-ham-sum-trong-excel.html#Nguyen_nhan_gay_ra_loi_VALUE_trong_Excel\" title=\"Nguy\u00ean nh\u00e2n g\u00e2y ra l\u1ed7i #VALUE! trong Excel\">Nguy\u00ean nh\u00e2n g\u00e2y ra l\u1ed7i #VALUE! trong Excel<\/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\/hoc-excel-co-ban-cach-sua-loi-value-khi-su-dung-ham-average-va-ham-sum-trong-excel.html#Cach_khac_phuc_loi_VALUE_trong_Excel\" title=\"C\u00e1ch kh\u1eafc ph\u1ee5c l\u1ed7i #VALUE! trong Excel\">C\u00e1ch kh\u1eafc ph\u1ee5c l\u1ed7i #VALUE! trong Excel<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Nguyen_nhan_gay_ra_loi_VALUE_trong_Excel\"><\/span>Nguy\u00ean nh\u00e2n g\u00e2y ra l\u1ed7i #VALUE! trong Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Khi s\u1eed d\u1ee5ng h\u00e0m AVERAGE ho\u1eb7c <a href=\"http:\/\/blog.ezworkapp.com\/cac-vi-du-ve-ham-sum-trong-excel-cach-chi-tinh-tong-cac-cot-hang-hoac-o-duoc-hien-thi.html\" target=\"_blank\" rel=\"noopener noreferrer\">h\u00e0m SUM trong Excel<\/a>, b\u1ea1n c\u00f3 th\u1ec3 g\u1eb7p ph\u1ea3i l\u1ed7i <strong>#VALUE!\u00a0<\/strong>n\u1ebfu trong v\u00f9ng t\u00ednh to\u00e1n c\u1ee7a 2 h\u00e0m n\u00e0y c\u00f3 gi\u00e1 tr\u1ecb <strong>#VALUE!\u00a0<\/strong>v\u00ed d\u1ee5 nh\u01b0 sau:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2017\/11\/cach-sua-loi-value-khi-su-dung-average-va-ham-sum.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11552\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/cach-sua-loi-value-khi-su-dung-average-va-ham-sum.png\" alt=\"cach-sua-loi-value-khi-su-dung-average-va-ham-sum\" width=\"493\" height=\"181\" title=\"cach-sua-loi-value-khi-su-dung-average-va-ham-sum\"><\/a><\/p>\n<p>Nh\u01b0 trong h\u00ecnh minh ho\u1ea1, b\u1ea1n c\u00f3 th\u1ec3 th\u1ea5y r\u1eb1ng \u00f4 E2 \u0111ang c\u00f3 l\u1ed7i <strong>#VALUE!<\/strong> b\u1edfi v\u00ec trong v\u00f9ng B2:D2 c\u00f3 \u00f4 C2 \u0111ang ch\u1ee9a gi\u00e1 tr\u1ecb <strong>#VALUE!<\/strong><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_khac_phuc_loi_VALUE_trong_Excel\"><\/span>C\u00e1ch kh\u1eafc ph\u1ee5c l\u1ed7i #VALUE! trong Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Nh\u01b0 v\u1eady \u0111\u1ec3 kh\u1eafc ph\u1ee5c l\u1ed7i n\u00e0y, ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c sau \u0111\u00e2y trong \u00f4 E2:<\/p>\n<p><strong>=AVERAGE(IF(ISERROR(B2:D2),\u201d\u201d,B2:D2))<\/strong><\/p>\n<p>B\u1edfi v\u00ec c\u00f4ng th\u1ee9c n\u00e0y l\u00e0 <a href=\"http:\/\/blog.ezworkapp.com\/cong-thuc-mang-trong-excel.html\" target=\"_blank\" rel=\"noopener noreferrer\">c\u00f4ng th\u1ee9c m\u1ea3ng<\/a>, n\u00ean \u0111\u1ec3 nh\u1eadp c\u00f4ng th\u1ee9c n\u00e0y, b\u1ea1n c\u1ea7n b\u1ea5m t\u1ed5 h\u1ee3p ph\u00edm <strong>CTRL + SHIFT + ENTER<\/strong> \u0111\u1ec3 nh\u1eadn \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 nh\u01b0 sau:<\/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-239.gif\" title=\"ex101_92019-nho-239\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2017\/11\/cach-sua-loi-value-khi-su-dung-average-va-ham-sum-02.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11553\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/cach-sua-loi-value-khi-su-dung-average-va-ham-sum-02.png\" alt=\"cach-sua-loi-value-khi-su-dung-average-va-ham-sum-02\" width=\"520\" height=\"176\" title=\"cach-sua-loi-value-khi-su-dung-average-va-ham-sum-02\"><\/a><\/p>\n<p>T\u1ea5t nhi\u00ean c\u00e1ch x\u1eed l\u00fd l\u1ed7i n\u00e0y ch\u00fang ta c\u00f3 th\u1ec3 \u00e1p d\u1ee5ng t\u01b0\u01a1ng t\u1ef1 cho h\u00e0m SUM:<\/p>\n<p><strong>=SUM(IF(ISERROR(B2:D2),\u201d\u201d,B2:D2))<\/strong><\/p>\n<p>H\u01a1n n\u1eefa, c\u00e1ch l\u00e0m n\u00e0y kh\u00f4ng nh\u1eefng x\u1eed l\u00fd \u0111\u01b0\u1ee3c \u0111\u01b0\u1ee3c cho l\u1ed7i <strong>#VALUE!<\/strong>, m\u00e0 c\u00f2n x\u1eed l\u00fd \u0111\u01b0\u1ee3c c\u00e1c l\u1ed7i kh\u00e1c nh\u01b0 <strong>#N\/A, #NULL!, #DIV\/0<\/strong><\/p>\n<p>\u0110\u1ec3 c\u00f3 ki\u1ebfn th\u1ee9c t\u1ed5ng h\u1ee3p v\u1ec1 t\u1ea5t c\u1ea3 c\u00e1c l\u1ed7i th\u01b0\u1eddng g\u1eb7p khi s\u1eed d\u1ee5ng Excel, c\u00e1c b\u1ea1n c\u00f3 th\u1ec3 theo d\u00f5i b\u00e0i vi\u1ebft sau \u0111\u00e2y:<\/p>\n<p><strong><a href=\"https:\/\/blog.hocexcel.online\/loi-value-ref-num-na-div0-name-null-trong-excel-va-cach-sua.html\" target=\"_blank\" rel=\"noopener noreferrer\">L\u1ed7i #VALUE!, #REF!, #NUM, #N\/A, #DIV\/0!, #NAME?, #NULL! trong Excel v\u00e0 c\u00e1ch s\u1eeda<\/a><\/strong><\/p>\n<p>\u0110\u1eebng qu\u00ean b\u1ea1n lu\u00f4n lu\u00f4n c\u00f3 th\u1ec3 tham kh\u1ea3o c\u00e1c kho\u00e1 h\u1ecdc c\u00f9ng v\u1edbi chuy\u00ean gia c\u00f3 nhi\u1ec1u n\u0103m kinh nghi\u1ec7m trong l\u0129nh v\u1ef1c gi\u1ea3ng d\u1ea1y v\u00e0 t\u01b0 v\u1ea5n cho c\u00e1c doanh nghi\u1ec7p n\u01b0\u1edbc ngo\u00e0i trong <a href=\"http:\/\/khoa-hoc-excel-co-ban1.hocexcel.online\/\" target=\"_blank\" rel=\"noopener noreferrer\">kho\u00e1 h\u1ecdc Excel t\u1eeb c\u01a1 b\u1ea3n t\u1edbi n\u00e2ng cao<\/a> c\u1ee7a H\u1ecdc Excel Online. Th\u00f4ng tin chi ti\u1ebft v\u1ec1 kh\u00f3a h\u1ecdc, c\u00e1c b\u1ea1n tham kh\u1ea3o t\u1ea1i \u0111\u00e2y:<\/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-239.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":10906,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-10905","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\/10905","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=10905"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/10905\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/10906"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=10905"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=10905"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=10905"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}