{"id":6159,"date":"2024-12-21T14:05:12","date_gmt":"2024-12-21T14:05:12","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/xac-dinh-gia-tri-dac-biet-trong-mang-hay-trong-mot-dai-o\/"},"modified":"2024-12-21T14:05:12","modified_gmt":"2024-12-21T14:05:12","slug":"xac-dinh-gia-tri-dac-biet-trong-mang-hay-trong-mot-dai-o","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/xac-dinh-gia-tri-dac-biet-trong-mang-hay-trong-mot-dai-o\/","title":{"rendered":"X\u00c1C \u0110\u1ecaNH GI\u00c1 TR\u1eca \u0110\u1eb6C BI\u1ec6T TRONG M\u1ea2NG HAY TRONG M\u1ed8T D\u1ea2I \u00d4"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>B\u1ea1n \u0111\u00e3 bao gi\u1edd ch\u1ec9 thao t\u00e1c v\u1edbi d\u1eef li\u1ec7u \u0111\u1eb7c bi\u1ec7t trong m\u1ed9t d\u1ea3i \u00f4? N\u1ebfu d\u1eef li\u1ec7u c\u1ee7a b\u1ea1n c\u00f3 d\u1ea1ng m\u1ed9t c\u01a1 s\u1edf d\u1eef li\u1ec7u, th\u00ec b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng l\u1ec7nh Advanced Filter \u0111\u1ec3 xu\u1ea5t c\u00e1c d\u1eef li\u1ec7u \u0111\u1eb7c bi\u1ec7t t\u1eeb m\u1ed9t c\u1ed9t. Nh\u01b0ng n\u1ebfu d\u1eef li\u1ec7u c\u1ee7a b\u1ea1n tr\u1ea3i d\u00e0i tr\u00ean nhi\u1ec1u c\u1ed9t, Advanced Filter s\u1ebd kh\u00f4ng ch\u1ea1y. V\u00e0 Advanced Filter s\u1ebd ch\u1eb3ng gi\u00fap \u00edch g\u00ec n\u1ebfu d\u1eef li\u1ec7u c\u1ee7a b\u1ea1n n\u1eb1m trong m\u1ea3ng VBA.<\/p>\n<p>Trong v\u0103n b\u1ea3n n\u00e0y, t\u00f4i tr\u00ecnh b\u00e0y m\u1ed9t t\u00ednh n\u0103ng VBA \u2013 t\u00ednh n\u0103ng n\u00e0y ch\u1ea5p nh\u1eadn ho\u1eb7c \u0111\u1ed1i t\u01b0\u1ee3ng l\u00e0 m\u1ed9t \u00f4 hay d\u1ea3i \u00f4 trong Excel ho\u1eb7c m\u1ea3ng VBA. T\u00ednh n\u0103ng tr\u1ea3 v\u1ec1 ho\u1eb7c:<\/p>\n<ul>\n<li>M\u1ed9t m\u1ea3ng kh\u00e1c ch\u1ec9 g\u1ed3m c\u00e1c d\u1eef li\u1ec7u \u0111\u1eb7c bi\u1ec7t trong m\u1ea3ng hay d\u1ea3i \u00f4 \u0111\u01b0\u1ee3c nh\u1ea5p v\u00e0o (hay)<\/li>\n<li>M\u1ed9t gi\u00e1 tr\u1ecb duy nh\u1ea5t: s\u1ed1 d\u1eef li\u1ec7u \u0111\u1eb7c bi\u1ec7t trong m\u1ea3ng hay d\u1ea3i \u00f4 \u0111\u01b0\u1ee3c nh\u1eadp v\u00e0o.<\/li>\n<\/ul>\n<p>\u0110\u00e2y l\u00e0 c\u1ea5u tr\u00fac cho t\u00ednh n\u0103ng Uniqueltems (n\u00f3 \u0111\u01b0\u1ee3c li\u1ec7t k\u00ea \u1edf cu\u1ed1i v\u0103n b\u1ea3n n\u00e0y):<\/p>\n<p>UniqueItems(ArrayIn, Count)<\/p>\n<ul>\n<li>ArrayIn: \u0111\u1ed1i t\u01b0\u1ee3ng l\u00e0 m\u1ed9t v\u00f9ng \u00f4, hay m\u1ed9t m\u1ea3ng<\/li>\n<li>Count: (Kh\u00f4ng b\u1eaft bu\u1ed9c) N\u1ebfu l\u00e0 True hay b\u1ecf tr\u1ed1ng, t\u00ednh n\u0103ng s\u1ebd tr\u1ea3 v\u1ec1 m\u1ed9t gi\u00e1 tr\u1ecb duy nh\u1ea5t \u2013 s\u1ed1 d\u1eef li\u1ec7u \u0111\u1eb7c bi\u1ec7t trong ArrayIn. N\u1ebfu l\u00e0 False, th\u00ec t\u00ednh n\u0103ng s\u1ebd tr\u1ea3 v\u1ec1 m\u1ed9t m\u1ea3ng g\u1ed3m c\u00e1c d\u1eef li\u1ec7u \u0111\u1eb7c bi\u1ec7t trong ArrayIn.<\/li>\n<\/ul>\n<p>V\u00cd D\u1ee4 1<\/p>\n<p>Th\u1ee7 t\u1ee5c con d\u01b0\u1edbi \u0111\u00e2y cho th\u1ea5y Uniqueltems. \u0110o\u1ea1n ch\u01b0\u01a1ng tr\u00ecnh t\u1ea1o ra 100 s\u1ed1 nguy\u00ean ng\u1eabu nhi\u00ean v\u00e0 l\u01b0u ch\u00fang trong m\u1ed9t m\u1ea3ng. M\u1ea3ng n\u00e0y sau \u0111\u00f3 \u0111\u01b0\u1ee3c truy\u1ec1n cho t\u00ednh n\u0103ng Uniqueltems v\u00e0 m\u1ed9t h\u1ed9p tin nh\u1eafn hi\u1ec3n th\u1ecb s\u1ed1 s\u1ed1 nguy\u00ean \u0111\u1eb7c bi\u1ec7t trong m\u1ea3ng. Con s\u1ed1 c\u00f3 th\u1ec3 kh\u00e1c nhau m\u1ed7i khi b\u1ea1n ch\u1ea1y \u0111o\u1ea1n ch\u01b0\u01a1ng tr\u00ecnh.<\/p>\n<pre>Sub Test1()\r\n    Dim z(1 To 100)\r\n    For i = 1 To 100\r\n        z(i) = Int(Rnd() * 100)\r\n    Next i\r\n    MsgBox UniqueItems(z, True)\r\nEnd Sub\r\nV\u00cd D\u1ee4 2\r\nTh\u1ee7 t\u1ee5c con d\u01b0\u1edbi \u0111\u00e2y \u0111\u1ebfm c\u00e1c d\u1eef li\u1ec7u chung trong hai trang t\u00ednh. N\u00f3 t\u1ea1o ra hai m\u1ea3ng. M\u1ea3ng 1 \r\ng\u1ed3m c\u00e1c d\u1eef li\u1ec7u \u0111\u1eb7c bi\u1ec7t trong d\u1ea3i \u00f4 A1:A16; M\u1ea3ng 2 g\u1ed3m c\u00e1c d\u1eef li\u1ec7u \u0111\u1eb7c bi\u1ec7t trong d\u1ea3i \u00f4 \r\nB1:B16. M\u1ed9t v\u00f2ng l\u1eb7p \u0111\u01b0\u1ee3c l\u1ed3ng v\u00e0o \u0111\u1ebfm s\u1ed1 d\u1eef li\u1ec7u c\u00f3 trong hai d\u1ea3i \u00f4.<\/pre>\n<pre>Sub Test2()\r\n    Set Range1 = Sheets(\"Sheet1\").Range(\"A1:A16\")\r\n    Set Range2 = Sheets(\"Sheet1\").Range(\"B1:B16\")\r\n    Array1 = UniqueItems(Range1, False)\r\n    Array2 = UniqueItems(Range2, False)\r\n    CommonCount = 0\r\n    For i = LBound(Array1) To UBound(Array1)\r\n        For j = LBound(Array2) To UBound(Array2)\r\n            If Array1(i) = Array2(j) Then _\r\n              CommonCount = CommonCount + 1\r\n        Next j\r\n    Next i\r\n    MsgBox CommonCount\r\nEnd Sub\r\nV\u00cd D\u1ee4 3\r\nT\u00ednh n\u0103ng Uniqueltems c\u0169ng c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c d\u00f9ng trong c\u00f4ng th\u1ee9c trang t\u00ednh. C\u00f4ng th\u1ee9c d\u01b0\u1edbi \u0111\u00e2y\r\ntr\u1ea3 v\u1ec1 s\u1ed1 d\u1eef li\u1ec7u \u0111\u1eb7c bi\u1ec7t trong m\u1ed9t d\u1ea3i \u00f4:<\/pre>\n<pre>=UniqueItems(A1:D21)\r\nV\u00cd D\u1ee4 4\r\n\u0110\u1ec3 hi\u1ec3n th\u1ecb c\u00e1c d\u1eef li\u1ec7u \u0111\u1eb7c bi\u1ec7t trong m\u1ed9t d\u1ea3i \u00f4, b\u1ea1n ph\u1ea3i nh\u1eadp c\u00f4ng th\u1ee9c m\u1ea3ng v\u00e0o d\u1ea3i \u00f4 (s\u1eed\r\nd\u1ee5ng Ctrl+Shift+Enter). K\u1ebft qu\u1ea3 c\u1ee7a t\u00ednh n\u0103ng Uniqueltems l\u00e0 m\u1ed9t m\u1ea3ng ngang. N\u1ebfu b\u1ea1n mu\u1ed1n hi\u1ec3n\r\nc\u00e1c d\u1eef li\u1ec7u \u0111\u1eb7c bi\u1ec7t trong m\u1ed9t c\u1ed9t, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng t\u00ednh n\u0103ng TRANSPOSE. C\u00f4ng th\u1ee9c d\u01b0\u1edbi \u0111\u00e2y\r\n(\u0111\u01b0\u1ee3c nh\u1eadp gi\u1ed1ng nh\u01b0 khi nh\u1eadp c\u00f4ng th\u1ee9c m\u1ea3ng v\u00e0o m\u1ed9t d\u1ea3i \u00f4 theo chi\u1ec1u d\u1ecdc) tr\u1ea3 v\u1ec1 c\u00e1c d\u1eef li\u1ec7u \r\n\u0111\u1eb7c bi\u1ec7t trong d\u1ea3i \u00f4 A1:D21.<\/pre>\n<pre>=TRANSPOSE(UniqueItems(A1:D21,FALSE))\r\nM\u00c3<\/pre>\n<pre>Option Base 1\r\n\r\nFunction UniqueItems(ArrayIn, Optional Count As Variant) As Variant\r\n<span style=\"color: #0000ff\">'   Accepts an array or range as input\r\n'   If Count = True or is missing, the function returns the number of unique elements\r\n'   If Count = False, the function returns a variant array of unique elements<\/span>\r\n    Dim Unique() As Variant ' array that holds the unique items\r\n    Dim Element As Variant\r\n    Dim i As Integer\r\n    Dim FoundMatch As Boolean\r\n<span style=\"color: #0000ff\">'   If 2nd argument is missing, assign default value<\/span>\r\n    If IsMissing(Count) Then Count = True\r\n<span style=\"color: #0000ff\">'   Counter for number of unique elements<\/span>\r\n    NumUnique = 0\r\n<span style=\"color: #0000ff\">'   Loop thru the input array<\/span>\r\n    For Each Element In ArrayIn\r\n        FoundMatch = False\r\n<span style=\"color: #0000ff\">'       Has item been added yet?<\/span>\r\n        For i = 1 To NumUnique\r\n            If Element = Unique(i) Then\r\n                FoundMatch = True\r\n                Exit For <span style=\"color: #0000ff\">'(exit loop)<\/span>\r\n            End If\r\n        Next i\r\nAddItem:\r\n<span style=\"color: #0000ff\">'       If not in list, add the item to unique list<\/span>\r\n        If Not FoundMatch And Not IsEmpty(Element) Then\r\n            NumUnique = NumUnique + 1\r\n            ReDim Preserve Unique(NumUnique)\r\n            Unique(NumUnique) = Element\r\n        End If\r\n    Next Element\r\n<span style=\"color: #0000ff\">'   Assign a value to the function<\/span>\r\n    If Count Then UniqueItems = NumUnique Else UniqueItems = Unique\r\nEnd Function\r\n(G\u1eedi l\u1eddi c\u1ea3m \u01a1n \u0111\u1ebfn Peter Atherton v\u00ec \u0111\u00e3 \u0111\u1ec1 ngh\u1ecb ph\u01b0\u01a1ng ph\u00e1p n\u00e0y nh\u1eb1m tr\u00e1nh chuy\u1ec3n gi\u00e1 tr\u1ecb \r\nr\u1ed7ng th\u00e0nh gi\u00e1 tr\u1ecb 0)<\/pre>\n<pre><\/pre>\n<pre><\/pre>\n<p>\u00a0<\/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=\"https:\/\/blog.hocexcel.online\/wp-content\/uploads\/2019\/09\/EX101_92019-nho.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":6160,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-6159","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\/6159","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=6159"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/6159\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/6160"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=6159"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=6159"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=6159"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}