Cách sử dụng và khắc phục lỗi Xác thực dữ liệu (Data Validation) trong Excel

Bài viết này sẽ hướng dẫn cho bạn cách tạo quy tắc Xác thực dữ liệu ( Data Validation ) tùy chỉnh trong Excel. Bạn sẽ tìm thấy một vài ví dụ về công thức xác thực dữ liệu Excel, hoặc chỉ cho phép nhập số hoặc văn bản trong những ô đơn cử, hay những văn bản khởi đầu bằng những ký tự đơn cử, ngăn trùng lặp, … và những khắc phục 1 số ít lỗi thường gặp khi sử dụng tính năng Data Validation .Đây là một hướng dẫn nâng cao, với những công thức và quy tắc xác thực tùy chỉnh. Nếu bạn chưa từng sử dụng tính năng Data Validation của Excel, hoặc muốn xem lại những tính năng cơ bản nhất của nó, sung sướng tìm hiểu thêm những bài viết bên dưới .Cách tạo quy tắc xác thực tùy chỉnh dựa trên công thức

Microsoft Excel có một số quy tắc xác thực dữ liệu tích hợp cho các số, ngày tháng và văn bản, nhưng chúng chỉ bao gồm các tình huống cơ bản nhất. Nếu bạn muốn xác thực các ô với tiêu chí của riêng mình, hãy tạo quy tắc xác thực tùy chỉnh dựa trên công thức. 

Bạn đang đọc: Cách sử dụng và khắc phục lỗi Xác thực dữ liệu (Data Validation) trong Excel

Dưới đây là những bước thực thi .

1. Chọn một hoặc nhiều ô để xác thực.
2. Mở hộp thoại Xác thực Dữ liệu. Đối với điều này, hãy nhấp vào nút Xác thực Dữ liệu trên tab Dữ liệu, trong nhóm Công cụ Dữ liệu hoặc nhấn tổ hợp phím Alt> D> L (mỗi phím cần được nhấn riêng biệt).
3. Trên tab Cài đặt của cửa sổ Data Validation, hãy chọn Custom trong hộp Allow box và nhập công thức xác thực dữ liệu của bạn vào hộp Formula box.
4. Bấm OK .

Bạn hoàn toàn có thể thêm thông tin nhập tùy chỉnh và cảnh báo nhắc nhở Lỗi sẽ hiển thị khi người dùng chọn ô đã xác thực hoặc nhập dữ liệu không hợp lệ tương ứng .Lưu ý :

• Tất cả các quy tắc xác thực dữ liệu Excel, được tích hợp sẵn và tùy chỉnh, chỉ xác minh dữ liệu mới được nhập vào một ô sau khi tạo quy tắc.
• Dữ liệu được sao chép không được xác thực, cũng không phải là dữ liệu đầu vào trong ô trước khi thực hiện quy tắc.
• Để ghim các mục nhập hiện có không đáp ứng tiêu chí xác thực dữ liệu của bạn, hãy sử dụng tính năng Circle Invalid Data bên dưới mục Data Validation để tìm dữ liệu không hợp lệ đã lọt vào trang tính của bạn trước khi bạn thêm xác thực dữ liệu.

Mục Chính

• Hướng dẫn tạo các quy tắc xác thực dữ liệu cho số và văn bản trong Excel
  • Chỉ cho phép nhập số trong Excel 
  • Chỉ cho phép nhập văn bản trong Excel
  • Cho phép văn bản bắt đầu bằng (các) ký tự cụ thể
  • Xác thực nhiều tiêu chí với Công thức Data validation tùy chỉnh
  • Công thức xác thực có phân biệt chữ hoa chữ thường
  • Chỉ cho phép nhập nội dung có bao gồm những ký tự, chữ cho trước
  • Chỉ cho phép nhập các giá trị duy nhất, không trùng lập trong Excel
• Hướng dẫn tạo công thức xác thực tùy chỉnh cho ngày và giờ
  • Chỉ cho phép nhập ngày trong khoản thời gian xác định
  • Chỉ cho phép nhập các ngày trong tuần hoặc cuối tuần
  • Chỉ cho phép nhập ngày trong quá khứ, hoặc ngày trong tương lai
  • Xác thực thời gian dựa trên thời gian hiện tại
• Cách khắc phục một số lỗi xác thực dữ liệu thường gặp trong Excel

