{"id":12694,"date":"2025-02-24T12:25:24","date_gmt":"2025-02-24T12:25:24","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/02\/24\/su-dung-ham-map-dem-tan-suat-xuat-hien-gia-tri-tang-tien\/"},"modified":"2025-02-24T12:25:24","modified_gmt":"2025-02-24T12:25:24","slug":"su-dung-ham-map-dem-tan-suat-xuat-hien-gia-tri-tang-tien","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/02\/24\/su-dung-ham-map-dem-tan-suat-xuat-hien-gia-tri-tang-tien\/","title":{"rendered":"S\u1eed d\u1ee5ng h\u00e0m MAP \u0111\u1ebfm t\u1ea7n su\u1ea5t xu\u1ea5t hi\u1ec7n gi\u00e1 tr\u1ecb t\u0103ng ti\u1ebfn"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Gi\u1ea3 s\u1eed ta c\u00f3 m\u1ed9t d\u00e3y [\u201ca\u201d;\u201db\u201d;\u201dc\u201d;\u201db\u201d;\u201da\u201d], l\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 \u0111\u1ebfm t\u1ea7n su\u1ea5t xu\u1ea5t hi\u1ec7n gi\u00e1 tr\u1ecb t\u0103ng ti\u1ebfn, hay n\u00f3i c\u00e1ch kh\u00e1c, tr\u1ea3 v\u1ec1 m\u1ed9t d\u00e3y [1;1;1;2;2]? Trong b\u00e0i vi\u1ebft n\u00e0y, H\u1ecdc Excel Online s\u1ebd h\u01b0\u1edbng d\u1eabn b\u1ea1n s\u1eed d\u1ee5ng h\u00e0m MAP \u0111\u1ec3 l\u00e0m \u0111i\u1ec1u tr\u00ean.<\/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-67bc65335471c\" 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\/dem-tan-suat-xuat-hien-gia-tri-tang-tien.html#Dem_tan_suat_xuat_hien_gia_tri_tang_tien\" title=\"\u0110\u1ebfm t\u1ea7n su\u1ea5t xu\u1ea5t hi\u1ec7n gi\u00e1 tr\u1ecb t\u0103ng ti\u1ebfn\">\u0110\u1ebfm t\u1ea7n su\u1ea5t xu\u1ea5t hi\u1ec7n gi\u00e1 tr\u1ecb t\u0103ng ti\u1ebfn<\/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\/dem-tan-suat-xuat-hien-gia-tri-tang-tien.html#Ung_dung\" title=\"\u1ee8ng d\u1ee5ng\">\u1ee8ng d\u1ee5ng<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Dem_tan_suat_xuat_hien_gia_tri_tang_tien\"><\/span>\u0110\u1ebfm t\u1ea7n su\u1ea5t xu\u1ea5t hi\u1ec7n gi\u00e1 tr\u1ecb t\u0103ng ti\u1ebfn<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Ta c\u00f3 d\u1eef li\u1ec7u sau:<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"369\" height=\"219\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/02\/image-3.png\" class=\"wp-image-44243\" title=\"image-3\"><\/figure>\n<\/div>\n<p>Trong th\u1ef1c t\u1ebf, \u0111\u00f4i khi ta s\u1ebd c\u1ea7n \u0111\u00e1nh s\u1ed1 th\u1ee9 t\u1ef1 cho t\u1eebng ph\u1ea7n t\u1eed c\u1ee7a m\u1ed9t d\u00e3y. V\u00ed d\u1ee5, gi\u00e1 tr\u1ecb a \u1edf \u00f4 A1 xu\u1ea5t hi\u1ec7n l\u1ea7n \u0111\u1ea7u s\u1ebd t\u01b0\u01a1ng \u1ee9ng v\u1edbi 1, \u1edf \u00f4 A5 xu\u1ea5t hi\u1ec7n l\u1ea7n th\u1ee9 hai s\u1ebd t\u01b0\u01a1ng \u1ee9ng v\u1edbi 2\u2026 \u00c1p d\u1ee5ng cho c\u00e1c gi\u00e1 tr\u1ecb b v\u00e0 c, m\u1ee5c ti\u00eau ta tr\u1ea3 v\u1ec1 s\u1ebd l\u00e0 m\u1ed9t m\u1ea3ng <strong>1,1,1,2,2:<\/strong><\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"474\" height=\"229\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/02\/image-4.png\" class=\"wp-image-44244\" title=\"image-4\"><\/figure>\n<\/div>\n<p>Ta ho\u00e0n to\u00e0n c\u00f3 th\u1ec3 l\u00e0m \u0111i\u1ec1u n\u00e0y v\u1edbi h\u00e0m COUNTIF\/COUNTIFS nh\u01b0 sau t\u1ea1i \u00f4 B1:<\/p>\n<pre class=\"wp-block-code\"><code>=COUNTIF($A$1:A1;A1)\n<\/code><\/pre>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"471\" height=\"253\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/02\/image-5.png\" class=\"wp-image-44245\" title=\"image-5\"><\/figure>\n<\/div>\n<p>M\u1ed9t c\u00f4ng th\u1ee9c v\u00f4 c\u00f9ng \u0111\u01a1n gi\u1ea3n ph\u1ea3i kh\u00f4ng n\u00e0o? Ch\u00fang ta s\u1ebd \u201ckho\u00e1\u201d \u00f4 \u0111\u1ea7u ti\u00ean, sau \u0111\u00f3 ti\u1ebfn h\u00e0nh \u0111\u1ebfm c\u00e1c ph\u1ea7n t\u1eed, t\u1eeb \u0111\u00f3 thu \u0111\u01b0\u1ee3c c\u00e1c gi\u00e1 tr\u1ecb t\u0103ng ti\u1ebfn.<\/p>\n<p>Tuy nhi\u00ean, c\u00f3 m\u1ed9t v\u1ea5n \u0111\u1ec1 v\u1edbi COUNTIF\/COUNTIFS \u0111\u00f3 l\u00e0 ch\u00fang ta b\u1eaft bu\u1ed9c ph\u1ea3i s\u1eed d\u1ee5ng <strong>tham chi\u1ebfu. <\/strong>N\u00f3i c\u00e1ch kh\u00e1c, n\u1ebfu d\u1eef li\u1ec7u c\u1ee7a ch\u00fang ta l\u00e0 k\u1ebft qu\u1ea3 c\u1ee7a m\u1ed9t ph\u00e9p t\u00ednh kh\u00e1c \u2013 m\u1ed9t m\u1ea3ng \u0111\u01b0\u1ee3c l\u01b0u trong b\u1ed9 nh\u1edb thay v\u00ec ghi v\u00e0o \u00f4 \u2013 th\u00ec c\u00f4ng th\u1ee9c tr\u00ean s\u1ebd kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng.<\/p>\n<p>V\u1eady gi\u1ea3i ph\u00e1p \u1edf \u0111\u00e2y l\u00e0 g\u00ec?<\/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\/2025\/02\/ex101_92019-nho-1.gif\" title=\"ex101_92019-nho-1\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>C\u00e1c b\u1ea1n h\u00e3y tham kh\u1ea3o c\u00f4ng th\u1ee9c sau:<\/p>\n<pre class=\"wp-block-code\"><code>=MAP(SEQUENCE(COUNTA({\"a\";\"b\";\"c\";\"b\";\"a\"}));LAMBDA(i;SUM(--(INDEX({\"a\";\"b\";\"c\";\"b\";\"a\"}; SEQUENCE(i)) = INDEX({\"a\";\"b\";\"c\";\"b\";\"a\"}; i)))))\n\n<\/code><\/pre>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"864\" height=\"281\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/02\/image-6.png\" class=\"wp-image-44246\" title=\"image-6\"><\/figure>\n<\/div>\n<p>Trong c\u00f4ng th\u1ee9c tr\u00ean, t\u1ea5t c\u1ea3 \u0111\u01b0\u1ee3c t\u1ea1o ra v\u00e0 ghi v\u00e0o b\u1ed9 nh\u1edb, kh\u00f4ng h\u1ec1 li\u00ean quan t\u1edbi b\u1ea5t k\u1ef3 tham chi\u1ebfu n\u00e0o. B\u1edfi v\u1eady, ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng \u0111\u1ec3 x\u1eed l\u00fd c\u00e1c d\u1eef li\u1ec7u t\u1eeb nh\u1eefng ph\u00e9p t\u00ednh tr\u01b0\u1edbc. C\u00f4ng th\u1ee9c tr\u00ean c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c di\u1ec5n gi\u1ea3i th\u00e0nh 2 b\u01b0\u1edbc: <\/p>\n<p><em>$A$1:A1=A1; $A$1:A2=A2;$A$3:A3=A3;$A$4:A4=A4;$A$5:A5=A5<\/em><\/p>\n<p>K\u1ebft qu\u1ea3 c\u1ee7a b\u01b0\u1edbc tr\u00ean s\u1ebd t\u1ea1o nh\u1eefng m\u1ea3ng con bao g\u1ed3m: <\/p>\n<p>{TRUE} \u2013 {FALSE;TRUE} \u2013 {FALSE;FALSE;TRUE} \u2013 {FALSE;TRUE;FALSE;TRUE} \u2013 {TRUE;FALSE;FALSE;FALSE;TRUE}<\/p>\n<p>V\u00e0 h\u00e0m SUM k\u1ebft h\u1ee3p ph\u00e9p bi\u1ebfn \u0111\u1ed5i \u2014 s\u1ebd bi\u1ebfn TRUE th\u00e0nh 1, FALSE th\u00e0nh 0. C\u1ed9ng TRUE v\u1edbi FALSE ta s\u1ebd c\u00f3 1;1;1;2;2.<\/p>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Ung_dung\"><\/span>\u1ee8ng d\u1ee5ng<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>M\u1ed9t trong nh\u1eefng \u1ee9ng d\u1ee5ng \u1edf \u0111\u00e2y l\u00e0 k\u1ebft h\u1ee3p v\u1edbi h\u00e0m PIVOTBY. Trong Excel ta c\u00f3 h\u00e0m PIVOTBY gi\u00fap t\u1ea1o ra b\u1ea3ng t\u01b0\u01a1ng t\u1ef1 Pivot Table \u0111\u1ec3 t\u1ed5ng h\u1ee3p v\u00e0 ph\u00e2n t\u00edch d\u1eef li\u1ec7u. Trong Pivot Table, t\u00ednh n\u0103ng Count Distinct (\u0111\u1ebfm kh\u00f4ng tr\u00f9ng l\u1eb7p) ch\u1ec9 kh\u1ea3 d\u1ee5ng n\u1ebfu b\u1ea1n \u0111\u01b0a d\u1eef li\u1ec7u v\u00e0o trong Data Model. V\u1edbi PIVOTBY k\u1ebft h\u1ee3p v\u1edbi h\u00e0m MAP tr\u00ean, ta c\u00f3 th\u1ec3 tr\u1ef1c ti\u1ebfp t\u1ea1o ra m\u1ed9t ph\u01b0\u01a1ng th\u1ee9c t\u00ednh to\u00e1n Distinct Count, v\u00ed d\u1ee5:<\/p>\n<pre class=\"wp-block-code\"><code>=PIVOTBY(h\u00e0ng;c\u1ed9t;gi\u00e1 tr\u1ecb;LAMBDA(x;LET(arr;VSTACK(x);filter_arr;MAP(SEQUENCE(COUNTA(arr));LAMBDA(i;SUM(--(INDEX(arr; SEQUENCE(i)) = INDEX(arr; i)))));COUNTA(FILTER(arr;filter_arr = 1 )))))<\/code><\/pre>\n<p>Logic \u1edf \u0111\u00e2y l\u00e0 ta s\u1ebd t\u1ea1o m\u1ed9t b\u1ea3ng \u0111\u1ebfm t\u0103ng ti\u1ebfn, sau \u0111\u00f3 ch\u1ec9 gi\u1eef l\u1ea1i nh\u1eefng gi\u00e1 tr\u1ecb = 1 v\u1edbi h\u00e0m FILTER. B\u1eb1ng c\u00e1ch n\u00e0y, ta s\u1ebd \u0111\u1ea3m b\u1ea3o \u0111\u01b0\u1ee3c nh\u1eefng gi\u00e1 tr\u1ecb thu v\u1ec1 lu\u00f4n l\u00e0 gi\u00e1 tr\u1ecb \u0111\u1ea7u ti\u00ean.<\/p>\n<p><em>Ngo\u00e0i ra, m\u1ed9t c\u00e1ch kh\u00e1c \u0111\u1ec3 \u0111\u1ebfm gi\u00e1 tr\u1ecb kh\u00f4ng tr\u00f9ng l\u1eb7p l\u00e0 d\u00f9ng h\u00e0m UNIQUE:<\/em><\/p>\n<pre class=\"wp-block-code\"><code>=PIVOTBY(h\u00e0ng;c\u1ed9t;gi\u00e1 tr\u1ecb;LAMBDA(x;COUNTA(UNIQUE(HSTACK(x)))))<\/code><\/pre>\n<\/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\/2025\/02\/ex101_92019-nho-1.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":12695,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-12694","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\/12694","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=12694"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/12694\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/12695"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=12694"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=12694"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=12694"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}