如何为Excel单个单元格设置多个条件？ [英] How to set multiple conditions for an excel sheet single cell?

问题描述

如何为Excel单个单元格设置多个条件？我能够设置两者的单一条件。我想检查excel单元格中的数据长度以及空格/特殊验证

想要在输入数据时在excel单元格中显示弹出错误。我试过



= if（LEN（Q3）> 0，LEN（Q3）= 14，SUBSTITUTE（Q3，，））。当单独设置它有效。多个时不工作。

How to set multiple conditions for an excel sheet single cell?. I am able to set single conditions of both. I want to check the length of data in excel cell and also space/special validation
Want to show pop up error in excel cell itself on entering the data in the same. I tried as

=if(LEN(Q3)>0,LEN(Q3)=14,SUBSTITUTE(Q3," ","")). When set singly it works. Not working when multiple.

推荐答案

SUBSTITUTE(Q3, " ", "")

未验证单元格它正在改变它。

解决这个问题的一种方法（如果你必须使用Excel数据表功能实现这一点）就是在某个地方隐藏一个虚拟列进行转换，然后指出你的验证公式到那个单元格。



例如，单元格公式[AL3]是 = SUBSTITUTE（Q3，，）并且您的验证更改为 = if（LEN（AL3）< 15，True_Thing，False_Thing）



您可以拥有许多隐藏列，每个列执行特定验证，然后您的最终验证将是（例如）

is not validating the cell, it is changing it.
One way around this (if you must achieve this using Excel datasheet functionality) is to have a "dummy" column hidden away somewhere that does the transformation, then point your validation formula to that cell.

For example Formula for Cell["AL3"] is =SUBSTITUTE(Q3, " ", "") and your validation changes to =if(LEN(AL3)<15,"True_Thing", "False_Thing")

You could have many hidden columns, each performing a specific validation, then your final validation would be (for example)

=AND(AM3, AN3, AO3)

或者在VBA中进行，您可以根据自己的选择申请尽可能多的验证...例如请参阅 http://stackoverflow.com/questions/409434/automatically -execute-an-excel-macro-on-a-cell-change [ ^ ]

我建​​议阅读： AND [ ^ ]和或者 [ ^ ]功能。



根据您的需要，您的folrmula应如下所示：

I would suggest to read about: AND[^] and OR[^] functions.

Depending on your needs, your folrmula should looks like:

=IF(AND(A1<>3,B1=5,C1>9),True, False)

这篇关于如何为Excel单个单元格设置多个条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！