{"id":1902,"date":"2024-12-02T06:28:35","date_gmt":"2024-12-02T06:28:35","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/power-query-tra-cuu-gia-tri-bang-hop-nhat\/"},"modified":"2024-12-02T06:28:35","modified_gmt":"2024-12-02T06:28:35","slug":"power-query-tra-cuu-gia-tri-bang-hop-nhat","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/power-query-tra-cuu-gia-tri-bang-hop-nhat\/","title":{"rendered":"POWER QUERY \u2013 TRA C\u1ee8U GI\u00c1 TR\u1eca B\u1eb0NG H\u1ee2P NH\u1ea4T"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><em><span style=\"font-weight: 400\">\u00a0N\u1ebfu b\u1ea1n s\u1eed 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 tra c\u1ee9u d\u1eef li\u1ec7u th\u00ec sao? Kh\u00f4ng c\u00f3 h\u00e0m VLOOKUP, v\u1eady l\u00e0m c\u00e1ch n\u00e0o ch\u00fang ta c\u00f3 th\u1ec3 <strong>tra c\u1ee9u d\u1eef li\u1ec7u b\u1eb1ng power query<\/strong>? <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong><\/a> s\u1ebd gi\u1ea3i \u0111\u00e1p c\u00e2u h\u1ecfi n\u00e0y b\u1eb1ng b\u00e0i vi\u1ebft c\u1ee5 th\u1ec3 ngay sau \u0111\u00e2y.<\/span><\/em><\/p>\n<p><span style=\"font-weight: 400\">V\u1ec1 hi\u1ec7u qu\u1ea3, c\u00f3 ba lo\u1ea1i tra c\u1ee9u: tra c\u1ee9u ch\u00ednh x\u00e1c, tra c\u1ee9u g\u1ea7n \u0111\u00fang v\u00e0 tra c\u1ee9u m\u1edd.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Kh\u1edbp ch\u00ednh x\u00e1c l\u00e0 ph\u1ed5 bi\u1ebfn nh\u1ea5t v\u00e0 y\u00eau c\u1ea7u gi\u00e1 tr\u1ecb tra c\u1ee9u ph\u1ea3i gi\u1ed1ng nhau.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Kh\u1edbp g\u1ea7n \u0111\u00fang t\u00ecm gi\u00e1 tr\u1ecb tr\u00ean (ho\u1eb7c d\u01b0\u1edbi) gi\u00e1 tr\u1ecb tra c\u1ee9u.\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Kh\u1edbp m\u1edd t\u00ecm gi\u00e1 tr\u1ecb d\u1ef1a tr\u00ean m\u1ee9c \u0111\u1ed9 t\u01b0\u01a1ng t\u1ef1 c\u1ee7a ch\u00fang v\u1edbi c\u00e1c gi\u00e1 tr\u1ecb kh\u00e1c b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng m\u1ed9t s\u1ed1 d\u1ea1ng thu\u1eadt to\u00e1n.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">Power Query c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n t\u1ea5t c\u1ea3 c\u00e1c lo\u1ea1i n\u00e0y, m\u1eb7c d\u00f9 trong b\u00e0i \u0111\u0103ng n\u00e0y, ch\u00fang ta s\u1ebd t\u1eadp trung ch\u1ee7 y\u1ebfu v\u00e0o hai lo\u1ea1i \u0111\u1ea7u ti\u00ean. \u0110\u1ec3 th\u1ef1c hi\u1ec7n tra c\u1ee9u d\u1eef li\u1ec7u trong Power Query, ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng ph\u00e9p chuy\u1ec3n \u0111\u1ed5i h\u1ee3p nh\u1ea5t n\u1eb1m tr\u00ean thanh c\u00f4ng c\u1ee5 Home.<\/span><\/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-674d5391db559\" 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\/power-query-tra-cuu-gia-tri-bang-hop-nhat.html#Downloads\" title=\"Downloads\">Downloads<\/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\/power-query-tra-cuu-gia-tri-bang-hop-nhat.html#Tong_doanh_so_theo_Town_tra_cuu_voi_ket_qua_khop_chinh_xac\" title=\"T\u1ed5ng doanh s\u1ed1 theo Town (tra c\u1ee9u v\u1edbi k\u1ebft qu\u1ea3 kh\u1edbp ch\u00ednh x\u00e1c)\">T\u1ed5ng doanh s\u1ed1 theo Town (tra c\u1ee9u v\u1edbi k\u1ebft qu\u1ea3 kh\u1edbp ch\u00ednh x\u00e1c)<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/power-query-tra-cuu-gia-tri-bang-hop-nhat.html#Tong_so_tien_hoan_lai_cua_khach_hang_tra_cuu_voi_ket_qua_khop_gan_dung\" title=\"T\u1ed5ng s\u1ed1 ti\u1ec1n ho\u00e0n l\u1ea1i c\u1ee7a kh\u00e1ch h\u00e0ng (tra c\u1ee9u v\u1edbi k\u1ebft qu\u1ea3 kh\u1edbp g\u1ea7n \u0111\u00fang)\">T\u1ed5ng s\u1ed1 ti\u1ec1n ho\u00e0n l\u1ea1i c\u1ee7a kh\u00e1ch h\u00e0ng (tra c\u1ee9u v\u1edbi k\u1ebft qu\u1ea3 kh\u1edbp g\u1ea7n \u0111\u00fang)<\/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-4\" href=\"https:\/\/blog.hocexcel.online\/power-query-tra-cuu-gia-tri-bang-hop-nhat.html#Tra_cuu_voi_khop_chinh_xac\" title=\"Tra c\u1ee9u v\u1edbi kh\u1edbp ch\u00ednh x\u00e1c\">Tra c\u1ee9u v\u1edbi kh\u1edbp ch\u00ednh x\u00e1c<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/power-query-tra-cuu-gia-tri-bang-hop-nhat.html#Cac_loai_lien_ket\" title=\"C\u00e1c lo\u1ea1i li\u00ean k\u1ebft\">C\u00e1c lo\u1ea1i li\u00ean k\u1ebft<\/a>\n<ul class=\"ez-toc-list-level-4\">\n<li class=\"ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/power-query-tra-cuu-gia-tri-bang-hop-nhat.html#Outer_Joins\" title=\"Outer Joins\">Outer Joins<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blog.hocexcel.online\/power-query-tra-cuu-gia-tri-bang-hop-nhat.html#Inner_Join\" title=\"Inner Join\">Inner Join<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/blog.hocexcel.online\/power-query-tra-cuu-gia-tri-bang-hop-nhat.html#Anti_Joins\" title=\"Anti Joins\">Anti Joins<\/a><\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/blog.hocexcel.online\/power-query-tra-cuu-gia-tri-bang-hop-nhat.html#Tra_cuu_voi_ket_qua_khop_gan_dung\" title=\"Tra c\u1ee9u v\u1edbi k\u1ebft qu\u1ea3 kh\u1edbp g\u1ea7n \u0111\u00fang\">Tra c\u1ee9u v\u1edbi k\u1ebft qu\u1ea3 kh\u1edbp g\u1ea7n \u0111\u00fang<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/blog.hocexcel.online\/power-query-tra-cuu-gia-tri-bang-hop-nhat.html#Tra_cuu_nhieu_tieu_chi\" title=\"Tra c\u1ee9u nhi\u1ec1u ti\u00eau ch\u00ed\">Tra c\u1ee9u nhi\u1ec1u ti\u00eau ch\u00ed<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/blog.hocexcel.online\/power-query-tra-cuu-gia-tri-bang-hop-nhat.html#Tra_cuu_fuzzy_match\" title=\"Tra c\u1ee9u fuzzy match\">Tra c\u1ee9u fuzzy match<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Downloads\"><\/span><b>Downloads<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 l\u00e0m vi\u1ec7c c\u00f9ng v\u1edbi c\u00e1c v\u00ed d\u1ee5 b\u00ean d\u01b0\u1edbi, h\u00e3y t\u1ea3i xu\u1ed1ng c\u00e1c t\u1ec7p m\u1eabu. Nh\u1ea5p v\u00e0o Click here \u0111\u1ec3 \u0111\u0103ng k\u00fd v\u00e0 c\u00f3 quy\u1ec1n truy c\u1eadp v\u00e0o ph\u1ea7n Downloads.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u00e1c v\u00ed d\u1ee5 trong b\u00e0i \u0111\u0103ng n\u00e0y s\u1eed d\u1ee5ng t\u1ec7p c\u00f3 t\u00ean <\/span><b>Example 14 \u2013 <a href=\"https:\/\/drive.google.com\/file\/d\/1LkCuZ2XkFnOSKgtqYR4y3VllpSn1XSlf\/view\">Lookup Data.xlsx<\/a><\/b><\/p>\n<p><span style=\"font-weight: 400\">Trong t\u1ec7p c\u00f3 ba b\u1ea3ng:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Sales \u2013 Ch\u1ee9a d\u1eef li\u1ec7u b\u00e1n h\u00e0ng h\u00e0ng th\u00e1ng<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Customers \u2013 Ch\u1ee9a th\u00f4ng tin li\u00ean h\u1ec7 kh\u00e1ch h\u00e0ng<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Rebates \u2013 Gi\u1ea3m gi\u00e1 cho kh\u00e1ch h\u00e0ng khi mua h\u00e0ng tr\u00ean c\u00e1c gi\u00e1 tr\u1ecb c\u1ee5 th\u1ec3<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 minh h\u1ecda c\u00e1ch ch\u00fang ta c\u00f3 th\u1ec3 tra c\u1ee9u d\u1eef li\u1ec7u trong Power Query, ch\u00fang ta s\u1ebd t\u1ea1o hai b\u00e1o c\u00e1o.<\/span><\/p>\n<div class=\"code-block code-block-2\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-57.gif\" title=\"vba101_92019-nho-57\"><br \/>\n<\/a>\n<\/div>\n<\/div>\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-139.gif\" title=\"ex101_92019-nho-139\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Tong_doanh_so_theo_Town_tra_cuu_voi_ket_qua_khop_chinh_xac\"><\/span><b>T\u1ed5ng doanh s\u1ed1 theo Town (tra c\u1ee9u v\u1edbi k\u1ebft qu\u1ea3 kh\u1edbp ch\u00ednh x\u00e1c)<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Gi\u00e1 tr\u1ecb Sales v\u00e0 Town c\u1ee7a m\u1ed7i kh\u00e1ch h\u00e0ng n\u1eb1m trong c\u00e1c b\u1ea3ng ri\u00eang bi\u1ec7t; do \u0111\u00f3, ch\u00fang ta c\u1ea7n s\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng Merge \u0111\u1ec3 th\u1ef1c hi\u1ec7n tra c\u1ee9u.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Tong_so_tien_hoan_lai_cua_khach_hang_tra_cuu_voi_ket_qua_khop_gan_dung\"><\/span><b>T\u1ed5ng s\u1ed1 ti\u1ec1n ho\u00e0n l\u1ea1i c\u1ee7a kh\u00e1ch h\u00e0ng (tra c\u1ee9u v\u1edbi k\u1ebft qu\u1ea3 kh\u1edbp g\u1ea7n \u0111\u00fang)<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 nh\u1eadn \u0111\u01b0\u1ee3c kho\u1ea3n gi\u1ea3m gi\u00e1 cho m\u1ed7i Kh\u00e1ch h\u00e0ng, ch\u00fang ta c\u1ea7n th\u1ef1c hi\u1ec7n kh\u1edbp g\u1ea7n \u0111\u00fang. Ch\u00fang ta kh\u00f4ng nh\u1ea5t thi\u1ebft mu\u1ed1n kh\u1edbp v\u1edbi gi\u00e1 tr\u1ecb ch\u00ednh x\u00e1c, nh\u01b0ng \u0111\u1ec3 t\u00ecm d\u1ea3i m\u00e0 gi\u00e1 tr\u1ecb b\u00e1n h\u00e0ng gi\u1ea3m xu\u1ed1ng. Ch\u00fang ta v\u1eabn s\u1ebd s\u1eed d\u1ee5ng h\u00e0m Merge, nh\u01b0ng ch\u00fang ta s\u1ebd c\u1ea7n th\u00eam m\u1ed9t v\u00e0i ph\u00e9p chuy\u1ec3n \u0111\u1ed5i \u0111\u1ec3 c\u00f3 \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 cu\u1ed1i c\u00f9ng ch\u00ednh x\u00e1c.<\/span><\/p>\n<p><span style=\"font-weight: 400\">T\u1ea3i d\u1eef li\u1ec7u v\u00e0o Power Query<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u1eaft \u0111\u1ea7u b\u1eb1ng c\u00e1ch t\u1ea3i ba b\u1ea3ng v\u00e0o Power Query<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u1ea5m v\u00e0o \u00f4 b\u1ea5t k\u1ef3 trong b\u1ea3ng Sales, sau \u0111\u00f3 ch\u1ecdn <\/span><span style=\"font-weight: 400\">\u00a0<\/span><b>Data -&gt; From Table \/ Range<\/b><span style=\"font-weight: 400\"> t\u1eeb menu Excel.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Power Query Editor s\u1ebd t\u1ea3i. T\u00f4i \u0111\u00e3 c\u1ed1 g\u1eafng set-up c\u00e1c b\u1ea3ng \u0111\u1ec3 ch\u00fang y\u00eau c\u1ea7u c\u00e1c ph\u00e9p chuy\u1ec3n \u0111\u1ed5i t\u1ed1i thi\u1ec3u, nh\u01b0ng trong th\u1ef1c t\u1ebf th\u00ec \u0111i\u1ec1u \u0111\u00f3 hi\u1ebfm khi \u0111\u01a1n gi\u1ea3n nh\u01b0 v\u1eady.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ed d\u1ee5 c\u1ee7a ch\u00fang ta, ch\u00fang ta kh\u00f4ng c\u1ea7n t\u1ea3i d\u1eef li\u1ec7u v\u00e0o trang t\u00ednh, thay v\u00e0o \u0111\u00f3 ch\u00fang ta c\u00f3 th\u1ec3 t\u1ea1o k\u1ebft n\u1ed1i. T\u1eeb ribbon Power Query, nh\u1ea5p v\u00e0o <\/span><b>Home -&gt; Close &amp; Load To\u2026<\/b><\/p>\n<p><span style=\"font-weight: 400\">Ch\u1ecdn <\/span><b>Only Create Connection<\/b><span style=\"font-weight: 400\"> t\u1eeb c\u1eeda s\u1ed5 <\/span><b>Import Data<\/b><span style=\"font-weight: 400\">, sau \u0111\u00f3 click <\/span><b>OK<\/b><span style=\"font-weight: 400\">.<\/span><\/p>\n<p><span style=\"font-weight: 400\">L\u1eb7p l\u1ea1i h\u00e0nh \u0111\u1ed9ng n\u00e0y v\u1edbi b\u1ea3ng Customer v\u00e0 Rebates.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Trong Excel, m\u1edf ng\u0103n <\/span><span style=\"font-weight: 400\">Queries &amp; Connections <\/span><span style=\"font-weight: 400\">(B\u1ea5m <\/span><b>Data -&gt; Queries &amp; Connections<\/b><span style=\"font-weight: 400\"> n\u1ebfu n\u00f3 kh\u00f4ng hi\u1ec3n th\u1ecb), ba truy v\u1ea5n s\u1ebd \u0111\u01b0\u1ee3c li\u1ec7t k\u00ea.<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd ch\u00fang ta \u0111\u00e3 s\u1eb5n s\u00e0ng \u0111\u1ec3 b\u1eaft \u0111\u1ea7u s\u1eed d\u1ee5ng t\u00ednh n\u0103ng Merge \ud83d\ude42<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Tra_cuu_voi_khop_chinh_xac\"><\/span><b>Tra c\u1ee9u v\u1edbi kh\u1edbp ch\u00ednh x\u00e1c<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 minh h\u1ecda m\u1ed9t k\u1ebft qu\u1ea3 kh\u1edbp ch\u00ednh x\u00e1c, ch\u00fang ta s\u1ebd t\u1ea1o m\u1ed9t b\u00e1o c\u00e1o t\u1ed5ng doanh s\u1ed1 b\u00e1n h\u00e0ng theo Town.<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u00e3y quay l\u1ea1i tr\u00ecnh so\u1ea1n th\u1ea3o Power Query b\u1eb1ng c\u00e1ch nh\u1ea5p \u0111\u00fap v\u00e0o truy v\u1ea5n Sales trong ng\u0103n <\/span><span style=\"font-weight: 400\">Queries and Connections<\/span><span style=\"font-weight: 400\">.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Trong tr\u00ecnh so\u1ea1n th\u1ea3o Power Query, ch\u1ecdn <\/span><b>Home -&gt; Merge Queries (drop-down)<\/b><span style=\"font-weight: 400\">.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u1ede \u0111\u00e2y c\u00f3 hai t\u00f9y ch\u1ecdn, <\/span><i><span style=\"font-weight: 400\">Merge Queries <\/span><\/i><span style=\"font-weight: 400\">v\u00e0 H\u1ee3p nh\u1ea5t <\/span><i><span style=\"font-weight: 400\">Merge Queries as New<\/span><\/i><span style=\"font-weight: 400\">. S\u1ef1 kh\u00e1c bi\u1ec7t gi\u1eefa ch\u00fang l\u00e0 li\u1ec7u Merge s\u1ebd t\u1ea1o ra m\u1ed9t truy v\u1ea5n m\u1edbi hay \u0111\u01b0\u1ee3c th\u00eam v\u00e0o d\u01b0\u1edbi d\u1ea1ng m\u1ed9t b\u01b0\u1edbc chuy\u1ec3n \u0111\u1ed5i trong m\u1ed9t truy v\u1ea5n hi\u1ec7n c\u00f3. \u0110\u1ec3 d\u1ec5 d\u00e0ng, ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng m\u1ed9t truy v\u1ea5n m\u1edbi. Ch\u1ecdn t\u00f9y ch\u1ecdn <\/span><b>Merge Queries as New<\/b><span style=\"font-weight: 400\">.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u1eeda s\u1ed5 Merge s\u1ebd m\u1edf:<\/span><\/p>\n<p><span style=\"font-weight: 400\">R\u1ea5t nhi\u1ec1u \u0111i\u1ec1u di\u1ec5n ra \u1edf \u0111\u00e2y:<\/span><\/p>\n<ol>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Ch\u1ecdn truy v\u1ea5n \u0111\u1ea7u ti\u00ean \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u2013 v\u00ed d\u1ee5 c\u1ee7a ch\u00fang ta, \u0111\u00f3 l\u00e0 truy v\u1ea5n Sales.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Ch\u1ecdn truy v\u1ea5n th\u1ee9 hai s\u1ebd \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u2013 trong v\u00ed d\u1ee5 c\u1ee7a ch\u00fang ta, \u0111\u00f3 l\u00e0 truy v\u1ea5n Customers.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Ch\u1ecdn c\u1ed9t t\u1eeb hai b\u1ea3ng s\u1ebd \u0111\u01b0\u1ee3c kh\u1edbp v\u1edbi nhau.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Join Kind cung c\u1ea5p s\u00e1u lo\u1ea1i h\u1ee3p nh\u1ea5t kh\u00e1c nhau \u2013 Ch\u1ecdn Left Outer n\u1ebfu l\u00e0m vi\u1ec7c c\u00f9ng v\u1edbi v\u00ed d\u1ee5.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Nh\u1ea5p OK.<\/span><\/li>\n<\/ol>\n<p><span style=\"font-weight: 400\">C\u00f3 6 ki\u1ec3u n\u1ed1i, h\u00e3y xem ph\u1ea7n b\u00ean d\u01b0\u1edbi \u0111\u1ec3 t\u00ecm hi\u1ec3u nh\u1eefng g\u00ec c\u00e1c ki\u1ec3u l\u00e0m kh\u00e1c nhau.<\/span><\/p>\n<p><span style=\"font-weight: 400\">M\u1ed9t truy v\u1ea5n m\u1edbi \u0111\u01b0\u1ee3c t\u1ea1o. Truy v\u1ea5n \u0111\u1ea7u ti\u00ean \u0111\u01b0\u1ee3c ch\u1ecdn trong c\u1eeda s\u1ed5 <\/span><span style=\"font-weight: 400\">Merge <\/span><span style=\"font-weight: 400\">\u0111\u01b0\u1ee3c hi\u1ec3n th\u1ecb, v\u1edbi m\u1ed9t c\u1ed9t b\u1ed5 sung ch\u1ee9a b\u1ea3ng c\u1ee7a truy v\u1ea5n th\u1ee9 hai.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Nh\u1ea5p v\u00e0o bi\u1ec3u t\u01b0\u1ee3ng b\u1ea3ng m\u1edf r\u1ed9ng trong ti\u00eau \u0111\u1ec1 c\u1ee7a c\u1ed9t Customers. Ch\u00fang ta ch\u1ec9 c\u1ea7n c\u00e1c c\u1ed9t Customers v\u00e0 Town, v\u00ec v\u1eady h\u00e3y ch\u1ecdn nh\u1eefng c\u1ed9t \u0111\u00f3, sau \u0111\u00f3 nh\u1ea5p v\u00e0o OK.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Power Query s\u1ebd h\u1ee3p nh\u1ea5t c\u00e1c truy v\u1ea5n, b\u1eb1ng c\u00e1ch tra c\u1ee9u t\u1eeb b\u1ea3ng \u0111\u1ea7u ti\u00ean v\u00e0o b\u1ea3ng th\u1ee9 hai.<\/span><\/p>\n<p><span style=\"font-weight: 400\">K\u1ebft h\u1ee3p \u0111\u00e3 ch\u1ecdn bao g\u1ed3m t\u1ea5t c\u1ea3 c\u00e1c m\u1ee5c t\u1eeb b\u1ea3ng Sales v\u00e0 c\u00e1c m\u1ee5c ph\u00f9 h\u1ee3p t\u1eeb b\u1ea3ng Customers. B\u1ea5t k\u1ef3 m\u1ee5c n\u00e0o kh\u00f4ng c\u00f3 k\u1ebft qu\u1ea3 ph\u00f9 h\u1ee3p s\u1ebd hi\u1ec3n th\u1ecb null, nh\u01b0 th\u1ec3 hi\u1ec7n h\u00ecnh b\u00ean d\u01b0\u1edbi. Kh\u00e1ch h\u00e0ng Mega Mart t\u1ed3n t\u1ea1i trong truy v\u1ea5n Sales, nh\u01b0ng kh\u00f4ng t\u1ed3n t\u1ea1i trong truy v\u1ea5n Customers, do \u0111\u00f3 gi\u00e1 tr\u1ecb r\u1ed7ng \u0111\u01b0\u1ee3c hi\u1ec3n th\u1ecb.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 ho\u00e0n th\u00e0nh v\u00ed d\u1ee5 c\u1ee7a ch\u00fang ta, ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng c\u1ed9t \u0111\u01b0\u1ee3c h\u1ee3p nh\u1ea5t \u0111\u1ec3 t\u1ea1o b\u00e1o c\u00e1o t\u00f3m t\u1eaft. Ch\u1ecdn c\u1ed9t Town, sau \u0111\u00f3 nh\u1ea5p v\u00e0o <\/span><b>Transform -&gt; Group By<\/b><span style=\"font-weight: 400\"> t\u1eeb menu.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Trong h\u00ecnh \u1edf tr\u00ean, t\u00f4i \u0111\u00e3 ch\u1ecdn T\u00ednh t\u1ed5ng c\u1ed9t Value, th\u00e0nh m\u1ed9t c\u1ed9t \u0111\u01b0\u1ee3c g\u1ecdi l\u00e0 <\/span><span style=\"font-weight: 400\">Total Sales<\/span><span style=\"font-weight: 400\">. Sau khi t\u00f4i nh\u1ea5p v\u00e0o OK, n\u00f3 s\u1ebd t\u1ea1o m\u1ed9t b\u00e1o c\u00e1o t\u00f3m t\u1eaft v\u1ec1 doanh s\u1ed1 b\u00e1n h\u00e0ng theo Town (xem h\u00ecnh b\u00ean d\u01b0\u1edbi).<\/span><\/p>\n<p>\u00a0<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Cac_loai_lien_ket\"><\/span><b>C\u00e1c lo\u1ea1i li\u00ean k\u1ebft<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">Tr\u01b0\u1edbc khi xem x\u00e9t m\u1ed9t v\u00ed d\u1ee5 kh\u00e1c, h\u00e3y d\u00e0nh v\u00e0i ph\u00fat \u0111\u1ec3 suy ngh\u0129 v\u1ec1 s\u00e1u lo\u1ea1i ph\u00e9p n\u1ed1i kh\u00e1c nhau.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u00e1c ph\u00e9p n\u1ed1i cho ph\u00e9p ch\u00fang ta so s\u00e1nh c\u00e1c danh s\u00e1ch, sau \u0111\u00f3 tr\u1ea3 v\u1ec1 c\u00e1c gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng. R\u1ea5t may, c\u00e1c m\u00f4 t\u1ea3 \u0111\u01b0\u1ee3c cung c\u1ea5p cho m\u1ed7i li\u00ean k\u1ebft l\u00e0 m\u1ed9t b\u1ea3n t\u00f3m t\u1eaft v\u1ec1 nh\u1eefng g\u00ec n\u00f3 ho\u1ea1t \u0111\u1ed9ng.<\/span><\/p>\n<h4><span class=\"ez-toc-section\" id=\"Outer_Joins\"><\/span><b>Outer Joins<\/b><span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><span style=\"font-weight: 400\">Outer joins tr\u1ea3 v\u1ec1 t\u1ea5t c\u1ea3 c\u00e1c h\u00e0ng t\u1eeb m\u1ed9t ho\u1eb7c c\u1ea3 hai danh s\u00e1ch. Ch\u00fang ta c\u00f3 th\u1ec3 ch\u1ecdn Tr\u00e1i, Ph\u1ea3i ho\u1eb7c To\u00e0n b\u1ed9, t\u00f9y thu\u1ed9c v\u00e0o danh s\u00e1ch n\u00e0o s\u1ebd tr\u1ea3 v\u1ec1 t\u1ea5t c\u1ea3 c\u00e1c h\u00e0ng c\u1ee7a n\u00f3.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Left Outer<\/span><span style=\"font-weight: 400\"> \u2013 t\u1ea5t c\u1ea3 c\u00e1c m\u1ee5c t\u1eeb danh s\u00e1ch \u0111\u1ea7u ti\u00ean \u0111\u01b0\u1ee3c tr\u1ea3 l\u1ea1i, c\u00f9ng v\u1edbi c\u00e1c m\u1ee5c ph\u00f9 h\u1ee3p t\u1eeb danh s\u00e1ch th\u1ee9 hai.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Right Outer<\/span><span style=\"font-weight: 400\"> \u2013 t\u1ea5t c\u1ea3 c\u00e1c m\u1ee5c t\u1eeb danh s\u00e1ch th\u1ee9 hai \u0111\u01b0\u1ee3c tr\u1ea3 l\u1ea1i, c\u00f9ng v\u1edbi c\u00e1c m\u1ee5c ph\u00f9 h\u1ee3p t\u1eeb danh s\u00e1ch \u0111\u1ea7u ti\u00ean.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Full Outer \u2013 t\u1ea5t c\u1ea3 c\u00e1c m\u1ee5c t\u1eeb c\u1ea3 hai danh s\u00e1ch.<\/span><\/li>\n<\/ul>\n<h4><span class=\"ez-toc-section\" id=\"Inner_Join\"><\/span><b>Inner Join<\/b><span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><span style=\"font-weight: 400\">Inner Join ch\u1ec9 tr\u1ea3 v\u1ec1 c\u00e1c gi\u00e1 tr\u1ecb t\u1ed3n t\u1ea1i tr\u00ean c\u1ea3 hai danh s\u00e1ch. N\u1ebfu danh s\u00e1ch \u0111\u1ea7u ti\u00ean ho\u1eb7c danh s\u00e1ch th\u1ee9 hai c\u00f3 c\u00e1c m\u1ee5c kh\u00f4ng c\u00f3 trong danh s\u00e1ch kh\u00e1c, th\u00ec nh\u1eefng m\u1ee5c n\u00e0y s\u1ebd b\u1ecb lo\u1ea1i kh\u1ecfi k\u1ebft qu\u1ea3 cu\u1ed1i c\u00f9ng.<\/span><\/p>\n<h4><span class=\"ez-toc-section\" id=\"Anti_Joins\"><\/span><b>Anti Joins<\/b><span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><span style=\"font-weight: 400\">Anti Joins tr\u1ea3 v\u1ec1 c\u00e1c m\u1ee5c kh\u00f4ng kh\u1edbp v\u1edbi b\u1ea5t k\u1ef3 gi\u00e1 tr\u1ecb n\u00e0o trong danh s\u00e1ch kh\u00e1c.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Left Anti \u2013 tr\u1ea3 v\u1ec1 b\u1ea5t k\u1ef3 m\u1ee5c n\u00e0o trong danh s\u00e1ch \u0111\u1ea7u ti\u00ean kh\u00f4ng kh\u1edbp v\u1edbi danh s\u00e1ch th\u1ee9 hai.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Right Anti \u2013 b\u1ea5t k\u1ef3 m\u1ee5c n\u00e0o trong danh s\u00e1ch th\u1ee9 hai kh\u00f4ng kh\u1edbp v\u1edbi danh s\u00e1ch \u0111\u1ea7u ti\u00ean.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">\u0110\u00e2y l\u00e0 t\u1ea5t c\u1ea3 nh\u1eefng b\u1eb1ng ch\u1ee9ng r\u1eb1ng Power Query c\u00f3 th\u1ec3 hi\u1ec7u qu\u1ea3 h\u01a1n VLOOKUP.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Tra_cuu_voi_ket_qua_khop_gan_dung\"><\/span><b>Tra c\u1ee9u v\u1edbi k\u1ebft qu\u1ea3 kh\u1edbp g\u1ea7n \u0111\u00fang<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd l\u00e0 l\u00fac \u0111\u1ec3 t\u00ecm hi\u1ec3u kh\u1edbp g\u1ea7n \u0111\u00fang. Tr\u01b0\u1edbc khi ch\u00fang ta b\u1eaft \u0111\u1ea7u, n\u1ebfu b\u1ea1n kh\u00f4ng ch\u1eafc \u0111i\u1ec1u n\u00e0y c\u00f3 ngh\u0129a l\u00e0 g\u00ec, vui l\u00f2ng \u0111\u1ecdc b\u00e0i \u0111\u0103ng c\u1ee7a t\u00f4i v\u1ec1 Kh\u1edbp g\u1ea7n \u0111\u00fang.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Trong v\u00ed d\u1ee5 n\u00e0y, ch\u00fang ta \u0111ang t\u00ednh to\u00e1n gi\u00e1 tr\u1ecb c\u1ee7a kho\u1ea3n gi\u1ea3m gi\u00e1 do m\u1ed9t kh\u00e1ch h\u00e0ng d\u1ef1a tr\u00ean gi\u00e1 tr\u1ecb b\u00e1n h\u00e0ng. Kh\u00e1ch h\u00e0ng c\u00f3 doanh s\u1ed1 b\u00e1n h\u00e0ng l\u1edbn h\u01a1n:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">$500 nh\u1eadn \u0111\u01b0\u1ee3c chi\u1ebft kh\u1ea5u 2%\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">$750 nh\u1eadn \u0111\u01b0\u1ee3c chi\u1ebft kh\u1ea5u\u00a0 5%\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">$1,000 nh\u1eadn \u0111\u01b0\u1ee3c chi\u1ebft kh\u1ea5u 10%<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">C\u00e1c ng\u01b0\u1ee1ng n\u00e0y \u0111\u01b0\u1ee3c hi\u1ec3n th\u1ecb trong b\u1ea3ng tr\u00ean trang t\u00ednh.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Tr\u01b0\u1edbc ti\u00ean, ch\u00fang ta c\u1ea7n t\u00ednh to\u00e1n t\u1ed5ng doanh s\u1ed1 b\u00e1n h\u00e0ng c\u1ee7a kh\u00e1ch h\u00e0ng tr\u01b0\u1edbc, sau \u0111\u00f3 th\u1ef1c hi\u1ec7n ph\u00e9p h\u1ee3p nh\u1ea5t ph\u1ee9c t\u1ea1p h\u01a1n \u0111\u1ec3 t\u00ednh gi\u00e1 tr\u1ecb gi\u1ea3m gi\u00e1 d\u1ef1a tr\u00ean t\u1ed5ng doanh s\u1ed1 b\u00e1n h\u00e0ng.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Th\u00eam m\u1ed9t phi\u00ean b\u1ea3n kh\u00e1c c\u1ee7a b\u1ea3ng Sales v\u00e0o Power Query b\u1eb1ng c\u00e1ch l\u00e0m theo c\u00e1c b\u01b0\u1edbc t\u01b0\u01a1ng t\u1ef1 \u1edf tr\u00ean.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Trong tr\u00ecnh so\u1ea1n th\u1ea3o Power Query, h\u00e3y nh\u1ea5p v\u00e0o <\/span><b>Transform -&gt; Group By<\/b><span style=\"font-weight: 400\">.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u1eeda s\u1ed5 Group By s\u1ebd m\u1edf ra, th\u1ef1c hi\u1ec7n c\u00e1c l\u1ef1a ch\u1ecdn sau:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Group by: Customer<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">New column name: Total Sales<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Operation: Sum<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Column: Value<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">B\u1ea5m OK. C\u1eeda s\u1ed5 Xem tr\u01b0\u1edbc s\u1ebd hi\u1ec3n th\u1ecb b\u1ea3ng sau, v\u1edbi t\u1ed5ng doanh s\u1ed1 c\u1ee7a kh\u00e1ch h\u00e0ng.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Trong v\u00ed d\u1ee5 cu\u1ed1i c\u00f9ng, ch\u00fang ta \u0111\u00e3 t\u1ea1o Merge d\u01b0\u1edbi d\u1ea1ng truy v\u1ea5n m\u1edbi; l\u1ea7n n\u00e0y, ch\u00fang ta c\u00f3 th\u1ec3 th\u00eam Merge nh\u01b0 m\u1ed9t b\u01b0\u1edbc kh\u00e1c. Nh\u1ea5p v\u00e0o <\/span><b>Home -&gt; Merge Queries<\/b><span style=\"font-weight: 400\">.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Trong c\u1eeda s\u1ed5 Merge, ch\u00fang ta mu\u1ed1n s\u1eed d\u1ee5ng m\u1ed9t ph\u00e9p n\u1ed1i Full Outer gi\u1eefa c\u1ed9t Total Sales c\u1ee7a b\u1ea3ng Sales v\u00e0 <\/span><span style=\"font-weight: 400\">Rebate Band<\/span><span style=\"font-weight: 400\"> c\u1ee7a b\u1ea3ng <\/span><span style=\"font-weight: 400\">Rebates<\/span><span style=\"font-weight: 400\">.<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u1ea5m OK \u0111\u1ec3 \u0111\u00f3ng v\u00e0 quay l\u1ea1i C\u1eeda s\u1ed5 xem tr\u01b0\u1edbc.<\/span><\/p>\n<p><span style=\"font-weight: 400\">M\u1edf r\u1ed9ng c\u1ed9t Rebates, bao g\u1ed3m c\u1ea3 hai c\u1ed9t, sau \u0111\u00f3 b\u1ea5m OK.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u1eeda s\u1ed5 Xem tr\u01b0\u1edbc b\u00e2y gi\u1edd tr\u00f4ng gi\u1ed1ng nh\u01b0 sau:<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd ch\u00fang ta s\u1ebd vi\u1ebft m\u1ed9t c\u00e2u l\u1ec7nh if. Ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng t\u00ednh n\u0103ng <\/span><span style=\"font-weight: 400\">Conditional Column<\/span><span style=\"font-weight: 400\">, nh\u01b0ng t\u00f4i th\u1ea5y vi\u1ebft n\u00f3 d\u01b0\u1edbi d\u1ea1ng c\u00f4ng th\u1ee9c d\u1ec5 d\u00e0ng h\u01a1n. Nh\u1ea5p v\u00e0o <\/span><b>Add Column -&gt; Custom Column<\/b><span style=\"font-weight: 400\">.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u1eeda s\u1ed5 <\/span><span style=\"font-weight: 400\">Custom Column<\/span><span style=\"font-weight: 400\"> m\u1edf ra. \u0110\u1eb7t t\u00ean m\u1edbi cho c\u1ed9t v\u00e0 nh\u1eadp v\u0103n b\u1ea3n sau v\u00e0o h\u1ed9p c\u00f4ng th\u1ee9c:<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ch\u00fang ta s\u1ebd \u0111\u1ec1 c\u1eadp chi ti\u1ebft h\u01a1n c\u00e1c c\u00e2u l\u1ec7nh if trong m\u1ed9t b\u00e0i \u0111\u0103ng trong t\u01b0\u01a1ng lai. B\u00e2y gi\u1edd, h\u00e3y ngh\u0129 v\u1ec1 n\u00f3 nh\u01b0 l\u00e0 h\u00e0m IF ti\u00eau chu\u1ea9n c\u1ee7a Excel v\u1edbi nh\u1eefng thay \u0111\u1ed5i sau:\u00a0<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Lo\u1ea1i b\u1ecf d\u1ea5u ngo\u1eb7c<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Thay d\u1ea5u ph\u1ea9y \u0111\u1ea7u ti\u00ean b\u1eb1ng t\u1eeb <\/span><b>then<\/b><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Thay \u0111\u1ea5u ph\u1ea9y cu\u1ed1i c\u00f9ng b\u1eb1ng t\u1eeb <\/span><b>else<\/b><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">S\u1eafp x\u1ebfp c\u1ed9t m\u1edbi b\u1eb1ng c\u00e1ch ch\u1ecdn ti\u00eau \u0111\u1ec1 c\u1ed9t, sau \u0111\u00f3 nh\u1ea5p v\u00e0o <\/span><b>Home -&gt; A-Z<\/b><span style=\"font-weight: 400\">.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ti\u1ebfp theo, ch\u1ecdn c\u1ed9t <\/span><b>Rebate Value<\/b><span style=\"font-weight: 400\"> v\u00e0 nh\u1ea5p v\u00e0o <\/span><b>Transform -&gt; Fill (dropdown) -&gt; Down<\/b><span style=\"font-weight: 400\">.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u1eeda s\u1ed5 Xem tr\u01b0\u1edbc s\u1ebd gi\u1ed1ng nh\u01b0 sau:<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd ch\u1ec9 c\u00f2n l\u1ea1i m\u1ed9t v\u00e0i b\u01b0\u1edbc \u0111\u1ec3 d\u1ecdn d\u1eb9p b\u1ea3ng:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">L\u1ecdc ra c\u00e1c gi\u00e1 tr\u1ecb r\u1ed7ng t\u1eeb c\u1ed9t Customers\u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Lo\u1ea1i b\u1ecf t\u1ea5t c\u1ea3 c\u00e1c c\u1ed9t ngo\u1ea1i tr\u1eeb <\/span><b>Customer<\/b><span style=\"font-weight: 400\">, <\/span><b>Total Sales, <\/b><span style=\"font-weight: 400\">v\u00e0 <\/span><b>Rebate Value.<\/b><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd ch\u00fang ta h\u00e3y k\u1ebft th\u00fac b\u1eb1ng c\u00e1ch t\u00ednh to\u00e1n gi\u00e1 tr\u1ecb gi\u1ea3m gi\u00e1. Ch\u1ecdn c\u1ed9t Total Sales v\u00e0 <\/span><b>Rebate Value<\/b><span style=\"font-weight: 400\">, nh\u1ea5p v\u00e0o <\/span><b>Add Column -&gt; Standard -&gt; Multiply<\/b><span style=\"font-weight: 400\">.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Truy v\u1ea5n hi\u1ec3n th\u1ecb k\u1ebft qu\u1ea3 cu\u1ed1i c\u00f9ng<\/span><\/p>\n<p><span style=\"font-weight: 400\">Multiple matches<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110i\u1ec1u g\u00ec x\u1ea3y ra n\u1ebfu c\u00f3 nhi\u1ec1u m\u1ee5c c\u00f3 th\u1ec3 kh\u1edbp? Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, kh\u1edbp ch\u00ednh x\u00e1c h\u00e0m VLOOKUP trong Excel tr\u1ea3 v\u1ec1 m\u1ee5c \u0111\u1ea7u ti\u00ean m\u00e0 n\u00f3 t\u00ecm th\u1ea5y trong danh s\u00e1ch. <\/span><span style=\"font-weight: 400\">Merge<\/span><span style=\"font-weight: 400\"> ho\u1ea1t \u0111\u1ed9ng kh\u00e1c.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Merge<\/span><span style=\"font-weight: 400\"> s\u1ebd tr\u1ea3 v\u1ec1 t\u1eebng phi\u00ean b\u1ea3n c\u1ee7a m\u1ed9t m\u1ee5c ph\u00f9 h\u1ee3p. V\u00ed d\u1ee5: gi\u1ea3 s\u1eed ch\u00fang ta c\u00f3 hai b\u1ea3ng m\u1ed9t c\u00f3 th\u00f4ng tin Product v\u00e0 m\u1ed9t b\u1ea3ng kh\u00e1c c\u00f3 d\u1eef li\u1ec7u Stock v\u1ec1 c\u00e1c s\u1ea3n ph\u1ea9m \u0111\u00f3.<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu ch\u00fang ta ch\u1ec9 <\/span><span style=\"font-weight: 400\">Merge<\/span><span style=\"font-weight: 400\"> tr\u00ean c\u1ed9t Size, c\u00e1c m\u1ee5c c\u00f3 k\u00edch th\u01b0\u1edbc M s\u1ebd \u0111\u01b0\u1ee3c sao ch\u00e9p v\u00ec c\u00f3 hai ch\u1eef M trong b\u1ea3ng \u0111ang \u0111\u01b0\u1ee3c tra c\u1ee9u.<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu v\u00ec b\u1ea5t c\u1ee9 l\u00fd do g\u00ec, b\u1ea1n ch\u1ec9 mu\u1ed1n kh\u1edbp m\u1ed9t m\u1ee5c, h\u00e3y x\u00f3a c\u00e1c m\u1ee5c tr\u00f9ng l\u1eb7p kh\u1ecfi m\u1ed9t trong c\u00e1c b\u1ea3ng tr\u01b0\u1edbc khi th\u1ef1c hi\u1ec7n Merge.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Tra_cuu_nhieu_tieu_chi\"><\/span><b>Tra c\u1ee9u nhi\u1ec1u ti\u00eau ch\u00ed<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">Tin vui l\u00e0 Power Query kh\u00f4ng gi\u1edbi h\u1ea1n b\u1ea1n trong m\u1ed9t danh s\u00e1ch. Gi\u1ea3 s\u1eed b\u1ea1n mu\u1ed1n kh\u1edbp ba c\u1ed9t?\u00a0 Kh\u00f4ng sao \u0111\u00e2u.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Th\u1ee9 t\u1ef1 b\u1ea1n ch\u1ecdn c\u00e1c c\u1ed9t x\u00e1c \u0111\u1ecbnh c\u1ed9t n\u00e0o \u0111\u01b0\u1ee3c kh\u1edbp. Nh\u00ecn v\u00e0o h\u00ecnh b\u00ean d\u01b0\u1edbi. C\u00e1c c\u1ed9t \u0111\u01b0\u1ee3c ch\u1ecdn trong b\u1ea3ng \u0111\u1ea7u ti\u00ean l\u00e0 <\/span><span style=\"font-weight: 400\">Color, Size<\/span><span style=\"font-weight: 400\">, sau \u0111\u00f3 l\u00e0 Location (theo th\u1ee9 t\u1ef1 \u0111\u00f3). C\u00e1c s\u1ed1 trong ti\u00eau \u0111\u1ec1 c\u1ed9t x\u00e1c \u0111\u1ecbnh th\u1ee9 t\u1ef1 c\u00e1c m\u1ee5c \u0111\u01b0\u1ee3c ch\u1ecdn. C\u00e1c c\u1ed9t \u0111\u01b0\u1ee3c ch\u1ecdn trong b\u1ea3ng th\u1ee9 hai c\u00f3 c\u00f9ng th\u1ee9 t\u1ef1, <\/span><span style=\"font-weight: 400\">Color, Size v\u00e0 Location.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110i\u1ec1u \u0111\u00f3 d\u1ec5 d\u00e0ng l\u00e0m sao!<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Tra_cuu_fuzzy_match\"><\/span><b>Tra c\u1ee9u fuzzy match<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">Khi nh\u00ecn v\u00e0o c\u1eeda s\u1ed5 Merge trong c\u00e1c v\u00ed d\u1ee5 c\u1ee7a ch\u00fang ta, b\u1ea1n c\u00f3 nh\u1eadn th\u1ea5y t\u00f9y ch\u1ecdn \u201cS\u1eed d\u1ee5ng kh\u1edbp m\u1edd \u0111\u1ec3 th\u1ef1c hi\u1ec7n h\u1ee3p nh\u1ea5t\u201d kh\u00f4ng? \u0110\u00e2y l\u00e0 m\u1ed9t t\u00f9y ch\u1ecdn m\u1edbi h\u1ee3p l\u00fd, n\u00f3 kh\u1edbp v\u1edbi c\u00e1c gi\u00e1 tr\u1ecb t\u01b0\u01a1ng t\u1ef1.\u00a0 V\u00ed d\u1ee5: n\u00f3 s\u1ebd kh\u1edbp \u201cPower Query\u201d v\u1edbi \u201cpower-query\u201d. B\u1ea1n kh\u00f4ng th\u1ec3 l\u00e0m \u0111i\u1ec1u \u0111\u00f3 v\u1edbi VLOOKUP!\u00a0 Ch\u00fang t\u00f4i th\u1eadm ch\u00ed c\u00f3 th\u1ec3 thay \u0111\u1ed5i c\u00e1c ng\u01b0\u1ee1ng c\u1ee7a c\u00e1c gi\u00e1 tr\u1ecb t\u01b0\u01a1ng t\u1ef1 tr\u01b0\u1edbc khi ch\u00fang kh\u1edbp v\u1edbi nhau.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ec \u0111\u00e2y l\u00e0 m\u1ed9t t\u00ednh n\u0103ng m\u1edbi n\u00ean t\u00f4i s\u1ebd kh\u00f4ng \u0111i v\u00e0o chi ti\u1ebft \u1edf \u0111\u00e2y, nh\u01b0ng b\u1ea1n c\u00f3 th\u1ec3 t\u00ecm hi\u1ec3u th\u00eam th\u00f4ng tin trong c\u00e1c b\u00e0i vi\u1ebft sau:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Excel University \u2013 <a href=\"http:\/\/blog.ezworkapp.com\/tag\/fuzzy-matching-trong-power-query\">Fuzzy match with Power Query<\/a><\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Microsoft \u2013 <a href=\"http:\/\/blog.ezworkapp.com\/group-doi-tuong-fuzzy-matching-trong-power-query.html\">Fuzzy match support for Get &amp; Transform<\/a> (Power Query)<\/span><\/li>\n<\/ul>\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\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-57.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":1903,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1902","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\/1902","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=1902"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/1902\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/1903"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=1902"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=1902"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=1902"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}