Hàm (Function) được xem như là những công thức định sẵn nhằm thực hiện các yêu cầu tính toán chuyên biệt. Trên ô thực hiện, hàm sẽ cho kết quả là một giá trị hay một chuỗi hoặc một thông báo lỗi. Excel có trên 300 hàm định sẵn và được phân loại thành từng nhóm.
Với tính năng Function Wizard và có bổ sung thêm nhiều ví dụ trong cửa sổ Help, Excel sẽ giúp bạn sử dụng và khai thác hàm trong bảng tính trở nên dễ dàng và nhanh chóng hơn.
Một số hàm cơ bản
Hàm thời gian và một số hàm đơn giản thông dụng
Hàm thời gian
1. Hàm DAY()
+ Chức năng: cho giá trị ngày trong dữ liệu dạng ngày.
+ Cú pháp: = DAY ()
Ví dụ:
= DAY(32501) = 24
= DAY("24- Dec-94")= 24
= DAY (B11) =24 (khi B11 chứa trị 24-dec-94).
2. Hàm MONTH()
+ Chức năng: cho giá trị tháng trong dữ liệu dạng ngày.
+ Cú pháp: = MONTH ()
Ví dụ:
= MONTH (32501) = 12
= MONTH("24-Dec-94") = 12
= MONTH (B11) = 12 (Khi B11 chứa trị 24-Dec-94)
- Hàm YEAR()
+ Chức năng: cho giá trị năm trong dữ liệu dạng ngày.
+ Cú pháp: = YEAR ()
Ví dụ:
= YEAR (32501) = 94
= YEAR ("24-Dec-94")= 94
= YEAR (B11) = 94 (khi B11 chứa trị 24-Dec-94).
4. Hàm Today()
+ Chức năng: cho kết quả là ngày hiện tại từ hệ thống ngày trong máy tính
5. Hàm DAYS360()
+ Chức năng: cho kết quả là số ngày giữa và , trong đó và là các giá trị ngày tháng.
+ Cú pháp: = days360(,)
Ví dụ: ô A1 là 1/4/98 ô A2 là 1/5/98, để tính khoảng thời gian giữa ô A1 và ô A2, bạn viết:
=Days360(A1,A2) có kết quả là 30
Hàm trên tương đương đương với bạn nhập công thức:
=A2-A1
6. Hàm NOW()
+ Chức nă ng: cho giá trị ngày, tháng và thời gian hiện thời.
Ví dụ:
Nhập = NOW() trên ô A5 = → 7/25/94 15:10
7. Các hàm HOUR(), MINUTE() và SECOND()
Các hàm Hour(), minute() và second() cho phép bạn lấy ra giá trị của giờ, phút và giây của dữ liệu là ngày tháng hoặc thời gian
Một số hàm thông dụng
1. Hàm SUM()
+ Chức năng: Tính tổng các giá trị số trong phạm vi.
+ Cú pháp: = SUM(danh sách các giá trị).
Ví dụ:
= SUM(A1:A5) (Tính tổng số trong phạm vi từ A1 đến A5).
= SUM(A1:A5,C6:C18)(tính tổng số trong phạm vi từ A1 đến A5 và từ C6 đến C8).
Lưu ý:
* Khi ở trong phạm vi chỉ định có ô chứa giá trị #VALUE! thì hàm sum sẽ cho trị #VALUE!
* Ô chứa dữ liệu trong phạm vi không được tính.
* Số lượng phạm vi trong hàm từ 1 đến 30.
2. Hàm AVERAGE()
+ Chức năng: Tính trung bình cộng trong phạm vi.
+ Cú pháp: = AVERAGE(danh sách các giá trị).
Ví dụ:
= AVERAGE (B1:B5) (tính trung bình cộng trong phạm vi từ B1 đến B5).
= AVERAGE (A1:A5,C6:C18) (tính TB cộng trong phạm vi từ A1 đến A5 và từ C6 đến C8).
Lưu ý:
* Khi ở trong phạm vi chỉ định có ô chứa giá trị #VALUE! thì hàm AVERAGE sẽ cho trị #VALUE!
* Ô chứa dữ liệu chuỗi hoặc ô trống trong phạm vi không được tính.
* Số lượng phạm vi trong hàm từ 1 đến 30.
3. Hàm MAX(), MIN()
+ Công dung: Hàm Max: cho giá trị cao nhất trong phạm vi khối ô, hàm MIN cho giá trị thấp nhất trong phạm vi khối ô.
= MAX (danh sách các giá trị)
= MIN (danh sách các giá trị)
Lưu ý:
* Những dữ liệu chuỗi và ký tự trống (blank) có trong phạm vi sẽ không được tính.
* Đối với những dữ liệu dạng ngày trong phạm vi cũng được tính dựa vào trị số tuần tự của các ngày đó.
* Có thể liên kết nhiều thành phần vào trong một hàm, nhưng phải được phân cách với nhau bởi dấu phẩy.
Hàm văn bản
1. Hàm LEFT và RIGHT
+ Chức năng:
= LEFT : Trích số ký tự bên trái chuỗi dữ liệu hoặc trong toạ độ ô chứa dữ liệu.
= RIGHT : Trích số ký tự bên phải chuỗi dữ liệu hoặc trong toạ độ ô chứa dữ liệu.
+ Cú pháp:
= LEFT ( , )
= RIGHT (, )
Ví dụ:
= LEFT ( "Hà nội", 2) = Hà
= RIGHT ( "Hà nội", 2) = ội
Lưu ý:
* Chuỗi ký tự ghi trực tiếp trên hàm phải được đặt trong hai dấu nháy kép.
* Nếu số ký tự cần trích ghi lớn hơn chiều dài của chuỗi dữ liệu thì Excel chỉ ghi lại đúng độ dài của chuỗi.
* Trị số chiều dài của chuỗi dữ liệu được tính kể cả các khoảng trống trong ô.
2. Hàm MID()
+ Chức năng: Cho kết quả là một chuỗi ký tự được lấy ra trong theo nguyên tắc sau:
- Lấy ra bắt đầu từ ký tự thứ nhất
- Lấy ra bắt đầu từ ký tự thứ nhất
+ Cú pháp: = Mid(, )
3. Hàm LEN()
+ Chức năng: cho kết quả là một số biểu thị độ dài của .
+ Cú pháp:
= Len()
4. Hàm LOWER()
+ Chức năng: chuyển đổi từ thành một chuỗi ký tự là chữ thường
+ Cú pháp:
= Lower()
5. Hàm UPPER()
+ Chức năng: chuyển đổi từ thành một chuỗi ký tự là chữ hoa
+ Cú pháp:
= Upper()
6. Hàm PROPER()
+ Chức năng: chuyển đổi từ thành một chuỗi ký tự mà ký tự đầu là chữ hoa
+ Cú pháp:
= Proper()
7. Hàm REPLACE()
+ Chức năng: để lấy ra trong chuỗi ký tự từ ký tự số và lấy ra ký tự. Thay vào đó là
+ Cú pháp:
= Replace(, , ,)
8. Hàm REPT()
+ Chức năng: cho kết quả là một chuỗi ký tự được lặp lại lần + Cú pháp:
= Rept(), )
9. Hàm SEARD()
+ Chức năng: dùng để tìm chuỗi trong chuỗi . Kết quả nhận được là vị trí bắt đầu xuất hiện của trong chuỗi . Nếu bạn có chỉ thị thì việc tìm kiếm được tiến hành bắt đầu từ ký tự thứ (bt số>. Nếu không có chỉ thị này thì ngầm định bắt đầu từ ký tự thứ nhất.
+ Cú pháp:
= Search(,,)
Ví dụ:
= Search(“Hồng”,”Ngọc Hồng Hồng”) kết quả nhận đuợc là 6
Nếu bạn chỉ thị :
= Search(“Hồng”,”Ngọc Hồng Hồng”,7) kết quả nhận đuợc là 11 vì bắt đầu tìm ký tự thứ 7.
10. Hàm SUBSTITUTE()
+ Chức năng: dùng để lấy ra trong chuỗi một chuỗi ký tự là và thay
vào đó là .
+ Cú pháp:
= Substitute (,,)
Ví dụ: = Substitute(“Nguyễn Khánh Ngọc”,”Ngọc”,”Linh”) kết quả nhận được là:
Nguyễn Khánh Linh
11. Hàm TRIM ()
+ Chức năng: cho kết quả là một chuỗi ký tự được lấy từ sau khi đã bỏ các ký tự trống ở đầu và cuối chuỗi ký tự. Hàm Trim() còn bỏ bớt các vị trí trống giữa hai từ và chỉ để lại một ký tự trống.
+ Cú pháp:
= Trim()
Ví dụ:
= Trim(“Trung tâm Tư vấn”) cho kết quả là: Trung tâm Tư vấn
12. Toán tử &
+ Chức năng: dùng để nối hai chuỗi ký tự.
+ Cú pháp:
= “text 1” & “text 2
Ví dụ: ở tại ô A1 chứa “Nguyễn Khánh”, tại ô A2 chứa “ Ngọc” , tại A3 nhập công thức
= A1&A2 , bạn nhận được kết quả là: Nguyễn Khánh Ngọc
13. Hàm CONCATENATE
+ Chức năng: nối kết các chuỗi văn bản thành một chuỗi văn bản đơn
+ Cú pháp: =Concatenate(, , ... )
14. Hàm VALUE()
+ Chức năng: đổi một văn bản sang một số
+ Cú pháp: Value()(có thể chuỗi ký tự hoặc là tham chiếu một có chứa ký tự)
15. Hàm CHAR()
+ Chức năng: cho một ký tự tương ứng với mã của nó.
+ Cú pháp: Char()
16. Hàm CODE()
+ Chức năng: cho mã tương ứng với ký tự đầu tiên trong văn bản.
+ Cú pháp: =Code()
Hàm số học
1. Hàm ABS()
+ Chức năng: cho kết quả là giá trị tuyệt đối của biểu thức số
+ Cú pháp: =ABS()
2. Hàm SQRT()
+ Chức năng: cho kết quả là căn bậc 2 của với điều kiện phải lớn hơn hoặc bằng 0.
+ Cú pháp: =SQRT()
3. Hàm MOD()
+ Chức năng: cho kết quả là số dư của chia cho số n.
+ Cú pháp: =Mod (,n)
4. Các hàm làm tròn số
a. Hàm ROUND()
+ Chức năng: làm tròn một số là với giá trị thập phân.
+ Cú pháp: =Round(, )
Nếu > là dương thì làm tròn <giá trị số> theo các số lẻ (bên phải của giá trị số)
Nếu > là âm thì làm tròn <giá trị số> theo các số nguyên (bên trái của giá trị số)
b. Hàm EVEN()
+ Chức năng: làm tròn một số tới số nguyên chẵn gần nhất các số nguyên tương ứng được làm tròn xuống.
+ Cú pháp: =Even ()
Ví dụ: bạn có số 23.4 nếu sử dụng hàm Even(23.4) ta có kết quả là 24
c. Hàm ODD()
+ Chức năng: làm tròn một số tới số nguyên lẻ nhất. Các số nguyên tương ứng được làm tròn xuống.
+ Cú pháp: =Odd()
Ví dụ: bạn có số 23.4 nếu sử dụng hàm Odd(23.4) ta có kết quả là 25
d. Hàm FLORR()
+ Chức năng: cho giá trị là bội số của gần với nhất. Giá trị này được tính nhỏ hơn
+ Cú pháp: =Floor(, )
Ví dụ: =Floor(23.4,5) ta có kết quả là 20
e. Hàm CEILING()
+ Chức năng: cho giá trị là bội số của gần với nhất. Giá trị này được tính lớn hơn
+ Cú pháp: =Ceiling(, )
Ví dụ: =Ceiling(23.4,5) ta có kết quả là 25
f. Hàm INT()
+ Chức năng: cho kết quả là phần nguyên của
+ Cú pháp: =Int ()
Ví dụ: =Int(23.46) ta có kết quả là 23
g. Hàm TRUNC()
+ Chức năng: loại bỏ các giá trị sau dấu chấm thập phân
+ Cú pháp: =Trunc ()
Ví dụ: =Trunc(23.46) ta có kết quả là 23
Lưu ý: Điểm khác biệt cơ bản giữa INT() và TRUNC() là khi xử lý các giá trị âm.
Ví dụ: =Int(-100.99), cho kết quả là -101
Nhưng kết quả hàm:
= Trunc(-100.99) cho kết quả là -100
5. Hàm FACT()
+ Chức năng: cho kết quả là giai thừa của
+ Cú pháp: =Fact ()
Ví dụ: =Fact(3) có kết quả là 6 (tương ứng với công thức =1*2*3)
6. Hàm COMBIN()
+ Chức năng: cho giá trị là tổ hợp của trong
+ Cú pháp: =Trunc ()
Ví dụ: Giả sử có 6 đội bóng được tổ chức theo thể thức thi đấu vòng tròn, mỗi đội đều thi đấu một trận với đội khác. Các bạn có thể sử dụng hàm Combin() để tính xem có bao nhiêu trận bóng đá cần tổ chức, bạn nhập công thức như sau:
=Combin(6,2), cho kết qủa là 15
Hàm logic
1. Hàm AND()
+ Chức năng: hàm cho kết quả là đúng khi tất cả các điều kiện trong danh sách đều đúng.
+ Cú pháp: =And(điều kiện 1, điều kiện 2)
2. Hàm OR()
+ Chức năng: hàm cho kết quả là đúng khi ít nhất một điều kiện trong danh sách đều đúng.
+ Cú pháp: =Or(điều kiện 1, điều kiện 2)
3. Hàm NOT()
+ Chức năng: hàm cho kết quả là đúng khi điều kiện sai. Ngược lại, làm cho giá trị sai khi điều kiện là đúng.
+ Cú pháp: =Not(điều kiện)
4. Hàm IF()
+ Chức năng: hàm cho kết quả là nếu là đúng, ngược lại kết quả
nhận được là .
+ Cú pháp: =IF(,, )
Ví dụ: =IF(diem>=5,”Đỗ”,”Truợt”)
Hàm thống kê
1. Hàm COUNT()
+ Chức năng: đếm tất các ô chứa giá trị là số trong danh sách.
+ Cú pháp: =COUNT(danh sách các giá trị)
2. Hàm COUNTA()
+ Chức năng: đếm tất các ô trong danh sách chứa dữ liệu
+ Cú pháp: =COUNTA(danh sách các trị)
2. Hàm COUNTIF()
+ Chức năng: đếm từng ô không trống thoả mãn tiêu chuẩn đã định.
+ Cú pháp: =COUNTIF(,)
Ví dụ: COUNTIF( B1:B6,”>=7”) đếm tất các ô từ B1:B7 có điểm >=7
3. Hàm RANK()
+ Chức năng: tìm thứ bậc của trị số trong một phạm vi.
+ Cú pháp: = RANK(, ,<0,1> )
- số 0 ( hoặc không ghi): thứ bậc được tính theo giá trị số giảm dần.
- số 1: thứ bậc được tính theo giá trị số tăng dần.
4. Hàm SUMIF()
+ Chức năng: cộng những ô thoả mãn điều kiện nào đó.
+ Cú pháp: = SUMIF(,<điều kiện>,)
Trong đó: là tham chiếu tới một dãy ô được kiểm tra theo một <điều kiện> nào đó đã cho trước, là tham chiếu tới một dãy ô chứa giá trị tổng, <điều kiện> có thể là một số, một biểu thức, hay một chuỗi văn bản xác định ô nào đó cần tính tổng nhưng phải đặt trong nháy kép và cùng kiểu dữ liệu với kiểu dữ liệu trong, .
Ví dụ: Giả sử, bạn có bảng tính sau, sau đó bạn hãy tính tiền thưởng của đơn vị A. Tại ô E6, bạn nhập công thức =SumIf(C2:C5,”=A”,E2:E5), tương tự như vậy ta tính được cho đơn vị B, C.
| A | B | C | D | E |
| 1 | TT | Họ tên | Đơn vị | Số công | Tiền thưởng |
| 2 | 1 | Lê Tuấn Đông | A | 28 | 560000 |
| 3 | 2 | Trần Anh Toàn | C | 25 | 125000 |
| 4 | 3 | Trần Thị Lan | A | 17 | 85000 |
| 5 | 4 | Nguyễn Thanh Tú | B | 27 | 270000 |
| 6 | | | | | |
Hàm tìm kiếm & tham chiếu
1. Hàm VLOOKUP()
+ Chức năng: Thực hiện việc tìm kiếm trị x trên cột chỉ mục (cột đầu tiên) của bảng tham chiếu và cho kết quả tương ứng trong cột tham chiếu chỉ định.
+ Cú pháp:
VLOOKUP( x, Bảng t/c, cột tham chiếu, cách dò)
x : giá trị tìm kiếm có thể là một chuỗi hoặc một số hoặc toạ độ ô chứa dữ liệu số hay chuỗi hoặc biểu thức có kết quả là một giá trị hay chuỗi.
Bảng: Là một khối các ô, thường gồm nhiều hàng và nhiều cột. Cột bên trái luôn luôn chứa các trị để dò tìm, các cột khác chứa các trị tương ứng để tham chiếu.
Cột tham chiếu: Là thứ tự của cột ( tính từ trái của bảng trở qua), cột đầu tiên của bảng là cột 1.
Cách dò: Là số 0 hoặc số 1, ngầm định là 1.
- Nếu cách dò là 1:
. Danh sách ở cột bên trái của bảng phải xếp theo thứ tự tăng dần.
. Nếu trị dò x nhỏ hơn phần tử đầu tiên trong danh sách, làm cho trị là #N/A ( bất khả thi )
. Nếu trị dò lớn hơn phần tử cuối cùng trong danh sách, xem như tìm thấy ở phần tử cuối cùng.
. Nếu trị dò x đúng khớp với một phần tử trong danh sách ( không phân biệt chữ hoa hay thường nếu là chuỗi ), đương nhiên tìm thấy ở tại phần tử đó và cho trị là trị của ô nằm trong cột tham chiếu cùng hàng với phần tử này.
- Nếu cách dò là 0:
. Danh sách ở cột bên trái của bảng không cần thiết phải xếp theo thứ tự.
. Nếu trị dò x không đúng khớp với bất kỳ phần tử nào trong danh sách ( không phân biệt chữ hoa hay thường nếu là chuỗi ) hàm cho trị là #N/A ( bất khả thi )
. Chỉ khi nào trị dò x đúng khớp với một phần tử trong danh sách ( không phân biệt chữ hoa hay chữ thường nếu là chuỗi ), mới cho trị là trị của ô nằm trong cột tham chiếu cùng hàng với phần tử này.
Ví dụ: Tính lương cho ba loại công lao động khác nhau, biết rằng, số tiền cho từng loại công lao động là: loại A: 20000 đồng/ công, loại B: 10000 đồng/công, loại C: 5000đồng/công. Tiền lương được tính theo công thức: Tiền lương = số công × số tiền một công.
Ta thực hiện như sau:
- Tạo bảng gồm 2 cột, cột A chỉ các loại công lao động (A,B,C) và cột B chỉ số tiền công tương ứng.
- Chọn ô E6, lập công thức: = VLOOKUP(C6,$A$1:$B$3,2,1)*D6
Copy công thức này xuống các ô E7, E8,...
Ta có kết quả:
Chú ý, trong công thức ta dùng địa chỉ tuyệt đối $A$1:$B$3 để đảm bảo địa chỉ này không bị thay đổi trong quá trình Copy.
2. Hàm HLOOKUP()
+ Chức năng: Thực hiện việc tìm kiếm x trên dòng chỉ mục (dòng đầu tiên) của bảng tham chiếu và cho kết quả tương ứng trong dòng tham chiếu chỉ định.
+ Hlookup(x, Bảng t/c, Hàng tham chiếu, Cách dò)
Mọi nguyên tắc hoạt động của hàm HLOOKUP này giống như hàm VLOOKUP, chỉ khác là hàm VLOOKUP dò tìm ở cột bên trái, tham chiếu số liệu ở các cột bên phải, còn hàm HLOOKUP dò tìm ở hàng trên cùng, tham chiếu số liệu ở các hàng phía dưới..
- Hàm ADDRESS()
+ Cú pháp: =Address(,,,,)
+ Chức năng: dùng để tham chiếu tới một địa chỉ theo các số, trong đó , là các số chỉ thứ tự của dòng và cột của ô cần tham chiếu.
- Nếu là True (đúng) thì ô tham chiếu sử dụng theo địa chỉ tuyệt đối.
- Nếu là True thì địa chỉ là tuyệt đối theo dòng và cột.
- Nếu là False thì địa chỉ là tuyệt đối theo địa chỉ R1C1
Bạn có thể chỉ định thêm tên bảng tính nếu cần tham chiếu sang một bảng tính khác. Nếu chỉ cần tham chiếu tới các ô trong bảng tính hiện thời thì không cần tham số này.
Ví dụ: =Address(3,2,True,True) kết quả là: $B$3, nếu bạn chỉ thị
=Address(3,2,True,False) thì kết quả nhận được là R3C2
- Hàm CHOOSE()
+ Chức năng: dùng để lấy ra một giá trị trong danh sách các giá trị được chỉ định
+ Cú pháp: = Choose(,)
Trong đó: là vị trí cần lấy giá trị trong danh sách các giá trị.
Ví dụ: giả sử bạn có bảng tính: nếu trong ô C5, bạn nhập công thức:
| A | B | = Choose(2,B1,B2,B3,B4) |
| 1 | | Bàn | Bạn sẽ nhận được giá trị là: Ghế |
| 2 | | Ghế | Bạn cũng cần lưu ý rằng, nếu bạn thay danh sách |
| 3 | | Tủ | các giá trị sẽ bị báo lỗi. Cụ thể là bạn đưa vào công |
| 4 | | Bàn là | Thức =Choose(2,B1:B4) thì không hợp lệ. |
- Hàm MATCH()
+ Chức năng: đưa ra vị trí tìm thấy (hoặc gần đúng nếu không tìm thấy) của giá trị cần tìm thấy trong .
+ Cú pháp: = MATCH(,,)
Trong đó:
là giá trị dạng số hoặc ký tự được dùng để tìm kiếm trong
xác định cách thức tìm kiếm và phải là một trong 3 giá trị 1, 0 và -1 (đay là giá trị ngầm định nếu bạn không chỉ thị tham số này).
- Hàm INDEX()
a. Dạng tìm kiếm theo tham chiếu
+ Cú pháp: =INDEX(,,)
+ Chức năng: đưa ra giá trị của một ô trong được xác định bởi và
b. Dạng tìm kiếm theo dãy
+ Cú pháp: =INDEX(,,)
+ Chức năng: đưa ra giá trị của một ô trong được xác định bởi và
Ví dụ: giả sử ta có bảng tính sau: nếu bạn nhập công thức
=INDEX(A2:E5,1,1) sẽ cho kết quả: Lê Tuấn Đông
| A | B | C | D | E |
| 1 | TT | Họ tên | Loại | Số công | Tiền |
| 2 | 1 | Lê Tuấn Đông | A | 28 | 560000 |
| 3 | 2 | Trần Anh Toàn | C | 25 | 125000 |
| 4 | 3 | Trần Thị Lan | D | 17 | 85000 |
| 5 | 4 | Nguyễn Thanh Tú | B | 27 | 270000 |
=INDEX({10,20,30,40,50,60}) sẽ cho kết quả: 20
Ví dụ áp dụng các hàm Match() và Index(): giả sử ta có bảng tính như trang sau:
| A | B | C | D | E | F | G |
| 1 | | Danh mục vật tư | | |
| 2 | | Mã VT | Tên VT | ĐV tính | Đơn giá | | |
| 3 | | V001 | Cassette | Chiếc | 300,000 | | |
| 4 | | V002 | Bia Hà nội | | 6,000 | | |
| 5 | | V003 | Bàn là | | 100,000 | | |
| 6 | Danh sách vật tư |
| 7 | Ngày | Mã VT | Tên VT | ĐV tính | Đơn giá | | |
| 8 | 1/8/98 | V001 | | | | | |
| 9 | 2/8/98 | V002 | | | | | |
| | | | | | | |
Dựa vào Mã vật tư, bạn hãy đưa các thông tin tương ứng của từng loại vật tư như: Tên vật tư, Đơn vị tính, Đơn giá; trong Danh mục vật tư vào Danh sách nhập vật tư.
Tại ô C8 (trong cột Tên vật tư), các bạn nhập công thức: =INDEX(B3:E5,Match(B8,B3:E5,0),2). Bạn sẽ nhận được kết quả là: Cassette. Trong đó hàm Match(B8,B3:E5,0) cho kết quả là 1, bởi vì tìm thấy giá trị của ô B8 (V001) nằm ở trong dòng 1 của vùng B3:B5 và như vậy, công thức trở thành:
= Index(B3:B5,1,2); kết quả nhận được là ô trên dòng 1 cột 2 của vùng B3:B5, đây chính là ô C3 như nói ở trên. Tương tự như vậy, bạn sao chép công thức này sang các ô còn lại của cột Tên vật tư. Đối với cột Đơn vị tính, bạn nhập công thức: =Index(B3:E5,Match(B8,B3:E5,0),3).
Trong công thức trên, số 3 để chỉ việc xác định ô cần lấy giá trị trên ô thứ ba của vùng B3:B5, đây chính là cột ĐV tính.
Một số hàm để phân tích tài chính
Excel cung cấp 6ba1 rất nhiều hàm nhưng chỉ đề cập đến ba hàm tài chính hay được sử dụng nhất là PMT, FV và RATE. Sử dụng các hàm này bạn có thể tính được số nợ phải trả, giá trị đầu tư trong tương lai và lãi suất của đầu tư.
1. Hàm PMT()
+ Chức năng: Hàm PMT tính thanh toán định kỳ cần thiết để trả dần nợ vay sau số kỳ hạn đã định.
+ Cú pháp: =PMT(rate, number of periods, present value, future value, type)
Trong đó: rate: mức lãi suất, number of periods: thời hạn phải trả, present value: giá trị hiện tại, future value: giá trị tương lai, type: thanh toán đầu kỳ nếu type=1, nếu type=0 thanh toán cuối kỳ).
+ Ví dụ: bạn vay một khoản nợ là 10000$ trong 3 năm với lãi suất là 9% năm. Số nợ phải trả hàng tháng là bao nhiêu?
Đưa con trỏ chuột vào ô bảng tính và gõ: = PMT(9%/12,36,10000) kết quả sẽ trả về (-318.00$)
2. Hàm FV()
+ Chức năng : tính giá trị tại thời điểm thanh toán trong tương lai.
+ Cú pháp: =FV(rate, number of periods, payment, present value,type)
Trong đó: rate: mức lãi suất, number of periods: thời hạn đầu tư, payment: mức đầu tư cho mỗi khoảng thời gian (cho 1 năm, hoặc 1 tháng), present value: giá trị hiện tại,type: thanh toán đầu kỳ nếu type=1, nếu type=0 thanh toán cuối kỳ).
+ Ví dụ: bạn gửi số tiền ban đầu là 2000$ với lãi suất 10% năm. Sau 30 năm bạn sẽ có một số tiền là bao nhiêu?
3. Hàm NPER()
+ Chức năng : tính số kỳ cần thiết tương lai.
+ Cú pháp: =NPER(rate, pmt, pv, fv,type)
Trong đó: rate: mức lãi suất, pmt: sô tiền trả cho mỗi kỳ, pv: giá trị hiện tại, fv: giá trị tương lai, type: thanh toán đầu kỳ nếu type=1, nếu type=0 thanh toán cuối kỳ).
+ Ví dụ: giả sử bạn vay một khoản tiền là 100000 với lãi suất 8% năm. Bạn có thể trả dần 1000 trong một tháng và bạn muốn biết trong thời gian bao nhiêu tháng?
Đưa con trỏ chuột vào ô bảng tính và gõ: = Nper((8/12)%,-1000,100000) kết quả sẽ trả về 165.34
4. Rate()
+ Chức năng : Hàm rate() cho phép bạn xác định mức lợi nhuận của một khoản đầu tư phát sinh thanh toán định kỳ hoặc thanh toán gộp.
+ Cú pháp: =RATE(number of periods, payment, present value, future value, type, guess)
Trong đó: number of periods: thời hạn đầu tư, payment: mức thanh toán cho hàng năm hoặc hàng tháng), present value: mức đầu tư ban đầu, guess: đối số tuỳ ý cho Excel điểm khởi đầu để tính toán lãi suất, nếu bạn bỏ qua Excel bắt đầu ở 0.1 (10%)).
+ Ví dụ: Giả sử bạn đang xem xét khoản đầu tư sẽ mang lại cho bạn món thanh toán hàng năm là 1000$ trong 5 năm, khoản đầu tư này tốn 3000$. Để xác định mức lợi nhuận hàng năm trên khoản đầu tư, bạn gõ vào công thức: =RATE(5,1000,-3000) trả về giá trị 20% mức lợi nhuận trên khoản đầu tư này.
5. Sử dụng lệnh Goal Seek để dự báo: lệnh Goal Seek tìm một giá trị nào đó nhằm tạo ra một kết quả mong muốn, ví dụ như lượng đĩa CD phải bán được để đạt được một doanh số 1000000$. Để sử dụng Goal Seek, hãy xác lập worksheet chứa:
- Một công thức để tính toán giá trị đích
- Một ô trống chứa giá trị cần tìm
- Các giá trị cần thiết khác cho công thức
Ô trống được tham chiếu đến trong công thức và hoạt động như một biến để Excel thay đổi. Để dự đoán bằng cách sử dụng lệnh Goal Seek, cách làm như sau:
1. Xây dựng worksheet chứa một công thức, một ô biến trống sẽ chứa giải pháp và các dữ liệu cần thiết cho phép tính toán. Ví dụ: để xác định số lượng li cà phê phải bán giá 1.75$ nhằm đạt được doanh số 30000$.
2. Trong worksheet, chọn một chứa công thức (trong hộp thoại Goal Seek ô đó được gọi là Set Cell)
3. Chọn Goal Seek từ lệnh đơn Tools, hộp thoại xuất hiện.
4. Nhấn Tab rồi gõ vào giá trị đích muốn đạt được trong mục To Value (gõ vào giá trị 30000).
5. Nhấn Tab để chọn mục By Changing Cell, bạn có thể dời hộp thoại Goal Seek đi nơi khác nếu cần thiết để chọn ô biến .
6. Chọn OK để tìm giải pháp doanh số. Lúc này, hộp thoại sẽ hiển thị thông báo sau khi đã kết thúc quá trình lặp, và kết quả của dự đoán sẽ được hiển thị trên worksheet. Như vậy, lệnh Goal Seek cho biết cần bán 17.143 ly cà phê với giá trị 1.75$ để đạt doanh số 30000$.
7. Chọn OK để đóng hộp thoại Goal Seek lại.
6. Sử dụng Solver để cân đối giữa số lượng và giá cả
Khi vấn đề cần dự đoán cần sử dụng nhiều biến, bạn nên sử dụng trình bổ sung Solver để phân tích kịch bản. Để minh hoạ cách sử dụng Solver, lấy ví dụ một tiệm cà phê có bán 3 loại thức uống: cà phê thường, cà phê latte và cà phê mocha. Giá mỗi li của chúng là 1.25$, 2$ và 2.25$. Hiện bạn chưa biết được loại cà phê nào đưa lại lợi nhuận nhiều hơn. Muốn làm được điều này, trước hết phải xây dựng được ô đích cho mục đích của vấn đề (ví dụ một công thức tính doanh thu lớn nhất ) - và gán một hay nhiều ô biến để Solver thay đổi nhằm đạt được mục đích của bạn. Bảng tính còn có thể chứa các giá trị và công thức khác sử dụng ô đích và các ô biến. Mỗi ô biến đều được ô đích sử dụng thì Solver mới có thể làm được (ô đích phải phụ thuộc vào các ô biến). Bảng dưới đây, ô G4 là ô đích để tính tổng lợi nhuận của 3 loại cà phê trên. Cả 3 dòng đều quy về ô G4, chúng cho biết công thức trong ô G4 phụ thuộc như thế nào đối với 3 phép tính toán kia. 3 ô biến D5, D9, D13 - đó là 3 giá trị bạn muốn Solver cho biết khi nó tìm giải pháp tối ưu nhằm thu được lợi nhuận tối đa hàng tuần. Từ G11:G13 là danh sách các hằng sẽ sử dụng nhằm dự đoán. Ví dụ như chỉ có thể bán 500 li cà phê mỗi tuần, trong đó số li cà phê mocha tối đa là 125 (vì còn phụ thuộc vào sô cô la để chế biến thêm vào cà phê mocha), tức 2 loại cà phê kia tối đa là 350 li. Bảng tính phải chứa các ô để chứa các giá trị hằng (trong ví dụ này là các ô g6:g8).
Muốn chạy Solver , bạn nhấn chuột vào ô đích - là ô chứa công thức dựa trên các ô biến muốn Solver giải quyết ( ô G4). Từ thực đơn Tools, chọn Solver, một hộp thoại xuất hiện. Vì ô đích đã chọn nên mục Set Cell Target sẽ chứa địa chỉ này. Chọn Max vào mục Equal to ⇒ chọn mục By Changing Cells ⇒ chọn từng ô biến, 3 ô trống D5, D9, D13 có nhiệm vụ hiển thị số li cà phê cho từng loại ⇒ nhấn lên nút Add để đưa hằng thứ nhất vào hộp thoại Constraint , nhấn lên ô G8 chọn ≤ G11 ⇒ nhấn lên nút Add để đưa hằng thứ 2, nhấn lên ô G7 chọn ≤ G12 ⇒ nhấn lên nút Add để đưa hằng thứ 3 , nhấn lên ô D13 chọn ≤ G13 ⇒ chọn OK để đưa cả 3 hằng vào hộp thoại Solver ⇒ nhấn Solver để tính toán kết quả.
| A | B | C | D | E | F | G |
| 1 | | | | | | | |
| 2 | | | | | | | |
| 3 | | | | | | | |
| 4 | | | Giá mỗi li cà phê thường là | 1.25$ | | Tổng lợi nhuận | =D6+D10+D14 |
| 5 | | | Số li sẽ bán được là | | | Cà phê thường | =D5 |
| 6 | | | Tổng | =D4*D5 | | Mocha+Latte | =D19+D13 |
| 7 | | | | | | Tổng 3 loại | =D5+D9+D13 |
| 8 | | | Giá mỗi li cà phê latte | 2.00$ | | | |
| 9 | | | Số li sẽ bán được là | | | | |
| 10 | | | Tổng | =D8*D9 | | | |
| 11 | | | | | | Tối đa cả 3 loại | 500 |
| 12 | | | Giá mỗi li cà phê mocha | 2.25$ | | Tối đa M+La | 350 |
| 13 | | | Số li sẽ bán được là | | | Tối đa Mocha | 125 |
| 14 | | | Tổng | =D12*D13 | | | |
| 15 | | | | | | | |
- Một số ví dụ ứng dụng để giải hệ phương trình 3 ẩn số
