WPS数据有效性与条件求和的搭配

 　　如图1和图2所示，“菜单”工作表中是常购菜名与单价，“明细”工作表是每日购买的菜名与数量，每日四种菜，菜名与数量各占一行，G列是需要计算的结果。

　　图1

　　图2

　　常规操方式是每日将种菜单名录入单元格，再设置公式将每个单元格(即每种菜)的数量乘以“菜单”工作表中对应的单价，然后汇总。公式如下：

　　=C2*菜单!B3+D2*菜单!B4+E2*菜单!B6+F2*菜单!B10

　　以上操作方式有三个缺点：

　　手工录入所有菜单名

　　手工查找菜名对应的单价

　　每行使用不同公式，即每天需要重新输入公式

　　是否有办法解决这些重复工作呢?即不用每天录入菜单，也不用每天输入公式即可完成所有需求。是的，利用数据有效性可以解决第一个问题，而数组公式可以解决另两个问题。

　　数据有效必性和数组公式应用得范围十分广泛，且使用方法灵活。数据有效性可以对某些具有固定输入项目的单元格通过下拉选择来简化输入，而数组公式往往可以将冗长的公式简化得精炼无比，且能完成很多普通公式无法完成的工作表，将它与定义名称和数据有效性等工具一起使用，更显其功能的强大。

　　下面开始数据有效性与数组公式结合，展示帐目制作之法。

　　第一步：定义名称及设置数据有效性

　　1. 激活“菜单”工作表;

　　2. 单击“插入”/“名称”/“定义”，打开“定义名称”对话框;

　　3. 在名称框中输入“菜单”，在“引用位置”框中输入“=菜单!$A$1:$A$10”，然后单击“添加”。

　　注：这里A1:A10区域的引用需要侃用绝对引用。

　　第二步：设置数据有效性

　　1. 激活“明细”工作表，选择B1:E1区域;

　　2. 单击菜单“数据”/“有效性”，打开“数据有效性”对话框;

　　3. 在“设置”选项卡“允许”列表中选择“序列”，“来源”文字框中处输入“=菜单”，最后单击“确定”按钮。

　　注：等号必须是半角状态下输入。

　　返回工作表中后，可以发现每个待录入数据的单元格已经产生下拉菜单，从中选择菜名即可

　　以后每天制作明细表时，只需复制第一行即可产生同样的下拉菜单。当然也可以第一天设计表格式时即将后面的区域一次性复制好，让所有奇数行都产生下拉列表供选择。

　　第三步：函数嵌套及数组公式

　　1.要F1单元格录入以下数组公式

　　=IF(MOD(ROW(),2),"菜价",SUM(IF(OFFSET(C1,-1,,,4)=菜单!A$1:A$10,C1:F1)*菜单!B$1:B$10))

　　注：这是一个数组公式，所以不能直接敲回车键，必须录入以式后同时按Shift+Ctrl+Enter结束。

　　2. 将光标移动至F1单元格右下角，当出现十字光标时向下拖动、填充即可完成多日数据一次运算。

　　注：从图3中可以看出，公式首尾自动产生了花扩号“{｝”，这正是数组公式的特点。

　　图3

　　公式解释：MOD函数是用来返回两数相除的余数，ROW函数用于返回当前行的行号。在本例中MOD配合ROW函数可用于判断公式所在行的奇偶性。对奇数行，公式返回结果“菜单”，而偶数行则返回当日的购菜总价。

　　IF的第三参数用于计算每日的菜单，它首先利用OFFSET函数引用本日的菜名，然后与“菜单”工作表中的菜名进行比较，再将名称同相的单价引用过来，并与数量相乘，通过SUM函数合计。

　　3.本例公式利用数组解决奇数行为“菜价”，偶数行计算菜价的问题，且实现了自动查找对应单价。但是利用Lookup函数还可以使用公式更简化。公式如下：

　　=IF(ISTEXT(C1),"菜价",SUM(LOOKUP(OFFSET(C1,-1,,,4),菜单!A$1:B$10)*C1:F1))

　　注：基于Lookup的特性，需要对“菜单”工作表的数据以A列为基准升序排列。