›
›

Bài giảng Excel - Chương 2: Hàm và biểu thức trong Excel

pdf 37 trang ngocly 2450
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Excel - Chương 2: Hàm và biểu thức trong Excel", để 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_excel_chuong_2_ham_va_bieu_thuc_trong_excel.pdf

Nội dung text: Bài giảng Excel - Chương 2: Hàm và biểu thức trong Excel

  1. Chương 2. HÀM VÀ BIỂU THỨC TRONG EXCEL
  2. CÁC TỐN TỬ TRONG EXCEL 3/11/2004 Bài giảng Excel 2
  3. Hàm luận lý  AND (đối 1, đối 2, , đối n): phép VÀ, là hàm logic, chỉ đúng khi tất cả các đối số cĩ giá trị đúng. Các đối số là các hằng, biểu thức logic. VD: = AND (B3>=23,B3 =25,D3<23)  NOT (logic) Trả về TRUE nếu cĩ ít nhất một logic của nĩ là TRUE, trả về False nếu tất cả các logic là False 3/11/2004 Bài giảng Excel 3
  4. Hàm số học Hàm lấy giá trị tuyệt đối ABS(N) N là số / biểu thức số Ví dụ: =ABS(-25) kết quả 25 =ABS(5-149) kết quả 144 Hàm lấy căn SQRT(N) N là số / biểu thức số (N>0) Ví dụ: SQRT(25) kết quả 5 3/11/2004 Bài giảng Excel 4
  5. Hàm số học Hàm lấy phần nguyên INT(N) N là số / biểu thức số Ví dụ: =INT(236.26) kết quả 236 Hàm lấy phần dư phép chia nguyên N cho M MOD(N, M) Ví dụ: MOD(10;3) kết quả 1 Hàm lấy giá trị PI (3.141593) PI() Hàm mũ POWER(x ; y) Trả về x mũ y Ví dụ: =power(4 ; 2) kết quả 16 3/11/2004 Bài giảng Excel 5
  6. Hàm số học Hàm làm trịn lên ROUND(biểu thức số ; N) • N>0: làm trịn bên phải cột thập phân • N<0: làm trịn bên trái cột thập phân • N=0: làm trịn, khơng lấy số lẻ Ví dụ: =ROUND(35123.376;2)kết quả 35123.38 =ROUND(35123.376;-3) kết quả 35000 =ROUND(35123.376;0)kết quả 35123 3/11/2004 Bài giảng Excel 6
  7. Hàm thống kê  SUM (đối 1, đối 2, , đối n): cho tổng của các đối số Các đối số là các hằng, địa chỉ ơ, miền.  AVERAGE (đối 1, đối 2, , đối n): cho giá trị TBC c các số 3/11/2004 Bài giảng Excel 7
  8. Hàm thống kê  MAX (đối 1, đối 2, , đối n): cho giá trị lớn nhất.  MIN (đối 1, đối 2, , đối n): cho giá trị nhỏ nhất. 3/11/2004 Bài giảng Excel 8
  9. Hàm thống kê  COUNT(đối1, đối2, ): đếm số lượng các ơ cĩ chứa số và các số trong các đối số.  Các đối số là các số, ngày tháng, địa chỉ ơ, địa chỉ miền. Ví dụ 1 Ví dụ 2 3/11/2004 Bài giảng Excel 9
  10. Hàm thống kê COUNTA(value1; value2; .) Đếm số lượng các ơ chứa dữ liệu trong danh sách 3/11/2004 Bài giảng Excel 10
  11. Hàm thống kê RANK(number , ref , order) Trả về thứ hạng của số number trong một danh sách các đối số ref  Number: là số muốn tìm hạng của nĩ  Ref: là một dãy hay một tham chiếu đến một danh sách các đối số  Order: là số chỉ định cách đánh hạng: Nếu order = 0 hoặc khơng ghi thì số lớn được xếp hạng trước Nếu order khác 0 thì số nhỏ được xếp hạng trước 3/11/2004 Bài giảng Excel 11
  12. Hàm thống kê Khi thứ thự xếp bằng 1 Khi thứ thự xếp bằng 0 3/11/2004 Bài giảng Excel 12
  13. Hàm thống kê Hàm tính tổng của các tích SUMPRODUCT (array1; [array2]; ) = 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3. 3/11/2004 Bài giảng Excel 13
  14. CÁC HÀM ĐIỀU KIỆN  IF (bt logic, trị đúng, trị sai):  Hiển thị trị đúng nếu BT logic cĩ g/t True  Hiển thị trị sai nếu BT logic cĩ g/t False VD: =IF(A3>=5,“Đỗ”,“Trượt”)  - Hàm IF cĩ thể viết lồng nhau. VD: = IF(C6 400,3,2)) - Hàm trên cho kết quả của phép thử sau: 1 nếu [dữ liệu trong ơ C6] ≤ 300  2 nếu 300 400 3/11/2004 Bài giảng Excel 14
  15. CÁC HÀM ĐIỀU KIỆN  SUMIF (miền_đ/k, đ/k, miền_tổng): hàm tính tổng cĩ điều kiện Giả sử miền B2:B5 chứa các g/t tiền nhập 4 mặt hàng tương ứng 100, 200, 300, 400. Miền C2:C5 chứa tiền lãi tương ứng 7, 14, 21, 28 thì hàm SUMIF(B2:B5,“>160”,C2:C5) cho kết quả bằng 63 (=14+21+28) 3/11/2004 Bài giảng Excel 15
  16. CÁC HÀM ĐIỀU KIỆN  COUNTIF(miền_đếm, điều_kiện): đếm số lượng các ơ trong miền đếm thoả mãn điều kiện. Ví dụ 1 Ví dụ 2 3/11/2004 Bài giảng Excel 16
  17. Hàm chuỗi  LEFT(“Chuỗi ký tự”, n): Cho n ký tự bên trái của chuỗi.  VD: =LEFT(“Gia Lâm – Hà Nội”,7) cho kết quả là chuỗi “Gia Lâm”  RIGHT(“Chuỗi ký tự”, n): Cho n ký tự bên phải của chuỗi.  VD: =RIGHT(“Gia Lâm – Hà Nội”,6) cho kết quả là chuỗi “Hà Nội”  MID(“Chuỗi ký tự”, m, n): Cho n ký tự tính từ ký tự thứ m của chuỗi.  VD: =MID(“Gia Lâm–Hà Nội”,9,2) cho kết quả là chuỗi “Hà” 3/11/2004 Bài giảng Excel 17
  18. Hàm chuỗi Hàm tính chiều dài của chuỗi LEN (“chuỗi”) Trả là chiều dài trong chuỗi, tức là số ký tự cĩ trong chuỗi Ví dụ: =LEN (“Microsoft Excel”) kq: 15 Hàm đổi chuỗi thường LOWER (“chuỗi”) Ví dụ: =LOWER(“KIẾN ĂN CÁ”) kq: “kiến ăn cá” 3/11/2004 Bài giảng Excel 18
  19. Hàm chuỗi Hàm đổi chuỗi hoa UPPER (“chuỗi”) Ví dụ: =UPPER(“kiến ăn cá”) kq: “KIẾN ĂN CÁ” Hàm đổi các ký tự đầu của “word” thành hoa PROPER (“chuỗi”) Ví dụ: =PROPER(“kiến ăn cá”) kq: “Kiến Ăn Cá” 3/11/2004 Bài giảng Excel 19
  20. Hàm chuỗi Hàm xố khoảng trống thừa trong chuỗi TRIM (“chuỗi”) Ví dụ: =TRIM (“ Xin Chào ”) kq: “Xin chào” Hàm đổi chuỗi số thành trị số VALUE (“chuỗi số”) Ví dụ: =VALUE (“123”) kq: 123 3/11/2004 Bài giảng Excel 20
  21. Hàm kiểu ngày WEEKDAY(“chuỗi ngày”) Trả về giá trị Thứ trong tuần (từ 1 đến 7: chủ nhật đến thứ hai) Ví dụ: =WEEKDAY(“2/09/2001”) kq:1 TODAY () Trả về ngày hiện tại của hệ thống máy tính Ví dụ: =TODAY() kq: 10/05/2007 3/11/2004 Bài giảng Excel 21
  22. Hàm kiểu ngày YEAR (“chuỗi ngày”) Trả về số năm tương ứng (1900 đến 2078) Ví dụ: =YEAR(“24/12/2004”) kq: 2004 DAYS360(“ngày bắt đầu”;”ngày kết thúc”) Trả về tổng số ngày từ “ngày bắt đầu” đến “ngày kết thúc” 3/11/2004 Bài giảng Excel 22
  23. Hàm dị tìm  VLOOKUP (trị_tra_cứu, bảng_tra_cứu, cột_lấy_dữ_liệu, [True/False]): tra cứu g/t với các g/t trong cột đầu tiên của bảng và hiển thị dữ liệu tương ứng trong bảng tra cứu nằm trên cột ở đối số 3. VD: = VLOOKUP(E3, $E$12:$F$16, 2, True) - Nếu g/t tra cứu nhỏ hơn g/t nhỏ nhất trong cột đầu tiên của bảng thì trả về lỗi #N/A. - Nếu đối số thứ 4 bằng True (hoặc 1): + Các g/t trong cột đầu tiên của bảng phải được sắp xếp tăng dần. + Nếu g/t tra cứu khơng cĩ trong cột đầu tiên của bảng thì hàm sẽ tra cứu g/t trong bảng ≤ g/t tra cứu. 3/11/2004 Bài giảng Excel 23
  24. Hàm dị tìm 3/11/2004 Bài giảng Excel 24
  25. Hàm dị tìm  HLOOKUP(g/t, bảng_g/t, hàng_lấy_d.liệu, [1/0]): hàm tra cứu theo hàng, tương tự hàm VLOOKUP  ISNA(value): Trả về giá trị TRUE nếu gặp lỗi #N/A, ngược lại trả về FALSE. 3/11/2004 Bài giảng Excel 25
  26. Hàm dị tìm  Match(lookup_value, lookup_ref, type)  lookup_value: giá trị cần tìm, giá trị này cĩ thể là: chuỗi, số của một ơ chứa dữ liệu là số hay chuỗi  lookup_ref: phạm vi vùng dữ liệu (chỉ một dịng hay cột)  type: các kiểu so sánh  1:  Tìm giá trị lớn nhất nhỏ hơn hay bằng lookup_value  Lookup_ref: phải được sắp theo thứ tự tăng dần  Nếu tất cả trong lookup_ref đều nhỏ hơn lookup_value thì hàm trả về #NA  -1:  Tìm giá trị nhỏ nhất lớn hơn hay bằng lookup_value  Lookup_ref: phải được sắp theo thứ tự giảm dần  Nếu tất cả trong lookup_ref đều nhỏ hơn lookup_value thì hàm trả về #NA  0: dị tìm chính xác đúng, nếu khơng trả về #N/A 3/11/2004 Bài giảng Excel 26
  27. Hàm dị tìm 3/11/2004 Bài giảng Excel 27
  28. Hàm dị tìm  INDEX(array,row_num,column_num): Trả về giá trị của ô tại dòng và cột trong một dãy ô Pear 3/11/2004 Bài giảng Excel 28
  29. Hàm tài chính  SLN(const, salvage, life)  Cơng dụng: Tính khấu hao đối với một thiết bị cĩ chi phí ban đầu bởi const, giá trị cịn lại là cuối thời gian sống của thiết bị định bởi salvage và thời gian thiết bị định bởi life bằng cách chia đều khấu hao cho các năm.  Cơng thức tính khấu hao: (const/life)-(salvage/life)  Ví dụ: một thiết bị được mua với giá 40000, sau 5 năm trị giá của máy cịn lại 12000. Tính chi phí khấu hao hàng năm của thiết bị trên.  SLN(40000,12000,5) = 5600  Tổng cộng khấu hao sau 5 năm là 5600*5 3/11/2004 Bài giảng Excel 29
  30. Hàm tài chính  Hàm: SYD(const, salvage, life, nper)  Cơng dụng: tính khấu hao tại một chu kỳ định bởi Nper bằng cách dùng phương pháp tính khấu hao huỹ kế (giảm dần theo từng năm)  Cơng thức tính khấu hao  ((cost - salvage)*(life-nper+1)*2)/(life*(life+1)) 3/11/2004 Bài giảng Excel 30
  31. Hàm tài chính  Ví dụ: một thiết bi mới mua với giá 40000, sau 5 năm trị giá của máy cịn 12000 tính chi phí khấu hao hàng năm của thiết bị trên.  Năm thứ 1 = SYD(40000, 12000, 5, 1)= 9333  Năm thứ 2 = SYD(40000, 12000, 5, 2)= 7467  Năm thứ 3 = SYD(40000, 12000, 5, 3)= 5600  Năm thứ 4 = SYD(40000, 12000, 5, 4)= 3733  Năm thứ 5 = SYD(40000, 12000, 5, 5)= 1867  Tổng cộng tiề khấu hao sau 5 năm = 28000 = (9333 + 7476 + 5600 + 3733 + 1867) 3/11/2004 Bài giảng Excel 31
  32. Hàm tài chính  Hàm IRR : IRR(value, geuss)  Cơng dụng: tính lãi suất thu nhâp bộ của một dãy số biểu thị thu nhập thường kỳ định bởi tham số value. Tham số guess (từ 0 đến 1) ước lượng tỉ lệ thu nhập cho một chu kỳ. 3/11/2004 Bài giảng Excel 32
  33. Hàm tài chính  Ví dụ: Giả sử để kinh doanh 1 ngành hàng, bạn cần đầu tư 70000 và mong muốn thu nhập trong 5 năm đầu tiên với các giá trị lần lượt là 12000, 15000, 18000, 21000 và 26000 A B 1 NĂM SỐ TIỀN 2 -70000 3 1 12000 4 2 15000 5 3 18000 6 4 21000 7 5 26000  Lãi suất thu nhập nội bộ được tính bởi hàm IRR như sau:  Sau 2 năm: = IRR(B2:B4) kết quả là -44.35%  Sau 3 năm: = IRR(B2:B5) kết quả là -18.21%  Sau 4 năm: = IRR(B2:B6) kết quả là -2.12%  Sau 5 năm: = IRR(B2:B7) kết quả là -8.66% 3/11/2004 Bài giảng Excel 33
  34. Hàm tài chính  Hàm PMT : PMT(Rate, Nper, Pv, Fv, Type)  Cơng dụng: tính số tiền phải trả vào mỗi chu kỳ ứng với các đối số cho trước. Lãi suất rate, số chu kỳ nper, giá trị hiện tại Pv, giá trị tương lai Fv, kiểu thanh tốn Type. Nếu bỏ qua đối số Fv và tyoe thì Excel sẽ lấy giá trị mặc nhiên là 0.  Ví dụ: Mua căn nhà trị giá 190000 và trả gĩp theo lãi suất cố định là 10%/năm trong vịng 30 năm. Như vậy hàng tháng phải trả số tiền là:  =PMT(0.1/12,12*30,190000) kết quả -1667 (giá trị âm đĩ là số tiền phải trả ) 3/11/2004 Bài giảng Excel 34
  35. Hàm tài chính  Hàm rate : rate(nper, pmt, Pv, Fv, Type, Guess)  Cơng dụng: tính lãi suất hàng năm theo các đối số cho trước  Nếu bỏ qua các đối số Fv và Type,guess thì excel sẽ lấy giá trị mặc nhiên là 0  Ví dụ: mua một chiếc xe trị giá 9000 và trả gĩp hàng tháng 800 trong 12 tháng. Vậy lãi suất là RATE(12,-800,9000) = 1.007% (tháng). Do đĩ lãi suất một năm sẽ là 1.007%*12 = 12.084% 3/11/2004 Bài giảng Excel 35
  36. Hàm tài chính  Hàm FV: FV(rate, nper, pmt, Pv, Type)  Cơng dụng: tính giá trị kết qủa vào cuối thời gian đầu tư  Ví dụ: Số tiền bỏ ra ban đầu là 2000, sau đĩ bỏ thêm vào mỗi tháng 100% trong vịng 5 năm (60 tháng) lãi suất hàng năm là 8%. Giá trị kết quả đầu tư như sau: =FV(0.08/12,5*12,-100,-2000,1)=10376.36 3/11/2004 Bài giảng Excel 36
  37. Hàm tài chính  Hàm PV: PV(Rate, Nper, Pmt, Fv, Type)  Cơng dụng: Tính giá trị hiện tại ứng với số tiền khơng đổi PMT phải trả thường trong vịng Nper chu kỳ với lãi suất Rate. Hàm PV cho kết quả là tổng số tiền vào thời điểm hiện tại cĩ giá trị ứng với tồn bộ số tiền thanh tốn trong tương lai vào mỗi chu kỳ theo lãi suất Rate.  Ví dụ: Dự định trả gĩp 1 xe hơi và trả gĩp hàng tháng là 220$. Trong vịng 4 năm với lãi suất vay là 9%. Như vậy số tiền phải trả gĩp ứng với hiện tại là: =PV(0.09/12, 4*12, -220) = 8840.65 3/11/2004 Bài giảng Excel 37