驽马十驾 才定不舍

作者：iamlaosong同事做了一个数据透视表，透视表是关于客户的统计数据，客户编码和名称是一一对应的，要求在行标签中同时显示出来，透视表字段配置和做出来的效果如下：这不是他想要的结果，他希望编码和名称放在一行显示，统计数据应该也在同一行内。我知道他想要得到的结果，要做到需要做如下两点：1、取消汇总 2、以经典的方式显示

作者：iamlaosong这两个函数统计时经常用到，功能有点类似，语法也差不多，容易搞混。语法如下：EDate(start_date,months)EOMonth(start_date,months)参数说明：start_date：表示起始日期的日期。months：表示start_date之前或之后的月份数。1、两个函数的功能介绍EDATE是以当前日期start_date为基准，向前（负数）或向后推迟几个月（第二个参数），比如当前日期是12月6日，向前一个月就是11月6日。即：E

作者：iamlaosong需求是统计全省各地市全年12个月的累计销售情况，原始数据是每个月一张明细表。简单的做法是合并12个月数据，做一个数据透视表就完成了。但同事不想合并12个月数据，因为那样每次数据变动都要合并一次。后来的做法是每个月做一个数据透视表（做好一个月后复制一份，改下数据源就可以完成另一个月）。然后再将已有月份的数据透视表中的数据求和。其实可以用SQL语句合并12各月份的数据（没有数据的月份做个带表头的空表），然后对这个合并的表做个数据透视表统计就可以了。这样每次数据变动时，只要刷新一

作者：iamlaosong同事在使用vlookup函数时，是跨文件查找，但是却报莫名其妙的错，导致公式无法完成。错误提示如下：接手过来看了一下，生成的公式如下：=VLOOKUP(B2,[法院热敏打印20201203.xls]热敏打印!$B:$C,2,0)似乎没啥问题，而且文件名和工作表名称也是自动生成出来的，不存在拼写错误的问题。仔细检查首先发现两个文件查找列数据类型不一致，一个是数值型，一个是文本型。改成一致后问题解决。而且，当查找的文件打开是，公式如上面显示，如果关闭文件，则

作者：iamlaosong同事发来一个Excel文件，说是下拉菜单看不见，我试了一下，没有问题，就问他用的是什么版本，他说是2007版，我估计是版本低了。打开数据有效性定义，看到定义的序列是另一张表的内容（这张表被隐藏了），如下图：早期版本数据有效性是不支持跨工作表引用的，看来07版就不支持。那时跨工作表引用都是用公式或者定义名称，即： 1、使用公式 如我要在sheet2的单元格中设置有效性，序列的来源为sheet1的A2：A50区域，则在sheet2的有效性设置中选择序列，...

作者：iamlaosong同事在用countif函数对比两列数据时发现一个奇怪的现象。先说一下这两列数据是邮件号码，内容基本相同，顺序一致，但其中一列少了几个。同事就想用countif函数计算本号码在另一列中的数量。如果存在，结果是1，反之结果为0，结果发现，在出现一个0之后，后面的全都是0，即便这个号码存在，如下图：仔细研究才发现，是countif函数两个参数写反了。正确的写法应该是“范围”在前，“条件”在后，如下图：只是第一种写法没有报错，还有一个计算结果。按照函数参数的理解，第一个

每个人的简历上，都有一句“精通Excel”，而根据经验，大多数人，功力一般在一二层之间。至于心法高处，用Excel打游戏或者绘画者，如下，达5、6层，可能是豪侠巨擘，此类不致于作为精通Excel的参考系。01 初级多数人在此列技能：了解简单公式、常用图表、可设计带有简单统计功能的图表；特征：仅限于简单操作，谈不上熟练。02 中级 如华山二老技能：·掌握常用函数如多重I...

作者：iamlaosong今天同事问了我一个vlookup函数的问题。他在使用这个函数时发现明明有值却显示#N/A。公式是复制的，只有一行没有结果，其它都有结果，不存在公式错误或者值不对的问题，如下图所示：我们知道，VLOOKUP第4参数（最后一个参数）为TRUE或忽略时是非精确匹配，为FALSE或0时是精确匹配，同事没有写第4个参数，所以结果是非精确匹配。有人说最后一个参数用TRUE...

作者：iamlaosongExcel的数据透视表经常用来进行统计分析。日常工作中常常是做好模板，通过更换原始数据的方式得到相应的统计结果，从而做到一劳永逸。数据源变更后，刷新透视表就可以得到新的统计结果。右击透视表，点击弹出菜单中的刷新即可：1、数据源正常情况下，生成数据透视表的时候，数据源是源表的一个范围，即：邮件!$A$2:$Q$12113，实际工作中数据源的行数是变化的，这样...

