Xác thực dữ liệu trên Excel

Xác thực dữ liệu trên Excel

CHÀO,

Tôi cần tạo một công thức để xác thực dữ liệu trong Excel để xác thực dữ liệu trong một ô ở định dạng này:

[5 chữ cái].[4 chữ cái].[3 số]!_[2 chữ cái], và 2 chữ cái đầu của 2 từ đầu viết hoa, ví dụ như sau:

Ví dụ về mục nhập dữ liệu phù hợp với tập hợp:

Kathy.John.289!_pc

Cảm ơn!




Trả lời:
Xin chào,

Cảm ơn bạn đã liên hệ. Tôi là Fritzie, Cố vấn độc lập. Tôi sẽ rất vui được giúp đỡ bạn ngay hôm nay.


Bạn có thể muốn kiểm tra các liên kết này để xác thực Dữ liệu với định dạng tùy chỉnh: https://www.youtube.com/watch?v=bDXQy60BcT4 class="AnswersExternalContent"> class="video-container">
https://www.ablebits.com/office-addins-blog/201...


Lưu ý: Đây là trang web không phải của Microsoft. Trang này dường như đang cung cấp thông tin chính xác và an toàn. Hãy chú ý đến những quảng cáo trên trang web có thể quảng cáo các sản phẩm thường được phân loại là PUP (Sản phẩm tiềm ẩn không mong muốn). Nghiên cứu kỹ lưỡng bất kỳ sản phẩm nào được quảng cáo trên trang web trước khi bạn quyết định tải xuống và cài đặt nó.

Hy vọng điều này sẽ giúp.

Chúc một ngày tốt lành !

Trân trọng,

Fritz :)

Trả lời:

Xin chào Fritz!

Cảm ơn bạn đã trả lời của bạn.

Trong trường hợp này, tôi chỉ cần ký tự thứ 1 và thứ 7 của dữ liệu là chữ in hoa và các chữ cái còn lại là chữ nhỏ.

Hướng dẫn này không cho phép tôi tìm ra cách thực hiện điều này.

Đánh giá cao sự hỗ trợ thêm về điều này.



Trả lời:

Có thể được thực hiện bằng UDF ( U ser D efined F unction) được tạo bằng mã VBA (Macro). Không chắc chắn về chuyên môn của bạn ở đây nên tôi sẽ đưa ra các hướng dẫn đầy đủ như sau. Có vẻ hướng dẫn nhiều nhưng thực sự không quá khó.

  • Alt và F11 để mở cửa sổ soạn thảo VBA
  • Trong trình soạn thảo VBA chọn mục menu Insert -> Module
  • Chọn Mục Menu Công cụ -> Tài liệu tham khảo
  • Cuộn xuống trong hộp thoại cho đến khi bạn tìm thấy "Microsoft VBScript Regular Expressions 5.5" rồi đánh dấu vào ô đó và nhấp vào OK ( Đảm bảo bạn chọn hộp chứ không chỉ đánh dấu mục)
  • Sao chép mã VBA ở cuối bài này và dán vào trình soạn thảo VBA.
  • Đóng trình soạn thảo VBA (Băng chéo trên cùng bên phải của cửa sổ trình soạn thảo VBA)
  • Lưu sổ làm việc dưới dạng bật Macro.

Đảm bảo macro được bật như sau.

  • Nếu dải băng Nhà phát triển không hiển thị thì nhấp chuột phải vào bất kỳ đâu trong một trong các dải băng và chọn "Tùy chỉnh dải băng".
  • Ở bên phải hộp thoại, chọn hộp "Nhà phát triển" và nhấp vào OK.
  • Chọn dải băng dành cho nhà phát triển.
  • Chọn Bảo mật Macro (Trong khối Mã ở đầu bên trái của dải băng Nhà phát triển)
  • Hộp thoại hiển thị sẽ mặc định là Macro ở cột bên trái.
  • Chọn nút tùy chọn bảo mật cần thiết. (Tùy chọn "Tắt tất cả macro có thông báo" sẽ ổn.)
  • Nhấn OK để đóng hộp thoại.


