VietNetwork.Vn

Để Excel đánh giá dữ liệu của bạn.

Các hàm logic của Excel kiểm tra xem một câu lệnh hoặc dữ liệu là đúng hay sai trước khi cho phép chương trình thực hiện hành động dựa trên kết quả. Chúng hữu ích để phân tích dữ liệu, tự động hóa một số tác vụ hoặc phép tính và cuối cùng là đưa ra quyết định.

Khi sử dụng ngẫu nhiên, chúng trả về ĐÚNG hoặc SAI, tùy thuộc vào việc tiêu chí bạn đặt ra có được đáp ứng hay không.


Tôi sẽ giới thiệu ba hàm logic (hoặc nhóm hàm) mà tôi thấy đặc biệt hữu ích và cách bạn có thể tận dụng tối đa chúng trong các tình huống thực tế.

1. IF với AND, OR và NOT

Trước khi giải thích cách sử dụng các hàm này cùng nhau, tôi sẽ giải thích chức năng riêng của từng hàm.

AND, OR và NOT tự chúng giúp xác định các điều kiện. Trong ví dụ dưới đây, tôi muốn giải quyết ba điều cho mỗi ứng viên:

• Họ có trên 18 tuổi và được chứng nhận vàng không (cột F)?
• Họ có giấy phép lái xe đầy đủ hoặc khả năng làm việc ở nước ngoài (cột G) không ?
• Họ không phải là trên 50 sao (cột H)?

Trong ô F2, tôi cần nhập.

=AND(B2>18,D2="Gold")
Vì tôi muốn Excel cho tôi biết giá trị trong ô B2 có lớn hơn 18 và giá trị trong ô D2 có bằng "Vàng" không.

Trong ô G2, tôi sẽ nhập.

=OR(C2="Full",E2="YES")
Vì tôi muốn Excel xác định giá trị trong C2 là "Đầy đủ" hay giá trị trong E2 là "Có".

Cuối cùng, trong ô H2, tôi sẽ cần nhập.

=NOT(B2>50)
Vì tôi cần kiểm tra giá trị trong ô B2 không lớn hơn 50.

Khi tạo các đối số logic, toàn bộ văn bản cần được đặt trong dấu ngoặc kép.

Sau đó, tôi sẽ sử dụng tính năng Tự động điền của Excel để sao chép công thức vào các hàng còn lại.


Lưu ý cách mỗi đầu ra là TRUE hoặc FALSE, tùy thuộc vào việc các điều kiện có được đáp ứng hay không. Mặc dù điều này rất tiện lợi, nhưng việc có một từ hoặc cụm từ chỉ báo cụ thể thậm chí còn hữu ích hơn. Đó là lúc IF phát huy tác dụng, vì nó trả về một giá trị cụ thể tùy thuộc vào các điều kiện đã đặt.

Lần này, tôi muốn đạt được những kết quả sau cho mỗi ứng viên:

• Nếu họ trên 18 tuổi và được chứng nhận vàng, họ có thể được coi là thành viên cao cấp.
• Nếu họ có giấy phép lái xe đầy đủ hoặc khả năng làm việc ở nước ngoài, họ có thể được coi là ứng viên có thể đi du lịch.
• Nếu họ không quá 50 tuổi, tôi muốn Excel gắn nhãn cho biết họ đủ điều kiện để được học nghề.

Vì vậy, trong ô F2, tôi sẽ nhập.

=IF(AND(B2>18,D2="Gold"),"Senior","Junior")
Vì tôi muốn Excel kiểm tra giá trị trong B2 có lớn hơn 18 và giá trị trong D2 là "Gold" hay không, sau đó trả về "Senior" nếu cả hai đều đúng hoặc "Junior" nếu không.

Trong ô G2, tôi cần phải đi với.

