excel一列求和_Excel六层心法

按我多年经验,Excel可分为六层心法。而每一层的修炼掌握,都将让你拥有解决一类问题的能力;知识由易到难,能力也由弱到强。

掌握前4层,就可以应付一般性的应用场景:能解决较复杂的计算、初级数据可视化、数据初步分析与汇报等问题;对应拥有编写复杂公式、制作互动性的静态图表、灵活应用透视表的能力;

如果能练到第6层,达到融汇贯通境界,则可随心所欲,解决绝大多数的数据处理、分析、展示的问题;对应拥有处理超复杂逻辑、实时报表、工具开发等能力;

而上述这些能力将在三个方面给你带来显著的回报:

1:摆脱大规模繁琐的重复操作,节约你的生命,高效准确完成任务,1个人能干2-3+个人活

2:高效处理复杂计算,能别人所不能,做出好作品、好报告,打动你的观众或者领导

3:开发功能性工具,改变数据处理的生产模式,变不可能为可能,创造新价值

第一层:快捷键与基本操作-快速完成数据区域的处理

1.1数据区域的快捷键

使用快捷键处理一块数据区域,更简单高效,还能避免鼠标手的伤害;常规的快捷键操作就不说了,这里我精选了小伙伴们可能不知道,但又很好用的快捷键:

1.1.1、一键筛选数据区域

Ctrl+Shift+L:对一个数据域筛选,再按一次取消筛选

Alt+↓:对筛选后的字段打开下拉菜单

↓:向下框中选项

Space:空格键选中选项或者取消选项

b40a871ce56f6ac05a08109c2abfccfb.gif

1.1.2、一键移动到数据区域的边缘

Ctrl+↓:到数据区域的最后一行,再按一次到下面有数据的单元格,直到表格最后一行

Ctrl+→:到数据区域的最右一列,在按一次到更右边的一个有数据的单元格,直到最右一列

2ff959f783b9f151aac41955a5eeaf16.gif

1.1.3、一键选中一组数据,并一键操作

Ctrl+Shift+↓:一键将某列的一串单元格选中

1adc7ce042681f3cebace64c53f7867c.gif

Ctrl+D:一键将所选数据区域全部复制第一个单元格(包括公式、数值)

77384fedd58ffff87ce557fbe6027c32.gif

Ctrl+Shift+1:一键将所选区域的的数字的格式设置为常用的千分位无小数格式

72f69ebcc857d2c65a1a5649bf9a722e.gif

Ctrl+Enter :因为Ctrl+D会复制单元格的格式,效率较低,当只需要复制数值或者公式的时候,选中数据区域用Ctrl+Enter即可

c4c65a34d059190ab0b10998a0bea564.gif

1.1.4、一键切换工作表

Ctrl+Pagedown ,Ctrl+Pageup:分别实现向左或者向右切换sheet页面,从此不要用鼠标在excel中点来点去了

48ccb32a11d6322817aa87f8f0865d86.gif

1.1.5、一键选中一个数据区域

Ctrl+Shift+一键将某列的一串单元格选中;保持状态按↓,选中数据区块

0bfab023344f95d17d3cc2e1a0548253.gif

1.1.6、一键定位数据

Ctrl+G:在选中的区域中定位你需要的数据

e42d4971deac9542d4e13b677f38c97c.gif

1.2、工作表的重要基础操作

1.2.1、组合工作表的行或者列-聚焦有用的数据列

这个功能帮助我们对太多数据列的数据域隐藏一部分数据,但可以随时打开,我们的大脑喜欢尽量少的数据列,以便于我们聚焦主要的问题,这是一个非常好的功能。

a4424876dad6e2f014070b2cd5c1870f.gif

1.2.2、收起整个工作上边的栏位-扩大数据域的显示范围

对于处理数据来说,将数据显示区域变大,对数据处理的人更友好

2fec2f8fef312cbe97203c85d131f427.gif

1.2.3、设置快捷工作栏位