Tạo Tên được xác định để chứa Công thức UDF (Không thể gọi UDF trực tiếp từ Xác thực dữ liệu nhưng có thể gọi nó thông qua Tên được xác định có chứa UDF)

  • Chọn ô A1 trên trang tính (Có thể gây ra sự cố khi tạo tên đã xác định nếu bạn không làm điều này)
  • Chọn dải băng công thức
  • Chọn Xác định tên
  • Trong trường Tên, nhập IsValid
  • Bấm vào biểu tượng thả xuống bên cạnh Phạm vi và chọn bảng tính bạn đang làm việc. (Phải Phạm vi cho Bảng tính nếu Yêu cầu Xác thực Dữ liệu trên các bảng tính khác)
  • Trong trường Tham khảo, nhập công thức =StringValidate(A1) Lưu ý: Bao gồm dấu = và A1 là định dạng tương đối (tức là không có dấu $)
  • Bấm vào đồng ý

Nếu bạn muốn Xác thực dữ liệu tương tự hoạt động trên các trang tính khác thì hãy lặp lại thao tác trên cho mỗi trang tính yêu cầu Xác thực dữ liệu. Không sao chép Tên đã xác định và Xác thực dữ liệu sang các trang tính khác.


Tạo xác thực dữ liệu.

  • Chọn ô nơi bạn muốn xác thực dữ liệu
  • Chọn dải băng dữ liệu
  • Chọn thả xuống xác thực dữ liệu -> Xác thực dữ liệu
  • Trong trường Cho phép, chọn Tùy chỉnh
  • Trong Trường Công thức, nhập công thức =IsValid
  • Bỏ chọn Bỏ qua trống (Cần thiết hoặc nó không hoạt động)
  • Bấm vào đồng ý

Bạn có thể sao chép ô Xác thực dữ liệu, sau đó chọn các ô khác nếu cần và Dán Đặc biệt -> Xác thực. Không sao chép sang các bảng tính khác. Tạo riêng trên các bảng tính khác.

Nếu có vấn đề thì vui lòng liên hệ lại với tôi.

Sao chép tất cả đoạn mã sau và Dán vào VBA Editor.

Hàm StringValidate(rng As Range) Dưới dạng Boolean

Dim strToMatch dưới dạng chuỗi

Dim regExpObj làm đối tượng

Dim regExpMatches Như IMatchCollection2

Dim regExpFound As IMatch2

StringValidate = Sai

Đặt regExpObj = RegExp mới

regExpObj.Pattern = "[AZ][az][az][az][az]\.[AZ][az][az][az]\.[0-9][0-9][0-9]!_[az][az]"

regExpObj.Global = Đúng

regExpObj.IgnoreCase = Sai

strToMatch = rng.Value

Đặt regExpMatches = regExpObj.Execute(strToMatch)

Nếu regExpMatches(0).Value = strToMatch thì StringValidate = True

Chức năng kết thúc

Phần sau không phải là một phần của mã nên đừng sao chép. Đây là phần giải thích về dòng mã khớp mẫu bên dưới.

regExpObj.Pattern = "[AZ][az][az][az][az]\.[AZ][az][az][az]\.[0-9][0-9][0-9]!_[az][az]"

Mỗi phần của dòng giữa các dấu ngoặc vuông biểu thị loại ký tự cần khớp.

[AZ] Bất kỳ ký tự chữ hoa nào

[az] Bất kỳ ký tự chữ thường nào

[0-9] Bất kỳ chữ số nào

\. Dấu gạch chéo ngược là một lối thoát vì dấu chấm thực sự được RegExp sử dụng để thể hiện bất kỳ ký tự nào nhưng trong trường hợp này chúng ta thực sự đang tìm kiếm một dấu chấm nên dấu gạch chéo ngược cho nó biết điều đó.