=IF(OR(C2="Full",E2="YES"),"Can travel","Cannot travel")
Bởi vì nếu giá trị trong C2 là "Đầy đủ" hoặc giá trị trong E2 là "Có", tôi cần biết rằng họ "Có thể di chuyển". Nếu không có đối số nào đúng, họ "Không thể di chuyển".

Cuối cùng, trong ô F2, tôi sẽ nhập.

=IF(NOT(B2>50),"Eligible for scholarship","No scholarship")
Vì nếu giá trị ở B2 không lớn hơn 50 thì đương đơn được coi là "Đủ điều kiện nhận học bổng", nhưng nếu lớn hơn 50 thì được coi là "Không nhận học bổng".

Một lần nữa, tôi sẽ kéo nút Tự động Điền xuống cho cả ba cột để điền vào các hàng còn lại của bảng.


Hãy thử sử dụng IF, AND và OR với các hộp kiểm trong Excel để theo dõi tiến trình tác vụ. Bạn cũng có thể chọn hàm XOR, ở dạng đơn giản nhất, hàm này trả về TRUE nếu chỉ có một giá trị là TRUE hoặc FALSE nếu cả hai giá trị đều là TRUE.

2. IFERROR

Tôi sử dụng IFERROR để giữ cho tất cả các bảng tính của mình gọn gàng. Sau cùng, không ai muốn một bảng tính đầy lỗi #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, và #NULL!, và IFERROR giúp ngăn chặn điều này.

Trong ví dụ này, tôi đang theo dõi tỷ lệ bàn thắng trên mỗi trận đấu của mười cầu thủ. Để làm điều này, tôi đã nhập

=SUM(C2/B2)
Vào ô D2, sau đó mở rộng phép tính này xuống toàn bộ cột D.

Tuy nhiên, vì người chơi C chưa chơi bất kỳ ván nào nên phép chia trả về #DIV/0!.


Để giải quyết vấn đề này, tôi sẽ nhúng tất cả các phép tính vào cột D trong hàm IFERROR.

=IFERROR(x,y)
Trong đó x là phép tính đang được thực hiện và y là giá trị trả về nếu có lỗi.

Nếu bạn để y trống sau dấu phẩy, Excel sẽ trả về 0 cho phép tính sai.

Vì vậy, trong ô D2, tôi sẽ nhập.

=IFERROR(SUM(C2/B2),"-")
Và mở rộng điều này xuống cột. Lưu ý cách

SUM(C2/B2)
Phép tính vẫn còn đó, nhưng nó được nhúng trong hàm IFERROR. Trong trường hợp này, bất cứ khi nào có lỗi trong phép tính, nó sẽ trả về một dấu gạch ngang, trông gọn gàng hơn nhiều so với thông báo lỗi.


Hãy nhớ rằng việc sử dụng IFERROR để ẩn lỗi trong bảng tính có thể khiến việc xác định lỗi tính toán trở nên khó khăn hơn. Đây là lý do tại sao tôi chỉ có xu hướng sử dụng nó trong các trường hợp khi #DIV/0! nếu không sẽ xuất hiện.

3. IS với IF

Có một số hàm IS và mỗi hàm thực hiện những việc rất khác nhau. Tôi thường sử dụng chúng (trong hàm IF) để kiểm tra xem có bất kỳ lỗi hoặc sự không nhất quán nào trong dữ liệu của tôi không.

Trước khi tìm hiểu cách kết hợp chúng với hàm IF, chúng ta hãy xem xét chúng một cách riêng biệt.

Tất cả các hàm IS đều có cú pháp giống nhau:

=IS[TYPE](a)
Trong đó [TYPE] là loại hàm IS bạn muốn sử dụng và a là tham chiếu ô hoặc giá trị cần đánh giá.

Sau đây là các loại hàm IS bạn có thể lựa chọn và bạn có thể xem một số ví dụ bên dưới:

