Bài giảng Tin học văn phòng: Bài 9&10 của Bùi Thị Thu Cúc

TIN HỌC VĂN PHÒNG Bài 9 & 10: HÀM TRONG EXCEL

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

1. Các loại địa chỉ trong công thức

(cid:73) 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

(cid:73) 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

Điều gì xảy ra nếu ô F 2 được viết là = E 2/E 8 ?

Các loại địa chỉ trong công thức

(cid:73) Các loại địa chỉ E 8 • Tương đối : • Cố định cột : $E 8 • Cố định hàng : E $8 $E $8 • Tuyệt đối :

(cid:73) Tham chiếu đến địa chỉ Sheet khác :

!<địa chỉ ô> Ví dụ : ’Baitap1’ !E$8

(cid:73) Tham chiếu đến địa chỉ Workbook khác :

[] !<địa chỉ ô> Ví dụ : [Bai9.xlsx]Sheet2 !E8

2. Khái niệm hàm và quy tắc sử dụng

(cid:73) 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

(cid:73) Cú pháp : Tên hàm(Các đối số) (cid:73) Tên hàm viết liền, không phân biệt chữ hoa, chữ thường (cid:73) Đối số có thể là giá trị, địa chỉ một ô hoặc nhiều ô (cid:73) 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

(cid:73) Không có đối số vẫn phải viết cặp ngoặc, ví dụ TODAY() (cid:73) Hàm có thể lồng nhau, ví dụ SQRT(SUM(B1 : B9))

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

(cid:73) Cách 1 : gõ dấu = rồi gõ trực tiếp tên hàm vào ô (cid:73) Cách 2 : vào ribbon Formulas → Function Library, chọn

hàm phù hợp trên menu

(cid:73) Cách 3 : vào ribbon Formulas → Function Library, ấn

Insert Function, chọn hàm và nhập đối số

3. Các hàm thời gian

(cid:73) 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

(cid:73) 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

• DAY("4/1/2017") trả về 1 • MONTH("4/1/2017") trả về 4 • YEAR("4/1/2017") trả về 2017

(cid:73) DAY/MONTH/YEAR(serial_number) : trả về ngày/tháng/năm trong chuỗi serial_number

Các hàm thời gian

• TIME(18,7,30) trả về 18 :07 :30 hoặc 6 :07 PM

(cid:73) TIME(hour,minute,second) : trả về thời gian dạng số

(cid:73) TODAY() : trả về ngày hiện tại

(cid:73) 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

Các hàm thời gian

(cid:73) 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

(cid:73) 1 : chủ nhật là 1 đến thứ 7 là 7 (cid:73) 2 : thứ 2 là 1 đến chủ nhật là 7 (cid:73) 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

4. Các hàm văn bản

(cid:73) 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

(cid:73) LOWER/UPPER(text) : chuyển text thành chữ

thường/hoa

• LOWER("EXCEL") trả về "excel"

(cid:73) 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"

Các hàm văn bản

(cid:73) 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

(cid:73) SEARCH(text1,text) : tương tự hàm FIND nhưng không

biệt chữ hoa chữ thường

• LEN("Hoa cỏ may") trả về 10

(cid:73) LEN(text) : trả về độ dài (số ký tự) của text

Các hàm văn bản

(cid:73) 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"

(cid:73) 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ỏ "

(cid:73) 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"

• TRIM(" Hoa cỏ may ") trả về "Hoa cỏ may"

(cid:73) TRIM(text) : cắt bỏ các ký tự trống vô nghĩa của text

5. Các hàm toán học

(cid:73) ABS(x) : tính giá trị tuyệt đối (cid:73) 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

(cid:73) SQRT(x) : tính căn bậc 2 của x với x > 0 (cid:73) COS(x), SIN(x), TAN(x) : các hàm lượng giác, x tính

bằng radian

(cid:73) PI() : trả về số π 3.141592654 (cid:73) DEGREES(x) : đổi radian sang độ (cid:73) RAND() : trả về số ngẫu nhiên giữa 0 và 1

Các hàm toán học

(cid:73) SUM(n1,n2,. . .) : tính tổng n1 + n2 + . . . (cid:73) PRODUCT(n1,n2,. . .) : tính tích n1 ∗ n2 ∗ . . . (cid:73) FACT(n) : tính n! = 1 ∗ 2 ∗ · · · ∗ n (cid:73) POWER(a,b) : tính ab (cid:73) EXP(x) : tính ex (cid:73) LOG(a,b) : trả về logb a, nếu không có b thì mặc định

b = 10

(cid:73) MOD(a,b) : trả về số dư trong phép chia hai số nguyên

a/b

Các hàm toán học

• TRUNC(3.24) trả về 3, TRUNC(-3.24) trả về -3

(cid:73) TRUNC(x) : cắt bỏ phần thập phân, chỉ lấy phần nguyên

• INT(3.24) trả về 3, INT(-3.24) trả về -4

(cid:73) INT(x) : trả về số nguyên lớn nhất không vượt quá x

(cid:73) 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

SUMIF(range,criteria,[sum_range])

• 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ộng

(cid:73) tính tổng các giá trị trong phạm vi đáp ứng tiêu chí

SUMIFS(sum_range,criteria_range1,criteria1, [criteria_range2,criteria2],. . .)

(cid:73) tính tổng các giá trị trong phạm vi đáp ứng nhiều tiêu chí

6. Các hàm thống kê

(cid:73) 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

(cid:73) LARGE/SMALL(array,k) : trả về phần tử lớn/nhỏ thứ k

trong vùng array

• 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

(cid:73) 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ác hàm thống kê

(cid:73) 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 (cid:73) MODE(n1,n2,. . .) : trả về giá trị hay gặp nhất trong vùng (cid:73) COUNT(range) : đếm số ô chứa dữ liệu số trong một vùng (cid:73) COUNTA(range) : đếm số ô không rỗng trong một vùng

COUNTIF(range,criteria)

(cid:73) đếm số ô trong phạm vi đáp ứng một tiêu chí nào đó

7. Các hàm logic

(cid:73) AND(logical1,logical2,. . .) : trả về True nếu tất cả các đối

số là True, ngược lại trả về False

(cid:73) OR(logical1,logical2,. . .) : trả về False nếu tất cả các đối

số là False, ngược lại trả về True

(cid:73) NOT(logical) : phép phủ định

(cid:73) 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

(cid:73) 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"

8. Các hàm tìm kiếm

• 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 (cid:73) D = True hoặc bỏ qua D : tìm "gần chính xác" (cid:73) 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"

(cid:73) cột đầu tiên của B phải xếp thứ tự tăng dần (cid:73) 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

(cid:73) 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