北大硕士给大脑植入Excel病毒,工作效率提升了好几倍

一份特别的Excel教程,拿走不谢

在工作中,我们经常会碰到这样的同事,他们是这样完成工作的:先用计算器算好结果,甚者动用手指头在电脑屏幕上数数,然后把数据填写到Excel表格中。

结果可以预见,原本可以在上班时间完成的工作,愣是加班也完不成。如果只是把Excel当作了记录工具,就好比把跑车开进了泥泞小路。不要抱怨Excel拖累了你,这纯粹是你的问题。

究竟应该如何使用Excel?有哪些操作技巧是需要掌握的?今天就和大家分享下Excel中最常用的数据整理和分析技巧,让你快速“脱胎换骨”,工作高效准确,从此告别加班!

快捷键操作七大技巧

技巧1:按住【CTRL】键,拖动实现表格复制

选中数据区域,复制-粘贴?不需要这么多步,选中数据区域,按住【CTRL】键,拖动即可复制。

按住CTRL快速复制表格

技巧2:按住【SHIFT】键,拖动实现两列位置互换

你是不是这么完成两列位置互换的:把一列数据复制到空白单元格后,先把另一列数据复制到指定列中,然后把复制到空白单元格的数据剪贴回要交换的列中。

何必这么麻烦呢?左手按住【SHIFT】键不放,右手按住鼠标左键不放拖动列边线到相应位置,即可实现两列互换。

SHIFT快速实现两列位置互换

技巧3:【CTRL】+方向键,快速定位单元格位置

好几万行数据,利用鼠标翻滚多少次才能到。利用【CTRL】+方向键,快速定位单元格位置。

提示:【CTRL】+↑,↓,←,→,分别到达数据区域的最上、最下、最左、最右的单元格

CTRL+方向键快速移动

技巧4:【CTRL】+【SHIFT】+方向键,快速选中数据区域

还用鼠标在一张庞大的Excel表格中拖来拖去选择数据?OUT啦!【CTRL】+【SHIFT】+方向键帮你轻松选中数据区域。

CTRL+SHIFT+方向键,快速选取

技巧5:【ALT】+↓,实现菜单输入

是不是还在一遍遍用键盘敲打:(部门所属)人力资源部、综合管理部、财务部、战略规划部等信息。不妨试下【ALT】+↓,菜单输入即时呈现,省掉多少指间功夫。

ALT+下拉箭头

技巧6:【CTRL】+G/F5快速定位,【CTRL】+【ENTER】批量填充

在数据规范处理中,我们经常需要给表格中的空白单元格输入0值。空白单元格如此多,难道要一个个输入0吗?这时就可以用【CTRL】+G/F5快速定位,定位“空值”,找到所有空白单元格后,在特定的一个单元格中输入“0”,按住【CTRL】+【ENTER】批量填充。

快速定位、批量填充

技巧7:【ALT】+【=】,实现批量求和

不用再一次又一次的书写SUM函数,批量求和,【ALT】+【=】,正为此而来。

ALT+=快速求和
数据规范两大利器

利器1:数据有效性,我的地盘我做主

“发出去让别人填的表总是错漏百出,要么该填的单元格没有填写完毕,要么填写的都是一些错误的数据,弄得我回收回来还必须一个一个地进行修改。填表规范和要求都写明了,难道都不看的嘛?”

面对这种囧境,使用数据有效性工具,就可以轻松解决!

应用1:变手动输入为菜单选择,杜绝输错

操作:数据-数据有效性-序列-选择或输入序列数据-设置完成

菜单设置

应用2:结合COUNTA函数,该填的想不填都没门

COUNTA函数的作用是返回列表中非空的单元格个数。如果数据区域需要输入5个有效信息,那COUNTA(数据区域)=5

操作:数据-数据有效性-序列-自定义-写入公式-设置完成

数据验证+counta:完整输入信息

利器2:分列,不止拆分

应用1:分列人员信息,规范输入(按分隔符号拆分)

记录人员信息时,没经验的员工总是把所有信息记录在同一个单元格中。试想下,不同的字段信息放在了同一单元格中,还如何进行数据分析呢。此时,我们就可以使用分列功能,对信息进行拆分

注:在分列向导第三步中,要对拆分后的每一部分进行数据格式选择,同时将需要的拆分结果放到指定的单元格位置

分列:快速拆分,规范记录信息

应用2:从身份证号中提取出生年月日信息(按固定宽度拆分)

身份证号中包含着众多的个人信息,比如7-14位为出生年月,最后第2位可用于判断性别。那么,如何快速地从身份证号中提取出生年月日信息呢?使用分列按固定宽度拆分即可。

注:不要忘记在分列向导第三步中把数据设置为日期格式

分列:从身份证号中提取出生日期

应用3:分列一下,日期数据规范起来

面对大量格式不准确的日期,分列一下,马上规范起来。

分列:批量规范数据格式
7大函数技巧

函数1:EDATE函数,计算合同到期日

合同期为36个月或3年,到期日期是哪天?在计算到期日时,可以使用EDATE函数

EDATE函数结构:=EDATE(起始日期,月数)

注:EDATE中第二参数为月数,如果不是月数,要记得转化为月数。

合同到期日=EDATE(合同签订日期,以月计的合同期限)-1

EDATE计算合同到期日

函数2:DATEDIF函数,根据出生日期,计算年龄

根据出生日期,计算年龄,计算退休年龄?根据工作时间,计算工龄?计算特别日子的纪念日?DATEDIF也可以浪漫一下。

DATEDIF函数结构:=DATEDIF(开始时间,结束时间,间隔时间参数),间隔时间参数可为年(Y)、月(M)、日(D)

DATEDIF:根据身份证号计算年龄

函数3:COUNTIFS函数,实现多条件计数

“小孙,咱们公司30-40岁的职工多少个”

如何计算多条件下的计数问题呢,COUNTIFS函数手到擒来

COUNTIFS函数结构:=COUNTIFS(条件判断区域1,条件1,条件判断区域2,条件2,······)

COUNTIFS多条件计数

函数4:SUMIFS函数,多条件求和再简单不过!

“小张,尽快把今年营业额超过5000万,销量超过100万件的销售人员的工资总额给我计算一下。”听到老板的吩咐,只会用SUM函数的小张脑袋顿时大了起来,筛选一次,筛选又一次,复制到新表,计算,结果到底准确不准确呢。

面对多条件求和,如果你学会了SUMIFS,岂不是再简单不过!

SUMIFS函数结构:=SUMIFS(求和范围,条件范围1,条件1,条件范围2,条件2,……)

SUMIFS多条件求和

函数5:VLOOKUP函数,查找匹配界一哥

对数据进行查询调用是职场一族在日常工作中经常要涉及到的内容,而VLOOKUP函数则是这项工作中使用率最高的函数,称之为查找匹配界一哥丝毫不过分。VLOOKUP函数究竟如何使用呢?

VLOOKUP函数结构:=VLOOKUP(查找值,查找数据范围,要返回的数据所在的列数,精确匹配还是模糊匹配)

其中,查找值位于查找的数据范围的第一列,查找的数据范围需为绝对引用,从查找值所在的数据列开始计数要返回的数据所在的列数,精确匹配参数为0

VLOOKUP:精确查找

模糊匹配则可以实现多重IF函数嵌套的功能,诸如实现对应成绩与对应等级的匹配,奖金等级与奖金指数的匹配等。

VLOOKUP:模糊匹配

函数6:MATCH函数,找到你的行列位置

要确定某一数据在行或列中的位置,找位置,就用MATCH函数

MATCH函数结构:=MATCH(查找值,查找区域,查找类型)

让Match找到你的位置

函数7:VLOOKUP+MATCH,查找更智能

VLOOKUP函数中第三参数为要返回的数据所在的列数。要解决列数,除了一列一列地数,当然可以用MATCH函数来实现计算了。

VLOOKUP+MATCH,查找更智能更容易了起来

VLOOKUP+MATCH:查找起来更方便
数据透视神器

数据透视表,是一种交互式的图表。它允许用户根据需要对各类数据维度进行划分,通过快捷地拖动各类数据维度,将他们进行不同的重组,实现我们想要的结果。数据透视表堪称Excel各项功能中的神器,助你轻松发现隐藏在数据背后的本质。

应用1:创建数据透视表,快速制作统计报表

根据你想要呈现的数据报表,透视一下,让你轻松“拖”出来。

操作步骤:在数据表中任意选中某个单元格—【插入】—【数据透视表】-【数据透视表字段及区间】—根据报表行列呈现需要,在字段列表中选定该字段并按住鼠标左键拖放到下方的矩阵窗口中,完成数据透视表布局。

创建数据透视表,快速制作统计报表

应用2:多种数值计算方式,信手拈来

除了求和与计数,透视表也允许使用平均值、最大值/最小值、标准差、方差等统计方式。你想要的数值计算方式,应有尽有。

操作:在数值区域中单击鼠标右键,在【值汇总依据】中选择即可。

多种数值计算方式,信手拈来

应用3:招式三:分组创建,数据标签由你定义

按季度?分年龄段?分组创建,数据标签由你定义

操作:选中字段下的数据—右键单击选择【创建组】-创建“起始于”和“终止于”对应的数值,也可采用自带的年、季度、月等组合方式

自动创建分组

应用4:手动创建分组,实现区域统计

销售数据只有按城市分布的报表,大区级的怎么统计?可以试试手动创建分组,比如我们可以通过手动创建分组把北京、天津等组合为华北区,轻松实现区域统计。

操作:选中需要组合的城市名称—右键单击选择【创建组】—修改组名称为需要的组合名称。

手动创建分组

应用5:妙用筛选器,自动实现分表显示

把总表按照某种规则拆分成一个个分表?一个数据透视表的筛选器就能搞定!

操作:数据透视表分析选项卡—选项—显示报表筛选页

妙用筛选器,自动实现分表显示

应用6:切片器链接,一键控多表

一枚切片器,轻松控制多个数据透视表,数据展现随心而动

操作:选中数据透视表中任一数据——【分析】选项卡—插入切片器—右键单击切片器—报表连接—勾选需要控制的多个表格

切片器链接,一键控多表

应用7:切片器+数据透视图,图表动起来

通过切片器操作,显示数据发生变化,数据透视图也就“动”了起来!

操作:插入数据透视图—切片器标签选择—动态图表展现

切片器+数据透视图,图表动起来

还在等什么!马上操作起来!

欢迎大家关注文集Excel常用技巧,与大家一起共同成长学习。

如果你觉得不错或者有用,希望大家能在文章最下面点个喜欢!

欢迎大家关注下作者北大小笨,更不要忘记打赏哟~~

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,511评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,495评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,595评论 0 225
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,558评论 0 190
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,715评论 3 270
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,672评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,112评论 2 291
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,837评论 0 181
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,417评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,928评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,316评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,773评论 2 234
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,253评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,827评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,440评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,523评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,583评论 2 249

推荐阅读更多精彩内容