THỰC HÀNH EXCEL BÀI 12 - 123doc

Sử dụng hàm SumProduct để giải bài toán tính điểm trung bình các môn theo các hệ số tùy ý.. - Hàm SumProduct tính tổng các tích một cách tương ứng từ các phần tử của mảng 1 với mảng 2...

BÀI SỐ 12

 Các bài tập bổ sung Sử dụng hàm SumProduct để giải bài toán tính điểm trung bình các môn theo các hệ số tùy ý

 SUMPRODUCT(mảng_1, mảng_2, )

- Hàm SumProduct tính tổng các tích một cách tương ứng từ các phần tử của mảng 1

với mảng 2 cho đến tối đa 32 mảng được phép sử dụng - các mảng này phải có cùng số chiều

 Lưu ý: hàm PRODUCT(so1, so2, ) thực hiện phép nhân liên tiếp các so1, so2, với nhau Ví dụ: Product(2,4,5) = 2*4*5 = 40

BẢNG GHI ĐIỂM

* Với hệ số các môn M1 đến M5 được cho trong bảng sau:

Câu 1 Căn cứ vào hệ số, tính điểm trung bình (TRBINH)

Gợi ý Điểm trung bình được tính bằng cách lấy tổng điểm các môn có nhân hệ số sau đó

chia cho tổng số các hệ số (ở trên là bằng 2+1+3+4+2=12) Công thức có dạng: SUMPRODUCT(điểm, he_so)/SUM(he_so)

Trong đó điểm tham chiếu đến các ô chứa điểm của từng học sinh; he_so là

vùng chứa thông tin về hệ số.

 Hãy để ý tình huống có một số học sinh được miễn một số môn học (ô điểm để

trống) thì công thức có còn đúng không? Rõ ràng trong tình huống này, phép chia cho toàn bộ tổng của các hệ số sẽ làm cho điểm trung bình bị thấp xuống Thay vào đó ta phải xét xem môn nào được miễn để giảm đi hệ số của môn đó Sử dụng hàm SumIf ta có thể khắc phục tình huống này:

SUMPRODUCT(điểm, he_so)/SUMIF(điểm, “>=0”, he_so)

 Hàm SumIf lúc này có nhiệm vụ tính tổng các hệ số tương ứng với các môn học

có điểm lớn hơn hoặc bằng 0, do đó sẽ không tính những môn miễn học (có giá trị rỗng)

Câu 2 Sử dụng hàm SumProduct với các giá trị bố trí theo cột

D32 50 4000 5000 Tổng cộng (số_lượng ´ giá) = ? ?

 Giả sử cần tính tổng cộng toàn bộ các mặt hàng trên theo đơn giá loại 1 mà

không phải tính tổng từng mặt hàng, ta sử dụng hàm SumProduct như sau:

SumProduct(so_luong, don_gia_1) sẽ cho kết quả: 340000 Tương tự tính tổng

theo đơn giá loại 2

BÀI SỐ 12

 Sử dụng công cụ Solver để giải các bài toán đặc biệt.

Trong Excel có bổ sung một số công cụ mạnh để giải các bài toán như: tìm nghiệm của hệ phương trình, giải bài toán tối ưu đó là Solver (trong menu Tools) Trong phần này chỉ giới thiệu một số ví dụ minh họa chức năng này

Bài 1 Giải hệ phương trình sau:

3x + 4y - 3z = 5 4x - 2y + 6z = 40

x + 4y + 8z = 78

Bước 1 Lập mô hình bài toán (theo mẫu dưới đây)

3

8

- Các ô B2:D2 sẽ chứa nghiệm của hệ, đầu tiên ta cho tất cả bằng 1 (và gọi là nghiệm tạm) và sẽ dùng Solver để yêu cầu tính ra nghiệm thật sự

- Các ô B5:D7 chứa hệ số của các phương trình

- Các ô B10:D10 chứa giá trị vế phải của các phương trình

- Các ô B9:D9 là các giá trị trung gian tương ứng với các nghiệm tạm của hệ; được tính bằng cách thay bộ nghiệm tạm vào vế trái của các phương trình (tổng các tích),

do đó ở đây ta dùng hàm SumProduct để tính Công thức ô B9 sẽ là:

