Excel_Liu

2020年1月30日星期四Excel进阶培训教程HR:WinsLiu2020年1月30日星期四page2/基础概念－单元格格式1.单元格格式设定A.设定单元格的属性:常规,文本,数值B.文本和数值属性的确定公式:TYPE()(数字1文本2逻辑值4)日期也表示为数字型C.要强行输入文本型数据,在输入前先输入‘D.文本和数值的互换的方式1).“1”+0=11&””=“1”2).数值转文本:=TEXT(B7,0.00)2.00=TEXT(B7,”0%”)2%InformationisTycoConfidential&ProprietaryDoNotReproduceorDistribute2020年1月30日星期四page3/基础概念－单元格引用2.单无格引用A.相对引用公式中的相对单元格引用（例如A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。例如，如果将单元格B2中的相对引用复制到单元格B3，将自动从=A1调整到=A2。B.绝对引用$单元格中的绝对单元格引用（例如$A$1）总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，需要将它们转换为绝对引用。例如，如果将单元格B2中的绝对引用复制到单元格B3，则在两个单元格中一样，都是$A$1。2020年1月30日星期四page4/基础概念－单元格引用C.混合引用混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用$A1、$B1等形式。绝对引用行采用A$1、B$1等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。例如，如果将一个混合引用从A2复制到B3，它将从=A$1调整到=B$12020年1月30日星期四page5/2020年1月30日星期四page6/2020年1月30日星期四page7/常用函数1.从左截取字符串:LEFT()2.从右截取字符串:RIGHT()3.从指定位置开始截取特定数目的字符:MID()4.返回文本字符串的长度:LEN()5.四舍五入:ROUND()6.日期:Now()7.返回内容格式:TYPE()8.将几个文本字符串合并:CONCATENATE(),&9.将数字的小数部分截去，返回整数:TRUNC()10.统计非空单元格:COUNTA()11.计算包含数字的单元格个数:COUNT函数2020年1月30日星期四page8/重要讲解函数1.OFFSET2.LOOKUP与VLOOKUP3.COUNTIF4.SUMIF5.FREQUENCY2020年1月30日星期四page9/2020年1月30日星期四page10/2020年1月30日星期四page11/2020年1月30日星期四page12/2020年1月30日星期四page13/常用函数2020年1月30日星期四page14/图表特殊效果制作1。图表背景横向分割2。图表背景纵向分割3。动态图表2020年1月30日星期四page15/2020年1月30日星期四page16/2020年1月30日星期四page17/2020年1月30日星期四page18/常用函数VLOOKUP()公式的使用VLOOKUP(要查找的内容,在那个区域进行查找,找到后返回区域中第几列,查找属性)A.区域中第一列是包含查找内容的数据列,这一列可以是文本、数字或逻辑值但不区分大小写B.确定区域时注意使用单元格的绝对引用$符C.如果查找属性为TRUE或省略，则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于要查找的内容的最大数值D.如果查找属性为FALSE，VLOOKUP将只寻找精确匹配值。如果找不到精确匹配值，则返回错误值#N/A。如果查找区域第一列中有两个或多个值与要查找的内容匹配，则使用第一个找到的值。E.要查找的内容和查找区域的第一列要是相同的数据类型2020年1月30日星期四page19/EXCEL常用小技巧•1。怎样快速插入/删除多行多列？•2。F4的功能•3。在Excel中快速移动行或列•4。如何将按行排列的数据转换成按列排列？•5。页面设定的使用（照相机功能）•6。多个工作表的页眉和页脚同时设置7。复制或移动整张工作表•8。怎样快速输入“√”、“×”、“平方”、“立方”•9。间隔排序•10。数字录入2020年1月30日星期四page20/EXCEL常用小技巧•11。用下拉列表快速输入数据•12。双击的运用（调整列宽、快速移动、填充有规律数据）•13。在Excel中强制换行•14。让Excel页脚设置的页码从第二页开始•15。快速实现Excel表格奇偶行交错着色•16。利用弹出菜单可以迅速切换到任意工作表•17。在Excel中制作打钩的方框•18。按Shift键把Excel单元格区域转换为图片•19。快速实现整块数据的移动•20。在单元格中自动输入时间和日期2020年1月30日星期四page21/小技巧：如何将按行排列的数据转换成按列排列如何将按行排列的数据转换成按列排列:选中要转换的数据，复制它，然后把光标移到新数据的起始位置，点鼠标右键,选”选择性粘贴”,再选窗口中的“转置”选项,按确定就好了.2020年1月30日星期四page22/小技巧：利用弹出菜单可以迅速切换到任意工作表如果工作簿有太多的工作表，不能同时在屏幕上看到它们的名字，用滚动的方法切换工作表就比较麻烦了。右击左下角的工作表滚动箭头，如图，利用弹出菜单可以迅速切换到任意工作表。2020年1月30日星期四page23/小技巧：在Excel中制作打钩的方框1.在菜单栏上右击，在弹出的菜单中选择“窗体”，将打开浮动工具栏“窗体”。(Forms)2.在“窗体”浮动工具栏中，单击一下“复选框”，如图。这时鼠标变成十字形。3.用鼠标在“理科”后单击并拖动一下，会制作一个复选框，删除复选框后的文字，单击复选框的边框并拖动到合适的位置。2020年1月30日星期四page24/小技巧：复制或移动整张工作表•1.打开目标工作簿，切换到包含要复制工作表的源工作簿。•2.右击该工作表的标签（如：sheet1），并在弹出的菜单中选择“移动或复制工作表”。•3.在“将选定工作表移至”下拉列表中，选择要复制到的工作簿，如要将选定工作表复制到新工作簿中，则单击下拉菜单中的新工作簿。•4.勾选“建立副本”复选框，如果不选择此复选框的话，该工作表将被移动走而不是复制。2020年1月30日星期四page25/小技巧：在Excel中快速移动行或列•在编辑Excel表格时，有时需要调整表格行或列的位置。用下面的方法可以快速实现，以移动行为例，假设要把工作表的第3行移动到第10行，操作如下：单击第3行的行标选中第3行，把鼠标指向第3行的边缘，当指针变为双十字箭头时，按住“Shift”键，同时按住左键向下拖动鼠标。在拖动过程中，会出现一条虚线，当虚线到达第10行上边缘时，屏幕上会出现“10:10”的提示。此时，松开鼠标左键，即可完成行移动。2020年1月30日星期四page26/小技巧：在Excel中快速插入多行和多列•Ctrl+/-2020年1月30日星期四page27/小技巧：在单元格中自动输入时间和日期•日期和时间的输入形式太麻烦，而且有时记不清楚。请问有什么方法让系统自动输入时间和日期吗？••答：可以执行以下方法来实现：选中需输入的单元格，直接按下“Ctrl+；”组合键可输入当前日期；如果直接按下“Ctrl+Shift+；”组合键即可输入当前时间；当然也可以在单元格中先输入其他文字然后再按以上组合键，如先输入“当前时间为：”再按下“Ctrl+Shift+；”组合键，就会在单元格中显示“当前时间为：16：27”这样的结果。2020年1月30日星期四page28/小技巧：快速实现Excel表格奇偶行交错着色•2.执行“格式→条件格式”，打开“条件格式”对话框。单击打开“条件1”的下拉列表，选择“公式”，在右侧的文本框中输入公式“=MOD(ROW(),2)=1”。再单击“格式”按钮，打开“单元格格式”对话框，指定一种颜色。公式的作用是选出行号为奇数的行，凡符合此公式条件的单元格即采用所指定的颜色格式。•3.单击“添加”按钮，添加第二个条件公式“=MOD(ROW(),2)=0”，再按上面的方法指定第二种颜色。即凡选中区域中行号为偶数的行，将以此颜色填充。如图2。2020年1月30日星期四page29/让Excel页脚设置的页码从第二页开始（1）•1.单击菜单“文件→页面设置”，选择“页眉/页脚”选项卡。•2.单击“自定义页脚”按钮，在中间的文本框中输入“&[页码]+1”，注意输入时不包括引号，且“+1”的后面有一空格。如果没有这个空格，首页的页码将显示为“11”的式样。依此类推，如果需要从第8页开始设置，则输入“&[页码]+7”即可。2020年1月30日星期四page30/让Excel页脚设置的页码从第二页开始（2）1.单击“文件→页面设置”，然后单击“页面”选项卡。2.在“起始页码”右侧的文本框中，将“自动”改为所需的工作表开始页的页码。2020年1月30日星期四page31/根据身份证号提取出生日期=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),IF(LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),错误身份证号))2020年1月30日星期四page32/小技巧：按Shift键把Excel单元格区域转换为图片•具体操作方法如下：•首先选中需要复制成图片的单元格区域，然后按住Shift键，依次选择“编辑→复制图片”命令，接着弹出“复制图片”窗口，选择“图片”单选项后点击“确定”按钮。这时选定的表格区域就已经被复制成图片了。•再到需要使用这张图片的地方选择“粘贴”命令即可(或者按Shift键再选择“编辑→粘贴图片”命令)，比如将其在Word中粘贴。•另外，如果选择“如打印效果”单选项，那么被转换为图片的数据区域是按照打印效果来处理的，比如设置有粗边框，那么粘贴出来的图片上就会有粗边框。