{"id":3908,"date":"2024-12-04T06:35:51","date_gmt":"2024-12-04T06:35:51","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-3\/"},"modified":"2024-12-04T06:35:51","modified_gmt":"2024-12-04T06:35:51","slug":"ung-dung-power-query-xay-dung-mo-hinh-du-lieu-phan-tich-nang-suat-lao-dong-phan-3","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-3\/","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 3"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-h4k0Fa0I#1\">\n<p><em>Qua 2 ph\u1ea7n tr\u01b0\u1edbc, ch\u00fang ta \u0111\u00e3 \u1ee9ng d\u1ee5ng <a href=\"http:\/\/blog.ezworkapp.com\/power-query-la-gi-tai-sao-can-hoc-power-query.html\"><strong>Power Query<\/strong><\/a> \u0111\u1ec3 c\u00f3 th\u1ec3 t\u00e1i c\u1ea5u tr\u00fac b\u1ea3ng d\u1eef li\u1ec7u, gi\u00fap x\u00e2y d\u1ef1ng b\u1ea3ng d\u1eef li\u1ec7u \u0111\u00fang chu\u1ea9n, \u0111\u1ed3ng th\u1eddi g\u1ed9p d\u1eef li\u1ec7u t\u1eeb 3 b\u1ea3ng v\u00e0o chung 1 b\u1ea3ng.\u00a0 Ph\u1ea7n n\u00e0y <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong><\/a> s\u1ebd c\u00f9ng c\u00e1c b\u1ea1n t\u00ecm hi\u1ec3u c\u00e1ch t\u1ea1o ra c\u00e1c gi\u00e1 tr\u1ecb c\u1ea7n s\u1eed d\u1ee5ng trong b\u00e1o c\u00e1o b\u1eb1ng <strong>c\u00e1ch s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c t\u00ednh trong Power Query<\/strong>. <\/em><\/p>\n<p>B\u1ea1n c\u00f3 th\u1ec3 xem l\u1ea1i c\u00e1c ph\u1ea7n t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/ung-dung-power-query-xay-dung-mo-hinh-du-lieu-phan-tich-nang-suat-lao-dong-phan-1.html\">Ph\u1ea7n 1:\u00a0T\u00e1i c\u1ea5u tr\u00fac b\u1ea3ng d\u1eef li\u1ec7u\u00a0\u0111\u00fang ti\u00eau chu\u1ea9n<\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/ung-dung-power-query-xay-dung-mo-hinh-du-lieu-phan-tich-nang-suat-lao-dong-phan-2.html\">Ph\u1ea7n 2:\u00a0G\u1ed9p c\u00e1c b\u1ea3ng d\u1eef li\u1ec7u v\u00e0o 1 b\u1ea3ng duy nh\u1ea5t<\/a><\/p>\n<p>File k\u1ebft qu\u1ea3 sau 2 ph\u1ea7n \u2013\u00a0 Link t\u1ea3i file:\u00a0<a href=\"http:\/\/bit.ly\/2Mo7Guc\">http:\/\/bit.ly\/2Mo7Guc<\/a><\/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-674ff84716236\" 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-3.html#Phan_3_cach_su_dung_cong_thuc_tinh_trong_Power_Query\" title=\"Ph\u1ea7n 3: c\u00e1ch s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c t\u00ednh trong Power Query\">Ph\u1ea7n 3: c\u00e1ch s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c t\u00ednh trong Power Query<\/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-2\" href=\"https:\/\/blog.hocexcel.online\/ung-dung-power-query-xay-dung-mo-hinh-du-lieu-phan-tich-nang-suat-lao-dong-phan-3.html#Tinh_nang_suat_lao_dong\" title=\"T\u00ednh n\u0103ng su\u1ea5t lao\u00a0\u0111\u1ed9ng\">T\u00ednh n\u0103ng su\u1ea5t lao\u00a0\u0111\u1ed9ng<\/a><\/li>\n<li class=\"ez-toc-page-1 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-3.html#Phan_nhom_theo_so_lao_dong\" title=\"Ph\u00e2n nh\u00f3m theo s\u1ed1 lao\u00a0\u0111\u1ed9ng\">Ph\u00e2n nh\u00f3m theo s\u1ed1 lao\u00a0\u0111\u1ed9ng<\/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-3.html#Tao_cot_theo_dieu_kien_trong_Power_Query\" title=\"T\u1ea1o c\u1ed9t theo\u00a0\u0111i\u1ec1u ki\u1ec7n trong Power Query\">T\u1ea1o c\u1ed9t theo\u00a0\u0111i\u1ec1u ki\u1ec7n trong Power Query<\/a><\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/ung-dung-power-query-xay-dung-mo-hinh-du-lieu-phan-tich-nang-suat-lao-dong-phan-3.html#Ket_luan\" title=\"K\u1ebft lu\u1eadn\">K\u1ebft lu\u1eadn<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Phan_3_cach_su_dung_cong_thuc_tinh_trong_Power_Query\"><\/span>Ph\u1ea7n 3: c\u00e1ch s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c t\u00ednh trong Power Query<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"Tinh_nang_suat_lao_dong\"><\/span>T\u00ednh n\u0103ng su\u1ea5t lao\u00a0\u0111\u1ed9ng<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Trong b\u1ea3ng d\u1eef li\u1ec7u k\u1ebft qu\u1ea3 c\u1ee7a Power Query (b\u1ea3ng c\u00f3 t\u00ean Query1, trong Sheet1) ch\u00fang ta c\u00f3 Gi\u00e1 tr\u1ecb c\u1ee7a t\u1eebng d\u1ef1\u00a0\u00e1n, t\u01b0\u01a1ng\u00a0\u1ee9ng v\u1edbi S\u1ed1 lao\u00a0\u0111\u1ed9ng. Do\u00a0\u0111\u00f3 N\u0103ng su\u1ea5t lao\u00a0\u0111\u1ed9ng t\u00ednh b\u1eb1ng Gi\u00e1 tr\u1ecb chia cho S\u1ed1 lao\u00a0\u0111\u1ed9ng. C\u00e1c b\u01b0\u1edbc\u00a0\u0111\u1ec3 c\u00f3 th\u1ec3 t\u1ea1o th\u00eam c\u1ed9t c\u00f3 ch\u1ee9a c\u00f4ng th\u1ee9c t\u00ednh trong Power Query nh\u01b0 sau:<\/p>\n<p><strong>B\u01b0\u1edbc 1: Ch\u1ecdn Edit Query\u00a0\u0111\u1ec3 tr\u1edf v\u1ec1 c\u1eeda s\u1ed5 l\u00e0m vi\u1ec7c c\u1ee7a Power Query<\/strong><\/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-310.gif\" title=\"ex101_92019-nho-310\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>B\u1ea5m ch\u1ecdn b\u1ea3ng Query1, tr\u00ean thanh c\u00f4ng c\u1ee5 s\u1ebd xu\u1ea5t hi\u1ec7n th\u1ebb Query, b\u1ea1n b\u1ea5m ch\u1ecdn Edit<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30522 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/3-1-chon-edit-query.png\" alt=\"cach-su-dung-cong-thuc-tinh-trong-power-query-1\" width=\"939\" height=\"203\" title=\"3-1-chon-edit-query\"><\/p>\n<p>(ho\u1eb7c b\u1ea5m chu\u1ed9t ph\u1ea3i v\u00e0o b\u1ea3ng Query1, ch\u1ecdn Table &gt; Edit)<\/p>\n<p><strong>B\u01b0\u1edbc 2: T\u1ea1o th\u00eam c\u1ed9t trong Power Query<\/strong><\/p>\n<p>Ch\u1ecdn th\u1ebb Add Column &gt; ch\u1ecdn Custom Column. Khi\u00a0\u0111\u00f3 s\u1ebd xu\u1ea5t hi\u1ec7n c\u1eeda s\u1ed5 Custom Column nh\u01b0 h\u00ecnh sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30523 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/3-2-add-column-trong-power-query.png\" alt=\"cach-su-dung-cong-thuc-tinh-trong-power-query-2\" width=\"708\" height=\"583\" title=\"3-2-add-column-trong-power-query\"><\/p>\n<ul>\n<li>Trong m\u1ee5c New column name: nh\u1eadp t\u00ean cho c\u1ed9t m\u1edbi t\u1ea1o n\u00e0y<\/li>\n<li>Trong m\u1ee5c Custome column formula: vi\u1ebft c\u00f4ng th\u1ee9c t\u00ednh\u00a0\u0111\u1ec3 t\u1ea1o ra k\u1ebft qu\u1ea3 cho c\u1ed9t. B\u1ea1n c\u00f3 th\u1ec3 ch\u1ecdn c\u00e1c c\u1ed9t c\u00f3 li\u00ean quan t\u1edbi vi\u1ec7c t\u00ednh to\u00e1n trong m\u1ee5c Avaiable columns (nh\u1ea5n\u00a0\u0111\u00fap\u00a0chu\u1ed9t v\u00e0o t\u00ean c\u1ed9t\u00a0\u0111\u1ec3 l\u1ea5y t\u00ean c\u1ed9t\u00a0\u0111\u00f3 v\u00e0o c\u00f4ng th\u1ee9c)<\/li>\n<\/ul>\n<p>C\u00f4ng th\u1ee9c c\u1ea7n s\u1eed d\u1ee5ng l\u00e0:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30524 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/3-3-cong-thuc-tinh-trong-custom-column.png\" alt=\"cach-su-dung-cong-thuc-tinh-trong-power-query-3\" width=\"875\" height=\"548\" title=\"3-3-cong-thuc-tinh-trong-custom-column\"><\/p>\n<p>Khi nh\u1ea5n OK, ch\u00fang ta s\u1ebd c\u00f3 c\u1ed9t m\u1edbi. C\u1ed9t n\u00e0y ch\u00ednh l\u00e0 N\u0103ng su\u1ea5t lao\u00a0\u0111\u1ed9ng.<\/p>\n<p><strong>B\u01b0\u1edbc 3: Nh\u1ea5n Close &amp; Load\u00a0\u0111\u1ec3 c\u1eadp nh\u1eadt b\u1ea3ng d\u1eef li\u1ec7u<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30525 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/3-4-ket-qua-sau-khi-tao-cot-nang-suat-lao-dong.png\" alt=\"cach-su-dung-cong-thuc-tinh-trong-power-query-4\" width=\"737\" height=\"158\" title=\"3-4-ket-qua-sau-khi-tao-cot-nang-suat-lao-dong\"><\/p>\n<p>(c\u1ed9t N\u0103m\u00a0\u0111\u00e3\u00a0\u0111\u01b0\u1ee3c\u00a0\u0111\u01b0a l\u1ea1i g\u1ea7n c\u1ed9t Th\u00e1ng\u00a0\u0111\u1ec3 d\u1ec5 nh\u00ecn h\u01a1n)<\/p>\n<p>Khi\u00a0\u0111\u00f3 ch\u00fang ta c\u00f3 th\u1ec3 th\u1ea5y n\u1ed9i dung\u00a0trong c\u1ed9t G (n\u0103ng su\u1ea5t L\u0110) l\u00e0 d\u1ea1ng gi\u00e1 tr\u1ecb, kh\u00f4ng ph\u1ea3i d\u1ea1ng c\u00f4ng th\u1ee9c t\u00ednh. Vi\u1ec7c t\u00ednh to\u00e1n\u00a0\u0111\u01b0\u1ee3c th\u1ef1c hi\u1ec7n qua Power Query v\u00e0 tr\u1ea3 k\u1ebft qu\u1ea3 l\u1ea1i v\u00e0o b\u1ea3ng Query1 m\u1ed9t c\u00e1ch t\u1ef1\u00a0\u0111\u1ed9ng.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Phan_nhom_theo_so_lao_dong\"><\/span>Ph\u00e2n nh\u00f3m theo s\u1ed1 lao\u00a0\u0111\u1ed9ng<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>V\u00ec s\u1ed1 l\u01b0\u1ee3ng c\u00e1c chi nh\u00e1nh l\u00e0 r\u1ea5t nhi\u1ec1u n\u00ean kh\u00f4ng th\u1ec3 tr\u00ecnh b\u00e0y h\u1ebft t\u1ea5t c\u1ea3 c\u00e1c chi nh\u00e1nh tr\u00ean b\u00e1o c\u00e1o. Do\u00a0\u0111\u00f3 ch\u00fang ta c\u00f3 th\u1ec3 ph\u00e2n nh\u00f3m c\u00e1c chi nh\u00e1nh\u00a0\u0111\u1ec3 c\u00f3 th\u1ec3 b\u00e1o c\u00e1o ph\u00e2n t\u00edch ri\u00eang trong t\u1eebng nh\u00f3m. Nh\u01b0 v\u1eady s\u1ebd gi\u00fap vi\u1ec7c ph\u00e2n t\u00edch\u00a0\u0111\u01b0\u1ee3c chi ti\u1ebft h\u01a1n.<\/p>\n<p>\u0110\u1ec3 l\u00e0m\u00a0\u0111i\u1ec1u n\u00e0y, ch\u00fang ta c\u1ea7n c\u00f3 1 c\u1ed9t th\u1ec3 hi\u1ec7n Nh\u00f3m chi nh\u00e1nh.<\/p>\n<p>Gi\u1ea3 s\u1eed ch\u00fang ta c\u00f3 ti\u00eau ch\u00ed ph\u00e2n nh\u00f3m chi nh\u00e1nh theo s\u1ed1\u00a0lao\u00a0\u0111\u1ed9ng nh\u01b0 sau (d\u1eef li\u1ec7u gi\u1ea3\u00a0\u0111\u1ecbnh, b\u1ea1n ho\u00e0n to\u00e0n c\u00f3 th\u1ec3 t\u1ef1 thay\u00a0\u0111\u1ed5i c\u00e1c ti\u00eau ch\u00ed n\u00e0y):<\/p>\n<ul>\n<li>Khu v\u1ef1c 1: c\u00f3 s\u1ed1 l\u01b0\u1ee3ng lao\u00a0\u0111\u1ed9ng &lt;=\u00a0180<\/li>\n<li>Khu v\u1ef1c 2: c\u00f3 s\u1ed1 l\u01b0\u1ee3ng lao\u00a0\u0111\u1ed9ng &lt;=\u00a0400<\/li>\n<li>Khu v\u1ef1c 3: c\u00f3 s\u1ed1 l\u01b0\u1ee3ng lao\u00a0\u0111\u1ed9ng &gt;\u00a0400<\/li>\n<\/ul>\n<p>V\u1edbi tr\u01b0\u1eddng h\u1ee3p n\u00e0y, ch\u00fang ta c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n 1 c\u00e1ch\u00a0\u0111\u01a1n gi\u1ea3n trong\u00a0Excel: s\u1eed d\u1ee5ng h\u00e0m\u00a0IF trong b\u1ea3ng Query1. C\u00e1ch l\u00e0m nh\u01b0 sau:<\/p>\n<ul>\n<li>B\u01b0\u1edbc 1: t\u1ea1i c\u1ed9t H, nh\u1eadp t\u00ean ti\u00eau\u00a0\u0111\u1ec1 c\u1ed9t v\u00e0o\u00a0\u00f4 H1 l\u00e0 <strong>Khu v\u1ef1c<\/strong>. Khi\u00a0\u0111\u00f3 c\u1ed9t H s\u1ebd\u00a0\u0111\u01b0\u1ee3c t\u00ednh chung v\u00e0o b\u1ea3ng Query1<\/li>\n<li>B\u01b0\u1edbc 2: vi\u1ebft c\u00f4ng th\u1ee9c t\u1ea1i\u00a0\u00f4 H2 nh\u01b0 sau:<\/li>\n<\/ul>\n<blockquote>\n<p>=IF([@[S\u1ed1 lao \u0111\u1ed9ng]]&lt;=180, \u201cKhu v\u1ef1c 1\u201d, IF([@[S\u1ed1 lao \u0111\u1ed9ng]]&lt;=400, \u201cKhu v\u1ef1c 2\u201d, \u201cKhu v\u1ef1c 3\u201d))<\/p>\n<\/blockquote>\n<p>Khi k\u1ebft th\u00fac c\u00f4ng th\u1ee9c v\u1edbi ph\u00edm Enter, to\u00e0n b\u1ed9 c\u1ed9t H s\u1ebd t\u1ef1\u00a0\u0111\u1ed9ng\u00a0\u0111\u01b0\u1ee3c t\u00ednh theo c\u00f4ng th\u1ee9c n\u00e0y. K\u1ebft qu\u1ea3 thu\u00a0\u0111\u01b0\u1ee3c l\u00e0:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30526 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/3-5-ket-qua-cot-phan-nhom-theo-so-lao-dong-1024x262-1.png\" alt=\"cach-su-dung-cong-thuc-tinh-trong-power-query-5\" width=\"1024\" height=\"262\" title=\"3-5-ket-qua-cot-phan-nhom-theo-so-lao-dong-1024x262-1\"><\/p>\n<p><strong>*\u00a0\u01afu\u00a0\u0111i\u1ec3m:<\/strong><\/p>\n<p>Trong\u00a0Excel, b\u1ea1n\u00a0\u0111\u00e3 quen v\u1edbi vi\u1ec7c vi\u1ebft c\u00f4ng th\u1ee9c, do\u00a0\u0111\u00f3 c\u00e1ch n\u00e0y r\u1ea5t d\u1ec5 th\u1ef1c hi\u1ec7n.<\/p>\n<p>Ch\u1ee9c n\u0103ng c\u1ee7a Table s\u1ebd gi\u00fap c\u1ed9t H t\u1ea1o tr\u1ef1c ti\u1ebfp tr\u00ean\u00a0Excel c\u0169ng\u00a0\u0111\u01b0\u1ee3c t\u00ednh t\u1ef1\u00a0\u0111\u1ed9ng ngay khi b\u1ea3ng Query1 c\u1eadp nh\u1eadt k\u1ebft qu\u1ea3 (d\u00f9 c\u1ed9t H l\u00fac n\u00e0y kh\u00f4ng c\u00f3 s\u1eb5n trong Query1)<\/p>\n<p><strong>*\u00a0Nh\u01b0\u1ee3c\u00a0\u0111i\u1ec3m:<\/strong><\/p>\n<p>Vi\u1ec7c n\u00e0y s\u1ebd khi\u1ebfn b\u1ea3ng Query1 c\u00f3 ch\u1ee9a c\u00f4ng th\u1ee9c\u00a0\u0111\u1ed9ng. C\u00f4ng th\u1ee9c n\u00e0y c\u00f3 th\u1ec3 l\u00e0m file n\u1eb7ng v\u00e0 ho\u1ea1t\u00a0\u0111\u1ed9ng ch\u1eadm h\u01a1n.<\/p>\n<p><strong>* C\u00e1ch kh\u1eafc ph\u1ee5c: S\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng t\u1ea1o c\u1ed9t tr\u1ef1c ti\u1ebfp trong Power Query<\/strong><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Tao_cot_theo_dieu_kien_trong_Power_Query\"><\/span><strong>T\u1ea1o c\u1ed9t theo\u00a0\u0111i\u1ec1u ki\u1ec7n trong Power Query<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><strong>B\u01b0\u1edbc 1<\/strong>: \u0110\u01b0a b\u1ea3ng t\u00ednh tr\u1edf l\u1ea1i c\u1eeda s\u1ed5 l\u00e0m vi\u1ec7c Power Query, t\u1ea1i th\u1ebb Add Column, ch\u00fang ta b\u1ea5m ch\u1ecdn m\u1ee5c <strong>Conditional Column<\/strong><\/p>\n<p><strong>B\u01b0\u1edbc 2<\/strong>: Trong c\u1eeda s\u1ed5 Add Conditional Column ch\u00fang ta thi\u1ebft l\u1eadp nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-30527 size-large\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/3-6-them-cot-theo-dieu-kien-1024x467-1.png\" alt=\"cach-su-dung-cong-thuc-tinh-trong-power-query-6\" width=\"1024\" height=\"467\" title=\"3-6-them-cot-theo-dieu-kien-1024x467-1\"><\/p>\n<p>C\u00e1ch thi\u1ebft l\u1eadp n\u00e0y gi\u1ed1ng v\u1edbi c\u00e2u l\u1ec7nh\u00a0IF m\u00e0 ch\u00fang ta\u00a0\u0111\u00e3 vi\u1ebft\u00a0\u1edf tr\u00ean. Tuy nhi\u00ean kh\u00f4ng vi\u1ebft tr\u1ef1c ti\u1ebfp trong Power Query m\u00e0 ph\u1ea3i chia ra t\u1eebng ph\u1ea7n,\u00a0\u0111\u1eb7t t\u1ea1i t\u1eebng v\u1ecb tr\u00ed\u00a0\u0111\u00e3 quy\u00a0\u0111\u1ecbnh.<\/p>\n<p><span style=\"text-decoration: underline\"><em>M\u1edf r\u1ed9ng<\/em><\/span>: c\u1ea5u tr\u00fac tr\u00ean g\u1ea7n gi\u1ed1ng v\u1edbi c\u1ea5u tr\u00fac vi\u1ebft h\u00e0m\u00a0IF trong VBA, b\u1ea1n c\u00f3 th\u1ec3 tham kh\u1ea3o th\u00eam t\u1ea1i\u00a0\u0111\u00e2y:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-viet-cau-truc-if-then-else-trong-vba-excel.html\">H\u01b0\u1edbng d\u1eabn c\u00e1ch vi\u1ebft c\u1ea5u tr\u00fac IF THEN ELSE trong VBA Excel<\/a><\/p>\n<p>Khi\u00a0\u0111\u00f3 k\u1ebft qu\u1ea3 thu\u00a0\u0111\u01b0\u1ee3c s\u1ebd l\u00e0 c\u1ed9t Khu v\u1ef1c\u00a0\u0111\u01b0\u1ee3c t\u1ea1o ngay trong Power Query. Khi b\u1ea5m Close &amp; Load th\u00ec to\u00e0n b\u1ed9 k\u1ebft qu\u1ea3 c\u1ee7a c\u1ed9t Khu v\u1ef1c s\u1ebd l\u00e0 d\u1ea1ng gi\u00e1 tr\u1ecb, kh\u00f4ng ph\u1ea3i c\u00f4ng th\u1ee9c t\u00ednh.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ket_luan\"><\/span>K\u1ebft lu\u1eadn<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Nh\u01b0 v\u1eady qua 2 v\u00ed d\u1ee5 tr\u00ean, ch\u00fang ta c\u00f3 th\u1ec3 bi\u1ebft c\u00e1ch t\u1ea1o ra c\u00e1c gi\u00e1 tr\u1ecb kh\u00f4ng c\u00f3 s\u1eb5n trong b\u1ea3ng d\u1eef li\u1ec7u g\u1ed1c b\u1eb1ng Power Query. Vi\u1ec7c n\u00e0y s\u1ebd gi\u00fap ch\u00fang ta c\u00f3 th\u00eam c\u00e1c h\u01b0\u1edbng\u00a0\u0111\u1ec3 ph\u00e2n t\u00edch d\u1eef li\u1ec7u t\u1ed1t h\u01a1n: th\u00eam ch\u1ec9 ti\u00eau ph\u00e2n t\u00edch, ph\u00e2n nh\u00f3m chia nh\u1ecf ph\u1ea1m vi\u00a0\u0111\u1ec3 ph\u00e2n t\u00edch s\u00e2u h\u01a1n.<\/p>\n<p>C\u00e1c b\u1ea1n c\u00f3 th\u1ec3 t\u1ea3i file k\u1ebft qu\u1ea3 t\u1ea1i\u00a0\u0111\u1ecba ch\u1ec9:\u00a0<a href=\"http:\/\/bit.ly\/33hR3XW\">http:\/\/bit.ly\/33hR3XW<\/a><\/p>\n<p>Trong b\u00e0i ti\u1ebfp theo, ch\u00fang ta s\u1ebd b\u1eaft \u0111\u1ea7u \u0111i v\u00e0o vi\u1ec7c x\u00e2y d\u1ef1ng c\u00e1c b\u00e1o c\u00e1o ph\u00e2n t\u00edch d\u1eef li\u1ec7u v\u1ec1 n\u0103ng su\u1ea5t lao \u0111\u1ed9ng. C\u1ea3m \u01a1n c\u00e1c b\u1ea1n \u0111\u00e3 theo d\u00f5i b\u00e0i vi\u1ebft.<strong> H\u1ecdc Excel Online<\/strong> xin ch\u00e0o c\u00e1c b\u1ea1n.<\/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-310.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":3909,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3908","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\/3908","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=3908"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/3908\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/3909"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=3908"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=3908"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=3908"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}