《2022年计算机一级WPS辅导：WPS数据有效性与条件求和的搭配.docx》由会员分享，可在线阅读，更多相关《2022年计算机一级WPS辅导：WPS数据有效性与条件求和的搭配.docx（4页珍藏版）》请在金锄头文库上搜索。
1、 2022年计算机一级WPS辅导：WPS数据有效性与条件求和的搭配我厂食堂中每日将购进4种菜，且规定连续的两天中尽量不能有菜名重复，使员工能吃上新奇菜。但这却使不太懂表格软件的帐房先生制作明细表时犯难了。如图1和图2所示，“菜单”工作表中是常购菜名与单价，“明细”工作表是每日购置的菜名与数量，每日四种菜，菜名与数量各占一行，G列是需要计算的结果。 常规操方式是每日将种菜单名录入单元格，再设置公式将每个单元格（即每种菜）的数量乘以“菜单”工作表中对应的单价，然后汇总。公式如下： =C2*菜单!B3+D2*菜单!B4+E2*菜单!B6+F2*菜单!B10以上操作方式有三个缺点：手工录入全部菜单名手
2、工查找菜名对应的单价每行使用不同公式，即每天需要重新输入公式是否有方法解决这些重复工作呢？即不用每天录入菜单，也不用每天输入公式即可完成全部需求。是的，利用数据有效性可以解决第一个问题，而数组公式可以解决另两个问题。数据有效必性和数组公式应用得范围非常广泛，且使用方法敏捷。数据有效性可以对某些具有固定输入工程的单元格通过下拉选择来简化输入，而数组公式往往可以将冗长的公式简化得精炼无比，且能完成许多一般公式无法完成的工作表，将它与定义名称和数据有效性等工具一起使用，更显其功能的强大。下面开头数据有效性与数组公式结合，展现帐目制作之法。第一步：定义名称及设置数据有效性1. 激活“菜单”工作表；2.
3、 单击“插入”“名称”“定义”，翻开“定义名称”对话框；3. 在名称框中输入“菜单”，在“引用位置”框中输入“=菜单!$A$1:$A$10”，然后单击“添加”。注：这里A1:A10区域的引用需要侃用肯定引用。其次步：设置数据有效性1. 激活“明细”工作表，选择B1:E1区域；2. 单击菜单“数据”“有效性”，翻开“数据有效性”对话框；3. 在“设置”选项卡“允许”列表中选择“序列”，“来源”文字框中处输入“=菜单”，最终单击“确定”按钮。注：等号必需是半角状态下输入。返回工作表中后，可以发觉每个待录入数据的单元格已经产生下拉菜单，从中选择菜名即可以后每天制作明细表时，只需复制第一行即可产生同样
4、的下拉菜单。固然也可以第一天设计表格式时马上后面的区域一次性复制好，让全部奇数行都产生下拉列表供选择。第三步：函数嵌套及数组公式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中可以看出，公式首尾自动产生了花扩号“”，这正是数组公式的特点。 公式解释：MOD函
5、数是用来返回两数相除的余数，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列为基准升序排列。
