Thực đơn
trang chủSự liên quan

Giá trị đã nhập không hợp lệ. Cách ngăn nhập giá trị không chính xác vào Excel bằng xác thực dữ liệu. Thiết lập điều kiện kiểm tra

Rất dễ mắc lỗi khi nhập nhiều thông tin vào ô bảng. Trong EXCEL có công cụ kiểm tra dữ liệu nhập ngay sau khi nhấn phím ENTER - Data Validation.

Dụng cụ Kiểm tra dữ liệu() sẽ không phổ biến nếu chức năng của nó chỉ giới hạn ở việc xác minh. Các kỹ thuật cơ bản để làm việc với công cụ này được liệt kê ngắn gọn dưới đây.

A. Kiểm tra các giá trị đã nhập

Như bạn có thể thấy trong hình bên dưới, các điều kiện để kiểm tra giá trị đã nhập có thể được cấu hình rất linh hoạt.

Bạn chỉ có thể cho phép nhập các giá trị vào một ô thuộc một loại nhất định bằng cách chọn điều kiện bắt buộc từ danh sách thả xuống:

  • số nguyên. Chỉ các số nguyên thuộc một phạm vi nhất định mới được phép nhập vào ô;
  • Có hiệu lực. Chỉ có thể nhập số vào ô, kể cả những số có phần thập phân (bạn không thể nhập văn bản nhưng có thể nhập ngày);
  • Ngày của. Giả định rằng các ngày từ 01/01/1900 đến 31/12/9999 sẽ được nhập vào ô. Đọc thêm về định dạng Date trong bài viết
  • Thời gian. Giả sử rằng trong ô có Séc này thuộc loại này thời gian sẽ được nhập. Ví dụ: hình bên dưới hiển thị một điều kiện khi được phép nhập thời gian vào một ô chỉ thuộc nửa sau của ngày, tức là. từ 12:00:00 đến 23:59:59. Thay vì nhập giá trị 12:00:00 một cách tẻ nhạt, bạn có thể sử dụng số tương đương 0,5. Khả năng nhập số thay vì thời gian xuất phát từ thực tế là bất kỳ ngày nào trong EXCEL đều được liên kết với một số nguyên dương và do đó thời gian (vì nó là một phần của ngày) tương ứng với phần phân số của số (ví dụ: 0,5 là buổi trưa). Số tương đương với 23:59:59 sẽ là 0,99999.

  • Độ dài văn bản. Chỉ có thể nhập một số ký tự nhất định vào một ô. Với hạn chế này, bạn có thể nhập cả số và ngày, điều chính là số ký tự được nhập không mâu thuẫn với giới hạn độ dài văn bản. Ví dụ: nếu số ký tự bị giới hạn dưới 5, bạn không thể nhập ngày muộn hơn 13/10/2173, bởi vì nó tương ứng với số 99999 và 14/10/2173 đã là 100000, tức là. 6 ký tự. Điều thú vị là, chẳng hạn, nếu bạn giới hạn ít hơn 5 ký tự, bạn sẽ không thể nhập công thức =ROOT(2) vào ô, bởi vì kết quả =1.4142135623731 (tùy thuộc vào độ chính xác được chỉ định trong EXCEL), nhưng =ROOT(4) - bạn có thể làm được, vì kết quả =2 và đây chỉ là 1 ký tự.
  • Danh sách. Có lẽ là loại dữ liệu thú vị nhất. Trong trường hợp này, việc nhập các giá trị vào một ô có thể bị giới hạn trong danh sách được xác định trước đó. Ví dụ: nếu bạn chỉ định đơn vị đo lường của sản phẩm làm nguồn, được phân tách bằng dấu chấm phẩy chiếc;kg;sq.m;cub.m, khi đó bạn sẽ không thể chọn bất cứ thứ gì ngoài 4 giá trị này từ danh sách. Trong nguồn, bạn có thể chỉ định một phạm vi ô chứa danh sách được tạo trước hoặc liên kết đến . Một ví dụ được đưa ra trong bài viết
  • Khác. Ô được phép nhập các giá trị thỏa mãn tiêu chí phức tạp hơn. Để đặt tiêu chí, bạn phải sử dụng công thức. Chúng ta hãy xem xét điều kiện này chi tiết hơn.

