CHƯƠNG III CHƯƠNG TRÌNH XỬ LÝ BẢNG TÍNH MICROSOFT EXCEL
BÀI 3. SỬ DỤNG CÁC HÀM TRONG EXCEL
I. GIỚI THIỆU VỀ HÀM (FUNCTION)
Hàm (Function) là một công thức định trước có thể tự động tính toán kết quả, thực hiện các thao tác trong bảng tính, hoặc giúp đỡ việc ra quyết định dựa trên thông tin đã cung cấp. Bạn có thể dùng hàm có sẵn trong MS Excel hay tự viết ra hàm mới. Tuy nhiên, ở đây chỉ giới hạn hướng dẫn sử dụng một số hàm có sẵn trong MS Excel.
2. Phân loại hàm
Trong MS Excel có rất nhiều hàm chức năng, tuy nhiên nó được phân thành các nhóm như sau:
− Tài chính (Financial): Tính toán về lãi suất, giá trị mong đợi, giá trị hiện tại của một khoản tiền gửi, tỷ lệ hoàn vốn nội sinh,…
− Ngày giờ (Date & Time): Trả về giá trị ngày tháng năm, giờ phút giây của khoảng giữa hai mốc thời gian, hay giá trị quy đổi tương đương,…
− Tính logic (Logical): Lựa chọn phương án tính toán theo giá trị biểu thức logic, đánh giả biểu thức trả về giá trị đúng, sai,…
− Tìm kiếm và tham chiếu (Lookup & reference): Thực hiện các phép tìm kiếm, quét vùng dữ liệu để trả về giá trị trong vùng tìm kiếm,…
− Thống kê (Statistical): Tính toán thống kê trên các dãy số liệu như tổng cộng, trung bình cộng, độ lệch chuẩn,…
− Cơ sở dữ liệu (Database): Thực hiện các phép toán thao tác trên cơ sở dữ liệu,…
− Toán học (Math & Trig): Các phép toán như tính cos, sin của một góc, làm tròn số, hay lấy phần nguyên của một số, tính tích phân,…
− Văn bản (Text): Xử lý các chuỗi ký tự như lấy độ dài chuỗi, chuyển số thành chuỗi ký tự, cắt chuỗi,…
3. Thành phần của một hàm
Nhìn chung một hàm nội tại của Excel có dạng như sau:
TÊNHÀM(đối số 1, đối số 2,..., đối số n) Ví dụ: SUM(A1,B3,C5)
Đối với từng loại hàm thì đối số có thể là điều kiện kiểm tra tính đúng đắn của dữ liệu (với các hàm logic), địa chỉ ô tham gia vào công thức, hay vùng dữ liệu để tìm kiếm, tên nhãn của vùng dữ liệu,… Do đó, đối số có thể là hằng kiểu số (75000), hằng kiểu văn bản (“Nguyễn Văn A”), biểu thức có giá trị đúng/ sai (A4>=0 hay
Giáo trình TIN VĂN PHÒNG 138 Ngaycong>20), tham chiếu ô (A4), mảng ({A4:A8}) hay địa chỉ tham chiếu vùng dữ liệu (A4:T16), thậm chí là tên (Name) của vùng dữ liệu,…
Mỗi hàm sẽ có số lượng đối số khác nhau, tuy nhiên số lượng đó không vượt quá 30 đối số. Các đối số có thể là bắt buộc hay tùy chọn. Khi xây dựng công thức thông qua cửa sổ xác định đối số của hàm đó (Function Arguments), với các đối số bắt buộc thì tên đối số được viết đậm, còn đối số không bắt buộc thì được viết thường.
4. Chèn hàm vào bảng tính
Có nhiều cách để tạo một công thức tính toán hoặc tự gõ công thức hoặc dùng Paste Function. Với cách tự gõ công thức thì buộc phải gõ dấu = để MS Excel biết bạn đang nhập công thức vào ô đó, tiếp theo là các tham chiếu của các ô tham gia vào công thức và tên hàm hoặc phép toán sử dụng. Nếu dùng Paste Fuction thì dấu = sẽ được MS Excel tự động điền vào, tiếp đó là tên hàm cũng như các tham chiếu ô dùng để tính toán trong công thức đó.
4.1. Cách chèn công thức
− Chọn menu Insert/ Function... hoặc chọn nút fx trên thanh Standard,
− Chọn nhóm chứa hàm cần dùng trong hộp Category của cửa sổ Paste function
− Chọn tên hàm cần dùng trong hộp Function name,
− Chọn OK để chấp nhận việc lấy hàm đó,
− Di chuyển con trỏ chuột lần lượt đến từng hộp đối số để khai báo các đối số (Chú ý:
đối số được mô tả trong phần 3 ở trên) cần thiết của công thức trong hộp thoại xây dựng công thức Function Arguments,
− Chọn OK khi kết thúc.
4.2. Quy ước về viết hàm
− Nếu viết hàm ở đầu công thức thì trước nó phải có dấu bằng (=).
− Nếu đối số nào được viết đậm thì đối số đó là bắt buộc, nếu đối số có dấu (...) tức là được phép nhập vào nhiều đối số cùng dạng.
− Nếu hàm được lồng trong hàm khác thì không cần đánh dấu (=) ở trước nó. Theo quy tắc này thì một hàm cũng có thể làm đối số cho một hàm khác.
− Các ngoặc đơn bao đối số hàm phải theo từng cặp, các đối số được cách nhau bởi dấu phảy (,) và không có khoảng trống.
4.3. Cách chèn hàm lồng trong một hàm khác
Trong một công thức tính có thể có sử dụng nhiều hàm khác nhau. MS Excel cũng hỗ trợ việc xây dựng công thức có hàm lồng nhau này.
− Chèn hàm theo cách thông thường (đã mô tả trong phần 4.1)
− Trong cửa sổ nhập đối số của hàm Function Arguments, nếu đối số nào cần dùng một hàm khác thì chọn nút bên cạnh hộp ngoài cùng bên trái của thanh Formular,
Giáo trình TIN VĂN PHÒNG 139
− Với menu xuất hiện, chọn More Functions… mở cửa sổ Paste Function để lựa chọn hàm cần dùng,
− Tiến hành khai báo đối số cho hàm mới chèn vào,
− Làm tương tự các bước trên nếu cần chèn thêm hàm khác làm đối số cho hàm trên,
− Để quay lại hàm đã gọi chèn hàm mới (vị trí đối số khi bắt đầu thực hiện bước 3) hay bất kể một hàm nào khác trong dãy công thức, bấm phím trái chuột vào tên hàm đó trên thanh Formular.
− Sau khi đã kết thúc quá trình xây dựng công thức, chọn OK (Chú ý: chỉ khi nào dừng việc xây dựng hàm tính toán mới được chọn nút OK).
4.4. Mở cửa sổ xây dựng công thức Function Arguments
Khi một ô nào đó trong bảng tính có chứa hàm, có thể mở lại cửa sổ Function Arguments để thay đổi bằng cách
− Di chuyển con trỏ đến ô chứa công thức cần thay đổi,
− Chọn nút fx trên thanh Formular,
− Các bước còn lại tương tự như trên.
5. Bài toán minh họa
Việc học cách dùng một hàm có sẵn của Excel dựa trên một bài toán cụ thể sẽ giúp cho bạn hình dung một cách dễ dàng hơn. Dưới đây là một bài toán để minh họa cho cách dùng hàm.
5.1. Mô tả bài toán
Công ty điện tử tin học ELINCO, tính tiền lương trả cho cán bộ, nhân viên như sau:
− Lương tối thiểu = 180,000đ
− Lương cơ bản = Hệ số bằng cấp * Lương tối thiểu
− Thâm niên là khoảng thời gian tính từ ngày bắt đầu làm việc cho đến thời điểm hiện tại (là thời điểm tính lương).
− Bảo hiểm: Nếu thời gian làm việc từ 7 năm trở lên thì bảo hiểm bằng 15% lương cơ bản, còn dưới 7 năm bảo hiểm bằng 10% lương cơ bản.
− Phụ cấp trách nhiệm = Hệ số chức vụ * 10% * Lương cơ bản
− Xếp loại: Nếu ngày công trên 24 ngày thì xếp loại A, dưới 18 ngày xếp loại C, còn lại xếp loại B.
− Khen thưởng: Nếu xếp loại A được thưởng 300000đ, loại C phạt 200000đ.
− Tổng lĩnh = Lương cơ bản - Bảo hiểm + Thưởng 5.2. Gợi ý
Với các công thức như trên, khi đưa dữ liệu đó vào bảng tính Excel như dưới đây, thì:
− Lương cơ bản (H4) = Hệ số bằng cấp(bằng cấp ≈ E4) * 180000
− Thâm niên (I4) = Năm hiện tại – Năm của ngày bắt đầu(D4)
Giáo trình TIN VĂN PHÒNG 140
− Bảo hiểm (J4): nếu Thâm niên (I4) ≥ 7 năm ⇒ 15% * Lương cơ bản (H4), còn nếu Thâm niên (I4) < 7 năm ⇒ 10% * Lương cơ bản (H4)
− Phụ cấp trách nhiệm (K4) = Hệ số chức vụ (Chức vụ ≈ F4) * 10% * H4
− Xếp loại (M4): nếu Ngày công (L4) ≥ 24 ⇒ A,
nếu Ngày công (L4) < 18 ⇒ C, còn lại ⇒ B
− Khen thưởng (N4): nếu Xếp loại (M4) = A ⇒ 300000 (Thưởng), còn nếu Xếp loại (M4) = C ⇒ -200000 (Phạt)
− Tổng lĩnh (O4) = Lương cơ bản (H4) - Bảo hiểm (J4) + Thưởng (N4)
A B C D E F G H I J K L M N O
1
2 BẢNG LƯƠNG THÁNG 12/2001 - CÔNG TY ĐIỆN TỬ TIN HỌC ELINCO
3 TT Họ và Tên Ngày
bắt đầu Bằng cấp Chức
vụ Hệ số Lương
cơ bản Thâm niên Bảo
hiểm PCấp t/nhiệm Ngày
công Xếp loại Khen
thưởng Tổng lĩnh
4 1 Trần Lê Nguyên 01/01/1990 TS GĐ 25
5 2 Vũ Minh Nguyệt 06/03/1995 ThS TP 18
6 3 Lê Thanh Hà 25/10/1995 TC CB 28
7 4 Nguyễn Kiều Anh 01/07/2000 CĐ CB 21
8 5 Đoàn Minh Chung 14/02/2001 ThS TP 20
9 6 Phạm Chí Hiếu 01/01/1992 TS PGĐ 15
10 7 Tô Thanh Tâm 25/06/1998 ĐH PP 25
11 8 Nguyễn Việt Nga 01/04/1999 ĐH CB 14
12
13 Ngày lập bảng