怎么在excel中构建辅助列

怎么在excel中构建辅助列,第1张

怎么在excel中构建辅助列?Excel是微软办公套装软件的一个重要的组成部分,它可以进行各种数据的处理、统计分析和辅助决策 *** 作建立辅助列的目的是为了简化函数公式,不至于在单元格输入很复杂的公式,下面我就教你excel中构建辅助列的方法。

excel中构建辅助列的方法如下:

①在原始数据中有一些时间,我们需要对其进行求和,计算出总时间。利用sum公式计算。

②然而,却得不到正确的计算结果,显示为0,那么怎么解决呢?

③创建一个辅助列,输入函数公式:=TIME(LEFT(A4,2),MID(A4,4,2),RIGHT(A4,2))

④回车得到结果,然后向下填充。

⑤选中这些单元格,设置单元格格式,选为时间类型,选择时分秒。

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

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

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

比如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)

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

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

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

选定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-17 使用辅助列快速合并同类项的单元格”,表格如图2-51所示。

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

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

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

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

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

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

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

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

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

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

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

在财务日常工作中,有时需要用公式实现数据的明细查询功能,即将符合条件的所有记录筛选出来。如图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)))&""}

然后拖动填充柄往右、往下填充公式即可。此公式比上面的例子更不好理解,但如果使用辅助列,则公式会简单得多。首先在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所示。

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


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/bake/11913158.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-05-19
下一篇 2023-05-19

发表评论

登录后才能评论

评论列表(0条)

保存