Hướng dẫn tạo các quy tắc xác thực dữ liệu cho số và văn bản trong Excel

Chỉ cho phép nhập số trong Excel 

Đáng kinh ngạc là không có quy tắc xác thực dữ liệu Excel nào có sẵn nào Giao hàng cho một trường hợp rất nổi bật là khi bạn cần hạn chế người dùng chỉ nhập số trong những ô đơn cử .Nhưng bạn hoàn toàn có thể thuận tiện thực thi với một công thức xác thực dữ liệu tùy chỉnh dựa trên hàm ISNUMBER, như sau := ISNUMBER ( C2 )Trong đó C2 là ô trên cùng của dải ô bạn muốn xác thực .

Hàm ISNUMBER cho phép bất kỳ giá trị số nào trong các ô đã được xác thực, bao gồm số nguyên, số thập phân, phân số cũng như ngày và giờ, vì chúng được được xem là số trong Excel.
 

Chỉ cho phép nhập văn bản trong Excel

Nếu muốn làm điều ngược lại – để chỉ được cho phép những mục nhập văn bản trong khoanh vùng phạm vi ô đã cho, hãy kiến thiết xây dựng quy tắc tùy chỉnh với hàm ISTEXT, ví dụ := ISTEXT ( D2 )Trong đó D2 là ô trên cùng của dải ô đã chọn .

Cho phép văn bản bắt đầu bằng (các) ký tự cụ thể

Nếu toàn bộ những giá trị trong một khoanh vùng phạm vi nhất định phải mở màn bằng một ký tự hoặc chuỗi con đơn cử, bạn hãy tạo quy tức xác thực dữ liệu Excel tùy chỉnh dựa trên hàm COUNTIF với một ký tự đại diện thay mặt := COUNTIF ( cell, ” text * ” )Ví dụ : để bảo vệ rằng toàn bộ id đơn hàng trong cột A đều khởi đầu bằng tiền tố ” AA – “, ” aa – “, ” Aa – ” hoặc ” aA – ” ( không phân biệt chữ hoa chữ thường ), hãy xác lập quy tắc tùy chỉnh với công thức này trong data validation= COUNTIF ( A2, ” aa – * ” )

Xác thực nhiều tiêu chí với Công thức Data validation tùy chỉnh

Trong trường hợp có 2 hoặc nhiều tiền tố hợp lệ, hãy thêm một số ít hàm COUNTIF để quy tắc xác thực dữ liệu Excel của bạn hoạt động giải trí với logic OR := COUNTIF ( A2, ” aa – * ” ) + COUNTIF ( A2, ” bb – * ” )

Công thức xác thực có phân biệt chữ hoa chữ thường

Trong những trường hợp cần phân biệt chữ hoa, chữ thường, Bạn hãy sử dụng hàm EXACT phối hợp với hàm LEFT để tạo công thức xác thực phân biệt chữ hoa chữ thường cho những mục mở màn bằng văn bản đơn cử := EXACT ( LEFT ( cell, number_of_chars ), text )Ví dụ : để chỉ được cho phép những mục khởi đầu bằng ” AA – ” ( không được cho phép ” aa – ” và ” Aa – ” ), hãy sử dụng công thức này := EXACT ( LEFT ( A2, 3 ), ” AA – ” )Trong công thức trên, hàm LEFT trích xuất 3 ký tự tiên phong từ ô A2 và EXACT triển khai so sánh phân biệt chữ hoa chữ thường với chuỗi con được viết hoa ( ” AA – ” trong ví dụ này ). Nếu hai chuỗi con khớp đúng mực, công thức trả về TRUE và quy trình xác nhận được chuyển sang ; nếu không thì trả về FALSE và việc xác thực không thành công xuất sắc .

Chỉ cho phép nhập nội dung có bao gồm những ký tự, chữ cho trước

