搜档网
当前位置:搜档网 › 利用Excel数据有效性实现单元格下拉菜单多种分类选项

利用Excel数据有效性实现单元格下拉菜单多种分类选项

利用Excel数据有效性实现单元格下拉菜单多种分类选项
利用Excel数据有效性实现单元格下拉菜单多种分类选项

利用Excel数据有效性实现单元格下拉菜单多种分类选项

一、准备的基础知识

1、创建多个选项下拉菜单

在EXCEL单元格做下拉列表还有一个更好的方法,因为下拉列表的内容可能有30项甚至于100项以上,如在“数据-有效性-来源”中填写100项是做不到的,我记得最多只可填写30项。

创建30项以上方法(以50项为例):

在下拉列表中选择的50项内容填在A1-A50,选择“插入-名称-定义”,定义名称可填下拉内容“一级”,定义的引用位置是A1-A50,确定后将一级下拉内容填入“数据-有效性-来源”中或者在“数据-有效性-来源”中填

“=$A$1:$A$50”。

2、选择下拉菜单中的一项,附带多项数值用方

我做的表比较复杂,要实现在一行中输入数据同时它相关的一些数据都要出来,而且要输入的数据量很大。

如:A1是一个下拉列表,我选中AA,同时一行的AA 的型号,价格都出现,而且是每行都是这样,可以实现吗?复杂吗?

设:原数据表在sheet1表,A列为型号,B--H列为相关数据。新表建在Sheet2表,表格式同SHeet1表。选中Sheet1表的A列型号的区域(设为A2至A30),定义名称为“型号”。

在Sheet2表的A2单元格,数据→有效性,“允许”选“序列”,“来源”中输

入“=型号”(等于应在英文状态下输入),确定退出。

在B2单元格输入公式:

=IF($A2<>0,VLOOKUP($A2,Sheet1!$A$2:$H$30,COLUMN(),0),"") 再将B2单元格横向拉到H2单元格。再将A2至H2单元格向下拉若干行。A列选型号后,后面出现相关数据。

二. 下拉菜单多种分类选项快速批量输入

因工作需要,常常要将企业的单位名称输入到Excel表格中,由于要求每次输入同一个企业的名称要完全一致,我就利用“数据有效性”制作了一个下拉列表来进行输入。但由于有150多个单位名称,下拉列表太长,选择起来非常不方便,于是,我对其进行了改进,实现了“分类列表选择、快速统一输入”之目的。

使用实例界面:

1、建库

启动Excel2000(XP也可),切换到Shift2工作表(其他工作表也可)中,将建筑施工企业名称按其资质等级分别分别输入不同列的单元格中,建立一个企业名称数据库(如图1)。

[特别提示]如果有其他企业加入,可以随时将名称输入到该列下面的单元格中。

2、命名

在Shift2工作表中,选中A列(一级企业名称所在列),然后将光标定在“名称”栏内(位于“编辑栏”左侧,如图2),输入“一级”,并按“回车”键进行确认。

仿照上面的操作,将B、C、D列分别命名为“二级、三级、四级”。

3、建立

切换到Sheet1工作表中,选中需要输入企业资质等级的列(如C列),执行“数据→有效性”命令,打开“数据有效性”对话框(如图3)。进入“设置”标签,单击“允许”右侧的下拉按钮,选中“序列”选项,在下面接着出现的“来源”方框中,输入“一级,二级,三级,四级”序列,输入完成后,确定退出。

[特别提示]在输入“序列”时,序列中各元素之间请用英文状态下的逗号隔开。

再选中需要输入企业名称的列(如D列),再次打开“数据有效性”对话框,选中“序列”选项后,在“来源”方框中输入公式:=INDIRECT(C1),确定退出。

4、使用

选中C列任意单元格(如C4),在其右侧出现了个下拉按钮,单击此按钮,即可选择企业的资质等级级别填入相应的单元格中。然后选中该单元格对应的D列单元格(如图4),同样在其右侧出现了个下拉按钮,单击此按钮,即可从相应资质等级的企业名称列表中选择需要的企业名称填入该单元格中。

三. 将下拉菜选项分类单快速批量输入的另一种方法

有如下一份省市数据:省份以及省份所辖地市清单,需要实现多级联动下

拉菜单操作,具体操作方式如下所示:

第一步:对各单元格区域进行名称定义

选中A1:A5单元格区域,然后公式——根据所选内容创建——如图设置PS:分别对B、C、D、E列重复以上操作

第二步:设置省份列的选项

选中G1:G22单元格区域,然后数据——数据有效性

弹出数据有效性设置窗口,然后如图设置

其中来源“=省份”,这里的省份与之前自定义的单元格区域名称是相对应的

省份选项搞定

第三步:设置地市列的选项:

选中H1:H22单元格区域,然后数据——数据有效性

在弹出的窗口,设置如下:其中来源=indirect($G1),PS:indirect函数,返回由文本字符串指定的引用。

地市选项也搞定了,N级操作以此类推

最新-Excel中如何对数据进行分类汇总 精品

Excel中如何对数据进行分类汇总 篇一:怎样在中对数据进行分类汇总怎样在中对数据进行分类汇总当需要在中对数据进行分类计算时,除了使用数据透视表,还可以使用分类汇总命令。 与数据透视表不同的是,它可以直接在数据区域中插入汇总行,从而可以同时看到数据明细和汇总。 下面是分类汇总的使用方法:在进行分类汇总前,需保证数据具有下列格式,即数据区域的第一行为标题行,数据区域中没有空行和空列,数据区域四周是空行和空列,如下图是几种商品在一些城市的销售数据。 另外,如果数据区域在应用分类汇总前已被设置成2019列表或2019表,需将其转换为普通区域。 因为对于2019列表或2019表无法使用分类汇总。 一、仅对某列进行分类汇总例如上例中需要对各城市的销售量进行分类汇总,方法如下:1首先对数据按需要分类汇总的列(本例为“城市”列)进行排序。 选择“城市”列中的任意单元格,在2019中单击工具栏中的排序按钮如“→”。 在2019中,选择功能区中“数据”选项卡,在“排序和筛选”组中单击“→”按钮。 2选择数据区域中的某个单元格,在2019中单击菜单“数据→分类汇总”。 如果是2019,则在“数据”选项卡的“分级显示”组中单击“分类汇总”。 3在弹出的“分类汇总”对话框中,在“分类字段”下选择“城市”,在“汇总方式”中选择某种汇总方式,可供选择的汇总方式有“求和”、“计数”、“平均值”等,本例中选择默认的“求和”。 在“选定汇总项”下仅选择“销售额”。 4单击确定,将按城市进行分类汇总。 二、对多列进行分类汇总如上例中需要同时对“城市”列和“商品名称”列进行分类汇总,可以插入嵌套分类汇总。 1对数据进行多列排序,即进行多关键字排序。 首先选择数据区域中的某个单元格。 在2019中,单击菜单“数据→排序”。 弹出“排序”对话框,其中主要关键字选择“城市”,次要关键字选择“商

Excel录入中实现单元格多选项下拉

当我们在Excel表格中需要输入大量的重复数据时,往往利用数据的有效性来制作一个下拉菜单以提高重复数据的输入速度。但在实际的操作过程中,必须选中需要输入重复数据的单元格并单击该单元格右边的下拉箭头,才可以看到自己事先定制的数据有效性的下拉菜单。那么,能否实现在选中Excel单元格时自动展开数据有效性的下拉菜单以使得重复数据的输入更快一步呢?答案当然是肯定的。接下来。笔者就给大家介绍一下如何来实现。 先启动Excel 2003并打开某个已设置好数据有效性下拉菜单的工作簿(如“工资表.xls”),然后单击菜单“工具→宏→Visual Basic 编辑器”命令,调出“Visual Basic 编辑器”;接着在“Visual Basic 编辑器”窗口中,双击“工程-VBAPRoject”面板中的“Sheet1(*)”选项(*为当前需要快速输入重复数据的工作表),在弹出的代码窗口中输入以下代码:Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 3 Then application.SendKeys “%{down}” If Target.Column = 4 Then Application.SendKeys “%{down}” End Sub 上述代码输入完毕后,关闭“Visual Basic 编辑器”窗口,此时我们输入的代码就会自动保存。 小提示:代码的含义是当我们选中第3列、第4列(即C列和D列)的任意一个单元格时,都会自动展开数据有效性的下拉菜单。在实际的操作过程中,大家可灵活地修改这两个自然数以满足自己的需要。 完成上述操作后,关闭“工资表.xls”并保存对其所做的修改,然后重新打开它,接着选中C列或D列的任意一个单元格(如图中的D11单元格),此时就可以看到该列单元格自动展开的数据有效性的下拉菜单了,如图A处。这样一来我们输入那些重复的数据就可以比原来更快一步了。有此需求的朋友不妨一试。 转载请注明文章出处https://www.sodocs.net/doc/8b14632698.html,/portal-view-aid-7550.html

Excel数据有效性(数据验证)应用详解

Excel数据有效性(数据验证)应用详解 我们可以利用数据有效性制作表格模板,强制性要求其他人按规矩填写表格。 看课件: 1、利用数据验证为单元格的数据输入设置条件限制 在表格内输入数据时,我们可以利用数据验证来规范数据的类型,甚至限制输入数值的大小范围。我们先来利用有效性对基本工资这一列进行设置:规定只能填写整数,并且不低于3500 选中这一列,然后点击数据有效性

在【允许】下拉选项里选中整数(这里还有很多其他的项目,有兴趣的朋友可以抽空自己琢磨琢磨) 选中整数以后,下面会出现【数据】这个下拉选项,如果【允许】选择的是其他项目,下面的选项菜单也会发生相应变化。 最小值我们填入3500,点确定就好了

这时候如果输入的数据不符合我们的规定,就会弹出提示框。 接下来我们对身份证号码这一列进行设置,要求是长度必须等于18位,防止输入错误: 同样的,选择这一列,设置有效性:文本长度等于18.

当输入的号码不是18位的时候,同样会提示错误。 对于日期的输入,是不规范的情况最多的一类数据,我们同样可以使用数据有效性进行限制:只能输入2010年1月1日到2018年1月31日之间的日期,并且只能是标准的日期格式: 如图进行设置。 特别说明一点,如果在开始日期或者结束日期输入格式不对的日期时,是会报错的:

2018.1.31这种是最常见的错误格式。 日期超过范围会提示

日期格式不对也会提示 接下来对性别进行设置,只能输入男或者女: 注意,来源里的项目之间用英文的逗号分隔。 这样设置以后,就可以使用下拉菜单进行填表了。 再来对姓名进行设置,要求是不能出现重名,如果有重名的话,需要加数字进行区分。

工作表保护和数据有效性的综合案例

工作表保护和数据有效性的综合案例 彭志伟 广州市数宇软件有限公司 数据有效性是Excel当中一个非常实用的功能,它可以对单元格的输入内容进行限制,从而达到表格数据输入规范和准确的目的。工作表保护可以锁定工作表的内容,并且可以控制哪些单元格允许用户填写,哪些单元格不能填写。企业学员对这两项功能也特别感兴趣,在企业中的应用也很多,尤其是用在模板方面。 下面这个范例是一张报销单的例子,它综合了工作表保护和数据有效性两个功能来实现的。首先,请大家先新建立一个Excel文档,并仿照下图建立一个数据表。 报销单功能描述: ●整张报销单中,除了白色底纹的单元格可以录入数据以外,其他的单元格都被保护。 ●C5单元格只能输入1-4个字符,如输入错误能提示“本单元格文本长度必须为1-4 个字符。”字样。 ●E5单元格只能输入介于18到60之间的整数,如输入错误能提示“本单元格必须 输入18-60之间的整数。”字样 ●G5单元格做成下拉菜单,菜单项包括“男”和“女”。 ●C6单元格做成下拉菜单,菜单项包括“财务部”、“销售部”、“培训部”和“人事 部”。 ●F6单元格能自动出现当前日期。 ●F8:F11区域单元格只能输入数字,并且整张报销单的总金额不能超过一万元,单 元格设置为带货币的两位小数格式。 ●D12单元格能自动统计F8:F11区域的总和。

操作提示: 1.选中C5单元格,打开“数据/有效性”,在“有效性条件”作如下图设置:

