Hàm điều kiện trong bảng tính

Khi học hầu như bất kỳ ngôn ngữ lập trình nào, điều đầu tiên xuất hiện là các câu lệnh điều kiện. Đó là các câu lệnh IF-ELSEIF-ELSE cổ điển, cho phép chương trình thực hiện các thao tác khác nhau dựa trên các điều kiện logic. Tuy nhiên, đây có thể không phải là điều đầu tiên bạn nghĩ đến trong Excel. Dẫu vậy, chúng có ở đó và rất quan trọng khi bạn làm các báo cáo Excel có mức độ phức tạp nhất định. Trong hướng dẫn này, chúng ta sẽ tìm hiểu cấu trúc câu lệnh IF-ELSE trong Excel, cũng như một số hàm nâng cao như COUNTIF() rất hữu ích trong những tình huống nhất định.

Câu lệnh IF cơ bản

Để bắt đầu, hãy điểm qua cách viết một câu lệnh IF cơ bản trong Excel. Cú pháp như sau:

IF(condition, value_if_true, value_if_false)

Trong đó:

• condition: giá trị hoặc phép toán logic cho kết quả TRUE hoặc FALSE
• value_if_true: Giá trị trả về nếu điều kiện là TRUE
• value_if_false: Giá trị trả về nếu điều kiện là FALSE

Vậy thì, hãy bắt đầu với một ví dụ đơn giản về cách dùng điều kiện cơ bản với một bảng minh họa:

Đơn giản đúng không? Với những bạn quen thuộc với R, có lẽ bạn sẽ nhận ra cú pháp này về cơ bản giống với hàm ifelse(). Tuy nhiên, có một vài điểm khác biệt. Chẳng hạn, các toán tử logic. Trong ví dụ đơn giản này, tôi đã dùng toán tử logic ">>", là cách chuẩn để chỉ lớn hơn. Dẫu vậy, một số toán tử khác không hoàn toàn chuẩn như vậy. Chẳng hạn, toán tử khác được viết là "<>"

Danh sách đầy đủ các toán tử logic dùng trong Excel như sau:

OR(), AND() và NOT()

Các toán tử so sánh không phải là thành phần duy nhất của câu lệnh điều kiện trong Excel có thể khác với những gì bạn dùng trong Python, R hoặc Matlab. Thực tế, cách bạn định nghĩa các phép toán boolean như OR, AND và NOT là khác. Ví dụ, trong Excel, bạn sẽ viết một biểu thức OR như sau:

IF(OR(value_1 = k, value_2 = y), value_if_true, value_if_false)

Để xem tất cả các toán tử boolean hoạt động trong Excel, hãy xem ví dụ dưới đây:

Lồng nhiều câu lệnh IF

Cũng như bạn có thể lồng các câu lệnh IF-ELSEIF-ELSE trong Python, R hoặc Matlab, bạn cũng có thể làm vậy trong Excel. Cách làm đơn giản là thêm một câu lệnh IF khác bên trong một câu lệnh IF trước đó khi câu lệnh này đánh giá là TRUE hoặc FALSE. Điều này cho phép bạn kiểm tra nhiều điều kiện hơn và trả về nhiều kết quả hơn dựa trên các điều kiện đó. Excel có thể lồng tối đa 64 câu lệnh IF. Tuy nhiên, tôi khuyên bạn không nên lồng quá nhiều IF vì bạn cần cẩn trọng với logic, và việc gỡ lỗi hoặc chỉnh sửa sau này có thể trở nên rất rườm rà.

Với điều đó, đây là một ví dụ đơn giản về IF lồng nhau ngay trong Excel:

COUNTIF() và COUNTIFS()

Bên cạnh các câu lệnh IF truyền thống, Excel có những hàm khác có thể thực hiện thao tác dựa trên một tập điều kiện cho trước. Ở phần này, tôi sẽ tập trung vào các hàm đếm có điều kiện, cụ thể là COUNTIF() và COUNTIFS().

COUNTIF() cho phép người dùng đếm các ô đáp ứng một tiêu chí. Cú pháp như sau:

COUNTIF(cell_range_to_count, criteria)

Trong đó:

• cell_range_to_count: Phạm vi ô cụ thể mà bạn muốn đếm
• criteria: Tiêu chí quyết định ô nào sẽ được đếm. Chúng có thể rất đa dạng, ví dụ:
  • COUNTIF(A1:A10, 10) - Đếm có bao nhiêu ô trong phạm vi từ A1 đến A10 bằng 10
  • COUNTIF(A1:A10, B5) - Đếm có bao nhiêu ô trong phạm vi từ A1 đến A10 bằng giá trị tại ô B5
  • COUNTIF(A1:A10, "Gandalf") - Đếm có bao nhiêu ô trong phạm vi từ A1 đến A10 bằng "Gandalf"
  • COUNTIF(A1:A10, ">20") - Đếm có bao nhiêu ô trong phạm vi từ A1 đến A10 lớn hơn 20
  • COUNTIF(A1:A10, "<>Gandalf") - Đếm có bao nhiêu ô trong phạm vi từ A1 đến A10 khác "Gandalf"

COUNTIFS, như bạn có thể đoán, khác COUNTIF ở chỗ nó cho phép người dùng đếm các ô đáp ứng nhiều tiêu chí. Cú pháp như sau:

COUNTIFS(cell_range_to_count_1, criteria_1, cell_range_to_count_2, criteria_2,...cell_range_to_count_n, criteria_n)

Lưu ý quan trọng: với mỗi phạm vi bổ sung mà bạn chỉ định trong COUNTIFS, bạn phải có cùng số hàng và cột như phạm vi ban đầu (cell_range_to_count_1), nếu không sẽ phát sinh lỗi.

Hai hàm này rất tuyệt để dùng trong báo cáo kinh doanh. Một ví dụ điển hình với tôi là cách chúng giúp đếm số cuộc hẹn mà các đại diện bán hàng thực hiện mỗi tháng và họ đang ở đâu trong phễu bán hàng.

Trong các video dưới đây, tôi sẽ minh họa cách hai hàm này hoạt động. Trước tiên, hãy bắt đầu bằng việc chỉ đếm tổng số cuộc hẹn của một nhóm nhân viên bán hàng hư cấu:

Quá dễ phải không? Bạn vừa tổng hợp số cuộc hẹn mà mỗi nhân viên bán hàng hư cấu của chúng ta có được. Về lý thuyết, bạn cũng có thể làm điều này chỉ với các câu lệnh IF cơ bản, nhưng sẽ tốn nhiều công sức hơn để đạt cùng kết quả.

Bây giờ, giả sử bạn cũng muốn tổng hợp các cuộc hẹn mà mỗi nhân viên này có theo từng tháng. Trong trường hợp này, COUNTIFS() là câu trả lời; bạn có thể xem nó hoạt động trong video bên dưới:

Lưu ý: Bạn có thể nhận thấy rằng trong video trước, tôi đã phủ phạm vi muốn đếm bằng các dấu $. Điều này nhằm cố định phạm vi và ngăn nó thay đổi khi tôi kéo công thức xuống.

Vậy là xong, chúng ta đã tổng hợp các cuộc hẹn của những nhân viên bán hàng mẫu theo tháng mà không tốn nhiều công sức. Cũng đáng nói rằng bạn có thể làm việc này theo cách "hiệu quả" hơn nếu khéo dùng dấu $ để cố định một số ô và phạm vi khi kéo công thức (tức là tôi có thể chỉ cần viết công thức ở F2 rồi kéo sang các ô còn lại). Tuy nhiên, tôi muốn viết rõ ràng các công thức COUNTIFS cho tháng 1 và tháng 2 để dễ theo dõi. Nếu tò mò, tôi khuyến khích bạn thử viết lại công thức COUNTIFS() ở F2 sao cho có thể kéo sang các ô còn lại mà vẫn ra cùng kết quả. Đây có thể là bài tập hữu ích vì việc cố định phạm vi và ô để kéo công thức rất thuận tiện khi báo cáo Excel của bạn trở nên lớn. Đây là bảng dữ liệu dùng trong hướng dẫn này.

SUMIF() và SUMIFS()

Chúng ta đã thấy cách đếm các ô đáp ứng một tiêu chí hoặc nhiều tiêu chí với COUNTIF() và COUNTIFS(), nhưng nếu bạn cần cộng các ô thay vì chỉ đếm thì sao? Hãy dùng SUMIF() và SUMIFS(). Những hàm này đặc biệt hữu ích khi bạn phải cộng doanh thu mà một nhân viên bán hàng tạo ra tổng cộng hoặc trong một tháng nhất định, chẳng hạn. Cú pháp của cả hai hàm như sau:

SUMIF(criteria_range, criteria, (optional) sum_range)

SUMIFS(sum_range, criteria_range_1, criteria_1, criterion_range_2, criteria_2...criteria_range_n, criteria_n)

Trong đó:

