Các hàm văn bản và toán học cơ bản trong Excel bạn nên biết

Việc áp dụng hàm có sẵn trong Excel giúp bạn tổng hợp số liệu một cách hiệu quả. Dưới đây là những hàm Excel cơ bản bạn cần biết để phục vụ cho công việc của mình.

1. Các hàm xử lý văn bản trong Excel

- Hàm TEXT trong Excel

Công dụng: Chuyển đổi số thành dạng văn bản.

Công thức: =TEXT (giá trị bạn muốn định dạng, 'mã định dạng bạn muốn sử dụng')

Ví dụ: Để thêm 'thứ' vào trước ngày ô B2 và hiển thị dưới dạng 'Wednesday/11/2019', hãy sử dụng công thức TEXT(B2, 'dd/mm/yyyy') để có kết quả là Wednesday/11/2019.

Hàm TEXT trong Excel

- Hàm CONCATENATE trong Excel

Công dụng: Kết hợp hai hoặc nhiều ô thành một ô duy nhất.

Công thức: =CONCATENATE(text1, [text2],...)

Các thành phần trong công thức:

+ Text 1 (bắt buộc): Thành phần đầu tiên để ghép nối. Đây có thể là giá trị văn bản, số hoặc tham chiếu ô.

+ Text 2 (tùy chọn): Thành phần văn bản bổ sung để ghép nối. Bạn có thể kết hợp tối đa 255 mục, lên đến 8192 ký tự.

Ví dụ: Khi nhập công thức =CONCATENATE(A2, “ ”, B2), bạn sẽ nhận được kết quả là Nguyễn Hòa.

Hàm CONCATENATE trong Excel

- Hàm TRIM trong Excel

Công dụng: Xóa các khoảng trắng dư thừa trong một văn bản hoặc chuỗi văn bản dài.

Công thức: =TRIM(text)

Text (bắt buộc): Văn bản mà bạn muốn loại bỏ khoảng trắng.

Ví dụ: Dù ô A2 và B2 có khoảng trắng, khi sử dụng hàm TRIM để loại bỏ, chúng sẽ kết hợp thành tên hoàn chỉnh. Nhập công thức =TRIM(A2) & “ ” & TRIM(B2) để có kết quả “Phan Huỳnh Kim My”.

Hàm TRIM trong Excel

- Hàm SUBSTITUTE trong Excel

Công dụng: Thay thế một hoặc nhiều ký tự trong một nội dung văn bản hiện có bằng những ký tự khác.

Công thức: =SUBSTITUTE(text, old_text, new_text, [instance_num])

Các thành phần:

+ Text (bắt buộc): Văn bản hoặc ô tham chiếu chứa văn bản bạn muốn thay thế ký tự.

+ Old_text (bắt buộc): Ký tự hoặc chuỗi văn bản bạn muốn thay thế.

+ New_text (bắt buộc): Văn bản bạn muốn dùng để thay thế cho old_text.

+ Instance_num (tùy chọn): Chỉ ra số lần xuất hiện của old_text mà bạn muốn thay thế bằng new_text. Nếu bạn xác định instance_num, chỉ những lần xuất hiện đó của old_text sẽ được thay thế. Nếu không, toàn bộ lần xuất hiện của old_text sẽ được thay thế.

Ví dụ: Thay dấu phẩy bằng dấu gạch chéo. Công thức =SUBSTITUTE(B2, ',', '/') cho kết quả là 09/03/1999.

Hàm SUBSTITUTE trong Excel

- Hàm VALUE trong Excel

Công dụng: Chuyển giá trị định dạng văn bản thành số.

Công thức: =VALUE(text)

Text là chuỗi số trong dấu ngoặc kép hoặc công thức, tham chiếu đến ô chứa chuỗi số bạn muốn chuyển đổi.

Ví dụ: Đổi giá trị văn bản '00299' thành số '299'. Khi dùng công thức =VALUE(A1), kết quả là 299.

Hàm VALUE trong Excel

- Các hàm chuyển đổi văn bản (UPPER, LOWER, PROPER)

+ Hàm UPPER