Khi được chọn Khác, trong lĩnh vực Công thức bạn cần nhập công thức để tính giá trị logic. Nếu nó trả về TRUE thì giá trị đó được phép nhập vào ô; nếu nó là FALSE thì giá trị của bạn không thể được nhập. Là một đối số công thức, bạn cần sử dụng tham chiếu đến chính ô mà nó được áp dụng. Kiểm tra dữ liệu hoặc một tham chiếu đến một ô phụ thuộc vào nó. Ví dụ,

ĐẾN

Nhập công thức

Giải trình

Tế bào B2 chỉ chứa văn bản

ITIN(B2)

TRONG Loại dữ liệu không có tùy chọn để chọn loại Chữ, vì vậy chúng ta phải đạt được điều này một cách gián tiếp. Bạn có thể Xác minh dữ liệuáp dụng trực tiếp vào tế bào B2

Cho phép nhập giá trị vào ô B1 chỉ khi sau khi nhập giá trị vào ô D1 sẽ hơn 100, trong D2 dưới 400

VÀ(D1>100;D2<400)

Xác minh dữ liệuáp dụng cho ô B1 . Hơn nữa, trong tế bào D1 công thức =B1*2 đã được giới thiệu và trong D2 – công thức =B1*3. Mặc dù công thức này tương đương với ràng buộc Có hiệu lực với phạm vi từ 50 đến 133,33, nhưng với các kết nối ô phức tạp hơn, kỹ thuật này có thể hữu ích

Giá trị trong ô chứa tuổi của nhân viên ( C1 ), phải luôn lớn hơn số năm làm việc trọn vẹn ( D1 ) cộng 18 (tuổi tối thiểu để làm việc)

=IF(C1>D1+18,TRUE,FALSE)

Khi điền vào bảng dữ liệu về độ tuổi và kinh nghiệm làm việc, bạn có thể đặt dấu kiểm này cho cả hai ô ( C1 Và D1 ). Để làm điều này, bạn cần chọn 2 ô cùng một lúc, gọi Xác minh dữ liệu và sửa đổi một chút công thức =IF($C1>$D1+18,TRUE,FALSE)

Tất cả dữ liệu trong một phạm vi ô A1:A20 giá trị chứa

=COUNTIF($A$1:$A$20,A1)=1

=MATCH(A1,$A:$A,0)=ROW(A1)

Các ô cần được chọn A1:A20 , gọi công cụ Kiểm tra dữ liệu và nhập công thức. Công thức thứ hai có thể được sử dụng cho toàn bộ cột MỘT , để làm điều này, bạn không cần chọn phạm vi mà là toàn bộ cột MỘT

Giá trị trong ô chứa tên mã sản phẩm ( B5 ), luôn bắt đầu bằng tiền tố tiêu chuẩn "ID-" và dài ít nhất 10 ký tự.

=AND(LEFT(B5,3)="ID-"; LENGTH(B5)>9)

Chúng tôi nhập xác minh dữ liệu cho ô B5

Khi chọn một số ô, nếu cần, đừng quên cung cấp tham chiếu tuyệt đối cho các ô (ví dụ: $A$1:$A$20 ).

Khi sử dụng công cụ Kiểm tra dữ liệu, giả định rằng các hằng số sẽ được nhập vào ô ( 123, sản phẩm1, 01/05/2010 v.v.), mặc dù không ai cấm nhập công thức. Trong trường hợp này, kết quả tính toán công thức vẫn sẽ được kiểm tra. Nói chung, tôi không khuyên bạn nên nhập công thức vào các ô có xác minh dữ liệu - rất dễ bị nhầm lẫn. Trong trường hợp này, tôi khuyên bạn nên sử dụng .

TRONG.Hiển thị nhận xét nếu ô là ô hiện tại.

Sử dụng tab Thông báo đầu rađể hiển thị bình luận.

Không giống như ghi chú thông thường ( Đánh giá/Nhận xét/Tạo nhận xét), biến mất sau khi con trỏ chuột rời khỏi ô (nếu tùy chọn không hoạt động Hiển thị tất cả ghi chú), nhận xét này được hiển thị bất cứ khi nào một ô được chọn.

VỚI.Hiển thị thông báo lỗi chi tiết.

Sau khi nhập sai giá trị Kiểm tra dữ liệu có thể hiển thị thông báo chi tiết về những gì đã làm sai. Đây là một loại tương tự Hộp thư() từ VBA.

D.Tạo phạm vi liên kết (danh sách)

E. Sử dụng tham chiếu tới các sheet khác trong quy tắc

