Hàm IF Excel với văn bản

Excel có một số công thức có thể giúp bạn sử dụng dữ liệu của mình một cách thuận tiện. Ví dụ: bạn có thể nhận kết quả dựa trên việc ô có đáp ứng các thông số kỹ thuật nhất định hay không. Một trong những nhiệm vụ phổ biến nhất của Excel là xem một ô có giá trị dựa trên một điều kiện cụ thể hay không. Giá trị này có thể là văn bản, ngày tháng hoặc bất kỳ số nào khác

Trong bài viết này, chúng tôi sẽ hiển thị một số công thức Excel để trả về giá trị trong một ô khác nếu ô chứa văn bản

Sử dụng hàm IF

Hàm IF là một hàm logic dựa trên một câu lệnh đã cho. Một câu lệnh IF có thể có hai kết quả. Khi so sánh, kết quả đầu tiên là đúng và kết quả thứ hai là sai

Cú pháp hàm

=IF (logic_test, [value_if_true], [value_if_false])

Mô tả cú pháp

Logical_test là một chi phí hoặc biểu thức logic đánh giá là TRUE hoặc FALSE

Value_if_true - [tùy chọn] Giá trị quay lại trong khi logic_test đánh giá là TRUE

Value_if_false - [tùy chọn] Chi phí trả lại trong khi logic_test đánh giá là FALSE

1. Chọn một ô trống

2. Nhập các thành phần =IF(C5=Dhaka, Đã giao, Chưa giao). Trong đó C5 đại diện cho giá di động và là Thành phố Dhaka, Cách thức giao hàng cho dù giá trị có đúng hay không được giao. Điều này có nghĩa là nếu chi phí là Sai

3. nhấn nút Enter

4. Sử dụng Công cụ xử lý điền để nhận các giá trị ô khác

Sử dụng Hàm ISNUMBER

Hàm ISNUMBER trả về TRUE và FALSE nếu không. Bạn có thể sử dụng ISNUMBER kết hợp với hàm TÌM KIẾM hoặc TÌM KIẾM để xác minh rằng một ô có giá trị mong muốn mà bạn muốn

Cú pháp của ISNUMBER là

=ISNUMBER (giá trị)

trong đó giá trị là đầu vào bạn muốn kiểm tra

Cú pháp của hàm TÌM KIẾM là

=TÌM KIẾM (tìm_văn bản, trong_văn bản, [số_bắt_đầu])

Mô tả cú pháp

find_text – Văn bản cần tìm

within_text – Văn bản để tìm kiếm bên trong

start_num - [TÙY CHỌN] Vị trí bắt đầu của văn bản cần tìm kiếm

1. Chọn một ô trống như D5

2. Nhập công thức =SNUMBER (TÌM KIẾM (Máy tính để bàn, A2. A4)) trong đó “một số” là văn bản cần tìm, A2. A4 là phạm vi ô mà bạn muốn tìm văn bản

3. nhấn nút Enter

Tuy nhiên, nếu bạn đang thêm một điều kiện phân biệt chữ hoa chữ thường, bạn phải sử dụng hàm TÌM với ISNUMBER

Tìm cú pháp hàm

=FIND (tìm_văn bản, trong_văn bản, [số_bắt_đầu])

Kết hợp hàm IF-OR/AND-ISNUMBER

Bây giờ nếu bạn muốn tìm "từ" hoặc "một số". Bạn có thể làm theo các bước dưới đây

Được rồi, trước khi đi vào vấn đề chính, hãy đặt một hàm OR nhỏ

=OR (logic1, [logic2],…)

Mô tả cú pháp

logic1 - Điều kiện đầu tiên hoặc giá trị phân tích để đánh giá

logic2 - [tùy chọn] điều kiện thứ hai hoặc giá trị phân tích để đánh giá

1. Chọn một ô trống như A5

