请选择 进入手机版 | 继续访问电脑版

职场休闲网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

搜索
查看: 505|回复: 11

Excel 使用方法

[复制链接]
发表于 2019-2-11 11:14:19 | 显示全部楼层 |阅读模式
作者:罗翔
链接:https://www.zhihu.com/question/21758700/answer/21687597
来源:知乎
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

以下为原文:
===============排版篇==================
  • 给他人发送excel前,请尽量将光标定位在需要他人首先阅览的位置,例如Home位置(A1),例如结论sheet,长表尽量将位置定位到最顶端
  • 有必要的时候请冻结首行;没必要但可追究的内容,可以隐藏处理
  • 行标题、列标题加粗,适当处理文字颜色、填充颜色,利人利己,可参见:Excel 中的颜色要怎么搭配,视觉上更能接受且区分度高? - 设计
  • 占用空间比较小的表格,可以放置在左上角,但留空A列和1行,并给表格加上合适的框线,观感很不错哦~
  • 同类型数据的行高、列宽、字体、字号,求你尽量一致,非要逼死强迫症吗!
  • 定义好比较标准的格式,例如百分比预留几位小数,手机号的列宽设置足够,时间显示尽量本土化...
  • 不要设置其他电脑没有的字体,除非这个表格就在这一台电脑使用...
  • 参考一些官方的模板,例如OfficePLUS,微软Office官方在线模板网站!,再例如Mac端excel打开就显示的各种模板,很多清单或者规划类的excel我都直接用这里面的,不需要重新设计

===============操作篇==================
  • Alt+Enter在表格内换行,楼上有提到
  • Ctrl+Shift+上/下,选择该列所有数据,当然加上左右可选择多列
  • Ctrl+上/下,跳至表格最下方
  • Ctrl+C/V,不仅仅复制表格内容,也可以复制格式和公式!
  • Ctrl+D/R,复制上行数据/左列数据
  • 还有个很好用的单元格格式转换,推荐大家用熟


(有点不清晰...当初偷懒直接把图片截到印象笔记的...)
  • Ctrl+F/H的查找、替换,点击“选项”,可以替换某种格式等等,另一片天地有木有!
  • F4,对,你没看错,就是F4!重复上一步操作,比如,插入行、设置格式等等频繁的操作,F4简直逆天!
  • ‘(分号后面那个)  比如输入网址的时候,一般输入完会自动变为超链接,在网址前输入’就解决咯
  • 复制,选择性粘贴里面有几个非常好用的——仅值,转置(个人推荐用transpose公式)
  • 公式里面切换绝对引用,直接点选目标,按F4轮流切换,例如A1,$A$1,$A1,A$1
  • 快速填充能取代大部分有简单规律的分列、抽取、合并的工作
===============公式篇==================
  • if、countif、sumif、countifs、sumifs,这几个一起学,用于条件计数、条件求和
  • max、min、large,这几个一起,用于简单的数据分析
  • rand、randbetween,这俩一起,用于生成随机数,也可以用于生成随机密码(用rand配合char可生成中英文大小写随机的)
  • 定位类型的函数:MID、SEARCH、LEN、LEFT、RIGHT一起学吧,简单但异常实用
  • 四舍五入个人偏好用round函数,举个简单例子,一列数据,2.04、2.03并求和,显示保留1位小数,你会在界面上看到2.0、2.0,求和却是4.1,表格打印出来会比较让人难理解
  • subtotal:用于对过滤后的数据进行汇总分析
  • sumproduct:返回一个区域的乘积之和,不用A1*B1之后再下拉再求和
  • Vlookup函数,这个不多说了,神器;另外推荐lookup函数:LOOKUP(1,0/(条件),查找数组或区域)
  • offset函数,常用于配合其他函数使用,例如想将10*20的表中的每行复制成3行按原顺序变成30行:=OFFSET($A$1,INT((ROW(A1)-1)/3),COLUMN(A1)-1,1,1) 下拉,由于不用到列,所以等同于=OFFSET($A$1,INT((ROW(A1)-1)/3),0),我当初是这么做笔记的....:=(A1,向下偏移(向下取整(行数-1)/3),向右偏移0)
  • text,例如19880110  text(A1,"0-00-00"),转为1988-01-10,用法很多
  • weekday,让你做时间计划表什么的时候,把日期转为“星期X”
  • column(目标单元格),返回目标单元格所在列数,有时候真的很好用...还有 @黄老邪

    推荐的columns
  • transpose(目标区域),神奇的转置,把行变成列,把列变成行...
  • &,可在目标单元格后面增加某些字符,偶尔用(我这种强迫患者用的是concatenate公式,我特么有病!)
  • 数组,虽然复杂,但是有的公式配上数组简直爽爆
  • 多百度,例如曾经碰到一个难题,把X分X秒,转为X秒,例如172分52秒,百度半天得到的公式:=IF( IFERROR( FIND( "分", $E2 ), 0) > 0, LEFT( $E2, FIND( "分", $E2 ) - 1 ) * 60 + IFERROR( MID( $E2, FIND( "分",$E2 ) + 1, FIND( "秒", $E2 ) - FIND( "分", $E2 ) - 1 ), 0 ), LEFT( $E2, FIND( "秒", $E2 ) - 1 ) * 1 ) 度娘很厉害的(评论里面直接用=TEXT(SUBSTITUTE(SUBSTITUTE("00:"&A1,"秒",""),"分",":"),""),把文本转为时分秒的标准格式再转秒,确实是更好的方法)

===============图表篇================
  • 不同的场景请用不同的图,转个非常精髓的图:

  • 数据透析表、数据透析图,嗯嗯,推荐的人太多了...
  • 图表设计——布局,灵活运用好多类数据时的“次坐标轴”
  • 选择数据——右键——更改图标类型,灵活在一张表上结合起来柱状图和折线图

===============技巧篇=================
  • 数据——分列,将列内的数据拆分成多列,比如“XXX省XXX市”,拆成省、市两列,“XX小时XX分钟”拆成时、分两列,可以按照宽度、文本、标点等作为界定进行拆分,非常多的场景会使用到,请优先学会...
  • 如果你不是靠excel吃饭,请不用那么geek,而是学会excel的逻辑——配合简单的公式、排序、替换、if等全局操作能得出的结果,不一定非要用一个长公式然后下拉,举例:
如何将无规律的一列上下翻转?
——创建一列,标上1、2、3……,下拉,以该列为主排序,改升序为降序,扩展目标列,得到结果,之后可以删掉创建的辅助排序列
如何将目标区域的每一行数据下面插入一条空行?
——创建一列,标上1、2、3……,下拉,下面空白行标上1.5、2.5、3.5……下拉,同理排序~Tada~
  • 条件格式——突出显示单元格规则,里面的“重复值”,在实时录入和检查标记时很实用
  • 在条件允许的情况下,升级到office 2013吧,excel 2013比2010好到爆啊!比如新增的sumifs、averageifs等多条件if,比如选择一个区域,右下角小标“快速分析”自动生成数据条、色阶、柱形图、汇总图、透视表、折线图等等啊,秒中出啊有木有!

===============插件篇=================
  • Power Map :在线地图+在线演示+制作视频,随便来个中国壕热力图:
当然,也有柱状图:

  • Power View:带可视化交互效果的图表,很适合演示
默认配色就很不错,而且演示的时候点击时会直接按你点击的类型帮你显示对应的数据(例如上面的堆积柱状图)
  • Power Query:这个用法很多,我主要用于以下两点:
1.  在线Web抓取:不需要学会某个编程语言也能爬虫+分析一些简单数据,随便举个例子输入新浪股票的网址,它自动帮忙抓取到N个表,我随便打开一个:

2. 连接数据库:不需要学会SQL语法也能查询+分析数据库内的数据,这个就不方便截图了...
打比方说,常见的 select * from ... where xxx = xxx and xxx>xxx group by xxx这种sql语法查询的内容,可以在Power Query中直接通过点击、筛选等操作就列出来
  • 各种excel工具箱,这个不多介绍了,不常用,也就不打广告了,但是挺适合部分长期使用excel的职场人士使用
  • SmartArt也是一大神器,我终于不用在Ai或者PPT上作图再粘过来了...

==================数据可视化====================
常见的数据可视化工具不再赘述,在知乎里面一搜一大把,我的经验不算多,但是单独加这个分类是想强调数据可视化的重要性,以及我对数据可视化的理解,希望大家结合下面的内容再去看其它答案的数据可视化工具:
1. 要『正确』地理解数据可视化,一切不以帮助理解数据为目的的可视化都是耍流氓,例如
  • 倒腾半天用各种花哨工具做出来的酷炫效果(也包括一些可视化工具的网站),砸UI饭碗
  • 本来需要输出的是给其它部门的数据报表,自以为是地加上可视化,请做好本职工作
  • 桑基图、箱线图、雷达图用的很溜,但实际Boss只想用看(或者只会看...)熟悉的柱状图、折线图、饼图

2. 我认为的『正确』的数据可视化是:
  • 易理解的。选择正确的表达形式,适合用柱状图就别用散点图,也别用那些受众人群不好理解的特殊用图
  • 可维护的。改了一点数据或者下次有类似报表再做不要花重复的时间再来一次
  • 有标准的。在同一工作范畴内选择尽量标准一致性的数据可视化效果,坐标、图例、筛选尽量统一,所以也不推荐一个报告用到多种可视化工具

3. 一些个人技巧
  • 个人分析使用excel自带透视图,后台用的inspinia模板 + echarts(另外补充,echarts-x很好用但很多人不知道),写报告用图说,商业数据分析用Tableau,另外觉得GA和umeng的可视化做的不错可以学习借鉴
  • 有时候看到数据维度多的时候,觉得用什么图都不合适,我会先看echart图例再往回看适用具体哪种
  • 思维不要固化在柱状图、折线图、饼图,多看多学才能横向拓宽自己的知识面
  • 大部分后台需求是可以用图来表达的,密密麻麻的数据表格会让人无法第一时间抓到重点,不要吝啬开发的时间
  • 补充上条,为了数据表格能随时导出,在可视化表达之后,也考虑是否要把表格补充进来,这点我觉得umeng做的真的很好
妈呀,写到这我真要吐槽这知乎新版的『可视化』,bug太多了,我编辑个答案各种丢图丢格式还多加换行...

==================其它篇====================
  • 不会写宏没关系,要懂得怎么使用别人的宏(自行百度“excel宏大全”吧~),怎么保存xlsm,怎么录制宏。当你把机械化的一套操作通过录制宏实现,并用xlsm配合auto_open自动操作,眼看表格自动化操作,在两秒内给你返回原来每天固定要做十几分钟的数据分析结果时,那个鸡皮疙瘩...
  • 有时间推荐泡泡excel的论坛,excelhome什么的,神人太多了....
  • excel满足不了你,又懂编程,想秀逼格的,请右转百度 SPSS

✎✎✎✎✎✎✎✎✎ 出题的分割线必须华丽✎✎✎✎✎✎✎✎✎
额,我都写那么多了,出个题又不会死——
【A列】
a
a
a
a
b
b
b
c
c
d
d
d
d
e
……
……
……
每行仅一个数据,无法准确知道有多少个a,多少个b,多少个c……
请【仅用一个公式】统计出——【A列中不同单元格的个数】,比如a、b、c、d,算4个
请注意,只允许公式,其他操作均不允许,因为这个题不是为了结果,而是考excel逻辑的...
(擦,这么快就被解答,说好的面子呢!)

【后记】
1. 关于答案
excel是一个很庞大很完整的“系统”,各行各业用处定然不尽相同,所以答案肯定仁者见仁智者见智,我也只是抛砖引玉,说说自己在个人工作上的经验
2. 关于疑问求解
excel里的帮助(F1)非常之有用,输入关键词就会提供给你需要的信息,请常用!个人推荐是——基础操作找百度,公式函数找F1,偏具体的需求找excelhome
3. 抛玉...
4. 关于学习与书籍
引用
@谌斌