隐藏了上方的栏位,但有的功能很常用,怎么才能快速的找到?可以将这些功能设置到快捷栏位去

179487da522914d5e26094df6851c39d.gif

1.2.4、冻结首行

当我使用滚轮向下滑动的时候,第一行的标题我就看不到了,如何能一直看到?

491b37b92cf8eb64af6716120d8767ac.gif

第二层:逻辑处理-理解公式并组合应用

想专门看公式讲解的,传送门:

柳致远:Excel心法第二层:公式一点通​zhuanlan.zhihu.com

这里一般的公式不说了,我挑选了一些的公式,通过对公式的组合,可以完成很多功能;

2.1、Countif 按条件计数

用法2.1.1:

=countif(被判断区域,"条件")

这种写法默认对“=条件的”值计数

31fe69a9450146d95f6d3373fba4598d.png

如下例对A列按条件计数

用法2.1.2 :

=countif(被判断区域,"运算符+条件")

这种写法对“运算符+条件”的值计数

如下例,对小于1003的值计数

ec02e604f63c477feed171327b498891.png

2.2、Sumif 按条件求和

用法:=sumif(“被判断区域”,"条件","进行求和的区域")

7e054043921fcdacfe466617f533d20b.png

2.3、Subtotal 按筛选求和

subtotal(数字,被计算区域)

数字9代表求和,其他可自行研究,这个公式可实现筛选之后的计算,下图中的筛选使用快捷键操作。这是个非常有用的技巧,当管理大量数据,不断筛选求和展示的,很有用

fa83218456646a1ebcab6057f69f4bfd.gif

2.4、If条件判断

=if("条件判断式",条件判断通过时返回的值,条件判断不通过时返回的值)

用法2.4.1:

与其他公式配合

db120fd9ba777fd0f9b7d532555926a2.png

用法2.4.2:

if自身嵌套,if自身作为一个返回值。

eea55b71766d41b43544af0e8c507eb3.png

2.5、=

= 号本身可组成一个公式,某个单元格写成:=A1=B1,意味着当A1单元格的值等于B1单元格的时候,反馈Ture,否则反馈False; 可用于比对大规模数据区域是否一致

2.6、&

连接符,作于是对字符串进行链接,某个单元格写成:=A1&B1,意味着反馈A1与B1字符串形态的链接

第三层:初级数据可视化-图表与条件格式

3.1、所有的图表基于一套核心逻辑:选择数据区域-选择图表类型-格式设置效果,我就用蛛网图举例,其他的图表都是类似的。

6b385a654c201dd53e308a9adeceedf5.gif

3.2、详细理解图表

3.2.1、随便新建一个图表,然后选中后右键,进入选择数据功能,本例中用的是直方图

217829338a9eb20d153e0adecb6499a4.png

3.2.2、按住ctrl 选择数据域,数据域与对应的图示如下

852b7bc6bc4cabff48f02b6e16a713ad.png

3.2.3、变更图表:为什么刚开始随便做个表都可以,是因为根据需要后续可以变更,选中图,右键:

7302300e9a836a3ad063b786ece4837b.png

将直方图换成饼图

d8655df455cecb420522af35ad684996.gif

3.2.4、使用图标的“设计”功能,快速调整显示风格

fb449bec6ad4ebad9b85d420f96334d5.gif

3.2.5、使用“格式”功能,微调效果,取决个人审美,我只是简单告诉大家设置的办法

88b276a34e11e21335b7c350a7dd7abc.gif

3.3、条件格式

a17c5820ca9373b1f042fb9daed02718.gif

第四层:神奇的数据透视表

4.1、数据透视表的原理:

数据透视表会对所选定的数据源放到缓存中,对数据区域按列的值分类计算,这里的计算包括,求和、计数、求平均等等。是一种非常优秀强大的计算工具;理解它的关键在于:数据透视表的页区域起到筛选条件的作用行、列区域的字段起到按行或者列展示数据的作用值区域起到页区域的筛选进行真正的计算的作用

