Excel là phần mềm đóng vai trò quan trọng trong công việc văn phòng. Chúng ta có thể tạo và quản lý dữ liệu trên các bảng tính, hay tính toán và phân tích dữ liệu bằng các hàm.
Nếu bạn là người thường xuyên dùng Excel để xử lý công việc nhưng chưa biết đến các hàm Excel nâng cao cho người đi làm thì bài viết này dành cho bạn. Hãy cùng tìm hiểu nhé!
{index}
Các hàm xử lý ngày tháng nâng cao
Khi bạn cần thao tác và tính toán dữ liệu về thời gian và ngày tháng, hãy tham khảo một số hàm ngày tháng nâng cao và cách sử dụng chúng dưới đây:
DATEDIF(start_date, end_date, "unit"): Dùng để tính số ngày, tháng hoặc năm giữa hai ngày cụ thể.
Ví dụ: Nếu bạn muốn biết khoảng cách thời gian từ ngày 01/01/2023 đến ngày 31/12/2025 là bao nhiêu tháng, bạn có thể sử dụng hàm:
=DATEDIF(A1, B1, "m")Trong đó: A1 là ngày bắt đầu, B1 là ngày kết thúc, m là kết quả trả về số tháng.
EDATE(start_date, months): Dùng để trả về ngày sau một số tháng từ ngày bắt đầu.
Ví dụ: Nếu bạn muốn biết ngày sau 6 tháng từ ngày 04/10/2023, bạn có thể sử dụng hàm sau:
=EDATE(A2, 6)Trong đó: A2 là ngày bắt đầu. Và bạn cần định dạng thời gian cho ô kết quả (ô D2)
EOMONTH(start_date, months): là một trong các hàm Excel nâng cao để trả về ngày cuối cùng của tháng sau một số tháng từ ngày bắt đầu.
Ví dụ: Nếu bạn muốn biết ngày cuối cùng trong tháng của 6 tháng sau từ ngày 01/01/2023, bạn có thể sử dụng hàm:
=EOMONTH(A3, 6)Trong đó: A3 là ngày bắt đầu và bạn cần định dạng ô trả về kết quả (D3)
Kết quả khi sử dụng 3 hàm trên như sau:
Các hàm xử lý chuỗi
Để thực hiện nhanh các thao tác trích xuất và biến đổi các chuỗi ký tự, xem ngay một số hàm xử lý chuỗi cơ bản đến nâng cao mà chúng tôi liệt kê dưới đây:
LEFT(text, num_chars): dùng khi muốn trích xuất một số ký tự từ phía bên trái của một chuỗi.
RIGHT(text, num_chars): để trích xuất một số ký tự từ phía bên phải của một chuỗi.
MID(text, start_num, num_chars): dùng để trích xuất một phần của chuỗi, bắt đầu từ vị trí đã cho và với số lượng ký tự xác định.
LEN(text): Trả về số ký tự trong một chuỗi.
Ví dụ: Giả sử chúng ta có chuỗi “mìnhB2C2023“. Lúc này, bạn muốn trích xuất thông tin từ chuỗi này:
Sử dụng hàm LEFT để lấy 6 ký tự từ bên trái:
=LEFT(A1, 6)Sử dụng hàm RIGHT để lấy 4 ký tự từ bên phải:
=RIGHT(A1, 4)Sử dụng hàm MID để lấy 3 ký tự từ vị trí thứ 7:
=MID(A1, 7, 3)
Các hàm trích xuất kí tự nâng caoLOWER(text): Chuyển đổi các ký tự trong chuỗi thành chữ thường.
UPPER(text): Chuyển đổi các ký tự trong chuỗi thành chữ hoa.
PROPER(text): Chuyển đổi chuỗi thành dạng chữ hoa đầu tiên của từng từ.
Ví dụ: Giả sử chúng ta có chuỗi “hello mình“. Chúng ta muốn thực hiện các thao tác sau:
Chuyển đổi các ký tự thành chữ thường bằng hàm LOWER:
=LOWER(A7)Chuyển đổi các ký tự thành chữ hoa với hàm UPPER:
=UPPER(A7)Chuyển đổi chuỗi thành dạng chữ hoa đầu tiên của từng từ bằng hàm PROPER:
=PROPER(A7)
TRIM(text): Loại bỏ các khoảng trắng không cần thiết từ chuỗi.
=TRIM(" Hello ")CONCATENATE(text1, text2, ...): Ghép nối các chuỗi lại với nhau.
Ví dụ:
=CONCATENATE(A17, " ",A18)FIND(find_text, within_text, [start_num]): Tìm vị trí của chuỗi con trong chuỗi khác.
Ví dụ:
=FIND("l", A17)REPLACE(old_text, start_num, num_chars, new_text): Thay thế một phần của chuỗi bằng chuỗi khác.
Ví dụ:
=REPLACE(A20, 7, 5, "Universe")SUBSTITUTE(text, old_text, new_text, [instance_num]): Thay thế các chuỗi con trong chuỗi khác.
Ví dụ:
=SUBSTITUTE(A21, "H", "J", 2)TEXT(value, format_text): Định dạng một giá trị dưới dạng chuỗi, sử dụng định dạng được chỉ định.
Ví dụ: Định dạng chuỗi số thành tiền tệ:
=TEXT(A22, "$#,##0.00")Kết quả khi sử dụng các hàm này như hình dưới:
Các hàm xử lý chuỗi nâng caoCác hàm Excel nâng cao tính toán số học
Ưu điểm của Excel so với các phần mềm tin học văn phòng khác là khả năng thực hiện các phép toán phức tạp và tính toán số học bằng hàm. Bạn có thể tham khảo một số hàm tính toán bên nâng cao bên dưới:
SUMIF(range, criteria, [sum_range]): Tính tổng các ô trong một phạm vi nếu các điều kiện được thỏa mãn.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...): Tính tổng các ô trong một phạm vi dựa trên nhiều điều kiện.
SUMPRODUCT(array1, [array2, ...]): Nhân các phần tử tương ứng của các mảng với nhau và trả về tổng của các kết quả.
Ví dụ: Giả sử tôi có các dữ liệu sau đây:
Hãy tính tổng của các giá trị trong phạm vi B1:B5 nếu giá trị tương ứng trong A1:A5 lớn hơn 1, và giá trị trong B1:B5 lớn hơn 3.
=SUMIF(A1:A5, ">1", B1:B5) + SUMIFS(B1:B5, A1:A5, ">1", B1:B5, ">3")Trong đó:
SUMIF(A1:A5, ">1", B1:B5): Sẽ tính tổng các giá trị trong phạm vi B1:B5 nếu giá trị tương ứng trong A1:A5 lớn hơn 1.SUMIFS(B1:B5, A1:A5, ">1", B1:B5, ">3"): Sẽ tính tổng các giá trị trong phạm vi B1:B5 nếu giá trị tương ứng trong A1:A5 lớn hơn 1 và giá trị trong B1:B5 lớn hơn 3.- Cộng kết quả của hai hàm lại với nhau.
Một yêu cầu khác, tính tổng của tích các phần tử tương ứng của A1:A5 và B1:B5, nếu giá trị tương ứng trong A1:A5 lớn hơn 1 và giá trị tương ứng trong B1:B5 lớn hơn 3.
Ta dùng công thức:
=SUMPRODUCT((A1:A5>1)*(B1:B5>3), C1:C5)Và thu được kết quả là:
Các hàm xử lý số học nâng caoABS(number): Giá trị tuyệt đối của số.
Công thức:
=ABS(-5)Kết quả: Giá trị tuyệt đối của -5 là 5.
ROUND(number, num_digits): Làm tròn số với số chữ số thập phân xác định.
Công thức:
=ROUND(3.5678, 2)Kết quả: Số 3.5678 sẽ được làm tròn thành 3.57.
MOD(number, divisor): Trả về phần dư của một phép chia.
Ví dụ:
=MOD(10, 3)Kết quả: Khi chia 10 cho 3, ta được phần nguyên là 3 và phần dư là 1.
RAND(): Trả về một số ngẫu nhiên từ 0 đến 1.
=RAND()Kết quả: Excel sẽ trả về một số ngẫu nhiên khác nhau trong phạm vi từ 0 đến 1.
RANDBETWEEN(bottom, top): Trả về một số ngẫu nhiên trong phạm vi từ nhỏ đến lớn.
=RANDBETWEEN(10, 20)Kết quả: Excel sẽ trả về một số ngẫu nhiên khác nhau trong phạm vi từ 10 đến 20.
INT(number): Trả về phần nguyên của một số.
=INT(5.8)Kết quả: Phần nguyên của 5.8 là 5.
PRODUCT(number1, number2, ...): Trả về tích của các số.
=PRODUCT(2, 3, 4)Kết quả: Tích của 2, 3, và 4 là 24.
Bạn có thể xem bảng kết quả minh họa sau:
ROUNDUP(number, num_digits): Làm tròn một số lên đến một số chữ số cụ thể.
Ví dụ:
=ROUNDUP(3.14159, 2)ROUNDDOWN(number, num_digits): Làm tròn một số xuống đến một số chữ số cụ thể.
Ví dụ:
=ROUNDDOWN(3.14159, 2)MROUND(number, multiple): Làm tròn số tới số gần nhất là bội số của một số khác.
=MROUND(15, 5)Kết quả: 15 (vì 15 là bội số của 5)
FLOOR(number, significance): hàm sẽ làm tròn số xuống đến bội số gần nhất của một số khác.
=FLOOR(26, 5)Kết quả: 25 (vì 25 là bội số của 5)
CEILING(number, significance): hàm dùng để làm tròn số lên đến bội số gần nhất của một số khác.
=CEILING(17, 5)Kết quả: 20 (vì 20 là bội số của 5)
TRUNC(number, [num_digits]): dùng để cắt bỏ phần thập phân của số.
=TRUNC(3.14159, 2)Kết quả: 3.14
EVEN(number): hàm làm tròn số lên đến số chẵn gần nhất.
=EVEN(7)Kết quả: 8
ODD(number): Hàm tròn số lên đến số lẻ gần nhất.
=ODD(6)Kết quả: 7
Bạn có thể xem hình minh họa dưới đây:
Các hàm làm tròn số học trong ExcelCác hàm dò tìm nâng cao trong Excel
Khi làm việc với dữ liệu lớn, việc tìm kiếm và trích xuất thông tin thủ công sẽ tốn rất nhiều thời gian của bạn. Chính vì điều đó, bạn cần có các hàm dò tìm để hỗ trợ bạn dò thông tin theo điều kiện và tiêu chí cụ thể và nhanh chóng:
Ví dụ: Ta có bảng dữ liệu sau:
Hãy sử dụng các hàm dò tìm để xử lý dữ liệu:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]): Tìm kiếm một giá trị trong một phạm vi và trả về giá trị tương ứng từ cột được chỉ định.
Yêu cầu: Tìm giá trị tương ứng với 90 trong cột A và trả về giá trị tương ứng ở cột B.
=VLOOKUP(90, A1:D3, 2, FALSE)Kết quả: Kết quả sẽ là 100.
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]): Tìm kiếm một giá trị trong một phạm vi và trả về giá trị tương ứng từ hàng được chỉ định.
Yêu cầu: Tìm giá trị tương ứng với 20 trong hàng 1 và trả về giá trị tương ứng ở hàng 2.
=HLOOKUP(20, A1:D3, 2, FALSE)Kết quả: Kết quả sẽ là 60.
MATCH(lookup_value, lookup_array, [match_type]): Tìm kiếm giá trị trong một mảng và trả về vị trí của giá trị đó.
Yêu cầu: Tìm vị trí của giá trị 50 trong cột A.
=MATCH(50, A1:A3, 0)Kết quả: Vị trí của giá trị 50 trong cột A là 2 (nằm ở hàng thứ 2).
INDEX(array, row_num, [column_num]): Truy xuất giá trị từ một phạm vi dựa trên số hàng và số cột xác định.
Yêu cầu: Truy xuất giá trị từ phạm vi A1:D3 dựa trên hàng thứ 2 và cột thứ 3.
=INDEX(A1:D3, 2, 3)Kết quả: Giá trị tại hàng 2, cột 3 là 70.
Các hàm dò tìm nâng cao trong ExcelCHOOSE(index_num, value1, [value2], ...): Chọn giá trị từ một danh sách các giá trị dựa trên chỉ số.
Yêu cầu: Chọn giá trị từ danh sách [100, 200, 300] dựa trên chỉ số 2.
=CHOOSE(2, 100, 200, 300)Kết quả: Chọn giá trị thứ 2 từ danh sách là 200.
SEARCH(find_text, within_text, [start_num]): Tìm kiếm một chuỗi con trong chuỗi khác và trả về vị trí của chuỗi con đó.
Yêu cầu: Tìm vị trí của chuỗi con “20” trong chuỗi “10 20 30“.
=SEARCH("20", "10 20 30")Kết quả: Vị trí của chuỗi con “20” trong chuỗi “10 20 30” là 4.
Các hàm Excel nâng cao để thống kê
Giả sử bạn có danh sách điểm số của sinh viên như sau:
Yêu cầu hãy tính:
- Tính trung bình cộng các điểm trong cột B.
- Tính trung bình cộng các điểm trong cột C chỉ khi điểm tương ứng trong cột A lớn hơn hoặc bằng 7.
- Tìm điểm cao nhất trong danh sách.
- Tìm điểm thấp nhất trong danh sách.
- Đếm số lượng điểm trong cột D.
- Đếm số lượng ô không rỗng trong cột C.
- Đếm số lượng điểm trong cột A lớn hơn hoặc bằng 7.
- Xếp hạng điểm 9 trong danh sách.
AVERAGE(number1, number2, ...): Trung bình cộng của các số.
=AVERAGE(D2:D4)Kết quả: Trung bình cộng các điểm trong cột B là 7.
AVERAGEIF(range, criteria, [average_range]): Tính trung bình cộng của các ô trong một phạm vi chỉ khi điều kiện được thỏa mãn.
=AVERAGEIF(B2:B4, ">=7", C2:C4)Kết quả: Trung bình cộng các điểm trong cột C chỉ khi điểm tương ứng trong cột A lớn hơn hoặc bằng 7 là 8.
MAX(number1, number2, ...): Giá trị lớn nhất trong danh sách.
=MAX(B2:B4)Kết quả: Điểm cao nhất trong danh sách là 9.
MIN(number1, number2, ...): Giá trị nhỏ nhất trong danh sách.
=MIN(B2:B4)Kết quả: Điểm thấp nhất trong danh sách là 7.
COUNT(number1, number2, ...): Đếm số lượng giá trị trong danh sách.
=COUNT(D2:D4)Kết quả: Có 3 điểm trong cột D.
COUNTA(value1, value2, ...): Đếm số lượng giá trị không rỗng trong danh sách.
=COUNTA(C2:C4)Kết quả: Có 3 ô không rỗng trong cột C.
COUNTIF(range, criteria): Đếm số lượng ô trong một phạm vi thỏa mãn một điều kiện cụ thể.
=COUNTIF(D2:D4, ">=7")Kết quả: Có 2 điểm trong cột D lớn hơn hoặc bằng 7.
RANK(number, ref, [order]): Xếp hạng của một số trong một danh sách.
=RANK(9, B2:B4)Kết quả: Xếp hạng điểm 9 trong danh sách là 1.
Bạn có thể xem hình minh họa bên dưới:
Các hàm thống kê nâng cao trong ExcelHàm Excel luận lý
Các hàm Excel này còn được gọi là hàm logic giúp bạn phân tích các dữ liệu dựa trên các điều kiện logic.
Giả sử bạn có bảng điểm của bạn học sinh như sau:
Hãy tính:
- Kiểm tra nếu điểm Sinh lớn hơn 8 và điểm Toán lớn hơn hoặc bằng 7, trả về “Học sinh giỏi”, ngược lại trả về “Học sinh khá”.
- Kiểm tra nếu có ít nhất một môn nào đó đạt điểm 9 trở lên, trả về “Học sinh xuất sắc”, ngược lại trả về “Học sinh giỏi”.
- Đảo ngược kết quả của điều kiện: nếu điểm Lý nhỏ hơn 7, trả về “Cần cố gắng hơn”, ngược lại trả về “Tiếp tục giữ vững”.
Ta dùng các hàm sau:
IF(logical_test, value_if_true, value_if_false): Thực hiện một kiểm tra logic và trả về giá trị dựa trên kết quả kiểm tra.
AND(logical1, [logical2], ...): Kiểm tra nhiều điều kiện và trả về TRUE nếu tất cả điều kiện đều đúng.
OR(logical1, [logical2], ...): Kiểm tra nhiều điều kiện và trả về TRUE nếu ít nhất một điều kiện đúng.
NOT(logical): Đảo ngược kết quả của một kiểm tra logic.
Để giải bài toán, chúng ta kết hợp các hàm trên thành công thức sau:
1. Hàm IF và AND
=IF(AND(A2>8, B2>=7), "Học sinh giỏi", "Học sinh khá")Kết quả: Kết quả sẽ là “Học sinh khá” (vì điểm Sinh không lớn hơn 8).
2. Hàm IF và OR
=IF(OR(A2>=9, B2>=9, C2>=9, D2>=9), "Học sinh xuất sắc", "Học sinh giỏi")Kết quả: Kết quả sẽ là “Học sinh giỏi” (vì không có môn nào đạt điểm 9 trở lên).
3. NOT
=IF(NOT(D2<7), "Tiếp tục giữ vững", "Cần cố gắng hơn")Kết quả: Kết quả sẽ là “Tiếp tục giữ vững” (vì điểm Lý là 8.0, không nhỏ hơn 7).
Các hàm luận ký nâng cao trong ExcelMột số hàm Excel nâng cao khác
IFERROR(value, value_if_error): Hàm trả về một giá trị được chỉ định nếu một lỗi xảy ra, ngược lại trả về giá trị khác.
Ví dụ:
=IFERROR(A1/B1, "Lỗi chia cho 0")Kết quả: Nếu lỗi xảy ra khi chia A1 cho B1, hàm này sẽ trả về chuỗi “Lỗi chia cho 0”
HYPERLINK(link_location, [friendly_name]): Hàm sẽ tạo một liên kết tới một địa chỉ web hoặc tệp trong máy tính.
Ví dụ:
=HYPERLINK("https://mình.com/", "Click here")OFFSET(reference, rows, cols, [height], [width]): Hàm trả về một tham chiếu dịch chuyển từ một ô hoặc phạm vi ô hiện tại.
=OFFSET(A1, 1, 2)Lấy A1 làm ô gốc, thì kết quả sẽ là giá trị của ô C2 (dịch xuống 1 hàng và qua phải 2 cột).
YEARFRAC(start_date, end_date, [basis]): Hàm trả về số năm và thập kỷ tương đương giữa hai ngày.
=YEARFRAC(DATE(2020, 1, 1), DATE(2022, 6, 30))Kết quả: 2.5 (tương đương 2 năm và nửa năm)
Các hàm Excel nâng cao khácDCOUNTA(database, field, criteria): Hàm dùng để đếm số bản ghi không rỗng trong một cơ sở dữ liệu dựa trên các tiêu chí cung cấp.
=DCOUNTA(A1:C5, "Age", B1:B5)Kết quả: 2 (có 2 bản ghi có giá trị trong cột “Age”)
DGET(database, field, criteria): Trích xuất một giá trị duy nhất từ một cơ sở dữ liệu dựa trên các tiêu chí cung cấp.
=DGET(A1:C5, "Age", A1:A2)Kết quả: Công thức trên dùng để trích xuất tuổi của “Chung Đại” là 31.
DSUM(database, field, criteria): Dùng để tính tổng của các giá trị trong một cột cơ sở dữ liệu dựa trên các tiêu chí cung cấp.
=DSUM(A1:C5, "Age", C1:C2)Kết quả: Tính tổng tuổi của của người thuộc thành phố NY (tức là số tuổi của Chung Đại – 31)
Một số hàm đếm và trích xuất giá trị nâng caoCác hàm Excel nâng cao trong kế toán
Ví dụ: Bạn đang đầu tư vào một dự án có dòng tiền như sau:
- Năm 0: -100,000 (đầu tư ban đầu)
- Năm 1: 30,000
- Năm 2: 40,000
- Năm 3: 50,000
NPV(rate, value1, [value2], ...): Dùng để tính giá trị hiện tại của một chuỗi các dòng tiền (tiền vào và tiền ra) dựa trên một tỷ lệ lãi suất.
Với tỷ lệ lãi suất là 5%, công thức NPV sẽ là:
=NPV(0.05, -100000, 30000, 40000, 50000)IRR(values, [guess]): Dùng khi bạn muốn tìm tỷ lệ lãi suất nội bộ mà NPV của chuỗi các dòng tiền là 0.
=IRR(B1:B5)PV(rate, nper, pmt, [fv], [type]): Dùng nếu bạn muốn tính giá trị hiện tại của một khoản tiền trong tương lai.
Ví dụ: Bạn có thể tính giá trị hiện tại của 50,000 đồng sau 5 năm tới (với lãi suất là 6%)
=PV(0.06, 5, 0, 50000)FV(rate, nper, pmt, [pv], [type]): Hàm dùng để tính giá trị tương lai của một khoản tiền dựa trên lãi suất và số năm.
Ví dụ: Nếu bạn muốn biết giá trị tương của 10,000,000 đồng sau 10 năm với lãi suất 8%, bạn có thể dùng công thức sau:
=FV(0.08, 10, 0, -10000000)Kết quả khi sử dụng các công thức trên như sau:
Kết luận
Hy vọng qua bài viết tổng hợp các hàm Excel nâng cao này, bạn có thể áp dụng chúng vào thực tế công việc của mình. Việc sử dụng chúng không chỉ hỗ trợ bạn phân tích, xử lý các dữ liệu và yêu cầu phức tạp mà còn giúp bạn tiết kiệm thời gian hơn đấy.