回答的如何快速有效地提高 Excel 技能水平?——
我觉得提高Excel水平最重要的是某种“刺激”:
你需要交一个报告;
你需要做一个分布图;
你需要做数据去重;
你需要做数据排序;
你需要做数据统计;
这种“刺激”唯一不能是:你想学Excel。个人非常感同身受,我所有的学习均是受上面所提的各种“刺激”,所以假如让我推荐学习excel的书籍,我怕误人子弟...不过,我个人还是比较倾向在有一定基础之后补充完善的干货...



回复

使用道具 举报

本网旨在提供公益服务,所有帖子不代表本网观点和立场。帖子仅供参考,不构成投资建议,据此操作风险自担。如有侵权请联系邮箱:FJL09@163.com,一定及时核实后删除。
 楼主| 发表于 2019-2-11 11:31:39 | 显示全部楼层

Excel快速填充功能

“快速填充”可能是EXCEL中使用频率最多的功能之一了,但是对于初学EXCEL的同学,有些地方还是需要注意的。
在EXCEL中可以通过拖动或双击实现快速填充。但是,如图所示,以单个数字开始填充其实是在复制单元格内容(注:WPS中为递增填充)。


而大多时候我们希望EXCEL能够帮我们智能递增填充。这就要我们在默认填充之后,选择填充方式。

为了实现递增填充,我们还可以“告诉”EXCEL应该按照什么逻辑进行填充。

“快速填充”功能对应的快捷键Ctrl+D,它不光能实现数字序号的填充,对于一些其他有规律的操作也能实现“自动填充”,比如:

EXCEL中还有一个自动填充的快捷键Ctrl+E,它和Ctrl+D稍有差别,Ctrl+E能根据内容智能识别填充模式,比如下图是提取字符串中数字:



回复 支持 反对

使用道具 举报

 楼主| 发表于 2019-2-12 08:47:16 | 显示全部楼层

今年过节送什么了?5个实用的Excel技巧,你要吗?职场办公俱乐



过年了,也不知道该送给大家什么好,那就送5个Excel实用小技巧吧,让你上班可以提高工作效率哦!
1.快速取消超链接
有超链接是很烦人的,所以需要想一个法子将它取消掉,点击【文件】-【选项】-【校对】-【自动更正】按钮,把Internet及网络路径替换为超链接前面的勾取消即可。


2.批量添加《》
在统计书名、电视剧时,我们常常用《》书写,但有时也会忘记,在数量较多的情况下如何批量添加来了?
首先先将一个需要添加《》的内容输入,然后利用【Ctrl+E】组合快捷键进行填充即可。


3.根据内容自动生成序号
首先选定一个单元格,然后输入函数公式:=IF(B2="","",COUNTA($B$2:B2)),再下拉填充即可。


4.制作二级菜单栏
点击-【数据验证】-【序列】,输入相应的内容即可。
(注意:手动输入需要使用英文状态下的逗号进行分隔)


5.快速填充星期
首先将日期复制到空白处,然后点击右键,选择【设置单元格格式】-【数字】-【日期】,最后在【类型】中选择格式即可。


好了,今天的分享就到这里了,不知道大家是否需要了?觉得实用的朋友建议收藏一份。





回复 支持 反对

使用道具 举报

 楼主| 发表于 2019-2-12 09:05:52 | 显示全部楼层
在Excel中多关键字排序

办公达人速成


Excel中多关键字排序就是对工作表中的数据按两个或两个以上的关键字进行排序。在此排序方式下,为了获得最佳结果,要排序的单元格区域应包含列标题。

如果对多个关键字进行排序时,在主要关键字完全相同的情况下,会根据指定的次要关键字进行排序;在次要关键字完全相同的情况下,会根据指定的下一个次要关键字进行排序,依次类推。具体操作如下。

