情侣分手甩出十年账单?Excel帮你花样清算!!

本文作者:小魔进

首发于一周进步

最近看到一条有意思的新闻:

某男子与女友十年恋爱长跑分手后,晒出十年间为女友付款的网购账单,称这十年都是自己赚钱养家,加上电商狂欢节一共为其花掉十万块,现欲让女友还钱。

有网友说如果十年都是男生付出就应该要钱,也有说各自都付出过,缘分尽了就好聚好散。

那么,如果你是女主你会怎么办呢?

如果是我,我一定会选择......

求和!

什么?为了不用还钱你居然选择低声下气地去求和?也太没有原则了!没有骨气!

停停停,此“求和”非彼“求和”,不求和你怎么知道一共要还多少钱给他!

那么,该怎样求和呢?用计算器?十年的账单,不说能按坏一台计算器吧,万一按错了还得从头算。

还钱可以,但我有一个要求,你得把这十年的账单一笔一笔给我输入到 Excel 表格里!

我们最原始的求和方法是按「+」号求和,好几百条数据一个一个按「+」恐怕要按到天荒地老,这和按计算器有什么分别?

哼,休想难到我,没点真本事怎么出来混!我可是会用 SUM 函数的人!



唔,怎么选择求和区域需要一直拖拽这么久,这要是有个几千几万条数据手指不得按废了(软妹纸的手指就是这么脆弱),有没有简单一点的方法呢?

有!有一个超级无敌巨简单最快速的求和方法!

请!注!意!操作时一定要集中注意力,不要眨眼!否则结果就自己蹦出来了。

选中账单区域任意单元格,进行以下操作

按下「 Ctrl + ↓」-「Enter」-「 Alt + =」,见证奇迹的时刻!



这是一段什么神奇密码?

其实都是 Excel 快捷键的功劳:

「 Ctrl + ↓」(快速定位表格最末行)-「Enter」(定位到求和单元格)-「Alt + =」(快速求和)。

或者在工具栏中选择「公式」选项卡-点击「自动求和」-选择「求和」,一样可以快速求和哟!


你看虽然这十年账单的数据他可能得输入几天,但我眨眼间就可以搞定求和,没有对比就没有伤害呀。

等等,先别高兴得太早,万一他找了几个狐朋狗友帮他一起输呢?

然后把每年的账单单独放在一个工作表里,还!没!有!合!计!(这么缺德怎么才分手)

难道我要按十遍「 Ctrl + ↓」,十遍「Enter」再按十遍「 Alt + =」吗?

不不不,这么令人发指的账单超过十秒我都没眼看,看本魔进如何见招拆招!

第一招——同步求和

在工作表名称区域选择「2018年」工作表,按住「Shift」键,鼠标向前移动直到第一个工作表,即「2009年」工作表,即可同步操作选中的多个工作表。(先选 2009 年后选 2018 年也是一样啦,都这个时候了就不要纠结先后顺序啦。)

翻看每年的账单,我发现 2018 年账单的行数最多,所以为了避免同步操作时影响其他工作表的原数据,我在 2018 年账单表格的结尾处添加合计行,在合计金额处即「B61」单元格,使用我们的超级无敌巨简单最快速求和法,按下快捷键「 Alt + =」,眨眼间轻松搞定 10 个工作表的求和问题!



检查一下,每个工作表的「B61」单元格都求出了对应账单的合计金额,简直美滋滋。

事实告诉我们多学一样本领就少说一件求人的事,一个人的智慧可以战胜他十个人的劳动力,失恋再难忍终敌不过一个最好的自己!


先别急着自我陶醉,这十年账单的总金额还没求出来呢?难道要把这些工作表一个一个点开,再一个一个选中合计区域去按「+」汇总?

那可就超过十秒了诶,不行不行,没眼看。

如果你今天选择忍受了这 10 个工作表,觉得手动去加问题不大,那当你遇到 100 个 1000 个工作表的求和问题呢?

生活中有很多我们不以为然的小问题,可一旦量化,你就会发现,原来悲伤那么大,为了把悲伤扼杀在摇篮里,我选择用智慧去破解它。

第二招——跨多表求和

在「B62」单元格填入公式「=SUM('*'!B61)」它会自动变成公式「=SUM(‘2009年:2017年’!B61)」即可自动求出除当前工作表外其余所有工作表中「B61」单元格的数据之和,即 2009 年到 2017 年账单的合计金额,我把公式中的 2017 改成 2018 就可以快速求出十年账单的总金额啦。

这样就算前面有 100 个 1000 个工作表也一样可以快速得到求和结果。

注意「*」号两边是英文状态下单引号。



「'*'!」是什么神仙符号,怎么会自动变身呢?

「*」是Excel中的通配符,它可以代表多个字符,如 2009 年、2010 年等等,那它怎么就不能代表 2018 年呢,还要我手动去修改?

这只能怪当初设计这个函数的程序猿了,我也不知道他是怎么想的,总之「'*'!」只能代表本工作簿中除本工作表之外的所有表(哭唧唧)。

但我们就只要稍稍改动一个数据就可以圆满完成我们的求和任务,省下来的时间我们就用来讨伐这个程序猿吧,哈哈哈。

