181 Hiểu tất tần tật IFERROR là hàm gì trong Excel, Google Sheets mới nhất

công nghệ thông tin

Hiểu mọi thứ IFERROR là hàm trong Excel, Google Sheets

Trong bài viết dưới đây, GhienCongList sẽ cho bạn biết IFERROR là gì và cách sử dụng nó mà không phải ai cũng biết.

IFFERROR là một hàm khá đặc biệt, không được sử dụng nhiều trong Excel và Google Sheets. Đối với nhiều bạn, bạn có thể chưa biết điều này Hàm IFERROR là gì?, cách sử dụng và kết hợp với các chức năng khác. Vì vậy, để hiểu rõ hơn, chúng ta hãy đi GhienCongNghe Kiểm tra bài viết dưới đây.

thông báo

Hàm IFERROR là gì?

quai hàm CÓ LỖI trong Excel được thiết kế để tận dụng và xử lý các lỗi trong công thức và tính toán. Cụ thể hơn, CÓ LỖI kiểm tra một công thức và nếu nó trả về lỗi, nó sẽ trả về một giá trị khác mà bạn chỉ định; ngược lại, nó trả về kết quả của công thức.

Cú pháp của hàm CÓ LỖI trong Excel như sau:

thông báo

=IFERROR(giá trị, giá_trị_nếu_lỗi)

Ở đâu:

thông báo

  • Giá trị (bắt buộc): Kiểm tra lỗi gì. Nó có thể là một công thức, một biểu thức, một giá trị hoặc một tham chiếu ô.
  • Value_if_error (bắt buộc): Giá trị trả về nếu gặp lỗi. Nó có thể là một chuỗi rỗng (ô trống), tin nhắn văn bản, giá trị số, công thức hoặc phép tính khác.

Ví dụ: khi bạn tách hai cột số, bạn có thể gặp một tập hợp lỗi khác nếu một trong các cột chứa các ô trống, số không hoặc văn bản.

mà hàm iferror là 01

Để ngăn điều này xảy ra, hãy sử dụng chức năng CÓ LỖI để phát hiện và xử lý lỗi theo ý muốn.

Nếu có lỗi, hãy để trống. Sau đó, cung cấp một chuỗi rỗng (“”) cho đối số value_if_error để trả về ô trống nếu gặp lỗi:

=SIERROR(A2/B2, “”)

mà hàm iferror là 02

Nếu không thành công, bạn sẽ muốn có thể hiển thị thông báo của riêng mình thay vì ký hiệu lỗi tiêu chuẩn của Excel:

=SIERROR(A2/B2, “Lỗi khi tính toán”)

chức năng iferror là gì 03

5 điều bạn nên biết về hàm IFERROR

  • quai hàm CÓ LỖI trong Excel xử lý tất cả các loại lỗi, bao gồm lỗi #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! và #VALUE!.
  • Tùy thuộc vào nội dung của đối số value_if_error, CÓ LỖI có thể thay thế lỗi bằng tin nhắn văn bản tùy chỉnh, số, ngày hoặc giá trị logic, kết quả của một công thức khác hoặc một chuỗi trống (ô trống).
  • Nếu đối số giá trị là một ô trống, nó sẽ được coi là một chuỗi rỗng (” “), nhưng không phải là lỗi.
  • CÓ LỖI được giới thiệu trong Excel 2007 và có sẵn trong tất cả các phiên bản sau này của Excel 2010, Excel 2013 và Excel 2016.

” Tìm hiểu về: Null là gì? Null trong lập trình có khác với lỗi trong Excel không?

Ví dụ ứng dụng của hàm IFERROR

Các ví dụ sau đây cho thấy cách sử dụng chức năng CÓ LỖI trong Excel và Google Trang tính kết hợp với các chức năng khác để thực hiện các tác vụ phức tạp hơn.

SỐ PHIẾU với VLOOKUP

Một trong những cách sử dụng phổ biến nhất của hàm. CÓ LỖI là để cho người dùng biết rằng giá trị mà họ đang tìm kiếm không tồn tại trong tập dữ liệu. Đối với điều này, bạn đặt một công thức TÌM KIẾM V Trong CÓ LỖI như thế này:

IFERROR(LOOKUP(…),”Không tìm thấy”)

IFERROR (VLOOKUP (…), “Không tìm thấy”)

Nếu không tìm thấy giá trị tra cứu trong bảng bạn đang tìm, công thức TÌM KIẾM V nó thường trả về lỗi #N/A:

chức năng iferror là gì 04

Đối với người dùng của bạn, bọc chức năng TÌM KIẾM V Trong CÓ LỖI và hiển thị một thông báo nhiều thông tin và thân thiện với người dùng hơn:

=SIERROR(SEARCH(A2, ‘Bảng tra cứu’!$A$2:$B$4, 2,FALSE), “Không tìm thấy”)

