Excel技巧及VBA编程(Excel_VBA)
文章平均质量分 68
预见未来to50
不忘初心,方得始终!
展开
-
调试经验——Excel散点图表示花形数据
本示例数据来自Coursera深度学习,可参考:https://blog.csdn.net/hpdlzu80100/article/details/113115439前言:散点图比较常用,但是类似下图这种比较复杂的散点图第一次绘制,感觉比较新鲜。散点图:遗留问题:如何使用Excel可视化带有彩色标签的散点图,如下图所示:数据:1.20E+00 3.58E+00 0 6.94E-01 2.89E+00 0 4.75E-01 2.48E+00原创 2021-01-25 17:14:35 · 508 阅读 · 0 评论 -
调试经验——Excel中去除单元格内的换行符(Chr(10))的四种方法
问题描述:客户提供的Excel文档格式中,同一属性值,有的单元格有换行符,有的又没有,造成了属性值列表(LOV, List of Value)中存在冗余,给数据分析造成了干扰。所以,需要进行数据清洗,即把这些换行符替换成空字符("")。解决方法:方法一:使用Excel内置函数Clean()方法二:使用文本替换功能,换行符的输入需要使用Ctrl+j方法三:使用E...原创 2020-02-29 23:54:30 · 4634 阅读 · 0 评论 -
调试经验——Excel函数连乘函数(PRODUCT)的应用
问题:解答:其中,分子部分的计算使用了连乘函数PRODUCT。Function DescriptionThe Excel PRODUCT function returns the product (multiplication) of a supplied set of numerical values.The syntax of the f...原创 2019-10-15 11:42:49 · 1978 阅读 · 0 评论 -
Excel数据分析与业务建模_第一章_名称(Range names)
Wayne L. Winston的Excel Data Analysis and Business Modeling(Excel 数据分析与业务建模)一书,早在2010年就已经拜读过一次了。时隔7年,恰逢工作对Excel技能的要求更高,有必要重新梳理一遍。相信必有回报。1. 名称的创建方法a. 选择要命名的单元格,在名称框中直接输入一个名称(entering a ran原创 2017-01-14 23:52:19 · 1979 阅读 · 0 评论 -
Excel数据分析与业务建模_第二章_查找函数VLOOKUP及HLOOKUP(语法及应用实例)
说句实在的,如果不使用查找函数的话,现在手头上的工作基本上都没法完成。在数据量动辄几十万行的多个数据表间整理数据,基本上就靠VLOOKUP函数了。1. LOOKUP函数的语法构成:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]),共4个参数。lookup_value类似于SQL查询时的查询条件或主原创 2017-01-15 23:50:25 · 1449 阅读 · 0 评论 -
Excel数据分析与业务建模_第三章_引用函数INDEX(语法详解及应用实例)
INDEX函数可以返回指定区域(Array)内特定行号、列号所对应的单元格的内容。常与MATCH函数配套使用。Syntax of the INDEX functionThe INDEX function enables you to return the entry in any row and column within an array of numbers. The most原创 2017-01-18 21:37:07 · 1192 阅读 · 0 评论 -
Excel数据分析与业务建模_第四章_匹配函数MATCH(语法详解及应用实例)
如果有一天,EXCEL中没有了LOOKUP函数,怎么办?答案是就靠MATCH和INDEX两兄弟了。MATCH函数可返回指定区域内指定内容所在的行号(纵向区域)或列号(横向区域)。Suppose you have a worksheet with 5,000 rows containing 5,000 names. You need to find the name John Doe, wh原创 2017-01-18 22:53:43 · 1946 阅读 · 0 评论 -
Excel VBA高效办公应用-第八章-商品销售决策与分析-Part1 (商品销售方式决策)
代码如下:Public Sub 商品销售方式()'定义用于循环的整型变量Dim i As Integer '选择“商品销售方式决策”工作表 Sheets("商品销售方式决策").Select '使用For……Next循环语句 For i = 2 To 7 '计算“自销毛利润”值 Cells(10, i) = (Cells(3,原创 2017-06-15 22:07:52 · 596 阅读 · 0 评论 -
Excel VBA高效办公应用-第八章-商品销售决策与分析-Part2 (商品分期付款决策)
今日彩蛋:模拟运算表(Data Table)"模拟运算表是一个单元格区域,它可显示一个或多个公式中替换不同值时的结果。有两种类型的模拟运算表:单输入模拟运算表和双输入模拟运算表。单输入模拟运算表中,用户可以对一个变量键入不同的值从而查看它对一个或多个公式的影响。双输入模拟运算表中,用户对两个变量输入不同值,而查看它对一个公式的影响"知道为什么以前没用过这玩意了,其实可以通过拖动复制原创 2017-06-16 00:18:08 · 791 阅读 · 0 评论 -
Excel VBA高效办公应用-第八章-商品销售决策与分析-Part3 (商品进货量决策)
这个示例是比较复杂的,所以,示例也没用通过编写VBA代码来实现,而是利用EXCEL规划求解工具来完成的。注意事项:选项中默认勾选了“忽略整数约束”,在本例中必须取消选择,否则会规划出小数结果。原创 2017-06-16 00:49:27 · 632 阅读 · 0 评论 -
Excel VBA高效办公应用-第九章-VBA文秘办公技巧-Part1 (每日行程安排提醒)
首先,这是个很有趣的例子,可以考虑在日常工作中提醒个人事项。代码如下:Option ExplicitPrivate Sub CommandButton1_Click()Dim mop As Boolean '定义一个布尔变量mop = True '令其等于TrueSelect Case mopCase OptionButton1 = mop '选中第一个,则原创 2017-06-18 23:18:08 · 1182 阅读 · 0 评论 -
Excel VBA高效办公应用-第九章-VBA文秘办公技巧-Part2(查找同名同姓的员工)
改写了一下示例中的代码,6行变一行。代码如下:Option ExplicitSub tongm()Application.ScreenUpdating = False '关闭执行程序时发生的屏幕更新现象,加快运行速度Dim i As Integer, Inum As IntegerDim k As Integer '定义变量k为"同名员工"工作表的行变量 k原创 2017-06-18 23:37:41 · 648 阅读 · 0 评论 -
Excel VBA高效办公应用-第十章-高效处理学员资料-Part1 (成绩排名)
初看,不理解为什么排名也要用VBA。待我继续研究。十分钟后,看出一点名目了:本示例的意义在于,展示了RANK()函数的实现方法。也就是说,如果你不知道有个排序用的Rank函数的话,你可以重用以下代码进行排序。不过,不能忘了“Don't invent the wheel”这句编程格言。Option ExplicitSub Getsum()Application.Scree原创 2017-06-19 00:42:57 · 783 阅读 · 0 评论 -
Excel VBA高效办公应用-第十章-高效处理学员资料-Part2 (考勤记录表)
这个示例的意义在于,当数据量较大时,通过在用户窗体中提供筛选条件,帮助用户快速获取期望数据。但应为使用多重筛选也能实现同样功能,所以,这里的做法有点鸡肋啊。Option ExplicitPrivate Sub ComboBox1_Change() showall '每次组合框变化都会调用函数showallEnd SubPrivate Sub Combo原创 2017-06-19 01:04:30 · 743 阅读 · 0 评论 -
Excel排名函数PERCENTRANK计算逻辑
给出一列数,如何计算某一数字在其中的排名百分比(例如,某考生成绩比百分之多少的考生成绩高)?这时可考虑使用Excel的percentrank函数。如,考生成绩表如下:排名后的结果如下:是不是挺酷?以上不是本帖子的重点,重点在于,percentrank的计算逻辑是什么?以525原创 2017-06-07 19:11:21 · 13631 阅读 · 0 评论 -
Excel技能——批量生成excel的工作表名称目录链接
在工作、学习过程中,有时候会碰到一些比较复杂的计算问题,如:a.以趸缴方式用10000元购买即期年金,年金支付期间为15期,利率为8%,每期支付额为多少?b.向银行申请贷款10000元,贷款利率为每期8%,还贷期间为15期,每期还贷额为多少?c.保险公司某重大疾病保险产品(保额为10万)有一条款:每月交纳保费421.2,交纳10年即可(累计交费=421.2*12*10=50554),如原创 2017-06-08 12:33:50 · 2503 阅读 · 0 评论 -
Excel VBA高效办公应用-第一章-Excel VBA简介
不管学什么东西,如果在一开始就能系统、全面的学习一番,可以为日后工作打下很好的基础。否则,东一榔头西一棒槌的,干什么事总感觉是个门外汉。既然靠这行吃饭,那就要凡事尽量表现出自己的专业性来!!!而学习,永远都是需要的,正所谓学无止境。好了,闲话少说,回到正题。1. 什么是VBA?Visual Basic for Application (VBA)是Visual Basic原创 2017-06-08 16:40:56 · 1502 阅读 · 0 评论 -
Excel VBA高效办公应用-第二章-Excel VBA编辑基础-Part1(VBA关键字)
VBA常用关键字有:AndArraryAsBinaryBooleanByrefByvalCaseCurrencyDateDimDoDoubleElseEmptyEndErrorExitFalseForFriendGetImpInputIntegerIsLenLetLockLong原创 2017-06-08 17:10:13 · 1236 阅读 · 0 评论 -
Excel VBA高效办公应用-第二章-Excel VBA编辑基础-Part3(Is, Like运算符的用法详解)
1. Is运算符: 比较两个对象的引用变量是否相同,如:Sub IsOperator() Dim A As Object Dim B As Object Dim C As Object Dim D As Object Dim E As Object Dim Check As Boolean Set A =原创 2017-06-09 00:03:30 · 2303 阅读 · 1 评论 -
Excel应用-人生格子(30*30)的绘制
“如果一个月算一个小格子,人生其实只有900个格子。在一张A4纸上画一个30X30的表格,每过一个月就涂掉一格。也许你没有想过,被量化后的人生原来如此短暂。。。把你的人生画成900个格子,然后用笔涂掉过完的,你的心有没有咯噔一下……”如果要给张人生表格加个月份的话,可以使用EDATE函数。1. 第一个单元格填写自己的出生年月(注意使用日期格式,如1980-11)2. 第一行其余列的原创 2017-06-09 01:06:16 · 10790 阅读 · 0 评论 -
Excel数据分析实例——各省人口数量及增长
数据如下:分析图表:容易看出,排名前5的人口大省为:广东、山东、河南、四川、江苏(人口多,不代表经济强哦)人口增长最快的5个省份是:北京、天津、上海、广东、浙江(都是发达地区啊)人口增长绝对数量最多的5个省份是:广东、浙江、北京、上海、河北(前4名经济都挺强)后记:本例本来是要用水晶易表来展现出以下效果,但我感觉,从数据模型角原创 2017-07-10 15:15:33 · 8268 阅读 · 3 评论 -
数据分析(入门篇)-第四章-让报告自动化(VBA)-Part1(Excel报告自动化)
VBA是一种通用的自动化语言,可以使Excel常用操作步骤自动化。VBA基本概念:对象、属性、方法、事件VBA调试技巧:立即窗口、监视窗口、本地窗口、编辑窗口Excel日报自动化原理:源数据:提取到Excel中:数据转化区:日报正文区:VBA代码:Sub initialize()'声明定义VBA语句原创 2017-07-10 22:56:45 · 9219 阅读 · 0 评论 -
Excel VBA高效办公应用-第十一章-教师员工数据管理-Part3 (排座位小程序)
有时候真是怀疑自己的智商,一个排座位算法就耗掉我一个多小时。受不了!不过,总算是搞定了。代码如下:Option ExplicitSub pai() Range("A3:C38").Sort key1:=Range("C3") '按视力排序 '增加新表在"视力表"工作表之后' Sheets.Add after:=Sheets("视力原创 2017-06-19 03:16:33 · 1022 阅读 · 0 评论 -
Excel VBA高效办公应用-第十一章-教师员工数据管理-Part2 (课表助手小程序)
这个例子也不错,挺有启发性。界面:代码:Option ExplicitDim Iweek As Integer '定义Iweek获得当前的星期数Sub Myfind()'定义Sstring获取单元格的内容,Lstr获取单元格内容的长度'定义Inum获取一天总共有多少节课,Sjie(4)获取第几节课Dim Sstring As String, Lstr As I原创 2017-06-19 01:38:46 · 792 阅读 · 0 评论 -
Excel VBA高效办公应用-第十一章-教师员工数据管理-Part1 (教师考核评测数据处理)
本示例有以下启发意义:1. 管理学方面-员工绩效考核中的互评,应该属于360度测评中的一种,在没有在线工具时,可使用Excel来完成2. 可将相对复杂的功能,作为子程序来实现,然后在其他子程序中进行调用。有利于保持代码结构的清晰。3. 第一次使用按行排序,有意思。界面:代码:Option Explicit'定义Inum为全局变量,用来接收每个人的总数原创 2017-06-19 01:23:15 · 977 阅读 · 0 评论 -
Excel VBA高效办公应用-第十二章-工资表的录入与工资表的建立-Part1 (工资表的录入)
这个例子也挺有意思,用了十几年的Excel了,也没发现这种玩法。Option ExplicitPrivate Sub Worksheet_Change(ByVal Target As Range)On Error GoTo error '设置错误陷阱 Select Case Target.Column Case 1 '自动套用格式在单元格数字前加MT0原创 2017-06-21 22:27:52 · 678 阅读 · 0 评论 -
Excel VBA高效办公应用-第十二章-工资表的录入与工资表的建立-Part2 (建立工资表)
现在很少有公司用Excel来算工资了吧,所以,这里的功能相对比较鸡肋。Option ExplicitPrivate Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) '判断当前单元格是在那行,如果是在迟到,旷工或者加班行则处理右键原创 2017-06-21 22:44:56 · 577 阅读 · 0 评论 -
Excel VBA高效办公应用-第十三章-工资条与工资查询-Part2 (工资条查询)
同样,在如今的互联网时代,以下的工资条处理方式看上去太陈旧了。不过,十多年前,我自己还真领过这种格式的纸质工资条。哎呀,又暴露年龄了不过,这个例子充分说明了Excel的强大。Option ExplicitPrivate Sub CommandButton1_Click()On Error GoTo Doerror '由于输入非法员工号码会引起错误 Si原创 2017-06-21 23:40:06 · 871 阅读 · 0 评论 -
Excel VBA高效办公应用-第十三章-工资条与工资查询-Part1 (制作工资条)
同样,在如今的互联网时代,以下的工资条处理方式看上去太陈旧了。不过,十多年前,我自己还真领过这种格式的纸质工资条。哎呀,又暴露年龄了Option Explicit'定义全局变量Sid,Sname,Sxueli以便于各个块都能访问'Sid表示员工号码,Sname表示员工姓名,Sxueli表示员工学历Public Sid As String, Sname原创 2017-06-21 23:50:43 · 1022 阅读 · 0 评论 -
Oracle调试经验-查看当前数据库的实例名
使用Excel连接Oracle数据库时,配置字符串中要提供data source这一项,实际上是要连接的Oracle实例名。那么,如果不确定自己的数据库实例名,应该怎么办呢?答案是:使用管理员权限,运行"select * from v$instance"进行查询查询结果示例:FieldValueINSTANCE_NUMBE原创 2017-07-11 23:53:38 · 5471 阅读 · 1 评论 -
Office使用经验——一键移除文章中的多余空行(分享VBA源码)
有时,需要复制网页上的部分文字,粘贴后发现有很多空行,如果手动移除空行,可能相当耗时(可能要花几分钟)。所以,搞了个模板。模板名称:PDF无效行去除模板.docm模板初始化界面:空行移除前(杂乱无序,搞不懂哪来这么多空白):一键移除后:怎么样,清爽多了吧。最后,分享VBA代码如下:Sub clearLine()''rem原创 2017-07-12 00:17:09 · 1105 阅读 · 0 评论 -
学以致用——Excel连接Oracle生成iKB报告——Part1(使用VBA从数据库提取汇总数据)
iKB程序开发出来了,用的很好,很舒服。用来管理自己的知识库是个非常好的工具,小到一个生字、单词,大到一本书,随便往里装。随意增删改查,都是因为底层使用了强大的Oracle数据库。那么,有没有办法生成一个报告,告诉我每天更新了多少词条,词条总量等汇总数据呢。这不,学习数据分析的时候,有个很好的例子出现了,即:Excel报告自动化。原示例用Excel连接Access,速度明显比Ora原创 2017-07-12 00:44:22 · 723 阅读 · 0 评论 -
学以致用——Excel连接Oracle生成iKB报告——Part2(Excel日报自动化的数据转换及用户界面)
承上篇。熬夜虽然伤身,但是效率出奇的高。原来心无旁骛的做原创 2017-07-12 02:28:34 · 801 阅读 · 0 评论 -
Excel应用-使用VBA自动绘制所有适用类型的Excel图表(代码及效果图)
深夜,没有睡意。因为,再次发现VBA的魅力。给出一组数据,如下:可以绘制多少种Excel图表呢?答案是以下70多种(Excel 2016实测):原创 2017-06-13 01:36:14 · 22499 阅读 · 6 评论 -
Excel图表-用三维柱形图展示各地GDP的对比效果
通过VBA一键生成69种Excel图表,可从众多的图表类型中最适合的一种作为重点展示方式。如,对于各城市历年GDP数据,发现了三维柱形图是个不错的展示方式。这算不算一种数据挖掘呢?后记:从这几张图中我看到了让人悲哀的数据,落后地区的GDP与发达地区的GDP的差距逐年扩大,真是富者愈富穷者愈穷的马太效应啊!!!原创 2017-07-03 01:57:30 · 3228 阅读 · 0 评论 -
Excel图表-用数据点雷达图展示各地GDP的对比效果
承上文,这次发现,数据点雷达图也是个很有意思的展示方式。有图有真相。原创 2017-07-03 02:21:01 · 1845 阅读 · 0 评论 -
Excel应用—一键生成69种Excel图表_V2.0
发现上次熬夜写的VBA代码还有一些bug,一不小心又花了近4个小时完善了一下“一键生成各种69种Excel图表”的功能。改进的地方有:1. 图表输出顺序调整为从左到右,从上到下输出(耗时最多的部分)2. 图表的标题中包含了图表类型(如,气泡图,三维堆积柱形图,三维堆积面积图等)现在,图表区看起来舒服了很多,有图为证。后记:前一篇博文说过在Spotfire进行数据可视原创 2017-07-03 01:32:51 · 3565 阅读 · 6 评论 -
Excel图表-用三维堆积柱形图展示各地GDP的对比效果
三维堆积柱形图与Spotfire中最常用的barchart很像,但前者是3D的,看起来更漂亮。原创 2017-07-03 03:20:46 · 2442 阅读 · 0 评论 -
Excel应用-自动生成字母序列号(ABCDE...AA...XFD)
Excel学到高级阶段,很多之前手动完成的工作,都想着能用更好的方法来完成。比如,如何让Excel自动填充字母序列号(ABCDE...)?横向生成公式(向右拖动鼠标进行填充A-Z):=CHAR(COLUMN(A1)+64)纵向生成公式(向下拖动鼠标进行填充A-Z):=CHAR(ROW(A1)+64)貌似问题解决。等等,再看看需求。要作为序列号,A-Z这26个字母就够原创 2017-07-03 11:30:12 · 14377 阅读 · 1 评论 -
利用Excel函数实现多重分类法与二分法的相互转换
1.多重分类法转换为二分法方法一:=IF(ISNUMBER(HLOOKUP(1,$B4:$D4,1,FALSE)),1,0)方法2:=IF(ISNUMBER(SEARCH("1",$B8)),1,0)二分法转多重分类法似乎有点难度,暂且放下。原创 2017-07-03 15:19:25 · 2267 阅读 · 0 评论