Trong Excel 2007 Xác minh dữ liệu, chẳng hạn như, bạn không thể chỉ định trực tiếp một liên kết đến các phạm vi của một trang tính khác, chẳng hạn như =Sheet2!$A$1 . Cho phép bạn bỏ qua giới hạn này bằng cách sử dụng .

Nếu ở Xác minh dữ liệu ví dụ, bạn cần tạo một tham chiếu đến một ô A1 một trang tính khác thì trước tiên bạn cần xác định cho ô đó rồi tham chiếu đến ô đó Tên trong quy tắc Xác minh dữ liệu.

Mặt khác, trong Excel 2010, bạn có thể sử dụng các quy tắc xác thực dữ liệu để tham chiếu các giá trị trong các trang tính khác. Trong Excel 2007 và Excel 97-2003, kiểu xác thực dữ liệu này không được hỗ trợ và không xuất hiện trên trang tính. Tuy nhiên, tất cả các quy tắc xác thực dữ liệu vẫn có sẵn trong sổ làm việc và được áp dụng khi bạn mở lại sổ làm việc trong Excel 2010 trừ khi chúng được thay đổi trong Excel 2007 hoặc Excel 97-2003.

F. Cách thức hoạt động của tính năng Kiểm tra lý lịch

Kiểm tra dữ liệuđược kích hoạt rõ ràng khi các giá trị được nhập vào một ô từ bàn phím và sau đó nhấn một phím ĐI VÀO. Trong trường hợp này, một cửa sổ mô tả lỗi sẽ xuất hiện.

Nếu các giá trị được chèn qua Bảng nhớ tạm (Dán giá trị) hoặc sử dụng phím tắt ĐIỀU KHIỂN+ D(sao chép giá trị từ ô từ trên xuống) hoặc được sao chép từ trên xuống dưới thì việc kiểm tra không được thực hiện rõ ràng. Ngoài ra, khi sao chép giá trị, bạn có thể vô tình xóa hoàn toàn các quy tắc Kiểm tra dữ liệu, ví dụ: nếu ô nguồn không được xác định Kiểm tra dữ liệu và dữ liệu từ nó được chèn thông qua Bảng nhớ tạm và sử dụng tổ hợp phím CTRL+V.

Hãy giải thích bằng một ví dụ. Giả sử đến ô A1 áp dụng Kiểm tra dữ liệu phải xác minh Khác, trong đó =ROW(A1)=1 được nhập vào trường công thức, tức là. đối với tất cả các ô từ hàng đầu tiên, điều kiện Kiểm tra dữ liệu sẽ lấy giá trị TRUE, đối với các hàng khác - FALSE, bất kể nội dung của ô.

Bây giờ hãy chọn ô A2 và hãy nhấn CTRL+D. Giá trị từ A1 sẽ được sao chép vào A2 cùng với điều kiện Kiểm tra dữ liệu. Mặc dù thực tế hiện nay điều kiện Kiểm tra dữ liệu sẽ đánh giá là FALSE và không có thông báo cảnh báo nào được hiển thị. Để đảm bảo rằng dữ liệu trong các ô đáp ứng các điều kiện được xác định trong Xác minh dữ liệu, bạn cần gọi lệnh menu Khoanh tròn thông tin sai (). Các ô có dữ liệu không chính xác sẽ được viền bằng hình bầu dục màu đỏ. Bây giờ hãy chọn lại ô A2 và nhấn phím F2(vào chế độ Chỉnh sửa), sau đó bấm vào ĐI VÀO- một cửa sổ sẽ xuất hiện với thông báo rằng giá trị đã nhập không chính xác.

Có một cách khác để bỏ qua việc kiểm tra lý lịch. Giả sử dữ liệu nhập vào một ô bị giới hạn ở các giá trị từ 1 đến 3. Bây giờ vào bất kỳ ô nào khác mà không có Kiểm tra dữ liệu nhập giá trị 4. Chọn ô này, trong Thanh công thức chọn giá trị 4 và sao chép nó vào Bảng nhớ tạm. Bây giờ hãy chọn ô có Xác minh dữ liệu và hãy nhấn CTRL+V. Giá trị đã được chèn vào ô! Bên cạnh đó, Kiểm tra dữ liệu vẫn không bị ảnh hưởng, không giống như trường hợp khi, thông qua Bảng nhớ tạm, ví dụ: một giá trị từ WORD được chèn vào. Để đảm bảo rằng dữ liệu trong ô không đáp ứng các điều kiện được xác định trong Xác minh dữ liệu, bạn cần gọi lệnh menu Khoanh tròn thông tin sai (Dữ liệu/ Xử lý dữ liệu/ Xác thực dữ liệu/ Khoanh tròn dữ liệu không chính xác).

G. Tìm kiếm ô có Xác thực dữ liệu

Nếu có nhiều ô trên trang tính với Xác minh dữ liệu, thì bạn có thể sử dụng công cụ ( Trang chủ/ Tìm và Chọn/ Chọn một nhóm ô).

Lựa chọn Kiểm tra dữ liệu Công cụ này cho phép bạn chọn các ô cần xác thực dữ liệu (được chỉ định bằng lệnh Dữ liệu / Làm việc với dữ liệu / Xác thực dữ liệu). Khi chọn công tắc Mọi người tất cả các ô như vậy sẽ được chọn. Khi chọn tùy chọn Những cái này như nhau Chỉ những ô đó mới được đánh dấu để đặt quy tắc xác thực dữ liệu tương tự như cho ô hiện hoạt.

Giả sử rằng bạn cần nhập các giá trị có kích thước giới hạn nghiêm ngặt vào một số ô nhất định. Ví dụ: TIN phải bao gồm 10 ký tự.

Nhưng thông thường, khi làm việc vội vàng, các lỗi ở dạng ít nhiều ký tự sẽ len lỏi vào dữ liệu đó. Do đó, đối với một số ô nhất định, bạn có thể giới hạn số lượng ký tự được nhập ở một số nhất định.

Đối với điều này:

1. Chọn các ô (bạn có thể chọn toàn bộ hàng, cột)

2. Trong Menu chọn “Dữ liệu - Kiểm tra”.

3. Trong cửa sổ hiện ra
trong cột “Loại dữ liệu”, chọn “độ dài văn bản”,
trong cột “Giá trị” - “bằng”,
trong cột “Độ dài”, chúng tôi chỉ ra “10” (đối với TIN).

4. Trong tab “Thông báo lỗi”, bạn có thể đặt tiêu đề và thông báo riêng về lỗi này. Ví dụ:

5. Sau khi nhập nội dung tin nhắn, nhấn OK.

Bây giờ, nếu bạn cố gắng nhập các giá trị có độ dài không bằng 10 ký tự vào các ô bạn đã chỉ định, một thông báo lỗi sẽ xuất hiện.

Hơn nữa, sẽ không thể nhập dữ liệu có kích thước khác.

Nếu giới hạn độ dài không quan trọng thì trong tab “thông báo lỗi”, hãy chọn “cảnh báo” thay vì “dừng” làm mặc định.

Bây giờ thông báo lỗi sẽ xuất hiện, nhưng bạn sẽ có cơ hội nhập các giá trị có kích thước khác.

Nếu bạn đang tạo một trang tính yêu cầu người dùng nhập dữ liệu, bạn có thể giới hạn đầu vào ở một phạm vi ngày hoặc số cụ thể hoặc đảm bảo rằng chỉ nhập số nguyên dương. Excel có thể hạn chế việc nhập dữ liệu vào một số ô nhất định bằng cách xác thực dữ liệu, nhắc bạn nhập dữ liệu hợp lệ khi một ô được đánh dấu và hiển thị thông báo lỗi khi người dùng nhập dữ liệu không hợp lệ.

Hạn chế nhập dữ liệu

Hạn chế nhập dữ liệu

Nhắc các mục nhập hợp lệ

Khi người dùng bấm vào ô có yêu cầu nhập dữ liệu, bạn có thể hiển thị thông báo giải thích dữ liệu nào hợp lệ.

Hiển thị thông báo lỗi khi nhập dữ liệu không hợp lệ

Nếu bạn có các hạn chế về dữ liệu và người dùng nhập dữ liệu không hợp lệ vào một ô, bạn có thể hiển thị thông báo mô tả lỗi.

Thêm xác thực dữ liệu vào một ô hoặc phạm vi ô

