Giáo trình Tin học văn phòng - Hoàng Vũ Luân

OKUP(...)) 
3. Lập vùng điều kiện dạng hoặc (OR) để lọc. 
4. Để tính tổng số lần bán ta dùng DCOUNTA, các giá trị khác thì dùng DSUM. 
) Đối với phép tính tổng theo điều kiện, ngoài hàm DSUM Excel còn cung cấp một 
hàm tương đương, đó là SUMIF 
 Cú pháp: SUMIF(khoảng_sẽ_tính, điều_kiện, khoảng thật sự sẽ tính) 
 Trong đó, khoảng_sẽ_tính tham chiếu đến khoảng các ô sẽ tham gia tính tổng; 
điều_kiện thường có dạng “biểu thức so sánh”; riêng khoảng thật sự sẽ tính là tùy 
chọn, nhưng nếu đưa vào thì tổng kết quả sẽ tính trong vùng này. 
* Ví dụ: xét bảng số liệu sau: 
 A B C D E F 
1 BAP 5 BAP BIA BAP BAP 
2 BIA 8 7 9 5 4 
3 BAP 7 
4 GAO 9 
5 BIA 6 
- Khi đó công thức: SUMIF(A1:A5,"BAP",B1:B5) sẽ có giá trị là 12; tương đương 
với việc dùng hàm DSUM với điều kiện tên hàng là BAP. 
- Tương tự ta có: SUMIF(C1:F1,"BAP",C2:F2) = 16 
) Thử dùng SUMIF để giải lại câu 4 ở trên. 
) Cùng dạng với SUMIF là hàm COUNTIF(khoảng ô, điều kiện) dùng để đếm số 
các ô trong khoảng ô hợp với điều kiện. 
6. Lập vùng điều kiện từ ngày 01/01/1997 đến 31/12/1997 (năm 1997) và tương tự 
để tính năm 1998. 
7. Lập vùng điều kiện có dạng ngày bán lớn hơn hoặc bằng ngày 01/02/1998 và nhỏ 
hơn ngày 01/03/1998 (trong khoảng tháng 2) 
) Ngoài phương pháp dùng một khoảng ngày như trên, ta còn có thể sử dụng dạng 
công thức trong vùng điều kiện để tính. Ví dụ, đối với câu 6 có thể lập điều kiện 
dạng =YEAR(ô đầu tiên chứa dữ liệu ngày)=1997 (lưu ý trong công thức trên có 
hai dấu =) và nhãn tên trường cần phải bỏ trống. 
Þ Tương tự, điều kiện trong câu 7 sẽ là =MONTH(ô chứa ngày)=2. 
HƯỚNG DẪN THỰC HÀNH EXCEL - BÀI SỐ 10 69 
BÀI SỐ 10 
) Bài tập tổng hợp - dạng đề thi 
BẢNG GHI TÊN, GHI ĐIỂM 
 Điểm chuẩn = 17 
TT SBD HOTEN TEN TRUONG BAN DVAN DTOAN TONG KETQUA
 A00 AI C 
 A11 BINH A 
 A20 CHAU B 
 B31 ANH A 
 B42 BAO C 
 C50 DUNG B 
 C61 HANH B 
 C71 HUONG A 
 D82 DUONG A 
 D90 PHUOC C 
Mã trường Tên trường Ký tự đầu của SBD là mã trường 
A QUOC HOC ký tự cuối của SBD là điểm ưu tiên. 
B HAI BA TRUNG 
C NGUYEN HUE 
D GIA HOI 
Câu 1 Căn cứ vào ký tự đầu của SBD điền thông tin vào TEN TRUONG 
Câu 2 Căn cứ vào SBD điền DVAN và DTOAN (theo bảng điểm ở Sheet2) 
Câu 3 Tính TONG theo các yêu cầu sau: 
 * Nếu ban A hoặc B thì điểm Toán hệ số 2 
 * Nếu ban C thì điểm Văn hệ số 2 
 * Cộng thêm điểm ưu tiên 
