Các hàm thông dụng trong excel

Bùi Văn Huy Đại Học Sư Phạm Hà Nội 2 
Address: Đông Xuân - Quốc Oai - Hà Nội Phone: 0984 961 848 
CÁC HÀM THÔNG DỤNG TRONG EXCEL 
*---------- Author: Bùi Văn Huy ----------* 
I./ ĐỊNH NGHĨA HÀM: 
 Hàm là một thành phần của dữ liệu loại công thức và được xem như những công thức được xây dựng 
sẵn nhằm thực hiện các công việc tính toán phức tạp: 
Dạng thức tổng quát: (Tham số 1, Tham số 2) 
Trong đó: là quy ước của hàm, không phân biệt chữ hoa chữ thường. 
Các tham số: Đặt cách nhau bởi dấu "," hoặc dấu ";" tuỳ theo khai báo trong Control Panel. 
Cách nhập hàm: Chọn một trong các cách sau: 
C1: Chọn lệnh Insert - Function 
C2: Ấn nút Insert Function trên thanh công cụ. 
C3: Gõ trực tiếp từ bàn phím. 
II./ CÁC HÀM THÔNG DỤNG 
ABS(N): - Cho giá trị tuyệt đối của biểu thức N 
 Ví dụ: =ABS(2-35) cho kết quả: 33 
SQRT(N): 
 Cho giá trị là căn bậc hai của số N (N>0) 
 Ví dụ: =SQRT(25) cho kết quả : 5 
INT(N): - Cho giá trị là phần n guyên của biểu thức N 
 Ví dụ: =INT(235.25) cho kết quả : 235 
PI(N): 
 Cho giá trị là số pi (3.141593) 
MOD(N,M): 
 Cho phần dư của phép chia N cho M 
 Ví dụ: =MOD(10,3) cho kết quả: 1 
ROUND(bt số, N): - Làm tròn của Biểu - Thức - Số đến N số lẻ. 
 Nếu N>0: Làm tròn về bên phải cột thập phân 
 Nếu N<0: Làm tròn về bên trái cột thập phân 
 Ví dụ: =ROUND(333333,-3) cho giá trị 333000 (làm tròn đến hàng nghìn). 
 Ví dụ: =ROUND(35123,5432,0) cho giá trị 123456 
SUM(ds các giá trị): - Tính tổng của các giá trị có trong danh sách 
 Ví dụ: =SUM(5,5) cho kết quả: 10 
AVERAGE(ds các trị): - Tính trung bình cộng của các giá trị có trong danh sách 
 Ví dụ: =AVERAGE(5,5) cho kết quả: 5 
MAX(ds các trị): - Tìm giá trị số lớn nhất của giá trị có trong danh sách 
 Ví dụ: =MAX(5,6,7,8,9) cho kết quả: 9 
MIN(ds các trị): - Tìm giá trị số học nhỏ nhất của giá trị có trong danh sách 
 Ví dụ: =MIN(5,6,7,8,9) cho kết quả: 5 
COUNT(ds các trị): - Cho các ô chứa giá trị số có trong danh sách 
RANK(X,Danh sách): - Xác định thứ tự của các số hạng X so với các giá trị trong danh sách 
 (thứ hạng xếp theo giá trị giảm dần). Trị X và danh sách phải là các giá trị 
 số, nếu không sẽ gây ra lỗi #VALUE! Trị X phải rơi vào 1 trong các giá trị của 
 danh sách, nếu không sẽ gây ra lỗi #NA. 
DAY(dl kiểu ngày): - Cho giá trị ngày của dữ liệu kiểu ngày 
 Ví dụ: =DAY("18/09/2009") cho kết quả: 18 
MONTH(dl kiểu ngày): Cho giá trị tháng của dữ liệu ngày 
 Ví dụ: =MONTH("18/09/2009") cho kết quả: 09 
YEAR(dl kiểu ngày): - Cho giá trị năm của dữ liệu ngày 
 Ví dụ: =YEAR("18/09/2009") cho kết quả: 2009 
WEEKDAY(dl kiểu 
ngày): - Cho giá trị biết thứ trong tuần dưới dạng số 
 Ví dụ: =WEEKDAY("18/09/2009") cho kết quả 7 
