Excel基础到公式运用个人整理Word下载.docx

1、1）在需要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。 2）将单元格引用、公式或函数作为数组常量输入。 3）赋予需要单一数值的运算符或函数一个数值区域。 3、#DIV/0！公式被零除时或公式中包含的除数零。 4、#NAME？1）删除了公式中使用的名称，或者使用了不存在的名称 2）名称拼写错误 3）在公式中输入文本时没有使用双引号 4）在区域的引用中缺少冒号 5、#N/A 出现原因：函数或公式中没有可用数值 6、#REF！单元格引用无效 7、#NUM！公式或函数中某个数字有问题（由公式产生的数字太大或太小，Excel不能表示） 8、#NULL！为两个并不相交的区域指定交叉

2、点公式：通常以“=”开头 “+”、“”号开关也可使用Excel识别其内容为公式 Ctrl+ 可以在公式与值的显示方式间切换输入数据：当用户输入数据时在工作窗口底部状态栏左侧会显示“输入”字样，原有编辑栏左边会出现“”按钮，单击“”表示取消输入，单击“”表示确定输入，单击“”时和按Enter有相同的效果，但按Enter键后会自动跳到下一单元格，按 则不会。 Excel中默认按Enter键是向下激活单元格，也可设置成其它，方法：文件选项高级编辑选项方向编辑单元格内容：双击或者按F2，也可激活目标单元格后在工作窗口编辑栏编辑 可以在键盘上按Insert键切换“插入”或“改写”模式日期输入：系统默认将

3、029之间的数字识别为2000年2029年，将3099之间的数字识别为1930年1999年。 用分隔符“ . ”来输入日期不能进行运算。数据输入技巧：强制换行Alt+Enter 在多个单元格输入相同的数据：同时选中要输入的单元格，输入数据，在输入结束时按Ctrl+Enter 分数输入：整数+空格+分数 可在编辑栏查看分数值 上下标：选中要设置的上下标值，Ctrl+1设置单元格格式/字体 在列表中选择：当要在目标单元格中输入此单元格上方的某一单元格内的内容时可按Alt+下方向键或点鼠标右键选从下拉列表中选择 自动填充：输入数据用填充柄下拉（顺序填充，可在填充完后点右下角的填充菜单进行类型选择）

4、双击填充柄按住Ctrl键下拉为复制填充 Ctrl+D向下填充 Ctrl+R向右填充（需框选出填充区域后使用） 使用填充菜单：开始编辑填充序列粘贴：粘贴选项，复制、粘贴后在右下角出现的粘贴选项 选择性粘贴，复制后在粘贴前点击鼠标右键选择的选择性粘贴选项查找/替换：Ctrl+F/Ctrl+H（可设置格式） 查找通配符应用：Excel中支持两个通配符 * 和 ？ 例：要查找以E开头以L结束可用E*L 以EX开头L结束的五个字母单词可用EX?L隐藏单元格数据：选中要隐藏的单元格点击右键设置单元格格式数字自定义在类型处打上三个半角分号（到这一步虽隐藏了单元格表面显示的内容但在编辑栏还是可以看到它本身的内

5、容）再点右键设置单元格格式保护隐藏审阅保护工作表加密（即可完全隐藏单元格的内容）禁止编辑表格中部分区域：单击行号和列标交叉处的按钮（全选整张工作表Ctrl+A）Ctrl+1保护取消勾选锁定OK选中要禁止编辑的区域Ctrl+1保护先勾选隐藏再勾选锁定审阅保护工作表加密OK批注中插入图片：审阅新建批注选中批注外边框点击右键设置批注格式颜色与线条填充填充效果图片打印批注：页面布局页面设置（点右下角小箭头）工作表打印批注-公式、函数-公式以”=“号以引号，必须且只能返回值。 函数按特定算法计算的产生一个或一个组结果的预定义的特殊公式。公式组成序号公式说明1=15*3+20*2包含常量运算的公式2=A1

6、*3+A2*2包含单元格引用的公式3=单价*数量包含名称的公式4=SUM（A1*3,A2*2）包含函数的公式在输入公式的状态下，鼠标选中其他单元格区域时，被选区域将作为引用自动输入到公式里。运算符符号例-算术运算符：负号=8*-5=-40%百分号=60*5%=3乘幂=32=9 =16（1/2）=4*和/乘和除=3*2/4=1.5+和-加和减=3+2+5=0= 、=、=比较运算符（比较大小）：等于、不等于、大于、小于、大于等于、小于等于=（A1=A2）判断A1与A2相等=（B1=5）判断C1大于等于5&文本运算符（将文本字符或字符串进行连接合并）：连接文本=”爱”&”点点”返回结果”爱点点“:区

