电子表格培训

1、Microsoft Office Excel 学习课程讲师：张瑞胜EXCEL值得学习的原因:普及性标准性兼容性为什么要学习EXCELEXCEL主要功能EXCEL功能分类EXCEL水平层级积极的心态正确的方法充足的资源如何学习EXCEL 学习EXCEL要有积极的心态成为EXCEL高手的捷径-积极的心态心态决定一切培养兴趣,挑战自我重视压力,迎接竞争方法决定成败循序渐进善用资源学以致用多多实践正确方法学习EXCEL要有正确的方法学习资源EXCEL联机帮助互联网（论坛等）书刊杂志周边人群EXCEL学习资源 本次EXCEL学习主要内容EXCEL基本操作篇STEP 1EXCEL操作技巧篇STEP 2EX

2、CEL函数及公式入门篇STEP 3EXCEL公式进阶篇STEP 4Microsoft Office ExcelExcel基本操作篇讲师：张瑞胜Excel启动与退出EXCEL窗口组成EXCEL工作表操作EXCEL数据输入EXCEL单元格设置EXCEL单元格定位与选择初识Excel 2003EXCEL的最基本操作内容 Excel启动与退出双击桌面快捷图标 开始程序 Microsoft Excel开始运行 excel关闭按钮 文件退出 快捷键 ALT+F4 EXCEL的退出方法：EXCEL的启动方法： 标题栏1EXCEL 窗 口 组 成EXCEL2003版本窗口主要有以下几个方面组成：6菜单栏名称框

3、2行标3状态栏5工作表区47工具栏8编辑区9列标10滚动条方法：插入 工作表 ：右键工作表标签 插入 工作表方法：编辑 删除工作表 ：右键 删除 （为永久性删除）方法：左键拖拽移动、CTRL+拖动复制 ：编辑/右键 移动或复制工作表插入工作表删除工作表重命名移动复制工作表方法：格式 工作表重命名：右键 重命名工 作 表 操 作 数据的录入文本的录入： 一般情况下，EXCEL可以自己识别录入的内容是否为文本格式，但当要录入的数字较长，比如身份证时，要先将单元格设置为文本格式再输入，或者输入前先输入“” 符号即可 。一个单元格可容纳汉字16000个，半角字符32000个）数字的录入：数字：数字键区

