Giáo trình Excel căn bản

; OK 
Ví dụ 5.3.4: Thiết kế bảng tính như sau. Sử dụng DV thực hiện các yêu cầu 
1. Vùng ô có địa chỉ C5:C12 nhập các khoản chi phí đảm bảo tổng các khoản chi phí 
không vượt quá giá trị trong địa chỉ C2 
2. Khi chuột trỏ vào vùng địa chỉ C5:C12 sẽ hiện lên thông báo số (1) 
3. Khi tổng các khoản chi vượt quá giá trị trong C2 thì hiển thị thông báo số (2) 
 (1) (2)
 =118= 
Hướng dẫn VD 5.3.4 
1. Vùng ô có địa chỉ C5:C12 nhập các khoản chi phí đảm bảo tổng các khoản chi phí 
không vượt quá giá trị trong địa chỉ C2 
 Chọn C5:C12; mở hộp thoại DV 
 Mục Setting 
 Dưới Allow: Custom 
 Fomula: =SUM($C$5:$C$12)<=$C$2 
 OK 
2. Khi chuột trỏ vào vùng địa chỉ C5:C12 sẽ hiện lên thông báo số (1) 
 Chọn C5:C12; mở hộp thoại DV 
 Mục Input Message 
 Đánh dấu: Show input message when cell is selected 
 Title: Chi trong tháng 
 Input Message: Chú ý: Tổng các khoản chi không vượt quá 15000000 
 OK 
3. Khi tổng các khoản chi vượt quá giá trị trong C2 thì hiển thị thông báo số (2) 
 Chọn C5:C12; mở hộp thoại DV 
 =119= 
 Mục Error Alert 
 Đánh dấu: Show error alert after invalid data is entered 
 Style: Stop 
 Title: Stop 
 Error message: Bạn đã tiêu quá số tiền cho phép 
 OK 
Ví dụ 5.3.5: Nhập danh sách 
1. Nhập dữ liệu cột A, B, C 
2. Nhập dữ liệu cho cột E từ danh sách của cột C 
3. Nhập dữ liệu của cột F vào cột E (huyện phải thuộc tính) 
Chú ý: Để là ví dụ này, trước hết tôi giới thiệu với các bạn hàm OFFSET mà trong đề 
cương bài giảng Tin ứng dụng bạn chưa gặp 
 Chức năng: Hàm OFFSET sẽ trả về một tham chiếu đến một vùng nào đó, vị trí bắt 
 đầu của tham chiếu từ một ô hay một vùng nào đó cho trước với độ lớn của vùng 
 được chỉ định. 
 =120= 
 Cú pháp: OFFSET(reference,rows,cols,height,width) 
 Các đối số: 
 Reference: Là vùng hay ô được chọn làm điểm xuất phát (điểm mốc) của vùng 
 tham chiếu sẽ trả về trong công thức. 
 Rows: Là số chỉ số dòng lệch lên hay lệch xuống của vùng tham chiếu trả về so với 
 vùng được chọn làm mốc. Rows có giá trị âm "-" thì vùng trả về sẽ lệch lên trên so 
 với vùng chọn làm mốc và ngược lại. 
 Cols: Là số chỉ số cột lệch qua trái hay qua phải của vùng tham chiếu trả về so với 
 vùng được chọn làm mốc. Cols có giá trị âm "-" thì vùng trả về sẽ lệch sang trái so 
 với vùng chọn làm mốc và ngược lại. 
 Height: Là số chỉ số dòng (độ cao) có trong vùng tham chiếu sẽ trả về trong công 
 thức. 
 Width: Là số chỉ số cột (độ rộng) có trong vùng tham chiếu sẽ trả về trong công 
 thức. 
 Chú ý: Nếu trong công thức không nhập giá trị của height và width thì xem như nó 
 bằng với độ lớn của Reference gốc. 
Ví dụ 5.3.6. Về cách sử dụng hàm OFFSET 
Hướng dẫn Ví dụ 5.3.6 
1. Nhập dữ liệu cột A, B, C (Nhập bình thường) 
2. Nhập dữ liệu cho cột E từ danh sách của cột C 
 Chọn E2:E13; mở hộp thoại DV 
 Mục Setting 
 Dưới Allow: List 
 Source: C2:C4 
 OK 
 =121= 
