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

2017-07-12 08:46:10 来源:三联 人气: 次阅读 660 条评论

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

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

<a href=/office/wps/ target=_blank class=infotextkey>wps</a>数据有效性与条件求和的搭配  三联

  图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列为基准升序排列。

  • WPS帮助老师轻松查询学生各科成绩

    WPS帮助老师轻松查询学生各科成绩

      上学的时候,经常到了期末各位同学的家长跑到学校来找老师咨询自己孩子的期末考试情况,老师们也为了应付做了本厚厚的成绩本,一页一页的翻查着,其实利用WPS表格可以相当轻松的解决相关查询问题,免去一天到晚翻本子的功...

    WPS教程 2017-07-12
  • 用WPS格式转换工具校验身份证号码

    用WPS格式转换工具校验身份证号码

      在录入身份证号码的时候,一不小心就可能出错。下面我们就讲讲如何利用ET的格式转换功能,校验身份证号码中的出生日期部分。  图1  如图1所示,A列为身份证号码(输入前请先将该列单元格格式统一设置成“文本...

    WPS教程 2017-07-12
  • WPS技巧:TRIMMEAN函数计算选手得分

    WPS技巧:TRIMMEAN函数计算选手得分

      如图1就是某大奖赛的选手评分情况表。  在“最后得分”一项中,我们可以用LARGE函数或SMALL函数来计算,如在J3中输入下面的公式:  =AVERAGE(LARGE(B3:I3,{2,3,4,5,6,7}))  即可以得到正确的结果。 ...

    WPS教程 2017-07-12
  • WPS中实现文档特定字符的字体替换

    WPS中实现文档特定字符的字体替换

      随Vista系统一起推出的“微软雅黑”字体,以其对液晶显示器的良好支持,很快获得了很多用户的青睐。就连一些Windows Xp用户也在系统中安装了“微软雅黑”,但是,由于微软对中文的了解不够深入,导...

    WPS教程 2017-07-12
  • 3种方法找到WPS网络模板的本地位置

    3种方法找到WPS网络模板的本地位置

      方法一:点击网络模板,切换到本地,可以看到模板已经完整地被保存在本地了。  方法二:另一种找到本机模板的方式是:点击文件下的本机模板,在download文件夹下可以找到。  方法三:下载后的模板存在于你的系统目录下,如...

    WPS教程 2017-07-06
  • WPS怎么删除空白页?

    WPS怎么删除空白页?

      WPS怎么删除空白页?在编辑WPS文档时,有的时候会出现空白页吗,怎么也删不掉,很是烦人。今天,小编收集整理了去除WPS空白页的方法,大家可以来学习一下!  WPS删除空白页方法一、  直接将鼠标放在空白页上点“退...

    WPS教程 2017-07-06