• ISBLANK: Kiểm tra xem a có trống không.
• ISOMITTED: Kiểm tra xem giá trị trong hàm LAMBDA có bị thiếu hay không.
• ISERROR: Kiểm tra xem a có phải là giá trị lỗi hay không (chẳng hạn như #N/A, #VALUE#, v.v.).
• ISERR: Kiểm tra xem a có phải là giá trị lỗi nào không, ngoại trừ #N/A.
• ISNA: Kiểm tra xem a có chứa lỗi #N/A hay không.
• ISFORMULA: Kiểm tra xem a có phải là công thức hay không.
• ISLOGICAL: Kiểm tra xem a có chứa giá trị logic (TRUE hoặc FALSE) dựa trên hàm logic hay không.
• ISTEXT: Kiểm tra xem a có phải là văn bản hay không, bao gồm văn bản được tạo ra thông qua hàm logic.
• ISNONTEXT: Kiểm tra xem a có phải là văn bản (chẳng hạn như công thức hoặc số) hay không, bao gồm cả văn bản được tạo ra thông qua hàm logic.
• ISNUMBER: Kiểm tra xem a có phải là số hay không, bao gồm cả số được tạo ra thông qua công thức.
• ISEVEN hoặc ISODD: Kiểm tra xem a là số chẵn hay số lẻ, tùy thuộc vào số bạn sử dụng. Trong những trường hợp này, ô trống được coi là chẵn và ô không phải số trả về lỗi.
• ISREF: Kiểm tra xem a có phải là tham chiếu ô hay không. Ví dụ, nếu a là (A1), điều này sẽ trả về TRUE, nhưng nếu a là ("apple"), điều này sẽ trả về FALSE.

Mặc dù bắt đầu bằng IS, các hàm ISPMT và ISOWEEKNUM không thuộc nhóm IS.

Hãy xem một số trong số này trong bảng tính Excel. Trong các cột B đến J, tôi đã sử dụng các hàm IS được chỉ định để kiểm tra các giá trị trong cột A.


Ví dụ, trong ô B2, tôi đã nhập.

=ISBLANK(A2)
Và trong ô G4, tôi đã nhập

=ISNONTEXT(A4)
Nếu công thức IS của bạn tham chiếu đến một ô trong bảng Excel được định dạng, giá trị trong dấu ngoặc đơn sẽ là tên cột. Trong ví dụ trên, hãy nhập

=ISTEXT([@Value to test])
Vào ô F1 sau đó sẽ tự động áp dụng vào các ô khác trong cột F trong bảng.

Để sử dụng hàm IS với IF, bạn cần nhúng hàm trước vào hàm sau.

=IF(IS[TYPE](a),b,c)
trong đó [TYPE] là loại hàm IS bạn muốn sử dụng từ danh sách trên, a là tham chiếu ô hoặc giá trị cần đánh giá, b là giá trị hoặc công thức nếu ĐÚNG và c là giá trị hoặc công thức nếu SAI.

Trong ví dụ dưới đây, tôi muốn tính toán lợi nhuận hàng tuần dự kiến của nhân viên dựa trên lợi nhuận hàng ngày của họ, nhưng tạo ra một thông báo nếu lợi nhuận hàng ngày bị trống. Để làm điều này, tôi đã nhập công thức này vào C2:

=IF(ISBLANK(B2),"Data required",B2*7)
Vì tôi muốn Excel tính toán xem ô B2 có trống không và trả về "Dữ liệu cần thiết" (nếu B2 trống) hoặc nhân giá trị trong B2 với bảy (nếu không trống). Sau đó, tôi sử dụng Tự động điền để sao chép công thức tương đối vào các ô còn lại trong cột C.


Bạn có thể làm tương tự với bất kỳ chức năng IS nào được liệt kê ở trên.

Ngoài việc sử dụng các hàm logic của Excel, tôi còn sử dụng nhiều tổ hợp hàm khác để đánh giá và sử dụng dữ liệu trong các bảng của Excel, chẳng hạn như INDEX với MATCH và COUNTIF với SUM. Nếu bạn chưa quen với những hàm này, thì chắc chắn là đáng để thử!