Tài liệu học tập Tin ứng dụng trong kinh doanh chuẩn nhất

BỘ CÔNG THƢƠNG TRƢỜNG ĐẠI HỌC KINH TẾ KỸ THUẬT CÔNG NGHIỆP KHOA QUẢN TRỊ KINH DOANH

Tài liệu học tập

TIN ỨNG DỤNG TRONG KINH DOANH

HÀ NỘI, 2019

MỤC LỤC

LỜI GIỚI THIỆU ..................................................................................................... 1

CHƢƠNG I : GIỚI THIỆU CHUNG VỀ MICROSOFT EXCEL ......................... 3

1.1. Lập trang tính đơn giản ................................................................................ 3

1.1.1. Lập một trang tính đơn giản .......................................................................... 3

1.1.2. Định dạng và chỉnh trang dữ liệu .............................................................. 8

1.2. Các hàm thông dụng ......................................................................................... 43

1.2.1. Các hàm ngày tháng ...................................................................................... 43

1.2.2. Các hàm ký tự:............................................................................................... 55

1.2.3. Các hàm toán học:......................................................................................... 57 1.2.4. Các hàm thống kê .......................................................................................... 63 1.2.5. Các hàm về lôgic............................................................................................ 64 1.2.6. Các hàm về thông tin (kiểm tra): ................................................................. 66 1.2.7. Các hàm tìm kiếm và tham chiếu ................................................................. 67 1.2.8. Các hàm tài chính ......................................................................................... 75 CHƢƠNG 2 : CƠ SỞ DỮ LIỆU ............................................................................ 81

2.1. Khái niệm về Cơ sở dữ liệu............................................................................... 81

2.2. Các thao tác cơ bản trên Cơ sở dữ liệu ............................................................ 81

2.3. Sắp xếp trên Cơ sở dữ liệu: .............................................................................. 82

2.4. Tính tổng các nhóm (SubTotal ): ..................................................................... 84

2.5. Các hàm trên Cơ sở dữ liệu .............................................................................. 86

2.6. Đặt lọc dữ liệu: .................................................................................................. 92

2.6.1. Các thông số cơ bản để thực hiện việc lọc dữ liệu: ........................................ 92

2.6.2. Các kiểu lọc : .................................................................................................. 94

2.7. Tạo bảng tổng hợp Pivot Table ...................................................................... 101

2.7.1. Công dụng: ................................................................................................... 101

2.7.2. Các thông số cơ bản của PivotTable: .......................................................... 105

2.7.3. Tạo mới 1 bảng tổng hợp: ............................................................................ 106

2.7.4. Sửa đổi 1 bảng tổng hợp: ............................................................................ 107 2.7.5. Thay đổi hàm số tính toán: ......................................................................... 108 2.7.6. Tự động điều chỉnh bảng tổng hợp khi dữ liệu gốc thay đổi:.................... 108 2.8. Vẽ đồ thị .......................................................................................................... 108

2.8.1. Khái niệm về đồ thị: ..................................................................................... 108

2.8.2. Chèn đồ thị vào bảng tính. ........................................................................... 109

CHƢƠNG 3: MỘT SỐ TÍNH NĂNG CAO CẤP ................................................ 122

3.1. Tính năng Goal Seek: ..................................................................................... 122

3.2. Tính năng Solver: ........................................................................................... 131

3.2.1. Các thông số cơ bản của Solver: ................................................................. 131 3.2.2. Sử dụng chức năng Solver để giải các bài toán quản lý: ........................... 132 3.3. Một số bài toán tối ƣu trong quản trị ............................................................ 143

3.4. Một số bài toán về lao động, tiền lƣơng ........................................................ 144

DANH MỤC BẢNG BIỂU, SƠ ĐỒ, HÌNH VẼ

BẢNG

Bảng 1.1 : Thao tác chèn thêm 1 bảng tính .............................................................. 9

Bảng 1. 2 : Thao tác xóa bớt 1 bảng tính ........................................................................... 9 Bảng 1.3 : Thao tác đổi tên 1 bảng tính ............................................................................. 9 Bảng 1.4 : Thao tác sao chép/ di chuyển 1 bảng tính ........................................................ 10 Bảng 1.5: Các tách bảng tính ............................................................................................ 10 Bảng 1.6 : Thao tác ẩn và hiện lại bảng tính .................................................................. 11 Bảng 1.7 : Thao tác tạo khóa bảo vệ/ bỏ tình trạng bảng tính ....................................... 12 Bảng 1.8 : Các trƣờng hợp chọn bảng tính ..................................................................... 12 Bảng 1.9 : Xử lý dữ liệu dạng chuỗi trong bảng tính ....................................................... 13 Bảng 1.10 : Xử lý dữ liệu dạng số trong bảng tính .......................................................... 13 Bảng 1.11 : Xử lý dữ liệu dạng công thức trong bảng tính ............................................. 13 Bảng 1.12 : Xử lý dữ liệu dạng thời gian trong bảng tính .............................................. 14 Bảng 1.13 : Các phép toán trong công thức với dữ liệu số ............................................. 14 Bảng 1.14 : Các phép toán nối chuỗi ................................................................................ 15 Bảng 1.15 : Các phép toán so sánh .................................................................................. 15 Bảng 1.16 : bảng chuỗi ngày, tháng tăng .......................................................................... 17 Bảng 1.17 : Ví dụ dữ liệu kiểu công thức .......................................................................... 19 Bảng 1.18 : Ví dụ dữ liệu kiểu công thức .......................................................................... 19 Bảng 1.19 : Ví dụ dữ liệu kiểu công thức .......................................................................... 20 Bảng 1.20 : Ví dụ dữ liệu kiểu công thức .......................................................................... 20 Bảng 1.21 : Thao tác sửa, xóa dữ liệu ............................................................................... 21 Bảng 1.22 : Thao tác đánh dấu ( chọn) khối .................................................................... 21 Bảng 1.23 : Thao tác đánh dấu ( chọn) khối khi khối là miền liên tục ........................... 21 Bảng 1.24 : Thao tác đánh dấu ( chọn) khối khi khối là miền rời rạc ............................ 22 Bảng 1.25 : Thao tác copy, xóa, dán khối ........................................................................ 22 Bảng 1.26 : Thao tác copy, di chuyển khối dùng chuột ................................................... 23 Bảng 1. 27 : Thao tác xử lý ô, cột, hàng trong bảng tính ................................................. 24 Bảng 1. 28 : Thao tác chèn thêm cột , hàng , ô trong bảng tính ..................................... 24 Bảng 1. 29 : Thao tác xóa cột, hàng, ô trong bảng tính ................................................... 25 Bảng 1. 30 : Thao tác chuyển hàng thành cột và ngƣợc lại ............................................. 25 Bảng 1. 31 : Thao tác ẩn / hiện cột hàng.......................................................................... 26 Bảng 1. 32 : Thao tác định dạng dữ liệu ........................................................................... 28 Bảng 1. 33 : Định dạng số ................................................................................................. 29 Bảng 1.34 : Các thao tác định dạng số theo kiểu Việt Nam ............................................. 29 Bảng 1.35 : Các thao tác căn biên dữ liệu ........................................................................ 30 Bảng 1. 36 : Ý nghĩa của Horizontal ................................................................................ 32 Bảng 1. 37 : Ý nghĩa của Vertical ..................................................................................... 32 Bảng 1.38 : Các thông số khác của hộp thoại Format Cells ............................................. 32 Bảng 1. 39 : Các thao tác kẻ khung .................................................................................. 33 Bảng 1. 40 : Các thao tác tô màu ...................................................................................... 34 Bảng 1.41 : Các thao tác định dạng tự động ..................................................................... 35 Bảng 1.42 : Thao tác định dạng toàn bộ bảng tính , tạo một kiểu mới ........................... 36

Bảng 1.43 : Thao tác định dạng toàn bộ bảng tính , tạo một kiểu đã có ......................... 36 Bảng 1.44 : Tác dụng của đặt tên cho ô hay nhóm ô........................................................ 37 Bảng 1. 45 : Thao tác đặt tên cho ô hay nhóm ô bằng tay .............................................. 37 Bảng 1. 46 : Thao tác đặt tên theo tiêu đề của cột hay hàng (tự động) ........................... 38 Bảng 1. 47 : Thao tác về nhanh một ô (hay vùng) đã đƣợc đặt tên ................................. 39 Bảng 1. 48 : Thao tác xoá tên ............................................................................................ 40 Bảng 1. 49 : Thao tác ghi chú cho ô (Comment) .............................................................. 41 Bảng 1. 50 : Thao tác tạo bảo vệ cho ô ............................................................................. 42 Bảng 1.51 : Các chức năng cơ bản của auditing .............................................................. 42 Bảng 1.52 : Các thao tác phân tích và thống kê dữ liệu ................................................... 43 Bảng 1. 53 : Các quy tác sử dụng hàm ............................................................................. 44 Bảng 1.54 : Các loại địa chỉ ô ............................................................................................ 45 Bảng 1.55 : Ví dụ minh họa .............................................................................................. 46 Bảng 1.55 : Ví dụ minh họa .............................................................................................. 47 Bảng 1.56 : Thao tác minh họa ......................................................................................... 48 Bảng 1.57: Thao tác minh họa .......................................................................................... 50 Bảng 1.58 : Thao tác nhập hàm vào bảng tính ................................................................. 52 Bảng 1. 59 : Ý nghĩa của các nhóm hàm trong khung Category ...................................... 53 Bảng 1. 60 : Các hàm thời gian ......................................................................................... 54 Bảng 1. 61 : Các hàm ký tự ............................................................................................... 57 Bảng 1.62 : Các hàm toán học .......................................................................................... 58 Bảng 1.63 : Các hàm Loga ................................................................................................ 59 Bảng 1. 64 : Các hàm lƣợng giác ...................................................................................... 60 Bảng 1. 65 : Các hàm tính toán có điều kiện .................................................................... 60 Bảng 1.66 : Ví dụ minh họa .............................................................................................. 61 Bảng 1.67 : Ví dụ minh họa .............................................................................................. 62 Bảng 1.68 : Ví dụ minh họa .............................................................................................. 62 Bảng 1.69 : Ví dụ minh họa .............................................................................................. 63 Bảng 1. 70 : Các hàm thống kê ......................................................................................... 64 Bảng 1. 71 : Các hàm logic ................................................................................................ 65 Bảng 1. 72 : ví dụ minh họa .............................................................................................. 66 Bảng 1.73 :Các hàm về thông tin (kiểm tra) .................................................................... 67 Bảng 1. 74 : Các hàm tìm kiếm và tham chiếu ................................................................. 68 Bảng 1. 75 : Ví dụ minh họa các hàm tìm kiếm và tham chiếu ....................................... 69 Bảng 1. 76 : Ví dụ minh họa các hàm tìm kiếm và tham chiếu ....................................... 70 Bảng 1. 77 : Ví dụ minh họa các hàm tìm kiếm và tham chiếu ....................................... 72 Bảng 1. 78 : Ví dụ minh họa các hàm tìm kiếm và tham chiếu ....................................... 73 Bảng 1. 79 : Ví dụ minh họa các hàm INDEX.................................................................. 74 Bảng 1. 80 : Ví dụ minh họa các hàm INDEX.................................................................. 74 Bảng 1. 81 : Các hàm tài chính ........................................................................................ 78 Bảng 1. 83 : Thao tác sửa công thức mảng ....................................................................... 79 Bảng 1. 84 : Thao tác sửa công thức mảng ....................................................................... 79 Bảng 1. 85 : Thao tác chọn ( bôi đen ) 1 dãy mảng .......................................................... 79 Bảng 2.1. Bảng thông số của hộp thoại sửa cơ sở dữ liệu ................................................. 82 Bảng 2.2. Bảng các thông số hộp thoại Sort sắp xếp 1 CSDL .......................................... 83 Bảng 2.3. Bảng các thông số hộp thoại Subtotal .............................................................. 85

Bảng 2.4. Các hàm trên Cơ sở Dữ liệu ............................................................................. 88 Bẩng 2.5. Các bƣớc thực hiện vùng điều kiện trên CSDL ............................................... 90 Bảng 2.6. Các bƣớc thực hiện hàm Dsum ........................................................................ 90 Bảng 2.7. Các bƣớc thực hiện hàm Daverage .................................................................. 90 Bảng 2.8. Các bƣớc thực hiện hàm Dmin ......................................................................... 91 Bảng 2.9. Các bƣớc thực hiện hàm Dmax ........................................................................ 91 Bảng 2.10. Các bƣớc thực hiện hàm Dcount .................................................................... 92 Bảng 2.11. Các bƣớc thực hiện hàm Dcounta .................................................................. 92 Bảng 2.12. Các thông số của lọc 1 vùng CSDL ................................................................ 93 Bảng 2.13. Các nguyên tắc tạo lập vùng tiêu chuẩn trực tiếp ......................................... 93 Bảng 2.14. Các nguyên tắc tạo lập vùng tiêu chuẩn gián tiếp ......................................... 94 Bảng 2.15. Các bƣớc thực hiện lọc dữ liệu tự động ......................................................... 95 Bảng 2.16. Thông số của Menu lọc tự động...................................................................... 95 Bảng 2.17. Các bƣớc lọc tự động Autofilter khi chọn Custom ........................................ 96 Bảng 2.18. Các thông số trong hộp thoại lọc tự động Autofilter ..................................... 97 Bảng 2.19. Khôi phục lại dữ liệu ban đầu.......................................................................... 97 Bảng 2.20. Các bƣớc thực hiện lọc nâng cao..................................................................... 97 Bảng 2.21. Các thông số trong hộp thoại Advance Filter ................................................. 98 Bảng 2.22. Các bƣớc thực hiện lọc bằng Advace Filter .................................................. 100 Bảng 2.23. Các bƣợc thực hiện lọc Advanced Filter (vùng điều kiện gián tiếp) ........... 101 Bảng 2.24. Các bƣớc thực hiện Pivot Table .................................................................... 104 Bảng 2.25. Các thông số cơ bản của PivotTable: ........................................................... 106 Bảng 2.26. Các bƣớc thực hiện tạo mới 1 bảng Pivot Table ......................................... 107 Bảng 2.27. Các thông số khác khi sử dụng Option trong hộp thoại Pivot Table .......... 107 Bảng 2.28. Các bƣớc sửa 1 bảng tổng hợp ..................................................................... 108 Bảng 2.29. Điều chỉnh bảng tổng hợp khi dữ liệu gốc thay đổi ..................................... 108 Bảng 2.30. Doanh thu của các sản phẩm qua các năm .................................................. 109 Bảng 2.31. Các bƣớc thực hiện ghép dữ liệu trên nhiều bảng thành một .....................117 Bảng 2.32. Các bƣớc giải quyết ví dụ sử dụng tính năng Cốnlidate.............................. 120 Bảng 3.1. Các thao tác thực hiện Goal Seek ................................................................... 122 Bảng 3.2. Các bƣớc thực hiện tính năng Goal seek để tìm số tiền ban đầu cần gửi ..... 124 Bảng 3.3. Các bƣớc dùng tính năng Goal seek để tìm điểm hòa vốn ............................ 125 Bảng 3.4. Các bƣớc dùng tính năng Goal seek để tìm giá sản phẩm khi có sản lƣợng hòa vốn ......................................................................................................................................... 126 Bảng 3.5. Các bƣớc dùng tính năng Goal seek để đạt lợi nhuận mong muốn .............. 127 Bảng 3.6. Các bƣớc dùng tính năng Goal seek để tìm giá sản phẩm để đạt lợi nhuận mong muốn ................................................................................................................................ 127 Bảng 3.7 Các bƣớc thực hiện cách sử dụng Goal Seek để tìm điểm hoà vốn (ví dụ 4) . 129

Bảng 3.8. Các bƣớc thực hiện cách sử dụng Goal Seek để giải phƣơng trình ................ 131

Bảng 3.9. Các bƣớc thực hiện tính năng Solver ............................................................. 132 Bảng 3.10. Các điều kiện ràng buộc để sản xuất của bác Ba Phi .................................. 133 Bảng 3.11. Các bƣớc thực hiện dùng tính năng Solver Parameters trợ giúp nhập các thông số cho ví dụ 1 ....................................................................................................................... 136 Bảng 3.12. Số liệu thống kê vật tƣ còn lại trong kho...................................................... 136 Bảng 3.13. Các bƣớc thực hiện sử dụng Solver để bài toán sử dụng vật tƣ (ví dụ 2) ... 139 Bảng 3.14. Các bƣớc thực hiện sử dụng Solver để giải hệ phƣơng trình ...................... 143

Bảng 3.15. Bảng giá trị tham chiếu của biến X .............................................................. 143 Bảng 3.16. Bảng tham chiếu giờ chuẩn sản xuất áo ....................................................... 144 Bảng 3.18. Bảng tham chiếu giá và thuế ........................................................................ 148 Bảng 3.19. Thống kê các trị giá hàng theo mẫu bảng trên ............................................ 148

SƠ ĐỒ, HÌNH 10

Hình 1.1: Giới thiệu về cửa sổ bảng tính Excel ................................................................................... 5

Hình 1.2: Ý nghĩa 1 số biểu tƣợng trên thanh công cụ (ToolBar) ....................................................... 7

Hình 1.3: Ý nghĩa 1 số biểu tƣợng trên thanh định dạng (Formating) ............................................... 7

Hình 1.4: Ý nghĩa các thành phần của thanh công thức (Formula bar) ............................................. 8

Hình 1.5: Một ví dụ về việc chia tách bảng tính ................................................................................. 11

Hình 1.6: Tự tạo 1 kiểu danh sách ...................................................................................................... 18

Hình 1.7: Tính Lƣơng, và tỷ lệ % của từng ngƣời so với Tổng Lƣơng ............................................. 19

Hình 1.8: Chuyển cột thành hàng sử dụng tính năng Tranpose ........................................................ 26

Hình 1.9: Định dạng dữ liệu ............................................................................................................... 27

Hình 1.12: Định dạng dữ liệu.............................................................................................................. 30

Hình 1.13: Hộp thoại Format Cells trợ giúp căn chỉnh dữ liệu ......................................................... 31

Hình 1.14: Hộp thoại Format Cells trợ giúp kẻ đƣờng viền .............................................................. 33

Hình 1.15: Sử dụng tính năng định dạng tự động AutoFormat ........................................................ 35

Hình 1.16: Đặt tên cho vùng dữ liệu ................................................................................................... 36

Hình 1.17: Sử dụng hộp thoại Define Name đặt tên cho vùng dữ liệu............................................... 38

Hình 1.18: Sử dụng tên để dán vào công thức .................................................................................... 39

Hình 1.20: Hộp thoại Format Cells giúp bảo vệ dữ liệu .................................................................... 42

Hình 1.21: Báo cáo doanh thu tháng 5 năm 2007............................................................................... 45

Hình 1.22 : Các bƣớc tính doanh thu từng loại sản phẩm ................................................................. 46

Hình 1.23: Tính tổng doanh thu ......................................................................................................... 47

Hình 1.24 : Tính % theo tổng doanh thu............................................................................................ 48

Hình 1.26: Hộp thoại Insert Function trợ giúp nhập hàm ................................................................ 52

Hình 1.27: Hộp thoại Function Arguments trợ giúp nhập các đối số cho hàm ................................. 53

Hình 1.29: Bảng quy định xét duyệt hạn ngạch ................................................................................. 65

Hình 1.30: Bảng quy định xét thƣởng ................................................................................................ 68

Hình 1.31: Bảng quy định mức phạt .................................................................................................. 72

Hình 1.32: Bảng đơn giá sản phẩm .................................................................................................... 73

Hình 1.33: Cách thức sử dụng hàm index .......................................................................................... 74

Hình 1.34: Lập 1 công thức mảng ...................................................................................................... 78

Hình 2.2: Hộp thoại sửa đổi 1 CSDL .................................................................................................. 82

Hình 2.3: Hộp thoại Sort sắp xếp 1 CSDL ......................................................................................... 83

Hình 2.4: Tính tổng 1 nhóm (SubTotal) ............................................................................................. 84

Hình 2.5: Hộp thoại trợ giúp SubTotal .............................................................................................. 86

Hình 2.6: Kết quả trên bảng tính khi sử dụng chức năng SubTotal ................................................. 86

Hình 2.7: Sử dụng các hàm trên CSDL .............................................................................................. 88

Hình 2.8: Điền dữ liệu vào các vùng điều kiện ................................................................................... 89

Hình 2.9: Lọc tự động Autofilter ........................................................................................................ 94

Hình 2.10: Hộp thoại lọc tự động Autofilter khi chọn Custom .......................................................... 96

Hình 2.11: Hộp thoại lọc Advanced Filter .......................................................................................... 98

Hình 2.12: Minh hoạ cách sử dụng lọc Advanced Filter (vùng điều kiện trực tiếp) ......................... 99

Hình 2.13: Minh hoạ cách sử dụng lọc Advanced Filter (vùng điều kiện gián tiếp) ........................100

Hình 2.14: Minh hoạ cách tạo bảng tổng hợp Pivot Table ...............................................................102

Hình 2.15: Kết quả khi tạo 1 bảng tổng hợp Pivot Table .................................................................102

Hình 2.16: Hộp thoại Pivot Step1 ......................................................................................................104

Hình 2.17: Hộp thoại Pivot Step2 ......................................................................................................104

Hình 2.18: Hộp thoại Pivot Step3 ......................................................................................................105

Hình 2.19: Hộp thoại Layout để thực hiện kéo thả ...........................................................................105

Hình 2.20. Đồ thị doanh thu sản phẩm trong 3 năm .........................................................................109

Hình 2.21. Hộp thoại các kiểu đồ thị .................................................................................................110

Hình 2.22. Hộp thoại lựa chọn kiểu bố trí dữ liệu .............................................................................111

Hình 2.23. Hộp thoại hiển trị các thông số để vẽ đồ thị ....................................................................111

Hình 2.24. Hộp thoại thể hiện tiêu đề các trục ..................................................................................112

Hình 2.25. Hộp thoại hiện trục tọa độ ..............................................................................................112

Hình 2.26. Hộp thoại thuộc tính các lƣới kẻ ô ...................................................................................112

Hình 2.27. Hộp thoại thuộc tính chú giải: legend .............................................................................113

Hình 2.28. Hộp thoại thuộc tính chọn nhãn cho dữ liệu ...................................................................113

Hình 2.29. Hộp thoại thuộc tính khác hiển thị bảng dữ liệu .............................................................114

Hình 2.30. Hộp thoại để chèn đồ thị vào trang tính ..........................................................................114

Hình 2.31. Các bƣớc định sửa lại đồ thị ............................................................................................115

Hình 2.32. Hộp thoại định dạng lại font, màu cho đồ thị ..................................................................116

Hình 2.33. Hộp thoại thêm dữ liệu và đƣờng hồi quy vào đồ thị .....................................................116

Hình 2.34: Hộp thoại Consolidate trợ giúp tổng hợp dữ liệu ...........................................................118

Hình 2.35: Dữ liệu File TONGHOP ..................................................................................................118

Hình 2.36: Dữ liệu File PHANXUONG1 ...........................................................................................119

Hình 2.37: Dữ liệu File PHANXUONG2 ...........................................................................................119

Hình 2.38: Dữ liệu File PHANXUONG3 ...........................................................................................119

Hình 3.1: Hộp thoại Goal Seek ..........................................................................................................123

Hình 3.2: Minh hoạ cách sử dụng Goal Seek để tìm điểm hoà vốn (ví dụ 2) ....................................124

Hình 3.3: Minh hoạ cách sử dụng Goal Seek để tìm điểm hoà vốn (ví dụ 4) ....................................128

Hình 3.4: Minh hoạ cách sử dụng Goal Seek để giải phƣơng trình (ví dụ 5) ...................................130

Hình 3.6: Hộp thoại Solver Parameters trợ giúp nhập các thông số ................................................132

Hình 3.7: Minh hoạ cách sử dụng Solver để bài toán lập kế hoạch sản xuất (ví dụ 1) .......................134

Hình 3.8: Hộp thoại Solver Parameters trợ giúp nhập các thông số cho ví dụ 1 .............................134

Hình 3.9: Minh hoạ cách sử dụng Solver để bài toán sử dụng vật tƣ (ví dụ 2) ................................137

Hình 3.10: Hộp thoại Solver Parameters trợ giúp nhập các thông số cho ví dụ 2 ...........................137

Hình 3.12: Hộp thoại Solver Parameters trợ giúp nhập các thông số cho ví dụ 3 ...........................141

Hình 3.13. Minh họa dữ liệu cho bài 1...............................................................................................144

Hình 3.14. Minh họa dữ liệu cho bài 2...............................................................................................145

Hình 3.15. Minh họa dữ liệu cho bài 3...............................................................................................146

Hình 3.16. Minh họa dữ liệu cho bài 4...............................................................................................147

Hình 3.17. Minh họa dữ liệu cho bài 5...............................................................................................148

Hình 3.18. Minh họa dữ liệu cho bài 6...............................................................................................149

Hình 3.19. Minh họa dữ liệu cho bài 7...............................................................................................149

Hình 3.20. Minh họa dữ liệu cho bài 8...............................................................................................150

DANH MỤC TỪ VIẾT TẮT

Từ viết tắt

Giải nghĩa

KHKT

: Khoa học kỹ thuật

DN

: Doanh nghiệp

: Trách nhiệm hữu hạn

TNHH

CSDL : Cơ sở dữ liệu

DL : Dữ liệu

Sheet : Bảng tính

Table : Bảng

VN : Việt Nam

LỜI GIỚI THIỆU

Kinh doanh là 1 hoạt động đòi hỏi đƣợc quản lý một cách khoa học, để có thể thực hiện tốt công việc kinh doanh ngƣời quản trị phải có các kỹ năng cơ bản đó là phân tích và ra quyết định. Nhƣ vậy, việc phân tích các thông tin đóng vai trò then chốt để đƣa ra các quyết định kinh doanh hiệu quả.

Cách đây chƣa lâu, do các công cụ trợ giúp tự động chƣa ra đời, việc phân tích các thông tin kinh doanh đa phần đều đƣợc làm bằng thủ công và dựa trên kinh nghiệm là chủ yếu, khi lƣợng thông tin cần xử lý là lớn, hoặc phức tạp sẽ khiến cho ngƣời quản trị mất rất nhiều thời gian để xử lý mà hiệu quả công việc cũng không cao.

Ngày nay, với sự phát triển nhanh chóng của KHKT, các công cụ trợ giúp cho công việc kinh doanh ra đời trong đó có máy vi tính, cùng với máy vi tính là 1 loạt các phần mềm ứng dụng phục vụ cho các chuyên ngành nhƣ quản trị, tài chính, kỹ thuật, y học ...và các phần mềm đó chất lƣợng ngày càng cao. Riêng với lĩnh vực quản trị thì nổi bật nhất là Microsoft Excel nhờ các tính năng mạnh mẽ của nó, hiệu quả của Excel là không thể phủ nhận thể hiện qua sự ứng dụng rộng rãi của phần mềm này trong văn phòng của các công ty tại Việt Nam.

Hầu hết các chuyên gia đều nhận định rằng mọi ngƣời đều không sử dụng quá 20% các tính năng mà Excel cung cấp. Các chức năng cơ bản của Excel là tính toán dữ liệu trên các bảng, quản lý các cơ sở dữ liệu, vẽ đồ thị. Bên cạch các chức năng truyền thống đó, Excel còn có 1 loạt các chức năng đặc biệt hơn là tạo bảng tổng hợp, tạo báo cáo theo tình huống, phân tích và đánh giá số liệu. Tất cả các chức năng đó đều rất có ích cho ngƣời quản trị trong việc phân tích các thông tin kinh doanh.

Để đáp ứng với yêu cầu học tập của sinh viên chuyên ngành Quản trị kinh doanh, trƣờng Đại học Kinh tế - Kỹ thuật công nghiệp tổ chức biên soạn tài liệu học tập „‟Tin ứng dụng trong kinh doanh‟‟. Đây là một học phần cơ bản của sinh viên chuyên ngành Quản trị. Học phần cung cấp những kiến thức cơ bản về Tin học mà cụ thể là Excel để từ đó giúp cho sinh viên vận dụng vào thực tế để đạt đƣợc hiệu quả phân tích kinh doanh cao nhất.

Tài liệu đƣợc biên soạn theo đúng chƣơng trình đào tạo và các quy định về cách trình

bày của Nhà trƣờng. Kết cấu của tài liệu bao gồm 3 chƣơng, nội dung chủ yếu nhƣ sau:

Chƣơng 1: Giới thiệu chung về Microsoft Excel

Chƣơng 2: Cơ sở dữ liệu

Chƣơng 3: Một số tính năng cao cấp

Tài liệu học tập đƣợc thực hiện bởi tập thể giảng viên môn Tin ứng dụng trong kinh doanh khoa Quản trị kinh doanh, Đại học Kinh tế - Kỹ thuật công nghiệp. Cụ thể là: Ths Hoàng Hiếu Thảo (chủ biên), Ths Mai Hoàng Thịnh, Ths Nguyễn Thị Thanh Hoa, Ths Đỗ Thị Hƣờng, Ths Nguyễn Văn Kỷ.

Do thời gian và trình độ có hạn, nên tài liệu khó có thể tránh khỏi những thiếu sót nhất định. Chúng tôi luôn mong nhận đƣợc sự góp ý của bạn đọc để tài liệu học tập có thể hoàn thiện hơn.

Xin trân trọng cảm ơn!

T/M TẬP THỂ TÁC GIẢ

Hoµng HiÕu Th¶o

CHƢƠNG I : GIỚI THIỆU CHUNG VỀ MICROSOFT EXCEL

MỤC ĐÍCH CỦA CHƢƠNG:

Sau khi nghiên cứu và học tập chƣơng này sinh viên nắm đƣợc:

 Các chức năng cơ bản của bảng tính.

 Các thao tác định dạng trên bảng tính.

 Phân biệt đƣợc địa chỉ tƣơng đối và địa chỉ tuyệt đối.

 Cách sử dụng các hàm cơ bản.

NỘI DUNG CHƢƠNG

1.1. Lập trang tính đơn giản

1.1.1. Lập một trang tính đơn giản

1.1.1.1. Giới thiệu về Microsoft Excel:

Phần mềm Microsoft Excel là sản phẩm tiêu biểu nằm trong bộ Microsoft Office nổi

tiếng của hãng Microsoft. Microsoft Excel đƣợc coi là mạnh nhất trong tất cả các phần

mềm tƣơng tự nhƣ Lotus, Quattro... và rất phù hợp với các công việc văn phòng, quản trị

 Tạo lập và tính toán trên các bảng tính (Spreadsheet).

 Quản lý các cơ sở dữ liệu đơn giản (Simple database).

 Thống kê và tạo lập các biểu đồ, đồ thị (Graph).

dữ liệu của các công ty. Ba chức năng cơ bản nhất của Microsoft Excel là:

Bên cạnh những chức năng truyền thống của một bảng tính thông thƣờng, Microsoft

Excel còn có một loạt các chức năng đặc biệt hơn các phần mềm khác là các chức năng tạo

bảng tổng hợp (Pivot Table), tạo báo cáo theo tình huống (Scenario manager), phân tích và