作者：iamlaosong新冠病毒疫情基本结束啦，我们也全面恢复正常上班了，虽然国外疫情正如火如荼。现有两张工作表需要对比，一张工作表是客户表，另一张是某月下单客户表，想通过对比确定：哪些老客户下单了，该月有哪些是新客户。方法很简单，按客户代码对客户表中每个代码到下单表中查找，找到即有下单。同样，按客户代码对下单表中每个代码到客户表中查找，找不到即为新客户。1、用vlookup函数...

作者：iamlaosongExcel通过ADO方式连接到Oracle并操作Oracle给那些编程能力不强的人带来很大的福音，结合着Excel的数据处理与图表制作，就能很轻松地处理一些常规工作。日常工作中需要查询各种数据，而且不断变化，处理这些数据的人不是技术人员，不会连接数据库自己查询，通过下面的办法就可以让技术人员编辑好包含查询语句的excel文件，让管理人员自己输入条件取数了。

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。如下图所示：

iamlaosong文要执行Excel的宏需要启用才行，在此之前需要进行宏安全设置，设置为“启用所有宏”是危险的，比较安全的做法是设置为“禁用所有宏，并发出通知”。这种设置下，Excel含有宏时，系统会发出安全警告，点击这个警告右边的“选项”按钮决定是否启用宏。现在碰到的情况是这个安全警告提示按钮一闪就不见了（开始以为没有，后来才发现是一闪就不见了）

iamlaosong文对于异常邮件在判定责任后按考核规定需要扣罚责任单位，情况不同，扣罚金额不同。现在有汇总的异常邮件明细，已经判定了责任单位和原因，现在要求各责任单位的扣罚总额。如果明细中有扣罚金额，那么汇总是很简单的事，用sumif函数和sumproduct函数均可以完成，现在是明细中只有原因，金额在一个对照表中。明细表如下图所示：要求生成下列统计结果（手机和省内两个明细表

iamlaosong文有两个工作表，里面是邮件明细，每个都有20万条，要想提出两个表中重复的邮件号，怎么办好呢？如果数据量小的话，可以用countif函数，也可以用vlookup函数，可是这么大数量的数据，用这个函数，电脑很快就死翘翘了，仔细考虑，想到用SQL语句完成这个工作，试了一下，时间不长，结果就出来了。假定数据在文件“比较.xlsx”中的sheet1和sheet2 工作表中，步

iamlaosong文SUMPRODUCT函数的含义为在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。按我的理解就是两个以上的数组乘积之和。例如，公式为：=SUMPRODUCT(A2:A4, B2:B4)就相当于=A2*B2+A3*B3+A4*B4，两个区域用逗号隔开，不过用乘号也是没有问题的。似乎也只有这个函数才有这种对应元素之间先行捉对计算的功能。1、条件求和时条件区域与求...

iamlaosong文当我们写一个复杂的公式时，返回的结果可能不是预期的结果或者返回错误，就需要对公式进行调试，或者说是分步计算，以便发现错在哪儿。有时我们在网上看到一个复杂的公式，可是却不能理解其机理或者精华所在，往往也需要对公式进行解读，这时我们也会用到公式调试。下面就谈谈如何进行公式调试。1、利用F9键这应该是最常用的一个调试方法了。方法是在编辑框中，用鼠标选中整个公式中需要进行

iamlaosong文工作中需要每日通报一些指标，不可能将所有单位或部门都通报出去，只能表扬前几名，警示后几名。因此，需要从一个详细的数据表中将前几名或后几名提出来（升序或降序），最后生成一个通报文本。1、提取的方法是用SQL语句，首先建立一个对这张表的查询，方法是数据选项卡—现有连接—浏览更多 或者 按快捷键Alt+D+D+D，然后选择要查询的Excel文件和文件中的的工作表，然后点击“属性

iamlaosong文一个工具的首页中插入一些图片和按钮，希望用户不能随便移动，方法如下：1、保护工作表工作表保护后，即使去掉选定锁定和未锁定单元格前面的勾选，按钮和图片还能选择，还可以移动，这时需要在做保护时，将“编辑对象”和“编辑方案”前面的勾选去掉，这样图片就不能动了。“编辑对象”是指工作表上画上的图形，插入的控件、图表、图像等，去掉这个勾选，对象就不能动了；

