Giáo trình mô đun Bảng tính Excel nâng cao

lại có thể 
phải mất một thời gian cực kỳ dài để tìm ra được lời giải chính xác, do đó, Excel 
đã hòa hợp bằng việc xác lập những giới hạn nhất định trong quá trình lặp lại. 
Để thấy những giới hạn này, bạn mở Excel Options ra rồi nhấn vào ngăn 
Formulas(xem hình 15.14). Trong đó có hai tùy chọn xác lập cho việc lặp lại: 
 Maximum Iterations — Giá trị trong text box này quy định số lần lặp lại 
 tối đa. Trong Goal Seek, chính là số giá trị tối đa mà Excel đưa vào ô thay 
 đổi (changing cell) để thử. 
 84 
 Maximum Change — Giá trị trong text box này là giới hạn mà Excel sử 
 dụng để quyết định xem nó có hội tụ đến một lời giải hay không. Nếu 
 hiệu số giữa lời giải hiện hành và mục tiêu muốn đạt được nhỏ hơn hoặc 
 bằng giá trị này, Excel sẽ ngừng lại. 
Chính giá trị Maximum Change = 0.001 (mặc định) đã ngăn Excel đưa ra lời 
giải chính xác cho phép tính lợi nhuận ở bài trước. Trong một lần lặp lại nào đó, 
Goal Seek đã tìm ra đáp số là 0.2992 (tương đương 29.92%), nhưng bởi vì 0.008 
(chênh lệch giữa 0.3 và 0.2992) thì nhỏ hơn giá trị mặc định 0.001 trong text 
box Maximum Change, nên Excel đã ngưng quá trịnh lặp lại và đưa ra lời giải. 
Để đạt được lời giải chính xác = 30%, hay 0.3, bạn chỉ cần sửa lại con số trong 
Maximum Change là 0.0001. 
 85 
 BÀI 10: ỨNG DỤNG TÍNH NĂNG SOLVER ĐỂ GIẢI BÀI TOÁN 
 TRONG KINH TẾ 
Mục tiêu: 
- Trình bày được khái niệm, cách dùng của Solver. 
- Thiết lập được bài toán để sử dụng Solver. 
- Vận dụng được Solver vào bài tập cụ thể. 
- Nghiêm túc, tự giác trong học tập 
Nội dung 
1. Thiết lập mô hình bài toán hai giá 
 Ví dụ: Chúng ta cần tìm số lượng hàng hóa bán ra (gồm nhiều mặt hàng) khi 
 đã biết được đơn giá của từng mặt hàng và giá trị tổng cộng. Trong đó số 
 lượng hàng hóa bán ra không được lớn hơn số lượng còn tồn trong kho và 
 tổng cộng số tiền không được vượt quá giá trị cho trước. 
 Cụ thể: Có 12 mặt hàng với đơn giá, tổng giá trị hóa đơn và số lượng tồn kho 
 của hàng hóa. Hãy tính số lượng hàng hóa bán ra của mỗi loại thật tối ưu số 
 với tổng giá trị hóa đơn cho trước. 
 86 
Thiết kế bảng tính để tính số lượng các mặt hàng khi đã biết trước đơn giá và giá 
trị của hóa đơn. 
2. Các bƣớc tạo Solver để giải bài toán 
 Đối với lần đầu sử dụng Solver, các bạn phải cài đặt Add-in Solver và chỉ cài 
 đặt một lần duy nhất để sử dụng cho các lần sau. Các bạn chọn FileOptions 
 Chọn mục Add-in, tại cuối Tab Add-in có dòng Manage: Excel Add-in bấm 
 vào Go 
 87 
Tick vào mục Solver Add-in và bấm OK. 
Lúc này tại Tab Data đã xuất hiện mục Solver. 
 88 
Tại mục thành tiền của từng sản phẩm đặt công thức số lượng x đơn giá và 
mục tổng cộng dùng lệnh SUM như bìn thường. 
Để con trỏ chuột tại vị trí cộng (công thức lệnh SUM) và bấm vào Solver. 
Trong đó: Tại mục Set Ojective: Để tham chiếu đến ô tổng cộng (có lệnh 
SUM) để Solver sử dụng giá trị này chia cho cột đơn giá để tính ra cột số 
lượng tối ưu. Tại mục Value Of: Để giá trị tổng cộng (chính là giá trị của hóa 
đơn), gõ trực tiếp số tiền vào đây. Sau đó bấm vào Add tại mục Subject to the 
Constaints: 
Tại Cell Reference chọn vùng tham chiếu của số lượng cần tính toán, mục 
toán tử chọn nhỏ hơn hoặc bằng, tại mục Constaint chọn vùng số lượng tồn 
kho. 
 89 
