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

+27

No comments posted yet

Comments

Slide 1

Tin học đại cương và ứng dụng (Một số hàm thông dụng trong Excel) Phan Trọng Tiến Department of Software Engineering Hanoi University of Agricaltural Office location: 3rd floor, Administrative building Office phone: 8276346, Ext: 132 Website: www.fita.hua.edu.vn/pttien Email:phantien84@gmail.com

Slide 2

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 2 Ch5c. Công thức và một số hàm thông dụng Nhắc lại khái niệm công thức Khái niệm hàm Nhập công thức và hàm Một số hàm thông dụng Bài tập thực hành số 2

Slide 3

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 3 1. Công thức (nhắc lại) Công thức Bắt đầu bởi dấu “=“ Sau đó: Địa chỉ, hằng, miền,… Toán tử Hàm VD: = A1+A2-B2 = SIN(A1) + COS(B2) = LN(A5)

Slide 4

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 4 2. Khái niệm hàm Là các hàm tính toán được xây dựng sẵn. 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 Ví dụ: =rank(x,range,order) Đối số có thể là giá trị, địa chỉ, hằng,…

Slide 5

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 5 Các loại hàm Toá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)

Slide 6

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 6 Ví dụ Toán học và lượng giác ABS(X): trị tuyệt đối SIN(X), COS(X) LN(X) Thống kê AVERAGE(miền): tính trung bình Thời gian NOW(): thời điểm hiện tại DATE(y,m,d)

Slide 7

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 7 3. Nhập công thức và hàm Nhập trực tiếp vào ô Sử dụng thanh công thức Kí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.

Slide 8

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 8 4. Một số hàm thông dụng Một số hàm toán học và lượng giác Một số hàm thống kê Một số hàm xử lý văn bản Một số hàm xử lý thời gian Một số hàm tra cứu và tham chiếu Một số hàm Logic

Slide 9

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 9 4.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 X ABS(4) = ABS(-4) = 4 ABS(-4.5) = 4.5 INT(X) Làm tròn “dưới” tới một số nguyên gần nhất INT(-4.45) = -5 INT( 4.55) = 4 CEILING (X,N) Trả về số nhỏ nhất ≥ X và chia hết cho N N ở đây có thể coi là sai số CEILING (4.27, 0.1) = 4.3 FLOOR (X,N) Trả về số lớn nhất ≤ X và chia hết cho N FLOOR (4.27, 0.1) = 4.2

Slide 10

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 10 4.1. Một số hàm toán học và lượng giác {2} ROUND(X,N) Làm tròn X N - số chữ số sau dấu phảy “.” ROUND(4.27, 1) = 4.3 ROUND(-4.27, 0) = - 4 ROUND(16.27, -1) = 20 TRUNC(X, [N]) Phần nguyên của X N - số chữ số sau dấu phảy “.” TRUNC(-4.45) = TRUNC(-4.45, 0) = - 4 TRUNC(11.276, 2) = 11.27 TRUNC(16.276, -1) = 10

Slide 11

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 11 4.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 X SIN(X) SIN của X TAN(X) TANG của X LOG10(X) Logarit cơ số 10 của X LN(X) Logarit Neper của X PI() 3.14159… RANDIANS (độ) Chuyển từ đơn vị độ sang đơn vị Radian DEGREES(radian) Chuyển từ Radian sang độ

Slide 12

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 12 4.1. Một số hàm toán học và lượng giác {4} EXP(X) eX SQRT(X) Căn bậc 2 của X MOD(X,Y) X mod Y RAND() Trả về số ngẫu nhiên trong khoảng (0,1) QUOTIENT(X,Y) X/Y, trả về phần nguyên phép chia. Phải lựa chọn Analysis Toolpak trong Tools  Add-ins

Slide 13

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 13 4.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ền Ví dụ: SUM(A1:A9) SUM(B2..B15)

Slide 14

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 14 4.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ện Miền kiểm tra điều kiện Miền tính tổng Ví dụ: B6: Số lượng cam Dùng công thức: SUMIF(A1:A5, “Cam”, B1:B5)

