美文网首页
Excel读书笔记7——使用辅助列(表)

Excel读书笔记7——使用辅助列(表)

作者: 梦幻天堂晓 | 来源:发表于2019-01-19 10:48 被阅读14次

          工作中使用Excel的原则是:实用至上,能简单就不复杂,不求最好,但求最懒,用最快的方法解决问题,不必追求最完美、最漂亮、最有技术含量的方案。

          遇到复杂问题时,编不出合适的公式、自己掌握的知识无法解决时,就应该要考虑能否使用辅助列、辅助表格来牵线搭桥。在很多时候,使用辅助列可以将复杂的问题简单化。

          辅助列就是在表格之外增加的列,对表格的数据编制公式进行计算。然后再对辅助列的数据进行运算。实质上辅助列就是将比较复杂的问题分解成几个小问题,通过在表格中增加辅助列,将复杂公式中本来在内存中进行的多步骤运算,放到表格中进行多步骤运算,从而将复杂的问题简单化。

    1、使用辅助列提高计算效率

    比如A1:A10000单元格区域有一列数据,现在需要计算数据中唯一值的个数(空值不纳入计算),可以使用下面的数组公式来计算:

    {=SUM(IF(LEN(A1:A10000)>0,1/COUNTIF(A1:A10000,A1:A10000)))}

    如果我们在B列添加一列辅助列(假设A列数据已排序),B1单元格则根据情况输入1或0,在B2输入公式:

    =IF(AND(A2< >"",A2< >A1),1,0)

    然后下拉填充至B10000,然后在C1输入公式:

    =SUM(B1:B10000)

    使用以上公式计算时间大大降低。

    2、使用辅助列提高操作效率

    使用辅助列来牵线搭桥:根据需要在辅助列增添一些数据或设置公式,然后使用Excel已有的功能解决工作中的需求。通过使用辅助列可大大提高操作效率。

    2.1使用辅助列快速隔行插入空行
    图2-49 使用辅助列快速隔行插入空行1

    需要在各记录间都插入一空行,可在F列构建一列辅助列,在F2单元格输入1,然后按住【Ctrl】键,拖动填充柄,下拉填充为1-14的序列。

    图2-50 使用辅助列快速隔行插入空行后效果图

    选定F2:F15单元格区域,按【Ctrl+C】键复制,将其粘贴到F16:F29单元格区域。在旁边的空白单元格输入0~1之间的任一小数,按【Ctrl+C】键复制。然后选定F16:F29单元格区域,选择性粘贴——运算(加),粘贴后F16:F29区域分别为1.1、2.1、3.1……选定A1:F29单元格区域,按F列对表格进行升序排序。排序后结果如图2-50所示。

    然后删除辅助列F列和H列。

    打印工资条时可以用到此技巧,具体方法为:使用上述操作步骤后,再选定A2:F28单元格区域,按【F5】键打开定位对话框,选择“空白”选项,即可选定空行的单元格,此时鼠标不要点击,输入公式“=A$1”。然后按【Ctrl+Enter】键,所有空白行均等于第一行,然后调整行高、列宽,就可打印工资条了。

    2.2快速合并相同内容的单元格

    使用辅助列技术来达到快速合并相同内容的单元格,主要有使用数据透视表和使用分类汇总两种方法。下面介绍使用数据透视表的方法。

    打开示例文件“表2-17 使用辅助列快速合并同类项的单元格”,表格如图2-51所示。

    Step1:在F列插入辅助列“序号”。

    Step2:选中数据表格任一单元格,点击【插入】选项卡—“表格”组的“数据透视表”按钮,弹出创建数据透视表对话框(见图2-52)。

    图2-52创建数据透视表1

    Step3:将“部门”“管理人员”字段拖入行标签区域,“序号”拖入数值区域(见图2-53)。

    图2-53 创建数据透视表2

    Step4:选中数据透视表,点击右键,选择“数据透视表选项”,在弹出的“数据透视表选项”对话框的“显示”选项卡勾选“经典数据透视表布局”(见图2-54)。或者在数据透视表工具的【设计】选项卡,点击“报表布局”按钮,选择“以表格形式显示”。

    图2-54 设置透视表布局

    Step5:点击透视表H列“部门”字段旁边的“自动排序”按钮,在弹出的快捷菜单中选择“其他排序选项”(见图2-55)。

    图2-55 给部门字段排序1

    Step6:“部门”字段依据“求和项:序号”字段升序排列(见图2-56)。

    图2-56 给部门字段排序2

    Step7:选择数据透视表的“部门”列,点击右键,将“分类汇总‘部门’”的勾去掉,取消对字段的汇总(见图2-57)。

    图2-57 取消按“部门”进行分类汇总

    Step8:选择数据透视表的任一单元格,点击右键,点击“数据透视表选项”,在弹出的“数据透视表选项”对话框中勾选“合并且居中排列带标签的单元格”(见图2-58)。

    图2-58 合并且居中排列带标签的单元格

    Step9:选择H2:H15单元格区域,点击格式刷,将H2:H15单元格区域格式应用于A2:A15单元格区域。

    3.使用辅助列化繁为简,简化公式

    使用辅助列除了可以提高计算效率,另外一个重要用途就是化繁为简,使用表格的物理空间换取内存空间,大大地简化公式。一般来说,使用辅助列后的公式更简单、更易懂、更易于维护。

    3.1使用辅助列轻松实现多条件查找

    在示例文件“表2-18使用辅助列多条件查找”中,如果要实现按商品名称和商品颜色进行多条件查找,常用的VLOOKUP函数无法实现,需利用数组公式,如图2-59所示。

    图2-59 使用数组公式实现多条件查找

    H3单元格的数组公式为:

    {=VLOOKUP(F2&G2,IF({1,0},B2:B10&C2:C10,D2:D10),2,0) }

    如果使用辅助列,将商品名称和商品颜色组合在一起,如图2-59的A列所示,然后用VLOOKUP使用H2单元格的公式进行查询就非常简单明了,H2单元格公式:

    =VLOOKUP(F2&G2,A1:D10,4,0)

    3.2使用辅助列轻松实现明细查询

    在财务日常工作中,有时需要用公式实现数据的明细查询功能,即将符合条件的所有记录筛选出来。如图2-60中B1:F15单元格区域为源数组表(见示例文件“表2-19使用辅助列查询明细”),现需查询出指定部分所有人员的记录。如果不用辅助列,可使用数组公式实现查询功能,H5单元格数组公式如下:

    { =INDEX(B:B,SMALL(IF(($B$2:$B$15=$H$2),ROW($2:$15),4^8),ROW(1:1)))&""}

    图2-60 使用数组公式查询明细

    然后拖动填充柄往右、往下填充公式即可。此公式比上面的例子更不好理解,但如果使用辅助列,则公式会简单得多。首先在A2单元格输入公式:

    =B2&"-"&COUNTIF($B$1:B2,B2)

    下拉填充公式,然后在H5单元格输入公式:

    =VLOOKUP($H$2&"-"&ROW()-4,$A$2:$F$15,COLUMN()-6,0)

    然后往下、往右填充公式即可,然后为了消除错误值,可将公式完善为:

    =IFERROR(VLOOKUP($H$2&"-"&ROW()-4,$A$2:$F$15, COLUMN()-6,0),"")

    如图2-61所示。

    图2-61 使用辅助列式查询明细

    另外,如果上级公司或其他部门分发的表格设计不合理,表格填列起来很麻烦又费时,而我们又无法改变表格格式,这时候怎么办?可以用辅助过渡表来进行转换,具体思路与方法与辅助列技术类似,不赘述。

    相关文章

      网友评论

          本文标题:Excel读书笔记7——使用辅助列(表)

          本文链接:https://www.haomeiwen.com/subject/skvadqtx.html