[B9] = SumProduct(B5:D5, $B$2:$D$2) = 4 (tương tự với các ô [C9] và [D9])

Bước 2 Cung cấp thông tin cho Solver

Sau khi lập xong mô hình, chọn ô [B9] làm mục tiêu, và gọi lệnh [Tools]Solver, ta có:

- Trong hộp [By Changing Cells] ta dùng mouse để chọn vùng lưu kết quả (nghiệm)

là B2:D2 (Excel sẽ tự động thêm kiểu tham chiếu tuyệt đối)

- Trong vùng [Subjects to the Constraints] ta nhập các ràng buộc dùng làm mục tiêu của lời giải Đối với việc giải hệ phương trình, mục tiêu cần đạt được là các giá trị tạm phải bằng với các giá trị thực có (vế phải của các phương trình) Ở trước ta đã

dùng ô B9 làm đích, vậy còn lại 2 giá trị phải đạt được Ta chọn Add và khai báo

đẳng thức còn lại làm mục tiêu trong hộp:

 Sử dụng nút Add để thêm các ràng buộc, sau khi hoàn tất chọn [OK] để quay về hộp Solver Parameters

Bước 3 Thực hiện lệnh và kết thúc

Sau khi hoàn tất các khai báo, ta chọn nút [Solve] để Excel tự động tính và thông báo kết quả;

 Nếu đồng ý với lời giải thì chọn nút Keep Solver Solution, nếu không thì phục hồi các giá trị gốc Restore Orginal Values và chọn OK để hoàn tất

 Cuối cùng, ta có kết quả như sau:

3

8

 Lưu ý rằng giá trị ở các ô B2:D2 và B9:D9 đã thay đổi

 Tóm lại, để sử dụng Solver đòi hỏi các yêu cầu sau:

- Phải cung cấp đầy đủ các tham số trong hộp thoại Đặc biệt cần lưu ý rằng ô đích (Set Target Cell) cần phải chứa công thức có liên quan đến vùng nghiệm, nếu không Excel sẽ báo lỗi

- Chỉ ra vùng mà Excel sẽ tác động và lưu kết quả, ở đây thường bắt đầu với các giá trị 1 là giá trị tạm thời để Excel có khởi điểm tính toán

- Cung cấp đầy đủ các ràng buộc dùng làm mục tiêu để tính toán

 Bài tập tương tự Giải hệ phương trình sau

3x + 4y - 3z - t = 26

x + 4y + 8z - 6t = 12 (x=8; y=3; z=2; t=4) } 2x - 9y + 5z + 3t = 11

Bài 2 Xét bài toán tìm cực đại lợi nhuận như sau: “Một nhà máy sản xuất 3 sản phẩm

(sph_a, sph_b và sph_c) với tiền lãi tương ứng trên 1 đơn vị sản phẩm là 75, 35

và 50 Các sản phẩm này đòi hỏi một số linh kiện (6 loại) với tương quan giữa chúng được cho bởi bảng và trong bảng còn cho biết số lượng các linh kiện tồn kho Hãy lập kế hoạch sản xuất sao cho lợi nhuận thu được là lớn nhất

 Dùng Solver, ta lập mô hình bài toán như sau:

3 Linh kiện Tồn kho Yêu cầu

 Trong mô hình trên, có các ô chứa công thức như sau:

- Các ô từ C4 đến C9 chứa hàm SumProduct($D$2:$F$2,D4:F4)

- Tính lợi nhuận bằng tiền lãi một sản phẩm nhân với số sản phẩm

- Tổng lợi nhuận bằng tổng cộng các lợi nhuận của các sản phẩm.

 Trong hộp thoại Solver Parameters ta khai báo như sau:

- Chọn ô D12 làm ô đích và giá trị mục tiêu là Max; các ô lưu kết quả là D2:F2 (với các giá trị khởi đầu là 100) và các ràng buộc như sau:

 Sau khi chọn Solve, ta được nghiệm là 107, 105 và 181 với Tổng lợi nhuận là 20750

 Bài tập tương tự Hãy thay đổi các tham số và giải lại bài toán trên Hãy tìm một

bài toán tương tự, (ví dụ lập lịch sản xuất sao cho chi phí thấp nhất) sau đó lập mô hình và giải.