Nội dung chính
Hướng dẫn giải thích ISNUMBER trong Excel là gì và cung cấp các ví dụ về sử dụng cơ bản và nâng cao.
Khái niệm về hàm ISNUMBER trong Excel rất đơn giản – nó chỉ kiểm tra xem một giá trị đã cho có phải là số hay không. Một điểm quan trọng ở đây là việc sử dụng thực tế của hàm vượt xa khái niệm cơ bản của nó, đặc biệt khi kết hợp với các hàm khác trong các công thức lớn hơn.
Hàm ISNUMBER Excel
Hàm ISNUMBER trong Excel kiểm tra xem một ô có chứa giá trị số hay không. Nó thuộc nhóm chức năng IS.
Chức năng có sẵn trong tất cả các phiên bản Excel cho Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 trở xuống.
Cú pháp ISNUMBER chỉ yêu cầu một đối số:
=ISNUMBER(value)
Ở đâu giá trị là giá trị bạn muốn kiểm tra. Thông thường, nó được biểu thị bằng một tham chiếu ô, nhưng bạn cũng có thể cung cấp một giá trị thực hoặc lồng một hàm khác trong ISNUMBER để kiểm tra kết quả.
Nếu giá trị là số, hàm trả về ĐÚNG. Đối với mọi thứ khác (giá trị văn bản, lỗi, khoảng trắng) ISNUMBER trả về SAI.
Ví dụ: hãy kiểm tra các giá trị trong các ô từ A2 đến A6 và chúng tôi sẽ tìm ra rằng 3 giá trị đầu tiên là các số và hai giá trị cuối cùng là văn bản:
2 điều bạn nên biết về hàm ISNUMBER trong Excel
Có một vài điểm thú vị cần lưu ý ở đây:
- Trong biểu diễn Excel nội bộ, ngày và lần là các giá trị số, vì vậy công thức ISNUMBER trả về TRUE cho chúng (vui lòng xem B3 và B4 trong ảnh chụp màn hình ở trên).
- Đối với các số được lưu dưới dạng văn bản, hàm ISNUMBER trả về FALSE (xem ví dụ này).
Ví dụ về công thức Excel ISNUMBER
Các ví dụ dưới đây minh họa một vài cách sử dụng ISNUMBER không phổ biến và đơn giản trong Excel.
Kiểm tra nếu một giá trị là số
Khi bạn có một loạt các giá trị trong bảng tính của mình và bạn muốn biết đó là những số nào, ISNUMBER là hàm phù hợp để sử dụng.
Trong ví dụ này, giá trị đầu tiên là ở A2, vì vậy chúng tôi sử dụng công thức dưới đây để kiểm tra nó, sau đó kéo công thức xuống càng nhiều ô nếu cần:
=ISNUMBER(A2)
Vui lòng chú ý rằng mặc dù tất cả các giá trị trông giống như số, công thức ISNUMBER đã trả về FALSE cho các ô A4 và A5, có nghĩa là các giá trị đó là chuỗi số, tức là các số được định dạng dưới dạng văn bản. Có thể có các lý do khác nhau cho việc này, ví dụ các số 0 đứng đầu, dấu nháy đơn trước, v.v … Dù lý do là gì, Excel không nhận ra các giá trị như số. Vì vậy, nếu các giá trị của bạn không tính toán chính xác, điều đầu tiên để bạn kiểm tra là liệu chúng có thực sự là số theo Excel hay không, sau đó chuyển đổi văn bản thành số Nếu cần thiết.
Công thức TÌM KIẾM Excel ISNUMBER
Ngoài việc xác định số, hàm ISNUMBER của Excel cũng có thể kiểm tra xem một ô có chứa văn bản cụ thể như một phần của nội dung hay không. Đối với điều này, sử dụng ISNUMBER cùng với Search chức năng.
Ở dạng chung, công thức trông như sau:
ISNUMBER (Search (chuỗi con, tế bào))
Ở đâu chuỗi con là văn bản mà bạn muốn tìm.
Ví dụ: hãy kiểm tra xem chuỗi trong A3 có chứa một màu cụ thể không, giả sử màu đỏ:
=ISNUMBER(SEARCH("red", A3))
Công thức này hoạt động độc đáo cho một tế bào duy nhất. Nhưng vì bảng mẫu của chúng tôi (vui lòng xem bên dưới) có ba màu khác nhau, viết một công thức riêng cho mỗi màu sẽ gây lãng phí thời gian. Thay vào đó, chúng tôi sẽ đề cập đến ô chứa màu quan tâm (B2).
=ISNUMBER(SEARCH(B$2, $A3))
Để công thức sao chép chính xác xuống và sang phải, hãy nhớ khóa các tọa độ sau bằng dấu $:
- Trong chuỗi con tham chiếu, khóa hàng (B $ 2) để các công thức được sao chép luôn chọn các chuỗi con trong hàng 2. Tham chiếu cột là tương đối vì chúng tôi muốn nó điều chỉnh cho mỗi cột, tức là khi công thức được sao chép vào C3, tham chiếu chuỗi con sẽ đổi thành C $ 2.
- bên trong tế bào nguồn tham chiếu, khóa cột ($ A3) để tất cả các công thức kiểm tra các giá trị trong cột A.
Ảnh chụp màn hình bên dưới hiển thị kết quả:
Search ISNUMBER – công thức phân biệt chữ hoa chữ thường
Vì chức năng Search là trường hợp không nhạy cảm, công thức trên không phân biệt các ký tự chữ hoa và chữ thường. Nếu bạn đang tìm kiếm một công thức phân biệt chữ hoa chữ thường, hãy sử dụng Find chức năng chứ không phải Search.
ISNUMBER (Find (chuỗi con, tế bào))
Đối với tập dữ liệu mẫu của chúng tôi, công thức sẽ có dạng này:
=ISNUMBER(FIND(B$2, $A3))
Công thức này hoạt động như thế nào
Logic của công thức khá rõ ràng và dễ làm theo:
- Hàm SEARCH / FIND tìm chuỗi con trong ô được chỉ định. Nếu tìm thấy chuỗi con, vị trí của ký tự đầu tiên được trả về. Nếu không tìm thấy chuỗi con, hàm sẽ tạo ra #VALUE! lỗi.
- Hàm ISNUMBER lấy nó từ đó và xử lý các vị trí số. Vì vậy, nếu chuỗi con được tìm thấy và vị trí của nó được trả về dưới dạng số, ISNUMBER xuất TRUE. Nếu không tìm thấy chuỗi con và #VALUE! xảy ra lỗi, ISNUMBER xuất ra SAI.
NẾU công thức ISNUMBER
Nếu bạn muốn lấy một công thức tạo ra thứ gì đó không phải TRUE hoặc FALSE, hãy sử dụng ISNUMBER cùng với hàm IF.
Ví dụ 1. Ô chứa văn bản nào
Lấy ví dụ trước hơn nữa, giả sử bạn muốn đánh dấu màu của từng mục bằng “x” như được hiển thị trong bảng bên dưới.
Để thực hiện điều này, chỉ cần bọc Công thức TÌM KIẾM ISNUMBER vào Tuyên bố IF:
=IF(ISNUMBER(SEARCH(B$2, $A3)), "x", "")
Nếu ISNUMBER trả về TRUE, hàm IF sẽ xuất ra “x” (hoặc bất kỳ giá trị nào khác bạn cung cấp cho value_if_true tranh luận). Nếu ISNUMBER trả về FALSE, hàm IF sẽ tạo ra một chuỗi rỗng (“”).
Ví dụ 2. Ký tự đầu tiên trong một ô là số hoặc văn bản
Hãy tưởng tượng rằng bạn đang làm việc với một danh sách các chuỗi ký tự chữ và số và bạn muốn biết liệu ký tự đầu tiên của chuỗi là số hay chữ cái.
Để xây dựng một công thức như vậy, chúng ta sẽ cần 4 hàm khác nhau:
- Các Chức năng TRÁI trích xuất ký tự đầu tiên từ đầu chuỗi, nói trong ô A2:
LEFT(A2, 1)
- Vì TRÁI thuộc về loại chức năng Văn bản, kết quả của nó luôn là một chuỗi văn bản, ngay cả khi nó chỉ chứa số. Do đó, trước khi kiểm tra ký tự trích xuất, chúng ta cần cố gắng chuyển đổi nó thành một số. Đối với điều này, sử dụng một trong hai Hàm GIÁ TRỊ hoặc là đôi đơn nhà điều hành:
VALUE(LEFT(A2, 1))
hoặc là(--LEFT(A2, 1))
- Hàm ISNUMBER xác định xem ký tự được trích xuất có phải là số hay không:
ISNUMBER(VALUE(LEFT(A2, 1)))
- Dựa trên kết quả ISNUMBER (TRUE hoặc FALSE), Hàm IF trả về “Số” hoặc “Thư”, tương ứng.
Giả sử chúng ta đang kiểm tra một chuỗi trong A2, công thức hoàn chỉnh có hình dạng này:
=IF(ISNUMBER(VALUE(LEFT(A2, 1))), "Number", "Letter")
hoặc là
=IF(ISNUMBER(--LEFT(A2, 1)), "Number", "Letter")
Chức năng ISNUMBER cũng có ích cho trích xuất số từ một chuỗi. Đây là một ví dụ: Lấy số từ bất kỳ vị trí nào trong chuỗi.
Kiểm tra nếu một giá trị không phải là số
Mặc dù Microsoft Excel có một chức năng đặc biệt, ISNONTEXT, để xác định xem giá trị của một ô có phải là văn bản hay không, một chức năng tương tự cho các số bị thiếu.
Một giải pháp dễ dàng là sử dụng ISNUMBER kết hợp với KHÔNG trả về giá trị ngược lại của giá trị logic. Nói cách khác, khi ISNUMBER trả về ĐÚNG, KHÔNG chuyển đổi nó thành FALSE và ngược lại.
Để xem nó hoạt động, vui lòng quan sát kết quả của công thức sau:
=NOT(ISNUMBER(A2))
Một cách tiếp cận khác là sử dụng các hàm IF và ISNUMBER cùng nhau:
=IF(ISNUMBER(A2), "", "Not number")
Nếu A2 là số, công thức không trả về gì (một chuỗi trống). Nếu A2 không phải là số, công thức sẽ trả trước: “Không phải số”.
Nếu bạn muốn thực hiện một số tính toán với các số, sau đó đặt phương trình hoặc công thức khác vào value_if_true đối số thay vì một chuỗi rỗng. Ví dụ: công thức dưới đây sẽ nhân các số với 10 và mang lại “Không phải số” cho các giá trị không phải là số:
=IF(ISNUMBER(A2), A2*10, "Not number")
Kiểm tra xem một phạm vi có chứa bất kỳ số nào không
Trong trường hợp khi bạn muốn kiểm tra toàn bộ phạm vi cho các số, hãy sử dụng hàm ISNUMBER kết hợp với Sumproduct như thế này:
Sumproduct (- ISNUMBER (phạm vi))> 0
Sumproduct (ISNUMBER (phạm vi) * 1)> 0
Ví dụ: để tìm hiểu xem phạm vi A2: A5 có chứa bất kỳ giá trị số nào không, các công thức sẽ như sau:
=SUMPRODUCT(--ISNUMBER(A2:A5))>0
=SUMPRODUCT(ISNUMBER(A2:A5)*1)>0
Nếu bạn muốn xuất “Có” và “Không” thay vì TRUE và FALSE, hãy sử dụng câu lệnh IF làm “trình bao bọc” cho các công thức trên. Ví dụ:
=IF(SUMPRODUCT(--ISNUMBER(A2:A5))>0, "Yes", "No")
Công thức này hoạt động như thế nào
Tại trung tâm của công thức, hàm ISNUMBER đánh giá từng ô của phạm vi đã chỉ định, giả sử B2: B5 và trả về TRUE cho các số, FALSE cho bất kỳ thứ gì khác. Vì phạm vi chứa 4 ô, mảng có 4 phần tử:
{TRUE;FALSE;FALSE;FALSE}
Các hoạt động nhân hoặc đôi đơn (-) ép buộc TRUE và FALSE thành 1 và 0, tương ứng:
{1;0;0;0}
Hàm SUMPRODVEL cộng các phần tử của mảng. Nếu kết quả lớn hơn 0, điều đó có nghĩa là có ít nhất một số phạm vi. Vì vậy, bạn sử dụng “> 0” để có kết quả cuối cùng là TRUE hoặc FALSE.
ISNUMBER trong định dạng có điều kiện để làm nổi bật các ô có chứa văn bản nhất định
Nếu bạn đang tìm cách làm nổi bật các ô hoặc toàn bộ các hàng có chứa văn bản cụ thể, hãy tạo quy tắc định dạng có điều kiện dựa trên TÌM KIẾM (không phân biệt chữ hoa chữ thường) hoặc TÌM KIẾM (phân biệt chữ hoa chữ thường).
Trong ví dụ này, chúng tôi sẽ làm nổi bật các hàng dựa trên giá trị trong cột A. Chính xác hơn, chúng tôi sẽ làm nổi bật các mục có chứa từ “đỏ”. Đây là cách thực hiện:
- Chọn tất cả các hàng dữ liệu (A2: C6 trong ví dụ này) hoặc chỉ cột mà bạn muốn làm nổi bật các ô.
- Trên Trang Chủ trong tab Kiểu dáng nhóm, nhấp Quy tắc mới > Sử dụng công thức để xác định các ô cần định dạng.
- bên trong Định dạng giá trị trong đó công thức này là đúng hộp, nhập công thức dưới đây (xin lưu ý rằng tọa độ cột bị khóa bằng dấu $):
=ISNUMBER(SEARCH("red", $A2))
- Nhấn vào định dạng nút và chọn định dạng bạn muốn.
- Nhấn OK hai lần.
Nếu bạn có ít kinh nghiệm với định dạng có điều kiện Excel, bạn có thể tìm thấy các bước chi tiết với ảnh chụp màn hình trong hướng dẫn này: Cách tạo quy tắc định dạng có điều kiện dựa trên công thức.
Kết quả là, tất cả các mục của màu đỏ được tô sáng:
Thay vì “mã hóa” màu trong quy tắc định dạng có điều kiện, bạn có thể nhập nó vào một ô được xác định trước, giả sử là E2 và tham chiếu đến ô đó trong công thức của bạn (vui lòng lưu ý đến tham chiếu ô tuyệt đối $ E $ 2). Ngoài ra, bạn cần kiểm tra xem ô nhập liệu có trống không:
=AND(ISNUMBER(SEARCH($E$2, $A2)), $E$2<>"")
Kết quả là, bạn sẽ nhận được một quy tắc linh hoạt hơn làm nổi bật các hàng dựa trên đầu vào của bạn trong E2:
Đó là cách sử dụng hàm ISNUMBER trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của dịch vụ kế toán tại Hà Nội vào tuần tới!