{"id":6006,"date":"2024-12-21T12:11:18","date_gmt":"2024-12-21T12:11:18","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/xep-hang-theo-dieu-kien-trong-excel-phan-1\/"},"modified":"2024-12-21T12:11:18","modified_gmt":"2024-12-21T12:11:18","slug":"xep-hang-theo-dieu-kien-trong-excel-phan-1","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/xep-hang-theo-dieu-kien-trong-excel-phan-1\/","title":{"rendered":"X\u1ebfp h\u1ea1ng theo \u0111i\u1ec1u ki\u1ec7n trong excel \u2013 Ph\u1ea7n 1"},"content":{"rendered":"
\n

Nh\u1eafc t\u1edbi vi\u1ec7c x\u1ebfp h\u1ea1ng trong\u00a0excel, ch\u00fang ta th\u01b0\u1eddng ngh\u0129 ngay t\u1edbi h\u00e0m RANK.<\/p>\n

Trong b\u00e0i vi\u1ebft n\u00e0y, blog.hocexcel.online s\u1ebd h\u01b0\u1edbng d\u1eabn c\u00e1c b\u1ea1n t\u00ecm hi\u1ec3u s\u00e2u h\u01a1n v\u1ec1 h\u00e0m RANK trong c\u00e1c phi\u00ean b\u1ea3n Office t\u1eeb 2010 tr\u1edf l\u00ean, v\u00e0 vi\u1ec7c l\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 x\u1ebfp h\u1ea1ng theo m\u1ed9t ho\u1eb7c nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n.<\/p>\n

Nh\u1eefng n\u1ed9i dung ch\u00ednh:<\/p>\n

Ph\u1ea7n 1. H\u00e0m x\u1ebfp h\u1ea1ng trong c\u00e1c phi\u00ean b\u1ea3n\u00a0office t\u1eeb 2010 tr\u1edf l\u00ean\u00a0\u0111\u01b0\u1ee3c thay\u00a0\u0111\u1ed5i t\u1eeb h\u00e0m RANK sang h\u00e0m RANK.AVG v\u00e0 h\u00e0m RANK.EQ<\/em><\/strong><\/p>\n

Ph\u1ea7n 2. H\u00e0m RANK \/ RANK.AVG \/ RANK.EQ\u00a0\u0111\u1ec1u kh\u00f4ng h\u1ed7 tr\u1ee3 x\u1ebfp h\u1ea1ng theo\u00a0\u0111i\u1ec1u ki\u1ec7n. Mu\u1ed1n x\u1ebfp h\u1ea1ng theo\u00a0\u0111i\u1ec1u ki\u1ec7n c\u1ea7n k\u1ebft h\u1ee3p th\u00eam nh\u1eefng b\u01b0\u1edbc t\u00ednh to\u00e1n kh\u00e1c.<\/em><\/strong><\/p>\n

Ph\u1ea7n 3. C\u00e1ch x\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c x\u1ebfp h\u1ea1ng theo m\u1ed9t\u00a0\u0111i\u1ec1u ki\u1ec7n, ho\u1eb7c theo nhi\u1ec1u\u00a0\u0111i\u1ec1u ki\u1ec7n (theo th\u1ee9 t\u1ef1\u00a0\u01b0u ti\u00ean)<\/em><\/strong><\/p>\n

\u0110\u1ec3 d\u1ec5 h\u00ecnh dung, ch\u00fang ta s\u1ebd\u00a0\u00e1p d\u1ee5ng b\u00e0i t\u1eadp sau\u00a0\u0111\u1ec3 l\u00e0m v\u00ed d\u1ee5 minh h\u1ecda:<\/p>\n

<\/p>\n

\n
\n

D\u00f9 \u0111\u00e3 c\u00f3 ph\u1ea7n m\u1ec1m, nh\u01b0ng k\u1ef9 n\u0103ng Excel v\u1eabn c\u1ef1c k\u1ef3 quan tr\u1ecdng v\u1edbi k\u1ebf to\u00e1n, b\u1ea1n \u0111\u00e3 v\u1eefng Excel ch\u01b0a? H\u00e3y \u0111\u1ec3 t\u00f4i gi\u00fap b\u1ea1n, \u0111\u0103ng k\u00fd kho\u00e1 h\u1ecdc Excel:<\/p>\n


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

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

Sau \u0111\u00e2y ch\u00fang ta b\u1eaft\u00a0\u0111\u1ea7u t\u00ecm hi\u1ec3u c\u00e1c n\u1ed9i dung:<\/p>\n

Ph\u1ea7n 1: Ph\u00e2n bi\u1ec7t c\u00e1c h\u00e0m RANK \/ RANK.AVG \/ RANK.EQ<\/strong><\/p>\n

B\u1eaft\u00a0\u0111\u1ea7u t\u1eeb phi\u00ean b\u1ea3n 2010, trong\u00a0excel xu\u1ea5t hi\u1ec7n 2 h\u00e0m thay th\u1ebf cho h\u00e0m RANK l\u00e0 h\u00e0m RANK.AVG v\u00e0 h\u00e0m RANK.EQ. C\u00e1c phi\u00ean b\u1ea3n tr\u01b0\u1edbc\u00a0\u0111\u00f3 (2003, 2007) ch\u1ec9 c\u00f3 h\u00e0m RANK m\u00e0 th\u00f4i.<\/p>\n

V\u1ec1 c\u00fa ph\u00e1p, 3 h\u00e0m n\u00e0y \u0111\u1ec1u c\u00f3 c\u00fa ph\u00e1p gi\u1ed1ng nhau<\/p>\n

C\u00da PH\u00c1P<\/strong><\/h4>\n

=RANK( number, ref, [order])<\/strong><\/p>\n

=RANK.AVG( number, ref, [order])<\/strong><\/p>\n

=RANK.EQ( number, ref, [order])<\/strong><\/p>\n

Trong \u0111\u00f3:<\/em><\/p>\n

\u2013\u00a0Number<\/em><\/strong>: \u0110\u1ed1i s\u1ed1 m\u00e0 b\u1ea1n mu\u1ed1n t\u00ecm th\u1ee9 h\u1ea1ng cho n\u00f3.
\n\u2013\u00a0Ref<\/em><\/strong>: M\u1ed9t m\u1ea3ng ho\u1eb7c tham chi\u1ebfu \u0111\u1ebfn danh s\u00e1ch c\u00e1c s\u1ed1.
\n\u2013\u00a0Order<\/em><\/strong>: S\u1ed1 ch\u1ec9 r\u00f5 c\u00e1ch x\u1ebfp h\u1ea1ng. (Order c\u00f3 2 gi\u00e1 tr\u1ecb l\u00e0 0 v\u00e0 1).<\/p>\n

*L\u01b0u \u00fd<\/strong>:\u00a0N\u1ebfu Order=0 th\u00ec x\u1ebfp h\u1ea1ng theo th\u1ee9 t\u1ef1 gi\u1ea3m d\u1ea7n, order =1 th\u00ec x\u1ebfp h\u00e0ng theo th\u1ee9 t\u1ef1 t\u0103ng d\u1ea7n.<\/p>\n

Ch\u00fang ta x\u00e9t vi\u1ec7c s\u1eed d\u1ee5ng 3 h\u00e0m n\u00e0y v\u00e0o b\u00e0i t\u1eadp v\u00e0 xem k\u1ebft qu\u1ea3 thu\u00a0\u0111\u01b0\u1ee3c th\u1ebf n\u00e0o nh\u00e9: (X\u1ebfp theo th\u1ee9 t\u1ef1 gi\u1ea3m d\u1ea7n)<\/p>\n

<\/p>\n

H\u00e0m RANK v\u00e0 RANK.AVG cho k\u1ebft qu\u1ea3 gi\u1ed1ng nhau.\u00a0\u1ede\u00a0\u0111\u00e2y h\u00e0m RANK.AVG ngh\u0129a l\u00e0 th\u1ee9 h\u1ea1ng trung b\u00ecnh, t\u1ee9c l\u00e0 n\u1ebfu c\u00f3 s\u1ef1\u00a0\u0111\u1ed3ng h\u1ea1ng (th\u1ee9 h\u1ea1ng ngang nhau) th\u00ec s\u1ebd x\u1ebfp trung b\u00ecnh cho s\u1ed1 h\u1ea1ng\u00a0\u0111\u00f3 (2 ng\u01b0\u1eddi\u00a0\u0111\u1ed3ng h\u1ea1ng\u00a0th\u00ec th\u1ee9 h\u1ea1ng trung b\u00ecnh l\u00e0 + 0,5 ; 10 ng\u01b0\u1eddi\u00a0\u0111\u1ed3ng h\u1ea1ng th\u00ec th\u1ee9 h\u1ea1ng trung b\u00ecnh l\u00e0 + 0,1) (AVG l\u00e0 vi\u1ebft t\u1eaft c\u1ee7a Average)<\/p>\n

H\u00e0m RANK.EQ s\u1ebd x\u1ebfp\u00a0\u0111\u1ed3ng h\u1ea1ng m\u00e0 kh\u00f4ng t\u00ednh trung b\u00ecnh h\u1ea1ng, t\u1ee9c l\u00e0 n\u1ebfu 2 ng\u01b0\u1eddi\u00a0\u0111\u1ed3ng h\u1ea1ng th\u00ec s\u1ebd x\u1ebfp c\u00f9ng v\u00e0o 1 th\u1ee9 h\u1ea1ng\u00a0\u0111\u01b0\u1ee3c l\u00e0m tr\u00f2n, ch\u1ee9 kh\u00f4ng ph\u1ea3i th\u1ee9 h\u1ea1ng l\u1ebb. (EQ l\u00e0 vi\u1ebft t\u1eaft c\u1ee7a Equal)<\/p>\n

\u0110\u1ec3 c\u00f3 th\u1ec3\u00a0\u1ee9ng d\u1ee5ng vi\u1ec7c x\u1ebfp h\u1ea1ng c\u1ee7a c\u00e1c h\u00e0m n\u00e0y v\u00e0o vi\u1ec7c x\u1ebfp h\u1ea1ng theo m\u1ed9t ho\u1eb7c nhi\u1ec1u\u00a0\u0111i\u1ec1u ki\u1ec7n, m\u1eddi c\u00e1c b\u1ea1n\u00a0\u0111\u00f3n xem\u00a0\u1edf nh\u1eefng ph\u1ea7n sau c\u1ee7a b\u00e0i vi\u1ebft n\u00e0y. M\u1ee5c ti\u00eau c\u1ee7a ch\u00fang ta l\u00e0 b\u1ea3ng k\u1ebft qu\u1ea3 sau:<\/p>\n

<\/p>\n

Ph\u1ea7n 2: C\u00e1ch ph\u00e2n bi\u1ec7t nh\u1eefng ng\u01b0\u1eddi\u00a0\u0111\u1ed3ng h\u1ea1ng theo 1 ti\u00eau chu\u1ea9n\u00a0\u01b0u ti\u00ean (x\u1ebfp h\u1ea1ng theo m\u1ed9t \u0111i\u1ec1u ki\u1ec7n)<\/strong><\/p>\n

Theo b\u00e0i t\u1eadp v\u00ed d\u1ee5\u00a0\u1edf tr\u00ean, ch\u00fang ta c\u00f3 th\u1ec3 x\u00e1c\u00a0\u0111\u1ecbnh y\u00eau c\u1ea7u\u00a0\u1edf\u00a0\u0111\u00e2y nh\u01b0 sau:<\/p>\n

X\u1ebfp h\u1ea1ng theo\u00a0\u0111i\u1ec3m trung b\u00ecnh, n\u1ebfu\u00a0\u0111\u1ed3ng h\u1ea1ng th\u00ec\u00a0\u01b0u ti\u00ean nh\u1eefng ai c\u00f3\u00a0\u0111i\u1ec3m to\u00e1n cao h\u01a1n s\u1ebd x\u1ebfp h\u1ea1ng cao h\u01a1n.<\/p>\n

<\/p>\n

Ch\u00fang ta c\u00f9ng ph\u00e2n t\u00edch\u00a0\u0111\u1ec1 b\u00e0i\u00a0\u0111\u1ec3 hi\u1ec3u c\u00e1ch l\u00e0m nh\u00e9:<\/p>\n

B\u01b0\u1edbc 1<\/em><\/strong>: X\u00e9t\u00a0\u01b0u ti\u00ean m\u00f4n to\u00e1n khi\u00a0\u0111i\u1ec3m trung b\u00ecnh b\u1eb1ng nhau.<\/p>\n

<\/p>\n

C\u00f4ng th\u1ee9c s\u1eed d\u1ee5ng:<\/p>\n

\n

=IF(COUNTIF($E$4:$E$13,E4)>1,RANK.EQ(B4,$B$4:$B$13,1),0)<\/p>\n<\/blockquote>\n

Ph\u00e2n t\u00edch c\u00f4ng th\u1ee9c:<\/p>\n

COUNTIF($E$4:$E$13,E4)>1\u00a0 \u00a0 \u00a0\u00a0\u0110\u1ebfm trong v\u00f9ng E4:E13 theo t\u1eebng gi\u00e1 tr\u1ecb\u00a0\u0111i\u1ec3m, x\u00e9t\u00a0\u0111i\u1ec3m n\u00e0o c\u00f3 k\u1ebft qu\u1ea3\u00a0\u0111\u1ebfm l\u1edbn h\u01a1n 1 (t\u1ee9c l\u00e0 c\u00f3\u00a0\u0111i\u1ec3m tr\u00f9ng)<\/p>\n

RANK.EQ(B4,$B$4:$B$13,1)\u00a0 \u00a0Khi\u00a0\u0111i\u1ec3m trung b\u00ecnh b\u1ecb tr\u00f9ng, th\u00ec ta s\u1ebd x\u1ebfp h\u1ea1ng theo\u00a0\u0111i\u1ec3m to\u00e1n, th\u1ee9 t\u1ef1 t\u0103ng\u00a0d\u1ea7n (ai\u00a0\u0111i\u1ec3m th\u1ea5p nh\u1ea5t s\u1ebd x\u1ebfp tr\u01b0\u1edbc)<\/p>\n

=> C\u00f4ng th\u1ee9c\u00a0\u0111\u01b0\u1ee3c hi\u1ec3u l\u00e0: n\u1ebfu\u00a0\u0111i\u1ec3m trung b\u00ecnh m\u00e0 tr\u00f9ng, th\u00ec t\u1ea1i nh\u1eefng ng\u01b0\u1eddi b\u1ecb tr\u00f9ng\u00a0\u0111\u00f3 s\u1ebd x\u1ebfp h\u1ea1ng h\u1ecd theo\u00a0\u0111i\u1ec3m m\u00f4n to\u00e1n, theo th\u1ee9 t\u1ef1 t\u0103ng\u00a0d\u1ea7n.<\/p>\n

* L\u01b0u\u00a0\u00fd\u00a0\u1edf\u00a0\u0111\u00e2y: T\u1ea1i sao th\u1ee9 t\u1ef1\u00a0\u01b0u ti\u00ean l\u1ea1i x\u1ebfp t\u0103ng d\u1ea7n m\u00e0 kh\u00f4ng ph\u1ea3i gi\u1ea3m d\u1ea7n.<\/p>\n

B\u1edfi v\u00ec nh\u01b0 v\u1eady ai\u00a0\u0111i\u1ec3m c\u00e0ng cao th\u00ec th\u1ee9 t\u1ef1 c\u00e0ng l\u00e0 s\u1ed1 l\u1edbn => S\u1ed1 th\u1ee9 h\u1ea1ng n\u00e0y c\u0169ng ch\u00ednh l\u00e0 s\u1ed1 s\u1ebd\u00a0\u0111\u01b0\u1ee3c c\u1ed9ng th\u00eam v\u00e0o\u00a0\u0111i\u1ec3m g\u1ed1c\u00a0\u0111\u1ec3 l\u00e0m c\u0103n c\u1ee9 x\u1ebfp h\u1ea1ng v\u1edbi\u00a0\u0111i\u1ec1u ki\u1ec7n\u00a0\u01b0u ti\u00ean => S\u1ed1\u00a0\u0111i\u1ec3m c\u00e0ng cao th\u00ec\u00a0\u0111i\u1ec3m c\u1ed9ng c\u00e0ng nhi\u1ec1u<\/p>\n

B\u01b0\u1edbc 2<\/em><\/strong>: T\u00ednh l\u1ea1i\u00a0\u0111i\u1ec3m trung b\u00ecnh khi c\u00f3\u00a0\u0111i\u1ec3m\u00a0\u01b0u ti\u00ean<\/p>\n

<\/p>\n

V\u00ec\u00a0\u0111i\u1ec3m trung b\u00ecnh c\u00f3 2 s\u1ed1 sau d\u1ea5u ph\u1ea9y n\u00ean\u00a0\u0111i\u1ec3m\u00a0\u01b0u ti\u00ean s\u1ebd t\u00ednh sau d\u1ea5u ph\u1ea9y 3 ch\u1eef s\u1ed1 (chia th\u1ee9 h\u1ea1ng\u00a0\u1edf c\u1ed9t\u00a0\u01b0u ti\u00ean cho 1000)<\/p>\n

Khi\u00a0\u0111\u00f3 ta s\u1ebd\u00a0\u0111\u01b0\u1ee3c\u00a0\u0111i\u1ec3m trung b\u00ecnh m\u1edbi bao g\u1ed3m\u00a0\u0111i\u1ec3m trung b\u00ecnh c\u0169 +\u00a0\u0111i\u1ec3m x\u1ebfp h\u1ea1ng v\u1edbi nh\u1eefng \u0111i\u1ec3m tr\u00f9ng m\u00e0 x\u00e9t\u00a0\u01b0u ti\u00ean v\u1edbi\u00a0\u0111i\u1ec3m to\u00e1n<\/p>\n

\u0110i\u1ec3m x\u1ebfp h\u1ea1ng\u00a0\u01b0u ti\u00ean n\u00e0y n\u00ean\u00a0\u0111\u1eb7t ph\u00eda sau\u00a0\u0111i\u1ec3m g\u1ed1c\u00a0\u0111\u1ec3 vi\u1ec7c s\u1eafp x\u1ebfp th\u1ee9 h\u1ea1ng t\u1ed1t h\u01a1n v\u00e0 d\u1ec5 ph\u00e2n bi\u1ec7t h\u01a1n.<\/p>\n

B\u01b0\u1edbc 3<\/strong><\/em>: X\u1ebfp h\u1ea1ng d\u1ef1a tr\u00ean\u00a0\u0111i\u1ec3m trung b\u00ecnh m\u1edbi<\/p>\n

<\/p>\n

H\u00e0m x\u1ebfp h\u1ea1ng\u00a0\u1edf\u00a0\u0111\u00e2y ch\u00fang ta s\u1eed d\u1ee5ng h\u00e0m RANK.EQ\u00a0\u0111\u1ec3 x\u1ebfp h\u1ea1ng cho\u00a0\u0111i\u1ec3m trung b\u00ecnh m\u1edbi.<\/p>\n

Ph\u1ea7n 3. C\u00e1ch x\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c x\u1ebfp h\u1ea1ng theo m\u1ed9t\u00a0\u0111i\u1ec1u ki\u1ec7n, ho\u1eb7c theo nhi\u1ec1u\u00a0\u0111i\u1ec1u ki\u1ec7n (theo th\u1ee9 t\u1ef1\u00a0\u01b0u ti\u00ean)<\/em><\/strong><\/p>\n

Sau khi hi\u1ec3u c\u00e1ch x\u1ebfp h\u1ea1ng theo m\u1ed9t\u00a0\u0111i\u1ec1u ki\u1ec7n, ch\u00fang ta c\u00f3 th\u1ec3\u00a0\u00e1p d\u1ee5ng c\u00e1ch\u00a0\u0111\u00f3 cho nhi\u1ec1u\u00a0\u0111i\u1ec1u ki\u1ec7n.<\/p>\n

V\u1edbi tr\u01b0\u1eddng h\u1ee3p nhi\u1ec1u\u00a0\u0111i\u1ec1u ki\u1ec7n, ch\u00fang ta c\u1ea7n quan t\u00e2m t\u1edbi 2 ti\u00eau ch\u00ed:<\/p>\n