3. Nhập dữ liệu của cột F vào cột E (huyện phải thuộc tính) 
 Chọn F2:F13; mở hộp thoại DV 
 Mục Setting 
 Dưới Allow: List 
 Source: Gõ công thức 
 =OFFSET($A$1;MATCH(E2;$A$2:$A$21;0);1;COUNTIF($A$2:$A$21;E2);1) 
 OK 
5.3.3. Tổng hợp dữ liệu từ nhiều bảng - Consolidate 
 Chức năng Consolidate cho phép hợp nhất dữ liệu từ nhiều vùng dữ liệu nguồn 
(Sources) và hiển thị kết quả trong vùng dữ liệu đích (Destination). 
Ví dụ 5.3.7: Giả sử công ty xăng dầu ABC có 3 cửa hàng, mỗi cửa hàng có một bảng báo 
cáo doanh thu 6 tháng đầu năm 2015. Công ty có nhu cầu tổng hợp các báo cáo của 3 cửa 
hàng thành một báo cáo. Để làm điều này ta dùng Consolidate 
Bảng doanh thu cửa hàng 1 
Bảng doanh thu cửa hàng 2 
 =122= 
Bảng doanh thu cửa hàng 3 
Thực hiện các bước sau: 
(1) Chọn vùng dữ liệu đích bằng cách trỏ chuột vào một ô bất kỳ ở vùng đó (‘TH’!A5) 
(2) Chọn lệnh Data \ Data Tools \ Consolidate, xuất hiện hộp thoại Consolidate 
 Hộp Function: Chọn hàm sử dụng (Sum, Min, Max, ), chọn Sum để tính tổng. 
 Hộp Reference: Để tham chiếu lần lượt các vùng dữ liệu nguồn: 
 ‘CH1’!$A$5:$G$8; ‘CH2’!$A$5:$G$8; ‘CH3’!$A$5:$G$8 
 Chú ý: Sau mỗi lần chọn vùng dữ liệu nguồn, nhấp chọn Add 
 Hộp All references: Chứa tất cả các vùng dữ liệu nguồn cần thiết cho việc hợp 
 nhất. Để xóa một vùng dữ liệu trong hộp All references, bạn chọn vùng đó, rồi 
 nhấp nút Delete. 
 Top row: chọn nếu muốn dùng tên cột của vùng nguồn, 
 =123= 
 Left column: Chọn nếu muốn dùng các giá trị của cột đầu tiên của vùng nguồn, ở 
 đây là giá trị của cột Mặt hàng, 
Create links to source data: Chọn nếu muốn dữ liệu hợp nhất được cập nhật mỗi khi có 
thay đổi ở vùng dữ liệu nguồn. 
Ví dụ 5.3.8. Bài tập tương tự 
5.3.4. Định dạng có điều kiện - Conditional Formatting 
1/ Tổng quan về định dạng có điều kiện 
 =124= 
 Conditional Formatting (CF) là công cụ định dạng theo điều kiện trong Excel 
 Một tính năng tuyệt vời của Excel từ 2007 
 Định dạng theo điều kiện (CF) là định dạng động, khác với cách định dạng 
 thông thường đó là định dạng tĩnh 
 Có hai phương phương pháp định dạng kiểu CF 
 Định dạng theo các qui luật định sẵn 
 Định dạng theo qui luật tự thiết lập 
2/ Định dạng theo quy luật định sẵn 
a/ Highlight Cells Rules 
 Làm nổi bật các ô theo một trong các điều kiện: Greater Than... (lớn hơn), Less 
 Than... (nhỏ hơn), Equal To (bằng) một giá trị so sánh nào đó, Between (giữa 2 giá 
 trị), Text that Contains (ô chữ có chứa chuỗi ký tự quy định), A Date Occurring 
 (theo quãng thời gian), Duplicate Values (ô dữ liệu trùng nhau). 
 Mở chức năng Highlight Cells Rules: Home \Style \Conditional Formatting 
 \Highlight Cells Rules (hình ảnh sau đây xuất hiện) 
 =125= 
Ví dụ 5.3.8. Thực hành Highlight Cells Rules 
1/ Nhập bảng tính sau 
2/ Yêu cầu định dạng bảng tính trên theo bằng Highlight Cells Rules 
 - Giá trị >0 font chữ in đậm mầu xanh (lời) 
 - Giá trị <0 font chữ in đậm mầu đỏ (lỗ) 
 - Giá trị =0 font chữ in đậm mầu đỏ sáng (hòa vốn) 
