該教程在自定義RegexMatch函數(shù)的幫助下，使用正則表達(dá)式在Excel中顯示瞭如何在Excel中進(jìn)行數(shù)據(jù)驗(yàn)證。

在限制Excel工作表中的用戶輸入時(shí)，數(shù)據(jù)驗(yàn)證是必不可少的。想要在給定的單元格中僅允許數(shù)字或日期嗎？還是將文本值限制為特定的長度？還是禁止在給定範(fàn)圍之外的時(shí)間？沒問題，所有這些都可以通過預(yù)設(shè)或自定義驗(yàn)證標(biāo)準(zhǔn)輕鬆完成。但是，如果我只想允許匹配特定模式的有效電子郵件地址或字符串怎麼辦？ las，這是不可能的。你說的正則是嗎？嗯...可能會起作用！

如何使用REGEX進(jìn)行Excel數(shù)據(jù)驗(yàn)證

遺憾的是，內(nèi)置的Excel都沒有支持Regexes，並且數(shù)據(jù)驗(yàn)證也不例外。為了能夠使用正則表達(dá)式驗(yàn)證單元格輸入，您需要先創(chuàng)建一個(gè)自定義的正則函數(shù)。另一個(gè)複雜的是，VBA用戶定義的功能不能直接將數(shù)據(jù)驗(yàn)證提供給數(shù)據(jù)驗(yàn)證 - 您需要以命名公式的形式進(jìn)行調(diào)解員。

考慮到上述內(nèi)容，讓我們簡要概述使用Regexes中驗(yàn)證Excel中的數(shù)據(jù)的步驟：

1. 創(chuàng)建一個(gè)自定義的正則函數(shù)，該功能檢查輸入值是否匹配正則表達(dá)式。
2. 為您的正則公式定義名稱。
3. 根據(jù)命名公式配置數(shù)據(jù)驗(yàn)證規(guī)則。
4. 將驗(yàn)證設(shè)置複製到所需的盡可能多的單元格。

聽起來像計(jì)劃嗎？讓我們嘗試在實(shí)踐中實(shí)施它！

使用自定義表達(dá)式的Excel數(shù)據(jù)驗(yàn)證

此示例解決了一個(gè)非常常見的情況 - 如何僅允許特定模式的值。

假設(shè)您在工作表中保留一些SKU代碼，並希望確保只有與給定模式匹配的代碼進(jìn)入列表。前提是每個(gè)SKU由兩組與連字符分開的字符組成，其中的第一組包括3個(gè)大寫字母和第二個(gè)組-3個(gè)數(shù)字，您可以使用以下正則表達(dá)式識別此類值。

模式： ^[az] {3} - \ d {3} $

請注意，字符串的開始（^）和末端（$）是錨定的，因此除了模式以外的字符都無法輸入單元格中。

1。添加自定義的正則匹配功能

首先在工作簿中插入RegexPMatch功能。該代碼已經(jīng)由我們的Excel Gurus編寫，因此您只需要從上述頁面複製它並在VBA編輯器中粘貼即可。

這是函數(shù)的語法供您參考：

在哪裡：

• 文本（必需） - 源字符串（在我們的上下文中 - 經(jīng)過驗(yàn)證的單元格）。
• 模式（必需） - 匹配的正則表達(dá)式。
• match_case （可選） - 匹配類型。真實(shí)或省略 - 對病例敏感；錯(cuò)誤 - 不敏感的情況。

提示。如果您是我們的Ultimate Suite的用戶，則可以在Excel中進(jìn)行正則數(shù)據(jù)驗(yàn)證，而無需在工作簿中添加任何VBA代碼。只需利用我們的Regex工具中包含的自定義Ablebitsregexmatch功能即可。

2。創(chuàng)建一個(gè)命名公式

在您的目標(biāo)工作表中，選擇單元格A1（無論您實(shí)際要驗(yàn)證哪個(gè)單元格，無論其內(nèi)容如何），按CTRL F3打開名稱管理器，並為此公式定義名稱：

=RegExpMatch(Sheet1!A1, "^[AZ]{3}-\d{3}$")

或者，您可以在某些單元格中輸入正則罰款（在此示例中的A2），並向第二個(gè)參數(shù)提供$ 2：

=RegExpMatch(Sheet1!A1, Sheet1!$A$2)

為了使公式正確工作，請確保為文本參數(shù)（A1）和模式的絕對參考使用相對參考（$ a $ 2）。

鑑於我們的公式目的是驗(yàn)證SKU號碼，因此我們將其命名： Validate_sku 。

重要說明！定義公式時(shí)，請仔細(xì)檢查第一個(gè)參數(shù)是否指當(dāng)前選擇的單元格，否則公式將無法使用。例如，如果在表上選擇了單元格A1，請將A1放入第一個(gè)參數(shù)（根據(jù)我們的建議）；如果選擇了B2，則將B2用於第一個(gè)參數(shù)，也是如此。只要與當(dāng)前選擇的單元格匹配，您使用哪個(gè)特定參考文獻(xiàn)並不重要。

有關(guān)分步說明，請查看如何在Excel中製作命名公式。

3。設(shè)置數(shù)據(jù)驗(yàn)證

選擇要檢查要檢查的第一個(gè)單元格（在我們的情況下為A5），並根據(jù)命名公式製定自定義數(shù)據(jù)驗(yàn)證規(guī)則。為此，請執(zhí)行以下操作：

1. 單擊數(shù)據(jù)選項(xiàng)卡>數(shù)據(jù)驗(yàn)證。
2. 在“允許”下拉列表中，選擇自定義。
3. 在相應(yīng)的框中輸入以下公式。

   =Validate_SKU

4. 取消選擇忽略空白選項(xiàng)，否則您的規(guī)則將無法正常工作。

可選地，您可以輸入在單元格中輸入無效數(shù)據(jù)時(shí)顯示的自定義錯(cuò)誤消息。

如果您覺得需要詳細(xì)的步驟，那麼您可以使用：如何在Excel中設(shè)置自定義數(shù)據(jù)驗(yàn)證。

4。將數(shù)據(jù)驗(yàn)證複製到更多單元

要將驗(yàn)證設(shè)置複製到更多的單元格，這就是您需要做的：

1. 選擇具有數(shù)據(jù)驗(yàn)證的單元格，然後按CTRL C複製。
2. 選擇要驗(yàn)證的其他單元格，右鍵單擊它們，單擊“粘貼”特價(jià)，然後選擇“驗(yàn)證”選項(xiàng)。
3. 單擊確定。

   

有關(guān)如何復(fù)制數(shù)據(jù)驗(yàn)證的更多信息。

現(xiàn)在，每當(dāng)有人試圖在任何經(jīng)過??驗(yàn)證的單元格中輸入無效的SKU時(shí)，都會出現(xiàn)以下警告消息：

用正則驗(yàn)證的電子郵件驗(yàn)證

要執(zhí)行電子郵件驗(yàn)證，您從編寫與電子郵件地址匹配的正則表達(dá)式開始。

模式： ^[\ w \。 \ - ] @[A-ZA-Z0-9] [A-ZA-Z0-9 \。 \ - \ - ]*[A-ZA-Z0-9] \。

有關(guān)語法的詳細(xì)說明，請參閱Regex以匹配有效的電子郵件地址。

現(xiàn)在，通過執(zhí)行已經(jīng)熟悉的步驟來指定驗(yàn)證標(biāo)準(zhǔn)：

1. 在B2中輸入上述正則罰款。
2. 選擇單元格A1並定義一個(gè)名為validate_email的名稱，該名稱是指：

   =RegExpMatch(Sheet1!A1, Sheet1!$B$2)

   

3. 對於單元格B5，請使用以下公式應(yīng)用自定義數(shù)據(jù)驗(yàn)證。至關(guān)重要的是，應(yīng)忽略空白選項(xiàng)。

   =Validate_Email

   

   此外，您可以配置自定義錯(cuò)誤消息，以提示用戶輸入有效的電子郵件地址。

4. 將規(guī)則復(fù)製到下面的單元格。

如果您在經(jīng)過驗(yàn)證的單元格中輸入的電子郵件地址與正則表達(dá)式不匹配，則以下警報(bào)將彈出：

使用正則表達(dá)式驗(yàn)證密碼

在使用Regex進(jìn)行密碼驗(yàn)證時(shí)，首先要確定的是您的正則表達(dá)式應(yīng)檢查的內(nèi)容。以下是一些示例，可能會使您進(jìn)入正確的軌道。

密碼必須至少6個(gè)字符長，並且只能包含字母（大寫或小寫）和數(shù)字：

模式： ^[A-ZA-Z0-9] {6，} $

密碼必須長至至少6個(gè)字符，並且至少包含一個(gè)字母和一位數(shù)字：

模式： ^（？=。*[a-za-z]）（？=。*\ d）[a-za-z \ d] {6，} $

密碼必須長6個(gè)字符，並至少包括一個(gè)大寫字母，一個(gè)小寫字母和一位數(shù)字：