步骤 1 单击要进行排序操作工作表中的任意非空单元格,然后单击“数据”选项卡上“排序和筛选”组中的“排序”按钮。

步骤 2 在打开的“排序”对话框中设臵“主要关键字”条件,然后单击“添加条件”按钮,添加一个次要条件,再设臵“次要关键字”条件,如图1左图所示。用户可添加多个次要关键字,设臵完毕,单击“确定”按钮即可,效果如图1右图所示。

在Excel中多关键字排序

图1

Excel中多关键字排序时,选中“排序”对话框的“数据包含标题”复选框,表示选定区域的第一行作为标题,不参加排序,始终放在原来的行位臵;取消该复选框,表示将选定区域第一行作为普通数据看待,参与排序。




回复 支持 反对

使用道具 举报

 楼主| 发表于 2019-2-12 09:34:10 | 显示全部楼层

Excel 的数据筛选功能

我们在对Excel工作表数据进行处理时,可能需要从工作表中找出满足一定条件的数据,这时可以用Excel 的数据筛选功能显示符合条件的数据,而将不符合条件的数据隐藏起来。要进行筛选操作,Excel数据表中必须有列标签。

自动筛选一般用于简单的条件筛选,筛选时将不需要显示的记录暂时隐藏起来,只显示符合条件的记录,具体操作如下。

步骤 1 单击要进行筛选操作的工作表中的任意非空单元格,如图1左图所示,然后单击“数据”选项卡上“排序和筛选”组中的“筛选”按钮,如图1右图所示。

Excel2010自动筛选的操作步骤

图1

步骤 2 此时,工作表标题行中的每个单元格右侧显示筛选箭头,单击要进行筛选操作列标题右侧的筛选箭头,本例单击“评估”右侧的箭头,本例单击“评估”右侧的箭头,在展开的列表中取消不需要显示的记录左侧的复选框,只勾选需要显示的记录,如图2左图所示,单击“确定”按钮,得到成绩表中“评估”成绩为“优秀”的筛选结果,如图2右图所示。

Excel2010自动筛选方法

图2

在Excel2010中,我们还可以按用户自定的筛选条件筛选出符合需要的数据。例如,要将成绩表中平均成绩在 70 至 80 之间的记录筛选出来,操作如下。

步骤 1 首先在图2左图所示的“评估”筛选列表中选择“全选”复选框,然后单击“确定”按钮,显示工作表中的全部记录。

步骤 2 单击“平均成绩”列标题右侧的筛选箭头,在打开的筛选列表选择“数字筛选”,然后在展开的子列表中选择一种筛选条件,如选择“介于”选项,如图3所示。

Excel 的数据筛选功能

图3

步骤 3 在打开的“自定义自动筛选方式”对话框中设臵具体的筛选项,本例设臵如图4左图所示,然后单击“确定”按钮,效果如图4右图所示。

Excel2010的自定义筛选功能

图4

通过Excel 的数据筛选功能,我们知道了对Excel工作表中的数据进行自动筛选、按条件筛选的方法,通过筛选让Excel工作表显示我们想要看到的内容和数据。




回复 支持 反对

使用道具 举报

 楼主| 发表于 2019-2-12 16:40:24 | 显示全部楼层

4个excel小技巧,工作中经常用得到!



职来职往知职场



1、调整列宽和另一个表相同
用途:把两个表的列宽调整一致,不用再一列一列的手工调整了。
操作方法:选取表1的列(A) - 复制 - 选取表2的列(F:I) - 选择性粘贴 - 列宽。
2、快速选取指定的列
用途:在一个几十列数据的表格中,快速跳转到指定的列。
操作方法:选取表格的前2行的区域(不要选整行) - 公式 - 根据所选内容创建 - 首行。
设置完成后,打开左上角地址框,点击就可以跳转到指定的列。
取消方法:公式 - 名称管理器 - 按shift可以批量选取名称 - 删除
3、多列内容合并成一列
用途:多列内容合并成一列,一般是用&连接,然后再把公式转换成数值。下面的方法
【例】如下图所示,要求把左表的四列合并成到一列,如右表黄色区域所示。
操作方法:
复制表格,粘贴到任一个excel批注中。复制批注内的内容,再粘到单元格中。
4、一列转多表
用途:一列数据,分成N列显示。
【列】如下图,要把把A1:A20的内容转换为5列显示(每列4行)。
操作方法:

  • 在B1设置公式=A5(如果每列5行则=A6),然后把公式复制5列20行。


  • 复制公式 - 选择性粘贴 - 数值,把公式转换成数值。

  • 删除5行及以下的数据