4.对C6单元格作如下图的有效性设置: 5.在F6单元格中输入函数= today() 。 6.选中F8:F11区域,将此区域的有效性设置为自定义,并输入以下公式 =AND(ISNUMBER(F8),SUM($F$8:$G$11)<10000) 7.在D12单元格中输入函数= sum(F8:G11)。

如何在Excel中设置下拉选项,实现下拉列表

在Excel中实现下拉列表 一、直接输入: 1.选择要设置的单元格,例如A1单元格; 2.选择菜单栏的“数据”→“数据有效性”→出现“数据有效性”弹出窗口; 3.在“设置”选项卡中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉箭头”全部打勾→在“来源”下面输入数据,如“部门1,部门2,部门3,部门4,部门5”(不包括双引号,分割符号“,”必须为半角模式)→点击“确定”完成,再次选择该A1单元格,就出现了下拉菜单。当然,可以拖动单元格扩展填充序列。 如图所示: 二、引用同一工作表内的数据:

如果同一工作表的某列区域就是下拉菜单想要的数据,例如引用工作表Sheet1的B1:B5。B1:B5分别有以下数据:部门1、部门2、部门3、部门4、部门5,操作如下: 1.选择要设置的单元格,例如A1单元格; 2.选择菜单栏的“数据”→“数据有效性”→出现“数据有效性”弹出窗口; 3.在“设置”选项卡中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉菜单”全部打勾→在“来源”下面输入数据 “=$B$1:$B$5”,也可以直接点击单元格选择B1:B5区域→按“确定”完成,再次选择该A1单元格,就出现了下拉菜单。 如图所示: 三、引用不同工作表内的数据(必须为引用的区域定义名称): 如果不同工作表的某列区域就是下拉菜单想要的数据,譬如工作表Sheet1的A1单元格要引用工作表Sheet2的A1:A5区域。工作表Sheet2的A1:A5分别有以下数据:部门1、部门2、部门3、部门4、部门5,操作如下: 1. 为引用单元格区域定义名称:

(完整版)EXCEL--数据有效性

数据的有效性----设置输入条件 在很多情况下,设置好输入条件后,能增加数据的有效性,避免非法数据的录入。如年龄为负数等。那么有没有办法来避免这种情况呢?有,就是设置数据的有效性。设置好数据的有效性后,可以避免非法数据的录入。 下面我们还是以实例来说明如何设置数据的有效性。 1. 设置学生成绩介于0----100分之间。 图6-4-1 假设试卷的满分100分,因此学生的成绩应当介于0----100分之间的。我们可以通过以下几步使我们录入成绩时保证是0----100分之间,其它的数据输入不能输入。 第1步:选择成绩录入区域。 此时我们选中B3:C9 。 第2步: 点击菜单数据―>有效性,弹出数据有效性对话框。界面如图6-4-2所示。

图6-4-2 在图6-4-2中的默认的是允许任何值输入的,见图中红色区域所示。 第3步:点击允许下拉按钮,从弹出的选项中选择小数。(如果分数值是整,此处可以选择整数) 图6-4-3

选择后界面如图6-4-4所示。在图6-4-4中,数据中选择介于,最小值:0 ,最大值:100 。 图6-4-4 第4步:点击确定按钮,完成数据有效性设置。 下面我们来看一下,输入数据时有何变化。见图6-4-5所示。

图6-4-5 从图中可以看出,我们输入80是可以的,因为80介于0----100之间。而输入566是不可以的,因为它不在0----100之间。 此时,点击重试按钮,重新输入,点击取消按钮,输入的数据将被清除。 第5步:设置输入信息 在数据有效性对话框中,点击输入信息页,打开输入信息界面。如图6-4-6所示。 图6-4-6 设置好输入信息后,我们再来看输入数据时,界面有何变化。如图6-4-7所示。

用ExcelXX数据有效性拒绝错误数据

用Excel XX数据有效性拒绝错误数据 Excel强大的制表功能,给我们的工作带来了方便,但是在表格数据录入过程中难免会出错,一不小心就会录入一些错误的数据,比如重复的身份证号码,超出范围的无效数据等。其实,只要合理设置数据有效性规则,就可以避免错误。下面咱们通过两个实例,体验Excel XX数据有效性的妙用实例一:拒绝录入重复数据 身份证号码、工作证编号等个人ID都是唯一的,不允许重复,如果在Excel录入重复的ID,就会给信息管理带来不便,我们可以通过设置Excel XX 的数据有效性,拒绝录入重复数据。 运行Excel XX,切换到“数据”功能区,选中需要录入数据的列(如:A列),单击数据有效性按钮,弹出“数据有效性”窗口。 图1 数据有效性窗口切换到“设置”选项卡,打下“允许”下拉框,选择“自定义”,在“公式”栏中输入“=countif(a:a,a1)=1”(不含双引号,在英文半角状态下输入)。 图2 设置数据有效性条件切换到“出错警告”选项卡,选择出错出错警告信息的样式,填写标题

和错误信息,最后单击“确定”按钮,完成数据有效性设置。 图3 设置出错警告信息这样,在A列中输入身份证等信息,当输入的信息重复时,Excel立刻弹出错误警告,提示我们输入有误。 图4 弹出错误警告这时,只要单击“否”,关闭提示消息框,重新输入正确的数据,就可以避免录入重复的数据。 实例二:快速揪出无效数据 用Excel处理数据,有些数据是有范围限制的,比如以百分制记分的考试成绩必须是0—100之间的某个数据,录入此范围之外的数据就是无效数据,如果采用人工审核的方法,要从浩瀚的数据中找到无效数据是件麻烦事,我们可以用Excel XX的数据有效性,快速揪出表格中的无效数据。 用Excel XX打开一份需要进行审核的Excel 表格,选中需要审核的区域,切换到“数据”功能区,单击数据有效性按钮,弹出数据有效性窗口,切换到“设置”选项卡,打开“允许”下拉框,选择“小数”,打开“数据”下拉框,选择“介于”,最小值设为0,最大值设为100,单击“确定”按钮(如图5)。

excel数据有效性的应用范文

