EXCEL培训资料.docx
- 文档编号:2894175
- 上传时间:2022-11-16
- 格式:DOCX
- 页数:14
- 大小:1.16MB
EXCEL培训资料.docx
《EXCEL培训资料.docx》由会员分享,可在线阅读,更多相关《EXCEL培训资料.docx(14页珍藏版)》请在冰豆网上搜索。
EXCEL培训资料
1.01开篇问题:
你是如何汇总数据的
单条件求和,通常用透视表和SUMIF函数;
两个条件求和,可以用透视表和SUMIFS;
三个条件计数,可以用函数COUNTIFS和SUMIFS。
1.0任督二脉:
数据清单表与报表
无合并不报表,Excel表格里有两种表,一种叫清单,一种叫报表。
先清单,后报表。
清单是行与行之间不能用空白行,列与列之间不能用空白列,它是纵向扩展的,不能横向扩展。
1.01技法管窥:
有效性下拉框及二级有效性
数据有效性制作下拉框:
为了能更方便、快捷的做好表格,需要在职位这列做个有效性下拉框。
List清单在当前表的操作步骤:
先准备一个清单在当前表AB列。
(注意:
准备的清单List,建议是不放在当前表,如果放在当前表可以在很靠后的地方,常用手法是选中清单整列,然后把它的字体变成白色,但是不要把这一列隐藏)。
选中C列,点击数据→数据有效性,出现一个对话框。
允许选序列,来源直接选AB2单元格到AB列最后一个有字单元格。
操作:
把鼠标放在AB2单元格后按快捷键ctrl+shift+↓,可直接到AB列的最下面有字的单元格。
如果鼠标在最下面单元格就按ctrl+shift+↑,可到达那一列最上面单元格。
快捷键ctrl+shift+↑↓表示快速选取。
返回,确定。
这时C列就有有效性下拉框了,所以第一步选中很关键。
现在要把C1单元格的有效性给去掉,就按上面步骤2的操作,出现对话框后点一下全部清除,再确定,这个时候C1的有效性就被清除了,这样C列(职位)的有效性就做好了。
List清单不在当前表的操作步骤:
打开List表格,选中C2到最下面有字单元格部分(C1不需要选),当前单元格应该叫C2-C37,但是这个名字过于学术化,我们需要给它起个通俗的名字。
快速取名字的方法:
选中单元格后,把鼠标移到上面这个名称框上,把原来的C2删除,输入职位(建议用中文、中文的全拼和英文的全称),名字输好后一定要记得回车确认,这时的C2-C37有一个新的名字叫职位。
标准取名字的方法:
选中单元格后,公式→名称管理器→新建,出现对话框,名称处输入职位,确定,这是标准的做法。
选择F列做,选中F列,点击数据→数据有效性。
设置允许序列(序列即为有下拉框的都是序列,是比较基础的,其他的内容也很多,可以自行研究),来源处输入=职位(名称一定要用等于号把它引出来)。
确定,这样F列的有效性下拉框就完成了。
设置性别有效性的操作步骤:
选中性别列,点击数据→数据有效性。
设置允许序列,来源输入男,女,未知。
需要关注下输入信息,输入信息界面有个标题,标题处填写请选择性别!
,输入信息处填写男,女,未知。
出错警告界面也要注意下,样式处一定要填写停止(即一旦出错误了就不让继续操作)。
确定,这时会发现性别列的任何一个单元格都有提示,其他列都没有。
二级联动下拉框:
设置一级科目有效性的操作步骤:
批量定义名称,选中范围E1-J10,点击公式→根据所选内容创建,快捷键是ctrl+shift+F3,出现对话框,创建名称首行打√。
确定,出现对话框,再一直点击是,这样就定义好了。
点击公式→名称管理器,出现对话框,每个名称对应的引用位置都是一样的。
改动引用位置,选中需要改动的那条名称,鼠标放到引用位置处,把最后的截止位置改为那一列的有字单元格的位置。
回车确认,这样依次改下去就可以了。
(如图3.2.5)
注意:
一级科目的项目内容和所有二级科目的名称都是相匹配的,但不是一回事,是间接关系。
先做一级科目,选中B列或直接选中B2-B20,点击数据→数据有效性,设置允许序列,来源=一级科目,确定。
再做二级科目,选中C2-C20,(注意选择的行数必须要跟一级科目是一样的),点击数据→数据有效性,设置允许序列,来源=indirect($B2)。
注意:
函数indirect表示间接返回,$B2表示锁死B列,不锁行。
确定。
选择的时候必须是一级科目先选,二级科目才能选。
1.02技法管窥:
连体数据的快速分开与合成
连体数据的快速分开:
需要把上图的B列分开,分开以后应该是四个部分,就要再分出三个部分,分列是不能自动插入空白列的,要手动插入空白列,那就要在C列前插入3个空白列。
分开单元格的操作步骤:
选中C、D、E三列,右键插入列,这样就插入了三列空
选中B列,点击数据→分列,出现对话框,文件类型选分隔符号,下一步。
分隔符号选其他-,下一步。
选中日期列,修改上面的列数据格式日期YMD。
完成,确定替换目标单元格内容。
连体数据的快速合成:
分开的列需要合成记得一定要加分隔符号。
合成单元格的操作步骤:
在E列输入公式=A2&“-”&B2&“-”&C2&“-”&D2,回车。
把鼠标放在E2单元格的右下角双击到底,这样可以看出所有的日期都是数字它其实代表的是某天的日期。
实例:
在空白单元格按快捷键ctrl+;会出现当前的日期,再选中这个日期按快捷键ctrl+shift+!
就会转变成数字。
在excel中1就代表了1900年1月1日。
快捷键:
ctrl+;代表当前的日期;ctrl+shift+!
日期转变成数字;ctrl+shift+#数字转变成日期。
用函数TEXT(文本函数,把某一个数字变成另一个形式)来改变日期的呈现方式,即=A2&“-”&B2&“-”&C2&“-”&TEXT(D2,“yyyy/mm/dd”),回车,双击。
注意:
Text(数字“yyyy-mm-dd”)中间的横杠可以是斜杠,也可以是点。
1.03技法管窥:
定位空值与Ctrl+Enter实现批量补充空白区域
空白处快速求和操作步骤:
选中D-I列,按快捷键F5或crtl+g定位。
定位条件选空值,确定。
注意这时的鼠标不能乱点,可以滚动但是不能点。
按快捷键alt+=,求和就出来了,字体颜色是可以改变的。
日期、合并单元格、空白补全操作步骤:
选中A列,点击开始→合并后居中,这时单元格合并现象就没有了。
选中A列,点击数据→分列,文件类型选分隔符号,下一步。
分隔符号选Tab键,下一步。
选中下面的内容,上面的列数据格式改为日期YMD。
完成,这样就变成了日期。
如果有英文的,直接快捷键crtl+shift+#,如果有其他格式问题,按快捷键crtl+1,自定义设置,就可以了。
选中A列,快捷键F5,定位条件空值,确定。
在活动单元格A3处输入=+↑,保持这种状态。
最后按crtl+enter,这样空白就补全了,选中A列,复制,右键,选择性粘贴,数值,Esc退出。
2.01函数灵魂:
单元格地址引用4种类型
目的:
求预测利润,在C6输入公式后右拖下拉到其他单元格,而不是一个格一个格的输
算法是取不同的合同总价*不同的利润率-固定支出
若在C6输入=B6*C5-G5后进行拖动,则其他单元格的结果是错误的,因此需要重新考虑引用关系
B6相对引用;$B$6绝对引用;$B6和B$6都是混合引用,F4键是切换几种引用的快捷键。
简言之$就是锁定,它在列号前,锁定列,在行号前,锁定行
所以C6中正确的公式是=$B6*C$5-$G$5
2.02函数灵魂:
单(多)条件计数COUNTIFCOUNTIFSSUMPRODUCT
单(多)条件计数——COUNTIF:
单条件计数函数COUNTIF,有两个参数,第一个条件区域,第二个是条件值。
条件区域不建议选整列,即源数据!
C:
C,因为COUNTIF是易失性函数,会不断重算,使操作速度放慢。
C2=COUNTIF(源数据!
$C$2:
$C$1226,$B2)
快捷键介绍:
Ø快速选取某行(列)数据的:
CTRL+SHIFT+←→↑↓
Ø快速到达:
CTRL+←→↑↓
Ø快速全选:
CTRL+A
为了避免数据增加而反复修改条件区域的大小,可将行号后加零,即源数据!
$C$2:
$C$12260
COUNTIF中第二个参数条件值的扩展说明。
在本例中是分城市求订单数,条件值应该等于B列的城市,$B2是省略的写法,完整的写法是“=”&$B2
条件判断的符号除了=还有>、<、>=、<=、<>
因此若将原题改为求销售量大于15的订单数,则公式应写为C2=COUNTIF(源数据!
$E$2:
$E$1226,“>”&15)
若将原题改为求销售量大于20且小于50的订单数,则需用两个COUNTIF相减,或者用COUNTIFS来完成。
单(多)条件计数——COUNTIFS
多条件计数函数COUNTIFS参数与COUNTIF类似,COUNTIFS(条件区域1,条件值1,条件区域2,条件值2……)
以本题为例,分城市分产品求订单数,公式可写为D3=COUNTIFS(源数据!
$C$2:
$C$12260,$C3,源数据!
$D$2:
$D$12260,D$2),右拖下拉到其他单元格即可。
单(多)条件计数——SUMPRODUCT
COUNTIFS是2007版才有的函数,用03版的要求多条件计数可以用SUMPRODUCT,其结构为SUMPRODUCT((条件区域1=条件值1)*1,(条件区域2=条件值2)*1……)
Ø其中条件区域=条件值,=也可换成>、<等
Ø条件区域=条件值的结果是一串True或False,*1之后会相应转化为1或0,把*1换成+0也可达到相同的效果,若想省略*1就要将各参数相乘,结构变化为SUMPRODUCT((条件区域1=条件值1)*(条件区域2=条件值2)……)
Ø书写时建议先将括号画好,再在中间填写内容,SUMPRODUCT(()*())猫脸
SUMPRODUCT字面理解为先乘积再求和,原来的用法是多行(列)相等个数的数据两两相乘再相加。
以本题为例,分城市分产品求订单数,公式可写为K3=SUMPRODUCT((源数据!
$C$2:
$C$1226=$J3)*(源数据!
$D$2:
$D$1226=K$2)),右拖下拉到其他单元格即可。
单(多)条件计数——动态扩展范围
前面提到在行号后加零扩大条件区域的大小,以减少后续修改的方法,其实EXCEL可以实现在清单的数据增加时,函数范围自动变化的,前提是需对清单进行设置。
将鼠标移到清单内任意一个有内容的单元格,点”插入”选项卡中”表”即可。
表样式也可按喜好更换。
在表范围内按Tab键到最右列后,会自动移到下一行的最左列,在右下角的单元格会有一个小三角,
继续增加数据,可以看到原先的源数据!
$C$2:
$C$1226,会自动变成源数据!
$C$2:
$C$1227
若需将表转化为普通区域,右键单击表中任一单元格,选择”表格→转换为区域”即可。
2.03函数灵魂:
单(多)条件求和SUMIFSUMIFSSUMPRODUCT
单(多)条件求和——SUMIF
单条件求和函数SUMIF,它有三个参数,第一个是条件区域,第二个是条件值,第三个是求和区域。
求和区域可以只写该区域首个单元格,若写范围则必须与条件区域所选范围一样大。
C3=SUMIF(销售清单!
$C$2:
$C$133,$B3,销售清单!
$E$2)或C3=SUMIF(销售清单!
$C$2:
$C$133,$B3,销售清单!
$E$2:
$E$133)
用SUMIF完成多条件求和:
Ø在清单中增加辅助列,将条件区域1&条件区域2……
Ø多个条件值也同样用&连接起来
用SUMIF对产品、月份、业务员三个条件求和
Ø将销售清单的F列变为辅助列,
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 培训资料