6 Công Thức Excel Thiết Yếu Giúp Tăng Hiệu Quả Công Việc

1. IF và IFS

IF là một trong những hàm logic được sử dụng rộng rãi nhất trong Excel. Nó kiểm tra một điều kiện và đánh giá kết quả là TRUE hoặc FALSE. Một công thức IF có hai kết quả có thể, một khi điều kiện là TRUE và một khi điều kiện là FALSE. Cú pháp của nó là:

=IF(test, value_if_true, value_if_false)

Đối với các tình huống phức tạp hơn, Excel còn cung cấp hàm IFS, cho phép kiểm tra nhiều điều kiện trong một công thức duy nhất mà không cần lồng ghép/cú pháp phức tạp. Cú pháp của IFS là:

=IFS(condition1, value1, condition2, value2,...)

Hai hàm này có thể tiết kiệm rất nhiều thời gian trong môi trường văn phòng. Một chuyên viên nhân sự có thể sử dụng hàm IF để tự động hóa việc đánh giá hiệu suất nhân viên. Một công thức IF đơn giản như sau có thể được sử dụng để phân loại nhân viên dựa trên điểm đánh giá hiệu suất của họ:

=IF(B1>80, "Vượt Kỳ Vọng", "Đạt/Không Đạt Kỳ Vọng")

Bảng tính đánh giá hiệu suất

Hoặc, đội ngũ bán hàng có thể sử dụng câu lệnh IFS để phân loại khách hàng dựa trên lợi nhuận của họ:

=IFS(B2>100000, "Bạch Kim", B2>50000, "Vàng", B2>10000, "Bạc")

Bảng tính lợi nhuận khách hàng

Một câu lệnh IFS có thể chứa tới 127 điều kiện và trả về giá trị tương ứng với điều kiện TRUE đầu tiên.

2. SUM và SUMIFS

Như tên gọi, hàm SUM được sử dụng để cộng các số lại với nhau. Cú pháp của nó là:

=SUM(num1, num2, ...)

Nó có thể nhận các số, tham chiếu ô và phạm vi ô làm tham số. Bạn có thể sử dụng hàm SUM để nhanh chóng tính tổng cho các cột hoặc hàng dữ liệu. Trong bảng tính ngân sách, bạn có thể sử dụng =SUM(B2:B10) để tổng hợp chi phí trong một khoảng thời gian cụ thể.

SUMIFS mở rộng chức năng này bằng cách cho phép người dùng tổng hợp dữ liệu dựa trên nhiều tiêu chí. Cú pháp của nó là:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Trong thực tế, một công thức SUMIFS có thể được sử dụng để tổng hợp chỉ doanh số từ một khu vực cụ thể, cho một sản phẩm cụ thể hoặc bởi một nhân viên bán hàng cụ thể. SUM và SUMIFS chỉ là hai trong số các hàm SUM trong Excel.

Bảng tính báo cáo doanh số

Bạn có thể chỉ định tới 127 cặp phạm vi/tiêu chí trong một câu lệnh SUMIFS.

3. AGGREGATE

Hàm AGGREGATE kết hợp các hàm tổng hợp số khác nhau thành một. Nó thực hiện các phép tính như SUM, COUNT, MAX và MIN trong khi bỏ qua các lỗi, ô ẩn và thậm chí cả các hàm AGGREGATE và SUBTOTAL khác. Cú pháp của nó là:

=AGGREGATE(function_num, options, ref, [k])

Trong đó function_num đại diện cho phép tính toán cần thực hiện, options xác định những gì cần bỏ qua (ví dụ: hàng ẩn hoặc lỗi), ref là phạm vi ô hoặc mảng ô cần tổng hợp, và k là tham số tùy chọn cho một số hàm.

AGGREGATE đặc biệt hữu ích trong các tập dữ liệu lớn, nơi các hàm tiêu chuẩn như SUM hoặc AVERAGE có thể bị cản trở bởi dữ liệu ẩn hoặc lỗi.

Trong ví dụ dưới đây, AGGREGATE được áp dụng cho một bảng có lỗi để tính tổng, trung bình và mức lương cao nhất mà nhân viên nhận được. Để tính mức lương cao nhất, tôi đã sử dụng công thức:

=AGGREGATE(14, 2, D6:D14, 1)

Bảng tính lương nhân viên

AGGREGATE sẽ không bỏ qua các hàng ẩn, các tổng phụ lồng nhau hoặc các tổng hợp lồng nhau khi đối số mảng bao gồm một phép tính, chẳng hạn như một phép toán logic để quyết định ô nào sẽ bao gồm và loại trừ (ví dụ C1:C50*(C1:C50>0)).

