{"id":477220,"date":"2025-07-26T13:23:07","date_gmt":"2025-07-26T06:23:07","guid":{"rendered":"https:\/\/hoanghamobile.com\/tin-tuc\/?p=477220"},"modified":"2025-07-26T13:23:07","modified_gmt":"2025-07-26T06:23:07","slug":"aggregate","status":"publish","type":"post","link":"https:\/\/hoanghamobile.com\/tin-tuc\/aggregate\/","title":{"rendered":"H\u00e0m AGGREGATE trong Excel l\u00e0 g\u00ec? C\u00fa ph\u00e1p v\u00e0 v\u00ed d\u1ee5 c\u00e1ch d\u00f9ng h\u00e0m"},"content":{"rendered":"

AGGREGATE<\/b> l\u00e0 m\u1ed9t h\u00e0m m\u1ea1nh m\u1ebd trong Excel, cho ph\u00e9p th\u1ef1c hi\u1ec7n nhi\u1ec1u ph\u00e9p t\u00ednh t\u1ed5ng h\u1ee3p nh\u01b0 trung b\u00ecnh, t\u1ed5ng, \u0111\u1ebfm, gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t\/nh\u1ecf nh\u1ea5t\u2026 \u0111\u1ed3ng th\u1eddi c\u00f3 th\u1ec3 b\u1ecf qua c\u00e1c l\u1ed7i ho\u1eb7c h\u00e0ng \u1ea9n trong d\u1eef li\u1ec7u. Trong b\u00e0i vi\u1ebft n\u00e0y, ch\u00fang ta s\u1ebd t\u00ecm hi\u1ec3u c\u00fa ph\u00e1p c\u1ee7a h\u00e0m n\u00e0y, c\u00e1c ch\u1ee9c n\u0103ng m\u00e0 n\u00f3 h\u1ed7 tr\u1ee3, c\u0169ng nh\u01b0 v\u00ed d\u1ee5 minh h\u1ecda c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m n\u00e0y m\u1ed9t c\u00e1ch hi\u1ec7u qu\u1ea3.<\/span><\/p>\n

H\u00e0m AGGREGATE trong Excel l\u00e0 g\u00ec?<\/b><\/h2>\n

\u0110\u00e2y l\u00e0 m\u1ed9t c\u00f4ng c\u1ee5 h\u1eefu \u00edch \u0111\u01b0\u1ee3c thi\u1ebft k\u1ebf \u0111\u1ec3 th\u1ef1c hi\u1ec7n c\u00e1c ph\u00e9p t\u00ednh t\u1ed5ng h\u1ee3p m\u1ed9t c\u00e1ch linh ho\u1ea1t v\u00e0 ch\u00ednh x\u00e1c. Kh\u00f4ng gi\u1ed1ng nh\u01b0 c\u00e1c h\u00e0m \u0111\u01a1n l\u1ebb nh\u01b0 SUM, AVERAGE, COUNT, MAX hay MIN, h\u00e0m t\u1eadp h\u1ee3p c\u00f3 kh\u1ea3 n\u0103ng t\u00edch h\u1ee3p nhi\u1ec1u ch\u1ee9c n\u0103ng t\u00ednh to\u00e1n kh\u00e1c nhau ch\u1ec9 trong m\u1ed9t c\u00f4ng th\u1ee9c duy nh\u1ea5t. \u0110i\u1ec1u n\u00e0y gi\u00fap ng\u01b0\u1eddi d\u00f9ng d\u1ec5 d\u00e0ng l\u1ef1a ch\u1ecdn lo\u1ea1i ph\u00e9p t\u00ednh ph\u00f9 h\u1ee3p v\u1edbi m\u1ee5c \u0111\u00edch x\u1eed l\u00fd d\u1eef li\u1ec7u m\u00e0 kh\u00f4ng c\u1ea7n s\u1eed d\u1ee5ng nhi\u1ec1u h\u00e0m ri\u00eang l\u1ebb.<\/span><\/p>\n

\"aggregate-1\"<\/picture><\/p>\n

M\u1ed9t \u0111i\u1ec3m n\u1ed5i b\u1eadt c\u1ee7a h\u00e0m t\u1ed5ng h\u1ee3p n\u00e0y l\u00e0 kh\u1ea3 n\u0103ng x\u1eed l\u00fd th\u00f4ng minh \u0111\u1ed1i v\u1edbi c\u00e1c \u00f4 ch\u1ee9a l\u1ed7i ho\u1eb7c b\u1ecb \u1ea9n. V\u1edbi c\u00e1c t\u00f9y ch\u1ecdn b\u1ed5 sung, b\u1ea1n c\u00f3 th\u1ec3 y\u00eau c\u1ea7u h\u00e0m b\u1ecf qua c\u00e1c h\u00e0ng \u0111\u00e3 b\u1ecb l\u1ecdc, c\u00e1c gi\u00e1 tr\u1ecb l\u1ed7i ho\u1eb7c th\u1eadm ch\u00ed l\u00e0 c\u00e1c \u00f4 kh\u00f4ng hi\u1ec3n th\u1ecb, \u0111i\u1ec1u m\u00e0 nhi\u1ec1u h\u00e0m Excel th\u00f4ng th\u01b0\u1eddng kh\u00f4ng th\u1ec3 l\u00e0m \u0111\u01b0\u1ee3c. Nh\u1edd v\u1eady, h\u00e0m gi\u00fap \u0111\u1ea3m b\u1ea3o k\u1ebft qu\u1ea3 t\u00ednh to\u00e1n lu\u00f4n ch\u00ednh x\u00e1c v\u00e0 ph\u00f9 h\u1ee3p v\u1edbi d\u1eef li\u1ec7u th\u1ef1c t\u1ebf.<\/span><\/p>\n

Ngo\u00e0i ra, v\u00ec h\u1ed7 tr\u1ee3 \u0111\u1ebfn 19 ch\u1ee9c n\u0103ng th\u1ed1ng k\u00ea v\u00e0 to\u00e1n h\u1ecdc kh\u00e1c nhau, h\u00e0m t\u1eadp h\u1ee3p \u0111\u1eb7c bi\u1ec7t h\u1eefu \u00edch trong c\u00e1c b\u1ea3ng t\u00ednh ph\u1ee9c t\u1ea1p, n\u01a1i y\u00eau c\u1ea7u x\u1eed l\u00fd kh\u1ed1i l\u01b0\u1ee3ng l\u1edbn d\u1eef li\u1ec7u c\u00f3 \u0111i\u1ec1u ki\u1ec7n. H\u00e0m n\u00e0y kh\u00f4ng ch\u1ec9 gi\u00fap gi\u1ea3m s\u1ed1 l\u01b0\u1ee3ng c\u00f4ng th\u1ee9c c\u1ea7n d\u00f9ng m\u00e0 c\u00f2n t\u0103ng t\u00ednh hi\u1ec7u qu\u1ea3 trong qu\u00e1 tr\u00ecnh ph\u00e2n t\u00edch d\u1eef li\u1ec7u.<\/span><\/p>\n

C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m AGGREGATE trong Excel<\/b><\/h2>\n

\u0110\u1ec3 s\u1eed d\u1ee5ng h\u00e0m t\u1ed5ng h\u1ee3p trong Excel hi\u1ec7u qu\u1ea3, b\u1ea1n c\u1ea7n hi\u1ec3u r\u00f5 c\u00fa ph\u00e1p, c\u00e1c tham s\u1ed1 v\u00e0 ch\u1ee9c n\u0103ng m\u00e0 h\u00e0m h\u1ed7 tr\u1ee3. D\u01b0\u1edbi \u0111\u00e2y l\u00e0 h\u01b0\u1edbng d\u1eabn chi ti\u1ebft c\u00e1ch d\u00f9ng h\u00e0m, k\u00e8m theo v\u00ed d\u1ee5 minh h\u1ecda gi\u00fap b\u1ea1n \u00e1p d\u1ee5ng linh ho\u1ea1t trong nhi\u1ec1u t\u00ecnh hu\u1ed1ng t\u00ednh to\u00e1n v\u00e0 x\u1eed l\u00fd d\u1eef li\u1ec7u.<\/span><\/p>\n

C\u00f4ng th\u1ee9c h\u00e0m AGGREGATE<\/b><\/h3>\n

H\u00e0m t\u00edch l\u0169y trong Excel c\u00f3 hai d\u1ea1ng c\u00fa ph\u00e1p t\u00f9y thu\u1ed9c v\u00e0o lo\u1ea1i h\u00e0m b\u1ea1n mu\u1ed1n s\u1eed d\u1ee5ng: d\u1ea1ng tham chi\u1ebfu v\u00e0 d\u1ea1ng m\u1ea3ng. M\u1ed7i d\u1ea1ng ph\u1ee5c v\u1ee5 m\u1ee5c \u0111\u00edch ri\u00eang, ph\u00f9 h\u1ee3p v\u1edbi t\u1eebng ki\u1ec3u d\u1eef li\u1ec7u v\u00e0 nhu c\u1ea7u x\u1eed l\u00fd.<\/span><\/p>\n

V\u1edbi bi\u1ec3u m\u1eabu tham chi\u1ebfu, c\u00fa ph\u00e1p \u0111\u01b0\u1ee3c vi\u1ebft nh\u01b0 sau:<\/span><\/p>\n

=AGGREGATE(function_num, options, ref1, [ref2],\u2026)<\/b><\/p>\n

C\u00e1ch n\u00e0y \u00e1p d\u1ee5ng khi b\u1ea1n l\u00e0m vi\u1ec7c v\u1edbi nhi\u1ec1u v\u00f9ng d\u1eef li\u1ec7u c\u1ee5 th\u1ec3. Trong \u0111\u00f3, function_num l\u00e0 s\u1ed1 \u0111\u1ea1i di\u1ec7n cho ph\u00e9p t\u00ednh b\u1ea1n mu\u1ed1n th\u1ef1c hi\u1ec7n nh\u01b0 trung b\u00ecnh, t\u1ed5ng, \u0111\u1ebfm,\u2026 Options cho ph\u00e9p ki\u1ec3m so\u00e1t vi\u1ec7c lo\u1ea1i b\u1ecf l\u1ed7i, h\u00e0ng \u1ea9n ho\u1eb7c gi\u00e1 tr\u1ecb b\u1ecb l\u1ecdc. Ref1 l\u00e0 v\u00f9ng d\u1eef li\u1ec7u \u0111\u1ea7u ti\u00ean, v\u00e0 b\u1ea1n c\u00f3 th\u1ec3 th\u00eam nhi\u1ec1u v\u00f9ng kh\u00e1c n\u1ebfu c\u1ea7n.<\/span><\/p>\n

\"aggregate-2\"<\/picture><\/p>\n

\u0110\u1ed1i v\u1edbi bi\u1ec3u m\u1eabu m\u1ea3ng, c\u00fa ph\u00e1p nh\u01b0 sau:<\/span><\/p>\n

=AGGREGATE(function_num, options, array, [k])<\/b><\/p>\n

D\u1ea1ng n\u00e0y th\u01b0\u1eddng \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng v\u1edbi c\u00e1c ph\u00e9p t\u00ednh y\u00eau c\u1ea7u x\u1eed l\u00fd m\u1ea3ng ho\u1eb7c t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb l\u1edbn\/th\u1ee9 h\u1ea1ng. Trong \u0111\u00f3, array l\u00e0 m\u1ea3ng d\u1eef li\u1ec7u c\u1ea7n t\u00ednh v\u00e0 k l\u00e0 tham s\u1ed1 t\u00f9y ch\u1ecdn, th\u01b0\u1eddng d\u00f9ng v\u1edbi c\u00e1c h\u00e0m c\u1ea7n th\u1ee9 t\u1ef1 nh\u01b0 LARGE, SMALL, PERCENTILE,\u2026<\/span><\/p>\n

B\u1ea3ng function_num trong h\u00e0m AGGREGATE<\/b><\/h3>\n

Trong c\u00fa ph\u00e1p c\u1ee7a h\u00e0m n\u00e0y, tham s\u1ed1 \u0111\u1ea7u ti\u00ean l\u00e0 function_num, d\u00f9ng \u0111\u1ec3 x\u00e1c \u0111\u1ecbnh lo\u1ea1i ph\u00e9p t\u00ednh m\u00e0 b\u1ea1n mu\u1ed1n th\u1ef1c hi\u1ec7n. M\u1ed7i gi\u00e1 tr\u1ecb s\u1ed1 nguy\u00ean t\u1eeb 1 \u0111\u1ebfn 19 t\u01b0\u01a1ng \u1ee9ng v\u1edbi m\u1ed9t h\u00e0m th\u1ed1ng k\u00ea ho\u1eb7c to\u00e1n h\u1ecdc c\u1ee5 th\u1ec3 trong Excel. \u0110\u00e2y l\u00e0 c\u00e1ch h\u00e0m g\u1ed9p nhi\u1ec1u h\u00e0m \u0111\u01a1n l\u1ebb l\u1ea1i th\u00e0nh m\u1ed9t c\u00f4ng c\u1ee5 \u0111a n\u0103ng duy nh\u1ea5t.<\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
function_num<\/b><\/td>\nH\u00e0m t\u01b0\u01a1ng \u1ee9ng<\/b><\/td>\n<\/tr>\n
1<\/span><\/td>\nAVERAGE<\/span><\/td>\n<\/tr>\n
2<\/span><\/td>\nCOUNT<\/span><\/td>\n<\/tr>\n
3<\/span><\/td>\nCOUNTA<\/span><\/td>\n<\/tr>\n
4<\/span><\/td>\nMAX<\/span><\/td>\n<\/tr>\n
5<\/span><\/td>\nMIN<\/span><\/td>\n<\/tr>\n
6<\/span><\/td>\nPRODUCT<\/span><\/td>\n<\/tr>\n
7<\/span><\/td>\nSTDEV.S<\/span><\/td>\n<\/tr>\n
8<\/span><\/td>\nSTDEV.P<\/span><\/td>\n<\/tr>\n
9<\/span><\/td>\nSUM<\/span><\/td>\n<\/tr>\n
10<\/span><\/td>\nVAR.S<\/span><\/td>\n<\/tr>\n
11<\/span><\/td>\nVAR.P<\/span><\/td>\n<\/tr>\n
12<\/span><\/td>\nMEDIAN<\/span><\/td>\n<\/tr>\n
13<\/span><\/td>\nMODE.SNGL<\/span><\/td>\n<\/tr>\n
14<\/span><\/td>\nLARGE<\/span><\/td>\n<\/tr>\n
15<\/span><\/td>\nSMALL<\/span><\/td>\n<\/tr>\n
16<\/span><\/td>\nPERCENTILE.INC<\/span><\/td>\n<\/tr>\n
17<\/span><\/td>\nQUARTILE.INC<\/span><\/td>\n<\/tr>\n
18<\/span><\/td>\nPERCENTILE.EXC<\/span><\/td>\n<\/tr>\n
19<\/span><\/td>\nQUARTILE.EXC<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

B\u1ea3ng options trong h\u00e0m AGGREGATE<\/b><\/h3>\n

Tham s\u1ed1 options \u0111\u00f3ng vai tr\u00f2 thi\u1ebft l\u1eadp c\u00e1ch x\u1eed l\u00fd d\u1eef li\u1ec7u \u0111\u1eb7c bi\u1ec7t trong qu\u00e1 tr\u00ecnh t\u00ednh to\u00e1n. Nh\u1edd v\u00e0o t\u00f9y ch\u1ecdn n\u00e0y, ng\u01b0\u1eddi d\u00f9ng c\u00f3 th\u1ec3 d\u1ec5 d\u00e0ng lo\u1ea1i b\u1ecf d\u1eef li\u1ec7u kh\u00f4ng c\u1ea7n thi\u1ebft, tr\u00e1nh sai l\u1ec7ch k\u1ebft qu\u1ea3 khi l\u00e0m vi\u1ec7c v\u1edbi b\u1ea3ng t\u00ednh ch\u1ee9a c\u00e1c d\u00f2ng b\u1ecb \u1ea9n, c\u00f4ng th\u1ee9c l\u1ed7i ho\u1eb7c c\u00e1c h\u00e0m ph\u1ee5.\u00a0<\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n
options<\/b><\/td>\n\u0110i\u1ec1u ki\u1ec7n \u00e1p d\u1ee5ng<\/b><\/td>\n<\/tr>\n
0<\/span><\/td>\nB\u1ecf qua h\u00e0ng ch\u1ee9a h\u00e0m SUBTOTAL v\u00e0 AGGREGATE<\/span><\/td>\n<\/tr>\n
1<\/span><\/td>\nB\u1ecf qua h\u00e0ng b\u1ecb \u1ea9n, c\u00f9ng v\u1edbi SUBTOTAL v\u00e0 AGGREGATE<\/span><\/td>\n<\/tr>\n
2<\/span><\/td>\nB\u1ecf qua gi\u00e1 tr\u1ecb l\u1ed7i, \u0111\u1ed3ng th\u1eddi lo\u1ea1i b\u1ecf SUBTOTAL v\u00e0 AGGREGATE<\/span><\/td>\n<\/tr>\n
3<\/span><\/td>\nB\u1ecf qua c\u1ea3 h\u00e0ng \u1ea9n, l\u1ed7i, c\u00f9ng SUBTOTAL v\u00e0 AGGREGATE<\/span><\/td>\n<\/tr>\n
4<\/span><\/td>\nKh\u00f4ng lo\u1ea1i b\u1ecf b\u1ea5t c\u1ee9 y\u1ebfu t\u1ed1 n\u00e0o<\/span><\/td>\n<\/tr>\n
5<\/span><\/td>\nB\u1ecf qua c\u00e1c h\u00e0ng b\u1ecb \u1ea9n<\/span><\/td>\n<\/tr>\n
6<\/span><\/td>\nB\u1ecf qua c\u00e1c gi\u00e1 tr\u1ecb g\u00e2y l\u1ed7i<\/span><\/td>\n<\/tr>\n
7<\/span><\/td>\nB\u1ecf qua c\u1ea3 h\u00e0ng \u1ea9n v\u00e0 c\u00e1c gi\u00e1 tr\u1ecb l\u1ed7i<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

V\u00ed d\u1ee5 minh h\u1ecda c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m AGGREGATE trong Excel<\/b><\/h2>\n

\u0110\u1ec3 hi\u1ec3u r\u00f5 c\u00e1ch ho\u1ea1t \u0111\u1ed9ng c\u1ee7a h\u00e0m trong th\u1ef1c t\u1ebf, b\u1ea1n c\u1ea7n xem qua m\u1ed9t s\u1ed1 v\u00ed d\u1ee5 minh h\u1ecda c\u1ee5 th\u1ec3. D\u01b0\u1edbi \u0111\u00e2y l\u00e0 c\u00e1c t\u00ecnh hu\u1ed1ng \u1ee9ng d\u1ee5ng gi\u00fap b\u1ea1n h\u00ecnh dung c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m n\u00e0y \u0111\u1ec3 t\u00ednh to\u00e1n, x\u1eed l\u00fd d\u1eef li\u1ec7u c\u00f3 l\u1ed7i ho\u1eb7c h\u00e0ng b\u1ecb \u1ea9n trong Excel m\u1ed9t c\u00e1ch hi\u1ec7u qu\u1ea3 v\u00e0 linh ho\u1ea1t.<\/span><\/p>\n

V\u00ed d\u1ee5 1<\/b><\/h3>\n

Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 m\u1ed9t danh s\u00e1ch s\u1ed1 li\u1ec7u t\u1eeb \u00f4 B4 \u0111\u1ebfn B14, trong \u0111\u00f3 m\u1ed9t v\u00e0i \u00f4 c\u00f3 th\u1ec3 ch\u1ee9a l\u1ed7i nh\u01b0 #DIV\/0! ho\u1eb7c #VALUE!. Thay v\u00ec d\u00f9ng SUM v\u00e0 b\u1ecb gi\u00e1n \u0111o\u1ea1n k\u1ebft qu\u1ea3, ta s\u1ebd s\u1eed d\u1ee5ng h\u00e0m t\u1ed5ng h\u1ee3p v\u1edbi function_num l\u00e0 9 (\u0111\u1ea1i di\u1ec7n cho h\u00e0m SUM), v\u00e0 options l\u00e0 6, ngh\u0129a l\u00e0 y\u00eau c\u1ea7u b\u1ecf qua m\u1ecdi gi\u00e1 tr\u1ecb l\u1ed7i trong v\u00f9ng d\u1eef li\u1ec7u \u0111\u01b0\u1ee3c ch\u1ecdn.<\/span><\/p>\n

C\u00f4ng th\u1ee9c \u00e1p d\u1ee5ng nh\u01b0 sau: <\/span>=AGGREGATE(9,6,B4:B14)<\/b><\/p>\n

\"aggregate-3\"<\/picture><\/p>\n

Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, c\u00f4ng th\u1ee9c s\u1ebd t\u1ef1 \u0111\u1ed9ng lo\u1ea1i b\u1ecf nh\u1eefng \u00f4 c\u00f3 l\u1ed7i, ch\u1ec9 t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb h\u1ee3p l\u1ec7 c\u00f2n l\u1ea1i trong v\u00f9ng B4:B14, v\u00e0 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 ch\u00ednh x\u00e1c l\u00e0 441.<\/span><\/p>\n

\u0110i\u1ec1u n\u00e0y \u0111\u1eb7c bi\u1ec7t h\u1eefu \u00edch khi b\u1ea1n \u0111ang x\u1eed l\u00fd c\u00e1c b\u1ea3ng d\u1eef li\u1ec7u l\u1edbn, n\u01a1i kh\u00f3 ki\u1ec3m so\u00e1t ho\u00e0n to\u00e0n l\u1ed7i ph\u00e1t sinh. H\u00e0m n\u00e0y kh\u00f4ng ch\u1ec9 gi\u00fap b\u1ea1n tr\u00e1nh b\u1ecb gi\u00e1n \u0111o\u1ea1n do l\u1ed7i, m\u00e0 c\u00f2n duy tr\u00ec hi\u1ec7u qu\u1ea3 v\u00e0 \u0111\u1ed9 tin c\u1eady trong qu\u00e1 tr\u00ecnh t\u00ednh to\u00e1n tr\u00ean Excel.\u00a0<\/span><\/p>\n

V\u00ed d\u1ee5 2<\/b><\/h3>\n

Khi d\u00f9ng h\u00e0m t\u1ed5ng h\u1ee3p v\u1edbi function_num = 9 (t\u01b0\u01a1ng \u1ee9ng v\u1edbi ph\u00e9p t\u00ednh t\u1ed5ng) v\u00e0 options = 4, b\u1ea1n \u0111\u00e3 ch\u1ecdn kh\u00f4ng b\u1ecf qua b\u1ea5t k\u1ef3 y\u1ebfu t\u1ed1 n\u00e0o, bao g\u1ed3m c\u1ea3 l\u1ed7i xu\u1ea5t hi\u1ec7n trong ph\u1ea1m vi \u0111\u01b0\u1ee3c ch\u1ec9 \u0111\u1ecbnh.<\/span><\/p>\n

C\u00f4ng th\u1ee9c th\u1ef1c hi\u1ec7n: <\/span>=AGGREGATE(9,4,B4:B14)<\/b><\/p>\n

\"aggregate-4\"<\/picture><\/p>\n

Do thi\u1ebft l\u1eadp options = 4 kh\u00f4ng y\u00eau c\u1ea7u b\u1ecf qua l\u1ed7i, n\u00ean h\u00e0m s\u1ebd c\u1ed1 g\u1eafng x\u1eed l\u00fd to\u00e0n b\u1ed9 d\u1eef li\u1ec7u trong ph\u1ea1m vi, bao g\u1ed3m c\u1ea3 c\u00e1c \u00f4 sai. K\u1ebft qu\u1ea3 l\u00e0 c\u00f4ng th\u1ee9c kh\u00f4ng th\u1ec3 ho\u00e0n th\u00e0nh ph\u00e9p t\u00ednh t\u1ed5ng, v\u00e0 s\u1ebd tr\u1ea3 v\u1ec1 l\u1ed7i t\u01b0\u01a1ng t\u1ef1 nh\u01b0 khi d\u00f9ng h\u00e0m SUM trong tr\u01b0\u1eddng h\u1ee3p c\u00f3 gi\u00e1 tr\u1ecb sai trong v\u00f9ng.<\/span><\/p>\n

K\u1ebft lu\u1eadn t\u1eeb v\u00ed d\u1ee5 n\u00e0y cho th\u1ea5y, khi x\u1eed l\u00fd b\u1ea3ng t\u00ednh c\u00f3 th\u1ec3 ph\u00e1t sinh l\u1ed7i, l\u1ef1a ch\u1ecdn gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p cho tham s\u1ed1 options l\u00e0 r\u1ea5t quan tr\u1ecdng. N\u1ebfu kh\u00f4ng ch\u1ee7 \u0111\u1ed9ng lo\u1ea1i b\u1ecf l\u1ed7i, k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 c\u00f3 th\u1ec3 kh\u00f4ng kh\u1ea3 d\u1ee5ng, g\u00e2y \u1ea3nh h\u01b0\u1edfng \u0111\u1ebfn to\u00e0n b\u1ed9 ph\u00e9p t\u00ednh. \u0110\u00e2y l\u00e0 l\u00fd do t\u1ea1i sao h\u00e0m t\u1eadp h\u1ee3p l\u1ea1i c\u00f3 l\u1ee3i th\u1ebf l\u1edbn trong vi\u1ec7c ki\u1ec3m so\u00e1t d\u1eef li\u1ec7u ph\u1ee9c t\u1ea1p v\u00e0 kh\u00f4ng ho\u00e0n h\u1ea3o.<\/span><\/p>\n

V\u00ed d\u1ee5 3<\/b><\/h3>\n

V\u00ed d\u1ee5 c\u1ee5 th\u1ec3, b\u1ea1n c\u00f3 m\u1ed9t v\u00f9ng d\u1eef li\u1ec7u t\u1eeb \u00f4 B4 \u0111\u1ebfn B14, trong \u0111\u00f3 m\u1ed9t s\u1ed1 \u00f4 ch\u1ee9a l\u1ed7i. Thay v\u00ec d\u00f9ng h\u00e0m MAX, b\u1ea1n s\u1ebd \u00e1p d\u1ee5ng h\u00e0m t\u1ed5ng h\u1ee3p v\u1edbi function_num = 4 (\u0111\u1ea1i di\u1ec7n cho ph\u00e9p t\u00ednh gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t) v\u00e0 options = 6 (thi\u1ebft l\u1eadp b\u1ecf qua c\u00e1c gi\u00e1 tr\u1ecb l\u1ed7i).<\/span><\/p>\n

C\u00f4ng th\u1ee9c nh\u01b0 sau: <\/span>=AGGREGATE(4,6,B4:B14)<\/b><\/p>\n

\"aggregate-5\"<\/picture><\/p>\n

Nh\u1edd v\u00e0o t\u00f9y ch\u1ecdn n\u00e0y, h\u00e0m s\u1ebd t\u1ef1 \u0111\u1ed9ng b\u1ecf qua c\u00e1c \u00f4 c\u00f3 l\u1ed7i v\u00e0 ch\u1ec9 x\u00e9t c\u00e1c gi\u00e1 tr\u1ecb h\u1ee3p l\u1ec7 trong v\u00f9ng d\u1eef li\u1ec7u. K\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 l\u00e0 102, ch\u00ednh l\u00e0 s\u1ed1 l\u1edbn nh\u1ea5t trong danh s\u00e1ch kh\u00f4ng b\u1ecb \u1ea3nh h\u01b0\u1edfng b\u1edfi c\u00e1c \u00f4 sai.<\/span><\/p>\n

V\u00ed d\u1ee5 n\u00e0y cho th\u1ea5y h\u00e0m g\u1ed9p kh\u00f4ng ch\u1ec9 h\u1eefu \u00edch khi c\u1ed9ng t\u1ed5ng hay \u0111\u1ebfm s\u1ed1 li\u1ec7u, m\u00e0 c\u00f2n r\u1ea5t hi\u1ec7u qu\u1ea3 trong c\u00e1c ph\u00e9p so s\u00e1nh nh\u01b0 t\u00ecm gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t. B\u1eb1ng c\u00e1ch linh ho\u1ea1t l\u1ef1a ch\u1ecdn lo\u1ea1i ph\u00e9p t\u00ednh v\u00e0 c\u00e1ch x\u1eed l\u00fd d\u1eef li\u1ec7u l\u1ed7i, h\u00e0m n\u00e0y s\u1ebd gi\u00fap \u0111\u1ea3m b\u1ea3o \u0111\u1ed9 tin c\u1eady v\u00e0 t\u00ednh ch\u00ednh x\u00e1c trong m\u1ecdi t\u00ecnh hu\u1ed1ng ph\u00e2n t\u00edch d\u1eef li\u1ec7u tr\u00ean Excel.<\/span><\/p>\n

V\u00ed d\u1ee5 4<\/b><\/h3>\n

Trong v\u00ed d\u1ee5 n\u00e0y, v\u00f9ng d\u1eef li\u1ec7u t\u1eeb \u00f4 B4 \u0111\u1ebfn B14 c\u00f3 th\u1ec3 c\u00f3 m\u1ed9t s\u1ed1 \u00f4 g\u1eb7p l\u1ed7i ho\u1eb7c d\u1eef li\u1ec7u kh\u00f4ng h\u1ee3p l\u1ec7. Thay v\u00ec s\u1eed d\u1ee5ng h\u00e0m AVERAGE truy\u1ec1n th\u1ed1ng, h\u00e0m t\u1ed5ng h\u1ee3p v\u1edbi tham s\u1ed1 function_num = 1 (\u0111\u1ea1i di\u1ec7n cho ph\u00e9p t\u00ednh trung b\u00ecnh) v\u00e0 options = 6 (b\u1ecf qua c\u00e1c gi\u00e1 tr\u1ecb l\u1ed7i) s\u1ebd gi\u00fap lo\u1ea1i tr\u1eeb nh\u1eefng \u00f4 sai kh\u1ecfi qu\u00e1 tr\u00ecnh t\u00ednh to\u00e1n.<\/span><\/p>\n

C\u00f4ng th\u1ee9c \u00e1p d\u1ee5ng nh\u01b0 sau: <\/span>=AGGREGATE(1,6,B4:B14)<\/b><\/p>\n

\"aggregate-6\"<\/picture><\/p>\n

K\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 l\u00e0 55.125, \u0111\u00e2y l\u00e0 gi\u00e1 tr\u1ecb trung b\u00ecnh ch\u00ednh x\u00e1c c\u1ee7a c\u00e1c \u00f4 h\u1ee3p l\u1ec7 trong v\u00f9ng B4:B14, kh\u00f4ng b\u1ecb \u1ea3nh h\u01b0\u1edfng b\u1edfi nh\u1eefng \u00f4 c\u00f3 l\u1ed7i. Nh\u1edd \u0111\u00f3, b\u1ea1n c\u00f3 th\u1ec3 y\u00ean t\u00e2m r\u1eb1ng ph\u00e9p t\u00ednh s\u1ebd lu\u00f4n ph\u1ea3n \u00e1nh \u0111\u00fang th\u1ef1c t\u1ebf d\u1eef li\u1ec7u m\u00e0 kh\u00f4ng b\u1ecb gi\u00e1n \u0111o\u1ea1n b\u1edfi l\u1ed7i.<\/span><\/p>\n

V\u00ed d\u1ee5 n\u00e0y minh h\u1ecda r\u00f5 \u01b0u \u0111i\u1ec3m n\u1ed5i b\u1eadt c\u1ee7a h\u00e0m t\u1eadp h\u1ee3p trong vi\u1ec7c x\u1eed l\u00fd c\u00e1c b\u1ea3ng t\u00ednh ph\u1ee9c t\u1ea1p, gi\u00fap ng\u01b0\u1eddi d\u00f9ng ti\u1ebft ki\u1ec7m th\u1eddi gian v\u00e0 tr\u00e1nh c\u00e1c sai s\u00f3t th\u01b0\u1eddng g\u1eb7p khi l\u00e0m vi\u1ec7c v\u1edbi d\u1eef li\u1ec7u c\u00f3 v\u1ea5n \u0111\u1ec1.<\/span><\/p>\n

L\u01b0u \u00fd khi s\u1eed d\u1ee5ng h\u00e0m AGGREGATE<\/b><\/h2>\n

\u0110\u1ea7u ti\u00ean, khi b\u1eaft \u0111\u1ea7u nh\u1eadp c\u00f4ng th\u1ee9c h\u00e0m, Excel s\u1ebd t\u1ef1 \u0111\u1ed9ng hi\u1ec3n th\u1ecb b\u1ea3ng function_num, gi\u00fap b\u1ea1n d\u1ec5 d\u00e0ng l\u1ef1a ch\u1ecdn lo\u1ea1i h\u00e0m t\u1ed5ng h\u1ee3p ph\u00f9 h\u1ee3p v\u1edbi nhu c\u1ea7u. Ti\u1ebfp theo, sau khi ch\u1ecdn m\u1ed9t gi\u00e1 tr\u1ecb function_num v\u00e0 nh\u1eadp d\u1ea5u ph\u1ea9y \u201c,\u201d, b\u1ea3ng options c\u0169ng s\u1ebd xu\u1ea5t hi\u1ec7n \u0111\u1ec3 b\u1ea1n thi\u1ebft l\u1eadp c\u00e1ch x\u1eed l\u00fd d\u1eef li\u1ec7u nh\u01b0 b\u1ecf qua l\u1ed7i hay h\u00e0ng \u1ea9n.<\/span><\/p>\n

\"aggregate-7\"<\/picture><\/p>\n

M\u1ed9t l\u1ed7i ph\u1ed5 bi\u1ebfn th\u01b0\u1eddng g\u1eb7p l\u00e0 khi kh\u00f4ng cung c\u1ea5p \u0111\u1ee7 \u0111\u1ed1i s\u1ed1 b\u1eaft bu\u1ed9c nh\u01b0 ref1 ho\u1eb7c th\u00eam c\u00e1c v\u00f9ng d\u1eef li\u1ec7u nh\u01b0 ref2 n\u1ebfu c\u1ea7n thi\u1ebft, h\u00e0m t\u1ed5ng h\u1ee3p s\u1ebd tr\u1ea3 v\u1ec1 l\u1ed7i #VALUE!. V\u00ec v\u1eady, h\u00e3y ch\u1eafc ch\u1eafn r\u1eb1ng b\u1ea1n \u0111\u00e3 nh\u1eadp \u0111\u1ea7y \u0111\u1ee7 c\u00e1c tham s\u1ed1 c\u1ea7n thi\u1ebft \u0111\u1ec3 c\u00f4ng th\u1ee9c ho\u1ea1t \u0111\u1ed9ng ch\u00ednh x\u00e1c.<\/span><\/p>\n

Ngo\u00e0i ra, h\u00e0m n\u00e0y kh\u00f4ng h\u1ed7 tr\u1ee3 tham chi\u1ebfu 3D, ngh\u0129a l\u00e0 kh\u00f4ng th\u1ec3 \u00e1p d\u1ee5ng cho v\u00f9ng d\u1eef li\u1ec7u tr\u1ea3i d\u00e0i tr\u00ean nhi\u1ec1u sheet kh\u00e1c nhau c\u00f9ng l\u00fac. B\u1ea1n c\u0169ng c\u1ea7n l\u01b0u \u00fd r\u1eb1ng h\u00e0m t\u1ed5ng h\u1ee3p \u0111\u01b0\u1ee3c thi\u1ebft k\u1ebf \u0111\u1ec3 l\u00e0m vi\u1ec7c t\u1ed1t nh\u1ea5t v\u1edbi d\u1eef li\u1ec7u theo chi\u1ec1u d\u1ecdc (c\u1ed9t), kh\u00f4ng th\u00edch h\u1ee3p khi \u00e1p d\u1ee5ng cho c\u00e1c v\u00f9ng theo chi\u1ec1u ngang (h\u00e0ng).<\/span><\/p>\n

K\u1ebft lu\u1eadn<\/b><\/h2>\n

Vi\u1ec7c n\u1eafm v\u1eefng c\u00fa ph\u00e1p, b\u1ea3ng function_num v\u00e0 options s\u1ebd gi\u00fap b\u1ea1n khai th\u00e1c t\u1ed1i \u0111a s\u1ee9c m\u1ea1nh c\u1ee7a h\u00e0m <\/span>AGGREGATE <\/b>trong c\u00e1c b\u00e0i to\u00e1n ph\u00e2n t\u00edch d\u1eef li\u1ec7u. T\u00f3m l\u1ea1i, h\u00e0m t\u1ed5ng h\u1ee3p kh\u00f4ng ch\u1ec9 h\u1ed7 tr\u1ee3 x\u1eed l\u00fd d\u1eef li\u1ec7u ch\u00ednh x\u00e1c h\u01a1n m\u00e0 c\u00f2n gi\u00fap \u0111\u01a1n gi\u1ea3n h\u00f3a c\u00e1c thao t\u00e1c ph\u1ee9c t\u1ea1p, l\u00e0 m\u1ed9t trong nh\u1eefng h\u00e0m kh\u00f4ng th\u1ec3 thi\u1ebfu \u0111\u1ed1i v\u1edbi ng\u01b0\u1eddi l\u00e0m vi\u1ec7c chuy\u00ean s\u00e2u tr\u00ean Excel. Hy v\u1ecdng v\u1edbi nh\u1eefng n\u1ed9i dung m\u00e0 Ho\u00e0ng H\u00e0 Mobile chia s\u1ebb tr\u00ean \u0111\u00e2y s\u1ebd gi\u00fap b\u1ea1n \u00e1p d\u1ee5ng c\u00f4ng th\u1ee9c h\u00e0m Excel hi\u1ec7u qu\u1ea3, c\u0169ng nh\u01b0 x\u1eed l\u00fd data t\u1ed1i \u01b0u h\u01a1n nh\u00e9.<\/span><\/p>\n

Xem th\u00eam:<\/b><\/p>\n