Excel là ứng dụng bảng tính của Microsoft Office dùng để tính toán và phân tích dữ liệu. Vậy các thủ thuật excel nâng cao là gì? Cách xác thực dữ liệu Data validation là gì? Cùng 123job tìm hiểu về nó nhé

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

Đây như là một hướng dẫn để excel 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 như bạn chưa từng được sử dụng đến tính năng Data Validation của Excel nâng cao, hoặc như muốn xem lại những tính năng cơ bản nhất đó, vui lòng tham khảo các bài viết bên dưới nhé!

I. 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 cơ bản

Học excel nâng cao 1

Học excel nâng cao 

1. Chỉ cho phép nhập số trong Excel nâng cao

Điều ngạc nhiên đó là không có một quy tắc xác thực dữ liệu học Excel nâng cao nào có sẵn nào để phục vụ cho một tình huống rất điển hình đó là khi bạn cần sự hạn chế người dùng khi chỉ nhập số trong các ô cụ thể.

Nhưng nếu bạn có thể dễ dàng thực hiện được với một công thức xác thực dữ liệu còn tùy chỉnh dựa trên hàm ISNUMBER, như sau:

=ISNUMBER (C2)

Trong đó C2 là một  ô trên cùng của dải ô mà bạn muốn xác thực.

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

2. Chỉ cho phép nhập văn bản trong Excel cơ bản

Nếu như muốn làm điều ngược lại - để chỉ được cho phép các mục nhập văn bản trong phạm vi mà ô đã cho, hãy xây dựng đến quy tắc tùy chỉnh với hàm ISTEXT, ví dụ:

=ISTEXT (D2)

Trong đó D2 là một ô trên cùng của dải ô đã chọn.

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

Nếu tất cả các giá trị có trong một phạm vi nhất định cần phải bắt đầu bằng một ký tự hoặc một  chuỗi con cụ thể, bạn hãy tạo ra những quy tức xác thực dữ liệu excel nâng cao để tùy chỉnh dựa trên hàm COUNTIF với ký tự đại diện:

=COUNTIF (cell,"text*")

Ví dụ: để đảm bảo được tất cả id đơn hàng có trong cột A đều được bắt đầu bằng tiền tố "AA-", "aa-", "Aa-" hoặc "aA-" (không phân biệt là chữ hoa chữ thường), hãy xác định đến quy tắc tùy chỉnh với những công thức này trong  data validation của excel nâng cao

=COUNTIF(A2,"aa-*")

4. 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 khi có 2 hoặc nhiều tiền tố hợp lệ hơn, hãy thêm một số vào hàm COUNTIF để làm quy tắc và  xác thực dữ liệu học excel nâng cao của bạn hoạt động một cách logic OR:

=COUNTIF(A2,"aa-*") + COUNTIF(A2,"bb-*")

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

Trong các trường hợp có cần phân biệt chữ hoa, chữ thường . Bạn hãy sử dụng đến hàm EXACT kết hợp với hàm LEFT tạo ra công thức xác thực phân biệt được chữ hoa và chữ thường cho những mục bắt đầu bằng một  văn bản cụ thể:

= EXACT (LEFT ( cell , number_of_chars ), text )

Ví dụ: để chỉ cho phép khi những mục bắt đầu bằng "AA-" (không cho phép "aa-" và "Aa-"), hãy sử dụng đến công thức này:

= EXACT (LEFT (A2,3),"AA-")

Trong công thức trên, hàm LEFT có trích xuất đến 3 ký tự đầu tiên từ ô A2 và EXACT thực hiện và so sánh phân biệt được chữ hoa và chữ thường với một  chuỗi con số được viết hoa ("AA-" trong ví dụ này). Nếu như hai chuỗi con đó khớp chính xác, công thức trả về TRUE và cả quá trình xác nhận sẽ được chuyển sang; nếu không trả về FALSE và việc xác thực đó không thành công.

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

Để cho phép đến các mục nhập chứa văn bản cụ thể khi ở bất kỳ vị trí nào trong ô (ở đầu, giữa hoặc cuối), hãy sử dụng đến hàm ISNUMBER kết hợp với FIND hoặc SEARCH sẽ tùy thuộc vào việc bạn muốn đối sánh và phân biệt chữ hoa chữ thường hay sẽ không phân biệt chữ hoa chữ thường:

Xác thực về không phân biệt chữ hoa chữ thường trong học excel nâng cao là :

ISNUMBER (SEARCH (text, cell))

Xác thực được phân biệt chữ hoa chữ thường trong học excel nâng cao là:

ISNUMBER( FIND (text, cell))

