›
›

Bài giảng Tổng quan về Microsoft Excel - Chương 2: Hàm và cơ sở dữ liệu trong Excel

pptx 62 trang ngocly 1820
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Tổng quan về Microsoft Excel - Chương 2: Hàm và cơ sở dữ liệu 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:

  • pptxbai_giang_tong_quan_ve_microsoft_excel_chuong_2_ham_va_co_so.pptx

Nội dung text: Bài giảng Tổng quan về Microsoft Excel - Chương 2: Hàm và cơ sở dữ liệu trong Excel

  1. Chương 2. HÀM VÀ CƠ SỞ DỮ LIỆU TRONG EXCEL 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 1
  2. NỘI DUNG • Các toán tử trong Excel • Các hàm trong Excel • Cơ sở dữ liệu trong Excel • Đồ thị • Bảng tổng hợp đa chiều 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 2
  3. 1. CÁC TOÁN TỬ TRONG EXCEL 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 3
  4. 2. HÀM • Hàm luận lý • Hàm số học • Hàm thống kê • Hàm điều kiện • Hàm chuỗi • Hàm thời gian • Hàm dò tìm 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 4
  5. 2.1. HÀM LÝ LUẬN • 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): phủ định 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 5
  6. 2.2. 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 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 6
  7. 2.2. 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 : PI() Ví dụ: = PI()*2^2 kết quả 12.566 Hàm mũ: POWER(x , y) Trả về x mũ y Ví dụ: =power(4 , 2) kết quả 16 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 7
  8. 2.2. 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 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 8
  9. 2.3. 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ố 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 9
  10. 2.3. 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. 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 10
  11. 2.3. 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 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 11
  12. 2.3. HÀM THỐNG KÊ COUNTA(value1; value2; .) Đếm số lượng các ô chứa dữ liệu trong danh sách 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 12
  13. 2.3. HÀM THỐNG KÊ • Hàm tính tích các số trong danh sách: PRODUCT (danh sách các số hoặc vùng) 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 13
  14. 2.3. 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: Order = 0 (hoặc không ghi): Số lớn được xếp hạng trước Rrder khác 0 : Số nhỏ được xếp hạng trước 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 14
  15. 2.3. HÀM THỐNG KÊ Khi thứ thự xếp Khi thứ thự xếp bằng 1 bằng 0 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 15
  16. 2.3. 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. 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 16
  17. 2.4. 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 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 17
  18. 2.4. 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 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 18
  19. 2.4. 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 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 19
  20. 2.5. 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à” 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 20
  21. 2.5. 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 (“Excel”) kq: 5 Hàm đổi chuỗi thường: LOWER (“chuỗi”) Ví dụ: =LOWER(“KINH TẾ HỌC”) kq: “kinh tế học” 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 21
  22. 2.5. HÀM CHUỖI Hàm đổi chuỗi hoa: UPPER (“chuỗi”) Ví dụ: =UPPER(“kinh tế học”) kq: “KINH TẾ HỌC” Hàm đổi các ký tự đầu của “word” thành hoa PROPER (“chuỗi”) Ví dụ: =PROPER(“kinh tế học”) kq: “Kinh Tế Học” 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 22
  23. 2.5. HÀM CHUỖI Hàm xoá 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 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 23
  24. 2.6. 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 thúc” 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 24
  25. 2.7. 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) ◼ 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. 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 25
  26. 2.7. 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 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 26
  27. 2.7. HÀM DÒ TÌM 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 27
  28. 2.7. 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 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 28
  29. 3. CƠ SỞ DỮ LIỆU CỦA EXCEL • Khái niệm • Sắp xếp • Tìm kiếm • Subtotal 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 29
  30. 3.1. KHÁI NIỆM • CSDL gồm các trường (field) và bản ghi (record). • Trường là một cột CSDL, mỗi trường biểu thị một thuộc tính của đối tượng và có kiểu dữ liệu nhất định. • Bản ghi là một hàng dữ liệu. • Dòng đầu của miền CSDL chứa các tên trường, các dòng tiếp sau là các bản ghi 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 30
  31. 3.2. SẮP XẾP • Khi xếp thứ tự 1 danh sách (CSDL), phải chọn tất cả các cột để tránh sự mất chính xác dữ liệu. • DS không có tên trường thì tên cột sẽ thay thế. • Trường quy định cách xếp thứ tự gọi là khoá. • Cách làm: – B1: Chọn miền. – B2: Chọn Menu Data sau đó chọn biểu tượng Sort 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 31
  32. 3.2. SẮP XẾP Thêm khoá Sắp xếp tăng dần Dòng đầu là tên mới (hoặc giảm dần) trường (ko sắp xếp) Chọn khoá thứ nhất [Chọn khoá thứ hai] [Chọn khoá thứ ba] 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 32
  33. 3.3. TÌM KIẾM • Mục đích: Lấy ra những bản ghi (thông tin) thoả mãn điều kiện nhất định. • Các bước thực hiện: – B1: Chọn miền – B2: Vào Menu Data → chọn biểu tượng Filter 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 33
  34. AUTOFILTER • Chọn miền CSDL gồm cả dòng tên trường • Menu Data/Filter/AutoFilter, ô tên trường có đầu mũi tên thả xuống của hộp danh sách • Kích chuột vào đó, có danh sách thả xuống: – All: để hiện lại mọi bản ghi – Top 10 : các giá trị lớn nhất – Custom : tự định điều kiện lọc – Các giá trị của cột 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 34
  35. AUTOFILTER • Chọn biểu tượng ở góc phải bên dưới của ô tiêu đề • → Number/Text Filters → Custom Filter để thiết lập điều kiện lọc: 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 35
  36. CÁC HÀM LIÊN QUAN ĐẾN CSDL DSUM(Database, Field, Criteria) DMAX(Database, Field, Criteria) DMIN(Database, Field, Criteria) DCOUNT(Database, Field, Criteria) DCOUNTA(Database, Field, Criteria) » Gần giống hàm DCOUNT DAVERAGE(Database, Field, Criteria) 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 36
  37. 3.4. Tạo các dòng tổng (Subtotal) • Subtotal dùng để tổng hợp dữ liệu theo nhóm trong danh sách Vùng dữ liệu ban đầu đã được sắp xếp theo thứ tự Phòng ban. 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 37
  38. 3.4. Tạo các dòng tổng (Subtotal) Vào Menu Data chọn Subtotal 2 (Có thể click các nút phân cấp để thu gọn hoặc bung ra từng cấp subtotal) 1 3 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 38
  39. 4. ĐỒ THỊ • Mục tiêu • Chọn đúng kiểu đồ thị • Tạo đồ thị • Thay đổi đồ thị 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 39
  40. 4.1. MỤC TIÊU • Đồ thị giúp trình bày quan điểm của bạn một cách nhanh chóng. Với đồ thị bạn chuyển dữ liệu trong bảng tính thành hình ảnh, và cho phép bạn so sánh và thấy hướng tăng trưởng chỉ trong chớp mắt. 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 40
  41. 4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊ • Mỗi đồ thị đều chứa đựng ý nghĩa bên trong của nó – đó là thông điệp mà người tạo đồ thị muốn gửi đến người xem. Vì thế đặc điểm đánh giá chính yếu là thông điệp phải truyền đi hiệu quả nhất. • Các kiểu đồ thị khác nhau có thể truyền đi các thông điệp khác nhau của cùng 1 đối tượng dữ liệu. Tiền xe Tiền xe Tiền xe 70000 70000 60000 60000 50000 50000 Đà Lạt 40000 40000 Tiền xe Tiền xe Nha Trang 30000 30000 Vũng tàu 20000 20000 Phú Quốc 10000 10000 Huế 0 0 Đà Lạt Nha V ũng tàu P hú Quốc Huế 0 1 2 3 4 5 6 T r ang 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 41
  42. 4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊ • Đồ thị cột (column) • Đồ thị cột là lựa chọn mặc định của wizard. Đồ thị cột thích hợp để so sánh trực tiếp các giá trị với nhau. Diem chuan theo khu vuc 20 15 ĐIỂM CHUẨN K.VỰC1 10 điểm ĐIỂM CHUẨN 5 K.VỰC2 0 MÁY TÍNH ĐIỆN TỬ XÂY DỰNG HÓA ngành 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 42
  43. 4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊ • Pie chart so sánh từng phần tử của 1 tổng thể • Đồ thị bánh để so sánh các phần tử trong 1 tổng thể với nhau để làm rỏ mức độ đóng góp của từng phần tử. Đây là đồ thị lý tưởng để biểu diễn cơ cấu, tỷ trọng của 1 đối tượng Thượng hạng Thượng hạng, Thượng hạng, 80,000 đ 100,000 đ TEA COFFEE CACAO Thượng hạng, 120,000 đ 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 43
  44. 4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊ • Line so sánh các giá trị qua thời gian • Đồ thị đường (Line) – phù hợp biểu diễn xu hướng biến động theo thời gian như sản lượng, doanh thu, và lợi nhuận. Sử dụng đồ thị Line khi trục hoành là thời gian. 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 44
  45. 4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊ • XY Scatter – mối quan hệ 2 đại lượng • XY Scatter – phù hợp để thể hiện mối quan hệ giữa các giá trị dữ liệu khoa học hay thống kê. • Thể hiện 2 thang đo trong 1 đồ thị qua 2 trục X và Y 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 45
  46. 4.3. TẠO ĐỒ THỊ • B1: Chọn miền dữ liệu vẽ đồ thị, chú ý chọn cả 1 tiêu đề hàng và 1 tiêu đề cột đối với các đồ thị kiểu Column, Line và Pie. • B2: Vào Menu Insert chọn loại biểu đồ cần thực hiện • B3: Sử dụng Chart Layout, Select Data hoặc nhấp vào các vùng muốn điều chỉnh để định dạng các kiểu thể hiện như: 1. Định kiểu đồ thị 2. Định dữ liệu 3. Các lựa chọn: tiêu đề, các trục, chú giải 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 46
  47. 4.4. THAY ĐỔI ĐỒ THỊ Khi đồ thị đã được tạo, có thể: 1. Chuyển đồ thị tới vị trí mới bằng phương thức Drag & Drop. 2. Thay đổi kích thước đồ thị 3. Thay đổi các thuộc tính của đồ thị (tiêu đề, chú giải, ) 4. Thay đổi các thuộc tính của các thành phần đồ thị (font chữ, tỷ lệ các trục, màu sắc nền, ) 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 47
  48. 5. BẢNG TỔNG HỢP ĐA CHIỀU (PIVOT TABLE) • Khái niệm • Tạo lập pivot table • Tiêu chí phân tích • Các phương pháp thống kê • Công cụ Group 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 48
  49. 5.1. KHÁI NIỆM • Pivot Table là 1 công cụ của Excel dùng tổng hợp và phân tích dữ liệu với nhiều góc độ và nhiều cấp khác nhau. 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 49
  50. BÀI TẬP ỨNG DỤNG Đại lý Tỉnh Mặt hàng Năm Doanh thu B HCM Điện tử 2001 22,449,000 B HCM Điện gia dụng 2002 26,739,000 A HCM Điện cơ 2002 82,091,000 A HCM Điện tử 2003 39,071,000 D An Giang Điện gia dụng 2005 51,881,000 C An Giang Điện gia dụng 2005 70,568,000 C An Giang Điện cơ 2005 59,568,000 F Hà Nội Điện cơ 2004 24,546,000 F Hà Nội Điện gia dụng 2005 41,352,000 F Hà Nội Điện gia dụng 2005 41,373,000 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 50
  51. BÀI TẬP ỨNG DỤNG • Tổng hợp: – Doanh thu từng tỉnh – Doanh thu từng nhóm hàng – Doanh thu từng đại lý – Doanh thu từng năm • Phân tích: – Doanh thu từng tỉnh theo từng mặt hàng – Doanh thu từng đại lý theo từng mặt hàng – Doanh thu từng năm của từng tỉnh – Doanh thu từng đại lý theo năm – Doanh thu từng năm theo nhóm hàng – Chi tiết Doanh thu từng nhóm hàng cho từng đại lý, nhóm theo tỉnh. 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 51
  52. 5.2. TẠO LẬP PIVOT TABLE ❑ Bước 1: Chỉ định nguồn dữ liệu - Bôi đen cơ sở dữ liệu cần tạo • - Menu Insert → chọn biểu tượng PivotTable 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 52
  53. 5.2. TẠO LẬP PIVOT TABLE Bước 2: Chỉ định dữ liệu Bước 3: Chỉ định nơi đặt PivotTable 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 53
  54. 5.2. TẠO LẬP PIVOT TABLE • Bước 4: Kéo và thả • Trường tổng hợp cấp cao nhất vào Report Filter (Năm) • 1 hoặc 2 Trường tổng hợp cấp thấp hơn vào Row (Tỉnh, Đại lý) • 1 hoặc 2 Trường phân tích vào Column (Mặt hàng) • Trường dữ liệu phân tích vào Values (Doanh thu) 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 54
  55. 5.3. TIÊU CHÍ PHÂN TÍCH – Tiêu chí phân tích là bất kỳ tiêu chí nào có thể dùng để phân loại dữ liệu – Nếu gõ sai chính tả, dư dấu space, thiếu dấu sắc, dấu huyền; Pivot cũng coi như 1 loại mới và tách riêng ra. – Phân loại dữ liệu cần chính xác, không trùng lắp, không mơ hồ. 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 55
  56. 5.4. CÁC PHƯƠNG PHÁP THỐNG KÊ • Thống kê – Số học sinh mỗi lớp dự thi – Điểm cao nhất của mỗi lớp – Điểm thấp nhất của mỗi lớp – Điểm trung bình mỗi lớp 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 56
  57. 5.4. CÁC PHƯƠNG PHÁP THỐNG KÊ • Hãy tạo 1 Pivot table với cấu trúc như sau: Kéo thả Lớp vào Rows, Tên HS và điểm vào Data. • Vì trường tên HS là text, nên Excel tự gán công thức là count: 2 lớp có 6 HS và 1 lớp có5 HS. Trường Điểm là số(number) nên Excel tự gán công thức Sum. 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 57
  58. 5.4. CÁC PHƯƠNG PHÁP THỐNG KÊ • Nhấn chuột phải vào trường “Sum of Điểm”, chọn field setting. Chọn lại là Max thay vìSum, sửa tên field thành Điểm lớn nhất 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 58
  59. 5.4. CÁC PHƯƠNG PHÁP THỐNG KÊ • Kéo thả Trường điểm vào 2 lần nữa, một lần là Min và 1 lần là Average ta sẽ có: • Nếu thay vì trường “Lớp”, ta kéo trường Môn vào Row, ta sẽ có: 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 59
  60. 5.5. CÔNG CỤ GROUP • Group trường loại số: Giả sử với dữ liệu Doanh thu như trên ta có thể Group trường Năm thành nhóm 2 năm, hoặc 3 năm như sau: • Bấm chuột phải vào trường Năm trên Pivot Table, Vào Menu Data → Group: 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 60
  61. 5.5. CÔNG CỤ GROUP Date USA Japan UK VN Bài tập ứng dụng: Jan 2, 2013 65 67 42 55 • Group trường loại Jan 3, 2013 23 57 64 67 Jan 4, 2013 67 43 24 57 ngày tháng Mar 5, 2013 64 56 64 31 Mar 6, 2013 24 34 67 42 May 7, 2013 76 76 57 64 Jun 5, 2013 42 42 43 24 Jul 6, 2013 65 67 57 76 Jul 7, 2013 24 64 86 42 Aug 11, 2013 56 76 67 42 Aug 12, 2013 34 42 67 64 Oct 13, 2013 24 64 56 24 Oct 14, 2013 77 22 34 76 Dec 15, 2013 24 55 43 42 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 61
  62. 5.6. Các ứng dụng khác từ Pivot Table • Pivot Table còn được sử dụng trong việc thực hiện các thao tác và ứng dụng cao cấp khác như: – Lấy dữ liệu từ nhiều nguồn – Ứng dụng PivotTable trong báo cáo tài chính 14/06/2021 MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel 62