đánh giá số liệu (Goal seek, Solver manager) ...Các công cụ nâng cao này sẽ đƣợc tiến hành

trong thực tập nâng cao trên bộ số liệu các bạn đƣợc thu thập khi đi doanh nghiệp.

1.1.1.2. Khởi động

 Nếu dùng Win 98, nháy vào Start / Programs / Microsoft Excel.

 Nếu dùng Win XP, chọn Start /All Programs /Microsoft Office /Microsoft Office

Sau khi khởi động Windows làm theo một trong các cách sau:

Excel 2003.

1.1.1.3. Màn hình

* Các thành phần của màn hình Excel:

Màn hình của Excel là một cửa sổ đã đƣợc phóng to và trông gần giống màn hình của

 Thanh Tiêu đề (Title bar): ở dòng trên cùng của màn hình, khi mới khởi động

Word, bao gồm các thành phần sau :

Excel tại đây ghi Microsoft Excel - Book1, khi ta đặt tên cho bảng tính, tên này kèm theo

 Các thanh Menu, Công cụ, Định dạng giống nhƣ của Word. Phần lớn các biểu

phần mở rộng .xls sẽ thay thế từ Book1.

tƣợng trên các thanh này có công dụng ý nghĩa nhƣ trong Word, ý nghĩa của một số biểu

 Thanh Công thức (Formula bar): Là dòng thứ năm của màn hình hiển thị toạ độ

tƣợng dùng riêng cho Excel đƣợc giải thích ở phần dƣới.

(địa chỉ hoặc tên) ô, nút huỷ bỏ, nút lựa chọn, nội dung dữ liệu trong ô hiện tại (ô có khung

 Thanh Trạng thái (Status bar): Là dòng cuối cùng hiển thị các chế độ hoạt động

viền chung quanh).

- Ready: Đang sẵn sàng làm việc.

- Enter: Đang nhập dữ liệu hay công thức.

- Pointer: Đang ghi 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 tại.

 Thanh thẻ tên bảng tính (Sheet tabs): là dòng ngay trên thanh trạng thái, hiển thị

của Excel :