Công dụng: Chuyển đổi toàn bộ văn bản thành chữ in hoa.

Công thức: =UPPER(text)

Text (bắt buộc): Văn bản cần chuyển đổi thành chữ hoa. Text có thể là tham chiếu ô hoặc chuỗi văn bản.

Ví dụ: Để biến nội dung ô A1 thành chữ in hoa, dùng hàm UPPER. Nhập công thức =UPPER(A1) sẽ cho kết quả “HELLO WORLD”.

Hàm UPPER trong Excel

+ LOWER

Công dụng: Đổi chữ in hoa thành chữ thường.

Công thức: =LOWER(text)

Text (bắt buộc): Văn bản trong dấu ngoặc kép, công thức trả về văn bản, hoặc tham chiếu ô chứa văn bản bạn muốn viết thường.

Ví dụ: Để chuyển nội dung ô A1 thành chữ thường, dùng LOWER. Nhập công thức =LOWER(A1) sẽ cho kết quả “hello world”.

Hàm LOWER trong Excel

+ PROPER

Công dụng: Viết hoa các ký tự đầu tiên trong văn bản với hàm PROPER.

Công thức: =PROPER(text)

Text (bắt buộc): Văn bản đặt trong dấu ngoặc kép, công thức trả về văn bản, hoặc tham chiếu ô chứa văn bản cần viết hoa.

Ví dụ: Đổi nội dung ô A1 thành văn bản có ký tự đầu viết hoa với hàm PROPER. Dùng công thức =PROPER(A1) cho kết quả “Hello World”.

Hàm PROPER trong Excel

- Hàm trích xuất ký tự trong văn bản (LEFT, RIGHT, MID)

+ LEFT

Công dụng: Lấy ký tự từ phía trái của chuỗi.

Công thức: =LEFT(text, [num_chars])

Trong đó:

Text (bắt buộc): Chuỗi văn bản chứa ký tự cần trích xuất.

Num_chars (tùy chọn): Xác định số lượng ký tự bạn muốn trích xuất bằng hàm LEFT.

Ví dụ: Dùng hàm LEFT để lấy 5 ký tự đầu tiên từ “Hello World”, ta nhập LEFT(A1, 5) (1), kết quả sẽ là “HELLO” (2).

LEFT

+ RIGHT

Công dụng: Lấy ký tự từ bên phải của chuỗi.

Công thức: =RIGHT(text, [num_chars])

Trong đó:

Text (bắt buộc): Chuỗi chứa ký tự bạn muốn trích xuất bằng hàm RIGHT.

Num_chars (tùy chọn): Số lượng ký tự muốn trích xuất từ bên phải của chuỗi bằng hàm RIGHT.

Ví dụ: Dùng hàm RIGHT để lấy 5 ký tự cuối của “HELLO WORLD”, nhập RIGHT(A1, 5) (1), kết quả sẽ là “WORLD” (2).

RIGHT

+ MID

Công dụng: Lấy chuỗi ký tự từ giữa văn bản.

Công thức: =MID(text, start_num, num_chars)

Trong đó:

Text (bắt buộc): Chuỗi chứa ký tự bạn muốn trích xuất bằng hàm MID.

Start_num (bắt buộc): Vị trí của ký tự đầu tiên bạn muốn trích từ chuỗi, bắt đầu từ 1.

Ví dụ: Để lấy chữ “CHÀO” từ “XIN CHÀO HOA”, nhập MID(A1, 5, 4) sẽ cho kết quả là “CHÀO”.

Hàm MID

- Hàm logic:

+ Hàm AND:

Công dụng: Hàm AND cho phép bạn kiểm tra xem tất cả các điều kiện cho trước có phải là TRUE hay không.

Công thức: =AND(logical1, [logical2], ...)

Trong đó: Điều kiện logic có thể được đánh giá là TRUE hoặc FALSE. Điều kiện đầu tiên (logical1) là bắt buộc, các điều kiện tiếp theo là tùy chọn.

Ví dụ: Nếu B1=3 và C1=0, cả hai đều đúng thì trả về TRUE, ngược lại sẽ trả về FALSE. Nhập công thức =AND(B1=3, C1=0) cho kết quả FALSE.