下面的图展示如何创建数据透视表,并处理创建中常见问题:使用透视表,首行必须填写,不能留空。创建后,设置成经典格式,方便后续拖拽变形。

d4049d31b481e0b55d34517564501fe6.gif

4.2、合并分组:

0f1565551ce3a8d5e8dd68ec9dd065c0.gif

4.3、在数据源添加字段

注意添加字段后要刷新,是因为,透视表是基于缓存中的数据做的,对数据源的修改,要刷新才能体现到缓存

bef3a408c5f1c617558b55b8231331ee.gif

4.4、计算字段

对一列数据,我们一般称之为一个字段,计算字段,即在原数据源上创建一个带有计算功能的字段。这样处理数据的好处就是可以保持数据源简洁,避免修改数据源和刷新的问题;但计算字段有个问题,就是会先求和,后求乘积,导致最后的计算结果与你预想的不同,因此在需要先乘积后求和的场景就不好用:

3b0cf973de3c902b9a6840f3d7fb2454.gif

但计算字段= 字段*常数的时候,也就是使用常数运算的时候,效果不错,例如:

6ffe6b483f94c32e20dc3740da73931c.gif

4.5、各种数据显示的方式与应用

当要在透视表中做出一列数字,一列百分比的时候,可以新建一个计算字段=原金额,将新计算字段按百分比显示

0ad98db09d602d6c3c63e5f6711f9301.gif

4.6、透视图与切片器

使用切片器,可以做出按钮的效果

ddab79d9a926a6ada7214c1995662ba2.gif

第五层:VBA

Excel为了更强大,提供了一个编程工具,即VBA;VBA内涵十分丰富,远不是我一篇文章能说完的,但为了给大家提起兴趣,选一点入门的知识说明

5.1、VBA世界的hello world

打开一个excel文件,将excel文件保存为启用VBA的xlsm文件,并打出第一行代码

8f758ad95c3a1ea20fa17f2579143884.gif

按Alt+F11 打开VBA编程界面,打出第一行代码 hello world

8691c326c5e0e196b76df1a06cc45766.gif

本文先只提一下VBA,因为这个东西实在是博大精深,再写几篇文章都不够的,后续有机会给大家介绍一些基本的VBA语句,帮助大家快速的实现一些功能。

5.2、VBA世界对单元格的操作

示例:

40284f81d1b6bf424c0e1d17eff6bca2.png

目的:按B列的电影名称进行产地判断,并将判断结果放到L列,

逻辑为:如果名称是复仇者、海王、变形金刚、毒液开头,就是美国,其他都是中国

07f619edf6c816ce644a0f1a5b491152.png

代码如下,后续再解释

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

2a8d22d36ffae10a6a7922e29d4a4cad.gif

第六层:全面综合打通,与Access,MySQL进行交互,制作复杂功能的商业工具

这一层最为高深,又可分为两级

6.1、初级:

只使用VBA的基本语句,主要处理Excel内部的各种复杂逻辑自动化的问题;其核心能力是需要掌握将业务逻辑提炼成程序逻辑的思维能力;掌握这一级的标志是对公式、VBA的基本语句熟练掌握,能在用户的陈述下,将业务逻辑直接转化为程序逻辑变成代码,在Excel文件范围内实现复制的自动化计算。

6.2、高级:

与数据库结合互动,需要使用Excel的一些数据库、网页对象;Excel提供了与常用数据库链接的方法,包括大家广泛使用的免费的Mysql,因此这里涉及到数据库的增删改查语句,需要懂一些SQL语言。掌握SQL语言之后,可以用Excel操作Mysql,这样就能将Excel化身为前台程序,以Mysql作为后台,做出更强大的工具

这一些东西也是博大精深的,我将在未来慢慢的总结并分享给大家~

最后的最后,我的微信公众号:柳致远;欢迎有兴趣的朋友加入,一起搞搞数据~

  • 0
    点赞
  • 1
    收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:数字20 设计师:CSDN官方博客 返回首页
评论
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值