{"id":3979,"date":"2024-12-04T08:29:30","date_gmt":"2024-12-04T08:29:30","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/04\/ung-dung-power-query-xay-dung-mo-hinh-du-lieu-phan-tich-nang-suat-lao-dong-phan-1\/"},"modified":"2024-12-04T08:29:30","modified_gmt":"2024-12-04T08:29:30","slug":"ung-dung-power-query-xay-dung-mo-hinh-du-lieu-phan-tich-nang-suat-lao-dong-phan-1","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/04\/ung-dung-power-query-xay-dung-mo-hinh-du-lieu-phan-tich-nang-suat-lao-dong-phan-1\/","title":{"rendered":"\u1ee8ng d\u1ee5ng Power Query x\u00e2y d\u1ef1ng m\u00f4 h\u00ecnh d\u1eef li\u1ec7u ph\u00e2n t\u00edch n\u0103ng su\u1ea5t lao \u0111\u1ed9ng \u2013 Ph\u1ea7n 1"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Trong b\u00e0i vi\u1ebft n\u00e0y, ch\u00fang ta c\u00f9ng t\u00ecm hi\u1ec3u m\u1ed9t\u00a0\u1ee9ng d\u1ee5ng c\u1ee7a Power Query trong vi\u1ec7c x\u00e2y d\u1ef1ng m\u00f4 h\u00ecnh d\u1eef li\u1ec7u v\u1ec1 ph\u00e2n t\u00edch n\u0103ng su\u1ea5t lao\u00a0\u0111\u1ed9ng. M\u00f4 h\u00ecnh n\u00e0y c\u0169ng th\u01b0\u1eddng g\u1eb7p trong th\u1ef1c t\u1ebf khi l\u00e0m vi\u1ec7c v\u1edbi\u00a0Excel. H\u00e3y xem v\u1edbi Power Query ch\u00fang ta c\u00f3 th\u1ec3 l\u00e0m\u00a0\u0111\u01b0\u1ee3c g\u00ec 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-675012e976380\" 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\/ung-dung-power-query-xay-dung-mo-hinh-du-lieu-phan-tich-nang-suat-lao-dong-phan-1.html#Bai_tap_xay_dung_mo_hinh_du_lieu_phan_tich_nang_suat_lao_dong\" title=\"B\u00e0i t\u1eadp x\u00e2y d\u1ef1ng m\u00f4 h\u00ecnh d\u1eef li\u1ec7u ph\u00e2n t\u00edch n\u0103ng su\u1ea5t lao\u00a0\u0111\u1ed9ng\">B\u00e0i t\u1eadp x\u00e2y d\u1ef1ng m\u00f4 h\u00ecnh d\u1eef li\u1ec7u ph\u00e2n t\u00edch n\u0103ng su\u1ea5t lao\u00a0\u0111\u1ed9ng<\/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\/ung-dung-power-query-xay-dung-mo-hinh-du-lieu-phan-tich-nang-suat-lao-dong-phan-1.html#Phan_tich_cau_truc_du_lieu\" title=\"Ph\u00e2n t\u00edch c\u1ea5u tr\u00fac d\u1eef li\u1ec7u\">Ph\u00e2n t\u00edch c\u1ea5u tr\u00fac d\u1eef li\u1ec7u<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/ung-dung-power-query-xay-dung-mo-hinh-du-lieu-phan-tich-nang-suat-lao-dong-phan-1.html#Dac_diem_bang_du_lieu_trong_bai_tap\" title=\"\u0110\u1eb7c\u00a0\u0111i\u1ec3m b\u1ea3ng d\u1eef li\u1ec7u trong b\u00e0i t\u1eadp\">\u0110\u1eb7c\u00a0\u0111i\u1ec3m b\u1ea3ng d\u1eef li\u1ec7u trong b\u00e0i t\u1eadp<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/ung-dung-power-query-xay-dung-mo-hinh-du-lieu-phan-tich-nang-suat-lao-dong-phan-1.html#Cau_truc_lai_bang_du_lieu_bang_Power_Query\" title=\"C\u1ea5u tr\u00fac l\u1ea1i b\u1ea3ng d\u1eef li\u1ec7u b\u1eb1ng Power Query\">C\u1ea5u tr\u00fac l\u1ea1i b\u1ea3ng d\u1eef li\u1ec7u b\u1eb1ng Power Query<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Bai_tap_xay_dung_mo_hinh_du_lieu_phan_tich_nang_suat_lao_dong\"><\/span>B\u00e0i t\u1eadp x\u00e2y d\u1ef1ng m\u00f4 h\u00ecnh d\u1eef li\u1ec7u ph\u00e2n t\u00edch n\u0103ng su\u1ea5t lao\u00a0\u0111\u1ed9ng<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Tr\u01b0\u1edbc h\u1ebft, c\u00e1c b\u1ea1n c\u00f3 th\u1ec3 t\u1ea3i file b\u00e0i t\u1eadp t\u1ea1i\u00a0\u0111\u1ecba ch\u1ec9:\u00a0<a href=\"http:\/\/bit.ly\/31Yt0gn\">http:\/\/bit.ly\/31Yt0gn<\/a><\/p>\n<p>N\u1ed9i dung g\u1ed3m 3 b\u1ea3ng d\u1eef li\u1ec7u t\u01b0\u01a1ng\u00a0\u1ee9ng v\u1edbi 3 n\u0103m: 2017, 2018, 2019. Trong\u00a0\u0111\u00f3 t\u1ea1i m\u1ed7i b\u1ea3ng\u00a0g\u1ed3m:<\/p>\n<ul>\n<li>Chi nh\u00e1nh:\u00a0\u1ee9ng v\u1edbi c\u00e1c t\u1ec9nh, th\u00e0nh ph\u1ed1 c\u1ee7a Vi\u1ec7t Nam.<\/li>\n<li>D\u1ef1\u00a0\u00e1n: c\u00f3 5 d\u1ef1\u00a0\u00e1n, c\u00e1c chi nh\u00e1nh\u00a0\u0111\u1ec1u tham gia v\u00e0o 5 d\u1ef1\u00a0\u00e1n n\u00e0y.<\/li>\n<li>S\u1ed1\u00a0lao\u00a0\u0111\u1ed9ng v\u00e0 Gi\u00e1 tr\u1ecb th\u1ef1c hi\u1ec7n c\u00e1c d\u1ef1\u00a0\u00e1n: t\u00f9y\u00a0theo t\u1eebng th\u00e1ng, t\u1eebng n\u0103m m\u00e0 c\u00f3 s\u1ef1 thay\u00a0\u0111\u1ed5i, kh\u00f4ng c\u1ed1\u00a0\u0111\u1ecbnh. C\u00e1c gi\u00e1 tr\u1ecb n\u00e0y l\u00e0 gi\u00e1 tr\u1ecb\u00a0\u0111\u01b0\u1ee3c ghi nh\u1eadn l\u1ea1i theo b\u1ea3ng k\u1ebft qu\u1ea3\u00a0\u0111\u00e3 c\u00f3 nh\u01b0 trong b\u00e0i.<\/li>\n<\/ul>\n<p>C\u00f3 th\u1ec3 th\u1ea5y b\u1ea3ng d\u1eef li\u1ec7u l\u00e0 kh\u00e1 l\u1edbn, c\u00f3 nhi\u1ec1u ch\u1ec9 ti\u00eau\u00a0\u0111\u1ec3 ph\u00e2n t\u00edch. Nh\u01b0ng c\u00f3 qu\u00e1 nhi\u1ec1u d\u1eef li\u1ec7u! L\u00e0m sao ph\u00e2n t\u00edch\u00a0\u0111\u01b0\u1ee3c\u00a0n\u0103ng su\u1ea5t lao\u00a0\u0111\u1ed9ng v\u1edbi 3 b\u1ea3ng n\u00e0y? B\u1ea1n\u00a0\u0111\u00e3 s\u1eb5n s\u00e0ng\u00a0\u0111i t\u00ecm c\u00e2u tr\u1ea3 l\u1eddi ch\u01b0a n\u00e0o.<\/p>\n<p>\u0110\u00e2y l\u00e0 k\u1ebft qu\u1ea3 sau khi b\u1ea1n theo d\u00f5i\u00a0\u0111\u1ebfn h\u1ebft Seri b\u00e0i vi\u1ebft n\u00e0y:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-30473\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/dashboard-phan-tich-nang-suat-lao-dong-1024x736-2.gif\" width=\"1024\" height=\"736\" title=\"dashboard-phan-tich-nang-suat-lao-dong-1024x736-2\"><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Phan_tich_cau_truc_du_lieu\"><\/span>Ph\u00e2n t\u00edch c\u1ea5u tr\u00fac d\u1eef li\u1ec7u<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"Dac_diem_bang_du_lieu_trong_bai_tap\"><\/span>\u0110\u1eb7c\u00a0\u0111i\u1ec3m b\u1ea3ng d\u1eef li\u1ec7u trong b\u00e0i t\u1eadp<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Nh\u00ecn v\u00e0o c\u1ea3 3 b\u1ea3ng, ch\u00fang ta nh\u1eadn th\u1ea5y c\u00f3 1\u00a0\u0111\u1eb7c\u00a0\u0111i\u1ec3m chung:<\/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\/2024\/12\/ex101_92019-nho-316.gif\" title=\"ex101_92019-nho-316\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-30477\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cau-truc-bang-du-lieu-goc-1024x321-1.png\" width=\"1024\" height=\"321\" title=\"cau-truc-bang-du-lieu-goc-1024x321-1\"><\/p>\n<p>D\u1eef li\u1ec7u\u00a0\u0111\u01b0\u1ee3c t\u1ed5 ch\u1ee9c theo d\u1ea1ng b\u1ea3ng 2 chi\u1ec1u, g\u1ed3m:<\/p>\n<ul>\n<li>Chi\u1ec1u d\u1ecdc (\u0111\u01b0\u1eddng m\u0169i t\u00ean m\u00e0u\u00a0\u0111\u1ecf) : M\u1ed7i chi nh\u00e1nh s\u1ebd\u00a0\u1ee9ng v\u1edbi 1 d\u00f2ng<\/li>\n<li>Chi\u1ec1u ngang (\u0111\u01b0\u1eddng m\u0169i t\u00ean m\u00e0u cam v\u00e0 m\u00e0u xanh) :\u00a0\u1ee8ng v\u1edbi c\u00e1c th\u00e1ng, trong m\u1ed7i th\u00e1ng s\u1ebd g\u1ed3m th\u00f4ng tin v\u1ec1 s\u1ed1 lao\u00a0\u0111\u1ed9ng v\u00e0 c\u00e1c d\u1ef1\u00a0\u00e1n.<\/li>\n<\/ul>\n<p>Giao\u00a0\u0111i\u1ec3m c\u1ee7a 2 chi\u1ec1u n\u00e0y ch\u00ednh l\u00e0 gi\u00e1 tr\u1ecb t\u01b0\u01a1ng\u00a0\u1ee9ng:<\/p>\n<ul>\n<li>\u00d4 B3: S\u1ed1 lao\u00a0\u0111\u1ed9ng trong th\u00e1ng 1 c\u1ee7a chi nh\u00e1nh An Giang<\/li>\n<li>\u00d4 G8: Gi\u00e1 tr\u1ecb c\u1ee7a d\u1ef1\u00a0\u00e1n 5 trong th\u00e1ng 1 c\u1ee7a chi nh\u00e1nh B\u1eafc Ninh<\/li>\n<\/ul>\n<p>C\u00f3 th\u1ec3 th\u1ea5y vi\u1ec7c t\u1ed5 ch\u1ee9c d\u1eef li\u1ec7u nh\u01b0 v\u1eady s\u1ebd gi\u00fap cho b\u1ea1n nhanh ch\u00f3ng ghi nh\u1eadn c\u00e1c d\u1eef li\u1ec7u ph\u00e1t sinh, nh\u01b0ng l\u1ea1i r\u1ea5t kh\u00f3 kh\u0103n trong vi\u1ec7c ph\u00e2n t\u00edch d\u1eef li\u1ec7u (kh\u00f4ng s\u1eed d\u1ee5ng \u0111\u01b0\u1ee3c Pivot Table).<\/p>\n<p>V\u1eady l\u00e0m th\u00ea n\u00e0o\u00a0\u0111\u1ec3 c\u00f3 th\u1ec3 v\u1eeba nh\u1eadp d\u1eef li\u1ec7u tr\u00ean c\u1ea5u tr\u00fac b\u1ea3ng c\u0169, v\u1eeba c\u00f3 b\u1ea3ng d\u1eef li\u1ec7u\u00a0\u0111\u00fang c\u1ea5u tr\u00fac c\u00f3 th\u1ec3 d\u00f9ng\u00a0\u0111\u01b0\u1ee3c Pivot Table?<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Cau_truc_lai_bang_du_lieu_bang_Power_Query\"><\/span>C\u1ea5u tr\u00fac l\u1ea1i b\u1ea3ng d\u1eef li\u1ec7u b\u1eb1ng Power Query<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>B\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng Power Query, ch\u00fang ta c\u00f3 th\u1ec3 t\u00e1i c\u1ea5u tr\u00fac l\u1ea1i b\u1ea3ng d\u1eef li\u1ec7u trong c\u00e1c n\u0103m. Quy tr\u00ecnh th\u1ef1c hi\u1ec7n g\u1ed3m c\u00e1c b\u01b0\u1edbc sau:<\/p>\n<p><strong>B\u01b0\u1edbc 1: Chuy\u1ec3n b\u1ea3ng d\u1eef li\u1ec7u g\u1ed1c v\u1ec1 d\u1ea1ng Table<\/strong><\/p>\n<p>M\u1ee5c\u00a0\u0111\u00edch: H\u1ea7u h\u1ebft m\u1ecdi thao t\u00e1c x\u1eed l\u00fd d\u1eef li\u1ec7u v\u1edbi Power Query\u00a0\u0111\u1ec1u th\u1ef1c hi\u1ec7n t\u1ed1t h\u01a1n khi d\u1eef li\u1ec7u\u00a0\u1edf d\u1ea1ng Table.<\/p>\n<p>V\u00ed d\u1ee5 v\u1edbi b\u1ea3ng d\u1eef li\u1ec7u n\u0103m 2017 (Sheet 2017):<\/p>\n<ul>\n<li>Ch\u1ecdn to\u00e0n b\u1ed9 b\u1ea3ng d\u1eef li\u1ec7u<\/li>\n<li>Trong th\u1ebb\u00a0Insert, ch\u1ecdn m\u1ee5c Table (ph\u00edm t\u1eaft l\u00e0 Ctrl + T)<\/li>\n<li>Khi hi\u1ec7n l\u00ean h\u1ed9p tho\u1ea1i x\u00e1c nh\u1eadn vi\u1ec7c chuy\u1ec3n sang d\u1ea1ng Table, b\u1ea1n b\u1ecf d\u1ea5u t\u00edch ch\u1ecdn t\u1ea1i m\u1ee5c \u2018My Data has Headers\u2019<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-30483\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-1-chuyen-du-lieu-goc-ve-dang-table.png\" width=\"524\" height=\"251\" title=\"1-1-chuyen-du-lieu-goc-ve-dang-table\"><\/p>\n<p>Khi th\u1ef1c hi\u1ec7n thao t\u00e1c n\u00e0y,\u00a0Excel s\u1ebd b\u1ecf tr\u1ed9n\u00a0\u00f4 v\u00e0 t\u1ea1o ra th\u00eam 1 d\u00f2ng l\u00e0m d\u00f2ng ti\u00eau\u00a0\u0111\u1ec1. B\u1ea1n kh\u00f4ng c\u1ea7n lo l\u1eafng v\u1ec1 vi\u1ec7c n\u00e0y. Ch\u00fang ta v\u1eabn c\u00f3 th\u1ec3 nh\u1eadp d\u1eef li\u1ec7u b\u00ecnh th\u01b0\u1eddng v\u00e0o b\u1ea3ng. Ch\u1ec9 kh\u00e1c l\u00e0 thay v\u00ec\u00a0\u1edf d\u1ea1ng Range th\u00ec nh\u1eadp\u00a0\u1edf d\u1ea1ng Table.<\/p>\n<p><strong>B\u01b0\u1edbc 2:\u00a0\u0110\u1eb7t t\u00ean cho b\u1ea3ng Table<\/strong><\/p>\n<p>V\u00ec c\u00f3 nhi\u1ec1u b\u1ea3ng kh\u00e1c nhau n\u00ean ch\u00fang ta c\u00f3 th\u1ec3\u00a0\u0111\u1eb7t t\u00ean cho c\u00e1c b\u1ea3ng\u00a0\u0111\u1ec3 tr\u00e1nh nh\u1ea7m l\u1eabn.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-30488\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-2-dat-ten-cho-table.png\" width=\"782\" height=\"128\" title=\"1-2-dat-ten-cho-table\"><\/p>\n<p>T\u00ean c\u00f3 th\u1ec3\u00a0\u0111\u1eb7t d\u01b0\u1edbi d\u1ea1ng<\/p>\n<ul>\n<li><strong>Nam_2017<\/strong> cho b\u1ea3ng trong Sheet 2017<\/li>\n<li><strong>Nam_2018<\/strong> cho b\u1ea3ng trong Sheet 2018<\/li>\n<li><strong>Nam_2019<\/strong> cho b\u1ea3ng trong Sheet 2019<\/li>\n<\/ul>\n<p><strong>B\u01b0\u1edbc 3: N\u1ea1p d\u1eef li\u1ec7u v\u00e0o Power Query<\/strong><\/p>\n<p>Trong Power Query, ch\u1ecdn m\u1ee5c <strong>From Table\/Range<\/strong> (V\u1edbi\u00a0Excel 2016 ho\u1eb7c\u00a0Office 365 th\u00ec t\u00ecm trong th\u1ebb Data):<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-30489\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-3-nap-du-lieu-vao-power-query-voi-from-table-range.png\" width=\"652\" height=\"161\" title=\"1-3-nap-du-lieu-vao-power-query-voi-from-table-range\"><\/p>\n<p><strong>B\u01b0\u1edbc 4:\u00a0\u0110\u1ecbnh h\u00ecnh b\u1ea3ng d\u1eef li\u1ec7u\u00a0\u0111\u00fang<\/strong><\/p>\n<p>(b\u01b0\u1edbc n\u00e0y c\u00f3 th\u1ec3 l\u00e0m ngay t\u1eeb tr\u01b0\u1edbc b\u01b0\u1edbc 1)<\/p>\n<p>C\u1ea5u tr\u00fac\u00a0\u0111\u00fang l\u00e0 c\u1ea5u tr\u00fac m\u00e0 v\u1edbi m\u1ed7i 1 gi\u00e1 tr\u1ecb s\u1ebd bao g\u1ed3m\u00a0\u0111\u1ea7y\u00a0\u0111\u1ee7 c\u00e1c n\u1ed9i dung c\u00f3 li\u00ean quan\u00a0tr\u00ean c\u00f9ng 1 d\u00f2ng.<\/p>\n<p>V\u00ed d\u1ee5:<\/p>\n<ul>\n<li>Chi nh\u00e1nh A, trong th\u00e1ng 1, c\u00f3 S\u1ed1 lao\u00a0\u0111\u1ed9ng l\u00e0 201 =&gt; Nh\u01b0 v\u1eady s\u1ebd g\u1ed3m 3 c\u1ed9t: Chi nh\u00e1nh, Th\u00e1ng. S\u1ed1 lao\u00a0\u0111\u1ed9ng<\/li>\n<li>Chi nh\u00e1nh B, trong th\u00e1ng 2, v\u1edbi D\u1ef1\u00a0\u00e1n 1, c\u00f3 gi\u00e1 tr\u1ecb l\u00e0 20000 =&gt; Nh\u01b0 v\u1eady s\u1ebd g\u1ed3m 4 c\u1ed9t: Chi nh\u00e1nh, Th\u00e1ng, D\u1ef1\u00a0\u00e1n, Gi\u00e1 tr\u1ecb<\/li>\n<\/ul>\n<p>Th\u00f4ng qua nh\u1eadn\u00a0\u0111\u1ecbnh m\u1ed9t v\u00e0i n\u1ed9i dung, ch\u00fang ta c\u00f3 th\u1ec3 t\u1ed5ng h\u1ee3p l\u1ea1i c\u1ea5u tr\u00fac 1 b\u1ea3ng d\u1eef li\u1ec7u\u00a0\u0111\u00fang chu\u1ea9n s\u1ebd g\u1ed3m:<\/p>\n<ul>\n<li>C\u00e1c c\u1ed9t: Chi nh\u00e1nh, Th\u00e1ng, D\u1ef1\u00a0\u00e1n, S\u1ed1 lao\u00a0\u0111\u1ed9ng, Gi\u00e1 tr\u1ecb<\/li>\n<li>V\u1edbi c\u1ed9t S\u1ed1 lao\u00a0\u0111\u1ed9ng v\u00e0 Gi\u00e1 tr\u1ecb s\u1ebd c\u00f3 d\u1eef li\u1ec7u t\u01b0\u01a1ng\u00a0\u1ee9ng theo Chi nh\u00e1nh, Th\u00e1ng, D\u1ef1\u00a0\u00e1n<\/li>\n<\/ul>\n<p>Do\u00a0\u0111\u00f3 c\u1ea5u tr\u00fac tr\u00ean\u00a0Excel s\u1ebd l\u00e0:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-30490\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-4-cau-truc-bang-du-lieu-dung.png\" width=\"593\" height=\"370\" title=\"1-4-cau-truc-bang-du-lieu-dung\"><\/p>\n<p><strong>B\u01b0\u1edbc 5: X\u1eed l\u00fd trong Power Query\u00a0\u0111\u1ec3 ra\u00a0\u0111\u01b0\u1ee3c c\u1ea5u tr\u00fac\u00a0\u0111\u00fang<\/strong><\/p>\n<p>Trong Power Query, giao di\u1ec7n b\u1ea3ng d\u1eef li\u1ec7u khi n\u1ea1p v\u00e0o c\u00f3 d\u1ea1ng:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-30491\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-5-1-giao-dien-power-query.png\" width=\"836\" height=\"191\" title=\"1-5-1-giao-dien-power-query\"><\/p>\n<p>C\u00f3 th\u1ec3 th\u1ea5y\u00a02 d\u00f2ng\u00a0\u0111\u1ea7u ti\u00ean ch\u1ee9a th\u00f4ng tin v\u1ec1 c\u00e1c th\u00e1ng v\u00e0 S\u1ed1 lao\u00a0\u0111\u1ed9ng, D\u1ef1\u00a0\u00e1n. Do\u00a0\u0111\u00f3 c\u00f4ng vi\u1ec7c s\u1ebd g\u1ed3m:<\/p>\n<p><strong>5.1. \u0110\u01b0a d\u00f2ng 1 v\u00e0 2 v\u1ec1 c\u00f9ng 1 d\u00f2ng: <\/strong><\/p>\n<ul>\n<li>Trong th\u1ebb Transform, ch\u1ecdn m\u1ee5c\u00a0<strong>Transpose<\/strong> (c\u1ea3 b\u1ea3ng): Chuy\u1ec3n d\u00f2ng th\u00e0nh c\u1ed9t, c\u1ed9t th\u00e0nh d\u00f2ng. M\u1ee5c \u0111\u00edch \u0111\u01b0a n\u1ed9i dung c\u1ea7n x\u1eed l\u00fd (d\u1eef li\u1ec7u t\u1ea1i c\u00e1c c\u1ed9t trong ph\u1ea1m vi 2 d\u00f2ng \u0111\u1ea7u)\u00a0xu\u1ed1ng th\u00e0nh c\u00e1c d\u00f2ng (th\u00e0nh 2 c\u1ed9t, nhi\u1ec1u d\u00f2ng)<\/li>\n<li>Trong th\u1ebb Transform, ch\u1ecdn m\u1ee5c <strong>Fill &gt; Down\u00a0<\/strong>cho c\u1ed9t Column1:\u00a0\u0110\u1ec3 l\u00e0m\u00a0\u0111\u1ea7y c\u00e1c gi\u00e1 tr\u1ecb Null b\u1eb1ng n\u1ed9i dung\u00a0\u1edf d\u00f2ng tr\u00ean (gi\u00e1 tr\u1ecb Null c\u00f3 ngh\u0129a l\u00e0\u00a0\u00f4 tr\u1ed1ng, xu\u1ea5t hi\u1ec7n khi b\u1ecf tr\u1ed9n\u00a0\u00f4)<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-30492\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-5-2-fill-down-trong-power-bi.png\" width=\"526\" height=\"404\" title=\"1-5-2-fill-down-trong-power-bi\"><\/p>\n<ul>\n<li>Transform &gt; <strong>Merge Columns<\/strong>: Tr\u1ed9n c\u1ed9t\u00a0Column1\u00a0v\u00e0\u00a0Column2 v\u00e0o chung 1 c\u1ed9t (\u0111\u1ec3\u00a0t\u1eeb 2\u00a0c\u1ed9t\u00a0chuy\u1ec3n v\u1ec1 1 c\u1ed9t). Khi g\u1ed9p n\u00ean l\u1ea5y theo 1 k\u00fd t\u1ef1 \u0111\u1eb7c bi\u1ec7t \u0111\u1ec3 c\u00f3 th\u1ec3 d\u00f9ng cho vi t\u00e1ch l\u1ea1i 2 d\u1eef li\u1ec7u n\u00e0y. V\u00ed d\u1ee5: D\u1ea5u ph\u1ea9y (comma)<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-30496\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-5-3-merge-column-power-query-2-1024x466-1.png\" width=\"1024\" height=\"466\" title=\"1-5-3-merge-column-power-query-2-1024x466-1\"><\/p>\n<ul>\n<li>Sau ghi gh\u00e9p c\u1ed9t xong, th\u1ef1c hi\u1ec7n l\u1ea1i thao t\u00e1c xoay b\u1ea3ng v\u1edbi <strong>Transpose<\/strong>. L\u00fac n\u00e0y ch\u1ec9 c\u00f2n l\u1ea1i d\u00f2ng 1 ch\u1ee9a th\u00f4ng tin v\u1edbi vai tr\u00f2 d\u00f2ng ti\u00eau\u00a0\u0111\u1ec1.<\/li>\n<li>Trong th\u1ebb Transform, s\u1eed d\u1ee5ng\u00a0<strong>Use First Row\u00a0as Headers<\/strong>:\u00a0\u0110\u1ec3 s\u1eed d\u1ee5ng d\u00f2ng 1 l\u00e0m d\u00f2ng ti\u00eau\u00a0\u0111\u1ec1 cho b\u1ea3ng.<\/li>\n<\/ul>\n<p><strong>5.2.\u00a0\u0110\u01b0a b\u1ea3ng d\u1eef li\u1ec7u v\u1ec1\u00a0\u0111\u00fang c\u1ea5u tr\u00fac<\/strong><\/p>\n<ul>\n<li>Trong th\u1ebb Transform, s\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng Unpivot Columns b\u1eb1ng c\u00e1ch: Ch\u1ecdn c\u1ed9t Chi nh\u00e1nh, sau\u00a0\u0111\u00f3 ch\u1ecdn m\u1ee5c Unpivot Other Column (Unpivot c\u00e1c c\u1ed9t c\u00f2n l\u1ea1i).<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-30494\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-5-5-unpivot-other-column.png\" width=\"255\" height=\"112\" title=\"1-5-5-unpivot-other-column\"><\/p>\n<p>K\u1ebft qu\u1ea3 s\u1ebd c\u00f3 d\u1ea1ng:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-30497\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-5-6-bang-du-lieu-sau-unpivot-column-2.png\" width=\"636\" height=\"191\" title=\"1-5-6-bang-du-lieu-sau-unpivot-column-2\"><\/p>\n<p><em>Ch\u1ee9c n\u0103ng <strong>Unpivot Column<\/strong> s\u1ebd gi\u00fap\u00a0\u0111\u01b0a c\u00e1c\u00a0d\u1eef li\u1ec7u tr\u00ean c\u00e1c d\u00f2ng ti\u00eau\u00a0\u0111\u1ec1 th\u00e0nh d\u1eef li\u1ec7u tr\u00ean 1 c\u1ed9t, t\u01b0\u01a1ng\u00a0\u1ee9ng v\u1edbi t\u1eebng d\u00f2ng d\u1eef li\u1ec7u ph\u00e1t sinh<\/em>.<\/p>\n<p>C\u00f3 th\u1ec3 th\u1ea5y trong c\u1ed9t Attribute ch\u00ednh l\u00e0 c\u1ed9t c\u00f3 ch\u1ee9a t\u00ean Th\u00e1ng, S\u1ed1 lao\u00a0\u0111\u1ed9ng, t\u00ean D\u1ef1\u00a0\u00e1n. C\u1ea7n ph\u1ea3i t\u00e1ch c\u00e1c gi\u00e1 tr\u1ecb\u00a0\u0111ang b\u1ecb tr\u1ed9n l\u1eabn v\u1edbi nhau ra t\u1eebng c\u1ed9t. Ti\u1ebfp theo ch\u00fang ta s\u1ebd d\u00f9ng ch\u1ee9c n\u0103ng <strong>Split Column<\/strong>\u00a0\u0111\u1ec3 l\u00e0m vi\u1ec7c n\u00e0y.<\/p>\n<p>T\u1eadn d\u1ee5ng d\u1ea5u ph\u1ea9y (comma)\u00a0\u0111\u00e3 s\u1eed d\u1ee5ng\u00a0\u1edf tr\u00ean l\u00e0m c\u0103n c\u1ee9 t\u00e1ch, ch\u00fang ta s\u1ebd ch\u1ecdn t\u00e1ch c\u1ed9t theo d\u1ea5u ph\u1ea9y (Split Column by Delimiter):<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-30498\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-5-7-split-column-theo-dau-phay.png\" width=\"934\" height=\"492\" title=\"1-5-7-split-column-theo-dau-phay\"><\/p>\n<p>Sau khi t\u00e1ch ra ta\u00a0\u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-30499\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-5-8-ket-qua-tach-du-lieu.png\" width=\"836\" height=\"216\" title=\"1-5-8-ket-qua-tach-du-lieu\"><\/p>\n<p>\u0110\u1ec3\u00a0\u0111\u01b0a n\u1ed9i dung S\u1ed1 lao\u00a0\u0111\u1ed9ng l\u00ean v\u1ecb tr\u00ed c\u1ed9t, ta c\u00f3:<\/p>\n<ul>\n<li>Ch\u1ecdn c\u1ed9t Attribute.2 v\u00e0 ch\u1ecdn Pivot Column, l\u1ea5y theo gi\u00e1 tr\u1ecb trong c\u1ed9t Value<img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-30500\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-5-9-pivot-column-cot-attribute2.png\" width=\"718\" height=\"349\" title=\"1-5-9-pivot-column-cot-attribute2\"><\/li>\n<li>Unpivot Column c\u00e1c c\u1ed9t D\u1ef1\u00a0\u00e1n: Ch\u1ecdn c\u00e1c c\u1ed9t t\u1eeb D\u1ef1\u00a0\u00e1n 1\u00a0\u0111\u1ebfn d\u1ef1\u00a0\u00e1n 5 r\u1ed3i b\u1ea5m Unpivot Columns (Unpivot Selected Columns)<\/li>\n<\/ul>\n<p>K\u1ebft qu\u1ea3 thu\u00a0\u0111\u01b0\u1ee3c l\u00e0:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-30501\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-5-10-ket-qua-xu-ly-du-lieu-1024x163-1.png\" width=\"1024\" height=\"163\" title=\"1-5-10-ket-qua-xu-ly-du-lieu-1024x163-1\"><\/p>\n<p>Vi\u1ec7c c\u00f2n l\u1ea1i l\u00e0 s\u1eafp x\u1ebfp l\u1ea1i c\u00e1c c\u1ed9t theo th\u1ee9 t\u1ef1,\u00a0\u0111\u1eb7t l\u1ea1i t\u00ean ti\u00eau\u00a0\u0111\u1ec1 cho c\u00e1c c\u1ed9t.<\/p>\n<p>Khi\u00a0\u0111\u00f3 ch\u00fang ta\u00a0\u0111\u00e3 ho\u00e0n th\u00e0nh to\u00e0n b\u1ed9 c\u00e1c b\u01b0\u1edbc x\u1eed l\u00fd l\u1ea1i c\u1ea5u tr\u00fac b\u1ea3ng d\u1eef li\u1ec7u trong Power Query r\u1ed3i. C\u00e1c thao t\u00e1c s\u1ebd\u00a0\u0111\u01b0\u1ee3c ghi l\u1ea1i t\u1ea1i m\u1ee5c <strong>Applied Steps<\/strong>\u00a0\u0111\u1ec3 c\u00f3 th\u1ec3 t\u1ef1\u00a0\u0111\u1ed9ng c\u1eadp nh\u1eadt m\u1ed7i khi b\u1ea3ng d\u1eef li\u1ec7u g\u1ed1c c\u00f3 thay\u00a0\u0111\u1ed5i n\u1ed9i dung.<\/p>\n<p><strong>B\u01b0\u1edbc 6: Xu\u1ea5t d\u1eef li\u1ec7u k\u1ebft qu\u1ea3 ra\u00a0Excel<\/strong><\/p>\n<p>Trong th\u1ebb Home c\u1ee7a c\u1eeda s\u1ed5 Power Query, b\u1ea1n ch\u1ecdn m\u1ee5c Close &amp; Load\u00a0\u0111\u1ec3 xu\u1ea5t k\u1ebft qu\u1ea3 ra\u00a0Excel.<\/p>\n<p>C\u00e1c b\u1ea1n c\u00f3 th\u1ec3\u00a0\u00e1p d\u1ee5ng t\u01b0\u01a1ng t\u1ef1 cho b\u1ea3ng Nam_2018 v\u00e0 Nam_2019\u00a0\u0111\u1ec3 ho\u00e0n t\u1ea5t c\u00f4ng vi\u1ec7c n\u00e0y.<\/p>\n<p>\u2013 \u2013 \u2013<\/p>\n<p>\u0110\u00f3n xem ph\u1ea7n 2: C\u00e1ch g\u1ed9p c\u00e1c b\u1ea3ng d\u1eef li\u1ec7u N\u0103m 2017, 2018, 2019 v\u00e0o chung 1 b\u1ea3ng d\u1eef li\u1ec7u<\/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\/2024\/12\/ex101_92019-nho-316.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":3980,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3979","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\/3979","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=3979"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/3979\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/3980"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=3979"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=3979"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=3979"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}