Hàm AND

+ Hàm OR

Công dụng: Trả về TRUE nếu ít nhất một điều kiện là TRUE.

Trong thực tế, hàm OR thường kết hợp với IF để tạo điều kiện logic và đưa ra kết quả mong muốn.

Công thức: =OR(logical1, [logical2], ...)

Trong đó: Các logic có thể là TRUE hoặc FALSE. Điều kiện đầu tiên (logical1) là bắt buộc, còn các điều kiện sau là tùy chọn.

Ví dụ: Nếu B1=3 hoặc C1=0, chỉ cần một điều kiện đúng là trả về TRUE; cả hai sai sẽ trả về FALSE. Công thức =OR(B1=3, C1=0) cho kết quả TRUE.

Hàm OR

- Hàm NOT

Công dụng: Hàm NOT đảo ngược giá trị logic: TRUE thành FALSE và ngược lại.

Công thức: =NOT(logical)

Trong đó: Logic là điều kiện có thể là TRUE hoặc FALSE.

Ví dụ: Ô B3 có giá trị TRUE. Sau khi áp dụng hàm NOT, giá trị trở thành FALSE. Công thức =NOT(B3) sẽ trả về FALSE.

Hàm NOT

- Hàm IF

Công dụng: Dùng để kiểm tra điều kiện và trả về giá trị dựa trên kết quả kiểm tra. Đây là một trong những hàm phổ biến nhất trong Excel.

+ Lồng hàm IF

Yêu cầu: Nếu thuộc phòng ban Kế toán thì cộng thêm 100, Nhân sự cộng thêm 200, IT cộng thêm 300, Marketing cộng 0.

Công thức: =IF(B1= “Kế toán”,100, IF(B1= “Nhân sự”,200,IF(B1= “IT”,300,0)))

Kết quả chị Tý được 100 điểm vì chị làm việc ở phòng Kế toán.

Hàm IF lồng IF

+ Kết hợp hàm IF với AND và OR

Công thức: =IF(logical_test, [value_if_true], [value_if_false])

Chú thích:

+ Logical_test (bắt buộc): Điều kiện cần kiểm tra.

+ Value_if_true (bắt buộc): Trả về giá trị nếu điều kiện Đúng.

+ Value_if_false (bắt buộc): Trả về giá trị nếu điều kiện Sai.

Ví dụ: Nếu ô B1 chứa giá trị HOA, công thức sẽ trả về “HOA XINH ĐẸP”, nếu không sẽ trả về “KHÔNG PHẢI HOA”. Công thức =IF(B1=“HOA”, “HOA XINH ĐẸP”, “KHÔNG PHẢI HOA”) sẽ cho ra kết quả “HOA XINH ĐẸP”.

Hàm IF

- Hàm IFERROR và IFNA

+ Hàm IFERROR

Công dụng: Xử lý tất cả các loại lỗi trong Excel như #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? hoặc #NULL!.

Công thức: =IFERROR(giá_trị, giá_trị_nếu_lỗi)

Chi tiết:

+ Giá trị (bắt buộc): Giá trị cần kiểm tra, có thể là phép tính, công thức, hoặc hàm Excel.

+ Giá trị nếu lỗi (bắt buộc): Giá trị trả về nếu gặp lỗi.

Lưu ý:

+ Giá trị trả về có thể là khoảng trắng (“”), số 0, hoặc thông báo như “Lỗi kết quả”.

+ Giá trị Value_if_error được sử dụng để trả về giá trị khi công thức gặp các lỗi như: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? hoặc #NULL!.

Ví dụ: Ô D3 tính tổng, nhưng gặp lỗi #VALUE! do cộng ô định dạng Text với Number. Nhập công thức =IFERROR(D3, “Lỗi rồi”) (1) sẽ cho kết quả “Lỗi rồi” (2).

Hàm IFERROR

+ Hàm IFNA

Công dụng: Hàm IFNA trả về giá trị bạn chỉ định nếu công thức gặp lỗi #N/A; nếu không, hàm trả về kết quả của công thức.

