{"id":6323,"date":"2024-12-21T15:04:00","date_gmt":"2024-12-21T15:04:00","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/power-query-da-cuu-vot-thoi-gian-thuc-tap-cua-minh-nhu-nao-phan-2\/"},"modified":"2024-12-21T15:04:00","modified_gmt":"2024-12-21T15:04:00","slug":"power-query-da-cuu-vot-thoi-gian-thuc-tap-cua-minh-nhu-nao-phan-2","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/power-query-da-cuu-vot-thoi-gian-thuc-tap-cua-minh-nhu-nao-phan-2\/","title":{"rendered":"Power Query \u0111\u00e3 c\u1ee9u v\u1edbt th\u1eddi gian th\u1ef1c t\u1eadp c\u1ee7a m\u00ecnh nh\u01b0 n\u00e0o? \u2013 Ph\u1ea7n 2"},"content":{"rendered":"
\n

Trong ph\u1ea7n tr\u01b0\u1edbc, H\u1ecdc Excel Online<\/strong> <\/a>\u0111\u00e3 n\u00f3i s\u01a1 qua v\u1ec1 nh\u1eefng c\u00f4ng vi\u1ec7c ph\u1ea3i l\u00e0m trong th\u00e1ng \u0111\u1ea7u th\u1ef1c t\u1eadp v\u00e0 \u00fd t\u01b0\u1edfng trong \u0111\u1ea7u c\u1ee7a m\u00ecnh \u0111\u1ec3 th\u1ef1c hi\u1ec7n n\u00f3. Ph\u1ea7n 2 s\u1ebd l\u00e0 kh\u00f3 kh\u0103n \u0111\u1ea7u ti\u00ean m\u00ecnh g\u1eb7p ph\u1ea3i khi \u1ee9ng d\u1ee5ng Power Query<\/strong><\/a>: \u0111\u01b0a d\u1eef li\u1ec7u v\u00e0o<\/em><\/p>\n

L\u1ed7i khi \u0111\u01b0a d\u1eef li\u1ec7u v\u00e0o b\u1eb1ng Power Query<\/strong><\/p>\n

Nh\u01b0ng m\u00e0 m\u1ecdi th\u1ee9 kh\u00f4ng c\u00f3 d\u1ec5 d\u00e0ng nh\u01b0 v\u1eady. Ngay t\u1eeb b\u01b0\u1edbc \u0111\u1ea7u ti\u00ean \u0111\u01b0a d\u1eef li\u1ec7u v\u1ec1 m\u00ecnh \u0111\u00e3 g\u1eb7p v\u1ea5n \u0111\u1ec1: Sai d\u1eef li\u1ec7u do 1. Ti\u00eau \u0111\u1ec1 c\u00e1c b\u1ea3ng b\u1ecb tr\u1ed9n \u00f4 (Merge cell), 2. D\u00f9 \u0111\u00e3 chuy\u1ec3n v\u1ec1 d\u1ea1ng b\u1ea3ng (Table) nh\u01b0ng v\u1eabn kh\u00f4ng nh\u1eadp \u0111\u01b0\u1ee3c b\u1ea5t c\u1ee9 c\u1ed9t d\u1eef li\u1ec7u n\u00e0o.<\/p>\n

\"Data
Data ban \u0111\u1ea7u tr\u00f4ng nh\u01b0 th\u1ebf n\u00e0y<\/figcaption><\/figure>\n

M\u00ecnh b\u1eaft \u0111\u1ea7u th\u1eed c\u00e1c c\u00e1ch nh\u01b0\u00a0thay v\u00ec import table th\u00ec chuy\u1ec3n qua import range, import s\u1eed d\u1ee5ng Microsoft Query\u2026\u00a0<\/em>nh\u01b0ng t\u1ea5t c\u1ea3 \u0111\u1ec1u kh\u00f4ng kh\u1ea3 thi.<\/p>\n

\u00a0<\/p>\n

\"l\u1ed7i<\/p>\n

Tr\u00ean \u0111\u00e2y l\u00e0 l\u1ed7i g\u1eb7p ph\u1ea3i khi m\u00ecnh s\u1eed d\u1ee5ng ph\u01b0\u01a1ng ph\u00e1p Pivot d\u1eef li\u1ec7u t\u1eeb nhi\u1ec1u worksheet. Khi m\u00ecnh \u0111em l\u1ed7i n\u00e0y l\u00ean Google h\u1ecfi, m\u00ecnh kh\u00f4ng nh\u1eadn \u0111\u01b0\u1ee3c b\u1ea5t c\u1ee9 c\u00e2u tr\u1ea3 l\u1eddi n\u00e0o th\u1ecfa \u0111\u00e1ng.<\/p>\n

Sau \u0111\u00f3 m\u00ecnh nh\u1eadn ra v\u1ea5n \u0111\u1ec1 n\u1eb1m \u1edf\u00a0kho\u1ea3ng tr\u1eafng \u0111\u1ea7u<\/strong>. V\u00ec m\u1ed9t l\u00fd do n\u00e0o \u0111\u00f3 m\u00e0 t\u1ea5t c\u1ea3 ti\u00eau \u0111\u1ec1 c\u1ed9t c\u0169ng nh\u01b0 t\u00ean trang t\u00ednh \u0111\u1ec1u c\u00f3 m\u1ed9t k\u00ed t\u1ef1 tr\u1eafng \u1edf \u0111\u1ea7u. C\u00f3 l\u1ebd b\u1edfi v\u1eady m\u00e0 m\u00ecnh kh\u00f4ng th\u1ec3 n\u1ea1p d\u1eef li\u1ec7u v\u00e0o \u0111\u01b0\u1ee3c. \u0110\u1ec3 s\u1eeda, m\u00ecnh th\u1eed x\u00f3a h\u1ebft s\u1ed1 kho\u1ea3ng tr\u1eafng th\u1eeba xem sao.<\/p>\n

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

V\u00e0 b\u1edfi v\u00ec nh\u1eefng file h\u00e0ng ng\u00e0y m\u00ecnh nh\u1eadn \u0111\u01b0\u1ee3c c\u00f3 c\u1ea5u tr\u00fac y h\u00eat nhau, n\u00ean m\u00ecnh \u0111\u00e3 ngh\u0129 t\u1edbi ph\u01b0\u01a1ng ph\u00e1p: T\u1ea1o m\u1ed9t \u0111o\u1ea1n code VBA c\u00f3 t\u00e1c d\u1ee5ng x\u00f3a h\u1ebft nh\u1eefng kho\u1ea3ng tr\u1eafng th\u1eeba \u1edf c\u00e1c c\u1ed9t c\u0169ng nh\u01b0 t\u00ean sheet, \u0111\u1ed3ng th\u1eddi \u0111\u01b0a d\u1eef li\u1ec7u t\u1eeb d\u1ea1ng range v\u1ec1 table \u0111\u1ec3 d\u1ec5 d\u00e0ng \u0111\u01b0a v\u00e0o Query.<\/p>\n

V\u1edbi s\u1ef1 gi\u00fap \u0111\u1ee1 c\u1ee7a anh Nguy\u1ec5n c\u00f9ng b\u00e0i vi\u1ebft c\u1ee7a anh T\u1ea0I LINK N\u00c0Y<\/a>, cu\u1ed1i c\u00f9ng m\u00ecnh \u0111\u00e3 c\u00f3 th\u1ec3 \u0111\u01b0a d\u1eef li\u1ec7u v\u00e0o b\u00ecnh th\u01b0\u1eddng.<\/em><\/p>\n

\u0110o\u1ea1n code VBA (mang t\u00ednh ch\u1ea5t tham kh\u1ea3o b\u1edfi m\u00ecnh \u0111\u00e3 fix k\u00edch c\u1ee1 b\u1ea3ng):<\/p>\n

\"\u0110o\u1ea1n<\/p>\n

\u00a0<\/p>\n

D\u1eef li\u1ec7u sau khi ch\u1ec9nh s\u1eeda \u0111\u00e3 c\u00f3 th\u1ec3 n\u1ea1p v\u00e0o b\u00ecnh th\u01b0\u1eddng:<\/p>\n

\"D\u1eef<\/p>\n

V\u00e0 \u0111\u00f3 l\u00e0 c\u00e2u chuy\u1ec7n m\u00ecnh \u0111\u00e3 l\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 v\u01b0\u1ee3t qua b\u01b0\u1edbc kh\u00f3 kh\u0103n \u0111\u1ea7u khi \u0111\u01b0a d\u1eef li\u1ec7u v\u00e0o. Tuy nhi\u00ean m\u1ecdi chuy\u1ec7n kh\u00f4ng ch\u1ec9 d\u1eebng l\u1ea1i \u1edf \u0111\u00f3, c\u00f2n r\u1ea5t nhi\u1ec1u v\u1ea5n \u0111\u1ec1 m\u00ecnh g\u1eb7p ph\u1ea3i trong nh\u1eefng b\u01b0\u1edbc ti\u1ebfp theo cho t\u1edbi t\u1eadn khi ra \u0111\u01b0\u1ee3c m\u1ed9t b\u00e1o c\u00e1o ho\u00e0n ch\u1ec9nh. H\u00e3y theo d\u00f5i nh\u1eefng ph\u1ea7n ti\u1ebfp theo nh\u00e9.<\/p>\n

\n

Danh s\u00e1ch c\u00e1c ph\u1ea7n:<\/p>\n

Power Query Ph\u1ea7n 1<\/a><\/p>\n

Power Query Ph\u1ea7n 3<\/a><\/p>\n

Power Query Ph\u1ea7n 4<\/a><\/p>\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":6324,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-6323","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\/6323","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=6323"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/6323\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/6324"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=6323"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=6323"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=6323"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}