Excels important things when you first use.pptx

excels

1. ĐẠI HỌC BÁCH KHOA HÀ NỘI NHÓM 2 MỘT SỐ NHÓM HÀM QUAN TRỌNG TRONG EXCEL VÀ ỨNG DỤNG

2. ĐẠI HỌC BÁCH KHOA HÀ NỘI DANH SÁCH SINH VIÊN NHÓM 2 1. VŨ VĂN HẬU 20202849 2. NGUYỄN THỊ TÚ ANH 20202839 3. NGUYỄN THỊ HỒNG 20202851 4. VŨ TIẾN THÀNH 20202878 5. BÙI MINH PHƯỢNG 20202863

3. VLOOKUP, HLOOKUP, MATCH, INDEX, CHOOSE, OFFSET MIN, MAX, MinIFS, MaxIFS AVERAGE AVERAGEIF COUNT COUNTIF SUM, SUMIF, PRODUCT, SUMPRODUCT, ROUND, SUBTOTAL NỘI DUNG CHÍNH Nhóm hàm Tìm kiếm Nhóm hàm thống kê Nhóm hàm tính toán

4. PHẦN I. NHÓM HÀM TÌM KIẾM

5. I. NHÓM HÀM TÌM KIẾM Nhóm hàm tìm kiếm và tham chiếu trong Excel là nhóm hàm được ứng dụng trong nhiều trường hợp cần tìm kiếm hoặc tham chiếu thông tin. Ở nhóm hàm này, ta có thể sử dụng một hàm đơn lẻ hoặc kết hợp các hàm với nhau để cho ra kết quả tìm kiếm tối ưu nhất và tiết kiệm thời gian (ví dụ như hàm INDEX và hàm MATCH). 1. Tổng quan về nhóm hàm 2. Danh sách một số hàm phổ biến VLOOKUP HLOOKUP MATCH INDEX CHOOSE OFFSET

6. I. NHÓM HÀM TÌM KIẾM 3. Tổng quan về hàm VLOOKUP Công dụng: Ta sử dụng hàm này khi cần tìm thông tin trong một bảng hoặc một dải ô theo hàng. Hàm VLOOKUP có thể tìm kiếm hàm dữ liệu trong 1 sheet, các sheet khác nhau hoặc các file, chiều tìm kiếm từ trái sang phải - Hàm thường được dùng để đ iền điểm sổ, xét học bổng, điền thông tin tương ứng với thông tin cho trước… Cú pháp: VLOOKUP (Lookup_value, Table_array, Col_index_ num, [Range_lookup]) Lookup_value: là tham số bắt buộc, là giá trị mà ta muốn tìm kiếm thông tin liên quan đến nó Table_array: là tham số bắt buộc, là bảng giới hạn để dò tìm ; Col_index_num: là tham số bắt buộc, là thứ tự của cột chứa giá trị cần tìm kiếm. Range_lookup: Là giá trị Logic quyết định so sánh chính xác hay tương đổi với bảng giới hạn. + Nếu Range_lookup = 1 (TRUE): So sánh tương đối + Nếu Range_lookup = 0 (FALSE): So sánh chính xác + Nếu bỏ qua đối này thì Excel hiểu là Range_lookup = 1.

7. I. NHÓM HÀM TÌM KIẾM 3. Tổng quan về hàm VLOOKUP Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ Ví dụ này yêu cầu bạn tính Thuế NK với công thức Thuế NK = Đơn giá * Thuế NK theo đơn giá. Mà Thuế NK theo đơn giá lại phụ thuộc vào đối tượng. Nên trong trường hợp này bạn dùng VLOOKUP sẽ giải quyết được vấn đề

8. I. NHÓM HÀM TÌM KIẾM 3. Tổng quan về hàm VLOOKUP Một số lỗi hay gặp khi sử dụng hàm VLOOKUP 3. Lỗi #REF: số cột nằm ngoài phạm vi bảng tham chiếu 4. Lỗi #VALUE!: xảy ra khi số cột nhỏ hơn hoặc bằng 0 1. Hàm VLOOKUP tìm kiếm theo bảng giá trị dọc nên khi sử dụng người dùng quên cố định vùng tìm kiếm, dẫn đến kết quả bị nhảy lung tung. Khắc phục: sử dụng địa chỉ tuyệt đối cho vùng tìm kiếm. VD: $A$4:$D$6 2. Lỗi #N/A: nếu phạm vi tìm kiếm range lookup được chọn là 0 (so sánh chính xác ) thì Excel trả về lỗi #N/A nếu không thể tìm được giá trị đó . Khắc phục: sử dụng tìm kiếm tương đối hoặc kiểm tra, chỉnh sửa lại các giá trị Khắc phục: kiểm tra lại công thức và sửa lại

