EXCEL的单元格数据有效性序列设置
吉林凤凰妇产医院
本文讲述重点：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,,)
这一段代表定位源数据的结尾单元格，
达到的效果：当点击已设置数据有效性序列的单元格，会出一个下拉列表，供使用者选择。
调出数据有效性设置的步骤。
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,,)
这一段代表定位源数据的结尾单元格，
- 上一篇：word图片如何自动编号
- 下一篇：Word页眉怎么设置每页都有
