Excel

CHƯƠNG 4
HÀM TRONG EXCEL

4.1 QUY TẮC SỬ DỤNG HÀM
4.2 NHẬP HÀM VÀO BẢNG TÍNH
4.3 MỘT SỐ HÀM THƯỜNG DÙNG
4.1 QUY TẮC SỬ DỤNG HÀM
Cấu trúc của hàm :
=Tên hàm (đối số 1, đối số 2,.)

Chú ý:
Các đối số cách nhau bằng dấu , or ;
Kiểu Text trong công thức phải nằm trong dấu nháy đôi. " "
4.2 NHẬP HÀM VÀO BẢNG TÍNH
Nhập trực tiếp
Nhập dấu = , tên hàm và các đối số
Dùng insert Function Wizard
All : Tất Cả Các Hàm
Financial : Hàm Tài Chính
Date & Time : Hàm Ngày Và Giờ
Math &Trig: Hàm Toán Và Lượng Giác
Statistical : Hàm Thống Kê
Lookup & Reference :Hàm Tìm Kiếm Và Tham Chiếu
4.3 MỘT SỐ HÀM THƯỜNG DÙNG
4.3.1 Hàm ngày tháng
4.3.2 Hàm ký tự
4.3.3 Hàm toán học
4.3.4 Hàm logic
4.3.5 Hàm thống kê
4.3.6 Hàm Tìm Kiếm Và Tham Chiếu.
4.3.1 Hàm ngày tháng
1. DATE
2. DAY
3. MONTH
4. YEAR
5. WEEKDAY
6. HOUR
7. MINUTE
8. SECOND
9. TIME
10. NOW
4.3.1 Hàm ngày tháng
DATE(YEAR, MONTH, DAY)
Chỉ ra ngày dạng số ứng với ngày tháng năm
Ví dụ: = DATE(06,09,27)
trả về kết quả 27-09-2006
4.3.1 Hàm ngày tháng
DAY(Date)
Số ngày trong tháng của biến ngày tháng date
Ví dụ := DAY("27-09-06")trả về 27
= day ("27-sep") trả về 27
MONTH(Date)
Số tháng của biến ngày tháng date
Ví dụ := MONTH("27-09-06")trả về 09
= MONTH ("27-sep") trả về 09
4.3.1 Hàm ngày tháng
YEAR(Date)
Số năm của biến ngày tháng date
Ví dụ := YEAR("27-09-06") trả về 06
4.3.1 Hàm ngày tháng
WEEKDAY(Date)
Trả về giá trị từ thứ (1 đến thứ 7) của dữ liệu ngày.
VD: =WEEKDAY("07/04/2005") Kết quả 5 (Thứ 5)
4.3.1 Hàm ngày tháng
Hàm HOUR : Tính phần giờ tương ứng
Cú pháp : HOUR(Time)
Ví dụ : hour("12:10") = 12
4.3.1 Hàm ngày tháng
Hàm MINUTE : Cho kết quả là mấy phút
Cú pháp : MINUTE(Time)

Ví dụ : =MINUTE("1:10:30") = 10
4.3.1 Hàm ngày tháng
Hàm SECOND : Cho kết quả là mấy giây
Cú pháp : SECOND(Time)
Vd :
=second("13:20:30") kq 30 giây
4.3.1 Hàm ngày tháng
Hàm TIME : trả về hh:mm:ss
CÚ PHÁP: TIME (hh,mm,ss)
VD:
=TIME(2,4,5) RESULT 2:04:05 AM
4.3.1 Hàm ngày tháng
Hàm NOW : Cho ngày, giờ hiện tại của hệ thống
Cú pháp : NOW()
4.3.1 Hàm ngày tháng
4.3.2 Hàm ký tự
1. EXACT
2. FIND
3. SEARCH
4. FIXED
5. LEFT
6. Right
7. Mid
8. UPPER
9. LOWER
10. PROPER
11. TRIM
12. LEN
13. REPLACE
14. REPT
15. SUBSTITUTE
=EXACT(Text1,Text2)
Nhận giá trị TRUE hay FALSE phụ thuộc vào hai chuỗi text1 và text2 có giống hệt nhau hay không
Ví dụ:
=EXACT("EXCEL","EXCEL") trả về TRUE
=EXACT("EXCEL","Excel") trả về FALSE
4.3.2 Hàm ký tự
4.3.2 Hàm ký tự
FIND(find_text,within_text,start_num)