3/ Yêu cầu định dạng bảng tính trên theo bằng Highlight Cells Rules \Manage Rules 
sao cho 
 - Giá trị >0 font chữ in đậm mầu xanh đậm (lời) 
 - Giá trị <0 font chữ in đậm mầu đỏ đậm (lỗ) 
 - Giá trị =0 font chữ in đậm mầu vàng cam (hòa vốn) 
 - Tô mầu nền: Giá trị > 0 mầu xanh, Giá tri = 0 mầu trắng, Giá trị <0 mầu đỏ 
Hướng dẫn cách làm (ý 3) 
 Home \Styles \Conditional Formatting \Manage Rules 
 =126= 
 Hộp thoại Conditional Formatting Rules Manager \New Rule 
 Chú ý: 
 Mỗi lần chọn New Rule ta đặt thêm một định dạng cho bảng tính (để có 
 kết quả dưới đây ta phải đặt 4 điều kiện) 
 Để xóa bỏ các định dạng có điều kiện đã thiết lập: Chọn vùng ô \Home 
 \Conditional Formatting \Clear Rules \Clear Rules from Selected Cells 
Kết quả 
b/ Top Bottom Rules 
 Quy luật này gồm các điều kiện: Top 10 Items (đánh dấu 10 ô có giá trị lớn 
 nhất), Top 10% (đánh dấu 10% số ô có giá trị lớn nhất), tương tự với Bottom 
 10 Items và Bottom 10%, Above Average (ô có giá trị lớn hơn giá trị trung 
 bình của cột/hàng), Below Average (ô có giá trị nhỏ hơn giá trị trung bình của 
 cột/hàng) 
 Mở chức năng Top /Bottom Rules: Home \Style \Conditional Formatting 
 \Bottom Rules 
 =127= 
Ví dụ 5.3.9. Thực hành Top Bottom Rules 
 Yêu cầu: Nhập bảng tính sau đó định 
 dạng: 
 + Những ô có giá trị lớn hơn giá trị 
 trung bình của dãy số B2:B12 thì tô 
 mầu nền đỏ sáng, số mầu đỏ tối 
 + Gõ thay đổi giá trị trong các ô đó 
 xem sự thay đổi xẩy ra thế nào. Hãy 
 giải thích tại sao 
c/ Data Bars 
 Áp dụng các đồ thị hình thanh trực tiếp trong các ô tương ứng với giá trị đang 
 chứa trong ô. 
 Mở chức năng Data Bars: Home \Style \Conditional Formatting \Data Bars 
 Thực hành Data Bars 
Ví dụ 5.3.9: Sử dụng Data Bars để vẽ đồ thị hình thanh trực tiếp trong các ô để so 
sánh GPD dự tính của các nước Châu Á năm 2009. 
Hướng dẫn: Định dạng theo điều kiện sử dụng Data Bars 
 Home\CF\Data Bars\Orange Data Bars 
 Chú ý: 
 Data Bars chỉ cung cấp 6 màu cơ bản, để có nhiều lựa chọn hơn ta nhấn vào 
 More Rules Hộp thoại 
 New Formatting Rule cung cấp cho bạn thêm một số tùy chọn như: 
 Show bar only: Ẩn các giá trị trong ô 
 Sử dụng các hộp Type và Value để điều chỉnh cách hiển thị của các thanh đồ 
 thị theo giá trị trong các ô 
 Bar Color: Danh mục các màu cho thanh đồ thị 
 Ô chứa giá trị nhỏ nhất trong vùng định dạng luôn luôn có chiều dài thanh đồ 
 thị bằng 10% chiều rộng của ô 
 Kết quả 
 =128= 
d/ Color Scale 
 Áp dụng màu nều tương ứng với giá trị chứa trong ô 
 Mở chức năng Color Scale: Home \Style \Conditional Formatting \Color Scale 
Ví dụ 5.3.10: Dùng các màu để tô nền một bảng số liệu về thị phần trình duyệt web năm 
2009 thống kê qua các tháng. Màu càng xanh là thị phần càng nhiều và màu càng đỏ thì 
thị phần càng ít. 
 Yêu cầu: Nhập và định dạng như mẫu sau 
 =129= 
e/ Icon Sets 
 Hiển thị các icon trực tiếp trong các ô. Các icon hiển thị phụ thuộc vào giá trị chứa 
 trong ô. 
 Mở chức năng Icon Sets: Home \Style \Conditional Formatting \Icon Sets 
