哈喽艾瑞宝迪，这里是一周进步。
我是小魔进。
最近有一条让我直呼牛哇牛哇的新闻：
图像源于网络 侵删
新闻的大概内容就是一名女研究生和男朋友吵架，坚持“以理服人”，唰唰两下就给男朋友做了个“近几日吵架分析”的思维导图。
思维导图的开发者：我怎么也没有想到它居然还有这个用处的......
这不是巧了嘛，在很久很久之前，我就曾经写过一篇《情侣分手甩出十年账单？Excel 帮你花样清算》。
Excel的开发者肯定也没想到，这个折磨千千万万打工人的绿色玩意儿，有一天还能用来算感情帐。
在这篇看似不正经的文章的结尾，我留下了一个终极求和大法的悬念——求和届的降龙十八掌。
想不起来也没关系，这里有个穿越通道可以带大家重新回顾一下>>
嘿嘿，现在记得了吧。
只要动动鼠标，十个工作表中每年每月的发生额跃然纸上。我是怎么做到的呢？
请大家搬好小板凳，腰板挺直手背后，我要来揭秘降龙十八掌背后的故事啦：
前情提要
在很久很久以前，我还是一个特别白的 Excel 小白，整日为汇总报表苦恼。
每天都希望有一天可以练出盖世神功，让 Excel 可以听我指挥，我想要什么，它就自动帮我算出什么。
哎呦，白日梦做得可真精彩，可万万没想到，有一天，它居然真的实现了！
你说啥？我听不懂......
“万能的 Excel ，请帮这位失恋的姑娘算出她十年间每月花了多少钱吧。”最初我是这样表达的。
可是它并没有帮我算出我想要的结果，好像完全听不懂我在说什么，是我没表达清楚吗？
“我想要统计每年每月的发生额，就是要在每一年的的工作表中找到每月的金额并求出合计。”
还是不行。一定是这个问题太难了，它理解不了才没帮我算。那我就勉为其难帮它分析分析吧。
汇总账单的列标题和表名 2009 年 - 2018 年一样，行标题又恰巧是要统计的月份，所以「统计每年每月的发生额」这个问题拆解出来就是
根据列标题引用相应年份的工作表
找出工作表中月份等于行标题的区域
把相应月份对应的金额区域求和
这样把接受不了的大问题拆解成一个一个可以消化的小问题，解决起来就会容易多了吧。
emm......还是没有任何动静，我恍然大悟，是不是它根本就不明白我在说什么呀？
也许再过个 100 年，我们和 Excel 之间真的可以进步到声控的地步吧。可是现在......
我需要一个 Excel 翻译。请跟我说「普通话」
如果你跟一个不懂汉语的外国人说中文，就算你措词再准确、表达再生动，语言不通他也一样不会明白。
所以若想指挥 Excel 就得用它那边的「普通话」来发出指令，而我们人类将这种普通话称作：逻辑语言。
好，那我就把问题一句一句翻译成逻辑语言，让它能够真正明白我想要的到底是什么。
01. 根据列标题引用相应年份的工作表
我想先把第一个问题「根据列标题引用相应年份的工作表」翻译出来。
我知道 sheet 表引用的格式是“ 表名!单元格 ”，而表名和列标题一样，可是怎样将列标题转换为表名的地址引用呢？
我发现了一个超级无敌万能的引用函数「INDIRECT」，掌握了它的用法就再也不用害怕和高手过招了。
INDIRECT 函数有两种引用形式：一种加引号，一种不加引号。
两者的区别见下表：
- 公式=INDIRECT("B2")，单元格引用加英文双引号，表示提取被引用单元格中的值，所以返回单元格 B2 中的值：D4。
- 公式=INDIRECT(B2)，表示提取被引用单元格中所指向单元格的值，所以返回 B2 单元格中所指向的 D4 单元格中的值：一周进步。
那我想提取列标题中工作表名称所指向的工作表区域该选择哪种用法呢？当然是第二种啦，这还能难倒我？
我还知道如果要引用2018 年工作表中 B4 单元格的数据，就是要组成「2018年!B4」的格式，2018年就是列标题K3 单元格的值；
再用 & 连接符将 「K3」和「 !B4」 连接起来就组成了 sheet 表引用的格式，所以公式就是=INDIRECT(K3&"!B4")，哎呀我可真棒！
注：「&」用于连接数值或定义名称外的字符串都要用双引号。
综上，「根据列标题引用相应年份的工作表」这个问题用Excel 的逻辑语言翻译过来就是
引用工作表日期区域「=INDIRECT(列标题&"!A4:A60")」
引用工作表金额区域「=INDIRECT(列标题&"!B4:B60")」
日期和金额都找到了，接下来再解决求和条件——按月统计的问题，也就是用 Excel 的逻辑语言表达「找出工作表中月份等于行标题的区域」。
02. 找出工作表中月份等于行标题的区域
原数据是按照日期填写的，怎样能按月统计这些数据呢？
我得先在这些日期中提取出相应的月份再判断是否和行标题中的月份相等呀。
这个不难，Excel 中的「MONTH」函数就可以解决，它的用法非常简单：MONTH(日期)。
比如，我要提取2018年工作表中的日期对应的月份公式就是「=MONTH(A4)」
所以「找出工作表中月份等于行标题的区域」这个问题用Excel 的逻辑语言翻译过来就是「MONTH(INDIRECT(列标题&"!A4:A60"))=行标题」。
如果符合条件则返回逻辑值 TURE ，比如要判断2009年账单中 A4 单元格的日期是否为 1 月 ，公式就是「MONTH(INDIRECT(B3&"!A4"))=A4」
求和条件解决啦，而求和区域就是与之对应的金额区域「=INDIRECT(列标题&"!B4:B60")」。
那怎样按条件求和呢？坚持住，还差一个问题就可以大功告成啦！
我得赶紧翻译出「把相应月份对应的金额区域求和」这个问题。
03. 把相应月份对应的金额区域求和
查了好多资料，我找到了一个巨好用的多条件求和函数「SUMPRODUCT」，它的基本格式是：SUMPRODUCT(条件1*条件2*……,求和数据区域)；
我把已经翻译完成的求和条件和求和区域填进去就得到了公式：SUMPRODUCT((MONTH(INDIRECT(列标题&"!A4:A60"))=行标题)*1,INDIRECT(列标题&"!B4:B60"))
为什么要乘以 1 呢？因为 SUMPRODUCT 函数有一个特性，它会将非数值型的数组元素作为 0 处理；
求和条件「MONTH(INDIRECT(列标题&"!A4:A60"))=行标题」返回的逻辑值 TRUE 自然是属于非数值型的数组元素，所以我使用「*1」的方式，把逻辑值转化为数值。
厉害吧，一般人我都不告诉他。
所以如果我要统计出 2009 年 1 月的金额公式就是=SUMPRODUCT((MONTH(INDIRECT(B3&"!A4:A60"))=A4)*1,INDIRECT(B3&"!B4:B60"))
终于算出来啦，那我把公式像下填充是不是就可以得到每个月的发生额了呢？
哎呦，怎么不对？！
原来我所引用的工作表名称单元格 B3在我向下拉的过程中自动变成了 B4，B5......
怎么能让Excel别那么自作主张，让 B3 在向下填充公式过程中保持不变呢？
04. 锁住我我就不会变
Excel 中有一把神奇的小锁头「$」，放在谁前面就会锁住谁，锁住它它就不会变，不信你看看：
为了使 B3 中的 3 保持不变，我在 3 前面放了一把小锁头「$」就把 3 锁住啦。
那如果我想统计其他工作表中的金额呢，我把公式向右填充，看看会发生什么：
在我向右填充公式的过程中月份列的 A4 又自作主张地变成了B4，B5......，这回我要把 A 锁住，所以我在 A 前面放一把锁。
当当当当，每年每月的发生额跃然纸上，在右下方填充选项里选择「不带格式填充」，就能保持原来美观的表格状态；
选中表格所有数据区域，按下「Alt + =」，每行每列的合计金额也会自动现身，简直不要太棒！
翻译的过程看似复杂，但其实我只用到了三个函数就轻松解决了普通人可能要用几个小时才能解决的问题，你说气人不气人。
“等等，说好的降龙十八掌呢？我看这些「翻译」不像招式倒像是咒语呢。”
这才是真正的降龙十八掌，准备好了嘛？我要开始出招咯！
新建一个 Excel 工作表（用于存放汇总后的数据）- 点击数据选项卡 - 选择新建查询 -从文件 - 从工作簿 - 选择「十年账单」（要汇总的 Excel 文件）- 点击导入 - 选择整个工作簿 - 按下右下角的编辑按钮，即可打开PowerQuery编辑器。
注：目前是只有 Excel 2016 可以直接使用 PowerQuery， Excel 2010 和 2013 必须安装插件才能使用，WPS 不支持该功能
插件下载地址
https://www.microsoft.com/zh-CN/download/details.aspx?id=39379）
工作簿里包含的所有表，全部位于 Data 字段。
选中 Data 列，在开始选项卡中选择「删除列-删除其他列」，点击 Data 字段右下角的小按钮，把全部字段都扩展出来，就可得了我们要合并的数据。
筛选掉每一列的空值「null」（把 null 前面的 √ 去掉即可），点击「将第一行用作标题」按钮，即可把首行提升成为字段标题。
检查一下汇总后的数据，发现每个表的表头（日期、金额）都被合并过来了，所以使用筛选去除每个表的表头。
最后点击「关闭并上载」，就把 2009 年 - 2018 年 十个工作表全部合并到新的工作表中啦。
前面的招式已经足够你行走江湖了，但是要想成为武林奇迹，降龙十八掌可不能只打一半哦，接下来的招式可得看好咯。
选择日期列，在开始选项卡中把单元格格式改为短日期，然后点击任意数据区域，按下快捷键「Ctrl + A」全选，在设计选项卡中选择「通过数据透视表汇总」。
为方便演示，我选择把汇总后的工作表放在当前工作表中。
在数据透视表的字段列表中，将「日期」字段拖动到列区域，「金额」拖动到值区域，Excel 2016 会自动对日期进行分组。
去掉我们不需要汇总的「季度」字段，我想要列标题为年，行标题为月份，所以在列区域剩下的「年」和「日期」字段中把「日期」字段拖动到行区域。
见证奇迹的时刻，大功告成啦！
怎么样，有没有被求和届的降龙十八掌惊艳到？Excel的世界其实并没有想象的那么枯燥嘛，PowerQuery 和数据透视表强强联合可以让你的效率 up up up......
Excel 的江湖还有更多绝招等待你去解锁，锁定一周进步，我们一起成为更好的自己
赶快练习一下吧，学会了记得回来赞我一下哦~ （就这点追求了）
我是小魔进，我们下期再见！
x-_p-_o-[U2FsdGVkX18PyfvT/qle+DOX67uExfk5ypWFnpUIFPeAR/cWZAPSuokOMH17rjy8IjB6+eBEBlX4wu+XKBKOrJeah7/+2P1Nh63Gt+YX5/aMaFt1/zHkpqH3AzLlKz9/HxDcNR8qqYTeRSGTftMK/JYG/ybDc3oQShqTqZnBAMaLt4sS7rIsUw7EPUwNBPehiUC8bfkWbM1Ni2Q9b9vewJUnPwUpPifoXtC+v6Y0LUWmS5lYGDFXBpr1jF3oMCSA9JWxLFug2J+abaWfcEVDq4DbLyI6IpPjIGk4IToMpCS2AXEio7CSl7FosnLkqH1rJqCQ9TlN0dAR+GWLv0Ib9BlNJKbrse2kJGqHxljPmixlf0VVxJyNT7rXeS8qqRHmHHGGjazCi3/sed1upJYjaY1WrTZyjdvyzRbkBrlZ1Co4VTG25Mnj/bBdkwD2isiCb0xvZOcVeUme5us4ZwBpQ5E8KlJ6JaGSQvQbRRxJ9745vAun7XdV1hjXG/o3Xspsn+j2wQyRWMK5kG37Sixp6OAyyKZSmhOs/SMlqvzQtMFP6iJalO7SNE+duBprOaa/cG3Zi2/TXtkOXmwka1ht+scXoPQEkoJF1dduDQLKbbUrQPyJh28FcpMfUGrvFLujcCYcbsbZpNp0BInOiZ3Zk8yCsGO90dF2Rl1LoO4sfCPyTx7EMUw8LBkX8HdZQ4AK9v/rlmZulgu2K6qMRJ6tOPp8tvTlQcva5hcuvkVYQfIJhndFQzkCGCPa4UfMU+0xR5OZADL53YdkvORhEBRVYoqGmXmKZuwkKxbIHVC8CvFN8qpWft0TXx23YwawTQHHihwl26OFElrSra9FeI4H0IZTjuCSfRyDg/d/VbowzkeW9/IuP9CGqOFFd2uB7/dHQTSxhRqh1oDhLPJttDwteGPMXqeX/CbKNsaHFvHmGmJUSp8sK6KOiYijVgzNLFK+yswe0r3r9pM/yO0vk++b/kYtJuZXVK46Jy8+r4kMyeIXeX5PFumf9RSeyEkud761qs7m/WiW/Cyk77EyInjq4QWj3B14mq3WYhoahijEGsLH3LauT3KUdptiA6qtG8fYsda3G5Pcc/NW+su38ycjaWLsQqLGWKnKdaLgG2rNU/EoPU8f2rM6h4w3PF/cPfyRllCeMzoS/mWgsja1nvC5MZnqh0QcYmgNbP2BXGuecjl0ald6FzJbnosy9rZcXtaPPJAbNege/uhT2shTXOKQh6mMvIHYIXYgKqL7TjVDMH35/7Eqjuk4COehAuGY9aI7Oke4XonUiTHigGqUXdQC1qAw0nMPgmoUqZbcGpWhf41cFpnYfDVDKjiI1lOCiTLMFE4ZlccjGzKUzqv/xNgKfZIfonz4dE+HmBXttkdeIzJb4w4yHE+Cz9YDB9XoDMwIezwjz+xGfJ070+OsWt3ib48awd823oTafYyr7KoUYBJTJMZK02a/0OuSBgFqTg0xYBygobQ5gWj21tLa90p6EqPgaYSfzYrwCzWZHUdsVKa4pnZ/tYxSIay/jRy59/t/UsRUPN+SkK+5nGxfutRfJ0+Rd0DY6JYZ/693np/uLKTMVuODCCQpPKUj5lONTK4Bp3Q7wH6KFdEebHi49RR3mH5WcZUAjtIuAeMVUTFVh2/m6pfc+KXnQb7LG1rQvfoFeX9S62wDyrAhVfb6N/PVAfFPflatBd5RaGr6dB7NLNyPz849CAb28q6hAWYM0F0+gHrnN/RiHrYISKlfaEVm+p5vlLqfq2jzD/C5v31E+8sAnJJt+d3hduD5azWJKdYGBX14xR99h2COlCkwdOfX3UaEAQmYg0qtnAsi8YQqbg3LVoOdxDDHAG5E4j+JrRcPF3sZMDJodtamMxwKM19j5JnDar9iGOejddV/c/yJofBIRY4T8r0m2E3ifIKUM09X+yw6nwtEWNpe/oojukUznjesEceozlZkWaIbjw24N9x7pIaFWIMOeAEmpmvOwDXmjyCuX/BlPhWG2sYx+YaiBh2c9BSh9UGykmCbOJmrEuRP4YJCYcixZ1SUOq6xdUZNGjkGgphO2/Qq9sMMU9voDb4RKF/IFnXf1RJ/5HuXs0Rw/JVLSumY7V7ZKe8XP3j3VEW7t7g3fprqKSf6jB0rmmDLCuMgg/CmovxyqGKQ32Vx4B+RLHUMoJZ5mwlMMDwSvF/wOa1i13LAJXn4uZq3oN2ABDwQP7gZvLL6+cdfWBMMQAzV0QKG+gnL7ihImaPS4tmyUjPx0xQZhkPniqe0QmNN6UIXxOR8Fo+DJxk3eFfzTXT4sSJwD5lkl6ZuGKUUwJPpPi8l10N5T8gVFB/0F8n2iWUqdwVzMOeA6/Y3SmHFFbhFfZNjViLoxr39aUSUayMqvFdwe1X6AyD3PiUmle+x3xwWcKWWEHMIhAzdgXDofZzc93IRKEc50Mtx0wzac4TZQD4L2kTITXxfI/6aN1ggOymYbzbTcTuL0ZZ7NVdOeMg+AOa5Lonjsw/V7E3pA7uGyymk29AOSVofhWv/hLNy7wAesoefHVsUNc0zKYFDeOubxiV6guDe7kO5/l95NulU6abQqAokHEQVP8rHCzkNhoC4F7njj+RU+TaQsuVN9V8teByIQlt34n8RUNwmIirnHbadfpIQ4z3S7EMdcEL83GdV6ORnHDsPjdlgtBCtlE0yBIlaD9wrZi6lIEHm2hOJY8bhzwvpeya6jjYLtobql9S0CWt4HKGod6R5AWFaVIYmHHNSOKcomwik2dSq79n7XHXIpdSCngWaDedcZ92nq9Vd7t3OYW4E/DNOO6jL0WlM4GMyfwqFxfw22KaYGCDvVjBCNvKV+Zrwdsvgzfl9HhKuAup/qlzxMBx8Kplk4ikJFkXAW/TraGx7IRBPWkU3ul6dW6BXPiWD4TJTqyrAL5M2os64ZIzvakOuBwXKJ3q2FOaW1sh/TkbgxWkEN9mZL59XA9vIHjiSrxCZLSYRvN28mIDbyo8E3YGuO+ATghMbZw73v0gj+D9m6ejhUcfpYoHrSYRu9zu67bejYBhDBJp47mFbxhYPA+bFV8CybW6wdwG0rSlaTwy8EScJZWyQfkTXGt+lnjbyT40I1KKAv4rNhb7kw0bVFMl8cAGEfDEu+1E2nVYl1A4MqpcqYKh+2kKTnG1/G7RhVR5Qz5llDEO2BEG94fNnabnY4dPTX0q6A5CBBgVj9pMMATpoAUvyv5OjW9gckDz2/8TY1vh5cw4S6BkAfoBYRkgBHKwWk3vV3PgFjGRzZY22BourQEfPnuU92HSTJG2G++PKYL1PiTQCsamrBoA4O7P8n5bx4IeXnW6qOKDXYqFrlOseKQZG5Bqz6qKEaR6DXBYrDetQpd+6GnzvrLhPDx42Ql66BuDRpOzL8yajeXQozkhAPIRNF/oBEIi5sfXd6CeCbaCOhsEoFIKE7MeJ2UugnNQCppd90xDcKm56qCQxMuqlqaWXciWNl+zv7YybpG2wIbiSTs2HoBrGaU0ESxDmr1yCeImWVZASAzVAS7lCiNuSi3JAn9u98owH9dyTUULP4lUjTSNpnrbpLosxSJ3+n8C6VGbb5LkBNkcfndTUuAEG09DVF/jOYsPktD1ILBy7h1rD6sFFZ8M2wSpSTY6w6QWZtEdtxWLonLv+St7hlyGK0Ni+KEA6XumfWr5fMsz+1ZaIJm4OM17UTqc0Rm9FMgRjbJW51C2FmsqVY07o+pScgShZqhhkSoZ4EclNdjQ+M6yy2Hv7Zla3S59c6gMSK0KjVnJvkGW9C6SzD8ttjkbD2ySYRwOIWNhJAzOALxQWCqSEe3WDrn0dMZhCsA2elc4bsPTa7VcDXiO8R+RvG5kYt0buFzVGjtKiXNXQvyCzabTvE6GBW6ChtgrZq56Rz02DG9Lm7pp1BtCxSrsl3tNBfu9id84ePh1PpYX5n8Rmg8oXoQyHVPk0T5CaG5hBa3X3+nYd4fzhD/N1/YmL+zhIpOZ8pdqAqD09d0+Qu1mc0hzThZwCmW+WG5XSYrNCjaiHpEw8ofmnB2drpIwbHkyt++rxOJIqimw9AnUkk1/rk4KtMzBa8rahZzZtpzgJRlVzAykUEELVcXDmJBEkqqZ3lbhii8BhsMw2GrYkRAJs9MbHHYKl+TILDl4r7DJGrYJFHKhd8F7zZgMLsEwQGzo/cLjLSTrp3HAbpeaQYYZ5J+Lkz/thlnMKIDKhEnDtNtj/S8XC/oJErwpdxfO9ES1rYLiDnE3qJ6vuuy5icnBCtBo9D8ILwOgJsyOZIXOOBeLgBFIG4dLELOQmRRqfKH+2hTJGpYUQwO4/NVRvvhQSA5cuwvz4AJQ5CrtiQn3bBAIIaq/6kxstfjca6GKMmiH/p+YY7jpTzaFfp2c/bGP3LfmlPiq8Je0RxpIH7n4Vt4t5qyjLODJH6S8yxFm9s8Y8l84cVrvMEQPHENHBp9bA50/wY3l+Ym72R7SV33haGQGa3wYRUGYQ/4Yi8PG7FugSWNE6SYtOuR3iN0n/EHx4EcaEgNz00hXCPsvqpUJlmbANvnzjONUX5eAo9HVz1YpjntAW7iERkxp3lEj2vjpap1hboevuykieJfcfxhFVVw5shZ1+m2hCzh+BmRrZwO6i6EXem7C1DkARR6cDPENvCHDLOkhXHIvf97jNQwyR+XXkndSvxcjUKEWAYjSLLHP8ud+96N0ycIJnADNVRAeiqL0w3j+DQuN236KyUwo5OVmyofEUwNH/NJtUKPrTjUKz1zPUq6YPeLAbC/quauPbiIHuDCBPwHr3IFbQSi5/M3nsgfxChYEWXyliwjEjKFsCgswWZ3SqRCh8qH4bqbNtzQ==]-o_q-_-x
热门跟贴