S dng ton t Hm trong Excel 2010

Sử dụng toán tử & Hàm trong Excel 2010

Tạo lập công thức Tạo công thức số học cơ bản Gõ dấu “=” trước công thức, ví dụ: =E 1+F 1 Nhấn Enter để kết thúc Nội dung công thức được hiển thị trên thanh Formula bar Sử dụng cặp dấu “( )” để thay đổi độ ưu tiên của các phép toán trong công thức 2

Toán tử số học Các toán tử số học trong Excel Cộng: + Trừ: Nhân: * Chia: / Mũ: ^ 3

Độ ưu tiên các toán tử số học �Toán tử ^ lũy thừa có ưu tiên cao nhất �Toán tử * nhân và / chia có độ ưu tiên bằng nhau và ưu tiên tiếp theo �Toán tử + cộng và – trừ có độ ưu tiên bằng nhau và ưu tiên thấp nhất �Trong biểu thức tính toán độ ưu tiên tính toán cao nhất cho các phép toán có cặp ngoặc trong cùng lần lượt tính toán từ cặp ngoặc trong cùng lần lượt ra ngoài cùng nếu cùng độ ưu tiên thì tính toán từ trái qua phải. 4

Toán tử so sánh Các toán tử so sánh Lớn hơn: > Nhỏ hơn: < Lớn hơn hoặc bằng: >= Nhỏ hơn hoặc bằng: <= Khác (không bằng): <> Các toán tử so sánh cho kết quả là TRUE hay FALSE 5

Giá trị luận lý là : TRUE hay FALSE không tương tác như toán tử mà tương tác như toán tử số học Các giá trị luận lý TRUE và FALSE khi tương tác các toán tử số học thì giá trị TRUE 1 và FALSE 0 Toán tử nối chuỗi Excel dùng ký hiệu & dùng làm toán tử nối chuỗi 6

Kết quả khi thực hiện phép toán với toán tử số học Khi thực hiện toán tử số học cho các con số thì kết quả thu được là con số. Khi thực hiện các toán tử số học với giá trị luận lý cho kết quả là con số Khi thực hiện toán tử & với dữ liệu nào thì kết quả sẽ là chuỗi Khi thực hiện các toán tử số học với toán tử so sánh cho kết quả là giá trị luận lý TRUE hay FALSE 7

Dữ liệu trong excel Trong excel có các dạng dữ liệu chính là : Số gồm có các loại dữ liệu : Ngày tháng năm, giờ phút giây, số nguyên tự nhiên, số nguyên , số hữu tỷ, số thực. Được hiển thị nhiều cách khác nhau theo yêu cầu của người sử dụng. Chúng ta có thể chuyển các định dạng khác nhau cho cùng một số liệu theo yêu cầu sử dụng. Mặc định khi nhập liệu sẽ canh bên phải Chuỗi là các ký tự hay ký tự kết hợp với số mặc định khi nhập liệu sẽ canh bên trái. Dữ liệu luận lý là TRUE hay FALSE 8

Tạo lập công thức �Nhận biết và sửa lỗi �####: không đủ độ rộng của ô để hiển thị, �#VALUE!: dữ liệu không đúng theo yêu cầu của công thức �#DIV/0!: chia cho giá trị 0 �#NAME? : không xác định được ký tự trong công thức �#N/A: không có dữ liệu để tính toán �#NUM!: dữ liệu không đúng kiểu số Sửa lại cho phù hợp với yêu cầu 9

Địa chỉ tương đối, tuyệt đối Giới thiệu các loại địa chỉ Địa chỉ tham chiếu tuyệt đối Gọi tắt là địa chỉ tuyệt đối Chỉ đến một ô hay các ô cụ thể Có thêm ký tự $ trước phần địa chỉ cột hoặc dòng Không thay đổi khi sao chép hoặc di chuyển công thức 10

Địa chỉ tương đối, tuyệt đối Giới thiệu các loại địa chỉ Địa chỉ tham chiếu tương đối Gọi tắt là địa chỉ tương đối Chỉ đến một ô hay các ô trong sự so sánh với vị trí nào đó Thay đổi theo vị trí ô mà ta copy công thức tới 11

Địa chỉ tương đối, tuyệt đối Giới thiệu các loại địa chỉ Địa chỉ tham chiếu hỗn hợp Có đối một thành phần là tuyệt đối, thành phần còn lại là tương Thay đổi từ các loại địa chỉ bằng cách ấn phím F 4 hoặc cho ký tự $ trực tiếp vào phần địa chỉ mong muốn 12

Sao chép công thức bằng chuột Nhắp chọn ô chứa công thức muốn sao chép Đặt con trỏ chuột vào góc phải dưới của ô Biểu tượng chuột chuyển thành hình dấu thập mầu đen nét đơn Nhấn phím trái, kéo và di chuyển chuột theo dòng hoặc theo cột đến các ô cần sao chép Sao chép công thức bằng cách tổ hợp phím Chuyển đến ô sao chép bằng bàn phím hay chuột chọn tổ hợp phím Ctrl+C di chuyển đến vị trí ô hay khối cần sao chép ấn tổ hợp phím Ctrl+V 13

Sao chép công thức bằng chuột phải Chọn ô muốn sao chép ấn phải chuột hiển thi menu Chọn menu copy. • Di chuyển đến ô cần sao chép công thức • Ấn phải chuột và chọn copy 14