7、域运算符：冒号=SUM（A1：C10） 引用冒号两边所引用的单元格左上角和右下角之间的所有单元格组成的矩形区域。空格交叉运算符：单个空格=SUM（A1:B5 A4:D9）引用A1:B5与A4:D9的交叉区域，公式相当于=SUM（A4:B5），联合运算符：逗号=SUM（A1,（A1:A10,C1:C10） 第2参数引用A1:A10和C1:C10两个不连续的单元格区域。运算符优先顺序（ ）小括号（优先级高于其他运算符，如果公式内有多组括号进行嵌套，计算顺序则由最内层括号逐级向外进行运算）： 空格 ，冒号、单个空格、逗号百分比5乘和除（注意区别数学中的和）678=、比较两个值（注意区别数学中的、）相

8、对引用：当复制公式到其他单元格时，Excel保持从属单元格与引用单元格的相对位置不变。 例=A1 向下复制为=A2、=A3、=A4-绝对引用：当复制公式到其他单元格时，Excel保持公式所引用的单元格绝对位置不变。 例=$A$1向下复制始终=$A$1，不随公式复制改变混合引用：当复制公式到其他单元格时，Excel仅保持所引用单元格的行或行方向之一的绝对位置不变，而另一方向位置发生变化。 例：=$A5 向下复制为=$A6、=$A7、=$A8 向右复制始终=$A5 =A$5 向右复制为=B$5、=C$5、=D$5 向下复制始终=A$5混合引用实例：跨表引用：在公式中引用其他工作表的单元格区域。 表

9、示方式：工作表名+半角感叹号+引用区域方法：在公式编辑状态下，通过鼠标单击相应的工作表标签，然后选取相应的单元格区域。例：要在工作表Sheet1中的A1单元格中引用工作表Sheet2中的A1:B10区域，则在Sheet1表中A1单元格输入=SUM（后单击Sheet2工作表标签，然后选取A1:B10区域，按回车结束。 则公式将显示为=SUM（Sheet2！A1:B10）引用连续多工作表相同区域：当跨表引用多个相邻的工作表中相同的单元格区域进行汇总时，可以使用三维引用进行计算而无须逐个工作表对单元格区域进行引用。它的表示方式为：按工作表排列顺序，使用冒号将起始工作表和终止工作表名进行连接，作为跨表

10、引用的工作表表名。 工作薄内有连续工作表1、2、3、4，要在另一汇总表内计算前4张表里的同一区域（假设为B5:E10）数值，在需执行公式的单元格内输入=SUM（ 然后单击1工作表标签，按住Shift键后再单击4工作表标签，然后选取引用区域，按Enter结束输入。显示公式为=SUM（1:4 ! B5:E10） 如果汇总表在表2与表3之间，则公式为=SUM（2 E10, 3:E10）套用表格格式：开始套用表格格式 方法：选择某一区域开始套用表格格式（勾选表包含标题），可在设计选项下进行其他操作，例如勾选汇总行，会在表下方自动出现汇总，在下拉列表中有各项汇总项。-函数-函数表示：=号开头、函数名称、

11、左括号、以半角逗号相间隔的参数、右括号。 公式中允许多个函数或计算式，通过运算符连接 函数的参数可以由数值、日期、文本等元素组成，可以使用常量、数组、单元格引用或其他函数。当使用函数作为另一个函数的参数时，称为嵌套函数。有的函数没有参数或者不需要参数，如NOW（返回日期时间格式的当前日期和时间：=NOW（） ）、TODAY（返回当前日期：=TODAY（）、RAND（返回大于或等于0且小于1的平均分布随机数：=RAND（）、PI（圆周率：=PI（）等。Alt + = ：插入求和函数 Shift+F3：打开插入函数对话框生成26个大写字母序列：=CHAR（64+COLUMN（） 小写字母序列：=C

12、HAR（96+COLUMN（）合并并换行：先点自动换行按钮，再输入公式 =G7&CHAR（10）&H7 10是自动换行符的ANSI编码分离姓名和数字 LEFT：从一个文本字符串的第一个字符开始返回指定个数的字符 LENB：返回文本中包含的字符数 LEN：返回文本字符串中的字符个数 RIGHT：从一个文本字符串的最后一个字符开始返回指定个数 的字符。LENB函数按照每个双字节字符（汉字姓名）为2个长度计算，单字节字符按1个长度计算，因此LENB（B4）-LEN（B4）可以求得单元格中双字节字符的个数。2*LEN（B4）-LENB（B4）则可以求得单元格中单字节字符的个数。 LEFT=左 RIGH

13、T=右计算职工人数 G列数据中带有“人”字，要在黄色填充部分求人数的和可以用SUBSTITUTE函数将“人”字删除后再求和。 SUBSTITUTE函数获得的结果为文本型数字，所以要使用减负（- -）运算将基转换为数值，以便SUMPRODUCT函数求和。也可用公式 =（DAYS360（H66,TODAY（）/360 来计算年龄 =IF（MOD（RIGHT（LEFT（B3,17）,2）,男,女MOD：取余函数（返回两数相除的余数）=MOD（34,9） 结果为7 余数结果符号与除数一致 被除数是除数的整数倍时结果为零判断奇偶性： =IF（MOD（15，2）0，”奇数”,”偶数”） 解释：如果15除以