2. Nhập công thức =IF(OR(ISNUMBER(SEARCH(“WINDOWS”,A5)), ISNUMBER(SEARCH(“DESKTOP,A5)),”SẴN”,”KHÔNG CÓ SẴN”). Trong đó A5 là giá trị Tra cứu

3. nhấn nút Enter

Bạn đã học cách sử dụng công thức nếu ô chứa văn bản trong excel chưa? . Để chỉnh sửa tài liệu word, excel và PowerPoint miễn phí, bạn cũng có thể tải xuống WPS Office. Lấy nó ngay. và có một kinh nghiệm làm việc dễ chịu và đơn giản

Sử dụng hàm IF của Excel khi bạn muốn kiểm tra điều gì đó và hiển thị một kết quả nếu kết quả kiểm tra là Đúng và một kết quả khác nếu kết quả kiểm tra là Sai. Trong Excel 365 còn có hàm IFS, có thể thay thế nhiều hàm IF lồng nhau

GHI CHÚ. Trong nhiều trường hợp, một hàm Excel khác sẽ hiệu quả hơn một công thức IF phức tạp. Thấy

Hàm IF Excel với văn bản

Giới thiệu - Hàm IF

Hàm IF trong Excel rất hữu ích nếu bạn muốn kiểm tra điều gì đó trên trang tính, sau đó hiển thị kết quả A hoặc B. Video ngắn này trình bày các bước thiết lập công thức IF đơn giản. Có các bước được viết bên dưới

Dòng thời gian video

  • 0. 00 Giới thiệu
  • 0. 13 Làm bài kiểm tra
  • 0. 39 Phiếu đặt hàng
  • 0. 55 Công Thức NẾU Đơn Giản
  • 1. 39 Kiểm Tra Công Thức

Hàm IF trong Excel hoạt động như thế nào

IF là một trong các hàm Logic trong Microsoft Excel và có 3 phần (đối số) trong cú pháp hàm IF

  1. kiểm tra logic. KIỂM TRA một cái gì đó, chẳng hạn như giá trị trong một ô
  2. value_if_true. Chỉ định điều gì sẽ xảy ra nếu kết quả kiểm tra là TRUE
  3. value_if_false. Chỉ định điều gì sẽ xảy ra nếu kết quả kiểm tra là SAI

Hàm IF Excel với văn bản

Ví dụ 1. Kiểm tra ô trống

Trong ví dụ này, Tổng trong ô E7 sẽ chỉ hiển thị số tiền nếu Số lượng đã được nhập vào ô D7

Đây là 3 phần (đối số) của hàm IF cho ví dụ này

  1. kiểm tra logic. KIỂM TRA ô D7, để xem nó có trống không (D7=""),
  2. giá trị nếu đúng. Nếu D7 trống (TRUE), ô có công thức Tổng sẽ không hiển thị gì ("")
  3. giá trị nếu sai. Nếu D7 không trống (SAI), ô có công thức Tổng sẽ nhân Giá x Số lượng (C7*D7)

Công thức hoàn chỉnh là. =IF(D7="","",C7*D7)

Trong ảnh chụp màn hình bên dưới, ô D7 trống (kết quả TRUE), vì vậy công thức IF trong ô E7 cũng có vẻ trống

Hàm IF Excel với văn bản

Khi ô D7 không trống (kết quả FALSE), phép tính Tổng ô hiển thị giá trị Giá x Số lượng

Hàm IF Excel với văn bản

Ví dụ 2. Hàm IF lồng nhau

Nếu cần thực hiện nhiều kiểm tra logic trong một công thức, bạn có thể kết hợp nhiều hàm IF trong một công thức. Đây được gọi là công thức lồng nhau vì các hàm IF bổ sung hoạt động như các đối số, được lồng trong một hàm IF khác

Một bài kiểm tra logic

Trong Ví dụ 1, công thức IF có một bài kiểm tra logic -- ô Qty (D7) có trống không?

Hai bài kiểm tra logic

Để kiểm tra cả ô Giá và Số lượng, một hàm IF khác được thêm vào công thức hiện có

Điều đó tạo ra một bài kiểm tra logic thứ hai -- ô Giá (C7) có trống không?

  • =IF(C7="","", IF(D7="","", C7*D7)

Kết quả cho các tình huống khác nhau được hiển thị trong ảnh chụp màn hình bên dưới

  1. Ở hàng 7, ô Giá trống, vì vậy ô Tổng cộng có một chuỗi trống ("")
  2. Ở hàng 8, ô Qty trống, vì vậy ô Tổng cộng có một chuỗi trống ("")
  3. Ở hàng 9, không có ô nào trống, vì vậy tổng giá được tính (giá x số lượng)

Hàm IF Excel với văn bản

Thứ tự các hàm IF lồng nhau

Trong ví dụ về công thức IF lồng nhau trước đó, có 2 bài kiểm tra logic

  • Ô Giá có trống không?
  • Ô Qty có trống không?

Những bài kiểm tra đó đều khó như nhau

  • Không quan trọng bài kiểm tra nào được thực hiện trước
  • Kết quả công thức sẽ giống nhau, với các bài kiểm tra theo một trong hai thứ tự

Các bài kiểm tra khó như thế nào?

Trong một số trường hợp, các bài kiểm tra logic trong công thức IF lồng nhau có các mức độ khó khác nhau

Ví dụ: chúng ta cần xây dựng một công thức trong đó điểm sản phẩm được nhóm thành các loại xếp hạng sau

Danh mục Điểm sản phẩm Xếp hạng Điểm Kiểm tra1Tốt80 trở lên>=802Trung bình60 đến 79B2>=603Kémdưới 60<60

Thứ tự kiểm tra logic về độ khó

3 bài kiểm tra logic ở trên được liệt kê theo thứ tự khó khăn

  1. Trong trường hợp này, bài kiểm tra "Tốt" là khó nhất - Điểm sản phẩm đạt từ 80 trở lên?
    • Chỉ một số ít sản phẩm đạt điểm cao, được đánh giá “Tốt”
  2. Bài kiểm tra "Trung bình" khó thứ hai - Sản phẩm có đạt điểm 60 trở lên không?
    • Hầu hết các sản phẩm sẽ có số điểm từ 60 trở lên và chúng sẽ nhận được xếp hạng "Trung bình"
  3. Bài kiểm tra “Kém” dễ nhất - Điểm sản phẩm dưới 60?
    • Các sản phẩm không nằm trong hai danh mục hàng đầu sẽ tự động rơi vào xếp hạng "Kém"

Công thức. Các bài kiểm tra logic khó hơn trước

Để xây dựng công thức IF lồng nhau, với các bài kiểm tra logic có mức độ khó khác nhau, điều quan trọng là phải đặt các bài kiểm tra khó nhất trước, theo thứ tự độ khó.

Đây là dữ liệu mẫu, với công thức này trong ô C7

  • =IF(B2>=80,"Tốt",IF(B2>=60,"Trung bình","Kém"))

Hàm IF Excel với văn bản

Điểm trong ô B2 là 86, vượt qua bài kiểm tra logic đầu tiên, vì vậy kết quả là "Tốt"

Kết quả sai. Kiểm tra dễ dàng hơn trước

Tuy nhiên, nếu kiểm tra "Trung bình" đầu tiên trong công thức, kết quả sẽ không chính xác

  • =IF(B2>=60,"Trung bình",IF(B2>=80,"Tốt","Kém"))

Điểm trong ô B2 là 86, vượt qua bài kiểm tra logic đầu tiên, vì vậy kết quả là "Trung bình"

Hàm IF Excel với văn bản

Ghi chú về công thức IF lồng nhau

Dưới đây là một số điều quan trọng cần lưu ý khi xây dựng công thức IF lồng nhau

  1. Khi xây dựng các công thức IF lồng nhau, thứ tự liệt kê các phép thử có thể quan trọng. Xem phần bên dưới --
  2. Không lồng quá nhiều hàm IF vào một công thức, nếu không công thức sẽ khó xây dựng chính xác và khó duy trì theo thời gian
  3. Nhiều công thức IF lồng nhau có thể được thay thế bằng một giải pháp khác, chẳng hạn như công thức VLOOKUP hoặc INDEX và MATCH, để linh hoạt hơn. Xem ví dụ trong phần "", bên dưới

Ví dụ 3. Hàm IF có hộp kiểm

Trong ví dụ này, thuế bán hàng sẽ được áp dụng cho các đơn đặt hàng địa phương. Trên mẫu đơn đặt hàng, có một hộp kiểm được liên kết với ô G11

  • Đối với các đơn đặt hàng địa phương, hãy nhấp vào hộp kiểm để thêm dấu kiểm và ô được liên kết hiển thị TRUE
  • Nếu dấu kiểm bị xóa, ô được liên kết hiển thị FALSE

Công thức IF trong ô E12 kiểm tra ô được liên kết (G11)l, để xem ô đó có chứa "SAI" không. Nếu vậy, số tiền thuế bằng không

Hàm IF Excel với văn bản

Nếu ô G11 không chứa "SAI", tổng phụ trong ô E10 được nhân với Thuế suất trong ô D12, để hiển thị số tiền thuế

=IF(G11=FALSE,0,E10*D12)

Hàm IF Excel với văn bản

Hàm IFS - Cách hoạt động

Trong Excel 365 còn có hàm IFS, dùng thay cho nhiều hàm IF lồng nhau. Với chức năng IFS

  • Bạn có thể kiểm tra nhiều điều kiện để xem chúng có ĐÚNG không
  • Công thức trả về kết quả cho điều kiện ĐÚNG đầu tiên

Cú pháp hàm IFS

Hàm IFS có các đối số sau trong cú pháp của nó

  1. logic_test1. KIỂM TRA một cái gì đó, chẳng hạn như giá trị trong một ô
  2. value_if_true1. Chỉ định điều gì sẽ xảy ra nếu kết quả kiểm tra là TRUE
  3. logic_test2. kiểm tra logic127. (tùy chọn) KIỂM TRA điều gì đó, chẳng hạn như giá trị trong một ô
  4. value_if_true2. value_if_true127. (Tùy chọn) Chỉ định điều gì sẽ xảy ra nếu kết quả kiểm tra là TRUE

Ghi chú. Mặc dù bạn có thể nhập tối đa 127 bài kiểm tra và các giá trị liên quan của chúng, nhưng Microsoft khuyên bạn không nên lồng quá nhiều điều kiện. Giới hạn số lượng bài kiểm tra logic chỉ còn một vài, nếu không sẽ khó xây dựng và duy trì công thức IFS

NẾU Ví dụ 1. Kiểm tra các ô trống

Trong ví dụ này, thay vì sử dụng công thức IF lồng nhau, đây là công thức IFS

Trong ô E9, công thức IFS sau đây kiểm tra 3 ô trong một biểu mẫu đặt hàng, để xem tên sản phẩm, giá và số lượng đã được nhập chưa

  • =IFS(COUNTA(B9. D9)=3, C9*D9, B9="","", C9="","không có giá", D9="","không có số lượng")

Hàm IF Excel với văn bản

Đây là cách thức hoạt động của công thức IFS này

  1. Đầu tiên, hàm COUNTA đếm các giá trị trong ô B9. D9. Số đó có bằng 3 không?
    • Nếu TRUE, nhân giá trị ô giá (C9) với số lượng (D9), để tính tổng giá
  2. Tiếp theo, ô B9 được kiểm tra xem nó có trống không. Hai dấu ngoặc kép đại diện cho một chuỗi rỗng
    • Nếu TRUE, trả về một chuỗi rỗng
  3. Sau đó, ô C9 được kiểm tra xem nó có trống không
    • Nếu TRUE, trả về chuỗi văn bản "không có giá"
  4. Cuối cùng, ô D9 được kiểm tra xem nó có trống không
    • Nếu TRUE, trả về chuỗi văn bản "không có giá"

Hàm IF Excel với văn bản

Dưới đây, bạn có thể xem kết quả cho công thức này, trong các ô E9. E12

  • Trong ô E9, kết quả là tổng giá, vì cả 3 ô đều được điền
  • Tại ô E10, hàm trả về chuỗi rỗng "", do thiếu tên sản phẩm
  • Trong ô E11, kết quả là "không có giá", vì ô giá trống

Hàm IF Excel với văn bản

IFS Ex2. Không có đối số value_if_false

Trong Excel 365, bạn có thể sử dụng hàm IFS, thay vì nhiều hàm IF lồng nhau

  • kiểm tra nhiều điều kiện, để xem chúng có ĐÚNG không
  • trả về kết quả cho điều kiện TRUE đầu tiên

Tuy nhiên, không giống như hàm IF, hàm IFS không có đối số cuối cùng, đối với value_if_false

Không có đối số đó, làm thế nào các công thức IFS có thể làm như sau?

  • kiểm tra 1, value_if_true1,
  • kiểm tra 2, value_if_true2,
  • value_for_everything_else

Kiểm tra cuối cùng và giá trị

Như một giải pháp thay thế, để mô phỏng đối số value_if_false của hàm IF

  • Sử dụng TRUE làm bài kiểm tra logic cuối cùng
  • Sử dụng "giá trị cho mọi thứ khác" của bạn làm đối số "value_if_true" cuối cùng

Ví dụ: công thức này có 2 bài kiểm tra logic là Khá và Trung bình

Bất kỳ điểm nào khác, không vượt qua hai bài kiểm tra đầu tiên, sẽ được xếp hạng "Kém", bởi vì họ vượt qua bài kiểm tra cuối cùng

=IFS(
C9>=80,"Tốt",
C9>=60,"Trung bình",
THẬT, "Nghèo"
)

Hàm IF có phải là giải pháp tốt nhất không?

Câu hỏi. Nếu bạn cần kiểm tra xem một hoặc nhiều điều kiện logic là TRUE hay FALSE, sau đó hiển thị kết quả, hàm IF hay hàm IFS luôn là giải pháp tốt nhất để sử dụng?

Câu trả lời. Không, không thường. Nếu bạn cần làm bất cứ điều gì khác ngoài một phép kiểm tra Đúng hoặc Sai đơn giản, thì các hàm Excel khác sẽ cung cấp giải pháp hiệu quả hơn cho vấn đề

Thí dụ. Hế thống đánh giá

Dưới đây là một ví dụ, trong đó một hàm dễ thiết lập và duy trì hơn so với công thức hàm IF lồng nhau hoặc công thức hàm IFS (trong Excel 365)

Trong một số sách bài tập, bạn có thể có hệ thống xếp hạng, vì vậy bạn có thể thay đổi điểm số thành điểm chữ. Trong ví dụ hiển thị bên dưới, nếu một sản phẩm được kiểm tra và đạt số điểm từ 85 trở lên, sản phẩm đó sẽ nhận được 3 sao -- xếp hạng cao nhất

Hàm IF Excel với văn bản

Hàm IF và Giải pháp hàm IFS

Công thức IF này, trong ô D2, có thể tính xếp hạng

  • =IF(C2>=80,"***",IF(C2>=60,"**","*"))

Hoặc, trong Excel 365, công thức IFS này có thể tính xếp hạng

  • =IFS(C2>=80,"***",C2>=60,"**",C2>=0,"*")

Tuy nhiên, trong cả hai công thức đó, các mức xếp hạng được mã hóa cứng vào công thức. Điều đó có thể gây ra vấn đề trong tương lai, nếu mức xếp hạng bị thay đổi

  • Bạn sẽ phải tìm tất cả các công thức đó và thay đổi tất cả các số cấp độ xếp hạng
  • Hoặc, nếu thêm nhiều mức xếp hạng, các công thức sẽ phải được viết lại
Giải pháp Hàm VLOOKUP

Thay vì sử dụng IF hoặc IFS, bạn có thể thiết lập bảng tra cứu xếp hạng, với điểm thấp nhất cho từng xếp hạng và nhóm xếp hạng

Hàm IF Excel với văn bản

Sau đó, sử dụng công thức VLOOKUP này, trong ô D2, để tính xếp hạng

  • =VLOOKUP(C2,RatingLookup,2,TRUE)

Trong tương lai, nếu mức xếp hạng thay đổi, chỉ cần thay đổi thông tin trong bảng RatingLookup

  • Bạn sẽ KHÔNG phải tìm tất cả các công thức đó và thay đổi tất cả các số cấp độ đánh giá
  • Hoặc, nếu thêm nhiều mức xếp hạng, các công thức sẽ KHÔNG phải viết lại

Thêm ví dụ

Dưới đây là một số ví dụ khác, trong đó các công thức khác hiệu quả hơn công thức hàm IF lồng nhau hoặc công thức IFS

  1. Chuyển đổi điểm của học sinh từ số sang chữ cái -- sử dụng hoặc INDEX/MATCH
  2. Chọn một mức lãi suất, dựa trên ngày giao dịch -- sử dụng
  3. Hiển thị một giá trị cụ thể thay vì lỗi -- sử dụng (Excel 2007 trở lên)

Công thức trong bảng Excel được đặt tên

Nếu bản ghi của bạn được lưu trữ trong Bảng Excel có tên, thì bạn có thể sử dụng tham chiếu có cấu trúc trong công thức của mình, thay vì tham chiếu ô thông thường

Ví dụ: công thức sau trong ô E7, để tính tổng giá trong hàng đó

Công thức này sử dụng tên cột của bảng, chẳng hạn như [Giá], thay vì tham chiếu ô thông thường, chẳng hạn như C7

Hàm IF Excel với văn bản

Tham chiếu ô thông thường và Tham chiếu có cấu trúc

Dưới đây là một số lưu ý về tham chiếu bình thường và có cấu trúc

  • Tham chiếu ô bình thường có địa chỉ ô, chẳng hạn như C7
    • Tham chiếu có thể bao gồm một hoặc hai ký hiệu $, chẳng hạn như $C7, C$7, $C$7
    • Dấu $ tạo tham chiếu tuyệt đối đến hàng hoặc cột (sẽ không thay đổi nếu công thức được sao chép sang vị trí khác)
    • Không có dấu $, tham chiếu hàng hoặc cột là tương đối (có thể thay đổi nếu công thức được sao chép sang vị trí khác)
  • Tham chiếu có cấu trúc có tên trường bên trong dấu ngoặc vuông, chẳng hạn như [Giá] hoặc [Số lượng]
    • Tham chiếu có cấu trúc cũng có thể bao gồm tên bảng hoặc ký hiệu @, chẳng hạn như Table1[@Total]

Lấy tệp mẫu

Để làm theo các ví dụ trong hướng dẫn này, hãy tải xuống sổ làm việc IF và IFS mẫu. Tệp nén có định dạng xlsx và không chứa bất kỳ macro nào