![\"T\u1ed5ng](\"https:\/\/centax.dsoftware.vn\/wp-content\/uploads\/2015\/09\/T\u1ed5ng-h\u1ee3p-c\u00e1c-h\u00e0m-Excel-hay-d\u00f9ng-trong-k\u1ebf-to\u00e1n-300x237.jpg\")
<\/h2>\n<\/h2>\nK\u1ebf to\u00e1n Excel v\u1eabn lu\u00f4n nh\u1eadn \u0111\u01b0\u1ee3c s\u1ef1 quan t\u00e2m c\u1ee7a r\u1ea5t nhi\u1ec1u k\u1ebf to\u00e1n m\u1eb7c d\u00f9 ng\u00e0y \u0111\u00e3 c\u00f3 r\u1ea5t nhi\u1ec1u ph\u1ea7n m\u1ec1m k\u1ebf to\u00e1n th\u00f4ng d\u1ee5ng v\u00e0 ti\u1ec7n l\u1ee3i ra \u0111\u1eddi. Trong b\u00e0i vi\u1ebft n\u00e0y, K\u1ebf to\u00e1n Centax<\/a><\/strong> xin chia s\u1ebb nh\u1eefng h\u00e0m Excel m\u00e0 k\u1ebf to\u00e1n hay s\u1eed dung trong c\u00f4ng t\u00e1c h\u1ea1ch to\u00e1n v\u00e0 l\u00ean s\u1ed5 k\u1ebf to\u00e1n tr\u00ean Excel, c\u00e1c h\u00e0m n\u00e0y s\u1ebd gi\u00fap ng\u01b0\u1eddi l\u00e0m k\u1ebf to\u00e1n Excel x\u1eed l\u00fd nhanh c\u00e1c thao t\u00e1c nh\u01b0 t\u00ednh t\u1ed5ng n\u1ebfu c\u00f3 \u0111i\u1ec1u ki\u1ebfn, t\u00ecm tra c\u1ee9u th\u00f4ng tin, \u0111\u1ebfm c\u00e1c s\u1ed1, t\u00ednh t\u1ed5ng theo \u0111i\u1ec1u ki\u1ec7n l\u1ecdc\u2026<\/span><\/p>\n C\u00fa ph\u00e1p:<\/strong>\u00a0If(\u0110i\u1ec1u ki\u1ec7n, Gi\u00e1 tr\u1ecb 1, Gi\u00e1 tr\u1ecb 2).<\/span><\/p>\n C\u00e1c tham s\u1ed1<\/strong>:<\/span><\/p>\n – \u0110i\u1ec1u ki\u1ec7n:<\/strong><\/em>\u00a0<\/em>\u0110i\u1ec1u ki\u1ec7n d\u00f9ng \u0111\u1ec3 tr\u1eafc nghi\u1ec7m (x\u00e1c \u0111\u1ecbnh \u0111i\u1ec1u ki\u1ec7n n\u00e0y l\u00e0 \u0111\u00fang hay sai).<\/span><\/p>\n – Gi\u00e1 tr\u1ecb 1:<\/strong><\/em>\u00a0<\/em>L\u00e0 k\u1ebft qu\u1ea3 c\u1ee7a h\u00e0m IF n\u1ebfu \u0111i\u1ec1u ki\u1ec7n \u0111\u00fang<\/span><\/p>\n – Gi\u00e1 tr\u1ecb 2:<\/strong><\/em>\u00a0L\u00e0 k\u1ebft qu\u1ea3 c\u1ee7a h\u00e0m IF n\u1ebfu \u0111i\u1ec1u ki\u1ec7n sai<\/span><\/p>\n V\u00ed d\u1ee5:\u00a0<\/strong><\/span><\/p>\n = IF(B2>=4,\u201cDUNG\u201d,\u201cSAI\u201d)\u00a0= DUNG.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><\/p>\n T\u1ee9c l\u00e0, n\u1ebfu \u00f4 B2 >=4 th\u00ec h\u00e0m tr\u1ea3 h\u1ebft qu\u1ea3 \u201cDUNG\u201d\u00a0<\/span><\/p>\n – T\u00ednh t\u1ed5ng c\u1ee7a c\u00e1c \u00f4 \u0111\u01b0\u1ee3c ch\u1ec9 \u0111\u1ecbnh b\u1edfi nh\u1eefng \u0111i\u1ec1u ki\u1ec7n \u0111\u01b0a v\u00e0o.<\/span><\/p>\n – C\u00fa ph\u00e1p:\u00a0<\/strong>Sumif(V\u00f9ng ch\u1ee9a \u0111i\u1ec1u ki\u1ec7n, \u0110i\u1ec1u ki\u1ec7n, V\u00f9ng c\u1ea7n t\u00ednh t\u1ed5ng).<\/span><\/p>\n – C\u00e1c tham s\u1ed1:<\/strong><\/span><\/p>\n – V\u00f9ng ch\u1ee9a \u0111i\u1ec1u ki\u1ec7n:<\/strong><\/em>\u00a0<\/em>L\u00e0 d\u00e3y \u00f4 ch\u1ee9a \u0111i\u1ec1u ki\u1ec7n c\u1ea7n t\u00ednh<\/span><\/p>\n – \u0110i\u1ec1u ki\u1ec7n:<\/strong><\/em>\u00a0L\u00e0 c\u00e1c ti\u00eau chu\u1ea9n m\u00e0 mu\u1ed1n t\u00ednh t\u1ed5ng.Ti\u00eau chu\u1ea9n n\u00e0y c\u00f3 th\u1ec3 l\u00e0 s\u1ed1, bi\u1ec3u th\u1ee9c ho\u1eb7c chu\u1ed7i.<\/span><\/p>\n – V\u00f9ng c\u1ea7n t\u00ednh t\u1ed5ng:<\/strong><\/em>\u00a0L\u00e0 c\u00e1c \u00f4 th\u1ef1c s\u1ef1 c\u1ea7n t\u00ednh t\u1ed5ng. S\u1ebd l\u00e0 c\u00e1c\u00f4 ch\u1ee9a gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng (s\u1ed1 ti\u1ec1n, l\u01b0\u1ee3ng ti\u1ec1n..)<\/span><\/p>\n H\u00e0m n\u00e0y tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u00ednh t\u1ed5ng c\u1ee7a c\u00e1c \u00f4 trong v\u00f9ng c\u1ea7n t\u00ednh tho\u1ea3 m\u00e3n m\u1ed9t \u0111i\u1ec1u ki\u1ec7n \u0111\u01b0a v\u00e0o.<\/span><\/p>\n – V\u00ed d\u1ee5:\u00a0\u00a0<\/strong>=SUMIF( $E15:$E180, 5111,$H$15:$H$180)<\/span><\/p>\n – D\u00e3y \u00f4 \u0111i\u1ec1u ki\u1ec7n l\u00e0 E15 \u0111\u1ebfn E180 ( t\u1ee9c l\u00e0 k\u00e9o t\u1eeb nghi\u1ec7p v\u1ee5 ph\u00e1t sinh \u0111\u1ea7u ti\u00ean c\u1ee7a th\u00e1ng )<\/span><\/p>\n – \u0110i\u1ec1u ki\u1ec7n c\u1ea7n t\u00ednh l\u00e0 \u00f4 E112 ( t\u1ee9c l\u00e0 \u00f4 \u0111i\u1ec1u ki\u1ec7n c\u1ea7n t\u00ednh )<\/span><\/p>\n – D\u00e3y \u00f4 t\u00ednh t\u1ed5ng l\u00e0 H15 \u0111\u1ebfn H180<\/span><\/p>\n \u00a0T\u1ee9c l\u00e0: Chi\u1ebfu t\u1eeb \u00f4 E15 \u0111\u1ebfn \u00f4 E18, t\u00ecm \u0111i\u1ec1u ki\u1ec7n \u00f4 n\u00e0o ch\u1ee9a TK 5111, th\u00ec t\u00ednh t\u1ed5ng gi\u00e1 tr\u1ecb n\u1eb1m t\u1eeb \u00f4 H15 \u0111\u1ebfn H180 t\u01b0\u01a1ng \u1ee9ng v\u1edbi d\u00f2ng ch\u1ee9a TK 5111 \u0111\u00e3 \u0111\u1ed1i chi\u1ebfu trong v\u00f9ng $E15:$E180<\/span><\/p>\n Ch\u00fa \u00fd:<\/strong>\u00a0V\u1edbi c\u00e1c b\u00fat to\u00e1n k\u1ebft chuy\u1ec3n cu\u1ed1i k\u1ef3 th\u00ec \u0111i\u1ec1u ki\u1ec7n c\u1ea7n t\u00ednh c\u00f3 th\u1ec3 b\u1ea5m tr\u1ef1c ti\u1ebfp v\u00e0o \u00f4 ch\u1ee9a n\u00f3 ho\u1eb7c g\u00f5 tr\u1ef1c ti\u1ebfp t\u00e0i kho\u1ea3n c\u1ea7n k\u1ebft chuy\u1ec3n v\u00e0o c\u00f4ng th\u1ee9c<\/span><\/p>\n H\u00e0m Vlookup l\u00e0 h\u00e0m tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm theo c\u1ed9t \u0111\u01b0a t\u1eeb b\u1ea3ng tham chi\u1ebfu l\u00ean b\u1ea3ng c\u01a1 s\u1edf d\u1eef li\u1ec7u theo \u0111\u00fang gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm. X=0 l\u00e0 d\u00f2 t\u00ecm m\u1ed9t c\u00e1ch ch\u00ednh x\u00e1c. X=1 l\u00e0 d\u00f2 t\u00ecm m\u1ed9t c\u00e1ch t\u01b0\u01a1ng \u0111\u1ed1i.<\/span><\/p>\n – C\u00fa ph\u00e1p:<\/strong>\u00a0Vlookup(Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm, B\u1ea3ng tham chi\u1ebfu, C\u1ed9t c\u1ea7n l\u1ea5y, X).<\/span><\/p>\n – C\u00e1c tham s\u1ed1:<\/strong><\/span><\/p>\n – Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm:<\/strong><\/em>\u00a0<\/em>Gi\u00e1 tr\u1ecb c\u1ea7n \u0111em ra so s\u00e1nh \u0111\u1ec3 t\u00ecm ki\u1ebfm.<\/span><\/p>\n – B\u1ea3ng tham chi\u1ebfu:<\/strong><\/em>\u00a0<\/em>B\u1ea3ng ch\u1ee9a th\u00f4ng tin m\u00e0 d\u1eef li\u1ec7u trong b\u1ea3ng l\u00e0 d\u1eef li\u1ec7u \u0111\u1ec3 so s\u00e1nh. V\u00f9ng d\u1eef li\u1ec7u n\u00e0y ph\u1ea3i l\u00e0 tham chi\u1ebfu tuy\u1ec7t \u0111\u1ed1i.<\/span><\/p>\n –\u00a0 N\u1ebfu gi\u00e1 tr\u1ecb Range lookup l\u00e0 TRUE ho\u1eb7c \u0111\u01b0\u1ee3c b\u1ecf qua, th\u00ec c\u00e1c gi\u00e1 tr\u1ecb trong c\u1ed9t d\u00f9ng \u0111\u1ec3 so s\u00e1nh ph\u1ea3i \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp t\u0103ng d\u1ea7n.<\/span><\/p>\n – C\u1ed9t c\u1ea7n l\u1ea5y:<\/strong><\/em>\u00a0<\/em>s\u1ed1 ch\u1ec9 c\u1ed9t d\u1eef li\u1ec7u m\u00e0 b\u1ea1n mu\u1ed1n l\u1ea5y trong ph\u00e9p so s\u00e1nh.<\/span><\/p>\n – X:<\/strong><\/em>\u00a0L\u00e0 m\u1ed9t gi\u00e1 tr\u1ecb lu\u1eadn l\u00fd \u0111\u1ec3 ch\u1ec9 \u0111\u1ecbnh cho h\u00e0m VLOOKUP t\u00ecm gi\u00e1 tr\u1ecb ch\u00ednh x\u00e1c ho\u1eb7c t\u00ecm gi\u00e1 tr\u1ecb g\u1ea7n \u0111\u00fang. + N\u1ebfu Range lookup l\u00e0 TRUE ho\u1eb7c b\u1ecf qua, th\u00ec gi\u00e1 tr\u1ecb g\u1ea7n \u0111\u00fang \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1.<\/span><\/p>\n Ch\u00fa \u00fd:<\/strong><\/span><\/p>\n – N\u1ebfu gi\u00e1 tr\u1ecb Lookup value nh\u1ecf h\u01a1n gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t trong c\u1ed9t \u0111\u1ea7u ti\u00ean c\u1ee7a b\u1ea3ng Table array, n\u00f3 s\u1ebd th\u00f4ng b\u00e1o l\u1ed7i #N\/A.<\/span><\/p>\n – V\u00ed d\u1ee5<\/strong>:\u00a0\u00a0\u00a0=VLOOKUP(F11,$C$20:$D$22,2,0)<\/span><\/p>\n – T\u00ecm m\u1ed9t gi\u00e1 tr\u1ecb b\u1eb1ng gi\u00e1 tr\u1ecb \u1edf \u00f4 F11 trong c\u1ed9t th\u1ee9 nh\u1ea5t, v\u00e0 l\u1ea5y gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng \u1edf c\u1ed9t th\u1ee9 2.<\/span><\/p>\n Chi ti\u1ebft m\u1eddi c\u00e1c b\u1ea1n xem t\u1ea1i \u0111\u00e2y:\u00a0H\u01b0\u1edbng d\u1eabn s\u1eed d\u1ee5ng h\u00e0m Vlookup trong Excel<\/strong><\/a><\/span><\/p>\n 4. H\u00e0m SUBTOTAL:<\/strong><\/span><\/p>\n H\u00e0m n\u00e0y r\u1ea5t nhi\u1ec1u c\u00f4ng d\u1ee5ng v\u1edbi nhi\u1ec1u s\u1ed1 ch\u1ee9c n\u0103ng kh\u00e1c nhau, tuy nhi\u00ean trong k\u1ebf to\u00e1n th\u01b0\u1eddng ch\u1ec9 d\u00f9ng \u0111\u1ebfn s\u1ed1 ch\u1ee9c n\u0103ng l\u00e0 s\u1ed1 9, s\u1ed1 9 t\u01b0\u01a1ng \u1ee9ng v\u1edbi h\u00e0m SUM (t\u00ednh t\u1ed5ng)<\/span><\/p>\n C\u00fa ph\u00e1p:<\/strong>\u00a0SUBTOTAL(9; V\u00f9ng t\u00ednh t\u1ed5ng)<\/span><\/p>\n – C\u00fa ph\u00e1p:\u00a0<\/strong>And(\u0111\u1ed1i 1, \u0111\u1ed1i 2,..).<\/span><\/p>\n – C\u00e1c \u0111\u1ed1i s\u1ed1:\u00a0<\/strong>\u00a0 \u0110\u1ed1i 1, \u0111\u1ed1i 2\u2026 l\u00e0 c\u00e1c bi\u1ec3u th\u1ee9c \u0111i\u1ec1u ki\u1ec7n.<\/span><\/p>\n – H\u00e0m n\u00e0y l\u00e0 Ph\u00e9p V\u00c0, ch\u1ec9 \u0111\u00fang khi t\u1ea5t c\u1ea3 c\u00e1c \u0111\u1ed1i s\u1ed1 c\u00f3 gi\u00e1 tr\u1ecb \u0111\u00fang. C\u00e1c \u0111\u1ed1i s\u1ed1 l\u00e0 c\u00e1c h\u1eb1ng, bi\u1ec3u th\u1ee9c logic.H\u00e0m tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb TRUE (1) n\u1ebfu t\u1ea5t c\u1ea3 c\u00e1c \u0111\u1ed1i s\u1ed1 c\u1ee7a n\u00f3 l\u00e0 \u0111\u00fang, tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb FALSE (0) n\u1ebfu m\u1ed9t hay nhi\u1ec1u \u0111\u1ed1i s\u1ed1 c\u1ee7a n\u00f3 l\u00e0 sai.<\/span><\/p>\n L\u01b0u \u00fd:<\/strong><\/span><\/p>\n \u00a0 – C\u00e1c \u0111\u1ed1i s\u1ed1 ph\u1ea3i l\u00e0 gi\u00e1 tr\u1ecb logic ho\u1eb7c m\u1ea3ng hay tham chi\u1ebfu c\u00f3 ch\u1ee9a gi\u00e1 tr\u1ecb logic.<\/span><\/p>\n \u00a0 – N\u1ebfu \u0111\u1ed1i s\u1ed1 tham chi\u1ebfu l\u00e0 gi\u00e1 tr\u1ecb text ho\u1eb7c Null (r\u1ed7ng) th\u00ec nh\u1eefng gi\u00e1 tr\u1ecb \u0111\u00f3 b\u1ecb b\u1ecf qua.<\/span><\/p>\n \u00a0 – N\u1ebfu v\u00f9ng tham chi\u1ebfu kh\u00f4ng ch\u1ee9a gi\u00e1 tr\u1ecb logic th\u00ec h\u00e0m tr\u1ea3 v\u1ec1 l\u1ed7i #VALUE!<\/span><\/p>\n – V\u00ed d\u1ee5<\/strong>:\u00a0\u00a0\u00a0\u00a0=AND(D7>0,D7<5000)<\/span><\/p>\n – C\u1ed9ng t\u1ea5t c\u1ea3 c\u00e1c s\u1ed1 trong m\u1ed9t v\u00f9ng d\u1eef li\u1ec7u \u0111\u01b0\u1ee3c ch\u1ecdn.<\/span><\/p>\n – C\u00fa ph\u00e1p:\u00a0\u00a0<\/strong>\u00a0SUM(Number1, Number2\u2026)<\/span><\/p>\n – C\u00e1c tham s\u1ed1:<\/strong>\u00a0Number1, Number2\u2026 l\u00e0 c\u00e1c s\u1ed1 c\u1ea7n t\u00ednh t\u1ed5ng.<\/span><\/p>\n – C\u00fa ph\u00e1p:\u00a0\u00a0<\/strong>\u00a0OR(Logical1, Logical2\u2026) ngh\u0129a l\u00e0 Or(\u0111\u1ed1i 1, \u0111\u1ed1i 2,..).<\/span><\/p>\n – C\u00e1c \u0111\u1ed1i s\u1ed1:<\/strong>\u00a0Logical1, Logical2\u2026 l\u00e0 c\u00e1c bi\u1ec3u th\u1ee9c \u0111i\u1ec1u ki\u1ec7n.<\/span><\/p>\n – H\u00e0m n\u00e0y l\u00e0 Ph\u00e9p HO\u1eb6C, ch\u1ec9 sai khi t\u1ea5t c\u1ea3 c\u00e1c \u0111\u1ed1i s\u1ed1 c\u00f3 gi\u00e1 tr\u1ecb sai. H\u00e0m tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb TRUE (1) n\u1ebfu b\u1ea5t c\u1ee9 m\u1ed9t \u0111\u1ed1i s\u1ed1 n\u00e0o c\u1ee7a n\u00f3 l\u00e0 \u0111\u00fang, tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb FALSE (0) n\u1ebfu t\u1ea5t c\u1ea3 c\u00e1c \u0111\u1ed1i s\u1ed1 c\u1ee7a n\u00f3 l\u00e0 sai.<\/span><\/p>\n – V\u00ed d\u1ee5:\u00a0\u00a0=OR(F7>03\/02\/74,F7>01\/01\/2013)<\/span><\/p>\n – Tr\u1ea3 v\u1ec1 s\u1ed1 l\u1edbn nh\u1ea5t trong d\u00e3y \u0111\u01b0\u1ee3c nh\u1eadp.<\/span><\/p>\n – C\u00fa ph\u00e1p<\/strong>:\u00a0\u00a0 MAX(Number1, Number2\u2026)<\/span><\/p>\n – C\u00e1c tham s\u1ed1:<\/strong>\u00a0Number1, Number2\u2026 l\u00e0 d\u00e3y m\u00e0 b\u1ea1n mu\u1ed1n t\u00ecm gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t \u1edf trong \u0111\u00f3.<\/span><\/p>\n – Tr\u1ea3 v\u1ec1 s\u1ed1 nh\u1ecf nh\u1ea5t trong d\u00e3y \u0111\u01b0\u1ee3c nh\u1eadp v\u00e0o.<\/span><\/p>\n – C\u00fa ph\u00e1p:\u00a0\u00a0 MIN(Number1, Number2\u2026)<\/span><\/p>\n – C\u00e1c tham s\u1ed1: Number1, Number2\u2026 l\u00e0 d\u00e3y m\u00e0 b\u1ea1n mu\u1ed1n t\u00ecm gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t \u1edf trong \u0111\u00f3.<\/span><\/p>\n M\u1eddi c\u00e1c b\u1ea1n theo d\u00f5i c\u00e1c b\u00e0i vi\u1ebft sau:<\/span><\/p>\n Ph\u1ea7n m\u1ec1m k\u1ebf to\u00e1n excel<\/a><\/span><\/p>\n M\u00f4 t\u1ea3 c\u00f4ng vi\u1ec7c c\u1ee7a k\u1ebf to\u00e1n t\u1ed5ng h\u1ee3p<\/a><\/span><\/p>\n Ph\u1ea7n m\u1ec1m ph\u00e2n t\u00edch chi ti\u1ebft BCTC c\u1ee7a doanh nghi\u1ec7p<\/a><\/span><\/p>\n C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m IF trong Excel<\/a><\/span><\/p>\n1. H\u00e0m IF<\/strong><\/span><\/h2>\n
\n
2. H\u00e0m SUMIF:<\/strong><\/span><\/h2>\n
3. H\u00e0m VLOOKUP:<\/strong><\/span><\/h2>\n
5. H\u00e0m AND:<\/strong><\/span><\/h2>\n
6. H\u00e0m SUM:<\/strong><\/span><\/h2>\n
7. H\u00e0m OR:<\/strong><\/span><\/h2>\n
8. H\u00e0m MAX:<\/strong><\/span><\/h2>\n
9. H\u00e0m MIN:<\/strong><\/span><\/h2>\n