跳到主要内容

 如何在Excel工作表的一个单元格中应用多个数据验证?

在Excel工作表中,对一个单元格应用一次数据验证可能对我们很常见,但是,您是否曾经尝试过在一个单元格中使用多个数据验证? 本文中,我将讨论一些用于解决Excel中此任务的不同示例。

在一个单元格中应用多个数据验证(示例1)

在一个单元格中应用多个数据验证(示例2)

在一个单元格中应用多个数据验证(示例3)


在一个单元格中应用多个数据验证(示例1)

例如,我要使用以下条件设置对单元格的数据验证:如果输入的数字必须小于100,如果输入的文本必须在D2到D7的列表中,如以下屏幕截图所示:

1。 选择您要应用多个数据验证的单元格,然后单击 时间 > 数据验证 > 数据验证,请参见屏幕截图:

2。 在 数据验证 对话框中的 个人设置 选项卡,请执行以下操作:

(1.)选择 定制版 来自 下拉列表;

(2.)输入以下公式: =OR(A2<$C$2,COUNTIF($D$2:$D$7,A2)=1)公式 文本框。

备注:在以上公式中, A2 是您要应用数据验证的单元格, C2D2:D7 是应用数据验证所需要的标准。

3。 然后点击 OK,从现在开始,只有符合条件的值可以输入到单元格中,否则,将弹出一个警告提示框提醒您,请参见屏幕截图:


在一个单元格中应用多个数据验证(示例2)

在此示例中,我只允许在特定单元格中输入文本“Kutools for Excel”或 12 年 1 月 2017 日至 12 年 31 月 2017 日之间的日期,如下图所示。要解决此工作,请按以下步骤操作:

1。 去 数据验证 对话框,然后在对话框中执行以下操作:

(1.)点击 个人设置 标签;

(2.)选择 定制版 选项从 下拉列表;

(3.)键入以下公式: =OR(A2=$C$2,AND(A2>=DATE(2017,12,1), A2<=DATE(2017,12,31))) 公式 文本框。

备注:在以上公式中, A2 是您要应用数据验证的单元格, C2, 2017,12,12017,12,31 是应用数据验证所需要的标准。

2。 然后点击 OK 按钮,现在仅允许输入符合条件的值,其他值将受到限制,如以下屏幕截图所示:


在一个单元格中应用多个数据验证(示例3)

第三个示例,我希望文本字符串必须以“ KTE”或“ www”开头,如果以“ KTE”开头,则只能使用6个字符。 如果以“ www”开头,则只能输入10个字符,请参见屏幕截图:

数据验证中的以下公式可以帮助您进行处理。

1。 去的 数据验证 对话框中,在对话框中,请执行以下操作:

(1.)点击 个人设置 标签;

(2.)选择 定制版 选项从 下拉列表;

(3.)键入以下公式: =OR(AND(LEFT(A2,3)="KTE",LEN(A2)=6),AND(LEFT(A2,3)="www",LEN(A2)=10))公式 文本框。

备注:在以上公式中, A2 是要应用数据验证的单元格,您可以将公式中的条件更改为自己的条件。

2。 然后点击 OK 按钮,现在仅允许输入与您指定的条件匹配的文本值,请参见屏幕截图:

备注:可能需要应用其他不同的数据验证标准,您可以根据需要为不同的标准创建自己的公式。

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (15)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi,
I am capturing department (10 departments), rating (5,4,3,2 & 1)and reward type criteria (X, Y and Z) of which i calculate the overall callibration based on certain %. Eg. i have 10 departments having each department 15-20 employees. Now i need a formula wherein in the table against the particular department, lets say department1 to populate the % of employees (only with rating 5 and criteria X) against the total employee of (criteria X and Z).
This comment was minimized by the moderator on the site
I have data in two different columns and i have to apply this with two different criteria. For example, first criteria shows result if a particular cell contains a text "Highlighter" and second criteria show when the cell contain text "Pointer".
Highlighter test results are different than the pointer results and we make separate validation of both data. Is this possible to extract the validation of each criteria in a single cell with no overlapping?
This comment was minimized by the moderator on the site
Hey I want a parent child relationship sort of multiple drop down lists.
For example, if in column one some selects 'a' among the list of a, b & c
Then in the second column the child category of 'a' which is x, y & z should appear
How can that be done?
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
Hi Team, I failed to build example no.1. I copied and pasted the formula:  =OR(A2<$C$2,COUNTIF($D$2:$D$7,A2)=1) and failed. Where is the bug ? Can you please assist ? THX a lot.
This comment was minimized by the moderator on the site
Hello Frank,
Glad to help. The reason why the data validation failed is that you probably didn't choose the data validation cell range. Please select the cell range first (say A2:A10), then click Data > Data Validation > Data Validation to input the formula =OR(A2<$C$2,COUNTIF($D$2:$D$7,A2)=1). Please see the screenshots I uploaded here. Hope it can solve your problem. Have a nice day.
Sincerely,
Mandy
This comment was minimized by the moderator on the site
How can I apply a data validation to multiple columns?
This comment was minimized by the moderator on the site
Hello Fabian,To apple a data validation to multiple columns, the trick is almost identical to the ways we mentioned above. The most important thing is that you select the columns first, then you can set up the rules in the Data Validation dialog box. Plesase have a try. Any question, please feel free to contact us. Have a nice day.Sincerely,Mandy
This comment was minimized by the moderator on the site
i am confuse
This comment was minimized by the moderator on the site
Hi,
I need to validate multiple columns before entering a value into cell. please find my requirement below,
I have 3 columns for example,

A1-Total Credit limit (Mandatory field)
Should have a minimum amount of 100 and multiples of 100:
=AND(ISNUMBER(A1),A1>=100, MOD(A1,100)=0) - Solved!
B1- Weekly Credit limit (Optional filed)
User should be able to input a value only when A1 is filled, Should not be greater than A1, Minimum amount 100 and multiples of 100
=AND(A1<>"",ISNUMBER(B1),B1<=A1, MOD(B1,100=0) - Solved!
C1-Daily Credit limit (Optional field)
User should be able to input a value only when A1 is filled, Should not be greater than A1&B1, Minimum amount 100 and multiples of 100

I need to add following validation, Please Help
I have to check if A1 is filled or not, then C1 shouldn't be greater than A1 and C1 min value should be 100 and multiples of 100 - I have answer to this
How can add a validation on B1 because B1 is optional it can have value and cannot. so if there's a value I have to make sure C1 is not greater than B1 else ignore B1?

How can i have this in data validation?

Thanks once again for the help
This comment was minimized by the moderator on the site
how can I add another Data validation function with this list function. for example I added a list of "yes" and "no". but I also want to add an OR function to this so that if "yes" is selected in one column then others only have to be "no".
This comment was minimized by the moderator on the site
Is it possible to apply data validation on the calcuated column attribute?
This comment was minimized by the moderator on the site
i'm looking for the ability to have a data validation which allows the user to select multiple choices. I.e. the list pops up and user can select Apple, grape, and/or banana. If they select all three, it shows in cell as: "apple, grade, banana". if only 1: "grape" etc... any ideas?
This comment was minimized by the moderator on the site
Hello, jeffe,
Maybe the below article can help you to deal with your problem, please check it:
https://www.extendoffice.com/documents/excel/2448-excel-drop-down-list-multiple-selection.html
This comment was minimized by the moderator on the site
Any idea how to have multiple data validation and a drop down. Drop down uses "List" and this example uses "Custom". Basically if the correct data is present in another column, I want the drop down to be able to be selected to input data. Otherwise if the dropdown is selected the error message appears.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations