Học Excel – Bài 10: Cách sử dụng Data Validation trong Excel

Học Excel – Bài 10: Cách sử dụng Data Validation trong Excel

  • 6 Đánh giá

Data Validation trong Excel là một tính năng tuyệt vời mà bạn cần phải ghi nhận. Dưới đây là chi tiết cách sử dụng Data Validation Excel.

Nhắc tới phần mềm bảng tính, chắc chắn hầu hết mọi người đều nghĩ ngay tới Microsoft Excel. Dù hiện đã có nhiều lựa chọn thay thế hữu ích không kém như Google Sheets, WPS Office Spreadsheets, Zoho Sheets…, Microsoft Excel vẫn là lựa chọn được ưu tiên hàng đầu ở những doanh nghiệp, công ty, tập đoàn lớn lớn. Tại sao vậy? Bởi Microsoft Excel cung ứng rất nhiều tính năng tuyệt vời, dễ dùng mà những chương trình khác không tồn tại.

Ở Microsoft Excel, những bạn sẽ tìm thấy rất nhiều hàm tính tổng, ngày tháng, lượng giác, logic… toàn bộ giúp cho công việc xử lý thông tin, nhất là bảng dữ liệu lớn trở thành dễ dàng, chính xác và nhanh gọn hơn khi nào hết. Một trong số những tính năng hỗ trợ xử lý dữ liệu thường được dùng nhất ở Excel là Data Validation.

Cách sử dụng Data Validation trong Excel

Đúng như tên thường gọi, Data validation trong Excel giúp bạn cố định dữ liệu trong ô nào đó. Điều này giảm thiểu nhầm lẫn thông tin trong những tờ phiếu, đơn giá, kiểm kê… Dưới đây là cách dùng Data Validation trong Excel.

Microsoft Excel Microsoft Excel cho Android Microsoft Excel cho iOS Microsoft Excel trực tuyến

Học Excel – Bài 10: Cách sử dụng Data Validation trong Excel

  • Cách sử dụng Data Validation trong Excel trên Windows
  • Cách sử dụng Data Validation trong Excel trên macOS

    • Office 2013 trở lên
    • Office 2011
  • Cách sử dụng Data Validation trong Excel trên Web

    • Thêm xác thực dữ liệu vào một ô hoặc một phạm vi
    • Thêm những kiểu xác thực dữ liệu khác
  • Ví dụ những công thức trong xác thực dữ liệu ở Excel

Cách sử dụng Data Validation trong Excel trên Windows

1. Chọn ô bạn muốn tạo quy tắc.

2. Chọn Data >Data Validation.

Data Validation trên Excel

3. Trên tab Settings, trong Allow, tích một tùy chọn:

  • Whole Number – Hạn chế ô chỉ đồng ý những giá trị số nguyên.
  • Decimal – Hạn chế ô chỉ đồng ý số thập phân.
  • List – Chọn dữ liệu từ danh sách thả xuống.
  • Date – Hạn chế ô chỉ đồng ý giá trị ngày.
  • Time – Hạn chế ô chỉ đồng ý giá trị thời gian.
  • Text Length – Hạn chính sách dài văn bản.
  • Custom – Tùy biến công thức.

4. Trong Data, chọn một điều kiện:

  • between – Ở giữa
  • not between – Không phải ở giữa
  • equal to – Bằng
  • not equal to – Không bằng
  • greater than – Lớn hơn
  • less than – Ít hơn
  • greater than or equal to – Lớn hơn hoặc bằng
  • less than or equal to – Ít hơn hoặc bằng

5.Trên tab Settings, trong Allow, chọn một trong số những option có sẵn.

6. Đặt những giá trị được yêu cầu khác, dựa trên những gì bạn chọn cho AllowData. Ví dụ, nếu lọc between, sau đó chọn những giá trị Minimum:Maximum: cho những ô đó.

7. Tích ô Ignore blank nếu muốn bỏ qua những khoảng cách trống.

8. Nếu muốn thêm title – Title và thông báo cho quy tắc của bạn, chọn tab Input Message, rồi gõ title và thông báo mong muốn.

9. Tích Show input message when cell is selected để hiện thông báo khi người tiêu dùng chọn hoặc trỏ chuột qua những ô lựa chọn.

10. Chọn OK.

Giờ, nếu người tiêu dùng nhập giá trị không hợp lệ, một pop-up sẽ hiện ra kèm thông báo “This value doesn’t match the data validation restrictions for this cell.”

Cách sử dụng Data Validation trong Excel trên macOS

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