Sao chép công thức Sao chép công thứ theo icon trên thanh ribbon Chọn ô cần sao chép công thức di chuyển chuột đến vị trí có icon copy trên ribbon Home Di chuyển chuột đến ô hay khối cần sao chép chọn Paste. Ngoài ra ta cũng có thể tổng hợp cách thực hiện nêu trên. 15

Thao tác với hàm Nhập hàm Cách 1: gõ trực tiếp vào ô theo dạng “=<Tên hàm>(đối số 1, đối số 2, …, đối số n)” Cách 2: Chuyển con trỏ ô về ô muốn nhập công thức Trên ribbon Formulas Chọn loại hàm trong mục Function Category Chọn hàm trong mục Function name Nhấn nút OK Nhập các đối số cần thiết Nhấn nút OK để hoàn tất 17

Thao tác với hàm Các hàm toán học và lượng giác Trên thanh ribbon Formulas chọn biểu tượng Math & Trig. Xem kết quả hiển thị trên thanh trạng thái Thay đổi lại hàm tính toán nhanh: Chọn Math & Trig trên thanh ribbon Formulas Chọn một hàm khác trong danh sách xuất hiện 18

Hàm số học : Hàm. ABS Hàm ABS Cú pháp : =ABS(Number) Công dụng : trả về trị tuyệt đối của (Number). Ví dụ : ABS(-15. 2) trả về giá trị 15. 2 19

Hàm SQRT Cú pháp : =SQRT(Number) Công dụng Ví dụ : trả về căn bậc hai của Number. : =SQRT(49) trả về giá trị 7. 20

Hàm INT; MOD Hàm INT Cú pháp : =INT(Number) Công dụng Ví dụ : trả về phần nguyên của (Number). : INT(5. 9) trả về giá trị 5. Hàm MOD Cú pháp : =MOD(Number, divisor) Công dụng: trả về giá trị phần dư của Number chia cho số bị chia divisor. Ví dụ : =MOD(45, 8) trả về giá trị 5 21

Hàm Round Hàm ROUND Cú pháp: =ROUND(number, num_digits) Công dụng: Hàm làm tròn number với độ chính xác đến con số num_digits. Nếu số kề phải số làm tròn >=5 thì cộng 1 đơn vị cho số làm tròn. Nếu num_digits > 0 hàm làm tròn phần thập phân, Nếu num_digits = 0 hàm làm tròn hàng đơn vị, Nếu num_digits < 0 hàm làm tròn phần nguyên. Ví dụ: =ROUND(123. 456789, 3) 123. 457 22

Hàm ROUNDUP, ROUNDDOWN Hàm ROUNDUP Tương tự hàm Round nhưng làm tròn lên VD: =roundup(9. 23, 1) = 9. 3 Hàm ROUNDDOWN Tương tự hàm Round nhưng làm tròn xuống VD: =rounddown(9. 27, 1) = 9. 2 23

Hàm MDETERM(array) dùng để tính định thức ma trận array. Array là ma trận vuông. Nếu nhập trực tiếp thì mỗi số hạng ma trận cách nhau dấu phảy “ , ” và mỗi dòng ma trận cách nhau dấu ; số phần tử trong dòng bằng số dòng ma trận. 24

Hàm MINVERSE(array) dùng để tính ma trận nghịch đảo của một ma trận vuông. Muốn tính được ma trận nghịch đảo thì định thức ma trận cần tím ma trận nghịch đảo phải khác không. Ma trận A và ma trận nghịch đảo A-1 khi nhân nhau ra ma trận đơn vị. Muốn tính ma trận nghịch đảo phải lựa khối bằng ma trận cần tính nghịch đảo rồi bàn hành công thức. Ấn tổ hợp phím Ctrl+Shift+Enter 25

Hàm MINVERSE(array) Ma trận cần tính ma trận đảo Định thức ma trận Ví dụ: Cho ma trận : Ma trận trên có định thức ma trận = 249 vậy có ma trận đảo Tìm ma trận đảo: Sau khi chọn như sau và gõ tổ hợp phím Ctrl+Shift+Enter và nhận kết quả Chọn khối tính ma trận đảo Gõ tổ hợp phím Ctrl+Shift+Ente r và nhận kết quả 26

MMULT(array 1, array 2) Hàm MMULT(array 1, array 2) dùng để tính tích hai ma trận. Muốn tính tích hai ma trận thì cột ma trận thứ nhất phải bằng dòng ma trận thứ hai. Ma trận tích có số dòng bằng dòng ma trận thứ nhất và có cột bằng cột ma trận thứ hai. Phải lựa khối bằng dòng ma trận thứ nhất và bằng cột ma trận hai. Ban hành công thức và ấn tổ hợp phím Ctrl+Shift+Enter. 27

MMULT(array 1, array 2) Ví dụ: Tính tích hai ma trận theo hình Ấn tổ hợp phím Ctrl+Shift+Enter và nhận kết quả 28

Hàm Transpose(Array) dùng để chuyển vị ma trận đổi dòng thành cột và đổi cột thành dòng. Sau khi chọn đúng khối ô dùng tổ hợp phím Ctrl+Shift+Enter. Sẽ cho ra kết quả. 29

Hàm FACT(number) dùng để tính giai thừa của dối số number Ví dụ : =FACT(5)=120 vì FACT(5)=1*2*3*4*5= 120 30

Hàm COMBIN(number, number_chosen) tìm số tổ hợp có số hạn number_chosen được chọn trong tập có number số. Hàm COMBIN được tính từ công thức. 31

Hàm xử lý chuỗi : Hàm LEFT Cú pháp: =LEFT(Text, [num_chars]) Công dụng : trả về một chuỗi con gồm num_chars ký tự bên trái của text. Ví dụ : =LEFT(“VIETHAN”, 4) trả về chuỗi “VIET” 32

Hàm RIGHT Cú pháp: =RIGHT(Text, [num_chars]) Công dụng : trả về một chuỗi con gồm num_chars ký tự bên phải của text. Ví dụ : =Right(“VIETHAN”, 3) trả về chuỗi “HAN” 33

Hàm MID Cú pháp: =MID(Text, start_num, num_chars) Công dụng: trả về num_char ký tự của text bắt đầu từ vị trí start_num Ví dụ : =MID(“VIETHANIT”, 5, 3) trả về chuỗi “HAN” 34

Hàm UPPER, LOWER Hàm UPPER Cú pháp : =UPPER(Text) Công dụng: trả về chuỗi Text đã được đổi sang dạng chữ in. Ví dụ : =UPPER(“Vie. THa. N”) trả về chuỗi “VIETHAN” Hàm LOWER() Cú pháp : =LOWER(Text) Công dụng: trả về chuỗi Text đã được đổi sang dạng chữ thường. Ví dụ : =Lower(“Vie. THa. N”, 4) trả về chuỗi “viethan” 35

Hàm PROPER Cú pháp : =PROPER(Text) Công dụng : trả về chuỗi Text, trong đó kí tự đầu tiên mỗi từ đã được đổi sang dạng chữ in. Ví dụ : =Proper(“NGUYễN văn AN”) trả về chuỗi “Nguyễn Văn An” 36

Hàm TRIM Cú pháp : =TRIM(Text) Công dụng : trả về chuỗi Text, trong đó kí tự trắng ở đầu và cuối chuỗi đã được cắt bỏ. đồng thời loại bỏ đi những khoảng trắng thừa giữa các từ (khoảng cách giữ hai từ nhiều hơn một kí tự trắng) Ví dụ : =Trim(“ HỒ CHÍ MINH ”) trả về chuỗi “HỒ CHÍ MINH” 37

Hàm LEN Cú pháp : =LEN(Text) Công dụng Ví dụ : Trả về độ dài của chuỗi đã cho. : =LEN(“HỒ CHÍ MINH”) trả về số 11 38

Hàm FIND tìm giá trị chuỗi của tham biến (find_text) trong chuỗi được tìm là tham biến(within_text), trả về vị trí thứ tự mà tham biến (find_text) trùng khớp trong tham biến (within_text). Trong hàm FIND giá trị chuỗi tìm phân biệt ký tự in hoa và thường và không sử dụng ký tự đại Hàm FIND dò tìm trong chuỗi (within_text) bắt đầu từ ký tự thứ của tham biến star_num 39

Cú pháp FIND(find_text, within_text, start_num) FINDB(find_text, within_text, start_num) Find_text là tham biến chuỗi dùng để tìm. Within_text tham biến mà chuỗi Find_text muốn tìm. Start_num xác định vị trí bắt đầu tìm trong chuỗi tìm (within_text ). Nếu tham biến này là 1 hay bỏ đi thì hàm FIND bắt đầu tìm tại ký tự đầu tiên 40

Chú ý �Nếu chuỗi tìm (find_text) là "" (chuỗi rỗng), hàm FIND cho kết quả là 1 �Chuỗi (Find_text) không được chứa ký tự đại diện. �Nếu chuỗi tìm (find_text) không xuất hiện trong chuỗi (within_text), hàm FIND trả về gía trị lỗi #VALUE! �Nếu tham biến (start_num ) là không dương hàm FIND trả về gía trị lỗi #VALUE!. �Nếu giá trị (start_num)lớn hơn chiều dài chuỗi tìm thì hàm FIND trả về gía trị lỗi #VALUE!. 41

Hàm Search(find_text, within_text, start_num) Hàm SEARCH(find_text, within_text, start_num) Giá trị Find_text là chuỗi dùng để dò. Ta có thể dùng ký tự đại diện “*” hay “? ” để thay thế giá trị tìm kiếm. Dấu * đại diện nhiều ký tự, dấu ? Đại diện cho một ký tự duy nhất. Within_text là chuỗi mà Find_text dò tìm. Start_num là số thứ tự mà Find_text bắt đầu tìm trong chuỗi within_text Hàm Search cho kết quả là vị trí thứ tự mà chuỗi Find_text trùng khớp với chuỗi Within_text. . 42

Chú ý Hàm SEARCH không phân biệt chữ thường hay in hoa. SEARCH giống như hàm FIND ngoại trừ phân biệt in hoa và wild card. � Nếu chuỗi tìm (find_text) là "" (chuỗi rỗng), hàm SEARCH cho kết quả là 1 � Nếu chuỗi tìm (find_text) không xuất hiện trong chuỗi (within_text), hàm SEARCH trả về gía trị lỗi #VALUE! � Nếu tham biến (start_num ) là không dương hàm SEARCH trả về gía trị lỗi #VALUE!. � Nếu giá trị (start_num)lớn hơn chiều dài chuỗi tìm thì hàm SEARCH trả về gía trị lỗi #VALUE!. 43

Hàm VALUE Cú pháp : =VALUE(text) Công dụng Ví dụ : Chuyển chuỗi text sang dữ liệu kiểu số : =value(“ 2006”) trả về giá trị số 2006. 44

Hàm thời gian Hàm TODAY Cú pháp: = TODAY() Công dụng: Trả về ngày hiện hành của Hệ thống. Ví dụ: =TODAY() “ 01/01/2008” Hàm NOW Cú pháp: =NOW() Công dụng: Trả về ngày và giờ hiện hành của Hệ thống. Ví dụ: =NOW() “ 01/01/2008 11: 59” 45

Hàm WEEKDAY Cú pháp: = WEEKDAY(serial_number, [return_type]) Công dụng: Trả về số thứ tự của ngày serial_number trong tuần. Nếu Return_type là: 1 hoặc bỏ trống: Số 1(Chủ Nhật) cho đến số 7 (Thứ 7). 2 : Số 1(Thứ 2) cho đến số 7(Chủ Nhật). 3 : Số 0 (Thứ 2) cho đến số 6(Chủ Nhật). 46

Hàm DAY Cú pháp : =DAY(serial_number ). Công dụng : trả về giá trị là ngày trong chuỗi serial_number. Ngày được trả về là số nguyên từ 1 ->31 Ví dụ: =DAY(“ 01/04/2009”) --> kết quả là 1 47

Hàm MONTH Cú pháp : =MONTH(serial_number ). Công dụng : trả về giá trị là tháng trong chuỗi serial_number. Ngày được trả về là số nguyên từ 1 ->12 Ví dụ: =Month(“ 01/04/2005”) --> kết quả là 4 48

Hàm YEAR Cú pháp : =YEAR(serial_number ). Công dụng : trả về giá trị là năm trong chuỗi serial_number. Ví dụ: =Month(“ 01/04/2005”) --> kết quả là 2005 49

Hàm HOUR, MINUTE, SECOND: Cú pháp chung: Tênhàm(serial_number): Công dụng: Hàm tách giờ, phút hoặc giây từ chuỗi dữ liệu thời gian của serial_number. Ví dụ: = HOUR(“ 11: 59: 30”) 11 = MINUTE(“ 11: 59: 30”) 59 = SECOND(“ 11: 59: 30”) 30 50

Hàm DATE Cú pháp : = DATE(year, month, day) Công dụng : Hiển thị các đối số ở dữ liệu kiểu ngày. Ví dụ : =DATE(10, 1, 29) 29/1/2010 51

6. 4. 4 Ha m xư ly dữ liệu nga y giơ 6. 4. 4. 7 Hàm TIME Cú pháp: TIME(hour, minute, second) Công dụng: Hiển thị các đối số dưới dạng giờ. Ví dụ : =Time(17, 30, 01) 17: 30: 01 hoặc 5: 30 PM 52

6. 4. 5. Ha m xư ly dữ liệu da ng Logic Hàm logic được xây dựng dựa trên các biểu thức logic. Biểu thức logic là những biểu thức chỉ trả về một trong hai giá trị: True (đúng) hoặc False (sai). 53

Ha m xư ly dữ liệu da ng Logic Hàm AND Cú pháp : =AND(logical 1, logical 2, . . . ) Công dụng: dùng để liên lết điều kiện để kiểm tra đồng bộ, trong đó: logical 1, logical 2, . . . là những biểu thức logic. Kết quả của hàm là True (đúng) nếu tất cả các đối số là True, các trường hợp còn lại cho giá trị False (sai) Ví dụ: =AND(3<6, 4>5) cho giá trị False 54

. Ha m xư ly dữ liệu da ng Logic Hàm OR Cú pháp: =OR(logical 1, logical 2, . . . ) Công dụng: dùng để liên lết điều kiện để kiểm tra đồng bộ, trong đó: logical 1, . . . là những biểu thức logic. Kết quả của hàm là False (sai) nếu tất cả các đối số là False, các trường hợp còn lại cho giá trị True (đúng) Ví dụ: =OR(3>6, 4>5) cho giá trị False. 55

Ha m xư ly dữ liệu da ng Logic Hàm NOT() Cú pháp: =NOT(logical) Công dụng : trả về trị phủ định của biểu thức logic Ví dụ: =NOT(3<6) cho giá trị False. 56

Ha m xư ly dữ liệu da ng Logic Bảng tổng hợp hàm AND, OR, NOT A B AND(A, B) OR(A, B) NOT(A) TRUE FALSE TRUE FALSE FALSE TRUE 57

Ha m xư ly dữ liệu da ng Logic Hàm IF Cú pháp: = IF(logical_test, [value_if_true], [value_if_false]) Công dụng: Trả lại giá trị ghi trong value_if_true(giá trị khi đúng) nếu logical_test (biểu thức logic) là TRUE Ngược trả về giá trị ghi trong value_if_false(giá trị khi sai) nếu logical_test (biểu thức logic) là FALSE Hàm IF có thể lồng nhau đến 7 cấp. Ví dụ: Nếu ô B 5 có giá trị >=5 thì ô tại vị trí chèn hàm IF nhận giá trị Đạt, nếu < 5 thì Hỏng. Gõ công thức cho ô cần tính như sau: = IF(B 5>=5, "Đạt“, "Hỏng") 58

Hàm thống kê Hàm MAX Cú pháp: = MAX(number 1, number 2, . . . ) = MAX(range) Công dụng : trả về giá trị lớn nhất trong danh sách đối số hoặc trong vùng. Ví dụ : =MAX(4, 2, 16, 0) trả về giá trị 16 59

Ha m thô ng kê Hàm MIN Cú pháp: = MIN(number 1, number 2, . . . ) = MIN(range) Công dụng : trả về giá trị nhỏ nhất trong danh sách đối số hoặc trong vùng. Ví dụ : =MIN(4, 2, 16, 0) trả về giá trị 0 60

Ha m thô ng kê Hàm AVERAGE Cú pháp: = AVERAGE(number 1, number 2, . . . ) hoặc = AVERAGE(range) Công dụng : trả về giá trị trung bình cộng của danh sách đối số hoặc của vùng. Ví dụ : =AVERAGE(7, 8, 5, 4) trả về giá trị 6. 61

Ha m thô ng kê Hàm SUM Cú pháp : =SUM(number 1, [number 2], [number 3], …) Công dụng : Hàm tính tổng của dãy số Ví dụ : =SUM(1, 3, 4, 7) 15 (1+3+4+7= 15) 62

Hàm SUMPRODUCT(array 1, [array 2], [array 3], . . . ) là hàm tính tổng các tích lần lượt của các dãy array 1, [array 2], [array 3], . . . Ví dụ: 63

Ha m thô ng kê Hàm COUNT Cú pháp: = COUNT(value 1, value 2…) = COUNT(range) Công dụng : đếm số lượng ô có chứa dữ liệu kiểu số trong vùng hoặc được liệt kê trong ngoặc(không đếm ô chuỗi và ô rỗng). Ví dụ : = COUNT(2, ab, 5, 4) trả về giá trị là 3 64

Ha m thô ng kê Hàm COUNTA, hàm COUNTBLANK COUNTA: đếm số lượng ô có chứa dữ liệu (không phân biệt kiểu số hay kiểu chuỗi). COUNTBLANK: đếm số lượng ô rỗng trong vùng 65

Ha m thô ng kê Hàm RANK Cú pháp: = RANK(number, ref, order) Công dụng : Sắp xếp vị thứ của số number trong vùng tham chiếu ref, dựa vào cách sắp xếp order Nếu order =0, hoặc bỏ trống, Excel sẽ sắp xếp theo thứ tự giảm dần (giá trị lớn nhất sẽ ở vị trí 1) Nếu order khác 0, Excel sẽ sắp xếp theo thứ tự tăng dần (giá trị lớn nhỏ sẽ ở vị trí 1) 66

Ha m thô ng kê Hàm SUMIF Cú pháp: =SUMIF (range, criteria, [sum_range]) Công dụng: Hàm tính tổng các ô thỏa mãn điều kiện. range: Vùng điều kiện criteria: Điều kiện tính tổng, có thể là số, chữ hoặc biểu thức sum_range: Vùng tính tổng Hàm SUMIF chỉ tính tổng theo 1 điều kiện. 67

Ha m thô ng kê Hàm SUMIF Ví dụ: Tính số lượng của sản phẩm bánh = Sumif(D 2: D 5, ”bánh”, E 2: E 5) D 1 E Sản phẩm Số lượng 2 Kẹo 4 3 Bánh 9 4 Mứt 8 5 Bánh 6 68

Hàm SUMIFS(sum_range, criteria_range 1, criteria 1, [criteria_range 2, criteria 2], . . . ) tính tổng tham biến sum_range theo điều kiện tại vùng criteria_range 1 phải thỏa mãn chuẩn criteria 1 , và tại vùng criteria_range 2 phải thỏa mãn chuẩn criteria 2 và … cho phép tối đa 127 cặp vùng điều kiện. Ví dụ: 69

Hàm AVERAGEIF(range, criteria, [average_range]) Dùng tính trung bình của tham biến [average_range] với những dữ liệu tương ứng tại tham biến range thỏa mãn tiêu chuẩn criteria Ví dụ: Tính số lượng trung bình của những mặt hàng là A 70

Hàm AVERAGEIFS(average_range, criteria_range 1, criteria 1, [criteria_range 2, criteria 2], . . . ) lấy giá trị trung bình của tham biến average_range, tại những vùng criteria_range 1 thỏa mãn chuẩn criteria 1, và vùng criteria_range 2 thỏa mãn chuẩn criteria 2 và … Hàm nầy cho phép tối đa 127 cặp vùng được thỏa mãn yêu cầu. 71

Hàm AVERAGEIFS(average_range, criteria_range 1, criteria 1, [criteria_range 2, criteria 2], . . . ) Ví dụ: 72

Ha m thô ng kê Hàm COUNTIF Cú pháp: = COUNTIF( range, criteria) Công dụng : đếm số lượng ô trong vùng range thỏa mãn điều kiện criteria Ví dụ: Cho bảng tính như sau, yêu cầu đếm số mặt hàng có số lượng >=15 Kết quả sẽ trả về giá trị là 3. 73

Hàm COUNTIFS(criteria_range 1, criteria 1, [criteria_range 2, criteria 2]…) là hàm đếm các dữ liệu phải thỏa mãn criteria_range 1, criteria 1, [criteria_range 2, criteria 2]… hàm này cho phép đếm dữ liệu thỏa mãn đến 127 điều kiện. 74

Ha m tra cư u va tham chiê u Hàm VLOOKUP Cú pháp: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Trong đó: lookup_value là giá trị tìm kiếm, lookup_value có thể là một giá trị, một tham chiếu hoặc một chuỗi văn bản. table_array là bảng chứa thông tin dữ liệu muốn tìm. 76

Ha m tra cư u va tham chiê u Hàm VLOOKUP col_index_num là thứ tự của cột(từ trái sang) trong table_array mà hàm Vlookup sẽ nhận về một trong những giá trị của cột này nếu tìm thấy. range_lookup: giá trị logic true(1) hoặc false(0) để xác định kiểu tìm. Nếu giá trị này là 0 hoặc bỏ trống dò tìm chính xác Nếu là 1 dò theo khoảng và danh sách giá trị tìm kiếm phải được sắp xếp theo chiều tăng dần. 77

Ha m tra cư u va tham chiê u Hàm VLOOKUP Công dụng: Hàm này dùng để trả về giá trị cho ô hiện hành dựa vào “trị dò” và “bảng dò”. Excel đem “trị dò” dò vào cột đầu tiên trong bảng dò, nếu tìm thấy thì trả về dữ liệu ở cột tham chiếu trên bảng dò phụ thuộc vào cách dò. Nếu cách dò =1(true), dò theo khoảng; nếu cách dò =0 (false) dò chính xác. 78

Ha m tra cư u va tham chiê u Hàm VLOOKUP Lưu ý: Bảng dò thường được chọn là địa chỉ tuyệt đối. Có thể lồng các hàm khác vào trong hàm Vlookup. Lỗi #N/A: dò tìm không có giá trị Lỗi #REF: cột tham chiếu không tồn tại trong bảng dò. 79

Ha m tra cư u va tham chiê u Hàm HLOOKUP Cú pháp: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) Trong đó: lookup_value là giá trị tìm kiếm, lookup_value có thể là một giá trị, một tham chiếu hoặc một chuỗi văn bản. table_array là bảng chứa thông tin dữ liệu muốn tìm. 80

Ha m tra cư u va tham chiê u Hàm HLOOKUP row_index_num là thứ tự của hàng(từ trên xuống dưới) trong table_array mà hàm Vlookup sẽ nhận về một trong những giá trị của hàng này nếu tìm thấy. range_lookup: giá trị logic true(1) hoặc false(0) để xác định kiểu tìm. Nếu giá trị này là 0 hoặc bỏ trống dò tìm chính xác Nếu là 1 dò theo khoảng và danh sách giá trị tìm kiếm phải được sắp xếp theo chiều tăng dần. 81

Ha m tra cư u va tham chiê u Hàm HLOOKUP Công dụng: Hàm này dùng để trả về giá trị cho ô hiện hành dựa vào “trị dò” và “bảng dò”. Excel đem “trị dò” dò vào hàng đầu tiên trong bảng dò, nếu tìm thấy thì trả về dữ liệu ở hàng tham chiếu trên bảng dò phụ thuộc vào cách dò. Nếu cách dò =1(true), dò theo khoảng; nếu cách dò =0 (false) dò chính xác. 82

Ha m tra cư u va tham chiê u Hàm HLOOKUP Lưu ý: Bảng dò thường được chọn là địa chỉ tuyệt đối. Có thể lồng các hàm khác vào trong hàm Vlookup. Lỗi #N/A: dò tìm không có giá trị Lỗi #REF: cột tham chiếu không tồn tại trong bảng dò. 83

Ha m tra cư u và tham chiê u Hàm INDEX Cú pháp 1: Công dụng: =INDEX(array, row_num, [column_num]) Hàm trả về giá trị tại vị trí hàng thứ row_num và cột thứ column_num trong array. Array có thể là mảng hoặc một vùng dữ liệu. 84

Ha m tra cư u va tham chiê u Hàm MATCH Cú pháp: MATCH(lookup_value, lookup_array, [match_type]) Công dụng: Hàm trả về vị trí của giá trị tìm kiếm lookup_value trong vùng tìm kiếm lookup_array dựa vào kiểu tìm kiếm match_type. 85

Ha m tra cư u va tham chiê u Hàm MATCH Trong đó : Vùng tìm kiếm lookup_array phải là mảng 1 chiều. Kiểu tìm kiếm match_type có thể là 0 hoặc 1. Nếu là 1 hoặc -1, tìm kiếm theo khoảng và vùng tìm kiếm phải được sắp xếp theo thứ tự giảm dần(-1) hoặc tăng dần (1) Nếu là 0 tìm kiếm chính xác. 86

Ha m tra cư u va tham chiê u Hàm MATCH Lưu ý : Hàm Match không phân biệt chữ hoa và thường. Nếu tìm kiếm không thành công trả về lỗi #N/A. Trong trường hợp match_type(kiểu tìm kiếm) là 0, và lookup_value là kiểu text, ta có thể sử dụng ký tự đại diện *, ? để tìm kiếm, và sẽ trả về vị trí tìm thấy đầu tiên của giá trị đó. 87

Hàm cơ sở dữ liệu DAVERAGE(database, field, criteria) Dùng tính giá trị trung bình tại cột Field của dữ liệu thỏa mãn điều kiện criteria Database là khối dữ liệu có chứa tiêu đề dòng danh sách dữ liệu, Field là tiêu đề cột cần tính toán trong hàm dữ liệu. Cơ sở dữ liệu database bắt buộc phải chứa dòng đầu tiên là tiêu đề cho mỗi cột. Field là cột xác định được tính toán trong hàm. Criteria tiêu chuẩn là khối ô dòng đầu chứa tiêu đề nhãn cột và các dòng kề dưới chứa các điều kiện cần thỏa mãn. Nếu có nhiều tiêu đề nhãn cột cần thỏa mãn thì tất cả các tiêu đề này phải cùng dòng và các điều kiện theo cùng nhãn cột nằm dưới tiêu đề nhãn theo quy ước nếu các điều kiện yêu cầu đồng thời thỏa mãn thì nằm trên cùng một dòng, khác dòng thì chỉ cần điều kiện này hay điều kiện kia được thỏa mãn Có thể dùng các ký tự đại diện 88

Hàm cơ sở dữ liệu DAVERAGE(database, field, criteria) Cơ sở dữ liệu (Database ) là vùng ô trong danh sách dữ liệu. Trong đó dòng là mẫu tin, cột là trường loại dữ liệu. Dòng đầu là nhãn cho biết thông tin trong cột. Field-Trường chỉ định cột nào sử dụng trong hàm. Trường có thể là text nằm trong dấu nháy kép là nhãn tên trường hay là số đại diện cho thứ tự cột. . Criteria tiêu chuẩn là vùng ô chứa điều kiện yêu cầu xác lập. Biến tiêu chuẩn có thể là khối nào thỏa mãn ít nhất dòng đầu khối tiêu chuẩn là các nhãn trường –Field và ô dưới nhãn chứa điều kiện tiêu chuẩn 89

Hàm cơ sở dữ liệu DCOUNT(database, field, criteria) Dùng số đếm giá trị số tại cột Field của dữ liệu thỏa mãn điều kiện criteria Database là khối dữ liệu có chứa tiêu đề dòng danh sách dữ liệu, Field là tiêu đề cột cần tính toán trong hàm dữ liệu. Cơ sở dữ liệu database bắt buộc phải chứa dòng đầu tiên là tiêu đề cho mỗi cột. Field là cột xác định được tính toán trong hàm. Criteria tiêu chuẩn là khối ô dòng đầu chứa tiêu đề nhãn cột và các dòng kề dưới chứa các điều kiện cần thỏa mãn. Nếu có nhiều tiêu đề nhãn cột cần thỏa mãn thì tất cả các tiêu đề này phải cùng dòng và các điều kiện theo cùng nhãn cột nằm dưới tiêu đề nhãn theo quy ước nếu các điều kiện yêu cầu đồng thời thỏa mãn thì nằm trên cùng một dòng, khác dòng thì chỉ cần điều kiện này hay điều kiện kia được thỏa mãn Có thể dùng các ký tự đại diện 90

Hàm cơ sở dữ liệu DCOUNTA(database, field, criteria) Dùng để đếm tất cả giá trị là ký tự hay số tại cột Field của dữ liệu thỏa mãn điều kiện criteria Database là khối dữ liệu có chứa tiêu đề dòng danh sách dữ liệu, Field là tiêu đề cột cần tính toán trong hàm dữ liệu. Cơ sở dữ liệu database bắt buộc phải chứa dòng đầu tiên là tiêu đề cho mỗi cột. Field là cột xác định được tính toán trong hàm. Criteria tiêu chuẩn là khối ô dòng đầu chứa tiêu đề nhãn cột và các dòng kề dưới chứa các điều kiện cần thỏa mãn. Nếu có nhiều tiêu đề nhãn cột cần thỏa mãn thì tất cả các tiêu đề này phải cùng dòng và các điều kiện theo cùng nhãn cột nằm dưới tiêu đề nhãn theo quy ước nếu các điều kiện yêu cầu đồng thời thỏa mãn thì nằm trên cùng một dòng, khác dòng thì chỉ cần điều kiện này hay điều kiện kia được thỏa mãn Có thể dùng các ký tự đại diện 91

Hàm cơ sở dữ liệu DMAX(database, field, criteria) Dùng tính giá trị lớn nhất tại cột Field của dữ liệu thỏa mãn điều kiện criteria Database là khối dữ liệu có chứa tiêu đề dòng danh sách dữ liệu, Field là tiêu đề cột cần tính toán trong hàm dữ liệu. Cơ sở dữ liệu database bắt buộc phải chứa dòng đầu tiên là tiêu đề cho mỗi cột. Field là cột xác định được tính toán trong hàm. Criteria tiêu chuẩn là khối ô dòng đầu chứa tiêu đề nhãn cột và các dòng kề dưới chứa các điều kiện cần thỏa mãn. Nếu có nhiều tiêu đề nhãn cột cần thỏa mãn thì tất cả các tiêu đề này phải cùng dòng và các điều kiện theo cùng nhãn cột nằm dưới tiêu đề nhãn theo quy ước nếu các điều kiện yêu cầu đồng thời thỏa mãn thì nằm trên cùng một dòng, khác dòng thì chỉ cần điều kiện này hay điều kiện kia được thỏa mãn Có thể dùng các ký tự đại diện . 92

Hàm cơ sở dữ liệu DMIN(database, field, criteria) Dùng tính giá trị nhỏ nhất tại cột Field của dữ liệu thỏa mãn điều kiện criteria Database là khối dữ liệu có chứa tiêu đề dòng danh sách dữ liệu, Field là tiêu đề cột cần tính toán trong hàm dữ liệu. Cơ sở dữ liệu database bắt buộc phải chứa dòng đầu tiên là tiêu đề cho mỗi cột. Field là cột xác định được tính toán trong hàm. Criteria tiêu chuẩn là khối ô dòng đầu chứa tiêu đề nhãn cột và các dòng kề dưới chứa các điều kiện cần thỏa mãn. Nếu có nhiều tiêu đề nhãn cột cần thỏa mãn thì tất cả các tiêu đề này phải cùng dòng và các điều kiện theo cùng nhãn cột nằm dưới tiêu đề nhãn theo quy ước nếu các điều kiện yêu cầu đồng thời thỏa mãn thì nằm trên cùng một dòng, khác dòng thì chỉ cần điều kiện này hay điều kiện kia được thỏa mãn Có thể dùng các ký tự đại diện 93

Hàm cơ sở dữ liệu DPRODUCT(database, field, criteria) Dùng để nhân tất cả các giá trị tại cột Field của dữ liệu thỏa mãn điều kiện criteria Database là khối dữ liệu có chứa tiêu đề dòng danh sách dữ liệu, Field là tiêu đề cột cần tính toán trong hàm dữ liệu. Cơ sở dữ liệu database bắt buộc phải chứa dòng đầu tiên là tiêu đề cho mỗi cột. Field là cột xác định được tính toán trong hàm. Criteria tiêu chuẩn là khối ô dòng đầu chứa tiêu đề nhãn cột và các dòng kề dưới chứa các điều kiện cần thỏa mãn. Nếu có nhiều tiêu đề nhãn cột cần thỏa mãn thì tất cả các tiêu đề này phải cùng dòng và các điều kiện theo cùng nhãn cột nằm dưới tiêu đề nhãn theo quy ước nếu các điều kiện yêu cầu đồng thời thỏa mãn thì nằm trên cùng một dòng, khác dòng thì chỉ cần điều kiện này hay điều kiện kia được thỏa mãn Có thể dùng các ký tự đại diện 94

Hàm Thông tin cho kết quả là TRUE hay FALSE ISBLANK(value) ISERROR(value) ISLOGICAL(value) ISNA(value) ISNONTEXT(value) ISNUMBER(value) ISREF(value) ISTEXT(value) 95

Hàm Thông tin ISBLANK(value) ISERROR(value) ISLOGICAL(value) ISNA(value) ISNONTEXT(value) ISNUMBER(value) ISREF(value) ISTEXT(value) 96

Hàm Thông tin � Value là tham biến mà ta muốn kiểm tra. Giá trị tham biến có thể là : blank (empty cell), error, logical, text, number, or reference value, hay là tên tham chiếu đến g bía trị khác bất kỳ mà ta muốn kiểm tra. Hàm trả về giá trịn TRUE nếu � Với hàm � ISBLANK gía trị tham chiếu là rỗng. � ISERR giá trị tham chiếu có lỗi trừ giá trị lỗi #N/A. � ISERROR giá trị tham chiếu lỗi gồm (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? , or #NULL!). � ISLOGICAL giá trị là luận lý. � ISNA giá trị tham chiếu là giá trị lỗi #N/A (value not available). � ISNONTEXT giá trị tham chiếu là bất cứ gì không phải ký tự. (Chú ý nếu tham chiếu vào ô trống sẽ cho kết quả TRUE) � ISNUMBE giá trị tham chiếu là số. � ISREF giá trị là nơi tham chiếu. � ISTEXT giá trị là ký tự. 97

Mô t sô thông ba o lô i Thông báo lỗi # DIV/0 ! (Devide by zero) : Xảy ra khi trong công thức có phép tính chia cho ô chứa giá trị 0 hoặc ô trống. Thông báo lỗi # N/A (Not available) : Xảy ra khi dùng hàm tìm kiếm tìm một giá trị không có trong phạm vi. Thông báo lỗi # NAME? : Xảy ra khi trong công thức có một tên mà Excel không hiểu được (Viết sai tên ô, tên vùng, tên hàm. Hằng chuỗi không viết trong cặp dấu nháy kép). 98

Mô t sô thông ba o lô i Thông báo lỗi # NULL! : Xảy ra khi xác định một giao giữa hai vùng nhưng thực tế hai vùng đó không giao nhau. Thông báo lỗi # NUM! : Xảy ra khi các dữ liệu số có sự sai sót như số vượt quá giới hạn cho phép (Căn, logarit của số âm. . . ). Thông báo lỗi # REF! : Xảy ra khi trong công thức tham chiếu đến một địa chỉ không hợp lệ Thông báo lỗi # VALUE! : Xảy ra khi trong công thức có các toán hạng và toán tử sai kiểu dữ liệu. 99

100