9. I. NHÓM HÀM TÌM KIẾM 4. Tổng quan về hàm HLOOKUP Công dụng: Giúp người dùng tìm kiếm các giá trị dựa trên điều kiện cho trước theo dòng . Hàm HLOOKUP có thể tìm kiếm hàm dữ liệu trong 1 sheet, các sheet khác nhau hoặc các file, chiều tìm kiếm từ trái sang phải - Hàm thường được dùng để đ iền điểm sổ, xét học bổng, điền thông tin tương ứng với thông tin cho trước… Cú pháp: HLOOKUP (Lookup_value, Table_array, Col_index_ num, [Range_lookup]) Lookup_value: là tham số bắt buộc, là giá trị mà ta muốn tìm kiếm thông tin liên quan đến nó Table_array: là tham số bắt buộc, là bảng giới hạn để dò tìm ; Col_index_num: là tham số bắt buộc, là thứ tự của cột chứa giá trị cần tìm kiếm. Range_lookup: Là giá trị Logic quyết định so sánh chính xác hay tương đổi với bảng giới hạn. + Nếu Range_lookup = 1 (TRUE): So sánh tương đối + Nếu Range_lookup = 0 (FALSE): So sánh chính xác + Nếu bỏ qua đối này thì Excel hiểu là Range_lookup = 1.

10. I. NHÓM HÀM TÌM KIẾM 4. Tổng quan về hàm HLOOKUP Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ Ví dụ này yêu cầu bạn điền xếp loại học lực của học sinh dựa trên điểm trung bình theo bảng có sẵn. Trường hợp này sử dụng hàm HLOOKUP sẽ giúp bạn giải quyết được vấn đề.

11. I. NHÓM HÀM TÌM KIẾM 4. Tổng quan về hàm HLOOKUP Một số lỗi hay gặp khi sử dụng hàm HLOOKUP 3. Lỗi #REF: số cột nằm ngoài phạm vi bảng tham chiếu 4. Lỗi #VALUE!: xảy ra khi số cột nhỏ hơn hoặc bằng 0 1. Hàm H LOOKUP tìm kiếm theo bảng giá trị ngang nên khi sử dụng người dùng quên cố định vùng tìm kiếm, dẫn đến kết quả bị nhảy lung tung. Khắc phục: sử dụng địa chỉ tuyệt đối cho vùng tìm kiếm. VD: $A$4:$D$6 2. Lỗi #N/A: nếu phạm vi tìm kiếm range lookup được chọn là 0 ( tìm kiếm chính xác ) thì Excel trả về lỗi #N/A nếu không thể tìm được giá trị đó . Khắc phục: sử dụng tìm kiếm tương đối hoặc kiểm tra, chỉnh sửa lại các giá trị Khắc phục: kiểm tra lại công thức và sửa lại

12. I. NHÓM HÀM TÌM KIẾM 5. Phân biệt VLOOKUP & HLOOKUP C ả hai hàm HLOOKUP VÀ VLOOKUP dùng để tìm kiếm một giá trị. Nhưng chúng khác nhau trong cách hoạt động. Chúng ta dễ dàng nhận ra tên của hai hàm khác nhau ở chữ cái đầu – “H” là ngang (horizontal) và “V” là dọc (vertical) Do đó, bạn sử dụng hàm VLOOKUP để tìm giá trị theo cột ở phía bên trái của dữ liệu bạn muốn tìm. Hàm HLOOKUP dùng để tìm giá trị theo hàng ngang. Nó tìm giá trị ở hàng đầu tiên của bảng và trả lại giá trị ở hàng được chỉ định ở trong cùng một cột.

13. I. NHÓM HÀM TÌM KIẾM 6. Tổng quan về hàm MATCH Công dụng: Ta sử dụng hàm này khi cần tìm một giá trị được chỉ định trong phạm vi của ô, sau đó trả về vị trí tương đối của giá trị đó trong phạm vi này. Hàm có thể được dùng để đối chiếu các thông tin cho trước, ví dụ như đối chiếu thông tin mã số sinh viên với tên sinh viên. Cú pháp: = MATCH(Lookup_value, Lookup_array, Match_type) Lookup_value: Là tham số bắt buộc, giá trị mà ta muốn tìm vị trí. (có thể là số, văn bản, giá trị logic hoặc một tham chiếu ô đến một số, văn bản hay giá trị logic) Lookup_array: là tham số bắt buộc và là mảng hay phạm vi ô được tìm kiếm, phạm vị này phải nằm trên cùng 1 cột hoặc 1 hàng với lookup_value. Match_type: Là kiểu tìm kiếm và không nhất thiết phải có. Mặc định là 1