Bấm Add để bổ sung điều kiện ràng buộc. Tại mục Cell Reference: Chọn 
vùng tham chiếu của mục số lượng cần tính toán, toán tử chọn dấu bằng, mục 
Constaint là Interger. Quy định mục số lượng cần tính toán là số nguyên. 
Sau khi OK, ta thấy có 2 điều kiện ràng buộc: Số lượng hàng hóa phải là số 
nguyên và phải nhỏ hơn hoặc bằng với hàng tồn kho. Sau đó chọn mục Select 
a Solving Method để chọn kiểu tính toán. Trong đó: GRG Nonlinear là tính 
toán đối với tất cả các mặt hàng; Simplex LP thực hiện tính toán số lượng ưu 
 90 
 tiên tính hết cho từng mặt hàng so với hàng còn tồn kho. Evolutionary: Tính 
 toán theo kiểu tiến hóa :)). 
 Kết quả đã được tối ưu khi chọn GRG Nonliear: Số lượng được phân bố đều 
 cho các mặt hàng. Bạn thử chọn Simples LP tại mục Select a Solving Method 
 xem có khác nhau không? Tuy nhiên, nếu chọn Evolutionary sẽ không có kết 
 quả đâu nhé 
 Muốn tiếp tục sử sụng nhưng với giá trị hóa đơn khác hoặc đơn giá, mặt hàng 
 khác, các bạn nhớ lưu lại phương thứcSolver đã thiết để sau đó đổi lại các giá 
 trị và bấm Solver để tiếp tục tính toán với những giá trị mới nhé 
3. Các dạng thay đổi bài toán trên 
4. Thiết lập mô hình bài toán với các ràng buộc 
 Solver trong Excel 2010 đã được thiết kế lại về giao diện và tên gọi của các 
hộp khai báo thông số cũng như sắp xếp lại các thành phần trên hộp thoại. 
Solver trong Excel 2010 đã bổ sung thêm phương pháp tìm kiếm lời giải mới 
Evolutionary Solver dựa trên các thuật toán di truyền (genetic 
algorithms). 
 91 
 Phương pháp này cho phép giải quyết những bài toán có sử dụng bất kỳ hàm 
nào trong Excel. Solver trong Excel 2010 được tối ưu cho việc giải quyết các bài 
toán tuyến tính và phi tuyến và bổ sung thêm 2 loại báo cáo kết quả Linearity 
và Feasibility. 
Set Object: Nơi đây ta cần nhập vào địa chỉ của hàm mục tiêu. 
Equal To: Hàm mục tiêu muốn đạt tới Max, Min hay Value of (bằng một giá 
trị mong muốn nào đó thì nhập giá trị vào hộp bên cạnh) 
By Changing Variable Cells: Nhập vào địa chỉ chứa các biến thay đổi của bài 
toán cần giải. 
Subject to the constraints: Nhập vào các ràng buộc của bài toán. 
Báo cáo Linearity 
 92 
 Khi không tìm được lời giải trong các bài toán phi tuyến. Solver sẽ chỉ ra các 
điều kiện ràng buộc và các biến không thõa trong bài toán. 
Báo cáo Feasibility 
 93 
 Nếu không tìm được lời giải khả thi cho bài toán. Solver hiển thị thông báo 
không tìm được lời giải khả thi và báo cáo sẽ giúp ta xác định nguyên nhân của 
vấn đề không tìm được lời giải. 
 Bạn có thể tinh chỉnh các thông số cho quá trình giải bài toán bằng cách nhấn 
vào nút Options. Trong hộp thoại Solver Parameters. Hộp thoại Options xuất 
hiện và được tổ chức thành 3 ngăn All Methods, GRG Nonlinear và 
Evolutionary tương ứng với các phương pháp tìm kiếm lời giải. 
 94 
6. Các bƣớc tạo Solver để giải bài toán 
7. Các dạng thay đổi bài toán trên 
 95 
 BÀI 11: CHIA SẺ BẢNG TÍNH VỚI WORD 