十年账单总金额十万,平均每年应该是一万,可 2018 年双十一之前就花了快两万,都买啥了呢,我得看看每个月都花了多少。

第三招——筛选求和

选中表头「A3:B3」区域-选择「数据」选项卡-点击「筛选」,点击「日期」右下角的小箭头,即可对日期列进行筛选,选择 1 月,本来以为又到了见证奇迹的时刻。

刚想炫耀一番,没想到合计金额居然没!有!变!化!还是 1 到 12 月的总金额。



怎样设置能够只计算当前可见的数据,筛选掉的、隐藏掉的都选择性遗忘?有没有一种求和方法只相信眼前看到的,不去戳破那些隐藏的“故事”呢?

当然有啦,此时你需要的是「SUBTOTAL」函数!

将「B61」单元格中的SUM函数改为「SUBTOTAL(9,B4:B60)」就能够计算在筛选状态下的求和啦。

注:「SUBTOTAL」第一参数用于指定汇总方式,可以是 1~11 的数值,通过指定不同的第一参数,可以实现平均值、求和、最大、最小、计数等多种计算方式。

如图:



如果第一参数使用 101~111,还可以忽略手工隐藏行的数据,小伙伴们有空可以试试。


诶,2 月份明明冷战了半个多月什么时候花了他那么多钱?可得检查一下,不能盲目信任,爱情里的人都是盲目的,现在我好不容易跳出来了可得清醒一下,看看他有没有蒙骗我。

把“不明数据”标黄,让他睁大眼睛好好想想,这钱到底是给哪个小妖怪花的。

那怎样快速求出标黄区域的合计呢?

这就是传说中按颜色求和的问题啦,据说有 90% 的 Excel 人都不会按颜色求和,而我,会用三种方法。(没错,就是在炫耀。)

第四招第一式——筛选法

顺着上面筛选求和的思路,既然可以按月筛选求出每月的合计,那按颜色筛选不就可以得出不同颜色区域的合计了嘛,为了筛选后保留合计区域,我把「合计」行也同样用黄色填充,点击「日期」右下角的小箭头,选择「按颜色筛选」-「黄色填充」,结果就出来啦。



如果我前面没有用到「SUBTOTAL」函数,也没有设置数据筛选,还有一个更简单的求和方法。

第四招第二式——名称框法

选中标黄区域,在左上角名称框处填入「黄色区域」,在「B63」单元格输入公式「=SUM(黄色区域)」,当当当当,啷个里个郎,黄色区域的合计金额跃然纸上,你就说快不快。



但是如果我有很多个不连续的标黄区域,一个一个去选中再输入名称框可就有点麻烦了,没关系,我还有终极大招。

第四招第三式——自定义函数法

这个可有点高级了,要用到传说中的「自定义函数」啦,赶紧拿出小本本,又多了一项可以炫耀的技能。

选择「公式」-「定义名称」-输入名称「color」(随便叫什么名字都行,我选了个洋气的)-在引用位置中输入公式「=GET.CELL(63,B4)」-单击「确定」即可启用宏表函数。

参数「63」表示返回单元格填充颜色的编码数字。



在「C4」单元格输入自定义函数「=color」即可返回「B4」单元格填充颜色的编码数字,将公式向下填充可以得到所有 B 列数据填充颜色的编码数字,如图:无填充 = 0,黄色填充 = 6。



要求出黄色填充区域的金额,也就是颜色编号为 6 的单元格对应「B列」的数据之和。这就要用到了「SUMIF」条件求和函数。

「SUMIF」函数的语法是:SUMIF(range,criteria,sum_range)即(条件区域,求和条件,实际求和区域)。

条件区域就是所有数据所在单元格的颜色编码区「C4:C60」,因为要求出黄色区域的合计,也就是颜色编码为「6」的区域对应「B列」的数值之和。

所以我在「A63」单元格输入求和条件值「6」,在「B63」单元格输入公式「=SUMIF(C4:C60,A63,B4:B60)」。

求和结果就出来啦。


你看,这些看似让人头疼的问题,有的人确实需要花费很长的时间去解决,甚至根本找不到解决办法,而有的人却能见招拆招,你有 100 种难题我就有 101 种解决办法。

其实生活也是一样,没有那么多解决不了的问题和过不去的坎儿,就看面对问题的你有多少能力去破解它。


明明在讲Excel怎么上升到人生哲学了?你以为我是在给你灌鸡汤?

不,其实,我是在炫耀。

简单的求和只能算出我一共要还他多少钱,他又不知道我是几秒钟算出来的,根本构不成智商的碾压,我还有终极大招——终极无敌求和大法。

为了实现智商碾压,我把账单按每年每月的发生额汇总到同一个工作表中,准确地说是用了 5 秒钟轻松算出了每年每月的发生额,并做了一个GIF动图发给他。

我把它命名为——求和届的降龙十八掌。这看似复杂的公式背后到底隐藏着哪些故事呢?且听下回分解。



声明:为了使教程更生动,文中常以第一人称增加代入感,但是我真的没有那样的男朋友,新闻里的人不是我。(严肃脸)

推荐阅读更多精彩内容