14. I. NHÓM HÀM TÌM KIẾM 6. Tổng quan về hàm MATCH 3 kiểu Match_type: + Match_type = 1: Tìm kiếm giá trị lớn nhất mà giá trị đó <= lookup_value. Điều kiện là lookup-array phải sắp xếp theo thứ tự tăng dần. + Match_type = 0: Tìm giá trị đầu tiên bằng chính xác với lookup_value. + Match_type = - 1: Tìm kiếm giá trị nhỏ nhất mà giá trị đó >= lookup_value. Gía trị trong lookup_array sắp xếp theo thứ tự giảm dần. VÍ DỤ Tìm vị trí tương đối của tên người bắt đầu bằng “Car” Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu).

15. I. NHÓM HÀM TÌM KIẾM 6. Tổng quan về hàm MATCH Một số lỗi hay gặp khi sử dụng hàm MATCH 1. Khi sử dụng hàm MATCH người dùng thường hay quên cố định vùng tìm kiếm, dẫn đến kết quả bị nhảy lung tung. Khắc phục: sử dụng địa chỉ tuyệt đối cho vùng tìm kiếm. VD: $A$4:$D$6 2. Lỗi #N/A: Lỗi N/A xuất hiện khi hàm không thể tìm kiếm kết quả phù hợp Khắc phục: kiểm tra lại Lookup_value hoặc các giá trị của bảng và sửa lại.

16. I. NHÓM HÀM TÌM KIẾM 7. Tổng quan về hàm INDEX Công dụng: Hàm này trả về giá trị của phần tử trong một bảng hoặc một mảng, được chọn bởi chỉ mục số hàng và cột. Có hai cách để sử dụng hàm INDEX tương ứng với hai cú pháp ở cột bên trái. Hàm này có thể được ứng dụng khi cần tìm kiếm thông tin về giá của một sản phẩm trong một bảng giá hoặc rộng hơn là tìm kiếm các thông tin của một đối tượng cụ thể trong phạm vi cho trước Cú pháp của hàm được chia làm 2 loại: 1. Cú pháp INDEX dạng mảng 2. Cú pháp INDEX dạng tham chiếu

17. I. NHÓM HÀM TÌM KIẾM 7. Tổng quan về hàm INDEX Cú pháp dạng mảng: INDEX (Array, Row_num, [Column_num]) - Dùng trong trường hợp nếu đối số thứ nhất của hàm là một hằng số mảng Array: là vùng ô hoặc một hằng số mảng nào đó và là tham số bắt buộc. Nếu mảng chỉ chứa một hàng hoặc cột, tham đối số giá row_num hoặc column_num tương ứng là tùy chọn. Nếu mảng có nhiều hàng và nhiều cột và chỉ có tác dụng row_num hoặc column_num thì công thức INDEX trả về mảng có toàn bộ hàng hoặc cột trong mảng. Row_num: là tham số bắt buộc và là hàng trong mảng mà ta muốn lấy giá trị. Column_num: là tham số không bắt buộc và là hàng trong mảng mà ta muốn lấy giá trị.

18. I. NHÓM HÀM TÌM KIẾM 7. Tổng quan về hàm INDEX Cú pháp dạng tham chiếu: INDEX (reference,row_num,[column_num],[area_num]) (dạng này sẽ trả về tham chiếu của ô nằm ở giao của một hàng và một cột không liền kề.) Reference: vùng tham chiếu bắt buộc. Nếu muốn nhập một phạm vi không liền kề cho tham số này thì cần đặt tham số trong dấu ngoặc đơn. Nếu mỗi vùng trong tham chiếu chỉ chứa một hàng hoặc 1 cột thì đối số row_num hoặc column_num tương ứng là đối số tùy chọn. Row_num: là tham chiếu bắt buộc, là chỉ số hàng từ đó trả về một tham chiếu Column_num: là tham chiếu tùy chọn, chỉ số cột từ đó trả về một tham chiếu Area_num: là tham số không bắt buộc, đây là một phạm vi tham chiếu, mà từ đó trả về giao điểm của row_num và column_num.

19. I. NHÓM HÀM TÌM KIẾM 7. Tổng quan về hàm INDEX Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ = INDEX ((A2: D3, A5: D7), 3, 4, 2)   trả về giá trị của ô D7, tại giao điểm của hàng thứ 3 và cột thứ 4 trong khu vực thứ hai (A5: D7).

20. I. NHÓM HÀM TÌM KIẾM 7. Tổng quan về hàm INDEX Một số lỗi thường gặp 1. Đối với hàm INDEX dạng mảng: Bắt buộc phải có một trong hai đối số row_num hoặc column_num. Thiếu sẽ bị lỗi Khắc phục: kiểm tra và sửa lại công thức. 2. Đối với hàm INDEX dạng tham chiếu: Row_num và column_num phải trỏ đến một ô trong mảng array, nếu không sẽ bị trả về lỗi #REF! Khắc phục: kiểm tra và sửa lại công thức.