Mục tiêu: 
- Trình bày được phương pháp chèn, liên kết, hòa trộn bảng tính Excel vào 
 Word. 
- Tạo được một bảng tính mới Excel từ Word. 
- Chèn một bảng tính Excel có sẵn vào Word. 
 96 
- Liên kết được một phần của bảng tính Excel có sẵn vào trong Word. 
- Hòa trộn được một tài liệu Word vào Excel 
- Nghiêm túc, tự giác trong học tập 
Nội dung 
1. Tạo một bảng tính mới Excel từ Word 
 Dùng Insert / Object / Object/ Microsoft Excel WorkSheet để kích hoạt Excel 
 ngay trong Word 
2. Chèn một bảng tính Excel có sẵn vào Word 
3. Liên kết một phần của bảng tính Excel có sẵn vào trong Word 
Nếu bạn muốn liên kết các ô cố định nào đó từ bảng tính Excel vào trong Word 
hãy làm như sau: 
- B1: Trong trang soạn thảo của Word, để con trỏ chuột tại vị trí cần chèn bảng 
tính 
- B2: Mở chương trình Excel lên và mở bảng tính cần chèn một phần vào Word, 
đánh dấu các ô cần chèn và copy 
- B3: Chuyển sang trong soạn thảo Word,, chọn Edit -> Paste Special. . . hộp 
thoại Paste Special xuất hiện 
 Chọn Microsoft Excel Worksheet Object từ khung As trong hộp thoại Paste 
 Special và bạn có thể chọn các chức năng sau: 
 + Paste: Nhúng các ô vào Word 
 + Paste: Liên kết các ô trong bảng tính vào Word, khi ta thay đổi dữ liệu của 
 bảng tính gốc thì các ô trong Word cũng được thay đổi 
 + Display as Icon: Nếu chọn chức năng này thì Word không lấy ra một bảng 
 tính thật sự mà thể hiện dưới dạng một biểu tượng Excel. Biểu tượng này có 
 97 
 đường dần đến bảng tính đó, khi cần xem hoặc thay đổi nội dung, ta nhấp đúp 
 chuột vào biểu tượng này. 
 - B4: Chọn xong các chức năng thì nhớ OK 
4. Hòa trộn một tài liệu Word vào Excel 
 BÀI 12: CHIA SẺ BẢNG TÍNH VỚI ACCESS 
Mục tiêu: 
- Trình bày được phương pháp chèn, liên kết bảng tính Excel vào Access. 
- Chèn một bảng tính Excel có sẵn vào Access. 
- Liên kết được bảng tính Excel có sẵn vào trong Access. 
- Nghiêm túc, tự giác trong học tập 
Nội dung. 
1. Chèn một bảng tính Excel có sẵn vào Access 
 ∗ Trong cửa sổ làm việc của Access, chọn tab External Data, click nút 
 Excel trong nhóm lệnh Import & Link. 
 ∗ Chọn tập tin Excel cần chép (Click nút Browse ñể tìm 
 tập tin Excel) Open 
 ∗ OK, sau ñó thực hiện theo các bước hướng dẫn của Access 
 + Chọn sheet chứa dữ liệu cần chép, Next. 
 + Chọn dòng làm tiêu ñề cho bảng, Next. 
 98 
 + Chỉ ñịnh thuộc tính cho các field Next. 
 + Chọn cách tạo khóa cho bảng. 
 ▪ Let Access add primary key: Access tự tạo khóa. 
 ▪ Chose my own primary key: Bạn chỉ ñịnh khóa. 
 ▪ No primary key: Không tạo khóa. 
 + Nhập tên cho bảng Finish. 
 + Access hiển thị thông báo cho biết hoàn tất quá trình 
 import một file Excel thành một bảng trong Access. 
2. Liên kết đƣợc một phần của bảng tính Excel có sẵn vào trong Access 
 Có thể chép dữ liệu từ ứng dụng khác như Excel, ODBC 
 Database, XML file, Access, vào cơ sở dữ liệu hiện hành. 
 Cách thực hiện: 
 Chọn tab External Data. 
 99 
 − Trong nhóm lệnh Import & Link, chọn ứng dụng mà 
 bạn muốn chép dữ liệu. 
 − Lần lượt làm theo các bước hướng dẫn của Access. 
