Bài giảng Tin học văn phòng - Bài 9 & 10: Hàm trong Excel

TIN HỌC VĂN PHÒNG Bài 9 & 10: HÀM TRONG EXCEL 1 Nội dung chính 1. Các loại địa chỉ trong công thức 2. Khái niệm hàm và quy tắc sử dụng 3. Các hàm thời gian 4. Các hàm văn bản 5. Các hàm toán học 6. Các hàm thống kê 7. Các hàm logic 8. Các hàm tìm kiếm 2 1. Các loại địa chỉ trong công thức I Khi sao chép công thức, địa chỉ bên trong công thức sẽ thay đổi theo. Ví dụ : • trong ô B1, gõ công thức = A1 • sao chép công thức từ B1 xuống B2, công thức trong ô B2 đổi thành = A2 I Có những trường hợp ta không muốn địa chỉ bị thay đổi → phải chỉ ra những thành phần cố định trong địa chỉ bằng cách thêm dấu $ vào đằng trước Các loại địa chỉ trong công thức „ Mỗi ô trong excel có địa chỉ giúp các công thức có thể lấy số liệu ra để thực hiệ tính toán. Ví dụ: ô B10 tức là ô ở cột B dòng 10 „ Khi thực hiện sao chép công thức, địa chỉ bên trong công thức sẽ thay đổi theo. Ví dụ: „ Gõ công thức =A1 ở ô B1 „ Sao chép từ B1 xuống B2, công thức đổi thành =A1 Trương Xuân Nam - Khoa CNTT 5 3 Các loại địa chỉ trong công thức Điều gì xảy ra nếu ô F2 được viết là = E2/E8 ? 96 VÝ dô : ®Ó tÝnh L­¬ng theo c«ng thøc : L­¬ng = Sè NC x TiÒn 1 Ngµy, lµm theo c¸c b­íc sau : 1. Chän miÒn E2:E7 (v× c«ng thøc tÝnh trong miÒn gièng nhau : ®Òu lÊy sè ë cét C nh©n víi sè ë cét D- xem phÇn b. ë trªn) 2. Gâ vµo dÊu = 3. Trá chuét vµo « C2, Ên nót tr¸i (hoÆc gâ C2) 4. Gâ dÊu * 5. Trá chuét vµo « D2, Ên nót tr¸i (hoÆc gâ D2). T¹i « E2 vµ thanh c«ng thøc xuÊt hiÖn = C2*D2 6. Ên Ctrl + ↵. T¹i c¸c « tõ E2 ®Õn E7, Excel cho kÕt qu¶ tÝnh L­¬ng cña tõng ng­êi. Ta còng cã thÓ lµm nh­ sau : - Bá qua b­íc 1 - Thùc hiÖn c¸c b­íc tõ 2 ®Õn 5, sau b­íc 5 Ên ↵ - §Ó tÝnh L­¬ng cho nh÷ng ng­êi cßn l¹i, ®­a con trá vÒ « E2, ®iÒn tù ®éng cho tíi « E7. • TÝnh tû lÖ phÇn tr¨m L­¬ng cña tõng ng­êi so víi Tæng L­¬ng : 1. §­a con trá vÒ « F2 4 Các loại địa chỉ trong công thức I Các loại địa chỉ • Tương đối : E8 • Cố định cột : $E8 • Cố định hàng : E$8 • Tuyệt đối : $E$8 I Tham chiếu đến địa chỉ Sheet khác : ! Ví dụ : ’Baitap1’ !E$8 I Tham chiếu đến địa chỉ Workbook khác : [] ! Ví dụ : [Bai9.xlsx]Sheet2 !E8 5 2. Khái niệm hàm và quy tắc sử dụng I Hàm là các công thức định sẵn nhằm thực hiện những tính toán chuyên biệt mà các toán tử đơn giản có thể không thực hiện được I Cú pháp : Tên hàm(Các đối số) I Tên hàm viết liền, không phân biệt chữ hoa, chữ thường I Đối số có thể là giá trị, địa chỉ một ô hoặc nhiều ô I Các đối số đặt trong cặp ngoặc đơn, ngăn cách nhau bởi dấu phẩy hay chấm phẩy hay dấu theo quy định I Không có đối số vẫn phải viết cặp ngoặc, ví dụ TODAY() I Hàm có thể lồng nhau, ví dụ SQRT(SUM(B1 : B9)) 6 Nhập hàm vào bảng tính Đưa con trỏ về ô cần tính rồi chọn một trong các cách sau I Cách 1 : gõ dấu = rồi gõ trực tiếp tên hàm vào ô I Cách 2 : vào ribbon Formulas → Function Library, chọn hàm phù hợp trên menu I Cách 3 : vào ribbon Formulas → Function Library, ấn Insert Function, chọn hàm và nhập đối số 7 3. Các hàm thời gian I Chú ý : định dạng ngày giờ trong Excel phụ thuộc vào thiết lập của máy tính, thường là theo kiểu Mỹ tháng/ngày/năm I DATE(year,month,day) : trả về ngày tháng ứng với số ngày tháng năm • DATE(2017,3,14) trả về 3/14/2017 I DAY/MONTH/YEAR(serial_number) : trả về ngày/tháng/năm trong chuỗi serial_number • DAY("4/1/2017") trả về 1 • MONTH("4/1/2017") trả về 4 • YEAR("4/1/2017") trả về 2017 8 Các hàm thời gian I TIME(hour,minute,second) : trả về thời gian dạng số • TIME(18,7,30) trả về 18 :07 :30 hoặc 6 :07 PM I TODAY() : trả về ngày hiện tại I DAYS(end_date,start_date) : trả về số ngày giữa hai thời điểm • DAYS(TODAY(),"1/1/2017") trả về số ngày từ đầu năm tới hiện tại 9 Các hàm thời gian I WEEKDAY(serial_number,return_type) : trả về thứ trong tuần • serial_number : giá trị biến biểu diễn ngày tháng • return_type : quy định kiểu tính ngày đầu tuần I 1 : chủ nhật là 1 đến thứ 7 là 7 I 2 : thứ 2 là 1 đến chủ nhật là 7 I 3 : thứ 2 là 0 đến chủ nhật là 6 • WEEKDAY("3/14/2017",1) trả về 3, ngày 3/14/2017 là thứ 3 10 4. Các hàm văn bản I EXACT(text1,text2) : trả về True nếu text1 và text2 giống hệt nhau, ngược lại trả về False • EXACT(Excel,EXCEL) trả về False I LOWER/UPPER(text) : chuyển text thành chữ thường/hoa • LOWER("EXCEL") trả về "excel" I PROPER(text) : viết hoa chỉ các chữ cái đầu của mỗi từ trong text • PROPER("HÔM nay") trả về "Hôm Nay" 11 Các hàm văn bản I FIND(text1,text) : trả về vị trí xuất hiện đầu tiên của text1 trong text, nếu không tìm thấy thì trả về #VALUE ! • FIND("a","Hoa cỏ may") trả về 3 • có thể thêm tham số thứ 3 quy định vị trí bắt đầu tìm FIND("a","Hoa cỏ may",4) trả về 9 • phân biệt chữ hoa chữ thường FIND("N","Bình MINH") trả về 8 I SEARCH(text1,text) : tương tự hàm FIND nhưng không biệt chữ hoa chữ thường I LEN(text) : trả về độ dài (số ký tự) của text • LEN("Hoa cỏ may") trả về 10 12 Các hàm văn bản I LEFT/RIGHT(text,n) : trả về n ký tự ngoài cùng bên trái/phải của text • RIGHT("Hoa cỏ may",3) trả về "may" I MID(text,n,k) : trả về đoạn giữa của text, tính từ vị trí n, lấy k ký tự • MID("Hoa cỏ may",5,3) trả về "cỏ " I REPLACE(text,n,k,text1) : cắt đoạn giữa của text gồm k ký tự tính từ vị trí n, thay bằng text1 • REPLACE("Hoa cỏ may",5,6,"gạo") trả về "Hoa gạo" I TRIM(text) : cắt bỏ các ký tự trống vô nghĩa của text • TRIM(" Hoa cỏ may ") trả về "Hoa cỏ may" 13 5. Các hàm toán học I ABS(x) : tính giá trị tuyệt đối I SIGN(x) : xác định dấu của x , trả về 1 nếu x > 0, 0 nếu x = 0, -1 nếu x < 0 I SQRT(x) : tính căn bậc 2 của x với x > 0 I COS(x), SIN(x), TAN(x) : các hàm lượng giác, x tính bằng radian I PI() : trả về số pi 3.141592654 I DEGREES(x) : đổi radian sang độ I RAND() : trả về số ngẫu nhiên giữa 0 và 1 14 Các hàm toán học I SUM(n1,n2,. . .) : tính tổng n1+ n2+ . . . I PRODUCT(n1,n2,. . .) : tính tích n1 ∗ n2 ∗ . . . I FACT(n) : tính n! = 1 ∗ 2 ∗ · · · ∗ n I POWER(a,b) : tính ab I EXP(x) : tính ex I LOG(a,b) : trả về logb a, nếu không có b thì mặc định b = 10 I MOD(a,b) : trả về số dư trong phép chia hai số nguyên a/b 15 Các hàm toán học I TRUNC(x) : cắt bỏ phần thập phân, chỉ lấy phần nguyên • TRUNC(3.24) trả về 3, TRUNC(-3.24) trả về -3 I INT(x) : trả về số nguyên lớn nhất không vượt quá x • INT(3.24) trả về 3, INT(-3.24) trả về -4 I ROUND(x,n) : làm tròn x đến n chữ số thập phân nếu n > 0 • Nếu n < 0 thì x được làm tròn đến chữ số bên trái của dấu chấm thập phân • ROUND(1234.567,2) trả về 1234.57, ROUND(1234.567,1) trả về 1234.6, ROUND(1234.567,-2) trả về 1200 16 SUMIF(range,criteria,[sum_range]) I tính tổng các giá trị trong phạm vi đáp ứng tiêu chí • range : phạm vi cần đánh giá theo tiêu chí • criteria : tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định • sum_range : các ô thực tế để cộngHÀM TOÁN HỌC Giá trị Tài sản Tiền hoa hồng Dữ liệu $ 100.000,00 $ 7.000,00 $ 250.000,00 $ 200.000,00 $ 14.000,00 $ 300.000,00 $ 21.000,00 $ 400.000,00 $ 28.000,00 Công thức Mô tả Kết quả =SUMIF(A2:A5,">160000",B2:B5) Tổng tiền hoa hồng cho các giá trị tài sản lớn hơn 160.000. $ 63.000,00 =SUMIF(A2:A5,">160000") Tổng các giá trị tài sản lớn hơn 160.000. $ 900.000,00 =SUMIF(A2:A5,300000,B2:B5) Tổng tiền hoa hồng cho các giá trị tài sản bằng 300.000. ? =SUMIF(A2:A5,">" & C2,B2:B5) Tổng tiền hoa hồng cho các giá trị tài sản lớn hơn giá trị tại C2. ? $ 21.000,00 $ 49.000,00 17 SUMIFS(sum_range,criteria_range1,criteria1, [criteria_range2,criteria2],. . .) I tính tổng các giá trị trong phạm vi đáp ứng nhiều tiêu chíHÀM TOÁN HỌC •  Ví dụ Số lượng Đã bán Sản phẩm Người bán hàng 5 Táo 1 4 Táo 2 15 Atisô 1 3 Atisô 2 22 Chuối 1 12 Chuối 2 10 Cà rốt 1 33 Cà rốt 2 Công thức Mô tả Kết quả =SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, 1) Cộng tổng số sản phẩm bán được bắt đầu bằng chữ "A" và do Người bán hàng 1 bán. 15 =SUMIFS(A2:A9, B2:B9, "Chuối", C2:C9, 1) Cộng tổng số sản phẩm (không bao gồm Chuối) do Người bán hàng 1 bán. 30 •  í Số lượng Đã bán ản p 5 Táo 4 Táo 15 tisô 3 tisô 22 huối 12 huối 10 à rốt 33 à rốt Công thức ô tả t =SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, 1) Cộng tổng số s t bằng ch " " v i . =SUMIFS(A2:A9, B2:B9, "Chuối", C2:C9, 1) Cộng tổng số s ( Chuối) do g i . 18 6. Các hàm thống kê I MAX/MIN(n1,n2,. . .) : trả về giá trị lớn/nhỏ nhất trong tập dữ liệu • MAX/MIN(range) trả về giá trị lớn/nhỏ nhất trong một vùng I LARGE/SMALL(array,k) : trả về phần tử lớn/nhỏ thứ k trong vùng array I RANK(x,range) : trả về thứ hạng của x trong danh sách các số tham chiếu bởi range, xếp theo thứ tự giảm dần • có thể thêm tham số thứ 3 với giá trị là 1 để lấy thứ hạng theo thứ tự tăng dần 19 Các hàm thống kê I AVERAGE(n1,n2,. . .) : trả về trung bình cộng của một dãy các số • AVERAGE(range) trả về trung bình cộng trong một vùng I MODE(n1,n2,. . .) : trả về giá trị hay gặp nhất trong vùng I COUNT(range) : đếm số ô chứa dữ liệu số trong một vùng I COUNTA(range) : đếm số ô không rỗng trong một vùng 20 COUNTIF(range,criteria) I đếm số ô trong phạm vi đáp ứng một tiêu chí nào đó HÀM TOÁN HỌC •  COUNTIF –  Hàm đếm số ô trong phạm vi xác định đáp ứng một tiêu chí nào đó. –  Cú pháp: COUNTIF( range, criteria ) •  range: mảng hay tham chiếu chứa số •  Criteria: tiêu chí Dữ liệu Dữ liệu táo 32 cam 54 đào 75 táo 86 Công thức Mô tả Kết quả =COUNTIF(A2:A5,"t áo") Số ô có chứa táo trong các ô từ A2 tới A5. ? =COUNTIF(A2:A5,A 4) Số ô có chứa đào trong các ô từ A2 tới A5. ? =COUNTIF(A2:A5,A 3)+COUNTIF(A2:A5, A2) Số ô có chứa cam và táo trong các ô từ A2 tới A5. ? =COUNTIF(B2:B5,"> 55") Số ô có giá trị lớn hơn 55 trong các ô từ B2 tới B5. ? =COUNTIF(B2:B5,"< >"&B4) Số ô có giá trị khác 75 trong các ô từ B2 tới B5. ? 2 1 3 2 3 HÀM TOÁN HỌC •  COUNTIF –  Hàm đếm số ô trong phạm vi xác định đáp ứng một tiêu chí nào đó. –  Cú pháp: COUNTIF( range, cr teria ) •  range: mảng hay tham chiếu chứa số •  Criteria: tiêu chí Dữ liệu Dữ liệu táo 32 cam 54 đào 75 táo 86 Công thức Mô tả Kết quả =COUNTIF(A2:A5,"t áo") Số ô có chứa táo trong các ô từ A2 tới A5. ? =COUNTIF(A2:A5,A 4) Số ô có chứa đào trong các ô từ A2 tới A5. ? =COUNTIF(A2:A5,A 3)+COUNTIF(A2:A5, A2) Số ô có chứa cam và táo trong các ô từ A2 tới A5. ? =COUNTIF(B2:B5,"> 55") Số ô có giá trị lớn hơn 55 trong các ô từ B2 tới B5. ? =COUNTIF(B2:B5,"< >"&B4) Số ô có giá trị khác 75 trong các ô từ B2 tới B5. ? 2 1 3 2 3 21 7. Các hàm logic I AND(logical1,logical2,. . .) : trả về True nếu tất cả các đối số là True, ngược lại trả về False I OR(logical1,logical2,. . .) : trả về False nếu tất cả các đối số là False, ngược lại trả về True I NOT(logical) : phép phủ định I IF(test,value1,value2) : trả về value1 nếu test có giá trị True, ngược lại trả về value2 • hàm IF có thể lồng nhau đến 7 cấp I IFERROR(expression,value) : trả về giá trị của expression nếu tính được, ngược lại trả về value • IFERROR(3/0,"lỗi tính toán") trả về "lỗi tính toán" 22 8. Các hàm tìm kiếm I VLOOKUP(A,B,C,D) : tìm giá trị A trong cột đầu tiên của vùng B, nếu tìm được sẽ trả về giá trị tương ứng trong cột thứ C của vùng B • B là vùng tìm kiếm hay bảng tra cứu, địa chỉ phải là tuyệt đối, nên đặt tên cho vùng này • Tham số D có giá trị logic, quy định cách thức tìm kiếm I D = True hoặc bỏ qua D : tìm "gần chính xác" I D = False : tìm chính xác giá trị A, nếu không thấy sẽ trả về #N/A • Trong chế độ tìm "gần chính xác" I cột đầu tiên của B phải xếp thứ tự tăng dần I A được xếp tương ứng với giá trị lớn nhất trong các giá trị nhỏ hơn hoặc bằng A 23 Các hàm tìm kiếm 24 Các hàm tìm kiếm I HLOOKUP(A,B,C,D) : tương tự VLOOKUP nhưng thay cột bằng hàng I COLUMN(A) : trả về thứ tự cột mà ô A đứng • COLUMN(D5) trả về 4 (cột D) I ROW(A) : trả về thứ tự dòng mà ô A đứng I MATCH(A,B,C) : trả về thứ tự của giá trị A trong dãy B, giá trị C quy định cách thức tìm • C = 1 hoặc bỏ qua C : tìm giá trị lớn nhất trong các giá trị nhỏ hơn hoặc bằng A, dãy B phải xếp tăng dần • C = -1 : tìm giá trị nhỏ nhất trong các giá trị lớn hơn hoặc bằng A, dãy B phải xếp giảm dần • C = 0 : tìm chính xác, không cần sắp xếp dãy B • MATCH("c",{"a","b","c"}, 0) trả về 3 25