21. I. NHÓM HÀM TÌM KIẾM 8. Tổng quan về hàm CHOOSE Công dụng: Hàm này trả về giá trị thứ n trong một chuỗi giá trị các đối số được nhập vào hàm Điền thứ ngày tháng một cách nhanh chóng có thể áp dụng hàm CHOOSE và WEEKDAY Cú pháp: CHOOSE (index_num,value1,[value2],…) Index_num: là tham số bắt buộc, nó xác định vị trí của giá trị được chọn, phải là số từ 1 đến 254 hoặc công thức hay tham chiếu đến ô chứa số từ 1 đến 254. Value1: tham số bắt buộc Value2,… : là tham số tùy chọn, là các đối số từ 1 đến tối đa 254, từ đó hàm chọn ra giá trị tại vị trí được chỉ định.

22. I. NHÓM HÀM TÌM KIẾM 8. Tổng quan về hàm CHOOSE VÍ DỤ 1. Điền giá trị index_num ngoài khoảng [1,254] sẽ bị lỗi Khắc phục: kiểm tra và sửa lại công thức. Một số lỗi thường gặp Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu).

23. I. NHÓM HÀM TÌM KIẾM 9. Tổng quan về hàm OFFSET Công dụng: Hàm này trả về giá trị tham chiếu nằm cách một ô hoặc một phạm vi ô một số hàng hoặc một số cột đã xác định. Tham chiếu được trả về có thể là một ô đơn hoặc một phạm vi ô. Ta có thể chỉ rõ số hàng và số cột được trả về. Hàm này được ứng dụng nhiều trong việc tính toán vùng dữ liệu di động, khi dữ liệu được cập nhật liên tục thì ta không cần phải cập nhật công thức. Trong nhiều trường hợp hàm này có thể kết hợp với hàm AVERAGE, SUM, MATCH.

24. I. NHÓM HÀM TÌM KIẾM 9. Tổng quan về hàm OFFSET Cú pháp: OFFSET (reference,rows, cols,[height],[width]) Reference: là tham số bắt buộc, là ô hoặc vùng tham chiếu mà ta dùng làm gốc để tham chiếu tới giá trị ta muốn hàm trả về, gọi là tham chiếu gốc. Đối số bày phải là một ô hoặc một phạm vi các ô liền kề Rows: là tham số bắt buộc; số hàng từ ô tham chiếu đến gốc ô ta muốn lấy giá trị có thể dóng lên trên hoặc xuống dưới. Nếu tham chiếu gốc ta muốn dùng là một vùng ô thì ô gốc là ô trên cùng bên trái của vùng. Cols: là tham số bắt buộc, là số cột từ ô tham chiếu đến gốc ô ta muốn lấy giá trị có thể dóng sang trái hoặc sang phải. Ô gốc tham chiếu cột trùng với ô gốc tham chiếu hàm. Tham số có thể là số dương (tham chiếu sang phải) hoặc số âm (tham chiếu sang trái) Height: là tham số tùy chọn. Chiếu cao tính bằng số hàng của vùng giá trị hàm trả về, phải là số dương Width: là tham số tùy chọn, là độ rộng, tính bằng số cột của vùng giá trị hàm trả về, phải là số dương.

25. I. NHÓM HÀM TÌM KIẾM 9. Tổng quan về hàm OFFSET Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ

26. I. NHÓM HÀM TÌM KIẾM 9. Tổng quan về hàm OFFSET Một số lỗi thường gặp 1. Lỗi #VALUE!: xảy ra khi reference không phải là một ô hoặc một phạm vi các ô liền kề. Khắc phục: kiểm tra và sửa lại công thức. 2. Lỗi #REF!: xảy ra nếu các đối số rows và cols làm cho vùng tham chiếu trả về vượt phạm vi trang tính Khắc phục: kiểm tra và sửa lại đối số trong công thức.

27. ĐẠI HỌC BÁCH KHOA HÀ NỘI PHẦN II. NHÓM HÀM THỐNG KÊ

28. II. NHÓM HÀM THỐNG KÊ Nhóm hàm thống kê bao gồm các hàm g iúp bạn tính toán dễ dàng các giá trị như: Tính tổng, giá trị lớn nhất, giá trị nhỏ nhất,... Thao tác chuyên nghiệp hơn khi sử dụng Excel. Và các hàm này k ết hợp được với nhiều hàm khác để hỗ trợ công việc. 1. Tổng quan về nhóm hàm MIN MAX MINIFS MAXIFS AVERAGE AVERAGEIF COUNT COUNTIFS 2. Một số hàm thông dụng

29. II. NHÓM HÀM THỐNG KÊ 3. Tổng quan về hàm MIN, MAX Công dụng: Hàm MIN là hàm sử dụng để trả về giá trị nhỏ nhất trong tập giá trị Hàm MAX là hàm sử dụng để trả về giá trị lớn nhất trong tập giá trị Cú pháp: MIN (number1,[number2],…) MAX (number1,[number2],…) number1 là tham số bắt buộc, number2 là tham số tùy chọn, tối đa 255 tham số. Ngoài ra number1, number 2 cũng có thể là ngoài vùng dữ liệu Các giá trị logical, văn bản hoặc ô trống sẽ được bỏ qua nếu nằm trong vùng dữ liệu tham chiếu.

