被逼急了什么事都能做出来吗?
恐怕数学不行!
恐怕公式不行!
恐怕......
自从开通专栏以来,常收到一些求公式的私信,相信也是被逼无奈才发出求助的。回答过程中也发现一些有趣的现象,一般发过来的表格如果不好看的,公式都非常难写。这里说的不好看,不是可视化方面的苛求,而是一些基本要求。例如:
小数位数保持一致
对齐
边框线
......
记得以前看过一本《为什么精英都是Excel控》,书中一直强调格式的规范。只有表格格式规范,才能一目了然,才能少犯错误,才能提高效率。建议对表格格式不大注意的同学可以去看看,绝对让你端正表格观。
回到主题,分享一个计算案例。
有一位搞勘察设计的小伙伴,他手里有一份勘察设计计算稿。
开始之前先了解下背景。
我以前眼里的土壤是这样的
在专业人士眼里的土壤是这样的
如果要在地上打一个洞,打洞的难易程度取决于土壤类型以及深度。土壤类型以及所处深度不同打洞的成本也有所差别。
- 现在问题来了,勘察队采集了从上到下一定范围内的土壤数据,如下
- 然后,还有一张单价表,标注了每种类型土壤在对应深度的单价
- 现在的问题是要计算每个地点的施工成本。有兴趣的同学可以模拟些数据,先不看后面的思路,拿去练练手,相信方法肯定非常多。
这是一个烧脑的事情,用一个单元格就把公式写出来,我是搞不定的。只能分步骤,将一个复杂不好解决的问题,转化为一个简单的容易解决的问题。
第一步,从上到下记录每个深度的土壤类型
如果能够将原始的以土壤类型为分类的数据表,转化为以深度为分类的土壤类型表,那么这个问题就容易解决了。
你可以理解为,前者是从上到下,分别记录每种土层的厚度的数据表。后者是从上到下记录每个深度的土壤类型。因为测量的精度是0.1米,所以把100米深的土壤,切成1000份,然后标记每份的土壤类型。
第二步,把每层厚度数据转化为每层的标高上下界数据
直接转化上述表格,还有些难度。继续建立辅助表。把每层厚度数据转化为,每层的标高上下界数据。这一步使用Sum函数+if函数+单元的混合引用,很容易完成。
第三步,填写第一步的表格
这个稍微有难度,但至少是我能够解决的问题。需要用到:
INDEX + MATCH的模糊匹配
以及If函数
还有Max函数
两层IF嵌套,加INDEX 和MATCH还算是比较正常的公式。
第四步,计算成本
有了第一步准备好的表格,这一步简单多了,一个SUMIFS公式轻松搞定。
以空间降难度的方法在Excel中经常用到,使用这种思路可以解决不少问题。思路是这样,形式多样,例如日期表、匹配表。
关注公众号【Tanxindata】回复【精彩模板】,获得文中模板。
加入在线课程《世界500强都这么玩Excel》手把手带你完成更多实用案例。
Liping:如何精进Excel水平?从邮件小工具讲起zhuanlan.zhihu.com