{"id":534,"date":"2024-12-02T02:24:11","date_gmt":"2024-12-02T02:24:11","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/cach-lay-danh-sach-ngau-nhien-khong-trung-lap-trong-excel\/"},"modified":"2024-12-02T02:24:11","modified_gmt":"2024-12-02T02:24:11","slug":"cach-lay-danh-sach-ngau-nhien-khong-trung-lap-trong-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/cach-lay-danh-sach-ngau-nhien-khong-trung-lap-trong-excel\/","title":{"rendered":"C\u00e1ch l\u1ea5y danh s\u00e1ch ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Trong qu\u00e1 tr\u00ecnh h\u1ed7 tr\u1ee3 h\u1ecdc vi\u00ean v\u00e0 m\u1ed9t s\u1ed1 kh\u00e1ch h\u00e0ng, m\u1ed9t v\u1ea5n \u0111\u1ec1 m\u00e0 c\u00e1c b\u1ea1n r\u1ea5t hay h\u1ecfi \u0111\u00f3 l\u00e0 l\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 c\u00f3 th\u1ec3 l\u1ea5y \u0111\u01b0\u1ee3c m\u1ed9t danh s\u00e1ch kh\u00f4ng tr\u00f9ng l\u1eb7p ng\u1eabu nhi\u00ean trong Excel. Trong b\u00e0i vi\u1ebft l\u1ea7n n\u00e0y, ch\u00fang ta s\u1ebd c\u00f9ng nhau \u0111i t\u00ecm hi\u1ec3u gi\u1ea3i ph\u00e1p cho v\u1ea5n \u0111\u1ec1 n\u00e0y c\u00f3 th\u1ec3 \u00e1p d\u1ee5ng cho t\u1ea5t c\u1ea3 c\u00e1c phi\u00ean b\u1ea3n Excel 365, Excel 2019, Excel 2016, Excel 2013 v\u00e0 c\u00e1c phi\u00ean b\u1ea3n tr\u01b0\u1edbc \u0111\u00f3.<\/p>\n<p>B\u00e0i vi\u1ebft n\u00e0y c\u00f3 k\u00e8m theo m\u1ed9t video h\u01b0\u1edbng d\u1eabn \u1edf cu\u1ed1i ph\u1ea7n cu\u1ed1i.<\/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-674d1a4af233e\" 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\/cach-lay-danh-sach-ngau-nhien-khong-trung-lap-trong-excel.html#Cach_lay_danh_sach_ngau_nhien_khong_trung_lap_trong_Excel\" title=\"C\u00e1ch l\u1ea5y danh s\u00e1ch ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel\">C\u00e1ch l\u1ea5y danh s\u00e1ch ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel<\/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\/cach-lay-danh-sach-ngau-nhien-khong-trung-lap-trong-excel.html#Cach_lay_nhieu_dong_ngau_nhien_khong_trung_lap_trong_Excel\" title=\"C\u00e1ch l\u1ea5y nhi\u1ec1u d\u00f2ng ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel\">C\u00e1ch l\u1ea5y nhi\u1ec1u d\u00f2ng ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel<\/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\/cach-lay-danh-sach-ngau-nhien-khong-trung-lap-trong-excel.html#Cach_lay_gia_tri_ngau_nhien_khong_trung_lap_trong_Excel_2010_%E2%80%93_2019\" title=\"C\u00e1ch l\u1ea5y gi\u00e1 tr\u1ecb ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel 2010 \u2013 2019\">C\u00e1ch l\u1ea5y gi\u00e1 tr\u1ecb ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel 2010 \u2013 2019<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Cach_lay_danh_sach_ngau_nhien_khong_trung_lap_trong_Excel\"><\/span>C\u00e1ch l\u1ea5y danh s\u00e1ch ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span class=\"note-label\">L\u01b0u \u00fd<\/span> C\u00e1ch n\u00e0y ch\u1ec9 ho\u1ea1t \u0111\u1ed9ng tr\u00ean phi\u00ean b\u1ea3n Excel 365 c\u00f3 h\u1ed7 tr\u1ee3 c\u00e1c h\u00e0m m\u1ea3ng \u0111\u1ed9ng<\/p>\n<p>\u0110\u1ec3 t\u1ea1o ra m\u1ed9t danh s\u00e1ch ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel, th\u00ec c\u00f4ng th\u1ee9c chung c\u1ee7a ch\u00fang ta nh\u01b0 sau<\/p>\n<p><code class=\"excel-formula-block\">INDEX(SORTBY(<em>c\u1ed9t_d\u1eef_li\u1ec7u<\/em>, RANDARRAY(ROWS(<em>c\u1ed9t_d\u1eef_li\u1ec7u<\/em>))), SEQUENCE(<em>n<\/em>))<\/code><\/p>\n<p>Trong \u0111\u00f3:<\/p>\n<p>\u2013 <code><em>c\u1ed9t_d\u1eef_li\u1ec7u<\/em><\/code> l\u00e0 c\u1ed9t d\u1eef li\u1ec7u ngu\u1ed3n<br \/>\n\u2013 <code><em>n<\/em><\/code> l\u00e0 s\u1ed1 l\u01b0\u1ee3ng \u00f4 d\u1eef li\u1ec7u b\u1ea1n c\u1ea7n l\u1ea5y<\/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-24.gif\" title=\"ex101_92019-nho-24\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>H\u00e3y c\u00f9ng nhau \u0111i th\u1eed c\u00f4ng th\u1ee9c n\u00e0y cho tr\u01b0\u1eddng h\u1ee3p sau \u0111\u00e2y:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-36418\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/lay-gia-tri-ngau-nhien-khong-trung-trong-excel-01-300x168-1.png\" alt=\"C\u00e1ch l\u1ea5y d\u1eef li\u1ec7u ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel\" width=\"441\" height=\"247\" title=\"lay-gia-tri-ngau-nhien-khong-trung-trong-excel-01-300x168-1\"><\/p>\n<p>Trong h\u00ecnh minh h\u1ecda tr\u00ean, ch\u00fang ta s\u1ebd \u0111i l\u1ea5y 5 gi\u00e1 tr\u1ecb ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p c\u1ee7a c\u00e1c t\u00ean trong c\u1ed9t A, ch\u00fang ta s\u1ebd \u00e1p d\u1ee5ng c\u00f4ng th\u1ee9c nh\u01b0 sau:<\/p>\n<p><code class=\"excel-formula-block\">=INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(5))<\/code><\/p>\n<p>\u0110\u1ec3 ti\u1ec7n d\u1ee5ng cho vi\u1ec7c thay \u0111\u1ed5i s\u1ed1 l\u01b0\u1ee3ng gi\u00e1 tr\u1ecb ng\u1eabu nhi\u00ean l\u1ea5y ra, ch\u00fang ta c\u00f3 th\u1ec3 thay s\u1ed1 5 trong c\u00f4ng th\u1ee9c tr\u00ean b\u1eb1ng tham chi\u1ebfu t\u1edbi \u00f4 C2, v\u00e0 c\u00f4ng th\u1ee9c b\u00e2y gi\u1edd c\u1ee7a ch\u00fang ta tr\u1edf th\u00e0nh:<\/p>\n<p><code class=\"excel-formula-block\">=INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(C2))<\/code><\/p>\n<p><strong>C\u00f4ng th\u1ee9c n\u00e0y ho\u1ea1t \u0111\u00f4ng nh\u01b0 th\u1ebf n\u00e0o?<\/strong><\/p>\n<p>\u0110\u1ec3 hi\u1ec3u \u0111\u01b0\u1ee3c c\u00f4ng th\u1ee9c tr\u00ean ho\u1ea1t \u0111\u1ed9ng th\u1ebf n\u00e0o, ch\u00fang ta h\u00e3y \u0111i t\u1eeb trong ra ngo\u00e0i nh\u01b0 c\u00e1ch l\u00e0m v\u1edbi nh\u1eefng c\u00f4ng th\u1ee9c Excel kh\u00e1c:<\/p>\n<p>\u2013 <a href=\"http:\/\/blog.ezworkapp.com\/ham-mang-dong-trong-excel-dynamic-array-formulas-phan-1-ham-co-kha-nang-mo-rong-vung-du-lieu.html\" target=\"_blank\" rel=\"noopener\">H\u00e0m RANDARRAY<\/a> s\u1ebd t\u1ea1o ra m\u1ed9t m\u1ea3ng c\u00e1c s\u1ed1 ng\u1eabu nhi\u00ean d\u1ef1a v\u00e0o gi\u00e1 tr\u1ecb tr\u1ea3 v\u1ec1 c\u1ee7a h\u00e0m ROWS()<br \/>\n\u2013 <a href=\"http:\/\/blog.ezworkapp.com\/xao-tron-du-lieu-ngau-nhien-sortby-randarray.html\" target=\"_blank\" rel=\"noopener\">H\u00e0m SORTBY<\/a> s\u1ebd s\u1eafp x\u1ebfp c\u00e1c gi\u00e1 tr\u1ecb ng\u1eabu nhi\u00ean \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1 n\u00e0y<br \/>\n\u2013 <a href=\"http:\/\/blog.ezworkapp.com\/6-tinh-nang-hieu-qua-nhat-cua-ham-index-trong-excel.html\" target=\"_blank\" rel=\"noopener\">H\u00e0m INDEX<\/a> s\u1ebd l\u1ea5y s\u1ed1 l\u01b0\u1ee3ng c\u00e1c gi\u00e1 tr\u1ecb ng\u1eabu nhi\u00ean do ch\u00fang ta ch\u1ec9 \u0111\u1ecbnh b\u1edfi h\u00e0m <a href=\"http:\/\/blog.ezworkapp.com\/ham-sequence-co-ban-trong-excel.html\">SEQUENCE<\/a><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_lay_nhieu_dong_ngau_nhien_khong_trung_lap_trong_Excel\"><\/span>C\u00e1ch l\u1ea5y nhi\u1ec1u d\u00f2ng ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span class=\"note-label\">L\u01b0u \u00fd<\/span> C\u00e1ch l\u00e0m n\u00e0y ch\u1ec9 \u00e1p d\u1ee5ng cho phi\u00ean b\u1ea3n Excel 365<\/p>\n<p>\u0110\u1ec3 l\u1ea5y ra nhi\u1ec1u d\u00f2ng ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel, ch\u00fang ta c\u00f3 th\u1ec3 l\u1ea5y theo m\u1ed9t c\u00f4ng th\u1ee9c chung nh\u01b0 sau:<\/p>\n<p><code class=\"excel-formula-block\">=INDEX(SORTBY(<em>data<\/em>, RANDARRAY(ROWS(<em>data<\/em>))), SEQUENCE(<em>n<\/em>), <em>{1,2,\u2026}<\/em>)<\/code><\/p>\n<p>Trong \u0111\u00f3:<br \/>\n\u2013 <code><em>n<\/em><\/code> l\u00e0 s\u1ed1 d\u00f2ng ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p c\u1ea7n l\u1ea5y ra<br \/>\n\u2013 <code><em>{1,2,\u2026}<\/em><\/code> l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 c\u00e1c c\u1ed9t c\u1ea7n l\u1ea5y d\u1eef li\u1ec7u v\u1ec1<\/p>\n<p>Ch\u00fang ta h\u00e3y c\u00f9ng \u0111i \u00e1p d\u1ee5ng c\u00f4ng th\u1ee9c tr\u00ean cho b\u1ea3ng t\u00ednh trong v\u00ed d\u1ee5 sau \u0111\u00e2y:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-36419\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/lay-gia-tri-ngau-nhien-khong-trung-trong-excel-02-300x148-1.png\" alt=\"L\u1ea5y d\u00f2ng ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel\" width=\"499\" height=\"246\" title=\"lay-gia-tri-ngau-nhien-khong-trung-trong-excel-02-300x148-1\"><\/p>\n<p>\u00c1p d\u1ee5ng c\u00f4ng th\u1ee9c t\u1ed5ng qu\u00e1t c\u1ee7a ch\u00fang ta v\u00e0 thay c\u00e1c v\u00f9ng gi\u00e1 tr\u1ecb, ch\u00fang ta c\u00f3 c\u00f4ng th\u1ee9c sau \u0111\u00e2y:<\/p>\n<p><code class=\"excel-formula-block\">=INDEX(SORTBY(A2:C10, RANDARRAY(ROWS(A2:C10))), SEQUENCE(F1), {1,2,3})<\/code><\/p>\n<p><strong>C\u00e1ch c\u00f4ng th\u1ee9c n\u00e0y ho\u1ea1t \u0111\u1ed9ng nh\u01b0 th\u1ebf n\u00e0o?<\/strong><\/p>\n<p>C\u00f4ng th\u1ee9c n\u00e0y c\u00f3 logic gi\u1ed1ng nh\u01b0 c\u00f4ng th\u1ee9c \u1edf trong ph\u1ea7n tr\u01b0\u1edbc c\u1ee7a b\u00e0i vi\u1ebft, ch\u1ec9 kh\u00e1c m\u1ed9t chi ti\u1ebft nh\u1ecf l\u00e0 ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng h\u00e0m INDEX v\u1edbi m\u1ed9t b\u1ed9 tham s\u1ed1 kh\u00e1c, tham s\u1ed1 row_num s\u1ebd nh\u1eadn gi\u00e1 tr\u1ecb t\u1eeb h\u00e0m SEQUENCE v\u00e0 column_num s\u1ebd nh\u1eadn ra tr\u1ecb t\u1eeb m\u1ea3ng th\u1ee9 t\u1ef1 c\u1ee7a c\u00e1c c\u1ed9t.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_lay_gia_tri_ngau_nhien_khong_trung_lap_trong_Excel_2010_%E2%80%93_2019\"><\/span>C\u00e1ch l\u1ea5y gi\u00e1 tr\u1ecb ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel 2010 \u2013 2019<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>B\u1edfi v\u00ec ch\u1ec9 Excel 365 m\u1edbi c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng nh\u1eefng <a href=\"http:\/\/blog.ezworkapp.com\/microsoft-excel-2018-dynamic-array.html\" target=\"_blank\" rel=\"noopener\">h\u00e0m m\u1ea3ng \u0111\u1ed9ng<\/a>, n\u00ean ki\u1ebfn th\u1ee9c trong hai ph\u1ea7n tr\u00ean s\u1ebd kh\u00f4ng \u00e1p d\u1ee5ng \u0111\u01b0\u1ee3c cho c\u00e1c phi\u00ean b\u1ea3n Excel t\u1eeb Excel 2019 tr\u1edf v\u1ec1 tr\u01b0\u1edbc.<\/p>\n<p>Ch\u00fang ta v\u1eabn c\u00f3 th\u1ec3 gi\u1ea3i quy\u1ebft v\u1ea5n \u0111\u1ec1 n\u00e0y nh\u01b0 sau:<\/p>\n<ol>\n<li>Trong c\u1ed9t B c\u1ee7a v\u00ed d\u1ee5 minh h\u1ecda, ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng h\u00e0m RAND() cho c\u00e1c \u00f4 t\u1eeb B2 t\u1edbi B10<\/li>\n<li>Tr\u00edch xu\u1ea5t ra gi\u00e1 tr\u1ecb ng\u1eabu nhi\u00ean \u0111\u1ea7u ti\u00ean v\u1edbi c\u00f4ng th\u1ee9c sau \u0111\u00e2y trong \u00f4 E2:<br \/>\n<code class=\"excel-formula-block\">=INDEX($A$2:$A$10, RANK.EQ(B2, $B$2:$B$10) + COUNTIF($B$2:B2, B2) - 1)<\/code><\/li>\n<li>Copy c\u00f4ng th\u1ee9c trong E2 cho s\u1ed1 d\u00f2ng t\u01b0\u01a1ng \u1ee9ng v\u1edbi s\u1ed1 gi\u00e1 tr\u1ecb ng\u1eabu nhi\u00ean b\u1ea1n mu\u1ed1n l\u1ea5y ra t\u1eeb danh s\u00e1ch. N\u1ebfu b\u1ea1n mu\u1ed1n 3 gi\u00e1 tr\u1ecb ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p, b\u1ea1n k\u00e9o c\u00f4ng th\u1ee9c t\u1eeb E2 cho t\u1edbi \u00f4 E4. N\u1ebfu mu\u1ed1n 4 gi\u00e1 tr\u1ecb ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p, ch\u00fang ta k\u00e9o c\u00f4ng th\u1ee9c t\u1eeb E2 t\u1edbi E5<\/li>\n<\/ol>\n<p>V\u00e0 k\u1ebft qu\u1ea3 nh\u1eadn \u0111\u01b0\u1ee3c s\u1ebd l\u00e0:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-36420\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/lay-gia-tri-ngau-nhien-khong-trung-trong-excel-03-300x160-1.png\" alt=\"L\u1ea5y d\u1eef li\u1ec7u ng\u1eabu nhi\u00ean s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c Excel\" width=\"521\" height=\"278\" title=\"lay-gia-tri-ngau-nhien-khong-trung-trong-excel-03-300x160-1\"><\/p>\n<p><strong>C\u00f4ng th\u1ee9c n\u00e0y ho\u1ea1t \u0111\u1ed9ng th\u1ebf n\u00e0o?<\/strong><\/p>\n<p>\u0110\u1ea7u ti\u00ean, h\u00e0m <code>RAND()<\/code> trong c\u1ed9t B s\u1ebd cho ch\u00fang ta m\u1ed9t danh s\u00e1ch c\u00e1c s\u1ed1 ng\u1eabu nhi\u00ean, tuy nhi\u00ean, nh\u01b0 b\u1ea1n th\u1ea5y \u1edf trong \u1ea3nh v\u00ed d\u1ee5 tr\u00ean, th\u00ec danh s\u00e1ch n\u00e0y m\u1eb7c d\u00f9 \u0111\u01b0\u1ee3c t\u1ea1o ra m\u1ed9t c\u00e1ch ng\u1eabu nhi\u00ean b\u1eb1ng h\u00e0m <code>RAND()<\/code>, nh\u01b0ng v\u1eabn c\u00f3 kh\u1ea3 n\u0103ng c\u00f3 s\u1ed1 tr\u00f9ng nhau. Ch\u00fang ta s\u1ebd \u0111i gi\u1ea3i th\u00edch c\u00e1ch ch\u00fang ta k\u1ebft h\u1ee3p h\u00e0m \u0111\u1ec3 c\u00f3 th\u1ec3 gi\u1ea3i quy\u1ebft v\u1ea5n \u0111\u1ec1 n\u00e0y.<\/p>\n<p>H\u00e0m RANK.EQ s\u1ebd t\u00ednh to\u00e1n th\u1ee9 h\u1ea1ng (rank) c\u1ee7a c\u00e1c con s\u1ed1 trong c\u1ed9t B \u1edf m\u1ed7i d\u00f2ng. V\u00ed d\u1ee5, trong \u00f4 E2, <code>RANK.EQ(B2, $B$2:$B$10)<\/code> s\u1ebd s\u1eafp x\u1ebfp th\u1ee9 h\u1ea1ng c\u1ee7a gi\u00e1 tr\u1ecb trong \u00f4 B2, so s\u00e1nh v\u1edbi gi\u00e1 tr\u1ecb trong v\u00f9ng B2:B10. Khi c\u00f4ng th\u1ee9c n\u00e0y \u0111\u01b0\u1ee3c copy xu\u1ed1ng E3, b\u1edfi v\u00ec B2 \u0111\u01b0\u1ee3c tham chi\u1ebfu t\u01b0\u01a1ng \u0111\u1ed1i (kh\u00f4ng c\u00f3 k\u00fd t\u1ef1 $ trong \u0111\u1ecba ch\u1ec9 tham chi\u1ebfu), v\u1eady n\u00ean, B2 chuy\u1ec3n th\u00e0nh B3 v\u00e0 c\u00f4ng th\u1ee9c s\u1ebd tr\u1ea3 v\u1ec1 th\u1ee9 h\u1ea1ng c\u1ee7a B3, \u2026<\/p>\n<p>H\u00e0m COUNTIF s\u1ebd gi\u00fap b\u1ea1n \u0111\u1ebfm s\u1ed1 l\u1ea7n m\u1ed9t con s\u1ed1 xu\u1ea5t hi\u1ec7n trong v\u00f9ng d\u1eef li\u1ec7u. V\u00ed d\u1ee5, trong c\u00f4ng th\u1ee9c \u1edf \u00f4 E2, <code>COUNTIF($B$2:B2, B2)<\/code> s\u1ebd ki\u1ec3m tra xem c\u00f3 bao nhi\u00eau gi\u00e1 tr\u1ecb trong \u00f4 B2 trong v\u00f9ng $B$2:B2, t\u1ea5t nhi\u00ean nh\u00ecn v\u00e0o \u0111\u00e2y c\u00e1c b\u1ea1n s\u1ebd th\u1eafc m\u1eafc v\u00ec sao ch\u00fang ta l\u1ea1i vi\u1ebft nh\u01b0 v\u1eady, tr\u00f4ng c\u00f3 h\u01a1i bu\u1ed3n c\u01b0\u1eddi kh\u00f4ng? C\u00e2u tr\u1ea3 l\u1eddi l\u00e0 c\u00f3 v\u00e0 kh\u00f4ng, b\u1edfi v\u00ec v\u1edbi k\u1ef9 thu\u1eadt n\u00e0y, khi tham chi\u1ebfu \u00f4 thay \u0111\u1ed5i l\u00fac b\u1ea1n k\u00e9o c\u00f4ng th\u1ee9c xu\u1ed1ng ph\u00eda d\u01b0\u1edbi, th\u00ec v\u00f9ng \u0111\u1ebfm b\u1edfi h\u00e0m COUNTIF c\u0169ng thay \u0111\u1ed5i theo. Khi b\u1ea1n k\u00e9o c\u00f4ng th\u1ee9c xu\u1ed1ng t\u1edbi E5, th\u00ec c\u00f4ng th\u1ee9c <code>COUNTIF($B$2:B5, B5)<\/code> s\u1ebd \u0111\u1ebfm s\u1ed1 l\u1ea7n xu\u1ea5t hi\u1ec7n c\u1ee7a gi\u00e1 tr\u1ecb trong \u00f4 B5 trong v\u00f9ng B2:B5.<\/p>\n<p>V\u1edbi c\u00f4ng th\u1ee9c COUNTIF nh\u01b0 tr\u00ean, n\u1ebfu gi\u00e1 tr\u1ecb trong c\u1ed9t B ch\u1ec9 xu\u1ea5t hi\u1ec7n 1 l\u1ea7n, th\u00ec k\u1ebft qu\u1ea3 c\u1ee7a h\u00e0m s\u1ebd l\u00e0 1; ch\u00fang ta s\u1ebd tr\u1eeb \u0111i 1 \u0111\u01a1n v\u1ecb \u0111\u1ec3 gi\u1eef th\u1ee9 h\u1ea1ng \u0111\u01b0\u1ee3c t\u1ea1o ra b\u1edfi h\u00e0m RANK.EQ. N\u1ebfu h\u00e0m COUNTIF tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u00e0 2, t\u01b0\u01a1ng \u1ee9ng v\u1edbi vi\u1ec7c c\u00f3 2 k\u1ebft qu\u1ea3 tr\u00f9ng l\u1eb7p, th\u00ec ch\u00fang ta c\u0169ng tr\u1eeb \u0111i 1 \u0111\u1ec3 t\u0103ng k\u1ebft qu\u1ea3 th\u1ee9 h\u1ea1ng l\u00ean, \u0111\u1ec3 \u0111\u1ea3m b\u1ea3o kh\u00f4ng b\u1ecb tr\u00f9ng l\u1eb7p trong th\u1ee9 h\u1ea1ng.<\/p>\n<p>Trong v\u00ed d\u1ee5 tr\u00ean, v\u1edbi c\u00f4ng th\u1ee9c trong \u00f4 E2 th\u00ec h\u00e0m RANK.EQ s\u1ebd tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u00e0 1. V\u00ec \u0111\u00e2y l\u00e0 gi\u00e1 tr\u1ecb \u0111\u1ea7u ti\u00ean, h\u00e0m COUNTIF c\u0169ng s\u1ebd tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u00e0 1. RANK.EQ v\u00e0 COUNTIF s\u1ebd cho k\u1ebft qu\u1ea3 b\u1eb1ng 2. Ch\u00fang ta tr\u1eeb \u0111i 1 \u0111\u01a1n v\u1ecb, nh\u01b0 v\u1eady th\u1ee9 h\u1ea1ng s\u1ebd tr\u1edf v\u1ec1 1.<\/p>\n<p>B\u00e2y gi\u1edd ch\u00fang ta s\u1ebd xem, vi\u1ec7c g\u00ec s\u1ebd x\u1ea3y ra khi c\u00f3 xu\u1ea5t hi\u1ec7n 2 gi\u00e1 tr\u1ecb tr\u00f9ng l\u1eb7p b\u1edfi h\u00e0m RAND. V\u00ed d\u1ee5 trong \u00f4 B5, h\u00e0m RANK.EQ cho gi\u00e1 tr\u1ecb l\u00e0 1 trong khi COUNTIF s\u1ebd cho gi\u00e1 tr\u1ecb l\u00e0 2. T\u1ed5ng hai k\u1ebft qu\u1ea3 l\u00e0 3, ch\u00fang ta tr\u1eeb \u0111i 1 s\u1ebd \u0111\u01b0\u1ee3c 2, v\u00e0 2 c\u0169ng ch\u00ednh l\u00e0 th\u1ee9 h\u1ea1ng c\u1ee7a gi\u00e1 tr\u1ecb trong \u00f4 B5.<\/p>\n<p>Th\u1ee9 h\u1ea1ng \u0111\u01b0\u1ee3c t\u00ednh to\u00e1n ra b\u1edfi s\u1ef1 k\u1ebft h\u1ee3p gi\u1eefa h\u00e0m RANK.EQ v\u00e0 h\u00e0m COUNTIF s\u1ebd \u0111\u01b0\u1ee3c \u0111\u01b0a v\u00e0o tham s\u1ed1 row_num c\u1ee7a h\u00e0m INDEX. H\u00e0m INDEX s\u1ebd nh\u1eadn nhi\u1ec7m v\u1ee5 l\u1ea5y ra d\u00f2ng t\u01b0\u01a1ng \u1ee9ng. \u0110\u00e2y l\u00e0 l\u00fd do t\u1ea1i sao, ch\u00fang ta kh\u00f4ng th\u1ec3 c\u00f3 2 d\u00f2ng c\u00f3 c\u00f9ng th\u1ee9 h\u1ea1ng (c\u00f9ng Rank), b\u1edfi v\u00ec khi \u0111\u00f3 h\u00e0m INDEX s\u1ebd l\u1ea5y ra gi\u00e1 tr\u1ecb b\u1ecb tr\u00f9ng l\u1eb7p.<\/p>\n<p><strong>L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 Excel kh\u00f4ng thay \u0111\u1ed5i c\u00e1c gi\u00e1 tr\u1ecb ng\u1eabu nhi\u00ean<\/strong><\/p>\n<p>Trong Excel, t\u1ea5t c\u1ea3 c\u00e1c h\u00e0m d\u1eef li\u1ec7u ng\u1eabu nhi\u00ean nh\u01b0 RAND, RANDBETWEEN v\u00e0 RANDARRAY \u0111\u1ec1u s\u1ebd \u0111\u01b0\u1ee3c t\u00ednh to\u00e1n l\u1ea1i v\u1edbi m\u1ed7i s\u1ef1 thay \u0111\u1ed5i tr\u00ean Worksheet. Do v\u1eady, n\u1ebfu b\u1ea1n mu\u1ed1n Excel kh\u00f4ng ph\u1ea3i t\u00ednh to\u00e1n qu\u00e1 nhi\u1ec1u, g\u00e2y ch\u1eadm file, b\u1ea1n c\u00f3 th\u1ec3 chuy\u1ec3n d\u1eef li\u1ec7u sau khi t\u00ednh to\u00e1n th\u00e0nh d\u1ea1ng Value b\u1eb1ng c\u00e1ch copy to\u00e0n b\u1ed9 d\u1eef li\u1ec7u, sau \u0111\u00f3 ch\u1ecdn \u00f4 s\u1ebd \u0111\u1ed5 d\u1eef li\u1ec7u ra tr\u00ean file Excel, b\u1ea5m chu\u1ed9t h\u1ea3i, ch\u1ecdn <a href=\"http:\/\/blog.ezworkapp.com\/cong-tru-nhan-chia-vung-voi-cung-1-paste-special.html\" target=\"_blank\" rel=\"noopener\">Paste Special &gt; Values<\/a>.<\/p>\n<p>Hi v\u1ecdng v\u1edbi n\u1ed9i dung n\u00e0y, b\u1ea1n c\u00f3 th\u1ec3 gi\u1ea3i quy\u1ebft \u0111\u01b0\u1ee3c v\u1ea5n \u0111\u1ec1 l\u1ea5y ra danh s\u00e1ch ng\u1eabu nhi\u00ean kh\u00f4ng tr\u00f9ng l\u1eb7p trong Excel c\u1ee7a b\u1ea1n. N\u1ebfu b\u1ea1n ch\u01b0a hi\u1ec3u, h\u00e3y th\u1eed theo d\u00f5i video sau \u0111\u00e2y c\u1ee7a Thanh:<\/p>\n<div id=\"erdyt-674d1a4aee2dc\" data-id=\"IoQhhtG7zaE\" class=\"erd-youtube-responsive\" style=\"position:relative;clear:both;width:100%;max-width:88%;margin-left:auto;margin-right:auto\">\n<div style=\"padding-bottom:56.25%\">\n<div class=\"erd-ytplay\" id=\"erdytp-IoQhhtG7zaE-674d1a4aee2dc\" data-vid=\"IoQhhtG7zaE\" data-src=\"https:\/\/www.youtube.com\/embed\/IoQhhtG7zaE?loop=1&amp;autoplay=1&amp;rel=0\" data-allowfullscreen=\"true\"><img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/hqdefault-5.jpg\" alt=\"YouTube video\" title=\"hqdefault-5\"><\/div>\n<\/div>\n<\/div>\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-24.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-534","post","type-post","status-publish","format-standard","hentry","category-excel-nang-cao"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/534","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=534"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/534\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=534"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=534"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=534"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}