iamlaosong文我们知道，最新的个人所得税起征点是 3500元，税率从3%到45%，分有7个等级，为提高计算速度，与不同税率相对应有7个速算扣除数，如下表所示： 级数 含税级距 不含税级距 税率 速算 （％） 扣除数 1 不超过1500元的 不超过1455元的 3 0 2 超过1500元至4500元的部分 ...

作者：iamlaosongExcel2007有个去重复功能（菜单：数据----删除重复项），很有用，过去需要用VBA编程实现的功能，现在点击一下图标就行了。去重复一般是指定某列或者某几列，根据这指定的列将重复的删除。需要注意的是，这个指定的列中的值虽然相同（重复），但未指定的列却不一定相同。删除重复是保留重复项的第一行，删除后续行，因此，最后的结果就和原始数据的排列有关系，不同的排列，虽然

作者：iamlaosong有一个二维表格，纵向是客户名称，横向是产品名称，内容是每个客户需要的产品数量，现在需要将这个二维表变成一维表，内容为客户名称、产品名称、数量，我记得数据透视表可以完成这个功能，于是网上搜了一下，找到了想要的答案，按步骤操作没有问题，操作见百度经验：如何将二维的excel变成一维其中用到数据透视表向导，这个功能平常看不到，通过下面链接操作即可：Excel2007

iamlaosng文单元格内容为日期、时间或数字等数据类内容时，如果单元格宽度不够显示全部内容，单元格就会显示为#，文本格式会不会出现这种情况呢？也会，只是影响单元格显示的不再是宽度，而是字符串长度，当字符串长度大于255时，单元格显示为#，不过，如果将单元格格式改为常规，则文本内容又显示出来了，虽然因为宽度显示不全，但不是#。PL/SQL Developer将查询结果导出成Excel文件时会将SQL语句带上，这个语句的长度一般都会超过255个字符，所以导出的Excel表格中存放SQL语句的单元格显示为

iamlaosong文先说一个应用场景吧，需要将两张统计表合并，两张统计表中的条目（比如城市名称吧）顺序可能不一致，这时可以用vlookup函数通过城市名称将另一张表的数据引入本表，但是，如果碰到另一张表中城市名称不存在，则vlookup函数会返回#N/A，过去我们都用IF加ISNA两个函数判断来取消这个#N/A，公式大致如下：=IF(ISNA(VLOOKUP($L6,$A$6:$G$21

作者：iamlaosong百度一下，很容易找到debug.print解释和使用介绍，其实很简单，就是将代码执行结果显示在“立即窗口”中，但不影响程序执行。VBA程序调试过程中，经常需要监控变量的值，虽然MsgBox也可以完成这个功能，但用debug.print显示变量或者表达式的值，有很多优点，具体如下：1、不影响程序执行，虽然MsgBox也可以显示代码执行结果，但是每次要按确定按钮才能继续下去，...

iamlaosong文Excel文件在使用过程中由于各种原因会变得越来越大，有的文件甚至存在一些问题，导致文件大，打开慢，实在让人头疼。网上有很多办法，无非是删除内容、删除对象等等，但一些隐含的东西或者变异（变坏）的东西是无法删除的，比如建立一个数据透视表，后来又删除了，那么这个透视表虽然删除了，这个对象还是存在的（用VBA代码刷新透视表不会报错）。还有一些已经损坏的Excel文件，虽然不大，

作者：iamlaosong1、可以将工资表拆分成可以撕开单独使用的工资条，即每个人工资数额上面加上项目名称，每个人后面加一个空行，第一次写VBA程序，主要靠录制宏，代码很粗糙，数据和代码如下：

iamlaosong文在做数据透视表统计时发现数据不对，原因是列标签中有些内容后面有空格（不小心输入的），因为列标签没有全选，所以没有发现，后来全选了，才发现这个空格的存在。为了定位这些有空格的单元格，在原Excel工作表中进行筛选，发现尽然筛选不出来，因为内容后面是否有空格是不影响筛选的（中间有空格肯定是能筛选出来的），所以要找这些单元格，最简单的办法就是查找，而去掉这些空格的办法就是替

作者：iamlaosong1、日期转换很简单，直接用转换函数就可以了，例如：CDate("2012-12-31")2、日期之间的天数用datediff函数，注意，工作表单元格中用的函数名称是datedif，少了一个f，而且参数顺序也不一样。例如：dt = Cells(2, 1)     ' 单元格内容：2013-1-1 09:35:08dd = DateDiff("D",

在VBA程序语言中有许多内置函数，可以帮助程序代码设计和减少代码的编写工作。一．测试函数IsNumeric(x) ‘是否为数字, 返回Boolean结果，True or FalseIsDate(x) ‘是否是日期, 返回Boolean结果，True or FalseIsEmpty（x） ‘是否为Empty, 返回Boolean结果，True or False

