CHƯƠNG III CHƯƠNG TRÌNH XỬ LÝ BẢNG TÍNH MICROSOFT EXCEL
BÀI 4. CÁC HÀM VỀ DỮ LIỆU
II. CÁC HÀM TÍNH TOÁN THỐNG KÊ
1.1. Tổ chức dữ liệu trong MS Excel
Trong MS Excel, dữ liệu tổ chức dưới dạng bảng biểu. Dữ liệu trong một cột chứa các giá trị tương tự nhau, ví dụ trong cùng cột chứa các giá trị cùng kiểu như Họ tên:
Nguyễn Văn A, Trần Thị C,… Cột có thể được đặt tên theo dữ liệu mà cột đó lưu trữ, ví dụ tên nhãn cột là Họ tên, Lớp,… Các cột như vậy gọi là các trường giá trị (value), với tên cột là tên trường (field name).
Tập hợp các giá trị trên cùng một dòng là tất cả các thông tin cần lưu trữ cho đối tượng đó được gọi là một bản ghi (record). Và bảng dữ liệu được tổ chức như vậy được gọi là cơ sở dữ liệu (databases) hay danh sách dữ liệu (list).
A B C
1
2 HỌ VÀ TÊN NGÀY SINH LỚP
3 Trần Thị T 8 – 4 - 1973 Q7
4 Nguyễn Văn A 1 - 1 - 1992 AJ13
5 Lê Văn C 12 - 12 - 1981 QE12
Nhãn cột (Tên trường) Hàng (Bản ghi)
Cột (Trường)
Giáo trình TIN VĂN PHÒNG 154 1.2. Quy ước về tổ chức dữ liệu
Khi tổ chức danh sách dữ liệu nên tuân theo một số chỉ dẫn sau:
− Mỗi worksheet chỉ nên có một danh sách dữ liệu,
− Mỗi cột phải có duy nhất một tên trường,
− Mỗi một cột chỉ chứa các giá trị tương tự nhau,
− Bảng không nên chứa các ô trộn (Merge cell),
− Danh sách phải độc lập có nghĩa là có ít nhất 1 hàng, 1 cột để phân cách danh sách với các dữ liệu không thuộc bảng khác,
− Khi thao tác trên danh sách thì danh sách phải được hiển thị tất cả các cột và các hàng.
2. Sử dụng hàm tính toán trên danh sách dữ liệu
Ngoài việc tính toán giá trị trong danh sách, MS Excel có hỗ trợ tính toán dựa trên danh sách dữ liệu. Các hàm này thuộc nhóm Databases và có tên bắt đầu bằng chữ D, nó cung cấp một hướng khác để tính toán thống kê trên cơ sở dữ liệu.
Do các hàm chỉ tính toán, thống kê trên các bản ghi được chọn hay nói khác thoả mãn điều kiện nào đó, nên các hàm này có sử dụng vùng điều kiện. MS Excel cũng chỉ tính toán trên các dòng (bản ghi) thoả mãn điều kiện.
Cách thực hiện trong Excel là lấy từng dòng điều kiện sau đó duyệt các bản ghi trong danh sách và kiểm tra theo điều kiện đó, nếu bản ghi nào thoả mãn điều kiện duyệt thì được tham gia vào công thức tính toán, thống kê mà người dùng yêu cầu.
Lưu ý: hầu hết các hàm này thường tính trên các trường giá trị kiểu số, kiểu ngày tháng, không thao tác tính toán trên các trường có kiểu dữ liệu khác.
2.1. Cấu trúc chung của hàm tính toán trên danh sách
− Cú pháp: DFunction_name(Databases, Field, Criteria)
− Databases là đối số chỉ danh sách dữ liệu dùng để tính toán thống kê, danh sách được chỉ định là địa chỉ tham chiếu vùng (A4:T16). Field là đối số chỉ cột được sử dụng để trích rút, tính toán trong hàm. Đối số này có thể là chuỗi ký tự chỉ tên trường (“Họ và tên”), địa chỉ ô chứa tên trường (C4) hoặc số thứ tự của cột (3) trong danh sách (Cột được tính thứ tự từ trái qua phải và bắt đầu từ 1. Criteria là đối số chỉ vùng các ô mà chứa các điều kiện của hàm tính toán.
2.2. Xây dựng vùng chứa Criteria (Điều kiện) a. Quy ước vùng điều kiện
− Vùng điều kiện có nhãn (tiêu đề cột) giống hệt tiêu đề cột của bảng dữ liệu. Cách tốt nhất là copy nhãn cột của bảng dữ liệu xuống làm tiêu đề cho vùng điều kiện.
Khi đó hai tiêu đề này là đồng nhất.
− Vùng điều kiện có thể đặt bất kỳ vị trí nào trong trang tính, nhưng không được đặt vùng điều kiện đó nằm trong vùng các ô chứa Danh sách dữ liệu.
Giáo trình TIN VĂN PHÒNG 155
− Các dòng điều kiện cần đặt ngay dưới sát với dòng tiêu đề của vùng điều kiện.
− Các điều kiện sẽ được nhập tương ứng tại cột có nhãn đó và được sử dụng các ký tự so sánh giá trị như >=, <=,…
− Ô nhận kết quả tính toán cũng không được nằm trong vùng Danh sách dữ liệu và vùng chứa điều kiện.
- Các điều kiện cùng thoả mãn (tương đương với hoạt động của hàm AND()) được nhập trên cùng một dòng, còn các điều kiện lựa chọn (nghĩa là chỉ thoả mãn một trong các điều kiện, tương đương với hàm OR()) được nhập trên các dòng khác nhau.
b. Ví dụ về vùng điều kiện
Ví dụ 1: Tính lương phải trả cho các cán bộ đã làm việc trên 5 năm. Có nghĩa là bài toán muốn tính tổng lương của các cán bộ có Thâm niên trên 5 năm. Điều kiện cần đặt trên cột Thâm niên, và vùng điều kiện được xây dựng là
STT Họ và tên Ngày bắt đầu ... Thâm niên ...
>5
Ví dụ 2: Đưa ra số người có thâm niên từ 10 năm trở lên và là Tiến sỹ. Khi đó điều kiện cần thoả mãn là Thâm niên >= 10 và Bằng cấp = TS, nên dùng toán tử AND.
Vùng điều kiện như sau:
... Ngày bắt đầu Bằng cấp ... Thâm niên ...
TS >=10
Khi đó chỉ những dòng (bản ghi) của cán bộ thỏa mãn cả hai điều kiện trên được đếm.
Ví dụ 3: Đếm số người hoặc làm việc trên 7 năm hoặc là Thạc sỹ. Khi đó điều kiện gồm Thâm niên >= 7 hoặc Bằng cấp = ThS. Ta thấy những dòng (bản ghi) thỏa mãn chỉ một trong hai điều kiện trên sẽ được tính, nên dùng toán tử OR. Vùng điều kiện có dạng sau:
... Ngày bắt đầu Bằng cấp ... Thâm niên ...
>=7 ThS
Ví dụ 4: Tính số người làm việc trên 7 năm mà là Thạc sỹ hoặc Tiến sỹ. Trong trường hợp này, điều kiện gồm (Thâm niên >= 7 và ( Bằng cấp = ThS hoặc Bằng cấp = TS). Nếu điều kiện trên dòng 1 là Thâm niên >= 7, Bằng cấp = “TS” và trên dòng 2 chỉ có Bằng cấp = “ThS” thì Excel sẽ lọc được các bản ghi thoả mãn điều kiện ở dòng 1 hoặc các bản ghi thoả mãn dòng 2.
... Ngày bắt đầu Bằng cấp ... Thâm niên ...
TS >=7
ThS
Giáo trình TIN VĂN PHÒNG 156 Nhưng như thế máy đã hiểu sai ý của người dùng. Ở đây, họ muốn đếm những người vừa làm việc trên 7 năm vừa là Thạc sỹ. Khi đó điều kiện đưa ra trên dòng 2 cũng phải bao gồm 2 điều kiện tương tự như dòng 1. Vùng điều kiện đúng sẽ là:
... Ngày bắt đầu Bằng cấp ... Thâm niên ...
TS >=7
ThS >=7
Ví dụ 5: Đếm số người có thâm niên làm việc từ 4 đến 10 năm, khi đó vùng điều kiện phải bao gồm Thâm niên >= 4 và Thâm niên <= 10, để thoả mãn đồng thời cả 2 điều kiện thì các điều kiện này phải trên cùng một dòng. Do vậy, vùng điều kiện phải chứa 2 tiêu đề cột Thâm niên và có dạng như sau:
... Ngày bắt đầu Bằng cấp ... Thâm niên ... Thâm niên
TS >=4 <=10
Ví dụ 6: Có bao nhiêu người họ Nguyễn làm việc trong công ty. Điều kiện đặt ra trên cột Họ và tên, và trong cột Họ và tên phải chứa từ “Nguyễn” ở đầu dòng. Vùng điều kiện được xây dựng như sau:
STT Họ và tên Ngày bắt đầu ...
Nguyễn
Khi đó tất cả các dòng chứa từ Nguyễn ở đầu ô của cột Họ và tên sẽ được tính. Điều kiện đặt ra với xâu ký tự trong trường hợp này là chỉ cần chứa giá trị chuỗi đó.
Nếu muốn thao tác trên các dòng chỉ chứa duy nhất từ Nguyễn trong cột Họ và tên thì vùng điều kiện phải là:
STT Họ và tên Ngày bắt đầu ...
=“=Nguyễn”
Ví dụ 7: Cho vùng dữ liệu sau
… Chức vụ Lương
TS 450
DH 280
TC 240
ThS 340
TS 500
TAS 600
Tính lương phải trả cho các cán bộ có mã Chức vụ là TS, ThS và TAS. Với trường hợp này, có thể xây dựng vùng điều kiện tương tự như ví dụ 3, có nghĩa là Chức vụ = TS hoặc Chức vụ = ThS hoặc Chức vụ = TAS. Tuy nhiên, có thể thấy cả 3 khả năng trên đều có chứa chuỗi ký tự TS. Nên điều kiện có thể xây dựng là Chức vụ chứa TS và có thể có hoặc không có thêm ký tự khác giữa 2 ký tự này.
MS Excel cho phép dùng: ký tự * khi xây dựng điều kiện với ý nghĩa có thể có ký tự bất kỳ hay ký tự ? với ý nghĩa có duy nhất 1 ký tự tại ví trí đó. Để sử dụng các ký tự
Giáo trình TIN VĂN PHÒNG 157 đưa trong biểu thức điều kiện, cần gõ điều kiện như xây dựng công thức, theo cách
=“=Xâu ký tự điều kiện có chứa ký tự đặc biệt”.
Và công thức cho ví dụ trên sẽ là
… Chức vụ
=“=T*S”
Cách sử dụng ký tự ? cũng làm tương tự.
3. Một số hàm dữ liệu 3.1. Hàm DCOUNT()
− Hàm DCOUNT() dùng để đếm số ô có chứa giá trị số.
− Field là đối số tùy chọn, nếu có thì phải chọn là một cột có kiểu dữ liệu số.
− Chú ý: Trường dữ liệu nhất thiết phải là cột dữ liệu kiểu số. Tuy nhiên, để dễ cho người dùng và không phụ thuộc vào dữ liệu tốt nhất nên lấy trường dữ liệu Số thứ tự, khi đó người dùng không cần quan tâm đến cột nào có dữ liệu kiểu số trên bảng.
− Ví dụ: Đếm những người có bằng cấp là Thạc sỹ. Khi đó, vùng dữ liệu là cả bảng dữ liệu, vùng điều kiện có tiêu chuẩn đánh giá là tại cột Bằng cấp có giá trị là Thạc sỹ, còn trường dữ liệu ở đây phải là một trường dữ liệu có kiểu số nên ta chỉ định là cột Số thứ tự (hoặc cột số 1) và công thức DCOUNT(A4:T16,1,I20:I21).
BẰNG CẤP TS 3.2. Hàm DMAX() và DMIN()
− Hàm DMAX() và hàm DMIN() dùng để tìm ra giá trị lớn nhất hay nhỏ nhất trong danh sách thoả mãn điều kiện.
− Đối số Field chỉ làm việc với kiểu dữ liệu số hoặc ngày tháng.
− Ví dụ 1: Tìm ra số năm làm việc lâu nhất của các cán bộ là Tiến sỹ. Vùng điều kiện được xây dựng:
BẰNG CẤP TS
Trường dữ liệu cần lọc chính là trường Ngày bắt đầu và trường có thứ tự là 7 (Số năm làm việc lâu nhất chính là người có Ngày bắt đầu làm việc sớm nhất). Nên hàm dữ liệu được dùng ở đây là hàm DMIN() và công thức là DMIN(A4:T16,7,I20:I21).
− Ví dụ 2: Những cán bộ là Thạc sỹ đã làm trên 3 năm mà có số ngày công nhiều nhất là bao nhiêu. Vùng điều kiện sẽ là:
SỐ NĂM BẰNG CẤP
>=3 ThS
Giáo trình TIN VĂN PHÒNG 158 Trường cần tính toán, thống kê ở đây là trường Ngày công và sử dụng hàm DMAX().
3.3. Hàm DSUM() và DAVERAGE()
− Hàm DSUM() tính tổng cộng giá trị trong trường dữ liệu của các bản ghi thoả mãn điều kiện.
− Hàm DAVERAGE() tính giá trị trung bình tại trường dữ liệu của các bản ghi thoả mãn điều kiện lọc.
− Chú ý: Khi dùng hàm DAVERAGE() mà không có bản ghi nào thỏa mãn điều kiện thì MS Excel báo lỗi #DIV/0! (lỗi chia 0).
− Ví dụ 1: Tìm tổng tiền lương trả cho các cán bộ là Thạc sỹ hoặc Tiến sỹ. Với hàm sử dụng là DSUM và vùng điều kiện như sau:
BẰNG CẤP TS ThS
− Ví dụ 2: Tính lương trung bình của các Cán bộ làm trong phòng Marketing mà vào làm năm từ năm 2000 đến 2004. Ở đây ta sử dụng hàm DAVERAGE() với điều kiện:
PHÒNG BAN NĂM BẮT ĐẦU NĂM BẮT ĐẦU
Thương mại >=2000 <=2004
3.4. Hàm DGET()
− Hàm DGET() trả về giá trị duy nhất của cột chỉ ra ở đối số thứ 2 của bản ghi mà thoả mãn điều kiện.
− Chú ý: Nếu có nhiều hơn 1 bản ghi thoả mãn điều kiện thì MS Excel báo lỗi
#NUM!. Nếu không có bản ghi nào thoả mãn điều kiện thì có thông báo lỗi
#VALUE!.
− Ví dụ: Đưa ra Tên cán bộ là Giảng viên chính (GVChinh) làm ở bộ môn Ngoại ngữ có thâm niên trên 10 năm.
NGHỀ NGHIỆP BỘ MÔN THÂM NIÊN
Thương mại Ngoại ngữ >10
4. Nhận xét
Nếu bạn đã sử dụng thành thạo một hàm thì sẽ dễ dàng biết cách dùng các hàm khác.
Điểm mạnh duy nhất của các hàm thống kê trên cơ sở dữ liệu chính là ở chỗ nếu giá trị dữ liệu thay đổi thì kết quả tính toán, thống kê tự động thay đổi theo một cách nhanh chóng. Và nếu điều kiện/ tiêu chuẩn thống kê mà thay đổi thì kết quả cũng tự động thay đổi theo.
Giáo trình TIN VĂN PHÒNG 159