Excel办公技巧分享qingr-2017.093

Excel表格制作要点,版面格式设置,更新日期、单位首行1和首列A留出，便于后期打印及查看格线是否完整设置自动筛选、冻结窗格外框用实线，文本内容多的用虚线，文本内容较少的可用实线去掉网格线，底色全白页面设置：版面、格式、页码、打印设置栏位内容少的在下面加总TOTAL,栏位内容多的在上面加总SUBTOTAL,excel单元格内容处理技巧,手工输入字体用蓝色，公式字体用黑色，负数在选定区域最后一行的最后一个单元格继续按Tab则会回到选定区域第一行第一个单元格。同样用Enter可以按列输入数据。,工作表之间的切换,在Excel中1个工作簿可以包含255张工作表，除了用鼠标选择外，还可以用快捷键当前工作表向前移一工作表Ctal+Pageup向后移一工作表Ctal+PageDown都可以连续往前移或是往后移哦,双击格式刷竟也能COPY文本(不是文本格式),步骤：选中“单元格”双击格式刷按住Ctrl键选择需复制的不连续目标区域按回车Enter键格式刷的作用其实没变，复制文本其实只是按Enter的结果。分解动作分为三步：1.定位在原数据上，双击格式刷：复制所有内容2.在目标区域按格式刷：选择性粘贴-格式3.按回车：粘贴所有内容。你会发现如果原单元格上有批注的话，一样也复制了，跟原数据按Ctrl+C选择区域按Enter是一样的,查找+格式刷的妙用,通常在数据校对时要用到查找，找到之后就用不同格式区分(如字体为红色、底纹为黄色等等)，如此重复。例如：先找到第一个数据并将字体改为红色,然后双击格式刷,当查找到其它相同数据时,再按Ctrl+A，excel就会将新找到的数据自动改为红色与之区分。具体操作为：Ctrl+F在查找栏输入要查找的值查找下一个设置格式（不要退出查找对话框）双击格式刷查找全部Ctrl+A关闭通常的方法是：Ctrl+F在查找栏输入要查找的值查找全部Ctrl+A，再设置格式。,快速移动/复制单元格,先选定单元格，然后移动鼠标指针到单元格边框上，按下鼠标左键并拖动到新位置，然后释放按键即可移动。若要复制单元格，则在释放鼠标之前按下Ctrl即可。,快速移动,利用鼠标，可使单元格指针快速移动很长距离。用鼠标双击单元格的下边框，单元格指针则快速向下移动，直到最后一个不是空白的单元格为止，若要使单元格指针在连续数据上向右快速移动，则双击单元格的右边框。,在excel中如何换行,有的时候，我们需要录入的内容很长，希望能够在同一个单元格内多行录入，可Excel的单元格不同于Word，没有换行的命令，直接用回车键不是在同一单元格内换行而是切换到了它下面的单元格.换行一般是通过选中格式菜单中的“单元格”-“对齐”的“自动换行”复选项，这种方法使用起来不是特别随心所欲，需要一步步地操作。还有一种方法是：当你需要重起一行输入内容的时候，只要按住Alt键的同时按下Enter回车键就可以了。,日期或时间,用斜杠或减号分隔日期的年、月、日部分，例如，可以键入96/5/9.同时按下“Ctrl”键与分号键“;”，再回车即可插入当前日期同时按住“Ctrl”键、“Shift”键与分号键;，再回车可以插入当前时间.,格式刷,Excel中格式刷的主要作用是复制某一单元格的格式（字体、字号、边框、对齐方式等）应用于其他区域，可将上述单元格选中，单击格式刷来取出所在位置或所选内容的文字格式，用这个刷子去刷别的单元格可实现格式的复制。每次刷完后，格式刷就变成不可用了，如果希望重复复制格式，就要不停地重复上述过程。有一个方法可避免上述的繁琐步骤：通过双击格式刷，可以将选定格式复制到多个位置。若要关闭格式刷，则按下“Esc”键或再次单击格式刷即可,填充有规律数据,要得到相同的一列或一行数据、文字或需要复制公式，只需在此列的第一个单元格内输入内容，然后用鼠标拖曳这个单元格右下角的填充柄，即可快速填充；若要得到等差数列，只需在此列的第一和第二单元格内输入等差数列的前两个数据并选定它们，用鼠标拖曳填充柄，即可快速填充。双击填充柄可以快速向下填充，直至空白元格。,调整行、列宽,单元格内的文本或数字在列宽不够时，超出宽度部分不显示或显示为#，这时可将鼠标指向此列列标右边界线，待鼠标指针变成左右的双向箭头时双击，可得到最适合的列宽，即列宽刚好容纳此单元格内最长的内容，用同样的办法可得到最适合的行高。,冻结窗口功能,由于Excel可以有最多65536行256列，有的时候我们在做表的时候习惯第一行写列名，第一列写行名，但是如果行或列太多的话，就看不到列名或行名了，这时可以使用冻结窗口功能使列名和行名冻结。,行列转置,选择一片区域复制，然后在要粘贴的地方选择编辑-选择性粘贴，勾选“转置”，你会发现行变成了列，列变成了行。,公式转化成数值,有时使用一排公式算出结果后，想要删除原数据，但又要保留结果，则可以先选中计算结果，复制，原地不动进行编辑-选择性粘贴，选择数值后确定。,标题行重复,在打印的时候有时需要每页都需要标题行，但是打印时第二页是不打印标题行的。点“文件页面设置工作表在“顶端标题行”选择你要重复的标题内容,同时设置多个工作表的页眉和页脚,有时要把一个Excel文件中的多个工作表设置成同样的页眉和页脚，分别对一张张工作表去设置感觉很烦琐。如果用下面的方法就可以一次将多个工作表中的页眉和页脚同时设置好。把鼠标移到工作表的名称处，然后点右键，在弹出的菜单中选择“选择全部工作表”的菜单项，这时再进行页眉和页脚设置就是针对全部工作表了。,快速输入大写中文数字,将光标移至需要输入大写数字的单元格中。利用数字小键盘在单元格中输入相应的小写数字(如12345)。右击该单元格，点击“设置单元格格式”，从弹出的“单元格格式”对话框中选择“数字”选项；然后从“分类”列表框中选择“特殊”从“类型”列表框中选择“中文大写数字”选项。最后单击“确定”按钮，这时输入的12345就自动变成“壹万贰仟叁佰肆拾伍”。,Excel中插入空白行,如果想在某一行上面插入几行空白行，可以用鼠标拖动自此行开始选择相应的行数，然后单击右键，选择插入。如果在每一行上面均插入一空白行，按住Ctrl键，依次单击要插入新行的行标按钮，单击右键，选择插入即可。,Excel中消除0值,在Excel中当单元格计算结果为0时，默认会显示0，这看起来显然有点碍眼。如果你想在数值为0时，显示为空白，可以用下面的方法。打开“工具选项视图”，取消“0值”复选项前的“”，确定后，当前工作表中的值为0的单元格将全部显示成空白。,快速选定“空白”和“数据”单元格,在Excel中，经常要选定空白单元格，逐个选定比较麻烦，使用下面的方法可以快速选定。打开“编辑定位”，在“定位”窗口中，按下“定位条件”按钮；选择“定位条件”中的“空值”，再按“确定”，空白单元格即被全部选定。如果要选定只含数据的单元格，在上面方法的“定位条件”窗口中，选择“常量”，再点“确定”，含有数据的单元格全部选定。,快捷键：Ctrl+F打开对话框,自动调整小数点,如果你有一大批小于1的数字要录入到Excel工作表中，如果录入前先进行下面的设置，将会使你的输入速度成倍提高。单击“工具”菜单中的“选项”，然后单击“编辑”选项卡，选中“自动设置小数点”复选框，在“位数”微调编辑框中键入需要显示在小数点右面的位数。在此，我们键入“2”单击“确定”按钮。完成之后，如果在工作表的某单元格中键入“4”，则在你按了回车键之后，该单元格的数字自动变为“0.04”。方便多了吧！此时如果你在单元格中键入的是“8888”，则在你结束输入之后，该单元格的数字自动变为“88.88”。,让“自动更正”输入统一的文本,如果经常需要输入某些固定的文本，可以用下面的方法解决。1.执行“工具自动更正”命令，打开“自动更正”对话框。2.在“替换”下面的方框中输入随意字符，在“替换为”下面的方框中输入想要替换的字符，再单击“添加”和“确定”按钮。3.以后如果要输入这些特定文本字，就可以用替换字符就可以了。,让数据按需排序,如果你要将员工按其所在的部门进行排序，如果按部门名称排序一般是按拼音顺序排序，而非我们需要的顺序。可以用下面的方法解决此问题。1.执行“工具选项”命令，打开“选项”对话框，进入“自定义序列”标签中，在“输入序列”下面的方框中输入部门排序的序列，单击“添加”和“确定”按钮退出。2.选中“部门”列中任意一个单元格，执行“数据排序”命令，打开“排序”对话框，单击“选项”按钮，弹出“排序选项”对话框，按其中的下拉按钮，选中刚才自定义的序列，按两次“确定”按钮返回，所有数据就按要求进行了排序。,WPS中快捷键Ctal+D，再按S，打开对话框,用连字符“其实也可以输入一个“1”，或者true,VLOOKUP使用示范vlookup就是竖直查找，即列查找。通俗的讲，根据查找值参数，在查找范围的第一列搜索查找值，找到该值后，则返回值为：以第一列为准，往后推数查找列数值的这一列所对应的值。这也是为什么该函数叫做vlookup（v为vertical-竖直之意，lookup即时英文的查找之意）现有如下手机的每日销售毛数据（图左），A分销商需要提供四个型号的销售数据（图右）,这个时候，如果一个一个人工查找，因为这里提供的数据数量很少，但是其实工作中这种数据很庞大，人工查找无疑浪费时间，而且不能让A分销商相信所提供数据的准确性。,第一步：选中要输入数据的单元格，=VLOOKUP(H3,$A$3:$F$19,5,FALSE)如图,参数解释：H3为我们想查找的型号，即iphone5。为什么要写H3，而不是直接写iphone5，因为方便公式进行拖拽填充，以及保证准确性。,参数解释：5从我们的数据源第一列起，我们要查询的7/31号的销量在我引用的第一列（即型号列）后面的第五列。注意：这里的列数是从引用范围的第一列做为1，而不是以A列作为第一列。,参数解释：false在开始已经强调，我们在日常生活中，大部分都使用的是精确的匹配到想要查询的值，慎用true，使用true的结果可是意想不到的惊喜哦。老老实实的使用false（或输入数字0）作为精确匹配,参数解释：$A$3:$F$19为我们需要在此范围内做查找，为什么要加上绝对引用呢，因为下面的ip4和剩余的查找都引用这个区域，即我们的数据源，加上了绝对引用后，就可以对公式进行直接的拖拽（复制/粘贴）。,MID函数,MID函数返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。语法MID(text,start_num,num_chars)Text是包含要提取字符的文本字符串。Start_num是文本中要提取的第一个字符的位置，即从左起第几位开始截取（用数字表达）。Num_chars指定希望MID从文本中返回字符的个数，即从左起向右截取的长度是多少（用数字表达）。,从身份证号码中提取出生日、性别信息,如果你有大量的人员信息要录入,可以从身份证号码中提取出生日性别信息，方法如下：先把光标指针放到“出生日期”列的单元格内，这里以C2单元格为例。然后输入“=MID(B2,7,4)&”年”&MID(B2,11,2)&”月”&MID(B2,13,2)&”日”（注意：外侧的双引号不用输入，函数式中的引号和逗号等符号应在英文状态下输入）。,MID函数与IF/TEXT/LEN函数的配合使用,身份证号码有15位和18位之分，借助IF函数来判断。如果单元格身份证号是15位的话，提取出生年月日=MID(身份证号,7,6)如果单元格身份证号是18位的话，提取出生年月日=MID(身份证号,7,8)在F2单元格输入=IF(LEN(A2)=18,MID(A2,7,8),IF(LEN(A2)=15,MID(A2,7,6),错误)如果身份证号不是15位或者18位就显示错误。注释：15位身份证号码（1-2位省、自治区、直辖市代码；3-4位地级市、盟、自治州代码；5-6位县、县级市、区代码；7-12位出生年月日,比如870126代表1987年1月26日,这是和18位号码的不同之处；13-15位为顺序号）18位身份证号码（1-2位省、自治区、直辖市代码；3-4位地级市、盟、自治州代码；5-6位县、县级市、区代码；7-14位出生年月日，比如19670401代表1967年4月1日；15-17位为顺序号，其中17位男为单数，女为双数；18位为校验码，0-9和X，随机产生）,F2单元格得出结果19870420，如果想要身份证号为18位的结果显示为1987-04-20格式，使得身份证号为15位的结果显示为87年04月20日格式。需要用到TEXT函数。在E2单元格输入公式=IF(LEN(A2)=18,TEXT(MID(A2,7,8),0000-00-00),IF(LEN(A2)=15,TEXT(MID(A2,7,6),0000年00月00日),FALSE)最后按CTRL+SHIFT+ENTER键结束。,MID与FIND函数配合使用，提取单元格括号里内容,A列是公司名称（公式名称里面包括地市），需要提取A列单元格括号里面的地址信息到D列。我们先用find函数查找(和)在单元格的位置。,在C2单元格输入公式=FIND(）,A2)得出)在“深圳连硕自动化（松岗）”中的位置为11,在B2单元格输入公式=FIND(（,A2)得出(在“深圳连硕自动化（松岗）”中的位置为8,用MID函数来综合FIND函数提取廊坊，在D2单元格输入公式=MID(A2,FIND(（,A2)+1,FIND(）,A2)-1-FIND(（,A2)最后按CTRL+SHIFT+ENTER键结束。注释：FIND(（,A2)+1表示从单元格内容中位于“（”后面一位开始截取。FIND(）,A2)-1-FIND(（,A2)就表示“（”与“）”之间位置相差几个。,INT函数、TRUNC函数,TRUNC函数将数字的小数部分截去，返回数值。语法TRUNC(number,num_digits)Number需要截尾取整的数字。Num_digits用于指定取整精度的数字。Num_digits的默认值为0。,INT函数将数字向下舍入到最接近的整数语法INT(number)Number需要进行向下舍入取整的实数。,特别提醒：在取值时，均不进行四舍五入,INDEX函数,INDEX函数返回数组中指定的单元格或单元格数组的数值。语法：INDEX(array，row_num，column_num)Array为单元格区域或数组常数Row_num为数组中某行的行序号，函数从该行返回数值Column_num是数组中某列的列序号，函数从该列返回数值,在Excel中index函数是用来引用我们所需的信息，主要分连续区域和非连续区域内的引用两种，下面我就通过实例来分享一下index函数的使用方法。,连续区域中index函数的公式格式是=index(array,row_num,column_num)，其中array表示我们要引用的区域，row_num表示要引用的行数，column_num表示要引用的列数，最终的结果就是引用出区域内行列交叉处的内容。,如下图所示，引用A2到C6区域中第二行第三列的信息。,在问号所在的单元格输入=INDEX(A2:C6,2,3)。,所得的结果是6，那么在看下是否符合，看到A2到C6区域的第二行第三列是C3单元格，其中的数值就是6。函数中A2:C6表示引用的区域，2和3分别是第二行，第三列。所以最终引用到的结果就是交叉处的C3单元格6。,非连续区域中index函数的公式格式是=index(（array_1,array_2,array_3.array_n),row_num,column_num,array_num)，其中array表示我们要引用的非连续区域，其必须用小括号括起来，row_num表示要引用的行数，column_num表示要引用的列数，array_num表示第几个区域。,还是以上文中实例看一下index函数的应用。将该区域分成两个区域以表示非连续的区域。,在问号处单元格内输入=INDEX(A2:C3,A5:C6),2,3,2)。,结果如下图所示，引用到的是第二个区域A5到C6中第二行第三列交叉处单元格中的内容5，非连续区域与连续区域的不同处是要把分散的区域用小括号括起来，同时在函数括号最后面加入一个array_num表示引用第几个区域。下图基本可以完整解释非连续区域中index函数的使用。,Left函数,主要功能：从一个文本字符串的第一个字符开始，截取指定数目的字符。参数说明：text代表要截字符的字符串；num_chars代表给定的截取数目。应用举例：假定A1单元格中保存了“我喜欢天极网”的字符串，我们在C1单元格中输入公式：=LEFT(A38,3)，确认后即显示出“我喜欢”的字符。特别提醒：此函数名的英文意思为“左”，即从左边截取，Excel很多函数都取其英文的意思。,函数名称：Right函数则是从右边开始截取,Dcount函数,主要功能：返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。参数说明：Database表示需要统计的单元格区域；Field表示函数所使用的数据列（在第一行必须要有标志项）；Criteria包含条件的单元格区域。应用举例：如图1所示，在F4单元格中输入公式：=DCOUNT(A1:D11,语文,F1:G2)，确认后即可求出“语文”列中，成绩大于等于70，而小于80的数值单元格数目（相当于分数段人数）。特别提醒：如果将上述公式修改为：=DCOUNT(A1:D11,F1:G2)，也可以达到相同目的。,Dcount函数,主要功能：计算符合指定条件的单元格区域内的数值和。参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式；Sum_Range代表需要计算的数值所在的单元格区域。应用举例：特别提醒：如果把上述公式修改为：=SUMIF(C2:C63,女,D2