作者：iamlaosong公司各支部收到的党费需要汇到公司公款账户。由于无法手机转账，必须到柜台办理，所以每次需要填写汇款单。汇款单涉及汇款人和收款人账号，收款人账户名称还特别长，居然用了32个字，填起来很麻烦，还容易出错，所以就做了个工具，打印这张汇款单，其他支部也可以用。如下图，只要改变序号，打印当前表就可以打印明细表中不同支部的汇款单，不用写任何代码。汇款单需要一张一张的打印（塞一张单据...

作者：iamlaosong同事在用ADO连接Excel用SQL查询工作表的时候，发现只能查询65535行数据，超过这个行数的数据就取不到了，其SQL语句如下：select  [单位] from [汇总$E:E]开始以为是连接串的驱动不对，因为不同03和07版驱动是不一样的，但连接串没有问题，下面代码可测试数据只能取65535行： strConn = "Provider=Mi...

iamlaosong文在使用Excel条码过程中碰到几个难以解决的问题，有的问题又因为没有及时发现导致浪费了很多时间和耗材，用户意见也很大。这些问题出现的原因是什么，目前还不知道，所以解决办法也只是一些变通的办法。本文最后有条码使用工具下载链接，可以查看源码学习。现将问题及解决办法总结如下：1、条码宽度和数据显示问题条码下方可以显示数据（ShowData=1），当数据字符串较长时，co...

iamlaosong文虽然抵扣方案还没有出来，但10月份开始个税起征点提高到5000，各档税率如下：级数 征税工资 税率 速算 （％） 扣除数 1 0-3000 3 0 2 3000-12000 10 210 3 12000-25000 20 1410 4 25000-35000 ...

iamlaosong文1、在Excel中增加条码控件，方法是点击开发工具下面的“插入”下拉小三角，点击“其它控件”。2、再选择Microsoft BarCode 14.0（版本可能不同），光标变成“+”号，在需要条码的地方拉出矩形框，条码就出来了。3、右击条码，点击“属性”菜单（VBA工程不可见时弹出的属性框是空白，需要输入密码进入工程后属性才可正常显示），在属性中选择关联的单...

iamlaosong文VBA循环打印对账单时，循环一次发出一个打印命令（本应用只打印一页），导致打印速度很慢，时间都浪费在电脑与打印机握手上面了。要解决这个问题，一种方法是打印前将内容合并，另一种方法是输出到文件，然后合并打印。最好的方法当然是第一种，但有时无法在打印前合并内容，例如打印内容涉及图片、条码、二维码、套打等，那么只有采用第二种方法。为了增加灵活性，程序设置了一个输出参数pm，...

iamlaosong文Excel条码控件生成的条码，单个打印没有问题，也会随着关联单元格的值变化，可是VBA循环打印时，打印出来的条码总是第一个，后面不再变化，尽管单元格的值在变化。首先想到的是屏幕更新（Application.ScreenUpdating = True），发现没有用。后来想到调试过程中关联单元格后条码也没有变化，但调整一下条码的大小，条码立马变了，于是在打印前增加下面两行...

作者：iamlasong最近研究了一下Excel控件。使用时可以插入两种类型的控件，一种是表单控件（在早期版本中也称为窗体控件，英文Form Controls），另一种是ActiveX控件。表单控件只能在工作表中添加和使用（呵呵，叫表单控件，但却不在表单Form中使用，Form中使用的反而是ActiveX控件），并且只能通过设置控件格式或者指定宏来使用它；而ActiveX控件不仅可以在工作表中使用...

iamlaosong文运行VBA程序需要启用宏，每次都打开文档都要去启用一下也是很麻烦的事，如果在信任中心的宏设置中设置为“启用所有宏”倒是可以避免这样的麻烦，但是安全性就没有了，碰到宏病毒就歇菜了。用下面大方法可以避免这样的麻烦。1、数字证书生成一个数字证书，开发的时候带上数字证书，使用的地方装上这个数字证书后就不用每次启用宏了。参见：【VBA研究】如何使用VBA项目的数字证书...

作者：iamlaosong把一文本文档复制到EXCEL中时，其中一列数字全部变成科学计数法，这些数其实是条码号，不需进行运算，如何可以取消科学计算法，将数字显示成原来的样子呢？一般方法如下：1、你先将单元格格式设置成文本格式2、转成文本后，你将那单元格双击一下3、或者在数字前面加符号“'”（没有引号，注意是英文输入法下的）不过对于数据量比较大的场合，这样一个一个的处