Trên tập dữ liệu đó mẫu của chúng ta, để chỉ cho phép về các mục nhập chứa văn bản "AA" trong các ô A2: A6, hãy sử dụng một trong những công thức sau:

Trường hợp khi không phân biệt chữ hoa chữ thường trong học excel nâng cao là:

= ISNUMBER( SEARCH ("AA", A2))

Trường hợp khi có phân biệt chữ hoa chữ thường trong học excel nâng cao là.

= ISNUMBER (FIND ("AA", A2))

Các công thức trên được hoạt động 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 được trả về vị trí của ký tự đầu tiên trong chuỗi con.

Nếu như văn bản không được tìm thấy, một lỗi đó sẽ được trả về.

Đối với hầu hết 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 mà có lỗi, ISNUMBER trả về FALSE và mục nhập đó sẽ không được phép trong một ô.

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

Trong các tình huống khi có một cột hoặc một dải ô nhất định sẽ không được chứa bất kỳ một giá trị trùng lặp nào, hãy định dạng cấu hình quy tắc xác thực dữ liệu tùy chỉnh để được chỉ cho phép nhập đến các giá trị duy nhất. Để thực hiện, chúng ta sẽ sử dụng về công thức COUNTIF cổ điển để xác định được các bản trùng lập trong học excel nâng cao​​​​​​​ là:

= COUNTIF(range, topmost_cell)

Ví dụ: để đảm bảo khi chỉ các id số thứ tự duy nhất đó được nhập vào những ô từ A2 đến A6, hãy tạo ra quy tắc tùy chỉnh với công thức xác thực dữ liệu sau trong học excel nâng cao​​​​​​​ là:

= COUNTIF($A$2:$A$6, A2)

Khi với  một giá trị duy nhất được nhập, công thức trả về TRUE và quá trình xác thực là  thành công. Nếu như giá trị tương tự đã được tồn tại trong phạm vi được chỉ định (số lượng lớn hơn 1), COUNTIF trả về FALSE và đầu vào đó không xác thực được.

Lưu ý

  • Chúng ta thường khóa phạm vi bằng tham chiếu ô tuyệt đối (A $ 2: $ A $ 6) và sử dụng đến tham chiếu tương đối cho ô trên cùng (A2) để có đượ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 sẽ không phân biệt chữ hoa và chữ thường.

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

Học excel nâng cao 2

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 hiện nay ngày tháng có sẵn trong học excel nâng cao​​​​​​​ sẽ cung cấp được khá nhiều tiêu chí và được xác định trước để hạn chế người dùng chỉ nhập đến 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.

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

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

Để giới hạn được mục nhập vào một ngày trong một phạm vi đã được chỉ định, bạn có thể sử dụng đến quy tắc Ngày được xác định trước với những tiêu chí "Between" hoặc tạo ra quy tắc xác thực còn tùy chỉnh với công thức ở bên dưới trong học excel nâng cao​​​​​​​ là :

= AND(cell >= start_date), cell

Trong đó:

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

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

Để hạn chế được người dùng chỉ nhập các ngày trong tuần hoặc các ngày cuối tuần, hãy định những cấu hình và  quy tắc xác thực đếntù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 từ 1 (Thứ Hai) đến 7 (Chủ Nhật). Vì vậy, khi đối với các ngày trong tuần (Thứ Hai đến Thứ Sáu), kết quả của công thức phải nhỏ hơn 6 và đối với ngay cả các 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 làm việc trong học excel nâng cao​​​​​​​ là :

= WEEKDAY ( ô , 2)

Chỉ cho phép các ngày cuối tuần trong học excel nâng cao​​​​​​​ là:

= WEEKDAY ( ô , 2)> 5

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

Trong khá nhiều trường hợp, bạn muốn sử dụng ngày hôm nay làm cột mốc bắt đầu của phạm vi mà ngày được phép, để người dùng sẽ chỉ có thể nhập ngày tương lai, hoặc là ngày trong quá khứ.

Để lấy được ngày hiện tại, hãy sử dụng đến hàm TODAY, sau đó thêm về  số ngày mong muốn vào đó để tính được ngày kết thúc.

Ví dụ: để giới hạn mục được việc nhập dữ liệu trong 6 ngày kể từ bây giờ (7 ngày kể cả hôm nay), chúng ta sẽ sử dụng đến quy tắc Ngày tích hợp với tiêu chí dựa trên công thức trong học excel nâng cao​​​​​​​ là:

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

Theo như cách tương tự, bạn có thể hạn chế được người dùng nhập ngày trước hoặc sau ngày hôm nay. Để thực hiện, 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 bắt đầu .

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

