MicroSoft Excell 03

Chương V
HÀM VÀ VIỆC SỬ DỤNG HÀM TRONG EXCEL
1. Hàm là gì ?
Hàm là những công thức mà Excel đã xây dựng sẵn mà có thể tiết kiệm thời gian khi Bạn cần làm những tính toán phức tạp. Chẳng hạn, nếu bạn cần tìm giá trị trung bình của năm ô bình thường có thể dẫn đến một công thức như : =( C1 + C2 + C3 + C4 + C5) / 5.
Nhưng Excel có hàm (AVG) sẽ tính giá trị trung bình chỉ cần bạn viết công thức sử dụng như (= AVG(C1:C5)).
Excel có khoảng trên 500 hàm viết sẵn !
2. Phạm trù các hàm trong Excel
Những hàm tài chính (Financial) giải quyết vấn đề lưu thông tiền tệ, như sự quan tâm thận trọng về vay tiền hoặc thời hạn vay tiền ...
Những hàm ngày tháng và thời gian (Date and Time) là những hàm thực hiện những tác vụ để đặt ngày tháng và thời gian hiện thời trên bảng tính hoặc kéo ra những chuỗi số ngày tháng hoặc thời gian cho mục đích tính toán.
Những hàm toán học và chuẩn (Math and Trig) là những hàm giúp bạn tính toán logarit, mũ, trị tuyệt đối, làm tròn số, lấy phần nguyên, số mũ, và những phương trình lượng giác ( cosin, sin, tang, cotang v.v...).
Thống kê (Statistical) là hàm làm những tính toán hàng ngày như tính tổng, tính các trị trung bình và sự đếm.
Những hàm tham chiếu (Lookup và Ref) là hàm lại làm việc với những bảng lookup mà bạn tạo ra bên trong bảng tính của bạn. Chúng cũng kéo thông tin từ những bảng đó vào trong những công thức của bạn.
Những hàm cơ sở dữ liệu (DataBase) được sử dụng để thông báo những lỗi trong những công thức và những giá trị.
Những hàm xử lý văn bản (Text) giúp đỡ bạn thao tác những chuỗi văn bản bởi việc lấy ra những phần hoặc bởi việc chuyển đổi vài phần văn bản phục vụ cho công việc khác.
Những hàm lôgic (Logical) cho phép bạn làm việc với những lệnh có điều kiện chọn lựa dữ liệu .
Những hàm thông tin (Informational) được sử dụng để thông báo những lỗi trong những công thức và những giá trị.
2. Cú pháp hàm
Có vài quy tắc mà khi viết những công thức và những quy tắc này được xem như những quy tắc cú pháp. Bạn phải đi theo những quy tắc cú pháp hoặc những công thức thì công việc sẽ không sai. Những quy tắc đó như sau :
Đi trước công thức là một dấu bằng (=).
Tên hàm phải được viết hoa như (SUM).
Nếu những hàm sử dụng những tham đối thì tham đối đi theo hàm nằm trong dấu ngoặc đơn và được tách ra bởi những dấu phẩy.
Nếu bạn sử dụng một chuỗi văn bản như tham đối thì văn bản phải trong dấu nháy kép “”.
3. Sử dụng những tham đối
Một tham đối trong một hàm là thông tin mà bạn cung cấp theo thứ tự cho hàm để thực hiện đúng cách. Một tham đối có thể là một số, một công thức, một địa chỉ ô, một miền, một chuỗi văn bản hoặc một hàm khác.
4. Vào những hàm trong những ô
Bạn có thể nhập hàm trực tiếp bằng tay bởi việc gõ nó trên thanh công thức, Bạn có thể chèn hàm bởi việc sử dụng những lệnh đơn để được giúp đỡ vào hàm và sau đó hoàn thành những tham đối. Để chèn một hàm trong một ô :
1. Click vào ô nơi bạn muốn sử dụng hàm.
2. Vào Insert chọn Function.
3. Trong mục Function Category ( Phạm trù hàm ) hãy chọn kiểu hàm bạn muốn dùng.
4. Click hàm cần dùng từ hộp danh sách hàm ( Function name ) .
5. Chọn nút lệnh OK.
6. Excel đưa ra hộp thoại hàm với dấu chèn nằm trong ô tham đối đầu tiên. Một cách nhìn tổng quan của hàm xuất hiện trong một cửa sổ trợ giúp.
7. Hãy nhập thông tin về tham đối được hàm yêu cầu.
8. Cuối cùng chọn OK.
5. Những ví dụ về sử dụng hàm
a) Sử dụng hàm tài chính (Financial)
Nhiều hàm tài chính giải quyết hình thức lãi suất và những điều kiện thanh toán cho tiền vay. Hàm được sử dụng thường xuyên là PMT. =PMT(vốn,lãi xuất, thời hạn)
Bạn sử dụng hàm này để tính toán sự thanh toán lãi xuất tiền vay hoặc đầu tư. Ví dụ chúng ta có một khỏan tiền vay 500,000đ với lãi xuất 7.2 % được trả trong 25 năm. Công thức tính là : = PMT(500000, 7.2 %, 25).
Tuy nhiên, bạn muốn xuất hiện thanh toán hàng tháng, không phải là hàng năm, vì vậy bạn cần chia cắt lãi xuất ra 12 tháng để có lợi tức hàng tháng ( từ 7.2 % là lãi suất hàng năm) và nhân lên số tháng trong một 12 có số lượng tháng qua đó những sự thanh toán sẽ được làm. Công thức sẽ là :
= PMT(500000, 7.2 %/ 12, 25 * 12).
b) Những hàm ngày tháng và thời gian
NOW(), không giống đa số các hàm, hàm NOW hàm không yêu cầu bất kỳ tham đối nào. Nó trả lại ngày tháng và thời gian hiện thời. Để làm ngày tháng xuất hiện theo ý mình Bạn sẽ phải định dạng nó.
TODAY() cũng không yêu cầu bất kỳ những đối số nào. Nó trả lại ngày tháng năm hiện thời. Để làm ngày tháng xuất hiện theo ý mình Bạn sẽ phải định dạng nó.
DATE(mm,dd,yy) nó dùng để nhập ngày tháng.
TIME(giờ,phút,giây) nó dùng để nhập thời gian.
c) Những hàm toán học
(Math and trig)
Chu vi của một đường tròn được tính toán bởi việc nhân đường kính với giá trị pi ([ Pi]).
Trong Excel giá trị của  đựơc cấp bởi hàm PI() với một sự xấp xỉ toán học 3.1415926... PI() không yêu cầu tham đối và thông thường được sử dụng phối hợp với một công thức khác. Để xác định chu vi của đường vòng tròn nơi giá trị cho đường kính trong A1, công thức = A1* PI(). Việc sử dụng một số với nhiều hàng chữ số thập phân trong một sự tính toán có thể đưa cho cho bạn một câu trả lời chính xác nhưng bạn có thể cần làm tròn mà ghi số xuống tới một đặc biệt số lượng hàng chữ số thập phân.
ABS(số)  trả về giá trị tuyệt đối của số. Ví dụ : ABS(-5)  5.
INT(số)  trả về phân nguyên của số.Ví dụ INT(5,125)   6.
MOD(số bị chia, số chia) trả về phần dư của phép chia.
Ví dụ : MOD(8,3)  2.
ROUND(số, n) trả về số có n chữ số thập phân sau dấu phảy.
ROUND(12.4566,2)  12.46. Mặc dầu làm tròn có thể lên tới 14 chữ số thập phân nhưng Excel chỉ trưng ra tối đa 9 chữ số thập phân.
SQRT(số) trả về giá trị căn bậc hai của số.
Ví dụ : SQRT(45)  6.70820.
RAND() trả về giá trị là một số ngẫu nhiên.
Ví dụ : RAND()  0.32129
d) Những hàm văn bản
Những hàm văn bản thao tác trên văn bản mà bạn gõ vào. Chẳng hạn muốn lấy một ra phần thông tin để từ đó bạn có thể làm một công việc khác. Trước hết giả sử mã của khách hàng là một chuỗi được tạo bởi ba chữ cái và 6 chữ số chỉ số điện thoại liên hệ. Muốn lấy ra chuỗi ký tự quy định đối tượng khách hàng ta dùng hàm LEFT.

