设为首页 | 加入收藏 | 网站地图 电脑入门知识_电脑知识学习_计算机基础知识教程_电脑技巧学习_电脑入门到精通
热门搜索:卫士 Linux 系统 怎么 如何 方法 win10 2010 网络赚钱 office2010

wps教程

当前位置:主页 > wps教程 > WPS数据有效性与条件求和的搭配

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

来源:互联网 作者: 发布时间:2016-05-18 13:39 浏览:

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

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

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

    =C*菜单!B+D*菜单!B+E*菜单!B+F*菜单!B

    以上操作方式有三个缺点

    手工录入所有菜单名

    手工查找菜名对应的单价

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

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

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

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

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

    激活“菜单”工作表;

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

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

    注这里A:A区域的引用需要侃用绝对引用

    第二步设置数据有效性

    激活“明细”工作表选择B:E区域;

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

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

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

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

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

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

    要F单元格录入以下数组公式

    =IF(MOD(ROW())"菜价"SUM(IF(OFFSET(C)=菜单!A$:A$C:F)*菜单!B$:B$))

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

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

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

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

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

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

    =IF(ISTEXT(C)"菜价"SUM(LOOKUP(OFFSET(C)菜单!A$:B$)*C:F))

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

(责任编辑:admini)
百度分享
office教程
看过这篇文章的人还看过
网友评论