{"id":293717,"date":"2023-12-09T12:19:23","date_gmt":"2023-12-09T05:19:23","guid":{"rendered":"https:\/\/hoanghamobile.com\/tin-tuc\/?p=293717"},"modified":"2023-12-09T12:19:23","modified_gmt":"2023-12-09T05:19:23","slug":"hlookup","status":"publish","type":"post","link":"https:\/\/hoanghamobile.com\/tin-tuc\/hlookup\/","title":{"rendered":"H\u00e0m HLOOKUP trong Excel v\u00e0 c\u00e1c v\u00ed d\u1ee5 s\u1eed d\u1ee5ng d\u1ec5 hi\u1ec3u"},"content":{"rendered":"

Ngo\u00e0i h\u00e0m VLOOKUP, <\/span>HLOOKUP <\/b>c\u0169ng l\u00e0 m\u1ed9t c\u00f4ng th\u1ee9c quan tr\u1ecdng m\u00e0 ng\u01b0\u1eddi d\u00f9ng Excel n\u00e0o c\u0169ng n\u00ean bi\u1ebft \u0111\u1ebfn. V\u1edbi \u0111\u1ed9 ch\u00ednh x\u00e1c t\u1eeb h\u00e0m th\u1ed1ng k\u00ea n\u00e0y, n\u00f3 s\u1ebd gi\u00fap b\u1ea1n nhanh ch\u00f3ng gi\u1ea3i quy\u1ebft c\u00f4ng vi\u1ec7c, c\u0169ng nh\u01b0 ti\u1ebft ki\u1ec7m r\u1ea5t nhi\u1ec1u th\u1eddi gian. V\u1eady c\u00f2n ch\u1ea7n ch\u1edd g\u00ec n\u1eefa m\u00e0 kh\u00f4ng t\u00ecm hi\u1ec3u c\u00e1ch \u00e1p d\u1ee5ng h\u00e0m c\u00f9ng c\u00e1c v\u00ed d\u1ee5 s\u1eed d\u1ee5ng d\u1ec5 hi\u1ec3u trong b\u00e0i vi\u1ebft n\u00e0y b\u1ea1n nh\u00e9.<\/span><\/p>\n

H\u00e0m HLOOKUP trong Excel l\u00e0 g\u00ec?<\/b><\/h2>\n

Horizontal Lookup \u0111\u01b0\u1ee3c xem l\u00e0 m\u1ed9t trong nh\u1eefng h\u00e0m c\u1ea7n thi\u1ebft trong Microsoft Excel. N\u00f3 \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u0111\u1ec3 t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb trong m\u1ed9t d\u00f2ng ngang c\u1ee7a m\u1ed9t ph\u1ea1m vi \u0111\u01b0\u1ee3c ch\u1ec9 \u0111\u1ecbnh v\u00e0 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng t\u1eeb d\u00f2ng kh\u00e1c. Hay n\u00f3i c\u00e1ch kh\u00e1c, \u0111\u00e2y l\u00e0 m\u1ed9t c\u00f4ng c\u1ee5 m\u1ea1nh m\u1ebd gi\u00fap ng\u01b0\u1eddi d\u00f9ng nhanh ch\u00f3ng v\u00e0 d\u1ec5 d\u00e0ng tr\u00edch xu\u1ea5t th\u00f4ng tin t\u1eeb b\u1ea3ng d\u1eef li\u1ec7u theo h\u00e0ng ngang.<\/span><\/p>\n

\"hlookup-1\"<\/picture><\/p>\n

Theo \u0111\u00f3, h\u00e0m r\u00e0 so\u00e1t data n\u00e0y th\u01b0\u1eddng \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng trong c\u00e1c b\u1ea3ng t\u00ednh Excel l\u1edbn v\u00e0 ph\u1ee9c t\u1ea1p. N\u01a1i ng\u01b0\u1eddi d\u00f9ng mu\u1ed1n nhanh ch\u00f3ng t\u00e1ch l\u1ecdc th\u00f4ng tin t\u1eeb h\u00e0ng \u0111\u1ea7u ti\u00ean c\u1ee7a b\u1ea3ng d\u1eef li\u1ec7u theo c\u00e1c ti\u00eau ch\u00ed c\u1ee5 th\u1ec3. V\u00ed d\u1ee5, n\u1ebfu b\u1ea1n c\u00f3 m\u1ed9t b\u1ea3ng d\u1eef li\u1ec7u v\u1edbi th\u00f4ng tin v\u1ec1 s\u1ea3n ph\u1ea9m theo t\u1eebng th\u00e1ng, h\u00e0m tra c\u1ee9u n\u00e0y c\u00f3 th\u1ec3 gi\u00fap b\u1ea1n d\u1ec5 d\u00e0ng tra c\u1ee9u th\u00f4ng tin v\u1ec1 m\u1ed9t s\u1ea3n ph\u1ea9m c\u1ee5 th\u1ec3 trong c\u00e1c th\u00e1ng kh\u00e1c nhau.<\/span><\/p>\n

L\u1ee3i \u00edch c\u1ee7a vi\u1ec7c s\u1eed d\u1ee5ng h\u00e0m HLOOKUP<\/b><\/h2>\n

C\u00f3 th\u1ec3 n\u00f3i, h\u00e0m ngang trong Excel kh\u00f4ng ch\u1ec9 l\u00e0 m\u1ed9t c\u00f4ng c\u1ee5 ti\u1ec7n \u00edch, m\u00e0 c\u00f2n mang l\u1ea1i nhi\u1ec1u l\u1ee3i \u00edch quan tr\u1ecdng cho ng\u01b0\u1eddi s\u1eed d\u1ee5ng khi x\u1eed l\u00fd d\u1eef li\u1ec7u. Vi\u1ec7c s\u1eed d\u1ee5ng h\u00e0m n\u00e0y s\u1ebd gi\u00fap t\u1ed1i \u01b0u h\u00f3a qu\u00e1 tr\u00ecnh ph\u00e2n t\u00edch v\u00e0 mang l\u1ea1i hi\u1ec7u qu\u1ea3 c\u00f4ng vi\u1ec7c cao. \u0110\u1ed3ng th\u1eddi, ng\u01b0\u1eddi d\u00f9ng c\u00f2n nh\u1eadn \u0111\u01b0\u1ee3c m\u1ed9t s\u1ed1 l\u1ee3i th\u1ebf nh\u01b0 b\u00ean d\u01b0\u1edbi \u0111\u00e2y.<\/span><\/p>\n

\u0110\u01b0\u1ee3c k\u1ebft h\u1ee3p v\u1edbi nhi\u1ec1u h\u00e0m Excel kh\u00e1c<\/b><\/h3>\n

Khi h\u00e0m t\u00ecm ki\u1ebfm \u0111\u01b0\u1ee3c \u201ch\u1ee3p l\u1ef1c\u201d v\u1edbi nhi\u1ec1u h\u00e0m kh\u00e1c, ch\u1eafc ch\u1eafn n\u00f3 s\u1ebd tr\u1edf th\u00e0nh m\u1ed9t kh\u00eda c\u1ea1nh th\u00fa v\u1ecb m\u00e0 b\u1ea1n n\u00ean xem x\u00e9t \u0111\u1ebfn. Th\u00f4ng qua c\u00e1ch t\u00edch h\u1ee3p \u0111\u1ed9c \u0111\u00e1o n\u00e0y s\u1ebd mang l\u1ea1i kh\u1ea3 n\u0103ng linh ho\u1ea1t v\u00e0 m\u1edf r\u1ed9ng trong vi\u1ec7c x\u1eed l\u00fd d\u1eef li\u1ec7u trong Microsoft Excel. Chi ti\u1ebft h\u01a1n th\u00ec n\u00f3 s\u1ebd t\u1ea1o ra c\u00f4ng th\u1ee9c ph\u1ee9c t\u1ea1p h\u01a1n. T\u1eeb \u0111\u00f3 gi\u00fap ng\u01b0\u1eddi d\u00f9ng th\u1ef1c hi\u1ec7n c\u00e1c ph\u00e9p t\u00ednh v\u00e0 x\u1eed l\u00fd d\u1eef li\u1ec7u kh\u00f3 gi\u1ea3i theo c\u00e1c y\u00eau c\u1ea7u c\u1ee5 th\u1ec3.<\/span><\/p>\n

\"hlookup-2\"<\/picture><\/p>\n

\u0110\u1eb7c bi\u1ec7t, khi k\u1ebft h\u1ee3p h\u00e0m t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng v\u1edbi h\u00e0m IF th\u00ec b\u1ea1n c\u00f3 th\u1ec3 tra c\u1ee9u th\u00f4ng tin d\u1ef1a theo m\u1ed9t \u0111i\u1ec1u ki\u1ec7n c\u1ee5 th\u1ec3 n\u00e0o \u0111\u00f3. H\u01a1n th\u1ebf n\u1eefa, n\u1ebfu \u0111i k\u00e8m v\u1edbi h\u00e0m INDEX v\u00e0 MATCH, h\u00e0m r\u00e0 so\u00e1t th\u00f4ng tin n\u00e0y s\u1ebd nhanh ch\u00f3ng tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb ch\u00ednh x\u00e1c t\u1eeb b\u1ea3ng d\u1eef li\u1ec7u, gi\u1ea3m nguy c\u01a1 l\u1ed7i v\u00e0 t\u0103ng \u0111\u1ed9 kh\u1ea3 thi c\u1ee7a data.<\/span><\/p>\n

Ngo\u00e0i nh\u1eefng h\u00e0m ti\u00eau bi\u1ec3u tr\u00ean, <\/span>HLOOKUP <\/b>c\u0169ng c\u00f3 th\u1ec3 \u201cc\u1ed9ng h\u01b0\u1edfng\u201d v\u1edbi m\u1ed9t s\u1ed1 c\u00f4ng th\u1ee9c n\u1ed5i b\u1eadt kh\u00e1c. B\u1ea1n c\u00f3 th\u1ec3 tham kh\u1ea3o th\u00eam v\u1ec1 s\u1ef1 k\u1ebft h\u1ee3p v\u1edbi h\u00e0m OFFSET, SUM, AVERAGE, hay COUNT.\u00a0<\/span><\/p>\n

T\u1ef1 \u0111\u1ed9ng c\u1eadp nh\u1eadt d\u1eef li\u1ec7u<\/b><\/h3>\n

Ng\u01b0\u1eddi d\u00f9ng c\u1ea7n bi\u1ebft r\u1eb1ng, \u0111\u00e2y l\u00e0 m\u1ed9t h\u00e0m t\u00ednh to\u00e1n d\u1ef1a tr\u00ean c\u00f4ng th\u1ee9c c\u00f3 s\u1eb5n trong Excel. Ch\u00ednh v\u00ec l\u1ebd \u0111\u00f3, khi b\u1ea1n s\u1eed d\u1ee5ng h\u00e0m n\u00e0y \u0111\u1ec3 l\u1ea5y ra d\u1eef li\u1ec7u t\u1eeb m\u1ed9t b\u1ea3ng, c\u00f4ng th\u1ee9c s\u1ebd t\u1ef1 \u0111\u1ed9ng thay \u0111\u1ed5i v\u00e0 c\u1eadp nh\u1eadt k\u1ebft qu\u1ea3 khi c\u00f3 s\u1ef1 thay \u0111\u1ed5i trong b\u1ea3ng d\u1eef li\u1ec7u.<\/span><\/p>\n

B\u00ean c\u1ea1nh \u0111\u00f3, m\u1ed7i khi gi\u00e1 tr\u1ecb ho\u1eb7c c\u1ea5u tr\u00fac c\u1ee7a b\u1ea3ng d\u1eef li\u1ec7u thay \u0111\u1ed5i, h\u00e0m s\u1ebd t\u1ef1 \u0111\u1ed9ng \u0111i\u1ec1u ch\u1ec9nh k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1. \u0110i\u1ec1u n\u00e0y gi\u00fap duy tr\u00ec t\u00ednh ch\u00ednh x\u00e1c c\u1ee7a th\u00f4ng tin trong b\u1ea3ng t\u00ednh m\u00e0 kh\u00f4ng c\u1ea7n ph\u1ea3i th\u1ef1c hi\u1ec7n l\u1ea1i c\u00f4ng th\u1ee9c m\u1ed7i khi c\u00f3 thay \u0111\u1ed5i.<\/span><\/p>\n

\"hlookup-3\"<\/picture><\/p>\n

Ch\u01b0a h\u1ebft, h\u00e0m t\u00ecm ki\u1ebfm d\u1eef li\u1ec7u n\u00e0y l\u00e0m vi\u1ec7c r\u1ea5t t\u1ed1t v\u1edbi d\u1eef li\u1ec7u \u0111\u1ed9ng, c\u00f3 ngh\u0129a l\u00e0 khi b\u1ea1n th\u00eam ho\u1eb7c x\u00f3a c\u00e1c c\u1ed9t trong b\u1ea3ng d\u1eef li\u1ec7u ngu\u1ed3n. V\u1eady th\u00ec c\u00f4ng th\u1ee9c h\u00e0m <\/span>s\u1ebd t\u1ef1 \u0111\u1ed9ng th\u00edch \u1ee9ng m\u00e0 kh\u00f4ng c\u1ea7n ph\u1ea3i \u0111i\u1ec1u ch\u1ec9nh c\u00f4ng th\u1ee9c theo ph\u01b0\u01a1ng ph\u00e1p th\u1ee7 c\u00f4ng.<\/span><\/p>\n

H\u01a1n n\u1eefa, b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m tra c\u1ee9u n\u00e0y, b\u1ea1n s\u1ebd gi\u1ea3m thi\u1ec3u kh\u1ea3 n\u0103ng xu\u1ea5t hi\u1ec7n l\u1ed7i do nh\u1eadp li\u1ec7u th\u1ee7 c\u00f4ng. C\u0169ng b\u1edfi v\u00ec c\u00f4ng th\u1ee9c l\u00e0m vi\u1ec7c t\u1ef1 \u0111\u1ed9ng 100%, n\u00ean b\u1ea1n kh\u00f4ng c\u1ea7n ph\u1ea3i lo l\u1eafng v\u1ec1 vi\u1ec7c qu\u00ean c\u1eadp nh\u1eadt th\u00f4ng tin khi c\u00f3 thay \u0111\u1ed5i trong d\u1eef li\u1ec7u ngu\u1ed3n. Nh\u01b0 v\u1eady, b\u1ea1n v\u1eeba c\u00f3 th\u1ec3 duy tr\u00ec t\u00ednh ch\u00ednh x\u00e1c c\u1ee7a th\u00f4ng tin, v\u1eeba t\u0103ng c\u01b0\u1eddng hi\u1ec7u qu\u1ea3 c\u00f4ng vi\u1ec7c c\u1ee7a m\u00ecnh.<\/span><\/p>\n

D\u1ec5 d\u00e0ng s\u1eed d\u1ee5ng v\u00e0 th\u00edch nghi<\/b><\/h3>\n

Ph\u1ea3i n\u00f3i m\u1ed9t s\u1ef1 th\u1eadt l\u00e0 h\u00e0m <\/span>HLOOKUP <\/b>t\u00edch h\u1ee3p trong Excel r\u1ea5t \u0111\u01a1n gi\u1ea3n v\u00e0 d\u1ec5 nh\u1edb. Ngay c\u1ea3 \u0111\u1ed1i v\u1edbi nh\u1eefng ng\u01b0\u1eddi m\u1edbi b\u1eaft \u0111\u1ea7u l\u00e0m quen v\u1edbi Excel, h\u00e0m n\u00e0y kh\u00f4ng \u0111\u00f2i h\u1ecfi ki\u1ebfn th\u1ee9c chuy\u00ean s\u00e2u v\u1ec1 c\u00f4ng th\u1ee9c ho\u1eb7c l\u1eadp tr\u00ecnh. B\u1ea1n ch\u1ec9 c\u1ea7n nh\u1eadp gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm, ph\u1ea1m vi d\u1eef li\u1ec7u,v\u00e0 ch\u1ec9 s\u1ed1 d\u00f2ng \u0111\u1ec3 nh\u1eadn k\u1ebft qu\u1ea3. V\u1edbi c\u00e1ch th\u1ee9c n\u00e0y l\u00e0m cho h\u00e0m tr\u1edf th\u00e0nh m\u1ed9t c\u00f4ng c\u1ee5 h\u1eefu \u00edch cho c\u1ea3 ng\u01b0\u1eddi m\u1edbi h\u1ecdc, l\u1eabn ng\u01b0\u1eddi c\u00f3 kinh nghi\u1ec7m.<\/span><\/p>\n

\"hlookup-4\"<\/picture><\/p>\n

Th\u00eam m\u1ed9t \u0111i\u1ec3m c\u1ed9ng kh\u00e1c l\u00e0 giao di\u1ec7n c\u1ee7a Excel v\u00e0 c\u00e1ch m\u00e0 h\u00e0m t\u00ecm ki\u1ebfm \u0111\u01b0\u1ee3c t\u00edch h\u1ee3p v\u00e0o s\u1ebd t\u1ea1o ra m\u1ed9t tr\u1ea3i nghi\u1ec7m t\u01b0\u01a1ng t\u00e1c v\u00f4 c\u00f9ng th\u00e2n thi\u1ec7n. N\u00f3i c\u1ee5 th\u1ec3 h\u01a1n l\u00e0 b\u1ea1n s\u1ebd thu\u1eadn ti\u1ec7n trong vi\u1ec7c nh\u1eadp ch\u1ec9 s\u1ed1, c\u0169ng nh\u01b0 nh\u1eadn k\u1ebft qu\u1ea3 ngay l\u1eadp t\u1ee9c.<\/span><\/p>\n

Ch\u01b0a k\u1ec3 \u0111\u1ebfn vi\u1ec7c, c\u00f4ng th\u1ee9c h\u00e0m c\u00f3 kh\u1ea3 n\u0103ng th\u00edch \u1ee9ng v\u1edbi r\u1ea5t nhi\u1ec1u t\u00ecnh hu\u1ed1ng c\u1ee5 th\u1ec3. T\u1eeb t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb ch\u00ednh x\u00e1c \u0111\u1ebfn t\u00ecm ki\u1ebfm x\u1ea5p x\u1ec9, t\u00f9y thu\u1ed9c v\u00e0o vi\u1ec7c b\u1ea1n s\u1eed d\u1ee5ng \u0111\u1ed1i s\u1ed1 [range_lookup] l\u00e0 TRUE ho\u1eb7c FALSE.<\/span><\/p>\n

C\u00f4ng th\u1ee9c h\u00e0m HLOOKUP trong Excel l\u00e0 g\u00ec?<\/b><\/h2>\n

Nh\u01b0 ch\u00fang ta \u0111\u00e3 bi\u1ebft, h\u00e0m Horizontal Lookup mang \u0111\u1ebfn r\u1ea5t nhi\u1ec1u l\u1ee3i \u00edch quan tr\u1ecdng cho ng\u01b0\u1eddi s\u1eed d\u1ee5ng. Tuy nhi\u00ean, n\u1ebfu b\u1ea1n mu\u1ed1n nh\u1eadn \u0111\u01b0\u1ee3c nh\u1eefng \u0111i\u1ec1u tuy\u1ec7t v\u1eddi tr\u00ean th\u00ec c\u1ea7n ph\u1ea3i bi\u1ebft c\u00e1ch d\u00f9ng h\u00e0m chu\u1ea9n x\u00e1c. V\u00e0 \u0111i\u1ec1u quan tr\u1ecdng h\u01a1n n\u1eefa l\u00e0 b\u1ea1n c\u1ea7n ph\u1ea3i bi\u1ebft c\u00f4ng th\u1ee9c h\u00e0m n\u00e0y trong Excel s\u1ebd \u0111\u01b0\u1ee3c di\u1ec5n \u0111\u1ea1t nh\u01b0 th\u1ebf n\u00e0o. C\u1ee5 th\u1ec3, Ho\u00e0ng H\u00e0 Mobile s\u1ebd chia s\u1ebb cho b\u1ea1n n\u1ed9i dung quan tr\u1ecdng ngay b\u00ean d\u01b0\u1edbi \u0111\u00e2y nh\u00e9.<\/span><\/p>\n

C\u00f4ng th\u1ee9c \u201c<\/span>=HLOOKUP(lookup_value, table_array, row_index_number, [range_lookup])<\/b>\u201d.<\/span><\/p>\n

\"hlookup-5\"<\/picture><\/p>\n

Trong \u0111\u00f3, \u201clookup_value\u201d \u0111\u01b0\u1ee3c xem l\u00e0 gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm trong h\u00e0ng \u0111\u1ea7u ti\u00ean c\u1ee7a b\u1ea3ng d\u1eef li\u1ec7u. Hay \u0111\u00e2y c\u0169ng c\u00f3 th\u1ec3 l\u00e0 \u00f4 hay ph\u1ea1m vi tham chi\u1ebfu m\u00e0 b\u1ea1n mu\u1ed1n c\u00f4ng th\u1ee9c h\u00e0m x\u00e1c \u0111\u1ecbnh data.<\/span><\/p>\n

Ti\u1ebfp \u0111\u1ebfn, \u201ctable_array\u201d kh\u00f4ng g\u00ec kh\u00e1c ngo\u00e0i ph\u1ea1m vi d\u1eef li\u1ec7u m\u00e0 b\u1ea1n \u0111\u1ecdc mu\u1ed1n t\u00ecm ki\u1ebfm. Tuy nhi\u00ean \u0111\u1ebfn ph\u1ea7n quan tr\u1ecdng n\u00e0y b\u1ea1n c\u1ea7n l\u01b0u \u00fd m\u1ed9t v\u1ea5n \u0111\u1ec1 h\u1ebft s\u1ee9c \u0111\u1eb7c bi\u1ec7t. \u0110\u00f3 l\u00e0 gi\u00e1 tr\u1ecb x\u00e1c \u0111\u1ecbnh c\u1ea7n ph\u1ea3i n\u1eb1m \u1edf h\u00e0ng \u0111\u1ea7u ti\u00ean trong ph\u1ea1m vi m\u00e0 c\u00f4ng th\u1ee9c Excel c\u1ea7n d\u00f2 t\u00ecm.<\/span><\/p>\n

B\u00ean c\u1ea1nh \u0111\u00f3, \u201crow_index_number\u201d ch\u00ednh l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 c\u1ee7a d\u00f2ng ch\u1ee9a gi\u00e1 tr\u1ecb b\u1ea1n mu\u1ed1n tr\u1ea3 v\u1ec1. S\u1ed1 th\u1ee9 t\u1ef1 n\u00e0y b\u1eaft bu\u1ed9c ph\u1ea3i \u0111\u01b0\u1ee3c \u0111\u1ebfm ch\u00ednh x\u00e1c th\u00ec h\u00e0m m\u1edbi g\u1eedi l\u1ea1i b\u1ea1n k\u1ebft qu\u1ea3 tra c\u1ee9u th\u00f4ng tin ch\u00ednh x\u00e1c nh\u1ea5t.<\/span><\/p>\n

Cu\u1ed1i c\u00f9ng, \u201c[range_lookup]\u201d ch\u00ednh l\u00e0 t\u00f9y ch\u1ecdn gi\u00e1 tr\u1ecb logic c\u1ee7a data v\u00e0 b\u1ea1n c\u00f3 th\u1ec3 l\u1ef1a ch\u1ecdn TRUE ho\u1eb7c FALSE. N\u1ebfu b\u1ea1n \u0111\u1ecdc ch\u1ecdn TRUE th\u00ec s\u1ebd s\u1eed d\u1ee5ng t\u00ecm ki\u1ebfm x\u1ea5p x\u1ec9, ng\u01b0\u1ee3c l\u1ea1i ch\u1ecdn FALSE \u0111\u1ec3 s\u1eed d\u1ee5ng t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c.<\/span><\/p>\n

H\u00e0m HLOOKUP v\u00e0 v\u00ed d\u1ee5 minh h\u1ecda d\u1ec5 hi\u1ec3u nh\u1ea5t<\/b><\/h2>\n

\u201cH\u1ecdc \u0111i \u0111\u00f4i v\u1edbi h\u00e0nh\u201d, sau khi ch\u00fang ta \u0111\u00e3 c\u00f3 \u0111\u01b0\u1ee3c c\u00f4ng th\u1ee9c h\u00e0m Excel quan tr\u1ecdng nh\u1ea5t. Nhi\u1ec7m v\u1ee5 ti\u1ebfp theo c\u1ee7a m\u1ecdi ng\u01b0\u1eddi l\u00e0 ph\u1ea3i bi\u1ebft c\u00e1ch v\u1eadn d\u1ee5ng h\u00e0m m\u1ed9t c\u00e1ch \u0111\u00fang nh\u1ea5t. Nh\u1eb1m gi\u00fap cho b\u1ea1n \u0111\u1ecdc c\u1ee7a Ho\u00e0ng H\u00e0 Mobile d\u1ec5 d\u00e0ng h\u00ecnh dung v\u1ec1 h\u00e0m n\u00e0y, ch\u00fang t\u00f4i \u0111\u00e3 t\u1ed5ng h\u1ee3p cho b\u1ea1n 3 v\u00ed d\u1ee5 minh h\u1ecda ngay b\u00ean d\u01b0\u1edbi \u0111\u00e2y.<\/span><\/p>\n

VD1: D\u00f9ng h\u00e0m \u0111\u1ec3 x\u1ebfp lo\u1ea1i danh hi\u1ec7u h\u1ecdc sinh<\/b><\/h3>\n

Ch\u00fang ta c\u00f3 gi\u1ea3 \u0111\u1ecbnh v\u1ec1 m\u1ed9t b\u1ea3ng th\u00f4ng tin h\u1ecdc sinh cu\u1ed1i n\u0103m (b\u1ea3ng 2), bao g\u1ed3m \u0111i\u1ec3m trung b\u00ecnh v\u00e0 x\u1ebfp lo\u1ea1i. C\u00f4ng vi\u1ec7c c\u1ee7a b\u1ea1n l\u00e0 c\u1ea7n ph\u1ea3i tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 x\u1ebfp lo\u1ea1i t\u01b0\u01a1ng \u1ee9ng v\u1edbi t\u00ean c\u1ee7a t\u1eebng em h\u1ecdc sinh trong l\u1edbp.\u00a0<\/span><\/p>\n

\"hlookup-6\"<\/picture><\/p>\n

\u0110\u1ec3 gi\u1ea3i b\u00e0i to\u00e1n n\u00e0y, ch\u00fang ta c\u1ea7n ph\u1ea3i v\u1eadn d\u1ee5ng h\u00e0m tra c\u1ee9u th\u00f4ng tin trong Excel v\u00e0 \u0111\u1ec3 n\u00f3 t\u1ef1 \u0111\u1ed9ng g\u1eedi tr\u1ea3 k\u1ebft h\u1ee3p h\u1ee3p l\u00fd. Thay v\u00ec ph\u1ea3i ph\u00e2n b\u1ed5 nh\u1eadp tay th\u1ee7 c\u00f4ng th\u00ec gi\u1edd \u0111\u00e2y b\u1ea1n c\u00f3 th\u1ec3 \u00e1p d\u1ee5ng c\u00f4ng th\u1ee9c \u201c<\/span>=HLOOKUP(C4,$B$11:$F$12,2,1)<\/b>\u201d.<\/span><\/p>\n

Khi \u00e1p d\u1ee5ng c\u00f4ng th\u1ee9c n\u00e0y, h\u00e0m s\u1ebd ti\u1ebfn h\u00e0nh d\u00f2 t\u00ecm k\u1ebft qu\u1ea3 g\u1ea7n nh\u01b0 l\u00e0 ch\u00ednh x\u00e1c v\u1edbi gi\u00e1 tr\u1ecb t\u1ea1i \u00f4 C4. V\u00e0 khi t\u00ecm th\u1ea5y \u0111\u01b0\u1ee3c \u0111\u00e1p \u00e1n chu\u1ea9n x\u00e1c th\u00ec h\u00e0m s\u1ebd ph\u00e2n lo\u1ea1i h\u1ecdc sinh v\u00e0 g\u1eedi k\u1ebft qu\u1ea3 v\u1ec1 \u00f4 D4.\u00a0<\/span><\/p>\n

\"hlookup-7\"<\/picture><\/p>\n

Trong tr\u01b0\u1eddng h\u1ee3p, b\u1ea1n mu\u1ed1n \u00e1p d\u1ee5ng c\u00f4ng th\u1ee9c cho t\u1ea5t c\u1ea3 c\u00e1c b\u1ea1n h\u1ecdc sinh c\u00f2n l\u1ea1i. Ng\u01b0\u1eddi d\u00f9ng ch\u1ec9 c\u1ea7n ch\u1ecdn v\u00e0o \u00f4 D4 v\u00e0 k\u00e9o xu\u1ed1ng n\u01a1i mu\u1ed1n tham chi\u1ebfu data. Ho\u1eb7c b\u1ea1n c\u0169ng c\u00f3 th\u1ec3 nh\u1ea5p \u201cdouble click\u201d v\u00e0o g\u00f3c vu\u00f4ng d\u01b0\u1edbi c\u00f9ng tay ph\u1ea3i c\u1ee7a \u00f4 D4 nh\u00e9.<\/span><\/p>\n

VD2: D\u00f9ng h\u00e0m \u0111\u1ec3 t\u00ednh ph\u1ee5 c\u1ea5p nh\u00e2n vi\u00ean<\/b><\/h3>\n

Ti\u1ebfp t\u1ee5c, ch\u00fang ta s\u1ebd c\u00f9ng kh\u00e1m ph\u00e1 v\u00ed d\u1ee5 th\u1ee9 hai v\u1edbi \u0111\u1ed9 ph\u1ee9c t\u1ea1p cao h\u01a1n n\u1eefa. \u0110\u1ec1 b\u00e0i l\u00e0 cho m\u1ed9t b\u1ea3ng 2 v\u1edbi d\u1eef li\u1ec7u ph\u1ee5 c\u1ea5p c\u1ee7a t\u1eebng c\u1ea5p b\u1eadc kh\u00e1c nhau. Theo \u0111\u00f3, k\u1ebf to\u00e1n ho\u1eb7c HR c\u1ea7n ph\u1ea3i tr\u1ea3 v\u1ec1 m\u1ee9c ph\u1ee5 c\u1ea5p t\u01b0\u01a1ng \u1ee9ng v\u1edbi t\u00ean c\u1ee7a t\u1eebng nh\u00e2n vi\u00ean trong c\u00f4ng ty. B\u1ea1n h\u00e3y nh\u00ecn qua h\u00ecnh \u1ea3nh minh h\u1ecda \u0111\u1ec3 hi\u1ec3u r\u00f5 h\u01a1n v\u1ec1 \u0111\u1ec1 b\u00e0i nh\u00e9.<\/span><\/p>\n

\"hlookup-8\"<\/picture><\/p>\n

B\u00e0i gi\u1ea3i c\u1ee7a v\u00ed d\u1ee5 n\u00e0y, ch\u00fang ta c\u0169ng s\u1ebd t\u1eadn d\u1ee5ng c\u00f4ng th\u1ee9c h\u00e0m \u201c<\/span>=HLOOKUP(C4,$B$11:$F$12,2,0)<\/b>\u201d. Trong \u0111\u00f3, h\u00e0m s\u1ebd ti\u1ebfn h\u00e0nh r\u00e0 so\u00e1t th\u00f4ng tin t\u1ea1i b\u1ea3ng 2 v\u00e0 n\u1ebfu b\u1ea3ng 1 c\u00f3 ch\u1ee9c v\u1ee5 n\u00e0o t\u01b0\u01a1ng \u1ee9ng th\u00ec n\u00f3 s\u1ebd tr\u1ea3 \u0111\u00e1p \u00e1n ch\u00ednh x\u00e1c nh\u1ea5t d\u00e0nh cho ng\u01b0\u1eddi d\u00f9ng.\u00a0<\/span><\/p>\n

V\u00e0 khi ng\u01b0\u1eddi d\u00f9ng Excel \u00e1p d\u1ee5ng \u0111\u01b0\u1ee3c \u0111\u00fang c\u00f4ng th\u1ee9c th\u00ec b\u1ea1n s\u1ebd ghi nh\u1eadn \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 nh\u01b0 b\u1ea3ng sau \u0111\u00e2y c\u1ee7a Ho\u00e0ng H\u00e0 Mobile.<\/span><\/p>\n

\"hlookup-9\"<\/picture><\/p>\n

VD3: D\u00f9ng h\u00e0m k\u1ebft h\u1ee3p v\u1edbi h\u00e0m IF \u0111\u1ec3 \u0111\u00e1nh gi\u00e1 nh\u00e2n vi\u00ean<\/b><\/h3>\n

Vi\u1ec7c s\u1eed d\u1ee5ng \u201ch\u00e0m l\u1ed3ng h\u00e0m\u201d ho\u00e0n to\u00e0n kh\u00f4ng kh\u00f3 nh\u01b0 b\u1ea1n ngh\u0129. D\u1ef1a v\u00e0o v\u00ed d\u1ee5 n\u00e0y, b\u1ea1n s\u1ebd bi\u1ebft c\u00e1ch k\u1ebft h\u1ee3p h\u00e0m t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng d\u1ef1a tr\u00ean m\u1ed9t \u0111i\u1ec1u ki\u1ec7n c\u1ee5 th\u1ec3 n\u00e0o \u0111\u00f3.\u00a0<\/span><\/p>\n

V\u00e0 trong v\u00ed d\u1ee5 b\u00ean d\u01b0\u1edbi \u0111\u00e2y, ch\u00fang ta s\u1ebd c\u00f9ng \u0111\u00e1nh gi\u00e1 nh\u00e2n vi\u00ean d\u1ef1a tr\u00ean s\u1ed1 ng\u00e0y l\u00e0m vi\u1ec7c c\u1ee7a h\u1ecd. C\u1ee5 th\u1ec3, nh\u1eefng b\u1ea1n thu\u1ed9c nh\u00f3m A,B v\u00e0 C c\u00f3 t\u1ed5ng s\u1ed1 ng\u00e0y l\u00e0m vi\u1ec7c l\u1edbn h\u01a1n 20 \u0111\u01b0\u1ee3c xem l\u00e0 \u201c\u0110\u1ea1t\u201d. Ng\u01b0\u1ee3c l\u1ea1i nh\u1eefng ai c\u00f3 s\u1ed1 ng\u00e0y l\u00e0m vi\u1ec7c th\u1ea5p h\u01a1n 20 th\u00ec s\u1ebd nh\u1eadn k\u1ebft qu\u1ea3 l\u00e0 \u201cKh\u00f4ng \u0111\u1ea1t\u201d.<\/span><\/p>\n

\"hlookup-10\"<\/picture><\/p>\n

V\u1edbi nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n \u1edf tr\u00ean, ch\u00fang ta ch\u1ec9 c\u1ea7n s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c \u201c<\/span>=IF(D4>=HLOOKUP(C4,$B$14:$F$15,2,0),”\u0110\u1ea1t”,”Kh\u00f4ng \u0110\u1ea1t”)\u201d<\/b>. Trong \u0111\u00f3, h\u00e0m tham chi\u1ebfu d\u1eef li\u1ec7u s\u1ebd d\u00f2 th\u00f4ng tin b\u1ea3ng 2 t\u1eeb B14 \u0111\u1ebfn F15. Sau \u0111\u00f3, h\u00e0m IF s\u1ebd ti\u1ebfn h\u00e0nh so s\u00e1nh k\u1ebft qu\u1ea3 v\u1eeba nh\u1eadn \u0111\u01b0\u1ee3c v\u1edbi s\u1ed1 ng\u00e0y l\u00e0m vi\u1ec7c c\u1ee7a m\u1ed7i ng\u01b0\u1eddi. V\u00e0 trong tr\u01b0\u1eddng h\u1ee3p th\u1ecfa \u0111i\u1ec1u ki\u1ec7n s\u1ed1 ng\u00e0y l\u00e0m vi\u1ec7c l\u1edbn h\u01a1n ch\u1ec9 ti\u00eau th\u00ec l\u00e0 \u201c\u0110\u1ea1t\u201d v\u00e0 ng\u01b0\u1ee3c l\u1ea1i l\u00e0 \u201cKh\u00f4ng \u0110\u1ea1t\u201d.<\/span><\/p>\n

\"hlookup-11\"<\/picture><\/p>\n

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

T\u1ed5ng k\u1ebft l\u1ea1i, <\/span>h\u00e0m HLOOKUP trong Excel<\/b> kh\u00f4ng ch\u1ec9 gi\u00fap gi\u1ea3m th\u1eddi gian t\u00ecm ki\u1ebfm th\u00f4ng tin, m\u00e0 c\u00f2n c\u00f3 th\u1ec3 \u00e1p d\u1ee5ng r\u1ed9ng r\u00e3i trong kh\u00e2u x\u1eed l\u00fd data c\u1ee7a nhi\u1ec1u l\u0129nh v\u1ef1c kh\u00e1c nhau. V\u00e0 nh\u01b0 nh\u1eefng th\u00f4ng tin \u1edf tr\u00ean, Ho\u00e0ng H\u00e0 Mobile \u0111\u00e3 t\u00f3m l\u01b0\u1ee3c cho b\u1ea1n c\u00e1c d\u1eef ki\u1ec7n quan tr\u1ecdng nh\u1ea5t, \u0111i k\u00e8m c\u00f4ng th\u1ee9c v\u00e0 v\u00ed d\u1ee5 minh h\u1ecda c\u1ee7a h\u00e0m. Hy v\u1ecdng qua b\u00e0i vi\u1ebft n\u00e0y, b\u1ea1n s\u1ebd \u00e1p d\u1ee5ng h\u00e0m t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb th\u00e0nh c\u00f4ng trong c\u00f4ng vi\u1ec7c c\u1ee7a m\u00ecnh nh\u00e9.<\/span><\/p>\n

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