4. TRIM

Hàm TRIM thực hiện việc làm sạch dữ liệu và loại bỏ các khoảng trắng thừa từ văn bản. Điều này đặc biệt hữu ích cho dữ liệu thô được nhập từ nguồn bên ngoài. Nó loại bỏ tất cả các khoảng trắng thừa từ văn bản, bao gồm khoảng trắng đầu, cuối và giữa.

Cú pháp của nó rất đơn giản:

=TRIM(text)

Nó có thể hữu ích khi chuẩn bị danh sách gửi thư, làm sạch mô tả sản phẩm và tổ chức thông tin bán hàng.

Ví dụ, bạn có thể sử dụng TRIM để làm sạch khoảng trắng đầu, cuối và giữa trong danh sách tên, như trong hình chụp màn hình dưới đây:

Dữ liệu thô và sạch sử dụng hàm TRIM

Để loại bỏ tất cả các ký tự không in được, tức là các ký tự ngắt dòng và ký tự khoảng trắng không ngắt, bạn có thể sử dụng hàm TRIM kết hợp với CLEAN.

5. MIN và MAX

Các hàm MIN và MAX rất dễ sử dụng và khá đơn giản. Chúng nhận một loạt số và tìm giá trị nhỏ nhất hoặc lớn nhất trong tập hợp đó. Chúng có thể được sử dụng thay thế cho AutoSum của Excel khi các ô cần phân tích nằm rải rác trên bảng tính.

Cú pháp của MIN là:

=MIN(num1, num2, ...)

và của MAX là:

=MAX(num1, num2, ...)

Bạn có thể nhập số, tham chiếu ô hoặc phạm vi cho cả hai hàm.

Các hàm này đặc biệt hữu ích cho việc phân tích dữ liệu, chẳng hạn như xác định doanh số cao nhất và thấp nhất trong báo cáo hoặc theo dõi sản phẩm hoạt động tốt nhất và kém nhất trong danh mục. Chúng là hai trong số các hàm Excel thiết yếu cho người mới bắt đầu.

Phân tích báo cáo doanh số sử dụng hàm MAX và MIN

MIN và MAX cũng có thể được kết hợp với các hàm khác, như IF hoặc SUMIFS, để báo cáo phức tạp hơn.

6. VLOOKUP và HLOOKUP

Các hàm VLOOKUP và HLOOKUP được sử dụng để tra cứu giá trị trong bảng Excel dựa trên một giá trị chỉ mục đã định trước.

VLOOKUP (tra cứu theo chiều dọc) tìm kiếm giá trị tra cứu trong cột đầu tiên của một phạm vi đã xác định và trả về giá trị tương ứng trong một cột khác. HLOOKUP (tra cứu theo chiều ngang) tìm kiếm giá trị tra cứu trong hàng đầu tiên của bảng và trả về giá trị từ một hàng khác trong cùng cột.

Cả hai công thức đều có cú pháp tương tự:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Trong đó lookup_value là số hoặc văn bản bạn muốn tìm kiếm, table_array là phạm vi ô chứa lookup_value và giá trị cần trả về, index_num là số cột hoặc hàng chứa giá trị trả về, và range_lookup là tham số tùy chọn xác định liệu hàm trả về kết quả gần đúng hay chính xác.

Chúng rất hữu ích khi làm việc với các tập dữ liệu lớn có hàng ngàn mục dữ liệu. Ví dụ, tôi có thể tìm kiếm trong hệ thống kho để tìm giá của sản phẩm có mã sản phẩm 223 bằng công thức:

=VLOOKUP(223,A2:F20, 6, TRUE)

Sử dụng hàm VLOOKUP trong Excel

HLOOKUP có thể được sử dụng để tìm dữ liệu trong các bảng được cấu trúc theo hàng, chẳng hạn như doanh thu cho một tháng cụ thể trong báo cáo tài chính. Sử dụng công thức sau, tôi đã có thể xác định doanh thu đạt được trong tháng Tám.

=HLOOKUP("Doanh thu", A1:D13, 9, TRUE)

Sử dụng hàm HLOOKUP trong Excel

Nếu bạn có bộ Microsoft 365, bạn có thể sử dụng hàm XLOOKUP thay thế cho cả tra cứu theo chiều dọc và chiều ngang.

Từ các phép tính đơn giản như MIN và SUM đến các hàm nâng cao như VLOOKUP và HLOOKUP, những công thức Excel này là thiết yếu cho bất kỳ nhân viên văn phòng nào muốn tăng hiệu quả và nổi bật.