【DEMO案例】关联电子表格并计算工资

目标

根据数据库中的每日销售记录,找到每个月的销售额均在前5名的优秀销售员。

原始数据存在数据库中:记录着一年中每个销售员每天的销售额

esCalc to find monthly top salesmen-1

计算出各个月份前5名的销售员,再求它们的交集,就是所求目标

esCalc to find monthly top salesmen-2

步骤

1、分组

先通过向导从数据库导入数据

esCalc to find monthly top salesmen-3

要计算每个月每个销售员的销售额,需要将数据按照月份和销售员分为2层,插入一列并根据日期计算出月份

esCalc to find monthly top salesmen-4

结果如下:

esCalc to find monthly top salesmen-5

可以看到,只需输入一次,公式就会自动复制到其他单元格。

再按照月份分组

esCalc to find monthly top salesmen-6

结果如下:

esCalc to find monthly top salesmen-7
刚才采用了”Quick operation”,默认是先排序再分组的。普通操作可以有更多选项。

继续对salesman分组,在D列的明细数据区选中任意一格,执行跟刚才同样的操作。
esCalc to find monthly top salesmen-8

为了便于观察不同的数据层次,可将第2行和第3行标识为不同的颜色

esCalc to find monthly top salesmen-9

收缩到不同的层级可以更直观地观察数据

esCalc to find monthly top salesmen-10

收缩到一层的效果如下:

esCalc to find monthly top salesmen-11

可以看到,单元格的风格可以在汇总区之间自动复制。在计算表中,自动复制无处不在。进行下一步前,让我们删除无用的A和B列,并展开数据。

esCalc to find monthly top salesmen-12

2、销售额

这一步的目标是计算每个月里每个销售员的销售额。 比如1月份Baker的销售额可以这样算:”将和C4同一月份同一销售员的那些单元格(简称同位格)相加”

esCalc to find monthly top salesmen-13

计算表是这样计算的:首先输入公式

esCalc to find monthly top salesmen-14

结果如下:

esCalc to find monthly top salesmen-15

公式的含义为:

esCalc to find monthly top salesmen-16

按照业务上的描述直观的写出公式,这是计算表的特点。 用传统电子表格写类似的公式则需要给出起始单元格。

将数据收缩

esCalc to find monthly top salesmen-17

可以看到公式被自动复制到了C35,C67等格。它们和C3的业务地位相同,也属于同位格关系。 非同位格则不会复制,比如C2,C325。
传统电子表格缺乏这种智能的公式复制,相同的计算全靠手工复制。

3、排序

这一步要将销售员按照销售额排序.
传统电子表格缺乏同位格模型,无法区分汇总区和明细区,因此无法正常排序。 计算表可以轻松实现.

esCalc to find monthly top salesmen-18

结果如下:

esCalc to find monthly top salesmen-19

可以看到,排序的对象只是C35的同位格,不会影响明细数据等非同位格。 另外,2-12月份的数据也自动排序了,它们都是C35的同位格。 传统电子表格则需要大量手工操作。

4、过滤

这一步要过滤出每月前5名的销售员。看似简单,但传统电子表格没有组内序号的概念,过滤起来并不轻松。 计算表可以轻松实现。 首先输入过滤公式:

esCalc to find monthly top salesmen-20

注意:#表示当前组成员的序号

过滤结果如下:

esCalc to find monthly top salesmen-21

所有月份都统计出来了!组内序号是同位格的属性,传统电子表格缺乏同位格模型,因此难以进行同样的运算。

5、求交集

这一步计算每个月均出现的销售人员,即交集。 先将销售人员集中在上层汇总区:

esCalc to find monthly top salesmen-22

“{B3}”表示B3的同位格,结果如下:

esCalc to find monthly top salesmen-23

再计算它们的交集,首先输入公式:

esCalc to find monthly top salesmen-24

注意:isect()用来求交集

结果如下:

esCalc to find monthly top salesmen-25

最终,我们找到了优秀的销售人员!计算表实现起来很轻松!同样的功能,传统的电子表格由于不支持集合运算,需要大量的操作和复杂的公式才能完成!