Để cho phép những mục nhập chứa văn bản đơn cử ở bất kể vị trí nào trong ô ( ở đầu, giữa hoặc cuối ), hãy sử dụng hàm ISNUMBER phối hợp với FIND hoặc SEARCH tùy thuộc vào việc bạn muốn đối sánh tương quan phân biệt chữ hoa chữ thường hay không phân biệt chữ hoa chữ thường :Xác thực không phân biệt chữ hoa chữ thường :ISNUMBER ( SEARCH ( text, cell ) )Xác thực phân biệt chữ hoa chữ thường :ISNUMBER ( FIND ( text, cell ) )Trên tập dữ liệu mẫu của tất cả chúng ta, để chỉ được cho phép những mục nhập chứa văn bản ” AA ” trong những ô A2 : A6, hãy sử dụng một trong những công thức sau :Trường hợp không phân biệt chữ hoa chữ thường := ISNUMBER ( SEARCH ( ” AA “, A2 ) )Trường hợp có phân biệt chữ hoa chữ thường .= ISNUMBER ( FIND ( ” AA “, A2 ) )Các công thức trên hoạt động giải trí với logic sau :

• Bạn tìm kiếm chuỗi con “AA” trong ô A2 bằng cách sử dụng FIND hoặc SEARCH và cả hai đều trả về vị trí của ký tự đầu tiên trong chuỗi con.
• Nếu văn bản không được tìm thấy, một lỗi sẽ được trả về.
• Đối với bất kỳ giá trị số nào được trả về là kết quả của tìm kiếm, hàm ISNUMBER cho kết quả là TRUE và xác thực dữ liệu thành công.
• Trong trường hợp có lỗi, ISNUMBER trả về FALSE và mục nhập sẽ không được phép trong một ô.

Chỉ cho phép nhập các giá trị duy nhất, không trùng lập trong Excel

Trong những trường hợp khi một cột hoặc một dải ô nhất định không được chứa bất kể giá trị trùng lập nào, hãy định thông số kỹ thuật quy tắc xác thực dữ liệu tùy chỉnh để chỉ cho phép nhập những giá trị duy nhất. Để triển khai, tất cả chúng ta sẽ sử dụng công thức COUNTIF cổ xưa để xác lập những bản trùng lập := COUNTIF ( range, topmost_cell ) < = 1Ví dụ : để bảo vệ rằng chỉ những id số thứ tự duy nhất được nhập vào những ô từ A2 đến A6, hãy tạo quy tắc tùy chỉnh với công thức xác thực dữ liệu sau := COUNTIF ( $ A $ 2 : USD A $ 6, A2 ) < = 1Khi một giá trị duy nhất được nhập, công thức trả về TRUE và quy trình xác thực thành công xuất sắc. Nếu giá trị tương tự như đã sống sót trong khoanh vùng phạm vi được chỉ định ( số lượng lớn hơn 1 ), COUNTIF trả về FALSE và nguồn vào không xác thực được .Lưu ý

• chúng ta khóa phạm vi bằng tham chiếu ô tuyệt đối (A $ 2: $ A $ 6) và sử dụng tham chiếu tương đối cho ô trên cùng (A2) để có công thức điều chỉnh đúng cho từng ô trong phạm vi đã xác thực.
   
• Công thức này không phân biệt chữ hoa và chữ thường.

Hướng dẫn tạo công thức xác thực tùy chỉnh cho ngày và giờ

Công cụ xác thực ngày tháng có sẵn trong Excel cung cấp khá nhiều tiêu chí được xác định trước để hạn chế người dùng chỉ nhập các ngày giữa hai ngày bạn chỉ định, lớn hơn, nhỏ hơn hoặc bằng một ngày nhất định.

Xem thêm: 5 Giai đoạn vàng phát triển trí não của trẻ mẹ đừng bỏ lỡ

Nếu bạn muốn trấn áp nhiều hơn việc xác thực dữ liệu trong trang tính của mình, bạn hoàn toàn có thể sao chép công dụng có sẵn với quy tắc tùy chỉnh hoặc viết công thức của riêng bạn để lan rộng ra hơn nữa những tính năng hiện có .

Chỉ cho phép nhập ngày trong khoản thời gian xác định

Để số lượng giới hạn mục nhập vào một ngày trong một khoanh vùng phạm vi được chỉ định, bạn hoàn toàn có thể sử dụng quy tắc Ngày được xác lập trước với tiêu chuẩn ” Between ” hoặc tạo quy tắc xác thực tùy chỉnh với công thức bên dưới := AND ( cell > = start_date ), cell < = end_date )Trong đó

• Cell là ô trên cùng trong dải ô đã được xác thực và
• ngày bắt đầu và ngày kết thúc là ngày hợp lệ được cung cấp thông qua hàm DATE hoặc tham chiếu đến các ô chứa ngày.