30. II. NHÓM HÀM THỐNG KÊ 3. Tổng quan về hàm MIN, MAX VÍ DỤ Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). Tìm giá trị lớn nhất và nhỏ nhất

31. II. NHÓM HÀM THỐNG KÊ 4. Tổng quan về hàm MINIFS Công dụng: Hàm MINIFS trả về giá trị tối thiểu trong tập giá trị được xác định bởi một loạt các điều kiện hoặc tiêu chí cho trước. Hàm này chỉ có sẵn ở phiên bản Excel 2019 trở lên và Office 365. Cú pháp: MINIFS (min_range, criteria_range1, criteria1, [criteria_range2,criteria2],…) min_range là tham số bắt buộc. Vùng ô muốn tìm giá trị nhỏ nhất criteria_range1 là tham số bắt buộc. Vùng ô điều kiện để đánh giá giá trị vùng min_range criteria1 là tham số bắt buộc. Là tiêu chí dạng số, biểu thức hoặc văn bản giúp chúng ta xác định ô nào được đánh giá là nhỏ nhất criteria_range2,criteria2 là các tham số không bắt buộc.

32. II. NHÓM HÀM THỐNG KÊ 4. Tổng quan về hàm MINIFS Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ Lấy ra số điểm trung bình thấp nhất của bạn nữ trong danh sách học sinh Hàm MINIFS(C2:C12,B2:B12,"Nữ") sẽ trả về kết quả là 6.9

33. II. NHÓM HÀM THỐNG KÊ 4. Tổng quan về hàm MINIFS Một số lỗi thường gặp 1. Lỗi #VALUE!: Tọa độ của dải ô chứa giá trị và dải ô chứa điều kiện phải có cùng kích cỡ, hình dạng nếu không sẽ bị lỗi này Khắc phục: kiểm tra lại dải giá trị và dải điều kiện và sửa lại công thức. 2. Lỗi #NAME : Lỗi này thường xảy ra đ ối với Các phiên bản Excel từ 2013 về trước Khắc phục: Sử dụng phiên bản Office cao hơn (từ 2016 trở lên)

34. II. NHÓM HÀM THỐNG KÊ 5. Tổng quan về hàm MAXIFS Công dụng: Hàm MAXIFS trả về giá trị lớn nhất trong tập giá trị được xác định bởi một loạt các điều kiện hoặc tiêu chí cho trước. Hàm này chỉ có sẵn ở phiên bản Excel 2019 trở lên và Office 365. Cú pháp: MAXIFS (max_range, criteria_range1, criteria1, [criteria_range2,criteria2],…) max_range là tham số bắt buộc. Vùng ô muốn tìm giá trị lớn nhất criteria_range1 là tham số bắt buộc. Vùng ô điều kiện để đánh giá giá trị vùng max_range criteria1 là tham số bắt buộc. Là tiêu chí dạng số, biểu thức hoặc văn bản giúp chúng ta xác định ô nào được đánh giá là nhỏ nhất criteria_range2,criteria2 là các tham số không bắt buộc.

35. II. NHÓM HÀM THỐNG KÊ 5. Tổng quan về hàm MAXIFS Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ Lấy ra số điểm cao nhất nhất của bạn nữ trong danh sách học sinh Hàm MAXIFS(D2:D12,B2:B12,"Nữ") sẽ trả về kết quả là 96

36. II. NHÓM HÀM THỐNG KÊ 5. Tổng quan về hàm MAXIFS Một số lỗi thường gặp 1. Lỗi #VALUE!: Tọa độ của dải ô chứa giá trị và dải ô chứa điều kiện phải có cùng kích cỡ, hình dạng nếu không sẽ bị lỗi này Khắc phục: kiểm tra lại dải giá trị và dải điều kiện và sửa lại công thức. 2. Lỗi #NAME : Lỗi này thường xảy ra đ ối với Các phiên bản Excel từ 2013 về trước Khắc phục: Sử dụng phiên bản Office cao hơn (từ 2016 trở lên)

37. II. NHÓM HÀM THỐNG KÊ 6. Tổng quan về hàm AVERAGE Công dụng: Hàm AVERAGE được sử dụng để tính giá trị trung bình của một tập dữ liệu. Các đối số của hàm có thể là số, tên bảng, vùng dữ liệu hoặc tham chiếu ô. Nếu các đối số được tham chiếu chứa văn bản, giá trị logic hoặc là ô trống hàm sẽ bỏ qua các ô này. Cú pháp: AVERAGE (number1, [number2] ,.. ) number1 là tham số bắt buộc, là tham số hoặc vùng mà bạn muốn tính giá trị trung bình; number 2 ,... là tham số tùy chọn, là tham chiếu ô hoặc phạm vi bổ sung mà bạn muốn tính giá trị trung bình.

