5个示例让你掌握公式在excel数据有效性自定义中的用法.pdf

5 个示例让你重新认识 excel 数据有效性 数据有效性， 用到最多的是制作下拉菜单， 其次是限制单元格输入的数据大小、 类型等。 你以为掌握这些就是它的全部吗 ?NO!! 今天本文通过 5 个示例让你认识一个全新的 excel 数 据有效性 。 1、借贷方只能一列填数据。 【例 1】如下图所示的 AB 两列中，要求只能在 A 或 B 列中的一列输入数据，如果一列 中已输入，另一列再输入会弹出错误提示，中止输入。 操作步骤： 选取 AB 列的区域，数据菜单 - 数据有效性，在有效性窗口中，允许：自定义 ;公式中 输入 =COUNTA($A2:$B2)=1 公式说明： counta 函数可以统一个区域有多少个非空单元格，本例中设置的条件是 Ab 两列同一行中统计结果只能是一个数字。 2、判断车牌输入是否正确 【例2】如下图所示，要求 A 列的车牌号必须输入以汉字开头，且总长度为 7 位。输入 错误就禁止输入。 数据有效性公式： =AND(LENB(LEFT(B2))=2,LEN(B2)=7) 注：汉字占用 2 个字节，数字和字母占用 1 个。 3、每行输入完成才能输入下一行 【例3】在 excel 表格的 A:D 输入时，只有上一行的四列都输入数据，在下一行才能输 入，否则就无法输入并提示错误信息，如下图所示。 操作步骤： 选取 A2:D100, 数据选项卡 - 有效性 - 允许 - 自定义，在来源框中输入以下公式： =COUNTA($A1:$D1)=4 公式说明： counta 函数可以统计非空单元格个数。 $A1:$D1 添加 $是把范围固定在 A:D 列。 4、库存表中有才能出库 【例4 】如下图所示，上表为库存表，要求在下表出库列中设置限制，如果为存表中数 量不足，禁止输入。 当出库大于库存时 设置方法 数据有效性公式： =E3=VLOOKUP(D3,A:B,2,0) 5、输入的内容必须包含指定字符 【例5】如下图所示，要求 A 列输入的内容必须包括字符 A 设置方法： 公式： =COUNTIF(A2,*A*) 补充：在数据有效性中使有公式，可以完成复杂的判断和输入限制。对于规范表格的数 据输入非常有帮助。