Bài giảng Lập bảng tính với EXCEL 2003 - Bài 5: Sử dụng hàm trong tính toán

Lập bảng tính vớiEXCEL 2003 (B.5)Bài 5SỬ DỤNG HÀM TRONG TÍNH TOÁN 1-  Gíới thiệu và phân loại Hàm2-  Cú pháp tổng quát của Hàm3-  Cách nhập Hàm vào bảng tính4- Một số Hàm thông dụng.1- Giới thiệu và phân loại HàmHà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àmCá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àmHà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àmCá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ínhLà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ínhLà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ụngCá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ụngCá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ụngCá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ụngCá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) = 0ABCD1Tháng 1Tháng 2Tháng 32quý 11242192503quý 23654155694quý 379181612354- Một số hàm thông dụngCá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) = 74- Một số hàm thông dụngCá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,””b”; 8-3*2; 5*4-12) → 84- Một số hàm thông dụngCá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,48,61)  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)  FALSE4- Một số hàm thông dụngCá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ụngCá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ụngCá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ữ hoaLOWER: chuyển các ký tự trong chuỗi sang chữ thườngPROPER: 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ụngCá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ụngCá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 AM4- Một số hàm thông dụngCá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.427434- Một số hàm thông dụngCá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”)  20044- Một số hàm thông dụngCá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”) 154- Một số hàm thông dụngCá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ụngCá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,566374- Một số hàm thông dụngCá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.	= 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ụngCá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ụngCá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.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