Ghi chú: Hai bước đầu tiên trong phần này là để thêm bất kỳ loại xác thực dữ liệu nào. Bước 3-7 dành riêng cho việc tạo danh sách thả xuống.

    Chọn một hoặc nhiều ô mà bạn muốn áp dụng kiểm tra.

    Trên tab Dữ liệu trong nhóm Làm việc với dữ liệu nhấn vào nút Kiểm tra dữ liệu.

    Trên tab Tùy chọn Trong chuong Cho phép nhấn vào nút Danh sách.

    Trong lĩnh vực Nguồn nhập các giá trị cách nhau bằng dấu chấm phẩy. Ví dụ: nhập " thấp trung bình cao".

    Đảm bảo hộp kiểm được chọn Danh sách các giá trị hợp lệ. Nếu không, mũi tên thả xuống sẽ không xuất hiện bên cạnh ô.

    Để chỉ định cách xử lý các giá trị trống (null), hãy chọn hoặc xóa hộp kiểm Bỏ qua các ô trống.

    Kiểm tra xem việc xác thực dữ liệu có hoạt động chính xác không. Hãy thử nhập dữ liệu hợp lệ và dữ liệu không hợp lệ vào một ô để đảm bảo rằng cài đặt xác thực được áp dụng như bạn muốn và thông báo của bạn xuất hiện vào đúng thời điểm.

Ghi chú:

Sử dụng các loại xác thực dữ liệu khác

Bảng liệt kê các loại xác thực dữ liệu khác và cách áp dụng chúng cho dữ liệu trong trang tính.

Thực hiện như sau:

Chỉ cho phép nhập số nguyên từ một phạm vi cụ thể

    Trên danh sách Cho phép chọn giá trị số nguyên.

    Trong lĩnh vực Dữ liệu chọn loại hạn chế cần thiết. Ví dụ: để đặt giới hạn trên và giới hạn dưới, hãy chọn Giới hạn Phạm vi.

    Nhập giá trị được phép tối thiểu, tối đa hoặc cụ thể.

    Bạn cũng có thể nhập công thức trả về một giá trị số.

    Ví dụ: giả sử bạn đang kiểm tra các giá trị trong ô F1. Để đặt số tiền khấu trừ tối thiểu bằng giá trị của ô này nhân với 2, hãy chọn Nhiều hơn hoặc bằng trong lĩnh vực Dữ liệu và nhập công thức =2*F1 trong lĩnh vực Giá trị tối thiểu.

Chỉ cho phép nhập số thập phân trong một phạm vi nhất định

Chỉ cho phép nhập ngày trong khoảng thời gian nhất định

    Thực hiện theo các bước 1-2 ở trên.

    Trong lĩnh vực Cho phép chọn giá trị ngày.

    Trong lĩnh vực Dữ liệu chọn loại hạn chế cần thiết. Ví dụ: để cho phép ngày sau một ngày cụ thể, hãy chọn hạn chế Hơn.

    Nhập ngày bắt đầu, ngày kết thúc hoặc ngày được phép cụ thể.

    Bạn cũng có thể nhập công thức trả về một ngày. Ví dụ: để đặt khoảng thời gian giữa ngày hiện tại và một ngày sau ngày hiện tại 3 ngày, hãy chọn Giữa trong lĩnh vực Dữ liệu, sau đó nhập =HÔM NAY() trong lĩnh vực ngày bắt đầu và sau đó nhập =HÔM NAY()+3 trong lĩnh vực Ngày hoàn thành.

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

    Thực hiện theo các bước 1-2 ở trên.

    Trong lĩnh vực Cho phép chọn giá trị Thời gian.

    Trong lĩnh vực Dữ liệu chọn loại hạn chế cần thiết. Ví dụ: để cho phép thời gian cho đến một thời điểm cụ thể trong ngày, hãy chọn hạn chế ít hơn.

    Chỉ định thời gian bắt đầu, thời gian kết thúc hoặc thời gian cụ thể để cho phép. Nếu bạn muốn nhập thời gian chính xác, hãy sử dụng định dạng hh:mm.

    Ví dụ: nếu ô E2 có thời gian bắt đầu là 8 giờ sáng và ô F2 có thời gian kết thúc là 5 giờ chiều và bạn muốn giới hạn các cuộc họp trong thời gian đó, hãy chọn giữa trong lĩnh vực Dữ liệu và sau đó nhập =E2 trong lĩnh vực Thời gian bắt đầu Và =F2 trong lĩnh vực Thời gian kết thúc.

