Microsoft Excel là một công cụ mạnh mẽ không thể thiếu trong học tập và công việc, đặc biệt khi cần xử lý số liệu. Để làm chủ Excel, việc nắm vững các công thức tính Excel cơ bản là điều kiện tiên quyết. Bài viết này của Gia Sư Thành Tâm sẽ cung cấp cái nhìn toàn diện về những hàm Excel thiết yếu, giúp bạn thực hiện các phép tính và phân tích dữ liệu một cách hiệu quả nhất.
Hàm Tính Toán Số Học Cơ Bản Trong Excel
Các hàm tính toán số học là nền tảng của mọi bảng tính Excel, giúp người dùng thực hiện các phép cộng, trừ, nhân, chia, tìm giá trị lớn nhất, nhỏ nhất hoặc tính trung bình một cách nhanh chóng. Việc hiểu rõ và áp dụng thành thạo những hàm này là bước đầu tiên để làm chủ các công thức tính Excel cơ bản.
Hàm SUM: Tính Tổng Các Giá Trị
Hàm SUM được sử dụng để tính tổng tất cả các số trong một dãy ô hoặc một tập hợp các số được chỉ định. Đây là một trong những hàm được dùng phổ biến nhất để tổng hợp dữ liệu.
Cú pháp: =SUM(number1, [number2], ...)
Trong đó:
number1, number2, ...: Là các giá trị số, tham chiếu ô, hoặc vùng ô cần tính tổng. Bạn có thể nhập tối đa 255 đối số.
Ví dụ: Nếu bạn muốn tính tổng các giá trị từ ô A1 đến A5, công thức sẽ là =SUM(A1:A5). Kết quả trả về sẽ là tổng của tất cả các số trong phạm vi đó. Chẳng hạn, với các giá trị 20, 40, 60 trong A1:A3, =SUM(A1:A3) sẽ cho ra kết quả 120.
Tổng các giá trị trong dãy
Hàm MIN và MAX: Tìm Giá Trị Nhỏ Nhất và Lớn Nhất
Hàm MIN và MAX giúp bạn nhanh chóng xác định giá trị nhỏ nhất hoặc lớn nhất trong một vùng dữ liệu cụ thể. Đây là các công thức tính Excel cơ bản rất hữu ích trong việc phân tích thống kê nhanh.
Hàm MIN
Cú pháp: =MIN(number1, [number2], ...)
Trong đó:
number1, number2, ...: Là các giá trị số, tham chiếu ô, hoặc vùng ô cần so sánh.
Kết quả trả về là giá trị nhỏ nhất trong vùng dữ liệu được chọn. Ví dụ, với công thức =MIN(A2:C5) trong một bảng số liệu, Excel sẽ trả về giá trị nhỏ nhất trong phạm vi từ A2 đến C5.
Tìm giá trị nhỏ nhất trong bảng dữ liệu
Hàm MAX
Cú pháp: =MAX(number1, [number2], ...)
Trong đó:
number1, number2, ...: Là các giá trị cần so sánh.
Kết quả trả về là giá trị lớn nhất trong vùng dữ liệu được chọn. Tương tự, nếu bạn dùng =MAX(A2:C5), Excel sẽ tìm và hiển thị giá trị lớn nhất trong vùng dữ liệu đó, ví dụ như số 89.
Tìm giá trị lớn nhất trong vùng dữ liệu
Hàm COUNT và COUNTA: Đếm Số Lượng Ô
Các hàm COUNT và COUNTA thuộc nhóm công thức tính Excel cơ bản giúp bạn đếm số lượng ô theo các tiêu chí khác nhau trong một vùng dữ liệu, từ đó cung cấp cái nhìn tổng quan về số lượng mục nhập.
Hàm COUNT
Hàm COUNT được dùng để đếm số lượng ô chỉ chứa giá trị số trong một vùng dữ liệu.
Cú pháp: =COUNT(value1, [value2], ...)
Trong đó:
value1, ...: Là tham chiếu ô hoặc phạm vi muốn đếm số.
Ví dụ: Với công thức =COUNT(A2:C5), Excel sẽ đếm tất cả các ô trong phạm vi A2:C5 có chứa số và hiển thị tổng số lượng đó.
Đếm giá trị là số trong vùng dữ liệu
Hàm COUNTA
Hàm COUNTA dùng để đếm các ô không trống (chứa bất kỳ loại dữ liệu nào: số, văn bản, lỗi, giá trị logic) trong một vùng dữ liệu nhất định.
Cú pháp: =COUNTA(value1, [value2], ...)
Trong đó:
value1, ...: Là những ô cần đếm hoặc một vùng cần đếm.
Ví dụ: Nếu áp dụng =COUNTA(A2:C5) vào bảng dữ liệu, kết quả sẽ là tổng số lượng các ô không trống, bao gồm cả số và văn bản.
Đếm số lượng ô không trống trong một vùng dữ liệu
Hàm ODD và EVEN: Làm Tròn Số Nguyên Lẻ/Chẵn
Hàm ODD và EVEN là các công thức tính Excel cơ bản dùng để làm tròn một số lên số nguyên lẻ hoặc chẵn gần nhất.
Hàm ODD
Hàm ODD trả về số được làm tròn lên số nguyên lẻ gần nhất.
Cú pháp: =ODD(Number)
Trong đó:
Number: Giá trị số cần làm tròn.
Ví dụ: Nếu bạn có số 4.6, công thức =ODD(4.6) sẽ trả về 5.
Trả về số nguyên lẻ gần nhất
Hàm EVEN
Hàm EVEN trả về số được làm tròn lên số nguyên chẵn gần nhất.
Cú pháp: =EVEN(Number)
Trong đó:
Number: Giá trị số cần làm tròn.
Ví dụ: Với số 53.4, công thức =EVEN(53.4) sẽ cho ra kết quả 54.
Làm tròn lên số nguyên chẵn gần nhất
Hàm AVERAGE: Tính Trung Bình Cộng
Hàm AVERAGE là công thức tính Excel cơ bản dùng để tính trung bình cộng của một dãy số trong trang tính. Đây là hàm rất quan trọng trong phân tích thống kê đơn giản.
Cú pháp: =AVERAGE(number1, [number2], ...)
Trong đó:
number1(Bắt buộc): Số thứ nhất, tham chiếu ô, hoặc phạm vi mà bạn muốn tính trung bình.number2, ...(Tùy chọn): Các số, tham chiếu ô hoặc phạm vi bổ sung mà bạn muốn tính trung bình, tối đa là 255 đối số.
Ví dụ: Để tính lương trung bình của 6 tháng từ ô C3 đến C8, bạn sẽ dùng công thức =AVERAGE(C3:C8). Kết quả sẽ là giá trị trung bình cộng của các tháng lương.
Giá trị trung bình của vùng dữ liệu
Hàm PRODUCT: Tính Tích Các Đối Số
Hàm PRODUCT là một công thức tính Excel cơ bản giúp nhân tất cả các đối số được chọn với nhau và trả về tích của chúng. Hàm này tương đương với việc sử dụng toán tử nhân (*) nhưng tiện lợi hơn khi làm việc với nhiều ô hoặc phạm vi.
Cú pháp: =PRODUCT(number1, [number2], ...)
Trong đó:
number1(Bắt buộc): Số thứ nhất, tham chiếu ô, hoặc phạm vi mà bạn muốn tính tích.number2, ...(Tùy chọn): Các số, tham chiếu ô hoặc phạm vi bổ sung mà bạn muốn tính tích, tối đa là 255 đối số.
Ví dụ: Để tính tích các giá trị trong phạm vi D2:D8, bạn sẽ nhập công thức =PRODUCT(D2:D8). Excel sẽ nhân tất cả các số trong các ô đó và hiển thị kết quả.
Đi đến ô tính tích > Nhập công thức =PRODUCT(D2:D8)
Hàm ROUND, ROUNDUP, ROUNDDOWN: Làm Tròn Số Nâng Cao
Ngoài ODD và EVEN, Excel còn cung cấp các hàm làm tròn mạnh mẽ hơn như ROUND, ROUNDUP, và ROUNDDOWN, giúp bạn kiểm soát chính xác số chữ số thập phân cần làm tròn. Đây là những công thức tính Excel cơ bản nhưng cực kỳ hữu ích trong kế toán và phân tích tài chính.
Hàm ROUND
Làm tròn một số đến một số chữ số thập phân cụ thể.
Cú pháp: =ROUND(number, num_digits)
Trong đó:
number: Số cần làm tròn.num_digits: Số chữ số thập phân muốn làm tròn đến. Nếunum_digitslớn hơn 0, số được làm tròn đến số vị trí thập phân được chỉ định. Nếu bằng 0, số được làm tròn đến số nguyên gần nhất. Nếu nhỏ hơn 0, số được làm tròn về phía bên trái dấu thập phân.
Ví dụ: =ROUND(3.14159, 2) sẽ trả về 3.14.
Hàm ROUNDUP
Làm tròn một số lên, ra xa số 0.
Cú pháp: =ROUNDUP(number, num_digits)
Ví dụ: =ROUNDUP(3.14159, 2) sẽ trả về 3.15.
Hàm ROUNDDOWN
Làm tròn một số xuống, về phía số 0.
Cú pháp: =ROUNDDOWN(3.14159, 2)
Ví dụ: =ROUNDDOWN(3.14159, 2) sẽ trả về 3.14.
Hàm MOD: Lấy Số Dư Của Phép Chia
Hàm MOD là một công thức tính Excel cơ bản dùng để trả về phần dư sau khi một số được chia cho một số chia. Hàm này rất hữu ích trong các phép tính liên quan đến chu kỳ hoặc nhóm.
Cú pháp: =MOD(number, divisor)
Trong đó:
number: Số bị chia.divisor: Số chia.
Ví dụ: =MOD(10, 3) sẽ trả về 1, vì 10 chia 3 được 3 và dư 1.
Hàm Điều Kiện Logic Phổ Biến
Các hàm điều kiện logic cho phép bạn thực hiện các phép tính dựa trên các tiêu chí nhất định, mang lại sự linh hoạt đáng kể trong việc phân tích dữ liệu. Việc thành thạo nhóm công thức tính Excel cơ bản này là rất quan trọng để xây dựng các bảng tính thông minh và tự động.
Hàm COUNTIF: Đếm Với Điều Kiện
Hàm COUNTIF được sử dụng để đếm số lượng ô thỏa mãn một điều kiện cụ thể trong một vùng dữ liệu được chọn. Đây là một trong những công thức tính Excel cơ bản giúp bạn thống kê nhanh các mục dữ liệu.
Cú pháp: =COUNTIF(range, criteria)
Trong đó:
range: Vùng dữ liệu cần đếm.criteria: Điều kiện để đếm (có thể là số, văn bản, biểu thức, tham chiếu ô).
Ví dụ: Nếu có một bảng thống kê mặt hàng và số lượng tồn kho. Để biết có bao nhiêu mặt hàng còn tồn trên 150 sản phẩm, bạn nhập công thức: =COUNTIF(C2:C11, ">150"). Kết quả sẽ cho thấy có 8 mặt hàng tồn trên 150 sản phẩm.
Ví dụ hàm COUNTIF
Hàm IF: Kiểm Tra Điều Kiện Logic
Hàm IF là một công thức tính Excel cơ bản nhưng cực kỳ mạnh mẽ, dùng để kiểm tra dữ liệu có thỏa mãn điều kiện người dùng đặt ra hay không, và trả về kết quả theo biểu thức logic (Đúng hoặc Sai).
Cú pháp: =IF(Logical_test, Value_if_true, Value_if_false)
Trong đó:
Logical_test: Điều kiện cần kiểm tra.Value_if_true: Giá trị trả về nếu điều kiện thỏa mãn.Value_if_false: Giá trị trả về nếu điều kiện không thỏa mãn.
Lưu ý: Nếu bạn bỏ trống Value_if_true và Value_if_false, nếu điều kiện thỏa mãn thì giá trị trả về sẽ là 0 và nếu điều kiện không thỏa mãn thì giá trị trả về sẽ là FALSE.
Ví dụ: Để xét học sinh có qua môn hay không với điều kiện điểm số từ 7 trở lên là “Đạt” và thấp hơn 7 là “Không Đạt”. Tại ô D2, công thức sẽ là: =IF(C2>=7, "Đạt", "Không Đạt"). Kết quả sẽ hiển thị “Đạt” hoặc “Không Đạt” tương ứng với từng học sinh.
Sử dụng hàm IF
Hàm SUMIF: Tính Tổng Với Điều Kiện
Hàm SUMIF là một công thức tính Excel cơ bản dùng để tính tổng các giá trị trong một phạm vi đáp ứng tiêu chí xác định. Đây là phiên bản có điều kiện của hàm SUM.
Cú pháp: =SUMIF(range, criteria, [sum_range])
Trong đó:
range: Phạm vi ô bạn muốn đánh giá theo tiêu chí.criteria: Tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định sẽ cộng ở ô nào.sum_range(Tùy chọn): Các ô thực tế để cộng. Nếu đối sốsum_rangebị bỏ qua, Excel sẽ cộng các ô được xác định trong đối sốrange.
Ví dụ: Để tính tổng số lượng màu “cam” có trong cột B dựa trên tên màu ở cột A, bạn có công thức =SUMIF(A1:A8, "cam", B1:B8). Excel sẽ tìm các ô có giá trị “cam” trong A1:A8 và cộng tổng các giá trị tương ứng trong B1:B8.
Hàm SUMIF
Hàm OR và AND: Kết Hợp Các Điều Kiện Logic
Hàm OR và AND là các công thức tính Excel cơ bản dùng để kết hợp nhiều điều kiện logic, cho phép bạn xây dựng các biểu thức điều kiện phức tạp hơn.
Hàm OR
Hàm OR trả về TRUE nếu bất kỳ điều kiện nào được chỉ định là TRUE, và trả về FALSE nếu tất cả các điều kiện đều FALSE.
Cú pháp: =OR(logical1, [logical2], ...)
Trong đó:
logical1, [logical2], ...: Là các điều kiện logic bạn muốn kiểm tra.
Ví dụ: =OR(A1>10, B1="Đạt") sẽ trả về TRUE nếu A1 lớn hơn 10 HOẶC B1 là “Đạt”.
Hàm AND
Hàm AND trả về TRUE nếu tất cả các điều kiện được chỉ định là TRUE, và trả về FALSE nếu bất kỳ điều kiện nào là FALSE.
Cú pháp: =AND(logical1, [logical2], ...)
Trong đó:
logical1, [logical2], ...: Là các điều kiện logic bạn muốn kiểm tra.
Ví dụ: =AND(A1>10, B1="Đạt") sẽ trả về TRUE nếu A1 lớn hơn 10 VÀ B1 là “Đạt”.
Hàm NOT: Đảo Ngược Điều Kiện Logic
Hàm NOT là một công thức tính Excel cơ bản dùng để đảo ngược giá trị logic của một điều kiện. Nếu điều kiện ban đầu là TRUE, NOT sẽ trả về FALSE và ngược lại.
Cú pháp: =NOT(logical)
Trong đó:
logical: Một giá trị hoặc biểu thức logic.
Ví dụ: =NOT(A1>10) sẽ trả về TRUE nếu A1 KHÔNG lớn hơn 10 (tức A1 nhỏ hơn hoặc bằng 10).
Hàm Xử Lý Văn Bản Trong Excel
Các hàm xử lý văn bản giúp bạn thao tác với chuỗi ký tự, trích xuất thông tin, kết hợp các đoạn văn bản hoặc chuẩn hóa định dạng dữ liệu. Nắm vững nhóm công thức tính Excel cơ bản này sẽ giúp bạn làm việc hiệu quả hơn với các dữ liệu dạng chữ.
Hàm LEFT, RIGHT, MID: Trích Xuất Ký Tự
Hàm LEFT, RIGHT và MID là những công thức tính Excel cơ bản quan trọng để trích xuất một phần của chuỗi văn bản từ vị trí đầu, cuối hoặc giữa.
Hàm LEFT
Hàm LEFT dùng để cắt chuỗi ký tự từ bên trái của chuỗi văn bản được chọn.
Cú pháp: =LEFT(text, [num_chars])
Trong đó:
text: Chuỗi văn bản chứa ký tự mà bạn muốn trích xuất.num_chars(Tùy chọn): Số lượng ký tự mà hàm LEFT trích xuất từ bên trái. Nếu bỏ qua, mặc định là 1.
Ví dụ: Để tìm 3 ký tự đầu tiên tại ô B3 chứa “Tập đoàn công nghệ”, bạn nhập =LEFT(B3, 3) và được kết quả “Tập”.
Ví dụ về hàm LEFT
Hàm RIGHT
Hàm RIGHT dùng để tách chuỗi ký tự từ bên phải của chuỗi văn bản được chọn.
Cú pháp: =RIGHT(text, [num_chars])
Trong đó:
text: Chuỗi văn bản chứa ký tự mà bạn muốn trích xuất.num_chars(Tùy chọn): Số lượng ký tự mà hàm RIGHT trích xuất từ bên phải. Nếu bỏ qua, mặc định là 1.
Ví dụ: Để tìm 7 ký tự cuối tại ô B3 chứa “Gia Sư Thành Tâm”, bạn nhập =RIGHT(B3, 7) và được kết quả “Thành Tâm”.
Ví dụ về hàm RIGHT
Hàm MID
Hàm MID trả về một số lượng ký tự cụ thể từ một chuỗi văn bản, bắt đầu từ vị trí do bạn chỉ định và dựa vào số lượng ký tự do bạn chỉ định.
Cú pháp: =MID(text, start_num, num_chars)
Trong đó:
text: Chuỗi văn bản có chứa các ký tự muốn trích xuất.start_num: Vị trí của ký tự đầu tiên mà bạn muốn trích xuất trong văn bản.num_chars: Số ký tự mà bạn muốn hàm MID trả về từ văn bản.
Ví dụ: Với chuỗi “Gia Sư Thành Tâm” tại ô B3, công thức =MID(B3, 5, 5) (bắt đầu từ ký tự thứ 5, lấy 5 ký tự) sẽ trả về “Sư Th”.
Ví dụ về hàm MID
Hàm LEN: Đếm Số Ký Tự Trong Chuỗi
Hàm LEN là một công thức tính Excel cơ bản dùng để đếm số ký tự trong một chuỗi hoặc một ô chứa chuỗi ký tự, bao gồm cả khoảng trắng.
Cú pháp: =LEN(text)
hoặc
=LEN(cell_reference)
Trong đó:
text: Chuỗi ký tự bạn muốn đếm.cell_reference: Ô chứa chuỗi ký tự.
Ví dụ: Nếu ô A1 đang chứa chuỗi ký tự “Gia Sư Thành Tâm”, công thức =LEN(A1) sẽ trả về 16 (bao gồm cả các khoảng trắng).
Ví dụ về hàm LEN
Hàm CONCAT và CONCATENATE: Nối Các Chuỗi Văn Bản
Hàm CONCAT và CONCATENATE là các công thức tính Excel cơ bản được sử dụng để nối hai hay nhiều chuỗi văn bản thành một chuỗi duy nhất.
Hàm CONCAT (có sẵn từ Excel 2016 trở lên, thay thế cho CONCATENATE)
Cú pháp: =CONCAT(text1, [text2], ...)
Trong đó:
text1, text2, ...: Là các chuỗi văn bản, tham chiếu ô hoặc mảng cần nối.
Ví dụ: =CONCAT("Gia", " ", "Sư", " ", "Thành Tâm") sẽ trả về “Gia Sư Thành Tâm”.
Hàm CONCATENATE (tương thích với các phiên bản Excel cũ hơn)
Cú pháp: =CONCATENATE(text1, [text2], ...)
Trong đó:
text1, text2, ...: Là các chuỗi văn bản, tham chiếu ô hoặc mảng cần nối.
Ví dụ: =CONCATENATE("Học", " ", "tập", " ", "hiệu quả") sẽ trả về “Học tập hiệu quả”.
Hàm TRIM: Loại Bỏ Khoảng Trắng Thừa
Hàm TRIM là một công thức tính Excel cơ bản giúp loại bỏ tất cả các khoảng trắng thừa trong một chuỗi văn bản, ngoại trừ các khoảng trắng đơn giữa các từ. Hàm này cực kỳ hữu ích để làm sạch dữ liệu nhập.
Cú pháp: =TRIM(text)
Trong đó:
text: Chuỗi văn bản mà bạn muốn loại bỏ khoảng trắng thừa.
Ví dụ: Nếu ô A1 chứa ” Gia Sư Thành Tâm “, công thức =TRIM(A1) sẽ trả về “Gia Sư Thành Tâm”.
Hàm UPPER, LOWER, PROPER: Thay Đổi Kiểu Chữ
Các hàm UPPER, LOWER, và PROPER là những công thức tính Excel cơ bản dùng để thay đổi định dạng chữ hoa/chữ thường của văn bản, giúp chuẩn hóa dữ liệu một cách nhanh chóng.
Hàm UPPER
Chuyển đổi tất cả các chữ cái trong một chuỗi văn bản thành chữ hoa.
Cú pháp: =UPPER(text)
Ví dụ: =UPPER("gia sư thành tâm") sẽ trả về “GIA SƯ THÀNH TÂM”.
Hàm LOWER
Chuyển đổi tất cả các chữ cái trong một chuỗi văn bản thành chữ thường.
Cú pháp: =LOWER(text)
Ví dụ: =LOWER("GIA SƯ THÀNH TÂM") sẽ trả về “gia sư thành tâm”.
Hàm PROPER
Chuyển đổi chữ cái đầu tiên của mỗi từ trong một chuỗi văn bản thành chữ hoa, và các chữ cái còn lại thành chữ thường.
Cú pháp: =PROPER(text)
Ví dụ: =PROPER("gia sư thành tâm") sẽ trả về “Gia Sư Thành Tâm”.
Hàm Làm Việc Với Ngày Tháng Trong Excel
Làm việc với dữ liệu ngày tháng là một phần quan trọng trong nhiều ứng dụng Excel. Các công thức tính Excel cơ bản này giúp bạn hiển thị ngày giờ hiện tại, tạo ngày tháng từ các thành phần riêng lẻ, hoặc tính toán khoảng thời gian.
Hàm NOW và TODAY: Lấy Thời Gian Hiện Tại
Hàm NOW và TODAY là các công thức tính Excel cơ bản giúp bạn nhanh chóng hiển thị ngày và/hoặc thời gian hiện tại trên hệ thống.
Hàm NOW
Hàm NOW dùng để hiển thị ngày và thời gian hiện tại trên hệ thống của bạn. Giá trị này sẽ tự động cập nhật mỗi khi bạn mở lại trang tính hoặc khi có sự thay đổi trong bảng tính.
Cú pháp: =NOW()
Ví dụ:
=NOW(): Trả về ngày và thời gian hiện tại, ví dụ 15/05/2023 10:30.=NOW()+7: Trả về ngày và thời gian sau 7 ngày kể từ hiện tại.
Hàm TODAY: Lấy Ngày Hiện Tại
Hàm TODAY tương tự NOW nhưng chỉ trả về ngày hiện tại, không bao gồm thời gian.
Cú pháp: =TODAY()
Ví dụ: =TODAY() sẽ trả về 15/05/2023 nếu hôm nay là ngày 15 tháng 5 năm 2023.
Hàm DATE: Tạo Ngày Tháng Từ Thành Phần
Hàm DATE là một công thức tính Excel cơ bản trả về một số thứ tự đại diện cho một ngày cụ thể. Hàm này hữu ích khi bạn có năm, tháng, ngày ở các ô riêng biệt và muốn kết hợp chúng thành một giá trị ngày tháng chuẩn.
Cú pháp: =DATE(Year, Month, Day)
Trong đó:
Year: Chỉ năm.Month: Chỉ tháng (một số nguyên từ 1 đến 12).Day: Chỉ ngày (một số nguyên dương ứng với ngày trong tháng).
Ví dụ: =DATE(2015, 5, 20) sẽ trả về ngày 20 tháng 5 năm 2015.
Hàm TIME: Tạo Thời Gian Từ Thành Phần
Tương tự như DATE, hàm TIME là công thức tính Excel cơ bản tạo ra một giá trị thời gian từ các thành phần giờ, phút, giây.
Cú pháp: =TIME(hour, minute, second)
Trong đó:
hour: Số từ 0 (12:00 AM) đến 23 (11:00 PM) đại diện cho giờ.minute: Số từ 0 đến 59 đại diện cho phút.second: Số từ 0 đến 59 đại diện cho giây.
Ví dụ: =TIME(10, 30, 0) sẽ trả về giá trị thời gian 10:30:00 AM.
Hàm NETWORKDAYS: Đếm Số Ngày Làm Việc Giữa Hai Ngày
Hàm NETWORKDAYS là một công thức tính Excel cơ bản hữu ích để tính số ngày làm việc (không bao gồm cuối tuần và các ngày lễ) giữa hai ngày cho trước.
Cú pháp: =NETWORKDAYS(start_date, end_date, [holidays])
Trong đó:
start_date: Ngày bắt đầu.end_date: Ngày kết thúc.holidays(Tùy chọn): Một phạm vi ô chứa các ngày lễ cần loại trừ khỏi số ngày làm việc.
Ví dụ: Để tính số ngày làm việc giữa ngày 01/01/2023 và 31/01/2023, bạn có thể dùng =NETWORKDAYS("1/1/2023", "31/1/2023").
Hàm DATEDIF: Tính Khoảng Thời Gian Giữa Hai Ngày
Hàm DATEDIF là một công thức tính Excel cơ bản giúp tính khoảng thời gian giữa hai ngày theo nhiều đơn vị khác nhau (năm, tháng, ngày). Hàm này không được hiển thị trong danh sách hàm của Excel nhưng vẫn hoạt động.
Cú pháp: =DATEDIF(start_date, end_date, unit)
Trong đó:
start_date: Ngày bắt đầu.end_date: Ngày kết thúc.unit: Đơn vị muốn tính khoảng thời gian (“Y” cho năm, “M” cho tháng, “D” cho ngày, “YM” cho số tháng còn lại sau khi trừ đi số năm, v.v.).
Ví dụ: =DATEDIF("1/1/2022", "1/1/2023", "Y") sẽ trả về 1 (tức 1 năm).
Hàm Tra Cứu và Tham Chiếu Dữ Liệu
Các hàm tra cứu và tham chiếu dữ liệu là những công thức tính Excel cơ bản cho phép bạn tìm kiếm và trích xuất thông tin từ các bảng dữ liệu lớn. Việc thành thạo các hàm này giúp tự động hóa quá trình nhập liệu và phân tích.
Hàm VLOOKUP: Tra Cứu Dữ Liệu Theo Cột Dọc
Hàm VLOOKUP là một trong những công thức tính Excel cơ bản và được sử dụng rộng rãi nhất khi cần dò tìm dữ liệu trong một bảng theo hàng dọc và trả về dữ liệu tương ứng theo hàng ngang.
Cú pháp: =VLOOKUP(Lookup_value, Table_array, Col_index_num, [Range_lookup])
Trong đó:
Lookup_value: Giá trị cần dò tìm.Table_array: Bảng giới hạn để dò tìm.Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng, tính từ trái qua phải.Range_lookup(Tùy chọn): Xác định kiểu tìm kiếm.TRUEhoặc1(mặc định): Dò tìm tương đối.FALSEhoặc0: Dò tìm chính xác.
Ví dụ: Để xác định mức phụ cấp cho nhân viên dựa trên mã phụ cấp. Tại ô E4, bạn điền công thức: =VLOOKUP(D4, $H$3:$I$8, 2, 0). Công thức này sẽ tìm giá trị trong ô D4 (mã phụ cấp) trong cột đầu tiên của bảng $H$3:$I$8, sau đó trả về giá trị từ cột thứ 2 của bảng đó (mức phụ cấp) khi tìm thấy kết quả khớp chính xác.
Sử dụng hàm VLOOKUP
Hàm INDEX: Lấy Giá Trị Tại Vị Trí Cụ Thể
Hàm INDEX là một công thức tính Excel cơ bản nhưng cực kỳ linh hoạt, cho kết quả trả về là một giá trị hoặc tham chiếu tới một giá trị trong bảng hoặc một phạm vi nhất định dựa trên chỉ số hàng và cột.
Cú pháp: =INDEX(array, row_num, [column_num])
Trong đó:
array: Phạm vi ô hoặc một mảng.row_num: Thứ tự của hàng trong mảng chứa giá trị trả về.column_num(Tùy chọn): Thứ tự của cột trong mảng chứa giá trị trả về.
Ví dụ: Để tìm giá trị của phần tử ở dòng thứ 4, cột thứ 1 trong mảng B4:E13. Bạn nhập công thức: =INDEX(B4:E13, 4, 1). Kết quả sẽ là giá trị tại giao điểm của hàng thứ 4 và cột thứ 1 trong phạm vi đã chọn.
Ví dụ về hàm INDEX
Hàm MATCH: Tìm Vị Trí Tương Đối Của Giá Trị
Hàm MATCH là một công thức tính Excel cơ bản dùng để tìm kiếm, xác định vị trí tương đối của một giá trị trong một mảng hoặc một dải ô. Hàm này thường được kết hợp với INDEX để tạo ra một cặp tra cứu mạnh mẽ hơn VLOOKUP.
Cú pháp: =MATCH(Lookup_value, Lookup_array, [Match_type])
Trong đó:
Lookup_value: Giá trị mà bạn muốn tìm kiếm.Lookup_array: Mảng hoặc dải ô bạn muốn xác định vị trí củaLookup_value.Match_type(Tùy chọn): Xác định kiểu khớp.0: Khớp chính xác.1: Khớp tương đối, tìm giá trị lớn nhất nhưng nhỏ hơn hoặc bằngLookup_value(yêu cầuLookup_arrayphải sắp xếp tăng dần).-1: Khớp tương đối, tìm giá trị nhỏ nhất nhưng lớn hơn hoặc bằngLookup_value(yêu cầuLookup_arrayphải sắp xếp giảm dần).
Ví dụ: Để tìm vị trí của học sinh “Lâm Thiên Trang” trong danh sách tên ở cột A (A2:A9). Tại ô G3 ta thực hiện công thức: =MATCH(G1, A2:A9, 0). Kết quả sẽ trả về số thứ tự (vị trí) của “Lâm Thiên Trang” trong danh sách đó.
Ví dụ về hàm MATCH
Hàm HLOOKUP: Tra Cứu Dữ Liệu Theo Hàng Ngang
Hàm HLOOKUP là công thức tính Excel cơ bản giúp tìm kiếm một giá trị trên dòng đầu tiên của bảng tham chiếu và cho kết quả tương ứng trong dòng chỉ định. Đây là phiên bản tìm kiếm ngang của VLOOKUP.
Cú pháp: =HLOOKUP(Lookup_value, Table_array, Row_index_Num, [Range_lookup])
Trong đó:
Lookup_value: Giá trị đối chiếu với dòng đầu tiên của bảng tham chiếu.Table_array: Địa chỉ tuyệt đối của bảng tham chiếu (không lấy cột tiêu đề).Row_index_Num: Số thứ tự dòng chứa giá trị cần lấy của bảng tham chiếu (tính từ trên xuống, bắt đầu từ 1).Range_lookup(Tùy chọn): Cách tìm kiếm trên bảng tham chiếu (TRUEhoặc1cho tương đối,FALSEhoặc0cho chính xác).
Ví dụ: Để xếp loại học sinh trong Bảng 1 (B3:D8) với dữ liệu tham chiếu ở Bảng 2 (B11:F12). Tại D4 ta có: =HLOOKUP(C4, $B$11:$F$12, 2, 1). Hàm này sẽ dò tìm điểm số ở ô C4 trong Bảng 2 từ trái qua phải, sau đó trả về kết quả xếp loại tương ứng nằm ở hàng 2.
Ví dụ về hàm HLOOKUP
Hàm CHOOSE: Chọn Giá Trị Từ Danh Sách
Hàm CHOOSE trong Excel là một công thức tính Excel cơ bản cho phép bạn chọn một giá trị từ một danh sách dựa trên một giá trị chỉ mục được cung cấp. Hàm này hữu ích để trích xuất dữ liệu cụ thể từ bảng hoặc danh sách dựa trên các điều kiện hoặc lựa chọn do người dùng cung cấp.
Cú pháp: =CHOOSE(index_num, value1, [value2], ...)
Trong đó:
index_num(Bắt buộc): Một số nguyên chỉ ra vị trí của giá trị bạn muốn chọn trong danh sáchvalue. Giá trị này phải nằm trong khoảng từ 1 đến số lượng giá trị trong danh sách.value1, value2, ...(Bắt buộc): Danh sách các giá trị mà bạn muốn chọn từ. Danh sách này có thể bao gồm các giá trị dạng số, văn bản, giá trị logic hoặc tham chiếu ô.
Ví dụ: Nếu =CHOOSE(2, "Apple", "Banana", "Cherry") sẽ trả về “Banana”.
Tổng Hợp Ứng Dụng VLOOKUP, HLOOKUP, INDEX/MATCH
Việc kết hợp và hiểu rõ các công thức tính Excel cơ bản như VLOOKUP, HLOOKUP, INDEX và MATCH mở ra nhiều khả năng xử lý dữ liệu phức tạp. VLOOKUP và HLOOKUP phù hợp cho các tra cứu đơn giản, một chiều. Tuy nhiên, khi cần linh hoạt hơn, đặc biệt là tra cứu hai chiều hoặc khi cột tìm kiếm không phải là cột đầu tiên, cặp INDEX/MATCH trở nên ưu việt hơn. Kết hợp các hàm này giúp bạn tự động điền thông tin, đối chiếu dữ liệu giữa các bảng và tạo báo cáo động, giảm thiểu lỗi thủ công và tiết kiệm thời gian đáng kể.
Hàm Thống Kê Nâng Cao (Với Điều Kiện)
Ngoài các hàm COUNTIF và SUMIF đơn lẻ, Excel còn cung cấp các phiên bản nâng cao với khả năng xử lý nhiều điều kiện. Đây là những công thức tính Excel cơ bản giúp bạn thực hiện thống kê phức tạp hơn, phân tích dữ liệu chi tiết hơn.
Hàm COUNTIFS: Đếm Với Nhiều Điều Kiện
Hàm COUNTIFS là một công thức tính Excel cơ bản mở rộng của COUNTIF, cho phép bạn đếm số lượng ô thỏa mãn nhiều điều kiện cùng lúc.
Cú pháp: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Trong đó:
criteria_range1, criteria_range2, ...: Các phạm vi ô cần kiểm tra.criteria1, criteria2, ...: Các điều kiện tương ứng cho từng phạm vi.
Ví dụ: Để đếm số lượng học sinh “Giỏi” ở lớp “12A”, bạn dùng =COUNTIFS(B2:B10, "12A", C2:C10, "Giỏi").
Hàm SUMIFS: Tính Tổng Với Nhiều Điều Kiện
Hàm SUMIFS là một công thức tính Excel cơ bản mở rộng của SUMIF, cho phép bạn tính tổng các giá trị thỏa mãn nhiều điều kiện cùng lúc.
Cú pháp: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Trong đó:
sum_range: Phạm vi các ô cần tính tổng.criteria_range1, criteria_range2, ...: Các phạm vi ô cần kiểm tra điều kiện.criteria1, criteria2, ...: Các điều kiện tương ứng cho từng phạm vi.
Ví dụ: Để tính tổng doanh thu của sản phẩm “A” bán được trong tháng “1”, bạn dùng =SUMIFS(D2:D10, A2:A10, "Sản phẩm A", B2:B10, "Tháng 1").
Hàm AVERAGEIFS: Tính Trung Bình Với Nhiều Điều Kiện
Hàm AVERAGEIFS là một công thức tính Excel cơ bản mở rộng của AVERAGE, cho phép bạn tính trung bình cộng của các giá trị thỏa mãn nhiều điều kiện cùng lúc.
Cú pháp: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Trong đó:
average_range: Phạm vi các ô cần tính trung bình.criteria_range1, criteria_range2, ...: Các phạm vi ô cần kiểm tra điều kiện.criteria1, criteria2, ...: Các điều kiện tương ứng cho từng phạm vi.
Ví dụ: Để tính điểm trung bình của các sinh viên “Nữ” thuộc khoa “Kinh tế”, bạn dùng =AVERAGEIFS(D2:D10, B2:B10, "Nữ", C2:C10, "Kinh tế").
Câu Hỏi Thường Gặp Về Công Thức Tính Excel Cơ Bản (FAQs)
1. Hàm Excel cơ bản nhất mà người mới nên học là gì?
Hàm SUM, AVERAGE, MIN, MAX, và COUNT là những công thức tính Excel cơ bản nhất mà mọi người mới bắt đầu nên học. Chúng là nền tảng cho hầu hết các phép tính và phân tích dữ liệu.
2. Làm thế nào để phân biệt giữa COUNT và COUNTA?
Hàm COUNT chỉ đếm các ô chứa giá trị số. Trong khi đó, COUNTA đếm tất cả các ô không trống, bất kể chúng chứa số, văn bản, giá trị logic hay lỗi.
3. Khi nào nên dùng hàm IF?
Hàm IF được sử dụng khi bạn cần kiểm tra một điều kiện logic và trả về một trong hai kết quả tùy thuộc vào việc điều kiện đó đúng hay sai. Ví dụ, để phân loại “Đạt” hoặc “Không Đạt” dựa trên điểm số.
4. Hàm VLOOKUP và HLOOKUP khác nhau như thế nào?
VLOOKUP tìm kiếm giá trị theo cột dọc và trả về kết quả tương ứng theo hàng ngang. HLOOKUP tìm kiếm giá trị theo hàng ngang và trả về kết quả tương ứng theo cột dọc.
5. Có thể kết hợp nhiều hàm Excel với nhau không?
Hoàn toàn có thể. Việc kết hợp các công thức tính Excel cơ bản là một kỹ thuật mạnh mẽ giúp tạo ra các công thức phức tạp để giải quyết các bài toán dữ liệu đa dạng. Ví dụ, kết hợp INDEX và MATCH để tra cứu hai chiều.
6. Hàm DATEDIF dùng để làm gì?
Hàm DATEDIF giúp tính khoảng thời gian giữa hai ngày theo các đơn vị như năm, tháng hoặc ngày. Nó thường được sử dụng để tính tuổi, thời gian làm việc hoặc thời hạn còn lại.
7. Tại sao công thức Excel của tôi lại hiển thị lỗi #VALUE! hoặc #N/A?
Lỗi #VALUE! thường xuất hiện khi công thức chứa các đối số không hợp lệ (ví dụ, phép tính số học với văn bản). Lỗi #N/A thường xảy ra khi các hàm tra cứu (như VLOOKUP, MATCH) không tìm thấy giá trị cần tìm.
8. Nên dùng CONCAT hay CONCATENATE để nối chuỗi?
Nếu bạn đang sử dụng Excel 2016 trở lên, nên ưu tiên dùng CONCAT vì nó linh hoạt hơn và là phiên bản hiện đại hơn của CONCATENATE. CONCATENATE vẫn hoạt động nhưng có thể bị loại bỏ trong các phiên bản tương lai.
9. Hàm TRIM có quan trọng không?
Hàm TRIM rất quan trọng trong việc làm sạch dữ liệu. Nó giúp loại bỏ các khoảng trắng thừa ở đầu, cuối chuỗi và giữa các từ, đảm bảo dữ liệu được chuẩn hóa và tránh lỗi khi thực hiện các phép tính hoặc so sánh.
10. Làm cách nào để luyện tập các công thức tính Excel cơ bản một cách hiệu quả?
Cách hiệu quả nhất là thực hành thường xuyên với các bài tập thực tế, áp dụng các công thức tính Excel cơ bản vào các bảng tính cá nhân hoặc dự án nhỏ. Bạn cũng có thể tìm kiếm các khóa học trực tuyến hoặc tài liệu hướng dẫn chi tiết để nâng cao kỹ năng.
Nắm vững các công thức tính Excel cơ bản không chỉ giúp bạn làm việc hiệu quả hơn mà còn mở ra nhiều cơ hội trong phân tích dữ liệu và quản lý thông tin. Hãy kiên trì luyện tập và ứng dụng những kiến thức này vào công việc hàng ngày. Gia Sư Thành Tâm tin rằng bạn sẽ sớm thành thạo công cụ tuyệt vời này.
