Bai 5

Lập bảng tính với
EXCEL 2003 (B.5)
Bài 5
SỬ DỤNG HÀM TRONG TÍNH TOÁN
1-  Gíới thiệu và phân loại Hàm
2-  Cú pháp tổng quát của Hàm
3-  Cách nhập Hàm vào bảng tính
4- Một số Hàm thông dụng.
1- Giới thiệu và phân loại Hàm
Hàm (Function) là gì?
- Hàm là công cụ nhằm giải quyết một công việc nhất định. Hàm gồm 2 thành phần là tên hàm và các đối số (đối số nằm trong cặp dấu ngoặc ()), Hàm cho kết quả là một giá trị hay một thông báo lỗi.
1- Giới thiệu và phân loại Hàm
Các hàm của Excel chia thành những nhóm nào?
+ Hàm về ngày và giờ (Date & Time)
+ Hàm toán học và lượng giác (Math & Trig)
+ Hàm dò tìm và tham chiếu (Lookup & Reference)
+ Hàm xử lý chuỗi ký tự (Text)
+ Hàm thông tin (Information)
+ Hàm logic (Logic)
+ Hàm thống kê (Statistical)
+ Hàm tài chính (Financial)
+ Hàm cơ sở dữ liệu (Database)
+ Hàm kỹ thuật (Engineering).
2- Cú pháp tổng quát của Hàm
Hàm có cú pháp như thế nào?
= (đối số 1, đối số 2, ..., đối số n)
Ví dụ: =Sum(A1,A2,B1,B2)
- Cú pháp hàm gồm ba thành phần:
+ Dấu =: để excel biết theo sau là một hàm hay công thức
+ Tên hàm: theo quy ước của Excel.
+ Đối số: là các giá trị, chuỗi, tọa độ ô, tên vùng, công thức, hoặc một hàm khác.
2- Cú pháp tổng quát của Hàm
Các điểm cần lưu ý đối với cú pháp của hàm.
 Phía trước hàm phải có dấu =
 Trong hàm không được chứa khoảng trắng
 Có thể chứa tối đa 30 đối số hoặc không quá 255 ký tự
 Nếu dùng 1 hàm làm đối số cho 1 hàm khác thì hàm làm đối số không cần phải có dấu = ở đằng trước
 Các đối số phải được đặt trong cặp dấu ( ) và giữa các đối số phải được phân cách bằng dấu phẩy (,) hoặc dấu chấm phẩy (;).
3- Cách nhập hàm vào bảng tính
Làm thế nào để nhập hàm từ bàn phím?
+ Chọn ô cần nhập hàm
+ Gõ dấu = (hoặc dấu @)
+ Nhập tên hàm và các đối số (đúng cú pháp).
3- Cách nhập hàm vào bảng tính
Làm thế nào để nhập hàm từ bảng liệt kê tên hàm?
+ Chọn ô cần nhập hàm
+ Chọn lệnh Insert/Function (hoặc nhấn nút )
+ Chọn mục hàm (Function category)
+ Chọn tên hàm (Function Name)
+ Chọn nút lệnh Next để chuyển qua Function wizard
+ Nhập các đối số bằng cách:
·  Bằng bàn phím
·  Click chuột trên các ô cần chọn
+ Nhấn nút Finish.
4- Một số hàm thông dụng
Các hàm thống kê
- Hàm SUM():
+ Công dụng: Tính tổng số trong một phạm vi.
+ Cú pháp: =SUM(number1, number2, ..., numbern)
+ Các đối số:
 Number: Trị số, tọa độ ô hoặc nhóm ô.
+ Ví dụ:
=SUM(A1:A5)
=SUM(A3, B3:B6, C5:C9)
4- Một số hàm thông dụng
Các hàm thống kê
- Hàm AVERAGE():
+ Công dụng: Tính trung bình cộng trong phạm vi.
+ Cú pháp: =AVERAGE(number1, ..., numbern)
+ Các đối số:
 Number: Trị số, tọa độ hoặc nhóm ô.
