2025年Excel数据有效性(下拉列表)设置考试题及答案

2025年Excel数据有效性(下拉列表)设置考试题及答案一、单项选择题（每题2分，共20分）1.在Excel2021及以上版本中，设置数据有效性（下拉列表）的主菜单入口是：A.开始选项卡-编辑组B.数据选项卡-数据工具组C.公式选项卡-定义名称组D.视图选项卡-显示组2.若要通过数据有效性创建下拉列表，且列表内容为“销售部、市场部、技术部”，在“来源”框中正确的输入方式是：A.销售部,市场部,技术部（英文逗号分隔）B.销售部；市场部；技术部（中文分号分隔）C.销售部/市场部/技术部（斜杠分隔）D.直接选择包含这三个内容的单元格区域（无分隔符）3.当用户输入的数据不符合有效性规则时，Excel默认的错误提示类型是：A.停止（必须输入有效数据）B.警告（可选择继续输入）C.信息（仅提示但允许输入）D.无提示（静默拒绝）4.若需在Sheet2的A列设置下拉列表，其来源为Sheet1的B2:B10区域，正确的来源表达式是：A.Sheet1!B2:B10B.Sheet2!B2:B10C.=Sheet1!B2:B10D.=Sheet2!B2:B105.关于数据有效性与下拉列表的关系，以下描述正确的是：A.下拉列表是数据有效性的唯一应用场景B.数据有效性可以限制数值范围，但无法直接提供下拉列表C.下拉列表必须通过数据有效性实现D.数据有效性的“序列”类型是创建下拉列表的主要方法6.若要让下拉列表随源数据区域的扩展自动更新（如源数据从A1:A5增加到A1:A8时，下拉列表自动包含新内容），最合理的设置方式是：A.直接输入源数据区域的绝对引用（如A1:A5）B.使用名称管理器定义动态名称（如=OFFSET(Sheet1!A1,0,0,COUNTA(Sheet1!A:C.手动修改每次源数据变化后的来源区域D.将源数据区域设置为混合引用（如A1:7.在数据有效性设置中，“输入信息”选项卡的作用是：A.当输入无效数据时提示错误原因B.当选中单元格时显示操作提示C.限制输入数据的类型或范围D.定义下拉列表的具体内容8.若需限制某单元格只能输入5-10位的数字，应在数据有效性的“允许”选项中选择：A.整数B.文本长度C.自定义D.序列9.以下哪种场景无法通过数据有效性下拉列表实现？A.根据部门选择自动关联岗位（如销售部下拉后显示“销售经理、销售专员”）B.限制输入的日期必须在本月内C.输入非列表内容时自动拒绝并提示D.下拉列表内容随工作簿关闭后永久保存（不依赖源数据区域）10.当源数据区域被删除或移动时，已设置的下拉列表会出现的情况是：A.下拉列表内容保持不变（与源数据无关）B.下拉列表显示错误值（如REF!）或空白C.自动恢复为可输入任意内容的状态D.系统自动提示重新选择源数据区域二、判断题（每题1分，共10分。正确填“√”，错误填“×”）1.数据有效性设置仅对当前选中的单元格生效，无法批量应用到整列或区域。（）2.下拉列表的来源可以是直接输入的文本列表（如“是,否”），也可以是单元格区域引用。（）3.合并单元格可以正常设置数据有效性下拉列表，且下拉选项会覆盖整个合并区域。（）4.若在“数据有效性”设置中勾选“忽略空值”，则允许单元格保留空白，否则必须输入有效数据。（）5.输入无效数据时，用户可以选择“取消”（返回原数据）或“确定”（强制输入），这取决于错误提示类型是否为“警告”。（）6.通过“数据有效性”设置的下拉列表，在复制到其他单元格时，其来源区域会自动调整为相对引用（如A1的下拉来源是B1:B3，复制到A2后来源变为B2:B4）。（）7.若要限制输入的身份证号必须为18位且最后一位是校验码（通过公式验证），应在“允许”中选择“自定义”并设置公式。（）8.数据有效性的“序列”类型中，若来源区域包含重复值，下拉列表会自动去重。（）9.在跨工作簿设置下拉列表时，需确保源工作簿处于打开状态，否则下拉列表可能显示错误。（）10.删除已设置数据有效性的单元格内容后，重新输入时仍需符合有效性规则。（）三、操作题（每题15分，共45分）1.请在Excel中完成以下操作（要求写出具体步骤）：背景：某公司员工信息表（Sheet1）的B列（部门）需要设置下拉列表，列表内容为“销售部、市场部、技术部、财务部”；同时，当选中B列单元格时，显示输入提示“请从下拉列表中选择部门”；当输入非列表内容时，显示错误警告“输入内容不在允许的列表中，请重新选择”，样式为“停止”。2.请基于Sheet2的A1:A10区域（已输入10个不同的项目名称），在Sheet3的B列设置动态下拉列表，要求当下拉列表的源数据区域（A1:A10）新增或删除项目名称时，下拉列表内容自动更新（无需手动修改来源）。3.某订单表（Sheet4）的D列（数量）需要满足以下规则：数值范围为1-100（含1和100）；输入非数值时提示“请输入1-100之间的整数”，样式为“警告”；输入数值小于1或大于100时提示“数量必须在1-100之间”，样式为“停止”；请写出设置数据有效性的具体步骤。四、综合应用题（共25分）背景：某公司需设计员工信息管理表（Sheet5），包含以下字段：工号（A列）、姓名（B列）、部门（C列）、岗位（D列）、入职日期（E列）、身份证号（F列）、联系电话（G列）。要求通过数据有效性实现以下功能：（1）部门（C列）下拉列表内容为“销售部、市场部、技术部”；（2）岗位（D列）下拉列表根据部门动态联动（如选择“销售部”时，岗位显示“销售经理、销售专员”；选择“市场部”时，显示“市场策划、市场推广”；选择“技术部”时，显示“开发工程师、测试工程师”）；（3）入职日期（E列）必须晚于2020年1月1日，且不早于当前日期（即入职日期≤今天）；（4）身份证号（F列）必须为18位，且最后一位校验码正确（校验码计算规则：前17位与对应的权重因子相乘求和，除以11的余数对应0-10的校验码，其中10用X表示）；（5）联系电话（G列）必须为11位数字（允许以“13”“15”“17”“18”开头）；（6）所有设置需包含输入提示（如部门列提示“请从下拉列表选择部门”）和错误警告（如岗位列输入无效内容时提示“请根据部门选择对应岗位”）。请详细说明实现上述功能的步骤（需结合数据有效性设置、名称管理器、公式应用等）。答案一、单项选择题1.B2.A3.A4.C5.D6.B7.B8.B9.D10.B二、判断题1.×（可通过选中整列/区域批量设置）2.√3.×（合并单元格无法正常设置）4.√5.√6.√（若来源为相对引用）7.√8.×（不会自动去重）9.√10.√三、操作题1.步骤如下：（1）选中Sheet1的B列（如B2:B100）；（2）点击“数据”选项卡-“数据工具”组-“数据有效性”；（3）在“设置”选项卡中，“允许”选择“序列”；（4）“来源”输入框中输入“销售部,市场部,技术部,财务部”（英文逗号分隔）；（5）切换到“输入信息”选项卡，勾选“选定单元格时显示输入信息”，标题输入“部门选择提示”，输入信息输入“请从下拉列表中选择部门”；（6）切换到“出错警告”选项卡，“样式”选择“停止”，标题输入“错误提示”，错误信息输入“输入内容不在允许的列表中，请重新选择”；（7）点击“确定”完成设置。2.步骤如下：（1）打开Sheet2，选中A1:A10区域，点击“公式”选项卡-“定义的名称”组-“定义名称”；（2）在“名称”框输入“项目列表”，“引用位置”输入公式：=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:（3）点击“确定”完成动态名称定义；（4）切换到Sheet3，选中B列（如B2:B100），点击“数据”选项卡-“数据有效性”；（5）“设置”选项卡中“允许”选择“序列”，“来源”输入“=项目列表”；（6）点击“确定”，此时B列下拉列表会随Sheet2的A列内容增减自动更新。3.步骤如下：（1）选中Sheet4的D列（如D2:D100），点击“数据”选项卡-“数据有效性”；（2）“设置”选项卡中，“允许”选择“整数”；（3）“数据”选择“介于”，“最小值”输入1，“最大值”输入100；（4）切换到“输入信息”选项卡（可选），设置提示信息（如“请输入1-100之间的整数”）；（5）切换到“出错警告”选项卡，分两种情况设置：若用户输入非数值（如文本），Excel默认触发“停止”警告，需调整为“警告”：在“样式”中选择“警告”，标题输入“非数值提示”，信息输入“请输入1-100之间的整数”；若输入数值超出范围（<1或>100），“样式”选择“停止”，标题输入“范围错误”，信息输入“数量必须在1-100之间”；（6）点击“确定”完成设置（注：Excel数据有效性无法直接区分“非数值”和“数值超范围”，实际需通过“自定义”公式实现更精细控制，此处为简化描述）。四、综合应用题（1）部门（C列）设置：选中C列（如C2:C100），点击“数据有效性”-“设置”-“允许”-“序列”，来源输入“销售部,市场部,技术部”（英文逗号分隔）；输入信息：标题“部门选择”，内容“请从下拉列表选择部门”；出错警告：样式“停止”，标题“错误”，内容“请选择指定部门”。（2）岗位（D列）二级联动设置：在Sheet5的空白区域（如H1:I4）建立部门-岗位映射表：H1:部门，I1:岗位；H2:销售部，I2:销售经理,销售专员；H3:市场部，I3:市场策划,市场推广；H4:技术部，I4:开发工程师,测试工程师；选中H2:H4，点击“公式”-“定义名称”，名称“部门列表”，引用位置“=Sheet5!H2:H4”；选中I2:I4，定义名称“岗位列表”，引用位置“=OFFSET(Sheet5!I2,MATCH(C2,部门列表,0)-1,0,1,1)”（C2为当前部门单元格）；选中D列（D2:D100），数据有效性-“序列”-来源输入“=岗位列表”；输入信息：“请根据部门选择岗位”；出错警告：“请选择与部门对应的岗位”。（3）入职日期（E列）设置：数据有效性-“允许”-“日期”；“数据”-“介于”，“最小值”输入“2020-1-1”，“最大值”输入“=TODAY()”；输入信息：“入职日期需在2020年1月1日之后且不晚于今天”；出错警告（停止）：“日期超出允许范围”。（4）身份证号（F列）验证：数据有效性-“允许”-“自定义”，公式输入：=AND(LEN(F2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(F2,1,17){7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2}),11)+1,1)=UPPER(RIGHT(F2,1)))输入信息：“请输入18位有效身份证号（末位X需大写）”；出错警告（停止）：“身份证号格式或校