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
II. CÁCH DÙNG MỘT SỐ HÀM THÔNG DỤNG
Trong phạm vi môn học, tài liệu cũng chỉ đưa ra một số hàm được học. Các hàm được phân chia theo từng nhóm như sau:
− Date & Time: TODAY(), NOW(), DAY(), MONTH(), YEAR(), WEEKDAY (), DAYS360().
− Text: LEFT(), RIGHT(), MID(), UPPER(), LOWER(), CONCATENATE(), PROPER().
− Logical: IF(), AND(), OR().
− Math & Trig: SUM(), ROUND(), ROUNDDOWN(), ROUNDUP(), INT(), SUMIF().
− Statistical: AVERAGE(), MAX(), MIN(), COUNT(), COUNTIF(), RANK().
− Lookup & Reference: HLOOKUP(), VLOOKUP().
1. Hàm TODAY(), NOW() 1.1. Lý thuyết
− Cú pháp: TODAY()
Giáo trình TIN VĂN PHÒNG 141 NOW()
− Hàm TODAY() và NOW() không có đối số.
− Hàm TODAY() trả về giá trị ngày hệ thống còn hàm NOW() trả về ngày giờ hệ thống (ngày hệ thống là giá trị ngày hiện tại được cài đặt trong hệ thống). Kết quả đưa vào ô nào thì ô đó sẽ được định dạng kiểu ngày tháng.
1.2. Ứng dụng
Cột Thâm niên (H) sẽ ghi giá trị là khoảng thời gian từ khi bắt đầu làm việc cho đến nay. Trước hết ta phải biết được Ngày hiện tại là ngày nào, sau đó lấy Ngày hiện tại trừ đi Ngày bắt đầu thì ta được khoảng thời gian làm việc.
Bạn có thể nhập vào giá trị Ngày hiện tại vào một ô nào đó, sau đó tính khoảng thời gian theo trình tự ở trên. Tuy nhiên cách làm này rất thủ công. Do vậy, ta có thể dùng hàm TODAY() để lấy giá trị Ngày hiện tại đã được thiết lập trong máy tính.
Ví dụ: Ghi giá trị ngày hiện tại vào ô D15, có nghĩa nhập công thức cho ô D15 = TODAY() ⇒ Ngày hệ thống, chẳng hạn như 12/10/2001.
2. Hàm DAY(), MONTH(), YEAR() 2.1. Lý thuyết
− Cú pháp: DAY(Serial_number) MONTH(Serial_number) YEAR(Serial_number)
− Các hàm trả về lần lượt giá trị ngày (Day()), tháng (Month()) và năm (Year()).
− Serial_nu mber là giá trị kiểu Date_Time tương ứng của Excel (01/15/2005) hoặc dự liệu kiểu Text có định dạng kiểu ngày (15, Jan 2005).
2.2. Ứng dụng
Hiển thị ra giá trị tháng, năm của cột Ngày tháng (trong bài Theo dõi bán hàng), sau đó có thể tổng hợp dữ liệu theo tháng, năm. Hoặc lấy ra năm vào làm của nhân viên dựa vào Ngày bắt đầu, khi đó công thức là YEAR(D4).
3. Hàm WEEKDAY() 3.1. Lý thuyết
− Cú pháp: WEEKDAY(Serial_number, Return_type)
− Hàm trả về thứ của ngày đó trong tuần.
− Serial_number là giá trị kiểu Date_Time tương tự như trên. Return_type là đối số tùy chọn, nó chỉ kiểu giá trị trả về và nhận giá trị từ 1 đến 3: nếu là 1 thì Sunday được tính bằng 1; nếu là 2 thì Monday được tính bằng 1; nếu là 3 thì Monday được tính là 0.
3.2. Ứng dụng
Nếu ngày hiện tại là ngày nghỉ (Thứ 7 và Chủ nhật) thì cần ghi Ngày lập bảng là ngày đầu tuần tiếp theo. Khi đó cần kiểm tra giá trị ngày hiện tại có phải là ngày nghỉ không,
Giáo trình TIN VĂN PHÒNG 142 sau đó tính Ngày lập bảng. Giả sử ngày hiện tại là 1/1/2001, khi đó tính thứ của ngày đó với quy ước Chủ nhật (Sunday) có thứ tự là 1, ta có công thức:
WEEKDAY(1/1/2001,1).
4. Hàm DAYS360() 4.1. Lý thuyết
− Cú pháp: DAYS360(Start_date, End_date, [Method])
− Hàm trả về số lượng ngày trong khoảng giữa 2 giá trị ngày cho trước theo quy ước là 1 tháng có 30 ngày.
− Start_date và End_date là giá trị kiểu Date_Time tương tự như trên. Method là giá trị logic quy ước cách tính giá trị: 0 – mặc định (False) theo chuẩn U.S và 1 (True) theo chuẩn Châu Âu.
3.2. Ứng dụng
Giả sử trong bảng có dữ liệu
A B C D E
1 Mã phiếu Ngày giao hàng Ngày trả tiền Số ngày
2 N001 2/1/2007 6/4/2007
3 X001 3/3/2007 18/3/2007
Cho biết sau bao nhiêu ngày thì khách hàng trả tiền theo quy chuẩn U.S. Khi đó cần tính số lượng ngày giữa 2 giá trị Ngày giao hàng và Ngày trả tiền và cột Số ngày sẽ có công thức là DAYS360(B2,C2,0).
5. Hàm LEFT() và RIGHT() 5.1. Lý thuyết
− Cú pháp: LEFT(Text, Num_chars) RIGHT(Text, Num_chars)
− Hàm LEFT() lấy ra các ký tự ở bên trái nhất của chuỗi ký tự (text), còn hàm RIGHT() lấy ra các ký tự ở bên phải nhất của chuỗi ký tự.
− Text là giá trị kiểu ký tự hoặc tham chiếu ô có chứa giá trị kiểu ký tự. Num_chars là số lượng ký tự cần lấy ra.
− Nếu hàm LEFT() và RIGHT() không có đối số thứ hai (Num_chars), thì hàm trả về ký tự đầu tiên phía bên trái và ký tự cuối cùng phía bên phải của chuỗi văn bản.
5.2. Ứng dụng
Giả sử ta có dữ liệu được cho như sau:
A B C D E
1 Mã giáo viên Tên giáo viên Kiểu GV Số tiết Lương
Giáo trình TIN VĂN PHÒNG 143
2 GC01 Lê Thu Vân 36
3 GM01 Nguyễn Tố Trinh 45
Trong đó Kiểu giáo viên được xác định bởi 2 ký tự đầu tiên của Mã giáo viên (Nếu mã GC là Giáo viên cơ hữu, nếu mã GM là giáo viên mời). Do đó dữ liệu khi nhập vào cột Kiểu giáo viên phải được lấy ra từ Mã giáo viên. Tại ô C2 ta xây dựng công thức với hàm sử dụng là hàm LEFT() và có công thức tính LEFT(A2,2) trong đó A2 chính là địa chỉ ô chứa Mã giáo viên.
Giả sử ta đặt trường hợp khác, Kiểu giáo viên chỉ là ký tự thứ 2 từ trái sang trong Mã giáo viên, khi đó cần lấy ra ký tự bên phải của LEFT(A2,2), ta có RIGHT(LEFT(A2,2)).
6. Hàm MID() 6.1. Lý thuyết
− Cú pháp: MID(Text, Start_num, Num_chars)
− Hàm MID() lấy ra các ký tự ở giữa bắt đầu tại vị trí xác định của chuỗi ký tự.
− Text là giá trị kiểu ký tự. Start_num là vị trí ký tự bắt đầu lấy ra. Num_chars là số lượng ký tự cần lấy ra.
6.2. Ứng dụng
Với ví dụ thứ 2 trong phần 4.2, có thể dùng hàm MID() trong đó bắt đầu lấy từ ký tự thứ 2 và lấy ra 1 ký tự, công thức MID(A2,2).
7. Hàm UPPER(), LOWER() và PROPER() 7.1. Lý thuyết
− Cú pháp: UPPER(Text) LOWER(Text) PROPER(Text)
− Hàm UPPER() là chuyển dữ liệu thành chữ hoa, LOWER() là chuyển dữ liệu thành chữ thường, PROPER() thì chuyển các chữ cái đầu tiên của các từ trong dữ liệu thành chữ hoa và tất cả các chữ còn lại là chữ thường.
− Cả 3 hàm chỉ chuyển dữ liệu là kiểu ký tự, các ký tự khác chữ thì không bị thay đổi.
7.2. Ứng dụng
Các hàm này thường dùng khi chuẩn hoá dữ liệu, hoặc quy chuẩn về 1 dạng để dễ dàng thực hiện các công việc khác. Giả sử dữ liệu về Mã giáo viên trong bảng ở mục 9.2 nhập có lúc viết hoa và đôi khi lại viết thườn, nên cần đổi mã này thành chữ hoa, ta có công thức UPPER(MID(A2,2)).
8. Hàm CONATENATE() và toán tử “&”
8.1. Lý thuyết
− Cú pháp: CONCATENET(Text1, Text2, ...)
Giáo trình TIN VĂN PHÒNG 144
− Hàm CONCATENATE() dùng để nối giá trị các đối số. Có thể dùng dấu cộng xâu (&) thay thế cho hàm đó. Hàm CONCATENAT() có thể nối được 30 xâu ký tự với nhau.
− Các xâu ký tự phải được đặt trong dấu bao xâu (nháy kép “”). Mọi thao tác trên dữ liệu kiểu ký tự, văn bản thì văn bản đều phải được đặt trong dấu bao xâu.
8.2. Ứng dụng
Có thể dùng hàm CONCATENATE() thực hiện thao tác cộng xâu như sau:
=CONCATENATE( “Họ và ”, “tên”) = “Họ và ”& “tên” = “Họ và”& “ ” & “tên”
và kết quả có được là “Họ và tên”
Giả sử ta có dữ liệu như sau:
A B C D
1 Số TT Họ tên Kiểu giáo viên Mã giáo viên
2 1 Lê A M
3 2 Nguyễn B C
Giá trị cột Mã giáo viên được quy định gồm Kiểu giáo viên và Số thứ tự của giáo viên đó. Do đó, giá trị chính là cộng 2 xâu giá trị của 2 cột tương ứng và công thức xây dựng cho ô D2 là =UPPER(A2)&C2
9. Hàm IF() 9.1. Lý thuyết
− Cú pháp: IF(Logical_test, Value_if_true, Value_if_false)
− Hàm IF() sẽ thực hiện biểu thức đúng khi thoả mãn điều kiện của biểu thức logic đã ấn định, và thực hiện biểu thức sai khi không thoả mãn điều kiện.
− Logical_test là thường là biểu thức so sánh có giá trị Đúng/ Sai (True/ False).
Value_if_true là giá trị, biểu thức tính,… mà hàm sẽ thực hiện khi biểu thức logic có giá trị đúng. Value_if_false được thực hiện khi biểu thức logic có giá trị sai.
− Hàm IF() có biểu thức điều kiện (Logical_test) là bắt buộc. Nếu không có 2 đối số còn lại, hàm IF() trả về giá trị của biểu thức logic.
9.2. Ứng dụng
Hàm IF() là một trong những hàm hay sử dụng nhất trong EXCEL. Hàm giúp cho người dùng khả năng lựa chọn phương án trả lời. Với bảng lương ở trên, phần tính Bảo hiểm có tính đến khả năng lựa chọn số năm công tác để tính tiền Bảo hiểm.
Nếu Thâm niên (I4) ≥ 7 thì tính 15% của Lương cơ bản, còn không tính 10% Lương cơ bản. Nên khi xây dựng công thức tính Bảo hiểm (J4) thì biểu thức logic điều khiển tính sẽ liên quan đến cột Thâm niên và biểu thức đó là I4>=7. Biểu thức đúng có nghĩa là nếu người đó thực sự làm việc trên 7 năm thì sẽ tính bảo hiểm 15% Lương cơ bản và công thức tính là 15%*H4. Biểu thức sai có nghĩa là nhân viên đó làm việc dưới 7 năm thì chỉ tính bảo hiểm bằng 10% Lương cơ bản, với công thức tính là 10%*H4.
Giáo trình TIN VĂN PHÒNG 145 Do đó công thức trong ô Bảo hiểm là IF(I4>=7, 15%*H4, 10%*H4). Với các ô khác cùng dạng ví dụ như cột Xếp loại ta cũng làm tương tự.
9.3. Dạng thức hàm IF() lồng nhau
Vẫn dựa trên nguyên tắc của hàm IF() là lựa chọn khả năng tính toán để áp dụng các bài toán phức tạp hơn. Có nghĩa là bài toán đó không chỉ có hai khả năng lựa chọn mà có nhiều khả năng lựa chọn để tính toán hơn.
Với ví dụ tính cột Xếp loại ở trên, ta có các khả năng lựa chọn như sau:
Nếu Ngày công từ 24 ngày trở lên thì Xếp loại A,
Nếu Ngày công từ 18 ngày và nhỏ hơn 24 ngày thì Xếp loại B,
Nếu Ngày công dưới 18 ngày thì Xếp loại C.
Ta thấy ở đây là ba khả năng lựa chọn phương án thực hiện cho ô M4. Giả sử dùng hàm IF() với biểu thức điều kiện là Ngày công >= 24, trường hợp thoả mãn biểu thức logic không có vấn đề bàn cãi, và biểu thức đúng là “A”. Tuy nhiên, sự khó khăn phức tạp lại nằm trong trường hợp không thoả mãn điều kiện. Ta xem hình mô tả dưới đây:
Trên sơ đồ có hai mốc dữ liệu điều kiện đó là 24 ngày và 18 ngày. Khi biểu thức sai bao gồm hai trường hợp dưới 18 ngày và trong khoảng từ 18 đến dưới 24 ngày. Do vậy ta cần sử dụng hàm IF() khác để lựa chọn một trong hai trường hợp này. Ở đây là trường hợp sai của biểu thức Ngày công >= 24 ngày, có nghĩa là các số nhỏ hơn 24 ngày, nên biểu thức điều kiện thứ hai đó là Ngày công >= 18 ngày (có nghĩa là 18 <=
Ngày công < 24). Khi đó biểu thức đúng là “B” còn biểu thức sai là “C”. Ta sử dụng 2 hàm IF() lồng nhau, với IF() lồng trong biểu thức sai của hàm IF() phía ngoài.
Công thức xây dựng được tại ô M4 là IF(L4>=24, “A”, IF(L4>=18, “B”, “C”)) Với dạng bài toán dùng IF() lồng nhau, điều chủ yếu là phải xác định được biểu thức điều kiện với những mốc dữ liệu như thế nào.
Với cột Khen thưởng ý tưởng tính toán cũng tương tự như cột Xếp loại. Tuy nhiên mốc dữ liệu điều kiện là các mức Xếp loại.
10. Hàm AND() và OR() 10.1. Lý thuyết
− Cú pháp: AND(Logical1, Logical2,...)
18 24
Biểu thức điều kiện Ngày công >= 24
BT sai BT đúng
C B A
Giáo trình TIN VĂN PHÒNG 146 OR(Logical1, Logical2,...)
− Hàm AND() trả về giá trị True nếu các biểu thức logic đều có giá trị là True. Hàm OR() trả về giá trị True nếu một trong các biểu thức logic có giá trị là True. Hàm AND() và OR() trả về giá trị False trong các trường hợp còn lại.
− Logical1,… là các biểu thức logic có giá trị True/ False.
− Hàm AND() và OR() không giới hạn số đối số, phải có ít nhất một đối số.
10.2. Ứng dụng
Hàm AND() và OR() thường sử dụng trong biểu thức điều kiện (Logical_test) của hàm IF(). Thông thường các biểu thức điều kiện này phức tạp và liên quan đến nhiều dữ liệu khác. Giả sử như cột Khen thưởng được tính như sau:
Nếu Xếp loại là A hoặc Chức vụ là Giám đốc hoặc là Trưởng phòng thì được thưởng 300,000đ.
Khi đó biểu thức điều kiện bao gồm các yếu tố ( Xếp loại = “A”, Chức vụ = “GĐ”, Chức vụ = “TP” ). Có nghĩa là chỉ Xếp loại A, hoặc chỉ là Giám đốc hoặc chỉ là Trưởng phòng thì được thưởng. Nên biểu thức điều kiện sẽ dùng đến hàm OR() để chỉ cần thoả mãn một trong ba điều kiện logic nêu trên biểu thức đúng sẽ được thực hiện. Ta có công thức IF(OR(N4=“A”, F4 =“GĐ”, F4=“TP”), 300000, 0).
Nếu Xếp loại A và có Chức vụ là Giám đốc hoặc là Trưởng phòng thì được thưởng 300,000đ.
Trong trường hợp này thì khác trường hợp trên ở chỗ nếu Xếp loại A và là Giám đốc, hoặc Xếp loại A và là Trưởng phòng thì được thưởng. Do vậy, phải thoả mãn điều kiện Xếp loại A và chỉ cần thoả mãn một trong 2 khả năng là Giám đốc hoặc Trưởng phòng thì biểu thức đúng mới được thực hiện.
Để thể hiện việc thoả mãn một trong hai Chức vụ trên ta dùng hàm OR() để lựa chọn, sau đó dùng hàm AND() để kết hợp điều kiện Xếp loại với điều kiện Chức vụ này. Nên công thức sẽ là IF(AND(N4=“A”, OR(F4 =“GĐ”, F4=“TP”)), 300000, 0).
11. Hàm SUM() 11.1. Lý thuyết
− Cú pháp: SUM(Number1, Number2,…)
− Hàm SUM() tính tổng giá trị của các số thuộc đối số.
− Number1,… có thể là địa chỉ ô chứa giá trị kiểu số, giá trị số, hay miền địa chỉ ô chứa các giá trị cần tính.
− Hàm SUM() không giới hạn số đối số, nhưng tối thiểu phải là 1 đối số.
11.2. Ứng dụng
Khi lập bảng tính lương, cán bộ tiền lương có thể muốn biết tổng tiền lương cơ bản phải trả trong toàn công ty, hay tính tổng tiền bảo hiểm phải nộp cho nhà thuế... Khi đó
Giáo trình TIN VĂN PHÒNG 147 dùng hàm SUM() sẽ cho kết quả theo yêu cầu của mình. Đây là hàm thông dụng nhất trong bảng tính.
Có thể dùng phím Σ trên thanh Format Standard để lấy hàm AutoSum, sau đó chọn vùng dữ liệu cần tính.
Nhận xét: Việc dùng hàm SUM() hạn chế việc lập công thức thủ công, vì SUM(A1:A5) ≈ SUM(A1,A2,A3,A4,A5) ⇔ A1+A2+A3+A4+A5. Do vậy, với dãy các đối số quá dài nên dùng Autosum.
12. Hàm INT() 12.1. Lý thuyết
− Cú pháp: INT(Number)
− Hàm dùng để làm tròn một số kiểu thập phân thành số nguyên cận dưới của số đó (làm tròn xuống).
Ví dụ: INT(3.15) = 3, INT(3.65) = 3
− Number là giá trị kiểu số hoặc tham chiếu ô có chứa số cần làm tròn.
12.2. Ứng dụng
Việc tính Thâm niên = Ngày hiện tại - Ngày bắt đầu chỉ cho kết quả là khoảng thời gian nếu định dạng theo kiểu Date/Time hoặc tổng số ngày trong khoảng thời gian đó nếu định dạng theo kiểu General. Do vậy, số năm công tác của một cán bộ (Thâm niên công tác) được tính bằng Tổng số ngày/Số ngày trong 1 năm (ở đây quy định số ngày là 365). Việc sử dụng phép chia sẽ cho kết quả là số thập phân, nên dùng hàm INT() để làm tròn giá trị đó xuống giá trị nguyên gần nhất. Đây chính là số năm làm việc của cán bộ đó, ta có công thức xây dựng trong ô G4 như sau: INT((D15-D4)/365).
13. Hàm ROUND(), ROUNDDOWN(), ROUNDUP() 13.1. Lý thuyết
− Cú pháp: ROUND(Number, Num_digits)
ROUNDDOWN(Number, Num_digits) ROUNDUP(Number, Num_digits)
− Hàm ROUND() dùng để làm tròn một số đến chính xác bao nhiêu chữ số đằng sau/
trước dấu chấm thập phân. Quy tắc làm tròn tương tự quy tắc trong toán học, có nghĩa nếu số từ 5 trở lên thì làm tròn lên, nếu số dưới 5 thì làm tròn xuống.
− Hàm ROUNDDOWN(), ROUNDUP() dùng để làm tròn xuống/ lên đến chính xác bao nhiêu số sau/ trước dấu thập phân.
− Number là giá trị kiểu số hoặc tham chiếu ô có chứa số cần làm tròn. Num_digits xác định số chữ số đằng sau/ trước dấu thập phân. Nếu giá trị dương là làm tròn đến các số sau, giá trị âm là làm tròn đến các số trước dấu thập phân, nếu là 0 là làm tròn đến hàng đơn vị.
13.2. Ứng dụng
Giáo trình TIN VĂN PHÒNG 148 Giả sử làm tròn kết quả phép tính 3.47*0.234 đến 3 chữ số sau dấu thập phân, khi đó công thức là ROUND(3.47*0.234, 0). Một ví dụ khác, ô A5 chứa giá trị 23456, cần làm tròn giá trị đến hàng trăm, và công thức là ROUND(A5,-2) = 23500.
14. Hàm SUMIF() 14.1. Lý thuyết
− Cú pháp: SUMIF(Range, Criteria, Sum_range)
− Hàm trả về tổng giá trị của các ô thuộc đối số thứ 3 hoặc thứ 1 mà giá trị ở ô tương ứng trong đối số thứ 1 thoả mãn điều kiện/ tiêu chuẩn đặt ra ở đối số thứ 2.
− Range là dãy các giá trị cần kiểm tra, đối số này là tham chiếu ô chứa dãy giá trị.
Criteria là điều kiện kiểm tra, đối số thể là địa chỉ ô chứa điều kiện hoặc là điều kiện được đặt trong nháy kép “”. Sum_range là đối số tùy chọn, nó chỉ ra dãy các giá trị (hoặc tham chiếu ô chứa giá trị) sẽ tính tổng nếu thỏa mãn điều kiện. Nếu không có đối số Sum_range, hàm sẽ tính tổng các giá trị mà thoả mãn điều kiện ở đối số Range.
14.2. Ứng dụng
Cho bảng dữ liệu như sau
A B C D
1 Chức vụ Hệ số Lương cơ bản TP
2 GĐ 2450000
3 TP 1970000
4 CB 1600000
5 CB 1700000
6 TP 2100000
Tính tổng tiền lương cơ bản trả cho các Trưởng phòng (TP). Có nghĩa là điều kiện duyệt được thực hiện trên cột Chức vụ nếu dòng nào là TP thì cộng giá trị tương ứng tại cột Lương cơ bản. Tại ô D2, ta có công thức SUMIF(A2:A6, “TP”, C2:C6). Nếu điều kiện trong ô D1, thì công thức là SUMIF(A2:A6, D1, C2:C6).
Hàm SUMIF() làm việc như sau: duyệt lần lượt các giá trị trong miền A2:A6, rồi so sánh với điều kiện. Nếu giá trị ở vị trí nào thỏa mãn điều kiện thì sẽ lấy giá trị ở vị trí tương ứng trong miền C2:C6 để tính tổng.
15. Hàm AVERAGE() và COUNT() 15.1. Lý thuyết
− Cú pháp: AVERAGE(Number1, Number2,…) COUNT(Number1, Number2,…)
− Hàm AVERAGE() tính giá trị trung bình cộng của các đối số và hàm COUNT() đếm các ô chứa trị số trong dãy đối số.
− Số lượng và kiểu đối số tương tự hàm SUM() ở trên.
15.2. Ứng dụng