excel - Excel: how ensure cells contain numbers 1-6 or comma separated numbers 1-6?
问题描述
I have question and answer spreadsheets with up to a couple hundred rows in them. One column in each row indicates a correct answer by entering a number from 1 to 6 or multiple answers separated by commas like 1,3,5. I'd like to enforce this rule on every row and not allow input of anything else.
I find can't a way to use Custom Data Validation and am not sure how to use a function in each row.
解决方案
Select your range of interest, for example
A1:A10
Add the following formula as a custom data validation:
=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[preceding::*=. or .*0!=0 or .*1>6 or .*1<1]"),"")=""
You'd need Excel2013 onwards but the formula will validate the string value to have:
- No duplicate values.
- No values other than numbers.
- No values above 6.
- No values below 1.
- Only comma will work as a valid seperator.
The A1
in the formula is relative so validation for A10
will work just the same way but then for A10
obviously.
推荐阅读
- angular - 无法绑定到“ngIF”,因为它不是“ion-item”的已知属性
- vue.js - 如何在 axios.get() 中添加前缀 sparql
- ms-access - 在将 MS Access 中的控件发送到表中进行验证之前,是否存在更改控件值的事件
- hibernate - Hibernate JPA 为 oneToMany 实体生成大量 sequence.nextval 查询
- c++ - 是否可以知道树中的项目何时被重命名?
- html - 为什么跨度没有自动换行?
- c - ls -al 的“总数”是什么?
- qb64 - 当我在游戏中达到这一点时,屏幕不断刷新
- google-cloud-storage - 有没有一种快速的方法来确定 gsutil cp 无法复制哪个文件?
- python - 从方程组中添加值