问答文章1 问答文章501 问答文章1001 问答文章1501 问答文章2001 问答文章2501 问答文章3001 问答文章3501 问答文章4001 问答文章4501 问答文章5001 问答文章5501 问答文章6001 问答文章6501 问答文章7001 问答文章7501 问答文章8001 问答文章8501 问答文章9001 问答文章9501

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

发布网友 发布时间:2022-09-16 13:38

我来回答

1个回答

热心网友 时间:2023-10-13 07:55

      工作中使用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:选中数据表格任一单元格,点击【插入】选项卡—“表格”组的“数据透视表”按钮,弹出创建数据透视表对话框(见图2-52)。

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

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

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

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

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

Step8:选择数据透视表的任一单元格,点击右键,点击“数据透视表选项”,在弹出的“数据透视表选项”对话框中勾选“合并且居中排列带标签的单元格”(见图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所示。

另外,如果上级公司或其他部门分发的表格设计不合理,表格填列起来很麻烦又费时,而我们又无法改变表格格式,这时候怎么办?可以用辅助过渡表来进行转换,具体思路与方法与辅助列技术类似,不赘述。
声明声明:本网页内容为用户发布,旨在传播知识,不代表本网认同其观点,若有侵权等问题请及时与本网联系,我们将在第一时间删除处理。E-MAIL:11247931@qq.com
“晚来天欲雪,能饮一杯无?”是什么意思_出处及原文翻译_学习力 胸肌中部怎么练 血沉高是怎么回事,怎样才能降低啊! 中海锦江城多久交房 “QMS”作为“质量管理体系”的英文缩写,其背后理论及应用如何?_百度... 我用QQ登的快手绑定了一个电话号码,电话号码不用了,现在怎么能登得进去... 桂林银行白金客户条件 桂林银行最高等级卡 iQOO 11系列曝光,预计年底正式发布! vivo X90先别买?iQOO 11系列配置堪称豪华 a time for与the time of区别 a. time. for. 和the. time. of什么意思,各位大神, at the time of是什么意思 the time of 是什么什么的时候? the time of和a time for区别? 婆婆带孙子玩掉河里死了又被人工呼吸救过来了但是没有醒好事情吗?_百度问一问 狮子座在生活中比较霸道和大气,真心喜欢狮子座的星座有哪些? 很好地走下去,真心喜欢狮子座的星座有哪些,你知道吗? 责任转质的分类 什么贷款对征信要求不高?这些事项要清楚 飞机坠机空姐赔付多少 韩国电影逆鳞结尾王祖母是被赐死了吗? 春节的老虎怎么画 用一句宣传语向秋天来南京旅游的游客推荐这座城市 落叶不扫的宣传语 我脸上又红又痒,吃了一个礼拜的抗过敏药没见好转,怎么办呢?很苦恼 皮肤过敏突然起小红疙瘩,还有点痒,用了糠酸莫米松乳膏也不见好转,该怎么办,急啊 过敏半年多不见好转 身体突然过敏 打了2针也吃药了,输液了但却不见好转还是出现红色疙瘩很痒 怎么办 脸部皮肤过敏怎么办?几天不见好转? 骁龙845相当于天玑多少 撒下一颗种子,让它自己开花 ---《走近二十四节气》教学反思 惊蛰节气习俗 碧生源”在CCTV10上广告了,是不是说明可以相信它?我想用它治疗便秘。 碧生源减肥茶不好为什么还能销售这么好 怀孕刚两个月,每天大便后肛门都出血,该怎么办 怀孕两个月了!上大便带了血是流产吗? 怀孕两个月大便老有血怎么回事 冬天宝宝小腿烫伤不穿裤子又冷?有什么好办法? 苹果6 plus手机屏碎了,怎么办? 苹果6 Plus屏幕碎了怎么办 碎屏补救方法 微信小程序看图猜成语一知半解答案 黑色素瘤早期症状有什么 微信小程序成语猜猜看501关的答案 微信小程序成语猜猜看501关怎么过呀? 我微信小程序闯到501关,成语是一知半解但是可选答案全是英文小写字母 驼乳粉的功能功效 驼乳粉的功效与作用 驼乳粉的功效与价用 到南七里站怎么坐公交车,最快需要多久