+ Ví dụ:
=AVERAGE(B4:B9)
=AVERAGE(C5:C9, D7:D12)
4- Một số hàm thông dụng
Các hàm thống kê
- Hàm MAX(), MIN():
+ Công dụng:
 MAX: Cho giá trị lớn nhất trong một khối
 MIN: Cho giá trị nhỏ nhất trong một khối
+ Cú pháp:
=MAX(Block)
=MIN(Block)
+ Các đối số
 Block: Tên vùng hoặc tọa độ của một khối ô
+ Ví dụ:
=MAX(B3:B5)
=MIN(C6:C10)
4- Một số hàm thông dụng
Các hàm thống kê
- Hàm COUNT():
+ Công dụng: Đếm số ô chứa gía trị số trong một khối ô.
+ Cú pháp: =COUNT(List)
+ Đối số:  List: Phạm vi các ô ( ô hoặc khối ô).
+ Ví dụ:





=COUNT(B1:B5) = 3
=COUNT(C5) = 0
4- Một số hàm thông dụng
Các hàm thống kê
- Hàm COUNTA():
+ Công dụng: Đếm số ô chứa gía trị số hoặc chuỗi trong một khối ô
+ Cú pháp: =COUNTA(List)
+ Đối số:
 List: Phạm vi các ô (ô hoặc khối ô)
+ Ví dụ:
=COUNTA(B1:B5) = 4
=COUNTA(C5) = 0
=COUNTA(C1:C5, D2:D5) = 7
4- Một số hàm thông dụng
Các hàm thống kê
- Hàm COUNTIF():
+ Công dụng: Đếm số ô trong dãy(range) có trị thỏa điều kiện (criteria)
+ Cú pháp: =COUNTIF(range,criteria)
+ Ví dụ: =COUNTIF(B2:B5,”<500”)
- Hàm SUMIF():
+ Công dụng: Tính tổng gía trị các ô trong một dãy thỏa điều kiện (criteria)
+ Cú pháp: SUMIF(range1, criteria, range2)
+ Ví dụ: =SUMIF(B2:B5,”<500”,D2:D5)
=SUMIF(C2:C5,”<500”,C2:C5)
4- Một số hàm thông dụng
Các hàm thống kê (Statistical)
- Hàm RANK( ):
+ Công dụng: trả về thứ bậc cuả một số trong một mảng
+ Cú pháp: =RANK(Number,Reference,Order)
+ Đối số :
 Number: Số cần xác định thứ bậc.
 Reference: mảng các số.
 Order:
* Bằng 0 hoặc không có: tính theo số lớn hơn có thứ bậc đầu
* Khác 0: thì tính theo số nhỏ hơn có thứ bậc đầu.
4- Một số hàm thông dụng
Các hàm luận lý (Logic)
- Hàm IF():
+ Công dụng: Xác định giá trị đúng, sai của một biểu thức luận ký để thực hiện các biểu thức tương ứng
+ Cú pháp: =IF(Cond, TrueExpr, FalseExpr)
+ Các đối số:
 Cond: Biểu thức logic để xác định
 TrueExpr: Biểu thức được thực hiện khi điều kiện đúng
 FalseExpr: Biểu thức được thực hiện khi điều kiện sai
+ Ví dụ: =IF(12=5; 4; 9) = 9
=IF(15 < 24; “YES”; “NO”) → YES
=IF(“a”>”b”; 8-3*2; 5*4-12) → 8
4- Một số hàm thông dụng
Các hàm luận lý (Logic)
- Hàm AND():
+ Công dụng: Cho trị TRUE nếu mọi đối số đều TRUE
+ Cú pháp: =AND(logical1,logical2,...)
+ Ví dụ: =AND(5>3,4<8)  TRUE
- Hàm OR():
+ Công dụng: Cho trị TRUE nếu có một đối số đều TRUE
+ Cú pháp: =OR(logical1,logical2,...)
+ Ví dụ: =OR(7>8,6<3,5>1)  TRUE
- Hàm NOT():
+ Công dụng: Cho trị ngược lại với trị logic cuả đối số
+ Cú pháp: =NOT(logical expression)
+ Ví dụ: =NOT(5>3)  FALSE
4- Một số hàm thông dụng
Các hàm xử lý chuỗi ký tự (Text)
- Hàm LEFT() và RIGHT():
+ Công dụng: Trích một số (number) ký tự ở bên trái hoặc bên phải của chuỗi ký tự (text)
+ Cú pháp: =LEFT(text, number)
=RIGHT(text,number)
+ Các đối số:
 Text: chuỗi dữ liệu hoặc ô chứa chuỗi dữ kiện cần trích
 Number: Số ký tự cần trích trên chuỗi (>0)
+ Ví dụ:
=LEFT(“February”, 3)  “Feb”
=RIGHT(“January”, 3)  “ary”
4- Một số hàm thông dụng
Các hàm xử lý chuỗi ký tự (Text)
- Hàm MID():
+ Công dụng: Trích n kí tự trong chuỗi kí tự TEXT tính từ kí tự thứ m.
+ Cú pháp: =MID(TEXT,m,n)
+ Ví dụ: =MID(“ABCDEF”,3,2)  “CD”
- Hàm TRIM():
+ Công dụng: bỏ kí tự trắng vô ích cuả chuỗi kí tự TEXT.
+ Cú pháp: =TRIM(TEXT)
+ Ví dụ:
=TRIM(“ MICROSOFT EXCEL “)  “MICROSOFT EXCEL”
4- Một số hàm thông dụng
Các hàm xử lý chuỗi ký tự (Text)
- Hàm UPPER(), LOWER(), PROPER():
+ Công dụng:
UPPER : chuyển các ký tự trong chuỗi sang chữ hoa
LOWER: chuyển các ký tự trong chuỗi sang chữ thường
PROPER: chuyển ký tự đầu trong chuỗi sang dạng chữ hoa
+ Cú pháp: =PPER(text)
=LOWER(text)
=PROPER(text)
+ Các đối số:  Text: chuỗi ký tự hoặc địa chỉ ô dữ liệu
+ Ví dụ: =UPPER(“Hello!”) = HELLO!
=LOWER(“EXCEL”) = excel
=PROPER(“WINWORD”) = Winword.
4- Một số hàm thông dụng
Các hàm xử lý chuỗi ký tự (Text)
- Hàm LEN():
+ Công dụng: Tính số ký tự (độ dài của chuỗi).
+ Cú pháp: =LEN(text)
+ Các đối số:  Text: Chuỗi ký tự hoặc tọa độ ô.
+ Ví dụ: =LEN(“Good, Morning!”) = 14
=LEN(3456028) = 7
- Hàm REPT():
+ Công dụng: Lập lại chuỗi dữ liệu với số lần chỉ định.
+ Cú pháp: =REPT(text, number)
+ Các đối số: Text: dữ liệu loại số, ký tự, hoặc tọa độ của ô
 Number: Số lần lập (>0)