注:如果需要把四行五列转换成一列,而在A5设置公式=B1,然后向下向右复制公式即可。


回复 支持 反对

使用道具 举报

 楼主| 发表于 2019-2-12 16:41:55 | 显示全部楼层

Excel分类汇总

办公达人速成



Excel分类汇总是把数据表中的数据分门别类地统计处理,无需建立公式,Excel会自动对各类别的数据进行求和、求平均值、统计个数、求最大值(最小值)和总体方差等多种计算,并且分级显示汇总的结果,从而增加了Excel工作表的可读性,使我们能更快捷地获得需要的数据并做出判断。今天我们先来了解下Excel简单分类汇总和Excel多重分类汇总。

1、Excel简单分类汇总

简单分类汇总指对数据表中的某一列以一种汇总方式进行分类汇总。例如,要汇总进货表中不同经手人所进货物的数量和金额总计,操作步骤如下。

步骤 1 我们先打开一个Excel工作表,对工作表中要进行分类汇总字段(列),这里对“经手人”列进行升序排序,如图 1左图所示。

图1

步骤 2 单击“数据”选项卡上“分级显示”组中的“分类汇总”按钮,如图1右图所示,打开“分类汇总”对话框。

步骤 3 在“分类字段”下拉列表选择要进行分类汇总的列标题“经手人”;在“汇总方式”下拉列表选择汇总方式“求和”;在“选定汇总项”列表中选择需要进行汇总的列标题“数量”和“金额”,如图2左图所示。设置完毕单击“确定”按钮,结果如图2右图所示。

图2

2、Excel多重分类汇总

对工作表中的某列数据选择两种或两种以上的分类汇总方式或汇总项进行汇总,就叫多重分类汇总,也就是说,多重分类汇总每次用的“分类字段”总是相同的,而汇总方式或汇总项不同,而且第2次汇总运算是在第1次汇总运算的结果上进行的。

例如,在图2所示汇总的基础上,要再次汇总不同“经手人”所进货物单价、数量和金额的最大值,可打开“分类汇总”对话框并进行图3左图所示的设置,单击“确定”按钮,结果如图3右图所示。出判断。

图3

Excel教程网提示您:要进行分类汇总的数据表的第一行必须有列标签,而且在分类汇总之前必须先对数据进行排序,以使得数据中拥有同一类关键字的记录集中在一起,然后再对记录进行分类汇总操作。


回复 支持 反对

使用道具 举报

 楼主| 发表于 2019-2-12 16:45:29 | 显示全部楼层

excel中的small函数和large函数,与其他函数结合还有这功能!

疏木excel学习



excel中,large函数small函数用的人比较少,但是用的少并不代表这两个函数没有用,反而十分有用,今天小编就专门写了这篇文章来介绍一下这两个函数,一起学习一下吧。

一、基本用法。对于large函数和small函数,都只有两个参数,分别为large(数值区域,返回的第几个最大值),small(数值区域,返回的第几个最小值)。如下图所示,查找倒数第二名的成绩,输入的函数公式为=SMALL(C2:C20,2),表示查找C2到C20单元格中第2小的数,结果为62。如果查找第三名的成绩,输入的函数公式为=LARGE(C2:C20,3),表示查找C2到C20单元格第3大的数,结果为80。

