›
›

Bài giảng Công thức và một số hàm thông dụng - Nguyễn Khắc Quốc

pdf 24 trang hapham 2330
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Công thức và một số hàm thông dụng - Nguyễn Khắc Quốc", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfbai_giang_cong_thuc_va_mot_so_ham_thong_dung_nguyen_khac_quo.pdf

Nội dung text: Bài giảng Công thức và một số hàm thông dụng - Nguyễn Khắc Quốc

  1. MỘT SỐ HÀM THÔNG DỤNG Th.S Nguyễn Khắc Quốc IT-Department
  2. Ch5c. Công thức và một số hàm thông dụng 1. Nhắc lại 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. Một số hàm thông dụng 5. Bài tập thực hành số 2 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 2
  3. 1. Công thức (nhắc lại)  Công thức  Bắt đầu bởi dấu “=“  Gồm:  Địa chỉ, hằng, miền,  Toán tử  Hàm  VD:  = A1+A2-B2  = SIN(A1) + COS(B2)  = LN(A5) 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 3
  4. 2. Khái niệm hàm  Các công thức tính toán được xây dựng trước.  Phục vụ các tính toán thông dụng.  Cú pháp: Tên hàm (danh sách đối số)  Đối số được phân cách bởi dấu phảy  Ví dụ: =rank(x,range,order)  Đối số có thể là giá trị, địa chỉ, hằng, 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 4
  5. Các loại hàm  Toán học và lượng  Thời gian (Date and giác (Math and Time) Trigonometry)  Lo-gic (Logical)  Thống kê (Statistical)  Cơ sở dữ liệu  Tài chính (Financial) (Database)  Tra cứu và tham chiếu (Lookup and  Thông tin Reference) (Information)  Văn bản (Text)  Kỹ thuật (Engineering) 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 5
  6. Ví dụ  Toán học và lượng giác  ABS(X): trị tuyệt đối  SIN(X), COS(X)  LN(X)  Thống kê  AVERAGE(miền): tính trung bình  Thời gian  NOW(): thời điểm hiện tại  DATE(y,m,d) 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 6
  7. 3. Nhập công thức và hàm  Nhập trực tiếp vào ô  Sử dụng thanh công thức  Kích chuột vào biểu tượng fx để mở hộp thoại chọn hàm.  Select a category: loại hàm.  Select a function: chọn hàm. 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 7
  8. 4. Một số hàm thông dụng  Một số hàm toán học và lượng giác  Một số hàm thống kê  Một số hàm xử lý văn bản  Một số hàm xử lý thời gian  Một số hàm tra cứu và tham chiếu  Một số hàm Logic 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 8
  9. 4.1. Một số hàm toán học và lượng giác {1}  ABS(X)  CEILING (X,N)  Giá trị tuyệt đối của  Trả về số nhỏ nhất ≥ X X và chia hết cho N  ABS(4) = ABS(-4) = 4  N ở đây có thể coi là sai  ABS(-4.5) = 4.5 số  INT(X)  CEILING (4.27, 0.1) = 4.3  Làm tròn “dưới” tới  FLOOR (X,N) một số nguyên gần nhất  Trả về số lớn nhất ≤ X  INT(-4.45) = -5 và chia hết cho N  INT( 4.55) = 4  FLOOR (4.27, 0.1) = 4.2 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 9
  10. 4.1. Một số hàm toán học và lượng giác {2}  ROUND(X,N)  Làm tròn X  N - số chữ số sau dấu phảy “.”  ROUND(4.27, 1) = 4.3  ROUND(-4.27, 0) = - 4  ROUND(16.27, -1) = 20  TRUNC(X, [N])  Phần nguyên của X  N - số chữ số sau dấu phảy “.”  TRUNC(-4.45) = TRUNC(-4.45, 0) = - 4  TRUNC(11.276, 2) = 11.27  TRUNC(16.276, -1) = 10 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 10
  11. 4.1. Một số hàm toán học và lượng giác {3}  COS(X)  LOG10(X)  COSIN của X (radian)  Logarit cơ số 10 của X  LN(X)  ACOS(X)  Logarit Neper của X  ARCCOS của X  PI()  SIN(X)  3.14159  SIN của X  RANDIANS (độ)  TAN(X)  Chuyển từ đơn vị độ sang đơn vị Radian  TANG của X  DEGREES(radian)  Chuyển từ Radian sang độ 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 11
  12. 4.1. Một số hàm toán học và lượng giác {4}  EXP(X)  QUOTIENT(X,Y) X  e  X/Y  SQRT(X)  Phải lựa chọn  Căn bậc 2 của X Analysis Toolpak  MOD(X,Y) trong Tools  X mod Y Add-ins  RAND()  Trả về số ngẫu nhiên trong khoảng (0,1) 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 12
  13. 4.1. Một số hàm toán học và lượng giác {5}  SUM(X1,X2, )  Tổng dãy số X1,X2,  SUM(miền)  Tổng các số trong miền  Ví dụ:  SUM(A1:A9)  SUM(B2 B15) 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 13
  14. 4.1. Một số hàm toán học và lượng giác {6}  SUMIF(miền kiểm tra, điều kiện, miền tổng)  Tính tổng các phần tử trong miền tổng với đ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 tổng  Ví dụ:  B6: Số lượng cam  Dùng công thức:  SUMIF(A1:A5, “Cam”, B1:B5) 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 14
  15. 4.2. Một số hàm thống kê {1}  COUNT(X1,X2, ) hay COUNT (miền)  Đếm số lượng giá trị số trong dãy, miền  COUNT(A1:A5): đếm số ô có dữ liệu là số  COUNTA (X1,X2, ) hay COUNTA(miền)  Đếm số ô có chứa dữ liệu trong miền  COUNTA(A1:A5): số ô chứa dữ liệu trong A1:A5  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  COUNTIF(C3:C11,">=5"): Số ô có giá trị ≥5 trong C3:C11  COUNTIF(C3 C11,”5”): Số ô có giá trị = 5 trong C3 C11 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 15
  16. 4.2. Một số hàm thống kê {2}  AVERAGE(X1,X2, )  Trung bình cộng của X1,X2,  Có thể thay X1,X2, bởi địa chỉ hay tên miền  AVERAGE(A1:A5)  MAX(X1,X2, )  Giá trị lớn nhất  MIN(X1,X2, )  Giá trị nhỏ nhất  RANK(X,miền,thứ_tự)  Cho thứ hạng của X trong miền  thứ_tự = 0 hoặc khuyết thì sắp xếp theo chiều giảm dần, khác 0 thì sắp xếp tăng dần. 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 16
  17. Ví dụ thống kê: tổng kết điểm 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 17
  18. 4.3. Một số hàm xử lý văn bản  LEFT(s,n)  VALUE(s)  Chuyển xâu s thành số.  n ký tự trái của s.  TEXT(value, định_dạng):  RIGHT(s,n)  Chuyển thành xâu theo  n ký tự phải của s. định dạng.  Ví dụ:  MID(s,m,n)  TEXT(“01/01/2004”,  n ký tự, từ vị trí m. ”mmm”) = “Jan”  TEXT(1/3,"0.00") = 0.33.  TRIM(s)  LOWER(s): Đổi xâu s thành  Bỏ dấu cách thừa. chữ thường.  LEN(s)  UPPER(s): Đổi xâu s thành chữ hoa.  Độ dài xâu s. 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 18
  19. 4.4. Một số hàm thời gian  NOW()  MONTH(xâu ngày tháng)  Thời điểm hiện tại  Lấy giá trị tháng  TODAY()  MONTH("5/10/2004") = 5  Ngày hôm nay  YEAR(xâu ngày tháng)  DATE(năm, tháng,  Lấy giá trị năm ngày)  DATEVALUE(xâu ngày)  Năm = 1900-9999  Chuyển xâu sang dữ liệu  DATE(99,1,1)=01/01/1999  DATE(2004,1,1)=01/01/2004 số biểu diễn ngày tháng  DATEVALUE("01/01/1900") = 1  DAY(xâu ngày tháng)  Lấy giá trị ngày  DAY(“4-Jan”) = 4 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 19
  20. 4.5. Một số hàm tra cứu và tham chiếu {1}  VLOOKUP(trị_tra_cứu, bảng_tra_cứu, cột_lấy_dữ_liệu, kiểu_tra_cứu)  Tra cứu giá trị của ô thuộc cột thứ cột_lấy_dữ_liệu mà giá trị của ô thuộc cột đầu tiên có giá trị bằng trị tra cứu.  Kiểu tra cứu quy định cách thức tra cứu:  0 (false):  So khớp  Vùng tra cứu không cần sắp xếp  1 (true):  So gần khớp (tìm giá trị “gần nhất”)  Vùng tra cứu phải được sắp xếp sẵn  Nói chung, vùng tra cứu nên được sắp xếp trước khi sử dụng vlookup để tra cứu. 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 20
  21. Ví dụ sử dụng VLOOKUP  VT xác định bởi mã VT.  Vật tư nhập  Miền A16:B24  Cột thứ 2 (cột B)  Báo cáo vật tư  Mỗi dòng ứng với một vật tư.  Cột “NHẬP”: số lượng vật tư nhập  Cần phải tra cứu từ A16:B24, sử dụng công thức: =VLOOKUP(B9,$D$17 :$E$25,2,1) 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 21
  22. 4.5. Một số hàm tra cứu và tham chiếu {2}  HLOOKUP(trị_tra_cứu, bảng_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  INDEX (miền,hàng,cột)  Tham chiếu tới ô có số thứ tự hàng và cột trong miền được truyền vào  Ví dụ:  INDEX(A2:D9,5,2) sẽ tham chiếu đến hàng thứ 5, cột thứ 2 trong miền A2:D9 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 22
  23. 4.6. Một số hàm Logic  NOT(X)  AND(X1,X2, )  OR(X1,X2, )  IF(điều_kiện,giá_trị_1,giá_trị_2)  Nếu điều kiện đúng, trả về giá trị 1  Nếu điều kiện sai, trả về giá trị 2  Giá trị 2 có thể là một hàm IF khác  Ví dụ:  IF(B2>5, “Đạt yêu cầu”, “Không đạt”)  IF(B2>=8, “Giỏi”, IF(B2<5,”Trượt”, “Đạt yêu cầu”)) 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 23
  24. 5. Bài tập thực hành 2  Bài tập thực hành 2 (tr.11, GT Tin học Excel ứng dụng)  Lập bảng điểm  AVERAGE: tính TB.  SUM: Tính tổng.  COUNTIF: Đếm.  IF: Xếp loại.  VLOOKUP: Tra cứu số trình.  RANK: Xếp thứ. 7/12/2014 Ch5c. Công thức và một số hàm thông dụng trong Excel 24