Chỉ cho phép nhập văn bản có độ dài nhất định

    Thực hiện theo các bước 1-2 ở trên.

    Trong lĩnh vực Cho phép chọn giá trị Độ dài văn bản.

    Trong lĩnh vực Dữ liệu chọn loại hạn chế cần thiết. Ví dụ: để đặt một số ký tự nhất định, hãy chọn giới hạn Ít hơn hoặc bằng.

    Trong trường hợp này, chúng tôi muốn giới hạn đầu vào ở 25 ký tự, vì vậy hãy chọn hộp ít hơn hoặc bằng trong lĩnh vực dữ liệu và nhập 25 trong lĩnh vực tối đa .

Tính giá trị hợp lệ dựa trên nội dung của ô khác

    Thực hiện theo các bước 1-2 ở trên.

    Trong lĩnh vực Cho phép chọn kiểu dữ liệu cần thiết.

    Trong lĩnh vực Dữ liệu chọn loại hạn chế cần thiết.

    Trong trường hoặc các trường nằm bên dưới trường Dữ liệu, hãy chọn ô bạn muốn sử dụng để xác định các giá trị hợp lệ.

    Ví dụ: để chỉ cho phép nhập thông tin hóa đơn khi tổng không vượt quá ngân sách trong ô E1, hãy chọn Số chữ số thập phân trong danh sách Cho phép, ràng buộc "Nhỏ hơn hoặc bằng" nằm trong danh sách "Dữ liệu" và trong trường Gia trị lơn nhât nhập >= =E1.

Ví dụ về công thức xác thực dữ liệu

Ghi chú:

    Các ví dụ bên dưới sử dụng tùy chọn tùy chỉnh khi tạo công thức có điều kiện. Trong trường hợp này, nội dung của trường "Dữ liệu" không thành vấn đề.

    Ảnh chụp màn hình trong bài viết này là từ Excel 2016; nhưng chức năng giống nhau trong Excel dành cho web.

ĐẾN

Nhập công thức

Giá trị trong ô chứa mã sản phẩm (C2) luôn bắt đầu bằng tiền tố tiêu chuẩn "ID-" và dài ít nhất 10 (hơn 9) ký tự.

= AND (LEFT(C2; 3) = "ID-"; CHIỀU DÀI (C2) >9)

=ETEXT(D2)

Giá trị trong ô chứa ngày sinh của ai đó (B6) lớn hơn số năm được liệt kê trong ô B4.

=IF(B6<=(СЕГОДНЯ()-(365*B4));TRUE,FALSE)

Tất cả dữ liệu trong phạm vi ô A2:A10 đều chứa các giá trị duy nhất.

Để giúp người dùng sử dụng bảng tính của bạn dễ dàng hơn, hãy thêm danh sách thả xuống vào các ô. Danh sách thả xuống cho phép người dùng chọn các mục từ danh sách bạn tạo.

Băng hình

    Trên một trang tính mới, nhập dữ liệu bạn muốn xuất hiện trong danh sách thả xuống. Điều mong muốn là các mục danh sách được chứa trong bảng Excel. Nếu không, bạn có thể nhanh chóng chuyển đổi danh sách thành bảng bằng cách chọn bất kỳ ô nào trong phạm vi và nhấn CTRL+T.

    Ghi chú:

  1. Chọn ô trên trang tính nơi bạn muốn đặt danh sách thả xuống.

    Trên dải băng, hãy mở tab Dữ liệu và nhấn nút Kiểm tra dữ liệu.

    Ghi chú: Nếu nút Kiểm tra dữ liệu không có sẵn, trang tính có thể được bảo vệ hoặc chia sẻ. Mở khóa các khu vực cụ thể của sổ làm việc được bảo vệ hoặc hủy chia sẻ trang tính, rồi lặp lại bước 3.

    Trên tab Tùy chọn trong lĩnh vực Loại dữ liệu chọn mục Danh sách.

    Bấm vào trường Nguồn và đánh dấu phạm vi danh sách. Trong ví dụ này, dữ liệu nằm trên trang Thành phố trong phạm vi A2:A9. Lưu ý rằng hàng tiêu đề không nằm trong phạm vi vì nó không phải là một trong các tùy chọn có sẵn để lựa chọn.

    Nếu bạn có thể để trống ô, hãy chọn hộp kiểm Bỏ qua các ô trống.

    Kiểm tra hộp Danh sách các giá trị hợp lệ

    Mở tab Gợi ý đầu vào.


  2. Mở tab Thông báo lỗi.


  3. Không biết nên chọn tùy chọn nào trong trường Xem?