+ Ví dụ: =REPT(“GOAL!”, 3) = “GOAL!GOAL!GOAL!”
4- Một số hàm thông dụng
Các hàm ngày, giờ (Date & Time)
- Hàm DATE():
+ Công dụng: Đổi 3 giá trị năm, tháng, ngày thành một biểu thức ngày.
+ Cú pháp: =DATE(year,month,day)
+ Ví dụ: DATE(96,12,19) → 19/12/96
- Hàm TIME():
+ Công dụng: Đổi 3 giá trị giờ, phút, giây thành một biểu thức giờ.
+ Cú pháp: =TIME(hour,minute,second)
+ Ví dụ: TIME(10,30,45) → 10:30:45 AM
4- Một số hàm thông dụng
Các hàm ngày, giờ (Date & Time)
- Hàm DATEVALUE():
+ Công dụng: Tính Serial number của một biểu thức ngày
+ Cú pháp: =DATEVALUE(“Biểu thức ngày”)
+ Ví dụ: =DATEVALUE(“15/07/2004”)  38183
- Hàm TIMEVALUE():
+ Công dụng: Tính Serial number của một biểu thức giờ
+ Cú pháp: =TIMEVALUE(“Biểu thức giờ”)
+ Ví dụ: =TIMEVALUE(“10:15:30”)  0.42743
4- Một số hàm thông dụng
Các hàm ngày, giờ (Date & Time)
- Hàm DAY():
+ Công dụng: Tính phần ngày cuả một biểu thức ngày
+ Cú pháp: = DAY(Serial_number / “biểu thức ngày”)
+ Ví dụ: = DAY(38183)  15
= DAY(“15/07/2004”)  15
- Hàm MONTH():
+ Công dụng: Tính phần tháng cuả một biểu thức ngày
+ Cú pháp: =MONTH(Serial_number / “biểu thức ngày”)
+ Ví dụ: =MONTH(38183)  07
=MONTH(“15/07/2004”)  7
- Hàm YEAR():
+ Công dụng: Tính phần năm cuả một biểu thức ngày
+ Cú pháp: =YEAR(Serial_number / “biểu thức ngày”)
+ Ví dụ: =YEAR(38183)  2004
=YEAR(“15/07/2004”)  2004
4- Một số hàm thông dụng
Các hàm ngày, giờ (Date & Time)
- Hàm HOUR():
+ Công dụng: Tính phần giờ cuả một biểu thức giờ (24 hours/day).
+ Cú pháp: =HOUR(Serial_number / Biểu thức giờ)
+ Ví dụ: =HOUR(0.42743) 10
=HOUR(“10:15:30”)  10
- Hàm MINUTE():
+ Công dụng: Tính phần giờ cuả một biểu thức giờ (24 hours/day).
+ Cú pháp: =MINUTE(Serial_number / Biểu thức giờ)
+ Ví dụ: =MINUTE(0.42743) 15
=MINUTE(“10:15:30”) 15
4- Một số hàm thông dụng
Các hàm ngày, giờ (Date & Time)
- Hàm SECOND():
+ Công dụng: Tính phần giây cuả một biểu thức giờ (24 hours/day).
+ Cú pháp: =SECOND(Serial_number / Biểu thức giờ)
+ Ví dụ: =SECOND(0.42743)  30
=SECOND(“10:15:30”)  30
- Hàm NOW():
+ Công dụng: Tính ngày giờ hiện tại.
+ Cú pháp: =NOW( )
+ Ví dụ: =NOW( ) 
4- Một số hàm thông dụng
Các hàm Toán học (Math)
- Hàm PRODUCT():
+ Công dụng: Tính tích của các đối số.
+ Cú pháp: =PRODUCT(number1,number2,...)
+ Ví dụ: =PRODUCT(3,5,20)  300
- Hàm PI():
+ Công dụng: Tính trị số của số PI.
+ Cú pháp: =PI()
+ Ví dụ: =PI() x 2 x 2  12,56637
4- Một số hàm thông dụng
Các hàm Toán học (Math)
- Hàm ROUND():
+ Công dụng: Làm tròn số.
+ Cú pháp: =ROUND(number, numberdigits)
+ Đối số:
 Number: Giá trị số, địa chỉ ô chứa số.
 Numberdigits:
>=0: Làm tròn phần số thập phân.
< 0: Làm tròn phần số nguyên.
+ Ví dụ: =ROUND(12345,54321; 0) = 12346
=ROUND(12345,54321; 2) = 12345,54
=ROUND(12345,54321; -2) = 12300
4- Một số hàm thông dụng
Các hàm Toán học (Math)
- Hàm ABS():
+ Công dụng: Tính trị tuyệt đối
+ Cú pháp: =ABS(number)
+ Đối số:  Number: Giá trị số, địa chỉ ô chứa số
+ Ví dụ: =ABS(-2) = 2
=ABS(12.54) = 12.54
- Hàm INT():
+ Công dụng: Tính phần nguyên của một số
+ Cú pháp: =INT(number)
+ Đối số:  Number: Giá trị số, địa chỉ ô chứa số
+ Ví dụ: = INT(7,6) = 7
= INT(C4) = Giá trị nguyên của số ở ô C4.
4- Một số hàm thông dụng
Các hàm Toán học (Math)
- Hàm MOD( ):
+ Công dụng: Tính số dư của phép chia.
+ Cú pháp: =MOD(number, divisornumber)
+ Đối số:
 Number: Giá trị số, địa chỉ của ô chứa số bị chia
 Divisornumber: Số chia
