Bài giảng Microsoft Excel (6)

/ Delete3. Huỷ bỏ vùng	- Chọn vùng muốn huỷ bỏ	- Edit / DeleteBài 4: Các hàm cơ bảnCú pháp chung các hàm	- Tất cả các hàm trong Microsoft Exel đều có chung một cú pháp như sau:	=Tên hàm (Các tham biến)	- Để xem các hàm, ta Click vào biểu tượng fx trên Standard Toolbar, hoặc vào Insert / Function (Shift+ F3)	- Nhập hàm từ bàn phím: Trước khi nhập hàm ta phải gõ dấu “=”II. Các hàm cơ bản	1. Nhóm hàm số	a. =ABS(Number)Hàm trả về giá trị tuyệt đối của một số NumberVD: Ô B1 có công thức: =2+ABS(-5.2). Khi đó giá trị trong ô sẽ là 7.2b. =INT(Number)	Hàm này trả về phần nguyên của một số NumberVD: Giá trị trong ô B1 là -3.2- Công thức trong ô C1 là: =INT(B1), giá trị sẽ là -4.- CT trong ô D1 là: =INT(ABS(B1)), giá trị sẽ là 3ABCD1-3.2-432c. =MOD(Number, divisor)	Hàm trả về giá trị phần dư của một phép chia số Number cho số divisor (số chia)VD: Mod(11,3)=2d. =ROUND(Số làm tròn, Cách làm tròn)Hàm trả về giá trị đã được làm tròn của số Number tại vị trí thứ làm tròn.VD: Round(368.592645,2)=368.59	Round(12.687,1)=12.72. Nhóm hàm thống kêa. =AVERAGE(Giá trị 1, giá trị 2 giá trị n)Hàm trả về giá trị trung bình cộng của các sốVD: Dữ liệu của các ô: B1, B2, B3 lần lượt là 4,8,3. Công thức ô B4 là:	=Average(B1:B3)=5Chú ý: Hàm này không tính các phần tử trống (các ô trống là ô không có dữ liệu)b. =COUNT(Vùng)	Hàm trả về số phần tử kiểu số	 VD: Count(2, “ab”,5,4)=3c. =COUNTA(Vùng)	Hàm trả về số phần tử khác rỗng trong vùng	 VD: =Counta(2, “ab”,5,4)=4d. =MAX(Giá trị 1, giá trị 2giá trị n)	Hàm trả về giá trị lớn nhất của các giá trị	 VD: Dữ liệu của các ô: B1, B2, B3 lần lượt là 4,8,3. Công thức ô B4 là: =Max(B1:B3)=8e.= MIN(Giá trị 1, giá trị 2giá trị n)	Hàm trả về giá trị nhỏ nhất của cá giá trị	 VD: Dữ liệu của các ô: B1, B2, B3 lần lượt là 4,8,3. Công thức ô B4 là: =Min(B1:B3)=3f. =SUM(Giá trị 1, giá trị 2 giá trị n)	Hàm trả về tổng của các giá trị	 VD: Dữ liệu của các ô: B1, B2, B3 lần lượt là 4,8,3. Công thức ô B4 là: =Sum(B1:B3)=153. Nhóm hàm chuỗia. =LEFT(Chuỗi, Số kí tự trích)	Hàm trả về chuỗi con của chuỗi kí tự được trích từ trái sang phải	VD: =Left(“Trần Văn Nam”,8)=“Trần Văn”b. =RIGHT(Chuỗi, Số kí tự trích)	Hàm trả về chuỗi con của chuỗi kí tự được trích từ phải sang trái	VD: =Right(“Trần Văn Nam”,3)=“Nam”c. =MID(Chuỗi, Số bắt đầu, Số kí tự trích)	Hàm trả về chuỗi con của chuỗi kí tự được trích từ vị trí bắt đầu và dài bằng số kí tự trích	VD: =Mid(“Trần Văn Nam”,6,3)=“Văn”d. =UPPER(Chuỗi)	Hàm chuyển các kí tự trong chuỗi sang chữ in hoa	VD: Uper(“thuy tien”) = “THUY TIEN”e. =LOWER(Chuỗi)	Hàm chuyển các kí tự trong chuỗi sang chữ thường	VD: Lower(“THUY TIEN”) = “thuy tien”f. =PROPER(Chuỗi)	Hàm chuyển tất cả các chữ cái đầu của mỗi từ sang chữ in hoa	VD: Proper(“trần thị thu hà”) = “Trần Thị Thu Hà”g. =TRIM(Chuỗi)	Hàm trả về chuỗi kí tự mà các kí tự trắng nằm ở đầu và cuối chuỗi được cắt bỏ	VD: Trim(“ Thuỷ Tiên ”) = “Thuỷ Tiên”h. =LEN(Chuỗi)	Cho biết độ dài của chuỗi kí tự (kể cả kí tự trắng)	VD: =Len(“Xin chao ban”)=12i. =REPT(Chuỗi, Số lần lặp)	Hàm lặp lại chuỗi dữ liệu trong hàm theo số lần chỉ định	VD: =REPT(“Good day!”,2) = “Good day! Good day!”4. Nhóm hàm ngày, thánga.=DATE(Năm, tháng, ngày)	Hàm cho kết quả ứng với ngày, tháng, năm	VD: =Date(93,08,25) = 25/08/1993 nếu ô chứa hàm định dạng dd/mm/yyyyb. =DAY(Chuỗi ngày tháng năm)	Hàm trả về ngày trong tháng	VD: =Day(10/12/1985) = 10c.=NOW( )	Cho kết quả ngày giờ hiện hành	VD: =Now() = 20/10/2006 8:00 amd.= TODAY( )	Hàm trả về ngày hiện hành	VD: =Today() = 20/10/2006e. =MONTH(Chuỗi ngày tháng năm)	Trả về tháng trong ô dữ liệu ngày tháng	VD: =Month(10/12/2000) = 12f. =YEAR(Chuỗi ngày tháng năm)	Trả về nam trong o du lieu ngay thang	VD: =Year(10/12/2000) = 20005. Hàm đổi kiểu dữ liệu=TEXT( Giá trị số, Cách định dạng)	Hàm biển đổi số thành chuỗi số theo định dạng hợp lệ	VD: =Text(12345.67, “#,###”) & “USD” = 12,345.67USDb. =VALUE(Chuỗi)	Hàm biến đổi chuỗi thành số	VD: =Value(“123”) = 1236. Nhóm hàm Logic1. =AND(Biểu thức1, Biểu thức 2..)- Hàm trả về giá trị “và” của biểu thức LogicNếu tồn tại ít nhất một biểu thức có giá trị FALSE thì hàm AND cho giá trị FALSE nếu không cho giá trị TRUEVD:= And(25) trả về true	=And(2>3,55) trả về false2. =OR(Biểu thức 1, Biểu thức 2)	Trả về giá trị đúng nếu bất kì biểu thức nào trong hàm đúngVD: Or(2>3,52) trả về FALSE	=Not(2>3) trả về TRUEBài 5: các hàm cấu trúc điều khiểnNhóm hàm điều kiện1. =IF(Biểu thức điều kiện, KQ 1, KQ 2)	Hàm trả về biểu thức đúng khi thoả mãn điều kiện, ngược lại hàm trả về giá trị sai.	VD: =If(A1>2, “A”, “B”)- Nếu A1 lớn hơn 2, giá trị trả về là “A”-Ngược lại giá trị trả về sẽ là “B”Chú ý: Có thể két hợp các hàm IF với nhau để kiểm tra cho nhiều trường hợp nhưng không vượt quá 254 kí tự.2. =COUNTIF(Vùng điều kiện, Điều kiện)Hàm trả về giá trị đếm những phần tử của vùng điều kiện thoả mãn điều kiện VD: Các giá trị vùng A1, A2, A3, A4 lần lượt là 100, 200, 300, 400. Khi đó	=Countif(A1:A4, “>160”) = 33. =SUMIF(Vùng dò, Điều kiện, Vùng lấy tổng số)	Hàm tính tổng những ô trong vùng lấy tổng số, những phần tử này được chọn ứng với những dòng của vùng dò có giá trị thoả mãn với vùng lấy tổng sốVD: Các giá trị vùng A1, A2, A3, A4 lần lượt là 100, 200, 300, 400	Các giá trị vùng B1:B4 là: 7, 14, 21, 18. Khi đó	=Sumif(A1:A4, “>160”,B1:B4) = 63II. Nhóm hàm tìm kiếm=VLOOKUP(Giá trị tìm, Vùng cần tìm,cột trả về,0)	Hàm tìm giá trị ngoài bảng (bắt đầu từ cột đầu tiên của bảng tham chiếu), tìm được tại cột thứ bao nhiêu sẽ trả về giá trị ở bảng chi tiết (Cột đầu tiên được đếm là 1). Trong đó:	- Giá trị tìm: có thể là 1 chuỗi. Số, toạ độ ô chứa dữ liệu, biểu thức có kết quả là một giá trị hay chuỗi.	- Vùng cần tìm: Phạm vi khối dữ liệu dùng để so sánh với giá trị tìm.	- cột trả về: Cột trả về của hàm là cột thứ bao nhiêu trong bảng tham chiếu.VD:2. =HLOOKUP(Giá trị tìm, Vùng cần tìm,Dòng trả về,0)	Hàm tìm giá trị ngoài bảng (bắt đầu từ dòng đầu tiên của bảng tham chiếu), tìm được tại tại dòng thứ bao nhiêu sẽ trả về giá trị ở bảng chi tiết (dòng đầu tiên được đếm là 1). Trong đó:	- Giá trị tìm: có thể là 1 chuỗi. Số, toạ độ ô chứa dữ liệu, biểu thức có kết quả là một giá trị hay chuỗi.	- Vùng cần tìm: Phạm vi khối dữ liệu dùng để so sánh với giá trị tìm.	- cột trả về:dòng trả về của hàm là cột thứ bao nhiêu trong bảng tham chiếu.III. Nhóm hàm cơ sở dữ liệu=DSUM(Vùng cơ sở dữ liệu, Cột tham chiếu, 	Vùng tiêu chuẩn)	Hàm tính tổng những mẩu tin thoả mãn điều kiện trên cột khai thác trong cơ sở dữ liệu2. =DAVERAGE(Vùng cơ sở dữ liệu, Cột tham chiếu,	 Vùng tiêu chuẩn)	Hàm tính trung bình cộng cua nhung mẩu tin thoả mãn điều kiện trên cột khai báo trong cơ sở dữ liệu.3. =DCOUNT(Vùng cơ sở dữ liệu, Cột tham chiếu,	 	 	Vùng tiêu chuẩn)	Hàm đếm tổng số ô của những mẩu tin thảo mãn điều kiện trên cột khai thác trong một cơ sở dữ liệu4. =DMAX(Vùng cơ sở dữ liệu, Cột tham chiếu, 	Vùng tiêu chuẩn)	Hàm tìm trị số cao nhất của những mẩu tin thoả mãn điều kiện trên cột khai thác trong một cơ sở dữ liệu5. =DMIN(Vùng cơ sở dữ liệu, Cột tham chiếu, 	Vùng tiêu chuẩn)	Hàm tìm trị số thấp nhất của những mẩu tin thoả mãn điều kiện trên cột khai thác trong một cơ sở dữ liệu.Giải thích ý nghĩa các hàm:Vùng cơ sở dữ liệu: Phạm vi khối cơ sở dữ liệu cần khai thácCột tham chiếu: Số cột chứa dữ liệu cần khai thác trong khối cơ sở dữ luệu, được tính từ trái sang phải và bắt đầu từ số 1Vùng tiêu chuẩn: Phạm vi khối điều kiệnMột số thông báo lỗi cần biếtLỗiý nghĩa#VALUE!Giá trị không đúng kiểu#NAME?Sai tên trong biểu thức#NUM!Trị số không hợp lệ#N/A!Không tính được#DIV/0!Không thể chia cho số 0#REF!Không tham chiếu được#NULL!Không có gì để tínhBài 6: Cơ sở dữ liệu trên bảng tínhThao tác lọc, xoá và rút trích1. Lọc	- Di chuyển con trỏ chuột vào một ô bất kì trong vùng dữ liệu (kể cả tiêu đề)	- Data / Filter / Advanced Filter	+ Trong Action: chọn nút chọn Filter the List, In-place	+ Trong List range: nhập địa chỉ vùng CSDL	+ Trong Criteria range: nhập địa chỉ vùng điều kiện.	+ Nếu chọn Unique record only: DL lọc ra nếu có những dòng trùng nhau sẽ trình bày chỉ 1 dòng.	- OK2. XoáThực hiện các bước giống thao tác lọc.Sau khi các dòng thoả mãn điều kiện được trình bày, chọn các dòng này và thực hiện lệnh Edit / Delete RowThực hiện lệnh Data / Filter / Show all để trình bày trở lại các mẩu tin bị dấu đi trong thao tác lọc.3. Rút trích 	- Di chuyển con trỏ chuột vào 1 ô bất kì	- Data / Filter / Advanced Filter	+ Trong Action chọn Copy to another location	+ Trong List range: nhập địa chỉ vùng DL	+ Trong Criteria range: nhập địa chỉ vùng điều kiện	+ Trong Copy to: nhập địa chỉ dòng đầu vùng trích DL.	- OK.bài 7: vẽ biểu đồCác loại biểu đồTrong Exel, có thể dựa vào dữ liệu trên worksheet để tạo các biểu đồ. Các biểu đồ trong Exel có rất nhiều loại:Column: cộtBar: KhốiLine: Đường kẻPie: Hình trònXY: Hàm sốArea: Miềnứng với mỗi loại biểu đồ, ta có thể chọn nhiều dạng khác nhau. VD ứng với kiểu ColumnII. Các thành phần cơ bản trong biểu đồValue (Y) AxisTitleCategory (X) AxisLegendTitle: Tiêu đề chung của biểu đồValue (Y) Axis: tiêu đề chung của trục tungCategory (X) Axis: tiêu đề chung của trục hoànhLegend: Chú thích minh họaIII. Vẽ biểu đồTrên Worksheet đã có sẵn một vùng dữ liệu. Di chuyển con trỏ vào vùng dữ liệu có sẵn trên Worksheet và thực hiện lệnh Insert / Chart hoặc Click vào biểu tượng Chart trên thanh công cụ Standard.Chọn loại (Chart Type) và dạng (Chart sub-type) biểu đồ (VD chọn loại Column). Click nút Next sang bước kế tiếp. Hộp hội thoại Step 2 xuất hiệnTrong Data range xác định vùng dữ liệu muốn vẽ. Click nút Next sang bước kế tiếp. Hộp hội thoại Step 3 xuất hiệnXác định tiêu đề và chú thích:Titles để xác định tiêu đề	- Tiêu đề của biểu đồ nhập trong Chart title	- Tiêu đề trục hoành nhập trong Category (X) axis	- Tiêu đề trục tung nhập trong Value (Y) axisLegend để bật hay tắt hoặc hiển thị chú thích minh họaSau đó Click Next để sang bước kế tiếp. Hộp hội thoại Step 4 xuất hiện.Nếu muốn đưa biểu dồ vào worksheet hiện hành chọn As object in.Nếu muốn tạo biểu đồ trên một sheet riêng chọn As new sheet.Finish.