{"id":330037,"date":"2024-03-30T15:28:26","date_gmt":"2024-03-30T08:28:26","guid":{"rendered":"https:\/\/hoanghamobile.com\/tin-tuc\/?p=330037"},"modified":"2024-03-30T15:28:26","modified_gmt":"2024-03-30T08:28:26","slug":"ham-sumproduct","status":"publish","type":"post","link":"https:\/\/hoanghamobile.com\/tin-tuc\/ham-sumproduct\/","title":{"rendered":"H\u00e0m SUMPRODUCT trong Excel: C\u00e1ch t\u00ednh t\u1ed5ng t\u00edch \u0111\u01a1n gi\u1ea3n d\u1ec5 hi\u1ec3u"},"content":{"rendered":"

SUMPRODUCT l\u00e0 m\u1ed9t h\u00e0m c\u01a1 b\u1ea3n v\u00e0 kh\u00f4ng qu\u00e1 kh\u00f3 \u0111\u1ec3 s\u1eed d\u1ee5ng trong Excel. Theo \u0111\u00f3, h\u00e0m n\u00e0y c\u00f3 th\u1ec3 gi\u00fap b\u1ea1n t\u00ednh t\u1ed5ng t\u00edch d\u1ef1a tr\u00ean nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n kh\u00e1c nhau. T\u1eeb \u0111\u00f3, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m linh ho\u1ea1t \u0111\u1ec3 ph\u1ee5c v\u1ee5 cho c\u00f4ng vi\u1ec7c v\u00e0 h\u1ecdc t\u1eadp. N\u1ebfu b\u1ea1n mu\u1ed1n t\u00ecm hi\u1ec3u chi ti\u1ebft h\u01a1n v\u00ea fhafm SUMPRODUCT trong Excel th\u00ec h\u00e3y d\u00e0nh \u00edt ph\u00fat xem qua b\u00e0i vi\u1ebft c\u1ee7a Ho\u00e0ng H\u00e0 Mobile nh\u00e9.<\/span><\/p>\n

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

SUMPRODUCT trong Excel l\u00e0 m\u1ed9t c\u00f4ng c\u1ee5 m\u1ea1nh m\u1ebd \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng r\u1ed9ng r\u00e3i \u0111\u1ec3 t\u00ednh t\u1ed5ng c\u1ee7a c\u00e1c t\u00edch c\u1ee7a c\u00e1c ph\u1ea7n t\u1eed t\u01b0\u01a1ng \u1ee9ng t\u1eeb c\u00e1c m\u1ea3ng ho\u1eb7c ph\u1ea1m vi d\u1eef li\u1ec7u. H\u00e0m \u0111<\/span>\u01b0\u1ee3c thi\u1ebft k\u1ebf \u0111\u1ec3 x\u1eed l\u00fd c\u00e1c t\u00ecnh hu\u1ed1ng ph\u1ee9c t\u1ea1p trong t\u00ednh to\u00e1n v\u00e0 ph\u00e2n t\u00edch d\u1eef li\u1ec7u. SUMPRODUCT cung c\u1ea5p kh\u1ea3 n\u0103ng \u0111a d\u1ea1ng trong vi\u1ec7c th\u1ef1c hi\u1ec7n c\u00e1c ph\u00e9p to\u00e1n \u0111a chi\u1ec1u.<\/span><\/p>\n

\"ham-sumproduct-1\"<\/picture><\/p>\n

Ngo\u00e0i vi\u1ec7c t\u00ednh t\u1ed5ng s\u1ea3n ph\u1ea9m c\u1ee7a c\u00e1c gi\u00e1 tr\u1ecb, h\u00e0m n\u00e0y c\u00f2n h\u1ed7 tr\u1ee3 trong vi\u1ec7c t\u1ea1o c\u00e1c b\u00e1o c\u00e1o ph\u00e2n t\u00edch d\u1eef li\u1ec7u ph\u1ee9c t\u1ea1p b\u1eb1ng c\u00e1ch k\u1ebft h\u1ee3p v\u00e0 t\u00ednh to\u00e1n d\u1eef li\u1ec7u t\u1eeb nhi\u1ec1u ph\u1ea1m vi d\u1eef li\u1ec7u kh\u00e1c nhau. B\u1eb1ng c\u00e1ch k\u1ebft h\u1ee3p v\u1edbi c\u00e1c h\u00e0m kh\u00e1c nh\u01b0 IF, SUMIF hay COUNTIF. Ng\u01b0\u1eddi d\u00f9ng c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n c\u00e1c t\u00ednh to\u00e1n ph\u1ee9c t\u1ea1p d\u1ef1a tr\u00ean m\u1ed9t s\u1ed1 ti\u00eau ch\u00ed nh\u1ea5t \u0111\u1ecbnh.<\/span><\/p>\n

L\u1ee3i \u00edch c\u1ee7a h\u00e0m SUMPRODUCT<\/b><\/h2>\n

Nh\u01b0 th\u00f4ng tin ph\u1ea7n kh\u00e1i ni\u1ec7m, Ho\u00e0ng H\u00e0 Mobile c\u00f3 nh\u1eafc \u0111\u1ebfn vi\u1ec7c SUMPRODUCT s\u1ebd gi\u00fap ng\u01b0\u1eddi d\u00f9ng t\u00ednh to\u00e1n \u0111\u01b0\u1ee3c s\u1ed1 li\u1ec7u trong m\u1ed9t ph\u1ea1m vi nh\u1ea5t \u0111\u1ecbnh. Th\u00eam v\u00e0o \u0111\u00f3, vi\u1ec7c s\u1eed d\u1ee5ng h\u00e0m n\u00e0y c\u00f2n mang \u0111\u1ebfn nhi\u1ec1u l\u1ee3i \u00edch kh\u00e1c m\u00e0 b\u1ea1n c\u1ea7n bi\u1ebft \u0111\u1ebfn.<\/span><\/p>\n

T\u00ednh linh ho\u1ea1t<\/b><\/h3>\n

D\u1ef1a v\u00e0o c\u00f4ng th\u1ee9c c\u1ee7a h\u00e0m, b\u1ea1n c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n c\u00e1c ph\u00e9p to\u00e1n ph\u1ee9c t\u1ea1p m\u00e0 c\u1ea7n t\u00ednh to\u00e1n d\u1ef1a tr\u00ean nhi\u1ec1u m\u1ea3ng d\u1eef li\u1ec7u c\u00f9ng m\u1ed9t l\u00fac. V\u00ed d\u1ee5, b\u1ea1n c\u00f3 th\u1ec3 t\u00ednh t\u1ed5ng doanh thu b\u1eb1ng c\u00e1ch nh\u00e2n s\u1ed1 l\u01b0\u1ee3ng s\u1ea3n ph\u1ea9m b\u00e1n \u0111\u01b0\u1ee3c v\u1edbi gi\u00e1 b\u00e1n c\u1ee7a t\u1eebng s\u1ea3n ph\u1ea9m t\u01b0\u01a1ng \u1ee9ng. T\u00ednh linh ho\u1ea1t n\u00e0y gi\u00fap ng\u01b0\u1eddi d\u00f9ng x\u1eed l\u00fd c\u00e1c t\u00ecnh hu\u1ed1ng ph\u1ee9c t\u1ea1p trong ph\u00e2n t\u00edch d\u1eef li\u1ec7u m\u1ed9t c\u00e1ch d\u1ec5 d\u00e0ng v\u00e0 hi\u1ec7u qu\u1ea3.<\/span><\/p>\n

\"ham-sumproduct-2\"<\/picture><\/p>\n

B\u00ean c\u1ea1nh \u0111\u00f3, SUMPRODUCT kh\u00f4ng ch\u1ec9 gi\u1edbi h\u1ea1n trong vi\u1ec7c t\u00ednh t\u1ed5ng c\u1ee7a c\u00e1c t\u00edch, m\u00e0 c\u00f2n c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u0111\u1ec3 th\u1ef1c hi\u1ec7n c\u00e1c t\u00ednh to\u00e1n kh\u00e1c. Ch\u1eb3ng h\u1ea1n nh\u01b0 t\u00edch v\u00f4 h\u01b0\u1edbng, t\u00ednh to\u00e1n trung b\u00ecnh tr\u1ecdng s\u1ed1,… T\u1eeb \u0111\u00f3 m\u1edf ra m\u1ed9t lo\u1ea1t c\u00e1c \u1ee9ng d\u1ee5ng v\u00e0 c\u01a1 h\u1ed9i s\u00e1ng t\u1ea1o trong vi\u1ec7c ph\u00e2n t\u00edch d\u1eef li\u1ec7u v\u00e0 t\u1ea1o b\u00e1o c\u00e1o.<\/span><\/p>\n

X\u1eed l\u00fd \u0111i\u1ec1u ki\u1ec7n<\/b><\/h3>\n

M\u1ed9t \u1ee9ng d\u1ee5ng ph\u1ed5 bi\u1ebfn kh\u00e1c c\u1ee7a <\/span>h\u00e0m SUMPRODUCT<\/b> l\u00e0 trong vi\u1ec7c th\u1ef1c hi\u1ec7n c\u00e1c ph\u00e9p to\u00e1n \u0111a \u0111i\u1ec1u ki\u1ec7n. SUMPRODUCT cho ph\u00e9p b\u1ea1n k\u1ebft h\u1ee3p nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n l\u1ea1i v\u1edbi nhau b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng to\u00e1n t\u1eed logic nh\u01b0 AND v\u00e0 OR. Nh\u01b0 v\u1eady, b\u1ea1n s\u1ebd ki\u1ec3m so\u00e1t \u0111\u01b0\u1ee3c c\u00e1c \u0111i\u1ec1u ki\u1ec7n ph\u1ee9c t\u1ea1p v\u00e0 ph\u00e2n t\u00edch d\u1eef li\u1ec7u theo c\u00e1ch t\u1ed5 ch\u1ee9c ph\u1ee9c t\u1ea1p h\u01a1n. V\u00ed d\u1ee5, b\u1ea1n c\u00f3 th\u1ec3 t\u00ednh t\u1ed5ng doanh thu t\u1eeb s\u1ea3n ph\u1ea9m A ch\u1ec9 trong nh\u1eefng th\u00e1ng c\u00f3 doanh thu cao h\u01a1n trung b\u00ecnh v\u00e0 gi\u00e1 b\u00e1n cao h\u01a1n m\u1ed9t ng\u01b0\u1ee1ng nh\u1ea5t \u0111\u1ecbnh.<\/span><\/p>\n

\"ham-sumproduct-3\"<\/picture><\/p>\n

\u0110\u1ed3ng th\u1eddi, SUMPRODUCT c\u0169ng h\u1ed7 tr\u1ee3 vi\u1ec7c s\u1eed d\u1ee5ng c\u00e1c h\u00e0m \u0111i\u1ec1u ki\u1ec7n kh\u00e1c nh\u01b0 IF v\u00e0 COUNTIF b\u00ean trong n\u00f3. V\u00ed d\u1ee5, b\u1ea1n c\u00f3 th\u1ec3 t\u00ednh t\u1ed5ng s\u1ed1 l\u01b0\u1ee3ng s\u1ea3n ph\u1ea9m \u0111\u00e3 b\u00e1n trong m\u1ed9t khu v\u1ef1c c\u1ee5 th\u1ec3 ch\u1ec9 trong nh\u1eefng th\u00e1ng c\u00f3 s\u1ed1 l\u01b0\u1ee3ng b\u00e1n cao h\u01a1n m\u1ed9t gi\u00e1 tr\u1ecb \u0111\u1ecbnh s\u1eb5n.<\/span><\/p>\n

T\u0103ng hi\u1ec7u su\u1ea5t t\u00ednh to\u00e1n<\/b><\/h3>\n

Thay v\u00ec ph\u1ea3i s\u1eed d\u1ee5ng nhi\u1ec1u c\u00f4ng th\u1ee9c ri\u00eang l\u1ebb \u0111\u1ec3 t\u00ednh to\u00e1n t\u1eebng ph\u1ea7n t\u1eed v\u00e0 sau \u0111\u00f3 k\u1ebft h\u1ee3p k\u1ebft qu\u1ea3. B\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng SUMPRODUCT \u0111\u1ec3 th\u1ef1c hi\u1ec7n c\u00e1c ph\u00e9p to\u00e1n n\u00e0y v\u00e0 \u0111\u1ea1t \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 nhanh ch\u00f3ng h\u01a1n. V\u00f4 h\u00ecnh chung, n\u00f3 gi\u00fap ng\u01b0\u1eddi d\u00f9ng ti\u1ebft ki\u1ec7m \u0111\u01b0\u1ee3c nhi\u1ec1u th\u1eddi gian v\u00e0 t\u0103ng hi\u1ec7u su\u1ea5t l\u00e0m vi\u1ec7c.<\/span><\/p>\n

\"ham-sumproduct-4\"<\/picture><\/p>\n

Song song \u0111\u00f3, SUMPRODUCT c\u0169ng gi\u00fap gi\u1ea3m b\u1edbt s\u1ed1 l\u01b0\u1ee3ng c\u00f4ng th\u1ee9c c\u1ea7n s\u1eed d\u1ee5ng trong b\u1ea3ng t\u00ednh Excel. Ng\u01b0\u1eddi d\u00f9ng ch\u1ec9 c\u1ea7n s\u1eed d\u1ee5ng m\u1ed9t h\u00e0m duy nh\u1ea5t l\u00e0 \u0111\u00e3 ghi nh\u1eadn \u0111\u01b0\u1ee3c con s\u1ed1 cu\u1ed1i c\u00f9ng. H\u01a1n n\u1eefa, b\u1ea1n c\u0169ng c\u00f3 th\u1ec3 \u00e1p d\u1ee5ng h\u00e0m v\u1edbi c\u00e1c ti\u00eau ch\u00ed ph\u00e2n lo\u1ea1i d\u1eef li\u1ec7u. \u0110\u1ec3 t\u1eeb \u0111\u00f3, n\u00f3 s\u1ebd tr\u1edf th\u00e0nh m\u1ed9t \u201cth\u01b0 k\u00fd\u201d gi\u00fap b\u1ea1n c\u00f3 \u0111\u01b0\u1ee3c nh\u1eefng d\u1eef li\u1ec7u chu\u1ea9n x\u00e1c nh\u1ea5t.<\/span><\/p>\n

S\u1eed d\u1ee5ng trong nhi\u1ec1u l\u0129nh v\u1ef1c<\/b><\/h3>\n

Trong l\u0129nh v\u1ef1c t\u00e0i ch\u00ednh v\u00e0 k\u1ebf to\u00e1n, <\/span>h\u00e0m SUMPRODUCT<\/b> th\u01b0\u1eddng \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u0111\u1ec3 th\u1ef1c hi\u1ec7n c\u00e1c t\u00ednh to\u00e1n ph\u1ee9c t\u1ea1p nh\u01b0 t\u00ednh t\u1ed5ng doanh thu, l\u1ee3i nhu\u1eadn ho\u1eb7c chi ph\u00ed t\u1eeb c\u00e1c b\u1ed9 d\u1eef li\u1ec7u kh\u00e1c nhau. Th\u00f4ng qua vi\u1ec7c k\u1ebft h\u1ee3p c\u00e1c m\u1ea3ng d\u1eef li\u1ec7u ch\u1ee9a th\u00f4ng tin v\u1ec1 doanh s\u1ed1 b\u00e1n h\u00e0ng, gi\u00e1 c\u1ea3 v\u00e0 c\u00e1c y\u1ebfu t\u1ed1 kh\u00e1c. H\u00e0m n\u00e0y gi\u00fap ng\u01b0\u1eddi d\u00f9ng ph\u00e2n t\u00edch v\u00e0 t\u1ed5ng h\u1ee3p th\u00f4ng tin m\u1ed9t c\u00e1ch hi\u1ec7u qu\u1ea3, t\u1eeb \u0111\u00f3 h\u1ed7 tr\u1ee3 quy\u1ebft \u0111\u1ecbnh kinh doanh.<\/span><\/p>\n

\"ham-sumproduct-5\"<\/picture><\/p>\n

Th\u00eam v\u00e0o \u0111\u00f3, trong qu\u1ea3n l\u00fd d\u1ef1 \u00e1n SUMPRODUCT \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u0111\u1ec3 t\u00ednh to\u00e1n c\u00e1c ch\u1ec9 s\u1ed1 ho\u1eb7c ch\u1ec9 ti\u00eau quan tr\u1ecdng trong vi\u1ec7c \u0111\u00e1nh gi\u00e1 ti\u1ebfn \u0111\u1ed9 d\u1ef1 \u00e1n. V\u00ed d\u1ee5, ng\u01b0\u1eddi qu\u1ea3n l\u00fd d\u1ef1 \u00e1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m n\u00e0y \u0111\u1ec3 t\u00ednh t\u1ed5ng s\u1ed1 gi\u1edd l\u00e0m vi\u1ec7c \u0111\u00e3 d\u00e0nh cho m\u1ed7i c\u00f4ng vi\u1ec7c. Ho\u1eb7c \u0111\u1ec3 t\u00ednh t\u1ed5ng chi ph\u00ed d\u1ef1 \u00e1n d\u1ef1a tr\u00ean c\u00e1c y\u1ebfu t\u1ed1 nh\u01b0 s\u1ed1 l\u01b0\u1ee3ng ng\u00e0y l\u00e0m vi\u1ec7c v\u00e0 chi ph\u00ed m\u1ed7i ng\u00e0y.<\/span><\/p>\n

Ngo\u00e0i ra, trong khoa h\u1ecdc d\u1eef li\u1ec7u v\u00e0 ph\u00e2n t\u00edch s\u1ed1 li\u1ec7u, SUMPRODUCT l\u00e0 m\u1ed9t c\u00f4ng c\u1ee5 quan tr\u1ecdng \u0111\u1ec3 th\u1ef1c hi\u1ec7n ph\u00e2n t\u00edch th\u00f4ng s\u1ed1. Hay trong m\u00f4i tr\u01b0\u1eddng gi\u00e1o d\u1ee5c th\u00ec h\u00e0m n\u00e0y c\u0169ng h\u1eefu \u00edch trong vi\u1ec7c\u00a0 t\u00ednh \u0111i\u1ec3m t\u1ed5ng k\u1ebft c\u1ee7a h\u1ecdc sinh d\u1ef1a tr\u00ean c\u00e1c th\u00e0nh ph\u1ea7n \u0111i\u1ec3m s\u1ed1 kh\u00e1c nhau, nh\u01b0 \u0111i\u1ec3m s\u1ed1 t\u1eeb c\u00e1c b\u00e0i ki\u1ec3m tra, b\u00e0i t\u1eadp v\u00e0 d\u1ef1 \u00e1n.\u00a0<\/span><\/p>\n

C\u00f4ng th\u1ee9c h\u00e0m <\/b>SUMPRODUCT t\u00ednh t\u1ed5ng nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n l\u00e0 g\u00ec?<\/b><\/h2>\n

C\u00f4ng th\u1ee9c ch\u00ednh l\u00e0 \u201c<\/span>=SUMPRODUCT(m\u1ea3ng d\u1eef li\u1ec7u 1; m\u1ea3ng d\u1eef li\u1ec7u 2; m\u1ea3ng d\u1eef li\u1ec7u 3;…)<\/b>\u201d<\/span><\/p>\n

\"ham-sumproduct-6\"<\/picture><\/p>\n

Trong \u0111\u00f3, \u201carray1 – m\u1ea3ng d\u1eef li\u1ec7u 1\u201d l\u00e0 \u0111\u1ed1i s\u1ed1 b\u1eaft bu\u1ed9c ng\u01b0\u1eddi d\u00f9ng ph\u1ea3i nh\u1eadp \u0111\u00fang. B\u1edfi v\u00ec nh\u1edd v\u00e0o \u0111\u1ed1i s\u1ed1 \u0111\u1ea7u ti\u00ean n\u00e0y th\u00ec b\u1ea1n m\u1edbi t\u00ednh \u0111\u01b0\u1ee3c t\u1ea5t c\u1ea3 th\u00e0nh ph\u1ea7n v\u00e0 c\u1ed9ng ch\u00fang l\u1ea1i v\u1edbi nhau. Ti\u1ebfp t\u1ee5c, \u201carray2 – m\u1ea3ng d\u1eef li\u1ec7u\u00a0 2\u201d v\u00e0 \u201carray3 – m\u1ea3ng d\u1eef li\u1ec7u 3\u201d l\u00e0 ph\u1ea1m vi th\u00f4ng tin t\u1ef1 ch\u1ecdn, c\u00e1i m\u00e0 ng\u01b0\u1eddi d\u00f9ng mu\u1ed1n s\u1eed d\u1ee5ng \u0111\u1ec3 nh\u00e2n r\u1ed3i c\u1ed9ng t\u1ed5ng.<\/span><\/p>\n

H\u01b0\u1edbng d\u1eabn 3 c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m SUMPRODUCT \u0111\u1ec3 t\u00ednh to\u00e1n nhanh h\u01a1n<\/b><\/h2>\n

Nh\u1ea7m gi\u00fap cho b\u1ea1n \u0111\u1ecdc c\u00f3 th\u1ec3 d\u1ec5 h\u00ecnh dung h\u01a1n v\u1ec1 c\u00f4ng th\u1ee9c t\u00ednh SUMPRODUCT. Ngay sau \u0111\u00e2y, Ho\u00e0ng H\u00e0 Mobile s\u1ebd n\u00eau ra 3 v\u00ed d\u1ee5 th\u01b0\u1eddng \u00e1p d\u1ee5ng SUMPRODUCT \u0111\u1ec3 t\u00ednh to\u00e1n nh\u1ea5t. \u0110\u1eb7c bi\u1ec7t, ch\u00fang t\u00f4i c\u00f2n cung c\u1ea5p th\u00eam h\u00ecnh \u1ea3nh minh h\u1ecda \u0111\u1ec3 b\u1ea1n d\u1ec5 \u00e1p d\u1ee5ng b\u00e0i to\u00e1n c\u1ee7a m\u00ecnh nh\u00e9.<\/span><\/p>\n

C\u00e1ch 1: S\u1eed d\u1ee5ng h\u00e0m c\u01a1 b\u1ea3n<\/b><\/h3>\n

\u0110\u1ed1i v\u1edbi c\u00e1ch t\u00ednh c\u01a1 b\u1ea3n, vi\u1ec7c \u00e1p d\u1ee5ng c\u00f4ng th\u1ee9c c\u1ee7a SUMPRODUCT c\u1ef1c k\u1ef3 gi\u1ea3n \u0111\u01a1n. T\u1ea1i \u0111\u00e2y, ch\u00fang ta s\u1ebd c\u00f9ng nhau th\u1ef1c hi\u1ec7n qua 2 b\u01b0\u1edbc nh\u01b0 sau.<\/span><\/p>\n

V\u00ed d\u1ee5: <\/b>Ch\u00fang ta s\u1ebd c\u00f3 b\u1ea3ng th\u00f4ng tin chi ti\u1ebft b\u00ean d\u01b0\u1edbi \u0111\u00e2y. Nhi\u1ec7m v\u1ee5 c\u1ee7a b\u1ea1n l\u00e0 ph\u1ea3i t\u00ednh \u0111\u01b0\u1ee3c t\u1ed5ng th\u00e0nh ti\u1ec1n c\u1ee7a t\u1ea5t c\u1ea3 product khi mua s\u1eafm cho gia \u0111\u00ecnh.<\/span><\/p>\n

\"ham-sumproduct-7\"<\/picture><\/p>\n

B\u01b0\u1edbc 1: \u0110\u1ea7u ti\u00ean, ng\u01b0\u1eddi d\u00f9ng c\u1ea7n ph\u1ea3i t\u00ednh \u0111\u01b0\u1ee3c t\u1ed5ng t\u00edch c\u1ee7a hai c\u1ed9t \u0111\u01a1n gi\u00e1 v\u00e0 s\u1ed1 l\u01b0\u1ee3ng. Theo \u0111\u00f3, ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c l\u00e0 \u201c<\/span>=SUMPRODUCT (D4:D12,E4:E12)<\/b>\u201d.<\/span><\/p>\n

C\u1ee5 th\u1ec3 h\u01a1n th\u00ec D4:D12 l\u00e0 c\u1ed9t bi\u1ec3u th\u1ecb cho \u0111\u01a1n gi\u00e1 s\u1ea3n ph\u1ea9m. E4:E12 l\u00e0 c\u1ed9t th\u1ec3 hi\u1ec7n cho s\u1ed1 l\u01b0\u1ee3ng s\u1ea3n ph\u1ea9m m\u00e0 b\u1ea1n \u0111\u00e3 mua.<\/span><\/p>\n

\"ham-sumproduct-8\"<\/picture><\/p>\n

B\u01b0\u1edbc 2: Sau khi b\u1ea1n \u00e1p d\u1ee5ng \u0111\u00fang c\u00f4ng th\u1ee9c th\u00ec s\u1ebd \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 nh\u01b0 h\u00ecnh minh h\u1ecda c\u1ee7a ch\u00fang t\u00f4i.<\/span><\/p>\n

\"ham-sumproduct-9\"<\/picture><\/p>\n

C\u00e1ch 2: S\u1eed d\u1ee5ng h\u00e0m c\u00f3 m\u1ed9t \u0111i\u1ec1u ki\u1ec7n<\/b><\/h3>\n

\u0110\u1ed1i v\u1edbi <\/span>h\u00e0m SUMPRODUCT<\/b> ch\u1ec9 \u00e1p d\u1ee5ng tr\u00ean m\u1ed9t \u0111i\u1ec1u ki\u1ec7n duy nh\u1ea5t th\u00ec c\u00e1ch th\u1ee9c c\u1ee7a n\u00f3 t\u01b0\u01a1ng t\u1ef1 nh\u01b0 h\u00e0m SUMIF. Nh\u01b0ng \u0111i\u1ec3m n\u1ed5i b\u1eadt l\u00e0 c\u00f4ng th\u1ee9c s\u1eed d\u1ee5ng s\u1ebd \u0111\u01a1n gi\u1ea3n v\u00e0 truy k\u1ebft qu\u1ea3 nhanh ch\u00f3ng h\u01a1n.<\/span><\/p>\n

V\u00ed d\u1ee5: <\/b>Ch\u00fang ta ti\u1ebfp t\u1ee5c s\u1eed d\u1ee5ng l\u1ea1i b\u1ea3ng s\u1ed1 li\u1ec7u \u1edf c\u00e1ch 1 v\u00e0 t\u00ednh s\u1ed1 l\u01b0\u1ee3ng c\u00e1c s\u1ea3n ph\u1ea9m \u0111\u01b0\u1ee3c ph\u00e2n lo\u1ea1i theo \u0111i\u1ec1u ki\u1ec7n l\u00e0 \u201cgia d\u1ee5ng\u201d.<\/span><\/p>\n

B\u01b0\u1edbc 1: \u0110\u1ea7u ti\u00ean, ch\u00fang ta c\u1ea7n ch\u00fa \u00fd \u0111\u1ebfn 2 c\u1ed9t l\u00e0 lo\u1ea1i v\u00e0 s\u1ed1 l\u01b0\u1ee3ng. Sau \u0111\u00f3, b\u1ea1n s\u1ebd \u00e1p d\u1ee5ng c\u00f4ng th\u1ee9c \u201c<\/span>=SUMPRODUCT((D6:D14=D6)*H6:H14)<\/b>\u201d.\u00a0<\/span><\/p>\n

\"ham-sumproduct-10\"<\/picture><\/p>\n

Ho\u00e0ng H\u00e0 Mobile s\u1ebd gi\u1ea3i th\u00edch c\u00e1c s\u1ed1 li\u1ec7u nh\u01b0 sau. \u0110\u1ea7u ti\u00ean, ch\u00fang ta s\u1ebd l\u1ea5y d\u1eef li\u1ec7u D6:D14 l\u00e0 c\u1ed9t lo\u1ea1i. Ti\u1ebfp \u0111\u1ebfn v\u00ec \u0111\u1ec1 b\u00e0i l\u00e0 t\u00ednh t\u1ed5ng s\u1ed1 l\u01b0\u1ee3ng c\u1ee7a \u201cgia d\u1ee5ng\u201d n\u00ean ch\u00fang ta s\u1ebd \u0111i\u1ec1n ti\u1ebfp c\u00f4ng th\u1ee9c l\u00e0 \u201c=D6\u201d. Cu\u1ed1i c\u00f9ng l\u00e0 b\u1ea1n nh\u00e2n v\u1edbi c\u1ed9t s\u1ed1 l\u01b0\u1ee3ng H6:H14.<\/span><\/p>\n

B\u01b0\u1edbc 2: Sau khi \u0111\u00e3 \u00e1p d\u1ee5ng c\u00f4ng th\u1ee9c th\u00ec b\u1ea1n h\u00e3y nh\u1ea5n ch\u1ecdn \u201cEnter\u201d \u0111\u1ec3 nh\u1eadn \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 nh\u01b0 h\u00ecnh nh\u00e9.<\/span><\/p>\n

\"ham-sumproduct-11\"<\/picture><\/p>\n

C\u00e1ch 3: S\u1eed d\u1ee5ng h\u00e0m nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/b><\/h3>\n

\u0110\u1ed1i v\u1edbi vi\u1ec7c s\u1eed d\u1ee5ng <\/span>h\u00e0m SUMPRODUCT t\u00ednh t\u1ed5ng nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/b> th\u00ec b\u1ea1n c\u00f3 th\u1ec3 ch\u1ecdn m\u1ed9t trong hai h\u00ecnh th\u1ee9c. M\u1ed9t l\u00e0 s\u1eed d\u1ee5ng h\u00e0m \u0111\u1ed9c l\u1eadp v\u00e0 hai s\u1eed d\u1ee5ng h\u00e0m k\u1ebft h\u1ee3p v\u1edbi c\u00f4ng th\u1ee9c Excel kh\u00e1c. \u0110\u1ec3 gi\u00fap b\u1ea1n hi\u1ec3u r\u00f5 h\u01a1n v\u1ec1 t\u1eebng tr\u01b0\u1eddng h\u1ee3p \u00e1p d\u1ee5ng, Ho\u00e0ng H\u00e0 Mobile chia s\u1ebb r\u00f5 h\u01a1n v\u1ec1 th\u1ee7 thu\u1eadt m\u00e1y t\u00ednh n\u00e0y nh\u00e9.<\/span><\/p>\n

V\u00ed d\u1ee5 1: <\/b>Ch\u00fang ta c\u1ea7n ph\u1ea3i t\u00ednh \u0111\u01b0\u1ee3c t\u1ed5ng s\u1ed1 l\u01b0\u1ee3ng n\u1ed9i th\u1ea5t m\u00e0 ng\u01b0\u1eddi d\u00f9ng s\u1ebd \u0111\u01b0\u1ee3c t\u1eb7ng k\u00e8m v\u1edbi th\u1ea3m.<\/span><\/p>\n

B\u01b0\u1edbc 1: \u0110\u1ea7u ti\u00ean, ch\u00fang ta c\u1ee9 \u00e1p d\u1ee5ng l\u1ea7n l\u01b0\u1ee3t t\u1eebng \u0111i\u1ec1u ki\u1ec7n v\u00e0 c\u00f3 \u0111\u01b0\u1ee3c c\u00f4ng th\u1ee9c \u201c<\/span>=SUMPRODUCT((D6:D14=D7)*(E6:E14=E8)*H6:H14)<\/b>\u201d.\u00a0<\/span><\/p>\n

\"ham-sumproduct-12\"<\/picture><\/p>\n

B\u01b0\u1edbc 2: B\u1ea1n nh\u1ea5n ch\u1ecdn n\u00fat \u201cEnter\u201d \u0111\u1ec3 Excel t\u00ednh to\u00e1n c\u00f4ng th\u1ee9c cho b\u1ea1n v\u00e0 ghi nh\u1eadn \u0111\u01b0\u1ee3c d\u1eef li\u1ec7u nh\u01b0 sau.<\/span><\/p>\n

\"ham-sumproduct-13\"<\/picture><\/p>\n

V\u00ed d\u1ee5 2:<\/b> Ch\u00fang ta c\u1ea7n ph\u1ea3i t\u00ednh \u0111\u01b0\u1ee3c t\u1ed5ng s\u1ed1 l\u01b0\u1ee3ng ph\u1ee5 ki\u1ec7n hay th\u1ea3m m\u00e0 ng\u01b0\u1eddi d\u00f9ng s\u1ebd \u0111\u01b0\u1ee3c t\u1eb7ng k\u00e8m th\u00eam voucher. B\u1edfi \u0111\u00e2y c\u00f3 y\u00eau c\u1ea7u l\u00e0 \u201chay\u201d n\u00ean ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng \u201ch\u00e0m l\u1ed3ng h\u00e0m\u201d.<\/span><\/p>\n

B\u01b0\u1edbc 1: \u0110\u1ea7u ti\u00ean, ch\u00fang ta c\u1ea7n x\u00e1c \u0111\u1ecbnh \u0111\u01b0\u1ee3c l\u00e0 m\u00ecnh s\u1ebd k\u1ebft h\u1ee3p v\u1edbi h\u00e0m g\u00ec. D\u1ef1a v\u00e0o b\u00e0i to\u00e1n, ch\u00fang ta s\u1ebd k\u1ebft h\u1ee3p gi\u1eefa <\/span>h\u00e0m SUMPRODUCT v\u00e0 OR<\/b>. C\u00f4ng th\u1ee9c cu\u1ed1i c\u00f9ng m\u00e0 ch\u00fang ta c\u00f3 \u0111\u01b0\u1ee3c l\u00e0 \u201c=SUMPRODUCT(OR((D6:D14=D10),(D6:D14=D11))*(E6:E14=E11)*(H6:H14)\u201d.<\/span><\/p>\n

\"ham-sumproduct-14\"<\/picture><\/p>\n

B\u01b0\u1edbc 2: \u0110\u1ec3 nh\u1eadn \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 cu\u1ed1i c\u00f9ng th\u00ec b\u1ea1n ch\u1ec9 c\u1ea7n nh\u1ea5n ch\u1ecdn n\u00fat \u201cEnter\u201d l\u00e0 ho\u00e0n th\u00e0nh.<\/span><\/p>\n

\"ham-sumproduct-15\"<\/picture><\/p>\n

C\u00e1c l\u1ed7i th\u01b0\u1eddng g\u1eb7p khi s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c h\u00e0m SUMPRODUCT<\/b><\/h2>\n

SUMPRODUCT tuy l\u00e0 m\u1ed9t h\u00e0m d\u1ec5 s\u1eed d\u1ee5ng, nh\u01b0ng n\u1ebfu b\u1ea1n kh\u00f4ng bi\u1ebft c\u00e1ch d\u00f9ng \u0111\u00fang c\u00e1ch th\u00ec r\u1ea5t d\u1ec5 d\u1eabn \u0111\u1ebfn l\u1ed7i. Ngay sau \u0111\u00e2y l\u00e0 2 l\u1ed7i b\u1ea1n s\u1ebd th\u01b0\u1eddng g\u1eb7p nh\u1ea5t. H\u00e3y c\u00f9ng Ho\u00e0ng H\u00e0 Mobile t\u00ecm hi\u1ec3u ph\u1ea7n th\u00f4ng tin n\u00e0y \u0111\u1ec3 tr\u00e1nh g\u1eb7p l\u1ed7i t\u01b0\u01a1ng t\u1ef1 b\u1ea1n nh\u00e9.<\/span><\/p>\n

L\u1ed7i sai d\u1eef li\u1ec7u #VALUE<\/b><\/h3>\n

L\u1ed7i #VALUE th\u01b0\u1eddng xu\u1ea5t hi\u1ec7n khi c\u00f3 s\u1ef1 kh\u00f4ng ph\u00f9 h\u1ee3p trong d\u1eef li\u1ec7u \u0111\u1ea7u v\u00e0o ho\u1eb7c khi Excel kh\u00f4ng th\u1ec3 hi\u1ec3u ho\u1eb7c x\u1eed l\u00fd d\u1eef li\u1ec7u m\u1ed9t c\u00e1ch \u0111\u00fang c\u00e1ch. Ri\u00eang \u0111\u1ed1i v\u1edbi SUMPRODUCT th\u00ec khi c\u00e1c m\u1ea3ng d\u1eef li\u1ec7u c\u1ee7a h\u00e0m kh\u00f4ng c\u00f9ng k\u00edch th\u01b0\u1edbc th\u00ec r\u1ea5t d\u1ec5 d\u1eabn \u0111\u1ebfn l\u1ed7i n\u00e0y. \u0110\u1ec3 x\u1eed l\u00fd l\u1ed7i n\u00e0y th\u00ec b\u1ea1n c\u1ea7n gi\u1eef \u0111\u01b0\u1ee3c gi\u00e1 tr\u1ecb b\u1eb1ng nhau gi\u1eefa hai c\u1ed9t gi\u00e1 tr\u1ecb.<\/span><\/p>\n

\"ham-sumproduct-16\"<\/picture><\/p>\n

L\u1ed7i b\u1eb1ng 0<\/b><\/h3>\n

Trong qu\u00e1 tr\u00ecnh s\u1eed d\u1ee5ng SUMPRODUCT trong Excel, m\u1ed9t l\u1ed7i ph\u1ed5 bi\u1ebfn m\u00e0 ng\u01b0\u1eddi d\u00f9ng th\u01b0\u1eddng g\u1eb7p ph\u1ea3i l\u00e0 l\u1ed7i “B\u1eb1ng 0”. \u0110i\u1ec1u n\u00e0y th\u01b0\u1eddng x\u1ea3y ra khi kh\u00f4ng c\u00f3 gi\u00e1 tr\u1ecb n\u00e0o ph\u00f9 h\u1ee3p v\u1edbi \u0111i\u1ec1u ki\u1ec7n \u0111\u01b0\u1ee3c \u00e1p d\u1ee5ng. Ho\u1eb7c khi c\u00f3 m\u1ed9t s\u1ed1 \u0111i\u1ec1u ki\u1ec7n kh\u00f4ng ch\u00ednh x\u00e1c trong c\u00f4ng th\u1ee9c. H\u01b0\u1edbng gi\u1ea3i quy\u1ebft \u0111\u1ed1i v\u1edbi l\u1ed7i n\u00e0y l\u00e0 b\u1ea1n ph\u1ea3i ch\u1ec9nh l\u1ea1i ph\u1ea1m vi ch\u1ee9a v\u0103n b\u1ea3n th\u00e0nh ch\u1ee9a s\u1ed1.<\/span><\/p>\n

\"ham-sumproduct-17\"<\/picture><\/p>\n

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

T\u1ed5ng quan, <\/span>h\u00e0m SUMPRODUCT<\/b> l\u00e0 m\u1ed9t c\u00f4ng c\u1ee5 m\u1ea1nh m\u1ebd v\u00e0 linh ho\u1ea1t trong Excel, cung c\u1ea5p nhi\u1ec1u l\u1ee3i \u00edch v\u00e0 ti\u1ec7n \u00edch cho ng\u01b0\u1eddi d\u00f9ng trong vi\u1ec7c t\u00ednh to\u00e1n v\u00e0 ph\u00e2n t\u00edch d\u1eef li\u1ec7u. Kh\u1ea3 n\u0103ng c\u1ee7a SUMPRODUCT kh\u00f4ng ch\u1ec9 d\u1eebng l\u1ea1i \u1edf vi\u1ec7c t\u00ednh t\u1ed5ng c\u00e1c s\u1ea3n ph\u1ea9m c\u1ee7a c\u00e1c ph\u1ea7n t\u1eed t\u01b0\u01a1ng \u1ee9ng t\u1eeb c\u00e1c m\u1ea3ng d\u1eef li\u1ec7u kh\u00e1c nhau, m\u00e0 c\u00f2n m\u1edf ra m\u1ed9t lo\u1ea1t c\u00e1c \u1ee9ng d\u1ee5ng v\u00e0 c\u01a1 h\u1ed9i s\u00e1ng t\u1ea1o kh\u00e1c.<\/span><\/p>\n

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