tên của các bảng tính (khi chúng chƣa đƣợc đặt tên, tại đây ghi (Sheet1, Sheet2, ..., Sheet7).

 Thanh cuộn Dọc (Vertical Scroll Bar), cuộn Ngang (Horizontal Scroll Bar):

Bên trái là các nút chuyển tới.

 Cửa sổ Bảng tính (Worksheet Window): là phần lớn nhất dùng để nhập dữ liệu,

giống nhƣ trong Word.

tính toán, vẽ đồ thị nhƣ sau:

Nh¾p chuét vµo ®©y ®Ó chän toµn bé b¶ng tÝnh

Tªn c¸c cét

Sè thø tù c¸c hµng

¤ chuÈn bÞ nhËp d÷ liÖu

Nót chuyÓn tíi, lui b¶ng tÝnh

B¶ng tÝnh hiÖn t¹i

Hình 1.1: Giới thiệu về cửa sổ bảng tính Excel

 Cột (Column): Là tập hợp các ô trong bảng tính theo chiều dọc đƣợc đánh thứ tự

1.1.1.4. Các thành phần của cửa sổ Bảng tính:

bằng chữ cái (từ trái sang phải bắt đầu từ A, B, C, ... AA, AB đến IV, tổng số có 256 cột).

 Hàng (Row): Là tập hợp các ô trong bảng tính theo chiều ngang đƣợc đánh thứ tự

Ngoài cùng bên trái là nút chọn (đánh dấu khối) toàn bộ bảng tính.

 Ô (Cell): Là giao của một cột và một hàng. Địa chỉ của ô đƣợc xác định bằng cột

bằng số từ 1 đến 16.384.

 Ô hiện tại: Là ô có khung viền chung quanh với một chấm vuông nhỏ ở góc phải

trƣớc, hàng sau, ví dụ C4, AB25.

dƣới (Mốc điền) hay còn gọi là Con trỏ ô (sau đây gọi tắt là con trỏ). Toạ độ của ô này đƣợc

 Con trỏ bàn phím: Là vạch đứng | nhấp nháy để biểu thị vị trí ký tự sẽ đƣợc chèn

hiển thị trên thanh công thức.

vào.

 Con trỏ chuột có các dạng sau:

- Dấu | : Dùng để đƣa con trỏ ô về vị trí nào đó.

- Dấu : Dùng để chọn lệnh, biểu tƣợng, vẽ hình hoặc cuộn bảng tính.

1.1.1.5. Dịch chuyển con trỏ ô trong bảng tính:

 Trỏ chuột vào ô cần chuyển tới, bấm nút trái.

 Ấn các phím mũi tên: Chuyển tới các hàng, cột lân cận.

- PgUp, PgDn : Lên hoặc xuống một màn hình.

- Home: Về ô A1.

- Tab: Sang phải một màn hình

- Shift + Tab: Sang trái một màn hình. 

- End + Home: Đến ô cuối cùng của bảng tính.

 F5, địa chỉ ô, : Về ô đó, ví dụ để về ô H22, ta ấn phím F5, gõ H22 rồi ấn 

 Chú ý: Các thao tác trên chỉ thực hiện đƣợc khi chọn lệnh Tools, Option, Transition,

Theo một trong các cách sau :

kích chọn ở tuỳ chọn Transition Navigation Keys.

1.1.1.6. Ra khỏi Excel:

 Chọn File/Exit hoặc ấn Alt + F4.

 Nháy đúp chuột tại dấu nhân (X) ở góc trái trên của màn hình để trở về Windows.

Theo một trong các cách sau :

AutoSum: TÝnh tæng

Sort Acsending: S¾p xÕp t¨ng

Xem tr íc khi in

Help: Trî gióp

Sort Decsending: S¾p xÕp gi¶m

Chart Wizard: VÏ ®å thÞ

Ghi file Excel ®ang lµm vµo æ ®Üa

Më 1 file Excel cã s½n

Drawing: VÏ mét h×nh

T¹o míi 1 file Excel

Zoom control: Phãng to, thu nhá cöa sæ b¶ng tÝnh

Chän cì Font vµ kiÓu ch÷ ®Ëm, nghiªng, g¹ch ch©n

Font Color: MÇu ch÷

Hîp nhÊt nhiÒu « thµnh 1 « duy nhÊt

Chän kiÓu Font

Color: Chän mÇu nÒn cho «

Currency Style: §iÒn dÊu tiÒn tÖ

Decrease Decimal: Bít ®i 1sâ thËp ph©n

Percent Style: Nh©n víi 100 vµ ®iÒn dÊu %

Borders: Chän kiÓu ® êng viÒn cho «

Increase Decimal: T¨ng thªm 1sâ thËp ph©n

Comma Style: DÊu ph©n c¸ch hµng ngh×n

Hình 1.2: Ý nghĩa 1 số biểu tƣợng trên thanh công cụ (ToolBar)

Hình 1.3: Ý nghĩa 1 số biểu tƣợng trên thanh định dạng (Formating)

Chän kiÓu hµm

N¬i thÓ hiÖn néi dung cña « ®ang nhËp d÷ liÖu

Nót gäi hµm

Huû néi dung võa gâ

Xem kÕt qu¶

Hình 1.4: Ý nghĩa các thành phần của thanh công thức (Formula bar)

1.1.2. Định dạng và chỉnh trang dữ liệu

 Chọn biểu tƣợng Save trên thanh công cụ hoặc mục File / Save.

 Nếu đây là lần đầu tiên thực hiện thao tác này với bảng tính, ta phải gõ vào tên cho bảng tính trong ô File Name, Excel sẽ tự gán kiểu đuôi là .xls cho nó. Tên bảng tính sẽ xuất hiện trên thanh tiêu đề của cửa sổ. Sau đó trong quá trình làm việc, ta thƣờng xuyên ghi bảng tính lên đĩa bằng cách trên mà không cần đặt tên cho nó.

 Nếu ta cần lƣu giữ bảng tính với tên khác, chọn mục File /Save As và đặt tên mới

1.1.2.1. Lưu (ghi) bảng tính lên đĩa:

cho nó.

 Chọn biểu tƣợng Open hoặc mục File/Open. Xuất hiện hộp thoại Open với danh sách các bảng tính trong khung File Name đƣợc xếp theo thứ tự A,B,C. Ta chọn tệp cần thiết rồi chọn OK. Nếu bảng tính ta cần lại ở trên đĩa khác hoặc thƣ mục khác, ta chọn đĩa từ ô Drives, nháy đúp tại thƣ mục cần thiết của khung Directories.

 Excel còn có cách mở bảng tính khác: Chọn mục File, xuất hiện Menu dọc với danh sách những File mới làm gần nhất ở phía dƣới. Ta chọn tệp cần thiết từ danh sách này. Danh sách các bảng tính này có thể nhiều hơn tuỳ thuộc vào ngƣời cài đặt.

1.1.2.2. Mở bảng tính đã có trên đĩa:

 Trƣớc khi chuyển sang bảng tính khác hoặc làm việc khác, phải ghi tệp lên đĩa sau đó mới đóng nó bằng cách chọn mục File /Close. Nếu quên chƣa ghi tệp lên đĩa, Excel sẽ hỏi :

1.1.2.3. Đóng bảng tính:

- Chọn Yes để ghi lại, No để không ghi những thay đổi vừa tạo ra cho bảng tính.

Do you want to save change to .xls ?

1.1.2.4. Chèn thêm 1 bảng tính (thêm sheet)

Các cách Thao tác

Cách 1 Insert / Worksheet

Cách 2

Nháy nút phải chuột trên thanh thẻ tên bảng tính để gọi Menu tắt (sau đây chúng ta quy ƣớc gọi thao tác này là [Menu tắt]), chọn Insert Worksheet

Cách 3 Tools/Option/ tab General/ Sheets in new workbook/ Gõ số lƣợng sheet mong muốn vào, giả sử 50 

Bảng 1.1 : Thao tác chèn thêm 1 bảng tính

1.1.2.5. Xoá bớt 1 bảng tính

Các cách Thao tác

Cách 1 Edit/ Delete Sheet

Cách 2 [Menu tắt], Delete Sheet

Bảng 1. 2 : Thao tác xóa bớt 1 bảng tính

1.1.2.6. Đổi tên bảng tính

Các cách Thao tác

Cách 1 Nháy đúp vào thẻ tên (tức là vào tên bảng tính, sau đây chúng ta quy ƣớc gọi là thẻ tên) trên thanh thẻ tên, gõ vào tên mới.

Cách 2 Format /Sheet /Rename, gõ vào tên mới.

Cách 3 [Menu tắt], Rename, gõ vào tên mới

Bảng 1.3 : Thao tác đổi tên 1 bảng tính

1.1.2.7. Sao chép / Chuyển 1 bảng tính:

Các cách Thao tác

Giữ Ctrl trong khi kéo thả thẻ tên tại một thẻ tên khác (Sheet khác). Cách 1 Nếu không giữ Ctrl bảng tính sẽ đƣợc chuyển đi.

Edit, Move or Copy Sheet. Chọn vị trí đặt bảng tính hiện tại trƣớc bảng

tính nào trong khung Before Sheet. Nếu đánh dấu chọn vào Creat a Cách 2

Copy, Excel sẽ sao chép bảng tính chứ không chuyển nó.

Lƣu ý: Chỉ dùng cách 2 nếu bảng tính nguồn và đích cách xa nhau (không thấy thẻ

Bảng 1.4 : Thao tác sao chép/ di chuyển 1 bảng tính

tên của chúng cùng một lúc).

1.1.2.8. Tách bảng tính:

Các cách Thao tác

Trỏ chuột vào thanh tách cho xuất hiện mũi tên 2 đầu, kéo thả nó tại vị Cách 1 trí cần tách.

Đƣa con trỏ ô về vị trí cần tách, chọn mục Window / Split. Sau đó để bỏ Cách 2 tách chọn Window / Remove Split

Đƣa con trỏ ô về vị trí cần tách, chọn mục Window / Freeze Panes. Sau Cách 3 đó để bỏ tách chọn Window / Unfreeze Panes

Lƣu ý: Việc tách bảng tính ra có ý nghĩa rất quan trọng, khi ta làm việc với những bảng tính có nhiều hàng, nhiều cột có những hàng hay cột ta luôn muốn quan sát để tiện làm việc thì ta phải sử dụng tính năng chia tách bảng tính

Bảng 1.5: Các tách bảng tính

Hình 1.5: Một ví dụ về việc chia tách bảng tính

1.1.2.9. Ẩn và hiện lại 1 bảng tính

Mục đích Thao tác

Ẩn bảng tính Chọn Format/Sheet/Hide để ẩn bảng tính.

Chọn Format /Sheet /Unhide.

Hiện lại bảng tính đã bị ẩn

Bảng 1.6 : Thao tác ẩn và hiện lại bảng tính

1.1.2.10. Bảo vệ bảng tính:

Để bảo vệ và che giấu những thông tin quan trọng trong bảng tính cần bảo vệ bảng 11

tính. Cách làm nhƣ sau:

Mục đích Thao tác

Tool / Protection

Chọn Protect Sheet để bảo vệ bảng tính, chọn Protect Workbook để bảo vệ file Excel. Bảo vệ

Nếu cần thiết gõ mật khẩu vào vùng Password, 2 lần gõ phải giống nhau và lƣu ý rằng mật khẩu trong Excel phân biệt chữ hoa với chữ thƣờng

Bỏ tình trang bảo vệ Chọn Tool / Protection / Unprotect Sheet hay Unprotect Workbook. Nếu có mật khẩu, phải gõ vào, nếu đúng ta mới cập nhật đƣợc bảng tính.

Bảng 1.7 : Thao tác tạo khóa bảo vệ/ bỏ tình trạng bảng tính

1.1.2.11. Chọn nhiều bảng tính

Trƣờng hợp Thao tác

Các bảng tính liền kề Nháy chuột vào thẻ tên đầu, giữ Shift trong khi nháy chuột vào thẻ tên cuối.

Giữ Ctrl trong khi lần lƣợt nháy chuột vào các thẻ tên. Các bảng tính cách nhau

Giữ Ctrl trong khi nháy chuột vào thẻ tên của bảng tính đó.

Để bỏ việc chọn một bảng tính nào

Bảng 1.8 : Các trƣờng hợp chọn bảng tính

1.1.2.12. Xử lý dữ liệu trong bảng tính:

Trong mỗi ô chỉ có thể chứa một kiểu dữ liệu. Kiểu dữ liệu của ô phụ thuộc vào ký tự

đầu tiên gõ vào. Các kiểu dữ liệu trong một ô đƣợc phân ra nhƣ sau :

- Dạng chuỗi (Text):

Ký hiệu ý nghĩa

a đến z hoặc A đến Z Các chữ cái

Bắt đầu bằng dấu nháy đơn (') Số điện thoại, số nhà, mã số...vv khi nhập vào phải bắt đầu bằng dấu nháy đơn („) và không có giá trị tính toán.

 Theo mặc định, dữ liệu dạng chuỗi đƣợc căn sang trái ô.

Bảng 1.9 : Xử lý dữ liệu dạng chuỗi trong bảng tính

- Dạng số (Number):

Ký hiệu ý nghĩa

Các số từ 0 đến 9 Các chữ số

Các dấu +, - , (, *, $ Biểu diễn về giá trị âm, dƣơng, tiền tệ

 Theo mặc định, dữ liệu dạng số đƣợc căn sang phải ô.

Bảng 1.10 : Xử lý dữ liệu dạng số trong bảng tính

 Bắt đầu bởi các dấu = hoặc +. Sau khi ấn công thức nhập vào chỉ thể hiện trên thanh công thức còn kết quả của nó đƣợc thể hiện trong ô. Nếu thấy các thông báo sau, lý do là:

- Dạng công thức (Formulas):

Thông báo Lý do

##### Cột quá hẹp

#DIV/0? Lỗi khi chia 1 số cho số 0

#NAME? Thực hiện phép tính với một biến không xác định (tên không gắn với một ô hay một vùng nào cả)

#N/A Tham chiếu đến một ô rỗng hoặc không có trong danh sách

#VALUE? Sai về kiểu của toán hạng (giả sử nhƣ chia 1 số cho 1 dữ liệu là chữ)

Bảng 1.11 : Xử lý dữ liệu dạng công thức trong bảng tính

- Dạng Ngày (Date), Giờ (Time): Trong cách trình bày dƣới đây :

Ký hiệu Ý nghĩa Ví dụ Kết quả

DD Là 2 con số chỉ Ngày 14 Ngày 14

MM Là 2 con số chỉ Tháng 11 Tháng 11

YY Là 2 con số chỉ Năm 07 Năm 2007

MM/DD/YY Nhập theo kiểu Anh, Mỹ 11/14/07 Tháng 11 ngày 14 năm 2007

14/11/07 DD/MM/YY Ngày 14 tháng 11 năm 2007 Nhập theo kiểu Việt, Pháp

Ctrl + ; Cho ta ngày tháng hiện tại

Ctrl + Shift + ; Cho ta giờ hiện tại

Theo mặc định, dữ liệu dạng ngày tháng đƣợc căn sang phải ô. Nếu nhập sai (không theo đúng định dạng ngày tháng) thì Excel sẽ căn dữ liệu sang bên trái ô và ta không thể dùng dữ liệu này để tính toán. Có thể nhập ngày bằng cách = DATE(YY,MM,DD), đây là cách nhập ngày tốt nhất. Để nhập dữ liệu ngày tháng theo kiểu Việt Nam ta thực hiện các bƣớc nhƣ sau:

- Control Panel /Regional Option /Regional/ Chọn French /OK

Bảng 1.12 : Xử lý dữ liệu dạng thời gian trong bảng tính

1.1.2.13. Các toán tử (phép toán) trong công thức

- Các phép toán với dữ liệu số:

Ký hiệu Ý nghĩa Ví dụ Kết quả

+ Phép cộng 10+5 15

- Phép trừ 10-5 5

* Phép nhân 10*5 50

/ Phép chia 10/5 2

^ Luỹ thừa 5^2 25

% Lấy phần trăm 50% 0,5

 Thứ tự ƣu tiên của các phép toán nhƣ sau: Luỹ thừa trƣớc rồi đến nhân chia và sau cùng mới đến cộng trừ. Các phép toán cùng mức ƣu tiên (nhƣ nhân chia hoặc cộng trừ) thực hiện từ trái sang phải. Muốn thay đổi thứ tự ƣu tiên, dùng các cặp ngoặc tròn, toán tử trong cặp ngoặc ở sâu nhất sẽ đƣợc thực hiện trƣớc.

Bảng 1.13 : Các phép toán trong công thức với dữ liệu số

 Ví dụ: Các ô A1, B1, C1 chứa các số 2,3, 4, nếu trong ô D1 gõ =A1+B1*C1 sẽ đƣợc

kết quả 14, gõ =(A1+B1)*C1 sẽ đƣợc kết quả 20.

- Phép toán nối chuỗi (toán tử &):

Ký hiệu Ý nghĩa Ví dụ Kết quả

& Nối các chuỗi lại thành 1 chuỗi ="Quản trị "&"kinh doanh" “Quản trị kinh doanh“

Bảng 1.14 : Các phép toán nối chuỗi

- Phép toán so sánh

Ký hiệu Ý nghĩa Ví dụ Kết quả

> Lớn hơn 14>13 True

>= Lớn hơn hoặc bằng 13>=14 False

<> Khác 13<>14 True

< Nhỏ hơn 14<13 False

<= Nhỏ hơn hoặc bằng 13<=14 True

Bảng 1.15 : Các phép toán so sánh

1.1.2.14. Nhập dữ liệu:

 Đƣa con trỏ ô về ô cần nhập.

 Nhập dữ liệu theo mong muốn.

 Để kết thúc việc nhập dữ liệu trong 1 ô, ta làm theo một trong các cách sau:

- Ấn phím Enter, con trỏ ô sẽ xuống ô dƣới.

- Ấn một phím mũi tên để đƣa con trỏ ô sang ô cần thiết, ví dụ ấn phím  sẽ đƣa con

* Dữ liệu bất kỳ:

- Kích chọn nút X ( (màu xanh lá cây) trên thanh công thức).

- Trỏ chuột vào ô cần tới, ấn nút trái.

trỏ ô sang bên phải, ấn phím  (sẽ đƣa con trỏ sang bên trái.)

 Đánh dấu khối miền cần điền dữ liệu (ví dụ A1:B5)

 Gõ vào dữ liệu (ví dụ số 2007)

 Kết thúc việc nhập bằng cách ấn phím Ctrl + 

* Dữ liệu trong các ô giống nhau:

Kết quả là: Miền A1:B5 sẽ đƣợc điền kín bởi số 2007

 Chuỗi số với bƣớc nhảy là 1:

- Đƣa con trỏ về ô đầu tiên của miền, gõ vào số bắt đầu, ví dụ để đánh số thứ tự cho

* Dữ liệu trong các ô tuân theo một quy luật

- Trỏ chuột vào mốc điền cho xuất hiện dấu + màu đen, giữ phím Ctrl trong khi kéo và

một số ô bắt đầu từ 1, ta gõ 1, xuống ô tiếp theo gõ số 2.

thả chuột tại ô cuối của miền (từ đây về sau ta gọi thao tác này là điền tự động AutoFill). Kết

 Chuỗi số với bƣớc nhảy bất kỳ:

- Đƣa con trỏ về ô đầu tiên của miền, gõ vào số bắt đầu, ví dụ để có chuỗi có số tt là 1,

quả ta đƣợc chuỗi số 1, 2, 3, 4...

- Đƣa con trỏ chuột về góc dƣới cùng bên phải, vừa kéo vừa nhấn phím phải xuống

3, 5,… ta gõ số 1

- Nếu muốn bƣớc nhảy là 1 thì ta chọn Fill series từ danh sách.

- Còn nếu muốn chọn bƣớc nhảy bất kỳ thì ta chọn Series từ danh sách thả ra và sau đó

dƣới khi tới ô cuối thì thả ra.

 Chuỗi ngày tháng tăng:

- Đƣa con trỏ về ô đầu tiên của miền, gõ vào ngày tháng năm bắt đầu.

- Trỏ chuột vào mốc điền cho xuất hiện dấu +, bấm và giữ nút phải, kéo thả tại ô cuối

ta điền bƣớc nhảy vào ô tƣơng ứng.

miền. Khi đó sẽ xuất hiện 1 menu, ta sẽ chọn:

Chọn Ý nghĩa Ví dụ Kết quả

15/04/2007,

16/04/2007, Fill Days Để tăng 1 ngày 15/04/2007

17/04/2007

15/04/2007, Fill 15/05/2007, Để tăng 1 tháng 15/04/2007 Months 15/06/2007

15/04/1999, Fill 15/05/2000, Để tăng 1 năm 15/04/2007 Years 15/06/2001

15/04/2007, Chọn step value =2 17/04/2007, Series Tự chọn bƣớc nhảy (ngày) 19/04/2007

 Điền một danh sách tự tạo : Không phải lúc nào việc điền tự động vào các ô kế tiếp cũng thoả mãn ngƣời dùng. Do vậy, trong Excel cung cấp cho chúng ta 1 phƣơng pháp để tự tạo danh sách theo ý ngƣời dùng. Giả sử khi ta muốn các ô có giá trị Thứ Hai, Thứ Ba ... Chủ Nhật ta phải làm nhƣ sau :

- Nếu danh sách này chƣa có thì phải tạo bằng cách :

- Tools / Option / Custom List

- Trong khung List Entries lần lƣợt nhập các giá trị cho danh sách, hết mỗi giá trị bấm

Bảng 1.16 : bảng chuỗi ngày, tháng tăng

 để xuống dòng. Ví dụ: Thứ Hai  Thứ Ba . Cuối cùng ta đƣợc nhƣ hình sau:

- Chọn OK

- Để sử dụng:

- Nhập một giá trị có trong danh sách tự tạo vào ô đầu tiên.

- Điền tự động tới ô cuối miền (xem cách điền tự động ở trên).

Hình 1.6: Tự tạo 1 kiểu danh sách

 Phải bắt đầu bởi dấu = hoặc các dấu +, -

 Khi cần lấy số liệu ở ô nào nháy chuột vào ô đó hoặc gõ luôn địa chỉ của ô đó vào.

* Dữ liệu kiểu công thức (nhập công thức hoặc hàm có sẵn để tính toán)

Ví dụ: Để tính Lƣơng theo công thức: Lƣơng = [Số ngày công] x [Tiền 1 ngày].

Thao tác Giải thích Các bƣớc

1 Chọn miền E2:E7 Vì công thức tính trong miền giống nhau: đều lấy số ở cột C nhân với số ở cột D

2 Gõ vào dấu =

3 Trỏ chuột vào ô C2, ấn nút

trái (hoặc gõ C2)

4 Gõ dấu *

5 Trỏ chuột vào ô D2, ấn nút trái (hoặc gõ D2) Tại ô E2 và thanh công thức xuất hiện = C2*D2

6 ấn Ctrl +  Tại các ô từ E2 đến E7, Excel cho kết quả tính Lƣơng của từng ngƣời

Bảng 1.17 : Ví dụ dữ liệu kiểu công thức

Ta cũng có thể làm nhƣ sau:

Thao tác Giải thích Các bƣớc

1 Bỏ qua bƣớc 1

2 Thực hiện các bƣớc từ 2 đến 5, ấn 

3 Đƣa con trỏ về ô E2, kéo xuống ô E7 để Excel điền tự động công thức cho tới ô E7 Để tính Lƣơng cho những ngƣời còn lại

Bảng 1.18 : Ví dụ dữ liệu kiểu công thức

Hình 1.7: Tính Lƣơng, và tỷ lệ % của từng ngƣời so với Tổng Lƣơng

 Tính tỷ lệ phần trăm Lƣơng của từng ngƣời so với Tổng Lƣơng:

Thao tác Các bƣớc

1 Đƣa con trỏ về ô F2

Để tính Tỷ lệ cho những ngƣời còn lại, gõ vào dấu =, dùng chuột chọn ô E2 (hoặc gõ E2), gõ dấu /, nháy chuột vào ô E8 (hoặc gõ E8). Chọn nút trên thanh công thức (hoặc ấn )

3 Đƣa con trỏ về ô F2, sao chép công thức tính bằng cách điền tự động cho tới ô F7

Tại các ô F3:F7 xuất hiện #DIV/0? (chia cho 0). Khi đƣa con trỏ về ô F3, ta thấy trên thanh công thức ghi = E3/E9, Excel đã lấy số ở ô bên trái (E3) chia cho số ở cách đó 6 ô (E9), ... tức là đã sử dụng địa chỉ tƣơng đối.

Để báo cho Excel lấy lần lƣợt các số từ E2 đến E7 chia cho số cố định ở ô E8 (ô này là địa chỉ tuyệt đối), ta làm nhƣ sau:

Đƣa con trỏ ô về E2, nháy đúp nút chuột, sau đó đƣa con trỏ bàn phím về ngay trƣớc ký hiệu E8, ấn phím F4, dấu $ đƣợc điền vào trƣớc và giữa ký hiệu đó, ấn  rồi copy công thức này xuống ô E7.

Đánh dấu khối các ô từ E2 đến E7 (xem phần dƣới đây), chọn biểu tƣợng % (Percent Style) trên thanh định dạng, Excel đổi ra dạng phần trăm và điền dấu % cho các số. Để lấy chính xác hơn, ta chọn các ô này rồi chọn biểu tƣợng Increase Decimal, mỗi lần ấn chuột tại biểu tƣợng này, các con số lại đƣợc thêm một số thập phân

 Tính Tổng Lƣơng và ghi vào ô E8:

Bảng 1.19 : Ví dụ dữ liệu kiểu công thức

Thao tác Các bƣớc

1 Đƣa con trỏ về ô E8

2 Gõ vào dấu =, trỏ chuột vào biểu tƣợng ( (AutoSum), nháy đúp. Nếu dùng bàn phím, gõ vào công thức = SUM(E2:E7).

Bảng 1.20 : Ví dụ dữ liệu kiểu công thức

1.1.2.15. Sửa, xoá dữ liệu

Mục đích Thao tác

Xoá Đƣa con trỏ ô về ô hoặc chọn miền cần thiết ấn phím Delete

Nháy đúp chuột tại ô (hoặc ấn phím F2), nội dung của ô xuất hiện tại ô đó và tại thanh công thức, đƣa con trỏ bàn phím về chỗ cần thiết và sửa

Sửa

Ta nên bấm chuột tại thanh công thức và sửa tại đó, sửa xong chọn ký hiệu (hoặc ấn ) để ghi lại, chọn (hoặc ấn Esc) để huỷ bỏ mọi sửa đổi

Nhập dữ liệu mới cho ô đó, sau đó ấn phím 

Bảng 1.21 : Thao tác sửa, xóa dữ liệu

 Đánh dấu (chọn) khối

1.1.2.16. Các thao tác với khốí: Khối là một miền chữ nhật trên bảng tính.

Cách thức Thao tác

Cách 1 Trỏ chuột vào một góc của khối, bấm nút trái đồng thời kéo chuột về góc đối diện theo đƣờng chéo

Cách 2

Đƣa con trỏ ô về một góc của khối, giữ phím Shift đồng thời sử dụng các phím mũi tên để đƣa con trỏ ô về góc đối diện theo đƣờng chéo

 Khi khối là một miền liên tục:

Bảng 1.22 : Thao tác đánh dấu ( chọn) khối

Mục đích Thao tác

Chọn khối bất kỳ Đƣa con trỏ ô về góc trái trên của khối, giữ phím Shift đồng thời ấn nút trái chuột tại ô ở góc phải dƣới của nó

Chọn 1 cột ấn nút trái chuột tại tên cột đó (các chữ A, B,...) , hoặc ấn Ctrl + dấu cách

Chọn 1 hàng ấn nút trái chuột tại số thứ tự của hàng đó (các số 1,2,...), hoặc ấn Shift + dấu cách

Chọn toàn bộ bảng tính ấn nút trái chuột tại nút chọn toàn bộ bảng tính (bên trái cột A phía trên hàng 1) hoặc ấn tổ hợp phím Ctrl + Shift + dấu cách

Chọn các ô rời rạc Đƣa con trỏ tới ô đầu định chọn, giữ Ctrl và bấm nút trái chuột tại các ô định chọn tiếp theo

Bảng 1.23 : Thao tác đánh dấu ( chọn) khối khi khối là miền liên tục

 Khối là các miền rời rạc:

Mục đích Thao tác

Chọn khối bất kỳ Chọn vùng đầu, giữ Ctrl, bấm nút trái và rê chuột tại các miền khác

- Ví dụ : Để chọn đƣợc các miền nhƣ hình dƣới đây, ta làm theo các bƣớc sau :

- Chọn miền liên tục B2:C3

- Ấn giữ phím Ctrl và chọn miền liên tục

Bảng 1.24 : Thao tác đánh dấu ( chọn) khối khi khối là miền rời rạc

- Thực hiện tƣơng tự đối với các vùng khác.

 Copy, xoá, dán khối: Trƣớc khi thực hiện các thao tác sau, ta phải chọn khối .

E4:G7.

Mục đích Thao tác

Copy Chọn biểu tƣợng Copy, hoặc ấn Ctrl+C, hoặc chọn mục Edit / Copy

Xoá Chọn biểu tƣợng Cut, hoặc ấn Ctrl+X, hoặc chọn mục Edit / Cut

Đƣa con trỏ ô tới góc trái trên của miền đích Dán Chọn biểu tƣợng Paste, hoặc ấn Ctrl+V, hoặc chọn mục Edit / Paste

 Copy, di chuyển khối dùng chuột

Bảng 1.25 : Thao tác copy, xóa, dán khối

Các bƣớc Thao tác

1 Chọn khối cần thiết

2 Trỏ chuột vào biên của khối sao cho xuất hiện. Sau đó thực hiện một trong các thao tác sau:

Giữ Ctrl đồng thời kéo và thả khối tại vị trí đích để Copy khối. Nếu trong bƣớc này không dùng phím Ctrl thì khối sẽ đƣợc chuyển tới vị trí đích, hoặc 3

Bấm giữ nút phải chuột, kéo và thả khối tại vị trí đích. Xuất hiện thực đơn cho phép chọn một trong các phƣơng án:

Copy: Copy toàn bộ, bao gồm cả dữ liệu và khuôn dạng.

Move: Di chuyển dữ liệu.

Copy Value: Chỉ copy dữ liệu.

Copy Format: Chỉ copy khuôn dạng.

Bảng 1.26 : Thao tác copy, di chuyển khối dùng chuột

Chú ý: Khi copy dữ liệu

- Nếu miền nguồn chứa dữ liệu số hoặc chuỗi, kết quả miền đích sẽ giống miền nguồn.

- Nếu miền nguồn chứa công thức, kết quả miền đích sẽ thay đổi hay không tuỳ thuộc

vào công thức trong miền nguồn tham chiếu đến địa chỉ tƣơng đối hay địa chỉ tuyệt đối.

1.1.2.17.. Xử lý ô, cột, hàng trong bảng tính

- Thay đổi kích thƣớc cột, hàng

Đối Thao tác tƣợng

Một Trỏ chuột vào vạch đứng ở bên phải tên cột sao cho xuất hiện +, kéo và thả

cột vạch đó tại vị trí mới

Một Trỏ chuột vào vạch ngang dƣới số thứ tự hàng sao cho xuất hiện +, kéo và

hàng thả vạch đó tại vị trí mới.

Chọn một số ô của các cột

Format, Column, Width Nhiều

cột Gõ vào độ rộng mới cho các cột

OK hoặc 

Chọn một số ô của các hàng.

Format, Row, Height. Nhiều

hàng Gõ vào chiều cao mới cho các hàng

OK hoặc 

Bảng 1. 27 : Thao tác xử lý ô, cột, hàng trong bảng tính

- Chèn thêm cột, hàng, ô

Đối Thao tác tƣợng

Chọn khối là tên các cột (các chữ A, B, ...) tại vị trí cần chèn, cần thêm bao

nhiêu cột ta chọn bấy nhiêu.

Cột

Chọn Insert, Columns. Excel sẽ chèn thêm các cột trống và đẩy các cột đƣợc

chọn sang phải

Chọn khối là số thứ tự của các hàng (các số 1, 2, ...) tại vị trí cần chèn, cần

thêm bao nhiêu hàng ta chọn bấy nhiêu.

Hàng

Chọn Insert /Rows. Excel sẽ chèn thêm các hàng trống và đẩy các hàng

đƣợc chọn xuống dƣới.

Chọn khối là các ô tại vị trí cần chèn (nhƣ ở ví dụ dƣới đây là các ô C3:D4),

cần thêm bao nhiêu ô ta chọn bấy nhiêu

Chọn Insert, Cells. Xuất hiện hộp thoại Insert, nếu ta chọn:

Chọn Công dụng Ô

Shift Cells Right Đẩy các ô đƣợc chọn sang phải

Shift Cells Left Đẩy các ô đƣợc chọn xuống dƣới

Thực hiện lệnh OK hoặc 

Bảng 1. 28 : Thao tác chèn thêm cột , hàng , ô trong bảng tính

- Xoá cột, hàng, ô

Đối tƣợng Thao tác

Chọn khối là tên các cột (các chữ A, B, ...) tại vị trí cần xoá, cần xoá bao nhiêu cột ta chọn bấy nhiêu. Cột

Chọn Edit /Delete.

Chọn khối là số thứ tự các hàng (các số 1, 2,...) tại vị trí cần xoá, cần xoá bao nhiêu cột ta chọn bấy nhiêu. Hàng

Chọn Edit /Delete.

Chọn khối là các ô cần xoá.

Chọn Edit /Delete. Xuất hiện hộp thoại Delete, nếu ta chọn:

Chọn Công dụng

Shift Cells Right Chuyển dữ liệu của các ô bên phải sang vùng bị xoá Ô Shift Cells Left Chuyển dữ liệu của các ô phía dƣới lên vùng bị xoá.

Entire Row Xoá toàn bộ các hàng chứa vùng đƣợc chọn.

Entire Column Xoá toàn bộ các hàng chứa vùng đƣợc chọn.

Thực hiện lệnh OK hoặc 

Bảng 1. 29 : Thao tác xóa cột, hàng, ô trong bảng tính

 Giả sử cần chuyển dữ liệu đang đƣợc xếp thành cột trong các ô A2:A6 thành hàng

- Chuyển hàng thành cột và ngƣợc lại

tại các ô C3:F3, ta làm nhƣ sau:

Các bƣớc

Thao tác

Chọn các ô muốn cho đổi chỗ hay còn gọi là miền nguồn (A2:A6)

Chọn biểu tƣợng Copy hoặc Ctrl+C

Đƣa con trỏ về ô đầu tiên của miền dán (C3).

Chọn mục Edit /Past Special, đánh dấu chọn ô Transpose

Bảng 1. 30 : Thao tác chuyển hàng thành cột và ngƣợc lại

Chú ý: Miền nguồn và miền dán không đƣợc giao nhau (không đƣợc có ô chung)

Hình 1.8: Chuyển cột thành hàng sử dụng tính năng Tranpose

 Để tiện cho thao tác, trên các bảng tính, nhất là các bảng tính lớn, ngƣời ta thƣờng cho ẩn các cột (hoặc hàng) không cần thiết. Khi nào cần lại cho chúng hiện trở lại. Cách làm nhƣ sau:

- Ẩn/ hiện cột, hàng

Mục đích Thao tác

Chọn các cột (hoặc các hàng) cần ẩn đi.

Chọn Format /Column (hoặc Row).

Chọn Hide. ẩn các hàng (cột)

Tại vị trí các cột (hoặc hàng) ẩn, xuất hiện đƣờng kẻ dọc (hoặc ngang) đậm, các cột (hoặc hàng) bị ẩn vẫn có tác dụng (vẫn sử dụng để tính toán).

Chọn các cột (hoặc các hàng) liền kề với chúng. Ví dụ cần cho các cột C, D, E hiện trở lại, ta chọn các cột đứng ngay trƣớc và sau chúng : B, F; cần cho các hàng 4, 5, 6 hiện trở lại, ta chọn các hàng ở ngay trên và ngay dƣới chúng: 3, 7. Hiện lại các hàng (cột) đã bị ẩn Chọn Format /Column (hoặc Row), Unhide

Bảng 1. 31 : Thao tác ẩn / hiện cột hàng

 Ở các bảng tính lớn, khi cuộn xem hoặc nhập dữ liệu ở phần dƣới thì không còn thấy tiêu đề cột của chúng ở hàng trên cùng, do đó rất dễ bị nhầm lẫn giữa cột nọ với cột kia. Tƣơng tự nhƣ vậy, khi cuộn xem hoặc nhập dữ liệu ở bên phải thì không còn thấy các cột trái nhất (nhƣ các cột Họ tên, Mặt hàng, ... ), rất dễ bị nhầm lẫn hàng nọ với hàng kia, Excel giúp ta cố định cột, hàng tiêu đề, cách làm nhƣ sau:

- Cố định cột, hàng tiêu đề

- Đƣa con trỏ về ô chuẩn để cố định. ô này phải nằm ngay dƣới hàng và ngay bên phải cột cần cố định. Ví dụ, cần cố định các cột A, B và các hàng 1, 2, ta đƣa con trỏ về ô C3. Chọn Window/Freeze Panel. Tại ô chuẩn xuất hiện 2 đƣờng kẻ dọc và ngang.

- Để huỷ bỏ việc cố định cột, hàng tiêu đề, chọn Window/Unfreeze Panel

 Định dạng ký tự: Chọn miền dữ liệu cần định dạng, sau đó:

1.1.2.18. Định dạng dữ liệu

Hình 1.9: Định dạng dữ liệu

Phƣơng pháp Thao tác

Chọn một kiểu chữ từ hộp Font

Chọn một cỡ chữ từ hộp Size

Dùng chuột Chọn Bold để (hoặc bỏ) in đậm, chọn Italic để (hoặc bỏ) in nghiêng, chọn Underline để (hoặc bỏ) gạch chân.

Chọn Color để đặt mầu nền.

Chọn Font Color để đặt mầu chữ.

Format/Cells/Font

Chọn Color để đặt mầu chữ.

Chọn một kiểu chữ từ hộp Font

Chọn một cỡ chữ từ hộp Size

Dùng Menu (để có thể chọn nhiều mục đồng thời)

Trong Font Style: Chọn Italic để in nghiêng, chọn Bold để in đậm, chọn Bold Italic để in vừa nghiêng vừa đậm, chọn Regular để ký tự trở lại bình thƣờng.

Trong Underline chọn một kiểu gạch chân: None (bỏ gạch chân), Single (gạch bằng nét đơn), Double (gạch bằng nét đôi), Single Accounting (gạch bằng nét đơn kiểu tài chính đến cuối mép phải của ô), Double Accounting (gạch bằng nét đôi kiểu tài chính đến cuối mép phải của ô)

Chọn Color để đặt mầu chữ.

Trong ô Effect: Chọn Strikerthough (gạch ngang), Superscript (số mũ), Subscript (chỉ số)

Chọn Normal Font nếu muốn bỏ mọi trình bầy và lấy lại kiểu ngầm định.

Ctrl + B In đậm

Ctrl + I In nghiêng

Dùng bàn phím Ctrl + U Gạch chân

Ctrl + 5 Gạch ngang

Ctrl + Shift + F Mở danh sách Font chữ

Bảng 1. 32 : Thao tác định dạng dữ liệu

 Định dạng số

Hình 1.10: Định dạng Font chữ từ menu Format

- Chọn miền dữ liệu cần định dạng, sau đó chọn các biểu tƣợng tƣơng ứng trên thanh

định dạng (xem ý nghĩa của các biểu tƣợng ở hình 1.3)

Số cần định dạng Sử dụng biểu tƣợng Thành

123 Currency Style (Điền dấu tiền tệ) $ 123.00

123456 Comma Style (Dấu tách nghìn, triệu, tỷ) 123,456.00

0.156 15.6 %

Percent Style (Nhân với 100 và điền dấu %)

12.346 12.3460

Increase Decimal (Lấy thêm 1 số thập phân)

12.346 12.35

Decrease Decimal (Giảm đi 1 số thập phân)

- Trong ví dụ trên, các thông số quốc tế (dấu tiền tệ; dấu tách nghìn, triệu, tỷ; dấu thập phân) là kiểu Mỹ. Có thể đặt lại các thông số này cho phù hợp. Ví dụ ta muốn hiển thị các số theo kiểu Việt Nam dấu phân cách hàng nghìn là dấu chấm (.); dấu phân cách thập phân là dấu phẩy (,) ta vào:

Bảng 1. 33 : Định dạng số

Các bƣớc Thao tác

1 Tools/Options

2 Chọn Tab International

3 Bỏ dấu chọn ở Use System Seperators

4 Chọn lại Decimal Seperator: Thay dấu chấm(.) bằng dấu phẩy (,)

5 Chọn lại Thousands Seperator: Thay dấu phầy (,) bằng dấu phẩy (.)

6 Chọn OK

 Dóng hàng (Căn biên dữ liệu)

- Sử dụng các biểu tƣợng trên màn hình: Ví dụ gõ vào Excel nội dung nhƣ hình dƣới

Bảng 1.34 : Các thao tác định dạng số theo kiểu Việt Nam

Hình 1.11: Căn biên dữ liệu và hợp nhất nhiều ô

Hình 1.12: Định dạng dữ liệu

Các Thao tác bƣớc

1 Tại ô A1 gõ Khoa Kinh tế - Pháp chế

2 Tại ô A2 gõ Bộ môn Quản trị kinh doanh

3 Tại ô E1 gõ cộng hoà xã hội chủ nghĩa việt nam

4 Tại ô E2 gõ Độc Lập - Tự Do - Hạnh Phúc

Chọn miền A1:C1, chọn biểu tƣợng hợp nhất nhiều ô thành 1 ô (Merge and 5 Center)

Chọn miền A2:C2, chọn biểu tƣợng hợp nhất nhiều ô thành 1 ô (Merge and 6 Center)

Chọn miền E1:H1, chọn biểu tƣợng hợp nhất nhiều ô thành 1 ô (Merge and 7 Center)

Chọn miền E2:H2, chọn biểu tƣợng hợp nhất nhiều ô thành 1 ô (Merge and 8 Center)

- Sử dụng menu

- Format /Cells

- Xuất hiện hộp thoại Format Cells

Bảng 1.35 : Các thao tác căn biên dữ liệu

- Chọn Aligment

- Các thông số dóng hàng của dữ liệu trong ô bao gồm:

Hình 1.13: Hộp thoại Format Cells trợ giúp căn chỉnh dữ liệu

 Horizontal: Căn theo chiều ngang, các thông số của Horizontal có ý nghĩa nhƣ sau:

Thông số Ý nghĩa

General Ký tự đƣợc căn về bên trái, số đƣợc căn sang phải, các giá trị logic và lỗi đƣợc căn vào giữa

Left, Center, Right Căn sang trái, vào giữa, sang phải

Fill Tự điền đầy ô (trong ô chỉ cần gõ 1 nhóm ký tự sau đó chọn mục này, Excel sẽ điền đầy miền bằng nhóm này)

Justify Căn thẳng 2 chiều. Trƣờng hợp chiều rộng của ô nhỏ hơn nội dung bên trong nó, Excel tự động tăng chiều cao của hàng

Căn vào giữa miền đã chọn Center Across Selection

Distributed Căn dữ liệu theo 1 khoảng cách xác định do ta tuỳ chọn (khi ta chọn tuỳ chọn này tính năng Indent ở bên cạnh sẽ đƣợc kích hoạt và ta điền khoảng cách vào đó)

 Vertical: Căn theo chiều đứng, các thông số của Vertical có ý nghĩa nhƣ sau:

Bảng 1. 36 : Ý nghĩa của Horizontal

Thông số ý nghĩa

Top, Center, Bottom Căn lên trên, xuống dƣới, ở giữa ô.

Justify Căn thẳng 2 chiều. Khi dữ liệu vƣợt quá chiều rộng của ô.

 Các thông số khác của hộp thoại Format Cells

Bảng 1. 37 : Ý nghĩa của Vertical

Thông số ý nghĩa

Wrap Text Nếu chọn hộp kiểm này Excel sẽ cho xuống dòng tại các ô có nội dung vƣợt quá chiều rộng ô.

Shink to Fit Khi chọn hộp kiểm này Excel sẽ tự động tăng hoặc giảm cỡ Font để cho vừa khít với chiều rộng của ô.

Merge cells Hợp nhất các ô thành 1 ô duy nhất

Orientation

Quay dữ liệu với 1 góc tuỳ chọn. Giả sử bạn muốn dữ liệu hiển thị theo 1 góc 30 độ so với chiều ngang, bạn điền giá trị 30 vào ô Degrees

Bảng 1.38 : Các thông số khác của hộp thoại Format Cells

 Kẻ khung

Hình 1.14: Hộp thoại Format Cells trợ giúp kẻ đƣờng viền

Thao tác Các bƣớc

1 Chọn vùng dữ liệu cần kẻ khung

2 Chọn Format /Cells

3 Xuất hiện hộp thoại Format Cells

4 Chọn tab Border

Chọn kiểu hình đƣờng viền cần kẻ ở vùng Presets. Khi ta chọn 1 trong 3 kiểu hình tổng quát này nó sẽ tác động đến mục Border (loại hình viền chi tiết), các thông số có ý nghĩa nhƣ sau:

Chọn ý nghĩa

5 Outline Viền xung quanh khối ô

Inside Viền các đƣờng kẻ bên trong khối ô, không kể đƣờng bao quanh khối ô.

None Không viền đƣờng nào cả.

6 Chọn kiểu hình đƣờng viền chi tiết tại mục Border, có ý nghĩa sau: Left, Right, Top, Bottom, Squad: Viền trái, phải, trên, dƣới, chéo mỗi ô

Color: Mầu của đƣờng viền 7

Chọn kiểu đƣờng kẻ trong khung Style 8

Bảng 1. 39 : Các thao tác kẻ khung

 Tô mầu

Thao tác Các bƣớc

1 Chọn vùng dữ liệu cần tô mầu

2 Vào Format /Cells

3 Chọn tab Patterns

4 Chọn Patterns để đặt mầu nền

5 Chọn Color để đặt mầu tô

 Định dạng tự động

Bảng 1. 40 : Các thao tác tô màu

Khi không muốn định dạng dữ liệu theo từng bƣớc nhƣ trên, bạn có thể sử dụng tính năng AutoFormat (định dạng tự động) của Excel. Chƣơng trình Excel cung cấp cho chúng ta một số khuôn mẫu đƣợc xây dựng sẵn để định dạng (khuôn mẫu kiểu Simple, Accounting, 3D) trong các kiểu mẫu đó đã quy định sẵn kiểu đƣờng viền, kiểu Font, căn biên dữ liệu, mầu tô. Khi đã áp kiểu định dạng tự động AutoFormat vào vùng dữ liệu chúng ta đƣợc định dạng, ngay lập tức kiểu định dạng đó sẽ tác động lên toàn bộ vùng dữ liệu đƣợc lựa chọn, nó sẽ thay đổi kiểu Font, đƣờng viền, mầu nền lên đối tƣợng dữ liệu.

Các Thao tác bƣớc

1 Chọn vùng dữ liệu cần định dạng.

2 Vào menu Format /AutoFormat

3 Chọn kiểu tạo khuôn trong danh sách Table Format

Nút Option cho phép thực hiện việc tạo khuôn tự động hạn chế trên các 4 các kiểu dữ liệu nhất định.

5 Vùng Sample là mẫu của khuôn đƣợc minh hoạ trên một ví dụ giả định

Bảng 1.41 : Các thao tác định dạng tự động

 Định dạng toàn bộ bảng tính (Style)

Hình 1.15: Sử dụng tính năng định dạng tự động AutoFormat

- Tạo một kiểu (Style) mới

Với tất cả các thao tác nhƣ phần trên ta chỉ có thể định dạng bảng tính với 1 vài thuộc tính riêng lẻ nào đó. Để có thể nhanh chóng định dạng bảng tính theo mong muốn của chúng ta trên tất cả các mặt (Định dạng Font chữ thống nhất, dóng hàng, căn chỉnh, kẻ đƣờng viền, mầu nền ...) ta sử dụng tính năng Style (kiểu) giúp định dạng bảng tính một cách nhanh chóng và thống nhất.

Thao tác Các bƣớc

1 Đƣa con trỏ chọn miền cần tạo, nếu là bảng tính mới đƣa con trỏ về ô A1

2 Chọn mục Format /Style. Xuất hiện hộp thoại Style

3 Trong khung Style Name chọn kiểu đã có hoặc gõ vào tên cho kiểu mới.

4 Chọn nút Modify. Xuất hiện hộp thoại Format Cells

5 Number (định dạng số), Aligment (dóng hàng), Font (định dạng ký tự), Border (kẻ khung), Pattern (đặt mầu nền), Protection (bảo vệ ô)

6 Tiến hành định dạng dữ liệu

7 Chọn Add để bổ sung vào danh sách

8 Chọn OK để kết thúc

- Sử dụng một kiểu (Style) đã có :

Bảng 1.42 : Thao tác định dạng toàn bộ bảng tính , tạo một kiểu mới

Thao tác Các bƣớc

1 Đƣa con trỏ về ô (hoặc chọn miền) cần áp dụng

2 Chọn mục Format /Style. Xuất hiện hộp thoại Style

3 Trong khung Style Name chọn kiểu tƣơng ứng

8 Chọn OK để kết thúc

 Đặt tên cho ô

Bảng 1.43 : Thao tác định dạng toàn bộ bảng tính , tạo một kiểu đã có

Giả sử chúng ta có bảng dữ liệu gồm có các thông tin nhƣ Họ tên; Đơn vị công tác; Lƣơng của từng ngƣời; Tổng lƣơng, khi đó chúng ta thấy rằng có thể đặt tên cho từng vùng để khi cần tính toán hay tham chiếu đến vùng đó chúng ta chỉ cần gọi tên các vùng đó thay vì phải đƣa con trỏ chuột chọn toàn bộ vùng đó.

Hình 1.16: Đặt tên cho vùng dữ liệu

Đặt tên cho ô hay nhóm ô có tác dụng sau:

Giải thích Tác dụng

1 Tên dễ đọc, dễ nhớ. Tên "TongLuong" dễ hiểu và dễ nhớ hơn công thức =SUM(D2:D6).

Khi đã gán tên có thể tham chiếu tới ô hay nhóm ô bằng tên, kể cả nếu bạn đang ở 1 Sheet khác trong cùng file Excel đó. Khi đó, chúng ta không cần phải mất thời gian để đƣa con trỏ chuột đến Sheet muốn tham chiếu dữ liệu

3 Việc di chuyển về một ô (hay vùng) đã đƣợc đặt tên trở nên nhanh chóng và thuận tiện

Bảng 1.44 : Tác dụng của đặt tên cho ô hay nhóm ô

 Đặt tên cho ô hay nhóm ô bằng tay

Tên phải bắt đầu bởi chữ cái (và tuân theo những quy định đặt tên của Excel), có độ dài nhiều nhất là 255 ký tự và không đƣợc chứa dấu cách. Để dễ đọc, các chữ cái đầu mỗi từ trong tên nên viết hoa, ví dụ TongLuong hay nối các từ bằng dấu _ (nối dƣới), ví dụ Bang_Tra_Cuu. Không nên gõ dấu tiếng Việt để Excel thuận tiện trong việc tra cứu, tìm kiếm.

Sau khi chọn ô hay 1 vùng cần đặt tên bạn làm theo một trong hai cách sau:

Các cách Thao tác

Nháy chuột tại của hộp tên trên thanh công thức.

Xoá địa chỉ đó đi, gõ vào tên rồi ấn 

Vào menu Insert /Name /Define (hoặc Ctrl + F3)

Xuất hiện hộp thoại Define Name.

Trong khung Names in Workbook gõ tên vào

Bảng 1. 45 : Thao tác đặt tên cho ô hay nhóm ô bằng tay

 Đặt tên theo tiêu đề của cột hay hàng (tự động)

Hình 1.17: Sử dụng hộp thoại Define Name đặt tên cho vùng dữ liệu

Thao tác Các bƣớc

1 Chọn ô hay nhóm ô cần đặt tên gồm cả các tiêu đề cột hoặc hàng. Giả sử ta muốn đặt tên cho cột Họ tên ở bảng trên, bạn chọn cả vùng B1:B6

2 Chọn Insert/ Name/Create (hoặc Ctrl+Shift+F3).

ý nghĩa các mục trong hộp Create Name nhƣ sau:

Chọn ý nghĩa

Top Row Lấy ô ở hàng đầu (của khối đã chọn) làm tên 3 Bottom Row Lấy ô ở hàng cuối (của khối đã chọn) làm tên

Left Column Lấy ô ở cột bên trái (của khối đã chọn) làm tên

Right Column Lấy ô ở cột bên phải (của khối đã chọn) làm tên

4 Chọn Top Row

5 OK

6 Kết quả là ở ví dụ trên, vùng B1:B6 có tên là Họ tên

Bảng 1. 46 : Thao tác đặt tên theo tiêu đề của cột hay hàng (tự động)

Dán tên vào công thức

Khi nhập hay sửa công thức, thay vì điền vào địa chỉ của ô (hay vùng) đã đƣợc đặt tên,

ta có thể dán tên vào công thức, thực hiện nhƣ sau:

Thao tác Các bƣớc

1 ấn F3 (hoặc nháy chuột tại của hộp tên trên thanh công thức, hoặc chọn mục Insert /Name /Paste)

2 Chọn tên cần thiết từ danh sách rồi OK

 Về nhanh một ô (hay vùng) đã đƣợc đặt tên

Hình 1.18: Sử dụng tên để dán vào công thức

Có thể đƣa con trỏ về nhanh một ô (hay vùng) đã đƣợc đặt tên bằng 1 trong 2 cách sau:

Các cách Thao tác

1 Nháy chuột tại của hộp tên trên thanh công thức và chọn tên cần thiết từ danh sách (hoặc gõ địa chỉ của ô).

ấn F5

2 Chọn tên cần thiết từ danh sách

Bảng 1. 47 : Thao tác về nhanh một ô (hay vùng) đã đƣợc đặt tên

 Xoá tên

Thao tác Các bƣớc

1 Vào menu Insert /Name /Define.

2 Chọn tên cần xoá từ danh sách

3 Chọn Delete

4 Ấn OK hoặc Close

Bảng 1. 48 : Thao tác xoá tên

Lƣu ý: Nếu muốn lấy tên của nhóm ô này để đặt cho nhóm ô khác, trƣớc hết phải xoá

 Ghi chú cho ô (Comment)

tên đó đi.

Có thể thêm phần ghi chú cho từng ô riêng rẽ. Những ghi chú này giúp ta giải thích

bảng tính của mình một cách rõ ràng hơn (giải thích ý nghĩa, hay nội dung chi tiết hơn của 1

ô, hay 1 vùng)

Mục đích Thao tác

Về ô cần tạo ghi chú.

Mở hộp thoại ghi chú: Chọn mục Insert /Comment

Gõ vào lời ghi chú trong khung Text Note Tạo ghi chú

OK để đóng hộp thoại.

Từ nay về sau mỗi khi chúng ta di chuyển con trỏ chuột đến ô đã tạo

ghi chú tì lời ghi chú sẽ hiện lên để giải thích về ý nghĩa của ô đó

Nếu cần xem ghi chú của một ô, bạn chỉ việc di chuyển chuột vào ô

Xem /Sửa đó, một khung với nội dung ghi chú xuất hiện. Để tắt khung này, di

/Xoá các ghi chuyển chuột sang ô khác

chú

Để sửa hay xoá một ghi chú: Chọn Insert /Edit Comment

Tiến hành sửa nội dung trong hộp thoại ghi chú.

Tiến hành sửa nội dung của ghi chú trong hộp thoại ghi chú. Để xoá

ô ghi chú, chọn đƣờng viền bao quanh và ấn phím Delete

Bảng 1. 49 : Thao tác ghi chú cho ô (Comment)

 Bảo vệ ô:

Hình 1.19: Tạo lời ghi chú cho 1 ô hay 1 vùng

Trong một số trƣờng hợp, để giới hạn quyền sử dụng của ngƣời khác, nhƣ là không cho họ sửa đổi một số ô này hay xem công thức ở một số ô khác hãy sử dụng tính năng bảo vệ và che dấu ô. Cách làm nhƣ sau:

Thao tác Các bƣớc

1 Chọn ô hay nhóm ô cần bảo vệ.

2 Chọn mục Format /Cells.

3 Xuất hiện hộp thoại Format Cells.

4 Chọn tab Protection.

Trong hộp thoại đánh dấu chọn vào:

5 Locked: Để khoá không cho sửa đổi

Hidden: Để che dấu công thức

6 OK

7 Sau đó cần phải bảo vệ bảng tính thì những thiết lập trên mới có tác dụng. Xem phần Bảo vệ bảng tính ở phần trƣớc

Bảng 1. 50 : Thao tác tạo bảo vệ cho ô

 Dò các lỗi trong công thức (Auditing) : Vào Tools /Auditing

Hình 1.20: Hộp thoại Format Cells giúp bảo vệ dữ liệu

Công dụng: Là công cụ cho phép xác định và kiểm tra mối liên hệ số liệu của các ô trong bảng tính. Thực tế cho thấy mối liên hệ số liệu giữa số liệu, công thức và hàm số là vô cùng phức tạp. Nhiều khi chúng ta không thể kiểm soát và tìm hiểu đƣợc nguyên nhân các lỗi đã xảy ra trong tính toán. Auditing là công cụ tuyệt với dùng để rà soát lại công thức, kiểm tra lỗi trong bảng tính. Các chức năng cơ bản của Auditting:

Chọn Tác dụng

Kiểm tra các ô dữ liệu có liên quan đến công thức của ô hiện

thời. (Hay nói cách khác các ô có liên quan tiền nhiệm của công Trace Precedent

thức hiện thời)

Kiểm tra dữ liệu của các ô chứa công thức có liên quan đến dữ

liệu của ô hiện thời. (Hay nói cách khác các ô chứa công thức có Trace Depedent

liên quan hậu nhiệm của ô hiện thời)

Trace Error Kiểm tra nguồn gốc của lỗi sinh ra tại ô hiện thời

Remove All Xoá tất cả mũi tên arrows

Bảng 1.51 : Các chức năng cơ bản của auditing

 Phân tích và thống kê dữ liệu (Data table)

Công dụng: Dùng để tự động điền dữ liệu cùng một lúc vào bảng bằng công thức, nếu công thức đó chỉ phụ thuộc vào một hoặc hai biến. Trong trƣờng hợp thứ 2, các bƣớc tiến hành nhƣ sau:

Thao tác Các bƣớc

1 Xác định công thức cần điền dữ liệu. Giả sử công thức này phụ thuộc vào 2 biến X và Y

Gõ công thức này vào một ô của bảng tính. Ô này nằm ở góc trái trên của vùng cần điền dữ liệu. Trong công thức tham chiếu tới hai ô nhập dữ liệu X và Y bằng địa chỉ của 2 ô bất kỳ, ví dụ A1 và A2

3 Trên Dòng bên phải ô công thức điền các giá trị sẽ đƣợc thay thế vào bảng cho giá trị X của công thức

4 Trên Cột bên dƣới ô công thức điền các giá trị sẽ đƣợc thay thế vào bảng cho giá trị Y của công thức

5 Đánh dấu vào vùng bảng tính cần điền dữ liệu, vùng này bao gồm cả hàng và cột chứa các giá trị X, Y đã nêu ở các bƣớc trên

6 Thực hiện lệnh Data /Table, xuất hiện hộp thoại

7 Trong hộp thoại nhập địa chỉ ô tƣơng ứng với giá trị X(A1) tại Row Input Cell và điền địa chỉ ô tƣơng ứng với giá trị Y (A2) tại Column Input Cell

8 Chọn OK

Bảng 1.52 : Các thao tác phân tích và thống kê dữ liệu

1.2. Các hàm thông dụng

1.2.1. Các hàm ngày tháng

* Hàm trong Excel :

Hàm (Function) đƣợc xem nhƣ là các công thức định sẵn nhằm thực hiện các tính toán chuyên biệt. Trên ô thực hiện hàm sẽ cho một giá trị hoặc một thông báo lỗi (khi ta nhập đối số không đúng theo quy định, xem ở phần thông báo lỗi phía trên). Excel có trên 300 hàm và đƣợc phân loại thành từng nhóm.

* Quy tắc sử dụng hàm

Quy Dạng thể hiện Minh họa Kết quả tắc

Cho ngày hiện tại trong máy, =TODAY( ) (hàm không cần đối số)

Cho độ dài của chuỗi là 8 =LEN("Quản trị") (hàm 1 đối số) 1 Tên hàm(Các đối số)

Trung bình cộng các số trong

=AVERAGE(A1,B5,D8) các ô A1, B5, D5 (hàm nhiều

đối số)

2 Tên hàm có thể viết thƣờng hay hoa hoặc vừa viết thƣờng vừa viết hoa đều đƣợc

Các đối số có thể có hoặc không nhƣng phải đặt trong hai dấu ( ) và cách nhau bởi

dấu phẩy (nhƣ trong tài liệu này), chấm phẩy hoặc một dấu ngăn cách nào khác 3 tuỳ theo cách đặt các thông số quốc tế. Trong 1 hàm có thể chứa nhiều nhất 30 đối

số nhƣng không đƣợc vƣợt quá 255 ký tự

4 Trong hàm không đƣợc có dấu cách

Hàm phải đƣợc bắt đầu bởi dấu = hoặc dấu của một phép tính. Trƣờng hợp dùng

một hàm để làm đối số cho một hàm khác (hàm lồng nhau, nhiều nhất là 7 mức)

5 không cần viết dấu = trƣớc tên hàm đó. Ví dụ : các ô A1, B1 chứa số đo các cạnh

của tam giác vuông, khi đó công thức = SQRT(SUM(A1^2, B1^2)) gõ tại ô C1

cho số đo cạnh huyền

ở đây, SQRT là hàm khai căn bậc 2, SUM là hàm tính tổng (bình phƣơng của 2

6 cạnh góc vuông), ta thấy trƣớc hàm này không có dấu = vì nó đƣợc dùng làm đối

số cho hàm SQRT.

Bảng 1. 53 : Các quy tác sử dụng hàm

 Trong các công thức tính toán hoặc các hàm ta cung cấp các giá trị cho Excel tính toán bằng 2 cách hoặc ta nhập trực tiếp giá trị vào hoặc nếu giá trị đó được chứa trong 1 ô nào đó của bảng tính thì ta chỉ việc nhập địa chỉ của ô đó vào công thức, Excel sẽ tự động

* Địa chỉ tuyệt đối và địa chỉ tương đối:

lấy giá trị của ô đó để tính toán. Excel có các loại địa chỉ sau đây

Hình thức Loại Mục đích

A1 Địa chỉ tƣơng đối Không cố định hàng và cột

$A$1 Địa chỉ tuyệt đối Cố định cả hàng và cột

A$1 Chỉ cố định hàng, cột không cố định Tƣơng đối cột, tuyệt đối hàng

$A1 Chỉ cố định cột, hàng không cố định Tuyệt đối cột, tƣơng đối hàng

 Khi nào thì áp dụng địa chỉ tƣơng đối, địa chỉ tuyệt đối sẽ đƣợc minh hoạ cụ thể qua

Bảng 1.54 : Các loại địa chỉ ô

các ví dụ sau:

 Ví dụ 1: Trong tháng 5 doanh thu tiêu thụ sản phẩm của Công ty cơ điện Trần Phú đƣợc cho nhƣ trong bảng trên. Mong muốn của chúng ta là tính doanh thu của từng loại sản phẩm, tổng doanh thu và % theo tổng doanh thu.

Hình 1.21: Báo cáo doanh thu tháng 5 năm 2007

Để có đƣợc doanh thu từng loại sản phẩm ta thực hiện các bƣớc sau đây:

Thao tác Giải thích Các bƣớc

1 F4=D4*E4

Tại ô F4 ta nhập công thức =D4*E4. Vì [Doanh thu]=[Số lƣợng tiêu thụ]x[Giá bán]. ở đây ta thấy địa chỉ của D4 và E4 đều là địa chỉ tƣơng đối. Khi sử dụng địa chỉ tƣơng đối ta có thể kéo xuống các ô F5 và F10 để Excel tự động điền công thức tƣơng tự vào các ô đó, giúp cho ta không phải gõ công thức lần lƣợt vào các ô.

2 Enter Kết thúc việc nhập công thức vào ô F4

3 Nháy trái vào ô F4 Chọn ô F4

4 Chuẩn bị kéo xuống để định dạng công thức cho các ô khác Đƣa con trỏ xuống phía dƣới góc trái ô F4, xuất hiện dấu +

Nhấn và giữ phím trái chuột, kéo xuống ô F5 cho đến F10, và thả ra Excel sẽ tự điền công thức cho các ô F5 đến F10, khi đó công thức trong ô F5 sẽ =D5*E5, .., ô F10 sẽ = D10*E10

Bảng 1.55 : Ví dụ minh họa

Hình 1.22 : Các bƣớc tính doanh thu từng loại sản phẩm

Để có đƣợc tổng doanh thu ta làm theo 1 trong 2 cách sau:

Các cách Thao tác Giải thích

F11=SUM(F4:F10) Ô F11 chứa giá trị tổng doanh thu

Sử dụng hàm Kết thúc tính toán, cho ta kết quả tổng doanh Enter thu

Thể hiện mong muốn của ta là muốn tính Đƣa con trỏ chuột chọn tổng từ ô F4 cho đến ô F10, còn ô F11 là nơi các ô từ F4 cho đến F11 Sử dụng biểu chứa kết quả

tƣợng 

Nhấp vào biểu tƣợng  Khi ta nhấp vào biểu tƣợng  thì ngay lập

trên thanh công cụ tức ta sẽ có giá trị tổng doanh thu tại ô F11

Bảng 1.55 : Ví dụ minh họa

Hình 1.23: Tính tổng doanh thu

Các bƣớc cần thực hiện khi tính % theo tổng doanh thu:

Thao tác Giải thích Các bƣớc

1 G4=F4/ $F$11

Tại ô G4 ta nhập công thức =F4*$F$11. Vì [% theo tổng doanh thu]=[Doanh thu] / [Tổng doanh thu]. ở đây ta thấy địa chỉ của F4 là tƣơng đối còn F11 tuyệt đối. Bởi vì nếu địa chỉ F11 là tƣơng đối thì khi kéo xuống định dạng cho các ô G5 cho đến G10 thì công thức ô G5 sẽ =F5/F12,..., ô G10 sẽ =F10/F17, tức là đã không còn đúng nữa. Do vậy, ta phải cố định ô F11 lại bằng địa chỉ tuyệt đối.

2 Enter Kết thúc việc nhập công thức vào ô G4

3 Nháy trái vào ô G4 Chọn ô G4

4 Chuẩn bị kéo xuống để định dạng công thức cho các ô khác Đƣa con trỏ xuống phía dƣới góc trái ô G4, xuất hiện dấu +

Excel sẽ tự điền công thức cho các ô G5 đến G10, khi đó công thức trong ô G5 sẽ =F5/$F$11, .., ô G10 sẽ = F10*$F$11 Nhấn và giữ phím trái chuột, kéo xuống ô G5 cho đến G10, và thả ra

Bảng 1.56 : Thao tác minh họa

 Ví dụ 2: Việc sử dụng địa chỉ tƣơng đối hay tuyệt đối là rất đơn giản, tuy nhiên ta sẽ

Hình 1.24 : Tính % theo tổng doanh thu

thắc mắc vậy loại địa chỉ thứ 3 (tƣơng đối cột, tuyệt đối hàng) và loại địa chỉ thứ 4 (tuyệt

đối cột, tƣơng đối hàng) dùng để làm gì? Để giải quyết đƣợc câu hỏi đó ta xem xét bài toán

Nhƣ ta đã biết Bảng cửu chƣơng là Bảng thể hiện tích của 2 số, với giá trị của số

sau: Hãy tạo 1 bảng cửu chƣơng bằng Excel?

Nhƣ vậy nếu làm thủ công thì ta sẽ phải điền tổng cộng 81 công thức vào 81 ô.

nhân và số bị nhân là từ 1 đến 9.

Để không phải nhập quá nhiều công thức vào các ô nhƣ thế, ta phải sử dụng đến

Điều đó là rất mất thời gian.

loại địa chỉ thứ 3 và thứ 4, các bƣớc nhƣ sau:

Các Thao tác Giải thích bƣớc

Điền các giá trị từ 1 đến 9 Các số đó chính là số nhân và số bị nhân, giao của

vào cột A và hàng 1 nhƣ từng cột và hàng chính là các giá trị tích của Bảng 1

hình vẽ cửu chƣơng.

Tại ô B2 ta nhập công thức= B$1*$A2. ở đây ta

thấy địa chỉ của B1 (số bị nhân) là loại 3 (tƣơng

đối cột, tuyệt đối hàng), còn A2 (số nhân) là loại 4 2 B2= B$1*$A2

(tuyệt đối cột, tƣơng đối hàng). Các thao tác phía

dƣới sẽ giải thích điều đó.

3 Enter Kết thúc việc nhập công thức vào ô B2

Ta thấy rằng khi kéo công thức nhƣ vậy, thì tại các

ô chỉ số sẽ thay đối tƣơng ứng, số bị nhân (B$1)

Kéo công thức từ ô B2 để lần lƣợt là C$1 cho đến J$1 vì ta luôn cố định

định dạng cho các ô C2 hàng, còn chỉ số cột ta cho thay đổi. Còn số nhân 4

đến J2 ($A2) vẫn tồn tại vì khi đó ta cố định cột, còn hàng

không cố định nhƣng ta chỉ kéo ở trên hàng có thứ

tự là 2

Ta thấy rằng khi kéo công thức nhƣ vậy, thì tại các

ô chỉ số sẽ thay đối tƣơng ứng, số bị nhân (B$1) Kéo công thức từ ô B2 để vẫn tồn tại vì khi đó ta cố định hàng, còn cột không định dạng cho các ô B3 5 cố định nhƣng ta chỉ kéo ở trên cột B. Còn số nhân đến B10 ($A2) lần lƣợt là $A3 cho đến $A10 vì ta luôn cố

định cột, còn chỉ số hàng ta cho thay đối

Qua việc kéo ở 2 bƣớc phía trên, ta thấy rằng công Kéo công thức để định thức đã hoàn toàn chính xác. Việc còn lại là ta chỉ dạng cho toàn bộ các ô 6 việc kéo để định dạng cho toàn bộ các ô trong còn lại trong bảng bảng.

Bảng 1.57: Thao tác minh họa

Hình 1.25: Tạo lập bảng cửu chƣơng

* Nhập hàm vào bảng tính: Có 3 cách nhập hàm vào bảng tính

Các cách Thao tác

Gõ dấu = Gõ vào từ bàn

phím Gõ vào tên hàm, dấu (các đối số theo đúng quy định)

Chọn biểu tƣợng fx trên thanh công cụ.

Xuất hiện hộp thoại Insert Function

Chọn nhóm hàm trong khung Function Category. Khi chọn

nhóm nào, Excel sẽ liệt kê các hàm của nhóm đó theo thứ tự chữ

cái trong khung Function Name

Chọn nhóm hàm trong khung Function Name

Dùng biểu tƣợng

Chọn hàm cần sử dụng (Function Wizard)

Xuất hiện hộp thoại Function Arguments liệt kê các đối số cần

nhập, thể hiện kết quả phía dƣới

Điền các đối số của hàm bằng cách ấn nút chuột vào khung cần

thiết, sau đó nhập từ bàn phím hoặc di chuyển chuột trên vùng

dữ liệu (ở hình dƣới là chọn miền D2:D6)

Chọn OK sẽ tắt hộp thoại và ghi kết quả của hàm vào ô

Vào menu Insert /Function

Dùng Menu Xuất hiện hộp thoại Function Wizard

Các bƣớc còn lại giống nhƣ khi chọn biểu tƣợng fx

Bảng 1.58 : Thao tác nhập hàm vào bảng tính

 Ý nghĩa của các nhóm hàm trong khung Category nhƣ sau:

Hình 1.26: Hộp thoại Insert Function trợ giúp nhập hàm

Tên ý nghĩa

Most Recently Used Các hàm sử dụng gần đây nhất

All Tất cả các hàm

Financial Các hàm về tài chính

Date & Time Các hàm về Ngày và Giờ

Math & Trig Các hàm về Toán và Lƣợng giác

Statistical Các hàm thống kê

Lookup & Reference Các hàm Tìm kiếm và Tham chiếu

Database Các hàm Cơ sở dữ liệu

Text Các hàm xử lý ký tự

Logical Các hàm Logic

Information Các hàm Thông tin về ô, về bảng tính

Bảng 1. 59 : Ý nghĩa của các nhóm hàm trong khung Category

Hình 1.27: Hộp thoại Function Arguments trợ giúp nhập các đối số cho hàm

Tên hàm Công dụng Minh họa Kết quả

Cho ta dạng thể hiện

DATE(yy,mm,dd) đúng của ngày tháng =DATE(07,09,27) 27/09/2007

năm theo Excel

Cho ta kết quả là giá DAY(date) =DAY(27/09/07) 27 trị ngày

Cho ta biết đó là thứ

mấy trong tuần. Nếu =WEEKDAY(27/09/0 WEEKDAY(date) kết quả 1 là thứ Hai, 2 5 (đó là thứ Sáu) 7) là thứ Ba..., 7 là Chủ

Nhật)

Cho ta kết quả là giá MONTH(date) =MONTH(27/09/07) 9 trị tháng

Cho ta kết quả là giá YEAR(date) =YEAR(27/09/07) 2007 trị năm

Cho ta kết quả là giá

TODAY() trị ngày tháng năm =TODAY() 27/09/2007

hiện tại của máy

Cho ta kết quả là giá 27/09/2007 NOW() trị ngày tháng năm và =NOW() 19:05 giờ hiện tại của máy

Cho ta dạng thể hiện

TIME(hh,mm,ss) đúng của thời gian =TIME(19,5,14) 19:05:14

theo Excel

Bảng 1. 60 : Các hàm thời gian

1.2.2. Các hàm ký tự:

Tên hàm Công dụng Minh họa Kết quả

Chuyển 1 chuỗi bất kỳ UPPER(chuỗi) =UPPER(“Quản Trị”) quản trị thành chữ hoa

Chuyển 1 chuỗi bất kỳ =LOWER(“Quản LOWER(chuỗi) quản trị thành chữ thƣờng Trị”)

Chuyển các chữ cái đầu PROPER(chuỗi) =PROPER(“kinh từ của chuỗi thành chữ Kinh Doanh doanh”) viết hoa.

Cắt bỏ các ký tự trắng =TRIM(“ Kinh doanh TRIM(chuỗi) “Kinh doanh” vô nghĩa ra khỏi chuỗi ”)

Cắt từ xâu cũ ra 1 xâu

với n ký tự, tính từ phía LEFT(chuỗi, n) =LEFT(“Quản trị, 6”) Quản t

bên trái

Cắt từ xâu cũ ra 1 xâu =RIGHT(“Quản trị, với n ký tự, tính từ phía RIGHT(chuỗi,n) ản trị 6”) bên phải

Lấy từ xâu cũ ra 1

xâuvới n ký tự, bắt đầu MID(chuỗi, m,n) =MID(“Quản trị”, 2,6) uản tr từ vị trí m tính từ phía

bên trái

LEN(chuỗi) Cho ta độ dài của chuỗi =LEN(“Quản trị”) 8

Kinh doanh Lặp lại n lần liên tiếp =REFT(“Kinh doanh Kinh doanh REPT(chuỗi, n) chuỗi đó ”,3) Kinh doanh

EXACT(chuỗi 1, =EXACT(“Kinh So sánh giữa chuỗi 1 TRUE chuỗi 2) doanh”,“Kinh doanh”) và chuỗi 2 có giống

nhau hoàn toàn hay =EXACT(“Kinh FALSE không doanh”,“kinh doanh”)

Cho ta kết quả là vị trí =FIND(“Quản trị kinh 6 của chuỗi 1 trong chuỗi doanh”, “trị”)

2 bắt đầu từ vị trí n, nếu FIND(chuỗi 1, bỏ qua n nó đƣợc cho chuỗi 2, n) =FIND(“Quản trị kinh bằng 1. Hàm này phân #VALUE! (lỗi) doanh”, “Kế toán”) biệt chữ HOA và

thƣờng

Tƣơng tự hàm Find

SEARCH(chuỗi nhƣng không phân biệt =SEARCH(“Quản trị 6 1, chuỗi 1, n) chữ in HOA hay chữ in kinh doanh”, “Trị”)

thƣờng.

CONCATENAT Nối các chuỗi lại với =CONCAT(“Quản trị Quản trị và Kế E( chuỗi 1, chuỗi nhau và ”, “Kế toán”) toán 2)

Tƣơng tự nhƣ

CONCAT, nhƣng ít sử = “Quản trị ”& “Kế & Quản trị Kế toán dụng khi có nhiều toán”)

chuỗi

Thay chuỗi 2 vào REPLACE(chuỗi =REPLACE("Tôi học chuỗi 1 bắt đầu từ vị trí Tôi học quản trị 1, n, m, chuỗi 2) kế toán",9,7,"quản trị") n và có độ dài m

Chuyển 1 chuỗi thành 1 VALUE(chuỗi) =VALUE("54321") 54321 (số) số

SUBSTITUTE(c =SUBSTITUTE("Quả Thay thế chuỗi mới vào Quản trị kinh huỗi mẹ, chuỗi n trị kinh kinh", "kinh", vị trí chuỗi cũ trong doanh cũ, chuỗi mới, n) "doanh",2) chuỗi mẹ ở lần xuất

hiện n (Nếu không có =SUBSTITUTE("Quả Quản trị doanh đối số này sẽ thay thế ở n trị kinh kinh", "kinh", doanh mọi vị trí). "doanh")

Chuyển số thành chuỗi Giả sử A1 có chứa số

với n sau dấu thập 12345.6789, khi đó ta 12 345.679 phân. Nếu không có n gõ vào

sẽ cho 2 chữ số thập =FIXED(A1,3,false)

phân. Nừu cách là

FIXED(số, n, FALSE (hoặc không

cách) ghi), chuỗi in ra sẽ có

dấu ngăn cách phần

=FIXED(A1,true) 12345.68 nghìn, nếu cách là

TRUE, chuỗi in ra sẽ

không có dấu ngăn

cách phần nghìn

Bảng 1. 61 : Các hàm ký tự

1.2.3. Các hàm toán học:

Các hàm toán học thông thƣờng

Tên hàm Công dụng Minh họa Kết quả

Cho ta kết quả là phần MOD(số bị chia, dƣ của phép chia 2 số =MOD(100,3) 1 số chia) nguyên

=INT(3.789) 3

Cho ta kết quả là 1 số -4 (vì -4 nhỏ INT(số x) nguyên gần với số x, =INT(-3.1) hơn) nhƣng nhỏ hơn số x

=INT(9/2) 4

TRUNC(số x) =TRUNC(3.789) 3 Cắt bỏ phần thập

phân của số x, chỉ lấy

phần nguyên của x. =TRUNC(-3.1) -3 Lƣu ý phân biệt với

hàm INT

Làm tròn số x với độ =ROUND(A1, 2) 123 456,15

chính xác đến con số =ROUND(A1, 1) 123 456,1 thứ n. Giả sử

A1=123456,146

Nếu n > 0 thì số x

đƣợc làm tròn đến ROUND(số x, n) chữ số bên trái thứ n 123 000 (làm

của dấu thập phân. tròn đến phần =ROUND(A1, -3)

nghìn) Nếu n < 0 thì số x

đƣợc làm tròn đến

chữ số thập phân thứ

ABS(số x) Trị tuyệt đối của số x =ABS(-2) 2

SQRT(số x) Căn bậc 2 của số x =SQRT(2) 1,4142

Cho ta 1 số ngẫu RAND( ) RAND( ) 0,786 nhiên giữa 0 và 1

PRODUCT(số Cho ta kết quả là tích =PRODUCT(2,5,10) 100 1,số 2, .. số n) của tất cả các số đó

Tổ hợp chập k của n COMBIN(n,k) =COMBIN(4,2) 6 phần tử

SUM(số 1,số 2..số Tính tổng các số đó =SUM(2,5,10) 17 n)

Bảng 1.62 : Các hàm toán học

Các hàm lôga

Tên hàm Công dụng Minh họa Kết quả

LOG(số x, cơ số a) =LOG(5,2) 2,32 Cho ta kết quả là lôga cơ số a của số x

LN(số x) =LN(5) 1,61 Cho ta kết quả là lôga cơ số e của số x

EXP(số x) Cho ta kết quả là e mũ x =EXP(5) 148,41

Bảng 1.63 : Các hàm Loga

Các hàm lƣợng giác

Tên hàm Công dụng Minh họa Kết quả

Cho ta giá trị của số Pi, có thể

dùng để tính toán trong các PI() =PI() 3,1416 công thức có liên quan đến số

Chuyển đối góc x đang tính

bằng độ sang radian. Bởi vì

các hàm lƣợng giác đều tính =RADIANS(4 RADIANS(góc x) 0,785 góc bằng Radian, do vậy ta 5)

phải chuyển góc từ độ sang

Radian

Chuyển góc x từ Radian sang =DEGREES(0 DEGREES(góc x) 45 độ ,785)

SIN(góc x) Tính Sin của góc x =SIN(0,785) 0,71

COS(góc x) Tính Cos của góc x =COS(0,785) 0,71

TAN(góc x) Tính Tang của góc x =TAN(0,785) 1

Hàm arcsin của góc x, x nằm

trong khoảng từ -1 đến 1, giá ASIN(góc x) =ASIN(0,71) 0,785

trị trả về là radian

Hàm arccos của góc x, x nằm

trong khoảng từ -1 đến 1, giá ACOS(góc x) =ACOS(0,71) 0,785

trị trả về là radian

Hàm arctang của góc x, giá ATAN(góc x) =ATAN(1) 0,785 trị trả về là radian

Bảng 1. 64 : Các hàm lƣợng giác

Các hàm tính toán có điều kiện

Tên hàm Công dụng Minh họa

COUNIF(vùng cần đếm, điều kiện đếm) Đếm số ô trong vùng lựa chọn xem có bao nhiêu ô thoả mãn điều kiện cho trƣớc. Xem ví dụ minh hoạ phần dƣới

SUMIF(vùng đối chiếu, điều kiện, vùng cần tính tổng) Xem ví dụ minh hoạ phần dƣới

Hàm này chỉ tính tổng của những ô trong vùng cần tính tổng mà ô tƣơng ứng (cùng hàng) với nó ở trong vùng đối chiếu thoả mãn điều kiện cho trƣớc. Còn những ô khác không thoả mãn sẽ không đƣợc đƣa vào giá trị tổng.

SUMPRODUCT(mản g 1, mảng 2, ..., mảng n) Minh hoạ ở phần bài tập phần 4

Hàm tính tổng các tích số từng phần tử của các mảng với nhau. Các mảng phải có kích thƣớc bằng nhau. Đây là 1 hàm quan trọng khi ta muốn tính giá trị 1 tổng với những điều kiện ràng buộc

 Ví dụ minh hoạ: Thông tin cá nhân của Công ty cổ phần Đầu tƣ và Phát triển công nghệ BPT nhƣ bảng dƣới đây. Cuối tháng Công ty cần phải thống kê các số liệu để lấy thông tin phục vụ cho các công việc chung nhƣ khen [thƣởng] cho từng ngƣời, nộp thuế thu nhập nếu thu nhập vƣợt quá 10 triệu đồng, tính tổng thu nhập của nam giới trong công ty

Bảng 1. 65 : Các hàm tính toán có điều kiện

 Để tính số nhân viên nữ trong công ty ta làm nhƣ sau:

× Hình 1.28: Bảng kê thu nhập cá nhân

Các Thao tác Giải thích bƣớc

1 Chọn ô F13 Chuẩn bị nhập công thức

Ta nhập hàm COUNIF với các thông số

nhƣ trên. Trong đó: C3:C12 là vùng cần 2 =COUNTIF(C3:C12;”Nữ”)

đếm,”Nữ” là điều kiện

3 Enter Kết thúc việc nhập hàm, kết quả sẽ là 3

Bảng 1.66 : Ví dụ minh họa

 Để tính số ngƣời đƣợc khen thƣởng ta làm nhƣ sau:

Các Thao tác Giải thích bƣớc

1 Chọn ô F14 Chuẩn bị nhập công thức

Ta nhập hàm COUNIF với các thông số

nhƣ trên. Trong đó: E3:E12 là vùng cần 2 =COUNTIF(E3:E12;”Xuất sắc”)

đếm,”Xuất sắc” là điều kiện

3 Enter Kết thúc việc nhập hàm, kết quả sẽ là 4

 Để tính số ngƣời có thu nhập >10 triệu ta làm nhƣ sau:

Bảng 1.67 : Ví dụ minh họa

Các Thao tác Giải thích bƣớc

1 Chọn ô F15 Chuẩn bị nhập công thức

Ta nhập hàm COUNIF với các thông

số nhƣ trên. Trong đó: D3:D12 là 2 =COUNTIF(D3:D12;”>10.000.000”) vùng cần đếm,”>10.000.000” là điều

kiện

Kết thúc việc nhập hàm, kết quả sẽ là 3 Enter 5

 Để tính tổng thu nhập của nam giới ta làm nhƣ sau:

Bảng 1.68 : Ví dụ minh họa

Thao tác Giải thích Các bƣớc

1 Chọn ô F16 Chuẩn bị nhập công thức

Ta nhập hàm SUMIF với các thông số nhƣ trên. Trong đó:

C3:C12 là vùng đối chiếu 2 =SUMIF(C3:C12;”Nam”,D3:D12)

“Nam” là điều kiện

D3:D12 là vùng cần tính tổng

3 Enter Kết quả sẽ là 86.300.000

Lƣu ý: Hàm COUNTIF chỉ đếm theo 1 điều kiện, nếu từ 2 điều kiện trở lên phải dùng hàm DCOUNT. Hàm SUMIF chỉ tính tổng theo 1 điều kiện, nếu cần tính tổng theo 2 điều kiện trở lên phải dùng hàm DSUM.

Bảng 1.69 : Ví dụ minh họa

1.2.4. Các hàm thống kê

Các hàm thống kê

Tên hàm Công dụng Minh họa Kết quả

MAX(số 1,số 2...,số n) =MAX(7,9,100,3) 100 Cho ta giá trị lớn nhất trong các số

MIN(số 1,số 2...,số n) =MAX(7,9,100,3) 3 Cho ta giá trị nhỏ nhất trong các số

29,75 AVERAGE(số 1,số 2,...số n) Trung bình cộng giản đơn của các số =AVERAGE(7,9,10 0,3)

MODE(vùng) 9 Trả lại giá trị hay gặp nhất trong vùng =AVERAGE(7,9,1,3 ,9)

LARGE(vùng,k) Phần tử lớn thứ k trong vùng =LARGE(A1:A4,2) 9

SMALL(vùng, k) Phần tử nhỏ thứ k trong vùng =SMALL(A1:A4,2) 7

2 RANK(số x,vùng, kiểu xếp) =RANK(9,$A$1:$A $4,0) Xác định thứ hạng của số x trong vùng, tức là xem số đó

3 đứng thứ mấy trong vùng đó. Đối số cuối cùng là kiểu xếp, xếp giảm dần nếu là 0, xếp tăng dần nếu là 1. =RANK(9,$A$1:$A $4,1)

Địa chỉ của vùng phải là tuyệt đối.

4 COUNT(vùng) =COUNT(A1:A4,"K há")

Đếm số ô là giá trị số trong vùng, những giá trị rỗng và không phải là số không đƣợc đếm vào

5 COUNTA(vùng) =COUNTA(A1:A4," Khá") Đếm số ô trong vùng, những giá trị rỗng không đƣợc đếm vào

Bảng 1. 70 : Các hàm thống kê

1.2.5. Các hàm về lôgic

Các hàm lôgic

Tên hàm Công dụng Minh họa Kết quả

Nhận giá trị TRUE (đúng) nếu =AND(9>8,19 TRUE AND(biểu thức 1, tất cả các biểu thức 1, biểu thức >11)

biểu thức 2,..,biểu 2.. . biểu thức n đều là TRUE,

=AND(9>8,19 thức n) nhận giá trị FALSE (sai) nếu có FALSE <11) ít nhất một đối số là FALSE

Nhận giá trị TRUE nếu một =AND(9>8,19 TRUE OR(biểu thức 1, trong các biểu thức 1,l biểu thức <11)

biểu thức 2,..,biểu 2,.., biểu thức n là TRUE, nhận

=AND(9<8,19 thức n) giá trị FALSE nếu tất cả các biểu FALSE <11) thức đó là FALSE.

Cho ta giá trị phủ định của đối =NOT(1+1>3) TRUE

NOT(biểu thức) số, kết quả TRUE hoặc là

=NOT(1+1=2) FALSE FALSE

Hàm không đối số, cho ta kết quả FALSE() =FALSE() FALSE là giá trị lôgic FALSE

Hàm không đối số, cho ta kết quả TRUE() =TRUE() TRUE là giá trị lôgic TRUE

Cho ta kết quả là giá trị 1 nếu

biểu thức lôgic là đúng, còn nếu

sai sẽ nhận giá trị 2. Giá trị 2 có

IF(biểu thức thể không có. Hàm IF có thể lồng =IF(A1>7,"Kh lôgic, giá trị 1, giá nhau đến 7 cấp. Đây là 1 hàm Khá á","Dở" trị 2) quan trọng, để có thể sử dụng

hiệu quả hàm này chúng ta sẽ xét

đến ở các ví dụ khác. Giả sử tại ô

A1=8

 Ví dụ: Để đƣa ra đƣợc hạn ngạch xuất khẩu hàng dệt may vào Hoa Kỳ, ngƣời ta căn cứ vào năng lực sản xuất của chính công ty đó, hãy xác định hạn ngạch xuất khẩu cho các công ty:

Bảng 1. 71 : Các hàm logic

Hình 1.29: Bảng quy định xét duyệt hạn ngạch

Thao tác Giải thích Các bƣớc

1 Đƣa con trỏ về ô C10 Chuẩn bị nhập công thức

2 Nhập công thức vào ô C10

=IF(B10=”Cực lớn”, 1.000.000, IF(B10=”Lớn”, 500.000, IF(B10=”Trung bình”, 300.000, 50.000)))

3 Enter Kết thúc nhập công thức

4 Kéo trái từ ô C10C16 Định dạng cho các ô còn lại

Bảng 1. 72 : ví dụ minh họa

1.2.6. Các hàm về thông tin (kiểm tra):

Các hàm kiểm tra

Tên hàm

Công dụng

Minh họa

Kết quả

Kiểm tra xem ô đó có phải là ô trống hay không, nếu

=ISSBLANK(

ISBLANK(đối số)

TRUE

đúng kết quả là TRUE, sai là FALSE

A1)

Kiểm tra xem ô đó có phải là ô chứa thông báo lỗi N /A

ISERR(đối số)

(lỗi không xác định) hay không, nếu đúng kết quả là

=ISERR(A1)

FALSE

TRUE, sai là FALSE

Kiểm tra xem ô đó có phải là ô chứa thông báo lỗi hay

=ISERROR(A1

ISERROR(đối số)

FALSE

không, nếu đúng kết quả là TRUE, sai là FALSE

)

Kiểm tra xem ô đó có phải là ô chứa giá trị logic hay

ISLOGICAL(đối

=ISERROR(A1

không, nếu đúng (True hay Fale) kết quả là TRUE, nếu

FALSE

số)

)

không phải thì là FALSE

Kiểm tra giá trị trong ô đó có phải là chuỗi hay không,

ISNONTEXT(đối

=ISNONTEXT

nếu không phải là chuỗi thì kết quả là TRUE, sai là

TRUE

số)

(A2)

FALSE

Kiểm tra giá trị trong ô đó có phải là chuỗi hay không,

ISTEXT(đối số)

=ISTEXT(A2)

FALSE

nếu đúng là chuỗi thì kết quả là TRUE, sai là FALSE

ISNUMBER(đối

Kiểm tra giá trị trong ô đó có phải là số hay không, nếu

=ISNUMBER(

TRUE

số)

đúng là số thì kết quả là TRUE, sai là FALSE

A3)

Kiểm tra kiểu của ô đó. Nếu giá trị trong ô đó là

TYPE(đối số)

Number thì kết quả là 1, nếu Text = 2, nếu là Lôgic = 4,

=TYPE(A3)

nếu là Lỗi =16, array=64

Xác định dấu của 1 số, nếu số x>0 thì kết quả là 1, số

SIGN(số x)

=SIGN(A3)

-1

x=0 thì kết quả là 0, số x<0 thì kết quả là -1

Bảng 1.73 :Các hàm về thông tin (kiểm tra)

1.2.7. Các hàm tìm kiếm và tham chiếu

Các hàm tìm kiếm và tham chiếu

Lookup value

Giá trị cần tìm kiếm, VLOOKUP sẽ dựa vào giá trị này để tìm kiếm ở cột thứ 1 bên Bảng tra cứu.

Table array

Bảng tra cứu (địa chỉ phải là tuyệt đối)

Col index num

Số thứ tự của cột trong Bảng tra cứu nơi VLOOKUP sẽ lấy giá trị trả về.

VLOOKUP(đối số)

Range lookup

Kiểu tìm kiếm là tuyệt đối hay tƣơng đối. Nếu tuyệt đối thì ta điền số 0, nếu tƣơng đối ta điền số 1 (khi nào thì áp dụng tuyệt đối hay tƣơng đối ta sẽ tìm hiểu thông qua các ví dụ phía dƣới). Tạm thời ta luôn tìm kiếm theo tuyệt đối.

Lookup value

Giá trị đƣợc tìm kiếm trên hàng đầu tiên của Bảng tra cứu

Table array

Bảng tra cứu đƣợc viết thành hàng (địa chỉ phải là tuyệt đối)

HLOOKUP(đối số)

Row index num

Số thứ tự của hàng trong Bảng tra cứu nơi HLOOKUP sẽ lấy giá trị trả về.

Range lookup

Kiểu tìm kiếm

INDEX(đối số)

Array

Miền tham chiếu

Tên hàm Các đối số Giải thích

Dạng 1

Row num

Số thứ tự hàng

Column num

Số thứ tự cột

Reference

Vùng tham chiếu (trong đó có chứa nhiều vùng nhỏ)

INDEX(đối số)

Row num

Số thứ tự hàng

Dạng 2

Column num

Số thứ tự cột

Area num

Chỉ số của vùng cần tham chiếu

 Lƣu ý: Trong 3 hàm nêu trên thì hàm VLOOKUP là hàm đƣợc sử dụng nhiều nhất

Bảng 1. 74 : Các hàm tìm kiếm và tham chiếu

do tầm quan trọng của nó. Hàm này sẽ giúp cho ngƣời dùng đỡ đƣợc nhiều thao tác phải

nhập dữ liệu, nó sẽ tự động lấy dữ liệu từ 1 bảng tham chiếu nào đó để tính toán. Việc sử

dụng hàm này cũng tƣơng đối đơn giản khi ngƣời dùng hiểu đƣợc công dụng của nó và các

thông số, tuy nhiên sau 1 thời gian không dùng đến nhiều ngƣời lại quên và không hiểu các

đối số của nó có nghĩa là gì (vì tên các đối số quá dài và khó hiểu hơn so với các hàm khác).

Để có thể thực sự nhớ và hiểu hàm này chúng ta hãy liên hệ đến 1 ví dụ điển hình sau:

 Ví dụ 1: Chi nhánh Agribank quận Hai Bà Trƣng có Bảng quy định về phụ cấp trách

Hình 1.30: Bảng quy định xét thƣởng

nhiệm và thƣởng cuối năm cho CBCNV tuỳ thuộc vào vị trí và chức vụ của họ, nhiệm vụ

của ngƣời quản lý cần phải đƣa ra đƣợc phụ cấp và mức thƣởng cuối năm mà mỗi ngƣời

nhận đƣợc, tuy nhiên do số lƣợng nhân viên trong công ty quá lớn không lẽ lại làm thủ công

điền cho từng ngƣời. Để đơn giản hoá công việc đó ta phải nhờ đến hàm VLOOKUP để tự

động cho ta mức thƣởng và phụ cấp của từng ngƣời căn cứ vào chức vụ của họ trong công

 Để tính mức thƣởng cuối năm cho từng ngƣời ta làm nhƣ sau:

ty.

Các Thao tác Giải thích bƣớc

1 Đƣa con trỏ chuột về ô C11 Chuẩn bị nhập công thức vào ô C11

Ta nhập hàm VLOOKUP vào ô C11 với các

thông số nhƣ trên. Trong đó:

B11: là giá trị tìm kiếm.

$A$3:$C$7 là Bảng quy định hay Bảng tra

cứu (lƣu ý: Không đƣa hàng tiêu đề vào, địa 2 =VLOOKUP(B11;$A$3:$C$7;3;0) chỉ của Bảng quy định là tuyệt đối $).

3: là số thứ tự của cột (trong bảng quy định

tính từ bên trái sang) mà ta muốn lấy giá trị,

đó chính là cột Thƣởng cuối năm.

0: Tìm kiếm theo tuyệt đối.

Kết thúc việc nhập hàm, kết quả sẽ là 3 Enter 3.500.000

Nhấn đè phím trái chuột để kéo từ ô Định dạng công thức tƣơng tự cho các ô còn 4 C11 đến ô C20 lại.

Bảng 1. 75 : Ví dụ minh họa các hàm tìm kiếm và tham chiếu

 Để tính phụ cấp trách nhiệm cho từng ngƣời ta làm nhƣ sau:

Các Thao tác Giải thích bƣớc

1 Đƣa con trỏ chuột về ô D11 Chuẩn bị nhập công thức vào ô D11

Ta nhập hàm VLOOKUP vào ô D11 với các

thông số nhƣ trên. Trong đó:

B11: là giá trị tìm kiếm.

$A$3:$C$7 là Bảng quy định hay Bảng tra

cứu (Lƣu ý: Không đƣa hàng tiêu đề, địa chỉ 2 =VLOOKUP(B11;$A$3:$C$7;2;0) của Bảng quy định là tuyệt đối $).

2: là số thứ tự của cột (trong bảng quy định

tính từ bên trái sang) mà ta muốn lấy giá trị,

đó chính là cột Phụ cấp trách nhiệm.

0: Tìm kiếm theo tuyệt đối.

Kết thúc việc nhập hàm, kết quả sẽ là 3 Enter 1.000.000

Nhấn đè phím trái chuột để kéo từ ô Định dạng công thức tƣơng tự cho các ô còn 4 D11 đến ô D20 lại.

 Ví dụ 2: Cũng tại chi nhánh trên, họ có Bảng quy định về mức phạt đối với những cá

Bảng 1. 76 : Ví dụ minh họa các hàm tìm kiếm và tham chiếu

nhân căn cứ vào số ngày nghỉ của họ trong tháng. Yêu cầu đối với ngƣời quản lý là phải đƣa

ra đƣợc mức phạt đối với từng ngƣời dựa vào số liệu thống kê ngày nghỉ của họ trong tháng.

 Các bƣớc nhƣ sau:

Thao tác Giải thích Các bƣớc

1 Đƣa con trỏ chuột về ô C11 Chuẩn bị nhập công thức vào ô C11

Ta nhập hàm VLOOKUP vào ô C11 với các thông số nhƣ trên. Trong đó:

B11: là giá trị tìm kiếm.

$A$3:$B$7 là Bảng quy định (lƣu ý: Không đƣa hàng tiêu đề, địa chỉ của Bảng quy định là tuyệt đối $, và cột thứ nhất trong Bảng quy định sắp xếp tăng dần từ giá trị nhỏ nhất cho tới giá trị lớn nhất, nhƣ VD trên là từ 0 21).

2 =VLOOKUP(B11;$A$3:$B$7;2;1) 2: là số thứ tự của cột (trong bảng quy định tính từ bên trái sang) mà ta muốn lấy giá trị, đó chính là cột Mức phạt

1: Tìm kiếm theo tƣơng đối (phân biệt với tuyệt đối). Nếu ta tìm kiếm theo tuyệt đối thì trong Bảng quy định phải có ít nhất 1 giá trị trùng với giá trị tìm kiếm, nếu không có giá trị tìm kiếm trong Bảng quy định thì Excel sẽ thông báo lỗi. Còn đối với tìm kiếm tƣơng đối thì không nhất thiết nhƣ vậy, nếu không có giá trị nào trong Bảng quy định trùng với giá trị tìm kiếm thì nó sẽ tìm đến giá trị gần bằng nó (nhƣng nhỏ hơn) để lấy so khớp và lấy giá trị.

3 Enter Kết thúc việc nhập hàm, kết quả sẽ là 500.000

4 Nhấn đè phím trái chuột để kéo từ ô C11 đến ô C20 Định dạng công thức tƣơng tự cho các ô còn lại.

Bảng 1. 77 : Ví dụ minh họa các hàm tìm kiếm và tham chiếu

 Ví dụ 3: Minh hoạ công dụng hàm HLOOKUP. Tƣơng tự nhƣ hàm VLOOKUP, chỉ

Hình 1.31: Bảng quy định mức phạt

khác là các giá trị cần tìm kiếm trong Bảng quy định đƣợc sắp thành các hàng. Công ty

TNHH nội thất La Xuyên có Bảng quy định về giá của từng loại sản phẩm nhƣ bảng dƣới

đây, Công ty Kiểm toán Việt Nam có đặt hàng mua của Công ty La Xuyên số lƣợng Ghế

với quy định về chủng loại mẫu mã nhƣ trong bảng. Hãy tính số tiền mà Công ty Kiểm toán

Việt Nam phải thanh toán:

Hình 1.32: Bảng đơn giá sản phẩm

Các

Thao tác

Giải thích

bƣớc

Đƣa con trỏ chuột về ô C11

Chuẩn bị nhập công thức vào ô C11

Ta nhập hàm HLOOKUP vào ô C11 với các thông

số nhƣ trên. Trong đó:

A11: là giá trị tìm kiếm.

$B$3:$G$7 là Bảng quy định (lƣu ý: địa chỉ của

=HLOOKUP(A11;$B$3:$G$7;3;0)

Bảng quy định là tuyệt đối $)

3: là số thứ tự của hàng (trong bảng quy định tính

từ trên xuống dƣới) mà ta muốn lấy giá trị, đó

chính là hàng Ghế

0: Tìm kiếm theo tuyệt đối

Enter

Kết thúc việc nhập hàm, kết quả sẽ là 500.000

Nhấn đè phím trái chuột để kéo từ ô C11

Định dạng công thức tƣơng tự cho các ô còn lại.

đến ô C16

 Ví dụ 4: Minh hoạ công dụng hàm INDEX. Hàm INDEX có 2 dạng, nếu ở:

Bảng 1. 78 : Ví dụ minh họa các hàm tìm kiếm và tham chiếu

 Dạng 1: Nó tƣơng tự nhƣ việc ta thay thế việc nhập địa chỉ của ô đó bằng hàm INDEX, Excel sẽ cho ta giá trị của ô đƣợc quy định bởi chỉ số hàng và chỉ số cột trong phạm vi mảng đó (thứ tự cột và hàng tuỳ thuộc vào vị trí của Mảng đó)

Kết quả TT Minh họa

4 1 =INDEX({1,2;3,4},2,2)

Đào 2 Nếu B5:B6 chứa các xâu Táo và Chuối, còn C5:C6 chứa các xâu Cam và Đào thì: =INDEX(B5:C6,2,2)

3 Chuối Nếu B5:B6 chứa các xâu Táo và Chuối, còn C5:C6 chứa các xâu Cam và Đào thì: =INDEX(B5:C6,2,1)

 Dạng 2: áp dụng trong trƣờng hợp miền tham chiếu có nhiều vùng khác nhau.Trong 1 bảng tính, vùng A2:C6 đƣợc đặt tên là Quả, vùng từ A8:C11 đƣợc đặt tên là Nƣớc, và vùng A1:C11 đƣợc đặt tên là HàngHoá thì:

Bảng 1. 79 : Ví dụ minh họa các hàm INDEX

TT Minh họa Kết quả

1 =INDEX(Quả,2,3) thì sẽ tham chiếu đến ô C3 2.000

2 =INDEX((A1:C6,A8:C11),2,2,2) thì sẽ tham chiếu đến ô B9 12.000

3 =SUM(INDEX(HàngHoá,0,3,1)) tƣơng tự SUM(C1:C11) 24.000

Bảng 1. 80 : Ví dụ minh họa các hàm INDEX

Hình 1.33: Cách thức sử dụng hàm index

1.2.8. Các hàm tài chính

Các hàm tài chính

Tên hàm Công dụng Minh họa Kết quả

20%

RATE(Số kỳ,khoản định toán thanh kỳ,giá trị đầu tƣ) Cho ta xác định lãi suất một khoản đầu tƣ thu đƣợc từ các khoản thanh toán định kỳ

Giả sử bạn đang cân nhắc về 1 khoản đầu tƣ mà có thể đem lại cho bạn mỗi năm $1000 và kéo dài trong 5 năm. Số vốn đầu tƣ ban đầu là $3000. Để xác định mức lãi thực của đầu tƣ hàng năm là:

=RATE(5,1000,-3000)

11% IRR(Values,guess) (khá cao)

IRR là tỉ lệ làm cho giá trị hiện tại của số tiền thu về giá trị đầu tƣ. IRR dùng để đánh giá cơ hội đầu tƣ, tức là so sánh với lãi suất bình quân Giả sử bạn đồng ý cho thuê 1 căn hộ chung cƣ trị giá $120.000. Sau 5 năm dự tính sẽ có $25000; $27000; $35000; $38000; $40000 tiền thuê. Các dữ liệu này đã đƣợc nhập vào bảng tính (từ A1:A6). Nhớ là -$120000

=IRR(A1:A6)

($4.389) PV(Rate, Nper, Pmt, Fv, Type) Nếu đầu tƣ vào thị trƣờng với lãi suất năm bằng 4,5% với thời hạn 5 năm, mỗi năm lấy ra 1000$ thì số tiền cần đầu tƣ là:

= PV(4,5%,5,$1000)

Cho ta số tiền cần đầu tƣ, với lãi suất (Rate), tổng số lần lấy ra hoặc nộp vào (Nper), số tiền lấy ra trong kỳ (Pmt), giá trị tƣơng lai (Fv có thể không cần điền), Type là 0 nếu lấy ra ở cuối kỳ, là 1 nếu lấy ở đầu kỳ

-6153 NPV(Rate, giá trị 1,giá trị 2,..,giá trị n)

tiền tƣ 1 khoản Đầu $250.000; lãi suất năm 12%. Năm đầu chịu thua lỗ $55.000; năm 2,3,4 thu đƣợc lợi nhuận $95.000; $140.000; $185.000. Để đánh giá sự đầu tƣ này ta sử dụng:

Đánh giá kết quả đầu tƣ, với lãi suất (rate), giá trị 1, giá trị 2, giá trị n là số tiền kỳ vọng trong các năm. Nếu kết quả - là thua lỗ; còn nếu + là có lãi.

=NPV(12%, -55000, 95000, 140000, 185000) - 250000

Cho ta giá trị trong tƣơng lai của số tiền đầu tƣ. Với

Rate: Lãi suất.

Nper: Tổng số tiền lấy ra (hoặc nộp vào) 12453 FV(Rate,Nper,Pmt, Pv,Type)

Pmt: Số tiền nộp vào (hoặc lấy ra) Giả sử trong tài khoản của bạn đã có $7500; hàng năm bạn còn gửi vào tài khoản của mình $2000; lãi suất hàng năm 11%. Vậy số tiền trong tài khoản sau 5 năm sẽ là:

Pv: Giá trị hiện tại FV (11%,5,-2000,-7500,1)

Type: Loaị thanh toán =0 nếu cuối kỳ; =1 nếu đầu kỳ.

Cho ta giá trị khấu hao của 1 kỳ theo phƣơng pháp số dƣ giảm dần theo 1 mức cố định trong 1 khoảng thời gian xác định. Với

- Cost: Giá trị ban đầu của tài sản.

Một tài sản có giá trị ban đầu 150.000.000; giá trị còn lại 50.000.000; số kỳ khấu hao là 4 năm. Vậy số tiền khấu hao cho kỳ đầu tiên (kỳ 1) sẽ 36 triệu - Salvage: Giá trị còn lại của tài sản DB(Cost,Salvage,Li fe,Period,Month)

=DB(150.000.000, 50.000.000, 4, 1)

- Life: Tuổi thọ (hoặc thời gian tính khấu hao)

(còn nếu kỳ tiếp theo điền 2,3,4) - Period: Kỳ khấu hao

- Month: Số tháng sử dụng trong năm đầu (có thể không cần điền)

Cho ta biết tổng số tiền khấu hao hàng năm của TSCĐ trong 1 khoảng thời gian xác định. Với

- Cost: Giá trị ban đầu của tài sản.

36 triệu SYS(Cost,Salvage, Life,Per) Một tài sản có giá trị ban đầu 150.000.000; giá trị còn lại 50.000.000; số kỳ khấu hao là 4 năm. Vậy số tiền khấu hao cho kỳ 1:

- Salvage: Giá trị còn lại của tài sản (giá trị thu hồi) =SYS(150.000.000, 50.000.000, 4,1)

- Life: Tuổi thọ (hoặc thời gian tính khấu hao)

- Per: Kỳ khấu hao

Cho ta số tiền khấu hao hàng năm của TSCĐ theo phƣơng pháp khấu hao đều. Với

- Cost: Giá trị ban đầu của tài sản. 25 triệu SLN(Cost,Salvage, Life) Một tài sản có giá trị ban đầu 150.000.000; giá trị còn lại 50.000.000; số kỳ khấu hao là 4 năm. Vậy số tiền khấu hao cho 1 kỳ:

- Salvage: Giá trị còn lại của tài sản (giá trị thu hồi) =SLN(150.000.000, 50.000.000, 4)

- Life: Tuổi thọ (hoặc thời gian tính khấu hao)

Bảng 1. 81 : Các hàm tài chính

 Ngoài việc sao chép công thức tƣơng đối nhƣ trên, Excel còn cung cấp một phƣơng

* Công thức mảng

 Mảng đƣợc dùng khi ta gặp phải một lƣợng tính toán nhiều và phức tạp trên một vị trí nhỏ hẹp, hoặc dùng để thay thế các công thức cần lặp lại nhiều lần, mảng giúp tiết kiệm đƣợc bộ nhớ (thay vì phải có 100 công thức sao chép vào 100 ô, chỉ cần một công thức mảng là đủ).

tiện khác gọn gàng hơn, đó là mảng (Array).

Hình 1.34: Lập 1 công thức mảng

 Nhập một công thức mảng (Array formula):

Thao tác Giải thích Các bƣớc

đen miền 1 Bôi E2:E7 Về ô cần thiết, nếu là dãy ô, phải đánh dấu khối chúng

2 =C2:C7*D2:D7 Khác với loại công thức tƣơng đối (= C2*D2), ở đây phải nhập toàn bộ dãy ô : =C2:C7*D2:D7

3 Ctrl + Shift + 

Giữ đồng thời 2 phím Ctrl và Shift trong khi ấn ). Excel sẽ bao công thức mảng này trong cặp ngoặc nhọn {C2:C7*D2:D7}

 Sửa một công thức mảng :

Bảng 1. 83 : Thao tác sửa công thức mảng

Thao tác Giải thích Các bƣớc

1 Nháy chuột lên thanh công thức. Cặp dấu ngoặc { } biến mất. Về ô bất kỳ đã áp dụng công thức mảng cần sửa

Tiến hà nh sửa công thức 2 Gõ vào nội dung sửa

3 Ctrl + Shift +  Kết thúc việc sửa công thức

 Chọn (bôi đen) 1 dãy mảng: Về ô bất kỳ của dãy mảng theo 1 trong 2 cách

Bảng 1. 84 : Thao tác sửa công thức mảng

Các cách Thao tác Giải thích

1 Bấm Ctrl + / Giữ phím Ctrl trong khi ấn phím sổ chéo xuôi /)

2 Bấm F5 Để hiện hộp thoại lựa chọn

3 Chọn mảng hiện thời

Chọn Special sau đó chọn Current Array

Bảng 1. 85 : Thao tác chọn ( bôi đen ) 1 dãy mảng

CÂU HỎI ÔN TẬP, THẢO LUẬN CHƢƠNG 1

Câu 1. Phân biệt sự khác nhau giữa địa chỉ tƣơng đối và địa chỉ tuyệt đối?

Câu 2. Tại sao nói “Việc xử lý, tính toán dữ liệu dựa trên công cụ bảng tính vừa là công cụ nâng cao hiệu suất làm việc, lại vừa là một áp lực đối với ngƣời dùng?”

Câu 3. Phân biệt sự khác nhau của các đối số giữa hàm Vlookup và hàm Hlookup?

Câu 4. Trong nhóm hàm tài chính, thanh toán đầu kỳ và thanh toán cuối kỳ có khác nhau không? Tại sao?

Câu 5. Việc tính toán dữ liệu dựa trên công cụ bảng tính có vai trò quan trọng nhƣ thế nào ở các doanh nghiệp Việt Nam hiện nay? Lấy ví dụ cụ thể.

BÀI TẬP ỨNG DỤNG

Hãy thực hiện các yêu cầu trong bảng

CHƢƠNG 2 : CƠ SỞ DỮ LIỆU

MỤC ĐÍCH CỦA CHƢƠNG:

Sau khi nghiên cứu và học tập chƣơng này, sinh viên nắm đƣợc:

 Các khái niệm cơ bản về Cơ sở dữ liệu  Cách sắp xếp dữ liệu.  Cách sử dụng tính năng Sub Total.  Cách lọc dữ liệu.  Cách sử dụng tính năng Pivot Table để tạo lập một bảng tổng hợp.

NỘI DUNG CHƢƠNG

 Cơ sở dữ liệu là tập hợp các dữ liệu đƣợc sắp xếp trên một vùng chữ nhật (gồm ít

2.1. Khái niệm về Cơ sở dữ liệu

- Hàng đầu tiên ghi các tiêu đề của dữ liệu, mỗi tiêu đề trên một cột. Các tiêu đề này

nhất 2 hàng) của bảng tính tuân theo những quy định sau:

- Từ hàng thứ hai trở đi chứa dữ liệu, mỗi hàng là một bản ghi (record).

- Tên các trƣờng phải là dạng ký tự (không đƣợc dùng số, công thức, toạ độ ô để đặt

đƣợc gọi là trƣờng (field).

- Không nên có miền rỗng trong CSDL.

tên). Nên đặt tên trƣờng ngắn gọn, không trùng lặp.

2.2. Các thao tác cơ bản trên Cơ sở dữ liệu

Thêm, xóa 1 bản ghi trên CSDL

 Giả sử ta có CSDL từ A1:E10 nhƣ hình trên, mong muốn của ta bây giờ là muốn thêm hay xoá đi 1 hàng (bản ghi) trong vùng CSDL này, để thực hiện đƣợc điều đó ta có thể

Hình 2.1: Sửa đổi 1 CSDL

- Chọn vùng CSDL từ A1:E10.

- Chọn menu Data /Form  xuất hiện hộp thoại, ý nghĩa các thông số nhƣ sau:

sử dụng lệnh Insert (delete) 1 hàng, tuy nhiên đối với 1 CSDL ta còn có cách khác, các bƣớc thực hiện nhƣ sau:

Hình 2.2: Hộp thoại sửa đổi 1 CSDL

STT Thông số Ý nghĩa

1 New Thêm 1 hàng (1 bản ghi) vào vùng CSDL

2 Delete Xoá 1 hàng (1 bản ghi) trong vùng CSDL

3 Restore Phục hồi 1 hàng (đã lỡ sửa ở thao tác trƣớc)

4 Find Prev Chuyển lên hàng phía trên

5 Find Next Chuyển tới hàng dƣới

6 Criteria (hoặc Form) Chuyển chế độ giữa tiêu chuẩn và vùng dữ liệu để thực hiện các thao tác sửa đổi, xoá 1 hàng

Bảng 2.1. Bảng thông số của hộp thoại sửa cơ sở dữ liệu

 Các CSDL đôi khi chƣa đƣợc sắp xếp theo ý của ngƣời dùng. Muốn sắp xếp 1

2.3. Sắp xếp trên Cơ sở dữ liệu:

- Chọn vùng CSDL, ở ví dụ trên là vùng A1:E10.

CSDL theo mong muốn ta thực hiện các thao tác sau đây:

- Vào menu Data /Sort  xuất hiện hộp thoại Sort, ý nghĩa các thông số nhƣ sau :

Hình 2.3: Hộp thoại Sort sắp xếp 1 CSDL

STT Thông số Ý nghĩa

1 Sort by Cột ƣu tiên thứ nhất để thực hiện việc sắp xếp

2 Then by Cột ƣu tiên thứ hai để thực hiện việc sắp xếp

3 Then by Cột ƣu tiên thứ ba để thực hiện việc sắp xếp

3 Ascending Sắp xếp tăng dần

4 Desending Sắp xếp giảm dần

5 My List Has Header row (hoặc No header row): miền dữ liệu chứa (hoặc không chứa) hàng tiêu đề

Các lựa chọn chi tiết hơn cho việc sắp xếp, ý nghĩa:

Case Sensitive Phân biệt HOA và thƣờng

6 Option Orientation Hƣớng sắp xếp

Sort Top To Bottom Sắp xếp các dòng

Sort Left To Right Sắp xếp các cột

- Hình trên là hộp thoại sắp xếp Sort với các thông số, cột [Mã vật tƣ] sẽ đƣợc ƣu tiên sắp xếp đầu tiên theo chiều tăng dần (theo thứ tự A, B, C...), những bản ghi trùng tên [Mã vật tƣ]

Bảng 2.2. Bảng các thông số hộp thoại Sort sắp xếp 1 CSDL

thì sẽ thực hiện sắp xếp tiếp tại cột [Tên vật tƣ] theo chiều tăng, nếu lại trùng thì sẽ sắp xếp theo cột thứ 3 [Đơn giá] những vật tƣ nào có Đơn giá cao hơn thì ở trên (sắp giảm dần)

 Công dụng SubTotal: Chèn tại các vị trí cần thiết các tính toán thống kê theo yêu cầu của ngƣời sử dụng (Lƣu ý: Muốn tính tổng 1 loại nào đó ta phải sắp xếp Sort trƣờng đó theo ý muốn)

2.4. Tính tổng các nhóm (SubTotal ):

 Doanh nghiệp thƣơng mại ZOHO hàng tháng mua vật tƣ, nhân viên quản lý nhập dữ liệu vào máy căn cứ theo hoá đơn nhập (Tên vật tƣ, ngày nhập, số lƣợng, đơn giá nhập ...) nhƣ hình trên. Đến cuối tháng nhà quản lý muốn ta thông báo các thông tin sau: Tổng lƣợng nhập vào Tổng tiền của Cát, Đá, Sỏi, Thép trong tháng. Để thực hiện đƣợc điều đó, ta thực hiện các bƣớc sau:

- Chọn vùng A2:G12.

- Sắp xếp (sort) dữ liệu theo trƣờng tính cần tính tổng (ở ví dụ trên là trƣờng Tên vật

Hình 2.4: Tính tổng 1 nhóm (SubTotal)

- Chọn Data/Subtotals  xuất hiện hộp thoại Subtotal, ý nghĩa các thông số nhƣ sau:

tƣ).

TT Thông số Ý nghĩa

1 At Each Change in

Chọn trƣờng mà theo trƣờng này, tại mỗi vị trí thay đổi, Excel sẽ chèn vào một dòng tổng kết, tức là dòng thực hiện các phép tính (ở ví dụ trên là trƣờng [Tên vật tƣ], cứ mỗi khi chuyển sang loại vật tƣ khác Excel lại chèn dòng 1 dòng tính tổng phía dƣới).

2 Use Function

Chọn hàm để tính toán tổng kết dữ liệu. Hàm ngầm định là SUM (Nếu chọn Average sẽ tính giá trị trung bình). ở đây ta chọn SUM

3 Add SubTotal to Chọn các trƣờng cần tính toán. ở đây ta chọn trƣờng [Số lƣợng] và [Thành tiền]

4 Replace Current SubTotal Khi tạo dòng tổng kết mới dòng này sẽ thay thế dòng tổng kết cũ. Theo ngầm định các dòng tổng kết sẽ nối tiếp nhau

Break 5 Page Between Group Chèn dấu ngắt trang tại mỗi vị trí có dòng SubTotal (tức là đƣa mỗi nhóm sang một trang riêng biệt)

6 Summary Below Data Đặt dòng tổng kết ở cuối mỗi nhóm. Nếu bỏ dấu dòng này sẽ đƣợc đƣa lên trƣớc mỗi nhóm

7 Remove All Huỷ bỏ mọi SubTotal đã thực hiện

8 Chọn OK Kết thúc tính tổng

Bảng 2.3. Bảng các thông số hộp thoại Subtotal

Hình 2.5: Hộp thoại trợ giúp SubTotal

 Để huỷ bỏ kiểu hiển thị SubTotal, ta thực hiện nhƣ sau:

- Chọn toàn bộ vùng A2:G16.

- Chọn Data /Subtotals  xuất hiện hộp thoại Subtotal.

- Chọn Remove All.

Hình 2.6: Kết quả trên bảng tính khi sử dụng chức năng SubTotal

 Phần 1 chúng ta đã làm quen với các hàm thông dụng của Excel, tuy nhiên khi ta muốn tính toán (đếm, tính tổng, thống kê ...) căn cứ vào 1 vài điều kiện nào đó thì không thể

2.5. Các hàm trên Cơ sở dữ liệu

thực hiện đƣợc, muốn giải quyết đƣợc ta phải sử dụng các hàm trên CSDL.

Các hàm trên Cơ sở dữ liệu

Các đối Tên hàm Giải thích Công dụng số

Tính tổng trên một cột (field) Database Vùng CSDL

của CSDL (database) thoả Trƣờng (cột) cần tính Field mãn điều kiện ghi trong vùng tổng điều kiện (criteria). Lƣu ý DSUM (các đối số) với thông số field ta điền số

Vùng chứa các điều thứ tự của cột (tính từ trái Criteria kiện để tính tổng sang) chứ không điền tên

trƣờng.

Database Vùng CSDL Tính giá trị trung bình cộng

Trƣờng (cột) cần tính trên một cột (field) của DAVERAGE(các Field trung bình CSDL (database) thoả mãn đối số) điều kiện ghi trong miền tiêu Vùng chứa các điều Criteria chuẩn (criteria) kiện để tính TB

Database Vùng CSDL Tính giá trị lớn nhất trên một

Trƣờng (cột) cần tìm cột (field) của CSDL DMAX (các đối Field giá trị lớn nhất (database) thoả mãn điều số) kiện ghi trong miền tiêu Vùng chứa các điều Criteria chuẩn (criteria) kiện để tìm

Database Vùng CSDL Tính giá trị nhỏ nhất trên một

Trƣờng (cột) cần tìm cột (field) của CSDL Field DMIN (các đối số) giá trị nhỏ nhất (database) thoả mãn điều

kiện ghi trong miền tiêu Vùng chứa các điều Criteria chuẩn (criteria) kiện để tìm

Database Vùng CSDL Đếm số bản ghi của CSDL

DCOUNT (các đối (database) trên cột số (field) Field Trƣờng (cột) cần đếm

số) thoả mãn điều kiện ghi trong Vùng chứa các điều Criteria miền tiêu chuẩn (criteria) kiện để đếm

Database Vùng CSDL Đếm số ô không rỗng của cột

DCOUNTA (các Field Trƣờng (cột) cần đếm bất kỳ (field) thoả mãn điều

đối số) kiện ghi trong miền tiêu Vùng chứa các điều Criteria chuẩn (criteria). kiện để đếm

Bảng 2.4. Các hàm trên Cơ sở Dữ liệu

 Ví dụ: Trong tháng 5 năm 2007 công ty CANHCAM có bảng lƣơng nhƣ hình trên. Ngƣời quản lý muốn biết các số liệu sau: Tổng lƣơng của nữ giới có tuổi >30, Lƣơng trung

Hình 2.7: Sử dụng các hàm trên CSDL

 Tạo ra 6 vùng điều kiện nhƣ hình dƣới đây, mỗi 1 vùng để phục vụ cho 1 yêu cầu cụ thể (lƣu ý: không nhất thiết ta phải tạo cả 6 vùng cùng 1 lúc, ở đây tạo cả 6 vùng cùng 1 lúc chủ yếu để cho việc giải thích đƣợc ngắn gọn)

bình nam giới có tuổi <35, Lƣơng thấp nhất của nữ giới tuổi dƣới 30, Lƣơng cao nhất của nam giới trên 35 tuổi, Số nhân viên nữ có lƣơng > 2 triệu, Số nhân viên nam có lƣơng < 1 triệu. Để có đƣợc các thông tin đó, ta giải quyết nhƣ sau:

Hình 2.8: Điền dữ liệu vào các vùng điều kiện

Vùng Vị trí Giải thích

1 Từ ô A22  E23 Đây chính là vùng Criteria (vùng điều kiện) của yêu cầu 1, tính Tổng lƣơng của nữ giới có tuổi >30

2 Từ ô A25  E26 Đây chính là vùng Criteria (vùng điều kiện) của yêu cầu 2, tính Lƣơng trung bình nam giới có tuổi <35

3 Từ ô A28  E29 Đây chính là vùng Criteria (vùng điều kiện) của yêu cầu 3, tính Lƣơng thấp nhất của nữ giới tuổi dƣới 30

4 Từ ô A31  E32 Đây chính là vùng Criteria (vùng điều kiện) của yêu cầu 4, tính Lƣơng cao nhất của nam giới trên 35 tuổi

5 Từ ô A34  E35 Đây chính là vùng Criteria (vùng điều kiện) của yêu cầu 5, tính Số nhân viên nữ có lƣơng > 2 triệu

6 Từ ô A37  E38 Đây chính là vùng Criteria (vùng điều kiện) của yêu cầu 6, tính Số nhân viên nam có lƣơng < 1 triệu

 Tổng lƣơng của nữ giới có tuổi >30, ta thực hiện theo các bƣớc sau:

Bẩng 2.5. Các bƣớc thực hiện vùng điều kiện trên CSDL

Thao tác Giải thích Các bƣớc

1 Đƣa con trỏ chuột về ô E14 Chuẩn bị nhập công thức vào ô E14

Ta nhập hàm DSUM vào ô E14 với các thông số nhƣ trên. Trong đó:

A2:E13 là vùng CSDL. 2 =DSUM(A2:E13;5;A22:E23)

5: là số thứ tự của cột cần tính tổng.

A22:E23 là vùng điều kiện lọc

3 Enter Kết thúc việc nhập hàm

 Lƣơng trung bình nam giới có tuổi <35, ta thực hiện theo các bƣớc sau:

Bảng 2.6. Các bƣớc thực hiện hàm Dsum

Thao tác Giải thích Các bƣớc

1 Đƣa con trỏ chuột về ô E15 Chuẩn bị nhập công thức vào ô E15

Ta nhập hàm DAVERAGE vào ô E15 với các thông số nhƣ trên. Trong đó:

A2:E13 là vùng CSDL. 2 =DAVERAGE(A2:E13;5;A25:E26)

5: là số thứ tự của cột cần tính trung bình.

A25:E26 là vùng điều kiện lọc

3 Enter Kết thúc việc nhập hàm

Bảng 2.7. Các bƣớc thực hiện hàm Daverage

 Lƣơng thấp nhất của nữ giới tuổi dƣới 30, ta thực hiện theo các bƣớc sau:

Thao tác Giải thích Các bƣớc

1 Đƣa con trỏ chuột về ô E16 Chuẩn bị nhập công thức vào ô E16

Ta nhập hàm DMIN vào ô E16 với các thông số nhƣ trên. Trong đó:

A2:E13 là vùng CSDL. 2 =DMIN(A2:E13;5;A28:E29)

5: là số thứ tự của cột cần tìm giá trị nhỏ nhất.

A28:E29 là vùng điều kiện lọc

3 Enter Kết thúc việc nhập hàm

 Lƣơng cao nhất của nam giới trên 35 tuổi, ta thực hiện theo các bƣớc sau:

Bảng 2.8. Các bƣớc thực hiện hàm Dmin

Thao tác Giải thích Các bƣớc

1 Đƣa con trỏ chuột về ô E17 Chuẩn bị nhập công thức vào ô E17

Ta nhập hàm DMAX vào ô E17 với các thông số nhƣ trên. Trong đó:

A2:E13 là vùng CSDL. 2 =DMAX(A2:E13;5;A31:E32)

5: là số thứ tự của cột cần tìm giá trị lớn nhất.

A31:E32 là vùng điều kiện lọc

3 Enter Kết thúc việc nhập hàm

 Số nhân viên nữ có lƣơng > 2 triệu, ta thực hiện theo các bƣớc sau:

Bảng 2.9. Các bƣớc thực hiện hàm Dmax

Thao tác Giải thích Các bƣớc

1 Đƣa con trỏ chuột về ô E18 Chuẩn bị nhập công thức vào ô E18

Ta nhập hàm DCOUNT vào ô E18 với các thông số nhƣ trên. Trong đó:

A2:E13 là vùng CSDL. 2 =DCOUNT(A2:E13;5;A34:E35)

5: là số thứ tự của cột cần đếm.

A34:E35 là vùng điều kiện lọc

3 Enter Kết thúc việc nhập hàm

 Số nhân viên nam có lƣơng < 1 triệu, ta thực hiện theo các bƣớc sau:

Bảng 2.10. Các bƣớc thực hiện hàm Dcount

Các Thao tác Giải thích bƣớc

1 Đƣa con trỏ chuột về ô E19 Chuẩn bị nhập công thức vào ô E19

Ta nhập hàm DCOUNTA vào ô E19 với các

thông số nhƣ trên. Trong đó:

A2:E13 là vùng CSDL. 2 =DCOUNTA(A2:E13;5;A37:E38)

5: là số thứ tự của cột cần đếm

A37:E38 là vùng điều kiện lọc

3 Enter Kết thúc việc nhập hàm

Bảng 2.11. Các bƣớc thực hiện hàm Dcounta

2.6. Đặt lọc dữ liệu:

 Đối với những CSDL lớn, để có đƣợc thông tin dƣới dạng rút gọn ta phải thực hiện

2.6.1. Các thông số cơ bản để thực hiện việc lọc dữ liệu:

thao tác lọc trên CSDL đó, việc lọc dữ liệu đơn giản là việc ta muốn các dữ liệu trên bảng

tính chỉ hiện những thông tin cần thiết đối với chúng ta, các thông tin dƣ thừa khác (thông

tin không cần thiết) có thể ẩn đi.

 Để thực hiện lọc 1 vùng CSDL, ta phải xác định các thông số cơ bản sau:

Thông số Giải thích

Database Miền dữ liệu: Nơi chứa toàn bộ dữ liệu cần lọc, kể cả hàng tiêu đề.

Criteria

Vùng chứa điều kiện lọc (vùng tiêu chuẩn): Là 1 vùng bất kỳ trên bảng tính ngoài vùng CSDL, chứa các tiêu chuẩn (điều kiện lọc mà các bản ghi phải thải mãn). Miền tiêu chuẩn gồm tối thiểu 2 hàng, hàng đầu chứa tiêu đề của miền tiêu chuẩn. Các tiêu đề này hoặc là tên trƣờng hoặc là tên bất kỳ phụ thuộc vào phƣơng pháp thiết lập tiêu chuẩn (trực tiếp hay gián tiếp). Từ hàng thứ hai trở đi là tiêu chuẩn của CSDL.

 Ở đây, ta sẽ phân biệt thế nào là 1 vùng tiêu chuẩn trực tiếp thế nào là 1 vùng tiêu

Bảng 2.12. Các thông số của lọc 1 vùng CSDL

chuẩn gián tiếp:

Vùng tiêu chuẩn trực tiếp

Hình thái thể hiện Các nguyên tắc tạo lập

Hàng đầu ghi tiêu đề cho các tiêu chuẩn, lấy tên trƣờng làm tiêu đề

Hàng thứ hai trở đi để ghi các điều kiện lọc, trƣớc các giá trị đó có thể thêm các toán tử so sánh nhƣ >, >=,<, <=

Vùng tiêu chuẩn trực tiếp là vùng mà ta có thể nhập trực tiếp các điều kiện để lọc mà không cần phải thông qua phép toán xử lý nào. Các tiêu chuẩn trên cùng 1 hàng đƣợc thực hiện đồng thời (tƣơng đƣơng nhƣ phép toán và AND)

Các tiêu chuẩn trên các hàng khác nhau đƣợc thực hiện không đồng thời (tƣơng đƣơng phép toán hoặc OR)

Bảng 2.13. Các nguyên tắc tạo lập vùng tiêu chuẩn trực tiếp

Vùng tiêu chuẩn gián tiếp

Hình thái thể hiện Các nguyên tắc tạo lập

Hàng đầu ghi tiêu đề cho các tiêu chuẩn (Lƣu ý: Tiêu đề này có thể đặt tên bất kỳ nhƣng không đƣợc trùng với tên trƣờng nào của vùng CSDL) Vùng tiêu chuẩn gián tiếp là vùng mà ta không thể nhập trực tiếp các điều kiện để lọc

mà phải thông qua 1 phép toán xử lý nào đó lên trƣờng cần lọc.

Từ hàng thứ hai trở đi ghi các điều kiện lọc, mỗi điều kiện là 1 công thức. Công thức (đƣợc bắt đầu với dấu =) này phải chứa địa chỉ của bản ghi đầu tiên (hàng đầu tiên của CSDL). Kết quả thực hiện công thức này là một giá trị Logic : TRUE (đúng) hoặc FALSE (sai)

Các tiêu chuẩn trên cùng 1 hàng đƣợc thực hiện đồng thời (tƣơng đƣơng nhƣ phép toán và AND)

Các tiêu chuẩn trên các hàng khác nhau đƣợc thực hiện không đồng thời (tƣơng đƣơng phép toán hoặc OR)

 Để hiểu rõ hơn thế nào là 1 vùng tiêu chuẩn trực tiếp hay gián tiếp ta sẽ xét ở các ví

Bảng 2.14. Các nguyên tắc tạo lập vùng tiêu chuẩn gián tiếp

dụ phần lọc nâng cao (Advanced Filter) dƣới đây.

2.6.2. Các kiểu lọc :

 Công dụng: Khi sử dụng tính năng lọc tự động (lọc đơn giản), Excel sẽ tự động phân nhóm trên các cột, công việc còn lại của ta chỉ là chọn nhóm nào sẽ đƣợc hiện trên màn hình bảng tính.

2.6.2.1. Lọc tự động (AutoFilter):

 Ví dụ minh hoạ: Có bảng công việc thực hiện tại công ty quảng cáo BOCAP nhƣ bảng trên, thứ tự thực hiện công việc của các phân xƣởng chỉ đƣợc nhập vào theo ngày

Hình 2.9: Lọc tự động Autofilter

tháng, đến cuối tháng ngƣời quản lý muốn biết phân xƣởng nào (giả sử phân xƣởng 3) đã làm đƣợc những việc gì trong tháng, để có đƣợc thông tin trên ta làm nhƣ sau:

Các bƣớc Thao tác Giải thích

Chọn miền dữ liệu định lọc (kể cả hàng 1 Chọn miền A2  E13 tiêu đề)

Vào menu Data /Filter Excel tự động chèn những mũi tên vào 2 /AutoFilter bên phải của các tên trƣờng

Chọn cột [Phân xƣởng thực 3 Bắt đầu lọc hiện]

Chọn một trong các mục đƣợc Lọc để hiển thị trên màn hình chỉ có 4 hiển thị, chọn phân xƣởng 3 các thông tin về phân xƣởng 3

 Ý nghĩa các thông số của menu lọc tự động:

Bảng 2.15. Các bƣớc thực hiện lọc dữ liệu tự động

Thứ tự Thông số Giải thích

1 All Hiện toàn bộ các bản ghi

2 Blanks Chỉ hiện các bản ghi trống

3 Nonblanks Chỉ hiện các bản ghi không trống

4 Custom Thêm 1 vài điều kiện để lọc

 Khi lựa chọn Custom (sử dụng các toán tử so sánh)  xuất hiện hộp thoại sau với 2 khung nhỏ để ghi thêm các điều kiện lọc. Giả sử ta chỉ muốn hiển thị trên bảng tính phân xƣởng 1 và phân xƣởng 3 ta làm nhƣ sau:

Bảng 2.16. Thông số của Menu lọc tự động

Hình 2.10: Hộp thoại lọc tự động Autofilter khi chọn Custom

TT Thao tác Giải thích

1 Tại ô trắng trên cùng bên tay trái chọn Equal (bằng) Chọn điều kiện bằng (=)

2 Điều kiện lọc 1 Tại ô trắng trên cùng bên tay phải ta gõ [Phân xƣởng 1]

3 Kích chọn vào hộp lựa chọn Or (hoặc) Điều kiện lọc 1 hoặc là ...

4 Tại ô trắng dƣới cùng bên tay trái chọn Equal (bằng) Chọn điều kiện bằng (=)

5 Điều kiện lọc 2 Tại ô trắng trên cùng bên tay phải ta gõ [Phân xƣởng 3]

6 Chọn OK Hiển thị kết quả lọc

 Ý nghĩa của các thông số còn lại trong hộp thoại Custom:

Bảng 2.17. Các bƣớc lọc tự động Autofilter khi chọn Custom

TT Thông số Giải thích

1 Equals Bằng (=)

2 Does not equal Không bằng (không phải là)

3 Is greater than Lớn hơn (>)

4 Is greater than or equal to Lớn hơn hoặc bằng (>=)

5 Is less than Nhỏ hơn (<)

6 Is less than or equal to Nhỏ hơn hoặc bằng (<=)

7 Begin with Khởi đầu bằng ...

8 Does not begin with Khởi đầu không bằng ...

9 End with Kết thúc là ...

10 Does not end with Kết thúc không là ...

 Khi muốn hiển thị trở lại bình thƣờng ta làm nhƣ sau:

Bảng 2.18. Các thông số trong hộp thoại lọc tự động Autofilter

TT Mục đích Thao tác

1 Huỷ lọc trong 1 cột Bấm nút chuột tại của cột đó, chọn All

2 Huỷ lọc toàn bộ Data /Filter /chọn AutoFilter và xoá dấu 

Hiện lại tất cả các hàng 3 Data /Filter /Show All trong danh sách đƣợc lọc

Bảng 2.19. Khôi phục lại dữ liệu ban đầu

 Công dụng: Lọc nâng cao (Advanced Filter) dùng để tìm các bản ghi thoả mãn các điều kiện phức tạp hơn. Khi thực hiện chức năng lọc này bắt buộc phải dùng miền tiêu chuẩn (điều kiện lọc).

 Các bƣớc thực hiện nhƣ sau:

2.6.2.2. Lọc nâng cao (Advanced Filter)

TT Thao tác Giải thích

1 Tạo miền tiêu chuẩn (các điều kiện lọc) Nhập vào các điều kiện lọc

2 Chọn vùng CSDL định lọc Bắt đầu lọc

3 Sử dụng tính năng lọc nâng cao Vào menu Data /Filter /Chọn Advanced Filter

 Ý nghĩa các mục trong hộp thoại Advanced Filter nhƣ sau :

Bảng 2.20. Các bƣớc thực hiện lọc nâng cao

Hình 2.11: Hộp thoại lọc Advanced Filter

TT Thông số Giải thích

1 Filter the List, in place Lọc ngay tại vùng CSDL

Lọc ra vùng khác, trích các bản ghi đạt tiêu chuẩn Copy to Another lọc sang miền khác của bảng tính, địa chỉ của miền 2 Location này đƣợc xác định tại khung Copy to

List Range Vùng CSDL nguồn muốn lọc 3

Criteria Range Vùng chứa điều kiện lọc 4

Copy to Miền đích để chứa các bản ghi đạt tiêu chuẩn lọc 5

6 Unique Record Only Chỉ hiện 1 bản ghi trong số các bản ghi trùng nhau

Bảng 2.21. Các thông số trong hộp thoại Advance Filter

 Ví dụ 1: Thông tin về các cá nhân của công ty CHAUCHAU nhƣ bảng trên, ngƣời quản lý muốn có danh sách các nữ nhân viên có có quê ở Hà Nội, để có đƣợc danh sách đó ta làm nhƣ sau:

Hình 2.12: Minh hoạ cách sử dụng lọc Advanced Filter (vùng điều kiện trực tiếp)

TT Thao tác Giải thích

1 Tạo miền tiêu chuẩn từ ô A16  F17 Đây chính là vùng tiêu chuẩn trực tiếp

2 Chọn vùng A2  F12 Đây là vùng CSDL muốn lọc

3 Sử dụng tính năng lọc nâng cao Vào menu Data /Filter /Chọn Advanced Filter

4 Gõ $A$2:$F$12 vào ô List Range Địa chỉ của vùng CSDL muốn lọc

5 Gõ $A$16:$F$17 vào ô Criteria Range Địa chỉ của vùng tiêu chuẩn

6 Chọn OK Kết thúc lọc, hiển thị kết quả

 Ví dụ 2: Cũng tại công ty CHAUCHAU nhƣ bảng trên, cuối năm các nhân viên của Sở Lao động xuống kiểm tra về tình hình lao động của công ty, muốn biết đƣợc liệu công ty có vi phạm các quy định trong Luật lao động nhƣ: Có sử dụng ngƣời dƣới 18 tuổi không; Thu nhập cả năm có dƣới 6 triệu không? Để giải quyết các vấn đề đó ta làm nhƣ sau:

Bảng 2.22. Các bƣớc thực hiện lọc bằng Advace Filter

Hình 2.13: Minh hoạ cách sử dụng lọc Advanced Filter (vùng điều kiện gián tiếp)

TT Thao tác Giải thích

1 Tạo miền tiêu chuẩn từ ô A16  F17 Đây là vùng tiêu chuẩn gián tiếp

2 C17 =(YEAR(TODAY())-YEAR(C3))<18

100

Gõ vào ô C17 với nội dung nhƣ trên để kiểm tra xem lao động có dƣới 18 tuổi hay không (năm hiện tại - năm sinh), ở đây cho ta FALSE vì Hải sinh năm 1978 đã lớn hơn 18 tuổi

F18=F3<6000000

Gõ vào ô F18 (lƣu ý: gõ vào ô F18 chứ không phải ô F17 vì đây là phép toán Hoặc nên không đƣợc cùng dòng 17) với nội dung nhƣ trên để xem tổng thu nhập có <6.000.000 hay không, ở đây cho ta kết quả FALSE vì Hải thu nhập >6.000.000

3 Chọn vùng A2  F12 Đây là vùng CSDL muốn lọc

/Filter /Chọn 4 Sử dụng tính năng lọc nâng cao Vào menu Data Advanced Filter

5 Gõ $A$2:$F$12 vào ô List Range Địa chỉ của vùng CSDL muốn lọc

6 Địa chỉ của vùng tiêu chuẩn Gõ $A$16:$F$18 vào ô Criteria Range

7 Chọn OK Kết thúc lọc, hiển thị kết quả

Bảng 2.23. Các bƣợc thực hiện lọc Advanced Filter (vùng điều kiện gián tiếp)

2.7. Tạo bảng tổng hợp Pivot Table

 Đây là 1 trong những chức năng mạnh nhất của Excel, chức năng này cho phép tự động hoá quá trình tổng kết 1 bảng dữ liệu nào đó theo nhiều loại nhóm (SubTotal chỉ tổng kết đƣợc theo 1 nhóm) giúp ta có cái nhìn tổng thể về nhiều mặt, chúng ta thƣờng gọi đó là Bảng tổng hợp.

101

2.7.1. Công dụng:

 Ví dụ: Doanh nghiệp SAHA, mặt hàng chủ yếu của công ty là giấy văn phòng, ngoài ra doanh nghiệp còn sản xuất các loại mặt hàng phụ là kẹp giấy, dập ghim, đục lỗ. Cuối tháng 5 có báo cáo về tình hình doanh thu tháng về các sản phẩm nhƣ bảng trên. Mong muốn của ngƣời quản lý muốn biết đƣợc doanh thu theo [Tên sản phẩm] và doanh thu tƣơng ứng với [tên đại lý] theo [Ngày bán] để có đƣợc chế độ khuyến khích tƣơng ứng. Muốn thực hiện đƣợc điều đó ta phải sử dụng tính năng Pivot Table. Kết quả sau khi sử dụng tính năng PivotTable nhƣ hình dƣới:

Hình 2.14: Minh hoạ cách tạo bảng tổng hợp Pivot Table

 Các bƣớc thực hiện nhƣ sau:

Hình 2.15: Kết quả khi tạo 1 bảng tổng hợp Pivot Table

TT Thao tác Giải thích

1 Chọn các ô A2  G17 Chọn vùng dữ liệu để tạo bảng tổng hợp

102

2 Vào menu Data /PivotTable Sử dụng Pivot để tạo bảng tổng hợp

3 Xuất hiện hộp thoại Pivot (Step1) với các lựa chọn Bắt đầu nhập các lựa chọn để tạo bảng

4 Chọn Microsoft Excel list or Database Chọn dữ liệu từ bảng Excel

5 Chọn PivotTable Tạo bảng tổng hợp (không tạo đồ thị)

6 Chọn Next Chuyển sang Pivot (Step2)

7 Nhập các lựa chọn Xuất hiện hộp thoại Pivot (Step2) với các lựa chọn

8 Gõ vào $A$2:$G$17 Đây chính là vùng chứa DL để tạo bảng

9 Chọn Next Chuyển sang Pivot (Step3)

10 Nhập các lựa chọn Xuất hiện hộp thoại Pivot (Step3) với các lựa chọn

11 Chọn New worksheet Bảng tổng hợp sau khi tạo xong sẽ đƣợc lƣu vào 1 sheet khác

12 Chọn Layout Bắt đầu nhập tên các trƣờng

13 Xuất hiện hộp thoại Layout Kéo thả tên các trƣờng vào vùng tƣơng ứng

14 [Tên sản phẩm] sẽ là trƣờng tổng thể Kéo thả trƣờng [Tên sản phẩm] vào {PAGE}

trƣờng [Tên đại lý] 15 [Tên đại lý] sẽ đƣợc bố trí theo cột thả Kéo vào{COLUMN}

16 Kéo thả trƣờng [Ngày bán] vào{ROW} [Ngày bán] sẽ đƣợc bố trí theo hàng

17 Kéo thả trƣờng [Thành tiền] vào{DATA} Đây là trƣờng sẽ đƣợc tính toán (nhất thiết phải có) theo hàm SUM

103

18 Chọn OK để xem bảng tổng hợp Kết thúc tạo bảng

Bảng 2.24. Các bƣớc thực hiện Pivot Table

Hình 2.16: Hộp thoại Pivot Step1

104

Hình 2.17: Hộp thoại Pivot Step2

Hình 2.18: Hộp thoại Pivot Step3

Hình 2.19: Hộp thoại Layout để thực hiện kéo thả

 Ở bảng CSDL trên, tiêu đề của các cột là các trƣờng [Tên sản phẩm], [Tên đại lý], [Ngày bán], [Thành tiền], các trƣờng này sẽ tham gia vào việc xây dựng bảng tổng hợp. Bảng tổng hợp đƣợc chia làm 4 vùng :

2.7.2. Các thông số cơ bản của PivotTable:

TT Tên vùng Giải thích

1 Page

Toàn bộ dữ liệu đƣợc tổng kết theo từng nhóm của trƣờng này. Page luôn nằm ở phía trên của bảng tổng hợp. Trong ví dụ này, Page là trƣờng [Tên sản phẩm]

2 Row

Mỗi nhóm dữ liệu của trƣờng này đƣợc tổng kết trên một dòng, vì vậy đƣợc gọi là "Row". Nếu số trƣờng kéo vào Row nhiều hơn 1, PivotTable sẽ tổng kết các trƣờng này theo kiểu lồng nhau theo thứ tự từ trên xuống dƣới. Trong ví dụ này, chỉ có 1 trƣờng là [Tên đại lý]

3 Column

Mỗi nhóm dữ liệu của trƣờng này đƣợc tổng kết trên một cột, vì vậy đƣợc gọi là "Column". Nếu số trƣờng kéo vào Column này nhiều hơn 1, PivotTable sẽ tổng kết các nhóm này theo thứ tự từ trái qua phải. Trong ví dụ này, chỉ có 1 trƣờng [Ngày bán]

4 Data

105

Vùng chính của bảng tổng hợp ghi kết quả của một phép toán. Trong ví dụ này, số liệu cuả trƣờng [Thành tiền] đƣợc đƣa vào và phép toán tổng kết là lấy tổng (SUM)

Bảng 2.25. Các thông số cơ bản của PivotTable:

2.7.3. Tạo mới 1 bảng tổng hợp:

TT Thao tác Giải thích

1 Chọn vùng dữ liệu để tạo bảng tổng hợp Chọn miền dữ liệu kể cả tiêu đề của các cột

2 Vào menu Data /PivotTable Sử dụng Pivot để tạo bảng tổng hợp

3 Bắt đầu nhập các lựa chọn để tạo bảng Xuất hiện hộp thoại Pivot (Step1) với các lựa chọn

4 Chọn dữ liệu từ bảng Excel Chọn Microsoft Excel list or Database

5 Chọn PivotTable Tạo bảng tổng hợp (không tạo đồ thị)

6 Chọn Next Chuyển sang Pivot (Step2)

7 Nhập các lựa chọn Xuất hiện hộp thoại Pivot (Step2) với các lựa chọn

8 Đây chính là vùng chứa DL để tạo bảng Gõ vào địa chỉ vùng chứa dữ liệu để tạo bảng

9 Chọn Next Chuyển sang Pivot (Step3)

10 Nhập các lựa chọn Xuất hiện hộp thoại Pivot (Step3) với các lựa chọn

11 Nếu chọn New worksheet Bảng tổng hợp sau khi tạo xong sẽ đƣợc lƣu vào 1 sheet mới, ngay tiếp sau sheet hiện tại.

12 Nếu chọn Exiting workshett

Bảng tổng hợp sau khi tạo xong sẽ đƣợc lƣu vào sheet hiện tại, ta phải chọn vị trí trong bảng để đặt bảng tổng hợp

12 Chọn Layout Bắt đầu nhập tên các trƣờng

13 Xuất hiện hộp thoại Layout Kéo thả tên các trƣờng vào vùng tƣơng ứng

14 Đây là trƣờng tổng thể để quan sát Kéo thả trƣờng tổng thể vào {PAGE}

106

15 Các trƣờng đó sẽ đƣợc bố trí theo cột Kéo thả trƣờng cần sắp xếp theo cột vào {COLUMN}

16 Các trƣờng này sẽ đƣợc bố trí theo hàng Kéo thả trƣờng cần sắp xếp theo hàng vào {ROW}

17 Kéo thả 1 trƣờng cần tính toán vào{DATA} Đây là trƣờng sẽ đƣợc tính toán theo 1 hàm nhất định (nhất thiết phải có), dữ liệu của trƣờng này sẽ đƣợc phân bố trên từng ô.

18 Chọn OK để xem bảng tổng hợp Kết thúc tạo bảng

 Trong hộp thoại PivotTable (Step3) nếu ta chọn Option thì sẽ điền thêm đƣợc 1 vài

Bảng 2.26. Các bƣớc thực hiện tạo mới 1 bảng Pivot Table

thông số sau:

TT Thông số Giải thích

1 Name Gõ vào tên của bảng tổng hợp

2 Grand Total For Columns Tạo thêm cột tổng cho mỗi số liệu của Column

3 Grand Total For Rows Tạo thêm hàng tổng cho mỗi số liệu của Row

4 Save Data With Table Layout Bảng tổng hợp đƣợc lƣu trữ cùng với Format đƣợc tạo

5 AutoFormat Table Tự động tạo khuôn cho bảng tổng hợp

Bảng 2.27. Các thông số khác khi sử dụng Option trong hộp thoại Pivot Table

2.7.4. Sửa đổi 1 bảng tổng hợp:

TT Mục đích Thao tác

1 Thay đổi vị trí các trƣờng Trong bảng tổng hợp kéo và thả tên trƣờng tại vị trí mong muốn.

Chọn ô bất kỳ của bảng tổng hợp

Chọn mục Data /PivotTable 2 Bổ sung các trƣờng

Thực hiện lại các bƣớc nhƣ khi tạo mới bảng tổng hợp để bổ sung trƣờng vào các vị trí mong muốn

3 Xoá trƣờng Trong bảng tổng hợp kéo tên trƣờng cần xoá ra khỏi bảng

4 Sửa tên trƣờng

107

Đƣa con trỏ ô về trƣờng cần thiết trong bảng tổng hợp và tiến hành sửa tên nhƣ sửa dữ liệu của bảng tính

Bảng 2.28. Các bƣớc sửa 1 bảng tổng hợp

 Ở chế độ mặc định số liệu của vùng DATA đƣợc lấy tổng và các cột hoặc hàng Total cũng đƣợc lấy tổng. Có thể thay đổi các cách tính này bằng các hàm số khác nhƣ đếm (Count), tính trung bình (Average), tìm cực đại (Max), tìm cực tiểu (Min). Các bƣớc nhƣ sau:

2.7.5. Thay đổi hàm số tính toán:

TT Thao tác

1 Trong bảng tổng hợp chọn trƣờng trƣớc đó ta đã đƣa vào vùng DATA

2 Chọn mục Data /PivotTable Field  xuất hiện hộp thoại

3 Chọn hàm tƣơng ứng danh sách Summarize by

4 Chọn OK

Bảng 2.28. Thay đổi hàm số tính toán trong bảng tổng hợp

2.7.6. Tự động điều chỉnh bảng tổng hợp khi dữ liệu gốc thay đổi:

TT Thao tác

1 Sau khi sửa số liệu của CSDL

2 Chọn ô bất kỳ của Bảng Tổng hợp

3 Chọn mục Data, Refresh Data

 Lƣu ý: Với ví dụ đầu tiên của chúng ta là 1 ví dụ đơn giản nhất về tạo bảng tổng hợp, về sau khi đã sử dụng thành thạo chức năng này chúng ta có thể tự tạo những bảng tổng hợp tuỳ biến theo mong muốn.

Bảng 2.29. Điều chỉnh bảng tổng hợp khi dữ liệu gốc thay đổi

2.8. Vẽ đồ thị

 Đồ thị (Graph) đƣợc sử dụng để diễn tả sự phân bố của các đại lƣợng dƣới dạng

2.8.1. Khái niệm về đồ thị:

 Đồ thị cột bao gồm một trục đánh dấu các mốc và các cột biểu diễn giá trị tại các

hình ảnh.

 Ví dụ: Hãy thể hiện bằng đồ thị doanh thu của các sản phẩm qua các năm 2005;

mốc của các đại lƣợng.

108

2006; 2007. Kết quả đƣợc thể hiện nhƣ hình dƣới đây.

Sản phẩm Năm 2005 Năm 2006 Năm 2007

Bóng đèn 1000 1200 1300

Phích nƣớc 1500 1400 1600

Bình sứ 1200 1500 1700

Bảng 2.30. Doanh thu của các sản phẩm qua các năm

Hình 2.20. Đồ thị doanh thu sản phẩm trong 3 năm

 Bƣớc 1: Chọn kiểu đồ thị

Chart Type (kiểu đồ thị):

 Column: dạng cột dọc.

 Bar: dạng thanh ngang.

 Line: dạng đƣờng.

 Pie: bánh tròn.  XY: Đƣờng, trục X là số.

 Area: dạng vùng.

 Doughtnut: băng tròn.

 3-D Column: Cột 3 chiều



2.8.2. Chèn đồ thị vào bảng tính.

 Surface: dạng bề mặt.

109

Radar: Toạ độ cực.

 Bubble: dạng bong bóng.

 Stock: 3 dãy (cao, thấp, khớp).

Chart sub-type: kiểu cụ thể của kiểu đã chọn.

Các kiểu chuẩn

Các kiểu tuỳ biến

Xem tên & lời giải thích của kiểu đồ thị

 Bƣớc 2: Xác định dữ liệu

Hình 2.21. Hộp thoại các kiểu đồ thị

Data Range

Miền dữ liệu vẽ đồ thị

Các đại lượng được bố trí theo:

Hàng (Rows)

Cột (Columns)

110

Hình 2.22. Hộp thoại lựa chọn kiểu bố trí dữ liệu

Series

Các đại lượng (series)

Tên (name) của đại lượng

Miền dữ liệu của đại lượng

Thêm (add), bỏ bớt (remove) đại

lượng được lựa chọn

Nhãn của trục X

 Bƣớc 3: Một số thuộc tính khác (tiêu đề của đồ thị)

Hình 2.23. Hộp thoại hiển trị các thông số để vẽ đồ thị

Titles - Các thuộc tính tiêu đề

Chart title - Tiêu đề đồ thị

Category (X) axis - Tiêu đề trục X

Value (Y) axis - Tiêu đề trục Y

Các kiểu đồ thị khác nhau có thể có các thuộc tính khác nhau

111

Hình 2.24. Hộp thoại thể hiện tiêu đề các trục

Hiện trục X -Vạch chia tự động -Vạch chia mặc định -Vạch chia dạng thời gian

Hiện trục Y

Hình 2.25. Hộp thoại hiện trục tọa độ

Bƣớc 3: Một số thuộc tính khác (các lƣới kẻ ô)

Lƣới kẻ ô trục X: Major gridlines - lƣới ô chính Minor gridelines - lƣới ô phụ

Lƣới kẻ ô trục Y: Major gridlines - lƣới ô chính Minor gridelines - lƣới ô phụ

112

Hình 2.26. Hộp thoại thuộc tính các lƣới kẻ ô

Show legend - hiển thị chú giải Vị trí hiển thị (bottom - phía dƣới, top - phía trên, …)

Hình 2.27. Hộp thoại thuộc tính chú giải: legend

Series name - hiện tên nhãn dữ liệu

Category name - hiện giá trị trên trục mốc lên đồ thị

Value - Hiện giá trị lên đồ thị

113

Hình 2.28. Hộp thoại thuộc tính chọn nhãn cho dữ liệu

thị bảng dữ

liệu

Show data table - Hiển kèm đồ thị

thị ký hiệu

(hình

Show legend keys - Hiển ảnh) chú giải

Hình 2.29. Hộp thoại thuộc tính khác hiển thị bảng dữ liệu

 Bƣớc 4: Chọn vị trí đặt đồ thị:

Đặt đồ thị tại một trang tính mới có tên là chart1

Đặt đồ thị tại trang tính đã có tên là sheet1

Kích chuột vào nút Finish để hoàn tất quá trình chèn đồ thị vào trang tính

114

Hình 2.30. Hộp thoại để chèn đồ thị vào trang tính

 Chart Area

 Plot Area

 Data series

 Data points

 Axis

 Title

 Legend

 Legend key

 Legend Entry

 Data table

 Trendline

 Gridlines

 Wall

2.8.3. Các thành phần của đồ thị:

 Floor

 …

2.8.4. Sửa đổi 1 đồ thị đã có sẵn:

Kích chuột phải vào không gian trống trên đồ thị: (hoặc chọn menu Chart) -Format Chart Area: Định dạng -Chart Type: Chọn lại kiểu đồ thị -Source Data: Chọn lại dữ liệu -Chart Options: Các thuộc tính khác -Add Data: Thêm dữ liệu -Add Trendline

Có thể kích chuột phải vào từng đối tượng của đồ thị

để hiện menu con tác động lên riêng đối tượng đó

115

Hình 2.31. Các bƣớc định sửa lại đồ thị

 Định dạng lại Font, màu

Màu và mẫu tô

Phông chữ

 Thêm dữ liệu và đƣờng hồi quy vào đồ thị.

Kích chuột vào menu Chart.

Add Data: Thêm dữ liệu (nhập vùng dữ liệu cần thêm).

Add Trendline: Thêm đƣờng hồi quy.

Chọn kiểu đƣờng hồi quy.

Chọn series

Hình 2.32. Hộp thoại định dạng lại font, màu cho đồ thị

116

Hình 2.33. Hộp thoại thêm dữ liệu và đƣờng hồi quy vào đồ thị

 Nói thêm về Consolidate (ghép dữ liệu trên nhiều bảng thành 1):

Khi báo cáo dữ liệu để ngƣời đọc có thể tiếp nhận thông tin 1 cách trực quan chúng ta có thể sử dụng tính năng vẽ đồ thị. Tuy nhiên để vẽ đồ thị chúng ta cần có dữ liệu, dữ liệu của 1 DN đôi khi nằm rải rác, vì vậy chúng ta cần ghép nối nhiều vùng dữ liệu của bảng tính, thậm chí trên nhiều bảng tính khác nhau thành một vùng kết quả trên bảng tính hiện thời. Trên thực tế số liệu đƣợc cập nhật và góp nhặt từ nhiều nguồn thông tin, nhiều địa điểm và hoàn cảnh khác nhau, do vậy ta phải đƣa tất cả các dữ liệu ở tất cả các bảng vào 1 sheet (tƣơng đƣơng nhƣ tính năng Copy), các công việc vừa nêu ở trên có thể thực hiện bằng tính năng Consolidate.

Các bƣớc thực hiện nhƣ sau:

TT Thao tác

1 Đặt con trỏ tại vị trí định kết xuất kết quả. Thực hiện lệnh Data /Consolidate. Hộp thoại Consolidate xuất hiện

2 Trong dòng Reference gõ hoặc chọn địa chỉ vùng cần tổng hợp. Kích nút Add để bổ sung địa chỉ đó vào hộp All references

3 Trong trƣờng hợp vùng liên kết nằm trên bảng tính khác, chọn Browse và trong danh sách để chọn file gõ địa chỉ vùng dữ liệu gốc

4 Nếu muốn xoá địa chỉ trong hộp All references, kích chuột vào nó và chọn Delete

5 Trong hộp Function chọn hàm số cần tổng hợp

6 Chọn các lựa chọn sau nếu cần thiết:

7 Top Row: Sử dụng hàng trên cùng của các vùng dữ liệu gốc trong vùng đích

8 Left Column: Sử dụng cột đầu tiên bên trái của các vùng dữ liệu trong vùng đích

Create links to Source Data: Tạo liên kết giữa dữ liệu các vùng gốc với kết quả của vùng đích. (Lựa chọn này chỉ đƣợc phép sử dụng khi vùng kết quả nằm ở bảng tính khác với các bảng tính chứa vùng dữ liệu gốc)

Bảng 2.31. Các bƣớc thực hiện ghép dữ liệu trên nhiều bảng thành một

Lƣu ý: Tất cả các bảng dữ liệu phải theo cùng 1 định dạng thống nhất theo quy định

117

của ngƣời quản lý đặt ra.

 Ví dụ: Tại công ty CAOCAO, cuối tháng các đơn vị thành viên phải gửi về máy tính cho ngƣời quản lý 1 File về tình hình sử dụng vật tƣ trong tháng của đơn vị mình, tên các File tƣơng ứng là PHANXUONG1, PHANXUONG2, PHANXUONG3. Trong máy tính của ngƣời quản lý đã có sẵn 1 File tên là TONGHOP nhƣng chƣa có dữ liệu nào trong đó. Nhiệm vụ của ngƣời quản lý là muốn tổng hợp dữ liệu của các đơn vị thành viên vào trong File TONGHOP của mình. Để thực hiện điều đó các bƣớc nhƣ sau:

Hình 2.34: Hộp thoại Consolidate trợ giúp tổng hợp dữ liệu

118

Hình 2.35: Dữ liệu File TONGHOP

Hình 2.36: Dữ liệu File PHANXUONG1

Hình 2.37: Dữ liệu File PHANXUONG2

Hình 2.38: Dữ liệu File PHANXUONG3

Thao tác TT

1 Đặt con trỏ tại ô A2 của File TONGHOP. Thực hiện lệnh Data /Consolidate. Hộp thoại Consolidate xuất hiện

2 Trong dòng Reference gõ hoặc chọn A1  B3 của File PHANXUONG1, chọn Add

4 Trong dòng Reference gõ hoặc chọn A1  B4 của File PHANXUONG2, chọn Add

5 Trong dòng Reference gõ hoặc chọn A1  B4 của File PHANXUONG2, chọn Add

6 Trong hộp Function chọn hàm số cần tổng hợp, hàm SUM

7 Left Column: Sử dụng cột đầu tiên bên trái của các vùng dữ liệu trong vùng đích

119

9 Chọn OK, hiện kết quả

Bảng 2.32. Các bƣớc giải quyết ví dụ sử dụng tính năng Cốnlidate

CÂU HỎI ÔN TẬP, THẢO LUẬN CHƢƠNG 2

Câu 1. Phân biệt sự khác nhau giữa vùng điều kiện tƣơng đối và vùng điều kiện tuyệt đối?

Câu 2. Tại sao nói “Lọc nâng cao là một công cụ vạn năng khi cần lọc dữ liệu, nhƣng lại không đƣợc sử dụng nhiều trong thực tế khi so sánh với Lọc đơn giản?”

Câu 3. Phân biệt sự khác nhau giữa phép toán AND và OR khi lọc dữ liệu?

Câu 4. Trong nhóm hàm Cơ sở dữ liệu các đối số thƣờng tƣơng đối giống nhau, đối số nào là quan trọng nhất, tại sao?

120

Câu 5. Việc tạo lập một bảng tổng hợp dựa trên công cụ Pivot Table đƣợc dùng khi nào? Lấy ví dụ cụ thể.

BÀI TẬP ỨNG DỤNG

BẢNG KÊ THU NHẬP CÁN BỘ CÔNG NHÂN VIÊN 2018

Đơn vị tính: 1.000.000 đ

Ho ten He so Luong Ngay sinh Que quan Gioi tinh Phong ban Ngay cong

3/6/1972 Nữ Kế hoạch 25 3 41 Hải Phòng Đặng Hoàng Phƣơng

1/9/1981 Nam Kỹ thuật 26 3,3 37 Trần Văn Hiệu Nam Định

4/5/1975 Hà Nội Nữ Tài chính 24 4,5 89 Đỗ Hƣơng Xuân

6/7/1980 Lào Cai Nam 23 2,67 110 Văn Thế Dũng Hành chính

2/7/1970 Nữ Dự án 22 3 50 Dƣơng Thị Hồng Quảng Ninh

9/4/1965 Bắc Ninh Nam Tài chính 23 4,1 57 Lê Xuân Tấn

6/2/1983 Nữ Kỹ thuật 21 2,67 95 Vũ Hƣớng Dƣơng Vĩnh Phúc

Câu hỏi:

1. Lọc ra những nhân viên có tên Hải

2. Lọc ra những nhân viên họ Nguyễn

3. Lọc ra những nhân viên họ Lê hoặc họ Vũ

121

4. Lọc ra những nhân viên tên Hồng hoặc Xuân

CHƢƠNG 3: MỘT SỐ TÍNH NĂNG CAO CẤP

MỤC ĐÍCH CỦA CHƢƠNG:

Sau khi nghiên cứu và học tập chƣơng này, sinh viên cần nắm đƣợc:

 Cách thức sử dụng tính năng Goal Seek để dò tìm mục tiêu.  Cách thực sử dụng tính năng Solver để giải quyết một số bài toán tối ƣu thƣờng gặp

trong quản trị

 Cách giải quyết một số bài toán mẫu về lao động, tiền lƣơng, vật tƣ trong quản trị.

NỘI DUNG CHƢƠNG

 Công dụng: Chức năng này sẽ cho kết quả của một ô (ô này gọi là hàm mục tiêu) bằng cách điều chỉnh giá trị của một ô khác (ô này chứa biến bị thay đổi). Các bƣớc thực hiện nhƣ sau:

3.1. Tính năng Goal Seek:

TT Thao tác

1 Chọn Tool /Goal Seek  xuất hiện hộp thoại Goal Seek

2 Trong hộp Set Cell gõ địa chỉ của ô chứa công thức (ô hàm mục tiêu) muốn định lại giá trị (lƣu ý là gõ địa chỉ)

4 Trong hộp To value, gõ kết quả mong muốn

5 Trong hộp By changing cell gõ hoặc chọn địa chỉ của ô chứa giá trị muốn điều chỉnh (lƣu ý là gõ địa chỉ)

6 Chọn OK, hiện kết quả

 Lƣu ý: Nội dung của ô chứa giá trị (Set cell) phải là 1 công thức, và trong công

Bảng 3.1. Các thao tác thực hiện Goal Seek

122

thức đó phải có sự góp mặt của ô chứa giá trị muốn thay đổi (By changing cell)

 Ví dụ 1: Ông Phƣơng gửi tiền vào ngân hàng, số tiền ban đầu PO = 50.000.000 (đồng) vào ngân hàng VPBank, lãi suất tháng r=1%. Nhƣ chúng ta đã biết số tiền sau N tháng sẽ đƣợc tính theo công thức sau:

Hình 3.1: Hộp thoại Goal Seek

PN = PO * (1+ r)N

Sau 6 tháng số tiền gửi sẽ là: P6 = 50.000.000*(1+0,01)6 = 53.076.000 (đồng)

Tuy nhiên ông Phƣơng muốn hỏi ngƣời quản lý trong ngân hàng là: Giả sử với lãi suất tháng 1% nhƣ thế, sau 6 tháng ông muốn nhận đƣợc số tiền là 60.000.000 (đồng) thì ban đầu ông phải gửi số tiền PO là bao nhiêu? Để giải quyết đƣợc điều đó ta phải sử dụng đến tính năng Goal Seek của Excel. Các bƣớc thực hiện nhƣ sau:

TT Thao tác Giải thích

1 Gõ giá trị 1 vào ô A1

Ô A1 chính là ô chứa giá trị của số tiền ban đầu gửi vào, ở đây không nhất thiết phải gõ số 1, bạn muốn gõ giá trị nào cũng đƣợc (không nên để trống)

2 Gõ vào ô A2 = A1*((1+0,01)^6) Ô A2 là ô chứa công thức tính số tiền nhận được sau 6 tháng, đây chính là ô chỉ định kết quả (ô hà m mục tiêu)

3 Vào menu Tool /Goal Seek Sử dụng tính năng Goal Seek để tính toán

4 Xuất hiện hộp thoại Goal Seek Bắt đầu điền vào các thông số

5 Trong hộp Set cell gõ A2 A2 chính là địa chỉ của ô chứa công thức (hàm mục tiêu)

6 Gõ 60.000.000 vào hộp To value Giá trị 60.000.000 chính là giá trị mà ô A2 sẽ nhận, ở trong ví dụ này chính là số tiền muốn nhận đƣợc

7 Trong hộp By changing cell gõ A1

123

A1 là địa chỉ của ô chứa giá trị sẽ thay đổi khi ô A2 nhận giá trị 60.000.000, ở ví dụ này A2 chính là giá trị của số tiền phải gửi vào khi muốn nhận số tiền 60.000.000 (đồng)

8 Chọn OK

Kết thúc, hiển thị kết quả, giá trị nhận đƣợc sẽ là 56.523.000 (đồng), tức là muốn nhận đƣợc 60 triệu sau 6 tháng thì ông Phƣơng phải gửi số tiền là 56.523.000 (đồng)

 Ví dụ 2: Doanh nghiệp HOAHONG sản xuất mặt hàng bình cắm hoa nghệ thuật, để sản xuất ra 1 sản phẩm thì chi phí biến đổi đơn vị là 50.000 (đồng) và giá bán đơn vị là 110.000 (đồng), chi phí cố định là 40.000.000 (đồng). Ngƣời quản lý muốn biết đƣợc rằng phải sản xuất bao nhiêu sản phẩm và bán đƣợc thì doanh nghiệp hoà vốn. Nói cách khác là tìm sản lƣợng hoà vốn.

 Các thông số cần nhập: Nhƣ ta đã biết, sản lƣợng hoà vốn là sản lƣợng tại đó doanh thu đủ bù đắp chi phí bỏ ra, tức là DT=CF , hay DT - CF =0. Giải phƣơng trình trên chúng ta sẽ đƣợc sản lƣợng hoà vốn (Q hoà vốn). Vậy các thông số cần nhập vào là:

- Một ô bất kỳ chứa giá trị FC (chi phí cố định): 40.000.000

- Một ô bất kỳ chứa giá trị VC (chi phí biến đổi đơn vị): 50.000

- Một ô bất kỳ chứa giá trị P (giá bán đơn vị): 110.000

- Một ô chứa giá trị Q (sản lƣợng hoà vốn): Nhập giá trị bất kỳ vào ô đó.

- Một ô chứa Hàm mục tiêu: Nơi chứa công thức DT - CF

Bảng 3.2. Các bƣớc thực hiện tính năng Goal seek để tìm số tiền ban đầu cần gửi

 Các bƣớc thực hiện:

124

Hình 3.2: Minh hoạ cách sử dụng Goal Seek để tìm điểm hoà vốn (ví dụ 2)

TT Thao tác Giải thích

1 Gõ các nội dung nhƣ hình trên vào các ô từ A1  A5

Nội dung các ô từ A1  A5 chỉ mang ý nghĩa minh hoạ cho các ô từ B1  B5, các ô này có thể không gõ vào cũng đƣợc vì nó chỉ giải thích không có ý nghĩa tính toán.

2 Gõ vào ô B1 giá trị 40.000.000 Ô B1 là ô chứa giá trị của FC (tổng chi phí cố định)

3 Gõ vào ô B2 giá trị 50.000 Ô B2 là ô chứa giá trị của VC (chi phí biến đổi đơn vị)

4 Gõ vào ô B3 giá trị 110.000 Ô B3 là ô chứa giá trị của P (giá bán đơn vị)

5 Gõ vào ô B4 giá trị 1

Ô B4 là ô chứa giá trị của Q (sản lƣợng hoà vốn), ta có thể gõ các giá trị bất kỳ, không nên để trống.

B5 6 ô vào Gõ =(B3*B4)-(B1+B2*B4)

Ô B5 là ô chứa Hàm mục tiêu (ô Set cell), nơi chứa công thức DT - CF. Trong đó (B3*B4) chính là DT, (B1+B2*B4) chính là CF

7 Vào menu Tool /Goal Seek Sử dụng tính năng Goal Seek để tính toán

8 Xuất hiện hộp thoại Goal Seek Bắt đầu điền vào các thông số

9 Trong hộp Set cell gõ B5 B5 chính là địa chỉ của ô chứa công thức (hàm mục tiêu): DT - CF

10 Gõ 0 vào hộp To value

Giá trị 0 chính là giá trị mà ô B5 sẽ nhận, ở trong ví dụ này chính là mong muốn DT-CF=0 để ta tìm sản lƣợng hoà vốn

11 Trong hộp By changing cell gõ B4

B4 là địa chỉ của ô chứa giá trị sẽ thay đổi khi ô B5 nhận giá trị 0, ở ví dụ này B4 chính là sản lƣợng hoà vốn

12 Chọn OK

Kết thúc, hiển thị kết quả, giá trị nhận đƣợc của ô B4 sẽ là 667, tức là doanh nghiệp phải sản xuất với số lƣợng là 667 thì hoà vốn

 Ví dụ 3: Cũng với doanh nghiệp HOAHONG nhƣ trên, các câu hỏi đặt ra đối với

125

Bảng 3.3. Các bƣớc dùng tính năng Goal seek để tìm điểm hòa vốn

- Yêu cầu 1: Với sản lƣợng hoà vốn là 500 thì khi đó giá bán sẽ là bao nhiêu (các chi

quản lý là:

- Yêu cầu 2: Để có đƣợc lợi nhuận là 20.000.000 thì doanh nghiệp phải tiêu thụ đƣợc

phí giữ nguyên)?

- Yêu cầu 3: Để có đƣợc lợi nhuận là 20.000.000 thì giá bán phải là bao nhiêu ở sản

bao nhiêu sản phẩm (các chi phí và giá nhƣ ban đầu)?

 Các bƣớc giải quyết yêu cầu 1:

lƣợng hoà vốn (667 sản phẩm, các chi phí giữ nguyên)?

TT Thao tác Giải thích

1 Vào menu Tool /Goal Seek Sử dụng tính năng Goal Seek để tính toán

2 Xuất hiện hộp thoại Goal Seek Bắt đầu điền vào các thông số

3 Trong hộp Set cell gõ B5 B5 chính là địa chỉ của ô chứa công thức (hàm mục tiêu): DT - CF

4 Gõ 0 vào hộp To value

Giá trị 0 chính là giá trị mà ô B5 sẽ nhận, ở trong ví dụ này chính là mong muốn DT-CF=0 để ta tìm giá bán.

5 Trong hộp By changing cell gõ B3

B3 là địa chỉ của ô chứa giá trị sẽ thay đổi khi ô B5 nhận giá trị 0, ở ví dụ này B3 chính là giá bán.

6 Chọn OK

Kết thúc, hiển thị kết quả, giá trị nhận đƣợc của ô B3 sẽ là 130.000, tức là doanh nghiệp phải bán với giá 130.000 thì sản lƣợng hoà vốn là 500

 Các bƣớc giải quyết yêu cầu 2:

Bảng 3.4. Các bƣớc dùng tính năng Goal seek để tìm giá sản phẩm khi có sản lƣợng hòa vốn

TT Thao tác Giải thích

7 Vào menu Tool /Goal Seek Sử dụng tính năng Goal Seek để tính toán

8 Xuất hiện hộp thoại Goal Seek Bắt đầu điền vào các thông số

126

9 Trong hộp Set cell gõ B5 B5 chính là địa chỉ của ô chứa công thức (hàm mục tiêu): DT - CF

10 Gõ 20.000.000 vào hộp To value

Giá trị 20.000.000 chính là giá trị mà ô B5 sẽ nhận, ở trong ví dụ này chính là mong muốn DT-CF=20.000.000 để ta tìm sản lƣợng bán

11 Trong hộp By changing cell gõ B4

B4 là địa chỉ của ô chứa giá trị sẽ thay đổi khi ô B5 nhận giá trị 20.000.000, ở ví dụ này B4 chính là số lƣợng bán.

12 Chọn OK

Kết thúc, hiển thị kết quả, giá trị nhận đƣợc của ô B3 sẽ là 1.000, tức là doanh nghiệp phải bán 1.000 sản phẩm thì sẽ đạt lợi nhuận là 20.000.000

 Các bƣớc giải quyết yêu cầu 3:

Bảng 3.5. Các bƣớc dùng tính năng Goal seek để đạt lợi nhuận mong muốn

TT Thao tác Giải thích

1 Vào menu Tool /Goal Seek Sử dụng tính năng Goal Seek để tính toán

2 Xuất hiện hộp thoại Goal Seek Bắt đầu điền vào các thông số

3 Trong hộp Set cell gõ B5 B5 chính là địa chỉ của ô chứa công thức (hàm mục tiêu): DT - CF

4 Gõ 20.000.000 vào hộp To value

Giá trị 20.000.000 chính là giá trị mà ô B5 sẽ nhận, ở trong ví dụ này chính là mong muốn DT-CF=20.000.000 để ta tìm giá bán

5 Trong hộp By changing cell gõ B3

B3 là địa chỉ của ô chứa giá trị sẽ thay đổi khi ô B5 nhận giá trị 20.000.000, ở ví dụ này B3 chính là giá bán.

6 Chọn OK

Kết thúc, hiển thị kết quả, giá trị nhận đƣợc của ô B3 sẽ là 140.000, tức là doanh nghiệp phải bán với giá 140.000 với sản lƣợng hoà vốn 667

 Ví dụ 4: Doanh nghiệp HOANVU đang dự kiến tung ra 1 sản phẩm mới. Số liệu từ bộ phận tài chính cho biết sản phẩm mới đƣợc dự kiến bán với giá 1.100.000 (đồng). Để sản xuất đƣợc sản phẩm đó, doanh nghiệp phải đầu tƣ mua 1 thiết bị SX mới với giá 300.000.000 (đồng); Tổng chi phí cố định khác là 100.000.000 (đồng). Chi phí để sản xuất

127

Bảng 3.6. Các bƣớc dùng tính năng Goal seek để tìm giá sản phẩm để đạt lợi nhuận mong muốn

- Lợi nhuận trong trƣờng hợp xấu nhất và tốt nhất là bao nhiêu?

- Công ty bán tối thiểu bao nhiêu sản phẩm thì hoà vốn?

- Khi nào thì có lời, khi nào thì lỗ?

 Các thông số cần nhập: Với các dữ liệu do bài toán cung cấp nhƣ trên ta có những

ra 1 sản phẩm là 500.000 (đồng). Ban giám đốc còn đang lƣỡng lự về quyết định cuối cùng. Bộ phận tiếp thị và bán hàng cho biết số lƣợng bán trong năm có thể từ 500  1.100 sản phẩm. Các câu hỏi mà ngƣời quản lý cần phải giải quyết đó là:

nhận xét sau:

- Giá bán đơn vị > Chi phí biến đổi đơn vị. Do vậy doanh nghiệp càng bán đƣợc nhiều sản phẩm thì lợi nhuận càng cao và ngƣợc lại. Tức là lợi nhuận trong trƣờng hợp xấu nhất là khi DN chỉ bán đƣợc 500 sản phẩm. Mức lợi nhuận xấu nhất là LN=500*1.100.000 -(40.000.000+500*500.000)=-100.000.000 (đồng). Lợi nhuận tốt nhất trong trƣờng hợp LN=1.100*1.100.000 phẩm, 1.100 DN -(40.000.000+1.100*500.000) = +260.000.000 (đồng).

- Doanh nghiệp sẽ có lời khi bán đƣợc số lƣợng sản phẩm > sản lƣợng hoà vốn, và sẽ

tƣơng đƣợc ứng bán sản

 Các bƣớc xác định sản lƣợng hoà vốn:

lỗ khi số lƣợng bán đƣợc < sản lƣợng hoà vốn.

Hình 3.3: Minh hoạ cách sử dụng Goal Seek để tìm điểm hoà vốn (ví dụ 4)

128

TT Thao tác Giải thích

1 Gõ các nội dung nhƣ hình trên vào các ô từ A1  A5

2 Gõ vào ô B1 giá trị 400.000.000 Ô B1 là ô chứa giá trị của FC (tổng chi phí cố định)

3 Gõ vào ô B2 giá trị 500.000 Ô B2 là ô chứa giá trị của VC (chi phí biến đổi đơn vị)

4 Gõ vào ô B3 giá trị 1.100.000 Ô B3 là ô chứa giá trị của P (giá bán đơn vị)

5 Gõ vào ô B4 giá trị 1

Ô B4 là ô chứa giá trị của Q (sản lƣợng hoà vốn), ta có thể gõ các giá trị bất kỳ, không nên để trống.

B5 6 Gõ ô vào =(B3*B4)-(B1+B2*B4)

Ô B5 là ô chứa Hàm mục tiêu (ô Set cell), nơi chứa công thức DT - CF. Trong đó (B3*B4) chính là DT, (B1+B2*B4) chính là CF

7 Vào menu Tool /Goal Seek Sử dụng tính năng Goal Seek để tính toán

8 Xuất hiện hộp thoại Goal Seek Bắt đầu điền vào các thông số

9 Trong hộp Set cell gõ B5 B5 chính là địa chỉ của ô chứa công thức (hàm mục tiêu): DT – CF

10 Gõ 0 vào hộp To value

Giá trị 0 chính là giá trị mà ô B5 sẽ nhận, ở trong ví dụ này chính là mong muốn DT-CF=0 để ta tìm sản lƣợng hoà vốn

11 Trong hộp By changing cell gõ B4

B4 là địa chỉ của ô chứa giá trị sẽ thay đổi khi ô B5 nhận giá trị 0, ở ví dụ này B4 chính là sản lƣợng hoà vốn

12 Chọn OK

Kết thúc, hiển thị kết quả, giá trị nhận đƣợc của ô B4 sẽ là 667, tức là doanh nghiệp phải sản xuất với số lƣợng là 667 thì hoà vốn

 Ví dụ 5: Giải phƣơng trình X3 - 6X2 + 11X - 6 = 0 bằng tính năng Goal Seek.

129

Bảng 3.7 Các bƣớc thực hiện cách sử dụng Goal Seek để tìm điểm hoà vốn (ví dụ 4)

 Các bƣớc thực hiện nhƣ sau

Hình 3.4: Minh hoạ cách sử dụng Goal Seek để giải phƣơng trình (ví dụ 5)

TT Thao tác Giải thích

1 Gõ các nội dung nhƣ hình trên vào các ô từ A1  A2

Nội dung các ô từ A1  A2 chỉ mang ý nghĩa minh hoạ cho các ô từ B1  B2, các ô này có thể không gõ vào cũng đƣợc vì nó chỉ giải thích không có ý nghĩa tính toán.

2 Gõ vào ô B1 giá trị 1 Ô B1 là ô chứa giá trị của ẩn X

vào B2 3 Gõ =B1^3-6*(B1^2)+11*B1-6 Ô B2 là ô chứa Hàm mục tiêu, nơi chứa phƣơng trình cần tính.

4 Vào menu Tool /Goal Seek Sử dụng tính năng Goal Seek để tính toán

5 Xuất hiện hộp thoại Goal Seek Bắt đầu điền vào các thông số

6 Trong hộp Set cell gõ B2 B2 chính là địa chỉ của ô chứa công thức (hàm mục tiêu) hay phƣơng trình cần tính

7 Gõ 0 vào hộp To value

Giá trị 0 chính là giá trị mà ô B2 sẽ nhận, ở trong ví dụ này chính là ta giải phƣơng trình.

8 Trong hộp By changing cell gõ B1

B1 là địa chỉ của ô chứa giá trị sẽ thay đổi khi ô B2 nhận giá trị 0, ở ví dụ này B1 chính là nghiệm của phƣơng trình.

130

9 Chọn OK Kết thúc, kết quả là 1

Bảng 3.8. Các bƣớc thực hiện cách sử dụng Goal Seek để giải phƣơng trình

3.2. Tính năng Solver:

 Công dụng: Đây là chức năng tổng quát hơn so với Goal Seek. Dùng Solver, một công thức có thể đạt đến một giá trị [Xác định] hoặc đạt giá trị [Max] hay [Min] dựa trên các ràng buộc cho trƣớc của các thông số đƣợc sử dụng trong công thức.

 Các bƣớc thực hiện:

3.2.1. Các thông số cơ bản của Solver:

TT Thao tác

1 Chọn Tool /Solver  Xuất hiện hộp thoại Solver Parameters

2 Trong hộp Set Target Cell, gõ địa chỉ hoặc tên của ô đích. Ô đích cần phải chứa công thức

3 Để nhận đƣợc giá trị lớn nhất của ô đích, kích Max

4 Để nhận đƣợc giá trị nhỏ nhất của ô đích, kích Min

5 Để nhận một giá trị xác định của ô đích, kích Value of, và gõ giá trị trong hộp

Trong hộp By changing Cells, gõ tên hoặc địa chỉ của các ô, phân cách nhau bởi dấu "," (tối đa 200 ô) Để Solver tự động điều chỉnh giá trị các ô, kích Guess

7 Trong hộp Subject to The Constraints, vào các điều kiện ràng buộc nào muốn áp dụng

8 Kích Solver

9 Để giữ kết quả Solver trên bảng tính, kích Keep Solver Solution trong hộp thoại Solver Results

10 Để khôi phục lại dữ liệu gốc, kích Restore Original Values

11 Để lƣu trữ kết quả nhƣ một Scenario thì kích Save Scenario. Gõ tên của Scenario vào hộp Scenario name

131

12 Kết thúc chọn OK

Bảng 3.9. Các bƣớc thực hiện tính năng Solver

Hình 3.6: Hộp thoại Solver Parameters trợ giúp nhập các thông số

 Ứng dụng: Trong thực tế Solver có rất nhiều ứng dụng, tuy nhiên ứng dụng lớn

3.2.2. Sử dụng chức năng Solver để giải các bài toán quản lý:

 Bài toán tối ƣu: Các bài toán tối ƣu mà chúng ta hay gặp đó là bài toán về lập kế hoạch sản xuất; bài toán quản lý vật tƣ; bài toán phân công. Bài toán tối ƣu tổng quát có dạng nhƣ sau:

- f(x1, x2, ...,xj)  min (hoặc max)

- Với hệ các ràng buộc {g(x1, x2,...,xi)<>bi}

- Trong đó:

nhất của Solver đó là để giải các bài toán tối ƣu (có liên quan đến quản lý).

f(x1, x2, ...,xj) đƣợc gọi là Hàm mục tiêu

 Ví dụ 1: Bài toán lập kế hoạch sản xuất.

- Bác Ba Phi muốn trồng lúa gạo và ngô, nhƣng khổ 1 nỗi bác lại bị hạn chế về diện tích đất canh tác, nƣớc tƣới và nhân công trồng trọt, các hạn chế đƣợc cho trong bảng dƣới. Câu hỏi của Bác là nên trồng bao nhiêu tấn lúa gạo là lúa mì để có đƣợc lợi nhuận lớn nhất với điều kiện hạn chế nhƣ đã nêu.

g(x1, x2,...,xi) đƣợc gọi là Các ràng buộc

TT Số liệu Lúa gạo Lúa mì Hạn chế

1 Diện tích (ha/tấn) 2 3 50

5 Lƣợng nƣớc (10 khối/tấn) 6 4 90

132

6 Nhân công (công/tấn) 20 5 250

7 Lợi nhuận (đồng/tấn) 18 21

- Muốn biết đƣợc Bác sẽ trồng gạo và mì nhƣ thế nào để đƣợc lợi nhuận lớn nhất ta

Bảng 3.10. Các điều kiện ràng buộc để sản xuất của bác Ba Phi

- Gọi x và y là số tấn lúa gạo và lúa mì Bác phải sản xuất.

- Hàm mục tiêu: ở đây chính là lợi nhuận mong muốn đạt đƣợc max: 18*x + 21*y 

sử dụng tính năng Solver, các bƣớc thực hiện nhƣ sau:

- Các ràng buộc:

max.

Ràng buộc về diện tích: 2*x + 3*y <=50

Ràng buộc về lƣợng nƣớc: 6*x + 4*y <=90

Ràng buộc về nhân công: 20*x + 5*y<=250

133

Ràng buộc về giá trị biến: x,y>=0

Hình 3.7: Minh hoạ cách sử dụng Solver để bài toán lập kế hoạch sản xuất (ví dụ 1)

Hình 3.8: Hộp thoại Solver Parameters trợ giúp nhập các thông số cho ví dụ 1

TT Thao tác Giải thích

1 Gõ các nội dung nhƣ hình trên vào các ô từ A1  A6

Nội dung các ô từ A1  A6 chỉ mang ý nghĩa minh hoạ cho các ô từ B1  B6, các ô này có thể không gõ vào cũng đƣợc vì nó chỉ giải thích không có ý nghĩa tính toán.

2 Gõ vào ô B1 giá trị 1 Ô B1 là ô chứa giá trị của ẩn X, gõ giá trị tuỳ ý (không nhất thiết phải là số 1)

3 Gõ vào ô B2 giá trị 1 Ô B2 là ô chứa giá trị của ẩn Y, gõ giá trị tuỳ ý (không nhất thiết phải là số 1)

4 Gõ vào B3 =18*B1+21*B2

Ô B3 là ô chứa Hàm mục tiêu, nơi chứa công thức tính lợi nhuận (với B1 là ẩn X, B2 là Y)

5 Gõ vào B4 = 2*B1+3*B2 Ô B4 là ô chứa ràng buộc về diện tích

6 Gõ vào B5 = 6*B1+4*B2 Ô B5 là ô chứa ràng buộc về lƣợng nƣớc

7 Gõ vào B6 = 20*B1+5*B2 Ô B6 là ô chứa ràng buộc về nhân công

8 Vào menu Tool / Solver Sử dụng tính năng Solver để tính toán

9 Xuất hiện hộp thoại Solver Bắt đầu điền vào các thông số

134

10 Trong hộp Set Target Cell gõ $B$3 B3 chính là địa chỉ của ô chứa công thức (hàm mục tiêu) hay lợi nhuận cần tính

11 Chọn Max

Giá trị Max chính là giá trị mà ô B3 sẽ nhận, ở trong ví dụ này chính là ta mong muốn lợi nhuận phải là cao nhất.

12 Trong hộp By changing cell gõ $B$1:$B$2

B1 và B2 là địa chỉ của các ô chứa giá trị sẽ thay đổi khi ô B3 đạt Max, ở ví dụ này B1 là ẩn X, B2 là ẩn Y (số lúa gạo và số lúa mì cần sản xuất)

13 Kích cho Add Chuẩn bị nhập các ràng buộc

14 Xuất hiện hộp thoại Add Constraint Nhập vào ràng buộc thứ 1

15 Tại Cell Reference gõ $B$4 Ràng buộc về diện tích

16 Chọn <= Nhỏ hơn hoặc bằng

17 Tại Constrain gõ 50 Ràng buộc về diện tích <=50 theo đầu bài

18 Kích Add Nhập vào ràng buộc thứ 2

19 Tại Cell Reference gõ $B$5 Ràng buộc về lƣợng nƣớc

20 Chọn <= Nhỏ hơn hoặc bằng

21 Tại Constrain gõ 90 Ràng buộc về diện tích <=90 theo đầu bài

22 Kích Add Nhập vào ràng buộc thứ 3

24 Tại Cell Reference gõ $B$6 Ràng buộc về nhân công

25 Chọn <= Nhỏ hơn hoặc bằng

26 Tại Constrain gõ 250 Ràng buộc về nhân công <=250 theo đầu bài

27 Kích Add Nhập vào ràng buộc thứ 4

28 Tại Cell Reference gõ $B$1 Ràng buộc về số lúa gạo sản xuất

29 Chọn >= Lớn hơn hoặc bằng

30 Tại Constrain gõ 0 Số lúa gạo sản xuất phải lớn hơn =0

31 Kích Add Nhập vào ràng buộc thứ 5

32 Tại Cell Reference gõ $B$2 Ràng buộc về số lúa gạo sản xuất

135

33 Chọn >= Lớn hơn hoặc bằng

34 Tại Constrain gõ 0 Số lúa mì sản xuất phải lớn hơn =0

35 Chọn OK Kết thúc việc nhập các ràng buộc

36 Kích Solver Xem kết quả

37 Giữ kết quả

Để giữ kết quả Solver trên bảng tính, kích Keep Solver Solution trong hộp thoại Solver Results

38 Xem qua kết quả, giữ nguyên giá trị gốc Để khôi phục lại dữ liệu gốc, kích Restore Original Values

39 Lƣu trữ kiểu Scenario, khi cần có thể xem lại Để lƣu trữ kết quả nhƣ một Scenario thì kích Save Scenario. Gõ tên của Scenario vào hộp Scenario name

40 Kết thúc chọn OK Kết thúc toàn bộ, kết quả là 7 tấn lúa gạo và 12 tấn lúa mì

 Ví dụ 2: Bài toán sử dụng vật tƣ

- Công ty REDWALL lập kế hoạch sản xuất các cấu kiện bê tông đúc sẵn. Công ty dự định sản xuất 3 loại cấu kiện đúc sẵn là Tấm đan; Cột điện, Panen. Định mức về xi măng, thép và đá cho mỗi loại cấu kiện đƣợc cho ở bảng dƣới. Hiện tại lƣợng vật tƣ còn lại trong kho là: Xi măng còn 3.400 kg; thép còn 1.800 kg; đá còn 22 m3. Vậy công ty nên sản xuất Tấm đan; Cột điện; Panen nhƣ thế nào để tận dụng 1 cách tốt nhất lƣợng vật tƣ còn lại trong kho.

Bảng 3.11. Các bƣớc thực hiện dùng tính năng Solver Parameters trợ giúp nhập các thông số cho ví dụ 1

TT Số liệu Tấm đan Cột điện Panel

1 Xi măng (kg) 300 100 200

5 Thép (kg) 100 100 200

6 Đá (m3) 1 3 2

- Gọi x là số lƣợng Tấm đan cần sản xuất.

- Gọi y là số lƣợng Cột điện cần sản xuất.

- Gọi z là số lƣợng Panen cần sản xuất

136

Bảng 3.12. Số liệu thống kê vật tƣ còn lại trong kho

- Hàm mục tiêu: ở đây không nhất thiết phải có, tuy nhiên ta sẽ chọn Hàm mục tiêu

- Các ràng buộc:

là 1 trong 3 phƣơng trình dƣới đây

Ràng buộc về xi măng: 300*x+100*y+200*z=3400

Ràng buộc về thép: 100*x+100*y+200*z=1800

Ràng buộc về nhân công: x+3*y+2*z=22

Ràng buộc về giá trị biến: x,y,z>=0

Hình 3.9: Minh hoạ cách sử dụng Solver để bài toán sử dụng vật tƣ (ví dụ 2)

Hình 3.10: Hộp thoại Solver Parameters trợ giúp nhập các thông số cho ví dụ 2

137

TT Thao tác Giải thích

1 Gõ các nội dung nhƣ hình trên vào các ô từ A1  A6

2 Gõ vào ô B1 giá trị 1 Ô B1 là ô chứa giá trị của ẩn X, gõ giá trị tuỳ ý (không nhất thiết phải là số 1)

3 Gõ vào ô B2 giá trị 1 Ô B2 là ô chứa giá trị của ẩn Y, gõ giá trị tuỳ ý (không nhất thiết phải là số 1)

4 Gõ vào ô B3 giá trị 1 Ô B3 là ô chứa giá trị của ẩn Z, gõ giá trị tuỳ ý (không nhất thiết phải là số 1)

vào B4 5 Ô B4 là ô chứa ràng buộc về xi măng Gõ =300*B1+100*B2+200*B3

= 6 Ô B5 là ô chứa ràng buộc về thép B5 vào Gõ 100*B1+100*B2+200*B3

7 Gõ vào B6 = B1+3*B2+2*B3 Ô B6 là ô chứa ràng buộc về đá

8 Vào menu Tool / Solver Sử dụng tính năng Solver để tính toán

9 Xuất hiện hộp thoại Solver Bắt đầu điền vào các thông số

10 Trong hộp By changing cell gõ $B$1:$B$3 B1, B2, B3 là địa chỉ của các ô chứa giá trị sẽ thay đổi khi ta nhập các ràng buộc bằng 0, ở ví dụ này đó là các ẩn X, Y, Z

11 Kích cho Add Chuẩn bị nhập các ràng buộc

12 Xuất hiện hộp thoại Add Constraint Nhập vào ràng buộc thứ 1

13 Tại Cell Reference gõ $B$4 Ràng buộc về xi măng

14 Chọn = Bằng

15 Tại Constrain gõ 3400 Ràng buộc về xi măng nhƣ đầu bài

16 Kích Add Nhập vào ràng buộc thứ 2

17 Tại Cell Reference gõ $B$5 Ràng buộc về lƣợng thép

18 Chọn = Bằng

19 Tại Constrain gõ 1800 Ràng buộc về thép nhƣ đầu bài

138

20 Kích Add Nhập vào ràng buộc thứ 3

21 Tại Cell Reference gõ $B$6 Ràng buộc về đá

22 Chọn = Bằng

23 Tại Constrain gõ 22 Ràng buộc về đá nhƣ đầu bài

24 Kích Add Nhập vào ràng buộc thứ 4

25 Tại Cell Reference gõ $B$1 Ràng buộc về số lƣợng Tấm đan

26 Chọn >= Lớn hơn hoặc bằng

27 Tại Constrain gõ 0 Số tấm đan sản xuất phải lớn hơn =0

28 Kích Add Nhập vào ràng buộc thứ 5

29 Tại Cell Reference gõ $B$2 Ràng buộc về số lƣợng Cột điện

30 Chọn >= Lớn hơn hoặc bằng

31 Tại Constrain gõ 0 Số cột điện sản xuất phải lớn hơn =0

32 Kích Add Nhập vào ràng buộc thứ 6

33 Tại Cell Reference gõ $B$3 Ràng buộc về số lƣợng Panen

34 Chọn >= Lớn hơn hoặc bằng

35 Tại Constrain gõ 0 Số panen sản xuất phải lớn hơn =0

36 Chọn OK Kết thúc việc nhập các ràng buộc

37 Kích Solver Xem kết quả

38 Giữ kết quả

Để giữ kết quả Solver trên bảng tính, kích Keep Solver Solution trong hộp thoại Solver Results

39 Xem qua kết quả, giữ nguyên giá trị gốc Để khôi phục lại dữ liệu gốc, kích Restore Original Values

40 Lƣu trữ kiểu Scenario, khi cần có thể xem lại Để lƣu trữ kết quả nhƣ một Scenario thì kích Save Scenario. Gõ tên của Scenario vào hộp Scenario name

41 Kết thúc chọn OK Kết thúc toàn bộ, kết quả là 8 tấm đan, 2 cột điện và 4 panen

139

Bảng 3.13. Các bƣớc thực hiện sử dụng Solver để bài toán sử dụng vật tƣ (ví dụ 2)

 Ví dụ 3: Giải hệ phƣơng trình tuyến tính

2*x+5*y+z=35

3*x+2*y+4*z=30

- Hàm mục tiêu: ở đây không nhất thiết phải có, tuy nhiên ta sẽ chọn Hàm mục tiêu

x+y+2*z=13

- Các ràng buộc:

là 1 trong 3 phƣơng trình

Ràng buộc của phƣơng trình thứ 1: 2*x+5*y+z=35

Ràng buộc của phƣơng trình thứ 2: 3*x+2*y+4*z=30

Ràng buộc của phƣơng trình thứ 3: x+y+2*z=13

140

Hình 3.11: Minh hoạ cách sử dụng Solver để giải hệ phƣơng trình (ví dụ 3)

Hình 3.12: Hộp thoại Solver Parameters trợ giúp nhập các thông số cho ví dụ 3

TT Thao tác Giải thích

Nội dung các ô từ A1  A6 chỉ mang ý

Gõ các nội dung nhƣ hình trên vào nghĩa minh hoạ cho các ô từ B1  B6, các 1 các ô từ A1  A6 ô này có thể không gõ vào cũng đƣợc vì nó

chỉ giải thích không có ý nghĩa tính toán.

Ô B1 là ô chứa giá trị của ẩn X, gõ giá trị 2 Gõ vào ô B1 giá trị 1 tuỳ ý (không nhất thiết phải là số 1)

Ô B2 là ô chứa giá trị của ẩn Y, gõ giá trị 3 Gõ vào ô B2 giá trị 1 tuỳ ý (không nhất thiết phải là số 1)

Ô B3 là ô chứa giá trị của ẩn Z, gõ giá trị 4 Gõ vào ô B3 giá trị 1 tuỳ ý (không nhất thiết phải là số 1)

5 Gõ vào B4 =2*B1+5*B2+B3 Ô B4 là ô chứa phƣơng trình thứ 1

6 Gõ vào B5 =3*B1+2*B2+4*B3 Ô B5 là ô chứa phƣơng trình thứ 2

7 Gõ vào B6 =B1+B2+2*B3 Ô B6 là ô chứa phƣơng trình thứ 3

8 Vào menu Tool / Solver Sử dụng tính năng Solver để tính toán

141

9 Xuất hiện hộp thoại Solver Bắt đầu điền vào các thông số

B1, B2, B3 là địa chỉ của các ô chứa giá trị Trong hộp By changing cell gõ sẽ thay đổi khi ta nhập các ràng buộc bằng 10 $B$1:$B$3 0, ở ví dụ này đó là các ẩn X, Y, Z

11 Kích cho Add Chuẩn bị nhập các ràng buộc

12 Xuất hiện hộp thoại Add Constraint Nhập vào ràng buộc thứ 1

13 Tại Cell Reference gõ $B$4 Ràng buộc cho phƣơng trình thứ 1

14 Chọn = Bằng

15 Tại Constrain gõ 35 Ràng buộc nhƣ đầu bài

16 Kích Add Nhập vào ràng buộc thứ 2

17 Tại Cell Reference gõ $B$5 Ràng buộc cho phƣơng trình thứ 2

18 Chọn = Bằng

19 Tại Constrain gõ 30 Ràng buộc nhƣ đầu bài

20 Kích Add Nhập vào ràng buộc thứ 3

21 Tại Cell Reference gõ $B$6 Ràng buộc cho phƣơng trình thứ 3

22 Chọn = Bằng

23 Tại Constrain gõ 13 Ràng buộc nhƣ đầu bài

36 Chọn OK Kết thúc việc nhập các ràng buộc

37 Kích Solver Xem kết quả

Để giữ kết quả Solver trên bảng

tính, kích Keep Solver Solution 38 Giữ kết quả

trong hộp thoại Solver Results

142

Để khôi phục lại dữ liệu gốc, kích 39 Xem qua kết quả, giữ nguyên giá trị gốc Restore Original Values

Để lƣu trữ kết quả nhƣ một Scenario Lƣu trữ kiểu Scenario, khi cần có thể xem thì kích Save Scenario. Gõ tên của 40 lại Scenario vào hộp Scenario name

41 Kết thúc chọn OK Kết thúc toàn bộ, kết quả X=4, Y=5, Z=2

Bảng 3.14. Các bƣớc thực hiện sử dụng Solver để giải hệ phƣơng trình

3.3. Một số bài toán tối ƣu trong quản trị

Bài 1: Công ty bột giặt OMO thấy rằng, lợi nhuận hàng tháng có thể biểu thị bằng hàm P= -10 -2*A2+16*A-4*S2+24*S-4*S*A Trong đó: A: Chi phí quảng cáo S: Số cửa hàng bán. P: Lợi nhuận Yêu cầu: Hãy xác định mức chi phí q / cáo và số cửa hàng bán để đƣợc lợi nhuận tối đa.

Bài 2: Giải hệ phƣơng trình 2*X+5*Y+Z=35 3*X+2*Y+4*Z=30 X+Y+2*Z=13

Bài 3: Một hàm chi phí có dạng bậc 2 là C=a+b*X+c*X2 Trong đó: X (số lƣợng); C(Tổng chi phí) Ngƣời lại đƣợc biết giá trị của nó tại 3 điểm nhƣ sau:

Điểm 1 600 104.000 Điểm 2 1.000 160.000 Điểm 3 2.000 370.000 X C

Bảng 3.15. Bảng giá trị tham chiếu của biến X

143

Yêu cầu: - Tìm các hệ số a, b, c. - Với giá trị nào của X thì chi phí cực tiểu. - Với giá trị nào của X thì chi phí là 160.000

Bài 4: Công ty may mặc An Phƣớc Piercardin sản xuất 3 loại áo Phông, Sơ mi, Gilê. Mỗi loại áo đều phải qua 3 công đoạn sản xuất là cắt, may và đóng gói với thời gian đƣợc cho ở bảng dƣới. Các bộ phận cắt, may, đóng gói có số giờ công tối đa trong mỗi tuần lần lƣợt là 1.160; 1.560; 480 giờ. Câu hỏi: Mỗi tuần công ty phải sản xuất bao nhiêu áo mỗi loại để sử dụng hết năng lực của nhà máy.

Bộ phận Cắt (giờ) May (giờ) Đóng gói (giờ) Sơ mi 0.4 0.5 0.2 Gilê 0.3 0.4 0.1 Tổng giờ công 1160 1560 480

Phông 0.2 0.3 0.1 Bảng 3.16. Bảng tham chiếu giờ chuẩn sản xuất áo

3.4. Một số bài toán về lao động, tiền lƣơng

Bài 1: Hãy nhập dữ liệu sau vào bảng tính và thực hiện các yêu cầu dƣới đây:

Hình 3.13. Minh họa dữ liệu cho bài 1

Yêu cầu:

- Tính chi phí trong cột tổng cả năm và hàng tổng chi

- Tính thu nhập từng quý và luỹ kế tới quý.

- Chèn một hàng vào giữa hàng "chi phí" và " bán" để tính % tiền bán đƣợc của từng quý so với cả năm.

144

- Chèn thêm một cột sau cột "cả năm" với tiêu đề" VNĐ" để tính tiền bán đƣợc và chi phí quy ra tiền VNĐ theo tỷ giá ở cuối bảng.

Bài 2: Hãy nhập dữ liệu sau vào bảng tính và thực hiện các yêu cầu dƣới đây:

Hình 3.14. Minh họa dữ liệu cho bài 2

Yêu cầu:

- Tính phụ cấp chức vụ(PCCV):

+ Nếu chức vụ là giám đốc (GD): 6000

+ Nếu chức vụ là phó giám đốc (PG ) hoặc trƣởng phòng (TP):4000

+ Nếu chức vụ là phó phòng (PP) hoặc kỹ thuật (KT):3000

+ Nếu chức vụ là nhân viên (NV)hoặc (BV):2000

- Tính lƣơng : Bằng (lƣơng cơ bản * ngày công) + phụ cấp chức vụ

- Tính tạm ứng = 2/3 lƣơng không vƣợt quá 250000.

- Tính còn lại : bằng lƣơng - tạm ứng

145

- Tính tổng cộng, cao nhất, thấp nhất.

Bài 3: Hãy nhập dữ liệu sau vào bảng tính và thực hiện các yêu cầu dƣới đây:

1/ Tính phụ cấp chức vụ nhƣ sau:

+ Nếu chức vụ là GĐ : 200000

+ Nếu chức vụ là PGĐ : 150000

+ Nếu chức vụ là TP : 100000

+ Nếu chức vụ là PP : 80000

+ Nếu chức vụ là NV : 0

+ Nếu chức vụ là BV: 30000

2/ Lƣơng = Tiền 1 ngày * Số ngày LV (Lƣu ý: Nếu Số ngày LV cao hơn Ngày công chuẩn (25 ngày) thì mỗi ngày vƣợt trội tính bằng 2 ngày LV)

3/ Thu nhập = Lƣơng + Phụ cấp CV

146

Hình 3.15. Minh họa dữ liệu cho bài 3

Bài 4: Hãy nhập dữ liệu sau vào bảng tính và thực hiện các yêu cầu trong bảng

Hình 3.16. Minh họa dữ liệu cho bài 4

1/ + Nếu thời gian lƣu kho <=30 nhận xét: Bán chạy + Nếu thời gian lƣu kho <=90 nhận xét: Bán đƣợc + Nếu thời gian lƣu kho > 90 nhận xét: Bán chậm

2/ Thuế = Hệ số thuế * Thành tiền

3/ Giá thành = Thành tiền + Thuế

147

Bài 5: Hãy thực hiện các yêu cầu trong bảng

Hình 3.17. Minh họa dữ liệu cho bài 5

Hàng Giá Thuế

RUOU 30 20%

Thuoc 20 5%

Cafe 10 2%

Banh 5 10%

Bảng 3.18. Bảng tham chiếu giá và thuế

Yêu cầu: Hãy tạo bảng tính trên bằng cách sử dụng hàm tìm kiếm theo hƣớng dẫn sau:

- Dựa vào bảng tham chiếu để tính giá trị cho mỗi loại hàng (Số lƣợng*Đơn giá)

- Dựa vào bảng tham chiếu để tính thuế cho mỗi loại hàng (Trị giá*Thuế)

- Tính tổng giá các mặt hàng và từng mặt hàng trên một góc khác của trang theo mẫu (dùng hàm SUMIF)

Hàng Trị giá

RUOU

Thuoc

Café

Banh

Bảng 3.19. Thống kê các trị giá hàng theo mẫu bảng trên

Bài 6: Hãy thực hiện các yêu cầu dƣới đây cho bảng doanh thu hàng hóa

- Tạo lập bảng tính

- Tính lãi đơn vị (%)

- Tính tiền lãi cho từng mặt hàng

- Thêm cột để tính tỷ trọng % của tiền lãi của từng mặt hàng trong tổng lãi

148

- Vẽ biểu đồ giá thành và giá bán của các mặt hàng

Hình 3.18. Minh họa dữ liệu cho bài 6

Bài 7: Hãy thực hiện các yêu cầu dƣới đây

Hình 3.19. Minh họa dữ liệu cho bài 7

- Hãy tạo bảng tính

- Nhận xét:

+ Nếu (ngày bán-ngày nhập)<=30: bán chạy

+ Nếu 30<(ngày bán-ngày nhập)<=90: bán đƣợc

149

+ Nếu (ngày bán-ngày nhập)>90: bán chậm

- Tính thành tiền = đơn giá * số lƣợng

- Tính thuế:

+ Nếu mã là A= thành tiến *1%

+ Nếu mã là B= thành tiến *2%

+ Nếu mã là C= thành tiến *3%

+ Nếu mã là D= thành tiến *5%

- Tính tổng giá mua (cả thuế) và tổng cộng

Bài 8: Hãy thực hiện các yêu cầu dƣới đây

Hình 3.20. Minh họa dữ liệu cho bài 8

- Tạo bảng tính trên

- Tính số tháng thuê

- Tính số ngày thuê

- Tính tiền tháng, tiền ngày và tổng tiền. Biết rằng nếu số ngƣời thuê <=3 thì tính giá đơn, >3 thì tính giá kép.

150

- Tạo bảng tổng hợp doanh thu các loại phòng khách

CÂU HỎI ÔN TẬP, THẢO LUẬN CHƢƠNG 3

Câu 1. Phân biệt sự khác nhau giữa Goal Seek và Solver?

Câu 2. Đâu là giới hạn của tính năng Goal Seek?

Câu 3. Phân biệt sự khác nhau khi sử dụng dạng thứ nhất của Hàm If và dạng thứ 2 của nó khi tính tổng có nhiều điều kiện?

BÀI TẬP ỨNG DỤNG

Bộ phận nghiên cứu thị trƣờng của công ty điện tử Phú Thành đã tiến hành xác định phƣơng trình của đƣờng cầu và phƣơng trình của tổng chi phí đối với sản phẩm đèn mầu trang trí và đƣợc kết quả nhƣ sau:

- Phƣơng trình đƣờng cầu: X=500-10*P

- Phƣơng trình tổng chi phí: C=3000+10*X

Trong đó:

X (cái): Số lƣợng đèn mầu.

P (đồng): Giá bán đơn vị sản phẩm.

C (đồng): Tổng chi phí.

Câu hỏi

1. Thể hiện tổng chi phí là hàm của P.

2. Thể hiện R là hàm của P. Biết rằng R = P*X

3. Tìm sản lƣợng hoà vốn.

151

4. Xác định giá bán sao cho có lợi nhuận lớn nhất.

Tài liệu học tập Tin ứng dụng trong kinh doanh

Tài liệu "Tin ứng dụng trong kinh doanh" hướng dẫn sinh viên ngành TàiQuản trị kinh doanh sử dụng Excel để phân tích dữ liệu. Nội dung gồm Excel cơ bản, cơ sở dữ liệu, và các tính năng nâng cao như Goal Seek, Solver.

Chủ đề:

Thủ thuật Excel

BỘ CÔNG THƢƠNG TRƢỜNG ĐẠI HỌC KINH TẾ KỸ THUẬT CÔNG NGHIỆP KHOA QUẢN TRỊ KINH DOANH