• criteria_range: Phạm vi ô dùng để kiểm tra tiêu chí.
• criteria: Tiêu chí điều kiện dùng để xác định những ô sẽ được cộng.
• sum_range: Các ô sẽ được cộng lại. Nếu không cung cấp trong COUNTIF(), các ô trong criteria_range sẽ được cộng.

Giờ thì không chần chờ nữa, hãy đến với ví dụ dùng cả SUMIF() và SUMIFS() để tính tổng doanh thu và doanh thu theo tháng do các đại diện bán hàng giả định của chúng ta tạo ra:

Điều thú vị là, trong trường hợp SUMIFS(), sum_range là đối số bắt buộc và là đối số đầu tiên, nhưng lại là tùy chọn và là đối số cuối cùng trong SUMIF(). Điều này có thể gây nhầm lẫn khi bạn dùng cả hai công thức, như bạn thấy khi tôi gõ hàm SUMIFS cho tháng 2. Vì vậy, tôi khuyên bạn nên thận trọng khi làm việc với cả hai công thức. Hãy luôn kiểm tra rằng bạn đang cộng đúng phạm vi ô.

IFERROR()

Với những bạn biết ngôn ngữ lập trình/kịch bản, bạn sẽ biết về các khối try/catch để xử lý lỗi. Trong Excel, chúng ta có hàm IFERROR() để làm đúng điều đó. Hàm IFERROR cho phép người dùng "bắt" các lỗi như #N/A, #VALUE! hoặc #REF!, và cung cấp đầu ra có ý nghĩa hơn. Cú pháp của hàm IRERROR như sau:

IFERROR(value, value_if_error)

Trong đó:

• value: Giá trị hoặc công thức cần kiểm tra lỗi
• value_if_error: Giá trị xuất ra khi hàm gặp lỗi.

Hãy xem nó hoạt động như thế nào trong video sau:

Khi làm việc với báo cáo doanh số, thường là thực hành tốt nếu bao bọc một số phép tính bằng hàm IFERROR. Đôi khi sẽ có tình huống nhân viên mới có rất ít cuộc hẹn, cơ hội đủ điều kiện hoặc thương vụ chốt, có thể gây ra phép chia cho 0 khi tính những thứ như phần trăm cuộc hẹn chuyển thành cơ hội đủ điều kiện cho một nhân viên bán hàng. Điều này có thể tạo ra các lỗi khó coi khiến báo cáo của bạn trở nên lộn xộn và khó đọc hơn với người ra quyết định. Tuy nhiên, nếu bạn bao bọc các phép tính này bằng IFERROR(), bạn có thể thay thế các lỗi đó bằng thứ gì đó dễ nhìn hơn như một dấu gạch ngang, giúp báo cáo trông chỉnh chu hơn. Hơn nữa, các câu lệnh IFERROR() còn có thể vượt ra ngoài việc hiển thị thông báo lỗi đẹp hơn. Bạn cũng có thể lồng các câu lệnh này để thực hiện những việc như VLOOKUP xâu chuỗi.

Kết luận

Chúc mừng! Giờ bạn đã biết nhiều cách tận dụng điều kiện trong Excel. Trong hướng dẫn này, tôi cố gắng đề cập đến những hàm mà tôi thường xuyên sử dụng khi được giao hoàn thành các báo cáo kinh doanh bằng Excel. Tuy nhiên, nếu bạn muốn tìm hiểu thêm về chủ đề này, còn có những hàm khác như SWITCH(), AVERAGEIF() hoặc AVERAGEIFS() mà tôi chưa bàn đến ở đây và có thể bạn sẽ quan tâm.

Ngoài ra, có những kỹ thuật như dùng ký tự đại diện trong các hàm như COUNTIFS() hoàn toàn phù hợp để đếm các chuỗi khớp một phần. Ví dụ, giả sử bạn có một tệp Excel với danh sách món trong thực đơn của nhà hàng và bạn muốn đếm có bao nhiêu món nhắc đến từ "fish". Trong trường hợp này, bạn có thể viết COUNTIF(cell_range,"*fish") và hàm này sẽ đếm các món như swordfish hoặc dolphinfish. Tôi rất khuyến khích bạn tìm hiểu thêm về ký tự đại diện vì chúng có thể rất hữu ích và bạn không bao giờ biết khi nào chúng sẽ phát huy tác dụng trong dự án Excel tiếp theo.

Cuối cùng, bạn cũng có thể xem khóa học Phân tích dữ liệu với bảng tính tại DataCamp vì có một phần dành riêng cho hàm điều kiện, có thể hữu ích thêm.

Xem thêm Hướng dẫn if…elif…else trong Python.