LEFT(Chuỗi, số lượng ký tự trích ra từ bên trái chuỗi)
Ví dụ =LEFT(ABX856932,3)  ABX.
Kéo ra phần còn lại của số điện thoại, bạn có thể sử dụng hàm RIGHT.
RIGHT(Chuỗi, số lượng ký tự trích ra từ bên trái chuỗi)
Ví dụ =RIGHT(ABX856932,6)  856932.
MID(Chuỗi, vị trí lấy, số lượng ký tự trích ra từ bên trái chuỗi)
Ví dụ =MID(ABX856932,3,5)  X85693.
Có vài hàm hữu ích để chuyển đổi văn bản.
UPPER(chuỗi văn bản) bản chuyển đổi văn bản thành chữ hoa.Ví dụ :
= UPPER(“NguYen VAN mUoI hai”)  NGUYEN VAN MUOI HAI .
LOWER(chuỗi văn bản) chuyển đổi văn bản thành chữ thường.
Ví dụ = LOWER(“NguYen VAN mUoI hai”)  nguyen van muoi hai.
PROPER(chuỗi văn bản) chuyển đổi văn bản thành chữ cái đầu viết hoa, các chữ sau viết thường.
Ví dụ = PROPER(“NguYen VAN mUoI hai”)  Nguyen Van Muoi Hai.
TRIM(chuỗi văn bản) cắt bỏ các khoảng trắng ở đầu và cuối chuỗi văn bản.Ví dụ :
= TRIM(“NguYen VAN mUoI hai ”)  “NguYen VAN mUoI hai”
LEN(chuỗi văn bản) trả về một số chỉ độ dài của chuỗi văn bản.
Ví dụ = LEN(“NguYen VAN mUoI hai”)  19.
e) Những hàm thống kê thực thi trên miền dữ liệu
Hàm thống kê thường sử dụng nhất là hàm tổng SUM(miền dữ liệu). Hàm tổng cộng những giá trị của những ô trong “miền” được chỉ rõ, như = SUM(B12:B15) sẽ cộng những số trong những ô B12, B13, B14 và B15. Bạn có thể cũng sử dụng hàm này để thêm những giá trị trong những ô mà không phải trong một phạm vi bởi việc đặt những dấu phẩy giữa chúng, như = SUM(B12, B15, B19) mà cộng những giá trị trong những ô B12, B15 và B19. Khi cần thiết, bạn có thể thậm chí kết hợp như
= SUM(A5:A10, B12, E4) để cộng những giá trị trong những ô A5, A6, A7, A8, A9, A10, B12 và E4.
AVG(miền dữ liệu) tính giá trị trung bình cộng trong một “miền” nhiều ô. Nếu bạn có một miền gồm 6 ô, hàm AVG cộng lại những giá trị trong những ô đó và chia lấy trung bình cộng như = AVG(B1:B6).
MAX(miền dữ liệu) để tìm giá trị lớn nhất trong một miền dữ liệu.
MIN(miền dữ liệu) để tìm giá trị nhỏ nhất trong một miền dữ liệu.
SUMIF(miền xét tiêu chuẩn,tiêu chuẩn,miền chứa số liệu) tính tổng các ô trong miền số liệu thỏa mãn tiêu chuẩn phù hợp với miền tiêu chuẩn.
COUNT(miền dữ liệu) đếm số giá trị là số trong miền dữ liệu.
COUNTA(miền dữ liệu) đếm số ô không trống trong miền dữ liệu.
COUNTIF(miền dữ liệu, tiêu chuẩn) đếm số ô có chứa số liệu trong miền số liệu thỏa mãn tiêu chuẩn.
f) Những hàm Logic
Hàm AND( biểu thức logic1, biểu thức logic 2) trả về trị đúng (TRUE) nếu cả hai biểu thức thành phần là đúng, trả về trị là sai (FALSE) nếu ít nhất một trong hai biểu thức thành phần là sai.
Hàm OR( biểu thức logic1, biểu thức logic 2) trả về trị đúng (TRUE ) nếu ít nhất một trong hai biểu thức thành phần là đúng, trả về trị là sai (FALSE) nếu cả hai biểu thức thành phần là sai.
Hàm NOT( biểu thức logic) trả về trị đúng (TRUE ) nếu biểu thức logic có trị là sai, trả về trị là sai (FALSE ) nếu biểu thức logic có trị là đúng.
Hàm IF( btL1, “ trị trả về khi btL1 nhận trị đúng”, “ trị trả về khi btL1 nhận trị sai”).
g) Những hàm tham chiếu
Cú pháp : HLOOKUP(x; bảng; n; kiểu tìm)
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Tìm kiếm một giá trị x trong hàng đầu tiên của một bảng những giá trị, sau đó trả lại một giá trị trong cùng cột từ một hàng thứ n mà bạn chỉ rõ trong bảng.
Sử dụng HLOOKUP khi những giá trị so sánh của bạn được định vị trong một hàng ngang đầu tiên của một bảng dữ liệu và bạn muốn nhìn xuống một số được chỉ rõ bởi hàng thứ n.
x là giá trị sẽ được tìm thấy trong hàng đầu tiên của Bảng, nó có thể là một giá trị, một tham chiếu hoặc một chuỗi văn bản.Bảng là một bảng thông tin trong đó dữ liệu được tham chiếu, tìm kiếm.
n là số thứ tự của hàng trong Bảng tham chiếu từ đó giá trị thích ứng sẽ được trả về. Nếu n là số nhỏ hơn 1 hoặc lớn hơn số hàng trong bảng tham chiếu thì HLOOKUP báo lỗi. Nếu không tìm thấy x thì HLOOKUP trả về lỗi #N/A.
Nếu kiểu tìm là 1  TRUE, những giá trị trong hàng đầu tiên Bảng tham chiếu phải được sắp thứ tự tăng :...- 2, 1, 0, 1, 2,... hoặc A  Z.Nếu kiểu tìm là FALSE những giá trị trong hàng đầu tiên Bảng tham chiếu phải cần phải sắp thứ tự tăng.
Ví dụ : HLOOKUP(RIGHT(C3;2);$B$11:$E$15;2;0).
Cú pháp : VLOOKUP(x; bảng; n; kiểu tìm)
VLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Tìm kiếm một giá trị x trong cột đầu tiên bên trái của một bảng những giá trị, sau đó trả lại một giá trị trong cùng hàng từ một cột thứ n mà bạn chỉ rõ trong bảng. Sử dụng VLOOKUP khi những giá trị so sánh của bạn được định vị trong một cột đầu tiên bên trái của một bảng dữ liệu và bạn muốn nhìn sang một số được chỉ rõ bởi cột thứ n. x là giá trị sẽ được tìm thấy trong cột đầu tiên bên trái của Bảng, nó có thể là một giá trị, một tham chiếu hoặc một chuỗi văn bản.Bảng là một bảng thông tin trong đó dữ liệu được tham chiếu, tìm kiếm.
n là số thứ tự của cột trong Bảng tham chiếu từ đó giá trị thích ứng sẽ được trả về. Nếu n là số nhỏ hơn 1 hoặc lớn hơn số cột trong bảng tham chiếu thì VLOOKUP báo lỗi.Nếu không tìm thấy x thì VLOOKUP trả về lỗi #N/A.
Nếu kiểu tìm là TRUE, những giá trị trong cột đầu tiên Bảng tham chiếu phải được sắp thứ tự tăng :...- 2, 1, 0, 1, 2,... hoặc A  Z.Nếu kiểu tìm là FALSE những giá trị trong hàng đầu tiên Bảng tham chiếu phải cần phải sắp thứ tự tăng.
Ví dụ : (C3D3)*VLOOKUP(A3;$A$14:$D$17;3;0).
h) Những thực thi tác vụ trên một cơ sở dữ liệu
Hàm DCOUNT(CSDL; trường n; tiêu chuẩn) Đếm những ô có chứa số liệu trong một cơ sở dữ liệu mà phù hợp cho những điều kiện bạn chỉ rõ ở tiêu chuẩn. Tham đối trường để chọn. Nếu trường bị bỏ sót, DCOUNT đếm tất cả các bản ghi trong cơ sở dữ liệu mà phù hợp với tiêu chuẩn.
CSDL là phạm vi những ô chứa dữ liệu.
trường n chỉ cho biết rằng trường nào trong bảng CSDL được sử dụng trong hàm.
tiêu chuẩn là những ô mà chứa đựng những điều kiện mà bạn cần chỉ rõ.Ví dụ : DCONUT(A3:H13;F22:F24)
Hàm DSUM(CSDL; trường n; tiêu chuẩn) tính tổng các số trong trường n của CSDL mà thỏa mãn điều kiện trong bảng tiêu chuẩn.
CSDL là phạm vi những ô chứa dữ liệu cần cộng.
trường n cho biết cột nào trong bảng được sử dụng cho hàm tính tổng.
tiêu chuẩn là những ô mà chứa đựng những điều kiện mà bạn cần chỉ rõ. Ví dụ : DSUM(A3:H13;H3;F22:F24)
Cú pháp : DAVERAGE(CSDL; trường n; tiêu chuẩn)
CSDL là phạm vi những ô chứa dữ liệu cần lấy trung bình cộng.
trường n cho biết cột nào trong CSDL được sử dụng cho hàm tính trung bình cộng.
tiêu chuẩn là những ô mà chứa đựng những điều kiện mà bạn cần chỉ rõ. Ví dụ : DAVERAGE(A3:H13;H3;F22:F24)
Hàm DMAX(CSDL; trường n; tiêu chuẩn) trả lại số lớn nhất của một trường n trong một mà phù hợp với những điều kiện mà bạn yêu cầu.Ví dụ : DMAX(A3:H13;H3;F22:F24)
Hàm DMIN(CSDL; trường n; tiêu chuẩn) trả lại số lớn nhất của một trường n trong một mà phù hợp với những điều kiện mà bạn yêu cầu.Ví dụ : DMIN(A3:H13;H3;F22:F24)