EXCEL的单元格数据有效性序列设置

腾讯视频/爱奇艺/优酷/外卖 充值4折起

本文讲述重点:EXCEL单元格数据有效性序列的来源

达到的效果:当点击已设置数据有效性序列的单元格,会出一个下拉列表,供使用者选择。

调出数据有效性设置的步骤。

1、 首先选中你要设置数据有效性的目标是一列 OR 一行 OR 某个单元格 OR 某一个单元格区域。

2、 菜单栏--- 数据--- 有效性,打开“设置”界面,在 “允许” 中选择 “序列”,那么接下来,本文的重点,就是在 “来源”框 中如何设置了。

一、常量型的来源设置

简单点讲,就是设好之后,列表不会随意变更,适用于不会经常变来变去的列表。比如:销售部,客服部,财务部,人事部等,公司不会随意变更这几个部门。

这种设置的好处,列表信息不占工作表资源,信息存储在应用程序里面。

设置方法:将列表名单输入“来源”下的框中。

特别注意:不同的名单中间以“,”号隔开,这个符号是在英文输入法状态下输入的,不要搞混了。(我的做法是,先用五笔把文字打出来,然后再切换到英文状态,输入“,”号)

设置完成后,点击“确定”,然后返回EXCEL表中,查看效果

二、变量型来源的设置,也称为引用型的来源设置。

这种设置就是所你可以自己指定一个单元格区域,来做为列表的来源,它可以是本工作表的,也可以是本工作簿其它工作表的。当然,这个区域内,你想以什么文字做为列表都可以,允许内容随时更改的。当然了,更改后数据有效性的序列也随之更新了。

1、普通引用型的“来源”设置