4、直接输入分数：先输入0 空格 再输入分数负数： 在数字前面输入“-”号即可 小数：直接输入“.”号即可(0.25可输入为.25）日期：日期用“/”号来输入或用“-”号来输入。当前日期为“Ctrl+;”时间：时间使用“：”来输入。当前时间为“Ctrl + Shift+:” 在名称框内直接输入单元格名称回车，若是区域则使用：如：A1：B2 定位：单元格：单击 列：单击列标 行：单击行号工作表：Ctrl +A 左上角的全选按钮 连续区域：按Shift+单击（左上角、右下角）不连续区域：先选中第一个需要选择的区域，然后按Ctrl+单击或拖选选择：单元格的定位与选择 13单元格设置数据类型:常规(正常情

5、况下使用)数值、日期文本(公式没有显示结果)自定义(功能强大)字体设置：字体、字号及特殊效果上标及下标输入对齐方式：对齐方式文本控制边框及颜色设置：边框设置(单元格斜线设置)单元格颜色填充Microsoft Office ExcelExcel操作技巧篇讲师：张瑞胜单击“格式”菜单，选择“单元格”命令，然后选择“对齐”选项卡，选中“文本控制”标题下的“自动换行”复选框。自动换行：在要换行的地方单击鼠标，按 ALT+ENTER 键来强制换行。按 下ALT+ENTER其实是插入了一个换行的符号，它是ASCII字符集中的第十个字符。强制换行：如何让单元格里面内容换行 选中要增加斜线的单元格,设置单元格

6、格式边框 选择斜线线条注意:在单元格文本对齐方式上,垂直对齐一定要选”靠上”对齐.如何给单元格加斜线 如何改变按回车键后鼠标移动的方向Description of the contents打开“工具”菜单，选择“选项”命令，单击“编辑”选项卡，然后从“按Enter键后移动方向”下拉列表框中选择“向右”或其它，单击“确定”即可。方法Description of the contents 禁止“零”显示或打印: 财务计算中不允许输入或计算出来的“0”显示或打印。如果某个工作表有这种要求，可以单击“工具”菜单中的“选项”命令，打开“选项”对话框中的“视图”选项卡，将其中的“零值”取消，“确定”以后就

7、可以达到目的了。 禁止“零”显示或打印 如何快速选择大范围的单元格区域首先在“名称”框中输入该操作区域的起始单元格名称代号，然后输入该操作区域的最后一个单元格名称代号，中间用“：”冒号分开。最后按下“Enter”键，这样以这两个单元格为对角的长方形区域就会被快速的选定。先选中区域的左上角单元格，按着shift键点击右下角单元格，也可以选定以这两个单元格为对角的长方形区域。方法一方法二禁止复制隐藏行或列方法一：如果你复制了包含隐藏列(或行)的一个数据区域，然后把它粘贴到一个新的工作表，那么Excel把隐藏列也粘贴过来了。要想避免这种情况，可以选取你要复制的数据区域，然后选择“编辑定位”命令，单击

8、“定位条件”按钮，选中“可见单元格”选项，再复制和粘贴这个选定区域就会得到你所希望的结果。方法二：选中区域后按Alt+; 快速选中可见单元格，然后复制。转置选择性粘贴复制巧用转置功能粘贴数据在我们输入数据中,经常需要行列转换,通常情况下我们都是手工逐个复制,其实EXCEL里面转置功能可以帮我们轻松实现.超15位数字输入：直接输入会自动使用科学计数法.如不想使用科学计数法可以打开“单元格格式数字”选项卡，选择“分类”下的“自定义”，然后在“类型”下选择“0.00”即可数字以文本形式输入：在输入较长数字或以0开头的数字时先输入单撇号“”或将单元格先设置为文本格式.分数：先输入0 空格 再输入分数。

9、日期：日期用“/”号来输入或用“-”号来输入。当前日期为“Ctrl+;” 时间：时间使用“：”来输入。当前时间为“Ctrl + Shift+:” 公式输入：“插入”“对象”“新建”“microsoft公式3.0”数据的输入如何为自己的文档加密单击“文件”菜单栏中的“保存或者（另存为）”命令后，在弹出的“保存或者（另存为）”的对话框中输入文件名再单击这个对话框中“工具”栏下的“常规选项”按钮，在弹出的“保存选项”的对话框中输入自己的密码这里要注意，它提供了两层保护，如果你也设置了修改权限密码的话，那么即使文件被打开也还需要输入修改权限的密码才能修改。Excel文件的加密与隐藏 如果你不愿意自己的

10、Excel文件被别人查看，那么你可以给它设置密码保护，采用在保存文件时用加密的方法就可以实现保护目的，给文件加密的具体方法为： FristSecondAttention隐藏单元格中的所有值当我们需要将单元格中所有值隐藏起来时，可按此方法操作即可：单击“格式单元格”命令，选择“数字”选项卡，在“分类”列表中选择“自定义”，然后将“类型”框中已有的代码删除，键入“;”(3个分号)即可。其实单元格数字的自定义格式是由正数、负数、零和文本4个部分组成。这4个部分用3个分号分隔，哪个部分空，相应的内容就不会在单元格中显示。自动选择下拉菜单如果能自动出来让我选择该多好呀!当我们在单元格里面经常要输入一些固

11、定的内容时,我们怎样来输入比较简单呢?路径: 数据数据有效性设置允许序列来源 二级数据有效性公式：=OFFSET($A$1,1,MATCH(A2,$1:$1,0)-1,COUNTA(OFFSET(A:A,MATCH(A2,$1:$1,0)-1)-1,1)输入出错自动提醒设置 我们有没有办法做到当我们往电子表格里面输入数据时,在输入数据不符合我们要求的情况下自动给我们提醒呢?路径：数据有效性设置允许公式以下设置为所指定区域输入的内容只能出现一次：找回因响应而异常关闭的文件如果正在使用的Excel 2003因故停止响应，如何找回没有保存到的内容呢：打开“开始程序Microsoft Office工具

12、”菜单，单击其中的“Microsoft Office应用程序恢复”命令。选中对话框中停止响应的应用程序即可。快速输入相邻单元格数据如果当前单元格要填充的数据与周围单元格(或区域)中的数据相同，可用以下方法填充：按Ctrl+D键，可以将上方的数据填入当前单元格；按Ctrl+R键，将左侧的数据填入当前单元格；单击“编辑填充”子菜单中的“向上填充”命令，可将下方的数据填入当前单元格；将右侧的数据填入当前单元格。如果要填充的是一个区域，可先将含有数据的区域选中，再按类似方法操作即可。如待填充列的左侧或右侧有内容，首先在待填充列的第一个单元格内输入内容，然后将这个单元格选中。将光标指向选中标记右下角的填

13、充柄，待空心十字光标变成黑色十字光标以后双击鼠标。方法一：复制公式或数据,选中要填充的内容,粘贴.方法二：首先在待填充列的第一个单元格内输入内容，用鼠标从此单元格开始选起,直到要填充的最后一个单元格,然后按 Ctrl+D 即可全部填充.方法三：如何快速填充相同公式/数据如何在多个单元格中输入相同内容选定要在多个单元格中输入同一个公式/数据的区域，在某一单元格中输入公式/数据后安组合键Ctrl+Enter，那么所选区域那的所有单元格中就都输入同一公式/数据了。同时对多个单元格执行相同运算我们常常要改变某些单元格中的单位,如几个单元格同时乘以1000,如何操作比较方便呢?如何快速删除工作表中的文本

14、框 我们在日常工作中,常常发现有一些表格看里面内容不多,但运行起来极慢,而且文件而较大,一般有几M以上,在这种情况下,多半是表格中存在着大量看不到的文本框,可用F5 定位条件对象确定 这样就可以选中所有文本框,然后按 Delete键即可.为什么输入公式后没有显示结果一般情况下我们输入公式后会自动求出结果,但有时输入后却只显示出我们输入的公式,这是为什么?原因:单元格格式设置成文本格式了.解决:将单元格格式调整为常规,再在该单元格输入状态下按回车键即可.如何查找完全匹配的单元格有时我们想在表格里面查找某些内容,但因为我们要查找的内容模糊匹配的单元格太多,往往不容易找到,可在查找时点击选项,再选中

15、单元格匹配即可.条件格式的应用有时候我们想要当我们的数据满足一定值的时候能够自动填充一种颜色，以便与其它值区分开来，此功能可能使用条件格式帮助我们完成。条件格式可以设置各种条件，这对我们每天都发生变动的数据特别有用。如何快速把工作表复制到另外一个工作簿中先打开两工作簿，一要复制，另一被复制，在要复制的工作簿中，右击左下角需复制工作表的名称，点“移动或复制工作表“,在工作簿栏中点需要复制到的工作簿，在下一栏中选复制到工作簿的位置，在“建立副本” 前打钩，按确定即可。查找替换中的*和?问题excel查找替换中的*和?是通配符,分别可以替代多个字符和单个字符,但要查找替换*和?自身怎么实现呢?有一个

16、绝窍:在*和?前加即可使它们失去通配符的作用,也就可以查找替换它自身了剔除表里所有空格的最快方法用查找替换来快速完成，查找里按一下空格键，取代里什么也不输入，然后点击全部取代即可。ALT系列快捷键ALTF11 可以调出VBA编辑器alt+enter拆分同一单元格内多行文(即强制换行)按ALT+向下键，即可得到本列中原有文本的列表ALT+0178= ALT+0179= ALT+0188= ALT+0177= ALT+0137= 操作方法是按住ALT键，直接在数字小键盘（注意，这是很多人作不出来的原因）上连续输入数字，松开ALT键就出现字符了。此方法只能够在EXCEL2003版本上面使用,在EXC

17、EL2007版本上面或笔记本电脑上面都无法使用.CTRL系列快捷键ctrl +9隐藏一行单元格ctrl+0隐藏一列单元格Ctrl+Shift+9取消隐藏行Ctrl+Shift+0取消隐藏列快速输入今天的日期：ctrl+;快速输入当前的时间：ctrl+shift+;ctrl+1快速打开格式对话框。+可以将当前工作表中的公式显示出来,再按+则恢复.CTRL系列快捷键Ctrl+Tab 可在程序与程序，表簿与表簿之间转换打开。使用ctrl+PageDown和ctrl+PageUp可以在工作表之间向前向后切换快速到达A1：CTRL+Home快速到达行首：CTRL+（向左小箭头）快速到达行尾：CTRL+（

18、向右小箭头）快速到达列首：CTRL+（向上小箭头）快速到达列尾：CTRL+（向下小箭头）CTRL系列快捷键选定从工作表开始到结尾的方法：选中开始单元格（如A21），同时按下ctrl+shift+end.用Ctrl+右键头和Ctrl+下箭头快速选定区域:如果一张表的第一行和第一列没有空格，一次即可，有几个空格按几次方向键!删除单元格/行/列：选中单元格，ctrl+“-”（减号）插入单元格/行/列：选中单元格，ctrl+shift+“+”（加号）按住ctrl+shift拖动单元格=复制+插入单元格+粘贴Microsoft Office ExcelExcel函数与公式学习篇讲师：张瑞胜 前言要真正发

19、挥 Excel的威力，必须掌握Excel的灵魂函数。不会使用Excel的函数，就不是一个真正的会用Excel的用户。什么是函数Excel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。例如，SUM 函数对单元格或单元格区域进行加法运算。对EXCEL函数的理解数据规则EXCEL函数结果A3200if(A3100,40,0)D3D3=40函数的作用：将指定的数据按一定的规则转化为需要的结果学习函数的方法(一)学

20、以致用：用才是目的就是你想要和将要用到的东西先学。比如你根本用不上财务、工程函数，没必要一下子就去看那些专业性很强的东西，而最好应该是从逻辑判断和查找和引用这两类函数入手，这样就容易入门了。学习函数的方法(二)除了“求助”式学习，还要“助人”式的学习：大家可以到网上关于EXCEL学习论坛上与其它EXCEL爱好者一起学习。只要有时间，少看一会儿电视、少聊一会儿QQ、少跟同事吹一会儿牛，到论坛上看看有没有别人不懂而你懂的，助人助己，有了越来越多的“求助”者给你免费提供了练习的机会，你就会综合各种思路的比较，就会有了自己一些想法，你的水平肯定与日俱增。学习函数的方法(三)多看函数帮助：各个函数帮助里

21、面有函数的基本用法和一些“要点”，以及对数据排序、引用类型等等的要求。在打开EXCEL的情况下按“F1”键就可以打开系统自带的帮助功能。当然，EXCEL自带的帮助并不囊括所有函数的细微之处，很多还需要我们在使用中慢慢体会。学习函数的方法(四)方法：庖丁解牛：函数的参数之间用逗号隔开。这些逗号就是“牛”的关节，先把长公式大卸八块之后逐个看明白了再拼凑起来读就容易多了。示例：RIGHT(C28,LEN(C28)-FIND(“ ”,C28)公式中，“C28”是RIGHT函数的第一个参数，“LEN(C28)-FIND(“ ”,C28) ”是RIGHT的第二个参数，这样逐个分解再逐个函数去学习，对于我们

22、学习长公式很有帮助。学习函数的方法(五)F9键: “F9”键用来“抹黑”公式对解读尤其是数组公式有非常强的作用，不过如果公式所含数据区域太大（比如上百行）你可以改变一下区域。具体方法：比如下面这个简单数组公式=sum(if(A1:A30,B1:B3)，用鼠标在编辑栏把把A1:A30部分“抹黑”，按下F9键，就看到True;True;False（假设A3不满足），表示if的条件是这么3行1列的逻辑值数组。别忘了，看完之后按ESC取消，否则公式就变了。学习函数的方法(六)公式求值：就是工具公式审核公式求值那个有fx的放大镜，与“F9”功能基本相同，能一步步看公式运行的结果。公式求值：就是工具公式审

23、核公式求值那个有fx的放大镜，与“F9”功能基本相同，能一步步看公式运行的结果。函数的组成一般来说,一个完整的函数由函数名加参数组成,参数与参数之间一律用英文逗号间隔，在单元格内使用等号作为函数的开始，函数可以进行嵌套,例如：函数名A1,参数1C:F,3,0参数2参数3参数4=VLOOKUP()什么是参数？参数可以是数字、文本、形如 TRUE 或 FALSE 的逻辑值、数组、形如 #N/A 的错误值或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。 术语说明 相对引用指公式中的单元格位置将随着公式单元格的位置而改变。如：A3.绝对引用是指公式和函数中的位置是固定不变

24、的.绝对引用是在列字母和行数字之前都加上美元符号”$”,如 $A$4,$C$6相对引用混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用.如:行变列不变: $A4，列变行不变:A$4混合引用单元格的引用方式绝对引用公式中的运算符 算术运算符算术运算符号运算符含义示 例+(加号)加2+3=5-(减号)减3-1=2*(星号乘3*2=6/(斜杠)除6/2=3%(百分号)百分号50%(脱字号)乘方43=43=64公式中的运算符2. 文本运算符 “&”号,可以将文本连接起来.3. 比较运算符比较运算符运算符含义示 例=(等号)相等B1=C1,若B1中单元格内的值确实与C1中的值相等,则产生逻辑真

25、值TRUE,若不相等,则产生逻辑假值FALSE(小于号)小于B1(大于号)大于B1C1,若B1中数值为6,C1中数值为4,则条件成立产生逻辑真值TRUE,否则产生逻辑假值FALSE=(大于等于号)大于等于B1=C1(不等号)不等于B1C1=(小于等于号)小于等于B1=90,A,IF(B4=80,B, IF(B4=70,C,IF(B4=60,D,F)IF(B4=90, A, （ )IF()IF(B4=80,B, )IF()IF(B4=70, C, )IF()IF(B4=60, D, F) SUMIF( )函数语法作用：根据给定条件对指定单元格求和。 =SUMIF(range,criteria,s

26、um_range)函数名条件计算的单元格区域求和的条件表达式求和的单元格区域共三个参数（第二可以是公式，函数或者值等）注意：1、 Criteria 为确定对哪些单元格相加的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32 或 apples；2、可以在条件中使用通配符、问号 (?) 和星号 (*)。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符 ()。 COUNTIF( )函数语法作用：计算区域中满足给定条件的单元格的个数。 =COUNTIF(range,criteria)函数名计数区域计数条件共二个参数（第二个参数可以

27、是公式，函数或者值等）条件公式写法文本型单元格 =COUNTIF($A$4:$B$14,*)等于50 =COUNTIF($A$18:$B$32,50)小于50 =COUNTIF($A$18:$B$32,&$B$23)等于B23单元格的值 =COUNTIF($A$18:$B$32,$B$23)两个字符并且第2个是B =COUNTIF($A$38:$B$46,?B)包含B =COUNTIF($A$38:$B$46,*B*)等于“你好” =COUNTIF($A$38:$B$46,你好)包含D46单元格的内容 =COUNTIF($A$38:$B$46,*&$D$45&*) MID( )函数语法作用：返

28、回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。 =MID( text, start_num, num_chars)函数名提取字符的文本或单元格要提取的第一个字符的位置提取字符个数共三个参数（都可以是公式，函数或者值等）与该函数类似功能的函数还有：1、 RIGHT（）函数：根据所指定的字符数返回文本字符串中最后一个或多个字符，表达式为：RIGHT(text,num_chars)2、LEFT（）函数：根据所指定的字符数，返回文本字符串中第一个字符或前几个字符表达式为：LEFT(text,num_chars)。 文本函数应用Branch IDPostal RegionDRS-CF-

29、476CFDRS-WA-842WAHLT-NP-190NPFull Branch CodePostal RegionDRS/STC/872STCHDRS/FC/111FCS/NORTH/874NORTHHQ/K/875KSPECIAL/UK & FR/876UK & FRB2=MID(A2,5,2)从A2单元格中的第五个字符开始，取两个字符从A2单元格中,使用FIND函数找到第一个“/”的位置及第二个“/” 的位置，使用第一个“/”的后一个字符为开始字符，第二个“/”位置减去第一个“/”位置再减1得出要取字符的长度。B2=MID(A2, FIND(/,A2)+1,FIND(/,A2,FIND(

30、/,A2)+1)-FIND(/,A2)-1 )FIND(/,A2)+1FIND(/,A2,FIND(/,A2)+1)-FIND(/,A2)-1FIND(/,A2,FIND(/,A2)+1) IND(/,A2)-1-FIND(/,A2)-1更多常用函数学习LENTODAYOFFSETCOUNTIFCOUNTSUMNOWINDEXINTMATCHORANDSUMPRODUCTRANKCHOOSEMicrosoft Office ExcelExcel公式进阶篇EXCEL公式与函数的区别在日常工作中,EXCEL函数确实可以帮助我们解决很多问题,提高了我们的效率,但单个函数的功能相对简单,我们利用EXC

31、EL函数的嵌套功能,将不同的函数结合使用,即EXCEL公式,可以大幅度的EXCEL函数的功能, EXCEL公式才是EXCEL精髓,也是我们学习的难点.数组公式简述（一）数组公式：是用于建立可以产生多个结果或对可以存放在行和列中的一组参数进行运算的单个公式。它的特点就是可以执行多重计算,它返回的是一组数据结果。由于一个单元格内只能储存一个数值,所以当结果是一组数据时,单元格只返回第一个值,如:23=23,24,25,22如果你需要用到所有的运算结果时,要么用多个单元格去分别返回,如:23=INDEX(23,24,25,22,1)24=INDEX(23,24,25,22,2)25=INDEX(23

32、,24,25,22,3)22=INDEX(23,24,25,22,4)要么用某些函数来取其共性,如SUM, MAX/MIN,等：94=SUM(23,24,25,22)25=MAX(23,24,25,22)数组公式简述（二）参数：数组公式最大的特征就是所引用的参数是数组参数,包括区域数组和常量数组。区域数组：是一个矩形的单元格区域,如 $A$1:$D$5。常量数组：是一组给定的常量,如1,2,3或1;2;3或1,2,3;1,2,3注意：数组公式中的参数必须为矩形,如1,2,3;1,2就无法引用了数组公式的输入：当公式完成输入后，同时按下CTRL+SHIFT+ENTER,数组公式的外面会自动加上大

33、括号予以区分.如对右表进行条件求和：=SUM($A$1:$310)*($B$1:$B$3)表示为当A列数字大于10时，对B列对应的数字进行求和，结果为55.注意：有的时候,看上去是一般应用的公式也应该是属于数组公式,只是它所引用的是数组常量，对于参数为常量数组的公式,则在参数外有大括号,公式外则没有,输入时也不必按CTRL+SHIFT+ENTER，如：55=SUM(10;20;3010)*11;22;33)101120223033数组公式计算过程（一）产品编号产品单价产品数量AA1100BB2150CC3200AA1250BB2300问题：求左表中产品AA的总价。公式：=SUM(IF($B$2

34、:$B$6=AA),($C$2:$C$6)*($D$2:$D$6),0)结果：1*100+1*250350计算过程：AATRUE1100BBFALSE2150CCFALSE3200AATRUE1250BBFALSE2300IF函数先判断$B$2:$B$6里=AA的参数,返回的结果，用公式表述为:=SUM(IF(TRUE;FALSE;FALSE;TRUE;FALSE,($C$2:$C$6)*($D$2:$D$6),0)使用IF函数逐个判断：$B$2:$B$6=AA数组公式计算过程（二）2. 对返回TRUE的行项执行下一个运算,($C$2:$C$6)*($D$2:$D$6)， 对于返回FALSE的

35、,则直接返回0TRUE1100100=1*100FALSE2150=0FALSE3200=0TRUE1250250=1*250FALSE2300=03. 到了这一步,公式返回出的是一组符合要求的数字了,该组数字用数组常量表示为：100;0;0;250;04. 最后用SUM函数把这一组数组常量相加,得到最后结果： 350=SUM(100;0;0;250;0)5. 在加减乘除的运算中TRUE=1,FALSE=0,所以逻辑值能直接参与运算。该公式可简化为： =SUM($B$2:$B$6=AA)*$C$2:$C$6*$D$2:$D$6) 计算结果同样为350.如何根据计划排产情况得到开始时间与完成时间

36、如果我们作为一个计划员，在排计划的时候，常常需要填写上每个订单的开始生产时间与完成时间，这个时间是根据每天的实际排产情况来得到的，这个时间我们一般是手工填写，但往往计划更改后，这个时间就会忘记更改，影响到其它人员的使用。我们有没有办法根据后面排产情况，自动得到开始时间与完成时间呢？方法肯定是有的，要知道，EXCEL除了必须手工输入的数据不能简化外，其它都有可能实现。上线时间公式：=IFERROR(MIN(IF(S3:AJ3,S$1:AJ$1),)先使用IF函数判断所确定区域中的非空单元格，当条件为真时，返回对应列的时间，得到一个数组，然后使用MIN函数来选出最小的日期，为了避免出现错误的符号，

37、最后使用了IFERROR函数，让出错时返回空值。预计上线时间预计完成时间12月10日12月1112月12日12月13日12月14日12月15日12月16日12月17日12月10日12月12日4030 970 12月12日12月13日3606 1394 12月13日12月13日3000 12月14日12月17日2136 3000 3000 1864 12月10日12月10日3000 完成时间公式：=IFERROR(LOOKUP(100000,S3:AJ3,$S$1:$AJ$1),)此公式主要是活用了 LOOKUP 函数， LOOKUP 函数语法为： LOOKUP(lookup_value, lo

38、okup_vector, result_vector)， 功能为LOOKUP 的向量形式在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。当找不到 lookup_value，则它与 lookup_vector 中小于或等于 lookup_value 的最大值匹配，如果所有数据都小于lookup_value值，即返回最后的一个数值。注塑机台计划排产公式(一)我们在做注塑机台计划的时候,一般是将产品按机台排出来,同吨位不同机台之间可以互相调整,如果我们要排产的机台较多,产能紧张的时候,每天的排产,就会花费我们较多的时间,因为每一次调整,都要重新计算产品的

39、预计投产时间与预计完成时间,现我们利用EXCEL强大的计算功能,让EXCEL来帮助我们减少一些重复繁杂的工作,把我们有更多的时间去思考问题,处理异常。通过公式的设置,我们可以让EXCEL自动帮我们完成以下的工作:1.不同的机台调整后,EXCEL自动帮我们把前面的机台号更改过来;2.自动计算出不同产品的预计投产日期;3.自动计算出不同产品的完成日期,并且可以把星期天不考虑在生产日期之内.4.我们每天要做的就是输入当天完成的数量,把完成的产品删除,把不能满足生产需要的产品调整到合适的机台,其它的工作都可以让EXCEL来完成.机台工作指令型号产品名称欠数日产能投产日完成日1#M10A094B2263

40、38大身112514402/142/141#Z261980A236338大身505514402/142/19-2#U16A105A116238H大身628012002/142/202#ZP09009157031大身1512002/202/20-3#F50A062A076138底座533048002/142/163#Z262014A096138底座204548002/162/16注塑机台计划排产公式(二)机台号自动改变公式：=IF(COUNTIF(B2,=),-,COUNTIF(INDIRECT($A$&1):INDIRECT($A$&(ROW()-1),-)+1如果两个条件都成立时,代表该机台

41、不是这个机台的第一个产品,他的要求投产日等于上一个产品的完成日加上1/6,即加上四个小时的转模时间；3.为了避免在使用中剪切单元格时，直接引用单元格造成引用混乱，公式中使用了INDIRECT函数，该函数与ROW函数结合，得出一个始终不变的引用。注塑机台计划排产公式(四)完成日公式:=IF(ISERROR(O2+L2/M2),O2+L2/M2-INT(O2-1)/7)+INT(O2+L2/M2-1)/7)+(MOD(INT(O2),7)=1)机台工作指令型号产品名称欠数日产能投产日完成日1#M10A094B226338大身112514402/142/141#Z261980A236338大身505

42、514402/142/19-2#U16A105A116238H大身628012002/142/202#ZP09009157031大身1512002/202/20-3#F50A062A076138底座533048002/142/163#Z262014A096138底座204548002/162/16思路：1.一般来说,完成日期是很简单的,直接等于开始生产日期+欠数/日产能即可；2.本例中使用ISERROR来进行出错判断,当结果不是日期格式时,返回空值；3.我们生产时,实际上星期天常常是不上班的,所以要与实际生产一致,完成时间还要减去星期天的时间,公式为:-INT(O8-1)/7)+INT(O8+

43、L8/M8-1)/7)+(MOD(INT(O8),7)=1)“4.EXCEL里面,每个日期都是一个数字,日期减1再除以7,代表的是从1900年1月1日起,到当天为止,经过了多少个星期天.这个公式减开始时间过去的星期天,再加上完成时间经过的星期天,就是开始时间与完成时间相隔的星期天数量.5.加上(MOD(INT(O8),7)=1,是防止特殊情况的出现,当要求投产日期为星期天时(这个情况只有在前一个产品的完成时间是在星期六的晚上八点钟之后,下一个订单的开始时间加上四小时的转模时间后就到了星期天),这一天是不生产的,所以完成时间加上1.如何计算一列数据中不重复数据的个数你是否试过想从一列有重复内容的

44、数据中，想知道到底有多少个不重复的内容呢？在内容较少的时候，逐个来数是我们大多的选择，但当内容较多的时候，你是否有办法来很快地完成呢？序号客户名称1韦小宝2张无忌3郭靖4黄蓉5韦小宝6令狐冲7杨过8东方不败9胡斐10郭靖11东方不败12韦小宝13张无忌14杨过计算公式：=SUM(1/COUNTIF(B4:B17,B4:B17)思路：1、先使用数组公式:COUNTIF(B4:B17,B4:B17),按名称逐个在整列数据中查找得出每个名称的个数;2、使用1/每个名称个量,将名称超过一个的变成分数,如本题中韦小宝计数为3,经过1除以3后每个都变成了1/3;3、使用SUM函数将所有1/COUNTIF(

45、B4:B17,B4:B17)返回的数据加起来,就得到了总个数;4、本公式巧妙之处就在于使用1/COUNTIF(B4:B17,B4:B17),将大于1的每个名称根据期实际个变成分数,最后加起来刚好等于1。如何提取不重复数据客户名称不重复的清单韦小宝韦小宝张无忌张无忌郭靖郭靖黄蓉黄蓉韦小宝令狐冲令狐冲杨过杨过东方不败东方不败胡斐胡斐END郭靖END东方不败韦小宝张无忌杨过在日常工作中，我们常常需要从一列数据中，找到不重复的数据来，如何使用EXCEL公式来帮助我们快速完成呢？细看本例，你以后遇到相似问题就可以做老师了！计算公式：=IF(SUM(1/COUNTIF($A$7:$A$20,$A$7:$A

46、$20)=ROW(A1),INDEX($A$7:$A$20,SMALL(IF(ROW($A$7:$A$20)-6=MATCH($A$7:$A$20,$A$7:$A$20,0),ROW($A$7:$A$20)-6,0),ROW(A1),END)思路：1、使用IF函数与“(ROW($A$7:$A$20)-6=MATCH($A$7:$A$20,$A$7:$A$20,0)”逐个判断A列中的数据是否是第一次出现,如果是第一次出现就使用ROW($A$7:$A$20)-6返回一个数字,这个数字为该名称从表头下移单元格的个数,如果不是第一次出现,返回0,这是一个文本,是不数字0.2、使用SMALL函数从小到大

47、把原来ROW($A$7:$A$20)-6返回的数据组成一个数组。3、使用INDEX函数从其引用数据区域“$A$7:$A$20”中，根据SMALL函数得到的数组来确定返回的单元格内容。4、在最后，使用IF函数与不重复计算公式“SUM(1/COUNTIF($A$7:$A$20,$A$7:$A$20)”判断是否已全部返回，当单元格数量超过了该列数据不重复值数量时，显示“END”，公式到此完成。如何进行多条件求和(一) 条件求和的公式,我们一般都会使用SUMIF,但是SUMIF只能进行单个条件求和,而数组公式能帮助你进行多条件求和。求产品BB的8月份产量计算公式：=SUM(IF($A$6:$A$19=

48、BB)*(MONTH($B$6:$B$19)=8),($D$6:$D$19),0)产品编号生产日期产品单价产品数量AA2000/6/151100AA2000/6/201125BB2000/6/302150BB2000/7/102175CC2000/7/153200CC2000/7/203225AA2000/7/301250AA2000/8/101275BB2000/8/152300BB2000/8/202325CC2000/8/303350CC2000/10/103375DD2000/10/154400DD2000/10/304425思路：1、条件表达式为TRUE时,在运算中可当作1,条件表达

49、式为FALSE时,运算中可当作0,多个表达式可以进行乘法运算,只有所有表达式都同时满足时,最终结果才会为TRUE.2、用IF()来判断,如果$B$7:$B$20=BB而且MONTH($C$7:$C$20)=8的话,那么我们就取$E$7:$E$20中对应的值,否则就让它等于0。如何进行多条件求和(二)除使用上面的数组公式外，其实还有很多其它的方法可以实现多条件求和，可以使用的函数有SUMPRODUCT函数或SUMIFS函数（2007或以上版本才能使用该函数）求产品BB的8月份产量计算公式： =SUMPRODUCT($A$6:$A$19=BB)*(MONTH($B$6:$B$19)=8)*($D$

50、6:$D$19)产品编号生产日期产品单价产品数量AA2000/6/151100AA2000/6/201125BB2000/6/302150BB2000/7/102175CC2000/7/153200CC2000/7/203225AA2000/7/301250AA2000/8/101275BB2000/8/152300BB2000/8/202325CC2000/8/303350CC2000/10/103375DD2000/10/154400DD2000/10/304425思路：1、该公式是活用了SUMPRODUCT函数的功能， SUMPRODUCT函数的功能是在给定的几组数组中，将数组间对应的元

51、素相乘，并返回乘积之和。2、本公式中，其中任何一个条件不成立时，将会返回0,经过相乘后，对应该行的数据就会变成0；如果所有结果都成立时，各条件相乘结果为1,乘以需要求和的单元格，最终结果还是不变，最后返回乘积之和就是我们要的结果。注意：虽然2007版开始，增加了多条件求和的函数：SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, .)，但在本例中，使用SUMIFS函数无法求出结果来，因为我们在月份条件中，是使用了month函数才能得到结果，而SUMIFS函数的区域中是不允许使用其它函数作为其参数的。指定第个满足条件查询正常情况下，我们使用VLOOKUP函数，如果要查找的内容有两个或两个以上的记录时，VLOOKUP函数只会得到第一个记录数据，假如我们想