Các hàm trong Microsof Excel

1 GV: Nguyễn Đình Liên Tin học đại cương 2 Bài 2 Các hàm trong Microsof Excel Phần 3 – Microsoft Excel 2 3 MS Excel: Biểu thức Excel  Lập biểu thức (công thức)  Bắt đầu bằng dấu “=“  Biểu thức bao gồm các hằng (nếu là chuỗi thì đặt trong dấu “ ”), địa chỉ ô (hoặc khối) hoặc các hàm, kết hợp với nhau bằng các phép toán.  Ngoài các phép toán cơ bản như: +, -, *, /, ^, & còn có các hàm xử lý số, chuổi, ngày tháng, các phép toán tìm kiếm, logic, so sánh, …  Ví dụ: =3.14 * 5 =A3 + 18 =“Lạc Hồng” & Right(C5,6) 4  các kiểu địa chỉ trong biểu thức  Địa chỉ tương đối: lấy địa chỉ dòng ghép với địa chỉ cột của ô (ví dụ: C13; A23). Địa chỉ tương đối trong một biểu thức sẽ bị thay đổi khi sao chép biểu thức qua ô khác.  Địa chỉ tuyệt đối: khi thêm dấu $ vào trước dòng và cột của địa chỉ tương đối (ví dụ: $C$13; $A$23). Địa chỉ tuyệt đối không bị thay đổi khi sao chép biểu thức.  Địa chỉ tuyệt đối dòng: khi thêm dấu $ vào trước dòng của địa chỉ tương đối (ví dụ: C$13; A$23). Địa chỉ tuyệt đối dòng không bị thay đổi phần dòng khi sao chép biểu thức.  Địa chỉ tuyệt đối cột: khi thêm dấu $ vào trước cột của địa chỉ tương đối (ví dụ: $C13; $A23). Địa chỉ tuyệt đối cột không bị thay đổi phần cột khi sao chép biểu thức.  Thay đổi kiểu địa chỉ: chọn địa chỉ đó rồi nhấn F4 nhiều lần.  Tham khảo tới ô ở sheet khác: SheetName!Địa_chỉ_ô =Sheet2!E6+Sheet3!G7 MS Excel: Biểu thức Excel 3 5  Một số lỗi thường gặp khi lập biểu thức  #Div/0!: trong biểu thức có phép chia cho 0.  #Num!: số có trong biểu thức không phù hợp (ví dụ: 1234^4321)  #Ref: tham chiếu đến những ô hoặc vùng không tồn tại trên bảng tính.  #Value!: sai kiểu dữ liệu trong biểu thức.  #Name!: sử dụng tên ô, vùng không đúng.  #NULL!: Hàm sử dụng dữ liệu giao nhau của 2 vùng mà 2 vùng này không có phần chung nên phần giao rỗng. MS Excel: Biểu thức Excel 6  Hàm trong excel  Ý nghĩa:  Là các phép toán được định nghĩa sẵn để thực hiện các tính toán chuyên biệt.  Ví dụ:  Sum(A1:A5): Tính tổng các số từ A1A5  Right(“Lac Hong”)  ‘g’  Int(7.5)  7 Mod(A1,7)  Mỗi hàm nhận các tham số khác nhau. Các tham số có thể là giá trị, địa chỉ ô, biểu thức hoặc một hàm khác. Số lượng tham số trong các hàm cũng khác nhau. MS Excel: Các hàm Excel 4 7  Hàm – ý nghĩa và cách sử dụng:  Sử dụng hàm:  Tên hàm(danh sách tham số phân cách bởi dấu phẩy)  Tên hàm phải gõ chính xác, không phân biệt hoa thường.  Phải truyền đầy đủ các tham số và phải đúng kiểu dữ liệu cho các hàm.  Những tham số đặt trong cặp dấu [] thì có thể truyền vào hoặc bỏ qua.  Một số hàm có số lượng các tham số không cố định. MS Excel: Các hàm Excel 8  Chèn hàm vào ô:  Nhập hàm trực tiếp  Click chọn nút trên thanh công thức  Từ Ribbon Formulas  Insert Function MS Excel: Các hàm Excel Tìm hàm Chọn hàm Mô tả hàm Chọn loại hàm 5 9  Chèn hàm vào ô:  Dưới đây là hộp thoại hỗ trợ nhập | chọn (các) tham số cho hàm MS Excel: Các hàm Excel 10  Các hàm xử lý số  Hàm ABS: trả về giá trị tuyệt đối  Cú pháp: ABS(n)  Ví dụ: ABS(5) = ABS(-5) = 5  Hàm SQRT: trả về giá trị là căn bậc 2  Cú pháp: SQRT(n)  Ví dụ: SQRT(4) = 2  Hàm INT: trả về phần nguyên của số thập phân  Cú pháp: INT(n)  Ví dụ: INT(5.4) = 5 MS Excel: Các hàm Excel 6 11  Các hàm xử lý số  Hàm ROUND: làm tròn số thập phân m đến n chữ số lẻ. (nếu n dương thì làm tròn phần thập phân, n âm thì làm tròn phần nguyên).  Cú pháp: ROUND(m, n)  Ví dụ: ROUND (5.736, 2) = 5.74 ROUND(139, -1) = 140  Hàm MOD: trả về phần dư của phép chia m cho n  Cú pháp: MOD(m, n)  Ví dụ: MOD(7, 3) = 1 MS Excel: Các hàm Excel 12  Các hàm xử lý chuỗi  Hàm LOWER: chuyển tất cả ký tự trong chuỗi thành chữ thường  Cú pháp: LOWER(s)  Ví dụ: LOWER(“Lạc Hồng”)  lạc hồng  Hàm UPPER: chuyển tất cả ký tự trong chuỗi thành chữ hoa.  Cú pháp: UPPER(s)  Ví dụ: UPPER(“excel”)  EXCEL MS Excel: Các hàm Excel 7 13  Các hàm xử lý chuỗi  Hàm PROPER: chuyển tất cả các ký tự đầu của mỗi từ thành chữ hoa.  Cú pháp: PROPER(s)  Ví dụ: PROPER(“lạc hồng”)  Lạc Hồng  Hàm LEFT: trích ra n ký tự bên trái chuỗi s  Cú pháp: LEFT(s, n)  Ví dụ: LEFT(“lạc hồng”, 3)  lạc MS Excel: Các hàm Excel 14  Các hàm xử lý chuỗi  Hàm RIGHT:  Cú pháp: RIGHT(s, n)  Công dụng: trích ra n ký tự bên phải chuỗi s  Ví dụ: RIGHT(“lạc hồng”, 4)  hồng  Hàm MID:  Cú pháp: MID(s, m, n)  Công dụng: trích ra n ký tự của chuổi s kể từ vị trí m.  Ví dụ: MID(“lạc hồng”, 3, 4)  c hồ MS Excel: Các hàm Excel 8 15  Các hàm xử lý chuỗi  Hàm LEN:  Cú pháp: LEN(s)  Công dụng: trả về chiều dài của chuỗi s  Ví dụ: LEN(“lạc hồng”)  8  Hàm TRIM:  Cú pháp: TRIM(s)  Công dụng: cắt bỏ các khoảng trắng thừa  Ví dụ: TRIM(“ lạc hồng “)  lạc hồng MS Excel: Các hàm Excel 16  Các hàm xử lý ngày tháng  Hàm TODAY:  Cú pháp: TODAY()  Công dụng: trả lại ngày tháng năm của hệ thống  Hàm NOW:  Cú pháp: NOW()  Công dụng: trả lại ngày tháng năm và giờ của hệ thống. MS Excel: Các hàm Excel 9 17  Các hàm xử lý ngày tháng  Hàm DAY:  Cú pháp: DAY(biểu thức ngày)  Công dụng: trả về ngày của biểu thức ngày tháng  Ví dụ: DAY(“06/10/2010”)  10  Hàm MONTH:  Cú pháp: MONTH(biểu thức ngày)  Công dụng: trả về tháng của biểu thức ngày tháng  Ví dụ: MONTH(“06/10/2010”)  6 MS Excel: Các hàm Excel 18  Các hàm xử lý ngày tháng  Hàm YEAR:  Cú pháp: YEAR(biểu thức ngày)  Công dụng: trả về phần năm của biểu thức ngày  Ví dụ: YEAR(“06/10/2010”)  2010  Hàm WEEKDAY:  Cú pháp: WEEKDAY(biểu thức ngày, kiểu trả về)  Công dụng: trả về thứ tự của ngày trong tuần  Ví dụ: WEEKDAY(“06/10/2010”, 1) MS Excel: Các hàm Excel 10 19  Các hàm xử lý giờ phút giây  Hàm SECOND, MINUTE, HOUR: trả về giây, phút, giờ của biểu thức giờ  Cú pháp:  SECOND(biểu thức giờ)  MINUTE(biểu thức giờ)  HOUR(biểu thức giờ)  Ví dụ: HOUR(“20:08:35”)  20 MS Excel: Các hàm Excel 20  Các hàm thống kê  Hàm COUNT:  Cú pháp: COUNT(phạm vi)  Công dụng: đếm số ô có chứa dữ liệu kiểu số trong phạm vi  Ví dụ: COUNT(A1:A10)  Hàm COUNTIF:  Cú pháp: COUNTIF(phạm vi, điều kiện)  Công dụng: đếm số ô thỏa mãn điều kiệu trong phạm vi  Ví dụ: COUNTIF(A1:A10,”>5”) MS Excel: Các hàm Excel 11 21  Các hàm thống kê  Hàm MAX: trả về giá trị lớn nhất trong phạm vi  Cú pháp: MAX(phạm vi)  Ví dụ: MAX(A1:A10)  Hàm MIN: trả về giá trị nhỏ nhất trong phạm vi  Cú pháp: MIN(phạm vi)  Ví dụ: MIN(A1:A10)  Hàm AVERAGE: trả về giá trị trung bình cộng của các ô trong phạm vi  Cú pháp: AVERAGE(A1:A10) MS Excel: Các hàm Excel 22  Các hàm thống kê  Hàm SUM:  Cú pháp: SUM(phạm vi)  Công dụng: trả về giá trị tổng của các ô trong phạm vi  Ví dụ: SUM(A1:A10)  Hàm SUMIF:  Cú pháp: SUMIF(phạm vi điều kiện, điều kiện, phạm vi tính tổng)  Công dụng: tính tổng có điều kiện  Ví dụ: SUMIF(A1:A10,”>5”, B1:B10) MS Excel: Các hàm Excel 12 23  Các hàm thống kê  Hàm RANK:  Cú pháp: RANK(x, phạm vi, n)  Công dụng: xếp thứ tự cho giá trị x trong phạm vi  Ví dụ: RANK(A1, $A$1:$A$10, 0) * Nếu n = 0 hoặc bỏ qua tham số này thì thứ tự sẽ được sắp từ lớn đến bé (có nghĩa là lớn xếp thứ 1) * Nếu n = 1 thì thứ tự sẽ được sắp từ bé đến lớn (có nghĩa là bé xếp thứ 1) MS Excel: Các hàm Excel 24  Các hàm Logic  Hàm IF  Cú pháp: IF(điều kiện, biểu thức 1, biểu thức 2)  Công dụng: Hàm trả về biểu thức 1 nếu điều kiện đúng; trả về biểu thức 2 nếu điều kiện sai. Biểu thức ở đây có thể là 1 giá trị, 1 ô hoặc 1 hàm khác.  Ví dụ: IF(A1>=B1, “A lớn hơn hoặc bằng B”, “B lớn hơn A”) IF(A10 >= 5, “Đạt”, “Trượt”) IF(C2 = “GĐ”, “Giám đốc”, IF(C2 = “TP”, “Trưởng phòng”, “Nhân viên”)) MS Excel: Các hàm Excel 13 25  Các hàm Logic  IF(BT_logic, biểu thức 1, biểu thức 2)  BT_logic: BT trả về kết quả TRUE hoặc FALSE. BT_logic thường có các phép so sánh  Biểu thức 1, 2: có thể là 1 số, 1 chuỗi, 1 biểu thức, 1 hàm IF khác, … TRUE FALSE MS Excel: Các hàm Excel 26  Các hàm Logic  Hàm AND  Cú pháp: AND(biểu thức đk1, biểu thức đk2, …)  Công dụng: hàm trả về giá trị TRUE nếu tất cả các biểu thức điều kiện đều đúng, các trường hợp khác là FALSE.  Ví dụ: IF(AND(A2>=5, B2>=5), “Đạt”, “Trượt”) IF(AND(A1>=B1, A1>=C1), “A lớn nhất”, IF(AND(B1>=A1, B1>=C1), ”B lớn nhất”, “C lớn nhất”)) MS Excel: Các hàm Excel 14 27  Các hàm Logic  Hàm OR  Cú pháp: OR(biểu thức đk 1, biểu thức đk 2, …)  Công dụng: hàm trả về kết quả là FALSE nếu tất cả các biểu thức điều kiện đều sai, các trường hợp khác là TRUE.  Ví dụ: IF(OR(A1>=5, B1>=5), “Đạt”, “Trượt”) IF(OR(B1=“Nữ”, C1=1985), D1+0.5, D1)  Hàm NOT  Cú pháp: NOT(biểu thức logic)  Công dụng: hàm trả về giá trị phủ định của biểu thức logic MS Excel: Các hàm Excel 28  Các hàm tra cứu dữ liệu  Hàm HLOOKUP  Cú pháp: HLOOKUP(n, t, m, k)  n là giá trị cần tra cứu trong bảng t  t là bảng (phạm vi) dùng để tra cứu  m là dòng sẽ trả về nếu tìm được n trong t  k là hình thức tra cứu  = 0 là tìm chính xác  0 là tìm gần đúng  Công dụng: tiến hành tìm kiếm giá trị n trên dòng đầu tiên của bảng t và lấy ra giá trị tương ứng trên dòng m của bảng t MS Excel: Các hàm Excel 15 29  Các hàm tra cứu dữ liệu  Hàm HLOOKUP  Ví dụ: Dựa vào ký tự đầu tiên của mãPB và bảng phụ đề để điền vào cột tên. MS Excel: Các hàm Excel 30  Các hàm tra cứu dữ liệu  Hàm VLOOKUP  Cú pháp: VLOOKUP(n, t, m, k)  n là giá trị cần tra cứu trong bảng t  t là bảng (phạm vi) dùng để tra cứu  m là cột sẽ trả về nếu tìm được n trong t  k là hình thức tra cứu  = 0 là tìm chính xác  0 là tìm gần đúng  Công dụng: tiến hành tìm kiếm giá trị n trong cột đầu tiên của bảng t và lấy ra giá trị tương ứng trên cột m của bảng t MS Excel: Các hàm Excel 16 31  Các hàm tra cứu dữ liệu  Hàm VLOOKUP  Ví dụ: MS Excel: Các hàm Excel 32  Các hàm tra cứu dữ liệu  Hàm VLOOKUP – HLOOKUP: VLOOKUP HLOOKUP MS Excel: Các hàm Excel