![](https://img-blog.csdnimg.cn/2019100810283053.png?x-oss-process=image/resize,m_fixed,h_224,w_224)
Excel应用
文章平均质量分 63
宋哥
哥一个学通信工程的,阴错阳差的搞起了软件,计算机知识基本来自于自学。总结记录工作中碰到的问题及解决办法是一个良好的习惯,过去我用笔,现在用键盘并分享到网上,让更多人看到,自己也方便查找。
博客的名字“驽马十驾,才定不舍。”取自荀子的“劝学篇”,记录并分享工作中的点点滴滴,积少成多,虽然不是什么人才,但只要孜孜不倦,也能做出一点成绩。
展开
-
如何在透视表中同时显示客户编码和客户名称
作者:iamlaosong同事做了一个数据透视表,透视表是关于客户的统计数据,客户编码和名称是一一对应的,要求在行标签中同时显示出来,透视表字段配置和做出来的效果如下:这不是他想要的结果,他希望编码和名称放在一行显示,统计数据应该也在同一行内。我知道他想要得到的结果,要做到需要做如下两点:1、取消汇总 2、以经典的方式显示原创 2022-01-14 15:22:11 · 3102 阅读 · 0 评论 -
EDATE和EOMONTH函数的用法和区别
作者:iamlaosong这两个函数统计时经常用到,功能有点类似,语法也差不多,容易搞混。语法如下:EDate(start_date,months)EOMonth(start_date,months)参数说明:start_date:表示起始日期的日期。months:表示start_date之前或之后的月份数。1、两个函数的功能介绍EDATE是以当前日期start_date为基准,向前(负数)或向后推迟几个月(第二个参数),比如当前日期是12月6日,向前一个月就是11月6日。即:E原创 2021-12-06 11:38:08 · 2842 阅读 · 0 评论 -
Excel用SQL合并多个工作表
作者:iamlaosong需求是统计全省各地市全年12个月的累计销售情况,原始数据是每个月一张明细表。简单的做法是合并12个月数据,做一个数据透视表就完成了。但同事不想合并12个月数据,因为那样每次数据变动都要合并一次。后来的做法是每个月做一个数据透视表(做好一个月后复制一份,改下数据源就可以完成另一个月)。然后再将已有月份的数据透视表中的数据求和。其实可以用SQL语句合并12各月份的数据(没有数据的月份做个带表头的空表),然后对这个合并的表做个数据透视表统计就可以了。这样每次数据变动时,只要刷新一原创 2021-04-07 10:08:34 · 3599 阅读 · 0 评论 -
Excel中VLOOKUP跨文件查找
作者:iamlaosong同事在使用vlookup函数时,是跨文件查找,但是却报莫名其妙的错,导致公式无法完成。错误提示如下:接手过来看了一下,生成的公式如下:=VLOOKUP(B2,[法院热敏打印20201203.xls]热敏打印!$B:$C,2,0)似乎没啥问题,而且文件名和工作表名称也是自动生成出来的,不存在拼写错误的问题。仔细检查首先发现两个文件查找列数据类型不一致,一个是数值型,一个是文本型。改成一致后问题解决。而且,当查找的文件打开是,公式如上面显示,如果关闭文件,则原创 2020-12-09 14:09:45 · 10529 阅读 · 0 评论 -
数据有效性跨表引用------不是问题的问题
作者:iamlaosong同事发来一个Excel文件,说是下拉菜单看不见,我试了一下,没有问题,就问他用的是什么版本,他说是2007版,我估计是版本低了。打开数据有效性定义,看到定义的序列是另一张表的内容(这张表被隐藏了),如下图:早期版本数据有效性是不支持跨工作表引用的,看来07版就不支持。那时跨工作表引用都是用公式或者定义名称,即: 1、使用公式 如我要在sheet2的单元格中设置有效性,序列的来源为sheet1的A2:A50区域,则在sheet2的有效性设置中选择序列,...原创 2020-09-18 11:15:27 · 3031 阅读 · 0 评论 -
写错countif函数的参数顺序引出的思考
作者:iamlaosong同事在用countif函数对比两列数据时发现一个奇怪的现象。先说一下这两列数据是邮件号码,内容基本相同,顺序一致,但其中一列少了几个。同事就想用countif函数计算本号码在另一列中的数量。如果存在,结果是1,反之结果为0,结果发现,在出现一个0之后,后面的全都是0,即便这个号码存在,如下图:仔细研究才发现,是countif函数两个参数写反了。正确的写法应该是“范围”在前,“条件”在后,如下图:只是第一种写法没有报错,还有一个计算结果。按照函数参数的理解,第一个原创 2020-08-12 09:39:49 · 606 阅读 · 0 评论 -
学习掌握Excel程度的层级划分
每个人的简历上,都有一句“精通Excel”,而根据经验,大多数人,功力一般在一二层之间。至于心法高处,用Excel打游戏或者绘画者,如下,达5、6层,可能是豪侠巨擘,此类不致于作为精通Excel的参考系。01 初级多数人在此列技能:了解简单公式、常用图表、可设计带有简单统计功能的图表;特征:仅限于简单操作,谈不上熟练。02 中级 如华山二老技能:·掌握常用函数如多重I...原创 2020-04-23 11:01:07 · 7872 阅读 · 0 评论 -
VLOOKUP函数最后一个参数导致的问题
作者:iamlaosong今天同事问了我一个vlookup函数的问题。他在使用这个函数时发现明明有值却显示#N/A。公式是复制的,只有一行没有结果,其它都有结果,不存在公式错误或者值不对的问题,如下图所示:我们知道,VLOOKUP第4参数(最后一个参数)为TRUE或忽略时是非精确匹配,为FALSE或0时是精确匹配,同事没有写第4个参数,所以结果是非精确匹配。有人说最后一个参数用TRUE...原创 2020-04-22 11:50:31 · 5762 阅读 · 0 评论 -
数据透视表的几个技巧
作者:iamlaosongExcel的数据透视表经常用来进行统计分析。日常工作中常常是做好模板,通过更换原始数据的方式得到相应的统计结果,从而做到一劳永逸。数据源变更后,刷新透视表就可以得到新的统计结果。右击透视表,点击弹出菜单中的刷新即可:1、数据源正常情况下,生成数据透视表的时候,数据源是源表的一个范围,即:邮件!$A$2:$Q$12113,实际工作中数据源的行数是变化的,这样...原创 2020-04-07 11:14:55 · 3866 阅读 · 0 评论 -
Excel如何实现两列数据的对比
作者:iamlaosong新冠病毒疫情基本结束啦,我们也全面恢复正常上班了,虽然国外疫情正如火如荼。现有两张工作表需要对比,一张工作表是客户表,另一张是某月下单客户表,想通过对比确定:哪些老客户下单了,该月有哪些是新客户。方法很简单,按客户代码对客户表中每个代码到下单表中查找,找到即有下单。同样,按客户代码对下单表中每个代码到客户表中查找,找不到即为新客户。1、用vlookup函数...原创 2020-04-22 16:27:59 · 2393 阅读 · 0 评论 -
【VBA研究】利用ADO让普通人用excel读取oracle数据库表的通用办法
作者:iamlaosongExcel通过ADO方式连接到Oracle并操作Oracle给那些编程能力不强的人带来很大的福音,结合着Excel的数据处理与图表制作,就能很轻松地处理一些常规工作。日常工作中需要查询各种数据,而且不断变化,处理这些数据的人不是技术人员,不会连接数据库自己查询,通过下面的办法就可以让技术人员编辑好包含查询语句的excel文件,让管理人员自己输入条件取数了。原创 2013-01-04 17:34:07 · 15140 阅读 · 5 评论 -
巧用countifs函数实现分类(区)排名
iamlaosong文1、用countif函数Excel可以用rank函数进行排名,用起来也很方便,例如:=RANK(H5,$H$2:$H$18),结果就是h5中的值在h2-h18中的排名,这个功能同样可以用countif函数实现,即:=COUNTIF($H$2:$H$18,">"&H5)+1,其含义就是统计h2-h18中大于h5的个数,个数为0,自然排第一,所以结果要加1。如下图所示:原创 2016-03-16 10:04:43 · 11641 阅读 · 1 评论 -
启用宏的安全警告提示一闪就不见了
iamlaosong文要执行Excel的宏需要启用才行,在此之前需要进行宏安全设置,设置为“启用所有宏”是危险的,比较安全的做法是设置为“禁用所有宏,并发出通知”。这种设置下,Excel含有宏时,系统会发出安全警告,点击这个警告右边的“选项”按钮决定是否启用宏。现在碰到的情况是这个安全警告提示按钮一闪就不见了(开始以为没有,后来才发现是一闪就不见了)原创 2017-04-07 09:19:05 · 3303 阅读 · 0 评论 -
巧用sumproduct和vlookup完成多种类金额分类汇总
iamlaosong文对于异常邮件在判定责任后按考核规定需要扣罚责任单位,情况不同,扣罚金额不同。现在有汇总的异常邮件明细,已经判定了责任单位和原因,现在要求各责任单位的扣罚总额。如果明细中有扣罚金额,那么汇总是很简单的事,用sumif函数和sumproduct函数均可以完成,现在是明细中只有原因,金额在一个对照表中。明细表如下图所示:要求生成下列统计结果(手机和省内两个明细表原创 2017-05-08 09:38:00 · 7607 阅读 · 3 评论 -
大数据量情况下比较两张工作表中相同或不同的邮件号
iamlaosong文有两个工作表,里面是邮件明细,每个都有20万条,要想提出两个表中重复的邮件号,怎么办好呢?如果数据量小的话,可以用countif函数,也可以用vlookup函数,可是这么大数量的数据,用这个函数,电脑很快就死翘翘了,仔细考虑,想到用SQL语句完成这个工作,试了一下,时间不长,结果就出来了。假定数据在文件“比较.xlsx”中的sheet1和sheet2 工作表中,步原创 2017-01-17 16:42:24 · 1690 阅读 · 0 评论 -
sumproduct多条件求和经典问题(乘号和逗号)剖析
iamlaosong文SUMPRODUCT函数的含义为在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。按我的理解就是两个以上的数组乘积之和。例如,公式为:=SUMPRODUCT(A2:A4, B2:B4)就相当于=A2*B2+A3*B3+A4*B4,两个区域用逗号隔开,不过用乘号也是没有问题的。似乎也只有这个函数才有这种对应元素之间先行捉对计算的功能。1、条件求和时条件区域与求...原创 2017-01-11 09:33:54 · 24196 阅读 · 2 评论 -
Excel公式的调试
iamlaosong文当我们写一个复杂的公式时,返回的结果可能不是预期的结果或者返回错误,就需要对公式进行调试,或者说是分步计算,以便发现错在哪儿。有时我们在网上看到一个复杂的公式,可是却不能理解其机理或者精华所在,往往也需要对公式进行解读,这时我们也会用到公式调试。下面就谈谈如何进行公式调试。1、利用F9键这应该是最常用的一个调试方法了。方法是在编辑框中,用鼠标选中整个公式中需要进行原创 2017-01-09 18:05:08 · 2781 阅读 · 0 评论 -
怎样提取Excel工作表中排名前几名的名称和数据
iamlaosong文工作中需要每日通报一些指标,不可能将所有单位或部门都通报出去,只能表扬前几名,警示后几名。因此,需要从一个详细的数据表中将前几名或后几名提出来(升序或降序),最后生成一个通报文本。1、提取的方法是用SQL语句,首先建立一个对这张表的查询,方法是数据选项卡—现有连接—浏览更多 或者 按快捷键Alt+D+D+D,然后选择要查询的Excel文件和文件中的的工作表,然后点击“属性原创 2017-01-06 15:19:32 · 21071 阅读 · 0 评论 -
怎样让插入Excel的图片不能移动
iamlaosong文一个工具的首页中插入一些图片和按钮,希望用户不能随便移动,方法如下:1、保护工作表工作表保护后,即使去掉选定锁定和未锁定单元格前面的勾选,按钮和图片还能选择,还可以移动,这时需要在做保护时,将“编辑对象”和“编辑方案”前面的勾选去掉,这样图片就不能动了。“编辑对象”是指工作表上画上的图形,插入的控件、图表、图像等,去掉这个勾选,对象就不能动了;原创 2017-04-18 17:30:00 · 23830 阅读 · 0 评论 -
Excel个人所得税简洁计算公式
iamlaosong文我们知道,最新的个人所得税起征点是 3500元,税率从3%到45%,分有7个等级,为提高计算速度,与不同税率相对应有7个速算扣除数,如下表所示: 级数 含税级距 不含税级距 税率 速算 (%) 扣除数 1 不超过1500元的 不超过1455元的 3 0 2 超过1500元至4500元的部分 ...原创 2016-12-19 17:19:13 · 23952 阅读 · 0 评论 -
使用Excel2007去重复功能时要注意的一个问题
作者:iamlaosongExcel2007有个去重复功能(菜单:数据----删除重复项),很有用,过去需要用VBA编程实现的功能,现在点击一下图标就行了。去重复一般是指定某列或者某几列,根据这指定的列将重复的删除。需要注意的是,这个指定的列中的值虽然相同(重复),但未指定的列却不一定相同。删除重复是保留重复项的第一行,删除后续行,因此,最后的结果就和原始数据的排列有关系,不同的排列,虽然原创 2014-09-17 10:02:32 · 1687 阅读 · 0 评论 -
将二维Excel表格变成一维
作者:iamlaosong有一个二维表格,纵向是客户名称,横向是产品名称,内容是每个客户需要的产品数量,现在需要将这个二维表变成一维表,内容为客户名称、产品名称、数量,我记得数据透视表可以完成这个功能,于是网上搜了一下,找到了想要的答案,按步骤操作没有问题,操作见百度经验:如何将二维的excel变成一维其中用到数据透视表向导,这个功能平常看不到,通过下面链接操作即可:Excel2007原创 2014-09-12 10:27:26 · 3659 阅读 · 0 评论 -
Excel单元格内容什么情况下显示为#
iamlaosng文单元格内容为日期、时间或数字等数据类内容时,如果单元格宽度不够显示全部内容,单元格就会显示为#,文本格式会不会出现这种情况呢?也会,只是影响单元格显示的不再是宽度,而是字符串长度,当字符串长度大于255时,单元格显示为#,不过,如果将单元格格式改为常规,则文本内容又显示出来了,虽然因为宽度显示不全,但不是#。PL/SQL Developer将查询结果导出成Excel文件时会将SQL语句带上,这个语句的长度一般都会超过255个字符,所以导出的Excel表格中存放SQL语句的单元格显示为原创 2016-12-14 14:38:15 · 2526 阅读 · 0 评论 -
怎样消除vlookup找不到目标时出现的#N/A
iamlaosong文先说一个应用场景吧,需要将两张统计表合并,两张统计表中的条目(比如城市名称吧)顺序可能不一致,这时可以用vlookup函数通过城市名称将另一张表的数据引入本表,但是,如果碰到另一张表中城市名称不存在,则vlookup函数会返回#N/A,过去我们都用IF加ISNA两个函数判断来取消这个#N/A,公式大致如下:=IF(ISNA(VLOOKUP($L6,$A$6:$G$21原创 2016-12-12 15:20:56 · 20628 阅读 · 0 评论 -
VBA调试利器debug.print
作者:iamlaosong百度一下,很容易找到debug.print解释和使用介绍,其实很简单,就是将代码执行结果显示在“立即窗口”中,但不影响程序执行。VBA程序调试过程中,经常需要监控变量的值,虽然MsgBox也可以完成这个功能,但用debug.print显示变量或者表达式的值,有很多优点,具体如下:1、不影响程序执行,虽然MsgBox也可以显示代码执行结果,但是每次要按确定按钮才能继续下去,...原创 2014-10-08 16:32:54 · 28614 阅读 · 1 评论 -
Excel文件减肥修复终极办法----解决Excel文件打开慢的问题
iamlaosong文Excel文件在使用过程中由于各种原因会变得越来越大,有的文件甚至存在一些问题,导致文件大,打开慢,实在让人头疼。网上有很多办法,无非是删除内容、删除对象等等,但一些隐含的东西或者变异(变坏)的东西是无法删除的,比如建立一个数据透视表,后来又删除了,那么这个透视表虽然删除了,这个对象还是存在的(用VBA代码刷新透视表不会报错)。还有一些已经损坏的Excel文件,虽然不大,原创 2016-11-08 17:30:37 · 21917 阅读 · 2 评论 -
【VBA研究】生成工资条
作者:iamlaosong1、可以将工资表拆分成可以撕开单独使用的工资条,即每个人工资数额上面加上项目名称,每个人后面加一个空行,第一次写VBA程序,主要靠录制宏,代码很粗糙,数据和代码如下:原创 2010-12-18 15:01:00 · 2352 阅读 · 1 评论 -
怎样检查单元格里的空格
iamlaosong文在做数据透视表统计时发现数据不对,原因是列标签中有些内容后面有空格(不小心输入的),因为列标签没有全选,所以没有发现,后来全选了,才发现这个空格的存在。为了定位这些有空格的单元格,在原Excel工作表中进行筛选,发现尽然筛选不出来,因为内容后面是否有空格是不影响筛选的(中间有空格肯定是能筛选出来的),所以要找这些单元格,最简单的办法就是查找,而去掉这些空格的办法就是替原创 2016-09-01 16:11:02 · 7660 阅读 · 0 评论 -
【VBA研究】VBA中日期和时间相关的计算
作者:iamlaosong1、日期转换很简单,直接用转换函数就可以了,例如:CDate("2012-12-31")2、日期之间的天数用datediff函数,注意,工作表单元格中用的函数名称是datedif,少了一个f,而且参数顺序也不一样。例如:dt = Cells(2, 1) ' 单元格内容:2013-1-1 09:35:08dd = DateDiff("D",原创 2014-01-18 22:01:09 · 52355 阅读 · 2 评论 -
Excel VBA内部函数大全
在VBA程序语言中有许多内置函数,可以帮助程序代码设计和减少代码的编写工作。一.测试函数IsNumeric(x) ‘是否为数字, 返回Boolean结果,True or FalseIsDate(x) ‘是否是日期, 返回Boolean结果,True or FalseIsEmpty(x) ‘是否为Empty, 返回Boolean结果,True or False转载 2017-06-19 17:54:02 · 13798 阅读 · 1 评论 -
打印邮政汇款单(不用编程)
作者:iamlaosong公司各支部收到的党费需要汇到公司公款账户。由于无法手机转账,必须到柜台办理,所以每次需要填写汇款单。汇款单涉及汇款人和收款人账号,收款人账户名称还特别长,居然用了32个字,填起来很麻烦,还容易出错,所以就做了个工具,打印这张汇款单,其他支部也可以用。如下图,只要改变序号,打印当前表就可以打印明细表中不同支部的汇款单,不用写任何代码。汇款单需要一张一张的打印(塞一张单据...原创 2019-07-19 11:21:11 · 720 阅读 · 0 评论 -
用ADO SQL查询Excel表只能取到65535行数据
作者:iamlaosong同事在用ADO连接Excel用SQL查询工作表的时候,发现只能查询65535行数据,超过这个行数的数据就取不到了,其SQL语句如下:select [单位] from [汇总$E:E]开始以为是连接串的驱动不对,因为不同03和07版驱动是不一样的,但连接串没有问题,下面代码可测试数据只能取65535行: strConn = "Provider=Mi...原创 2018-11-22 11:31:22 · 2086 阅读 · 2 评论 -
使用Excel2010条码控件碰到的问题及解决办法
iamlaosong文在使用Excel条码过程中碰到几个难以解决的问题,有的问题又因为没有及时发现导致浪费了很多时间和耗材,用户意见也很大。这些问题出现的原因是什么,目前还不知道,所以解决办法也只是一些变通的办法。本文最后有条码使用工具下载链接,可以查看源码学习。现将问题及解决办法总结如下:1、条码宽度和数据显示问题条码下方可以显示数据(ShowData=1),当数据字符串较长时,co...原创 2018-11-08 16:41:50 · 5730 阅读 · 1 评论 -
2018年个人所得税Excel计算公式
iamlaosong文虽然抵扣方案还没有出来,但10月份开始个税起征点提高到5000,各档税率如下:级数 征税工资 税率 速算 (%) 扣除数 1 0-3000 3 0 2 3000-12000 10 210 3 12000-25000 20 1410 4 25000-35000 ...原创 2018-10-22 16:30:59 · 4102 阅读 · 0 评论 -
怎样再次修改Excel中的条码控件
iamlaosong文1、在Excel中增加条码控件,方法是点击开发工具下面的“插入”下拉小三角,点击“其它控件”。2、再选择Microsoft BarCode 14.0(版本可能不同),光标变成“+”号,在需要条码的地方拉出矩形框,条码就出来了。3、右击条码,点击“属性”菜单(VBA工程不可见时弹出的属性框是空白,需要输入密码进入工程后属性才可正常显示),在属性中选择关联的单...原创 2018-06-01 11:47:11 · 5237 阅读 · 0 评论 -
解决Excel中VBA批量打印速度慢的问题
iamlaosong文VBA循环打印对账单时,循环一次发出一个打印命令(本应用只打印一页),导致打印速度很慢,时间都浪费在电脑与打印机握手上面了。要解决这个问题,一种方法是打印前将内容合并,另一种方法是输出到文件,然后合并打印。最好的方法当然是第一种,但有时无法在打印前合并内容,例如打印内容涉及图片、条码、二维码、套打等,那么只有采用第二种方法。为了增加灵活性,程序设置了一个输出参数pm,...原创 2018-06-05 11:22:52 · 3300 阅读 · 0 评论 -
解决Excel中VBA批量打印条码不能自动更新的问题
iamlaosong文Excel条码控件生成的条码,单个打印没有问题,也会随着关联单元格的值变化,可是VBA循环打印时,打印出来的条码总是第一个,后面不再变化,尽管单元格的值在变化。首先想到的是屏幕更新(Application.ScreenUpdating = True),发现没有用。后来想到调试过程中关联单元格后条码也没有变化,但调整一下条码的大小,条码立马变了,于是在打印前增加下面两行...原创 2018-06-04 13:16:14 · 6032 阅读 · 2 评论 -
Excel中表单控件和ACTIVEX控件主要区别
作者:iamlasong最近研究了一下Excel控件。使用时可以插入两种类型的控件,一种是表单控件(在早期版本中也称为窗体控件,英文Form Controls),另一种是ActiveX控件。表单控件只能在工作表中添加和使用(呵呵,叫表单控件,但却不在表单Form中使用,Form中使用的反而是ActiveX控件),并且只能通过设置控件格式或者指定宏来使用它;而ActiveX控件不仅可以在工作表中使用...原创 2014-08-12 17:18:02 · 51249 阅读 · 1 评论 -
怎样避免每次运行都启用宏的麻烦
iamlaosong文运行VBA程序需要启用宏,每次都打开文档都要去启用一下也是很麻烦的事,如果在信任中心的宏设置中设置为“启用所有宏”倒是可以避免这样的麻烦,但是安全性就没有了,碰到宏病毒就歇菜了。用下面大方法可以避免这样的麻烦。1、数字证书生成一个数字证书,开发的时候带上数字证书,使用的地方装上这个数字证书后就不用每次启用宏了。参见:【VBA研究】如何使用VBA项目的数字证书...原创 2018-01-11 17:25:04 · 2123 阅读 · 0 评论 -
如何批量把excel中已显示的科学计数法取消
作者:iamlaosong把一文本文档复制到EXCEL中时,其中一列数字全部变成科学计数法,这些数其实是条码号,不需进行运算,如何可以取消科学计算法,将数字显示成原来的样子呢?一般方法如下:1、你先将单元格格式设置成文本格式2、转成文本后,你将那单元格双击一下3、或者在数字前面加符号“'”(没有引号,注意是英文输入法下的)不过对于数据量比较大的场合,这样一个一个的处原创 2014-11-11 17:38:04 · 69977 阅读 · 0 评论