Để xác thực được những dữ liệu dựa trên thời gian hiện tại, hãy sử dụng đến quy tắc Thời gian được xác định trước với những  công thức xác thực dữ liệu của riêng bạn trong học excel cơ bản​​​​​​​ là:

  • Trong hộp Allow box, chọn Time.
  • Trong hộp Dữ liệu, hãy chọn nhỏ hơn để chỉ cho phép được thời gian trước ở thời điểm hiện tại hoặc lớn hơn để có thể  cho phép thời gian sau thời điểm hiện tạiđó .
  • Trong hộp Thời gian kết thúc hoặc Thời gian bắt đầu (tùy thuộc vào những  tiêu chí bạn đã chọn ở bước trước), hãy nhập đến 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 học excel cơ bản​​​​​​​ là:

= NOW()

Để xác thực thời gian dựa trên thời gian hiện tại học excel cơ bản​​​​​​​ là:

= TIME( HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()))

Ảnh chụp màn hình đó bên dưới hiển thị những quy tắc chỉ cho phép lớn hơn ở thời gian hiện tại.

III. Cách quản lý hàng hóa bằng Data validation cực hay 

Học excel nâng cao 3

Cách quản lý hàng hóa bằng Data validation cực hay 

1. Đặt tên cho các vùng dữ liệu vào Data validation để xử lý

Tuỳ vào từng trường hợp khi mà bạn sẽ có được bao nhiêu vùng cần phải  đặt tên cho dữ liệu, ở đây ta sẽ có các vùng như "Loại sản phẩm", trạng sản phẩm", "iPhone", "Sony",... Các bạn nên có để tạo ra ở một sheet riêng gồm nhiều list khác nhau sẽ tiện hơn trong việc quản lý chức năng Data Validation trong học excel nâng cao

Bước 1: Tạo được một vùng dữ liệu tên "loaisanpham" để biểu thị cho những loại sản phẩm khác nhau chẳng hạn  về đích của việc này là tí đưa vùng "loaisanpham" đó vào cột "Loại sản phẩm" để chọn được chức năng Data Validation trong excel. Ví dụ: iphone sony samsung LG Oppo FPT Các bạn quét vùng đến loại sản phẩm vừa tạo và chuột phải, chọn "Name a Range", đặt tên là "loaisanpham"

Bước 2: Tạo được một vùng dữ liệu mang tên "ip" để biểu thị cho các đời sản phẩm - đời iphone khác nhau, "sn" để b cho các đời sony khác nhau, và còn lại tùy các bạn. Mục đích chính  của việc này là tí đưa vùng "ip", "sn" đó vào cột sản phẩm" để chọn được chức năng Data Validation trong excel. Ví dụ: iphone 6 iphone 7 iphone 1000 sony z3 sony z5 sony zzz chưa làm 1 chưa làm 2 chưa làm 3 chưa làm 4 chưa làm 5 chưa làm 6 --> Các bạn quét đến vùng đời sản phẩm vừa tạo và chuột phải, chọn "Name a Range", đặt tên là "ip" cho vùng iphon "sn" cho vùng sony, các vùng khác tùy các bạn....

 Bước 3: Tạo ra một vùng dữ liệu mang tên "tinhtrang" để biểu thị cho những tình trạng sản phẩm còn hàng hay là  hết hàng. đích của việc này là tí đưa vùng "còn hàng", "hết hàng" đó vào cột "Tình trạng sản phẩm" để chọn chức năng Dat Validation trong học excel cơ bản​​​​​​​. Ví dụ: còn hàng hết hàng ->> ở đây ta chỉ cần đặt ra  tên vùng dữ liệu là "tinhtrang

2. Tạo tính năng Data validation bằng chức năng name range 

Bước 1: Cột "Loại sản phẩm" bôi đen hết và chọn Data - Data Validation. Trong phần Allow chọn "List", p source ta viết chính đến tên của vùng dữ liệu ta đặt ở phần 1. Ở đây ta sẽ viết là "=loaisanpham" --> kiểm tra được thành quả năng Data Validation trong học excel cơ bản​​​​​​​.

Bước 2: Ở cột "Đời sản phẩm" bôi đen hết và chọn Data - Data Validation. Trong phần Allow chọn "L phần source ở đây sẽ phải viết một hàm if để đạt được điều kiện thỏa mãn điều kiện "phù hợp với cột "Loại sản phẩm Ví dụ ở cột loại sản phẩm là iphone thì bên cột đời sản phẩm cái menu xổ xuống đó chỉ được xuất hiện đến những đời sản phẩm về iphone tương ứng ví dụ iphone 100, iphone 1000,..."