(1,在本表中直接指定。

比如设定A1:A4为来源,方法如下:直接在“来源”框中点一下鼠标激活,然后鼠标再点住A1单元格不放,直接向下拖动到A4单元格。默认状态下是绝对引用,如果是手工在“来源”框中输入,请记得按F4 键,或Shift+ 4 键,以便输入 “$” 符号

本方法适用于在当前Sheet表里做。如果你的源数据列表来源于本工作簿其它Sheet表,那么在指定“来源”时将不能指定,需要名称公式。

(2,在本工作簿其它表中指定。

比如,数据列表在Sheet 1工作表的 A1:A4单元格,现在希望为Sheet 2 工作表的B5单元格设置数据有效性序列,序列的来源于Sheet 1工作表的 A1:A4 区域。

方法如下:

第1个步骤。

将工作窗口切换至Sheet 2 工作表,打开 菜单栏—“插入”—“名称”,选择“定义”,打开定义名称设置窗口。

在引用位置中,将当前的填写内容删掉,然后点 Sheet 1工作表标签,用鼠标选中A1:A4区域。默认状态下,你的最后结果应该是 =Sheet1!$A$1:$A$4 注意检查一下噢,呵呵…… 然后在“在当前工作簿中的名称”下面的框框中,输入你为这个公式定义的名称,比如取个名字叫“部门列表”,然后依次点右侧的按钮“添加”、“确定”。

如下图所示

第2个步骤。

为Sheet 2 工作表的 B5 单元格设置 数据有效性序列。 菜单打开的先后顺序还记得吗? 菜单栏---“数据”-----“有效性”,在 设置 界面,将 “允许”设置为“序列”。

那么接下来“来源”怎么写呢? 请输入引号里面的“=部门列表”

第3步,查验一下效果,呵呵,成了,那么,如果你Sheet 1工作表中的“人事部”改为“行政部”呢? 结果怎么样? 呵呵,是不是很好玩。

那如果你在A1和A4单元格再插入一行,输入“总经办”,现在再返回Sheet 2工作表,查看B 5单元格,告诉我你发现了什么? 是不是类似于下图。呵呵…..

上述方法,适用于一时半会儿不能确定列表内容的情况。如果有增加,允许在第1个单元格和最后1个单格之间插入新的单元格数据。

注意:我们的示例中最后一个单元格是A4,如果现在要在A5单元格填入新数据,那么数据有效性序列将不能显示这一条。因为我们的名称公式,只定义了A1:A4。

如果你不能确定未来的最后一个单元格是多少,那么,你将需要下面这种较复杂的方法。

在上一篇中,使用常量的数据有效性序列设置,类似于打固定靶;使用普通引用型的设置方法,类似于打单方向的移动靶。接下来,我们讲解打双方向的移动靶,你不能确定数据源的首尾单元格的位置时,应该怎么设置。

内容如下:

2,查找偏移引用型的“来源”设置

前提设定:列表数据来自Sheet 1 工作表A列,列表个数不确定;列表首尾前后是否要添加数据不确定。 现在又在Sheet 2 工作表的B 5单元格 设置数据有效性序列。序列来源于Sheet列

方法:因为数据使用的是另一个Sheet表,因此,还像之前那样,我们首选在Sheet 2工作窗口,设置名称公式。设置名称公式的步骤你还会吗? 不会的话,一起来做吧。

<1、定义名称

点开 Sheet 2 工作表窗口,菜单栏---“插入”---“名称”—“定义”

将引用位置框填入

=INDEX(Sheet1!$A:$A,1,1):OFFSET(INDEX(Sheet1!$A:$A,1,1),COUNTA(Sheet1!$A:$A)-1,0,,)

定义名称为"我的列表", 然后依次点 “添加”,“确定”。

<2、设置有效性

依然在Sheet 2 工作表窗口,点一下 B 5单元格,然后再从 菜单栏—“数据”—“有效性” 设置 允许 值为序列,在来源框中输入 =我的列表 确定。 OK了,呵呵。

那么现在试一下成果,你在Sheet 1的A列第1行新加入一行,然后填上数据后,在Sheet 2 的B5单元格,看一下效果,或者在Sheet 列紧接着最后一行,再填写一个数据看看。呵呵,是不是灵活性很大了。

<3、公式解析

=INDEX(Sheet1!$A:$A,1,1)

这一段代表定位源数据的开头始终为第1个单元格,

INDEX函数,指定返回到Sheet 1 工作表的A列第1行与第1列的交叉单元格。

OFFSET(INDEX(Sheet1!$A:$A,1,1),COUNTA(Sheet1!$A:$A)-1,0,,)

这一段代表定位源数据的结尾单元格,

OFFSET函数是一个偏移函数,在本公式中代表,以(INDEX(Sheet1!$A:$A,1,1)为参照点,向下偏移COUNTA(Sheet1!$A:$A)-1行,向右偏移0列。

COUNTA函数统计在Sheet1!$A中数据的行数。减1是因为这一次统计是统计一共有多少行,而OFFSET函数偏移时是不计算参照物那一行的,因此需要减去1行。

<4、相关说明

上述公式统计的区域是A列,因为这一列不能用做其它用途,如果不需要统计1列,那么可以将COUNTA函数中指定为类似于

COUNTA(INDEX(Sheet1!$A:$A,1,1): Sheet1!$A30)

不能对源数据表的有数据行进行删除操作,否则会引起错误,如果需要更改,可以使用复制粘贴的形式,使上1行与下1行保持不空行。

<5、关于EXCEL 单元格数据有效性序列设置其它运用

限于篇幅和难度,本次仅简单介绍

运用一:二级引用运用。

举例,书写工具 可以分为钢笔、铅笔、水性笔,而钢笔又有 英雄钢笔、派克钢笔、万宝龙钢笔等。 只要将源数据的分类列好。可以使用有效性序列,根据大类的名称,自己显示出小类的列表。

设计思路:1、使用INDEX找到大类别名称的位置;

2、使用OFFSET以大类别名称为参照点,进行双向移动靶的首尾确定

运用二:针对于经常变更的数字设置有效性序列。

比如A5单元格是当前销售价格,在A17设定数据有效性序列为 =A5, 那么接来的输入就是这个价格了,但如果下个月销售价格有变动, A5的值变更了,那么下个月A17的值就是新的值。但是上个月输入的值不会受什么影响。

excel合并单元格在哪设置?exl合并单元格快捷键

05/01 18:17
使用office办公软件的朋友们很多不知道excel合并单元格在哪设置,其实不难非常简单,有什么excel合并单元格快捷技巧呢?小编就为大家带来exl合并单元格快捷键。 excel合并单元格在哪设置?exl合并单元格快捷键 如图我们可以看到长数据内容写不下一个单元格。 这里我们需要选中所有需要合并的单元格,如图所示合并按钮(根据自己意愿选择) 然后多个单元格就合并成功了。 excel合并单元格在哪设置?exl合并单元格快捷键就为大家介绍到这里,更多软件教程欢迎关注第九软件网。

excel合并单元格在哪设置?

09/03 09:17
excel合并单元格在哪设置? 如图我们可以看到长数据内容写不下一个单元格. 这里我们需要选中所有需要合并的单元格,如图所示合并按钮(根据自己意愿选择) 然后多个单元格就合并成功了.

excel怎么调整单元格大小 excel自定义单元格尺寸的设置方法

12/06 12:16
​excel是我们常用的办公软件,为了美观,有时候我们调整不同单元格的大小,那么excel怎么调整单元格大小?下面小编带来excel自定义单元格尺寸的设置方法,希望对大家有所帮助. excel自定义单元格尺寸的设置方法: 下图即是excel软件的界面,图中黑色的小框就是excel的单元格.我们可以在里面输入数据信息.​我们所要改变的就是它的大小. ​用鼠标左键点击软件左侧单元格的某个序号,如图所示,点击之后,整行的单元格都会处于被选定状态.​ ​然后,按住鼠标左键,不要松开,向下拖动,随便选择几

禁止修改excel指定单元格数据数据的设置方法

07/05 21:26
对于某个Excel工作簿中的数据,如果我们仅仅是希望别人查看而不希望其随意地修改的话,为该工作簿添加一个密码无疑是个很简单实用的方法.在实际应用中,我们在对Excel工作簿中某些指定单元格中的数据加以保护的同时,却还得允许别人可以修改其他单元格中的数据.那么,这又该如何操作呢?接下来,笔者就以某Excel工作簿为例(只保护C列和D列单元格中的数据),给大家介绍一下如何来实现. 第一步:打开工作簿并切换到"Sheet1"工作表下;接着选中该工作表中的所有单元格并按下"Ctrl+

电脑Excel上单元格的日期输入范围怎么进行设置

07/05 04:55
有的小伙伴在使用Excel处理数据时,需要设置单元格的日期输入范围,但是却不知道该如何进行操作,那么小编就来为大家介绍一下吧.具体如下:1. 第一步,双击并打开Excel软件,接着选中A1列.2. 第二步,点击工具栏中的数据选项.3. 第三步,点击下图箭头所指的数据有效性选项.4. 第四步,我们可以看到如下图所示的弹出窗口,点击任何值右边的下拉箭头.5. 第五步,在下拉选项中,点击选择日期选项.6. 第六步,设置开始如期和结束日期,接着点击窗口底部的确定选项.7. 第七步,在选中的A1列中的任意

Excel锁定单元格设置

03/15 03:25
在制作大量的Excel表格的时候,我们常常自己都可能搞忘记哪些数据是非常重要的,可能一个误操作就会导致一些自己辛苦的数据丢失了。所以, 每当我们做完一些重要的数据的时候不仅要保存备份,而且最好是将一些“单元格锁定”起来,这样我们就不会出现误操作或误删除而导致删除重要数据。下面来给 大家说一下如何灵活运用“Excel锁定单元格”以及锁定某列和某行的技巧! 这里我所说的“锁定单元格”含义是指将某块区域的一些单元格保护起来,保护后是无法进行编辑与修改的,只有通过输入先前设置的密码正确后才可以重

excel怎样快速把多个单元格内容合并 合并多个单元格内容的设置方法

12/08 09:08
excel是我们常用的办公软件,有时我们需要把多个单元个的内容合并到一个单元格里,那么excel怎样快速把多个单元格内容合并?下面小编带来合并多个单元格内容的设置方法,希望对大家有所帮助. 合并多个单元格内容的设置方法: 1.如图表格,想把ABCD列的内容合并到一个单元格里,如果数据不多,可利用连字符,公式==A2&B2&C2&D2. 2.如果数据很多,用连字符不方便,可在菜单栏点击编辑--office剪贴板. 3.剪贴板会出现在工作表的最左边,然后复制需要合并的内容. 4.这样复

电脑excel单元格背景色怎么设置为应用图案和填充效果

04/15 14:15
excel是我们经常使用的数据处理工具之一,我们经常会将数据在excel中处理成表格,为了增加我们表格的美观,我们也可以给单元格背景色进行应用图案或者填充效果,接下来小编就教大家怎样使用.一 单元格背景色应用图案1. 首先我们打开电脑进入到桌面,找到想要设置的excel图表点击打开 .进入到软件界面之后,我们首先使用鼠标选中想要设置的单元格.2. 选中完成之后,我们点击上方状态栏中的开始按钮.3. 接下来我们就可以在下方的功能栏字体选项右侧找到小图标点击打开.4. 接下来在我们这面中就会打开设置

怎么设置和限制Excel表格单元格的输入字数

04/24 21:24
今天,小编给大家介绍设置和限制Excel表格单元格的输入字数的方法,希望对大家有所帮助.具体如下:1. 首先,请大家在自己的电脑中找到[Excel]图标,点击进入主界面,然后将自己的目标表格打开,接着将想限制输入字数的单元格选中.2. 第二步,接下来,请大家选择屏幕最上面的[数据]按钮,然后选择[数据工具]选项.3. 第三步,此时我们需要选择屏幕中的[数据验证]这一项.4. 第四步,如图所示,我们点击最上面的[设置],接着点击[验证条件]一栏[允许]中的[文本长度]即可.5. 第五步,如图所示,