二,名次排列。利用large函数和small函数可以进行升序排列和降序排列。下图中如果要对倒数前三名进行排列,则第一个单元格输入公式=SMALL($C$2C$11,ROW(A1)),row(A1)表示行数从1开始,往下拖动填充公式第2个参数会发生变化,就会进行升序排列。如果要进行降序排列,函数公式为=LARGE($C$2C$11,ROW(A1))。下图都是只对倒数前三名和前三名进行排列,对所有数据进行排名往下填充即可。

三、条件查找。如果查找70分以下的最大值,输入的函数公式为=LARGE((C2:C11<70)*C2:C11,1)。C2:C11<70表示逻辑判断,返回值为{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE},false函数相当于0,true函数相当于1,乘以C2:C11后的值为{0;0;61;0;0;0;62;64;0;63},在这组数据中找最大的数,最后结果为64。同理,查找80分以上的最小值,函数公式为=SMALL((C2:C11>80)*C2:C11,1),即在数据{0;0;0;0;0;81;0;0;85;0}中找到最小值,计算结果为81。

四、求平均值。如果需要求前三名的平均值,输入的函数公式为AVERAGE(LARGE(C2:C11,{1,2,3}))。其中LARGE(C2:C11,{1,2,3})是一个数组函数,表示返回C2到C11单元格的前三个最大的数,返回结果为{85,81,80},外面的average即对这三个数求平均数。最后计算出前三名的平均值为82。同理,如果计算倒数前三名的平均值,输入的函数公式为=AVERAGE(SMALL(C2:C11,{1,2,3})),即=AVERAGE({61,62,63}),计算结果为62。

