Đề tài Những tính năng cơ bản – nâng cao của Excel

tin thoả mãn điều kiện trên cột khai báo trong cơ sở dữ liệu =DAVERAGE(Database,Field,Criteria) Database:Dãy ô được xác định trong cơ sở dữ liệu. Field: Tên trường hay một dãy các ô biểu diễn cột muốn lấy trung bình. Criteria: Tên vùng hay một dãy các ô chứa tiêu chuẩn để xác định những giá trị trong trường cần tính trung bình. Hàm Dcount. Đếm số ô thoả mãn tiêu chuẩn được định nghĩa trong đối Criteria. =DCOUNT(Database,Field,Criteria) Database:Dãy ô được xác định trong cơ sở dữ liệu. Field: Tên trường hay một dãy các ô biểu diễn cột muốn cần đếm. Nếu không chọn số này thì Excel sẽ đếm toàn bộ cơ sở dữ liệu. Criteria: Tên vùng hay một dãy các ô chứa tiêu chuẩn để xác định trường không trống trong khi đếm. Hàm Dmax Lấy giá trị lớn nhất trong một cột của những mẫu tin dữ liệu. =DMAX(Database,Field,Criteria) Database:Dãy ô được xác định trong cơ sở dữ liệu. Field: Tên trường hay một dãy các ô biểu diễn cộtmà bạn muốn tìm giá trị lớn nhất. Criteria: Tên vùng hay một dãy các ô chứa tiêu chuẩn để xác định ràng buộc trên mẫu tin nào cần tìm giá trị lớn nhất. Hàm Dmin Lấy giá trị nhỏ nhất trong một cột của những mẫu tin dữ liệu =DMIN(Database,Field,Criteria) Database:Dãy ô được xác định trong cơ sở dữ liệu. Field: Tên trường hay một dãy các ô biểu diễn cộtmà bạn muốn tìm giá trị nhỏ nhất. Criteria: Tên vùng hay một dãy các ô chứa tiêu chuẩn để xác định ràng buộc trên mẫu tin nào cần tìm giá trị nhỏ nhất. Chú ý: Trong khi giải quyết các bài toán về Excel chúng ta thường gặp phải những thông báo lỗi sau đây: TÊN LỖI Ý NGHĨA Name? Lỗi này xảy ra khi Excel không thể nhận diện được tên trong công thức chẳng hạn: bạn không đặt chuỗi trong dấu nháy kép hay viết sai tên hàm... #VALUE! Lỗi này là lỗi giá trị , chẳng hạn tính toán trên ký tự không đúng hay các đối không phù hợp với hàm. #NUM! Lối này là lỗi về số trong hàm chẳng hạn: Đưa vào một số quá lớn hay quá bé, hay số này không tồn tại ...nói chung nhập một đối số không phù hợp. #DIV/0 Đây là lỗi chia cho zero(chia cho 0), Excel không thể thực hiện phép tính được, Excel mặc định những ô trống là bằng 0. #NULL! Lỗi này là do trong công thức tồn tại phần giao rỗng. #REF! Lỗi này là lỗi tham chiếu ô, chẳng hạn : Xoá các dòng hay cột mà dòng hay cột đó được dùng trong công thức hay phép tham chiếu xa trên các chương trình không chạy được. N/A Lỗi này có ý nghĩa tuỳ theo công thức chẳng hạn: Công thức đẫ tham chiêú đến một giá trị không tồn tại. Bài toán tổng hợp hãy xem ví dụ sau BẢNG THỐNG KÊ HOÁ ĐƠN HÀNG MÁY IN Stt Mã hàng Tên hàng Chủng loại Ngày bán Sl Hoá đơn đơn giá Thuế VAT Doanh thu 1 P121L 5/8/2005 8 X 2 P300K 6/8/2005 6 3 P110L 6/8/2005 7 X 4 P110L 9/8/2005 12 5 P810P 11/8/2005 8 X 6 P895P 11/8/2005 10 7 P810P 12/8/2005 3 8 P300K 13/8/2005 2 X 9 P121L 14/8/2005 4 10 P300K 15/8/2005 9 11 P580K 16/8/2005 7 X 12 P810L 17/8/2005 14 X 13 P810L 18/8/2005 15 X 14 P580K 20/8/2005 3 15 P810L 29/8/2005 1 Bảng tên hàng và hoá đơn Mã hàng Tên hàng đơn giá 300 LQ300 193 580 LP580 418 810 Stylus Photo 810 196 895 Stylus Photo 895 347 110 HP Larser 1100 188 121 Samsung1210 154 810 CanonLBP810 145 Yêu cầu thực hiện 1 tính tên hàng dựa vào bảng tến hàng và đơn giá 2 tính chủng loại dựa vào ký tự thứ hai của mã hàng 3 đơn giá dựa vào bảng tên hàng và đơn giá 4 thuế VAT:nếu hoá đơn đánh dấu x thì mới tính 5% thuếu VAT còn ngược lại thì không tính 5 doanh thu được tính :đơn giá *sl * tỷ giá +thuế VAT 6 đặt lọc cao cấp advanced:lọc loại máy in HP Larser1100 với đơn giá >=188,máy in LQ 300 với mức thuế VAT 386 7 thống kê theo nhóm chủng loại bài giải Bảng thống kê hoá đơn hàng máy in stt mã hàng tên hàng chủng loại ngày bán sl hoá đơn đơn giá thuế VAT doanh thu 1 P121L samsung 1210 1 05/08/2005 8 x 154 7.7 1239.7 2 P300K LQ300 3 06/08/2005 6 193 1158 3 P110L HP Larser 1100 1 06/08/2005 7 x 188 9.4 1325.4 4 P110L HP Larser 1100 1 09/08/2005 12 188 2256 5 P810P stylus Photo 810 8 11/08/2005 8 x 196 9.8 1577.8 6 P895P stylus Photo 895 8 11/08/2005 10 347 3470 7 P810P stylus Photo 810 8 12/08/2005 3 196 588 8 P300K LQ300 3 08/15/05 2 x 193 9.65 395.65 9 P121L samsung 1210 1 8/15/2005 4 154 616 10 P300K LQ300 3 16/08/2005 9 193 1737 11 P580K LQ580 5 17/08/2005 7 x 418 20.9 2946.9 12 P810L stylus Photo 810 8 18/08/2005 14 x 196 9.8 2753.8 13 P810L stylus Photo 810 8 20/08/2005 15 x 196 9.8 2949.8 14 P580K LQ580 5 25/08/2005 3 418 1254 15 P810L stylus Photo 810 8 29/08/2005 1 196 196 Bảng tên hàng và đơn giá BẢNG THỐNG KÊ Thống kê nhóm 1 4 Thống kê nhóm 3 3 thống kê nhóm 5 2 Thống kê nhóm 8 6 mã hàng tên hàng đơn giá USD 300 LQ300 193 580 LQ580 418 810 stylus Photo 810 196 895 stylus Photo 895 347 110 HP Larser 1100 188 121 samsung 1210 154 810 canon LBP 810 145 Bảng điều kiện tên hàng đơn giá thuế VAT HP Larser 1100 >=188 LQ300 <=386 Bảng lọc stt mã hàng tên hàng chủng loại ngày bán sl Hoá đơn đơn giá thuế VAT doanh thu 3 P110L HP Larser 1100 1 06/08/2005 7 x 188 9.4 1325.4 4 P110L HP Larser 1100 1 09/08/2005 12 188 2256 8 P300K LQ300 3 08/15/05 2 x 193 9.65 395.65 Trước tiên ta làm thao tác với cột đầu tiên là cột số thứ tự ta làm thao tác như sau: đánh số 1 vào ô A5 nhấn phím Ctrl kéo xuống đến ô 19 Để biết được tên hàng trước tiên ta cần phải biết được tham chiếu tuyệt đối , tương đối và tham chiếu trang tính Một ô trong bảng tính được xác định bởi cặp hai giá trị toạ độ của trục ngang và dọc trục ngang theo thứ tự A,B,C và trục dọc theo thứ tự 1,2,3 Ví dụ ô C6 chứa xâu mục hàng (LQ300) Từ một ô mới chúng ta có thể sử dụng giá trị của các ô đã có bằng cách sử dụng tham chiếu theo địa chỉ toạ độ như trên . Ví dụ ô J5 được tính bởi F5*H5 và được nhập theo công thức là sự kêt hợp cả hàm if =if(G5=”x”,F5*H5+I5,F5*H5) công thức này được sao chép thông minh tới các ô khác trong cột J5 đến J19 bằng cách nhấp chuột vào J5 xuất hiện dấu cộng ta kéo dấu vuông bên dưới co tới ô cuối cùng các công thức sẽ tự động gióng theo chỉ số tương ứng từ 5 đến 19 một tham chiếu toạ độ xy còn có các biểu diẽn khác là $XY,X$Y,$X$Y. Dấu $ ược sử dụng để cố định giá trị toạ độ đi sau nó ,bất kể cả khi công thức chứa nó được sao chép tới vị trí mới . Khái niệm tham chiếu đó gọi là tham chiếu tuyệt đối . _Tham chiếu giữa các trang tính với bài này tìm được tên hàng thì ta phải sử dụng công thức của hàm tham chiếu =VLOOKUP(value(mid(B5,2,3)),$B$21:$D$28,2,0) ở đây không đơn thuần chỉ là một công thức đơn giản mà nó còn đòi hỏi sự kết hợp giữa các hàm Hàm VLOOKUP là hàm tham chiếu theo cột VALUE : Là hàm chuyển đổi từ chuỗi sang số MID: Hàm trích giữa Thay vì gõ tên hàng được quyết định bởi mã hàng chúng ta sử dụng quan hệ giữa mã hàng và tên hàng được mô tả trong bảng phụ là bảng tên hàng và đơn giá Cách thức tự động gán các giá trị mà chỉ có Excell mới có Chú ý :$B$21:$D$28 ký hiệu này cho phép chúng ta tham chiếu tới những ngôi trên một trang tính khác, thậm chí có thể là trên cả tệp Excell khác ( dùng ký hiệu ‘tên tệp !tên trang!địa chỉ ô) Đối với chủng loại ta sử dụng công thức sau =MID(B5,2,1) Với đơn giá talàm tương tự như đối với tên hàng chỉ co thay đổi số cột ở bang tham chiếu là cột thứ 3 mà thôi. Tính thuế ta tính theo công thức =IF(G5=”x”,H5*5%,””) Lọc cao cấp có điều kiện nhờ advanced theo đề bài ta phân tích đây không phải là lọc dữ liệu theo điều kiện tuỳ chọn mà lọc có điều kiện nếu theo điều kiện tuỳ chọn thì cho phép ta làm công việc xác định tiêu chí có liên quan đến nhiều cột với cả toán tử logic AND,OR xác định rõ 2 hoặc 3 tiêu chí cho một cột cụ thể -sử dụng các hàm trong tiêu chí lọc Ví dụ: Liệt kê những nhân viên có lương hơn 25%trung bình Vùng chứa điều kiện lọc Lệnh advanced filterd đòi hỏi bạn phải có điều kiện lọc trong một vùng bảng tính tách riêng khỏi danh sách của bạn . Do toàn bộ các dòng bị ẩn đi khi việc lọc được tiến hành do vậy không nên đặt vùng tiêu chuẩn song song với danh sách mà thay vào đó hãy đặt nó ở trên hoặc dưới danh sách. Một vùng tiêu chuẩn gồm ít nhất hai dòng .Bạn đưa vào một hoặc hai tiêu đề cột ở đầu dòng và tiêu chuẩn lọc từ dòng thứ hai trở đi . Vùng điều kiện lọc được xây dựng theo các bước ; Chèn một vài dòng mới vào đầu bảng tính (hoặc thêm vào cuối) để tạo không gian chứa điều kiện lọc Gõ vào điều kiện lọc cho các cột chú ý dòng đầu tiên chứa tiêu đề của cột trong bảng tính. Các điều kiện trên cùng dòng sẽ được liên kết với nhau bằng toán tử AND còn các điều kiện khác dòng được liên kết bằng toán tử OR. Chọn lệnh advanced filter từ menu data và nhập thông tin về vùng chứa dữ liệu cũng vùng chứa điều kiện lọc sau đó bấm OK. Dữ liệu đích có thể đè vào dữ liệu gốc hoặc sao chép sang vùng mới tuỳ bạn chọn . Kết quả là chúng ta thu được thông tin về máy in HP Lrser 1100 với đơn giá >=188và loại máy in LQ 300 với mức thuế VAT là <=386 như bảng lọc ở trên tôi đã làm Thống kê theo nhóm Ta sử dụng công thức =COUNTIF($D$5:$D$19,”1”) Đây là ta thống kê theo nhóm chủng loại 1, còn chủng loại 3,5,8 làm tương tự Bài tập này coi như ta đã giải quyết xong. CHƯƠNG II XỬ LÝ DỮ LIỆU VÀ KIỂM TRA LỖI CHÍNH TẢ I. Các thao tác cơ bản khi làm việc với excel 1.Các thao tác làm với Sheet. Chọn Sheet làm việc: Nháy trái chuột vào tên Sheet cần làm việc Chèn thêm Sheet B1:Chọn Sheet bất kỳ và nháy phải chuột =>xuất hiện hộp thoại B2: Chọn Insert =>Xuất hiện hộp thoại B3:Tìm và chọn biểu tượng có tên Worksheet B4: Ok Xoá Sheet B1: Chọn một Sheet cần xoá và nháy phải chuột =>xuất hiện hộp thoại B2: Chọn Delete =>Xuất hiện hộp thoại B3: Nhấn Ok để đồng ý Đổi tên Sheet B1: Chọn Sheet cần đổi tên và nháy phải chuột =>Xuất hiện hộp thoại B2: Chọn Rename B3: Nhập tên mới vào cho Sheet từ bàn phím. 2.Các thao tác với dòng, cột. Chèn thêm cột(dòng) B1: Bôi đen số cột hoặc dòng cần chèn thêm B2: Nháy phải chuột =>Xuất hiện hộp thoại B3: Chọn Insert xoá bớt cột(dòng) B1: Bôi đen số cột hoặc dòng cần xoá B2: Nhay phải chuột =>Xuất hiện hộp thoại B3: Chọn Delete Thay đổi kích thước của cột(dòng) B1: Di chuyển chuột đén mép cột (hoặc dòng)cần thay đổi kích thước B2: Giữ tay vào trái chuột và dùng động tác kéo thả 3.Các thao tác với dữ liệu Nhập dữ liệu B1:Đưa con trỏ chuột đến ô cần nhập dữ liệu B2: Nhập bình thường từ bàn phím Sửa dữ liệu B1: Nháy trái chuột vào ô dữ liệu cần sửa B2: Thực hiện quá trình suqả như bình thường Định dạng dữ liệu dạng số B1: Bôi đen phần dữ liệu cần định dạng B2: Sử dụng các nút lệnh trên thanh công cụ $ Định dạng con số ở dạng tiền tệ % Định dạng cn số ở dạng phần trăm Và những con số giẩm dần hay tăng dần sau dấu chấm Hợp các ô B1: Bôi đen ô cần hợp nhất B2: Nháy chuột vào biểu tượng hợp nhất trên thanh công cụ Định dạng kiểu chữ ,màu chữ ,đường bao... cho dữ liệu. B1: Bôi đen phần dữ liệu cần định dạng B2: Vào thực đơn format, chọn Cell =>Xuất hiện hộp thoại B3: Lựa chọn các thẻ để định dạng Number: Định dạng dữ liệu kiểu số Alignment: Căn chỉnh dữ liệu Font: Chọn kiểu chữ, cỡ chữ, màu chữ Border: Tạo các kiểu đường viền cho dữ liệu Pattens: đổi màu nền B4: Nhấn Ok II. Thiết lập Form đơn giản để nhập/tìm kếm dữ liệu,Sắp xếp và lọc dữ liệu 1.Thiết lập Form đơn giản để nhập/tìm kếm dữ liệu Nếu bạn sử dụng các chương trình máy tính thì bạn quen sử lý các văn bản ghi dữ liệudạng Form. Với Excel bạn cũng có tể làm như vậy một cách đơn giản. Chọn ô bất kỳ trong danh sách của bạn Chọn lệnh Form từ menu Data và khi hộp thoại xuất hiện vào nút Criteria để đưa vào điều kiện tìm kiếm. Nhập vào các giá trị mà bạn muốn tìm kiếm cho các trường trong Form Nhấp chuột vào nut Find Next để hiển thị dòng đầu tiên trong trang danh sách thoả mãn điều kiện tìm kiếm của bạn. Tiếp tục Find next hoặc quay ngược lại với Find Prev Với Forn bạn cũng có thể dùng phím New ,’Delete’ và cuộn lên ,cuộn xuống để thêm, xoá , và duyệt các dòng tương tự như đôí với bản ghi trong cơ sở dữ liệu. 2.Sắp xếp dữ liệu Trong Excel có thể sắp xếp số liệu trong một phạm vi được chọn một cách độc lập với các ô ngoài khu vực chọn. Việc sắp xếp có thể thực hiện trên hàng ngang hoặc cột dọc. Excel cho phép sắp xếp dữ liệu tối đa theo ba khoá. Ví dụ nếu một danh sách lương sắp xếp theo tên cùng tên sắp xếp theo họ thì khoá thứ nhất là dữ liệu cột tên khoá thứ hai là dữ liệu cột họ . Các bước để sắp xếp dữ liệu : Chọn vùng dữ liệu muốn sắp xếp, Thực hiện lệnh Data/sort hộp thoại sort xuất hiện ra . Xử lí hộp thoại: Chọn tiêu đề khoá sắp xếp thứ nhất và thứ tự sắp xếp Chọn tiêu đề cho từ khoá sắp xếp thứ hai và Thứ tự sắp xếp nếu có Chọn OK. Chú ý muốn chỉ định sắp xếp theo hàng hay theo cột thì trong hộp thoại sort options xuất hiện. Trong hộp thoại này có hai lựa chọn : Sort to button (xếp theo hàng) Sort left to righ (xếp theo cột). Ví dụ: stt Họ tên Chưc vụ Lương tháng 1 Nguyễn văn Anh GĐ 1 500 000 2 Phạm Văn Dương Trưởng phòng 1 000 000 3 Nguyễn Văn Hùng PGĐ 1 000 000 4 Lê Anh Tuấn Nhân viên 900 000 Trong hộp thoại Sort: Ascending: Sắp xếp thứ tự tăng dần Descending: Sắp xếp thứ tự giảm dần Sắp xếp theo cột Sort by: Chọn tiêu đề của cột làm khoá sắp xếp chính và chọn thứ tự sắp xếp cho cả khoá Then by: Để chọn tiêu đề làm khóa sắp xếp phụ và chọn thứ tự sắp xếp tương ứng Có hai Then by, then by thứ hai phía dưới để khai báo khoá sắp xếp phụ thứ hai. Chú ý rằng nếu bạn đánh dấu cả cột tiêu đề thì bấm vào nút My list has Header row còn không thì chọn No header row. Ta có thể sắp xếp dữ liệu tuỳ vào biến Trong thực tế có những dữ liệu mà Excel không thứ tự phải sắp xếp như thế nào ví dụ như các ngày trong một tuần khi đó bạn phải giúp Excel xác định thứ tự do chính bạn định nghĩa ra. Từ menu Tool\Options\Custmlist. Bấm vào NewList trong danh sách bên trái Gõ theo trình tự các giá trị mà bạn muốn định nghĩa mỗi giá trị được nhập trên một dòng riêng biệt. Nếu danh sách mà bạn muốn định nghĩa là khá dài thì bạn có thể chỉ ra định nghĩa thứ tự bằng cách nhập danh sách từ một vùng cho trước trên bảng tính Nếu tinh ý bạn sẽ nhận thấy rằng tiếng việt không hoàn toàn được sắp xếp tốt với chức năng Sort mặc định của Excel. Bạn thử nghĩ xem chúng ta nên làm cách nào. 3.Lọc dữ liệu Đối với việc quản lý một danh sách hay một cơ sở dữ liệu của Excel, ngoài các thao tác sắp xếp ra, chúng ta còn có nhu cầu tạo lập hoặc hiển thị những danh sách khác từ danh sách ban đầu mà các danh sách mới này chỉ gồm những dòng thoả mãn một số điều kiện nào đó do người dùng tạo ra. Chức năng Autofilter hoặc Advancedfilter sẽ giúp bạn thực hiện một cách nhanh chóng và chính xác những vấn đề trên. 3.1. Lọc dữ liệu tự động(Autofilter) Ta hãy xem ví dụ STT Tên HV Phái NS Toán Văn N ngữ Đ thêm ĐTN ĐTB ĐKquả Xếp loại Xếp hạng 1 Hiệp Nữ 70 9 8 7 0.5 7 8.14 8.143 Giỏi 3 2 Nga Nữ 74 4.5 6 4 0.75 4 4.79 5.536 TB 9 3 Hà Nữ 68 8 8.5 9 0.5 8 8.43 8.429 Giỏi 2 4 Sơn Nam 72 4 6 7 0.75 4 5.43 5.429 TB 11 5 Lâm Nam 68 10 8 9 0.5 8 9.14 9.143 Giỏi 1 6 Hạnh Nữ 74 4 4.5 6 0.75 4 4.71 5.464 TB 10 7 Nam Nữ 71 3 9 7 0.5 3 5.86 5.857 Yếu 8 8 Linh Nữ 74 7.5 5 6 0.75 5 6.36 6.357 TB 7 9 Diễm Nữ 69 4 5 5.5 0.5 4 4.71 5.214 TB 12 10 Khoa Nam 73 3 5 5 0.75 3 4.14 4.893 Yếu 13 11 Hơng Nữ 74 6 6 8 0.75 6 6.57 6.571 Khá 6 12 Thảo Nữ 66 10 4 7 0.5 4 7.43 7.429 TB 4 13 Minh Nam 60 9 5 6 0.5 5 7 7 Khá 5 Yêu cầu lọc ra những người đạt điểm 10 môn toán. STT Tên HV Phái NS Toán Văn N ngữ Đ thêm ĐTN ĐTB ĐKquả Xếp loại Xếp hạng 1 HiÖp N÷ 70 9 8 7 0.5 7 8.14 8.143 Giái 3 2 Nga N÷ 74 4.5 6 4 0.75 4 4.79 5.536 TB 9 3 Hµ N÷ 68 8 8.5 9 0.5 8 8.43 8.429 Giái 2 4 S¬n Nam 72 4 6 7 0.75 4 5.43 5.429 TB 11 5 Lâm Nam 68 10 8 9 0.5 8 9.14 9.143 Giỏi 1 6 H¹nh N÷ 74 4 4.5 6 0.75 4 4.71 5.464 TB 10 7 Nam N÷ 71 3 9 7 0.5 3 5.86 5.857 YÕu 8 8 Linh N÷ 74 7.5 5 6 0.75 5 6.36 6.357 TB 7 9 DiÔm N÷ 69 4 5 5.5 0.5 4 4.71 5.214 TB 12 10 Khoa Nam 73 3 5 5 0.75 3 4.14 4.893 YÕu 13 11 H¬ng N÷ 74 6 6 8 0.75 6 6.57 6.571 Kh¸ 6 12 Thảo Nữ 66 10 4 7 0.5 4 7.43 7.429 TB 4 Đây là kết quả tôi đã thực hiện được. Để thực hiện Autofilter đầu tiên ta lựa chọn một ô bất kỳ trong danh sách. Sau đó chọn vào Data\Filter\Autofilter, Excel sẽ hiển thị các hộp thả xuống bên cạnh các tiêu đề cột trong danh sách của bạn. Nhấp chuột vào mũi tên tiếp theo đến bất kỳ một giá trị nào trong danh sách để xác định tiêu chí lọc cho danh sách, bạn có thể xác định nhiều hơn một tiêu chí lọc. Khi lọc Excel cho bạn biết bao nhiêu dòng thoả mãn điều kiện (tiêu chuẩn) lọc của bạn và hiển thị số bản ghi tìm thấy trong tổng số trên thanh trạng thái. Ngoài việc chọn một trong những giá trị cụ thể Excel còn có một số tuỳ chọn All, Top 10, Custom... sẽ giúp chúng ta rất nhiều trong quá trình lọc hoặc tìm kiếm bằng chức năng Autofilter. All: Trở về hiện trạng của danh sách như trước lúc chọn diều kiện lọc tại cột hiện hành. Blanks: Hiển thị những dòng của danh sách mà tại cột này chưa nhập dữ liệu. Non blanks: ẩn đi những dòng để trống ,chỉ liệt kê các dòng chứa dữ liệu. Top 10: Sẽ lọc một danh sách gồm 10 record mà giá trị đạt được của chúng lớn hơn các mẫu tin còn lại. Custom: Sử dụng trong trường hợp lọc một danh sách mà giá trị của cột hiện hành cần phải thoả mãn hai điều kiện Đối với tiêu chuẩn lọc rắc rối hơn, bạn có thể chọn mục custom trong danh sách thả xuống của mỗi cột. Bạn có thể nhập một hoặc hai tiêu chuẩn trong hộp thoại của Autofilter và có thể chọn toán tử logic And hoặc Or để liên kết hai tiêu chuẩn với nhau. Đối với giá trị tìm kiếm là văn bản thì ccs kí hiệu đặc biệt sau có thể sử dụng. Ký tự? dùng đại diện cho một ký tự bất kỳ. Ví dụ ‘S?n’ có thể là ‘son’ hoặc ‘sơn’ hay cũng có thể là ‘sun’/ Ký tự * dùng đại diện cho một xau ký tự bất kỳ. Ví dụ ‘S*n’có thể là ‘Station’, ‘Son’ ... Chú ý nếu bạn đặt nhiều điều kiện lọc trên các cột khác nhau thi các điều kiện sẽ được liên kết với nhau theo toán tử logic And. Để huỷ bỏ chức năng Autofilter bạn làm các thao tác như lúc chúng ta vào. 3.2Lọc dữ liệu bằng lệnh Advancefildter. Trước tiên ta vào Data\filter\Advanced Filter để thực hiện công việc ở trên ví dụ tôi đã nói rõ phần này. Vùng điều kiện lọc được xây dựng theo các bước: Chèn một vài dòng mới vào phần đầu bảng tính (hoặc cuối bảng tính )để tạo không gian chứa điều kiện lọc. Gõ điều kiện lọc cho các cột, chú ý dòng đầu tiên chứa tiêu đề của cột trong bảng tính. Các điều kiện trên cùng dòng sẽ được liên kết với nhau băng toán tử And còn các điều kiện khác dòng được liên kết bằng toán tử Or. Chọn lệnh Advanced Filter từ menu và nhập thông tin từ vùng chứa dữ liệu cũng như chứa điều kiện lọc. Sau đó nhấn Ok dữ liệu đích có thể đặt đè vào dữ liệu gốc hoặc sao chép sang một vùng mới tuỳ bạn . Trong hộp thoại trên gồm có các thành phần: Action: Chọn phương thức xử lý các mẫu tin thoả mãn điều kiện lọc : Filtẻ the list, in-place: Hiển thị những dòng thoả mãn điều kiện lọc . Copy to another location: Sao chép các dòng thoả mãn điều kiện đến vùng khác. List range: Khai báo phạm vi chứa ô danh sách cần lọc Criteri range: Khai báo địa chỉ khối ô chứa bảng diều kiện . Copy to: Phần này chỉ có tác dụng khi bạn đánh dấu chọn mục copy to anther location. Unique Record Only: Nếu mục này được đánh dấu thì Excel chỉ hiển thị hoặc sao chép những dòng trùng nhau. Với các bước thực hiện kết quả như trên là chúng ta thu được thông tin mà chúng ta cần thực hiện. III.Tính năng bảo vệ trong Excel 1.Bảo vệ sổ tính. Đối với sổ tính ,bạn có thể bảo vệ Structruc(cấu trúc ): ngăn ngừa người dùng sắp sếp lại trang trong sổ tính . Window(cửa sổ )ngăn ngừa người dùng dịch chuyển hoặc định lại kích cỡ của cửa sổ tính Chỉ người dùng có mật khẩu hợp lệ là được phép thực hiện các thao tác sửa đổi trên . 2.Bảo vệ trang tính. Đối với trang tính bạn có thể bảo vệ : Contents(nội dung) : ngăn ngừa người dùng sửa đổi các ô trong trang tính. Objects(đối tượng ) : ngăn ngừa người dùng sửa đổi các đối tượng đò hoạ. Scenarois(kịch bản) ngăn ngừa người dùng sửa đổi các kịch bản phân tích theo điều kiện What-if Bạn có thể chọn hoặc huỷ ba thuộc tính trên. Chỉ người dung có mật khẩu hợp lệ là được dùng các thao tác sửa đổi trên . Trong thực tế bạn thường chỉ muốn khoá một số vùng và cho những người dùng còn lại có quyền nhập và sửa dữ liệu trên phần hạn chế còn lại. Mặc định Excel khoá toàn bộ các ô trên trang tính bạn có thể mở khoá bằng cách : Đánh dấu chọn vùng muốn mở khoá Chọn mục fomat cells trong menu qua phím phải chuột Trong mục Protection của hộp thoại fomat cellsbỏ dấu chọn locked. 3.Bảo vệ vùng dữ liệu trong trang. Bạn có thể chọn chức năng này để quản lý tốt hơn nhóm người dùng được phép thao tác tới một vùng định trước thậm chí bạn xác lập quyền thâm nhập khác nhau cho nhiều nhóm người dùng đối với các vùng dữ liệu . tính năng này tương đối phức tạp và đòi hỏi một chút kiến thức về quản lý người dùng và nhóm người dùng trên máy vì vậy bạn có thể bỏ qua rắc rối này bằng cách . Chọn chức năng tools/protection/ allow usertoeditranges: bấm phím new để bổ sung các vùng cần quản lý(cho phép người khác soạn thảo trên vùng đó) . chọn một vùng đã tạo trong danh sách và bấm pemision để bổ sung người dùng hoăc nhóm người dùng được quyền thao tác với vùng đã chọn. 4.Gỡ bỏ chế độ bảo vệ. Từ menu bar vào Tool\Protection\Unprotect Sheet... Nhập mật mã trước. Nhấp Ok để bỏ chế độ bảo vệ. IV.Kiểm tra lỗi chính tả Chọn ô cần kiểm tra lỗi chính tả, nếu cần kiểm tra toàn bộ bảng tính thì nhấp vào ô bất kỳ. Nhấp chọn biểu tượng Spelling trên thanh công cụ Standard. Khi bộ kiểm tra lỗi chính tả ta nhận dạng từ không có trong từ điển, hãy thực hiện thay đổi bằng cách sử dụng các tuỳ chọn trong hộp thoại này. Nếu muốn kiểm tra lỗi chính tả Text bằng một ngôn ngữ khác , trong hộp thoại Dictionary Language, nhấp chọn ngôn ngữ mà bạn muốn thực hiện kiểm tra. Chú ý : Việc kiểm tra lỗi chính tả trong hộp thoại Spelling là kiểm tra bằng ngôn ngữ tiếng anh. V. Sử dụng tính năng Autocorrect Options Với những từ lặp đi lặp lại nhiều lần trong văn bản gõ nhiều lần hay copy rất mất thời gian, để khắc phục điều này bạn sử dụng tính năng Autocorrect. Từ menu bar vào tool\ Autocorrect Options...chọn nhãn Autocorrect. Tại khung Replace: Gõ nhập chữ đại diện chữ cho dòng chữ bên khung With Khung With nhập dong chữ cần thay. Gõ nhập xong nhấn Add để cộng thêm vào . Nhấp Ok để chấp nhận, Khi soạn thảo trong bảng tính gặp từ vừa đặt nó lập tức thay thế dòng chữ trong khung With. VI.Chuẩn bị trang in Trang tính Excel không giới hạn khuôn khổ bảng dữ liệu trong trang in định trước . người ta có thể soạn bảng dữ liệu có kích thước tuỳ ý và sau đó Excel sẽ cắt ra thành các trang in. Thay vì để Excel cắt và in toàn bộ trang tính chúng ta nên tự xác định vùng dữ liệu định in bằng cách bôi đen trang in và chọn menu File.printArea. như vậy trên trang tính có thể chứa nhiều kết quả tính toán trung gian nhưng chỉ có kết quả cuối cùng mới được chọn và in ra . bước tiếp theo la chọn File.pagesetup.để căn chỉnh vị trí trên trang giấy. Có thể chọn hộp kiểm ‘center on page horizontally’để căn vào giữa theo chiều ngang và chọn ‘center on page vertically’để căn giữa theo chiều dọc. Sau đó chọn File print preview để xem lại lần cuối trước khi in ra. CHƯƠNGIII BIỂU ĐỒ VÀ CHÈN HÌNH ẢNH I.Làm việc với biểu đồ Người ta vẫn thường nói “Một hình ảnh nói được nghìn từ ‘cách trình diễn ấn tượng nhất là hãy để các hình ảnh tự giải thích cho dữ liệu gắn kèm. 1. Khái niệm biểu đồ Biểu đồ là đồ thị biểu diễn dữ liệu bảng tính. Các biểu đồ làm cho dữ liệu của bảng tính phức tạp trỏ thành trực quan và dễ hiểu hơn. Biểu đồ biến đổi từ các hàng và các cột trên bảng tính thành hình khối, đồ thị .. ..Biểu đồ các trục trên đó có các giá trị tỷ lệ tương ứng với các giá trị dữ liệu trên bảng tính . 2. Các bước tạo biểu đồ Xét bảng tính dưới đây về thu nhập của một đơn vị sản xất nông nghiệp trên ba ngành nghề : trồng trọt , chăn nuôi ,thủ công từ năm 1999 đến năm 2005 (đơn vị là ngàn đồng) Để tạo biểu đồ này ta có hai cách chọn lựa Chọn chart từ menu insert Nhấn vao nút chart wizard Tạo đồ thị từ dữ liệu của cột trồng trọt .Các bước thực hiện Dùng chuột chọn vùng từ A1 : B8 Nhấy vào biểu tượng chart wizard trên thanh công cụ để hiển thị hộp thoại chart wizard -step of 4 - chart wizard chọn lớp standard types, trong mục chart type chọn column, trong mục chart sub type chọn một trong các mẫu con Nháy nút next hộp thoạ