Giáo trình Tin học đại cương và ứng dụng - Chương 5c: Công thức và một số hàm thông dụng - Trần Quang Hải Bằng

Nội dung text: Giáo trình Tin học đại cương và ứng dụng - Chương 5c: Công thức và một số hàm thông dụng - Trần Quang Hải Bằng

1. Tin hc đi cươ ng và ng dng (Một số hàm thơng dụng trong Excel) Tr ần Quang H ải B ằng Faculty of Information Technology University of Communication and Transport (Unit 2) Office location: Administration building, Block D3, Room 6 Office phone: 38962018 Cell phone: N/A Email: bangtqh@hotmail.com Ch5c. Cơng th c và mt s hàm thơng dng 1. Nh c li khái ni m cơng th c 2. Khái ni m hàm 3. Nh p cơng th c và hàm 4. Mt s hàm thơng dng 5. Bài tp th c hành s 2 Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 2
2. 1. Cơng th c (nh c li) n Cơng th c ¤ Bt đu bi du “=“ ¤ Gm: n Đa ch , hng, mi n, n Tốn t n Hàm n VD: ¤ = A1+A2-B2 ¤ = SIN(A1) + COS(B2) ¤ = LN(A5) Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 3 2. Khái ni m hàm n Các cơng th c tính tốn đưc xây dng tr ưc. n Ph c v các tính tốn thơng dng. n Cú pháp: Tên hàm (danh sách đi s) n Đi s đưc phân cách bi du ph y ¤ Ví d: =rank(x,range,order) n Đi s cĩ th là giá tr , đa ch , hng, Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 4
3. Các lo i hàm n Tốn hc và lưng n Th i gian (Date and giác (Math and Time) Trigonometry) n Lo-gic (Logical) n Th ng kê (Statistical) n Cơ s d li u n Tài chính (Financial) (Database) n Tra cu và tham chi u (Lookup and n Thơng tin Reference) (Information) n V ă n bn (Text) n K thu t (Engineering) Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 5 Ví d n Tốn hc và lưng giác ¤ ABS(X): tr tuy t đi ¤ SIN(X), COS(X) ¤ LN(X) n Th ng kê ¤ AVERAGE(mi n): tính trung bình n Th i gian ¤ NOW(): th i đ i m hi n ti ¤ DATE(y,m,d) Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 6
4. 3. Nh p cơng th c và hàm n Nh p tr c ti p vào ơ n S dng thanh cơng th c ¤ Kích chu t vào bi u tưng đ fx m hp tho i ch n hàm. ¤ Select a category: lo i hàm. ¤ Select a function: ch n hàm. Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 7 4. Mt s hàm thơng dng n Mt s hàm tốn hc và lưng giác n Mt s hàm th ng kê n Mt s hàm x lý v ă n bn n Mt s hàm x lý th i gian n Mt s hàm tra cu và tham chi u n Mt s hàm Logic Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 8
5. 4.1. Mt s hàm tốn hc và lưng giác {1} n ABS(X) n CEILING (X,N) ¤ Giá tr tuy t đi ca ¤ Tr v s nh nh t ≥ X X và chia ht cho N n ABS(4) = ABS(-4) = 4 ¤ đ n ABS(-4.5) = 4.5 N ây cĩ th coi là sai n INT(X) s n CEILING (4.27, 0.1) = 4.3 ¤ Làm trịn “dưi” ti n FLOOR (X,N) mt s nguyên gn nh t ¤ Tr v s ln nh t ≤ X n INT(-4.45) = -5 và chia ht cho N n INT( 4.55) = 4 n FLOOR (4.27, 0.1) = 4.2 Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 9 4.1. Mt s hàm tốn hc và lưng giác {2} n ROUND(X,N) ¤ Làm trịn X ¤ N - s ch s sau du ph y “.” n ROUND(4.27, 1) = 4.3 n ROUND(-4.27, 0) = - 4 n ROUND(16.27, -1) = 20 n TRUNC(X, [N]) ¤ Ph n nguyên ca X ¤ N - s ch s sau du ph y “.” n TRUNC(-4.45) = TRUNC(-4.45, 0) = - 4 n TRUNC(11.276, 2) = 11.27 n TRUNC(16.276, -1) = 10 Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 10
6. 4.1. Mt s hàm tốn hc và lưng giác {3} n COS(X) n LOG10(X) ¤ COSIN ca X (radian) ¤ Logarit cơ s 10 ca X n n ACOS(X) LN(X) ¤ Logarit Neper ca X ¤ ARCCOS ca X n PI() n SIN(X) ¤ 3.14159 ¤ SIN ca X n RANDIANS (đ) n TAN(X) ¤ Chuy n t đơn v đ sang đơn v Radian ¤ TANG ca X n DEGREES(radian) ¤ Chuy n t Radian sang đ Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 11 4.1. Mt s hàm tốn hc và lưng giác {4} n EXP(X) n QUOTIENT(X,Y) ¤ eX ¤ X/Y n SQRT(X) ¤ Ph i la ch n ¤ C ă n bc 2 ca X Analysis Toolpak n MOD(X,Y) trong Tools ¤ X mod Y Add-ins n RAND() ¤ Tr v s ng u nhiên trong kho ng (0,1) Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 12
7. 4.1. Mt s hàm tốn hc và lưng giác {5} n SUM(X1,X2, ) ¤ Tng dãy s X1,X2, n SUM(mi n) ¤ Tng các s trong mi n ¤ Ví d: n SUM(A1:A9) n SUM(B2 B15) Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 13 4.1. Mt s hàm tốn hc và lưng giác {6} n SUMIF(mi n ki m tra, đ i u ki n, mi n tng) ¤ Tính tng các ph n t trong mi n tng vi đ i u ki n ph n t tươ ng ng trong mi n ki m tra tho mãn đ i u ki n ¤ Mi n ki m tra đ i u ki n ¤ Mi n tính tng n Ví d: ¤ B6: S lưng cam ¤ Dùng cơng th c: n SUMIF(A1:A5, “Cam”, B1:B5) Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 14
8. 4.2. Mt s hàm th ng kê {1} n COUNT(X1,X2, ) hay COUNT (mi n) ¤ Đm s lưng giá tr s trong dãy, mi n n COUNT(A1:A5): đm s ơ cĩ d li u là s n COUNTA (X1,X2, ) hay COUNTA(mi n) ¤ Đm s ơ cĩ ch a d li u trong mi n n COUNTA(A1:A5): s ơ ch a d li u trong A1:A5 n COUNTIF (X1,X2, , đ i u_ki n) hay COUNTIF(mi n, đ i u_ki n) ¤ Đm s lưng giá tr tho mãn đ i u ki n n COUNTIF(C3:C11,">=5"): S ơ cĩ giá tr ≥5 trong C3:C11 n COUNTIF(C3 C11,”5”): S ơ cĩ giá tr = 5 trong C3 C11 Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 15 4.2. Mt s hàm th ng kê {2} n AVERAGE(X1,X2, ) ¤ Trung bình cng ca X1,X2, ¤ Cĩ th thay X1,X2, bi đa ch hay tên mi n n AVERAGE(A1:A5) n MAX(X1,X2, ) ¤ Giá tr ln nh t n MIN(X1,X2, ) ¤ Giá tr nh nh t n RANK(X,mi n,th _t ) ¤ Cho th hng ca X trong mi n ¤ th _t = 0 ho c khuy t thì sp xp theo chi u gi m dn, khác 0 thì sp xp t ă ng dn. Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 16
9. Ví d th ng kê: tng kt đi m Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 17 4.3. Mt s hàm x lý v ă n bn n LEFT(s,n) n VALUE(s) ¤ ¤ n ký t trái ca s. Chuy n xâu s thành s. n TEXT(value, đnh_d ng): n RIGHT(s,n) ¤ Chuy n thành xâu theo ¤ n ký t ph i ca s. đnh dng. n MID(s,m,n) ¤ Ví d: n TEXT(“01/01/2004”, ¤ n ký t, t v trí m. ”mmm”) = “Jan” n n TEXT(1/3,"0.00") = 0.33. TRIM(s) n LOWER(s): Đi xâu s thành ¤ B du cách th a. ch th ưng. n LEN(s) n UPPER(s): Đi xâu s thành ch hoa. ¤ Đ dài xâu s. Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 18
10. 4.4. Mt s hàm th i gian n NOW() n MONTH(xâu ngày tháng) ¤ Th i đ i m hi n ti ¤ Ly giá tr tháng n TODAY() ¤ MONTH("5/10/2004") = 5 ¤ Ngày hơm nay n YEAR(xâu ngày tháng) n DATE(n ă m, tháng, ¤ Ly giá tr n ă m ngày) n DATEVALUE(xâu ngày) ¤ N ă m = 1900-9999 ¤ ¤ DATE(99,1,1)=01/01/1999 Chuy n xâu sang d li u ¤ DATE(2004,1,1)=01/01/2004 s bi u di n ngày tháng ¤ n DAY(xâu ngày tháng) DATEVALUE("01/01/1900") = 1 ¤ Ly giá tr ngày ¤ DAY(“4-Jan”) = 4 Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 19 4.5. Mt s hàm tra cu và tham chi u {1} n VLOOKUP(tr _tra_c u, bng_tra_c u, ct_l y_d _li u, ki u_tra_c u) ¤ Tra cu giá tr ca ơ thu c ct th ct_l y_d _li u mà giá tr ca ơ thu c ct đu tiên cĩ giá tr bng tr tra cu. n Ki u tra cu quy đnh cách th c tra cu: ¤ 0 (false): n So kh p n Vùng tra cu khơng cn sp xp ¤ 1 (true): n So gn kh p (tìm giá tr “gn nh t”) n Vùng tra cu ph i đưc sp xp sn n Nĩi chung, vùng tra cu nên đưc sp xp tr ưc khi s dng vlookup đ tra cu. Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 20
11. Ví d s dng VLOOKUP n VT xác đnh bi mã VT. n Vt tư nh p ¤ Mi n A16:B24 ¤ Ct th 2 (ct B) n Báo cáo vt tư ¤ Mi dịng ng vi mt vt tư. ¤ Ct “NH P”: s lưng vt tư nh p n Cn ph i tra cu t A16:B24, s dng cơng th c: =VLOOKUP(B9,$D$17 :$E$25,2,1) Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 21 4.5. Mt s hàm tra cu và tham chi u {2} n HLOOKUP(tr _tra_c u, bng_tra_c u, hàng_l y_d _li u, ki u_tra_c u) ¤ Gi ng hàm VLOOKUP nh ưng d li u đưc x lý theo hàng n INDEX (mi n,hàng,c t) ¤ Tham chi u ti ơ cĩ s th t hàng và ct trong mi n đưc truy n vào ¤ Ví d: n INDEX(A2:D9,5,2) s tham chi u đn hàng th 5, ct th 2 trong mi n A2:D9 Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 22
12. 4.6. Mt s hàm Logic n NOT(X) n AND(X1,X2, ) n OR(X1,X2, ) n IF(đ i u_ki n,giá_tr _1,giá_tr _2) ¤ Nu đ i u ki n đúng, tr v giá tr 1 ¤ Nu đ i u ki n sai, tr v giá tr 2 ¤ Giá tr 2 cĩ th là mt hàm IF khác ¤ Ví d: n IF(B2>5, “Đt yêu cu”, “Khơng đt”) n IF(B2>=8, “Gi i”, IF(B2<5,”Tr ưt”, “Đt yêu cu”)) Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 23 5. Bài tp th c hành 2 n Bài tp th c hành 2 (tr.11, GT Tin hc Excel ng dng) n Lp bng đ i m ¤ AVERAGE: tính TB. ¤ SUM: Tính tng. ¤ COUNTIF: Đm. ¤ IF: Xp lo i. ¤ VLOOKUP: Tra cu s trình. ¤ RANK: Xp th . Ch5c. Cơng th c và mt s hàm thơng d ng trong Excel 24