Những tính năng cơ bản – Nâng cao của Excel

h năng này cao cấp nhưng bù lại bạn có thể sử dụng dữ liệu hoặc sử dụng kết quả của mình hay cho người khác. Mỗi người có thể phụ trách một phần dữ liệu của mình nhưng báo cáo là thành quả của nhóm –một mô hình cộng tác lý tưởng. Đây là những vùng cho phép và không cho phép sửa đổi. Khi chia sẻ hẳn nhưng người khác có thẻ sơ hay cố ý thay đổi dữ liệu của bạn. Điều này quả là không mong muốn bạn hãy cân nhắc kỹ vùng nào cố định, vùng nào chia sẻ chung. Nêú các vùng quá rời rạc và vụn vặt thì rất khó quản lý và bạn rễ mắc lỗi. Xác lập quy ước tổ chức dữ liệu theo thời gian và phiên bản: Đối với dữ liệu được cập nhật theo thời gian bạn nên lưu dữ liệu dưới các tên khác nhau theo phiên bản để dễ dàng khôi phục và thống kê, phân tích theo thời gian. Ví dụ bạn có thể bổ sung ngày tháng vào cuối tên tệp lưu trữ như là phiên bản cho dữ liệu sổ tính. Bước 4 :Tạo báo cáo từ dữ liệu đã có. Xử lý và phân tích dữ liệu: Quyết định sử dụng loại biểu đồ ,công thức xử lý dữ liệu những cột nào sẽ xuất hiện trong báo cáo. Tổ chức sắp đặt trang in; Hãy chọn vị trí đặt dữ liệu , biểu đồ, tiêu đề chính, dòng đầu, cuối trang và thực hiện các bước dưới đây. Xây dựng các khung hình khác nhau cho một bộ nhớ dữ liệu và lưu trữ lại. Thông thường với cùng một bộ dữ liệu chúng ta có thể sinh ra nhiều báo cáo khác nhau. Nếu công việc là định kỳ mà mỗi lần báo cáo lại phải chọn lọc , sắp xếp và định dạng lại trang in cho bảng tính thì quả là mất thời gian và dễ gây sai sót. Excel cho phép bạn lưu trữ lại các mẫu báo cáo để sử dụng lại về sau. Bạn hãy nghĩ tới bảng tính như là một kho dữ liệu và báo cáo là cách thức trình bày dữ liệu đó như thế nào. 3.Các hàm cơ bản trong Excel 3.1 Hàm thống kê Hàm tính tổng: =Sum(number1, number2,...) Trả về tổng tất cả các tham số trong danh sách các tham số . Number1,number2...là các số cần tính tổng ,chúng có thể là số, địa chỉ ô , phạm vi khối ô. (cho phép đến 30), Hàm làm tròn =Round(number,n) Làm tròn số number theo con số được định bởi n Nếu n=0 làm tròn đến hàng đơn vị hoặc lấy 0 số lẻ Nếu n=1làm tròn lấy 1 số lẻ Nếu n =2 làm tròn lấy 3 số lẻ Nếu n=-1 làm tròn đến hàng chục Nếu n=-2 làm tròn đến hàng trăm Hàm tính trung bình =Average(number1,number2,...). Hàm lấy giá trị lớn nhất =Max(number1,number2,...) Trả về giá trị lớn nhất trong danh sách các tham số Hàm lấy giá trị nhỏ nhất =Min (number1,number2,...) Trả về giá trij nhỏ nhất trong danh sách các tham số Hàm điều kiện =If(logical_test,value_if_true,value_if_false). Logical_test: Điều kiện cho trước bất kỳ(dạng số hay công thức...), điều kiện này trả về true hay =1 hoặc false hay 0 Value_if_true: Đối số này ở dạng bất kỳ, nó được thể hiện nếu được kiểm tra điều kiện ở đối logical_test là đúng(True hay bằng 1). Value_if_false: Đối số này ở dạng bất kỳ, nó được thể hiện nếu được kiểm tra điều kiện ở đối logical_test là sai(False hay bằng 0). Hàm logic And và Or =And(logical1,logical2,..) Hàm trả về giá trị True nếu cả các tham số của nó có giá trị True. Hàm trả về giá trị False nếu có ít nhất một tham số mang giá trị False. =Or(logical1,logical2,..) Hàm trả về True nếu có ít nhất một tham số la True. Hàm trả về False nếu tất cả tham số là False. Hàm xếp hạng. =Rank(number,ref ,oder) Number là số muốn xem thứ hạng + Ref một mảng ,địa chỉ ô haydãy ô có chứa số muốn tìm thứ hạng + Order đối này có giá trị 0,1quy định xếp thứ hạng theo thứ tự từ nhỏ đến lớn Nếu order =1 thì Excel sắp xếp thứ tự từ nhỏ đến lớn Nếu order =0 thì Excel sắp xếp thứ tự từ lớn đến nhỏ Nếu bỏ qua đối số này thì Exclel hiểu order hiểu đối số này băng 0 Hàm đếm số trong dãy. =Count(Value1,Value2,...). Đếm số ô trong dãy ô, đếm các ô có chứa số hoặc ngày tháng còn kiểu dữ liệu khác không đếm. COUTA(value1,value2,...) Value1,value2 có thể là giá trị số ,địa chỉ ô hay dãy ô công thức. Đếm số ô trong dãy ô, đếm các ô có nội dung bất kỳ. 3.2. Nhóm hàm tìm kiếm và tham chiếu. Hàm HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) Lookup_value:giá trị cần tìm kiếm trong bảng thứ nhất của bảng Table_array:là mảng ,tên,dãy hay vung để tìm kiếm thông tin Row_index_num:số hàng được xác định trog bảng Range_lookup:là giá trị logic (true=1,false=0) Quyết định sẽ so tương đối hay chính xác với đối số lookup_value Nếu range_lookup=1(true) so tương đối Nếu range_lookup=0 (false) so chính xác Nếu bỏ qua đối số nay thì Excel hiểu là range_lookup =1 Hàm Index Chứa chức năng mảng =Index(array,row_num,column_num) Array là mảng được xác định trước Row_num:số hàng trong mảng Column_num:số cột trong mảng Chứa chức năng tham chiếu =Index(reference,row_num,column_num,area_num) Reference:là một dãy hay nhiều dãy gọi là khu vực nội dung có thể là Row_num: số hàng trong bảng Column_num :số cột trong bảng Area_num:đối số này dùng để nhận diện từng khu vực. Hàm VLOOKUP =Vlookup(lookup_value,table_array,col_inder_num,range_lookup) Lookup_value:giá trị cần tìm trong cột thứ nhất của bảng giới hạn Table_array:bảng giới hạn để dò Col_inder_num:số thứ tự cột trong bảng giới hạn để trả về kết quả Range_lookup:là giá trị logic(true=1,false=0)quyết định chính xác hay tương đối trong bảng giới hạn Nếu range_lookup=1(true) so tương đối Nếu range_lookup=0(false)so chính xác Nếu bỏ qua đối số này thì Excel hiểu range _lookup=1 Các hàm ngày =Date(year,month,day) Trả về số thứ tự của ngày theo năm tháng và ngày chỉ định =Day(serial_number) Trả về giá trị ngày(từ 1 đến 31)trong tháng tương ứng Serial có thể là số hay chuỗi =Month(serial_number) Trả về giá trị tháng từ 1 đến 12 trong năm =Year(serial_number) Trả về số năm tương ứng 3.3. Hàm tính toán và xử lý chuỗi Hàm lấy phần nguyên =INT(number) Trả về phần nguyên của số number Hàm lấy phần dư =Mod(number,divior) Trả về phần dư của phép chia nguyên (numberchia cho divior) Hàm lấy ký tự bên trái của chuỗi =Left(text,number_chars) Text: Là chuỗi ký tự văn bản. Number_chars: là tổng số ký tự cần lấy Hàm lấy ký tự bên phải của chuỗi =Right(text,number_chars) Text: Là chuỗi ký tự văn bản. Number_chars: là tổng số ký tự cần lấy Hàm lấy ký tự bên trong của chuỗi =Mid(text,Start_num,Number_chars) Text: Chuỗi ký tự văn bản. Start_num: Là vị trí bắt đầu lấy ký tự. Number_chars:Tổng số ký tự cần lấy. =Len(text) Trả về chiều dài của chuỗi text tức là số ký tự có trong chuỗi Hàm đổi chuỗi thành giá trị số =Value(text) Chuyển chuỗi thành giá trị số 3.4.Nhóm hàm cơ sở dữ liệu. Hàm Daverage. Tính trung bình cộng của những mẫu 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ại xuất hiện Trong hộp thoại này cho dạng đồ thị ban đầu và gồm hai lớp. Lớp data range dùng để chọn vùng chứa dữ liệu cho biểu đồ xác định cách bố trí theo hàng hay theo cột , lớp serier : chọn tên cho biểu đồ, chuỗi số làm biểu đồ các nhẵn cho mục x Khi nháy vào khung cửa sổ nhỏ bảng tính hiện lên để ta chọn vùng thích hợp nháy biểu tượng Excell trong vùng cửa sổ nhỏ để về hộp thoại . đây là kết quả ta thực hiện được Chú ý : Có rất nhiều loại biểu đồ chứ đơn không chỉ đơn thuần chỉ có cột và đường ví dụ Bảng giá trị sản phẩm sản xuất trong năm của xí nghiệp sản xuất xe máy Hoà Bình (tỷ đồng) quý 1 quý 2 quý 3 quý 4 tổ 1 60 50 58 65 tổ 2 30 40 70 52 tổ 3 25 37 50 45 Yêu cầu Lập biểu đồ biểu diễn giá trị sản phẩm của từng tổ dạng hình tròn trong không gian 3 chiều. đây là kết quả Còn đối với tổ 2, 3 thì chúng ta làm tương tự 3. Hiệu chỉnh biểu đồ Để thưc hiện được hiệu chỉnh biểu đồ trước tiên ta cần chọn biểu đồ bằng cách nháy chuột vào biểu đồ muốn hiệu chỉnh khung viền biểu đồ sẽ hiện 8 nút ở 4 góc và điểm giữa các cạnh sau đó ta tiến hành các thao tác Di chuyển biểu đồ Thay đổi kích thước Nháy nút phải chuột lên biểu đồ xuất hiện menu dọc nhỏ chọn chart Options hộp thoại xuất hiện. Hộp thoại này có nhiều lớp để thay đổi tiêu đề đồ thị, thuyết minh các cột các đường kẻ ngang dọc, chú thích các nhãn ... Hiệu chỉnh các thành phần bên trong biểu đồ Xoá một thành phần : Nhấychuột vào thành phần ,chọn delete Thay đổi kích thước của một thành phần Định dạng lại một thành phần ; Nháy đúp vào thành phần xuất hiện một hộp thoại gồm nhiều lớp tiến hành lựa chọn theo hứng dẫn trên hợp thoại ,ta có thể chọn mẫu tô hoặc màu tô cho thành phần . 4.Các tiêu đề đặc biệt của biểu đồ Hộp thoại mục title cho phép ta ấn định một tiêu đề chỉ đồ thị và miêu tả văn bản cho mỗi trục ; Mục Char Title dùng để đặt tiêu đề cho biểu đồ Mục Category(X) axi dùng để đặt tên cho trục toạ độ X Mục Category(Y) axi dùng để đặt tên cho trục toạ độY 5. Hiển thị hoặc ẩn các trục toạ độ Thỉnh thoảng bạn có thể nhận thấy biểu đồ trông bớt rắc rối hơn nếu bạn bỏ bớt đi phần biểu diễn trục toạ độ cùng với các thang chia. Để ẩn đi trục toạ độ chỉ cần xoá bỏ hộp kiểm cho trục X,Y,Z tương ứng. 6.Chia tỷ lệ đối với loại dữ liệu kiểu thời gian Nếu dữ liệu trên trục toạ độ cùng thuộc loại dạng ngày Excel chấp nhận giá trị thực tế và tự độngáp dụng chia tỷ lệ thời gian trên trục toạ độ. Tỷ lệ thời gian không giống nhau , thông thường người ta chia theo các cách sau: Excel đặt tất cả dữ liệu tại thời điểm phù hợp với chúng theo thứ tự thời gian. Ví dụ như mùng 1/1 và 3/1 ,9/1 thì thời điểm 1/1 và 3/1 gần nhau hơn là 3/1 va 9/1 vì khoảng cách của chúng khác nhau do vậy có sự chênh lệch. Excel tự động vẽ các chuôi dữ liệu thời gian theo thứ tự tăng dần ngày ca khi dữ liệu của bạn không theo thứ tự thời gian. Excel sử dụng thời gian nhỏ nhất chênh lệch giữa thpời gian của bạn với đơn vị cơ sở nhưng bạn có thể chuyển đổi thời gian cơ sở lớn nhất khác gần với dữ liệu của bạn. Ví dụ bạn muốn vẽ biểu đồ giá cổ phiếu hàng ngày Excel dùng cơ sở là một ngày ,nhưng bạn có thể chuyển thành tuần hoặc tháng.. 7. Hiển thị đường kẻ lưới. Các đường kẻ lưới bắt nguồn từ các thang chia nhỏ trên trục. Thang chia gồm các mốc chính và các mốc phụ. Excel tự động xác định các thang chia nhỏ này nhưng bạn cũng có thể thay đổi mức chia cũng như màu và kiểu dáng của đường kẻ lưới sau khi đã vẽ xong đồ thị . 8. Hiển thị hoặc ẩn hộp chú giải Thông thường Excel hiển thị hộp chú giải ngay bên phải của đồ thị. Bạn có thể đặt sang vùng khác bằng cắch chọn Placement và cũng có thể giấđi bằng cách bỏ dấu kiểm Show Legend 9.Hiển thị nhãn dữ liệu Đối với các biểu đồ dạng quạt, vành khăn hay dạng cột nơi có ít các mốc dữ liệu chúng ta nên cho hiển thị các giá trị chodữ liệu của mỗi phần biểu diễn trên biểu đồ. 10.Hiển thị bảng dữ liệu Nếu dữ liệu của bạn không quá lớn, chúng ta có thể chèn ngay vào cùng biểu đồ để tiện cho việc đối chiếc chi tiết. Đánh dấu kiểm vào mục Show data table để hiển thị nội dung bảng dữ liệu đã sinh ra biểu đồ. 11. Quyết định nơi đặt biểu đồ. Biểu đồ sinh ra có thể được đặt trên cung trang với bảng dữ liệu hoặc đặt trên một trang tính mới. Nếu trang tính dữ liệu chứa nhiều phần tính toán chung gian hoặc không dùng tới khi in báo cáo thì bạn nên đặt biểu đồ ở một trang mới. Sau khi đã tạo biểu đồ, bạn có thể nhấn chuột phải vào các thành phần trong biểu đồ để thay đổi các lựa chọn đã xác lập. II. Đưa đối tượng hình ảnh vào bảng tính Thao tác hoàn toàn như trong word ta có thể dùng thanh công cụ Drawing để đưa một đối tượng Autoshape, text box, word Art,vào trong một bảng tính hay cũng có thể ta vào insert/picture/from fìe để chèn một bức ảnh vào một bảng tính. CHƯƠNG IV CHIA SẺ VỚI CÁC CHƯƠNG TRÌNH KHÁC . I. Hòa trộn Excel với word Tạo một bảng tính mới từ word Bạn đang soạn thảo word đặt con chuột tại vị trí muốn chèn bảng tinh của Excell Nhấp chuột vào biểu tượng insert Microsoft Excell Worrksheet trên thanh công cụ thấy xuất hiện dãy các ô tiếp tục rê chuột xuống dưới hay sang ngang, chọn số hàng ,cột rồi thả chuột lúc này trên màn hình word xuất hiện thêm bảng tính Excellngay tai vị tri con chuột và thanh công cụ hay menu bar đều là của Excell Soạn thảo bảng tính này được tính bình thường nhuuư chúng ta làm trong Excell vậy. Soạn thảo xong nháy chuột đến một chỗ bất kỳ nào ngoài bảng tính bạn trở về word như bình thường Chèn một bảng tính Exell có sẵn vào Word Bạn đang soạn thảo trong word nhấp chuột để con nhấy nơi muốn chèn bảng tính Từ menu bar vào Insert/Object ... hộp thoại Object hiện lên trên màn hình . chọn nhãn Creat From File từ hộp thoại này . *giải thích thành phần trong hộp thoại Khung file name :nhập tên đường đẫn và tệp tin Excell cần đưa vào word nếu không nhớ cụ thể ấn Browse Hộp kiểm Link to file:nếu chọn hộp này có nghĩa là liên kết bảng tính Hộp kiểm Display as Icon: nếu chọn hộp này thì word sẽ lấy ra không thể hiện một bảng tính thực sự mà thể hiện dưới một biểu tượng Excell, biểu tượng này có đường dẫn và tên bảng tính đó . Nhấp đôi biểu tượng để xem nội dung của nó. Chọn xong nhấn OK để thực hiện 3.Liên kết một phần của bảng tính Excell có sẵn vào word Trong word nhấp chuột để đặt con nháy nơi bạn muốn chèn bảng tính Mở chương trình Excel mở bảng tính cần chèn những ô vào trong word,đánh dấu các ô muốn chèn Vẫn đứng trong chương trình Excel hãy thực hiện lệnh copy với nhưng ô đã được chọn Chuyển sang chương trình word từ menu bar chọn Edit/paste Special.. hộp thoại hiển thị nhấp chọn microsoft excel worksheet Object từ khung As Paste:chọn chức năng này nghĩa là ta nhúng các ô vào word Paste link: chọn chức năng này nghĩa là liên kết các ô trong bảng tính vào word, khi ta thay đổi dữ liệu gốc thì các ô trong Word chung bi thay đổi Display as icon : nếu chọn chức năng này word thể hiện ổ một biểu tượng nhấy đôi vào nó sẽ hiển thị nội dung Nhấn Ok 4.Hoà trộn một tài liệu word với Excel Làm tương tự như các mục trên Nếu bạn muốn liên kết tài liệu bạn nên sử dụng lệnh copy trong word và sử dụng lệnh dán paste Special trong menu Edit của Excelvaf dùng các kiểu dán như lần trước. Bạn có thể soạn thảo trực tiếp một tài liệu word trong Excel bằng cách chèn một trang mới của word vào Excelsuwr dùng lệnh Object trong menu Insert và lúc này bạn có thể soạn thảo bình thường như trong word. II. Hoà trộn Excel với access 1 Chèn bảng tính Excel vào access Mở cơ sở dữ liệu trong access Từ menu bar vào file/ get External/data/import Nhấn vào import Spreadsheet wizard xuất hiện bạn chọn bảng tính cần chèn nhấn vào nút next Hộp thoai hiện lên nếu dòng đầu tiên trong bảng tính có tiêu đè (tên các trường )khi nhấp chọn khi nhấp chọn trong first row contains và nó không chứa dữ liệu sau đó nhấn next Hộp thoại xuất hiện co các thành phần chức năng in a new table :nếu trong access chưa có dữ liệu thì chọn chức năng này nó sẽ cho bạn hoà trộn theo một bảng mới còn trong bảng có dữ liệu chọn in a Existing table Tiếp tục nhấn next và nhấp chuột vào khung chứa có tô màu đen để chọn trường khung file name cho phép bạn đổi tên trường khung indexed có các tuỳ chọn chỉ mục cho các trường . No không có chỉ mục. Yes chỉ mục cho phép nhiều hơn một ghi trong cùng giá trị của trường đó nhấn next để tiếp tục Sau khi nhấn next hộp thoại xuất hiện chức năng Let Access Add Primary:nếu không có các trường sẵn chứa dữ liệu đòng nhất cho mỗi bản ghi nhưng bạn lại muón sử dụng một trường cơ bản ,chức năng Choose My own Prymary key :nếu trường có sẵn thích hợp cho việc sử dụng như một phím căn bản sau đó chọn trường từ danh sách thả xuống ,chức năng No Primary Key nếu bảng không cần thiết có phím căn bản sau đo nhấn next Hộp thoại hiện ra ,khung Import To table:trong hộp này cho phép bạn nhập tên mới cho bản ,nhấn Finish hộp thoại thông báo xác nhận xuất hiện ,nhấn OKđể thực hiện . III.Chia sẻ các sổ tính trên mạng Excel cho phép hai hay nhiều người cùng làm việc đồng thời trong cùng một sổ tính bằng lệnh Share Workbook trên menu tools. Khi bạn kiểm tra hộp Allow Change by More then One User At The Same Time và bấm Ok thì hộp thoại Save As xuất hiện báo cho bạn biết sổ tính phải được lưu theo một trật tự hoàn chỉnh mới. Điều này cần thiết vì sổ tính được lưu như một tệp tin được dùng chung trước khi một người sử dụng khác mở nó. Sổ tính chỉ lưu một lần, trên thanh tiêu đề sẽ xuất hiện từ ‘Share’ khi sổ tính được mở cho tới khi bạn tắt tuỳ chọn Allow Change by More than One User At the Same time. Danh sách những ngưồi đang dùng chung sổ tính được liệt kê trong list boxcủa hộp thoại Share Workbook. Dĩ nhiên có những rủi ro vốn có khi có nhiều người cùng làm việc với một sổ tính trong cùng một thời điểm. Những xung đột không thể tránh sẽ xảy ra khi nhiều người cùng thực hiện thay đổi đối với những ô có cùng địa chỉ. Khi có một ai đó lưu lại thay đổi, Excel không chỉ lưu lại sổ tính mà còn cập nhật bất kỳ một thay đổi nào được tạo ra bởi các người sử dụng khác. Do đó một hộp thoại cho bạn biết các thay đổi đã được thực hiện bởi những người khác sẽ được nhấn mạnh bởi những đường viền màu và có một hộp đề nghị đặc biệt sẽ giải thích ai được thực hiện. Khi một tập tin dùng chung được lưu trữ, Excel kiểm tra ngay và thông báo cho bạn những gì thay đổi mà người khác đã thực hiện. Nếu như người khác thay đổi dữ liệu của một ô nào đó mà bạn cũng đã thay đổi thì excel sẽ đưa ra một sự dàn xếp nhằm giải quyết vấn đề này hộp thoại resolve Conflicts. Đối với từng xung đột , hộp thoại chỉ rõ ô liên quan và cho phép bạn quyết định nên giữ lại sự thay đổi. Bạn có thể giải quyết từng xung đột một hoặc dùng nút lệnh nằm phía dưới cùng của hộp thoại để chọn tất cả sự thay đổi mà người khác thực hiện. CHƯƠNGV ỨNG DỤNG THỰC TẾ CỦA EXCEL Trong thực tế tin học văn phòng được sử dụng rộng rãi và nhất là Excel được phổ biến đến mọi ngành mọi lĩch vực nhưng áp dụng nhiều nhất là các ngành quản lý. Sau đây tôi giới thiệu đến các bạn một số bài toán áp dụng trong thực tế như văn phòng :thanh toán tiền lương cho công nhân áp dụng rất phổ biến, ở trong trường học... Trong trường học. KẾT QUẢ THI CUỐI KHOÁ 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 TRUNG BÌNH 6.31 6.15 6.6538 6.363 6.5742 CAO NHẤT 10 9 9 9.143 9.1429 THẤP NHẤT 3 4 4 4.143 4.8929 Yêu cầu thực hiện: 1. Nhập Stt tự động 2. Thêm vào bên phải cột Đk quả hai cột Xếp loại và Xếp hạng 3.Điểm đợc thêm (Đ Thêm ) đợc tính nh sau: -Học viên từ năm 72 về sau có điểm thêm là 0.75 - Học viên trớc năm 72 nhng là phái nữ có điểm thêm là 0.5 -Trờng hợp còn lại điểm thêm là 0 4. Điểm thấp nhất (ĐTN) là điểm thấp nhất trong 3 điểm của từng học viên. 5. Điểm trung bình(ĐTB):Với điểm Toán hệ số 3,Văn hệ số 2,NNgữ hệ số 2 và làm tròn lấy 2 số 6.Điểm kết quả (ĐKquả) : Những học viên nào có ĐTB>=5 ĐK quả chính là ĐTB, ngợc lại ĐKquả lả ĐTB cộng với Đthêm , nhng nếu sau khi cộng thêm mà lớn hơn 5 thì chỉ đợc lấy 5 mà thôi 7. Xếp loại dựa vào các tiêu chuẩn sau: - Giỏi : Nếu ĐKquả>=8 và không có môn nào dới 6.5 -Khá: Nếu ĐKquả>=6.5 va không có môn nào dời 5.0 -TB: Nếu ĐKquả >=5.0 và không có môn nào dới 3.5 -Yếu: Nếu ĐKquả>=3.5 và không có môn nào dới 2.0 -Kém: Còn lại 8. Trung bình, Cao nhất, Thấp nhất theo cột cho từng môn, ĐTB, ĐKquả 9. Xếp hạng theo điểm kết quả Các hàm sử dụng: If,and, Rank, max, min, average... Trong quản lý xuất nhập khẩu. A 5% JPY 132 B 10% USD 16 C 20% RUP 200 D 50% HKD 20 BẢNG TÍNH THUẾ XUẤT KHẨU STT TÊN HÀNG SỐ LỢNG ĐƠN GIÁ MNT NGÀY QUY RA USD TTVN THUẾ thành tiền A001 A 10 30 hkd 02/12/2005 53.57 300 15 300 B001 B 15 100 jpy 04/12/2005 11.36 1500 150 1500 B002 B 4 10000 jpy 24/12/2005 303 40000 4000 40000 A002 A 120 20000 rup 04/12/2005 18182 2400000 120000 2400000 C001 C 10 10000 ff 19/12/2005 17857 100000 20000 100000 D001 D 100 3000 jpy 21/12/2005 2273 300000 150000 300000 C002 C 15 4000 hkd 22/12/2005 10714 60000 12000 60000 D002 D 4 30000 jpy 22/12/2005 909.1 120000 60000 120000 A003 A 2 30000 usd 10/11/2005 3750 60000 3000 60000 D003 D 5 10000 jpy 10/10/2005 378.8 50000 25000 50000 Yêu cầu thực hiện tính: 1. Quy định ngày tháng năm của hệ thống theo dạng:dd/mm/yyyy và chọn khổ giấy nằm ngang. 2. Chèn thêm 2 cột thành tiền, Quy ra USD trớc cột TTVN 3. Tính thành tiền=Số lợng*đơn giá 4. Tính quy USD=thành tiền/tỷ ra - Tỷ giá đợc tính dựa của MNT (mã ngoại tệ) và Bảng tỷ giá so với đồng USD 5. Tính TTVN (thành tiền việt nam)=Quy ra USD *Tỷ giá - tỷ giá đợc tính dựa vào ngày nhập + Nếu tỷ giá nhập trớc ngày 10/10/2005 thì tỷ giá là 15630 + Từ ngày 10/10/2005 về sau thì tỷ giá là 15900 6. Tính thuê=Tỷ lệ thoại loại hàng*thành tiền việt nam - Tỷ lệ dựa vào ký tự đầu của mã số và Bảng tỷ lệ ở trên 7. Sử dụng chức năng Adcanced Filter để lọc ra những mẩu tin sau: a. Những mặt hàng có mã số là A001 hoặc A003 b. Những mặt hàng có mã MNT là jpy ( Tạo ra bảng điều kiện liên quan đến thông tin cần lọc) 8. Trình bày hoàn chỉnh bảng tính trên. Thiết lập tiêu đề đầu trang và cuối trang Trong quản lý tiền lương cho công nhân. BẢNG PHÒNG BAN VÀ BẬC LƯƠNG Mã loại Tên phòng phụ cấp thâm niên A nhân sự 200 180 150 250 B hành chính C kế toán D dự án BẢNG THANH TOÁN TIỀN LƠNG THÁNG CỦA CÔNG TY ABC Mã CB Họ tên Loại CB Phòng ban Bậc lương Lương cb Ngày công Phụ cấp Thực lĩnh A115 Trần Anh Tuấn A nhân sự 1 310 24 200 7640 B226 Lê Anh Thơ B hành chính 2 290 26 180 15260 C113 Nguyễn Đông C kế toán 1 310 24 150 7590 A239 Lê Hoàng Huy A nhân sự 3 390 23 200 27110 B137 Nguyễn Thị Hạnh B hành chính 3 350 22 180 23280 D133 Nguyễn Văn Đức D dự án 3 290 25 250 22000 C215 Hoàng Thị Đan C kế toán 1 310 24 150 7590 D226 Trang Công Đức D dự án 2 290 26 250 15330 A223 Nguyễn Thy Xuân A nhân sự 2 290 23 200 13540 B121 Lê Kiều Nga B hành chính 2 290 26 180 15260 Yêu cầu thực hiện: 1. Nhập và cân chỉnh, thiết kế vừa trang in 2. Điền dự liệu cho cột phòng ban dựa vào Mã CB và bảng phụ 3. Điền loại CB dựa vào ký tự thứ hai của Mã CB và chuyển sang trị số 4. Điền bậc lương dựa vào ký tự thứ 3 của Mã CB và chuyển sang dang số 5. Cột phụ cấp đợc tính dựa vào ký tự đầu của mã CB đi tìm trong Bảng phụ và ký tự cuối của Mã CB So sánh với các cột tương ứng trong bảng phụ. Ký tự cuối phải chuyển sang dạng trị số 6. Thực lĩnh=Bậc lương*lương CB*ngày công+phụ cấp 7. Đếm xem có bao nhiêu người ở phòng hành chính, phòng nhân sự, Phòng dự án Bảng thống kê hành chính 3 nhân sự 3 dự án 2 Các hàm đã sử dụng Vlookup,left,count... 4.Trong quản lý vận tải BẢNG ĐƠN GIÁ VÀ THỜI GIAN QUI ĐỊNH BẢNG QUY ĐINH TRONG TẢI Nơi đến Đơn giá Thời gian SỐ XE TRỌNG TẢI Hà nội 10000 5 50 4 Đà nẵng 5000 7 51 8 Quy nhơn 4000 2 52 12 Playku 6000 4 Lào 25000 8 Campuchia 20000 6 Nha Trang 3000 1 BẢNG TÍNH CƯỚC PHÍ VẬN TẢI Số xe Số lượng Nơi đến Đơn giá Cước phí Ngày đi Ngày đến T. gian th Thởng 50-2980 5 Playku 6000 30000 01/01/04 05/03/04 64 0 52-8456 10 Quy Nhơn 4000 40000 03/05/04 04/05/04 1 0.05 50-2641 2 Nha Trang 5000 10000 03/05/04 05/05/04 2 0 50-6341 3 Hà Nội 10000 30000 05/01/04 05/07/04 182 0 51-1111 6 Lào 5000 30000 06/05/04 06/05/04 0 0.05 52-1102 5 Lào 5000 25000 10/05/04 16/05/04 6 0 52-1234 10 Hà Nội 10000 100000 20/05/04 26/05/04 6 0 51-1111 5 Campuchia 20000 100000 21/05/04 27/05/04 6 0 50-4455 3 Đà Nẵng 5000 15000 25/05/04 31/05/04 6 0 52-2929 2 Nha Trang 5000 10000 22/05/04 26/05/04 4 0 51-1111 4 Hà Nội 10000 40000 05/10/04 05/12/04 61 0.05 YÊU CẦU THỰC HIỆN: 1.Quy định ngày tháng của hệ thống theo dạng dd/mm/yy 2.Thêm cột thởng vào bên phải cột thời gian thực hiện (T gian Th) 3.Tính đơn giá :từ bảng đơn giá và thời gian quy định . 4.Tính cước phí :nếu số lợng(SL) chở ít hơn trọng tải xe (cho trong bảng trọng tải ) cớc phí:=đơn giá *SL(tính nguyên giá ), ngược lại Cước phí =Đơn giá *SL*105% 5.Tính thời gian thực hiện (T gian Th)=Ngày đến-ngày đi, nhưng trường hợp đến và đi trong một ngày thì thời gian thực hiện tính là 1 ngày 6.Tính thưởng :Nếu thời gian thực hiện ít hơn thời gian quy định thì thưởng là 5% cước phí, còn lại là 0 7.Vẽ đồ thị so sánh giữa cước phí và đơn giá cho hai chuyến xe :52-2929 và 50-4455 Hàm đã sử dụng là Vlookup,và sử dụng các một số hàm khác.