Làm việc với Danh sách thả xuống

chiều rộng cột và chiều cao hàng có thay đổi hay không khi hiển thị tất cả bài đăng của bạn.

Đang tải xuống ví dụ

Chúng tôi khuyên bạn nên tải xuống một cuốn sách mẫu có một số ví dụ về xác minh dữ liệu, tương tự như ví dụ trong bài viết này. Bạn có thể sử dụng chúng hoặc tạo tập lệnh xác minh dữ liệu của riêng mình. Tải xuống ví dụ xác thực dữ liệu Excel.

Bạn có thể nhập dữ liệu nhanh hơn và chính xác hơn bằng cách giới hạn các giá trị trong một ô ở các tùy chọn từ danh sách thả xuống.

Đầu tiên, tạo danh sách các phần tử hợp lệ trên trang tính, sau đó sắp xếp hoặc sắp xếp chúng theo thứ tự mong muốn. Các phần tử này sau này có thể đóng vai trò là nguồn cho danh sách dữ liệu thả xuống. Nếu danh sách nhỏ, bạn có thể dễ dàng tham khảo và nhập các mục trực tiếp vào trình kiểm tra dữ liệu.

Xem thêm

Sau khi tạo danh sách thả xuống, hãy đảm bảo nó hoạt động chính xác. Ví dụ, nên kiểm tra

Để giảm thiểu tình trạng nhập dữ liệu thiếu chính xác, tôi thường sử dụng danh sách dựa trên một phạm vi ô. Hãy xem xét việc sử dụng công cụ này bằng ví dụ về việc giữ bảng chấm công.

Để bắt đầu, trên một trang tính riêng biệt (điều này không cần thiết), chúng tôi sẽ đặt danh sách các giá trị được chấp nhận trong một cột hoặc một hàng (Hình 1a); xem thêm tệp Excel, bảng “Danh sách”.

Cơm. 1. Danh sách họ: (a) theo thứ tự ngẫu nhiên; (b) theo thứ tự bảng chữ cái.

Tải xuống ở định dạng, ví dụ ở định dạng

Danh sách họ này sẽ xuất hiện trong danh sách thả xuống theo thứ tự xuất hiện trong cột này. Rõ ràng là để dễ tìm kiếm, tốt hơn nên sắp xếp chúng theo thứ tự bảng chữ cái (Hình 1b).

Hãy đặt tên của phạm vi cho danh sách của chúng tôi. Để thực hiện việc này, hãy chọn một phạm vi; trong trường hợp của chúng tôi, đây là vùng A2:A21 và nhập tên của phạm vi, như trong Hình. 2; trong trường hợp của chúng tôi, đây là “họ”:

Cơm. 2. Đặt tên cho dãy

Hãy chọn khu vực mà chúng ta sẽ nhập họ (xem tệp Excel, trang “Nhập”). Trong ví dụ của chúng tôi – A2:A32 (Hình 3). Chúng ta vào tab Data, nhóm Data Working, chọn lệnh Data Validation:

Cơm. 3. Xác minh dữ liệu

Trong hộp thoại “Kiểm tra các giá trị đã nhập”, hãy chuyển đến tab Tham số (Hình 4). Trong trường “Loại dữ liệu”, chọn “Danh sách”. Trong trường “Nguồn”, chúng tôi chỉ ra: (a) vùng ô chứa danh sách; tùy chọn này phù hợp nếu danh sách nằm trên cùng một bảng Excel; (b) tên dải ô; Tùy chọn này có thể được sử dụng cả khi danh sách nằm trên cùng một trang tính Excel và khi danh sách nằm trên một trang tính Excel khác (như trong trường hợp của chúng tôi). Trong cả hai trường hợp, hãy đảm bảo rằng có dấu bằng (=) trước liên kết hoặc tên.

Cơm. 4. Chọn nguồn dữ liệu cho danh sách: (a) trên cùng một trang tính; (b) trên bất kỳ trang tính nào