Ví dụ 5.3.11: 
Yêu cầu 1 
Sử dụng Icon Set để định dạng sự thay đổi của giá tại cột E sao cho 
Khi % thay đổi dương thêm vào phía trước của ô hình mũi tên hướng lên mầu xanh 
Khi % thay đổi bằng 0 thì thêm vào phía trước của ô hình mũi tên nằm ngang màu 
vàng 
Khi % thay đổi giảm thì thêm vào hình mũi tên hướng xuống màu đỏ 
Yêu cầu 2 
Sử dụng định dạng Highlight Cells Rules định dạng cột thay đổi (D) 
Khi thay đổi > 0 số mầu xanh in đậm 
Khi thay đổi = 0 số mầu vàng gia cam 
Khi thay đổi <0 số màu đỏ 
Yêu cầu 3 
Sử dụng định dạng Icon Set để định dạng cột H sao cho khối lượng > 1000000 
Như vậy chỉ hiển thị một Icon trong một bộ Icon 
 =130= 
 Hướng dẫn thực hiện 
 =131= 
Chú ý: 
Khi bạn không vừa ý với các định dạng đã chọn, để xóa chúng mà không mất dữ liệu, bạn 
bấm Conditional Formatting \ Clear Rules, chọn một trong các kiểu xóa như Clear Rules 
from: Selected Cells (chỉ xóa trong cột chọn), Entire Sheet (xóa trong cả sheet), This 
Table (chỉ xóa trong bảng đang xử lý). 
3/ Định dạng theo quy luật tự thiết lập 
 Người dùng có thể tạo qui luật định dạng mới tại New rules 
 Quản lý các qui luật định dạng bằng công cụ Manage Rules 
 Manage Rules: Hiển thị hộp thoại Conditional Formatting Rules Manager (CF), tại 
 CF có thể tạo mới, chỉnh sửa hoặc xoá các qui luật đã tạo. 
a/ New Rules 
 Cho phép tạo ra qui luật CF mới dựa vào kết quả luận lý của một công thức 
 =132= 
 Hộp thoại New Formatting Rule 
 Use a formula to determine which cells to format: Qui luật này áp dụng định 
 dạng dựa trên kết quả luận lý của các công thức. 
 Ta có thể sử dụng linh hoạt kết quả của công thức và hàm để làm điều kiện định 
 dạng. 
 Công thức làm điều kiện phải trả về các kết quả luận lý True hoặc False. Nếu công 
 thức trả về True có nghĩa là điều kiện được thõa mãn và định dạng theo điều kiện 
 được áp dụng. Nếu công thức trả về False thì định dạng theo điều kiện không áp 
 dụng. 
 Nhập công thức điều kiện 
 Home | Style | CF | New rules 
 Trong hộp thoại New Formatting Rule | chọn Use A Formula To Determine Which 
 Cells To Format 
 Nhập công thức cần làm điều kiện vào hộp Format values where this formula is 
 true 
 Nút Format để thiết lập các định dạng 
 =133= 
Ví dụ 5.3.12: 
Ví dụ 5.3.13: 
 -Tại A19 gõ ngày đầu tháng 
 -Tại B19 gõ =A19 
 -Sao chép A19 đến AA19 
 -Chọn A19:AA19 định dạng kiểu dd 
 -Thu nhỏ cột từ A19:AA19 cho hợp lý 
 -Chọn B19:AA31 
 -Chọn Home\CF\Manage Rules\New Rules  
 Use a formula to determine wich cells to format 
 Format value where this formula true: =OR(WEEKDAY(B$19)=1; 
 WEEKDAY(B$19)=7) 
 =134= 
Ví dụ 5.3.14: 
Ví dụ 5.3.15: 
 =135= 
Ví dụ 5.3.16: 
 =136= 
Ví dụ 5.3.17: 
Ví dụ 5.3.18: 
5.4. Một số hàm trong cơ sở dữ liệu 
5.4.1. Cấu trúc chung của hàm cơ sở dữ liệu. 
 Các hàm dữ liệu thực hiện việc tính toán cho các dòng thỏa mãn điều kiện. Tất cả 
hàm dữ liệu sử dụng chung cú pháp: 
 Function(database; field; criteria) 
 =137= 
Trong đó: 
Function: Tên hàm 
Database: Là cơ sở dữ liệu chứa cột tính toán và điều kiện. 
Field: Tên cột hay tên cột. Tham chiếu các cột bằng nhãn và đặt trong dấu ngoặc kép “”, 
hoặc địa chỉ ô chứa nhãn cột hoặc cũng có thể tham chiếu đến cột thông qua số thứ tự cột 
của nó trong CSDL. 
Criteria: Vùng tiêu chuẩn (hay vùng điều kiện) 
 Bảng: dữ liệu về loại cây 