Công thức: =IFNA(giá_trị, giá_trị_nếu_na)

Chi tiết:

+ Value: Giá trị hoặc kết quả từ một công thức có thể gặp lỗi #N/A.

+ Value_if_na: Giá trị thay thế khi công thức trả về lỗi #N/A.

Ví dụ: Nếu không tìm được tên Nguyễn Văn M, hàm sẽ trả về “KHÔNG TÌM THẤY Nguyễn Văn M”. Nhập công thức =IFNA(F2, “KHÔNG TÌM THẤY” & E2) (1) cho kết quả “KHÔNG TÌM THẤY Nguyễn Văn M” (2).

Hàm IFNA

2. Các hàm toán học trong Excel

- Hàm tính tổng ô

+ Hàm SUM

Công dụng: Hàm SUM cộng các giá trị trong các ô và trả về kết quả tổng.

Ví dụ: Để tính tổng các ô từ A2 đến A18, dùng công thức =SUM(A2:A18).

Công thức: =SUM(number1, [number2],…).

Trong đó: Number1 (bắt buộc) là số đầu tiên, các số tiếp theo là tùy chọn.

Ví dụ: Tính tổng giá trị trong ô B2 và C2 bằng cách dùng hàm SUM. Công thức =SUM(B2, C2) (1) cho kết quả là 108 (2).

Hàm SUM

+ Hàm SUMPRODUCT

Công dụng: Hàm SUMPRODUCT nhân các mảng với nhau và trả về tổng của các tích đó.

Công thức: =SUMPRODUCT(array1, array2,…)

Trong đó: Array1 (bắt buộc) là mảng chính, các mảng khác là tùy chọn.

Ví dụ:

- SUMPRODUCT theo điều kiện

Ví dụ: Máy tính lấy giá trị ô C2 nhân D2, rồi C3 nhân D3 và tiếp tục đến C10 nhân D10. Công thức =SUMPRODUCT(C2:C10, D2:D10) (1) cho kết quả 115900 (2).

SUMPRODUCT với điều kiện

- Tính SUMPRODUCT không điều kiện

Ví dụ: Để tính tổng giá trị của ô “GÀ”, nhập công thức =SUMPRODUCT(--(B2:B10=$B$2), C2:C10, D2:D10) (1) cho kết quả 4200 (2).

Tính SUMPRODUCT không điều kiện

- Hàm làm tròn số

+ Hàm ROUND

Công dụng: Làm tròn số; nếu giá trị nhỏ hơn 5 thì làm tròn xuống, nếu giá trị từ 5 trở lên thì làm tròn lên.

Công thức: =ROUND(number, num_digits).

Giải thích:

+ Number (bắt buộc): Số thực cần làm tròn.

+ Num_digits (bắt buộc): Số chữ số thập phân cần làm tròn.

Ví dụ: Khi muốn làm tròn điểm số của học sinh, dùng hàm ROUND. Ví dụ ô C2 chứa điểm số cần làm tròn và 1 là số thập phân. Nhập công thức =ROUND(C2,1) sẽ cho kết quả là 9.

Hàm ROUND

+ Hàm ROUNDUP

Công dụng: Làm tròn số lên, tăng số cho đến mức cao hơn.

Công thức: =ROUNDUP(number, num_digits)

Thông tin:

+ Number (bắt buộc): Số thực muốn làm tròn lên.

+ Num_digits (bắt buộc): Số chữ số thập phân cần làm tròn.

Ví dụ: Sử dụng ROUNDUP để làm tròn số lên. Khi nhập công thức =ROUNDUP(C2,1) sẽ có kết quả là 2.4.

Hàm ROUNDUP

+ Hàm ROUNDDOWN

Công dụng: Làm tròn số xuống, tiến gần về 0.

Công thức: =ROUNDDOWN(number, num_digits)

Chi tiết:

+ Number (bắt buộc): Số thực cần làm tròn xuống.

+ Num_digits (bắt buộc): Số chữ số thập phân muốn làm tròn đến.

Ví dụ: Khi cần làm tròn số xuống, sử dụng ROUNDDOWN. Với công thức =ROUNDDOWN(C2,1) sẽ có kết quả 2.3.

