第二天excel应用--高效做报表之excel自动填充及函数的混合应用

当我们的excel内容是由公式填写时，沿着横竖拖拽箭头的黑十字excel会对内容进行自动填充，在填充的过程中，不变的是公式的形式，变的是填充的内容，如下图，注意观察横纵坐标，如1的位置是H60

另起一个表格，另它的的公式为H60，我们以它为起点，分别向下向右拖拽，结果如下

大家看到此处想必内心有了想法，不妨停下思考整个表格数值，对照以下完整结果看看是否有出入

因此我们可以得出结论

横向拖拽时，变横坐标,每次变化1个单位--如A+1=B

竖向拖拽时，变纵坐标,每次变化1个单位--如1+1=2

这个变化规律十分有用，也解释了为什么有时直接拖拽会出错。有时我们不想让区域变化，这时可以用到锁定“$”在你要保持的区域前，快捷键Fn+F4（笔记本）是横竖坐标都锁定，再按一次就是解除锁定

还想让excel更加顺心地自动填充，就要用到index，match，sumifs函数的混合应用了

index，match，sumifs函数的混合应用

对于以下表格，我有一份源数据，我已知平台门店名称，如何求门店id和品牌名称呢呢？

源数据

最低级的做法，就是回到源数据，一家一家的对应查找如何填上。这无疑很慢，我们不妨用函数填写，然后拖拽。已知平台门店名称求门店id，我们想到了index函数，它会返回对应值那么我们可以写出伪公式? ?=INDEX('拌客源数据1-8月'!A:X（源数据全部）,行,列)

求值区域就是源数据全部，那么行列呢，我们只知道具体平台名称，那是不是就可以选中具体平台名称对应的那一行，我们要求的是门店id，是不是对应的列也选定了，所以问题来了，我们如何知道具体的行列地址呢？求地址，我们想到了match函数，我们分别用2个match函数，是不是就可以分别返回他的行号和列号

行号：MATCH('常用函数-完成版'!B112（具体门店名称的坐标）,'拌客源数据1-8月'!I:I(具体门店名称所在列),0（具体查询）)，这样就可以返回行号

列号也是以葫芦画瓢：MATCH(D111(门店id在表格中的坐标),'拌客源数据1-8月'!A1:X1（门店id在源数据中所在行）,0)

再把他们写回index函数，我们的求值公式就完成了

但是我们还要实现自动填写，所以要考虑锁区域的问题，首先源数据的范围不应该变把，所以要锁定。

先看行号，我们希望数据里的行号参考的永远是平台门店名称这一列，所以把B列锁定（'常用函数-完成版'!$B112），希望列的查询范围永远是源数据平台门店名称所在的那一列，锁住（'拌客源数据1-8月'!$I:$I），匹配模式不会自动改变，不用管

再看列号，我们希望数据里的列号参考的永远是111这一列，所以锁住111列（'常用函数-完成版'!D$111），希望查询范围永远是源数据的标题，锁住（'拌客源数据1-8月'!$A$1:$I$1）

至此，自动获取值函数填写大功告成

那如果要算GMV怎么算呢？注意，由于源数据是多日的门店数据，所以计算GMV要把多日数据累加求和，首先确实大函数，累加求和用sumifs（sumif也可以）

我们已知求和的判断范围和标准对吧，判断范围是源数据所在平台门店名称那一列，标准是对应的平台门店名称 。公式=SUMIFS(求和区域,'拌客源数据1-8月'!I:I（源数据所在平台门店名称那一列）,'常用函数-完成版'!B112（平台门店名称 ）)

那么我们需要确定的是用来求和的数值区域，什么可以返回GMV一列数值？

index行号为0时可以返回一列数据，因此我们用index函数? INDEX('拌客源数据1-8月'!A:X（数据范围）,0,MATCH('常用函数-完成版'!H111,'拌客源数据1-8月'!A1:X1,0)（对应列号）)

把index函数写回，加锁，我们的求和函数也写好啦！

数据与函数解释来自https://yrzu9y4st8.feishu.cn/mindnotes/bmncnOxqQPowAqr0iPzUZYFTirg

我是初学者，文章内容基于我个人理解，如有问题欢迎指正，也欢迎大家多多和我交流！

你今天很棒！