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

mục lục Phần I: Giới thiệu về Windows 2000. Giới thiệu. khởi động và thốt khỏi Windows. 2 Khởi động. Thốt khỏi Windows. Cài đặt chương trình ứng dụng. 4 Gỡ bỏ chương trình ứng dụng. 6 Phần II. Làm việc với Excel. Chương I. Xây dựng bảng tính và các hàm trong Excel. Giới thiệu chung. 6 Các bước xây dựng bảng tính. 8 Các hàm cơ bản trong Excel. Hàm thống kê. 9 Hàm tham chiếu và tìm kiếm. 11 Hàm tính tốn và xử lý chuỗi. 12 Nhĩm hàm cơ sở dữ liệu. 13 Chương II. Xử lý dữ liệu và kiểm tra lỗi chính tả. I. Thao tác cơ bản khi làm việc với Excel Thao tác làm với Sheet. 18 Thao tác làm với dịng ,cột. 19 Thao tác làm với cơ sở dữ liệu. 19 II. Thiết lập Form đơn giản để nhập/tìm kiếm dữ liệu, Sắp xếp và lọc dữ liệu Thiết lập Form đơn giản để nhập/tìm kiếm dữ liệu. 20 Sắp xếp dữ liệu. 21 Lọc dữ liệu Lọc tự động. 22 Lọc bằng lệnh Advanced Filter. 23 III. Tính năng bảo vệ trong Excel 1. Bảo vệ sổ tính. 23 2. Bảo vệ trang tính 23 3. Bảo vệ vùng dữ liệu trong trang 24 4. Gỡ bỏ chế độ bảo vệ 24 IV. Kiểm tra lỗi chính tả 24 V. Sử dụng tính năng Autocorrect Options 24 VI. Chuẩn bị trang in 25 Chương III. Biểu đồ và chèn hình ảnh trong Excel I. Làm việc với biểu đồ 1. Khái niệm 25 2. Các bước tạo biểu đồ 26 3. Hiệu chỉnh biểu đồ 27 4. Các tiêu đề đặc biệt của biểu đồ 28 5. Hiển thị hoặc ẩn các trục toạ độ 28 6. Chia tỷ lệ đối với loại dữ liệu kiểu thời gian 28 7. Hiển thị đường kẻ lưới 28 8. Hiển thị hoặc ẩn hộp chú giải 28 9. Hiển thị nhãn dữ liệu 28 10. Hiển thị bảng dữ liệu 28 11. Quyết định nơi đặt biểu đồ 29 Chương IV Chia sẻ các chương trình khác I.Hồ trộn Excel với Word 1.Tạo một bảng tính mới từ Word 29 2.Chèn một bảng tính Excel cĩ sẵn vào Word 3Liên kết 1 phần của bảng tính Excel cĩ sẵn trong Word 30 4.Hồ trộn tài liệu Word với Excel 30 II. Hồ trộn excel với Access 1. Chèn 1 bảng tính Excel vào Access III. Chia sẻ các sổ tính trên mạng 31 Chương V. ứng dụng thực tế của Excel 1.Trong trường học 32 2.Trong quản lý xuất nhập khẩu 33 3.Trong quản lý tiền lương 34 4. Trong quản lý vận tải 36 LỜI MỞ ĐẦU. Chúng ta đang ở trong thời kỳ cơng nghệ thơng tin phát triển mạnh mẽ, khơng cịn ai cĩ thể nghi ngờ vai trị của cơng nghệ thơng tin trong đời sống trong khoa học, kỹ thuật kinh doanh cũng như trong mọi mặt vận động của xã hội, Dưới mọi quy mơ từ xí nghiệp, cơng ty cho đến Quốc Gia và cả Quốc tế. Việc nắm bắt thơng tin thật nhanh nhiều và chính xác kịp thời ngày càng đĩng vai trị quan trọng trong quản lý điều hành. Hay nĩi cách khác xử lý thực chất và xử lý thơng tin , xong mọi thơng tin cần được xử lý trên máy(theo bất cứ quy trình cụ thể nào) cũng đều được thể hiện bằng các dữ liệu mà cụ thể hơn là các số liệu đối với MS Excel ghi trên dạng tải nào đĩ. ở nước ta trong những năm gần đây việc xử lý số liệu khơng cịn xa lạ với người làm tin học. Đặc biệt với người làm văn phịng hay kinh doanh buơn bán. Các ứng dụng vào trong quản lý ngày càng được phổ biến hơn và địi hỏi quản lý thơng tin khá hơn, phức tạp với nhiều luồng thơng tin khác nhau. Chính vì vậy em đã chọn đề tài: Những tính năng cơ bản – nâng cao của Excel cho báo cáo tốt nghiệp. Trong báo cáo này gồm cĩ 2 phần chính Phần I: Em đã giới thiệu sơ qua về Windows và cài đặt chương trình ứng dụng trên Windows Phần II: Làm việc với Excel. Trong phần II này em đã nêu một số tính năng cơ bản và nâng cao trong khi làm việc với Excel, ngồi ra em cịn nêu ra một số bài tốn Excel ứng dụng vào thực tế quản lý. Qua thời gian học tập và rèn luyện em đã biết thêm được một số kiến thức sau này em mong muốn ứng dụng nĩ vào thực tế cơng việc. Tuy nhiên vì vẫn cịn non yếu về kiến thức kinh nghiệm về thực tế cũng như hạn chế về tài liệu và thời gian thực hiện nên báo cáo khơng tránh khỏi thiếu sĩt. Vì vậy em mong thầy cơ giáo đĩng gĩp ý kiến xây dựng báo cáo của em ngày càng hồn thiện hơn. Em xin chân thành cảm ơn thầy Phạm Văn Sơn, và các thầy cơ trong khoa cơng nghệ thơng tin đã giúp đỡ em hồn thành bài báo cáo này. Hà nội, ngày 7 tháng 8 năm 2006 sinh viên: Lê thị thu Hà. PHẦN I: GIỚI THIỆU VỀ WINDWS 2000 1. GIỚI THIỆU Windows 2000 cĩ 2 bộ :bộ Microft windows 2000 sever là bộ hệ diều hành mạng dành cho máy chủ, bộ Microsoft windows 2000 Professional dành cho máy trạm và máy tính xách tay. windows 2000 cĩ độ tin cậy cao, tránh lỗi cho người sử dụng,dễ học,dẽ sử dụng nhất, hỗ trợ rộng rãi các dạng chuẩn internet, cĩ nhiều phơng chữ đẹp dùng mã Unicode :times new roman, Arial,Tahoma…, với các phơng chữ này cĩ thể dùng tiếng việt đồng thời với nhiều ngơn ngữ khác trên thế giới Yêu cầu phần cứng chạy windows 2000 :CPU 133 Mhz trở lên , bộ nhớ Ram tối thiểu là 64MB , đĩa cứng cồn trống ít nhất là 650MB ,màn hình VGA trở lên, ổ đĩa CD-ROM hay DVD bàn phím và chuột . 2.khởi động và thốt windows 2000 2.1 Khởi động với máy tính đã cài windows2000 đầu tiên chắc chắn rằng khơng cĩ đĩa trong ổ A ,bật máy tính .Trong chốc lát windows 2000 sẽ nắm chắc quyền điều khiển hệ thống và chuyển sang giao dịên đồ hoạ. Nếu máy tính nối mạng khi khởi động sẽ xuất hiện Welcome To Windows yêu cầu ta nhập vào tên người dùng và mật khẩu truy nhập mạng Màn hình giao diện gồm cĩ màn hình nền desktop, trên màn hình cĩ các biểu tưởng. 2.2 Thốt khỏi WINDOWS 2000 Để thốt khỏi WINDOWS 2000 ta chi cần nhấn vào Start sau đĩ chọn turn off compuer, một cửa sổ hiện ra ta chọn turn off để tắt máy. 3. Cài đặt các chương trình ứng dụng Tơi giới thiệu với các bạn đây là một chương trình ứng dụng nhạc JetAudio 6.0. Bước đầu tiên ta nhấn đúp chuột vào file setup của chương trình sau đĩ một cửa s hiện ra ta chọn next, một cửa sổ khác hiện ra ta chọn vào lựa chọn một và tiếp tục next như hình minh hoạ Ta chọn next Tiếp tục next Next next tiếp Sau đĩ chờ đời giây lát để chương trình cài đặt tiếp đến là finish để hồn thành cơng việc. 4. Gỡ bỏ các chương trình ứng dụng Bước đầu ta bấm vào start chọn control panel\Add or Remove program chọn chương trình cần gỡ bỏ chọn Remove sau đĩ làm theo chỉ dẫn của chương trình và next cuối cùng là chọn finish để hồn tất việc gỡ bỏ một chương trình. Ví dụ như ta gỡ bỏ chương trình JetAudio chọn vào chương trình nhấn vào Change\Remove nĩ hiện ra một cửa sổ hỏi ta cĩ muốn gỡ bỏ chương trình ứmg dụng này khơng. Nếu gỡ bỏ ta chọn Yes cịn khơng ta chọn No. Sau đĩ chương trình sé hỏi ta cĩ muốn gỡ bỏ dữ liệu của chương trình hay khơng ta chọn Yes để gỡ bỏ hết . Sau đĩ cửa sổ finish se hiện ra và ta chọn finish để hồn tất cơng việc gỡ bỏ PHẦN II LÀM VIỆC VỚI BẢNG TÍNH EXCEL CHƯƠNGI XÂY DỰNG BẢNG TÍNH VÀ CÁC HÀM CƠ BẢN TRONG EXCEL 1.Giới thiệu chung. Làm cơng tác văn phịng hẳn bạn cĩ nhu cầu xử lý số liệu để làm báo cáo theo yêu cầu nghiệp vụ của mình. Dữ liệu cĩ thể do bạn trực tiếp gõ vào để quản lý nhưng cũng cĩ thể một chương trình máy tính nào đĩ sinh ra một đống dữ liệu và bạn phải xử lý chúng thành một bảng biểu với những số liệu phân tích. Mục tiêu của khố học là giúp bạn bắt đầu hệ thống hố lại các thao tác cơ bản ,sau đĩ cung cấp một số kỹ năng cao cấp để bạn cĩ thể xử lý với số liệu lớn ,từ nhiều nguồn khác nhau và đích cuối cùng là in được một bản báo cáo chuyên nghiệp với cấu trúc định dạng thay đổi tuỳ ý chỉ qua vài nhấp chuột . Chúng ta sẽ cùng nhau giải quyết vấn đề khĩ khăn này thơng qua các tình huống nghiệp vụ cụ thể .Trong mỗi chương, mỗi bài tốn tình huống được giới thiệu lồng ghép với các thao tác kỹ thuật với Excel cho tới khi bài tốn được giải quyết . cách tiếp cận qua các ví dụ như vậy cho phép chúng ta nhanh chĩng nắm bắt được các tính năng của Excel và hồn cảnh áp dụng chúng . Nhưng sẽ cĩ vẻ hơi vội néu nghĩ rằng như vậy là đủ cho hành trang kỹ năng văn phịng của bạn .kỹ năng chỉ được hình thành một khi áp dụng thương xuyên các những điều biết được trong những tình huống biến đổi đa dạng . Chúng ta sẽ lần lượt xem xét và giải quyết các tình huống sau : Tình huống đơn giản đầu tiên giúp bạn đánh bĩng lại nhữnh kỹ năng cơ sở mà bạn đã biết hoặc đã làm với Excel, xử lý tính tốn đơn giản ,sinh biểu đồ ,định dạng trang in và in ra thành báo một cách chuyên nghiệp. Nảy sinh một số yêu cầu phân tích dữ liệu .chúng ta hãy thử một số hàm phân tích, thao tác xử lý dữ liệu cao cấp . Dữ liệu trở lên lớn hơn , thậm chí là đầu ra của mơt chương trình máy tính nào đĩ. Dường như bạn đang phải làm chức năng của một chương trình máy tính và xử lý đống dữ liệu đĩ cĩ thể cơ sở dữ liệu .Chưa hết , yêu cầu báo cáo hơm nay thì thế này , mai thế khác. Làm sao để làm báo cáo phức tạp và theo các khuơn dạng yêu cầu khác nhau?. Mạng máy tính làm mọi người ít thấy nhau nhưng lại hay “gặp “ nhau hơn . Trong một mơi trường cơng tác trên mạng, bạn cĩ thể làm việc với những người bạn chưa gặp mặt. Dữ liệu bây giờ cĩ thể được cập nhật và xử lý bởi nhiều người. Sản phẩm của bạn cũng phải được chia sẻ cùng với đồng nghiệp. Làm sao cùng nhau làm việc và sử dụng dữ liệu của nhau trên cùng một bàn tính. Thơng tin lấy từ nhiều nguồn khác nhau, thậm chí qua internet. Thay vì cắt dán một cách thủ cơng với nhiều sai sĩt, bạn cần đảm bảo dữ liệu khi xử lý luơn được cập nhật mới nhất. Thêm nữa dữ liệu sẽ đựơc trao đổi với những ứng dụng khác nhau như thế nào. 2. Các bước cần chuẩn bị xây dựng bảng tính Bước 1: quyết định chọn cơng cụ Excel Bạn hãy trả lời các ccâu hởi dưới đây , nếu các câu trả lời là “cĩ” thì Excel đúng là cơng cụ mà bạn sử dụng . Các thơng tin cĩ cấu trúc dạng bảng khơng? Dữ liệu cĩ ổn định và được xử lý định kỳ khơng ? Cĩ cần thực hiện tính tốn , phân tích trên số liệu khơng? Bước 2 tổ chức cấu trúc bảng chứa thơng tin. Chia tách thơng tin thành các bảng , trang cĩ tính độc lập khơng nên bắt đầu cơng việc bằng cách nhập luơn dữ liệu vào trang tính Excel. Một chút thời gian tổ chức bảng dữ liệu sẽ tiết kiệm vơ khối cơng sức sau này và hơn thế nữa nĩ cịn giúp bạn quản lý dữ liệu nhất quán hơn. nếu cịn nghi ngờ bạn nên hởi một chuyên gia cơ sở dữ liệu trong cơng ty bạn. Việc thiết kế, tổ chức bản dữ liệu chính là nghề tạo cơng ăn việc làm cho anh ta. Những dữ liệu liên quan trực tiếp với nhau và khơng gây ra sự lặp lại dư thừa trong bảng tính thì nên xếp vào với nhau thành một bảng. Mỗi bảng nên đặt trên một trang trong sổ tính. Trong mỗi bảng chọn lưu theo hàng ngày hay cột cho một thuộc tính : theo cách thơng thương thì các cột sẽ là các loại thơng tin thuộc tính cố định và các hàng sẽ chứa dữ liệu được cập nhật và bổ sung. Như vậy bạn cĩ thể chọn cuộn lên, xuống màn hình để trình duyệt qua dữ liệu một cách tự nhiên. Nếu tất cả các thuộc tính của dữ liệu cố định, tức là bảng dữ liệu cĩ kích thước ổn định thì việc sắp xếp theo cột hoặc hàng chỉ là ý thích của bạn. Gộp nhĩm các thơng tin liên quan, tách/ che dấu thơng tin phụ :Do kích thước màn hình và trang giấy máy in là bạn cĩ hạn và người đọc báo cáo cũng khơng phải quan tâm đến tất cả dữ liệu của bạn, vì vậy cần sắp xếp các thuộc tính cột cho hợp lý. Hãy đặt các cột thơng tin cĩ lien quan hệ gần gũi nhau để dễ quản lý .Nếu một cột khơng cần xuất hiện trong báo cáo chính thì chúng ta cĩ thể dấu nĩ đi . Chú ý tới các rằng buộc ‘chia ra’ trong tổng số dữ liệu được nhất quán. Rất thơng thường các báo cáo thường chứa một cột giá trị tổng nĩi chung và được phân tích chi tiết thành các số liệu trong các cột con. Giá trị trong các cột con cĩ thể bị rằng buộc theo các cách sau. Chia ra: Cộng các giá trị trong các cột con bằng giá trị tổng số. Ví dụ tổng số nhân viên chia ra theo nam và nữ . Trong đĩ :Cộng các gía trị trong các cột nhỏ hơn gí trị tổng số. Ví dụ số nhân viên trong đĩ biết tiếng Anh. Trong tổng: Cộng các giá trị trong các cột con cĩ thể lớn hơn hoặc bằng tổng số. Ví dụ trong tổng số nhân viên cĩ x người biết tiêng Pháp và y người biết tiếng Anh. Bước 3: Nhập và lưu trữ dữ liệu: Cĩ thể dùng tính năng chia sẻ để phân chia cho nhiều người cập nhật. Tính 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 tố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 tồ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 tố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 tố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 : Xố 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 tốn tổng hợp hãy xem ví dụ sau BẢNG THỐNG KÊ HỐ ĐƠN HÀNG MÁY IN Stt Mã hàng Tên hàng Chủng loại Ngày bán Sl Hố đơ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à hố đơ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 hố đơ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ê hố đơn hàng máy in stt mã hàng tên hàng chủng loại ngày bán sl hố đơ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 Hố đơ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ả tố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 tồ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 tốn tử AND cịn các điều kiện khác dịng được liên kết bằng tố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 Xố Sheet B1: Chọn một Sheet cần xố 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 xố bớt cột(dịng) B1: Bơi đen số cột hoặc dịng cần xố 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, xố , 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 ơ ngồ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 khố. 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ì khố thứ nhất là dữ liệu cột tên khố 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 đề khố sắp xếp thứ nhất và thứ tự sắp xếp Chọn tiêu đề cho từ khố 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 khố sắp xếp chính và chọn thứ tự sắp xếp cho cả khố 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 khố 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 hồn tồ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, ngồ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 Tố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 tốn. STT Tên HV Phái NS Tố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 Ỹ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 Ỹ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. Ngồ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 tố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 tố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 tốn tử And cịn các điều kiện khác dịng được liên kết bằng tố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 khố 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 khố tồn bộ các ơ trên trang tính bạn cĩ thể mở khố bằng cách : Đánh dấu chọn vùng muốn mở khố 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 tồ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 tồ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 tố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 Hồ 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 đồ Xố 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 xố 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 tố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 hồn tồ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 ngồ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.Hồ 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. Hồ 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 hồ 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ự hồ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 tốn áp dụng trong thực tế như văn phịng :thanh tố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 KHỐ STT Tên HV Phái NS Tố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 Tố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 hồ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ế tốn D dự án BẢNG THANH TỐ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ế tốn 1 310 24 150 7590 A239 Lê Hồ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 Hồng Thị Đan C kế tố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.