{"id":388641,"date":"2024-09-21T15:32:23","date_gmt":"2024-09-21T08:32:23","guid":{"rendered":"https:\/\/hoanghamobile.com\/tin-tuc\/?p=388641"},"modified":"2024-09-21T15:32:23","modified_gmt":"2024-09-21T08:32:23","slug":"loi-na","status":"publish","type":"post","link":"https:\/\/hoanghamobile.com\/tin-tuc\/loi-na\/","title":{"rendered":"C\u00e1ch s\u1eeda l\u1ed7i #N\/A trong Excel \u0111\u01a1n gi\u1ea3n, d\u1ec5 hi\u1ec3u nh\u1ea5t"},"content":{"rendered":"

Khi l\u00e0m vi\u1ec7c v\u1edbi Excel r\u1ea5t nhi\u1ec1u ng\u01b0\u1eddi g\u1eb7p ph\u1ea3i tr\u01b0\u1eddng h\u1ee3p b\u1ecb b\u00e1o l\u1ed7i NA. \u0110i\u1ec1u n\u00e0y c\u00f3 th\u1ec3 xu\u1ea5t ph\u00e1t t\u1eeb nhi\u1ec1u nguy\u00ean nh\u00e2n kh\u00e1c nhau v\u00e0 b\u1ea1n c\u1ea7n ph\u1ea3i bi\u1ebft \u0111\u01b0\u1ee3c l\u1ed7i \u0111\u1ec3 c\u00f3 c\u00e1ch s\u1eeda l\u1ed7i #N\/A ph\u00f9 h\u1ee3p nh\u1ea5t. V\u00e0 trong b\u00e0i vi\u1ebft n\u00e0y, Ho\u00e0ng H\u00e0 Mobile s\u1ebd chia s\u1ebb cho b\u1ea1n chi ti\u1ebft th\u1ee7 thu\u1eadt \u0111\u1ec3 nhanh ch\u00f3ng kh\u1eafc ph\u1ee5c \u0111\u01b0\u1ee3c l\u1ed7i Excel tr\u00ean m\u00e1y t\u00ednh nh\u00e9.<\/span><\/p>\n

L\u1ed7i NA trong Excel l\u00e0 g\u00ec?<\/b><\/h2>\n

Trong qu\u00e1 tr\u00ecnh l\u00e0m vi\u1ec7c v\u1edbi Excel, kh\u00f4ng \u00edt ng\u01b0\u1eddi g\u1eb7p ph\u1ea3i l\u1ed7i #N\/A, m\u1ed9t trong nh\u1eefng l\u1ed7i ph\u1ed5 bi\u1ebfn v\u00e0 g\u00e2y kh\u00f3 ch\u1ecbu. L\u1ed7i n\u00e0y c\u00f3 ngh\u0129a l\u00e0 “kh\u00f4ng c\u00f3 s\u1eb5n – Not Available” v\u00e0 th\u01b0\u1eddng x\u1ea3y ra khi Excel kh\u00f4ng th\u1ec3 t\u00ecm th\u1ea5y d\u1eef li\u1ec7u c\u1ea7n tra c\u1ee9u ho\u1eb7c x\u1eed l\u00fd trong qu\u00e1 tr\u00ecnh t\u00ednh to\u00e1n d\u1eef li\u1ec7u.<\/span><\/p>\n

\"loi-na-1\"<\/picture><\/p>\n

\u0110\u1ec3 b\u1ea1n d\u1ec5 h\u00ecnh dung h\u01a1n v\u1ec1 l\u1ed7i n\u00e0y, Ho\u00e0ng H\u00e0 Mobile cung c\u1ea5p cho b\u1ea1n m\u1ed9t v\u00ed d\u1ee5 v\u1ec1 l\u1ed7i Excel kh\u00f4ng th\u1ec3 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 nh\u01b0 h\u00ecnh \u1ea3nh minh h\u1ecda \u1edf tr\u00ean nh\u00e9.<\/span><\/p>\n

L\u1ed7i #N\/A th\u01b0\u1eddng xu\u1ea5t hi\u1ec7n \u1edf h\u00e0m n\u00e0o trong Excel?<\/b><\/h2>\n

L\u1ed7i #N\/A th\u01b0\u1eddng xu\u1ea5t hi\u1ec7n \u1edf c\u00e1c h\u00e0m tra c\u1ee9u v\u00e0 tham chi\u1ebfu trong Excel, \u0111\u1eb7c bi\u1ec7t l\u00e0 khi kh\u00f4ng t\u00ecm th\u1ea5y k\u1ebft qu\u1ea3 ph\u00f9 h\u1ee3p. Trong \u0111\u00f3, b\u1ea1n \u0111\u1ecdc n\u00ean ch\u00fa \u00fd \u0111\u1ebfn c\u00e1ch s\u1eed d\u1ee5ng c\u1ee7a nh\u1eefng h\u00e0m sau \u0111\u00e2y \u0111\u1ec3 h\u1ea1n ch\u1ebf \u0111\u01b0\u1ee3c l\u1ed7i tham chi\u1ebfu kh\u00f4ng c\u00f3 gi\u00e1 tr\u1ecb nh\u00e9.<\/span><\/p>\n

H\u00e0m VLOOKUP<\/b><\/h3>\n

H\u00e0m VLOOKUP (Vertical Lookup) \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u0111\u1ec3 t\u00ecm ki\u1ebfm m\u1ed9t gi\u00e1 tr\u1ecb c\u1ee5 th\u1ec3 trong c\u1ed9t \u0111\u1ea7u ti\u00ean c\u1ee7a m\u1ed9t b\u1ea3ng d\u1eef li\u1ec7u v\u00e0 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng t\u1eeb c\u1ed9t kh\u00e1c trong c\u00f9ng m\u1ed9t h\u00e0ng. Tuy nhi\u00ean, khi VLOOKUP kh\u00f4ng th\u1ec3 t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb c\u1ea7n tra c\u1ee9u, Excel s\u1ebd tr\u1ea3 v\u1ec1 l\u1ed7i #N\/A.<\/span><\/p>\n

\"loi-na-2\"<\/picture><\/p>\n

Nguy\u00ean nh\u00e2n g\u00e2y ra l\u1ed7i #N\/A trong h\u00e0m VLOOKUP th\u01b0\u1eddng th\u1ea5y l\u00e0 s\u1ef1 kh\u00e1c bi\u1ec7t v\u1ec1 \u0111\u1ecbnh d\u1ea1ng d\u1eef li\u1ec7u. N\u1ebfu gi\u00e1 tr\u1ecb tra c\u1ee9u v\u00e0 gi\u00e1 tr\u1ecb trong b\u1ea3ng d\u1eef li\u1ec7u c\u00f3 \u0111\u1ecbnh d\u1ea1ng kh\u00e1c nhau (v\u00ed d\u1ee5, m\u1ed9t gi\u00e1 tr\u1ecb l\u00e0 s\u1ed1 v\u00e0 gi\u00e1 tr\u1ecb kia l\u00e0 v\u0103n b\u1ea3n), h\u00e0m VLOOKUP s\u1ebd kh\u00f4ng th\u1ec3 kh\u1edbp \u0111\u01b0\u1ee3c v\u00e0 tr\u1ea3 v\u1ec1 l\u1ed7i #N\/A.\u00a0<\/span><\/p>\n

Ho\u1eb7c b\u1ea1n c\u0169ng s\u1ebd th\u1ea5y l\u1ed7i n\u00e0y xu\u1ea5t hi\u1ec7n khi \u0111\u1ed1i s\u1ed1 cu\u1ed1i c\u00f9ng c\u1ee7a h\u00e0m VLOOKUP l\u00e0 TRUE (tra c\u1ee9u g\u1ea7n \u0111\u00fang). H\u00e0m s\u1ebd tr\u1ea3 v\u1ec1 #N\/A n\u1ebfu kh\u00f4ng t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb g\u1ea7n \u0111\u00fang ph\u00f9 h\u1ee3p.<\/span><\/p>\n

H\u00e0m MATCH<\/b><\/h3>\n

L\u1ed7i NA trong Excel<\/b> th\u01b0\u1eddng g\u1eb7p khi s\u1eed d\u1ee5ng h\u00e0m MATCH, m\u1ed9t h\u00e0m \u0111\u01b0\u1ee3c d\u00f9ng \u0111\u1ec3 t\u00ecm v\u1ecb tr\u00ed c\u1ee7a m\u1ed9t gi\u00e1 tr\u1ecb trong m\u1ed9t ph\u1ea1m vi ho\u1eb7c m\u1ea3ng d\u1eef li\u1ec7u. MATCH tr\u1ea3 v\u1ec1 v\u1ecb tr\u00ed c\u1ee7a gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm, nh\u01b0ng n\u1ebfu kh\u00f4ng t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb \u0111\u00f3, h\u00e0m s\u1ebd tr\u1ea3 v\u1ec1 l\u1ed7i #N\/A, b\u00e1o hi\u1ec7u r\u1eb1ng kh\u00f4ng c\u00f3 k\u1ebft qu\u1ea3 ph\u00f9 h\u1ee3p.<\/span><\/p>\n

\"loi-na-3\"<\/picture><\/p>\n

Theo \u0111\u00f3, nguy\u00ean nh\u00e2n c\u1ee7a l\u1ed7i n\u00e0y l\u00e0 do MATCH c\u00f3 ba t\u00f9y ch\u1ecdn t\u00ecm ki\u1ebfm. 1 (tra c\u1ee9u gi\u00e1 tr\u1ecb l\u1edbn h\u01a1n ho\u1eb7c b\u1eb1ng), 0 (tra c\u1ee9u gi\u00e1 tr\u1ecb ch\u00ednh x\u00e1c), v\u00e0 -1 (tra c\u1ee9u gi\u00e1 tr\u1ecb nh\u1ecf h\u01a1n ho\u1eb7c b\u1eb1ng). N\u1ebfu t\u00f9y ch\u1ecdn tra c\u1ee9u kh\u00f4ng kh\u1edbp v\u1edbi y\u00eau c\u1ea7u t\u00ecm ki\u1ebfm c\u1ee7a b\u1ea1n, h\u00e0m s\u1ebd tr\u1ea3 v\u1ec1 l\u1ed7i #N\/A.<\/span><\/p>\n

H\u00e0m INDEX<\/b><\/h3>\n

H\u00e0m INDEX l\u00e0 m\u1ed9t h\u00e0m m\u1ea1nh m\u1ebd cho ph\u00e9p l\u1ea5y gi\u00e1 tr\u1ecb t\u1eeb m\u1ed9t m\u1ea3ng ho\u1eb7c b\u1ea3ng d\u1eef li\u1ec7u d\u1ef1a tr\u00ean v\u1ecb tr\u00ed h\u00e0ng v\u00e0 c\u1ed9t. H\u00e0m INDEX th\u01b0\u1eddng kh\u00f4ng t\u1ef1 g\u00e2y ra <\/span>l\u1ed7i NA<\/b>. Nh\u01b0ng khi k\u1ebft h\u1ee3p v\u1edbi c\u00e1c h\u00e0m kh\u00e1c nh\u01b0 MATCH ho\u1eb7c khi \u0111\u1ed1i s\u1ed1 nh\u1eadp kh\u00f4ng h\u1ee3p l\u1ec7, l\u1ed7i n\u00e0y s\u1ebd x\u1ea3y ra.\u00a0<\/span><\/p>\n

\"loi-na-4\"<\/picture><\/p>\n

B\u1ea1n c\u00f3 th\u1ec3 \u0111\u1ec3 \u00fd th\u1ea5y n\u1ebfu b\u1ea1n nh\u1eadp gi\u00e1 tr\u1ecb h\u00e0ng ho\u1eb7c c\u1ed9t v\u01b0\u1ee3t qu\u00e1 ph\u1ea1m vi c\u1ee7a m\u1ea3ng d\u1eef li\u1ec7u, h\u00e0m INDEX s\u1ebd tr\u1ea3 v\u1ec1 l\u1ed7i #N\/A. V\u00ed d\u1ee5, n\u1ebfu b\u1ea1n y\u00eau c\u1ea7u l\u1ea5y gi\u00e1 tr\u1ecb t\u1eeb h\u00e0ng 10 trong m\u1ed9t ph\u1ea1m vi ch\u1ec9 c\u00f3 5 h\u00e0ng, l\u1ed7i #N\/A s\u1ebd xu\u1ea5t hi\u1ec7n.<\/span><\/p>\n

Nguy\u00ean nh\u00e2n c\u1ee7a l\u1ed7i NA trong Excel l\u00e0 g\u00ec? C\u00e1ch kh\u1eafc ph\u1ee5c<\/b><\/h2>\n

L\u1ed7i #N\/A trong Excel th\u01b0\u1eddng xu\u1ea5t hi\u1ec7n khi m\u1ed9t c\u00f4ng th\u1ee9c ho\u1eb7c h\u00e0m kh\u00f4ng th\u1ec3 t\u00ecm th\u1ea5y k\u1ebft qu\u1ea3 ph\u00f9 h\u1ee3p. D\u01b0\u1edbi \u0111\u00e2y, Ho\u00e0ng H\u00e0 Mobile \u0111\u00e3 t\u1ed5ng h\u1ee3p \u0111\u01b0\u1ee3c m\u1ed9t s\u1ed1 nguy\u00ean nh\u00e2n ph\u1ed5 bi\u1ebfn g\u00e2y ra l\u1ed7i n\u00e0y, m\u1eddi b\u1ea1n c\u00f9ng xem qua nh\u00e9.<\/span><\/p>\n

L\u1ed7i do v\u00f9ng t\u00ecm ki\u1ebfm kh\u00f4ng \u0111\u1ea7y \u0111\u1ee7<\/b><\/h3>\n

Khi s\u1eed d\u1ee5ng c\u00e1c h\u00e0m, b\u1ea1n c\u1ea7n \u0111\u1ea3m b\u1ea3o r\u1eb1ng gi\u00e1 tr\u1ecb b\u1ea1n mu\u1ed1n tra c\u1ee9u n\u1eb1m trong v\u00f9ng d\u1eef li\u1ec7u m\u00e0 b\u1ea1n ch\u1ec9 \u0111\u1ecbnh. N\u1ebfu v\u00f9ng t\u00ecm ki\u1ebfm kh\u00f4ng bao g\u1ed3m gi\u00e1 tr\u1ecb c\u1ea7n thi\u1ebft, Excel s\u1ebd kh\u00f4ng th\u1ec3 t\u00ecm th\u1ea5y k\u1ebft qu\u1ea3 v\u00e0 tr\u1ea3 v\u1ec1 l\u1ed7i #N\/A!. V\u00ed d\u1ee5, trong h\u00e0m VLOOKUP, gi\u00e1 tr\u1ecb c\u1ea7n tra c\u1ee9u ph\u1ea3i n\u1eb1m trong c\u1ed9t \u0111\u1ea7u ti\u00ean c\u1ee7a b\u1ea3ng tra c\u1ee9u. N\u1ebfu ph\u1ea1m vi ch\u1ec9 \u0111\u1ecbnh kh\u00f4ng bao g\u1ed3m c\u1ed9t n\u00e0y, l\u1ed7i #N\/A! s\u1ebd x\u1ea3y ra.<\/span><\/p>\n

B\u00ean c\u1ea1nh \u0111\u00f3, l\u1ed7i n\u00e0y c\u0169ng xu\u1ea5t hi\u1ec7n n\u1ebfu b\u1ea1n nh\u1eadp ph\u1ea1m vi t\u00ecm ki\u1ebfm kh\u00f4ng ch\u00ednh x\u00e1c. V\u00ed d\u1ee5 nh\u01b0 ch\u1ec9 \u0111\u1ecbnh m\u1ed9t v\u00f9ng d\u1eef li\u1ec7u nh\u1ecf h\u01a1n so v\u1edbi d\u1eef li\u1ec7u th\u1ef1c t\u1ebf c\u1ea7n tra c\u1ee9u, ho\u1eb7c b\u1ecf s\u00f3t m\u1ed9t ph\u1ea7n d\u1eef li\u1ec7u quan tr\u1ecdng, Excel s\u1ebd kh\u00f4ng th\u1ec3 t\u00ecm ra k\u1ebft qu\u1ea3 ph\u00f9 h\u1ee3p.<\/span><\/p>\n

V\u00ed d\u1ee5<\/b><\/h4>\n

Trong b\u1ea3ng d\u1eef li\u1ec7u sau \u0111\u00e2y, gi\u1ea3 s\u1eed b\u1ea1n c\u1ea7n t\u00ecm th\u00f4ng tin v\u1ec1 \u201cXo\u00e0i\u201d v\u00e0 \u1edf b\u01b0\u1edbc \u0111\u1ea7u ti\u00ean, b\u1ea1n s\u1ebd nh\u1eadp d\u1eef li\u1ec7u \u201c=VLOOKUP(“XO\u00c0I”,A2:D6,4,0)\u201d.<\/span><\/p>\n

\"loi-na-5\"<\/picture><\/p>\n

Ti\u1ebfp \u0111\u00f3, b\u1ea1n s\u1ebd nh\u1ea5n ch\u1ecdn ph\u00edm \u201cEnter\u201d v\u00e0 Excel s\u1ebd tr\u1ea3 l\u1ea1i d\u1eef li\u1ec7u <\/span>l\u1ed7i NA<\/b>! v\u00ec kh\u00f4ng c\u00f3 th\u00f4ng tin trong b\u1ea3ng d\u1eef li\u1ec7u.<\/span><\/p>\n

\"loi-na-6\"<\/picture><\/p>\n

C\u00e1ch kh\u1eafc ph\u1ee5c<\/b><\/h4>\n

B\u1ea1n h\u00e3y \u0111\u1ea3m b\u1ea3o r\u1eb1ng v\u00f9ng d\u1eef li\u1ec7u m\u00e0 b\u1ea1n ch\u1ecdn bao g\u1ed3m to\u00e0n b\u1ed9 c\u00e1c c\u1ed9t ho\u1eb7c h\u00e0ng c\u00f3 ch\u1ee9a d\u1eef li\u1ec7u m\u00e0 b\u1ea1n mu\u1ed1n tra c\u1ee9u. Ho\u1eb7c n\u1ebfu l\u1ed7i x\u1ea3y ra do ph\u1ea1m vi qu\u00e1 h\u1eb9p, b\u1ea1n c\u1ea7n m\u1edf r\u1ed9ng ph\u1ea1m vi t\u00ecm ki\u1ebfm \u0111\u1ec3 bao g\u1ed3m to\u00e0n b\u1ed9 c\u00e1c c\u1ed9t ho\u1eb7c h\u00e0ng li\u00ean quan.\u00a0<\/span><\/p>\n

\"loi-na-7\"<\/picture><\/p>\n

Nh\u01b0 v\u00ed d\u1ee5 \u1edf tr\u00ean, b\u1ea1n h\u00e3y ch\u1ec9nh l\u1ea1i \u0111\u1ed1i t\u01b0\u1ee3ng c\u1ea7n t\u00ecm l\u00e0 S\u1ea7u Ri\u00eang v\u00e0 s\u1eed d\u1ee5ng h\u00e0m \u201c=VLOOKUP(“S\u1ea7u Ri\u00eang”,B2:D6,2,0)\u201d, nhanh ch\u00f3ng k\u1ebft qu\u1ea3 s\u1ebd hi\u1ec3n th\u1ecb ra nh\u01b0 h\u00ecnh \u1ea3nh minh h\u1ecda.<\/span><\/p>\n

L\u1ed7i do kh\u00f4ng c\u1ed1 \u0111\u1ecbnh v\u00f9ng t\u00ecm ki\u1ebfm khi sao ch\u00e9p c\u00f4ng th\u1ee9c<\/b><\/h3>\n

Khi b\u1ea1n kh\u00f4ng c\u1ed1 \u0111\u1ecbnh v\u00f9ng t\u00ecm ki\u1ebfm b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng d\u1ea5u $ \u0111\u1ec3 t\u1ea1o tham chi\u1ebfu tuy\u1ec7t \u0111\u1ed1i. Excel s\u1ebd t\u1ef1 \u0111\u1ed9ng \u0111i\u1ec1u ch\u1ec9nh ph\u1ea1m vi t\u00ecm ki\u1ebfm theo v\u1ecb tr\u00ed t\u01b0\u01a1ng \u0111\u1ed1i khi b\u1ea1n sao ch\u00e9p c\u00f4ng th\u1ee9c sang c\u00e1c \u00f4 kh\u00e1c. \u0110i\u1ec1u n\u00e0y c\u00f3 ngh\u0129a l\u00e0 v\u00f9ng t\u00ecm ki\u1ebfm ban \u0111\u1ea7u b\u1ecb d\u1ecbch chuy\u1ec3n, d\u1eabn \u0111\u1ebfn <\/span>l\u1ed7i NA<\/b> v\u00ec Excel kh\u00f4ng th\u1ec3 t\u00ecm th\u1ea5y d\u1eef li\u1ec7u trong ph\u1ea1m vi m\u1edbi \u0111\u00e3 b\u1ecb sai l\u1ec7ch.<\/span><\/p>\n

V\u00ed d\u1ee5<\/b><\/h4>\n

T\u1ea1i \u0111\u00e2y ch\u00fang ta s\u1ebd t\u1eadn d\u1ee5ng l\u1ea1i v\u00ed d\u1ee5 tr\u00ean v\u00e0 kh\u00f4ng c\u1ed1 \u0111\u1ecbnh v\u00f9ng d\u1eef li\u1ec7u v\u00e0 khi sao ch\u00e9p d\u1eef li\u1ec7u th\u00ec n\u00f3 v\u1eabn xu\u1ea5t hi\u1ec7n l\u1ed7i #N\/A trong Excel.<\/span><\/p>\n

C\u00e1ch kh\u1eafc ph\u1ee5c<\/b><\/h4>\n

\u0110\u1ec3 kh\u1eafc ph\u1ee5c l\u1ed7i v\u1eeba r\u1ed3i, b\u1ea1n h\u00e3y tham kh\u1ea3o qua 2 b\u01b0\u1edbc h\u01b0\u1edbng d\u1eabn b\u00ean d\u01b0\u1edbi \u0111\u00e2y c\u1ee7a Ho\u00e0ng H\u00e0 Mobile nh\u00e9.<\/span><\/p>\n

B\u01b0\u1edbc 1: \u0110\u1ea7u ti\u00ean, b\u1ea1n h\u00e3y t\u00f4 \u0111en d\u1eef li\u1ec7u v\u00f9ng ch\u1ecdn v\u00e0 \u1edf \u0111\u00e2y l\u00e0 ch\u00fang t\u00f4i b\u00f4i \u0111en “B2:D6”.<\/span><\/p>\n

\"loi-na-8\"<\/picture><\/p>\n

B\u01b0\u1edbc 2: B\u1ea1n h\u00e3y nh\u1ea5n ch\u1ecdn n\u00fat F4 tr\u00ean b\u00e0n ph\u00edm \u0111\u1ec3 c\u00e1c d\u1eef li\u1ec7u trong v\u00f9ng ch\u1ecdn \u0111\u01b0\u1ee3c c\u1ed1 \u0111\u1ecbnh l\u1ea1i. Ngay sau \u0111\u00f3, b\u1ea1n c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n thao t\u00e1c sao ch\u00e9p m\u00e0 kh\u00f4ng th\u1ea5y b\u1ea5t k\u1ef3 l\u1ed7i n\u00e0o xu\u1ea5t hi\u1ec7n.<\/span><\/p>\n

\"loi-na-9\"<\/picture><\/p>\n

L\u1ed7i NA khi s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p h\u00e0m MID<\/b><\/h3>\n

M\u1ed9t trong nh\u1eefng h\u00e0m x\u1eed l\u00fd chu\u1ed7i th\u00f4ng d\u1ee5ng l\u00e0 h\u00e0m MID, d\u00f9ng \u0111\u1ec3 tr\u00edch xu\u1ea5t m\u1ed9t ph\u1ea7n c\u1ee7a chu\u1ed7i v\u0103n b\u1ea3n d\u1ef1a tr\u00ean v\u1ecb tr\u00ed b\u1eaft \u0111\u1ea7u v\u00e0 s\u1ed1 k\u00fd t\u1ef1 c\u1ea7n l\u1ea5y. Khi k\u1ebft h\u1ee3p h\u00e0m MID v\u1edbi c\u00e1c h\u00e0m kh\u00e1c nh\u01b0 VLOOKUP, MATCH, HLOOKUP ho\u1eb7c INDEX, l\u1ed7i #N\/A c\u00f3 th\u1ec3 x\u1ea3y ra n\u1ebfu c\u00e1c \u0111\u1ed1i s\u1ed1 kh\u00f4ng ch\u00ednh x\u00e1c ho\u1eb7c d\u1eef li\u1ec7u b\u1ecb l\u1ed7i. Hi\u1ec3u r\u00f5 nguy\u00ean nh\u00e2n g\u00e2y ra l\u1ed7i n\u00e0y v\u00e0 c\u00e1ch kh\u1eafc ph\u1ee5c l\u00e0 c\u1ea7n thi\u1ebft \u0111\u1ec3 t\u1ed1i \u01b0u h\u00f3a qu\u00e1 tr\u00ecnh l\u00e0m vi\u1ec7c v\u1edbi d\u1eef li\u1ec7u chu\u1ed7i trong Excel.<\/span><\/p>\n

Nguy\u00ean nh\u00e2n \u0111i\u1ec3n h\u00ecnh nh\u1ea5t l\u00e0 khi h\u00e0m tham chi\u1ebfu \u0111\u1ebfn v\u1ecb tr\u00ed b\u1eaft \u0111\u1ea7u ho\u1eb7c s\u1ed1 k\u00fd t\u1ef1 trong h\u00e0m MID kh\u00f4ng h\u1ee3p l\u1ec7. N\u1ebfu \u0111\u1ed1i s\u1ed1 n\u00e0y v\u01b0\u1ee3t qu\u00e1 \u0111\u1ed9 d\u00e0i chu\u1ed7i ho\u1eb7c gi\u00e1 tr\u1ecb \u0111\u01b0\u1ee3c cung c\u1ea5p kh\u00f4ng ph\u1ea3i l\u00e0 s\u1ed1 h\u1ee3p l\u1ec7, Excel kh\u00f4ng th\u1ec3 th\u1ef1c hi\u1ec7n h\u00e0m.\u00a0<\/span><\/p>\n

B\u00ean c\u1ea1nh \u0111\u00f3, n\u1ebfu chu\u1ed7i v\u0103n b\u1ea3n m\u00e0 h\u00e0m MID \u0111ang x\u1eed l\u00fd l\u00e0 chu\u1ed7i tr\u1ed1ng ho\u1eb7c kh\u00f4ng c\u00f3 \u0111\u1ee7 k\u00fd t\u1ef1 \u0111\u1ec3 tr\u00edch xu\u1ea5t, h\u00e0m s\u1ebd kh\u00f4ng th\u1ec3 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 mong mu\u1ed1n. Khi k\u1ebft h\u1ee3p v\u1edbi c\u00e1c h\u00e0m tra c\u1ee9u kh\u00e1c, Excel c\u00f3 th\u1ec3 tr\u1ea3 v\u1ec1 <\/span>l\u1ed7i NA<\/b> n\u1ebfu kh\u00f4ng t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p t\u1eeb k\u1ebft qu\u1ea3 h\u00e0m MID.<\/span><\/p>\n

V\u00ed d\u1ee5<\/b><\/h4>\n

Trong v\u00ed d\u1ee5 minh h\u1ecda b\u00ean d\u01b0\u1edbi \u0111\u00e2y l\u00e0 d\u00f2ng h\u00e0m “=HLOOKUP(MID(A3,4,1),$C$9:$F$10,2,0)”. V\u1edbi MID(A3,4,1) l\u00e0 l\u1ea5y k\u00fd t\u1ef1 th\u1ee9 4 \u1edf \u00f4 A3 v\u00e0 \u0111\u00f3 ch\u00ednh l\u00e0 s\u1ed1 1. C\u00f2n $C$9:$F$10 ch\u00ednh l\u00e0 n\u1ed9i dung to\u00e0n b\u1ea3ng ch\u1ee9a khu v\u1ef1c thi v\u00e0 \u0111i\u1ec3m khu v\u1ef1c. Ti\u1ebfp \u0111\u1ebfn l\u00e0 s\u1ed1 2 th\u1ec3 hi\u1ec7n \u0111i\u1ec3m c\u1ee7a khu v\u1ef1c. Cu\u1ed1i c\u00f9ng l\u00e0 s\u1ed1 0 y\u00eau c\u1ea7u h\u00e0m l\u1ea5y gi\u00e1 tr\u1ecb ch\u00ednh x\u00e1c tuy\u1ec7t \u0111\u1ed1i.<\/span><\/p>\n

\"loi-na-10\"<\/picture><\/p>\n

V\u00e0 khi b\u1ea1n nh\u1ea5n ch\u1ecdn ph\u00edm “Enter” th\u00ec s\u1ebd xu\u1ea5t hi\u1ec7n l\u1ed7i nh\u01b0 h\u00ecnh \u1ea3nh minh h\u1ecda.<\/span><\/p>\n

\"loi-na-11\"<\/picture><\/p>\n

C\u00e1ch kh\u1eafc ph\u1ee5c<\/b><\/h4>\n

\u0110\u1ed1i v\u1edbi <\/span>l\u1ed7i NA<\/b> n\u00e0y, ng\u01b0\u1eddi d\u00f9ng s\u1ebd c\u00f3 hai h\u01b0\u1edbng x\u1eed l\u00fd, m\u1ed9t l\u00e0 th\u1ef1c hi\u1ec7n chuy\u1ec3n \u0111\u1ed5i h\u00e0m Mid th\u00e0nh h\u00e0m Value. Hai l\u00e0 b\u1ea1n h\u00e3y tham kh\u1ea3o \u0111\u1ebfn c\u00e1ch \u0111\u1ecbnh d\u1ea1ng tr\u00f9ng kh\u1edbp gi\u1eefa k\u1ebft qu\u1ea3 hi\u1ec3n th\u1ecb ra v\u00e0 d\u1eef li\u1ec7u tra c\u1ee9u ban \u0111\u1ea7u.<\/span><\/p>\n

V\u00e0 b\u00ean d\u01b0\u1edbi h\u00ecnh minh h\u1ecda n\u00e0y, Ho\u00e0ng H\u00e0 Mobile s\u1ebd s\u1eed d\u1ee5ng c\u00e1ch 1 \u0111\u1ec3 x\u1eed l\u00fd l\u1ed7i nh\u00e9. C\u1ee5 th\u1ec3, b\u1ea1n \u0111\u1ecdc h\u00e3y s\u1eed d\u1ee5ng h\u00e0m “=HLOOKUP(VALUE(MID(A3,4,1)),$C$9:$F$10,2,0)”.<\/span><\/p>\n

\"loi-na-12\"<\/picture><\/p>\n

Sau \u0111\u00f3, b\u1ea1n c\u0169ng s\u1ebd nh\u1ea5n ch\u1ecdn n\u00fat “Enter” v\u00e0 nh\u1eadn \u0111\u01b0\u1ee3c gi\u00e1 tr\u1ecb nh\u01b0 h\u00ecnh \u1ea3nh minh h\u1ecda.<\/span><\/p>\n

\"loi-na-13\"<\/picture><\/p>\n

L\u1ed7i NA khi s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p h\u00e0m LEFT<\/b><\/h3>\n

H\u00e0m LEFT l\u00e0 m\u1ed9t h\u00e0m x\u1eed l\u00fd chu\u1ed7i ph\u1ed5 bi\u1ebfn, d\u00f9ng \u0111\u1ec3 tr\u00edch xu\u1ea5t m\u1ed9t s\u1ed1 l\u01b0\u1ee3ng k\u00fd t\u1ef1 nh\u1ea5t \u0111\u1ecbnh t\u1eeb ph\u00eda b\u00ean tr\u00e1i c\u1ee7a chu\u1ed7i v\u0103n b\u1ea3n. V\u00e0 n\u1ebfu nh\u01b0 b\u1ea1n k\u1ebft h\u1ee3p h\u00e0m LEFT v\u1edbi h\u00e0m kh\u00e1c th\u00ec c\u0169ng s\u1ebd xu\u1ea5t hi\u1ec7n l\u1ed7i #N\/A nh\u01b0 b\u00ecnh th\u01b0\u1eddng. \u0110i\u1ec1u n\u00e0y c\u00f3 th\u1ec3 x\u1ea3y ra khi chu\u1ed7i \u0111\u01b0\u1ee3c tr\u00edch xu\u1ea5t ch\u1ee9a c\u00e1c k\u00fd t\u1ef1 th\u1eeba, kh\u00f4ng h\u1ee3p l\u1ec7, ho\u1eb7c kh\u00e1c bi\u1ec7t v\u1ec1 \u0111\u1ecbnh d\u1ea1ng (v\u00ed d\u1ee5: chu\u1ed7i v\u0103n b\u1ea3n v\u00e0 chu\u1ed7i s\u1ed1).<\/span><\/p>\n

H\u01a1n n\u1eefa, h\u00e0m LEFT y\u00eau c\u1ea7u s\u1ed1 k\u00fd t\u1ef1 c\u1ea7n tr\u00edch xu\u1ea5t ph\u1ea3i l\u00e0 m\u1ed9t gi\u00e1 tr\u1ecb s\u1ed1 nguy\u00ean d\u01b0\u01a1ng. N\u1ebfu s\u1ed1 k\u00fd t\u1ef1 b\u1ea1n ch\u1ec9 \u0111\u1ecbnh l\u00e0 m\u1ed9t gi\u00e1 tr\u1ecb kh\u00f4ng h\u1ee3p l\u1ec7 (v\u00ed d\u1ee5: \u00e2m, kh\u00f4ng ph\u1ea3i s\u1ed1), Excel s\u1ebd kh\u00f4ng th\u1ec3 th\u1ef1c hi\u1ec7n ph\u00e9p tr\u00edch xu\u1ea5t.<\/span><\/p>\n

\u0110\u1ed3ng th\u1eddi, c\u00e1c tham s\u1ed1 trong c\u00e1c h\u00e0m n\u00e0y c\u1ea7n ph\u1ea3i kh\u1edbp nhau v\u1ec1 d\u1eef li\u1ec7u v\u00e0 \u0111\u1ecbnh d\u1ea1ng. Tr\u01b0\u1eddng h\u1ee3p c\u00f3 s\u1ef1 kh\u00f4ng nh\u1ea5t qu\u00e1n trong vi\u1ec7c x\u00e1c \u0111\u1ecbnh s\u1ed1 k\u00fd t\u1ef1 tr\u00edch xu\u1ea5t ho\u1eb7c d\u1eef li\u1ec7u tra c\u1ee9u. Excel s\u1ebd kh\u00f4ng th\u1ec3 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 mong mu\u1ed1n v\u00e0 s\u1ebd hi\u1ec3n th\u1ecb l\u1ed7i #N\/A.<\/span><\/p>\n

V\u00ed d\u1ee5<\/b><\/h4>\n

Nh\u01b0 h\u00ecnh b\u00ean d\u01b0\u1edbi \u0111\u00e2y, b\u1ea1n \u0111i\u1ec1n v\u00e0o c\u00fa ph\u00e1p “=VLOOKUP(LEFT(B3,3),E8:F11,2,0)”. V\u1edbi VLOOKUP l\u00e0 h\u00e0m d\u00f2 t\u00ecm d\u1eef li\u1ec7u, LEFT(A3,3) l\u00e0 l\u1ec7nh t\u00ecm d\u1eef li\u1ec7u v\u00e0 E8:F11 ch\u00ednh l\u00e0 v\u00f9ng d\u1eef li\u1ec7u m\u00e0 ng\u01b0\u1eddi d\u00f9ng th\u1ef1c hi\u1ec7n t\u00ecm ki\u1ebfm.<\/span><\/p>\n

\"loi-na-14\"<\/picture><\/p>\n

Ti\u1ebfp \u0111\u1ebfn, b\u1ea1n s\u1ebd nh\u1ea5n ch\u1ecdn n\u00fat “Enter” \u0111\u1ec3 hi\u1ec3n th\u1ecb k\u1ebft qu\u1ea3, nh\u01b0ng \u1edf \u0111\u00e2y l\u00e0 l\u1ed7i.<\/span><\/p>\n

\"loi-na-15\"<\/picture><\/p>\n

C\u00e1ch kh\u1eafc ph\u1ee5c<\/b><\/h4>\n

M\u1ed9t trong nh\u1eefng c\u00e1ch \u0111\u01a1n gi\u1ea3n nh\u1ea5t \u0111\u1ec3 ng\u0103n l\u1ed7i #N\/A xu\u1ea5t hi\u1ec7n tr\u00ean b\u1ea3ng t\u00ednh c\u1ee7a b\u1ea1n l\u00e0 s\u1eed d\u1ee5ng h\u00e0m IFERROR. H\u00e0m n\u00e0y cho ph\u00e9p b\u1ea1n thay th\u1ebf l\u1ed7i b\u1eb1ng m\u1ed9t th\u00f4ng b\u00e1o ho\u1eb7c gi\u00e1 tr\u1ecb kh\u00e1c t\u00f9y \u00fd.<\/span><\/p>\n

\"loi-na-16\"<\/picture><\/p>\n

Ho\u1eb7c nh\u01b0 v\u00ed d\u1ee5 b\u00ean tr\u00ean \u0111\u00e2y th\u00ec b\u1ea1n s\u1ebd c\u00e1ch kh\u1eafc ph\u1ee5c l\u00e0 ki\u1ec3m tra v\u00e0 ch\u1ec9nh l\u1ea1i gi\u00e1 tr\u1ecb c\u1ee7a h\u00e0m LEFT. V\u00ec t\u1ea1i \u00f4 gi\u00e1 tr\u1ecb n\u00e0y ch\u1ee9a 2 d\u1eef li\u1ec7u nh\u01b0ng b\u1ea1n l\u1ea5y gi\u00e1 tr\u1ecb b\u1eb1ng 3. Nh\u01b0 v\u1eady, ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng h\u00e0m “=VLOOKUP(LEFT(A3,2),HANGSANXUAT,2,0)”. N\u1ebfu nh\u01b0 b\u1ea1n s\u1eeda l\u1ea1i th\u00ec s\u1ebd nh\u1eadn \u0111\u01b0\u1ee3c gi\u00e1 tr\u1ecb ch\u00ednh x\u00e1c nh\u01b0 h\u00ecnh \u1ea3nh minh h\u1ecda.<\/span><\/p>\n

K\u1ebft lu\u1eadn<\/b><\/h2>\n

L\u1ed7i NA trong Excel<\/b> l\u00e0 m\u1ed9t l\u1ed7i ph\u1ed5 bi\u1ebfn khi l\u00e0m vi\u1ec7c v\u1edbi c\u00e1c h\u00e0m tra c\u1ee9u v\u00e0 x\u1eed l\u00fd d\u1eef li\u1ec7u. N\u00f3 b\u00e1o hi\u1ec7u r\u1eb1ng gi\u00e1 tr\u1ecb t\u00ecm ki\u1ebfm kh\u00f4ng t\u1ed3n t\u1ea1i ho\u1eb7c d\u1eef li\u1ec7u kh\u00f4ng h\u1ee3p l\u1ec7. Nguy\u00ean nh\u00e2n c\u00f3 th\u1ec3 do d\u1eef li\u1ec7u kh\u00f4ng kh\u1edbp, thi\u1ebfu ho\u1eb7c c\u00e1c tham s\u1ed1 kh\u00f4ng h\u1ee3p l\u1ec7. Tuy nhi\u00ean, v\u1edbi vi\u1ec7c ki\u1ec3m tra k\u1ef9 l\u01b0\u1ee1ng d\u1eef li\u1ec7u, s\u1eed d\u1ee5ng c\u00e1c h\u00e0m h\u1ed7 tr\u1ee3 nh\u01b0 IFERROR, TRIM v\u00e0 CLEAN. L\u1ed7i #N\/A c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c kh\u1eafc ph\u1ee5c hi\u1ec7u qu\u1ea3, gi\u00fap b\u1ea1n \u0111\u1ea3m b\u1ea3o t\u00ednh ch\u00ednh x\u00e1c trong qu\u00e1 tr\u00ecnh t\u00ednh to\u00e1n v\u00e0 x\u1eed l\u00fd d\u1eef li\u1ec7u.<\/span><\/p>\n

Xem th\u00eam:<\/b><\/p>\n