Ví dụ : để chỉ được cho phép những ngày trong tháng 7 của năm 2017, hãy sử dụng công thức sau := AND ( C2 > = DATE ( 2017,7,1 ), C2 < = DATE ( 2017,7,31 ) )Hoặc, nhập ngày mở màn và ngày kết thúc vào một số ít ô ( ví dụ như F1 và F2 ) và tham chiếu những ô đó trong công thức của bạn := AND ( C2 > = USD F USD 1, C2 < = $ F USD 2 )

Chỉ cho phép nhập các ngày trong tuần hoặc cuối tuần

Để hạn chế người dùng chỉ được phép nhập những ngày trong tuần hoặc cuối tuần, hãy định thông số kỹ thuật quy tắc xác thực tùy chỉnh dựa trên hàm WEEKDAY .Với đối số return_type được đặt thành 2, WEEKDAY trả về một số nguyên trong khoảng chừng từ 1 ( Thứ Hai ) đến 7 ( Chủ Nhật ). Vì vậy, so với những ngày trong tuần ( Thứ Hai đến Thứ Sáu ), hiệu quả của công thức phải nhỏ hơn 6 và so với những ngày cuối tuần ( Thứ Bảy và Chủ Nhật ) lớn hơn 5 .Chỉ cho phép nhập ngày thao tác := WEEKDAY ( ô, 2 ) < 6Chỉ được cho phép những ngày cuối tuần := WEEKDAY ( ô, 2 ) > 5Ví dụ : để chỉ cho phép nhập ngày thao tác trong những ô C2 : C6, hãy sử dụng công thức sau := WEEKDAY ( C2, 2 ) < 6

Chỉ cho phép nhập ngày trong quá khứ, hoặc ngày trong tương lai

Trong nhiều trường hợp, bạn hoàn toàn có thể muốn sử dụng ngày ngày hôm nay làm mốc khởi đầu của khoanh vùng phạm vi ngày được phép, để người dùng chỉ hoàn toàn có thể nhập ngày tương lai, hoặc ngày trong quá khứ .Để lấy ngày hiện tại, hãy sử dụng hàm TODAY, sau đó thêm số ngày mong ước vào đó để tính ngày kết thúc .Ví dụ : để số lượng giới hạn mục nhập dữ liệu trong 6 ngày kể từ giờ đây ( 7 ngày kể cả thời điểm ngày hôm nay ), tất cả chúng ta sẽ sử dụng quy tắc Ngày tích hợp với tiêu chuẩn dựa trên công thức :

1. Chọn Date trong Allow
2. Chọn Between trong Data
3. Trong hộp Start date, hãy nhập =TODAY()
4. Trong hộp End date, hãy nhập=TODAY() + 6

Theo cách tương tự như, bạn hoàn toàn có thể hạn chế người dùng nhập ngày trước hoặc sau ngày ngày hôm nay. Để thực thi, bạn hãy chọn less than hoặc greater than trong hộp Dữ liệu, rồi nhập công thức = TODAY ( ) tương ứng cho ngày kết thúc hoặc ngày khởi đầu .

Xác thực thời gian dựa trên thời gian hiện tại

Để xác thực dữ liệu dựa trên thời hạn hiện tại, hãy sử dụng quy tắc Thời gian được xác lập trước với công thức xác thực dữ liệu của riêng bạn :

1. Trong hộp Allow box, chọn Time.
2. Trong hộp Dữ liệu, hãy chọn nhỏ hơn để chỉ cho phép thời gian trước thời điểm hiện tại hoặc lớn hơn để cho phép thời gian sau thời điểm hiện tại.
3. Trong hộp Thời gian kết thúc hoặc Thời gian bắt đầu (tùy thuộc vào tiêu chí bạn đã chọn ở bước trước), hãy nhập một trong các công thức sau.

Để xác thực ngày và giờ dựa trên ngày và giờ hiện tại := NOW ( )Để xác thực thời hạn dựa trên thời hạn hiện tại := TIME ( HOUR ( NOW ( ) ), MINUTE ( NOW ( ) ), SECOND ( NOW ( ) ) )Ảnh chụp màn hình hiển thị bên dưới hiển thị quy tắc chỉ được cho phép lớn hơn thời hạn hiện tại .

Cách khắc phục một số lỗi xác thực dữ liệu thường gặp trong Excel