Câu 4 Tính KETQUA: Đậu nếu TONG lớn hơn hoặc bằng Điểm chuẩn ngược lại là Rớt 
Câu 5 Lọc ra các danh sách và ghi vào Sheet3 những người: 
 a) Có kết quả Rớt 
 b) Có kết quả Đậu và tổng điểm lớn hơn 23 
Câu 6 Lập báo cáo theo mẫu sau: 
Học sinh trường Điểm trung bình Số học sinh Đậu 
QUOC HOC ? ? 
HAI BA TRUNG ? ? 
NGUYEN HUE ? ? 
GIA HOI ? ? 
Câu 7 Cột TT đánh số theo cột TONG với giá trị cao nhất là 1 
Bảng điểm (Lưu trong Sheet2) 
70  Hoàng Vũ Luân 
SBD DTOAN DVAN 
D90 4 7 
D82 5 4 
C71 8 7 
C61 9 8 
C50 5 5 
B42 3 7 
B31 5 4 
A20 4 5 
A11 7 9 
A00 6 8 
) Ghi bài thực hành với tên BTAP10.XLS 
BÀI SỐ 11 
) 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 ý. 
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. 
HƯỚNG DẪN THỰC HÀNH EXCEL - BÀI SỐ 12 71 
BẢNG GHI ĐIỂM 
TT HTEN M1 M2 M3 M4 M5 TRBINH 
1 AN 5 7 6 8 7 6.8 
2 BINH 8 7 9 6 8 7.5 
3 SON 9 9 8 7 8.1 
4 VAN 8 7 6 9 5 7.3 
5 TUAN 8 4 5 8 5.5 
6 LAN 3 5 4 5 8 4.9 
7 HOA 4 7 6 7 5 5.9 
8 QUANG 5 4 3 5 4 4.3 
9 VINH 9 9 9 8 8.8 
10 THANH 9 7 9 8 9 8.5 
* Với hệ số các môn M1 đến M5 được cho trong bảng sau: 
Môn M1 M2 M3 M4 M5 
Hệ số 2 1 3 4 2 
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 
Tên hàng Số lượng Giá_1 Giá_2 
A12 20 3000 4000 
C21 30 1000 3000 
E23 25 2000 4000 
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. 
72  Hoàng Vũ Luân 
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) 
 A B C D 
1 Biến x y z 
2 Nghiệm (tạm) 1 1 1 
3 
4 Hệ số a b c 
5 phương trình 1 3 4 -3 
6 phương trình 2 4 -2 6 
7 phương trình 3 1 4 8 
8 
9 Giá trị tạm 4 8 13 
10 Mục tiêu 5 40 78 
- 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ó: 
HƯỚNG DẪN THỰC HÀNH EXCEL - BÀI SỐ 12 73 
- 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: 
 A B C D 
1 Biến x y z 
2 Nghiệm 2 5 7 
3 ... ... ... ... 
8 
9 Giá trị 5 40 78 
10 Mục tiêu 5 40 78 
) Lưu ý rằng giá trị ở các ô B2:D2 và B9:D9 đã thay đổi. 
y 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. 
74  Hoàng Vũ Luâ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 
4x - 2y + 6z - 7t = 10 {nghiệm là: 
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: 
 A B C D E F 
1 SPh_a SPh_b SPh_c 
2 Mục tiêu sản xuất -> 100 100 100 
3 Linh kiện Tồn kho Yêu cầu 
4 Lk_1 700 400 2 0 2 
5 Lk_2 850 500 3 0 2 
6 Lk_3 380 300 0 3 0 
7 Lk_4 500 400 2 1 1 
8 Lk_5 650 400 1 0 3 
9 Lk_6 450 200 0 1 1 
10 Tiền lãi/sp 75 35 50 
11 Lợi nhuận 7500 3500 5000 
12 Tổng lợi nhuận 16000 
) 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.y