Dấu chấm than và dấu gạch dưới chỉ là các ký tự đơn nên không cần đặt trong dấu ngoặc vuông.



Trả lời:

Xin chào Koh

Hãy thử

=AND(EXACT(PROPER(LEFT(A3,10)),LEFT(A3,10)),ISTEXT(LEFT(A3,10)),MID(A3,6,1)=".",ISNUMBER(VALUE(MID( A3,12,3))),MID(A3,15,2)="!_",EXACT(LOWER(RIGHT(A3,2)),(RIGHT(A3,2))),ISTEXT(RIGHT(A3,2)),LEN(A3)=18)

Ngoài video về câu trả lời trước, bạn cũng có thể kiểm tra liên kết bên dưới.

https://exceljet.net/data-validation-formulas

Hy vọng điều này sẽ giúp bạn.

Nếu có, vui lòng xem xét việc đánh dấu chủ đề này là đã trả lời. Nó sẽ giúp những người khác trong cộng đồng có câu hỏi hoặc vấn đề tương tự.

Cảm ơn bạn trước

Trân trọng

Jeovany



Trả lời:
Xin chào, nó có thể không hoàn hảo nhưng bạn có thể làm việc theo công thức này:
=AND(EXACT(ISTEXT(LEFT(A1,5)),LEN(LEFT(A1,5))=5),EXACT(LEFT(A1,1),UPPER(LEFT(A1,1))),MID(A1,6,1)=".",EXACT(MID(A1, 7,1),UPPER(MID(A1,7,1))),ISTEXT(MID(A1,7,3)),ISNUMBER(VALUE(MID(A1,11,3))),MID(A1,14,2)="!_",ISTEXT(MID(A1,16,2)))

Cảm ơn.

Trả lời:
Tôi cũng thêm xác nhận vào độ dài:
=AND(EXACT(ISTEXT(LEFT(A1,5)),LEN(LEFT(A1,5))=5),EXACT(LEFT(A1,1),UPPER(LEFT(A1,1))),MID(A1,6,1)=".",EXACT(MID(A1,7,1), UPPER(MID(A1,7,1))),ISTEXT(MID(A1,7,3)),ISNUMBER(VALUE(MID(A1,11,3))),MID(A1,14,2)="!_",ISTEXT(MID(A1,16,2)),LEN(A1)=17)

Trả lời:

@Fritzie

Tổng chiều dài của các ký tự phải là 18

5+4+3+2=14+2 dấu chấm+ dấu chấm than +dấu gạch dưới=18



Trả lời:

Xin chào Koh

Tôi hy vọng bạn cũng ổn như những người thân yêu của bạn

Bạn đã tiếp cận cộng đồng này để tìm giải pháp cho vấn đề của mình.

Giải pháp là có

Bạn có thể cho chúng tôi một số phản hồi về nó không

Đã vài ngày kể từ lần cuối liên lạc với bạn.

Trân trọng

Jeovany



Trả lời:

Chào mọi người!

Cảm ơn bạn đã hỗ trợ. Đối với tôi điều đó cũng tốt thôi, chỉ là bận rộn với công việc thôi.

Mọi người hãy quan tâm, đặc biệt là trong thời gian cố gắng này, và vâng, tôi cũng đã tìm ra cách.

Trân trọng,

Chín Âu

Comments

Post a Comment

Popular posts from this blog

Excel 2016 - mở tất cả các tệp trong MỘT phiên bản

