Một số hàm thông dụng trong Excel

Sử dụng phần mềm bảng tính điện tử Một số hàm thông dụng trong Excel*1Ch5c. Công thức và một số hàm thông dụng trong ExcelCông thức và một số hàm thông dụngNhắc lại khái niệm công thứcKhái niệm hàmNhập công thức và hàmMột số hàm thông dụngBài tập thực hành số 2Date2Ch5c. Công thức và một số hàm thông dụng trong Excel1. Công thức (nhắc lại)Công thứcBắt đầu bởi dấu “=“Gồm:Địa chỉ, hằng, miền,Toán tửHàmVD: = A1+A2-B2= SIN(A1) + COS(B2)= LN(A5)Date3Ch5c. Công thức và một số hàm thông dụng trong Excel2. Khái niệm hàmCác công thức tính toán được xây dựng trước.Phục vụ các tính toán thông dụng.Cú pháp:Tên hàm (danh sách đối số)Đối số được phân cách bởi dấu phẩy hoặc chấm phẩyVí dụ: =rank(x,range,order)Đối số có thể là giá trị, địa chỉ, hằng,Date4Ch5c. Công thức và một số hàm thông dụng trong ExcelCác loại hàmToán học và lượng giác (Math and Trigonometry)Thống kê (Statistical)Tài chính (Financial)Tra cứu và tham chiếu (Lookup and Reference)Văn bản (Text)Thời gian (Date and Time)Lo-gic (Logical)Cơ sở dữ liệu (Database)Thông tin (Information)Kỹ thuật (Engineering)Date5Ch5c. Công thức và một số hàm thông dụng trong ExcelVí dụToán học và lượng giácABS(X): trị tuyệt đốiSIN(X), COS(X)LN(X)Thống kêAVERAGE(miền): tính trung bìnhThời gianNOW(): thời điểm hiện tạiDATE(y,m,d)Date6Ch5c. Công thức và một số hàm thông dụng trong Excel3. Nhập công thức và hàmNhập trực tiếp vào ôSử dụng thanh công thứcKích chuột vào biểu tượng fx để mở hộp thoại chọn hàm.Select a category: loại hàm.Select a function: chọn hàm.Date7Ch5c. Công thức và một số hàm thông dụng trong Excel4. Một số hàm thông dụngMột số hàm toán học và lượng giácMột số hàm thống kêMột số hàm xử lý văn bảnMột số hàm xử lý thời gianMột số hàm tra cứu và tham chiếuMột số hàm LogicDate8Ch5c. Công thức và một số hàm thông dụng trong Excel4.1. Một số hàm toán học và lượng giác {1}ABS(X)Giá trị tuyệt đối của XABS(4) = ABS(-4) = 4ABS(-4.5) = 4.5INT(X)Làm tròn “dưới” tới một số nguyên gần nhấtINT(-4.45) = -5INT( 4.55) = 4CEILING (X,N)Trả về số nhỏ nhất ≥ X và chia hết cho NN ở đây có thể coi là sai sốCEILING (4.27, 0.1) = 4.3FLOOR (X,N)Trả về số lớn nhất ≤ X và chia hết cho NFLOOR (4.27, 0.1) = 4.2Date9Ch5c. Công thức và một số hàm thông dụng trong Excel4.1. Một số hàm toán học và lượng giác {2}ROUND(X,N)Làm tròn XN - số chữ số sau dấu phảy “.”ROUND(4.27, 1) = 4.3ROUND(-4.27, 0) = - 4ROUND(16.27, -1) = 20TRUNC(X, [N])Phần nguyên của XN - số chữ số sau dấu phảy “.”TRUNC(-4.45)= TRUNC(-4.45, 0) = - 4TRUNC(11.276, 2) = 11.27TRUNC(16.276, -1) = 10Date10Ch5c. Công thức và một số hàm thông dụng trong Excel4.1. Một số hàm toán học và lượng giác {3}COS(X)COSIN của X (radian)ACOS(X)ARCCOS của XSIN(X)SIN của XTAN(X)TANG của XLOG10(X)Logarit cơ số 10 của XLN(X)Logarit Neper của XPI()3.14159RANDIANS (độ)Chuyển từ đơn vị độ sang đơn vị RadianDEGREES(radian)Chuyển từ Radian sang độDate11Ch5c. Công thức và một số hàm thông dụng trong Excel4.1. Một số hàm toán học và lượng giác {4}EXP(X)eXSQRT(X)Căn bậc 2 của XMOD(X,Y)X mod YRAND()Trả về số ngẫu nhiên trong khoảng (0,1)QUOTIENT(X,Y)X/YPhải lựa chọnAnalysis Toolpaktrong Tools Add-insDate12Ch5c. Công thức và một số hàm thông dụng trong Excel4.1. Một số hàm toán học và lượng giác {5}SUM(X1,X2,)Tổng dãy số X1,X2,SUM(miền)Tổng các số trong miềnVí dụ:SUM(A1:A9)SUM(B2..B15)Date13Ch5c. Công thức và một số hàm thông dụng trong Excel4.1. Một số hàm toán học và lượng giác {6}SUMIF(miền kiểm tra, điều kiện, miền tổng)Tính tổng các phần tử trong miền tổng với điều kiện phần tử tương ứng trong miền kiểm tra thoả mãn điều kiệnMiền kiểm tra điều kiệnMiền tính tổngVí dụ:B6: Số lượng camDùng công thức:SUMIF(A1:A5, “Cam”, B1:B5)Date14Ch5c. Công thức và một số hàm thông dụng trong Excel4.2. Một số hàm thống kê {1}COUNT(X1,X2,) hay COUNT (miền)Đếm số lượng giá trị số trong dãy, miềnCOUNT(A1:A5): đếm số ô có dữ liệu là sốCOUNTA (X1,X2,) hay COUNTA(miền)Đếm số ô có chứa dữ liệu trong miềnCOUNTA(A1:A5): số ô chứa dữ liệu trong A1:A5COUNTIF (X1,X2,, điều_kiện) hay COUNTIF(miền, điều_kiện)Đếm số lượng giá trị thoả mãn điều kiệnCOUNTIF(C3:C11,">=5"): Số ô có giá trị ≥5 trong C3:C11COUNTIF(C3..C11,”5”): Số ô có giá trị = 5 trong C3..C11Date15Ch5c. Công thức và một số hàm thông dụng trong Excel4.2. Một số hàm thống kê {2}AVERAGE(X1,X2,)Trung bình cộng của X1,X2,Có thể thay X1,X2, bởi địa chỉ hay tên miềnAVERAGE(A1:A5)MAX(X1,X2,)Giá trị lớn nhấtMIN(X1,X2,)Giá trị nhỏ nhấtRANK(X,miền,thứ_tự)Cho thứ hạng của X trong miềnthứ_tự = 0 hoặc khuyết thì sắp xếp theo chiều giảm dần, khác 0 thì sắp xếp tăng dần.Date16Ch5c. Công thức và một số hàm thông dụng trong ExcelVí dụ thống kê: tổng kết điểmDate17Ch5c. Công thức và một số hàm thông dụng trong Excel4.3. Một số hàm xử lý văn bảnLEFT(s,n)n ký tự trái của s.RIGHT(s,n)n ký tự phải của s.MID(s,m,n)n ký tự, từ vị trí m.TRIM(s)Bỏ dấu cách thừa.LEN(s)Độ dài xâu s.VALUE(s)Chuyển xâu s thành số.TEXT(value, định_dạng):Chuyển thành xâu theo định dạng.Ví dụ:TEXT(“01/01/2004”, ”mmm”) = “Jan”TEXT(1/3,"0.00") = 0.33.LOWER(s): Đổi xâu s thành chữ thường.UPPER(s): Đổi xâu s thành chữ hoa.Date18Ch5c. Công thức và một số hàm thông dụng trong Excel4.4. Một số hàm thời gianNOW()Thời điểm hiện tạiTODAY()Ngày hôm nayDATE(năm, tháng, ngày)Năm = 1900-9999DATE(99,1,1)=01/01/1999DATE(2004,1,1)=01/01/2004DAY(xâu ngày tháng)Lấy giá trị ngàyDAY(“4-Jan”) = 4MONTH(xâu ngày tháng)Lấy giá trị thángMONTH("5/10/2004") = 5YEAR(xâu ngày tháng)Lấy giá trị nămDATEVALUE(xâu ngày)Chuyển xâu sang dữ liệu số biểu diễn ngày thángDATEVALUE("01/01/1900") = 1Date19Ch5c. Công thức và một số hàm thông dụng trong Excel4.5. Một số hàm tra cứu và tham chiếu {1}VLOOKUP(trị_tra_cứu, bảng_tra_cứu, cột_lấy_dữ_liệu, kiểu_tra_cứu)Tra cứu giá trị của ô thuộc cột thứ cột_lấy_dữ_liệu mà giá trị của ô thuộc cột đầu tiên có giá trị bằng trị tra cứu.Kiểu tra cứu quy định cách thức tra cứu:0 (false):So khớpVùng tra cứu không cần sắp xếp1 (true):So gần khớp (tìm giá trị “gần nhất”)Vùng tra cứu phải được sắp xếp sẵnNói chung, vùng tra cứu nên được sắp xếp trước khi sử dụng vlookup để tra cứu.Date20Ch5c. Công thức và một số hàm thông dụng trong ExcelVí dụ sử dụng VLOOKUPVT xác định bởi mã VT.Vật tư nhậpMiền A16:B24Cột thứ 2 (cột B)Báo cáo vật tưMỗi dòng ứng với một vật tư.Cột “NHẬP”: số lượng vật tư nhậpCần phải tra cứu từ A16:B24, sử dụng công thức: 	=VLOOKUP(B9,$D$17:$E$25,2,1)Date21Ch5c. Công thức và một số hàm thông dụng trong Excel4.5. Một số hàm tra cứu và tham chiếu {2}HLOOKUP(trị_tra_cứu, bảng_tra_cứu, hàng_lấy_dữ_liệu, kiểu_tra_cứu)Giống hàm VLOOKUP nhưng dữ liệu được xử lý theo hàngINDEX (miền,hàng,cột)Tham chiếu tới ô có số thứ tự hàng và cột trong miền được truyền vàoVí dụ: INDEX(A2:D9,5,2) sẽ tham chiếu đến hàng thứ 5, cột thứ 2 trong miền A2:D9Date22Ch5c. Công thức và một số hàm thông dụng trong Excel4.6. Một số hàm LogicNOT(X)AND(X1,X2,)OR(X1,X2,)IF(điều_kiện,giá_trị_1,giá_trị_2)Nếu điều kiện đúng, trả về giá trị 1Nếu điều kiện sai, trả về giá trị 2Giá trị 2 có thể là một hàm IF khácVí dụ:IF(B2>5, “Đạt yêu cầu”, “Không đạt”)IF(B2>=8, “Giỏi”, IF(B2<5,”Trượt”, “Đạt yêu cầu”))Date23Ch5c. Công thức và một số hàm thông dụng trong Excel5. Bài tập thực hành 2Bài tập thực hành 2 (tr.11, GT Tin học Excel ứng dụng)Lập bảng điểmAVERAGE: tính TB.SUM: Tính tổng.COUNTIF: Đếm.IF: Xếp loại.VLOOKUP: Tra cứu số trình.RANK: Xếp thứ.Date24Ch5c. Công thức và một số hàm thông dụng trong Excel