7 hàm Excel bạn nên học ngoài SUM và VLOOKUP

Khi viết công thức trong Excel, SUM và VLOOKUP là những hàm cơ bản cho người mới bắt đầu. Tuy nhiên, vì Excel có hơn 400 hàm, việc nắm rõ hơn về những hàm cực kỳ hữu ích sẽ rất có ích, đặc biệt là khi bạn muốn trở thành người dùng trung và cao cấp hơn nữa. Hãy cùng khám phá những hàm sẽ giúp đơn giản hóa quy trình làm việc Excel của bạn hơn nữa trong bài viết sau đây!

Hàm COUNT cung cấp một cách tốt hơn để đếm ô

Nếu chúng chứa dữ liệu số

Hàm COUNT đếm tất cả các ô có giá trị số và trả về kết quả. Điều này giúp bạn tiết kiệm công sức đếm thủ công, vốn có thể rất tẻ nhạt và tốn thời gian trong các tập dữ liệu lớn.

=COUNT(value1, value2, ... value_n)

Công thức ví dụ dưới đây sẽ trả về kết quả 10 nếu tất cả các ô đều chứa số.

=COUNT(A1:A10)

Hàm AVERAGE giúp đơn giản hóa việc tính toán giá trị trung bình

Phù hợp với mọi phạm vi

Đúng như tên gọi, hàm AVERAGE tính giá trị trung bình (trung bình cộng) bằng cách cộng tất cả các giá trị số trong một phạm vi và chia cho tổng số của chúng.

=AVERAGE(value1, value2, ... value_n)

Ví dụ, đây là công thức để tìm giá trị trung bình của những giá trị trong các ô từ A1 đến A10:

=AVERAGE(A1:A10)

Hàm MIN tìm giá trị nhỏ nhất trong một phạm vi

Ngoài ra còn có hàm MAX

Giả sử bạn có một tập dữ liệu lớn và cần tìm giá trị nhỏ nhất. Hàm MIN là cách nhanh nhất để thực hiện việc này.

=MIN(value1, value2, ... value_n)

Đây là ví dụ về cách thức hoạt động của hàm:

=MIN(A1:A10)

Mặt khác, nếu bạn muốn tìm giá trị lớn nhất trong một phạm vi, hãy sử dụng hàm MAX.

=MIN(value1, value2, ... value_n)

Hàm SUMIF là phiên bản thông minh hơn của hàm SUM

Cần một số logic điều kiện

Hàm SUM chỉ cộng bất kỳ giá trị số nào bạn cung cấp cho nó. Tuy nhiên, hàm SUMIF chỉ cộng các giá trị đó nếu chúng đáp ứng một tiêu chí cụ thể. Về cơ bản, hàm này kết hợp hàm SUM và hàm IF trong Excel, do đó bạn không cần phải sắp xếp hoặc lọc dữ liệu thủ công trước khi tính tổng.

=SUMIF(range_to_evaluate, criteria, [range_to_sum])

Ở đây, range_to_evaluate là các ô có giá trị mà hàm phải kiểm tra theo điều kiện được chỉ định trong tham số criteria. Tham số tùy chọn, range_to_sum, là các ô cụ thể trong phạm vi bạn muốn tính tổng. Nếu bạn bỏ qua tham số này, nó sẽ chỉ tính tổng tất cả các ô trong phạm vi đáp ứng điều kiện.

Dưới đây là ví dụ về hàm đang hoạt động. Công thức này chỉ tính tổng các giá trị trong vùng A1:A10 nếu chúng lớn hơn 50.

=SUMIF(A1:A10, ">50")

Cũng có các biến thể của hàm SUMIF mà bạn có thể xem xét, bao gồm COUNTIF, AVERAGEIF, MINIF và MAXIF. Một số phiên bản cho phép bạn chỉ định nhiều tiêu chí, chẳng hạn như SUMIFS, COUNTIFS và AVERAGEIFS.

Hàm SUBTOTAL cung cấp khả năng tổng hợp gọn gàng hơn

Dữ liệu không cần thiết sẽ không làm hỏng các phép tính của bạn

