1- Hàm tính tổng SUM
- Chức năng: Tính tổng cộng toàn bộ giá trị trong các ô được chọn.
- Cú pháp: =SUM([ô thứ 1], [ô thứ 2],…)
- Ví dụ như trong bên dưới. Chúng ta có thể cộng các ô theo hàng, theo cột, theo vùng, hoặc cộng các ô, vùng lộn xộn theo nhu cầu mình mong muốn.
2- Hàm tính giá trị trung bình AVERAGE
- Chức năng: Tính giá trị trung bình cộng của toàn bộ các ô được chọn.
- Cú pháp: =AVERAGE([ô thứ 1], [ô thứ 2],…)
- Cú pháp hàm AVERAGE tương tự như hàm SUM, nhưng kết quả của hàm AVERAGE cho ra giá trị trung bình cộng, còn hàm SUM cho ra giá trị tổng cộng.
- Ví dụ như trên hàm SUM, nếu gõ công thức =AVERAGE(B2,B6) sẽ có kết quả là 45.6
3- Hàm tìm giá trị lớn nhất MAX
- Chức năng: tìm giá trị lớn nhất trong toàn bộ các ô được chọn.
- Cú pháp: =MAX([ô thứ 1], [ô thứ 2],…)
- Cú pháp hàm MAX tương tự như hàm SUM, AVERAGE nhưng kết quả của hàm MAX cho ra giá trị lớn nhất trong toàn bộ các ô được chọn.
- Ví dụ như hình trên, nếu gõ công thức =MAX(B2,B11) sẽ có kết quả là 79.
4- Hàm tìm giá trị nhỏ nhất MIN
- Chức năng: Tìm giá trị lớn nhất trong toàn bộ các ô được chọn.
- Cú pháp: =MIN([ô thứ 1], [ô thứ 2],…)
- Cú pháp hàm MIN tương tự như hàm SUM, AVERAGE, MAX nhưng kết quả của hàm MIN cho ra giá trị nhỏ nhất trong toàn bộ các ô được chọn.
- Ví dụ như hình trên, nếu gõ công thức =MIN(B2,B11) sẽ có kết quả là 79.
5- Hàm logic IF, AND, OR
Hàm IF
- Chức năng: Đưa ra 1 hoặc nhiều điều kiện đối với 1 hoặc nhiều ô. Phần mềm sẽ cho ra 1 trong 2 kết quả mà bạn đã đặt sẵn tương ứng với 2 trường hợp ĐÁP ỨNG ĐÚNG hoặc KHÔNG ĐÁP ỨNG ĐÚNG được điều kiện đã đưa ra.
- Cú pháp: =IF([điều kiện], [kết quả nếu đáp ứng đúng điều kiện], [kết quả nếu không đáp ứng được điều kiện])
- Ví dụ như hình. Giáo viên muốn xếp loại học sinh. Điều kiện đưa ra là điểm trung bình thấp hơn 5 điểm. Nếu điểm trung bình dưới 5 điểm (đáp ứng đúng điều kiện), kết quả sẽ cho ra xếp loại YẾU, trường hợp ngược lại (không đáp ứng đúng điều kiện) sẽ cho ra kết quả là KHÔNG YẾU.
Hàm OR
Chức năng: kết hợp với hàm IF trong trường hợp cần đưa ra từ hai điều kiện trở lên và chỉ cần đáp ứng được ít nhất 1 trong các điều kiện đưa ra thì sẽ cho ra [kết quả nếu đáp ứng đúng điều kiện]
- Cú pháp: =IF(OR([điều kiện 1], [điều kiện 2],...),[kết quả nếu đáp ứng đúng 1 trong các điều kiện], [kết quả nếu không đáp ứng được bất kỳ điều kiện nào])
- Ví dụ như hình. Hai điều kiện đưa ra là điểm trung bình phải lớn hơn 9, hoặc phải bằng 9. Nếu đáp ứng đúng 1 trong 2 điều kiện, kết quả sẽ cho ra xếp loại XUẤT SẮC, trường hợp không đáp ứng đúng cả 2 điều kiện sẽ cho ra kết quả là KHÔNG XUẤT SẮC.
Hàm AND
- Chức năng: kết hợp với hàm IF trong trường hợp cần đưa ra từ hai điều kiện trở lên, và phải đáp ứng được toàn bộ các điều kiện thì mới cho ra [kết quả nếu đáp ứng đúng điều kiện]
- Cú pháp: =IF(AND([điều kiện 1], [điều kiện 2],...),[kết quả nếu đáp ứng đúng toàn bộ các điều kiện], [kết quả nếu không đáp ứng được 1 điều kiện bất kỳ])
- Ví dụ như hình.Hai điều kiện đưa ra là (i) điểm trung bình phải lớn hơn hoặc bằng 8 và (ii) điểm trung bình nhỏ hơn 9. Nếu đáp ứng đúng điều kiện, kết quả sẽ cho ra xếp loại GIỎI, trường hợp không đáp ứng đúng điều kiện sẽ cho ra kết quả là LOẠI KHÁC. Chúng ta sẽ kết hợp hàm OR, AND và hàm IF như sau:
= IF(AND(OR(B3>8,B3=8),B3<9), “GIỎI”, “LOẠI KHÁC”)
Kết hợp hàm IF, AND, OR để xếp loại học sinh theo điểm trung bình
- Điều kiện đưa ra như sau:
(1) Điểm trung bình ³ 9 xếp loại XUẤT SẮC
(2) Điểm trung bình ³ 8 và < 9 xếp loại GIỎI
(3) Điểm trung bình ³ 6.5 và < 8 xếp loại KHÁ
(4) Điểm trung bình ³ 5 và < 6.5 xếp loại TRUNG BÌNH
(5) Điểm trung bình < 5 xếp loại YẾU
Chúng ta sẽ bắt đầu từ điều kiện đầu tiên. Nếu đáp ứng được điều kiện 1 thì sẽ trả về kết quả XUẤT SẮC, nếu không đáp ứng sẽ cho ra loại khác. Công thức như sau:
= IF(OR(B3>9,B3=9),"XUẤT SẮC","LOẠI KHÁC")
Tiếp theo chúng ta thay thế kết quả “LOẠI KHÁC” bằng 1 hàm IF khác như sau
IF(OR(B3>8,B3=8),"GIỎI","LOẠI KHÁC")
Vì ở hàm IF đầu tiên chúng ta đã loại hết các học sinh XUẤT SẮC, nên tại hàm IF thứ 2 này, chúng ta chỉ còn lại các học sinh có điểm số nhỏ hơn 9. Vì vậy điều kiện OR(B3>8,B3=8) chỉ có hiệu lực đối với các điểm số từ 8 đến < 9.
Ta tiếp tục thay thế LOẠI KHÁC bằng các hàm IF đến khi nào đến điều kiện thứ 4. Nếu đạt điều kiện 4 thì xếp loại TRUNG BÌNH, nếu không thì sẽ xếp loại duy nhất còn lại YẾU. Hàm điều kiện của chúng ta sẽ như sau:
=IF(OR(B3>9,B3=9),"XUẤT SẮC", IF(OR(B3>8,B3=8),"GIỎI", IF(OR(B3>6.5,B3=6.5),"KHÁ", IF(OR(B3>5,B3=5),"TRUNG BÌNH","YẾU"))))
6- Hàm ngày tháng năm TODAY, DAY, MONTH, YEAR, DATE
Hàm TODAY
- Chức năng: hiển thị kết quả ngày hôm nay.
- Cú pháp: =TODAY()
- Ví dụ nếu hôm nay là ngày 01 tháng 12 năm 2018 thì hàm =TODAY() sẽ cho ra kết quả là 01/12/2018
Hàm DAY
- Chức năng: Hiển thị kết quả là ngày trong dữ liệu đầu vào có định dạng ngày tháng năm
- Cú pháp: =DAY([ô dữ liệu ngày tháng năm])
- Ví dụ như trong hình dưới. Ô dữ liệu đầu vào B3 là 01/12/1985. Hàm =DAY(B2) sẽ cho ra kết quả là 1.
Hàm MONTH
- Chức năng: Hiển thị kết quả là tháng trong dữ liệu đầu vào có định dạng ngày tháng năm
- Cú pháp: =MONTH([ô dữ liệu ngày tháng năm])
Hàm YEAR
- Chức năng: Hiển thị kết quả là năm trong dữ liệu đầu vào có định dạng ngày tháng năm
- Cú pháp: =YEAR([ô dữ liệu ngày tháng năm])
Hàm DATE
- Chức năng: cho ra một ô dữ liệu theo định dạng năm tháng ngày từ 3 ô chứa dữ liệu ngày, tháng, năm khác nhau.
- Cú pháp: =DATE([ô dữ liệu năm], [ô dữ liệu tháng], [ô dữ liệu ngày])
- Ví dụ: tại ô F2 ta dùng hàm: =DATE(E2,D2,C2) ta sẽ được kết quả là 01/12/1985
7- Hàm LEFT, RIGHT, MID
- Chức năng: tách các ký tự cần lấy từ ô dữ liệu đầu vào là một chuỗi ký tự, trong đó các ký tự cần lấy nằm bên tay trái, hoặc bên phải, hoặc nằm giữ chuỗi ký tự.
- Cú pháp:
=LEFT([ô chứa chuỗi ký tự],[số lượng ký tự cần lấy tính từ bên tay trái])
=RIGHT([ô chứa chuỗi ký tự],[số lượng ký tự cần lấy tính từ bên tay phải])
=MID([ô chứa chuỗi ký tự],[vị trí bắt đầu của chuỗi ký tự cần tách ra],[số lượng ký tự cần lấy tính từ vị trí bắt đầu])
- Ví dụ như hình.
Ô B2 là chuỗi các ký tự được xuất ra từ hệ thống cho biết ngày giải ngân, ngày đáo hạn, lãi suất và đồng tiền cho vay của từng khế ước nhận nợ tại ngân hàng. Chúng ta cần lấy thông tin là ngày giải ngân thì sẽ áp dụng công thức như sau:
= LEFT(B2,10)
Kết quả sẽ là 24/12/2018 (Lấy từ bên phải qua 10 ký tự)
Nếu thông tin cần lấy là đồng tiền giải ngân thì áp dụng công thức như sau:
= RIGHT(B3,3)
Kết quả sẽ là VNĐ
Nếu thông tin cần lấy là ngày đáo hạn khế ước thì công thức áp dụng như sau:
= MID(B4,14,10)
Kết quả sẽ là 07/04/2019
Lưu ý dấu cách (khoảng trống) vẫn được tính là 1 ký tự.8- Hàm VLOOKUP, HLOOKUP
Hàm VLOOKUP
- Chức năng: Tra cứu theo từng
hàng trong vùng dữ liệu, nếu có dữ liệu trùng với điều kiện cần tìm kiếm thì sẽ
trả về giá trị tại cột ta mong muốn lấy thông tin tương ứng.
- Cú pháp: = VLOOKUP([ô giá trị
cần tìm kiếm], [vùng dữ liệu cần tìm kiếm], [cột giá trị cần trả về khi tìm
thấy ô giá trị khớp với ô giá trị cần tìm kiếm], [Kiểu đối chiếu dữ liệu])
* Kiểu đối chiếu dữ liệu có 2 dạng là TRUE là tương đối gần giống với ô
giá trị cần tìm kiếm và FALSE là giống hoàn toàn với ô giá trị cần tìm kiếm.
Thông thường, để có độ chính xác tuyệt đối, chúng ta hay chọn dạng FALSE để tìm
kiếm các giá trị trùng khớp hoàn toàn.
- Ví dụ: Bạn có một bản dữ liệu đầu vào là thông tin nhân viên. Bạn đang cần
xuất thông tin của 1 số nhân viên trong bảng dữ liệu đó. Thông tin bạn có là mã
nhân viên của những nhân viên cần xuất ra.
Hàm VLOOKUP sẽ hỗ trợ chúng ta lấy
các dữ liệu cần thiết theo từng mã nhân viên.
Tại ô B11 ta đặt công thức như sau:
=VLOOKUP(A11,A3:D7,2,FALSE)
Excel sẽ tìm kiếm từng dòng trong bảng dữ liệu, nếu dòng nào có ô giá trị
trùng với mã nhân viên tại ô A11, Excel sẽ trả về giá trị ở cột thứ 2 của dòng
đó. Cột thứ 2 chính là cột tên nhân viên.
Để có thể kéo công thức xuống các dòng bên dưới mà không thay đổi vùng
tìm kiếm, ta sẽ cố định vùng dữ liệu cần tìm kiếm bằng cách thêm ký tự $ như
sau:
=VLOOKUP(A11,$A$3:$D$7,2,FALSE)
Tương tự đối với cột Đơn vị công tác và Chức danh, ta sẽ thay đổi cột giá
trị trả về của công thức. Đơn vị công tác sẽ là cột 3 và Chức danh sẽ là cột 4.
Ta sẽ thấy được hiệu quả của hàm VLOOKUP khi bảng dữ liệu đầu vào có hàng
trăm nhân viên, trong khi chúng ta chỉ cần thông tin của vài chục nhân viên.
Hàm HLOOKUP
- Chức năng: Tra cứu theo từng cột
trong vùng dữ liệu, nếu có dữ liệu trùng với điều kiện cần tìm kiếm thì sẽ trả
về giá trị tại dòng ta mong muốn lấy thông tin tương ứng.
- Cú pháp: = HLOOKUP([ô giá trị
cần tìm kiếm], [vùng dữ liệu cần tìm kiếm], [dòng giá trị cần trả về khi tìm
thấy ô giá trị khớp với ô giá trị cần tìm kiếm], [Kiểu đối chiếu dữ liệu])
HLOOKUP có chức năng tương tự như VLOOKUP, chỉ khác là HLOOKUP tìm theo
từng cột và trả về giá trị theo dòng.
Ví dụ ta cần xuất dữ liệu bán hàng các tháng của Nguyễn Văn B từ bản số
liệu đầu vào. Chúng ta sẽ áp dụng HLOOKUP như sau:
B9 = HLOOKUP(A9,B2:D5,2,FALSE)
Tương tự cho tháng 2 và tháng 3, ta chỉ thay đổi dòng dữ liệu trả về
thành 3 và 4
9- Hàm TRIM
- Chức năng: Xóa các ký tự trống
dư thừa trong ô excel.
- Cú pháp: =TRIM([ô dữ liệu cần
xóa ký tự trống])
- Ví dụ như hình. Ta có bảng danh sách tên bị lỗi ký tự thừa. Ta sẽ áp
dụng hàm TRIM để xóa chúng đi và nhận được danh sách tên hoàn hảo như sau:
B1=TRIM(A1)
10- Hàm INT
- Chức năng: lấy giá trị số nguyên của ô dữ liệu đầu vào.
- Cú pháp: =INT ([ô dữ liệu chứa
số])
- Ví dụ như hình: B1 = INT(A1) sẽ có kết quả là 12. Hàm INT sẽ loại bỏ
toàn bộ phần sau dấu chấm (.) và giữ lại phần số nguyên.
11- Hàm ROUND
- Chức năng: Làm tròn số.
- Cú pháp: = ROUND ([ô dữ liệu chứa
số], [số thập phân cần làm tròn đến])
- Ví dụ như hình trên.
C1 = ROUND(A1,0) sẽ cho kết quả là 13
D1 = ROUND(A1,0) sẽ
cho kết quả là 12.864Chúc các bạn thành công!
Không có nhận xét nào:
Đăng nhận xét