Vị trí của chuổi con (Find_text) trong chuỗi lớn (text) bắt đầu từ vị trí start_num
FIND(find_text,within_text,start_num)
SEARCH(find_text,within_text,start_num)
Tìm vị trí của ký tự trong chuỗi
Vd:
=SEARCH("e","Statements",6) Result: 7
=SEARCH("Margin","Profit Margin") Result: 8
4.3.2 Hàm ký tự
FIXED(number,decimals,no_commas)

Chuyển số (number) thành chuỗi dạng cố định với decimal số thập phân .
4.3.2 Hàm ký tự
LEFT(Text, Number)
Lấy Number ký tự bên trái của Text
Ví dụ:
= LEFT("EXCEL",3) trả về EXC
4.3.2 Hàm ký tự
Right(Text, Number)
Lấy number ký tự bên phải của text
Ví dụ:
= RIGHT("Excel",3) trả về cel
4.3.2 Hàm ký tự
Mid(Text,n1,n2)

Trích chuỗi từ vị trí thứ n1 và lấy n2 ký tự về phía bên phải.

Vd:
=MID("EXCEL",3,3) sesult CEL
4.3.2 Hàm ký tự
HÀM UPPER : Chuyển ký tự chuỗi sang chữ in
CÚ PHÁP : UPPER("Text")
Vd:
=Upper("Total") Result TOTAL
4.3.2 Hàm ký tự
HÀM : LOWER : Chuyển sang chữ thường
CÚ PHÁP: LOWER("Text")
Vd:
=Lower("E. E. Cummings ") Result e. e. cummings
4.3.2 Hàm ký tự
HÀM : PROPER : Chuyển các ký tự đầu của chuỗi thành chữ hoa
Cú pháp: Proper("Text")
Vd:
=Proper("this is a TITLE ") Result This Is A Title
4.3.2 Hàm ký tự
HÀM TRIM : Trả về chuỗi ký tự sau khi đã cắt các ký tự trắng ở hai đầu chuỗi và trong chuỗi.
CÚ PHÁP : TRIM("Text")
Vd:
=Trim("Tin học") result: Tin học
4.3.2 Hàm ký tự
HÀM LEN : kết quả là 1 số độ dài của chuỗi
CÚ PHÁP : LEN("TEXT")
Vd:
=len("My computer") Result: 11
4.3.2 Hàm ký tự
HÀM REPLACE : Lấy ra trong chuỗi n ký tự và thay bằng chuỗi mới.
Cú pháp: REPLACE(old_text,start_num,num_chars,new_text)
Vd:
=replace("nguyễn thu bé",9,3,"thị") Result: Nguyễn thị bé
4.3.2 Hàm ký tự
HÀM REPT : Cho kết quả là 1 chuỗi ký tự được lặp lại
Cú pháp: REPT(Text,number_times)
Vd:
=REPT("thanh thảo", 3)
Result: thanh thảo thanh thảo thanh thảo thanh thảo
4.3.2 Hàm ký tự
HÀM SUBSTITUTE : Lấy ra trong chuỗi một chuỗi ký tự và thay bằng chuỗi ký tự mới.
Cú pháp : SUBSTITUTE(Text,old_text,new_text)
Vd: =Substitute("Nguyễn khánh ân","khánh","thị")
Result : Nguyễn thị ân.
4.3.2 Hàm ký tự
4.3.3 Hàm toán học
1. PI():
2. ABS
3. ACOS
4. ASIN
5. ATAN
6. COS
7. SIN
8. DEGREES
9. LOG
10. MDETERM
11. SQRT
12. TRUNC
13. COUNTIF
14. SUM
15. SUMIF
16. ROUND
17. INT, MOD
4.3.3 Hàm toán học
PI(): Trả về số pi bằng
3.141492654.
4.3.3 Hàm toán học
ABS(Number)
Trị tuyệt đối của số
Ví dụ:
=ABS(2)=2
=ABS(-2)=2
ACOS(Number)
Số trong khoảng từ -1 đến 1. Giá trị góc, Trả về theo radian từ 0 đến pi .Nếu muốn chuyển kết quả sang độ, Nhân nó với 180/pi()
Vd:
=ACOS(-0.5)*180/PI() = 120
4.3.3 Hàm toán học
Vd:
=ASIN(-0.5)*180/PI() = -30
ASIN(Number)
Hàm Arcsin của x, x name trong khoảng tử -1 đến 1. Giá trị góc trả về Theo radian từ -pi/2 đến pi/2.
4.3.3 Hàm toán học
Vd:
=ATAN(1)*180/PI() = 45
4.3.3 Hàm toán học
ATAN(Number)
Trả về Giá trị góc -pi/2 đến pi/2
Vd:
=COS(60*PI()/180) =0.5
4.3.3 Hàm toán học
COS(Number)
Hàm Cos của góc x, x là góc theo radian, nếu là độ phải nhân nó với PI()/180
SIN(Number)
Hàm sin của x
4.3.3 Hàm toán học
Vd:
=SIN(30*PI()/180) =0.5
Vd:
=DEGREES(PI()) = 180
DEGREES(angle)
Đổi radian sang độ
4.3.3 Hàm toán học
LOG(Number, Base)
Logarir cơ số base của number)
4.3.3 Hàm toán học
Vd:
MDETERM(array)
Định thức ma trận cho bởi địa chỉ vùng.
4.3.3 Hàm toán học
MDETERM(A1:C3) equals:
A1*(B2*C3-B3*C2)+
A2*(B3*C1-B1*C3)+
A3*(B1*C2-B2*C1)
RESULT = 6
RESULT = 20
SQRT(Number)
Hàm căn bậc hai của số.
VD:
=SQRT(16) = 4
4.3.3 Hàm toán học
TRUNC(Number)
Cắt bỏ phần thập phân của số chỉ lấy phần nguyên.
4.3.3 Hàm toán học
Ví dụ:
=TRUNC(2.789) trả về 2
=TRUNC(-2.4) trả về -2
COUNTIF(Range,Criteria)
Đếm số ô không rỗng theo điều kiện cho trước.
4.3.3 Hàm toán học
=COUNTIF(A2:A4,”K”) = 2
Tính tổng các số
SUM(number1,number2, ...)
4.3.3 Hàm toán học
=SUM(A1:A5) = 16
SUMIF( range, critera, sum_range)
Tính tổng thoã điều kiện.
4.3.3 Hàm toán học
=SUMIF(A1:A5,"B",B1:B5)
=600
4.3.3 Hàm toán học
ROUND(number,num_digits)
Laøm troøn soá thaäp phaân
Number: Soá thaäp phaân.
Num_digits: Laáy bao nhieâu soá leû.
=Round(1234.5678,2) = 1234.57
=Round(1235.4648,-2) = 1200
=Round(1235.4648,-3) = 1000
INT: Tìm phần nguyên của đối số
Cú pháp : INT(Number)
4.3.3 Hàm toán học
MOD: (Tìm phần dư của phép chia)
Cú pháp : Mod(Num,divisor)
Divisor : Số chia
=INT((B2-A2)/7)
=MOD((B2-A2),7)
INT(Number)
Mod(Num,divisor)
4.3.4 Hàm logic
Nhóm hàm luận lý
Phép toán so sánh : cho kết quả là 1 trong 2 giá trị đặc biệt :
True : tương đương với 1
False : tương đương với 0
Các phép toán so sánh gồm : =, <> , <, <=, >, >=.
4.3.4 Hàm logic
1. IF
2. AND
3. OR
4. NOT
IF(logical_test,value_if_true,value_if_false)
Hàm IF(Điều kiện,Giá trị ĐK đúng,Giá trị ĐK sai)
4.3.4 Hàm logic
SEE
Dùng hàm IF lồng nhau
IF(ĐK,KQ Đúng,IF(ĐK,KQ Đúng,.))
4.3.4 Hàm logic
SEE
AND: trả về kết quả đúng hoặc sai.
Cú pháp : AND(ĐK1,ĐK2,... ĐKN)
4.3.4 Hàm logic
SEE
OR: cho kết quả là True khi ít nhất 1 trong các đối số có giá trị True.
Cú pháp : OR(ĐK1,ĐK2,.....ĐKn)
4.3.4 Hàm logic
SEE
NOT(logic): Hàm phủ định
4.3.4 Hàm logic
Example:
4.3.5 Hàm thống kê
1. AVERAGE
2. COUNT
3. COUNTA
4. LARGE
5. Min, MAX
6. MODE
7. Rank
8. SMALL
4.3.5 Hàm thống kê
=AVERAGE(number1,number2,...) Tính trung bình cộng của các số num1, num2,..
Ví dụ:=AVERAGE(10,8,9,3) = 7.5
=AVERAGE(A1:A4) = 6
=COUNT(num1,num2,...)
Đếm số ô chứa giá trị số
4.3.5 Hàm thống kê
=COUNTA(num1,num2,...)
Đếm số ô chứa giá trị khác rỗng
4.3.5 Hàm thống kê
4.3.5 Hàm thống kê
LARGE(array,k)
Phần tử lớn thứ k trong vùng
4.3.5 Hàm thống kê
Min(num1,num2)
Tìm số nhỏ nhất
Max(num1,num2)
Tìm số lớn nhất
=MAX(A2,A4) = 7
=MIN(A2:A4) = 5
4.3.5 Hàm thống kê
MODE(number1,number2,...)
Trả lại giá trị hay gặp nhất trong vùng
=MODE(A2:A5) = 5
=MODE(1,2,4,5,2) = 2
4.3.5 Hàm thống kê
Xếp hạng số trong danh sách các số
Rank(number,vùng dữ liệu,Order)
Number: số cần tìm hạng
Vùng dữ liệu: phải giữ cố định
Order = 0 hay không có đối số này: số giá trị lớn nhất sẽ xếp thứ nhất.
Order > 0 số có giá trị nhỏ nhất sẽ xếp thứ nhất.
SHOW
=SMALL(array,k)
Phần tử nhỏ thứ k trong vùng
4.3.5 Hàm thống kê
=SMALL(A2:A5,1) = 5
=SMALL(A2:A5,2) = 5
=SMALL(A2:A5,3) = 6
=SMALL(A2:A5,4) = 7
4.3.6 Hàm Tìm Kiếm Và Tham Chiếu
1. MATCH
2. INDEX
3. VLOOKUP
4. HLOOKUP
4.3.6 Hàm Tìm Kiếm Và Tham Chiếu
MATCH(Gía trị dò,Dãy giá trị, cách dò)
Công dụng: cho biết giá trị dò nằm vị trí thứ mấy của dãy giá trị tính từ bên trái qua, từ trên xuống.
Cách dò : 0 dò tuyệt đối.
Cách dò : 1 dò tương đối.
4.3.6 Hàm Tìm Kiếm Và Tham Chiếu
INDEX(Bảng tra, dòng, cột)
=INDEX(A1:E3,2,3) = 40
=INDEX(A1:E3,3,2) = 60
=INDEX(A1:E3,MATCH(15,A1:E1,0),MATCH(35,A2:E2,0)) = 60
Vlookup(Giá trị tìm,bảng,cột cần tìm trong bảng,cách dò)
Công dụng: Tìm giá trị theo cột
4.3.6 Hàm Tìm Kiếm Và Tham Chiếu
Giá trị tìm: Giá trị sẽ được dò tìm.
Bảng: vùng chứa giá trị dò, thường dùng địa chỉ tuyệt đối.
Cột: số thứ tự của cột trong bảng.
Cách dò: 0, 1
EXAMPLE
Ví dụ : 4
Vượt từ 1 đến 100kw đơn giá 500ng.đ
Vượt từ 101- 200kw đơn giá 750ng.đ
Vượt từ 201- 300kw đơn giá 1000ng.đ
Vượt từ 301- 400kw đơn giá 1300ng.đ
Vượt trên 400kw đơn giá 1500ng.đ
Ví dụ: cách dò tương đối (1)
EXAMPLE
4.3.6 Hàm Tìm Kiếm Và Tham Chiếu
Hlookup Dò giá trị theo hàng
Cú pháp: Hlookup(Giá trị tìm,bảng,dòng,cách dò)
EXAMPLE