{"id":10299,"date":"2025-01-13T08:08:05","date_gmt":"2025-01-13T08:08:05","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/13\/huong-dan-cach-an-loi-n-a-bang-conditional-formatting-trong-excel\/"},"modified":"2025-01-13T08:08:05","modified_gmt":"2025-01-13T08:08:05","slug":"huong-dan-cach-an-loi-n-a-bang-conditional-formatting-trong-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/13\/huong-dan-cach-an-loi-n-a-bang-conditional-formatting-trong-excel\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn c\u00e1ch \u1ea9n l\u1ed7i #N\/A b\u1eb1ng Conditional Formatting trong Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>L\u1ed7i #N\/A l\u00e0 m\u1ed9t l\u1ed7i th\u01b0\u1eddng g\u1eb7p trong Excel.\u00a0L\u1ed7i n\u00e0y cho ch\u00fang ta bi\u1ebft c\u00f4ng th\u1ee9c, h\u00e0m t\u00ednh ra 1 k\u1ebft qu\u1ea3 l\u00e0 1 gi\u00e1 tr\u1ecb kh\u00f4ng t\u1ed3n t\u1ea1i (not avaiable). Do \u0111\u00f3 l\u1ed7i n\u00e0y kh\u00f4ng ph\u1ea3i l\u00e0 1 l\u1ed7i nghi\u00eam tr\u1ecdng. Trong m\u1ed9t s\u1ed1 tr\u01b0\u1eddng h\u1ee3p ch\u00fang ta\u00a0c\u1ea7n\u00a0gi\u1eef l\u1ed7i n\u00e0y nh\u01b0ng kh\u00f4ng mu\u1ed1n nh\u00ecn th\u1ea5y k\u1ebft qu\u1ea3 l\u1ed7i. H\u00e3y c\u00f9ng H\u1ecdc Excel Online t\u00ecm hi\u1ec3u\u00a0c\u00e1ch \u1ea9n l\u1ed7i #N\/A b\u1eb1ng Conditional Formatting trong Excel nh\u00e9.<\/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-6784c9e43eb13\" 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-an-loi-na-bang-conditional-formatting-trong-excel.html#Loi_NA_trong_Excel\" title=\"L\u1ed7i #N\/A trong Excel\">L\u1ed7i #N\/A 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\/huong-dan-cach-an-loi-na-bang-conditional-formatting-trong-excel.html#Cach_giau_loi_NA_bang_Conditional_formatting\" title=\"C\u00e1ch gi\u1ea5u l\u1ed7i #N\/A b\u1eb1ng Conditional formatting\">C\u00e1ch gi\u1ea5u l\u1ed7i #N\/A b\u1eb1ng Conditional formatting<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Loi_NA_trong_Excel\"><\/span>L\u1ed7i #N\/A trong Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>L\u1ed7i #N\/A trong\u00a0Excel c\u00f3 ngh\u0129a\u00a0l\u00e0 trong c\u00f4ng th\u1ee9c b\u1ea1n s\u1eed d\u1ee5ng c\u00f3 ch\u1ee9a n\u1ed9i dung\u00a0kh\u00f4ng c\u00f3 s\u1eb5n trong v\u00f9ng d\u1eef li\u1ec7u, d\u1eabn t\u1edbi kh\u00f4ng th\u1ec3 t\u00ednh to\u00e1n, ho\u00e0n th\u00e0nh c\u00f4ng th\u1ee9c\u00a0\u0111\u01b0\u1ee3c. L\u1ed7i n\u00e0y th\u01b0\u1eddng x\u1ea3y ra khi s\u1eed d\u1ee5ng c\u00e1c h\u00e0m d\u00f2 t\u00ecm, tham chi\u1ebfu.\u00a0\u0110\u1ed1i t\u01b0\u1ee3ng c\u1ea7n d\u00f2 t\u00ecm, tham chi\u1ebfu kh\u00f4ng c\u00f3 s\u1eb5n trong v\u00f9ng c\u1ea7n tra c\u1ee9u n\u00ean b\u00e1o l\u1ed7i #N\/A.<\/p>\n<p>Do\u00a0\u0111\u00f3 v\u1ec1 b\u1ea3n ch\u1ea5t\u00a0l\u1ed7i #N\/A\u00a0kh\u00f4ng ph\u1ea3i l\u00e0 l\u1ed7i sai h\u00e0m, sai c\u00f4ng th\u1ee9c m\u00e0 ch\u1ec9 l\u00e0 kh\u00f4ng t\u00ecm th\u1ea5y\u00a0\u0111\u1ed1i t\u01b0\u1ee3ng c\u1ea7n t\u00ecm. Ch\u00ednh v\u00ec v\u1eady trong nhi\u1ec1u tr\u01b0\u1eddng h\u1ee3p ch\u00fang ta ch\u1ec9 c\u1ea7n\u00a0\u1ea9n l\u1ed7i n\u00e0y\u00a0\u0111i, bi\u1ec7n lu\u1eadn tr\u01b0\u1edbc nh\u1eefng tr\u01b0\u1eddng h\u1ee3p d\u1eef li\u1ec7u kh\u00f4ng t\u1ed3n t\u1ea1i tr\u01b0\u1edbc khi th\u1ef1c hi\u1ec7n c\u00e1c h\u00e0m d\u00f2 t\u00ecm, tham chi\u1ebfu.<\/p>\n<p>C\u00e1c b\u1ea1n c\u00f3 th\u1ec3 t\u00ecm hi\u1ec3u th\u00eam v\u1ec1 c\u00e1ch b\u1eaby l\u1ed7i #N\/A khi kh\u00f4ng mu\u1ed1n ra k\u1ebft qu\u1ea3 l\u1ed7i n\u00e0y t\u1ea1i b\u00e0i vi\u1ebft:<\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-loi-na-trong-excel.html\">H\u01b0\u1edbng d\u1eabn c\u00e1ch \u1ea9n l\u1ed7i #N\/A trong Excel hi\u1ec7u qu\u1ea3 nh\u1ea5t<\/a><\/p>\n<p>Tuy nhi\u00ean trong b\u00e0i vi\u1ebft n\u00e0y, ch\u00fang ta s\u1ebd v\u1eabn s\u1eed d\u1ee5ng l\u1ed7i #N\/A, nh\u01b0ng ch\u1ec9 kh\u00f4ng hi\u1ec3n th\u1ecb l\u1ed7i \u0111\u00f3 ra.<\/p>\n<p>D\u01b0\u1edbi \u0111\u00e2y l\u00e0 1 v\u00ed d\u1ee5 v\u1ec1 l\u1ed7i #N\/A 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-153.gif\" title=\"ex101_92019-nho-153\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-20520\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/loi-na-khi-chon-thang.gif\" width=\"624\" height=\"401\" title=\"loi-na-khi-chon-thang\"><\/p>\n<p>Trong b\u1ea3ng t\u00ednh tr\u00ean, khi thay \u0111\u1ed5i th\u00e1ng \u0111\u01b0\u1ee3c ch\u1ecdn \u1edf \u00f4 F1 th\u00ec k\u1ebft qu\u1ea3 c\u1ed9t E v\u00e0 F s\u1ebd thay \u0111\u1ed5i t\u01b0\u01a1ng \u1ee9ng theo s\u1ed1 th\u1ef1c t\u1ebf, b\u1eaft \u0111\u1ea7u t\u1eeb th\u00e1ng 1 t\u1edbi th\u00e1ng \u0111\u01b0\u1ee3c ch\u1ecdn. Th\u00e1ng n\u00e0o &gt; th\u00e1ng \u0111\u01b0\u1ee3c ch\u1ecdn th\u00ec ra k\u1ebft qu\u1ea3 l\u00e0 #N\/A (ch\u1ee9 kh\u00f4ng ph\u1ea3i l\u00e0 r\u1ed7ng).<\/p>\n<p>C\u00f4ng th\u1ee9c \u00e1p d\u1ee5ng \u1edf c\u00e1c c\u1ed9t:<\/p>\n<ul>\n<li>C\u1ed9t E: E3=IF(A3&lt;=MATCH($F$1,$A$3:$A$14,0),A3,NA())<\/li>\n<li>C\u1ed9t F: F3=IF(A3&lt;=MATCH($F$1,$A$3:$A$14,0),C3,NA())<\/li>\n<\/ul>\n<p>Xem th\u00eam:<\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/blog.ezworkapp.com\/su-dung-ham-match.html\">C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m MATCH<\/a><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_giau_loi_NA_bang_Conditional_formatting\"><\/span>C\u00e1ch gi\u1ea5u l\u1ed7i #N\/A b\u1eb1ng Conditional formatting<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/cach-su-dung-dinh-dang-co-dieu-kien-trong-excel-2010-2013-va-2016.html\">C\u00f4ng c\u1ee5 Conditional formatting<\/a> (vi\u1ebft t\u1eaft l\u00e0 CF) l\u00e0 m\u1ed9t c\u00f4ng c\u1ee5 r\u1ea5t h\u1eefu \u00edch trong Excel gi\u00fap \u0111\u1ecbnh d\u1ea1ng d\u1eef li\u1ec7u theo \u0111i\u1ec1u ki\u1ec7n. Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng Conditional formatting \u0111\u1ec3 ph\u00e1t hi\u1ec7n c\u00e1c \u00f4 l\u1ed7i v\u00e0 \u1ea9n c\u00e1c l\u1ed7i \u0111\u01b0\u1ee3c hi\u1ec3n th\u1ecb.<\/p>\n<p>C\u00e1ch l\u00e0m nh\u01b0 sau:<\/p>\n<p><strong>B\u01b0\u1edbc 1: Ch\u1ecdn b\u1eb3ng d\u1eef li\u1ec7u c\u1ea7n \u00e1p d\u1ee5ng CF<\/strong><\/p>\n<p>Trong v\u00ed d\u1ee5 tr\u00ean, ch\u1ecdn b\u1ea3ng E3:F14<\/p>\n<p><strong>B\u01b0\u1edbc 2: Ch\u1ecdn c\u00f4ng c\u1ee5 CF tr\u00ean th\u1ebb Home. Trong CF ch\u1ecdn m\u1ee5c New Rule<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-20523\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/chon-new-rule-trong-cf-bay-loi-na.png\" width=\"863\" height=\"562\" title=\"chon-new-rule-trong-cf-bay-loi-na\"><\/p>\n<p><strong>B\u01b0\u1edbc 3<\/strong>: Trong c\u1eeda s\u1ed5 <strong>New Formatting Rule<\/strong>, ch\u1ecdn m\u1ee5c <strong>Format only cells that contain<\/strong>.<\/p>\n<p>Trong m\u1ee5c <strong>Format only cells with<\/strong> ch\u1ecdn <strong>Errors<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-20526\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/cf-new-rule-error-cell.png\" width=\"452\" height=\"448\" title=\"cf-new-rule-error-cell\"><\/p>\n<p><strong>B\u01b0\u1edbc 4<\/strong>: Thi\u1ebft l\u1eadp \u0111\u1ecbnh d\u1ea1ng cho \u00f4 ch\u1ee9a l\u1ed7i v\u1edbi font ch\u1eef gi\u1ed1ng v\u1edbi m\u00e0u n\u1ec1n trong \u00f4<\/p>\n<p>Khi thi\u1ebft l\u1eadp m\u00e0u ch\u1eef gi\u1ed1ng v\u1edbi m\u00e0u n\u1ec1n, ch\u00fang ta s\u1ebd kh\u00f4ng c\u00f2n nh\u00ecn th\u1ea5y n\u1ed9i dung \u0111\u00f3 n\u1eefa. \u0110i\u1ec1u n\u00e0y t\u01b0\u01a1ng t\u1ef1 nh\u01b0 vi\u1ec7c nh\u00ecn th\u1ea5y \u00f4 tr\u1ed1ng, nh\u01b0ng th\u1ef1c ch\u1ea5t v\u1eabn c\u00f3 n\u1ed9i dung trong \u00f4<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-20527\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/cf-thiet-lap-font-chu-mau-trang.png\" width=\"1133\" height=\"647\" title=\"cf-thiet-lap-font-chu-mau-trang\"><\/p>\n<p>K\u1ebft qu\u1ea3 cu\u1ed1i c\u00f9ng c\u1ee7a ch\u00fang ta nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-20528\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/ket-qua-cf-giau-loi-na.png\" width=\"704\" height=\"399\" title=\"ket-qua-cf-giau-loi-na\"><\/p>\n<p>Nh\u01b0 v\u1eady ch\u00fang ta ho\u00e0n to\u00e0n c\u00f3 th\u1ec3 gi\u1ea5u \u0111\u01b0\u1ee3c l\u1ed7i #N\/A trong Excel m\u00e0 kh\u00f4ng c\u1ea7n ph\u1ea3i l\u00e0m thay \u0111\u1ed5i b\u1ea3n ch\u1ea5t k\u1ebft qu\u1ea3 trong \u00f4. Vi\u1ec7c n\u00e0y \u0111\u01b0\u1ee3c \u1ee9ng d\u1ee5ng r\u1ea5t nhi\u1ec1u trong vi\u1ec7c l\u1eadp b\u00e1o c\u00e1o \u0111\u1ec3 v\u1ebd bi\u1ec3u \u0111\u1ed3 trong Excel. B\u1edfi d\u1eef li\u1ec7u v\u1ebd bi\u1ec3u \u0111\u1ed3 r\u1ea5t\u00a0d\u1ec5 sai khi c\u00e1c k\u1ebft qu\u1ea3 th\u1ec3 hi\u1ec7n gi\u00e1 tr\u1ecb b\u1eb1ng kh\u00f4ng\u00a0ho\u1eb7c b\u1eb1ng r\u1ed7ng. C\u00e1c b\u1ea1n c\u00f3 th\u1ec3 t\u00ecm hi\u1ec3u th\u00eam trong b\u00e0i vi\u1ebft:<\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/blog.ezworkapp.com\/cach-khac-phuc-loi-chay-ve-0-khi-ve-bieu-do-dang-duong-thang-trong-excel.html\">C\u00e1ch kh\u1eafc ph\u1ee5c l\u1ed7i ch\u1ea1y v\u1ec1 0 khi v\u1ebd bi\u1ec3u \u0111\u1ed3 d\u1ea1ng \u0111\u01b0\u1eddng th\u1eb3ng trong Excel<\/a><\/p>\n<p>T\u1ea3i v\u1ec1 file m\u1eabu t\u1ea1i \u0111\u1ecba ch\u1ec9:\u00a0<a href=\"http:\/\/bit.ly\/2LCwz3H\">http:\/\/bit.ly\/2LCwz3H<\/a><\/p>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng t\u1ed1t c\u00e1c h\u00e0m, c\u00e1c c\u00f4ng c\u1ee5 c\u1ee7a Excel th\u00ec c\u00e1c b\u1ea1n h\u00e3y tham gia kh\u00f3a h\u1ecdc\u00a0<a href=\"http:\/\/bit.ly\/2Luw6E0\">EX101 \u2013 Excel t\u1eeb c\u01a1 b\u1ea3n t\u1edbi chuy\u00ean gia<\/a>\u00a0c\u1ee7a H\u1ecdc Excel Online. Trong kh\u00f3a h\u1ecdc n\u00e0y b\u1ea1n s\u1ebd \u0111\u01b0\u1ee3c t\u00ecm hi\u1ec3u c\u00e1ch s\u1eed d\u1ee5ng c\u00e1c c\u00f4ng c\u1ee5: conditional formatting, data validation, pivot table\u2026 \u0111\u1ec3 \u1ee9ng d\u1ee5ng v\u00e0o trong c\u00f4ng vi\u1ec7c. Ngo\u00e0i ra b\u1ea1n c\u00f2n bi\u1ebft c\u00e1ch s\u1eed d\u1ee5ng c\u00e1c h\u00e0m trong excel, t\u1eeb c\u00e1c h\u00e0m c\u01a1 b\u1ea3n cho t\u1edbi c\u00e1c h\u00e0m n\u00e2ng cao, ph\u1ee9c t\u1ea1p. H\u00e3y tham gia ngay \u0111\u1ec3 nh\u1eadn \u0111\u01b0\u1ee3c nh\u1eefng \u01b0u \u0111\u00e3i t\u1eeb h\u1ec7 th\u1ed1ng. 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\/2025\/01\/ex101_92019-nho-153.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":10300,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-10299","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\/10299","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=10299"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/10299\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/10300"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=10299"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=10299"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=10299"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}