+ Ví dụ:
=MOD(9,2) (Cho kết quả là 1) = 1
=MOD(15,3) = 0
4- Một số hàm thông dụng
Các hàm tìm kiếm và tham chiếu (Lookup)
- Hàm VLOOKUP():
+ Công dụng: Tìm kiếm X trên cột đầu tiên của khối và cho kết quả tương ứng với cột (column) chỉ định.
+ Cú pháp: =VLOOKUP(X, BLOCK, COLUMN,TYPE)
+ Đối số:- X: Chuỗi, số, tọa độ, biểu thức
- BLOCK: Phạm vi khối cần khai thác.
- COLUMN: Cột chứa dữ liệu trong khối cần khai thác, được tính theo thứ tự từ trái sang phải và bắt đầu từ số 1.
- TYPE : gồm các trường hợp sau
* FALSE (=0): khi cột đầu tiên của table không sắp thứ tự, khi đó VLOOKUP() sẽ tìm trị chính xác nếu không sẽ trả về trị #N/A.
* TRUE (=1, hoặc không ghi) : Cột đầu tiên của table phải được săp thứ tự tăng dần, khi đó nếu VLOOKUP() không tìm thấy trị chính xác thì trả về trị lớn nhất trong các số 4- Một số hàm thông dụng
Các hàm tìm kiếm và tham chiếu (Lookup)
+ Ví dụ:






=VLOOKUP(7, A1: D4, 3) = 94
=VLOOKUP(3, A1:D4, 0) = #VALUE! (không hợp lệ)
=VLOOKUP(“5”, A1:D4, 2) = #N/A
(Không có dữ liệu chuỗi trong khối khai thác)
=VLOOKUP(7, A1:D4, 6) = #REF!
(Số cột khai thác lớn hơn số cột trong khối khai thác).
4- Một số hàm thông dụng
Các hàm tìm kiếm và tham chiếu (Lookup)
+ Ghi chú:
 Số cột khai thác phải là trị số >= 1 và không được lớn hơn số cột có trong cột khai thác.
 Nếu X là chuỗi thì phải là chuỗi chính xác với dữ kiện có trong cột đầu tiên của khối khai thác.
 Nếu X là số thì phải là số lớn hơn hoặc bằng với trị số nhỏ nhất trong cột đầu tiên của khối khai thác.
4- Một số hàm thông dụng
Các hàm tìm kiếm và tham chiếu (Lookup)
- Hàm HLOOKUP():
+ Công dụng: Tìm kiếm X trên dòng đầu tiên của khối khai thác và cho kết quả không tương ứng với dòng (row) theo chỉ định. (Nếu không tìm thấy thì cho kết quả là #VALUE !)
+ Cú pháp: =HLOOKUP(X, BLOCK, ROW,TYPE)
+ Đối số:
 Chuỗi, số, tọa độ. biểu thức
 Block: Phạm vi khối ô cần khai thác
 Row: Dòng chứa dữ liệu trong khối khai thác, được tính theo thứ tự từ trên xuống và bắt đầu bằng 1.
4- Một số hàm thông dụng
Các hàm tìm kiếm và tham chiếu (Lookup)
- Hàm MATCH( ):
+ Công dụng: Tìm vị trí của một số X trong một mảng.
+ Cú pháp: =MATCH(X,Array,Type)
+ Đối số: Type có 3 gía trị:
 1: array phải được sắp tăng dần, sẽ cho kết quả là vị trí cuả ô có gía trị lớn nhất và <=X.
 0: array sắp xếp bất kì, sẽ cho kết quả là vị trí ô có gía trị chính xác bằng X.
 -1: array phải được sắp giảm dần, sẽ cho kết quả là vị trí ô có gía trị nhỏ nhất và >= X.
THỰC HÀNH
Nhập số liệu và tính toán cho các bảng tính 7, 8, 9 trong tài liệu thực hành