模式： ^（？=。*[az]）（？=。*[az]）（？=。*\ d）[a-za-z \ d] {6，} $

密碼必須長6個(gè)字符，至少包括一個(gè)字母，一個(gè)數(shù)字和一個(gè)特殊字符：

模式： ^（？=。*[a-za-z]）（？=。*\ d）（？=。*[@$?。?＃？＆_--]）[a-za-z \ d@$！ $！ ％*＃＆_---＆_--] {6，} $

建立模式後，您可以繼續(xù)設(shè)置數(shù)據(jù)驗(yàn)證：

1. 在C2中輸入您的密碼正則表達(dá)式。
2. 選擇單元格A1並創(chuàng)建一個(gè)名為validate_password的命名公式：

   =RegExpMatch(Sheet1!A1, Sheet1!$C$2)

   

3. 對於Cell C5，使用以下公式創(chuàng)建自定義驗(yàn)證規(guī)則。請記住要解開忽略空白複選框。

   =Validate_Password

   

4. 將規(guī)則復(fù)製到任意多的單元格。

現(xiàn)在，您可以將新密碼安全地添加到列表中。如果輸入字符串不匹配正則條件，則以下警報(bào)將提醒您接受哪些值：

REGEX數(shù)據(jù)驗(yàn)證不起作用

如果Regex數(shù)據(jù)驗(yàn)證在您的Excel中不起作用，則很可能是由於以下原因之一。

REGEXPMATCH函數(shù)缺少

在應(yīng)用數(shù)據(jù)驗(yàn)證之前，請確保在工作簿中插入RegexPMatch函數(shù)的代碼。

不正確的正則表達(dá)

為了確保您的正則表達(dá)式按預(yù)期工作，您可以在某些單元格中輸入REGEXPMATCH公式並檢查結(jié)果。有關(guān)更多信息，請參閱Excel正則表達(dá)式與示例匹配。

要分析和調(diào)試您的正則表達(dá)式，您可以使用Regex101或Regexr等免費(fèi)在線Regex測試服務(wù)。

錯(cuò)誤命名公式

數(shù)據(jù)驗(yàn)證失敗的一個(gè)非常常見的原因是指定為錯(cuò)誤的單元格的正則公式。在所有示例中，我們建議定義一個(gè)參考A1的公式：

=RegExpMatch(A1, regex)

僅當(dāng)使用“定義名稱”和“無$符號”的相對引用時(shí)，僅當(dāng)單元格A1處於活動(dòng)狀態(tài)時(shí)才起作用。

這個(gè)想法是，公式（A1）中指定的相對參考將根據(jù)已驗(yàn)證的單元格的相對位置自動(dòng)更改。換句話說，僅出於方便和一致性而選擇細(xì)胞A1。實(shí)際上，您可以選擇細(xì)胞B1並參考B1，選擇單元格C1並參考C1，依此類推。關(guān)鍵是引用的單元應(yīng)該是活動(dòng)單元格。

要檢查您的命名公式是否正確，請選擇工作表中的任何單元格，打開名稱管理器，然後查看公式指向哪個(gè)單元格。如果它涉及當(dāng)前選擇的單元格，則公式是正確的。否則，您應(yīng)該在第一個(gè)參數(shù)中更改參考。

在下面的屏幕截圖中，選擇了單元格A7，這意味著命名公式在第一個(gè)參數(shù)中應(yīng)具有A7。第二個(gè)參數(shù)（$ a 2 $ 2）是指正發(fā)性 - 此引用應(yīng)該保持恆定，因此它被$符號鎖定。

忽略空白選項(xiàng)

設(shè)置自定義數(shù)據(jù)驗(yàn)證規(guī)則時(shí)，重要的是要取消選擇空白複選框。否則，該規(guī)則由於以下原因而行不通：

如果找不到匹配項(xiàng)，則REGEXPMATCH函數(shù)返回false。選擇忽略空白選項(xiàng)時(shí)，F(xiàn)alse等於空白並被忽略。

另一種解決方案是明確指出該公式應(yīng)返回true：

=RegExpMatch(…)=TRUE

這就是如何使用正則表達(dá)式在Excel中進(jìn)行數(shù)據(jù)驗(yàn)證的方法。我感謝您閱讀，並期待下週在我們的博客上與您見面！

練習(xí)工作簿下載

REGEX數(shù)據(jù)驗(yàn)證示例（.XLSM文件）

以上是使用正則表達(dá)式（REGEX）的Excel數(shù)據(jù)驗(yàn)證的詳細(xì)內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！