Hàm ROUNDDOWN

+ Hàm MROUND

Công dụng: Làm tròn số đến bội số mong muốn.

Công thức: =MROUND(number, multiple)

Chi tiết:

+ Number (bắt buộc): Số cần làm tròn.

+ Multiple (bắt buộc): Bội số cần làm tròn đến.

Ví dụ: Dùng MROUND để làm tròn số lên 9.5. Công thức =MROUND(C2,9.5) cho kết quả 9.5.

Hàm MROUND

- Hàm MOD

Công dụng: Trả về phần dư sau phép chia.

Công thức: =MOD(number, divisor)

Chi tiết:

+ Number (bắt buộc): Số bị chia.

+ Divisor (bắt buộc): Số chia.

Ví dụ: Dùng hàm MOD để tìm số dư khi chia tổng số lượng và giá trị, 154 chia 5 dư 4. Công thức =MOD(D2,C2) (1) cho kết quả 4 (2).

Hàm MOD

3. Hàm thống kê trong Excel

- Hàm tìm giá trị lớn nhất, nhỏ nhất và trung bình

+ Hàm MIN

Công dụng: Trả về giá trị thấp nhất từ danh sách số liệu.

Công thức: =MIN(number1, [number2], …)

Trong đó: Number1 là bắt buộc, các số tiếp theo là tùy chọn. Bạn có thể chọn từ 1 đến 255 số để tìm giá trị nhỏ nhất.

Ví dụ: Để tìm số lượng áo ít nhất, dùng hàm MIN. Khi nhập công thức =MIN(C2, C3, C4) (1) sẽ cho kết quả 3 (2).

Hàm MIN

+ Hàm MAX

Công dụng: Trả về giá trị lớn nhất từ danh sách số liệu.

Công thức: =MAX(number1, [number2],...)

Trong đó: Number1 là bắt buộc, các số tiếp theo là tùy chọn. Bạn có thể chọn từ 1 đến 255 số để tìm giá trị lớn nhất.

Ví dụ: Để tìm áo có số lượng nhiều nhất, dùng hàm MAX. Khi nhập công thức =MAX(C2, C3, C4) (1) sẽ cho kết quả 12 (2).

Hàm MAX

+ Hàm AVERAGE

Công dụng: Tính giá trị trung bình của các đối số.

Công thức: =AVERAGE(number1, [number2],…).

Trong đó:

+ Number1 (bắt buộc): Giá trị đầu tiên, tham chiếu ô hoặc phạm vi cần tính trung bình.

+ Number2,... (tùy chọn): Các số, tham chiếu ô hoặc phạm vi khác để tính trung bình, tối đa 255 mục.

Ví dụ: Sử dụng hàm AVERAGE để tính điểm trung bình học sinh, với điểm môn toán ở vị trí đầu tiên, và môn văn ở vị trí thứ hai. Khi nhập công thức =AVERAGE(C2, D2) (1) sẽ cho kết quả 6 (2).

Hàm AVERAGE

+ Hàm SMALL

Công dụng: Trả giá trị nhỏ thứ n trong mảng.

Công thức: =SMALL(array, n)

Trong đó:

+ Mảng (bắt buộc): Phạm vi dữ liệu số để tìm giá trị nhỏ thứ k.

+ N (bắt buộc): Vị trí (từ nhỏ nhất) trong mảng cần trả về.

Ví dụ: Để tìm học sinh có điểm thấp nhất dùng hàm SMALL. Với cột điểm là E2:E4, và n = 1 để tìm giá trị nhỏ nhất. Nhập công thức =SMALL(E2:E4,1) cho kết quả 5.5.

Hàm SMALL

+ Hàm LARGE

Công dụng: Trả về giá trị lớn thứ n trong mảng, cho phép chọn một giá trị dựa trên vị trí của nó.

Công thức: =LARGE(array, n)

Giải thích:

+ Phạm vi (bắt buộc): Mảng dữ liệu số cần tìm giá trị nhỏ thứ k.