Ảnh chụp màn hình bên dưới hiển thị công thức CÓ LỖI cái này trong Excel:

chức năng iferror là gì 05

Nếu bạn chỉ muốn ném lỗi #N/A chứ không phải tất cả, hãy sử dụng chức năng IFNA thay vì CÓ LỖI.

Các hàm IFERROR lồng nhau để thực hiện các VLOOKUP tuần tự

Trong những tình huống cần phải làm nhiều việc TÌM KIẾM V dựa trên TÌM KIẾM V trước khi thành công hay thất bại, bạn có thể lồng hai hoặc nhiều chức năng CÓ LỖI trong một chức năng khác.

Giả sử bạn có một số báo cáo bán hàng từ các văn phòng khu vực của công ty bạn và bạn muốn lấy số tiền cho một ID đơn đặt hàng cụ thể. Với A2 là giá trị tra cứu trong trang tính hiện tại và A2:B5 là phạm vi tra cứu trong 3 trang tính tra cứu (báo cáo 1, báo cáo 2 và báo cáo 3), công thức như sau:

=IFERROR(SEARCH(A2,’Báo cáo 1′!A2:B5,2,0),IFERROR(SEARCH(A2,’Báo cáo 2′!A2:B5,2,0), IFERROR(SEARCH(A2,’Báo cáo 3 ) ) ′!A2:B5,2,0)”không tìm thấy”)))

Kết quả sẽ như thế này:

chức năng iferror là gì 06

IFERROR trong công thức mảng

Như bạn đã biết, công thức mảng trong Excel có nghĩa là thực hiện nhiều phép tính trong một công thức. Nếu bạn cung cấp một công thức hoặc biểu thức mảng dẫn đến một mảng trong đối số giá trị của hàm CÓ LỖI, trả về một mảng các giá trị cho mỗi ô trong phạm vi được chỉ định. Ví dụ sau đây cho thấy các chi tiết.

Ví dụ: bạn có Tổng ở cột B và Giá ở cột C và bạn muốn tính tổng số tiền. Điều này có thể được thực hiện bằng cách sử dụng công thức mảng sau đây, chia từng ô trong phạm vi B2:B4 cho ô tương ứng trong phạm vi C2:C4, rồi cộng kết quả:

=SUM($B$2:$B$4/$C$2:$C$4)

Công thức hoạt động tốt miễn là phạm vi ước số không chứa số 0 hoặc ô trống. Nếu có ít nhất một giá trị 0 hoặc ô trống, giá trị #DIV/0! lỗi trả về:

chức năng iferror là gì 07

Để khắc phục lỗi này, chỉ cần thực hiện phép chia trong hàm CÓ LỖI:

=SUM(IF ERROR($B$2:$B$4/$C$2:$C$4,0))

