excel表格_excel函数公式大全_execl从入门到精通
Excel高效公式的常见五元素，你用过几种？2024-02-28 10:42:59
上次分享讲到，我的微课学员们在Excel应用中的一个常见问题，那就是公式并不高效，比如，有的在每个单元格输入公式，而有的在源数据变化后，需要再依次修改公式等等。
那么，怎样才能写出高效的Excel公式呢？在我看来，这离不开下面的五种高效公式的常见元素。
一、相对引用和绝对引用
计算各个商品的总价：在D2单元格输入公式：=B2*C2，右下角双击复制。
计算各个商品的折扣：在E2单元格输入公式：=D2*$G$2，右下角双击复制。
计算折扣时，引用到G2单元格的折扣率，为了让公式向下复制后，能得到正确的结果，这里的G2单元格引用采用了绝对引用，“$”符号就是绝对引用的标志。
公式进行复制时，绝对引用的，即行号列号前带有“$”符号的，复制后不会发生变化，否则，将根据复制目标与原公式的相对距离，被相对引用的单元格，将发生相应的变化。
借力相对引用和绝对引用，使得公式可以被正确的复制，实现高效的公式输入。
——之前的分享《“$”符号说：相对于Excel，我绝对不仅表示美元，我还有更多内涵！》，对相对引用和绝对引用也有过相关的介绍，大家可以关注和参考，这个概念可是Excel公式的重要基石！——
二、行号、列号要查找某个订单号的所有客户销售信息，在H2单元格输入公式：=VLOOKUP($G$2,$A$2:$E$11,COLUMN(B1),0)，右下角向右拉，复制到K2单元格。
VLOOKUP的第3参数，要指定返回的内容在查询区域中的列数，这里使用了“COLUMN(B1)”的形式，使得公式可以直接右拉复制，而不是逐个单元格地更改第3参数的返回列数，那样就算不上高效的公式了。
通常使用到的行号、列号函数有COLUMN、COLUMNS、ROW、ROWS，通过在这些函数中，结合相对引用和绝对引用，可以产生行或列方向上的多种自然序数，再嵌套到其他函数中，从而实现高效公式的复制粘贴。
三、辅助数据
要查找某销售员经手的所有销售订单号信息，先在最前面的A列插入一个辅助列，在A2单元格输入辅助列公式：=(E2=$G$2)+A1，右下角双击填充到A11单元格。
之后，在H2单元格输入查询公式：=IFERROR(VLOOKUP(ROW(A1),A:B,2,0),"")，右下角下拉复制到H11单元格。
VLOOKUP函数有个硬性要求，要查找的内容必须位于查询区域的第1列，所以，这里通过构造第1列的辅助列，来实现销售员对应所有销售订单号的逆向查询。
另外，一个销售员对应多个销售订单号，通过构造辅助列，将销售员经手的每个销售订单号编个唯一递增编号，那么，VLOOKUP以这个唯一编号作为查询对象，随着公式向下复制，每行将查询不同的编号（看，这里也用到了ROW函数对应的行号“高效公式元素”），从而罗列出正确的销售订单号列表数据。
最外层的IFERROR函数，是为了防错，公式下拉复制到H11单元格，是假设某销售员经手的最多销售订单为10个，当不足10个时，使用IFERROR，使得公式返回空，而不是错误值“#N/A”。
使用辅助列、行、单元格等辅助数据，不仅可以简化公式，更能完成一些意想不到的妙用，使得应用公式来解决实际应用问题变得更加高效。
四、名称
要统计不同部门、不同年度的总工资数据，在G2单元格输入公式：=SUMIFS(工资,部门,$F2,年度,G$1)，向右复制到I2单元格，再向下复制到I6单元格。
一个公式，可以复制到所有目标单元格区域，得到全部的统计结果，这正是高效公式的重要特点。
公式中的“工资”、“部门”、“年度”都是定义好的名称。
从上图大家也能看到，我是通过“根据所选内容创建”的功能按钮，批量完成了名称创建的。
SUMIFS函数汇总统计满足多个条件时的数据，公式中直接使用名称引用（提示：通过F3键可以直接选择粘贴名称），而不是引用到单元格区域，使得公式更易维护和理解。
——更多关于【名称】的内容，大家可以关注并参考我之前的分享《浅谈Excel中的name——名称》——
五、表格
Excel中专门有个“表格”，可不是我们通常所说的Excel工作簿或工作表哦。
通过CTRL+T，可以快速将单元格区域转换为“表格”。
“表格”拥有很多优秀的特性：颜值高、自动冻结首行、首行自动添加筛选、数据范围自动变化等等。
这里，我们仍然是统计不同部门的员工数，那么通过将单元格区域转换为“表格”后，再在E2单元格输入公式：=COUNTIF(表8[部门],D2)，下拉填充到E6单元格。
当我们在第23行新增了一条管理部的员工数据后，可以看到E2单元格的员工数统计结果就自动变化了，我们不用对公式做任何修改，是的，“表格”已经帮我们做好了，是不是很棒？这就是“表格”数据范围自动变化的体现。
再回头看看公式，其中的“表8[部门]”，看起来跟单元格区域引用、名称引用都长得不一样，这个就是“表格”所特有的结构化引用，“表8[部门]”就代表着“表格”的“部门”数据区域，正是这种结构化引用，让公式变得不一样，能找到变化后的引用区域在哪里。
那么今后也将更加详细地跟大家分享“表格”的更多魅力。
以上这些高效公式的常见元素，大家掌握后加以应用，就能避免很多的公式反复录入和修改，让你变身为Excel公式达人！
标签: EXCEL中的公式包含哪五种元素