技巧1 在单元格创建下拉列表 有许多新手在EXCEL中第一次见到下图所示的下拉列表时,都以为是程序做的,当他们知道图中下拉列表只是一个普通的利用数据有效性完成的EXCEL技巧时,他们会觉得很惊奇。 那么,现在我们一起学习一下,怎么利用数据有效性来做个下拉列表吧: 第一步在一个连续的单元格区域输入列表中的项目,如图中E7:E11有个商品名称的表 第二步选中A2单元格,单击“菜单”——“数据”——“有效性”,在“数据有效性”对话框的"设置"选项卡中,在“允许”下拉列表中选择“序列” 项. 第三步在"来源"框中输入“=$E$8:$E$11”(或输入“=”号后,用鼠标选中E8:E11) 第四步勾选"忽略空值"与"提供下拉箭头"复选框,如图所示 第五步单击"确定"按钮,关闭"数据有效性"对话框. 这样,就能实现第一张图所示的效果了。 如果列表的内容较少,或者不方便在工作表中输入列表项目,也可以省略上述的第一步,然后将第

三步的操作 改为:直接在"来源"框中输入列表内容,项目之间以半角的逗号分隔.如图所示 在一般情况下,数据的有效性中的序列来源,只能引用当前工作表中的单元格区域。 如果希望能够引用其他工作表中的单元格区域,则必须先为单元格区域定义名称,然后在"来源"框中输入名称. 例如,将另一张工作表中的A2:A10区域,名称定义为“SPMC”,然后在“数据有效性”的“来源”框中输入“=SPMC”。 技巧二: 另类的批注 当我们需要对表格中的项目进行特别说明时,常常会使用EXCEL的批注功能。给单元格做批注的方法,这里不 多浪费时间。而给大家介绍一下另类批注: 使用批注多了,我们会发现EXCEL的批注也有不足之处: 一、批注框的大小尺寸会受到单元格行高变化的影响; 二、批注框的默认情况下,是只显示标识符。必须把光标悬停在单元格的上方批注内容才会显示出来,否则即使当单元格处于活动状态时,它也不会显示; 三、是在上面2种情况的共同作用下,加上拆分(冻结)窗口下的插入、拖曳等工作表操作,会导致批注的位置远离原来的单元格,而被主人遗忘,并随着主人对单元格的复制或格式刷操作而被大量复制,这也是造成文件增肥的主要原因之一。我曾经为一个会员给他的文件减肥时,从表里找出3500多个远离母单元格的批注弃儿,最终我通过删除这些个“批注弃儿”,帮那个会员给文件容量缩减了2/3之多。 言归正传,说说数据有效性 利用数据有效性功能,我们可以实现另类的批注效果,克服以上不足。 第一步:选定单元格,如C1。 第二步:单击菜单"数据"-"有效性",在"数据有效性"对话框的"输入信息"选项卡中,勾选"选定单元格时显示

Excel分类汇总使用全攻略

Excel分类汇总使用全攻略 操作指南 利用Excel进行数据分析时,需要根据某个字段进行快速汇总数据,例如在统计学生成绩时,希望查看各班各门课程的平均成绩,应该怎么做呢? 这就需要使用Excel的分类汇总功能,分类汇总就是把数据表中的数据分门别类地进行快速汇总、统计处理,Excel会自动对各类别的数据进行求和、求平均值等多种计算。 1.创建分类汇总 第一步:选中表格数据区域内的任意单元格,打开【数据】选项卡的【排序和筛选】选项组,选择【排序】按钮。创建分类汇总之前,首先要依据分类字段进行排序。 第二步:在打开【排序】对话框中,选择【主要关键字】为【性别】,单击【确定】。 第三步:选中表格数据区域内的任意单元格,打开【数据】选项卡的【分级显示】选项组,选择【分类汇总】按钮。 第四步:在打开的【分类汇总】对话框,选择【分类字段】为性别,汇总方式为求平均值,选定汇总项为大学英语、高等数学、应用文写作、计算机应用等各门课程,默认勾选【替换当前分类汇总】、【汇总结果显示在数据下方】,如果想将分类汇总的数据分页显示,可将【每组数据分页】复选框勾选,单

击【确定】。 这样就可以完成分类汇总,求出男、女生各门课程的平均成绩。 并且是分级显示的,左侧有显示级数。 点击1表示各门课程的平均值。 2表示男、女生各门课程的平均值。 3表示全班男、女生各门课程及平均值。

2.消除分级显示 系统会默认在表格左侧建立分级显示,一般为三级,如果不需要或者看不习惯的话,可不可以删除呢? 将光标定位到要清除分级显示的工作表内,切换到【数据】选项卡,在【分级显示】选项组中找到“取消组合”下的“清除分级显示”。 这样表格左侧的分级显示已经清除完毕,但汇总结果仍然保留着。 3.建立分级显示 如果清除后的分级,又想显示出来,将光标定位到建立分级显示的工作表内,切换到【数据】选项卡,在【分级显示】选项组中找到【创建组】下的【自动建立分级显示】,这样表格左侧的分级显示又会创建出来。 4.删除分类汇总 如果不想进行分类汇总,显示至原来的详细数据信息,就要要删除分类汇总。 选中表格数据区域内的任意单元格,切换至【数据】选项卡,在【分级显示】组中单击【分类汇总】按钮,在弹出的【分类汇总】对话框中单击“全部删除”即可。 5.嵌套分类汇总 分类汇总还可进行嵌套分类汇总,例如要统计各班男、女生各门课程的平均成绩。 进行分类汇总前,必须设定主要关键字和次要关键字进行排序操作,如要统计各班男、女生各门课程的平均成绩,则设置主要关键字为班级,次要关键字为性别。

EXCEL中数据有效性自定义怎么使用

EXCEL中数据有效性“自定义”怎么使用[应用一]下拉菜单输入的实现 例1:直接自定义序列 有时候我们在各列各行中都输入同样的几个值,比如说,输入学生的等级时我们只输入四个值:优秀,良好,合格,不合格。我们希望Excel2000单元格能够象下拉框一样,让输入者在下拉菜单中选择就可以实现输入。 操作步骤:先选择要实现效果的行或列;再点击"数据\有效性",打开"数据有效性"对话框;选择"设置"选项卡,在"允许"下拉菜单中选择"序列";在"数据来源"中输入"优秀,良好,合格,不合格"(注意要用英文输入状态下的逗号分隔!);选上"忽略空值"和"提供下拉菜单"两个复选框。点击"输入信息"选项卡,选上"选定单元格显示输入信息",在"输入信息"中输入"请在这里选择"。 例2:利用表内数据作为序列源。 有时候序列值较多,直接在表内打印区域外把序列定义好,然后引用。 操作步骤:先在同一工作表内的打印区域外要定义序列填好(假设在在Z1:Z8),如“单亲家庭,残疾家庭,残疾学生,