Office 2013 trở lên

Giới hạn nhập dữ liệu

1. Chọn ô muốn hạn chế nhập dữ liệu.

2. Trên tab Data, click Data Validation > Data Validation.

Lưu ý: Nếu không tồn tại sẵn lệnh xác thực, sheet này rất có thể được bảo vệ hoặc workbook đó được sẻ chia. Bạn không thể thay đổi seting xác thực dữ liệu trong trường hợp này.

3. Trong box Allow, chọn loại dữ liệu bạn muốn được chấp nhận nhập, điền vào trong tiêu chuẩn và những giá trị hạn chế.

Lưu ý: những box nhập giá trị hạn chế sẽ được dán nhãn dựa theo dữ liệu và tiêu chuẩn giới hạn bạn đã chọn. Ví dụ, nếu lọc Data làm loại dữ liệu, bạn cũng rất có thể nhập những giá trị hạn chế trong box giá trị tối thiểu và tối đa được dán nhãn Start Date & End Date.

Nhắc người tiêu dùng những mục nhập hợp lệ

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

1. Chọn ô muốn nhắc người tiêu dùng những mục nhập dữ liệu hợp lệ.

2. Trên tab Data, click Data Validation > Data Validation.

3. Trên tab Input Message, tích ô Show input message when cell is selected.

4. Trong box Title, gõ title thông báo.

5. Trong box Input message, gõ thông báo bạn muốn hiển thị.

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

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

1. Chọn những ô muốn hiện thông báo lỗi.

2. Trên tab Data, click Data Validation > Data Validation.

3. Trên tab Error Alert, trong box Title, gõ title cho thông báo của bạn.

4. Trong box Error message, gõ thông báo bạn muốn hiển thị nếu ai đó nhập dữ liệu không hợp lệ.

5. triển khai một trong những thao tác sau:

Tác vụ Click lựa chọn sau trên menu pop-up Style
Yêu cầu người tiêu dùng sửa lỗi trước khi xử lý Stop
Cảnh báo người tiêu dùng dữ liệu không hợp lệ, yêu cầu họ chọn Yes hoặc No nếu muốn quay lại hoặc tiếp tục Warning
Cảnh báo người tiêu dùng dữ liệu không hợp lệ, nhưng được chấp nhận họ tiếp tục sau khi bỏ qua cảnh báo Important

Office 2011

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

1. Chọn ô muốn giới hạn mục nhập dữ liệu.

2. Trên tab Data, trong Tools, click Validate.

Box Tools của Excel trên macOS

3. Trên menu pop-up Allow, chọn loại dữ liệu bạn muốn được chấp nhận.

4. Trên menu Data, chọn loại tiêu chuẩn giới hạn mong muốn rồi nhập những giá trị hạn chế.

Nhắc người tiêu dùng những mục nhập hợp lệ

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

1. Chọn ô muốn nhắc người tiêu dùng nhập dữ liệu hợp lệ.

2. Trên tab Data, trong Tools, click Validate.

3. Trên tab Input Message, tích ô Show input message when cell is selected.

4. Trong box Title, gõ title thông báo.

5. Trong box Input message, gõ thông báo bạn muốn hiển thị.

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

Nếu có những hạn chế dữ liệu tại chỗ và người tiêu dùng nhập dữ liệu không hợp lệ vào ô đó, bạn cũng rất có thể hiện thông báo giải thích lỗi như sau:

1. Chọn những ô muốn hiện thông báo lỗi.

2. Trên tab Data, trong Tools, click Validate.

3. Trên tab Error Alert, trong box Title, gõ title thông báo.

4. Trong box Error message, gõ thông báo muốn hiện.

5. Chọn một trong số tùy chọn sau ở menu pop-up Style

  • Stop – Yêu cầu người tiêu dùng sửa lỗi trước khi tiếp tục.
  • Warning – Cảnh báo người tiêu dùng dữ liệu không hợp lệ, chọn Yes hoặc No để quay lại hoặc tiếp tục.
  • Important – Cảnh báo dữ liệu không hợp lệ nhưng được chấp nhận họ tiếp tục sau khi bỏ qua cảnh báo.

Cách sử dụng Data Validation trong Excel trên Web

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

