Giáo trình Microsoft Excel 2010

n TênVT, Đơn Giá vào ô D13 và F13 (xem hình 3.16) =HLOOKUP(C13,$B$4:$G$7, 2,0)  Cassette 65 =HLOOKUP(C13,$B$4:$G$7,4,0)  170000 Hình 3.16: Ví dụ mô tả dùng hàm Hlookup NHỮNG SAI HỎNG THƯỜNG GẶP TT Hiện tượng Nguyên nhân Biện pháp khắc phục 1 Ban hành công thức báo lỗi  không xử lý được. Có thể một hoặc nhiều trong các nguyên nhân sau: - Sai cú pháp. - Sai tên hàm. - Địa chỉ dữ liệu lấy từ xa chưa truyền trị tuyệt đối. - Kiểm tra lại cú pháp của các hàm trên công thức  sửa cho đúng cú pháp. - Kiểm tra lại tên các hàm có trên công thức  sửa cho đúng tên hàm. - Nhấn F4 ngay sau khi lấy địa chỉ từ xa. 2 Ô có dạng ngày tháng không thực hiện +, -, *, / được. - Chưa thống nhất định dạng ngày tháng trong Control Panel và trong Format Cells. - Trước khi nhập dữ liệu ô có dạng ngày tháng phải kiểm tra định dạng Date trong Control Panel  Date và trong Format Cells  Date. 66 - Nếu nhập dữ liệu trước khi định dạng thì phải xóa dữ liệu  định dạng  rồi mới nhập lại dữ liệu. D. TIÊU CHÍ ĐÁNH GIÁ MÔ ĐUN. TT Nội dung Điểm chuẩn Điểm đánh giá I Điểm thao tác 10 1 Thực hiện ban hành được công thức. 05 2 Thực hiện truyền giá trị tương đối và tuyệt đối cho ô dữ liệu dạng số. 05 II Điểm kỹ thuật 80 1 Thực hiện ban hành công thức đúng cú pháp của các hàm xử lý văn bản khi giải bài toán  cho ra kết quả đúng. 10 2 Thực hiện ban hành công thức đúng cú pháp của các hàm về số và thống kê khi giải bài toán toán  cho ra kết quả đúng. 10 3 Thực hiện ban hành công thức đúng cú pháp của các hàm về ngày giờ khi giải bài toán toán  cho ra kết quả đúng. 10 4 Thực hiện ban hành công thức đúng cú pháp của các hàm logic khi giải bài toán toán  cho ra kết quả đúng. 15 5 Thực hiện ban hành công thức đúng cú pháp của các hàm tìm kiếm và tham chiếu khi giải bài toán toán  cho ra kết quả đúng. 15 6 Kế hợp tất cả các hàm đã học để giải bài toán phức tạp cho từng trường hợp cụ thể theo đúng yêu cầu. 10 7 Khắc phục được khi có lỗi xảy ra 10 III. Điểm thời gian 10 67 1 Đúng thời gian 0 2 Xong trước thời gian định mức cứ 1 phút thì được cộng thêm: 02 Điểm cộng tối đa là 10 điểm Tổng điểm 100 Bài 4. XỬ LÝ DỮ LIỆU A. MỤC TIÊU - Trình bày được một số khái niệm về cơ sở dữ liệu trên Excel; - Sắp xếp được dữ liệu, trích lọc cơ sở dữ liệu theo yêu cầu; - Sử dụng được một số hàm về cơ sở dữ liệu; - Rèn luyện ý thức lao động, tác phong công nghiệp, có trách nhiệm và sáng tạo. B. DỤNG CỤ - THIẾT BỊ - VẬT LIỆU TT TÊN THIẾT BỊ - DỤNG CỤ - VẬT LIỆU THÔNG SỐ KT ĐƠN VỊ SỐ LƯỢNG GHI CHÚ I THIẾT BỊ- MÁY MÓC 1 Máy Tính Core I3 – Ram 8G – HDD:500G bộ 18 2 Máy lạnh 2HP cái 02 3 Máy chiếu Shard PG-D3510X cái 01 4 Ổn áp ROBOT 10KVA cái 01 II DỤNG CỤ 1 Bàn học viên mặt đá rộng 0.96m cái 18 2 Ghế Ghế xếp hòa phát cái 18 3 Ghế giáo viên Ghế xoay hòa phát cái 01 68 4 Bảng viết bút lông (bảng từ) 1.6x1.25m cái 01 5 Bóng đèn Đèn tuýp Huỳnh Quang 14-40W cái 08 6 Quạt trần 03 cánh cái 04 7 Switch 18 cái 18 III VẬT LIỆU 1 Bút lông viết bảng. Bút lông bảng Thiên Long WB03 xanh. cái 2 Bút bi Thiên Long cái 3 Giấy in Double a A4 80 gsm trang 41 4 Mực in 12A Hộp 0,037 C. NỘI DUNG. 4.1. MỘT SỐ KHÁI NIỆM VỀ CƠ SỞ DỮ LIỆU TRÊN EXCEL CSDL (còn gọi là bảng dữ liệu) là tập hợp có cấu trúc các thông tin có liên hệ với nhau, được tổ chức theo một nguyên tắc nhất định nhằm phản ánh thuộc tính của một lớp đối tượng. Có các mô hình tổ chức CSDL như: mô hình phân cấp; mô hình mạng; mô hình quan hệ... Trong đó, mô hình quan hệ có thể được biểu diễn bởi mảng 2 chiều, tổ chức thành hàng và cột. Mỗi hàng chứa thông tin về một đối tượng được gọi là một mẫu tin (record), mỗi cột chứa thông tin phản ánh thuộc tính chung của các đối tượng, được gọi là trường dữ liệu (field). Trong Excel, CSDL được tổ chức theo mô hình quan hệ dưới dạng danh sách (list). Danh sách là một dạng đặc biệt của bảng tính, bao gồm một khoảng liên tục các ô. Trong danh sách, hàng đầu tiên chứa tên của các cột, các hàng còn lại chứa dữ liệu về các đối tượng trong danh sách. 4.2. SẮP XẾP DỮ LIỆU Để thực hiện một sắp xếp theo chiều tăng dần hay giảm dần trên một cột, thực hiện theo các bước sau: 69 - Bước 1: Chọn các ô muốn được sắp xếp - Bước 2: Click nút Sort & Filter trên tab Home + Click nút Sort Ascending (A-Z) nếu muốn sắp xếp dữ liệu tăng dần + Click nút Sort Descending (Z-A) nếu muốn sắp xếp dữ liệu giảm dần Hình 4.1: Mô tả chọn công cụ sắp xếp dữ liệu trên tab Home Tùy chỉnh sắp xếp: Để sắp xếp nhiều hơn một cột, thực hiện theo các bước sau - Bước 1: Chọn các ô muốn được sắp xếp - Bước 2: Click nút Sort & Filter trên tab Home - Bước 3: Click chọn Custom Sortxuất hiện hộp thoại như hình 4.2 - Bước 4: Chọn cột mà bạn muốn sắp xếp đầu tiên - Bước 5: Click Add Level - Bước 6: Chọn cột tiếp theo bạn muốn sắp xếp - Bước 7: Click OK 70 Hình 4.2: Hộp thoại Sort cho phép tùy chỉnh sắp xếp nhiều cột 4.3. LỌC CSDL Trong excel, bộ lọc cho phép bạn chỉ hiển thị dữ liệu mà đáp ứng các tiêu chuẩn nhất định. 4.3.1. Quy trình lọc đơn giản - Bước 1: Chọn vùng chứa dữ liệu mà bạn muốn lọc - Bước 2: Click Sort & Filter trên tab Home  Filter - Bước 3: Click vào mũi tên tại đầu cột mà bạn muốn lọc - Bước 4: Click vào ô check Select All để bỏ chọn dữ liệu đã mặc định - Bước 5: Click vào ô check của dữ liệu sẽ lọc ra (xem hình minh họa - Bước 6: Click OK 71 Hình 4.3: Ví dụ minh họa chọn dữ liệu lọc đơn giản 4.3.2. Quy trình lọc phức tạp - Bước 1: Chọn vùng chứa dữ liệu mà bạn muốn lọc - Bước 2: Nhấp chuột vào Sort & Filter trên tab Home  Filter - Bước 3: Click vào mũi tên tại đầu cột mà bạn muốn lọc - Bước 4: Nhấp chuột vào Text Filter (nếu cột dữ liệu bạn chọn thuộc dạng Text, Date Filte nếu cột dữ liệu bạn chọn dạng ngày tháng, Number Filter nếu cột dữ liệu bạn chọn dạng số). - Bước 5: Nhấp chuột chọn Custom. Xuất hiện hộp thoại như hình 4.4 sau: 72 Hình 4.4: Hộp thoại Custom AutoFilter Trong đó: + Equals: Bằng nhau + Does not equals: Không bằng nhau + Is greater than: Lớn hơn + Is greater than or equal to: Lớn hơn hoặc bằng + Is less than: Nhỏ hơn + Is less than or equal to: Nhỏ hơn hoặc bằng + Begin with: bắt đầu với + Does not begin with: Không bắt đầu với + Ends with: Kết thúc với + Does not ends with: Không kết thúc với + Contains: Chứa đựng, bao gồm + Does not contains: Không chứa đựng - Bước 5: Click chọn 1 trong các thực đơn đơn lệnh như mô tả ở trên (xem ví dụ hình 4.5) 73 Hình 4.5: Ví dụ chọn thực đơn lệnh để lọc phức tạp - Bước 6: Click Ok Để ko áp dụng bộ lọc, kích nút Sort & Filter  Click Clear 4.3.3. Lọc cao cấp (Advanced) - Bước 1: Đặt điều kiện lọc - Bước 2: Chọn vùng cơ sở cần lọc - Bước 3: Click tab Data  Advanced (xuất hiện hộp thoại Advanced Filter – xem hình 4.6) - Bước 4: Click chọn Copy to another location - Bước 5: Đặt con trỏ tại ô Criteria range  sau đó chọn vùng điều kiện đã đặt ở bước 1 - Bước 6: Đặt con trỏ tại ô Copy to:  sau đó chọn vùng sẽ đặt kết quả lọc - Bước 7: Click OK Xem hình 4.6 và thực hiện các bước lần lượt như đã ghi chú ở hình 74 Hình 4.6: Hình minh họa các bước lọc dữ liệu với Advanced 4.4. CÁC HÀM VỀ CSDL Là những hàm chỉ sử dụng đối với cơ sở dữ liệu. Khi sử dụng các hàm này thì đã có cơ sở dữ liệu và đã tạo vùng điều kiện. 4.4.1. Hàm DSUM Cú pháp: Dsum (CSDL,Cột,Vùng Điều Kiện) Công dụng: Tính tổng các ô trong “cột” ở các ô mẩu tin thỏa vùng điều kiện của CSDL Ví dụ: Tính tổng thành tiền của những mặt hàng có tên là Cassette (xem hình 4.7) = DSUM(B15:H20, 7, $I$14:$I$15)  25500000 75 Hình 4.7: Màn hình ví dụ tính tổng thành tiền của những mặt hàng Cassette 4.4.2. Hàm DMAX Cú pháp: Dmax(CSDL,Cột,Vùng Điều Kiện) Công dụng: Lấy giá trị ô lớn nhất trong cột ở các mẩu tin thỏa vùng điều kiện của CSDL. Ví dụ: CSDL xem hình 4.8. Tìm xem mặt hàng nào bán có giá trị thành tiền lớn nhất trong tháng 08 = DMAX(B15:H20,7,$I$14:$I$15)  27000000 Hình 4.8: Màn hình ví dụ sử dụng hàm Max 4.4.3. Hàm Dmin Cú pháp: Dmin(CSDL,Cột,Vùng Điều Kiện) 76 Công dụng: Lấy giá trị ô nhỏ nhất trong cột ở các mẩu tin thỏa vùng điều kiện của CSDL. Ví dụ: (Xem CSDL hình 4.9). Tìm LƯƠNG nhỏ nhất của những người có C.VỤ là NV được xếp loại A = DMIN(A3:K11,7,$L$3:$L$4)  1610000 Hình 4.9: Màn hình ví dụ sử dụng hàm Min 4.4.4. Hàm Dcounta Cú pháp: Dmin(CSDL,Cột,Vùng Điều Kiện) Công dụng: Đếm số ô có dữ liệu trong cột ở các mẩu tin thỏa vùng điều kiện của CSDL Ví dụ: đếm số người có C.VỤ là N.VIÊN xếp loại A (CSDL xem hình 4.8) = DCOUNTA(A3:K12,4, $L$3:$L$4)  2 NHỮNG SAI HỎNG THƯỜNG GẶP TT Hiện tượng Nguyên nhân Biện pháp khắc phục Lọc dữ liệu không được. - Điều kiện lọc sai. - Các vùng địa chỉ khi lọc chưa chính xác hoặc chưa truyền trị tuyệt đối. - Điều chỉnh lại điều kiện lọc cho đúng. - Kiểm tra lại các vùng địa chỉ, điều kiện khi lọc xem đã truyền trị tuyệt đối chưa? 77 D. TIÊU CHÍ ĐÁNH GIÁ MÔ ĐUN. TT Nội dung Điểm chuẩn Điểm đánh giá I Điểm thao tác 10 1 Sắp xếp được dữ liệu tăng dần. 05 2 Sắp xếp được dữ liệu giảm dần. 05 II Điểm kỹ thuật 80 1 Lọc được dữ liệu bằng Sort by. 08 2 Lọc được dữ liệu bằng Custom AutoFilter. 12 3 Lọc được dữ liệu bằng Advance Filter. 20 4 Thực hiện tính được tổng có điều kiện bằng hàm Dsum. 10 5 Thực hiện tìm số lớn nhất có điều kiện bằng hàm DMax. 10 6 Thực hiện tìm số nhỏ nhất có điều kiện bằng hàm DMin. 10 7 Thực hiện đếm có điều kiện bằng hàm DCounta. 10 III. Điểm thời gian 10 1 Đúng thời gian 0 2 Xong trước thời gian định mức cứ 1 phút thì được cộng thêm: 02 Điểm cộng tối đa là 10 điểm Tổng điểm 100 78 Bài 5. ĐỒ THỊ TRONG EXCEL A. MỤC TIÊU - Trình bày được các khái niệm, các thành phần và ý nghĩa của trong biểu đồ; - Thực hiện dựng được biểu đồ; hiệu chỉnh và định dạng biểu đồ; - Rèn luyện ý thức lao động, tác phong công nghiệp, có trách nhiệm và sáng tạo. B. DỤNG CỤ - THIẾT BỊ - VẬT LIỆU TT TÊN THIẾT BỊ - DỤNG CỤ - VẬT LIỆU THÔNG SỐ KT ĐƠN VỊ SỐ LƯỢNG GHI CHÚ I THIẾT BỊ- MÁY MÓC 1 Máy Tính Core I3 – Ram 8G – HDD:500G bộ 18 2 Máy lạnh 2HP cái 02 3 Máy chiếu Shard PG-D3510X cái 01 4 Ổn áp ROBOT 10KVA cái 01 II DỤNG CỤ 1 Bàn học viên mặt đá rộng 0.96m cái 18 2 Ghế Ghế xếp hòa phát cái 18 3 Ghế giáo viên Ghế xoay hòa phát cái 01 4 Bảng viết bút lông (bảng từ) 1.6x1.25m cái 01 5 Bóng đèn Đèn tuýp Huỳnh Quang 14-40W cái 08 6 Quạt trần 03 cánh cái 04 7 Switch 18 cái 18 79 III VẬT LIỆU 1 Bút lông viết bảng. Bút lông bảng Thiên Long WB03 xanh. cái 2 Bút bi Thiên Long cái 3 Giấy in Double a A4 80 gsm trang 20 4 Mực in 12A Hộp 0,018 C. NỘI DUNG. 5.1. GIỚI THIỆU ĐỒ THỊ Đồ thị giúp trình bày các số liệu khô khan bằng việc vẽ thành các hình ảnh trực quan, dễ hiểu. Đồ thị được liên kết với dữ liệu của nó trong bảng tính, do đó khi thay đổi dữ liệu của nó trong bảng tính thì lập tức đồ thị sẽ thay đổi tương ứng theo. Trong Excel 2010 việc vẽ đồ thị chưa bao giờ dễ dàng và đẹp như bây giờ. Excel có rất nhiều kiểu đồ thị khác nhau phục vụ cho nhiều mục đích khác nhau của rất nhiều loại đối tượng sử dụng bảng tính, chúng ta sẽ cùng nhau khám phá thế giới đồ thị của Excel trong bài học này. Đồ thị là một đối tượng (object) của Excel, đối tượng này chứa các dữ liệu và biểu diễn thành hình ảnh với màu sắc và kiểu dáng rất phong phú. Nhìn chung, Excel có 2 loại đồ thị đó là đồ thị nằm trong WorkSheet (còn gọi là Embedded chart) và ChartSheet. Để chuyển đổi qua lại giữa 2 loại đồ thị này ta làm như sau: Chọn đồ thị Chart Tools  Design  Location  Move Chart chọn Object in + Tên Sheet (đồ thị nằm trong Worksheet) hay chọn New sheet + Nhập tên ChartSheet vào. 80 Hình 5.1: Mô tả màn hình chuyển đổi giữa 2 đồ thị 5.2. VẼ ĐỒ THỊ Phần này trình bày các bước vẽ đồ thị từ một bảng số liệu cho trước và một số tùy chọn của đồ thị. Kết quả nghiên cứu về sự thõa mãn của các nhóm khách hàng phân theo độ tuổi được cho như hình bên dưới, nếu chúng ta dùng kết quả này để báo cáo cũng không có vấn đề gì, tuy nhiên báo cáo sẽ sinh động và thuyết phục hơn nếu chúng ta biến các con số này thành đồ thị để được nhìn thấy một cách trực quan hơn. Hình 5.2: Bảng số liệu nghiên cứu 5.2.1. Quy trình vẽ đồ thị Để vẽ đồ thị, bạn thực hiện theo các bước sau: - Bước 1: Chọn vùng dữ liệu, chọn luôn các nhãn của các cột. - Bước 2: Chọn kiểu đồ thị từ Ribbon Inserts  Chart. - Bước 3: Chọn nhóm hiển thị dạng đồ thị (ví dụ tôi chọn Column)  Clustered Column. 81 Hình 5.3: Mô tả màn hình vẽ đồ thị dạng Column 5.2.2. Thay đổi bố trí các thành phần trên biểu đồ: Các thành phần có trên biểu đồ hiển thị sau khi thực hiện quy trình vẽ, trong trường hợp bạn muốn thay dổi vị trí hiển thị thì thực hiện theo các bước sau: - Bước 1: Chọn đồ thị - Bước 2: Vào Chart Tools  chọn Design  Chart Layout. - Bước 3: Chọn cách bố trí thích hợp. Ví dụ tôi chọn kiểu Layout 3 trong Chart Layout. Hình 5.4: Mô tả màn hình thay đổi bố trí các thành phần trên biểu đồ 5.2.3. Đảo các chuỗi số liệu từ dòng thành cột và ngược lại: - Bước 1: Chọn đồ thị. - Bước 2: Vào Chart Tools    . 82 Chúng ta thực hiện lệnh này khi các đồ thị ở bước trên chưa hiển thị đúng như mong muốn. Ví dụ chúng ta muốn nhóm các nhóm tuổi lại để dễ so sánh giữa các tháng với nhau. Hình 5.5: Mô tả màn hình biểu đồ sau khi đảo vị trí đặt số liệu Đảo dòng/ cột Nếu thấy kiểu đồ thị trên không đẹp, chúng ta có thể đổi sang kiểu khác bằng cách: Chart Tools Design Type Change Chart Type, hộp thoại Insert Chart hiển thị liệt kê toàn bộ các kiểu đồ thị hiện có của Excel tha hồ cho bạn lựa chọn. Hình 5.6: Hộp thoại Insert Chart 83 Chọn màu cho đồ thị: Ngoài ra, nếu bạn thấy tông màu của đồ thị chưa đẹp thì vào chọn Chart Tools Design Chart Styles  More ( ). Hình 5.7: Màn hình mô tả chọn Chart Style 5.3. CÁC THAO TÁC TRÊN ĐỒ THỊ 5.3.1. Nhận biết các thành phần trên đồ thị Hình 5.8: Chọn thành phần trên đồ thị 1. Chart Title 7. Horizontal Axis 84 2. Chart Area 8. Data Table 3. Plot Area 9. Horizontal Axis itle 4. Data Label 10. Vertical Gridlines 5. Legend 11. Vertical Axis 6. Horizontal Gridlines 12. Vertical Axis Title Một số thành phần chỉ có trong đồ thị 3-D: - Back wall: Màu/ hình hền phía sau đồ thị - Side wall: Màu/ hình nền ở các cạnh bên của đồ thị - Floor: Màu/ hình nền bên dưới đồ thị - Column depth: Độ sâu của các thành phần biểu diễn chuỗi số liệu dưới dạng 3-D. 5.3.2. Các thao tác với đồ thị Chọn thành phần trên đồ thị: - Cách dễ nhất là dùng chuột nhấp lên thành phần cần chọn, thành phần được chọn sẽ có 4 hoặc 8 nút xuất hiện bao quanh. - Khi đồ thị đã được chọn, chúng ta có thể dùng các phím mũi để di chuyển đến các thành phần trong đồ thị. - Ngoài ra, bạn có thể chọn các thành phần tại Chart Tools  Current Selection. Hình 5.9: Chọn thành phần trên đồ thị Di chuyển đồ thị: - Bước 1: Chọn đồ thị. - Bước 2: Nhấn và giữ chuột di chuyển đồ thị đến vị trí. Thay đổi kích thước đồ thị: 85 - Bước 1: Nhấp trái chuột lên đồ thị vùng Chart Area để chọn đồ thị cần thay đổi kích thước, khi đó xung quanh đồ thị xuất hiện 8 nút nắm. - Bước 2: Di chuyển chuột vào các nút này, giữ trái chuột và kéo hướng vào tâm đồ thị để thu nhỏ và hướng ra ngoài để phóng to. Sao chép đồ thị: - Bước 1: Chọn đồ thị. - Bước 2: Nhấn tổ hợp phím Ctrl+C để chép đồ thị vào bộ nhớ. - Bước 3: Di chuyển con chuột đến một ô nào đó trong bảng tính  nhấn Ctrl+V để dán đồ thị vào. Xóa đồ thị: - Chọn đồ thị sau đó nhấn phím Delete để xóa đồ thị. - Để xóa Chart Sheet, trước tiên hãy chọn Chart Sheet, sau đó nhấp phải chuột và chọn Delete từ thực đơn ngữ cảnh hiện ra. Thêm các thành phần của đồ thị: - Chọn đồ thị  chọn Chart Tools  chọn Design  chọn Chart Layouts. Sử dụng các nhóm lệnh tạo các thành phần tương ứng trên đồ thị được thiết kế sẵn như tựa đề, chú thích, các nhãn, đường lưới, Sắp xếp và xóa các thành phần của đồ thị: - Một số thành phần trong đồ thị có thể di chuyển được như tựa đề, chú thích, nhãn. Muốn di chuyển thành phần nào trước tiên hãy dùng chuột chọn nó, sau đó nhấp và giữ trái tại cạnh của thành phần và kéo đến vị trí mới trong đồ thị. - Ngoài ra có thể vào Chart Tools  chọn Layout  chọn từ các danh sách thiết kế sẵn. - Để xóa thành phần nào, bạn chỉ cần dùng chuột chọn thành phần đó và nhấn phím Delete. Hình 5.10: Các thành phần trên đồ thị 86 In đồ thị: - In đồ thị cũng giống như in các đối tượng khác của Excel lưu ý Print Preview trước khi in để đảm bảo trang in được trọn vẹn nội dung. - Nếu bạn muốn in đồ thị thành một trang riêng thì hãy chọn đồ thị và nhấp nút Print để in, khi đó Excel chỉ ra lệnh in đồ thị mà bạn đang chọn. 5.3.3. Hiệu chỉnh và định dạng đồ thị Hiệu chỉnh đồ thị: - Bước 1: Chọn đồ thị  chọn Chart Tools  chọn Format  chọn Shape Styles  chọn kiểu định dạng - Bước 2: Nhấp chuột trái vào các nút bao quan Plot Area và kéo hướng vào Plot Area để thu nhỏ và hướng ra ngoài Plot Area để phóng to. Hiệu chỉnh tiêu đề đồ thị, chú thích, tiêu đề trục hoành và trục tung, Tùy theo kiểu đồ thị đang xử lý mà Excel cho phép bạn hiệu chỉnh các loại tiêu đề của kiểu đồ thị đó. - Để thêm tiêu đề chính cho đồ thị vào: Chart Tools  Layout  Labels  Chart Title  lựa chọn kiểu từ danh sách. - Để thêm tiêu đề cho trục hoành (hay trục tung) vào Chart Tools  chọn Layout  chọn Labels  chọn Axis Titles  lựa chọn kiểu từ danh sách. - Để thêm chú thích vào Chart Tools  chọn Layout  chọn Labels  chọn Legend  lựa chọn kiểu từ danh sách. - Để thêm nhãn dữ liệu vào Chart Tools  chọn Layout  chọn Labels  chọn Data Labels  lựa chọn kiểu từ danh sách. - Ngoài ra chúng ta có thể chèn các Text Box vào đồ thị Chart Tools  chọn Layout  chọn Insert  chọn Text Box. 87 Hình 5.11: Hiệu chỉnh chú thích cho đồ thị từ hộp thoại Data Source Hiệu chỉnh đường lưới ngang và dọc: - Muốn thêm hay bỏ đường lưới trước tiên bạn chọn đồ thị, rồi vào Chart Tools  chọn Layout  chọn Axes  Gridlines  chọn kiểu phù hợp từ danh sách. - Để hiệu chỉnh màu sắc, kiểu của đường lưới nào thì chọn nó và vào Chart Tools  chọn Format  Shape Style  chọn kiểu và màu sắc theo nhu cầu. - Hiệu chỉnh các trục Muốn hiệu chỉnh thông số cho trục trên đồ thị thì bạn hãy chọn trục  nhấp phải chuột  chọn Format Axis Sau đây là giải thích các thông số trong hộp thoại Format Axis. Hình 5.12: Hộp thoại Format Axis tùy theo giá trị của trục mà bạn chọn Hầu hết các đồ thị trình bày giá trị trên trục tung và phân nhóm trên trục hoành. Các bạn nên chọn thử để biết rõ hơn công dụng của các tùy chọn. 88 - Minimum: Xác định giá trị nhỏ nhất trên trục (giá trị khởi đầu) + Auto: Để Excel tự xác định + Fixed: Qui định giá trị nhỏ nhất cho trục - Maximum: Xác định giá trị lớn nhất trên trục (giá trị kết thúc) + Auto: Để Excel tự xác định + Fixed: Qui định giá trị nhỏ nhất cho trục - Major unit: Xác định giá trị các khoảng chia chính trên trục. + Auto: Để Excel tự xác định + Fixed: Qui định giá trị nhỏ nhất cho trục - Minor unit: Xác định giá trị các khoảng chia phụ trên trục. + Auto: Để Excel tự xác định + Fixed: Qui định giá trị nhỏ nhất cho trục - Values in reverse order: Đảo thứ tự sắp xếp các giá trị trên trục - Logarithmic scale: Chuyển các giá trị trên trục sang hệ logarith với cơ số do ta qui định tại Base (không thể dùng cho giá trị âm hay bằng 0) - Display units: Chọn đơn vị hiển thị trên trục. - Show display units label on chart: Cho hiển thị các nhãn đơn vị trên đồ thị - Major tick mark type: Qui định cách hiển thị ký hiệu phân cách chính trên đồ thị. + None Không sử dụng + Inside Hiển thị phía bên trong trục + Outside Hiển thị phía bên ngoài trục + Cross Hiển thị cắt ngang trục - Minor tick mark type Qui định cách hiển thị ký hiệu phân cách phụ trên đồ thị. - Axis labels Xác định cách hiển thị các nhãn trên trục + None Không sử dụng + High Hiển thị phía bên phải trục + Low Hiển thị phía bên trái trục + Next to Axis Hiển thị kế bên trục (theo mặc định) - Horizontal axis crosses: Các lựa chọn qui định cách mà trục tung cắt trục hoành (Đối với đồ thị 3-D đó chính là tùy chọn Floor crosses at) 89 + Automatic: Do Excel tự xác định. + Axis value: Xác định giá trị mà các trục sẽ giao nhau + Maximum axis value: Hai trục sẽ giao nhau tại giá trị lớn nhất trên trục (tung) 5.3.4. Thao tác với chuỗi số liệu trong đồ thị Xóa bớt một chuỗi số liệu khỏi đồ thị: - Bước 1: Chọn chuỗi số liệu trên đồ thị (Ví dụ: chọn đường cung) - Bước 2: Nhấn phím Delete trên bàn phím để xót chuỗi khỏi đồ thị Hình 5.13: Minh họa xóa chuỗi đường cung khỏi đồ thị Thêm chuỗi mới vào đồ thị: - Bước 1: Chọn đồ thị cần thêm chuỗi mới vào. - Bước 2: Nhấp chuột vào Chart Tools  Design  Data  Select Data. Hộp thoại Select Data Source xuất hiện. - Bước 3: Nhấp nút Add, hộp thoại Edit Series xuất hiện. - Bước 4: Đặt tên cho chuỗi mới tại Series Name (bằng tham chiếu hoặc nhập trực tiếp tên vào từ bàn phím)  chọn vùng chứa dữ liệu tại Series Values. - Ngoài ra ta có thể thêm nhanh chuỗi mới vào đồ thị bằng cách chép (Ctrl+C) dữ liệu của nó vào bộ nhớ, sau đó chọn đồ thị và dán (Ctrl+V) vào đồ thị. 90 Hình 5.14: Minh họa thêm chuỗi dữ liệu của tháng 3/2008 vào đồ thị Thay đổi chuỗi số liệu: Cũng với ví dụ trên nhưng ta muốn chỉ vẽ cho hai sản phẩm Giày và Dép - Bước 1: Chọn đồ thị cần chỉnh sửa. - Bước 2: Chart Tools  Design  Data  Select Data. Hộp thoại Select Data Source xuất hiện. - Bước 3: Chọn chuỗi cần chỉnh sửa, nhấp nút Edit, hộp thoại Edit Series xuất hiện - Bước 4: Chọn lại vùng dữ liệu mới tại Series Values. Làm tương tự cho các chuỗi số liệu khác. 91 Hình 5.15: Minh họa thay đổi chuỗi số liệu Thêm đường xu hướng vào đồ thị: Khi vẽ các đồ thị với dữ liệu theo thời gian chúng ta thường vẽ thêm đường xu hướng để biết được xu hướng trong tương lai của tập dữ liệu. Một tập số liệu có thể có nhiều đường xu hướng tùy theo cách phân tích của chúng ta. Để thêm đường xu hướng bạn thực hiện như sau: - Vào Chart Tools  chọn Layout  Analysis  Trendline  chọn kiểu đường xu hướng từ danh sách hay vào More Trendline Options Hình 5.16: Minh họa thêm đường xu hướng vào đồ thị 92 NHỮNG SAI HỎNG THƯỜNG GẶP TT Hiện tượng Nguyên nhân Biện pháp khắc phục 1 Không vẽ được đồ thị. - Không chọn dữ liệu khi thực hiện vẽ. - Chọn dữ liệu trước khi vẽ. - Thực hiện đúng quy trình. - Rèn luyện thật nhiều để hình thành kỹ năng. 2 Đồ thị vẽ ra không đúng mẫu. - Chưa/định dạng không đúng. - Thực hiện hiệu chỉnh và định dạng sau khi vẽ đồ thị. - Rèn luyện nhiều để hình thành kỹ năng. D. TIÊU CHÍ ĐÁNH GIÁ MÔ ĐUN. TT Nội dung Điểm chuẩn Điểm đánh giá I Điểm thao tác 10 Đảo được chuỗi số liệu từ hàng  cột và ngược lại. II Điểm kỹ thuật 80 1 Vẽ được đồ thị theo đúng yêu cầu. 10 2 Thay đổi các thành phần trên đồ thị theo đúng mẫu. 20 3 Định dạng màu nền cho các khối trên đồ thị đúng yêu cầu. 10 4 Hiệu chỉnh tên đồ thị, chú thích đúng vị trí – font – size – màu chữ. 20 5 Thêm/xóa chuỗi số liệu trên đồ thị. 10 6 Kẻ được đường lưới trên đồ thị 10 III. Điểm thời gian 10 1 Đúng thời gian 0 93 2 Xong trước thời gian định mức cứ 1 phút thì được cộng thêm: 02 Điểm cộng tối đa là 10 điểm Tổng điểm 100 Bài 6. ĐỊNH DẠNG TRANG IN VÀ BẢNG TÍNH A. MỤC TIÊU - Trình bày được cách định dạng các thông số cho trang in; - Thực hiện in ấn được trang bảng tính; - Rèn luyện ý thức lao động, tác phong công nghiệp, có trách nhiệm và sáng tạo. B. DỤNG CỤ - THIẾT BỊ - VẬT LIỆU TT TÊN THIẾT BỊ - DỤNG CỤ - VẬT LIỆU THÔNG SỐ KT ĐƠN VỊ SỐ LƯỢNG GHI CHÚ I THIẾT BỊ- MÁY MÓC 1 Máy Tính Core I3 – Ram 8G – HDD:500G bộ 18 2 Máy lạnh 2HP cái 02 3 Máy chiếu Shard PG-D3510X cái 01 4 Ổn áp ROBOT 10KVA cái 01 5 Máy in HP – LaserJet M105 cái 01 II DỤNG CỤ 1 Bàn học viên mặt đá rộng 0.96m cái 18 2 Ghế Ghế xếp hòa phát cái 18 3 Ghế giáo viên Ghế xoay hòa phát cái 01 94 4 Bảng viết bút lông (bảng từ) 1.6x1.25m cái 01 5 Bóng đèn Đèn tuýp Huỳnh Quang 14-40W cái 08 6 Quạt trần 03 cánh cái 04 7 Switch 18 cái 18 III VẬT LIỆU 1 Bút lông viết bảng. Bút lông bảng Thiên Long WB03 xanh. cái 2 Bút bi Thiên Long cái 3 Giấy in Double a A4 80 gsm trang 35 4 Mực in 12A Hộp 0,032 C. NỘI DUNG. 6.1. CÁC CHẾ ĐỘ HIỂN THỊ TRANG Excel 2010 hỗ trợ mạnh hơn trong việc in ấn, với nhiều chế độ xem trực quan giúp bạn có thể thấy được kết quả in ngay trên màn hình. Có 3 chế độ hiển thị là Nornal View, Page Layout View và Page Break Preview. Để truy cập các chế độ xem vào nhóm View  chọn Workbook Views  chọn kiểu xem. - Normal View: Đây là chế độ bạn sử dụng thường xuyên trong quá trình nhập liệu, tính toán, trên bảng tính và là chế độ mặc định của Excel. - Page Layout View: Là chế độ xem trước khi in, trong chế độ này bạn vẫn có thể tính toán và nhập liệu. - Page Break Preview: Hiển thị bảng tính Excel với các dấu phân trang, tại đây bạn có thể chia lại trang bằng cách kéo thả các đường chia cách trang. 6.2. THIẾT LẬP THÔNG SỐ CHO TRANG IN Tất cả các tùy chọn thiết lập thông số trang in có tại menu Page Layout  nhóm Page Setup (xem hình 6.1): - Margins: tùy chọn lề trang. - Orientation: chiều trang ngang hay dọc. 95 - Size: tùy chọn khổ giấy. - Print Area: tùy chọn vùng in. - Breaks: tùy chọn ngắt trang. - Background: chèn hình nền. - Print Titles: tùy chọn in tiêu đề lặp lại trên mỗi trang,). Bạn có thể thiết lập các thông số tại đây hoặc vào hộp thoại Page Setup để điều chỉnh. Để vào hộp thoại Page Setup bạn vào menu Page Layout  đến nhóm Page Setup  nhấn vào nút Hình 6.1: Nhóm Page Setup trên Thanh Ribbon Hình 6.2: Hộp thoại Page Setup chọn thẻ Page 6.2.1. Thẻ Page - Orientation: + Portrait: Chọn trang dọc + Landscape : Chọn trang ngang - Paper size : Chọn khổ giấy in (A3, A4) 96 6.2.2. Thẻ margins Hình 6.3: Hộp thoại Page Setup chọn thẻ Margins - Top: canh lề trên - Bottom: canh lề dưới - Left: canh lề trái - Right: canh lề phải - Header: canh lề tiêu đề đầu trang - Footer: canh lề tiêu đề chân trang - Ngoài ra còn có tùy chọn canh giữa: Horizontally: canh giữa trang theo chiều ngang và Vertically: canh giữa trang theo chiều dọc 6.2.3. Thêm thông tin vào đầu và cuối trang: thẻ Header/Footer 97 Hình 6.4: Hộp thoại Page Setup chọn thẻ Header/Footer Khi click chuột chọn Custom Header sẽ xuất hiện hộp thoại như hình 6.5 Hình 6.5: Hộp thoại Header Khi click chuột vào Custom Footer sẽ xuất hiện hộp thoại như hình 6.6 Hình 6.6: Hộp thoại Custom Footer Các mã lệnh và nút lệnh trong Header và Footer Nút lệnh Mã lệnh Chức năng &[Page] Hiển thị số trang tài liệu &[Pages] Hiển thị tổng số trang được in &[Date] Hiển thị ngày hiện tại &[Time] Hiển thị giờ hiện tại 98 &[Path]&[File] Hiển thị đường dẫn đầy đủ đến nơi lưu tập tin và Tên tập tin &[File] Hiển thị tên tập tin &[Tab] Hiển thị tên sheet Không có Chèn hình lựa chọn vào. Bạn phải tìm đến hình cần chèn vào trong hộp thoại Insert Picture, chọn hình và nhấn nút Insert. Không có Thiết lập các thông cố cho hình chèn vào. Ở hộp thoại Custom Header và Custom Footer cho phép bạn thiết lập tiêu đề đầu trang và cuối trang như ở MS.Word. Có một điểm khác hơn so với MS.Word là Excel phân ra thành 3 vùng: - Left section: tiêu đề được canh lề trái - Center section: tiêu đề được canh giữa - Right section: tiêu đề được canh lề phải Bạn có thể chèn ngày giờ, số trang tự động, hiển thị tên tập tintừ vào các nút lệnh tương ứng trên hộp thoại như đã mô tả ở trên bảng “Các mã lệnh và nút lệnh trong Header và Footer”. Điều chỉnh tỉ lệ phóng to/ thu nhỏ trang in: Trong một số trường hợp ta cần in nén hay phóng to nội dung vào một số trang nhất định khi đó vào menu Page Layout  chọn Scale To Fit  chọn Scale. Ta có thể thu nhỏ tài liệu đến 10% và phóng to tới 400%. Ngoài ra ta có thể ép Excel in tài liệu ra với số trang ta qui định tại menu Page Layout  chọn Scale To Fit  chọn Width và Ribbon  chọn Page Layout  Scale To Fit  Height (xem hình 6.7) 99 Hình 6.7: Mô tả điều chỉnh tỉ lệ phóng to/ thu nhỏ trang in trên thanh Ribbon 6.3. THIẾT LẬP THÔNG SỐ HỘP THOẠI PRINT Để gọi hộp thoại Print, có 2 cách: - Cách 1: Nhấp chuột vào menu File  chọn Print. - Cách 2: Nhấn tổ hợp phím Ctrl+P. Dùng hộp thoại này để chọn máy in, chọn trang cần in, chọn số lượng bản sao và một số tùy chọn khác. Bạn thực hiện thiết lập hộp thoại in theo hình 6.8: Số bản in Chọn máy in Chọn in từ trang đến trang Chọn in trang dọc hay ngang Thực hiện in 100 Hình 6.8: Hộp thoại Print Tại Print Active Sheets  Nhấp chuột vào mũi tên, các thông số hiển thị với công dụng sau: - Print Selection: Chỉ in vùng đang chọn. - Print Active sheet(s): Chỉ in sheet hiện hành hay các sheet đang chọn. - Print Entire workbook: In toàn bộ workbook. NHỮNG SAI HỎNG THƯỜNG GẶP TT Hiện tượng Nguyên nhân Biện pháp khắc phục 101 1 - Khi in ra trang văn bản nhìn không đẹp mắt, khoảng cách lề trái – phải – trên – dưới không đúng quy định; lề phải của văn bản không thẳng hàng giữa các dòng với nhau - Chưa định dạng trước khi in. - Chưa có kỹ năng. - Định dạng Page Setup trước khi in bao gồm canh lề: trái – phải – trên – dưới đúng quy định. - Chọn khổ giấy, số trang, số bản trước khi in. - Định dạng số trang trước khi in. - Rèn luyện nhiều để tạo thành thói quen và kỹ năng xử lý. D. TIÊU CHÍ ĐÁNH GIÁ MÔ ĐUN. TT Nội dung Điểm chuẩn Điểm đánh giá I Điểm thao tác 20 1 Mở được hộp thoại Page Setup để định dạng 10 2 In ấn được văn bản 10 II Điểm kỹ thuật 70 1 Canh lề văn bản, chọn khổ giấy, hướng giấy đúng theo yêu cầu 30 2 Chèn được số trang giấy vào văn bản 10 Số trang được định dạng font, size, kiểu chữ, màu chữ đúng yêu cầu. 30 III. Điểm thời gian 10 1 Đúng thời gian 0 2 Xong trước thời gian định mức cứ 1 phút thì được cộng thêm: 02 Điểm cộng tối đa là 10 điểm Tổng điểm 100 102 BÀI TẬP THỰC HÀNH BÀI TẬP SỐ 1: 1. Khởi động Excel. Mở một Workbook mới. 2. Tạo một Workbook có nội dung sau và lưu lại với tên canban.xls BẢNG DỰ TRÙ VẬT TƯ STT VẬT TƯ ĐV Đ.GIÁ S.LG T.TIỀN T.TRƯỚC CÒN THIẾU 1 Xi măng Bao 50000 120 2 Đất Xe 150000 5 3 Đá Khối 320000 6 4 Gạch Viên 200 2000 5 Đinh Kg 7000 3 6 Sơn Kg 20000 12 7 Gỗ m 18000 35 Tổng cộng: Trong đó: T.TIỀN = Đ.GIÁ * S.LG - T.TRƯỚC = 40% của T.TIỀN - CÒN THIẾU = T.TIỀN – T.TRƯỚC - Thêm một cột T.LÃI sau cột T.TRƯỚC, T.LÃI = 0,8 % của CÒN THIẾU 3. Mở một Workbook mới, có nội dung sau và lưu lại với tên LUONG.XLS BẢNG LƯƠNG THÁNG 10 STT HỌ & TÊN LƯƠNG TẠM ỨNG THỰC LÃNH 1 Hoàng Phi Hồng 424000 2 Tiểu Yến Tử 554000 3 Lý Sư Huynh 450000 4 Võ Tắc Thiên 290000 5 Triều cái 290000 6 Ngô Tiên Sinh 390000 Tổng cộng: 103 Trong đó: - TẠM ỨNG = 3/5 của LƯƠNG. - Tính THỰC LÃNH = LƯƠNG – TẠM ỨNG. - Tính tổng cộng của các cột: LƯƠNG, TẠM ỨNG, THỰC LÃNH. - Trở qua cửa sổ Workbook canban.xls và tìm cách tính các ô ở hàng Tổng cộng. Sau đó thêm vào trong bảng này 3 vật tư với Đ.GIÁ, S.LG tùy ý. Sau đó quan sát xem cột hàng Tổng cộng có thay đổi gì không. BÀI TẬP SỐ 2: 1. Lập bảng tính sau và lưu với tên DULICH.XLSX BÁO CÁO CHI PHÍ CỦA CÔNG TY DU LỊCH HOÀN CẦU – TUẦN LỄ KẾT THÚC Đ.ĐIỂM DI CHUYỂN ĂN Ở PHỤC VỤ CỘNG TRẢ TRƯỚC Vũng Tàu 120000 Nha Trang 420000 Đà Lạt 420000 Huế 720000 Tổng cộng Thực thu Trong đó: - Cột DI CHUYỂN có giá trị bằng 20 % của cột ĂN Ở. - Cột PHỤC VỤ có giá trị bằng 80 % của cột DI CHUYỂN - Cột CỘNG có giá trị là tổng DI CHUYỂN, ĂN Ở VÀ PHỤC VỤ. - TRẢ TRƯỚC = ¾ của CỘNG - Tính các tổng các số liệu của hàng TỔNG CỘNG - Hàng Thực Thu có số liệu bằng 90 % của hàng Tổng cộng 104 2. Lập bảng tính sau và lưu lại với tên HANGHOA.XLS BÁO CÁO BÁN HÀNG Tên Hàng Đơn Giá Cửa Hàng số 1 Cửa Hàng số 2 Tổng Cộng SL T.TIỀN SL T.TIỀN T.TIỀN % (MH) Bánh 5000 50 55 Kẹo 3000 45 40 Sữa 4500 50 65 Đường 5500 25 35 Bột Ngọt 9500 65 75 Tổng Cộng: Trong đó: - Cột T.TIỀN (ở các cửa hàng) = Đơn Giá * Số Lượng. - Cột T.TIỀN (ở cột Tổng Cộng) = T.TIỀN (ở cửa hàng 1) + T.TIỀN (ở cửa hàng 2). - Dòng Tổng Cộng là tổng giá trị các cột - Cột % (MH) = giá trị T.TIỀN của từng mặt hàng ở cột Tổng Cộng / tổng giá trị T.TIỀN ở cột Tổng Cộng BÀI TẬP SỐ 3: 1. Tạo bảng tính dưới đây trong Sheet1 và đặt tên cho bảng tính BANG LUONG T12 CÔNG TY TNHH FREEHAND CỘNG HOÀ XÃ HỘI CHỦ NGHĨA VIỆT NAM PHÒNG KẾ TOÁN - TÀI VỤ Độc Lập - Tự Do - Hạnh Phúc ********** BẢNG LƯƠNG CÔNG NHÂN THÁNG 12/20007 STT HỌ VÀ TÊN C H Ứ C V Ụ M Ứ C L Ư Ơ N G NGÀY CÔNG T H À N H T IỀ N G H I C H Ú 1 Lê Văn Sơn TP 460.000 đ/ngay 26 ? T ạm ứ n g th aù n g 1 2 1 .0 0 0 .0 0 0 2 Trần Hoàng Hà PP 420.000 đ/ngay 24 105 3 Nguyễn Thanh Long NV 280.000 đ/ngay 26 4 Nguyễn Trường Giang NV 350.000 đ/ngay 26 Tạm ứ n g th án g 1 2 5 0 0 .0 0 0 ñ 5 Hà Thảo Trang NV 260.000 đ/ngay 28 6 Trần Bảo Ngọc BV 280.000 đ/ngay 27 TỔNG CỘNG ? ? - THÀNH TIỀN = MỨC LƯƠNG * NGÀY CÔNG - Tính tổng cộng các NGÀY CÔNG VÀ MỨC LƯƠNG - Định dạng cột THÀNH TIỀN có ký hiệu là "VNĐ"; cột MỨC LƯƠNG là “đ/ngày” 2. Tạo bảng tính dưới đây trong sheet 2 và đặt tên là HÀNG NHẬP Tỷ giá:16.004 VNĐ/USD STT Số Lượng Hàng Nhập Đơn Giá (USD) Thành Tiền Bắp(tấn) Gạo (tấn) Bắp Gạo Bắp Gạo 1 7T 5T 125 $/T 126 $/T ? ? 2 8T 6T 126 $/T 127 $/T ? ? 3 9T 7T 127 $/T 128 $/T ? ? 4 10T 8T 128 $/T 129 $/T ? ? 5 11T 9T 129 $/T 130 $/T ? ? 6 12T 10T 130 $/T 131 $/T ? ? TỔNG CỘNG: ? ? - Định dạng và nhập các cột số liệu như mẫu trên - Tính Thành Tiền cho mỗi loại hàng, chuyển đổi và định dạng là vnđ BÀI TẬP 4: Thực hành các hàm SUM, MIN, MAX, AVERAGE, IF. 1. Tạo bảng tính sau và lưu với tên VIDEO ĐIỂM THUÊ VIDEO SỐ: STT Mã Tên Phim Loại Đ.Giá Ngày Thuê Ngày Trả Thành Tiền BÁO CÁO HÀNG NHẬP NĂM 2007 106 1 S1 F1 10/11/2016 11/11/2016 2 S2 F2 10/11/2016 12/11/2016 3 S3 F3 10/11/2016 10/11/2016 4 C1 F1 10/11/2016 11/11/2016 5 C2 F1 10/11/2016 12/11/2016 6 C3 F2 10/11/2016 10/11/2016 7 T1 F3 10/11/2016 12/11/2016 8 T2 F1 10/11/2016 10/11/2016 9 T3 F1 10/11/2016 11/11/2016 10 T4 F2 10/11/2016 12/11/2016 Tổng: Yêu cầu: - Cột Đ.Giá tính theo ngày. Nếu Loại là F1 thì bằng 2000 còn lại 1500. - Cột Thành Tiền. Nếu trả trong ngày thì bớt 500, còn lại thì bằng số ngày thuê nhân với Đơn Giá. - Tổng là tổng cộng của cột Thành Tiền 2. Tạo Workbook có tên B.LƯƠNG.xls có nội dung như sau: BẢNG LƯƠNG THÁNG 12 / 2007 SỐ TT HỌ TÊN BẬC NGÀY CÔNG CÔNG NHẬT LƯƠNG TẠM ỨNG THỰC LÃNH 1 Hải Châu A 20 2 Diễm Quỳnh B 22 3 La Hán C 23 4 Lý Hương A 25 5 Phạm Khắc C 24 6 Trần Duy A 23 7 Hạ Băng B 24 Yêu cầu: 107 1. Cột CÔNG NHẬT phụ thuộc vào BẬC. Nếu BẬC A thì 60000. Bậc B thì 50000 còn lại 40000. 2. Cột LƯƠNG = NGÀY CÔNG * CÔNG NHẬT. 3. Cột TẠM ỨNG = 40% của LƯƠNG. 4. Cột THỰC LÃNH = LƯƠNG – TẠM ỨNG. 5. Tính Tổng, Trung Bình, Max, Min cho các cột tương ứng BÀI TẬP SỐ 5: Thực hành kết hợp nhiều hàm IF 1. Tạo bảng tính sau, lưu với tên LƯƠNG.xls Trong đó: 1. CÔNG NHẬT dựa vào LOẠI : - Nếu LOẠI A thì 70000 - Nếu LOẠI B thì 50000 - Nếu LOẠI C thì 40000 - Còn lại thì 30000 2. LƯƠNG = CÔNG NHẬT * NGÀY CÔNG. 3. THƯỞNG dựa vào NGÀY CÔNG: 108 - Nếu > 26 ngày thì 3/4 LƯƠNG - Nếu > 20 ngày thì 3/5 LƯƠNG - Còn lại không thưởng 4. PCCV dựa vào CHỨC VỤ: - Nếu là GĐ thì PCCV 30% LƯƠNG. - Nếu là TP thì PCCV 20% LƯƠNG. - Nếu là PP thì PCCV 10% LƯƠNG. - Còn lại không có PCCV. 4. TẠM ỨNG dựa vào NGÀY CÔNG: - Nếu > 26 ngày thì TẠM ÚNG 3/5 LƯƠNG. - Nếu > 23 ngày thì TẠM ÚNG 1/2 LƯƠNG. - Nếu > 20 ngày thì TẠM ỨNG 2/5 LƯƠNG - Còn lai không tạm ứng. 5. THỰC LÃNH = LƯƠNG + THƯỞNG + PCCV – TẠM ỨNG. 6. Tính Tổng, Max, Min, Trung Bình các cột tương ứng. BÀI TẬP SỐ 6: Thực hành các hàm: COUNT, COUNTIF, IF, AND, OR Tổng số thí sinh dự thi: [CT1] Số TT Họ Tên Hạnh Kiểm Ưu Tiên Môn Thi Điểm Cộng Tổng Điểm Kết Quả Văn Toán Hóa N.Ngữ 1 Lê Minh A CTB 7 8 9 6 2 Tần Hán B TN 2 7 4 5 3 Tiểu Hồng C 9 6 8 9 4 Trần Khải A TN 4 7 7 4 5 Vương Hà A 4 9 9 6 6 Lý Mai B TN 3 4 4 5 7 Thái Toàn C 8 9 9 8 8 Vĩ Hưng A CTB 3 4 4 9 9 Khải Vi A TN 5 8 9 9 109 Điểm Cao nhất: Điểm Thấp nhất: Điểm Trung bình: Đếm số học sinh đạt kết quả: Giỏi Khá TB Hỏng Yêu cầu 1. [CT1]: Đếm tổng số thí sinh dự thi. 2. Cột Điểm Cộng phụ thuộc vào cột Ưu Tiên và cột Hanh Kiểm. - Nếu Ưu Tiên là CTB thì Điểm Cộng là 2. - Nếu Ưu Tiên là TN thì Điểm Cộng là 1 - Và cả hai loại trên phải có Hạnh Kiểm là A hoặc B 3. Cột Tổng Điểm = tổng điểm Các Môn và Điểm Cộng. 4. Cột Kết Quả phụ thuộc vào Tổng Điểm: - Nếu Tổng Điểm >= 32 thì Kết Quả là Giỏi. - Nếu Tổng Điểm >= 26 và < 32 thì Kết Quả là Khá. - Nếu Tổng Điểm >= 20 và < 26 thì Kết Quả là TB. - Còn lại là Hỏng. 5. Tìm trị Cao Nhất, Thấp Nhất, Trung Bình của các cột tương ứng. 6. Đếm số thí sinh Giỏi, Khá, TB và Hỏng BÀI TẬP SỐ 7: Thực hành các hàm: Dò tìm, Left, Int, Mod.. Tạo bảng tính sau lưu với tên KHACHSAN BẢNG THỐNG KÊ TIỀN PHÒNG STT Mã số Phòng Thuê Tên Phòng Thuê Ngày Thuê Phòng Ngày Trả Phòng Tuần Ngày Tổng Cộng Số Tuần Đơn Giá Thành Tiền Số Ngày Đơn Giá Thành Tiền 1 A001 15/09/2007 27/09/2007 2 B002 11/09/2007 12/09/2007 110 3 C004 14/09/2007 17/09/2007 4 A005 20/09/2007 27/09/2007 5 C002 29/09/2007 12/10/2007 6 D009 27/09/2007 27/10/2007 7 B001 23/09/2007 23/09/2007 Yêu cầu: 1. Tên Phòng Thuê dựa vào ký tự đầu của Mã Số Phòng Thuê và bằng Bảng Giá Thuê Phòng. 2. Số Tuần Thuê phụ thuộc vào Ngày Thuê và Ngày trả. 3. Số Ngày Thuê phụ thuộc vào Ngày Thuê và Ngày trả. 4. Đơn Giá 1 Tuần và Giá 1 Ngày dựa vào ký tự đầu của Mã Số Phòng Thuê và Bảng giá Thuê Phòng. 5. Thành Tiền của Tuần = Số Tuần thuê * Đơn Giá tuần. 6. Thành Tiền của Ngày = Số Ngày thuê * Đơn Giá ngày. 7. Tổng Cộng = Tiền Tuần + Tiền Ngày 8. Thêm vào bên phải bảng tính hai cột Trả Trước và Còn Lại. 9. Cột Trả Trước dựa vào kí tự đầu của Mã Số Phòng Thuê và Bảng Giá Thuê Phòng. 10. Cột Còn Lại = Tổng Cộng – Trả Trước. 11. Tính tổng Min, Max, Trung bình các cột tương ứng. BẢNG GIÁ THUÊ PHÒNG Loại Phòng Giá 1 Tuần Giá 1 Ngày Tên Phòng Trả Trước A 500 80 Đặc Biệt 30% B 370 60 Hạng Nhất 20% C 240 40 Hạng Nhì 10% D 60 10 Bình Dân 5% 111 BÀI TẬP SỐ 8: Thực hành các hàm: Dò tìm, kết hợp IF, Left, Right, Mid, Value.. 1. Tạo các bảng tính sau và lưu với tên TIENNHA.XLS BẢNG THANH TOÁN THUÊ TIỀN NHÀ THÁNG SỐ TT MÃ NHÀ ĐƠN GIÁ SỐ THÁNG TIỀN NHÀ TIỀN ĐIỆN TIỀN NƯỚC TIỀN D.VỤ THÀNH TIỀN TRẢ TRƯỚC CÒN THIẾU 1 A-N01 2 A-N03 3 B-D07 4 B-D14 5 C-N05 6 B-D04 7 C-N03 8 B-N04 BẢNG ĐƠN GIÁ THUÊ NHÀ, DỊCH VỤ MÃ NHÀ A B C D ĐƠN GIÁ 800000 600000 400000 300000 TIỀN ĐIỆN 30000 28000 20000 18000 TIỀN NƯỚC 12000 10000 8000 6000 TIỀN D.VỤ 100000 90000 70000 50000 TRẢ TRƯỚC 70% 60% 50% 40% Yêu cầu: 1. ĐƠN GIÁ dựa vào kí tự đầu của MÃ NHÀ và BẢNG ĐƠN GIÁ THUÊ NHÀ, DỊCH VỤ. Nếu thuê dài hạn thì tính 80% ĐƠN GIÁ (dựa vào kí tự giữa của MÃ NHÀ để biết nếu D là dài hạn, N là ngắn hạn). 2. SỐ THÁNG dựa vào hai kí tự sau của MÃ NHÀ (nhớ đổi ra kiểu số). 3. TIỀN NHÀ = ĐƠN GIÁ * SỐ THÁNG THUÊ. 112 4. TIỀN ĐIỆN, TIỀN NƯỚC, TIỀN DỊCH VỤ dựa vào kí tự đầu cũa MÃ NHÀ và BÀNG ĐƠN GIÁ THUÊ NHÀ, DỊCH VỤ (nhớ nhân với SỐ THÁNG thuê). 5. THÀNH TIỀN = TIỀN NHÀ + TIỀN ĐIỆN + TIỀN NƯỚC + TIỀN DỊCH VỤ nếu thuê ngắn hạn thì cộng thêm khấu hao bằng 10% của THÀNH TIỀN. 6. TRẢ TRƯỚC dựa vào kí tự đầu của của MÃ NHÀ và BẢNG ĐƠN GIÁ THUÊ NHÀ, DỊCH VỤ (nhớ nhân với THÀNH TIỀN). Nếu thuê dài hạn không phải trả trước (TRẢ TRƯỚC = 0) 7. CÒN THIẾU = THÀNH TIỀN – TRẢ TRƯỚC BÀI TẬP SỐ 9: Thực hành về: CƠ SỞ DỮ LIỆU 1. Lập bảng xơ sở dữ liệu sau: (Trong đó LƯƠNG = HỆ SỐ * 180000) STT HỌ VÀ TÊN N.SINH C.DANH MÃ NGHẠCH HỆ SỐ LƯƠNG CHỨC VỤ TỔNG CỘNG 1 Trần Văn Tiễn 1957 N.Viên 1.009 2.08 374400 2 Đỗ Văn Lượng 1957 H.Trưởng 15.113 3.54 42000 679200 3 Lưu Cao Thắng 1965 Dạy Lớp 15.113 3.26 586800 4 Hà Hợp Phát 1961 H.Phó 15.113 2.98 30000 566400 5 Lê Văn Liệu 1970 Dạy Lớp 15.113 2.98 536400 6 Đỗ Đức Thắng 1973 H.Phó 15.113 2.70 30000 516000 7 Ng.Thanh tâm 1968 Dạy Lớp 15.113 2.42 435000 8 Đỗ T.Thu Vân 1975 Dạy Lớp 15.113 2.42 435000 9 Ng.Ngọc Nga 1978 Dạy Lớp 15.113 1.86 334000 10 Lưu thị Loan 1969 Dạy Lớp 15.113 2.42 435000 11 Phạm Trần Anh 1962 Dạy Lớp 15.113 2.42 435000 - Hãy rút trích những nhân viên có HỆ SỐ lương là 2.42 - Hãy rút trích những nhân viên có TỔNG CỘNG > 400000 và sinh sau 1960 - Hãy rút trích những nhân viên có LƯƠNG > 500000 hặc có tiền phụ cấp CHỨC VỤ 113 2. Tạo bảng thống kê BẢNG THỐNG KÊ H.Phó Dạy Lớp N.Viên Số Lượng Lương Tổng cộng Lương Cao Nhất Lương Thấp Nhất Lương Trung Bình 114 BÀI TẬP SỐ 10: Thực Hành: Vẽ Biểu Đồ 1. Thực hành vẽ biểu đồ theo dạng số liệu sau: (Tạo giống hình vẽ) Quốc Gia Singapore Indonesia Philippines Thailand Malayia Viêt Nam Số Lượng 7300 4500 7900 200 7300 1400 2. Vẽ biểu đồ theo số liệu và mẫu sau: Năm Đậu Cao Su Cà Phê Thịt Hạt Điều 2005 80681 75105 110502 8389 53460 2006 90000 81795 45355 4263 66098 115 BÀI TẬP 11: QUẢN LÝ NHÂN VIÊN STT MÃ NV TÊN NV NGÀY VÀO CÔNG TÁC SỐ NĂM LÀM VIỆC SỐ CON PHỤ CẤP PHÉP NĂM LƯƠNG THÁNG 01 A01 HOA 01/11/1998 ? 3 ? ? ? 02 B01 HẰNG 07/02/1999 2 03 A02 NGUYỆT 25/05/1990 1 04 B02 PHÚC 06/08/1991 4 05 C01 CHÂU 22/05/1992 5 06 D01 HIẾU 06/03/1987 3 07 C02 HÀ 09/11/1996 1 08 D02 TRUNG 03/11/1998 0 09 E01 MINH 22/07/1999 3 10 G01 AN 03/06/2001 2 11 E02 TRANG 08/09/2002 2 12 G02 HỒNG 04/06/1990 1 BẢNG 1 BẢNG 2 116 MÃ NV LƯƠNG /THÁNG A 1.000.000đ B 1.100.000đ C 1.200.000đ D 1.300.000đ E 1.500.000đ G 2.000.000đ Yêu cầu: 1. SỐ NĂM LÀM VIỆC = NĂM HIỆN TẠI - năm của NGÀY VÀO CÔNG TÁC. 2. PHỤ CẤP = PHỤ CẤP THÁNG + PHỤ CẤP CON Trong đó: - PHỤ CẤP THÁNG: Dựa vào 2 ký tự cuối của MÃ NV bạn tra trong BẢNG 2 để tính. - PHỤ CẤP CON: Mỗi con được phụ cấp tháng là 50.000đ, nếu 3 con trở lên thì không có phụ cấp 3. PHÉP NĂM: Từ năm làm việc thứ 3 mới được nghỉ phép 15 ngày. Sau năm thứ 5, mỗi năm thêm một ngày nghỉ phép 4. LƯƠNG THÁNG = PHỤ CẤP + LƯƠNG CB Trong đó: LƯƠNG CB dựa vào KÝ TỰ ĐẦU CỦA MÃ NV tra trong BẢNG 1. 5. Sắp xếp bảng tính theo TÊN NV tăng dần. Trích những nhân viên làm việc trên 10 năm BÀI TẬP 12: Tạo bảng tính dưới đây NGUYỆT SAN VÀ NHẬT BÁO STT MÃ BÁO TÊN BÁO NGÀY GIAO ĐƠN GIÁ SỐ LƯỢNG THÀNH TIỀN 1 TNNS01 ? 05/12/2007 ? ? ? MÃ NV PHỤ CẤP THÁNG 1 200.000đ 2 150.000đ 117 2 TNNB05 07/11/2007 3 CANB03 07/10/2007 4 PNNB04 08/11/2007 5 TTNS02 05/12/2007 6 CANS01 12/12/2007 7 PNNS03 15/11/2007 8 TTNB02 17/12/2007 BẢNG TRA Mã loại Tên báo Giá báo Nhật báo Nguyệt san TT Tuổi trẻ 2000 3000 TN Thanh niên 1500 3500 PN Phũ nữ 2000 5000 CA Công an 1500 3000 Mô tả: - Hai ký tự đầu của MÃ BÁO cho biết MÃ LOẠI - Ký tự thứ 3,4 là NS là báo Nguyệt San, NB là Nhật Báo Yêu cầu: 1. TÊN BÁO: Dựa vào 2 ký tự đầu của MÃ HÀNG dò tìm trong BẢNG TRA và kết nối vào phía sau chuỗi "Nguyệt San" nếu nếu ký tự thứ 3,4 là NS và "Nhật Báo" nếu ký tự thứ 3,4 là NB. 2. ĐƠN GIÁ: Dựa vào hai ký tự đầu của MÃ HÀNG dò tìm trong BẢNG TRA, lấy đúng với giá báo trong bảng 3. SỐ LƯỢNG: Nếu hai ký tự cuối<=3 thì số lượng là 100 còn lại là 50 4. THÀNH TIỀN = SÓ LƯỢNG * ĐƠN GIÁ, tuy nhiên sẽ giảm 5% của THÀNH TIỀN cho các mã báo có số lượng là 100 5. Tính tổng thu được từ ngày 5 đến ngày 12 của tháng 12 6. Săp xếp bảng tính với MÃ BÁO tăng dần 118 7. Trích đầy đủ các thông tin của báo Công An và báo Phụ Nữ mà có số lượng là 1 119 BÀI TẬP 13: KẾT QUẢ TUYỂN SINH NĂM 2007 MÃ SỐ TÊN SV NGÀNH THI KHU VỰC TOÁN LÝ HOÁ KẾT QUẢ A101 Trung 4 7 5 B102 Nga 5 6 6 C103 Mạnh 6 3 1 D204 Uyên 3 4 2 A205 Tùng 7 7 9 C106 Hùng 8 6 7 D107 Hoa 9 7 8 A208 Sơn 9 9 9 BẢNG 1 MÃ NGÀNH NGÀNH THI ĐIỂM CHUẨN 1 ĐIỂM CHUẨN 2 A Tin học 19 18 B Điện tử 17 16 C Xây dựng 16 15 D Hoá 15 14 Yêu cầu: 1. Chèn vào trước cột KẾT QUẢ 2 cột TỔNG CỘNG VÀ ĐIỂM CHUẨN 120 2. NGÀNH THI: Dựa vào ký tự đầu tiên của MÃ SỐ và tra trong BẢNG 1 3. KHU VỰC: là ký tự thứ hai của MÃ SỐ 4. Từ ký tự đầu của MÃ SỐ, KHU VỰC và BẢNG 1, hãy điền dữ liệu cho cột ĐIỂM CHUẨN. Trong đó nếu thí sinh nào thuộc Khu vực 1 thì lấy cho ĐIỂM CHUẨN 1, ngược lại thì lấy ĐIỂM CHUẨN 2. 5. TỔNG CỘNG: Là tổng của 3 môn. 6. KẾT QUẢ: Nếu thí sinh mà 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 thì ghi là “Rớt”. 7. Thêm cột HỌC BỔNG sau KẾT QUẢ và lập công thức điền vào “Có” nếu điểm TỔNG CỘNG của thí sinh lớn hơn hay bằng ĐIỂM HB của ngành mình dự thi, ngược lại để trống. 8. Thêm cột XẾP HẠNG vào trước cột HỌC BỔNG đồng thời xếp thứ hạng cho thí sinh nào có điểm TỔNG CỘNG cao nhất thì đứng hàng đầu bảng. 9. Tìm thí sinh nào có điểm cao nhất, thấp nhất. 10. Trích danh sách các thí sinh có KẾT QUẢ là “Đậu” 121 Bài Tập 14: Mã số Tên Xếp Loại Văn Toán Điểm cộng Diện UT Điểm UT Tổng công Kết Quả B12 Quế TB 4,5 8 A11 Gấm Khá 3 6 C22 ụa Giỏi 6 7,5 A11 Nhung Khá 5 3 B12 Toàn TB 2,5 4,5 Mã Số Diện UT Điểm UT B Diện 2 1,5 A Diện 1 2 C Diện 3 1 Tổng số thí sinh rớt Bảng phụ KÕt Qña Kú Thi TuyÓn Líp 10 Th«ng tin vÒ kú thi: Điểm văn cao nhất Điểm toán cao nhất Tổng số thí sinh Tổng số thí sinh đậu Yêu cầu: 1. Điểm cộng. - Nếu xếp loại giỏi thì điểm cộng là 2. - Nếu xếp loại khá thì điểm cộng là 1. - Còn lại không có điểm cộng. 2. Diện UT : Dựa vào ký tự đầu tiên ở cột Mã số và dò trong Bảng phụ. 3. Điểm UT : Dựa vào ký tự đầu tiên ở cột Mã số và dò trong Bảng phụ. 4. Tổng cộng là tổng của Điểm cộng, Văn, Toán và Điểm UT. 5. Kết quả : Nếu tổng điểm >=13,25 thì kết quả là Đậu, còn lại là rớt. 122 BÀI TẬP 15 STT Mã hàng Tên hàng Ngày bán Số lượng (kg) Đơn giá Tiền giảm Thành tiền 1 F Sắt 15/01/1998 50 2 C Đồng 10/02/1998 100 3 X Xi măng 20/04/1998 200 4 F Sắt 30/03/1998 20 5 C Đồng 20/04/1998 50 6 A Nhôm 10/02/1998 30 7 X Xi măng 15/01/1998 50 1 2 3 4 F Sắt 5000 5500 5000 5500 A Nhôm 7000 8000 9000 9000 C Đồng 3000 300 3500 4000 X Xi măng 8000 8500 9000 10000 1 2 3 4 Tổng thành tiền Tổng số lượng của mặt hàng xi măng bán trong tháng 1 Yêu cầu: 1. Tên hàng: Dựa vào cột Mã hàng và dò trong bảng Đơn giá 2. Dựa vào Mã hàng và tháng bán hàng trong cột Ngày bán để dò trong Bảng đơn giá 3. Tiền giảm : Nếu Số lượng > 100 thì được giảm 5% 4. Thành tiền = (Số lượng * Đơn giá) - Tiền giảm 5. Tạo bảng thống kê Tổng thành tiền của từng tháng 6. Tính tổng số lượng của mặt hàng "Xi măng" bán trong tháng 1 Tháng BẢNG THEO DÕI CHI TIẾT BÁN HÀNG BẢNG ĐƠN GIÁ Mã hàng Tên hàng Đơn giá mỗi tháng (đồng/1kg) Bảng thống kê 123 BÀI TẬP 16: Số Ngày Mã chuyến Địa điểm Miễn giảm Thành tiền 1 15/07/2011 CH04 2 20/08/2011 CH02 3 01/07/2011 CH01 4 05/09/2011 CH03 5 01/08/2011 CH04 6 10/07/2011 CH03 Mã chuyến Địa điểm Loại xe Giá CH01 Nha Trang 52 15.000.000 đ CH02 Hà Nội 24 20.000.000 đ CH03 Đà Lạt 12 5.000.000 đ CH04 Vũng Tàu 52 10.000.000 đ Tháng Tổng số chuyến Tổng giá trị 7 8 9 Yêu cầu: 1. Dựa vào cột Mã chuyến và dò trong Bảng Phụ 2. Miễn Giảm: Dựa vào Đơn giá trong Bảng Phụ để tính. Nếu Mã chuyến là CH02 thì được giảm 10%, còn lại là không được giảm 3. Dựa vào Mã chuyến và Bảng Phụ để dò Đơn giá - Miễn giảm 4. Tính Tổng số chuyến của tháng 7, 8, 9 trong Bảng Thống Kê 7. Tính Tổng giá trị các chuyến trong tháng 7, 8,9 tại Bảng thống kê CÔNG TY DU LỊCH AN SÀI GÒN BẢNG PHỤ BẢNG THỐNG KÊ 124 BÀI TẬP 17: BẢNG TÍNH TIỀN ĐIỆN (Tháng 01/2016) Loại DS Chỉ số đầu Chỉ số cuối Hệ số Thành tiền Phụ trội Cộng KD 34 87 NN 58 400 TT 90 150 CN 150 700 KD 400 1500 NN 50 90 Yêu cầu: 1. Hệ số: Nếu Loại DS là NN thì hệ số 5 Nếu Loại DS là KD thì hệ số 4 Nếu Loại TT là KD thì hệ số 2,5 Còn lại là hệ số 2 2. Thành tiền = (Chỉ số cuối - Chỉ số đầu) * Hệ số 3. Phụ trội: Nếu (Chỉ số cuối - Chỉ số đầu)<=50 thì không tính Nếu (Chỉ số cuối - Chỉ số đầu) <=100 thì Phụ trội là 35% Thành tiền Còn lại tính 100% Thành tiền 4. Cộng = Thành tiền + Phụ trội 5. Lập Bảng thống kê Tổng số tiền của từng Loại DS 6. Trích lọc Loại DS là "KD" 125 BÀI TẬP 18: Yêu cầu: 1. Tên CLB: Dựa vào kí tự thứ 2, 3 của cột Mã CLB và dò trong Bảng Mã câu lạc bộ. 2. Tên Nước: Dựa vào kí tự đầu của cột Mã CLB và dò trong Bảng mã nước. 3. Giá vé: Dựa vào kí thứ 2,3 của cột Mã CLB và dò trong Bảng Mã câu lạc bộ. 4. Doanh thu = SL CĐV * Gía vé. 5. Lãi - Lỗ: Nếu kí tự cuối của cột Mã CLB là 1 và Doanh thu > 900000 hoặc kí tự cuối của cột Mã CLB là 2 và Doanh thu >600000 thì Lãi, còn lại là Lỗ. 6. Tính tất cả những ô còn trống ở Bảng thống kê.