+ N (bắt buộc): Thứ tự (từ giá trị lớn nhất) trong mảng cần trả về.

Ví dụ: Dùng hàm LARGE để tìm điểm cao thứ hai. Với mảng dữ liệu là cột điểm trung bình, và n là 2 để tìm giá trị cao thứ hai. Nhập công thức =LARGE(E2:E4,2) cho kết quả 6.

Hàm LARGE

- Hàm đếm ô

+ Hàm COUNT

Công dụng: Đếm số lượng các giá trị số (số và ngày) trong các đối số.

Công thức: =COUNT(giá trị1, [giá trị2],…)

Giải thích:

+ Giá trị1 (bắt buộc): Đối số đầu tiên, ô hoặc phạm vi để đếm số.

+ Giá trị2,... (tùy chọn): Tối đa 255 giá trị hoặc phạm vi cần đếm.

Ví dụ: Dùng hàm COUNT để đếm số môn học có điểm của học sinh trong mảng giá trị. Nhập công thức =COUNT(C2:E2) cho kết quả 3.

Hàm COUNT

+ Hàm COUNTA

Công dụng: Đếm ô có chứa dữ liệu và bao gồm cả ô có giá trị lỗi hoặc chuỗi văn bản rỗng.

Công thức: =COUNTA(giá trị1, [giá trị2],…)

Giải thích:

+ Giá trị1 (bắt buộc): Đối số đầu tiên để đếm.

+ Giá trị2,... (tùy chọn): Các đối số bổ sung để đếm, tối đa 255.

Ví dụ: Sử dụng hàm COUNTA để đếm ô có dữ liệu trong khoảng C3:E3 cho kết quả 3.

Hàm COUNTA

+ Hàm COUNTBLANK

Công dụng: Đếm số ô trống trong phạm vi xác định, bao gồm ô có chuỗi văn bản trống (“”).

Công thức: =COUNTBLANK(phạm vi)

Trong đó: Phạm vi (bắt buộc): Khu vực cần đếm ô trống.

Ví dụ: Để đếm môn học chưa có điểm, sử dụng COUNTBLANK với phạm vi từ C2 đến E2. Khi nhập công thức =COUNTBLANK(C2:E2), kết quả là 0.

Hàm COUNTBLANK

+ Hàm COUNTIF

Công dụng: Hàm COUNTIF đếm số ô thỏa mãn một điều kiện cụ thể.

Công thức: =COUNTIF(phạm vi, điều kiện)

Trong đó:

+ Phạm vi: Khu vực ô cần đếm.

+ Điều kiện: Tiêu chí cụ thể để đếm ô trong phạm vi, có thể là số, ký tự hoặc biểu thức logic.

Ví dụ: Khi nhập công thức =COUNTIF(B2:B7,$B$2), có 2 ô chứa giá trị 'Nguyễn Thị A'.

Hàm COUNTIF

+ Hàm COUNTIFS

Công dụng: Hàm COUNTIFS đếm số ô thỏa mãn tất cả các tiêu chí đã chỉ định. Phù hợp để đếm ô với nhiều điều kiện phức tạp.

Công thức: =COUNTIFS(phạm vi_tiêu_chí1, tiêu_chí1, [phạm vi_tiêu_chí2, tiêu_chí2]...)

Trong đó:

+ Phạm vi_tiêu_chí1 (bắt buộc): Phạm vi đầu tiên để áp dụng tiêu chí liên quan.

+ Tiêu_chí1 (bắt buộc): Điều kiện cần thỏa mãn trong phạm vi_tiêu_chí1, có thể là số, biểu thức, ô tham chiếu hoặc văn bản.

+ Phạm vi_tiêu_chí2, tiêu_chí2,... (tùy chọn): Phạm vi và tiêu chí bổ sung. Tối đa 127 cặp phạm vi và tiêu chí.

Ví dụ: Công thức =COUNTIFS($B$2:$B$8,E3,$C$2:$C$8,F3) cho kết quả 1.

Hàm COUNTIFS

Trên đây là các công thức Excel thông dụng để xử lý và tính toán dữ liệu. Hy vọng sẽ hữu ích cho bạn.