Lưu ý: Hai bước trước tiên trong phần này dùng để thêm kiểu xác thực dữ liệu. Bước 3-7 để tạo danh sách thả xuống.

  1. Chọn một hoặc nhiều ô muốn xác thực.
  2. Trên tab Data, trong nhóm Data Tools, click Data Validation.
  3. Trên tab Settings > Allow, chọn List.
  4. Trong box Source, gõ những giá trị liệt kê, được phân tách bằng dấu phẩy. Ví dụ, gõ Low, Average, High.
  5. Đảm bảo đã tích In-cell dropdown. Nếu không, những bạn sẽ không thể thấy mũi tên thả xuống bên cạnh ô đó.
  6. Để xác định cách bạn muốn xử lý những giá trị trống, chọn hoặc bỏ tích ô Ignore blank.
  7. Kiểm tra xác thực dữ liệu để đảm bảo nó hoạt động chính xác. Thử nhập cả dữ liệu hợp lệ và không hợp lệ vào những ô, nhờ đó, những bạn sẽ biết những thiết lập có hoạt động đúng như dự định và thông báo hiện khi bạn mong đợi.

Lưu ý:

  • Sau khi tạo danh sách thả xuống, đảm bảo nó hoạt động theo cách bạn muốn.
  • Remove data validation – Chọn ô chứa xác thực dữ liệu bạn muốn xóa, rồi tới Data > Data Validation và trong hộp thoại Data Validation, nhấn nút Clear All, rồi click OK.

Thêm những kiểu xác thực dữ liệu khác

Bảng sau liệt kê những loại xác thực dữ liệu khác và cách thêm nó vào worksheet.

Nhiệm vụ những bước triển khai
Hạn chế mục nhập dữ liệu ở số nguyên nằm trong giới hạn
  1. Làm theo những bước 1-2 kể trên.
  2. Từ list Allow, chọn Whole number.
  3. Trong box Data, chọn loại giới hạn mong muốn. Ví dụ, thiết lập giới hạn trên & dưới, chọn between.
  4. Nhập giá trị tối thiểu, tối đa hoặc cụ thể được chấp nhận.

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

Ví dụ: Bạn đang xác thực dữ liệu ở ô F1. Đặt giới hạn khấu trừ tối thiểu bằng 2 lần số con trong ô đó, chọn greater than or equal to trong box Data > nhập công thức: =2*F1 vào box Minimum.

Hạn chế mục nhập dữ liệu ở phân số trong giới hạn
  1. Làm theo bước 1-2 kể trên.
  2. Trong box Allow, chọn Decimal.
  3. Trong box Data, chọn loại giới hạn bạn muốn.
  4. Nhập giá trị tối thiểu, tối đa hoặc cụ thể được chấp nhận.

bạn cũng rất có thể nhập công thức trả về giá trị số. Ví dụ: Đặt giới hạn tối đa cho tiền thưởng và hoa hồng là 6% lương của nhân viên bán hàng trong ô E1, chọn less than or equal to trong Data > nhập công thức =E1*6% vào box Maximum.

Lưu ý: Để được chấp nhận người tiêu dùng nhập phần trăm, chọn Decimal trong Allow, chọn loại giới hạn bạn muốn ở box Data, nhập giá trị tối thiểu, tối đa hoặc cụ thể dưới dạng số thập phân (ví dụ: .2), rồi hiện ô xác thực dữ liệu ở dạng phần trăm bằng phương pháp chọn ô đó, rồi click Percent Style trong nhóm Number ở tab Trang chủ.

Hạn chế mục nhập dữ liệu ở ngày trong phạm vi ngày
  1. Làm theo những bước 1-2 ở trên.
  2. Trong box Allow, chọn Date.
  3. Trong box Data, chọn loại hạn chế bạn muốn. Ví dụ, được chấp nhận hiển thị những ngày sau một ngày cụ thể, chọn greater than.
  4. Nhập ngày bắt đầu, kết thúc được chấp nhận.

Bạn cũng rất có thể nhập công thức trả về ngày. Ví dụ: Đặt khung thời gian giữa ngày hôm nay và 3 ngày sau đó, chọn between trong Data, nhập =TODAY() vào box Start day, nhập =TODAY()+3 vào End date.

Hạn chế mục nhập dữ liệu ở thời gian trong một mốc giờ
  1. Làm theo những bước 1-2 ở trên.
  2. Trong Allow, chọn Time.
  3. Trong Data, chọn loại giới hạn. Ví dụ, được chấp nhận thời gian trước giờ cụ thể trong ngày, chọn less than.
  4. Nhập thời gian bắt đầu, kết thúc hoặc cụ thể được chấp nhận. Nếu muốn nhập giờ cụ thể, dùng định dạng thời gian hh:mm.

