Bài giảng môn học Tin học lớp 11 - Bài 3 : MicroSoft Excel ( Bảng tính điện tử )

Bài 3 : MicroSoft Excel  ( Bảng tính điện tử )I/ Các khái niệm cơ bản- Excel là phần mềm chuyên dùng cho công tác kế toán, văn phòng được chạy trên môi trường Windows do hãng MicroSoft sản xuất. Nó có các tính năng và ứng dụng sau:+ Thực hiện được nhiều phép tính từ đơn giản đến phức tạp + Tổ chức và lưu trữ thônh tin dưới dạng bảng+ Khi có thay đổi về dữ liệu, bảng tính tự động tính lại theo số liệu mới1/ Khởi động Excel: Giống Word(Nhắp chuột vào biểu tượng X MicroSoft Excel )2/ Màn hình Excela/ Thanh tiêu đềb/ Thanh menu ngang c/ Thanh công cụd/ Thanh định dạng ( Giông Word )e/ Thanh công thức (Formular ): Hiển thị toạ độ ô hiện hành và nội dung dữ liệu trong ô hiện hành f/ Tập bảng tính Work book Là cửa sổ chứa nội dung tệp, tên tệp ngầm định là Book 1*Các thành phần của một tập bảng tính: - Đường viền ngang : Ghi kí hiệu cột từ trái qua phải theo thứ tự chữ cái từ a, b, c,...,aa,ab,..., ba, bb, bc,.. Cột cuối cùng kí hiệu là I V , có tổng số 256 cột - Đường viền dọc : Ghi số thứ tự dòng từ trên xuống, có tổng số 16.384 dòng - Ô Cell: là giao điểm của 1 cột với 1 dòng, được xác định địa chỉ bởi cột trước, dòng sauVí dụ: C3 Cột C, dòng 3- Ô hiện hành : Là ô có khung viền quanh bởi con trỏ ô đậu vào, toạ độ của ô được hiển thị trên thanh công thức - Bảng tính Sheet : Là một bảng gồm có 256 cột và 16.384 dòng. Theo ngầm định một tập bảng tính gồm có 3 Sheet. Ta có thể chèn thêm 256 Sheet* Cách chèn thêm 1 SheetNhắp phải chuột lên trên Sheet cuối  htxh  Chọn Insert  htxh  OK* Đổi tên Sheet : Nhắp phải chuột lên tên Sheet cần đổi  Rename  rồi gõ vào tên mới  Enter. 3/ các thao tác cơ bản trên bảng tínha/ Cách di chuyển con trỏ ô+   lên, xuống, trái, phải+ Home : di chuyển con trỏ về ô đầu dòng Ctrl + Home : Chuyển con trỏ về ô A1 + Pageup : di chuyển con trỏ ô lên 1 trang màn hình+ Page Down : di chuyển con trỏ ô xuống 1 trang màn hình+ Nhắp chuột vào ô cần chuyển tớib/ Vùng dữ liệu: Là 1 hoặc nhiều ô liên tục được xác định địa chỉ bởi đ/c ô đầu vùng và địa chỉ ô cuối vùng- Dạng vùng:Địa chỉ ô đầu : địa chỉ ô cuốiVí dụ: A3 : A8 B2 : E5- Cách chọn vùng: Bôi đen+ Chọn 1 ô: Nhắp chuột vào ô cần chọn + Chọn 1 dòng: Nhắp chuột vào số thứ tự dòng + Chọn 1 cột: Nhắp chuột vào kí hiệu cột + chọn 1 vùng: nhắp giữ nút trái chuột tại ô đầu vùng, giữ rồi kéo xuống ô cuối vùng C/ Các kiểu dữ liệuTrong 1 ô chỉ có thể chứa được 1 kiểu dữ liệu, kiểu dữ liệu phụ thuộc vào kí tự đầu tiên khi gõ vào.- Có 3 kiểu dữ liệu:+ Kiểu 1: DL kiểu số: Kí tự đầu tiên gõ vào là các số từ 0 đến 9+ Kiểu 2: DL kiểu chuỗi: Kí tự đầu tiên gõ vào là các chữ từ A đến Z+ Kiểu 3: DL Kiểu công thức: Kí tự đầu tiên gõ vào là dấu = - Trong một công thức có thể chứa dữ liệu kiểu số, kiểu chuỗi ( Nếu là kiểu chuỗi, trong công thức phải đặt trong dấu ngoặc kép ), Toạ độ ô, địa chỉ vùng, các toán tử và các loại hàm.- Các toán tử sử dụng trong công thức:+ Toán tử tính toán: + ; - ; * ; / chia; ^ luỹ thừa; %Ví dụ: =C4*D =C4/D4 210 = 2^10 = 1024+ Toán tử chuỗi $ nối chuỗi + Toán tử so sánh = ; khác ; >= ; ; 0 : Làm tròn về bên phải cột thập phân n 25 ) ; 15000 ; 0 )))Ví dụ: Tính lương tháng ở BTTH2.3:=IF(E4>25;(E4-5)*2*D4+25*D4;D4*E4)Ví dụ: BTTH2.4+ Tính lương tháng 01 = C7*3,25*(D7+E7*1,5)+ Lương tháng 02 =C7*2,57*(G7+H7*1,5)+ Tính tổng các mụcPhòng tổ chức: D6=Sum(D7:D12)Phòng tài vụ: D13=Sum(D14:D19)+ Tổng cộng: =D6+D13+ Tổng quỹ thưởng vào ô J22 =J2-(F20+I20)+ Thưởng năng suất =J22/(D20+G20)*(D7*G7) =($J$22/$D$20+$G$20)*(D7+G7)*Địa chỉ tương đối & địa chỉ tuyệt đối - Đ/c tương đối: là đ/c có dạng cột dòng. Khi sao chép công thức đ/c này sẽ thay đổi theo phương, chiều, khoảng cáchVí dụ: J22 đ/c tương đối -Đ/c tuyệt đối: Là đ/c có dạng $cột$dòng. Khi sao chép công thức đ/c này giữ nguyên không thay đổi Ví dụ: $J$22 đ/c tuyệt đối Cách viết: Gõ đ/c tương đối rồi ấn phím F4III / Định dạng dữ liệu trên Bảng tính1/ Định dạng dữ liệu số- Tách, nhóm các số: chọn vùng dl, bôi đen rồi Nhắp chuột vào nút để tách nhóm các số bắt đầu từ phải qua trái ba chữ số một nhóm và thêm vào phần lẻ thập phân hai chữ số 0- Thêm số 0 vào phần lẻ thập phân: Chọn vùng dl và bôi đen  Nhắp chuột vào nút - Bớt một số 0 ở phần lẻ thập phân:Chọn vùng dl và bôi đen  Nhắp chuột vào nút 2/ Thay đổi phông chữ, cỡ chữ, màu chữ: ( Giống Word )3/ Định vị dl ( dùng cho các tiêu đề cột )- Chọn vùng dl và bôi đen - Nhắp chuột vào nút Format  Cells- htxh  chọn thẻ Alignment+Mục Horizontal:Căn dl theo chiều ngang ( chọn Center )+ Mục VertiCal: Căn dl theo chiều dọc ( chọn Center )+ Mục Wrap text: Đánh dấu vào mục này, dl sẽ tự động xuồng dòng khi chiều rộng ô không đủ + Orientation: Chọn hình thức trải dlNhắp chuột vào các nút để chọn độ nghiêng của dl  OK4/ Tạo các đường kẻ cho bảng -Chọn toàn bộ vùng dl cần kẻ và bôi đen - vào Format  Cells  htxh- Chọn thẻ Border+ Line Style: Kiểu đường kẻ+ Mục Out line: Kẻ khung viền quanh In Side:Kẻ các đường trong bảng + Khung Text: Kẻ đường phân cách giữa các cột, dòng  OKIV/ Bổ sung một số hàm sắp xếp dữ liệu1/ Hàm Rank= Rank(x;Danh sách các trị số ) - Xác định thứ hạng của giá trị x so với các giá trị có trong D/sVí dụ: Tính vị thứ ( xếp thứ mấy )trong Bài tập 5=Rank(M7;$M$7:$M$16) xác định giá trị trong ô M7 so với các giá trị trong các ô từ M7  M16 đứng thứ tự bao nhiêu2/ Hàm Vlookup=Vlookup(x;Bảng;Cột tham chiếu;Cách dò)- x: Dò tìm giá trị x ở cột bên trái của bảng, khi tìm có thì lệch qua bên phải để lấy giá trị trong ô của nó ứng với giá trị của x- Bảng: Gồm nhiều dòng, nhiều cột, cột bên trái luôn luôn chứa giá trị để dò tìm, các cột khác chứa giá trị tương ứng để tham chiếu - Cột tham chiếu: Là thứ tự cột trong bảng, cột đầu tiên là 1, cột tham chiếu từ thứ hai trở đi- Cách dò: Là 0 hoặc 1Nếu là 1: D/s ở cột bên trái của bảng phải chép theo thứ tự tăng dần Nếu là 0: D/s ở cột bên trái của bảng không cần chép theo thứ tự tăng dần Ví dụ: Bài tập 5- Tính điểm Ưu tiên: =Vlookup(F7;$C$21:$D$23;2;0)- Tính điểm Trung bình =(G7*2+H7*2+I7+J7+K7+L7)/7-Tính vị thứ:=Rank(M7;$M$7:$M$16)- Tính Kết quả:=IF(M7>=8,0;"Giỏi";IF(M7>=6,5"Khá";IF(M7>=5,0"TB";"Yếu")))3/ Các hàm chuỗi:- Hàm Left: =Left ( Chuỗi Text ; n )Cho kết quả là n kí tự của chuỗi text tính từ trái qua phảiVí dụ: Giả sử trong ô B5 chứa: Trung tâm tin học = Left(B5;3)  ( = Tru ) =Left(B5;2)  ( =Tr )- Hàm Right =Right ( chuỗi Text ; n )Cho kết quả là n kí tự của chuỗi Text tính từ phải qua trái Ví dụ: =Reght(B5;4)  ( = y Hà )4/ Sắp xếp dữ liệuExCel cho phép sắp xếp dl giữa các ô được chọn độc lập với các ô ngoài khu vực chọn. Việc sắp xếp dl được thực hiện tối đa theo 3 cộtVí dụ: Tên Họ đệm Ngày sinh Cách thực hiện:- Chọn toàn bộ vùng dữ liệu cần sắp xếp ( bôi đen )- Nhắp chuột vào Data  Sort  htxh- Nhắp chuột vào mục No header row- Chọn cột cần sắp xếp chính trong khung Sort by- Chọn 2 cột tiếp theo trong mục Then by+ Chọn Ascending: sx tăng dần+Chọn Descending: sx giảm dần - Chọn OKV / cơ sở dữ liệu trên bảng tính 1/ Khái niệm về cơ sở dữ liệu (CSDL)- Là tập hợp các thông tin DL được tổ chức theo cấu trúc dòng và cột. Để có thể liệt kê, truy tìm, xoá hay rút trích những dòng DL thỏa mãn một tiêu chuẩn nào đó một cách thuận tiện và nhanh chóng. Để làm được điều này ta phải tạo ra 3 vùng DL- Vùng CSDL ( Database )Gồm ít nhất có 2 dòng. Dòng đầu tiên chứa các tiêu đề cột gọi là tên vùng tin. Tên vùng tin phải là DL kiểu chuỗi và không trùng lặp. Các dòng còn lại chứa DL, mỗi dòng gọi là mẫu tin.- Vùng tiêu chuẩn ( Griteria )ít nhất có hai dòng. Dòng đầu tiên chứa tiêu đề cột, Các dòng còn lại chứa điều kiện.- Vùng trích DL ( Extract )Chứa các mẫu tin trong vùng CSDL thỏa mãn điều kiện vùng tiêu chuẩn 2 / Lọc Dữ liệu ( Rút - Trích DL )Ta phải tạo trước 3 vùng DL. Gồm 3 bước:- Chọn toàn bộ vùng CSDL và bôi đen- Vào Data  Chọn Filter  AdvanCed Filter  HTXH- Trong khung Action chọn copy to another location+ Mục List Range: Nhập đ/c vùng CSDL+ Mục Criteria Range: Nhập đ/c vùng tiêu chuẩn+ Mục Copy to : Nhập địa chỉ vùng trích DL OK- Các mẫu tin trong vùng CSDL thỏa mãn điều kiện vùng tiêu chuẩn sẽ được chép vào vùng trích DLss3/ Các dạng vùng tiêu chuẩn - Trích những người có số con >2 và chức vụ là "NV” - Trích những người có số con >2 hay chức vụ là "NV”* Nếu các ô đk khác cột có tính chất And (và)* Nếu các ô đk khác dòng có t/c OR (Hoặc)4/ Các hàm trong cơ sở dữ liệu a/ Hàm DSum=DSum(Vùng CSDL;n;Vùng tiêu chuẩn)Cho kết quả là tổng DL số trên cột thứ n của những mẫu tin trong vùng CSDL thỏa mãn đk vùng tiêu chuẩn Trong đó n là thứ tự cột cần tính tổng trong CSDL, cột đầu tiên là 1 tính từ trái qua phải Ví dụ: Tính tổng thu nhập của cán bộ nữ - Tạo vùng tiêu chuẩn D11 D12Tổng thu nhập cán bộ nữ = DSum(A2:I9;9;D11:D12) b/ Hàm Daverage=Daverage(Vùng CSDL;n;Vùng t/c) Cho kết quả là giá trị trung bình của các mẫu tin trong vùng CSDL thỏa mãn đk trong vùng tiêu chuẩn Ví dụ: c/ Hàm DMax=DMax(Vùng CSDL;n;vùng t/c) Cho kết quả là giá trị lớn nhất trên cột thứ n của các mẫu tin trong vùng CSDL thỏa mãn đk trong vùng tiêu chuẩn d/ Hàm DMin : Ngược lại với hàm DMaxe/ Hàm DCounta=DCounta(Vùng CSDL;n;Vùng T/c) Cho kq là các ô chứa DL trên cột thứ n của những mẫu tin trong vùng CSDL thỏa mãn đk vùng tiêu chuẩn Ví dụ: Thống kê xem có bao nhiêu cán bộ nữ D11 - Tạo vùng tiêu chuẩn: D12=DCounta(A2:I9;3;D11:D12) = 5VI / tạo lập biểu đồTạo từ dữ liệu trên bảng tính được thực hiện 4 bước như sau:Bước 1: -Nhập bảng DL - Chọn toàn bộ vùng DL cần lập biểu đồ và bôi đen - Nhắp chuột vào nút ChartWizard - Nhắp chọn kiểu biểu đồ ở khung trái - Chọn kiểu chi tiết ở khung phải - Nhắp vào Next  Bước 2Bước 2: - Chọn cách phân tích DL - Chọn Row: Vẽ theo dòng Column: Vẽ theo cột - Next  Bước 3Bước 3: Biểu đồ nháp - Chọn thẻ TitLer  Nhắp vào khung CharTitLer để gõ vào tiêu đề của biểu đồ - Tiêu đề trục X, Y- Chọn thẻ Data Lebels, Nhắp chuột vào dòng Show Lsbel and Percent để đưa số lượng phần trăm(%) lên biểu đồ - Nhắp Next  B4Bước 4: Chọn vị trí đặt biểu đồ - Trong khung As Object in: Chọn Sheet cần đặt biểu đồ  chọn Finish.VII / IN ấN 1/ Chọn cỡ giấy, hướng in- Vào File  chọn Page Setup  htxh- Chọn thẻ Page + Mục Orientation: Chọn hướng in Chọn Portrait : in dọc giấy Chọn LandsCape: in ngang giấy+ Mục Paper Size : Chọn cỡ giấy + Mục Print Quality: chất lượng in. (Chọn càng nhiều DPI nét chữ càng đẹp)  OK2/ Xem trước khi in Nhắp chuột vào nút Print Preview  Màn hình chế độ View xuất hiện+ Zoom: phóng to,thu nhỏ màn hình (dùng chuột)+ Print: Mở hộp thoại in để in ra giấy+ SetUp: Mở ht Page SetUp để chọn cỡ giấy, hướng in+ Margine: Đặt lề  Cloes: Đóng chế độ View3/ In ra giấy - Vào File  Chọn Print  htxh- Mục Printer : Chọn kiểu máy in- Mục Print range : Chọn cách in ra All : In toàn bộ bảng tính Pages From:......To....... (in từ trang ........đến trang .......) - Mục Copies: Chọn số lần in ra  OK VIII / Bảo vệ bảng tính * Bảo vệ không cho thay đổi Dữ liệu:- Mở tệp bảng tính cần đặt khoá- Vào Tool  chọn Protection  Protect Sheet  ht 1 xh- Gõ vào từ khoá và ấn   ht 2 xh - Gõ lại từ khoá một lần nữa và ấn * Gỡ khoá: - Vào Tool  chọn Protection  Un Protect Sheet  htxh- Gõ vào đúng từ khoá đã đặt và ấn  Hướng dẫn bài tậpBTTH 3.7