Slide 15

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 15 4.1. Một số hàm toán học và lượng giác {7} PRODUCT(X1,[X2],…] X1*X2*… SUMSQ(X1,[X2],…) hoặc SUMQ(miền) Tính ∑xi2 SUMPRODUCT(mảng_x,mảng_y,…) ∑(xi*yi*…) SUMX2MY2(mảng_x, mảng_y) ∑(xi2 - yi2) SUMX2PY2(mảng_x, mảng_y) ∑(xi2 + yi2) SUMXMY2(mảng_x, mảng_y) ∑(xi – yi ) 2

Slide 16

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 16 4.2. Một số hàm thống kê {1} COUNT(X1,X2,…) hay COUNT (miền) Đếm số ô có dữ liệu là số COUNT(A1:A5) COUNTA (X1,X2,…) hay COUNTA(miền) Đếm số ô có chứa dữ liệu trong dãy COUNTA(A1:A5) COUNTIF (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ện COUNTIF(C3:C11,">=5"): Số ô có giá trị ≥5 trong C3:C11 COUNTIF(C3..C11,”5”): Số ô có giá trị = 5 trong C3..C11

Slide 17

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 17 4.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ền AVERAGE(A1:A5) MAX(X1,X2,…) Giá trị lớn nhất của dãy MIN(X1,X2,…) Giá trị nhỏ nhất của dãy RANK(X,miền,[thứ_tự]) Cho thứ hạng của X trong miền thứ_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.

Slide 18

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 18 Ví dụ thống kê: tổng kết điểm

Slide 19

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 19 4.3. Một số hàm xử lý văn bản LEFT(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.

Slide 20

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 20 4.4. Một số hàm thời gian NOW() Thời điểm hiện tại TODAY() Ngày hôm nay DATE(năm, tháng, ngày) Năm = 1900-9999 DATE(99,1,1)=01/01/1999 DATE(2004,1,1)=01/01/2004 DAY(xâu ngày tháng) Lấy giá trị ngày DAY(“4-Jan”) = 4 MONTH(xâu ngày tháng) Lấy giá trị tháng MONTH("5/10/2004") = 5 YEAR(xâu ngày tháng) Lấy giá trị năm DATEVALUE(xâu ngày) Chuyển xâu sang dữ liệu số biểu diễn ngày tháng DATEVALUE("01/01/1900") = 1

Slide 21

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 21 4.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]) Tìm trị_tra_cứu ở cột đầu tiên của bảng_tra_cứu nếu có trị_tra_cứu bằng hoặc giá trị lớn nhất nhỏ hơn trị_tra_cứu thì trả về giá trị ứng với hàng tìm thấy và cột_lấy_dữ_liệu trong bảng_tra_cứu. Kiểu tra cứu quy định cách thức tra cứu: 0 (false): Tìm chính xác Vùng tra cứu không cần sắp xếp 1 (true) hoặc khuyết: Tìm gần chính xác Vùng tra cứu phải được sắp xếp tăng dần Nói chung, vùng tra cứu nên được sắp xếp trước khi sử dụng vlookup để tra cứu.

Slide 22

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 22 Ví dụ sử dụng VlookUp

Slide 23

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 23 Ví dụ sử dụng VLOOKUP VT xác định bởi mã VT. Vật tư nhập Miền A16:B24 Cộ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ập Cần phải tra cứu từ A16:B24, sử dụng công thức: =VLOOKUP(B9,$D$17:$E$25,2,1)

Slide 24

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 24 4.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àng INDEX (miền,hàng,cột) Trả về giá trị tham chiếu ứng với hàng và cột trong miền được truyền vào Ví dụ: INDEX(A2:D9,5,2) sẽ tham chiếu đến hàng thứ 5, cột thứ 2 trong miền A2:D9

Slide 25

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 25 4.6. Một số hàm Logic NOT(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ị 1 Nếu điều kiện sai, trả về giá trị 2 Giá trị 2 có thể là một hàm IF khác Ví 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”))

Slide 26

4/25/2010 Ch5c. Công thức và một số hàm thông dụng trong Excel 26 5. Bài tập thực hành Bài tập thực hành 8 (tr.151, GT Nhập môn tin học) Lập bảng điểm AVERAGE: 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ứ.

URL: