Cung hoc Excel Chuong4

CHƯƠNG 4
CÁC HÀM CƠ BẢN
Hàm là dạng công thức đã được xây dựng sẵn để giúp người sử dụng tính toán nhanh hơn. Tất cả các Hàm đều có dạng tổng quát
Tên hàm(Các tham số)
I) Nhóm Hàm Số:
Hàm ABS: Lấy giá trị tuyệt đối của một số
Cú pháp: ABS(số)
Ví dụ: =ABS(-5) = 5
Hàm INT: Lấy phần nguyên của một số
Cú pháp: INT(Số)
Ví dụ: =INT(8.6) =8
Hàm Mod: Lấy phần dư của phép chia
Cú pháp: MOD(Số bị chia, Số chia)
Ví dụ: =Mod(7,3) = 1
4- Hàm Round: Làm tròn số
Cú pháp: Round(Số cần làm tròn, Số lẻ)
- Số lẻ >0: Làm tròn về phía bên phải dấu thập phân
- Số lẻ <0: làm tròn về phía bên trái dấu thập phân
- Số lẻ = 0: Làm tròn hàng đơn vị
Ví dụ: =Round(15.3524,2) = 15.35
=Round(15268.534,-3) = 15000
=Round(15.813,0)=16
5- Hàm SQRT: Lấy căn bậc 2 của 1 số
Cú pháp: SQRT(số)
Ví dụ: =SQRT(16) = 4
6- Hàm VALUE: Đổi kiểu dữ liệu chuỗi là kí số thành số
Cú Pháp: Value(Chuổi kí số)
Ví dụ: =Value(“123”)= 123
II) Các Hàm thống Kê
1- Hàm AVERAGE: Tính trung bình cộng của các số
Cú pháp: AVERAGE(số 1, số 2, …)
Ví dụ: = Average(4,6,8,10) = 7
2- Hàm COUNT: Đếm các phần tử kiểu số
Cú pháp: COUNT(giá trị 1, giá trị 2, …)
Ví dụ: =Count(2, “sd”,4,7, “cv”) =3
3- Hàm COUNTA: Đếm các phần tử khác trống
Cú pháp: COUNTA(giá trị 1, giá trị 2, …)
Ví dụ: =COUNTA(2, “a”,5, “c”,6)=5
4- Hàm COUNTBLANK: Đếm các ô chưa có dữ liệu
Cú pháp: COUNTBLANK(giá trị 1, giá trị 2, …)
Ví dụ: =CountBlank(2, “”,5, “”,6)= 2
5- Hàm MAX,MIN: Lấy số lớn nhất hoặc nhỏ nhất
Cú pháp: MAX[MIN](số 1, số 2, …)
Ví dụ: =Max(3,6,23,8)= 23; =Min(3,6,23,8)= 3
6- Hàm SUM: Tính tổng các số
Cú pháp: SUM(số 1, số 2, …)
Ví dụ: =SUM(3,5,7,4)=19
7-Hàm RANK: Trả về thứ hạng
Cú pháp: RANK(ô cần xếp thứ hạng, Phạm vi, Order)
Order = 0:Ô giá trị số lớn nhất xếp hạng 1
Order =1: Ô giá trị số nhỏ nhất xếp hạng 1
III) Nhóm Hàm Toán học
1- Hàm Product: Tính tích các đối số
Cú Pháp: Product(số 1, số 2, …)
Ví dụ: =Product(1,3,5) = 15
2- Hàm SumProduct: Tính tổng của tích các phần tử trong các mảng tương ứng
Cú Pháp: SumProduct(Vùng 1, vùng 2,…)
Ví dụ: =SumProduct(A1:A3,B1:B3,C1:C3)=271
IV) Nhóm Hàm xử lý chuỗi
1- Hàm LEFT: Lấy các kí tự phía bên trái chuỗi
Cú Pháp: LEFT(Chuỗi, số kí tự cần lấy)
Ví dụ: =LEFT(“TP_HCM”,2)  “TP”
2- Hàm RIGHT: Lấy các kí tự phía bên phải chuỗi
Cú Pháp: RIGHT(Chuỗi, số kí tự cần lấy)
Ví dụ: =RIGHT(“TP_HCM”,2)  “CM”
3- Hàm MID: Lấy số kí tự từ vị trí bắt đầu đến vị trí kết thúc
Cú pháp: MID(Chuỗi,số bắt đầu,số kí tự cần lấy)
Ví dụ: =MID(“TP_HCM”,4,2)  “HC”
4- Hàm UPPER: Đổi chuỗi thành chữ Hoa
Cú pháp: UPPER(chuỗi)
Ví dụ: =UPPER(“lê vân”)  “LÊ VÂN”
5- Hàm LOWER: Đổi chuỗi thành chữ thường
Cú pháp: LOWER(chuỗi)
Ví dụ: =UPPER(“LÊ VÂN”)  “lê vân”
6- Hàm PROPER: Đổi kí tự đầu mỗi từ thành chữ Hoa
Cú pháp: PROPER(chuỗi)
Ví dụ: =PROPER(“LÊ VÂN”)  “Lê Vân”
7- Hàm TRIM: Cắt bỏ các khoảng trắng ở hai đầu chuỗi
Cú pháp: TRIM(chuỗi)
Ví dụ: =Trim(“ Lê Vân ”)  “Lê Vân”
8- Hàm Len: Lấy chiều dài chuỗi
Cú pháp: LEN(chuỗi)
Ví dụ: =Len(“TP_HCM”)  6
V) Nhóm Hàm Điều kiện
1- Hàm IF: Trả về giá trị 1, nếu điều kiện đúng ngược lại trả về giá tri 2
Cú pháp: IF(Điều kiện, giá trị 1, giá trị 2)
Ví dụ: IF(C4>=5, “Đạt”, “Không Đạt”)
2- Hàm SUMIF: Tính tổng giá trị các ô trong vùng 2 tương ứng các ô vùng 1 thoả điều kiện
Cú pháp: SUMIF(vùng 1,”Điều kiện”, vùng 2)
Ví dụ: Dữ liệu trong các ô như sau:
=SUMIF(A1:A4,">5",B1:B4) = 22
3- Hàm COUNTIF: Đếm các ô thoả
điều kiện trong vùng
Cú pháp: COUNIF(vùng,”Điều kiện”)
Ví dụ:Count(A1:A4, “<5”) => 1
VI) Nhóm Hàm Ngày Tháng
1- Hàm Date: Trả về số tứ tự của ngày theo năm tháng và ngày chỉ định (được tính từ ngày 1/1/1990)
Cú pháp: DATE (year, month, day)
Trong đó:
- year: Là con số chỉ năm có giá trị từ 1900 – 2078
- month: Là con số chỉ tháng của năm
- day: Là con số chỉ ngày của tháng
Ví dụ: =DATE(2004,11,3)=38294
2- Hàm DAY: Trả về ngày (1 -31) trong tháng tương ứng
Cú pháp: DAY(serial_number)
Ví dụ: =DAY(38294)= 3
3- Hàm MONTH: Trả về số tháng (1 – 12)
Cú pháp: MONTH(serial_number)
Ví dụ: ô D2 chứa dữ liệu là ngày 11/04/2004
=MONTH(D2)=11
4- Hàm YEAR: Trả về số năm (từ 1900 – 2078)
Cú pháp: YEAR(serial_number)
Ví dụ: ô D2 chứa dữ liệu là ngày 11/04/2004
=YEAR(D2) = 2004
5- Hàm TODAY(): Trả về số thứ tự của ngày hiện tại
6- Hàm NOW(): Trả về ngày tháng năm và giờ phút của hệ thống
7- Hàm WEEKDAY: Trả về số thứ trong tuần
Cú pháp: WEEKDAY(serial_number)
Ví dụ: ô D2 chứa dữ liệu là ngày 11/04/2004
=WEEKDAY(D2) là ngày thứ 5
VII) Nhóm Hàm Logic
1- Hàm AND: Trả về True nếu tất cả đối số là True, Trả về False nếu ít nhất một đối số là False
Cú pháp: AND(logical1,logical2,. . .)
Ví dụ: =IF(AND(E2>5,F2="A"),"Đậu","Rớt")
2- Hàm OR: Trả về True nếu ít nhất một đối số là True, Trả về False nếu tất cả các đối số là False
Cú pháp: OR(logical1,logical2,. . .)
Ví dụ:
=IF(OR(E2>5,F2="A"),"Đậu","Rớt")
3- Hàm NOT: Trả về giá trị là phủ định của biểu thức điều kiện
Cú pháp: NOT(Biểu thức Điều kiện)
Ví dụ: =IF(AND(E2>=5,NOT(F2="A")),"Đậu","Rớt")
VIII) Nhóm Hàm Tìm kiếm
1- Hàm VLOOKUP: Hàm này tìm kiếm 1 giá trị trong cột bên trái của vùng dữ liệu và trả về giá trị trên cột thứ n nếu tìm thấy, hoặc trả về #N/A nếu không tìm thấy
Cú pháp: VLOOKUP(Gtrị dò tìm , Vùng Dliệu, n [,x])
Trong đó:
- Gtrị dò: Là giá trị tìm kiếm trong cột bên trái của vùng dữ liệu
- Vùng Dliệu: Là vùng chứa dữ liệu được tìm kiếm cho trước
- n: Là số thứ tự các cột trong vùng dữ liệu nơi mà Hàm Vlookup sẽ lấy giá trị trả về, n=1 chỉ cột đầu tiên, n=2 là cột thứ 2,. . .
[,x]: Là giá trị logic, với x = 0 thì tìm kiếm chính xác, x = 1 thì tìm kiếm gần đúng
2- Hàm HLOOKUP: Hàm này tìm kiếm 1 giá trị trong hàng đầu tiên của vùng dữ liệu và trả về giá trị trên hàng thứ n nếu tìm thấy, hoặc trả về #N/A nếu không tìm thấy
Cú pháp: HLOOKUP(Gtrị dò tìm , Vùng Dliệu, n [,x])
Trong đó:
- Gtrị dò: Là giá trị tìm kiếm trong cột bên trái của vùng dữ liệu
- Vùng Dliệu: Là vùng chứa dữ liệu được tìm kiếm cho trước (vùng dữ liệu chứa địa chỉ tuyệt đối)
- n: Là số thứ tự các hàng trong vùng dữ liệu nơi mà Hàm Vlookup sẽ lấy giá trị trả về, n=1 chỉ hàng đầu tiên, n=2 là hàng thứ 2,. . .
[,x]: Là giá trị logic, với x = 0 thì tìm kiếm chính xác, x = 1 thì tìm kiếm gần đúng
Ví dụ 1: =VLOOKUP(F3,$B$3:$C$6,2,0)
Ví dụ 2: =HLOOKUP(F3,$B$3:$C$6,2,0)
3- Hàm Index
Hàm này trả về tham chiếu của ô nằm tại giao điểm của hàng và cột phân biệt
Cú pháp: INDEX(Mảng, chỉ số dòng, chỉ số cột)
Trong đó:
Mảng: Là một bảng dữ liệu hai chiều chứa dữ liệu để tham chiếu
Chỉ số dòng: Là Số thứ tự của hàng trong tham chiếu sẽ lấy giá trị từ đó
Chỉ số cột: Là Số thứ tự của cột trong tham chiếu sẽ lấy giá trị từ đó
4- Hàm Match: Trả về vị trí tìm kiếm thấy trong một bảng dò tìm
Cú pháp: Match(Gtrị dò,Bảng dò tìm, Cách thức dò)
Giá trị dò: Là giá trị dùng để dò tìm trong bảng dò tìm
Bảng dò tìm: Là vùng chứa dữ liệu tìm kiếm
Cách thức dò: Mang 1 trong các giá trị sau:
= 1: Tìm kiếm giá trị lớn nhất bé hơn hay bằng giá trị tìm kiếm. Bảng dò tìm phải sắp xếp tăng dần
= 0: Tìm giá trị bằng với giá trị trong giá trị dò tìm
= -1: Tìm giá trị bé nhất lớn hơn hay bằng giá trị tìm kiếm. Bảng dò tìm phải sắp theo thứ tự giảm dần
Vídụ: =MATCH("b",{"a","b","c"},0) cho giá trị là 2
5- Hàm ISNA(Value):Trả về True nếu Value liên hệ có ô chứa giá trị mã lỗi #N/A