Bài giảng Microsoft Excel (3)

n hỡnh làm việcThanh tiêu đề(Title Bar )Menu BarFormalar BarCác SheetThanh CuộnVùng soạn thảo bảng tínhStandardFormattingSau khi khởi động, màn hình Excel xuất hiện như sauThanh trạng thái (Status bar)I.GIỚI THIỆU MÀN HèNH LÀM VIỆC1,Thanh tiờu đề :2,Thanh Thực đơn (Menu bar)Chứa các tên lệnh đơn tương ứng với các nhóm chức năng xử lý dữ liệu3,Thanh công cụ (Toolbars)Hiện thị tên của cửa sổ ứng dụngLà các thanh công cụ , trên thanh này có nhiều nút công cụ lệnh,giúp thực hiện nhanh các chức năng lệnh thay vì thực hiện bằng thanh thực đơnTrong Excel ngầm định hiển thị sẵn 2 thanh công cụ :Standard và Formatting vì chúng thường được sử dụng.Ngoài ra còn rất nhiều nút công cụ khác trong quá trình làm việc,nếu bạn muốn dùng nút nào thì cho hiển thị lên, nếu không thì giấu nó đI.Để hiện thị hoặc giấu thanh công cụ thì vào View \ Toolbars hoặc vào Tools \ Customize\ ToolbarsĐể hiện thị hoặc giấu nút công cụ nào thì vào Tools \ Customize\ Commands rồi nhấn chuột trái vào nút muốn hiển thị rê ra thanh công cụ, còn muốn giấu nút nào thì làm ngược lại4,Thanh công thức (Formula bar)Địa chỉ của ô đang làm việcHiển thị nội dung của ô. Cũng là nơI dùng để sửa công thức, dữ liệu5,Thanh trạng thái (Status bar)Nằm ở cuối cửa sổ ứng dụng, cho biết trạng thái làm việc của WorkbookReady :trạng thái sẵn sàng chờ nhập dữ liệu cho ôEnter :Trạng thái đang nhập dữ liệuEdit : Trạng thái đang sửa dữ liệu6,Phần Bảng Tính :Được tổ chức thành các trang bảng tính, mỗi trang bảng tính tương ứng với một Sheet, mỗi trang bảng tính có 256 cột được đánh thứ tự A, B,AA,IVvà 65536 dòng được đánh thứ tự từ 1, 2,3,.65536.Trong một tệp của Excel mặc định là 3 SheetTa có thể thay đổi số lượng Sheet mặc định bằng cách vào Tools \Option \General \, nhập số lượng Sheet mặc định vào hộp Sheet in new workbook rồi OKCác loại địa chỉ trong Excel1.Địa chỉ của một ô (Cell)2.Địa chỉ của vùng7. ĐỊA CHỈ CỦA MỘT ễ (CELL)7.1 Khái niệmĐịa chỉ của ô hay một khối ô trong Excel chính là tên của ô hay khối ô đó. Excel phân ra làm 2 loại cơ bản :+ Địa chỉ tương đối + Địa chỉ tuyệt đốiLà loại địa chỉ thay đổi được khi sao chép dữ liệu hay công thức. Địa chỉ bình thường nhập vào trong công thức là địa chỉ tương đốiLà loại địa chỉ không thay đổi được khi sao chép dữ liệu hay công thức8. ĐỊA CHỈ CỦA VÙNGLà địa chỉ của một khối ô liên tiếp (2 ô trở lên).Nó được xác định bằng cách lấy địa chỉ của ô đầu tiên và cuối cùng trong vùng nhưng được đặt cách nhau bởi dấu hai chấmCũng có hai loại địa chỉ là :+ Địa chỉ tương đối+ Địa chỉ tuyệt đốiVí dụ :A2 :C10 là địa chỉ tương đối của khối ô từ ô A2 cho đến ô C10$A2 :$C10 là địa chỉ tuyệt đối của khối ô từ ô A2 cho đến ô B10- Địa chỉ tương đối được xác định dưới dạng Cách xác định địa chỉ của ôVí dụ :B5 : là địa chỉ của ô B5 (trong đó A là tên cột, 5 là số thứ tự của dòng- Địa chỉ tuyệt đối của một ô được xác định dưới dạng $$Ví dụ :$C$6 : là địa chỉ tuyệt đối của ô C6- Tương tự cho địa chỉ của vùngChú ý :Để chuyển địa chỉ tương đối sang địa chỉ tuyệt đối ta dùng phím F4Gõ địa chỉ rồi ấn phím F4ý nghĩa1 lầnĐịa chỉ tuyệt đối 2 lầnCố định địa chỉ hàng3 lầnCố định địa chỉ cột4 lầnĐịa chỉ tương đốiVí dụ:Ví dụ :Ta có địa chỉ tương đối là A1 thì sau khi ta nhấn F4 sẽ được kết quả sau:ấn phím F4Kết quảý nghĩa1 lần$A$1Địa chỉ tuyệt đối2 lầnA$1địa chỉ hàng cố định,hàng thay đổi3 lần$A1địa chỉ cột cố định,cột thay đổi 4 lầnA1Về địa chỉ tương đốiGiới Thiệu Về HàmHàm là những công thức được xác định sẵn trong máy tính cho phép tính toán với những phép tính xác định, kết quả sau phép tính không phải là hàm mà là giá trị được trả về từ hàm đóTất cả các hàm trong Excel đều có dạng:= Tên hàm(DSĐS)Trong đó:Tên hàm do Excel quy định, được viết bằng tiếng anh và không phân biệt chữ hoa hay chữ thườngDSĐS là danh sách các đối số, có thể nhận các giá trị như : Giá trị số, địa chỉ ô, tên vùng, công thức hoặc hàm khácSUMAVERAGEMINCOUNTANDVLOOKUPSUMIFCOUNTIFORRANKHLOOKUPIFMAXII,Các Hàm Cơ BảnNhóm hàm với sốNhóm Hàm Chuỗi*.Hàm Sum=Sum(DSĐS)Trong đó :Sum :là tên hàmDSĐS : là các số, địa chỉ của ô chứa số hoặc địa chỉ của dãy ô liên tiếpHàm cho kết quả là tổng các giá trị trong danh sách đối số=Sum(A2:H2) tức tính tổng các số liệu từ ô A2 đến ô H2=Sum(1,3,5) cho ra kết quả là 9	Các Hàm Cơ Bản Trong ExcelCú Pháp:Chức năng :Ví Dụ :*Hàm MaxCú Pháp:=Max(DSĐS)Hàm cho kết quả là giá trị lớn nhất trong các giá trị trong danh sách đối sốChức năng :Ví Dụ :=Max(1,5,8,4) trả về giá trị lớn nhất trong 4 số trên là 9=Max(A3:C5) trả về giá trị lớn nhất trong các giá trị trong vùng từ A3 đến C5	Các Hàm Cơ Bản Trong Excel* Hàm Average=Average(DSĐS)Hàm cho kết quả là giá trị trung bình của các giá trị trong danh sách đối số=Average(2,6,3,7) cho kết quả là 9=Average(A2:A6) cho kết quả là giá trị trung bình của các giá trị từ ô A2 đến A6=Average(B2:D5) cho kết quả là giá trị trung bình trong vùng từ ô B2 đến ô D5	Các Hàm Cơ Bản Trong ExcelCú Pháp:Chức năng :Ví Dụ :* Hàm Min=Min(DSĐS)Hàm cho kết quả là giá trị nhỏ nhất trong các giá trị trong danh sách đối số=Min(1,5,8,4) trả về giá trị nhỏ nhất trong 4 số trên là 1=Min(A3:C5) trả về giá trị lớn nhất trong các giá trị trong vùng từ A3 đến C5	Các Hàm Cơ Bản Trong ExcelCú Pháp:Chức năng :Ví Dụ :*Hàm Count=Count(DSĐS)Hàm cho kết quả là số giá trị kiểu số và các thành phần có tính chất kiểu số trong danh sách đối số =Count(1,6,”tn”,t2”,5)  3Chú ý: Hàm chỉ đếm khi đối số của hàm thỏa mãn là kiểu số COUNT(C1:C5)=5COUNT(A1:A5)=2	Các Hàm Cơ Bản Trong ExcelCú Pháp:Chức năng :Ví Dụ :*.Hàm Vlookup=Vlookup(x,bảng dò tim,n,m)Tham số :X :là giá trị mang dò tìmBảng dò tìm : là địa chỉ tuyệt đối của bảng tham chiếu không bao gồm phần tiêu đề.Trong Bảng dò tìm cột đầu tiên thường là cột dò tìm n : là số nguyên dương chỉ số thứ tự của cột tham chiếu trong bảng dò tìmm : Kiểu dò tìm+Nếu m =0 thì giá trị trên cột dò tìm không phải sắp xếp 	Các Hàm Cơ Bản Trong ExcelCú Pháp:+ Nếu m =1 thì trong bảng dò tìm, cột dò tìm phải được sắp xếp theo thứ tự tăng dầnHàm lấy giá trị x trong cột dò tìm của bảng tham chiếu, nếu tìm được thì dóng sang cột thứ n của bảng dò tìm để lấy giá trị	Các Hàm Cơ Bản Trong ExcelChức năng :Ví Dụ :Để tính ô B2 ta gõ công thức =Vlookup(A2,$A$9:$C$10,2,0)  Enter  Máy tínhĐể tính ô D2 gõ công thức :=Vlookup(A2,$A$9:$C$10,3,0) Enter  300	Các Hàm Cơ Bản Trong Excel*.Hàm Hlookup=Hlookup(x,bảng dò tim,n,m)Cú Pháp:Tham số : giống như hàm Vlookup Hàm lấy giá trị x trong dòng dò tìm của bảng tham chiếu, nếu tìm được thì dóng sang dòng thứ n của bảng dò tìm để lấy giá trịChức năng :Ví Dụ :Để tính ô E2 ta gõ công thức =Hlookup(A2,$A$8:$G$9,2,0)  Enter  10%*.Hàm If :	Các Hàm Cơ Bản Trong ExcelCú Pháp: =If(btđk,Giá trị 1,Giá trị 2)Trong đó :Chức năng :Btđk là biểu thức có dạng logicGiá trị 1, Giá trị 2 : có thể là giá trị số hoặc “chuỗi”, hoặc công thức.Cho kết quả là Giá trị 1 nếu btđk đúng,ra Giá trị 2 nếu btđk saiVí Dụ :Để tính các ô C2 đến C5 với đk học bổng khi ĐTB lớn hơn hoặc bằng 8*.Hàm And	Các Hàm Cơ Bản Trong ExcelCú Pháp:Ví Dụ :Chức năng :=And(Btđk1,Btđk2,.,Btđkn)Hàm cho kết quả là đúng nếu tất cả các Btđk đúng(True),kết quả là sai nếu 1 trong các Btđk là sai(False)=And(9>4,5>2,65,53)  False=And(24,5>2,65,53)  True=Or(2<6,7<4,5<1)  False*.Hàm Rank	Các Hàm Cơ Bản Trong ExcelCú Pháp:Ví Dụ :=Rank(x,DS,m)Tham số : x : là giá trị cần sắp xếpDS : là địa chỉ( thường lấy địa chỉ tuyệt đối) vùng làm cơ sở để xếp thứ bậcm : cách sắp xếp thứ bậc Nếu m =0 :Giá trị lớn được xếp thứ trước Nếu m =1 :Giá trị nhỏ được xếp thứ trước*.Hàm Sumif :	Các Hàm Cơ Bản Trong ExcelCú Pháp:=Sumif(VCĐK,”n”,VCTT)Trong đó : VCĐK : là địa chỉ của vùng làm cơ sở để xét điều kiện n : là điều kiện có thể gõ trực tiếp vào hoặc là địa chỉ của ô chứa điều kiện VCTT : là địa chỉ của vùng cần tính tổngChức năng :Hàm cho kết quả là tổng của các giá trị trên VCTT tương ứng với các giá trị thỏa mãn đk ở VCĐTVí Dụ :*.Hàm Countif :	Các Hàm Cơ Bản Trong ExcelCú Pháp:=Countif(VCĐK,”n”)Trong đó : VCĐK : là địa chỉ của vùng làm cơ sở để xét điều kiện n : là điều kiện có thể gõ trực tiếp vào hoặc là địa chỉ của ô chứa điều kiệnChức năng :Hàm cho kết quả là số các ô trong vùng VCĐK thỏa mãn điều kiện nVí Dụ :Ví Dụ :Để tính các ô từ E2 đến E6 ta dùng hàm Rank gõ công thức vào ô E2=rank(B2,$B$2:$B$6,0) Enter rồi saochép công thức cho đến ô C6Để tính ô D7 ta dùng hàm Sumif, gõ công thức vào ô D7 =sumif(C2:C6.”1990”,B2,B6) Enter 24.8Để tính ô D8 ta dùng hàm Countif, gõ công thức vào ô D8 =Countif(D2:D6,”Hà Tây”) Enter  3*.Nhóm Hàm với số :	Các Hàm Cơ Bản Trong ExcelQuy Ước chung : n,m : Có thể là một số Hay một địa chỉ ô chứa giá trị sốHoặc một phép toán cho kết quả là một giá trị số Hàm lấy giá trị tuyệt đối :Cú Pháp:=Abs(n)Ví Dụ :Ô B2 = -6=Abs(-21)  21=Abs(B2)  6=Abs(5+B2)  1 Hàm lấy phần nguyên*.Nhóm Hàm với số :	Các Hàm Cơ Bản Trong ExcelCú Pháp:Ví Dụ :=Int(n)Ô B2 =21,69=Int(18,568)  18=Int(B2)  21=Int(5,46+B2)  27 Hàm lấy phần dư của phép chia n cho mCú Pháp:=Mod(n,m)Ví Dụ :Ô B2=22,C2=4=Mod(B2,3)  1=Mod(B2,C2)  2	Các Hàm Cơ Bản Trong ExcelCú Pháp:Ví Dụ :*.Nhóm Hàm với số : Hàm lấy Căn bậc hai=Sqrt(n)Ô B2=16,C2=81=Sqrt(B2)  4=Sqrt(64)  8=Sqrt(Sqrt(C2))  3 Hàm làm tròn số liệuCú Pháp:=Round(n,m)Ví Dụ : Ô B2 = 21,4745=Round(B2,1)  21,5 ;=Round(B2,0)  21=Round(B2,2)  21,47*.Nhóm Hàm Chuỗi :	Các Hàm Cơ Bản Trong Excel Hàm Cắt Trái Cú Pháp:Ví Dụ :=Left(st,n)Trong đó : st có thể là chuỗi kí tự được đặt trong dấu nháy kép hay là địa chỉ ô chứa giá tri kiểu TextB2 = Tin học Trẻ=Left(B2,3)  Tin=Left(“PhanTuan”,4)  Phan Hàm Cắt Phải=Right(st,n)Cú Pháp:Ví Dụ :=Right(B2,3)  Trẻ=Right(“PhanTuan”,4)  TuanHàm cắt giữaCú Pháp:=Mid(st,n,m)Trong đó : st giống như hàm Left, Right	n : là vị trí bắt đầu cắt	m : là số ký tự cần lấyVí Dụ :B2 = Tin học Trẻ=Mid(B2,5,3)  “học”=Left(“PhanTuan”,2,5)  “han T” Chú ý :Dấu cách ( Space ) cũng được coi là một ký tựNgoài ra còn rất nhiều hàm khácDay : hàm lấy ngàyMonth : hàm lấy thángYear : hàm lấy nămBài Giảng Đến Đây Kết ThúcChúc các bạn và cô giáo mạnh khỏeNgười thực hện :Phan Quang TuấnHà Nội, ngày 08/12/2008