Image
Excel 2016 - mở tất cả các tệp trong MỘT phiên bản Xin chào, Tôi gặp sự cố với Excel 2016 (16.0.10228.20051 32-bit): Tôi có một phiên bản Excel đang chạy. (NB - hộp kiểm " Bỏ qua các ứng dụng khác sử dụng Trao đổi dữ liệu động (DDE) " trong Tùy chọn nâng cao KHÔNG được chọn.) Điều tôi cần là - đối với tất cả các tệp Excel mà tôi sắp mở để mở trong phiên bản Excel đầu tiên và duy nhất của mình e, luôn luôn và bất kể: - tôi mở bao nhiêu tập tin cùng một lúc, - nơi chứa các tập tin (tức là trên ổ đĩa cục bộ hoặc trên OneDrive nên hệ thống cần tải chúng xuống trước khi mở) và kể cả khi mở tệp Excel từ các ứng dụng Office khác, tức là tệp đính kèm Outlook. Hành vi mà tôi thấy trong Excel 2016 tốt nhất là không nhất quán: 1. Khi mở nhiều tệp bổ sung cùng một lúc từ đĩa (chọn nhiều tệp/nhấp chuột phải/Mở) - nó sẽ mở n tệp đầu tiên trong phiên bản Excel đầu tiên, nhưng sau đó từ tệp n +1, nó bắt đầu mở chúng trong phiên bản mới . 2. Một biến thể của trường hợp trên là k...
Read more

Điều tôi muốn làm trong Excel 2010 là tạo một nút tùy chỉnh và gắn nó vào thanh công cụ Truy nhập nhanh và chạy một macro cụ thể.

Image
Điều tôi muốn làm trong Excel 2010 là tạo một nút tùy chỉnh và gắn nó vào thanh công cụ Truy nhập nhanh và chạy một macro cụ thể. Trong excel 2003, trong mục tùy chỉnh thanh công cụ, người ta có thể lấy một nút và chỉnh sửa hình ảnh của nút đó. Điều tôi muốn làm trong excel 2010 là tạo một nút tùy chỉnh và gắn nó vào thanh công cụ Truy cập nhanh và để nút đó chạy một macro cụ thể. Ví dụ, tạo nút có chữ G lớn và khi nhấp vào, nút sẽ thực hiện truy vấn web và nhập giá vàng từ kitco.com. Có cách nào để thực hiện việc này trong Excel 2010 không? Trả lời: Xin chào, Để bật tab Nhà phát triển 1. Trên tab Tệp , chọn Tùy chọn để mở hộp thoại Tùy chọn . 2. Nhấp vào Tùy chỉnh Ribbon ở phía bên trái của hộp thoại. 3. Trong mục Chọn lệnh từ ở phía bên trái hộp thoại, chọn Lệnh phổ biến . 4. Trong mục Tùy chỉnh Dải băng ở phía bên phải hộp thoại, chọn Tab chính trong hộp danh sách thả xuống, sau đó chọn hộp kiểm Nhà phát triển . 5. Nhấp vào OK . Để biết thêm thông tin về cách tạo ...
Read more

Khẩn cấp - File Excel chứa các ký tự đặc biệt ở họ và tên liên hệ

Khẩn cấp - File Excel chứa các ký tự đặc biệt ở họ và tên liên hệ Kính gửi bộ phận hỗ trợ, Đây là một vấn đề được ưu tiên hàng đầu đối với các marcomms của chúng tôi. Tôi có tệp Microsoft Excel chứa các ký tự đặc biệt trong họ và tên của liên hệ. Chúng tôi muốn gửi một email được cá nhân hóa dựa trên họ và tên liên hệ. Tôi đã thực hiện rất nhiều nghiên cứu và xem video nơi mọi người chia sẻ giải pháp nhưng không hiệu quả. Tôi sẽ đánh giá cao nếu bạn có thể chia sẻ cách loại bỏ những ký tự đặc biệt đó cùng một lúc. Mong muốn được nghe từ bạn. Trân trọng, Mohsin Ali Trả lời: Bạn sẽ cần phải cung cấp một số chi tiết ít nhất là một đoạn excel đính kèm với bài đăng của bạn Trả lời: Tải dữ liệu của bạn vào PowerQuery. Có lệnh xóa ký tự cụ thể theo vị trí > https://docs.microsoft.com/en-us/powerquery-m/text-removerange > https://www.thebiccountant.com/2020/03/20/trimming-text-with-custom-characters-in-power-bi-and-power-query/ > https://www.mrexcel.com/excel-tips/extracti...
Read more