Chức năng Link trong Acces ñể 
duy trì mối liên kết với dữ liệu nguồn. Nếu dữ liệu nguồn thay ñổi thì 
dữ liệu trong bảng liên kết sẽ thay ñổi theo và ngược lại. 
Cách thực hiện tương tự như chức năng Import, nhưng trong cửa sổ Get 
External Data, ta chọn tùy chọn ―Link to data source by creating a 
link table‖ Chọn bảng muốn link OK 
 100 
 BÀI 13: TẠO VÀ THỰC THI MACRO THEO KỊCH BẢN 
Mục tiêu: 
- Trình bày được khái niệm, công dụng của Macro. 
- Trình bày được phương pháp tạo, quản lý, chỉnh sửa, xóa Macro. 
- Tạo được Macro theo kịch bản. 
- Nghiêm túc, tự giác trong học tập 
Nội dung 
1. Định nghĩa Macro 
 Macros là các tính năng cao cấp, có thể làm tăng tốc độ chỉnh sửa hay định 
 dạng mà bạn có thể thực hiện thường xuyên trong một bảng tính Excel. 
 Chúng ghi lại những hành động mà bạn đã chọn ở menu để cho một chuỗi 
 các hành động đó có thể được hoàn thành trong một bước. 
2. Tạo Macro theo kịch bản 
 Để ghi một Macro: 
 • Kích tab View trên vùng Ribbon 
 • Kích Macros 
 • Kích Record Macro 
 • Nhập tên cho Marco (không chứa dấu cách) 
 • Nhập một phím tắt 
 • Nhập vào mục Description để giải thích hay mô tả 
 101 
 Để thực hiện Macro: 
 • Kích Macro 
 • Kích Stop Recording 
3. Quản lý Macro 
4. Thực thi Macro 
Để thực thi một Macro từ phím tắt, chỉ cần ấn vào phím mà bạn đã chọn để chạy 
Macro. Hoặc bạn có thể xem tất cả các Macro và thực thi bằng cách: 
• Kích Macros 
• Kích View Macros 
• Chọn một Macro và kích Run 
 102 
5. Xóa Macro 
 + Cách 1: 
 Tool - Macro - Security . Chọn Security level, rồi chọn mức Low là xong, 
 đừng để mức High nữa. 
 + Cách 2: 
 Bấm tổ hợp Alt + F11 rồi xem ở mấy cái Module nó có module nào thì xóa đi 
 BÀI 14: TẠO VÀ THỰC THI MACRO SỬ DỤNG VBA 
Mục tiêu: 
- Trình bày được phương pháp tạo Macro sử dụng VBA. 
- Tạo được Macro sử dụng VBA. 
- Trình bày được các cách thực thi Macro qua phím tắt, trình quản lý, VIBAIDE. 
- Sử dụng được Macro bằng phím tắt. 
- Sử dụng được Macro thông qua trình quản lý Macro. 
- Sử dụng được Macro trực tiếp từ VIBAIDE. 
 103 
- Nghiêm túc, tự giác trong học tập 
Nội dung. 
1. Tạo Macro sử dụng VBA 
 Trên tab Developer, trong nhóm Code, bấm Visual Basic. 
 Nếu cần, trong Visual Basic Editor, trên menu Insert, bấm Modul. 
 Ghi chú Mô-đun được tạo tự động cho tất cả trang tính trong sổ làm việc. 
 Trong cửa sổ mã của mô-đun, hãy nhập hoặc sao mã macro mà bạn muốn 
 dùng. 
 Để chạy macro từ cửa sổ mô-đun, hãy nhấn F5. 
 Trong Visual Basic Editor, trên menu File, hãy bấm Close and return Excel 
 khi bạn kết thúc ghi macro. 
2. Chỉnh sửa Macro 
 Trên tab Developer, trong nhóm Code , bấm Macros. 
 Trong hộp Tên Macro, bấm vào macro mà bạn muốn sửa. 
 Bấm Sửa. Trình soạn thảo Visual Basic xuất hiện. 
 Mẹo Để được trợ giúp khi làm việc với Trình soạn thảo Visual Basic, trên 
 menu Trợ giúp, bạn hãy bấm Trợ giúp Microsoft Visual Basic hoặc nhấn F1. 