Ví dụ, thiết lập ô E2 ở thời gian bắt đầu (8:00 AM) và ô F2 với thời gian kết thúc (5:00 PM). Bạn muốn giới hạn thời gian họp giữa mốc giờ này, chọn between trong box Data, nhập E2 ở box Start time, nhập =F2 ở box End time.

Hạn chế mục nhập dữ liệu văn bản ở độ dài lựa chọn
  1. Làm theo những bước 1-2 kể trên.
  2. Trong box Allow, chọn Text Length.
  3. Trong box Data, chọn loại giới hạn. Ví dụ, được chấp nhận giới hạn tới số lượng ký tự nào đó, chọn less than or equal to.
  4. Trong trường hợp muốn hạn chế mục nhập tới 25 ký tự, chọn less than or equal to trong Data, rồi nhập 25 vào box Maximum.
Tính toán giới hạn được phép dựa trên nội dung của ô khác.
  1. Làm theo bước 1-2 ở trên.
  2. Trong Allow, chọn loại dữ liệu bạn muốn.
  3. Trong box Data, chọn loại giới hạn bạn muốn.
  4. Trong những ô dưới box Data, click ô bạn muốn dùng để chọn dữ liệu được chấp nhận.

Ví dụ, chỉ được chấp nhận những mục giành cho tài khoản nếu kết quả không vượt quá ngân sách trong ô E1, chọn Allow >Whole number, Data, less than or equal to, và Maximum >= =E1.

Ví dụ những công thức trong xác thực dữ liệu ở Excel

Lưu ý:

  • Những ví dụ này dùng tùy chọn Custom tại nơi bạn viết công thức để đặt điều kiện. Bạn không cần lo lắng về bất kể dữ liệu hiện trong box Data chính vì nó đã bị Custom vô hiệu hóa.
  • Ảnh chụp màn hình này ở Excel 2016 nhưng công dụng của nó giống như Excel trực tuyến.
Đảm bảo Công thức
Ô chứa ID sản phẩm (C2) luôn bắt đầu bằng tiền tố chuẩn “ID-” và dài tối thiểu 10 ký tự

=AND(LEFT(C2,3)=”ID-“,LEN(C2)>9)

Ví dụ công thức trong xác thực dữ liệu ở Excel

Ô chứa tên sản phẩm (D2) chỉ chứa text

=ISTEXT(D2)

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

Ô chứa ngày sinh (B6) phải lớn hơn số năm được thiết lập ở ô B4

=IF(B6vàlt;=(TODAY()-(365*B4)),TRUE,FALSE)

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

Toàn bộ dữ liệu trong phạm vi ô A2:A10 chứa những giá trị riêng biệt

=COUNTIF($AUSD2:$AUSD10,A2)=1

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

Lưu ý: Bạn phải nhập công thức xác thực dữ liệu cho ô A2 trước, rồi sao chép A2 sang A3:A10 để đối số thứ 2 tới COUNTIF sẽ khớp với ô hiện tại. Cụ thể, phần A2)= sẽ thay đổi thành A3)=1, A4)=1…

Đảm bảo mục địa chỉ email trong ô B4 chứa hình tượng @

=ISNUMBER(FIND(“@”,B4))

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

  • 6.582 lượt xem
Tìm thêm: excel microsoft excel Học Excel Microsoft Excel

Xem thêm bài viết khác

  • 🖼️

    Học Excel – Bài 9: Cách gộp và bỏ gộp ô trong Excel

  • 🖼️

    Cách đổi số thành chữ trong Excel 2016/2010/2007/2003

  • 🖼️

    Học Excel – Bài 7: Di chuyển & sao chép nội dung ô trong Excel

  • 🖼️

    Học Excel – Bài 14: Những thao tác định dạng dữ liệu cơ bản trong Excel

  • 🖼️

    Học Excel – Bài 13: Cách dùng định dạng có điều kiện trong Microsoft Excel

  • 🖼️

    Học Excel – Bài 12: những định dạng số có sẵn trong Excel

  • 🖼️

    Học Excel – Bài 11: Nhập và xuất file văn bản

  • 🖼️

    Cách sửa lỗi Microsoft Excel phổ biến

  • 🖼️

    Học Excel – Bài 5: Hàng & cột trong Microsoft Excel

  • 🖼️

    Học Excel – Bài 8: Tìm & thay thế text, số trên worksheet

Xem thêm Tin học Văn phòng

Chủ đề liên quan

Leave a Reply

Your email address will not be published. Required fields are marked *