五、根据名次查找姓名。下图中,如何按照名次查找姓名?即怎样根据E列数据把查找结果显示在F列呢?此处的函数公式为=VLOOKUP(LARGE($C$2C$11,E3),IF({1,0},$C$2C$11,$B$2B$11),2,FALSE)。咋一看函数公式比较长,实际上也仅仅是large函数vlookup反向查找函数的嵌套。对于vlookup函数的第一个参数(LARGE($C$2C$11,E3)表示根据E3单元格对应的名次返回成绩,此处计算结果为85,第二个参数IF({1,0},$C$2C$11,$B$2B$11),表示调换C列B列的数据,第三个参数2表示位于第二列,第四个参数false表示精确匹配。向下拖动即可对公式进行填充,根据相应的名次查找到姓名。



回复 支持 反对

使用道具 举报

 楼主| 发表于 2019-2-12 16:47:32 | 显示全部楼层

excel中的DSUM函数——条件求和原来如此简单
疏木excel学习



excel中,DSUM函数表示根据条件查找或者求和,学习这个函数可以避免使用筛选等功能进行求和时破坏原数据,而且这个函数理解起来也十分简单,下面就和大家一起来学习一下吧。

一、基本查找。

对于dsum函数的三个参数为=dsum(数据区域,查找返回值的标题,条件区域),或者也可以写成=dsum(数据区域所在的列,求和数据所在列的列数,,条件区域)。现在就以下图为例说明一下dsum函数的基本用法。

下图中,如果查找赵云的工资,第一个函数公式=DSUM(A1:E12,E1,G1:G2),函数中第一个参数A1:E12表示查找的范围区域,第二个参数E1表示查找返回的结果是工资,第三个参数G1:G2表示条件区域,即查找的是姓名所在列赵云对应的工资。计算结果为5743,核对无误。

第二个函数公式=DSUM(A:E,5,G5:G6),第一个参数选择的区域所在列A到E列,第二个参数为5表示查找结果工资所在列为第5列。第三个参数为条件区域。返回结果同样是5743.

二、查找同列多条件的合计数。

如下图所示,如果要查找张飞和赵云的工资之和。输入的函数公式为=DSUM(A1:E12,E1,G1:G3),三个参数分别为数据区域,求和标题所在数据区域中的单元格,条件区域,此处张飞和赵云必须纵向排列,表示或的条件。

如果要查找本科和硕士学历的工资之和,输入的函数公式为=DSUM(A1:E12,E1,G6:G7),和根据姓名查找同样道理。

三、查找多列多条件的合计数。

如下图所示,要查找符合本科男、硕士女的工资合计。输入的函数公式为=DSUM(A1:E12,E1,G1:H3)。前两个参数和上述没有区别,第三个参数把条件区域全部选中即可。需要注意的是,此处男、本科或者女、硕士横向排列表示需要同时满足,是且的关系。男本科和女硕士是上下纵向排列,是或的关系。

四、查到日期之间的合计数。

如下图所示,如果要查找入职日期在2018年6月1日~~2018年8月31日之间的工资合计,输入的函数公式为=DSUM(A1:F12,F1,H1:I2)。此处,条件区域的日期用大于小于等于号做出判断,数据区域和标题单元格和前述没有变化。最后返回结果为24354,即表中表黄色单元格对应的工资。

五、利用通配符进行查找。

如下图所示,查找姓曹的和姓夏的(请忽略夏侯是复姓)工资水平。则条件区域中用姓加上通配符*后,输入函数公式=DSUM(A1:E12,E1,G1:G3)即可查到准确的结果。此处函数的含义仍然和上述没有区别。

这就是利用dsum函数进行查找求和,很简单吧?



回复 支持 反对

使用道具 举报

 楼主| 发表于 2019-2-15 20:03:06 | 显示全部楼层

excel中的8个shift键技巧,涨知识

疏木excel学习


谈到shift键,很多朋友可能一下会想到输入法切换或者shift加其他键组合输入一些特殊的字符或者标点符号,其实shift键在excel中也有很多用法,下面一起来看一下吧。

一、shift+鼠标拖动插入空白行列、插入剪切的行列、移动区域。

如下图所示,选中一列数据后按住shift键,鼠标移动到边缘区域,当光标变为两个横两个箭头时,向右拖动即可插入相应的空白列。

选中列后按住shift键,移动到边缘区域光标变为四个方向的箭头时,向右拖动即可把选中的列拖到相应的位置,且删除原先的位置。

选中相关区域后,按住shift键拖动,就可以移动相关区域,如果有隐藏区域,隐藏区域也会被移动。

二、shift+方向键(上下左右)选择单元格。

如图所示,按住shift后按方向键,可以选择相应方向的单元格。

三、ctrl+shift+方向键、home、end选择连续区域。

如图所示,按住ctrl+shift后,再按上下左右方向键及home、end键可以选择当前单元格到区域中最上、最下、最左、最右、第一个、最后一个单元格之间的所有区域。

四、ctrl+shift+加号键插入空白行列。

下图中,光标位置在区域内的任一单元格,按下ctrl+shift+加号,会弹出插入选项框,我们可以根据需要通过上下方向键选择活动单元格下移、右移,整行,整列,然后按enter键即可插入空白单元格或者空白行列。

五、按住shift点关闭可以关闭所有工作簿。

遇到要同时关闭所有excel文件的就不必一个一个关闭啦,当然,此处关闭工作簿时对于修改过的工作簿还是会弹出来是否保存的提醒。

六、shift+F11新建工作表。

按下shift+F11键后会在当前工作表前面插入一个新的工作表。动图如下,请注意下方工作表标签的变化。

七、shift+F2快速插入并编辑批注。

八、shift+插入形状插入标准图形或者shift+拖动图形水平垂直拖动。

如下图所示,要插入圆形或者正方形,直接插入的可能是椭圆或者矩形,而按住shift后插入就会是标准的形状,而且按住shift拖动只会在左右水平线,上下水平线移动,常用来多个形状对齐,比如设计一些流程图等。

这就是shift键常用的一些功能了,欢迎一起学习。






回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

本网旨在提供公益服务,所有帖子不代表本网观点和立场。帖子仅供参考,不构成投资建议,据此操作风险自担。如有侵权请联系邮箱:FJL09@163.com,一定及时核实后删除。

QQ|Archiver|手机版|小黑屋|职场休闲网 ( 黑ICP备06005472号

GMT+8, 2019-8-25 20:24 , Processed in 0.311746 second(s), 20 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表