Và về hai tùy chọn khác trên tab “Tùy chọn”:

  • Bỏ qua các ô trống. Nếu hộp kiểm được chọn, Excel sẽ cho phép bạn để trống ô. Nếu hộp kiểm không được chọn, bạn chỉ có thể thoát khỏi ô sau khi chọn một trong các tên trong danh sách. Điểm đặc biệt của tùy chọn này là Excel sẽ cho phép bạn di chuyển giữa các ô (ví dụ: sử dụng phím Enter hoặc mũi tên lên/xuống), nhưng bạn không thể bắt đầu nhập, sau đó xóa tất cả các ký tự và di chuyển sang ô khác.
  • Danh sách các ô hợp lệ. Nếu không có dấu kiểm thì khi bạn đặt con trỏ vào ô nhập, biểu tượng danh sách sẽ không xuất hiện bên cạnh ô đó và do đó bạn sẽ không thể chọn từ danh sách. Mặc dù tất cả các thuộc tính khác khi làm việc với danh sách sẽ có hiệu lực nhưng Excel sẽ không cho phép bạn nhập giá trị tùy ý vào ô.

Hãy chuyển đến tab “Tin nhắn đầu vào” trong cửa sổ “Kiểm tra giá trị đầu vào”. Chọn hộp “Hiển thị chú giải công cụ nếu ô hiện tại”. Nhập tiêu đề và nội dung của tin nhắn vào các trường thích hợp (Hình 5). Sau đó, khi người dùng đứng trên một trong các ô của vùng nhập (trong ví dụ ở Hình 5 - ở ô A6), thông báo chúng ta đã tạo sẽ được hiển thị.

Cơm. 5. Cài đặt thông báo cho đầu vào

Trong cửa sổ “Kiểm tra các giá trị đã nhập”, hãy chuyển đến tab “Thông báo lỗi” (Hình 6). Chọn hộp “Hiển thị thông báo lỗi”. Trong trường “Loại”, chọn loại tin nhắn. Nhập tiêu đề và nội dung của tin nhắn. Thông báo chúng tôi đã tạo sẽ xuất hiện bất cứ khi nào người dùng cố gắng nhập một giá trị không hợp lệ.

Cơm. 6. Đặt thông báo lỗi

Các loại thông báo lỗi được chấp nhận (Hình 7):

  • Dừng lại– ngăn chặn việc nhập dữ liệu không hợp lệ; cái nút Lặp lại cho phép bạn quay lại đầu vào, nút Hủy bỏ Lặp lại.
  • Cảnh báo– cảnh báo về việc nhập dữ liệu không hợp lệ, nhưng không cấm việc nhập dữ liệu đó; cái nút Đúng cho phép bạn chấp nhận đầu vào không hợp lệ; cái nút KHÔNG cho phép bạn tiếp tục nhập (giá trị đã nhập trước đó vào ô sẽ có sẵn để chỉnh sửa); cái nút Hủy bỏ xóa một ô và cho phép bạn bắt đầu nhập lại hoặc chuyển sang nhập vào các ô khác; nút được chọn theo mặc định KHÔNG.
  • Tin nhắn– thông báo về việc nhập dữ liệu không hợp lệ; mặc dù anh ấy cho phép họ được giới thiệu. Loại tin nhắn này là linh hoạt nhất. Khi xuất hiện thông báo thông tin, người dùng có thể nhấn nút ĐƯỢC RỒIđể chấp nhận việc nhập dữ liệu không hợp lệ hoặc nhấn nút Hủy bỏ hủy bỏ mục nhập; nút được chọn theo mặc định ĐƯỢC RỒI.

Cơm. 7. Chọn loại thông báo lỗi

Một vài bình luận. 1. Nếu bạn nhập văn bản quá dài vào cửa sổ Thông báo của tab Thông báo Lỗi, cửa sổ thông báo lỗi sẽ quá rộng (như trong Hình 7); sử dụng ngắt dòng Shift + Enter tại điểm trong tin nhắn mà bạn muốn tách dòng (Hình 8).

Cơm. 8. Hộp thông báo lỗi giảm độ rộng

2. Độ rộng của danh sách thả xuống được xác định bởi độ rộng của ô áp dụng xác thực dữ liệu. Giữ các ô đầu vào đủ rộng để tránh cắt bớt các mục nhập hợp lệ rộng hơn danh sách thả xuống. Nếu không, bạn có thể lựa chọn sai dựa trên các chữ cái đầu tiên của từ.

3. Số lượng mục tối đa trong danh sách thả xuống bị giới hạn, mặc dù không quá nhiều :), cụ thể là con số 32.767.

4. Nếu không muốn người dùng chỉnh sửa danh sách đánh giá, hãy đặt nó vào một sheet riêng, sau đó ẩn và bảo vệ sheet này.