›
›

Bài giảng môn Tin học ứng dụng

pdf 32 trang vanle 2450
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng môn Tin học ứng dụng", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfbai_giang_mon_tin_hoc_ung_dung.pdf

Nội dung text: Bài giảng môn Tin học ứng dụng

  1. LỜI NÓI ĐẦU Tập bài giảng được viết nhằm đáp ứng nhu cầu giảng dạy và học tập của giáo viên cũng như sinh viên của trường Cao đẳng Kinh tế - Kỹ thuật Vĩnh Phúc bộ môn Tin học ứng dụng - Đối tượng sử dụng tập bài giảng : Sinh viên cao đẳng ngành kế toán và quản trị kinh doanh - Mục đích yêu cầu đặt ra cho đối tượng sử dụng tập bài giảng: Hiểu rõ được thống kê học và sử dụng thành các hàm trong Excel vào quản lý tài chính, và dự báo kinh tế - Cấu trúc cuốn tập bài giảng gồm có 3 chương. - Sơ lược về các kiến thức chính sẽ trình bày trong tập bài giảng : Chương 1 MỘT SỐ KIẾN THỨC TIN HỌC CĂN BẢN Chương 2 ỨNG DỤNG EXCEL TRONG QUẢN LÝ TÀI CHÍNH Chương 3 ỨNG DỤNG EXCEL TRONG HỒI QUY TƯƠNG QUAN VÀ DỰ BÁO KINH TẾ - Hướng dẫn sử dụng tập bài giảng : Sinh viên chủ động nghiên cứu các kiến thức có trong tập bài giảng 1
  2. Chương 1 MỘT SỐ KIẾN THỨC TIN HỌC CĂN BẢN Mục tiêu: Sau khi học xong chương này, học sinh có khả năg: - Hiểu được những vấn đề cơ bản của tin học ứng dụng: + Căn bản về Windows + Căn bản về Excel - Sử dụng được các công cụ và các hàm đã học trên một bảng tính mẫu 1.1.CĂN BẢN VỀ WNDOWS Windows là hệ điều hành sử dụng giao diện đồ họa và là sản phẩm của tập đoàn Microsoft. Các phiên bản gần đây của windows là hệ điều hành đa nhiệm và đa người sử dụng. Gần 80% các máy tính cá nhân (PC) trên thế giới sử dụng hệ điều hành windows vì tính thân thiện, dễ sử dụng của hệ điều hành này. Windows có chức năng điều khiển phần cứng của máy tính; tạo môi trường cho các ứng dụng khác hoạt động; quản lý việc lưu trữ thông tin trên đĩa; cung cấp khả năng kết nối giữa các máy tính. Cho đến thời điểm hiện tại, tập đoàn Micrrosoft đã cho xuất xưởng nhiều phiên bản windows như windows 95, windows 98, windows ME, windows NT, windows 2000, windows XP, windows 2003, và gần đây nhất là windows Vista. Trong phạm vi giáo trình này, chúng tôi trình bày một số nét căn bản về windows XP. 1.1.1 Thiết lập cách biểu diên ngày giờ, số và tiền tệ Sau khi cài đặt Windows, máy tính sẽ biểu diễn ngày, giờ, số, tiền tệ theo chuẩn của Mỹ, muốn thiết lập lại cách biểu diễn này, khởi động bộ công cụ control panel bằng menu start | settings | Control panel cửa sổ Control Panel xuất hiện. Để thay đổi cách biểu diễn ngày giờ, từ màn hình Control Panel, chọn Date, Time, Language, and Regional Options | Regional and Language Options. Hộp thoại Regional and Language Options xuất hiện, chọn Customize. Thẻ number có các lựa chọn về biểu diễn dữ liệu kiểu số. Trong đó Decimal symbol thể hiện cách biểu diễn dấu thập phân; degital grouping symbol biểu diễn dấu ngăn cách lớp đơn vị, lớp nghìn, lớp triệu ; negative sign symbol thể hiện biểu diễn dấu âm và list seperator thể hiện dấu ngăn cách danh sách. Lựa chọn cách biểu diễn thích hợp và kích chọn OK. Thẻ date thể hiện cách biểu diễn ngày. Như có thể thấy trong hình 1.13, người sử dụng có thể cài đặt cách biểu diễn ngày kiểu Việt nam theo dạng dd-mm-yyyy. Trong đó dd là hai chữ số biểu diễn ngày; mm là hai chữ số biểu diễn tháng; yyyy là bốn chữ số biểu diễn năm. 1.1.2 Chạy chương trình trong Windows Để chạy chương trình trong windows, thực hiện một trong các cách sau: Cách 1: Tìm và chạy chương trình từ shortcut trên desktop. Nếu chương trình cài đặt có tạo một shortcut trên nền desktop thì chạy chương trình bằng cách kích kép chuột vào shortcut trên desktop. 2
  3. Hình 1-1 Công cụ Control Panel Hình 1-2 Hộp thoại Customize Regional Option – thẻ Number 3
  4. Hình 1-3 Hộp thoại Customize Regional Option – thẻ Date Cách 2: Tìm và chạy chương trình từ menu Start. Chọn Start | Programs . Chọn chương trình muốn chạy và kích đúp phím trái chuột để chạy. Hình 1.14 mô tả cách chạy chương trình Microsoft Excel từ menu Start. Cách 3: Tìm và chạy chương trình từ menu Start | Run. Để chạy chương trình từ cửa sổ Run của menu Start, có thể nhập trực tiếp tên chương trình vào cửa sổ Run như hình 1.15. Chú ý cần nhập đầy đủ cả đường dẫn. Có thể dùng nút Browse để tìm đến vị trí có chương trình càn chạy và thực hiện chạy chương trình.Chỉ các file có phần mở rộng là *.com, *.exe, *.bat mới có thể thực thi được. Hình 1-4 Chạy chương trình từ menu programs 4
  5. Hình 1-5 Chạy chương trình từ cửa sổ Run 1.2. CĂN BẢN VỀ EXCEL 1.2.1 Giới thiệu Microsoft Excel là phần mềm bảng tính điện tử nổi tiếng trong gói phần mềm Mcrosoft Office của Microsoft. Excel chạy trên môi trường Windows và được dùng phổ biến trong công tác văn phòng, trong quản lí bởi tính đơn giản, trực quan và dễ sử dụng của nó. Cho đến thời điểm hiện tại (năm 2007) Microsoft đã đưa ra thị trường phiên bản Office 7.0. Tuy nhiên trên thị trường đang sử dụng phổ biến phiên bản Office XP hoặc Office 2003. Giáo trình này trình bày các điểm chung ứng dụng cho các phiên bản Office 97, 2000, XP, 2003 nhưng minh họa bằng Office 2003. Để cài mới Office hoặc cài đặt bổ sung thêm một số tính năng của Office. Có thể cài đặt Office từ đĩ CD hay từ ổ cứng. Nếu cài dặt từ CD thì đưa đĩa CD vào ổ CD quá trình cài đặt sẽ tự động tiến hành. Nếu cài đặt từ ổ cứng thì tìm đếm thư mục chứa bộ cài đặt và chạy file Setup.exe. Thông thường bộ cài đặt thường chứa tại D:\setup\Office. Hình 1.6 minh họa cách chạy file setup.exe trong thư mục D:\setup\office2003. Khi cửa sổ cài đặt hiện ra yêu cầu nhập số đăng ký (product key) tiến hành nhập như trong hình 1.7. Nhấn NEXT khi quá trình nhập số đăng ký kết thúc. Chấp nhận các điều kiện về bản quyền phần mềm và nhấn NEXT. Cửa sổ như hình 1.8 hiện ra, chọn cài đặt Custom Install để chọn các tính năng nâng cao ứng dụng cho môn học Tin học ứng dụng. Ở bước tiếp theo chọn Choose Advanced Customization of Applications. Nhấn NEXT để tiếp tục quá trình cài đặt. Hình 1-6 Chạy file setup để cài đặt MS Office 5
  6. Hình 1-7 Nhập số đăng ký và chấp nhận điều kiện bản quyền Khi quá trình cài đặt kết thúc, nhấn OK. Để khởi động Excel, thực hiện một trong các cách sau Cách 1: Kích đúp vào biểu tượng Excel trên nền desktop. Cách 2: Từ menu Start | Programs | Microsft office | Microsoft Office Excel 2003 như trong hình dưới đây: 6
  7. Hình 1-8 Chọn Custom Install để cài các tính năng nâng cao cho Excel Hình 1-9 Chọn Runall from Mycomputer để cài đặt các gói Add-ins cho Excel Các thành phần chính của màn hình làm việc của Excel như trong hình 1.10 Hình 1. 10 Màn hình làm việc của Excel Thanh tiêu đề (Title): Thanh tiêu đề chứa biểu tượng của Excel, tên tệp hiện tại, và nút 7
  8. thu nhỏ cửa sổ (minimize), nút phóng to cửa sổ (maximize) và nút đóng cửa sổ (close). Thanh bảng chọn (Menu): Thanh menu liệt kê các lệnh chính của Excel. Mỗi mục ứng với một menu dọc, để mở menu dọc nháy chuột vào tên mục. Thanh công cụ chuẩn (Standard): Thanh công cụ standard chứa một số lệnh thông dụng của Excel dưới dạng các nút biểu tượng. Các lệnh này có thể được truy xuất trực tiếp bằng chuột. Để hiện tên các nút trỏ chuột lên trên nút mà không nháy chuột. Thanh công cụ đinh dạng (Formatting): Thanh formattng chứa các lệnh dưới dạng các nút có biểu tượng để định dạng dữ liệu của bảng tính như kiểu font, cỡ font Thanh công thức (Formula): gồm các ô Namebox (hiển thị toạ độ của ô hiện hành), Insert Function (nhập công thức vào ô), Formula bar (nội dung dữ liệu của ô hiện hành). Thanh cuốn (Scroll): Hai thanh trượt bên phải và bên trái cửa sổ dể hiển thị những phần bị che khuất của bảng tính. Dòng trạng thái (Status): Dòng chứa chế độ làm việc hiện hành như Ready (sẵn sàng nhập dữ liệu), Enter (đang nhập dữ liệu), Point (đang ghi chép công thức tham chiếu đến một địa chỉ), Edit (đang điều chỉnh dữ liệu hay công thức trong ô hiện hành) hay ý nghĩa lệnh hiện hành của bảng tính và các tình trạng hiện hành của hệ thống như NumLock, Capslock Danh sách sheet và các nút dịch chuyển (Sheet tab): Tên các sheet được hiển thị trên các vạt (tab) ở góc trái dưới cửa sổ workbook. Để di chuyển ví trí của sheet trong workbook kích giữ và thả chuột đến ví trí mới. Vùng làm việc: Với phiên bản 2003 về trước vùng làm việc bao gồm hơn 16 triệu ô bảng tính 1.2.2 Worksheet, workbook, địa chỉ Workbook. Một file bảng tính Excel được gọi là một Workbook. Workbook ngầm định có tên là book#. Khi mới khởi động, file bảng tính đầu tiên ngầm định có tên là book1.xls. Phần mở rộng XLS do Excel tự động thêm vào khi đặt tên file. Worksheet: Mỗi workbook chứa 256 worksheet (hay chartsheet). Các worksheet hay chartsheet được gọi là các trang bảng tính. Các trang này có thể chứa dữ liệu, công thức tính toán, đồ thị, bản đồ. Thêm một Worksheet mới: Dùng một trong các cách sau để chèn thêm một Sheet mới C1: Vào menu Insert | Worksheet. C2: Kích phải chuột lên một sheet bất kỳ nào đó trên bảng tính chọn Insert, hộp thoại Insert hiện ra như hình 1.21,chọn Worksheet và bấm nút OK để chèn. 8
  9. Hình 1-11 Chèn thêm worksheet Hình 1-12 Đổi tên worksheet Đổi tên một Sheet: Các bước đổi tên một sheet bao gồm: B1: Chọn Sheet muốn đổi tên. B2: Dùng một trong các cách: C1: Kích phải chuột lên Sheet đã chọn, chọn Rename, sau đó nhập tên cho Sheet như hình 1.22. C2: Vào menu Format | Sheet | Rename, sau đó nhập tên cho Sheet B3: Nhập tên xong gõ Enter để kết thúc Sắp xếp vị trí các sheet. Để sắp xếp vị trí các sheet, chọn một trong các cách sau: C1: Nhấp chuột vào tên sheet muốn di chuyển kéo và thả vào vị trí mong muốn. C2: Kích phải chuột lên sheet muốn di chuyển chọn Move or Copy, hộp thoại Move or Copy hiện ra. To book: Vị trí Workbook muốn sao chép tới hoặc di chuyển tới Before sheet: Vị trí đích muốn sheet chuyển tới. 9
  10. Hình 1-13 Sắp xếp vị trí worksheet Xoá bỏ một Sheet. Để xóa worksheet, dùng một trong các cách sau C1: Chọn Sheet muốn xoá, vào menu Edit | Delete sheet C2: Kích phải chuột vào sheet muốn xoá chọn Delete Nếu sheet không chứa dữ liệu thì Excel xóa mà không hỏi gì. Nếu sheet có chứa dữ liệu, sẽ hiện ra hộp thoại hỏi xác nhận thao tác xóa như hình 1.24. Để xác nhận thao tác xóa, nhấn nút delete. Để hủy nhấn nút cancel. Hình 1-14 Hộp thoại xác nhận xóa dữ liệu Cell. Mỗi worksheet chứa hơn 16 triệu ô (cell). Ô là giao của các cột và các dòng. Từ phiên bản Office 2003 trở về trước, mỗi sheet có 256 cột ký hiệu từ A-IV. Đồng thời mỗi sheet có 65.536 dòng ký hiệu từ 1- 65536. Trong phiên bản Excel 2007, số cột trong một worksheet là 16 384 đánh số từ A- XFD và số dòng là 1 048 576 đánh số từ 1-1 048 576. Do đó mỗi sheet có 17 178 820 608 000. Các ô được tham khảo bằng địa chỉ ô. Dạng địa chỉ thông dụng là Cột Dòng Ví dụ ô A1 là giao của cột A dòng 1. Ngoài ra còn sử dụng dạng địa chỉ R1C1 (số thứ tự dòng số thứ tự cột) Để chọn một dòng trong bảng tính, bấm chuột trái vào chữ số ký hiệu dòng đó. Để chọn một cột, bấm chuột trái vào chữ cái ký hiệu cột đó. Để chọn một vùng gồm nhiều ô liên tục, bấm giữ chuột trái và kéo. Cũng có thể sử dụng phím shift kết hợp với phím mũi tên hoặc phím shift kết hợp với bấm chuột trái để chọn một vùng. Để chọn nhiều vùng không liền nhau, nhấn giữ phím Ctrl trong khi bấm chuột trái. Để chọn cả bảng tính, bấm chuột trái vào vị trí góc trên cùng bên trái của bảng tính, nơi giao nhau của tên hàng và tên cột hoặc dùng tổ hợp phím Ctrl + A. Để điều chỉnh độ rộng của dòng bấm giữ và kéo chuột trên đường phân cách giữa hai vị trí tiêu đề tên cột. 10
  11. Để thay đổi độ rộng của dòng, bấm giữ và kéo chuột trên đường phân cách giữa hai dòng tại vị trí tiêu đề dòng. Để thay đổi độ rộng của nhiều cột, chọn vùng có các cột cần thay đổi độ rộng, vào menu Format | Column | Width, hộp thoại column width xuất hiện. Gõ vào độ rộng cột cần thay đổi rồi chọn OK hay nhấn Enter. Làm tương tự cho dòng (row). Địa chỉ tương đối: Địa chỉ tương đối có dạng cột dòng ví dụ B3. Một công thức có chứa địa chỉ tương đối khi copy đến vị trí mới địa chỉ sẽ tự động biến đổi. Địa chỉ tuyệt đối: Địa chỉ tuyệt đối có dạng $cột$dòng. Ví dụ $B$3. Một công thức có chứa địa chỉ tuyệt đối khi copy đến vị trí mới địa chỉ không thay đổi. Địa chỉ hỗn hợp: Địa chỉ hỗn hợp là dạng kết hợp của cả địa chỉ tương đối và địa chỉ tuyệt đối theo dạng $cột dòng (ví dụ $B3) hay cột $dòng (ví dụ B$3). Khi copy một công thức có chứa địa chỉ hỗn hợp, thành phần tuyệt đối không biến đổi, thành phần tương đối biến đổi. Để chuyển đổi giữa các dạng địa, sử dụng phím chức năng F4. 1.2.3 Các dạng dữ liệu trong Excel Nắm vững các dạng dữ liệu rất quan trọng để giải các bài toán trong Excel. Để định dạng dữ liệu, lựa chọn (bôi đen) vùng dữ liệu, chọn menu format | cells | numbers. Cửa sổ như hình 1.15 hiện ra. Có thể sử dụng tổ hợp phím tắt Ctrl + 1 hoặc kích chuột phải vào vùng đã chọn cũng cho cùng kết quả. Hình 1. 15 Định dạng dữ liệu General - Dữ liệu tổng quát: Kiểu này do Excel tự động nhận dạng 11
  12. Number - Dữ liệu số: Dữ liệu kiểu số tự động căn thẳng bên phải ô. Theo ngầm định, kiểu số sử dụng dấu chấm (.) để ngăn cách phần thập phân và phần nguyên. Biểu diễn số âm trong Excel sử dụng 4 cách như trong hình 1.26 Cách 1: Sử dụng dấu trừ (-) như trong toán học. Cách 2: Sử dụng dấu ngoặc đơn như trong kế toán. Cách 3: Sử dụng màu đỏ. Cách 4: Vừa dùng màu đỏ vừa dùng dấu ngoặc đơn. Các hàm tài chính của Excel sử dụng cách thứ tư. Để sử dụng dấu phân cách phần nghìn, bấm chọn ô Use 1000 separator (,). Thay đổi số các số sau dấu phân cách thập phân trong ô Decimal places. Hình 1-16 Biểu diễn số âm trong Excel Currency - Dữ liệu kiểu tiền tệ: Dữ liệu kiểu tiền tệ tự động căn thẳng bên phải ô. Dạng này biểu diễn các đơn vị tiền tệ của các nước trên thế giới. Dữ liệu kiểu tiền tệ biểu diễn số thập phân giống như dữ liệu kiểu số. Accounting - Dữ liệu kiểu kế toán: Dữ liệu kiểu kế toán tự động căn thẳng bên phải ô. Dữ liệu kiểu kế toán cũng biểu diễn các đơn vị tiền tệ tương tự như dữ liệu kiểu currency, tuy nhiên không có cách biểu diễn số âm. Date - Kiểu ngày tháng: Dữ liệu kiểu ngày tháng tự động căn thẳng bên phải ô. Kiểu ngày tháng có thể thực hiện với các phép tính số học. Theo ngầm định, kiểu ngày tháng nhập vào Excel theo dạng MM/DD/YY. (MM: Biểu diễn tháng. DD: Biểu diễn ngày. YY: Biểu diễn năm). Trong cửa sổ type liệt kê các cách hiển thị ngày trong Excel. Time - Kiểu thời gian: Kiểu thời gian tự động căn thẳng bê phải ô. Biểu diễn thời gian trong Excel có dạng HH:MM:SS. (HH: Chỉ giờ. MM: Chỉ phút SS: Chỉ giây). Percentage - Kiều phần trăm: Kiểu phần trăm tự động căn thẳng bên phải ô. Kiểu phần 12
  13. trăm đổi một số sang dạng phần trăm bằng cách nhân với 100 và thêm dấu % vào sau số đó. Fraction - Kiểu phân số: Kiểu phân số tự động căn thẳng bên phải ô. Kiểu phân số biểu diễn các số ở dạng phân số. Kiểu hiển thị được chọn trong mục Type. Text - Kiểu ký tự: Kiểu ký tự là sự pha trộn của các chữ cái các chữ số và các ký tự đặc biệt Dữ liệu kiểu ký tự tự động căn trái. Sử dụng dữ liệu kiểu ký tự trong hàm hoặc trong các phép toán phải được bao giữa cặp dấu nháy kép (“”). Lưu ý: Cặp dấu nháy kép khác 2 cặp dấu nháy đơn (‘’). Chú ý: Với các dãy kí tự bắt đầu bằng số 0 vô nghĩa (ví dụ số điện thoại 0913 ) khi nhập vào bảng tính, Excel sẽ tự động cắt đi số 0 đầu tiên. Để giữ lại số 0 này, sử dụng dấu (‘) trước khi dãy kí tự hoặc định dạng ô kiểu ký tự. Scientific - Kiểu rút gọn: Áp dụng cho kiểu số. Khi biểu diễn các số quá lớn hoặc quá nhỏ, Excel đưa về dạng rút gọn để tiết kiệm không gian. Ví dụ: 123.45 = 1.23E+02; 0.00123 = 1.23E-03 Special - Kiểu đặc biệt: Kiểu này dùng để biểu diễn các dạng đặc biệt như mã số bưu điện, số điện thoại kiểu Mỹ Custom - Kiểu do người dùng định nghĩa: Với kiểu này người dùng có thể định nghĩa cách hiển thị các dạng dữ liệu theo ý muốn. Ví dụ: Khi nhập ngày vào Excel, có thể sử dụng cách ngầm định MM/DD/YY (tháng/ngày/năm). Sau đó sử dụng kiểu custom để định dạng thành ngày kiểu Việt Nam bằng cách nhập vào cửa sổ type DD/MM/YYYY (Ngày/Tháng/Năm) như trong hình 1.26 1.2.4 Các phép tính trong Excel Trong Excel có thể thực hiện các phép toán số học (+, -, *, / và ^), các phép toán logic (>=, <= ), và phép toán ghép chuỗi (&). Bảng 1.1 tóm tắt các phép toán và cách dùng. Hình 1-17. Dùng kiểu Custom để định dạng ngày tháng 13
  14. Bảng 1. 1 Các phép tính trong Excel Phép toán Ý nghĩa Ví dụ + Phép cộng 1+2 = 3 - Phép trừ 5-2 = 3 * Phép nhân 3*5 = 15 / Phép chia 27/3 = 9 ^ Luỹ thừa 2^3 = 23 = 8 % Phần trăm 5% = 0.05 & Nối chuỗi “Việt” & “Nam” = “Việt Nam” > Lớn hơn 4 > 3 = True = Lớn hơn hoặc băng “Aa” >= “aa” = False Khác “a” <> “A” = True Thứ tự thực hiện các phép toán như sau: phép toán lũy thừa, phép toán nhân chia, phép toán cộng trừ, phép toán logic.Để thực hiện các phép toán trái với thứ tự này, sử dụng dấu ngặc đơn ( ). 1.2.5 Sử dụng hàm trong Excel Hàm (Function) trong Excel là một tổ hợp các công thức đã được xây dựng sẵn nhằm thực hiện các tính toán hay xử lí chuyên biệt nào đó. Hàm được đặc trưng bởi tên hàm, dấu mở đóng ngoặc và danh sách các tham số. VD: Hàm tính tổng =Sum(2,5,3) = 10. Tên hàm là các tên chuẩn do Excel quy định. Dấu mở đóng ngoặc bắt buộc phải có. Đối số là các dữ liệu đầu vào để tính toán hay xử lý. Đối số có thể có hoặc không có. Nếu có nhiều hơn một đối số thì các đối số được ngăn cách bởi dấu “,”. Trường hợp dấu “,” được sử dụng để làm dấu thập phân thì dấu ngăn cách là dấu “;”. Xem hình 1.12 mục 1.10 để thiết lập cách biểu diễn kiểu số. Các đối số có thể là kiểu số, kiểu ngày tháng, là địa chỉ ô, kiểu ký tự, có thể là hàm khác. Số lượng đối số tối đa là 30. Trong Excel 2007, số lượng đối số tối đa là 64. Nếu ký tự nhập vào từ bàn phím thì phải bao trong cặp dấu nháy kép (ví dụ “A”). Khi sử dụng các hàm lồng nhau, hàm nọ làm đối số cho hàm kia, Excel cho phép các hàm lồng nhau tối đa là 7 cấp. Trong Excel 2007, hàm có thể lồng nhau tối đa là 50 cấp. Để nhập hàm vào bảng tính, sử dụng một trong các cách sau: Cách 1: Nhập hàm trực tiếp vào ô chứ kết quả tính toán theo cú pháp = tenham(danh sách tham số). Có thể nhập trực tiếp từ thanh công thức. Cách 2: Nhập hàm từ giao diện Insert function. Truy cập menu Insert | function hoặc từ biểu tượng fx trên thanh công thức 14
  15. Khi đó, hộp thoại insert fucntion sẽ hiện ra như hình trên, chọn tên hàm từ mục select a fucntion. Có thể chọn mục select a category để chọn nhóm hàm nhằm thu hẹp phạm vi tìm kiếm hàm. Mục category chỉ ra một số nhóm hàm như nhóm hàm tài chính (finacial), nhóm hàm ngày tháng và thời gian (date & time), nhóm hàm toán học và lượng giác (math & trig), nhóm hàm thống kê (statistical), nhóm hàm tìm kiếm và tham khảo (lookup & reference), nhóm hàm cơ sở dữ liệu (database), nhóm hàm xử lý ký tự (text), nhóm hàm logic (logical), nhóm hàm thông tin bảng tính (information) và nhóm hàm kỹ thuật máy tính (engineering). Hình 1-18 Hộp thoại Insert function 1.2.6 Một số hàm thông dụng trong Excel - Excel cung cấp nhiều hàm hỗ trợ tính toán và thống kê dữ liệu. - Toàn bộ các hàm Excel được liệt kê trong biểu tượng (Insert Function). - Cú pháp chung của hàm: Tên_hàm(các tham số) Ví dụ: = SUM(4, 5) Sum là tên hàm và 4, 5 là 2 tham số. - Lưu ý: Tên hàm trong Excel không phân biệt chữ thường và chữ hoa. a. Một số hàm Logic Hàm AND - Cú pháp: AND(logical1, [logical 2], ) - Với logical là biểu thức. 15
  16. - Ý nghĩa: Trả về giá trị đúng khi tất cả các biểu thức tham gia đều đúng. - Ví dụ 1: = AND(7 > 5, 3 > 2) Hàm trả về kết quả là TRUE. - Ví dụ 2: = AND(7 > 5, 6 > 8) Cho kết quả là FALSE. Hàm OR Cú pháp: OR(logical1, [logical 2], ) Với logical là biểu thức. Ý nghĩa: Trả về giá trị đúng khi một trong các biểu thức tham gia có giá trị đúng. Ví dụ 1: = OR(7 > 5, 3 8) Hàm trả về kết quả là FALSE. Ví dụ 3: = OR(7 5, 10 + 2, 5 * 2) Cho kết quả là 12. Ví dụ 2: = IF(7 < 5, 50, 100) Cho kết quả là 100. b. Các hàm xử lý chuỗi Hàm LEFT Cú pháp: LEFT(text, [num_chars]) Text: Là chuỗi ký tự gốc. Num_chars: Số ký tự muốn lấy. Ý nghĩa: Trích ra n ký tự ở bên trái của chuỗi được chỉ định. Ví dụ: = LEFT("abcd", 2) Trả về giá trị là "ab". = LEFT("abcd") Trả về giá trị là "a" (Nếu bỏ qua tham số thứ 2 thì sẽ tự động hiểu là giá trị 1). Hàm RIGHT Cú pháp: RIGHT(text, [num_chars]) Text: Là chuỗi ký tự gốc. Num_chars: Số ký tự muốn lấy. 16
  17. Ý nghĩa: Trích ra n ký tự ở bên phải của chuỗi được chỉ định. Ví dụ: = RIGHT("abcd", 3) Trả về giá trị là "bcd". = RIGHT("abcd",1) Trả về giá trị là "d". Hàm MID Cú pháp: MID(text, start_num, num_chars) Text: Là chuỗi ký tự gốc. Start_num: Là vị trí bắt đầu. Num_chars: Số ký tự muốn lấy. Ý nghĩa: Trích ra n ký tự tính từ vị trí thứ p của chuỗi được chỉ định. Ví dụ: = MID("abcd", 2, 2) Trả về giá trị là "bc". = MID("abcd", 3, 2) Trả về giá trị là "cd". Hàm LOWER Cú pháp: LOWER(text) Ý nghĩa: Chuyển hết thành ký tự thường trong chuỗi. Ví dụ: = LOWER(“Trường Thế Giới Tin Học – Ngoại Ngữ Infoworld”) Cho kết quả là: trường thế giới tin học – ngoại ngữ infoworld. Hàm UPPER Cú pháp: UPPER(text) Ý nghĩa: Chuyển hết thành ký tự in hoa trong chuỗi. Ví dụ: = UPPER (“ infoworldshool”) Cho kết quả là: INFOWORLDSHOOL. Hàm PROPER Cú pháp: PROPER(text) Ý nghĩa: Chuyển ký tự đầu của mỗi từ thành chữ in hoa. Ví dụ: = PROPER(“microsoft excel”) Cho kết quả là: Microsoft Excel. Toán tử nối chuỗi: & Cú pháp: "text 1" & "text 2" & 17
  18. c. Các hàm truy tìm dữ liệu Hàm VLOOKUP Thực hiện việc tìm kiếm giá trị x trong một vùng dữ liệu được chỉ định, nếu thấy thì sẽ lấy giá trị ở cột xác định tương ứng. Cú pháp: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Lookup_value: Là giá trị dò tìm. Giá trị được tìm kiếm trên cột bên trái của table_array. - Table_array: Là vùng dò tìm. Vùng dò tìm bao gồm một cột bên trái chứa dữ liệu cần tìm và các cột bên phải chứa các dữ liệu cần lấy. Địa chỉ nên là tuyệt đối (nên đặt tên cho vùng). - Col_index_num: Số thứ tự cột trong table_array, nơi VLOOKUP sẽ lấy giá trị trả về. - Range_lookup: Giá trị Logic xác định việc tìm kiếm chính xác hay gần đúng, nếu là: - 0 (hoặc FALSE): Là tìm kiếm chính xác, nếu tìm thấy thì lấy kết quả ở cột tương ứng, nếu không tìm thấy thì trả về giá trị #N/A. - 1 (hoặc TRUE): Là tìm kiếm gần đúng, nếu không tìm thấy thì lấy giá trị gần đúng. Điều kiện của tìm kiếm gần đúng là vùng dữ liệu phải được sắp xếp theo thứ tự tăng dần của cột đầu tiên. Hàm HLOOKUP Thực hiện việc tìm kiếm giá trị x trong một vùng dữ liệu được chỉ định, nếu thấy thì sẽ lấy giá trị ở dòng xác định tương ứng. Cú pháp: HLOOKUP(lookup_value, table_aray, row_index_num, [range_lookup]) Lookup_value: Là giá trị dò tìm. Table_array: Là vùng dò tìm. Row_index_num: Là số thứ tự dòng cần lấy trong table_array. Range_lookup: Là phương thức dò tìm. d. Một số hàm số học Hàm INT Cú pháp: INT(number) Ý nghĩa: Hàm lấy phần nguyên của một số. Khi dữ kiện trong Cell là chuỗi thì kết quả là #VALUE!. Khi không có dữ kiện trong Cell thì kết quả là 0. Ví dụ: = INT(12.7) Trả về kết quả là 12. = INT(10.2) Trả về kết quả là 10. 18
  19. Hàm MOD Cú pháp: MOD(number, divisor) - Number: là số bị chia. - Divisor: là số chia. - Ý nghĩa: Trả về phần dư của phép chia nguyên. Kết quả có cùng dấu với số chia. - Ví dụ: = MOD(25, 7) Trả về kết quả là 4. Hàm ROUND Cú pháp: ROUND(number, num_digit) - Ý nghĩa: Làm tròn số number với độ chính xác đến con số thứ num_digit. - Nếu num_digit > 0 thì number sẽ được làm tròn đến chữ số thập phân thứ num_digit. - Nếu num_digit = 0 thì number sẽ được làm tròn đến số nguyên gần nhất. - Nếu num_digit < 0 thì number sẽ được làm tròn đến chữ số bên trái num_digit của dấu thập phân. - Ví dụ: Ô A1 chứa số 347654.126 - = ROUND(A1, -3) Kết quả trả về 348000 - = ROUND(A1, 2) Kết quả trả về 347654.13 Hàm MAX - Cú pháp: MAX(number1, [number2], ) - Number1, number2, : Từ 1 đến 30 đối số. Các đối số là giá trị số, hoặc tên khối, dãy hay tham chiếu đến những giá trị số. Các giá trị chuỗi, Logic, Cell rỗng sẽ bị bỏ qua. - Ý nghĩa: Trả về giá trị lớn nhất trong danh sách các đối số. - Ví dụ: = MIN(10, 2, 8) trả về giá trị là 10. Hàm MIN - Cú pháp: MIN(number1, [number2], ) - Number1, number2, : Từ 1 đến 30 đối số. Các đối số là giá trị số, hoặc tên khối, dãy hay tham chiếu đến những giá trị số. Các giá trị chuỗi, Logic, Cell rỗng sẽ bị bỏ qua. - Ý nghĩa: Trả về giá trị nhỏ nhất trong danh sách các đối số. - Ví dụ: = MIN(10, 2, 8) trả về giá trị là 2. Hàm AVERAGE - Cú pháp: AVERAGE(number1, [number2], ) - Number1, number2, : Từ 1 đến 30 đối số. Các đối số là giá trị số, hoặc tên khối, dãy hay tham chiếu đến những giá trị số. Các giá trị chuỗi, Logic, Cell rỗng sẽ bị bỏ qua. - Ý nghĩa: Trả về giá trị trung bình của các đối số. - Ví dụ: = AVERAGE(17, 2, 8) Trả về giá trị là 9. 19
  20. Hàm SUM - Cú pháp: SUM(number1, [number2], ) - Number1, number2, : Từ 1 đến 30 đối số. Các đối số là giá trị số, hoặc tên khối, dãy hay tham chiếu đến những giá trị số. Các giá trị chuỗi, Logic, Cell rỗng sẽ bị bỏ qua. - Ý nghĩa: Hàm tính tổng của các đối số. Hàm RANK - Cú pháp: RANK(number, ref, [order]) - Number: Là ô muốn xếp hạng. - Ref: Dãy địa chỉ hoặc một tên tham chiếu đến một vùng. - Order: - Nếu là 0 (hoặc bỏ trống): Thứ tự xếp hạng được tính theo giá trị đã sắp xếp giảm dần trong danh sách các số. - Nếu khác 0: Thứ tự xếp hạng được tính theo giá trị đã sắp xếp tăng dần trong danh sách các số. - Ý nghĩa: Xếp hạng một số trong danh sách các số. Hàm COUNT - Cú pháp: COUNT(value1, [value2], ) - Value1, value2, : Từ 1 đến 30 đối số. - Những đối số nào có giá trị số thì được đếm. Nếu đối số là một dãy hay một tham chiếu thì chỉ những giá trị nào là số mới được đếm. Giá trị sẽ trả về 0 (Zero) nếu không có giá trị số nào được tìm thấy trong đối số. - Ý nghĩa: Đếm số Cell dữ liệu kiểu số trong miền địa chỉ. Hàm COUNTA - Cú pháp: COUNTA(value1, [value2], ) - Ý nghĩa: Đếm số Cell không rỗng trong miền địa chỉ. Các giá trị được đếm là giá trị chuỗi, giá trị số, giá trị ngày, giá trị rỗng do hàm trả về và kể cả giá trị lỗi nhưng không kể Cell rỗng. Hàm COUNTIF - Cú pháp: COUNTIF(range, criteria) - Range: Là phạm vi Cell cần đếm. - Criteria: Là tiêu chuẩn để được đếm. Tiêu chuẩn này dưới hình thức một giá trị số, biểu thức hay chuỗi ký tự để chọn lọc điều kiện cho Cell nào được đếm. - Ý nghĩa: Đếm số Cell trong vùng thỏa điều kiện đã cho. 20
  21. Hàm SUMIF - Cú pháp: SUMIF(range, criteria, [sum_range]) - Range: Là vùng ô để so sánh với criteria. - Criteria: Là điều kiện cộng, có thể là số, chữ hoặc biểu thức. Quyết định ô nào trong vùng sum_range sẽ được cộng. - Sum_range: Là vùng ô sẽ được cộng. - Ý nghĩa: Tính tổng của những ô trên vùng sum_range nếu vùng điều kiện range của dòng tương ứng thoả mãn điều kiên criteria. e. Một số lỗi thường gặp trong Excel ##### Lỗi độ rộng . Lỗi này sinh ra khi cột thiếu độ rộng. Dùng chuột kéo độ rộng cột ra cho phù hợp. Khi giá trị ngày tháng hoặc thời gian nhập vào là số âm cũng phát sinh lỗi này. #VALUE! Lỗi giá trị. Lỗi này sinh ra khi công thức được nhập vào một chuỗi trong khi hàm yêu cầu một số hoặc một giá trị logic. Có thể do đang nhập một hoặc chỉnh sửa các thành phần của công thức mà vô tình nhấn Enter. Cũng có thể do nhập quá nhiều tham số cho một toán tử hoặc một hàm trong khi chúng chỉ dùng một tham số. Ví dụ =LEFT(A2:A5). Một trường hợp sinh lỗi # VALUE khi là thi hành một marco (lệnh tự động) liên quan đến một hàm mà hàm đó trả về lỗi #VALUE! #DIV/0! Lỗi chia cho 0. Lỗi này sinh ra do nhập vào công thức số chia là 0. Ví dụ = MOD(10,0) hoặc số chia trong công thức là một tham chiếu đến ô trống. #NAME! Sai tên. Lỗi này do dùng những hàm không thường trực trong Excel như EDATE, EOMONTH, NETWORKDAYS, WORKDAYS, Khi đó cần phải vào menu Tools - Add-ins. Đánh dấu chọn vào tiện ích Analysis ToolPak. Cũng có thể do nhập sai tên một hàm số. Trường hợp này xảy tra khi dùng bộ gõ tiếng Việt ở chế độ Telex vô tình làm sai tên hàm như IF thành Ì, VLOOKUP thành VLÔKUP. Trường hợp dùng những ký tự không được phép trong công thức cũng phát sinh lỗi này. Một số trường hợp khác bao gồm hhập một chuỗi trong công thức mà không có đóng và mở dấu nháy đôi."" hoặc không có dấu 2 chấm : trong dãy địa chỉ ô trong công thức. #N/A Lỗi dữ liệu. Lỗi này sinh ra khi giá trị trả về không tương thích từ các hàm dò tìm như VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH hoặc dùng hàm HLOOKUP, VLOOKUP, MATCH để trả về một giá trị trong bảng chưa được sắp xếp. Cũng có thể do không đồng nhất dữ liệu khi sử dụng địa chỉ mảng trong Excel. Trường hợp quên một hoặc nhiều đối số trong các hàm tự tạo hoặc dùng một hàm tự tạo không hợp lý cũng sinh ra lỗi này. #REF! Sai vùng tham chiếu. Lỗi này phát sinh do xóa những ô đang được tham chiếu bởi công thức. Cũng có khi do dán những giá trị được tạo ra từ công thức lên chính vùng tham chiếu của công thức đó. Có thể do liên kết hoặc tham chiếu đến một ứng dụng không thể chạy được. #NUM! Lỗi dữ liệu kiểu số. Lỗi này phát sinh do dùng một đối số không phù hợp trong công thức sử dụng đối số là dữ liệu kiểu số. Ví dụ công thức chỉ tính số dương nhưng lại nhập vào 21
  22. số âm. Có thể do dùng hàm lặp đi lặp lại dẫn đến hàm không tìm được kết quả trả về. Cũng có trường hợp do dùng một hàm trả về một số quá lớn hoặc quá nhỏ so với khả năng tính toán của Excel. #NULL! Lỗi dữ liệu rỗng. Lỗi này do dùng một dãy tóan tử không phù hợp hoặc dùng một mảng không có phân cách. 1.2.7. Công thức mảng Trong Excel có một khả năng độc đáo là tính toán với các mảng dữ liệu. Các công thức kiểu này được gọi là công thức mảng. Một công thức mảng có thể trả kết quả về một ô hay là một vùng (range). Một số hàm của Excel thao tác với công thức mảng như MINVERSE,MMULT, TREND, LINEST, LOGEST, GROWTH Để nhập công thức mảng, chọn vùng chứa kết quả, nhập công thức mà nhấn đồng thời tổ hợp phím Control+Shift+Enter (CSE). Đây là điểm khác biệt quan trọng giữa công thức mảng và công thức thường. Công thức thường kết thúc việc nhập công thức bằng phím Enter. Chú ý rằng Excel tự động sinh ra cặp dấu { } bao lấy công thức mảng. Sử dụng công thức mảng có các ưu điểm sau: - Tránh được vô tính sao chép sai công thức do chạy địa chỉ tham chiếu - Tránh được việc vô tình xóa hay sửa chữa một ô nào đó của vùng công thức mảng vì công thức mảng không cho phép sửa chữa hay xóa một ô trong vùng có công thức mảng. - Tránh việc người sử dụng không thành thạo Excel làm xáo trộn các công thức a. Công thức mảng trả kết quả vể một vùng nhiều ô Hình 1.20 minh họa việc sử dụng công thức mảng. Thông thường để tính toán giá trị cho cột thành tiền, cần phải sử dụng 6 phép toán nhân (nhập công thức cho ô D2 rồi copy công thức cho vùng D2:D7). Tuy nhiên khi sử dụng công thức mảng chỉ cần nhập một công thức duy nhất. Chọn vùng sẽ lưu kết quả (trong hình 1.30 là vùng D2:D7), nhập công thức = B2:B7*C2:C7 nhấn CSE. Như vậy vùng D2:D7 khi xem sẽ thấy công thức {= B2:B7*C2:C7}. Hình 1. 19 Công thức mảng trả về một vùng 22
  23. b. Công thức mảng trả kết quả về một ô Vận dụng ý tưởng trên, nếu không cần tính toán cột thành tiền mà chỉ cần tính tổng tiền thu được thì có thể sử dụng công thức mảng như sau: {=SUM(B2:B7*C2:C7)}. Hình 1.31 Minh họa cách sử dụng công thức này. Chú ý rằng trường hợp này có thể sử dụng công thức =SUMPRODUCT(B2:B7,C2:C7) cũng cho cùng kết quả. Hình 1. 20 Công thức mảng trả kết quả về một ô Trong các chương sau, ta sẽ có dịp sử dụng một số công thức mảng của Excel áp dụng cho hồi quy và dự báo. c. Hiệu chỉnh công thức mảng Do không thể xóa một ô, thay đổi nội dung của ô hay chèn thêm một ô vào vùng có chứa công thức mảng, để hiệu chỉnh công thức mảng, kích hoạt thanh công thức hay nhấn phím F2, Excel sẽ bỏ cặp dấu { } và khi hiệu chỉnh xong lại nhấn tổ hợp phím CSE để kết thúc. Để mở rộng hay thu hẹp công thức mảng nhiều ô, chọn toàn bộ vùng chứa công thức mảng trên bảng tính. Nhấn phím F2 để vào chế độ hiệu chỉnh. Nhấn Control+Enter để chuyển về công thức thường. Chọn vùng xuất kết quả mới bằng cách thu hẹp hay mở rộng vùng cũ. Nhấn phím F2 để vào chế độ hiệu chỉnh. Kết thúc bằng CSE. 23
  24. BÀI TẬP CHƯƠNG 1 Bài 1 Tạo thư mục trong ổ đĩa C: theo dạng C:\tenthumuc. Trong đó tenthumuc là tên lớp của sinh viên. Bài 2 Sử dụng các công thức đã học để điền vào các cột của bảng tính sau Đơn Thành STT Mã HĐ Mặt hàng Ngày Đơn vị SLượng giá tiền 1 K2AM3 07-12 5 2 H5BN4 12-12 2 3 K7AN3 08-12 12 4 H9BP4 09-12 7 5 B9NAB 11-12 3 6 B8NCB 07-12 5 7 K3AB3 10-12 6 8 K5AL0 14-12 4 9 B9NPB 13-12 10 10 K8AP3 14-12 20 Tổng Cộng Bảng Danh Mục Đơn giá Mã Tên mặt hàng Đơn vị (1000 đ) KA3 Giấy phô to A3 Ram 39,5 HB4 Bút chì đen Tá 15,2 KA0 Giấy A0 Tờ 10 BNB Bút bi Bến nghé Hộp 22,1 Tổng số tiền bán được của các mặt hàng Giấy Bút chì Bút bi Sử dụng ký tự thứ 1, thứ 3 và 5 để tim tên hàng trong bản danh mục. Tính tổng số tiền bán được bằng hàm DSUM. Ghi bài vào thư mục vừa tạo ở câu 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên, chương 1, bài số 2”. Bài 3 Nhập bảng I bảng II bảng III trong sheet 1. Nhập danh sách khách hàng trong sheet 2 Mã đk Số cũ Số mới 1259 CQC 572 793 1574SHB 2550 2802 2337SHA 1500 1580 4210CQB 3200 3450 4879CQA 4400 4650 5046CQD 2800 2970 5282KDB 4500 4980 5781SHC 2400 2450 6254KDA 1880 2200 8219KDC 1250 1350 9231KDD 4321 4492 24
  25. Bảng phu I Bảng phụ II K vực SH CQ KINH DOANH Kvực A B C D A 500 800 900 B 600 900 1000 SH 150 100 80 75 C 650 1000 1250 CQ 250 200 170 150 D 700 1200 1350 KD 200 150 120 100 Bảng phụ III Vượt DM Số lần Hệ số Dưới 1 1.5 Từ1 lần đến dưới 2 2.0 Trên hoặc bằng 2 3.0 Yêu cầu:. Tính số điện tiêu thụ của từng khách hàng.Tính định mức điện cho từng khách hàng dựa vào 2 ký tự thứ 5&6, và ký tự cuối của Mã DK và bảng II. Tính giá điện cho từng khách hàng dựa vào 2 ký tự thứ 5 & 6 và ký tự cuối Mã DK và bảng phụ I. Tính tiền điện đối với phần tiêu thụ điện trong định mức. Tính tiền điện đối với phần tiêu thụ vượt định mức dự theo hệ số phụ số lần vượt định mức (KTT-DM)/DM trong bảng III Tính số tiền khách hàng phải trả Sắp thứ tự bảng tính theo cột định mức giảm dần. Tính bảng thống kê sau SH CQ KD Tổng tiền điện Tiền điện vượt định mức cao nhất Tổng tiền điện vượt định mức Dựa vào bảng này vẻ biểu đồ Tổng tiền điện và tiền điện vượt định mức cho từng khách hàng Trích thông tin về những khách hàng tiêu thụ vượt định mức khu vực D. Ghi bài vào thư mục vừa tạo ở câu 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 1, bài số 3” Bài 4 Sử dụng các công thức đã học của Excel để điền vào các cột của bảng tính sau Tên S Đơn Thành Loại hđ Ngày Mã Hàng Loại lượng giá tiền X N X 23-02 FECT-L1 100 X X N 25-02 ALXD-L2 50 X 01-03 FECT-L2 20 15-03 FECT-L3 50 28-03 ALXD-L1 100 29-03 FECC-L1 500 04-04 ALXD-L3 30 25
  26. 2 ký tự đầu của mã hàng Tên hàng Đơn giá xuất FE Thép xây dựng 2 kí L L2 AL Nhôm xây Loại Loại 1 Loại 2 dựng FhàEn g 8000 60000 AL 12000 100000 0 Thống kê Tổng thành tiền Tên hàng Xuất Nhập FE AL Nếu loại hóa đơn là X (xuất) thì tra trong bảng phụ. Nếu loại hóa đơn là N (nhập) thì giá chỉ bằng 85% giá trong bảng phụ. Thành tiền = Sluợng*Đơn giá. Nếu hóa đơn bằng X và ngày 15/3 thì được giảm 10%. Điền thông tin vào bảng thống kê. Ghi bài vào thư mục vừa tạo ở câu 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 1, bài số 4” Bài 5 Sử dụng các công thức đã học của Excel để điền vào các cột của bảng tính sau Giá điện vượt định mức bằng giá điện định mức*1.35 Tạo bảng thống kê số tiền các hộ CQ, và các hộ CB phải nộp. Tiền Tiền Điện Chủ Loại Số Trong Vượt Phải Số cũ trong Ngoài kế hộ hộ Mới Đ mức Đ mức ĐM ĐM trả DK001 CQ-A 210 280 DK002 NN-D 220 330 DK003 CB-A 270 300 DK004 CB-C 270 350 DK005 CQ-A 300 340 DK006 CB-E 280 330 DK007 CB-C 210 340 DK008 CN-D 230 350 DK009 NN-D 260 320 DK010 CN-E 270 340 Bảng Định mức 26
  27. CQ CB CN KINH NN A 50 40 20 60DOAN 30 B 60 40 50 80 60 C 70 55 70 95 85 D 75 65 95 120 90 E 85 70 110 150 95 Bảng giá điện (US Cent) A B C D E CQ 30 40 45 55 70 CB 15 25 25 50 50 CN 12,5 24,5 20,5 50,5 40,5 KINH 35,7 60 70,5 80 90,5 NNDOAN 10 20 15 45 35 Ghi bài vào thư mục vừa tạo ở câu 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 1, bài số 5” Bài 6 Sử dụng các công thức đã học của Excel để điền vào các cột của bảng tính sau Ghi bài vào thư mục vừa tạo ở câu 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 1, bài số 6”. Đơn Số Ngày Thành STT Mã Hàng Tên hàng vị lượng bán Đơn giá tiền 1 PC586 14 02-04 2 PC686 10 12-04 3 FD120 12 09-04 4 HD420 4 22-04 5 HD850 10 18-04 6 PC586 4 27-04 7 FD120 10 11-04 8 FD185 10 06-04 Bảng tồn kho tháng 3 Đơn giá Số Mã hàng Tên hàng (USD) lượng PC586 Máy tính 586 700 20 PC686 Máy tính 686 1000 30 HD420 Đĩa cứng Samsung 80,5 20 HD850 Đĩa cứng MAXTOR 120,2 14 FD120 USB Ultra 4,9 10 FD185 USB Traveller 6,2 20 27
  28. Bài 7 A B C D E F G T Lương Ngày Tiền Ký T 1 S Họ Và Tên Chức Vụ Cơ Bản Công Thưởng Nhận 2 1 Trần Sang Giám Đốc 550,000 26 Phó Giám 3 2 Trương Thị Chi Đốc 435,000 24 4 3 Nguyễn Thị Hai Trợ Lý 379,000 25 5 4 Phạm Thị Hà Kế Toán 350,000 18 6 5 Trần Hoà Lê Kế Toán 345,000 16 7 6 Nguyễn Bính Kế Toán 345,000 26 Nhân Viên 8 7 Trần Nam VT 273,000 25 Nhân Viên 9 8 Vũ Thuý Bình VT 405,000 25 10 9 Hà Cẩm Lan Lễ Tân 425,000 26 11 10 Nguyễn Huy Lễ Tân 237,000 22 12 11 Hà Lan Anh Thủ Kho 237,000 22 13 Tổng Cộng Yêu cầu: 1. Nhập và lưu tập tin với tên TIENTHUONG.xls. 2. Định dạng bảng tính như trên. 3. Cột tiền thưởng được tính như sau: - Nếu ngày công >= 22 thì tiền thưởng là tháng lương cơ bản, còn ngược lại sẽ không có tiền thưởng. 4. Tính tiền thưởng của mỗi nhân viên và tổng số tiền thưởng. Bài 8 BÁO CÁO XUẤT KHẨU HÀNG HOÁ A B C D E F G H I g n T Tên Hàng Ký Hiệu á m Mã Thành ă T 1 S Th N Nước SL ĐG Tiền 2 1 Thể Thao Mùa Đông SPW1201NOW 45000 16 3 2 Jacket JKT1101FRA 7000 21 4 3 Quần Áo Trẻ Em CHL1101ENG 16000 7 5 4 Bảo Hộ Lao Động NIK0901POR 6500 8 6 5 Thể Thao Mùa Đông SPW1201DEN 2000 16 7 6 Jacket JKT0102GER 800 20 8 7 Thể Thao Mùa Hè SPS1002JAP 400 15 9 Tổng Cộng Yêu cầu: 28
  29. 1. Nhập và lưu tập tin với tên BAOCAO.xls. 2. Định dạng bảng tính như trên. 3. Cột tháng: Căn cứ vào ký tự thứ 4 và thứ 5 của cột ký hiệu hàng hoá. 4. Cột năm: Căn cứ vào ký tự thứ 6 và thứ 7 của cột ký hiệu hàng hoá. 5. Cột mã nước: Căn cứ vào 3 ký tự cuối của cột ký hiệu hàng hoá. 6. Cột thành tiền = số lượng * đơn giá. Bài 9 Yêu cầu: 1. TÊN HÀNG: Nếu kí tự bên trái của MÃ HÀNG là “B” thì ghi “Bàn”, “G” thì ghi “Ghế”, còn ngược lại ghi “Tủ”. 2. ĐƠN GIÁ: a. Nếu kí tự bên trái của MÃ HÀNG là “B” thì ĐƠN GIÁ =80000 , “G” thì ĐƠN GIÁ =15000, “T” thì ĐƠN GIÁ = 250000. b. TRỊ GIÁ = SỐ LƯỢNG * ĐƠN GIÁ. 3. TỈ LỆ THUẾ: a. Nếu ký tự bên phải của MÃ HÀNG là “1” thì TỈ LỆ THUẾ = 10%, ngược lại thì TỈ LỆ THUẾ =5%. b. THUẾ = TRỊ GIÁ * TỈ LỆ THUẾ. c. DOANH THU = TRỊ GIÁ -THUẾ. 29
  30. Bài 10 Yêu cầu: 1. LỚP HỌC: Nếu kí tự bên trái của MÃ SỐ là D thì ghi “Đồ Họa” Nếu kí tự bên trái của MÃ SỐ là Q thì ghi “Quản Lý” Nếu kí tự bên trái của MÃ SỐ là V thì ghi “Văn Phòng” BÌNH QUÂN = (MÔN 1 + MÔN 2 + MÔN 3) / 3 2. KẾT QUẢ : Nếu BÌNH QUÂN >=5 thì ghi “ĐẠT”,ngược lại thì ghi “THI LẠI” XẾP LOẠI : theo tiêu chuẩn sau: Nếu BÌNH QUÂN >=9 thì ghi “Giỏi” , nếu BÌNH QUÂN >=7 thì ghi “Khá”, nếu BÌNH QUÂN >=5 thì ghi “TB”, ngược lại thì ghi “YẾU” Bài 11 Yêu cầu: 1. Tạo tập tin và lưu trên đĩa C:\KQ_TUYENSINH.XLS. 2. Chèn vào trước cột Toán 2 cột NGÀNH THI và KHU VỰC; trước cột KẾT QUẢ 2 30
  31. cột TỔNG CỘNG v à ĐIỂM CHUẨN. 3. Sắp xếp bảng tính theo cột TÊN tăng dần. 4. Lập công thức điền dữ liệu vào cột KHU VỰC. Biết: KHU V ỰC là ký tự thứ 2 của MÃ SỐ. 5. Lập công thức điền dữ liệu vào cột NGÀNH THI. Biết: NGÀNH THI là ký tự thứ 1 của MÃ SỐ. Nếu ký tự đầu là A thì NGÀNH THI là Máy tính, B: Điện tử, C: Xây dựng, D: Hóa. 6. Lập công thức điền dữ liệu vào cột ĐIỂM CHUẨN. Biết: Nếu NGÀNH THI là Máy tính thì ĐIỂM CHUẨN là 19 , Điện tử: 17 , Xây dựng: 15, Hóa: 13. 7. Tính điểm tổng cộng là tổng điểm 3 môn. 8. Hãy lập công thức điền kết quả như sau :Nếu thí sinh có điểm tổng cộng lớn hơn hay bằng điểm chuẩn của ngành mình dự thi sẽ có kết quả là “Đậu”,ngược lại là “Rớt”. 9. Copy các công thức cho các ô còn lại. 31