Công thức thực hiện là chia một giá trị trong cột B cho một giá trị trong cột C ở mỗi hàng (100/2, 200/5 và 0/0) và trả về kết quả là mảng {50; 40; #DIV/0!}. quai hàm CÓ LỖI bắt tất cả #DIV/0! lỗi và thay thế chúng bằng số không. Và sau đó hàm SUM cộng các giá trị vào mảng kết quả {50; 40; 0} và kết quả cuối cùng (50 + 40 = 90).

chức năng iferror là gì 08

Ghi chú: Hãy nhớ rằng công thức mảng phải được hoàn thành bằng cách nhấn Ctrl + Shift + Quay lại – đường tắt.

NẾU LỖI vs. NẾU CÓ LỖI

Bây giờ bạn đã biết cách sử dụng chức năng CÓ LỖI Dễ dàng như trong Excel, bạn có thể thắc mắc tại sao một số người vẫn có xu hướng sử dụng kết hợp CÓ LỖI. Nó có lợi thế gì CÓ LỖI? Không một ai. Ngày xưa của Excel 2003 trở về trước khi CÓ LỖI nó không tồn tại, NẾU CÓ LỖI là cách duy nhất có thể để bắt lỗi. Trong Excel 2007 trở lên, sẽ phức tạp hơn một chút để đạt được kết quả tương tự.

Ví dụ: để bắt lỗi VLOOKUP, bạn có thể sử dụng một trong các công thức sau.

Trong Excel 2007 – Excel 2016:

IFERROR(TÌM KIẾM(…), “Không tìm thấy”)

Trong tất cả các phiên bản của Excel:

IF(ISERROR(SEARCH(…)), “Không tìm thấy”, TÌM KIẾM(…)

Lưu ý rằng trong công thức TÌM KIẾM V NẾU CÓ LỖIbạn phải làm điều đó TÌM KIẾM V hai lần. Nói một cách dễ hiểu, công thức có thể được đọc như sau: Nếu kết quả TÌM KIẾM V lỗi, trả về “Không tìm thấy”, nếu không thì hiển thị kết quả TÌM KIẾM V.

Và đây là một ví dụ thực tế về một công thức Excel Nếu xảy ra lỗi TÌM KIẾM V:

=IF(ISERROR(SEARCH(D2, A2:B5,2,FALSE)),”Không tìm thấy”, TÌM KIẾM(D2, A2:B5,2,FALSE))

chức năng iferror là gì 09

IFERROR vs. IFNA

Được giới thiệu với Excel 2013, IFNA là một chức năng khác để kiểm tra lỗi công thức. Cú pháp của nó tương tự như CÓ LỖI:

IFNA(giá trị, value_if_na)

  • IFNA nó khác nhau CÓ LỖI lúc mấy giờ – Hàm IFNA nó chỉ bắt lỗi #N/A trong khi thực hiện chức năng CÓ LỖI xử lý tất cả các loại lỗi.
  • Bạn có thể muốn sử dụng IFNA trong những trường hợp nào? – Ví dụ: khi làm việc với dữ liệu quan trọng hoặc nhạy cảm, bạn có thể muốn được cảnh báo về các lỗi tiềm ẩn trong tập dữ liệu của mình và các thông báo lỗi Excel tiêu chuẩn có ký hiệu “#” có thể là các chỉ báo trực quan sống động.

Xem cách bạn có thể tạo một công thức hiển thị thông báo “Không tìm thấy” thay vì lỗi #N/A, xuất hiện khi không tìm thấy giá trị tra cứu trong tập dữ liệu, nhưng dẫn đến các lỗi Excel khác.

Giả sử bạn muốn xóa Qty. từ bảng tra cứu sang bảng tóm tắt như trong ảnh chụp màn hình bên dưới. Sử dụng công thức NẾU TÌM KIẾM V LỖI trong Excel sẽ tạo ra các kết quả đẹp mắt về mặt thẩm mỹ, điều này không đúng về mặt kỹ thuật vì Lemons tồn tại trong bảng tra cứu:

Hàm iferror là gì 10

Để chụp #N/A nhưng hiển thị lỗi #DIV/0, hãy sử dụng chức năng IFNA trong Excel 2013 và Excel 2016:

=IFNA(SEARCH(F3,$A$3:$D$6,4,FALSE), “Không tìm thấy”)

Hoặc kết hợp CÓ ISNA trong Excel 2010 và các phiên bản cũ hơn:

=IF(ISNA(SEARCH(F3,$A$3:$D$6,4,FALSE)),”Không tìm thấy”, TÌM KIẾM(F3,$A$3:$D$6,4,FALSE))

Cú pháp của công thức TÌM KIẾM V IFNA tôi NẾU LÀ TÌM V giống như công thức NẾU TÌM KIẾM V LỖI tôi NẾU VLOOKUP LỖI thảo luận trước đó.

Như thể hiện trong ảnh chụp màn hình bên dưới, công thức Ifna TÌM KIẾM V chỉ trả về “Không tìm thấy” cho các mục không tìm thấy trong bảng tra cứu (Đào). Đối với chanh, hiển thị #DIV/0! chỉ ra rằng bảng tra cứu của chúng tôi có lỗi chia cho 0:

chức năng iferror là gì 11

Các phương pháp hay nhất để sử dụng hàm IFERROR

Bây giờ bạn biết chức năng đó CÓ LỖI là cách dễ nhất để phát hiện lỗi trong Excel và che giấu chúng bằng các ô trống, giá trị bằng 0 hoặc thông báo tùy chỉnh của riêng bạn. Tuy nhiên, điều đó không có nghĩa là bạn phải kết thúc mọi công thức bằng cách xử lý lỗi. Những gợi ý đơn giản sau đây có thể giúp bạn giữ thăng bằng.

  • Đừng phạm sai lầm mà không có lý do.
  • Bọc phần nhỏ nhất có thể của một công thức CÓ LỖI.
  • Để chỉ xử lý các lỗi cụ thể, hãy sử dụng chức năng xử lý lỗi phạm vi nhỏ hơn:
    IFNA hoặc là CÓ ISNA để chỉ phát hiện lỗi #N/A.
    ISER để bắt tất cả các lỗi ngoại trừ lỗi #N/A.

Xem thêm:

  • Cách ngắt dòng văn bản trong Google Sheets
  • Cách tô màu các dòng xen kẽ trong Google Sheets
  • Cách tạo bảng trong Google Sheet

Trên đây GhienCongList đã giải đáp cho các bạn IFERROR là gì. Nếu bạn có bất kỳ câu hỏi, xin vui lòng bình luận dưới đây. Đừng quên like và share nếu bạn thấy bài viết này hữu ích và đây cũng là động lực để GhienCongNghe cung cấp nhiều nội dung hữu ích hơn nữa trong tương lai.

Kiểm tra Ablebit