CÔNG THỨC HÀM TRÊN EXEL

Chương VI: Công thức và hàm trong Excel
I. Xây dựng công thức
Chọn ô cần ®Æt kết quả
Gõ dấu “=“
Sử dụng các to¸n tö sè häc: +,-,*,/, ^,các hàm tính toán…,và (,) thứ tự tính toán nh­ trong to¸n häc, c¸c phÐp so s¸nh .
Tham số trong công thức có thể là giá trị cụ thể (=2*2) hoặc các tham chiếu ®Þa chØ (=c2*b2)
Có thể sao chép công thức b»ng c¸c c¸ch:
Edit/ Fill
Edit/ Copy; Edit Paste
§­a trá vÒ mÐp d­íi ph¶i « ®· tÝnh to¸n, kÝch rª vµ kÐo chuét theo phÝa c¸c « cÇn tÝnh cßn l¹i
II. Một số hàm cơ bản
Cách chèn hàm:
Chọn nơi cần chèn
Chọn Insert/ Function
Chọn loại hàm
Chọn tên hàm
Kích OK để đóng hộp thoại
1. Loại hàm Math & Trig
ABS(x): lấy giá trị tuyệt đối x
Sum(dãy số): tính tổng của dãy số
SumIf(vùng điều kiện,điều kiện,vùng tính tổng): tính tổng có điều kiện
Round(gt,n): làm tròn gt tại vị trí n
Sqrt(gt): Tính căn bậc hai của gt
Int(gt): cho phần nguyên của gt
Mod(gt1,gt2): cho số dư của phép chia gt1 cho gt2
2. Hàm Statistical (hàm thống kê)
Hàm Count(danh sách các giá trị): đếm các giá trị là số nằm trong danh sách các giá trị, không đếm các ô trống
CountA(danh sách giá trị): đếm các giá trị khác rỗng trong danh sách
CountIf(vùng đếm, điều kiện): đếm theo điều kiện(điều kiện đặt trong "")
H�m Min(gt1,gt2,..), Max(gt1,gt2,.): Tìm giá trị nhỏ nhất,lớn nhất của dãy gía trị
Hàm Rank( gt, dãy số, cách xếp):
- Dãy số: các giá trị cần xếp hạng ở dạng địa chỉ tuyệt đối, chẳng hạn $A$1:$A$10
- Cách xếp hạng: nhận 2 giá trị:
Bằng 1: giá trị lớn nhất sẽ có thứ hạng cao nhất
Bằng 0: giá trị lớn nhất sẽ có thứ hạng thấp nhất
- Nếu có 2 phần tử bằng nhau về giá trị thì sẽ có cùng một thứ hạng
Hàm trung bình cộng: Average(gt1,gt2..).
3. Hàm Date & Time
Hàm Now(): cho giá trị ngày giờ hiện thời
Hàm Today():Trả về ngày/tháng/năm hiện thời
Hàm Date(y,m,d): Cho giá trị ngày/tháng/năm theo dạng m/d/y
Hàm Day(N): Trả về ngày của N
Hàm Month(N): Trả về tháng của N
Hàm Year(N) : Trả về năm của N
4. Nhóm hàm Lôgic
Hàm If(ĐK,GT1,GT2): Nếu ĐK đúng thì hàm cho giá GT1, ngược lại hàm If trả GT2
Hàm AND(đk1,đk2.)
Trả giá trị True nếu đk1, đk2,.đều đúng
Trả giá trị False nếu có 1 đk nào đó sai
c. Hàm OR(đk1,đk2.)
Trả giá trị True nếu có 1 đk nào đó đúng
Trả giá trị False nếu tất cả đk đều sai
d. Hàm NOT(đk): phủ định lại giá trị lôgic của đk
Chú ý
Hàm And nhận tối đa 30 đk
Hàm If có thể lồng nhau 8 vòng
5. Các hàm văn bản
Hàm Left(Text,n): lấy n kí tự trái của Text
Hàm Right(Text,n): lấy n kí tự phải của Text
Hàm Mid(Text,m,n): cho n kí tự kể từ kí tự thứ m của Text
Hàm Len(Text): cho số kí tự của biểu thức văn bản, kể cả ký tự trắng - dấu cách
Hàm Lower(Text): chuyển Text thành chữ thường
Hàm Upper(Text): trả về chữ hoa cho Text
Hàm Proper(Text): cho Text có chữ đầu của các từ là chữ hoa.
Hàm Replace(Text1,m,n, Text2): Text1 được thay thế n kí tự từ kí tự thứ m bằng Text2
Hàm Rept(Text,n): Text sẽ được lặp n lần
Hàm Search(Text1, Text2): cho vị trí bắt đầu xuất hiện của Text1 trong Text2
Hàm Trim(Text): Text bị bỏ các dấu cách đầu và cuối
Ghép văn bản:
sử dụng toán tử &
vd: ="tin"& " " & "học" ? tin học
Sử dụng hàm Concatenate(Text1, Text2,.)
Hàm Value(Text): chuyển Text thành số
6. Hàm tham chiếu và tìm kiếm
Hàm Choose(n, gt1,gt2,gt3...,gtn): lấy ra giá trị thứ n trong danh sách gt1,gt2,.,gtn
Hàm Match(gt,vùng tìm,dạng): trả về thứ tự của phần tử đầu tiên trong vùng tìm trùng với gt
"Dạng" ; nhận các giá trị
1: thì vùng dữ liệu phải xếp tăng dần
0: vùng dữ liệu không cần săp xếp
-1: vùng dữ liệu phải sắp xếp giảm dần
H�m Index
D?ng 1: Index(vựng c?n tỡm, dũng, c?t)
D?ng 2: Index(cỏc vựng c?n tỡm, dũng, c?t, s? tt vựng)
H�m cho giỏ tr? c?a ụ ?ng v?i dũng c?t c?n tỡm
Hàm Vlookup
Vlookup (giá trị tìm, vùng tìm,cột lấy trị, kiểu tìm kiếm)
Ví dụ: Vlookup(C7,$D$15:$F$18,2,0)
Kiểu tìm kiếm: Có 2 giá trị
Bằng 0: Vùng tìm không cần sắp xếp
Bằng 1: Vùng tìm nên được sắp xếp
Hàm Hlookup
Hlookup(Giá trị tìm, vùng tìm, dòng lấy trị, kiểu tìm kiếm)
Ví dụ: Hlookup(E6,$D$14:$F$15,2,0)
Che dấu công thức của bảng tính
Bước 1: Chọn bảng tính cần thao tác
Bước 2: FormatCellProtection
Bước 3 : Chọn Hide
Bước 4: Tool/Protection/Protect Sheet
Bước 5: Nhập mã bảo vệ vào ô Password sau đó nhập lại Password ban đầu một lần nữaOK
7. Hàm tài chính
Hàm PV(lãi xuất%, tổng số kì, tiền nhận theo kì): Tính số tiền phải đầu tư ban đầu với các định kì nhận tiền (sau khi hết tổng số kì, tài khoản rỗng).
Hàm PV(lãi xuất%, tổng số kì, tiền nhận theo kì, tiền tương lai, kiểu thanh toán): tính số tiền phải đầu tư với các định kì thanh toán (sau thời gian tổng số kì, tài khoản còn lại bằng tiền tương lai).
Kiểu =0: thanh toán cuối kì
Kiểu =1: thanh toán đầu kì
Hàm FV(lãi xuất%, tổng số kì,- tiền đầu tư kì, - vốn ban đầu, kiểu thanh toán): tính giá trị tương lai của vốn đầu tư.
Hàm PMT(lãi xuất% tháng, số tháng thanh toán, số tiền vay) hàm tính tiền trả góp (trả hàng tháng) cho một khoản vay.
Hàm NPER(lãi xuất%, - số tiền trả mỗi kì, số tiền vay): hàm tính số kì hạn trả tiền.
Database Function - hàm cơ sở dữ liệu
Tính tổng có điều kiện với hàm Dsum
Cú pháp
Dsum(Vùng dữ liệu, số thứ tự cột cần tính tổng, vùng điều kiện)
Chú ý: Vùng dữ liệu bao gồm cả cột xét điều kiện và cột cần tính tổng
Số thứ tự của cột cần tính tổng là thứ tự của cột đó trong vùng dữ liệu
Vùng điều kiện: Có thể do người sử dụng tự thiết kế
Ví dụ:
Tính tổng lương của những người có chức vụ là NV
Lưu ý: Nếu 2 hay nhiều điều kiện là đồng thời thì ta viết các điều kiện đó trên hai cột khác nhau, nếu là không đồng thời thì chúng ta viết trên cùng một cột nhưng ở những dòng khác nhau.
Ví dụ: Tính tổng lương của những người có Chức vụ là GĐ và bậc lương là 1
Tương tự ta cũng có các hàm
Tính giá trị trung bình có điều kiện: Daverage()
Tìm giá trị lớn nhất có điều kiện: Dmax()
Tìm giá trị nhỏ nhất có điều kiện: Dmin()
Đếm các bản ghi là số có điều kiện: Dcount()
Đếm các bản ghi khác rỗng có điều kiện: DCountA()