{"id":8302,"date":"2024-12-22T06:32:37","date_gmt":"2024-12-22T06:32:37","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/22\/ham-vlookup-nang-cao-to-chuc-du-lieu-de-tranh-loi-vlookup\/"},"modified":"2024-12-22T06:32:37","modified_gmt":"2024-12-22T06:32:37","slug":"ham-vlookup-nang-cao-to-chuc-du-lieu-de-tranh-loi-vlookup","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/22\/ham-vlookup-nang-cao-to-chuc-du-lieu-de-tranh-loi-vlookup\/","title":{"rendered":"H\u00e0m VLOOKUP n\u00e2ng cao \u2013 T\u1ed5 ch\u1ee9c d\u1eef li\u1ec7u \u0111\u1ec3 tr\u00e1nh l\u1ed7i VLOOKUP"},"content":{"rendered":"
\n

Khi s\u1eed d\u1ee5ng h\u00e0m VLOOKUP<\/a> trong Excel, n\u1ebfu b\u1ea3ng tra c\u1ee9u b\u1ecb thay \u0111\u1ed5i c\u1ea5u tr\u00fac th\u00ec c\u00f4ng th\u1ee9c VLOOKUP r\u1ea5t d\u1ec5 b\u1ecb l\u1ed7i. Trong b\u00e0i vi\u1ebft n\u00e0y, H\u1ecdc Excel Online mu\u1ed1n chia s\u1ebb v\u1edbi c\u00e1c b\u1ea1n m\u1ed9t th\u1ee7 thu\u1eadt nh\u1ecf khi t\u1ed5 ch\u1ee9c d\u1eef li\u1ec7u \u0111\u1ec3 gi\u1ea3i quy\u1ebft tr\u01b0\u1eddng h\u1ee3p b\u1ea3ng tra c\u1ee9u b\u1ecb thay \u0111\u1ed5i c\u1ea5u tr\u00fac nh\u01b0 v\u1eady. Th\u1ee7 thu\u1eadt n\u00e0y \u0111\u01b0\u1ee3c \u00e1p d\u1ee5ng trong vi\u1ec7c qu\u1ea3n l\u00fd danh m\u1ee5c b\u00e1n h\u00e0ng trong c\u00f4ng vi\u1ec7c qu\u1ea3n l\u00fd kho<\/a>.<\/p>\n

Gi\u1ea3 s\u1eed ta c\u00f3 b\u1ea3ng t\u00ednh sau \u0111\u00e2y:<\/p>\n

\"thu<\/a><\/p>\n

N\u1ebfu ch\u00fang ta vi\u1ebft h\u00e0m VLOOKUP \u0111\u1ec3 tra c\u1ee9u t\u00ean h\u00e0ng theo m\u00e3 h\u00e0ng, c\u00f4ng th\u1ee9c vlookup n\u00e0y s\u1ebd nh\u01b0 sau:<\/p>\n

\n

=VLOOKUP(\u201cSGK01\u201d, $B$2:$G$11, 2,FALSE)<\/strong><\/p>\n<\/blockquote>\n

Khi ch\u00fang ta th\u00eam 1 c\u1ed9t v\u00e0o gi\u1eefa c\u1ed9t M\u00e3 H\u00e0ng v\u00e0 c\u1ed9t T\u00ean H\u00e0ng, c\u00f4ng th\u1ee9c VLOOKUP tr\u00ean s\u1ebd ph\u1ea3i s\u1eeda l\u1ea1i nh\u01b0 sau:<\/p>\n

\n

=VLOOKUP(\u201cSGK01\u201d, $B$2:$G$11, 3,FALSE)<\/strong><\/p>\n<\/blockquote>\n

Tham s\u1ed1 th\u1ee9 3 c\u1ee7a VLOOKUP c\u1ea7n \u0111\u01b0\u1ee3c ch\u1ec9nh s\u1eeda khi ch\u00fang ta thay \u0111\u1ed5i c\u1ea5u tr\u00fac c\u1ee7a b\u1ea3ng t\u00ednh. Trong tr\u01b0\u1eddng h\u1ee3p c\u1ea5u tr\u00fac b\u1ea3ng th\u01b0\u1eddng xuy\u00ean thay \u0111\u1ed5i trong qu\u00e1 tr\u00ecnh chu\u1ea9n b\u1ecb file, ch\u00fang ta s\u1ebd m\u1ea5t r\u1ea5t nhi\u1ec1u c\u00f4ng \u0111\u1ec3 thay \u0111\u1ed5i t\u1ea5t c\u1ea3 nh\u1eefng n\u01a1i c\u00f3 c\u00f4ng th\u1ee9c VLOOKUP, v\u00ec v\u1eady, m\u1ed9t gi\u1ea3i ph\u00e1p cho v\u1ea5n \u0111\u1ec1 n\u00e0y l\u00e0: th\u00eam 1 d\u00f2ng ph\u1ee5 d\u01b0\u1edbi ti\u00eau \u0111\u1ec1 v\u00e0 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c =COLUMN()\u00a0<\/strong>nh\u01b0 sau:<\/p>\n

\"th\u1ee7<\/a><\/p>\n

C\u00f4ng th\u1ee9c \u1edf h\u00e0ng th\u1ee9 2: =COLUMN()<\/strong><\/p>\n

\n
\n
\n
\n<\/a>\n<\/div>\n<\/div>\n

C\u00f4ng th\u1ee9c VLOOKUP trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, ch\u00fang ta s\u1ebd s\u1eeda l\u1ea1i nh\u01b0 sau:<\/p>\n

\n

=VLOOKUP(\u201cSGK01\u201d, $B$2:$G$12, C2 \u2013 1 <\/span>,FALSE)<\/strong><\/p>\n<\/blockquote>\n

V\u1edbi c\u00f4ng th\u1ee9c VLOOKUP nh\u01b0 v\u1eady, khi ch\u00fang ta ch\u00e8n th\u00eam c\u1ed9t v\u00e0o gi\u1eefa c\u1ed9t M\u00e3 H\u00e0ng v\u00e0 T\u00ean H\u00e0ng, c\u00f4ng th\u1ee9c VLOOKUP n\u00e0y s\u1ebd \u201ct\u1ef1 nh\u1ea3y\u201d v\u00e0 k\u1ebft qu\u1ea3 l\u00e0 ch\u00fang ta s\u1ebd kh\u00f4ng ph\u1ea3i \u0111i ch\u1ec9nh s\u1eeda l\u1ea1i c\u00f4ng th\u1ee9c VLOOKUP c\u1ee7a ch\u00fang ta n\u1eefa.<\/p>\n

C\u00e1ch l\u00e0m n\u00e0y c\u00f2n 1 l\u1ee3i \u00edch n\u1eefa, \u0111\u00f3 l\u00e0 khi ch\u00fang ta s\u1eed d\u1ee5ng t\u00ednh n\u0103ng Filter d\u1eef li\u1ec7u, n\u00fat filter s\u1ebd kh\u00f4ng \u0111\u00e8 l\u00ean ch\u1eef trong ti\u00eau \u0111\u1ec1 b\u1ea3ng nh\u01b0 sau:<\/p>\n

\"th\u1ee7<\/a><\/p>\n

K\u1ef9 thu\u1eadt t\u1ed5 ch\u1ee9c d\u1eef li\u1ec7u trong b\u1ea3ng t\u00ednh Excel n\u00e0y \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng trong vi\u1ec7c qu\u1ea3n l\u00fd danh m\u1ee5c h\u00e0ng ho\u00e1, m\u00e0 b\u1ea1n c\u00f3 th\u1ec3 tham kh\u1ea3o t\u1ea1i link sau \u0111\u00e2y:<\/p>\n

http:\/\/www.hocexcel.online\/im101<\/a><\/p>\n

Kh\u00e1m ph\u00e1 th\u00eam th\u1eadt nhi\u1ec1u ki\u1ebfn th\u1ee9c v\u1edbi H\u1ecdc Excel Online:<\/h2>\n
    \n
  1. H\u1ecdc Excel, C\u00f3 Vi\u1ec7c Ngay: https:\/\/www.hocexcel.online\/ex101<\/a><\/li>\n
  2. H\u00e3y \u0111\u1ec3 VBA gi\u00fap b\u1ea1n: https:\/\/www.hocexcel.online\/vba101<\/a><\/li>\n
  3. Hi\u1ec3u v\u00e0 ph\u00e2n t\u00edch d\u1eef li\u1ec7u v\u1edbi Excel: https:\/\/www.hocexcel.online\/dv101<\/a><\/li>\n
  4. H\u00e3y l\u00e0m k\u1ebf to\u00e1n v\u1edbi VBA: https:\/\/www.hocexcel.online\/im101<\/a><\/li>\n
  5. Qu\u1ea3n l\u00fd kho h\u00e0ng trong Excel: https:\/\/www.hocexcel.online\/im201<\/a><\/li>\n<\/ol>\n<\/div>\n
    \n

    Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n

    T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0\ud83d\udcd6<\/span><\/a><\/p>\n

    H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n

    HKD H\u1ecdc Excel Online
    S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n

    \n

    \u00a9 document.currentScript.insertAdjacentHTML(‘afterend’, ” + new Intl.DateTimeFormat(document.documentElement.lang, {year: ‘numeric’}).format() + ”); H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

    \n
    \n
    \n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":8303,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-8302","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\/8302","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=8302"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/8302\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/8303"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=8302"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=8302"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=8302"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}