Giáo trình Excel 2010

dians. =RAND Tr? v'? m0t s'? ng0u nhiên trong khoang 0 và 1 =RANDBETWEEN Tr'? v'? m0t s0 ng0u nhiên trong khoang do b0n ch0 đ0nh Hàm ROMAN() Dùng đ0 chuyên đ0i m0t s0 d0ng 0-r0p sang d0ng s0 La-mã Cú pháp: = ROMAN(number, form) number: S0 c0n chuyên đ0i form: d0ng chuyên đ0i 0 (ho0c TRUE, ho0c không nh0p): D0ng c0 đi0n 1 cho đ0n 3: D0ng c0 đi0n nh0ng đEEc rút g0n, s0 càng l0n rút g0n càng nhi0u (xem thêm 0 ví d0) 4 (ho0c FALSE): D0ng hi0n đ0i Chú ý: number ph0i là s0 d00ng, n0u number < 0 hàm s0 báo l0i #VALUE! N0u number là s0 th0p phân, ROMAN() ch0 chuy0n đ0i ph0n nguyên c0a nó Hàm ROMAN() ch0 x0 lý đ00c t0i s0 l0n nh0t là 3999, n0u number > 3999 hàm s0 báo l0i #VALUE! Sau khi đã chuy0n đ0i, k0t qu0 s0 là m0t d0 li0u d0ng text, và không th0 tính toán v0i nó đ00c n0a Ví d0: ROMAN(499, 0) = CDXCIX = ROMAN(499) = ROMAN(499, TRUE) ROMAN(499, 1) = LDVLIV ROMAN(499, 2) = XDIX ROMAN(499, 3) = VDIV ROMAN(499, 4) = ID = ROMAN(499, FALSE) ROMAN(2008) = MMVIII =ROUND(X,n) Hàm làm tròn n s0 c0a X N0u n d00ng (n>0) s0 làm tròn s0 bên ph0i k0 t0 v0 trí d0u ch0m th0p phân. N0u n âm (n<0) s0 làm tròn s0 bên trái k0 t0 v0 trí d0u ch0m th0p phân. =ROUNDDOWN Làm tròn m0t s0 h00ng xu0ng zero =ROUNDUP Làm tròn m0t s0 h00ng ra xa zero. =SIN Tính sin c0a m0t góc =SINH Tính sin hyperbol c0a m0t s0 =SUM Tính t0ng c0a các s0 =DSUM (vùng d0 li0u, c0t giá tr0, vùng tiêu chu0n) Hàm tính t0ng trong c0t giá tr0 tho0 mãn đi0u ki0n c0a vùng tiêu chu0n trong vùng d0 li0u. VD: Tính t0ng tilSn l00ng nh0ng ng00i 26 tu0i. Hàm SUMIF() Tính t0ng các ô trong m0t vùng th0a m0t đi0u ki0n cho tr00c. Cú pháp: = SUMIF(range, criteria, sum_range) Range : Dãy các ô đ0 tính t0ng, có this là ô ch0a s0, tên, m0ng, hay tham chi0u đ0n các ô ch0a s0. Ô rising và ô ch0a giá trs text s0 đ00c b0 qua. Criteria : Đi0u ki0n đ0 tính tlSng. Có this 0 d0ng s0, bi0u thlSc, ho0c text. Ví d0, criteria có this là 32, "32", "> 32", ho0c "apple", v.v... Sum_range : Là vùng th0c s0 đ0 tính t0ng. N0u b0 qua, Excel s0 coi nh0 sum_range = range. L0u ý: Sum_range không nh0t thist phsi cùng kích th00c v0i range. Vùng th0c s0 đ0 tính t0ng đ00c xác đ0nh b0ng ô đlSu tiên phía trên bên trái c0a sum_range, và bao g0m thêm nh0ng ô t00ng 0ng vsi kích th00c c0a range. Ví d0: N0u Range là A1:A5, Sum_range là B1:B5, thì vùng th0c s0 đ0 tính t0ng là B1:B5 N0u Range là A1:A5, Sum_range là B1:B3, thì vùng th0c s0 đ0 tính t0ng là B1:B5 N0u Range là A1:B4, Sum_range là C1:D4, thì vùng th0c s0 đ0 tính t0ng là C1:D4 N0u Range là A1:B4, Sum_range là C1:D2, thì vùng th0c s0 đ0 tính t0ng là C1:D4 Có this dùng các ký t0 đ0i di0n trong đi0u ki0n: d0u ? đsi di0n cho m0t ký t0, d0u * đ0i di0n cho nhi0u ký t0 (n0u nh0 đi0u ki0n là tìm nh0ng d0u ? ho0c *, thì gõ thêm d0u ~ 0 tr00c d0u ? hay *). Khi đi0u ki0n đ0 tính t0ng là nh0ng ký t0, SUMIF() không phân bi0t ch0 th00ng hay ch0 hoa. Hàm SUMIFS() Tính t0ng các ô trong mEt vùng th0a nhi0u đi0u ki0n cho tr00c. Cú pháp: = SUMIFS(sum_range, criteria_range1, criterial, criteria_range2, criteria2, ...) Sum_range : Dãy các ô đ0 tính t?ng, có this là ô ch0a s0, tên, m0ng, hay tham chi0u đ0n các ô ch0a sS. Ô rising và ô ch0a giá tris text s0 đ?'?c b0 qua. Criteria_range1, criteria_range2... : Có this có tis 1 đ0n 127 vùng dùng đsi liên kst v0i các đi0u ki0n cho vùng. Criteria1, criteria2... : Có this có tis 1 đSn 127 đi0u ki0n đis tính t0ng. Chúng có this 0 d0ng sis, bi0u thisc, hoisc text. Ví dis, criteria có this là 32, "32", "> 32", hoisc "apple", v.v... usu ý: Misi ô trong sum_range chis đisisc tính tsng nisu tsit cis các điisu kiên tisisng isng visi ô đó đisu đúng. Nisu thisa các điisu kiisn, nó sis bisng 1, còn không, thì nó bisng 0. Không giisng nhis nhisng đsi sis range và criteria cisa hàm SUMIF, trong hàm SUMIFS, misi vùng criteria_range phsn có cùng kích thisisc và hình disng giisng nhis sum_range. Có this dùng các ký tis đsi diisn trong các điisu kiisn: dsu ? đsi diisn cho mEt ký tis, dsu * đsi diisn cho nhisu ký tis (nisu nhis điisu kiisn là tìm nhisng disu ? hoisc *, thì gõ thêm disu ~ is trisisc dsu ? hay *) Khi điisu kiisn đis đism là nhisng ký tis, SUMIFS() không phân biist chis thsnsng hay chis hoa. Hàm SUMSQ() Dùng đs tính tsng các bình phssng csa các ss Cú pháp: = SUMSQ(number1, number2, ...) number1, number2, ... : Có this dùng đisn 255 tham sis (visi Excel 2003 tris vis trisisc, con sis này chs là 30) Các tham sis (number) có this là mEt sffl, là mEt misng, mEt tên, hay là mEt tham chiisu đisn mist ô chisa sis, v.v... Ví ds: SUMSQ(3, 4) = (3A2) + (4A2) = 9 + 16 = 25 Ba hàm sau đây có cách dùng và cú pháp tEEng t0 nhau: Hàm SUMX2MY2(), Hàm SUMXPY2() và Hàm SUMXMY2() Đ0 d0 nh0 tên c0a ba hàm này, b0n đ0c chúng t0 trái sang ph0i v0i các quy 00c sau: SUM = T0ng, M (Minus) = Tr0 (hi0u s0), P (Plus) = C0ng (t0ng s0), 2 = Bình ph00ng, X và Y là hai m0ng gì đó, có ch0a nhi0u ph0n t0 x và y V0y, đ0nh nghĩa và cách tính toán c0a 3 hàm này là: = SUMX2MY2: T0ng c0a hi0u hai bình ph00ng c0a các ph0n t0 t00ng 0ng trong 2 m0ng d0 li0u = SUMX2PY2: T0ng c0a t0ng hai bình ph00ng c0a các ph0n t0 t00ng 0ng trong 2 m0ng d0 li0u = SUMXMY2: T0ng c0a bình ph00ng c0a hi0u các ph0n t0 t00ng 0ng trong 2 m0ng d0 li0u Cú pháp: = SUMX2MY2(array_x, array_y) = SUMX2PY2(array_x, array_y) = SUMXMY2(array_x, array_y) array_x và array_y là các dãy ô ho0c giá tr0 ki0u m0ng L0u ý: array_x và array_y b0t bu0c ph0i có cùng kích th00c, n0u không, hàm s0 báo l0i #NA! N0u trong array_x ho0c array_y có nh0ng giá tr0 ki0u text, ki0u logic ho0c r0ng, thì s0 đ00c b0 qua (không tính), tuy nhiên các giá tr0 = 0 v0n đ00c tính. =TAN Tính tang c0a m0t góc =TANH Tính tang hyperbol c0a m0t s0 =TRUNC C0t b0t ph0n th0p phân c0a s0 Hàm SUMPRODUCT() Tính t0ng các tích các ph0n t0 t00ng 0ng trong các m0ng giá tr0 Sum = T0ng-Product = Tích SUMPRODUCT = T0ng c0a tích (các m0ng d0 li0u) Cú pháp: = SUMPRODUCT(array1, array2, ...) arrayl, array2, ... : Có this dùng tis 2 tisi 255 m0ng (v0i Excel 2003 tris v0 tr00c thì con sl'?l này ch0 là 30) và các m0ng này phsi cùng kích thiasc v0i nhau Ll'?lu ý: N0u các m0ng không cùng kích thiasc, SUMPRODUCT sl'?l báo lisi #VALUE! Bist kỳ mHt ph0n ts nào trong m0ng không phsi là dl'?l liSu ki0u sis, sl'?l đ?'?c SUMPRODUCT coi nh0 bisng 0 (zero) Hàm SUBTOTAL là mist hàm rst linh ho0t nh0ng cũng là mEt trong các hàm hsi khó sl'?l d0ng c?a Excel. Đi0u khó hi0u this nhst chính là cái tên c?a nó, vì nó th0c sl'?l làm đ?'?c nhĩ0u this h0n ý nghĩa clSa tên hàm. ĐSi sS this nhst clSa hàm bst bulSc bsn phsi nhs con sS đsi diSn cho phép tính c0n th0c hisn trên tisp sis liisu (trong Excel 2010 có tính năng AutoComplete giúp chúng ta khsi nhs các con sis này). Hàm SUBTOTAL đisisc Microsoft nâng c0p kis tis phiên b0n Excel 2003 visi sis gia tăng các tuỳ ch0n cho đisi sis this nhist c?a hàm, tuy nhiên đi0u này d0n đ0n sis không tsnsng thích vsi các phiên b0n cũ n0u chúng ta sis d0ng các tính năng misi bis sung này. Đ0i sis đisu tiên c?a cl?a hàm SUBTOTAL xác đisnh hàm th0c sis nào sis đsnsc sis d0ng khi tính toán (xem trong danh sách bên disisi). Ví dis n0u đsi sis là 1 thì hàm SUBTOTAL hoist đisng giisng nh0ng hàm AVERAGE, n0u đsi sis this nhist là 9 thì hàm hàm SUBTOTAL hoist đisng giisng nhisng hàm SUM. SUBTOTAL là hàm tính toán cho mEt nhóm con trong mist danh sách hoisc bisng dis liisu tuỳ theo phép tính mà bisn chisn lisa trong đsn sis this nhist. Cú pháp: = SUBTOTAL(function_num, refl, ref2,...) Function_num: Các con sis tis 1 đisn 11 (hay 101 đisn 111) qui đisnh hàm nào sis đisisc dùng đis tính toán trong SUBTOTAL Ref1, ref2: Các vùng đisa chis tham chiisu mà bsn muisn thisc hiisn phép tính trên đó. Trong Excel 2010, bisn có this dùng đisn 254 ref (vsn Excel 2003 trs vs trisisc thì con sis này chis là 29) Ghi chú: N0u có hàm SUBTOTAL khác đ0t l0ng trong các đ0i s0 ref1, ref2,... thì các hàm l0ng này s0 b0 b0 qua không đEEc tính, nh0m tránh tr00ng h0p tính toán 2 l0n. Đ0i s0 function_num n0u t0 1 đ0n 11 thì hàm SUBTOTAL tính toán bao g0m c0 các giá tr0 0n trong t0p s0 li0u (hàng 0n). Đ0i s0 function_num n0u t0 101 đ0n 111 thì hàm SUBTOTAL ch0 tính toán cho các giá tr0 không 0n trong t0p s0 li0u (b0 qua các giá trS 0n). Hàm SUBTOTAL s'? b0 qua không tính toán t0t c0 các hàng b0 0n b0i l0nh Filter (Auto Filter) mà không ph0 thu0c vào đ0i s'? function_num đ??c dùng (1 gi0ng 101...). Hàm SUBTOTAL đEEc thi0t k0 đ0 tính toán cho các c0t s'? li0u theo chi0u d0c, nó không đ??c thi0t k0 đ0 tính theo chi0u ngang. Hàm này ch0 tính toán cho d0 li0u 2-D, do v0y n0u d0 li0u tham chi0u d0ng 3-D (Ví d0 v0 tham chi0u 3-D: =SUM(Sheet2:Sheet13!B5) thì hàm SUBTOTAL báo l0i #VALUE! Hàm SQRT() Dùng đ0 tính căn b'?c hai c'?a s'? Cú pháp: = SQRT(number) number: S'? th0c, d00ng (n0u number < 0 hàm s'? báo l0i #NUM!) Ví dB: GĨ0 s0 0 ô A2, có con s0 -16 SQRT(16) = 4 SQRT(A2) = #NUM! SQRT(ABS(A2)) = 4 Hàm SQRTPI() Dùng đ0 tính căn b0c hai c0a m0t s0 nhân v0i Pi (= 3.14159265358979) Cú pháp: = SQRTPI(number) number: S0 th0c, d00ng nhân v0i Pi (n0u number < 0 hàm s0 báo l0i #NUM!) Ví d0: Gi0 s0 0 ô A2, có con s0 -16 SQRT(1) = 1.772454 (căn b0c hai c0a Pi) SQRT(2) = 2.506628 (căn b0c hai c0a 2*Pi) Hàm SIGN() Tr0 v0 d0u c0a s0: 1 n0u là s0 d00ng, 0 (zero) n0u là s0 0 và -1 n0u là s0 âm. Cú pháp: = SIGN(number) Ví d0: SIGN(10) = 1 SIGN(4-4) = 0 SIGN(-0.057) = -1 Hàm SERIESSUM() Dùng đ0 tính t0ng lũy th0a c0a m0t chu0i s0, theo công th0c sau đây: series (x, n, m, a) = a1*xAn + a2*xA(n+m) + a3*xA(n+2m) + ... + ai*xA(n+(i-1)m) Cú pháp: = SERIESSUM(x, n, m, coefficients) x : giá tr0 nh0p vào cho chu0i lũy th0a n : lũy th0a kh0i t0o đ0 tăng t0i x m : b00c tăng cho m0i ph0n t0 trong chu0i coefficients : t0p h0p h0 s0 s0 đ00c nhân v0i m0i lũy th0a c0a x Các thông s0 này ph0i là các d0 li0u ki0u s0, n0u không, hàm s0 báo l0i #VALUE! Ví d0: SERIESSUM(5, 0, 2, {1, 2, 3, 4}) = 64,426 Di0n gi0i chi ti0t: (x = 5, n = 0, m = 2, coefficients = 1, 2, 3, 4) =1*5A0 + 2*5A(0+2) + 3*5A(0+2*2) + 4*5A(0+3*2) = 64426 HÀM TH0NG KÊ Bao g0m các hàm sl'?l giúp b0n gi0i quy0t các bài toán th0ng kê đ0n gi0n đ0n ph0c tHp. =AVEDEV Tính bình quân đl'?l phân cl?c =AVERAGE(vùng) Tính trung bình c0ng các sS. =AVERAGEA Tính trung bình c0ng các giá tr0 =DAVERAGE (vùng dl'?l li0u, cHt c0n tính, vùng tiêu chu0n) Hàm tính trung bình các giá trS trong d3t tho0 mãn đi0u ki0n cl?a vùng tiêu chu0n trong vùng dl'?l li0u. Hàm AVERAGEIF() Tri? v’ trung bình c0ng (s0 h0c) cl?a t0t cl? các ô đ?l?c ch0n th0a mãn m0t đi0u ki0n cho tr00c. Cú pháp: = AVERAGEIF(range, criteria, average_range) range : Là m0t ho0c nhi0u ô c0n tính trung bình, có thH bao g0m các con s0, các tên vùng, các m0ng ho0c các tham chi0u đ0n các giá trH... criteria : Là đi0u ki0n dEEi d0ng m0t s0, m0t bi0u th0c, đl?a ch0 ô ho0c chu0i, đl? qui đ0nh vi0c tính trung bình cho nh0ng ô nào... average_range : Là t0p h0p các ô th0t sl? đ?l?c tính trung bình. N0u bl? tr0ng thì Excel dùng range đl? tính. LlSu ý: Các ô trong range n0u có ch0a nh0ng giá trH lu0n lý (TRUE ho0c FALSE) thì sl? đ?l?c b0 qua. Nh0ng ô rising trong average_range cũng s0 đ00c b0 qua. N0u range rising ho0c có ch0a d0 li0u text, AVERAGEIF s0 báo l0i #DIV/0! N0u có m0t ô nào trong criteria rising, AVERAGEIF s0 xem nh0 nó b0ng 0. N0u không có ô nào trong range th0a mãn đi0u ki0u c0a criteria, AVERAGEIF s0 báo l0i #DIV/0! B0n có this các ký t0 đ0i di0n nh0 ?, * trong criteria (d0u ? thay cho mHt ký t0 nào đó, và d0u * thay cho mEt chu0i nào đó). Khi đi0u ki0n trong criteria là chính các d0u ? ho0c *, thì b0n gõ thêm d:?u ~ triSBc nó. average_range không nh0t thist phsi có cùng kích thSEc v0i range, mà các ô th0c s0 đ?'?c tính trung bình s0 dùng ô trên cùng bên trái c?a average_range làm ô bst đ0u, và bao g0m thêm nh0ng ô t00ng 0ng v0i kích thSEc c?a range. Hàm AVERAGEIFS() Tri? v0 trung bình c0ng (sS h0c) c?a tst c0 các ô đ?'?c ch0n th0a mãn nhi0u đi0u ki0n cho tr00c. Cú pháp: = AVERAGEIFS(average_range, criteria_range1, criterial, criteria_range2, criteria2, ...) average_range : Vùng c0n tính trung bình, có thH bao g0m các con s0, các tên vùng, các m0ng ho0c các tham chi0u đ0n các giá trH... criteria_range1, criteria_range2, ... : Vùng ch0a nh0ng đi0u ki0n đ0 tính trung bình. Có thH khai báo ts 1 đ0n 127 vùng. criterial, criteria2, ... : Là các đi0u ki0n đ0 tính trung bình. Có thH khai báo ts 1 đ0n 127 đi0u ki0n, dSEi d0ng sS, bi0u th0c, tham chi0u ho0c chu0i... L?u ý: N0u average_range rising ho0c có ch0a d0 liSu text, AVERAGEIFS s0 báo ls #DIV/0! N0u có m0t ô nào trong nh0ng vùng criteria_range rising, AVERAGEIFS s?? xem nh0 nó b0ng 0. Nh0ng giá trs logic: TRUE s?? đlSBc xem là 1, và FALSE s?? đlSBc xem là 0. MSi ô trong average_range chs đ?'?c tính trung bình n0u thsia tst clSI đilSu kilSn quy đ0nh cho ô đó Không giising nh0 AVERAGEIF(), m0i vùng criteria_range phSi có cùng kích th00c v0i average_range N0u có misit ô nào trong average_range không this chuyên đSi sang d0ng sS, ho0c n0u không có ô nào th0a tst c0 các đi0u kiSn, AVERAGEIFS s0 báo l0i #DIV/0! * Có this các ký t0 đ0i di0n nh0 ?, * cho các đi0u ki0n (d0u ? thay cho ký t0 nào đó, và d0u * thay cho mEt chu0i nào đó). Khi đi0u ki0n trong criteria là chính các d0u ? ho0c *, thì b0n gõ thêm d0u ~ trEEc nó. Hàm COUNT() Đ0m ô d0 liSu ch0a s0 (đ0m s0 ô không triSng) - đ0m s0 ô có trong vùng. Nh0ng ch0 đ0m nh0ng ô có ki0u d0 liSu là ki0u s0. Cú pháp=COUNT(vùng) =COUNTA Đ0m s0 ô ch0a d0 liSu =COUNTIF(Vùng c0n đ0m, ô đi0u kiSn) Hàm đ0m có đi0u ki0n - đ0m s0 ô có trong vùng v0i đi0u ki0n đã ch0 ra 0 ô đi0u ki0n. Hàm COUNTIF đ0m ô có ki0u d0 li0u là ki0u s0 và c0 ki0u ký t0. =DCOUNT(vùng d0 li0u, c0t c0n đ0m, vùng tiêu chu0n) Đ0m s0 ô ch0a l00ng giá tr0 s0 trong c0t tho0 mãn đk trong vùng tiêu chu0n tìm ki0m trong vùng d0 li0u. VD: Đ0m xem có bao nhiêu ng00i có m0c l00ng là 500000. =MAX(vùng) Tìm s0 l0n nh0t trong vùng =MAXA Tìm giá tr0 l0n nh0t Hàm DMAX() Cú pháp: = DMAX(database, field, criteria)=DMAX (vùng d0 li0u, c0t giá tr0, vùng tiêu chu0n) Hàm tính giá tr0 cao nh0t trong c0t giá tr0 tho0 mãn đi0u ki0n c0a vùng tiêu chu0n trong vùng d0 li0u. VD: Tìm xem nh0ng ng00i 26 tu0i ai cao l00ng nh0t. =MIN(vùng) Tìm s0 nh0 nh0t trong vùng =MINA Tìm giá tr0 nh0 nh0t Hàm DMIN() Cú pháp: =DMIN(database, field, criteria)=DMIN (vùng d0 li0u, c0t giá tr0, vùng tiêu chu0n) Hàm tính giá tr0 nh0 nh0t trong c0t giá trS tho0 mãn đi0u ki0n cSa vùng tiêu chu0n trong vùng d0 li0u. VD: Tìm xem nh0ng ng00i 26 tu0i ai th0p l00ng nh0t. =RANK(ô c0n x0p this, vùng c0n so sánh) Tìm v0 this csa m0t s0 trong dãy s0. Vùng c0n so sánh: Th00ng đ0 0 ch0 đ0 giá tr0 tuy0t đsi: $ Hàm DVARP() Cú pháp: = DVARP(database, [field,] criteria) Tính toán s0 bi0n thiên cSa m0t t0p h0p d0a trên toàn th0 t0p h0p, b0ng cách s0 d0ng các s0 li0u trong m0t c0t cSa m0t danh sách hay cSa m0t c0 s0 d0 li0u, theo m0t đi0u ki0n đ00c ch0 đ0nh. Hàm DVAR() Cú pháp: = DVAR(database, [field,] criteria) 00c l00ng s'? bi0n thiên c0a m0t t0p h0p d0a trên m0t m0u, b0ng cách s'? d0ng các s'? li0u trong m0t c0t c'?a m0t danh sách hay c'?a m0t c0 s'? d0 li0u, theo m0t đi0u ki0n đ00c ch0 đ0nh. Hàm DSUM() Cú pháp: = DSUM(database, field, criteria) C0ng các s0 trong m0t c0t c0a m0t danh sách hay c0a m0t c0 s0 d0 li0u, theo m0t đi0u ki0n đ00c ch0 đ0nh. Hàm DSTDEVP() Cú pháp: = DSTDEVP(database, field, criteria) Tính đ0 l0ch chu0n c0a m0t t0p h0p theo toàn th0 các t0p h0p, b0ng cách s0 d0ng các s0 li0u trong m0t c0t c0a m0t danh sách hay c0a m0t c0 s0 d0 li0u, theo m0t đi0u ki0n đ00c ch0 đ0nh. Hàm DSTDEV() Cú pháp: = DSTDEV(database, field, criteria) 00c l00ng đl? l0ch chu0n c?a mHt tHp h0p theo m0u, b0ng cách si? d0ng các si? li0u trong mHt cist cl?a mHt danh sách hay cl?a mHt cl? si? dl? li0u, theo mHt đi0u ki0n đEEc ch0 đ0nh. Hàm DPRODUCT() Cú pháp: = DPRODUCT(database, field, criteria) Nhân các giá trS trong mHt cHt cl?a mHt danh sách hay cl?a mHt cl? sl? d0 li0u, theo mHt đi0u ki0n đ?l?c ch0 đ0nh. Đ0m sl? ô rising trong b0ng tính Dùng công th0c m0ng: {=SUM(IF(ISBLANK(range), 1, 0))} v0i range là vùng dl? li0u c0n ki0m tra. Đ0m sl? ô ch0a nh0ng giá trs không ph0i là ki0u sl? Dùng công th0c m0ng: {=SUM(IF(ISNUMBER(range), 0, 1))} v0i range là vùng dl? li0u c0n ki0m tra. Đ?m sl? ô bl? l0i Dùng công th0c m0ng: {=SUM(IF(ISERROR(range), 1, 0))} v0i range là vùng dl? li0u c0n ki0m tra. HÀM XỬ LÝ VĂN BẢN VÀ DỮ LIỆU Bao g0m các hàm xl? lý chu0i văn b0n nh0 trích l0c, tìm ki0m, thay thH, chuyên đsi chu0i văn b0n trong Excel. Hàm ASC() Dùng đ0 đ0i các ký ts double-byte sang các ký ts single-byte cho nh0ng ngôn ng0 s0 d0ng b0 ký ts double-byte. Cú pháp: = ASC(text) text : Là ch0 ho0c tham chi0u đ0n mHt ô có ch0a ch0. N0u text không ch0a bHt kỳ m0u ts nào thu0c lo0i double-byte, thì text s0 không đ00c chuyên đ0i. Ví ds: = ASC("Excel") = Excel =CHAR Chuy0n s0 thành ký ts =CLEAN Xóa ký t0 không phù h0p =CODE Tr? v0 mã s'? c'?a ký t0 đ0u tiên Hàm CONCATENATE Công d0ng: Dùng n0i nhi0u chu0i l0i v0i nhau Công th0c: =CONCATENATE(text1,text2,...) Ví dB: =CONCATENATE("Gi0i pháp", "Excel", " - ", "Công c'? tuy0t v0i c'?a b0n) -> GĨ0Ĩ pháp Excel - Công c'? tuy0t v0i c'?a b0n =DOLLAR Chuy0n đ0nh d0ng s'? thành ti0n t0 Hàm EXACT Công d0ng: Dùng đ'? so sánh hai chu0i v0i nhau. Công th0c: =EXACT(text1,text2) Hàm EXACT phân bi0t ch0 th00ng và ch0 hoa. N0u 2 chu0i textl, text2 gi0ng nhau hoàn toàn, hàm s'? trS v'? TRUE; n0u không, s'? trS v'? tr0 FALSE Ví dB: =EXACT("Gi0i pháp", "GĨ0Ĩ pháp") TRUE =EXACT("Gi0i pháp", "GĨ0Ĩ Pháp") FALSE =FIXED Chuy0n m0t s'? sang đ0nh d0ng văn b0n =LEFT(X,n) Hàm LEFT l0y n ký t0 t0 bên trái sang c0a văn b0n X =LEN Tính đ0 dài m0t chu0i =LOWER Chuy0n thành ch0 th00ng. =PROPER Chuy0n ký t0 đ0u m0i t0 thành ch0 hoa Hàm MID Dùng đ0 trích xu0t m0t chu0i con (substring) t0 m0t chu0i Công th0c: =MID(text, start_num, num_chars])=MID(X, m, n) Hàm MID l0y n ký ts trong chuisi X bst đ0u tsi vs trí m. text: chu0i văn b0n c0n trích xu0t start_num: v0 trí bst đ0u trích ra chu0i con, tính tsi bên trái sang num_chars: ss ký tsi c?a chu0i con c0n trích ra num_chars ph0i là ss nguyên d00ng start_num ph0i là ss nguyên d00ng N0u start_num l0n h0n đs dài cl?a chu0i thì kist qu0 trH vs ss là chu0i rising Ví ds: =MID("Karen Elizabeth Hammond", 7, 9) Elizabeth Hàm REPLACE Công d0ng: Dùng đs thay thH mist ph0n csa chu0i b0ng mist chu0i khác, d0a vào ss ký ts đssc ch0 đ0nh Công th0c: =REPLACE(old_text, start_num, num_chars, new_text) old_text: chu0i văn b0n c0n đssc xs lý start_num: vs trí bist đl2lu tìm cái sl? thay this, tính ts bên trái sang num_chars: ss ký ts csa chu0i c0n đEEc thay this new_text: chu0i văn b0n ss thay this cho ss ký tis đã ch0n bsi start_num và num_chars Cái khó csa hàm này là xác đ0nh đssc bsi start_num và num_chars. Làm sao bist đssc bst đ0u tis đâu và thay this bao nhiêu ch0? Tôi gSi ý nhé: Bsn dùng hàm FIND() holSc SEARCH() đs xác đ0nh vs trí bst đ0u (start_num) Dùng hàm LEN() đis xác đ0nh s0 ký tis c0a chuisi s0 đsnsc thay this (num_chars) Ví dS: đis thay sis 2007 b0ng 2008 trong câu Expense Budget for 2007 Dùng công thsc nhs sau: =REPLACE(A1, FIND("2007", A1), LEN("2007"), "2008"H Expense Budget for 2008 Hàm RIGHT Dùng đ'? trích xu0t ph0n bên ph0i c'?a mHt chu0i mHt ho0c nhi0u ký t0 tùy theo s'? ch0 đ0nh c'?a b0n Công th0c: =RIGHT(text [,num_chars])=RIGHT(X,n) Hàm RIGHT l0y n ký t0 t0 bên ph0i sang c'?a văn b0n X text: chu0i văn b0n c0n trích xu0t ký t0 num_chars: s'? ký t0 c0n trích ra phía bên ph0i c'?a chu0i text, m'?c đ0nh là 1 num_chars ph0i là s'? nguyên d00ng N0u num_chars l0n h0n đ'? dài c'?a chu0i thì k0t qu0 trH v0 s'? là toàn b'? chu0i text Ví dEI: =RIGHT("Karen Elizabeth Hammond", 7) -> Hammond =REPT L'?p l0i mHt chu0i =SUBSTITUTE Thay thH m0t chu0i xác đ0nh Hàm FIND và Hàm SEARCH Công d0ng: Dùng đ'? tìm v'? trí b0t đ0u c'?a mHt chu0i con (substring) trong mHt chu0i Công th0c: =FIND(find_text, within_text [, start_num]) =SEARCH(find_text, within_text [, start_num]) find_text: chu0i văn b0n c0n tìm (chu0i con) within_text: chu0i văn b0n ch0a chu0i c0n tìm (chu0i m0) start_num: v0 trí bHt đ0u tìm trong chu0i within_text (m0c đ0nh là 1) M0t s0 ll3u ý: K0t qu0 c0a hai hàm này là mHt con s0, ch0 v0 trí bHt đ0u (tính t0 start_num) c0a find_text trong within_text Dùng SEARCH() khi mu0n tìm mHt chu0i bHt kỳ. Ví dEI: SEARCH("e", "Expenses") s0 cho k0t qu0 là 1. Dùng FIND() khi mu0n tìm chính xác mEt chu0i có phân bi0t ch0 hoa, ch0 th00ng. Ví ds: FIND("e", "Expenses") ss cho kst qu0 là 4. N0u không tìm th0y find_text, hàm ss báo l0i #VALUE Có this dùng nh0ng ký tsi đ0i di0n nh0 *, ? trong find_text csa hàm SEARCH() vsi hàm SEARCH(), nsu mu0n tìm chính ký ts * ho0c ? thì gõ d0u ~ trSEc ký ts đó ( ~* ho0c là ~?) =TEXT Chuy0n mEt ss sang text. Hàm T Công d0ng: Tr? v0 mEt chu0i n0u tris tham chi0u là chu0i, ngEEc lisi, ss tris vs chu0i rising - Ki0m tra ds liSu ki0u text Công th0c: =T(value) Hàm này ít khi đ?'?c dùng. Hàm SUBSTITUTE Công d0ng: Dùng đs thay this mEt chu0i này b0ng mEt chu0i khác. Hàm này cũng t00ng tffl hàm REPLACE(), nh0ng ds ss d0ng h0n. Công th0c: =SUBSTITUTE(text, old_text, new_text [,instance_num]) text: chu0i văn b0n g0c, c0n đ?'?c xs lý old_text: chu0i văn b0n c0n đ00c thay this new_text: chuisi văn blSn s0 thay this vào instance_num: sis lEln thay this old_text b0ng new_text, n0u bs qua thì tst cis old_text tìm đ00c s0 đ00c thay this b0ng new_text Ví ds: đis thay sis 2007 b0ng 2008 trong câu Expense Budget for 2007 Dùng công thsc nhs sau: =SUBSTITUTE("Expense Budget for 2007", "2007", "2008") Expense Budget for 2008 HÀM THAY TH0 CHU0I NÀY B0NG CHU0I KHÁC (Substituting One Substring for Another) Có nhi0u ch00ng trình có kh0 năng tìm ki0m mist s'? đo0n văn và thay this nó b0ng đo0n văn khác. Excel cũng có kh0 năng làm chuyên đó b0ng cách dùng hàm. Đó là hàm REPLACE và hàm SUBSTITUTE. =TRIM Xóa nh0ng ký ts trisng bên trong chu0i. =UPPER Chuyên ký ts thEEng thành hoa. Hàm VALUE Công d0ng: Dùng đ'? đsi mHt chu0i đsi di0n cho mEt s'? thành ki0u s'? Công th0c: =VALUE(text) text phsi là đ0nh d0ng sis, ngày tháng ho0c bst kỳ mHt thsi gian nào mi0n là đEEc Excel công nhisn. N0u 2 chu0i textl, text2 giSng nhau hoàn toàn, hàm s'? tris v'? TRUE; n0u không, s'? tris v'? tris FALSE Ví ds: Đ'? trích ra s'? 6500 trong SQA6500, b0n có this dùng hàm RIGHT() =RIGHT("SQA6500", 4) 6500 Tuy nhiên kst quis do hàm RIGHT() có đlSISc sS 0 dlSng text, bsn không this nhân chia clSng tris gì vsi cái "6500" này đssc. Đis có this tính toán visi "6500", bsn phisi đisi nó sang disng sis: =VALUE(RIGHT("SQA6500",4)) 6500 Cũng là 6500, nhisng bây gis bsn có this cisng tris nhân chia visi nó. *Trích xuist his và tên (ví dis dùng đis trích cho tên tiisng Anh, bis qua tên đism) Cách làm là dùng hàm FIND() đ0 tìm nh0ng khoang tr0ng phân cách gi0a h0 và tên, sau đó dùng hàm LEFT() đ0 tách ph0n tên, và hàm RIGHT() đ0 tách ph0n h0. Đ0 l0y ph0n tên (First Name), chúng ta dùng công th0c sau (gi0 s0 h0 tên n0m 0 cell A2): =LEFT(A2, FIND(" ", A2) - 1) Nghĩa là dùng hàm FIND() đ0 tìm v0 trí c0a ký t0 tr0ng đ0u tiên k0 t0 bên trái, ví d0 nó là v0 trí th0 5, khi đó hàm LEFT() s0 xác đ0nh đ00c cái tên này g0m có 4 ch0 (= 5-1). Đ0 l0y ph0n h0 (Last Name), chúng ta dùng công th0c: =RIGHT(A2, LEN(A2) - FIND(" ", A2)) B0n t0 d0ch câu này nhé! *Trích xu0t h0, tên đ0m và tên (ví d0 v0i tên ti0ng Anh, ph0n tên đ0m đ00c vi0t t0t) Cách làm gi0ng nh0 bài Trích xu0t h0 và tên 0 trên, tuy nhiên có khác m0t chút, đ0 trích thêm ph0n tên đ0m. Gi0 s0 H0 và Tên (full name) n0m 0 cell A2, và đang có giá tr0 là Karen E. Hammond Đ0u tiên, nh0 bài trên, dùng công th0c sau đ0 tách ph0n Tên (first name): =LEFT(A2, FIND(" ", A2) - 1) Karen Công th0c FIND(" ", A2) s0 cho k0t qu0 là 6, là v0 trí c0a kho0ng tr0ng đ0u tiên (sau ch0 Karen). Đ0 tìm v0 trí c0a kho0ng tr0ng th0 hai, thì b0n ph0i gán v0 trí b0t đ0u tìm (start_num) là 7, ho0c là b0ng k0t qu0 c0a FIND(" ", A2) c0ng thêm 1: =FIND(" ", A2, FIND(" ",A2) + 1) R0i dùng k0t qu0 c0a công th0c này làm tham s0 cho hàm RIGHT() đ0 trích ra ph0n H0 (last name): =RIGHT(A2, LEN(A2) - FIND(" ", A2, FIND(" ", A2) +1)) Hammond Đ0 trích ph0n tên đ0m, dùng hàm FIND() đ0 tìm v0 trí c0a d0u ch0m (.) r0i đ0a vào trong công th0c c0a hàm MID() đ0 tìm ký t0 đ0ng tr00c d0u ch0m: =MID(A2, FIND(".", A2) - 1, 1) E HÀM LOGIC Hàm AND Có l0 kh0i gi0i thích nh0. AND có nghĩa là VÀ. V0y thôi. Dùng hàm này khi mu0n nói đ0n cái này và cái này và cái này Cú pháp: AND(logical1 [, logical2] [, logical3]...) logical: Nh0ng bi0u th0c s0 đ00c xét xem đúng (TRUE) hay sai (FALSE) N0u t0t c0 các bi0u th0c đ0u đúng, hàm AND() s0 tr0 v0 giá tr0 TRUE, và ch0 c0n 1 trong các bi0u th0c sai, hàm AND() s0 tr0 v0 giá tr0 FALSE. B0n có th0 dùng hàm AND() b0t c0 ch0 nào b0n mu0n, nh0ng th00ng thì hàm AND() hay đ00c dùng chung v0i hàm IF(). Ví d0: =IF(AND(B2 > 0, C2 > 0), "1000", "No bonus") N0u giá tr0 0 B2 v