38. II. NHÓM HÀM THỐNG KÊ 6. Tổng quan về hàm AVERAGE Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ Tính điểm trung bình của các học sinh biết điểm trung bình bằng trung bình của 3 điểm toán, văn, anh. Hàm AVERAGE(B2,C2,D2) trả về 8

39. II. NHÓM HÀM THỐNG KÊ 7. Tổng quan về hàm AVERAGEIF Công dụng: Hàm này trả về giá trị trung bình cộng của tất cả các ô trong một phạm vi đáp ứng một tiêu chí được chỉ định từ trước. Cú pháp: AVERAGEIF (range, criteria, [average_range]) range là tham số bắt buộc, có thể là một hoặc nhiều ô để tính giá trị trung bình bao gồm số, tên bảng, vùng hoặc tham chiếu có chứa số; criteria là tham số bắt buộc, là tiêu chí để tính giá trị trung bình, thường ở dạng số, biểu thức, tham chiếu ô hoặc văn bản; average_range là tham số không bắt buộc, là vùng ô mà bạn muốn tính giá trị trung bình. Nếu bạn không nhập đối số cho tham số này, tham số bạn nhập vào range sẽ được coi là average_range.

40. II. NHÓM HÀM THỐNG KÊ 7. Tổng quan về hàm AVERAGEIF Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ Tính điểm số trung bình của những sinh viên có điểm số lớn hơn 5. Công thức AVERAGEIF(D2:D6,">5") sẽ trả về giá trị 7.75

41. II. NHÓM HÀM THỐNG KÊ 7. Tổng quan về hàm AVERAGEIF Một số lỗi thường gặp 1. Nếu đối số average_range là vùng ô trống, thì hàm sẽ bỏ qua đối số này. Nếu đối số range là trống hoặc là giá trị văn bản, hàm sẽ báo lỗi #DIV/0 Khắc phục: kiểm tra lại đối số range 2. Nếu đối số criteria trống hàm sẽ tính là 0 và nếu ko có ô nào trong range thỏa mãn tiêu chí thì giá trị trả về sẽ báo lỗi #DIV/0 Khắc phục: kiểm tra lại hàm, giá trị trong range, tiêu chí

42. II. NHÓM HÀM THỐNG KÊ 8. Tổng quan về hàm COUNT Công dụng: Hàm COUNT được sử dụng để đếm số ô có chứa số, và đếm các số được nhập như đối số trong hàm. Cú pháp: COUNT (value1, [value2],..) value1 là tham số bắt buộc, là ô tham chiếu hoặc vùng tham chiếu mà bạn muốn đếm ô; value2 là tham số tùy chọn, các ô tham chiếu hoặc phạm bi bổ sung trong đó mà bạn muốn đếm số ô Ngoài số, hàm sẽ đếm cả các ô chứa ngày tháng và bỏ qua giá trị logic, văn bản, ô trống và ô chứa lỗi.

43. II. NHÓM HÀM THỐNG KÊ 8. Tổng quan về hàm COUNT Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ Đếm số lượng ô có doanh thu trong tháng một và tháng 3 Dùng hàm COUNT(B2:B7,D2:D7) ta được kết quả là 9

44. II. NHÓM HÀM THỐNG KÊ 9. Tổng quan về hàm COUNTIF Công dụng: Hàm được sử dụng để đếm các ô thỏa mãn một điều kiện được chỉ định trước. Ví dụ, đếm xem tên thành phố A xuất hiện bao nhiêu lần trong danh sách dữ liệu Cú pháp: COUNTIF (range, criteria) range là tham số bắt buộc, là vùng ô chứa dữ liệu bạn muốn đếm. Vùng này có thể chứa số vùng, tên bảng, tham chiếu chứa số. Hàm sẽ tự động bỏ qua các giá trị văn bản và ô trống. c riteria là tham số bắt buộc, là tiêu chí như số, biểu thức, tham chiếu ô hoặc chuỗi văn bản giúp xác định ô mà bạn muốn đếm

45. II. NHÓM HÀM THỐNG KÊ 9. Tổng quan về hàm COUNTIF Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ Công thức = COUNTIF (D2: D9,5) đếm các ô có giá trị 5 ở Cột D.

46. II. NHÓM HÀM THỐNG KÊ 7. Tổng quan về hàm AVERAGEIF Một số lỗi thường gặp 1. Hàm sẽ xảy ra lỗi nếu đối số điều kiện là chuỗi văn bản lớn hơn 255 kí tự và văn bản không được đặt trong trong dấu ngoặc kép. Khắc phục: thêm dấu ngoặc kép vào đối số là văn bản và thay đổi đối số điều kiện nếu đó là chuỗi văn bản lớn hơn 255 kí tự 2. Hàm không phân biệt được chữ viết hoa và viết thường, không xử lý được các kí tự lỗi nên có thể gây ra lỗi hàm không chạy được hoặc sai kết quả Khắc phục: kiểm tra lại hàm và sửa đổi.