特困,低收人,突发事件,孤儿,军烈属”等,然后选择要实现效果的列(资助原因);再点击"数据\有效性",打开"数据有效性"对话框;选择"设置"选项卡,在"允许"下拉菜单中选择"序列";“来源”栏点击右侧的展开按钮(有一个红箭头),用鼠标拖动滚动条,选中序列区域Z1:Z8(如果记得,可以直接输入=$Z$1:$Z$8;选上"忽略空值"和"提供下拉菜单"两个复选框。点击"输入信息"选项卡,选上"选定单元格显示输入信息",在"输入信息"中输入"请在这里选择"。 例3:横跨两个工作表来制作下拉菜单 用INDIRECT函数实现跨工作表 在例2中,选择来源一步把输入=$Z$1:$Z$8换成=INDIRECT("表二!$Z$1:$Z$8"),就可实现横跨两个工作表来制作下拉菜单。 [应用二]自动实现输入法中英文转换 有时,我们在不同行或不同列之间要分别输入中文和英文。我们希望Excel能自动实现输入法在中英文间转换。

Excel函数数据有效性例题大全

Excel函数与数据有效性配合快速填通知书 用Excel函数中的vlookup查询函数和数据有效性功能配合来填写通知书,可以免去老师们一个一个写的繁琐劳动,这下不用写到手抽筋了! 第一步:处理学生成绩 把学生的期末考试成绩放在Sheet1表中,算出每个学生的成绩总分,为了在后面输函数公式时方便,我在前面加了一列“序号”。把Sheet1表重命名为“考试成绩”。如图1所示。 第二步:设置“通知书”模版 在“考试成绩”表旁的空白表Sheet2中,设置好“通知书”的基本格式和文字内容,页面设置为B5纸,底色可以设置为默认。如图2所示。

右击表“通知书”的A1单元格,选择“设置单元格格式”命令,弹出“单元格格式”对话框,选择“字体”选项卡,把字体颜色设置为“白色”,“确定”即可。如图3所示。

它的作用在后面就会体现出来。设置好后把此表表名重命名为“通知书”。 第三步:插入“查询函数” 在“通知书”表的C3单元格输入函数“=Vlookup(A1,考试成绩!A3:J4 3,2,FALSE)”,如图4所示。

此公式的含义是:使用Vlookup查询函数,根据A1单元格的内容,在“考试成绩”表的A3到J43单元格中进行查询,把查询到相同内容的这行的第2个单元格的内容显示在C3单元格中。即根据A1单元格的内容,把考试成绩表中与之相同内容的这行的第2个单元格的姓名提取到此单元格。由此在A10单元格中输入函数“=Vlookup(A1,考试成绩!A3:J43,3,FALSE)”,理解了C3、A10单元格的函数后,根据同样的原理我们分别如法设置B10、C10、D10、E10、F10、G10就可以了。

Excel分类汇总使用全攻略

《Excel分类汇总使用全攻略》Excel表格数据分类汇总怎么用 Excel中分类汇总指的是在工作表中的数据进行了基本的数据管理之后,再使数据达到条理化和明确化的基础上,利用Excel本身所提供的函数,对数据进行了一种数据汇总。数据的分类汇总是分为两个步骤进行的,第一个步骤是利用排序功能进行数据分类汇总。第二个步骤是利用了函数的计算,进行了一个汇总的操作。下面我们针对三种不同的分类汇总需求,为大家介绍不同的解决方案。 一、既想分类汇总,又想分类打印 解决方案:直接利用Excel内置的“分类汇总”功能来实现。 ①选中工程类型列的任意一个单元格,按一下“常用”工具栏上的“升序排序”或“降序排序”按钮,对数据进行一下排序。(使用“分类汇总”功能时, 一定要按分类对象进行排序!) ②执行“数据→分类汇总”命令,打开“分类汇总”对话框。 ③将“分类字段”设置为“工程类型”;“汇总方式”设置为“求和”;“选定汇总项”为“面积”和“造价”;再选中“每组数据分页”选项。最后,

确定返回。 ④分类汇总完成(下图是是“综合办公”类的打印预览结果)。 二、不想分类打印,只是想随时查看各类数据的明细和统计情况 解决方案:利用Excel自身的“自动筛选”功能来实现。 ①任意选中数据表格中的某个单元格,执行“数据→筛选→自动筛选”命 令,进入“自动筛选”状态。 ②分别选中F203、G203单元格(此处假定表格中共有200条数据),输入公式:=SUBTOTAL(9,F3:F202)和=SUBTOTAL(9,G3:G202)。(此函数有一个特殊的功能,就是后面进行自动筛选后,被隐藏行的数据不会被统计到其中,达到分类统 计的目的。) ③以后需要随时查看某类(如“经济住宅”)数据的明细和统计情况时,点 击“工程类型”右侧的下拉按钮,在随后弹出的快捷菜单中即可。 三、不打乱正常的流水式数据表格的数据顺序,随时查看各类数据统计结果(此处假 定将统计结果保存在另外一个工作表中)

Excel表格制作二级下拉菜单全步骤

Excel表格制作二级下拉菜单全步骤 通过2级菜单,倒是可以比较深入熟悉INDIRECT, INDEX, OFFSET, 这三个函数,是你要想学excel函数,估计你肯定过的关。 不过二级菜单的第一级的做法,都是相同的,大家可以参考上面的文章创建一个一级菜单。 第一种做法:使用函数INDIRECT 看看例子 很明显,我们希望在一级选择机房故障,那么2级就出现他下面的列表。 要实现这个目标并不复杂。 1:建立“名称” 3个一级选项,我们需要建立3个“名称”:机房故障线路故障用户端故障。 这个时候,你就创建了3个名称

还需要建立一个名称,叫做“故障类型”这个名称包括“机房故障线路故障用户端故障”这3项。

这个时候,你就可以在“名称管理器”看到4个名称2:创建1级菜单 1级菜单的办法就简单了 这样就解决了一级的下拉菜单。 3:2级菜单

这个地方你需要注意,=INDIRECT($F2) 如果你写成=INDIRECT($F$2), 如果是这样的话,你确定会出现一个报错这个时候,你就实现了2级菜单

第二种做法:使用函数INDEX 还是上面的例子,用另外一种方式来实现。这个例子里,你只需要建立2个名称。 1:建立名称 建立:故障类型名称,方法和上面一样。 建立:2级名称

创建上面两个名称。 2:创建下拉菜单 第一下拉菜单和上面一样,我就不重复了,现在是演示第二级下拉菜单 =INDEX(二级,0,match(I$2,故障类型,0)) 这样你就搞定2级菜单里。 第三种做法:使用函数OFFSET 学这个offset函数半天,才搞定。现在只是可以实现这个功能。不过还有需要改进的地方 还是上面的例子,

EXCEL如何设置数据有效性

怎样在excel中利用有效性序列建立二级下拉菜单 比如在A列中选择部门名称,B列中的选择菜单自动会变成该部门下所有员工 问题补充: 问题一样。那答案呢? madm 的二级下拉菜单的公式如何运用。。能否发文件上来 提问者: superaoyi - 一级最佳答案 试试、看看,是否所需! 设置“数据”表 A列 B列 部门员工 A 张三李四 B 王二郑大 C 刘一王五 D 初一赵钱 …… 命名: 选中A列,在“名称框”中输入“部门”,回车确认。 选中B列,在“名称框”中输入“员工”,回车确认。 在“菜单”表制作下拉菜单: 制作一级下拉菜单 选中A1:B1单元格区域; 执行“数据/有效性”命令,打开“数据有效性”对话框; 在“设置”选项卡下,“允许”选择“序列”、“来源”中输入“部门,员工”(不含引号,用英文逗号分隔); 选中“忽略空值”、“提供下拉箭头”,单击“确定”按钮,完成一级下拉菜单制作。 此时在A1、B1中,单击右侧的下拉按钮进行选择输入。 制作二级下拉菜单 从A2单元格起向下选中单元格区域; 执行“数据/有效性”命令,打开“数据有效性”对话框; 在“设置”中,“允许”选择“序列”、“来源”中输入公式“=INDIRECT(A$1)”; 选中“忽略空值”、“提供下拉箭头”,单击“确定”按钮,完成“部门”的二级菜单制作。同法制作“员工”的二级菜单。此时“来源”中输入公式“=INDIRECT(B$1)”。

此时在部门、员工下面的单元格中,单击右侧的下拉按钮进行“部门”、“员工”的选择输入。 Excel二级下拉菜单下拉菜单的相关联(2009-07-22 10:43:49) 标签:杂谈 公司因业务需要,经常要向外界发送大量信函,因此查找邮政编码,就成了一件非常头痛的事,于是,我就用Excel制作了一个简单的查询表,使用起来觉得很方便,现在就推荐给大家。 1. 启动Excel 2003(其他版本请大家仿照操作),新建一工作簿,取名保存。 2. 切换到Sheet2工作表中,仿照图1的样式,将相关数据输入到表格相应的单元格中。 提示:有关邮政编码的数据可以在网络上搜索到,然后复制粘贴到Excel中,再整理一下即可。 3.选中B1至B10单元格(即北京市所有地名所在的单元格区域),然后将鼠标定位在右上侧“名称框”中,输入“北京市”字样,并用“Enter”键进行确认。 4. 仿照上面的操作,对其他省、市、自治区所在的单元格区域进行命名。 提示:命名的名称与E列的省、市、自治区的名称保持一致。 5. 选中E1至E30单元格区域,将其命名为“省市”(命名为其他名称也可)。 6. 切换到Sheet1工作表中,仿照图3的样式,输入“选择省市”等相关固定的字符。 图3 选择查询的省市 7.选中B5单元格,执行“数据→有效性”命令,打开“数据有效性”界面(如图4),单击“允许”右侧的下拉按钮,在随后弹出的下拉列表中,选择“序列”项,然后在“来源”

Excel 2011数据有效性的妙用

Excel 2010数据有效性的妙用 来源: https://www.sodocs.net/doc/8b14632698.html,时间: 2010-01-29 作者: apollo Excel强大的制表功能,给我们的工作带来了方便,但是在表格数据录入过程中难免会出错,一不小心就会录入一些错误的数据,比如重复的身份证号码,超出范围的无效数据等。Excel 2010中有个“数据有效性”的功能,只要合理设置数据有效性规则,就可以避免错误。下面咱们通过两个Excel 2010数据有效性应用实例,体验Excel 2010数据有效性的妙用。 实例一:拒绝录入重复数据 身份证号码、工作证编号等个人ID都是唯一的,不允许重复,如果在Excel 录入重复的ID,就会给信息管理带来不便,我们可以通过设置Excel 2010的数据有效性,拒绝录入重复数据。 运行Excel 2010,切换到“数据”功能区,选中需要录入数据的列(如:A列),单击数据有效性按钮,弹出“数据有效性”窗口。 图1数据有效性窗口 切换到“设置”选项卡,打下“允许”下拉框,选择“自定义”,在“公式”栏中输入“=countif(a:a,a1)=1”(不含双引号,在英文半角状态下输入)。

图2设置数据有效性条件 切换到“出错警告”选项卡,选择出错出错警告信息的样式,填写标题和错误信息,最后单击“确定”按钮,完成数据有效性设置。 图3设置出错警告信息 这样,在A列中输入身份证等信息,当输入的信息重复时,Excel立刻弹出错误警告,提示我们输入有误。

图4弹出错误警告 这时,只要单击“否”,关闭提示消息框,重新输入正确的数据,就可以避免录入重复的数据。 实例二:快速揪出无效数据 用Excel处理数据,有些数据是有范围限制的,比如以百分制记分的考试成绩必须是0—100之间的某个数据,录入此范围之外的数据就是无效数据,如果采用人工审核的方法,要从浩瀚的数据中找到无效数据是件麻烦事,我们可以用Excel 2010的数据有效性,快速揪出表格中的无效数据。 用Excel 2010打开一份需要进行审核的Excel表格,选中需要审核的区域,切换到“数据”功能区,单击数据有效性按钮,弹出数据有效性窗口,切换到“设置”选项卡,打开“允许”下拉框,选择“小数”,打开“数据”下拉框,选择“介于”,最小值设为0,最大值设为100,单击“确定”按钮(如图5)。

Excel如何固定表头,设置单列下拉菜单

Excel如何固定表头,设置单列下拉菜单 作者:梳子出处:博客2011-06-15 07:32 鼠标选定B1单元格(可根据具体情况选定,这里是固定第一行为表头)…… 如何固定表头: 鼠标选定B1单元格(可根据具体情况选定,这里是固定第一行为表头) 窗口选项——选择拆分——再次选择冻结窗口——完成 如何设置单列下拉菜单: 无关联添加:使用有效性-序列,将要下拉的内容输入框内,中间以逗号隔开。 可设置项添加: (1)首先在Excel中设计好所需要的表格,在任意一列中输入要在下拉列表中所显示的内容,然后选定要使用选择性输入的区域。 (2)单击“数据→有效性”命令,在弹出的窗口中单击“设置”选项卡。在“允许”下拉列表中选择“序列”;在“来源”框中输入序列来源的单元格绝对地址,或者单击其右侧的隐藏窗体按钮,用鼠标选定要使用下拉列表中所显示的内容的单元格区域,再次单击隐藏窗体按钮回到“设置”选项卡。最后单击“确定”按钮返回(如图3)。 (3)用鼠标单击区域中的任一单元格,会在单元格旁边出现一个下拉箭头,单击箭头,会显示出所有的设置序列,从中单击所需的项目即可完成录入。 关联性添加: 1、在A1到A10输入名称,在B1到B10输入电话号码,做为源数据。 单位1 1000001

单位2 1000002 单位3 1000003 单位4 1000004 单位5 1000005 单位6 1000006 单位7 1000007 单位8 1000008 单位9 1000009 单位10 1000010 2、选中D1,菜单中选择数据,有效性,允许“序列”,来源:=$A$1:$A$10,D1下拉菜单可以选择公司名称。 3、选中E1,输入公式:=VLOOKUP(D1,$A$1:$B$10,2,0),在E1中根据D1公司名称的变化得到相应的电话号码。 根据实际情况,对源数据进行改变,在公式做相应变化即可。 可将数据源放在另一sheet中,或放在另一Excel文件中。如果在同一文件中,可以定义数据源为某一名称,如果在另一文件中,可以通过路径调用数据源。

如何在Excel中设置下拉选项实现下拉列表

在Excel中实现下拉列表 注:以下示例在Microsoft Excel 2007中通过测试 一、直接输入: 1.选择要设置的单元格,例如A1单元格; 2.选择菜单栏的“数据”→“数据有效性”→出现“数据有效性”弹出窗口; 3.在“设置”选项卡中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉箭头”全部打勾→在“来源”下面输入数据,如“部门1,部门2,部门3,部门4,部门5”(不包括双引号,分割符号“,”必须为半角模式)→点击“确定”完成,再次选择该A1单元格,就出现了下拉菜单。当然,可以拖动单元格扩展填充序列。 如图所示: 二、引用同一工作表内的数据:

如果同一工作表的某列区域就是下拉菜单想要的数据,例如引用工作表Sheet1的B1:B5。B1:B5分别有以下数据:部门1、部门2、部门3、部门4、部门5,操作如下: 1.选择要设置的单元格,例如A1单元格; 2.选择菜单栏的“数据”→“数据有效性”→出现“数据有效性”弹出窗口; 3.在“设置”选项卡中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉菜单”全部打勾→在“来源”下面输入数据 “=$B$1:$B$5”,也可以直接点击单元格选择B1:B5区域→按“确定”完成,再次选择该A1单元格,就出现了下拉菜单。 如图所示: 三、引用不同工作表内的数据(必须为引用的区域定义名称): 如果不同工作表的某列区域就是下拉菜单想要的数据,譬如工作表Sheet1的A1单元格要引用工作表Sheet2的A1:A5区域。工作表Sheet2的A1:A5分别有以下数据:部门1、部门2、部门3、部门4、部门5,操作如下: 1. 为引用单元格区域定义名称:

最新excel数据有效性实例培训讲学

什么是数据有效性? 数据有效性一个包含帮助你在工作表中输入资料提示信息的工具. 它有如下功能: --给用户提供一个选择列表 --限定输入内容的类型或大小 --自定义设置 Excel –数据有效性–自定义条件示例 防止输入重复值 防止在工作表一定范围输入重复值. 本例中, 在单元格B3:B10中输入的是员工编号. 1. 选择单元格B3:B10 2. 选择数据|有效性 3. 在“允许”下拉框中选择“自定义” 4. 在“公式”框中, 使用COUNTIF函数统计B3出现次数, 在$B$3:$B$10范围内. 结果必须是1或0: =COUNTIF($B$3:$B$10,B3)<=1 限定总数 防止一个范围数据总数超过指定值.本例中, 预算不能超过$3500.预算总额统计的单元格在C3:C7范围内 1. 选择单元格C3:C7 2. 选择数据|有效性 3. 在“允许”下拉框中选择“自定义” 4. 在“公式”框中, 使用SUM函数统计$C$3:$C$7合计值. 结果必须小于或等于$3500: =SUM($C$3:$C$7)<=350

没有前置或后置间隔 防止用户在输入文本前面或后面加入空白间隔. TRIM函数移除文本前后空白间隔. 1. 选择单元格B2 2. 选择数据|有效性 3. 在“允许”下拉框中选择“自定义” 4. 在“公式”框中, 输入: =B2=TRIM(B2) 防止输入周末日期 防止输入的日期为星期六或星期日. WEEKDAY将输入的日期返回到星期, 并且不允许其值为1 (星期日) 和7 (星期六). 1. 选择单元格B2 2. 选择数据|有效性 3. 在“允许”下拉框中选择“自定义” 在“公式”框中, 输入: =AND(WEEKDAY(B2)<>1,WEEKDAY(B2)<>7) 创建下拉列表选项 使用数据有效性可以为一个单元格创建一个选择输入内容的下拉列表. 列表数据项可以在工作表的行或列中输入, 也可以直接在数据有效性对话框中输入. 1. 创建列表数据项 a. 在一个半单行或单列中输入你想在下拉列表中看到的条目. 2.命名列表范围 如果你在一个工作表中输入了一个有效性列表条目,并且给它定义了名称,你就可以在同一工作簿的其它工作表的数据有效性对话框中引用这个名称. 1. 选择列表单元格范围. 2. 点击公式编辑栏左边的名称框(Name Box) 3. 定义一个名称,如:FruitList. 4. 按回车键.

在Excel工作表的数据列表中插入分类汇总

插入分类汇总 通过使用Excel“数据”选项卡的“分级显示”组中的“分类汇总”命令,可以自动计算列的列表 (列表:包含相关数据的一系列行,或使用“创建列表”命令作为数据表指定给函数的一系列行。>中的分类汇总和总计。 ·分类汇总分类汇总是通过 SUBTOTAL 函数利用汇总函数 (汇总函数:是一种计算类型,用于在数据透视表或合并计算表中合并源数据,或在列表或数据库中插入自动分类汇总。汇总函数的例子包括Sum、Count 和 Average。>(例如,“求和”或“平均值”>计算得到的。可以为每列显示多个汇总函数类型。 ·总计总计是从明细数据 (明细数据:在自动分类汇总和工作表分级显示中,由汇总数据汇总的分类汇总行或列。明细数据通常与汇总数据相邻,并位于其上方或左侧。>派生的,而不是从分类汇总中的值派生的。例如,如果使用“平均值”汇总函数,则总计行将显示列表中所有明细行的平均值,而不是分类汇总行中的值的平均值。 如果将工作簿设置为自动计算公式,则在您编辑明细数据时,“分类汇总”命令将自动重新计算分类汇总和总计值。“分类汇总”命令还会分级显示 (分级显示:工作表数据,其中明细数据行或列进行了分组,以便能够创建汇总报表。分级显示可汇总整个工作表或其中的一部分。>列表,以便您可以显示和隐藏每个分类汇总的明细行。 1、确保每个列在第一行中都有标签,并且每个列中都包含相似的事实数据,而且该区域没有空的行或列。 2、选择该区域中的某个单元格。 3、请执行下列操作之一: 插入一个分类汇总级别 可以为一组数据插入一个分类汇总级别,如下例所示。 运动列中的每个更改...

...销售额列的分类汇总。 1、对构成组的列排序。有关排序的详细信息,请参阅对区域或表中的数据排序。 2、在“数据”选项卡上的“分级显示”组中,单击“分类汇总”。 将显示“分类汇总”对话框。 3、在“分类字段”框中,单击要计算分类汇总的列。在上面的示例中,应当选择“运动”。 4、在“汇总方式”框中,单击要用来计算分类汇总的汇总函数。在上面的示例中,应当选择“求和”。 5、在“选定汇总项”框中,对于包含要计算分类汇总的值的每个列,选中其复选框。在上面的示例,应当选择“销售额”。 6、如果想按每个分类汇总自动分页,请选中“每组数据分页”复选框。 7、若要指定汇总行位于明细行的上面,请清除“汇总结果显示在数据下方”复选框。若要指定汇总行位于明细行的下面,请选中“汇总结果显示在数据下方”复选框。在上面的示例中,应当清除该复选框。 8、(可选>通过重复步骤一到步骤七,可以再次使用“分类汇总”命令,以便使用不同汇总函数添加更多分类汇总。若要避免覆盖现有分类汇总,请清除“替换当前分类汇总”复选框。 插入分类汇总的嵌套级别 可以在相应的外部组中为内部嵌套组插入分类汇总,如下例所示。 每次在外部组中更改时,地区列... ...对该地区的销售额进行分类汇总,在每次在内部组中更改时,运动列对销售额进行分类汇总。 1、对构成组的列排序。有关排序的详细信息,请参阅对数据排序。 2、插入外部分类汇总。 如何插入外部分类汇总 A 在“数据”选项卡上的“分级显示”组中,单击“分类汇总”。 将显示“分类汇总”对话框。 B 在“分类字段”框中,单击外部分类汇总的列。在上面的示例中,应当单击“地区”。 C 在“汇总方式”框中,单击要用来计算分类汇总的汇总函数。在上面的示例中,应当选择“求和”。

excel 中数据的有效性的应用

excel 中数据的有效性的应用 3、防止数据输入错误 典型应用如下: (1)防止日期错误: 只准输入日期或某个日期之后的特定日期:点击EXCEL菜单“数据-有效性-”,在“设置-允许”对话框中选择“日期”、并在相应位置输入起始日期。 (2)只准输入整数: 在“设置-允许”对话框中选择“整数”

(3)防止输入重复值: 首先要选定整行/列或单元格区域(比如选中B列),然后再点击EXCEL 菜单“数据-有效性-”,在“设置”对话框中选择“自定义”、在“公式”中输入“=COUNTIF(B:B,B1)<2”

检验一下:在B3单元格中输入“A”,看看会出现什么结果? (4)只能输入大于上一行的数值(或日期): 注意引用区域的最后一行行标为相对引用。A4的有效性公式为:=MAX($B$3:$B4) 4、条件输入 只准输入符合一定条件的数据: (1)只能输入大于左侧的数字

(2)按条件输入_根据左侧条件来决定右侧单元格如何输入: 下图中,如果单据类型选择了“入库单”,则只能在“入库数量”所在列即C列输入数据,而不能在“出库数量”所在列即D列输入数据;反之亦然。

C列公式为:=IF(B5="入库单",ISNUMBER(C5),FALSE) D列公式为:=IF(B5="出库单",ISNUMBER(D5),FALSE) [应用一]下拉菜单输入的实现 例1:直接自定义序列 有时候我们在各列各行中都输入同样的几个值,比如说,输入学生的等级时我们只输入四个值:优秀,良好,合格,不合格。我们希望Excel2000单元格能够象下拉框一样,让输入者在下拉菜单中选择就可以实现输入。 操作步骤:先选择要实现效果的行或列;再点击"数据\有效性",打开"数据有效性"对话框;选择"设置"选项卡,在"允许

相关主题