怎么让数据有效性根据索引改变

我用EXCEL做了个商品出入库记录，但因为每个人的习惯或其他原因，录入的数据总会有各种问题，每次汇总时最头痛的就是商品名称，比如康师傅红烧牛肉面，有的人名称填写的就是“康师傅-红烧牛肉面”等。为了规范数据填写，我设置了数据有效性，每次下拉箭头点开，下面一长串的名称，选起来特别麻烦，让记录的人叫苦不迭。几经改善，我想到了通过给出条件让数据有效性跟着改变的方法，让选择的范围变小了，使用起来方便多了，下面就是这次改良的过程，为了方便，里面的数据进行了删减。

工具/原料

安装有OFFICE软件的PC一台
输入设备（鼠标、键盘等）

步骤一：规划表格、填充数据

1

首先，我们在表格中创建三个SHEET,分别是：商品清单、数据有效性和出入库记录。商品清单是把我们的商品归类，以便做出索引。数据有效性这个SHEET是一个辅助的表格。出入库记录这个表格就是我们每天要用到的表格了。
2

首先在商品清单中编辑我们的基础数据，然后在品类和商品名称中间插入一列“索引”，在B2单元格中输入函数：=A2&COUNTIF($A$2:A2,A2)，向下填充，填充是尽量超过我们已有的数据行数，这样我们以后商品名称有增加时，直接在表格里填写品类和名称就可以了。完成后隐藏B列（隐藏是为了防止别人误操作改变了公式。）
3

在出入库记录编辑表头（如下图）。

END

步骤二：制作索引

1

第一步先制作品类的有效性，在数据有效性这个表格中，我们把商品分多少类，就把品类信息编辑进去。回到商品清单中，选中A列除A1的所有单元格（选中A列，按住CTRL键，单击A1），单击数据有效性选项卡，如图（2）进行设置。结果如图（3）
2

在数据有效性表格的C1和D1单元格内分别输入：类别、商品名称。在C2单元格中输入函数：=INDIRECT("出入库记录!B"&COUNTA(出入库记录!B:B))。在D2 单元格中输入函数：=IFERROR(VLOOKUP($C$2&ROW()-1,商品清单!B:C,2,0),"")，将D2填充至D15.
3

将出入库记录的B列品类同前面商品清单里的品类一样设置有效性。C列商品名称如图设置，D列的有效性设置时，来源为：出库，入库。（注意：出库和入库中间一定要用英文状态下输入的逗号隔开）
4

做到这里，表格就算完成了，下面我们来试验一下吧，当我们的品类选择茶饮料时，商品名称就只有茶类的几种，品类选择碳酸饮料的时候，商品名称就只有碳酸饮料的几种了，使用起来是不是方便多了。

END