›
›

Bài giảng Tin học văn phòng - Bài 2: Các hàm cơ bản trong Excel - Thiều Quang Trung

pdf 47 trang ngocly 1330
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 2: Các hàm cơ bản trong Excel - 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_2_cac_ham_co_ban_trong_excel.pdf

Nội dung text: Bài giảng Tin học văn phòng - Bài 2: Các hàm cơ bản trong Excel - Thiều Quang Trung

  1. BÀI 2 CÁC HÀM CƠ BẢN TRONG EXCEL 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 • Các toán tử trong Excel • Nhóm hàm xử lý số học, chuỗi, ngày 2 tháng 3 • Nhóm hàm logic 4 • Nhóm hàm dò tìm GV. Thiều Quang Trung 2
  3. Các toán tử đối với dữ liệu dạng số • Toán tử số học Toán tử Mục đích + Cộng - Trừ * Nhân / Chia % Lấy phần trăm ^ Luỹ thừa GV. Thiều Quang Trung 3
  4. Các toán tử đối với dữ liệu dạng số Độ ưu tiên của các toán tử được thực hiện theo mức độ sau: Toán tử Mục đích ^ Luỹ thừa *, / Nhân, chia +, - Cộng, trừ Chú ý: - Các toán tử trong một biểu thức có cùng độ ưu tiên thì thứ tự thực hiện là từ trái sang phải. - Có thể thay đổi thứ tự ưu tiên bằng cách thêm dấu ngoặc trong các thành phần tạo nên công thức. GV. Thiều Quang Trung 4
  5. Các toán tử đối với dữ liệu dạng số • Toán tử logic NOT : Toán tử phủ định AND : Toán tử và OR : Toán tử hoặc Biểu thức logic chỉ có hai giá trị TRUE và FALSE GV. Thiều Quang Trung 5
  6. Các toán tử đối với dữ liệu dạng số • Toán tử quan hệ Để so sánh các kết quả ta có thể lập các công thức với các toán tử so sánh. Chúng sẽ cho giá trị TRUE hoặc FALSE tuỳ thuộc vào cách xem xét các điều kiện. TOÁN TỬ MỤC ĐÍCH = Bằng nhau Lớn hơn = Lớn hơn hoặc bằng <> Không bằng nhau GV. Thiều Quang Trung 6
  7. Các toán tử đối với kiểu ký tự Các toán tử quan hệ: = , , =, <> Toán tử nối chuỗi : & Ví dụ: ="Khoa " & "Công Nghệ" cho kết quả là "Khoa Công Nghệ" Chú ý: Chuỗi phải được đặt trong cặp dấu nháy kép (" "). GV. Thiều Quang Trung 7
  8. Các toán tử đối với dữ liệu ngày tháng Ta có thể sử dụng các phép toán cộng, trừ số học và các phép toán quan hệ khi sử dụng dữ liệu kiểu ngày tháng. GV. Thiều Quang Trung 8
  9. Biến đổi công thức thành giá trị . Thứ tự thực hiện như sau: - Chọn ô chứa công thức muốn biến đổi. - Nhấn phím F2 hoặc click đôi chuột. - Nhấn phím F9, Excel sẽ thay công thức bằng giá trị của nó. GV. Thiều Quang Trung 9
  10. Điền số thứ tự • Có thể điền nhanh số thứ tự bằng các bước: – Nhập số thứ tự đầu tiên, click chuột vào ô vừa nhập. – Đưa chuột đến nút Fill handle của ô vừa nhập, lúc này chuột sẽ có dạng dấu + – Nhấn và giữ phím Ctrl, rồi kéo rê chuột xuống các ô cần điền số thứ tự. – Thả nút trái chuột trước, thả phím Ctrl sau, số thứ tự sẽ được tự động điền vào đúng ô của nó. GV. Thiều Quang Trung 10
  11. Các lỗi của công thức Một số lỗi thường gặp do tạo công thức sai: LỖI Ý NGHĨA # DIV/O Công thức gặp trường hợp chia cho số không # N/A Công thức chứa giá trị không dùng được # NAME? Công thức có dùng tên mà Excel không nhận ra # NUM! Công thức dùng một số không đúng # VALUE Công thức dùng một đối số hoặc toán tử sai kiểu GV. Thiều Quang Trung 11
  12. Hàm trong Excel • Hàm là một biểu thức tính toán đã được định nghĩa trước để thực hiện các tính toán phức tạp thay cách viết công thức cho các ô. • Cú pháp chung của hàm như sau: = ( ) • Tên hàm có thể viết bằng chữ hoa hay chữ thường. • Các đối số thường cách nhau bằng dấu "," hoặc dấu “;” tuỳ theo thiết lập trong máy. • Đối số có thể là các hằng số, địa chỉ khối, tên khối hoặc một hàm khác GV. Thiều Quang Trung 12
  13. Hàm trong Excel • Nếu đối số là một vùng địa chỉ ô thì cần ghi theo dạng: : . • Ví dụ: – A1:B2 là bao gồm các ô A1, A2, B1, B2. – Hàm tính tổng: =Sum(A1:A4) => tính tổng giá trị các ô từ ô A1 đến ô A4. GV. Thiều Quang Trung 13
  14. Các nhóm hàm cơ bản trong Excel 1. Nhóm hàm xử lý toán học 2. Nhóm hàm xử lý ký tự 3. Nhóm hàm ngày tháng năm 4. Nhóm hàm logic 5. Nhóm hàm dò tìm GV. Thiều Quang Trung 14
  15. 1. Nhóm hàm xử lý toán học 1. Hàm ABS(x) Trả về giá trị tuyệt đối của số x. Ví dụ: ABS(-2) → 2. ABS(2) → 2. 2. Hàm SQRT(x) Trả về căn bậc hai của số không âm x. Ví dụ: SQRT(9) → 3. SQRT(-9) sẽ trả về lỗi #NUM!. GV. Thiều Quang Trung 15
  16. 1. Nhóm hàm xử lý toán học 3. Hàm MOD(n,t) Trả về số dư của phép chia nguyên n/t. Ví dụ: MOD(12,5) → 2. 4. Hàm INT(number) Trả về phần nguyên của số number. Hàm sẽ trả về giá trị theo xu hướng lấy số nguyên nhỏ hơn. Ví dụ: INT(3.5) → 3. INT(-3.5) → -4. GV. Thiều Quang Trung 16
  17. 1. Nhóm hàm xử lý toán học 5. Hàm ROUND(x,n) Trả về số x sau khi làm tròn với độ chính xác đến n số thập phân (n>0). n=0 làm tròn hàng đơn vị. n=-1 làm tròn hàng chục Ví dụ: ROUND(3.547,2) → 3.55. ROUND(14624.47,-2) → 14600 (làm tròn hàng trăm). GV. Thiều Quang Trung 17
  18. 2. Nhóm hàm xử lý ký tự 1. Hàm LEFT(text,number) Lấy number ký tự bên trái của text. Ví dụ: LEFT(“Thiều Quang Trung",5) → “Thiều". 2. Hàm RIGHT(text,number) Lấy number ký tự bên phải của text. Ví dụ: RIGHT(“Thiều Quang Trung”,11) → “Quang Trung”. GV. Thiều Quang Trung 18
  19. 2. Nhóm hàm xử lý ký tự 3. Hàm LEN(text) Trả về độ dài của chuỗi ký tự text Ví dụ: LEN(“Thiều Quang Trung") → 17. 4. Hàm LOWER(text) Chuyển text thành chữ in thường Ví dụ: LOWER("Học Và Hành") → "học và hành". GV. Thiều Quang Trung 19
  20. 2. Nhóm hàm xử lý ký tự 5. Hàm UPPER(text) Chuyển chữ thường thành chữ in hoa. Ví dụ: UPPER("Học Và Hành") → "HỌC VÀ HÀNH". 6. Hàm PROPER(text) Chuyển các chữ cái đầu từ của text thành chữ hoa và các chữ còn lại thành chữ thường. Ví dụ: PROPER("trUng tâM") → "Trung Tâm". GV. Thiều Quang Trung 20
  21. 2. Nhóm hàm xử lý ký tự 7. Hàm MID(text,m,n) Cho kết quả là chuỗi con trích ra từ chuỗi text từ vị trí m và gồm n ký tự. Ví dụ: MID(“Thiều Quang Trung",7,5) → “Quang". 8. Hàm TRIM(text) Cắt bỏ các ký tự trắng hai phía của text. Ví dụ:TRIM(" Quang Trung ") → “Quang Trung". GV. Thiều Quang Trung 21
  22. 2. Nhóm hàm xử lý ký tự 9. Hàm FIND và Hàm SEARCH FIND(find_text, within_text [, start_num]) SEARCH(find_text, within_text [, start_num]) – Tìm vị trí bắt đầu của một chuỗi con trong một chuỗi find_text: chuỗi văn bản cần tìm (chuỗi con) within_text: chuỗi văn bản chứa chuỗi cần tìm start_num: vị trí bắt đầu tìm (mặc định là 1) – Hàm Search() không phân biệt chữ hoa, còn hàm Find() có phân biệt chữ hoa → tìm chính xác GV. Thiều Quang Trung 22
  23. 2. Nhóm hàm xử lý ký tự 10. Hàm SUBSTITUTE(text, old_text, new_text, [instance_num]) Thay thế chuỗi cũ bằng một chuỗi mới trong chuỗi dữ liệu ban đầu. Ví dụ: SUBSTITUTE("Chi phí ngân sách năm 2015", "2015", "2016") → Chi phí ngân sách năm 2016 GV. Thiều Quang Trung 23
  24. 2. Nhóm hàm xử lý ký tự 11. Hàm REPLACE(old_text, start_num, num_chars, new_text) Thay thế một phần chuỗi cũ bằng một chuỗi mới, phần chuỗi thay thế sẽ được xác định tại vị trí n và có chiều dài m Ví dụ: ô A1 đang chưa chuỗi ”Chi phí ngân sách năm 2015” REPLACE(A1, FIND("2015", A1), LEN("2015"), "2016") → Chi phí ngân sách năm 2016 GV. Thiều Quang Trung 24
  25. 3. Nhóm hàm ngày tháng năm 1. Hàm DATE(year, month, day) Cho giá trị là một dữ liệu kiểu ngày ứng với các đối số được đưa vào. Ví dụ: DATE(2016,04,30) → 04/30/2016 (định dạng theo tháng/ngày/ năm). GV. Thiều Quang Trung 25
  26. 3. Nhóm hàm ngày tháng năm 2. Hàm DAY(date) Trả về giá trị ngày trong tháng của biến ngày tháng date. Ví dụ: Day(“04/30/2016”) → 30 (định dạng theo kiểu tháng/ngày/năm) GV. Thiều Quang Trung 26
  27. 3. Nhóm hàm ngày tháng năm 3. Hàm MONTH(date) Trả về giá trị tháng trong năm của biến ngày tháng date. Ví dụ: MONTH(“04/30/2016”) → 04 (định dạng theo tháng/ngày/ năm). GV. Thiều Quang Trung 27
  28. 3. Nhóm hàm ngày tháng năm 4. Hàm YEAR(date) Trả về giá trị năm của biến ngày tháng date. Ví dụ:YEAR(“04/30/2016”) → 2016 (định dạng theo tháng/ngày/ năm). GV. Thiều Quang Trung 28
  29. 3. Nhóm hàm ngày tháng năm 5. Hàm DATEDIF(date1, date2, kiểu trả về) Trả về số ngày (số tháng hoặc số năm) tính từ mốc thời gian date1 đến date2. Trong đó là: “d” : trả về số ngày “m” : trả về số tháng “y” : trả về số năm Ví dụ: DATEDIF("1970/06/20","2016/06/20","y") → 46 GV. Thiều Quang Trung 29
  30. 4. Nhóm hàm logic 1. Hàm AND(logical1,logical2, ) Trong đó logical1, logical2, là các đối số nhận một trong hai giá trị logic TRUE hoặc FALSE. Nếu đối số không nhận giá trị logic, hàm AND trả về giá trị lỗi #VALUE! Hàm sẽ trả về giá trị TRUE (ĐÚNG) nếu tất cả các đối số nhận giá trị TRUE; trả về giá trị FALSE (SAI) nếu có ít nhất một đối số nhận giá trị FALSE. Ví dụ: AND(2+2=4, 2+3=5) → TRUE. AND(3>4, 2+3=5) → FALSE. GV. Thiều Quang Trung 30
  31. 4. Nhóm hàm logic 2. Hàm OR(logical1, logical2 ) Trong đó logical1, logical2, là các đối số nhận một trong hai giá trị logic TRUE hoặc FALSE. Nếu đối số không nhận giá trị logic, hàm OR trả về giá trị lỗi #VALUE! Trả về giá trị đúng nếu có ít nhất một đối số nhận giá trị đúng, cho giá trị sai nếu tất cả đối số đều nhận giá trị sai. Ví dụ: OR(2+2=4, 2+3>5) → TRUE. OR(3>4, 2+3<5) → FALSE. GV. Thiều Quang Trung 31
  32. 4. Nhóm hàm logic 3. Hàm IF(biểu thức logic, giá trị đúng, giá trị sai) Hàm trả về giá trị đúng nếu biểu thức logic đúng, ngược lại trả về giá trị sai. Ví dụ: giả sử ô D2 chứa Điểm trung bình, hãy tính Xếp loại dựa vào Điểm trung bình theo ba mức: ĐTB >=8: Xếp loại Giỏi 8 > ĐTB >=7: Xếp loại Khá 7 > ĐTB: Xếp loại Trung bình IF(D2>=8, ”Giỏi”, If(D2>=7, ”Khá”, ”Trung bình”)) GV. Thiều Quang Trung 32
  33. 5. Nhóm hàm dò tìm 1. Hàm VLOOKUP(Giá trị tìm, Bảng tham chiếu, Cột tham chiếu, Cách tìm) . Thực hiện dò tìm với Giá trị tìm trên cột đầu tiên của Bảng tham chiếu, khi tìm thấy thì dịch chuyển qua bên phải đến Cột tham chiếu để lấy trị trong ô tương ứng với vị trí tìm thấy của Giá trị tìm. . Bảng tham chiếu: là một khối ô nhiều hàng nhiều cột. Cột đầu tiên luôn luôn chứa các trị để dò tìm, các cột khác chứa các trị tương ứng để tham chiếu. . Cột tham chiếu: thứ tự của cột tính từ trái qua phải trong bảng tham chiếu, cột đầu tiên được đánh số là 1. GV. Thiều Quang Trung 33
  34. 5.1. Hàm VLOOKUP . Cách tìm: Có hai giá trị: – Nếu là số 0 (hoặc FALSE, còn gọi là dò tìm chính xác): Danh sách các trị của cột 1 không cần sắp xếp theo thứ tự tăng dần. Nếu không tìm thấy trị dò ở Bảng tham chiếu, hàm sẽ trả về lỗi #N/A. – Nếu là số 1 (hoặc TRUE, còn gọi là dò tìm lân cận hay dò tìm xấp xỉ): Danh sách các trị của cột 1 phải được sắp xếp theo thứ tự tăng dần. Nếu không tìm thấy trị dò chính xác nó sẽ lấy giá trị nhỏ hơn và gần bằng với trị dò. Nếu trị dò nhỏ hơn trị đầu tiên của cột 1 thì hàm sẽ trả về lỗi #N/A. – Mặc định cách dò là 1. GV. Thiều Quang Trung 34
  35. 5.1. Hàm VLOOKUP Ví dụ: Thiết lập công thức tại ô E2 ? B C D E TT Họ tên Khu vực Điểm chuẩn 1 Hoàng Phi 2 2 Nguyễn Thị Như 3 3 Trần Đức 1 Khu vực Điểm chuẩn 1 24 2 23 3 22 GV. Thiều Quang Trung 35
  36. 5.2. Hàm HLOOKUP HLOOKUP(Giá trị tìm, Bảng tham chiếu, Hàng tham chiếu, Cách tìm) . Thực hiện tìm kiếm Giá trị tìm trên hàng đầu tiên của Bảng tham chiếu, khi tìm thấy thì dịch chuyển xuống dòng đến Hàng tham chiếu để lấy trị trong ô tương ứng với vị trí tìm thấy của Giá trị tìm. . Bảng tham chiếu: là khối ô nhiều hàng nhiều cột. Hàng đầu tiên luôn luôn chứa các trị để dò tìm, các hàng khác chứa các trị tương ứng để tham chiếu. . Hàng tham chiếu: thứ tự của hàng tính từ trên xuống dưới trong bảng tham chiếu, hàng đầu tiên được đánh số là 1. . Cách tìm: tương tự như VLOOKUP GV. Thiều Quang Trung 36
  37. 5.2. Hàm HLOOKUP Ví dụ: Thiếp lập công thức tại ô E2 ? B C D E 1 TT Họ tên Khu vực Điểm chuẩn 2 1 Hoàng Phi 2 3 2 Nguyễn Thị Như 3 4 3 Trần Đức 1 5 6 Khu vực 1 2 3 7 Điểm chuẩn 24 23 22 8 9 GV. Thiều Quang Trung 37
  38. 5.3. Hàm INDEX INDEX(array, row_num, column_num) • Trả về một giá trị nằm bên trong một mảng dựa vào chỉ số dòng và cột. • array: Là một mảng . • row_num: Chỉ số dòng cần trả về giá trị. • colum_num: Chỉ số cột cần trả về giá trị. GV. Thiều Quang Trung 38
  39. 5.4. Hàm MATCH MATCH(lookup_value, lookup_array, match_type) • Trả về vị trí (chỉ mục) của một giá trị từ một dãy giá trị. • Lookup_value: Là giá trị cần tìm trong bảng giá trị. • Lookup_array: Là một dãy ô liên tục để tìm kiếm giá trị. • Match_type: Có 3 kiểu tìm kiếm là: – 1:Less than (Nhỏ hơn giá trị tìm kiếm) – 0: Exact match (Chính xác giá trị tìm kiếm) – -1:Greater than ( Lớn hơn giá trị tìm kiếm) • Khi bỏ qua không nhập gì thì hàm MATCH mặc định là 1. GV. Thiều Quang Trung 39
  40. GV. Thiều Quang Trung 40
  41. Câu 1: Công thức =ROUND(136485.22, -3) sẽ cho kết quả là: a. 136,000.22 b. 137,000.22 c. 136000 d. 137000 GV. Thiều Quang Trung 41
  42. Câu 02: Giả sử ô A1 chứa giá trị ngày sinh của 1 người là 31/12/1996, ô A2 chứa giá trị ngày hiện tại, để tính tuổi thì gõ công thức nào sau đây: a. =YEAR(TIMEVALUE(A1)) b. =DATEDIF(A1,A2,"y") c. =DATEDIF(A2,A1,"y") d. =YEAR(A2) - YEAR(A1) GV. Thiều Quang Trung 42
  43. Câu 3: Hàm OR( ) trả về kết quả đúng khi nào? a. Chỉ cần một đối tượng trong hàm trả về TRUE b. Nếu có hai đối số trong hàm đều trả về TRUE c. Nếu tất cả các đối số trong hàm đều trả về TRUE d. Tất cả đều sai GV. Thiều Quang Trung 43
  44. Câu 4 : Cho bảng số liệu sau: A B C D E F 1 MaNganh TenNganh N4 N3 N2 N1 2 101 SP. Toan 22.5 21.5 20.5 19.5 3 102 SP. Ly 20.5 19.5 18.5 17.5 4 103 SP. Tin hoc 19.5 18.0 17.5 16.5 5 202 SP. Hoa 20.0 19.0 18.0 17.0 6 309 CN Sinh hoc 22.0 21.0 20.0 19.0 Công thức sau: =VLOOKUP(104,$A$2:$B$6, 2) thì kết quả sẽ là: a. Báo lỗi “N/A” b. SP. Tin hoc c. CN Sinh hoc d. Báo lỗi vì công thức sai GV. Thiều Quang Trung 44
  45. Câu 5 : Cho bảng số liệu sau: A B C D E F 1 MaNganh TenNganh N4 N3 N2 N1 2 101 SP. Toan 22.5 21.5 20.5 19.5 3 102 SP. Ly 20.5 19.5 18.5 17.5 4 103 SP. Tin hoc 19.5 18.0 17.5 16.5 5 202 SP. Hoa 20.0 19.0 18.0 17.0 6 309 CN Sinh hoc 22.0 21.0 20.0 19.0 Công thức MATCH(202,$B$2:$B$6) cho kết quả là: a. 4 b. 5 c. Lỗi “N/A” d. Báo lỗi vì công thức sai GV. Thiều Quang Trung 45
  46. Câu 6 : Cho bảng số liệu sau: A B C D E F 1 MaNganh TenNganh N4 N3 N2 N1 2 101 SP. Toan 22.5 21.5 20.5 19.5 3 102 SP. Ly 20.5 19.5 18.5 17.5 4 103 SP. Tin hoc 19.5 18.0 17.5 16.5 5 202 SP. Hoa 20.0 19.0 18.0 17.0 6 309 CN Sinh hoc 22.0 21.0 20.0 19.0 Công thức INDEX($C$2:$F$6,2,3) cho kết quả là: a. 18.5 b. 18.0 c. 17.5 d. 19.5 GV. Thiều Quang Trung 46
  47. Câu 7 : Cho bảng số liệu sau: A B C D E F 1 MaNganh TenNganh N4 N3 N2 N1 2 101 SP. Toan 22.5 21.5 20.5 19.5 3 102 SP. Ly 20.5 19.5 18.5 17.5 4 103 SP. Tin hoc 19.5 18.0 17.5 16.5 5 202 SP. Hoa 20.0 19.0 18.0 17.0 6 309 CN Sinh hoc 22.0 21.0 20.0 19.0 Công thức INDEX($C$2:$F$6, MATCH(102, $A$2:$A$6, 0), 2) cho kết quả là: a. 18.5 b. 18.0 c. 17.5 d. 19.5 GV. Thiều Quang Trung 47