TODAY(): - Cho biết dữ liệu ngày của ngày hiện tại 
IF(bt logic,bt1,bt2): - Nếu bt logic đúng thì cho hàm giá trị là Bt1, trái lại cho giá trị (bt2) 
Bùi Văn Huy Đại Học Sư Phạm Hà Nội 2 
Mail: quanghuyhn85@yahoo.com.vn 
 Các bt1 và bt2 cũng có thể là những giá trị, bt (chuỗi, số,logic) và cũng có thể 
 là 1 hàm IF khác. 
 Biểu thức điều kiện của hàm IF luôn phải có kết quả trả về 1 trong 2 điều kiện 
 TRUE (đúng) hoặc FALSE (sai) 
 Ví dụ: =IF("A"="B","C","D") cho kết quả D 
 Ví dụ: =IF("A"="B","C") cho kết quả lỗi: #VALUE 
 Trong hàm IF nếu không có đối số thứ 3 thì khi biểu thức điều kiện sai hàm 
 sẽ trả về giá trị FALSE 
 Ví dụ: =IF(2<3,"sai") cho kết quả: sai 
 Ví dụ: =IF(2>3,"sai") cho kết quả: FALSE 
 Chú ý: Hàm IF dùng để chọn 1 trong 2 lựa chọn, nhưng nếu phải chọn nhiều 
 hơn 2 lựa chọn thì dùng Hàm IF theo kiểu lồng nhau. 
 Ví dụ: =IF(D4<5,"yếu",IF(D4<=6.5,"TB",IF(D4<8,"Khá","Giỏi))) 
AND(đk1,đk2,): - Hàm trả về giá trị TRUE (đúng) nếu tất cả các biểu thức điều kiện đều đúng 
 Trả về FALSE (sai) khi có it nhất 1 bt điều kiền sai. 
 VÍ dụ: =AND(9>7,8>5) cho kết quả TRUE 
 Ví dụ: =AND((>7,8<5) cho kết quả: FALSE 
OR(đk1,đk2,): - Cho giá trị đúng khi có bất kỳ một đk trong dang sách cho giá trị đúng. 
 Ví dụ: =OR(2>9,9>8) cho kết quả: TRUE 
 Ví dụ: =OR(8>9,9<6) cho kết quả: FALSE 
NOT(đk): - Cho giá trị đúng nếu điều kiện sai, cho giá trị sai nếu điều kiện đúng. 
 Ví dụ: =NOT(5>9) cho kết quả: TRUE 
 Ví dụ: =NOT(5<9) cho kết quả: FALSE 
LEFT(text,N): - Cho giá trị là chuỗi con của chuỗi text được tính từ Trái sang phải N ký tự. 
 Ví dụ: =LEFT("ABCD",2) cho kết quả: AB 
RIGHT(text,N): - Cho giá trị là chuỗi con của chuỗi text được tính từ Phải sang trái N ký tự. 
 Ví dụ: =RIGHT("ABCD",2) cho kết quả: CD 
MID(Text,bắt đầu,cần 
lấy): - Lấy số ký tự trong chuỗi Text từ vị trí bắt đầu (Tính từ trái sang phải). 
 Ví dụ: =MID("ABCD",2,2) cho kết quả: BC 
LEN(text): - Cho độ dài của chuỗi text 
 Ví dụ: =LEEN("ABCD") cho kết quả: 4 
LOWER(text): 
 - Chuyển chuỗi text thành chữ THƯỜNG 
 Ví dụ: =LOWER("QUANG HUY") cho kết quả: quang huy 
UPPERT(text): - Chuyển chuỗi text thành chuỗi chữ HOA 
 Ví dụ: =UPPERT("quang huy") cho kết quả: QUANG HUY 
PROPER(text): - Chuyển các ký tự đầu của chuỗi text thành chữ HOA 
 Ví dụ: =PROPER("quang huy") cho kết quả: Quang Huy 
COUNTA(phạm vi): - Đếm số ô có chứa dữ liệu trong danh sách 
 Ví dụ: =COUNTA(C4:C8) cho kết quả: 4 
COUNTIF(phạm vi,đk): - Đếm số ô thoả mãn điều kiện trong phạm vi 
 Ví dụ: =COUNTIF(C6:C9,"QUANG HUY") nếu từ C6:C9 có 2 QUANG HUY 
 thì kết quả: 2 
 Chú ý: Trường hợp điều kiện là một con số chính xác thì các trường hợp 
 còn lại đều phải bỏ điều kiện trong dấu ngoặc kép. 
 Ví dụ: đếm số nhân viên có LƯƠNG là 1000000: =COUNTIF(C6:C9,1000000) 
 Vd: đếm số NV có LƯƠNG lớn hơn 1000000: =COUNTIF(C6:C9,">1000000") 
SUMIF(vcđk,đk,vctt): 
- Hàm dùng để tính tổng có điều kiện.Chỉ những ô nào trên vùng chứa điều 
kiện thoả mãn đk thì sẽ tính tổng những ô tương ứng trên vùng cần tính tổng. 
 Ví dụ: =SUMIF(C6:C12,"QUANG HUY",E6:E12) 
Bùi Văn Huy Đại Học Sư Phạm Hà Nội 2 
Address: Đông Xuân - Quốc Oai - Hà Nội Phone: 0984 961 848 
NHỮNG THÔNG BÁO LỖI THƯỜNG GẶP TRONG EXCEL 
Thông Báo Mô Tả 
##### Thông báo này xác định kết quả phép tính là quá lớn không vừa một ô, nó không phải là lỗi chỉ cần nới rộng cột ra là được. 
#DIV/0! Lỗi này xảy ra khi thực hiện phép chia cho 0. Excel xem những ô trống là 0, 
nếu gặp lỗi này hãy sửa lại công thức. 
#N/A 
Đây không phải là lỗi. Thực ra, nó không phải là thông báo từ Excel. Đây chỉ 
là thông báo khi chúng ta không có giá trị thực nhập vào, nếu để ô trắng 
Excel hiểu giá trị nó là 0. Nếu đánh văn bản vào trong ô sẽ gặp lỗi khác 
(#VALUE!). 
#NAME? 
Đây là lỗi xác định rằng bạn đã dùng một tên mà Excel không nhận ra, có lẽ 
bạn đã đánh sai chính tả. Chỉ cần suae công thức chỉ chứa những tham chiếu 
mà Excel có thể nhân ra. 
#NUM! 
Xác định rằng ô đã chứa 1 số quá lớn, không giống như lỗi #####, lỗi này đại 
diện cho 1 giá trị quá lớn không trình bày được. #NUM! Cho chúng ta biết 
giá trị vượt ra ngoài biên Excel không thể tính toán được. Chỉ cần tạo lại công 
thức để tạo ra kết quả thấp hơn để nhìn được. 
#REF! 
Xảy ra khi tham chiếu trong ô công thức không còn giá trị nữa (có thể đã xoá 
ô dữ liệu nguồn công thức). Lỗi này cũng xuất hiện trong bất cứ ô nào mà chỉ 
tới một ô với giá trị #FREF!. Tất cả những tham chiếu ô bị xoá phải được 
thay đổi thành ô hợp lệ. 
#VALUM! 
Thông điệp này xúa hiện khi chúng ta dùng những loại giá trị sai trong công 
thức (ví dụ: như thêm chữ số vào). Kiểm tra tham chiếu ô và xác định rằng tất 
cả những ô đã được định dạng đúng cho hàm và công thức. 
Ý NGHĨA CỦA CÁC MÃ ĐỊNH DẠNG TRONG EXCEL 
Mã định 
dạng Ý nghĩa 
0 Đại diện cho ký số, kể cả số 0 vô nghĩa ở bên phải hay bên trái 
# Đại diện cho ký số, không kể số 0 vô nghĩa ở bên phải hay bên trái 
? Đại diện cho ký số, thay số 0 vô nghĩa ở bên phải hay bên trái bằng dấu cách 
. Dấu chấm thập phân 
% Tỷ lệ % 
, Dấu phân cách hàng ngàn 
$ - + ( ) Đại diện cho chính nó 
m Tháng (bỏ qua số 0 đứng trước) 
mm Tháng (hai chữ số, kể cả số 0 đứng trước) 
mmm Tháng (Ba ký tự tắt) 
mmmm Tháng (ghi tên đầy đủ) 
d Ngày (bỏ qua số 0 đứng trước) 
dd Ngày ( hai chữ số, kể cả số 0 đứng trước) 
yy Năm (hai chữ số) 
yyyy Năm (bốn chữ số) 
[COLOR] Xác định màu của số 
Bùi Văn Huy Đại Học Sư Phạm Hà Nội 2 
Mail: quanghuyhn85@yahoo.com.vn 
Thí dụ: 
#,##”đồng” [RED] #,##”đồng”;0;”phải là số mới đúng” 
Nghĩa là: 
- Nếu là số dương, hiện với dạng số có phân cách hàng nghìn, làm tròn số nguyên, thêm 
chữ “đồng” phía sau. 
- Nếu là số âm, hiện như số dương nhưng với màu đỏ 
- Nếu là số 0 hiện 0 
- Nếu là Text, hiện câu “phải là số mới đúng” 
Số nhập vào Nóm 
(Dmtegory) 
Mã (Format code) 
1250 -1250 0,75 
Number 0 1250 -1250 1 
0.00 1250.00 -1250.00 0.75 
#,##0 1,250 -1,250 1 
#,##0.00 1,250.00 -1,250.00 0.75 
#.##0; [RED](#,##0) 1,255 (1,255) 1 
Cnrrency $#,##0;($#,##0) $1,250 ($1,250) $1 
Percentage 0% 125000% -125000% 75% 
0.00% 125000.00% -125000.00% 75.0%0 
Fraction #?/? 1250 -1250 3/4 
Sctentific 0.00E+00 1.25E+03 -1.25E+03 7.50E-01 
CÁCH ĐỊNH DẠNG NGÀY THÁNG TRONG EXCEL 
Định dạng Ví dụ 
d/m/yy 25/12/08 
d-mmm-yy 25-Dec-08 
d-mmm 25-Dec 
mmm-yy Dec-08 
m/d/yy h:mm 25/12/08 0:00 
h:mm AM/PM 8:35 PM 
h:mm:ss AM/PM 3:35:15 PM 
dd-mm-yy 20-13-08 
h:mm 20:45 
h:mm:ss 2:15:25 
m/d/yy h:mm 05/06/08 20:45 
*------------------------------- MY APPLICATIONS ----------------------------------* 
=IF(RIGHT(B7,1)=1,VLOOKUP(MID(B7,2,2),D$23:F$30,2,0)*0.95,VLOOKUP(MID(B7,2,2)
,D$23:F30,2,0)*1.05) 
=IF(OR(AND(RIGHT(B7,1)=1,H7>,AND(RIGHT(B7,1)=2,H7>7)),”G”,”K”) 
=IF(NOT(OR(B2=”A”,B2=”B”)),C2*10/100,C2*20/100) 
=IF(OR(B2)=”Y”,C2=3,E2>=3),”TT”,”T”) 
=VLOOKUP(DAYS360(D7,E7),F$22:G$25,2,0) 
Bùi Văn Huy Đại Học Sư Phạm Hà Nội 2 
Address: Đông Xuân - Quốc Oai - Hà Nội Phone: 0984 961 848 
=DSUM(A2:E7,5,G3:G4) 
=DMAX(A2:E7,5,G3:G4) 
=DMIN(A2:E7,5,G3:G4) 
=DCOUNTA(A2:E7,5,G3:G4) Đếm những ô không chứa dữ liệu. 
=COUNTBLANK(E3:E10) đếm những ô không có dữ liệu. 
=DAYS360(A3,B3) 
 DAYS360 = (START-DATE,END-DATE,MOTHD) 
=VALUE(MID(B3,2,2)) 
=SUMPRODUCT(vung CSDL1, vung CSDL2) 
 vd: SUMPRODUCT({1,2,3},{5,10,15}) 
=ROUND(AVERAGE(B3:E3),1) 
=HLOOKUP(X,vung CSDL,N,0): Dò những giá trị X trong vùng CSDL và lấy giá trị ở dòng N 
để điền vào. 
= DAVERAGE(A3:E25,E3,G3:G4) 
=CONCATENATE(A1,D1,C1) N ỗi ch ữ ở 3 ô A1,B1,C1 l ại v ới nhau. 
=CONCATENATE(A1,” ”,b2,” ”,C1) – Nối 3 ô lại và cách nhau 1 dấu cách (có thể dùng &). 
=PMT(% x,y): Tính giá trị lãi phải trả. 
=MOD(DAYS360(A,B),7) Tính số ngày dư trong các tuần. 
=MID(DAYS360(A,B)/7) Tính số tuần. 
=IF(D7=”A”,VLOOKUP(C7,H$21:I$22,2), 
IF(D7=”B”,VLOOKUP(C7,H$24:I$26,2),VLOOKUP(C7,H$29:I$31,2))). 
=D8*IF(E8>25.(E8-25)*2+25,E8). 
=IF(E6=”3”,1, IF(E6=”2”,0.5,0). 
=IF(AND(RIGHT(A6,1)=”S”,E6>20),(E6*F6)/5%,E6*F6). 
=MATCH(n,khoi,r) Tra về số thứ tự n trong khối. 
 VD: =MATCH(“chi”,B2:B10,0). 
=VLOOKUP(LEFT(A7,2),B$18:E$21,IF(RIGHT(A7,1)=”S”3,4),0).