3. Thực thi Macro bằng phím tắt 
 Nếu tab Developer không sẵn dùng, hãy làm như sau để hiển thị tab này: 
 Bấm tab File, bấm Option rồi bấm vào thể loại Custom Ribbon 
 Trong danh sách Tab Chính, hãy chọn hộp kiểm Developer rồi bấm OK. 
 Trên tab Developer, trong nhóm Mã, bấm Macros. 
 Trong hộp Tên Macro, hãy bấm macro mà bạn muốn gán cho một phím Ctrl 
 kết hợp phím lối tắt. 
 Bấm Tùy chọn. Hộp thoại Tùy chọn Macro xuất hiện. 
 Trong hộp Phím tắt, hãy nhập bất cứ chữ thường hoặc chữ viết hoa nào mà 
 bạn muốn dùng với phím Ctrl. 
 104 
 Ghi chú Phím lối tắt sẽ ghi đè lên bất cứ phím lối tắt Excel mặc định tương 
 ứng nào trong khi sổ làm việc có chứa macro đang mở. 
 Trong hộp Mô tả, hãy nhập một mô tả macro. 
 Hãy bấm OK để lưu thay đổi của bạn rồi bấm Hủy để đóng hộp thoại Macro. 
4. Thực thi Macro thông qua trình quản lý Macro. 
 Để chạy macro từ một nút trên Thanh công cụ Truy nhập Nhanh, trước hết 
 bạn phải thêm nút vào thanh công cụ. Để làm như thế: 
 Hãy bấm vào tab Tệp, bấm Tùy chọn rồi sau đó bấm Thanh công cụ Truy 
 nhập Nhanh. 
 Trong danh sách Chọn lệnh từ, hãy chọn Macro. 
 Trong danh sách, hãy bấm macro mà bạn đã tạo rồi sau đó bấm Thêm. 
 Để thay đổi ảnh nút của macro, hãy chọn macro trong hộp mà nó được thêm 
 vào rồi bấm Sửa đổi. 
 Bên dưới Biểu tượng, hãy bấm vào ảnh nút mà bạn muốn dùng. 
 Để thay đổi tên của macro được hiển thị khi bạn dừng con trỏ trên nút, trong 
 hộp Hiển thị tên, hãy nhập tên mà bạn muốn dùng. 
 Bấm OK để thêm nút macro vào Thanh công cụ Truy nhập Nhanh. 
 Trên Thanh công cụ Truy nhập Nhanh, hãy bấm nút macro mà bạn vừa mới 
 thêm vào. 
5. Thực thi Macro trực tiếp từ VIBAIDE 
 105 
 BÀI 15: TẠO HÀM MỚI TRONG EXCEL BẰNG VBA 
Mục tiêu: 
- Trình bày được khái niệm hàm trong Excel. 
- Trình bày được các bước để tạo hàm mới trong Excel. 
- Tạo được một số hàm đơn giản bằng cách sử dụng VBA. 
- Nghiêm túc, tự giác trong học tập 
Nội dung. 
1. Khái niệm Hàm trong Excel 
 Hàm là những công thức đã được định nghĩa sẵn trong excel thực hiện tính 
 toán dựa trên các số liệu đầu vào gọi là tham số theo 1 trình tự được lập trình 
 dẵn nhằm thực hiện các phép tính từ đơn giản đến phức tạp. 
2. Các bƣớc để tạo hàm mới trong Excel 
 106 
 Khi tạo hàm mới, phải tuân thủ các dạng thức khai báo như sau: 
 Để tạo 1 hàm mới , phải theo các bước sau: 
 Bước 1: Developer/Code/Visual Basic. 
3. Ví dụ 
 VD1: 
 107 
 TÀI LIỆU CẦN THAM KHẢO 
[1] Nguyễn Quỳnh, Giáo trình Microsoft Excel nâng cao,  
[2] Đinh Thế Hiển, Excel Ứng Dụng - Phân Tích Hoạt Động Kinh Doanh & Tài 
Chính Kế Toán, NXB Thống kê, 2007; 
[3] Nguyễn Ngọc Hiển, Dẫn Thực Hành Sổ Sách Kế Toán Lập Báo Cáo Tài 
Chính Và Báo Cáo Thuế GTGT Trên Excel, NXB Tài chính, 2007. 
 108 
109