Dựa theo những gì làm ở phần 1, bước 2 mà các bạn viết hàm trong học excel cơ bản​​​​​​​, ở đây tôi sẽ dùng hàm if với cú pháp như =if(b6="iphone",ip,if(b6="sony",sn,chualam)) ip ở đây chính là tên một  vùng dữ liệu đời sản phẩm iphone, tương tự với sn là tên vùng dữ liệu đó các đời sản phẩm của chualam là các vùng dữ liệu còn lại. --> Tận hưởng về  thành  quả chức năng Data Validation trong excel nâng cao

Bước 3: Ở cột "Tình trạng sản phẩm" thực hiện khá đơn giản, giống bước 1: ta bôi đen hết và chọn Data - Data Validation. Trong phần Allow chọn "List", phần source ta viết chính tên của vùng dữ liệu đặt ở phần 1. Ở đây t viết là "=tinhtrang" ----> kiểm tra đến thành quả chức năng Data Validation trong học excel nâng cao​​​​​​​

IV. 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âng cao

Học excel nâng cao 4

Cách khắc phục một số lỗi xác thực dữ liệu

Nếu quy tắc xác thực dữ liệu dựa trên công thức excel nâng cao của bạn không hoạt động 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 về công thức được xác thực dữ liệu Excel nâng cao của bạn xem đã được chính xác hay chưa.

Đối với nhiều người mới bắt đầu, hãy sao chép những công thức xác thực của bạn vào một ô, để đảm bảo được rằng công thức đó không chỉ trả về lỗi như # N/A, lỗi #VALUE hoặc # DIV/0 !.

Nếu như bạn đang tạo ra quy tắc tùy chỉnh, công thức đó phải trả về các giá trị logic là TRUE và FALSE hoặc các giá trị 1 và 0 tương ứng với chúng.

Nếu như bạn sử dụng đến tiêu chí dựa trên những công thức trong quy tắc tích hợp (giống như việc chúng tôi đã làm để xác thực được thời gian dựa trên thời gian ở hiện tại ), công thức đó có thể trả về một đến giá trị số khác.

Trong khá nhiều trường hợp, nếu như bạn chọn một hộp Bỏ qua trống khi xác định đến quy tắc (thường được chọn theo mặc định) và một hoặc nhiều những ô được tham chiếu trong bất kì công thức của bạn bị trống, hay  bất kỳ giá trị nào sẽ được phép làm trong ô đã xác thực đó .

Đây sẽ  là một ví dụ ở dạng đơn giả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ệu

Khi thiết lập được những quy tắc xác thực Excel nâng cao dựa vào công thức, hãy nhớ rằng đến tất cả các tham chiếu ô trong công thức đó của bạn đều có liên quan đến những  ô phía trên bên trái trong phạm vi đã được chọn.

Nếu như bạn đang tạo quy tắc cho nhiều ô và nhiều tiêu chí xác thực của bạn có phụ thuộc vào các ô cụ thể, hãy đảm bảo được rằng việc sử dụng tham chiếu những ô tuyệt đối (với dấu $ như $ A $ 1), nếu không những  quy tắc đó của bạn sẽ chỉ hoạt động được chính xác cho ô đầu tiên. Để minh họa về  rõ hơn điểm này, hãy vui lòng xem xét đến ví dụ sau.

Giả sử, nếu bạn muốn giới hạn những mục nhập dữ liệu trong ô D2 đến D5 thành các số nguyên giữa 1 (giá trị nhỏ nhất) và kết quả đó của phép chia A2 cho B2. Vì vậy, bạn cần tính giá trị lớn nhất với những công thức đơn giản này =A2/B2, như được hiển thị trong những ảnh chụp màn hình bên dưới:

Vấn đề đó là công thức đó có vẻ đúng này sẽ không hoạt động được cho các ô D3 đến D5 vì các tham chiếu tương đối và  thay đổi dựa trên một  vị trí tương đối của các hàng và cột. Vì vậy, khi đối với ô D3, công thức đó sẽ thay đổi thành =A3/B3, và đối với D4, nó sẽ trở thành =A4/B4, việc xác thực dữ liệu hoàn toàn sẽ bị sai!

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

Trong trường hợp nếu như bạn muốn xác thực đến từng ô dựa trên tiêu chí riêng của từng ô đó, hãy sử dụng đến những tham chiếu ô tương đối không có dấu $ để nhận công thức điều chỉnh cho từng loại hàng hoặc / và cột:

Như bạn thấy đó, sẽ không có "công thức đúng tuyệt đối" nào cả, cùng với một công thức sẽ có thể đúng hoặc sai còn tùy thuộc vào tình huống và nhiệm vụ cụ thể đó của bạn.

V. Kết luận 

Đây là một số cách sử dụng xác thực dữ liệu trong Excel nâng cao với những công thức tùy chỉnh để tạo ra những quy tắc xác thực riêng cho bạn. Chúc bạn thành công!