5.4.2. Các hàm cơ sở dữ liệu thông dụng. 
1. Hàm DAVERAGE() 
- Cú pháp 
 DAVERAGE(database; field; criteria) 
- Chức năng: Tính trung bình các ô trên cột (field) của CSDL thỏa mãn điều kiện cho 
trước 
Ví dụ 5.4.1: 
 Từ bảng 1 Dữ liệu về loại cây, tính sản lượng trung bình của cây Táo có năm lớn 
hơn 11 và năm nhỏ hơn 21 
2. Hàm DCOUNTA(); DCOUNT() 
- Cú pháp 
 =138= 
 DCOUNTA(database; field; criteria) 
 DCOUNT(database; field; criteria) 
- Chức năng của hàm DCOUNTA: Đếm số ô chứa giá trị khác rỗng trong một cột (field) 
của cơ sở dữ liệu thỏa mãn điều kiện. 
- Chức năng của hàm DCOUNT: Đếm số ô chứa giá trị số trong một cột (field) của cơ sở 
dữ liệu thỏa mãn điều kiện 
Ví dụ 5.4.2: Từ bảng 1 dữ liệu về loại cây, đếm số lượng cây Táo có năm lớn hơn 11 và 
năm nhỏ hơn 21. 
 =DCOUNTA(A1:E10; A1;A13:C14) 
3. Hàm DMAX() 
- Cú pháp 
 DMAX(database; field; criteria) 
- Chức năng: Cho biết giá trị lớn nhất trong một cột của cơ sở dữ liệu thỏa mãn điều kiện 
Ví dụ 5.4.3: Từ bảng 1 Dữ liệu về loại cây, sản lượng lớn nhất của cây Táo có năm lớn 
hơn 11 và năm nhỏ hơn 21. 
 =DMAX(A1:E10;5;A13:C14) 
4. Hàm DSUM() 
- Cú pháp 
 DSUM(database; field; criteria) 
- Chức năng: Cho biết giá trị của tổng các số trong một cột của cơ sở dữ liệu thỏa mãn 
điều kiện 
Ví dụ 5.4.4: Từ bảng 1 Dữ liệu về loại cây, tính tổng sản lượng của cây Táo có năm lớn 
hơn 11 và năm nhỏ hơn 21. 
 =DSUM(A1:E10;5;A13:C14) 
Ví dụ 5.4.5: (tổng hợp sử dụng Database) 
 =139= 
1. Tính tổng lương của những nhân viên thuộc phòng TV 
Lập vùng tiêu chuẩn B9:B10. Chọn một ô B11 nhập công thức 
 =DSUM(A1:D7, “Lương”, B9:B10) 
2. Cho biết nhân viên lớn tuổi nhất của phòng TV 
Lập vùng điều kiện B9:C10. Chú ý công thức trong ô C10 là =MAX(B2:B7) 
Chọn một ô C11 nhập công thức =DMAX(A1:D7, B2, B9:C10) 
3. Cho biết số lượng nhân viên có họ bắt đầu bằng chữ T 
Lập vùng tiêu chuẩn A9:A10 (trong đó ô A9 để rỗng, ô A10 bạn nhập công thức = 
LEFT(A3)=”T”) 
Chọn một ô A11 gõ công thức =DCOUNTA(A1:D7, 1, A9:A10) 
4. Tính tổng lương của những người có mức lương từ 3000000 đến 4000000 
Lập vùng tiêu chuẩn D9:D10 (trong đó ô D9 để rỗng, ô D10 bạn nhập công thức 
=AND(D2>=3000000; D2<=4000000) 
Chọn một ô D11 gõ công thức =DSUM(A1:D7;4;D9:D10) 
 =140= 
 TÀI LIỆU THAM KHẢO 
1. Giáo trình tin học ứng dụng, trường ĐHKTQD, chủ biên PGS.TS Hàn Viết Thuận, nhà 
xuất bản ĐHKTQD, năm 2012 
2. Đề cương bài giảng tin học ứng dụng, trường ĐHTCQTKD, chủ biên Ths. Tạ Minh 
Châu, nhà xuất bản Lao động – xã hội, năm 2012 
3. Bài tập thực hành tin học ứng dụng, trường ĐHTCQTKD, chủ biên Ths. Tạ Minh 
Châu, nhà xuất bản Lao động – xã hội, năm 2012 
 =141=