{"id":4707,"date":"2024-12-08T06:39:50","date_gmt":"2024-12-08T06:39:50","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/08\/data-validation-va-nhung-nguoi-ban-khong-dung-vba\/"},"modified":"2024-12-08T06:39:50","modified_gmt":"2024-12-08T06:39:50","slug":"data-validation-va-nhung-nguoi-ban-khong-dung-vba","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/08\/data-validation-va-nhung-nguoi-ban-khong-dung-vba\/","title":{"rendered":"Data Validation v\u00e0 nh\u1eefng ng\u01b0\u1eddi b\u1ea1n (kh\u00f4ng d\u00f9ng VBA)"},"content":{"rendered":"
\n

B\u00e0i to\u00e1n \u0111\u1eb7t ra, ch\u00fang ta mu\u1ed1n s\u1eed d\u1ee5ng Data Validation<\/a><\/strong>, \u0111\u1ec3 ch\u1ecdn nhanh t\u1eeb danh m\u1ee5c s\u1eb5n c\u00f3. Nh\u01b0ng d\u00e0i qu\u00e1, ch\u00fang ta c\u1ea7n th\u00eam t\u00ednh n\u0103ng g\u00f5 t\u1eeb \u0111\u1ec3 l\u1ecdc s\u1eb5n, v\u00e0 sau \u0111\u00f3 ch\u1ec9 vi\u1ec7c ch\u1ecdn l\u1ea1i s\u1ebd nhanh h\u01a1n. Cu\u1ed1i c\u00f9ng ta c\u00f3 k\u1ebft qu\u1ea3 nh\u01b0 trong v\u00ed d\u1ee5 b\u00ean d\u01b0\u1edbi:<\/p>\n

<\/a><\/p>\n

\u1ede \u0111\u00e2y, t\u1ea1i \u00f4 B2 ch\u00fang ta v\u1eadn d\u1ee5ng h\u00e0m COUNTIF \u0111\u1ec3 t\u00ecm ra nh\u1eefng danh m\u1ee5c c\u00f3 ch\u1ee9a t\u1eeb c\u1ea7n t\u00ecm, \u1edf \u0111\u00e2y c\u00e1c b\u1ea1n th\u1ea5y COUNTIF v\u1eadn d\u1ee5ng k\u1ebft h\u1ee3p v\u1edbi d\u1ea5u sao \u201c*\u201d (k\u00fd t\u1ef1 b\u1ea5t k\u1ef3).<\/p>\n

Ngo\u00e0i ra, t\u1ea1i \u00f4 C2, m\u00ecnh v\u1eadn d\u1ee5ng h\u00e0m OFFSET<\/a>\u00a0<\/strong>(\u00f4 b\u1eaft \u0111\u1ea7u, d\u00f2ng, c\u1ed9t, s\u1ed1 d\u00f2ng, s\u1ed1 c\u1ed9t) k\u1ebft h\u1ee3p MATCH v\u00e0 ROW(1:1). C\u1ee5 th\u1ec3 l\u00e0 sao? H\u00e0m ROW(1:1) khi b\u1ea1n k\u00e9o xu\u1ed1ng n\u00f3 s\u1ebd t\u0103ng d\u1ea7n, v\u00e0 ROW(1:1) =1, d\u00f2ng b\u00ean d\u01b0\u1edbi s\u1ebd l\u00e0 ROW(2:2)=2. K\u1ebft h\u1ee3p v\u1edbi MATCH, ch\u00fang ta c\u00f3: MATCH(ROW(1:1),$B$2:$B$103,0). V\u1edbi $B$2:$B$103 l\u00e0 v\u00f9ng d\u1eef li\u1ec7u t\u00ecm ki\u1ebfm.<\/p>\n

T\u01b0\u01a1ng \u1ee9ng cho Named Range, v\u1eabn v\u1edbi OFFSET, ch\u00fang ta s\u1ebd c\u00f3 \u0111\u01b0\u1ee3c to\u00e0n b\u1ed9 danh s\u00e1ch t\u00ecm th\u1ea5y \u1edf c\u1ed9t C. \u00d4 b\u1eaft \u0111\u1ea7u l\u00e0 \u00f4 \u0111\u1ea7u ti\u00ean ngay ph\u00eda tr\u00ean s\u1ea3n ph\u1ea9m \u0111\u1ea7u ti\u00ean t\u00ecm th\u1ea5y, trong c\u00f4ng th\u1ee9c l\u00e0: OFFSET($C$1,1,0,MAX(B:B)). B\u1ea1n th\u00edch ch\u1ecdn \u00f4 kh\u00e1c \u0111\u01b0\u1ee3c kh\u00f4ng, v\u00ed d\u1ee5 nh\u01b0 C2?! \u0110\u01b0\u1ee3c, khi \u0111\u00f3 c\u00f4ng th\u1ee9c ch\u00fang ta c\u00f3 s\u1ebd l\u00e0: OFFSET($C$2,0,0,MAX(B:B)).<\/p>\n

\u0110\u1ec3 \u00f4 Data Validation cho ph\u00e9p k\u1ebft qu\u1ea3 kh\u00f4ng c\u00f3 s\u1eb5n trong danh m\u1ee5c, b\u1ea1n c\u1ea7n b\u1ecf ch\u1ecdn trong ph\u1ea7n Error Alert:<\/p>\n

<\/a><\/p>\n

B\u1ea1n mu\u1ed1n file demo? Click xem th\u00f4ng qua Excel Online<\/a>
\n<\/p>\n

\n
\n
\n
\n<\/a>\n<\/div>\n<\/div>\n

Tr\u00ean \u0111\u00e2y l\u00e0 c\u00e1ch s\u1eed d\u1ee5ng m\u1ed9t s\u1ed1 h\u00e0m c\u1ee7a Excel trong c\u00e1c tr\u01b0\u1eddng h\u1ee3p \u0111\u1eb7c bi\u1ec7t. C\u00e1c h\u00e0m c\u1ee7a Excel kh\u00f4ng h\u1ec1 kh\u00f3 d\u00f9ng. Khi b\u1ea1n \u0111\u00e3 hi\u1ec3u \u0111\u00fang b\u1ea3n ch\u1ea5t v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng c\u00e1c h\u00e0m n\u00e0y, b\u1ea1n s\u1ebd th\u1ea5y ch\u00fang v\u00f4 c\u00f9ng \u0111\u01a1n gi\u1ea3n, d\u1ec5 d\u00e0ng k\u1ebft h\u1ee3p v\u1edbi c\u00e1c h\u00e0m v\u00e0 c\u00f4ng c\u1ee5 kh\u00e1c \u0111\u1ec3 t\u1ea1o ra k\u1ebft qu\u1ea3 kh\u00f4ng ng\u1edd.<\/p>\n

\u1ee8ng d\u1ee5ng Data Validation trong vi\u1ec7c t\u1ea1o danh s\u00e1ch ph\u1ee5 thu\u1ed9c Th\u00e0nh ph\u1ed1T\u1ec9nh > Qu\u1eadn Huy\u1ec7n > Ph\u01b0\u1eddng X\u00e3.<\/p>\n