47. ĐẠI HỌC BÁCH KHOA HÀ NỘI PHẦN III. NHÓM HÀM TÍNH TOÁN

48. III. NHÓM HÀM TÍNH TOÁN Nhóm hàm tính toán rất phổ biến trong excel, bao gồm các hàm cơ bản như SUM (tính tổng), SUMIF ... Hàm tính toán được dùng để thực hiện các phép tính mà người dùng đưa ra một cách nhanh gọn và chính xác. 1. Tổng quan về nhóm hàm SUM SUMIF PRODUCT SUMPRODUCT ROUND SUBTOTAL 2. Một số hàm thông dụng

49. III. NHÓM HÀM TÍNH TOÁN 3. Tổng quan về hàm SUM Công dụng: Hàm được sử dụng để tính tổng một chuỗi các số hoặc ô. Bạn có thể tính tổng các giá trị riêng lẻ, các ô hoặc các vùng tham chiếu hoặc cả 3 dạng. Cú pháp: SUM (number1,[number2],..) number1 là tham số bắt buộc, là tham chiếu ô, hoặc phạm vi cần tính tổng number2,... là tham số không bắt buộc, l à tham chiếu ô hoặc phạm vi cần tính tổng bổ sung, tối đa 225 số. Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ

50. III. NHÓM HÀM TÍNH TOÁN 4. Tổng quan về hàm SUMIF Công dụng: Hàm SUMIF trả về giá trị tổng của các ô thỏa mãn một điều kiện cho trước nào đó Cú pháp: SUMIF (range, criteria,[sum_range]) range là tham số bắt buộc, là vùng ô mà bạn muốn đánh giá theo điều kiện để tính tổng criteria là tham số bắt buộc, là điều kiện để bạn đánh giá dải ô phía trước sum_range là tham số tùy chọn. Đây là các ô thực tế được đem ra tính tổng nếu các ô tương ứng ở phần range thỏa mãn điều kiện. Nếu bạn không điền sum_range, excel sẽ tính các ô thỏa mãn trong vùng range. sum_range có cùng kích cỡ với range, nếu không việc tính toán sẽ bị gián đoạn hoặc việc tính toán sẽ bắt đầu với ô đầu tiên trong sum_range và kéo dài hoặc thu ngắn theo kích thước của range.

51. III. NHÓM HÀM TÍNH TOÁN 4. Tổng quan về hàm SUMIF Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ Tính tổng lương phòng hành chính Lỗi hay gặp: địa chỉ của range không được cố định đãn tới bị sai lệch. Khắc phục bằng cách sử dụng địa chỉ cố định (thêm $)

52. III. NHÓM HÀM TÍNH TOÁN 5. Tổng quan về hàm PRODUCT Công dụng: Hàm PRODUCT được sử dụng để nhân các đối số và trả về tích của chúng. Hàm này thay thế cho toán tử nhân trong Excel. Cú pháp: PRODUCT (number1, [number2],..) number1: là tham số bắt buộc, là số hoặc phạm vi thứ nhất mà bạn muốn nhân. number2 : là tham số tùy chọn, là các số hoặc phạm vi bổ sung mà bạn muốn nhân, tối đa 255 số

53. III. NHÓM HÀM TÍNH TOÁN 5. Tổng quan về hàm PRODUCT Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ Tính tiền lương biết tiền lương bằng Ngày làm việc * Tiền công/ ngày Kết quả trả về như bảng

54. III. NHÓM HÀM TÍNH TOÁN 6. Tổng quan về hàm SUMPRODUCT Công dụng: Hàm SUMPRODUCT trả về tổng của tích các dải ô hoặc mảng tương ứng. Thao tác mặc định là nhân nhưng cũng có thể thực hiện phép nhân, phép trừ và phép chia. Hàm này thường được ứng dụng để tính tổng doanh thu, tổng số mặt hàng ... Cú pháp: SUMPRODUCT (array1, [array2], [array3], …) array1 là tham số bắt buộc. Đối số mảng đầu tiên mà bạn muốn nhân các thành phần của nó rồi cộng lại [array2], [array3] ,.. là các tham số tùy chọn. Các đối số mảng từ 2 đến 255 mà bạn muốn nhân các ô của nó với array1 rồi cộng tổng.

55. III. NHÓM HÀM TÍNH TOÁN 6. Tổng quan về hàm SUMPRODUCT Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ Giả sử bạn có số lượng ở các ô A2:A4, và bạn muốn tính tổng. Nếu bạn đang giải một phép toán ở trường, thì bạn sẽ nhân số lượng với giá tiền của mỗi sản phẩm, rồi cộng chúng lại