14、2的余数大于0，则返回奇数，否则就返回偶数IF（条件函数）：判断是否满足某个条件，如果满足就返回这个值，如果不满足则返回另一个值。INT：将数值向下取最接近的整数 例：=INT（15.35） 结果为15TRUNC：将数字截为整数或保留指定位数的小数 例：=TRUNC（-8.72） 结果为-8 =TRUNC（9.72） 结果为9ROUNDUP：无条件向上舍入数字 例： =ROUNDUP（23.416,2） 结果为23.42 （和四舍五入类似）ROUNDOWN：无条件向下舍入数字 例： =ROUNDUP（23.416,2） 结果为23.41 （直接舍去） 可用TRUNC函数代替使用。CEILING

15、：向上舍入 例：=CEILING（123.456,0.2） 结果为123.6 FLOOR：向下舍去 例：=FLOOR（123.456,0.2） 结果为123.4 利用取舍函数控制单元格录入数值：数据有效性在公式处输入=C3=FLOOR（C3,0.01） C3为可变更单元格。ROUND：按指定的位数对数值进行四舍五入 例：=ROUND（123.456,2） 结果为：123.46日期和时间函数：允许输入日期区间1900-1-1至9999-12-31 当年份缺省时默认为1900年，当月份缺省为0时表示上一年的12月，当日期缺省为0时表示上一个月的月末天数。常用日期函数函数名称功能作用TODAY和NO

16、W用于生成当前日期和时间DATE根据指定的年份、月份和日期数返回具体的日期值YEAR、MONTH和DAY用于从日期数据中提取年份、月份和日期值YEARFRAC用于计算两个日期序列相差的年数，结果以实数显示EDATE根据指定月份数返回指定日期之前或之后的日期EOMONTH从任意一个日期返回指定月份数的之前或之后的月末日期WORKDAY或WORKDAY.INTL（可以随意指定周工作日）根据源日期按指定工作日天数返回之前或之后的日期NETWORKDAYS或NETWORKDAYS.INTL（可以随意指定周工作日）计算两个日期差工作日天数的函数基本日期函数：快速生成当前日期和时间 Ctrl+ ；和Ctr

17、l+Shift+ ； 这样生成的日期和时间格式是固定不变的 =Today（）和=Now（） 生成的日期会自动更新 =DATE（A2,B2+1,C2） 类似都OK从特定日期提取年月日：年份=YEAR（单元格名称） 月份=MONTH（单元格名称） 日期=DAY（单元格名称）如=DATE（YEAR（TODAY（）+1,10,1）会返回明年的国庆节日期日期之间的运算：+或- 1）计算相差天数：日期-日期=数值 2）计算之前的日期：日期-数值=日期 3）计算之后的日期：日期+数值=日期 TODAY=2014年6月29日 判断一个日期所在的季度：公式利用了DAY （A2）返回的日期数是A2所在月份从第1日

18、起的第几天的来进行处理，当日期减去天数后得出当前月第0天的日期，即为上个月月末日期，再通过DAY函数得出天数。可根据此公式算出当前日期的下一个星期天的日期。工作日函数：NETWORKDAYS：返回参数start_date 和end date之间完整的工作日数值。 工作日不包括周末和专门指定的假期。可以使用函数 NETWORKDAYS，根据某一特定时期内雇员的工作天数，计算其应计的报酬。提示:若要使用参数来指明周末的日期和天数,从而计算两个日期间的全部工作日数,请使用NETWORKDAYS.INTL函数.时间的计算：Time：用于按指定数字生成具体的时间 hour：用于提取时间系列值中的小时数M

19、inute：用于提取时间系列值中的分钟数 second：用于提取时间系列值中的秒数计算下班时间：例 上班时间为2014-7-6 8：30 计算工作8小时后的时间：统计与求和 汇总大于1000的点点的合计类似汇总指定商品的销量计算退休日期：计算工龄（年龄）： 小数位数开始时期 结束日期 开始-条件格式利用VLOOKUP函数查询并汇总指定产品月销量和1、选中区域创建数据表：表1 2、输入内容：3、制作查询表：4、输入数组公式：公式在输入结束后，按Ctrl + Shift + Enter完成数组公式的输入。按Ctrl + Shift + Enter两边多了个大括号 随便输入条件自动查询使用通配符（*

20、 ?二维区域的条件求和（SUMIF/SUMIFS）高级图表1、筛选法动态图表 设置自动筛选是实现动态图表最简单的方法，只要选择全部数据制作图表，再设置自动筛选就好啦。比如：先建立数据表， 再选中全部数据建立柱形图（其它的也可以） 再设置筛选 数据-筛选看看效果：2、公式法动态图表制表 添加辅助行 A11选中A11设置数据验证（又叫数据有效性） 列 行（ROW） 确定后完成有效性设置，效果 然后在B11中输入公式 并将公式复制到C11和D11单元格中。效果 再选中A11-D11建立图表选任意一个部门查看图表效果3、定义名称法动态图表制表 添加组合框窗体控件（开发工具插入） 点击组合框点键，选取设置控件格式设置好相应数据 后按确定效果是这样：然后再定义名称：公式-名称管理器新建输入定义名称：及位置 工作表名