SUBTOTAL được các chuyên gia Excel sử dụng vì nó loại trừ thông minh dữ liệu đã lọc hoặc ẩn, trong khi SUM và những hàm tương tự bao gồm tất cả. Điều này khiến nó trở thành lựa chọn lý tưởng khi bạn làm việc với các tập dữ liệu động, đặc biệt là khi việc ẩn và lọc đảm bảo độ chính xác.

=SUBTOTAL(function_code, range)

Tham số function_code là một số từ 1-11 hoặc 101-111, chỉ định hàm cần sử dụng (ví dụ: 1 cho AVERAGE, 2 cho COUNT, 9 cho SUM). Các số từ 1-11 sẽ bao gồm tất cả dữ liệu, trong khi 101-111 sẽ loại trừ các hàng ẩn. Tham số range là các ô cần tính tổng phụ.

Ví dụ dưới đây sẽ tính tổng khoảng A1:A10 nhưng loại trừ các hàng ẩn:

=SUBTOTAL(109, A1:A10)

Hàm XLOOKUP khắc phục nhược điểm của VLOOKUP

Cách tra cứu hiện đại

Nếu đã quen thuộc với VLOOKUP, có lẽ bạn đã biết nhược điểm lớn nhất của nó - nó chỉ có thể tìm kiếm từ trái sang phải và yêu cầu cột tra cứu phải nằm bên trái cột trả về. XLOOKUP mạnh mẽ và linh hoạt hơn, cho phép bạn tìm kiếm theo bất kỳ hướng nào. Bạn thậm chí không cần phải sắp xếp các cột.

=XLOOKUP(lookup_value, lookup_range, return_range)

Trong đó, lookup_value là giá trị bạn đang tìm kiếm, lookup_range là vị trí tìm kiếm giá trị, và return_range là giá trị trả về khi tìm thấy giá trị. Ngoài ra, hãy nhớ rằng đây là phiên bản đơn giản của XLOOKUP, với phiên bản đầy đủ bao gồm xử lý lỗi như một đối số tùy chọn.

Đây là ví dụ trong đó phạm vi A2:A5 chứa ID khách hàng và phạm vi B2:B5 chứa tên khách hàng. Sử dụng XLOOKUP để tìm tên khách hàng có ID được tìm thấy trong ô D1.

=XLOOKUP(D1, A2:A5, B2:B5)

Hàm INDEX/MATCH là tổ hợp tra cứu cổ điển

Trước khi XLOOKUP xuất hiện

XLOOKUP không khả dụng trong các phiên bản Excel cũ hơn 2021 và những bộ ứng dụng office khác như LibreOffice hoặc OnlyOffice. Vì vậy, nếu muốn sử dụng XLOOKUP linh hoạt trong các trường hợp này, bạn sẽ cần kết hợp các hàm INDEX và MATCH. Hơn nữa, sự kết hợp này giúp bạn kiểm soát tốt hơn từng bước tra cứu, mặc dù nó không có khả năng xử lý lỗi như XLOOKUP.

=INDEX(return_range, MATCH(lookup_value, lookup_range, match_type))

Trong đó, return_range là phạm vi các ô chứa giá trị bạn muốn lấy, lookup_value là giá trị bạn muốn tra cứu và lookup_range là phạm vi các ô mà bạn muốn tìm kiếm giá trị tra cứu. Tham số match_type chấp nhận các giá trị sau: 0 cho kết quả khớp chính xác, 1 cho kết quả nhỏ hơn và -1 cho kết quả lớn hơn.

Tiếp tục với ví dụ XLOOKUP ở phần trước, phiên bản INDEX MATCH sẽ là:

=INDEX(B2:B5, MATCH(D1, A2:A5, 0))

Như đã đề cập trước đó, bạn có quyền kiểm soát ở đây. Ví dụ, bạn có thể sử dụng hàm XMATCH thay vì hàm MATCH cho các tra cứu nâng cao hơn. Một số người thậm chí còn sử dụng hàm FILTER nếu họ không muốn lọc và sắp xếp thủ công trong quá trình tra cứu.