56. III. NHÓM HÀM TÍNH TOÁN 6. Tổng quan về hàm SUMPRODUCT Một số lỗi thường gặp 1. Lỗi #VALUE xảy ra khi các mảng đối số không có cùng kích thước. Khắc phục: kiểm tra và sửa đổi lại các mảng đối số sao cho chúng có cùng kích thước Mở rộng: - Đối với các vùng tham chiếu không phải giá trị số, hàm sẽ coi ô đó là 0. Nếu hàm SUMPRODUCT chỉ có 1 vùng đối số tham chiếu, kết quả giống hàm SUM - Khi bạn muốn sử dụng một mảng đối số như một điều kiện, sử dụng dấu hoa thi (*) cùng với mảng đối số đó.

57. III. NHÓM HÀM TÍNH TOÁN 7. Tổng quan về hàm ROUND Công dụng: Hàm ROUND được sử dụng để làm tròn một số tới chữ số xác định Cú pháp: ROUND (number, num_digits) number là tham số bắt buộc, là số mà bạn muốn làm tròn. num_digits là tham số bắt buộc, là số chữ số mà bạn muốn làm tròn số tới đó Nếu num_digits lớn hơn 0 thì số được làm tròn tới số vị trí thập phân được chỉ định. Nếu num_digits bằng 0 thì số được làm tròn tới số nguyên gần nhất. Nếu num_digits nhỏ hơn 0 thì số được làm tròn sang bên trái dấu thập phân.

58. III. NHÓM HÀM TÍNH TOÁN 7. Tổng quan về hàm ROUND Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ với num_digits = 0 với num_digits < 0 với num_digits > 0

59. III. NHÓM HÀM TÍNH TOÁN 8. Tổng quan về hàm SUBTOTAL Công dụng: Hàm có nhiệm vụ để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trong trường hợp này, “tổng phụ” (subtotal) không phải tổng các con số trong 1 dãy ô xác định. Các hàm Excel khác được thiết kế chỉ để thực hiện 1 chức năng cụ thể, nhưng hàm SUBTOTAL lại rất linh hoạt – có thể tính toán hoặc làm phép logic như đếm số ô, tính trung bình, tìm giá trị lớn nhất/nhỏ nhất… Cú pháp: SUBTOTAL (function_num,ref1,[ref2],...) Function_num - Bắt buộc. Số 1-11 hay 101-111 chỉ định hàm sử dụng cho tổng phụ. 1-11 bao gồm những hàng ẩn bằng cách thủ công, còn 101-111 loại trừ chúng ra; những ô được lọc ra sẽ luôn được loại trừ. Ref1 - Bắt buộc. Phạm vi hoặc tham chiếu được đặt tên đầu tiên mà bạn muốn tính tổng phụ cho nó. Ref2 ,... - Tùy chọn. Phạm vi hoặc chuỗi được đặt tên từ 2 đến 254 mà bạn muốn tính tổng phụ cho nó.

60. III. NHÓM HÀM TÍNH TOÁN 8. Tổng quan về hàm SUBTOTAL Function_num (bao gồm giá trị ẩn) Function_num (bỏ qua giá trị ẩn) Hàm 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP

61. III. NHÓM HÀM TÍNH TOÁN 8. Tổng quan về hàm SUBTOTAL Cách sử dụng: Sử dụng cú pháp đã phân tích ở trên và áp dụng vào thực tế. (Tham khảo ví dụ mẫu). VÍ DỤ Ta sẽ dùng công thức SUBTOTAL 9 để cộng tổng các giá trị trong ô từ C2 đến C8: SUBTOTAL(9,C2:C8)

62. III. NHÓM HÀM TÍNH TOÁN 8. Tổng quan về hàm SUBTOTAL Một số lỗi thường gặp 1. #VALUE! Số xác định chức năng không nằm trong khoảng 1-11 hoặc 101-111 hay có tham chiếu (ref) là tham chiếu 3D. Khắc phục: kiểm tra và sửa đổi lại số xác định chức năng 2 . #DIV/0! Xảy ra khi 1 tổng cụ thể phải chia cho 0 (ví dụ: tính trung bình cộng hoặc độ lệch chuẩn của 1 dãy ô không chứa giá trị số) Khắc phục: kiểm tra và sửa đổi lại hàm hoặc giá trị 3. #NAME? tên hàm SUBTOTAL sai chính tả. Khắc phục: kiểm tra và sửa đổi lại tên hàm

63. ĐẠI HỌC BÁCH KHOA HÀ NỘI KẾT THÚC NỘI DUNG THUYẾT TRÌNH CẢM ƠN THẦY CÔ VÀ CÁC BẠN ĐÃ LẮNG NGHE