Nếu quy tắc xác thực dữ liệu dựa trên công thức của bạn không hoạt động giải trí như mong đợi, có 3 điểm chính cần kiểm tra :

• Công thức xác thực dữ liệu có chính xác hay không
• Công thức xác thực có tham chiếu đến ô không chứa giá trị hay không
• Các tham chiếu có được sử dụng chính xác không

1. Kiểm tra công thức xác thực dữ liệu Excel của bạn xem đã đúng chuẩn chưa .Đối với người mới khởi đầu, hãy sao chép công thức xác thực của bạn vào một ô, để bảo vệ rằng công thức đó không trả về lỗi như # N / A, # VALUE hoặc # DIV / 0 ! .Nếu bạn đang tạo quy tắc tùy chỉnh, công thức phải trả về những giá trị logic là TRUE và FALSE hoặc những giá trị 1 và 0 tương ứng với chúng .Nếu bạn sử dụng tiêu chuẩn dựa trên công thức trong quy tắc tích hợp ( giống như chúng tôi đã làm để xác thực thời hạn dựa trên thời hạn hiện tại ), công thức đó cũng hoàn toàn có thể trả về một giá trị số khác .Trong nhiều trường hợp, nếu bạn chọn hộp Bỏ qua trống khi xác lập quy tắc ( thường được chọn theo mặc định ) và một hoặc nhiều ô được tham chiếu trong công thức của bạn bị trống, bất kể giá trị nào sẽ được phép trong ô đã xác thực .Đây là một ví dụ ở dạng đơn thuần nhất :2. Tham chiếu ô tuyệt đối và tương đối trong công thức xác thực dữ liệuKhi thiết lập quy tắc xác thực Excel dựa trên công thức, hãy nhớ rằng toàn bộ những tham chiếu ô trong công thức của bạn đều có tương quan đến ô phía trên bên trái trong khoanh vùng phạm vi đã chọn .Nếu bạn đang tạo quy tắc cho nhiều ô và tiêu chuẩn xác thực của bạn nhờ vào vào những ô đơn cử, hãy bảo vệ sử dụng tham chiếu ô tuyệt đối ( với dấu USD như $ A $ 1 ), nếu không quy tắc của bạn sẽ chỉ hoạt động giải trí đúng mực cho ô tiên phong. Để minh họa rõ hơn điểm này, sung sướng xem xét ví dụ sau .Giả sử, bạn muốn số lượng giới hạn mục nhập dữ liệu trong ô D2 đến D5 thành những số nguyên giữa 1 ( giá trị nhỏ nhất ) và tác dụng của phép chia A2 cho B2. Vì vậy, bạn tính giá trị lớn nhất với công thức đơn thuần này = A2 / B2, như được hiển thị trong ảnh chụp màn hình hiển thị bên dưới :Vấn đề là công thức có vẻ như đúng này sẽ không hoạt động giải trí cho những ô D3 đến D5 vì những tham chiếu tương đối biến hóa dựa trên vị trí tương đối của những hàng và cột. Vì vậy, so với ô D3, công thức sẽ biến hóa thành = A3 / B3, và so với D4, nó sẽ trở thành = A4 / B4, việc xác thực dữ liệu đều sai !

Để khắc phục lỗi này, ban chỉ cần gõ “$” các sổ cột và hàng để cố định chúng: =$A$2/$B$2. Hoặc, nhấn F4 để chuyển đổi giữa các loại tham chiếu khác nhau.

Xem thêm: Giáo án dạy học Toán 11 theo định hướng phát triển phẩm chất năng lực – https://thomaygiat.com

Trong trường hợp bạn muốn xác thực từng ô dựa trên tiêu chuẩn riêng của từng ô, hãy sử dụng tham chiếu ô tương đối không có dấu USD để nhận công thức kiểm soát và điều chỉnh cho từng hàng hoặc / và cột :Như bạn thấy, không có ” công thức đúng tuyệt đối “, cùng một công thức hoàn toàn có thể đúng hoặc sai tùy thuộc vào trường hợp và trách nhiệm đơn cử của bạn .

Đây là cách sử dụng xác thực dữ liệu trong Excel với các công thức của tùy chỉnh để tạo quy tắc xác thực riêng cho bạn. Bên cạnh đó, để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng hữu ích khác, hãy tham gia Gitiho ngay hôm nay.
 

Source: https://thomaygiat.com
Category : Kỹ Thuật Số