›
›

Bài giảng Tin học văn phòng - Bài 3: Hàm thống kê và công thức mảng - Thiều Quang Trung

pdf 45 trang ngocly 1170
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Tin học văn phòng - Bài 3: Hàm thống kê và công thức mảng - Thiều Quang Trung", để 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_tin_hoc_van_phong_bai_3_ham_thong_ke_va_cong_thuc.pdf

Nội dung text: Bài giảng Tin học văn phòng - Bài 3: Hàm thống kê và công thức mảng - Thiều Quang Trung

  1. BÀI 3 HÀM THỐNG KÊ VÀ CÔNG THỨC MẢNG GV: Th.S. Thiều Quang Trung Bộ môn Khoa học cơ bản Trường Cao đẳng Kinh tế đối ngoại
  2. Nội dung 1 • Hàm thống kê 2 • Khái niệm công thức mảng 3 • Ưu điểm của công thức mảng 4 • Hằng số mảng 5 • Ứng dụng công thức mảng GV. Thiều Quang Trung 2
  3. Nhóm hàm thống kê 1. Hàm AVERAGE(number1,number2, ) Tính trung bình cộng các số number1, number2, Nếu địa chỉ ô nằm trong đối số chứa kiểu ký tự, giá trị logic hoặc là các ô trống thì các giá trị này được bỏ qua. Các ô chứa giá trị bằng 0 đều được tính. Ví dụ: Nếu A1:A4 chứa các số 1,3,5,7 thì AVERAGE(A1:A4) bằng 4. GV. Thiều Quang Trung 3
  4. Nhóm hàm thống kê 2. Hàm MAX(number1, number2, ) Trả về số lớn nhất trong các đối số. Ví dụ: Nếu A1:A4 chứa các dữ liệu là 1,9,5,7 thì MAX(A1:A4) bằng 9. 3. Hàm MIN (number1, number2, ) Trả về số nhỏ nhất trong các đối số. Ví dụ: Nếu A1:A4 chứa các dữ liệu là 9,1,5,7 thì MIN(A1:A4) bằng 1. GV. Thiều Quang Trung 4
  5. Nhóm hàm thống kê 4. Hàm RANK(số, dãy số, loại thứ tự) Hàm sẽ trả về thứ tự của số trong dãy số, dãy số có thể là vùng giá trị hoặc địa chỉ của vùng dãy số cần xếp thứ tự. – Nếu loại thứ tự là 0 hoặc bỏ qua thì Excel sẽ xếp thứ bậc theo thứ tự giảm dần của dãy số. – Nếu loại thứ tự là một giá trị bất kỳ khác 0 thì Excel sẽ xếp thứ bậc theo thứ tự tăng dần của dãy số. – Ví dụ: Nếu A1:A4 chứa các dữ liệu là 3,1,5,7 thì RANK(A1,$A$1:$A$4,0) bằng 3, trong khi đó RANK(A1,$A$1:$A$4,1) bằng 2. GV. Thiều Quang Trung 5
  6. Nhóm hàm thống kê 5. Hàm COUNT(value1, value2, ) Trả về số lượng các ô có kiểu số trong các đối số. Ví dụ: Nếu A1:A4 chứa các dữ liệu là: 1, "test", 5, 7 thì COUNT(A1:A4) bằng 3. GV. Thiều Quang Trung 6
  7. Nhóm hàm thống kê 6. Hàm COUNTIF(vùng địa chỉ, điều kiện) Trả về số lượng các ô trong vùng địa chỉ thỏa mãn điều kiện. Điều kiện phải đặt trong cặp dấu nháy kép " " Ví dụ: Cho vùng địa chỉ như sau: hàm COUNTIF(A1:B3,">=4") bằng 5 A B 1 9 3 2 4 4 3 7 5 GV. Thiều Quang Trung 7
  8. Nhóm hàm thống kê 7. Hàm COUNTIFS(vùng đk1, đk1, [vùng đk2, đk2], ) Đếm các ô trong vùng thỏa mãn 1 hoặc nhiều điều kiện. vùng đk1: bắt buộc, là một vùng các ô để so sánh với điều kiện đếm đk1: bắt buộc, là điều kiện để đếm, giá trị của đk1 có thể là số, biểu thức, cột tham chiếu, chuỗi vùng đk2, đk2, là tùy chọn, không bắt buộc GV. Thiều Quang Trung 8
  9. Nhóm hàm thống kê • Ví dụ hàm COUNTIF và COUNTIFS A B C D E F G H Kiểu thanh 1 Mã khách Loại phòng Ngày đến Ngày đi Số Tuần Số ngày Tiền Trả toán 2 001 C Trả sau 27/08/2010 13/09/2010 2 3 1,300,000 3 002 B Trả sau 31/08/2010 14/09/2010 2 0 1,600,000 4 003 C Trả trước 05/09/2010 10/09/2010 0 5 500,000 5 004 B Trả sau 29/09/2010 17/10/2010 2 4 2,240,000 6 005 A Trả sau 17/09/2010 27/09/2010 1 3 1,600,000 7 006 C Trả sau 22/09/2010 01/10/2010 1 2 700,000 8 007 C Trả trước 09/10/2010 09/10/2010 0 0 0 9 008 B Trả sau 18/10/2010 24/10/2010 0 6 960,000 10 009 B Trả trước 19/10/2010 25/10/2010 0 6 960,000 11 010 A Trả trước 12/10/2010 14/10/2010 0 2 400,000 GV. Thiều Quang Trung 9
  10. Nhóm hàm thống kê • Đếm các khách thuê phòng loại B = COUNTIF(B2:B11,"B") • Đếm các khách thuê phòng loại B và kiểu trả sau: =COUNTIFS(B2:B11,"B",C2:C11,"Trả sau") • Đếm các khách thuê phòng loại B và kiểu trả sau, có ngày đến trong tháng 9: =COUNTIFS(B2:B11,"B",C2:C11,"Trả sau", D2:D11,">=2010/09/01",D2:D11,"<=2010/09/30") GV. Thiều Quang Trung 10
  11. So sánh hàm COUNTIF() và COUNTIFS() COUNTIF() COUNTIFS() 2 đối số bắt buộc: 2 đối số đầu tiên là bắt range và criterial buộc: range và criterial Các đối số còn lại là tùy chọn Đếm chỉ với một điều kiện Đếm với 1 hoặc nhiều điều kiện GV. Thiều Quang Trung 11
  12. Nhóm hàm thống kê 8. Hàm SUM(n1,n2, ) Trả về tổng của các đối số n1, n2 Các đối số có thể thay bằng địa chỉ khối của vùng dữ liệu cần tính tổng. Ví dụ: SUM(3,5) bằng 8. SUM(A1:A5) sẽ trả về tổng giá trị của các ô từ A1 đến A5. GV. Thiều Quang Trung 12
  13. Nhóm hàm thống kê 9. Hàm SUMIF(vùng điều kiện,điều kiện, vùng tổng) Trả về tổng giá trị số: nếu các ô trong vùng điều kiện thoả mãn điều kiện, hàm sẽ trả về tổng giá trị số trong các ô tương ứng trong vùng tổng. Chú ý: điều kiện phải đặt trong cặp dấu nháy kép " ". Ví dụ: SUMIF(A1:A3,">=6",B1:B3) bằng 8, giả sử có các dữ liệu ở địa chỉ sau: A B 1 9 3 2 4 4 3 7 5 GV. Thiều Quang Trung 13
  14. Nhóm hàm thống kê 10. Hàm SUMIFS(vùng tổng, vùng đk1, đk1, [vùng đk2, đk2], ) Tính tổng các ô trong thỏa mãn 1 hoặc nhiều điều kiện. vùng tổng: bắt buộc, là vùng các ô để tính tổng vùng đk1: bắt buộc, là vùng các ô để so sánh với điều kiện tính tổng đk1: bắt buộc, là điều kiện để tính tổng, giá trị của đk1 có thể là số, biểu thức, cột tham chiếu, chuỗi vùng đk2, đk2, là tùy chọn, không bắt buộc GV. Thiều Quang Trung 14
  15. Nhóm hàm thống kê • Ví dụ hàm SUMIF và SUMIFS A B C D E F G H Kiểu thanh 1 Mã khách Loại phòng Ngày đến Ngày đi Số Tuần Số ngày Tiền Trả toán 2 001 C Trả sau 27/08/2010 13/09/2010 2 3 1,300,000 3 002 B Trả sau 31/08/2010 14/09/2010 2 0 1,600,000 4 003 C Trả trước 05/09/2010 10/09/2010 0 5 500,000 5 004 B Trả sau 29/09/2010 17/10/2010 2 4 2,240,000 6 005 A Trả sau 17/09/2010 27/09/2010 1 3 1,600,000 7 006 C Trả sau 22/09/2010 01/10/2010 1 2 700,000 8 007 C Trả trước 09/10/2010 09/10/2010 0 0 0 9 008 B Trả sau 18/10/2010 24/10/2010 0 6 960,000 10 009 B Trả trước 19/10/2010 25/10/2010 0 6 960,000 11 010 A Trả trước 12/10/2010 14/10/2010 0 2 400,000 GV. Thiều Quang Trung 15
  16. Nhóm hàm thống kê • Tính tổng tiền trả cho các khách thuê phòng loại B = SUMIF(B2:B11,"B",H2:H11) • Tính tổng tiền trả cho các khách thuê phòng loại B và kiểu trả sau: =SUMIFS(H2:H11,B2:B11,"B",C2:C11,"Trả sau") • Tính tổng tiền trả cho các khách thuê phòng loại B và kiểu trả sau, có ngày đến trong tháng 9: =SUMIFS(H2:H11,B2:B11,"B",C2:C11,"Trả sau",D2:D11,">=2010/09/01",D2:D11,"<=2010/09/30") GV. Thiều Quang Trung 16
  17. So sánh hàm SUMIF() và SUMIFS() SUMIF() SUMIFS() Tính tổng chỉ với một điều Tính tổng với 1 hoặc nhiều kiện điều kiện Các ô tính tổng (vùng tổng) Các ô tính tổng (vùng tổng) là đối số cuối cùng trong là đối số đầu tiên của hàm hàm SUMIF() và là tùy chọn SUMIFS(), và là bắt buộc (không bắt buộc) phải có GV. Thiều Quang Trung 17
  18. Các khái niệm công thức mảng • Công thức mảng (Array Formulas) là công thức có thể thực hiện nhiều phép tính đối với một hoặc nhiều mục trong mảng • Mảng là một hàng hoặc một cột các ô chứa giá trị, hoặc kết hợp nhiều hàng và nhiều cột các ô chứa giá trị • Khi nhập công thức mảng, cần nhấn tổ hợp phím Ctrl+Shift+Enter để hoàn tất công thức => Khi đó công thức sẽ được bao bọc bởi hai dấu ngoặc {} GV. Thiều Quang Trung 18
  19. Các khái niệm • Ví dụ: Công thức mảng {=SUM(B2:D2*B3:D3)} tương đương với công thức: B2*B3 + C2*C3 + D2*D3 GV. Thiều Quang Trung 19
  20. Các khái niệm • Công thức mảng có thể trả về nhiều kết quả hoặc một kết quả duy nhất => Công thức mảng bao gồm nhiều ô được gọi là công thức đa ô và công thức mảng trong một ô duy nhất được gọi là công thức đơn ô GV. Thiều Quang Trung 20
  21. Các khái niệm • Công thức mảng trả về kết quả nhiều ô GV. Thiều Quang Trung 21
  22. Các khái niệm • Công thức mảng trả về kết quả một ô: GV. Thiều Quang Trung 22
  23. Các khái niệm • So sánh với hàm SUMPRODUCT: GV. Thiều Quang Trung 23
  24. Ưu điểm của công thức mảng • Nhất quán => giúp đảm bảo độ chính xác GV. Thiều Quang Trung 24
  25. Ưu điểm của công thức mảng • An toàn => không thể xóa ô của công thức mảng đa ô GV. Thiều Quang Trung 25
  26. Ưu điểm của công thức mảng • Kích cở tập tin nhỏ hơn => loại trừ những công thức trung gian GV. Thiều Quang Trung 26
  27. Hằng số mảng • Hằng số mảng là một thành phần của công thức mảng, được nhập bằng cách nhập danh sách các mục, sau đó đặt danh sách này trong một cặp dấu ngoặc nhọn ({ }), ví dụ: ={1,2,3,4,5}. Sau đó nhấn Ctrl+Shift+Enter để hoàn tất công thức GV. Thiều Quang Trung 27
  28. Hằng số mảng • Nếu phân cách các mục bằng dấu phẩy (,) => sẽ tạo ra mảng ngang (hàng) GV. Thiều Quang Trung 28
  29. Hằng số mảng • Nếu phân cách các mục bằng dấu chấm phẩy (;) => sẽ tạo ra mảng dọc (cột) GV. Thiều Quang Trung 29
  30. Hằng số mảng • Để tạo mảng hai chiều => phân cách các mục trong mỗi hàng bằng dấu phẩy và phân cách các hàng bằng dấu chấm phẩy GV. Thiều Quang Trung 30
  31. Hằng số mảng • Hàm TRANSPOSE để chuyển đổi hàng thành cột và ngược lại GV. Thiều Quang Trung 31
  32. Hằng số mảng • Tính bình phương mảng GV. Thiều Quang Trung 32
  33. Ứng dụng công thức mảng • Tìm doanh số của 3 tháng thấp nhất • Công thức mảng {=SUM(SMALL(A2:K2,{1,2,3}))} tương đương với = SMALL(A2:K2,1) + SMALL(A2:K2,2) + SMALL(A2:K2,3) = 10 + 15 + 20 GV. Thiều Quang Trung 33
  34. Ứng dụng công thức mảng • Loại trừ lỗi #DIV/0! GV. Thiều Quang Trung 34
  35. Ứng dụng công thức mảng • Tổng hợp theo nhiều điều kiện của mảng GV. Thiều Quang Trung 35
  36. Ứng dụng công thức mảng • Thay cho hàm đếm có nhiều điều kiện COUNTIFS GV. Thiều Quang Trung 36
  37. Ứng dụng công thức mảng • Thay cho hàm SUMPRODUCT GV. Thiều Quang Trung 37
  38. Ứng dụng công thức mảng • Đếm số ô rỗng trong bảng tính • Dùng công thức mảng: {=SUM(IF(ISBLANK(range), 1, 0))} với range là vùng dữ liệu cần kiểm tra. GV. Thiều Quang Trung 38
  39. Ứng dụng công thức mảng • Đếm số ô chứa những giá trị không phải là kiểu số • Dùng công thức mảng: {=SUM(IF(ISNUMBER(range), 0, 1))} với range là vùng dữ liệu cần kiểm tra. GV. Thiều Quang Trung 39
  40. Ứng dụng công thức mảng • Đếm số ô bị lỗi • Dùng công thức mảng: {=SUM(IF(ISERROR(range), 1, 0))} với range là vùng dữ liệu cần kiểm tra. GV. Thiều Quang Trung 40
  41. GV. Thiều Quang Trung 41
  42. Câu 1: Cho bảng số liệu sau: A B C D 30 Họ và tên Chức vụ Thâm niên Lương 31 Nguyễn Văn An TP 8 100000 32 Nguyễn Văn Bê TP 5 50000 33 Trần Kim Chi PP 4 55000 34 Lê Thùy Dung KT 6 45000 35 Lương Thế Vinh NV 2 30000 Công thức SUMIF(D31:D35, >=50000, C31:C35) cho kết quả là: a. 17 b. 25 c. 13 d. Báo lỗi vì công thức sai GV. Thiều Quang Trung 42
  43. Câu 2: Cho bảng số liệu sau: A B C D 30 Họ và tên Chức vụ Thâm niên Lương 31 Nguyễn Văn An TP 8 100000 32 Nguyễn Văn Bê TP 5 50000 33 Trần Kim Chi PP 4 55000 34 Lê Thùy Dung KT 6 45000 35 Lương Thế Vinh NV 2 30000 Công thức COUNTIF(A31:C35, ">=50000") cho kết quả là: a. 17 b. 13 c. 0 d. Báo lỗi vì công thức sai GV. Thiều Quang Trung 43
  44. Câu 3: Cho bảng số liệu sau: A B C D 30 Họ và tên Chức vụ Thâm niên Lương 31 Nguyễn Văn An TP 8 100000 32 Nguyễn Văn Bê TP 5 50000 33 Trần Kim Chi PP 4 55000 34 Lê Thùy Dung KT 6 45000 35 Lương Thế Vinh NV 2 30000 Công thức SUMIF(D31:D35, ">=50000", D31:D35) cho kết quả là: a. 205000 b. 155000 c. 0 d. báo lỗi vì công thức sai GV. Thiều Quang Trung 44
  45. Câu 4: Cho bảng số liệu sau: A B C D 30 Họ và tên Chức vụ Thâm niên Lương 31 Nguyễn Văn An TP 8 100000 32 Nguyễn Văn Bê TP 5 50000 33 Trần Kim Chi PP 4 55000 34 Lê Thùy Dung KT 6 45000 35 Lương Thế Vinh NV 2 30000 Công thức SUMIFS(D31:D35,C31:C35,">4", B31:B35,”>TP") cho kết quả là: a. 205000 b. 155000 c. 150000 d. 0 GV. Thiều Quang Trung 45