excel一列求和vba代码 |
您所在的位置:网站首页 › excel整列求和公式 › excel一列求和vba代码 |
按我多年经验,Excel可分为六层心法。而每一层的修炼掌握,都将让你拥有解决一类问题的能力;知识由易到难,能力也由弱到强。 掌握前4层,就可以应付一般性的应用场景:能解决较复杂的计算、初级数据可视化、数据初步分析与汇报等问题;对应拥有编写复杂公式、制作互动性的静态图表、灵活应用透视表的能力; 如果能练到第6层,达到融汇贯通境界,则可随心所欲,解决绝大多数的数据处理、分析、展示的问题;对应拥有处理超复杂逻辑、实时报表、工具开发等能力; 而上述这些能力将在三个方面给你带来显著的回报: 1:摆脱大规模繁琐的重复操作,节约你的生命,高效准确完成任务,1个人能干2-3+个人活 2:高效处理复杂计算,能别人所不能,做出好作品、好报告,打动你的观众或者领导 3:开发功能性工具,改变数据处理的生产模式,变不可能为可能,创造新价值 第一层:快捷键与基本操作-快速完成数据区域的处理 1.1数据区域的快捷键 使用快捷键处理一块数据区域,更简单高效,还能避免鼠标手的伤害;常规的快捷键操作就不说了,这里我精选了小伙伴们可能不知道,但又很好用的快捷键: 1.1.1、一键筛选数据区域 Ctrl+Shift+L:对一个数据域筛选,再按一次取消筛选 Alt+↓:对筛选后的字段打开下拉菜单 ↓:向下框中选项 Space:空格键选中选项或者取消选项 ![]() 1.1.2、一键移动到数据区域的边缘 Ctrl+↓:到数据区域的最后一行,再按一次到下面有数据的单元格,直到表格最后一行 Ctrl+→:到数据区域的最右一列,在按一次到更右边的一个有数据的单元格,直到最右一列 ![]() 1.1.3、一键选中一组数据,并一键操作 Ctrl+Shift+↓:一键将某列的一串单元格选中 ![]() Ctrl+D:一键将所选数据区域全部复制第一个单元格(包括公式、数值) ![]() Ctrl+Shift+1:一键将所选区域的的数字的格式设置为常用的千分位无小数格式 ![]() Ctrl+Enter :因为Ctrl+D会复制单元格的格式,效率较低,当只需要复制数值或者公式的时候,选中数据区域用Ctrl+Enter即可 ![]() 1.1.4、一键切换工作表 Ctrl+Pagedown ,Ctrl+Pageup:分别实现向左或者向右切换sheet页面,从此不要用鼠标在excel中点来点去了 ![]() 1.1.5、一键选中一个数据区域 Ctrl+Shift+→:一键将某列的一串单元格选中;保持状态按↓,选中数据区块 ![]() 1.1.6、一键定位数据 Ctrl+G:在选中的区域中定位你需要的数据 ![]() 1.2、工作表的重要基础操作 1.2.1、组合工作表的行或者列-聚焦有用的数据列 这个功能帮助我们对太多数据列的数据域隐藏一部分数据,但可以随时打开,我们的大脑喜欢尽量少的数据列,以便于我们聚焦主要的问题,这是一个非常好的功能。 ![]() 1.2.2、收起整个工作上边的栏位-扩大数据域的显示范围 对于处理数据来说,将数据显示区域变大,对数据处理的人更友好 ![]() 1.2.3、设置快捷工作栏位 隐藏了上方的栏位,但有的功能很常用,怎么才能快速的找到?可以将这些功能设置到快捷栏位去 ![]() 1.2.4、冻结首行 当我使用滚轮向下滑动的时候,第一行的标题我就看不到了,如何能一直看到? ![]() 第二层:逻辑处理-理解公式并组合应用 想专门看公式讲解的,传送门: 柳致远:Excel心法第二层:公式一点通zhuanlan.zhihu.com这里一般的公式不说了,我挑选了一些的公式,通过对公式的组合,可以完成很多功能; 2.1、Countif 按条件计数: 用法2.1.1: =countif(被判断区域,"条件") 这种写法默认对“=条件的”值计数 ![]() 如下例对A列按条件计数 用法2.1.2 : =countif(被判断区域,"运算符+条件") 这种写法对“运算符+条件”的值计数 如下例,对小于1003的值计数 ![]() 2.2、Sumif 按条件求和 用法:=sumif(“被判断区域”,"条件","进行求和的区域") ![]() 2.3、Subtotal 按筛选求和 subtotal(数字,被计算区域) 数字9代表求和,其他可自行研究,这个公式可实现筛选之后的计算,下图中的筛选使用快捷键操作。这是个非常有用的技巧,当管理大量数据,不断筛选求和展示的,很有用 ![]() 2.4、If条件判断 =if("条件判断式",条件判断通过时返回的值,条件判断不通过时返回的值) 用法2.4.1: 与其他公式配合 ![]() 用法2.4.2: if自身嵌套,if自身作为一个返回值。 ![]() 2.5、= = 号本身可组成一个公式,某个单元格写成:=A1=B1,意味着当A1单元格的值等于B1单元格的时候,反馈Ture,否则反馈False; 可用于比对大规模数据区域是否一致 2.6、& 连接符,作于是对字符串进行链接,某个单元格写成:=A1&B1,意味着反馈A1与B1字符串形态的链接 第三层:初级数据可视化-图表与条件格式 3.1、所有的图表基于一套核心逻辑:选择数据区域-选择图表类型-格式设置效果,我就用蛛网图举例,其他的图表都是类似的。 ![]() 3.2、详细理解图表 3.2.1、随便新建一个图表,然后选中后右键,进入选择数据功能,本例中用的是直方图 ![]() 3.2.2、按住ctrl 选择数据域,数据域与对应的图示如下 ![]() 3.2.3、变更图表:为什么刚开始随便做个表都可以,是因为根据需要后续可以变更,选中图,右键: ![]() 将直方图换成饼图 ![]() 3.2.4、使用图标的“设计”功能,快速调整显示风格 ![]() 3.2.5、使用“格式”功能,微调效果,取决个人审美,我只是简单告诉大家设置的办法 ![]() 3.3、条件格式 ![]() 第四层:神奇的数据透视表 4.1、数据透视表的原理: 数据透视表会对所选定的数据源放到缓存中,对数据区域按列的值分类计算,这里的计算包括,求和、计数、求平均等等。是一种非常优秀强大的计算工具;理解它的关键在于:数据透视表的页区域起到筛选条件的作用、行、列区域的字段起到按行或者列展示数据的作用,值区域起到页区域的筛选进行真正的计算的作用 下面的图展示如何创建数据透视表,并处理创建中常见问题:使用透视表,首行必须填写,不能留空。创建后,设置成经典格式,方便后续拖拽变形。 ![]() 4.2、合并分组: ![]() 4.3、在数据源添加字段 注意添加字段后要刷新,是因为,透视表是基于缓存中的数据做的,对数据源的修改,要刷新才能体现到缓存 ![]() 4.4、计算字段 对一列数据,我们一般称之为一个字段,计算字段,即在原数据源上创建一个带有计算功能的字段。这样处理数据的好处就是可以保持数据源简洁,避免修改数据源和刷新的问题;但计算字段有个问题,就是会先求和,后求乘积,导致最后的计算结果与你预想的不同,因此在需要先乘积后求和的场景就不好用: ![]() 但计算字段= 字段*常数的时候,也就是使用常数运算的时候,效果不错,例如: ![]() 4.5、各种数据显示的方式与应用 当要在透视表中做出一列数字,一列百分比的时候,可以新建一个计算字段=原金额,将新计算字段按百分比显示 ![]() 4.6、透视图与切片器 使用切片器,可以做出按钮的效果 ![]() 第五层:VBA Excel为了更强大,提供了一个编程工具,即VBA;VBA内涵十分丰富,远不是我一篇文章能说完的,但为了给大家提起兴趣,选一点入门的知识说明 5.1、VBA世界的hello world 打开一个excel文件,将excel文件保存为启用VBA的xlsm文件,并打出第一行代码 ![]() 按Alt+F11 打开VBA编程界面,打出第一行代码 hello world ![]() 本文先只提一下VBA,因为这个东西实在是博大精深,再写几篇文章都不够的,后续有机会给大家介绍一些基本的VBA语句,帮助大家快速的实现一些功能。 5.2、VBA世界对单元格的操作 示例: ![]() 目的:按B列的电影名称进行产地判断,并将判断结果放到L列, 逻辑为:如果名称是复仇者、海王、变形金刚、毒液开头,就是美国,其他都是中国 ![]() 代码如下,后续再解释 Sub judge() With Sheet3 finalrow = .Range("B1048576").End(xlUp).Row Debug.Print finalrow For n = 2 To finalrow If Left(.Range("B" & n), 3) = "复仇者" Or Left(.Range("B" & n), 2) = "海王" Or Left(.Range("B" & n), 2) = "毒液" Or Left(.Range("B" & n), 2) = "变形金刚" Then .Range("L" & n) = "美国" Else .Range("L" & n) = "中国" End If Next End With End Sub 试用一下,并设置这个程序的快捷键是Ctrl+Q ![]() 第六层:全面综合打通,与Access,MySQL进行交互,制作复杂功能的商业工具 这一层最为高深,又可分为两级 6.1、初级: 只使用VBA的基本语句,主要处理Excel内部的各种复杂逻辑自动化的问题;其核心能力是需要掌握将业务逻辑提炼成程序逻辑的思维能力;掌握这一级的标志是对公式、VBA的基本语句熟练掌握,能在用户的陈述下,将业务逻辑直接转化为程序逻辑变成代码,在Excel文件范围内实现复制的自动化计算。 6.2、高级: 与数据库结合互动,需要使用Excel的一些数据库、网页对象;Excel提供了与常用数据库链接的方法,包括大家广泛使用的免费的Mysql,因此这里涉及到数据库的增删改查语句,需要懂一些SQL语言。掌握SQL语言之后,可以用Excel操作Mysql,这样就能将Excel化身为前台程序,以Mysql作为后台,做出更强大的工具 这一些东西也是博大精深的,我将在未来慢慢的总结并分享给大家~ 最后的最后,我的微信公众号:柳致远;欢迎有兴趣的朋友加入,一起搞搞数据~ |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |